数据库下

五、MySQL中的数据类型

• 整型

整数类型	字节数	最小值（有符号/无符号）	最大值（有符号/无符号）
TINYINT	1	-128/0	127/255
SMALLINT	2	-32768/0	32767/65535
MEDOIMINT	3	-8388608/0	8388607/1677215
INT,INTEGER	4	-2147483648/0	2147483647/4299497295
BIGINT	8	-9223372036854775808/0	9223372036854775807/18446744073709551616

设置无符号类型：UNSIGNED

• 浮点型

  • float：4个字节

    • FLOAT(M,D) M显示的位数（0~255），D表示精确到第几位（带四舍五入），该写法为非标准写法，建议迁移库的时候不要使用

  • double：8个字节

    • DOUBLE(M,D) ：同FLOAT(M,D)

    • DOUBLE 和FLOAT 在不指定精度时，默认会按照实际精度，但最终的精度是由操作系统所决定的

    • 双精度需要指明标度和精度

  • real：实际上就是DOUBLE ，如果SQL服务器模式为REAL_AS_FLOAT real就是FLOAT（0·30）

• 定点数

  • decimal

    DECIMAL(M,D)：处理规格和浮点型是相同的，M取值范围是0~65，D取值范围为（0~30）

    DECIMAL：mysql内部使用字符串存放，定点型占M+2字节，但是不能大于M的最大存储范围，没有指明精度的时候默认为整数位10，小数位0

  • numeric

    NUMERIC：等价于DEIMAL

• 日期时间类型

  • date：日期 YYYY-MM-DD

  • time：时间 HH:MM:SS

  • datetime：日期时间 YYYY-MM-DD HH:MM:SS

  • year：年份 YYYY和YYl

  • timestamp：时间戳 YYYY-MM-DD HH:MM:SS

    注意：注册类时间，发布类时间不建议使用datetime类型，建议使用时间戳，因为datetime不利于时间计算

• 字符串

  • char(存较短字符串用)固定长度类型)

  • char(m) ，m是最大长度，如果不指定m，则默认m = 1,在数据列里每个值占用M个字符，如果长度小于m对应在右边会用空格补充

  • varchar（可变长，必须指定长度，没有默认值）

  • char是定长的，其处理效率上比VARCHAR高得多，但是比较浪费空间

  • text文本类型，可以存储文本段，不建议使用，text加默认值不起作用

  • 在什么情况下使用char

    1. 存储信息较短

    2. 固定长度的也可使用

    3. 频繁的修改用char类型，varchar在每次改变的是都会区计算

• 二进制类型

  • blob

    二进制大对象容量是可变的，通过容量不同可以分为四种blob类型

    1. TINYBLOB

    2. BLOB

    3. MEDIUBLOB

    4. LONGBLO B

  • binary

  • 两者区别类似于char 和varchar ，不同的是他们存储的不是字符串，存储的是二进制串，所以blob和binary没有字符集，排序通过比较列值中的字节数

• NULL类型

  • 特征：MySQL中所有的类型都可以为NULL

  • 空字符串和0不是NULL

  • 所有运算符和NULL运算都是NULL

  • NULL的判断只能用IS NULL 和IS NOT NULL

  • NULL影响查询速度，所以在SQL优化中尽可能的避免使用NULL

  • Q：为什么建表时，加NOT NULL DEFAULT ''/ DEFAULT 0

    A：不想让表中使用NULL

    Q：为什么不想使用NULL值

    A：①不好比较，NULL是一种类型，比较时只能用专门的 IS NULL 和IS NOT NULL，碰到运算符，一律返回NULL

    ② 效率不高，影响提高所索引的效果

• 枚举：enum，在mysql中是一个字符串对象，值来源于表创建时，在列规定中显示枚举，可以插入空字符串和null值

• 集合：set 和枚举很像，可以包含0-64个成员。set和枚举不同的是，set是通过存储的成员个数来决定字节数，set一次性可以选择多个成员，enum一次只能选择一个

六、约束

定义：就是一种限制，为了保持数据一致性

1 主键约束：PRIMARY KEY

• 使得字段具有唯一性和非空性

2 非空约束：NOT NULL

• 保证字段的值不能为空

3 唯一约束：UNIQUE

• 保证该字段的值是唯一的

4 默认约束：DEFAULT

• 给字段添加默认值

5 检查约束：CHECK

• MySQL中不支持

6 外键约束：

• 用来保证两张表的参照完整性

分类：

1. 列级约束：除外键约束外都可作为列级约束

2. 表级约束：出了默认和非空约束，其他都可以作为表级约束

CREATE TABLE stu_info(
snum INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(15) NOT NULL,
gender CHAR CHECK(gender IN('男','女')),
person_id VARCHAR(20) UNIQUE,
join_timet TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
teacher_id INT REFERENCES teacher(teacher_id) 
)
CREATE TABLE teacher (
teacher_id INT PRIMARY KEY,
tname VARCHAR(20) NOT NULL
)

USE student
CREATE TABLE stu_info(
snum INT AUTO_INCREMENT,
sname VARCHAR(15) NOT NULL,
gender CHAR CHECK(gender IN('男','女')),
person_id VARCHAR(20),
join_timet TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
teacher_id INT ,
CONSTRAINT pk PRIMARY KEY (snum),-- pk时可以省略的
CONSTRAINT fk UNIQUE(sname),
CONSTRAINT gk FOREIGN KEY(teacher_id) REFERENCES teacher(teacher_id)
)
CREATE TABLE teacher (
teacher_id INT PRIMARY KEY,
tname VARCHAR(20) NOT NULL
)
​
#查看表索引
SHOW INDEX FROM stu_info

#创建表之后添加约束
/*
添加表级别约束
ALTER TABLE 表名 ADD[CONSTRAINT 约束名] 约束类型 [KEY](字段名)
添加列级别约束
ALTER TABLE 表名 MODIFY COLUMN 列名 类型 约束类型 [KEY]
*/
​
#创建表之后添加主键约束(主键有默认的索引名，故无需添加索引名)
ALTER TABLE stu_info ADD CONSTRAINT PRIMARY KEY(sid)
ALTER TABLE stu_info MODIFY COLUMN sid INT PRIMARY KEY
​
#创建表之后添加唯一约束
ALTER TABLE stu_info ADD CONSTRAINT uk UNIQUE (sname)
​
#创建表之后添加外键约束
ALTER TABLE stu_info ADD CONSTRAINT fk FOREIGN(teacher_id)
​
#添加非空约束
ALTER TABLE stu_info MODIFY COLUMN sname VARCHAR(20) NOT NULL
​
#删除主键约束,主键唯一，故可不用指定字段
ALTER TABLE stu_info DROP PRIMARY KEY
​
#删除唯一约束（删除唯一约束的时候，是通过删除索引来完成的）
ALTER TABLE stu_info DROP INDEX uq
​
#删除外键约束（删除外键约束时不会删除其索引，所以通常来说需要删除对应的索引）
ALTER TABLE stu_info DROP FOREIGN KEY fk
ALTER TABLE stu_info DROP INDEX fk
​
#删除默认约束
ALTER TABLE stu_info MODIFY COLUMN join_time TIMESTAMP
​
#删除非空约束
ALTER TABLE stu_info MODIFY COLUMN sname VARCHAR(20) NULL
​
#mysql中提供了自增长列,
AUTO_INCREMENT -- 通常放在主键或者唯一键上，一张表只能出现一个自增列
-- 说明：如果一张表中使用了自增长列，如果使用 DELETE 删除的表中的所有数据，则下次添加时自增长
-- 会从断电开始，TRUNCATE则不会
#设置自增列的步长
SET AUTO_INCREMENT_INCREMENT = 2;
​

 

七、事务（tcl事务控制语言）

7.1 概述

定义：一个或者一组sql组成的一个执行单元，数据的添加、修改、删除、查询。表和库没有事务这个说法

特性：acid特性

• 原子性：数据库事务具有不可分割性，要么都成功，要么都失败

• 一致性：事务的前后应该保持一致

• 隔离性：某个事务的操作对其他事务是不可见的

• 持久性：当事务提交之后，其影响应该保留下来，不能撤销

#查看引擎
SHOW ENGINES

7.2 事务开启

自动事务/隐式事务：事务的提交是自动的

#查看AUTOCOMMIT变量
SHOW VARIABLES LIKE 'AUTOCOMMIT' 

切换为手动事务

SET AUTOCOMMIT = FALSE -- FALSE 可替换为 0

事务开启

START TRANSACTION;

结束事务

COMMIT 提交事务
ROLLBACK 回滚事务

事务回滚演示

#事务回滚演示
SET autocommit = FALSE;
SHOW VARIABLES autocommit;
#开启事务，结束事务之前的语句都是事务
START TRANSACTION;
INSERT INTO stu_info(sname,gender,person_id,teacher_id)
VALUES('ljq','m','1',1);
INSERT INTO stu_info(sname,gender,person_id,teacher_id)
VALUES('ljq',m,'12321421324',1); -- 有错语句
#结束事务
-- COMMIT; #提交事务
-- ROLLBACK; #回滚事务到事务开启之前的状态，事务一旦提交不能回滚

7.3事务的隔离级别

实质是多线程并发问题

1. 脏读（脏数据）：对于两个事务：T1、T2，T1读取了T2更新但是还没有提交的数据，如果此时T2回滚，T1读取的数据是临时无效数据

2. 不可重复度：对于两个事务：T1、T2，T1读取了一个字段，T2更新了一个字段并提交，此时T1在读取相同字段时值会改变

3. 幻读：主要针对于修改和插入，T1、T2两个事务，T1从表中修改了一条数据，T2从表中添加数据，添加的数据满足T1修改数据的条件，则对应T1会修改T2未提交的数据，TI在修改的时候会发现多出了几条修改的数据

隔离级别	描述
read-uncommitted	允许事务去读其他食物未提交的数据。脏读，不可重复读，幻读都无法解决
read-committed-	只允许读其他食物已经提交的数据，可解决脏读，但是依旧不能处理不可重复，幻读问题
repeatable-read	确保事务可以多次从一个字段中读取相同的值，解决了不可重复读的问题，依旧会出现幻读
serializable	确保事务可以从一个表中读取相同的行，事务持续期间禁止其他事务执行插入、更新、删除操作，可以解决幻读问题，但一般没人用

#查看当前的默认隔离级别
SELECT @@TX_ISOLATION;
#修改隔离级别
SET SESSION  TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; #SESSION 加上之后可以立马生效

事务回滚

#关于 ROLLBACK 
set autocommit = FALSE;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT @@tx_isolation;
START TRANSACTION;
SAVEPOINT b; -- 设置回滚点
DELETE FROM stu_info;
SELECT * FROM stu_info;
ROLLBACK TO b; -- 回滚到b
SELECT * FROM stu_info; --再次查看表

说明：

1. 在innodb存储引擎中事务隔离级别前三个默认使用行级锁，serializable加标记锁

2. trancate清除表的时候无法回滚

#创建表时指定引擎
CREATE TABLE score(
id int;
)ENGINE = INNODB DEFAULT CHARACTER=utf8

serializable实验

1. T1

   Microsoft Windows [版本 10.0.17763.652]
   (c) 2018 Microsoft Corporation。保留所有权利。
   ​
   C:\Users\18392>mysql -uroot -proot
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 60
   Server version: 5.5.54 MySQL Community Server (GPL)
   ​
   Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
   ​
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   ​
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   ​
   mysql> set session transaction isolation level serializable
       -> ;
   Query OK, 0 rows affected (0.00 sec)
   ​
   mysql> select @@tx_isolation;
   +----------------+
   | @@tx_isolation |
   +----------------+
   | SERIALIZABLE   |
   +----------------+
   1 row in set (0.00 sec)
   ​
   mysql> start transaction
       -> ;
   Query OK, 0 rows affected (0.00 sec)
   ​
   mysql> select * from stu_info;
   ERROR 1046 (3D000): No database selected
   mysql> set session transaction isolation level serializable
       -> exit
       -> ;
   ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'exit' at line 2
   mysql> exit
   Bye
   ​
   C:\Users\18392>mysql -uroot -proot
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 63
   Server version: 5.5.54 MySQL Community Server (GPL)
   ​
   Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
   ​
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   ​
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   ​
   mysql> use student
   Database changed
   mysql>  set session transaction isolation level serializable;
   Query OK, 0 rows affected (0.00 sec)
   ​
   mysql> select @@tx_isolation;
   +----------------+
   | @@tx_isolation |
   +----------------+
   | SERIALIZABLE   |
   +----------------+
   1 row in set (0.00 sec)
   ​
   mysql>  start transaction;
   Query OK, 0 rows affected (0.00 sec)
   ​
   mysql> select * from stu_info;
   +------+-----------+--------+-----------+---------------------+------------+
   | snum | sname     | gender | person_id | join_timet          | teacher_id |
   +------+-----------+--------+-----------+---------------------+------------+
   |    1 | 杩炴帴      | n      | we        | 2019-07-29 15:13:00 |          1 |
   |    2 | 閮痉绾?   | m      | 13        | 2019-07-29 15:18:06 |          1 |
   +------+-----------+--------+-----------+---------------------+------------+
   2 rows in set (0.00 sec)
   ​
   mysql> set names utf8;
   Query OK, 0 rows affected (0.00 sec)
   ​
   mysql> select * from stu_info;
   +------+-----------+--------+-----------+---------------------+------------+
   | snum | sname     | gender | person_id | join_timet          | teacher_id |
   +------+-----------+--------+-----------+---------------------+------------+
   |    1 | 杩炴帴      | n      | we        | 2019-07-29 15:13:00 |          1 |
   |    2 | 閮痉绾?   | m      | 13        | 2019-07-29 15:18:06 |          1 |
   +------+-----------+--------+-----------+---------------------+------------+
   2 rows in set (0.00 sec)
   ​
   mysql> update stu_info set sname = 'ljq';
   Query OK, 2 rows affected (0.07 sec)
   Rows matched: 2  Changed: 2  Warnings: 0
   ​
   mysql> update stu_info set sname = 'ln';
   Query OK, 2 rows affected (0.00 sec)
   Rows matched: 2  Changed: 2  Warnings: 0
   ​
   mysql> update stu_info set sname = 'lnn';
   Query OK, 2 rows affected (0.00 sec)
   Rows matched: 2  Changed: 2  Warnings: 0
   ​
   mysql> update stu_info set sname = 'ln';
   Query OK, 2 rows affected (0.00 sec)
   Rows matched: 2  Changed: 2  Warnings: 0
   ​
   mysql> commit;
   Query OK, 0 rows affected (0.00 sec)
   ​
   mysql> select * from stu_info;
   +------+-------+--------+-----------+---------------------+------------+
   | snum | sname | gender | person_id | join_timet          | teacher_id |
   +------+-------+--------+-----------+---------------------+------------+
   |    1 | ln    | n      | we        | 2019-07-29 15:13:00 |          1 |
   |    2 | ln    | m      | 13        | 2019-07-29 15:18:06 |          1 |
   +------+-------+--------+-----------+---------------------+------------+
   2 rows in set (0.00 sec)
   ​
   mysql> select * from stu_info;
   +------+-------+--------+-----------+---------------------+------------+
   | snum | sname | gender | person_id | join_timet          | teacher_id |
   +------+-------+--------+-----------+---------------------+------------+
   |    1 | ln    | n      | we        | 2019-07-29 15:13:00 |          1 |
   +------+-------+--------+-----------+---------------------+------------+
   1 row in set (0.00 sec)

2. T2

   Microsoft Windows [版本 10.0.17763.652]
   (c) 2018 Microsoft Corporation。保留所有权利。
   ​
   C:\Users\18392>mysql -uroot -proot
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 61
   Server version: 5.5.54 MySQL Community Server (GPL)
   ​
   Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
   ​
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   ​
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   ​
   mysql>  set session transaction isolation level serializable;
   Query OK, 0 rows affected (0.00 sec)
   ​
   mysql> select @@tx_isolation;
   +----------------+
   | @@tx_isolation |
   +----------------+
   | SERIALIZABLE   |
   +----------------+
   1 row in set (0.00 sec)
   ​
   mysql> start transaction;
   Query OK, 0 rows affected (0.00 sec)
   ​
   mysql> exit
   Bye
   ​
   C:\Users\18392>mysql -uroot -proot
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 62
   Server version: 5.5.54 MySQL Community Server (GPL)
   ​
   Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
   ​
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   ​
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   ​
   mysql> use student
   Database changed
   mysql>  set session transaction isolation level serializable;
   Query OK, 0 rows affected (0.00 sec)
   ​
   mysql> select @@tx_isolation;
   +----------------+
   | @@tx_isolation |
   +----------------+
   | SERIALIZABLE   |
   +----------------+
   1 row in set (0.00 sec)
   ​
   mysql>  start transaction;
   Query OK, 0 rows affected (0.00 sec)
   ​
   mysql> delete from stu_info where snum = 1;
   ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
   mysql> delete from stu_info where snum = 1;
   ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
   mysql> delete from stu_info where snum = 2;
   Query OK, 1 row affected (29.15 sec)
   ​
   mysql> select * from stu_info;
   +------+-------+--------+-----------+---------------------+------------+
   | snum | sname | gender | person_id | join_timet          | teacher_id |
   +------+-------+--------+-----------+---------------------+------------+
   |    1 | ln    | n      | we        | 2019-07-29 15:13:00 |          1 |
   +------+-------+--------+-----------+---------------------+------------+
   1 row in set (0.00 sec)
   ​
   mysql> commit;
   Query OK, 0 rows affected (0.00 sec)

八、视图

8.1概述

视图的定义：保存一组sql语句的逻辑。不保存数据，只保存一组sql的逻辑

创建视图的语法

CREATE VIEW 视图名
AS
编写的sql

视图一般只用来查找

SELECT * FROM 视图名

视图的用法和表一样

创建视图

#创建视图
#查询各部门员工的姓名，角色，部门名称，工资级别
CREATE VIEW salarys
AS
SELECT
d.stuff_name,
dt.department_id

FROM
department d JOIN department_type dt ON d.department_id = dt.department_id;

8.2修改视图

#修改视图
​
#方法一
CREATE OR REPLACE VIEW 视图名
AS
新的sql语句
​
#方法二
ALTER VIEW SALARYS
AS
新的sql语句

#更改视图一
CREATE OR REPLACE VIEW salays
AS
SELECT * FROM department;
​
#更改视图二
ALTER VIEW salays
AS
SELECT * FROM department;
​

8.3 删除视图

DROP VIEW 视图1，视图2…

#删除视图
DROP VIEW salarys;

8.4 查询视图

#查询视图法一
SELECT 
* 
FROM
视图名
​
#查询视图法二(看详细的语法)
SHOW CREATE VIEW salarys;
​
#查看视图法三
DESC salarys；

#查询视图法一
SELECT
*
FROM 
salays;
​
#查询视图法二(可以看详细的语法)
SHOW CREATE VIEW salarys;
​
#查看视图法三
DESC salarys；

总结：视图一般情况下只做查询，保存sql的逻辑，不做数据保存

九 存储过程和函数

9.1变量

和Java中的变量是一样的，都需要声明，赋值之后才能使用

1. 系统变量：系统提供的，不需要自定义

   • 全局变量

   • 会话变量

   查看系统变量的语法

   #查所有全局变量|会话变量
   SHOW GLOBAL|SESSION VARIABLES -- 若不指明是GLOBAL|SESSION默认查询SESSION
   ​
   #通过模糊查询方式查找
   SHOW GLOBAL|SESSION VARIABLES LIKE '%auto%'
   ​
   #查看指定的变量
   SELECT @@GLOBAL|SESSION.系统变量名
   ​
   #为变量赋值
   SET GLOBAL|SESSION 系统变量名 = 值
   SET @@GLOBAL|SESSION.系统变量名 = 值

   作用范围：服务器每一次启动，都会为全局变量赋默认初始值，如果修改了全局变量的值，只在本次服务的生命周期内有效，重启服务器将恢复默认值

2. 自定义变量

   • 用户变量：针对于一次有效的数据库连接

     #声明变量
     语法：
     SET @用户变量名=值
     SET @用户变量名:=值
     SELECT @用户变量名:=值
     ​
     #赋值法一
     SET @用户变量名=值
     SET @用户变量名:=值
     SELECT @用户变量名:=值
     SET @count_people = 0
     ​
     #赋值法二
     SELECT 字段 INTO @变量名 FROM 表(查询的字段值只能由一个)
     SELECT COUNT(*) INTO @count_people FROM employee
     ​
     #查看变量
     SELECT @count_people;

   • 局部变量：只在begin end中有效，而且只能使用在begin end 中第一行

     #声明
     DECLARE 变量名称 类型;
     DECLARE 变量名称 类型 DEFAULT 默认值;
     ​
     #赋值
     SET 局部变量名 = 值;
     SET 局部变量:=值;
     SELECT 字段 INTO 局部变量名 FROM 表;  -- 表中被查询的字段只能有一个 
     SELECT 字段1,字段2 INTO 局部变量1,局部变量2 FROM 表
     ​
     #查看变量：局部变量无法查看

9.2 存储过程

9.2.1 存储过程概述：

• 定义：一组预先编译好的sql语句集

• 优点：语句提高重用性，减少冗余度，减少了sql编译次数，以及减少了与服务器连接交互的次数

9.2.2 语法

1. 参数列表包含三个部分：参数的模式，参数名，参数类型

   #参数列表示意
   IN deptname VARCHAR(20)

    

2. 参数模式

   • IN：需要调用者传入，类似于java中的形参

   • OUT：作为返回值，类似于java中的return

   • INOUT：调用者传入参数，对应的可以有返回值

3. 语法

   #创建语法
   DELIMITER $ -- 定义结束标记
   CREATE PROCEDURE 存储过程名(参数列表)
   BEGIN
   存储过程体(sql语句); -- 存储过程体中的每条语句结尾必须加分号
   END $ -- 利用结束标记结束
   -- 如果存储体过程中只有一句话，则对应的BEGIN END可以省略
   -- 存储过程结尾需要使用delimiter标记，重新设置存储过程的结束标记
   ​
   #存储过程的调用
   CALL 存储过程名(实参)

   #定义存储结构
   DELIMITER $
   CREATE PROCEDURE isr()
   BEGIN 
   INSERT INTO stu_info(sname) VALUE('LDJ');
   END $
   #运行
   CALL isr();
   ​
   ​
   #定义存储结构
   DELIMITER $
   CREATE PROCEDURE findall(INOUT d INT)
   BEGIN
   SELECT * FROM stu_info;
   SELECT COUNT(*) INTO d FROM teacher;
   SELECT @a;
   END $
   ​
   #运行
   CALL findall(@a);
   ​

   #通过部门名称查询对应的员工信息
   DELIMITER $
   CREATE PROCEDURE empinfo(IN deptname VARCHAR(20))
   BEGIN
   SELECT
   d.stuff_id,d.stuff_name,d.department_id
   FROM department d RIGHT JOIN department_type dt 
   ON d.department_id = dt.department_id
   WHERE dt.department_name = deptname;
   END $
   ​
   #调用函数
   CALL empinfo('研发部')

   • 带IN的存储过程

     #通过查询员工部门是否有指定的员工角色
     DELIMITER $
     CREATE PROCEDURE empinfospecify(IN deptname VARCHAR(20),IN roletype VARCHAR(20))
     BEGIN
     DECLARE result INT DEFAULT 0;
     SELECT COUNT(*) INTO result
     FROM department d 
     LEFT JOIN department_type dt
     ON d.department_id = dt.department_id
     LEFT JOIN stuff_role sr
     ON d.stuff_salary BETWEEN sr.MIN AND sr.MAX
     WHERE deptname = dt.department_name AND roletype = sr.role;
     SELECT IF(result > 0,'存在','不存在');
     END $
     ​
     #调用函数
      CALL empinfospecify('研发部','员工');

   • 带OUT的存储过程

     #传入员工姓名返回对应的部门名称
     DELIMITER $
     CREATE PROCEDURE finddeptname(IN stuffname VARCHAR(20),OUT deptname VARCHAR(20))
     BEGIN
     SELECT dt.department_name INTO deptname
     FROM department d LEFT JOIN department_type dt
     ON d.department_id = dt.department_id
     WHERE stuffname = d.stuff_name;
     END $
     ​
     #调用函数
      CALL finddeptname('LJQ',@deptname);
     SELECT @deptname;

   • 带IN的存储过程

     #传入两个工资并计算年薪
     DELIMITER $
     CREATE PROCEDURE salaryear(INOUT salary1 INT,INOUT salary2 INT)
     BEGIN
     SET salary1 = salary1*12;
     SET salary2 = salary2*12;
     END $
     ​
     #调用函数
     SET @salary1 = 1000;
     SET @salary2 = 2343;
     CALL salaryear(@salary1,@salary2);
     SELECT @salary1,@salary2;

9.3 其他存储过程的命令

#删除存储过程
DROP PROCEDURE 存储过程名;
​
#查看存储过程
SHOW CREATE PROCEDURE 存储过程名;

9.4 函数

1. 基础概念

   • 定义：是一组运行编译好的sql语句集

   • 优点：提高重用性，减少冗余度，减少SQL编译次数，以及和服务器连接交互的慈湖

2. 函数和存储过程中的区别

   • 存储过程可以没有返回或多个返回，函数有且只有一个返回

   • 存储过程中适合做批量插入，函数适合做处理数据后返回的结果

3. 语法

   #创建函数语法
   DELIMITER $
   CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型 -- 注意这个是RETURNS不是RETURN
   BEGIN
   函数体 -- 函数体必须有return语句进行返回，其中的return不起到中断作用，放哪里都可以，建议放最后
   END 
   ​
   #调用函数
   SELECT 函数名(参数列表)
   ​

#查看函数 SHOW CREATE FUNCTION 函数名

#删除函数 DROP FUNCTION 函数名

   
   ```mysql
   #返回员工个数
   DELIMITER $
   CREATE FUNCTION stuffcount() RETURNS INT
   BEGIN
   DECLARE c INT DEFAULT 0;
   SELECT COUNT(*)INTO c FROM department;
   RETURN c;
   END $
   
   #调用
   SELECT stuffcount()
   
   #查看函数
   SHOW CREATE FUNCTION stuffcount
   
   #删除函数
   DROP FUNCTION stuffcount

9.5 循环结构

1. WHILE

   #WHILE语法
   循环名:WHILE 循环条件
   DO 
   循环体;
   END 循环名;

   #批量插入到teacher表中,如果大于20条则停止
   DELIMITER $
   CREATE PROCEDURE pro_insert(IN insertCount INT)
   BEGIN
   DECLARE i INT DEFAULT 1;
   pIn:WHILE i<=insertCount DO
   INSERT INTO teacher(tname) VALUES(CONCAT('教师',i));
   SET i=i+1;
   END WHILE pIn;
   END $
   ​
   #调用存储空间
   CALL pro_insert(10);

    

2. LOOP

   #LOOP语法 没有循环条件可以用来模拟死循环
   循环名:LOOP
   循环条件;
   END LOOP 循环名;

3. REPEAT

   #REPEAT语法
   循环名:REPEAT
   循环体;
   UNTIL 循环结束条件
   END REPEAT 循环名

4. 循环控制

   • ITERATE 类似于continue

   • LEAVE 类似于 break

十、数据库的三范式

1. 第一范式：必须要有主键，强调的十列的原子性，即列不能再分割为其他列

2. 第二范式：非主键列必须完全依赖于主键列，完全依赖要求不允许存在非主主键依赖于主键中的某一部分属性（主键有可能是多个属性构成）。满足第二范式的条件是满足第一范式，

3. 第三范式：非主键列必须直接雨来主键列，不能存在传递依赖

十一、附录

• department 表

  stuff_id	stuff_name	stuff_salary	department_id	manager	join_date
  1	LDJ	5000	1	2	2019/7/24
  2	JQQ	9000	2	4	2019/7/24
  3	LXX	4237	0	6	2019/7/24
  4	LXN	24333	2	2	2019/7/24
  5	LNN	12323	2	3	2032/8/1
  6	LN	2343	0		2019/7/24
  7	LJQ	123213	1	3	2019/7/24
  8	LJJ	2343	2	2	2019/7/24
  9	JQ_LN	30033	0	2	2019/7/24
  10	JQQ	223			2019/7/24
  12	LPP	3000		0	2019/7/26
  14	LKA	3000		0	2019/7/26
  15	LBB	3000		0	2019/7/26
  16	XQQ	3000		0	2019/7/26
  18	XJJ	5000		0	2019/7/26
  20	LSS	3333	2	1	2019/7/26

• department_type 表

  department_name	department_id
  人事部	0
  研发部	1
  后勤部	2

• stuff_role 表

  MAX	MIN	role
  14999	5000	员工
  100000	15000	经理
  4999	0	临时工作者
  1		暂无

• teacher 表

  teacher_id	tname
  1	231

• stu_info表

•  

五、MySQL中的数据类型

• 整型

整数类型	字节数	最小值（有符号/无符号）	最大值（有符号/无符号）
TINYINT	1	-128/0	127/255
SMALLINT	2	-32768/0	32767/65535
MEDOIMINT	3	-8388608/0	8388607/1677215
INT,INTEGER	4	-2147483648/0	2147483647/4299497295
BIGINT	8	-9223372036854775808/0	9223372036854775807/18446744073709551616

设置无符号类型：UNSIGNED

• 浮点型

  • float：4个字节

    • FLOAT(M,D) M显示的位数（0~255），D表示精确到第几位（带四舍五入），该写法为非标准写法，建议迁移库的时候不要使用

  • double：8个字节

    • DOUBLE(M,D) ：同FLOAT(M,D)

    • DOUBLE 和FLOAT 在不指定精度时，默认会按照实际精度，但最终的精度是由操作系统所决定的

    • 双精度需要指明标度和精度

  • real：实际上就是DOUBLE ，如果SQL服务器模式为REAL_AS_FLOAT real就是FLOAT（0·30）

• 定点数

  • decimal

    DECIMAL(M,D)：处理规格和浮点型是相同的，M取值范围是0~65，D取值范围为（0~30）

    DECIMAL：mysql内部使用字符串存放，定点型占M+2字节，但是不能大于M的最大存储范围，没有指明精度的时候默认为整数位10，小数位0

  • numeric

    NUMERIC：等价于DEIMAL

• 日期时间类型

  • date：日期 YYYY-MM-DD

  • time：时间 HH:MM:SS

  • datetime：日期时间 YYYY-MM-DD HH:MM:SS

  • year：年份 YYYY和YYl

  • timestamp：时间戳 YYYY-MM-DD HH:MM:SS

    注意：注册类时间，发布类时间不建议使用datetime类型，建议使用时间戳，因为datetime不利于时间计算

• 字符串

  • char(存较短字符串用)固定长度类型)

  • char(m) ，m是最大长度，如果不指定m，则默认m = 1,在数据列里每个值占用M个字符，如果长度小于m对应在右边会用空格补充

  • varchar（可变长，必须指定长度，没有默认值）

  • char是定长的，其处理效率上比VARCHAR高得多，但是比较浪费空间

  • text文本类型，可以存储文本段，不建议使用，text加默认值不起作用

  • 在什么情况下使用char

    1. 存储信息较短

    2. 固定长度的也可使用

    3. 频繁的修改用char类型，varchar在每次改变的是都会区计算

• 二进制类型

  • blob

    二进制大对象容量是可变的，通过容量不同可以分为四种blob类型

    1. TINYBLOB

    2. BLOB

    3. MEDIUBLOB

    4. LONGBLO B

  • binary

  • 两者区别类似于char 和varchar ，不同的是他们存储的不是字符串，存储的是二进制串，所以blob和binary没有字符集，排序通过比较列值中的字节数

• NULL类型

  • 特征：MySQL中所有的类型都可以为NULL

  • 空字符串和0不是NULL

  • 所有运算符和NULL运算都是NULL

  • NULL的判断只能用IS NULL 和IS NOT NULL

  • NULL影响查询速度，所以在SQL优化中尽可能的避免使用NULL

  • Q：为什么建表时，加NOT NULL DEFAULT ''/ DEFAULT 0

    A：不想让表中使用NULL

    Q：为什么不想使用NULL值

    A：①不好比较，NULL是一种类型，比较时只能用专门的 IS NULL 和IS NOT NULL，碰到运算符，一律返回NULL

    ② 效率不高，影响提高所索引的效果

• 枚举：enum，在mysql中是一个字符串对象，值来源于表创建时，在列规定中显示枚举，可以插入空字符串和null值

• 集合：set 和枚举很像，可以包含0-64个成员。set和枚举不同的是，set是通过存储的成员个数来决定字节数，set一次性可以选择多个成员，enum一次只能选择一个

六、约束

定义：就是一种限制，为了保持数据一致性

1 主键约束：PRIMARY KEY

• 使得字段具有唯一性和非空性

2 非空约束：NOT NULL

• 保证字段的值不能为空

3 唯一约束：UNIQUE

• 保证该字段的值是唯一的

4 默认约束：DEFAULT

• 给字段添加默认值

5 检查约束：CHECK

• MySQL中不支持

6 外键约束：

• 用来保证两张表的参照完整性

分类：

1. 列级约束：除外键约束外都可作为列级约束

2. 表级约束：出了默认和非空约束，其他都可以作为表级约束

CREATE TABLE stu_info(
snum INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(15) NOT NULL,
gender CHAR CHECK(gender IN('男','女')),
person_id VARCHAR(20) UNIQUE,
join_timet TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
teacher_id INT REFERENCES teacher(teacher_id) 
)
CREATE TABLE teacher (
teacher_id INT PRIMARY KEY,
tname VARCHAR(20) NOT NULL
)

USE student
CREATE TABLE stu_info(
snum INT AUTO_INCREMENT,
sname VARCHAR(15) NOT NULL,
gender CHAR CHECK(gender IN('男','女')),
person_id VARCHAR(20),
join_timet TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
teacher_id INT ,
CONSTRAINT pk PRIMARY KEY (snum),-- pk时可以省略的
CONSTRAINT fk UNIQUE(sname),
CONSTRAINT gk FOREIGN KEY(teacher_id) REFERENCES teacher(teacher_id)
)
CREATE TABLE teacher (
teacher_id INT PRIMARY KEY,
tname VARCHAR(20) NOT NULL
)
​
#查看表索引
SHOW INDEX FROM stu_info

#创建表之后添加约束
/*
添加表级别约束
ALTER TABLE 表名 ADD[CONSTRAINT 约束名] 约束类型 [KEY](字段名)
添加列级别约束
ALTER TABLE 表名 MODIFY COLUMN 列名 类型 约束类型 [KEY]
*/
​
#创建表之后添加主键约束(主键有默认的索引名，故无需添加索引名)
ALTER TABLE stu_info ADD CONSTRAINT PRIMARY KEY(sid)
ALTER TABLE stu_info MODIFY COLUMN sid INT PRIMARY KEY
​
#创建表之后添加唯一约束
ALTER TABLE stu_info ADD CONSTRAINT uk UNIQUE (sname)
​
#创建表之后添加外键约束
ALTER TABLE stu_info ADD CONSTRAINT fk FOREIGN(teacher_id)
​
#添加非空约束
ALTER TABLE stu_info MODIFY COLUMN sname VARCHAR(20) NOT NULL
​
#删除主键约束,主键唯一，故可不用指定字段
ALTER TABLE stu_info DROP PRIMARY KEY
​
#删除唯一约束（删除唯一约束的时候，是通过删除索引来完成的）
ALTER TABLE stu_info DROP INDEX uq
​
#删除外键约束（删除外键约束时不会删除其索引，所以通常来说需要删除对应的索引）
ALTER TABLE stu_info DROP FOREIGN KEY fk
ALTER TABLE stu_info DROP INDEX fk
​
#删除默认约束
ALTER TABLE stu_info MODIFY COLUMN join_time TIMESTAMP
​
#删除非空约束
ALTER TABLE stu_info MODIFY COLUMN sname VARCHAR(20) NULL
​
#mysql中提供了自增长列,
AUTO_INCREMENT -- 通常放在主键或者唯一键上，一张表只能出现一个自增列
-- 说明：如果一张表中使用了自增长列，如果使用 DELETE 删除的表中的所有数据，则下次添加时自增长
-- 会从断电开始，TRUNCATE则不会
#设置自增列的步长
SET AUTO_INCREMENT_INCREMENT = 2;
​

 

七、事务（tcl事务控制语言）

7.1 概述

定义：一个或者一组sql组成的一个执行单元，数据的添加、修改、删除、查询。表和库没有事务这个说法

特性：acid特性

• 原子性：数据库事务具有不可分割性，要么都成功，要么都失败

• 一致性：事务的前后应该保持一致

• 隔离性：某个事务的操作对其他事务是不可见的

• 持久性：当事务提交之后，其影响应该保留下来，不能撤销

#查看引擎
SHOW ENGINES

7.2 事务开启

自动事务/隐式事务：事务的提交是自动的

#查看AUTOCOMMIT变量
SHOW VARIABLES LIKE 'AUTOCOMMIT' 

切换为手动事务

SET AUTOCOMMIT = FALSE -- FALSE 可替换为 0

事务开启

START TRANSACTION;

结束事务

COMMIT 提交事务
ROLLBACK 回滚事务

事务回滚演示

#事务回滚演示
SET autocommit = FALSE;
SHOW VARIABLES autocommit;
#开启事务，结束事务之前的语句都是事务
START TRANSACTION;
INSERT INTO stu_info(sname,gender,person_id,teacher_id)
VALUES('ljq','m','1',1);
INSERT INTO stu_info(sname,gender,person_id,teacher_id)
VALUES('ljq',m,'12321421324',1); -- 有错语句
#结束事务
-- COMMIT; #提交事务
-- ROLLBACK; #回滚事务到事务开启之前的状态，事务一旦提交不能回滚

7.3事务的隔离级别

实质是多线程并发问题

1. 脏读（脏数据）：对于两个事务：T1、T2，T1读取了T2更新但是还没有提交的数据，如果此时T2回滚，T1读取的数据是临时无效数据

2. 不可重复度：对于两个事务：T1、T2，T1读取了一个字段，T2更新了一个字段并提交，此时T1在读取相同字段时值会改变

3. 幻读：主要针对于修改和插入，T1、T2两个事务，T1从表中修改了一条数据，T2从表中添加数据，添加的数据满足T1修改数据的条件，则对应T1会修改T2未提交的数据，TI在修改的时候会发现多出了几条修改的数据

隔离级别	描述
read-uncommitted	允许事务去读其他食物未提交的数据。脏读，不可重复读，幻读都无法解决
read-committed-	只允许读其他食物已经提交的数据，可解决脏读，但是依旧不能处理不可重复，幻读问题
repeatable-read	确保事务可以多次从一个字段中读取相同的值，解决了不可重复读的问题，依旧会出现幻读
serializable	确保事务可以从一个表中读取相同的行，事务持续期间禁止其他事务执行插入、更新、删除操作，可以解决幻读问题，但一般没人用

#查看当前的默认隔离级别
SELECT @@TX_ISOLATION;
#修改隔离级别
SET SESSION  TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; #SESSION 加上之后可以立马生效

事务回滚

#关于 ROLLBACK 
set autocommit = FALSE;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT @@tx_isolation;
START TRANSACTION;
SAVEPOINT b; -- 设置回滚点
DELETE FROM stu_info;
SELECT * FROM stu_info;
ROLLBACK TO b; -- 回滚到b
SELECT * FROM stu_info; --再次查看表

说明：

1. 在innodb存储引擎中事务隔离级别前三个默认使用行级锁，serializable加标记锁

2. trancate清除表的时候无法回滚

#创建表时指定引擎
CREATE TABLE score(
id int;
)ENGINE = INNODB DEFAULT CHARACTER=utf8

serializable实验

1. T1

   Microsoft Windows [版本 10.0.17763.652]
   (c) 2018 Microsoft Corporation。保留所有权利。
   ​
   C:\Users\18392>mysql -uroot -proot
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 60
   Server version: 5.5.54 MySQL Community Server (GPL)
   ​
   Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
   ​
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   ​
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   ​
   mysql> set session transaction isolation level serializable
       -> ;
   Query OK, 0 rows affected (0.00 sec)
   ​
   mysql> select @@tx_isolation;
   +----------------+
   | @@tx_isolation |
   +----------------+
   | SERIALIZABLE   |
   +----------------+
   1 row in set (0.00 sec)
   ​
   mysql> start transaction
       -> ;
   Query OK, 0 rows affected (0.00 sec)
   ​
   mysql> select * from stu_info;
   ERROR 1046 (3D000): No database selected
   mysql> set session transaction isolation level serializable
       -> exit
       -> ;
   ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'exit' at line 2
   mysql> exit
   Bye
   ​
   C:\Users\18392>mysql -uroot -proot
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 63
   Server version: 5.5.54 MySQL Community Server (GPL)
   ​
   Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
   ​
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   ​
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   ​
   mysql> use student
   Database changed
   mysql>  set session transaction isolation level serializable;
   Query OK, 0 rows affected (0.00 sec)
   ​
   mysql> select @@tx_isolation;
   +----------------+
   | @@tx_isolation |
   +----------------+
   | SERIALIZABLE   |
   +----------------+
   1 row in set (0.00 sec)
   ​
   mysql>  start transaction;
   Query OK, 0 rows affected (0.00 sec)
   ​
   mysql> select * from stu_info;
   +------+-----------+--------+-----------+---------------------+------------+
   | snum | sname     | gender | person_id | join_timet          | teacher_id |
   +------+-----------+--------+-----------+---------------------+------------+
   |    1 | 杩炴帴      | n      | we        | 2019-07-29 15:13:00 |          1 |
   |    2 | 閮痉绾?   | m      | 13        | 2019-07-29 15:18:06 |          1 |
   +------+-----------+--------+-----------+---------------------+------------+
   2 rows in set (0.00 sec)
   ​
   mysql> set names utf8;
   Query OK, 0 rows affected (0.00 sec)
   ​
   mysql> select * from stu_info;
   +------+-----------+--------+-----------+---------------------+------------+
   | snum | sname     | gender | person_id | join_timet          | teacher_id |
   +------+-----------+--------+-----------+---------------------+------------+
   |    1 | 杩炴帴      | n      | we        | 2019-07-29 15:13:00 |          1 |
   |    2 | 閮痉绾?   | m      | 13        | 2019-07-29 15:18:06 |          1 |
   +------+-----------+--------+-----------+---------------------+------------+
   2 rows in set (0.00 sec)
   ​
   mysql> update stu_info set sname = 'ljq';
   Query OK, 2 rows affected (0.07 sec)
   Rows matched: 2  Changed: 2  Warnings: 0
   ​
   mysql> update stu_info set sname = 'ln';
   Query OK, 2 rows affected (0.00 sec)
   Rows matched: 2  Changed: 2  Warnings: 0
   ​
   mysql> update stu_info set sname = 'lnn';
   Query OK, 2 rows affected (0.00 sec)
   Rows matched: 2  Changed: 2  Warnings: 0
   ​
   mysql> update stu_info set sname = 'ln';
   Query OK, 2 rows affected (0.00 sec)
   Rows matched: 2  Changed: 2  Warnings: 0
   ​
   mysql> commit;
   Query OK, 0 rows affected (0.00 sec)
   ​
   mysql> select * from stu_info;
   +------+-------+--------+-----------+---------------------+------------+
   | snum | sname | gender | person_id | join_timet          | teacher_id |
   +------+-------+--------+-----------+---------------------+------------+
   |    1 | ln    | n      | we        | 2019-07-29 15:13:00 |          1 |
   |    2 | ln    | m      | 13        | 2019-07-29 15:18:06 |          1 |
   +------+-------+--------+-----------+---------------------+------------+
   2 rows in set (0.00 sec)
   ​
   mysql> select * from stu_info;
   +------+-------+--------+-----------+---------------------+------------+
   | snum | sname | gender | person_id | join_timet          | teacher_id |
   +------+-------+--------+-----------+---------------------+------------+
   |    1 | ln    | n      | we        | 2019-07-29 15:13:00 |          1 |
   +------+-------+--------+-----------+---------------------+------------+
   1 row in set (0.00 sec)

2. T2

   Microsoft Windows [版本 10.0.17763.652]
   (c) 2018 Microsoft Corporation。保留所有权利。
   ​
   C:\Users\18392>mysql -uroot -proot
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 61
   Server version: 5.5.54 MySQL Community Server (GPL)
   ​
   Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
   ​
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   ​
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   ​
   mysql>  set session transaction isolation level serializable;
   Query OK, 0 rows affected (0.00 sec)
   ​
   mysql> select @@tx_isolation;
   +----------------+
   | @@tx_isolation |
   +----------------+
   | SERIALIZABLE   |
   +----------------+
   1 row in set (0.00 sec)
   ​
   mysql> start transaction;
   Query OK, 0 rows affected (0.00 sec)
   ​
   mysql> exit
   Bye
   ​
   C:\Users\18392>mysql -uroot -proot
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 62
   Server version: 5.5.54 MySQL Community Server (GPL)
   ​
   Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
   ​
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   ​
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   ​
   mysql> use student
   Database changed
   mysql>  set session transaction isolation level serializable;
   Query OK, 0 rows affected (0.00 sec)
   ​
   mysql> select @@tx_isolation;
   +----------------+
   | @@tx_isolation |
   +----------------+
   | SERIALIZABLE   |
   +----------------+
   1 row in set (0.00 sec)
   ​
   mysql>  start transaction;
   Query OK, 0 rows affected (0.00 sec)
   ​
   mysql> delete from stu_info where snum = 1;
   ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
   mysql> delete from stu_info where snum = 1;
   ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
   mysql> delete from stu_info where snum = 2;
   Query OK, 1 row affected (29.15 sec)
   ​
   mysql> select * from stu_info;
   +------+-------+--------+-----------+---------------------+------------+
   | snum | sname | gender | person_id | join_timet          | teacher_id |
   +------+-------+--------+-----------+---------------------+------------+
   |    1 | ln    | n      | we        | 2019-07-29 15:13:00 |          1 |
   +------+-------+--------+-----------+---------------------+------------+
   1 row in set (0.00 sec)
   ​
   mysql> commit;
   Query OK, 0 rows affected (0.00 sec)

八、视图

8.1概述

视图的定义：保存一组sql语句的逻辑。不保存数据，只保存一组sql的逻辑

创建视图的语法

CREATE VIEW 视图名
AS
编写的sql

视图一般只用来查找

SELECT * FROM 视图名

视图的用法和表一样

创建视图

#创建视图
#查询各部门员工的姓名，角色，部门名称，工资级别
CREATE VIEW salarys
AS
SELECT
d.stuff_name,
dt.department_id

FROM
department d JOIN department_type dt ON d.department_id = dt.department_id;

8.2修改视图

#修改视图
​
#方法一
CREATE OR REPLACE VIEW 视图名
AS
新的sql语句
​
#方法二
ALTER VIEW SALARYS
AS
新的sql语句

#更改视图一
CREATE OR REPLACE VIEW salays
AS
SELECT * FROM department;
​
#更改视图二
ALTER VIEW salays
AS
SELECT * FROM department;
​

8.3 删除视图

DROP VIEW 视图1，视图2…

#删除视图
DROP VIEW salarys;

8.4 查询视图

#查询视图法一
SELECT 
* 
FROM
视图名
​
#查询视图法二(看详细的语法)
SHOW CREATE VIEW salarys;
​
#查看视图法三
DESC salarys；

#查询视图法一
SELECT
*
FROM 
salays;
​
#查询视图法二(可以看详细的语法)
SHOW CREATE VIEW salarys;
​
#查看视图法三
DESC salarys；

总结：视图一般情况下只做查询，保存sql的逻辑，不做数据保存

九 存储过程和函数

9.1变量

和Java中的变量是一样的，都需要声明，赋值之后才能使用

1. 系统变量：系统提供的，不需要自定义

   • 全局变量

   • 会话变量

   查看系统变量的语法

   #查所有全局变量|会话变量
   SHOW GLOBAL|SESSION VARIABLES -- 若不指明是GLOBAL|SESSION默认查询SESSION
   ​
   #通过模糊查询方式查找
   SHOW GLOBAL|SESSION VARIABLES LIKE '%auto%'
   ​
   #查看指定的变量
   SELECT @@GLOBAL|SESSION.系统变量名
   ​
   #为变量赋值
   SET GLOBAL|SESSION 系统变量名 = 值
   SET @@GLOBAL|SESSION.系统变量名 = 值

   作用范围：服务器每一次启动，都会为全局变量赋默认初始值，如果修改了全局变量的值，只在本次服务的生命周期内有效，重启服务器将恢复默认值

2. 自定义变量

   • 用户变量：针对于一次有效的数据库连接

     #声明变量
     语法：
     SET @用户变量名=值
     SET @用户变量名:=值
     SELECT @用户变量名:=值
     ​
     #赋值法一
     SET @用户变量名=值
     SET @用户变量名:=值
     SELECT @用户变量名:=值
     SET @count_people = 0
     ​
     #赋值法二
     SELECT 字段 INTO @变量名 FROM 表(查询的字段值只能由一个)
     SELECT COUNT(*) INTO @count_people FROM employee
     ​
     #查看变量
     SELECT @count_people;

   • 局部变量：只在begin end中有效，而且只能使用在begin end 中第一行

     #声明
     DECLARE 变量名称 类型;
     DECLARE 变量名称 类型 DEFAULT 默认值;
     ​
     #赋值
     SET 局部变量名 = 值;
     SET 局部变量:=值;
     SELECT 字段 INTO 局部变量名 FROM 表;  -- 表中被查询的字段只能有一个 
     SELECT 字段1,字段2 INTO 局部变量1,局部变量2 FROM 表
     ​
     #查看变量：局部变量无法查看

9.2 存储过程

9.2.1 存储过程概述：

• 定义：一组预先编译好的sql语句集

• 优点：语句提高重用性，减少冗余度，减少了sql编译次数，以及减少了与服务器连接交互的次数

9.2.2 语法

1. 参数列表包含三个部分：参数的模式，参数名，参数类型

   #参数列表示意
   IN deptname VARCHAR(20)

    

2. 参数模式

   • IN：需要调用者传入，类似于java中的形参

   • OUT：作为返回值，类似于java中的return

   • INOUT：调用者传入参数，对应的可以有返回值

3. 语法

   #创建语法
   DELIMITER $ -- 定义结束标记
   CREATE PROCEDURE 存储过程名(参数列表)
   BEGIN
   存储过程体(sql语句); -- 存储过程体中的每条语句结尾必须加分号
   END $ -- 利用结束标记结束
   -- 如果存储体过程中只有一句话，则对应的BEGIN END可以省略
   -- 存储过程结尾需要使用delimiter标记，重新设置存储过程的结束标记
   ​
   #存储过程的调用
   CALL 存储过程名(实参)

   #定义存储结构
   DELIMITER $
   CREATE PROCEDURE isr()
   BEGIN 
   INSERT INTO stu_info(sname) VALUE('LDJ');
   END $
   #运行
   CALL isr();
   ​
   ​
   #定义存储结构
   DELIMITER $
   CREATE PROCEDURE findall(INOUT d INT)
   BEGIN
   SELECT * FROM stu_info;
   SELECT COUNT(*) INTO d FROM teacher;
   SELECT @a;
   END $
   ​
   #运行
   CALL findall(@a);
   ​

   #通过部门名称查询对应的员工信息
   DELIMITER $
   CREATE PROCEDURE empinfo(IN deptname VARCHAR(20))
   BEGIN
   SELECT
   d.stuff_id,d.stuff_name,d.department_id
   FROM department d RIGHT JOIN department_type dt 
   ON d.department_id = dt.department_id
   WHERE dt.department_name = deptname;
   END $
   ​
   #调用函数
   CALL empinfo('研发部')

   • 带IN的存储过程

     #通过查询员工部门是否有指定的员工角色
     DELIMITER $
     CREATE PROCEDURE empinfospecify(IN deptname VARCHAR(20),IN roletype VARCHAR(20))
     BEGIN
     DECLARE result INT DEFAULT 0;
     SELECT COUNT(*) INTO result
     FROM department d 
     LEFT JOIN department_type dt
     ON d.department_id = dt.department_id
     LEFT JOIN stuff_role sr
     ON d.stuff_salary BETWEEN sr.MIN AND sr.MAX
     WHERE deptname = dt.department_name AND roletype = sr.role;
     SELECT IF(result > 0,'存在','不存在');
     END $
     ​
     #调用函数
      CALL empinfospecify('研发部','员工');

   • 带OUT的存储过程

     #传入员工姓名返回对应的部门名称
     DELIMITER $
     CREATE PROCEDURE finddeptname(IN stuffname VARCHAR(20),OUT deptname VARCHAR(20))
     BEGIN
     SELECT dt.department_name INTO deptname
     FROM department d LEFT JOIN department_type dt
     ON d.department_id = dt.department_id
     WHERE stuffname = d.stuff_name;
     END $
     ​
     #调用函数
      CALL finddeptname('LJQ',@deptname);
     SELECT @deptname;

   • 带IN的存储过程

     #传入两个工资并计算年薪
     DELIMITER $
     CREATE PROCEDURE salaryear(INOUT salary1 INT,INOUT salary2 INT)
     BEGIN
     SET salary1 = salary1*12;
     SET salary2 = salary2*12;
     END $
     ​
     #调用函数
     SET @salary1 = 1000;
     SET @salary2 = 2343;
     CALL salaryear(@salary1,@salary2);
     SELECT @salary1,@salary2;

9.3 其他存储过程的命令

#删除存储过程
DROP PROCEDURE 存储过程名;
​
#查看存储过程
SHOW CREATE PROCEDURE 存储过程名;

9.4 函数

1. 基础概念

   • 定义：是一组运行编译好的sql语句集

   • 优点：提高重用性，减少冗余度，减少SQL编译次数，以及和服务器连接交互的慈湖

2. 函数和存储过程中的区别

   • 存储过程可以没有返回或多个返回，函数有且只有一个返回

   • 存储过程中适合做批量插入，函数适合做处理数据后返回的结果

3. 语法

   #创建函数语法
   DELIMITER $
   CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型 -- 注意这个是RETURNS不是RETURN
   BEGIN
   函数体 -- 函数体必须有return语句进行返回，其中的return不起到中断作用，放哪里都可以，建议放最后
   END 
   ​
   #调用函数
   SELECT 函数名(参数列表)
   ​

#查看函数 SHOW CREATE FUNCTION 函数名

#删除函数 DROP FUNCTION 函数名

   
   ```mysql
   #返回员工个数
   DELIMITER $
   CREATE FUNCTION stuffcount() RETURNS INT
   BEGIN
   DECLARE c INT DEFAULT 0;
   SELECT COUNT(*)INTO c FROM department;
   RETURN c;
   END $
   
   #调用
   SELECT stuffcount()
   
   #查看函数
   SHOW CREATE FUNCTION stuffcount
   
   #删除函数
   DROP FUNCTION stuffcount

9.5 循环结构

1. WHILE

   #WHILE语法
   循环名:WHILE 循环条件
   DO 
   循环体;
   END 循环名;

   #批量插入到teacher表中,如果大于20条则停止
   DELIMITER $
   CREATE PROCEDURE pro_insert(IN insertCount INT)
   BEGIN
   DECLARE i INT DEFAULT 1;
   pIn:WHILE i<=insertCount DO
   INSERT INTO teacher(tname) VALUES(CONCAT('教师',i));
   SET i=i+1;
   END WHILE pIn;
   END $
   ​
   #调用存储空间
   CALL pro_insert(10);

    

2. LOOP

   #LOOP语法 没有循环条件可以用来模拟死循环
   循环名:LOOP
   循环条件;
   END LOOP 循环名;

3. REPEAT

   #REPEAT语法
   循环名:REPEAT
   循环体;
   UNTIL 循环结束条件
   END REPEAT 循环名

4. 循环控制

   • ITERATE 类似于continue

   • LEAVE 类似于 break

十、数据库的三范式

1. 第一范式：必须要有主键，强调的十列的原子性，即列不能再分割为其他列

2. 第二范式：非主键列必须完全依赖于主键列，完全依赖要求不允许存在非主主键依赖于主键中的某一部分属性（主键有可能是多个属性构成）。满足第二范式的条件是满足第一范式，

3. 第三范式：非主键列必须直接雨来主键列，不能存在传递依赖

4. 实际开发中三范式能遵守尽量遵守，因为有些情况下我们是需要冗余度去提高效率

十一、附录

• department 表

  stuff_id	stuff_name	stuff_salary	department_id	manager	join_date
  1	LDJ	5000	1	2	2019/7/24
  2	JQQ	9000	2	4	2019/7/24
  3	LXX	4237	0	6	2019/7/24
  4	LXN	24333	2	2	2019/7/24
  5	LNN	12323	2	3	2032/8/1
  6	LN	2343	0		2019/7/24
  7	LJQ	123213	1	3	2019/7/24
  8	LJJ	2343	2	2	2019/7/24
  9	JQ_LN	30033	0	2	2019/7/24
  10	JQQ	223			2019/7/24
  12	LPP	3000		0	2019/7/26
  14	LKA	3000		0	2019/7/26
  15	LBB	3000		0	2019/7/26
  16	XQQ	3000		0	2019/7/26
  18	XJJ	5000		0	2019/7/26
  20	LSS	3333	2	1	2019/7/26

• department_type 表

  department_name	department_id
  人事部	0
  研发部	1
  后勤部	2

• stuff_role 表

  MAX	MIN	role
  14999	5000	员工
  100000	15000	经理
  4999	0	临时工作者
  1		暂无

• teacher 表

  teacher_id	tname
  1	231

• stu_info表

• snum	sname	gender	person_id	join_timet	teacher_id
  1	ln	n	we	2019/7/29 15:13	1
  19	LDJ			2019/7/30 9:52
  20	LDJ			2019/7/30 9:53