MYSQL数据库-修改和删除
删除数据库:
$ DROP DATABASE t_name;
重命名一张表：
$ RENAME TABLE ori_name TO new_name;
$ ALTER TABLE ori_name RENAME new_name;
$ ALTER TABLE or_name RENAME TO new_name;
删除一张表：
$ DROP TABLE t_name;
==================================================
对表结构的修改：
增加表中一列：
$ ALTER TABLE t_name ADD COLUMN col_name data_type constrain;
or $ ALTER TABLE t_name ADD col_name data_type constrain;
规定新加列的位置，放在col_name列的下一列，在SQL语句后加：
$ AFTER col_name;
如果想放在第一列,在SQL后加：
$ FIRST
删除一列：
$ ALTER TABLE t_name DROP COLUMN col_name;
or $ ALTER TABLE t_name DROP col_name;
列的重命名：
$ ALTER TABLE t_name CHANGE ori_col new_col data_type constrain;
改变数据类型：
$ ALTER TABLE t_name MODIFY col_name new_data_type;
===================================================
对表内容修改：
修改表中某个值：
$ UPDATE t_name SET col1=v1,col2=v2 WHERE condition;
删除一行记录：
$ DELETE FROM t_name WHERE condition;
SQL
1 CREATE DATABASE test_01; 2 3 CREATE DATABASE mysql_shiyan; 4 5 use mysql_shiyan; 6 7 CREATE TABLE department 8 ( 9 dpt_name CHAR(20) NOT NULL, 10 people_num INT(10) DEFAULT \'10\', 11 CONSTRAINT dpt_pk PRIMARY KEY (dpt_name) 12 ); 13 14 CREATE TABLE employee 15 ( 16 id INT(10) PRIMARY KEY, 17 name CHAR(20), 18 age INT(10), 19 salary INT(10) NOT NULL, 20 phone INT(12) NOT NULL, 21 in_dpt CHAR(20) NOT NULL, 22 UNIQUE (phone), 23 CONSTRAINT emp_fk FOREIGN KEY (in_dpt) REFERENCES department(dpt_name) 24 ); 25 26 CREATE TABLE project 27 ( 28 proj_num INT(10) NOT NULL, 29 proj_name CHAR(20) NOT NULL, 30 start_date DATE NOT NULL, 31 end_date DATE DEFAULT \'2015-04-01\', 32 of_dpt CHAR(20) REFERENCES department(dpt_name), 33 CONSTRAINT proj_pk PRIMARY KEY (proj_num,proj_name) 34 ); 35 36 CREATE TABLE table_1 37 ( 38 l_1 INT(10) PRIMARY KEY, 39 l_2 INT(10), 40 l_3 INT(10) 41 ); 42 43 44 45 #INSERT INTO department(dpt_name,people_num) VALUES(\'\u90e8\u95e8\',\u4eba\u6570); 46 47 INSERT INTO department(dpt_name,people_num) VALUES(\'dpt1\',11); 48 INSERT INTO department(dpt_name,people_num) VALUES(\'dpt2\',12); 49 INSERT INTO department(dpt_name,people_num) VALUES(\'dpt3\',10); 50 INSERT INTO department(dpt_name,people_num) VALUES(\'dpt4\',15); 51 52 53 #INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(\u7f16\u53f7,\'\u540d\u5b57\',\u5e74\u9f84,\u5de5\u8d44,\u7535\u8bdd,\'\u90e8\u95e8\'); 54 55 INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(01,\'Tom\',26,2500,119119,\'dpt4\'); 56 INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(02,\'Jack\',24,2500,120120,\'dpt2\'); 57 INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(03,\'Rose\',22,2800,114114,\'dpt3\'); 58 INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(04,\'Jim\',35,3000,100861,\'dpt1\'); 59 INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(05,\'Mary\',21,3000,100101,\'dpt2\'); 60 INSERT INTO employee(id,name,age,salary,phone,in_dpt) VALUES(06,\'Alex\',26,3000,123456,\'dpt1\'); 61 62 63 64 65 66 #INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(\u7f16\u53f7,\'\u5de5\u7a0b\u540d\',\'\u5f00\u59cb\u65f6\u95f4\',\'\u7ed3\u675f\u65f6\u95f4\',\'\u90e8\u95e8\u540d\'); 67 68 INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(01,\'proj_a\',\'2015-01-15\',\'2015-01-31\',\'dpt2\'); 69 INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(02,\'proj_b\',\'2015-01-15\',\'2015-02-15\',\'dpt1\'); 70 INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(03,\'proj_c\',\'2015-02-01\',\'2015-03-01\',\'dpt4\'); 71 INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(04,\'proj_d\',\'2015-02-15\',\'2015-04-01\',\'dpt3\'); 72 INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(05,\'proj_e\',\'2015-02-25\',\'2015-03-01\',\'dpt4\'); 73 INSERT INTO project(proj_num,proj_name,start_date,end_date,of_dpt) VALUES(06,\'proj_f\',\'2015-02-26\',\'2015-03-01\',\'dpt2\'); 74 75 76 #INSERT INTO table_1 VALUES(01,11,12); 77 78 INSERT INTO table_1 VALUES(02,22,89); 79 INSERT INTO table_1 VALUES(03,56,33); 80 INSERT INTO table_1 VALUES(04,34,37); 81 INSERT INTO table_1 VALUES(05,39,32); 82 INSERT INTO table_1 VALUES(06,90,33); 83 84 85 86 87 88
View Code