1.SQL的介紹
SQL標(biāo)準(zhǔn)：SQL-92，SQL-99
SQL_mode
2.SQL的常用分類(lèi)
DDL
數(shù)據(jù)定義語(yǔ)言
DCL
數(shù)據(jù)控制語(yǔ)言
DML
數(shù)據(jù)操作語(yǔ)言
DQL
數(shù)據(jù)查詢(xún)語(yǔ)言
3.表的核心屬性介紹
3.1表屬性
3.1.1存儲(chǔ)引擎
engine=innodb（默認(rèn)）
3.1.2字符集及校對(duì)規(guī)則
字符集：
gbk
utf8（utf8mb3）：中文字符占3個(gè)字符
utf8mb4 ：中文字符占4個(gè)字符
區(qū)別：
emoji 字符支持
拼音yu
校對(duì)（排序）規(guī)則：
校對(duì)規(guī)則是跟著字符集走的
例如：utf8mb4
show collation;
utf8mb4_general_ci -->通用的校對(duì)，默認(rèn)值，不區(qū)分大小寫(xiě)
utf8mb4_bin -->區(qū)分大小寫(xiě)
3.2列屬性
3.2.1數(shù)據(jù)類(lèi)型
數(shù)字類(lèi)型：
---tinyint 128-127
---int -2^31~2^31-1
字符類(lèi)型：
---char（10）
---varchar（10）
----說(shuō)明：
以上數(shù)據(jù)類(lèi)型，必須要定義最長(zhǎng)字符長(zhǎng)度，用括號(hào)中的數(shù)字表示。
----char和varchar的區(qū)別：
char類(lèi)型：
是定長(zhǎng)類(lèi)型，存儲(chǔ)數(shù)據(jù)是，無(wú)關(guān)字符長(zhǎng)度，不管存儲(chǔ)多長(zhǎng)的數(shù)據(jù)，都立即分配10個(gè)字符唱的的存儲(chǔ)空間,無(wú)法占滿的部分，使用空來(lái)填充
varchar類(lèi)型：
可變長(zhǎng)度，按需分配存儲(chǔ)空間，每次都要計(jì)算字符串長(zhǎng)度
----如何選擇char和varchar類(lèi)型
變長(zhǎng)列，推薦varchar
定長(zhǎng)列，推薦char
---enum（'bj','sh','sz',......）
---枚舉類(lèi)型
時(shí)間類(lèi)型：
---datetime
---timetamp
二進(jìn)制型：
（一般不使用）圖片，視頻等存儲(chǔ)
3.2.2約束
not null 非空
unique key 唯一
primary key 主鍵（非空且唯一）
說(shuō)明：
1.一個(gè)表有且一個(gè)主鍵列，最好是一個(gè)無(wú)關(guān)列數(shù)字列，一般會(huì)在表中設(shè)置自增長(zhǎng)的id列
2.盡量每個(gè)列非空，如果無(wú)法保證，可以追加默認(rèn)值
3.手機(jī)號(hào)，身份證號(hào)，銀行卡號(hào)……種類(lèi)的列設(shè)定為唯一
3.2.3其他屬性
| 屬性 | 解釋 |
|---|---|
| unsigned | 無(wú)符號(hào)，一般是在int或tinyint后添加的附加屬性 |
| default | 設(shè)定more值 |
| auto_increment | 數(shù)字列自增長(zhǎng) |
| commnet | 注釋 |
4.DDL語(yǔ)句
4.1庫(kù)定義
4.1.1創(chuàng)建數(shù)據(jù)庫(kù)
CREATE DATABASE xiaopengyou CHARSET utf8mb4;
建庫(kù)的規(guī)范：
1.庫(kù)名是小寫(xiě)
2.庫(kù)名不能是數(shù)字開(kāi)頭
3.庫(kù)名要和業(yè)務(wù)有關(guān)
4.建庫(kù)是要添加字符集
4.1.2刪庫(kù)
DROP DATABASE xiaopengyou;
4.1.3查庫(kù)
show create database ku;查看創(chuàng)建庫(kù)語(yǔ)句
show databases;查看所有庫(kù)
4.1.4改庫(kù)
alter database ku charset utf8mb4;更改庫(kù)字符集
4.2表定義
4.2.1建表
例：
CREATE TABLE xs (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '學(xué)號(hào)',
NAME VARCHAR(64) NOT NULL COMMENT '學(xué)生姓名',
age TINYINT NOT NULL DEFAULT 0 COMMENT '年齡',
gender CHAR(1) NOT NULL DEFAULT 'n' COMMENT '性別',
shengfen ENUM('bj','sh','cq','tj') NOT NULL DEFAULT 'bj' COMMENT '省份',
TIME DATETIME NOT NULL DEFAULT NOW() COMMENT '入學(xué)時(shí)間'
)ENGINE=INNODB CHARSET=utf8mb4;
建表規(guī)范：
1.表名小寫(xiě)，無(wú)數(shù)字開(kāi)頭，與業(yè)務(wù)有關(guān)
2.必須要有主鍵，一般是一個(gè)自增長(zhǎng)的無(wú)關(guān)例
3.選擇合適的數(shù)據(jù)類(lèi)型，字符長(zhǎng)度要適中
4.每個(gè)列都非空，并設(shè)定默認(rèn)值
5.每個(gè)列必須要有注釋
6.必須設(shè)置存儲(chǔ)引擎和字符集
4.2.2改表
1）添加 列
DESC xs #查看表結(jié)構(gòu)
SHOW CREATE TABLE xs; # 查看建表語(yǔ)句
1.在xs表中添加列 shouji
ALTER TABLE xs ADD shouji CHAR(11) NOT NULL UNIQUE KEY COMMENT '手機(jī)'
2.在xs表中，在gender后面添加‘微信’列
ALTER TABLE xs ADD 微信 VARCHAR(30) NOT NULL UNIQUE KEY COMMENT '微信號(hào)' AFTER gender
3.在第一列位置添加QQ號(hào)列
ALTER TABLE xs ADD QQ VARCHAR(30) NOT NULL UNIQUE KEY COMMENT 'QQ號(hào)' FIRST
2）刪除 列
ALTER TABLE xs DROP shouji;（危險(xiǎn)操作?。?3）更改 列屬性
只改屬性,不改列名
ALTER TABLE xs MODIFY ssname VARCHAR(64) NOT NULL COMMENT '姓名';
列名和屬性都修改
ALTER TABLE xs CHANGE sname ssname VARCHAR(32) NOT NULL COMMENT '姓名';
4.2.2刪表
drop table xuesheng；表定義和數(shù)據(jù)全部刪除
truncate table xs；清空表的區(qū)，數(shù)據(jù)清空，表定義保留
4.2.3查表
show tables;
show create table xs;
desc xs;
5.DCL語(yǔ)句
grant 權(quán)限 on 范圍 to 用戶(hù) identified by ‘密碼’；
revoke 權(quán)限 on 范圍 from 用戶(hù)；
6.DML語(yǔ)句
用作表的數(shù)據(jù)行的增，刪，改，查
6.1 insert
6.1.1最規(guī)范的錄入方法
INSERT INTO xs(id,NAME,age,gender,shengfen,TIME) VALUES(1,'張三',18,'m','bj',NOW())
說(shuō)明：
INSERT INTO 插入到
xs(id,NAME,age,gender,shengfen,TIME) 對(duì)應(yīng)列名
VALUES 值
(1,'張三',18,'m','bj',NOW()) 詳細(xì)錄入信息
6.1.2簡(jiǎn)化寫(xiě)法
INSERT INTO xs VALUES(2,'李四',22,'m','tj',NOW())
6.1.3 針對(duì)性的錄入數(shù)據(jù)
INSERT INTO xs(NAME,age,sex,shengfen)
VALUES('wangwu',20,'f','tj');
6.1.4批量錄入
INSERT INTO
xs(NAME,age,gender,shengfen)
VALUES
('a',20,'f','tj'),
('b',20,'f','tj'),
('c',20,'f','tj'),
('d',20,'f','tj');
6.2update
UPDATE xs SET age=20 WHERE id=1;
說(shuō)明：
update xs 修改xs表
set age=20 更改的值
where id=1 過(guò)濾到id為1的那一列
注意！：update 語(yǔ)句必須要加where條件
6.3delete
DELETE FROM xs WHERE id=5;
注意?。篸elete語(yǔ)句必須要加where條件
面試題：以下語(yǔ)句的區(qū)別？
truncate ：
1)是DDL語(yǔ)句，清空整表的所有數(shù)據(jù)，按照區(qū)來(lái)刪除的，屬于物理刪除，性能高
2)表所占用的空間，會(huì)立即釋放delete ：
1）是DML語(yǔ)句，清空整表的所有數(shù)據(jù)，按照行來(lái)刪除，屬于邏輯刪除，性能低
2）表所占用的空間，不會(huì)立即釋放
6.4使用update 替代delete 實(shí)現(xiàn)偽刪除
原語(yǔ)句:
DELETE FROM xs WHERE id=6;
改寫(xiě)后:
UPDATE xs SET state=0 WHERE id=6;
業(yè)務(wù)語(yǔ)句進(jìn)行調(diào)整
select * from xs;
改為:
select * from xs where state=1;
7.DQL語(yǔ)句基礎(chǔ)應(yīng)用
7.1.select 語(yǔ)句應(yīng)用
select （單表）的執(zhí)行邏輯
| 執(zhí)行順序 | 語(yǔ)句 | 說(shuō)明 |
|---|---|---|
| 1 | select | 列1 ，列2 |
| 2 | from | 表 |
| 3 | where | 條件 |
| 4 | group by | 條件 |
| 5 | having | 條件 |
| 6 | order by | 條件 |
| 7 | limit | 條件 |
7.1.1select 單獨(dú)使用的情況（MySQL獨(dú)家）
SELECT @@參數(shù)名
例子：
SELECT @@datadir;
SELECT @@port
SELECT @@socket
SHOW VARIABLES LIKE '%trx%';模糊查找參數(shù)
7.1.2SELECT 函數(shù)();
SELECT NOW();
USE MYSQL;
SELECT DATABASE();
SELECT USER();
SELECT 16*16
SELECT CONCAT("HELLO WORD!!");
SELECT CONCAT(USER,"@",HOST) FROM MYSQL.USER;
7.2 FROM子句應(yīng)用
USE WORLD;
SELECT * FROM CITY;
SELECT NAME,COUNTRYCODE FROM CITY;
DESC CITY;
7.3WHERE 字句應(yīng)用
7.3.1等值查詢(xún)
查詢(xún)中國(guó)城市信息
SELECT * FROM city WHERE countrycode='CHN'
7.3.2 不等值查詢(xún)
-- 查詢(xún)?nèi)丝跀?shù)量少于100人城市.
SELECT * FROM city WHERE population<100;
-- 查詢(xún)ID小于10的城市信息
SELECT * FROM city WHERE id<10;
-- 查詢(xún)不是中國(guó)的城市信息(盡量不使用不等于,可能不走索引)
SELECT * FROM city WHERE countrycode!='CHN';
7.3.3模糊查詢(xún)
查詢(xún)國(guó)家代號(hào)為ch打頭的城市信息
SELECT * FROM city WHERE countrycode LIKE 'CH%';
SELECT * FROM city WHERE countrycode LIKE '%CH%';
7.3.4 AND 和 OR
與 或
7.3.5 where配合between AND 的使用
---查詢(xún)?nèi)丝跀?shù)在100w-200w區(qū)間的城市信息（包含頭尾）
SELECT * FROM city WHERE population BETWEEN 1000000 AND 2000000
7.3.6WHERE 配合 IN 使用
---查看山東或河北的城市信息
SELECT * FROM city
WHERE district IN ('shandong','hebei')
7.4 GROUP BY 子句
7.4.1什么是分組
按照某個(gè)列進(jìn)行分組
7.4.2常用的聚合函數(shù)
| 函數(shù) | 含義 |
|---|---|
| COUNT() | 計(jì)數(shù) |
| MAX() | 最大值 |
| MIN() | 最小值 |
| AVG() | 平均值 |
| SUM() | 求和 |
| GROUP_CONCAT() | 列轉(zhuǎn)行 |
---例子
統(tǒng)計(jì)每個(gè)國(guó)家的城市個(gè)數(shù)
SELECT countrycode,COUNT(id) FROM city
GROUP BY countrycode;
統(tǒng)計(jì)每個(gè)國(guó)家的總?cè)丝跀?shù)
SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode;
統(tǒng)計(jì)中國(guó)每個(gè)省的城市個(gè)數(shù)及總?cè)丝跀?shù)
SELECT district,COUNT(NAME),SUM(population)
FROM city WHERE countrycode='CHN'
GROUP BY district
統(tǒng)計(jì)各個(gè)國(guó)家的城市名列表
SELECT countrycode,GROUP_CONCAT(NAME) FROM city GROUP BY countrycode;
7.5 having子句的使用(后過(guò)濾條件)
--統(tǒng)計(jì)中國(guó)每個(gè)省的城市個(gè)數(shù)及總?cè)丝跀?shù)
--且只顯示人口大于800w的城市
SELECT district,COUNT(NAME),SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>8000000
7.6ORDER BY 子句
--以上例子 ，將人口數(shù)進(jìn)行排列輸出
SELECT district,COUNT(NAME),SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>8000000
ORDER BY SUM(population) DESC;
末尾desc 從大到小排序
查詢(xún)中國(guó)所有城市信息，并以人口數(shù)就降序輸出
SELECT NAME,population FROM city
WHERE countrycode='CHN'
ORDER BY population;
7.7. limit 應(yīng)用
-- 查詢(xún)中國(guó)所有城市信息,并以人口數(shù)降序輸出,只顯示前五名
SELECT * FROM city WHERE countrycode='CHN'
ORDER BY population DESC
LIMIT 5 ;
SELECT * FROM city WHERE countrycode='CHN'
ORDER BY population DESC
LIMIT 10 OFFSET 0;
SELECT * FROM city WHERE countrycode='CHN'
ORDER BY population DESC
LIMIT 5 OFFSET 5
SELECT * FROM city WHERE countrycode='CHN'
ORDER BY population DESC
LIMIT 3,5;
-- 跳過(guò)前N行,顯示M行(N和M代表的是數(shù)字)
LIMIT M offet N
LIMIT N,M
8.多表連接查詢(xún)
8.1作用
業(yè)務(wù)需要的數(shù)據(jù)來(lái)自多張表時(shí)
8.2多表連接查詢(xún)基本語(yǔ)法
內(nèi)連接 *****
外連接*
全連接#
笛卡爾#
8.3多表連接的基本語(yǔ)法（內(nèi)連接）
傳統(tǒng)連接**
自連接**
join uing**
join on ****
8.4join on 的語(yǔ)法
多表連接的套路：
1.根據(jù)需求找關(guān)聯(lián)列
2.找到表與表的關(guān)聯(lián)列
3.列名調(diào)用時(shí)，需要添加表前綴 ，例如a.id b.name
8.5多表連接案例
8.5.1查詢(xún)?nèi)丝跀?shù)量少于100人的城市所在：國(guó)家明，國(guó)土面積，城市名，人口數(shù)
SELECT
country.name ,country.SurfaceArea,city.name,city.Population
FROM city
JOIN country
ON city.CountryCode=country.Code
WHERE city.Population<100;
8.5.2張三學(xué)習(xí)了哪些課程
SELECT student.sname,COUNT(sc.cno)
FROM student
JOIN sc
ON student.sno=sc.sno
WHERE student.sname='zhang3';
8.5.3張三學(xué)習(xí)了哪些課程名稱(chēng)
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';
group by student.sname
8.5.4oldguo老師教了學(xué)生的個(gè)數(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';
8.5.5 每位老師所教課程的平均分,并按平均分排序
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.tname
ORDER BY AVG(sc.score)DESC;
8.5.6 查詢(xún)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 sc.score<'60' AND teacher.tname='oldguo';
8.5.7 查詢(xún)所有老師所教學(xué)生不及格的信息
SELECT teacher.tname,student.sno,student.`sage`,student.`sname`,student.`ssex`,sc.`score`
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.score<60;
GROUP BY teacher.tname
8.6別名的使用
8.6.1表別名
SELECT a.tname,GROUP_CONCAT(student.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' AND a.tname='oldguo';
說(shuō)明:
表別名一般是在 FROM的表的別名,或者join后的表的別名
在 where, group by ,select后的列,having,order by
8.6.2列別名
例子:
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 平均分 ;
OVER ! ! !
說(shuō)明:
列別名一般是在select后的列,定義的別名
作用:
- 結(jié)果集顯示會(huì)以別名形式展示
- 在 having 和order by中可以調(diào)用列別名
8.7外連接簡(jiǎn)介
left join
左表中所有的信息及匹配到右表的內(nèi)容進(jìn)行顯示
right join
結(jié)論(優(yōu)化小提示):
- 多表連接中,小表驅(qū)動(dòng)大表
- 通過(guò)left join 強(qiáng)制選定驅(qū)動(dòng)表
9.元數(shù)據(jù)獲取
”基表“ 》》》》
數(shù)據(jù)字典信息（列結(jié)構(gòu)frm），
系統(tǒng)狀態(tài)，
對(duì)象狀態(tài)
9.1show 語(yǔ)句 ）
| 語(yǔ)句 | 含義 |
|---|---|
| show databases; | #查看所有數(shù)據(jù)庫(kù) |
| show tables; | #查看當(dāng)前庫(kù)的所有表 |
| SHOW TABLES FROM | #查看某個(gè)指定庫(kù)下的表 |
| show create database world | #查看建庫(kù)語(yǔ)句 |
| show create table world.city | #查看建表語(yǔ)句 |
| show grants for root@'localhost' | #查看用戶(hù)的權(quán)限信息 |
| show charset； | #查看字符集 |
| show collation | #查看校對(duì)規(guī)則 |
| show processlist; | #查看數(shù)據(jù)庫(kù)連接情況 |
| show index from | #表的索引情況 |
| show status | #數(shù)據(jù)庫(kù)狀態(tài)查看 |
| SHOW STATUS LIKE '%lock%'; | #模糊查詢(xún)數(shù)據(jù)庫(kù)某些狀態(tài) |
| SHOW VARIABLES | #查看所有配置信息 |
| SHOW variables 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 evnets in | #查看二進(jìn)制日志事件 |
| show slave status \G | #查看從庫(kù)狀態(tài) |
| SHOW RELAYLOG EVENTS | #查看從庫(kù)relaylog事件信息 |
| desc (show colums from city) | #查看表的列定義信息 |
| help show | #查看幫助 |
9.2 information_schema 虛擬庫(kù)
information_schema ---> views 視圖
將查詢(xún)?cè)獢?shù)據(jù)的語(yǔ)句封裝成視圖，當(dāng)要獲取此數(shù)據(jù)時(shí)，直接調(diào)用此視圖。
9.2.1 詳解TABLS 作用和結(jié)構(gòu)
作用：存儲(chǔ)整個(gè)數(shù)據(jù)庫(kù)中，所有表的元數(shù)據(jù)的查詢(xún)方式
desc tables; >>>>>>>>>
| 視圖 | 含義 |
|---|---|
| TABLE_SCHEMA | 表所在的庫(kù) |
| TABLE_NAME | 表名 |
| ENHING | 存儲(chǔ)引擎 |
| TABLE_ROWS | 表的行數(shù) |
| AVG_ROW_LENGTH | 平均行長(zhǎng)度 |
| INDEX_LENGTH | 索引長(zhǎng)度 |
例：對(duì)MySQL的數(shù)據(jù)庫(kù)進(jìn)行分庫(kù)分表備份
#命令行語(yǔ)句
-- mysqldump -uroot -p123 world city >/backup/world_city.sql
#SQL語(yǔ)句，按表備份所有庫(kù)
SELECT CONCAT("mysqldump -uroot -p123456 ",table_schema ," ",table_name ," >/backup/",table_schema,
"_",table_name,".sql")
FROM information_schema.tables INTO OUTFILE '/tmp/bak1.sql';
