SQL练习（零散）

1.用一条SQL语句查询出grade表中每门课都大于80分的学生姓名

name  course    score 
张三     语文     81
张三     数学     75
李四     语文     76
李四     数学     90
王五     语文     81
王五     数学     100
王五     英语     90

select name from grade 
group by name 
having min(分数)>80

2.将表city1中num_person字段根据人数进行重新编码
要求:0<=x<500,000编码为1
500,000<=x<1,000,000编码为2
1,000,000<=x编码为3

select name,
case 
    when num_person>=0 and num_person<500000 then 1
    when num_person>=500000 and num_person<1000000 then 2
    when num_person>=1000000 then 3 end as mark  #将重新编码的后的数值在mark字段下展示。
from city1

学生表：Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别
课程表：Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号
教师表：Teacher(t_id,t_name) –教师编号,教师姓名
成绩表：Score(s_id,c_id,s_s_score) –学生编号,课程编号,分数

查询男生、女生人数

SELECT
    s_sex,count(*)
FROM
    Student
GROUP BY
    s_sex;

查询平均成绩大于60分的学生的学号和平均成绩

select s_id, avg(s_score)
from Score
group by s_id
having avg(s_score)>60

查询两门以上不及格课程的同学的学号及其平均成绩

select s_id as 学号, avg(s_score)as 平均成绩
from Score
where s_score <60
group by s_id
having count(c_id)>=2;

查询同名同性学生名单并统计同名人数

select s_name,count(*)
from Student
group by s_name
having count(*)>1

有关group by having 和where
where里的语句会优先在筛选之前就把不符合的删掉
当有group by存在，但where里的字段不出现在group by里的时候，where语句会失效
where后面不可跟聚合函数，只有select，having，order by后面可以跟聚合函数（count,，sum，avg，max，min）
having后面才能跟聚合函数，聚合函数是针对group by后面的字段的
WHERE 语句和HAVING配合的使用。WHERE在HAVING之前.
WHERE 过滤针对的是行，HAVING过滤针对的是组

查询不及格的课程并按课程号从大到小排列

select c_id as 课程号 ,s_score as 分数
from Score 
where s_score<60
order by c_id;

查询没有学全所有课的学生的学号、姓名

也就是学生学习的课程数小于总的课程数
select s_id, s_name
from Student 
where s_id IN (SELECT s_id FROM Score group by s_id having count(c_id)<(select count(c_id) from Course))

查询出只选修了两门课程的全部学生的学号和姓名

select s_id,s_name
from Student
WHERE s_id IN (SELECT s_id FROM Score GROUP BY s_id having count(c_id)=2)

查询课程编号为“001”的课程比“002”的课程成绩高的所有学生的学号

首先查询课程编号分别为001和002的所有学生的学号及其分数作为内嵌视图A和B，然后将A和B通过学号关联，过滤条件就是A的分数大于B的分数
select a.s_id
from (select * from Score where c_id = '01') as a 
join (select * from Score where c_id='02') as b
on a.s_id = b.s_id
where a.s_score > b.s_score;

使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称

考察case语句，分段统计的题目都是这个套路
select a.c_id AS '课程ID',c_name '课程名称',
sum(case when s_score between 85 and 100 then 1 else 0 end) as '[100-85]',
sum(case when s_score >=70 and s_score<85 then 1 else 0 end) as '[85-70]',
sum(case when s_score>=60 and s_score<70  then 1 else 0 end) as '[70-60]',
sum(case when s_score<60 then 1 else 0 end) as '[<60]'
from Score a join Course b on a.c_id=b.c_id
group by a.c_id,c_name;

查询出每门课程的及格人数和不及格人数

select c_id ,
sum(case when s_score>=60 then 1 else 0 end) as '及格人数',
sum(case when s_score<60 then 1 else 0 end) as '不及格人数',
(select count(s_score) from Score) as '总人数' 
from score
group by c_id

查询和“01”号同学所学课程完全相同的其他同学的学号

首先找出学号为1001的学生学习过的课程，然后根据这些课程号和所学课程总数就可以找到有哪些同学学习过和他一样的课程
select s_id
from Score
where c_id in
(select c_id from Score where s_id='01')
and s_id <> '01'
group by s_id
having count(c_id)=(select count(c_id) from Score where s_id='01');

把“SCORE”表中“张三”老师教的课的成绩都更改为此课程的平均成绩

考察数据库更改操作。首先找到张三老师教过哪些课程及其课程的平均成绩，然后根据课程号关联成绩表进行更新
update Score as a 
join (select avg(s_score) as t, Score.c_id 
from Score join Course on Score.c_id= Course.c_id
join Teacher on Teacher.t_id= Course.t_id
where t_name ='张三' group by c_id) as b#张三老师教的课与平均分
on a.c_id= b.c_id
set a.s_score= b.t;

查询学生平均成绩及其名次~~

算出在所有同学中有几个同学的平均分高于某个ID,然后+1，就是名次
SELECT s_id as '学号',(SELECT COUNT(*) FROM(SELECT s_id,AVG(s_score)AS '平均成绩'
FROM Score GROUP BY s_id)AS b 
WHERE b.平均成绩>a.平均成绩)+1 as '名次'
FROM (select s_id,avg(S_score) as 平均成绩 from Score group by s_id)AS a
order by 平均成绩 desc;

查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名

select s_id,s_name
from Student 
where s_id in
(select s_id from Score where c_id = '01') 
AND s_id in
(select s_id from Score where c_id = '02')

查询有2门不同课程成绩相同的学生的学号、课程号、学生成绩

select distinct a.s_id as 学生编号 ,a.c_id as 课程编号,a.s_score as 学生成绩
from Score a join Score b
on a.s_id=b.s_id and a.c_id<> b.c_id
where a.s_score=b.s_score;

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

select a.s_id, s_name,s_birth, s_sex
from Student a join Score b on a.s_id=b.s_id
group by a.s_id
having count(Score.c_id)=(select count(distinct c_id) from Score);

查询没学过“张三”老师课的学生的学号、姓名

三表联结。首先查询学习过“张三”老师课的学生的学号作为子查询，而“张三”老师涉及到TEACHER表，TEACHER表要和学生有关联必须通过课程和成绩表。
select s_id, s_name
from Student
where s_id not in 
(select s_id from Score join Course on Score.c_id = Course.c_id
join Teacher on Course.t_id = Teacher.t_id
where t_name = '张三');

删除学生编号为“02”的课程编号为“01”的成绩

delete from Score
where s_id='02'
and c_id='01';

按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分

select t.s_id as '学生ID',
(select s_score from Score where s_id =t.s_id  and c_id='002') as '语文',
(select s_score from Score where s_id =t.s_id  and c_id='003') as '数学',
(select s_score from Score where s_id =t.s_id  and c_id='004') as '英语',
count(t.c_id) as '有效课程数',
avg(t.s_score) as '有效平均分'
from Score t
group by t.s_id
order by avg(t.s_score)

4.如果一张表中有一个非主键的字段指向了别一张表中的主键，就将该字段叫做外键。一张表中可以有多个外键。
作用：用于保持数据一致性、完整性，控制存储在外键表中的数据

5.现有表 tb1 ，有字段 name, class, score .分别代表 姓名，所在班级，分数。
要求：用一条SQL语句查询出每个班的及格人数和不及格人数，格式为：class,及格人数，不及格人数（score>=60为及格）

select class 班级,
sum(case when score>=60 then 1 else 0 end) as 及格人数,
sum(case when score<60 then 1 else 0 end) as 不及格人数
from tb1
group by class;

6.餐馆菜单输出最贵的五道菜

SELECT food_name 
FROM table 
ORDER BY food_price LIMIT 5;

7.mysql的连接查询

INNER JOIN：两边表同时有对应的数据，即任何一边数据缺失就不显示
LEFTJOIN：读取左边数据表中的全部数据，即使右边无对应数据
RIGHT JOIN:读取右边数据表中的全部数据，即使左边无对应数据

8.订单表user_order结构和数据如下。请编写sql语句查出首次下单日期是2018年05月22号的用户数量

注意是首次下

id user_id product price create_date
1 234 坚果Pro2 1400 '2018-05-21'
2 234 锤子TNT 1400 '2018-05-21'
3 356 小米mix 1400 '2018-05-21'
4 357 硅胶娃娃 1400 '2018-05-21'

select count(*) 
from (select * from user_order group by user_id having min(create_date) = '2018-05-22')

9.一张学生表。把数学成绩前10的学生信息查出来

SELECT * 
FROM table 
ODER BY math ASC 
LIMIT 10

10.订单表结构为 (用户id，商品id)，写一个sql语句，查询购买商品数最多的前十个用户

SELECT user_id 
FROM table 
GROUP BY user_id 
ORDER BYproduct_id LIMIT 0,10

select d.dt,a.area,d.songid 
from t_user a,(select * from t_user b,t_play c where b.area=a.area and c.dt=d.dt and b.userid=c.userid order by c.play_cnt desc limit 0,3) e ,t_play  d 
group by d.dt,a.area 
having e.area=a.area and d.dt=e.dt and a.userid=d.userid ;

select  (select count(*) from  t_user, t_play where t_user.userid=t_play.userid and t_play.dt=20180502 and  t_user.userid  in (select * from t_user a, t_play b where a.userid=b.userid and b=20180501))/(select count(*)from t_user, t_play where t_user.userid=t_play.userid and t_play.dt=20180501)

12.count() 和 count(1)和count(列名)区别
执行效果上：
count()包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

执行效率上：
列名为主键，count(列名)会比count(1)快
列名不为主键，count(1)会比count(列名)快
如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（）
如果有主键，则 select count（主键）的执行效率是最优的
如果表只有一个字段，则 select count（）最优。

SELECT A  AS 日期,
SUM(CASE  WHEN  B='胜'  THEN  1  ELSE  0  END) AS `胜`,
SUM(CASE  WHEN  B='负'  THEN  1  ELSE  0  END) AS `负`
FROM  table
GROUP BY A

或者

SELECT  A  AS 日期,
SUM(IF(B='胜',1,0) AS 胜,
SUM(IF(B='负',1,0) AS 负
FROM  table
GROUP BY A

14.假设教师带了三门课，如何写可以让教师查出每门课的及格率

SELECT count(case when cou1 >=60 then 1 else null end ) /count(*) as kecheng1,
count(case when cou2 >=60 then 1 else null end ) /count(*)  as kecheng2,
count(case when cou3 >=60 then 1 else null end ) /count(*)  as kecheng3
from  world.gradedata001

15.学生表 如下:
自动编号 学号 姓名 课程编号 课程名称 分数
1 2005001 张三 0001 数学 69
2 2005002 李四 0001 数学 89
3 2005001 张三 0001 数学 69
删除除了自动编号不同,其他都相同的学生冗余信息

delete tablename 
where 自动编号 not in(select min(自动编号) 
from tablename 
group by 学号,姓名,课程编号,课程名称,分数)

16.一个叫department的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球对，现在四个球对进行比赛，用一条sql语句显示所有可能的比赛组合.

select a.name, b.name 
from team a, team b 
where a.name < b.name

17.从TestDB数据表中查询出所有月份的发生额都比101科目相应月份的发生额高的科目。请注意：TestDB中有很多科目，都有1－12月份的发生额。
AccID：科目代码，Occmonth：发生额月份，DebitOccur：发生额。
数据库名：JcyAudit，数据集：Select * from TestDB

select a.*
from TestDB a 
,(select Occmonth,max(DebitOccur) Debit101ccur from TestDB where AccID='101' group by Occmonth) b
where a.Occmonth=b.Occmonth and a.DebitOccur>b.Debit101ccur

18.

year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4

select year, 
(select amount from aaa m where month=1 and m.year=aaa.year) as m1,
(select amount from aaa m where month=2 and m.year=aaa.year) as m2,
(select amount from aaa m where month=3 and m.year=aaa.year) as m3,
(select amount from aaa m where month=4 and m.year=aaa.year) as m4
from aaa group by year

19.有两个表A和B，均有key和value两个字段，如果B的key在A中也有，就把B的value换为A中对应的value

update b 
set b.value=(select a.value from a where a.key=b.key) 
where b.id in(select b.id from b,a where b.key=a.key);
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 194,390评论 5赞 459
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 81,821评论 2赞 371
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 141,632评论 0赞 319
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 52,170评论 1赞 263
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 61,033评论 4赞 355
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 46,098评论 1赞 272
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 36,511评论 3赞 381
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 35,204评论 0赞 253
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 39,479评论 1赞 290
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 34,572评论 2赞 309
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 36,341评论 1赞 326
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 32,213评论 3赞 312
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 37,576评论 3赞 298
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 28,893评论 0赞 17
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,171评论 1赞 250
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 41,486评论 2赞 341
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 40,676评论 2赞 335