复杂查询

3.复杂查询

本篇主要内容如下：

索引
连接查询
子查询联合查询
报表函数
分组查询
分组连接函数
索引最左匹配原则（自学）

索引

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 '%李%'

4.平均函数－AVG

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

案例：

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

5.边角函数－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 … ]

其中 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 的数据了，这个是非常重要的性质，即索引的最左匹配特性。

章节练习：

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查询出G1T07班学生的编号、姓名、班级名称、计算机成绩

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 student.studentid 编号,studentname 姓名,score 计算机成绩  from student inner join computer on student.studentid = computer.studentid;
select stu.studentid 学生编号,stu.studentname 学生姓名,com.score as 计算机成绩 from student as stu,computer as com where stu.studentid=com.studentid;
--2查询参加过考试的学生信息

select * from student where studentid in(select studentid from computer);
--3查询出学生的编号、姓名、所在班级名称、计算机成绩

select stu.studentid as 学生编号,stu.studentname as 姓名,cl.classname as 班级名称,com.score as 计算机成绩 from class as cl,student as stu,

computer as com where cl.classid=stu.classid and stu.studentid=com.studentid;
--4查询出年龄大于19岁的学生编号、姓名、计算机成绩

select stu.studentid as 学生编号,stu.studentname as 学生姓名,com.score as 计算机成绩 from student as stu,

computer as com where stu.studentid=com.studentid and stu.studentage>19;
--5查询出姓名中包含有c的学生编号、姓名、计算机成绩

select stu.studentid as 学生编号,stu.studentname as 学生姓名,com.score as 计算机成绩 from student as stu,

computer as com where stu.studentid=com.studentid and stu.studentname like '%c%';
--6查询出计算机成绩大于80分的学生编号、姓名、班级名称

select stu.studentid as 学生编号,stu.studentname as 学生姓名,com.score as 计算机成绩 from student as stu,

computer as com where stu.studentid=com.studentid and com.score>80;
--7查询出所有学生的信息和计算机成绩信息

select stu.studentid 编号,stu.studentname 姓名,stu.studentage 年龄,stu.studentsex 性别,stu.studentaddress 地址,stu.classid 班级编号,com.score 成绩

from student stu

left join computer as com on stu.studentid=com.studentid;
--8查询出每个班的学生的计算机成绩的平均分，最高分，最低分

select c.classname as 班级,avg(co.score) as 平均分,max(co.score) as 最高分,min(co.score) as 最低分 from class as c

left join student as s on c.classid=s.classid left join computer as co on s.studentid=co.studentid group by c.classname;
--9查询显示出班级的计算机平均分大于75的班级名称、平均分信息，并按照平均分降序显示

select c.classname as 班级名称,avg(co.score) as 平均分 from class as c inner join student as s on c.classid=s.classid

inner join computer as co on s.studentid=co.studentid group by c.classname having 平均分>75 order by 平均分 desc;
--10查询出和Jim住在同一个地方的学生的基本信息

select * from student where studentaddress=(select studentaddress from student where studentname='Jim');
--11查询出班级编号大于3的学生基本信息

select * from student where classid>3;
--12查询出班级编号大于3的学生的计算机平均分信息

select avg(co.score) as 平均分 from student as s,computer as co where co.studentid=s.studentid and s.classid>3;
--13查询出班级编号大于3的男生的学生信息

select * from student where classid>3 and studentsex='男';
--14查询男、女生的计算机平均成绩、最高分、最低分

select s.studentsex as 性别, avg(c.score) as 平均分,max(c.score) as 最高分,min(c.score) as 最低分 from student as s

inner join computer as c on s.studentid=c.studentid group by s.studentsex;
--15将参加过考试的学生的年龄更改为20

update student set studentage=20 where studentid in(select studentid from computer);
--16查询出每个班级的学生的平均分（查询的结果中包含平均分和班级名称）

select cl.classname as 班级名称, avg(co.score) as 平均分 from computer as co,class as cl,

student as s where cl.classid=s.classid and s.studentid=co.studentid group by s.classid;
--17删除姓名包含“c”字符的学生计算机成绩

delete from computer where studentid in (select studentid from student where studentname like '%c%');
--18查询出G1T07班学生的编号、姓名、班级名称、计算机成绩

select s.studentid as 编号,s.studentname as 姓名,c.classname as 班级,co.score as 成绩 from student as s,

class as c,computer as co where s.studentid=co.studentid and c.classid=s.classid and c.classname='G1T07';
--19查询出年龄在20-25之间的学生的编号、姓名、年龄、计算机成绩

select s.studentid as 编号,s.studentname as 姓名,s.studentage as 年龄,c.score as 成绩 from student as s

inner join computer as c on s.studentid=c.studentid where s.studentage between 20 and 25;
select s.studentid as 编号,s.studentname as 姓名,s.studentage as 年龄,c.score as 成绩 from student as s,

computer as c where s.studentid=c.studentid and s.studentage between 20 and 25;
--20查询出成绩最高的学生的编号、姓名、计算机成绩、所在班级名称

select s.studentid as 编号, s.studentname as 姓名,c.score as 成绩,cl.classname as 班级名称 from  student as s

inner join computer as c on s.studentid=c.studentid inner join class as cl on cl.classid=s.classid order by c.score desc limit 1;
--21查询统计出每个班的平均分、显示平均分超过70分的信息、并按照降序显示信息

select classname 班级 ,AVG(score) 平均分  from class

inner join student on class.classid = student.classid

inner join computer on computer.studentid = student.studentid  group by classname having 平均分>70 order by 平均分 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())

posted @ 2021-09-25 22:44 柠檬色的橘猫阅读(203) 评论(0) 收藏举报

刷新页面返回顶部

柠檬色的橘猫

复杂查询

3.复杂查询

注意是和'01'号同学课程完全相同但非学习课程数相同的,这里我用左连接解决这个问题

暂跳过update题目

这里先按照平均成绩排序，再按照及格百分数排序，题目有点奇怪

只查出来sid即可，后面懒得交student表

题目有歧义，这套题的质量感觉有点差

有问题的题目

不太严谨，但实务中应该没问题，如需严谨见12题思路

公告