寫在前面的話

?

上一節(jié)主要談談 MySQL 是怎么安裝的以及最簡單的初始化我們應該做哪些配置。其中也用到了一些簡單的用戶操作 SQL，所以這一節(jié)主要學習常用的 SQL 使用。

?

?

SQL 介紹

?

在了解 SQL 之前，對于 SQL 需要有以下簡單的認知：

1. 主流的來個標準，SQL92 和 SQL99，在 MySQL 5.7 以后采用的是 SQL92。

2. 在 5.7 中新增了 sql_mode，作用在于限制哪些 SQL 能夠使用，一個很明顯的例子是 group by 的使用。

?

在上一節(jié)簡單說過常用的 SQL 分類（主要前 3 個）：

DDL：數(shù)據(jù)定義語言

DCL：數(shù)據(jù)控制語言

DML：數(shù)據(jù)操作語言

DQL：數(shù)據(jù)查詢語言

?

?

數(shù)據(jù)類型

?

這算是數(shù)據(jù)定義過程中的一個重點，針對不同的數(shù)據(jù)我們給定不同的數(shù)據(jù)類型，作用在于保證數(shù)據(jù)的準確性和標準性。

數(shù)值類型：

?

字符類型：

在生產(chǎn)中最容易出現(xiàn)的就是字符串字段長度不足的問題，所以在設計的時候注意選對類型。

char 之所以是定長，比如我們定義 char(10)，那就意味著傳的最大長度是 10，如果不夠補充空格，反正就是占用 10 個字符。

varchar 相比之下，則是在指定的范圍內(nèi)按需分配，如 varchar(10)，最大長度 10，不足就不足，不浪費。

至于 enum，則屬于特別的使用，如本字段的值是指定范圍，如 enum("北京", "上海", "廣州", "深圳")，這樣使用能夠優(yōu)化索引。但用的其實并不多。

?

時間類型：

timestamp 會受到時區(qū)的影響，且范圍有限制。不是很建議。

?

二進制類型（這類不建議存到 MySQL）：

?

?

表屬性

?

1. 列屬性（主要關(guān)鍵字）：

primary key：主鍵，非空唯一約束，一個表只能有一個，但是能由多個列組成。

not null：非空約束，屬于設計規(guī)范，盡可能不要列空，可以使用默認值 0 替代空。

unique key：唯一鍵，值不能重復 。

unsigned：無符號，主要用于數(shù)字列，非負數(shù)。

key：索引，可以給某列建立索引來優(yōu)化查詢。

default：默認值，列沒有值時默認填充。

auto_increment：自增，主要針對數(shù)字，順序填充數(shù)據(jù)，默認 1 開始，可以設置起始值和偏移量。

comment：注釋。

?

2. 表屬性：

存儲引擎：在 5.7 默認是 innodb，在老版本中可能是 MyISAM。

字符集：常見的 utf8，utf8mb4 等。

校對（排序）規(guī)則：如 utf8_general_ci，utf8_bin 這種。主要用于大小寫是否敏感。

可以通過以下 SQL 查看系統(tǒng)支持：

# 查看編碼
show charset;

# 查看排序規(guī)則
show collation;

?

?

數(shù)據(jù)庫操作（DDL）

?

1. 創(chuàng)建數(shù)據(jù)庫并指定查看字符集：

# 默認創(chuàng)建
create database a;
show create database a;

# 指定字符集創(chuàng)建
create database b charset utf8mb4;
show create database b;

# 指定字符集和排序規(guī)則創(chuàng)建
create database c charset utf8mb4 collate utf8mb4_bin;
show create database c;

結(jié)果如下：

可以看到，MySQL 默認不指定字符集創(chuàng)建數(shù)據(jù)庫的時候，創(chuàng)建的數(shù)據(jù)庫的字符集為拉?。╨atin1）。

排序規(guī)則 ci 結(jié)尾的都是大小寫不敏感的。bin 大小寫敏感。

當然，創(chuàng)建數(shù)據(jù)庫還可以使用：

create schema d;

這樣也是能夠創(chuàng)建數(shù)據(jù)庫的。

建庫規(guī)范：

a. 庫名不應該包含大小寫。

b. 庫名不該以數(shù)字開頭。

c. 建庫一定要加字符集。

d. 庫名要有意義。

?

特別注意：

禁止生產(chǎn)種執(zhí)行 drop database xxx;

?

2. 修改數(shù)據(jù)庫字符集：

alter database a charset utf8mb4;

查看：

但是值得注意的是，修改后的字符集必須比之前的字符集范圍更大。原因是數(shù)據(jù)的兼容性。

同時，不到萬不得已一般不要修改。

?

?

數(shù)據(jù)表操作（DDL）

?

語法格式：

create table students(
列1 屬性（數(shù)據(jù)類型, 約束, 其它）,
列2 屬性,
...
)

?

1. 創(chuàng)建一個名為學校的測試庫，創(chuàng)建一張名為學生的用戶表：

# 創(chuàng)建庫
create database school charset utf8mb4 collate utf8mb4_bin;

# 指定庫
use school;

# 創(chuàng)建表
create table students (
id int not null primary key auto_increment comment "學號",
sname varchar(255) not null comment "姓名",
sage tinyint unsigned not null default 0 comment "年齡",
sgender enum("m", "f", "n") not null default "n" comment "性別",
id_card char(18) not null unique comment "身份證",
add_time timestamp not null default now() comment "入學時間"
) engine=innodb charset=utf8mb4 comment "學生表";

建表規(guī)范：

a. 表名小寫，不能數(shù)字開頭且具有意義。

b. 選擇合適的數(shù)據(jù)類型，字符集，存儲引擎。

c. 每個列都需要有注釋說明且非空，如果為空選擇 0 代替。

?

特別注意：

禁止生產(chǎn)種執(zhí)行 drop table xxx;

?

2. 查看表結(jié)構(gòu)：

desc students;

結(jié)果如圖：

也可以查看建表語句：

show create table students\G

?

3. 添加列：

a. 直接添加手機號列：

alter table students add mobile varchar(20) not null unique comment "手機號";

如圖：

默認添加列加到最后面。

?

b. 在 id_card 后面添加微信列：

alter table students add wechat varchar(20) not null unique comment "微信" after id_card;

如圖：

?

c. 在最前面加個列：

alter table students add school_id tinyint not null comment "學校編號" first;

如圖：

?

4. 修改列：

a. 添加 qq 列，然后刪除它：

# 添加
alter table students add qq varchar(20) not null unique comment "QQ";
desc students;

# 刪除
alter table students drop qq;
desc students;

添加：

刪除：

?

b. 修改列屬性：

alter table students modify mobile varchar(15);

如圖：

可以發(fā)現(xiàn)，雖然只是修改了 varchar，但是 null 也修改了。所以修改的時候建議多以屬性都加一遍。

?

c. 修改列名和數(shù)據(jù)類型：

alter table students change sgender sg char(1) not null default 'n';

我們這里將 sgender 改為 sg，并修改類型：

?

在過去的版本中，我們應該避免在業(yè)務高峰期修改表結(jié)構(gòu)，因為這會導致數(shù)據(jù)庫鎖表。

但可以使用?pt-osc 工具（Percona 的），可以在線修改，不再鎖表，原理在于創(chuàng)建一個新表。

當然，在 MySQL 8.0 以后的版本以及自身集成了該工具。

?

5. 復制表結(jié)構(gòu)建立一張新表：

create table t1 like students;

對于 DCL，主要就兩個，一個是 grant，一個是 revoke。

?

?

數(shù)據(jù)增刪改（DML）

?

1. 插入數(shù)據(jù)：

a. 最標準的 insert 語法：

insert into students(school_id,id,sname,sage,sg,id_card,wechat,add_time,mobile) values (11,1,'張三',18,'m','511123199311111214','13290909801',now(),'13290909801');

?

b. 省事寫法：

由于我們每個字段都按照順序?qū)懀詻]必須要把字段列出來。

insert into students values (11,2,'李四',19,'f','511123199311111124','13290222201',now(),'13290222201');

?

c. 部分插入：

因為有些字段是由默認值的，所以我們可以就使用默認值：

insert into students(school_id,sname,sage,id_card,wechat,mobile) values (11,'王五',18,'511123199311112224','13290909221','13290909221');

?

d. 同時插入多個：

insert into students(school_id,sname,sage,id_card,wechat,mobile) values
(11,'老趙',12,'511123133311112224','13233909221','13233909221'),
(11,'老錢',16,'511333133311112224','13333909221','13333909221'),
(12,'老孫',25,'511113133311112224','13111909221','13111909221');

?

e. 查看插入結(jié)果：

select * from students;

結(jié)果如圖：

?

2. 修改數(shù)據(jù)：

a. 把張三的年齡改為 20：

update students set sage=20 where sname='張三';

??

b. 把所有名字老開頭的性別改為 m：

update students set sg='m' where sname like '老%';

結(jié)果如圖：

?

c. 同時修改多個值：

update students set sage=25,sg='f' where sname='張三';

結(jié)果如圖：

?

3. 刪除數(shù)據(jù)：

delete from students where sname="老孫";

不推薦使用?。?！

?

清空表的方法：

delete from students;

delete 逐行全部刪除，屬于 DDL 操作，速度慢！??！

同時，我們可以從上面的 id 可以看到，由于發(fā)生了 delete 導致 id 不連續(xù)，確實的那一部分仍然占據(jù)著磁盤，這將導致可能數(shù)據(jù)量不大，但是磁盤占用很大的情況。這就是磁盤碎片。

truncate table students;

truncate?全部清空數(shù)據(jù)頁，干干凈凈，屬于 DML 操作，速度快。

都不推薦?。?！

特別注意：

update / delete 一定要記得?where，否則原地爆炸。

?

在實際生產(chǎn)中，我們都是使用偽刪除的方式，也就是新加數(shù)據(jù)狀態(tài)字段，如可用為 1，不可用為 0，我們刪除就將狀態(tài)由 1 改為 0。

alter table students add status tinyint not null default 1 comment "數(shù)據(jù)狀態(tài)";

刪除就將改行數(shù)據(jù) status 改為 0。

?

?

查詢 DQL（Data Query Language）

?

1. 單獨使用，查看系統(tǒng)參數(shù)：select @@xxx

select @@port;
select @@basedir;
select @@datadir;
select @@socket;
select @@server_id;

結(jié)果如下：

有些復雜的我們可以使用 show 來模糊查詢：

show variables like 'log%';

如圖：

?

2. 單獨使用，調(diào)用函數(shù)：select 函數(shù)();?

# 顯示當前信息
select now();
select database();
select user();

# 打印輸出
select concat("hello world");

# 定制化輸出
select concat(user,"@",host) from mysql.user;

# 一行輸出
select group_concat(user,"@",host) from mysql.user;

結(jié)果：

更多的函數(shù)可以查看官方文檔：

https://dev./doc/refman/5.7/en/func-op-summary-ref.html

?

3. 單表子句，FROM：

select * from students;

不建議在生產(chǎn)中使用，如果表數(shù)據(jù)太大會造成卡死。

查詢指定列：

select sname,sage,mobile from students;

如圖：

?

4. 單表子句，WHERE：

在使用之前，MySQL 官方提供了專門用于學習的一個數(shù)據(jù)庫：world，可以前往官網(wǎng)下載：

https://dev./doc/index-other.html

如圖：

將示例的數(shù)據(jù)庫導入 MySQL 中，當然也可以下載 world_x，那個是新數(shù)據(jù)，只是測試沒必要：

包含三張表，城市，國家，國家語言。可以使用 desc 了解每張表的數(shù)據(jù)結(jié)構(gòu)。

a. 等值查詢：查詢中國（CHN）的城市

select * from city where CountryCode="CHN";

結(jié)果：

也可以單獨查詢深圳：

select * from city where Name="shenzhen";

如圖：

?

b. 比較查詢：>，<，>=，<=，<>

查詢世界人口小于 100 的城市：

select * from city where population<100;

如圖：

?

c. 邏輯查詢：and，or

?查詢中國人口大于 500 萬的：

select * from city where countrycode="CHN" and population>=5000000;

如圖：

查詢中國或美國的城市：

select * from city where countrycode="CHN" or countrycode="USA";

?

d. 模糊查詢：%

查詢 bei 開頭的：

select * from city where name like "bei%";

查詢名字中包含 bei 的（不走索引，性能極差，不推薦）：

select * from city where name like "?i%";

如圖：

?

e. 列表匹配：in（類似 or）

select * from city where countrycode in ("CHN","USA");

如圖：

?

f. 查詢范圍：between and（類似 >= and <=）

查詢?nèi)丝谠?830000 和 840000 之間的城市：

select * from city where population between 830000 and 840000;

換成 and 寫為：

select * from city where population>=830000 and population<=840000;

結(jié)果：

?

5. GROUP BY 聚合函數(shù)

常用的聚合函數(shù)有以下一些：

最大值：max()
最小值：min()
平均值：avg()
求和：sum()
統(tǒng)計：count()
列轉(zhuǎn)行：group_concat()

?

a. 統(tǒng)計每個國家的人口數(shù)量：

select countrycode,sum(population) from city group by countrycode;

結(jié)果：

?

b. 統(tǒng)計中國各省總?cè)丝冢?/p>

select District,sum(population) from city where countrycode="CHN" group by District;

結(jié)果：

?

3. 統(tǒng)計世界上每個國家城市數(shù)量：

select CountryCode,count(name) from city group by CountryCode;

結(jié)果：

?

6. 單表子句：HAVING

例如統(tǒng)計中國各省總?cè)丝跀?shù)，只顯示小于 100 萬的。

select district,sum(population) from city where countrycode="CHN" group by district having sum(population)<1000000;

如圖：

之所以使用 having，是因為 where 有順序要求，分別是 where -- group by -- having。

在 group by 之后只能使用 having 不能再用 where 了。另外 having 后條件不走索引。

?

7. 排序和限制：ORDER BY LIMIT

a. 查詢中國的城市信息，并按照人口升序排序。

select * from city where countrycode="CHN" order by population;

如圖：

?

b. 統(tǒng)計各省總?cè)丝跀?shù)并按照降序排列。

select district,sum(population) from city where countrycode="CHN" group by district order by sum(population) desc;

默認升序，降序需要 desc：

?

c. 統(tǒng)計全國各省人口大于 500 萬的按照降序排列并取前三。

select district,sum(population) from city where countrycode="CHN" group by district order by sum(population) desc limit 3;

如圖：

當然，limit 也可做限制，如：

select district,sum(population) from city where countrycode="CHN" group by district order by sum(population) desc limit 2,3;

limit n,m，這意味著跳過前面的 n 行，然后顯示 m 行，于是結(jié)果為：

當然也可以另外的寫法：limit m offset n，一個意思。

?

8. 去重復：DISTINCT

查詢所有國家：

select distinct(countrycode) from city;

如果只是查詢 countrycode 會有很多重復數(shù)據(jù)，可以通過 distinct 去掉重復：

?

9. 聯(lián)合查詢：union all

之前查詢中國和美國使用了 and 和 in 的方法，但這并不是性能最優(yōu)的方法。最好的是使用 union all：

select * from city where countrycode="CHN" union all select * from city where countrycode="USA";

值得注意的是 union 會去掉重復數(shù)據(jù)，而 union all 不會去重復。?

?

10. 多表連接查詢：join

首選準備 4 張表，關(guān)系是這樣的：

建表語句：

-- 刪掉舊數(shù)據(jù)新建數(shù)據(jù)庫
drop database school;
create database school charset utf8mb4 collate utf8mb4_bin;
use school;

-- 學生表
create table student(
sno int not null primary key auto_increment comment "學號",
sname varchar(20) not null comment "姓名",
sage tinyint unsigned not null comment "年齡",
sgender enum("m","f","n") not null default "n" comment "性別"
) engine=innodb charset=utf8mb4;

-- 課程表
create table course(
cno int not null primary key auto_increment comment "課程編號",
cname varchar(20) not null comment "課程名稱",
tno int not null comment "教師編號"
) engine=innodb charset=utf8mb4;

-- 學生成績表
create table sc(
sno int not null comment "學號",
cno int not null comment "課程編號",
score tinyint not null default 0 comment "成績"
) engine=innodb charset=utf8mb4;

-- 教師表
create table teacher(
tno int not null primary key auto_increment comment "教師編號",
tname varchar(20) not null comment "教師名字"
) engine=innodb charset=utf8mb4;

?

基礎數(shù)據(jù)：

-- 學生信息
INSERT INTO student VALUES
(1,'張三',18,'m'),
(2,'李四',18,'m'),
(3,'王五',18,'m'),
(4,'老趙',19,'f'),
(5,'老錢',20,'m'),
(6,'老孫',20,'f'),
(7,'老李',25,'m');

-- 教師信息
INSERT INTO teacher VALUES
(101,'李老師'),
(102,'張老師'),
(103,'王老師');

-- 課程信息
INSERT INTO course VALUES
(1001,'linux',101),
(1002,'python',102),
(1003,'mysql',103);

-- 學生成績
INSERT INTO sc VALUES
(1,1001,80),
(1,1002,59),
(2,1002,90),
(2,1003,100),
(3,1001,99),
(3,1003,40),
(4,1001,79),
(4,1002,61),
(4,1003,99),
(5,1003,40),
(6,1001,89),
(6,1003,77),
(7,1001,67),
(7,1003,82);?

?

最后效果：

?

多表查詢測試：

a. 統(tǒng)計張三學習了幾門課：

select st.sname,count(sc.cno) as course_nums from student as st join sc on st.sno=sc.sno where st.sname="張三";

這里用到的知識有：

1. 通過 as 可以對字段就行取別名，便于后面書寫使用。

2. 在一開始不知道這么寫的時候可以選擇將 select 和 from 之間的內(nèi)容替換為 *，然后再根據(jù)需求修改。

結(jié)果：

?

b. 查詢張三學習的課程名稱：

同樣可以先查出所有信息：

select * from student as st join sc on st.sno=sc.sno join course as co on co.cno=sc.cno where st.sname="張三";

結(jié)果：

然后我們只需要姓名列和課程名稱列：

select st.sname,co.cname from student as st join sc on st.sno=sc.sno join course as co on co.cno=sc.cno where st.sname="張三";

結(jié)果：

?

c. 查詢李老師教的學生名字：

select te.tname,co.cname,st.sname from teacher as te join course as co on co.tno=te.tno join sc on sc.cno=co.cno join student as st on st.sno=sc.sno where te.tname="李老師";

結(jié)果：

?

d. 計算李老師所教課程的平均分：

select avg(sc.score) from teacher as te join course as co on te.tno=co.tno join sc on co.cno=sc.cno where te.tname="李老師";

結(jié)果：

?

e. 計算每個老師的平均分，并降序排列：

select te.tname,avg(sc.score) from teacher as te join course as co on te.tno=co.tno join sc on co.cno=sc.cno group by te.tname order by avg(sc.score) desc;

結(jié)果：

?

f. 查詢李老師80分以下的學生：

select * from teacher as te join course as co on te.tno=co.tno join sc on sc.cno=co.cno join student as st on st.sno=sc.sno where te.tname="李老師" and sc.score<80;

結(jié)果：

?

g. 查詢所有老師成績不及格的：

select * from teacher as te join course as co on co.tno=te.tno join sc on sc.cno=co.cno join student as st on sc.sno=st.sno where sc.score<60;

結(jié)果：

?

h. 查詢平均成績大于 60 的學生：

select st.sname,avg(score) from sc join student as st on st.sno=sc.sno group by sc.sno having avg(score)>60;

結(jié)果：

這里值得注意的是，由于 group by 后面不能使用 where，所以篩選條件變成 having。

?

i. 顯示各門成績的最高分最低分：

select co.cname,max(sc.score) as "最高分",min(sc.score) as "最低分" from sc join course as co on co.cno=sc.cno group by sc.cno;

結(jié)果：

?

11.?information_schema 視圖庫：

在說明這個之前，可以簡單了解以下視圖?？梢赃@樣理解，視圖就是對復雜 SQL 的封裝，舉個例子：

select co.cname,max(sc.score) as "最高分",min(sc.score) as "最低分" from sc join course as co on co.cno=sc.cno group by sc.cno;

這是上面一條復雜的查詢，如果每次用這個就寫一次，這么長肯定很麻煩，這就可以將它保存為視圖：

create view mytest_view as select co.cname,max(sc.score) as "最高分",min(sc.score) as "最低分" from sc join course as co on co.cno=sc.cno group by sc.cno;

可以在前面增加創(chuàng)建視圖：create view 視圖名字 as

此時就可以直接使用：

select * from mytest_view;

結(jié)果：

?

在 MySQL 5.7 中，有三個庫用于存儲視圖：information_schema，performance_schema，sys

這里主要談談?information_schema 中的 tables 表，其中主要的字段包括：

可以直接查詢：

select TABLE_SCHEMA,TABLE_NAME,ENGINE,TABLE_ROWS,AVG_ROW_LENGTH,DATA_LENGTH,INDEX_LENGTH from tables;

?

示例：

a. 顯示每個庫都有哪些表：

select TABLE_SCHEMA,group_concat(TABLE_NAME) from information_schema.tables group by TABLE_SCHEMA;

結(jié)果類似：

| world              | countrylanguage,country,city|

?

b. 統(tǒng)計所有庫下表的個數(shù)：

select TABLE_SCHEMA,count(TABLE_NAME) from information_schema.tables group by TABLE_SCHEMA;

結(jié)果：

?

c. 統(tǒng)計 world 下面每張表所占的磁盤：

select TABLE_NAME,concat((DATA_LENGTH INDEX_LENGTH)/1024, " KB") as LENGTH from information_schema.tables where TABLE_SCHEMA="world";

結(jié)果：

當然會發(fā)現(xiàn)小數(shù)位數(shù)太多，沒啥意義，可以設置小數(shù)：保留兩位

select TABLE_NAME,concat(round((DATA_LENGTH INDEX_LENGTH)/1024, 2)," KB") as LENGTH from information_schema.tables where TABLE_SCHEMA="world";

結(jié)果：

?

d. 統(tǒng)計所有庫占用大?。?/p>

select TABLE_SCHEMA,concat(round(sum(DATA_LENGTH   INDEX_LENGTH)/1024,2)," KB") as DB_LENGTH from information_schema.tables group by TABLE_SCHEMA;

結(jié)果：

?

e. 統(tǒng)計整個庫占用的空間：

select concat(sum(DATA_LENGTH   INDEX_LENGTH)/1024," KB") as TOTAL_LENGTH from information_schema.tables;

結(jié)果：

?

f. 假設現(xiàn)在有 1000 張表，需要單獨生產(chǎn)備份語句，就需要用到 SQL 拼接：

mysqldump -uroot -p123 world city >/tmp/world_city.sql

這是備份單個表的語句，如果需要備份 1000 個，可以使用 SQL 來生成：

select concat("mysqldump -uroot -p123 ",TABLE_SCHEMA," ",TABLE_NAME," >/tmp/",TABLE_SCHEMA,"_",TABLE_NAME,".sql") from information_schema.tables where TABLE_SCHEMA="world";

結(jié)果如下：

但這只是單純的 SQL 結(jié)果，可以將其輸出到文件：前提是在 /etc/my.cnf 中指定安全目錄?secure-file-priv=/tmp

select concat("mysqldump -uroot -p123 ",TABLE_SCHEMA," ",TABLE_NAME," >/tmp/",TABLE_SCHEMA,"_",TABLE_NAME,".sql") from information_schema.tables where TABLE_SCHEMA="world" into outfile '/tmp/1.sh'

?

12. show 命令如下表

?

?

小結(jié)?

?

增刪查改的核心語句都在這里，內(nèi)容非常多！