1.select
1.1 SELECT 單獨(dú)使用 ***
1.查詢數(shù)據(jù)庫(kù)的參數(shù)
SELECT @@port;
SELECT @@datadir;
SELECT @@basedir;
SELECT @@innodb_flush_log_at_trx_commit;
SHOW VARIABLES LIKE '%trx%';
SHOW VARIABLES LIKE '%dir%';
2.調(diào)用內(nèi)置函數(shù)
USE oldboy
SELECT DATABASE();
SELECT NOW()
SELECT CONCAT (USER,"@",HOST) FROM mysql.user;
SELECT GROUP_CONCAT(xid) FROM student;
SELECT SUM(xid) FROM student;
3.簡(jiǎn)易計(jì)算器
SELECT 4*5;
1.2SELECT 配合其他的句子使用
1.2.1 子句列表介紹
FROM -- 查詢對(duì)象（表，視圖）
WHERE -- 過(guò)濾子句（grep）
GROUP BY -- 分組子句（統(tǒng)計(jì)分析類）
HAVING -- 后過(guò)濾子句
ORDER BY -- 排序子句
LIMIT -- 限制子句（分頁(yè)子句）
1.2.2配合from使用
word模板庫(kù)介紹
--- city -- 城市
--- id -- 序號(hào)id主鍵
--- NAME -- 城市名
--- Countrycode -- 國(guó)家代碼（CHN USA JPN）
--- District -- 省局扶，州
--- Population -- 城市人口數(shù)
例子：
1.查詢表中所有數(shù)據(jù)(cat)
SELECT * FROM city;
2.查詢name和population的信息
SELECT NAME,Population FROM city;
1.2.3 select+from + where(grep)使用
where配合等值查詢
例子：
1.查詢中國(guó)所有的城市
SELECT * FROM city WHERE CountryCode='CHN';
SELECT * FROM city WHERE ID=100;
2.查詢中國(guó)河北省的城市信息
SELECT * FROM city
WHERE CountryCode='CHN' AND District='hebei';
3.查詢中國(guó)或者美國(guó)的城市
SELECT * FROM city
WHERE CountryCode='CHN' OR CountryCode='USA';
或者：
SELECT * FROM city
WHERE CountryCode IN ('CHN','USA');
或者：(用UNION ALL將上下的內(nèi)容鏈接起來(lái))
SELECT * FROM city
WHERE CountryCode='CHN'
UNION ALL
SELECT * FROM city
WHERE CountryCode='USA';
where配合范圍查詢
例子：
1.查詢?nèi)丝跀?shù)量小于100人的城市
SELECT * FROM city
WHERE Population<100;
2.查詢?nèi)丝跀?shù)量100w-200w之間的
SELECT * FROM city
WHERE Population>=1000000 AND Population<=2000000;
或者：
SELECT * FROM city
WHERE Population BETWEEN 1000000 AND 2000000;
3.國(guó)家代號(hào)為CH開頭的城市信息(like只能針對(duì)字符串)
SELECT * FROM city
WHERE CountryCode LIKE 'CH%';
1.2.4 group by 分組子句+聚合函數(shù)應(yīng)用
-- 聚合函數(shù)？
COUNT() -- 計(jì)數(shù)
SUM() -- 求和
AVG() -- 求平均值
MAX() -- 求最大值
MIN() -- 求最小值
GROUP_CONCAT() -- 聚合列值
結(jié)果集顯示特點(diǎn)：必須是1v1，不能是一對(duì)多
例子：
1.統(tǒng)計(jì)每個(gè)個(gè)國(guó)家的人口總數(shù)
SELECT CountryCode,SUM(Population)
FROM city
GROUP BY CountryCode;
2. 統(tǒng)計(jì)中國(guó)每個(gè)省的人口總數(shù)
SELECT District,SUM(Population)
FROM city WHERE CountryCode='CHN'
GROUP BY District;
3. 統(tǒng)計(jì)一下中國(guó)每個(gè)省的城市個(gè)數(shù)及城市名
SELECT CountryCode,COUNT(NAME),GROUP_CONCAT(NAME)
FROM city WHERE CountryCode='CHN'
GROUP BY District;
4.統(tǒng)計(jì)每個(gè)國(guó)家的城市個(gè)數(shù)
SELECT CountryCode,COUNT(NAME),GROUP_CONCAT(NAME)
FROM city
GROUP BY CountryCode;
1.2.5 having 后判斷
1. -- 統(tǒng)計(jì)中國(guó)每個(gè)省的人口總數(shù)，只顯示總?cè)丝跀?shù)大于500w的省信息
SELECT District,SUM(Population)
FROM city WHERE CountryCode='CHN'
GROUP BY District
HAVING SUM(Population) >=5000000
ORDER BY SUM(Population) DESC;
1.2.6 order by 排序子句
例子：
1.查詢中國(guó)城市信息并按人口數(shù)從大到小排序輸出顯示
SELECT * FROM city
WHERE CountryCode='CHN'
ORDER BY Population DESC;
2.查詢中國(guó)城市信息并按城市名排序輸出顯示
SELECT * FROM city
WHERE CountryCode='CHN'
ORDER BY NAME;
3.查詢中國(guó)所有省的總?cè)丝跀?shù)并按總?cè)丝跀?shù)從大到小排序輸出
SELECT District,SUM(Population)
FROM city WHERE CountryCode='CHN'
GROUP BY District
ORDER BY SUM(Population) DESC;
1.2.7 limit 分頁(yè)限制子句
1.查詢中國(guó)所有省的總?cè)丝跀?shù)并按總?cè)丝跀?shù)從大到小排序輸出,顯示某幾行
SELECT District,SUM(Population)
FROM city WHERE CountryCode='CHN'
GROUP BY District
HAVING SUM(Population) >=5000000
ORDER BY SUM(Population) DESC
LIMIT 10 OFFSET 1;
SELECT District,SUM(Population)
FROM city WHERE CountryCode='CHN'
GROUP BY District
HAVING SUM(Population) >=5000000
ORDER BY SUM(Population) DESC
LIMIT 5;
SELECT District,SUM(Population)
FROM city WHERE CountryCode='CHN'
GROUP BY District
HAVING SUM(Population) >=5000000
ORDER BY SUM(Population) DESC
LIMIT 3,7;
注意：limit 謹(jǐn)慎使用筹误， 500w+的表思犁。
LIMIT 5000000.100 性能極差
一般會(huì)改為明確范圍的查詢
