USE h /*默认值*/ CREATE TABLE emp( id INT, ename VARCHAR(5), address VARCHAR(20) DEFAULT '山东淄博'(默认值) ) INSERT INTO emp(id,ename) VALUES(1,'张三'); INSERT INTO emp VALUES(2,'李四','山东济南'); INSERT INTO emp VALUES(3,'小红',''); SELECT * FROM emp; /*不能为空值*/ CREATE TABLE emp02( id INT NOT NULL,(不能为空值,前面不加is) ename VARCHAR (5), sex VARCHAR (5) ) INSERT INTO emp02 VALUES(1,'王瑞昌','男'); INSERT INTO emp02 VALUES(1,'王昌','男'); SELECT * FROM emp02; /*唯一约束,可以插入重复null,但别的值不能重复*/ CREATE TABLE emp03( /*非空(null)+唯一*/ id INT NOT NULL UNIQUE,(唯一约束), ename VARCHAR(5) ) INSERT INTO emp03 VALUES(1,'小红'); /*id不能重复*/ INSERT INTO emp03 VALUES(1,'小名'); SELECT * FROM emp03; /*主键*/ CREATE TABLE emp04( id INT PRIMARY KEY, ename VARCHAR (5) ) SELECT * FROM emp04; INSERT INTO emp04 VALUES(1,'小红'); /*不能插null*/ INSERT INTO emp04 VALUES(NULL,'小兰'); /*id不能重复*/ INSERT INTO emp04 VALUES(1,'小兰'); /*正确情况*/ INSERT INTO emp04 VALUES(2,'小兰'); /*自增长(前提是主键)*/ CREATE TABLE emp05( id INT PRIMARY KEY AUTO_INCREMENT,(自增长) ename VARCHAR (5) ) /*id自增长*/ INSERT INTO emp05(ename) VALUES('小兰'); INSERT INTO emp05(ename) VALUES('小红'); INSERT INTO emp05(ename) VALUES('小明'); SELECT * FROM emp05; /*删除id2后再插入2,id从3开始*/ DELETE FROM emp05 WHERE id='2' /*前面id固定4位,自动补0*/ CREATE TABLE emp06( id INT(4) ZEROFILL(自动补0) PRIMARY KEY AUTO_INCREMENT, ename VARCHAR (5) ) SELECT * FROM emp06; INSERT INTO emp06(ename) VALUES('小兰'); INSERT INTO emp06(ename) VALUES('小红'); INSERT INTO emp06(ename) VALUES('小明'); /*数据删除*/ DELETE FROM emp05 WHERE id='2' /*外键(仿乱写)*/ /*主表*/ CREATE TABLE dept( id INT PRIMARY KEY, ename VARCHAR (10) ) /*附表*/ CREATE TABLE employee( id INT PRIMARY KEY, ename VARCHAR (5), deptid INT, /*外键写法*/ CONSTRAINT enmployee_dept_fk FOREIGN KEY(deptid) REFERENCES dept(id) ) INSERT INTO dept VALUES(20,'秘书部'); INSERT INTO dept VALUES(30,'经理部'); /*数据修改*/ UPDATE dept SET id=50 WHERE id=20; SELECT * FROM dept; INSERT INTO employee VALUES(1,'张三',20); /*前面表没有50所以失败*/ INSERT INTO employee VALUES(2,'李三',50); /*前表有30所以成功*/ INSERT INTO employee VALUES(2,'李三',30); SELECT * FROM employee; /*数据修改*/ UPDATE employee SET deptid=30 WHERE id=1; /*表连接*/ /*交叉连接(完全连接)如果不加条件*/ SELECT * FROM zj1 INNER JOIN fb1; /*内连接(有条件on)*/ SELECT * FROM zj1 INNER JOIN fb1 ON zj1.`protype_id`=fb1.`protype_id`; /*左外连接*/ SELECT * FROM zj1 LEFT JOIN fb1 ON zj1.`protype_id`=fb1.`protype_id`; /*右外连接*/ SELECT * FROM zj1 RIGHT JOIN fb1 ON zj1.`protype_id`=fb1.`protype_id`; /*找出索尼4g手机所属类别*/ SELECT zj1.protype_name FROM fb1 INNER JOIN zj1 ON zj1.`protype_id`=fb1.`protype_id` WHERE protype_name LIKE '%索尼%' AND protype_name LIKE '%4G%'; /*查找属于手机数码的商品*/ SELECT *FROM fb1 INNER JOIN zj1 ON zj1.`protype_id`=fb1.`protype_id` WHERE protype_name='手机数码'; /*子查询*/ /*查找属于手机数码的商品*/ SELECT * FROM fb1 WHERE protype_id=( SELECT protype_id FROM zj1 WHERE protype_name='手机数码' ) /*找出索尼4g手机所属类别 (=可以用in)*/ SELECT * FROM zj1 WHERE protype_id=( SELECT protype_id FROM fb1 WHERE pro_name LIKE '%索尼%' AND pro_name LIKE '%4G%' ) /* */ SELECT pro_name FROM fb1 WHERE protype_id IN (1,2); /*联合查询*/ SELECT protype_id,protype_name FROM zj1 UNION SELECT pro_id,pro_name FROM fb1;