# MySQL
## 一、MySQL服務的啟動和停止
? 方式一：計算機——右擊管理——服務
方式二：通過管理員身份運行
net start 服務名（啟動服務）
net stop 服務名（停止服務）
## 二、MySQL的常見命令
```mysql
查看服務器的版本
方式一：登錄到mysql服務端
select version();
方式二：沒有登錄到mysql服務端
mysql --version
或
mysql --V
查看當前所有的數據庫
show databases;
打開指定的庫
use 庫名
查看當前庫的所有表
show tables;
查看其它庫的所有表
show tables from 庫名;
創建表
create table 表名(
列名 列類型,
列名 列類型，
。。。
);
查看表結構
desc 表名;
插入信息
insert into 表名() values()
更新信息
update 表名() set 字段 where 條件
刪除信息
delete from 表名 where 條件
```
?
## 三、MySQL的語法規范
? 1.不區分大小寫,但建議關鍵字大寫，表名、列名小寫
2.每條命令最好用分號結尾
3.每條命令根據需要，可以進行縮進 或換行
4.注釋
單行注釋：#注釋文字
單行注釋：-- 注釋文字
多行注釋：/* 注釋文字 */
## 四、數據查詢語言DQL
### 1、基礎查詢
1.查詢列表可以是：表中的字段、常量值、表達式、函數
2.查詢的結果是一個虛擬的表格
```mysql
select 內容 from 表名
-- SELECT first_name,salary FROM employees;
-- SELECT DISTINCT department_name 部門 FROM departments;
-- SELECT CONCAT(first_name,'的工資是',salary) 員工信息 FROM employees
-- SELECT last_name,job_id,salary AS sal FROM employees
-- SELECT * FROM employees
-- SELECT employee_id,last_name,salary * 12 as "ANNUAL SALARY" from employees
-- desc departments
-- select * from departments
-- select DISTINCT job_id from employees
-- select * from employees
-- select count(salary) from employees
-- select SUM(salary) from employees
-- select SUM(distinct salary) from employees
-- select ROUND(AVG(salary),2) from employees
-- SELECT CONCAT(first_name,',',last_name,',',job_id,',',IFNULL(commission_pct,0)) out_put
-- FROM employees
-- select NOW()
```
### 2、條件查詢
根據條件過濾原始表的數據，查詢到想要的數據
語法：
select
要查詢的字段|表達式|常量值|函數
from
表
where
條件 ;
```mysql
一、條件運算符
> < >= <= = != <>
-- 查詢工資大于12000的員工姓名和工資
-- select first_name,salary
-- from employees
-- where salary>12000
二、邏輯運算符
and（&&）:兩個條件如果同時成立，結果為true，否則為false
or(||)：兩個條件只要有一個成立，結果為true，否則為false
not(!)：如果條件成立，則not后為false，否則為true
-- 3.選擇工資不在5000到12000的員工的姓名和工資
-- select first_name,salary
-- from employees
-- where salary not between 5000 and 12000
三、模糊查詢
like
between and
in
is null
-- 選擇公司中沒有管理者的員工姓名及job-id
-- SELECT first_name,job_id
-- FROM employees
-- WHERE manager_id is NULL
```
### 3、排序查詢
```mysql
order by 排序的字段|表達式|函數|別名 【asc|desc】
-- 查詢員工的姓名和部門號和年薪，按年薪降序，按姓名升序
-- SELECT first_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪
-- FROM employees
-- ORDER BY 年薪 DESC,first_name ASC
```
### 4、常見函數
一、單行函數
```mysql
1、字符函數
concat拼接
substr截取子串
upper轉換成大寫
lower轉換成小寫
trim去前后指定的空格和字符
ltrim去左邊空格
rtrim去右邊空格
replace替換
lpad左填充
rpad右填充
instr返回子串第一次出現的索引
length 獲取字節個數
2、數學函數
round 四舍五入
rand 隨機數
floor向下取整
ceil向上取整
mod取余
truncate截斷
3、日期函數
now當前系統日期+時間
curdate當前系統日期
curtime當前系統時間
str_to_date 將字符轉換成日期
date_format將日期轉換成字符
4、流程控制函數
if 處理雙分支
case語句 處理多分支
情況1：處理等值判斷
情況2：處理條件判斷
5、其他函數
version版本
database當前庫
user當前連接用戶
```
二、分組函數
```mysql
sum 求和
max 最大值
min 最小值
avg 平均值
count 計數
特點：
1、以上五個分組函數都忽略null值，除了count(*)
2、sum和avg一般用于處理數值型
max、min、count可以處理任何數據類型
3、都可以搭配distinct使用，用于統計去重后的結果
4、count的參數可以支持：
字段、*、常量值，一般放1
建議使用 count(*)
```
### 5、分組查詢
? 語法：
select 查詢的字段，分組函數
from 表
group by 分組的字段
? 特點：
1、可以按單個字段分組
2、和分組函數一同查詢的字段最好是分組后的字段
3、分組篩選
分組前篩選： where group by
分組后篩選： group by having
```mysql
-- 查詢各個管理者手下員工的最低工資，其中最低工資不能低于6000，沒有管理者的員工不計算在內
-- SELECT manager_id,MIN(salary)
-- FROM employees
-- GROUP BY manager_id
-- HAVING MIN(salary)>=6000 AND manager_id IS NOT NULL
```
### 6、連接查詢
笛卡爾乘積：如果連接條件省略或無效則會出現
解決辦法：添加上連接條件
1、傳統模式下的連接 ：等值連接——非等值連接
```mysql
1.等值連接的結果 = 多個表的交集
2.n表連接，至少需要n-1個連接條件
3.多個表不分主次，沒有順序要求
4.一般為表起別名，提高閱讀性和性能
-- 查詢部門所在城市名包含's'的員工名、部門名和城市，并按部門名降序排列
-- SELECT last_name,department_name,city
-- FROM employees e,departments d,locations l
-- WHERE e.department_id=d.department_id
-- AND d.location_id=l.location_id
-- AND city LIKE 's%'
-- ORDER BY department_name DESC
```
2、sql99語法：通過join關鍵字實現連接
```mysql
含義：1999年推出的sql語法
支持：
等值連接、非等值連接 （內連接）
外連接
交叉連接
語法：
select 字段，...
from 表1
【inner|left outer|right outer|cross】join 表2 on 連接條件
【inner|left outer|right outer|cross】join 表3 on 連接條件
【where 篩選條件】
【group by 分組字段】
【having 分組后的篩選條件】
【order by 排序的字段或表達式】
-- 查詢每個工種、每個部門的部門名、工種名和最低工資
-- SELECT department_name,job_title,MIN(salary)
-- FROM employees e
-- JOIN departments d
-- JOIN jobs j
-- ON e.department_id=d.department_id AND e.job_id=j.job_id
-- GROUP BY job_title,department_name
好處：語句上，連接條件和篩選條件實現了分離，簡潔明了！
```
三、自連接
1、99語法
```mysql
-- 選擇指定員工的姓名，員工號，以及他的管理者的姓名和員工號
-- SELECT e.first_name employee,e.employee_id emp,m.first_name manager,m.employee_id mgr
-- FROM employees e
-- JOIN employees m
-- ON e.manager_id=m.employee_id
```
2、92語法
```mysql
SELECT e.first_name employee,e.employee_id emp,m.first_name manager,m.employee_id mgr
FROM employees e,employees m
WHERE e.manager_id=m.employee_id;
```
### 7、子查詢
? 一條查詢語句中又嵌套了另一條完整的select語句，其中被嵌套的select語句，稱為子查詢或內查詢。在外面的查詢語句，稱為主查詢或外查詢
1、子查詢都放在小括號內
2、子查詢可以放在from后面、select后面、where后面、having后面，但一般放在條件的右側
3、子查詢優先于主查詢執行，主查詢使用了子查詢的執行結果
4、子查詢根據查詢結果的行數不同分為以下兩類：
單行子查詢
結果集只有一行
一般搭配單行操作符使用：> < = <> >= <=
非法使用子查詢的情況：
a、子查詢的結果為一組值
b、子查詢的結果為空
```mysql
-- 查詢和Zlotkey相同部門的員工姓名和工資
-- SELECT first_name,salary
-- FROM employees
-- WHERE department_id=(SELECT department_id FROM employees WHERE last_name='Zlotkey')
② 多行子查詢
結果集有多行
一般搭配多行操作符使用：any、all、in、not in
in： 屬于子查詢結果中的任意一個就行
any和all往往可以用其他查詢代替
-- 查詢管理者是King的員工姓名和工資
-- SELECT last_name,salary
-- FROM employees
-- WHERE manager_id in(SELECT employee_id FROM employees WHERE last_name='K_ing')
```
### 8、分頁查詢
實際的web項目中需要根據用戶的需求提交對應的分頁查詢的sql語句
1.起始條目索引從0開始
2.limit子句放在查詢語句的最后
3.公式：select * from 表 limit （page-1）*sizePerPage,sizePerPage
假如:
每頁顯示條目數sizePerPage
要顯示的頁數 page
```mysql
select 字段|表達式,...
from 表
【where 條件】
【group by 分組字段】
【having 條件】
【order by 排序的字段】
limit 【起始的條目索引，】條目數;
-- 查詢平均工資最高的job信息
-- SELECT j.*
-- FROM jobs j
-- WHERE job_id=(SELECT job_id
-- FROM employees
-- GROUP BY job_id
-- ORDER BY AVG(salary) DESC
-- LIMIT 1)
```
### 9、聯合查詢
1、多條查詢語句的查詢的列數必須是一致的
2、多條查詢語句的查詢的列的類型幾乎相同
3、union代表去重，union all代表不去重
```mysql
select 字段|常量|表達式|函數 【from 表】 【where 條件】 union 【all】
select 字段|常量|表達式|函數 【from 表】 【where 條件】 union 【all】
select 字段|常量|表達式|函數 【from 表】 【where 條件】 union 【all】
.....
select 字段|常量|表達式|函數 【from 表】 【where 條件】
```
## 五、數據操縱語言DML
### 1、插入
語法：
? insert into 表名(字段名，...)
values(值1，...);
1、字段類型和值類型一致或兼容，而且一一對應
2、可以為空的字段，可以不用插入值，或用null填充
3、不可以為空的字段，必須插入值
4、字段個數和值的個數必須一致
5、字段可以省略，但默認所有字段，并且順序和表中的存儲順序一致
### 2、修改
- 修改單表
```mysql
update 表名 set 字段=新值,字段=新值
【where 條件】
```
- 修改多表
```mysql
update 表1 別名1,表2 別名2
set 字段=新值，字段=新值
where 連接條件
and 篩選條件
```
### 3、刪除
- delete語句
單表刪除
```mysql
delete from 表名 【where 篩選條件】
```
? 多表刪除
```mysql
delete 別名1，別名2
from 表1 別名1，表2 別名2
where 連接條件
and 篩選條件;
```
- truncate語句
```mysql
truncate table 表名
```
## 六、數據定義語言DDL
### 1、庫和表的管理
- 庫的管理
```mysql
一、創建庫
create database 庫名
二、刪除庫
drop database 庫名
```
- 表的管理
創建表
```mysql
CREATE TABLE IF NOT EXISTS stuinfo(
stuId INT,
stuName VARCHAR(20),
gender CHAR,
bornDate DATETIME
);
```
? 修改表
```mysql
語法：ALTER TABLE 表名 ADD|MODIFY|DROP|CHANGE COLUMN 字段名 【字段類型】;
#①修改字段名
ALTER TABLE studentinfo CHANGE COLUMN sex gender CHAR;
#②修改表名
ALTER TABLE stuinfo RENAME [TO] studentinfo;
#③修改字段類型和列級約束
ALTER TABLE studentinfo MODIFY COLUMN borndate DATE ;
#④添加字段
ALTER TABLE studentinfo ADD COLUMN email VARCHAR(20) first;
#⑤刪除字段
ALTER TABLE studentinfo DROP COLUMN email;
```
? 刪除表
```mysql
DROP TABLE [IF EXISTS] studentinfo;
```
### 2、數值類型
- 數值類型
```mysql
整型：
tinyint 1字節 -128~127
smallint 2字節 -32768~32767
mediumint 3字節 -2^31~2^31-1
int 4字節 -2^63~2^63-1
bigint 8字節
浮點型：
float 4字節
double 8字節
decimal(M,D) M指定總位數，D指定小數位數
```
- 日期類型
```mysql
date 3字節 YYYY-MM-DD
time 3字節 HH:MM:SS
year 1字節 YYYY
datetime 8字節 YYYY-MM-DD HH:MM:SS
timestamp 4字節 YYYYMMDD HHMMSS
```
- 字符串類型
```mysql
char 0~255字節 固定長度字符串
varchar 0~65535字節 可變長度字符串
tinyblob 0~255字節 不超過255個字符的二進制字符串
tinytext 0~255字節 短文本字符串
blob 0~65535字節 二進制形式的長文本數據
text 0~65535字節 長文本數據
mediumblob 二進制形式的中等長度文本數據
mediumtext 中等長度文本數據
longblob 二進制形式的極大文本數據
longtext 極大文本數據
```
### 3、標識列
? 又稱為自增長列，可以不用手動的插入值，系統提供默認的序列值。
1.標識列要求是一個key，不一定要和主鍵搭配。
2.一個表至多有一個標識列。
3.標識列只能是數值型。
4.標識列可以通過set auto_increment_increment=3;設置步長
```mysql
mysql> CREATE TABLE insect
-> (
-> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (id),
-> name VARCHAR(30) NOT NULL, # type of insect
-> date DATE NOT NULL, # date collected
-> origin VARCHAR(30) NOT NULL # where collected
);
```
### 4、常見約束
? 一種限制，用于限制表中的數據，為了保證表中的數據的正確性和可靠性。
添加約束的時機：1.創建表時；2.修改表時
```mysql
分類：六大約束
NOT NULL：非空，用于保證該字段的值不能為空
比如姓名、學號等
DEFAULT:默認，用于保證該字段有默認值
比如性別
PRIMARY KEY:主鍵，用于保證該字段的值具有唯一性，并且非空
比如學號、員工編號等
UNIQUE:唯一，用于保證該字段的值具有唯一性，可以為空
比如座位號
CHECK:檢查約束【mysql中不支持】
比如年齡、性別
FOREIGN KEY:外鍵，用于限制兩個表的關系，用于保證該字段的值必須來自于主表的關聯列的值
在從表添加外鍵約束，用于引用主表中某列的值
比如學生表的專業編號，員工表的部門編號，員工表的工種編號
約束的添加分類：
列級約束：
六大約束語法上都支持，但外鍵約束沒有效果
表級約束：
除了非空、默認，其他的都支持
主鍵和唯一的大對比：
保證唯一性 是否允許為空 一個表中可以有多少個 是否允許組合
主鍵 √ × 至多有1個 √，但不推薦
唯一 √ √ 可以有多個 √，但不推薦
外鍵：
1、要求在從表設置外鍵關系
2、從表的外鍵列的類型和主表的關聯列的類型要求一致或兼容，名稱無要求
3、主表的關聯列必須是一個key（一般是主鍵或唯一）
4、插入數據時，先插入主表，再插入從表
刪除數據時，先刪除從表，再刪除主表
CREATE TABLE 表名(
字段名 字段類型 列級約束,
字段名 字段類型,
表級約束
)
CREATE DATABASE students;
#一、創建表時添加約束
#1.添加列級約束
/*
語法：
直接在字段名和類型后面追加 約束類型即可。
只支持：默認、非空、主鍵、唯一
*/
USE students;
DROP TABLE stuinfo;
CREATE TABLE stuinfo(
id INT PRIMARY KEY,#主鍵
stuName VARCHAR(20) NOT NULL UNIQUE,#非空
gender CHAR(1) CHECK(gender='男' OR gender ='女'),#檢查
seat INT UNIQUE,#唯一
age INT DEFAULT 18,#默認約束
majorId INT REFERENCES major(id)#外鍵
);
CREATE TABLE major(
id INT PRIMARY KEY,
majorName VARCHAR(20)
);
#查看stuinfo中的所有索引，包括主鍵、外鍵、唯一
SHOW INDEX FROM stuinfo;
#2.添加表級約束
/*
語法：在各個字段的最下面
【constraint 約束名】 約束類型(字段名)
*/
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT,
CONSTRAINT pk PRIMARY KEY(id),#主鍵
CONSTRAINT uq UNIQUE(seat),#唯一鍵
CONSTRAINT ck CHECK(gender ='男' OR gender = '女'),#檢查
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外鍵
);
SHOW INDEX FROM stuinfo;
#通用的寫法：★
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY,
stuname VARCHAR(20),
sex CHAR(1),
age INT DEFAULT 18,
seat INT UNIQUE,
majorid INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);
#二、修改表時添加約束
/*
1、添加列級約束
alter table 表名 modify column 字段名 字段類型 新約束;
2、添加表級約束
alter table 表名 add 【constraint 約束名】 約束類型(字段名) 【外鍵的引用】;
*/
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT
)
DESC stuinfo;
#1.添加非空約束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
#2.添加默認約束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#3.添加主鍵
#①列級約束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#②表級約束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
#4.添加唯一
#①列級約束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#②表級約束
ALTER TABLE stuinfo ADD UNIQUE(seat);
#5.添加外鍵
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);
#三、修改表時刪除約束
#1.刪除非空約束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
#2.刪除默認約束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;
#3.刪除主鍵
ALTER TABLE stuinfo DROP PRIMARY KEY;
#4.刪除唯一
ALTER TABLE stuinfo DROP INDEX seat
#5.刪除外鍵
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
SHOW INDEX FROM stuinfo;
```
### 5、視圖
? 相當于一張虛擬表，不存放實際數據，只保存了sql邏輯。
- 創建視圖
```mysql
create view myv1
as
select last_name,department_name,job_title
from employees e
join departments d
join jobs j
on e.department_id=d.department_id and j.job_id=e.job_id;
```
- 修改視圖
```mysql
#方式一
create or replace view myv3
as
select avg(salary),job_id
from employees
group by job_id;
#方式二
alter view myv3
as
select * from employees;
```
- 刪除視圖
```mysql
drop view myv1,myv2;
```
- 查看視圖
```mysql
show create view myv3;
```
- 視圖的數據更新
```mysql
#1.插入
INSERT INTO myv1 VALUES('張飛','zf@qq.com');
#2.修改
UPDATE myv1 SET last_name = '張無忌' WHERE last_name='張飛';
#3.刪除
DELETE FROM myv1 WHERE last_name = '張無忌';
#具備以下特點的視圖不允許更新
#①包含以下關鍵字的sql語句：分組函數、distinct、group by、having、union或者union all
CREATE OR REPLACE VIEW myv1
AS
SELECT MAX(salary) m,department_id
FROM employees
GROUP BY department_id;
SELECT * FROM myv1;
#②常量視圖
CREATE OR REPLACE VIEW myv2
AS
SELECT 'john' NAME;
SELECT * FROM myv2;
#③Select中包含子查詢
CREATE OR REPLACE VIEW myv3
AS
SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工資
FROM departments;
#④join
CREATE OR REPLACE VIEW myv4
AS
SELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;
#⑤from一個不能更新的視圖
CREATE OR REPLACE VIEW myv5
AS
SELECT * FROM myv3;
#⑥where子句的子查詢引用了from子句中的表
CREATE OR REPLACE VIEW myv6
AS
SELECT last_name,email,salary
FROM employees
WHERE employee_id IN(
SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL
);
```
### 6、變量
```mysql
/*
系統變量：
全局變量
會話變量
自定義變量：
用戶變量
局部變量
*/
#一、系統變量
/*
說明：變量由系統定義，不是用戶定義，屬于服務器層面
注意：全局變量需要添加global關鍵字，會話變量需要添加session關鍵字，如果不寫，默認會話級別
使用步驟：
1、查看所有系統變量
show global|【session】variables;
2、查看滿足條件的部分系統變量
show global|【session】 variables like '%char%';
3、查看指定的系統變量的值
select @@global|【session】系統變量名;
4、為某個系統變量賦值
方式一：
set global|【session】系統變量名=值;
方式二：
set @@global|【session】系統變量名=值;
*/
#1》全局變量
/*
作用域：針對于所有會話（連接）有效，但不能跨重啟
*/
#①查看所有全局變量
SHOW GLOBAL VARIABLES;
#②查看滿足條件的部分系統變量
SHOW GLOBAL VARIABLES LIKE '%char%';
#③查看指定的系統變量的值
SELECT @@global.autocommit;
#④為某個系統變量賦值
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;
#2》會話變量
/*
作用域：針對于當前會話（連接）有效
*/
#①查看所有會話變量
SHOW SESSION VARIABLES;
#②查看滿足條件的部分會話變量
SHOW SESSION VARIABLES LIKE '%char%';
#③查看指定的會話變量的值
SELECT @@autocommit;
SELECT @@session.tx_isolation;
#④為某個會話變量賦值
SET @@session.tx_isolation='read-uncommitted';
SET SESSION tx_isolation='read-committed';
#二、自定義變量
/*
說明：變量由用戶自定義，而不是系統提供的
使用步驟：
1、聲明
2、賦值
3、使用（查看、比較、運算等）
*/
#1》用戶變量
/*
作用域：針對于當前會話（連接）有效，作用域同于會話變量
*/
#賦值操作符：=或:=
#①聲明并初始化
SET @變量名=值;
SET @變量名:=值;
SELECT @變量名:=值;
#②賦值（更新變量的值）
#方式一：
SET @變量名=值;
SET @變量名:=值;
SELECT @變量名:=值;
#方式二：
SELECT 字段 INTO @變量名
FROM 表;
#③使用（查看變量的值）
SELECT @變量名;
#2》局部變量
/*
作用域：僅僅在定義它的begin end塊中有效
應用在 begin end中的第一句話
*/
#①聲明
DECLARE 變量名 類型;
DECLARE 變量名 類型 【DEFAULT 值】;
#②賦值（更新變量的值）
#方式一：
SET 局部變量名=值;
SET 局部變量名:=值;
SELECT 局部變量名:=值;
#方式二：
SELECT 字段 INTO 具備變量名
FROM 表;
#③使用（查看變量的值）
SELECT 局部變量名;
#案例：聲明兩個變量，求和并打印
#用戶變量
SET @m=1;
SET @n=1;
SET @sum=@m+@n;
SELECT @sum;
#局部變量
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE SUM INT;
SET SUM=m+n;
SELECT SUM;
#用戶變量和局部變量的對比
作用域 定義位置 語法
用戶變量 當前會話 會話的任何地方 加@符號，不用指定類型
局部變量 定義它的BEGIN END中 BEGIN END的第一句話 一般不用加@,需要指定類型
```
### 7、存儲過程和函數
? 類似于java中的方法，提高代碼的重用性，簡化操作。
- 存儲過程
```mysql
#存儲過程
/*
含義：一組預先編譯好的SQL語句的集合，理解成批處理語句
1、提高代碼的重用性
2、簡化操作
3、減少了編譯次數并且減少了和數據庫服務器的連接次數，提高了效率
*/
#一、創建語法
CREATE PROCEDURE 存儲過程名(參數列表)
BEGIN
存儲過程體（一組合法的SQL語句）
END
#注意：
/*
1、參數列表包含三部分
參數模式 參數名 參數類型
舉例：
in stuname varchar(20)
參數模式：
in：該參數可以作為輸入，也就是該參數需要調用方傳入值
out：該參數可以作為輸出，也就是該參數可以作為返回值
inout：該參數既可以作為輸入又可以作為輸出，也就是該參數既需要傳入值，又可以返回值
2、如果存儲過程體僅僅只有一句話，begin end可以省略
存儲過程體中的每條sql語句的結尾要求必須加分號。
存儲過程的結尾可以使用 delimiter 重新設置
語法：
delimiter 結束標記
案例：
delimiter $
*/
#二、調用語法
CALL 存儲過程名(實參列表);
#1.空參列表
#案例：插入到admin表中五條記錄
SELECT * FROM admin;
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username,`password`)
VALUES('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');
END;
#調用
CALL myp1();
#2.創建帶in模式參數的存儲過程
#案例1：創建存儲過程實現 根據女神名，查詢對應的男神信息
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
WHERE b.name=beautyName;
END;
#調用
CALL myp2('柳巖');
#案例2 ：創建存儲過程實現，用戶是否登錄成功
CREATE PROCEDURE myp4(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
DECLARE result INT DEFAULT 0;#聲明并初始化
SELECT COUNT(*) INTO result#賦值
FROM admin
WHERE admin.username = username
AND admin.password = PASSWORD;
SELECT IF(result>0,'成功','失敗');#使用
END;
#調用
CALL myp3('張飛','8888');
#3.創建out 模式參數的存儲過程
#案例1：根據輸入的女神名，返回對應的男神名
CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
SELECT bo.boyname INTO boyname
FROM boys bo
RIGHT JOIN
beauty b ON b.boyfriend_id = bo.id
WHERE b.name=beautyName;
END;
#案例2：根據輸入的女神名，返回對應的男神名和魅力值
CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT)
BEGIN
SELECT boys.boyname ,boys.usercp INTO boyname,usercp
FROM boys
RIGHT JOIN
beauty b ON b.boyfriend_id = boys.id
WHERE b.name=beautyName ;
END;
#調用
CALL myp7('小昭',@name,@cp);
SELECT @name,@cp;
#4.創建帶inout模式參數的存儲過程
#案例1：傳入a和b兩個值，最終a和b都翻倍并返回
CREATE PROCEDURE myp8(INOUT a INT ,INOUT b INT)
BEGIN
SET a=a*2;
SET b=b*2;
END;
#調用
SET @m=10;
SET @n=20;
CALL myp8(@m,@n);
SELECT @m,@n;
#三、刪除存儲過程
#語法：drop procedure 存儲過程名
DROP PROCEDURE p1;
#四、查看存儲過程的信息
SHOW CREATE PROCEDURE myp2;
```
- 函數
```mysql
/*
含義：一組預先編譯好的SQL語句的集合，理解成批處理語句
1、提高代碼的重用性
2、簡化操作
3、減少了編譯次數并且減少了和數據庫服務器的連接次數，提高了效率
區別：
存儲過程：可以有0個返回，也可以有多個返回，適合做批量插入、批量更新
函數：有且僅有1 個返回，適合做處理數據后返回一個結果
*/
#一、創建語法
CREATE FUNCTION 函數名(參數列表) RETURNS 返回類型
BEGIN
函數體
END
/*
注意：
1.參數列表 包含兩部分：
參數名 參數類型
2.函數體：肯定會有return語句，如果沒有會報錯
如果return語句沒有放在函數體的最后也不報錯，但不建議
return 值;
3.函數體中僅有一句話，則可以省略begin end
4.使用 delimiter語句設置結束標記
*/
#二、調用語法
SELECT 函數名(參數列表)
#1.無參有返回
#案例：返回公司的員工個數
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0;#定義局部變量
SELECT COUNT(*) INTO c#賦值
FROM employees;
RETURN c;
END;
SELECT myf1();
#2.有參有返回
#案例1：根據員工名，返回它的工資
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
SET @sal=0;#定義用戶變量
SELECT salary INTO @sal #賦值
FROM employees
WHERE last_name = empName;
RETURN @sal;
END;
SELECT myf2('k_ing');
#案例2：根據部門名，返回該部門的平均工資
CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
DECLARE sal DOUBLE ;
SELECT AVG(salary) INTO sal
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name=deptName;
RETURN sal;
END;
SELECT myf3('IT');
#三、查看函數
SHOW CREATE FUNCTION myf3;
#四、刪除函數
DROP FUNCTION myf3;
#案例
#一、創建函數，實現傳入兩個float，返回二者之和
CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
DECLARE SUM FLOAT DEFAULT 0;
SET SUM=num1+num2;
RETURN SUM;
END;
SELECT test_fun1(1,2);
```
## 七、數據控制語言DCL
### 1、事務
? 一個或一組sql語句組成一個執行單元，這個執行單元要么全部執行，要么全部不執行。
```mysql
事務的特性：
ACID
原子性：一個事務不可再分割，要么都執行要么都不執行
一致性：一個事務執行會使數據從一個一致狀態切換到另外一個一致狀態
隔離性：一個事務的執行不受其他事務的干擾
持久性：一個事務一旦提交，則會永久的改變數據庫的數據.
事務的創建
隱式事務：事務沒有明顯的開啟和結束的標記
比如insert、update、delete語句
delete from 表 where id =1;
顯式事務：事務具有明顯的開啟和結束的標記
前提：必須先設置自動提交功能為禁用
set autocommit=0;
步驟1：開啟事務
set autocommit=0;
start transaction;可選的
步驟2：編寫事務中的sql語句(select insert update delete)
語句1;
語句2;
...
步驟3：結束事務
commit;提交事務
rollback;回滾事務
savepoint 節點名;設置保存點
事務的隔離級別：
臟讀 不可重復讀 幻讀
read uncommitted： √ √ √
read committed(oracle默認級別)：× √ √
repeatable read(mysql默認級別)：× × √
serializable： × × ×
mysql中默認 第三個隔離級別 repeatable read
oracle中默認第二個隔離級別 read committed
查看隔離級別
select @@tx_isolation;
設置隔離級別
set session|global transaction isolation level 隔離級別;
開啟事務的語句;
update 表 set 張三豐的余額=500 where name='張三豐'
update 表 set 郭襄的余額=1500 where name='郭襄'
結束事務的語句;
*/
SHOW VARIABLES LIKE 'autocommit';
SHOW ENGINES;
#1.演示事務的使用步驟
#開啟事務
SET autocommit=0;
START TRANSACTION;
#編寫一組事務的語句
UPDATE account SET balance = 1000 WHERE username='張無忌';
UPDATE account SET balance = 1000 WHERE username='趙敏';
#結束事務
ROLLBACK;
#commit;
SELECT * FROM account;
#2.演示事務對于delete和truncate的處理的區別
SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;
#3.演示savepoint 的使用
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=1;
SAVEPOINT a;#設置保存點
DELETE FROM account WHERE id=2;
ROLLBACK TO a;#回滾到保存點
SELECT * FROM account;
```
### 2、流程控制結構
```mysql
/*
順序、分支、循環
*/
#一、分支結構
#1.if函數
/*
語法：if(條件,值1，值2)
功能：實現雙分支
應用在begin end中或外面
*/
#2.case結構
/*
語法：
情況1：類似于switch
case 變量或表達式
when 值1 then 語句1;
when 值2 then 語句2;
...
else 語句n;
end
情況2：
case
when 條件1 then 語句1;
when 條件2 then 語句2;
...
else 語句n;
end
應用在begin end 中或外面
*/
#3.if結構
/*
語法：
if 條件1 then 語句1;
elseif 條件2 then 語句2;
....
else 語句n;
end if;
功能：類似于多重if
只能應用在begin end 中
*/
#案例1：創建函數，實現傳入成績，如果成績>90,返回A，如果成績>80,返回B，如果成績>60,返回C，否則返回D
CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
DECLARE ch CHAR DEFAULT 'A';
IF score>90 THEN SET ch='A';
ELSEIF score>80 THEN SET ch='B';
ELSEIF score>60 THEN SET ch='C';
ELSE SET ch='D';
END IF;
RETURN ch;
END;
SELECT test_if(87);
#案例2：創建存儲過程，如果工資<2000,則刪除，如果5000>工資>2000,則漲工資1000，否則漲工資500
CREATE PROCEDURE test_if_pro(IN sal DOUBLE)
BEGIN
IF sal<2000 THEN DELETE FROM employees WHERE employees.salary=sal;
ELSEIF sal>=2000 AND sal<5000 THEN UPDATE employees SET salary=salary+1000 WHERE employees.`salary`=sal;
ELSE UPDATE employees SET salary=salary+500 WHERE employees.`salary`=sal;
END IF;
END;
CALL test_if_pro(2100);
#案例1：創建函數，實現傳入成績，如果成績>90,返回A，如果成績>80,返回B，如果成績>60,返回C，否則返回D
CREATE FUNCTION test_case(score FLOAT) RETURNS CHAR
BEGIN
DECLARE ch CHAR DEFAULT 'A';
CASE
WHEN score>90 THEN SET ch='A';
WHEN score>80 THEN SET ch='B';
WHEN score>60 THEN SET ch='C';
ELSE SET ch='D';
END CASE;
RETURN ch;
END;
SELECT test_case(56);
#二、循環結構
/*
分類：
while、loop、repeat
循環控制：
iterate類似于 continue，繼續，結束本次循環，繼續下一次
leave 類似于 break，跳出，結束當前所在的循環
*/
#1.while
/*
語法：
【標簽:】while 循環條件 do
循環體;
end while【 標簽】;
while(循環條件){
循環體;
}
*/
#2.loop
/*
語法：
【標簽:】loop
循環體;
end loop 【標簽】;
可以用來模擬簡單的死循環
*/
#3.repeat
/*
語法：
【標簽：】repeat
循環體;
until 結束循環的條件
end repeat 【標簽】;
*/
#1.沒有添加循環控制語句
#案例：批量插入，根據次數插入到admin表中多條記錄
DROP PROCEDURE pro_while1$
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=insertCount DO
INSERT INTO admin(username,`password`) VALUES(CONCAT('Rose',i),'666');
SET i=i+1;
END WHILE;
END;
CALL pro_while1(100);
/*
int i=1;
while(i<=insertcount){
//插入
i++;
}
*/
#2.添加leave語句
#案例：批量插入，根據次數插入到admin表中多條記錄，如果次數>20則停止
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<=insertCount DO
INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
IF i>=20 THEN LEAVE a;
END IF;
SET i=i+1;
END WHILE a;
END;
CALL test_while1(100);
#3.添加iterate語句
#案例：批量插入，根據次數插入到admin表中多條記錄，只插入偶數次
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 0;
a:WHILE i<=insertCount DO
SET i=i+1;
IF MOD(i,2)!=0 THEN ITERATE a;
END IF;
INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
END WHILE a;
END;
CALL test_while1(100);
/*
int i=0;
while(i<=insertCount){
i++;
if(i%2==0){
continue;
}
插入
}
*/
```
