系统的捡一下Mysql

使用mysql很久了，但没有系统的学习过mysql，看看漏掉了那些基础但又有用东西。

一、Mysql基础使用

1.1 Mysql命令

1. 命令结束符号

一个命令要想执行必须使用;、\g、\G这几个符号之一作为结尾。

符号\g等同于;，是我们常用的命令结束语句。\G可以将数据表格转换为横向，即第一列为字段名，然后数据依次在后面的列，对字段很多的时候的查询展示很有帮助。

2. 可以一次提交多个命令

例如：三个语句同时混合上边的命令结束符号

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> select now(); select now()\g select now()\G
+---------------------+
| now()               |
+---------------------+
| 2022-05-12 11:34:49 |
+---------------------+
1 row in set (0.000 sec)

+---------------------+
| now()               |
+---------------------+
| 2022-05-12 11:34:49 |
+---------------------+
1 row in set (0.000 sec)

*************************** 1. row ***************************
now(): 2022-05-12 11:34:49
1 row in set (0.000 sec)

3. 使用\c放弃本次操作

可以使用如果你想放弃本次编写的命令，可以在输入的命令后边加上\c，比如这样：

1
2
mysql> SELECT NOW()\c
mysql>

如果不使用\c，那客户端会以为这是一个多行命令，还在一直傻傻的等你输入命令～

二、MySQL数据类型

2.1 MySQL整型类型

类型 占用的存储空间（单位：字节） 无符号数取值范围 有符号数取值范围
TINYINT 1 $0\sim 2^8-1$ $-2^7\sim 2^7-1$
SMALLINT 2 $0\sim 2^{16}-1$ $-2^{15}\sim -2^{15}-1$
MEDIUMINT 3 $0\sim 2^{24}-1$ $-2^{23}\sim 2^{23}-1$
INT/INTEGER 4 $0 \sim 2^{32} - 1$ $-2^{31} \sim 2^{31}-1$
BIGINT 8 $0 \sim 2^{64} - 1$ $-2^{63} \sim 2^{63} - 1$

2.2 MySQL浮点型类型

类型 占用的存储空间（单位：字节） 绝对值最小非0值 绝对值最大非0值
FLOAT 4 ±1.175494351E-38 ±3.402823466E+38
DOUBLE 8 ±2.2250738585072014E-308 ±1.7976931348623157E+308

2.3 设置最大位数和小数位数

1
2
3
4

```sql
FLOAT(M, D)
DOUBLE(M, D)

可以用来限制存储到本列中的小数范围。其中

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

2.4 定点数类型

类型DECIMAL(M, D),占用的存储空间取决于M和D，取值范围也取决于M和D。

2.5 日期和时间类型

类型 存储空间要求 取值范围 含义
YEAR 1字节 $1901\sim 2155$ 年份值
DATE 3字节 $’1000-01-01’\sim ‘9999-12-31’$ 日期值
TIME 3字节+小数秒的存储空间 $’-838:59:59[.000000]’ \sim ‘838:59:59[.000000]’$ 时间值
DATETIME 5字节+小数秒的存储空间 $’1000-01-01 00:00:00[.000000]’ ～ ‘9999-12-31 23:59:59[.999999]’$ 日期加时间值
TIMESTAMP 4字节+小数秒的存储空间 $’1970-01-01 00:00:01[.000000]’ ～ ‘2038-01-19 03:14:07[.999999]’$ 时间戳

小数秒的存储空间：

保留的小数秒位数 额外需要的存储空间
0 0字节
1/2 1字节
3/4 2字节
5/6 3字节

2.5 MySQL的字符串类型

类型 最大长度 存储空间要求 备注
CHAR(M) M个字符 M*W个字节 M取值范围0 ~ 255，默认为1CHAR和CHAR(1)同义，CHAR(0)只能存储''或者NULL；
CHAR会对剩余的空间补空格字符补齐
VARCHAR(M) M个字符 L+1或L+2个字节 M理论取值范围0 ~ 65535，
但MySQL规定，表中某一行包含的所有列的数据大小总共不得超过65535字节，所以VARCHAR(M)字符数量是小于65535的
TINYTEXT $2^8-1$个字节 L+1个字节 不受65535限制
TEXT $2^{16} - 1$字节 L+2个字节 不受65535限制
MEDIUMTEXT $2^{24} - 1$个字节 L+3个字节 不受65535限制
LONGTEXT $2^{32} - 1$个字节 L+4个字节 不受65535限制

其中M代表该数据类型最多能存储的字符数量，L代表我们实际向该类型的属性中存储的字符串在特定字符集下所占的字节数，W代表在该特定字符集下，编码一个字符最多需要的字节数（ascii取1，gbk取2，utf8取3等）。

2.6 ENUM类型和SET类型

1
2
ENUM('str1', 'str2', 'str3')
SET('str1', 'str2', 'str3')

2.7 二进制类型

  • BIT(M)
  • BINARY(M)/VARBINARY(M)
  • TINYBLOB
  • BLOB
  • MEDIUMBLOB
  • LONGBLOB

三、存储函数

3.1 创建存储函数

1
2
3
4
5
CREATE FUNCTION 存储函数名称([参数列表])
RETURNS 返回值类型
BEGIN
    函数体内容
END

例如（要先用delimiter修改语句结束分割符号）：

1
2
3
4
5
6
7
8
9
mysql> delimiter $
mysql> CREATE FUNCTION avg_score(s VARCHAR(100))
    -> RETURNS DOUBLE
    -> BEGIN
    ->     RETURN (SELECT AVG(score) FROM student_score WHERE subject = s);
    -> END $
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

3.2 查看和删除存储函数

查看存储函数：

1
SHOW FUNCTION STATUS [LIKE 需要匹配的函数名]

查看函数定义：

1
SHOW CREATE FUNCTION 函数名

自定义变量：

1
SET @value = 1;

3.3 判断语句的编写

1
2
3
4
5
6
7
8
IF 表达式 THEN
    处理语句列表
[ELSEIF 表达式 THEN
    处理语句列表]
... # 这里可以有多个ELSEIF语句
[ELSE
    处理语句列表]
END IF;

3.4 循环语句的编写

WHILE循环语句

1
2
3
WHILE 表达式 DO
    处理语句列表
END WHILE;

REPEAT循环语句

1
2
3
REPEAT
    处理语句列表
UNTIL 表达式 END REPEAT;

LOOP循环语句

1
2
3
LOOP
    处理语句列表
END LOOP;

四、存储过程

4.1 创建存储过程

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

例如：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> delimiter $
mysql> CREATE PROCEDURE t1_operation(
    ->     m1_value INT,
    ->     n1_value CHAR(1)
    -> )
    -> BEGIN
    ->     SELECT * FROM t1;
    ->     INSERT INTO t1(m1, n1) VALUES(m1_value, n1_value);
    ->     SELECT * FROM t1;
    -> END $
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql>

4.2 存储过程的调用

1
CALL 存储过程([参数列表]);

4.3 查看和删除存储过程

查看当前数据库中创建的存储过程都有哪些的语句：

1
SHOW PROCEDURE STATUS [LIKE 需要匹配的存储过程名称]

查看某个存储过程具体是怎么定义的语句：

1
SHOW CREATE PROCEDURE 存储过程名称

删除存储过程的语句：

1
DROP PROCEDURE 存储过程名称

4.4 存储过程参数前缀

前缀IN，OUT，INOUT

五、游标的使用

5.1 创建游标

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

5.2 打开和关闭游标

在创建完游标之后，我们需要手动打开和关闭游标，语法也简单：

1
2
3
OPEN 游标名称;

CLOSE 游标名称;

5.3 使用游标获取记录

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

每调用一次 FETCH 语句，游标就移动到下一条记录的位置。

六、触发器和事件

6.1 触发器

我们使用MySQL的过程中可能会有下边这些需求：

  • 在向t1表插入或更新数据之前对自动对数据进行校验，要求m1列的值必须在1~10之间，校验规则如下：
    • 如果插入的记录的m1列的值小于1，则按1插入。
    • 如果m1列的值大于10，则按10插入。
  • 在向t1表中插入记录之后自动把这条记录插入到t2表。

也就是我们在对表中的记录做增、删、改操作前和后都可能需要让MySQL服务器自动执行一些额外的语句，这个就是所谓的触发器的应用场景。

6.1.1 创建触发器

1
2
3
4
5
6
7
8
CREATE TRIGGER 触发器名
{BEFORE|AFTER}
{INSERT|DELETE|UPDATE}
ON 表名
FOR EACH ROW
BEGIN
    触发器内容
END

6.1.2 查看和删除触发器

查看当前数据库中定义的所有触发器的语句：

1
SHOW TRIGGERS;

查看某个具体的触发器的定义：

1
SHOW CREATE TRIGGER 触发器名;

删除触发器：

1
DROP TRIGGER 触发器名;

6.2 事件

有时候我们想让MySQL服务器在某个时间点或者每隔一段时间自动地执行一些语句，这时候就需要去创建一个事件。

6.2.1 创建事件

1
2
3
4
5
6
7
8
9
10
CREATE EVENT 事件名
ON SCHEDULE
{
    AT 某个确定的时间点| 
    EVERY 期望的时间间隔 [STARTS datetime][END datetime]
}
DO
BEGIN
    具体的语句
END

事件支持两种类型的自动执行方式：

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

    比如：

    1
    2
    3
    4
    5
    6
    7
    CREATE EVENT insert_t1_event
    ON SCHEDULE
    AT '2019-09-04 15:48:54'
    DO
    BEGIN
        INSERT INTO t1(m1, n1) VALUES(6, 'f');
    END

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

    比如：

    1
    2
    3
    4
    5
    6
    7
    CREATE EVENT insert_t1
    ON SCHEDULE
    EVERY 1 HOUR
    DO
    BEGIN
        INSERT INTO t1(m1, n1) VALUES(6, 'f');
    END

6.2.2 查看和删除事件

查看当前数据库中定义的所有事件的语句：

1
SHOW EVENTS;

查看某个具体的事件的定义：

1
SHOW CREATE EVENT 事件名;

删除事件：

1
DROP EVENT 事件名;

6.2.3 事件使用注意事项

默认情况下，MySQL服务器并不会帮助我们执行事件，除非我们使用下边的语句手动开启该功能：

1
2
3
4
mysql> SET GLOBAL event_scheduler = ON;
Query OK, 0 rows affected (0.00 sec)

mysql>

小贴士： event_scheduler其实是一个系统变量，它的值也可以在MySQL服务器启动的时候通过启动参数或者通过配置文件来设置event_scheduler的值。这些所谓的系统变量、启动参数、配置文件的各种东东并不是我们小白现在需要掌握的，大家忽略它们就好了～

#mysql
系统的捡一下Mysql
https://wttch96.github.io/post/mysql/系统的捡一下Mysql.html
作者
Wttch
发布于
2022年5月11日
许可协议