MySQL、基础查询、视图、事务、存储过程、流程控制

MySQL

一、MySQL服务的启动和停止

​ 方式一：计算机——右击管理——服务
方式二：通过管理员身份运行
net start 服务名（启动服务）
net stop 服务名（停止服务）

二、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.查询的结果是一个虚拟的表格

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
条件 ;

一、条件运算符
	> < >= <= = != <>
-- 查询工资大于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、排序查询

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、常见函数

一、单行函数

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当前连接用户	

二、分组函数

	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

-- 查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
-- SELECT manager_id,MIN(salary)
-- FROM employees
-- GROUP BY manager_id
-- HAVING MIN(salary)>=6000 AND manager_id IS NOT NULL

6、连接查询

笛卡尔乘积：如果连接条件省略或无效则会出现
解决办法：添加上连接条件

1、传统模式下的连接 ：等值连接——非等值连接

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关键字实现连接

含义：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语法

-- 选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号
-- 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语法

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、子查询的结果为空

-- 查询和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

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代表不去重

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、修改

• 修改单表

update 表名 set 字段=新值,字段=新值
【where 条件】

• 修改多表

update 表1 别名1,表2 别名2
set 字段=新值，字段=新值
where 连接条件
and 筛选条件

3、删除

• delete语句

  单表删除

delete from 表名 【where 筛选条件】

​ 多表删除

delete 别名1，别名2
	from 表1 别名1，表2 别名2
	where 连接条件
	and 筛选条件;

• truncate语句

truncate table 表名

六、数据定义语言DDL

1、库和表的管理

• 库的管理

一、创建库
create database 库名
二、删除库
drop database 库名

• 表的管理

  创建表

CREATE TABLE IF NOT EXISTS stuinfo(
	stuId INT,
	stuName VARCHAR(20),
	gender CHAR,
	bornDate DATETIME
);

​ 修改表

语法：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;

​ 删除表

DROP TABLE [IF EXISTS] studentinfo;

2、数值类型

• 数值类型

整型：
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指定小数位数

• 日期类型

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

• 字符串类型

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> 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.修改表时

分类：六大约束
	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逻辑。

• 创建视图

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;

• 修改视图

#方式一
create or replace view myv3
as
select avg(salary),job_id
from employees
group by job_id;
#方式二
alter view myv3
as
select * from employees;

• 删除视图

drop view myv1,myv2;

• 查看视图

show create view myv3;

• 视图的数据更新

#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、变量

/*
系统变量：
	全局变量
	会话变量

自定义变量：
	用户变量
	局部变量
*/
#一、系统变量
/*
说明：变量由系统定义，不是用户定义，属于服务器层面
注意：全局变量需要添加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中的方法，提高代码的重用性，简化操作。

• 存储过程

#存储过程
/*
含义：一组预先编译好的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;

• 函数

/*
含义：一组预先编译好的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语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。

事务的特性：
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、流程控制结构

/*
顺序、分支、循环
*/
#一、分支结构
#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;
	}
	插入
}
*/