day 04 DQL數(shù)據(jù)庫查詢語言
上集回顧:
- create table like 只能復(fù)制表結(jié)構(gòu),那有什么命令可以連數(shù)據(jù)一起復(fù)制?
(1) 方法一:
create table stu select * from student;
主鍵等特性沒有被復(fù)制.
(2) 方法二:
create table st like student;
insert into st select * from student
pt 索引章節(jié)之后
pt-archivher
pt-osc主鍵是干什么用的
(1) 約束 : unique not null
(2) 聚簇索引索引: 組織和存儲數(shù)據(jù).
(3) 加速查詢update 和 delete 范圍操作
update t1 set xname='王二麻子' where xname like '王二%'
delete from t1 where xname like '王二%';
delete from t1 where xid >1 and xid <5 ;
delete from t1 where xid between 1 and 5 ;
===========================================
DQL 數(shù)據(jù)查詢語言. ---oldguo
- SELECT
1.1 SELECT 單獨使用 ***
(1) 查詢數(shù)據(jù)庫的參數(shù).
SELECT @@port;
SELECT @@datadir;
SELECT @@basedir;
SELECT @@innodb_flush_log_at_trx_commit;
SHOW VARIABLES LIKE '%trx%';
(2) 調(diào)用內(nèi)置函數(shù).
USE oldguo
SELECT DATABASE();
SELECT NOW();
SELECT CONCAT(USER,"@",HOST) FROM mysql.user;
SELECT GROUP_CONCAT(xid) FROM student;
SELECT SUM(xid) FROM student;
(3) 簡易計算器
SELECT 4*5;
-- 1.2 select 配合其他子句使用 *****
-- 1.2.1 子句列表介紹
FROM -- 查詢對象(表,視圖)
WHERE -- 過濾子句(grep)
GROUP BY -- 分組子句(統(tǒng)計分析類)
HAVING -- 后過濾子句
ORDER BY -- 排序子句
LIMIT -- 限制子句(分頁子句)
-- 1.2.2 配合FROM應(yīng)用
-- world 模板庫介紹
--- 英文單詞介紹
--- city -- 城市
--- id -- 序號ID主鍵
--- NAME -- 城市名
--- Countrycode -- 國家代碼(CHN,USA,JPN)
--- District -- 省,州
--- Population -- 城市人口數(shù)
-- 例子:
-- 1.查詢表中所有數(shù)據(jù)(cat)
SELECT * FROM city;
-- 2. 查詢name和population信息 (awk取列)
SELECT NAME,population FROM city;
-- 1.2.3 select+ from + where(grep)使用
-- where 配合等值查詢
-- 例子:
-- 1. 查詢中國所有的城市信息
SELECT * FROM city
WHERE countrycode='CHN';
-- 2. 查詢ID為100的城市信息
SELECT * FROM city
WHERE id=100;
-- 3. 查詢 中國河北省的城市信息
SELECT * FROM city
WHERE countrycode='CHN' AND district='hebei' ;
-- 4. 查詢 中國或者美國的城市
SELECT * FROM city
WHERE countrycode='CHN' OR countrycode='USA';
或者:
SELECT * FROM city
WHERE countrycode IN ('CHN','USA');
或者:
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. 查詢國家代號是CH開頭的城市信息
SELECT * FROM city
WHERE countrycode LIKE 'CH%';
-- 1.2.4 group by 分組子句+聚合函數(shù)應(yīng)用
-- 聚合函數(shù)?
COUNT() -- 計數(shù)
SUM() -- 求和
AVG() -- 求平均值
MAX() -- 求最大值
MIN() -- 最小值
GROUP_CONCAT() -- 聚合列值
-- 結(jié)果集顯示特點: 必須是1v1,不能是1vN
-- 例子 :
-- 1. 統(tǒng)計一下每個國家的人口總數(shù)
SELECT countrycode,SUM(population)
FROM city
GROUP BY countrycode;
-- 2. 統(tǒng)計中國每個省的人口總數(shù)
SELECT district,SUM(population) FROM city
WHERE countrycode='CHN'
GROUP BY district;
-- 3. 統(tǒng)計下中國每個省的城市個數(shù)及城市名.
SELECT district,COUNT(NAME),GROUP_CONCAT(NAME) FROM city
WHERE countrycode='CHN'
GROUP BY district;
-- 4. 統(tǒng)計每個國家城市個數(shù)
SELECT countrycode ,COUNT(NAME) FROM city
GROUP BY countrycode;
-- 1.2.6 having 后判斷
-- 1. 統(tǒng)計中國每個省的人口總數(shù),只顯示總?cè)丝跀?shù)大于500w的省信息.
SELECT district,SUM(population) FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population) >=5000000;
-- 1.2.7 order by 排序子句
-- 例子:
-- 1. 查詢中國所有城市信息,人口數(shù)從大到小排序輸出.
SELECT * FROM city
WHERE countrycode='CHN'
ORDER BY population DESC ;
-- 2. 查詢中國所有城市信息,按城市名排序.
SELECT * FROM city
WHERE countrycode='CHN'
ORDER BY NAME;
-- 3. 查詢中國所有省的總?cè)丝?并按總?cè)丝跀?shù)從大到小排序輸出.
SELECT district,SUM(population) FROM city
WHERE countrycode='CHN'
GROUP BY district
ORDER BY SUM(population) DESC;
-- 1.2.8 limit 分頁限制子句
-- 查詢中國所有省的總?cè)丝?并按總?cè)丝跀?shù)從大到小排序輸出.
SELECT district,SUM(population) FROM city
WHERE countrycode='CHN'
GROUP BY district
ORDER BY SUM(population) DESC
LIMIT 5 OFFSET 1;
SELECT district,SUM(population) FROM city
WHERE countrycode='CHN'
GROUP BY district
ORDER BY SUM(population) DESC
LIMIT 10;
SELECT district,SUM(population) FROM city
WHERE countrycode='CHN'
GROUP BY district
ORDER BY SUM(population) DESC
LIMIT 1,5;
注意: LIMIT 謹慎使用, 500w+的表.
LIMIT 5000000,100
一般會改為明確查找范圍
limit >xxxx and < xxxx;
