mysql多表操作基础

最新推荐文章于 2024-09-12 10:27:27 发布

原创最新推荐文章于 2024-09-12 10:27:27 发布 · 483 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

数据分析专栏收录该内容

24 篇文章

订阅专栏

本文介绍了SQL中外键的概念及如何在数据库表间建立联系，详细解析了不同类型的连接查询和子查询的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. 外键

外键是指应用另外的表的一列或者多列，被引用的列应该具有主键约束或唯一性约束，外键用于建立和加强两个表之间的联系。
建立数据库及数据表：

create database bs4;
use db4;
create table grade(
id int(4) not null primary key,
name varchar(36));
create table student(
sid int(4) not null primary key,
sname varchar(36),
gid int(4) not null);

此时student的gid字段表示学生的年级非空，即可表示student表的外键，两个表可以通过gid建立联系。
为student表添加外键：

alter table student add constraint FK_ID foreign key(gid) references grade(id);

其中FK_ID是外键名，使用desc查看表的结果：

mysql> desc grade;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | NO   | PRI | NULL    |       |
| name  | varchar(36) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sid   | int(4)      | NO   | PRI | NULL    |       |
| sname | varchar(36) | YES  |     | NULL    |       |
| gid   | int(4)      | NO   | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

删除外键约束：

alter table student drop foreign key FK_ID;

2.操作关联表

2.1添加数据

首先创建外键

alter table student add constraint FK_ID foreign key(gid) references grade(id);

此时，student与grade是多对一的关系。要添加数据首先要对主表grade进行添加数据，然后对从表student。主表：在数据库中建立的表格即Table，其中存在主键(primary key)用于与其它表相关联，并且作为在主表中的唯一性标识。从表：以主表的主键（primary key）值为外键 (Foreign Key)的表，可以通过外键与主表进行关联查询。从表与主表通过外键进行关联查询。
首先为grade表添加数据：

insert into grade values(1,'信管1'),(2,'信管2');

在主表班级为1，2的情况下，从表student的gid也只能为1，2，所以插入数据如下：

insert into student(sid,sname,gid) values(1,'王',1),(2,'张',2),(3,'李',1),(4,'赵',2);

2.2 删除数据

删除id=1,信管1班数据，只能选择先删除从表（student）中的数据，然后再删除主表数据。
语句：

delete from student where sname='王'；
delete from student where sname='李'；

然后删除主表grade中的数据：

delete from grade where id=1;

3. 链接查询

3.1交叉链接

新建表，更新数据
employee：

+----+------+------+-----+
| id | name | age  | did |
+----+------+------+-----+
|  1 | 王   |   20 |   1 |
|  2 | 张   |   22 |   1 |
|  3 | 李   |   20 |   2 |
|  4 | 赵   |   20 |   4 |
+----+------+------+-----+

department：

+-----+-----------+
| did | dname     |
+-----+-----------+
|   1 | 网络部    |
|   2 | 媒体部    |
|   3 | 研发部    |
|   5 | 人事部    |
+-----+-----------+

返回所有数据行的笛卡尔积，返回第一个表中符合查询的行数乘第二个表中符合查询条件的行数。
使用select * from [table1] cross join [table2]

insert into department values(1,'网络部'),(2,'媒体部'),(3,'研发部'),(5,'人事部');
insert into employee values(1,'王',20,1),(2,'张',22,1),(3,'李',20,2),(4,'赵',20,4);

查询结果如下：

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 (0.01 sec)

3.2内链接查询

常见的查询方式，使用比较运算符对两个表的数据进行比较，只有满足条件的记录才会出现在查询结果中。
语法select [查询字段] from [表1] join [表2] on [查询条件]
查询employee id和department id相同的数据。

select employee.did,employee.name,department.dname,department.did from department join employee on department.did = employee.did;
+-----+------+-----------+-----+
| did | name | dname     | did |
+-----+------+-----------+-----+
|   1 | 王   | 网络部    |   1 |
|   1 | 张   | 网络部    |   1 |
|   2 | 李   | 媒体部    |   2 |
+-----+------+-----------+-----+
3 rows in set (0.00 sec)

上述查询使用where语句也可以实现。查询结果一致。

select employee.did,employee.name,department.dname,department.did from department,employee where department.did = employee.did;

在查询时可能会涉及两个要查询的表是同一个表，比如，要查询王所在部门有哪些员工，只使用employee表即可完成查询。称之为自然链接查询，是内链接的一种，在物理上是同一个表，逻辑上分为两个表。

select p1.* from employee p1 join employee p2 on p1.did=p2.did where p2.name='王';

查询结果如下：

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 (0.00 sec)

3.3外链接查询

与内链接查询相似。left join：返回包括左表中所以数据和右表中符合条件的数据；right join：返回包括右表的所以数据和左表中符合条件的数据。
1，左链接查询

select * from department left join employee on department.did=employee.did;
+-----+-----------+------+------+------+------+
| did | dname     | id   | name | age  | did  |
+-----+-----------+------+------+------+------+
|   1 | 网络部    |    1 | 王   |   20 |    1 |
|   1 | 网络部    |    2 | 张   |   22 |    1 |
|   2 | 媒体部    |    3 | 李   |   20 |    2 |
|   3 | 研发部    | NULL | NULL | NULL | NULL |
|   5 | 人事部    | NULL | NULL | NULL | NULL |
+-----+-----------+------+------+------+------+
5 rows in set (0.00 sec)

2，又链接查询

select * from department right join employee on department.did=employee.did;
+------+-----------+----+------+------+-----+
| did  | dname     | id | name | age  | did |
+------+-----------+----+------+------+-----+
|    1 | 网络部    |  1 | 王   |   20 |   1 |
|    1 | 网络部    |  2 | 张   |   22 |   1 |
|    2 | 媒体部    |  3 | 李   |   20 |   2 |
| NULL | NULL      |  4 | 赵   |   20 |   4 |
+------+-----------+----+------+------+-----+
4 rows in set (0.00 sec)

4.子查询

查询结果作为外层查询的条件，通常使用in,exists,any,all操作符。

4.1 in

查询年龄存在22岁的部门

select * from department where did in(select did from employee where age=22);
+-----+-----------+
| did | dname     |
+-----+-----------+
|   1 | 网络部    |
+-----+-----------+
1 row in set (0.01 sec)

4.2 exists

不返回数据，只返回true或者false，当返回true时外层查询才执行。
本例，内层存在，执行外层。

select * from department where exists(select did from employee where age>20);
+-----+-----------+
| did | dname     |
+-----+-----------+
|   1 | 网络部    |
|   2 | 媒体部    |
|   3 | 研发部    |
|   5 | 人事部    |
+-----+-----------+
4 rows in set (0.00 sec)

4.3 any

只要满足内层子查询中的任意一个比较条件，就返回一个结果作为外层查询条件。
本例子由于employee存在did为1，所以返回department2，3，5

select * from department where did > any(select did from employee);
+-----+-----------+
| did | dname     |
+-----+-----------+
|   2 | 媒体部    |
|   3 | 研发部    |
|   5 | 人事部    |
+-----+-----------+
3 rows in set (0.00 sec)

4.4 all

与any相似，但是需要满足全部条件。

select * from department where did > all(select did from employee);
+-----+-----------+
| did | dname     |
+-----+-----------+
|   5 | 人事部    |
+-----+-----------+
1 row in set (0.00 sec)

4.5 带比较运算符的子查询

还可以使用：

select * from department where did=(select did from employee where name='王');
+-----+-----------+
| did | dname     |
+-----+-----------+
|   1 | 网络部    |
+-----+-----------+
1 row in set (0.00 sec)