MySQL基础（三）之复杂查询(文章末内含70道MySQL练习题)

最新推荐文章于 2024-04-07 23:56:42 发布

faramita_of_mine

最新推荐文章于 2024-04-07 23:56:42 发布

阅读量444

点赞数 2

分类专栏： MySQL 文章标签： mysql 全文检索数据库

本文链接：https://blog.csdn.net/faramita_of_mine/article/details/121145932

版权

MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

数据库之复杂查询

本篇主要内容如下：

一、索引

MySQL 索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高 MySQL 的检索速度。拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。

组合索引，即一个索引包含多个列。创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。注意：如果过多的使用索引将会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

索引在 mysql 中分为普通索引，唯一索引和全文索引

1.普通索引

CREATE INDEX indexName ON `users` (username(length))

如果是 CHAR，VARCHAR 类型，length 可以小于字段实际长度；如果是 BLOB 和 TEXT 类型，必须指定 length。

2.唯一索引：它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。

CREATE UNIQUE INDEX indexName ON mytable(username(length))

3.全文索引：针对大文本 TEXT 的内容查找

CREATE FULLTEXT INDEX indexName ON `users` (username)

由于全文索引需要对该字段进行分词，对中文的支持不是很好，所以全文索引在实际开发中不建议使用。在一些大文本的内容管理系统中一般使用 elasticsearch 完成全文检索。

使用全文索引的格式：  MATCH (columnName) AGAINST ('string')
eg:
SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('聪')

删除索引：

ALTER TABLE `users` DROP INDEX `indexName`

练习：

1.索引之所以能够极大加快查询速度，原因是数据库底层采用树形结构存储，而且在随着 MySQL 版本的升级树的类型也在发生变化，搜索相应博客了解在 MySQL 中使用的树型数据都有哪些优点以及各版本的优势。

二、连接查询

数据准备：

CREATE TABLE `client` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `tel` varchar(20) NOT NULL,
  `emp_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `emp_id_fk` (`emp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;
insert  into `client`(`id`,`name`,`tel`,`emp_id`) values (2,'王二','123',2);
insert  into `client`(`id`,`name`,`tel`,`emp_id`) values (4,'麻子','234',2);
insert  into `client`(`id`,`name`,`tel`,`emp_id`) values (5,'小明','345',1);
insert  into `client`(`id`,`name`,`tel`,`emp_id`) values (6,'小红','456',1);
insert  into `client`(`id`,`name`,`tel`,`emp_id`) values (7,'不知道找谁','789',NULL);

CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `pwd` varchar(50) NOT NULL,
  `birth` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
insert  into `emp`(`id`,`name`,`pwd`,`birth`) values (1,'张三','123456','2020-06-04');
insert  into `emp`(`id`,`name`,`pwd`,`birth`) values (2,'李四','13456','2020-06-04');
insert  into `emp`(`id`,`name`,`pwd`,`birth`) values (3,'晓峰','123','2020-06-05');

两表连接分为内连接和外连接：

内连接（inner join）：返回连接表中符合连接条件及查询条件的数据行。

外连接：分为

左外连接（left outer join）可以简写 left join
右外连接（right outer join）可以简写为 right join
与内连接不同的是，外连接不仅返回连接表中符合连接条件及查询条件的数据行，也返回左表（左外连接时）或右表（右外连接时）中仅符合查询条件但不符合连接条件的数据行。

from 表1 连接类型 表2 [on (连接条件)] [where (查询条件)]

内连接查询（重点）

SELECT e.id '员工编号',e.`name` '员工名字',c.`name` '客户名',c.`tel` '客户电话' FROM `emp` e ,`client` c WHERE e.`id`=c.`emp_id`;
SELECT e.id '员工编号',e.`name` '员工名字',c.`name` '客户名',c.`tel` '客户电话' FROM `emp` e INNER JOIN `client` c ON e.`id`=c.`emp_id`;
SELECT e.id '员工编号',e.`name` '员工名字',c.`name` '客户名',c.`tel` '客户电话' FROM `emp` e LEFT OUTER JOIN `client` c ON e.`id`=c.`emp_id`;

左外连接查询

使用 left outer join 关键字，在 on 子句中设定连接条件不仅包含符合连接条件的数据行，还包含左表全部数据，右表没连上的不显示。左连接时左表优先全部显示。

三、子查询联合查询

子查询也叫嵌套查询，是指在 select 子句或者 where 子句中又嵌入 select 查询语句。

SELECT * FROM `emp` WHERE id IN(SELECT emp_id FROM `client` WHERE id IN(2,4,5))

联合查询

合并两条查询语句的查询结果，去掉其中的重复数据行，然后返回没有重复数据行的查询结果。联合查询使用 union 关键字

SELECT * FROM `client` WHERE emp_id = 2 UNION SELECT * FROM `client` WHERE emp_id = 1;
SELECT `id`,`name` FROM `client` WHERE emp_id = 2 UNION SELECT `id`,`name` FROM `emp`;

注意：两条查询语句返回的结构和字段的数量必须相同，否则无法合并。

四、报表函数

报表函数也叫聚合函数，一般用于统计汇总数据库表信息。通常和分组函数配合使用，如果不使用分组函数则认为该表的全部数据为一个组。常用报表函数如下：

1.计数函数－COUNT，如果参数是字段名，则统计该字段不为空的记录数。

Select count(*)|count(列名) from tablename [WHERE where_definition]

案例：

SELECT COUNT(*) FROM `client`

2.求和函数－SUM

Select sum(列名){,sum(列名)…} from tablename [WHERE where_definition]

案例：

SELECT SUM(`chinese`),SUM(`english`),SUM(`math`) FROM `student` WHERE `name` LIKE '%李%'

3.平均函数－AVG

Select avg(列名){,avg(列名)…} from tablename [WHERE where_definition]

案例：

SELECT AVG(`chinese`),AVG(`english`),AVG(`math`) FROM `student` WHERE `name` LIKE '%李%'

4.边角函数－MAX/MIN

Select max(列名)　from tablename [WHERE where_definition]

案例：

SELECT MAX(`english`) FROM `client`;
SELECT MIN(`english`) FROM `client`;

五、分组查询

分组查询是按一个或多个字段进行分组，字段值相同的为一组进行分组统计，其语法格式为：

[select …] from … [where…] [ group by … [having… ]] [ order by … ][limit n]

其中 group by 子句指定按照哪些字段分组。having 子句设定分组查询条件。

数据准备：

CREATE TABLE `staff` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `dept` varchar(10) DEFAULT NULL,
  `salary` double DEFAULT NULL,
  `edlevel` int(11) DEFAULT NULL,
  `hiredate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (1,'张三','开发部',2000,3,'2020-02-10 15:19:59');
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (2,'李四','开发部',2500,3,'2019-07-16 15:20:01');
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (3,'王五','设计部',2600,5,'2019-09-25 15:20:03');
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (4,'王六','设计部',2300,4,'2020-06-05 15:20:05');
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (5,'马七','设计部',2100,4,'2019-11-11 15:20:07');
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (6,'赵八','销售部',3000,5,'2020-06-05 15:20:19');
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (7,'钱九','销售部',3000,7,'2020-06-05 15:20:21');
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (8,'孙十','销售部',3000,7,'2020-06-05 15:20:26');

案例：

1.列出每个部门最高薪水的结果
SELECT `dept`, MAX(`salary`) AS '最高薪资' FROM `staff` GROUP BY `dept`;

2.查询每个部门的总的薪水数
SELECT `dept`, SUM( `salary` ) AS '总薪水' FROM `staff` GROUP BY `dept`;

3.查询公司 2020 年入职的各个部门每个级别里的最高薪水
SELECT `dept`, `edlevel`, MAX( `salary` ) AS maximum FROM staff WHERE `hiredate` > '2020-01-01' GROUP BY `dept`, `edlevel`;

4.查询雇员数超过2个的部门的最高和最低薪水并按照部门升序查询
SELECT `dept`, MAX( `salary` ) AS maximum, MIN( `salary` ) AS minimum FROM `staff` GROUP BY `dept` HAVING COUNT(*) > 2 ORDER BY DEPT

5.查询雇员平均工资大于或等于 2300 的部门的最高和最低薪水并按照最高薪水排序
SELECT `dept`, MAX( `salary` ) AS maximum, MIN( `salary` ) AS minimum FROM `staff` GROUP BY `dept` HAVING AVG( `salary` ) >= 2300 ORDER BY MAX(`salary`)

六、分组连接函数

在 MySQL 中将分组相同的行数据合并起来使用 group_concat 函数，该函数语法如下：

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

案例：以 id 分组，把 name 字段的值打印在一行，逗号分隔(默认)

select id,group_concat(name) from aa group by id;

案例：以id分组，把name字段的值打印在一行，分号分隔

select id,group_concat(name separator ';') from aa group by id;

案例：以id分组，把去冗余的name字段的值打印在一行，并用逗号分隔

select id,group_concat(distinct name) from aa group by id;

案例：以id分组，把name字段的值打印在一行，逗号分隔，以name排倒序

select id,group_concat(name order by name desc) from aa group by id;

七、索引最左匹配原则（自）

创建测试表并建立了复合（联合）索引（a，b，c）

create table test(
    a int ,
    b int,
    c int,
    d int,
    key index_abc(a,b,c)
)engine=InnoDB default charset=utf8;

插入 10000 条数据

DROP PROCEDURE IF EXISTS proc_initData;
DELIMITER $
CREATE PROCEDURE proc_initData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=10000 DO
    INSERT INTO test(a,b,c,d) VALUES(i,i,i,i);
    SET i = i+1;
END WHILE;
END $
CALL proc_initData();

explain 指令查看下面SQL

explain select * from test where a<10 ;
explain select * from test where a<10 and b <10;
explain select * from test where a<10 and b <10 and c<10;

将 a，b出现顺序换一下，a，b，c 出现顺序换一下

explain select * from test where b<10 and a <10;
explain select * from test where b<10 and a <10 and c<10;

查了下资料发现：mysql 查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。所以，当然是我们能尽量的利用到索引时的查询顺序效率最高咯，所以 mysql 查询优化器会最终以这种顺序进行查询执行。

mysql> explain select * from test where b<10 and a <10;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test  | NULL       | range | index_abc     | index_abc | 5       | NULL |    9 |    33.33 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

为什么 b<10 and c <10，没有用到索引？而 a<10 and c <10用到了？

mysql> explain select * from test where b<10 and c <10;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10005 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

当 b+ 树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+ 数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+ 树会优先比较 name 来确定下一步的所搜方向，如果 name 相同再依次比较 age 和 sex，最后得到检索的数据；但当(20,F)这样的没有 name 的数据来的时候，b+ 树就不知道下一步该查哪个节点，因为建立搜索树的时候 name 就是第一个比较因子，必须要先根据 name 来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+ 树可以用 name 来指定搜索方向，但下一个字段 age 的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是 F 的数据了，这个是非常重要的性质，即索引的最左匹配特性。
章节练习：（第一题默写 20 分钟）

1.数据准备：

CREATE TABLE `class` (
    `classid` int(11) NOT NULL AUTO_INCREMENT,
    `classname` varchar(20) DEFAULT NULL,
    PRIMARY KEY (`classid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;
insert  into `class`(`classid`,`classname`) values (1,'G1T01');
insert  into `class`(`classid`,`classname`) values (2,'G1T02');
insert  into `class`(`classid`,`classname`) values (3,'G1T03');
insert  into `class`(`classid`,`classname`) values (4,'G1T04');
insert  into `class`(`classid`,`classname`) values (5,'G1T05');
insert  into `class`(`classid`,`classname`) values (6,'G1T06');
insert  into `class`(`classid`,`classname`) values (7,'G1T07');

CREATE TABLE `computer` (
    `studentid` varchar(20) DEFAULT NULL,
    `score` float DEFAULT NULL,
    KEY `studentid` (`studentid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert  into `computer`(`studentid`,`score`) values ('2010001',90);
insert  into `computer`(`studentid`,`score`) values ('2010002',80);
insert  into `computer`(`studentid`,`score`) values ('2010003',70);
insert  into `computer`(`studentid`,`score`) values ('2010004',60);
insert  into `computer`(`studentid`,`score`) values ('2010005',75);
insert  into `computer`(`studentid`,`score`) values ('2010006',85);
insert  into `computer`(`studentid`,`score`) values ('2010007',70);
insert  into `computer`(`studentid`,`score`) values ('2010008',60);
insert  into `computer`(`studentid`,`score`) values ('2010009',75);
insert  into `computer`(`studentid`,`score`) values ('2010010',85);

CREATE TABLE `student` (
    `studentid` varchar(20) NOT NULL,
    `studentname` varchar(20) DEFAULT NULL,
    `studentage` int(11) DEFAULT NULL,
    `studentsex` char(10) DEFAULT NULL,
    `studentaddress` varchar(50) DEFAULT NULL,
    `classid` int(11) DEFAULT NULL,
    PRIMARY KEY (`studentid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert  into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010001','Jack',21,'男','湖北襄樊',1);
insert  into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010002','Scott',22,'男','湖北武汉',2);
insert  into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010003','Lucy',23,'女','湖北武汉',3);
insert  into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010004','Alan',19,'女','湖北襄樊',4);
insert  into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010005','Bill',20,'男','湖北襄樊',5);
insert  into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010006','Bob',21,'男','湖北宜昌',6);
insert  into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010007','Colin',22,'女','湖北襄樊',6);
insert  into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010008','Fred',19,'男','湖北宜昌',5);
insert  into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010009','Hunk',19,'男','湖北武汉',4);
insert  into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010010','Jim',18,'男','湖北襄樊',3);

练习：

1查询出学生的编号，姓名，计算机成绩
2查询参加过考试的学生信息
3查询出学生的编号、姓名、所在班级名称、计算机成绩
4查询出年龄大于19岁的学生编号、姓名、计算机成绩
5查询出姓名中包含有c的学生编号、姓名、计算机成绩
6查询出计算机成绩大于80分的学生编号、姓名、班级名称
7查询出所有学生的信息和计算机成绩信息
8查询出每个班的学生的计算机成绩的平均分，最高分，最低分
9查询显示出班级的计算机平均分大于75的班级名称、平均分信息，并按照平均分降序显示
10查询出和Jim住在同一个地方的学生的基本信息        
11查询出班级编号大于3的学生基本信息
12查询出班级编号大于3的学生的计算机平均分信息    
13查询出班级编号大于3的男生的学生信息
14查询男、女生的计算机平均成绩、最高分、最低分
15将参加过考试的学生的年龄更改为20
16查询出每个班级的学生的平均分（查询的结果中包含平均分和班级名称）
17删除姓名包含“c”字符的学生计算机成绩
18查询出G1T06班学生的编号、姓名、班级名称、计算机成绩
19查询出年龄在20-25之间的学生的编号、姓名、年龄、计算机成绩
20查询出成绩最高的学生的编号、姓名、计算机成绩、所在班级名称
21查询统计出每个班的平均分、显示平均分超过70分的信息、并按照降序显示信息

2.练习

数据准备：

表结构预览

–学生表 Student(SId,Sname,Sage,Ssex) --SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

–课程表 Course(CId,Cname,TId) --CId 课程编号,Cname 课程名称,TId 教师编号

–教师表 Teacher(TId,Tname) --TId 教师编号,Tname 教师姓名

–成绩表 SC(SId,CId,score) --SId 学生编号,CId 课程编号,score 分数

CREATE TABLE `course` (
  `cid` varchar(10) DEFAULT NULL,
  `cname` varchar(10) DEFAULT NULL,
  `tid` varchar(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
insert  into `course`(`cid`,`cname`,`tid`) values ('01','语文','02');
insert  into `course`(`cid`,`cname`,`tid`) values ('02','数学','01');
insert  into `course`(`cid`,`cname`,`tid`) values ('03','英语','03');

CREATE TABLE `sc` (
  `sid` varchar(10) DEFAULT NULL,
  `cid` varchar(10) DEFAULT NULL,
  `score` decimal(18,1) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
insert  into `sc`(`sid`,`cid`,`score`) values ('01','01','80.0');
insert  into `sc`(`sid`,`cid`,`score`) values ('01','02','90.0');
insert  into `sc`(`sid`,`cid`,`score`) values ('01','03','99.0');
insert  into `sc`(`sid`,`cid`,`score`) values ('02','01','70.0');
insert  into `sc`(`sid`,`cid`,`score`) values ('02','02','60.0');
insert  into `sc`(`sid`,`cid`,`score`) values ('02','03','80.0');
insert  into `sc`(`sid`,`cid`,`score`) values ('03','01','80.0');
insert  into `sc`(`sid`,`cid`,`score`) values ('03','02','80.0');
insert  into `sc`(`sid`,`cid`,`score`) values ('03','03','80.0');
insert  into `sc`(`sid`,`cid`,`score`) values ('04','01','50.0');
insert  into `sc`(`sid`,`cid`,`score`) values ('04','02','30.0');
insert  into `sc`(`sid`,`cid`,`score`) values ('04','03','20.0');
insert  into `sc`(`sid`,`cid`,`score`) values ('05','01','76.0');
insert  into `sc`(`sid`,`cid`,`score`) values ('05','02','87.0');
insert  into `sc`(`sid`,`cid`,`score`) values ('06','01','31.0');
insert  into `sc`(`sid`,`cid`,`score`) values ('06','03','34.0');
insert  into `sc`(`sid`,`cid`,`score`) values ('07','02','89.0');
insert  into `sc`(`sid`,`cid`,`score`) values ('07','03','98.0');

CREATE TABLE `student` (
  `sid` varchar(10) DEFAULT NULL,
  `sname` varchar(10) DEFAULT NULL,
  `sage` datetime DEFAULT NULL,
  `ssex` varchar(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
insert  into `student`(`sid`,`sname`,`sage`,`ssex`) values ('01','赵雷','1990-01-01 00:00:00','男');
insert  into `student`(`sid`,`sname`,`sage`,`ssex`) values ('02','钱电','1990-12-21 00:00:00','男');
insert  into `student`(`sid`,`sname`,`sage`,`ssex`) values ('03','孙风','1990-05-20 00:00:00','男');
insert  into `student`(`sid`,`sname`,`sage`,`ssex`) values ('04','李云','1990-08-06 00:00:00','男');
insert  into `student`(`sid`,`sname`,`sage`,`ssex`) values ('05','周梅','1991-12-01 00:00:00','女');
insert  into `student`(`sid`,`sname`,`sage`,`ssex`) values ('06','吴兰','1992-03-01 00:00:00','女');
insert  into `student`(`sid`,`sname`,`sage`,`ssex`) values ('07','郑竹','1989-07-01 00:00:00','女');
insert  into `student`(`sid`,`sname`,`sage`,`ssex`) values ('08','王菊','1990-01-20 00:00:00','女');

CREATE TABLE `teacher` (
  `tid` varchar(10) DEFAULT NULL,
  `tname` varchar(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
insert  into `teacher`(`tid`,`tname`) values ('01','张三');
insert  into `teacher`(`tid`,`tname`) values ('02','李四');
insert  into `teacher`(`tid`,`tname`) values ('03','王五');

题目：

1、查询“01”课程比“02”课程成绩高的所有学生的学号；
2、查询平均成绩大于60分的同学的学号和平均成绩；
3、查询所有同学的学号、姓名、选课数、总成绩
4、查询姓“李”的老师的个数；
5、查询没学过“张三”老师课的同学的学号、姓名；
6、查询学过编号“01”并且也学过编号“02”课程的同学的学号、姓名；
7、查询学过“张三”老师所教的课的同学的学号、姓名；
8、查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名；
9、查询所有课程成绩小于60分的同学的学号、姓名；
10、查询没有学全所有课的同学的学号、姓名；
11、查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名；
12、查询和"01"号的同学学习的课程完全相同的其他同学的学号和姓名
13、把“SC”表中“张三”老师教的课的成绩都更改为此课程的平均成绩；
14、查询没学过"张三"老师讲授的任一门课程的学生姓名
15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
16、检索"01"课程分数小于60，按分数降序排列的学生信息
17、按平均成绩从高到低显示所有学生的平均成绩
18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
20、查询学生的总成绩并进行排名
21、查询不同老师所教不同课程平均分从高到低显示
22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
24、查询学生平均成绩及其名次
25、查询各科成绩前三名的记录
26、查询每门课程被选修的学生数
27、查询出只选修了一门课程的全部学生的学号和姓名
28、查询男生、女生人数
29、查询名字中含有"风"字的学生信息
30、查询同名同性学生名单，并统计同名人数
31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)
32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
37、查询不及格的课程，并按课程号从大到小排列
38、查询课程编号为"01"且课程成绩在60分以上的学生的学号和姓名；
40、查询选修“张三”老师所授课程的学生中，成绩最高的学生姓名及其成绩
42、查询每门功课成绩最好的前两名
43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
44、检索至少选修两门课程的学生学号
45、查询选修了全部课程的学生信息
46、查询各学生的年龄
47、查询本周过生日的学生
48、查询下周过生日的学生
49、查询本月过生日的学生
50、查询下月过生日的学生

参考答案1：

# 1查询出学生的编号，姓名，计算机成绩
select s.studentid, studentname, score
from student s
         left join computer c on s.studentid = c.studentid;
# 2查询参加过考试的学生信息
select *
from student s
         left join computer c on s.studentid = c.studentid
where score is not null;
# 3查询出学生的编号、姓名、所在班级名称、计算机成绩
select s.studentid, s.studentname, c2.classname, c.score
from student s
         left join computer c on s.studentid = c.studentid
         left join class c2 on s.classid = c2.classid;
# 4查询出年龄大于19岁的学生编号、姓名、计算机成绩
select s.studentid, studentname, score
from student s
         left join computer c on s.studentid = c.studentid
where s.studentage > 19;
# 5查询出姓名中包含有c的学生编号、姓名、计算机成绩
select s.studentid, studentname, score
from student s
         left join computer c on s.studentid = c.studentid
where s.studentname like '%c%';
# 6查询出计算机成绩大于80分的学生编号、姓名、班级名称
select distinct s.studentid, s.studentname, c2.classname
from student s
         left join computer c on s.studentid = c.studentid
         left join class c2 on s.classid = c2.classid
where c.score > 80;
# 7查询出所有学生的信息和计算机成绩信息
select *
from student s
         left join computer c on s.studentid = c.studentid;

# 8查询出每个班的学生的计算机成绩的平均分，最高分，最低分
select c.classname, avg(c2.score), max(c2.score), min(c2.score)
from student s
         left join class c on s.classid = c.classid
         left join computer c2 on s.studentid = c2.studentid
group by c.classname;

# 9查询出班级的计算机平均分大于75的班级名称、平均分信息，并按照平均分降序显示
select c.classname, avg(c2.score) a
from student s
         left join class c on s.classid = c.classid
         left join computer c2 on s.studentid = c2.studentid
group by c.classname
having a > 75
order by a desc;

# 10查询出和Jim住在同一个地方的学生的基本信息
select *
from student
where studentaddress in (select studentaddress from student where studentname = 'JIM');

# 11查询出班级编号大于3的学生基本信息
select *
from student s
         left join class c on s.classid = c.classid
where c.classid > 3;
# 12查询出班级编号大于3的学生的计算机平均分信息
select avg(c2.score)
from student s
         left join class c on s.classid = c.classid
         left join computer c2 on s.studentid = c2.studentid
where c.classid > 3;

# 查询班级id大于3的每个班的学生的计算机平均信息（where 优先）
select c.classid, c.classname, avg(c2.score)
from student s
         left join class c on s.classid = c.classid
         left join computer c2 on s.studentid = c2.studentid
where c.classid > 3
group by c.classid, c.classname;
# 查询班级id大于3的每个班的学生的计算机平均信息（having 警告）
select c.classid, c.classname, avg(c2.score)
from student s
         left join class c on s.classid = c.classid
         left join computer c2 on s.studentid = c2.studentid
group by c.classid, c.classname
having c.classid > 3;

# 13查询出班级编号大于3的男生的学生信息
select *
from student s
         left join class c on s.classid = c.classid
where c.classid > 3
  and s.studentsex = '男';
# 14查询男、女生的计算机平均成绩、最高分、最低分
select s.studentsex, avg(c.score), max(c.score), min(c.score)
from student s
         left join computer c on s.studentid = c.studentid
group by s.studentsex;
# 15将参加过考试的学生的年龄更改为20
update student
set studentage=20
where studentid in (select distinct studentid from computer);
# 16查询出每个班级的学生的平均分（查询的结果中包含平均分和班级名称）
select c.classid, c.classname, avg(c2.score)
from student s
         left join class c on s.classid = c.classid
         left join computer c2 on s.studentid = c2.studentid
group by c.classname, c.classid;
# 17删除姓名包含“c”字符的学生计算机成绩
delete
from computer
where studentid in (select studentid from student where studentname like '%c%');
# 18查询出G1T06班学生的编号、姓名、班级名称、计算机成绩
select s.studentid, s.studentname, c2.classname, c.score
from student s
         left join computer c on s.studentid = c.studentid
         left join class c2 on s.classid = c2.classid
where c2.classname = 'G1T06';
# 19查询出年龄在20-25之间的学生的编号、姓名、年龄、计算机成绩
select s.studentid, s.studentname, s.studentage, c.score
from student s
         left join computer c on s.studentid = c.studentid
where s.studentage >= 20
  and s.studentage <= 25;
# 20查询出成绩最高的学生的编号、姓名、计算机成绩、所在班级名称
select s.studentid, s.studentname, c2.classname, c.score
from student s
         left join computer c on s.studentid = c.studentid
         left join class c2 on s.classid = c2.classid
order by c.score desc
limit 1;
# 21查询统计出每个班的平均分、显示平均分超过70分的信息、并按照降序显示信息
select c2.classname, avg(c.score)
from student s
         left join computer c on s.studentid = c.studentid
         left join class c2 on s.classid = c2.classid
group by c2.classname
having avg(c.score) > 70
order by avg(c.score) desc

注意：limit 1 是 MySQL 专有语法，并不是 SQL 标准规范，在 Oralce 没有该用法。

参考答案2：

#1、查询“01”课程比“02”课程成绩高的所有学生的学号；
SELECT DISTINCT t1.sid AS sid
FROM (SELECT * FROM sc WHERE cid = '01') t1
         LEFT JOIN (SELECT * FROM sc WHERE cid = '02') t2 ON t1.sid = t2.sid
WHERE t1.score > t2.score;

#2、查询平均成绩大于60分的同学的学号和平均成绩；
SELECT sid, AVG(score)
FROM sc
GROUP BY sid
HAVING AVG(score > 60);

#3、查询所有同学的学号、姓名、选课数、总成绩
SELECT student.sid AS sid, sname, COUNT(DISTINCT cid) course_cnt, SUM(score) AS total_score
FROM student
         LEFT JOIN sc ON student.sid = sc.sid
GROUP BY sid, sname;

#4、查询姓“李”的老师的个数；
SELECT COUNT(DISTINCT tid) AS teacher_cnt
FROM teacher
WHERE tname LIKE '李%';

#5、查询没学过“张三”老师课的同学的学号、姓名；
SELECT sid, sname
FROM student
WHERE sid NOT IN (SELECT sc.sid
                  FROM teacher
                           LEFT JOIN course ON teacher.tid = course.tid
                           LEFT JOIN sc ON course.cid = sc.cid
                  WHERE teacher.tname = '张三');

#6、查询学过“01”并且也学过编号“02”课程的同学的学号、姓名；
SELECT t.sid AS sid, sname
FROM (SELECT sid, COUNT(IF(cid = '01', score, NULL)) AS count1, COUNT(IF(cid = '02', score, NULL)) AS count2
      FROM sc
      GROUP BY sid
      HAVING COUNT(IF(cid = '01', score, NULL)) > 0
         AND COUNT(IF(cid = '02', score, NULL)) > 0) t
         LEFT JOIN student ON t.sid = student.sid;

#7、查询学过“张三”老师所教的课的同学的学号、姓名；
SELECT student.sid, sname
FROM (SELECT DISTINCT cid
      FROM course
               LEFT JOIN teacher ON course.tid = teacher.tid
      WHERE teacher.tname = '张三') course
         LEFT JOIN sc ON course.cid = sc.cid
         LEFT JOIN student ON sc.sid = student.sid
GROUP BY student.sid, sname;

#8、查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名；
SELECT t1.sid, sname
FROM (SELECT DISTINCT t1.sid AS sid
      FROM (SELECT * FROM sc WHERE cid = '01') t1
               LEFT JOIN (SELECT * FROM sc WHERE cid = '02') t2 ON t1.sid = t2.sid
      WHERE t1.score > t2.score) t1
         LEFT JOIN student ON t1.sid = student.sid;

#9、查询所有课程成绩小于60分的同学的学号、姓名；
SELECT t1.sid, sname
FROM (SELECT sid, MAX(score) FROM sc GROUP BY sid HAVING MAX(score < 60)) t1
         LEFT JOIN student ON t1.sid = student.sid;

#10、查询没有学全所有课的同学的学号、姓名；
SELECT t1.sid, sname
FROM (SELECT COUNT(cid), sid FROM sc GROUP BY sid HAVING COUNT(cid) < (SELECT COUNT(DISTINCT cid) FROM course)) t1
         LEFT JOIN student ON t1.sid = student.sid;

#11、查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名；
SELECT DISTINCT sc.sid
FROM (SELECT cid FROM sc WHERE sid = '01') t1
         LEFT JOIN sc ON t1.cid = sc.cid;

#12、查询和"01"号的同学学习的课程完全相同的其他同学的学号和姓名
#注意是和'01'号同学课程完全相同但非学习课程数相同的,这里我用左连接解决这个问题
SELECT t1.sid, sname
FROM (SELECT sc.sid, COUNT(DISTINCT sc.cid)
      FROM (SELECT cid FROM sc WHERE sid = '01') t1
               LEFT JOIN sc ON t1.cid = sc.cid
      GROUP BY sc.sid
      HAVING COUNT(DISTINCT sc.cid) = (SELECT COUNT(DISTINCT cid) FROM sc WHERE sid = '01')) t1
         LEFT JOIN student ON t1.sid = student.sid
WHERE t1.sid != '01';

#13、把“SC”表中“张三”老师教的课的成绩都更改为此课程的平均成绩；
#暂跳过update题目
#14、查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT sname
FROM student
WHERE sid NOT IN (SELECT DISTINCT sid
                  FROM sc
                           LEFT JOIN course ON sc.cid = course.cid
                           LEFT JOIN teacher ON course.tid = teacher.tid
                  WHERE tname = '张三');

#15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT t1.sid, sname, avg_score
FROM (SELECT sid, COUNT(IF(score < 60, cid, NULL)), AVG(score) AS avg_score
      FROM sc
      GROUP BY sid
      HAVING COUNT(IF(score < 60, cid, NULL)) >= 2) t1
         LEFT JOIN student ON t1.sid = student.sid;

#16、检索"01"课程分数小于60，按分数降序排列的学生信息
SELECT sid, IF(cid = '01', score, 100)
FROM sc
WHERE IF(cid = '01', score, 100) < 60
ORDER BY IF(cid = '01', score, 100) DESC;

#17、按平均成绩从高到低显示所有学生的平均成绩
SELECT sid, AVG(score)
FROM sc
GROUP BY sid
ORDER BY AVG(score) DESC;

#18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率
SELECT sc.cid,
       cname,
       MAX(score)                                     max_score,
       MIN(score)                                     min_score,
       AVG(score)                                     avg_score,
       COUNT(IF(score >= 60, sid, NULL)) / COUNT(sid) pass_rate
FROM sc
         LEFT JOIN course ON sc.cid = course.cid
GROUP BY sc.cid;

#19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
#这里先按照平均成绩排序，再按照及格百分数排序，题目有点奇怪
SELECT cid, AVG(score) AS avg_score, COUNT(IF(score >= 60, sid, NULL)) / COUNT(sid) AS pass_rate
FROM sc
GROUP BY cid
ORDER BY avg_score, pass_rate DESC;

#20、查询学生的总成绩并进行排名
SELECT sid, SUM(score) AS sum_score
FROM sc
GROUP BY sid
ORDER BY sum_score DESC;

#21、查询不同老师所教不同课程平均分从高到低显示
SELECT tid, AVG(score) AS avg_score
FROM course
         LEFT JOIN sc ON course.cid = sc.cid
GROUP BY tid
ORDER BY avg_score DESC;

#22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
SELECT sid, rank_num, score, cid
FROM (SELECT rank() over (PARTITION BY cid ORDER BY score DESC) AS rank_num, sid, score, cid FROM sc) t
WHERE rank_num IN (2, 3);

#23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
SELECT sc.cid,
       cname,
       COUNT(IF(score BETWEEN 85 AND 100, sid, NULL)) / COUNT(sid),
       COUNT(IF(score BETWEEN 70 AND 85, sid, NULL)) / COUNT(sid),
       COUNT(IF(score BETWEEN 60 AND 70, sid, NULL)) / COUNT(sid),
       COUNT(IF(score BETWEEN 0 AND 60, sid, NULL)) / COUNT(sid)
FROM sc
         LEFT JOIN course ON sc.cid = course.cid
GROUP BY sc.cid, cname;

#24、查询学生平均成绩及其名次
SELECT sid, avg_score, rank() over (ORDER BY avg_score DESC)
FROM (SELECT sid, AVG(score) AS avg_score FROM sc GROUP BY sid) t;

#25、查询各科成绩前三名的记录
SELECT sid, cid, rank1
FROM (SELECT cid, sid, rank() over (PARTITION BY cid ORDER BY score DESC) AS rank1 FROM sc) t
WHERE rank1 <= 3;

#26、查询每门课程被选修的学生数
SELECT COUNT(sid), cid
FROM sc
GROUP BY cid;

#27、查询出只选修了一门课程的全部学生的学号和姓名
#只查出来sid即可，后面懒得交student表
SELECT sid
FROM sc
GROUP BY sid
HAVING COUNT(cid) = 1;

#28、查询男生、女生人数
SELECT ssex, COUNT(DISTINCT sid)
FROM student
GROUP BY ssex;

#29、查询名字中含有"风"字的学生信息
SELECT sid, sname
FROM student
WHERE sname LIKE '%风%';

#30、查询同名同性学生名单，并统计同名人数
#题目有歧义，这套题的质量感觉有点差
SELECT ssex, sname, COUNT(sid)
FROM student
GROUP BY ssex, sname
HAVING COUNT(sid) >= 2;

#31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)
SELECT sid, sname, sage
FROM student
WHERE YEAR(sage) = 1990;

#32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
SELECT cid, AVG(score) AS avg_score
FROM sc
GROUP BY cid
ORDER BY avg_score, cid DESC;

#37、查询不及格的课程，并按课程号从大到小排列
#有问题的题目
SELECT cid, sid, score
FROM sc
WHERE score < 60
ORDER BY cid DESC, sid;

#38、查询课程编号为"01"且课程成绩在60分以上的学生的学号和姓名；
SELECT sid, cid, score
FROM sc
WHERE cid = '01'
  AND score > 60;

#40、查询选修“张三”老师所授课程的学生中，成绩最高的学生姓名及其成绩
SELECT sc.sid, sname, cname, score
FROM sc
         LEFT JOIN course ON sc.cid = course.cid
         LEFT JOIN teacher ON course.tid = teacher.tid
         LEFT JOIN student ON sc.sid = student.sid
WHERE tname = '张三'
ORDER BY score DESC
LIMIT 1;

#42、查询每门功课成绩最好的前两名
SELECT cid, sid, rank1
FROM (SELECT cid, sid, rank() over (PARTITION BY cid ORDER BY score DESC) AS rank1 FROM sc) t
WHERE rank1 <= 2;

#43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT cid, COUNT(sid) AS cnt
FROM sc
GROUP BY cid
HAVING cnt >= 5
ORDER BY COUNT(sid) DESC, cid;

#44、检索至少选修两门课程的学生学号
SELECT sid, COUNT(cid)
FROM sc
GROUP BY sid
HAVING COUNT(cid) >= 2;

#45、查询选修了全部课程的学生信息
#不太严谨，但实务中应该没问题，如需严谨见12题思路
SELECT sid, COUNT(cid)
FROM sc
GROUP BY sid
HAVING COUNT(cid) = (SELECT COUNT(DISTINCT cid) FROM sc);

#46、查询各学生的年龄
SELECT sid, sname, YEAR(CURDATE()) - YEAR(sage) AS sage
FROM student 47、查询本周过生日的学生
SELECT sid, sname, sage
FROM student
WHERE WEEKOFYEAR(sage) = WEEKOFYEAR(CURDATE());

#48、查询下周过生日的学生
SELECT sid, sname, sage
FROM student
WHERE WEEKOFYEAR(sage) = WEEKOFYEAR(DATE_ADD(CURDATE(), INTERVAL 1 WEEK));

#49、查询本月过生日的学生
SELECT sid, sname, sage
FROM student
WHERE MONTH(sage) = MONTH(CURDATE());

#50、查询下月过生日的学生
SELECT sid, sname, sage
FROM student
WHERE MONTH(DATE_SUB(sage, INTERVAL 1 MONTH)) = MONTH(CURDATE());

faramita_of_mine

关注

2
点赞
踩
5

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL基础（三）之复杂查询(文章末内含70道MySQL练习题)

数据库之复杂查询一、索引二、连接查询三、子查询联合查询四、报表函数五、分组查询六、分组连接函数七、索引最左匹配原则（自）本篇主要内容如下：一、索引MySQL 索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高 MySQL 的检索速度。拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。创建索
复制链接

扫一扫