1、什么是连接查询？ 连接查询是将两个或两个以上的表按某个条件连接起来，从中选取需要的数据。连接查询是同时查询两个或两个以上的表时使用的。当不同的表中存在相同意义的字段时，可以通过该字段连接这几个表
2、内连接查询 内连接是用JOIN、CROSS JOIN或INNER JOIN通过ON连接来显示两个表中符合连接条件的记录，例如我们查询cms_user中的用户与provinces中的省份：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 mysql> SELECT u.id,u.username,u.email,u.sex,p.proName -> FROM cms_user AS u # 先任意选一张表 -> INNER JOIN -> provinces AS p # 再查询省份表 -> ON u.proId=p.id; # 通过id相同 + | id | username | email | sex | proName | + | 1 | 张三 | user@qq.com | 男 | 北京 | | 2 | 张三丰 | user@qq.com | 女 | 上海 | | 3 | 章子怡 | user@qq.com | 男 | 深圳 | | 4 | long | user@qq.com | 女 | 广州 | | 5 | ring | user@qq.com | 男 | 上海 | | 6 | queen | user@qq.com | 女 | 深圳 | | 7 | sing | user@qq.com | 男 | 重庆 | | 8 | blek | user@qq.com | 女 | 北京 | | 9 | rose | user@qq.com | 男 | 上海 | | 10 | lily | user@qq.com | 女 | 上海 | | 11 | john | user@qq.com | 保密 | 上海 | | 12 | test1 | user@qq.com | 保密 | 北京 | + 12 rows in set (0.01 sec) `` ` 就测试一个，另两个是一样的，之前的查询条件等一样使用，比如上面的方法只查询性别为` 男`： ` `` SQL mysql> SELECT u.id,u.username,u.sex,p.proName -> FROM cms_user AS u -> JOIN -> provinces AS p -> ON u.proId=p.id -> WHERE u.sex='男' ; + | id | username | sex | proName | + | 1 | 张三 | 男 | 北京 | | 3 | 章子怡 | 男 | 深圳 | | 5 | ring | 男 | 上海 | | 7 | sing | 男 | 重庆 | | 9 | rose | 男 | 上海 | + 5 rows in set (0.00 sec)
来一个完整型的查询：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 mysql> SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username) -> FROM cms_user AS u -> JOIN -> provinces AS p -> ON u.proId=p.id -> WHERE u.sex='男' -> GROUP BY p.proName -> HAVING COUNT(*)>=1 -> ORDER BY u.id ASC -> LIMIT 0,2; + | id | username | sex | proName | totalUsers | GROUP_CONCAT(username) | + | 1 | 张三 | 男 | 北京 | 1 | 张三 | | 3 | 章子怡 | 男 | 深圳 | 1 | 章子怡 | + 2 rows in set (0.01 sec)
又例如，查询cms_news中的id、title,查询cms_cate中的cateName：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 mysql> SELECT n.id,n.title,c.cateName FROM -> cms_news AS n -> JOIN -> cms_cate AS c -> ON n.cId=c.id; + | id | title | cateName | + | 1 | 亚航客机失联搜救尚无线索 未发求救信号 | 国内新闻 | | 2 | 北京新开通四条地铁线路 迎接首位客人 | 国际新闻 | | 3 | 考研政治题多次出现习近平讲话内容 | 体育新闻 | | 4 | 深度-曾雪麟：佩兰别重蹈卡马乔覆辙 | 国际新闻 | | 5 | 国产JAD-1手枪枪架投入使用 手枪可变"冲锋枪" | 军事新闻 | | 6 | 测试数据1 | 国内新闻 | | 7 | 测试数据2 | 国内新闻 | | 8 | 测试数据3 | 国内新闻 | + 8 rows in set (0.01 sec)
如果要查询新闻的id、title、cateName、username、role,那么涉及到了三张表新闻表、分类表、用户表：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 mysql> SELECT n.id,n.title,c.cateName,a.username,a.role -> FROM cms_cate AS c -> JOIN -> cms_news AS n -> ON n.cId=c.id -> JOIN -> cms_admin AS a -> ON n.aId=a.id; + | id | title | cateName | username | role | + | 1 | 亚航客机失联搜救尚无线索 未发求救信号 | 国内新闻 | sing | 普通管理员 | | 2 | 北京新开通四条地铁线路 迎接首位客人 | 国际新闻 | admin | 超级管理员 | | 3 | 考研政治题多次出现习近平讲话内容 | 体育新闻 | sing | 普通管理员 | | 4 | 深度-曾雪麟：佩兰别重蹈卡马乔覆辙 | 国际新闻 | test | 普通管理员 | | 5 | 国产JAD-1手枪枪架投入使用 手枪可变"冲锋枪" | 军事新闻 | test | 普通管理员 | + 5 rows in set (0.00 sec)
3、外连接查询 首先我们看下省份表的数据：
1 2 3 4 5 6 7 8 9 10 11 mysql> SELECT * FROM provinces; + | id | proName | + | 2 | 上海 | | 1 | 北京 | | 4 | 广州 | | 3 | 深圳 | | 5 | 重庆 | + 5 rows in set (0.01 sec)
可以看到有5条数据，加入我们插入一条错误数据，比如用户的proId不在此表内：
1 2 3 mysql> INSERT cms_user(username,password,regTime,proId) -> VALUES('TEST2','TEST2','1381203974',20); Query OK, 1 row affected (0.01 sec)
然后我们查询这条数据是插入成功的，但是通过内连接查询永远也查不出来，因为这是条错误数据，这条数据是不应该插入成功的，可以通过外键来控制。
1、左外连接 LEFT [OUTER] JOIN，显示左表的全部记录及右表符合连接条件的记录
2、右外连接 RIGHT [OUTER] JOIN，显示右表的全部记录以及左表符合条件的记录
首先测试一下左外连接： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 mysql> SELECT u.id,u.username,u.email,u.sex,p.proName -> FROM cms_user AS u -> LEFT JOIN provinces AS p -> ON u.proId=p.id; + | id | username | email | sex | proName | + | 1 | 张三 | user@qq.com | 男 | 北京 | | 2 | 张三丰 | user@qq.com | 女 | 上海 | | 3 | 章子怡 | user@qq.com | 男 | 深圳 | | 4 | long | user@qq.com | 女 | 广州 | | 5 | ring | user@qq.com | 男 | 上海 | | 6 | queen | user@qq.com | 女 | 深圳 | | 7 | sing | user@qq.com | 男 | 重庆 | | 8 | blek | user@qq.com | 女 | 北京 | | 9 | rose | user@qq.com | 男 | 上海 | | 10 | lily | user@qq.com | 女 | 上海 | | 11 | john | user@qq.com | 保密 | 上海 | | 12 | test1 | user@qq.com | 保密 | 北京 | | 13 | TEST2 | user@qq.com | NULL | NULL | + 13 rows in set (0.00 sec)
上面这条查询为左表连接查询，首先查询LEFT JOIN左边的表的全部记录，也就是cms_user为主表，会查询到所以记录，然后在右表条件不符合的会用NULL代替，所以可以看到TEST2这条记录，那么互换一下表：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 mysql> SELECT u.id,u.username,u.email,u.sex,p.proName -> FROM provinces AS p -> LEFT JOIN cms_user AS u -> ON u.proId=p.id; + | id | username | email | sex | proName | + | 1 | 张三 | user@qq.com | 男 | 北京 | | 2 | 张三丰 | user@qq.com | 女 | 上海 | | 3 | 章子怡 | user@qq.com | 男 | 深圳 | | 4 | long | user@qq.com | 女 | 广州 | | 5 | ring | user@qq.com | 男 | 上海 | | 6 | queen | user@qq.com | 女 | 深圳 | | 7 | sing | user@qq.com | 男 | 重庆 | | 8 | blek | user@qq.com | 女 | 北京 | | 9 | rose | user@qq.com | 男 | 上海 | | 10 | lily | user@qq.com | 女 | 上海 | | 11 | john | user@qq.com | 保密 | 上海 | | 12 | test1 | user@qq.com | 保密 | 北京 | + 12 rows in set (0.00 sec)
由此看到，首先查询到了provinces中的所有数据，只有5条，所以是查不到TEST2这条数据的。
1 2 3 4 5 6 7 8 9 SELECT u.id,u.username,u.email,u.sex,p.proNameFROM provinces AS pRIGHT JOIN cms_user AS uON u.proId=p.id;SELECT u.id,u.username,u.email,u.sex,p.proNameFROM provinces AS pRIGHT JOIN cms_user AS uON u.proId=p.id;
=============http://www.maiziedu.com/course/306-3378/
4、外键 外键是表的一个特殊字段。被参照的表是主表，外键所在字段的表为子表。设置外键的原则需要记住，就是依赖于数据库中已存在的表的主键。外键的作用是建立该表与其父表的关联关系。父表中对记录做操作时，子表中与之对应的信息也应有相应的改变。
外键的作用保持数据的一致性和完整性，可以实现一对一或一对多的关系。 注意：
父表和子表必须使用相同的存储引擎，而且禁止使用临时表。
数据表的存储引擎只能为InnoDB。
外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同。
外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL将自动创建索引。
外键约束的参照操作
CASCADE：从父表删除或更新且自动删除或更新子表中匹配的行。
SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL。
RESTRICT：拒绝对父表的删除或更新操作。
NO ACTION：标准SQL的关键字，在MySQL中与RESTRICT相同。
比如，某公司的组成，一定是先有部门再有员工，那么部门为主表，员工表为子表，首先创建主表部门表并插入几条数据：
1 2 3 4 5 6 7 8 9 10 11 12 mysql> CREATE TABLE IF NOT EXISTS department( -> id TINYINT UNSIGNED AUTO_INCREMENT KEY, -> depName VARCHAR(20) NOT NULL UNIQUE -> )ENGINE=INNODB; Query OK, 0 rows affected (0.02 sec) mysql> INSERT department(depName) VALUES('教学部'), -> ('市场部'), -> ('运营部'), -> ('督导部'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0
然后我们创建子表员工表并插入几条数据：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 mysql> CREATE TABLE IF NOT EXISTS employee( -> id SMALLINT UNSIGNED AUTO_INCREMENT KEY, -> username VARCHAR(20) NOT NULL UNIQUE, -> depId TINYINT UNSIGNED -> )ENGINE=INNODB; Query OK, 0 rows affected (0.02 sec) mysql> INSERT employee(username,depId) VALUES('king',1), -> ('queen',2), -> ('张三',3), -> ('李四',4), -> ('王五',1); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0
此时两张表是管理的，如果我们删除直接督导部：
1 DELETE FROM department WHERE depName='督导部' ;
此时是可以成功的，而且也删除掉了，但是我们的员工表中还有一条数据存在于督导部中，很明显不合理，应该先把该部门下的人删除或修改别的部门再删除，或者用我们的外键FOREIGN KEY和REFERENCES来操作，先删除之前的表，并创建：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 mysql> DROP TABLE employee,department; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE IF NOT EXISTS department( -> id TINYINT UNSIGNED AUTO_INCREMENT KEY, -> depName VARCHAR(20) NOT NULL UNIQUE -> )ENGINE=INNODB; # 一定要先创建主表 Query OK, 0 rows affected (0.02 sec) mysql> INSERT department(depName) VALUES('教学部'), -> ('市场部'), -> ('运营部'), -> ('督导部'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> CREATE TABLE IF NOT EXISTS employee( -> id SMALLINT UNSIGNED AUTO_INCREMENT KEY, -> username VARCHAR(20) NOT NULL UNIQUE, -> depId TINYINT UNSIGNED, -> FOREIGN KEY(depId) REFERENCES department(id)# 外键depId，参照部门表的id -> )ENGINE=INNODB; Query OK, 0 rows affected (0.02 sec) mysql> INSERT employee(username,depId) VALUES('king',1), -> ('queen',2), -> ('张三',3), -> ('李四',4), -> ('王五',1); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0
然后我们查看一下表结构：
1 2 3 4 5 6 7 8 9 mysql> DESC employee; + | Field | Type | Null | Key | Default | Extra | + | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | NO | UNI | NULL | | | depId | tinyint(3) unsigned | YES | MUL | NULL | | + 3 rows in set (0.00 sec)
我们可以看到在depId中KEY已经添加了MUL，再看一下创建表的详细信息：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> SHOW CREATE TABLE employee; + | Table | Create Table | + | employee | CREATE TABLE `employee` ( `id` smallint (5 ) unsigned NOT NULL AUTO_INCREMENT, `username` varchar (20 ) NOT NULL , `depId` tinyint(3 ) unsigned DEFAULT NULL , PRIMARY KEY (`id` ), UNIQUE KEY `username` (`username` ), KEY `depId` (`depId` ), CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`depId` ) REFERENCES `department` (`id` ) ) ENGINE =InnoDB DEFAULT CHARSET =utf8 | + 1 row in set (0.00 sec)
可以看到系统默认添加了外键employee_ibfk_1，这个在删除外键的时候用，我们先删除一下主表的一条数据：
1 2 mysql> DELETE FROM department WHERE id=1; ERROR 1451 (23000): Cannot delete or update a parent row : a foreign key constraint fails (`cms` .`employee` , CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`depId` ) REFERENCES `department` (`id` ))
很明显1部门下有员工，所以必须先删除该部门下的员工才可以删除该表：
1 2 3 4 5 mysql> DELETE FROM employee WHERE depId=1; Query OK, 2 rows affected (0.00 sec) mysql> DELETE FROM department WHERE id=1; Query OK, 1 row affected (0.00 sec)
如果我们再出入一条数据，那么这个员工必须是已有的部门下，比如插入一条错误数据：
1 2 mysql> INSERT employee(username,depId) VALUES('test',11); ERROR 1452 (23000): Cannot add or update a child row : a foreign key constraint fails (`cms` .`employee` , CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`depId` ) REFERENCES `department` (`id` ))
该语句是没有任务错误，如果没有外键约束也可以成功的。
外键的指定与删除 我们可以在创建表的时候通过CONSTRAINT指定外键名称，而外键的名字我们可以用主表名简写_fk_子表名简写来命名更清晰的看到谁是主表谁是子表，首先删除employee并重新创建：
1 2 3 4 5 6 7 8 9 10 mysql> DROP TABLE employee; Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE IF NOT EXISTS employee( -> id SMALLINT UNSIGNED AUTO_INCREMENT KEY, -> username VARCHAR(20) NOT NULL UNIQUE, -> depId TINYINT UNSIGNED, -> CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id) -> )ENGINE=INNODB; Query OK, 0 rows affected (0.03 sec)
可以通过SHOW CREATE TABLE employee;来查看建表是的详细信息中外键名就是我们指定的名称，那么如何删除外键？
1 2 3 mysql> ALTER TABLE employee DROP FOREIGN KEY emp_fk_dep; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0
此时已经成功删除，可以通过表的详细定义查看，那么又如何动态添加外键？
1 2 3 mysql> ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id); Query OK, 5 rows affected (0.08 sec) Records: 5 Duplicates: 0 Warnings: 0
成功之后同样可以通过表的详细定义查看外键。
CASCADE 当删除父表中的记录，子表中的与之对应的外键列也会被删除，首先删除之前的表：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 mysql> DROP TABLE employee,department; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE IF NOT EXISTS department( -> id TINYINT UNSIGNED AUTO_INCREMENT KEY, -> depName VARCHAR(20) NOT NULL UNIQUE -> )ENGINE=INNODB; Query OK, 0 rows affected (0.02 sec) mysql> mysql> INSERT department(depName) VALUES('教学部'), -> ('市场部'), -> ('运营部'), -> ('督导部'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> mysql> CREATE TABLE IF NOT EXISTS employee( -> id SMALLINT UNSIGNED AUTO_INCREMENT KEY, -> username VARCHAR(20) NOT NULL UNIQUE, -> depId TINYINT UNSIGNED, -> FOREIGN KEY(depId) REFERENCES department(id) ON DELETE CASCADE -> )ENGINE=INNODB; Query OK, 0 rows affected (0.01 sec) mysql> mysql> mysql> INSERT employee(username,depId) VALUES('king',1), -> ('queen',2), -> ('张三',3), -> ('李四',4), -> ('王五',1); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0
看一下建表的详细信息：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> SHOW CREATE TABLE employee; + | Table | Create Table | + | employee | CREATE TABLE `employee` ( `id` smallint (5 ) unsigned NOT NULL AUTO_INCREMENT, `username` varchar (20 ) NOT NULL , `depId` tinyint(3 ) unsigned DEFAULT NULL , PRIMARY KEY (`id` ), UNIQUE KEY `username` (`username` ), KEY `depId` (`depId` ), CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`depId` ) REFERENCES `department` (`id` ) ON DELETE CASCADE ) ENGINE =InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET =utf8 | + 1 row in set (0.00 sec)
可以看到CONSTRAINTemployee_ibfk_1FOREIGN KEY (depId) REFERENCESdepartment(id) ON DELETE CASCADE中多了ON DELETE CASCADE，测试一下当我们删除部门表中的第一个数据时：
1 2 mysql> DELETE FROM department WHERE id=1; Query OK, 1 row affected (0.01 sec)
之前是不让删的，当我们写了一个级联的条件时可以看到成功删除，我们再看下两个数据表中的数据：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 mysql> SELECT * FROM department; + | id | depName | + | 2 | 市场部 | | 4 | 督导部 | | 3 | 运营部 | + 3 rows in set (0.00 sec) mysql> SELECT * FROM employee; + | id | username | depId | + | 2 | queen | 2 | | 3 | 张三 | 3 | | 4 | 李四 | 4 | + 3 rows in set (0.00 sec)
可以看到我们的department表中的部门被删掉了，同时employee表中的两条数据也跟着删掉了，这是删除，对于更新：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 mysql> UPDATE department SET id=id+10; ERROR 1451 (23000): Cannot delete or update a parent row : a foreign key constraint fails (`cms` .`employee` , CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`depId` ) REFERENCES `department` (`id` ) ON DELETE CASCADE ) mysql> DELETE FROM employee; Query OK, 3 rows affected (0.00 sec) mysql> UPDATE department SET id=id+10; Query OK, 3 rows affected (0.01 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> SELECT * FROM department; + | id | depName | + | 12 | 市场部 | | 14 | 督导部 | | 13 | 运营部 | + 3 rows in set (0.00 sec)
可以看到我们直接更新是不允许的，除非先删除子表，再更新，我们可以用另一种方式来级联操作(先删除之前的表重新创建)：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 mysql> DROP TABLE employee; Query OK, 0 rows affected (0.00 sec) mysql> DROP TABLE department; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE IF NOT EXISTS department( -> id TINYINT UNSIGNED AUTO_INCREMENT KEY, -> depName VARCHAR(20) NOT NULL UNIQUE -> )ENGINE=INNODB; Query OK, 0 rows affected (0.02 sec) mysql> INSERT department(depName) VALUES('教学部'), -> ('市场部'), -> ('运营部'), -> ('督导部'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> CREATE TABLE IF NOT EXISTS employee( -> id SMALLINT UNSIGNED AUTO_INCREMENT KEY, -> username VARCHAR(20) NOT NULL UNIQUE, -> depId TINYINT UNSIGNED, -> FOREIGN KEY(depId) REFERENCES department(id) ON DELETE CASCADE ON UPDATE CASCADE # 这里是新加的语句 -> )ENGINE=INNODB; Query OK, 0 rows affected (0.01 sec) mysql> INSERT employee(username,depId) VALUES('king',1), -> ('queen',2), -> ('张三',3), -> ('李四',4), -> ('王五',1); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0
继续看一下现在的表信息：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> SHOW CREATE TABLE employee; + | Table | Create Table | + | employee | CREATE TABLE `employee` ( `id` smallint (5 ) unsigned NOT NULL AUTO_INCREMENT, `username` varchar (20 ) NOT NULL , `depId` tinyint(3 ) unsigned DEFAULT NULL , PRIMARY KEY (`id` ), UNIQUE KEY `username` (`username` ), KEY `depId` (`depId` ), CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`depId` ) REFERENCES `department` (`id` ) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE =InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET =utf8 | + 1 row in set (0.00 sec)
可以看到，删除的时候是级联的，更新的时候也是级联的，我们再执行上面的删除部门，并查看：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 mysql> DELETE FROM department WHERE id=1; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM department; + | id | depName | + | 2 | 市场部 | | 4 | 督导部 | | 3 | 运营部 | + 3 rows in set (0.00 sec) mysql> SELECT * FROM employee; + | id | username | depId | + | 2 | queen | 2 | | 3 | 张三 | 3 | | 4 | 李四 | 4 | + 3 rows in set (0.00 sec)
可以看到成功删除，并且相应的子表数据也删除掉了，再看一下更新操作：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 mysql> UPDATE department SET id=id+10; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> SELECT * FROM department; + | id | depName | + | 12 | 市场部 | | 14 | 督导部 | | 13 | 运营部 | + 3 rows in set (0.00 sec) mysql> SELECT * FROM employee; + | id | username | depId | + | 2 | queen | 12 | | 3 | 张三 | 13 | | 4 | 李四 | 14 | + 3 rows in set (0.00 sec)
也同样的子表随着父表更新而更新，这是CASCADE，再测一下SET NULL,先删除之前的表，并重新创建，创建的时候使用SET NULL:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 mysql> DROP TABLE employee,department; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE IF NOT EXISTS department( -> id TINYINT UNSIGNED AUTO_INCREMENT KEY, -> depName VARCHAR(20) NOT NULL UNIQUE -> )ENGINE=INNODB; Query OK, 0 rows affected (0.02 sec) mysql> INSERT department(depName) VALUES('教学部'), -> ('市场部'), -> ('运营部'), -> ('督导部'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> CREATE TABLE IF NOT EXISTS employee( -> id SMALLINT UNSIGNED AUTO_INCREMENT KEY, -> username VARCHAR(20) NOT NULL UNIQUE, -> depId TINYINT UNSIGNED, -> FOREIGN KEY(depId) REFERENCES department(id) ON DELETE SET NULL ON UPDATE SET NULL -> )ENGINE=INNODB; Query OK, 0 rows affected (0.02 sec) mysql> INSERT employee(username,depId) VALUES('king',1), -> ('queen',2), -> ('张三',3), -> ('李四',4), -> ('王五',1); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0
然后看一下表信息：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> SHOW CREATE TABLE employee; + | Table | Create Table | + | employee | CREATE TABLE `employee` ( `id` smallint (5 ) unsigned NOT NULL AUTO_INCREMENT, `username` varchar (20 ) NOT NULL , `depId` tinyint(3 ) unsigned DEFAULT NULL , PRIMARY KEY (`id` ), UNIQUE KEY `username` (`username` ), KEY `depId` (`depId` ), CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`depId` ) REFERENCES `department` (`id` ) ON DELETE SET NULL ON UPDATE SET NULL ) ENGINE =InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET =utf8 | + 1 row in set (0.00 sec)
然后我们分别执行删除和更新操作，并查看员工信息：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 mysql> DELETE FROM department WHERE id=1; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM employee; + | id | username | depId | + | 1 | king | NULL | | 2 | queen | 2 | | 3 | 张三 | 3 | | 4 | 李四 | 4 | | 5 | 王五 | NULL | + 5 rows in set (0.00 sec) mysql> UPDATE department SET id =id +10 ; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> SELECT * FROM employee; + | id | username | depId | + | 1 | king | NULL | | 2 | queen | NULL | | 3 | 张三 | NULL | | 4 | 李四 | NULL | | 5 | 王五 | NULL | + 5 rows in set (0.00 sec)
可以看到我们的子表信息都会自动变为NULL；
5、联合查询
区别是UNION去掉相同记录，UNION ALL 是简单的合并到一起,例如我们查询employee和cms_user中的username并将结果放在一起:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 mysql> SELECT username FROM employee UNION SELECT username FROM cms_user; + | username | + | king | | queen | | 张三 | | 李四 | | 王五 | | blek | | john | | lily | | long | | ring | | rose | | sing | | test1 | | TEST2 | | 张三丰 | | 章子怡 | + 16 rows in set (0.00 sec) mysql> SELECT username FROM employee UNION ALL SELECT username FROM cms_user; + | username | + | king | | queen | | 张三 | | 李四 | | 王五 | | blek | | john | | lily | | long | | queen | | ring | | rose | | sing | | test1 | | TEST2 | | 张三 | | 张三丰 | | 章子怡 | + 18 rows in set (0.00 sec)
可以看到UNION中去掉了重复的记录，而UNION ALL中并没有。需要注意的是所查询的列要相同。