删除数据库:
$ 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 DATABASEtest_01;2
3 CREATE DATABASEmysql_shiyan;4
5 usemysql_shiyan;6
7 CREATE TABLEdepartment8 (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 TABLEemployee15 (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) REFERENCESdepartment(dpt_name)24 );25
26 CREATE TABLEproject27 (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) REFERENCESdepartment(dpt_name),33 CONSTRAINT proj_pk PRIMARY KEY(proj_num,proj_name)34 );35
36 CREATE TABLEtable_137 (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