MySQL使用基础

数据库是当前所有软件应用都离不开的基本数据存储库。例如，每当有人执行 Web 搜索、登录帐户或完成一项事务时，数据库系统就会存储相关信息以供未来访问。MySQL 是一个全球流行的开源数据库。MySQL 中的 “SQL” 指的是“结构化查询语言”，它是访问数据库时最常用的标准化语言。根据您的编程环境，您可以直接输入 SQL 来执行操作（例如生成报告），可以将 SQL 语句嵌入基于其他语言编写的代码中，或使用隐藏了 SQL 语法的特定语言 API。

“MySQL” 的官方读法是 “My ess-cue-el”，但也有很多人将其读作 “My sequel”。

MySQL 的标志是一个海豚，名字叫做 “Sakila”。这个名字是从“海豚命名”竞赛中用户提议的一堆名字中选出来的，是来自非洲斯威士兰的开源软件开发人员 Ambrose Twebaze 提出的。

初步

安装

# 使用包管理器安装
dnf install -y mysql-server
# 新建配置文件，指定服务端和客户端使用utf8mb4
cat >/etc/my.cnf.d/charset.cnf <<EOF
[mysqld]
character-set-server = utf8mb4

[client]
default-character-set = utf8mb4
EOF

# 拉起服务
systemctl enable --now mysqld
systemctl status --no-pager mysqld

# 查看软件版本号
[root@wulan ~]# mysql -V
mysql  Ver 8.0.40 for Linux on x86_64 (Source distribution)

# 程序放置
[root@wulan ~]# rpm -ql mysql-server
/etc/my.cnf.d/mysql-server.cnf
/run/mysqld
/usr/bin/ibd2sdi
/usr/bin/innochecksum
/usr/bin/my_print_defaults
/usr/bin/myisam_ftdump
/usr/bin/myisamchk
/usr/bin/myisamlog
/usr/bin/myisampack
/usr/bin/mysql_migrate_keyring
/usr/bin/mysql_secure_installation
/usr/bin/mysql_ssl_rsa_setup
/usr/bin/mysql_tzinfo_to_sql
/usr/bin/mysql_upgrade
/usr/bin/mysqld_pre_systemd
/usr/bin/mysqldumpslow
/usr/bin/perror
/usr/lib/systemd/system/mysqld.service
/usr/lib/systemd/system/mysqld@.service
/usr/lib/tmpfiles.d/mysqld.conf
/usr/lib64/mysql
/usr/lib64/mysql/INFO_BIN
/usr/lib64/mysql/INFO_SRC
/usr/lib64/mysql/plugin
/usr/lib64/mysql/plugin/adt_null.so
/usr/lib64/mysql/plugin/auth_socket.so
/usr/lib64/mysql/plugin/authentication_fido_client.so
/usr/lib64/mysql/plugin/authentication_kerberos_client.so
/usr/lib64/mysql/plugin/authentication_ldap_sasl_client.so
/usr/lib64/mysql/plugin/authentication_oci_client.so
/usr/lib64/mysql/plugin/component_audit_api_message_emit.so
/usr/lib64/mysql/plugin/component_keyring_file.so
/usr/lib64/mysql/plugin/component_log_filter_dragnet.so
/usr/lib64/mysql/plugin/component_log_sink_json.so
/usr/lib64/mysql/plugin/component_log_sink_syseventlog.so
/usr/lib64/mysql/plugin/component_mysqlbackup.so
/usr/lib64/mysql/plugin/component_query_attributes.so
/usr/lib64/mysql/plugin/component_reference_cache.so
/usr/lib64/mysql/plugin/component_validate_password.so
/usr/lib64/mysql/plugin/conflicting_variables.so
/usr/lib64/mysql/plugin/connection_control.so
/usr/lib64/mysql/plugin/daemon_example.ini
/usr/lib64/mysql/plugin/ddl_rewriter.so
/usr/lib64/mysql/plugin/group_replication.so
/usr/lib64/mysql/plugin/ha_example.so
/usr/lib64/mysql/plugin/ha_mock.so
/usr/lib64/mysql/plugin/innodb_engine.so
/usr/lib64/mysql/plugin/keyring_file.so
/usr/lib64/mysql/plugin/keyring_udf.so
/usr/lib64/mysql/plugin/libmemcached.so
/usr/lib64/mysql/plugin/libpluginmecab.so
/usr/lib64/mysql/plugin/locking_service.so
/usr/lib64/mysql/plugin/mypluglib.so
/usr/lib64/mysql/plugin/mysql_clone.so
/usr/lib64/mysql/plugin/mysql_no_login.so
/usr/lib64/mysql/plugin/rewrite_example.so
/usr/lib64/mysql/plugin/rewriter.so
/usr/lib64/mysql/plugin/semisync_master.so
/usr/lib64/mysql/plugin/semisync_replica.so
/usr/lib64/mysql/plugin/semisync_slave.so
/usr/lib64/mysql/plugin/semisync_source.so
/usr/lib64/mysql/plugin/validate_password.so
/usr/lib64/mysql/plugin/version_token.so
/usr/libexec/mysql-check-socket
/usr/libexec/mysql-prepare-db-dir
/usr/libexec/mysql-scripts-common
/usr/libexec/mysql-wait-stop
/usr/libexec/mysqld
/usr/sbin/mysqld
/usr/share/man/man1/ibd2sdi.1.gz
/usr/share/man/man1/innochecksum.1.gz
/usr/share/man/man1/lz4_decompress.1.gz
/usr/share/man/man1/my_print_defaults.1.gz
/usr/share/man/man1/myisam_ftdump.1.gz
/usr/share/man/man1/myisamchk.1.gz
/usr/share/man/man1/myisamlog.1.gz
/usr/share/man/man1/myisampack.1.gz
/usr/share/man/man1/mysql_secure_installation.1.gz
/usr/share/man/man1/mysql_ssl_rsa_setup.1.gz
/usr/share/man/man1/mysql_tzinfo_to_sql.1.gz
/usr/share/man/man1/mysql_upgrade.1.gz
/usr/share/man/man1/mysqldumpslow.1.gz
/usr/share/man/man1/mysqlman.1.gz
/usr/share/man/man1/perror.1.gz
/usr/share/man/man8/mysqld.8.gz
/usr/share/mysql/dictionary.txt
/usr/share/mysql/innodb_memcached_config.sql
/usr/share/mysql/install_rewriter.sql
/usr/share/mysql/uninstall_rewriter.sql
/var/lib/mysql
/var/lib/mysql-files
/var/lib/mysql-keyring
/var/log/mysql
/var/log/mysql/mysqld.log

配置

# 配置防火墙
[root@wulan ~]# firewall-cmd --add-service=mysql
success
[root@wulan ~]# firewall-cmd --runtime-to-permanent
success

# 安全配置
[root@wulan ~]# mysql_secure_installation 
Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

# 是否启用密码策略
Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file

# 选择使用低、中、高三种密码策略，低为小于8位，中为8位以上，使用数字、字母和特殊符号，高为加入字典文件
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
Please set the password for root here.

# 设置管理员密码
New password:

Re-enter new password:

# 确认密码信息
Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

# 移除匿名用户，即无密码用户
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

# 是否禁止管理员账号远程访问
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

# 删除测试数据库
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

# 重新加载数据库权限
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

# 查看数据库服务状态
[root@wulan ~]# mysqladmin version -uroot -p
Enter password: 
mysqladmin  Ver 8.0.40 for Linux on x86_64 (Source distribution)
Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version		8.0.40
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/var/lib/mysql/mysql.sock
Uptime:			1 hour 18 min 45 sec

Threads: 2  Questions: 23  Slow queries: 0  Opens: 157  Flush tables: 3  Open tables: 73  Queries per second avg: 0.0

# 查看已存在数据库
[root@wulan ~]# mysqlshow -uroot -p
Enter password: 
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

操作

# 登录数据库
[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> create user 'sujx'@'%' identified by 'qaz1wsx2@';
Query OK, 0 rows affected
Time: 0.009s

# 修改密码
mysql> set password for 'sujx'@'%' = 'qwe123!!';
Query OK, 0 rows affected
Time: 0.008s

# 查询用户权限
mysql> show grants for 'sujx'@'%' ;
+----------------------------------+
| Grants for sujx@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO `sujx`@`%` |
+----------------------------------+
1 row in set (0.00 sec)

# 将world数据库权限赋予账号
mysql> grant all on world.* to 'sujx'@'%';
Query OK, 0 rows affected (0.00 sec)

# 再次查看就可以看到账号有了world库的全部权限
mysql> show grants for 'sujx'@'%' ;
+-------------------------------------------------+
| Grants for sujx@%                               |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `sujx`@`%`                |
| GRANT ALL PRIVILEGES ON `world`.* TO `sujx`@`%` |
+-------------------------------------------------+
2 rows in set (0.00 sec)

# 删除用户
mysql> drop user '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)