1. DDL應(yīng)用

1.1 數(shù)據(jù)定義語(yǔ)言

1.2 庫(kù)定義

1.2.1 創(chuàng)建數(shù)據(jù)庫(kù)

create database school charset utf8mb4;   #指定字符集
create database school charset utf8mb4 collate utf8mb4_bin;  #指定校對(duì)規(guī)則（默認(rèn)大小寫不敏感）

show charset;      #查看數(shù)據(jù)庫(kù)中支持的字符集
show cllocation;   #查看校對(duì)規(guī)則

建庫(kù)規(guī)范：
1.庫(kù)名不能有大寫字母   
2.建庫(kù)要加字符集         
3.庫(kù)名不能有數(shù)字開頭
4. 庫(kù)名要和業(yè)務(wù)相關(guān)

建庫(kù)標(biāo)準(zhǔn)語(yǔ)句

damao[(none)]>create database test charset utf8mb4;
Query OK, 1 row affected (0.01 sec)

damao[(none)]>show create database test;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

damao[(none)]>

1.2.2 刪除數(shù)據(jù)庫(kù)（生產(chǎn)中禁止使用）

damao[(none)]>drop database test;

1.2.3 查詢及修改數(shù)據(jù)庫(kù)

show create database test;  #查看數(shù)據(jù)庫(kù)的字符集及較對(duì)規(guī)則
alter database test charset utf8;
注意：修改字符集不會(huì)使數(shù)據(jù)庫(kù)中原有的數(shù)據(jù)字符集發(fā)生改變偿短，只會(huì)改變新數(shù)據(jù)的字符集冗锁，所以要將原有的數(shù)據(jù)導(dǎo)出更改字符集再進(jìn)行導(dǎo)入油宜。
注意：修改字符集，修改后的字符集一定是原字符集的嚴(yán)格超集

1.3 表定義

1.3.1 創(chuàng)建表

create table stu(
列1  屬性（數(shù)據(jù)類型沿癞、約束、其他屬性） ，
列2  屬性匆帚，
列3  屬性
) 存儲(chǔ)引擎，字符集編碼旁钧，注釋;

1.3.2 建表

use school;  #首先要進(jìn)入到相應(yīng)的庫(kù)中

CREATE TABLE stu(
id      INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '學(xué)號(hào)',
sname   VARCHAR(64) 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  DATETIME NOT NULL DEFAULT NOW() COMMENT '入學(xué)時(shí)間'
) ENGINE=INNODB CHARSET=utf8mb4 COMMENT '學(xué)生表';

建表規(guī)范

1. 表名小寫
2. 不能是數(shù)字開頭
3. 注意字符集和存儲(chǔ)引擎
4. 表名和業(yè)務(wù)有關(guān)
5. 選擇合適的數(shù)據(jù)類型
6. 每個(gè)列都要有注釋
7. 每個(gè)列設(shè)置為非空吸重，無(wú)法保證非空，用0來(lái)填充歪今。

1.3.3 刪除表（生產(chǎn)中禁用命令）

drop table stu;       *刪除整個(gè)表（表結(jié)構(gòu)+數(shù)據(jù)行）
truncate table stu;  *只會(huì)刪除表中的數(shù)據(jù)行嚎幸，不會(huì)刪除表結(jié)構(gòu)

1.3.4 修改表

1.在school庫(kù)的stu表中添加qq列

alter table stu add qq varchar(20) not null unique comment 'QQ號(hào)';

2.在sname后加入地址列

alter table stu add addr varchar(64) not null commit '地址' after sname;

3.在id列前加入新列 num

alter table stu add num int not null comment '數(shù)字' first;

4.

alter table stu drop num;
alter table stu drop addr;
alter table stu drop qq;

5.修改sname列的數(shù)據(jù)類型屬性

alter table stu modify sname varchar(32) not null;

6.將sgender列改為sg列，將數(shù)據(jù)類型改為char類型

alter table stu change sgender sg tinyint not null default 0 comment '0是女生彤委，1是男生';
desc stu;

7.修改表名

alter table stu reanme to xuesheng;

說(shuō)明：歸檔表鞭铆，日志表
作為一個(gè)企業(yè)或者DBA，我們通常會(huì)有這種想法，數(shù)據(jù)是一個(gè)公司的核心命脈车遂，應(yīng)該需要永久保存封断，很多時(shí)候DBA和開發(fā)溝通的時(shí)候，開發(fā)人員也會(huì)這么告訴我們舶担，這份數(shù)據(jù)非常重要坡疼，數(shù)據(jù)需要永久保存。然而衣陶，如果將數(shù)據(jù)庫(kù)的數(shù)據(jù)永久保存柄瑰，那么遲早有一天，你會(huì)擁有一個(gè)非常大的數(shù)據(jù)庫(kù)剪况。作為一個(gè)DBA教沾，通常為了業(yè)務(wù)對(duì)數(shù)據(jù)庫(kù)的操作性能考慮和存儲(chǔ)容量的考慮。我們會(huì)建議對(duì)數(shù)據(jù)庫(kù)里大表進(jìn)行數(shù)據(jù)歸檔译断，例如將使用的高頻數(shù)據(jù)保留在當(dāng)前表授翻，對(duì)低頻數(shù)據(jù)保留在歸檔表中，或定期對(duì)數(shù)據(jù)進(jìn)行歸檔孙咪，或當(dāng)數(shù)據(jù)達(dá)到一定量時(shí)對(duì)數(shù)據(jù)表進(jìn)行歸檔處理堪唐；

歸檔表原理操作（類似于日志切割）
1.方式一
alter table stu rename to stu_2019_11;  *將舊表改名
create table stu like stu_2019_11;          *創(chuàng)建新表

2.方式二
 Oracle 物化視圖 (了解)

面試題：若開發(fā)要緊急上線DDL SQL，如何進(jìn)行評(píng)估翎蹈，請(qǐng)寫審核SQL要點(diǎn)

答：SQL語(yǔ)句是數(shù)據(jù)DDL操作淮菠，是屬于列的添加操作
直接執(zhí)行時(shí)會(huì)產(chǎn)生表鎖，對(duì)業(yè)務(wù)的影響較大荤堪，數(shù)據(jù)量大合陵，業(yè)務(wù)量大時(shí)，對(duì)于業(yè)務(wù)影響較大逞力。
所以我們推薦
(1)最好業(yè)務(wù)不繁忙做
(2)使用 pt-osc(percona-toolkit) 工具來(lái)進(jìn)行online DDL,減少對(duì)業(yè)務(wù)的影響
https://www.percona.com/software/database-tools/percona-toolkit
(3)對(duì)于歸檔表,可以使用pt-archiver 自己擴(kuò)展.

1.3.5 表屬性查詢

use school  *進(jìn)入到指定庫(kù)
show tables;  *查看表
desc stu;       *查看表結(jié)構(gòu)
show create table stu;  *查看表（列）屬性

2. DCL　數(shù)據(jù)控制語(yǔ)言

grant  權(quán)限 on 庫(kù)表 to 用戶名@'白名單' identified by '密碼';
revoke 權(quán)限 on 庫(kù)表 from 用戶名@'白名單' identified by '密碼';

3. DML 數(shù)據(jù)操縱語(yǔ)言

主要對(duì)表中數(shù)據(jù)行操作

3.1 insert 數(shù)據(jù)插入

-----標(biāo)準(zhǔn)寫法
use database school;
desc stu;
INSERT INTO stu(id,sname,sage,sg,sfz,intime) 
VALUES
(1,'zs',18,'m','123456',NOW());

-----省事寫法
INSERT INTO stu 
VALUES
(2,'ls',18,'m','1234567',NOW());

--- 針對(duì)性的錄入數(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;

3.2 數(shù)據(jù)修改（更新）

update stu  set age=18 where name='zs';
注意：update語(yǔ)句必須要加where曙寡。

3.3 delete 數(shù)據(jù)刪除

-----刪除id為4的數(shù)據(jù)行
delete from stu where id=4;

------若不跟條件，則刪除所有數(shù)據(jù)行,類似于 truncate table stu
delete from stu ;
說(shuō)明：
DELETE 語(yǔ)句數(shù)據(jù)邏輯刪除,磁盤空間不會(huì)立即釋放,會(huì)產(chǎn)生碎片.
truncate會(huì)立即釋放磁盤空間
思考: 2億行的大表,批量刪除5000w(按時(shí)間條件)

3.4 偽刪除

刪除id=5的行,偽刪除實(shí)現(xiàn)
ALTER TABLE stu ADD state TINYINT NOT NULL DEFAULT 1;
SELECT * FROM stu;
原語(yǔ)句:
DELETE FROM stu WHERE id=5;
替換為: 
UPDATE stu SET state=0 WHERE id=5;

原業(yè)務(wù)語(yǔ)句:
SELECT * FROM stu;
替換為:
SELECT * FROM stu WHERE state=1;

4. DQL 數(shù)據(jù)查詢語(yǔ)言 ********

4.1 select ******

4.1.1 單獨(dú)使用

（1）查詢參數(shù)

SELECT @@datadir;
SELECT @@port;
SELECT @@socket;
SELECT @@basedir;
SELECT @@innodb_flush_log_at_trx_commit;
SHOW VARIABLES ;      **配置文件中的所有參數(shù)

（2）簡(jiǎn)易計(jì)算

select 1024*1024;
select   1+1寇荧；

（3）函數(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;
https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html?tdsourcetag=s_pcqq_aiomsg

4.1.2 配合多子句(單表查詢標(biāo)準(zhǔn)用法)

多子句應(yīng)用順序: *****
(1) FROM     必須使用的
(2) WHERE    
(3) GROUP BY 
(5) HAVING 
(6) ORDER BY  
(7) LIMIT 

（1）單表子句 ---from

SELECT 列1,列2 FROM 表
SELECT  *  FROM 表

例：

--- 1. 查詢city表中的所有數(shù)據(jù)(不要對(duì)大表進(jìn)行操作)
SELECT * FROM city;
--- 2. 查詢city表中所有的name和population
SELECT NAME,population FROM city;

（2）單表子句----where

SELECT 列1,列2 FROM TABLE WHERE 條件;

1.where 配合等值查詢

--- 查詢中國(guó)所有城市的信息
SELECT * FROM city WHERE countrycode='CHN';

-- 查詢北京市的信息
SELECT * FROM city WHERE NAME='peking';

-- 查詢甘肅省所有城市信息
SELECT * FROM city WHERE district='gansu';

2.where 的不等值查詢(> < >= <= !=)

--- 1. 城市人口小于100人的城市
SELECT * FROM city WHERE population<100;

--- 2. 不是中國(guó)的城市信息 
SELECT * FROM city WHERE countrycode <> 'CHN';

3.where 配合 like語(yǔ)句使用（模糊查詢）

--- 1. 查詢國(guó)家代號(hào)是CH開頭的城市信息.
SELECT * FROM city WHERE countrycode LIKE 'CH%';

注意: LIKE 語(yǔ)句 百分號(hào)不要出現(xiàn)在查詢條件前,因?yàn)椴樵儾蛔咚饕?

4.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';

5.where配合in語(yǔ)句(or)

--- 中國(guó)或者美國(guó)城市信息
SELECT * FROM city WHERE countrycode IN ('CHN','USA');

6.where 配合 between ...and....(and)

--- 查詢 人口數(shù)量在100w-200w之間的城市
SELECT * FROM city WHERE population >= 1000000 AND population <= 2000000;

SELECT * FROM city WHERE population BETWEEN 1000000 AND 2000000;

（3）group by （分組）+ 聚合函數(shù)應(yīng)用

常用聚合函數(shù)
MAX()        最大值
MIN()        最小值
AVG()        平均值
COUNT()      數(shù)量
SUM()        求和
GROUP_CONCAT() 列轉(zhuǎn)行

說(shuō)明：
有 GROUP by子句,必然會(huì)有聚合函數(shù)
在業(yè)務(wù)查詢需求中,需要對(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;

關(guān)于 GROUP BY 的限制
group by的工作原理举庶，先將group by之后的所寫之列進(jìn)行排序，然后去重揩抡，再將函數(shù)中包含的列進(jìn)行函數(shù)運(yùn)算户侥；

damao[(none)]>select countrycode,district,sum(population) from world.city group by district;
ERROR 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)系.

（4）having
作用：用于進(jìn)一步過(guò)濾，在group by 之后執(zhí)行

where | group by |having   #使用順序

例：統(tǒng)計(jì)中國(guó)每個(gè)省的總?cè)丝跀?shù)峦嗤，只打印總?cè)丝跀?shù)小于100萬(wàn)

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

（5）order by + limit
作用：用于將將指定列數(shù)據(jù)進(jìn)行排序

----1.查看中國(guó)所有的城市蕊唐，并按人口數(shù)進(jìn)行排序(從大到小)

select name,population from city
where countrycode='CHN'
order by population desc;
注：加上desc表示逆序（從大到小）烁设，不加為升序（從小到大）

----2.統(tǒng)計(jì)中國(guó)各個(gè)省的總?cè)丝跀?shù)量替梨，按照總?cè)丝趶拇蟮叫∨判?/p>

SELECT district AS 省 ,SUM(Population) AS 總?cè)丝?FROM city
WHERE countrycode='chn'
GROUP BY district
ORDER BY 總?cè)丝?DESC ;

----3.統(tǒng)計(jì)中國(guó),每個(gè)省的總?cè)丝?找出總?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 3 ;    注：此時(shí)相當(dāng)于為limit 0,3. 跳過(guò)0行钓试，顯示3行

注：LIMIT N ,M --->跳過(guò)N,顯示一共M行
LIMIT 5,5 跳過(guò)五行，一共顯示5行副瀑。顯示6-10行  

SELECT  district, SUM(population)  FROM  city 
WHERE countrycode='CHN'
GROUP BY district 
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 5,5;

4.1.3 distinct：去重復(fù)

----從city表中查出國(guó)家名稱

SELECT countrycode FROM city ;
SELECT DISTINCT(countrycode) FROM city  ;

4.1.4 聯(lián)合查詢- union all 將多個(gè)select的數(shù)據(jù)合并(縱向合并)顯示

-- 中國(guó)或美國(guó)城市信息

SELECT * FROM city 
WHERE countrycode IN ('CHN' ,'USA');

SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA'

說(shuō)明:一般情況下,我們會(huì)將 IN 或者 OR 語(yǔ)句 改寫成 UNION ALL,來(lái)提高性能
UNION     去重復(fù)
UNION ALL 不去重復(fù)

4.1.5 join 多表連接查詢

案例準(zhǔn)備弓熏，按需創(chuàng)建表

CREATE DATABASE school CHARSET utf8mb4;
USE school

CREATE TABLE student(
sno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '學(xué)號(hào)',
sname VARCHAR(20) NOT NULL COMMENT '姓名',
sage TINYINT UNSIGNED  NOT NULL COMMENT '年齡',
ssex  ENUM('f','m') NOT NULL DEFAULT 'm' COMMENT '性別'
)ENGINE=INNODB CHARSET=utf8;

CREATE TABLE course(
cno INT NOT NULL PRIMARY KEY COMMENT '課程編號(hào)',
cname VARCHAR(20) NOT NULL COMMENT '課程名字',
tno INT NOT NULL  COMMENT '教師編號(hào)'
)ENGINE=INNODB CHARSET utf8;

CREATE TABLE sc (
sno INT NOT NULL COMMENT '學(xué)號(hào)',
cno INT NOT NULL COMMENT '課程編號(hào)',
score INT  NOT NULL DEFAULT 0 COMMENT '成績(jī)'
)ENGINE=INNODB CHARSET=utf8;

CREATE TABLE teacher(
tno INT NOT NULL PRIMARY KEY COMMENT '教師編號(hào)',
tname VARCHAR(20) NOT NULL COMMENT '教師名字'
)ENGINE=INNODB CHARSET utf8;

INSERT INTO student(sno,sname,sage,ssex)
VALUES 
(1,'zhang3',18,'m'),
(2,'zhang4',18,'m'),
(3,'li4',18,'m'),
(4,'wang5',19,'f'),
(5,'zh4',18,'m'),
(6,'zhao4',18,'m'),
(7,'ma6',19,'f'),
(8,'oldboy',20,'m'),
(9,'oldgirl',20,'f'),
(10,'oldp',25,'m');


INSERT INTO teacher(tno,tname) VALUES
(101,'oldboy'),
(102,'hesw'),
(103,'oldguo');



INSERT INTO course(cno,cname,tno)
VALUES
(1001,'linux',101),
(1002,'python',102),
(1003,'mysql',103);


INSERT INTO sc(sno,cno,score)
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),
(8,1001,70),
(9,1003,80),
(10,1003,96);

語(yǔ)法

image.png

查詢張三的家庭住址

SELECT A.name,B.address FROM
A JOIN  B
ON A.id=B.id
WHERE A.name='zhangsan';

多表練習(xí)SQL題

-- 1. 每位老師講的課程名稱
SELECT teacher.tno,teacher.tname,GROUP_CONCAT(course.cname)
FROM teacher 
JOIN course
ON teacher.tno=course.tno
GROUP BY teacher.tno;

思考: 如果老師名重名,或者老師講多門課,怎么辦?

-- 2. 每位學(xué)員學(xué)習(xí)了幾門課?
SELECT student.sno,student.sname,COUNT(sc.cno)
FROM student 
JOIN sc
ON student.sno=sc.sno 
GROUP BY student.sno;

-- 3. 每位學(xué)員學(xué)習(xí)了幾門課及課程名稱列表?
SELECT 
student.sno,
student.sname,
COUNT(sc.cno),
GROUP_CONCAT(course.cname)
FROM student 
JOIN sc
ON student.sno=sc.sno 
JOIN course
ON sc.cno=course.cno
GROUP BY student.sno;

** case when ...then.. end 語(yǔ)句**

5. 寫多表連接技巧

1. 相關(guān)表 
2. 找相關(guān)表關(guān)聯(lián)條件
E-R 圖 (自己擴(kuò)展) ----> 開發(fā)DBA要做的事情

外連接

A  left join  B 
A 表所有的數(shù)據(jù)+B表滿足關(guān)聯(lián)條件的數(shù)據(jù)

A  right join B
B表所有的數(shù)據(jù)+A 滿足關(guān)聯(lián)條件的數(shù)據(jù)

實(shí)現(xiàn)外連接原生功能,需要把where的條件改為and/

mysql> select city.name,city.population ,country.name from city left join country on city.countrycode=country.code   and  city.population<100 order by population desc ;

結(jié)論: left join 應(yīng)用場(chǎng)景,強(qiáng)制驅(qū)動(dòng)表(關(guān)聯(lián)查詢中結(jié)果集小的)

1. where 條件
2. 原始表大小

6. 別名的應(yīng)用

表別名: 給表設(shè)計(jì)的別名,在任何子句中調(diào)用
列別名: 給select 后的列設(shè)定別名,在having 和 order by子句中調(diào)用

表別名:

select a.tname AS "老師姓名" ,group_concat(d.sname) AS "不及格的學(xué)生"
from  teacher AS a 
join course AS b 
on a.tno = b.tno
join sc AS c
on c.cno = b.cno
join student AS d
on d.sno = c.sno
where c.score < 60
group by a.tno;

列別名:

select 
student.sno   AS 學(xué)生學(xué)號(hào) ,
student.sname AS 學(xué)生姓名,
avg(sc.score) AS 平均成績(jī)
from student
join sc
on student.sno=sc.sno
group by sutdent.sno
having 平均成績(jī) >85

==================
子查詢 : (自己了解)
高級(jí)SQL編程: 內(nèi)置函數(shù),存儲(chǔ)過(guò)程,函數(shù),視圖,事件,觸發(fā)器,游標(biāo),Json開發(fā) (自己了解)
==================

7. show 語(yǔ)句

show databases ;                      查看所有庫(kù)名
show tables;                          查看當(dāng)前庫(kù)下的所有表名.
show tables from world;               查看world庫(kù)下的所有表
show create database world;           查看建庫(kù)語(yǔ)句
show create tables city;              查看建表語(yǔ)句
show [global] variables like '%trx%'; 查看參數(shù)信息
show grants for root@'localhost'; 查看用戶權(quán)限信息
show [full] processlist;      查看會(huì)話連接情況 
show engines;                 查看當(dāng)前數(shù)據(jù)庫(kù)支持的引擎.
show charset;                 查看當(dāng)前數(shù)據(jù)庫(kù)支持的字符集.
show collation;               查看當(dāng)前數(shù)據(jù)庫(kù)支持的排序規(guī)則.
show [global] status;         查看當(dāng)前數(shù)據(jù)庫(kù)的狀態(tài)信息.
show status like '%lock%'\G   模糊查詢數(shù)據(jù)庫(kù)狀態(tài).
show master status;           查看當(dāng)前使用的二進(jìn)制日志信息.
show binary logs;             查看所有二進(jìn)制日志信息.
show binlog evnets in 'xxxx'  查看二進(jìn)制日志事件信息.
show relaylog events in 'xxx' 查看中繼日志事件信息.
show slave status \G          查看從庫(kù)復(fù)制狀態(tài)信息.
show engine innodb status \G  查看InnoDB引擎相關(guān)狀態(tài)信息.

======================

8. 元數(shù)據(jù)獲取

8.1 什么是元數(shù)據(jù)?

庫(kù),表   : 屬性(字符集,校對(duì)規(guī)則,數(shù)據(jù)類型,存儲(chǔ)引擎,約束,其他數(shù)據(jù))
權(quán)限    :
狀態(tài)信息:
等.

8.2 元數(shù)據(jù)獲取方法

show語(yǔ)句                : 封裝好的基礎(chǔ)功能,可以實(shí)現(xiàn)大部分的元數(shù)據(jù)查詢需求.     
information_schema<視圖>庫(kù):  mysql 給我們定義好的元數(shù)據(jù)查詢的方法.

8.3 information_schema<視圖>庫(kù)

應(yīng)用場(chǎng)景: 做數(shù)據(jù)庫(kù)資產(chǎn)統(tǒng)計(jì).
tables : 
TABLE_SCHEMA  :  表所在的庫(kù)
TABLE_NAME    :  表名
ENGINE        :  引擎
TABLE_ROWS    :  表的行數(shù)
AVG_ROW_LENGTH:  平均行長(zhǎng)度
INDEX_LENGTH  :  索引長(zhǎng)度
TABLE_COMMENT :  表的注釋

create view  v_xxx as
select a.tname AS "老師姓名" ,group_concat(d.sname) AS "不及格的學(xué)生"
    from  teacher AS a 
    join course AS b 
    on a.tno = b.tno
    join sc AS c
    on c.cno = b.cno
    join student AS d
    on d.sno = c.sno
    where c.score < 60
    group by a.tno;

select  * from v_xxx ;

例子:
----- 1. 統(tǒng)計(jì)所有庫(kù)下的表的個(gè)數(shù)

select table_schema,count(table_name) from information_schema.tables group by table_schema;

----- 2. 統(tǒng)計(jì)不同存儲(chǔ)引擎的表名

select engine,group_concat(table_name) from information_schema.tables group by engine;

-----3. 統(tǒng)計(jì)所有非系統(tǒng)表,非InnoDB的表

mysql 
information_schema 
performace_schema 
sys

SELECT table_schema,table_name ,ENGINE 
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
HAVING  ENGINE != 'innodb';

作業(yè):
-- 5. 統(tǒng)計(jì)總數(shù)據(jù)量(不包含系統(tǒng)表)
-- 6. 統(tǒng)計(jì)每個(gè)庫(kù)的數(shù)據(jù)量(不包含系統(tǒng)表)

SELECT 
table_schema,
SUM( TABLE_ROWS * AVG_ROW_LENGTH + index_length)/1024/1024 AS "total_mb"
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
GROUP BY table_schema
ORDER BY total_mb DESC; 

擴(kuò)展題
-- 7. 將school庫(kù)下所有的數(shù)據(jù)字典信息(列名,數(shù)據(jù)類型,注釋信息)進(jìn)行統(tǒng)計(jì)
例如:
id int 城市id
name varchar 城市名

SELECT column_name,column_type,column_comment 
FROM information_schema.columns 
WHERE table_schema='school'; 

.....
-- 8. 模仿以下語(yǔ)句,生成數(shù)據(jù)庫(kù)下單表的備份語(yǔ)句(不包含系統(tǒng)表),并保存至/backup/sh/bak.sh
例子語(yǔ)句:
mysqldump -uroot -p123 world city >/backup/world_city.sql
mysqldump -uroot -p123 world city >/backup/world_city.sql
mysqldump -uroot -p123 world city >/backup/world_city.sql
mysqldump -uroot -p123 world city >/backup/world_city.sql
mysqldump -uroot -p123 world city >/backup/world_city.sql
mysqldump -uroot -p123 world city >/backup/world_city.sql
mysqldump -uroot -p123 world city >/backup/world_city.sql
mysqldump -uroot -p123 world city >/backup/world_city.sql
mysqldump -uroot -p123 world city >/backup/world_city.sql
mysqldump -uroot -p123 world city >/backup/world_city.sql

select  concat('mysqldump -uroot -p123 ' ,table_schema, ' ' ,table_name ', >/backup/',table_schema,'_',table_name,'.sql' ) 
from information_schema.tables
 where table_schema not in ('mysql','information_schema','performance_schema','sys')
 into outfile '/backup/sh/bak.sh';