计算机导论的赋值语句,MYSQL经典45题笔记

网上搜的，各个版本都有一点，部分解法参考数据蛙公众号

Dataset

create database mysql45 charset = utf8;

use mysql45;

#建学生信息表student

create table student(

sno varchar(20) not null primary key, # 学号

sname varchar(20) not null, # 姓名

ssex varchar(20) not null, # 性别

sbirthday datetime, # 生日

class varchar(20) # 所在班级

);

#建立教师表teacher

create table teacher

(

tno varchar(20) not null primary key, # 教师编号

tname varchar(20) not null, # 教师姓名

tsex varchar(20) not null, # 教师性别

tbirthday datetime, # 生日

prof varchar(20), # 职称

depart varchar(20) not null # 所在部门

);

#建立课程表course

create table course

(

cno varchar(20) not null primary key, # 课程编号

cname varchar(20) not null, # 课程名称

tno varchar(20) not null, # 教师编号

foreign key(tno) references teacher(tno)

);

#建立成绩表score

create table score

(

sno varchar(20) not null, # 学号

foreign key(sno) references student(sno),

cno varchar(20) not null, # 课程编号

foreign key(cno) references course(cno),

degree decimal # 成绩

);

#添加学生信息

insert into student values('108','曾华','男','1977-09-01','95033');

insert into student values('105','匡明','男','1975-10-02','95031');

insert into student values('107','王丽','女','1976-01-23','95033');

insert into student values('101','李军','男','1976-02-20','95033');

insert into student values('109','王芳','女','1975-02-10','95031');

insert into student values('103','陆君','男','1974-06-03','95031');

#添加教师信息

insert into teacher values('804','李诚','男','1958-12-02','副教授','计算机系');

insert into teacher values('856','张旭','男','1969-03-12','讲师','电子工程系');

insert into teacher values('825','王萍','女','1972-05-05','助教','计算机系');

insert into teacher values('831','刘冰','女','1977-08-14','助教','电子工程系');

#添加课程信息

insert into course values('3-105','计算机导论','825');

insert into course values('3-245','操作系统','804');

insert into course values('6-166','数字电路','856');

insert into course values('9-888','高等数学','831');

#添加成绩信息

insert into score values('103','3-245','86');

insert into score values('105','3-245','75');

insert into score values('109','3-245','68');

insert into score values('103','3-105','92');

insert into score values('105','3-105','88');

insert into score values('109','3-105','76');

insert into score values('101','3-105','64');

insert into score values('107','3-105','91');

insert into score values('108','3-105','78');

insert into score values('103','6-166','85');

insert into score values('105','6-166','79');

insert into score values('109','6-166','81');

顺便总结一下数据库的基本操作

# 创建

CREATE DATABASE database_name CHARSET = utf8; # 我的习惯是创建的时候设置编码格式

CREATE TABLE table_name(column_name data_type);

# 使用数据库

USE database_name;

# 更改表

ALTER TABLE table_name MODIFY column_name data_type; # 更改字段数据类型

ALTER TABLE table_name ADD column_name data_type; # 增加一个字段

ALTER TABLE table_name (还有别的KEY WORDS...)

# 更新数据

UPDATE table_name SET column_name = value,... WHERE conditions; # 不加WHERE就是更改整个列

# 删除(别乱用)

DROP DATABASE database_name;

DROP TABLE table_name;

DELETE FROM table_name WHERE conditions; # 删除记录

# 插入

INSERT INTO table_name (column_names) VALUES(values);

# 查看

SHOW DATABASES;

SHOW TABLES;

DESC table_name; # 查看表结构

查询和学号为108、101的学生同年出生的所有学生的Sno、Sname和Sbirthday列

select sno, sname, sbirthday from student

where year(sbirthday) in (select year(sbirthday) from student where sno = 108 or sno = 101)

and sno != 108 AND sno != 101;

查询选修某课程的学生人数多于5人的教师姓名

select tname from teacher

join course on teacher.tno = course.tno

join score on course.cno = score.cno

group by score.cno

having count(*) > 5;

查询95033班和95031班全体学生的记录

select * from Student,Score,Course

where Student.Sno=Score.Sno and Score.Cno=Course.Cno and class = '95033' or '95031';

查询选修编号为“3-105”课程且成绩至少高于选修编号为“3-245”的学生的Cno、Sno和Degree,并按Degree从高到低次序排序

select cno, sno, degree from score

where cno = '3-105'

and degree > (select min(degree) from score where cno = '3-245')

order by degree desc;

查询所有教师和学生的name、sex和birthday

用UNION联合显示两次查询的结果

PS：union相当于是union all取DISTINCT的结果

select sname,ssex,sbirthday from student

union

select tname,tsex,tbirthday from teacher;

查询成绩比该课程平均成绩低的学生的成绩表

用自连接的方式体现“该课程”

select * from score a

where degree < (select avg(degree) from score b where a.cno = b.cno);

数据库引擎会采取逐条取主查询记录与子查询实施比对(where degree < ...)，子查询中，取主查询的字段a.cno作为过滤，输出不同课程的avg(degree)；比对结束后确定是否检出该条记录，最后汇总各次检索的结果输出整个记录集

查询Student表中每个学生的姓名和年龄

select sname,year(now())-year(sbirthday) from student;

以班号和年龄从大到小的顺序查询Student表中的全部记录

select * from student order by class desc, sbirthday asc;

查询参加3-105和3-245两门课程的学生的成绩(左右表的形式)

# 条件放在where后面和on后面的逻辑是不同的

select * from score a

join score b on a.sno = b.sno

where a.cno = '3-105' and b.cno = '3-245';

----

select * from (select * from score where cno = '3-105') a

join score b on a.sno = b.sno and b.cno = '3-245';

查询不参加6-166课程，但是参加3-105课程的学生(分步)

select * from

(select sno from score where sno not in (select sno from score where cno = '6-166')) a

join score b on a.sno = b.sno and b.cno = '3-105'; # 会出现两个sno列

----

select * from score

where sno not in (select sno from score where cno = '6-166')

and cno = '3-105';

查询和109号同学参加的课程至少有一门相同的同学信息

select distinct a.* from student a

join score b on a.sno = b.sno

where b.cno in (select cno from score where sno = '109')

and b.sno != '109';

查询和109号同学课程安排完全相同的同学信息

查询逻辑：找出109同学参加的课程，求补集；限制和109同学参加的课程数量相同

select sno from score

where sno not in (select sno from score where cno not in (select cno from score where sno = '109'))

and sno != '109'

group by sno

having count(*) = (select count(*) from score where sno = '109');

查询没有参加过李诚老师课的学生

思路：选出参加过李诚老师课的学生的sno，用NOT IN取没参加过的学生(主体是学生而不是课程)

select distinct sname from student

join score on student.sno = score.sno

where score.sno not in(

select distinct score.sno from score

join course on score.cno = course.cno

join teacher on course.tno = teacher.tno

where tname = '李诚');

查询两门课及以上都不到80分的学生信息及其平均成绩

select a.*, avg(b.degree) from student a

join score b on a.sno = b.sno

where a.sno in (

select sno from score

where degree <80

group by sno

having count(*) > 1)

group by a.sno;

按平均成绩降序显示所有学生的成绩和平均成绩

思路：先对score表求聚合average，再把平均成绩贴到score表上进行排序

select a.*, b.avg_score from score a

join (

select sno,avg(degree) as avg_score from score

group by sno) b on a.sno = b.sno

order by b.avg_score desc;

查询各课程最高分、最低分、平均分、优良率

及格率/好评率同理

条件计数的思路：满足条件的记为1，不满足则记为0，最后求和，得到共有多少条记录满足指定条件

select

cno,

max(degree) as 最高分,

min(degree) as 最低分,

avg(degree) as 平均成绩,

sum(case when degree >= 80 then 1 else 0 end) / count(1) as 优良率

from score

group by cno;

按成绩进行排序，并显示排名， 成绩重复时保留名次空缺

思路：对score表按degree字段降序排序，并依次赋予排名

select

sno,

cno,

degree,

case when @score = degree then @rank # 方便理解，设置为和上一条记录相同的rank值

else @rank:=@rank+1 end as 排名,

@score:=degree

from score, (select @rank:=0, @score:=null) as t

order by degree desc;

知识点：涉及到mysql中的自定义变量@变量名，以及赋值符号:=；关联表时，若不指定关联的键，则会生成笛卡尔集，即score表的每一条记录后都会带有t表的记录

我的推测：由于我们在关联表时定义了@score为null值，又由于mysql是逐行选取，因此会取上一条非空的记录作为@score的值和该行的degree值进行比较

弊端：最后的结果会多出来@score列，是我们不想要的，那么可以将@score列并入case when的条件语句中...

select

sno,

cno,

degree,

case when @score = degree then ''

when @score:=degree then @rank:=@rank+1 end as 排名

from score, (select @rank:=0, @score:=null) as t

order by degree desc;

知识点：由于case when语句的运行规则是逐条进行，因此当@score = degree语句成立时，不执行下一行判断；若不成立，则由于@score:=degree是一个赋值语句，恒成立，进而执行@rank+1语句

18.

思路：难点在于前三如何取，用大于某成绩的记录条数<3来做为限制条件即可以选取到前三名的记录

select

*

from score a

where (select count(1) from score b where a.cno = b.cno and b.degree > a.degree) < 3

order by cno desc, degree desc;

我推测的取数逻辑：按cno和degree降序排列；取第一个记录后，进入到where过滤；子查询的第一个条件为cno相等，第二个条件为大于第一条记录中degree的值的记录，假设有2条，则count函数返回2，满足小于3的条件，父查询中的where返回true；第一条记录被选出，进入第二条记录...

选出同名同性别的学生(非重复记录)，并统计人数

思路：名字性别可以重复，学生的编号不能重复，

select

a.sname,

a.ssex,

count(1)

from student a

inner join student b on a.sname = b.sname and a.ssex = b.ssex and a.sno != b.sno

group by a.sname, a.ssex;

查询均分高于85的学生姓名

思路：分步式，先查询出均分高于85的学生编号；再和student表关联，查询学生姓名

select b.sname,a.avg_score

from

(select sno, avg(degree) as avg_score

from score

group by sno

having avg(degree) > 85) a

join student b on a.sno = b.sno;

查询任意一门课程的成绩在80分以上的学生信息

思路：先查找出成绩大于80分的学生编号(这些学生至少有一门课成绩大于80分)，再用编号作为限定条件选取学生信息

select *

from student a

where sno in (

select sno

from score

where degree > 80);

查询李诚老师的学生中成绩最好的学生姓名和成绩

select

a.sname, b.degree

from student a

join score b on a.sno = b.sno

join course c on b.cno = c.cno

join teacher d on c.tno = d.tno

where d.tname = '李诚'

order by b.degree desc

limit 1;

如果成绩出现重复，用limit方法则只能选取出一位学生

假设成绩有重复的情况下，查询李诚老师的学生中成绩最好的学生姓名和成绩

思路：先取出李诚老师的学生信息以及成绩，再对学生成绩进行排名，最后取 排名为1的记录即可

select * # 第三步

from

(

select # 第二步

a.sname,

case when @score = degree then @rank

when @score:=degree then @rank:=@rank+1 end as 排名

from

(select # 第一步

a.sno, a.sname, b.degree, d.tname

from student a

join score b on a.sno = b.sno

join course c on b.cno = c.cno

join teacher d on c.tno = d.tno

where d.tname = '李诚') a, (select @rank:=0, @score:=null) b

) a

where 排名 = 1;

36.

和之前的思路(大于某成绩的记录数量小于3)不同，使用分组排名的形式

select sno, cno, degree, 排名 from

(select

a.*,

@rank:=if(@class=cno,if(@score=degree,@rank,@rank+1),1) as 排名,

@score:=degree,

@class:=cno

from score a, (select @rank:=0, @score:=null, @class:=null) b

order by cno, degree desc) a

where 排名 < 3;

大前提：整张表是按cno升序和degree降序进行排列的

第四行@rank:=if(@class=cno, if(@score=degree, @rank,@rank+1), 1)所做的事是：

当一条记录进入选择语句，首先判断课程cno是否和上一条记录@class相同，如果不是，说明该记录属于一门新的课程，从1开始排名

如果@class=cno为真，说明该记录和上一条记录属于同一门课程，则进入下一层判断，判断该记录的成绩degree是否和上一条记录的成绩@score相同；如果相同，则和上一条记录并列排名；如果不同，排名加一

如果出现排名相同的情况，但是我们只想取第一个学生怎么办？

并未考证过，仅为思路(数据中没有相同成绩的学生)

where 排名 < 3 and (select sno from temp b where b.排名 = a.排名 limit 1)

with temp as

(select

a.*,

@rank:=if(@class=cno,if(@score=degree,@rank,@rank+1),1) as 排名,

@score:=degree,

@class:=cno

from score a, (select @rank:=0, @score:=null, @class:=null) b

order by cno, degree desc)

select sno, cno, degree, 排名 from temp a

where 排名 < 3

and (select sno from temp b where b.排名 = a.排名 limit 1);

选修了全部课程的学生信息

select *

from score

join student on score.sno = student.sno

group by score.sno

having count(1) = (select count(1) from course);

# 由于我们的课程数据有4条，但是成绩表中只包含了三门课程，因此没有人修全部课程

要求按照出生年月日计算学生年龄

运用时间差函数timestampdiff或datediff

时间差函数比之前用到的年份相减法多涵盖了日期和时间，只有完整地相差一年才会返回1

select sname, timestampdiff(year,sbirthday, now()) as 年龄 from student;

# year是自行设置的参数，设定返回的值类型

查询本周过生日的学生

week()函数能返回该日期在一年内对应的周数

select sname from student

where week(sbirthday) = week(now());

如果使用month()函数同理可查询到本月过生日的学生

查询同月过生日的学生数量

在select后的变量名(如mon)可以用在筛选/分组等操作

select count(sname),month(sbirthday) as mon

from student

group by mon

order by mon;

查询成绩在前1/3的学生姓名和总成绩

思路：运用窗口函数ntile进行分箱

select

b.sname, a.tot

from(

select

sno, sum(degree) tot,

ntile(3) over(order by sum(degree) desc) lvl

from score

group by sno) a join student b

on a.sno = b.sno

where a.lvl = 1;

查询按男生/女生分组，成绩分别在前50%的学生姓名和成绩

思路：使用group by将成绩按sno求和，再使用ntile窗口函数对总成绩分箱，取出level=1的学生记录

select

b.sname, b.ssex, a.tot

from(

select

b.sno, b.ssex, sum(a.degree) tot,

ntile(2) over(partition by b.ssex order by sum(degree) desc) lvl

from score a join student b

on a.sno = b.sno

group by a.sno) a # 或者group by a.sno, b.ssex因为外层的优先级高于内层

join student b on a.sno = b.sno

where a.lvl = 1;

问：为什么不能按男/女生分组？原因对数据不够理解，因为一个学生会参加多个课程，只能通过在外层对sno进行分组求sum(degree)，才能在内层进行排序，内层排序可以和partition同时进行(如果在外层对ssex进行分组，窗口函数的内层就无法按sno求sum，而是按照ssex作为聚合方式求sum)

回顾：外层的where 、group by、order by会优先于窗口函数内层的partition by、order by执行

查询计算机导论课程成绩最高的学生和操作系统成绩最高的学生

select cname, max(degree)

from score join course on score.cno = course.cno

where cname in ('计算机导论', '操作系统')

group by score.cno;

查询score表中3-245课程选修人数和3-105课程选修人数之比

WITH子句的用法：

基本格式为WITH table_name AS(query_expressions)

在创建CTE之后必须跟随索引该CTE的语句(见代码注解)

为什么使用WITH子句？增加代码可读性...

with temp as(

select distinct count(*) as cnt,

cno

from score

group by cno)

select distinct # with子句和主query是连在一起的一条query，没有分号 # 这里不加distinct会出现3条相同的记录，暂时不明白原理

# 在select中使用select!!!

(select cnt from temp where cno = '3-245')/(select cnt from temp where cno = '3-105') as ratio

from temp # 必须包含temp表，否则会报错

;

查询连续3位学生成绩都在70分以上的cno(非排序)

连续n条记录相同的情况

select

distinct cno

from(

select

cno,

case when @sc <= 70 and degree <= 70 then @rank:= 0

when @sc <= 70 and degree > 70 then @rank:= 1

when @sc > 70 and degree > 70 then @rank:= @rank + 1

when @sc > 70 and degree <= 70 then @rank:= 0

end rn,

@sc:=degree

from score, (select @rank:=0, @sc:=0) a

) b

where rn >= 3;

查询每位学生参加的课程中，编号最大的课程成绩

HAVING的用法：本质是筛选

select * from score

group by sno

having max(cno);

将所有学生的成绩均分为5级，查询3-105课程在每级中的贡献率

分级怎么分？

按序号排序asc，查询下一位同学比上一位同学出生要晚的同学姓名

select sno, sname

from(

select

sno, sname,

case when timestampdiff(day, @birth, sbirthday) > 0 then 1

else 0 end rn,

@birth:= sbirthday

from student, (select @birth:= null) a) a

where rn = 1;

查询每位学生各门课的成绩{leetcode1179}

要求是输出sno列和各课程名称

select

sno,

sum(case cno when '3-105' then degree end) 计算机导论,

sum(case cno when '3-245' then degree end) 操作系统,

sum(case cno when '6-166' then degree end) 数字电路

from score

group by sno;

查询顺序是先group by再select；注意sum的用法，不加sum则只会输出第一条数据

选取第N(4)高的成绩{leetcode177}

排序类窗口函数的运用

ROW_NUMBER()排序规则1、2、3、4

RANK()排序规则1、2、2、4

DENSE_RANK()排序规则1、2、2、3

select

distinct degree # 成绩可能重复

from(

select

degree,

dense_rank() over(order by degree desc) rnk

from score) a

where rnk = 4;

查询每门课每位学生和上一位学生成绩的差

第一种方法：用自定义变量

之前错误的思路为：希望将上一条记录的degree赋值给自定义变量，然后做差；实际上做不到，只能通过判断或运算来调用上一条记录的自定义变量

select

cno, sno,

degree,

case when @cls=cno then round(degree - @pre, 0) # 使用自定义变量@cls来保证diff在同一cno内

when @pre:=null then null end diff, # 若@cls!=cno则进入下一个when，@pre:=null恒成立

@pre:=degree,

@cls:=cno

from score, (select @pre:=null, @cls:=null) a

order by cno; # 第一步先进行排序

第二种方法：窗口函数

select cno, sno, degree-pre diff

from(

select

cno, sno, degree,

lag(degree,1) over(partition by cno) pre

from score) a;