基礎解析：
select 要幾列
where要幾行，用來對行進行過濾，加where,查出來的行變少
*代表所有的列
增刪改查
UPDATE SET 更新
UPDATE scores SET score=300 WHERE NAME="王大" AND kemu="語文"
delete語法
DELETE FROM scores WHERE ID =11
#刪除整張表裡的資料
DELETE from ceshi
#刪除張三的這一行
DELETE from ceshi WHERE Name ="張三"
INsert into values插入
插入為空
INSERT INTO ceshi VALUES (9,"李二四","天津",62,NULL,"英語","男")
LIMIT1
取第一列資料
where 狀態="單生" order by score DESC LIMIT1
LIMIT高階用法
從第幾條開始，取幾條
where 狀態="單生" order by score DESC LIMIT2,,5
order by排序
ASC DESC
（where和order by之間不需要AND）
where 狀態="單生" order by score(預設ASC升序)
where 狀態="單生" order by score DESC（降序）
多條件用AND連線
（AND是必須同時滿足所有條件）
列名="語文" AND 列名=“19”
或者OR
（OR是必須同時任意滿足條件）
列名="語文" OR 列名=“19”
Between and
19到29
列名 Between 19 and 29
不等於
<>
SELECT*FROM ceshi where kemu <>"英語"
大於等於
SELECT * FROM studentliu WHERE Age >=25
模糊查詢like%
列名 LIKE "張%"
包含like
列名 LIKE "%張%"
不包含not like
列名 not LIKE "%張%"
多條件in
列名 IN（"1","2","3"）
not in排除（以外）
列名 not in('1')
查重
distinca 列名
為空
列名 is NULL
非空
is not NULL
聚合函式
MAX(列名)最大值
SELECT kemu,MAX(score) FROM Scores1 where name = "張三"
MIN(列名) 最小值
SELECT kemu,MIN(score) FROM Scores1 where name = "張三"
SUM(列名)求和
SELECT name,SUM(score) FROM scores WHERE name ="王大"
AVG（列名）求平均
SELECT kemu,AVG(score) FROM scores WHERE kemu="語文"
Count統計行數
SELECT COUNT(*) FROM scores WHERE kemu="語文" AND score <60
括號內容沒搞懂，好像並沒有意義，不是別名，也不是新值，列名的話無意義（）*
full join全連結
SELECT * FROM liancha1 full JOIN liancha2 on liancha1.ID=liancha2.ID
左連線右連結
SELECT * FROM liancha1 right JOIN liancha2 on liancha1.ID=liancha2.ID
SELECT * FROM liancha1 left JOIN liancha2 on liancha1.ID=liancha2.ID
inner join內連線
求張三的考試情況（兩表聯查）
SELECT name,kemu,score FROM liancha1 INNER JOIN liancha2 on liancha1.ID=liancha2.ID
WHERE name="張三"
兩表求和+分組
SELECT name, SUM(score)from liancha1 INNER JOIN liancha2 on liancha1.ID = liancha2.ID
GROUP BY name
兩表+分組+Where+HAVing
SELECT name, AVG (score)from liancha1 INNER JOIN liancha2 on liancha1.ID = liancha2.ID
WHERE sex = "女" GROUP BY name HAVing AVG (score) >60
主鍵和外來鍵
兩表聯查一定要知道主鍵和外來鍵的區別
一個表中的某一列，指向另一個表中唯一一列（主鍵列）這種列就稱之為外來鍵
在左右連線的時候，就要注意是以哪個表為主
HAVing+Where
SELECT NAME,AVG(score) FROM ceshi WHERE sex="女"
GROUP BY name HAVing AVG(score)>60
兩表聯查
兩表INNER join+統計行數count（*）+分組group by+OR/and發生錯誤
#統計考了語文和數學的女生人數
SELECT kemu,COUNT(*)總人數 FROM liancha1 A INNER JOIN liancha2 B ON A.SID=B.ID
WHERE sex="女"AND (kemu="語文" OR kemu="數學")
GROUP BY kemu
兩表INNER join+最低MIN（*）+分組group by
#求男生和女生的最低分
select sex,MIN(Score)最低分 FROM liancha1 A INNER JOIN liancha2 B ON A.SID=B.ID
WHERE sex="男" OR sex="女"
GROUP BY sex
列名衝突，需要以列表名.列名的形式指出來
SELECT liancha1.name,liancha2.name,score FROM liancha1 INNer JOIN liancha2 on liancha1.SID=liancha2.ID
WHERE liancha1.name="張三"
列名不一樣的連結
SELECT * FROM liancha1 INNer JOIN liancha2 on liancha1.SID=liancha2.ID
as 新名（別名）
SELECT sex ,name,SUM(Score) as總分 FROM ceshi
WHERE SEX ="女" GROUP BY Name
group by 分組
group by+聚合函式
考過1門以上的學生的資訊
SELECT name,count()考試次數 from ceshi group by name HAVING COUNT()>2
每門課都大於60分的學生姓名
子查詢
SELECT DISTINCT name from ceshi
WHERE name not in (SELECT DISTINCT name FROM ceshi where score<60)
分組+聚合函式
SELECT name,score from ceshi GROUP BY name HAVING MIN(score)>60
三表聯查
三表聯查+MAX+分組
#查詢每班分數最高的學生
SELECT banjiname,name,MAX(score)分數
FROM student-class a INNER JOIN class b ON a.banjiID = b.banjiID
INNER JOIN students c ON a.studentID = c.studentID
GROUP BY banjiname
三表聯查+聚合函式+分組
#查詢班級名稱、和所有班中的女生的人數和平均分
SELECT banjiname,sex,count(*)人數,AVG(score)平均分
FROM student-class a INNER JOIN class b ON a.banjiID = b.banjiID
INNER JOIN students c ON a.studentID = c.studentID
WHERE sex="女"
GROUP BY banjiname
普通三表聯查
#查詢得分在60分以上的學生，顯示姓名、性別、班級名稱、分數
SELECT name,sex,banjiname,score
FROM student-class a INNER JOIN class b ON a.banjiID = b.banjiID
INNER JOIN students c ON a.studentID = c.studentID c.
WHERE score >60
group by （對非聚合函式）再排序
SELECT kemu,AVG(score) FROM ceshi WHERE sex="女" GROUP BY kemu
group by (再對聚合函式做操作)
求考了兩個科目以上的學生姓名
SELECT name FROM ceshi GROUP BY Name HAVing Count(*)>2
group by （聚合函式）再排序
SELECT NAME,AVG(score) FROM ceshi GROUP BY name HAVing AVG(score)>70
group by 分組（再求最平均）
SELECTNAME,AVG(score) FROM ceshi GROUP BY name
group by 分組（再求和）
SELECTNAME,SUM(score) FROM ceshi GROUP BY name
group by 分組（再求最小/大值）
SELECT kemu, NAME,MAX(score) FROM ceshi GROUP BY kemu
SELECT kemu, NAME,MIN(score) FROM ceshi GROUP BY kemu
子查詢
語文分數>語文分數平均分的學生資訊
這是一個比較繞的題，我們需要先求出語文平均分數是多少
SELECT * FROM liancha2 WHERE kemu ="語文" AND score>(SELECT AVG(score)from liancha2 WHERE kemu="語文")
Not in 不包含子查詢
SELECT NAME,score FROM ceshi
WHERE name not in (SELECT name FROM ceshi WHERE score<60 )
in like（%%）子查詢包含
SELECT kemu,score FROM scores
WHERE kemu ="語文" AND id in (SELECT id FROM zlcstudent WHERE address LIKE "%周口%")
子查詢不包含not in like（%%）
SELECT kemu,score FROM scores
WHERE kemu ="語文" AND id not in (SELECT id FROM zlcstudent WHERE address LIKE "%周口%")
子查詢=
SELECT * FROM scores WHERE score >5 AND id = (SELECT id FROM zlcstudent WHERE address = "周口")
子查詢in
SELECT * FROM scores WHERE score >5 AND id in (SELECT id FROM zlcstudent WHERE address = "周口")
