SQL基本操作

1 設(shè)計(jì)表操作

1.1 關(guān)系表字段

• 1 學(xué)生表 student

s_id	s_name	s_birth	s_sex
學(xué)生編號(hào)	學(xué)生姓名	學(xué)生年月	學(xué)生性別

• 2 課程表 course

c_id	c_name	t_id
課程編號(hào)	課程名稱	教師標(biāo)號(hào)

• 3 教師表 teacher

t_id	t_name
教師編號(hào)	教師姓名

• 4 成績(jī)表 score

s_id	c_id	s_score
學(xué)生編號(hào)	課程編號(hào)	課程分?jǐn)?shù)

1.2 關(guān)系表創(chuàng)建

• 1 創(chuàng)建 student 表

CREATE TABLE student(
s_id VARCHAR(20),
s_name VARCHAR(20) NOT NULL DEFAULT '',
s_birth VARCHAR(20) NOT NULL DEFAULT '',
s_sex VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(s_id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

• 2 創(chuàng)建 course 表

CREATE TABLE course(
c_id VARCHAR(20),
c_name VARCHAR(20) NOT NULL DEFAULT '',
t_id VARCHAR(20) NOT NULL,
PRIMARY KEY(c_id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

• 3 創(chuàng)建 teacher 表

CREATE TABLE teacher(
t_id VARCHAR(20),
t_name VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(t_id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

• 4 創(chuàng)建score表

CREATE TABLE score(
s_id VARCHAR(20),
c_id VARCHAR(20),
s_score INT(3),
PRIMARY KEY(s_id,c_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1.3 關(guān)系表數(shù)據(jù)

INSERT INTO student VALUES('01' , '趙雷' , '1990-01-01' , '男');
INSERT INTO student VALUES('02' , '錢電' , '1990-12-21' , '男');
INSERT INTO student VALUES('03' , '孫風(fēng)' , '1990-05-20' , '男');
INSERT INTO student VALUES('04' , '李云' , '1990-08-06' , '男');
INSERT INTO student VALUES('05' , '周梅' , '1991-12-01' , '女');
INSERT INTO student VALUES('06' , '吳蘭' , '1992-03-01' , '女');
INSERT INTO student VALUES('07' , '鄭竹' , '1989-07-01' , '女');
INSERT INTO student VALUES('08' , '王菊' , '1990-01-20' , '女');

INSERT INTO course VALUES('01' , '語文' , '02');
INSERT INTO course VALUES('02' , '數(shù)學(xué)' , '01');
INSERT INTO course VALUES('03' , '英語' , '03');

INSERT INTO teacher VALUES('01' , '張雪峰');
INSERT INTO teacher VALUES('02' , '李佳琦');
INSERT INTO teacher VALUES('03' , '王思聰');

INSERT INTO score VALUES('01' , '01' , 80);
INSERT INTO score VALUES('01' , '02' , 90);
INSERT INTO score VALUES('01' , '03' , 99);
INSERT INTO score VALUES('02' , '01' , 70);
INSERT INTO score VALUES('02' , '02' , 60);
INSERT INTO score VALUES('02' , '03' , 80);
INSERT INTO score VALUES('03' , '01' , 80);
INSERT INTO score VALUES('03' , '02' , 80);
INSERT INTO score VALUES('03' , '03' , 80);
INSERT INTO score VALUES('04' , '01' , 50);
INSERT INTO score VALUES('04' , '02' , 30);
INSERT INTO score VALUES('04' , '03' , 20);
INSERT INTO score VALUES('05' , '01' , 76);
INSERT INTO score VALUES('05' , '02' , 87);
INSERT INTO score VALUES('06' , '01' , 31);
INSERT INTO score VALUES('06' , '03' , 34);
INSERT INTO score VALUES('07' , '02' , 89);
INSERT INTO score VALUES('07' , '03' , 98);

1.4 關(guān)系表關(guān)系

2 SQL操作

2.1 SQL 1-10

1、查詢 “01” 課程比 “02” 課程成績(jī)高的學(xué)生的信息及課程分?jǐn)?shù)

SELECT a.* ,b.s_score AS 01_score,c.s_score AS 02_score 
FROM student a
JOIN score b 
ON a.s_id=b.s_id AND b.c_id='01'
LEFT JOIN Score c 
ON a.s_id=c.s_id AND c.c_id='02' OR c.c_id = NULL 
WHERE b.s_score>c.s_score;

2、查詢"01"課程比"02"課程成績(jī)低的學(xué)生的信息及課程分?jǐn)?shù)

SELECT a.* ,b.s_score AS 01_score,c.s_score AS 02_score 
FROM student a 
LEFT JOIN score b 
ON a.s_id=b.s_id AND b.c_id='01' OR b.c_id=NULL
JOIN score c 
ON a.s_id=c.s_id AND c.c_id='02' 
WHERE b.s_score<c.s_score;

3、查詢平均成績(jī)大于等于60分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)

SELECT b.s_id,b.s_name,ROUND(AVG(a.s_score),2) AS avg_score 
FROM student b
JOIN score a ON b.s_id = a.s_id
GROUP BY b.s_id,b.s_name 
HAVING ROUND(AVG(a.s_score),2)>=60;

4、查詢平均成績(jī)小于60分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)
(包括有成績(jī)的和無成績(jī)的)

SELECT b.s_id,b.s_name,ROUND(AVG(a.s_score),2) AS avg_score 
FROM student b
LEFT JOIN score a 
ON b.s_id = a.s_id
GROUP BY b.s_id,b.s_name 
HAVING ROUND(AVG(a.s_score),2)<60
UNION 
SELECT a.s_id,a.s_name,0 AS avg_score 
FROM student a
WHERE a.s_id NOT IN (SELECT DISTINCT s_id FROM score);

5、查詢所有同學(xué)的學(xué)生編號(hào)、學(xué)生姓名、選課總數(shù)、所有課程的總成績(jī)

SELECT a.s_id,a.s_name,
COUNT(b.c_id) AS sum_course,
SUM(b.s_score) AS sum_score 
FROM student a
LEFT JOIN score b 
ON a.s_id=b.s_id
GROUP BY a.s_id,a.s_name;

6、查詢"李"姓老師的數(shù)量

SELECT COUNT(t_id) FROM teacher WHERE t_name LIKE '李%';

7、查詢學(xué)過"張雪峰"老師授課的同學(xué)的信息

SELECT a.* 
FROM student a
JOIN score b 
ON a.s_id=b.s_id 
WHERE b.c_id IN(
SELECT c_id FROM course WHERE t_id =(
SELECT t_id FROM teacher WHERE t_name = '張雪峰'));

8、查詢沒學(xué)過"張雪峰"老師授課的同學(xué)的信息

SELECT c.* 
FROM student c
WHERE c.s_id NOT IN(
SELECT a.s_id FROM student a JOIN score b ON a.s_id=b.s_id 
WHERE b.c_id IN(
SELECT c_id FROM course WHERE t_id =(
SELECT t_id FROM teacher WHERE t_name = '張雪峰')));

9、查詢學(xué)過編號(hào)為"01"并且也學(xué)過編號(hào)為"02"的課程的同學(xué)的信息

SELECT a.* 
FROM student a,score b,score c
WHERE a.s_id = b.s_id 
AND a.s_id = c.s_id 
AND b.c_id='01' 
AND c.c_id='02';

10、查詢學(xué)過編號(hào)為"01"但是沒有學(xué)過編號(hào)為"02"的課程的同學(xué)的信息

SELECT a.* 
FROM student a
WHERE a.s_id IN 
(SELECT s_id FROM score WHERE c_id='01' ) 
AND a.s_id NOT IN
(SELECT s_id FROM score WHERE c_id='02')

2.2 SQL 11-20

11、查詢沒有學(xué)全所有課程的同學(xué)的信息

SELECT s.* 
FROM student s 
WHERE s.s_id IN(
SELECT s_id FROM score WHERE s_id NOT IN(
SELECT a.s_id FROM score a
JOIN score b ON a.s_id = b.s_id AND b.c_id='02'
JOIN score c ON a.s_id = c.s_id AND c.c_id='03'
WHERE a.c_id='01'))

12、查詢至少有一門課與學(xué)號(hào)為"01"的同學(xué)所學(xué)相同的同學(xué)的信息

SELECT * 
FROM student 
WHERE s_id IN(
SELECT DISTINCT a.s_id FROM score a WHERE a.c_id IN(
SELECT a.c_id FROM score a WHERE a.s_id='01'));

13、查詢和"01"號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息
方式1：

SELECT * FROM student WHERE s_id IN(
SELECT DISTINCT d.s_id FROM (
SELECT b.s_id , c.c_id FROM (
SELECT s.s_id,COUNT(a.c_id) c_num 
FROM student s,score a 
WHERE s.s_id = a.s_id 
GROUP BY s.s_id 
HAVING c_num=(SELECT COUNT(c_id) FROM score WHERE s_id='01')) b 
JOIN score c 
WHERE b.s_id = c.s_id AND c.c_id IN (
SELECT c_id FROM score WHERE s_id='01')) d) 
AND s_id !='01'; 

第一步：先查詢s_id是01的學(xué)生的課程號(hào)和課程號(hào)的個(gè)數(shù)
SELECT COUNT(c_id) FROM score WHERE s_id='01'
SELECT c_id FROM score WHERE s_id='01'
第二步：求學(xué)生c_id的個(gè)數(shù)是3的s_id,并且c_id在第一步查詢的c_id的值內(nèi)
SELECT b.s_id , c.c_id FROM (
SELECT s.s_id,COUNT(a.c_id) c_num 
FROM student s,score a 
WHERE s.s_id = a.s_id 
GROUP BY s.s_id 
HAVING c_num=(
SELECT COUNT(c_id) 
FROM score WHERE s_id='01')) b 
JOIN score c WHERE b.s_id = c.s_id AND c.c_id IN (
SELECT c_id FROM score WHERE s_id='01')
第三步：查詢第二步的結(jié)果中的s_id
第四步：查詢學(xué)生信息且s_id在第三步的s_id中 并且去除掉編號(hào)是01的學(xué)生信息

方式2：

SELECT * 
FROM student 
WHERE s_id IN ( 
SELECT s_id FROM (
SELECT s_id, COUNT( s_id ) cou 
FROM ( 
SELECT * FROM score WHERE s_id IN (
SELECT s_id FROM (
SELECT s_id, COUNT( s_id ) COUNT 
FROM score WHERE s_id != '01' GROUP BY s_id ) t1 
WHERE t1.count = (
SELECT COUNT( c_id ) FROM score WHERE s_id = '01' ))) t3 
WHERE t3.c_id IN (
SELECT c_id FROM score WHERE s_id = '01' ) GROUP BY s_id ) t4 
WHERE t4.cou=(SELECT COUNT( c_id ) FROM score WHERE s_id = '01'))

– 14、查詢沒學(xué)過"張三"老師講授的任一門課程的學(xué)生姓名

select a.s_name from student a where a.s_id not in (
select s_id from score where c_id =
(select c_id from course where t_id =(
select t_id from teacher where t_name = ‘張三’))
group by s_id);

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

select a.s_id,a.s_name,ROUND(AVG(b.s_score)) from
student a
left join score b on a.s_id = b.s_id
where a.s_id in(
select s_id from score where s_score<60 GROUP BY s_id having count(1)>=2)
GROUP BY a.s_id,a.s_name

– 16、檢索"01"課程分?jǐn)?shù)小于60，按分?jǐn)?shù)降序排列的學(xué)生信息

select a.*,b.c_id,b.s_score from
student a,score b
where a.s_id = b.s_id and b.c_id=‘01’ and b.s_score<60 ORDER BY b.s_score DESC;

– 17、按平均成績(jī)從高到低顯示所有學(xué)生的所有課程的成績(jī)以及平均成績(jī)

select a.s_id,(select s_score from score where s_id=a.s_id and
c_id=‘01’) as 語文,
(select s_score from score where s_id=a.s_id and c_id=‘02’) as 數(shù)學(xué),
(select s_score from score where s_id=a.s_id and c_id=‘03’) as 英語,
round(avg(s_score),2) as 平均分 from score a GROUP BY a.s_id ORDER BY 平均分 DESC;

– 18.查詢各科成績(jī)最高分、最低分和平均分：以如下形式顯示：課程ID，課程name，最高分，最低分，平均分，及格率，中等率，優(yōu)良率，優(yōu)秀率
–及格為>=60，中等為：70-80，優(yōu)良為：80-90，優(yōu)秀為：>=90

select
a.c_id,b.c_name,MAX(s_score),MIN(s_score),ROUND(AVG(s_score),2),
ROUND(100*(SUM(case when a.s_score>=60 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 及格率,
ROUND(100*(SUM(case when a.s_score>=70 and a.s_score<=80 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 中等率,
ROUND(100*(SUM(case when a.s_score>=80 and a.s_score<=90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 優(yōu)良率,
ROUND(100*(SUM(case when a.s_score>=90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 優(yōu)秀率
from score a left join course b on a.c_id = b.c_id GROUP BY a.c_id,b.c_name

– 19、按各科成績(jī)進(jìn)行排序，并顯示排名(實(shí)現(xiàn)不完全)
– mysql沒有rank函數(shù)

select a.s_id,a.c_id,
@i:=@i +1 as i保留排名,
@k:=(case when @score=a.s_score then @k else @i end) as rank不保留排名,
@score:=a.s_score as score
from (
select s_id,c_id,s_score from score WHERE c_id=‘01’ GROUP BY s_id,c_id,s_score ORDER BY s_score DESC )a,(select
@k:=0,@i:=0,@score:=0)s
union
select a.s_id,a.c_id,
@i:=@i +1 as i,
@k:=(case when @score=a.s_score then @k else @i end) as rank,
@score:=a.s_score as score
from (
select s_id,c_id,s_score from score WHERE c_id=‘02’ GROUP BY s_id,c_id,s_score ORDER BY s_score DESC )a,(select
@k:=0,@i:=0,@score:=0)s
union
select a.s_id,a.c_id,
@i:=@i +1 as i,
@k:=(case when @score=a.s_score then @k else @i end) as rank,
@score:=a.s_score as score
from (
select s_id,c_id,s_score from score WHERE c_id=‘03’ GROUP BY s_id,c_id,s_score ORDER BY s_score DESC )a,(select
@k:=0,@i:=0,@score:=0)s

– 20、查詢學(xué)生的總成績(jī)并進(jìn)行排名

select a.s_id,
@i:=@i+1 as i,
@k:=(case when @score=a.sum_score then @k else @i end) as rank,
@score:=a.sum_score as score from (select s_id,SUM(s_score) as sum_score from score GROUP BY s_id ORDER BY sum_score DESC)a,
(select @k:=0,@i:=0,@score:=0)s

2.3 SQL 21-30

– 21、查詢不同老師所教不同課程平均分從高到低顯示

select a.t_id,c.t_name,a.c_id,ROUND(avg(s_score),2) as avg_score from course a
left join score b on a.c_id=b.c_id
left join teacher c on a.t_id=c.t_id
GROUP BY a.c_id,a.t_id,c.t_name ORDER BY avg_score DESC;
1
2
3
4
– 22、查詢所有課程的成績(jī)第2名到第3名的學(xué)生信息及該課程成績(jī)

select d.,c.排名,c.s_score,c.c_id from (
select a.s_id,a.s_score,a.c_id,@i:=@i+1 as 排名 from score a,(select @i:=0)s where a.c_id=‘01’
)c
left join student d on c.s_id=d.s_id
where 排名 BETWEEN 2 AND 3
UNION
select d.,c.排名,c.s_score,c.c_id from (
select a.s_id,a.s_score,a.c_id,@j:=@j+1 as 排名 from score a,(select @j:=0)s where a.c_id=‘02’
)c
left join student d on c.s_id=d.s_id
where 排名 BETWEEN 2 AND 3
UNION
select d.*,c.排名,c.s_score,c.c_id from (
select a.s_id,a.s_score,a.c_id,@k:=@k+1 as 排名 from score a,(select @k:=0)s where a.c_id=‘03’
)c
left join student d on c.s_id=d.s_id
where 排名 BETWEEN 2 AND 3;

– 23、統(tǒng)計(jì)各科成績(jī)各分?jǐn)?shù)段人數(shù)：課程編號(hào),課程名稱,[100-85],[85-70],[70-60],[0-60]及所占百分比

select distinct
f.c_name,a.c_id,b.85-100,b.百分比,c.70-85,c.百分比,d.60-70,d.百分比,e.0-60,e.百分比
from score a
left join (select c_id,SUM(case when s_score >85 and s_score <=100 then 1 else 0 end) as 85-100,
ROUND(100*(SUM(case when s_score >85 and s_score <=100 then 1 else 0 end)/count()),2) as 百分比
from score GROUP BY c_id)b on a.c_id=b.c_id
left join (select c_id,SUM(case when s_score >70 and s_score <=85 then 1 else 0 end) as 70-85,
ROUND(100(SUM(case when s_score >70 and s_score <=85 then 1 else 0 end)/count()),2) as 百分比
from score GROUP BY c_id)c on a.c_id=c.c_id
left join (select c_id,SUM(case when s_score >60 and s_score <=70 then 1 else 0 end) as 60-70,
ROUND(100(SUM(case when s_score >60 and s_score <=70 then 1 else 0 end)/count()),2) as 百分比
from score GROUP BY c_id)d on a.c_id=d.c_id
left join (select c_id,SUM(case when s_score >=0 and s_score <=60 then 1 else 0 end) as 0-60,
ROUND(100(SUM(case when s_score >=0 and s_score <=60 then 1 else 0 end)/count(*)),2) as 百分比
from score GROUP BY c_id)e on a.c_id=e.c_id
left join course f on a.c_id = f.c_id

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

select a.s_id,
@i:=@i+1 as ‘不保留空缺排名’,
@k:=(case when @avg_score=a.avg_s then @k else @i end) as ‘保留空缺排名’,
@avg_score:=avg_s as ‘平均分’
from (select s_id,ROUND(AVG(s_score),2) as avg_s from score GROUP BY s_id)a,(select @avg_score:=0,@i:=0,@k:=0)b;

– 25、查詢各科成績(jī)前三名的記錄
– 1.選出b表比a表成績(jī)大的所有組
– 2.選出比當(dāng)前id成績(jī)大的 小于三個(gè)的

select a.s_id,a.c_id,a.s_score from score a left join score b on a.c_id = b.c_id and a.s_score<b.s_scoregroup by a.s_id,a.c_id,a.s_score HAVING COUNT(b.s_id)<3ORDER BY a.c_id,a.s_score DESC

1
2
3
4
– 26、查詢每門課程被選修的學(xué)生數(shù)

select c_id,count(s_id) from score a GROUP BY c_id

1
– 27、查詢出只有兩門課程的全部學(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);
1
2
– 28、查詢男生、女生人數(shù)

select s_sex,COUNT(s_sex) as 人數(shù)  from student GROUP BY s_sex

1
– 29、查詢名字中含有"風(fēng)"字的學(xué)生信息

select * from student where s_name like '%風(fēng)%';

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

select a.s_name,a.s_sex,count(*) from student a  JOIN student b on a.s_id !=b.s_id and a.s_name = b.s_name and a.s_sex = b.s_sex
GROUP BY a.s_name,a.s_sex

1
2
3

2.4 SQL 31-40

– 31、查詢1990年出生的學(xué)生名單

select s_name from student where s_birth like ‘1990%’
1
– 32、查詢每門課程的平均成績(jī)，結(jié)果按平均成績(jī)降序排列，平均成績(jī)相同時(shí)，按課程編號(hào)升序排列

select c_id,ROUND(AVG(s_score),2) as avg_score from score GROUP BY c_id ORDER BY avg_score DESC,c_id ASC
1
– 33、查詢平均成績(jī)大于等于85的所有學(xué)生的學(xué)號(hào)、姓名和平均成績(jī)

select a.s_id,b.s_name,ROUND(avg(a.s_score),2) as avg_score from score a
left join student b on a.s_id=b.s_id GROUP BY s_id HAVING avg_score>=85
1
2
– 34、查詢課程名稱為"數(shù)學(xué)"，且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù)

select a.s_name,b.s_score from score b LEFT JOIN student a on a.s_id=b.s_id where b.c_id=(select c_id from course where c_name ='數(shù)學(xué)') and b.s_score<60

1
2
– 35、查詢所有學(xué)生的課程及分?jǐn)?shù)情況；

select a.s_id,a.s_name,
SUM(case c.c_name when ‘語文’ then b.s_score else 0 end) as ‘語文’,
SUM(case c.c_name when ‘?dāng)?shù)學(xué)’ then b.s_score else 0 end) as ‘?dāng)?shù)學(xué)’,
SUM(case c.c_name when ‘英語’ then b.s_score else 0 end) as ‘英語’,
SUM(b.s_score) as ‘總分’
from student a left join score b on a.s_id = b.s_id
left join course c on b.c_id = c.c_id
GROUP BY a.s_id,a.s_name

– 36、查詢?nèi)魏我婚T課程成績(jī)?cè)?0分以上的姓名、課程名稱和分?jǐn)?shù)；

select a.s_name,b.c_name,c.s_score from course b left join score c
on b.c_id = c.c_id
left join student a on a.s_id=c.s_id where c.s_score>=70

– 37、查詢不及格的課程

select a.s_id,a.c_id,b.c_name,a.s_score from score a left join course
b on a.c_id = b.c_id
where a.s_score<60

–38、查詢課程編號(hào)為01且課程成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和姓名；

select a.s_id,b.s_name from score a LEFT JOIN student b on a.s_id = b.s_id
where a.c_id = ‘01’ and a.s_score>80
1
2
– 39、求每門課程的學(xué)生人數(shù)

select count(*) from score GROUP BY c_id;

– 40、查詢選修"張三"老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生信息及其成績(jī)

– 查詢老師id
select c_id from course c,teacher d where c.t_id=d.t_id and d.t_name=‘張三’
– 查詢最高分（可能有相同分?jǐn)?shù)）
select MAX(s_score) from score where c_id=‘02’
– 查詢信息
select a.*,b.s_score,b.c_id,c.c_name from student a
LEFT JOIN Score b on a.s_id = b.s_id
LEFT JOIN course c on b.c_id=c.c_id
where b.c_id =(select c_id from course c,Teacher d where c.t_id=d.t_id and d.t_name=‘張三’)
and b.s_score in (select MAX(s_score) from Score where c_id=(select c_id from course c,Teacher d where c.t_id=d.t_id and
d.t_name=‘張三’))

2.5 SQL 41-50

– 41、查詢不同課程成績(jī)相同的學(xué)生的學(xué)生編號(hào)、課程編號(hào)、學(xué)生成績(jī)

select DISTINCT b.s_id,b.c_id,b.s_score from score a,score b where a.c_id != b.c_id and a.s_score = b.s_score
1
– 42、查詢每門功成績(jī)最好的前兩名
– 牛逼的寫法

select a.s_id,a.c_id,a.s_score from Score a
where (select COUNT(1) from Score b where b.c_id=a.c_id and b.s_score>=a.s_score)<=2 ORDER BY a.c_id

– 43、統(tǒng)計(jì)每門課程的學(xué)生選修人數(shù)（超過5人的課程才統(tǒng)計(jì)）。要求輸出課程號(hào)和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號(hào)升序排列

select c_id,count(*) as total from score GROUP BY c_id HAVING total>5
ORDER BY total,c_id ASC

– 44、檢索至少選修兩門課程的學(xué)生學(xué)號(hào)

select s_id,count(*) as sel from score GROUP BY s_id HAVING sel>=2

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

select * from student where s_id in(        select s_id from score GROUP BY s_id HAVING count(*)=(select count(*) from course))

1
2
–46、查詢各學(xué)生的年齡
– 按照出生日期來算，當(dāng)前月日 < 出生年月的月日則，年齡減一

select s_birth,(DATE_FORMAT(NOW(),’%Y’)-DATE_FORMAT(s_birth,’%Y’) -
(case when DATE_FORMAT(NOW(),’%m%d’)>DATE_FORMAT(s_birth,’%m%d’) then 0 else 1
end)) as age
from student;

– 47、查詢本周過生日的學(xué)生

select * from student where WEEK(DATE_FORMAT(NOW(),‘%Y%m%d’))=WEEK(s_birth)
select * from student where YEARWEEK(s_birth)=YEARWEEK(DATE_FORMAT(NOW(),‘%Y%m%d’))
select WEEK(DATE_FORMAT(NOW(),‘%Y%m%d’))
1
2
3
– 48、查詢下周過生日的學(xué)生

select * from student where WEEK(DATE_FORMAT(NOW(),‘%Y%m%d’))+1 =WEEK(s_birth)
1
– 49、查詢本月過生日的學(xué)生

select * from student where MONTH(DATE_FORMAT(NOW(),’%Y%m%d’))
=MONTH(s_birth)

– 50、查詢下月過生日的學(xué)生

select * from student where MONTH(DATE_FORMAT(NOW(),‘%Y%m%d’))+1 =MONTH(s_birth)