vlambda博客
学习文章列表

数据结构

浮点型

浮点数

FLOAT(M, D)  // 4字节
DOUBLE(M, D)  // 8字节  

• M 表示该小数最多需要的十进制有效数字个数

• D 表示该小数的小数点后的十进制数字个数

在D相同的情况下，M越大，该类型的取值范围越大；在M相同的情况下，D越大，该类型的取值范围越小, M和D都是可选的，如果我们省略了它们，那它们的值按照机器支持的最大值来存储

定点数

DECIMAL(M, D)

占用空间随着M和D的值的变动而变动,M的范围是1~65，D的范围是0~30，且D的值不能超过M

日期和时间类型

在MySQL5.6.4这个版本之后，TIME、DATETIME、TIMESTAMP这几种类型添加了对毫秒、微秒的支持。由于毫秒、微秒都不到1秒，所以也被称为小数秒，MySQL最多支持6位小数秒的精度，各个位代表的意思如下：

例: DATETIME(0)表示精确到秒，DATETIME(3)表示精确到毫秒，DATETIME(5)表示精确到10微秒

保留小数位数及其对应额外占用空间如下:

小数秒位数	存储空间
0	0字节
1~2	1字节
3~4	2字节
5~6	3字节

字符串类型

正宗的utf8字符集是使用1~4个字节来编码一个字符,MySQL中对utf8字符集做了阉割，编码一个字符最多使用3个字节,MySQL中正宗的utf8字符集为utf8mb4

MySQL中还有一个规定，表中某一行包含的所有列中存储的数据大小总共不得超过65535个字节，TEXT,MEDIUMTEXT,LONGTEXT等特殊类型不受此限制

基本操作

数据库

展示数据库:  SHOW DATABASES;

创建数据库:  CREATE DATABASE IF NOT EXISTS 数据库名;

切换数据库:  USE 数据库名;

登录即选择数据库: mysql -h localhost -u root -p123456 数据库名

删除数据库: DROP DATABASE IF EXISTS 数据库名;

数据表

展示当前数据库中所有表:  SHOW TABLES;

创建表:

CREATE TABLE IF NOT EXISTS 表名 (
    列名1    数据类型    [列的属性],
    列名2    数据类型    [列的属性],
    ...
    列名n    数据类型    [列的属性]
) COMMENT '表的注释信息';

删除表:  DROP TABLE IF EXISTS 表1, 表2, ..., 表n;

查看表结构:

DESCRIBE 表名;
DESC 表名;
EXPLAIN 表名;
SHOW COLUMNS FROM 表名;
SHOW FIELDS FROM 表名;
SHOW CREATE TABLE 表名;

修改表

改表名:

RENAME TABLE 旧表名1 TO 新表名1, 旧表名2 TO 新表名2, ... 旧表名n TO 新表名n;

增加列:

ALTER TABLE 表名 ADD COLUMN 列名 数据类型 [列的属性];
ALTER TABLE 表名 ADD COLUMN 列名 列的类型 [列的属性] FIRST;  # 增加列到第一列
ALTER TABLE 表名 ADD COLUMN 列名 列的类型 [列的属性] AFTER 指定列名;  # 添加到指定列的后边

删除列:

ALTER TABLE 表名 DROP COLUMN 列名;

修改列:

ALTER TABLE 表名 MODIFY 列名 新数据类型 [新属性];  # 仅修改列属性
ALTER TABLE 表名 MODIFY 列名 列的类型 列的属性 FIRST; # 修改列属性并移动到第一列
ALTER TABLE 表名 MODIFY 列名 列的类型 列的属性 AFTER 指定列名; # 修改列属性并移动到指定列后面
ALTER TABLE 表名 CHANGE 旧列名 新列名 新数据类型 [新属性]; # 修改列名称及属性
ALTER TABLE 表名 操作1, 操作2, ..., 操作n; # 多个修改操作

列属性

主键:

PRIMARY KEY (列名1, 列名2, ...)  # 单独定义  单个列作为主键也可以在列后面声明 PRIMARY KEY

1. 一个表最多只能有一个主键

2. 主键的值不能重复, 通过主键可以找到唯一的一条记录

UNIQUE:

UNIQUE [KEY] [约束名称] (列名1, 列名2, ...) # 单独定义  也可以在列后面声明 UNIQUE或者UNIQUE KEY

主键与UNIQUE区别:

1. 一张表中只能定义一个主键，UNIQUE可以有多个

2. 主键列不允许存放NULL，UNIQUE属性的列可以存放NULL，而且NULL可以重复地出现在多条记录中

外键:

CONSTRAINT [外键名称] FOREIGN KEY(列1, 列2, ...) REFERENCES 父表名(父列1, 父列2, ...); # 定义

父表中被子表依赖的列或者列组合必须建立索引

AUTO_INCREMENT:

列名 列的类型 AUTO_INCREMENT

一个表中最多有一个具有AUTO_INCREMENT属性的列

具有AUTO_INCREMENT属性的列必须建立索引

拥有AUTO_INCREMENT属性的列就不能再通过指定DEFAULT属性来指定默认值

一般拥有AUTO_INCREMENT属性的列都是作为主键的属性，来自动生成唯一标识一条记录的主键值

数据查询

简单查询

SELECT [DISTINCT] 查询列表
[FROM 表名]
[WHERE 布尔表达式]
[GROUP BY 分组列表 ]
[HAVING 分组过滤条件]
[ORDER BY 排序列表]
[LIMIT 开始行, 限制条数]; # 去重查询,多列重复代表两条结果的每一个列中的值都相同

分组注意事项:

1. 如果分组列中含有NULL值，那么NULL也会作为一个独立的分组存在

2. 如果存在多个分组列，也就是嵌套分组，聚集函数将作用在最后的那个分组列上

3. 查询语句中存在WHERE子句和ORDER BY子句，那么GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前

4. 非分组列不能单独出现在检索列表中(可以被放到聚集函数中)

5. GROUP BY子句后也可以跟随表达式(但不能是聚集函数)

6. WHERE子句和HAVING子句的区别: WHERE子句在分组前进行过滤，作用于每一条记录，WHERE子句过滤掉的记录将不包括在分组中。而HAVING子句在数据分组后进行过滤，作用于整个分组

子查询

不相关子查询:

子查询和外层查询都没有依赖关系,子查询可以独立运行并产生结果，然后再拿结果作为外层查询的条件去执行外层查询

SELECT * FROM student_score WHERE number = (SELECT number FROM student_info WHERE name = '杜琦燕'); # 标量子查询 子查询的结果只有一个值
SELECT * FROM student_score WHERE number IN (SELECT number FROM student_info WHERE major = '计算机科学与工程'); # 列子查询 子查询的结果是一个列
SELECT * FROM student_score WHERE (number, subject) = (SELECT number, '母猪的产后护理' FROM student_info LIMIT 1); # 行子查询 子查询的结果是一个行
SELECT * FROM student_score WHERE (number, subject) IN (SELECT number, '母猪的产后护理' FROM student_info WHERE major = '计算机科学与工程'); # 表子查询 子查询的结果包含多行多列
SELECT * FROM student_score WHERE [NOT] EXISTS (SELECT * FROM student_info WHERE number = 20180108); # EXISTS/NOT EXISTS子查询

相关子查询:

在子查询的语句中引用到外层查询的值，子查询就不能当作一个独立的语句去执行

SELECT number, name, id_number, major FROM student_info WHERE EXISTS (SELECT * FROM student_score WHERE student_score.number = student_info.number);
SELECT * FROM student_score WHERE subject = '母猪的产后护理' AND score > (SELECT AVG(score) FROM student_score WHERE subject = '母猪的产后护理'); # 同表子查询

连接查询

SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];  # 左（外）连接
SELECT * FROM t1 RIGHT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];  # 右（外）连接
SELECT * FROM t1 [INNER | CROSS] JOIN t2 [ON 连接条件] [WHERE 普通过滤条件];  # 内连接

数据增删改

插入数据

INSERT [IGNORE] INTO 表名 VALUES (列1的值，列2的值, ..., 列n的值)[,(列1的值，列2的值, ..., 列n的值)...] [ON DUPLICATE KEY UPDATE 表达式];

插入记录的时候，某些列的值可以被省略，但是这个列必须满足以下条件之一:

• 该列允许存储NULL值

• 该列有DEFAULT属性，给出了默认值

IGNORE:

对于主键或者UNIQUE约束的列或者列组合，如果表中已存在的记录中没有与待插入记录在这些列或者列组合上重复的值，那么就把待插入记录插到表中，否则忽略此次插入操作

ON DUPLICATE KEY UPDATE:

对于主键或者UNIQUE约束的列或者列组合，如果表中已存在的记录中没有与待插入记录在这些列或者列组合上重复的值，那么就把待插入记录插到表中，否则按照规定去更新那条重复的记录中某些列的值

删除数据

DELETE FROM 表名 [WHERE 表达式];

更新数据

UPDATE 表名 SET 列1=值1, 列2=值2, ...,  列n=值n [WHERE 布尔表达式];

视图

视图特性:

1. 可以对视图进行一些类似表的增删改查操作

2. 对视图的相关操作都会被映射到查询语句对应的底层表上

3. 创建视图的并不会把查询语句的结果集维护在硬盘或者内存里,操作视图时，MySQL会把对视图的操作语句转换为对底层表的操作语句再执行

创建

CREATE VIEW 视图名(自定义列名1,自定义列名2...) AS 查询语句

查看

SHOW TABLES;  # 查看视图列表
SHOW CREATE VIEW 视图名;  # 查看视图的定义

更新

在生成视图的时候使用了下列语句的都不能进行更新:

• 聚集函数（比如SUM(), MIN(), MAX(), COUNT()等等）

• DISTINCT

• GROUP BY

• HAVING

• UNION 或者 UNION ALL

• 某些子查询

• 某些连接查询

删除

DROP VIEW 视图名

存储程序

封装一些语句，然后给用户提供一种简单的方式来调用这个存储程序，从而间接地执行这些语句

根据调用方式的不同分类如下:

自定义变量

SET @a = 1;  # 设置自定义变量并赋值
SET @a = (SELECT m1 FROM t1 LIMIT 1);  # 设置自定义变量并赋值
SELECT n1 FROM t1 LIMIT 1 INTO @b;  # 将查询的结果赋值给一个变量
SELECT m1, n1 FROM t1 LIMIT 1 INTO @a, @b;  # 查询结果是有多个列的值，把这几个列的值分别赋值到不同的变量中
SET @b = @a;  # 把一个变量的值赋值给另一个变量

SELECT @a;  # 查看自定义变量的值

语句结束分隔符

默认语句结束分隔符:  ;、\g或者\G

自定义语句结束分隔符:  delimiter 分隔符 ,  eg:  delimiter $

存储函数

定义

CREATE FUNCTION 存储函数名称([参数列表])
RETURNS 返回值类型
BEGIN
    函数体内容(一条或多条语句)
END

eg

CREATE FUNCTION avg_score(s VARCHAR(100))
RETURNS DOUBLE
BEGIN
    RETURN (SELECT AVG(score) FROM student_score WHERE subject = s);
END;

调用

SELECT avg_score('母猪的产后护理');

查看

SHOW CREATE FUNCTION 函数名

删除

DROP FUNCTION 函数名

函数体定义

在函数体中定义局部变量

DECLARE 变量名1, 变量名2, ... 数据类型 [DEFAULT 默认值]; # 在函数体中定义局部变量,未赋值则默认值是NULL,DECLARE语句必须放到其他语句的前边

eg

DECLARE c INT DEFAULT 1;  # 声明一个名称为c的INT类型的默认值为1的局部变量

在函数体中使用自定义变量

SET @abc = 10;

存储函数的参数

参数名 数据类型 # 定义存储函数时，可以指定多个参数,用逗号分隔

• 每个参数都要指定对应的数据类型

• 参数名不能和函数体语句中的其他变量名、查询语句列名冲突

• 函数参数不可以指定默认值，在调用函数的时必须显式的指定所有的参数，并且参数类型一定要匹配

判断语句

IF 表达式 THEN
    处理语句列表
[ELSEIF 表达式 THEN
    处理语句列表]  # 处理语句列表中可以包含多条语句，每条语句以分号;结尾
... # 这里可以有多个ELSEIF语句
[ELSE
    处理语句列表]
END IF;

循环语句

WHILE循环语句

WHILE 表达式 DO
    处理语句列表
END WHILE;
# 先判断表达式的值，再执行处理语句

REPEAT循环语句

REPEAT
    处理语句列表
UNTIL 表达式 END REPEAT;
# 先执行处理语句，再判断表达式的值，至少执行一次处理语句

LOOP循环语句

LOOP
    处理语句列表
END LOOP;

LOOP循环终止条件写在处理语句里，终止方式有俩种：

1. 使用return语句返回

   CREATE FUNCTION sum_all(n INT UNSIGNED)
   RETURNS INT
   BEGIN
       DECLARE result INT DEFAULT 0;
       DECLARE i INT DEFAULT 1;
       LOOP
           IF i > n THEN
               RETURN result;
           END IF;
           SET result = result + i;
           SET i = i + 1;
       END LOOP;
   END

2. 使用LEAVE语句

   CREATE FUNCTION sum_all(n INT UNSIGNED)
   RETURNS INT
   BEGIN
       DECLARE result INT DEFAULT 0;
       DECLARE i INT DEFAULT 1;
       # 在LOOP语句前加了一个flag:，相当于为这个循环打了一个名叫flag的标记
       flag:LOOP
           IF i > n THEN
            	# 使用LEAVE flag语句来结束flag这个标记所代表的循环
               LEAVE flag;
           END IF;
           SET result = result + i;
           SET i = i + 1;
       END LOOP flag;
       RETURN result;
   END

存储过程

创建

CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
    需要执行的语句
END

与存储函数最直观的不同点就是，存储过程的定义不需要声明返回值类型

调用

CALL 存储过程([参数列表]); #存储过程在执行中产生的所有结果集，全部将会被显示到客户端

只有查询语句才会产生结果集

查看与删除

SHOW PROCEDURE STATUS [LIKE 需要匹配的存储过程名称] # 查看当前数据库中有哪些存储过程
SHOW CREATE PROCEDURE 存储过程名称  # 查看某个存储过程具体定义语句

DROP PROCEDURE 存储过程名称  # 删除指定存储过程

存储函数中使用到的各种语句，包括变量的使用、判断、循环结构都可以被用在存储过程中

参数前缀

比存储函数强大的一点是，存储过程在定义参数的时候可以选择将不同参数定义为不同的类型

参数类型[IN | OUT | INOUT] 参数名 数据类型

前缀（参数类型）	实际参数是否必须是变量	描述
IN	否	仅用于调用者向存储过程传递数据，缺省值
OUT	是	仅用于把存储过程运行过程中产生的数据赋值给OUT参数，可供外部使用
INOUT	是	可以用于调用者向存储过程传递数据，也可以用于存放存储过程中产生的数据以供调用者使用

eg

CREATE PROCEDURE get_score_data(
        OUT max_score DOUBLE,
        OUT min_score DOUBLE,
    	OUT avg_score DOUBLE,
    	s VARCHAR(100)
    )
BEGIN
    SELECT MAX(score), MIN(score), AVG(score) FROM student_score WHERE subject = s INTO max_score, min_score, avg_score;
END;

存储过程和存储函数的不同点

• 存储函数在定义时需要显式用RETURNS语句标明返回的数据类型，而且在函数体中必须使用RETURN语句来显式指定返回的值，存储过程不需要

• 存储函数只支持IN参数，而存储过程支持IN参数、OUT参数、和INOUT参数

• 存储函数只能返回一个值，而存储过程可以通过设置多个OUT参数或者INOUT参数来返回多个结果

• 存储函数执行过程中产生的结果集并不会被显示到客户端，而存储过程执行过程中产生的结果集会被显示到客户端

• 存储函数直接在表达式中调用，而存储过程只能通过CALL语句来显式调用

游标

创建游标

DECLARE 游标名称 CURSOR FOR 查询语句;

eg

CREATE PROCEDURE cursor_demo()
BEGIN
    DECLARE t1_record_cursor CURSOR FOR SELECT m1, n1 FROM t1;
END

如果存储程序中也有声明局部变量的语句，创建游标的语句一定要放在局部变量声明后头

打开和关闭

# 打开游标，意味着执行查询语句，创建一个该查询语句得到的结果集关联起来的游标
OPEN 游标名称;

# 关闭游标意味着会释放该游标相关的资源，使用完后一定要关闭，如果不显式的使用CLOSE语句关闭游标的话，在该存储函数或存储过程的END语句执行完之后会自动关闭
CLOSE 游标名称;

使用游标获取记录

FETCH 游标名 INTO 变量1, 变量2, ... 变量n

eg

CREATE PROCEDURE cursor_demo()
BEGIN
    DECLARE m_value INT;
    DECLARE n_value CHAR(1);
    DECLARE record_count INT;
    DECLARE i INT DEFAULT 0;

    DECLARE t1_record_cursor CURSOR FOR SELECT m1, n1 FROM t1;

    SELECT COUNT(*) FROM t1 INTO record_count;

    OPEN t1_record_cursor;

    WHILE i < record_count DO
    	# 每调用一次 FETCH 语句，游标就移动到下一条记录的位置
        FETCH t1_record_cursor INTO m_value, n_value;
        SELECT m_value, n_value;
        SET i = i + 1;
    END WHILE;

    CLOSE t1_record_cursor;
END

遍历结束时的执行策略

在FETCH语句获取不到记录的时候会触发一个事件，从而我们可以得知所有的记录都被获取过了，然后我们就可以去主动的停止循环。MySQL中响应这个事件的语句如下

# 在FETCH语句获取不到记录的时候，服务器就会执行我们填写的处理语句,处理语句可以是简单的一条语句，也可以是由BEGIN ... END 包裹的多条语句
DECLARE CONTINUE HANDLER FOR NOT FOUND 处理语句;

eg

CREATE PROCEDURE cursor_demo()
BEGIN
    DECLARE m_value INT;
    DECLARE n_value CHAR(1);
    DECLARE not_done INT DEFAULT 1;

    DECLARE t1_record_cursor CURSOR FOR SELECT m1, n1 FROM t1;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_done = 0;

    OPEN t1_record_cursor;

    flag: LOOP
        FETCH t1_record_cursor INTO m_value, n_value;
        IF not_done = 0 THEN
            LEAVE flag;
        END IF;
        SELECT m_value, n_value, not_done;
    END LOOP flag;

    CLOSE t1_record_cursor;
END

触发器

定义

CREATE TRIGGER 触发器名
{BEFORE|AFTER} # 表示触发器内容执行的时机，BEFORE表示在具体的语句执行之前，AFTER表示在具体的语句执行之后
{INSERT|DELETE|UPDATE}
ON 表名
FOR EACH ROW
BEGIN
    触发器内容
END

• 由大括号{}包裹并且内部用竖线|分隔的语句表示必须在给定的选项中选取一个值，比如{BEFORE|AFTER}表示必须在BEFORE、AFTER这两个之间选取一个。

• {BEFORE|AFTER} 表示触发器内容执行的时机，BEFORE表示在具体的语句执行之前，AFTER表示在具体的语句执行之后

• MySQL目前只支持对INSERT、DELETE、UPDATE这三种类型的语句设置触发器

• FOR EACH ROW BEGIN ... END表示对具体语句影响的每一条记录都执行我们自定义的触发器内容，对于INSERT语句来说，FOR EACH ROW影响的就是准备插入的新记录，对于DELETE语句和UPDATE语句来说，FOR EACH ROW影响的就是符合WHERE条件的那些记录

• 如果触发器内容只有一条语句，则可以省略BEGN、END

MySQL服务器会对某条语句影响的所有记录依次调用触发器内容，针对每一条受影响的记录，MySQL提供了NEW和OLD两个单词来分别代表新记录和旧记录，在不同语句中的含义：

• 对于INSERT语句设置的触发器，NEW代表准备插入的记录，OLD无效。

• 对于DELETE语句设置的触发器，OLD代表删除前的记录，NEW无效。

• 对于UPDATE语句设置的触发器，NEW代表修改后的记录，OLD代表修改前的记录。

eg

CREATE TRIGGER bi_t1
    BEFORE INSERT ON t1
    FOR EACH ROW
    BEGIN
       IF NEW.m1 < 1 THEN
           SET NEW.m1 = 1;
   	    ELSEIF NEW.m1 > 10 THEN
    	SET NEW.m1 = 10;
    END IF;
END

对t1表定义了一个名叫bi_t1的触发器，在对t1表插入新记录之前，对准备插入的每一条记录都会执行BEGIN ... END之间的语句，NEW.列名表示当前待插入记录指定列的值

查看与删除

SHOW TRIGGERS;  # 查看当前数据库中定义的所有触发器
SHOW CREATE TRIGGER 触发器名;  # 查看某个触发器的具体定义

DROP TRIGGER 触发器名;  # 删除触发器

脚下留心

1. 触发器内容中不能有输出结果集的语句

2. 触发器内容中NEW代表记录的列的值可以被更改，OLD代表记录的列的值无法更改

3. BEFORE触发器中可以使用SET NEW.列名 = 某个值的形式来更改待插入记录或者待更新记录的某个列的值，但是这种操作不能在AFTER触发器中使用，因为在执行AFTER触发器的内容时记录已经被插入完成或者更新完成了

4. 如果BEFORE触发器内容执行过程中遇到了错误，那这个触发器对应的具体语句将无法执行；如果具体的操作语句执行过程中遇到了错误，那与它对应的AFTER触发器的内容也无法执行

5. 对于支持事务的表，不论是执行触发器内容还是具体操作语句过程中出现了错误，就会把这个过程中所有的语句都回滚

事件

作用：让MySQL服务器在某个时间点或者每隔一段时间自动地执行一些语句

创建

CREATE EVENT 事件名
ON SCHEDULE
{
    AT 某个确定的时间点 | EVERY 期望的时间间隔 [STARTS datetime][END datetime]
}
DO
BEGIN
    具体的语句
END

执行方式

1. 在某个确定的时间点执行

eg

CREATE EVENT insert_t1
ON SCHEDULE
AT DATE_ADD(NOW(), INTERVAL 2 DAY)
DO
BEGIN
    INSERT INTO t1(m1, n1) VALUES(6, 'f');
END

DATE_ADD(NOW(), INTERVAL 2 DAY)表示该事件将在当前时间的两天后执行

2. 每隔一段时间执行一次

eg

CREATE EVENT insert_t1
ON SCHEDULE
EVERY 1 HOUR STARTS '2019-09-04 15:48:54' ENDS '2019-09-16 15:48:54'
DO
BEGIN
    INSERT INTO t1(m1, n1) VALUES(6, 'f');
END

该事件将从'2019-09-04 15:48:54'开始直到'2019-09-16 15:48:54'为止，中间每隔1个小时执行一次, STARTS 和ENDS可省略，表示从创建事件的事件开始，无限制的执行下去

表示事件间隔的单位：YEAR、QUARTER、MONTH、DAY、HOUR、 MINUTE、WEEK、SECOND、YEAR_MONTH、DAY_HOUR、DAY_MINUTE、DAY_SECOND、HOUR_MINUTE、HOUR_SECOND、MINUTE_SECOND

查看与删除

SHOW EVENTS;  # 查看当前数据库中定义的所有事件
SHOW CREATE EVENT 事件名;  # 查看某个事件的具体定义

DROP EVENT 事件名;  # 删除事件

脚下留心

默认情况下，MySQL服务器并不会执行事件，需要使用下边的语句手动开启该功能

SET GLOBAL event_scheduler = ON;

event_scheduler是一个系统变量，它的值也可以在MySQL服务器启动的时候通过启动参数或者通过配置文件来设置