CREATE TABLE IF NOT EXISTS user1(

id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(20) NOT NULL UNIQUE,

pid TINYINT UNSIGNED ,

CONSTRAINT user1_fk_provinces FOREIGN KEY(pid) REFERENCES provinces(id)

);

INSERT user(username,pid) VALUES('DSFSA',1);

INSERT user(username,pid) VALUES('ZDX',2);

INSERT user(username,pid) VALUES('SSA',3);

INSERT user(username,pid) VALUES('HGA',4);

INSERT user(username,pid) VALUES('HGRE',5);省份没有id=5,插入失败

DELETE FROM provinces WHERE id=4;不成功，id=4的省份还有用户，应当先删除用户，在删除省份。

SELECT * FROM user;

DELETE FROM user WHERE pid=4;

DELETE FROM provinces WHERE id=4;

DELETE FROM provinces WHERE id=3;一样不成功，原因同id=4，有外键约束存在：

下面删除外键：

ALTER TABLE tb_name DROP FOREIGN KEY 约束名称；

--查看user约束名称

SHOW CREATE TABLE user;

--删除外键约束

ALTER TABLE user DROP FOREIGN KEY user_ibfk_1;

--现在做删除操作，成功

DELETE FROM provinces WHERE id=3;

添加外键

ALTER TABLE tb_name ADD CONSTRAINT 约束名称 FOREIGN KEY(字段） REFERENCES 主表（字段）；

ALTER TABLE user ADD CONSTRAINT user_fk_provinces FOREIGN KEY(pid) REFERENCES provinces(id);不成功

DELETE FROM user WHERE pid=3;

ALTER TABLE user ADD CONSTRAINT user_fk_provinces FOREIGN KEY(pid) REFERENCES provinces(id);删除pid=3的记录后添加成功

先删除子表记录，再删除父表记录

创建外键的时候可以指定一下，当我们删除父表记录时，子表应当进行什么样的操作。

CASCADE,级联

SET NULL(保证列没有非空约束)

RESTRICT

NO ACTION效果同RESTRICT

ON UPDATE|ON DELETE CASCADE|SET NULL|RESTRICT|NO ACTION

--测试CASCADE

ALTER TABLE user DROP FOREIGN KEY user_fk_provinces;

SHOW CREATE TABLE user;

ALTER TABLE user ADD CONSTRAINT user_fk_provinces FOREIGN KEY(pid) REFERENCES provinces(id) ON DELETE CASCADE;

DELETE FROM provinces WHERE id=2;实际上先删除user中的pid=2的记录，再删除provinces表中的记录。

实际开发中采用外键思想进行操作，而不是纯的物理外键

外连接

LEFT|RIGHT [OUTER] JOIN ON 条件

LEFT:显示左表中的全部记录和右表中符合条件的记录

RIGHT:显示右表中的全部记录和左表中符合条件的记录

--内连接

SELECT e.id,e.username,d.depname FROM employee AS e JOIN department AS d ON e.depid=d.id;

--外连接

SELECT e.id,e.username,d.depname FROM employee AS e LEFT JOIN department AS d ON e.depid=d.id;

当右表中没有满足条件时，NULL填充

SELECT e.id,e.username,d.depname FROM employee AS e RIGHT JOIN department AS d ON e.depid=d.id;

内连接查询两个或多个表中都符合条件的记录

INSERT department(depname) VALUES('测试部门');

SELECT e.id,e.username,d.depname FROM employee AS e RIGHT JOIN department AS d ON e.depid=d.id;

联合查询

多个表中的记录和在一起

UNION ALL，简单地将查询结果合并到一起

UNION会去掉重复记录

SELECT * FROM employee;9

SELECT * FROM user;1

SELECT username FROM user UNION ALL SELECT username FROM employee;

联合查询，字段顺序、字段数目一定要相同

SELECT id,username,age FROM employee UNION ALL SELECT id AS uid FROM user;不成功

SELECT id,username,age FROM employee UNION ALL SELECT id AS uid,username AS uname,1 FROM user;

多表更新和删除

--将employee表中的addr存为省份表中的id

UPDATE employee AS e JOIN provinces AS p ON e.pid=p.id SET e.addr=p.id;

SELECT * FROM employee;

--删除所有所在省份在省份表中省份的人

DELETE e FROM employee AS e JOIN provinces AS p ON e.pid=p.id;

多表操作，注意表和表间的连接条件

删除数据表

一张表

DROP TABLE tb_name;

多张表

DROP TABLE tb_name1,tb_name;