1. DDL 數(shù)據(jù)的定義語言
1.1 庫的定義
1) 增
CREATE DATABASE luping CHARSET utf8mb4;
規(guī)范: *****
1. 字符集必須設(shè)置
2. 不要有大寫字母
3. 不要有數(shù)字開頭
5. 庫名要和業(yè)務(wù)有關(guān)
6. 庫名不要太長
7. 庫名不能是關(guān)鍵字
(2) 刪
DROP DATABASE LUPING;
一般只有管理員有權(quán)限刪除數(shù)據(jù)庫.
(3) 改
ALTER DATABASE test CHARSET utf8mb4;
(5) 查
show databases;
SHOW CREATE DATABASE test;
1.2 表的定義
(1)增
表屬性:
存儲引擎類型
字符集
列屬性:
數(shù)據(jù)類型
約束
其他屬性
USE luping;
CREATE TABLE stu (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '學(xué)號',
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 '入學(xué)時間'
)ENGINE=INNODB CHARSET=utf8mb4 COMMENT '學(xué)生表';
建表規(guī)范: *****
1. 表名: 小寫,業(yè)務(wù)相關(guān),不能數(shù)字開頭,不能太長,不能是關(guān)鍵字.
2. 表屬性: 字符集,存儲引擎,注釋
3. 列屬性:
合適的數(shù)據(jù)類型(簡短,合適的,完整的)
必須要有PK(id 自增長數(shù)字列).
每個列要非空,并設(shè)定默認值.
必須要有注釋.
(2)刪
DROP TABLE xuesheng;
TRUNCATE TABLE xuesheng;
區(qū)別:
drop : 表結(jié)構(gòu)+表行
truncate: 表行刪除(表段物理清空)
(3)改 *****
例子:
-- 在stu表中添加shouji列 *****
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 xs;
-- 修改表名 *****
ALTER TABLE stu RENAME TO xs;
說明: 歸檔表,日志表
rname老的表為新名: xxxx_201906
CREATE TABLE xxx LIKE xxxx_201906;
-- 歸檔表例子 *****
--- 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代表男' ;
注意:
所有表結(jié)構(gòu)變更,都會自動鎖表.盡量避免大表Online DDL.
建議:
1. 最好業(yè)務(wù)不繁忙做
2. 使用第三方工具pt-osc (percona-toolkit)
https://www.percona.com/software/database-tools/percona-toolkit
3. 對于歸檔表,可以使用pt-archiver
(5)查
DESC xs;
2. DCL
grant
revoke
show grants for
3.DML 數(shù)據(jù)操作語言
主要針對表中數(shù)據(jù)行操作.
3.1 insert 數(shù)據(jù)插入
DESC xs;
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');
SELECT * FROM xs;
INSERT INTO xs
VALUES(4,'ss',18,1,'2019-11-13');
INSERT INTO xs(sname,age,gender)
VALUES('w5',18,1);
3.2 DELETE 數(shù)據(jù)刪除
DELETE FROM xs WHERE id=4;
SELECT * FROM xs;
說明:
DELETE 語句數(shù)據(jù)邏輯刪除,磁盤空間不會立即釋放,會產(chǎn)生碎片.
思考: 2億行的大表,批量刪除5000w(按時間條件)
3.3 UPDATE 數(shù)據(jù)修改
UPDATE xs SET age=28 WHERE id=5;
3.5 偽刪除
刪除id=5的行,偽刪除實現(xiàn)
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;
5. DQL 數(shù)據(jù)查詢語言 *****
5.1 select *****
- 單獨使用
(1) 查詢參數(shù)
SELECT @@datadir;
SELECT @@port;
SELECT @@socket;
SELECT @@basedir;
SELECT @@innodb_flush_log_at_trx_commit;
SHOW VARIABLES ;
(2) 簡易計算
SELECT 1024*1024;
SELECT 1+2;
(3) 函數(shù)查詢
SELECT NOW();
SELECT CONCAT("吃飯了，出動！")
- 5.1.2 配合多子句(單表查詢標準用法)
多子句應(yīng)用順序: *****
(1) FROM 必須添加的
(2) WHERE
(3) GROUP BY
(5) HAVING
(6) ORDER BY
(7) LIMIT
-- from 子句 應(yīng)用
--- 1. 查詢city表中的所有數(shù)據(jù)(不代表生產(chǎn)操作)
SELECT * FROM city;
--- 2. 查詢city表中所有的name和population
SELECT NAME,population FROM city;
-- where 子句 應(yīng)用
-- where 的等值查詢
--- 1. 查詢中國所有城市的信息
SELECT * FROM city
WHERE countrycode='CHN';
-- where 的不等值查詢(> < >= <= !=)
--- 1. 城市人口小于100人的城市
SELECT * FROM city
WHERE population<100;
--- 2. 不是中國的城市信息
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 配合邏輯連接符(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)行
說明:
有 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
--- sql_mode=only_full_group_by
1. 在 SELECT 后的條件,不在 GROUP BY 后
2. 在 SELECT 后的條件,不在函數(shù)里包裹
3. 如果select后列是唯一值時候,就不會報錯
5. GROUP BY 的條件列是主鍵或者唯一鍵時.
總結(jié): sql_mode=only_full_group_by 為了防止出現(xiàn)結(jié)果集1對多的關(guān)系.
