深圳家居網(wǎng)站建設(shè)公司青島app開發(fā)公司
1、Mysql概述
1.1 數(shù)據(jù)庫相關(guān)概念
三個概念:數(shù)據(jù)庫、數(shù)據(jù)庫管理系統(tǒng)、SQL
名稱 | 全稱 | 簡稱 |
---|---|---|
數(shù)據(jù)庫 | 存儲數(shù)據(jù)的倉庫,數(shù)據(jù)是有組織的進(jìn)行存儲 | DataBase(DB) |
數(shù)據(jù)庫管理系統(tǒng) | 操縱和管理數(shù)據(jù)庫的大型軟件 | DataBase Mangement System(DBMS) |
SQL | 操縱關(guān)系型數(shù)據(jù)庫的編程語言,定義了一套操作關(guān)系型數(shù)據(jù)庫統(tǒng)一標(biāo)準(zhǔn) | Structured Query Language(SQL) |
1.2 Mysql數(shù)據(jù)庫
1.2.1 下載
下載鏈接:MySQL :: Download MySQL Installer (Archived Versions)
1.2.2 啟動停止
MySQL安裝完成之后,在系統(tǒng)啟動時,會自動啟動MySQL服務(wù)
# 啟動服務(wù)
net start mysql80# 停止服務(wù)
net stop mysql80
1.2.3客戶端連接
- 方式一: 使用Mysql提供的客戶端命令行工具
2)方式二:使用系統(tǒng)自帶的命令行工具執(zhí)行指令
mysql [-h 127.0.0.1] [-p 3306] -u -root -P參數(shù):-h : Mysql服務(wù)所在的主機(jī)IP-P : Mysql服務(wù)器端口號,默認(rèn)3306-u : Mysql數(shù)據(jù)庫用戶名-p :Mysql數(shù)據(jù)庫用戶名對應(yīng)的密碼
1.2.4 數(shù)據(jù)模型
- 關(guān)系模型數(shù)據(jù)庫 (RDBMS)
概念:
建立在關(guān)系模型基礎(chǔ)上,由多張二維表組成的數(shù)據(jù)庫
特點(diǎn):
A、使用表存儲數(shù)據(jù),格式統(tǒng)一,便于維護(hù)
B、使用SQL語言格式統(tǒng)一,使用方便
2)數(shù)據(jù)模型
MySQL是關(guān)系型數(shù)據(jù)庫,是基于二維表進(jìn)行數(shù)據(jù)存儲的,具體結(jié)構(gòu)如下
- 我們可以通過MySQL客戶端連接數(shù)據(jù)庫管理系統(tǒng)DBMS,然后通過DBMS操作數(shù)據(jù)庫。
- 可以使用SQL語句,通過數(shù)據(jù)庫管理系統(tǒng)操作數(shù)據(jù)庫,以及操作數(shù)據(jù)庫中的表結(jié)構(gòu)及數(shù)據(jù)。
- 一個數(shù)據(jù)庫服務(wù)器中可以創(chuàng)建多個數(shù)據(jù)庫,一個數(shù)據(jù)庫中也可以包含多張表,而一張表中又可以包 含多行記錄。
2、SQL
全稱 Structured Query Language,結(jié)構(gòu)化查詢語言。操作關(guān)系型數(shù)據(jù)庫的編程語言,定義了 一套操作關(guān)系型數(shù)據(jù)庫統(tǒng)一標(biāo)準(zhǔn) 。
2.1 SQL分類
分類 | 全稱 | 說明 |
---|---|---|
DDL | Data definition Language | 數(shù)據(jù)定義語言,用來定義數(shù)據(jù)庫對象(數(shù)據(jù)庫,表,字段) |
DML | Data Manipulation Language | 數(shù)據(jù)操作語言,用來對數(shù)據(jù)庫表中的數(shù)據(jù)據(jù)進(jìn)行增刪改 |
DQL | Data Query Language | 數(shù)據(jù)查詢語言,用來查詢數(shù)據(jù)庫表中的記錄 |
DCL | Data Control Language | 數(shù)據(jù)控制語言,用來創(chuàng)建數(shù)據(jù)庫用戶、控制數(shù)據(jù)庫的訪問權(quán)限 |
2.2 DDL
Data Definition Language 數(shù)據(jù)定義語言,用來定義數(shù)據(jù)庫對象(數(shù)據(jù)庫,表,字段)
2.2.1、數(shù)據(jù)庫操作
1)查詢所有數(shù)據(jù)庫
show databases;
- 查詢當(dāng)前數(shù)據(jù)庫
select database();
- 創(chuàng)建數(shù)據(jù)庫
create database [if not exists] 數(shù)據(jù)庫名 [default charset 字符集] [ collate 排序規(guī)則];
案例:
A、創(chuàng)建一個mysql_learn數(shù)據(jù)庫,使用默認(rèn)字符集。
create database mysql_learn;
B、創(chuàng)建一個mysql_learn數(shù)據(jù)庫,使用指定字符集
create database mysql_learn default charset utf8mb4;
- 刪除數(shù)據(jù)庫
drop database [if exists] 數(shù)據(jù)庫名;
5)切換數(shù)據(jù)庫
use 數(shù)據(jù)庫名;
2.2.2 表操作
2.2.2.1表操作-查詢創(chuàng)建
- 查詢當(dāng)前數(shù)據(jù)庫所有表
show tables;
切換到sys系統(tǒng)數(shù)據(jù)庫,并查看系統(tǒng)數(shù)據(jù)庫的所有表結(jié)構(gòu)
use sys;
show tables;
- 查看指定表結(jié)構(gòu)
desc 表名;
- 查詢指定表的建表語句
show create table 表名;
4)創(chuàng)建表結(jié)構(gòu)
create table 表名(字段1 字段1類型 [comment 字段1注釋],字段2 字段2類型 [comment 字段2注釋],……) [comment 表注釋];
創(chuàng)建tb_user
create table tb_user(id int comment '編號',name varchar(50) comment '姓名',age int comment '年齡',gender varchar(1) comment '性別'
) comment '用戶表';
2.2.2.2 表操作-數(shù)據(jù)類型
1)數(shù)值類型
類型 | 大小 | 有符號(SIGNED)范圍 | 無符(UNSIGNED)范圍 | 描述 |
---|---|---|---|---|
tinyint | 1byte | (-128,127) | (0, 255) | 小整數(shù)值 |
smallint | 2bytes | (-32768, 32767) | (0, 65535) | 大整數(shù)值 |
mediumint | 3bytes | (-8388608, 8388607) | (0, 16777215) | 大整數(shù)值 |
int/integer | 4bytes | (-2147483648, 2147483647) | (0,4294967295) | 大整數(shù)值 |
bigint | 8bytes | (-263,263-1) | (0,2^64-1) | 極大整數(shù)值 |
float | 4bytes | (-3.402823466 E+38, 3.402823466351 E+38) | 0 和 (1.175494351 E38,3.402823466 E+38) | 單精度浮點(diǎn)數(shù)值 |
double | 8bytes | (-1.7976931348623157 E+308, 1.7976931348623157 E+308) | 0 和 (2.2250738585072014 E-308, 1.7976931348623157 E+308) | 雙精度浮點(diǎn)數(shù)值 |
decimal | 依賴于M(精度)和D(標(biāo)度) 的值 | 依賴于M(精度)和D(標(biāo)度)的 值 | 小數(shù)值(精確定點(diǎn)數(shù)) |
1)年齡字段 – 不可能出現(xiàn)負(fù)數(shù),而且人的年齡不會太大
age tinyint unsigned
- 分?jǐn)?shù) – 總分100分,最多出現(xiàn)4位小數(shù)
score double(4,1)
- 字符類型
char與varchar都可以描述字符串,char是定長度字符串,指定長度多長,就占用多少個字符,和字段值的長度無關(guān),而varchar是變長字符串,指定的長度為最大占用長度。相對來說,char的性能會更高
1)用戶名 username -----> 長度不定,最長不會超過50
username varchar(50)
2)性別 gender ---->存儲值,不是男就是女
gender char(1)
3)手機(jī)號phone----->固定長度為1
phone char(11)
3)日期時間類型
- 生日字段 birthday
birthday date
2)創(chuàng)建時間 createtime
createtime datetime
2.2.2.3 表操作-案例
設(shè)計(jì)一張員工信息表,要求如下:
1.編號(純數(shù)字)
2.員工工號(字符串類型,長度不超過10位)
3.員工姓名(字符串類型,長度不超過10位)
4.性別(男/女,存儲一個漢字)
5.年齡(正常人年齡,不可能存儲負(fù)數(shù))
6.身份證號(二代身份證均為18位,身份證中有x這樣的字符)
7.入職時間(取值年月日即可)
create table tmp (id int comment'編號',workno varchar(10) comment'員工工號',name varchar(10) comment'姓名',gender char(1) comment'性別',age tinyint unsigned comment'年齡',idcard char(18) comment'身份證號',entrydate date comment'入職時間'
) comment '員工表';
2.2.2.4 表操作-修改
1)添加字段
alter table 表名 add 字段名 類型(長度) [comment 注釋] [約束];
案例:
為emp表增加一個新的字段“昵稱”為nickname,類型為varchar(20)
alter table emp add nickname varchar(20) comment '昵稱';
2)修改數(shù)據(jù)類型
alter table 表名 modify 字段名 新數(shù)據(jù)類型(長度);
alter table tmp modify nickname varchar(30);
- 修改字段名和字段類型
alter table 表名 change 舊字段名 新字段名 類型(長度) [comment 注釋] [約束];
案例:
將emp表的nickname字段改成username,類型為varchar(30)
alter table emp change nickname username varchar(30) comment '昵稱';
4)刪除字段
alter table 表名 drop 字段名
案例:
將emp表的字段username刪除
alter table emp drop username;
- 修改表名
alter table 表名 rename to 新表名;
rename table 舊表名 to 新表名;
案例:
將emp表的表名修改成employee
alter table emp rename to employee;
2.2.2.5 表操作-刪除
1)刪除表
drop table [if exists] 表名;
案例:
如果存在tb_user,則刪除tb_user;
drop table if exists tb_user;
- 刪除指定表,并重新創(chuàng)建表
truncate table 表名;
注意: 在刪除表的時候,表中的全部數(shù)據(jù)也都會被刪除。
2.3 DML
DML英文全稱是Data Manipulation Language(數(shù)據(jù)操作語言),用來對數(shù)據(jù)庫中表的數(shù)據(jù)記錄進(jìn) 行增、刪、改操作。
2.3.1添加數(shù)據(jù)
1)給指定的字段添加數(shù)據(jù)
insert into 表名 (字段名1, 字段名2,…)values (值1, 值2,…)
案例:給employee表所有字段添加數(shù)據(jù)
insert into employee(id, workno, name, gender, age, idcard, entrydate)
values
(1,'1','leilei','男',10,'123456789012345678','2000-01-01')
- 給全部字段添加數(shù)據(jù)
insert into 表名 values (值1, 值2, ……)
案例:插入數(shù)據(jù)到employee表,具體的SQL如下:
insert into employee values(2, '2','張無忌', '男', 18, '123456789012345678','2005-01-01');
3)批量添加數(shù)據(jù)
insert into 表名(字段名1, 字段名2,……) values
(值1,值2, ……),
……
(值1, 值2, ……);
insert into 表名 values
(值1,值2, ……),
……
(值1, 值2, ……);
案例:批量插入數(shù)據(jù)到employee表,具體的SQL如下:
insert into employee values
(3, '3','韋一笑', '男', 38, '123456789012345670','2005-01-01'),
(4, '4','趙敏', '女', 18, '123456789012345670','2005-01-01');
注意事項(xiàng)
- 插入數(shù)據(jù)時,指定的字段順序需要與值的順序是一一對應(yīng)的。
- 字符串和日期型數(shù)據(jù)應(yīng)該包含在引號中
- 插入的數(shù)據(jù)大小,應(yīng)該在字段規(guī)定的范圍內(nèi)
2.3.2 修改數(shù)據(jù)
修改數(shù)據(jù)的具體語法為:
update 表名 set 字段名1 = 值1, 字段名2 = 值2,…… [where 條件]
案例:
A、修改id為1的數(shù)據(jù),將name修改為leijiong
update employee set name = 'leijiong' where id = 1;
B、修改id為1的數(shù)據(jù),將name修改為小昭,gender修改成女
update employee set name = '小昭', gender = '女' where id = 1;
C、 將所有的員工入職日期修改為 2008-01-01
update employee set entrydate = '2008-01-01';
注意事項(xiàng):
修改語句的條件可以有,也可以沒有,如果沒有條件,則會修改整張表的所有數(shù)據(jù)。
2.3.3 刪除數(shù)據(jù)
刪除數(shù)據(jù)的具體語法:
delete from 表名 [where 條件];
案例
A、刪除gender為女的員工
delete from employee where gender = '女';
B、刪除所有員工
delete from employee;
注意事項(xiàng):
- DELETE 語句的條件可以有,也可以沒有,如果沒有條件,則會刪除整張表的所有數(shù)據(jù)。
- DELETE 語句不能刪除某一個字段的值(可以使用UPDATE,將該字段值置為NULL即可)。
- 當(dāng)進(jìn)行刪除全部數(shù)據(jù)操作時,datagrip會提示我們,詢問是否確認(rèn)刪除,我們直接點(diǎn)擊Execute即可。
2.4 DQL
DQL英文全稱是Data Query Language(數(shù)據(jù)查詢語言),數(shù)據(jù)查詢語言,用來查詢數(shù)據(jù)庫中表的記錄。
查詢關(guān)鍵字: SELECT
查詢數(shù)據(jù)準(zhǔn)備:
drop table if exists employee;
create table emp(
id int comment '編號',
workno varchar(10) comment '工號',
name varchar(10) comment '姓名',
gender char(1) comment '性別',
age tinyint unsigned comment '年齡',
idcard char(18) comment '身份證號',
workaddress varchar(50) comment '工作地址',
entrydate date comment '入職時間'
)comment '員工表';
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (1, '00001', '柳巖666', '女', 20, '123456789012345678', '北京', '2000-01-
01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (2, '00002', '張無忌', '男', 18, '123456789012345670', '北京', '2005-09-
01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (3, '00003', '韋一笑', '男', 38, '123456789712345670', '上海', '2005-08-
01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (4, '00004', '趙敏', '女', 18, '123456757123845670', '北京', '2009-12-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (5, '00005', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (6, '00006', '楊逍', '男', 28, '12345678931234567X', '北京', '2006-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (7, '00007', '范瑤', '男', 40, '123456789212345670', '北京', '2005-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (8, '00008', '黛綺絲', '女', 38, '123456157123645670', '天津', '2015-05-
01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (9, '00009', '范涼涼', '女', 45, '123156789012345678', '北京', '2010-04-
01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (10, '00010', '陳友諒', '男', 53, '123456789012345670', '上海', '2011-01-
01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (11, '00011', '張士誠', '男', 55, '123567897123465670', '江蘇', '2015-05-
01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (12, '00012', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-
01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (13, '00013', '張三豐', '男', 88, '123656789012345678', '江蘇', '2020-11-
01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (14, '00014', '滅絕', '女', 65, '123456719012345670', '西安', '2019-05-
01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (15, '00015', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-
01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (16, '00016', '周芷若', '女', 18, null, '北京', '2012-06-01');
2.4.1 基礎(chǔ)語法
DQL查詢語句,語法結(jié)構(gòu)如下:
select 字段列表
from表名列表
where條件列表
group by分組字段列表
having分組后條件列表
order by排序字段列表
limit分頁參數(shù)
2.4.2 基礎(chǔ)查詢
1) 查詢多個字段
select 字段1,字段2,字段3 … from 表名;
select * from 表名;
注意:*號代表查詢所有的字段,在實(shí)際開發(fā)中盡量少用(不直觀,影響效率)
2)字段設(shè)置別名
select 字段1 [as 別名1], 字段2 [as 別名2] … from 表名;
select 字段1 [別名], 字段2 [別名] … from 表名;
- 去除重復(fù)記錄
select distinct 字段列表 from 表名;
案例:
A、查詢指定字段 name, workno, age 返回
select name, workno, age from emp;
B、查詢返回所有字段
select id, workno, name, gender, age, idcard, workaddress, entrydate from emp;
select * from emp;
C、查詢所有員工的工作地址,起別名
select workaddress as '工作地址' from emp;
-- as 可以省略
select workaddress '工作地址' from emp;
D、查詢所有員工的上班地址有那些(不要重復(fù))
select distinct workaddress '工作地址' from emp;
2.4.3 條件查詢
1)語法
select 字段列表 from 表名 where 條件列表;
2)條件
常用的比較運(yùn)算符:
常用的邏輯運(yùn)算符
案例:
A、查詢年齡等于88的員工
select * from emp where age = 88;
B、查詢年齡小于20的員工
select * from emp where age < 20;
C、查詢年齡小于等于20的員工信息
select * from emp where age <= 20;
D、查詢沒有身份證號的員工信息
select * from emp where idcard is null;
E、查詢有身份證號的員工信息
select * from emp idcard is not null;
F、查詢年齡不等于88的員工
select * from emp where age != 88;
select * from emp where age <> 88;
G、查詢年齡在15(包含)到20(包含)之間的員工
select * from emp where age >= 15 && age <= 20;
select * from emp where age >= 15 and age <= 20;
select * from emp where age between 15 and 20;
H、查詢性別為 女 且年齡小于25歲的員工信息
select * from emp where gender = '女' and age < 25;
I、查詢年齡等于18 或者 20 或者 40 的員工信息
select * from emp where age = 18 or age = 20 or age = 40;
select * from emp where age in (18, 10, 40);
J、查詢姓名為兩個字的員工
select * from emp where name like '__';
K、查詢身份證最后一位是X的員工信息
select * from emp where idcard like '%X';
select * from emp where idcard like '_________________X';
2.4.4 聚合查詢
- 介紹
將一列數(shù)據(jù)作為一個整體,進(jìn)行縱向計(jì)算 。
- 常見的聚合函數(shù)
3)語法
select 聚合函數(shù)(字段列表) from 表名;
注意:NULL值是不參與所有聚合函數(shù)運(yùn)算的
案例:
A、統(tǒng)計(jì)該企業(yè)員工的數(shù)量
select count(*) from emp; -- 統(tǒng)計(jì)的是總記錄
select count(idcard) from emp; -- 統(tǒng)計(jì)的是idcard字段不為null的情況
對于count聚合函數(shù),統(tǒng)計(jì)符合條件的總記錄數(shù),還可以通過count(數(shù)字/字符串)的形式進(jìn)行統(tǒng)計(jì)查詢,比如:
select count(1) from emp;
B、統(tǒng)計(jì)該企業(yè)的平均年齡
select avg(age) from emp;
C、統(tǒng)計(jì)該企業(yè)員工的最大年齡
select max(age) from emp;
D、統(tǒng)計(jì)該企業(yè)員工的最小年齡
select min(age) from emp;
E、統(tǒng)計(jì)西安地區(qū)員工年齡之和
select sum(age) from emp where workaddress = '西安';
2.4.5 分組查詢
- 語法
select 字段列表 from 表名 [where 條件] group by 分組字段名 [having 分組 后過濾條件];
- where與having區(qū)別
- 執(zhí)行時機(jī)不同:where是分組之前進(jìn)行過濾,不滿足where,不參與分組;而having是分組之后對結(jié)果進(jìn)行過濾。
- 判斷條件不同:where不能對聚合函數(shù)進(jìn)行判斷,而having可以。
注意事項(xiàng):
- 分組之后,查詢的字段一般為聚合函數(shù)和分組字段,查詢其他字段無任何意義。
- 執(zhí)行順序: where > 聚合函數(shù) > having 。
- 支持多字段分組, 具體語法為 : group by columnA,columnB
案例:
A、根據(jù)性別分組,統(tǒng)計(jì)男性員工 和 女性員工的數(shù)量
select gender, count(*) from emp group by gender;
B、根據(jù)性別分組,統(tǒng)計(jì)男性員工 和 女性員工的平均年齡
select gender, avg(age) from emp group by gender;
C、查詢年齡小于45的員工,并根據(jù)工作地址分組,獲取員工數(shù)量大于等于3的工作地址
select workaddress, count(*) address_count
from emp
where age < 45
group by workaddress
having address_count >= 3;
D、統(tǒng)計(jì)各個工作地址上班的男性和女性的工作數(shù)量
select workaddress, gender, count(*) '數(shù)量'
from emp
group by workaddress,gender;
2.4.6 排序查詢
- 語法
select 字段列表 from 表名 group by 字段1 排序方式1, 字段2 排序方式2;
- 排序方式
ASC : 升序(默認(rèn)值)
DESC : 降序
注意事項(xiàng):
如果是升序, 可以不指定排序方式ASC ;
如果是多字段排序,當(dāng)?shù)谝粋€字段值相同時,才會根據(jù)第二個字段進(jìn)行排序 ;
案例:
A、根據(jù)年齡對公司員工進(jìn)行升序排序
select * from emp order by age asc;
select * from emp order by age;
B、 根據(jù)入職時間, 對員工進(jìn)行降序排序
select * from emp order by entrydate desc;
C、 根據(jù)年齡對公司的員工進(jìn)行升序排序 , 年齡相同 , 再按照入職時間進(jìn)行降序排序
select * from emp order by age asc, entrydate desc;
2.4.7 分頁查詢
- 語法
select 字段列表 from 表名 limit 起始索引,查詢記錄數(shù);
起始索引從0開始,起始索引 = (查詢頁碼 - 1) * 每頁顯示記錄數(shù)。
分頁查詢是數(shù)據(jù)庫的方言,不同的數(shù)據(jù)庫有不同的實(shí)現(xiàn),MySQL中是LIMIT。
如果查詢的是第一頁數(shù)據(jù),起始索引可以省略,直接簡寫為 limit 10.
2)案例
A、查詢第一頁員工數(shù)據(jù),每頁展示10條記錄
select * from emp limit 0, 10;
select * from emp from 10;
B、查詢第二頁數(shù)據(jù),每頁展示10條
select * from emp limit 10, 10;
2.4.8 案例
1). 查詢年齡為20,21,22,23歲的員工信息。
select * from emp where age in (20, 21, 22, 23);
2). 查詢性別為 男 ,并且年齡在 20-40 歲(含)以內(nèi)的姓名為三個字的員工。
select * from emp where gender = '男' and (age between 20 and 40) and name like '___' ;
3). 統(tǒng)計(jì)員工表中, 年齡小于60歲的 , 男性員工和女性員工的人數(shù)。
select gender, count(*) from emp where age < 60 group by gender;
4). 查詢所有年齡小于等于35歲員工的姓名和年齡,并對查詢結(jié)果按年齡升序排序,如果年齡相同按 入職時間降序排序。
select name, age from emp where age <= 35 order by age asc, entrydate desc;
5). 查詢性別為男,且年齡在20-40 歲(含)以內(nèi)的前5個員工信息,對查詢的結(jié)果按年齡升序排序, 年齡相同按入職時間升序排序。
select * from
emp
where gender = '男'
and
(age between 20 and 40)
order by
age asc, entrydate asc
limit 5;
2.6.9 執(zhí)行順序
2.5 DCL
DCL英文全稱是Data Control Language(數(shù)據(jù)控制語言),用來管理數(shù)據(jù)庫用戶、控制數(shù)據(jù)庫的訪問權(quán)限。
2.5.1 管理用戶
- . 查詢用戶
select * from mysql.user;
- . 創(chuàng)建用戶
create user '用戶名'@'主機(jī)名' identified by '密碼';
3). 修改用戶密碼
alter user '用戶名'@'主機(jī)名' idrntified with mysql_native_password by '新密碼';
4). 刪除用戶
drop user '用戶名'@'主機(jī)名';
注意事項(xiàng):
在MySQL中需要通過用戶名@主機(jī)名的方式,來唯一標(biāo)識一個用戶。
主機(jī)名可以使用 % 通配。
這類SQL開發(fā)人員操作的比較少,主要是DBA( Database Administrator 數(shù)據(jù)庫 管理員)使用。
案例:
A. 創(chuàng)建用戶lei, 只能夠在當(dāng)前主機(jī)localhost訪問, 密碼123456;
create user 'lei'@'localhost' identified by '123456';
B. 創(chuàng)建用戶jiong, 可以在任意主機(jī)訪問該數(shù)據(jù)庫, 密碼123456;
create user 'jiong'@'%' identified by '123456';
C. 修改用戶jiong的訪問密碼為1234;
alter user 'jiong'@'%' identified with mysql_native_password by '1234';
D. 刪除 lei@localhost 用戶
drop user 'lei'@'localhost';
2.5.2 權(quán)限控制
- 查詢權(quán)限
show crants for '用戶名'@'主機(jī)名';
- 授予權(quán)限
crant 權(quán)限列表 on 數(shù)據(jù)庫名.表名 to '用戶名'@'主機(jī)名';
- 撤銷權(quán)限
revoke 權(quán)限列表 on 數(shù)據(jù)庫名.表名 from '用戶名'@'主機(jī)名';
注意:
多個權(quán)限之間,使用逗號分隔
授權(quán)時,數(shù)據(jù)庫名和表名可以使用 * 進(jìn)行通配,代表所有
案例:
A、查詢’leo’@‘%’ 用戶的權(quán)限
show grants for 'jiong'@'%';
B、授予’jiong’@‘%’ 用戶mysql_learn數(shù)據(jù)庫所有表的所有操作權(quán)限
grant all on mysql_learn.* to 'jiong'@'%';
C、撤銷’jiong’@‘%’ 用戶的mysql_learn數(shù)據(jù)庫的所有權(quán)限
revoke all on mysql_learn.* from 'jiong'@'%';
3.函數(shù)
MySQL中的函數(shù)主要分為以下四類:字符串函數(shù)、數(shù)值函數(shù)、日期函數(shù)、流程函數(shù)。
3.1字符串函數(shù)
示例:
A、concat:字符串拼接
select concat('Hello', 'MySQL');
B、lower: 全部轉(zhuǎn)小寫
select lower('Hello');
C、upper: 全部大寫
select upper('Hello');
D、lpad:左填充
select lpad('01', 5, '~');
E、rpad:右填充
select rpad('01', 5, '~');
F、trim:去除空格
select trim('Hello MySQL ');
G、substring:截取子字符串
select substring('Hello MySQL', 1, 5);
案例:
由于業(yè)務(wù)需求變更,企業(yè)員工的工號,統(tǒng)一為5位數(shù),目前不足5位數(shù)的全部在前面補(bǔ)0。比如: 1號員 工的工號應(yīng)該為00001。
update emp set workno = lpad(workno, 5, 0);
3.2數(shù)值函數(shù)
示例:
A. ceil:向上取整
select ceil(1.1);
B. floor:向下取整
select floor(1.9);
C. mod:取模
select mod(7, 4);
D. rand:獲取隨機(jī)數(shù)
select rand();
E. round:四舍五入
select round(2.344, 2);
通過數(shù)據(jù)庫的函數(shù),生成一個六位數(shù)的隨機(jī)驗(yàn)證碼。
select lpad(round(rand() * 1000000, 0), 6, '0');
3.3 日期函數(shù)
A. curdate:當(dāng)前日期
select curdate();
B. curtime:當(dāng)前時間
select curtime();
C. now:當(dāng)前日期和時間
select now();
D. YEAR , MONTH , DAY:當(dāng)前年、月、日
select year(now());
select month(now());
select day(now());
E. date_add:增加指定的時間間隔
select date_add(now(), interval 70 year);
F. datediff:獲取兩個日期相差的天數(shù)
select datediff('2021-10-01', '2021-12-01');
查詢所有員工的入職天數(shù),并根據(jù)入職天數(shù)倒序排序。
select name, datediff(curdate(), entrydate) as 'entrydays' from emp order by entrydays desc;
3.4 流程函數(shù)
示例:
A. if
select if(false, 'Ok', 'Error');
B. ifnull
select ifnull('Ok', 'Default');
select ifnull('', 'Default');
select ifnull(null, 'Default');
C. case when then else end
需求: 查詢emp表的員工姓名和工作地址 (北京/上海 ----> 一線城市 , 其他 ----> 二線城市)
select name,(case workaddress when '北京' then '一線城市' when '上海' then '一線城市' else'二線城市' end) as '工作地址'
from emp;
案例:
create table score(
id int comment 'ID',
name varchar(20) comment '姓名',
math int comment '數(shù)學(xué)',
english int comment '英語',
chinese int comment '語文'
) comment '學(xué)員成績表';
insert into score(id, name, math, english, chinese) VALUES (1, 'Tom', 67, 88, 95
), (2, 'Rose' , 23, 66, 90),(3, 'Jack', 56, 98, 76);
判斷分?jǐn)?shù)等級
select id, name,(case when math >= 85 then '優(yōu)秀' when math >= 60 then '及格' else '不及格' end) '數(shù)學(xué)',(case when math >= 85 then '優(yōu)秀' when math >= 60 then '及格' else '不及格' end) '英語',(case when math >= 85 then '優(yōu)秀' when math >= 60 then '及格' else '不及格' end) '語文'from score;
4.約束
4.1概述
概念:約束是作用于表中的字段上的規(guī)則,用于限制存儲在表中的數(shù)據(jù)。
目的:保證數(shù)據(jù)庫中數(shù)據(jù)的正確,有效性和完整性。
分類:
約束是作用于表中的字段上的,可以再創(chuàng)建表/修改表的時候添加約束。
4.2 約束演示
根據(jù)需求創(chuàng)建表:
建表語句:
create table tb_user (id int auto_increment primary key comment 'ID唯一標(biāo)識',name varchar(10) not null unique comment '姓名',age int check (age > 0 && age <= 120) comment '年齡',status char(1) default '1' comment '狀態(tài)',gender char(1) comment '性別'
);
測試:
insert into tb_user(name,age,status,gender) values ('Tom1',19,'1','男'),
('Tom2',25,'0','男');
insert into tb_user(name,age,status,gender) values ('Tom3',19,'1','男');
insert into tb_user(name,age,status,gender) values (null,19,'1','男');
insert into tb_user(name,age,status,gender) values ('Tom3',19,'1','男');
insert into tb_user(name,age,status,gender) values ('Tom4',80,'1','男');
insert into tb_user(name,age,status,gender) values ('Tom5',-1,'1','男');
insert into tb_user(name,age,status,gender) values ('Tom5',121,'1','男');
insert into tb_user(name,age,gender) values ('Tom5',120,'男');
4.3 外鍵約束
4.3.1 介紹
外鍵:用來讓兩張表的數(shù)據(jù)連接,從而保持?jǐn)?shù)據(jù)的一致性和完整性。
舉例:
數(shù)據(jù)準(zhǔn)備:
create table dept(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '部門名稱'
)comment '部門表';
INSERT INTO dept (id, name) VALUES (1, '研發(fā)部'), (2, '市場部'),(3, '財(cái)務(wù)部'), (4,
'銷售部'), (5, '總經(jīng)辦');
create table emp(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '姓名',
age int comment '年齡',
job varchar(20) comment '職位',
salary int comment '薪資',
entrydate date comment '入職時間',
managerid int comment '直屬領(lǐng)導(dǎo)ID',
dept_id int comment '部門ID'
)comment '員工表';
INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id)
VALUES
(1, '金庸', 66, '總裁',20000, '2000-01-01', null,5),(2, '張無忌', 20,
'項(xiàng)目經(jīng)理',12500, '2005-12-05', 1,1),
(3, '楊逍', 33, '開發(fā)', 8400,'2000-11-03', 2,1),(4, '韋一笑', 48, '開
發(fā)',11000, '2002-02-05', 2,1),
(5, '常遇春', 43, '開發(fā)',10500, '2004-09-07', 3,1),(6, '小昭', 19, '程
序員鼓勵師',6600, '2004-10-12', 2,1);
4.3.2語法
1)添加外鍵
create table 表名(字段名 數(shù)據(jù)類型,…[constraint] [外鍵約束] foreign key (外鍵字段名) references 主表(主表列名)
);
alter table 表名 add constraint 外鍵名稱 foreign key(外鍵字段名稱)
references 主表(主表列名);
案例:
為emp表的dept_id字段添加外鍵約束,關(guān)聯(lián)dept表的主鍵id 。
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id);
- 刪除外鍵
alter table 表名 drop foreign key 外鍵名稱;
案例:
刪除emp表的外鍵fk_emp_dept_id。
alter table emp drop foreign key fk_emp_dept_id;
4.3.3 刪除/更新行為
添加了外鍵之后,再刪除父表數(shù)據(jù)時產(chǎn)生的約束行為,我們就稱為刪除/更新行為。具體的刪除/更新行 為有以下幾種 :
具體的語法:
alter table 表名 add constraint 外鍵名稱 foreign key(外鍵字段) references
主表名(主表字段名) on update cascade on delete cascade;
1)cascade
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id) on update cascade on delete cascade;
A. 修改父表id為1的記錄,將id修改為6
我們發(fā)現(xiàn),原來在子表中dept_id值為1的記錄,現(xiàn)在也變?yōu)?了,這就是cascade級聯(lián)的效果。
B. 刪除父表id為6的記錄
父表的數(shù)據(jù)刪除成功了,但是子表中關(guān)聯(lián)的記錄也被級聯(lián)刪除了。
- set null
alter table emp add constraint fk_demp_dept_id foreign key (dept_id) references
dept(id) on update set null on delete set null;
刪除id為1的數(shù)據(jù)
父表的記錄是可以正常刪除的,父表的數(shù)據(jù)刪除后,emp表中的dept_id字段為1的數(shù)據(jù),都被置為null;
5.多表查詢
5.1多表關(guān)系
- 一對多(多對一)
- 多對多
- 一對一
5.1.1一對多
案例:部門與員工的關(guān)系
關(guān)系:一個部門對應(yīng)多個員工,一個員工對應(yīng)一個部門
實(shí)現(xiàn):在多的一方建立外鍵,指向一的一方的主鍵
5.1.2多對多
案例:學(xué)生與課程之間的關(guān)系
關(guān)系; 一個學(xué)生可以選修多門課程,一門課程可以供多個學(xué)生選擇
實(shí)現(xiàn): 建立三張表,中間表至少包含兩個外鍵,分別關(guān)聯(lián)兩方主鍵
對應(yīng)SQL腳本:
create table student (id int auto_increment primary key comment'主鍵ID',name varchar(10) comment'姓名',no varchar(10) comment'學(xué)號'
) comment'學(xué)生表';
insert into student values (null, '黛綺絲', '2000100101'),(null, '謝遜',
'2000100102'),(null, '殷天正', '2000100103'),(null, '韋一笑', '2000100104');create table course(id int auto_increment primary key comment'主鍵ID',name varchar(20) comment'課程名稱'
);
insert into course values (null, 'Java'), (null, 'PHP'), (null , 'MySQL') ,
(null, 'Hadoop');create table student_course(id int auto_increment comment'主鍵' primary key,studentid int not null comment'學(xué)生ID',courseid int not null comment'課程ID',constraint fk_courseid foreign key (courseid) references course(id),constraint fk_studentif foreign key (studentid) references student(id)
) comment '學(xué)生課程中間表';insert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2),
(null,2,3),(null,3,4);
5.1.3 一對一
案例: 用戶與用戶詳情的關(guān)系
關(guān)系:一對一關(guān)系,多用于表拆分,將一張表的基礎(chǔ)字段放在一這張表中,其他詳情字段放在另一張表中,以提升操作效率
實(shí)現(xiàn):在任意一方加入外鍵,關(guān)聯(lián)另外一方的主鍵,并且設(shè)置外鍵為唯一的(UNIQUE)
對應(yīng)SQL腳本:
create table tb_user(
id int auto_increment primary key comment '主鍵ID',
name varchar(10) comment '姓名',
age int comment '年齡',
gender char(1) comment '1: 男 , 2: 女',
phone char(11) comment '手機(jī)號'
) comment '用戶基本信息表';
create table tb_user_edu(
id int auto_increment primary key comment '主鍵ID',
degree varchar(20) comment '學(xué)歷',
major varchar(50) comment '專業(yè)',
primaryschool varchar(50) comment '小學(xué)',
middleschool varchar(50) comment '中學(xué)',university varchar(50) comment '大學(xué)',
userid int unique comment '用戶ID',
constraint fk_userid foreign key (userid) references tb_user(id)
) comment '用戶教育信息表';
insert into tb_user(id, name, age, gender, phone) values
(null,'黃渤',45,'1','18800001111'),
(null,'冰冰',35,'2','18800002222'),
(null,'碼云',55,'1','18800008888'),
(null,'李彥宏',50,'1','18800009999');
insert into tb_user_edu(id, degree, major, primaryschool, middleschool,
university, userid) values
(null,'本科','舞蹈','靜安區(qū)第一小學(xué)','靜安區(qū)第一中學(xué)','北京舞蹈學(xué)院',1),
(null,'碩士','表演','朝陽區(qū)第一小學(xué)','朝陽區(qū)第一中學(xué)','北京電影學(xué)院',2),
(null,'本科','英語','杭州市第一小學(xué)','杭州市第一中學(xué)','杭州師范大學(xué)',3),
(null,'本科','應(yīng)用數(shù)學(xué)','陽泉第一小學(xué)','陽泉區(qū)第一中學(xué)','清華大學(xué)',4);
5.2多表查詢概述
5.2.1 數(shù)據(jù)準(zhǔn)備
-- 創(chuàng)建dept表,并插入數(shù)據(jù)
create table dept(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '部門名稱'
)comment '部門表';
INSERT INTO dept (id, name) VALUES (1, '研發(fā)部'), (2, '市場部'),(3, '財(cái)務(wù)部'), (4,
'銷售部'), (5, '總經(jīng)辦'), (6, '人事部');
-- 創(chuàng)建emp表,并插入數(shù)據(jù)
create table emp(
id int auto_increment comment 'ID' primary key,name varchar(50) not null comment '姓名',
age int comment '年齡',
job varchar(20) comment '職位',
salary int comment '薪資',
entrydate date comment '入職時間',
managerid int comment '直屬領(lǐng)導(dǎo)ID',
dept_id int comment '部門ID'
)comment '員工表';
-- 添加外鍵
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id);
INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id)
VALUES
(1, '金庸', 66, '總裁',20000, '2000-01-01', null,5),
(2, '張無忌', 20, '項(xiàng)目經(jīng)理',12500, '2005-12-05', 1,1),
(3, '楊逍', 33, '開發(fā)', 8400,'2000-11-03', 2,1),
(4, '韋一笑', 48, '開發(fā)',11000, '2002-02-05', 2,1),
(5, '常遇春', 43, '開發(fā)',10500, '2004-09-07', 3,1),
(6, '小昭', 19, '程序員鼓勵師',6600, '2004-10-12', 2,1),
(7, '滅絕', 60, '財(cái)務(wù)總監(jiān)',8500, '2002-09-12', 1,3),
(8, '周芷若', 19, '會計(jì)',48000, '2006-06-02', 7,3),
(9, '丁敏君', 23, '出納',5250, '2009-05-13', 7,3),
(10, '趙敏', 20, '市場部總監(jiān)',12500, '2004-10-12', 1,2),
(11, '鹿杖客', 56, '職員',3750, '2006-10-03', 10,2),
(12, '鶴筆翁', 19, '職員',3750, '2007-05-09', 10,2),
(13, '方東白', 19, '職員',5500, '2009-02-12', 10,2),
(14, '張三豐', 88, '銷售總監(jiān)',14000, '2004-10-12', 1,4),
(15, '俞蓮舟', 38, '銷售',4600, '2004-10-12', 14,4),
(16, '宋遠(yuǎn)橋', 40, '銷售',4600, '2004-10-12', 14,4),
(17, '陳友諒', 42, null,2000, '2011-10-12', 1,null);
5.2.1 笛卡爾積
select * from emp, dept;
笛卡爾積:笛卡爾乘積是指在數(shù)學(xué)上,兩個集合A集合和B集合的所有情況.
在多表查詢中,我們需要消除無效的笛卡爾積,只保留兩張關(guān)聯(lián)部分表
select * from emp, dept where emp.dept_id = dept.id;
5.2.3 分類
- 連接查詢:
- 內(nèi)連接:相當(dāng)于查詢A、B交集部分?jǐn)?shù)據(jù)
- 外連接
- 左外連接:查詢左表的所有數(shù)據(jù),以及兩張表的交集
- 右外連接:查詢右表的所有數(shù)據(jù),以及兩張表的交集
- 自連接:當(dāng)前表與自身的連接查詢,自連接必須使用表別名
- 子查詢
5.3內(nèi)連接
內(nèi)連接查詢的時兩張表交集部分的數(shù)據(jù)(綠色)
內(nèi)連接分為:隱式內(nèi)連接,顯式內(nèi)連接
1)隱式內(nèi)連接
select 字段列表 from 表1, 表2 where 條件 …;
2)顯式內(nèi)連接
select 字段列表 from 表1 [inner] join 表2 on 連接條件…;
案例:
A. 查詢每一個員工的姓名 , 及關(guān)聯(lián)的部門的名稱 (隱式內(nèi)連接實(shí)現(xiàn))
表結(jié)構(gòu): emp , dept
連接條件: emp.dept_id = dept.id
select * from emp, dept where emp.dept_id = dept.id;
B. 查詢每一個員工的姓名 , 及關(guān)聯(lián)的部門的名稱 (顯式內(nèi)連接實(shí)現(xiàn)) — INNER JOIN … ON …
表結(jié)構(gòu): emp , dept
連接條件: emp.dept_id = dept.id
select * from emp inner join dept on emp.dept_id = dept.id;
一旦為表起了別名,就不能再使用表名來指定對應(yīng)的字段了,此時只能夠使用別名來指定字段。
5.4 外連接
連接分為兩種,分別是:左外連接 和 右外連接。具體語法為:
1)左外連接
select 字段列表 from 表1 left [outer] join 表2 on 條件 …;
左外連接相當(dāng)于查詢表1(左表)的所有數(shù)據(jù),當(dāng)然也包含表1和表2交集部分的數(shù)據(jù)。
- 右外連接
select 字段列表 from 表1 right [outer] join 表2 on 條件 …;
左外連接相當(dāng)于查詢表1(左表)的所有數(shù)據(jù),當(dāng)然也包含表1和表2交集部分的數(shù)據(jù)。
案例:
A. 查詢emp表的所有數(shù)據(jù), 和對應(yīng)的部門信息
select e.*, d.name from emp e left join dept d on e.dept_id = d.id;
B. 查詢dept表的所有數(shù)據(jù), 和對應(yīng)的員工信息(右外連接)
select d.*, e.* from emp e right join dept d on e.dept_id = d.id;
左外連接和右外連接是可以相互替換的,只需要調(diào)整在連接查詢時SQL中,表結(jié)構(gòu)的先后順 序就可以了。而我們在日常開發(fā)使用時,更偏向于左外連接。
5.5自連接
5.5.1自連接查詢
select 字段列表 from 表A 別名A join 表A 別名B on 條件 …;
對于自連接查詢,可以是內(nèi)連接查詢,也可以是外連接查詢
案例:
A. 查詢員工及其所屬領(lǐng)導(dǎo)的名字
select a.name, b.name from emp a join emp b where a.managerid = b.id;
B. 查詢所有員工 emp 及其領(lǐng)導(dǎo)的名字 emp , 如果員工沒有領(lǐng)導(dǎo), 也需要查詢出來
select a.name, b.name from emp a left join emp b on a.managerid = b.id;
自連接查詢中必須指定別名
5.5.2 聯(lián)合查詢
union查詢,就是把多次查詢的結(jié)果合并起來,形成一個新的查詢結(jié)果
select 字段列表 from 表A
union [all]
select 字段列表 from 表B …;
- 對于聯(lián)合查詢的多張表的列數(shù)必須保持一致,字段類型也需要保持一致。
- union all 會將所有的數(shù)據(jù)合并到一起,union 會對合并之后的數(shù)據(jù)去重
案例:
A. 將薪資低于 5000 的員工 , 和 年齡大于 50 歲的員工全部查詢出來
select * from emp where salary < 5000
union all
select * from emp where age > 50;
union 聯(lián)合查詢,會對查詢出來的結(jié)果進(jìn)行去重處理。
select * from emp where salary < 5000
union
select * from emp where age > 50;
5.6子查詢
5.6.1概念
- 概念
SQL語句中嵌套select語句,稱為嵌套查詢,又稱子查詢。
select * from t1 where column1 = (select column1 from t2);
子查詢外部語句可以是insert/update/delete/select 的任何一種。
- 分類
根據(jù)子查詢結(jié)果不同,分為:
A. 標(biāo)量子查詢(子查詢結(jié)果為單個值)
B.列子查詢(子查詢結(jié)果為一列)
C.行子查詢(子查詢結(jié)果為一行)
D.表子查詢(子查詢結(jié)果為多行多列)
根據(jù)子查詢位置,分為:
A. WHERE之后
B. FROM之后
C. SELECT之后
5.6.2標(biāo)量子查詢
子查詢返回的結(jié)果是單個值(數(shù)字,字符串,日期等),最簡單的形式,這種子查詢稱為標(biāo)量查詢。
常用操作符: = <> > >= < <=
案例:
A. 查詢 "銷售部"的所有員工信息
①. 查詢 “銷售部” 部門ID
select id from dept where name = '銷售部';
②. 根據(jù) “銷售部” 部門ID, 查詢員工信息
select * from emp where dept_id = (select id from dept where name = '銷售部');
B. 查詢在 “方東白” 入職之后的員工信息
select * from
emp
where
entrydate > (select entrydate from emp where name = '方東白');
5.6.3 列子查詢
子查詢返回的結(jié)果是一行(可以是多行),這種查詢成為列子查詢
常用操作符: in、not in、any、some、all
案例:
A. 查詢 “銷售部” 和 “市場部” 的所有員工信息
select * from emp where dept_id in
(select id from dept where name = '銷售部' or name ='市場部');
B. 查詢比財(cái)務(wù)部所有人工資都高的員工信息
select * from emp where salary >
(select max(salary) from emp join dept where dept.name = '財(cái)務(wù)部');
select * from emp where salary > all(select salary from emp where dept_id = (select id from dept where name = '財(cái)務(wù)部'
))
C. 查詢比研發(fā)部其中任意一人工資高的員工信息
select * from emp where salary > any(select salary from emp e left join dept d on e.dept_id = d.id where d.name = '研發(fā)部');
select * from emp where salary > any(select salary from emp where dept_id = (select id from dept where name = '研發(fā)部')
);
5.6.4 行子查詢
子查詢的結(jié)果可以是一行也可以是多行,這種查詢稱為行子查詢。
常用操作符:=、<>、in、not in
案例:
A. 查詢與 “張無忌” 的薪資及直屬領(lǐng)導(dǎo)相同的員工信息 ;
select * from emp where (salary, managerid) = (select salary, managerid where name = '張無忌'
)
5.6.5表子查詢
子查詢返回的結(jié)果是多行多列,這種查詢稱為表子查詢
常用的操作符: in
案例:
A. 查詢與 “鹿杖客” , “宋遠(yuǎn)橋” 的職位和薪資相同的員工信息
select * from emp where (salary, job) in (select salary, job from emp where name = '鹿杖客' or name = '宋遠(yuǎn)橋'
);
B. 查詢?nèi)肼毴掌谑?“2006-01-01” 之后的員工信息 , 及其部門信息
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join
dept d on e.dept_id = d.id;
5.7 多表查詢案例
數(shù)據(jù)準(zhǔn)備:
create table salgrade(
grade int,
losal int,
hisal int
) comment '薪資等級表';
insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,30000);
1). 查詢員工的姓名、年齡、職位、部門信息 (隱式內(nèi)連接)
select e.name, e.age, e.job, d.name from emp e, dept d where e.dept_id = d.id;
2). 查詢年齡小于30歲的員工的姓名、年齡、職位、部門信息(顯式內(nèi)連接)
select e.name, e.age, e.job, d.name from emp e inner join dept d on e.dept_id = d.id
where age < 30;
3). 查詢擁有員工的部門ID、部門名稱
select distinct d.id, d.name from emp e, dept d where e.dept_id = d.id;
4). 查詢所有年齡大于40歲的員工, 及其歸屬的部門名稱; 如果員工沒有分配部門, 也需要展示出 來(外連接)
select e.*, d.* from emp e left join dept d on e.dept_id = d.id where e.age > 40;
5). 查詢所有員工的工資等級
select e.*, s.grade, s.losal, s.hisal from emp e, salgrade s
where e.salary between s.losal and s.hisal;
6). 查詢 “研發(fā)部” 所有員工的信息及 工資等級
select e.*, s.grade from emp e, salgrade s, dept d
where e.dept_id = d.id and (e.salary between s.losal and s.hisal) and d.name = '研發(fā)部' ;
7). 查詢 “研發(fā)部” 員工的平均工資
select avg(e.salary) from emp e, dept d
where e.dept_id = d.id and d.name = '研發(fā)部';
8). 查詢工資比 “滅絕” 高的員工信息。
select * from emp where salary > (select salary from emp where name = '滅絕'
)
9). 查詢比平均薪資高的員工信息
select * from emp where salary > (select avg(salary) from emp
)
10). 查詢低于本部門平均工資的員工信息
select * from emp e where e.salary < (select avg(e1.salary) from emp e1 where e1.dept_id = e.dept_id
);
11). 查詢所有的部門信息, 并統(tǒng)計(jì)部門的員工人數(shù)
select d.*, (select count(*) from emp e where e.dept_id = d.id) '人數(shù)' from dept d;
12). 查詢所有學(xué)生的選課情況, 展示出學(xué)生名稱, 學(xué)號, 課程名稱
select s.name , s.no , c.name from student s , student_course sc , course c where
s.id = sc.studentid and sc.courseid = c.id ;
6.事務(wù)
6.1事務(wù)
事務(wù)是一組操作集合,它是一個不可分割的工作單位,事務(wù)會把所有的操作作為一個整體一起向系統(tǒng)提交或撤銷操作請求,妖魔同時成功,妖魔同時失敗。
正常:
異常:
在業(yè)務(wù)邏輯執(zhí)行之前開啟事務(wù),執(zhí)行完畢后提交事務(wù)。如果執(zhí)行過程中報(bào)錯,則回滾事務(wù),把數(shù)據(jù)恢復(fù)到事務(wù)開始之前的狀態(tài)
默認(rèn)MySQL的事務(wù)是自動提交的,當(dāng)執(zhí)行完一條DML語句時,MySQL會立即隱式的提交事務(wù)。
6.2 事務(wù)操作
數(shù)據(jù)準(zhǔn)備:
drop table if exists account;
create table account(
id int primary key AUTO_INCREMENT comment 'ID',
name varchar(10) comment '姓名',
money double(10,2) comment '余額'
) comment '賬戶表';
insert into account(name, money) VALUES ('張三',2000), ('李四',2000);
6.2.1 未控制事務(wù)
1)測試正常情況
-- 1. 查詢張三余額
select * from account where name = '張三';
-- 2. 張三的余額減少1000
update account set money = money - 1000 where name = '張三';
-- 3. 李四的余額增加1000
update account set money = money + 1000 where name = '李四';
2)測試異常情況
-- 1. 查詢張三余額
select * from account where name = '張三';
-- 2. 張三的余額減少1000
update account set money = money - 1000 where name = '張三';
出錯了....
-- 3. 李四的余額增加1000
update account set money = money + 1000 where name = '李四';
6.2.2 控制事務(wù)
1)查看設(shè)置事務(wù)提交方式
select @@autocommit;
set @@autocommit = 0;
- 提交事務(wù)
commit;
3)回滾事務(wù)
rollback;
6.2.3 控制事務(wù)二
1)開啟事務(wù)
start transaction 或 begin;
2)提交事務(wù)
commit;
3)回滾事務(wù)
rollback;
轉(zhuǎn)賬案例:
-- 開啟事務(wù)
start transaction
-- 1. 查詢張三余額
select * from account where name = '張三';
-- 2. 張三的余額減少1000
update account set money = money - 1000 where name = '張三';
-- 3. 李四的余額增加1000
update account set money = money + 1000 where name = '李四';
-- 如果正常執(zhí)行完畢, 則提交事務(wù)
commit;
-- 如果執(zhí)行過程中報(bào)錯, 則回滾事務(wù)
-- rollback;
6.3 事務(wù)四大特性ACID
原子性( Atomicity ):
事務(wù)是不可分割的最小操作單元,要么全部成功,要么全部失敗。
一致性( Consistency):
事務(wù)完成時,必須使所有的數(shù)據(jù)保持一致狀態(tài)
隔離性( Isolation):
數(shù)據(jù)庫提供的隔離機(jī)制,保證事務(wù)在不受外部并發(fā)操作映像的獨(dú)立環(huán)境下運(yùn)行
持久性( Durability):
事務(wù)一旦提交或回滾,它對數(shù)據(jù)庫中的數(shù)據(jù)的改變就是永久的。
6.4 并發(fā)事務(wù)問題
1)臟讀:一個事務(wù)讀到另外一個事務(wù)還沒有提交的數(shù)據(jù)
B讀取到了A未提交的數(shù)據(jù)。
2)不可重復(fù)讀:一個事務(wù)先后讀取到同一條記錄,但兩次讀取的數(shù)據(jù)不同,稱之為不可重復(fù)讀
事務(wù)A兩次讀取同一條記錄,但是讀取到的數(shù)據(jù)卻是不一樣的。
3)幻讀:一個事務(wù)按照條件查詢數(shù)據(jù)時,沒有對應(yīng)數(shù)據(jù)行,但是插入數(shù)據(jù)時,又發(fā)現(xiàn)這行數(shù)據(jù)已經(jīng)存在
6.5事務(wù)隔離級別
1). 查看事務(wù)隔離級別
SELECT @@TRANSACTION_ISOLATION;
2)設(shè)置事務(wù)隔離級別
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED |
READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
事務(wù)隔離級別越高,數(shù)據(jù)越安全,但是性能越低。
7.存儲引擎
7.1 MySQL體系結(jié)構(gòu)
1)連接層
最上層是一些客戶端和鏈接服務(wù),包含本地scok通信和大多數(shù)客戶端/服務(wù)端工具實(shí)現(xiàn)的類似于TCP/IP的通信。主要完成一些類似于連接處理、授權(quán)認(rèn)證、及相關(guān)的安全方案。在該層上引入了線程池的概念,為通過認(rèn)證安全接入的客戶端提供線程。同樣在該層上實(shí)現(xiàn)基于SSL的安全鏈接。服務(wù)器也會為安全接入的每個客戶端驗(yàn)證它所具有的操作權(quán)限。
2)服務(wù)層
第二層架構(gòu)主要完成大多數(shù)的核心服務(wù)功能,如SQL接口,并完成緩存的查詢,SQL的分析和優(yōu)化,部分內(nèi)置函數(shù)的執(zhí)行。所有跨存儲引擎的功能也在這一層實(shí)現(xiàn),如過程、函數(shù)等。在該層,服務(wù)器會解析查詢并創(chuàng)建對應(yīng)的內(nèi)部解析樹,并對其完成的優(yōu)化如表的查詢順序,是否利用索引等,最后生成相應(yīng)的執(zhí)行操作,如果是select語句,服務(wù)還會查詢內(nèi)部緩存,如果緩存空間足夠大,這樣在解決大量讀操作的環(huán)境中能夠很好的提升系統(tǒng)的性能。
3)引擎層
存儲引擎層,存儲引擎真正的負(fù)責(zé)了MySQL中數(shù)據(jù)的存儲和提取,服務(wù)器通過API和存儲引擎進(jìn)行通信。不同的存儲引擎具有不同的功能,這樣我們可以根據(jù)自己的需要,來選擇合適的存儲引擎。數(shù)據(jù)庫中的索引是在存儲引擎層實(shí)現(xiàn)的。
4)存儲層
數(shù)據(jù)存儲層,主要是將數(shù)據(jù)(如:redolog、undolog、數(shù)據(jù)、索引、二進(jìn)制日志、錯誤日志、查詢?nèi)罩?、慢查詢?nèi)罩镜?#xff09;存儲在文件系統(tǒng)之上,并完成與存儲引擎的交互。
和其他數(shù)據(jù)庫相比,MySQL有點(diǎn)與眾不同,它的架構(gòu)可以在多種不同的場景中應(yīng)用并發(fā)揮良好作用。主要體現(xiàn)在存儲引擎上,插件式的存儲引擎架構(gòu),將查詢處理和其他的系統(tǒng)任務(wù)以及數(shù)據(jù)的存儲提取分離。這種架構(gòu)可以根據(jù)業(yè)務(wù)的需求和實(shí)際需要選擇合適的存儲引擎。
7.2 存儲引擎介紹
存儲引擎就是存儲數(shù)據(jù)、建立索引、更新/查詢數(shù)據(jù)等技術(shù)的實(shí)現(xiàn)方式。存儲引擎是基于表,而不是基于庫的,所以存儲引擎也可以被稱為表類型,我們可以在創(chuàng)建表的時候,來指定選擇的存儲引擎,如果沒有指定將自動選擇默認(rèn)的存儲引擎.
- 建表時指定存儲引擎
CREATE TABLE 表名(
字段1 字段1類型 [ COMMENT 字段1注釋 ] ,
......
字段n 字段n類型 [COMMENT 字段n注釋 ]
) ENGINE = INNODB [ COMMENT 表注釋 ] ;
2)查詢當(dāng)前數(shù)據(jù)庫支持的存儲引擎
show engines;
示例:
A. 查詢建表語句 默認(rèn)存儲引擎: InnoDB
show create table account;
B. 查詢當(dāng)前數(shù)據(jù)庫支持的存儲引擎
show engines;
C. 創(chuàng)建表 my_myisam , 并指定MyISAM存儲引擎
create table my_myisam(
id int,
name varchar(10)
) engine = MyISAM ;
D. 創(chuàng)建表 my_memory , 指定Memory存儲引擎
create table my_memory(
id int,
name varchar(10)
) engine = Memory ;
7.3 存儲引擎特點(diǎn)
7.3.1 InnoDB
1)介紹
InnoDB是一種兼顧高性能的通用存儲引擎,在MySQL5.5之后,InnoDB是默認(rèn)的MySQL存儲引擎
- 特點(diǎn)
- DML操作遵循ACID模型,支持事務(wù)
- 行級鎖,提高并發(fā)訪問性能
- 支持外鍵foreign key約束,保證數(shù)據(jù)的完整性和正確性
- 文件
xxx.ibd: xxx代表表名,innoDB引擎對每一張表都會對應(yīng)這樣一個表空間,存儲該表的表結(jié)構(gòu)(frm-早期的、sdi-新版的)、數(shù)據(jù)和索引
參數(shù):innodb_file_per_table
show variables like 'innodb_file_per_table';
如果參數(shù)開啟,代表對于InnoDB引擎的表,每一張表都對應(yīng)一個ibd文件。我們直接打開MySQL的數(shù)據(jù)存放目錄: C:\ProgramData\MySQL\MySQL Server 8.0\Data
ibd文件存放表結(jié)構(gòu)、數(shù)據(jù)和表對應(yīng)的索引。文件是基于二進(jìn)制存儲的,可以使用mysql提供的一個指令ibd2sdi
ibd2sdi 文件名
4)邏輯存儲結(jié)構(gòu)
- 表空間:InnoDB存儲引擎邏輯結(jié)構(gòu)最高層,ibd文件其實(shí)就是表空間文件,在表空間中可以包含多個Segment段
- 段:表空間由各個段組成的,常見的段有數(shù)據(jù)段、索引段、回滾段等。InnoDB中對于段的管理,都是由引擎自身完成,一個段包含多個區(qū)
- 區(qū):區(qū)是表空間的單元結(jié)構(gòu),每個區(qū)的大小為1M。默認(rèn)情況下,InnoDB存儲引擎頁大小為16k,即一個分區(qū)中一共有64個連續(xù)的頁
- 頁:頁是組成區(qū)的最小單元,**頁也是InnoDB存儲引擎磁盤管理的最小單元,**每個頁的大小默認(rèn)為16KB,為了保證頁的連續(xù)性,InnoDB存儲引擎每次從磁盤申請4-5個區(qū)
- 行:InnoDB存儲引擎是面向行的,數(shù)據(jù)是按行存放的,每一行中除了定義表時所指定的字段以外,還包含兩個隱藏字段
7.3.2 MyISAM
- 介紹
MyISAM是MySQL早期的默認(rèn)存儲引擎。
2)特點(diǎn)
不支持事務(wù),不支持外鍵
支持表鎖,不支持行鎖
訪問速度快
3)文件
xxx.sdi:存儲表結(jié)構(gòu)信息
xxx.MYD:存儲數(shù)據(jù)
xxx.MYI:存儲索引
7.3.3 Memory
1)介紹
Memory引擎的表數(shù)據(jù)存儲在內(nèi)存中,由于受到硬件問題,或斷電問題的映像,只能將這些表作為臨時表或緩存表。
2)特點(diǎn)
內(nèi)存存放
hash索引(默認(rèn))
- 文件
xxx.sdi: 存儲表結(jié)構(gòu)信息
7.3.4 區(qū)別及特點(diǎn)
面試題: InnoDB引擎與MyISAM引擎的區(qū)別 ?
①. InnoDB引擎, 支持事務(wù), 而MyISAM不支持。 ②. InnoDB引擎, 支持行鎖和表鎖, 而MyISAM僅支持表鎖, 不支持行鎖。 ③. InnoDB引擎, 支持外鍵, 而MyISAM是不支持的。 主要是上述三點(diǎn)區(qū)別,當(dāng)然也可以從索引結(jié)構(gòu)、存儲限制等方面,更加深入的回答,具體參 考如下官方文檔: https://dev.mysql.com/doc/refman/8.0/en/innodb-introduction.html https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html
7.4存儲引擎選擇
- InnoDB: 是MySQL的默認(rèn)存儲引擎,支持事務(wù)、外鍵。如果應(yīng)用對事務(wù)的完整性有比較高的要求,在并發(fā)條件下要求數(shù)據(jù)的一致性,數(shù)據(jù)操作除了插入和查詢之外,還包含很多的更新、刪除操作,那么InnoDB存儲引擎是比較合適的選擇。
- MyISAM:如果應(yīng)用是以讀寫和插入操作為主,只有很少的更新和刪除操作,并且對事務(wù)的完整性、并發(fā)性要求不是很高,那么選擇這個存儲引擎是非常合適的。
- MEMORY:將所有數(shù)據(jù)保存在內(nèi)存中,訪問速度快,通常用于臨時表及緩存。MEMORY的缺陷就是對標(biāo)的大小有限制,太大的表無法緩存在內(nèi)存中,而且無法保障數(shù)據(jù)的安全性。
8、索引
8.1 索引介紹
8.1.1介紹
索引 是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)(有序)。在數(shù)據(jù)之外,數(shù)據(jù)庫系統(tǒng)還維護(hù)著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu),這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用(指向)數(shù)據(jù),這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實(shí)現(xiàn)高級查找算法,這種數(shù)據(jù)結(jié)構(gòu)就是索引。
B+Tree 紅黑樹 二叉樹 B-Tree
8.1.2 演示
數(shù)據(jù):
執(zhí)行SQl語句:
select * from user where age = 45;
1)無索引情況
全表掃描:在無索引情況下,需要從第一行掃描到最后一行。性能很低
2)有索引情況
假設(shè)結(jié)構(gòu)是二叉樹
8.2.3 特點(diǎn)
優(yōu)勢 | 劣勢 |
---|---|
提高數(shù)據(jù)庫的檢索效率,減少IO成本 | 索引列也是要占內(nèi)存的 |
通過索引列對數(shù)據(jù)進(jìn)行排序,降低數(shù)據(jù)排序的成本,降低CPU的消耗 | 索引大大提高了查詢效率,同時也降低更新表的速度,如:對表進(jìn)行insert、update、delete時,效率降低 |
8.2 索引結(jié)構(gòu)
8.2.1 概述
不同的存儲引擎對于索引結(jié)構(gòu)的支持情況
8.2.2 二叉樹
假設(shè)采用二叉樹,比較理想的情況:
如果主鍵是順序插入的,會形成一條單鏈表,結(jié)構(gòu)如下:
二叉樹作為索引結(jié)構(gòu),缺點(diǎn):
- 順序插入時,會形成一條鏈表,查詢性能大大降低
- 大數(shù)據(jù)量情況下,層級較深,檢索速度慢
紅黑樹是一顆平衡二叉樹,順序插入數(shù)據(jù),最終形成的形成的數(shù)據(jù)結(jié)構(gòu)是一顆平衡二叉樹,結(jié)構(gòu)如下:
缺點(diǎn):
- 大數(shù)據(jù)量的情況下,層級較深,檢索速度慢。
8.2.3 B-Tree
B-Tree,B樹是一種多叉路衡查找樹,相當(dāng)于二叉樹,B樹每個節(jié)點(diǎn)可以有多個分支,即多叉。以一顆最大度數(shù)(max-degree)為 5(5階)的b-tree為例,那么這個B樹最多存儲4個key,5個指針:
在線演示鏈接
100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250 251
特點(diǎn):
- 5階的B樹,每一個節(jié)點(diǎn)最多存儲4個key,對應(yīng)5個指針
- 一旦節(jié)點(diǎn)的存儲的key數(shù)量到5,就會裂變,中間元素向上裂變。
- 在B樹中,非葉子節(jié)點(diǎn)和葉子節(jié)點(diǎn)都會存放數(shù)據(jù)
8.2.4 B+Tree
B+Tree是B-Tree的變種,最大度數(shù)(max-degree)為4階的b+tree為例
- 綠色是索引部分,僅僅起到索引數(shù)據(jù)的作用,不存儲數(shù)據(jù)。
- 紅色是數(shù)據(jù)存儲部分,在其葉子節(jié)點(diǎn)中要存儲具體的數(shù)據(jù)
在線演示鏈接
數(shù)據(jù): 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250
B+Tree 與 B-Tree區(qū)別:
- 所有的數(shù)據(jù)都會出現(xiàn)在葉子節(jié)點(diǎn)
- 葉子節(jié)點(diǎn)形成一個單向鏈表
- 非葉子節(jié)點(diǎn)僅僅起到索引數(shù)據(jù)作用,具體的數(shù)據(jù)都是在唉葉子節(jié)點(diǎn)存放的
MySQL優(yōu)化之后的B+Tree
MySQL索引數(shù)據(jù)結(jié)構(gòu)對經(jīng)典的B+Tree增加一個指向相鄰葉子節(jié)點(diǎn)的鏈表指針形成了帶有順序指針的B+Tree,提高區(qū)間訪問的性能,利于排序。
8.2.5 Hash
1).結(jié)構(gòu)
哈希索引就是采用一定的hash算法,將鍵值換算成新的hash值,映射到對應(yīng)的槽位上,然后存儲在hash表中
hash沖突通過拉鏈法解決
2)特點(diǎn)
A. Hash索引只能對等比較(in, =),不支持范圍查詢(between,>,<,…)
B.無法利用索引完成排序
C.查詢效率高(不存在hash沖突,只檢索一次就可以)
3).存儲引擎支持
Memory存儲引擎支持hash。
InnoDB中具有自適應(yīng)hash功能,hash索引是InnoDB存儲引擎根據(jù)B+Tree索引在指定條件下自動創(chuàng)建的。
為什么InnoDB存儲引擎選擇使用B+tree索引結(jié)構(gòu)?
A.相對于二叉樹,層級更少,檢索效率更高;
B.對于B-tree。無論是葉子節(jié)點(diǎn)還是非葉子節(jié)點(diǎn),都會保存數(shù)據(jù),這樣導(dǎo)致一頁中存儲的鍵值減少,指針跟著減少,要保存大量的數(shù)據(jù),只能增加樹的高度,導(dǎo)致性能降低;
C.相對于Hash索引,B+tree支持范圍匹配及排序操作;
8.3索引分類
8.3.1索引分類
MySQL索引:主鍵索引、唯一索引、常規(guī)索引、全文索引。
8.3.2 聚集索引&二級索引
分類 | 含義 | 特點(diǎn) |
---|---|---|
聚集索引(Clustered Index) | 將數(shù)據(jù)存儲與索引放到一塊,索引結(jié)構(gòu)的葉子節(jié)點(diǎn)保存了行數(shù)據(jù) | 必須有,而且只有一個 |
二級索引(Secondary Index) | 將數(shù)據(jù)與索引分開存儲,索引結(jié)構(gòu)的葉子節(jié)點(diǎn)關(guān)聯(lián)的是對應(yīng)的主鍵 | 可以存在多個 |
聚集索引選取規(guī)則:
- 如果存在主鍵,主鍵索引就是聚集索引。
- 如果不存在主鍵,將使用第一個唯一索引作為聚集索引
- 如果表沒有主鍵,或沒有合適的唯一索引,則InnoDB會自動生成一個rowid作為隱藏的聚集索引
聚集索引和二級索引的具體結(jié)構(gòu)如下:
- 聚集索引的葉子節(jié)點(diǎn)下掛的是這一行的數(shù)據(jù)。
- 二級索引的葉子節(jié)點(diǎn)下掛的是該字段值對應(yīng)的主鍵值。
查詢過程:
具體過程如下:
1、由于是根據(jù)name字段進(jìn)行查詢,所以先根據(jù)name='Arm’到name字段的二級索引中進(jìn)行匹配查找。但是在二級索引中只能查找到Arm對應(yīng)的主鍵值10.
2、由于查詢返回的數(shù)據(jù)是*,所以此時,還需要根據(jù)主鍵值10,到聚集索引中查找10對應(yīng)的記錄,最終找到10對應(yīng)的行row。
3、最終拿到這一行的數(shù)據(jù),直接返回即可。
回表查詢
先到二級索引中查找數(shù)據(jù),找到主鍵值,然后再到聚集索引中根據(jù)主鍵值,獲取數(shù)據(jù)的方式,就稱為回表查詢。
思考題:
下面兩條SQL語句,那個執(zhí)行效率高?為什么?
A.select * from user where id = 10;
B.select * from user where name = ‘Arm’;
備注:id位主鍵,name字段創(chuàng)建的有索引
解答:
A 語句執(zhí)行性能要高于B語句。
因?yàn)锳語句直接走聚集索引,直接返回?cái)?shù)據(jù),而B語句需要查詢name字段的二級索引,然后再查詢聚集索引,也就是需要回表查詢
思考題:
InnoDB主鍵索引的B+Tree高度為多高?
假設(shè):
一行數(shù)據(jù)大小為1k,一頁數(shù)據(jù)可以存儲16行這樣的數(shù)據(jù)。InnoDB的指針占用6個字節(jié)空間,主鍵即使為bigint,占用字節(jié)數(shù)為8.
高度為2:
n * 8 + (n + 1)* 6 = 16 * 1024,算出n約為1170
1171 * 16 = 18736
也就是說,如果樹的高度為2,則可以存儲18000多條記錄
高度為3
1171 * 1171 * 16 = 21939856
如果樹的高度為3,則可以存儲2200w左右的記錄
8.4索引語法
- 創(chuàng)建索引
create [unique | fulltext] index index_name on table_name (
index_col_name,…
);
- 查看索引
show index from table_name;
- 刪除索引
drop index index_name on table_name;
案例演示:
創(chuàng)建tb_user并查詢測試數(shù)據(jù)。
create table tb_user(id int primary key auto_increment comment '主鍵',name varchar(50) not null comment '用戶名',phone varchar(11) not null comment '手機(jī)號',email varchar(100) comment '郵箱',profession varchar(11) comment '專業(yè)',age tinyint unsigned comment '年齡',gender char(1) comment '性別 , 1: 男, 2: 女',status char(1) comment '狀態(tài)',createtime datetime comment '創(chuàng)建時間'
) comment '系統(tǒng)用戶表';
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES ('呂布', '17799990000', 'lvbu666@163.com', '軟件工程', 23, '1','6', '2001-02-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES ('曹操', '17799990001', 'caocao666@qq.com', '通訊工程', 33,'1', '0', '2001-03-05 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES ('趙云', '17799990002', '17799990@139.com', '英語', 34, '1','2', '2002-03-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES ('孫悟空', '17799990003', '17799990@sina.com', '工程造價(jià)', 54,'1', '0', '2001-07-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES ('花木蘭', '17799990004', '19980729@sina.com', '軟件工程', 23,'2', '1', '2001-04-22 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES ('大喬', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2','0', '2001-02-07 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES ('露娜', '17799990006', 'luna_love@sina.com', '應(yīng)用數(shù)學(xué)', 24,'2', '0', '2001-02-08 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38,
'1', '5', '2001-05-23 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('項(xiàng)羽', '17799990008', 'xiaoyu666@qq.com', '金屬材料', 43,
'1', '0', '2001-09-18 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('白起', '17799990009', 'baiqi666@sina.com', '機(jī)械工程及其自動
化', 27, '1', '2', '2001-08-16 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('韓信', '17799990010', 'hanxin520@163.com', '無機(jī)非金屬材料工
程', 27, '1', '0', '2001-06-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('荊軻', '17799990011', 'jingke123@163.com', '會計(jì)', 29, '1',
'0', '2001-05-11 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('蘭陵王', '17799990012', 'lanlinwang666@126.com', '工程造價(jià)',
44, '1', '1', '2001-04-09 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('狂鐵', '17799990013', 'kuangtie@sina.com', '應(yīng)用數(shù)學(xué)', 43,
'1', '2', '2001-04-10 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('貂蟬', '17799990014', '84958948374@qq.com', '軟件工程', 40,
'2', '3', '2001-02-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('妲己', '17799990015', '2783238293@qq.com', '軟件工程', 31,
'2', '0', '2001-01-30 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('羋月', '17799990016', 'xiaomin2001@sina.com', '工業(yè)經(jīng)濟(jì)', 35,
'2', '0', '2000-05-03 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1',
'1', '2001-08-08 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('狄仁杰', '17799990018', 'jujiamlm8166@163.com', '國際貿(mào)易',
30, '1', '0', '2007-03-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('安琪拉', '17799990019', 'jdodm1h@126.com', '城市規(guī)劃', 51,
'2', '0', '2001-08-15 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('典韋', '17799990020', 'ycaunanjian@163.com', '城市規(guī)劃', 52,
'1', '2', '2000-04-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('廉頗', '17799990021', 'lianpo321@126.com', '土木工程', 19,
'1', '3', '2002-07-18 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('后羿', '17799990022', 'altycj2000@139.com', '城市園林', 20,
'1', '0', '2002-03-10 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('姜子牙', '17799990023', '37483844@qq.com', '工程造價(jià)', 29,
'1', '4', '2003-05-26 00:00:00');
docker進(jìn)入容器內(nèi)部命令:
## 通過Docker命令進(jìn)入Mysql容器內(nèi)部
docker exec -it mysql /bin/bash
## 或者
docker exec -it mysql bash
bash-5.1# mysql -uroot -p
A. name字段為姓名字段,該字段的值可能會重復(fù),為該字段創(chuàng)建索引。
create index idx_user_name on tb_user(name);
B. phone手機(jī)號字段的值,是非空,且唯一的,為該字段創(chuàng)建唯一索引。
create unique index idx_user_phone on tb_user(phone);
C. 為profession、age、status創(chuàng)建聯(lián)合索引。
create index idx_user_pro_age_sta on tb_user(profession, age, status);
D. 為email建立合適的索引來提升查詢效率。
create index idx_email on tb_user(email);
完成上述的需求之后,我們再查看tb_user表的所有的索引數(shù)據(jù)。
show index from tb_user;
8.5 SQL性能分析
8.5.1 SQL執(zhí)行頻率
通過show [session|global] status 命令可以提供服務(wù)器狀態(tài)信息。
查看當(dāng)前數(shù)據(jù)庫insert、update、delete、select的訪問頻率
-- session 查看當(dāng)前會話
-- global 查詢?nèi)謹(jǐn)?shù)據(jù)
show global status like 'Com_______';
Com_delete: 刪除次數(shù) Com_insert: 插入次數(shù) Com_select: 查詢次數(shù) Com_update: 更新次數(shù)
增刪為主 不考慮索引優(yōu)化
查詢?yōu)橹?考慮索引優(yōu)化
8.5.2 慢查詢?nèi)罩?/h4>
慢查詢?nèi)罩居涗浟怂袌?zhí)行時間超過指定參數(shù)(long_query_time, 單位:秒,默認(rèn)10秒)的所有SQL語句的日志
MySQL的慢查詢?nèi)罩灸J(rèn)沒有開啟,我們可以查看一下系統(tǒng)變量 slow_query_log
show variables like 'slow_query_log';
開啟慢查詢?nèi)罩?#xff0c;需要在MySQL的配置文件(/etc/my.cnf)中配置下面信息:
# 開啟MySQL慢日志查詢開關(guān)
slow_query_log = 1
# 設(shè)置慢日志的時間為2s
long_query_time = 2
開啟慢查詢?nèi)罩?#xff0c;設(shè)置閾值
#開啟慢查詢
set global slow_query_log = ON;
#修改慢sql的閾值,sql執(zhí)行超過閾值的時候就會記錄到慢日志的log文件中。
set global slow_launch_time = 3;//根據(jù)需要設(shè)置秒數(shù)
測試:
select count(*) from tb_sku;
查看慢查詢?nèi)罩?#xff1a;
mysql> show variables like 'slow_query%';
+---------------------+--------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/d469607b4c06-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.00 sec)mysql> exit
Bye
bash-5.1# cat /var/lib/mysql/d469607b4c06-slow.log
/usr/sbin/mysqld, Version: 9.0.1 (MySQL Community Server - GPL). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
/usr/sbin/mysqld, Version: 9.0.1 (MySQL Community Server - GPL). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
# Time: 2024-09-04T07:00:50.890686Z
# User@Host: root[root] @ [192.168.200.1] Id: 14
# Query_time: 30.524193 Lock_time: 0.000005 Rows_sent: 500001 Rows_examined: 500001
use mysql_learn;
SET timestamp=1725433220;
/* ApplicationName=DataGrip 2024.1.4 */ SELECT t.*
FROM mysql_learn.tb_sku t
LIMIT 500001;
/usr/sbin/mysqld, Version: 9.0.1 (MySQL Community Server - GPL). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
# Time: 2024-09-05T05:32:04.823934Z
# User@Host: root[root] @ localhost [] Id: 12
# Query_time: 21.555516 Lock_time: 0.000004 Rows_sent: 1 Rows_examined: 0
use mysql_learn;
SET timestamp=1725514303;
select count(*) from tb_sku;
# Time: 2024-09-05T05:42:14.979317Z
# User@Host: root[root] @ localhost [] Id: 12
# Query_time: 22.770587 Lock_time: 0.000004 Rows_sent: 1 Rows_examined: 0
SET timestamp=1725514912;
select count(*) from tb_sku;
8.5.3 profile詳情
show profiles 能夠在做SQL優(yōu)化時幫助我們了解時間消耗到哪里去了。通過have_profiling參數(shù),能夠看到當(dāng)前MySQL是否支持該操作:
SELECT @@have_profiling;select @@profiling;
通過set語句開啟profiling:
set profiling = 1;
打開后,執(zhí)行SQL都會被MySQl記錄下來:
select * from tb_user;
select * from tb_user where id = 1;
select * from tb_user where name = '白起';
select count(*) from tb_sku;
查看指令執(zhí)行耗時:
-- 查看每一條SQL的耗時基本情況
show profiles;-- 查看指定query_id的SQL語句各個階段的耗時情況 query_id替換成對應(yīng)的數(shù)字
show profile for query query_id;-- 查看指定的query_id的SQL語句CPU占用情況
show profile cpu for query query_id;
8.5.4 explain
explain 或者desc命令獲取MySQL如何執(zhí)行select語句的信息,包括select語句執(zhí)行過程中如何連接和連接的順序.
語法:
-- 直接在select語句之前加上關(guān)鍵字 explain / desc
explain select 字段列表 from 表名 where 條件;
explain select * from tb_user where id = 1;
explain 執(zhí)行計(jì)劃中各個字段的含義:
字段 | 含義 |
---|---|
id | select 查詢的序列號,表示查詢中執(zhí)行select子句或者是操作表的順序(id相同,執(zhí)行順序從上到下;id不同,值越大,越先執(zhí)行) |
select_type | 表示select的類型,常見的取值有simple(簡單表,就是不適用表連接或者子查詢)、primary(主查詢,即外層的查詢)、union(union中的第二個或者后面的查詢語句)、subquery(select/where之后包含了子查詢)等 |
type | 表示連接類型,性能由好到差的連接類型為null、system、const、eq_ref、ref、range、index、all |
possible_key | 顯示可能應(yīng)用在這張表上的索引,一個或多個。 |
key | 實(shí)際使用的索引,如果為NULL,則沒有使用索引。 |
key_len | 表示索引中使用的字節(jié)數(shù), 該值為索引字段最大可能長度,并非實(shí)際使用長 度,在不損失精確性的前提下, 長度越短越好。 |
rows | MySQL認(rèn)為必須要執(zhí)行查詢的行數(shù),在innodb引擎的表中,是一個估計(jì)值, 可能并不總是準(zhǔn)確的。 |
filtered | 表示返回結(jié)果的行數(shù)占需讀取行數(shù)的百分比, filtered 的值越大越好。 |
8.6索引使用
8.6.1 驗(yàn)證索引效率
tb_sku主鍵是有索引的執(zhí)行如下SQL:
select * from tb_sku where id = 1\G;
根據(jù)sn字段進(jìn)行查詢:
select * from tb_sku where sn = '100000003145001'\G;
創(chuàng)建索引:
create index idx_sku_sn on tb_sku(sn);
再次執(zhí)行相同的SQL語句,耗時大大減小:
select * from tb_sku where sn = '100000003145001'\G;
我們明顯會看到,sn字段建立了索引之后,查詢性能大大提升。建立索引前后,查詢耗時都不是一個數(shù) 量級的。
8.6.2 最左前綴法則
如果索引了多列(聯(lián)合索引),要遵循最左前綴法則。最多前綴法則指的是從索引的最左列開始,并且不跳過索引中的列。如果跳躍某一列,索引將會全部失效(后面的字段索引失效)。
以tb_user表為例,查看tb_user所創(chuàng)建的索引
show index from tb_user;
在tb_user表中,有一個聯(lián)合索引,這個聯(lián)合索引涉及三個字段,順序分別是profession、age、status
對于最左前綴法則指的是,查詢時,最左變的列,也就是profession必須存在,否則索引全部失效。而且中間不能跳過某一列,否則該列后面的索引全部失效。下面是案例演示:
explain select * from tb_user where profession = '軟件工程' and age = 31 and status = '0';
explain select * from tb_user where profession = '軟件工程' and age = 31;
explain select * from tb_user where profession = '軟件工程';
只要聯(lián)合索引最左邊字段profession存在,索引就會生效,只不過索引的長度不同。通過上面三組測試可以推出行對應(yīng)的索引長度
explain select * from tb_user where age = 31 and status = '0';
explain select * from tb_user where status = '0';
上面幾組測試,索引并未生效,原因是不滿足最左前綴法則,聯(lián)合索引最左邊的列profession不存在.
explain select * from tb_user where profession = '軟件工程' and status = '0';
上述的SQL查詢時,存在profession字段,最左邊的列是存在的,索引滿足最左前綴法則的基本條 件。但是查詢時,跳過了age這個列,所以后面的列索引是不會使用的,也就是索引部分生效,所以索 引的長度就是47。
思考題
當(dāng)執(zhí)行SQL語句:
explain select * from tb_user where age = 31 and status = '0' and profession = '軟件工程';
時,是否滿足最左前綴法則,走不走 上述的聯(lián)合索引,索引長度?
滿足最左前綴法則,索引長度54,聯(lián)合索引時生效的
注意:
最左前綴法則中指的是最左邊的列,是指在查詢時,聯(lián)合索引的最左的字段(也就是第一個字段)必須存在,與我們編寫SQL時,條件編寫的先后順序無關(guān).
8.6.3 范圍查詢
在聯(lián)合索引中,出現(xiàn)范圍查詢(>,<),范圍查詢有責(zé)的列索引失效
explain
select *
from tb_user
where profession = '軟件工程'and age > 30and status= '0';
當(dāng)使用范圍>或者<時,走聯(lián)合索引,但是索引長度為49,就說明范圍右邊的status字段沒有走索引的。
explain select * from tb_user where profession = '軟件工程' and age >= 30 and
status = '0';
索引長度為54,就說明所有的字段都是走索引的。
所以在業(yè)務(wù)情況下,盡可能使用類似于>= 或 <=這類范圍查詢,避免使用<或>
8.6.4 索引失效情況
8.6.4.1索引列運(yùn)算
不要在索引列上進(jìn)行運(yùn)算操作,索引將失效
在tb_user表中,還有一個單列索引phone
show index from tb_user;
A.根據(jù)phone字段進(jìn)行等值匹配查詢時,索引生效
explain select * from tb_user where phone = '17799990015';
B.當(dāng)根據(jù)phone字段進(jìn)行函數(shù)運(yùn)算操作之后,索引失效
explain select * from tb_user where substring(phone,10,2) = '15';
8.6.4.2 字符串不加括號
字符串類型字段使用時,不加引號,索引失效
explain select * from tb_user where profession = '軟件工程' and age = 31 and status
= '0';
explain select * from tb_user where profession = '軟件工程' and age = 31 and status
= 0;
explain select * from tb_user where phone = '17799990015';
explain select * from tb_user where phone = 17799990015;
字符串不加單引號,對于查詢結(jié)果沒有什么影響,但是數(shù)據(jù)庫存在隱式類型轉(zhuǎn)換,索引失效
8.6.4.3 模糊查詢
如果僅僅時尾部模糊查詢,索引不會失效,如果是頭部模糊匹配,索引失效
由于下面查詢語句中,都是根據(jù)profession字段查詢,符合最左前綴法則,聯(lián)合索引是可以生效的, 我們主要看一下,模糊查詢時,%加在關(guān)鍵字之前,和加在關(guān)鍵字之后的影響。
explain select * from tb_user where profession like '軟件%';
explain select * from tb_user where profession like '%工程';
explain select * from tb_user where profession like '%工%';
在like模糊查詢中,在關(guān)鍵字后面加上%,索引可以生效,如果在關(guān)鍵字前面加上%,索引將會失效.
8.6.4.4 or連接條件
用ir分隔開的條件,如果or前的條件中的列有索引,后面的列沒有索引,那么設(shè)計(jì)的索引都不會生效
explain select * from tb_user where id = 10 or age = 23;
explain select * from tb_user where phone = '17799990017' or age = 23;
對age建立索引
create index idx_user_age on tb_user(age);
再次執(zhí)行上面的SQL語句
explain select * from tb_user where id = 10 or age = 23;
explain select * from tb_user where phone = '17799990017' or age = 23;
or兩則都由索引時,索引才會生效
8.6.4.5 數(shù)據(jù)分布影響
如果MySQL評估使用索引比全表更慢,則不使用索引。
explain select * from tb_user where phone >= '17799990005';
explain select * from tb_user where phone >= '17799990015';
相同的SQL語句,只是傳入的字段值不同,最終的執(zhí)行計(jì)劃也完全不一樣,這是為 什么呢?
因?yàn)镸ySQL在查詢時,會評估使用索引的效率與走全表掃描的效率,如果走全表掃描更快,則放棄索引,走全表掃面。因?yàn)樗饕怯脕硭饕倭繑?shù)據(jù)的,如果通過索引返回大批量的數(shù)據(jù),則不如走全表掃描來的快,此時索引就會失效。
is null 與 is not null 操作是否走索引
執(zhí)行下面的SQL:
explain select * from tb_user where profession is null;
explain select * from tb_user where profession is not null;
一模一樣的SQL語句,先后執(zhí)行了兩次,結(jié)果查詢計(jì)劃是不一樣的,為什么會出現(xiàn)這種 現(xiàn)象,這是和數(shù)據(jù)庫的數(shù)據(jù)分布有關(guān)系。查詢時MySQL會評估,走索引快,還是全表掃描快,如果全表 掃描更快,則放棄索引走全表掃描。 因此,is null 、is not null是否走索引,得具體情況具體 分析,并不是固定的。
8.6.5 SQL提示
刪除 idx_user_age, idx_email 索引
drop index idx_user_age on tb_user;
drop index idx_email on tb_user;
A.執(zhí)行SQL:
explain select * from tb_user where profession = '軟件工程';
B.執(zhí)行SQL,創(chuàng)建profession的單列索引
create index idx_user_pro on tb_user(profession);
C.創(chuàng)建索引后,再次執(zhí)行A中的SQL語句
MySQL最終選擇了idx_user_pro_age_sta索引
在查詢的時候,自己指定使用哪個索引,可以借助MySQl的SQL提示完成
SQL提示,是優(yōu)化數(shù)據(jù)庫的一種重要手段,就是在SQL語句中加入一些人為的提示來達(dá)到優(yōu)化操作的目的。
1)use index : 建議MySQL使用哪一個索引完成此次查詢(僅僅是建議,mysql內(nèi)部還會再次進(jìn)行評估)
explain select * from tb_user use index(idx_user_pro) where profession = '軟件工程';
2)ignore index : 忽略指定的索引.
explain select * from tb_user ignore index(idx_user_pro) where profession = '軟件工程';
- force index : 強(qiáng)制使用索引
explain select * from tb_user force index(idx_user_pro) where profession = '軟件工程';
8.6.6 覆蓋索引
盡量使用覆蓋索引,減少使用select *
覆蓋索引是指 查詢使用了索引,并且需要返回的列,在該索引中全部都能夠找到
explain select id, profession from tb_user where profession = '軟件工程' and age = 31 and status = '0';
explain select id,profession,age, status from tb_user where profession = '軟件工程'
and age = 31 and status = '0' ;
explain select id,profession,age, status, name from tb_user where profession = '軟
件工程' and age = 31 and status = '0' ;
explain select * from tb_user where profession = '軟件工程' and age = 31 and status
= '0';
Extra | 含義 |
---|---|
Using where, Using index | 查找使用了索引,但是需要的數(shù)據(jù)都在索引列中找到,所以不需要徽標(biāo)查詢數(shù)據(jù) |
Using index condition | 查找使用了索引,但是需要回表查詢數(shù)據(jù) |
因?yàn)?#xff0c;在tb_user表中有一個聯(lián)合索引idx_user_pro_age_sta,該索引關(guān)聯(lián)了三個字段profession、age、status,而這個索引是一個二級索引,所以葉子節(jié)點(diǎn)下面掛的是一行的主鍵id。所以當(dāng)我們查詢返回?cái)?shù)據(jù)在id。所以當(dāng)我們查詢返回當(dāng)前的數(shù)據(jù)在id、profession、age、status之中,則直接走二級索引直接返回?cái)?shù)據(jù)。如果超出這個范圍,就需要拿到主鍵id,再去掃描聚集索引,再獲取額外的數(shù)據(jù),這個過程就是回表。而我們?nèi)绻恢笔褂胹elect * 查詢返回所有字段值,很容易就會造成回表查詢(除非是根據(jù)主鍵查詢,此時智慧掃描聚集索引)
執(zhí)行過程:
A.表結(jié)構(gòu)及索引示意圖:
id是主鍵,是一個聚集索引。name字段建立了普通索引,是一個二級索引(輔助索引)
B. 執(zhí)行SQL:
select * from tb_user where id = 2;
根據(jù)id查詢,直接走聚集索引查詢,一次性索引掃描,直接返回?cái)?shù)據(jù),性能高。
C.執(zhí)行SQL:
select id,name from tb_user where name = 'Arm';
雖然是根據(jù)name字段查詢,查詢二級索引,但是由于查詢返回的字段為id, name, 在name的二級索引中,這兩個值都是可以直接獲取到的,因?yàn)楦采w索引,所以不需要回表查詢,性能高。
D、執(zhí)行SQL:
select id,name,gender from tb_user where name = 'Arm';
由于在name的二級索引中,不包含gender,所以,需要兩次索引掃描,也就是需要回表查詢,性能相對較差一點(diǎn)
思考題:
一張表,有四個字段(id,username, password, status) 由于數(shù)據(jù)量大,需要對以下SQL語句進(jìn)行優(yōu)化,該如何進(jìn)行才是最優(yōu)方案:
select id,username,password from tb_user where username = 'gege';
針對username和password建立聯(lián)合索引,可以避免出現(xiàn)回表查詢
create index idx_user_name_pass on tb_user(username, password);
8.6.7 前綴索引
當(dāng)字段類型為字符串(varchar,text,longtext等)時,有時候需要索引很長的字符串,這會讓索引變得很大,查詢時,浪費(fèi)大量的磁盤IO, 映像查詢效率。此時可以只將字符串的一部分前綴,建立索引,這樣可以大大節(jié)約索引空間,從而提升索引效率。
1)語法
create index idx_xxxx on table_name(column(n));
示例:
為tb_user表的email字段,建立長度為5的前綴索引
create index idx_email_5 tb_user(email(5))
2).前綴長度
可以根據(jù)索引的選擇性決定,而選擇性是指不重復(fù)的索引值(基數(shù))和數(shù)據(jù)表的記錄總數(shù)的比值,索引選惡行越高查詢效率越高,唯一索引的選擇性是1,這是最好的索引選擇性,性能也是最好的。
select count(distinct email) / count(*) from tb_user ;
select count(distinct substring(email,1,5)) / count(*) from tb_user ;
3)前綴索引的查詢流程
8.6.8 單列索引與聯(lián)合索引
單列索引:一個索引值包含單個列。
聯(lián)合索引:一個索引值包含多個列。
tb_user表中目前的索引情況:
explain select id,phone,name from tb_user where phone
= '17799990010' and name = '韓信';
and連接的兩個字段phone、name上都是單列索引的,但是最終mysql只會選擇一個索引,只能走一個字段的索引,此時是回表查詢。
創(chuàng)建一個phone和name字段的聯(lián)合索引執(zhí)行計(jì)劃:
create unique index idx_phone_name on tb_user(phone, name);
查詢時,走了聯(lián)合索引,在聯(lián)合索引中包含phone、name的信息,在葉子節(jié)點(diǎn)下面掛的是對應(yīng)的主鍵id,所以無需回表查詢。
8.7 索引設(shè)計(jì)原則
1)針對數(shù)據(jù)量較大,且查詢比較頻繁的表建立索引
2)針對于常作為查詢條件(where)、排序(order by)、分組(group by)操作的字段建立索引
3)盡量選擇區(qū)分度高的列作為索引,盡量建立唯一索引,區(qū)分度越高,使用索引的效率越高
4)如果是字符串的字段,字段的長度較長,可以針對字段的特點(diǎn),建立前綴索引。
5)盡量使用聯(lián)合索引,減少單列索引,查詢時,聯(lián)合索引很多時候可以覆蓋索引,節(jié)省存儲空間,避免回表,提高查詢效率.
6)要控制索引的數(shù)量,索引并不是多多益善,索引越多,維護(hù)索引結(jié)構(gòu)的代價(jià)也就越大,會影響增刪改的效率.
7)如果索引列不能存儲NULL值,請?jiān)趧?chuàng)建表時使用NOT NULL約束它。當(dāng)優(yōu)化器知道每列是否包含NULL值時,它可以更好的確定哪個索引最有效的用于查詢
9.SQL優(yōu)化
9.1 插入數(shù)據(jù)
9.1.1 insert
一次性插入多條數(shù)據(jù),三個優(yōu)化方面
insert into tb_test values(1,'tom');
insert into tb_test values(2,'cat');
insert into tb_test values(3,'jerry');
- 優(yōu)化方案一
批量插入數(shù)據(jù)
insert into tb_test values(1, 'Tom'), (2, 'Cat'), (3, 'Jerry');
2)優(yōu)化方案二
手動控制事務(wù)
start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;
3)優(yōu)化方案三
主鍵順序插入,性能要高于亂序插入
主鍵亂序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
主鍵順序插入 : 1 2 3 4 5 7 8 9 15 21 88 89
9.1.2 大批量插入數(shù)據(jù)
大批量導(dǎo)入數(shù)據(jù)使用load命令
執(zhí)行命令:
-- 客戶端連接服務(wù)端時,加上參數(shù) -–local-infile
mysql –-local-infile -u root -p
-- 設(shè)置全局參數(shù)local_infile為1,開啟從本地加載文件導(dǎo)入數(shù)據(jù)的開關(guān)
set global local_infile = 1;
-- 執(zhí)行l(wèi)oad指令將準(zhǔn)備好的數(shù)據(jù),加載到表結(jié)構(gòu)中
load data local infile '/root/sql1.log' into table tb_user fields
terminated by ',' lines terminated by '\n' ;
示例演示:
A.創(chuàng)建表結(jié)構(gòu)
create table `tb_user`(`id` int(11) not null auto_increment,`username` varchar(50) not null,`password` varchar(50) not null,`name` varchar(20) not null,`birthday` date default null,`sex` char(1) default null,primary key(`id`),unique key `unique_user_username` (`username`)
) engine = innodb default charset = utf8;
B.創(chuàng)建參數(shù)
-- 客戶端連接服務(wù)器,加上參數(shù) --local-infile
mysql --local-infile -u root -p
-- 設(shè)置全局參數(shù)local_infile,開啟從本地加載文件導(dǎo)入數(shù)據(jù)的開關(guān)
set global local_infile = 1;
C.load加載數(shù)據(jù)
load data local infile '/root/sql/load_user_100w_sort.sql' into table
tb_usertest fields terminated by ',' lines terminated by '\n';
9.2 主鍵優(yōu)化
主鍵順序插入的性能高于亂序插入的,具體原因,主鍵該如何設(shè)計(jì)
1)數(shù)據(jù)組織方式
在InnoDB存儲引擎中,表數(shù)據(jù)都是根據(jù)主鍵順序組織存放的,這種方式的表稱為索引組織表
行數(shù)據(jù),都是存儲在聚集索引的葉子節(jié)點(diǎn)上的,InnoDB存儲邏輯結(jié)構(gòu)圖:
在InnoDB引擎中,數(shù)據(jù)行記錄在邏輯結(jié)構(gòu)page頁中的,而每一個頁的大小是固定的,默認(rèn)16k。一個頁中所存儲的行也是有限的,如果插入的數(shù)據(jù)行row在該頁存儲不小, 將會存儲到下一頁,頁與頁之間通過指針連接。
2)頁分裂
頁可以為空,也可以填充一半,也可以填充100%,每個頁面2-N行數(shù)據(jù)(如果一行數(shù)據(jù)過大,會溢出),根據(jù)主鍵排列。
A.主鍵順序插入效果
①.從磁盤中申請頁,主鍵順序插入
②.第一頁沒有滿,繼續(xù)往第一頁插入
③.當(dāng)?shù)谝粋€頁寫滿之后,再寫入第二個頁,頁與頁之間會通過指針連接
④.當(dāng)?shù)诙搶憹M了,再往第三頁寫入
B.主鍵亂序插入效果
①.假如1#,2#頁都已經(jīng)寫滿了,存放如圖所示的數(shù)據(jù)
②.此時再插入id為50的記錄
索引結(jié)構(gòu)是有順序的。按照順序,應(yīng)該存儲在47之后。
但是47所在的1#頁,已經(jīng)寫滿了,存儲不了50對應(yīng)的數(shù)據(jù)了,那么此時會開辟一個新的頁3#。
但是并不會直接獎50存入3#頁,而是會將1#頁后一半的數(shù)據(jù),移動到3#頁,插入50.
移動數(shù)據(jù),并插入id為50的數(shù)據(jù)之后,那么此時,這三個頁面之間的數(shù)據(jù)是有問題的。1#的下一個頁,應(yīng)該是3#,3#的下一頁是2#。所以,需要重新設(shè)置鏈表指針。
上述的這種現(xiàn)象,稱之為“頁分裂”,是比較耗費(fèi)時間的操作。
3)頁合并
目前表中的已有數(shù)據(jù)的索引結(jié)構(gòu)(葉子節(jié)點(diǎn))如下:
當(dāng)刪除數(shù)據(jù)時,實(shí)際上記錄沒有被物理刪除,只是記錄被標(biāo)記(flaged)為刪除并且他的空間變得允許被其他記錄聲明使用。
繼續(xù)刪除2#的數(shù)據(jù)記錄
當(dāng)頁中刪除的記錄達(dá)到MENGE_THRESHOLD(默認(rèn)為頁的50%),InnoDB會開始尋找最靠近的頁(前或后)看看是否可以將兩個頁合并以優(yōu)化空間使用.
刪除數(shù)據(jù),并將頁合并之后,再次插入新的數(shù)據(jù)21,則直接插入3#頁
上述現(xiàn)象稱之為“頁合并”
MERGE_THRESHOLD:合并頁的閾值,可以自己設(shè)置,在創(chuàng)建表或者創(chuàng)建索引時指定。
4).索引設(shè)計(jì)原則
- 滿足業(yè)務(wù)需求的情況下,盡量降低主鍵的長度.
- 插入數(shù)據(jù)時,盡量選擇順序插入,選擇使用AUTO_INCREMENT自增主鍵
- 盡量不要選擇UUID做主鍵或者是其他自然主鍵,如身份證號
- 業(yè)務(wù)操作時,避免對主鍵的修改.
9.3 order by優(yōu)化
MySQL的排序,有兩種方式:
Using filesort:通過表的索引或全表掃描,讀取滿足條件的數(shù)據(jù)行,然后再排序緩沖區(qū)sortbuffer中完成排序操作,所有不是通過索引直接返回排序結(jié)果的排序都叫FileSort排序
Using index:通過有序索引順序掃描返回有序數(shù)據(jù),這種情況即為using index,不需要額外排序,操作效率高。
對于以上的兩種排序方式,Using index的性能高,而Using filesort性能低,我們再優(yōu)化排序操作時,盡量要優(yōu)化為Using index
測試:
A、數(shù)據(jù)準(zhǔn)備
刪除tb_user建立的部分索引
drop index idx_user_phone on tb_user;
drop index idx_user_phone_name on tb_user;
drop index idx_user_name on tb_user;
B、執(zhí)行排序SQL
explain select id, age, phone from tb_user order by age;
explain select id, age, phone from tb_user order by age, phone;
由于age,name都沒有索引,出現(xiàn)Using filesort,排序性能比較低
C、創(chuàng)建索引
create index idx_user_age_phone_aa on tb_user(age, phone);
D、創(chuàng)建索引后,根據(jù)age,phone進(jìn)行升序排序
explain select id, age, phone from tb_user order by age;
explain select id, age, phone from tb_user order by age, phone;
建立索引之后,再次進(jìn)行排序查詢,就由原來的Using filesort, 變?yōu)榱?Using index,性能 就是比較高的了。
E、創(chuàng)建索引后,根據(jù)age,phone進(jìn)行降序排序
explain select id, age, phone from tb_user order by age desc, phone desc;
Backward index scan 反向索引掃描
MySQL8支持降序索引
F、根據(jù)phone、age進(jìn)行升序排序,phone在前,age在后
explain select id, age, phone from tb_user order by phone, age;
不遵循最左前綴法則就會出現(xiàn)Using filesort
F、根據(jù)age,phone進(jìn)行降序一個升序,一個降序
explain select id,age,phone from tb_user order by age asc, phone desc;
一個升序,一個降序,此時 就會出現(xiàn)Using filesort。
為了解決上述問題,我們可以創(chuàng)建一個索引,這個聯(lián)合索引中age升序排序,phone倒序排列
G、創(chuàng)建聯(lián)合索引(age升序排列,phone倒序排列)
create index idx_user_age_phone_phone_aa on tb_user(age asc, phone desc);
H、然后再次執(zhí)行如下SQL
explain select id,age,phone from tb_user order by age asc, phone desc;
升序/降序聯(lián)合索引結(jié)構(gòu)圖示:
order by優(yōu)化原則:
A、根據(jù)排序字段建立合適的索引,多字段排序時,也遵循最左前綴法則。
B、盡量使用覆蓋索引。
C、多字段排序,一個升序一個降序,此時需要注意聯(lián)合索引在創(chuàng)建時的規(guī)則(ASC/DESC)
D、如果不可避免的出現(xiàn)filesort,大數(shù)據(jù)量排序時,可以適當(dāng)增大排序緩沖區(qū)大小sort_buffer_size(默認(rèn)256k)
9.4 group by優(yōu)化
分組操作主要看索引對于分組操作的影響
首先我們先將tb_user表的索引全部刪除掉
drop index idx_user_pro_age_sta on tb_user;
drop index idx_email_5 on tb_user;
drop index idx_user_age_phone_aa on tb_user;
drop index idx_user_age_phone_ad on tb_user;
在沒有索引的情況下執(zhí)行查詢計(jì)劃:
explain select profession , count(*) from tb_user group by profession ;
針對profession,age,status創(chuàng)建一個聯(lián)合索引。
create index idx_user_pro_age_sta on tb_user(profession, age, status);
緊接著,再執(zhí)行前面相同的SQL查看執(zhí)行計(jì)劃
explain select profession , count(*) from tb_user group by profession ;
執(zhí)行下面的分組SQL:
explain select profession,count(*) from tb_user group by profession, age;
explain select age,count(*) from tb_user group by age;
僅僅根據(jù)age分組,就會出現(xiàn)Using tempoary;而如果是根據(jù)profession,age兩個字段相同分組,則不會出現(xiàn)Using temporary,原因是因?yàn)閷τ诜纸M操作,在聯(lián)合索引中,也是符最左前綴法則
在分組操作中,性能優(yōu)化:
A、通過索引來提高效率
B、索引的使用要滿足最左前綴法則
9.5 limit優(yōu)化
在數(shù)據(jù)量比較大時,如果進(jìn)行l(wèi)imit分頁查詢,在查詢時,越往后,分頁查詢效率越低
select * from tb_sku limit 0, 10;
select * from tb_sku limit 1000000, 10;
select * from tb_sku limit 5000000, 10;
select * from tb_sku limit 9000000, 10;
測試發(fā)現(xiàn):越往后,分頁效率越低,這就是分頁查詢的問題所在
因?yàn)?#xff0c;當(dāng)在進(jìn)行分頁查詢時,如果執(zhí)行l(wèi)imit2000000,10,此時需要MySQL排序前2000010記錄,僅僅返回2000000-2000010記錄,查詢排序的代價(jià)非常大
優(yōu)化思路:一般分頁查詢時,通過創(chuàng)建覆蓋索引能夠比較好地提升性能,可以通過覆蓋索引加子查詢形式進(jìn)行優(yōu)化
explain select * from tb_sku t, (select id from tb_sku order byid limit 2000000, 10) a where t.id = a.id;
9.6 count優(yōu)化
9.6.1 概述
select count(*) from tb_user;
數(shù)據(jù)量很大執(zhí)行count操作時,是非常耗時的。
- MyISAM引擎把一張表的總行數(shù)存在磁盤上,因此執(zhí)行count(*)的時候會直接返回這個數(shù)字,效率很高,但是如果是帶條件的count,MyISAM也慢。
- InnoDB引擎就麻煩了,他執(zhí)行count(*)的時候,需要把數(shù)據(jù)一行行地從引擎里面讀取出來,然后累積計(jì)數(shù)。
如果說要大幅度提升InnoDB表的count效率,主要的優(yōu)化思路:自己計(jì)數(shù)(可以借助于redis這樣的數(shù)據(jù)庫進(jìn)行,但是如果是帶條件的count又比較麻煩了)
9.6.2 count用法
count()是一個聚合函數(shù),對于返回的結(jié)果集,一行行地判斷,如果count函數(shù)的參數(shù)不是NULL,累計(jì)值就加1,否則不加,最后返回累計(jì)值。
用法:count(*)、count(主鍵)、count(字段)、count(數(shù)字)
count用法 | 含義 |
---|---|
count(主鍵) | InnoDB引擎會遍歷整張表,把一行的主鍵id值取出來,返回給服務(wù)層。服務(wù)層拿到主鍵后,直接按行進(jìn)行累加(主鍵不可能為null) |
count(字段) | 沒有not null約束:InnoDB引擎遍歷整張表把每一行的字段值都取出來,返回給服務(wù)層,服務(wù)層判斷是否為null,不為null,計(jì)數(shù)累加。 又not null 約束:InnnoDB引擎會便利整張表把每一行的字段都取出來,返回給服務(wù)層,直接按行進(jìn)行累加。 |
count(數(shù)字) | InnoDB引擎遍歷整張表但不取值,服務(wù)層對于返回的每一行,放一個數(shù)字“1”進(jìn)去,直接按行進(jìn)行累加 |
count(*) | InnoDB引擎并不會把全部字段取出來,而是專門做了優(yōu)化,不取值,服務(wù)層直接按行進(jìn)行累加 |
按照效率排序的話,count(字段) < count(主鍵 id) < count(1) ≈ count(),所以盡 量使用 count()。
9.7 update優(yōu)化
我們主要需要注意一下update語句執(zhí)行時的注意事項(xiàng)
update course set name = 'javaEE' where id = 1;
當(dāng)我們在執(zhí)行刪除的SQL語句時,會鎖定id為1的這一行數(shù)據(jù),然后事務(wù)提交之后,行鎖釋放。
但是當(dāng)我們在執(zhí)行如下SQL時
update course set name = 'SpringBoot' where name = 'PHP';
當(dāng)我們開啟多個事務(wù),再執(zhí)行上述SQL時,我們發(fā)現(xiàn)行鎖升級為表鎖,導(dǎo)致update語句的性能大大降低
InnoDB的行鎖是針對索引加的鎖,不是針對記錄加的鎖,并且該索引不能失效,否則會從行鎖升級為表鎖。
10.視圖/存儲過程/觸發(fā)器
數(shù)據(jù)準(zhǔn)備
create table student(id int auto_increment comment '主鍵ID' primary key,name varchar(10) null comment '姓名',no varchar(10) null comment '學(xué)號'
)comment '學(xué)生表';INSERT INTO student (name, no) VALUES ('黛綺絲', '2000100101');
INSERT INTO student (name, no) VALUES ('謝遜', '2000100102');
INSERT INTO student (name, no) VALUES ('殷天正', '2000100103');
INSERT INTO student (name, no) VALUES ('韋一笑', '2000100104');create table course(id int auto_increment comment '主鍵ID' primary key,name varchar(10) null comment '課程名稱'
)comment '課程表';INSERT INTO course (name) VALUES ('Java');
INSERT INTO course (name) VALUES ('PHP');
INSERT INTO course (name) VALUES ('MySQL');
INSERT INTO course (name) VALUES ('Hadoop');create table student_course(id int auto_increment comment '主鍵' primary key,studentid int not null comment '學(xué)生ID',courseid int not null comment '課程ID',constraint fk_courseid foreign key (courseid) references course (id),constraint fk_studentid foreign key (studentid) references student (id)
)comment '學(xué)生課程中間表';INSERT INTO student_course (studentid, courseid) VALUES (1, 1);
INSERT INTO student_course (studentid, courseid) VALUES (1, 2);
INSERT INTO student_course (studentid, courseid) VALUES (1, 3);
INSERT INTO student_course (studentid, courseid) VALUES (2, 2);
INSERT INTO student_course (studentid, courseid) VALUES (2, 3);
INSERT INTO student_course (studentid, courseid) VALUES (3, 4);
10.1 視圖
10.1.1介紹
視圖是一種虛擬存在的表,視圖中的數(shù)據(jù)并不在數(shù)據(jù)庫中真實(shí)存在,行和列數(shù)據(jù)來自定義視圖的查詢中的表,并且使用視圖時動態(tài)生成。
視圖只保存了查詢的SQL邏輯,不保存查詢的結(jié)果,所以沃恩創(chuàng)建視圖的時候,主要的工作就落在這條SQL查詢語句上
10.1.2 語法
1)創(chuàng)建
create [or replace] view 視圖名稱[(列表名稱)] AS select 語句[with[cascaded | local] check option];
- 查詢
查看創(chuàng)建視圖語句: show create view 視圖名稱;
查看視圖數(shù)據(jù): select * from 視圖名稱……;
3)修改
方式一:
create [or replace] view 視圖名稱[(列名列表)] as select語句
[with [cascaded | local] check option]
方式二:
alter view 視圖名稱[(列名列表) as select語句 [with [cascaded | local] check option ]
- 刪除
drop view [if exists] 視圖名稱 [,視圖名稱] …
演示示例
-- 創(chuàng)建視圖
create or replace view stu_v_1 as
select id, name from student where id <= 10;-- 查詢視圖
show create view stu_v_1;select * from stu_v_1;
select * from stu_v_1 where id < 3;-- 修改視圖
create or replace view stu_v_1 as
select id, name, no from student where id <= 10;alter view stu_v_1 as
select id, name from student where id <= 10;-- 刪除視圖
drop view if exists stu_v_1;
能不能用視圖插入數(shù)據(jù)、更新數(shù)據(jù)?
create or replace view stu_v_1 as
select id, name from student where id <= 10;select * from stu_v_1;insert into stu_v_1 values(6, 'Tom');insert into stu_v_1 values(17, 'Tom22');
id為6和17的數(shù)據(jù)都是可以成功插入的。但是我們執(zhí)行查詢,查詢出來的數(shù)據(jù),卻沒有id為17的記錄。
因?yàn)槲覀冊趧?chuàng)建視圖時,指定的條件為id<=10, id為17的數(shù)據(jù),是不符合條件的,所以沒有查詢出來,但是這條數(shù)據(jù)確實(shí)已經(jīng)成功的插入到基表中。
如果定義視圖時制定了條件,再執(zhí)行插入、修改、刪除數(shù)據(jù)必須滿足條件才能操作,否則不能操作
10.1.3 檢查選項(xiàng)
當(dāng)使用with check option子句創(chuàng)建視圖時,MySQL會通過視圖檢查正在更改的每個行,列如,插入,更新,刪除,以使其符合視圖的定義。MySQL允許基于另外一個視圖創(chuàng)建視圖,它還會檢查依賴視圖中的規(guī)則以保持一致性。為了確定插件的范圍,mysql提供了兩個選項(xiàng):cascaded 和 local,默認(rèn)值為cascaded
1)cascaded 級聯(lián)
v2視圖是基于v1視圖的,如果在v2視圖創(chuàng)建的時候指定了檢查選項(xiàng)為cascaded,但是v1視圖創(chuàng)建時并未指定檢查選項(xiàng)。則在執(zhí)行時,不僅會檢查v2,還會級聯(lián)檢查v2的關(guān)聯(lián)視圖v1.
2)local 本地
比如,v2視圖是基于v1視圖的,如果在v2視圖創(chuàng)建的時候指定了檢查選項(xiàng)local,但是v1視圖未指定檢查選項(xiàng)。則在執(zhí)行檢查時,只會檢查v2,不會檢查v2的關(guān)聯(lián)視圖。
10.1.4 視圖的更新
要使視圖可更新,視圖中的行與基礎(chǔ)表中的行之間必須存在一對一的關(guān)系。如果視圖包含以下任何一項(xiàng),則該視圖不可更新:
A、聚合函數(shù)或窗口函數(shù)(sum()、min()、max()、count()等)
B、distinct
C、group by
D、having
E、union或者 union all
實(shí)例演示:
create view stu_v_count as select count(*) from student;
上述視圖中,只有單行單列的數(shù)據(jù),如果我們對這個視圖進(jìn)行更新或插入,將會報(bào)錯
insert into stu_v_count values(10);
10.1.5視圖作用
1)、簡單
視圖不僅可以簡化用戶對數(shù)據(jù)的理解,也可以簡化其他的操作,那些經(jīng)常使用的查詢可以被定義未視圖,從而使得用戶不必為以后的操作每次指定全部條件。
2)、安全
數(shù)據(jù)庫可以授權(quán),但不能授權(quán)到數(shù)據(jù)庫特定行和特定列上。通過視圖用戶只能查詢和修改他們所能見到的數(shù)據(jù)
3)數(shù)據(jù)獨(dú)立
試圖可以幫助用戶屏蔽真實(shí)表結(jié)構(gòu)變化帶來的影響。
10.1.6 案例
1)、為保障數(shù)據(jù)表的安全性,開發(fā)人員在操作tb_user表時,只能看到用戶的基本字段,屏蔽手機(jī)號和郵箱兩個字段
create view tb_user_view as select id, name, profession, age, gender,
status, createtime from tb_user;select * from tb_user_view;
2).查詢每個學(xué)生所選修的課程(三張表聯(lián)查)
create view tb_stu_course_view as select s.name student_name, s.no student_no,
c.name course_name from student s, student_course sc, course c where s.id
= sc.studentid and sc.courseid = c.id;select * from tb_stu_course_view;
10.2存儲過程
10.2.1 介紹
存儲過程是事先經(jīng)過編譯并存儲在數(shù)據(jù)庫的一段SQL語句的集合,調(diào)用存儲過程可以簡化應(yīng)用開發(fā)人員的很多工作,減少數(shù)據(jù)在數(shù)據(jù)庫和應(yīng)用服務(wù)器之間的傳輸,對于提高數(shù)據(jù)處理的效率是有好處的。存儲過程思想簡單,就是數(shù)據(jù)庫SQL數(shù)據(jù)層面的代碼封裝與重用。
- 封裝,復(fù)用:把某一業(yè)務(wù)SQL封裝在存儲過程中,需要用到的時候直接調(diào)用即可
- 可以接受參數(shù),也可以返回?cái)?shù)據(jù):在存儲過程中可以接受參數(shù)也可以接受返回值
- 減少網(wǎng)絡(luò)交互,效率提升:如果涉及多條SQL,每執(zhí)行一次都是一次網(wǎng)絡(luò)傳輸。封裝在存儲過程中,我們只需要網(wǎng)路交互一次就可以了。
10.2.2 基本語法
- 創(chuàng)建
create procedure 存儲過程([參數(shù)列表])
begin -- SQL語句
end;
- 調(diào)用
call 名稱 ([ 參數(shù) ]);
3)查看
select * from information_schema.routines where routine_schema
='xxx'; -- 查詢指定數(shù)據(jù)庫的存儲過程及狀態(tài)信息
4)刪除
drop procedure [if exists] 存儲過程名稱;
注意
在命令行中,知心話創(chuàng)建存儲國臣的SQL時,需要通過關(guān)鍵字delimiter指定SQL語句的結(jié)束語
演示示例:
-- 存儲過程基本語法
-- 創(chuàng)建
create procedure p1()
begin select count(*) from student;
end;-- 調(diào)用
call p1();-- 查看
select * from information_schema.routines
where routine_schema = 'leo';show create procedure p1;-- 刪除
drop procedure if exists p1;
10.2.3 變量
在MySQL中變量分為三種類型:系統(tǒng)變量、用戶自定義變量、局部變量。
10.2.3.1 系統(tǒng)變量
系統(tǒng)變量是MySQL服務(wù)器提供,不是用戶定義的,屬于服務(wù)器層面。分為全局變量(GLOBAL)、會話變量(SESSION)。
1)查看系統(tǒng)變量
show [session | global] variables; -- 查看所有系統(tǒng)變量
show [session | global] variables like '......'; -- 可以通過
like模糊匹配的方式查找變量
select @@[session | global] 系統(tǒng)變量; -- 查看指定變量的值
2)設(shè)置系統(tǒng)變量
set [session | global] 系統(tǒng)變量名 = 值;
set @@[sesssion | global] 系統(tǒng)變量名 = 值;
注意
如果沒有你指定session/global,默認(rèn)是session, 會話變量
mysql服務(wù)啟動之后,所設(shè)置的全局參數(shù)會失效,要想不失效,可以在/etc/my.cnf中配置
A、全局變量(global):全局變量針對所有的會話。
B、會話變量(session):會話變量針對單個會話,在兩以惡搞會話窗口就不生效了
演示示例:
-- 查看系統(tǒng)變量
show session variables;show session variables like 'auto%';
show global variables like 'auto%';select @@global.autocommit;
select @@session.autocommit;-- 設(shè)置系統(tǒng)變量
set session autocommit = 1;insert into course(id, name) values(6, 'ES');set global autocommit = 0;set @@global.autocommit;
10.2.3.2 用戶定義變量
用戶定義變量 是用戶根據(jù)需要自己定義的變量,用戶變量不同提前聲明,在用的時候直接使用“@變量名”使用就可以,起作用域?yàn)楫?dāng)前連接。
1)賦值
方式一:
set @var_name = expr [, @var_name = ecpr] …;
set @var_name := expr [, @var_name := expr]…;
方式二:
select @var_name := expr [, @var_name := expr]…;
select 字段名 insert @var_name from 表名;
- 使用
select @var_name;
注意: 用戶定義的變量無需對其進(jìn)行聲明或初始化,只不過獲取到的值為NULL。
演示示例:
-- 賦值
set @myname = 'leo';
set @myage := 10;
set @mygender := '男', @myhobby := 'java';select @mycolor := 'red';
select count(*) into @mycount from tb_user;-- 使用
select @myname, @myage, @mygender, @myhobby;select @mycolor , @mycount;select @abc;
10.2.3.3 局部變量
局部變量 是根據(jù)需要定義的在局部生效的變量,訪問之前,需要declare聲明??捎米鞔鎯^程內(nèi)的局部變量和輸入變量,局部變量的范圍是在其內(nèi)聲明的begin…end塊。
1)聲明
declare 變量名 變量類型 [default ...];
變量類型就是數(shù)據(jù)庫字段類型:int、bigint、char、varchar、date、time等。
2)賦值
set 變量名 = 值;
set 變量名 := 值;
select 字段名 into 變量名 from 表名 ...;
演示示例:
-- 聲明局部變量 -declare
-- 賦值
create procedure p2()
begin declare stu_count int default 0;select count(*) into stu_count from student;select stu_count;
end;call p2();
10.2.4 if
1)介紹
if用于用于條件判斷,具體的語法結(jié)構(gòu)為:
if 條件1 then
……
elseif 條件2 then -- 可選
……
else -- 可選
……
end if;
2)案例
根據(jù)定義的分?jǐn)?shù)score變量,判定當(dāng)前分?jǐn)?shù)對應(yīng)的等級。
- score >= 85分,等級為優(yōu)秀。
- score >= 60分 且 score < 85分,等級為及格。
- score < 60分,等級為不及格。
create procedure p3()
begindeclare score int default 58;declare result varchar(10);if score >= 85 thenset result := '優(yōu)秀';elseif score >= 60 thenset result := '及格';elseset result := '不及格';end if;select result;
end;
call p3();
DROP PROCEDURE IF EXISTS p3;
10.2.5 參數(shù)
參數(shù)的類型,主要分為以下三種: in、out、inout,具體的含義如下:
類型 | 含義 | 備注 |
---|---|---|
in | 該類參數(shù)作為輸入,也就是需要調(diào)用時傳入值 | 默認(rèn) |
out | 該參數(shù)作為輸出,也就是該參數(shù)可以作為返回值 | |
inout | 既可以作為輸入?yún)?shù),也可以作為輸出參數(shù) |
用法:
create procedure 存儲過程名稱([in/out/inout 參數(shù)名 參數(shù)類型])
begin-- SQL語句
end;
- 案例一
根據(jù)傳入?yún)?shù)score,判定當(dāng)前分?jǐn)?shù)對應(yīng)的分?jǐn)?shù)等級,并返回。
score >= 85分,等級為優(yōu)秀。
score >= 60分 且 score < 85分,等級為及格。
score < 60分,等級為不及格。
create procedure p4(in score int, out result varchar(10))
beginif score >= 85 thenset result := '優(yōu)秀';elseif score >= 60 thenset result := '及格';else set result := '不及格';end if;
end;call p4(18, @result);
select @result;
- 案例二:
將傳入的200分制的分?jǐn)?shù),進(jìn)行換算,換算成百分制,然后返回。
create procedure p5(inout score double)
beginset score := score * 0.5;
end;set @score = 198;
call p5(@score);select @score;
10.2.6 case
1)語法:
語法一:
-- 含義:當(dāng)case_value的值為when_value1,執(zhí)行statement_list1,當(dāng)值為
when_value2時,執(zhí)行statement_list2,否則就執(zhí)行statement_list
case case_valuewhen when_values then statement_list1[when when_value2 then statement_list2]...[else statement_list]
end case;
語法二:
casewhen search_condition1 then statement_list1[when search_condition1 then statement_list2]...[else statement_list]
end case;
2)案例
根據(jù)傳入的月份,判定月份所屬的季節(jié)。
1-3月份,為第一季度 4-6月份,為第二季度 7-9月份,為第三季度 10-12月份,為第四季度
create procedure p6(in month int)
begindeclare result varchar(10);casewhen month >= 1 and month <= 3 then set result := '第一季度';when month >= 4 and month <= 6 then set result := '第二季度';when month >= 7 and month <= 9 then set result := '第三季度';when month >= 10 and month <= 12 then set result := '第四季度';else set result := '非法參數(shù)';end case;select concat('您輸入的月份為: ', month, ', 所屬的季度為: ', result);
end;call p6(16);
判斷多個條件可以用and或or來連接
10.2.7 while
- 語法:
while 條件 dosql邏輯...
end while;
2)案例:
計(jì)算從1累加到n
create procedure p7(in n int)
begindeclare total int default 0;while n > 0 doset total := total + n;set n := n - 1;end while;select total;
end;call p7(100);
10.2.8 repeat
- 語法:
repeat是具有條件的循環(huán)控制語句,當(dāng)滿足until生命條件的時候,則退出循環(huán)。
repeatSQL邏輯util 條件
end repeat;
- 案例:
計(jì)算從1累加到n的值,n為傳入的參數(shù)值。(使用repeat實(shí)現(xiàn))
create procedure p8(in n int)
begindeclare total int default 0;repeatset total := total + n;set n := n - 1;until n <= 0end repeat;select total;
end;call p8(101);
call p8(100);
10.2.9 loop
1)語法:
loop實(shí)現(xiàn)簡單的循環(huán),如果不在SQL邏輯中增加退出循環(huán)的條件,可以用來實(shí)現(xiàn)簡單的死循環(huán)。loop可以配合兩個語句使用:
leave:配合循環(huán)使用,退出循環(huán)
iterate:必須在循環(huán)中,作用時跳過當(dāng)前循環(huán)剩下的語句,直接進(jìn)入下一次循環(huán)
[begin_label:] loopSQL邏輯...
end loop [end_label];
leave label; -- 退出指定標(biāo)記的循環(huán)體
iterate label -- 直接進(jìn)入下一次循環(huán)
上述語法中出現(xiàn)的begin_label, end_label, label 指的是我們所自定義的標(biāo)記
- 案例一
計(jì)算從1累加到n的值, n為傳入的參數(shù)值.
create procedure p9(in n int)
begindeclare total int default 0;sum:loopif n <= 0 thenleave sum;end if;set total := total + n;set n := n - 1;end loop sum;select total;
end;
call p9(100);
- 案例二
計(jì)算從1到n之間的偶數(shù)累加的值,n為傳入的參數(shù)值。
create procedure p10(in n int)
begindeclare total int default 0;sum:loopif n <= 0 thenleave sum;end if;if n % 2 = 1 thenset n := n - 1;end if;set total := total + n;set n := n - 1;end loop sum;select total;
end;call p10(100);
10.2.10 游標(biāo)
1)語法:
游標(biāo)是用來存儲查詢結(jié)果集的數(shù)據(jù)類型,在存儲過程和函數(shù)中可以使用游標(biāo)對結(jié)果集進(jìn)行循環(huán)處理。游標(biāo)的使用包括游標(biāo)的聲明、open、fetch和close,其語法分別如下。
A、聲明游標(biāo)
declare 游標(biāo)名稱 cursor for 查詢語句;
B、打開游標(biāo)
open 游標(biāo)名稱;
C、獲取游標(biāo)記錄
fetch 游標(biāo)名稱 into 變量 [, 變量];
D、關(guān)閉游標(biāo)
close 游標(biāo)名稱;
2)案例
根據(jù)傳入的參數(shù)uage,來查詢用戶表tb_user中,所有的用戶年齡小于等于uage的用戶姓名(name)和專業(yè)(profession),并將用戶的姓名和專業(yè)插入到所創(chuàng)建的一張新表(id, name, profession)中。
-- 邏輯:
-- A.聲明游標(biāo),存儲查詢結(jié)果集
-- B.準(zhǔn)備:創(chuàng)建表結(jié)構(gòu)
-- C.開啟游標(biāo)
-- D.獲取游標(biāo)中的記錄
-- E.插入數(shù)據(jù)到新表中
-- F.插入數(shù)據(jù)到新表中