多表操作

1.外键

1.1：什么是外键

 为了了解外键，我们先创两张表

mysql> USE  itcast;

Database changed

创建完成第一张表

mysql> CREATE TABLE class(id INT(11) PRIMARY KEY,name VARCHAR(20))ENGINE=INNODB;

Query OK, 0 rows affected

给第一张表添加数据

mysql> INSERT INTO class(id,name) VALUES(1913,"上午班"),(1923,"下午班");

Query OK, 2 rows affected

Records: 2  Duplicates: 0  Warnings: 0

创建第二张表

mysql> CREATE TABLE student(id INT PRIMARY KEY,name VARCHAR(20),class_id  INT(11))ENGINE=INNODB;

Query OK, 0 rows affected

给第二张表添加数据

mysql> INSERT INTO student(id,name,class_id) VALUES (191301,"张三",1913),(192301,"李四",1923),(191302,"王二",1913);

Query OK, 3 rows affected

Records: 3  Duplicates: 0  Warnings: 0

1.2:为表添加外键约束

为表添加外键格式如下：

 

 需要注意的地方如下几点：

MYSQL可以在建立外键时添加ON　DELETE 或ON　UPDATE　子句来告诉数据库，怎么避免垃圾数据的产生，具体格式如下:

 语句中的各参数的具体说明如下：

 

 1.3：删除外键约束

格式如下：

 2：操作关联表

2.1：关联关系

MYSQL中数据表的关联关系有三种，具体如下:

1:多对一

是最常见的一种关系，如,员工与部门之间的关系，一个部门可以有多个员工，而一个员工不能属于多个部门，也就是说部门表中的一行在员工表中可以有许多匹配行，但员工表中的一行在部门表中只能有一个匹配行。

2：多对多

3：一对一

 

 

 

3:添加数据

最常见的关联关系为多对一，接下来，为表class和student添加外键约束来建立两个表的关联关系。具体语句如下：

mysql> alter table student add constraint FK_ID foreign key(class_id) REFERENCES class(id);
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

3.1查看外键约束是否成功添加，查询结果如下：

mysql> SHOW CREATE TABLE student;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                   |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_ID` (`class_id`),
  CONSTRAINT `FK_ID` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

我们事先添加过数据了，使用可以不用添加数据，如果要添加数据的话，也可以选择添加几个数据

3.2.1查询1913班有哪些学生，首先要查询1923班id，根据id在student表中查询该班级有哪些学生，具体步骤如下：

mysql> SELECT id  FROM class WHERE id=1913;

+------+

| id   |

+------+

| 1913 |

+------+

1 row in set

3.2.2：在student表中，查询class_id=1913的学生，即为1913班的学生，具体语句如下：

mysql> SELECT name FROM student WHERE class_id=1913;
+------+
| name |
+------+
| 张三 |
| 王二 |
+------+
2 rows in set

3.3：删除数据

由于class表和student表之间具有关联关系，参照列被参照的值是不能被删除的，所以，在删除1913班的时候，一定要删除该班级的所有学生，然后再删除班级，具体步骤如下：

mysql> delete from student WHERE name="张三";
Query OK, 1 row affected

mysql> delete from student WHERE name="王二";
Query OK, 1 row affected

查询是否删除成功

mysql> SELECT name FROM student WHERE class_id=1913;
Empty set

 

在class表中，删除1913班，具体语句如下：

mysql> delete from class WHERE id=1913;
Query OK, 1 row affected

 

查看是否删除成功：

mysql> SELECT *FROM class;
+------+--------+
| id   | name   |
+------+--------+
| 1923 | 下午班 |
+------+--------+
1 row in set

如果不删除student表中数据，直接删除class表中1923班，看看会发生什么情况，具体语句如下：

mysql> delete from class WHERE id=1923;
1451 - Cannot delete or update a parent row: a foreign key constraint fails (`itcast`.`student`, CONSTRAINT `FK_ID` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))

4：连接查询

4.1：交叉连接

 

 交叉语句格式如下：

 接下来通过具体的案例演示如何实现交叉连接，首先创建两个表，department和emlpoyee，具体语句如下：

mysql> CREATE TABLE department( did int(4)NOT NULL PRIMARY KEY,dname VARCHAR(36));
Query OK, 0 rows affected

mysql> CREATE TABLE employee(id INT(4),name VARCHAR(36),age INT(2),did INT(4) NOT NULL);
Query OK, 0 rows affected

添加数据：

mysql> INSERT INTO department(did,dname)VALUES(1,'网络部'),(2,'媒体部'),(3,'研发部'),(5,'人事部');
Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO employee(id,name,age,did)VALUES(1,'王红',20,1),(2,'李强',22,1),(3,'赵四',20,2),(4,'郝娟',20,4);
Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

使用交叉查询部门表和员工表中的所有数据，具体语句和结果如下：

mysql> SELECT *FROM department CROSS JOIN employee;
+-----+--------+----+------+-----+-----+
| did | dname  | id | name | age | did |
+-----+--------+----+------+-----+-----+
|   1 | 网络部 |  1 | 王红 |  20 |   1 |
|   2 | 媒体部 |  1 | 王红 |  20 |   1 |
|   3 | 研发部 |  1 | 王红 |  20 |   1 |
|   5 | 人事部 |  1 | 王红 |  20 |   1 |
|   1 | 网络部 |  2 | 李强 |  22 |   1 |
|   2 | 媒体部 |  2 | 李强 |  22 |   1 |
|   3 | 研发部 |  2 | 李强 |  22 |   1 |
|   5 | 人事部 |  2 | 李强 |  22 |   1 |
|   1 | 网络部 |  3 | 赵四 |  20 |   2 |
|   2 | 媒体部 |  3 | 赵四 |  20 |   2 |
|   3 | 研发部 |  3 | 赵四 |  20 |   2 |
|   5 | 人事部 |  3 | 赵四 |  20 |   2 |
|   1 | 网络部 |  4 | 郝娟 |  20 |   4 |
|   2 | 媒体部 |  4 | 郝娟 |  20 |   4 |
|   3 | 研发部 |  4 | 郝娟 |  20 |   4 |
|   5 | 人事部 |  4 | 郝娟 |  20 |   4 |
+-----+--------+----+------+-----+-----+
16 rows in set

4.2：内连接

 内连接语法格式

 

mysql> SELECT employee.name,department.dname FROM department JOIN employee ON department.did=employee.did;
+------+--------+
| name | dname  |
+------+--------+
| 王红 | 网络部 |
| 李强 | 网络部 |
| 赵四 | 媒体部 |
+------+--------+
3 rows in set

 mysql> SELECT employee.name,department.dname FROM department,employee WHERE department.did=employee.did;
+------+--------+
| name | dname  |
+------+--------+
| 王红 | 网络部 |
| 李强 | 网络部 |
| 赵四 | 媒体部 |
+------+--------+
3 rows in set

 

 mysql> SELECT p1.* FROM employee p1 JOIN employee p2 ON p1.did=p2.did WHERE p2.name="王红";
+----+------+-----+-----+
| id | name | age | did |
+----+------+-----+-----+
|  1 | 王红 |  20 |   1 |
|  2 | 李强 |  22 |   1 |
+----+------+-----+-----+
2 rows in set

4.3:外连接

分为左连接和右连接

语法格式如下：

 

 

4.3.1：左连接

 

 mysql> SELECT department.did,department.dname,employee.name FROM department LEFT JOIN employee ON department.did=employee.did;
+-----+--------+------+
| did | dname  | name |
+-----+--------+------+
|   1 | 网络部 | 王红 |
|   1 | 网络部 | 李强 |
|   2 | 媒体部 | 赵四 |
|   3 | 研发部 | NULL |
|   5 | 人事部 | NULL |
+-----+--------+------+
5 rows in set

 4.3.2：右连接

 

 mysql> SELECT department.did,department.dname,employee.name FROM department RIGHT JOIN employee ON department.did=employee.did;
+------+--------+------+
| did  | dname  | name |
+------+--------+------+
|    1 | 网络部 | 王红 |
|    1 | 网络部 | 李强 |
|    2 | 媒体部 | 赵四 |
| NULL | NULL   | 郝娟 |
+------+--------+------+
4 rows in set

4.4：复合条件连接查询

 

 mysql> SELECT employee.name,employee.age,department.dname FROM department JOIN employee ON department.did=employee.did order by age;
+------+-----+--------+
| name | age | dname  |
+------+-----+--------+
| 王红 |  20 | 网络部 |
| 赵四 |  20 | 媒体部 |
| 李强 |  22 | 网络部 |
+------+-----+--------+
3 rows in set

5.１带IN关键字的子查询

 

 mysql> SELECT *FROM department WHERE did IN(SELECT did FROM employee WHERE age=20);
+-----+--------+
| did | dname  |
+-----+--------+
|   1 | 网络部 |
|   2 | 媒体部 |
+-----+--------+
2 rows in set

mysql> SELECT *FROM department WHERE did NOT IN(SELECT did FROM employee WHERE age=20);
+-----+--------+
| did | dname  |
+-----+--------+
|   3 | 研发部 |
|   5 | 人事部 |
+-----+--------+
2 rows in set

５.２：带EXISTS关键字的子查询

 

 mysql> SELECT *FROM department WHERE EXISTS(SELECT did FROM employee WHERE age>21);
+-----+--------+
| did | dname  |
+-----+--------+
|   1 | 网络部 |
|   2 | 媒体部 |
|   3 | 研发部 |
|   5 | 人事部 |
+-----+--------+
4 rows in set

 

 ５.３：带ANY关键字的子查询

 

mysql> SELECT *FROM department WHERE did>any(SELECT did FROM employee);
+-----+--------+
| did | dname  |
+-----+--------+
|   2 | 媒体部 |
|   3 | 研发部 |
|   5 | 人事部 |
+-----+--------+
3 rows in set

 5.4：带ALL关键字的子查询

 

 mysql> SELECT *FROM department WHERE did>all(SELECT did FROM employee);
+-----+--------+
| did | dname  |
+-----+--------+
|   5 | 人事部 |
+-----+--------+
1 row in set

5.5：带比较运算符的子查询

 

 mysql> SELECT *FROM department WHERE did=(SELECT did FROM employee where name="赵四");
+-----+--------+
| did | dname  |
+-----+--------+
|   2 | 媒体部 |
+-----+--------+
1 row in set