數據表
--1.學生表 Student(SId,Sname,Sage,Ssex)
SId 學生編號,Sname 學生姓名,Sage 出生年月,Ssex 學生性別
--2.課程表 Course(CId,Cname,TId)
CId 課程編號,Cname 課程名稱,TId 教師編號
--3.教師表 Teacher(TId,Tname)
TId 教師編號,Tname 教師姓名
--4.成績表 SC(SId,CId,score)
SId 學生編號,CId 課程編號,score 分數
創建測試數據
學生表 Student：
導入數據方法：將以下 mysql 語句，完整復制到 workbench 語句窗口（或者是 mysql 的黑窗口），然後運行即可導入，不需要另外創建表，下面表的操作壹樣。這些語句第壹條是創建表（create table），後面都是插入數據到表中（insert into table ）。
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '趙雷' , '1990-01-01' , '男');
insert into Student values('02' , '錢電' , '1990-12-21' , '男');
insert into Student values('03' , '孫風' , '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('09' , '張三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2017-12-30' , '女');
insert into Student values('12' , '趙六' , '2017-01-01' , '女');
insert into Student values('13' , '孫七' , '2018-01-01' , '女');
科目表 Course
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '語文' , '02');
insert into Course values('02' , '數學' , '01');
insert into Course values('03' , '英語' , '03');
教師表 Teacher
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '張三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
成績表 SC
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
1.查詢" 01 "課程比" 02 "課程成績高的學生的信息及課程分數
select * from sc a left join student b
on a.sid=b.sid
inner join sc c
on a.cid !=c.cid and a.cid='01' and c.cid='02'
where a.score>c.score;
<meta charset="utf-8">
1.1. 查詢同時存在" 01 "課程和" 02 "課程的情況
select * from sc a inner join sc b
on a.sid=b.sid
and a.cid = '01' and b.cid = '02';
1.2. 查詢存在" 01 "課程但可能不存在" 02 "課程的情況(不存在時顯示為 null )
select * from sc a left join sc b
on a.sid=b.sid
and b.Cid='02' where a.Cid='01';
1.3. 查詢不存在" 01 "課程但存在" 02 "課程的情況
select * from sc
where sid not in (select sid from sc where Cid='01')
and Cid='02';
2.查詢平均成績大於等於 60 分的同學的學生編號和學生姓名和平均成績
select s.Sid,Sname,avg(score) from student s left join sc
on s.Sid=sc.Sid
group by s.Sid having avg(score)>=60;
on s.sid=sc.sid group by s.sid;
select s.* from sc left join student s on s.sid=sc.sid where cid in (select cid from
course where tid=(select tid from teacher where tname='張三')) group by sid;
9.查詢和" 01 "號的同學學習的課程完全相同的其他同學的信息
(沒有1和2的。因為"IN"的作用可以篩選與條件完全相同，或者有其中壹個的）
SELECT s.*
FROM student s
INNER JOIN sc
ON s.sid = sc.sid
WHERE s.sid NOT IN(
SELECT sid
FROM sc #篩選沒有'01'同學的課程的同學sid
WHERE cid NOT IN (
SELECT cid
FROM sc
WHERE sid = '01'))
AND NOT s.sid = '01'
GROUP BY s.sid
HAVING COUNT(cid) = (
SELECT COUNT(1)
FROM sc
WHERE sid ='01'
GROUP BY sid);
10.查詢沒學過"張三"老師講授的任壹門課程的學生姓名
SELECT Sname FROM student WHERE sid NOT IN (
SELECT sid
FROM sc
WHERE cid IN(
SELECT CID
FROM course c
INNER JOIN teacher t
ON c.Tid = t.Tid
WHERE Tname='張三'));
11.查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績
select s.sid,Sname,avg(score) from student as s
left join sc on s.sid=sc.sid where score<60
group by s.sid
having count(cid)>2;
12.檢索" 01 "課程分數小於 60，按分數降序排列的學生信息
select * from student as s left join sc on s.sid=sc.sid
where cid='01' and score<60
order by score desc;
15.按各科成績進行排序，並顯示排名， Score 重復時也繼續排名
select sid,cid,score, @rank:=@rank+1 as rn from sc ,(select @rank:=0) as t
order by score desc;
16.1. 查詢學生的總成績，並進行排名，總分重復時不保留名次空缺
select s.* ,
@rank:=if (@sco=scos,'',@rank+1) as rn,
@sco:=scos from
(select sid,sum(score) as scos
from sc group by sid order by scos desc) s,
(select @rank:=0,@sco:=null)as t;
擴展：賦值變量
第壹種方法：
set @a：=1；
第二種：
select @b:=2;
修改：select @b:=4;
select s.* ,
@rank:=if (@sco=scos,@rank,@rank+1) as rn,
@sco:=scos from
(select sid,sum(score) as scos
from sc group by sid order by scos desc) s,
(select @rank:=0,@sco:=null)as t;
19.查詢每門課程被選修的學生數
select cid,count(1) from sc
group by cid;
select * from sc left join student s on s.sid=sc.sid
left join course c on sc.cid=c.cid
left join teacher d on c.tid=d.tid where Tname='張三'
order by sc.score desc limit 1;
select * ,count( )from student s
inner join sc on s.sid=sc.sid
group by s.sid
having count(cid)=(select count( ) from course);
由於now（）壹直變，可能出現沒有數據集的生日日期，所以可以挑壹個數據集出現的數據放入
例如：select * ,
week(sage),
week(now())
from student
where week(sage)=week("2020-05-20");