必备SQL语句

SQL程序语言有四种类型，对数据库的基本操作都属于这四类，它们分别为；数据定义语言(DDL)、数据查询语言（DQL）、数据操纵语言（DML）、数据控制语言（DCL）

类型分类

DDL

主要语法有

CREATE、ALTER、DROP和TRUNCATE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
)

ALTER TABLE table_name
ALTER COLUMN column_name datatype

DROP TABLE 表名称
DROP DATABASE 数据库名称
TRUNCATE TABLE 表名称

DQL

主要语法是

SELECT

1
SELECT 列名称 FROM 表名称

DML

主要语法有

INSERT、UPDATE、DELETE

1
2
3
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
DELETE FROM 表名称 WHERE 列名称 = 值

DCL

主要语法有

GRANT,REVOKE,COMMIT,ROLLBACK

1
2
3
4
5
6
7
8
9
10
11
12
13
GRANT 权限1, … , 权限n ON 数据库.对象  TO 用户名;

REVOKE 权限1, … , 权限n ON 数据库.对象 FORM 用户名;

BEGIN TRANSACTION  //事务开始
SQL1
SQL2
COMMIT   //事务提交

BEGIN TRANSACTION  //事务开始
SQL1
SQL2
 ROLLBACK   //事务回滚

而基于上面的语法可以有很多操作,练习基础表,可以建表敲敲代码试试看

1
2
3
4
5
6
7
8
9
CREATE TABLE depart(
	id int not null primary key auto_increment,
	title varchar(16) not NULL)DEFAULT CHARSET=utf8;
CREATE table info(
	id int not null primary key auto_increment,
	name varchar(20) not NULL,
	`email` varchar(20) NULL,
	age INT not NULL,
	`depart_id` int not NULL)DEFAULT CHARSET=utf8;
1
2
3
4
5
6
7
8
9
INSERT INTO `depart` VALUES (1, '开发');
INSERT INTO `depart` VALUES (2, '运营');
INSERT INTO `depart` VALUES (3, '销售');
INSERT INTO `info` VALUES (1, 'abc', '123@qq.com', 20, 1);
INSERT INTO `info` VALUES (2, 'bobo', '530@qq.com', 20, 1);
INSERT INTO `info` VALUES (3, 'ccc', '3321@163.com', 10, 1);
INSERT INTO `info` VALUES (4, 'abc1', 'asda@qq.com', 51, 2);
INSERT INTO `info` VALUES (5, '1b', 'asd@qq.com', 30, 3);
INSERT INTO `info` VALUES (6, 'cc', '3321@163.coms', 28, 1);

条件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
select * from info where id >1;
select * from info where id =1;
select * from info where id >=1;
select * from info where id !=1;
select * from info where id between 2 and 4; #2、4取得到
SELECT * from info where id>2 and id <4;	#只能取得到3
 
select * from  info where name ='abc' and age=20;
select * from info where (name='abc' or email="123@qq.com") and age =20;
select * from info where ( email="530@qq.com" or name='abc') and age =20;#返回两个

select * from info where id in(1,4,6);
select * from info where id not in (1,4,6);
select * from info where id in (select id from depart); -- 只返回id=1的
-- select * from info where depart_id in (select depart.id from depart where depart.id = 3);
select * from info where depart_id in (select id from depart where id = 3); -- 我真服了自己在这里纠结这么久 一直写的是id in 而不是depart_id in
select * from info where exists (select * from depart where id =3); --exists -- 用于判断，判断子查询有没有结果，有的话为TRUE 返回所有 没有则为False

select * from info where not exists (select * from depart where id=3); -- 结果为空集,就是没有结果

select * from (select * from info where id >3) as T where T.age >10;#T表示子查询语句，将临时查询的结果作为一张表

通配符

一般用于模糊查找

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- % 表示匹配任意字符
select * from info where name like  "%b%";
select * from info where name like  "%b";
select * from info where email LIKE "%@qq.com";
select * from info where name like "a%c";
select * from info where email like "3321%"


-- 一个_表示一个字符占位符
select * from info where email like "___@qq.com";
select * from info where email like "123_qq.co_";
select * from info where email like "_23_qq.co_";

select * from info where email like "%@__.com";

注意：数量少，数据量大的搜索

映射

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select * from info;

select id, name 			from info;
select id, name as NM 		from info;
select id, bane as MN,123	from info;

select max(id) from depart ;
select min(id) from depart ;
SELECT max(id) as mid ,min(id) as nid FROM depart;
select 
	id,
	name,
	666 as num,
	(select max(id) from depart) as mid,
	(select min(id) from depart) as nid, 
	age
from info;
1
2
3
4
5
6
7
8
# 注意效率很低

select 
	id,
	name,
	(select title from depart where depart.id=info.id )as x1,
	(select title from depart where depart.id=info.depart_id) as x2
	FROM info;,
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select 
	id,
	name,
	case depart_id when 1 then "第1部门" end v1,
	case depart_id when 1 then "第1部门" else "其他" end v2,
	case depart_id when 1 then "第1部门" when 2 then "第2部门" else "其他" end v3,
	case when age<18 then "未成年" end v4,
    case when age<18 then "少年" else "老少年" end v5,
    case when age<18 then "少年" when age<30 then "青年" else "大叔" end v6
from info;

/* 
UPDATE info SET age_level = 
    CASE 
        WHEN age BETWEEN 0 AND 10 THEN 1
        WHEN age BETWEEN 11 AND 20 THEN 2
        WHEN age BETWEEN 21 AND 30 THEN 3
        WHEN age BETWEEN 31 AND 40 THEN 4
        ELSE 5
    END ; 
*/

排序

1
2
3
4
5
6
7
8
9
10
select * from info order by age desc; -- 倒数
select * from info order by age asc; -- 顺序

select * from info order by id desc;
select * from info order by id asc;

select * from info order by age asc ,id desc; -- 优先按照age从小到大,如果age相同则按照id从大到小。
select * from info where id >2 order by age asc ,id desc;
select * from info where id >3 or name like "%qq.com" order by age asc ,id desc;

取部分

1
2
3
4
5
select * from info limit 5;									-- 获取前5条数据
select * from info order by id desc limit 3;				-- 先排序，再获取前3条数据
select * from info where id > 10 order by id desc limit 3;	-- 先排序，在获取前3条数据

select * from info limit 3 offset 2							-- 从位置2开始，向后获取前3条数据（初始索引为0）

分组

1
2
select 字段名 from 表名 group by 字段名;
select depart_id ,count(id) from info group by depart_id;  -- 分组依据的字段要放入查询结果中
1
select age,max(id),min(id),sum(id),count(id),avg(id) from info group by age;
1
2
3
select age,name from info group by age; -- 不建议 因为同一个年龄下有多个姓名数据，所以会报错
select * from info where id in (select max(id) from info group by age);
-- 先括号内获取每个年龄段最大的id号，然后查询出来
1
2
3
select age ,count(id) from info group by age having count(id) > 2; -- 聚合条件
SELECT age,count(id) FROM info WHERE id>1 GROUP BY age HAVING count(id)>0 ORDER BY age ASC;
-- 统计范围id>2且人数大于2的年龄，且结果以年龄升序
1
2
3
4
5
6
7
8
-- 到目前为止SQL顺序
	where 			--
	group by		-- 聚合条件
	having			--	
	order by		--
	limit			-- 
	-- 用了group by 想要再进行二次筛选应该使用having
	-- where 可以单独使用 having只能分组后使用
1
2
3
4
5
6
7
8
select age,count(id) from info where id >2 group by age having count(id)>1 order by age desc limit 1;
-要查询的表info
-条件 id>2
-根据age分组
-过滤数量小于等于1
-根据age降序排序
-获取第一条
SELECT age,count(id) FROM info WHERE id>1 GROUP BY age HAVING count(id)>0 ORDER BY age ASC limit 1;

连表

多个表可以连接起来查询

外连接

1
主表 left outer join 从表 on 主表.x =从表.id
1
2
select * from info left outer join depart on info.depart_id=depart.id; -- 向外表进行左连接，表示这个表为主表.连接条件为depart_id
select info.id,info.name,info.email,depart.title from info left outer join depart on info.depart_id=depart.id;
1
从表 right outer join  主表 on主表.字段=从表.字段
1
select * from depart right outer join  info on depart.id=info.depart_id;

主表在左从表在右

为了更加直接的查看效果，我们分别在depart表和info中额外插入一条数据。

1
2
insert into depart(title) values("安全");
insert into info(name,email,age,depart_id) values("蛮吉","sjs@kuiba.com",2012);

左外连接

右外连接

简写

1
select * from depart left join info on ...

内连接

有对应关系才显示，没有就忽略掉

1
2
select * from info  inner join depart on info.depart_id=depart.id;
-- 注意这里没有左右了
1
2
3
4
5
6
7
8
9
到目前位置的SQL执行顺序
select
from
join on
where -- (select from)
group by
having -- (和group by绑定)
order by
limit

联合

1
2
3
4
5
6
7
8
9
10
select id,title from depart
union
select id,name from info;

-- 列数需相同,就能连接。
-- 列数指的是查询结果为几列的列

select id,title from depart
union
select email,name from info;
1
2
3
4
5
select id from depart
union
select id from info;

-- 自动去重,如果列名相同
1
2
3
4
5
select id from depart
union all
select id from info;

-- 保留所有

表关系

  • 单表
  • 一对多
  • 多对多

外键约束

1
2
3
4
5
6
7
8
9
CREATE table info(
	id int not null primary key auto_increment,
	name varchar(20) not NULL,
	`email` varchar(20) NULL,
	age INT not NULL,
	`depart_id` int not NULL,
    constraint fk_info_depart foreign key (depart_id) references depart(id)
    
)DEFAULT CHARSET=utf8;

如果表已经建 好了

1
alter table info add constraint fk_info_depart foreign key info(depart_id) references depart(id);

删除外键

1
alter table info drop foreign key fk_info_depart;

外键条件
在我们使用外键的时候，应该遵循如下条件：

外键要存在，首先必须保证表的引擎是 InnoDB（默认的存储引擎），如果不是 InnoDB 存储引擎，那么外键可以创建成功，但没有约束作用；
外键字段的字段类型（列类型），必须与父表的主键类型完全一致；
每张表中的外键名称不能重复；
增加外键的字段，如果数据已经存在，那么要保证数据与父表中的主键对应。
如果外键约束模式选择SET NULL ，那么字段必须允许为NULL，否则出现Cannot add foreign key constraint。

在以后项目开发时，设计表结构及其关系的是一个非常重要的技能。一般项目开始开发的步骤：

  • 需求调研

  • 设计数据库表结构（根据需求）

  • 项目开发（写代码）

    大量的工作应该放在前2个步骤，前期的设计完成之后，后续的功能代码开发就比较简单了。

授权

之前我们无论是基于Python代码还是自带客户端去连接MySQL时，均使用的是root账户，拥有对MySQL数据库操作的所有权限。

如果有多个程序的数据库都放在同一个MySQL中，如果程序都用root账户就存在风险了。
这种情况怎么办呢？

在MySQL中支持创建账户，并给账户分配权限，例如：只拥有数据库A操作的权限、只拥有数据库B中某些表的权限、只拥有数据库B中某些表的读权限等。

用户管理

在MySQL的默认数据库mysql中的user表中存储着所有的账户信息(含账户、权限等)。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
mysql> use mysql
Database changed
mysql> SELECT user, authentication_string FROM user;
+------------------+------------------------------------------------------------------------+
| user             | authentication_string                                                  |
+------------------+------------------------------------------------------------------------+
| mysql.infoschema | $s$012$THISISACOMBINAcccccccccDPASSWORDcccccNEVasEaaaaUSED |
| mysql.session    | $s$012$THISISACOMBINAcccccccccDPASSWORDcccccNEVasEaaaaUSED |
| mysql.sys        | $s$012$THISISACOMBINAcccccccccDPASSWORDcccccNEVasEaaaaUSED |
| root             | $s$012$THISISACOMBINAcccccccccDPASSWORDcccccNEVasEaaaaUSED  |
+------------------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)

1
2
desc mysql.`user`
-- 查看权限

创建和删除用户

1
create user '用户名'@'连接者的IP地址' identified by '密码';
1
2
3
4
5
6
7
8
9
10
11
create user bobo@127.0.0.1 identified by 'root';
drop user bobo@127.0.0.1;

create user bobo1@'127.0.0.%' identified by 'root';
drop user bobo1@'127.0.0.%';

create user bobo2@'%' identified by 'root';
drop user bobo@'%';

create user 'bobo3'@'%' identified by 'root';
drop user 'bobo3'@'%';

修改用户

1
rename user '用户名'@'连接者ip地址' identified by '密码';
1
2
rename user bobo@127.0.0.1 to bobo@localhost;
rename user 'bobo'@'127.0.0.1' to 'bobo'@'localhost';

修改密码(mysql==5.7)

1
set password for '用户名'@'ip地址'=Password('新密码');
1
set password for 'bobo'@'127.0.0.1'=Password('123')

授权管理

创建好用户后就可以进行授权了

  • 授权

    1
    grant 权限 on 数据库.表 to '用户'@'ip地址'
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    grant all privileges on *.* TO 'bobo'@'localhost';				
    -- 用户bobo且在本地时拥有所有权限
    grant all privileges on django_test.* TO 'bobo'@'localhost';	
    -- 用户bobo且在本地时拥有django_test库所有权限
    grant all privileges on django_test.info to 'bobo'@'localhost';
    -- 用户bobo且在本地时拥有django_test库的info表所有权限

    grant select on django_test.info To 'bobo'@'localhost'; 		
    -- 用户bobo且在本地时拥有django_test库select权限
    grant select,insert on django_test.* to 'bobo'@'localhost'; 
    -- 用户bobo且在本地时拥有django_test库select,insert权限

    -- 注意flush privileges; 将数据读取到内存，从而立即生效

    • 对于权限

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      all privileges				除grant外的所有权限
      select						仅查权限
      select,insert				查和插入权限
      ...
      usage						无访问权限
      alter						使用alter table
      alter routine				使用alter procedure和drop procedure
      create						使用create table
      create routine				使用create procedure
      create temporary tables		使用create temporary tables
      create user					使用create user、drop user、rename user和revoke all privileges
      create view					使用create view
      delete						使用delete
      drop						使用drop table
      execute						使用ca11和存储过程
      file						使用select into outfile和load data infile
      grant option				使用grant和revoke
      index						使用index
      insert						使用insert
      lock tables					使用lock tab1e
      process						使用show ful1 processlist
      select						使用select
      show databases				使用show databases
      show view					使用show view
      update						使用update
      reload						使用f1ush
      shutdown					使用mysqladmin shutdown(关闭MySQL)
      super						使用change master、kill、logs、purge、master和set global。还允许mysqladmin调试登陆
      replication client			服务器位置的访问
      replication slave			由复制从属使用

    • 对数据库和表

      1
      2
      3
      4
      数据库名					数据库中的所有
      数据库.表名			       指定数据库中的某张表	
      数据库.存储过程名			 指定数据库中的存储过程			
      *。*

  • 查看授权

    1
    show grants for '用户'@'IP地址'
    1
    show grants for 'bobo'@'localhost';

  • 取消权限

    1
    revoke 权限 on数据库.表 from '用户'@'IP地址';
    1
    revoke all privileges on django_test.info.* from 'bobo'@'localhost';

一般情况下，在很多的正规公司，数据库都是由DBA来统一进行管理，DBA为每个项目的数据库创建用户，并赋予相关的权限。

数据库的导入和导出

根据上图创建 数据库&表结构 并录入数据（可以自行创造数据）

1
2
3
4
create database test default charset utf8 collate utf8_general_ci;

drop database test;
drop databases IF EXISTS test;#如果存在就删除

利用导入数据库命令:

  • 导入

    1
    mysql -u root -p test < 
    1
    mysql -uroot < /home/project/init-MySql.sql

  • 导出

    1
    2
    3
    4
    5
    6
    # 数据+结构
    mysqldump -u root -p test > backup.sql

    mysqldump -u root -p database_name >/home/project/init-MySql.sql
    # 结构
    mysqldump -u root -p -d test > backup.sql

练习题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
1.根据上图创建数据库&表结构并录入数据（可以自行创造数据）
2.创建用户test_root并赋予此数据库的所有权限。
3.查询姓“李"的老师的个数。
4.查询姓“张"的学生名单。
5.查询男生、女生的人数。
6.查询同名同姓学生名单，并统计同名人数。
7.查询“三年二班”的所有学生。
8.查询每个班级的班级名称、班级人数。
9.查询成绩小于60分的同学的学号、姓名、成绩、课程名称。
10.查询选修了"生物课"的所有学生ID、学生姓名、成绩。
11.查询选修了"生物课”且分数低于60的的所有学生ID、学生姓名、成绩。
12.查询所有同学的学号、姓名、选课数、总成绩。
13.查询各科被选修的学生数。
14.查询各科成绩的总分、最高分、最低分，显示：课程ID、课程名称、总分、最高分、最低分。
15.查询各科成绩的平均分，显示：课程ID、课程名称、平均分。
16.查询各科成绩的平均分，显示：课程ID、课程名称、平均分（按平均分从大到小排序）。
17.查询各科成绩的平均分和及格率，显示：课程ID、课程名称、平均分、及格率。
18.查询平均成绩大于60的所有学生的学号、平均成绩。
19.查询平均成绩大于85的所有学生的学号、平均成绩、姓名。
20.查询“三年二班”每个学生的学号、姓名、总成绩、平均成绩。
21.查询各个班级的班级名称、总成绩、平均成绩、及格率（按平均成绩从大到小排序）
22.查询学过“朱元璋”老师课的同学的学号、姓名。
23.查询没学过“朱元璋”老师课的同学的学号、姓名。
24.查询选修“朱棣”老师所授课程的学生中，成绩最高的学生姓名及其成绩（不考虑并列）。
25.查询选修“朱棣”老师所授课程的学生中，成绩最高的学生姓名及其成绩（考虑并列）
26.查询只选修了一门课程的全部学生的学号、姓名。
27.查询至少选修两门课程的学生学号、学生姓名、选修课程数量。
28.查询两门及以上不及格的同学的学号、学生姓名、选修课程数量。
29.查询选修了所有课程的学生的学号、姓名。
30.查询未选修所有课程的学生的学号、姓名。
31.查询所有学生都选修了的课程的课程号、课程名。
32.查询选修“生物°和“物理”课程的所有学生学号、姓名。
33.查询至少有一们课与学号为“1的学生所选的裸程相同的其他学生学号和姓名
34.查询与学号为“2”的同学选修的课程完全相同的具地学生学号和姓名。
35.查询生物”课程比”物理”课程成绩高的所有学生的学号：
36.查询每门课程成绩最好的前3名（不专虑成绩并列情况）。
37.查询每门课程成绩最好的前3名（考虑成绩并列情况）。
38.创建一个表sc,然后将score表中所有数据插入到sc表中。
39.向score表中插入一些记录，这些记录要求符合以下条件:
	- 学生ID为：没上过课程D为“2”课程的学生的学号：
    - 课程ID为：2
	- 成绩为：80
40.向score表中插人些记录，这些记录要求符合以下条件：
	- 学生ID为：学过课程ID为“5”课程的学生的学号。
	- 课程ID为：2。(我知道根据我的数据这个同学已经学历这个课程)
	- 成绩为：课程ID为3的最高分。

注意下面为答案

个人写的可能有些问题

1-10

1.根据上图创建数据库&表结构并录入数据（可以自行创造数据）

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
-- 创建数据库
create DATABASE `test`;
use `test`;
-- 班级表
create TABLE class(
 cid int not null auto_increment PRIMARY key,
 caption VARCHAR(12) not null);
insert into class values(1,"三年二班"),(2,"一年三班"),(3,"三年一班");

-- 教师表
CREATE TABLE teacher(tid int not null auto_increment PRIMARY KEY,tname VARCHAR(10) not null);
insert into teacher VALUES(1,'朱元璋'),(2,'朱棣'),(3,'于谦'),(4,'郭子兴'),(5,'姚广孝'),(6,'王守仁'),(7,'海瑞');

-- 学生表
CREATE TABLE student(
sid int not null auto_increment PRIMARY key,
sname VARCHAR(10) not null,
gender char(1) not null ,
class_id int not null);
INSERT INTO `student` VALUES (1, '常遇春', '男', 1);
INSERT INTO `student` VALUES (2, '蓝玉', '男', 1);
INSERT INTO `student` VALUES (3, '朱标', '男', 2);
INSERT INTO `student` VALUES (4, '秦良玉', '女', 2);
INSERT INTO `student` VALUES (5, '张居正', '男', 1);
INSERT INTO `student` VALUES (6, '宋濂', '男', 2);
INSERT INTO `student` VALUES (7, '朱厚照', '男', 2);
INSERT INTO `student` VALUES (8, '朱由检', '男', 3);
INSERT INTO `student` VALUES (9, '刘基', '男', 3);
INSERT INTO `student` VALUES (10, '唐寅', '男', 3);
INSERT INTO `student` VALUES (11, '朱允炆', '男', 3);
INSERT INTO `student` VALUES (12, '徐阶', '男', 2);
INSERT INTO `student` VALUES (13, '朱祐樘', '男', 3);
INSERT INTO `student` VALUES (14, '李如柏', '男', 1);
INSERT INTO `student` VALUES (15, '李成梁', '男', 1);
INSERT INTO `student` VALUES (16, '李如松', '男', 3);
INSERT INTO `student` VALUES (17, '徐霞客', '男', 1);
INSERT INTO `student` VALUES (18, '胡宗宪', '男', 1);
INSERT INTO `student` VALUES (19, '戚继光', '男', 3);
INSERT INTO `student` VALUES (20, '戚继光', '男', 2);
-- 课程表
CREATE TABLE course (cid int not null auto_increment PRIMARY key ,cname VARCHAR(10),teacher_id int not null);
insert into course values(1,"生物",1),(2,"体育",1),(3,"物理",2),(4, 'IT', 3),(5, '刑法', 5),(6, '语文', 7);

-- 成绩表
CREATE TABLE score (sid int not null PRIMARY key auto_increment ,student_id int not null ,course_id int not null,number int not null);
INSERT INTO `score` VALUES (1, 1, 1, 60);
INSERT INTO `score` VALUES (2, 1, 2, 59);
INSERT INTO `score` VALUES (3, 2, 2, 100);
INSERT INTO `score` VALUES (4, 3, 3, 54);
INSERT INTO `score` VALUES (5, 4, 4, 59);
INSERT INTO `score` VALUES (6, 5, 6, 86);
INSERT INTO `score` VALUES (7, 11, 1, 59);
INSERT INTO `score` VALUES (8, 8, 1, 100);
INSERT INTO `score` VALUES (9, 16, 1, 45);
INSERT INTO `score` VALUES (10, 12, 1, 66);
INSERT INTO `score` VALUES (11, 6, 1, 66);
INSERT INTO `score` VALUES (12, 6, 2, 67);
INSERT INTO `score` VALUES (13, 6, 3, 68);
INSERT INTO `score` VALUES (14, 6, 4, 69);
INSERT INTO `score` VALUES (15, 6, 5, 70);
INSERT INTO `score` VALUES (16, 6, 6, 71);
INSERT INTO `score` VALUES (17, 2, 1, 66);
INSERT INTO `score` VALUES (18, 3, 1, 66);
INSERT INTO `score` VALUES (19, 4, 1, 66);
INSERT INTO `score` VALUES (20, 19, 1, 99);
INSERT INTO `score` VALUES (21, 20, 1, 99);
INSERT INTO `score` VALUES (22, 5, 1, 66);

2.创建用户test_root并赋予此数据库的所有权限。

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
create user 'test_user'@'127.0.0.1' identified by 'root';
grant all privileges on test.* TO  'test_user'@'127.0.0.1';
flush privileges;

注意要是链接写的是127.0.0.1 实际上也是本地，但是还是得写127.0.0.1而不能写localhost 否则1045

3.查询姓“李"的老师的个数。

警告此处为个人本题答案仅供参考，非唯一答案
1
select count(tid) from teacher where tname like '李%';

4.查询姓“张"的学生名单。

警告此处为个人本题答案仅供参考，非唯一答案
1
SELECT * from student WHERE sname like "张%";

5.查询男生、女生的人数。

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
SELECT gender,count(1) from student GROUP BY gender ;-- count在这里只是一个聚合函数里面写1还是0都行

-- 横向统计
SELECT gender,COUNT(sid) as `人数` FROM student GROUP BY gender;
-- 竖向统计
SELECT
	* 
FROM
	( SELECT COUNT( sid ) AS `女` FROM student WHERE gender = '女' ) nv,
	( SELECT COUNT( sid ) AS `男` FROM student WHERE gender = '男' ) AS nan;

6.查询同名同姓学生名单，并统计同名人数。icon:hashtag

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- 返回同名同姓的姓名和人数
SELECT
	sname,
	count( 1 ) 
FROM
	student 
GROUP BY
	sname;-- 所有名字数量的统计
	
SELECT
	sname,
	count( 1 ) 
FROM
	student 
GROUP BY
	sname 
HAVING
	COUNT( 1 )> 1;
SELECT
	sname,
	COUNT( sid ) AS `人数` 
FROM
	student 
GROUP BY
	sname 
HAVING
	COUNT( sid )> 1;
	
-- 返回同名同姓的姓名和信息
SELECT
	student.* 
FROM
	student 
WHERE
	sname IN ( SELECT sname FROM student GROUP BY sname HAVING COUNT( sid )> 1 );
综合整的花活
1
2
3
4
5
6
7
8
9
-- 综合 整的花活
SELECT student.*, zi.`同名同姓人数`
FROM student
INNER JOIN (
    SELECT sname, COUNT(sid) AS `同名同姓人数`
    FROM student
    GROUP BY sname
    HAVING COUNT(sid) > 1
) AS zi ON student.sname = zi.sname;

7.查询“三年二班”的所有学生。

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- (1)
SELECT
	* 
FROM
	student 
WHERE
	class_id = ( SELECT cid FROM class WHERE caption = "三年二班" );
-- (2)
SELECT
	* 
FROM
	student
	LEFT JOIN class ON student.class_id = class.cid 
WHERE
	class.caption = '三年二班'
-- 显示class_id还是cid都行，重要是拿取到关键数据

8.查询每个班级的班级名称、班级人数。

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 显示班级id不显示班级名
SELECT class_id,COUNT(1) FROM student GROUP BY class_id;
-- 显示班级名
-- (1)
SELECT
	class.caption,
	COUNT( 1 ) AS `人数` 
FROM
	student
	LEFT JOIN class ON student.class_id = class.cid 
GROUP BY
	class.caption;
-- (2)
SELECT
	class.caption,
	count( sid ) AS `人数` 
FROM
	student,
	`class` 
WHERE
	class.cid = student.class_id 
GROUP BY
	student.class_id;

9.查询成绩小于60分的同学的学号、姓名、成绩、课程名称。

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- (1)
SELECT
	student.sid,
	student.sname,
	score.number,
	course.* 
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid
	LEFT JOIN course ON score.course_id = course.cid 
WHERE
	number < 60;
-- (2)
SELECT
	student.sname,
	zi.cname,
	zi.number,
	zi.student_id 
FROM
	student,
	( SELECT course.cname, score.* FROM score INNER JOIN course ON score.course_id = course.cid WHERE number < 60 ) AS zi 
WHERE
	student.sid = zi.student_id;

10.查询选修了"生物课"的所有学生ID、学生姓名、成绩。

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- (1)
SELECT
	student.sname,
	student.sid,
	score.number,
	course.cname 
FROM
	course,
	student,
	score 
WHERE
	student.sid = score.sid 
	AND course.cid = score.course_id 
	AND course.cname = '生物';
-- (2)
SELECT
	student.sid,
	student.sname,
	score.number 
FROM
	score
	LEFT JOIN course ON score.course_id = course.cid
	LEFT JOIN student ON score.student_id = student.sid 
WHERE
	course.cname = '生物';

11-20

11.查询选修了"生物课”且分数低于60的的所有学生ID、学生姓名、成绩。

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- (1)
SELECT
	student.sname,
	student.sid,
	score.number,
	course.cname 
FROM
	course,
	student,
	score 
WHERE
	student.sid = score.sid 
	AND course.cid = score.course_id 
	AND course.cname = '生物' 
	AND score.number < 60;
-- (2)
SELECT
	student.sname,
	student.sid,
	score.number,
	course.cname 
FROM
	score
	LEFT JOIN course ON score.course_id = course.cid
	LEFT JOIN student ON score.student_id = student.sid 
WHERE
	course.cname = '生物' 
	AND score.number < 60

12.查询所有同学的学号、姓名、选课数、总成绩。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- (1)
SELECT
	student.sid,
	student.sname,
	SUM( score.number ) AS total,
	count(student.class_id) as '选课数'
FROM
	student,
	score 
WHERE
	score.student_id = student.sid 
GROUP BY
	student.sid,
	student.sname;
-- (2)
SELECT
	student_id,
	student.sname,
	SUM( number ) AS '总分',
	COUNT( 1 ) AS '选课数量' 
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid 
GROUP BY
	student_id

13.查询各科被选修的学生数。

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- 核心
SELECT
	course_id,
	COUNT( 1 ) 
FROM
	score 
GROUP BY
	course_id;
-- (1)	
SELECT
	course.cname,
	count( score.sid ) AS `人数` 
FROM
	score,
	course 
WHERE
	score.course_id = course.cid 
GROUP BY
	score.course_id;
-- (2)
SELECT
	cname,
	count( 1 ) AS `人数` 
FROM
	score
	LEFT JOIN course ON score.course_id = course.cid 
GROUP BY
	course_id

14.查询各科成绩的总分、最高分、最低分，显示：课程ID、课程名称、总分、最高分、最低分。

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- (1)
SELECT
	course_id AS '课程ID',
	course.cname AS '课程名称',
	SUM( number ) AS '总分',
	MAX( number ) AS '最高分',
	MIN( number ) AS '最低分',
	AVG( number ) AS '平均分' 
FROM
	score
	LEFT JOIN course ON score.course_id = course.cid 
GROUP BY
	course_id 
ORDER BY
	AVG( number ) DESC;
-- (2)
SELECT
	course.cid AS '课程ID',
	course.cname AS '课程名称',
	sum( score.number ) AS '总分',
	max( score.number ) AS '最高分',
	min( score.number ) AS '最低分',
	avg( score.number ) AS '平均分',
	count( score.sid ) AS `人数` 
FROM
	score,
	course 
WHERE
	score.course_id = course.cid 
GROUP BY
	score.course_id 
ORDER BY
	avg( score.number ) DESC;

15.查询各科成绩的平均分，显示：课程ID、课程名称、平均分。

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- (1)
SELECT
	course_id AS '课程ID',
	course.cname AS '课程名称',
	SUM( number ) AS '总分',
	MAX( number ) AS '最高分',
	MIN( number ) AS '最低分',
	AVG( number ) AS '平均分' 
FROM
	score
	LEFT JOIN course ON score.course_id = course.cid 
GROUP BY
	course_id 
ORDER BY
	AVG( number ) DESC;
-- (2)
SELECT
	course.cid AS '课程ID',
	course.cname AS '课程名称',
	sum( score.number ) AS '总分',
	max( score.number ) AS '最高分',
	min( score.number ) AS '最低分',
	avg( score.number ) AS '平均分',
	count( score.sid ) AS `人数` 
FROM
	score,
	course 
WHERE
	score.course_id = course.cid 
GROUP BY
	score.course_id 
ORDER BY
	avg( score.number ) DESC;

16.查询各科成绩的平均分，显示：课程ID、课程名称、平均分（按平均分从大到小排序）。

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- (1)
SELECT
	course_id AS '课程ID',
	course.cname AS '课程名称',
	SUM( number ) AS '总分',
	MAX( number ) AS '最高分',
	MIN( number ) AS '最低分',
	AVG( number ) AS '平均分' 
FROM
	score
	LEFT JOIN course ON score.course_id = course.cid 
GROUP BY
	course_id 
ORDER BY
	AVG( number ) DESC;
-- (2)
SELECT
	course.cid AS '课程ID',
	course.cname AS '课程名称',
	sum( score.number ) AS '总分',
	max( score.number ) AS '最高分',
	min( score.number ) AS '最低分',
	avg( score.number ) AS '平均分',
	count( score.sid ) AS `人数` 
FROM
	score,
	course 
WHERE
	score.course_id = course.cid 
GROUP BY
	score.course_id 
ORDER BY
	avg( score.number ) DESC;

注14-16题写的都是满足所有的需求，即句子一样。看题目删去

17.查询各科成绩的平均分和及格率，显示：课程ID、课程名称、平均分、及格率。

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- (1)
SELECT
	course.cid AS '课程ID',
	course.cname AS '课程名称',
	sum( score.number ) AS '总分',
	max( score.number ) AS '最高分',
	min( score.number ) AS '最低分',
	avg( score.number ) AS '平均分',
	count( score.sid ) AS `人数`,
	count( CASE WHEN score.number >= 60 THEN 1 END ) / count( score.sid )* 100 AS '及格率(%)' 
FROM
	score,
	course 
WHERE
	score.course_id = course.cid 
GROUP BY
	score.course_id;
-- (2)
SELECT
	course_id AS '课程ID',
	course.cname AS '课程名称',
	AVG( number ) AS '平均分',
	SUM( CASE WHEN score.number >= 60 THEN 1 ELSE 0 END ) / COUNT( 1 ) * 100 AS '及格率(%)' 
FROM
	score
	LEFT JOIN course ON score.course_id = course.cid 
GROUP BY
	course_id;

18.查询平均成绩大于60的所有学生的学号、平均成绩。

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- (1)
SELECT
	student.sid AS '学生ID',
	avg( score.number ) AS '平均成绩' 
FROM
	student,
	score,
	course 
WHERE
	student.sid = score.student_id 
	AND course.cid = score.course_id 
GROUP BY
	score.student_id 
HAVING
	avg( score.number )> 60;
-- (2)
SELECT
	student_id AS '学生ID',
	AVG( number )  '平均成绩' 
FROM
	score 
GROUP BY
	student_id 
HAVING
	AVG( number )> 60

19.查询平均成绩大于85的所有学生的学号、平均成绩、姓名。

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
-- (核心)
SELECT
	student_id,
	AVG( number ) 
FROM
	score 
GROUP BY
	student_id 
HAVING
	AVG( number ) > 85;
-- (1)
SELECT
	student.sid,
	student.sname,
	avg( score.number ) AS '平均成绩' 
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid 
GROUP BY
	student_id 
HAVING
	AVG( number ) > 85;
-- (2)
SELECT
	student.sid,
	student.sname,
	avg( score.number ) AS '平均成绩' 
FROM
	student,
	score,
	course 
WHERE
	student.sid = score.student_id 
	AND course.cid = score.course_id 
GROUP BY
	score.student_id 
HAVING
	avg( score.number )> 85;

20.查询“三年二班”每个学生的学号、姓名、总成绩、平均成绩。

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- (1)
SELECT
	student.sid,
	student.sname,
	sum( score.number ) AS '总成绩',
	avg( score.number ) AS '平均' 
FROM
	class
	JOIN student ON class.cid = student.class_id
	JOIN score ON student.sid = score.student_id 
WHERE
	class.caption = '三年二班' 
GROUP BY
	score.student_id;
-- (2)
SELECT
	student_id,
	sname,
	sum( number ) AS '总成绩',
	avg( number ) AS '平均' 
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid
	LEFT JOIN class ON class.cid = student.class_id 
WHERE
	class.caption = '三年二班' 
GROUP BY
	score.student_id;

21-30

21.查询各个班级的班级名称、总成绩、平均成绩、及格率（按平均成绩从大到小排序）

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- (1)
SELECT
	class.caption AS '班级名称',
	sum( score.number ) AS '总成绩',
	avg( score.number ) AS '平均成绩',
	count( CASE WHEN score.number >= 60 THEN 1 END )/ count( score.sid )* 100 AS '及格率(%)' 
FROM
	student,
	score,
	class 
WHERE
	student.sid = score.student_id 
	AND student.class_id = class.cid 
GROUP BY
	class.caption 
ORDER BY
	avg( score.number ) DESC;
	
-- (2)
SELECT
	class.caption AS '班级名称',
	sum( number ) AS '总成绩',
	avg( number ) AS '平均成绩',
	SUM( CASE WHEN score.number >= 60 THEN 1 ELSE 0 END )/ count( 1 )* 100 AS '及格率(%)' 
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid
	LEFT JOIN class ON class.cid = student.class_id 
GROUP BY
	class.caption 
ORDER BY
	avg( number ) DESC;

22.查询学过“朱元璋”老师课的同学的学号、姓名。

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- (1)
SELECT
	student.sid,
	student.sname 
FROM
	teacher,
	student,
	score,
	course 
WHERE
	score.course_id = course.cid 
	AND teacher.tid = course.teacher_id 
	AND student.sid = score.student_id 
	AND teacher.tname = '朱元璋';

-- (2)
SELECT
	student.sid,
	student.sname 
FROM
	score LEFT JOIN student on score.student_id=student.sid
	LEFT JOIN course on score.course_id=course.cid
	LEFT JOIN teacher on course.teacher_id=teacher.tid
WHERE
	teacher.tname = '朱元璋';

23.查询没学过“朱元璋”老师课的同学的学号、姓名。

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- 有选课的
select student.sid,student.sname from teacher,student,score,course where score.course_id=course.cid and teacher.tid = course.teacher_id and student.sid=score.student_id and teacher.tname !='朱元璋' GROUP BY student.sid
-- 包含无选课的
UNION 
select student.sid ,student.sname from student where  student.sid   not in (SELECT score.student_id from score) ORDER BY sid

-- 好看一点(就是拼凑在一起)
SELECT
	student.sid,
	student.sname 
FROM
	teacher,
	student,
	score,
	course 
WHERE
	score.course_id = course.cid 
	AND teacher.tid = course.teacher_id 
	AND student.sid = score.student_id 
	AND teacher.tname != '朱元璋' 
GROUP BY
	student.sid UNION
SELECT
	student.sid,
	student.sname 
FROM
	student 
WHERE
	student.sid NOT IN ( SELECT score.student_id FROM score ) 
ORDER BY
	sid;

24.查询选修“朱棣”老师所授课程的学生中，成绩最高的学生姓名及其成绩（不考虑并列）。

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
-- (1)
SELECT
	student.sid,
	student.sname,
	score.number 
FROM
	teacher
	JOIN course ON teacher.tid = course.teacher_id
	JOIN score ON score.course_id = course.cid
	JOIN student ON student.sid = score.student_id 
WHERE
	teacher.tname = '朱棣' 
ORDER BY
	number DESC 
	LIMIT 1;
-- (2)
SELECT
	student.sid,
	student.sname,
	score.number 
FROM
	teacher,
	student,
	score,
	course 
WHERE
	score.course_id = course.cid 
	AND teacher.tid = course.teacher_id 
	AND student.sid = score.student_id 
	AND teacher.tname = '朱棣' 
ORDER BY
	score.number DESC 
	LIMIT 1;
-- (3)
SELECT
	student.sid,
	student.sname,
	score.number 
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid
	LEFT JOIN course ON score.course_id = course.cid
	LEFT JOIN teacher ON course.teacher_id = teacher.tid 
WHERE
	teacher.tname = '朱棣' 
ORDER BY
	score.number DESC 
	LIMIT 1;

25.查询选修“朱棣”老师所授课程的学生中，成绩最高的学生姓名及其成绩（考虑并列）

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- (1)
SELECT student.sid, student.sname, score.number 
FROM teacher, student, score, course 
WHERE score.course_id = course.cid 
  AND teacher.tid = course.teacher_id 
  AND student.sid = score.student_id 
  AND teacher.tname = '朱棣' 
  AND score.number = (
    SELECT MAX(score.number) -- 满足等于最高分就考虑了并列的情况
    FROM teacher, student, score, course 
    WHERE score.course_id = course.cid 
      AND teacher.tid = course.teacher_id 
      AND student.sid = score.student_id 
      AND teacher.tname = '朱棣'
  );
 
-- (2) 
SELECT
	student.sid,student.sname ,score.number
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid
	LEFT JOIN course ON score.course_id = course.cid
	LEFT JOIN teacher ON course.teacher_id = teacher.tid 
WHERE
	teacher.tname = '朱棣'  
	AND score.number =(
	SELECT
		max( number ) 
	FROM
		score
		LEFT JOIN course ON score.course_id = course.cid
		LEFT JOIN teacher ON course.teacher_id = teacher.tid 
	WHERE
	teacher.tname = '朱棣' 
	)

26.查询只选修了一门课程的全部学生的学号、姓名

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
SELECT
	student.sname,
	student.sid,
	count( score.sid ) AS '选修课程数量' 
FROM
	student
	JOIN score ON student.sid = score.student_id 
GROUP BY
	score.student_id 
HAVING
	count( score.sid )= 1;

27.查询至少选修两门课程的学生学号、学生姓名、选修课程数量。

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
SELECT
	student.sname,
	student.sid,
	count( score.sid ) AS '选修课程数量' 
FROM
	student
	JOIN score ON student.sid = score.student_id 
GROUP BY
	score.student_id 
HAVING
	count( score.sid )> 1;

28.查询两门及以上不及格的同学的学号、学生姓名、选修课程数量。

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
SELECT
	student.sid,
	student.sname,
	count( score.sid ) AS '选修课程数量' 
FROM
	student
	JOIN score ON student.sid = score.student_id 
GROUP BY
	score.student_id 
HAVING
	count( score.number <= 60 ) >1

29.查询选修了所有课程的学生的学号、姓名。

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
	student.sname,
	student.sid,
	count( score.sid ) AS '选课数量' 
FROM
	student
	JOIN score ON student.sid = score.student_id 
GROUP BY
	score.student_id 
HAVING
	count( score.sid ) =(
	SELECT
		count( course.cid ) 
	FROM
	course 
	);

30.查询未选修所有课程的学生的学号、姓名。

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
-- (1)
-- 选修了课程 连上没有选修课程的学生
SELECT
	student.sname,
	student.sid 
FROM
	student
	JOIN score ON student.sid = score.student_id 
GROUP BY
	score.student_id 
HAVING
	count( score.sid ) !=(
	SELECT
		count( course.cid ) 
	FROM
		course 
	) UNION
SELECT
	student.sname,
	student.sid 
FROM
	student 
WHERE
	student.sid IN ( SELECT score.student_id FROM score );
	-- UNION 会去重
	
-- (2)
SELECT
	student.sid,
	student.sname 
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid 
GROUP BY
	student_id 
HAVING
	COUNT( 1 ) !=(
	SELECT
		COUNT( 1 ) 
FROM
	course)

31-40

31.查询所有学生都选修了的课程的课程号、课程名。

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- 可能有些没有选的，排除
SELECT
	course.cid,
	course.cname 
FROM
	course
	JOIN score ON course.cid = score.course_id 
GROUP BY
	score.course_id 
HAVING
	count( score.sid ) = ( SELECT COUNT( 1 ) FROM ( SELECT student_id FROM score GROUP BY student_id ) AS subquery );-- having的另一边对应着选了选修课的人数

-- 范围为全部，不排除没选课的
-- (1)
SELECT
	course.cid,
	course.cname 
FROM
	course
	JOIN score ON course.cid = score.course_id 
GROUP BY
	score.course_id 
HAVING
	count( score.sid ) = ( SELECT count( sid ) FROM student );
-- (2)
SELECT
	course.cid,
	course.cname 
FROM
	score
	left JOIN course ON course.cid = score.course_id 
GROUP BY
	course_id
HAVING
	count(1) = ( SELECT count( 1 ) FROM student );

32.查询选修“生物°和“物理”课程的所有学生学号、姓名。

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
12
SELECT
	student.sid,
	student.sname,
	course.cname 
FROM
	course
	JOIN score ON course.cid = score.course_id
	JOIN student ON score.student_id = student.sid 
WHERE
	course.cname IN ( '生物', '物理' ) 
ORDER BY
	course.cname

33.查询至少有一们课与学号为“1的学生所选的裸程相同的其他学生学号和姓名

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- (1)
SELECT
	student.sid,
	student.sname,
	count( score.course_id ) '与学号为1的同学相同的选课数量' 
FROM
	student
	JOIN score ON student.sid = score.student_id 
WHERE
	score.course_id IN ( SELECT course_id FROM score WHERE student_id = 1 ) 
GROUP BY
	score.student_id 
HAVING
	score.student_id != 1;
	
-- (2)	
SELECT
	student.sid,
	student.sname,
	count( score.course_id ) '与学号为1的同学相同的选课数量' 
FROM
	score
	LEFT JOIN course ON score.course_id = course.cid
	LEFT JOIN student ON score.student_id = student.sid 
WHERE
	score.course_id IN ( SELECT course_id FROM score WHERE student_id = 1 ) 
	AND score.student_id != 1 
GROUP BY
	student_id 
HAVING
	count( 1 )>= 1;

34.查询与学号为“2”的同学选修的课程完全相同的具地学生学号和姓名。

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- (1)
SELECT
	score.student_id,
	student.sname 
FROM
	score
	JOIN student ON score.student_id = student.sid 
WHERE
	course_id IN ( SELECT course_id FROM score WHERE student_id = 2 ) 
GROUP BY
	student_id 
HAVING
	COUNT( DISTINCT course_id ) = ( SELECT COUNT( DISTINCT course_id ) FROM score WHERE student_id = 2 ) 
	AND student_id != 2;
	
-- (2)	
SELECT
	student.sid,
	student.sname 
FROM
	score
	LEFT JOIN course ON score.course_id = course.cid
	LEFT JOIN student ON score.student_id = student.sid 
WHERE
	score.course_id IN ( SELECT course_id FROM score WHERE student_id = 2 ) 
	AND score.student_id != 2 
GROUP BY
	student_id 
HAVING
	COUNT( 1 )=(
	SELECT
		COUNT( 1 ) 
	FROM
		score 
	WHERE
	student_id = 2 
	);

35.查询生物”课程比”物理”课程成绩高的所有学生的学号。

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- 没有选择目标课程不做考虑
SELECT
	s1.student_id 
FROM
	score s1 
WHERE
	(
	SELECT
		s2.number 
	FROM
		score s2 
	WHERE
		s1.student_id = s2.student_id 
		AND s1.course_id = 3 
		AND s2.course_id = 1 
		AND s2.number > s1.number 
	);
-- 只满足一个选课条件，但是给另一个假定值0或-1

-- (1)
SELECT
	student_id,
	max( CASE cname WHEN '生物' THEN number ELSE 0 END ) AS sw,
	max( CASE cname WHEN '物理' THEN number ELSE 0 END ) AS wl 
FROM
	score
	LEFT JOIN course ON score.course_id = course.cid 
WHERE
	cname IN ( '生物', '物理' ) 
GROUP BY
	student_id 
HAVING
	sw > wl;
-- (2)	
SELECT
	*,
	CASE cname WHEN '生物' THEN number ELSE -1 END  sw,
	CASE cname WHEN '物理' THEN number ELSE -1 END  wl 
FROM
	score
	LEFT JOIN course ON score.course_id = course.cid 
WHERE
	cname IN ( '生物', '物理' ) ;

36.*查询每门课程成绩最好的前3名（不专虑成绩并列情况）。

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
-- (1) 前三信息(并列也取前三个)
WITH cte AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY course_id ORDER BY number DESC) rn
  FROM score
)
SELECT course_id, student_id, number
FROM cte
WHERE rn <= 3
ORDER BY course_id, rn;

-- (2)前三成绩
SELECT
	cid,
	cname,
	(
	SELECT
		student.sname 
	FROM
		score
		LEFT JOIN student ON student.sid = score.student_id 
	WHERE
		course_id = course.cid 
	ORDER BY
		number DESC 
		LIMIT 1 OFFSET 0 
		) AS '第一名',(
	SELECT
		student.sname 
	FROM
		score
		LEFT JOIN student ON student.sid = score.student_id 
	WHERE
		course_id = course.cid 
	ORDER BY
		number DESC 
		LIMIT 1 OFFSET 1 
		) AS '第二名',(
	SELECT
		student.sname 
	FROM
		score
		LEFT JOIN student ON student.sid = score.student_id 
	WHERE
		course_id = course.cid 
	ORDER BY
		number DESC 
		LIMIT 1 OFFSET 2 
	) AS '第三名' 
FROM
	course;
-- (3)前三分数
SELECT
	cid,
	cname,(
	SELECT
		number 
	FROM
		score 
	WHERE
		course_id = course.cid 
	GROUP BY
		number 
	ORDER BY
		number DESC 
		LIMIT 1 OFFSET 0 
		) AS '最高分',(
	SELECT
		number 
	FROM
		score 
	WHERE
		course_id = course.cid 
	GROUP BY
		number 
	ORDER BY
		number DESC 
		LIMIT 1 OFFSET 1 
		) AS '第二高分',(
	SELECT
		number 
	FROM
		score 
	WHERE
		course_id = course.cid 
	GROUP BY
		number 
	ORDER BY
		number DESC 
		LIMIT 1 OFFSET 2 
	) AS '第三高分' 
FROM
	course;

37.*查询每门课程成绩最好的前3名（考虑成绩并列情况）。

警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
SELECT s1.course_id, s1.student_id, s1.number
FROM score s1
WHERE (
  SELECT COUNT( s2.number)
  FROM score s2
  WHERE s2.course_id = s1.course_id AND s2.number > s1.number
) < 3
ORDER BY s1.course_id, s1.number DESC;

-- 只显示没课前三，不显示学生信息	
SELECT
	cid,
	cname,(
	SELECT
		number 
	FROM
		score 
	WHERE
		course_id = course.cid 
	GROUP BY
		number 
	ORDER BY
		number DESC 
		LIMIT 1 OFFSET 0 
		) AS '最高分',(
	SELECT
		number 
	FROM
		score 
	WHERE
		course_id = course.cid 
	GROUP BY
		number 
	ORDER BY
		number DESC 
		LIMIT 1 OFFSET 1 
		) AS '第二高分',(
	SELECT
		number 
	FROM
		score 
	WHERE
		course_id = course.cid 
	GROUP BY
		number 
	ORDER BY
		number DESC 
		LIMIT 1 OFFSET 2 
	) AS '第三高分' 
FROM
	course;
	
-- 综合
SELECT
	score.*,
	( SELECT MAX( number ) FROM score WHERE course_id = score.course_id ) AS '最高分',
	(
	SELECT
		MAX( number ) 
	FROM
		score 
	WHERE
		course_id = score.course_id 
		AND number < ( SELECT MAX( number ) FROM score WHERE course_id = score.course_id ) 
	) AS '第二高分',
	(
	SELECT
		MAX( number ) 
	FROM
		score 
	WHERE
		course_id = score.course_id 
		AND number < ( SELECT MAX( number ) FROM score WHERE course_id = score.course_id ) 
		AND number < (
		SELECT
			MAX( number ) 
		FROM
			score 
		WHERE
			course_id = score.course_id 
			AND number < ( SELECT MAX( number ) FROM score WHERE course_id = score.course_id ) 
		) 
	) AS '第三高分' 
FROM
	score;

38.创建一个表sc,然后将score表中所有数据插入到sc表中。

警告此处为个人本题答案仅供参考，非唯一答案(step1)
1
2
3
4
5
6
7
8
CREATE TABLE sc(
    sid int not null PRIMARY key auto_increment ,
    student_id int not null ,
    course_id int not null,
    number int not null,
    CONSTRAINT `fk_sc_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
		CONSTRAINT `fk_sc_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) DEFAULT CHARSET=utf8;
警告此处为个人本题答案仅供参考，非唯一答案(step2)
1
2
3
insert into sc select * from score;

insert into sc(sid,student_id,course_id,number)   SELECT sid,student_id,course_id,number FROM score;

39.向score表中插入一些记录，这些记录要求符合以下条件:

- 学生ID为：没上过课程D为“2”课程的学生的学号：
- 课程ID为：2
- 成绩为：80
警告此处为个人本题答案仅供参考，非唯一答案(step1)
1
SELECT student.sid,2,80 FROM  student JOIN  score  on student.sid=score.student_id   GROUP BY score.student_id HAVING MAX(CASE WHEN score.course_id = 2 THEN score.number END) IS NULL

这里使用了MAX函数和CASE表达式来判断学生是否选了课程2。如果学生选了课程2，那么MAX函数会返回该课程的最高分数；如果学生没有选课程2，那么MAX函数会返回NULL。通过判断MAX函数的返回值是否为NULL，就可以筛选出没有选课程2的学生了。

警告此处为个人本题答案仅供参考，非唯一答案(step2)
1
insert into sc (student_id,course_id,number)  SELECT student.sid,2,80 FROM  student JOIN  score  on student.sid=score.student_id   GROUP BY score.student_id HAVING MAX(CASE WHEN score.course_id = 2 THEN score.number END) IS NULL

40.向score表中插人些记录，这些记录要求符合以下条件：icon:hashtag

- 学生ID为：学过课程ID为“5”课程的学生的学号。
- 课程ID为：2。(我知道根据我的数据这个同学已经学历这个课程)
- 成绩为：课程ID为3的最高分。
警告此处为个人本题答案仅供参考，非唯一答案
1
2
3
4
INSERT INTO score (student_id, course_id, number)
SELECT student_id, 2, (SELECT MAX(number) FROM score)
FROM score
WHERE course_id = 5;

总结

练习题做了挺久的，至于多久呢？我也不好意思说，以免你说我太菜了（扶额

好吧，最近可能要开启爆更模式咯。喜欢我的文章麻烦点个有用或者赞同或者收藏之类的呗(^_^)。有什么问题和疑问可以在评论区留盐

同样在知乎发布:

MySQL必备语句和授权🧠&四十道练习题📜(含答案🕶) - 知乎 (zhihu.com)

许可协议

本文采用 署名-非商业性使用-相同方式共享 4.0 国际 许可协议，转载请注明出处。

分享文章

快来参与讨论吧~

博客近期分类标签归档
笔记Vue学习笔记Django学习笔记MySQL学习笔记
社交友链留言板
更多关于本站GitHubGitee

本破站由 @BXZDYG 使用 Stellar 主题创建。
本博客部分素材来源于网络，如有侵权请联系1476341845@qq.com删除
本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议，转载请注明出处。

本"页面"访问 次 | 👀总访问 次 | 总访客 人
全部都是博主用心学编写的啊！不是ai啊 只要保留原作者姓名并在基于原作创作的新作品适用同类型的许可协议，即可基于非商业目的对原作重新编排、改编或者再创作。