本篇文章為我個(gè)人MySQL基礎(chǔ)知識(shí)復(fù)習(xí)筆記屏歹，記錄的比較粗略隐砸，旨在自己能看懂，所以各位讀者如果恰好看到本篇筆記并閱讀蝙眶，遇到不詳細(xì)的地方還是需要自己查更詳細(xì)的資料季希，見諒！Ｓ姆住式塌！

測(cè)試數(shù)據(jù)請(qǐng)見文章末尾

1、DQL 數(shù)據(jù)查詢語(yǔ)言（data query language）

1.1 函數(shù)

# 字符函數(shù)
## length（字符長(zhǎng)度）
select length('Hello World!'); # 12
## concat（拼接文本）友浸，
select concat('Hello', ' ', 'World', '!'); # Hello World!
## upper峰尝、lower 字母大小寫轉(zhuǎn)換
select upper('Hello World!'), lower('Hello World!'); # HELLO WORLD!, hello world!
## substr/substring（字符串截取，索引位置從1開始計(jì)算）
select substr('0123', 2), substr('0123', 2, 1); # 123, 1
## instr（返回substr在str中第一次出現(xiàn)的索引位置收恢，若不匹配返回0武学，索引從0開始）
select instr('0123', '1'), instr('0123', 4); # 2, 0# trim（去除收尾空格，字符串中間空格不會(huì)去除）
select trim(' Hello World! '), length(trim(' Hello World! ')); # Hello World!, 12
## lpad（長(zhǎng)度不夠指定長(zhǎng)度左邊填充自定字符派诬，長(zhǎng)度超出指定長(zhǎng)度去掉字符末尾部分）
select lpad('Hello World!', 14, '*'); # **Hello World!
## rpad（字符右邊填充）
select rpad('Hello World!', 14, '*'); # Hello World!**
## replace（替換指定字符）
select replace('Hello World!', '!', '?'); # Hello World?

# 數(shù)學(xué)函數(shù)
## round（四舍五入）
select round(3.1415), round(3.1415, 2); # 3, 3.14
## cell（向上取整）劳淆、floor（向下取整）
select ceil(3.14), floor(3.14); # 4, 3
## mod（取余）
select mod(10, 3), 10%3; # 1, 1

# 日期函數(shù)
## now()、current_timestamp（獲取當(dāng)前日期及時(shí)間）
select now(), current_timestamp; # 2022-01-08 18:58:41, 2022-01-08 18:58:41
## curdate()默赂、current_date（獲取當(dāng)前日期）
select curdate(), current_date; # 2022-01-08, 2022-01-08
## curtime()沛鸵、current_time（獲取當(dāng)前時(shí)間）
select curtime(), current_time; # 18:58:41，18:58:41
## year、month曲掰、date疾捍、hour、minute栏妖、second（提取指定時(shí)間字符串里面的年月日）
select year(now()), year('2020-01-08'); # 2022, 2022
## str_to_date（指定格式字符串轉(zhuǎn)換成時(shí)間乱豆，%Y：2022、%y：22吊趾、%m：01宛裕、%c：1、%d：08...）
select str_to_date('2020年01月08日', '%Y年%m月%d日'); # 2020-01-08
## date_format（時(shí)間轉(zhuǎn)換成指定格式的字符串论泛，%Y：2022揩尸、%y：22、%m：01屁奏、%c：1岩榆、%d：08...）
select date_format(now(), '%Y年%m(%c)月%d日 %H(%h):'); # 2022年01(1)月08日 19(07):
## datediff（求兩個(gè)日期間隔，前者-后者坟瓢，只能精確到天）
select datediff(now(), '2021-01-08');

# 統(tǒng)計(jì)函數(shù)
## sum（求和）勇边、avg（求平均值），只能處理數(shù)值類型
select sum(age), avg(age) from student;
## max折联、min粒褒、count（*代表某行只要有一列不為空就算，指定列名的話就統(tǒng)計(jì)指定列崭庸，空則不算）
select max(age), min(age), count(*) from student;

# 流程控制函數(shù)
## if（條件判斷）
select if(1=1, 'yes', 'no'), if(1=2, 'yes', 'no'); # yes, no
## ifnull（判斷是否為null）
select ifnull('Hello World!', 'is null'), ifnull(null, 'is null'); # Hello World!, is null

# 其他函數(shù)
## version（數(shù)據(jù)庫(kù)版本）怀浆、database（當(dāng)前數(shù)據(jù)庫(kù)）、user（當(dāng)前連接用戶）怕享、password（指定用戶密碼）
select version(), database(), user(), password('root'); # 5.7.24、test镰踏、root@218.108.217.138函筋、81F...

# 附加其他
## 取別名，可以用as奠伪，也可以用空格
select  name as 姓名, age 年齡 from student;
## 去重（distinct）
select distinct age from student;

1.2 查詢

# 條件查詢
  # 語(yǔ)法：select 查詢列/統(tǒng)計(jì)函數(shù) from 表名 where 條件
  # 條件表達(dá) <跌帐、>、=绊率、!=/<>谨敛、<=、>=滤否、 is [not] null
  # 邏輯表達(dá) &&或and脸狸、||或or、!或not
  # 模糊查詢
    # like（%匹配任意多個(gè)字符、_匹配一個(gè)字符）
    # between and（左閉右閉）
    # in（不支持通配符炊甲，例如 % 泥彤、_ ...）
select * from student where classId=1 or classId=2;

# 分組查詢
  # 語(yǔ)法：
  #      select 統(tǒng)計(jì)分組字段, 統(tǒng)計(jì)函數(shù) 
  #      from 表名 [where 條件] 
  #      group by 字段 
  #      [having 包含統(tǒng)計(jì)函數(shù)的字段]
select classId, count(*) count from student group by classId having count>1;

# 查詢結(jié)果排序
  # 語(yǔ)法：
  #      select 查詢列/統(tǒng)計(jì)函數(shù) 
  #      from 表名 [where 條件] 
  #      [group by 字段 having 包含統(tǒng)計(jì)函數(shù)的字段] 
  #      order by 字段 asc/desc
select * from student order by age desc;

# 分頁(yè)查詢
  # 語(yǔ)法：
  #      select 查詢列/統(tǒng)計(jì)函數(shù) 
  #      from 表名 
  #      [group by 字段 having 包含統(tǒng)計(jì)函數(shù)的字段] 
  #      [order by 字段 asc/desc] 
  #      limit [pos,] size
  # 注意：pos索引從0開始，可選卿啡，不寫默認(rèn)是從0開始吟吝，計(jì)算公式 limit (page-1)*size, size
select * from  student limit 2;
select * from  student limit 0,2;

# 多表連接查詢
## 內(nèi)連接（不加連接條件會(huì)產(chǎn)生笛卡爾效應(yīng)，加連接條件會(huì)查詢兩張表的**交集**）
### 92版等值連接颈娜，語(yǔ)法：select ... from 表1, 表2, ... where 條件 ...
select s.*, c.* from student s, class c where s.classId=c.id;
### 99版等值連接剑逃，語(yǔ)法：select from 表1 [inner] join 表2 on 連接條件 ...
select s.*, c.* from student s join class c on s.classId=c.id;
### 非等值連接
#### 查詢所有學(xué)生分?jǐn)?shù)所屬等級(jí)
select s.name, s.grade, gl.level from student s 
    join grade_level gl 
        on s.grade between gl.l_grade and gl.h_grade;
### 自連接（連接自己）

## 外連接（分主表和從表，left左邊為主表官辽，right右邊為主表蛹磺，不加where條件會(huì)查詢出主表的全部?jī)?nèi)容）
### 左外連接，語(yǔ)法：select from 主表名 left [outer] join 從表名 on 連接條件 ...
#### 查詢所有學(xué)生及所在班級(jí)
select s.id, s.name, s.classId, c.name from student s 
    left join class c on s.classId=c.id; 
#### 查詢沒(méi)有（沒(méi)合理）分配班級(jí)的學(xué)生
select s.id, s.name, s.classId, c.name from student s 
    left join class c on s.classId=c.id where c.id is null;
#### 查詢合理分配了班級(jí)的學(xué)生
select s.id, s.name, s.classId, c.name from student s 
    left join class c on s.classId=c.id where c.id is not null;
### 右外連接野崇，語(yǔ)法：select from 從表名 right [outer] join 主表名 on 連接條件 ...
#### 查詢所有學(xué)生及所在班級(jí)
select s.id, s.name, s.classId, c.name from class c 
    right join student s on s.classId=c.id;

# 子查詢
  # 子查詢位置可以放在select称开、from及where或having后面
## select 后面，僅支持標(biāo)量子查詢（一行一列）
#### 查詢每個(gè)班學(xué)生個(gè)數(shù)
select c.*, 
       (select count(*) from student s where s.classId=c.id) scount 
from class c;
## from 后面乓梨，支持表子查詢（多行多列）鳖轰，注意：from后面的表子查詢，必須起別名
#### 查詢每個(gè)班級(jí)平均成績(jī)水平
select t.classId, t.avg_grade, g.level 
from (select s.classId classId, avg(s.grade) avg_grade 
      from student s 
      group by s.classId) t 
    left join grade_level g 
      on t.avg_grade between g.l_grade and g.h_grade; 
## where/having 后面 支持標(biāo)量子查詢（一行一列）扶镀、列子查詢（多行一列）蕴侣、行子查詢（一行多列）
### 標(biāo)量子查詢（一行一列）
#### 查詢成績(jī)比Anne好的
select * from student where grade>(select grade from student where name='Anne');
### 列子查詢（多行一列）
#### 查看Bob老師所帶班級(jí)的全部學(xué)生
select * from student where classId in (select c.id from class c where teacher='Bob');
### 行子查詢（一行多列）
#### 查詢年級(jí)最小且成績(jī)最差的學(xué)生信息，有則輸出（注：有偶然性臭觉，年級(jí)小的成績(jī)不一定是最差）
select * from student where (age, grade)=(select min(age), min(grade) from student);

# 聯(lián)合查詢
  # 語(yǔ)法：查詢語(yǔ)句1 union [all] 查詢語(yǔ)句2
  # 特點(diǎn)：查詢列數(shù)一致昆雀；每個(gè)查詢語(yǔ)句的列的排序一致；union會(huì)去重蝠筑，顯示全部需要加上all
select id, name from student union select id, name from class;
select id from student union select id from class; # sql會(huì)自動(dòng)去重
select id from student union all select id from class; # 查詢?nèi)繑?shù)據(jù)狞膘，不去重

2、DML 語(yǔ)言（data manipulate language）

# 插入
## 方式一：insert into 表名 (列名...) values (值...) ...
insert into class (name, teacher) values ('五班', 'John');
insert into class (name, teacher) values ('六班', 'Bob'), ('七班', 'Bob');
## 方式二：insert into 表名 set 列名=值... ...
insert into class set name='八班', teacher='Bob';
## 支持子查詢的方式插入
insert into class select 10, '十班', 'Bob';
insert into class select 11, '十一班', 'Bob' union all select 12, '十二班', 'Bob'

# 更新
  # 語(yǔ)法：update 表名 set 列名=值... where ....
## 單表記錄修改
update class set teacher='John' where id=4;
## 多表記錄修改
#### 例如將未合理分配班級(jí)的學(xué)生分配到4班
update student s 
    left join class c on s.classId=c.id 
set s.classId=4 where c.id is null;

# 刪除
## delete 單表刪除
delete from class where id = 10;
## delete 配合limit使用什乙，限制刪除條數(shù)
delete from class where id > 9 limit 1;
## delete 連表刪除
#### 刪除那些還沒(méi)有分配學(xué)生的班級(jí)
delete c from class c left join student s on c.id=s.classId where s.id is null;
## delete 多表刪除
#### 刪除John老師所帶班級(jí)挽封，并且和所在班級(jí)的全部學(xué)生
delete c,s from class c join student s on c.id=s.classId where c.teacher='John';
## truncate 刪除指定表里面所有記錄，并重置索引臣镣，效率比delete高
truncate table class;
delete from class;

3辅愿、DDL 數(shù)據(jù)定義語(yǔ)言（data definition language）

3.1 庫(kù)和表的管理

# 庫(kù)管理
## 查看所有數(shù)據(jù)庫(kù)
show databases;
## 庫(kù)創(chuàng)建，if not exists 為可選
create database if not exists books;
## 指定使用的數(shù)據(jù)庫(kù)忆某，語(yǔ)法：use 庫(kù)名
use books;
#### 庫(kù)刪除点待，if exists 為可選
drop database if exists books;

# 表管理
## 查看該數(shù)據(jù)庫(kù)下的所有表
show tables;
## 表創(chuàng)建，語(yǔ)法：create table [if not exists book] 表名 (字段 類型 [長(zhǎng)度, 約束], 字段 類型 [長(zhǎng)度, 約束] ...)
create table if not exists book (id int, name varchar(20), author varchar(20));
## 表信息展示弃舒，語(yǔ)法：describe 表名
describe book;
## 修改類型和約束癞埠，語(yǔ)法：alter table book modify [column] 列名 新類型
alter table book modify name varchar(21);
## 修改列名或列名和類型同時(shí)修改，語(yǔ)法：alter table 表名 change [column] 舊名 新名 舊類型/新類型
alter table book change name book_name varchar(20);
## 添加新列，語(yǔ)法：alter table 表名 add [column] 字段 類型 [約束] [first/after 已有字段];
alter table book add price float;
alter table book add price float after id; # 新列放在id列后面
alter table book add price float first; # 新列放在最前面
## 刪除列燕差，語(yǔ)法：alter table 表名 drop [column] 列名
alter table book drop price;
## 修改表名遭笋，語(yǔ)法：alter table 表名 rename 新表名
alter table book rename books;
## 表復(fù)制，只復(fù)制結(jié)構(gòu)徒探，語(yǔ)法：create table 新表名 like 表名/庫(kù)名.表名
create table class_copy like class;
#### 表復(fù)制瓦呼，結(jié)構(gòu)加數(shù)據(jù)，語(yǔ)法：create table 新表名 select * from 舊表名
create table class_copy2 select * from class;
#### 復(fù)制表测暗，結(jié)構(gòu)加部分?jǐn)?shù)據(jù)央串，語(yǔ)法：create table 新表 select * from 舊表名 where ...
create table class_copy3 select * from class where id<2;
#### 復(fù)制表，部分結(jié)構(gòu)加數(shù)據(jù)碗啄，語(yǔ)法：create table 新表 select 需要復(fù)制的列名... from 舊表 where ...
create table class_copy4 select id, name from class;
#### 表刪除质和，語(yǔ)法：drop table [if exists] 表名
drop table if exists book;

3.2 常見列類型

# 常見數(shù)值類型
## 數(shù)值型，超過(guò)則填充臨界值
  # 類型                大小          注意
  # tinyint           1 byte
  # mediumint         2 byte
  # int/integer       4 byte
  # bigint            8 byte
  # float             4 byte
  # double            8 byte
  # decimal(m,d)      依賴m和d的值    如果m>d稚字，為m+2否則為d+2
## 字符型
  # 類型                大小              n取值（字符個(gè)數(shù)）                   注意
  # char(n)           0-255 byte        0-255饲宿，可省略，省略默認(rèn)為1       固定長(zhǎng)度字符串胆描，不夠后面補(bǔ)空格瘫想，取出會(huì)去掉尾部空格
  # varchar(n)        0-65535 byte      字符轉(zhuǎn)換成二進(jìn)制不能超過(guò)65535     可變長(zhǎng)度字符串
  # text              0-65535 byte                                    長(zhǎng)文本數(shù)據(jù)
  # blob              0-65535 byte                                    二進(jìn)制形式的長(zhǎng)文本數(shù)據(jù)
## 日期型
  # 類型                大小      范圍                                      注意
  # date              3 byte  [1000-01-01, 9999-12-31]
  # time              3 byte  [-838:59:59, 838:59:59]
  # year              1 byte  [1901, 2155]
  # datetime          8 byte  [1000-01-01 00:00:00, 9999-12-31 23:59:59]  不會(huì)根據(jù)機(jī)器時(shí)區(qū)變化
  # timestamp         4 byte  [1970-01-01 00:00:00, 2038-xx-xx xx:xx:xx]  存儲(chǔ)的是格林威治時(shí)間，取出時(shí)會(huì)根據(jù)系統(tǒng)時(shí)間進(jìn)行格式化

3.3 常見約束&標(biāo)識(shí)列

# 常見約束
## not null（不能為空）昌讲，僅支持列級(jí)約束
create table if not exists book (id int not null);
## default（指定默認(rèn)值）国夜，僅支持列級(jí)約束
create table if not exists book (id int default 0);

## unique（唯一鍵，用于保證字段唯一短绸，字段可以為null）车吹，支持列級(jí)約束和表級(jí)約束（constraint 和別名可以省略）
create table if not exists book (id int unique);
# create table if not exists book (id int, constraint id unique(id)); # 不推薦使用表級(jí)約束
# create table if not exists book (id int, unique(id)); # 不推薦使用表級(jí)約束

## primary key（主鍵，非空和唯一的合集醋闭，但是只能指定一個(gè)字段為主鍵）窄驹，支持列級(jí)約束和表級(jí)約束（constraint 和別名可以省略）
create table if not exists book (id int primary key);
#### 單個(gè)主鍵，使用表級(jí)約束（不是很規(guī)范）
# create table if not exists book (id int, primary key(id)); 
#### 復(fù)合主鍵证逻，必須使用表級(jí)約束
create table if not exists book (id int, 
                                 name varchar(20), 
                                 auth_id int, 
                                 constraint book_pk primary key(name, auth_id));

## foreign key（外鍵）馒吴，僅支持表級(jí)約束，語(yǔ)法：... [constraint 別名] foreign key(本表字段) references 外表(關(guān)聯(lián)的外表中的字段)
create table if not exists book (id int primary key, authId int,
                                constraint fk_book_student foreign key(authId) references student(id));
create table if not exists book (id int primary key , authId int,
                                foreign key(authId) references person(id));

## 列級(jí)約束修改瑟曲，語(yǔ)法：alter table 表名 modify [column] 列名 類型 [新約束]
alter table book modify column id int unique;

# 標(biāo)識(shí)列
  # auto_increment（自增長(zhǎng)），只能用來(lái)標(biāo)識(shí)數(shù)值類型列
create table if not exists book (id int primary key auto_increment);
## 添加標(biāo)識(shí)列
alter table book modify id int auto_increment;
## 刪除標(biāo)識(shí)列
alter table book modify id int;

4豪治、TCL 事務(wù)（transaction control language）

# 特征：ACID
  # atomicity（原子性）
  # consistency（一致性）
  # isolation（隔離性）
  # durability（持久性）
# 分類
  # 隱式事務(wù) insert洞拨、update、delete
  # 顯示事務(wù)负拟，實(shí)現(xiàn)步驟如下
    # 1烦衣、set autocommit=0; 針對(duì)本次會(huì)話
    # 2、start transaction; 可選
    # 3、sql 語(yǔ)句
    # 4花吟、savepoint point; 可選秸歧，自己設(shè)置的回滾節(jié)點(diǎn)名
    # 5、sql 語(yǔ)句
    # 6衅澈、rollback to point; 可選键菱，有需要回滾的節(jié)點(diǎn)可以加上
    # 4、commit/rollback;
# 事務(wù)級(jí)別（mysql默認(rèn)事務(wù)級(jí)別 repeatable read）
  # 級(jí)別類型（由低到高）    # 是否可以避免臟讀   # 是否可以避免不可重復(fù)讀    # 是否可以避免幻讀
  # read uncommitted          NO                      NO                  NO
  # read committed            YES                     NO                  NO
  # repeatable read           YES                     YES                 NO
  # serializable              YES                     YES                 YES
  # ---------------------------------------------------------------------------
  # serializable性能差今布，一個(gè)事務(wù)操作需要等待別的事務(wù)結(jié)束经备，一般使用默認(rèn)事務(wù)級(jí)別（repeatable read）就可以了

# 查看mysql支持的存儲(chǔ)引擎
show engines;
# 查看當(dāng)前數(shù)據(jù)庫(kù)的默認(rèn)事務(wù)級(jí)別
select @@tx_isolation;
# 設(shè)置當(dāng)前會(huì)話的隔離級(jí)別，只針對(duì)當(dāng)前連接有效部默，語(yǔ)法：set session/global transaction isolation level 事務(wù)級(jí)別
set session transaction isolation level repeatable read;
# 查看當(dāng)前數(shù)據(jù)庫(kù)自動(dòng)提交是否打開（on 是開啟狀態(tài)）
show variables like 'autocommit';

# 事務(wù)具體使用示例
set autocommit=0; # 關(guān)閉當(dāng)前會(huì)話的事務(wù)自動(dòng)提交功能
start transaction; # 開始事務(wù)侵蒙，可選
insert into class (name, teacher) values ('九班', 'Bob');
savepoint rollback_tag; # 設(shè)置回滾節(jié)點(diǎn)，可選傅蹂，語(yǔ)法：savepoint 節(jié)點(diǎn)名
insert into class (name, teacher) values ('十班', 'Bob');
rollback to rollback_tag; # 回滾到指定節(jié)點(diǎn)纷闺，可選，語(yǔ)法：rollback to 節(jié)點(diǎn)名
commit; #可以 commit 提交份蝴，也可以 rollback 回滾全部操作

# 事務(wù)針對(duì)delete刪除犁功，數(shù)據(jù)可回滾
set autocommit=0;
start transaction;
delete from class;
rollback;

#### 事務(wù)針對(duì)truncate刪除，數(shù)據(jù)不可回滾
start transaction;
truncate table class;
rollback;

5搞乏、視圖（view）

# 定義：mysql 5.1 出現(xiàn)的新特性波桩，普通表一樣，可以查詢请敦，某些情況下可以增刪改（不推薦）镐躲，
#      但是他是一種虛擬存在的表，是一個(gè)邏輯表侍筛，本身并不包含數(shù)據(jù)萤皂，
#      可以理解成是一個(gè)子sql，存儲(chǔ)在mysql中匣椰，多條連接共享裆熙，重啟mysql依然存在
# 用處：可以展示基表的部分?jǐn)?shù)據(jù)
# 優(yōu)點(diǎn)：簡(jiǎn)單、安全禽笑、數(shù)據(jù)獨(dú)立入录，總而言之，使用視圖的大部分情況是為了保障數(shù)據(jù)安全性佳镜，提高查詢效率

# 視圖創(chuàng)建僚稿，語(yǔ)法：create [or replace] view 視圖名[(自取列名...)] as select語(yǔ)句;
#### 列名跟class表一樣（當(dāng)然這種視圖是無(wú)意義的）
create view class_view as select name, teacher from class; 
#### 選取class里面的部分列，并且列名自定義
create view class_view(v_name, v_teacher) 
    as select name, teacher from class;
#### 連表數(shù)據(jù)構(gòu)建一個(gè)view蟀伸，查詢出學(xué)生部分信息和其所在班級(jí)信息
create view student_view(id, name, class_name, class_teacher)
    as select s.id, s.name, c.name, c.teacher from student s 
        left join class c on s.classId=c.id;

# 視圖查詢蚀同，跟普通表查詢一樣
select * from class_view;
# 視圖修改缅刽，語(yǔ)法：create or replace / alter 視圖名 as select ....
create or replace view class_view as select * from class;
alter view test.class_view as select * from class;
# 視圖刪除，語(yǔ)法：drop view 視圖名
drop view class_view;
# 視圖查看
show create view class_view;
describe class_view;

6蠢络、臨時(shí)表（temporary table）

# 定義：mysql 3.23 出現(xiàn)的新特性衰猛，用戶自己定義的外部臨時(shí)表，
#      這種臨時(shí)表只對(duì)當(dāng)前用戶可見刹孔，當(dāng)前會(huì)話結(jié)束的時(shí)候啡省，該臨時(shí)表會(huì)被自動(dòng)銷毀。
#      這種臨時(shí)表的命名與非臨時(shí)表可以同名（同名后非臨時(shí)表將對(duì)當(dāng)前會(huì)話不可見芦疏，直到臨時(shí)表被刪除）
# 用處：用來(lái)存儲(chǔ)一些臨時(shí)數(shù)據(jù)
# 有點(diǎn)：臨時(shí)冕杠，連接斷開就可以自動(dòng)銷毀

## 臨時(shí)表的創(chuàng)建
create temporary table class_temp as select * from student;
## 臨時(shí)表的刪除
drop table class_temp;

7、變量

# 變量分為系統(tǒng)變量和用戶自定義變量
  # 系統(tǒng)變量：變量由系統(tǒng)提供酸茴，不是用戶定義的變量分预，分為全局變量和會(huì)話變量
    # 全局變量：針對(duì)本次啟動(dòng)全部連接都生效，mysql服務(wù)重啟會(huì)被重置薪捍，修改會(huì)對(duì)新建連接的同名會(huì)話變量有影響笼痹，對(duì)已經(jīng)建立連接的會(huì)話變量無(wú)影響
    # 會(huì)話變量：值針對(duì)本次連接生效，對(duì)其他連接無(wú)效酪穿，斷開重連變量也會(huì)被重置
  # 自定義變量：用戶自己定義的變量凳干，分為用戶變量和局部變量
    # 用戶變量：作用域在當(dāng)前會(huì)話，變量名前必須加@
    # 局部變量：只能在begin end第一句聲明被济，并且只作用在當(dāng)前begin end中

# 系統(tǒng)變量

## 全局變量
### 查看所有全局變量
show global variables;
### 查看滿足條件的全局變量救赐，語(yǔ)法：show global variables like xxx
show global variables like '%char%';
### 修改全局變量，語(yǔ)法：set global 變量名=變量值
set global autocommit=0;

## 會(huì)話變量
### 查看所有會(huì)話變量只磷，語(yǔ)法：show [session] variables
show session variables;
show variables;
### 查看滿足條件的會(huì)話變量经磅，語(yǔ)法：show [session] variables like xxx
show variables like 'autocommit';
### 修改會(huì)話變量，語(yǔ)法：set [session] 變量名=變量值
set autocommit=0;

# 自定義變量

## 用戶變量
### 自定義申明并初始化钮追、更新有如下四種方式
  # set @變量名=值;
  # set @變量名:=值;
  # select @變量名:=值;
  # select 字段 into @變量名 from 表名....;
set @name='Tom';
set @age:=23;
select @classId:=2;
select grade into @grade from student where id=1;
### 自定義的用戶變量輸出预厌，語(yǔ)法：select @變量名;
select @name, @age, @classId, @grade;

## 局部變量
### 聲明 DECLARE 變量名 類型 [DEFAULT 值]
### 賦值
  # set 變量名=值;
  # set 變量名:=值;
  # select 變量名:=值
  # select 字段 into 變量名 from 表名....;
### 輸出，語(yǔ)法：select 變量名;

8元媚、存儲(chǔ)過(guò)程

# 定義：一組預(yù)先編譯好的SQL集合
# 好處：
#   存儲(chǔ)的是編譯好的代碼塊轧叽，執(zhí)行效率要比T-SQL語(yǔ)句高
#   替代大堆的單個(gè)T-SQL語(yǔ)句，降低網(wǎng)絡(luò)通信量刊棕，提高通信速率
#   通過(guò)存儲(chǔ)過(guò)程能夠使沒(méi)有權(quán)限的用戶在控制下間接地存取數(shù)據(jù)庫(kù)炭晒，從而確保數(shù)據(jù)的安全

# 創(chuàng)建語(yǔ)法
  # create procedure 存儲(chǔ)過(guò)程名(參數(shù)模式 參數(shù)名 參數(shù)類型, 參數(shù)模式 參數(shù)名 參數(shù)類型, ....)
  # begin
  #   存儲(chǔ)過(guò)程體（一組合法的SQL語(yǔ)句）
  # end
  #
  # 參數(shù)模式可以分為 in、out甥角、inout三種
  # in：傳入?yún)?shù)腰埂，調(diào)用方傳入
  # out：該參數(shù)可以作為返回值
  # inout：該參數(shù)既可以傳入也可以作為返回值傳出
  #
  # 下面來(lái)一組案例，查看某班分?jǐn)?shù)在某一水平線下并且年級(jí)最小的學(xué)生蜈膨，輸出其姓名和真實(shí)分?jǐn)?shù)
## 第一步. 使用 delimiter 指令將SQL語(yǔ)句的結(jié)束符號(hào)變成$屿笼，注意結(jié)尾沒(méi)有";"，只針對(duì)本次會(huì)話有效
delimiter $
## 第二步：創(chuàng)建存儲(chǔ)過(guò)程
create procedure my_procedure (in classId int, out name varchar(20), inout grade int)
begin
    declare age int; # 定義一個(gè)局部變量翁巍，需要放在begin end第一樣
    select min(s.age) into age from student s where s.classId=classId and s.grade<grade;
    select s.name, s.grade into name, grade from student s where s.age=age limit 1;
end $
## 第三步：調(diào)用創(chuàng)建好的存儲(chǔ)過(guò)程
set @grade=60$ # 定義一個(gè)用戶變量驴一，用作參數(shù)輸入
call my_procedure(1, @name, @grade)$
select @name, @grade$
## 刪除存儲(chǔ)過(guò)程
drop procedure my_procedure $
## 查看存儲(chǔ)過(guò)程
show create procedure my_procedure$

測(cè)試數(shù)據(jù)

drop table class;
drop table student;
drop table grade_level;

create table test.class
(
    id      int auto_increment comment '班級(jí)id'
        primary key,
    name    varchar(10) null comment '班級(jí)名稱',
    teacher varchar(10) null comment '班主任'
)
    comment '班級(jí)表';

create table test.student
(
    id          int auto_increment comment '學(xué)生id'
        primary key,
    name        varchar(10)                        not null comment '學(xué)生姓名',
    age         int      default 0                 null comment '學(xué)生年齡',
    grander     char     default '0'               null comment '學(xué)生性別',
    grade       int      default 0                 null comment '成績(jī)',
    classId     int      default 0                 not null comment '關(guān)聯(lián)的班級(jí)id',
    create_time datetime default CURRENT_TIMESTAMP null comment '創(chuàng)建時(shí)間，默認(rèn)為當(dāng)前時(shí)間'
)
    comment '學(xué)生表';

create table test.grade_level
(
    id      int auto_increment comment 'id'
        primary key,
    level   char default 'A' null comment '等級(jí)灶壶，分為A肝断、B...',
    l_grade int              null comment '該等級(jí)最低分?jǐn)?shù)',
    h_grade int              null comment '該等級(jí)最高分?jǐn)?shù)'
)
    comment '成績(jī)等級(jí)表';

insert into test.class (name, teacher)
values  ('一班', 'Bob'),
        ('二班', 'John'),
        ('三班', 'Bob'),
        ('四班', 'Bob');

insert into test.student (name, age, grander, grade, classId)
values  ('Tom', 12, '男', 30, 1),
        ('Jerry', 24, '女', 88, 1),
        ('Anne', 12, '女', 70, 2),
        ('Rose', 23, '女', 90, 3),
        ('Lisa', 22, '女', 101, 5),
        ('Kevin', 24, '男', 78, 100);

insert into test.grade_level (level, l_grade, h_grade)
values  ('A', 81, 100),
        ('B', 61, 80),
        ('C', 0, 60);

select * from class;
select * from student;
select * from grade_level;

truncate class;
truncate student;
truncate grade_level;