資料參考：https://www.bilibili.com/video/BV12b411K7Zu?p=1

本文根據(jù)尚硅谷相關(guān)教程整理了在使用MySQL時(shí)需要了解的相關(guān)概念和操作的知識(shí)。望可以幫助到需要的童鞋~

概念

數(shù)據(jù)庫(kù)相關(guān)概念

一、數(shù)據(jù)庫(kù)的好處
1、可以持久化數(shù)據(jù)到本地
2、結(jié)構(gòu)化查詢

二、數(shù)據(jù)庫(kù)的常見概念 ★
1、DB：數(shù)據(jù)庫(kù)，存儲(chǔ)數(shù)據(jù)的容器
2、DBMS：數(shù)據(jù)庫(kù)管理系統(tǒng)，又稱為數(shù)據(jù)庫(kù)軟件或數(shù)據(jù)庫(kù)產(chǎn)品，用于創(chuàng)建或管理DB
3、SQL：結(jié)構(gòu)化查詢語(yǔ)言，用于和數(shù)據(jù)庫(kù)通信的語(yǔ)言，不是某個(gè)數(shù)據(jù)庫(kù)軟件特有的，而是幾乎所有的主流數(shù)據(jù)庫(kù)軟件通用的語(yǔ)言

三、數(shù)據(jù)庫(kù)存儲(chǔ)數(shù)據(jù)的特點(diǎn)
1、數(shù)據(jù)存放到表中，然后表再放到庫(kù)中
2、一個(gè)庫(kù)中可以有多張表，每張表具有唯一的表名用來(lái)標(biāo)識(shí)自己
3、表中有一個(gè)或多個(gè)列，列又稱為“字段”，相當(dāng)于java中“屬性”
4、表中的每一行數(shù)據(jù)，相當(dāng)于java中“對(duì)象”

四、常見的數(shù)據(jù)庫(kù)管理系統(tǒng)
mysql、oracle、db2、sqlserver

MySQL介紹

一、MySQL的背景
前身屬于瑞典的一家公司，MySQL AB
08年被sun公司收購(gòu)
09年sun被oracle收購(gòu)

二、MySQL的優(yōu)點(diǎn)
1、開源、免費(fèi)、成本低
2、性能高、移植性也好
3、體積小，便于安裝

三、MySQL的安裝
屬于c/s架構(gòu)的軟件，一般來(lái)講安裝服務(wù)端
企業(yè)版
社區(qū)版

四、MySQL服務(wù)的啟動(dòng)和停止
方式一：通過(guò)命令行
net start 服務(wù)名
net stop 服務(wù)名
方式二：計(jì)算機(jī)——右擊——管理——服務(wù)

五、MySQL服務(wù)的登錄和退出

登錄：mysql 【-h 主機(jī)名 -P 端口號(hào)】 -u 用戶名 -p密碼
退出：exit或ctrl+C

MySQL常見命令

1.查看當(dāng)前所有的數(shù)據(jù)庫(kù)
show databases;
2.打開指定的庫(kù)
use 庫(kù)名
3.查看當(dāng)前庫(kù)的所有表
show tables;
4.查看其它庫(kù)的所有表
show tables from 庫(kù)名;
5.創(chuàng)建表
create table 表名(

列名 列類型,
列名 列類型，
。。。

);
6.查看表結(jié)構(gòu)
desc 表名;
7.查看服務(wù)器的版本
方式一：登錄到mysql服務(wù)端
select version();
方式二：沒(méi)有登錄到mysql服務(wù)端
mysql --version
或
mysql --V

MySQL的語(yǔ)法規(guī)范

1.不區(qū)分大小寫,但建議關(guān)鍵字大寫，表名、列名小寫
2.每條命令最好用分號(hào)結(jié)尾
3.每條命令根據(jù)需要，可以進(jìn)行縮進(jìn) 或換行
4.注釋
單行注釋：#注釋文字
單行注釋：-- 注釋文字
多行注釋：/* 注釋文字 */

SQL的語(yǔ)言分類

? DQL（Data Query Language）：數(shù)據(jù)查詢語(yǔ)言
? select
? DML(Data Manipulate Language):數(shù)據(jù)操作語(yǔ)言
? insert 、update、delete
? DDL（Data Define Languge）：數(shù)據(jù)定義語(yǔ)言
? create、drop、alter
? TCL（Transaction Control Language）：事務(wù)控制語(yǔ)言
? commit、rollback

DQL語(yǔ)言

基礎(chǔ)查詢

一、語(yǔ)法
select 查詢列表
from 表名;

二、特點(diǎn)
1、查詢列表可以是字段、常量、表達(dá)式、函數(shù)，也可以是多個(gè)
2、查詢結(jié)果是一個(gè)虛擬表

三、示例
1、查詢單個(gè)字段
select 字段名 from 表名;
2、查詢多個(gè)字段
select 字段名，字段名 from 表名;
3、查詢所有字段
select * from 表名
4、查詢常量
select 常量值;
注意：字符型和日期型的常量值必須用單引號(hào)引起來(lái)，數(shù)值型不需要
5、查詢函數(shù)
select 函數(shù)名(實(shí)參列表);
6、查詢表達(dá)式
select 100/1234;
7、起別名
①as
②空格
8、去重
select distinct 字段名 from 表名;
9、+
作用：做加法運(yùn)算
select 數(shù)值+數(shù)值; 直接運(yùn)算
select 字符+數(shù)值;先試圖將字符轉(zhuǎn)換成數(shù)值，如果轉(zhuǎn)換成功，則繼續(xù)運(yùn)算；否則轉(zhuǎn)換成0，再做運(yùn)算
select null+值;結(jié)果都為null
10、【補(bǔ)充】concat函數(shù)
功能：拼接字符
select concat(字符1，字符2，字符3,...);
11、【補(bǔ)充】ifnull函數(shù)
功能：判斷某字段或表達(dá)式是否為null，如果為null 返回指定的值，否則返回原本的值
select ifnull(commission_pct,0) from employees;
12、【補(bǔ)充】isnull函數(shù)
功能：判斷某字段或表達(dá)式是否為null，如果是，則返回1，否則返回0

條件查詢

一、語(yǔ)法
select 查詢列表
from 表名
where 篩選條件

二、篩選條件的分類
1、簡(jiǎn)單條件運(yùn)算符
> < = <> != >= <= <=>安全等于

2、邏輯運(yùn)算符
&& and
|| or
! not

3、模糊查詢
like:一般搭配通配符使用，可以判斷字符型或數(shù)值型
通配符：%任意多個(gè)字符，_任意單個(gè)字符

between and
in
is null /is not null：用于判斷null值

4、is null PK <=>
普通類型的數(shù)值 null值 可讀性
is null × √ √
<=> √ √ ×

排序查詢

一、語(yǔ)法
select 查詢列表
from 表
where 篩選條件
order by 排序列表 【asc|desc】

二、特點(diǎn)
1、asc ：升序，如果不寫默認(rèn)升序
desc：降序

2、排序列表 支持 單個(gè)字段、多個(gè)字段、函數(shù)、表達(dá)式、別名

3、order by的位置一般放在查詢語(yǔ)句的最后（除limit語(yǔ)句之外）

常見函數(shù)

一、概述
功能：類似于java中的方法
好處：提高重用性和隱藏實(shí)現(xiàn)細(xì)節(jié)
調(diào)用：select 函數(shù)名(實(shí)參列表);

二、單行函數(shù)
1、字符函數(shù)
concat:連接
substr:截取子串
upper:變大寫
lower：變小寫
replace：替換
length：獲取字節(jié)長(zhǎng)度
trim:去前后空格
lpad：左填充
rpad：右填充
instr:獲取子串第一次出現(xiàn)的索引

2、數(shù)學(xué)函數(shù)
ceil:向上取整
round：四舍五入
mod:取模
floor：向下取整
truncate:截?cái)?br> rand:獲取隨機(jī)數(shù)，返回0-1之間的小數(shù)

3、日期函數(shù)
now：返回當(dāng)前日期+時(shí)間
year:返回年
month：返回月
day:返回日
date_format:將日期轉(zhuǎn)換成字符
curdate:返回當(dāng)前日期
str_to_date:將字符轉(zhuǎn)換成日期
curtime：返回當(dāng)前時(shí)間
hour:小時(shí)
minute:分鐘
second：秒
datediff:返回兩個(gè)日期相差的天數(shù)
monthname:以英文形式返回月

4、其他函數(shù)
version 當(dāng)前數(shù)據(jù)庫(kù)服務(wù)器的版本
database 當(dāng)前打開的數(shù)據(jù)庫(kù)
user當(dāng)前用戶
password('字符')：返回該字符的密碼形式
md5('字符'):返回該字符的md5加密形式

5、流程控制函數(shù)
①if(條件表達(dá)式，表達(dá)式1，表達(dá)式2)：如果條件表達(dá)式成立，返回表達(dá)式1，否則返回表達(dá)式2
②case情況1
case 變量或表達(dá)式或字段
when 常量1 then 值1
when 常量2 then 值2
...
else 值n
end
③case情況2
case
when 條件1 then 值1
when 條件2 then 值2
...
else 值n
end

三、分組函數(shù)
1、分類
max 最大值
min 最小值
sum 和
avg 平均值
count 計(jì)算個(gè)數(shù)

2、特點(diǎn)
①語(yǔ)法
select max(字段) from 表名;
②支持的類型
sum和avg一般用于處理數(shù)值型
max、min、count可以處理任何數(shù)據(jù)類型
③以上分組函數(shù)都忽略null
④都可以搭配distinct使用，實(shí)現(xiàn)去重的統(tǒng)計(jì)
select sum(distinct 字段) from 表;
⑤count函數(shù)
count(字段)：統(tǒng)計(jì)該字段非空值的個(gè)數(shù)
count():統(tǒng)計(jì)結(jié)果集的行數(shù)
案例：查詢每個(gè)部門的員工個(gè)數(shù)
1 xx 10
2 dd 20
3 mm 20
4 aa 40
5 hh 40
count(1):統(tǒng)計(jì)結(jié)果集的行數(shù)
效率上：
MyISAM存儲(chǔ)引擎，count()最高
InnoDB存儲(chǔ)引擎，count(*)和count(1)效率>count(字段)
⑥ 和分組函數(shù)一同查詢的字段，要求是group by后出現(xiàn)的字段

分組查詢

一、語(yǔ)法
select 分組函數(shù)，分組后的字段
from 表
【where 篩選條件】
group by 分組的字段
【having 分組后的篩選】
【order by 排序列表】

二、特點(diǎn)
使用關(guān)鍵字 篩選的表 位置
分組前篩選 where 原始表 group by的前面
分組后篩選 having 分組后的結(jié)果 group by 的后面

連接查詢

一、含義
1、當(dāng)查詢中涉及到了多個(gè)表的字段，需要使用多表連接
select 字段1，字段2
from 表1，表2,...;

2、笛卡爾乘積：當(dāng)查詢多個(gè)表時(shí)，沒(méi)有添加有效的連接條件，導(dǎo)致多個(gè)表所有行實(shí)現(xiàn)完全連接
如何解決：添加有效的連接條件

二、分類
按年代分類：
sql92：
等值
非等值
自連接

? 也支持一部分外連接（用于oracle、sqlserver，mysql不支持）
sql99【推薦使用】
? 內(nèi)連接
? 等值
? 非等值
? 自連接
? 外連接
? 左外
? 右外
? 全外（mysql不支持）
? 交叉連接

三、SQL92語(yǔ)法
1、等值連接
語(yǔ)法：
select 查詢列表
from 表1 別名,表2 別名
where 表1.key=表2.key
【and 篩選條件】
【group by 分組字段】
【having 分組后的篩選】
【order by 排序字段】

特點(diǎn)：
① 一般為表起別名
②多表的順序可以調(diào)換
③n表連接至少需要n-1個(gè)連接條件
④等值連接的結(jié)果是多表的交集部分

2、非等值連接
語(yǔ)法：
select 查詢列表
from 表1 別名,表2 別名
where 非等值的連接條件
【and 篩選條件】
【group by 分組字段】
【having 分組后的篩選】
【order by 排序字段】

3、自連接
語(yǔ)法：
select 查詢列表
from 表 別名1,表 別名2
where 等值的連接條件
【and 篩選條件】
【group by 分組字段】
【having 分組后的篩選】
【order by 排序字段】

四、SQL99語(yǔ)法
1、內(nèi)連接
語(yǔ)法：
select 查詢列表
from 表1 別名
【inner】 join 表2 別名 on 連接條件
where 篩選條件
group by 分組列表
having 分組后的篩選
order by 排序列表
limit 子句;
特點(diǎn)：
①表的順序可以調(diào)換
②內(nèi)連接的結(jié)果=多表的交集
③n表連接至少需要n-1個(gè)連接條件
分類：
等值連接
非等值連接
自連接

2、外連接
語(yǔ)法：
select 查詢列表
from 表1 別名
left|right|full【outer】 join 表2 別名 on 連接條件
where 篩選條件
group by 分組列表
having 分組后的篩選
order by 排序列表
limit 子句;
特點(diǎn)：
①查詢的結(jié)果=主表中所有的行，如果從表和它匹配的將顯示匹配行，如果從表沒(méi)有匹配的則顯示null
②left join 左邊的就是主表，right join 右邊的就是主表
full join 兩邊都是主表
③一般用于查詢除了交集部分的剩余的不匹配的行
3、交叉連接
語(yǔ)法：
select 查詢列表
from 表1 別名
cross join 表2 別名;
特點(diǎn)：
類似于笛卡爾乘積

子查詢

一、含義
嵌套在其他語(yǔ)句內(nèi)部的select語(yǔ)句稱為子查詢或內(nèi)查詢，
外面的語(yǔ)句可以是insert、update、delete、select等，一般select作為外面語(yǔ)句較多
外面如果為select語(yǔ)句，則此語(yǔ)句稱為外查詢或主查詢

二、分類
1、按出現(xiàn)位置
select后面：
僅僅支持標(biāo)量子查詢
from后面：
表子查詢
where或having后面：
標(biāo)量子查詢
列子查詢
行子查詢
exists后面：
標(biāo)量子查詢
列子查詢
行子查詢
表子查詢

2、按結(jié)果集的行列
標(biāo)量子查詢（單行子查詢）：結(jié)果集為一行一列
列子查詢（多行子查詢）：結(jié)果集為多行一列
行子查詢：結(jié)果集為多行多列
表子查詢：結(jié)果集為多行多列

三、示例
where或having后面
1、標(biāo)量子查詢
案例：查詢最低工資的員工姓名和工資
①最低工資
select min(salary) from employees

②查詢員工的姓名和工資，要求工資=①
select last_name,salary
from employees
where salary=(
select min(salary) from employees
);

2、列子查詢
案例：查詢所有是領(lǐng)導(dǎo)的員工姓名
①查詢所有員工的 manager_id
select manager_id
from employees

②查詢姓名，employee_id屬于①列表的一個(gè)
select last_name
from employees
where employee_id in(
select manager_id
from employees
);

分頁(yè)查詢

一、應(yīng)用場(chǎng)景
當(dāng)要查詢的條目數(shù)太多，一頁(yè)顯示不全

二、語(yǔ)法
select 查詢列表
from 表
limit 【offset，】size;
注意：
offset代表的是起始的條目索引，默認(rèn)從0開始
size代表的是顯示的條目數(shù)

公式：
假如要顯示的頁(yè)數(shù)為page，每一頁(yè)條目數(shù)為size
select 查詢列表
from 表
limit (page-1)*size,size;

聯(lián)合查詢

一、含義
union：合并、聯(lián)合，將多次查詢結(jié)果合并成一個(gè)結(jié)果

二、語(yǔ)法
查詢語(yǔ)句1
union 【all】
查詢語(yǔ)句2
union 【all】
...

三、意義
1、將一條比較復(fù)雜的查詢語(yǔ)句拆分成多條語(yǔ)句
2、適用于查詢多個(gè)表的時(shí)候，查詢的列基本是一致

四、特點(diǎn)
1、要求多條查詢語(yǔ)句的查詢列數(shù)必須一致
2、要求多條查詢語(yǔ)句的查詢的各列類型、順序最好一致
3、union 去重，union all包含重復(fù)項(xiàng)

查詢總結(jié)

語(yǔ)法：
select 查詢列表 ⑦
from 表1 別名 ①
連接類型 join 表2 ②
on 連接條件 ③
where 篩選 ④
group by 分組列表 ⑤
having 篩選 ⑥
order by排序列表 ⑧
limit 起始條目索引，條目數(shù); ⑨

DML語(yǔ)言

插入

一、方式一
語(yǔ)法：
insert into 表名(字段名,...) values(值,...);
特點(diǎn)：
1、要求值的類型和字段的類型要一致或兼容
2、字段的個(gè)數(shù)和順序不一定與原始表中的字段個(gè)數(shù)和順序一致
但必須保證值和字段一一對(duì)應(yīng)
3、假如表中有可以為null的字段，注意可以通過(guò)以下兩種方式插入null值
①字段和值都省略
②字段寫上，值使用null
4、字段和值的個(gè)數(shù)必須一致
5、字段名可以省略，默認(rèn)所有列

二、方式二
語(yǔ)法：
insert into 表名 set 字段=值,字段=值,...;

三、兩種方式 的區(qū)別：
1.方式一支持一次插入多行，語(yǔ)法如下：
insert into 表名【(字段名,..)】 values(值，..),(值，...),...;
2.方式一支持子查詢，語(yǔ)法如下：
insert into 表名
查詢語(yǔ)句;

修改

一、修改單表的記錄 ★
語(yǔ)法：update 表名 set 字段=值,字段=值 【where 篩選條件】;

二、修改多表的記錄【補(bǔ)充】
語(yǔ)法：
update 表1 別名
left|right|inner join 表2 別名
on 連接條件
set 字段=值,字段=值
【where 篩選條件】;

刪除

方式一：使用delete
一、刪除單表的記錄★
語(yǔ)法：delete from 表名 【where 篩選條件】【limit 條目數(shù)】

二、級(jí)聯(lián)刪除[補(bǔ)充]
語(yǔ)法：
delete 別名1,別名2 from 表1 別名
inner|left|right join 表2 別名
on 連接條件
【where 篩選條件】

方式二：使用truncate
語(yǔ)法：truncate table 表名

兩種方式的區(qū)別【面試題】★
1.truncate刪除后，如果再插入，標(biāo)識(shí)列從1開始
delete刪除后，如果再插入，標(biāo)識(shí)列從斷點(diǎn)開始
2.delete可以添加篩選條件
truncate不可以添加篩選條件
3.truncate效率較高
4.truncate沒(méi)有返回值
delete可以返回受影響的行數(shù)
5.truncate不可以回滾
delete可以回滾

DDL語(yǔ)言

庫(kù)的管理

一、創(chuàng)建庫(kù)
create database 【if not exists】 庫(kù)名【 character set 字符集名】;

二、修改庫(kù)
alter database 庫(kù)名 character set 字符集名;

三、刪除庫(kù)
drop database 【if exists】 庫(kù)名;

表的管理

一、創(chuàng)建表 ★
create table 【if not exists】 表名(
字段名 字段類型 【約束】,
字段名 字段類型 【約束】,
。。。
字段名 字段類型 【約束】

)

二、修改表
1.添加列
alter table 表名 add column 列名 類型 【first|after 字段名】;
2.修改列的類型或約束
alter table 表名 modify column 列名 新類型 【新約束】;
3.修改列名
alter table 表名 change column 舊列名 新列名 類型;
4 .刪除列
alter table 表名 drop column 列名;
5.修改表名
alter table 表名 rename 【to】 新表名;

三、刪除表
drop table【if exists】 表名;

四、復(fù)制表
1、復(fù)制表的結(jié)構(gòu)
create table 表名 like 舊表;
2、復(fù)制表的結(jié)構(gòu)+數(shù)據(jù)
create table 表名
select 查詢列表 from 舊表【where 篩選】;

數(shù)據(jù)類型

一、數(shù)值型
1、整型
tinyint、smallint、mediumint、int/integer、bigint
1 2 3 4 8

特點(diǎn)：
①都可以設(shè)置無(wú)符號(hào)和有符號(hào)，默認(rèn)有符號(hào)，通過(guò)unsigned設(shè)置無(wú)符號(hào)
②如果超出了范圍，會(huì)報(bào)out or range異常，插入臨界值
③長(zhǎng)度可以不指定，默認(rèn)會(huì)有一個(gè)長(zhǎng)度
長(zhǎng)度代表顯示的最大寬度，如果不夠則左邊用0填充，但需要搭配zerofill，并且默認(rèn)變?yōu)闊o(wú)符號(hào)整型

2、浮點(diǎn)型
定點(diǎn)數(shù)：decimal(M,D)
浮點(diǎn)數(shù):
float(M,D) 4
double(M,D) 8

特點(diǎn)：
①M(fèi)代表整數(shù)部位+小數(shù)部位的個(gè)數(shù)，D代表小數(shù)部位
②如果超出范圍，則報(bào)out or range異常，并且插入臨界值
③M和D都可以省略，但對(duì)于定點(diǎn)數(shù)，M默認(rèn)為10，D默認(rèn)為0
④如果精度要求較高，則優(yōu)先考慮使用定點(diǎn)數(shù)

二、字符型
char、varchar、binary、varbinary、enum、set、text、blob

char：固定長(zhǎng)度的字符，寫法為char(M)，最大長(zhǎng)度不能超過(guò)M，其中M可以省略，默認(rèn)為1
varchar：可變長(zhǎng)度的字符，寫法為varchar(M)，最大長(zhǎng)度不能超過(guò)M，其中M不可以省略

三、日期型
year年
date日期
time時(shí)間
datetime 日期+時(shí)間 8
timestamp 日期+時(shí)間 4 比較容易受時(shí)區(qū)、語(yǔ)法模式、版本的影響，更能反映當(dāng)前時(shí)區(qū)的真實(shí)時(shí)間

常見的約束

一、常見的約束
NOT NULL：非空，該字段的值必填
UNIQUE：唯一，該字段的值不可重復(fù)
DEFAULT：默認(rèn)，該字段的值不用手動(dòng)插入有默認(rèn)值
CHECK：檢查，mysql不支持
PRIMARY KEY：主鍵，該字段的值不可重復(fù)并且非空 unique+not null
FOREIGN KEY：外鍵，該字段的值引用了另外的表的字段

主鍵和唯一
1、區(qū)別：
①、一個(gè)表至多有一個(gè)主鍵，但可以有多個(gè)唯一
②、主鍵不允許為空，唯一可以為空
2、相同點(diǎn)
都具有唯一性
都支持組合鍵，但不推薦
外鍵：
1、用于限制兩個(gè)表的關(guān)系，從表的字段值引用了主表的某字段值
2、外鍵列和主表的被引用列要求類型一致，意義一樣，名稱無(wú)要求
3、主表的被引用列要求是一個(gè)key（一般就是主鍵）
4、插入數(shù)據(jù)，先插入主表
刪除數(shù)據(jù)，先刪除從表
可以通過(guò)以下兩種方式來(lái)刪除主表的記錄
方式一：級(jí)聯(lián)刪除
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;

方式二：級(jí)聯(lián)置空
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;

二、創(chuàng)建表時(shí)添加約束
create table 表名(
字段名 字段類型 not null,#非空
字段名 字段類型 primary key,#主鍵
字段名 字段類型 unique,#唯一
字段名 字段類型 default 值,#默認(rèn)
constraint 約束名 foreign key(字段名) references 主表（被引用列）

)
注意：
支持類型 可以起約束名
列級(jí)約束 除了外鍵 不可以
表級(jí)約束 除了非空和默認(rèn) 可以，但對(duì)主鍵無(wú)效

列級(jí)約束可以在一個(gè)字段上追加多個(gè)，中間用空格隔開，沒(méi)有順序要求

三、修改表時(shí)添加或刪除約束
1、非空
添加非空
alter table 表名 modify column 字段名 字段類型 not null;
刪除非空
alter table 表名 modify column 字段名 字段類型 ;

2、默認(rèn)
添加默認(rèn)
alter table 表名 modify column 字段名 字段類型 default 值;
刪除默認(rèn)
alter table 表名 modify column 字段名 字段類型 ;

3、主鍵
添加主鍵
alter table 表名 add【 constraint 約束名】 primary key(字段名);
刪除主鍵
alter table 表名 drop primary key;

4、唯一
添加唯一
alter table 表名 add【 constraint 約束名】 unique(字段名);
刪除唯一
alter table 表名 drop index 索引名;

5、外鍵
添加外鍵
alter table 表名 add【 constraint 約束名】 foreign key(字段名) references 主表（被引用列）;
刪除外鍵
alter table 表名 drop foreign key 約束名;

四、自增長(zhǎng)列
特點(diǎn)：
1、不用手動(dòng)插入值，可以自動(dòng)提供序列值，默認(rèn)從1開始，步長(zhǎng)為1
auto_increment_increment
如果要更改起始值：手動(dòng)插入值
如果要更改步長(zhǎng)：更改系統(tǒng)變量
set auto_increment_increment=值;
2、一個(gè)表至多有一個(gè)自增長(zhǎng)列
3、自增長(zhǎng)列只能支持?jǐn)?shù)值型
4、自增長(zhǎng)列必須為一個(gè)key

一、創(chuàng)建表時(shí)設(shè)置自增長(zhǎng)列
create table 表(
字段名 字段類型 約束 auto_increment
)
二、修改表時(shí)設(shè)置自增長(zhǎng)列
alter table 表 modify column 字段名 字段類型 約束 auto_increment
三、刪除自增長(zhǎng)列
alter table 表 modify column 字段名 字段類型 約束

TCL語(yǔ)言

事務(wù)

一、含義
事務(wù)：一條或多條sql語(yǔ)句組成一個(gè)執(zhí)行單位，一組sql語(yǔ)句要么都執(zhí)行要么都不執(zhí)行
二、特點(diǎn)（ACID）
A 原子性：一個(gè)事務(wù)是不可再分割的整體，要么都執(zhí)行要么都不執(zhí)行
C 一致性：一個(gè)事務(wù)可以使數(shù)據(jù)從一個(gè)一致狀態(tài)切換到另外一個(gè)一致的狀態(tài)
I 隔離性：一個(gè)事務(wù)不受其他事務(wù)的干擾，多個(gè)事務(wù)互相隔離的
D 持久性：一個(gè)事務(wù)一旦提交了，則永久的持久化到本地

三、事務(wù)的使用步驟 ★
了解：
隱式（自動(dòng)）事務(wù)：沒(méi)有明顯的開啟和結(jié)束，本身就是一條事務(wù)可以自動(dòng)提交，比如insert、update、delete
顯式事務(wù)：具有明顯的開啟和結(jié)束

使用顯式事務(wù)：
①開啟事務(wù)
set autocommit=0;
start transaction;#可以省略

②編寫一組邏輯sql語(yǔ)句
注意：sql語(yǔ)句支持的是insert、update、delete

設(shè)置回滾點(diǎn)：
savepoint 回滾點(diǎn)名;

③結(jié)束事務(wù)
提交：commit;
回滾：rollback;
回滾到指定的地方：rollback to 回滾點(diǎn)名;
四、并發(fā)事務(wù)
1、事務(wù)的并發(fā)問(wèn)題是如何發(fā)生的？
多個(gè)事務(wù) 同時(shí) 操作 同一個(gè)數(shù)據(jù)庫(kù)的相同數(shù)據(jù)時(shí)
2、并發(fā)問(wèn)題都有哪些？
臟讀：一個(gè)事務(wù)讀取了其他事務(wù)還沒(méi)有提交的數(shù)據(jù)，讀到的是其他事務(wù)“更新”的數(shù)據(jù)
不可重復(fù)讀：一個(gè)事務(wù)多次讀取，結(jié)果不一樣
幻讀：一個(gè)事務(wù)讀取了其他事務(wù)還沒(méi)有提交的數(shù)據(jù)，只是讀到的是 其他事務(wù)“插入”的數(shù)據(jù)
3、如何解決并發(fā)問(wèn)題
通過(guò)設(shè)置隔離級(jí)別來(lái)解決并發(fā)問(wèn)題
4、隔離級(jí)別
臟讀 不可重復(fù)讀 幻讀
read uncommitted:讀未提交 × × ×
read committed：讀已提交 √ × ×
repeatable read：可重復(fù)讀 √ √ ×
serializable：串行化 √ √ √

其他

視圖

一、含義
mysql5.1版本出現(xiàn)的新特性，本身是一個(gè)虛擬表，它的數(shù)據(jù)來(lái)自于表，通過(guò)執(zhí)行時(shí)動(dòng)態(tài)生成。
好處：
1、簡(jiǎn)化sql語(yǔ)句
2、提高了sql的重用性
3、保護(hù)基表的數(shù)據(jù)，提高了安全性

二、創(chuàng)建
create view 視圖名
as
查詢語(yǔ)句;

三、修改
方式一：
create or replace view 視圖名
as
查詢語(yǔ)句;
方式二：
alter view 視圖名
as
查詢語(yǔ)句

四、刪除
drop view 視圖1，視圖2,...;

五、查看
desc 視圖名;
show create view 視圖名;

六、使用
1.插入
insert
2.修改
update
3.刪除
delete
4.查看
select
注意：視圖一般用于查詢的，而不是更新的，所以具備以下特點(diǎn)的視圖都不允許更新
①包含分組函數(shù)、group by、distinct、having、union、
②join
③常量視圖
④where后的子查詢用到了from中的表
⑤用到了不可更新的視圖

七、視圖和表的對(duì)比
關(guān)鍵字 是否占用物理空間 使用
視圖 view 占用較小，只保存sql邏輯 一般用于查詢
表 table 保存實(shí)際的數(shù)據(jù) 增刪改查

變量

分類
一、系統(tǒng)變量
說(shuō)明：變量由系統(tǒng)提供的，不用自定義
語(yǔ)法：
①查看系統(tǒng)變量
show 【global|session 】variables like ''; 如果沒(méi)有顯式聲明global還是session，則默認(rèn)是session
②查看指定的系統(tǒng)變量的值
select @@【global|session】.變量名; 如果沒(méi)有顯式聲明global還是session，則默認(rèn)是session
③為系統(tǒng)變量賦值
方式一：
set 【global|session 】 變量名=值; 如果沒(méi)有顯式聲明global還是session，則默認(rèn)是session
方式二：
set @@global.變量名=值;
set @@變量名=值；

1、全局變量
服務(wù)器層面上的，必須擁有super權(quán)限才能為系統(tǒng)變量賦值，作用域?yàn)檎麄€(gè)服務(wù)器，也就是針對(duì)于所有連接（會(huì)話）有效

2、會(huì)話變量
服務(wù)器為每一個(gè)連接的客戶端都提供了系統(tǒng)變量，作用域?yàn)楫?dāng)前的連接（會(huì)話）

二、自定義變量
說(shuō)明：
1、用戶變量
作用域：針對(duì)于當(dāng)前連接（會(huì)話）生效
位置：begin end里面，也可以放在外面
使用：

①聲明并賦值：
set @變量名=值;或
set @變量名:=值;或
select @變量名:=值;

②更新值
方式一：
set @變量名=值;或
set @變量名:=值;或
select @變量名:=值;
方式二：
select xx into @變量名 from 表;

③使用
select @變量名;

2、局部變量
作用域：僅僅在定義它的begin end中有效
位置：只能放在begin end中，而且只能放在第一句
使用：
①聲明
declare 變量名 類型 【default 值】;
②賦值或更新
方式一：
set 變量名=值;或
set 變量名:=值;或
select @變量名:=值;
方式二：
select xx into 變量名 from 表;
③使用
select 變量名;

存儲(chǔ)過(guò)程

一、創(chuàng)建 ★
create procedure 存儲(chǔ)過(guò)程名(參數(shù)模式 參數(shù)名 參數(shù)類型)
begin
存儲(chǔ)過(guò)程體
end
注意：
1.參數(shù)模式：in、out、inout，其中in可以省略
2.存儲(chǔ)過(guò)程體的每一條sql語(yǔ)句都需要用分號(hào)結(jié)尾
3.需要設(shè)置新的結(jié)束標(biāo)記
delimiter 新的結(jié)束標(biāo)記

二、調(diào)用
call 存儲(chǔ)過(guò)程名(實(shí)參列表)
舉例：
調(diào)用in模式的參數(shù)：call sp1（‘值’）;
調(diào)用out模式的參數(shù)：set @name; call sp1(@name);select @name;
調(diào)用inout模式的參數(shù)：set @name=值; call sp1(@name); select @name;

三、查看
show create procedure 存儲(chǔ)過(guò)程名;

四、刪除
drop procedure 存儲(chǔ)過(guò)程名;

函數(shù)

一、創(chuàng)建
create function 函數(shù)名(參數(shù)名 參數(shù)類型) returns 返回類型
begin
函數(shù)體
end
注意：函數(shù)體中肯定需要有return語(yǔ)句

二、調(diào)用
select 函數(shù)名(實(shí)參列表);

三、查看
show create function 函數(shù)名;

四、刪除
drop function 函數(shù)名；

流程控制結(jié)構(gòu)

分支結(jié)構(gòu)

特點(diǎn)：
1、if函數(shù)
功能：實(shí)現(xiàn)簡(jiǎn)單雙分支
語(yǔ)法：
if(條件，值1，值2)
位置：
可以作為表達(dá)式放在任何位置

2、case結(jié)構(gòu)
功能：實(shí)現(xiàn)多分支
語(yǔ)法1：
case 表達(dá)式或字段
when 值1 then 語(yǔ)句1;
when 值2 then 語(yǔ)句2；
..
else 語(yǔ)句n;
end [case];

語(yǔ)法2：
case
when 條件1 then 語(yǔ)句1;
when 條件2 then 語(yǔ)句2；
..
else 語(yǔ)句n;
end [case];

位置：
可以放在任何位置，
如果放在begin end 外面，作為表達(dá)式結(jié)合著其他語(yǔ)句使用
如果放在begin end 里面，一般作為獨(dú)立的語(yǔ)句使用

3、if結(jié)構(gòu)
功能：實(shí)現(xiàn)多分支
語(yǔ)法：
if 條件1 then 語(yǔ)句1;
elseif 條件2 then 語(yǔ)句2;
...
else 語(yǔ)句n;
end if;
位置：
只能放在begin end中

循環(huán)結(jié)構(gòu)

位置：
只能放在begin end中

特點(diǎn)：都能實(shí)現(xiàn)循環(huán)結(jié)構(gòu)

對(duì)比：

①這三種循環(huán)都可以省略名稱，但如果循環(huán)中添加了循環(huán)控制語(yǔ)句（leave或iterate）則必須添加名稱
②
loop 一般用于實(shí)現(xiàn)簡(jiǎn)單的死循環(huán)
while 先判斷后執(zhí)行
repeat 先執(zhí)行后判斷，無(wú)條件至少執(zhí)行一次

1、while
語(yǔ)法：
【名稱:】while 循環(huán)條件 do
循環(huán)體
end while 【名稱】;
2、loop
語(yǔ)法：
【名稱：】loop
循環(huán)體
end loop 【名稱】;

3、repeat
語(yǔ)法：
【名稱:】repeat
循環(huán)體
until 結(jié)束條件
end repeat 【名稱】;

二、循環(huán)控制語(yǔ)句
leave：類似于break，用于跳出所在的循環(huán)
iterate：類似于continue，用于結(jié)束本次循環(huán)，繼續(xù)下一次