數(shù)據(jù)庫(kù)的內(nèi)置功能
? ?1.1連接數(shù)據(jù)庫(kù)
-u
-p????
-S
-h
-P
-e
<列子
? ? 1?????mysql -uroot -p -S /tmp/mysql.sock
? ? 2????mysql -uroot -p -h10.20.52.190 -P3306/3307/3308
? ? 3????mysql -uroot- p -e "show databases;" //免交互執(zhí)行sql語(yǔ)句
? ? 4? ? 恢復(fù)數(shù)據(jù)
? ? mysql -uroot -p123 < /root/world.sql
內(nèi)置命令
help?打印MySQL幫助
(\q) Exit mysql. Same as quit. ctrl +d?退出
\G? 將數(shù)據(jù)豎起來(lái)?select * from mysql.user\G;
source? ?恢復(fù)備份文件? source /root/sang.sql;
1、SQL介紹
結(jié)構(gòu)化查詢語(yǔ)言
5.7 以后符合SQL92嚴(yán)格模式
通過(guò)sql_mode參數(shù)來(lái)控制
2、常用SQL分類
DDL：數(shù)據(jù)定義語(yǔ)言
DCL：數(shù)據(jù)控制語(yǔ)言
DML：數(shù)據(jù)操作語(yǔ)言
DQL：數(shù)據(jù)的查詢語(yǔ)言
3、數(shù)據(jù)類型、表屬性、字符集
3.1 數(shù)據(jù)類型
3.1.1 作用
保證數(shù)據(jù)的準(zhǔn)確性和標(biāo)準(zhǔn)性。
3.1.2 種類
數(shù)值類型
tinyint ：-128~127int：-2^31~2^31-1說(shuō)明：手機(jī)號(hào)是無(wú)法存儲(chǔ)到int的。一般是使用char類型來(lái)存儲(chǔ)收集號(hào)
char(11)：定長(zhǎng) 的字符串類型,在存儲(chǔ)字符串時(shí)，最大字符長(zhǎng)度11個(gè)，立即分配11個(gè)字符長(zhǎng)度的存儲(chǔ)空間，如果存不滿，空格填充。varchar(11):變長(zhǎng)的字符串類型看，最大字符長(zhǎng)度11個(gè)。在存儲(chǔ)字符串時(shí)，自動(dòng)判斷字符長(zhǎng)度，按需分配存儲(chǔ)空間。enum('bj','tj','sh')：枚舉類型，比較適合于將來(lái)此列的值是固定范圍內(nèi)的特點(diǎn)，可以使用enum,可以很大程度的優(yōu)化我們的索引結(jié)構(gòu)。
列值不能為空，也是表設(shè)計(jì)的規(guī)范，盡可能將所有的列設(shè)置為非空?？梢栽O(shè)置默認(rèn)值為0
unique key：唯一鍵
列值不能重復(fù)
unsigned：無(wú)符號(hào)
針對(duì)數(shù)字列，非負(fù)數(shù)。
其他屬性:
key:索引
可以在某列上建立索引，來(lái)優(yōu)化查詢
作者：wwwoldguocom
鏈接：http://www.itdecent.cn/p/08c4b78402ff
來(lái)源：簡(jiǎn)書
著作權(quán)歸作者所有。商業(yè)轉(zhuǎn)載請(qǐng)聯(lián)系作者獲得授權(quán)，非商業(yè)轉(zhuǎn)載請(qǐng)注明出處。
DATETIME 范圍為從 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。TIMESTAMP 1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。timestamp會(huì)受到時(shí)區(qū)的影響
3.2 表屬性
3.2.1 列屬性
約束(一般建表時(shí)添加):**primary key**：主鍵約束設(shè)置為主鍵的列，此列的值必須非空且唯一，主鍵在一個(gè)表中只能有一個(gè)，但是可以有多個(gè)列一起構(gòu)成。**notnull**：非空約束列值不能為空，也是表設(shè)計(jì)的規(guī)范，盡可能將所有的列設(shè)置為非空。可以設(shè)置默認(rèn)值為0**unique key**：唯一鍵列值不能重復(fù)**unsigned**：無(wú)符號(hào)針對(duì)數(shù)字列，非負(fù)數(shù)。其他屬性:**key**:索引可以在某列上建立索引，來(lái)優(yōu)化查詢,一般是根據(jù)需要后添加**default**:默認(rèn)值列中，沒有錄入值時(shí)，會(huì)自動(dòng)使用default的值填充**auto_increment**:自增長(zhǎng)針對(duì)數(shù)字列，順序的自動(dòng)填充數(shù)據(jù)（默認(rèn)是從1開始，將來(lái)可以設(shè)定起始點(diǎn)和偏移量）**comment**:注釋
3.2.2 表的屬性
存儲(chǔ)引擎:
InnoDB（默認(rèn)的）
字符集和排序規(guī)則:
utf8? ? ?
utf8mb4
3.3 字符集和校對(duì)規(guī)則
3.3.1 字符集
utf8? ? ? utf8mb4
3.3.2 校對(duì)規(guī)則（排序規(guī)則）
大小寫是否敏感
大小寫敏感utf8mb4_bin
大小寫不敏感utf8mb4_general_ci
4、DDL應(yīng)用
4.1 數(shù)據(jù)定義語(yǔ)言
4.2 庫(kù)定義
4.2.1 創(chuàng)建
4.2.1 創(chuàng)建數(shù)據(jù)庫(kù)
create database school;create schema sch;show charset;show collation;CREATE DATABASE test CHARSET utf8;create database xyz charset utf8mb4 collate utf8mb4_bin;建庫(kù)規(guī)范：1.庫(kù)名不能有大寫字母2.建庫(kù)要加字符集3.庫(kù)名不能有數(shù)字開頭4.庫(kù)名要和業(yè)務(wù)相關(guān)
建庫(kù)標(biāo)準(zhǔn)語(yǔ)句
mysql>create database db charset utf8mb4;mysql>show create database xuexiao;
4.2.2 刪除(生產(chǎn)中禁止使用)
mysql>drop database oldboy;
4.2.3 修改
SHOW CREATE DATABASE school;ALTER DATABASE school? CHARSET utf8;注意：修改字符集，修改后的字符集一定是原字符集的嚴(yán)格超集
4.2.4 查詢庫(kù)相關(guān)信息（DQL）
show databases；show create database oldboy；
4.3 表定義
4.3.1 創(chuàng)建
DEFAULT :一般配合 NOT NULL?使用
UNSIGNED:無(wú)符號(hào)，一般是配合數(shù)字列，非負(fù)數(shù)
COMMENT:注釋
AUTO_INCREMENT:自增長(zhǎng)
create table stu(列1? 屬性（數(shù)據(jù)類型、約束、其他屬性） ，列2? 屬性，列3? 屬性)
myslq 可以設(shè)置數(shù)據(jù)庫(kù)級(jí)別，表級(jí)別，列級(jí)別 字符集編碼；
優(yōu)先級(jí)順序?yàn)椋簲?shù)據(jù)庫(kù)字符集 < 表字符集 < 列字符集；
也就是 上面三個(gè)級(jí)別 字符集不一致時(shí)，以 更小范圍的配置為準(zhǔn)；
例如：數(shù)據(jù)庫(kù)字符集為utf8? 表字符集不設(shè)置的情況下 會(huì)默認(rèn) utf8 ，如果表主動(dòng)設(shè)置了編碼 utf8mb4;那么表的字符集編碼就為utf8MB4;
4.3.2 建表
USEschool;CREATETABLEstu(idINTNOTNULLPRIMARYKEYAUTO_INCREMENTCOMMENT'學(xué)號(hào)',snameVARCHAR(255)NOTNULLCOMMENT'姓名',sageTINYINTUNSIGNEDNOTNULLDEFAULT0COMMENT'年齡',sgenderENUM('m','f','n')NOTNULLDEFAULT'n'COMMENT'性別',sfzCHAR(18)NOTNULLUNIQUECOMMENT'身份證',intimeTIMESTAMPNOTNULLDEFAULTNOW()COMMENT'入學(xué)時(shí)間')ENGINE=INNODBCHARSET=utf8COMMENT'學(xué)生表';
建表規(guī)范：
1. 表名小寫2. 不能是數(shù)字開頭3. 注意字符集和存儲(chǔ)引擎4. 表名和業(yè)務(wù)有關(guān)5. 選擇合適的數(shù)據(jù)類型6. 每個(gè)列都要有注釋7. 每個(gè)列設(shè)置為非空，無(wú)法保證非空，用0來(lái)填充。
展示表
show tables;
展示建表語(yǔ)句
mysql> show create table stu;
4.3.2 刪除(生產(chǎn)中禁用命令)
drop table t1;
4.3.3 修改
在stu表中添加qq列
DESCstu;ALTERTABLEstuADDqqVARCHAR(20)NOTNULLUNIQUECOMMENT'qq號(hào)';
在sname后加微信列
ALTERTABLEstuADDwechatVARCHAR(64)NOTNULLUNIQUECOMMENT'微信號(hào)'AFTERsname;
在id列前加一個(gè)新列num
ALTER TABLE stu ADD num INT NOT NULL COMMENT'數(shù)字'FIRST;DESC stu;
把剛才添加的列都刪掉(危險(xiǎn))
ALTER TABLE stu DROP num;ALTER TABLE stu DROP qq;ALTER TABLE stu DROP wechat;
修改sname數(shù)據(jù)類型的屬性
ALTER TABLE stu MODIFY snameVARCHAR(128)NOTNULL;
將sgender 改為 sg? 數(shù)據(jù)類型改為 CHAR 類型
ALTERTABLEstuCHANGEsgender sgCHAR(1)NOTNULLDEFAULT'n';DESCstu;
4.3.4 表屬性查詢（DQL）
use schoolshow tables；desc stu;show create table stu；CREATE TABLE ceshi LIKE stu;
5. DCL應(yīng)用 ****
grant revoke
6. DML應(yīng)用
6.1 作用
對(duì)表中的數(shù)據(jù)行進(jìn)行增、刪、改
6.2 insert
---最標(biāo)準(zhǔn)的insert語(yǔ)句INSERTINTOstu(id,sname,sage,sg,sfz,intime)VALUES(1,'zs',18,'m','123456',NOW());SELECT*FROMstu;---省事的寫法INSERTINTOstu VALUES(2,'ls',18,'m','1234567',NOW());---針對(duì)性的錄入數(shù)據(jù)INSERTINTOstu(sname,sfz)VALUES('w5','34445788');---同時(shí)錄入多行數(shù)據(jù)INSERTINTOstu(sname,sfz)VALUES('w55','3444578d8'),('m6','1212313'),('aa','123213123123');SELECT*FROMstu;
6.3 update
DESC stu;SELECT * FROM stu;UPDATE stu SET sname='zhao4' WHERE id=2;注意：update語(yǔ)句必須要加where。
6.4 delete（危險(xiǎn)！?。?/p>
DELETE FROM stu? WHERE id=3;
全表刪除:
DELETE FROM stutruncate table stu;區(qū)別:delete:DML操作,是邏輯性質(zhì)刪除,逐行進(jìn)行刪除,速度慢.truncate:DDL操作,對(duì)與表段中的數(shù)據(jù)頁(yè)進(jìn)行清空,速度快.
偽刪除：用update來(lái)替代delete，最終保證業(yè)務(wù)中查不到（select）即可
1.添加狀態(tài)列ALTER TABLE stu ADD state TINYINT NOTNULLDEFAULT1;SELECT*FROM stu;2.UPDATE 替代 DELETEUPDATE stu SET state=0WHERE id=6;3.業(yè)務(wù)語(yǔ)句查詢SELECT*FROM stu WHERE state=1;
7. DQL應(yīng)用(select )
7.1 單獨(dú)使用
-- select @@xxx查看系統(tǒng)參數(shù)SELECT @@port;SELECT @@basedir;SELECT @@datadir;SELECT @@socket;SELECT @@server_id;
-- select 函數(shù)();
SELECTNOW();SELECTDATABASE();SELECTUSER();SELECTCONCAT("hello world");SELECTCONCAT(USER,"@",HOST)FROMmysql.user;SELECTGROUP_CONCAT(USER,"@",HOST)FROMmysql.user;https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html?tdsourcetag=s_pcqq_aiomsg
7.2 單表子句-from
SELECT 列1,列2 FROM 表SELECT? *? FROM 表
例子:
-- 查詢stu中所有的數(shù)據(jù)(不要對(duì)大表進(jìn)行操作)
SELECT * FROM stu ;
-- 查詢stu表中,學(xué)生姓名和入學(xué)時(shí)間
SELECT sname , intime FROM stu;
=====================
oldguo帶大家學(xué)單詞：
world===>世界city===>城市country===>國(guó)家countrylanguage===>國(guó)家語(yǔ)言city:城市表DESC city;ID:城市IDNAME:城市名CountryCode:國(guó)家代碼，比如中國(guó)CHN 美國(guó)USADistrict:區(qū)域Population:人口SHOW CREATE TABLE city;SELECT*FROM city WHERE id<10;
======================
7.3 單表子句-where
SELECT col1,col2 FROM TABLE WHERE colN 條件;
7.3.1 where配合等值查詢
例子:
-- 查詢中國(guó)(CHN)所有城市信息
SELECT * FROM city WHERE countrycode='CHN';
-- 查詢北京市的信息
SELECT * FROM city WHERE NAME='peking';
-- 查詢甘肅省所有城市信息
SELECT * FROM city WHERE district='gansu';
7.3.2 where配合比較操作符(> < >= <= <>)
例子:
-- 查詢世界上少于100人的城市
SELECT * FROM city WHERE population<100;
7.3.3 where配合邏輯運(yùn)算符(and? or )
例子:
-- 中國(guó)人口數(shù)量大于500w
SELECT * FROM city WHERE countrycode='CHN' AND population>5000000;
-- 中國(guó)或美國(guó)城市信息
SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';
7.3.4 where配合模糊查詢
例子:
-- 查詢省的名字前面帶guang開頭的
SELECT*FROMcityWHEREdistrictLIKE'guang%';注意:%不能放在前面,因?yàn)椴蛔咚饕?
7.3.5 where配合in語(yǔ)句
-- 中國(guó)或美國(guó)城市信息
SELECT*FROMcityWHEREcountrycode IN('CHN','USA');
7.3.6 where配合between and
例子:
-- 查詢世界上人口數(shù)量大于100w小于200w的城市信息
SELECT * FROM city? WHERE population >1000000 AND population <2000000;SELECT * FROM city? WHERE population BETWEEN 1000000 AND 2000000;
7.4 group by + 常用聚合函數(shù)
7.4.1 作用
根據(jù) by后面的條件進(jìn)行分組，方便統(tǒng)計(jì)，by后面跟一個(gè)列或多個(gè)列
7.4.2 常用聚合函數(shù)
**max()**：最大值**min()**：最小值**avg()**：平均值**sum()**：總和**count()**：個(gè)數(shù)group_concat():列轉(zhuǎn)行
7.4.3 例子：
例子1：統(tǒng)計(jì)世界上每個(gè)國(guó)家的總?cè)丝跀?shù).
USEworldSELECTcountrycode,SUM(population)FROMcityGROUPBYcountrycode;
例子2： 統(tǒng)計(jì)中國(guó)各個(gè)省的總?cè)丝跀?shù)量(練習(xí))
SELECT district,SUM(Population) FROM city? WHERE countrycode='chn' GROUP BY district;
例子3：統(tǒng)計(jì)世界上每個(gè)國(guó)家的城市數(shù)量(練習(xí))
SELECT countrycode,COUNT(id)FROM city GROUP BY countrycode;
7.5 having
where|group|having
例子4：統(tǒng)計(jì)中國(guó)每個(gè)省的總?cè)丝跀?shù)，只打印總?cè)丝跀?shù)小于100
SELECTdistrict,SUM(Population)FROMcityWHEREcountrycode='chn'GROUPBYdistrictHAVINGSUM(Population)<1000000;
7.6 order by + limit
7.6.1 作用
實(shí)現(xiàn)先排序，by后添加條件列
7.6.2 應(yīng)用案例
查看中國(guó)所有的城市，并按人口數(shù)進(jìn)行排序(從大到小)
SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC;
統(tǒng)計(jì)中國(guó)各個(gè)省的總?cè)丝跀?shù)量，按照總?cè)丝趶拇蟮叫∨判?/p>
SELECTdistrictAS省,SUM(Population)AS總?cè)丝贔ROMcityWHEREcountrycode='chn'GROUPBYdistrictORDERBY總?cè)丝贒ESC;
統(tǒng)計(jì)中國(guó),每個(gè)省的總?cè)丝?找出總?cè)丝诖笥?00w的,并按總?cè)丝趶拇蟮叫∨判?只顯示前三名
SELECTdistrict,SUM(population)FROMcityWHEREcountrycode='CHN'GROUPBYdistrictHAVINGSUM(population)>5000000ORDERBYSUM(population)DESCLIMIT3;LIMITN,M--->跳過(guò)N,顯示一共M行LIMIT5,5SELECTdistrict,SUM(population)FROMcityWHEREcountrycode='CHN'GROUPBYdistrictHAVINGSUM(population)>5000000ORDERBYSUM(population)DESCLIMIT5,5;
7.7 distinct：去重復(fù)
SELECT countrycode FROM city;SELECTDISTINCT(countrycode)FROM city;
7.8 聯(lián)合查詢- union all
--中國(guó)或美國(guó)城市信息SELECT*FROMcityWHEREcountrycode IN('CHN','USA');SELECT*FROMcityWHEREcountrycode='CHN'UNIONALLSELECT*FROMcityWHEREcountrycode='USA'說(shuō)明:一般情況下,我們會(huì)將 IN 或者 OR 語(yǔ)句 改寫成UNIONALL,來(lái)提高性能UNION? ? 去重復(fù)UNIONALL 不去重復(fù)
7.9 join 多表連接查詢
7.9.0 案例準(zhǔn)備
按需求創(chuàng)建一下表結(jié)構(gòu):
useschoolstudent ：學(xué)生表sno：? ? 學(xué)號(hào)sname：學(xué)生姓名sage： 學(xué)生年齡ssex： 學(xué)生性別teacher ：教師表tno：? ? 教師編號(hào)tname：教師名字course ：課程表cno：? 課程編號(hào)cname：課程名字tno：? 教師編號(hào)score? ：成績(jī)表sno：? 學(xué)號(hào)cno：? 課程編號(hào)score：成績(jī)--項(xiàng)目構(gòu)建drop database school;CREATEDATABASEschoolCHARSETutf8;USEschoolCREATETABLEstudent(snoINTNOTNULLPRIMARYKEYAUTO_INCREMENTCOMMENT'學(xué)號(hào)',snameVARCHAR(20)NOTNULLCOMMENT'姓名',sageTINYINTUNSIGNEDNOTNULLCOMMENT'年齡',ssexENUM('f','m')NOTNULLDEFAULT'm'COMMENT'性別')ENGINE=INNODBCHARSET=utf8;CREATETABLEcourse(cnoINTNOTNULLPRIMARYKEYCOMMENT'課程編號(hào)',cnameVARCHAR(20)NOTNULLCOMMENT'課程名字',tnoINTNOTNULLCOMMENT'教師編號(hào)')ENGINE=INNODBCHARSETutf8;CREATETABLEsc(snoINTNOTNULLCOMMENT'學(xué)號(hào)',cnoINTNOTNULLCOMMENT'課程編號(hào)',scoreINTNOTNULLDEFAULT0COMMENT'成績(jī)')ENGINE=INNODBCHARSET=utf8;CREATETABLEteacher(tnoINTNOTNULLPRIMARYKEYCOMMENT'教師編號(hào)',tnameVARCHAR(20)NOTNULLCOMMENT'教師名字')ENGINE=INNODBCHARSETutf8;INSERTINTOstudent(sno,sname,sage,ssex)VALUES(1,'zhang3',18,'m');INSERTINTOstudent(sno,sname,sage,ssex)VALUES(2,'zhang4',18,'m'),(3,'li4',18,'m'),(4,'wang5',19,'f');INSERTINTOstudentVALUES(5,'zh4',18,'m'),(6,'zhao4',18,'m'),(7,'ma6',19,'f');INSERTINTOstudent(sname,sage,ssex)VALUES('oldboy',20,'m'),('oldgirl',20,'f'),('oldp',25,'m');INSERTINTOteacher(tno,tname)VALUES(101,'oldboy'),(102,'hesw'),(103,'oldguo');DESCcourse;INSERTINTOcourse(cno,cname,tno)VALUES(1001,'linux',101),(1002,'python',102),(1003,'mysql',103);DESCsc;INSERTINTOsc(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);SELECT*FROMstudent;SELECT*FROMteacher;SELECT*FROMcourse;SELECT*FROMsc;
7.9.1 語(yǔ)法
image
查詢張三的家庭住址
SELECT A.name,B.address FROMA JOIN? BON A.id=B.idWHERE A.name='zhangsan'
7.9.2 例子：
查詢一下世界上人口數(shù)量小于100人的城市名和國(guó)家名
SELECTb.name,a.name,a.populationFROMcityASaJOINcountryASbONb.code=a.countrycodeWHEREa.Population<100
查詢城市shenyang，城市人口，所在國(guó)家名（name）及國(guó)土面積（SurfaceArea）
SELECTa.name,a.population,b.name,b.SurfaceAreaFROMcityASaJOINcountryASbONa.countrycode=b.codeWHEREa.name='shenyang';
7.9.3 別名
列別名,表別名SELECTa.NameASan,b.nameASbn,b.SurfaceAreaASbs,a.PopulationASbpFROMcityASaJOINcountryASbONa.CountryCode=b.CodeWHEREa.name='shenyang';
7.9.4 多表SQL練習(xí)題
統(tǒng)計(jì)zhang3,學(xué)習(xí)了幾門課
SELECTst.sname,COUNT(sc.cno)FROMstudentASstJOINscONst.sno=sc.snoWHEREst.sname='zhang3'
查詢zhang3,學(xué)習(xí)的課程名稱有哪些?
SELECTst.sname,GROUP_CONCAT(co.cname)FROMstudentASstJOINscONst.sno=sc.snoJOINcourseAScoONsc.cno=co.cnoWHEREst.sname='zhang3'
查詢oldguo老師教的學(xué)生名.
SELECTte.tname,GROUP_CONCAT(st.sname)FROMstudentASstJOINscONst.sno=sc.snoJOINcourseAScoONsc.cno=co.cnoJOINteacherASteONco.tno=te.tnoWHEREte.tname='oldguo';
查詢oldguo所教課程的平均分?jǐn)?shù)
SELECTte.tname,AVG(sc.score)FROMteacherASteJOINcourseAScoONte.tno=co.tnoJOINscONco.cno=sc.cnoWHEREte.tname='oldguo'
4.1 每位老師所教課程的平均分,并按平均分排序
SELECT te.tname,AVG(sc.score)FROM teacher AS teJOIN course AS coON te.tno=co.tnoJOIN scON co.cno=sc.cnoGROUP BY te.tnameORDER BYAVG(sc.score)DESC;
查詢oldguo所教的不及格的學(xué)生姓名
SELECTte.tname,st.sname,sc.scoreFROMteacherASteJOINcourseAScoONte.tno=co.tnoJOINscONco.cno=sc.cnoJOINstudentASstONsc.sno=st.snoWHEREte.tname='oldguo'ANDsc.score<60;
5.1 查詢所有老師所教學(xué)生不及格的信息
SELECTte.tname,st.sname,sc.scoreFROMteacherASteJOINcourseAScoONte.tno=co.tnoJOINscONco.cno=sc.cnoJOINstudentASstONsc.sno=st.snoWHEREsc.score<60;
7.9.5 綜合練習(xí)
1. 查詢平均成績(jī)大于60分的同學(xué)的學(xué)號(hào)和平均成績(jī)；2. 查詢所有同學(xué)的學(xué)號(hào)、姓名、選課數(shù)、總成績(jī)；3. 查詢各科成績(jī)最高和最低的分：以如下形式顯示：課程ID，最高分，最低分 4. 統(tǒng)計(jì)各位老師,所教課程的及格率5. 查詢每門課程被選修的學(xué)生數(shù)6. 查詢出只選修了一門課程的全部學(xué)生的學(xué)號(hào)和姓名7. 查詢選修課程門數(shù)超過(guò)1門的學(xué)生信息8. 統(tǒng)計(jì)每門課程:優(yōu)秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的學(xué)生列表9. 查詢平均成績(jī)大于85的所有學(xué)生的學(xué)號(hào)、姓名和平均成績(jī)
8.information_schema.tables視圖
DESC information_schema.TABLESTABLE_SCHEMA---->庫(kù)名TABLE_NAME---->表名ENGINE---->引擎TABLE_ROWS---->表的行數(shù)AVG_ROW_LENGTH---->表中行的平均行（字節(jié)）INDEX_LENGTH---->索引的占用空間大?。ㄗ止?jié)）
查詢整個(gè)數(shù)據(jù)庫(kù)中所有庫(kù)和所對(duì)應(yīng)的表信息
SELECT table_schema,GROUP_CONCAT(table_name)FROM? information_schema.tablesGROUP BY table_schema;
統(tǒng)計(jì)所有庫(kù)下的表個(gè)數(shù)
SELECT table_schema,COUNT(table_name)FROM information_schema.TABLESGROUP BY table_schema
查詢所有innodb引擎的表及所在的庫(kù)
SELECT table_schema,table_name,ENGINE FROM information_schema.`TABLES`WHERE ENGINE='innodb';
統(tǒng)計(jì)world數(shù)據(jù)庫(kù)下每張表的磁盤空間占用
SELECTtable_name,CONCAT((TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB")ASsize_KBFROMinformation_schema.tablesWHERETABLE_SCHEMA='world';
統(tǒng)計(jì)所有數(shù)據(jù)庫(kù)的總的磁盤空間占用
SELECTTABLE_SCHEMA,CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB")AS Total_KBFROM information_schema.tablesGROUP BY table_schema;mysql-uroot-p123-e"SELECT TABLE_SCHEMA,CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024,' KB') AS Total_KB FROM information_schema.tables GROUP BY table_schema;"
生成整個(gè)數(shù)據(jù)庫(kù)下的所有表的單獨(dú)備份語(yǔ)句
模板語(yǔ)句：mysqldump-uroot-p123 world city>/tmp/world_city.sqlSELECTCONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql")FROMinformation_schema.tablesWHEREtable_schemaNOTIN('information_schema','performance_schema','sys')INTOOUTFILE'/tmp/bak.sh';CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql")
107張表，都需要執(zhí)行以下2條語(yǔ)句
ALTERTABLEworld.cityDISCARDTABLESPACE;ALTERTABLEworld.cityIMPORTTABLESPACE;SELECTCONCAT("alter table ",table_schema,".",table_name," discard tablespace")FROMinformation_schema.tablesWHEREtable_schema='world'INTOOUTFILE'/tmp/dis.sql';
9. show 命令
show? databases;#查看所有數(shù)據(jù)庫(kù)show tables;#查看當(dāng)前庫(kù)的所有表SHOWTABLESFROM? ? ? ? ? ? ? ? ? ? ? ? #查看某個(gè)指定庫(kù)下的表show create database world? ? ? ? ? ? ? ? #查看建庫(kù)語(yǔ)句show create table world.city? ? ? ? ? ? ? ? #查看建表語(yǔ)句show? grantsforroot@'localhost'#查看用戶的權(quán)限信息show? charset；? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? #查看字符集show collation? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? #查看校對(duì)規(guī)則show processlist;#查看數(shù)據(jù)庫(kù)連接情況show indexfrom#表的索引情況show status? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? #數(shù)據(jù)庫(kù)狀態(tài)查看SHOWSTATUSLIKE'%lock%';#模糊查詢數(shù)據(jù)庫(kù)某些狀態(tài)SHOWVARIABLES? ? ? ? ? ? ? ? ? ? ? ? ? ? #查看所有配置信息SHOWvariables LIKE'%lock%';#查看部分配置信息show engines? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? #查看支持的所有的存儲(chǔ)引擎show engine innodb status\G? ? ? ? ? ? ? #查看InnoDB引擎相關(guān)的狀態(tài)信息show binary logs? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? #列舉所有的二進(jìn)制日志show master status? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? #查看數(shù)據(jù)庫(kù)的日志位置信息show binlog evnetsin#查看二進(jìn)制日志事件show slave status \G? ? ? ? ? ? ? ? ? ? ? ? ? ? #查看從庫(kù)狀態(tài)SHOWRELAYLOGEVENTS? ? ? ? ? ? ? #查看從庫(kù)relaylog事件信息desc(show columsfromcity)#查看表的列定義信息http://dev.mysql.com/doc/refman/5.7/en/show.html
作者：wwwoldguocom
鏈接：http://www.itdecent.cn/p/08c4b78402ff
來(lái)源：簡(jiǎn)書
著作權(quán)歸作者所有。商業(yè)轉(zhuǎn)載請(qǐng)聯(lián)系作者獲得授權(quán)，非商業(yè)轉(zhuǎn)載請(qǐng)注明出處。
