MySQL数据库基础概述

前言：

MySQL作为全球应用最广泛的开源关系型数据库管理系统（RDBMS），凭借其高性能、高可靠性与零成本特性，已成为Web应用、企业系统的核心数据引擎。它遵循SQL标准，通过表结构实现数据的结构化存储，并借助多存储引擎架构（如事务型InnoDB、高性能MyISAM）灵活适配不同业务场景。从Linux到Windows的跨平台兼容性，从轻量级应用到千万级数据的高并发处理能力，MySQL为开发者提供了兼顾效率与稳定的数据管理基石，支撑着全球超80%的动态网站数据存储。

一、概述

1.数据库概念

2.数据库的类型

3.关系型数据库模型

4.关系数据库相关概念

二、安装

1.mariadb安装

2.mysql安装

3.启动并开机自启并初始化部署

4.本地连接（本地登录）

5.软件连接

三、mysqld数据库配置与命令

1.yum安装后生成的目录

2.mysqld服务器的启动脚本

3.数据存储目录的权限

4.服务启动时读取的默认配置文件

5.mysqld操作命令

6.mysql命令

7.mysqladmin命令

四、默认数据库

五、 SQL语言

六、mysql数据类型

1.整型

2.浮点型

3.BIT类型

4.定点型

5.日期时间类型

（1）YEAR类型

（2）DATE类型

（3）TIME类型

（4）DATETIME类型

（5）TIMESTAMP类型

6.字符串型

（1）CHAR和VARCHAR类型

（2）TEXT类型

7.ENUM类型

8.SET类型

9.BINARY和VARBINARY类型

10.BLOB类型

11.JSON数据类型

12.特殊的NULL类型

七、数据库操作命令

1.库操作命令

2.表结构操作命令

3.表数据操作

where字句运算符

更新数据

删除数据

查询数据

实战

ER图

完整建表SQL（含字符集设置）

完整数据插入脚本

数据验证查询

设计说明

查询验证结果说明

八、mysqld用户权限管理

MySQL数据库权限分类

创建远程管理用户

创建远程web服务数据库的操作用户

查看用户权限

删除用户权限

案例

mysqld用户权限关键命令总结

九、mysqld数据库备份

9.1 数据备份的重要性

9.2 造成数据丢失的原因

9.2 备份需要考虑的问题

9.4 备份类型

1、根据是否需要数据库离线

1.1、常用备份工具

2、根据要备份的数据集合的范围

建议的恢复策略

3、根据备份数据或文件

9.5 常见的备份方法

1、物理冷备（完全备份）

2、专用备份工具mysqldump或mysqlhotcopy （完全备份，逻辑备份）

3、第三方工具备份

9.6 备份案例

1、完整备份

所有数据库的备份

指定数据库的备份

指定数据表的备份

2、增量备份

配置过程

查看日志文件内容

二进制备份的恢复

模拟数据丢失流程

数据恢复

XtraBackup 8.0.35-33 使用指南

简介

安装

在基于 RPM 的系统上安装

在基于 Debian 的系统上安装

基本使用

1. 完整备份

2. 准备备份（应用日志）

3. 恢复备份

高级使用案例

案例1：增量备份

案例2：压缩备份

案例3：并行备份和恢复

案例4：加密备份

案例5：流式备份

常用选项说明

注意事项

最佳实践

MyDumper 详细使用指南

安装 MyDumper

Ubuntu/Debian 系统

CentOS/RHEL 系统

从源码编译

基本使用

1. 完整备份数据库

2. 恢复数据库

常用参数说明

使用案例

案例1：备份单个数据库

案例2：备份多个特定表

案例3：多线程备份（8个线程）

案例4：压缩备份

案例5：按100万行分割表数据

案例6：备份数据库结构（不备份数据）

案例7：恢复数据库到不同名称

案例8：只恢复特定表

高级功能

1. 一致性快照备份

2. 正则表达式过滤表

3. 备份时排除某些表

4. 长查询超时设置

5. 只备份数据不备份结构

实际应用场景

场景1：生产环境每日备份

场景2：大数据表部分恢复

场景3：跨服务器迁移数据库

注意事项

性能优化建议

mysqlhotcopy 使用指南

安装与准备

检查是否安装

确保依赖安装（Perl 模块）

基本语法

常用选项

使用案例

案例1：备份单个数据库

案例2：备份多个数据库

案例3：使用正则表达式备份匹配的数据库

案例4：保留旧备份

案例5：远程备份到其他服务器

案例6：不备份索引文件

案例7：备份后刷新日志

案例8：模拟运行（不实际备份）

实际应用场景

场景1：生产环境每日备份脚本

场景2：备份特定表

场景3：增量备份策略

恢复数据库

注意事项

性能优化建议

替代方案

附录 A mysqld配置文件

附录 B mysql常见内置函数

一、字符串函数

二、日期时间函数

三、数学函数

四、逻辑函数

五、加密函数

总结

一、概述

1.数据库概念

数据库(Database)

简称DB，按照一定格式存储数据的一些文件的组合，顾名思义就是存储数据的仓库，实际上就是一堆文件。这些文件中存储了具有特定格式的数据。数据库管理数据有诸多优点，如降低存储数据的冗余度，存储的数据可以共享，便于维护数据完整性，能够实现数据的安全性等。数据库的发展经历了萌芽、初级、中级、和高级阶段。

数据库管理系统（DateBase Management System）

简称DBMS，数据库管理系统是专门用来管理数据库中的数据的，可以对数据库当中的数据进行增、删、改、查等操作，常见的数据库管理系统：MySql、Oracle、MS SQLServer、DB2、sysbase、pstgreSQL等…

SQL（Structured Query Language）

结构化查询语言，针对关系型数据库的一种语言；SQL 是一种操作数据库的语言，包括创建数据库、删除数据库、查询记录、修改记录、添加字段等。SQL在MySql中可以使用，同时在Oracle中也可以使用，在DB2中也可以使用。

2.数据库的类型

关系型数据库

MariaDB/MySQL

Oracle

DB2

MS SQLServer，只能用于windows系统

国产数据库

达梦

人大金仓

TiDB，分布式关系型数据库

NOSQL数据库

Redis，缓存数据库

MongoDB，文档型数据库

3.关系型数据库模型

层次模型

网状模型

关系模型

关系模型的相关概念：

实体（Entity）：实体是指现实世界中可以区分的对象，它可以是具体的人、事物或概念，也可以是抽象的物体。在数据库中，实体通常对应于表中的行，而表则实体的一种集合。例如，在一个学生信息管理系统中，学生、课程和教师都可以被视为实体。

属性（Attribute）：属性是实体的特征或性质，它提供了关于实体的具体信息。属性可以是简单的数据类型，如姓名、年龄或地址，也可以是更复杂的结构，如电话号码或电子邮件地址。在数据库中，属性通常对应于表中的列，每列代表实体的一种属性。例如，学生的姓名、学号和年龄都是学生的属性。

联系（Relationship）：联系描述了不同实体之间的关系。在现实世界中，实体之间往往存在各种形式的关联。在数据库中，联系通过关系表来实现，这个表通常包含两个或更多的实体作为表的列。例如，学生选修课程的关系表就包含了学生实体和课程实体的相关信息，描述了哪位学生选修了哪门课程。

在关系模型中，实体和联系通常通过以下三种类型来进一步分类：

一对一（1:1）：这种联系表示两个实体之间存在一对一的关系，即每个实体只有一个相关的实体，反之亦然。例如，一个部门与一个负责人之间可能存在一对一的联系。

一对多（1:N）：这种联系表示一个实体与多个实体相关，但每个相关实体只与一个实体相关。例如，一个教师可以教授多门课程，但每门课程只能由一个教师教授。

多对多（M:N）：这种联系表示两个实体之间存在多对多的关系，即每个实体可以与多个实体相关，同时每个相关实体也可以与多个实体相关。例如，学生可以选修多门课程，同时每门课程也可以被多个选修。

在设计数据库时，正确地识别实体、属性和联系，以及它们之间的类型，对于创建一个结构良好、能够有效存储和检索数据的数据库至关重要。

4.关系数据库相关概念

库（Database）：库是一个存储数据的容器，它可以包含多个数据库。在某些管理系统中，库等同于数据库。

表（Table）：表是数据库中的一个表格，由行和列组成。表是存储数据的主要结构，每个表通常对应一个实体类型。表的列名称为属性，而表的行名称为记录。

行（Record）：行也称为记录，它是表中的一个单元，代表表中的一个具体实例。每一行包含了一组属性值，这些值共同描述了一个实体的状态。

列（Column）：列是表中的一列，它代表了表的一个属性。每列都有一个数据类型，用于定义存储在其中的数据的种类和格式。

字段（Field）：字段通常指的是表中的行与列的交叉点，它存储了单个数据项。在数据库中，每个字段都有其特定的数据类型和用途。

数据（Data）：数据是存储在数据库中的信息。它可以是文本、数字、日期、图像、声音等各种形式。数据是数据库管理和操作的核心。

二、安装

默认监听端口号：3306/tcp

1.mariadb安装

服务端程序： mariadb-server；客户端程序：mariadb

yum install -y mariadb-server mariadb

2.mysql安装

服务端程序：mysql-server；客户端程序：mysql；

[root@mysql ~]# yum install -y mysqld-server mysql

##编译安装，需要cmake环境

####或者

# 下载 MySQL 8.0 官方仓库配置

sudo wget https://dev.mysql.com/get/mysql80-community-release-el8-4.noarch.rpm

# 安装仓库并导入 GPG 密钥

sudo rpm -ivh mysql80-community-release-el8-4.noarch.rpm

sudo rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql

# 安装 MySQL Server 8.0

sudo yum install -y mysql-server

# 启动服务并设置开机自启

sudo systemctl start mysqld

sudo systemctl enable mysqld

3.启动并开机自启并初始化部署

[root@mysql ~]# systemctl enable --now mysqld.service

[root@mysql ~]# systemctl disable --now firewalld

[root@mysql ~]# setenforce 0

4.本地连接（本地登录）

[root@mysql ~]# mysql

Welcome to the mysqld monitor. Commands end with ; or \g.

Your mysqld connection id is 2

Server version: 5.5.68-mysqld mysqld Server

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]>

##[(none)]：表示当前登录用户选择的数据库的“空”，没有在任何库中

##使用“exit”命令可以退出数据库登录

注意：mysqld默认的管理用户是“root”，与操作系统的“root”没有任何关系！！！root用户默认没有密码！！

5.软件连接

192.168.72.155主机创建用户赋予权限

mysql> create user zhangsan@192.168.72.1 identified by '123.com';

Query OK, 0 rows affected (0.02 sec)

mysql> grant all on *.* to zhangsan@'192.168.72.1';

Query OK, 0 rows affected (0.00 sec)

到Visual Studio Code软件连接库

连接成功

Visual Studio Code软件新创建表

表创建完成

三、mysqld数据库配置与命令

1.yum安装后生成的目录

[root@mysql mysql]# rpm -ql mysqld-server

/etc/logrotate.d/mysqld

/etc/my.cnf.d/server.cnf

/usr/bin/innochecksum

/usr/bin/myisam_ftdump

/usr/bin/myisamchk

/usr/bin/myisamlog

/usr/bin/myisampack

/usr/bin/mysql_convert_table_format

/usr/bin/mysql_fix_extensions

/usr/bin/mysql_install_db

/usr/bin/mysql_plugin

/usr/bin/mysql_secure_installation

/usr/bin/mysql_setpermission

/usr/bin/mysql_tzinfo_to_sql

/usr/bin/mysql_upgrade

/usr/bin/mysql_zap

/usr/bin/mysqlbug

/usr/bin/mysqld_multi

/usr/bin/mysqld_safe

/usr/bin/mysqld_safe_helper

/usr/bin/mysqldumpslow

/usr/bin/mysqlhotcopy

/usr/bin/mysqltest

/usr/bin/perror

/usr/bin/replace

/usr/bin/resolve_stack_dump

/usr/bin/resolveip

/usr/lib/systemd/system/mysqld.service

/usr/lib/tmpfiles.d/mysqld.conf

/usr/lib64/mysql/INFO_BIN

/usr/lib64/mysql/INFO_SRC

/usr/lib64/mysql/mysqlbug

/usr/lib64/mysql/plugin

/usr/lib64/mysql/plugin/adt_null.so

/usr/lib64/mysql/plugin/auth_0x0100.so

/usr/lib64/mysql/plugin/auth_pam.so

/usr/lib64/mysql/plugin/auth_socket.so

/usr/lib64/mysql/plugin/auth_test_plugin.so

/usr/lib64/mysql/plugin/daemon_example.ini

/usr/lib64/mysql/plugin/dialog_examples.so

/usr/lib64/mysql/plugin/ha_innodb.so

/usr/lib64/mysql/plugin/ha_sphinx.so

/usr/lib64/mysql/plugin/handlersocket.so

/usr/lib64/mysql/plugin/libdaemon_example.so

/usr/lib64/mysql/plugin/mypluglib.so

/usr/lib64/mysql/plugin/qa_auth_client.so

/usr/lib64/mysql/plugin/qa_auth_interface.so

/usr/lib64/mysql/plugin/qa_auth_server.so

/usr/lib64/mysql/plugin/query_cache_info.so

/usr/lib64/mysql/plugin/semisync_master.so

/usr/lib64/mysql/plugin/semisync_slave.so

/usr/lib64/mysql/plugin/server_audit.so

/usr/lib64/mysql/plugin/sphinx.so

/usr/lib64/mysql/plugin/sql_errlog.so

/usr/libexec/mysqld-prepare-db-dir

/usr/libexec/mysqld-wait-ready

/usr/libexec/mysqld

/usr/share/man/man1/innochecksum.1.gz

/usr/share/man/man1/msql2mysql.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.server.1.gz

/usr/share/man/man1/mysql_convert_table_format.1.gz

/usr/share/man/man1/mysql_fix_extensions.1.gz

/usr/share/man/man1/mysql_install_db.1.gz

/usr/share/man/man1/mysql_plugin.1.gz

/usr/share/man/man1/mysql_secure_installation.1.gz

/usr/share/man/man1/mysql_setpermission.1.gz

/usr/share/man/man1/mysql_tzinfo_to_sql.1.gz

/usr/share/man/man1/mysql_upgrade.1.gz

/usr/share/man/man1/mysql_zap.1.gz

/usr/share/man/man1/mysqlbinlog.1.gz

/usr/share/man/man1/mysqlbug.1.gz

/usr/share/man/man1/mysqlcheck.1.gz

/usr/share/man/man1/mysqld_multi.1.gz

/usr/share/man/man1/mysqld_safe.1.gz

/usr/share/man/man1/mysqldumpslow.1.gz

/usr/share/man/man1/mysqlhotcopy.1.gz

/usr/share/man/man1/mysqlimport.1.gz

/usr/share/man/man1/mysqltest.1.gz

/usr/share/man/man1/perror.1.gz

/usr/share/man/man1/replace.1.gz

/usr/share/man/man1/resolve_stack_dump.1.gz

/usr/share/man/man1/resolveip.1.gz

/usr/share/man/man8/mysqld.8.gz

/usr/share/mysql/README.mysql-cnf

/usr/share/mysql/errmsg-utf8.txt

/usr/share/mysql/fill_help_tables.sql

/usr/share/mysql/my-huge.cnf

/usr/share/mysql/my-innodb-heavy-4G.cnf

/usr/share/mysql/my-large.cnf

/usr/share/mysql/my-medium.cnf

/usr/share/mysql/my-small.cnf

/usr/share/mysql/mysql_performance_tables.sql

/usr/share/mysql/mysql_system_tables.sql

/usr/share/mysql/mysql_system_tables_data.sql

/usr/share/mysql/mysql_test_data_timezone.sql

/var/lib/mysql ##mysqld数据库的数据存储目录

/var/log/mysqld

/var/log/mysqld/mysqld.log

/var/run/mysqld

2.mysqld服务器的启动脚本

[root@mysql ~]# cat /usr/lib/systemd/system/mysqld.service

[Unit]

Description=mysqld database server

After=syslog.target

After=network.target

[Service]

Type=simple

User=mysql ##程序运行用户

Group=mysql

ExecStartPre=/usr/libexec/mysqld-prepare-db-dir %n

# Note: we set --basedir to prevent probes that might trigger SELinux alarms,

# per bug #547485

ExecStart=/usr/bin/mysqld_safe --basedir=/usr

ExecStartPost=/usr/libexec/mysqld-wait-ready $MAINPID

"/usr/lib/systemd/system/mysqld.service" 48L, 1697C 39,1 55%

# http://fedoraproject.org/wiki/Systemd#How_do_I_customize_a_unit_file.2F_add_a_custom_unit_file.3F

# For example, if you want to increase mysqld's open-files-limit to 10000,

# you need to increase systemd's LimitNOFILE setting, so create a file named

# "/etc/systemd/system/mysqld.service.d/limits.conf" containing:

# [Service]

# LimitNOFILE=10000

# Note: /usr/lib/... is recommended in the .include line though /lib/...

# still works.

# Don't forget to reload systemd daemon after you change unit configuration:

# root> systemctl --system daemon-reload

[Unit]

Description=mysqld database server

After=syslog.target

After=network.target

[Service]

Type=simple

User=mysql

Group=mysql

ExecStartPre=/usr/libexec/mysqld-prepare-db-dir %n

# Note: we set --basedir to prevent probes that might trigger SELinux alarms,

# per bug #547485

ExecStart=/usr/bin/mysqld_safe --basedir=/usr

ExecStartPost=/usr/libexec/mysqld-wait-ready $MAINPID

# Give a reasonable amount of time for the server to start up/shut down

TimeoutSec=300

# Place temp files in a secure directory, not /tmp

PrivateTmp=true

[Install]

WantedBy=multi-user.target

3.数据存储目录的权限

[root@mysql mysql]# ls -ld /var/lib/mysql/

drwxr-xr-x 5 mysql mysql 177 1月 31 10:16 /var/lib/mysql/

4.服务启动时读取的默认配置文件

[root@mysql mysql]# cat /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql ##定义数据存储目录

socket=/var/lib/mysql/mysql.sock ##定义连接的网络接口文件

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

# Settings user and group are ignored when systemd is used.

# If you need to run mysqld under a different user or group,

# customize your systemd unit file for mysqld according to the

# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]

log-error=/var/log/mysqld/mysqld.log ##程序运行的错误日志

pid-file=/var/run/mysqld/mysqld.pid ##程序运行的PID文件

# include all files from the config directory

!includedir /etc/my.cnf.d

5.mysqld操作命令

[root@mysql ~]# ls -l /usr/bin/mysql*

-rwxr-xr-x 1 root root 3543584 10月 2 2020 /usr/bin/mysql

-rwxr-xr-x 1 root root 111971 10月 2 2020 /usr/bin/mysqlaccess

-rwxr-xr-x 1 root root 3096720 10月 2 2020 /usr/bin/mysqladmin

-rwxr-xr-x 1 root root 3259880 10月 2 2020 /usr/bin/mysqlbinlog

lrwxrwxrwx 1 root root 26 1月 31 10:15 /usr/bin/mysqlbug -> /etc/alternatives/mysqlbug

-rwxr-xr-x 1 root root 3094016 10月 2 2020 /usr/bin/mysqlcheck

-rwxr-xr-x 1 root root 4215 10月 2 2020 /usr/bin/mysql_convert_table_format

-rwxr-xr-x 1 root root 24116 10月 2 2020 /usr/bin/mysqld_multi

-rwxr-xr-x 1 root root 27105 10月 2 2020 /usr/bin/mysqld_safe

-rwxr-xr-x 1 root root 2887144 10月 2 2020 /usr/bin/mysqld_safe_helper

-rwxr-xr-x 1 root root 3176704 10月 2 2020 /usr/bin/mysqldump

-rwxr-xr-x 1 root root 7876 10月 2 2020 /usr/bin/mysqldumpslow

-rwxr-xr-x 1 root root 3288 10月 2 2020 /usr/bin/mysql_find_rows

-rwxr-xr-x 1 root root 1246 10月 2 2020 /usr/bin/mysql_fix_extensions

-rwxr-xr-x 1 root root 34942 10月 2 2020 /usr/bin/mysqlhotcopy

-rwxr-xr-x 1 root root 3088968 10月 2 2020 /usr/bin/mysqlimport

-rwxr-xr-x 1 root root 16701 10月 2 2020 /usr/bin/mysql_install_db

-rwxr-xr-x 1 root root 2926112 10月 2 2020 /usr/bin/mysql_plugin

-rwxr-xr-x 1 root root 12126 10月 2 2020 /usr/bin/mysql_secure_installation

-rwxr-xr-x 1 root root 17464 10月 2 2020 /usr/bin/mysql_setpermission

-rwxr-xr-x 1 root root 3087504 10月 2 2020 /usr/bin/mysqlshow

-rwxr-xr-x 1 root root 3106896 10月 2 2020 /usr/bin/mysqlslap

-rwxr-xr-x 1 root root 3449016 10月 2 2020 /usr/bin/mysqltest

-rwxr-xr-x 1 root root 2921448 10月 2 2020 /usr/bin/mysql_tzinfo_to_sql

-rwxr-xr-x 1 root root 2998448 10月 2 2020 /usr/bin/mysql_upgrade

-rwxr-xr-x 1 root root 2912968 10月 2 2020 /usr/bin/mysql_waitpid

-rwxr-xr-x 1 root root 3856 10月 2 2020 /usr/bin/mysql_zap

6.mysql命令

mysql数据库登录的命令行工具，本地登录时且root用户没有密码，输入mysql，等效于”mysql -uroot -hlocalhost -P3306“

语法

mysql [options] db_name

常用选项

-u：指定登录用户

-p：指定用户密码

-h：指定登录数据库的IP或者域名

-P：指定登录数据库的端口号

-e：能够在终端执行数据库指令

使用案例

[root@mysql ~]# mysql -uroot -hlocalhost -P3306

###或者

[root@mysql ~]# mysql -h127.0.0.1 -P3306 -uroot

###或者使用mysql客户端连接到本地的MySQL服务器（127.0.0.1:3306），用户名为root，并指定了数据库名为sys

[root@mysql ~]# mysql -h127.0.0.1 -P3306 -uroot sys

##直接在外部执行查看数据库的命令

[root@mysql ~]# mysql -p123.com -e "show databases"

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

+--------------------+

##输入密码登录

[root@mysql ~]# mysql -uroot -p

Enter password: 123.com

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 15

Server version: 8.0.41 Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

##指定登录的数据库，只能是一个

[root@mysql mysql]# mysql -p123.com mysql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Welcome to the mysqld monitor. Commands end with ; or \g.

Your mysqld connection id is 14

Server version: 5.5.68-mysqld mysqld Server

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysqld [mysql]>

7.mysqladmin命令

超级管理命令

语法

mysqladmin [options] command [command-arg] [command [command-arg]] ...

设置root用户密码

[root@mysql ~]# mysqladmin -uroot password '123.com'

使用密码登录

[root@mysql ~]# mysql -uroot -p123.com -h127.0.0.1 -P3306

创建其他用户登录（允许来自于某个主机登录）

mysql> create user 'zhangsan'@'192.168.72.9' identified by '123.com';

Query OK, 0 rows affected (0.01 sec)

查看zhangsan用户权限（USAGE可以连接的权限）

mysql> show grants for zhangsan@192.168.72.9;

+-------------------------------------------------+

| Grants for zhangsan@192.168.72.9 |

+-------------------------------------------------+

| GRANT USAGE ON *.* TO `zhangsan`@`192.168.72.9` |

+-------------------------------------------------+

1 row in set (0.01 sec)

切换到192.168.72.9远程账户

###关闭防火墙，安全上下文

[root@ding ~]# systemctl disable --now firewalld.service

[root@ding ~]# setenforce 0

###Rocky9 安装MySQL客户端

[root@ding ~]# yum install -y mysql

###远程账户192.168.72.9连接192.168.72.155主机

[root@ding ~]# mysql -h192.168.72.155 -uzhangsan -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 21

Server version: 8.0.41 Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

四、默认数据库

information_schema ：信息数据库，存储所有的库、表、列的名称，任意可登录数据库的用户都可读；sql注入

表名作用CHARACTER_SETS字符集信息表。用于查看和管理MySQL数据库中的字符集信息。CLIENT_STATISTICS客户端统计信息表。用于查看和管理客户端的统计信息，例如连接数、请求次数等。COLLATIONS排序规则信息表。用于查看和管理MySQL数据库中的排序规则信息。COLLATION_CHARACTER_SET_APPLICABILITY字符集和排序规则的适用性表。用于查看和管理字符集和排序规则之间的适用关系。COLUMNS数据表的列信息表。用于查看和管理MySQL数据库中数据表的列信息。COLUMN_PRIVILEGES列级别的权限信息表。用于查看和管理列级别的权限信息，例如用户对各个列的SELECT、INSERT、UPDATE等权限。ENGINES存储引擎信息表。用于查看和管理MySQL数据库中支持的存储引擎信息。EVENTS定时事件信息表。用于查看和管理MySQL数据库中的定时事件信息。FILES文件信息表。用于查看和管理MySQL数据库服务器上文件的信息。GLOBAL_STATUS全局状态信息表。用于查看和管理MySQL数据库服务器的全局状态信息。GLOBAL_VARIABLES全局变量信息表。用于查看和管理MySQL数据库服务器的全局变量信息。INDEX_STATISTICS索引统计信息表。用于查看和管理MySQL数据库中索引的统计信息。KEY_CACHES键缓存表。用于查看和管理MySQL数据库中的键缓存信息。KEY_COLUMN_USAGE键列使用表。用于查看和管理键列的使用情况。PARAMETERS参数表。用于查看和管理MySQL数据库的参数信息，例如连接超时时间、最大连接数等。PARTITIONS分区表。用于查看和管理MySQL数据库中的分区信息。PLUGINS插件表。用于查看和管理MySQL数据库中的插件信息。PROCESSLIST进程列表表。用于查看当前正在运行的MySQL进程，包括连接的客户端和正在执行的查询等。PROFILING性能分析表。用于启用性能分析，记录和分析SQL查询的性能数据。REFERENTIAL_CONSTRAINTS外键约束表。用于查看和管理外键约束的信息，包括外键列和参考列等。ROUTINES存储过程和函数表。用于查看和管理MySQL数据库中的存储过程和函数的信息，包括创建时间、修改时间、函数名等。SCHEMATA架构表。用于查看和管理MySQL数据库中的架构信息，包括架构名、架构下的表名等。SCHEMA_PRIVILEGES架构权限表。用于查看和管理架构下的权限信息，包括用户对架构下表的SELECT、INSERT、UPDATE等权限。SESSION_STATUS会话状态信息表。用于查看和管理当前会话的状态信息，例如会话的连接时间、查询时间等。SESSION_VARIABLES会话变量表。用于查看和管理当前会话的变量信息，例如会话的最大连接数、最大内存使用量等。STATISTICS统计信息表。用于查看和管理MySQL数据库中表的统计信息，包括表的行数、平均行大小等。TABLES数据表信息表。用于查看和管理MySQL数据库中的数据表信息，包括表名、引擎类型等。TABLESPACES存储空间表。用于查看和管理MySQL数据库中的存储空间信息，包括存储空间名、大小等。TABLE_CONSTRAINTS表约束信息表。用于查看和管理表的约束信息，包括主键约束、外键约束等。TABLE_PRIVILEGES表级别的权限信息表。用于查看和管理表级别的权限信息，例如用户对各个表的SELECT、INSERT、UPDATE等权限。INNODB_CMPMEM_RESETInnoDB 内存比较器重置表。此表用于记录 InnoDB 内存比较器（comparison memory）的清除操作。INNODB_RSEGInnoDB 重做段表此表用于记录 InnoDB 数据文件的重做段信息。INNODB_UNDO_LOGSInnoDB 撤销日志表。此表用于记录 InnoDB 撤销操作的日志信息。INNODB_CMPMEMInnoDB 内存比较器表。此表用于记录 InnoDB 内存比较器的分配和使用情况。INNODB_SYS_TABLESTATSInnoDB 系统表统计信息表。此表用于记录 InnoDB 系统表的统计信息，如数据量、碎片率等。INNODB_LOCK_WAITSInnoDB 锁等待信息表。此表记录 InnoDB 锁等待的情况，包括等待锁的线程 ID、等待时间等。INNODB_INDEX_STATSInnoDB 索引统计信息表。此表用于记录 InnoDB 索引的统计信息，如索引大小、索引列的数据类型等。INNODB_CMPInnoDB 比较器表。此表用于记录 InnoDB 比较器的分配和使用情况。INNODB_CHANGED_PAGESInnoDB 更改页表。此表用于记录 InnoDB 数据文件中已更改的页的信息。INNODB_BUFFER_POOL_PAGESInnoDB 缓冲池页表。此表用于记录 InnoDB 缓冲池中每个页的信息，包括页的类型、页的状态等。INNODB_TRXInnoDB 事务表。此表用于记录 InnoDB 事务的信息，如事务 ID、事务状态等。INNODB_BUFFER_POOL_PAGES_INDEXInnoDB 缓冲池页索引表。此表用于记录 InnoDB 缓冲池中每个页的索引信息。INNODB_LOCKSInnoDB 锁表。此表用于记录 InnoDB 锁的信息，如锁的类型、锁的持有者等。INNODB_BUFFER_PAGE_LRUInnoDB 缓冲池页 LRU 表。此表用于记录 InnoDB 缓冲池中每个页的最近最少使用（Least Recently Used，LRU）信息。INNODB_SYS_TABLESInnoDB 系统表信息表。此表用于记录 InnoDB 系统表的信息，如表名、表状态等。INNODB_SYS_FIELDSInnoDB 系统字段信息表。此表用于记录 InnoDB 系统表中每个字段的信息，如字段名、字段类型等。INNODB_SYS_COLUMNSInnoDB 系统列信息表。此表用于记录 InnoDB 系统表中每个列的信息，如列名、列类型、列长度等。INNODB_SYS_STATSInnoDB 系统统计信息表。此表用于记录 InnoDB 系统统计信息，如表数量、数据量等。INNODB_SYS_FOREIGNInnoDB 系统外键信息表。此表用于记录 InnoDB 系统表中外键的信息，如外键约束条件等。INNODB_SYS_INDEXESInnoDB 系统索引信息表。此表用于记录 InnoDB 系统表中索引的信息，如索引名、索引类型等。

mysql ：主数据库，mysqld运行的必须数据库，用户与配置信息

表名称作用columns_priv保存了每个表的列级别的权限信息，包括用户对各个列的SELECT、INSERT、UPDATE、REFERENCES等权限。db保存了每个数据库的权限信息，包括用户对每个数据库的CREATE、ALTER、DROP等权限。event保存了MySQL中的事件信息，包括事件的名称、执行时间、执行语句等。func保存了用户定义的存储函数的信息，包括函数的名称、参数、返回类型等。general_log记录了MySQL服务器上所有的日志操作，包括查询、连接、错误日志等。help_category保存了MySQL帮助文档中的分类信息，用于帮助查找和浏览文档。help_keyword保存了MySQL帮助文档的关键字信息，用于快速搜索和查找文档。help_relation保存了MySQL帮助文档中关键字之间的关系信息，用于帮助构建文档的结构。help_topic保存了MySQL帮助文档的具体内容信息，包括每个主题的标题、内容等。host保存了MySQL服务器上的主机信息，包括主机名、IP地址、连接权限等。ndb_binlog_index保存了使用NDB存储引擎的MySQL服务器上的二进制日志索引信息。plugin保存了MySQL服务器上安装的插件信息。proc保存了用户定义的存储过程的信息，包括过程的名称、参数、语句等。procs_priv保存了用户对存储过程的访问权限信息。proxies_priv保存了MySQL服务器上的代理用户的权限信息。servers实验性表，保存了MySQL服务器的外部服务器和复制配置信息。slow_log记录了MySQL服务器上执行时间超过默认阈值的慢查询日志。tables_priv保存了用户对表的访问权限信息。time_zone保存了MySQL服务器上的时区信息。time_zone_leap_second保存了时区闰秒的信息。time_zone_name保存了时区的名称和相关信息。time_zone_transition保存了时区的变化规则和信息。time_zone_transition_type保存了时区变化类型的信息。user保存了MySQL服务器上的用户账号信息，包括用户名、密码、权限等。

performance_schema：性能数据库，存储mysqld的资源使用、安全策略权限配置信息

表名作用cond_instances条件实例表，该表用于存储各种条件或锁定的实例信息events_waits_current当前等待事件表，记录了当前正在等待某个事件发生的线程或会话信息events_waits_history历史等待事件表，记录了过去一段时间内等待事件的信息，包括等待事件的类型、等待时间等events_waits_history_long长期等待事件表，记录了长时间等待事件的信息，包括等待事件的类型、等待时间、等待时长等events_waits_summary_by_instance按实例总结等待事件表，提供了按实例总结的等待事件统计信息events_waits_summary_by_thread_by_event_name按线程和事件总结等待事件表，提供了按线程和特定事件总结的等待事件统计信息events_waits_summary_global_by_event_name按全局和事件总结等待事件表，提供了按全局和特定事件总结的等待事件统计信息file_instances文件实例表，记录了数据库中各个文件的信息，包括文件路径、文件大小等file_summary_by_event_name按事件名总结文件表，提供了按特定事件名总结的文件统计信息file_summary_by_instance按实例总结文件表，提供了按实例总结的文件统计信息mutex_instances互斥体实例表，记录了数据库中各个互斥体的信息，包括互斥体的名称、状态等performance_timers性能计时器表，提供了数据库性能的计时信息，包括执行时间、资源使用情况等rwlock_instances读写锁实例表，记录了数据库中各个读写锁的信息setup_consumers设置消费者表，记录了数据库设置的各种消费者信息setup_instruments设置仪器表，记录了数据库设置的各类性能指标信息setup_timers设置定时器表，记录了数据库设置的各类定时器信息threads线程表，记录了数据库中的各个线程信息，包括线程ID、线程状态等

sys库

五、 SQL语言

DDL：数据定义语言，对数据库结构操作

create：创建（用户，库，表）

alter：改变

drop：删除

DML：数据操作语言，对数据表的操作

insert：插入

update：更新

delete：删除数据

DCL：数据控制语言，针对用户权限设置

grant：用户赋权

revoke：移除用户权限

DQL：数据查询语言，对数据表的操作

select：查询

六、mysql数据类型

常用的数据类型有：

整型

浮点型

BIT类型

定点数

日期时间类型

字符串

NULL类型

1.整型

整数类型占用字节无符号数的取值范围有符号数的取值范围TINYINT10~255-128~127SMALLINT20~65535-32768~32767MEDIUMINT30~16777215-8388608~8388607INT40~4294967295-2147483648~2147483647BIGINT80~18446744073709551615-9223372036854774808~9223372036854774807

整数列的可选属性有三个：

M: 宽度(在0填充的时候才有意义，否则不需要指定)

unsigned: 无符号类型(非负)

zerofill: 0填充,(如果某列是zerofill，那么默认就是无符号)，如果指定了zerofill只是表示不够M位时，用0在左边填充，如果超过M位，只要不超过数据存储范围即可

在 int(M) 中，M 的值跟 int(M) 所占多少存储空间并无任何关系。 int(3)、int(4)、int(8) 在磁盘上都是占用 4 bytes 的存储空间。

各整数数据类型的使用场所

数据类型应用场景TINYINT一般用于枚举数据，比如系统设定取值范围很小且固定的场景。SMALLINT可以用于较小范围的统计数据，比如统计工厂的固定资产库存数量等。MEDIUMINT用于较大整数的计算，比如车站每日的客流量等。INT、INTEGER取值范围足够大，一般情况下不用考虑超限问题，用得最多。比如商品编号。BIGINT只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等。

2.浮点型

数据类型字节数取值范围FLOAT4-2^128~2 ^128，即-3.40E+38~+3.40E+38DOUBLE8-2^1024~ 2^1024，即-1.79E+308~1.79E+308

当浮点数类型使用unsigned修饰无符号时，取值范围将不包含负数。

浮点数的取值范围是理论上的极限值，但根据不同的硬件或操作系统，实际范围可能会小。

浮点数虽然取值范围很大，但精度并不高。float类型的精度为6位或7位，double类型的精度大约为15位。

如果给定的数值超出精度，可能会导致给定的数值与实际保存的数值不一致，发生精度损失。

当一个数字的整数部分和小数部分加起来达到7位时，第7位就会进行四舍五入操作。

要避免使用“=”来判断两个浮点数是否相等，因为浮点数是不准确的，存在精度损失。

3.BIT类型

函数函数用途BIT(M)存储二进制数据ASCll(M)获取M的ASCll值BIN(M)获取M的二进制值LENGTH(M)获取M的数字长度

BIT数据类型可用来保存位字段值。BIT(M)类型允许存储M位值。M范围为1~64，默认为1。

BIT其实就是存入二进制的值，类似010110。如果存入一个BIT类型的值，位数少于M值，则左补0。如果存入一个BIT类型的值，位数多于M值，MySQL的操作取决于此时有效的SQL模式：如果模式未设置，MySQL将值裁剪到范围的相应端点，并保存裁减好的值。如果模式设置为traditional(“严格模式”)，超出范围的值将被拒绝并提示错误，并且根据SQL标准插入会失败。

对于位字段，直接使用SELECT命令将不会看到结果，可以用bin()或hex()函数进行读取。

4.定点型

DECIMAL在MySQL内部以字符串形式存放，比浮点数更精确。定点类型占M+2个字节

DECIMAL(M,D)与浮点型一样处理规则。M的取值范围为065，D的取值范围为030，而且必须<=M，超出范围会报错。

DECIMAL如果指定精度时，默认的整数位是10，默认的小数位为0。

NUMERIC等价于DECIMAL。

例如，DECIMAL(5,2)表示的取值范围为-999.99~999.99。

5.日期时间类型

数据类型字节取值范围日期格式零值YEAR11901~2155YYYY0000DATE31000-01-01~9999-12-31YYYY-MM-DD0000-00-00TIME3-838:59:59~838:59:59HH:MM:SS00:00:00DATETIME81000-01-01 00:00:00~9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS0000-00-00 00:00:00TIMESTAMP41970-01-01 00:00:01~2038-01-19 03:14:07YYYY-MM-DD HH:MM:SS0000-00-00 00:00:00

（1）YEAR类型

YEAR类型用来表示年份，在所有的日期时间类型中所占用的存储空间最小，只需要1个字节的存储空间，格式为YYYY。

在MySQL中，可使用以下3种格式指定TEAR类型的值：

使用4位字符串或数字表示，为1901'2155或19012155。

例如，输入2022或2022，插入到数据库中的值均为2022.

使用两位字符串表示，为00~99。

00 ~ '69的值会被转换为2000~2069的YEAR值

70 ~ '99的值会被自动转换为1970~1999的YEAR值

例如，输入22，插入到数据表中的值为2022。

使用两位数字表示，为1~99。

1 ~ 69的值会被转换为2001~2069的YEAR值

70 ~ 99的值会被自动转换为1970~1999的YEAR值

例如，输入22，插入到数据表中的值为2022。

注意：

当使用YEAR类型时，一定要区分0和0。

数字格式的0表示的YEAR值为0000

字符串格式的0表示的YEAR值为2000

（2）DATE类型

DATE类型用来表示日期值，不包含时间部分，需要 3个字节的存储空间，且其格式为 YYYY-MM-DD 。其中，YYYY表示年份，MM表示月份，DD表示日期。

在MySQL中，可以使用以下4种格式指定DATE类型的值：

以YYYY-MM-DD或者YYYYMMDD字符串格式表示。

以YY-MM-DD或者YYMMDD字符串格式表示。

以YY-MM-DD或者YYMMDD数字格式表示。

使用CURRENT_DATE或者NOW()输入当前系统日期。

注意：

通过"SELECT CURRENT_DATE;"或者"SELECT NOW();"可查询当前日期。

日期中的分隔符"-“，还可以使用”.“”,“”/"等符号来表示。

（3）TIME类型

TIME类型用于表示时间值，它的显示形式一般为HH:MM:SS，其中HH表示小时，MM表示分，SS表示秒。

在MySQL中，可以使用以下3种格式指定TIME类型的值：

以HHMMSS字符串或者HHMMSS数学格式表示。

以HH:MM:SS字符串格式表示。

使用CURRENT_TIME或NOW()输入当前系统时间。

（4）DATETIME类型

DATETIME类型在所有的日期时间类型中占用的存储空间最大，总共需要 8 个字节的存储空间，用来表示日期和时间，它的显示形式为YYYY-MM-DD HH:MM:SS。

在MySQL中，可以使用以下4种格式指定DATETIME类型的值：

以YYYY-MM-DD HH:MM:SS或YYYYMMDDHHMMSS字符串格式表示的日期和时间，取值范围为1000-01-01 00:00:00~9999-12-31-23-59-59。

以YY-MM-DD HH:MM:SS或YYMMDDHHMMSS字符串格式表示的日期和时间，

以YYYYMMDDHHMMSS或YYMMDDHHMMSS数字格式表示的日期和时间，

使用NOW()来输入当前系统的日期和时间。

（5）TIMESTAMP类型

TIMESTAMP（时间戳）类型用于表示日期和时间，需要4个字节的存储空间，它的显示形式与DATETIME类型的相同，但取值范围比DATETIME类型的小。

TIMESTAMP类型与DATETIME类型的不同形式：

使用CURRENT_TIMESTAMP来输入系统当前的日期和时间。

无任何输入，或输入NULL时，实际保存的是系统当前日期和时间。

6.字符串型

数据类型类型说明CHAR固定长度字符串VARCHAR可变长度字符串TEXT大文本数据ENUM枚举类型SET字符串类型BINARY固定长度的二进制数据VARBINARY可变长度是二进制数据BLOB二进制大对象

（1）CHAR和VARCHAR类型

CHAR和VARCHAR类型都是用来保存字符串数据，两者不同的是，VARCHAR可以存储可变长度的字符串数据。

字符串类型特点长度长度范围占用的存储空间CHAR(M)固定长度M0<=M<=255M个字节VARCHAR(M)可变长度M0<=M<=65535(实际长度 + 1) 个字节

（2）TEXT类型

TEXT类型用于保存大文本数据，例如，文章内容，评论等比较长的文本。

数据类型存储范围存储空间占用量特点TINYTEXT(tinytext)0~2^8-1文本长度+2小文本，可变长度TEXT(text)0~2^16-1文本长度+2文本，可变长度MEDIUMTEXT(mediumtext)0~2^24-1文本长度+3中等文本，可变长度LONGTEXT(longtext)0~2^32-1文本长度+4大文本，可变长度

7.ENUM类型

ENUM类型又称为枚举类型，其定义格式为：

ENUM('值1','值2','值3','值4','值5',....,'值n')

ENUM类型的取值范围需要在定义字段时进行指定。

设置字段值时，ENUM类型只允许从成员中选取单个值，不能一次选取多个值。

其所需要的存储空间由定义ENUM类型时指定的成员个数决定。

当ENUM类型包含1~255个成员时，需要1个字节的存储空间

当ENUM类型包含256~65535个成员时，需要2个字节的存储空间

ENUM类型的成员个数的上限为65535个

8.SET类型

SET类型用于保存字符串对象，其定义格式与ENUM类型相似。

SET('值1','值2','值3','值4','值5',....,'值n')

（1）其所需要的存储空间由定SET类型时指定的成员个数决定。

SET类型包含的成员数存储空间1~81个字节9~162个字节17~243个字节25~324个字节33~648个字节

（2）SET类型在存储数据时成员个数越多，其占用的存储空间越大。

（3）SET类型在选取成员时，与ENUM类型不同，其可以一次选择多个成员。

9.BINARY和VARBINARY类型

BINARY和VARBINARY类型类似于CHAR和VARCHAR，不同的是，它们所表示的是二进制数据。

类型特点长度长度范围占用的存储空间BINARY(M)固定长度M0<=M<=255M个字节VARBINARY(M)可变长度M0<=M<=65535(M+ 1) 个字节

10.BLOB类型

BLOB类型用于保存数据量比较大的二进制数据，如图片，PDF文档等。

数据类型存储范围占用空间TINYBLOB0~2^8 -1字节len+1个字节BLOB0~2^16 -1字节（相当于64KB）len+2个字节MEDIUMBLOB0~2^24 -1字节（相当于16MB）len+3个字节LONGBLOB0~2^32 -1字节（相当于4GB）len+4个字节

11.JSON数据类型

JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，简洁和清晰的层次结构使得 JSON 成为理想的数据交换语言。它易于人阅读和编写，同时也易于机器解析和生成，并有效地提升网络传输效率。

JSON 可以将 JavaScript 对象中表示的一组数据转换为字符串，然后就可以在网络或者程序之间轻松地传递这个字符串，并在需要的时候将它还原为各编程语言所支持的数据格式。

在MySQL 5.7中，就已经支持JSON数据类型。在MySQL中，JSON类型常见的表示方式有2种，分别为JSON数组和JSON对象。

#JSON数组

['abc',10,null,true]

#JSON对象

{"k1":"value","k2":10}

12.特殊的NULL类型

所有的类型的值都可以是null，包括int、float等数据类型

空字符串””，不等于null，0也不等于null，false也不等于null

任何运算符,判断符碰到NULL,都得NULL

NULL的判断只能用is null,is not null

NULL 影响查询速度,一般避免使值为NULL

七、数据库操作命令

1.库操作命令

（1）查看数据库（存储当前MySQL库名称，表名称及列名称唯独没有数据）

show databases;

（2）查看所在数据库

select database();

（3）查看当前登录用户（返回函数值）

select user();

（4）查看当前数据库版本

select version();

（5）查看用户权限列表

show privileges[\G];

mysql> show privileges\G;

*************************** 1. row ***************************

Privilege: Alter

Context: Tables

Comment: To alter the table

*************************** 2. row ***************************

Privilege: Alter routine

Context: Functions,Procedures

Comment: To alter or drop stored functions/procedures

*************************** 3. row ***************************

Privilege: Create

Context: Databases,Tables,Indexes

Comment: To create new databases and tables

*************************** 4. row ***************************

Privilege: Create routine

Context: Databases

Comment: To use CREATE FUNCTION/PROCEDURE

*************************** 5. row ***************************

Privilege: Create role

Context: Server Admin

Comment: To create new roles

*************************** 6. row ***************************

Privilege: Create temporary tables

Context: Databases

Comment: To use CREATE TEMPORARY TABLE

*************************** 7. row ***************************

Privilege: Create view

Context: Tables

Comment: To create new views

*************************** 8. row ***************************

Privilege: Create user

Context: Server Admin

Comment: To create new users

*************************** 9. row ***************************

Privilege: Delete

Context: Tables

Comment: To delete existing rows

*************************** 10. row ***************************

Privilege: Drop

Context: Databases,Tables

Comment: To drop databases, tables, and views

*************************** 11. row ***************************

Privilege: Drop role

Context: Server Admin

Comment: To drop roles

*************************** 12. row ***************************

Privilege: Event

Context: Server Admin

Comment: To create, alter, drop and execute events

*************************** 13. row ***************************

Privilege: Execute

Context: Functions,Procedures

Comment: To execute stored routines

*************************** 14. row ***************************

Privilege: File

Context: File access on server

Comment: To read and write files on the server

*************************** 15. row ***************************

Privilege: Grant option

Context: Databases,Tables,Functions,Procedures

Comment: To give to other users those privileges you possess

*************************** 16. row ***************************

Privilege: Index

Context: Tables

Comment: To create or drop indexes

*************************** 17. row ***************************

Privilege: Insert

Context: Tables

Comment: To insert data into tables

*************************** 18. row ***************************

Privilege: Lock tables

Context: Databases

Comment: To use LOCK TABLES (together with SELECT privilege)

*************************** 19. row ***************************

Privilege: Process

Context: Server Admin

Comment: To view the plain text of currently executing queries

*************************** 20. row ***************************

Privilege: Proxy

Context: Server Admin

Comment: To make proxy user possible

*************************** 21. row ***************************

Privilege: References

Context: Databases,Tables

Comment: To have references on tables

*************************** 22. row ***************************

Privilege: Reload

Context: Server Admin

Comment: To reload or refresh tables, logs and privileges

*************************** 23. row ***************************

Privilege: Replication client

Context: Server Admin

Comment: To ask where the slave or master servers are

*************************** 24. row ***************************

Privilege: Replication slave

Context: Server Admin

Comment: To read binary log events from the master

*************************** 25. row ***************************

Privilege: Select

Context: Tables

Comment: To retrieve rows from table

*************************** 26. row ***************************

Privilege: Show databases

Context: Server Admin

Comment: To see all databases with SHOW DATABASES

*************************** 27. row ***************************

Privilege: Show view

Context: Tables

Comment: To see views with SHOW CREATE VIEW

*************************** 28. row ***************************

Privilege: Shutdown

Context: Server Admin

Comment: To shut down the server

*************************** 29. row ***************************

Privilege: Super

Context: Server Admin

Comment: To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.

*************************** 30. row ***************************

Privilege: Trigger

Context: Tables

Comment: To use triggers

*************************** 31. row ***************************

Privilege: Create tablespace

Context: Server Admin

Comment: To create/alter/drop tablespaces

*************************** 32. row ***************************

Privilege: Update

Context: Tables

Comment: To update existing rows

*************************** 33. row ***************************

Privilege: Usage

Context: Server Admin

Comment: No privileges - allow connect only

*************************** 34. row ***************************

Privilege: TELEMETRY_LOG_ADMIN

Context: Server Admin

Comment:

*************************** 35. row ***************************

Privilege: SHOW_ROUTINE

Context: Server Admin

Comment:

*************************** 36. row ***************************

Privilege: RESOURCE_GROUP_USER

Context: Server Admin

Comment:

*************************** 37. row ***************************

Privilege: REPLICATION_APPLIER

Context: Server Admin

Comment:

*************************** 38. row ***************************

Privilege: PASSWORDLESS_USER_ADMIN

Context: Server Admin

Comment:

*************************** 39. row ***************************

Privilege: INNODB_REDO_LOG_ENABLE

Context: Server Admin

Comment:

*************************** 40. row ***************************

Privilege: XA_RECOVER_ADMIN

Context: Server Admin

Comment:

*************************** 41. row ***************************

Privilege: GROUP_REPLICATION_STREAM

Context: Server Admin

Comment:

*************************** 42. row ***************************

Privilege: GROUP_REPLICATION_ADMIN

Context: Server Admin

Comment:

*************************** 43. row ***************************

Privilege: SENSITIVE_VARIABLES_OBSERVER

Context: Server Admin

Comment:

*************************** 44. row ***************************

Privilege: FLUSH_USER_RESOURCES

Context: Server Admin

Comment:

*************************** 45. row ***************************

Privilege: FLUSH_TABLES

Context: Server Admin

Comment:

*************************** 46. row ***************************

Privilege: FLUSH_STATUS

Context: Server Admin

Comment:

*************************** 47. row ***************************

Privilege: PERSIST_RO_VARIABLES_ADMIN

Context: Server Admin

Comment:

*************************** 48. row ***************************

Privilege: AUTHENTICATION_POLICY_ADMIN

Context: Server Admin

Comment:

*************************** 49. row ***************************

Privilege: BINLOG_ADMIN

Context: Server Admin

Comment:

*************************** 50. row ***************************

Privilege: ROLE_ADMIN

Context: Server Admin

Comment:

*************************** 51. row ***************************

Privilege: BACKUP_ADMIN

Context: Server Admin

Comment:

*************************** 52. row ***************************

Privilege: CONNECTION_ADMIN

Context: Server Admin

Comment:

*************************** 53. row ***************************

Privilege: BINLOG_ENCRYPTION_ADMIN

Context: Server Admin

Comment:

*************************** 54. row ***************************

Privilege: RESOURCE_GROUP_ADMIN

Context: Server Admin

Comment:

*************************** 55. row ***************************

Privilege: INNODB_REDO_LOG_ARCHIVE

Context: Server Admin

Comment:

*************************** 56. row ***************************

Privilege: AUDIT_ABORT_EXEMPT

Context: Server Admin

Comment:

*************************** 57. row ***************************

Privilege: FLUSH_OPTIMIZER_COSTS

Context: Server Admin

Comment:

*************************** 58. row ***************************

Privilege: CLONE_ADMIN

Context: Server Admin

Comment:

*************************** 59. row ***************************

Privilege: SET_USER_ID

Context: Server Admin

Comment:

*************************** 60. row ***************************

Privilege: SESSION_VARIABLES_ADMIN

Context: Server Admin

Comment:

*************************** 61. row ***************************

Privilege: REPLICATION_SLAVE_ADMIN

Context: Server Admin

Comment:

*************************** 62. row ***************************

Privilege: SYSTEM_VARIABLES_ADMIN

Context: Server Admin

Comment:

*************************** 63. row ***************************

Privilege: FIREWALL_EXEMPT

Context: Server Admin

Comment:

*************************** 64. row ***************************

Privilege: SYSTEM_USER

Context: Server Admin

Comment:

*************************** 65. row ***************************

Privilege: APPLICATION_PASSWORD_ADMIN

Context: Server Admin

Comment:

*************************** 66. row ***************************

Privilege: TABLE_ENCRYPTION_ADMIN

Context: Server Admin

Comment:

*************************** 67. row ***************************

Privilege: SERVICE_CONNECTION_ADMIN

Context: Server Admin

Comment:

*************************** 68. row ***************************

Privilege: AUDIT_ADMIN

Context: Server Admin

Comment:

*************************** 69. row ***************************

Privilege: ENCRYPTION_KEY_ADMIN

Context: Server Admin

Comment:

69 rows in set (0.00 sec)

（6）查看指定用户的权限

show grants for root@localhost\G;

mysql> show grants for root@localhost\G;

*************************** 1. row ***************************

Grants for root@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION

*************************** 2. row ***************************

Grants for root@localhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION

*************************** 3. row ***************************

Grants for root@localhost: GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION

3 rows in set (0.00 sec)

（7）切换数据库

use databaseName[;]

（8）创建数据库

create database databaseName [character set utf8];

（9）删除数据库

drop database databaseName;

（10）查看支持的字符集

SHOW CHARACTER SET；

（11）赋加用户权限

grant all on *.* to zhangsan@'192.168.72.1';

（12）查看当前MySQL活动连接（除了事件调度器和当前会话）

mysql> show processlist;

+----+-----------------+-----------+------+---------+------+------------------------+------------------+

+----+-----------------+-----------+------+---------+------+------------------------+------------------+

+----+-----------------+-----------+------+---------+------+------------------------+------------------+

2 rows in set, 1 warning (0.00 sec)

（13）输出1的表头（字段名称）以及1的值

mysql> select 1;

+---+

| 1 |

+---+

| 1 |

+---+

1 row in set (0.00 sec)

（14）调出user表中的所有

mysql> select * from user;

+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+

| localhost | mysql.infoschema | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | 0x | 0x | 0x | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2025-06-25 23:25:32 | NULL | Y | N | N | NULL | NULL | NULL | NULL |

| localhost | mysql.session | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | | 0x | 0x | 0x | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2025-06-25 23:25:32 | NULL | Y | N | N | NULL | NULL | NULL | NULL |

| localhost | mysql.sys | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | 0x | 0x | 0x | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2025-06-25 23:25:32 | NULL | Y | N | N | NULL | NULL | NULL | NULL |

| localhost | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | 0x | 0x | 0x | 0 | 0 | 0 | 0 | mysql_native_password | | N | 2025-06-25 23:25:32 | NULL | N | Y | Y | NULL | NULL | NULL | NULL |

4 rows in set (0.00 sec)

2.表结构操作命令

（1）查看数据表

show tables;

mysql> show tables;

+------------------------------------------------------+

| Tables_in_mysql |

+------------------------------------------------------+

| columns_priv |

| component |

| db |

| default_roles |

| engine_cost |

| func |

| general_log |

| global_grants |

| gtid_executed |

| help_category |

| help_keyword |

| help_relation |

| help_topic |

| innodb_index_stats |

| innodb_table_stats |

| password_history |

| plugin |

| procs_priv |

| proxies_priv |

| replication_asynchronous_connection_failover |

| replication_asynchronous_connection_failover_managed |

| replication_group_configuration_version |

| replication_group_member_actions |

| role_edges |

| server_cost |

| servers |

| slave_master_info |

| slave_relay_log_info |

| slave_worker_info |

| slow_log |

| tables_priv |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

+------------------------------------------------------+

37 rows in set (0.00 sec)

（2）查看表结构

desc[describe] tableName;

[root@mysqld ~]# mysql -e "desc mysql.user"

+--------------------------+-----------------------------------+------+-----+-----------------------+-------+

+--------------------------+-----------------------------------+------+-----+-----------------------+-------+

| Host | char(255) | NO | PRI | | |

| User | char(32) | NO | PRI | | |

| Select_priv | enum('N','Y') | NO | | N | |

| Insert_priv | enum('N','Y') | NO | | N | |

| Update_priv | enum('N','Y') | NO | | N | |

| Delete_priv | enum('N','Y') | NO | | N | |

| Create_priv | enum('N','Y') | NO | | N | |

| Drop_priv | enum('N','Y') | NO | | N | |

| Reload_priv | enum('N','Y') | NO | | N | |

| Shutdown_priv | enum('N','Y') | NO | | N | |

| Process_priv | enum('N','Y') | NO | | N | |

| File_priv | enum('N','Y') | NO | | N | |

| Grant_priv | enum('N','Y') | NO | | N | |

| References_priv | enum('N','Y') | NO | | N | |

| Index_priv | enum('N','Y') | NO | | N | |

| Alter_priv | enum('N','Y') | NO | | N | |

| Show_db_priv | enum('N','Y') | NO | | N | |

| Super_priv | enum('N','Y') | NO | | N | |

| Create_tmp_table_priv | enum('N','Y') | NO | | N | |

| Lock_tables_priv | enum('N','Y') | NO | | N | |

| Execute_priv | enum('N','Y') | NO | | N | |

| Repl_slave_priv | enum('N','Y') | NO | | N | |

| Repl_client_priv | enum('N','Y') | NO | | N | |

| Create_view_priv | enum('N','Y') | NO | | N | |

| Show_view_priv | enum('N','Y') | NO | | N | |

| Create_routine_priv | enum('N','Y') | NO | | N | |

| Alter_routine_priv | enum('N','Y') | NO | | N | |

| Create_user_priv | enum('N','Y') | NO | | N | |

| Event_priv | enum('N','Y') | NO | | N | |

| Trigger_priv | enum('N','Y') | NO | | N | |

| Create_tablespace_priv | enum('N','Y') | NO | | N | |

| max_questions | int unsigned | NO | | 0 | |

| max_updates | int unsigned | NO | | 0 | |

| max_connections | int unsigned | NO | | 0 | |

| max_user_connections | int unsigned | NO | | 0 | |

| password_expired | enum('N','Y') | NO | | N | |

| account_locked | enum('N','Y') | NO | | N | |

| Create_role_priv | enum('N','Y') | NO | | N | |

| Drop_role_priv | enum('N','Y') | NO | | N | |

+--------------------------+-----------------------------------+------+-----+-----------------------+-------+

（3）创建数据表

create table tableName(columnName（列名称） dataType（数据类型）, ............);

（4）查看表结构

mysql> describe t1;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int | YES | | NULL | |

| sex | varchar(4) | YES | | NULL | |

| phone | int | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

###或者

mysql> desc t1;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int | YES | | NULL | |

| sex | varchar(4) | YES | | NULL | |

| phone | int | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

（5）查看表内容

mysql> select * from t1;

Empty set (0.01 sec)

（6）删除数据表（表结构）/删除数据库

drop table tableName;

（7）删除表数据

delete table tableName;

（8）删除id=1的数据

mysql> delete from t1 where id=1;

Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;

+------+----------+------+------------+

| id | name | sex | phone |

+------+----------+------+------------+

| 2 | zhangsan | 男 | 1111111111 |

+------+----------+------+------------+

1 row in set (0.00 sec)

（9）全表更新

mysql> update t1 set name=NULL; ##空

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from t1;

+------+------+------+------------+

| id | name | sex | phone |

+------+------+------+------------+

| 2 | NULL | 男 | 1111111111 |

+------+------+------+------------+

1 row in set (0.00 sec)

mysql> update t1 set name=''; ##空字符串

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from t1;

+------+------+------+------------+

| id | name | sex | phone |

+------+------+------+------------+

| 2 | | 男 | 1111111111 |

+------+------+------+------------+

1 row in set (0.00 sec)

（10）自动增长列

mysql> desc t1;

+-------------+--------------+------+-----+---------+----------------+

+-------------+--------------+------+-----+---------+----------------+

| id | int | NO | PRI | NULL | auto_increment |

+-------------+--------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

mysql> insert t1(create_time,name) value('2025-06-26 11:37:00','zhangsan'),('2025-06-26 11:37:01','lisi');

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from t1;

+----+---------------------+----------+

| id | create_time | name |

+----+---------------------+----------+

| 1 | 2025-06-26 11:37:00 | zhangsan |

| 2 | 2025-06-26 11:37:01 | lisi |

+----+---------------------+----------+

2 rows in set (0.00 sec)

（11）alter命令用法

语法

ALTER TABLE <表名> [修改选项]

常见用法

| ADD COLUMN <列名> <类型>

| CHANGE COLUMN <旧列名> <新列名> <新列类型>

| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }

| MODIFY COLUMN <列名> <类型>

| DROP COLUMN <列名>

| RENAME TO <新表名>

| CHARACTER SET <字符集名>

| COLLATE <校对规则名>

案例

（1）创建测试表

create table stu_score(id tinyint, name varchar(50), score decimal(4,2), object varchar(50));

（2）修改表名

#alter table tab_name rename to tab_new;

#修改表名

alter table stu_score rename to stuS;

（3）修改字段类型(修改字段相对位置)

#alter table tab_name modify field type (first/before/after field1);

alter table stuS modify id int;

（4）修改字段默认值/是否为空/自动增长

#alter table tab_name modify field type not null/default ="未知"/auto_increment;

#自动增长

alter table stuS modify id int primary key;

alter table stuS modify id int auto_increment;

（5）extra设定默认值的自动归补

（6）修改字段名/字段类型

#alter table tab_name change field newfield newtype;

#修改字段类型

alter table stuS modify id int;

#修改字段名及类型

alter table stuS change column object subject varchar(50);

#通过alter modify 修改字段是否为null或者默认值!

alter table stuS modify column name varchar(50) not null default "未知";

（7）添加字段

#alter table tab_name add field type (first/before/after field1);

alter table stuS add column extra text;

#添加到第一列

alter table stuS add i int first;

#添加到某一字段后面!

alter table stuS add sex varchar(10) after name;

（8）删除字段

#alter table tab_name drop field;

alter table stuS drop id;

3.表数据操作

（1）插入数据

①单行插入

insert [into] tableName[(columnName,.........)] value('value1',value2,.......);

②批量插入

insert into stu value('张三', 22, '11011011011', '男'),('王无',26 , '12011011011', '女'),('里斯',27 ,'12011011099', '男');

注意：只书写表名称，表示全表插入，即，全字段插入！插入的数据数量与字段数量一定要相等！

where字句运算符

比较运算符

运算符说明>, >=, <, <=大于，大于等于，小于，小于等于=等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL<=>等于，NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1)!=, <>不等于BETWEEN a0 AND a1范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1)IN (option, …)如果是 option 中的任意一个，返回 TRUE(1)IS NULL是 NULLIS NOT NULL不是 NULLLIKE模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符

逻辑运算符

运算符说明AND （&&）多个条件必须都为 TRUE(1)，结果才是 TRUE(1)OR （||）任意一个条件为 TRUE(1), 结果为 TRUE(1)NOT （！）条件为 TRUE(1)，结果为 FALSE(0)

更新数据

全表更新

update tableName set columnName=newValue;

条件更新

update tableName set columnName=newValue where columnName=currentValue;

update stuS set sex='女' where name='铁蛋' and subject='中国武术';

mysql> update student set address='上海市浦东新区' where name = '张三';

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

删除数据

全表删除

delete from tableName;

条件删除

delete from tableName where columnName=currentValue;

###使用唯一标识删除老六记录行

mysql> delete from student where student_id = 6;

Query OK, 1 row affected (0.02 sec)

查询数据

实战

题目：学生管理系统数据库设计

作为资深DBA架构师，设计学生管理系统表结构，实体有学生、专业、学院、课程、班级等，提供ER图，提供建表SQL语句，提供插入数据，插入数据要求：学院设定3个，每个学院设定3个专业，每个专业设定3个年级，每个年级设定3个班，每个班设定50人，每个专业设定6门课，每个门课设定3个老师，并进行验证，字符集为utf8mb4，排序规则为utf8mb4_general_ci。

ER图

完整建表SQL（含字符集设置）

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

-- 学院表

CREATE TABLE `college` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`name` VARCHAR(50) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 专业表

CREATE TABLE `major` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`name` VARCHAR(50) NOT NULL,

`college_id` INT(11) NOT NULL,

PRIMARY KEY (`id`),

KEY `college_id` (`college_id`),

CONSTRAINT `major_ibfk_1` FOREIGN KEY (`college_id`) REFERENCES `college` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 班级表

CREATE TABLE `class` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`name` VARCHAR(20) NOT NULL,

`grade` TINYINT(4) NOT NULL COMMENT '年级 (1-3)',

`major_id` INT(11) NOT NULL,

PRIMARY KEY (`id`),

KEY `major_id` (`major_id`),

CONSTRAINT `class_ibfk_1` FOREIGN KEY (`major_id`) REFERENCES `major` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 学生表

CREATE TABLE `student` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`student_id` CHAR(12) NOT NULL COMMENT '学号',

`name` VARCHAR(50) NOT NULL,

`class_id` INT(11) NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `student_id` (`student_id`),

KEY `class_id` (`class_id`),

CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 课程表

CREATE TABLE `course` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`name` VARCHAR(50) NOT NULL,

`credit` FLOAT NOT NULL,

`major_id` INT(11) NOT NULL,

PRIMARY KEY (`id`),

KEY `major_id` (`major_id`),

CONSTRAINT `course_ibfk_1` FOREIGN KEY (`major_id`) REFERENCES `major` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 教师表

CREATE TABLE `teacher` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`teacher_id` CHAR(8) NOT NULL COMMENT '教师编号',

`name` VARCHAR(50) NOT NULL,

`college_id` INT(11) NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `teacher_id` (`teacher_id`),

KEY `college_id` (`college_id`),

CONSTRAINT `teacher_ibfk_1` FOREIGN KEY (`college_id`) REFERENCES `college` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 授课关系表

CREATE TABLE `teaching` (

`teacher_id` INT(11) NOT NULL,

`course_id` INT(11) NOT NULL,

PRIMARY KEY (`teacher_id`,`course_id`),

KEY `course_id` (`course_id`),

CONSTRAINT `teaching_ibfk_1` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`),

CONSTRAINT `teaching_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 成绩表

CREATE TABLE `score` (

`student_id` INT(11) NOT NULL,

`course_id` INT(11) NOT NULL,

`grade` FLOAT DEFAULT NULL CHECK (`grade` BETWEEN 0 AND 100),

PRIMARY KEY (`student_id`,`course_id`),

KEY `course_id` (`course_id`),

CONSTRAINT `score_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`),

CONSTRAINT `score_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

SET FOREIGN_KEY_CHECKS = 1;

完整数据插入脚本

-- 清空表（按依赖顺序）

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE TABLE score;

TRUNCATE TABLE teaching;

TRUNCATE TABLE student;

TRUNCATE TABLE class;

TRUNCATE TABLE course;

TRUNCATE TABLE teacher;

TRUNCATE TABLE major;

TRUNCATE TABLE college;

SET FOREIGN_KEY_CHECKS = 1;

-- 插入3个学院

INSERT INTO college (name) VALUES

('计算机科学与技术学院'),

('经济管理学院'),

('机械工程学院');

-- 插入9个专业（每个学院3个）

INSERT INTO major (name, college_id) VALUES

-- 计算机学院 (ID:1)

('软件工程', 1), ('人工智能', 1), ('网络安全', 1),

-- 经管学院 (ID:2)

('金融学', 2), ('会计学', 2), ('市场营销', 2),

-- 机械学院 (ID:3)

('机械设计制造', 3), ('车辆工程', 3), ('材料成型', 3);

-- 插入81个班级（每个专业3个年级×3个班）

INSERT INTO class (name, grade, major_id)

SELECT

CONCAT(m.name, '-', g.grade, '-', c.class_no),

g.grade,

m.id

FROM major m

CROSS JOIN (SELECT 1 AS grade UNION SELECT 2 UNION SELECT 3) g

CROSS JOIN (SELECT 1 AS class_no UNION SELECT 2 UNION SELECT 3) c

ORDER BY m.id, g.grade, c.class_no;

-- 插入4050名学生（每个班50人）

INSERT INTO student (student_id, name, class_id)

SELECT

CONCAT('S', LPAD(c.id, 3, '0'), LPAD(n.num, 3, '0')) AS student_id,

CONCAT('学生', c.id, '-', n.num) AS name,

c.id AS class_id

FROM class c

JOIN (

WITH RECURSIVE numbers AS (

SELECT 1 AS num

UNION ALL

SELECT num + 1 FROM numbers WHERE num < 50

)

SELECT num FROM numbers

) n;

-- 插入54门课程（每个专业6门）

INSERT INTO course (name, credit, major_id) VALUES

-- 软件工程专业

('Java程序设计', 3.0, 1), ('数据库系统', 4.0, 1), ('Web开发技术', 3.5, 1),

('软件测试', 2.5, 1), ('算法设计与分析', 4.0, 1), ('移动应用开发', 3.0, 1),

-- 人工智能专业

('机器学习', 4.0, 2), ('深度学习', 4.5, 2), ('自然语言处理', 3.5, 2),

('计算机视觉', 4.0, 2), ('神经网络', 4.0, 2), ('智能机器人', 3.0, 2),

-- 网络安全专业

('网络攻防技术', 3.5, 3), ('密码学', 4.0, 3), ('信息安全', 3.0, 3),

('操作系统安全', 3.5, 3), ('数据安全', 3.0, 3), ('区块链技术', 4.0, 3),

-- 金融学专业

('金融学原理', 3.0, 4), ('投资学', 3.5, 4), ('国际金融', 4.0, 4),

('商业银行管理', 3.0, 4), ('证券分析', 3.0, 4), ('金融工程', 4.0, 4),

-- 会计学专业

('财务会计', 4.0, 5), ('管理会计', 3.5, 5), ('审计学', 3.0, 5),

('税法', 3.5, 5), ('成本会计', 3.0, 5), ('财务报表分析', 3.5, 5),

-- 市场营销专业

('市场营销学', 3.0, 6), ('消费者行为学', 3.5, 6), ('市场调研', 3.0, 6),

('品牌管理', 3.0, 6), ('数字营销', 4.0, 6), ('国际市场营销', 3.5, 6),

-- 机械设计制造专业

('机械原理', 4.0, 7), ('机械设计', 4.5, 7), ('工程材料', 3.0, 7),

('制造技术', 4.0, 7), ('CAD/CAM', 3.5, 7), ('机械振动', 3.0, 7),

-- 车辆工程专业

('汽车构造', 4.0, 8), ('汽车理论', 4.0, 8), ('发动机原理', 4.5, 8),

('汽车电子', 3.5, 8), ('汽车设计', 4.0, 8), ('新能源汽车', 3.5, 8),

-- 材料成型专业

('材料科学基础', 4.0, 9), ('材料成型原理', 4.0, 9), ('塑性成型', 3.5, 9),

('焊接技术', 3.5, 9), ('模具设计', 4.0, 9), ('3D打印技术', 4.0, 9);

-- 插入27名教师（每个学院9名）

INSERT INTO teacher (teacher_id, name, college_id) VALUES

-- 计算机学院

('T01001', '张教授', 1), ('T01002', '李副教授', 1), ('T01003', '王讲师', 1),

('T01004', '赵教授', 1), ('T01005', '刘副教授', 1), ('T01006', '陈讲师', 1),

('T01007', '杨教授', 1), ('T01008', '周副教授', 1), ('T01009', '吴讲师', 1),

-- 经管学院

('T02001', '钱教授', 2), ('T02002', '孙副教授', 2), ('T02003', '郑讲师', 2),

('T02004', '马教授', 2), ('T02005', '朱副教授', 2), ('T02006', '胡讲师', 2),

('T02007', '林教授', 2), ('T02008', '高副教授', 2), ('T02009', '何讲师', 2),

-- 机械学院

('T03001', '宋教授', 3), ('T03002', '唐副教授', 3), ('T03003', '许讲师', 3),

('T03004', '韩教授', 3), ('T03005', '冯副教授', 3), ('T03006', '邓讲师', 3),

('T03007', '曹教授', 3), ('T03008', '彭副教授', 3), ('T03009', '曾讲师', 3);

-- 为每门课程分配3名教师（同学院）

INSERT INTO teaching (teacher_id, course_id)

SELECT teacher_id, course_id

FROM (

SELECT

c.id AS course_id,

t.id AS teacher_id,

ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY RAND()) AS rn

FROM course c

JOIN major m ON c.major_id = m.id

JOIN teacher t ON m.college_id = t.college_id

) AS ranked

WHERE rn <= 3;

-- 验证数据完整性

SELECT '学院' AS entity, COUNT(*) AS count FROM college

UNION ALL

SELECT '专业', COUNT(*) FROM major

UNION ALL

SELECT '班级', COUNT(*) FROM class

UNION ALL

SELECT '学生', COUNT(*) FROM student

UNION ALL

SELECT '课程', COUNT(*) FROM course

UNION ALL

SELECT '教师', COUNT(*) FROM teacher

UNION ALL

SELECT '授课关系', COUNT(*) FROM teaching;

数据验证查询

-- 1. 验证学院-专业结构

SELECT

c.name AS '学院',

GROUP_CONCAT(m.name SEPARATOR ', ') AS '专业列表'

FROM college c

JOIN major m ON c.id = m.college_id

GROUP BY c.id;

###验证

+--------------------------------+------------------------------------------------+

| 学院 | 专业列表 |

+--------------------------------+------------------------------------------------+

| 计算机科学与技术学院 | 软件工程, 人工智能, 网络安全 |

| 经济管理学院 | 金融学, 会计学, 市场营销 |

| 机械工程学院 | 机械设计制造, 车辆工程, 材料成型 |

+--------------------------------+------------------------------------------------+

3 rows in set (0.00 sec)

-- 2. 验证班级-学生分布

SELECT

m.name AS '专业',

cl.grade AS '年级',

COUNT(DISTINCT cl.id) AS '班级数',

COUNT(s.id) AS '学生数'

FROM class cl

JOIN major m ON cl.major_id = m.id

JOIN student s ON cl.id = s.class_id

GROUP BY m.id, cl.grade;

###验证

+--------------------+--------+-----------+-----------+

| 专业 | 年级 | 班级数 | 学生数 |

+--------------------+--------+-----------+-----------+

| 软件工程 | 1 | 3 | 150 |

| 软件工程 | 2 | 3 | 150 |

| 软件工程 | 3 | 3 | 150 |

| 人工智能 | 1 | 3 | 150 |

| 人工智能 | 2 | 3 | 150 |

| 人工智能 | 3 | 3 | 150 |

| 网络安全 | 1 | 3 | 150 |

| 网络安全 | 2 | 3 | 150 |

| 网络安全 | 3 | 3 | 150 |

| 金融学 | 1 | 3 | 150 |

| 金融学 | 2 | 3 | 150 |

| 金融学 | 3 | 3 | 150 |

| 会计学 | 1 | 3 | 150 |

| 会计学 | 2 | 3 | 150 |

| 会计学 | 3 | 3 | 150 |

| 市场营销 | 1 | 3 | 150 |

| 市场营销 | 2 | 3 | 150 |

| 市场营销 | 3 | 3 | 150 |

| 机械设计制造 | 1 | 3 | 150 |

| 机械设计制造 | 2 | 3 | 150 |

| 机械设计制造 | 3 | 3 | 150 |

| 车辆工程 | 1 | 3 | 150 |

| 车辆工程 | 2 | 3 | 150 |

| 车辆工程 | 3 | 3 | 150 |

| 材料成型 | 1 | 3 | 150 |

| 材料成型 | 2 | 3 | 150 |

| 材料成型 | 3 | 3 | 150 |

+--------------------+--------+-----------+-----------+

27 rows in set (0.01 sec)

-- 3. 验证课程分配

SELECT

m.name AS '专业',

COUNT(c.id) AS '课程数',

AVG(t_count.teacher_count) AS '平均教师/课程'

FROM major m

JOIN course c ON m.id = c.major_id

JOIN (

SELECT course_id, COUNT(*) AS teacher_count

FROM teaching

GROUP BY course_id

) t_count ON c.id = t_count.course_id

GROUP BY m.id;

###验证

+--------------------+-----------+---------------------+

| 专业 | 课程数 | 平均教师/课程 |

+--------------------+-----------+---------------------+

| 软件工程 | 6 | 3.0000 |

| 人工智能 | 6 | 3.0000 |

| 网络安全 | 6 | 3.0000 |

| 金融学 | 6 | 3.0000 |

| 会计学 | 6 | 3.0000 |

| 市场营销 | 6 | 3.0000 |

| 机械设计制造 | 6 | 3.0000 |

| 车辆工程 | 6 | 3.0000 |

| 材料成型 | 6 | 3.0000 |

+--------------------+-----------+---------------------+

9 rows in set (0.01 sec)

-- 4. 验证教师授课情况

SELECT

c.name AS '学院',

t.name AS '教师',

COUNT(te.course_id) AS '授课课程数'

FROM teacher t

JOIN college c ON t.college_id = c.id

LEFT JOIN teaching te ON t.id = te.teacher_id

GROUP BY t.id

ORDER BY c.id, COUNT(te.course_id) DESC;

###验证

+--------------------------------+--------------+-----------------+

| 学院 | 教师 | 授课课程数 |

+--------------------------------+--------------+-----------------+

| 计算机科学与技术学院 | 李副教授 | 8 |

| 计算机科学与技术学院 | 王讲师 | 8 |

| 计算机科学与技术学院 | 赵教授 | 7 |

| 计算机科学与技术学院 | 陈讲师 | 7 |

| 计算机科学与技术学院 | 杨教授 | 6 |

| 计算机科学与技术学院 | 周副教授 | 6 |

| 计算机科学与技术学院 | 张教授 | 6 |

| 计算机科学与技术学院 | 刘副教授 | 5 |

| 计算机科学与技术学院 | 吴讲师 | 1 |

| 经济管理学院 | 何讲师 | 9 |

| 经济管理学院 | 郑讲师 | 8 |

| 经济管理学院 | 马教授 | 8 |

| 经济管理学院 | 高副教授 | 6 |

| 经济管理学院 | 朱副教授 | 5 |

| 经济管理学院 | 胡讲师 | 5 |

| 经济管理学院 | 钱教授 | 5 |

| 经济管理学院 | 林教授 | 4 |

| 经济管理学院 | 孙副教授 | 4 |

| 机械工程学院 | 曹教授 | 9 |

| 机械工程学院 | 许讲师 | 8 |

| 机械工程学院 | 唐副教授 | 6 |

| 机械工程学院 | 邓讲师 | 6 |

| 机械工程学院 | 彭副教授 | 6 |

| 机械工程学院 | 曾讲师 | 6 |

| 机械工程学院 | 冯副教授 | 5 |

| 机械工程学院 | 宋教授 | 4 |

| 机械工程学院 | 韩教授 | 4 |

+--------------------------------+--------------+-----------------+

27 rows in set (0.00 sec)

-- 5. 完整结构验证

WITH stats AS (

SELECT

(SELECT COUNT(*) FROM college) AS colleges,

(SELECT COUNT(*) FROM major) AS majors,

(SELECT COUNT(*) FROM class) AS classes,

(SELECT COUNT(*) FROM student) AS students,

(SELECT COUNT(*) FROM course) AS courses,

(SELECT COUNT(*) FROM teacher) AS teachers,

(SELECT COUNT(*) FROM teaching) AS teachings

)

SELECT

colleges AS '学院数',

majors AS '专业数',

classes AS '班级数',

students AS '学生数',

courses AS '课程数',

teachers AS '教师数',

teachings AS '授课关系数',

majors/colleges AS '专业/学院',

classes/majors AS '班级/专业',

students/classes AS '学生/班',

courses/majors AS '课程/专业',

teachings/courses AS '教师/课程'

FROM stats;

###验证

+-----------+-----------+-----------+-----------+-----------+-----------+-----------------+---------------+---------------+------------+---------------+---------------+

| 学院数 | 专业数 | 班级数 | 学生数 | 课程数 | 教师数 | 授课关系数 | 专业/学院 | 班级/专业 | 学生/班 | 课程/专业 | 教师/课程 |

+-----------+-----------+-----------+-----------+-----------+-----------+-----------------+---------------+---------------+------------+---------------+---------------+

| 3 | 9 | 81 | 4050 | 54 | 27 | 162 | 3.0000 | 9.0000 | 50.0000 | 6.0000 | 3.0000 |

+-----------+-----------+-----------+-----------+-----------+-----------+-----------------+---------------+---------------+------------+---------------+---------------+

1 row in set (0.01 sec)

设计说明

1.字符集规范：

所有表使用 utf8mb4 字符集排序规则为 utf8mb4_general_ci 支持完整Unicode字符（包括emoji）

2.数据规模实现：

学院: 3

专业: 9 (3学院×3专业)

班级: 81 (9专业×3年级×3班)

学生: 4,050 (81班×50人)

课程: 54 (9专业×6门课)

教师: 27 (3学院×9人)

授课关系: 162 (54课程×3教师)

3.管理系统的14类核心查询操作及其实现SQL：

（1）全表查询

#select * from tableName;

-- 所有学生信息

mysql> SELECT * FROM student;

+------+------------+-------------+----------+

+------+------------+-------------+----------+

| 1 | S001050 | 学生1-50 | 1 |

| 2 | S001049 | 学生1-49 | 1 |

| 3 | S001048 | 学生1-48 | 1 |

......

| 4048 | S081003 | 学生81-3 | 81 |

| 4049 | S081002 | 学生81-2 | 81 |

| 4050 | S081001 | 学生81-1 | 81 |

+------+------------+-------------+----------+

4050 rows in set (0.00 sec)

-- 所有课程信息

mysql> SELECT * FROM course;

+----+-----------------------+--------+----------+

+----+-----------------------+--------+----------+

| 1 | Java程序设计 | 3 | 1 |

| 2 | 数据库系统 | 4 | 1 |

......

| 53 | 模具设计 | 4 | 9 |

| 54 | 3D打印技术 | 4 | 9 |

+----+-----------------------+--------+----------+

54 rows in set (0.00 sec)

-- 查询所有学院信息

mysql> SELECT * FROM college;

+----+--------------------------------+

| id | name |

+----+--------------------------------+

| 1 | 计算机科学与技术学院 |

| 2 | 经济管理学院 |

| 3 | 机械工程学院 |

+----+--------------------------------+

3 rows in set (0.00 sec)

（2）去重查询

#distinct

-- 查询所有不同专业名称

mysql> SELECT DISTINCT name FROM major;

+--------------------+

| name |

+--------------------+

| 软件工程 |

| 人工智能 |

| 网络安全 |

| 金融学 |

| 会计学 |

| 市场营销 |

| 机械设计制造 |

| 车辆工程 |

| 材料成型 |

+--------------------+

9 rows in set (0.00 sec)

-- 查询不同年级列表

mysql> SELECT DISTINCT grade FROM class;

+-------+

| grade |

+-------+

| 1 |

| 2 |

| 3 |

+-------+

3 rows in set (0.00 sec)

（3）统计查询，非空数据

#count()

-- 统计非空教师信息数量

mysql> SELECT COUNT(*) AS teacher_count

FROM teacher

WHERE name IS NOT NULL AND teacher_id IS NOT NULL;

+---------------+

| teacher_count |

+---------------+

| 27 |

+---------------+

1 row in set (0.01 sec)

-- 统计有学号的学生数量

mysql> SELECT COUNT(*) AS valid_student_count

FROM student

WHERE student_id <> '';

+---------------------+

| valid_student_count |

+---------------------+

| 4050 |

+---------------------+

1 row in set (0.01 sec)

（4）重新定义查询结果表中的列名称

#as

-- 重命名列展示学生信息

mysql> SELECT

student_id AS 学号,

name AS 姓名,

class_id AS 班级ID

FROM student

LIMIT 5;

+---------+------------+----------+

| 学号 | 姓名 | 班级ID |

+---------+------------+----------+

| S001050 | 学生1-50 | 1 |

| S001049 | 学生1-49 | 1 |

| S001048 | 学生1-48 | 1 |

| S001047 | 学生1-47 | 1 |

| S001046 | 学生1-46 | 1 |

+---------+------------+----------+

5 rows in set (0.00 sec)

-- 重命名学院信息列

mysql> SELECT

id AS 学院编号,

name AS 学院名称

FROM college;

+--------------+--------------------------------+

| 学院编号 | 学院名称 |

+--------------+--------------------------------+

| 1 | 计算机科学与技术学院 |

| 2 | 经济管理学院 |

| 3 | 机械工程学院 |

+--------------+--------------------------------+

3 rows in set (0.00 sec)

（5）聚合函数查询

#sum()

-- 计算课程平均学分

mysql> SELECT AVG(credit) AS avg_credit FROM course;

+--------------------+

| avg_credit |

+--------------------+

| 3.5833333333333335 |

+--------------------+

1 row in set (0.00 sec)

-- 查询最高学分和最低学分

mysql> SELECT

MAX(credit) AS max_credit,

MIN(credit) AS min_credit

FROM course;

+------------+------------+

| max_credit | min_credit |

+------------+------------+

| 4.5 | 2.5 |

+------------+------------+

1 row in set (0.00 sec)

（6）分组查询

#group by

-- 统计各学院教师数量

mysql> SELECT

c.name AS 学院名称,

COUNT(t.id) AS 教师数量

FROM college c

LEFT JOIN teacher t ON c.id = t.college_id

GROUP BY c.id;

+--------------------------------+--------------+

| 学院名称 | 教师数量 |

+--------------------------------+--------------+

| 计算机科学与技术学院 | 9 |

| 经济管理学院 | 9 |

| 机械工程学院 | 9 |

+--------------------------------+--------------+

3 rows in set (0.00 sec)

-- 统计每个学院专业数量

mysql> SELECT

c.name AS 学院名称,

COUNT(m.id) AS 专业数量

FROM college c

LEFT JOIN major m ON c.id = m.college_id

GROUP BY c.id;

+--------------------------------+--------------+

| 学院名称 | 专业数量 |

+--------------------------------+--------------+

| 计算机科学与技术学院 | 3 |

| 经济管理学院 | 3 |

| 机械工程学院 | 3 |

+--------------------------------+--------------+

3 rows in set (0.00 sec)

-- 统计每个学院年级数量

mysql> SELECT

c.name AS 学院名称,

COUNT(DISTINCT cl.grade) AS 年级数量

FROM college c

JOIN major m ON c.id = m.college_id

JOIN class cl ON m.id = cl.major_id

GROUP BY c.id;

+--------------------------------+--------------+

| 学院名称 | 年级数量 |

+--------------------------------+--------------+

| 计算机科学与技术学院 | 3 |

| 经济管理学院 | 3 |

| 机械工程学院 | 3 |

+--------------------------------+--------------+

3 rows in set (0.00 sec)

-- 统计每个学院班级数量

mysql> SELECT

c.name AS 学院名称,

COUNT(cl.id) AS 班级数量

FROM college c

JOIN major m ON c.id = m.college_id

JOIN class cl ON m.id = cl.major_id

GROUP BY c.id;

+--------------------------------+--------------+

| 学院名称 | 班级数量 |

+--------------------------------+--------------+

| 计算机科学与技术学院 | 27 |

| 经济管理学院 | 27 |

| 机械工程学院 | 27 |

+--------------------------------+--------------+

3 rows in set (0.00 sec)

-- 查询每个学院的学生人数（使用GROUP BY）

mysql> SELECT c.name AS 学院名称, COUNT(s.id) AS 学生人数

FROM college c

JOIN major m ON c.id = m.college_id

JOIN class cl ON m.id = cl.major_id

JOIN student s ON cl.id = s.class_id

GROUP BY c.id;

+--------------------------------+--------------+

| 学院名称 | 学生人数 |

+--------------------------------+--------------+

| 计算机科学与技术学院 | 1350 |

| 经济管理学院 | 1350 |

| 机械工程学院 | 1350 |

+--------------------------------+--------------+

3 rows in set (0.00 sec)

-- 查询平均学分超过3.5的专业（使用HAVING）

mysql> SELECT m.name AS 专业名称, AVG(c.credit) AS 平均学分

FROM major m

JOIN course c ON m.id = c.major_id

GROUP BY m.id

HAVING AVG(c.credit) > 3.5;

+--------------------+--------------------+

| 专业名称 | 平均学分 |

+--------------------+--------------------+

| 人工智能 | 3.8333333333333335 |

| 机械设计制造 | 3.6666666666666665 |

| 车辆工程 | 3.9166666666666665 |

| 材料成型 | 3.8333333333333335 |

+--------------------+--------------------+

4 rows in set (0.00 sec)

-- 查询2021级（3年级）各专业平均成绩（使用GROUP BY）

mysql> SELECT m.name AS 专业名称, AVG(sc.grade) AS 平均成绩

FROM score sc

JOIN student s ON sc.student_id = s.id

JOIN class cl ON s.class_id = cl.id

JOIN major m ON cl.major_id = m.id

WHERE cl.grade = 3

GROUP BY m.id;

+--------------------+-------------------+

| 专业名称 | 平均成绩 |

+--------------------+-------------------+

| 软件工程 | 80.34662497024895 |

| 人工智能 | 80.73711007530075 |

| 网络安全 | 80.3958139700483 |

| 金融学 | 80.33328075589442 |

| 会计学 | 79.82463297758368 |

| 市场营销 | 80.11517571031857 |

| 机械设计制造 | 79.64532925779568 |

| 车辆工程 | 80.22602530157529 |

| 材料成型 | 79.74545451138388 |

+--------------------+-------------------+

9 rows in set (0.00 sec)

-- 查询平均分高于80的班级（使用HAVING）

mysql> SELECT cl.name AS 班级名称, AVG(sc.grade) AS 平均分

FROM score sc

JOIN student s ON sc.student_id = s.id

JOIN class cl ON s.class_id = cl.id

GROUP BY cl.id

HAVING AVG(sc.grade) > 80;

+------------------------+-------------------+

| 班级名称 | 平均分 |

+------------------------+-------------------+

| 软件工程-1-1 | 80.92850680804361 |

| 软件工程-2-2 | 80.50913245492875 |

| 软件工程-3-3 | 81.3000002742363 |

| 人工智能-1-3 | 80.01069777732671 |

| 人工智能-2-1 | 80.9174527402194 |

| 人工智能-2-2 | 80.10204104987942 |

| 人工智能-3-1 | 80.73098591907484 |

| 人工智能-3-2 | 80.03819131132346 |

| 人工智能-3-3 | 81.44974601087232 |

| 网络安全-1-1 | 80.39540800756338 |

| 网络安全-1-2 | 81.02666673206147 |

| 网络安全-1-3 | 80.65047201120629 |

| 网络安全-2-1 | 80.55399986267089 |

| 网络安全-2-3 | 80.89642839772361 |

| 网络安全-3-1 | 81.19374999633202 |

| 网络安全-3-3 | 80.68238109406971 |

| 金融学-1-2 | 80.70194174016564 |

| 金融学-1-3 | 80.20092598597209 |

| 金融学-2-1 | 80.80426535222203 |

| 金融学-2-2 | 80.12923077314328 |

| 金融学-2-3 | 80.24114860644181 |

| 金融学-3-2 | 80.81849966049194 |

| 金融学-3-3 | 80.61466681586371 |

| 会计学-1-2 | 80.94259260318897 |

| 会计学-1-3 | 80.23101857856467 |

| 会计学-2-1 | 80.48059700733394 |

| 会计学-2-2 | 81.92361135836 |

| 会计学-2-3 | 81.44999979943344 |

| 会计学-3-2 | 80.75373113451906 |

| 市场营销-2-3 | 81.20507611114967 |

| 市场营销-3-3 | 80.86009170812204 |

| 机械设计制造-2-1 | 80.03623177348703 |

| 机械设计制造-2-2 | 80.17033521296305 |

| 机械设计制造-2-3 | 81.1699504946253 |

| 机械设计制造-3-1 | 80.08497646492971 |

| 车辆工程-2-2 | 80.78142845517112 |

| 车辆工程-2-3 | 80.21034490651098 |

| 车辆工程-3-2 | 80.18365375812238 |

| 车辆工程-3-3 | 80.85887860806189 |

| 材料成型-1-2 | 80.3454545091241 |

| 材料成型-2-2 | 80.90187792710854 |

| 材料成型-2-3 | 80.52870381319964 |

| 材料成型-3-3 | 80.09400913572532 |

+------------------------+-------------------+

43 rows in set (0.01 sec)

-- 查询各学院教师授课课程数量（使用GROUP BY）

mysql> SELECT c.name AS 学院名称, COUNT(te.course_id) AS 授课课程数

FROM teacher t

JOIN college c ON t.college_id = c.id

JOIN teaching te ON t.id = te.teacher_id

GROUP BY c.id;

+--------------------------------+-----------------+

| 学院名称 | 授课课程数 |

+--------------------------------+-----------------+

| 计算机科学与技术学院 | 54 |

| 经济管理学院 | 54 |

| 机械工程学院 | 54 |

+--------------------------------+-----------------+

3 rows in set (0.00 sec)

-- 查询2023级会计学每个班平均分（使用多表JOIN）

mysql> SELECT cl.name AS 班级名称, AVG(sc.grade) AS 平均分

FROM score sc

JOIN student s ON sc.student_id = s.id

JOIN class cl ON s.class_id = cl.id

JOIN major m ON cl.major_id = m.id

WHERE m.name = '会计学' AND cl.grade = 1

GROUP BY cl.id;

+---------------+-------------------+

| 班级名称 | 平均分 |

+---------------+-------------------+

| 会计学-1-1 | 79.34975133487835 |

| 会计学-1-2 | 80.94259260318897 |

| 会计学-1-3 | 80.23101857856467 |

+---------------+-------------------+

3 rows in set (0.00 sec)

-- 查询机械工程学院2023级总分第一名（使用JOIN和分组）

mysql> SELECT s.name AS 学生姓名, SUM(sc.grade) AS 总分

FROM score sc

JOIN student s ON sc.student_id = s.id

JOIN class cl ON s.class_id = cl.id

JOIN major m ON cl.major_id = m.id

JOIN college c ON m.college_id = c.id

WHERE c.name = '机械工程学院' AND cl.grade = 1

GROUP BY s.id

ORDER BY 总分 DESC

LIMIT 1;

+--------------+-------------------+

| 学生姓名 | 总分 |

+--------------+-------------------+

| 学生65-35 | 538.4000015258789 |

+--------------+-------------------+

1 row in set (0.00 sec)

-- 查询各年级学生选课数量（使用GROUP BY多个字段）

mysql> SELECT cl.grade AS 年级, COUNT(sc.course_id) AS 选课总数

FROM score sc

JOIN student s ON sc.student_id = s.id

JOIN class cl ON s.class_id = cl.id

GROUP BY cl.grade;

+--------+--------------+

| 年级 | 选课总数 |

+--------+--------------+

| 1 | 5692 |

| 2 | 5577 |

| 3 | 5678 |

+--------+--------------+

3 rows in set (0.01 sec)

-- 验证学院平均分计算

mysql> SELECT

c.name AS 学院,

AVG(sc.grade) AS 平均成绩

FROM score sc

JOIN student s ON sc.student_id = s.id

JOIN class cl ON s.class_id = cl.id

JOIN major m ON cl.major_id = m.id

JOIN college c ON m.college_id = c.id

GROUP BY c.id;

+--------------------------------+-------------------+

| 学院 | 平均成绩 |

+--------------------------------+-------------------+

| 计算机科学与技术学院 | 80.26949365078221 |

| 经济管理学院 | 80.11942959953757 |

| 机械工程学院 | 79.91787660719116 |

+--------------------------------+-------------------+

3 rows in set (0.01 sec)

-- 验证学生平均分

mysql> SELECT

s.name AS 学生姓名,

AVG(sc.grade) AS 平均成绩

FROM student s

JOIN score sc ON s.id = sc.student_id

GROUP BY s.id

ORDER BY 平均成绩 DESC

LIMIT 10;

+--------------+-------------------+

| 学生姓名 | 平均成绩 |

+--------------+-------------------+

| 学生31-3 | 99.9000015258789 |

| 学生6-17 | 99.80000305175781 |

| 学生25-12 | 99.25 |

| 学生11-18 | 99 |

| 学生70-9 | 98.6999994913737 |

| 学生61-27 | 98.33333333333333 |

| 学生32-28 | 98 |

| 学生23-37 | 97.8499984741211 |

| 学生76-1 | 97.55000305175781 |

| 学生22-42 | 97.5 |

+--------------+-------------------+

10 rows in set (0.01 sec)

-- 2021级计算机科学与技术学院成绩最高分姓名

mysql> SELECT s.name AS 学生姓名, sc.grade AS 最高分

FROM score sc

JOIN student s ON sc.student_id = s.id

JOIN class cl ON s.class_id = cl.id

JOIN major m ON cl.major_id = m.id

JOIN college c ON m.college_id = c.id

WHERE c.name = '计算机科学与技术学院' -- 使用学院名称

AND cl.grade = 3 -- 2021级对应3年级

ORDER BY sc.grade DESC

LIMIT 1;

+--------------+-----------+

| 学生姓名 | 最高分 |

+--------------+-----------+

| 学生7-34 | 100 |

+--------------+-----------+

1 row in set (0.00 sec)

-- 2021级计算机科学与技术学院各个班的平均分

mysql> SELECT

cl.name AS 班级名称,

AVG(sc.grade) AS 平均分

FROM score sc

JOIN student s ON sc.student_id = s.id

JOIN class cl ON s.class_id = cl.id

JOIN major m ON cl.major_id = m.id

JOIN college c ON m.college_id = c.id

WHERE c.name = '计算机科学与技术学院'

AND cl.grade = 3

GROUP BY cl.id;

+------------------+-------------------+

| 班级名称 | 平均分 |

+------------------+-------------------+

| 软件工程-3-1 | 79.78405802261426 |

| 软件工程-3-2 | 79.92206589604767 |

| 软件工程-3-3 | 81.3000002742363 |

| 人工智能-3-1 | 80.73098591907484 |

| 人工智能-3-2 | 80.03819131132346 |

| 人工智能-3-3 | 81.44974601087232 |

| 网络安全-3-1 | 81.19374999633202 |

| 网络安全-3-2 | 79.39955939070244 |

| 网络安全-3-3 | 80.68238109406971 |

+------------------+-------------------+

9 rows in set (0.00 sec)

-- 2023级会计学每个班总平均分

mysql> SELECT

cl.name AS 班级名称,

AVG(sc.grade) AS 总平均分

FROM score sc

JOIN student s ON sc.student_id = s.id

JOIN class cl ON s.class_id = cl.id

JOIN major m ON cl.major_id = m.id

WHERE m.name = '会计学' -- 会计学专业存在

AND cl.grade = 1 -- 2023级对应1年级

GROUP BY cl.id;

+---------------+-------------------+

| 班级名称 | 总平均分 |

+---------------+-------------------+

| 会计学-1-1 | 79.34975133487835 |

| 会计学-1-2 | 80.94259260318897 |

| 会计学-1-3 | 80.23101857856467 |

+---------------+-------------------+

3 rows in set (0.00 sec)

-- 机械工程学院2023级总分第一名

mysql> SELECT

s.name AS 学生姓名,

SUM(sc.grade) AS 总分

FROM score sc

JOIN student s ON sc.student_id = s.id

JOIN class cl ON s.class_id = cl.id

JOIN major m ON cl.major_id = m.id

JOIN college c ON m.college_id = c.id

WHERE c.name = '机械工程学院'

AND cl.grade = 1 -- 2023级对应1年级

GROUP BY s.id

ORDER BY 总分 DESC

LIMIT 1;

+--------------+-------------------+

| 学生姓名 | 总分 |

+--------------+-------------------+

| 学生65-35 | 538.4000015258789 |

+--------------+-------------------+

1 row in set (0.00 sec)

（7）排序查询

#order by

-- 按学分降序排列课程

mysql> SELECT name, credit

FROM course

ORDER BY credit DESC;

+-----------------------+--------+

| name | credit |

+-----------------------+--------+

| 发动机原理 | 4.5 |

| 深度学习 | 4.5 |

| 机械设计 | 4.5 |

| 汽车理论 | 4 |

| 国际金融 | 4 |

| 金融工程 | 4 |

| 财务会计 | 4 |

| 数字营销 | 4 |

| 机械原理 | 4 |

| 制造技术 | 4 |

| 汽车构造 | 4 |

| 数据库系统 | 4 |

| 汽车设计 | 4 |

| 材料科学基础 | 4 |

| 材料成型原理 | 4 |

| 模具设计 | 4 |

| 3D打印技术 | 4 |

| 区块链技术 | 4 |

| 神经网络 | 4 |

| 计算机视觉 | 4 |

| 密码学 | 4 |

| 算法设计与分析 | 4 |

| 机器学习 | 4 |

| CAD/CAM | 3.5 |

| 网络攻防技术 | 3.5 |

| 国际市场营销 | 3.5 |

| 自然语言处理 | 3.5 |

| 消费者行为学 | 3.5 |

| 汽车电子 | 3.5 |

| 财务报表分析 | 3.5 |

| 税法 | 3.5 |

| 新能源汽车 | 3.5 |

| 管理会计 | 3.5 |

| 操作系统安全 | 3.5 |

| 塑性成型 | 3.5 |

| 焊接技术 | 3.5 |

| Web开发技术 | 3.5 |

| 投资学 | 3.5 |

| 移动应用开发 | 3 |

| 机械振动 | 3 |

| Java程序设计 | 3 |

| 工程材料 | 3 |

| 智能机器人 | 3 |

| 品牌管理 | 3 |

| 市场调研 | 3 |

| 市场营销学 | 3 |

| 成本会计 | 3 |

| 审计学 | 3 |

| 信息安全 | 3 |

| 证券分析 | 3 |

| 商业银行管理 | 3 |

| 数据安全 | 3 |

| 金融学原理 | 3 |

| 软件测试 | 2.5 |

+-----------------------+--------+

54 rows in set (0.00 sec)

-- 按学生ID升序排列

mysql> SELECT student_id, name

FROM student

ORDER BY student_id ASC

LIMIT 10;

+------------+------------+

| student_id | name |

+------------+------------+

| S001001 | 学生1-1 |

| S001002 | 学生1-2 |

| S001003 | 学生1-3 |

| S001004 | 学生1-4 |

| S001005 | 学生1-5 |

| S001006 | 学生1-6 |

| S001007 | 学生1-7 |

| S001008 | 学生1-8 |

| S001009 | 学生1-9 |

| S001010 | 学生1-10 |

+------------+------------+

10 rows in set (0.00 sec)

（8）分页查询

#limit number

-- 分页查询学生信息（每页50条）

-- 第一页

mysql> SELECT * FROM student ORDER BY id LIMIT 0, 50;

+----+------------+------------+----------+

+----+------------+------------+----------+

| 1 | S001050 | 学生1-50 | 1 |

| 2 | S001049 | 学生1-49 | 1 |

| 3 | S001048 | 学生1-48 | 1 |

| 4 | S001047 | 学生1-47 | 1 |

| 5 | S001046 | 学生1-46 | 1 |

| 6 | S001045 | 学生1-45 | 1 |

| 7 | S001044 | 学生1-44 | 1 |

| 8 | S001043 | 学生1-43 | 1 |

| 9 | S001042 | 学生1-42 | 1 |

........

| 44 | S001007 | 学生1-7 | 1 |

| 45 | S001006 | 学生1-6 | 1 |

| 46 | S001005 | 学生1-5 | 1 |

| 47 | S001004 | 学生1-4 | 1 |

| 48 | S001003 | 学生1-3 | 1 |

| 49 | S001002 | 学生1-2 | 1 |

| 50 | S001001 | 学生1-1 | 1 |

+----+------------+------------+----------+

50 rows in set (0.00 sec)

-- 第二页

mysql> SELECT * FROM student ORDER BY id LIMIT 50, 50;

+-----+------------+------------+----------+

+-----+------------+------------+----------+

| 51 | S002050 | 学生2-50 | 2 |

| 52 | S002049 | 学生2-49 | 2 |

| 53 | S002048 | 学生2-48 | 2 |

| 54 | S002047 | 学生2-47 | 2 |

| 55 | S002046 | 学生2-46 | 2 |

| 56 | S002045 | 学生2-45 | 2 |

| 57 | S002044 | 学生2-44 | 2 |

| 58 | S002043 | 学生2-43 | 2 |

| 59 | S002042 | 学生2-42 | 2 |

| 60 | S002041 | 学生2-41 | 2 |

........

| 91 | S002010 | 学生2-10 | 2 |

| 92 | S002009 | 学生2-9 | 2 |

| 93 | S002008 | 学生2-8 | 2 |

| 94 | S002007 | 学生2-7 | 2 |

| 95 | S002006 | 学生2-6 | 2 |

| 96 | S002005 | 学生2-5 | 2 |

| 97 | S002004 | 学生2-4 | 2 |

| 98 | S002003 | 学生2-3 | 2 |

| 99 | S002002 | 学生2-2 | 2 |

| 100 | S002001 | 学生2-1 | 2 |

+-----+------------+------------+----------+

50 rows in set (0.00 sec)

（9）子查询

#subquery(嵌套查询（Nested Query）或内部查询（Inner Query）)

-- 查询软件工程专业的所有课程

mysql> SELECT name, credit

FROM course

WHERE major_id = (

SELECT id FROM major WHERE name = '软件工程'

);

+-----------------------+--------+

| name | credit |

+-----------------------+--------+

| Java程序设计 | 3 |

| 数据库系统 | 4 |

| Web开发技术 | 3.5 |

| 软件测试 | 2.5 |

| 算法设计与分析 | 4 |

| 移动应用开发 | 3 |

+-----------------------+--------+

6 rows in set (0.00 sec)

-- 查询教师最多的学院

mysql> SELECT name

FROM college

WHERE id = (

SELECT college_id

FROM teacher

GROUP BY college_id

ORDER BY COUNT(*) DESC

LIMIT 1

);

+--------------------------------+

| name |

+--------------------------------+

| 计算机科学与技术学院 |

+--------------------------------+

1 row in set (0.00 sec)

-- 查询高于专业平均分的学生（使用子查询）

SELECT s.name AS 学生姓名, sc.grade AS 成绩, c.name AS 课程

FROM score sc

JOIN student s ON sc.student_id = s.id

JOIN course c ON sc.course_id = c.id

WHERE sc.grade > (

SELECT AVG(sc2.grade) -- 明确指定 sc2.grade

FROM score sc2

JOIN student s2 ON sc2.student_id = s2.id

JOIN class cl2 ON s2.class_id = cl2.id

JOIN major m2 ON cl2.major_id = m2.id

WHERE m2.id = (

SELECT major_id FROM class WHERE id = s.class_id

)

);

+--------------+--------+-----------------------+

| 学生姓名 | 成绩 | 课程 |

+--------------+--------+-----------------------+

| 学生1-50 | 93 | Java程序设计 |

| 学生1-50 | 84.4 | 数据库系统 |

| 学生1-50 | 94.8 | 软件测试 |

| 学生1-49 | 81.1 | Java程序设计 |

| 学生1-49 | 83.3 | 移动应用开发 |

.....

| 学生81-2 | 96.4 | 3D打印技术 |

| 学生81-1 | 91.7 | 材料科学基础 |

| 学生81-1 | 89.8 | 焊接技术 |

| 学生81-1 | 88.8 | 模具设计 |

+--------------+--------+-----------------------+

8473 rows in set (12.13 sec)

-- 查询没有成绩记录的学生（使用NOT EXISTS）

mysql> SELECT s.name AS 学生姓名

FROM student s

WHERE NOT EXISTS (

SELECT 1 FROM score sc WHERE sc.student_id = s.id

);

+--------------+

| 学生姓名 |

+--------------+

| 学生20-12 |

| 学生29-26 |

| 学生39-23 |

| 学生47-37 |

| 学生58-36 |

+--------------+

5 rows in set (0.00 sec)

-- 查询成绩排名前三的学生（使用子查询）

mysql> SELECT s.name AS 学生姓名, SUM(sc.grade) AS 总分

FROM student s

JOIN score sc ON s.id = sc.student_id

GROUP BY s.id

ORDER BY 总分 DESC

LIMIT 3;

+--------------+-------------------+

| 学生姓名 | 总分 |

+--------------+-------------------+

| 学生27-9 | 566.8000030517578 |

| 学生70-22 | 563 |

| 学生5-18 | 547.6999969482422 |

+--------------+-------------------+

3 rows in set (0.01 sec)

-- 查询平均成绩高于学院平均的学生（使用HAVING和子查询）

-- 步骤1：首先创建学院平均成绩视图

CREATE VIEW college_average AS

SELECT

m.college_id,

AVG(sc.grade) AS avg_grade

FROM score sc

JOIN student s ON sc.student_id = s.id

JOIN class cl ON s.class_id = cl.id

JOIN major m ON cl.major_id = m.id

GROUP BY m.college_id;

-- 步骤2：查询高于学院平均分的学生

mysql> SELECT

s.name AS 学生姓名,

AVG(sc.grade) AS 平均成绩,

ca.avg_grade AS 学院平均

FROM student s

JOIN score sc ON s.id = sc.student_id

JOIN class cl ON s.class_id = cl.id

JOIN major m ON cl.major_id = m.id

JOIN college_average ca ON m.college_id = ca.college_id

GROUP BY s.id

HAVING AVG(sc.grade) > ca.avg_grade;

+--------------+-------------------+-------------------+

| 学生姓名 | 平均成绩 | 学院平均 |

+--------------+-------------------+-------------------+

| 学生1-50 | 84.2750015258789 | 80.26949365078221 |

| 学生1-48 | 82.89999961853027 | 80.26949365078221 |

| 学生1-47 | 90.26666768391927 | 80.26949365078221 |

....

| 学生81-3 | 83.6500015258789 | 79.91787660719116 |

| 学生81-2 | 80.1833324432373 | 79.91787660719116 |

| 学生81-1 | 87.30000114440918 | 79.91787660719116 |

+--------------+-------------------+-------------------+

2021 rows in set (0.03 sec)

-- 验证学院平均分计算

mysql> SELECT

c.name AS 学院,

AVG(sc.grade) AS 平均成绩

FROM score sc

JOIN student s ON sc.student_id = s.id

JOIN class cl ON s.class_id = cl.id

JOIN major m ON cl.major_id = m.id

JOIN college c ON m.college_id = c.id

GROUP BY c.id;

+--------------------------------+-------------------+

| 学院 | 平均成绩 |

+--------------------------------+-------------------+

| 计算机科学与技术学院 | 80.26949365078221 |

| 经济管理学院 | 80.11942959953757 |

| 机械工程学院 | 79.91787660719116 |

+--------------------------------+-------------------+

3 rows in set (0.01 sec)

（10）union联合查询

#union

-- 合并查询计算机学院和机械学院的教师

mysql> SELECT name, '计算机学院' AS college FROM teacher WHERE college_id = 1

UNION ALL

SELECT name, '机械学院' AS college FROM teacher WHERE college_id = 3

ORDER BY name;

+--------------+-----------------+

| name | college |

+--------------+-----------------+

| 冯副教授 | 机械学院 |

| 刘副教授 | 计算机学院 |

| 吴讲师 | 计算机学院 |

| 周副教授 | 计算机学院 |

| 唐副教授 | 机械学院 |

| 宋教授 | 机械学院 |

| 张教授 | 计算机学院 |

| 彭副教授 | 机械学院 |

| 曹教授 | 机械学院 |

| 曾讲师 | 机械学院 |

| 李副教授 | 计算机学院 |

| 杨教授 | 计算机学院 |

| 王讲师 | 计算机学院 |

| 许讲师 | 机械学院 |

| 赵教授 | 计算机学院 |

| 邓讲师 | 机械学院 |

| 陈讲师 | 计算机学院 |

| 韩教授 | 机械学院 |

+--------------+-----------------+

18 rows in set (0.00 sec)

-- 合并查询计算机学院和机械学院的专业（使用UNION）

mysql> SELECT name AS 专业名称 FROM major

WHERE college_id = (SELECT id FROM college WHERE name = '计算机科学与技术学院')

UNION

SELECT name AS 专业名称 FROM major

WHERE college_id = (SELECT id FROM college WHERE name = '机械工程学院');

+--------------------+

| 专业名称 |

+--------------------+

| 软件工程 |

| 人工智能 |

| 网络安全 |

| 机械设计制造 |

| 车辆工程 |

| 材料成型 |

+--------------------+

6 rows in set (0.00 sec)

-- 合并查询所有学院和专业信息（使用UNION ALL）

mysql> SELECT name AS 名称, '学院' AS 类型 FROM college

UNION ALL

SELECT name AS 名称, '专业' AS 类型 FROM major;

+--------------------------------+--------+

| 名称 | 类型 |

+--------------------------------+--------+

| 计算机科学与技术学院 | 学院 |

| 经济管理学院 | 学院 |

| 机械工程学院 | 学院 |

| 软件工程 | 专业 |

| 人工智能 | 专业 |

| 网络安全 | 专业 |

| 金融学 | 专业 |

| 会计学 | 专业 |

| 市场营销 | 专业 |

| 机械设计制造 | 专业 |

| 车辆工程 | 专业 |

| 材料成型 | 专业 |

+--------------------------------+--------+

12 rows in set (0.00 sec)

（11）内连接查询

#INNER JOIN

-- 查询学生及其班级信息

mysql> SELECT

s.student_id,

s.name AS student_name,

c.name AS class_name

FROM student s

INNER JOIN class c ON s.class_id = c.id

LIMIT 10;

+------------+--------------+------------------+

| student_id | student_name | class_name |

+------------+--------------+------------------+

| S001050 | 学生1-50 | 软件工程-1-1 |

| S001049 | 学生1-49 | 软件工程-1-1 |

| S001048 | 学生1-48 | 软件工程-1-1 |

| S001047 | 学生1-47 | 软件工程-1-1 |

| S001046 | 学生1-46 | 软件工程-1-1 |

| S001045 | 学生1-45 | 软件工程-1-1 |

| S001044 | 学生1-44 | 软件工程-1-1 |

| S001043 | 学生1-43 | 软件工程-1-1 |

| S001042 | 学生1-42 | 软件工程-1-1 |

| S001041 | 学生1-41 | 软件工程-1-1 |

+------------+--------------+------------------+

10 rows in set (0.00 sec)

-- 查询课程及其所属专业

mysql> SELECT

course.name AS course_name,

major.name AS major_name

FROM course

INNER JOIN major ON course.major_id = major.id;

+-----------------------+--------------------+

| course_name | major_name |

+-----------------------+--------------------+

| Java程序设计 | 软件工程 |

| 数据库系统 | 软件工程 |

| Web开发技术 | 软件工程 |

| 软件测试 | 软件工程 |

| 算法设计与分析 | 软件工程 |

| 移动应用开发 | 软件工程 |

| 机器学习 | 人工智能 |

| 深度学习 | 人工智能 |

| 自然语言处理 | 人工智能 |

| 计算机视觉 | 人工智能 |

| 神经网络 | 人工智能 |

| 智能机器人 | 人工智能 |

| 网络攻防技术 | 网络安全 |

| 密码学 | 网络安全 |

| 信息安全 | 网络安全 |

| 操作系统安全 | 网络安全 |

| 数据安全 | 网络安全 |

| 区块链技术 | 网络安全 |

| 金融学原理 | 金融学 |

| 投资学 | 金融学 |

| 国际金融 | 金融学 |

| 商业银行管理 | 金融学 |

| 证券分析 | 金融学 |

| 金融工程 | 金融学 |

| 财务会计 | 会计学 |

| 管理会计 | 会计学 |

| 审计学 | 会计学 |

| 税法 | 会计学 |

| 成本会计 | 会计学 |

| 财务报表分析 | 会计学 |

| 市场营销学 | 市场营销 |

| 消费者行为学 | 市场营销 |

| 市场调研 | 市场营销 |

| 品牌管理 | 市场营销 |

| 数字营销 | 市场营销 |

| 国际市场营销 | 市场营销 |

| 机械原理 | 机械设计制造 |

| 机械设计 | 机械设计制造 |

| 工程材料 | 机械设计制造 |

| 制造技术 | 机械设计制造 |

| CAD/CAM | 机械设计制造 |

| 机械振动 | 机械设计制造 |

| 汽车构造 | 车辆工程 |

| 汽车理论 | 车辆工程 |

| 发动机原理 | 车辆工程 |

| 汽车电子 | 车辆工程 |

| 汽车设计 | 车辆工程 |

| 新能源汽车 | 车辆工程 |

| 材料科学基础 | 材料成型 |

| 材料成型原理 | 材料成型 |

| 塑性成型 | 材料成型 |

| 焊接技术 | 材料成型 |

| 模具设计 | 材料成型 |

| 3D打印技术 | 材料成型 |

+-----------------------+--------------------+

54 rows in set (0.00 sec)

-- 查询所有学生及其班级信息（使用INNER JOIN）

mysql> SELECT s.name AS 学生姓名, cl.name AS 班级名称

FROM student s

JOIN class cl ON s.class_id = cl.id;

+--------------+------------------------+

| 学生姓名 | 班级名称 |

+--------------+------------------------+

| 学生1-50 | 软件工程-1-1 |

| 学生1-49 | 软件工程-1-1 |

.......

| 学生66-32 | 车辆工程-1-3 |

| 学生66-31 | 车辆工程-1-3 |

....

| 学生81-2 | 材料成型-3-3 |

| 学生81-1 | 材料成型-3-3 |

+--------------+------------------------+

4050 rows in set (0.00 sec)

-- 查询计算机学院所有专业课程（使用INNER JOIN）

mysql> SELECT m.name AS 专业名称, c.name AS 课程名称

FROM college col

JOIN major m ON col.id = m.college_id

JOIN course c ON m.id = c.major_id

WHERE col.name = '计算机科学与技术学院';

+--------------+-----------------------+

| 专业名称 | 课程名称 |

+--------------+-----------------------+

| 软件工程 | Java程序设计 |

| 软件工程 | 数据库系统 |

| 软件工程 | Web开发技术 |

| 软件工程 | 软件测试 |

| 软件工程 | 算法设计与分析 |

| 软件工程 | 移动应用开发 |

| 人工智能 | 机器学习 |

| 人工智能 | 深度学习 |

| 人工智能 | 自然语言处理 |

| 人工智能 | 计算机视觉 |

| 人工智能 | 神经网络 |

| 人工智能 | 智能机器人 |

| 网络安全 | 网络攻防技术 |

| 网络安全 | 密码学 |

| 网络安全 | 信息安全 |

| 网络安全 | 操作系统安全 |

| 网络安全 | 数据安全 |

| 网络安全 | 区块链技术 |

+--------------+-----------------------+

18 rows in set (0.00 sec)

-- 查询学生所选课程及成绩（使用LEFT JOIN）

mysql> SELECT s.name AS 学生姓名, c.name AS 课程名称, sc.grade AS 成绩

FROM student s

LEFT JOIN score sc ON s.id = sc.student_id

LEFT JOIN course c ON sc.course_id = c.id;

+--------------+-----------------------+--------+

| 学生姓名 | 课程名称 | 成绩 |

+--------------+-----------------------+--------+

| 学生1-50 | Java程序设计 | 93 |

| 学生1-50 | 数据库系统 | 84.4 |

| 学生1-50 | 软件测试 | 94.8 |

| 学生1-50 | 算法设计与分析 | 64.9 |

| 学生1-49 | Java程序设计 | 81.1 |

| 学生1-49 | Web开发技术 | 73.8 |

| 学生1-49 | 软件测试 | 67.7 |

.....

| 学生81-2 | 模具设计 | 95.2 |

| 学生81-2 | 3D打印技术 | 96.4 |

| 学生81-1 | 材料科学基础 | 91.7 |

| 学生81-1 | 焊接技术 | 89.8 |

| 学生81-1 | 模具设计 | 88.8 |

| 学生81-1 | 3D打印技术 | 78.9 |

+--------------+-----------------------+--------+

16952 rows in set (0.02 sec)

（12）外连接查询

①左外连接

#left join

-- 左外连接：所有班级及学生（包括无学生的班级）

mysql> SELECT

c.name AS class_name,

s.student_id,

s.name AS student_name

FROM class c

LEFT JOIN student s ON c.id = s.class_id

ORDER BY c.id

LIMIT 10;

+------------------+------------+--------------+

| class_name | student_id | student_name |

+------------------+------------+--------------+

| 软件工程-1-1 | S001050 | 学生1-50 |

| 软件工程-1-1 | S001049 | 学生1-49 |

| 软件工程-1-1 | S001048 | 学生1-48 |

| 软件工程-1-1 | S001047 | 学生1-47 |

| 软件工程-1-1 | S001046 | 学生1-46 |

| 软件工程-1-1 | S001045 | 学生1-45 |

| 软件工程-1-1 | S001044 | 学生1-44 |

| 软件工程-1-1 | S001043 | 学生1-43 |

| 软件工程-1-1 | S001042 | 学生1-42 |

| 软件工程-1-1 | S001041 | 学生1-41 |

+------------------+------------+--------------+

10 rows in set (0.00 sec)

-- 查询所有课程及授课教师（使用LEFT JOIN）

mysql> SELECT c.name AS 课程名称, t.name AS 教师姓名

FROM course c

LEFT JOIN teaching te ON c.id = te.course_id

LEFT JOIN teacher t ON te.teacher_id = t.id;

+-----------------------+--------------+

| 课程名称 | 教师姓名 |

+-----------------------+--------------+

| Java程序设计 | 张教授 |

| Java程序设计 | 赵教授 |

| Java程序设计 | 陈讲师 |

| 数据库系统 | 王讲师 |

| 数据库系统 | 赵教授 |

.....

| 模具设计 | 宋教授 |

| 模具设计 | 许讲师 |

| 模具设计 | 曾讲师 |

| 3D打印技术 | 韩教授 |

| 3D打印技术 | 冯副教授 |

| 3D打印技术 | 曾讲师 |

+-----------------------+--------------+

162 rows in set (0.00 sec)

-- 查询没有授课的教师（使用LEFT JOIN）

mysql> SELECT t.name AS 教师姓名

FROM teacher t

LEFT JOIN teaching te ON t.id = te.teacher_id

WHERE te.teacher_id IS NULL;

Empty set (0.00 sec)

-- 查询没有学生的班级（使用LEFT JOIN）

mysql> SELECT cl.name AS 班级名称

FROM class cl

LEFT JOIN student s ON cl.id = s.class_id

WHERE s.id IS NULL;

Empty set (0.00 sec)

-- 查询所有学院及学生人数（包含无学生的学院）（使用LEFT JOIN）

mysql> SELECT c.name AS 学院名称, COUNT(s.id) AS 学生人数

FROM college c

LEFT JOIN major m ON c.id = m.college_id

LEFT JOIN class cl ON m.id = cl.major_id

LEFT JOIN student s ON cl.id = s.class_id

GROUP BY c.id;

+--------------------------------+--------------+

| 学院名称 | 学生人数 |

+--------------------------------+--------------+

| 计算机科学与技术学院 | 1350 |

| 经济管理学院 | 1350 |

| 机械工程学院 | 1350 |

+--------------------------------+--------------+

3 rows in set (0.00 sec)

②右外连接

#select A.name as nameA, A.school, B.name as nameB, B.job from B right join A on A.name=B.name;

-- 右外连接：所有教师及授课信息（包括无授课的教师）

mysql> SELECT

t.name AS teacher_name,

c.name AS course_name

FROM teaching te

RIGHT JOIN teacher t ON te.teacher_id = t.id

LEFT JOIN course c ON te.course_id = c.id

ORDER BY t.id;

+--------------+-----------------------+

| teacher_name | course_name |

+--------------+-----------------------+

| 张教授 | Java程序设计 |

| 张教授 | 软件测试 |

| 张教授 | 神经网络 |

| 张教授 | 网络攻防技术 |

| 张教授 | 数据安全 |

| 张教授 | 区块链技术 |

| 李副教授 | Web开发技术 |

| 李副教授 | 软件测试 |

.......

| 彭副教授 | 汽车设计 |

| 彭副教授 | 材料科学基础 |

| 曾讲师 | 机械原理 |

| 曾讲师 | 工程材料 |

| 曾讲师 | 机械振动 |

| 曾讲师 | 材料科学基础 |

| 曾讲师 | 模具设计 |

| 曾讲师 | 3D打印技术 |

+--------------+-----------------------+

162 rows in set (0.00 sec)

查询验证结果说明

查询类型验证要点预期结果全表查询数据完整性返回所有记录去重查询唯一值返回无重复记录统计查询非空数据计数返回有效数据数量列重命名列别名显示结果集显示自定义列名聚合函数统计计算准确性正确计算MAX/MIN/AVG/COUNT分组查询分组逻辑正确性按指定分组条件返回统计结果排序查询排序顺序准确性结果按指定顺序排列分页查询分页逻辑正确性正确返回指定范围的记录子查询嵌套查询正确性返回基于子查询条件的结果UNION查询结果集合并正确性合并多个查询结果内连接关联数据匹配只返回匹配的记录外连接包含非匹配记录左表/右表所有记录+匹配记录

八、mysqld用户权限管理

MySQL数据库权限分类

PrivilegeContextCommentAlterTablesTo alter the tableAlter routineFunctions,ProceduresTo alter or drop stored functions/proceduresCreateDatabases,Tables,IndexesTo create new databases and tablesCreate routineDatabasesTo use CREATE FUNCTION/PROCEDURECreate temporary tablesDatabasesTo use CREATE TEMPORARY TABLECreate viewTablesTo create new viewsCreate userServer AdminTo create new usersDeleteTablesTo delete existing rowsDropDatabases,TablesTo drop databases, tables, and viewsEventServer AdminTo create, alter, drop and execute eventsExecuteFunctions,ProceduresTo execute stored routinesFileFile access on serverTo read and write files on the serverGrant optionDatabases,Tables,Functions,ProceduresTo give to other users those privileges you possessIndexTablesTo create or drop indexesInsertTablesTo insert data into tablesLock tablesDatabasesTo use LOCK TABLES (together with SELECT privilege)ProcessServer AdminTo view the plain text of currently executing queriesProxyServer AdminTo make proxy user possibleReferencesDatabases,TablesTo have references on tablesReloadServer AdminTo reload or refresh tables, logs and privilegesReplication clientServer AdminTo ask where the slave or master servers areReplication slaveServer AdminTo read binary log events from the masterSelectTablesTo retrieve rows from tableShow databasesServer AdminTo see all databases with SHOW DATABASESShow viewTablesTo see views with SHOW CREATE VIEWShutdownServer AdminTo shut down the serverSuperServer AdminTo use KILL thread, SET GLOBAL, CHANGE MASTER, etc.TriggerTablesTo use triggersCreate tablespaceServer AdminTo create/alter/drop tablespacesUpdateTablesTo update existing rowsUsageServer AdminNo privileges - allow connect only

中文释义

在MySQL中，权限控制是通过Grant和Revoke命令来管理用户和角色的权限的。

- Alter：修改。允许用户修改数据库中的表结构。

- Alter routine：修改存储过程或函数。允许用户修改已创建的存储过程或函数。

- Create：创建。允许用户创建新的数据库对象，如表、视图、存储过程等。

- Create routine：创建存储过程或函数。允许用户创建新的存储过程或函数。

- Create temporary tables：创建临时表。允许用户创建临时表，这些表在会话结束时会自动删除。

- Create view：创建视图。允许用户创建新的视图。

- Create user：创建用户。允许用户创建新的数据库用户账户。

- Delete：删除。允许用户删除表中的数据。

- Drop：删除。允许用户删除数据库中的对象，如表、视图等。

- Event：事件。允许用户管理和修改事件调度器中的事件。

- Execute：执行。允许用户执行存储过程或函数。

- File：文件。允许用户访问数据库服务器的文件系统。

- Grant option：授予选项。允许用户将权限授予其他用户。

- Index：索引。允许用户在表上创建、修改或删除索引。

- Insert：插入。允许用户向表中插入数据。

- Lock tables：锁定表。允许用户锁定数据库中的表，以防止其他用户同时访问这些表。

- Process：进程。允许查看或杀死数据库服务器上的进程。

- Proxy：代理。允许用户作为代理登录，代表其他用户执行操作。

- References：引用。允许用户创建外键约束。

- Reload：重新加载。允许用户重新加载配置文件或刷新权限表。

- Replication client：复制客户端。允许用户从主服务器接收数据以进行复制。

- Replication slave：复制从机。允许用户作为数据复制的从服务器。

- Select：选择。允许用户查询表中的数据。

- Show databases：显示数据库。允许用户查看用的数据库列表。

- Show view：显示视图。允许用户查看数据库中的视图定义。

- Shutdown：关闭。允许用户关闭数据库服务器。

- Super：超级。允许用户所有的权限，并且可以不受限制地执行许多系统命令。

- Trigger：触发器。允许用户创建触发器，以在特定事件发生时自动执行代码。

- Create tablespace：创建表空间。允许用户创建新的表空间，用于存储数据库对象。

- Update：更新。允许用户更新表中的数据。

- Usage：使用。这个权限通常用于限制用户只能查看自己的权限，不能进行任何实际的数据库操作。

user_namehost_name说明ryan192.168.72.155ryan，只能从此ip连接ding192.168.72.%ding，从192.168.72子网中的任何主机yu%ryan，任何主机可连

创建远程管理用户

创建用户 CREATE USER '用户名'@'主机' IDENTIFIED BY '密码';

授权 GRANT 权限 ON 数据库.* TO '用户'@'主机';

-- 用户ryan：仅允许从 192.168.72.155 连接

CREATE USER 'ryan'@'192.168.72.155' IDENTIFIED BY '密码';

GRANT ALL PRIVILEGES ON *.* TO 'ryan'@'192.168.72.155' WITH GRANT OPTION;

-- 用户ding：允许从 192.168.72.0/24 网段连接

CREATE USER 'ding'@'192.168.72.%' IDENTIFIED BY '密码';

GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'ding'@'192.168.72.%';

-- 用户yu：允许从任意主机连接（注：说明中的'ryan'应为笔误）

CREATE USER 'yu'@'%' IDENTIFIED BY '密码';

GRANT USAGE ON *.* TO 'yu'@'%'; -- 默认无权限，需单独授权

创建远程web服务数据库的操作用户

刷新权限 FLUSH PRIVILEGES;

-- 创建仅允许本地和192.168.72.0网段连接的用户

CREATE USER 'web_user'@'localhost' IDENTIFIED BY 'web_password';

CREATE USER 'web_user'@'192.168.72.%' IDENTIFIED BY 'web_password';

-- 授权仅操作web_db数据库

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `web_db`.* TO 'web_user'@'localhost';

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `web_db`.* TO 'web_user'@'192.168.72.%';

FLUSH PRIVILEGES; -- 刷新权限

查看用户权限

-- 查看全局用户权限

SELECT * FROM mysql.user WHERE User IN ('ryan', 'ding', 'yu', 'web_user')\G

###验证

mysql> select * from mysql.user where user in ('ryan','ding','yu','web_user')\G

*************************** 1. row ***************************

Host: %

User: yu

Select_priv: N

Insert_priv: N

Update_priv: N

Delete_priv: N

Create_priv: N

Drop_priv: N

Reload_priv: N

Shutdown_priv: N

Process_priv: N

File_priv: N

Grant_priv: N

References_priv: N

Index_priv: N

Alter_priv: N

Show_db_priv: N

Super_priv: N

Create_tmp_table_priv: N

Lock_tables_priv: N

Execute_priv: N

Repl_slave_priv: N

Repl_client_priv: N

Create_view_priv: N

Show_view_priv: N

Create_routine_priv: N

Alter_routine_priv: N

Create_user_priv: N

Event_priv: N

Trigger_priv: N

Create_tablespace_priv: N

ssl_type:

ssl_cipher: 0x

x509_issuer: 0x

x509_subject: 0x

max_questions: 0

max_updates: 0

max_connections: 0

max_user_connections: 0

plugin: mysql_native_password

authentication_string: *AC241830FFDDC8943AB31CBD47D758E79F7953EA

password_expired: N

password_last_changed: 2025-07-02 17:27:27

password_lifetime: NULL

account_locked: N

Create_role_priv: N

Drop_role_priv: N

Password_reuse_history: NULL

Password_reuse_time: NULL

Password_require_current: NULL

User_attributes: NULL

*************************** 2. row ***************************

Host: 192.168

User: web_user

Select_priv: N

Insert_priv: N

Update_priv: N

Delete_priv: N

Create_priv: N

Drop_priv: N

Reload_priv: N

Shutdown_priv: N

Process_priv: N

File_priv: N

Grant_priv: N

References_priv: N

Index_priv: N

Alter_priv: N

Show_db_priv: N

Super_priv: N

Create_tmp_table_priv: N

Lock_tables_priv: N

Execute_priv: N

Repl_slave_priv: N

Repl_client_priv: N

Create_view_priv: N

Show_view_priv: N

Create_routine_priv: N

Alter_routine_priv: N

Create_user_priv: N

Event_priv: N

Trigger_priv: N

Create_tablespace_priv: N

ssl_type:

ssl_cipher: 0x

x509_issuer: 0x

x509_subject: 0x

max_questions: 0

max_updates: 0

max_connections: 0

max_user_connections: 0

plugin: mysql_native_password

authentication_string:

password_expired: N

password_last_changed: 2025-07-02 17:31:57

password_lifetime: NULL

account_locked: N

Create_role_priv: N

Drop_role_priv: N

Password_reuse_history: NULL

Password_reuse_time: NULL

Password_require_current: NULL

User_attributes: NULL

*************************** 3. row ***************************

Host: 192.168.72.%

User: ding

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: N

Drop_priv: N

Reload_priv: N

Shutdown_priv: N

Process_priv: N

File_priv: N

Grant_priv: N

References_priv: N

Index_priv: N

Alter_priv: N

Show_db_priv: N

Super_priv: N

Create_tmp_table_priv: N

Lock_tables_priv: N

Execute_priv: N

Repl_slave_priv: N

Repl_client_priv: N

Create_view_priv: N

Show_view_priv: N

Create_routine_priv: N

Alter_routine_priv: N

Create_user_priv: N

Event_priv: N

Trigger_priv: N

Create_tablespace_priv: N

ssl_type:

ssl_cipher: 0x

x509_issuer: 0x

x509_subject: 0x

max_questions: 0

max_updates: 0

max_connections: 0

max_user_connections: 0

plugin: mysql_native_password

authentication_string: *AC241830FFDDC8943AB31CBD47D758E79F7953EA

password_expired: N

password_last_changed: 2025-07-02 17:24:26

password_lifetime: NULL

account_locked: N

Create_role_priv: N

Drop_role_priv: N

Password_reuse_history: NULL

Password_reuse_time: NULL

Password_require_current: NULL

User_attributes: NULL

*************************** 4. row ***************************

Host: 192.168.72.%

User: web_user

Select_priv: N

Insert_priv: N

Update_priv: N

Delete_priv: N

Create_priv: N

Drop_priv: N

Reload_priv: N

Shutdown_priv: N

Process_priv: N

File_priv: N

Grant_priv: N

References_priv: N

Index_priv: N

Alter_priv: N

Show_db_priv: N

Super_priv: N

Create_tmp_table_priv: N

Lock_tables_priv: N

Execute_priv: N

Repl_slave_priv: N

Repl_client_priv: N

Create_view_priv: N

Show_view_priv: N

Create_routine_priv: N

Alter_routine_priv: N

Create_user_priv: N

Event_priv: N

Trigger_priv: N

Create_tablespace_priv: N

ssl_type:

ssl_cipher: 0x

x509_issuer: 0x

x509_subject: 0x

max_questions: 0

max_updates: 0

max_connections: 0

max_user_connections: 0

plugin: mysql_native_password

authentication_string: *2CDBC28387B17501C0071F7E7B39939A8BCEB708

password_expired: N

password_last_changed: 2025-07-02 17:32:22

password_lifetime: NULL

account_locked: N

Create_role_priv: N

Drop_role_priv: N

Password_reuse_history: NULL

Password_reuse_time: NULL

Password_require_current: NULL

User_attributes: NULL

*************************** 5. row ***************************

Host: 192.168.72.155

User: ryan

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Reload_priv: Y

Shutdown_priv: Y

Process_priv: Y

File_priv: Y

Grant_priv: Y

References_priv: Y

Index_priv: Y

Alter_priv: Y

Show_db_priv: Y

Super_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Execute_priv: Y

Repl_slave_priv: Y

Repl_client_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Create_user_priv: Y

Event_priv: Y

Trigger_priv: Y

Create_tablespace_priv: Y

ssl_type:

ssl_cipher: 0x

x509_issuer: 0x

x509_subject: 0x

max_questions: 0

max_updates: 0

max_connections: 0

max_user_connections: 0

plugin: mysql_native_password

authentication_string: *AC241830FFDDC8943AB31CBD47D758E79F7953EA

password_expired: N

password_last_changed: 2025-07-02 17:22:01

password_lifetime: NULL

account_locked: N

Create_role_priv: Y

Drop_role_priv: Y

Password_reuse_history: NULL

Password_reuse_time: NULL

Password_require_current: NULL

User_attributes: NULL

*************************** 6. row ***************************

Host: localhost

User: web_user

Select_priv: N

Insert_priv: N

Update_priv: N

Delete_priv: N

Create_priv: N

Drop_priv: N

Reload_priv: N

Shutdown_priv: N

Process_priv: N

File_priv: N

Grant_priv: N

References_priv: N

Index_priv: N

Alter_priv: N

Show_db_priv: N

Super_priv: N

Create_tmp_table_priv: N

Lock_tables_priv: N

Execute_priv: N

Repl_slave_priv: N

Repl_client_priv: N

Create_view_priv: N

Show_view_priv: N

Create_routine_priv: N

Alter_routine_priv: N

Create_user_priv: N

Event_priv: N

Trigger_priv: N

Create_tablespace_priv: N

ssl_type:

ssl_cipher: 0x

x509_issuer: 0x

x509_subject: 0x

max_questions: 0

max_updates: 0

max_connections: 0

max_user_connections: 0

plugin: mysql_native_password

authentication_string: *2CDBC28387B17501C0071F7E7B39939A8BCEB708

password_expired: N

password_last_changed: 2025-07-02 17:31:30

password_lifetime: NULL

account_locked: N

Create_role_priv: N

Drop_role_priv: N

Password_reuse_history: NULL

Password_reuse_time: NULL

Password_require_current: NULL

User_attributes: NULL

6 rows in set (0.00 sec)

-- 查看具体用户权限（示例：ding）

SHOW GRANTS FOR 'ding'@'192.168.72.%';

###验证

mysql> show grants for 'ding'@'192.168.72.%';

+----------------------------------------------------------------------+

| Grants for ding@192.168.72.% |

+----------------------------------------------------------------------+

| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `ding`@`192.168.72.%` |

+----------------------------------------------------------------------+

1 row in set (0.00 sec)

删除用户权限

REVOKE ALL [PRIVILEGES] ON 库名.表名 FROM '用户名'@'IP地址';

flush privileges;

-- 撤销所有权限

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'yu'@'%';

-- 验证删除

SELECT User, Host FROM mysql.user WHERE User='yu'; -- 应无结果

###验证

mysql> revoke all privileges,grant option from 'yu'@'%';

Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user where user='yu';

+------+------+

| user | host |

+------+------+

| yu | % |

+------+------+

1 row in set (0.01 sec)

案例

-- 删除用户的连接权限

revoke usage on *.* from 'username'@'ipaddress';

-- 删除用户的删除权限

revoke delete on *.* from 'ding'@'192.168.72.%';

###验证

mysql> revoke delete on *.* from 'ding'@'192.168.72.%';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

-- 验证权限更新

mysql> show grants for 'ding'@'192.168.72.%'; -- DELETE权限已移除

+--------------------------------------------------------------+

| Grants for ding@192.168.72.% |

+--------------------------------------------------------------+

| GRANT SELECT, INSERT, UPDATE ON *.* TO `ding`@`192.168.72.%` |

+--------------------------------------------------------------+

1 row in set (0.00 sec)

mysqld用户权限关键命令总结

操作命令示例创建用户CREATE USER '用户名'@'主机' IDENTIFIED BY '密码';授权GRANT 权限 ON 数据库.* TO '用户'@'主机';查看权限SHOW GRANTS FOR '用户'@'主机';撤销权限REVOKE 权限 ON 数据库.* FROM '用户'@'主机';删除用户DROP USER '用户'@'主机';刷新权限FLUSH PRIVILEGES;

九、mysqld数据库备份

9.1 数据备份的重要性

备份的主要目的是灾难恢复。

在生产环境中，数据的安全性至关重要。

任何数据的丢失都可能产生严重的后果。

9.2 造成数据丢失的原因

程序错误

人为操作错误

运算错误

磁盘故障

灾难（如火灾、地震）和盗窃

9.2 备份需要考虑的问题

可以容忍丢失多长时间的数据；

恢复数据要在多长时间内完成；

恢复的时候是否需要持续提供服务；

恢复的对象，是整个库，多个表，还是单个库，单个表。

9.4 备份类型

1、根据是否需要数据库离线

冷备（cold backup）：需要关mysql服务，读写请求均不允许状态下进行；

温备（warm backup）：服务在线，但仅支持读请求，不允许写请求；

热备（hot backup）：备份的同时，业务不受影响。

注意：

这种类型的备份，取决于业务的需求，而不是备份工具

MyISAM不支持热备，InnoDB支持热备，但是需要专门的工具

1.1、常用备份工具

商业工具

Navicat for MySQL

功能特点：它提供了直观的图形化界面，方便用户轻松地创建备份任务、设置备份计划以及恢复数据。支持多种备份类型，如完整备份、增量备份等，还能对备份文件进行加密和压缩，以确保数据的安全性和减小备份文件的大小。

适用场景：适用于各种规模的企业和开发团队，尤其是那些需要频繁进行数据库备份和恢复操作，且对操作的便捷性和可视化有较高要求的用户。

SQLyog Ultimate

功能特点：具有强大的备份和恢复功能，支持定时备份，可以按照设定的时间周期自动执行备份任务。同时，它还提供了数据同步功能，能够在不同的 MySQL 数据库之间进行数据同步，方便数据迁移和数据一致性维护。

适用场景：受 MySQL 开发者和数据库管理员的欢迎，常用于开发环境和生产环境中的数据库管理与备份工作，对于需要进行数据库版本控制和数据迁移的场景也非常实用。

开源工具

Percona XtraBackup

功能特点：它是一款开源的热备份工具，能够在不停止 MySQL 服务的情况下进行备份，支持对 InnoDB 和 XtraDB 存储引擎的备份和恢复，备份过程中不会阻塞数据库的正常读写操作，从而保证了业务的连续性。此外，它还提供了增量备份和部分备份功能，可有效减少备份时间和备份文件的大小。

适用场景：因其高效、灵活的特点，广泛应用于各种对业务连续性要求较高的生产环境，特别适合大型数据库和高并发应用场景下的备份需求。

MyDumper

功能特点：是一款多线程的备份和恢复工具，能够充分利用系统资源，大大提高备份和恢复的速度。它支持将数据库备份为多个文件，便于在恢复时进行灵活的选择和操作，同时还可以对备份文件进行压缩，节省存储空间。

适用场景：适用于需要快速备份和恢复大型 MySQL 数据库的场景，如互联网公司的大规模数据存储和处理环境，以及对备份效率有较高要求的企业级应用。

2、根据要备份的数据集合的范围

完全备份：full backup，备份全部字符集（全部数据库），每次备份都会进行完全备份，会导致备份文件占用大量的磁盘空间，并且有大量的重复数据，只适合第一次备份，不常用。

差异备份: incremental backup，要先进行一次完全备份，每次差异备份都会备份上一次完全备份后的数据，可能会出现备份的重复数据，导致占用大量的磁盘空间；

增量备份：differential backup，要先执行一次完全备份，每一次增量备份的数据都是备份在上一次完全备份或者上一次增量备份后的数据，不会出现重复数据，也不会占用额外的磁盘空间

建议的恢复策略

完全+增量+二进制日志

完全+差异+二进制日志

3、根据备份数据或文件

物理备份：直接备份数据文件

优点：备份和恢复操作都比较简单，能够支持兼容的mysql版本；恢复速度快，属于文件系统级别的。

建议：不要假设备份一定可用，要测试 mysql>check tables；检测表是否可用。

逻辑备份: 备份表中的数据和代码

优点：恢复简单；备份的结果为ASCII文件，可以编辑；与存储引擎无关；可以通过网络备份和恢复；

缺点：备份或恢复都需要mysql服务器进程参与；备份结果占据更多的空间；浮点数可能会丢失精度；还原之后，缩影需要重建

9.5 常见的备份方法

1、物理冷备（完全备份）

备份时数据库处于关闭状态，直接打包数据库文件

备份速度快，恢复时也是最简单的

2、专用备份工具mysqldump或mysqlhotcopy （完全备份，逻辑备份）

mysqldump常用的逻辑备份工具（导出为sql脚本）

mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表

启用二进制日志进行增量备份（增量备份）

进行增量备份，需要刷新二进制日志

3、第三方工具备份

免费的MySQL热备份软件Percona XtraBackup（阿里云的工具：dts，支持热迁移）

9.6 备份案例

mysqldump工具

1、完整备份

所有数据库的备份

备份

mysqldump --all-databases -uroot > all.sql

恢复

mysql -uroot < all.sql

指定数据库的备份

备份

mysqldump --databases test -uroot > test.sql

恢复

mysql -uroot < test.sql

指定数据表的备份

备份

mysqldump test stu > test.stu.sql

恢复

mysql -uroot test < test.sql

2、增量备份

二进制备份

配置过程

[root@master etc]# vim my.cnf

[mysqld]

log-bin=mysql-bin

binlog_format="statement"

[root@master mysql]# systemctl restart mysqld

查看日志文件内容

##在数据库中查看日志文件及书写位置

show master status;

######

mysqlbinlog mysql-bin.000001 |less

二进制备份的恢复

模拟数据丢失流程

flush logs; ###刷新日志文件，产生新的日志文件

-- 创建数据表及插入数据

flush logs;

-- 执行删除操作

数据恢复

基于位置

#基于开始位置

mysqlbinlog --start-position="245" mysql-bin.000003 | mysql -uroot

#基于结束位置

mysqlbinlog --stop-position="632" mysql-bin.000003 | mysql -uroot

#基于中间位置

mysqlbinlog --start-position="245" --stop-position="632" mysql-bin.000003 | mysql -uroot

基于时间

#基于开始时间

mysqlbinlog --start-datetime="2022-12-23 16:17:59" mysql-bin.000003 | mysql -uroot

#基于结束时间

mysqlbinlog --stop-datetime="2022-12-23 16:17:59" mysql-bin.000003 | mysql -uroot

#基于中间时间

mysqlbinlog --start-datetime="2022-12-23 16:17:59" --stop-datetime="2022-12-23 16:17:59" mysql-bin.000003 | mysql -uroot

XtraBackup 8.0.35-33 使用指南

简介

XtraBackup 8.0.35-33 是 Percona 提供的一个开源热备份工具，用于 MySQL 8.0 数据库的物理备份和恢复。它是 MySQL 企业级备份解决方案的重要组成部分。

安装

在基于 RPM 的系统上安装

sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

sudo percona-release enable-only tools release

sudo yum install percona-xtrabackup-80

在基于 Debian 的系统上安装

wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb

sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb

sudo percona-release enable-only tools release

sudo apt-get update

sudo apt-get install percona-xtrabackup-80

基本使用

1. 完整备份

xtrabackup --backup --target-dir=/path/to/backup --user=username --password=password

2. 准备备份（应用日志）

xtrabackup --prepare --target-dir=/path/to/backup

3. 恢复备份

xtrabackup --copy-back --target-dir=/path/to/backup

高级使用案例

案例1：增量备份

首先进行完整备份：

xtrabackup --backup --target-dir=/backups/full --user=root --password=yourpassword

进行第一次增量备份：

xtrabackup --backup --target-dir=/backups/inc1 --incremental-basedir=/backups/full --user=root --password=yourpassword

进行第二次增量备份：

xtrabackup --backup --target-dir=/backups/inc2 --incremental-basedir=/backups/inc1 --user=root --password=yourpassword

准备完整备份：

xtrabackup --prepare --apply-log-only --target-dir=/backups/full

应用第一个增量备份：

xtrabackup --prepare --apply-log-only --target-dir=/backups/full --incremental-dir=/backups/inc1

应用第二个增量备份：

xtrabackup --prepare --target-dir=/backups/full --incremental-dir=/backups/inc2

案例2：压缩备份

xtrabackup --backup --compress --target-dir=/backups/compressed --user=root --password=yourpassword

解压并准备备份：

xtrabackup --decompress --target-dir=/backups/compressed

xtrabackup --prepare --target-dir=/backups/compressed

案例3：并行备份和恢复

xtrabackup --backup --parallel=4 --target-dir=/backups/parallel --user=root --password=yourpassword

并行压缩：

xtrabackup --backup --compress --compress-threads=4 --parallel=4 --target-dir=/backups/parallel_compressed --user=root --password=yourpassword

案例4：加密备份

生成加密密钥：

openssl rand -base64 24 > /backups/encryption_key

创建加密备份：

xtrabackup --backup --target-dir=/backups/encrypted --encrypt=AES256 --encrypt-key-file=/backups/encryption_key --user=root --password=yourpassword

解密备份：

xtrabackup --decrypt=AES256 --encrypt-key-file=/backups/encryption_key --target-dir=/backups/encrypted

案例5：流式备份

备份到单个压缩文件：

xtrabackup --backup --stream=xbstream --user=root --password=yourpassword | gzip > /backups/backup.xbstream.gz

从流式备份恢复：

gunzip -c /backups/backup.xbstream.gz | xbstream -x -C /var/lib/mysql

xtrabackup --prepare --target-dir=/var/lib/mysql

常用选项说明

选项描述--backup执行备份操作--prepare准备备份以进行恢复--copy-back将备份复制回原始位置--target-dir指定备份目标目录--userMySQL 用户名--passwordMySQL 密码--hostMySQL 主机地址--portMySQL 端口--parallel并行线程数--compress启用压缩--compress-threads压缩线程数--encrypt加密算法--encrypt-key-file加密密钥文件--incremental-basedir增量备份的基础目录--incremental-dir增量备份目录--stream流式备份格式 (xbstream)

注意事项

确保有足够的磁盘空间存放备份文件

备份期间数据库负载可能会增加

恢复前确保 MySQL 服务已停止

恢复后可能需要调整文件权限

定期测试备份的可用性

最佳实践

定期进行完整备份和增量备份

备份文件存储在不同于数据库服务器的位置

实施备份验证流程

记录备份和恢复过程

监控备份作业的状态和持续时间

通过以上指南，您可以充分利用 XtraBackup 8.0.35-33 的强大功能来保护您的 MySQL 数据。

MyDumper 详细使用指南

MyDumper 是一个高性能的 MySQL 逻辑备份工具，相比传统的 mysqldump 具有并行备份、快照一致性等优势。以下是 MyDumper 的详细使用方式和案例。

安装 MyDumper

Ubuntu/Debian 系统

sudo apt-get install mydumper

CentOS/RHEL 系统

sudo yum install mydumper

从源码编译

git clone https://github.com/mydumper/mydumper.git

cd mydumper

mkdir build

cd build

cmake ..

make

make install

基本使用

1. 完整备份数据库

mydumper -u [username] -p [password] -h [host] -P [port] -o /backup/directory

2. 恢复数据库

myloader -u [username] -p [password] -h [host] -P [port] -d /backup/directory

常用参数说明

参数描述示例-u用户名-u root-p密码-p secret-h主机地址-h 127.0.0.1-P端口号-P 3306-o输出目录-o /backups-d恢复时指定备份目录-d /backups-B指定备份的数据库-B db1,db2-T指定备份的表-T db1.table1-t线程数-t 8-c压缩输出-c-v详细输出-v 3-C压缩备份文件-C-e备份表结构-e-r分割表的行数-r 100000-F按大小分割备份文件(MB)-F 256-s一致性快照-s

使用案例

案例1：备份单个数据库

mydumper -u root -p password -h localhost -B mydatabase -o /backups/mydatabase

案例2：备份多个特定表

mydumper -u root -p password -h localhost -T db1.table1,db1.table2 -o /backups/tables

案例3：多线程备份（8个线程）

mydumper -u root -p password -h localhost -t 8 -o /backups/full

案例4：压缩备份

mydumper -u root -p password -h localhost -c -o /backups/compressed

案例5：按100万行分割表数据

mydumper -u root -p password -h localhost -r 1000000 -o /backups/split

案例6：备份数据库结构（不备份数据）

mydumper -u root -p password -h localhost -e -o /backups/schema

案例7：恢复数据库到不同名称

myloader -u root -p password -h localhost -d /backups/full -B new_db_name

案例8：只恢复特定表

myloader -u root -p password -h localhost -d /backups/full -T db1.table1

高级功能

1. 一致性快照备份

mydumper -u root -p password -h localhost -s -o /backups/snapshot

2. 正则表达式过滤表

mydumper -u root -p password -h localhost -x '^sakila\.(actor|film)' -o /backups/regex

3. 备份时排除某些表

mydumper -u root -p password -h localhost -B sakila -X '^sakila\.film_text' -o /backups/exclude

4. 长查询超时设置

mydumper -u root -p password -h localhost --long-query-retries=10 --long-query-retry-interval=30 -o /backups/timeout

5. 只备份数据不备份结构

mydumper -u root -p password -h localhost --no-schemas -o /backups/data_only

实际应用场景

场景1：生产环境每日备份

# 备份脚本

#!/bin/bash

DATE=$(date +%Y%m%d)

BACKUP_DIR="/backups/mysql/$DATE"

LOG_FILE="/var/log/mydumper_${DATE}.log"

mydumper -u backup_user -p backup_pass -h 10.0.0.1 -P 3306 -t 4 -c -v 3 -o $BACKUP_DIR > $LOG_FILE 2>&1

# 保留7天备份

find /backups/mysql/ -type d -mtime +7 -exec rm -rf {} \;

场景2：大数据表部分恢复

# 只恢复用户表的前100万条数据

myloader -u root -p password -h localhost -d /backups/full -T db.users --rows=1000000

场景3：跨服务器迁移数据库

# 源服务器

mydumper -u root -p password -h source_host -B db_to_migrate -c -o /tmp/db_backup

# 目标服务器

myloader -u root -p password -h target_host -d /tmp/db_backup

注意事项

确保备份用户有足够的权限（至少需要 SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT 权限）

大数据库备份时考虑磁盘空间

备份期间可能会对生产数据库性能产生影响

恢复前最好在测试环境验证备份文件

考虑使用--no-locks选项减少锁表时间（但可能影响一致性）

性能优化建议

根据服务器CPU核心数设置适当的线程数(-t)

对大表使用-r参数分割备份文件

使用-c或-C参数压缩备份减少存储空间

考虑使用--no-locks选项减少锁表时间（如果允许不一致）

对于InnoDB表，使用-s参数获取一致性快照

通过合理配置MyDumper，您可以高效地完成MySQL数据库的备份和恢复工作，满足各种业务场景的需求。

mysqlhotcopy 使用指南

mysqlhotcopy 是 MySQL 提供的一个 Perl 脚本工具，用于快速备份 MyISAM 和 ARCHIVE 表。它通过直接复制数据库文件来实现快速备份，比逻辑备份工具如 mysqldump 更快，但只适用于特定存储引擎。

安装与准备

mysqlhotcopy 通常随 MySQL 客户端一起安装，位于 MySQL 的 bin 目录下。

检查是否安装

which mysqlhotcopy

确保依赖安装（Perl 模块）

sudo apt-get install perl-dbi perl-dbd-mysql # Debian/Ubuntu

sudo yum install perl-DBI perl-DBD-Mysql # CentOS/RHEL

基本语法

mysqlhotcopy [options] db_name [/path/to/backup/directory]

常用选项

选项描述--user=MySQL 用户名--password=MySQL 密码--host=MySQL 主机--port=MySQL 端口--socket=MySQL socket 文件--regexp=使用正则表达式匹配数据库--allowold不覆盖现有备份，添加 _old 后缀--keepold不删除被覆盖的备份--noindices不备份索引文件--method=复制方法 (cp 或 scp)--flushlog备份后刷新日志--resetmaster备份后重置二进制日志--resetslave备份后重置从库信息--addtodest添加而不是替换目标目录--dryrun模拟执行，不实际复制

使用案例

案例1：备份单个数据库

mysqlhotcopy --user=root --password=yourpassword mydatabase /backup/mysql

案例2：备份多个数据库

mysqlhotcopy --user=root --password=yourpassword db1 db2 db3 /backup/mysql

案例3：使用正则表达式备份匹配的数据库

mysqlhotcopy --user=root --password=yourpassword --regexp='^test_' /backup/mysql

案例4：保留旧备份

mysqlhotcopy --user=root --password=yourpassword --allowold --keepold mydatabase /backup/mysql

案例5：远程备份到其他服务器

mysqlhotcopy --user=root --password=yourpassword --method=scp mydatabase user@remotehost:/remote/backup/dir

案例6：不备份索引文件

mysqlhotcopy --user=root --password=yourpassword --noindices mydatabase /backup/mysql

案例7：备份后刷新日志

mysqlhotcopy --user=root --password=yourpassword --flushlog mydatabase /backup/mysql

案例8：模拟运行（不实际备份）

mysqlhotcopy --user=root --password=yourpassword --dryrun mydatabase /backup/mysql

实际应用场景

场景1：生产环境每日备份脚本

#!/bin/bash

DATE=$(date +%Y%m%d)

BACKUP_DIR="/backup/mysql/$DATE"

LOG_FILE="/var/log/mysqlhotcopy_${DATE}.log"

# 确保备份目录存在

mkdir -p $BACKUP_DIR

# 备份所有数据库

mysqlhotcopy --user=backup_user --password=backup_pass \

--allowold --keepold \

--flushlog \

--regexp='.*' \

$BACKUP_DIR > $LOG_FILE 2>&1

# 删除7天前的备份

find /backup/mysql/ -type d -mtime +7 -exec rm -rf {} \;

场景2：备份特定表

# 备份 mydatabase 中的 table1 和 table2

mysqlhotcopy --user=root --password=yourpassword \

mydatabase.table1 mydatabase.table2 \

/backup/mysql

场景3：增量备份策略

#!/bin/bash

DATE=$(date +%Y%m%d)

FULL_BACKUP_DIR="/backup/mysql/full"

INC_BACKUP_DIR="/backup/mysql/inc_$DATE"

# 每周日做完整备份

if [ $(date +%u) -eq 7 ]; then

mysqlhotcopy --user=backup_user --password=backup_pass \

--allowold --keepold \

--flushlog \

--regexp='.*' \

$FULL_BACKUP_DIR

else

# 其他日子做增量备份

mysqlhotcopy --user=backup_user --password=backup_pass \

--addtodest \

--regexp='.*' \

$INC_BACKUP_DIR

恢复数据库

mysqlhotcopy 的恢复是通过直接复制文件回原始位置实现的：

停止 MySQL 服务

systemctl stop mysql

复制备份文件到 MySQL 数据目录

cp -R /backup/mysql/mydatabase /var/lib/mysql/

确保文件权限正确

chown -R mysql:mysql /var/lib/mysql/mydatabase

启动 MySQL 服务

systemctl start mysql

注意事项

存储引擎限制：mysqlhotcopy 只适用于 MyISAM 和 ARCHIVE 表，不适用于 InnoDB

锁表：mysqlhotcopy 在备份期间会锁定表，可能导致应用程序短暂阻塞

备份一致性：对于正在写入的表，备份可能不一致

权限要求：

执行用户需要有读取数据库文件的权限

MySQL 用户需要 SELECT、RELOAD、LOCK TABLES 权限

备份完整性：备份后建议验证备份文件的完整性

版本兼容性：确保备份和恢复使用相同版本的 MySQL

性能优化建议

在低峰期执行备份操作

对于大型数据库，考虑分批备份

使用 --noindices 选项可以加快备份速度（但恢复时需要重建索引）

考虑使用 --method=scp 直接备份到远程服务器，减少本地磁盘IO

对于频繁更新的表，考虑结合 FLUSH TABLES 命令确保数据一致性

替代方案

由于 mysqlhotcopy 的局限性，现代 MySQL 环境通常使用以下替代方案：

对于 InnoDB 表：使用 Percona XtraBackup

逻辑备份：使用 mysqldump 或 mydumper

文件系统快照：LVM 快照或存储设备快照功能

mysqlhotcopy 最适合用于 MyISAM 表的快速备份场景，特别是当数据库大小适中且可以接受短暂锁表的情况下。

附录 A mysqld配置文件

# mysqld配置文件

# ----------------- 客户端配置 -----------------

[client]

port = 3306 # 客户端连接数据库的端口，默认为3306

socket = /var/lib/mysql/mysql.sock # 服务器socket文件的路径，默认为/var/lib/mysql/mysql.sock

# ----------------- MySQL客户端工具配置 -----------------

[mysql]

default-character-set=utf8mb4 # 客户端连接数据库时的默认字符集，这里使用utf8mb4

# ----------------- 服务器配置 -----------------

[mysqld]

user = mysql # 运行数据库服务器的系统用户，默认为mysql

port = 3306 # 服务器监听的端口，默认为3306

socket = /var/lib/mysql/mysql.sock # 服务器socket文件的路径，默认为/var/lib/mysql/mysql.sock

pid-file = /var/lib/mysql/mysql.pid # 服务器进程ID保存的文件路径，默认为/var/lib/mysql/mysql.pid

bind-address = 127.0.0.1 # 服务器绑定的IP地址，默认为本地回环地址

# ----------------- 日志配置 -----------------

log-error = /var/log/mysql/error.log # 错误日志文件路径，默认为/var/log/mysql/error.log

slow-query-log = 1 # 是否启用慢查询日志，1表示启用，0表示禁用，默认为禁用

slow-query-log-file = /var/log/mysql/slow.log # 慢查询日志文件路径，默认为/var/log/mysql/slow.log

# ----------------- 缓存配置 -----------------

query_cache_type = 1 # 查询缓存类型，1表示启用，0表示禁用，默认为禁用

query_cache_size = 32M # 查询缓存大小，默认为32M

query_cache_limit = 2M # 查询缓存单个查询的最大缓存大小，默认为2M

# ----------------- 字符集配置 -----------------

character-set-server = utf8mb4 # 服务器使用的字符集，默认为utf8mb4

collation-server = utf8mb4_unicode_ci # 服务器使用的字符集排序规则，默认为utf8mb4_unicode_ci

# ----------------- 默认存储引擎 -----------------

default-storage-engine = InnoDB # 默认使用的存储引擎，默认为InnoDB

# ----------------- InnoDB配置 -----------------

innodb_buffer_pool_size = 256M # InnoDB缓冲池大小，默认为256M

innodb_flush_log_at_trx_commit = 2 # 日志刷新策略，2表示每秒刷新，默认为每次事务提交刷新

innodb_log_buffer_size = 8M # InnoDB日志缓冲区大小，默认为8M

innodb_file_per_table = 1 # 是否为每个InnoDB表使用单独的表空间，1表示启用，0表示禁用，默认为启用

innodb_open_files = 400 # InnoDB打开的文件数量，默认为400

# ----------------- 网络和连接配置 -----------------

max_connections = 1000 # 最大并发连接数，默认为1000

max_allowed_packet = 16M # 允许的最大数据包大小，默认为16M

skip_external_locking = 1 # 是否禁用外部锁定，默认为是

# ----------------- 安全性配置 -----------------

secure-file-priv = /var/lib/mysql-files # 加载数据文件的安全目录，默认为/var/lib/mysql-files

sql-mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES # 服务器的SQL模式，默认为NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

附录 B mysql常见内置函数

一、字符串函数

-- ASCII(str):返回字符串str的最左面字符的ASCII代码值。如果str是空字符串，返回0。如果str是NULL，返回NULL

SELECT ASCII("dd");-- 100

SELECT ASCII("dc");-- 100

-- CONCAT(str1,str2,...)：返回来自于参数连结的字符串。如果任何参数是NULL，返回NULL。可以有超过2个的参数。一个数字参数被变换为等价的字符串形式

select CONCAT('My', 'S', 'QL'); -- MySQL

select CONCAT('My', NULL, 'QL'); -- NULL

select CONCAT(14.3); -- 14.3

-- LENGTH(str):返回字符串str的字节长度

select LENGTH('text'); -- 4

select LENGTH('字符串');-- 9

-- CHAR_LENGTH(str):用于获取字符串长度

select CHAR_LENGTH('text'); -- 4

select CHAR_LENGTH('字符串');-- 3

-- LOCATE(substr,str):返回子串substr在字符串str第一个出现的位置，如果substr不是在str里面，返回0

select LOCATE('bar', 'foobarbar'); -- 4

select LOCATE('xbar', 'foobar'); -- 0

-- INSTR(str,substr):返回子串substr在字符串str中的第一个出现的位置

select INSTR('foobarbar', 'bar'); -- 4

select INSTR('xbar', 'foobar'); -- 0

-- LEFT(str,len)/RIGHT(str,len):返回字符串str的最左/右面len个字符

select LEFT('foobarbar', 5); -- fooba

select RIGHT('foobarbar', 4); -- rbar

-- SUBSTRING(str,pos):从字符串str的起始位置pos返回一个子串

select SUBSTRING('foobarbar',5);-- arbar

-- TRIM(str):返回字符串str，所有前缀或后缀被删除了

select TRIM(' xbar ');-- xbar

-- LTRIM(str)/RTRIM(str)：返回删除了其前/后置空格字符的字符串str。

select LTRIM(' xbar');-- xbar

select RTRIM('xbar ');--xbar

-- REPLACE(str,from_str,to_str):返回字符串str，其字符串from_str的所有出现由字符串to_str代替

select REPLACE('xbar', 'x', 'bar');-- barbar

-- REPEAT(str,count):返回由重复countTimes次的字符串str组成的一个字符串。如果count <= 0，返回一个空字符串。如果str或count是NULL，返回NULL

select REPEAT('bar', 3);-- barbarbar

-- REVERSE(str):返回颠倒字符顺序的字符串str。

select REVERSE('bar');-- rab

-- INSERT(str,pos,len,newstr):返回字符串str，在位置pos起始的子串且len个字符长的子串由字符串newstr代替。

select INSERT(whatareyou', 5, 3, is');-- whatisyou

-- strcmp(str1,str2):用于比较两个字符串的大小。左大于右时返回1，左等于右时返回0，，左小于于右时返回-1

SELECT strcmp('ab','ac');-- -1

-- 大写：upper(x),ucase(x)；小写lower(x),lcase(x):字母大小写转换函数;

SELECT UPPER("abc");-- ABC

SELECT UCASE("abc");-- ABC

SELECT LOWER("ABC");-- abc

SELECT LCASE("ABC");-- abc

-- find_in_set(str1,str2):返回字符串str1在str2中的位置，str2包含若干个以逗号分隔的字符串（可以把str2看出一个列表，元素是多个字符串，查找结果是str1在str2这个列表中的索引位置，从1开始）

SELECT FIND_IN_SET('abc','123,456,abc');-- 3

-- field(str,str1,str2,str3…):与find_in_set类似，但str2由一个类似列表的字符串变成了多个字符串，返回str在str1,str2,str3…中的位置。

SELECT FIELD('abc','123','456','abc');-- 3

-- elt(index,str1,str2,str3…):获取指定位置的字符串

SELECT elt(3,'123','456','abc');-- abc

二、日期时间函数

-- curdate()/current_date():获取当前日期

SELECT curdate();-- 2018-08-09

SELECT current_date();-- 2018-08-09

-- curtime()/current_time()：获取当前时间

SELECT curtime();-- 15:38:54

SELECT current_time();-- 15:38:54

-- now():获取当前日期时间

select now();-- 2018-08-09 15:40:09

-- month(date),monthname(date):获取日期月份

SELECT MONTH (now());-- 8

SELECT monthname(now());-- August

-- week(date)：获取日期周数

select week(now());-- 31

-- year(date):获取日期年数

select year(now());-- 2018

-- hour(time):获取时间时刻

select hour(now());-- 15

-- minute(time):获取时间分钟数

select minute(now());-- 47

-- DAYOFWEEK(date)/DAYNAME(date)/WEEKDAY(date):获取时间星期数

select DAYOFWEEK(NOW());-- 5

select DAYNAME(now());-- Thursday

select WEEKDAY(now());-- 3

-- DATE_ADD(date,INTERVAL expr type)/DATE_SUB(date,INTERVAL expr type):进行日期增加/减少的操作，可以精确到秒

SELECT '2018-08-09'+INTERVAL 1 DAY;-- 2018-08-10

SELECT '2018-08-09'-INTERVAL 1 SECOND;-- 2018-08-08 23:59:59

SELECT DATE_ADD('2018-08-08 23:59:59',INTERVAL 1 SECOND);-- 2018-08-09 00:00:00

SELECT DATE_SUB('2018-08-09 00:01:01',INTERVAL '1:1' MINUTE_SECOND);-- 2018-08-09 00:00:00

-- date_format('time','format')/time_format(time,format):日期时间转换为字符串

select date_format(now(), '%Y%m%d%H%i%s');-- 20180809160315

select time_format(now(),'%H:%i:%s');--16:03:15

-- str_to_date(str, format)：字符串转换为日期

select str_to_date('08.09.2018 16:06:30', '%m.%d.%Y %H:%i:%s');-- 2018-08-09 16:06:30

-- makdedate(year,dayofyear)/maketime(hour,minute,second)：拼凑日期、时间函数

select makedate(2001,31); -- '2001-01-31'

select makedate(2001,32); -- '2001-02-01'

select maketime(12,15,30); -- '12:15:30'

-- unix_timestamp()/unix_timestamp(date)/from_unixtime(unix_timestamp)/from_unixtime(unix_timestamp,format):Unix 时间戳、日期转换函数

select unix_timestamp(); -- 1533802315

select unix_timestamp(now());-- 1533802315

select from_unixtime(1533802315); -- 2018-08-09 16:11:55

select from_unixtime(1533802315, '%Y%m%d%H%i%s'); -- 20180809161155

三、数学函数

-- ABS(X):返回X的绝对值

select ABS(-32);-- 32

-- MOD(N,M)或%:返回N被M除的余数

select MOD(15,7);-- 1

select 15 % 7; -- 1

-- FLOOR(X):返回不大于X的最大整数值

select FLOOR(1.23); -- 2

select FLOOR(-1.23); -- -2

-- CEILING(X)/ceil(x):返回不小于X的最小整数值

select CEILING(1.23);-- 2

select CEILING(-1.23); -- -1

-- ROUND(X) :返回参数X的四舍五入的一个整数。

select ROUND(1.58); -- 2

select ROUND(-1.58); -- -2

-- round(x,y):返回数值x带有y为小数结果的数值（四舍五入）

SELECT round(3.1415926,2);-- 3.14

-- rand():返回随机数

select rand();-- 0.5911854436538978

-- truncate(x,y):返回数值x截取y位小数的结果（不四舍五入）

select truncate(3.1415926,4);-- 3.1415

四、逻辑函数

-- CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END：在第一个方案的返回结果中， value=compare-value。而第二个方案的返回结果是第一种情况的真实结果。如果没有匹配的结果值，则返回结果为ELSE后的结果，如果没有ELSE 部分，则返回值为 NULL。

SELECT

CASE 1

WHEN 1 THEN

ELSE

END;-- 2

-- IF(expr1,expr2,expr3)：如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定

SELECT IF(1>2,2,3);-- 3

-- STRCMP(expr1,expr2)：如果字符串相同，STRCMP()返回0，如果第一参数根据当前的排序次序小于第二个，返回-1，否则返回1

select STRCMP('text', 'text2');-- -1

-- SELECT IFNULL(expr1,expr2)：如果expr1为空则返回expr2否则返回expr1

SELECT IFNULL(1,2);-- 2

五、加密函数

-- MD5(str)：函数可以对字符串str进行加密。MD5(str)函数主要对普通的数据进行加密。下面使用MD5(str)函数为字符串“abcd”加密

SELECT MD5('abcd');-- e2fc714c4727ee9395f324cd2e7f331f

-- ENCODE(str,pswd_str)：函数可以使用字符串pswd_str来加密字符串str。加密的结果是一个二进制数，必须使用BLOB类型的字段来保存它。

SELECT ENCODE("abcd","evan");

-- DECODE(crypt_str,pswd_str)函数可以使用字符串pswd_str来为crypt_str解密。crypt_str是通过ENCODE(str,pswd_str)加密后的二进制数据。字符串pswd_str应该与加密时的字符串pswd_str是相同的。下面使用DECODE(crypt_str,pswd_str)为ENCODE(str,pswd_str)加密的数据解密。

SELECT DECODE(ENCODE("abcd","evan"),"evan");

总结：

MySQL的核心价值体现在三层技术维度：

1.基础能力普适性

支持标准SQL语法（DDL/DML/DQL/DCL），五分钟快速部署

数据范式约束保障结构严谨性，ACID事务模型维护操作原子性

2.架构设计先进性

分层架构解耦连接处理与数据存储，支持每秒万级查询

索引优化（B+树、全文索引）提升检索效率300%+

3.生态整合开放性

无缝集成LAMP/LEMP技术栈，提供Python/Java/PHP等驱动接口

开源社区持续贡献高可用方案（主从复制、分库分表）

时代定位：在云原生与分布式演进中，MySQL凭借K8s Operator生态和云数据库兼容协议，持续巩固其作为“数据层事实标准”的核心地位。无论是初创项目快速验证，还是金融级系统承载高并发事务，MySQL凭借二十年技术沉淀与全球开发者验证，始终是平衡性能、成本、可靠性的最优解之一。