数据库 基础 三

MySQL基础

DML（Data Manipulation Language）数据操作语言 的学习

插入语句

语法：

方式一：★

• insert into 表名 [(列名, ...)]
• values(val, ...);

方式二：

• insert into 表名
• set 列名=值, 列名=值, ...;

注意事项：

1. 插入的值的类型要与列的类型一致或兼容
2. 可以为 null的列
3. 列的顺序可以调换
4. 字段列数和值列数必须一致
5. 可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致

两种方式对比：

1. 方式一支持插入多行

   INSERT INTO
      beauty [(字段名, ...)]
   VALUES(130, '唐探1', '女', '1990-4-22', '18925632141', NULL, 2),
   (131, '唐探2', '女', '1990-4-22', '18925632141', NULL, 2),
   (132, '唐探3', '女', '1990-4-22', '18925632141', NULL, 2),
   (...);
   

2. 方式一支持子查询

   # 1.
   INSERT INTO
    beauty(id, name, phone)
   SELECT 
    26, '喜喜', '15623301546';
   
   # 2.
    INSERT INTO
            beauty(id, name, phone)
    SELECT
            id, boyname, '15603222658'
    FROM
            boys
    WHERE
            id < 3;
   

实例：

# 插入 INSERT

# 方式一：经典的插入
# 1.
INSERT INTO
	beauty(id, name, sex, borndate, phone, photo, boyfriend_id)
VALUES(13, '唐探', '女', '1990-4-22', '18925632141', NULL, 2);

SELECT
	*
FROM
	beauty;

# 2.可以为 null的列，如何插入值：

# 方式一：用 NULL替代
INSERT INTO
	beauty(id, name, sex, borndate, phone, photo, boyfriend_id)
VALUES(13, '唐探', '女', '1990-4-22', '18925632141', NULL, 2);

# 方式二：忽略可以为 NULL的列
INSERT INTO
	beauty(id, name, sex, borndate, phone, boyfriend_id)
VALUES(14, '唐糖', '女', '1990-12-22', '18925252141', 3);

# 3.列的顺序可以调换
INSERT INTO
	beauty(name, sex, id, phone)
VALUES('蒋欣', '女', 15, '15666666212');

# 4.字段列数和值列数必须一致
INSERT INTO
	beauty(name, sex, id, phone, boyfriend_id)
VALUES('关关', '女', 16, '15666666212', NULL);

# 5.可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致
INSERT INTO
	beauty
VALUES(17, '张飞飞', '男', NULL, '119', NULL, NULL);

# 插入语法方式二：经典的插入

INSERT INTO
	beauty
SET
	id=18, sex='女', name='娜扎', phone='12345698710';

修改语句

修改单表的记录

语法：

• update 表名
• set 列=新值, 列=新值, ...
• where 筛选条件;

执行顺序：update 表 -> where 筛选条件 -> set 列=值

实例：

# UPDATE

# 更改 name中有姓唐的电话
UPDATE
	beauty
SET
	phone = 12325612580
WHERE
	name LIKE '唐%';

# 修改 boys表中 id为 2的名称为张飞，值为 10
UPDATE
	boys
SET
	boyName = '张飞', userCP = 10
WHERE
	id = 2;

修改多表的记录

92语法：

• update 表名 as 别名, 表2 as 别名
• set 列=值, ...
• where 连接条件
• and 筛选条件;

99语法：

• update 表1 as 别名
• inner/left/right join 表2 as 别名
• on 连接条件
• set 列=值, ...
• where 筛选条件;

实例：

# 修改张无忌的女朋友的手机号为 114
UPDATE
	beauty AS bg
INNER JOIN
	boys AS b
ON
	b.id = bg.boyfriend_id
SET
	phone = 114
WHERE
	boyName = '张无忌';

# 修改没有男朋友的女神的男朋友编号为 2号
UPDATE
	beauty be
LEFT JOIN
	boys b
ON
	be.boyfriend_id = b.id
SET
	be.boyfriend_id = 2
WHERE
	b.id IS NULL;

删除语句

单表的删除

语法：

方式一：delete - 删除某一行

• delete from 表名
• where 筛选条件;

方式二：truncate - 删除整个表中的数据

• truncate table 表名; //（不属于事务，执行后会自动提交（commit））

多表的删除

92语法：

方式一：

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

方式二：

• truncate table 表名

# 单表的删除

# 删除手机号以 9结尾的女神信息
DELETE FROM
	beauty
WHERE
	phone LIKE '%4';

99语法：

方式一：

• delete 表1别名, 表2别名, ...
• from 表1 别名
• inner|left|right join 表2 别名, ...
• on 连接条件
• where 筛选条件;

方式二：

• truncate table 表名

# 删除张无忌的女朋友的信息
DELETE
	be
FROM
	beauty be
INNER JOIN
	boys bo
ON
	be.boyfriend_id = bo.id
WHERE
	bo.boyName = '张无忌';

# 删除 boyfriend_id为 3的女朋友和男盆友
DELETE
	bo, be
FROM
	beauty be
INNER JOIN
	boys bo
ON
	bo.id = be.boyfriend_id
WHERE
	bo.id = 3;
	

delete VS truncate ★

delete from 表名; VS truncate table 表名;

1. delete删除，可以加 where条件，truncate不能加任何条件
2. truncate清空数据，效率高一点
3. 如果被删除的表中有自增长列，使用 delete删除后，再插入数据，自增长列的值从 断点开始，而 truncate删除后，再插入数据，自增长列的值从 1开始
4. truncate删除无返回值，delete删除有返回值，提示有几行受影响
5. truncate删除不能回滚，delete删除可以回滚

DDL（Data Define Language）数据定义语言 的学习

库和表的管理和操作

库和表的管理

• 用于判断 库和表 是否存在，增强代码的健壮性
  • if exists：仅能用库和表
  • if not exists：仅能用库和表

库的管理

1. 创建：create 语法：CREATE DATABASE [IF NOT EXISTS] 库名 [character set 字符集名];

# 创建库 books
CREATE DATABASE IF NOT EXISTS books;

2. 修改：alter

# 更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;

3. 删除：drop 语法：DROP DATABASE [IF EXISTS] 库名;

# 库的删除
DROP DATABASE IF EXISTS books;

表的管理

1. 创建：create

• 语法：
• CREATE TABLE [IF NOT EXISTS] 表名(
• 列名 列的类型[(长度) 约束],
• 列名 列的类型[(长度) 约束],
• ...
• 列名 列的类型[(长度) 约束]
• );

# 创建 book表
USE books;
CREATE TABLE book(
	id INT,
	bName VARCHAR(20),
	price DOUBLE,
	authorId INT,
	publishDate DATETIME
);

# 查看表的字段设置
DESC book;

# 创建 author表
CREATE TABLE author(
	id INT,
	au_name VARCHAR(20),
	nation VARCHAR(10)
);
DESC author;

2. 修改：alter

语法：alter table 表名 add|drop|modify|change column 列名 [类型、约束];

• 修改列名
  • alter table 表名 change column 原列名 新列名 类型;
• 修改类的类型或约束
  • ALTER TABLE 表名 MODIFY COLUMN 列名 类型 [ 约束 ];
• 新增列，并指定新增列的位置
  • ALTER TABLE 表名 ADD COLUMN 列名 类型 [ first | after 列名 ];
• 删除列
  • ALTER TABLE 表名 DROP COLUMN 列名;
• 修改表名
  • ALTER TABLE 原表名 RENAME TO 新表名;

# 1.修改列名
ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;

# 2.修改类的类型或约束
ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP;

# 3.新增列
ALTER TABLE author ADD COLUMN annual DOUBLE;

# 4.删除列
ALTER TABLE author DROP COLUMN annual;

# 5.修改表名
ALTER TABLE author RENAME TO book_authors;

3. 删除：drop

语法：DROP TABLE [IF EXISTS] 表名;

# 表的删除
DROP TABLE IF EXISTS book_author;

# 查看当前库的所有表
SHOW TABLES;

4. 复制，可以跨库复制表

仅仅复制表的结构

• CREATE TABLE IF NOT EXISTS authorCopy LIKE author;

复制表的结构 + 数据

• CREATE TABLE
• authorCopy2
• SELECT
• *
• FROM
• author;

USE books;
ALTER TABLE book_authors RENAME TO author;

INSERT INTO
	author
VALUES
	(1, '村上春树', '日本'),
	(2, '莫言', '中国'),
	(3, '冯唐', '中国'),
	(4, '金庸', '中国'),
	(5, '古龙', '中国');

# 表的复制

# 1.仅仅复制表的结构
CREATE TABLE IF NOT EXISTS authorCopy LIKE author;

# 2.复制表的结构 + 数据
CREATE TABLE
	authorCopy2
SELECT
	*
FROM
	author;

# 3.有选择的复制表的结构 + 数据
CREATE TABLE IF NOT EXISTS
	authorCopy3
SELECT
	id, au_name
FROM
	author
WHERE
	id < 4;

# 4.只复制部分结构
CREATE TABLE
	authorCopy4
SELECT
	id, au_name
FROM
	author
WHERE
	1 = 2;

建库建表的通用写法

建库：

# 建库
DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;

建表：

# 建表
DROP TABLE IF EXISTS 旧表名;
CREATE TABLE 表名(
	...
);

练习 - 表

# 创建表 dept1
CREATE TABLE IF NOT EXISTS dept1(
	id INT(7),
	name VARCHAR(25)
);
# 将表 departments中的数据插入新表 dept2中
CREATE TABLE IF NOT EXISTS
	dept2
SELECT
	*
FROM
	myemployees.departments;

# 创建表 emp5
CREATE TABLE IF NOT EXISTS emp5(
	id int(7),
	First_name VARCHAR(25),
	Last_name VARCHAR(25),
	dept_id INT(7)
);

# 将 列 Last_name的长度增加到 50
ALTER TABLE emp5 MODIFY COLUMN Last_name VARCHAR(50);
# 根据表 employees 创建 employees2
CREATE TABLE
	employess2
LIKE
	myemployees.employees;

# 删除 employees2
DROP TABLE employees2;
# 将 emp5重命名为 employees5
ALTER TABLE emp5 RENAME TO employees2;

# 在表 dept1和 employees2中添加新列 test_column，并检查所作操作
ALTER TABLE dept1 ADD COLUMN test_column VARCHAR(2);
ALTER TABLE employees2 ADD COLUMN test_column VARCHAR(2);

# 直接删除表 emp5中的列 dept_id
ALTER TABLE employees2 DROP COLUMN dept_id;

常见数据类型介绍

数值型：

整型：

• Tinyint 1byte
• Smallint 2byte
• Mediumint 3byte
• Int、integer 4byte
• Bigint 8byte 特点：

1. 如不设置无符号还是有符号，默认有符号，使用 UNSIGNED设置为无符号
2. 如果插入的数值超出了整型的范围，会插入临界值
3. 如果不设置长度，会有默认长度，长度代表显示的最大宽度，如果不够会在左边用 0填充，但必须搭配 zerofill使用

# 数值型

/*
特点：
1.如不设置无符号还是有符号，默认有符号，使用 UNSIGNED设置为无符号
2.如果插入的数值超出了整型的范围，会插入临界值
3.如果不设置长度，会有默认长度，长度代表显示的最大宽度，如果不够会在左边用0填充
*/
# 1.如何设置无符号和有符号
DROP TABLE IF EXISTS table_int;
CREATE TABLE IF NOT EXISTS table_int(
# 默认有符号
	t1 INT(7) ZEROFILL,
# 无符号 UNSIGNED
	t2 INT(7) ZEROFILL
);
DESC table_int;

INSERT INTO
	table_int
VALUES
	(723, 565);
SELECT
	*
FROM
	table_int;

小数：

• 定点数：精确度高
  • DEC(M,D)/DECIMAL(M,D)
• 浮点数
  • float(M,D) 4byte
  • double(M,D) 4byte 特点：

1. M：整数部位 + 小数部位，D：小数部位，如果超出范围，则插入临界值
2. M和 D都可以省略，如果是 decimal，则 M 默认为 10，D 默认为 0，如果是 float、double，则会根据插入的数值的精度来决定精度
3. 定点型的精度较高，如果要求插入数值的精度较高，如货币运算则考虑使用

# 小数

CREATE TABLE table_float(
	f1 FLOAT(5, 2),
	d1 DOUBLE(5, 2),
	de DECIMAL(5, 2)
);
DESC table_float;

INSERT INTO
	table_float
VALUES
	(123.45, 123.45, 123.45);
# 123.45     123.45	123.45

INSERT INTO
	table_float
VALUES
	(123.456, 123.456, 123.456);

# 123.46     123.46	123.46

INSERT INTO
	table_float
VALUES
	(123321.456, 123321.456, 123321.456);
# Err

SELECT
	*
FROM
	table_float;
        

字符型：

较短的文本：

• char(M)：M可以省略
• varchar(M)：M不嫩省略
• enum(, , ...)：用于保存枚举
• set(, , ...)：用于保存集合
• binary和 varbinary：用于保存较短的二进制（了解）

较长的文本：

• text
• blob(较大的二进制数据)

特点：

1. M 表示字符数
2. char 与 varchar的区别是，char是固定长度的字符，比较耗费空间，varchar是可变长度的字符，比较节省空间，char的效率要高于 varchar，char的默认长度为 1，varchar无默认值必须声明

# 字符
CREATE TABLE table_char(
	c1 ENUM('a', 'b', 'c')
);
DESC table_char;
INSERT INTO
	table_char
VALUES
	('a');
        
INSERT INTO
	table_char
VALUES
	('d');
# Err

INSERT INTO
	table_char
VALUES
	('A');
# 不区分大小写

SELECT
	*
FROM
	table_char;

# SET
CREATE TABLE table_set(
	s1 SET('a', 'b', 'c', 'd')
);
INSERT INTO table_set
VALUES('a'), ('a,b'), ('a,c,d'), ('A,B,C,D');
DESC table_set;
SELECT
	*
FROM
	table_set;

日期型：日期数值必须用单引号

• date：1000-01-01
• datetime：1000-01-01 00：00：00，不受时区影响
• timestamp：19700101010001，受时区影响
• time：00：00：00
• year：1901

# 日期
CREATE TABLE table_date(
	t1 DATETIME,
	t2 TIMESTAMP
);
INSERT INTO table_date
VALUES('2021-12-14 00:00:01', '20211214000001');
INSERT INTO table_date
VALUES(NOW(), NOW());

# 显示时区
SHOW VARIABLES LIKE 'time_zone';
# 更改 mysql的时区
SET time_zone = '+9:00'

SELECT
	*
FROM
	table_date;
# 2021-12-14 00:00:01	2021-12-14 00:00:01
# 2021-05-13 22:55:56	2021-05-13 22:55:56

数据类型的选择原则

1. 所选的类型越简单越好
2. 能保存数值的类型越小越好

常见约束

含义：一种限制，用于限制表中的数据，为了保证表中的数据准确性和可靠性；

注意：每列可以添加多个约束，注意不要矛盾

分类：六种

• not null：非空，用于保证该字段的值不能为空；如 姓名、学号
• default：默认值，用于保证该字段有默认值；如 性别
• primary key：主键，用于保证该字段的值具有唯一性，并且非空；如 学号、员工编号等
• unique：唯一，用于保证该字段的值具有为一性，可以为空；如 座位号
• check：检查约束（mysql不支持）
• foreign key：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表关联列的值

约束的添加分类：

• 列级约束：六大约束语法上都支持，但外键约束没有效果
• 表级约束：除了非空、默认值，其它都支持

列级约束：

语法：直接在字段名和类型后面追加 约束类型即可；

注意：只支持 默认、非空、主键、唯一

# 1.添加列级约束

CREATE DATABASE students;
USE students;
CREATE TABLE stuinfo(
	id INT PRIMARY KEY, # 主键
	stuName VARCHAR(20) NOT NULL, # 非空
	gender CHAR(1) CHECK(gender='男' OR gender='女'), # 检查
	seat INT UNIQUE, # 唯一
	age INT DEFAULT 18, # 默认约束
	majorId INT REFERENCES majors(id) # 外键
);

CREATE TABLE majors(

	id INT PRIMARY KEY,
	majorName VARCHAR(20) NOT NULL
);
DESC stuinfo;
DESC majors;

# 查看 stuinfo表中所有的索引，包括主键、外键、唯一
SHOW INDEX FROM stuinfo;

表级约束：

语法：在所有字段的最下面使用 [constraint 约束名(自定义)] 约束类型(字段名)

注意：非空约束和 默认约束不支持此写法

# 2.添加表级约束
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT,

	CONSTRAINT pkey PRIMARY KEY(id), # 主键
	CONSTRAINT uq UNIQUE(seat), # 唯一
	CONSTRAINT ck CHECK(gender='男' OR gender='女'), # 检查
	CONSTRAINT fk_majors FOREIGN KEY(majorid) REFERENCES majors(id) # 外键

	# 注意：非空和默认不支持此写法
-- 	CONSTRAINT nn NOT NULL(stuname),
-- 	CONSTRAINT de DEFAULT(age='18')
);

列级和表级约束的通用写法：

CREATE TABLE stuinfo(
	id INT PRIMARY KEY,
	stuname VARCHAR(20) NOT NULL,
	gender CHAR(1) CHECK(gender='男' OR gender='女'),
	seat INT UNIQUE,
	age INT DEFAULT 18,
	majorid INT,
	
	CONSTRAINT fk_majors FOREIGN KEY(majorid) REFERENCES majors(id)
);
SHOW INDEX FROM stuinfo;

主键 VS 唯一

标题	唯一性	可以为空	表中出现的次数	是否允许组合（不推荐）
主键	√	❌	至多有一个	√
唯一	√	√	可以有多个	√

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT,

	CONSTRAINT pkey PRIMARY KEY(id, stuname), # 组合主键
	CONSTRAINT uq UNIQUE(seat), # 唯一
	CONSTRAINT ck CHECK(gender='男' OR gender='女'), # 检查
	CONSTRAINT fk_majors FOREIGN KEY(majorid) REFERENCES majors(id) # 外键
);

INSERT INTO stuinfo
VALUES(2, 'Tom', '男', 24, 20, 1), (2, 'Jery', '女', 23, 22, 2);
INSERT INTO majors
VALUES(1, 'java'), (2, 'h5');

SHOW INDEX FROM stuinfo;

SELECT
	*
FROM
	stuinfo;

外键：

1. 要求从表设置外键关系，且从表外键值可以为空
2. 从表的外键列的类型和主表的关联列类型要求一致或兼容，名称无特殊要求
3. 主表关联列的约束必须是一个 key（一般是 主键 或 唯一）
4. 插入数据时，先插入主表，再插入从表；删除数据时，先删除从表，再删除主表
5. 级联删除 on delete cascade，级联置空 on delete set null

CREATE TABLE majors(

	id INT PRIMARY KEY,
	majorName VARCHAR(20) NOT NULL
);

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,
	
	CONSTRAINT fk_majors FOREIGN KEY(majorid) REFERENCES majors(id)
);

# 级联删除
alter table stuinfo add constraint fk_stu foreign key(majorid) references major(id) on delete cascade;
# 级联置空
alter table stuinfo add constraint fk_stu foreign key(majorid) references major(id) on delete set null;

标识列（自增长列）：AUTO_INCREMENT

含义：可以不用手动的插入值，系统提供默认的序列值，默认从 1开始，默认步长为 1

特点：

1. 标识列必须和 键搭配使用（主键、外键、唯一键）
2. 一个表至多有一个标识列
3. 标识列的类型只能是数值型
4. 通过 SET auto_increment_increment = 1;设置表示列的步长，也可以通过手动插入值设置起始值

实例：

# 标识列

# 1.创建表时设置标识列
CREATE TABLE table_identity(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20)
);
TRUNCATE TABLE table_identity;
INSERT INTO table_identity
VALUES(NULL, 'john');
INSERT INTO table_identity(name)
VALUES('lusy');

SHOW VARIABLES LIKE '%auto_%';
# 步长，可以更改
SET auto_increment_increment = 1;
# 起始值，不能改变
SET auto_increment_offset = 1;

/*
1.标识列必须和 键搭配使用（主键、外键、唯一键）
2.一个表至多有一个标识列
3.标识列的类型只能是数值型
4.通过 SET auto_increment_increment = 1;设置表示列的步长，也可以通过手动插入值设置起始值
*/

# 2.修改表时设置标识列
DROP TABLE table_identity;
CREATE TABLE table_identity(
	id INT PRIMARY KEY,
	name VARCHAR(20)
);
ALTER TABLE table_identity MODIFY COLUMN id INT AUTO_INCREMENT;

SHOW INDEX FROM table_identity;
INSERT INTO table_identity
VALUES(NULL, 'Jery');

# 3.修改表时删除标识列
ALTER TABLE table_identity MODIFY COLUMN id INT;

修改表时 添加约束

• 添加列级约束
  • alter table 表名 modify column 字段名 字段类型 新约束;
• 添加表级约束
  • 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
);
SHOW INDEX FROM stuinfo;
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;

# 4.添加唯一约束
# 4.1 列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
# 4.2 表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);

# 5.添加外键约束
ALTER TABLE stuinfo ADD constraint fk_major FOREIGN KEY(majorid) REFERENCES majors(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;
SHOW INDEX FROM stuinfo;
# 5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_majors;

练习-约束

• 列级约束 VS 表级约束

约束类型	位置	支持的约束类型	是否可以起约束名
列级约束	列的后面	语法都支持，但外键没有效果	不可以
表级约束	所有列的后面	默认和非空不支持，其它都支持	可以（主键除外）

# 向表 emp2的 id列中添加 主键约束
ALTER TABLE emp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id);
# 向表 emp2中 dept_id添加 外键约束
ALTER TABLE emp2 ADD CONSTRAINT fk_dept_id FOREIGN KEY(dept_id) REFERENCES dept2(id);
CREATE TABLE emp2(
	id INT,
	dept_id INT,
	CONSTRAINT my_emp_id_pk PRIMARY KEY(id),
	CONSTRAINT fk_dept2 FOREIGN KEY(dept_id) REFERENCES dept2(id)
);

# 向表 dept2的 id列中添加 主键
ALTER TABLE dept2 ADD CONSTRAINT my_dept_id_pk PRIMARY KEY(id);
CREATE TABLE dept2(
	id INT,
	CONSTRAINT my_dept_id_pk PRIMARY key(id)
);
SHOW INDEX FROM emp2;

常见约束

• not null：非空，该字段的值必填
• unique：唯一，该字段的值不可重复
• default：默认值
• check：检查，mysql不支持
• primary key：主键，该字段的值不可重复并且非空
• foreign key：外键，该字段的值引用了另外的表字端

TCL（Transaction Control Language）事务控制语言 的学习

• 事务：一个或一组 sql语句组成一个执行单元，这个执行单元要么全执行，要么全不执行

事务的 ACID(acid)特性 ★

• 原子性（Atomicity）： 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生
• 一致性（Consistency）： 事务必须使数据库从一个一致性状态变换到另一个一致性状态
• 隔离性（Isolation） 事务的隔离性是指一个事务的执行不能被其它事务干扰，既一个事务内部的操作及使用的数据对并发的其它事务是隔离的，并发执行的各个事务之间不能互相干扰
• 持久性（Durability） 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是持久性的，接下来的其它操作和数据库故障不应该对其有任何影响

事务的创建

隐式事务： 事务没有明显的开启和结束的标记，比如 insert、update、delete等语句

显式事务： 事务具有明显的开启和结束的标记；前提是必须先设置自动提交功能为禁用

• 步骤1：开启事务

  • #只针对当前的会话有效
  • SET AUTOCOMMIT = 0;
  • START TRANSACTION;
  • SHOW VARIABLES LIKE 'autocommit';

• 步骤2：编写事务的 sql语句（select、insert、update、delete）

  • 语句1;
  • 语句2;
  • ...;

• 步骤3：结束事务

  • COMMIT; # 提交事务
  • ROLLBACK; # 回滚事务

注意：事务中支持的 sql语句是 insert、update、delete、select等

实例：

# 显式事务使用步骤：
# 1.开启事务
SET AUTOCOMMIT = 0;
START TRANSACTION;

# 2.编写一组事务语句
UPDATE account
SET balance = 500
WHERE username = '张三丰';
UPDATE account
SET balance = 1500
WHERE username = '赵敏';

# 3.结束事务
COMMIT;
# ROLLBACK;

# 演示 SAVEPOINT 的使用
SET AUTOCOMMIT = 0;
START TRANSACTION;
DELETE FROM account WHERE id = 25;

# 设置保存点
SAVEPOINT a;
DELETE FROM account WHERE id = 26;
# 回滚到保存点
ROLLBACK TO a;
# id=26被保存下来，id=25被删除

数据库的隔离级别

对于同时运行的多个事务，当这些事务访问 数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题：

• 脏读：临时且无效的数据（没有被提交的字段），其它事务出现问题
• 不可重复读：刷新前后数据不一致（更新），其它事务出现问题
• 幻读：（插入），本事务出现问题

数据库提供的四种事务隔离级别：

• READ UNCOMMITED（读取未提交数据）：允许事务读取未被其它事务提交的变更，脏读、不可重复度和 幻读的问题都会出现

  • set session transaction isolation level READ UNCOMMITTED;

• READ COMMITED（读取已提交数据）：只允许事务读取已经被其它事务提交的变更，可以避免 脏读，但不能避免 不可重复读和 幻读问题

• REPEATABLE READ（可以重复读，默认，推荐）：确保同一事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其它事务对此字段进行更新，可以避免 脏读和不可重复读，但 幻读问题任然存在

• SERIALIZABLE（串行化）：禁止其它事务对此表执行 插入、更新和 删除操作，从而所有并发问题都可以避免，但性能十分低下

类型	脏读	不可重复读	幻读
read uncommitted	√	√	√
read committed	×	√	√
repeatable read（默认，推荐）	×	×	√
serializable	×	×	×

# mysql中默认 第三隔离级别 repeatable read
# oracle中默认 第二隔离级别 read committed

# 查看当前的隔离级别
select @@tx_isolation;

# 更改当前 mysql会话的隔离级别
set session transaction isolation level READ UNCOMMITED;
# 设置数据库系统全局的隔离级别
set global transaction isolation level read committed;

# 开启事务
set autocommit=0;
START TRANSACTION;

# 编写事务...

# 关闭事务
COMMIT;
# ROLLBACK;

delete和 truncate在事务使用时的区别

• delete执行后，数据还能回滚
• truncate执行后，自动 commit，数据不能回滚

# delete 数据还能恢复
SET autocommit = 0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;

# truncate 数据不能恢复
SET autocommit = 0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK;

MySql中的存储引擎

1. 概念：在 mysql中的数据用各种不同的技术存储在文件（或内存）中
2. 通过 show engines;来查看 mysql支持的存储引擎
3. 在 mysql中用的最多的存储引擎有：innodb、myisam、memory等；其中 innodb支持事务，而 myisam、memory等不支持事务

视图的讲解

含义： 虚拟表，和普通表一样使用，是通过表动态生成的数据；一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时 动态生成的，只保存 sql逻辑，不保存查询结果 语法：

• create view 视图名
• as
• 复杂查询语句;

应用场景：

• 多个地方用到同样的查询结果
• 该查询结果使用的 sql语句较复杂

视图的优点：

• 重用 sql语句
• 简化复杂的 sql语句，不必知道它的查询细节
• 保护数据提高安全性

视图的操作：

1. 创建视图：

   create view 视图名
   as
   查询语句;
   

2. 修改视图：

   // 方式一
   CREATE OR REPLACE VIEW 视图名
   AS
   查询语句;
   // 方式二
   ALTER VIEW 视图名
   AS
   查询语句;
   

3. 删除视图：

   DROP VIEW 视图名, 视图名, ...;
   

4. 查看视图：

   DESC 视图名;
   SHOW CREATE VIEW 视图名;
   

5. 视图（数据）的更新（基本不能使用）

• 注意：视图是用于查询的，而不是用于 增删改等操作
• 具备以下特点的视图是 不能更新的

  1. sql中包含以下关键字：group by、distinct、having、union、union all

  2. 常量视图

  3. SELECT中包含子查询

  4. JOIN

  5. FROM 一个不能更新的视图

  6. WHERE子句的子查询引用了 from子句中的表

实例：

# 1.视图的创建

# 查询姓 张的学生名和专业名
SELECT
	stuname, majorName
FROM
	stuinfo s
INNER JOIN
	majors m
ON
	s.majorid = m.id
WHERE
	s.stuname LIKE '张%';

INSERT INTO stuinfo
VALUES(1, '张风', '男', 22, 20, 1);

# 1.创建视图
CREATE VIEW v1
AS
SELECT
	stuname, majorName
FROM
	stuinfo s
INNER JOIN
	majors m
ON
	s.majorid = m.id;

# 2.使用视图
SELECT
	*
FROM
	v1
WHERE
	stuname LIKE '张%';

USE myemployees;

# 查询邮箱中包含 a字符的员工名、部门名和工种名
SELECT
	e.first_name, d.department_name, j.job_title, e.email
FROM
	employees e
LEFT JOIN
	departments d
ON
	e.department_id = d.department_id
LEFT JOIN
	jobs j
ON
	e.job_id = j.job_id
WHERE
	e.email LIKE '%a%';

# 创建 v1
CREATE VIEW v1
AS
SELECT
	e.first_name, d.department_name, j.job_title, e.email
FROM
	employees e
LEFT JOIN
	departments d
ON
	e.department_id = d.department_id
LEFT JOIN
	jobs j
ON
	e.job_id = j.job_id;

# 使用 v1
SELECT
	*
FROM
	v1
WHERE
	email LIKE '%a%';


# 查询各部门的平均工资级别
# 1.部门平均工资
SELECT
	AVG(e.salary) AS avg_salary, d.department_name
FROM
	employees e
LEFT JOIN
	departments d
ON
	e.department_id = d.department_id
GROUP BY e.department_id;
# 2.工资级别
SELECT
	jg.grade_level, avg_d.department_name
FROM
	job_grades jg
RIGHT JOIN
	(
	SELECT
		AVG(e.salary) AS avg_salary, d.department_name
	FROM
		employees e
	LEFT JOIN
		departments d
	ON
		e.department_id = d.department_id
	GROUP BY e.department_id
) AS avg_d
ON
	avg_d.avg_salary BETWEEN lowest_sal AND highest_sal;

# 创建平均值视图
CREATE VIEW avg_v
AS
SELECT
	AVG(e.salary) AS avg_salary, d.department_name
FROM
	employees e
LEFT JOIN
	departments d
ON
	e.department_id = d.department_id
GROUP BY e.department_id;
SELECT * FROM avg_v;
SELECT
	grade_level, avg_salary, department_name
FROM
	avg_v
LEFT JOIN
	job_grades
ON
	avg_salary BETWEEN lowest_sal AND highest_sal;

# 查询平均工资最低的部门信息
# 1.获取平居工资最低的部门
SELECT
	AVG(salary) avg_s, d.*
FROM
	employees e
LEFT JOIN
	departments d
ON
	d.department_id = e.department_id
GROUP BY
	department_id
ORDER BY
	avg_s ASC
LIMIT 0, 1;

# 2.
CREATE VIEW dep_v
AS
SELECT
	AVG(salary) avg_s, d.*
FROM
	employees e
LEFT JOIN
	departments d
ON
	d.department_id = e.department_id
GROUP BY
	department_id
ORDER BY
	avg_s ASC
LIMIT 0, 1;

SELECT
	*
FROM
	dep_v;

# 2.视图的修改

# 方式一
/*
CREATE OR REPLACE VIEW 视图名
AS
查询语句;
*/
CREATE OR REPLACE VIEW avg_j
AS
SELECT
	AVG(salary), job_id
FROM
	employees
GROUP BY
	job_id;

# 方式二
/*
ALTER VIEW 视图名
AS
查询语句;
*/
ALTER VIEW avg_j
AS
SELECT
	AVG(salary), job_id
FROM
	employees
GROUP BY
	job_id;

# 删除视图
/*
DROP VIEW 视图名, 视图名, ...;
*/

# 查看视图
DESC avg_j;
SHOW CREATE VIEW avg_j;

# 创建视图 emp_v1,要求查询电话号码以'011'开头的员工姓名和工资、邮箱
CREATE OR REPLACE VIEW emp_v1
AS
SELECT
	last_name, salary, email
FROM
	employees
WHERE
	phone_number LIKE '011%';

# 创建视图 emp_v2,要求查询部门的最高工资高于 12000的部门信息
CREATE OR REPLACE VIEW emp_v2
AS
SELECT
	MAX(salary) max_dep, department_id
FROM
	employees
GROUP BY
	department_id
HAVING
	max_dep > 12000;

SELECT
	d.*
FROM
	departments d
JOIN
	emp_v2 e2
ON
	e2.department_id = d.department_id;

# 视图的更新：更改视图中的数据

CREATE OR REPLACE VIEW myv1
AS
SELECT
	last_name, email, salary*12*(1 + IFNULL(commission_pct, 0)) AS 'annual salary'
FROM
	employees;

CREATE OR REPLACE VIEW myv1
AS
SELECT
	last_name, email
FROM
	employees;

# 视图中 插入数据,原始表中也会插入数据
INSERT INTO myv1 VALUES('张飞', 'zf@qq.com');
# 视图中 修改数据，原始表中的数据也会修改
UPDATE myv1 SET last_name = '张无忌' WHERE last_name = '张飞';
# 视图中 删除数据
DELETE FROM myv1 WHERE last_name = '张无忌';

/*
具备以下特点的视图是不能修改的
1.sql中包含以下关键字：group by、distinct、having、union、union all

2.常量视图

3.SELECT中包含子查询

4.JOIN

5.FROM 一个不能更新的视图

6.WHERE子句的子查询引用了 from子句中的表
*/
# 1.更新
INSERT INTO emp_v2
VALUES(18000, 30);

# 2.常量视图
CREATE OR REPLACE VIEW myv2
AS
SELECT 'Tom' name;

UPDATE myv2 SET name = 'Jery' WHERE name = 'Tom';

# 3.SELECT中包含子查询
CREATE OR REPLACE VIEW myv3
AS
SELECT (SELECT MAX(salary) FROM employees) max_e;

DELETE FROM myv3 WHERE max_e > 0;

# 5.from 一个不能更新的视图
CREATE OR REPLACE VIEW myv5
AS
SELECT * FROM myv3;

UPDATE myv5 SET max_e = 10000 WHERE max_e > 0;

# 6.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
);
# 更新
UPDATE myv6 SET salary = 10000 WHERE last_name = 'k_ing';

视图 VS 表

	创建语法	是否占据物理空间	使用
视图	create view	只保存了 sql逻辑	增删改查，一般不能 增删改
表	create table	保存表中完整数据	增删改查

变量

系统变量

系统变量由系统提供，非用户定义，属于服务器层面

全局变量 global

# 全局变量
/*
作用域：服务器每次启动将为所有的全局变量赋初始值，针对于所有的会话(连接)有效，但不能跨启动

*/
# 1.查看所有的全局变量
SHOW GLOBAL VARIABLES;

# 2.查看部分的全局变量
SHOW GLOBAL VARIABLES LIKE '%char%';

# 3.查看指定的全局变量的值
SELECT @@global.autocommit;
SELECT @@tx_isolation;

# 4.为指定的全局变量赋值
SET @@global.autocommit = 0;
SET GLOBAL 系统变量名 = 值;

会话变量 session

# 会话变量
/*
作用域：仅仅针对于当前的会话（连接）有效
*/
# 1.展示会话变量
SHOW SESSION VARIABLES;

# 2.查看部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';

# 3.查看指定会话变量
SELECT @@session.autocommit;
SELECT @@tx_isolation;

# 4.为指定的会话变量赋值
SET @@session.autocommit = 0;
SET @@tx_isolation = 'read-uncommitted';
SET SESSION tx_isolation = 'read-committed';

练习：

# 查看所有的系统变量
SHOW GLOBAL VARIABLES;

# 查看所有的会话变量
SHOW SESSION VARIABLES;

# 查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';

# 查看指定的某个系统变量的值
# SELECT @@global|SESSION.系统变量名;
SELECT @@global.character_set_database; # utf8

# 为某个系统变量赋值
# 方式一：
# SET GLOBAL|SESSION 系统变量名 = 值;

# 方式二：
# SET @@global|SESSION.系统变量名 = 值;

# 注意：如果是 全局级别，则需要加 GLOBAL，如果是 会话级别，则需要加 SESSION，如果不写，默认 SESSION;

自定义变量

• 自定义变量 /*

说明：变量是用户自定义的，不是由系统提供的

使用步骤： 1.声明 2.赋值 3.使用（查看、比较、运算等）

*/

用户变量

• 用户变量 /*

作用域：针对于当前会话（连接）有效；同于会话变量的作用域；

应用在任何地方，也就是 begin、end里面或其它任何位置；

*/

# 自定义变量
/*
说明：变量是用户自定义的，不是由系统提供的

使用步骤：
1.声明
2.赋值
3.使用（查看、比较、运算等）
*/

# 用户变量
/*
作用域：针对于当前会话（连接）有效；同于会话变量的作用域
*/

# 1.声明并初始化
SET @用户变量名 = 值;
SET @用户变量名 := 值;
SELECT @用户变量名 := 值;

# 2.赋值

# 方式一：set 、select
SET @用户变量名 = 值;
SET @用户变量名 := 值;
SELECT @用户变量名 := 值;

# 方式二：select into
SELECT 字段 INTO 变量名
FROM 表;

# 3.使用
SELECT @用户变量名;


# 案例：
SET @name = 'john';
SET @name = 123;
SELECT COUNT(*) INTO @count
FROM myemployees.employees;

SELECT @count;

局部变量

• 局部变量

/*

作用域：仅仅在定义它的 BEGIN END中有效；

应用在 begin end中的第一句话；

*/

# 局部变量

/*
作用域：仅仅在定义它的 BEGIN END中有效
应用在 begin end中的第一句话
*/

# 1.声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;

# 2.赋值
# 方式一：
SET 变量名 = 值;
SET 变量名 := 值;
SELECT @变量名 := 值;
# 方式二：
SELECT 字段 INTO 变量名
FROM 表;

# 3.使用
SELECT 变量名;


#案例：
DECLARE age INT DEFAULT 18;
SHOW age;

用户变量 VS 局部变量

标题	作用域	定义和使用位置	语法
用户变量	当前会话	会话中的任何地方	必须加 @符号，不用指明数据类型
局部变量	begin end中	只能在 begin end中，且为第一句话	一般不用加 @符号，需指明数据类型

#案例：
# 声明两个变量并赋初始值，求和并打印

# 用户变量
SET @m = 1;
SET @n := 2;
SET @sum := @m + @n;
SELECT @sum;

# 局部变量
# BEGIN
DECLARE m INT DEFAULT 18;
DECLARE n INT DEFAULT 0;
DECLARE sums INT;
SET sums := m + n;
SELECT sums;
# END;

存储过程和函数

存储过程和函数，类似于 java中的方法

好处：

1. 提高代码的重用性
2. 简化操作

存储过程：

含义：一组预先编译好的 sql语句的集合，可以理解成批处理语句

好处：

1. 提高代码的重用性
2. 简化操作
3. 减少了编译次数并减少了和数据库服务器的连接次数，提高效率

创建语法：

• create procedure 存储过程名(参数列表)
• begin
• 存储过程体（一组合法的 sql语句）...
• end

注意：

1. 参数列表包含三部分：参数模式、参数名、参数类型
2. 如果存储过程体仅有一句话，begin、end可以省略
3. 存储过程体中的每条 sql语句的结尾都要求必须加分号
4. 存储过程的结尾可以使用 delimiter重新设置：如 delimiter 结束标记符

参数模式：

• in：该参数可以作为输入，也就是该参数需要调用方传入
• out：该参数可以作为输出，也就是该参数可以作为返回值
• inout：该参数可以作为输入又可以作为输出，也就是该参数可以传入值，也可以传出值

调用语法：

• call 存储过程名(实参列表);

删除语法：只能单个删除

• drop procedure 存储过程名;

# 删除存储过程
DROP PROCEDURE myp1;

查看存储过程的信息

• show create procedure 存储过程名;

SHOW CREATE PROCEDURE myp2;

实例：

1. 空参列表
2. in参数模式的使用
3. OUT参数模式的使用
4. INOUT参数模式的使用

# 1.空参列表

# 插入到 admin表中五条记录
# 创建：
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO admin(username, `password`)
	VALUES('john1', '001'), ('john2', '002'), ('john3', '003'), ('john4', '004'), ('john5', '005');
END $

# 调用：
CALL myp1()$

# 2.创建带 in模式参数的存储过程

# 根据女神名，查询对应的男神信息
DELIMITER $
CREATE PROCEDURE myp2(IN queryname VARCHAR(20))
BEGIN
SELECT
	b.*
FROM
	boys b
JOIN
	beauty be
ON
	be.boyfriend_id = b.id
WHERE
	be.`name` = queryname;
END $

CALL myp2()$

# 创建存储过程，用户是否登录成功
CREATE PROCEDURE myp3(IN username VARCHAR(20), IN `password` VARCHAR(20))
BEGIN
DECLARE result VARCHAR(20) DEFAULT ''; # 1.变量声明并初始化
SELECT
	COUNT(*) INTO result # 2.赋值
FROM
	admin a
WHERE
	a.username = username && a.`password` = `password`;
SELECT result; # 3.使用
END $

CALL myp3('john', 8888) $

CREATE PROCEDURE myp4(IN username VARCHAR(20), IN `password` VARCHAR(20))
BEGIN
DECLARE result INT DEFAULT 0; # 1.变量声明并初始化
SELECT
	COUNT(*) INTO result # 2.赋值
FROM
	admin a
WHERE
	a.username = username && a.`password` = `password`;
SELECT IF(result > 0, '登录成功', '登录失败'); # 3.使用
END $

# 修改字符集
set names gbk$

# 创建带 OUT模式的存储过程

# 根据女神名，返回对应的男神名
DELIMITER $
CREATE PROCEDURE myp5(IN bename VARCHAR(20), OUT boname VARCHAR(20))
BEGIN
SELECT
	bo.boyName INTO boname
FROM
	boys bo
INNER JOIN
	beauty be
ON
	be.boyfriend_id = bo.id
WHERE
	be.`name` = bename;
END $

SET @boyName$
CALL myp5('柳岩', @boyName)$
SELECT @boyName$

# 根据女神名，返回对应的男神名及魅力值
CREATE PROCEDURE myp6(IN bename VARCHAR(20), OUT boname VARCHAR(20), OUT userCP INT)
BEGIN
SELECT
	bo.boyName, bo.userCP INTO boname, userCP
FROM
	boys bo
INNER JOIN
	beauty be
ON
	be.boyfriend_id = bo.id
WHERE
	be.`name` = bename;
END $

CALL myp6('柳岩', @bName, @userCP)$
SELECT @bName, @userCP$

# INOUT参数模式的使用
CREATE PROCEDURE myp7(INOUT bename VARCHAR(20))
BEGIN
SELECT
	bo.boyName INTO bename
from
	boys bo
INNER JOIN
	beauty be
ON
	be.boyfriend_id = bo.id
WHERE
	be.`name` = bename;
END $

SET @beName := '柳岩'$
CALL myp7(@beName)$
SELECT @beName$

# 传入 a和 b两个值，最终 a和 b都翻倍并返回
CREATE PROCEDURE myp8(INOUT a INT, INOUT b INT)
BEGIN
SET a := 2*a;
SET b := 2*b;
END $

SET @a := 10$
SET @b := 100$
CALL myp8(@a, @b)$
SELECT @a, @b$

练习-存储过程和函数

# 创建存储过程实现传入用户名和密码，插入到 admin表中
DELIMITER $
CREATE PROCEDURE addUser(IN userName VARCHAR(20), IN pas VARCHAR(20))
BEGIN
INSERT INTO admin(admin.username, `password`)
VALUES(userName, pas);
END $

CALL addUser('john888', '8888')$
SELECT
	*
FROM
	admin$
        
# 创建存储过程或函数实现传入 女神编号，返回女神名和电话
DELIMITER $
CREATE PROCEDURE girlInfo(IN girlId INT, OUT girlName VARCHAR(20), OUT girlPhone VARCHAR(20))
BEGIN
SELECT
	be.`name`, be.phone INTO girlName, girlPhone
FROM
	beauty be
WHERE
	be.id = girlId;
END $

CALL girlInfo(2, @girlName, @girlPhone)$

SELECT @girlName, @girlPhone$

# 创建存储过程或函数，实现传入两个日期，返回比较的大小
CREATE PROCEDURE compareBir(IN t1 DATETIME, IN t2 DATETIME, OUT res INT)
BEGIN
SELECT DATEDIFF(t1, t2) INTO res;
END $

CALL compareBir('2020-01-01', now(), @res)$

SELECT @res$

# 创建存储过程或函数，实现传入一个日期，格式化成 xx年xx月xx日并返回
CREATE PROCEDURE formatTime(IN t1 DATETIME, OUT res VARCHAR(20))
BEGIN
SELECT DATE_FORMAT(t1, '%y年%m月%d日') INTO res;
END $

CALL formatTime('2020-12-14', @timeRes);
SELECT @timeRes$

# 创建存储过程或函数实现传入女神名，返回：女神 AND 男神 格式的字符串
CREATE PROCEDURE strRes(IN gName VARCHAR(20), OUT strs VARCHAR(20))
BEGIN
SELECT
	CONCAT(gName, ' AND ', IFNULL(bo.boyName, 'null')) INTO strs
FROM
	beauty be
JOIN
	boys bo
ON
	be.boyfriend_id = bo.id
WHERE
	be.name = gName;
END $

CALL strRes('柳岩', @strs)$
SELECT @strs$

# 创建存储过程或函数，根据传入的条目数和起始索引，查询 beauty表的记录
CREATE PROCEDURE beInfo(IN page INT, IN pageSize INT)
BEGIN
SELECT
	*
FROM
	beauty
LIMIT page, pageSize;
END $

CALL beInfo(2, 5)$

函数：

• 含义：一组预先编译好的 sql语句的集合，理解成批处理语句

特点：

1. 提高代码的重用性
2. 减少了编译次数，并且减少了和数据服务器的连接次数，提高效率
3. 简化操作

函数 VS 存储过程

• 存储过程：可以有 0个返回，也可以有多个返回，适合做批量插入、批量更新
• 函数：有且只有一个返回值，适合做处理数据后返回一个结果

创建语法：

• create function 函数名(参数列表) returns 返回类型
• begin
• 函数体...
• end

参数列表：包含两部分

• 参数名
• 参数类型

函数体：最后肯定会有 return语句，如果没有会报错

调用语句：

• select 函数名(参数列表)

查看语法：

• show create function 函数名;

删除语法：

• drop function 函数名;

实例：

# 无参有返回

# 案例：返回公司的员工个数
DELIMITER $
CREATE FUNCTION myCount() RETURNS INT
BEGIN
DECLARE sum INT DEFAULT 0; # 定义局部变量
SELECT COUNT(0) INTO sum
FROM employees;
RETURN sum;
END $

SELECT myCount()$ # 107

# 有参有返回
# 根据员工名返回它的工资
CREATE FUNCTION mySal(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
SET @sal := 0; # 定义用户变量
SELECT
	salary INTO @sal
FROM
	employees
WHERE
	last_name = empName;
RETURN @sal;
END $

SELECT mySal('Hunold') $

# 根据部门名，返回该部门的平均工资
CREATE FUNCTION depSal(depName 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 = depName;
RETURN sal;
END $

SELECT depSal('Adm')$

# 查看函数
DELIMITER ;
SHOW CREATE FUNCTION depSal;

# 删除函数
DROP FUNCTION depSal;

# 创建函数，实现传入两个float，返回二者之和
CREATE FUNCTION addFun(a FLOAT, b FLOAT) RETURNS FLOAT
BEGIN
DECLARE sums FLOAT;
SET sums := a + b;
return sums;
END $

SELECT addFun(1, 100)$

流程控制结构

分类

• 顺序结构：程序从上往下依次执行
• 分支结构：程序从两条或多条路径中选择一条执行
• 循环结构：程序在满足一定的基础上，重复执行一段代码

分支结构：

if函数：

• 功能：实现简单的双分支
• 应用：任何位置

语法：

• if(表达式1, 表达式2, 表达式3)
• 执行顺序：如果表达式1成立，则 if函数返回表达式2的值，否则返回表达式3的值

case结构

情况1：

• 类似于 java中的 switch语句，一般用于实现 等值判断
• 语法：可以作为单独的语句使用
• case 变量|表达式|字段
• when 要判断的值 then 变量|表达式|字段|语句;
• when 要判断的值 then 变量|表达式|字段|语句;
• ...
• [else 要返回的值n|语句;]
• end case;

情况2：

• 类似于 Java中的多重 if语句，一般用于实现 区间判断
• 语法：可以作为单独的语句使用
• case
• when 要判断的条件1 then 变量|表达式|字段|语句;
• when 要判断的条件2 then 变量|表达式|字段|语句;
• ...
• [else 要返回的值n|语句;]
• end case;

特点：

• 可以作为表达式，嵌套在其它语句中使用，可以放在任何地方，begin end中或 begin end的外边
• 可以作为独立的语句去使用，但只能放在 begin end中

实例：

# 创建存储过程，根据传入的成绩，显示等级
DELIMITER $
CREATE PROCEDURE sourceLev(IN source INT)
BEGIN
	CASE
	WHEN source >= 90 && source <= 100 THEN SELECT 'A';
	WHEN source >= 80 THEN SELECT 'B';
	WHEN source >= 60 THEN SELECT 'C';
	ELSE SELECT 'D';
	END CASE;
END $

CALL sourceLev(90)$

if结构：

• 功能：实现多重分支
• 应用：只能在 begin end中使用
• 语法：
• if 条件1 then 语句1;
• elseif 条件2 then 语句2;
• ...
• [else 语句n;]
• end if;

实例：

# 创建函数，根据传入的成绩，返回等级
CREATE FUNCTION sourceFn(source INT) RETURNS VARCHAR(20)
BEGIN
	DECLARE str VARCHAR(20);
	IF source >= 90 && source <= 100 THEN SELECT 'a' INTO str;
	ELSEIF source >= 80 THEN SELECT 'b' INTO str;
	ELSEIF source >= 60 THEN SELECT 'c' INTO str;
	ELSE SELECT 'd' INTO str;
	END IF;
	RETURN str;
END $

SELECT sourceFn(50)$

# 或
CREATE FUNCTION sourceFn1(source INT) RETURNS VARCHAR(20)
BEGIN
	IF source >= 90 && source <= 100 THEN RETURN 'a';
	ELSEIF source >= 80 THEN RETURN 'b';
	ELSEIF source >= 60 THEN RETURN 'c';
	ELSE RETURN 'd';
	END IF;
END $
SELECT sourceFn(80)$

循环结构：

分类：只能出现在 begin end中

• while
• loop
• repeat

循环控制：

• iterate：类似于 continue，结束本次循环，继续下一次
• leave：类似于 break，结束循环

while：

语法：

• 标签: while 循环条件 do
• 循环体;
• end while [标签];

实例：

# 循环控制语句

# 批量插入，根据次数插入到 admin中多条记录
DROP PROCEDURE insertPro$
CREATE PROCEDURE insertPro(IN n INT)
BEGIN
DECLARE i INT DEFAULT 0;
a: WHILE i < n DO
INSERT INTO admin(username, `password`)
VALUES(CONCAT('Rose', i), '666');
# i++;
SET i = i + 1;
END WHILE a;
END $

CALL insertPro(2)$

# LEAVE语句，批量插入，根据次数插入到 admin中多条记录，如果次数超过 20则停止
TRUNCATE TABLE admin$
DROP PROCEDURE insertPro1$
CREATE PROCEDURE insertPro1(IN n INT)
BEGIN
DECLARE i INT DEFAULT 1;
a: WHILE i <= n DO
INSERT INTO admin(username, `password`)
VALUES(CONCAT('Rose', i), '666');
IF i >= 20 THEN LEAVE a;
END IF;
# i++;
SET i = i + 1;
END WHILE a;
END $

CALL insertPro1(2)$

# ITERATE语句，批量插入，根据次数插入到 admin中多条记录，如果次数超过 20则停止
TRUNCATE TABLE admin$
DROP PROCEDURE insertPro2$
CREATE PROCEDURE insertPro2(IN n INT)
BEGIN
DECLARE i INT DEFAULT 0;
a: WHILE i <= n DO
SET i = i + 1;
IF MOD(i, 2) != 0 THEN ITERATE a;
END IF;
INSERT INTO admin(username, `password`)
VALUES(CONCAT('Rose', i), '666');
# i++;
END WHILE a;
END $

CALL insertPro2(30)$

# 向表 strcontent中插入指定个数的 随机字符串
DROP TABLE IF EXISTS strcontent;
CREATE TABLE strcontent(
	id INT PRIMARY KEY AUTO_INCREMENT,
	content VARCHAR(20)
);
# Math.floor(Math.random() * (max - min + 1) + min)
CREATE PROCEDURE addStr(IN num INT)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
DECLARE startIndex INT DEFAULT 1;
DECLARE len INT DEFAULT 1;
WHILE i <= num DO
SET startIndex = FLOOR(RAND()*26 + 1);
SET len = FLOOR(RAND()*(20-startIndex + 1) + 1);
INSERT INTO strcontent(content)
VALUES(SUBSTR(str, startIndex, len));
SET i = i + 1;
END WHILE;
END $

loop：

语法：

• [标签:] loop
• 循环体;
• end loop [标签];

repeat：

语法：

• [标签:] repeat
• 循环体;
• until 结束循环的条件
• end repeat [标签];