1. DDL 數(shù)據(jù)的定義語(yǔ)言
1.1 庫(kù)的定義
1) 增
CREATE DATABASE luping CHARSET utf8mb4;
規(guī)范: *****
1. 字符集必須設(shè)置
2. 不要有大寫(xiě)字母
3. 不要有數(shù)字開(kāi)頭
5. 庫(kù)名要和業(yè)務(wù)有關(guān)
6. 庫(kù)名不要太長(zhǎng)
7. 庫(kù)名不能是關(guān)鍵字
(2) 刪
DROP DATABASE LUPING;
一般只有管理員有權(quán)限刪除數(shù)據(jù)庫(kù).
(3) 改
ALTER DATABASE test CHARSET utf8mb4;
(5) 查
show databases;
SHOW CREATE DATABASE test;
1.2 表的定義
(1)增
表屬性:
存儲(chǔ)引擎類(lèi)型
字符集
列屬性:
數(shù)據(jù)類(lèi)型
約束
其他屬性
USE luping;
CREATE TABLE stu (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '學(xué)號(hào)',
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é)時(shí)間'
)ENGINE=INNODB CHARSET=utf8mb4 COMMENT '學(xué)生表';
建表規(guī)范: *****
1. 表名: 小寫(xiě),業(yè)務(wù)相關(guān),不能數(shù)字開(kāi)頭,不能太長(zhǎng),不能是關(guān)鍵字.
2. 表屬性: 字符集,存儲(chǔ)引擎,注釋
3. 列屬性:
合適的數(shù)據(jù)類(lèi)型(簡(jiǎn)短,合適的,完整的)
必須要有PK(id 自增長(zhǎng)數(shù)字列).
每個(gè)列要非空,并設(shè)定默認(rèn)值.
必須要有注釋.
(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 '手機(jī)號(hào)';
-- 在name列后添加dizhi列
ALTER TABLE stu ADD dizhi VARCHAR(100) NOT NULL COMMENT '地址' AFTER sname;
-- 在首列添加qq號(hào)列
ALTER TABLE stu ADD qq BIGINT NOT NULL UNIQUE COMMENT 'qq號(hào)' FIRST;
-- 刪除列
ALTER TABLE stu DROP qq ;
ALTER TABLE stu DROP shouji;
ALTER TABLE stu DROP dizhi;
-- 修改表屬性
ALTER TABLE stu ENGINE=INNODB;
-- 查詢(xún)表屬性
SHOW CREATE TABLE xs;
-- 修改表名 *****
ALTER TABLE stu RENAME TO xs;
說(shuō)明: 歸檔表,日志表
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ù)類(lèi)型改為tinyint,加個(gè)注釋(0代表nv,1代表男)
ALTER TABLE xs CHANGE sex gender TINYINT NOT NULL DEFAULT 1 COMMENT '0代表nv,1代表男' ;
注意:
所有表結(jié)構(gòu)變更,都會(huì)自動(dòng)鎖表.盡量避免大表Online DDL.
建議:
1. 最好業(yè)務(wù)不繁忙做
2. 使用第三方工具pt-osc (percona-toolkit)
https://www.percona.com/software/database-tools/percona-toolkit
3. 對(duì)于歸檔表,可以使用pt-archiver
(5)查
DESC xs;
2. DCL
grant
revoke
show grants for
3.DML 數(shù)據(jù)操作語(yǔ)言
主要針對(duì)表中數(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;
說(shuō)明:
DELETE 語(yǔ)句數(shù)據(jù)邏輯刪除,磁盤(pán)空間不會(huì)立即釋放,會(huì)產(chǎn)生碎片.
思考: 2億行的大表,批量刪除5000w(按時(shí)間條件)
3.3 UPDATE 數(shù)據(jù)修改
UPDATE xs SET age=28 WHERE id=5;
3.5 偽刪除
刪除id=5的行,偽刪除實(shí)現(xiàn)
ALTER TABLE xs ADD state TINYINT NOT NULL DEFAULT 1;
SELECT * FROM xs;
原語(yǔ)句:
DELETE FROM xs WHERE id=5;
替換為:
UPDATE xs SET state=0 WHERE id=5;
原業(yè)務(wù)語(yǔ)句:
SELECT * FROM xs;
替換為:
SELECT * FROM xs WHERE state=1;
5. DQL 數(shù)據(jù)查詢(xún)語(yǔ)言 *****
5.1 select *****
- 單獨(dú)使用
(1) 查詢(xún)參數(shù)
SELECT @@datadir;
SELECT @@port;
SELECT @@socket;
SELECT @@basedir;
SELECT @@innodb_flush_log_at_trx_commit;
SHOW VARIABLES ;
(2) 簡(jiǎn)易計(jì)算
SELECT 1024*1024;
SELECT 1+2;
(3) 函數(shù)查詢(xún)
SELECT NOW();
SELECT CONCAT("吃飯了掉弛，出動(dòng)捺癞！")
- 5.1.2 配合多子句(單表查詢(xún)標(biāo)準(zhǔn)用法)
多子句應(yīng)用順序: *****
(1) FROM 必須添加的
(2) WHERE
(3) GROUP BY
(5) HAVING
(6) ORDER BY
(7) LIMIT
-- from 子句 應(yīng)用
--- 1. 查詢(xún)city表中的所有數(shù)據(jù)(不代表生產(chǎn)操作)
SELECT * FROM city;
--- 2. 查詢(xún)city表中所有的name和population
SELECT NAME,population FROM city;
-- where 子句 應(yīng)用
-- where 的等值查詢(xún)
--- 1. 查詢(xún)中國(guó)所有城市的信息
SELECT * FROM city
WHERE countrycode='CHN';
-- where 的不等值查詢(xún)(> < >= <= !=)
--- 1. 城市人口小于100人的城市
SELECT * FROM city
WHERE population<100;
--- 2. 不是中國(guó)的城市信息
SELECT * FROM city
WHERE countrycode <> 'CHN';
-- where 配合 like語(yǔ)句使用
--- 1. 查詢(xún)國(guó)家代號(hào)是CH開(kāi)頭的城市信息.
SELECT * FROM city
WHERE countrycode LIKE 'CH%';
注意: LIKE 語(yǔ)句 百分號(hào)不要出現(xiàn)在查詢(xún)條件前,因?yàn)椴樵?xún)不走索引.
-- where 配合邏輯連接符(and or)
--- 1. 中國(guó)城市人口大于500w的城市
SELECT * FROM city
WHERE countrycode='CHN' AND population>5000000 ;
--- 2. 中國(guó)或者美國(guó)城市信息
SELECT * FROM city
WHERE countrycode='CHN' OR countrycode='USA';
-- where 配合邏輯連接符(and or)
--- 1. 中國(guó)城市人口大于500w的城市
SELECT * FROM city
WHERE countrycode='CHN' AND population>5000000 ;
--- 2. 中國(guó)或者美國(guó)城市信息
SELECT * FROM city
WHERE countrycode='CHN' OR countrycode='USA';
-- where 配合 in
--- 中國(guó)或者美國(guó)城市信息
SELECT * FROM city
WHERE countrycode IN ('CHN','USA');
-- where 配合 between and
--- 查詢(xún) 人口數(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)行
說(shuō)明:
有 GROUP by子句,必然會(huì)有聚合函數(shù)
在業(yè)務(wù)查詢(xún)需求中,需要對(duì)于表中數(shù)據(jù)按照數(shù)據(jù)特點(diǎn)進(jìn)行分別統(tǒng)計(jì)時(shí),GROUP BY + 聚合函數(shù)來(lái)實(shí)現(xiàn)
--- 1. 統(tǒng)計(jì)世界的所有人口總和
SELECT SUM(population) FROM city ;
--- 2. 統(tǒng)計(jì)國(guó)家的總?cè)丝跀?shù)量
SELECT countrycode, SUM(population) FROM city
GROUP BY countrycode ;
--- 3. 統(tǒng)計(jì)中國(guó)每個(gè)省的總?cè)丝跀?shù)
SELECT countrycode,district , SUM(population)
FROM city
GROUP BY district;
--- 5. 統(tǒng)計(jì)中國(guó)每個(gè)省的城市名列表.
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后列是唯一值時(shí)候,就不會(huì)報(bào)錯(cuò)
5. GROUP BY 的條件列是主鍵或者唯一鍵時(shí).
總結(jié): sql_mode=only_full_group_by 為了防止出現(xiàn)結(jié)果集1對(duì)多的關(guān)系.
