MYSQL---DBA---叁（SQL基礎(chǔ)及元數(shù)據(jù)獲扔交ā）

一. SQL簡單介紹

（1）類似linux中的shell割卖，統(tǒng)一標(biāo)準(zhǔn)，能夠應(yīng)用于各種數(shù)據(jù)庫
（2）SQL標(biāo)準(zhǔn)：SQL-92或SQL-99
（3）SQL驗(yàn)證：SQL_MODE約束

二. SQL常用分類

（1）DDL：數(shù)據(jù)定義語言
（2）DCL: 數(shù)據(jù)控制語言
（3）DML: 數(shù)據(jù)操作語言
（4）DQL: 數(shù)據(jù)查詢語言

三. 數(shù)據(jù)類型（表的核心屬性癣朗，字符集）

作用：保證數(shù)據(jù)的準(zhǔn)確性和標(biāo)準(zhǔn)性

3.1 種類

3.1.1 數(shù)值類型

image.png

3.1.2 字符類型

image.png

char(10)與varchar(10)的區(qū)別（括號內(nèi)的數(shù)字是自己規(guī)定限制的字符長度）：
char(10)類型：是定長類型，不管存儲什么長度的數(shù)據(jù)旺罢，都立即分配你所指定的固定字符串長度的存儲空間旷余，若數(shù)據(jù)小于你所指定的，無法沾滿則用空來填充
varchar(10)類型：可變長度類型扁达，按需分配存儲空間正卧，在你指定的長度的字符串存儲空間中只要不超過你所指定的長度，需要多少就分配多少（但每次都要計(jì)算所需要字符串長度的存儲空間）在varchar類型中跪解，除了存儲字符串本身外炉旷，還會存儲字符長度，對于<=255個字符的會額外占用1個字節(jié)存儲長度叉讥；>255個字符的會額外占用2個字節(jié)長度存儲字符長度

注意：對于char(10)與varchar(10)的數(shù)據(jù)類型窘行，括號中表示的是最多的字符個數(shù)
基礎(chǔ)字符：a（字母）----1字節(jié) 1（數(shù)字）----1字節(jié) {（符號）----1字節(jié)
中文：張---gbk字符集---2字節(jié) 張---utf8---3字節(jié) 張---utf8mb4---4字節(jié)

3.1.3 時(shí)間類型

image.png

3.1.4 二進(jìn)制類型

一般不使用，neo4j：圖片图仓，視頻的存儲應(yīng)用

image.png

3.1.5 枚舉類型

enum（‘bj’罐盔，‘sh’，‘tj’,...）
對應(yīng)著編號1,2,3,...

四. 表與列的屬性

4.1 列屬性

約束作用（一般建表時(shí)添加）
（1）not null：非空約束
- 列值不能為空救崔，也是表設(shè)計(jì)的規(guī)范惶看，盡可能將所有的列設(shè)置為非空。若無法保證帚豪，可以設(shè)置默認(rèn)值碳竟，可以設(shè)置默認(rèn)值為0。
（2）unique key：唯一鍵
- 列值不能重復(fù)狸臣，手機(jī)號，身份證號昌执，銀行卡號種類的列設(shè)定為UK
（3）primary key：主鍵約束
- 設(shè)置為主鍵的列烛亦，此列的值必須非空且唯一诈泼，主鍵在一個表中只能有一個，但是可以有多個列一起構(gòu)成煤禽，一般會在表中設(shè)置自增長的id列铐达。
（4）unsigned：無符號
- 針對數(shù)字列，非負(fù)數(shù)檬果。一般是在int或tinyint后添加的附加屬性

其他屬性
（1）key：屬性
- 可以在某列上建立索引瓮孙，來優(yōu)化查詢,一般是根據(jù)需要后添加
（2）default：默認(rèn)值
- 列中，沒有錄入值時(shí)选脊，會自動使用default的值填充
（3）auto_increment：自增長
- 針對數(shù)字列杭抠，順序的自動填充數(shù)據(jù)（默認(rèn)是從1開始，將來可以設(shè)定起始點(diǎn)和偏移量）
（4）comment：注釋信息

4.2 表屬性

存儲引擎:
InnoDB（默認(rèn)的）
字符集和排序規(guī)則:
utf8
utf8mb4

五. 字符集及校對規(guī)則

5.1 字符集（charset）

show charset;（mysql中的查看方式）

gbk:中文字符占2個字節(jié)
utf8(mb3):中文字符占3個字節(jié)
utf8mb4:中文字符占4個字節(jié)

create database xs charset utf8mb4;
create table t1 ()charset utf8mb4;

5.2 校對/排序規(guī)則（collation）

5.2.1 舉例

- 舉例數(shù)據(jù)：Asd, ads, ass, bca, cda, Cdd
- 不區(qū)分大小寫：ads, Asd, ass, bca, cda, Cdd
- 區(qū)分大小寫：Asd, Cdd, ads, ass, bca, cda

5.2.2 utf8mb4字符集

show collaction（在mysql中查看方式）

（1）utf8mb4_general_ci: 通過校對默認(rèn)值恳啥，不區(qū)分大小寫
（2）utf8mb4_bin：區(qū)分大小寫

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

6.1 庫的定義

6.1.1 標(biāo)準(zhǔn)建庫語句

mysql> create database db charset utf8mb4;（創(chuàng)建）
mysql> show create database xuexiao;（查看）

6.1.2 創(chuàng)建數(shù)據(jù)庫方式

create database school;

create schema sch;

show charset;

show collation;

CREATE DATABASE test CHARSET utf8;

create database xyz charset utf8mb4 collate utf8mb4_bin;

6.1.3 建庫規(guī)范

1.庫名不能有大寫字母
2.建庫要加字符集
3.庫名不能有數(shù)字開頭

庫名要和業(yè)務(wù)相關(guān)

6.1.4 刪除庫

drop database oldboy;（生產(chǎn)中禁用）

6.1.5 修改庫（一般就是修改字符集）

SHOW CREATE DATABASE school;
ALTER DATABASE school CHARSET utf8;

6.1.6 查詢庫

show databases偏灿；
show create database oldboy；

6.2 表的定義

6.2.1 創(chuàng)建表

模板

create table stu(
列1  屬性（數(shù)據(jù)類型钝的、約束翁垂、其他屬性） ，
列2  屬性硝桩，
列3  屬性
)

實(shí)例

USE school;
CREATE TABLE stu(
id      INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '學(xué)號',
sname   VARCHAR(255) NOT NULL COMMENT '姓名',
sage    TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年齡',
sgender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性別' ,
sfz     CHAR(18) NOT NULL UNIQUE  COMMENT '身份證',
intime  TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '入學(xué)時(shí)間'
) ENGINE=INNODB CHARSET=utf8 COMMENT '學(xué)生表';

建表規(guī)范

表名小寫

不能是數(shù)字開頭

注意字符集和存儲引擎

表名和業(yè)務(wù)有關(guān)

選擇合適的數(shù)據(jù)類型

每個列都要有注釋

每個列設(shè)置為非空沿猜，無法保證非空，用0來填充碗脊。

必須有主鍵啼肩，一般是一個自增長的無關(guān)列。

6.2.2 刪除表

drop table xuesheng;（直接把整個表刪除包括表定義（表中的列）和數(shù)據(jù)）
truncate table xuesheng;（保留表結(jié)構(gòu)（表定義）望薄，清空表的區(qū)和數(shù)據(jù)）

6.2.3 修改表（添加刪除列）

添加列

（1）在xs表中添加手機(jī)號列“shouji”
- ALTER TABLE xs ADD shouji CHAR(11)  NOT NULL UNIQUE KEY COMMENT '手機(jī)號';
（2）在xs表中疟游，sex列后添加列“微信”
- ALTER TABLE xs ADD 微信 VARCHAR(64) NOT NULL UNIQUE KEY COMMENT '微信號'  AFTER sex;
（3）在xs表中的第一列位置添加列“qq號”
- ALTER TABLE xs ADD qq VARCHAR(64)  NOT NULL UNIQUE KEY COMMENT  'qq號'  first;

刪除列

ALTER TABLE stu DROP num;
ALTER TABLE stu DROP qq;
ALTER TABLE stu DROP wechat;

修改列屬性

（1）只改屬性不改列名
- ALTER TABLE xs MODIFY ssname  VARCHAR(64)  NOT NULL  COMMENT  '姓名';
（2）列名與屬性都改
- ALTER TABLE xs CHANGE sname  ssname  VARCHAR(32)  NOT NULL COMMENT '姓名';

6.2.4 查詢列（屬性）

use school
show tables；
desc stu;
show create table stu痕支；
CREATE TABLE ceshi LIKE stu;

七. 數(shù)據(jù)控制語言

7.1 賦予權(quán)限

grant all on wordpress.* to wordpress@'10.0.0.%' identified by '123';
（grant+權(quán)限+on+對象范圍+to +用戶@網(wǎng)段 +identified by '密碼';）

7.2 收回權(quán)限

revoke delete on wordpress.* from dev@'10.0.0.%';
（revoke +權(quán)限+on+對象范圍+from+用戶@網(wǎng)段）

7.3 權(quán)限查詢

show grants for dev@'10.0.0.%';
（show+grants+for+用戶@網(wǎng)段）

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

DML語句用作表中數(shù)據(jù)行的增刪改查

8.1 insert（錄入數(shù)據(jù)）

--- 最標(biāo)準(zhǔn)的insert語句
INSERT INTO stu(id,sname,sage,sg,sfz,intime) 
VALUES
(1,'zs',18,'m','123456',NOW());
SELECT * FROM stu;
--- 省事的寫法
INSERT INTO stu 
VALUES
(2,'ls',18,'m','1234567',NOW());
--- 針對性的錄入數(shù)據(jù)
INSERT INTO stu(sname,sfz)
VALUES ('w5','34445788');
--- 同時(shí)錄入多行數(shù)據(jù)
INSERT INTO stu(sname,sfz)
VALUES 
('w55','3444578d8'),
('m6','1212313'),
('aa','123213123123');
SELECT * FROM stu;

8.2 update（修改數(shù)據(jù)）

DESC stu;
SELECT * FROM stu;
UPDATE stu SET sname='zhao4' WHERE id=2;
（修改stu表中的sname數(shù)據(jù)行為zhao4）
注意：update語句必須要加where颁虐，不然整個數(shù)據(jù)列都會被改動。

8.3 delete（刪除數(shù)據(jù)）

DELETE FROM stu WHERE id=3;
（也必須由where指定具體修改什么卧须，危險(xiǎn)操作）

8.4 全表刪除（delete與truncate的區(qū)別）

全表刪除命令

DELETE FROM stu
truncate table stu;
區(qū)別
delete: DML操作, 清空整表的所有數(shù)據(jù)另绩，是邏輯性質(zhì)刪除,逐行進(jìn)行刪除,速度慢.并且表所占用的空間不會立即釋放。
truncate: DDL操作,清空整表的所有數(shù)據(jù)花嘶，按區(qū)刪除笋籽，屬于物理刪除，對與表段中的數(shù)據(jù)頁進(jìn)行清空,速度快.并且表所占用空間會被立即釋放椭员。

8.5 使用update代替delete實(shí)現(xiàn)偽刪除

（1）在xs表中添加一個狀態(tài)列state

ALTER TABLE xs ADD state TINYINT NOT NULL DEFAULT 1 ;
SELECT * FROM stu;（查看）
（2）用UPDATE 替代 DELETE
原語句：delete from xs where id=6;
改寫后：update xs set state=0 where id=6;
（3）業(yè)務(wù)語句查詢調(diào)整
原語句：select * from xs;
改寫后：SELECT * FROM xs WHERE state=1;

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

9.1 select 子句應(yīng)用（單表）

9.1.1 select 子句執(zhí)行邏輯

select 列1 from 表 where 條件 group by 條件 having 條件 order by 條件 limit 條件（順序不可亂）

9.1.2 select單獨(dú)使用（mysql獨(dú)家）

-- select @@xxx 查看系統(tǒng)參數(shù)
1. SELECT @@port;   #顯示連接端口   
2. SELECT @@basedir;   #顯示軟件安裝目錄
3. SELECT @@datadir;   #顯示數(shù)據(jù)存放目錄
4. SELECT @@socket;   #顯示socket文件
5. SELECT @@server_id;   #顯示服務(wù)id
6. SELECT @@innodb_flush_log_at_trx_commit;   #顯示innodb_log_buffer向innodb_log_file的刷寫策略
7. SHOW VARIABLES LIKE '%trx%'   #模糊查詢參數(shù)方式

9.1.3 select +函數(shù)()

1. select  now();   #顯示當(dāng)前時(shí)間
2. select  database();   #顯示當(dāng)前處于哪個庫
3. select  user();   #顯示哪個用戶登錄數(shù)據(jù)庫
4. select 16*16;   #用于計(jì)算（顯示計(jì)算結(jié)果）
5. select concat("hello world")   #顯示出括號內(nèi)容
6. select concat(user,"@",host)  from mysql.user;   #顯示出mysql.user中調(diào)出的用戶與主機(jī)名（用@連接起來车海，語句拼接）
7. select group_concat(user,"@",host) from mysql.user;   #與上訴顯示結(jié)果一樣，只不過是一行顯示（列轉(zhuǎn)行）

9.2 from子句應(yīng)用（單表）

use world;   #進(jìn)入world庫中
show tables;   #顯示庫中的所有表
desc city;   #以表結(jié)構(gòu)形式顯示city表的所有數(shù)據(jù)
select *  from city;   #查看city中所有數(shù)據(jù)（相當(dāng)于cat文件）
select name,countrycode   from city;   #從city表中調(diào)取國家代碼與名稱信息（相當(dāng)于awk文件）

9.3 where子句應(yīng)用

9.3.1 等值查詢

select * from city where countrycode='CHN';（查詢中國城市信息）

9.3.2 不等值查詢

select * from city where population<100;（查詢?nèi)丝谑切∮?00人的城市）
select * from city where id<10;（查詢id小于10的城市信息）
select * from city where countrycode !='CHN';（查詢不是中國城市的信息）

9.3.3 模糊查詢

select * from city where countrycode like 'CH%';（查詢國家代號是CH打頭的城市信息隘击，盡量避免like前帶%的模糊查詢）

9.3.4 邏輯連接符（and）

select * from city where countrycode='CHN' and population>5000000;（查詢中國人口超過500萬的城市）
select * from city where population>5000000 and population<6000000;
（查詢?nèi)丝跀?shù)在500萬到600萬之間的城市）

9.3.5 邏輯連接符（or）

select * from city where district='shandong' or district='hebei';（查詢山東省或河北省的信息）

9.3.6 where配合between and使用

select * from city where population between 5000000 and 6000000;（查詢?nèi)丝跀?shù)在500萬到600萬之間的城市）

9.3.7 where配合in的使用

select * from city where district in('shandong','hebei');（查詢山東省或河北省的信息）

9.4 group by分組幾句+聚合函數(shù)的應(yīng)用

image.png

9.4.1 什么是分組

以查詢的某個項(xiàng)進(jìn)行合理分組侍芝，以便更直觀的顯示

9.4.2 常用的聚合函數(shù)

COUNT()---用于計(jì)數(shù)
MAX()---最大值
MIN()---最小值
AVG()---平均數(shù)
SUM()---求和
GROUP_CONCAT()---列轉(zhuǎn)行

9.4.3 應(yīng)用舉例

select countrycode,COUNT(id) from city GROUP BY countrycode;（統(tǒng)計(jì)每個國家的城市個數(shù)）
select countrycode,SUM(population) from city GROUP BY countrycode;（統(tǒng)計(jì)每個國家的總?cè)丝跀?shù)）
select district,COUNT(NAME),SUM(population) from city where countrycode='CHN' GROUP BY district;（統(tǒng)計(jì)中國各個城市的個數(shù)及人口數(shù)）
select countrycode,GROUP_CONCAT(NAME) from city GROUP BY countrycode;（統(tǒng)計(jì)各個國家的城市名列表）

9.5 having子句應(yīng)用（一般在group by篩選過后再篩選一遍）打印

select district,COUNT(NAME),SUM(population) from city where countrycode='CHN' GROUP BY district HAVING SUM(population)>8000000;（統(tǒng)計(jì)中國每個省的城市個數(shù)研铆，省總?cè)丝跀?shù)，只顯示人口數(shù)大于800萬的手莸）

9.6 order by子句應(yīng)用（默認(rèn)增序輸出）

select district,COUNT(NAME),SUM(population) from city where countrycode='CHN' GROUP BY district HAVING SUM(population)>8000000 ORDER BY SUM(population) DESC;（統(tǒng)計(jì)中國各個省的城市個數(shù)棵红，省總?cè)丝跀?shù)，只顯示人口數(shù)大于800萬的省咧栗，降序輸出）
select * from city where countrycode='CHN' order by population DESC;（查詢中國所有城市信息逆甜，并以人口降序輸出）

9.7 limit子句應(yīng)用（顯示前幾行或跳過前幾行再顯示后幾行，應(yīng)用于order by之后）

9.7.1 格式顯示

LIMIT 10 OFFSET 0=LIMIT 10（顯示前10行）
LIMIT 5 OFFSET 5=LIMIT 5,5（跳過前5行致板，再顯示后5行）

9.7.2 例子

select * from city where countrycode='CHN' order by population DESC limit 10 offset 0;（查詢中國所有城市信息交煞，并以人口降序輸出，只顯示前10行）

9.8 distinct子句應(yīng)用（去重復(fù)）

select DISTINCT countrycode from city;（查詢所有國家的代號信息可岂，有的代號一樣错敢，這里就去重復(fù)了）

9.9 union與union all子句應(yīng)用（聯(lián)合查詢）

9.9.1 舉例

---查看山東省或河北省信息
（原語句）：
- select *  from city where district='shandong'  or  district='hebei';
（union all優(yōu)化）：
- select * from city where  district='shandong' union all select *  from city  where district='hebei';

---查看中國或美國的所有信息
（原語句）：
- select * from city  where countrycode  in('CHN','USA');
（union all優(yōu)化）：
- select * from city where countrycode='CHN'  union all  select *  from city  where countrycode='USA';

9.9.2 說明

一般情況下，我們會將in或or的語句改寫為union all語句來提高性能（并不是所有語句越復(fù)雜性能越差缕粹，這就是個實(shí)例）

9.9.3 union與union all的區(qū)別

union帶有去重復(fù)的功能（隱含了排序功能稚茅，更耗費(fèi)資源）；而union all則沒有去重復(fù)功能

十. 多表連接查詢

10.1 多表連接作用

業(yè)務(wù)需要的數(shù)據(jù)來自多張表平斩，則需要各張表的關(guān)聯(lián)項(xiàng)來進(jìn)行選取所需

10.2 多表連接類型

內(nèi)連接（重要）

外連接（一般）

全連接（普通）

笛卡爾（普通）

10.3 多表連接基本語法（內(nèi)連接類型）

傳統(tǒng)連接（普通）

自連接（普通）

join uing（普通）

join on （重要）

10.4 join on語法過程（內(nèi)連接）

兩表

select ---（顯示內(nèi)容亚享，看題意）
from 第一表
join  第二表
on  第一表的內(nèi)容=第二表的內(nèi)容（兩表的關(guān)聯(lián)列）
where 條件
group by 條件
having  條件
order by  條件
limit 條件

多表

select  （顯示內(nèi)容看要求）
from  第一表
join  第二表
on  第一表內(nèi)容=第二表內(nèi)容（第一第二表的關(guān)聯(lián)列）
join  第三表
on  第二表的內(nèi)容=第三表的內(nèi)容（第二第三表的關(guān)聯(lián)列）
join ----
on ----
where  條件
group  by  條件
having  條件
order  by  條件
limit 條件

10.5 多表連接查詢套路

根據(jù)要求找到關(guān)聯(lián)列

找到表與表的關(guān)聯(lián)列

列名調(diào)用時(shí)，需要添加關(guān)聯(lián)列绘面，例如：a.id b.name

10.6 給與如下關(guān)聯(lián)欺税，進(jìn)行后續(xù)多表連接案例

1. course（課程表）與sc（成績表）關(guān)聯(lián)列為cno（課程編號）
2. course（課程表）與teacher（教師表）關(guān)聯(lián)列為tno（教師編號）
3. sc（成績表）與student（學(xué)生表）關(guān)聯(lián)列為sno（學(xué)生編號）

10.7 多表連接查詢案例

（1）查詢張三學(xué)習(xí)了幾門課程
---分析：
1. 由題意看出需要的是學(xué)生表與課程表
2. 但學(xué)生表與課程表并沒有關(guān)聯(lián)列，而成績表與課程表有關(guān)聯(lián)列揭璃，所以需要成績表進(jìn)行過渡（三標(biāo)連接）
3. 再分析題意晚凿，需要的是學(xué)生學(xué)習(xí)了幾門課程，而課程同樣在成績表中也能顯示瘦馍，同時(shí)成績表與學(xué)生表是有關(guān)聯(lián)列的歼秽，所以直接應(yīng)用學(xué)生表與成績表兩表關(guān)聯(lián)即可
---答題：
select student.sname,COUNT(sc.cno)   from student  join  sc on student.sno=sc.sno  where  student.sname='zhang3';

（2）統(tǒng)計(jì)oldguo老師教的學(xué)生個數(shù)
select teacher.tname,COUNT(student.sno)
from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
join student
on sc.sno=student.sno
where teacher.tname='oldguo';

（3）統(tǒng)計(jì)每位老師所教的課程的平均分，并按平均分排序
select teacher.tname,AVG(sc.score)
from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
group by teacher.tno,teacher.tname
order by AVG(sc.score);

（4）統(tǒng)計(jì)zhang3學(xué)習(xí)的課程名稱
select  student.sname,GROUP_CONCAT(course.cname)
from student
join sc
on student.sno=sc.sno
join course
on sc.cno=course.cno
where student.sname='zhang3';

（5）查詢oldguo所教不及格的學(xué)生
select teacher.tname,GROUP_CONCAT(student.sname)
from teacher 
join  course
on teacher.tno=course.tno
join  sc
on course.cno=sc.cno
join student
on sc.sno=student.sno
where teacher.tname='oldguo'  and  sc.score<60;

（6）查詢所有老師所教的學(xué)生不及格的信息
select teacher.tname,GROUP_CONCAT(student.sname)
from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
join student
on sc.sno=student.sno
where sc.scroe<60
GROUP BY  teacher.tname;

10.8 別名（表別名情组，列別名）

表別名

（1）表別名舉例（查詢所有老師所教學(xué)生不及格的信息）
select a.tname,GROUP_CONCAT(d.sname)
from teacher AS a
join course  AS b
on a.tno=b.tno
join sc  AS c
on b.cno=c.cno
join student  AS  d
on c.sno=d.sno
where c.score<60
GROUP BY a.tname;

（2）表別名說明
1. AS可以省略燥筷，直接加別名即可，但是為了區(qū)分最好加上
2. 表別名一般是在from后的表或join后的表定義的別名
3. 表別名定義后在select院崇，where肆氓，group by，having以及order by后都可以應(yīng)用

列別名

（1）列別名舉例（統(tǒng)計(jì)每位老師所教課程的平均分底瓣，并按平均分排序）
select a.tname AS 講師谢揪，AVG(c.score) AS  平均分
from teacher  as a
join course as b
on a.tno=b.tno
join sc as c
on b.cno=c.cno
GROUP BY a.tno
ORDER BY 平均分；

（2）列別名說明
1. 列別名一般是在select后定義的別名
2. 結(jié)果集顯示會以別名的形式展示
3. 在having與order by字句中應(yīng)用

十一. 外連接簡介

11.1 A left join B（左外連接）

應(yīng)用方式：
...
A left join B
on  A表中的所有條件列=B表中符合兩表的關(guān)聯(lián)條件的列（A.xx=B.yy）
and 條件（進(jìn)行過濾所需，外連接沒有where等字句賽選條件键耕，只有and來代替）

11.2 A right join B（右外連接）

應(yīng)用方式：
...
A right  join B
on A表中符合兩表的關(guān)聯(lián)條件的列=B表中所有條件列（A.xx=B.yy）
and  條件

11.3 結(jié)論

多表連接中寺滚，小表驅(qū)動大表
通過left/right join強(qiáng)制選定驅(qū)動表

十二. 元數(shù)據(jù)獲取

12.1 元數(shù)據(jù)包含內(nèi)容

基表---數(shù)據(jù)字典信息（列結(jié)構(gòu)frm）柑营，系統(tǒng)狀態(tài)信息屈雄，對象狀態(tài)信息，不能直接操作官套，只能用專門的管理命令進(jìn)行修改（DDL語句酒奶，DCL語句等），視圖information_schema與show語句查詢奶赔⊥锖浚基表就相當(dāng)于linux中的inode，存放各種屬性信息站刑。

12.2 獲取方式

1. show database;   #查看數(shù)據(jù)庫中所有庫的信息
2. show tables;   #查看庫下所有表
3. show tables from ...;   #查詢某個指定庫下的所有表
4. show create database world;   #查看world庫的建庫語句
5. show create table world.city;   #查看world庫下city表的建表語句
6. show grants  for root@'localhost';   #查看用戶權(quán)限信息
7. show charset;   #查看字符集
8. show collation;   #查看校對規(guī)則
9. show processlist;   #查看數(shù)據(jù)庫連接情況
10. show  index  from ...   #查看某個表的索引情況
11. show  status;   #查看數(shù)據(jù)庫狀態(tài)信息
12. show status like '%lock%'   #模糊查看數(shù)據(jù)庫某些狀態(tài)信息
13. show  variables;   #查看數(shù)據(jù)庫所有配置信息
14. show variables  like  '%lock%'   #模糊查找數(shù)據(jù)庫默寫配置信息
15. show engines;   #查看數(shù)據(jù)庫支持的所有的存儲引擎
16. show engines  innodb  status  \G;    #查看關(guān)于innodb引擎所有狀態(tài)信息
17. show  binary  logs;   #列舉出所有二進(jìn)制日志
18. show  master status;   #查看數(shù)據(jù)路的日志位置信息
19. show  binlog  events  in ... ;    #查看二進(jìn)制日志事件
20. show slave status  \G;   #查看數(shù)據(jù)庫從庫狀態(tài)
21. show relaylog  events;   #查看從庫relaylog事件信息
22. desc  (show colums  from city)   #查看表的列定義信息

12.3 視圖views：information_schema（虛擬庫）查詢

12.3.1 創(chuàng)建視圖test

create view  test  as select
country.name  as co_name,country.Surface Area,city.name as ci_name,city.Population
from city  join country
on city.xountrycode=country.code
where city.population<100;

12.3.2 查看視圖test

select * from test;

12.3.3 tables視圖的作用和結(jié)構(gòu)

（1）tables視圖作用

存儲整個數(shù)據(jù)庫的所有表的元數(shù)據(jù)查詢方式
（2）tables下重要列

1. table_schema：表所在的庫名
2. table_name：表名
3. engine：表的引擎
4. table_rows：表的行數(shù)
5. avg_row_length：表中行的平均行（字節(jié)）
6. index_length：索引的占用空間大辛砦椤（字節(jié)）

12.3.4 tables視圖查詢舉例

（1）查詢world庫下所有表名
- show tables  from world;

（2）查詢整個數(shù)據(jù)庫中的所有庫和對應(yīng)表的信息
select table_schema,GROUP_CONCAT(table_name)
from  information_schema.tables
GROUP BY table_schema;

（3）統(tǒng)計(jì)所有庫下表的個數(shù)
select table_schema,COUNT(table_name)
from information_schema.tables 
GROUP BY table_schema;

（4） 查詢所有innodb引擎的表所在的庫
select table_schema,table_name.ENGINE
from information_schema.tables
where ENGINE='innodb';

（5）統(tǒng)計(jì)每張表的實(shí)際占用空間大小情況
（AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH）統(tǒng)計(jì)大小默認(rèn)為字節(jié)，其中AVG_ROW_LENGTH與TABLE_ROWS可以互換位置

select table_name,AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH
from information_schema.tables;

（6）統(tǒng)計(jì)world庫下每張表的磁盤空間
select table_name,CONCAT((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024,"  KB")  as size_KB
from  information_schema.tables
where table_schema='world';

（7）統(tǒng)計(jì)每個庫（所有庫）的總的磁盤空間占用（數(shù)據(jù)庫查詢绞旅，如下命令是最準(zhǔn)確的數(shù)據(jù)量級查詢摆尝，與
linux中的“du  -sh”的區(qū)別很大，linux中的查詢結(jié)果還包含了很多日志之類的文件因悲，并不是真正的數(shù)據(jù)量級）

select table_schema
CONCAT(SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)1024,"  KB")  AS Total_KB
from information_schema.tables
GROUP BY table_schema;

linux命令行：
mysql -uroot -p123 -e  "select table_schema CONCAT(SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)1024,"  KB")  AS Total_KB   from information_schema.tables  GROUP BY table_schema;"

（8）對整個數(shù)據(jù)庫下的所有表進(jìn)行分庫分表備份（生成單獨(dú)備份語句）
模板語句：
mysqldump  -uroot -p123 world city  >/tmp/world_city.sql
分析：
實(shí)際上就是進(jìn)行上述的一個批量的語句拼接過程（CONCAT）
答題：
select CONCAT("mysqldump  -uroot -p123 ",table_schema," ",table_name,"  >/tmp",table_schema,"_",table_name,".sql")
from information_schema.tables
where table_schema  NOT IN('information_schema','performance_schema','sys')
INTO OUTFILE  '/tmp/bak.sh';

（9）進(jìn)行模仿一下模板語句的批量操作
模板語句： 
alter table world.city  discard  tablespace;
操作：
select CONCAT("alter table  ",table_schema,".",table_name,"  discard  tablespace")
from information_schema.tables
where  table_schema='world'
INTO OUTFILE  '/tmp/dis.sql';

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末堕汞，一起剝皮案震驚了整個濱河市，隨后出現(xiàn)的幾起案子晃琳，更是在濱河造成了極大的恐慌讯检，老刑警劉巖，帶你破解...
沈念sama閱讀 206,723評論 6贊 481
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件卫旱，死亡現(xiàn)場離奇詭異人灼，居然都是意外死亡，警方通過查閱死者的電腦和手機(jī)顾翼，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 88,485評論 2贊 382
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門投放，熙熙樓的掌柜王于貴愁眉苦臉地迎上來，“玉大人暴构，你說我怎么就攤上這事跪呈。” “怎么了取逾？”我有些...
開封第一講書人閱讀 152,998評論 0贊 344
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵耗绿，是天一觀的道長。經(jīng)常有香客問我砾隅，道長误阻，這世上最難降的妖魔是什么？我笑而不...
開封第一講書人閱讀 55,323評論 1贊 279
?港島之戀（遺憾婚禮）
正文為了忘掉前任，我火速辦了婚禮究反，結(jié)果婚禮上寻定，老公的妹妹穿的比我還像新娘。我一直安慰自己精耐，他們只是感情好狼速，可當(dāng)我...
茶點(diǎn)故事閱讀 64,355評論 5贊 374
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布。她就那樣靜靜地躺著卦停，像睡著了一般向胡。火紅的嫁衣襯著肌膚如雪。梳的紋絲不亂的頭發(fā)上惊完，一...
開封第一講書人閱讀 49,079評論 1贊 285
城市分裂傳說
那天僵芹，我揣著相機(jī)與錄音，去河邊找鬼小槐。笑死拇派，一個胖子當(dāng)著我的面吹牛，可吹牛的內(nèi)容都是我干的凿跳。我是一名探鬼主播件豌，決...
沈念sama閱讀 38,389評論 3贊 400
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼，長吁一口氣：“原來是場噩夢啊……” “哼拄显！你這毒婦竟也來了苟径？” 一聲冷哼從身側(cè)響起，我...
開封第一講書人閱讀 37,019評論 0贊 259
萬榮殺人案實(shí)錄
序言：老撾萬榮一對情侶失蹤躬审，失蹤者是張志新（化名）和其女友劉穎棘街，沒想到半個月后，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體承边，經(jīng)...
沈念sama閱讀 43,519評論 1贊 300
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡遭殉，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 35,971評論 2贊 325
?白月光啟示錄
正文我和宋清朗相戀三年，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了博助。大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片险污。...
茶點(diǎn)故事閱讀 38,100評論 1贊 333
活死人
序言：一個原本活蹦亂跳的男人離奇死亡，死狀恐怖富岳，靈堂內(nèi)的尸體忽然破棺而出蛔糯，到底是詐尸還是另有隱情，我是刑警寧澤窖式，帶...
沈念sama閱讀 33,738評論 4贊 324
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布蚁飒，位于F島的核電站，受9級特大地震影響萝喘，放射性物質(zhì)發(fā)生泄漏淮逻。R本人自食惡果不足惜琼懊，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 39,293評論 3贊 307
男人毒藥：我在死后第九天來索命
文/蒙蒙一、第九天我趴在偏房一處隱蔽的房頂上張望爬早。院中可真熱鬧哼丈，春花似錦、人聲如沸筛严。這莊子的主人今日做“春日...
開封第一講書人閱讀 30,289評論 0贊 19
一樁弒父案，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽脑漫。三九已至髓抑，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間优幸，已是汗流浹背。一陣腳步聲響...
開封第一講書人閱讀 31,517評論 1贊 262
情欲美人皮
我被黑心中介騙來泰國打工褪猛，沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留网杆，地道東北人。一個月前我還...
沈念sama閱讀 45,547評論 2贊 354
代替公主和親
正文我出身青樓伊滋，卻偏偏與公主長得像碳却，于是被迫代替她去往敵國和親。傳聞我的和親對象是個殘疾皇子笑旺，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 42,834評論 2贊 345