1 限制輸出結(jié)果條數(shù) 數(shù)據(jù)第一行是0
select * from 表名 limit 5，5 ; 從行5開始的5行? 第一個數(shù)為開始的位置，第二個數(shù)為要檢索的位置
2 單列降序
select? 列名1，列名2 from表名 order by? 列名1 desc
3 區(qū)間范圍值檢查：
select lie1,lie 2 from student where lie1 betewen 5 and 10;? 帶上關(guān)鍵字? and? 包含5和10
4? 查詢次序
SELECT * from SC where (Cno = 'c01' OR Cno = 'c02' ) AND Grade >88? 優(yōu)先級 ()> and >or
5? 查詢范圍 IN ;NOT IN?
select? lie1 ,lie2 from? biao where lie3? in? （1002,1003）? order by lie1
6 select? lie1 ,lie2 from? biao where lie2? like 'jet%' 檢索任意 jet 起頭的詞。
? select? lie1 ,lie2 from? biao where lie2? like '%@jet%'? 匹配任何位置包含文本@jet%
7? select count（*) as num_cust from customers? 利用count（*） 計算customer表中客戶的總數(shù)
8? select sum (item_price*quantity) as total_price? from orderitems where order_num=2005? (item_price*quantity) 用來計算所有物品價錢之和? ，WHERE同樣保證只統(tǒng)計某個物品訂單中的物品?
select sum(Grade) from sc where Sno='9512101' 統(tǒng)計總和
9 分組? select? Sno,COUNT(*) as num from SC GROUP BY Sno
count（*）表中行的數(shù)目計數(shù)，不管表列中包含的是空值還是非空值。
count（column）對特定列中具有值得行進行計數(shù)，忽略NULL值
10? having?
select? cust_id,count(*) as orders from orders group by cust_id having count(*)>=2? 基于聚集分組值而不是特定行值
select? Sno,COUNT(*) as num from SC GROUP BY Sno having num >3
11? 排序? select? Sno,COUNT(*) as num from SC GROUP BY Sno having num >1 ORDER BY num desc
12? 子查詢
作用：利用子查詢進行過濾 使用的數(shù)據(jù)庫關(guān)系表都是關(guān)系表? 訂單存儲在兩個表中:
查詢考試成績不及格的學(xué)生的姓名
select sname? from student? where student .sno in (select sno from sc where sc.grade <60 )
13 嵌套查詢
查詢選修了課程“c02”且成績高于此課程的平均成績的學(xué)生的學(xué)號和成績
SELECT sno,grade FROM sc WHERE cno='c02' AND grade> (
SELECT avg(IFNULL(Grade,"0")) AS '平均成績' FROM sc WHERE cno='c02')
SELECT sno,sname FROM student WHERE sno IN (
SELECT sno FROM sc WHERE cno IN (
SELECT cno FROM course WHERE Cname='數(shù)據(jù)庫基礎(chǔ)'))
查詢選修了劉晨沒有選修的課程的學(xué)生的學(xué)號和所在系
SELECT Sno,Sdept FROM Student WHERE Sno IN (
SELECT Sno FROM SC WHERE Cno NOT IN (
SELECT Cno FROM SC WHERE Sno=(
SELECT Sno FROM Student WHERE Sname='劉晨')))
14? 去重
SELECT DISTINCT sno? from sc
15? 以Class降序查詢Student表的所有記錄
select * from student ORDER BY class DESC
