DQL資料查詢語句使用範例

-- 查詢所有的學生  select 欄位 from 表
select * from student
-- 查詢指定欄位
select studentno,studentname from student
-- 給結果起一個名字 ， As，可以給欄位起別名，也可以給表起別名
select studentno as 學號,studentname as 學生姓名 from student
select studentno 學號,studentname 學生姓名 from student
-- 函數 concat（a，b）
select CONCAT('姓名：',studentname) as 新名字 from student

-- 查詢一下有哪些同學參加了考試
select * from result -- 查詢全部的考試成績
select studentno from result -- 查詢有哪些學生參加了考試
select distinct studentno from result -- 去重

select version() -- 查詢系統版本號（函數）
select 100*3-2 -- 用來計算（表示式）
select @@auto_increment_increment -- 查詢自增的步長（變數）
-- 學生考試成績+1分檢視
select studentno,studentresult+1 as '提分後' from result

select studentno,studentresult from result
-- 查詢考試成績在95~100之間的
-- and &&
select studentno,studentresult from result 
where studentresult>=95 and studentresult<=100
-- 模糊查詢（區間）
select studentno,studentresult from result 
where studentresult between 95 and 100
-- ! not
select studentno,studentresult from result 
where not studentno=1000

-- ===========模糊查詢========================
-- 查詢姓張的同學
-- like結合%（代表0到任意個字元） _（一個字元）
select studentno,studentname from student
where studentname like '張%'
-- 查詢姓張的同學，名字只有兩個字
select studentno,studentname from student
where studentname like '張_'
-- ======in（具體的一個或者多個值）========
-- 查詢學號1000,1001的學生
select studentno,studentname from student
where studentno in(1000,1001)
-- 查詢在北京的學生
select studentno,studentname from student
where address in('北京朝陽')
-- ========null not null====================
-- 查詢地址為空的同學 null或者''
select studentno,studentname from student
where address='' or address is null
-- 查詢出生日期不為空的學生
select studentno,studentname from student
where borndate is not null

 -- 查詢參加考試的同學（學號，姓名，科目編號，分數）
select * from student
select * from result
/*思路：
1.分析需求：分析查詢的欄位來自哪些表（連線查詢）
2.確定使用哪種連線查詢？ 7種
  確定交叉點，這兩個表中哪些資料是相同的
	判斷的條件：學生表的studentno=成績表 studentno	
*/
select s.studentno,studentname,subjectno,studentresult 
from student as s
inner join  result as r
where s.studentno=r.studentno
-- right join 
select s.studentno,studentname,subjectno,studentresult 
from student as s
right join result as r
on s.studentno = r.studentno
-- left join 
select s.studentno,studentname,subjectno,studentresult 
from student as s
left join result as r
on s.studentno = r.studentno

-- ===============聯表查詢==================
 -- 查詢參加考試的同學（學號，姓名，科目編號，分數）
select * from student
select * from result
/*思路：
1.分析需求：分析查詢的欄位來自哪些表（連線查詢）
2.確定使用哪種連線查詢？ 7種
  確定交叉點，這兩個表中哪些資料是相同的
	判斷的條件：學生表的studentno=成績表 studentno	
*/
-- join（連線的表） on（判斷的條件） 連線查詢
-- where 等值查詢
select s.studentno,studentname,subjectno,studentresult 
from student as s
inner join  result as r
where s.studentno=r.studentno
select s.studentno,studentname,subjectno,studentresult 
from student as s
inner join  result as r
on s.studentno=r.studentno
-- right join 
select s.studentno,studentname,subjectno,studentresult 
from student as s
right join result as r
on r.studentno = s.studentno
-- left join 
select s.studentno,studentname,subjectno,studentresult 
from student as s
left join result as r
on s.studentno = r.studentno
-- 查詢缺考的同學(只要讓成績為null，就能找到）
select s.studentno,studentname,subjectno,studentresult 
from student as s
left join result as r
on s.studentno = r.studentno where studentresult is null
-- 查詢了參加考試的同學資訊：學號，學生姓名，科目名稱，分數
-- 表 student result subject
-- 右查詢
-- 交叉點：學生表 studentno=成績表 studentno  
--         成績表 subjectno=科目表 subjectno
select s.studentno,studentname,sub.subjectname,studentresult
from student as s
right join result as r
on r.studentno=s.studentno
inner join `subject` as sub
on r.subjectno=sub.subjectno
-- 我要查詢哪些資料 select ...
-- 從哪幾個表中查詢 from 表 xxx join 連線的表 on 較差條件
-- 假設存在一種多張表查詢，先查詢兩張表開始，然後再慢慢增加
-- from a left join b
-- from a right join b

-- 查詢父子資訊:把一張表看為兩個一模一樣的表
select a.categoryname as '父欄目',b.categoryname as '子欄目'
from category as a,category as b
where a.categoryid = b.pid

-- 排序：升序 ASC  降序DESC
-- 通過哪個欄位排序，怎麼排
select s.studentno,studentname,subjectname,studentresult
from student as s
inner join result as r
on s.studentno = r.studentno
inner join `subject` as sub
on sub.subjectno=r.subjectno
where subjectname = '高等數學-1'
order by studentresult DESC

-- 為什麼要分頁
-- 緩解資料庫的壓力，給人更好的體驗  ---瀑布流
-- 分頁，每頁顯示五條資料
-- 語法：limit 起始值，頁面的大小
-- 網頁應用：當前頁，總頁數，頁面的大小
-- limit 0,5 1~5條資料
-- limit 1,5 2~6條資料
select * from student limit 0,5
-- 第一頁 limit 0,5   （1-1）*5
-- 第二頁 limit 5,5   （2-1）*5
-- 第二頁 limit 10,5  （3-1）*5
-- 第N頁  limit       （n-1）*5
-- pageSize：頁面的大小
-- （n-1）*pageSize：起始值
-- n：當前頁
-- 資料總數/頁面大小=總頁數

-- 查詢高等數學-1的所有考試結果（學號，科目編號，成績），降序排列
-- 方式1
select studentno,subjectno,studentresult
from result as r
inner join `subject` as sub
on r.subjectno=sub.subjectno
where subjectname='高等數學-1'
order by studentresult DESC
-- 方式2：使用子查詢
select studentno,subjectno,studentresult
from result
where subjectno=(
	select subjectno from `subject` 
	where  subjectname='高等數學-1'
)order by studentresult DESC
-- 查詢所有高等數學-1的學生的學號
select subjectno from `subject` where subjectname='高等數學-1'
-- 查詢分數不小於80分的學生的學號和姓名
select studentno,studentname
from student 
where studentno=(
select distinct studentno from result where studentresult>=80
)
-- 查詢課程為高等數學-2並且分數不小於80分的同學的學號和姓名
-- 聯表查詢
select s.studentno,studentname
from student as s
inner join result as r
on s.studentno=r.studentno
inner join `subject` as sub
on r.subjectno=sub.subjectno
where subjectname='高等數學-2' and studentresult>=80
-- 子查詢
select DISTINCT s.studentno,studentname
from student as s
inner join result as r
on s.studentno=r.studentno
where studentresult>=80 and subjectno=(
	select subjectno from `subject` where subjectname='高等數學-2'
)
-- 子查詢：（由裡及外）
select studentno,studentname
from student where studentno in(
	select studentno from result where studentresult>=80 and subjectno=(
		select subjectno from `subject` where subjectname='高等數學-2'
	)
)