MySQL使用基础

# 登录数据库
[root@wulan ~]# mysql -uroot -p
Enter password: 

# 查看已建立数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set ( 0. 00 sec )

# 查看数据库用户
mysql> select user, host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set ( 0. 00 sec )

# 创建数据库
mysql> CREATE DATABASE sujx;
Query OK, 1 row affected ( 0. 00 sec )

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sujx               |
| sys                |
+--------------------+
5 rows in set ( 0. 00 sec )

# 切换默认数据库
mysql> use sujx;
Database changed

# 删除数据库
mysql> drop database sujx;
Query OK, 0 rows affected ( 0. 01 sec )

# 导入示例数据库
mysql> source /root/world-db/world.sql;

# 查询world数据库的表
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set ( 0. 00 sec )

# 计算city表有多少个值
mysql> select count( * ) from city;
+----------+
| count( * ) |
+----------+
|     4079 |
+----------+
1 row in set ( 0. 00 sec )

# 计算country表有多少个值
mysql> select count( * ) from country;
+----------+
| count( * ) |
+----------+
|      239 |
+----------+
1 row in set ( 0. 01 sec )

# 查询数据
mysql> select * from city;
+------+------------------------------------+-------------+------------------------+------------+
| ID   | Name                               | CountryCode | District               | Population |
+------+------------------------------------+-------------+------------------------+------------+
|    1 | Kabul                              | AFG         | Kabol                  |    1780000 |
|    2 | Qandahar                           | AFG         | Qandahar               |     237500 |
|    3 | Herat                              | AFG         | Herat                  |     186800 |
|    4 | Mazar-e-Sharif                     | AFG         | Balkh                  |     127800 |
|    5 | Amsterdam                          | NLD         | Noord-Holland          |     731200 |

# 备份数据库
# 备份整库
[root@wulan ~]# mysqldump -uroot -hlocalhost -p world >dump-world.sql
# 备份表
[root@wulan ~]# mysqldump -uroot -hlocalhost -p world city > dump-city.sql

# 创建测试表
mysql> create database sujx;
Query OK, 1 row affected ( 0. 00 sec )

mysql> use sujx;
Database changed
mysql> create table first_table( 
    -> first_column INT,
    -> second_column VARCHAR( 100 )
    ->  );
Query OK, 0 rows affected ( 0. 02 sec )

# 查找数据库中的表
mysql> show tables;
+----------------+
| Tables_in_sujx |
+----------------+
| first_table    |
+----------------+
1 row in set ( 0. 00 sec )
# 查看表的数据结构
mysql> describe first_table;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| first_column  | int          | YES  |     | NULL    |       |
| second_column | varchar( 100 ) | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
2 rows in set ( 0. 00 sec )
# 查看表的内容
mysql> select * from first_table;
Empty set ( 0. 00 sec )

# 重命名表
mysql> rename table first_table to tESt_Table;
Query OK, 0 rows affected ( 0. 02 sec )

mysql> show tables;
+----------------+
| Tables_in_sujx |
+----------------+
| tESt_Table     |
+----------------+
3 rows in set ( 0. 00 sec )

# 删除表
mysql> drop table tESt_Table;
Query OK, 0 rows affected ( 0. 02 sec )

# 创建学生信息表
mysql> create table student_info( 
    -> number int,
    -> name varchar( 5 ),
    -> sex ENUM( '男', '女' ),
    -> id_number char( 18 ),
    -> department varchar( 30 ),
    -> major varchar( 30 ),
    -> enrollment_time date )
    -> comment '学生信息表';
# 查看创建的学生信息表    
mysql> describe student_info;
+-----------------+-------------------+------+-----+---------+-------+
| Field           | Type              | Null | Key | Default | Extra |
+-----------------+-------------------+------+-----+---------+-------+
| number          | int               | YES  |     | NULL    |       |
| name            | varchar( 5 )        | YES  |     | NULL    |       |
| sex             | enum( '男', '女' )   | YES  |     | NULL    |       |
| id_number       | char( 18 )          | YES  |     | NULL    |       |
| department      | varchar( 30 )       | YES  |     | NULL    |       |
| major           | varchar( 30 )       | YES  |     | NULL    |       |
| enrollment_time | date              | YES  |     | NULL    |       |
+-----------------+-------------------+------+-----+---------+-------+
7 rows in set ( 0. 00 sec )
   
# 创建学生成绩表
mysql> create table student_score ( 
    -> number int,
    -> subject varchar( 30 ),
    -> score tinyint )
    -> comment '学生成绩表';
Query OK, 0 rows affected ( 0. 02 sec )

# 查看成绩表的结构
mysql> describe student_score;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| number  | int         | YES  |     | NULL    |       |
| subject | varchar( 30 ) | YES  |     | NULL    |       |
| score   | tinyint     | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set ( 0. 01 sec )

# 查看测试对象的数据结构
mysql> describe tESt_Table;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| one   | int         | YES  |     | NULL    |       |
| two   | varchar( 5 )  | YES  |     | NULL    |       |
| three | varchar( 10 ) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set ( 0. 00 sec )

# 新增一个列，名为four
mysql> alter table tESt_Table add column four char( 4 );
Query OK, 0 rows affected ( 0. 04 sec )
Records: 0  Duplicates: 0  Warnings: 0

# 新增一个列，名为zero，但要排到第一
mysql> alter table tESt_Table add column zero char( 4 ) first;
Query OK, 0 rows affected ( 0. 02 sec )
Records: 0  Duplicates: 0  Warnings: 0

# 新增一个列，指定跟在zero之后
mysql> alter table tESt_Table add column start char( 4 ) after zero;
Query OK, 0 rows affected ( 0. 02 sec )
Records: 0  Duplicates: 0  Warnings: 0

# 查看当前表结构
mysql> describe tESt_Table;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| zero  | char( 4 )     | YES  |     | NULL    |       |
| start | char( 4 )     | YES  |     | NULL    |       |
| one   | int         | YES  |     | NULL    |       |
| two   | varchar( 5 )  | YES  |     | NULL    |       |
| three | varchar( 10 ) | YES  |     | NULL    |       |
| four  | char( 4 )     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
6 rows in set ( 0. 00 sec )

# 删除新增列
mysql> alter table tESt_Table drop column start;
Query OK, 0 rows affected ( 0. 01 sec )
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe tESt_Table;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| zero  | char( 4 )     | YES  |     | NULL    |       |
| one   | int         | YES  |     | NULL    |       |
| two   | varchar( 5 )  | YES  |     | NULL    |       |
| three | varchar( 10 ) | YES  |     | NULL    |       |
| four  | char( 4 )     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set ( 0. 00 sec )

# 修改列属性
# 修改zero列的类型为int型
mysql> alter table tESt_Table modify zero int;
Query OK, 0 rows affected ( 0. 07 sec )
Records: 0  Duplicates: 0  Warnings: 0

#修改zero列名为start
mysql> alter table tESt_Table change zero start int;
Query OK, 0 rows affected ( 0. 02 sec )
Records: 0  Duplicates: 0  Warnings: 0

# 将start列放到最后
mysql> alter table tESt_Table modify start int after four;
Query OK, 0 rows affected ( 0. 04 sec )
Records: 0  Duplicates: 0  Warnings: 0

# 查看表的结构
mysql> describe tESt_Table;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| one   | int         | YES  |     | NULL    |       |
| two   | varchar( 5 )  | YES  |     | NULL    |       |
| three | varchar( 10 ) | YES  |     | NULL    |       |
| four  | char( 4 )     | YES  |     | NULL    |       |
| start | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set ( 0. 00 sec

# 简单的查询
mysql> select * from first_table;
Empty set ( 0. 00 sec )

# 插入数据，只指定one two 就只插入对应列的值，其他为null
mysql> insert into first_table( one, two ) values( 1, 'AaA' );
Query OK, 1 row affected ( 0. 01 sec )

mysql> select * from first_table;
+------+------+-------+------+-------+
| one  | two  | three | four | start |
+------+------+-------+------+-------+
|    1 | AaA  | NULL  | NULL |  NULL |
+------+------+-------+------+-------+
1 row in set ( 0. 00 sec )

# 只指定tree列，则指插入对应列的值，其他为null
mysql> insert into first_table( three ) values( 2 );
Query OK, 1 row affected ( 0. 00 sec )

mysql> insert into first_table( four ) values( 'BbB' );
Query OK, 1 row affected ( 0. 00 sec )
# 只要不指定对应列名，对应值则为null
mysql> select * from first_table;
+------+------+-------+------+-------+
| one  | two  | three | four | start |
+------+------+-------+------+-------+
|    1 | AaA  | NULL  | NULL |  NULL |
| NULL | NULL | 2     | NULL |  NULL |
| NULL | NULL | NULL  | BbB  |  NULL |
+------+------+-------+------+-------+
3 rows in set ( 0. 00 sec )

# 同时插入多条数据
mysql> insert into first_table( one, two, three, four, start ) values( 1, 2, 3, 4, 5 ), ( 11, 12, 13, 14, 15 ), ( 21, 'BBB', 'CCC', 'DDD', '25' );

mysql> select * from first_table;
+------+------+-------+------+-------+
| one  | two  | three | four | start |
+------+------+-------+------+-------+
|    1 | AaA  | NULL  | NULL |  NULL |
| NULL | NULL | 2     | NULL |  NULL |
| NULL | NULL | NULL  | BbB  |  NULL |
|    1 | 2    | 3     | 4    |     5 |
|   11 | 12   | 13    | 14   |    15 |
|   21 | BBB  | CCC   | DDD  |    25 |
+------+------+-------+------+-------+
6 rows in set ( 0. 00 sec )

# 删除旧表
mysql> drop table first_table;
Query OK, 0 rows affected ( 0. 02 sec )
# 创建新表，第二行默认值为abc
mysql> create table first_table( first_column INT, second_column varchar( 100 ) default 'abc' );
Query OK, 0 rows affected ( 0. 02 sec )

mysql> insert into first_table( first_column ) values( 1 );
Query OK, 1 row affected ( 0. 00 sec )

mysql> select * from first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | abc           |
+--------------+---------------+
1 row in set ( 0. 00 sec )
# 向表插入数据
mysql> insert into first_table( first_column, second_column ) values( 2, 'BBB' );
Query OK, 1 row affected ( 0. 00 sec )
# 设定表的第一列属性非空
mysql> alter table first_table modify first_column INT NOT NULL;
Query OK, 0 rows affected ( 0. 05 sec )
# 空值无法输入
mysql> insert into first_table( first_column, second_column ) values( NULL, 'BBB' );
ERROR 1048 ( 23000 ): Column 'first_column' cannot be null

# 主键
# 一个表最多只能有一个主键，主键的值不能重复，通过主键可以找到唯一的记录。单列为主键，则直接指定PRIMARY KEY。
# 如果一个列组合为主键，则使用PRIMAY KEY( A， B )的形式
# 创建学生信息表
mysql> create table student_info( number INT PRIMARY KEY, name VARCHAR( 5 ), sex ENUM( '男', '女' ), id_number CHAR( 18 ), department VARCHAR( 30 ), major VARCHAR( 30 ), enrollment_time DATE, UNIQUE KEY ( id_number )  );
Query OK, 0 rows affected ( 0. 03 sec )
mysql> describe student_info;
+-----------------+-------------------+------+-----+---------+-------+
| Field           | Type              | Null | Key | Default | Extra |
+-----------------+-------------------+------+-----+---------+-------+
| number          | int               | NO   | PRI | NULL    |       |
| name            | varchar( 5 )        | YES  |     | NULL    |       |
| sex             | enum( '男', '女' )   | YES  |     | NULL    |       |
| id_number       | char( 18 )          | YES  | UNI | NULL    |       |
| department      | varchar( 30 )       | YES  |     | NULL    |       |
| major           | varchar( 30 )       | YES  |     | NULL    |       |
| enrollment_time | date              | YES  |     | NULL    |       |
+-----------------+-------------------+------+-----+---------+-------+
7 rows in set ( 0. 00 sec )

# UNIQUE 约束
# 声明列值是唯一的，如果有重复数据插入，MySQL报错，拒绝插入新数据
# 创建学生成绩表
mysql> create table student_score( number INT, subject VARCHAR( 30 ), score TINYINT, PRIMARY KEY ( number, subject ), constraint foreign key ( number ) references student_info( number ));
Query OK, 0 rows affected ( 0. 03 sec )
mysql> describe student_score;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| number  | int         | NO   | PRI | NULL    |       |
| subject | varchar( 30 ) | NO   | PRI | NULL    |       |
| score   | tinyint     | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set ( 0. 00 sec )

# 插入数据
mysql> insert into student_info( number, name, sex, id_number, department, major, enrollment_time ) values ( 20210101, '狗哥', '男', '158177200301044792', '计算机学院', '计算机科学与工程', '2021-09-01' ),  ( 20210102, '猫爷', '男', '151008200201178529', '计算机学院', '计算机科学与工程', '2021-09-01'  ), ( 20210103, '艾希', '女', '171563200201178529', '计算机学院', '软件工程', '2021-09-01'  ), ( 20210104, '亚索', '男', '141992200201078600', '计算机学院', '软件工程', '2021-09-01'  ) , ( 20210105, '莫甘娜', '女', '181048200008156368', '航天学院', '飞行器设计', '2021-09-01'  ), ( 20210106, '赵信', '男', '197995200201078445', '航天学院', '电子信息', '2021-09-01'  );
Query OK, 5 rows affected ( 0. 01 sec )
mysql> select * from student_info;
+----------+-----------+------+--------------------+-----------------+--------------------------+-----------------+
| number   | name      | sex  | id_number          | department      | major                    | enrollment_time |
+----------+-----------+------+--------------------+-----------------+--------------------------+-----------------+
| 20210101 | 狗哥      | 男   | 158177200301044792 | 计算机学院      | 计算机科学与工程         | 2021-09-01      |
| 20210102 | 猫爷      | 男   | 151008200201178529 | 计算机学院      | 计算机科学与工程         | 2021-09-01      |
| 20210103 | 艾希      | 女   | 171563200201178529 | 计算机学院      | 软件工程                 | 2021-09-01      |
| 20210104 | 亚索      | 男   | 141992200201078600 | 计算机学院      | 软件工程                 | 2021-09-01      |
| 20210105 | 莫甘娜    | 女   | 181048200008156368 | 航天学院        | 飞行器设计               | 2021-09-01      |
| 20210106 | 赵信      | 男   | 197995200201078445 | 航天学院        | 电子信息                 | 2021-09-01      |
+----------+-----------+------+--------------------+-----------------+--------------------------+-----------------+
6 rows in set ( 0. 00 sec )

Records: 5  Duplicates: 0  Warnings: 0
mysql> insert into student_score ( number, subject, score ) values ( 20210101, '计算机是怎样运行的', 78 ), ( 20210101, 'MySQL是怎样运行的', 88 ), ( 20210102, '计算机是怎样运行的', 100 ), ( 20210102, 'MySQL是怎样运行
的', 98 ), ( 20210103, '计算机是怎样运行的', 59 ), ( 20210103, 'MySQL是怎样运行的', 61 ), ( 20210104, '计算机是怎样运行的', 55 ), ( 20210104, 'MySQL是怎样运行的', 46 );
Query OK, 8 rows affected ( 0. 00 sec )
Records: 8  Duplicates: 0  Warnings: 0

mysql> select * from student_score;
+----------+-----------------------------+-------+
| number   | subject                     | score |
+----------+-----------------------------+-------+
| 20210101 | MySQL是怎样运行的           |    88 |
| 20210101 | 计算机是怎样运行的          |    78 |
| 20210102 | MySQL是怎样运行的           |    98 |
| 20210102 | 计算机是怎样运行的          |   100 |
| 20210103 | MySQL是怎样运行的           |    61 |
| 20210103 | 计算机是怎样运行的          |    59 |
| 20210104 | MySQL是怎样运行的           |    46 |
| 20210104 | 计算机是怎样运行的          |    55 |
+----------+-----------------------------+-------+
8 rows in set ( 0. 00 sec )

# 查询单个列
mysql> select number from student_info;
+----------+
| number   |
+----------+
| 20210104 |
| 20210102 |
| 20210101 |
| 20210103 |
| 20210105 |
| 20210106 |
+----------+
6 rows in set ( 0. 00 sec )

# 别名
mysql> select number AS 学号 FROM student_info;
+----------+
| 学号     |
+----------+
| 20210104 |
| 20210102 |
| 20210101 |
| 20210103 |
| 20210105 |
| 20210106 |
+----------+
6 rows in set ( 0. 00 sec )

# 查询多个列
mysql> select number, name, id_number, major from student_info;
+----------+-----------+--------------------+--------------------------+
| number   | name      | id_number          | major                    |
+----------+-----------+--------------------+--------------------------+
| 20210101 | 狗哥      | 158177200301044792 | 计算机科学与工程         |
| 20210102 | 猫爷      | 151008200201178529 | 计算机科学与工程         |
| 20210103 | 艾希      | 171563200201178529 | 软件工程                 |
| 20210104 | 亚索      | 141992200201078600 | 软件工程                 |
| 20210105 | 莫甘娜    | 181048200008156368 | 飞行器设计               |
| 20210106 | 赵信      | 197995200201078445 | 电子信息                 |
+----------+-----------+--------------------+--------------------------+
6 rows in set ( 0. 00 sec )

mysql> select number AS 学号, name AS 姓名, id_number AS 身份证号, major AS 专业 from student_info;
+----------+-----------+--------------------+--------------------------+
| 学号     | 姓名      | 身份证号           | 专业                     |
+----------+-----------+--------------------+--------------------------+
| 20210101 | 狗哥      | 158177200301044792 | 计算机科学与工程         |
| 20210102 | 猫爷      | 151008200201178529 | 计算机科学与工程         |
| 20210103 | 艾希      | 171563200201178529 | 软件工程                 |
| 20210104 | 亚索      | 141992200201078600 | 软件工程                 |
| 20210105 | 莫甘娜    | 181048200008156368 | 飞行器设计               |
| 20210106 | 赵信      | 197995200201078445 | 电子信息                 |
+----------+-----------+--------------------+--------------------------+
6 rows in set ( 0. 00 sec )

# 去重单列
mysql> select distinct department from student_info;
+-----------------+
| department      |
+-----------------+
| 计算机学院      |
| 航天学院        |
+-----------------+
2 rows in set ( 0. 00 sec )

# 多列去重
mysql> select distinct department, major from student_info;
+-----------------+--------------------------+
| department      | major                    |
+-----------------+--------------------------+
| 计算机学院      | 计算机科学与工程         |
| 计算机学院      | 软件工程                 |
| 航天学院        | 飞行器设计               |
| 航天学院        | 电子信息                 |
+-----------------+--------------------------+
4 rows in set ( 0. 00 sec )

# 按找从小到大排序
mysql> select * from student_score order by score;
+----------+-----------------------------+-------+
| number   | subject                     | score |
+----------+-----------------------------+-------+
| 20210104 | MySQL是怎样运行的           |    46 |
| 20210104 | 计算机是怎样运行的          |    55 |
| 20210103 | 计算机是怎样运行的          |    59 |
| 20210103 | MySQL是怎样运行的           |    61 |
| 20210101 | 计算机是怎样运行的          |    78 |
| 20210101 | MySQL是怎样运行的           |    88 |
| 20210102 | MySQL是怎样运行的           |    98 |
| 20210102 | 计算机是怎样运行的          |   100 |
+----------+-----------------------------+-------+
8 rows in set ( 0. 00 sec )
# 按照从大到小排序
mysql> select * from student_score order by score desc;
+----------+-----------------------------+-------+
| number   | subject                     | score |
+----------+-----------------------------+-------+
| 20210102 | 计算机是怎样运行的          |   100 |
| 20210102 | MySQL是怎样运行的           |    98 |
| 20210101 | MySQL是怎样运行的           |    88 |
| 20210101 | 计算机是怎样运行的          |    78 |
| 20210103 | MySQL是怎样运行的           |    61 |
| 20210103 | 计算机是怎样运行的          |    59 |
| 20210104 | 计算机是怎样运行的          |    55 |
| 20210104 | MySQL是怎样运行的           |    46 |
+----------+-----------------------------+-------+
8 rows in set ( 0. 00 sec )

# 按照多个列的值进行排序
mysql> select * from student_score order by subject, score DESC;
+----------+-----------------------------+-------+
| number   | subject                     | score |
+----------+-----------------------------+-------+
| 20210102 | MySQL是怎样运行的           |    98 |
| 20210101 | MySQL是怎样运行的           |    88 |
| 20210103 | MySQL是怎样运行的           |    61 |
| 20210104 | MySQL是怎样运行的           |    46 |
| 20210102 | 计算机是怎样运行的          |   100 |
| 20210101 | 计算机是怎样运行的          |    78 |
| 20210103 | 计算机是怎样运行的          |    59 |
| 20210104 | 计算机是怎样运行的          |    55 |
+----------+-----------------------------+-------+
8 rows in set ( 0. 01 sec )

# 限制显示条数
# 使用world数据库
mysql> use world;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set ( 0. 00 sec )

mysql> describe city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int      | NO   | PRI | NULL    | auto_increment |
| Name        | char( 35 ) | NO   |     |         |                |
| CountryCode | char( 3 )  | NO   | MUL |         |                |
| District    | char( 20 ) | NO   |     |         |                |
| Population  | int      | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+

mysql> select ID, Name from city limit 9;
+----+----------------+
| ID | Name           |
+----+----------------+
|  1 | Kabul          |
|  2 | Qandahar       |
|  3 | Herat          |
|  4 | Mazar-e-Sharif |
|  5 | Amsterdam      |
|  6 | Rotterdam      |
|  7 | Haag           |
|  8 | Utrecht        |
|  9 | Eindhoven      |
+----+----------------+
9 rows in set ( 0. 00 sec )

mysql> select ID, Name from city limit 9, 9;
+----+-----------+
| ID | Name      |
+----+-----------+
| 10 | Tilburg   |
| 11 | Groningen |
| 12 | Breda     |
| 13 | Apeldoorn |
| 14 | Nijmegen  |
| 15 | Enschede  |
| 16 | Haarlem   |
| 17 | Almere    |
| 18 | Arnhem    |
+----+-----------+

# 找出《计算机是怎样运行的》最高分
mysql> select * from student_score where subject = '计算机是怎样运行的' order by score DESC limit 1;
+----------+-----------------------------+-------+
| number   | subject                     | score |
+----------+-----------------------------+-------+
| 20210102 | 计算机是怎样运行的          |   100 |
+----------+-----------------------------+-------+
1 row in set ( 0. 00 sec )

# 大于
mysql> select number, name, id_number, major from student_info where number > 20210103;
+----------+-----------+--------------------+-----------------+
| number   | name      | id_number          | major           |
+----------+-----------+--------------------+-----------------+
| 20210104 | 亚索      | 141992200201078600 | 软件工程        |
| 20210105 | 莫甘娜    | 181048200008156368 | 飞行器设计      |
| 20210106 | 赵信      | 197995200201078445 | 电子信息        |
+----------+-----------+--------------------+-----------------+
3 rows in set ( 0. 00 sec )

# 不等于
mysql> select number, name, major from student_info where major != '计算机科学与工程';
+----------+-----------+-----------------+
| number   | name      | major           |
+----------+-----------+-----------------+
| 20210103 | 艾希      | 软件工程        |
| 20210104 | 亚索      | 软件工程        |
| 20210105 | 莫甘娜    | 飞行器设计      |
| 20210106 | 赵信      | 电子信息        |
+----------+-----------+-----------------+
4 rows in set ( 0. 00 sec )

# 查询两数之间的值
mysql> select number, name, department from student_info where number between 20210102 and 20210104;
+----------+--------+-----------------+
| number   | name   | department      |
+----------+--------+-----------------+
| 20210102 | 猫爷   | 计算机学院      |
| 20210103 | 艾希   | 计算机学院      |
| 20210104 | 亚索   | 计算机学院      |
+----------+--------+-----------------+
3 rows in set ( 0. 00 sec )

# 匹配指定元素
mysql> select number, name, major from student_info where major IN ( '软件工程', '飞行器设计' );
+----------+-----------+-----------------+
| number   | name      | major           |
+----------+-----------+-----------------+
| 20210103 | 艾希      | 软件工程        |
| 20210104 | 亚索      | 软件工程        |
| 20210105 | 莫甘娜    | 飞行器设计      |
+----------+-----------+-----------------+
3 rows in set ( 0. 00 sec )

# 多个搜索条件
# 统计课时及格数量 and运算符
mysql> select * from student_score where subject = 'MySQL是怎样运行的' and score > 60;
+----------+-------------------------+-------+
| number   | subject                 | score |
+----------+-------------------------+-------+
| 20210101 | MySQL是怎样运行的       |    88 |
| 20210102 | MySQL是怎样运行的       |    98 |
| 20210103 | MySQL是怎样运行的       |    61 |
+----------+-------------------------+-------+
3 rows in set ( 0. 00 sec )

# 或运算符
mysql> select * from student_score where score > 90 or score <60;
+----------+-----------------------------+-------+
| number   | subject                     | score |
+----------+-----------------------------+-------+
| 20210102 | MySQL是怎样运行的           |    98 |
| 20210102 | 计算机是怎样运行的          |   100 |
| 20210103 | 计算机是怎样运行的          |    59 |
| 20210104 | MySQL是怎样运行的           |    46 |
| 20210104 | 计算机是怎样运行的          |    55 |
+----------+-----------------------------+-------+
5 rows in set ( 0. 00 sec )

# 多个搜索条件时使用小括号来显示指定搜索顺序
mysql> select * from student_score where( score > 95 or score < 55 ) and subject = 'MySQL是怎样运行的';
+----------+-------------------------+-------+
| number   | subject                 | score |
+----------+-------------------------+-------+
| 20210102 | MySQL是怎样运行的       |    98 |
| 20210104 | MySQL是怎样运行的       |    46 |
+----------+-------------------------+-------+
2 rows in set ( 0. 00 sec )

# 通配符
mysql> select number, name, major from student_info where name like '狗%';
+----------+--------+--------------------------+
| number   | name   | major                    |
+----------+--------+--------------------------+
| 20210101 | 狗哥   | 计算机科学与工程         |
+----------+--------+--------------------------+
1 row in set ( 0. 00 sec )

mysql> select number, name, major from student_info where name like '%甘%';
+----------+-----------+-----------------+
| number   | name      | major           |
+----------+-----------+-----------------+
| 20210105 | 莫甘娜    | 飞行器设计      |
+----------+-----------+-----------------+
1 row in set ( 0. 00 sec )

mysql> select number, name, major from student_info where name like '狗_';
+----------+--------+--------------------------+
| number   | name   | major                    |
+----------+--------+--------------------------+
| 20210101 | 狗哥   | 计算机科学与工程         |
+----------+--------+--------------------------+
1 row in set ( 0. 00 sec )

# 使用字符串处理函数
mysql> select concat ( '学号为', number, '的学生在《', subject, '》课程的成绩是:', score, '分' ) AS 成绩.+-----------------------------------------------------------------------------------------+
| 成绩描述                                                                                |
+-----------------------------------------------------------------------------------------+
| 学号为20210101的学生在《MySQL是怎样运行的》课程的成绩是:88分                            |
| 学号为20210101的学生在《计算机是怎样运行的》课程的成绩是:78分                           |
| 学号为20210102的学生在《MySQL是怎样运行的》课程的成绩是:98分                            |
| 学号为20210102的学生在《计算机是怎样运行的》课程的成绩是:100分                          |
| 学号为20210103的学生在《MySQL是怎样运行的》课程的成绩是:61分                            |
| 学号为20210103的学生在《计算机是怎样运行的》课程的成绩是:59分                           |
| 学号为20210104的学生在《MySQL是怎样运行的》课程的成绩是:46分                            |
| 学号为20210104的学生在《计算机是怎样运行的》课程的成绩是:55分                           |
+-----------------------------------------------------------------------------------------+
8 rows in set ( 0. 00 sec )
# 当前时间函数
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-10-18 21:08:46 |
+---------------------+
1 row in set ( 0. 00 sec )

# CASE表达式
mysql> select number, subject, score, case when score < 60 then '不及格' when score < 90 then '及格' else '优秀' end AS level from student_score;
+----------+-----------------------------+-------+-----------+
| number   | subject                     | score | level     |
+----------+-----------------------------+-------+-----------+
| 20210101 | MySQL是怎样运行的           |    88 | 及格      |
| 20210101 | 计算机是怎样运行的          |    78 | 及格      |
| 20210102 | MySQL是怎样运行的           |    98 | 优秀      |
| 20210102 | 计算机是怎样运行的          |   100 | 优秀      |
| 20210103 | MySQL是怎样运行的           |    61 | 及格      |
| 20210103 | 计算机是怎样运行的          |    59 | 不及格    |
| 20210104 | MySQL是怎样运行的           |    46 | 不及格    |
| 20210104 | 计算机是怎样运行的          |    55 | 不及格    |
+----------+-----------------------------+-------+-----------+
8 rows in set ( 0. 00 sec )

# if表达式，当表达式1为真，则返回表达式2，否则返回表达式3
mysql> select if( 1>2, 3, 4 );
+---------------+
| if( 1>2, 3, 4 ) |
+---------------+
|             4 |
+---------------+
1 row in set ( 0. 00 sec )

mysql> select if( 2>1, 3, 4 );
+---------------+
| if( 2>1, 3, 4 ) |
+---------------+
|             3 |
+---------------+
1 row in set ( 0. 00 sec )
# 汇总函数
# 求最大值
mysql> select max( score ) from student_score where subject = 'MySQL是怎样运行的';
+------------+
| max( score ) |
+------------+
|         98 |
+------------+
1 row in set ( 0. 00 sec )
# 求最小值
mysql> select min( score ) from student_score where subject = 'MySQL是怎样运行的';
+------------+
| min( score ) |
+------------+
|         46 |
+------------+
1 row in set ( 0. 00 sec )
# 求平均值
mysql> select avg( score ) from student_score where subject = 'MySQL是怎样运行的';
+------------+
| avg( score ) |
+------------+
|    73. 2500 |
+------------+
1 row in set ( 0. 00 sec )
# 求总和
mysql> select sum( score ) from student_score where subject = 'MySQL是怎样运行的';
+------------+
| sum( score ) |
+------------+
|        293 |
+------------+
1 row in set ( 0. 00 sec )

# group by 分组查询
mysql> select subject, AVG( score ) from student_score GROUP by subject;
+-----------------------------+------------+
| subject                     | AVG( score ) |
+-----------------------------+------------+
| MySQL是怎样运行的           |    73. 2500 |
| 计算机是怎样运行的          |    73. 0000 |
+-----------------------------+------------+
2 rows in set ( 0. 00 sec )
# 带where子句的分组查询
mysql> select subject, avg( score ) from student_score where score >= 60 group by subject;
+-----------------------------+------------+
| subject                     | avg( score ) |
+-----------------------------+------------+
| MySQL是怎样运行的           |    82. 3333 |
| 计算机是怎样运行的          |    89. 0000 |
+-----------------------------+------------+
2 rows in set ( 0. 00 sec )

# 分组和排序
mysql> select subject, avg( score ) from student_score group by subject order by avg( score ) desc;
+-----------------------------+------------+
| subject                     | avg( score ) |
+-----------------------------+------------+
| MySQL是怎样运行的           |    73. 2500 |
| 计算机是怎样运行的          |    73. 0000 |
+-----------------------------+------------+
2 rows in set ( 0. 00 sec )
# 分组汇总
mysql> select department, major, count( * ) from student_info group by department, major;
+-----------------+--------------------------+----------+
| department      | major                    | count( * ) |
+-----------------+--------------------------+----------+
| 计算机学院      | 计算机科学与工程         |        2 |
| 计算机学院      | 软件工程                 |        2 |
| 航天学院        | 飞行器设计               |        1 |
| 航天学院        | 电子信息                 |        1 |
+-----------------+--------------------------+----------+
4 rows in set ( 0. 00 sec )

# 标量子查询
mysql> select * from student_score where number= ( select number from student_info where name = '狗哥' );
+----------+-----------------------------+-------+
| number   | subject                     | score |
+----------+-----------------------------+-------+
| 20210101 | MySQL是怎样运行的           |    88 |
| 20210101 | 计算机是怎样运行的          |    78 |
+----------+-----------------------------+-------+
2 rows in set ( 0. 00 sec )

# 列子查询
mysql> select * from student_score where number in ( select number from student_info where major='计算机科学与工程' );
+----------+-----------------------------+-------+
| number   | subject                     | score |
+----------+-----------------------------+-------+
| 20210101 | MySQL是怎样运行的           |    88 |
| 20210101 | 计算机是怎样运行的          |    78 |
| 20210102 | MySQL是怎样运行的           |    98 |
| 20210102 | 计算机是怎样运行的          |   100 |
+----------+-----------------------------+-------+
4 rows in set ( 0. 00 sec )

# 行子查询
mysql> select * from student_score where ( number, subject )=( select number, 'MySQL是怎样运行的' From student_info LIMIT 1 );
+----------+-------------------------+-------+
| number   | subject                 | score |
+----------+-------------------------+-------+
| 20210104 | MySQL是怎样运行的       |    46 |
+----------+-------------------------+-------+

# 内连接
mysql> select student_info.number, name, major, subject, score From student_info, student_score where student_info.number = student_score.number;
+----------+------+------------------+--------------------+-------+
| number   | name | major            | subject            | score |
+----------+------+------------------+--------------------+-------+
| 20210101 | 狗哥 | 计算机科学与工程 | MySQL是怎样运行的  | 88    |
| 20210101 | 狗哥 | 计算机科学与工程 | 计算机是怎样运行的 | 78    |
| 20210102 | 猫爷 | 计算机科学与工程 | MySQL是怎样运行的  | 98    |
| 20210102 | 猫爷 | 计算机科学与工程 | 计算机是怎样运行的 | 100   |
| 20210103 | 艾希 | 软件工程         | MySQL是怎样运行的  | 61    |
| 20210103 | 艾希 | 软件工程         | 计算机是怎样运行的 | 59    |
| 20210104 | 亚索 | 软件工程         | MySQL是怎样运行的  | 46    |
| 20210104 | 亚索 | 软件工程         | 计算机是怎样运行的 | 55    |
+----------+------+------------------+--------------------+-------+

# 外连接
mysql> select student_info.number, name, major, subject, score from student_info LEFT JOI
                     -> N student_score on student_info.number = student_score.number;
+----------+--------+------------------+--------------------+--------+
| number   | name   | major            | subject            | score  |
+----------+--------+------------------+--------------------+--------+
| 20210101 | 狗哥   | 计算机科学与工程 | MySQL是怎样运行的  | 88     |
| 20210101 | 狗哥   | 计算机科学与工程 | 计算机是怎样运行的 | 78     |
| 20210102 | 猫爷   | 计算机科学与工程 | MySQL是怎样运行的  | 98     |
| 20210102 | 猫爷   | 计算机科学与工程 | 计算机是怎样运行的 | 100    |
| 20210103 | 艾希   | 软件工程         | MySQL是怎样运行的  | 61     |
| 20210103 | 艾希   | 软件工程         | 计算机是怎样运行的 | 59     |
| 20210104 | 亚索   | 软件工程         | MySQL是怎样运行的  | 46     |
| 20210104 | 亚索   | 软件工程         | 计算机是怎样运行的 | 55     |
| 20210105 | 莫甘娜 | 飞行器设计       | <null>             | <null> |
| 20210106 | 赵信   | 电子信息         | <null>             | <null> |
+----------+--------+------------------+--------------------+--------+
# 表的别名
mysql> select s1. number, s1. name, s1. major, s2. subject, s2. score from student_info AS s1 i
                     -> nner join student_score AS s2 where s1. number=s2. number;
+----------+------+------------------+--------------------+-------+
| number   | name | major            | subject            | score |
+----------+------+------------------+--------------------+-------+
| 20210101 | 狗哥 | 计算机科学与工程 | MySQL是怎样运行的  | 88    |
| 20210101 | 狗哥 | 计算机科学与工程 | 计算机是怎样运行的 | 78    |
| 20210102 | 猫爷 | 计算机科学与工程 | MySQL是怎样运行的  | 98    |
| 20210102 | 猫爷 | 计算机科学与工程 | 计算机是怎样运行的 | 100   |
| 20210103 | 艾希 | 软件工程         | MySQL是怎样运行的  | 61    |
| 20210103 | 艾希 | 软件工程         | 计算机是怎样运行的 | 59    |
| 20210104 | 亚索 | 软件工程         | MySQL是怎样运行的  | 46    |
| 20210104 | 亚索 | 软件工程         | 计算机是怎样运行的 | 55    |
+----------+------+------------------+--------------------+-------+

Time: 0. 005s
# 自连接 查询和狗哥狗哥同专业的学生
mysql> select s2. number, s2. name, s2. major from student_info AS s1 inner join student_info AS s2 where s1. major = s2. major and s1. name= '狗哥';
+----------+------+------------------+
| number   | name | major            |
+----------+------+------------------+
| 20210101 | 狗哥 | 计算机科学与工程 |
| 20210102 | 猫爷 | 计算机科学与工程 |
+----------+------+------------------+

```
### 插入、删除和更新
```mysql
# 更新，将所有低于60分的修改为60分
mysql> update student_score set score = 60 where score < 60;
Query OK, 3 rows affected
Time: 0. 004s
MySQL root@( none ):sujx> select * from student_score;
+----------+--------------------+-------+
| number   | subject            | score |
+----------+--------------------+-------+
| 20210101 | MySQL是怎样运行的  | 88    |
| 20210101 | 计算机是怎样运行的 | 78    |
| 20210102 | MySQL是怎样运行的  | 98    |
| 20210102 | 计算机是怎样运行的 | 100   |
| 20210103 | MySQL是怎样运行的  | 61    |
| 20210103 | 计算机是怎样运行的 | 60    |
| 20210104 | MySQL是怎样运行的  | 60    |
| 20210104 | 计算机是怎样运行的 | 60    |
+----------+--------------------+-------+

# 插入
# 单挑记录插入
insert into student_score( number, subject, score ) values( 20210105, '计算机是怎样运行的', 90 );
# 批量插入
# 临时禁用外键检查
mysql> SET FOREIGN_KEY_CHECKS = 0;
Query OK, 0 rows affected
Time: 0. 000s
mysql> insert into student_score( number, subject, score ) values( 20210106, 'MySQL是怎样运行的', 84 ), ( 202101016, '计算机是怎样运行的', 79 ) AS new on duplicate key update score=new.score;
Query OK, 2 rows affected
Time: 0. 004s
MySQL root@( none ):sujx> select * from student_score;
+-----------+--------------------+-------+
| number    | subject            | score |
+-----------+--------------------+-------+
| 20210101  | MySQL是怎样运行的  | 88    |
| 20210101  | 计算机是怎样运行的 | 78    |
| 20210102  | MySQL是怎样运行的  | 98    |
| 20210102  | 计算机是怎样运行的 | 100   |
| 20210103  | MySQL是怎样运行的  | 61    |
| 20210103  | 计算机是怎样运行的 | 60    |
| 20210104  | MySQL是怎样运行的  | 60    |
| 20210104  | 计算机是怎样运行的 | 60    |
| 20210105  | MySQL是怎样运行的  | 83    |
| 20210105  | 计算机是怎样运行的 | 90    |
| 20210106  | MySQL是怎样运行的  | 84    |
| 202101016 | 计算机是怎样运行的 | 79    |
+-----------+--------------------+-------+
# 恢复外键检查
mysql> SET FOREIGN_KEY_CHECKS = 1;

# 删除错误项
mysql> delete from student_score where number = 202101016;
mysql> select * from student_score;
+----------+--------------------+-------+
| number   | subject            | score |
+----------+--------------------+-------+
| 20210101 | MySQL是怎样运行的  | 88    |
| 20210101 | 计算机是怎样运行的 | 78    |
| 20210102 | MySQL是怎样运行的  | 98    |
| 20210102 | 计算机是怎样运行的 | 100   |
| 20210103 | MySQL是怎样运行的  | 61    |
| 20210103 | 计算机是怎样运行的 | 60    |
| 20210104 | MySQL是怎样运行的  | 60    |
| 20210104 | 计算机是怎样运行的 | 60    |
| 20210105 | MySQL是怎样运行的  | 83    |
| 20210105 | 计算机是怎样运行的 | 90    |
| 20210106 | MySQL是怎样运行的  | 84    |
+----------+--------------------+-------+

# 创建视图, 视图其实是某个查询语句的别名
mysql> create view male_student_view as select s1. number, s1. name, s1. major, s2. subject, s2. score from student_info as s1 inner join student_score as s2 where s1. number = s2. number and s1. sex ='男';
Query OK, 0 rows affected
Time: 0. 008s
mysql> select * from male_student_view;
+----------+------+------------------+--------------------+-------+
| number   | name | major            | subject            | score |
+----------+------+------------------+--------------------+-------+
| 20210101 | 狗哥 | 计算机科学与工程 | MySQL是怎样运行的  | 88    |
| 20210101 | 狗哥 | 计算机科学与工程 | 计算机是怎样运行的 | 78    |
| 20210102 | 猫爷 | 计算机科学与工程 | MySQL是怎样运行的  | 98    |
| 20210102 | 猫爷 | 计算机科学与工程 | 计算机是怎样运行的 | 100   |
| 20210104 | 亚索 | 软件工程         | MySQL是怎样运行的  | 60    |
| 20210104 | 亚索 | 软件工程         | 计算机是怎样运行的 | 60    |
| 20210106 | 赵信 | 电子信息         | MySQL是怎样运行的  | 84    |
+----------+------+------------------+--------------------+-------+
# 根据已有视图创建新的视图
mysql> create view male_student_view3( 学号, 姓名, 课程, 成绩 ) as select number, name, subject, score from male_student_view;
mysql> select * from male_student_view3;
+----------+------+--------------------+------+
| 学号     | 姓名 | 课程               | 成绩 |
+----------+------+--------------------+------+
| 20210101 | 狗哥 | MySQL是怎样运行的  | 88   |
| 20210101 | 狗哥 | 计算机是怎样运行的 | 78   |
| 20210102 | 猫爷 | MySQL是怎样运行的  | 98   |
| 20210102 | 猫爷 | 计算机是怎样运行的 | 100  |
| 20210104 | 亚索 | MySQL是怎样运行的  | 60   |
| 20210104 | 亚索 | 计算机是怎样运行的 | 60   |
| 20210106 | 赵信 | MySQL是怎样运行的  | 84   |
+----------+------+--------------------+------+
# 查看视图
MySQL root@( none ):sujx> show tables;
+--------------------+
| Tables_in_sujx     |
+--------------------+
| first_table        |
| male_student_view  |
| male_student_view2 |
| male_student_view3 |
# 查看视图定义
mysql> show create view male_student_view\G
***************************[ 1. row ]***************************
View                 | male_student_view
Create View          | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `male_student_view` AS select `s1`.`number` AS `number`, `s1`.`name` AS `name`, `s1`.`major` AS `major`, `s2`.`subject` AS `subject`, `s2`.`score` AS `score` from ( `student_info` `s1` join `student_score` `s2` ) where ( (`s1`.`number` = `s2`.`number` ) and ( `s1`.`sex` = '男' ))
character_set_client | utf8mb4
collation_connection | utf8mb4_0900_ai_ci

1 row in set
Time: 0. 001s

# 删除视图
mysql> drop view male_student_view2;

# 设置存储函数
mysql> set global log_bin_trust_function_creators=TRUE$
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. 02 sec )
delimiter ;

# 调用存储函数
mysql> select avg_score( 'MySQL是怎样运行的' );
+--------------------------------------+
| avg_score( 'MySQL是怎样运行的' )       |
+--------------------------------------+
|                                   79 |
+--------------------------------------+
1 row in set ( 0. 00 sec )

# 查看函数定义
mysql> show create function avg_score\G
*************************** 1. row ***************************
            Function: avg_score
            sql_mode: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `avg_score`( s varchar( 100 )) RETURNS double
begin
  return ( select avg( score ) from student_score where subject = s );
END
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
# 删除函数
mysql> drop function avg_score;
Query OK, 0 rows affected ( 0. 01 sec )

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. 01 sec )

mysql> delimiter ;
mysql> call t1_operation( 4, 'd' );
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set ( 0. 00 sec )

+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
4 rows in set ( 0. 00 sec )

Query OK, 0 rows affected ( 0. 00 sec )

mysql> delimiter $
mysql> 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 $
Query OK, 0 rows affected ( 0. 01 sec )
mysql> delimiter ;

mysql> insert into t1( m1, n1 ) values( 5, 'e' ), ( 100, 'z' );
Query OK, 2 rows affected ( 0. 01 sec )
Records: 2  Duplicates: 0  Warnings: 0
# 插入值小于5时可以正常插入，大于10时，按10插入
mysql> select * from t1;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|   10 | z    |
+------+------+
6 rows in set ( 0. 00 sec )
# 查看触发器
mysql> show triggers\G
*************************** 1. row ***************************
             Trigger: bi_t1
               Event: INSERT
               Table: t1
           Statement: begin
    if new.m1 < 1 then
       set new.m1 = 1;
    elseif new.m1 >10 then
       set new.m1 = 10;
   END if;
end
              Timing: BEFORE
             Created: 2024-11-19 20:58:47. 20
            sql_mode: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set ( 0. 00 sec )
# 查看触发器的定义
mysql> show create trigger bi_t1\G
*************************** 1. row ***************************
               Trigger: bi_t1
              sql_mode: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` 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
  character_set_client: utf8mb4
  collation_connection: utf8mb4_0900_ai_ci
    Database Collation: utf8mb4_0900_ai_ci
               Created: 2024-11-19 20:58:47. 20
1 row in set ( 0. 00 sec )
# 删除触发器
mysql> drop trigger bi_t1;
Query OK, 0 rows affected ( 0. 00 sec )

# 设置一次性定时任务
mysql> delimiter $
mysql> create event insert_t1_event
    -> on schedule
    -> at '2024-11-02 21:07:30'
    -> do
    -> begin
    ->     insert into t1( m1, n1 ) values( 6, 'f' );
    -> end
    -> $
Query OK, 0 rows affected ( 0. 01 sec )
mysql> delimiter ;

# 设置周期性定时任务
mysql> delimiter $
mysql> create event insert_t1_event
    -> on schedule
    -> every 1 minute
    -> do
    -> begin
    ->     insert into t1( m1, n1 ) values( 7, 'g' );
    -> end$
mysql> delimiter ;

# 查看定时任务
mysql> show events\G
*************************** 1. row ***************************
                  Db: sujx
                Name: insert_t1_event
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 1
      Interval field: MINUTE
              Starts: 2024-11-02 21:10:05
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set ( 0. 00 sec )
# 查看定时任务的定义
mysql> show create event insert_t1_event\G
*************************** 1. row ***************************
               Event: insert_t1_event
            sql_mode: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION
           time_zone: SYSTEM
        Create Event: CREATE DEFINER=`root`@`localhost` EVENT `insert_t1_event` ON SCHEDULE EVERY 1 MINUTE STARTS '2024-11-02 21:10:05' ON COMPLETION NOT PRESERVE ENABLE DO begin
    insert into t1( m1, n1 ) values( 7, 'g' );
end
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set ( 0. 00 sec )
# 删除定时任务
mysql> drop event insert_t1_event;
Query OK, 0 rows affected ( 0. 00 sec )