MySQL多表查询实训

实训1

实践需求1：查询张三当前借阅的图书信息，图书信息只需显示借阅人编号、借阅人名称、图书名称和借阅时间。

动手实践1：根据图书表book的borrower_id字段和用户表user的name字段将表book和表user进行关联，并查询出user表中name值为张三的记录，查询出的记录只返回user表的id、name字段的值，以及book表的 borrower、name、bookname、b.borrow_time字段的值，具体的SQL语句如下所示。

SELECT u.id,u.name borrower,b.name bookname,b.borrow_time FROM book b,user u
WHERE b.borrower_id=u.id AND u.name='张三';

实践需求2：查询价格比《西游记》的价格高的图书信息，图书信息只需显示图书名称和图书价格。

动手实践2：首先查询图书表book中name的值为西游记的图书价格，将该查询作为子查询；然后查询价格大于子查询结果的图书信息，查询出的图书信息只返回name、price字段的值，具体的SQL语句如下所示。

SELECT name,price FROM book
WHERE price>(SELECT price FROM book WHERE name='西游记');

实践需求3：查询高于平均价的图书信息。查询价格比所有图书的平均价格还低的图书信息，图书信息只需显示图书名称和图书价格。

动手实践3：首先查询图书表book中price字段的平均值，将该查询作为子查询；然后查询价格小于于子查询结果的图书信息，查询出的图书信息只返回name、price字段的值，具体的SQL语句如下所示。

 SELECT name,price FROM book
WHERE price<(SELECT AVG(price) FROM book);

实践需求4：根据图书状态查询同类状态的图书。查询图书状态和《三国演义》相同的图书信息，图书信息只需显示图书名称、图书价格和状态。

动手实践4：首先查询图书表book中三国演义state的值，将该查询作为子查询；然后查询book表中state值和子查询结果相同的图书信息，查询出的图书信息只返回name、price、state字段的值，具体的SQL语句如下所示。

SELECT name,price,state FROM book
WHERE state=(SELECT state FROM book WHERE name='三国演义');

实践需求5：查询已借阅的低于某价格的图书信息。查询已借阅图书中，价格比任意未借阅的图书价格还低的图书信息，图书信息只需显示图书名称、图书价格和状态。

动手实践5：首先查询图书表book中state等于0的图书价格，将该查询作为子查询；然后查询book表中state值等于1，并且price字段的值小于任意一个子查询结果的图书信息，查询出的图书信息只返回name、price、state字段的值，具体的SQL语句如下所示。

 SELECT name,price,state FROM book
WHERE state=1 AND price<ANY(SELECT price FROM book WHERE state=0);

实践需求6：查询价格比任意已借阅的图书价格还高的图书信息，图书信息只需显示图书名称、图书价格和状态。

动手实践6：首先查询图书表book中state等于1的图书价格，将该查询作为子查询；然后查询book表中price字段的值大于所有子查询结果的图书信息，查询出的图书信息只返回name、price、state字段的值，具体的SQL语句如下所示。

SELECT name,price,state FROM book
WHERE price>ALL(SELECT price FROM book WHERE state=1);

实践需求7：添加外键约束，对图书表中的借阅者编号添加外键约束，以保证数据的完整性。

动手实践7：在图书表book中borrower_id字段添加外键约束，创建的外键引用user表的id字段，具体的SQL语句如下所示。

ALTER TABLE book ADD CONSTRAINT FK_ID FOREIGN KEY(borrower_id)
 REFERENCES user (id);

本章主要对多表操作进行了详细讲解。首先对多表查询进行了介绍，其次讲解了子查询，然后讲解了外键约束；最后通过上机实践案例，加深读者对多表操作的熟练度。通过本章的学习，希望读者能够掌握多表操作的基本使用，为后续的学习打下坚实的基础。

mysql> select * from user;
+----+------+-------+
| id | name | state |
+----+------+-------+
|  1 | 张三 | 0     |
|  2 | 李四 | 0     |
+----+------+-------+
2 rows in set (0.00 sec)

mysql> select * from book\G;
*************************** 1. row ***************************
         id: 1
   bookname: 三国演义
      price: 62.10
upload_time: 2024-05-23 08:07:01
borrower_id: NULL
borrow_time: NULL
      state: 2
*************************** 2. row ***************************
         id: 2
   bookname: MySQL数据库入门
      price: 36.00
upload_time: 2024-05-23 08:07:01
borrower_id: 1
borrow_time: 2024-05-28 11:14:47
      state: 1
*************************** 3. row ***************************
         id: 3
   bookname: JavaWeb程序开发入门
      price: 44.10
upload_time: 2024-05-23 08:07:01
borrower_id: NULL
borrow_time: NULL
      state: 0
*************************** 4. row ***************************
         id: 4
   bookname: 西游记
      price: 53.10
upload_time: 2024-05-23 08:07:01
borrower_id: NULL
borrow_time: NULL
      state: 0
*************************** 5. row ***************************
         id: 5
   bookname: 红楼梦
      price: 29.70
upload_time: 2024-05-23 08:07:01
borrower_id: NULL
borrow_time: NULL
      state: 2
*************************** 6. row ***************************
         id: 6
   bookname: Java基础入门（第3版）
      price: 53.10
upload_time: 2024-05-23 08:07:01
borrower_id: NULL
borrow_time: NULL
      state: 0
*************************** 7. row ***************************
         id: 7
   bookname: 水浒传
      price: 66.00
upload_time: 2024-05-23 08:07:01
borrower_id: NULL
borrow_time: NULL
      state: 2
7 rows in set (0.00 sec)

ERROR:
No query specified

mysql> select u.id,u.name,b.borrower,b.bookname,b.borrow_time from book as b,use
r as u where b.borrower_id=u.id;
ERROR 1054 (42S22): Unknown column 'b.borrower' in 'field list'
mysql> select u.id,u.name,b.borrower_id,b.bookname,b.borrow_time from book as b,
user as u where b.borrower_id=u.id;
+----+------+-------------+-----------------+---------------------+
| id | name | borrower_id | bookname        | borrow_time         |
+----+------+-------------+-----------------+---------------------+
|  1 | 张三 |           1 | MySQL数据库入门 | 2024-05-28 11:14:47 |
+----+------+-------------+-----------------+---------------------+
1 row in set (0.02 sec)

mysql> select b.bookname,b.price from book as b where price>(select price from b
ook where name='西游记');
ERROR 1054 (42S22): Unknown column 'name' in 'where clause'
mysql> select b.bookname,b.price from book as b where price>(select price from b
ook where bookname='西游记');
+----------+-------+
| bookname | price |
+----------+-------+
| 三国演义 | 62.10 |
| 水浒传   | 66.00 |
+----------+-------+
2 rows in set (0.00 sec)

mysql> select avg(b.price) as avg,b.bookname,b.price,b.state from book as b wher
e b.price<(select avg(price) from book);
+-----------+-----------------+-------+-------+
| avg       | bookname        | price | state |
+-----------+-----------------+-------+-------+
| 36.600000 | MySQL数据库入门 | 36.00 | 1     |
+-----------+-----------------+-------+-------+
1 row in set (0.02 sec)
mysql> select b.bookname,b.price,b.state from book b where state=(select state f
rom book where bookname='三国演义');
+----------+-------+-------+
| bookname | price | state |
+----------+-------+-------+
| 三国演义 | 62.10 | 2     |
| 红楼梦   | 29.70 | 2     |
| 水浒传   | 66.00 | 2     |
+----------+-------+-------+
3 rows in set (0.00 sec)
mysql> select b.bookname,b.price,b.state from book b where price < any(select p
ice from book where state=1);
+----------+-------+-------+
| bookname | price | state |
+----------+-------+-------+
| 红楼梦   | 29.70 | 2     |
+----------+-------+-------+
1 row in set (0.02 sec)
mysql> alter table book add constraint fk_id foreign key(borrower_id) references
 user(id);
Query OK, 7 rows affected (0.30 sec)
Records: 7  Duplicates: 0  Warnings: 0
mysql> show create table book\G;
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bookname` varchar(20) NOT NULL,
  `price` decimal(6,2) NOT NULL,
  `upload_time` datetime NOT NULL,
  `borrower_id` int(11) DEFAULT NULL,
  `borrow_time` datetime DEFAULT NULL,
  `state` char(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`bookname`),
  UNIQUE KEY `bookname` (`bookname`),
  KEY `fk_id` (`borrower_id`),
  CONSTRAINT `fk_id` FOREIGN KEY (`borrower_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

ERROR:
No query specified

实训2

在数据库chapter05中建两张表

department

did	int(4),	Not null, primary key
dname	Varchar(36),

id	Int(4),	Not null,primary key
name	Varchar(36),
age	Int(2),
did	Int(4),	Not null

Employee

在department中插入数据(1,'网络部'),(2,'媒体部'),(3,'研发部'),(5,'人事部')
在employee中插入四条数据(1,'王红',20,1),(2,'李强',22,1),(3,'赵四',20,2),(4,'郝鹃',20,4),
使用交叉连接查询部门表和员工表中所有的数据
使用内连接查询两个表
查询王红所在部门有哪些员工

mysql>
mysql>
mysql> create schema chapter05;
Query OK, 1 row affected (0.00 sec)

mysql> use chapter05;
Database changed
mysql> create table department;
ERROR 1113 (42000): A table must have at least 1 column
mysql> create table department (did int(4) not null primary key,dname varchar(36
));
Query OK, 0 rows affected (0.07 sec)

mysql> desc department;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| did   | int(4)      | NO   | PRI | NULL    |       |
| dname | varchar(36) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>
mysql>
mysql> alter table department  convert to character set utf8mb4;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into department values (1,'网络部'),(2,'媒体部'),(3,'研发部'),(5,'
人事部')
    -> ;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

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

mysql> create schema employee (id int(4) not null primary key,
    -> name varchar(36),
    -> age int(2),
    -> did int(4) not null);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '(id i
nt(4) not null primary key,
name varchar(36),
age int(2),
did int(4) not nu' at line 1
mysql> create table employee (id int(4) not null primary key,
    -> name varchar(36),
    -> age int(2),
    -> did int(4) not null);
Query OK, 0 rows affected (0.04 sec)

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

mysql> insert into employee values (1,'王红',20,1),(2,'李强',22,1),(3,'赵四',20,
2),(4,'郝鹃',20,4);
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from employee cross join department on  employee;
ERROR 1054 (42S22): Unknown column 'employee' in 'on clause'
mysql> select * from employee cross join department;
+----+------+------+-----+-----+--------+
| id | name | age  | did | did | dname  |
+----+------+------+-----+-----+--------+
|  1 | 王红 |   20 |   1 |   1 | 网络部 |
|  2 | 李强 |   22 |   1 |   1 | 网络部 |
|  3 | 赵四 |   20 |   2 |   1 | 网络部 |
|  4 | 郝鹃 |   20 |   4 |   1 | 网络部 |
|  1 | 王红 |   20 |   1 |   2 | 媒体部 |
|  2 | 李强 |   22 |   1 |   2 | 媒体部 |
|  3 | 赵四 |   20 |   2 |   2 | 媒体部 |
|  4 | 郝鹃 |   20 |   4 |   2 | 媒体部 |
|  1 | 王红 |   20 |   1 |   3 | 研发部 |
|  2 | 李强 |   22 |   1 |   3 | 研发部 |
|  3 | 赵四 |   20 |   2 |   3 | 研发部 |
|  4 | 郝鹃 |   20 |   4 |   3 | 研发部 |
|  1 | 王红 |   20 |   1 |   5 | 人事部 |
|  2 | 李强 |   22 |   1 |   5 | 人事部 |
|  3 | 赵四 |   20 |   2 |   5 | 人事部 |
|  4 | 郝鹃 |   20 |   4 |   5 | 人事部 |
+----+------+------+-----+-----+--------+
16 rows in set (0.00 sec)

mysql> select * from employee inner join department on employee.did = department
.did;
+----+------+------+-----+-----+--------+
| id | name | age  | did | did | dname  |
+----+------+------+-----+-----+--------+
|  1 | 王红 |   20 |   1 |   1 | 网络部 |
|  2 | 李强 |   22 |   1 |   1 | 网络部 |
|  3 | 赵四 |   20 |   2 |   2 | 媒体部 |
+----+------+------+-----+-----+--------+
3 rows in set (0.00 sec)

mysql> select * from employee where did=(select * employee where name='王红');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'emplo
yee where name='王红')' at line 1
mysql> select * from employee where did=(select * from employee where name='王红
');
ERROR 1241 (21000): Operand should contain 1 column(s)
mysql> select * from employee where did=(select did from employee where name='王
红');
+----+------+------+-----+
| id | name | age  | did |
+----+------+------+-----+
|  1 | 王红 |   20 |   1 |
|  2 | 李强 |   22 |   1 |
+----+------+------+-----+
2 rows in set (0.00 sec)

实训3

1带in关键字的子查询

例1，查询存在年龄为20岁的员工的部门

例2，查询不存在年龄为20岁的员工的部门

2带exists关键字的子查询

例，查询employee表中是否存在年龄大于21岁的员工，如果存在，则查询department表中的所有记录。

3带any关键字的子查询

例，查询department表中did大于employee表中任一个did的部门

4带all关键字

例，查询department表中did大于employee表中所有did的部门

5带比较运算符的

例，使用带比较运算符的子查询，查询赵四是哪个部门的员工

mysql> select * from department inner join employee on employee.age=20;
+-----+--------+----+------+------+-----+
| did | dname  | id | name | age  | did |
+-----+--------+----+------+------+-----+
|   1 | 网络部 |  1 | 王红 |   20 |   1 |
|   2 | 媒体部 |  1 | 王红 |   20 |   1 |
|   3 | 研发部 |  1 | 王红 |   20 |   1 |
|   5 | 人事部 |  1 | 王红 |   20 |   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 |
+-----+--------+----+------+------+-----+
12 rows in set (0.00 sec)

mysql> select * from department inner join employee on employee.age=20;
+-----+--------+----+------+------+-----+
| did | dname  | id | name | age  | did |
+-----+--------+----+------+------+-----+
|   1 | 网络部 |  1 | 王红 |   20 |   1 |
|   2 | 媒体部 |  1 | 王红 |   20 |   1 |
|   3 | 研发部 |  1 | 王红 |   20 |   1 |
|   5 | 人事部 |  1 | 王红 |   20 |   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 |
+-----+--------+----+------+------+-----+
12 rows in set (0.00 sec)

mysql> select dname from department inner join employee on employee.age=20 and e
mployee.did=department.did;
+--------+
| dname  |
+--------+
| 网络部 |
| 媒体部 |
+--------+
2 rows in set (0.02 sec)

mysql> select * from department where did in (select did from employee where age
=20 );
+-----+--------+
| did | dname  |
+-----+--------+
|   1 | 网络部 |
|   2 | 媒体部 |
+-----+--------+
2 rows in set (0.02 sec)

mysql> select * from department where did not in (select did from employee where
 age=20 );
+-----+--------+
| did | dname  |
+-----+--------+
|   3 | 研发部 |
|   5 | 人事部 |
+-----+--------+
2 rows in set (0.01 sec)

mysql> select * from department where age exists (select * from employee where a
ge>21 );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'exist
s (select * from employee where age>21 )' at line 1
mysql> select * from department where  exists (select * from employee where age>
21 );
+-----+--------+
| did | dname  |
+-----+--------+
|   1 | 网络部 |
|   2 | 媒体部 |
|   3 | 研发部 |
|   5 | 人事部 |
+-----+--------+
4 rows in set (0.00 sec)

mysql> select * from department where not  exists (select * from employee where
age>21 );
Empty set (0.00 sec)

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

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

mysql> select * from department where  did = (select did from employee where nam
e = '赵四' );
+-----+--------+
| did | dname  |
+-----+--------+
|   2 | 媒体部 |
+-----+--------+
1 row in set (0.00 sec)

第5章多表操作.pptx

https://www.alipan.com/s/ncMMHnK36jr

菜单

分享

MySQL 多表查询实训

MySQL多表查询实训

实训1

实训2

实训3

评论

arpl编译下载速度过慢？修改两个文件拯救你的下载速度（转载）

用.bat批处理文件运行conda环境下的Python程序

nat打洞(p2p)

PHP 命名空间(namespace)详解

PHP|Python 循环与条件语句内变量作用域及global、nonlocal关键字解析

Linux 使用 ‘useradd’ 命令的 ‘-g’ 选项指定组时，新用户为何不在 /etc/group 中作为组成员显示？

MySQL 外键约束

Linux输入输出(错误)重定向，分界符，解释，实例，综合实例，实际应用 cat命令解释

mysql设置约束|删除约束

MySQL 索引详解