# 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;
}
插入
}
*/
```
