SQL基礎應用及元數(shù)據(jù)獲取.png
一肮蛹、DQL基礎應用
1.select 語句應用
1.1 select 的執(zhí)行邏輯
shell
select 列1卸奉，列2
from 表
where 條件
group by 條件
having 條件
order by 條件
limit 條件
1.2 select 單獨使用的情況（MySQL獨有）
（1） select @@參數(shù)名
SELECT @@datadir;
SELECT @@port;
SELECT @@socket;
SHOW VARIABLES;
SELECT @@innodb_flush_log_at_trx_commit;
1.3 select 函數(shù)
SELECT NOW();
SELECT DATABASE();
SELECT USER();
SELECT CONCAT("hello word")
SELECT USER,HOST FROM mysql.`user`
SELECT GROUP_CONCAT(USER,"@",HOST) FROM mysql.user;
2.from 使用
USE world;
SHOW TABLES
SELECT * FROM city
**3.where子句的使用****
`3.1 等值查詢`
-- 查詢中國城市信息
SELECT * FROM city WHERE countrycode='CHN';
`3.2 不等值查詢`
-- 查詢?nèi)丝跀?shù)量小于100人的
SELECT * FROM city WHERE population<100;
-- 查詢ID小于10的城市
SELECT * FROM city WHERE id<10;
-- 查詢不是中國的城市信息
SELECT * FROM city WHERE countrycode!='CHN';
`3.3 模糊查詢`
-- 查詢國家代號為CH打頭的城市信息
SELECT * FROM city WHERE countrycode LIKE 'CH%';
SELECT * FROM city WHERE countrycode LIKE '%CH%';
-- 注意： 避免使用 like 中前面帶%的模糊查詢
`3.4 邏輯連接符 （and，or）`
-- 查詢中國城市人口超過500w的城市信息
SELECT * FROM city WHERE countrycode ='CHN' AND population > 5000000;
-- 改城市名字
UPDATE city SET NAME='beijing' WHERE id=1891;
-- 查看山東省或者河北省的城市信息
SELECT * FROM city WHERE district='shandong' OR district='hebei'
-- 查詢?nèi)丝跀?shù)量在500w-600w的城市
SELECT * FROM city WHERE population>5000000 AND population<6000000;
-- BETWEEN and 區(qū)間
SELECT * FROM city WHERE population BETWEEN 1000000 BETWEEN 2000000;
`-- 3.6 where 配合 in 使用`
-- 查看山東和河南的城市信息
SELECT *FROM city WHERE district IN('shandong','henan');
4.group by 子句+聚合函數(shù)應用
4.1 什么是分組
按照某個列進行分組 -- 4.2 常用的聚合函數(shù)
COUNT() 計數(shù)
MAX() 最大值
MIN() 最小值
AVG() 平均值
SUM() 求和
GROUP_CONCAT() 列轉行
`4.3例子`
-- 統(tǒng)一每個國家的總人口數(shù)
SELECT countrycode,SUM(population) FROM city GROUP BY countrycode;
-- 統(tǒng)計中國每個省的城市個數(shù)及省總人口數(shù)
SELECT district,COUNT(NAME),SUM(population) FROM city WHERE countrycode='CHN'
GROUP BY district
-- 統(tǒng)計各個國家的城市名列表
SELECT countrycode ,GROUP_CONCAT(NAME)
FROM city
GROUP BY countrycode
5.having 語句(后過濾)
-- 統(tǒng)計中國每個省的城市個數(shù)及省總人口數(shù)
-- 只顯示人口總數(shù)大于800w的省
SELECT district,COUNT(NAME),SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>8000000;
6.order by 子句
-- 以上例子將人口數(shù)進行排序輸出
SELECT district,COUNT(NAME),SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>8000000
ORDER BY SUM(population) DESC;
-- 查詢中國所有城市信息,并以人口數(shù)降序輸出
SELECT * FROM city WHERE countrycode='CHN'
ORDER BY population DESC ;
7.limit 應用
SELECT * FROM city WHERE countrycode='CHN'
ORDER BY population DESC
LIMIT 5 OFFSET 5;
SELECT * FROM city WHERE countrycode='CHN'
ORDER BY population DESC
LIMIT 3,5;
-- 跳過前N行,顯示M行(N和M代表的是數(shù)字)
LIMIT M offet N
LIMIT N,M
8.distinct 應用
-- 查詢所有的國家代號信息
SELECT DISTINCT countrycode FROM city;
**9.untion與untion all****
