-- 查詢所有學(xué)生的信息
select * from tb_student;

-- 查詢所有老師的信息
select * from tb_teacher;

-- 查詢所有課程的信息
select * from tb_course;

-- 查詢所有課程名稱及學(xué)分(投影和別名)
select couname as 課程名稱, coucredit as 學(xué)分 from tb_course;

-- 查詢學(xué)生的姓名和性別
select stuname as 姓名, stusex as 性別 from tb_student;

select stuname as 姓名, case stusex when 1 then '男' else '女' end as 性別 from tb_student;

select stuname as 姓名, if(stusex, '男', '女') as 性別 from tb_student;

-- 查詢所有女學(xué)生的姓名和出生日期(篩選)
select stuname, stubirth from tb_student where stusex=0;

-- 查詢所有80后學(xué)生的姓名、性別和出生日期(篩選)
select stuname, stusex, stubirth from tb_student where stubirth >= '1980-1-1' and stubirth <= '1989-12-31';

select stuname, stusex, stubirth from tb_student where stubirth between '1980-1-1' and '1989-12-31';

-- 查詢姓”楊“的學(xué)生姓名和性別(模糊)
select stuname, stusex from tb_student where stuname like '楊%';

-- 查詢姓”楊“名字兩個(gè)字的學(xué)生姓名和性別(模糊)
select stuname, stusex from tb_student where stuname like '楊_';

-- 查詢姓”楊“名字三個(gè)字的學(xué)生姓名和性別(模糊)
select stuname, stusex from tb_student where stuname like '楊__';

-- 查詢名字中有“不”字或“嫣”字的學(xué)生的姓名(模糊)
select stuname from tb_student where stuname like '%不%' or stuname like '%嫣%';

-- 查詢沒有錄入家庭住址的學(xué)生姓名(空值)
select stuname from tb_student where stuaddr is null;

-- 查詢錄入了家庭住址的學(xué)生姓名(空值)
select stuname from tb_student where stuaddr is not null;

-- 查詢學(xué)生選課的所有日期(去重)
select distinct seldate from tb_record;

-- 查詢學(xué)生的家庭住址(去重)
select distinct stuaddr from tb_student where stuaddr is not null;

-- 查詢男學(xué)生的姓名和生日按年齡從大到小排列(排序)
select stuname, stubirth from tb_student where stusex=1 order by stubirth asc, stuid desc;

select stuname as 姓名, datediff(curdate(), stubirth) div 365 as 年齡 from tb_student where stusex=1 order by 年齡 desc;

-- 查詢年齡最大的學(xué)生的出生日期(聚合函數(shù))
select min(stubirth) from tb_student;

-- 查詢年齡最小的學(xué)生的出生日期(聚合函數(shù))
select max(stubirth) from tb_student;

-- 聚合函數(shù) - max / min / sum / avg / count

-- 查詢學(xué)生總?cè)藬?shù)
select count(stuid) from tb_student;

-- 查詢男女學(xué)生的人數(shù)(分組和聚合函數(shù))
select if(stusex, '男', '女') as 性別, count(stuid) as 人數(shù) from tb_student group by stusex order by 人數(shù) desc;

-- 查詢課程編號(hào)為1111的課程的平均成績(jī)(篩選和聚合函數(shù))
select round(avg(score), 1) as 平均分 from tb_record where cid=1111;

-- 查詢學(xué)號(hào)為1001的學(xué)生所有課程的平均分(篩選和聚合函數(shù))
select round(avg(score), 1) as 平均分 from tb_record where sid=1001;

-- 查詢每個(gè)學(xué)生的學(xué)號(hào)和平均成績(jī)(分組和聚合函數(shù))
select sid as 學(xué)號(hào), round(avg(score), 1) as 平均分 from tb_record group by sid;

-- 查詢平均成績(jī)大于等于90分的學(xué)生的學(xué)號(hào)和平均成績(jī)
-- 注意：分組以前的篩選使用where子句 分組以后的篩選使用having子句
select sid as 學(xué)號(hào), round(avg(score), 1) as 平均分 from tb_record group by sid having 平均分>=90;

-- 查詢年齡最大的學(xué)生的姓名(子查詢)
-- 嵌套查詢：把一個(gè)查詢的查詢結(jié)果作為另外一個(gè)查詢的一部分來(lái)使用
select stuname from tb_student where stubirth=(
select min(stubirth) from tb_student
);

-- 查詢年齡最大的學(xué)生姓名和年齡(子查詢+運(yùn)算)
select stuname as 姓名, datediff(curdate(), stubirth) div 365 as 年齡 from tb_student where stubirth=(
select min(stubirth) from tb_student
);

-- 查詢選了兩門以上的課程的學(xué)生姓名(子查詢/分組條件/集合運(yùn)算)
select stuname from tb_student where stuid in (
select sid from tb_record
group by sid having count(sid)>2
);

-- 查詢學(xué)生姓名、課程名稱以及成績(jī)(連接查詢)
select stuname, couname, score from tb_student t1, tb_record t2, tb_course t3 where t1.stuid=t2.sid and t2.cid=t3.couid and score is not null;

select stuname, couname, score from tb_student inner join tb_record on stuid=sid inner join tb_course on couid=cid where score is not null;

-- 查詢選課學(xué)生的姓名和平均成績(jī)(子查詢和連接查詢)
select stuname, averagescore from tb_student as t1,
(select sid, avg(score) as averagescore from tb_record group by sid) as t2 where stuid=sid;

-- 查詢每個(gè)學(xué)生的姓名和選課數(shù)量(左外連接和子查詢)
select stuname, 選課數(shù)量 from tb_student,
(select sid, count(sid) as 選課數(shù)量 from tb_record group by sid) as t2 where stuid=sid;

-- 左外連接是指左邊的表里面不滿足條件的內(nèi)容如也給選出來(lái)
select stuname, ifnull(選課數(shù)量, 0) as 選課數(shù)量 from tb_student left outer join
(select sid, count(sid) as 選課數(shù)量 from tb_record group by sid) as t2 on stuid=sid limit 5 offset 3;

select stuname, ifnull(選課數(shù)量, 0) as 選課數(shù)量 from tb_student left outer join
(select sid, count(sid) as 選課數(shù)量 from tb_record group by sid) as t2 on stuid=sid limit 5, 3;