1.用一條SQL語句查詢出grade表中每門課都大于80分的學(xué)生姓名

name  course    score 
張三     語文     81
張三     數(shù)學(xué)     75
李四     語文     76
李四     數(shù)學(xué)     90
王五     語文     81
王五     數(shù)學(xué)     100
王五     英語     90

select name from grade 
group by name 
having min(分?jǐn)?shù))>80

2.將表city1中num_person字段根據(jù)人數(shù)進(jìn)行重新編碼
要求: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  #將重新編碼的后的數(shù)值在mark字段下展示。
from city1

學(xué)生表：Student(s_id,s_name,s_birth,s_sex) –學(xué)生編號(hào),學(xué)生姓名, 出生年月,學(xué)生性別
課程表：Course(c_id,c_name,t_id) – –課程編號(hào), 課程名稱, 教師編號(hào)
教師表：Teacher(t_id,t_name) –教師編號(hào),教師姓名
成績(jī)表：Score(s_id,c_id,s_s_score) –學(xué)生編號(hào),課程編號(hào),分?jǐn)?shù)

查詢男生瓮栗、女生人數(shù)

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

查詢平均成績(jī)大于60分的學(xué)生的學(xué)號(hào)和平均成績(jī)

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

查詢兩門以上不及格課程的同學(xué)的學(xué)號(hào)及其平均成績(jī)

select s_id as 學(xué)號(hào), avg(s_score)as 平均成績(jī)
from Score
where s_score <60
group by s_id
having count(c_id)>=2;

查詢同名同性學(xué)生名單并統(tǒng)計(jì)同名人數(shù)

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

有關(guān)group by having 和where
where里的語句會(huì)優(yōu)先在篩選之前就把不符合的刪掉
當(dāng)有g(shù)roup by存在果港，但where里的字段不出現(xiàn)在group by里的時(shí)候印蓖，where語句會(huì)失效
where后面不可跟聚合函數(shù)，只有select京腥，having赦肃，order by后面可以跟聚合函數(shù)（count,，sum公浪，avg鲁驶，max歼捐，min）
having后面才能跟聚合函數(shù)琅摩，聚合函數(shù)是針對(duì)group by后面的字段的
WHERE 語句和HAVING配合的使用盗蟆。WHERE在HAVING之前.
WHERE 過濾針對(duì)的是行，HAVING過濾針對(duì)的是組

查詢不及格的課程并按課程號(hào)從大到小排列

select c_id as 課程號(hào) ,s_score as 分?jǐn)?shù)
from Score 
where s_score<60
order by c_id;

查詢沒有學(xué)全所有課的學(xué)生的學(xué)號(hào)预柒、姓名

也就是學(xué)生學(xué)習(xí)的課程數(shù)小于總的課程數(shù)
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))

查詢出只選修了兩門課程的全部學(xué)生的學(xué)號(hào)和姓名

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)

查詢課程編號(hào)為“001”的課程比“002”的課程成績(jī)高的所有學(xué)生的學(xué)號(hào)

首先查詢課程編號(hào)分別為001和002的所有學(xué)生的學(xué)號(hào)及其分?jǐn)?shù)作為內(nèi)嵌視圖A和B队塘，然后將A和B通過學(xué)號(hào)關(guān)聯(lián)，過濾條件就是A的分?jǐn)?shù)大于B的分?jǐn)?shù)
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]來統(tǒng)計(jì)各科成績(jī)宜鸯，分別統(tǒng)計(jì)各分?jǐn)?shù)段人數(shù)：課程ID和課程名稱

考察case語句憔古，分段統(tǒng)計(jì)的題目都是這個(gè)套路
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;

查詢出每門課程的及格人數(shù)和不及格人數(shù)

select c_id ,
sum(case when s_score>=60 then 1 else 0 end) as '及格人數(shù)',
sum(case when s_score<60 then 1 else 0 end) as '不及格人數(shù)',
(select count(s_score) from Score) as '總?cè)藬?shù)' 
from score
group by c_id 

查詢和“01”號(hào)同學(xué)所學(xué)課程完全相同的其他同學(xué)的學(xué)號(hào)

首先找出學(xué)號(hào)為1001的學(xué)生學(xué)習(xí)過的課程，然后根據(jù)這些課程號(hào)和所學(xué)課程總數(shù)就可以找到有哪些同學(xué)學(xué)習(xí)過和他一樣的課程
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”表中“張三”老師教的課的成績(jī)都更改為此課程的平均成績(jī)

考察數(shù)據(jù)庫更改操作淋袖。首先找到張三老師教過哪些課程及其課程的平均成績(jī)鸿市，然后根據(jù)課程號(hào)關(guān)聯(lián)成績(jī)表進(jìn)行更新
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;

查詢學(xué)生平均成績(jī)及其名次~~

算出在所有同學(xué)中有幾個(gè)同學(xué)的平均分高于某個(gè)ID,然后+1，就是名次
SELECT s_id as '學(xué)號(hào)',(SELECT COUNT(*) FROM(SELECT s_id,AVG(s_score)AS '平均成績(jī)'
FROM Score GROUP BY s_id)AS b 
WHERE b.平均成績(jī)>a.平均成績(jī))+1 as '名次'
FROM (select s_id,avg(S_score) as 平均成績(jī) from Score group by s_id)AS a
order by 平均成績(jī) desc;

查詢學(xué)過編號(hào)為“01”的課程并且也學(xué)過編號(hào)為“02”的課程的學(xué)生的學(xué)號(hào)即碗、姓名

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門不同課程成績(jī)相同的學(xué)生的學(xué)號(hào)焰情、課程號(hào)、學(xué)生成績(jī)

select distinct a.s_id as 學(xué)生編號(hào) ,a.c_id as 課程編號(hào),a.s_score as 學(xué)生成績(jī)
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;

查詢選修了全部課程的學(xué)生信息

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);

查詢沒學(xué)過“張三”老師課的學(xué)生的學(xué)號(hào)剥懒、姓名

三表聯(lián)結(jié)内舟。首先查詢學(xué)習(xí)過“張三”老師課的學(xué)生的學(xué)號(hào)作為子查詢，而“張三”老師涉及到TEACHER表初橘，TEACHER表要和學(xué)生有關(guān)聯(lián)必須通過課程和成績(jī)表验游。
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 = '張三');

刪除學(xué)生編號(hào)為“02”的課程編號(hào)為“01”的成績(jī)

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

按平均成績(jī)從低到高顯示所有學(xué)生的“語文”充岛、“數(shù)學(xué)”、“英語”三門的課程成績(jī)批狱，按如下形式顯示： 學(xué)生ID,語文,數(shù)學(xué),英語,有效課程數(shù),有效平均分

select t.s_id as '學(xué)生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 '數(shù)學(xué)',
(select s_score from Score where s_id =t.s_id  and c_id='004') as '英語',
count(t.c_id) as '有效課程數(shù)',
avg(t.s_score) as '有效平均分'
from Score t
group by t.s_id
order by avg(t.s_score)

4.如果一張表中有一個(gè)非主鍵的字段指向了別一張表中的主鍵裸准，就將該字段叫做外鍵展东。一張表中可以有多個(gè)外鍵赔硫。
作用：用于保持?jǐn)?shù)據(jù)一致性、完整性盐肃，控制存儲(chǔ)在外鍵表中的數(shù)據(jù)

5.現(xiàn)有表 tb1 爪膊，有字段 name, class, score .分別代表 姓名，所在班級(jí)砸王，分?jǐn)?shù)推盛。
要求：用一條SQL語句查詢出每個(gè)班的及格人數(shù)和不及格人數(shù)，格式為：class,及格人數(shù)谦铃，不及格人數(shù)（score>=60為及格）

select class 班級(jí),
sum(case when score>=60 then 1 else 0 end) as 及格人數(shù),
sum(case when score<60 then 1 else 0 end) as 不及格人數(shù)
from tb1
group by class;

6.餐館菜單輸出最貴的五道菜

SELECT food_name 
FROM table 
ORDER BY food_price LIMIT 5;

7.mysql的連接查詢

INNER JOIN：兩邊表同時(shí)有對(duì)應(yīng)的數(shù)據(jù)耘成，即任何一邊數(shù)據(jù)缺失就不顯示
LEFTJOIN：讀取左邊數(shù)據(jù)表中的全部數(shù)據(jù)，即使右邊無對(duì)應(yīng)數(shù)據(jù)
RIGHT JOIN:讀取右邊數(shù)據(jù)表中的全部數(shù)據(jù)驹闰，即使左邊無對(duì)應(yīng)數(shù)據(jù)

8.訂單表user_order結(jié)構(gòu)和數(shù)據(jù)如下瘪菌。請(qǐng)編寫sql語句查出首次下單日期是2018年05月22號(hào)的用戶數(shù)量

注意是首次下

id user_id product price create_date
1 234 堅(jiān)果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.一張學(xué)生表。把數(shù)學(xué)成績(jī)前10的學(xué)生信息查出來

SELECT * 
FROM table 
ODER BY math ASC 
LIMIT 10

10.訂單表結(jié)構(gòu)為 (用戶id嘹朗，商品id)师妙，寫一個(gè)sql語句，查詢購買商品數(shù)最多的前十個(gè)用戶

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(列名)區(qū)別
執(zhí)行效果上：
count()包括了所有的列屹培，相當(dāng)于行數(shù)默穴，在統(tǒng)計(jì)結(jié)果的時(shí)候，不會(huì)忽略列值為NULL
count(1)包括了忽略所有列褪秀，用1代表代碼行蓄诽，在統(tǒng)計(jì)結(jié)果的時(shí)候，不會(huì)忽略列值為NULL
count(列名)只包括列名那一列媒吗，在統(tǒng)計(jì)結(jié)果的時(shí)候若专，會(huì)忽略列值為空（這里的空不是只空字符串或者0，而是表示null）的計(jì)數(shù)蝴猪，即某個(gè)字段值為NULL時(shí)调衰，不統(tǒng)計(jì)。

執(zhí)行效率上：
列名為主鍵自阱，count(列名)會(huì)比count(1)快
列名不為主鍵嚎莉，count(1)會(huì)比count(列名)快
如果表多個(gè)列并且沒有主鍵，則 count（1） 的執(zhí)行效率優(yōu)于 count（）
如果有主鍵沛豌，則 select count（主鍵）的執(zhí)行效率是最優(yōu)的
如果表只有一個(gè)字段趋箩，則 select count（）最優(yōu)赃额。

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

或者

SELECT  A  AS 日期,
SUM(IF(B='勝',1,0) AS 勝,
SUM(IF(B='負(fù)',1,0) AS 負(fù)
FROM  table
GROUP BY A

14.假設(shè)教師帶了三門課，如何寫可以讓教師查出每門課的及格率

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.學(xué)生表 如下:
自動(dòng)編號(hào) 學(xué)號(hào) 姓名 課程編號(hào) 課程名稱 分?jǐn)?shù)
1 2005001 張三 0001 數(shù)學(xué) 69
2 2005002 李四 0001 數(shù)學(xué) 89
3 2005001 張三 0001 數(shù)學(xué) 69
刪除除了自動(dòng)編號(hào)不同,其他都相同的學(xué)生冗余信息

delete tablename 
where 自動(dòng)編號(hào) not in(select min(自動(dòng)編號(hào)) 
from tablename 
group by 學(xué)號(hào),姓名,課程編號(hào),課程名稱,分?jǐn)?shù))

16.一個(gè)叫department的表叫确，里面只有一個(gè)字段name,一共有4條紀(jì)錄跳芳，分別是a,b,c,d,對(duì)應(yīng)四個(gè)球?qū)ΓF(xiàn)在四個(gè)球?qū)M(jìn)行比賽竹勉，用一條sql語句顯示所有可能的比賽組合.

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

17.從TestDB數(shù)據(jù)表中查詢出所有月份的發(fā)生額都比101科目相應(yīng)月份的發(fā)生額高的科目飞盆。請(qǐng)注意：TestDB中有很多科目，都有1－12月份的發(fā)生額次乓。
AccID：科目代碼吓歇，Occmonth：發(fā)生額月份，DebitOccur：發(fā)生額票腰。
數(shù)據(jù)庫名：JcyAudit城看，數(shù)據(jù)集：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
查成這樣一個(gè)結(jié)果
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.有兩個(gè)表A和B，均有key和value兩個(gè)字段杏慰，如果B的key在A中也有测柠，就把B的value換為A中對(duì)應(yīng)的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);