一.昨天遺留的不懂的地方
數(shù)據(jù)類型
char（n）和varchar（n）的區(qū)別
共同點：
1.都是字符串數(shù)據(jù)類型
2.小括號中設(shè)置的數(shù)字跪腹，都是字符的最大長度
不同點：
1.char是定長，存不滿用空字符串填充
2.varchar是變長
3.varchar需要單獨占用1-2字節(jié)存儲字符長度
4.char理論上最多255字節(jié)，varchar理論上是65533 char（255）
二.SQL常用分類
1.DDL 數(shù)據(jù)定義語言
1.1庫的定義
增
CREATE DATABASE yzg CHARSET utf8mb4;
規(guī)范：
01.字符集必須設(shè)置。
02.庫的名稱不要有大寫字母液走。
03.不要有數(shù)字開頭偷俭。
04.庫名要和業(yè)務(wù)有關(guān)。
05.庫名不要太長
06.庫名不能是關(guān)鍵字
刪
drop database yzg虹曙；
一般只有管理員有權(quán)限刪除數(shù)據(jù)庫
改
alter database 庫名 charset utf8mb4谷誓；
查
show databases绒障；
show create database test；
1.2表的定義
增
表屬性：
存儲引擎類型
字符集
列屬性：
數(shù)據(jù)類型
約束
其他屬性
USE yzg;
CREATE TABLE stu (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '學號',
sname VARCHAR(32) NOT NULL COMMENT '姓名',
age TINYINT NOT NULL DEFAULT 18 COMMENT '年齡',
sex ENUM('male','female','others')NOT NULL DEFAULT 'others' COMMENT '性別',
intime DATETIME NOT NULL DEFAULT NOW() COMMENT '入學時間'
)ENGINE=INNODB CHARSET=utf8mb4 COMMENT '學生表';
建表規(guī)范：
1.表名：小寫捍歪，業(yè)務(wù)相關(guān)户辱，不能數(shù)字開頭鸵钝，不能太長，不能是關(guān)鍵字庐镐。
2.表屬型：字符集恩商，存儲引擎，注釋
3.列屬性：合適的數(shù)據(jù)類型（簡短必逆，合適的怠堪，完整的），必須要有主鍵（id名眉，自增長數(shù)字列）
每個列要非空粟矿，并設(shè)定默認值
必須要有注釋
[https://www.cnblogs.com/xiaoliwang/p/8956736.html]
刪
drop table 表名;
truncate table 表名;
區(qū)別：
drop：表結(jié)構(gòu)+表行
truncate：表行刪除（表段物理清空）保留表結(jié)構(gòu)。
改
例子：
--在stu表中添加手機列损拢；
ALTER TABLE stu ADD shouji BIGINT NOT NULL UNIQUE COMMENT '添加手機號';
-- 在name列后添加dizhi列
ALTER TABLE stu ADD dizhi VARCHAR(100) NOT NULL COMMENT '地址' AFTER sname;
-- 在首列添加qq號列
ALTER TABLE stu ADD qq BIGINT NOT NULL UNIQUE COMMENT 'qq號' FIRST;
-- 刪除列
ALTER TABLE stu DROP qq ;
ALTER TABLE stu DROP shouji;
ALTER TABLE stu DROP dizhi;
-- 修改表屬性
ALTER TABLE stu ENGINE=INNODB;
-- 查詢表屬性
SHOW CREATE TABLE stu;`
--修改表名
ALTER TABLE stu RENAME TO xs;
-- 歸檔表例子
--- 1. RENAME 舊表
ALTER TABLE xs RENAME TO xs_201911;
CREATE TABLE xs LIKE xs_201911;
--- 2. Oracle 物化視圖 (了解)
-- 將sex列名改為gender,數(shù)據(jù)類型改為tinyint,加個注釋(0代表nv,1代表男)
ALTER TABLE xs CHANGE sex gender TINYINT NOT NULL DEFAULT 1 COMMENT '0代表nv,1代表男' ;
查
DESC xs;
說明：歸檔表陌粹，日志表
rename老的表為新的名稱：xx_201906
create table xxx like xxx_20906;
注意：
所有表結(jié)構(gòu)變更，都會自動鎖表福压，盡量避免大表
建議：
1.最好業(yè)務(wù)不繁忙做
2.使用第三方工具pt-osc（percona-toolkit）自己擴展
[https://www.percona.com/software/database-tools/percona-toolkit]
3.對于歸檔表掏秩，可以使用pt-archiver 自己擴展
online DDL。
小結(jié)：DDL
(1) 建庫建表規(guī)范
建庫規(guī)范: *****
1. 字符集必須設(shè)置
2. 不要有大寫字母
3. 不要有數(shù)字開頭
5. 庫名要和業(yè)務(wù)有關(guān)
6. 庫名不要太長
7. 庫名不能是關(guān)鍵字
建表規(guī)范: *****
1. 表名: 小寫,業(yè)務(wù)相關(guān),不能數(shù)字開頭,不能太長,不能是關(guān)鍵字.
2. 表屬性: 字符集,存儲引擎,注釋
3. 列屬性:
合適的數(shù)據(jù)類型(簡短,合適的,完整的)
必須要有PK(id 自增長數(shù)字列).
每個列要非空,并設(shè)定默認值.
必須要有注釋.
(2) 修改表結(jié)構(gòu)
-- 在stu表中添加shouji列 *****
ALTER TABLE stu ADD shouji BIGINT NOT NULL UNIQUE COMMENT '手機號';
-- 歸檔表例子 *****
-- RENAME 舊表
ALTER TABLE xs RENAME TO xs_201911;
CREATE TABLE xs LIKE xs_201911;
(3) Online DDL
注意:
所有表結(jié)構(gòu)變更,都會自動鎖表.盡量避免大表Online DDL.
2.DCL
grant
revoke
show grants for
3.DML數(shù)據(jù)操作語言
主要針對表中數(shù)據(jù)進行操作
insert數(shù)據(jù)插入
INSERT INTO xs(id,sname,age,gender,intime)
VALUES(1,'zs',18,1,'2019-11-13');
SELECT * FROM xs;
INSERT INTO xs(id,sname,age,gender,intime)
VALUES(2,'ls',18,1,'2019-11-13'),(3,'xh',19,0,'2019-11-13');
delete數(shù)據(jù)刪除
delete from xs where id=4;
select * from xs;
說明：
delete語句數(shù)據(jù)邏輯刪除荆姆，磁盤空間不會立即釋放蒙幻，會產(chǎn)生碎片。
上億行的大表胆筒，批量刪除5000w（按時間條件）
update數(shù)據(jù)修改
UPDATE xs SET age=28 WHERE id=5;
偽刪除
ALTER TABLE xs ADD state TINYINT NOT NULL DEFAULT 1;
SELECT * FROM xs;
原語句:
DELETE FROM xs WHERE id=5;
替換為:
UPDATE xs SET state=0 WHERE id=5;
原業(yè)務(wù)語句:
SELECT * FROM xs;
替換為:
SELECT * FROM xs WHERE state=1;
4.DQL 數(shù)據(jù)查詢語言 *****
1.select
01.單獨使用
(1)查詢參數(shù)
SELECT @@datadir;
SELECT @@socket;
SELECT @@port;
SELECT @@basedir;
SELECT @@innodb_flush_log_at_trx_commit;
SHOW VARIABLES LIKE '%trx%';
(2)簡易計算
SELECT 1024*1024;
SELECT 1+2;
(3)函數(shù)查詢
SELECT NOW;
SELECT CONCAT("開心");
02.配合多子句（標準用法）
(1) FROM
(2) WHERE
(3) GROUP BY
(4) HAVING
(5)ORDER BY
(6)LIMIT
FROM
單詞:
world 世界
============================
city 城市
DESC city;
============================
id 城市ID
NAME 城市名
countrycode 國家代碼(CHN,JPN,USA)
district 區(qū)域(省,州)
population 人口
先后順序 如果出現(xiàn)幾個參數(shù)必須按照當前順序
--from 字句應(yīng)用
--1.查詢city表中的所有數(shù)據(jù)（不代表生產(chǎn)操作）
SELECT * FROM city;
--2.查詢city表中所有的name和population
SELECT NAME,population FROM city;
WHERE
--where 字句應(yīng)用
--where 等值查詢
--1.查看中國所有城市信息
SELECT * FROM city
WHERE countrycode='CHN';
--where的不等值查詢
--1.城市人口小于100人的城市
SELECT * FROM city
WHERE population<100;
SELECT * FROM city
不是中國的城市信息
WHERE countrycode<>'CHN';
-- where 配合 like語句使用
--- 1. 查詢國家代號是CH開頭的城市信息.
SELECT * FROM city
WHERE countrycode LIKE 'CH%';
注意: LIKE 語句 百分號不要出現(xiàn)在查詢條件前,因為查詢不走索引.
-- where 配合邏輯連接符(and or)
--- 1. 中國城市人口大于500w的城市
SELECT * FROM city
WHERE countrycode='CHN' AND population>5000000 ;
--- 2. 中國或者美國城市信息
SELECT * FROM city
WHERE countrycode='CHN' OR countrycode='USA';
--where 配合in
SELECT * FROM city
WHERE countrycode IN ('CHN','USA');
--where配合between AND
--查詢 人口數(shù)量在100w-200w之間的城市
SELECT * FROM city
WHERE population > 1000000 AND population <= 2000000;
SELECT * FROM city
WHERE population BETWEEN 1000000 AND 2000000;
GROUP BY +聚合函數(shù)應(yīng)用
聚合函數(shù)杆煞？
max（）最大值
min（）最小值
avg（）平均值
count（）數(shù)量
sum()求和
group_concat()列轉(zhuǎn)行
說明：有g(shù)roup by字句，必然會有聚合函數(shù)
在業(yè)務(wù)查詢需求中腐泻，需要對于表中數(shù)據(jù)按照數(shù)據(jù)特點進行分別統(tǒng)計時，GROUP BY +聚合函數(shù)來實現(xiàn)
有 GROUP by子句,必然會有聚合函數(shù)
在業(yè)務(wù)查詢需求中,需要對于表中數(shù)據(jù)按照數(shù)據(jù)特點進行分別統(tǒng)計時,GROUP BY + 聚合函數(shù)來實現(xiàn)
--- 1. 統(tǒng)計世界的所有人口總和
SELECT SUM(population) FROM city ;
--- 2. 統(tǒng)計國家的總?cè)丝跀?shù)量
SELECT countrycode, SUM(population) FROM city
GROUP BY countrycode ;
--- 3. 統(tǒng)計中國每個省的總?cè)丝跀?shù)
SELECT countrycode,district , SUM(population)
FROM city
GROUP BY district;
--- 5. 統(tǒng)計中國每個省的城市名列表.
SELECT district,GROUP_CONCAT(NAME)
FROM city
WHERE countrycode='CHN'
GROUP BY district;
--- 6. 關(guān)于 GROUP BY 的限制
RROR 1055 (42000): Expression
-- #1 of SELECT list is not in GROUP BY clause and contains nonaggregated
-- column 'world.city.CountryCode' which is not functionally dependent
-- on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
HAVING 后過濾
SELECT district,SUM(population)
FROM city WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population) >10000000;
ORDER BY
--- 查詢中國所有的城市信息,并按照人口數(shù)排序輸出結(jié)果
SELECT * FROM city
WHERE countrycode='CHN'
ORDER BY population DESC ;
--- 統(tǒng)計中國每個省的總?cè)丝跀?shù),只顯示總?cè)丝诖笥?00w,并按照總?cè)丝诮敌蜉敵?SELECT district , SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC ;
LIMIT
統(tǒng)計中國每個省的總?cè)丝跀?shù)队询，只顯示人口數(shù)大于500W派桩，并按照總?cè)丝诮敌蜉敵銮?名
SELECT district , SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 10;
SELECT district , SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 2,3;
SELECT district , SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 4 OFFSET 2;
distinct 去重復(fù)
select distinct countrycode from city;
union&&union all
SELECT * FROM city WHERE countycode='CHN'
UNION ALL
SELECT * FROM city WHERE countycode='USA
union:去重復(fù)
union all :不去重復(fù)
2.show
yearing SQL語句審核平臺
圖片.png
