01 MySQL 数据库服务概述
(1) 课程知识章节说明
⽬前在互联⽹的实际应⽤中，各个企业都会⽐较关注⾃⾝⽹站的数据信息，既要保证数据信息的安全性，同时也要保证数据存储读取效率
并且在特殊的场景下，还要对存储的数据信息进⾏检索和分析；因此数据库服务业务已经在各⾏各业应⽤⾮常的⼴泛
对于互联⽹领域的技术⼈员，对于数据库服务知识的掌握，也将是在求职时必备的技能，有些时候还会绝对⼊职的定级和薪资⽔平。
(2) 数据库存储服务概述
今时今⽇，数据库系统已经成为各个动态⽹站上 web 应⽤程序的重要组成部分。 由于⾮常敏感和机密的数据有可能保存在数据库中，所以对数据库实施保护就显得尤为重要了。 要从数据库中提取或者存⼊数据，就必须经过连接数据库、发送⼀条合法查询、获取结果、关闭连接等步骤。 ⽬前，能完成这⼀系列动作的最常⽤的查询语⾔是结构化查询语⾔ Structured Query Language (SQL)。 并且在对数据库进⾏管理与维护的过程中，需要对数据库的相关知识进⾏充分的掌握，最终才能保证企业核⼼数据的安全性。
(3) 数据库知识引⼊说明
数据信息到底什么？
| 序号 | 识别数据⽅式 | 数据信息 |
|---|---|---|
| 01 | ⼈类识别数据 | 账号 密码 图⽚ 视频 数字 ⽂字 特殊符号 地址链接... |
| 02 | 电脑识别数据 | ⼆进制数值信息 |
数据如何进⾏存储？
可以利⽤专业的软件程序，将相关的⼈类识别的数据，转换为电脑可以识别的⼆进制信息进⾏存储 其中⼀些重要的且核⼼的数据信息，⽐如⽹站中的账号和密码数据信息，需要保证数据存储的安全性，提供更⼤的存储空间，便于管理； 因此，专⻔管理企业核⼼数据信息软件应运⽽⽣，数据库管理系统程序（软件-DBMS）
- 最核⼼数据信息存储：RDBMS-关系型数据库进⾏存储
- 次核⼼数据信息存储：NoSQL-⾮关系型数据库进⾏存储
- 分布型数据信息存储：DDBMS-分布式数据库进⾏存储（NewSQL）
常⻅数据库管理系统程序排名： https://db-engines.com/en/ranking
(4) 数据库服务概念介绍
数据库（database DB）,是⼀种存储数据的仓库，在实际应⽤过程具有如下特征：
- 数据库是根据数据结构组织、存储和管理数据的；
- 数据库能够⻓期、⾼效的管理和存储数据；
- 数据库的⽬的就是能够存储（写）和提供（读）数据
(5) 数据库服务分类说明
数据库分为三个⼤的类型：(随着互联⽹发展的产品类型)
- 关系型数据库：（属于数据整合时代）
把复杂的数据结构归结为简单的⼆元关系（RDBMS），即⼆维表格形式（⼆维表）；会注重数据存储的持久性，但存储效率低； 此类型数据库服务，类似于excel表格的存储数据⽅式，多采⽤SQL语⾔⽅式进⾏操作管理； 关系型数据库四层结构信息：
| 序号 | 数据库结构 | 描述说明 |
|---|---|---|
| 01 | 数据库管理系统（DBMS） | 进⾏数据存储应⽤管理的操作环境或命令 |
| 02 | 数据库（DB） | 数据存储的承载环境 |
| 03 | 数据表（Table） | 数据关系的构建环境 |
| 04 | 数据字段（Field） | 实际数据信息展现形式 |
常⻅的关系数据库应⽤程序：
| 序号 | 数据库程序 | 应⽤说明 |
|---|---|---|
| 01 | MySQL | 互联⽹公司应⽤最⼴泛 |
| 02 | Mariadb | 企业场景应⽤较少（20%），主要⽤于教学环境较多 |
| 03 | Oracle | 传统企业和部分国企应⽤较多，但也逐步被国产数据库替代 |
| 04 | SQLserver | 适合windows server系统环境部署的数据库服务，属于微软公司发布的数据库服务 |
| 05 | PostgreSQL | 适合于海量数据信息存储，对于⾦融⾏业数据信息分析能⼒将强 |
说明：关系型数据库的极致追求：数据存储的安全性，但是在某种程度会损失数据存储和读取的性能。
- ⾮关系型数据库：（属于数据拆分时代）
没有具体模型的数据结构，英⽂简称NoSQL（Not Only SQL），意为不仅仅是SQL，⽐较注重数据读取的效率； 利⽤NoSQL数据库主要处理⾼并发应⽤的场景，以及海量数据存储应⽤的场景 常⻅的⾮关系数据库应⽤程序：
| 序号 | 数据库程序 | 应⽤说明 |
|---|---|---|
| 01 | Redis | 可以利⽤内存存储数据，也可以采⽤磁盘存储数据，数据常⻅展⽰形式为 key-value 形式 |
| 02 | Memcache | 可以利⽤内存存储数据，也可以采⽤磁盘存储数据，数据常⻅展⽰形式为 key-value 形式 |
| 03 | Mongodb | 属于⾯向⽂档数据存储的数据库 |
| 04 | ES | 主要⽤于做⽇志数据的收集与检索的数据库（ELK ELFK） |
说明：⾮关系型数据库的极致追求：数据存储的⾼效性，但是在某种程序会牺牲数据存储的安全性。
- 企业新型数据库：（属于业务整合时代）
属于近些年，由国⼈研发设计出的数据库服务，可以满⾜很多国内⾼并发量⽹站数据存储和读取业务的需求； 常⻅的新型数据库应⽤程序：
| 序号 | 数据库程序 | 应⽤说明 |
|---|---|---|
| 01 | TiDB | 开源分布式关系型数据库，是⼀款同时⽀持在线事务处理与在线分析处理的融合型分布式数据库产品 |
| 02 | OceanBase | 是由蚂蚁集团完全⾃主研发的国产原⽣分布式数据库，兼顾分布式架构的扩展性与集中式架构的性能优势 |
| 03 | PolarXDB | 是由阿⾥巴巴⾃主研发的云原⽣分布式数据库，是⼀款基于云架构理念分布式数据库产品，专注海量数据处理 |
| 04 | RDS/TDSQL | 阿⾥云/腾讯云平台基于SaaS云计算服务环境构建的数据库产品（PolarXDB TDSQL TiDB） |
(6) 数据库服务版本应⽤
根据本课程安排，主要会介绍讲解关系型数据库中的 MySQL 数据库服务，也是业界⽐较知名和流⾏的开源数据库产品；
因此，需要关注下MySQL软件程序的版本应⽤（产品线），以及同类型相关的数据库服务产品。
| 序号 | ⼚商信息 | 数据库服务 |
|---|---|---|
| 01 | Oracle（官⽅） | Oracle/ MySQL（5.6 5.7* 8.0） |
| 02 | MariaDB | MariaDB Server（从5.5+之后，10.x+版本有⾃⼰的⼀些架构特性） |
| 03 | Percona | Percona Server（最接近于Oracle MySQL） |
MySQL企业应⽤的版本分析：
| 序号 | ⼤版本应⽤ | ⼩版本应⽤ |
|---|---|---|
| 01 | MySQL 5.6（企业应⽤极少） | 5.6.36 5.6.38 5.6.40 5.6.46 （GA 6-12⽉） 2021 01⽉ 常规/扩展服务全部停⽌ |
| 02 | MySQL 5.7 （部分企业应⽤） | 5.7.20 5.7.22 5.7.24 5.7.26 ..5.7.40（GA最新双数版本） 2023 10⽉ 常规/扩展服务全部停⽌ |
| 03 | MySQL 8.0 （应⽤最为⼴泛） | 8.0.11 8.0.17+ 8.0.18 8.0.26 8.0.32 （GA最新双数版本） |
MySQL企业应⽤的发布版本：
- C：表⽰为社区版本，属于开源免费版本
- C：表⽰为集群版本，属于开源盈利版本（应⽤极少）
- E：表⽰为企业版本，属于开源盈利版本（国企应⽤）
(7) 数据库服务优势特点
- MySQL数据库服务性能卓越，服务稳定，很少出现异常宕机的情况；
- MySQL数据库服务是开放源代码且⽆版权制约，⾃主性强，且使⽤成本低；
- MySQL数据库服务使⽤历史悠久，社区及⽤⼾⾮常活跃，遇到问题可以获取⼤量帮助；
- MySQL数据库服务软件体积⼩，安装使⽤简单，并且易于维护管理，安装及维护成本低；
- MySQL数据库服务业界⼝碑好，使得企业⽆需考虑就能直接使⽤；
- MySQL数据库服务架构应⽤⼴泛，可以⽤于构建LAMP LNMP LNMT等流⾏web架构；
- MySQL数据库服务⽀持多种操作系统，提供多种API接⼝，⽀持多种开发语⾔利⽤驱动接⼝调⽤；
(8) 常⻅⾯试题⽬
(30%~40% 属于技术类⾯试题 剩余部分属于⾮技术类⾯试题）
01 请你介绍你熟悉的数据库产品？ 你们公司⽤了哪些数据库产品？
关系型数据库（RDBMS）：MySQL Oracle PG MSSQL;
⾮关系数据库（NoSQL）：Redis MongoDB ES Memcached
云数据库：RDS
02 你喜欢MySQL数据库吗？平常如何学习的？MySQL需要具备哪些技能？
⽐较热衷于研究数据库领域技术，特别是⼀些开源的MySQL数据库产品都⽐较感兴趣研究；
在以往的⼯作中不断积累的技术经验，同时也经常通过官⽅⽂档查询数据库使⽤的⽅法，以及通过⼀些书籍和⽹上资料总结使⽤案例；
MySQL需要具备哪些技能可以将课程框架简单介绍⼀些；
02 MySQL 数据库服务部署
(1) 数据库服务程序下载
利⽤官⽅⽹址获取数据库软件程序：https://www.mysql.com/
选择官⽹⽹站的 DOWNLOADS 进⾏数据库软件程序下载选择；
在数据库官⽅下载⻚⾯中，选择 MySQL Community (GPL) Downloads 链接，进⾏数据库程序社区版下载；
进⼊数据库程序的社区版下载⻚⾯后，会看到社区版数据库服务的多种安装场景，本课程选择 MySQL Community Server ⽅式安装 官⽅最新版下载⻚⾯：
官⽅历史版下载⻚⾯：（本次课程讲解选择历史相对稳定版本进⾏安装部署）
进⼊到社区版服务器安装场景界⾯，需要选择数据库服务的安装部署环境和需要下载的程序具体版本信息；
查看Linux系统的glibc运⾏使⽤的C语⾔库版本信息：
[root@master ~]# getconf GNU_LIBC_VERSION
glibc 2.17
[root@master ~]# ldd --version
ldd (GNU libc) 2.17
Copyright (C) 2012 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
Written by Roland McGrath and Ulrich Drepper.
(2) 数据库服务安装⽅式
- 不同系统环境数据库服务安装⽅式-Linux
在Linux系统中常⻅的部署安装⽅式：
| 序号 | 安装⽅式 | 特征说明 |
|---|---|---|
| 01 | 采⽤⼆进制⽅式安装 | 直接解压缩⼆进制程序包，进⾏简单的配置和初始化操作即可 |
| 02 | 采⽤ rpm 包⽅式安装 | 需要从官⽅⽹站下载 rpm 程序包，并且需要考虑系统环境的兼容性，解决软件程序包依赖 |
| 03 | 采⽤ yum 包⽅式安装 | 需要根据官⽅⽹站说明，配置 yum 下载的仓库源信息，在联⽹情况下进⾏安装部署 |
| 04 | 采⽤源码包⽅式安装 | 需要从官⽅⽹站下载源码程序包，并且需要解决程序包依赖问题，以及需要采⽤编译安装 |
说明：本次课程主要围绕数据库服务的应⽤和管理进⾏讲解，所以选择⼆进制包安装⽅式，使安装过程更加简单和规范。
部署安装 MySQL 数据库服务程序：
## 确认操作系统数据库服务安装环境
# 查看操作系统的版本信息
[root@db01 ~]# cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)
# 查看操作系统的网卡地址
[root@db01 ~]# ip address show eth0
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:fa:10:44 brd ff:ff:ff:ff:ff:ff
inet 10.0.0.51/24 brd 10.0.0.255 scope global noprefixroute eth0
valid_lft forever preferred_lft forever
inet6 fe80::b80:de34:f743:47e9/64 scope link noprefixroute
valid_lft forever preferred_lft forever
# 查看系统本地域名解析信息
[root@xiaoQ- ~]# cat /etc/hosts
10.0.0.51 db01
# 查看防火墙是否关闭
[root@db01- ~]# systemctl is-active firewalld
unknown
[root@db01- ~]# systemctl is-enabled firewalld
disabled
# 查看 seLinux 安全策略是否关闭
[root@db01 ~]# getenforce
Disabled
# 清楚系统⾃带的 mariadb 数据库服务相关的程序包
[root@db01 ~]# rpm -qa|grep mariadb
mariadb-libs-. .-.el .x _
[root@db01 ~]# yum remove -y mariadb-libs
# 下载数据库程序依赖软件
[root@db01 ~]# yum install -y libaio-devel
# 需要修改链接库信息（只有centos8 系统才需要进⾏操作修改）
[root@db01 ~]# ln -s /usr/lib64/libncurses.so. /usr/lib/libncurses.so.5
或者
[root@db01 ~]# yum install ncurses-compat-libs
## 数据库服务程序详细安装过程
# 01 上传数据库程序软件包
# yum -y install autoconf (mysql 5.6 要安装此项)
[root@db01 ~]# mkdir /app # 创建目录专门存放安装的程序
[root@db01 ~]# cd /app
[root@db01 app]# rz -y
或者
[root@db01 app]# wegt https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz
# 02 解压数据库服务软件程序压缩包
[root@db01 app]# tar xf mysql-8.0.26-linux-glibc2.-x86_64.tar.xz
# 03 创建数据库服务程序⽬录的软链接
[root@db01 app]# ln -s mysql-8.0.26-linux-glibc2.-x86_64.tar.xz mysql
# 04 配置数据库服务程序环境变量
[root@db01 app]# echo 'export PATH=$PATH:/app/mysql/bin' >> /etc/profile
[root@db01 app]# source /etc/profile
# 05 可以获取数据库服务版本信息表⽰环境变量配置⽣效
[root@db01 app]# mysql -V
mysql Ver 8.0.26 for Linux on x86_64 (MySQL Community Server - GPL)
# 06 创建数据库服务管理⽤⼾信息
mysql两个核心目录：
- 程序目录：/app/mysql
- 数据目录：存储数据信息
# 创建用户信息：
[root@db01 app]# useradd mysql -M -s /sbin/nologin
# 07 创建数据库服务相关⽬录并进⾏⽬录信息进⾏授权
[root@db01 app]# mkdir -p /data/3306/data /data/3306/logs
[root@db01 app]# chown -R mysql. /data
# 08 数据目录初始化操作
[root@db01 app]# rm -f /etc/my.cnf
[root@db01 app]# mysqld --initialize-insecure --user=mysql --datadir=/data/3306/data --basedir=/app/mysql
# 5.6 版本
/app/mysql/scripts/mysql_install_db --user=mysql --basedir=/app/mysql --datadir=/data/3306/data
# 初始化常见错误：
1）没有安装依赖软件
yum install -y libaio-devel
2）没有创建用户信息
[ERROR] [MY-010124] [Server] Fatal error: Can't change to run as user 'mysql' ; Please check that the user exists!
没有初始化所指定的用户
3）没有指定正确数据目录信息或者程序目录信息
[ERROR] [MY-013236] [Server] The designated data directory /data/3307/data/ is unusable. You can remove all files that the server added to it.
[ERROR] [MY-010338] [Server] Can't find error-message file '/user/local/mysql/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
4) 没有授权数据目录
chown mysql.mysql /data/3306/data
5) 初始化数据目录必须是空目录
[ERROR] [MY-010457] [Server] --initialize specified but the data directory has files in it. Aborting.
[ERROR] 2024-06-14T08:12:09.804474Z 0 [ERROR] [MY-013236] [Server] The designated data directory /data/3306/data/ is unusable. You can remove all files that the server added to it.
保存原有数据，删除数据目录中数据
\rm -rf /data/3306/data/*
说明：以上操作步骤只是保证了数据库服务的软件程序部分安装完毕，在系统中可以识别数据库程序。
进⾏初始 MySQL 数据库服务操作：（8.0版本数据库）
数据库初始化命令应⽤解读：
| 核⼼程序命令 | 初始化核⼼参数 | 数据管理⽤⼾ | 数据存储路径 | 数据存放⽬录 |
|---|---|---|---|---|
| mysqld | --initialize-insecure | --user=mysql | --basedir=/usr/local/mysql | --datadir=/data/3306/data |
数据库初始化命令实践操作:
## 数据库服务初始化操作过程
[root@db01 data]# mysqld --initialize-insecure --user=mysql --datadir=/data/3306/data --basedir=/app/mysql
2024-07-01T03:03:32.074217Z 0 [System] [MY-013169] [Server] /app/mysql-8.0.26-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.26) initializing of server in progress as process 23181
2024-07-01T03:03:32.081351Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-07-01T03:03:33.049526Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-07-01T03:03:33.814596Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2024-07-01T03:03:33.814888Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2024-07-01T03:03:33.897363Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
## 数据库服务初始化经典报错信息
报错情况-01：
[报错信息]：
mysqld: error while loading shared libraries: libaio.so. : cannot open shared object file: No such file or directory
[解决⽅法]：
yum install -y libaio-devel
报错情况-02：
[报错信息]：
initialize specified but the data directory has files in it. Aborting.
The designated data directory /data/3306/data/ is unusable. You can remove all files that the server added to it.
[解决⽅法]：
rm -rf /data/3306/data/*
初始化补充
补充知识介绍：
1）初始化操作过程，有两种方式
mysql 5.7 / 8.0 以后
方式一：不安全初始化
mysqld --initialize-insecure --user=mysql --datadir=/data/3306/data --basedir=/usr/local/mysql
不安全初始化，数据库管理用户没有密码，可以直接登录数据库
方式二：安全初始化
mysqld --initialize --user=mysql --datadir=/data/3306/data --basedir=/usr/local/mysql
安全初始化，数据库管理用户会生成临时，用于登录数据库
PS：临时密码信息，只能用于登录数据库，登录成功后需要重新设置新的密码，使用临时密码登录数据库会有权限限制
alter user root@'localhost' identified by '123456';
补充：低版本 mysql 安装命令
mysql 5.5 / 5.6
/usr/local/mysql56/scripts/mysql_install_db --user=mysql --datadir=/data/3306/data --basedir=/usr/local/mysql
说明：数据库服务初始化的过程，是⽤于让数据库服务创建出⼀些默认的数据信息，以及识别存储数据⽬录信息
编写⽣成MySQL数据库配置⽂件：
# 创建数据库服务运⾏需要加载的my.cnf配置模板⽂件
[root@db01 app]# cat /etc/my.cnf
[mysql]
socket=/tmp/mysql.sock
[mysqld]
user=mysql
basedir=/app/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
启动运⾏MySQL数据库服务脚本：
# 01 查看数据库服务默认启动脚本⽂件
[root@db01 app ~]# ls -l /app/mysql/support-files/mysql.server
-rwxr-xr-x ⽉ 1 2021 /app/mysql/support-files/mysql.server
# 02 设置数据库服务程序启动运⾏⽂件
[root@db01 app ~]# cp /app/mysql/support-files/mysql.server /etc/init.d/mysqld
# 03 启动数据库服务程序利⽤脚本⽂件
[root@db01 app ~]# /etc/init.d/mysqld start
[root@db01 app ~]# service mysqld start
Starting MySQL.Logging to '/data/ /data/xiaoQ-.edu.err'.
SUCCESS!
[root@db01 app ~]# service mysqld stop
Shutting down MySQL.. SUCCESS!
# 04 设置数据库服务开机⾃动运⾏
[root@db01 app ~]# chkconfig --add mysqld
# 启动数据库服务程序利⽤脚本⽂件 (systemd⽅式)
# 将service管理服务⽅式转换为systemd管理服务⽅式 (前提是 /etc/init.d/下有 mysql 脚本)
[root@db01 app ~]# systemctl enable mysqld
mysqld.service is not a native service, redirecting to /sbin/chkconfig.
Executing /sbin/chkconfig mysqld on
[root@db01 app ~]# systemctl start mysqld
[root@db01 app ~]# systemctl is-active mysqld
active
# 最好编写如下脚本 将 mysql 交给 systemd 管理
[root@db01 data]# cat /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=mysqld.service
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/app/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
[root@db01 init.d]# systemctl daemon-reload
[root@db01 init.d]# systemctl start mysqld
[root@db01 init.d]# systemctl enable mysqld
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.
[root@db01 init.d]# systemctl status mysqld
# 05 检查确认数据库服务是否启动成功
[root@db01 data]# netstat -lntup | grep 3306
tcp6 0 0 :::33060 :::* LISTEN 42051/mysqld
tcp6 0 0 :::3306 :::* LISTEN 42051/mysqld
[root@db01 data]# ps -ef | grep mysql
mysql 42051 1 0 06:47 ? 00:00:45 /app/mysql/bin/mysqld --defaults-file=/etc/my.cnf
root 72929 57457 0 16:10 pts/1 00:00:00 grep --color=auto mysql
数据库启动失败排错过程
数据库启动失败排错过程
步骤一：确认系统环境
selinux 是否有其他数据库服务
步骤二：分析错误日志
Starting MySQL. ERROR! The server quit without updating PID file (/data/3306/data/dbo1.pid).
日志中错误信息举例：
cat /data/3306/data/db01.err
2024-06-14T09:15:36.120048Z 1 [ERROR] [MY-012271] [InnoDB] The innodb_system data file 'ibdata1' must be writable
步骤三：没有错误日志如何处理错误
找到配置文件信息
- 确认配置文件路径与名称
- 确认配置文件内容是否正确
特殊说明：在启动 mysql 数据库服务时，可能看到 33060 端⼝信息，此端⼝信息主要实现 mysqlx 协议的通讯过程；
利⽤ mysqlx 协议可以实现利⽤ mysql-shell 功能组件，对数据进⾏ key-value 操作，即识别 json ⽂件信息，进⾏远程管理；
Port 3306 is the default port for the MySQL Protocol, which is used by the mysql client, MySQL Connectors, and utilities such as mysqldump and mysqlpump.
Port 33060 is the default port for the MySQL Database Extended Interface (the MySQL X Protocol).
The X Plugin can be disabled at startup by either setting mysqlx=0 in your MySQL configuration file, or by passing in either --mysqlx=0 or --skip-mysqlx when
starting the MySQL server.
Alternatively, use the -DWITH_MYSQLX=OFF CMake option to compile MySQL Server without X Plugin.
参考链接：https://blog.csdn.net/michaelwoshi/article/details/107955539
不同数据库服务版本安装初始化差异说明：
| 版本信息 | 命令信息 | 初始化操作参数 | 备注说明 |
|---|---|---|---|
| 5.6 | mysql_install_db | 初始化的命令不⼀致，参数相同（⽆管理员密码） | |
| 5.7 8.0 | mysqld | --initialize-insecure | 表⽰不安全⽅式初始化（⽆管理员密码） |
| --initialize | 表⽰安全⽅式初始化（有随机默认管理员密码） |
表⽰安全⽅式初始化（有随机默认管理员密码）
将会⽣成临时密码（12位密码 需要满⾜4种复杂度），必须在⾸次登录数据库时改掉，才能正常管理数据库；
使⽤安全模式初始化数据库后，需要利⽤临时密码登录数据库服务，并设置管理员⽤⼾密码信息
mysql > alter user root@'localhost' identified by '123456';
-- 如果不修改设置管理员⽤⼾密码，使⽤临时密码只是能登录数据库，但是⽆法管理数据库
(3) 数据库服务启动关闭
在企业实际环境中，数据库服务的运⾏状态⼀般是不会进⾏随意调整的，在特殊场景下需要提前审批后，才能进⾏调整；
在企业实际环境中，在进⾏数据库服务关闭前，可以将业务先切换到备库（从库），再停⽌原有主库服务；
数据服务运⾏启动的进程信息查看
数据库服务运⾏启动与关闭操作逻辑：（ps -ef|grep mysqld --> bin/mysqld ）
数据库服务除了应⽤脚本⽂件或命令进⾏关闭数据库服务之外，还可以进⼊到数据库服务中进⾏关闭服务：
mysql> shutdown;
或者
mysqladmin -uroot -p shutdown
说明：当利⽤脚本或命令⽆法正常关闭服务时，也可以使⽤以上操作⽅式关闭数据库服务，但不建议利⽤ kill -9 ⽅式杀进程停数据库
数据库服务启动和关闭⽅式总结：
| 序号 | 启动⽅式 | 关闭⽅式 |
|---|---|---|
| 01 | mysqld & | kill/pkill/killall (不建议) kill -9 禁⽤ |
| 02 | mysqld_safe & | kill/pkill/killall mysqld (不建议) |
| 03 | mysql.server start | mysql.server stop |
| 04 | service mysqld start (建议) | service mysqld stop (建议) |
| 05 | systemctl start mysqld | systemctl stop mysqld |
| 06 | > shutdown | |
| 07 | mysqladmin -uroot -poldboy123 shutdown |
为什么停止 MySQL 服务禁用 kill -9
MySQL 是一种广泛使用的开源关系型数据库管理系统，其服务的停止通常需要遵循一定的程序，而不是简单地使用 kill -9 强制终止进程。
以下是几个原因：
1. 数据完整性
MySQL 在正常停止时，会有一个优雅的关闭过程，以确保正在处理的数据操作能够正确提交或回滚，从而维护数据库的一致性。使用 kill -9 强制终止 MySQL 进程会导致这个过程无法正常完成，可能会造成数据损坏或不完整。
2. 客户端连接处理
当使用 kill -9 杀死 MySQL 进程时，正在进行的客户端连接会突然中断，这可能导致连接丢失，并且可能在客户端留下未提交的事务，从而影响数据的一致性。
3. 产生 core 文件
kill -9 不会给进程发送任何信号，因此 MySQL 进程不会有机会写入 core 文件，这使得调试和故障排除变得更加困难。
4. 再次启动的困难
如果使用 kill -9 终止了 MySQL 服务，再次启动时可能会遇到各种问题，如权限问题、日志文件损坏等，这需要 DBA 手动处理，增加了维护的复杂性。
正确的停止方法
正确的做法是使用操作系统级的服务管理工具（如 systemctl、service 或 sudo shutdown -h now）来停止 MySQL 服务，这样可以确保 MySQL 有一个机会保存其状态并优雅地关闭。这样，我们不仅可以保证数据的安全和完整性，而且可以避免因强制终止带来的各种潜在问题。
结论
总之，停止 MySQL 服务时应避免使用 kill -9，因为这可能会导致数据损坏、连接丢失、无法产生 core 文件以及启动困难等问题。正确的做法是使用操作系统提供的服务管理工具来安全地停止 MySQL 服务，以维护数据库的完整性和稳定性。
(4) 数据库服务密码设置
数据库服务密码管理
1）没有密码可以设置密码
方式一：利用命令操作
mysqladmin -uroot password 'oldboy123'
方式二：利用SQL语句操作
mysql > alter user root@'localhost' identified by '123456';
2）已有密码可以修改密码
方式一：利用命令操作
mysqladmin -uroot -poldboy123 password '123456'
方式二：利用SQL语句操作
mysql -uroot -poldboy123
alter user root@'localhost' identified by '123456';
3）已有密码但是忘记密码（重置密码）
密码应用机制==数据库进行身份验证机制
客户端 -访问- 服务端
mysql 数据包 检测地址和端口
-u root root 123 --验证模块--加载授权表 mysql.user（磁盘 -- 内存）验证通过 登录访问
-p 123
-h 10.0.0.51
-P 3307
重置密码过程：
步骤一：关闭数据库服务
mysql> shutdown;
步骤二：采用安全模式启动数据库（可以免密登录 -- 不会加载授权表）
/app/mysql/bin/mysqld_safe --datadir=/data/3306/data --skip-grant-tables --skip-networking &
步骤三：重置密码信息
flush privileges
作用一：可以将内存中授权表信息 -- 同步写入磁盘 授权/用户数据 内存 -- 磁盘
作用二：可以将磁盘中授权表信息 -- 加载到内存中 授权/用户数据 磁盘 -- 内存
alter user root@'localhost' identified by '123123'; --- mysql.user
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
步骤四：重新启动数据库服务
pkill mysql
/etc/init.d/mysqld start
步骤五：进行登录访问测试
mysql -uroot -p123123
(5) MySQL 数据库部署参考链接
https://www.cnblogs.com/oldboy666/p/15559963.html
CentOS7.9 使用 yum 方式安装 mysql8.0.27（ 2022 最新版 ）
1.安装说明
yum 方式安装 MySQL 数据库比 rpm 安装要简单很多， yum 方式的安装原理是在执行 yum 安装命令后，会自动从 yum 源地址下载相应名称的 MySQL 数据库 rpm 包，然后到系统上安装，并自动解决各种软件包之间的依赖问题。这是一个非常不错的安装软件的方式，不仅仅是针对MySQL，安装其他软件也是如此。
Yum 安装方式的最大优点就是超级简单，但是它也有自身的问题，例如它继承了 rpm 包的无法定制化安装的问题，另外一个缺点是采用默认的 yum 安装时，无法安装mysql，因为 CentOS7 开始已经在官方库把默认的 mysql 换成了开源的 mariadb 了。如果要用 yum 方式安装 mysql，必须先下载安装 MySQL 源才行。
#1)关闭 selinux:
setenforce 0
getenforce
sed -i 's#SELINUX=enforcing#SELINUX=disabled#g' /etc/selinux/config
2.卸载 mariadb 依赖包
[root@oldboy ~]# rpm -qa|grep mariadb #<==查找已经安装的mariadb的包。
mariadb-libs-5.5.68-1.el7.x86_64
[root@oldboy ~]# yum remove mariadb-libs -y #<==卸载系统已经安装的mariadb依赖包。
3.下载 mysql 安装源
在 MySQL 官网中下载 YUM 源 rpm 安装包：https://dev.mysql.com/downloads/repo/yum/ 或者直接执行下面命令
[root@oldboy ~]# wget https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm
[root@oldboy opt]# rpm -ivh mysql80-community-release-el7-11.noarch.rpm
4.查看安装的 mysql 源
[root@oldboy opt]# rpm -ql mysql80-community-release
/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
/etc/yum.repos.d/mysql-community-source.repo
/etc/yum.repos.d/mysql-community.repo
5.开始安装 mysql
yum install mysql-community-server -y
6.启动 mysql 服务，并检查启动状态
mysqld --initialize-insecure --user=mysql
systemctl start mysqld
[root@oldboy ~]# netstat -lntup|grep 330
tcp6 0 0 :::33060 :::* LISTEN 3267/mysqld
tcp6 0 0 :::3306 :::* LISTEN 3267/mysqld
7.登录测试
[root@oldboy lib]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.27 MySQL Community Server - GPL
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
8.安装 FAQ
如果出现安装问题：
1.看日志 /var/log/mysqld.log
2.selinux 问题，执行 setenforce 0 临时关闭看看。
3.登录不进去
[root@oldboy ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
办法：
systemctl stop mysqld
rm -fr /var/lib/mysql/
mysqld --initialize-insecure --user=mysql
systemctl start mysqld
mysql
03 MySQL 数据库服务基础管理
(1) 数据库服务⽤⼾管理
在数据库服务中，也是有⽤⼾知识概念的，类似系统中的⽤⼾管理应⽤，主要利⽤⽤⼾管理知识，可以实现：
- ⽤⼾登录数据库服务系统中（实现数据库服务登录）
- ⽤⼾管理数据库服务中对象（实现数据库对象管理）
创建数据库服务⽤⼾信息
# 数据库⽤⼾创建：'⽤⼾名称信息'@'⽩名单信息'
-- ⽤⼾名称信息：创建的⽤⼾字符串信息
-- ⽩名单信息：⽹段IP地址信息或者名称信息
用户信息构成：用户名称@主机名称/主机地址范围
主机范围就是允许哪些机器连接。连接的机器的 iP 地址，必须再允许的主机范围，否则不允许连接
主机名称或主机地址范围表⽰可以以什么样的⽅式，来使⽤数据库管理⽤⼾登录数据库系统；
常⻅的有两种⽅式：
- 第⼀种⽅式：以本地登录⽅式进⾏登录数据库系统；
- 第⼆种⽅式：以远程登录⽅式进⾏登录数据库系统；
白名单：
1）本地登录
mysql > create user wh@'localhost' identified by '123';
[root@db01 data]# mysql -uwh -p123
2）远程登录
方法一：wh1@'10.0.0.%' -- 允许特定网段
mysql> create user wh1@'10.0.0.%' identified by '123';
[root@web01 ~]# mysql -uwh1 -p123 -h10.0.0.51 -P3306
方法二：wh1@'10.0.0.7' -- 允许特定 IP
方法三：wh1@'web01' -- 允许特定主机名（前提是在 /etc/hosts 文件里做好解析）
方法四：wh1@'%' -- 允许所有远程主机
补充：% 白名单设置
作用一：只允许远程用户连接
作用二：可以允许本地和远程任意用户连接（看 mysql 的版本）
进⼊到数据库环境中，执⾏以下 SQL 语句进⾏创建：
mysql> create user wh1@'10.0.0.%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,authentication_string from mysql.user;
# 数据库创建用户后，无法远程登录，显示认证错误
./mysql -uwh -pwh -h10.0.0.51
Warning:Using a password on the command line interface can be insecure.
ERROR 2059 (HY000):Authentication plugin 'caching_sha2_password' cannot be locaded:
# 出现此问题原因：新版数据库密码加密插件进行了更新
解决方式一：升级客户端，使客户端加密密码方式支持 caching_sha2_password
解决方式二：升降级服务端，使服务端加密密码方式支持 mysql——native——password
alter user oldboy@'10.0.0.%' identified with mysql_native_password by '123456';
查询数据库服务⽤⼾信息
进⾏到数据库环境中，执⾏以下SQL语句进⾏查询：
mysql> select user,host,authentication_string from mysql.user;
查看当前登录系统的⽤⼾信息：
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
修改数据库服务⽤⼾密码
进⼊到数据库环境中，执⾏以下 SQL 语句进⾏修改；
# 要修改的用户如下
[root@master ~]# mysql -uwh -p123
# 使用 root 进入数据库才能修改 并确定白名单信息
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| wh1 | 10.0.0.% |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
| wh | localhost |
+------------------+-----------+
6 rows in set (0.01 sec)
mysql> alter user wh@'localhost' identified by 'wh';
Query OK, 0 rows affected (0.01 sec)
# 5.7 以后用 alter
mysql > alter user wh@'localhost' identified by '123';
mysql > flush privileges; # 只有修改密码后才需要执行此项
删除数据库服务⽤⼾信息
进⼊到数据库环境中，执⾏以下 SQL 语句进⾏删除:（在⽣产环境慎⽤，不⽤的⽤⼾信息可以先锁定）
# 使用 root 进入数据库才删除 并确定白名单信息
MySQL> drop user 'user'@'主机域
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| wh1 | 10.0.0.% |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
| wh | localhost |
+------------------+-----------+
6 rows in set (0.01 sec)
mysql> drop user wh1@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
| wh | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
# 批量删除指定的用户信息 删除 test 相关用户
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| test01 | % |
| test02 | % |
| test04 | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| oldboy | localhost |
| root | localhost |
| wh | localhost |
+------------------+-----------+
9 rows in set (0.00 sec)
# 01 先将要批量删除的用户筛选出来 如下所示：
mysql> select user,host from mysql.user where user like "test%";
+--------+------+
| user | host |
+--------+------+
| test01 | % |
| test02 | % |
| test04 | % |
+--------+------+
3 rows in set (0.00 sec)
# 02 将 select 改为 delect 即可
mysql> delete from mysql.user where user like "test%";
Query OK, 3 rows affected (0.01 sec)
# 03 在对表进行查看验证是否已经批量删除
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| oldboy | localhost |
| root | localhost |
| wh | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)
修改用户信息（修改密码）
# 5.7 以后用 alter
mysql > alter user wh@'localhost' identified by '123';
mysql > flush privileges; # 只有修改密码后才需要执行此项
查看用户权限
# 查看指定用户所拥有的权限
mysql> show grants for root@'localhost';
mysql> show grants for wh@'localhost';
+----------------------------------------+
| Grants for wh@localhost |
+----------------------------------------+
| GRANT USAGE ON *.* TO `wh`@`localhost` |
+----------------------------------------+
1 row in set (0.00 sec)
# 查看所有可以给用户赋予的权限
mysql> show privileges;
锁定数据库服务⽤⼾信息
在数据库服务进⾏⽤⼾管理时，也可以将特定数据库管理⽤⼾进⾏锁定，从⽽实现禁⽌某些特定⽤⼾再次登录数据库服务进⾏管理；
锁定数据库⽤⼾操作：
mysql> create user oldboy@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| oldboy | localhost |
| root | localhost |
| wh | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)
# 进行数据库用户锁定操作
mysql> alter user oldboy@'localhost' account lock;
Query OK, 0 rows affected (0.01 sec)
# 查看数据库用户锁定状态
mysql> select user,host,account_locked from mysql.user;
+------------------+-----------+----------------+
| user | host | account_locked |
+------------------+-----------+----------------+
| mysql.infoschema | localhost | Y |
| mysql.session | localhost | Y |
| mysql.sys | localhost | Y |
| oldboy | localhost | Y |
| root | localhost | N |
| wh | localhost | N |
+------------------+-----------+----------------+
6 rows in set (0.00 sec)
# 使用 oldboy 用户登录测试
[root@master ~]# mysql -uoldboy -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3118 (HY000): Access denied for user 'oldboy'@'localhost'. Account is locked.
解除锁定用户
# 进⾏数据库⽤⼾解除锁定操作
mysql> alter user oldboy@'localhost' account unlock;
Query OK, 0 rows affected (0.00 sec)
# 查看数据库用户锁定状态
mysql> select user,host,accunt_locked from mysql.uesr;
ERROR 1146 (42S02): Table 'mysql.uesr' doesn't exist
mysql> select user,host,accunt_locked from mysql.user;
ERROR 1054 (42S22): Unknown column 'accunt_locked' in 'field list'
mysql> select user,host,account_locked from mysql.user;
+------------------+-----------+----------------+
| user | host | account_locked |
+------------------+-----------+----------------+
| mysql.infoschema | localhost | Y |
| mysql.session | localhost | Y |
| mysql.sys | localhost | Y |
| oldboy | localhost | N |
| root | localhost | N |
| wh | localhost | N |
+------------------+-----------+----------------+
6 rows in set (0.00 sec)
⾯试题:若数据库服务本地管理员密码遗忘如何处理
解决处理⽅法：将数据库重启并进⼊数据库安全模式（不加载验证授权表模式）
停⽌数据库服务，并检查数据库服务是否停⽌：
# 在系统命令⾏关闭
mysql> shutdown;
或者
service mysql stop
重新启动数据库服务，并设置启动⽅式为忽略授权表启动⽅式：
[root@oldboy ~]# mysqld_safe --skip-grant-tables --skip-networking &
--skip-grant-tables 表⽰忽略授权表启动
--skip-networking 忽略⽹络通讯⽅式启动
# 进⾏测试服务是否启动
[root@oldboy ~]# ps -ef|grep mysql
采⽤免密码⽅式登录数据库系统中，并重置root⽤⼾密码信息：
[root@oldboy ~]# mysql -uroot
mysql> flush privileges;
# 表⽰先将数据库服务授权表加载到内存中，也可以将内存中修改的授权信息存⼊到磁盘中
mysql> alter user root@'localhost' identified by 'oldboy123';
# 修改数据库服务⽤⼾密码信息
重新启动数据库服务程序：
# 杀死已经运⾏的数据库服务进程信息
[root@oldboy ~]# pkill mysqld
[root@oldboy ~]# ps -ef|grep mysql
# 重新启动数据库服务
[root@oldboy ~]# systemctl start mysqld
[root@oldboy ~]# netstat -lntup|grep 3306
# 重新登录进⾏测试
[root@oldboy ~]# mysql -uroot -poldboy123
(2) 数据库服务权限管理
在数据库服务中，权限管理可以⽤于针对不同的⽤⼾，限制登录管理数据库服务⽤⼾的操作⾏为，也可以区分不同⽤⼾的操作能⼒；
思考：系统权限和数据库权限作⽤在哪？
系统权限是作⽤于⽂件上的；
数据库权限是作⽤在⽤⼾上的，或者⻆⾊（相当于系统的⽤⼾组）上的；
数据库服务常⻅的权限管理定义设置：（ 采用具有的字符参数信息进行定义,并且权限是赋予数据库管理用户的 ） 数据库服务中可以授权的所有权限信息查看：
MySQL> show privileges;
输出展⽰的信息表⽰内容：
| 序号 | 输出列 | 解释说明 |
|---|---|---|
| 01 | Privilege | 表⽰可以授权的所有权限信息展⽰ |
| 02 | Context | 表⽰设置的权限可以对数据库服务哪些资源进⾏操 |
| 03 | Comment | 对相应的权限功能进⾏简单解释说明 |
核⼼重要授权权限总结：
| 序号 | 权限 | 授权资源 | 解释说明 |
|---|---|---|---|
| 01 | Select | Tables | 可以对表进⾏操作，查询表中数据信息 |
| 02 | Insert | Tables | 可以对表进⾏操作，插⼊表中数据信息 |
| 03 | Update | Tables | 可以对表进⾏操作，修改表中数据信息 |
| 04 | Delete | Tables | 可以对表进⾏操作，删除表中数据信息 |
| 05 | Alter | Tables | 可以对表进⾏操作，修改表中结构信息（元数据-属性） |
| 06 | Index | Tables | 可以对表进⾏操作，删除或创建表中的索引信 |
| 07 | Create | Databases,Tables | 可以对表和库进⾏操作，⽤于创建数据库和表信息 |
| 08 | Drop | Databases,Tables | 可以对表和库进⾏操作，⽤于删除数据库和表信息 |
说明：all privileges（all）包含查看的所有权限信息，但是唯独缺了 Grant option ，不能授权⽤⼾，此权限只能给 root@local ⽤⼾
设置数据库服务⽤⼾授权
通过理解数据库内部结构，可以知晓，数据库是⼀个很⼤的仓库，⾥⾯包含多个库，每个库中有多个表，每个表中有多个⾏和列；
当然作为数据库服务管理⽤⼾，要管理的不仅仅是库和表，其实还有视图、触发器、存储过程等和数据库有关的信息；
因此，在某些时候创建完⽤⼾信息，还需要对创建的⽤⼾进⾏授权，就是让不同⽤⼾拥有管理库和表对象的权限。 进⼊到数据库环境中，执⾏以下 SQL 语句进⾏设置：
# 授权语法格式
MySQL> grant all on 授权对象范围 to 权限赋予⽤⼾信息
MySQL> grant all on * . * to blog@' . . .%';
授权 权限 库信息 . 表信息 ⽤⼾ 主机域
# 对数据库和表同时进⾏授权⽅法
MySQL> grant all on *.* # 表⽰对所有的数据库和所有的表进⾏授权
MySQL> grant all on oldboy.* # 表⽰对指定的数据库中所有的表进⾏授权
MySQL> grant all on oldboy.t1 # 表⽰对指定的数据库中指定的表进⾏授权
具体授权权限操作步骤：
MySQL> grant all on *.* to oldgirl@'localhost';
# 给开发来开权限（Insert,Update,Select,Delete）
MySQL> grant Insert,Update,Select on *.* to oldboy@'localhost%'; # 规范化 最好不加 delete
GRANT SELECT (user,host) ON 数据库名.表名 TO '用户名'@'%' IDENTIFIED BY '密码'; # 授权到字段 权限
# 创建远程特权管理⽤⼾（练习）
MySQL > create user root@'10.0.0.%' identified by '123456';
MySQL > grant all on *.* to root@'10.0.0.%'
具体回收权限操作步骤：
# 数据库服务⽤⼾授权操作
mysql> grant insert,update,delete,select on *.* to wh@'localhost';
Query OK, 0 rows affected (0.00 sec)
# 数据库服务⽤⼾权限授权成功
mysql> show grants for wh@'localhost';
+-----------------------------------------------------------------+
| Grants for wh@localhost |
+-----------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `wh`@`localhost` |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)
# 数据库服务⽤⼾回收权限
mysql> revoke delete on *.* from wh@'localhost';
Query OK, 0 rows affected (0.01 sec)
# 数据库服务⽤⼾权回收成功
mysql> show grants for wh@'localhost';
+---------------------------------------------------------+
| Grants for wh@localhost |
+---------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE ON *.* TO `wh`@`localhost` |
+---------------------------------------------------------+
1 row in set (0.00 sec)
查看数据库服务⽤⼾权限
当⽤⼾授权操作完毕后，可以对授权的⽤⼾进⾏查看确认：
mysql> show grants for wh@'localhost';
+---------------------------------------------------------+
| Grants for wh@localhost |
+---------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE ON *.* TO `wh`@`localhost` |
+---------------------------------------------------------+
1 row in set (0.00 sec)
授权操作实践过程：
# 数据库⽤⼾权限设置实践
# 授权 blog 作为管理员，但是不具有创建⽤⼾权利
[root@oldboy ~]# mysql -uroot -poldboy
MySQL> grant all on *.* to blog@'192.16.30.%';
# blog@'192.16.30.%' 权限，就相当于管理员，但是缺少⼀个创建⽤⼾权限
MySQL> flush privileges;
# 数据库⽤⼾权限设置查看
MySQL> show grants for blog@'192.16.30.%';
# 再查看发现权限变化 ALL PRIVILEGES，重新登录数据库可以对数据库操作。
企业⽣产情况授权思路：
企业⽣产库表授权⼀定要更⼩，最⼩的授权最⼤的安全，给业务⽤⼾常规授权，包含对内容的增删改查；
| 序号 | 权限 | 互联网应用 |
|---|---|---|
| 01 | SELECT 查询数据 | 访问⽹站⻚⾯时，表⽰浏览查看数据信息； |
| 02 | INSERT 插⼊数据 | 访问⽹站⻚⾯时，表⽰发表⽂章数据信息； |
| 03 | UPDATE 修改数据 | 访问⽹站⻚⾯时。表⽰修改⽂章数据信息； |
| 04 | DELETE 删除数据 | 访问⽹站⻚⾯时，表⽰删除⽂章数据信息； |
# ⽣产常规授权实践
# 创建数据库操作
MySQL> create database bbs;
# 创建管理数据库⽤⼾
MySQL> create user bbs@'192.168.56.%' identified by '123';
# 进⾏管理⽤⼾授权
MySQL> grant insert,select,update,delete on bbs.* to bbs@'192.168.56.%';
# 授权等价于root管理⽤⼾
MySQL> grant all on *.* to oldboy@'localhost' identified by 'oldboy123' with grant option;
MySQL> flush privileges;
拓展:（角色设置）
8.0 之后版本，出现了数据库服务中对⽤⼾的 role ⽀持，表⽰可以将企业中常⽤的权限汇总打包为⼀个特定的 role 信息，在赋予给指定⽤⼾；
# 指定⽤⼾操作特定数据库时，具有共性相同的权限：分为运维权限（rw） 分为开发权限（ro）
# 环境准备
mysql> create database oldboy;
mysql> use oldboy;
mysql> create table t1 (id int);
mysql> insert into t1 values (1),(2),(3);
mysql> select * from oldboy.t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
# 查看role创建的⽅法
mysql> help create role;
# 01 创建可读可写的角色和只读的角色
mysql> create role oldboy_rw,oldboy_r;
# 02 进⾏ role 权限信息设定
mysql> grant select on oldboy.* to oldboy_r;
Query OK, 0 rows affected (0.01 sec)
mysql> grant select,update,insert on oldboy.* to oldboy_rw;
Query OK, 0 rows affected (0.01 sec)
# 03 将指定 role 信息与相应的⽤⼾信息进⾏捆绑
# 创建要捆绑的用户 user01 user02
mysql> create user user01@'localhost' identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> create user user02@'localhost' identified by '123';
Query OK, 0 rows affected (0.00 sec)
# 实现不同的role信息与不同的⽤⼾进⾏捆绑
mysql> grant oldboy_r to user01@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> grant oldboy_rw to user02@'localhost';
Query OK, 0 rows affected (0.00 sec)
# 04 激活角色功能（不激活角色 用户的赋予权限是无法使用的）
# user01 登录数据库时，会显⽰对应激活的⻆⾊信息
mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| NONE |
+----------------+
1 row in set (0.00 sec)
# ⽅式⼀：⼿动激活⻆⾊
# 使用管理员账号激活 （user01 也可以最好不用）
mysql> set default role all to user01@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> set default role all to user02@'localhost';
Query OK, 0 rows affected (0.00 sec)
# 使用user01 账号验证
mysql> use oldboy;
Database changed
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| `oldboy_r`@`%` |
+----------------+
1 row in set (0.00 sec
# 使用user02 账号验证 （其他的省略）
mysql> select current_role();
+-----------------+
| current_role() |
+-----------------+
| `oldboy_rw`@`%` |
+-----------------+
1 row in set (0.00 sec)
#⽅式⼆：实现⾃动激活⽤⼾⻆⾊信息（管理员登录数据库进行设置此功能）
[root@master ~]# mysql -uroot -p
# 首先可以查看此功能的设置状态
mysql> select @@activate_all_roles_on_login;
+-------------------------------+
| @@activate_all_roles_on_login |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (0.00 sec
mysql > set global activate_all_roles_on_login=on;
mysql> select @@activate_all_roles_on_login;
+-------------------------------+
| @@activate_all_roles_on_login |
+-------------------------------+
| 1 |
+-------------------------------+
# PS:此功能开启后 未激活角色捆绑的用户 第一次进入数据库时就可以直接激活了
(3) 数据库服务连接管理
当数据服务中⽤⼾创建设置完毕后，就可以实现利⽤指定⽤⼾⾝份连接登录管理数据服务，具体连接操作如下：
执⾏命令操作连接登录数据库服务
# 本机连接数据库服务：
[root@master ~]# mysql -ublog -poldboy
[root@master ~]# mysql -uroot -poldboy
# 以上连接⽅式等价于，以下⻓格式命令登录⽅式
[root@oldboy ~]# mysql -uroot -poldboy -hlocalhost -P -S /var/lib/mysql/mysql.sock
# 进⾏本地连接时，-hlocalhost -P -S /var/lib/mysql/mysql.sock 可以省略
# 远程连接数据库服务：
[root@web01 ~]# mysql -uroot -poldboy -h10.0.0.51 -P3306 -S /var/lib/mysql/mysql.sock
数据库服务连接⽅式特殊说明：
在对数据库服务进⾏连接管理操作时，是可以基于两种⽹络连接⽅式：
⽅式⼀：利⽤socket建⽴远程连接（unix 套接字⽂件连接）
利⽤此⽅法建⽴数据库服务的连接，只能⽤于建⽴本地数据库服务的连接；
# 套接字⽂件信息定义：
[root@master ~]# cat /etc/my.cnf
[mysql]
socket=/tmp/mysql.sock # 指定数据库服务加载的套接字⽂件路径信息
[mysqld]
user=mysql
basedir=/app/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock # 指定数据库服务加载的套接字⽂件路径信息
# 利⽤套接字⽂件建⽴数据库服务连接
[root@web01 ~]# mysql -uroot -poldboy123 -S /tmp/mysql.sock
说明：需要在数据库服务中授权localhost⽤⼾，才能实现采⽤ socket ⽅式登录操控数据库服务
⽅式⼆：利⽤TCP/IP建⽴远程连接（⽹络地址与端⼝）
利⽤此⽅法建⽴数据库服务的连接，可以⽤于建⽴远程数据库服务的连接；
[root@web01 ~]# mysql -uroot -poldboy -h 10.0.0.51 -P3306 -S /var/lib/mysql/mysql.sock
补充：数据库服务启动特殊端口信息
利用 33060 端口 可以实现 mysqlx 协议通信
利用 3306 端口 可以实现 mysql 协议通信
利用 80端口 可以实现 http 协议通信
利用 443 端口 可以实现 https 协议通信
利用 23 端口 可以实现 telnet 协议通信
说明：需要在数据库服务中授权⽹络⽩名单⽤⼾，才能实现采⽤TCP/IP⽅式登录操控数据库服务
软件终端操作连接登录数据库服务
利⽤Navicat数据库客⼾端远程软件，实现远程连接管理数据库服务；
数据库远程连接客⼾端下载链接：
软件激活步骤参考： https://zhuanlan.zhihu.com/p/404086701
软件连接数据库排错流程
拍错步骤一：测试网络是否正常
ping 10.0.0.51
telnet 10.0.0.51 3306
排错步骤二：测试用户是否可以远程登录
mysql -uroot -proot -h10.0.0.51 -P3306
排错步骤三：需要确定 windows 主机地址是否和远程用户白名单匹配
select user,host from mysql.user;
例如：如果 select 出的 root 用户白名单是 root@'localhost' 则 windows 无法进行远程连接
解决方法：
USE mysql;
UPDATE user SET host = '%' WHERE user = 'root';
FLUSH PRIVILEGES;
排错步骤四：确定服务器端密码插件设置
当使用第三方软件用用户连接数据库时出现 Public Key Retrieval is not allowed
解决方法：
ALTER USER 'username'@'hostname' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;
排错步骤五：换一个连接工具
程序代码操作连接登录数据库服务
| 序号 | 开发语言 | 连接数据库驱动程序 |
|---|---|---|
| 01 | python | 连接数据库驱动-pymysql |
| 02 | golang | 连接数据库驱动-gomysql |
| 03 | java | 连接数据库驱动-jar |
| 04 | php | 连接数据库驱动-phpmysql |
不同语⾔程序代码连接数据库服务驱动： https://dev.mysql.com/downloads/
数据库服务连接管理⽅式逻辑图:
04 MySQL 数据库服务初始配置
数据库服务的初始化配置操作的⽬的，主要是在某些场景下，想让数据库服务程序启动后，具备⼀些定制化的功能应⽤；
(1) 初始化配置⽅式⼀：源码安装数据库服务
数据库服务源码安装准备阶段：
# 下载数据库服务软件程序（源码程序）
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.12.tar.gz
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-boost-8.0.12.tar.gz
-- 下载链接地址：https://dev.mysql.com/downloads/mysql/
# 处理数据库服务软件依赖
yum -y install wget cmake gcc gcc-c++ ncurses ncurses-devel libaio-devel openssl openssl-devel
# 解压数据库服务软件程序
tar xf mysql-8.0.12.tar.gz
tar zxf mysql-boost-8.0.12.tar.gz -C /usr/local
数据库服务源码安装环境创建：
# 创建数据库服务管理⽤⼾
useradd mysql -s /sbin/nologin
# 创建数据库服务所需⽬录
mkdir -p /usr/local/mysql8.0
-- 数据库服务程序数据存储⽬录
mkdir -p /data/mysql8.0/log
-- 数据库服务⽇志存储⽬录
chown -R mysql:mysql /data/mysql8.0/
chown -R mysql:mysql /usr/local/mysql8.0
数据库服务源码编译安装过程：
# 数据库服务编译安装配置过程（初始化过程）
cd /usr/local/src/mysql-8.0.12
cmake .-DCMAKE_INSTALL_PREFIX=/usr/local/mysql8.0 -DMYSQL_DATADIR=/usr/local/mysql/data -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/mysql-8.0.12 /
DSYSCONFDIR=/etc -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1
DWITH_BLACKHOLE_STORAGE_ENGINE= -DWITH_MYISAM_STORAGE_ENGINE= -DWITH_EMBEDDED_SERVER= -DENABLE_DTRACE= -DENABLED_LOCAL_INFILE=
DDEFAULT_CHARSET=utf mb
# 数据库服务编译安装部署过程
make && make install
数据库服务源码程序启动配置：
vim /etc/my.cnf
. .. 部分信息省略 ...
更详细数据库服务源码部署安装过程参考链接：http://www.hzhcontrols.com/new-641636.html
(2) 初始化配置⽅式⼆：修改数据库配置⽂件
# 修改数据库配置⽂件参数，定义数据库服务功能
vim /etc/my.cnf
配置⽂件加载应⽤读取顺序：
# 通过数据库服务启动命令获取加载⽂件顺序
mysqld --help --verbose | grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
-- 数据库服务加载配置⽂件顺序：
/etc/my.cnf --> /etc/mysql/my.cnf --> /usr/local/mysql/etc/my.cnf --> ~/.my.cnf
# 数据库服务加载特定路径中的配置⽂件信息
配置⽂件特定路径举例：/opt/my.cnf,/data/3306/my.cnf./data/3307/my.cnf,/data/3308/my.cnf
mysqld --defaults-file=/opt/my.cnf &
mysqld_safe --defaults-file=/opt/my.cnf &
-- 利⽤启动脚本加载参数信息，⼿⼯指定加载配置⽂件的路径位置信息
配置⽂件书写格式规范说明：
# 配置⽂件模板信息参考
[root@master ~]# cat /etc/my.cnf -- 获取全部配置参数 mysql --verbose --help
[mysql]
socket=/tmp/mysql.sock
[mysqld] -- 获取全部配置参数 mysql --verbose --help
user=mysql
basedir=/app/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
-- 服务端常⻅标签类型：[mysqld] [mysql_safe] [server]
-- 客⼾端常⽤标签类型：[mysql] [mysqldump] [client]
配置⽂件信息结构说明：
| 序号 | 配置部分 | 配置信息说明 |
|---|---|---|
| 01 | 带有[]部分 | 表⽰配置标签部分，⽤于区分不同程序运⾏的参数，包含服务器端和客⼾端标签信息 |
| [服务端标签] | 负责数据库服务端运⾏参数信息设定 | |
| [客户端标签] | 影响客⼾端与服务连接参数信息设定（只影响本地客⼾端连接，不影响远程客⼾端连接） | |
| 02 | 不带[]部分 | 表⽰配置参数部分 |
配置⽂件信息内容说明：
| 序号 | 配置信息 | 解释说明 |
|---|---|---|
| [mysqld] | 服务端配置标签 | |
| 01 | user=mysql | 表⽰数据库服务管理⽤⼾信息 |
| 02 | basedir=/usr/local/mysql | 表⽰数据库服务程序安装路径 |
| 03 | datadir=/data/3306/data | 表⽰数据库服务数据存储路径 |
| 04 | server_id=6 | 表述数据库服务标识节点编号（主从复制的唯⼀编号） |
| 05 | port=3306 | 表⽰数据库服务启动端⼝设定 |
| 06 | socket=/tmp/mysql.sock | 表⽰数据库服务本地连接⽂件（套接字⽂件） |
| 07 | default_authentication_plugin=mysql_native_password | 表⽰数据库服务加密插件设定，⽤于向下兼容 |
| [mysql] | 客⼾端配置标签（本地连接数据库⽣效） | |
| 01 | socket=/tmp/mysql.sock | 表⽰数据库服务本地连接⽂件（套接字⽂件） |
(3) 初始化配置⽅式三：设置数据库启动命令
# 利⽤数据库服务命令启动服务加参数信息，完成初始化定制操作
mysqld_safe --skip-grant-tables --skip-networking &
说明：在命令加参数信息完成的初始化过程，是会优于配置⽂件中加载的配置参数信息
05 MySQL 数据库服务实例创建
在数据库服务运⾏过程中，是可以存在多实例运⾏概念的，什么是数据库服务的多实例呢？
⼀般在⼀个系统环境中，可以运⾏多个相同的服务程序信息，并且产⽣不同的进程和⽹络端⼝信息，就可以称为多实例概念；
在数据库服务运⾏过程中，也可以启动多个数据库服务程序，产⽣多个数据库服务进程和不同的服务端⼝，形成多实例；
多个数据库服务实例信息中存储的数据库信息是相互隔离和独⽴的，并且利⽤数据库服务多实例可以实现测试与分布式架构需求；
企业数据库服务多实例应⽤架构设计：（主要⽤于⽀持多套业务场景）
数据库服务多实例配置实现过程
步骤⼀：多实例部署环境规划
| 实例信息编号 | 实例服务端⼝ | 实例存储路径 | 实例配置⽂件 | 套接字⽂件 |
|---|---|---|---|---|
| mysql-01 | 端⼝信息: 3307 | /data/3307/data | /data/3307/data/my.cnf | /tmp/mysql3307.sock |
| mysql-02 | 端⼝信息: 3308 | /data/3308/data | /data/3308/data/my.cnf | /tmp/mysql3308.sock |
| mysql-03 | 端⼝信息: 3309 | /data/3309/data | /data/3309/data/my.cnf | /tmp/mysql3309.sock |
步骤⼆：多实例部署环境准备
# 创建数据库多实例所需⽬录
[root@master ~]# mkdir /data/330{7..9}/data -p
# 授权数据库多实例所需权限
[root@master ~]# chown -R mysql.mysql /data/*
步骤三：多实例配置⽂件编写
[root@master ~]# cat /data/3307/my.cnf
[mysqld]
mysqlx=0
user=mysql
port=3307
basedir=/app/mysql
datadir=/data/3307/data
socket=/tmp/mysql3307.sock
[root@master ~]# cat /data/3308/my.cnf
[mysqld]
mysqlx=0
user=mysql
port=3308
basedir=/app/mysql
datadir=/data/3308/data
socket=/tmp/mysql3308.sock
[root@master ~]# cat /data/3309/my.cnf
[mysqld]
mysqlx=0
user=mysql
port=3309
basedir=/app/mysql
datadir=/data/3309/data
socket=/tmp/mysql3309.sock
步骤四：多实初始化
[root@master ~]# mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/app/mysql
[root@master ~]# mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/app/mysql
[root@master ~]# mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/app/mysql
步骤五：多实例服务运⾏启动
[root@master ~]# mysqld_safe --defaults-file=/data/3307/my.cnf &
[root@master ~]# mysqld_safe --defaults-file=/data/3308/my.cnf &
[root@master ~]# mysqld_safe --defaults-file=/data/3309/my.cnf &
利用 service 文件启动
[root@master system]# cat mysql3307.service
[Unit]
Description=MySQL Server
Documentation=mysqld.service
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
[root@master system]# cat mysql3308.service
[Unit]
Description=MySQL Server
Documentation=mysqld.service
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000
[root@master system]# cat mysql3309.service
[Unit]
Description=MySQL Server
Documentation=mysqld.service
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
[root@master system]# systemctl daemon-reload
[root@master system]# systemctl start mysql3307
[root@master system]# systemctl start mysql3308
[root@master system]# systemctl start mysql3309
[root@master system]# systemctl enable mysql3307
[root@master system]# systemctl enable mysql3308
[root@master system]# systemctl enable mysql3309
步骤六：多实例服务端⼝查看
[root@master system]# ss -lntup | grep mysqld
tcp LISTEN 0 70 [::]:33060 [::]:* users:(("mysqld",pid=75504,fd=21))
tcp LISTEN 0 128 [::]:3306 [::]:* users:(("mysqld",pid=75504,fd=23))
tcp LISTEN 0 128 [::]:3307 [::]:* users:(("mysqld",pid=111382,fd=18))
tcp LISTEN 0 128 [::]:3308 [::]:* users:(("mysqld",pid=111618,fd=18))
tcp LISTEN 0 128 [::]:3309 [::]:* users:(("mysqld",pid=111828,fd=18))
步骤七：多实例数据库验证
# 多实例的话要使用本地 sock 文件进入
[root@master system]# mysql -uroot -S /tmp/mysql3307.sock
mysql> select @@port;
+--------+
| @@port |
+--------+
| 3307 |
+--------+
1 row in set (0.00 sec)
[root@master system]# mysql -uroot -S /tmp/mysql3308.sock
[root@master system]# mysql -uroot -S /tmp/mysql3309.sock
06 MySQL 数据库版本升级
随着互联⽹技术的不断发展，很多新的应⽤都会随着企业的需求⽇新⽉异，同样数据库服务也会不断的升级更新；
所以早期很多企业的⽹站架构中，所部署安装的历史版本的数据库服务程序就需要升级迭代，来满⾜新的场景应⽤需求；
但是对于数据库服务的升级不同于其他程序升级，因为数据库服务中保存着企业重要的数据信息，⼀旦升级失败就会造成巨⼤损失；
因此，数据库程序的版本升级⼀定要采⽤⽐较规范正确的⽅式升级，即保证程序升级后的应⽤，同时保证数据的稳定性；
常⻅的数据库服务程序升级⽅式：
| ⽅式 | 升级说明 | 备注说明 |
|---|---|---|
| 单台服务器升级（ Inplace-就地 ） | 数据库服务⼩版本升级 | ⽐如：5.7.20 ->5.7.22 |
| （⻛险较⼤） | 数据库服务⼤版本升级 | ⽐如：5.7.20 ->5.8.20 |
| 迁移服务器升级（Mergeing-迁移） | 数据库服务⼩版本升级 | 安装新版本数据库服务（备份迁移/主从迁移) |
| （需要迁移数据） | 数据库服务⼤版本升级 | 安装新版本数据库服务（备份迁移/主从迁移） |
说明：数据库服务升级时，不管哪个⽅式升级，都应该先做好数据备份，⽅便升级失败的回退。
(1) 数据库服务升级注意事项
- 数据库服务版本升级时，只⽀持在GA（General Availability）版本之间进⾏升级 (正式版)
- 数据库服务版本升级时，⽀持从数据库5.6到5.7再到8.0，跨版本升级，但是需要先将5.6升级到最新⼩版本，在进⾏跨版本升级
- 数据库服务版本升级时，需要提前考虑好版本回退的⽅案，最好升级前做好数据备份（特别是向8.0版本升级）
- 数据库服务版本升级时，制定的升级⽅案和升级步骤，需要尽可能降低数据库服务停机的时间
数据库服务官⽅参考资料：https://dev.mysql.com/doc/refman/8.0/en/upgrade-paths.html
(2) 数据库服务升级过程规划（Inplace）
- 数据库服务数据备份保存（可以采⽤热备和冷备两种⽅案，冷备是需要停⽌业务后备份，热备是⽆需停⽌业务备份）
- 数据库服务最新程序安装（最新版本数据库服务安装过程时，⽆需停⽌原有数据库旧版服务）
- 数据库服务原有程序关闭（⽹站显⽰维护⻚⾯）
- 数据库服务最新程序启动（加载原有程序数据实现挂库升级，并采⽤跳过授权表和跳过⽹络⽅式启动）
- 数据库服务升级数据结构（数据库服务升级程序后，还需要升级数据系统结构信息，因此升级时间和数据量⽆关）
- 数据库服务可以正常重启（数据库服务升级完毕后，确认数据库服务是可以正常完成重启操作）
- 数据库服务功能测试验证（反复核实验证与数据库服务相关的各项功能是否正常）
- 数据库服务升级⼯作完毕（取消⽹站维护⻚⾯，恢复正常⽹站线上运营业务）
(3) 数据库服务升级过程实践
07 MySQL 数据库服务语句应⽤（基础）
(1) 操作管理语⾔概述介绍
什么是SQL语句？
SQL，英⽂全称为Structured Query Language，中⽂意思是结构化查询语⾔（属于编程语⾔）；
它是⼀种对关系数据库中的数据进⾏定义和操作的语⾔，是⼤多数关系数据库管理系统所⽀持的⼯业标准语⾔。
在使⽤SQL语句时，也会⽤到⼏种常⽤的标准：SQL 89 / SQL 92 / SQL 99 / SQL 03
在企业实际应⽤过程，还会根据SQL语⾔操作的⽅式，细化为四种类型：
- DDL Data Definition Language（数据定义语⾔）
概念介绍：
负责管理数据库的基础数据（不会对表的内容修改），⽐如增删库、增删表、增删索引、增删⽤⼾等；
涉及语句：
CREATE（创建）、ALTER（修改）、DROP（删除）等；
关注⼈群：
运维⼈员和开发⼈员都要熟悉。
相关具体的DDL负责的操作⾏为，可以执⾏以下命令进⾏查看：
# 查看获取 DDL 语言的操作行为
mysql> ? Data Definition;
You asked for help about help category: "Data Definition"
For more information, type 'help <item>', where <item> is one of the following
topics:
ALTER DATABASE
ALTER EVENT
ALTER FUNCTION
ALTER INSTANCE
ALTER LOGFILE GROUP
ALTER PROCEDURE
ALTER SCHEMA
ALTER SERVER
ALTER TABLE
ALTER TABLESPACE
ALTER VIEW
CREATE DATABASE
CREATE EVENT
CREATE FUNCTION
CREATE INDEX
CREATE LOGFILE GROUP
CREATE PROCEDURE
CREATE SCHEMA
CREATE SERVER
CREATE SPATIAL REFERENCE SYSTEM
CREATE TABLE
CREATE TABLESPACE
CREATE TRIGGER
CREATE VIEW
DROP DATABASE
DROP EVENT
DROP FUNCTION
DROP INDEX
DROP PROCEDURE
DROP SCHEMA
DROP SERVER
DROP SPATIAL REFERENCE SYSTEM
DROP TABLE
DROP TABLESPACE
DROP TRIGGER
DROP VIEW
FOREIGN KEY
RENAME TABLE
TRUNCATE TABLE
- DCL Data Control Language（数据控制语⾔）
概念介绍：
主要⽤来定义访问权限和安全级别
涉及语句：
GRANT（用户授权）、REVOKE（权限回收）、COMMIT（提交）、ROLLBACK（回滚）
关注⼈群：
运维⼈员需要熟练
相关具体的DCL负责的操作⾏为，可以执⾏以下命令进⾏查看：
# 查看获取 DCL 语言的操作行为
mysql> ? Account Management;
You asked for help about help category: "Account Management"
For more information, type 'help <item>', where <item> is one of the following
topics:
ALTER RESOURCE GROUP
ALTER USER
CREATE RESOURCE GROUP
CREATE ROLE
CREATE USER
DROP RESOURCE GROUP
DROP ROLE
DROP USER
GRANT
RENAME USER
REVOKE
SET DEFAULT ROLE
SET PASSWORD
SET RESOURCE GROUP
SET ROLE
- DML Data Manipulation Language（数据操作语⾔）
概念介绍：
主要针对数据库⾥的表⾥的数据进⾏操作，⽤来定义数据库记录（数据）；
涉及语句：
SELECT（查）、INSERT（增）、DELETE（删）、UPDATE（改）
关注⼈群：
开发⼈员要熟练，运维⼈员熟悉即可
相关具体的DML负责的操作⾏为，可以执⾏以下命令进⾏查看：
# 查看获取 DML 语⾔的操作⾏为
mysql> ? Data Manipulation;
You asked for help about help category: "Data Manipulation"
For more information, type 'help <item>', where <item> is one of the following
topics:
CALL
DELETE
DO
DUAL
HANDLER
IMPORT TABLE
INSERT
INSERT DELAYED
INSERT SELECT
JOIN
LOAD DATA
LOAD XML
PARENTHESIZED QUERY EXPRESSIONS
REPLACE
SELECT
TABLE
UNION
UPDATE
VALUES STATEMENT
- DQL Data Query Language（数据查询语⾔）
概念介绍：
主要⽤来查询记录（数据）
涉及语句：
SELECT（查）
基本结构是由SELECT⼦句，FROM⼦句，WHERE⼦句组成的查询块
关注⼈群：
运维⼈员和开发⼈员都要熟悉。
select version(); 查函数
select @@port; 查看 mysql
(2) 数据库字符编码设置
在使⽤数据库服务时，有时在数据库表中输⼊中⽂信息条⽬时，默认经常会遇到字符乱码问题，输⼊中⽂信息为什么会出现乱码：
系统字符编码概念解释：
计算机，不能直接存储⽂字，存储的是编码。计算机只能处理⼆进制的数据。a -
对于其它数据，⽐如： 0-9、 a-z、 A-Z，这些字符，我们可以定义⼀套规则来表⽰。
假如： A-110表示， B-111表示等。
- ASCII码 ：
ASCII码 ： 00000000 = 0-255 00000001 a 00000010 b
美国发布的，⽤1个字节(8位⼆进制)来表⽰⼀个字符，共可以表⽰2^8=256个字符。 美国的国家语⾔是英语，只要能表⽰0-9、 a-z、 A-Z、特殊符号。
- ANSI编码：
每个国家为了显⽰本国的语⾔，都对ASCII码进⾏了扩展。
⽤2个字节(16位⼆进制)来表⽰⼀个汉字，共可以表⽰2^16＝ 65536个汉字。
中国的ANSI编码是GB2312编码(简体)，对6763汉字进⾏编码，含600多特殊字符。另外还有GBK(简体)。
⽇本的ANSI编码是JIS编码。
台湾的ANSI编码是BIG5编码（繁体）
- GBK：
对GB2312进⾏了扩展，⽤来显⽰罕⻅的、古汉语的汉字。现在已经收录了2.1万左右。并提供了1890个汉字码
位。 K的含义就是“扩展”。
- Unicode编码(统⼀编码)： 4个字节 a - 4字节 4k
⽤4个字节(32位⼆进制)来表⽰⼀个字符，想法不错，但效率太低。例如，字⺟A⽤ASCII表⽰的话⼀个字节就够，
可⽤Unicode编码的话，得⽤4个字节表⽰，造成了空间的极⼤浪费。
A的Unicode编码是：0000 0000 0000 00000000 0000 0100 0000
- UTF-8
编码： 你 11111100000 好 00000011111
根据字符的不同，选择其编码的⻓度。⽐如：⼀个字符A⽤1个字节表⽰，⼀个汉字⽤2个字节表⽰。
毫⽆疑问，开发中，都⽤UTF-8编码吧，准没错。
中⽂能够使⽤的字符集两种：
第⼀种： UTF-8。 UTF-8是国际通⽤字库，⾥⾯涵盖了所有地球上所有⼈类的语⾔⽂字，⽐如阿拉伯⽂、汉
语、⻦语……
第⼆种： GBK（对GB2312进⾏了扩展）。 gb2312 是国标，是中国的字库，⾥⾯仅涵盖了汉字和⼀些常⽤外
⽂，⽐如⽇⽂⽚假名，和常⻅的符号。
字库规模： UTF-8（字很全） > gb2312（只有汉字）
⼤部分情况下，作为中国⽹⺠⽤⼾在向⽹站服务端进⾏数据信息存储时，会经常使⽤到中⽂字符信息；
但是由于数据库服务系统默认的字符编码设置问题，会导致在识别存储的中⽂信息时，出现字符乱码问题；
因此，为了能更准确的识别数据库中的中⽂字符信息，需要对数据库服务配置⽂件中的字符编码设置进⾏调整。 配置修改数据库服务配置⽂件中的默认字符编码设置：
查看获取当前数据库默认字符编码信息：
# 查看获取数据库服务可以使用的字符集
# ⼀般数据库服务中，常规使⽤的字符集编码为utf8、 utf8mb4
mysql> show charset;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| binary | Binary pseudo charset | binary | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.02 sec)
# 查看获取数据库服务默认字符编码
mysql> show variables like "%character%";
+--------------------------+----------------------------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | /app/mysql-8.0.26-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------+
8 rows in set (0.02 sec)
默认情况下，数据库存储数据与数据库服务端识别的字符编码均为：utf8mb4 编码。
说明：数据库服务最新8.0版本的字符集信息为utf8mb4，早期数据库服务版本字符集编码为latin1；
特殊知识点说明：在数据库服务应⽤中 utf8 和 utf8mb4 之间有什么区别？
| 序号 | 区别说明 | 解释说明 |
|---|---|---|
| 01 | 两种字符集的字符存储量不同 | |
| utf8 | 最多存储3字节⻓度字符 张-3字节 | |
| utf8mb4 | 最多存储4字节⻓度字符（表情字符emoji） 张-4字节 |
说明：企业⽣产环境中，建议客⼾端与服务端字符集要统⼀;
模拟存储中⽂数据信息数据库中，查看中⽂字符的显⽰情况：
# 创建新的数据库信息
mysql> create database xiaoq;
Query OK, 1 row affected (0.01 sec)
# 查看新的数据库编码
mysql> show create database xiaoq;
mysql> show create database xiaoq;
+----------+---------------------------------------------------------------------------------------------------------------
| Database | Create Database
+----------+-----------------------------------------------------------------------------------------------------------------
| xiaoq | CREATE DATABASE `xiaoq` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+----------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
# 创建新的数据库表信息
mysql> use xiaoq;
Database changed
mysql> create table a(name char(10));
Query OK, 0 rows affected (0.05 sec)
# 向数据库表中插⼊数据
mysql> insert into a values("oldboy");
# 查看数据库中表的信息
mysql> select * from a;
+--------+
| name |
+--------+
| oldboy |
+--------+
1 row in set (0.00 sec)
编写修改数据库服务配置⽂件字符编码：
# 将数据库服务配置⽂件进⾏备份
[root@xiaoQ ~]# cp /etc/my.cnf{,.ori}
-- my.cnf是数据库服务配置
# 编辑修改服务配置⽂件参数信息
[root@xiaoQ ~]# vim /etc/my.cnf
[mysqld]
character-set-server=utf8
-- 设置服务端字符集编码为utf8
编写修改数据库客⼾配置⽂件字符编码：
# 编辑修改客⼾配置⽂件参数信息
[root@xiaoQ ~]# vim /etc/my.cnf.d/client.cnf
-- client.cnf是客⼾端配置⽂件信息
[client]
default-character-set=utf8
客户端配置不用重启 mysql 服务端要重启
配置编写完后重启数据库服务进⾏查看：
# 重启数据库服务程序
[root@xiaoQ ~]# systemctl restart mysql
# 进⼊数据库系统检查
[root@xiaoQ ~]# mysql -uroot -poldboy
mysql > show variables like "%character%";
数据库服务建库时单独设置字符集：
# 创建数据库时单独设置字符集，默认后续库中创建的表也是这个字符集，存在库表的继承关系
mysql > create database wanghao charset set utf8;
# 查看创建的数据库字符集信息
mysql> show create database wanghao;
+----------+-----------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------------------------------------------+
| wanghao | CREATE DATABASE `wanghao` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-----------------------------------------------------------------------------------------------------+
# 修改创建的数据库字符集
alter database wanghao charset set utf8;
数据库服务建表时单独设置字符集：
# 创建数据库中表时单独设置字符集
mysql> use xiaoQ;
mysql> create table t1(id int) charset set gbk;
# 查看创建的数据表字符集信息
mysql> show create table t ;
+-------+-----------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------+
| t1 | CREATE TABLE `t ` (`id` int DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+-----------------------------------------------------------------------------+
数据库服务字符编码信息调整⽅法：
# 假设数据库表原有字符集为gbk，并且已经存储数据了，需要将表和数据字符集进⾏调整转换utf8-mb4
# ⽅法⼀：
mysql > alter table t1 charset set utf8mb4;
-- 不严谨的⽅法，只会影响之后存储的数据，不会修改之前存储的数据
# ⽅法⼆：
·锁表逻辑导出数据（例如：mysqldump）
·重新创建数据空表（设置⽬标字符集）
·导⼊备份数据信息
-- 严谨的⽅法，可以影响之后存储的数据，也会修改之前存储的数据
-- 字符集转换是可以的，但是必须保证修改后的字符集是修改前的严格超集（包含）
数据库中字符设置参数信息说明：
| 序号 | 参数信息 | 解释说明 |
|---|---|---|
| 01 | character_set_client | ⽤来设置客⼾端使⽤的字符集 |
| 02 | character_set_connection | ⽤来设置连接数据库时的字符集 如果程序中没有指明连接数据库使⽤的字符集类型则按照这个字符集设置。 |
| 03 | character_set_database | ⽤来设置默认创建数据库的编码格式 如果在创建数据库时没有设置编码格式，就按照这个格式设置。 |
| 04 | character_set_filesystem | ⽂件系统的编码格式，把操作系统上的⽂件名转化成此字符集 即把 character_set_client转换character_set_filesystem， 默认binary是不做任何转换 |
| 05 | character_set_results | 数据库给客⼾端返回时使⽤的编码格式，如果没有指明，使⽤服务器默认的编码格式。 |
| 06 | character_set_server | 服务器安装时指定的默认编码格式，这个变量建议由系统⾃⼰管理，不要⼈为定义。 |
| 07 | character_set_system | 数据库系统使⽤的编码格式，这个值⼀直是utf8不需要设置，它是为存储系统元数据的编码格式。 |
| 08 | character_sets_dir | 这个变量是字符集安装的⽬录。 |
根据客⼾端请求数据库服务端字符集转换流程，可以更好的理解以上字符编码配置参数：
01 mysql Server收到请求时将请求数据从 character_set_client 转换为 character_set_connection；
02 进⾏内部操作前将请求数据从 character_set_connection 转换为内部操作字符集；
- 使⽤每个数据字段的 CHARACTER SET 设定值；
- 若上述值不存在，则使⽤对应数据表的字符集设定值；
- 若上述值不存在，则使⽤对应数据库的字符集设定值；
- 若上述值不存在，则使⽤ character_set_server 设定值；
03 最后将操作结果从内部操作字符集转换为 character_set_results
字符集知识扩展：已经发现表中有乱码信息，如何修复
步骤一：需要备份数据表中数据（逻辑备份 mysqldump）
步骤二：清理表中数据信息
步骤三：修改数据库或数据表字符集
步骤四：重新 导入表中数据信息（DML insert）
(3) 数据库服务排序规则设置（校对规则）
在进⾏字符编码设置时，还需要设置校对规则信息，校对规则是什么？
排序规则，就是指字符⽐较时按照字符编码还是直接⽤⼆进制数据⽐较，以及是否区分⼤⼩写。
主要可以根据校对规则定义或设置的不同：在查询数据信息时，影响数据信息的查询输出和排序效果；
校对规则设置功能：
1）保证表中数据查询结果（字符大小写有区别）
2）保证数据在表中排序效果
其中utf8mb4字符集中，常⽤的排序规则有utf8mb4_unicode_ci、utf8mb4_general_ci、utf8mb4_bin：
排序规则前缀是字符集编码，中间是排序规则名称，后缀有特殊意义如下（常⽤的）：
| 排序规则后缀 | 解释说明 |
|---|---|
| _ci | 不区分⼤⼩写，Case-insensitive 的缩写 |
| _cs | 区分⼤⼩写，Case-sensitive 的缩写 |
| _ai | 不区分重⾳，Accent-insensitive 的缩写 |
| _as | 区分重⾳，Accent-sensitive 的缩写 |
| _bin | 采⽤⼆进制⽅式存储数据信息 |
utf8mb4_unicode_ci是基于标准Unicode来排序和⽐较，能够在各种语⾔之间精确排序。且在特殊情况下，Unicode排序规则为了能够处理特殊字符的情况，实现了略微复杂的排序算法。但是
在绝⼤多数情况下不会发⽣此类复杂⽐较。
utf8mb4_general_ci没有实现Unicode排序规则，在遇到某些特殊字符情况下，排序结果可能不⼀致。但是，在绝⼤多数情况下，这些特殊字符的顺序并不需要那么精确。
utf8mb4_bin将字符串的每个字符⽤⼆进制数据编译存储，区分⼤⼩写，⽽且可以存⼆进制的内容。
综合来说，utf8mb4_unicode_ci⽐较准确，utf8mb4_general_ci速度较快。utf8mb4_unicode_ci对于特殊字符的处理，在中⽂、英⽂应⽤中不会使⽤到，除⾮你的应⽤有德语、法语、俄语等，则需要使⽤utf8mb4_unicode_ci，否则⼀般选⽤utf8mb4_general_ci就可以了。
# 查看数据库服务中可⽤的校对规则信息
mysql> show collation;
# 设置数据库服务中可⽤的校对规则信息
mysql> create database xiaoA charset utf8 collate utf8_general_mysql500_ci;
mysql> show create database xiaoA;
# 数据表设置字符编码校对规则
mysql> use xiaoA;
mysql> create table t (id int) charset utf8 collate utf8_german2_ci;
mysql> show create table t1;
(4) 数据库数据类型介绍
数据类型:
数据类型从数据存储底层机制来看，主要和内存中如何存储数据信息有关；
在数据库服务中，每⼀个常量、变量和参数都有数据类型，数据类型⽤来指定数据的存储格式、约束和有效范围
| 序号 | 类型 | 类型细化 | 说明 | |
|---|---|---|---|---|
| 01 | 数字类型 | |||
| 整型（数字/整数） | int | 普通整型数字 | ||
| tinyint | 微⼩整型数字 | |||
| bigint | 超⼤整型数字 | |||
| 浮点（数字/⼩数） | float | 单精度浮点数 | ||
| double | 双精度浮点数 | |||
| decimal | 定点数 | |||
| 02 | 字符串类型 | |||
| 字符（字符/符号/整数） | char（8） | 定⻓字符类型 | ||
| varchar（8） | 变⻓字符类型 | |||
| enum | 枚举类型 | |||
| set | 集合类型 | |||
| text | ⼤⽂本类型 | |||
| 03 | 时间类型 | |||
| date | ⽇期类型 | |||
| time | 时间类型 | |||
| datetime | ⽇期时间类型（1000~9999）占 8 字节 | |||
| timestamp | 时间戳类型（1970~2038）格林威治时间 占 4 字节 | |||
| 04 | ⼆进制类型 | |||
| 05 | json 类型 |
数据类型区别分析：
| 类型 | 数据类型细分 | |
|---|---|---|
| 整数类型 | tinyint | 占⽤1字节 有符号取值 -128~127 ⽆符号取值 0 ~ 255（最⼤3位数） |
| int | 占⽤4字节 有符号取值 -2147483648 ~ 2147483647 ⽆符号取值 0 ~ 4294967295（最⼤10位数） | |
| BIGINT | 占⽤8字节 ... 0~2^64-1（最⼤20位数） |
说明：9位数是亿，10位数是⼗亿，13位数是万亿，14位数是兆，19位数是万兆，20位数是京；
字符类型区别分析：
| 类别 | 数据类型细分 | 差异区别 |
|---|---|---|
| 字符类型 | char(n) | 表⽰定⻓的字符串类型，n表⽰可以存储字符的字节上限（n取值 0~255） |
| varchar(n) | 表⽰变⻓的字符串类型，n表⽰可以存储字符的字节上限（n取值 0~65535） |
详细的数据类型知识参考链接： https://m.php.cn/article/460317.html
数据类型约束与属性：
在数据库服务中进⾏数据存储时，类似于在⼀个execl表中存储数据⼀样，如果没有对表的字段进⾏约束和限制，是可以随意存储数据的；
但是，在数据表的某些字段上，是有特殊含义的，如果随意进⾏存储数据，会造成存储信息的混乱，因此引⼊了约束与属性概念；
通过数据类型设置的约束与属性，可以让数据库服务限制⼈类录⼊的数据信息，从⽽避免录⼊数据信息混乱的局⾯；
并且，通过数据类型的约束与属性设置，还可以避免数据信息输⼊的重复与输⼊数据信息不能为空；
常⻅的约束定义：
| 序号 | 约束⽅法 | 解释说明 |
|---|---|---|
| 01 | PK（primary key） | 表⽰主键约束，⾮空且唯⼀（表中只能有⼀个主键） |
| 02 | UK（unique key） | 表⽰唯⼀约束 |
| 03 | NN（not null） | 表⽰⾮空约束 |
| 04 | FK（foreign key） | 表⽰外键约束，多表之间关联使⽤ |
常⻅的属性定义：
| 序号 | 属性信息 | 解释说明 |
|---|---|---|
| 01 | default | 设定默认数据信息，可以实现⾃动填充 |
| 02 | auto_increment | 设定数值信息⾃增，可以实现数值编号⾃增填充（⼀般配合主键使⽤） |
| 03 | comment | 设定数据注释信息 |
| 04 | unsigned | 设定数值信息⾮负，可以实现数值信息列不能出现负数信息 |
外键应⽤补充说明：
外键也称之为外键约束: foreign key
外键: 外⾯的键, ⼀张表的⼀个字段(⾮主键)指向另外⼀个表的主键, 那么该字段就称之为外键
外键所在的表称之为⼦表(附表); 外键所指向的主键所在的表称之为⽗表(主表)
01 外键设置说明
- 设置添加外键
实现将⼀个表的字段与另外⼀张表的主键进⾏关联(实体与实体之间的联系)，具体增加外键有两种形式：
⽅式⼀：在创建表的时候就增加外键: 在表字段之后使⽤foreign key
# 创建外键语法格式
foreign key(外键字段) references 主表(主键);
# 创建外键关联的⽗表
create table class(
id int primary key auto_increment,
name varchar(10) not null comment "班级名字，不能为空",
room varchar(10) comment '教室：允许为空'
) charset utf8;
# 创建⼦表使⽤外键
create table student(
id int primary key auto_increment,
number char(10) not null unique comment "学号：不能重复",
name varchar(10) not null comment "姓名",
c_id int,
foreign key(c_id) references class(id)
) charset utf8;
# 增加外键：c_id是外键字段，class是引⽤表（⽗表），id是引⽤字段（主键）
查看外键信息是否配置成功：
mysql> desc student;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| number | char(10) | NO | UNI | NULL | |
| name | varchar(10) | NO | | NULL | |
| c_id | int | YES | MUL | NULL | |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
⽅式⼆：在创建表之后增加外键: 指定外键名字
# 创建外键语法格式
alter table 表名 add constraint 外键名 foreign key(外键字段) references ⽗表(主键字段)
# 创建没有外键信息的表
create table t_foreign(
id int primary key auto_increment,
c_id int
)charset utf8;
此时表中（t_foreign）没有创建外键：
mysql> desc t_foreign;
+-------+------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| c_id | int | YES | | NULL | |
+-------+------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
# 在没有外键的表中添加外键
alter table t_foreign add constraint class_foreign foreign key(c_id) references class(id);
此时表中（t_foreign）显⽰了外键信息：
mysql> desc t_foreign;
+-------+------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| c_id | int | YES | MUL | NULL | |
+-------+------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
外键增加条件: 外键字段必须与引⽤表(⽗表主键)的数据类型严格保持⼀致
- 设置删除外键
外键不能被修改，只能先删除后再新增；
# 删除外键语法格式
alter table 表名 drop foreign key 外键名;
# 删除表（t_foreign）中外键信息
alter table t_foreign drop foreign key class_foreign;
删除外键后进⾏信息查看：
删除外键只能删除外键部分，不能删除外键创建的索引部分
mysql> desc t_foreign;
+-------+------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| c_id | int | YES | MUL | NULL | |
+-------+------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> show create table t_foreign\G;
*************************** 1. row ***************************
Table: t_foreign
Create Table: CREATE TABLE `t_foreign` (
`id` int NOT NULL AUTO_INCREMENT,
`c_id` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `class_foreign` (`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
02 外键作⽤说明
外键也称之为外键约束，主要作⽤在于对数据进⾏约束：
约束01：外键对⼦表的数据写操作约束（增加和更新）
如果⼦表中插⼊的数据所对应的外键在⽗表不存在，创建不能成功.
约束02：外键对⽗表也有数据约束
当⽗表操作⼀个记录，但是该记录被⼦表所引⽤的时候，那么⽗表的操作将会被限制(更新: 主键和删除)
(5) 数据库数据模式概念
在数据库服务应⽤过程中存在SQL_mode概念（SQL模式），规范SQL执⾏⾏为和数据的准确性，能够符合数据录⼊常识和执⾏结果意义
例如：⽇期信息不能出现 0000-00-00 信息，⽉份只能是 1-12，⽇期只能是 1-31，⼀旦违反常识便会报错；
例如：在进⾏数据运算时，除法运算时，除数不能为 0；
例如：当定义数据类型为 char(10)，不能超过字符⻓度，超过⻓度就报错；
例如：设置 only_full_group_by（5.7以后的特性），禁⽌进⾏分组查询时，出现聚合信息 1 对多的显⽰输出；
获取 SQLmode 设置的默认信息：
mysql> select @@sql_mode;
SQLmode配置参数信息解释说明：
| 序号 | 模式参数配置 | 解释说明 |
|---|---|---|
| 01 | ONLY_FULL_GROUP_BY | 对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中。 |
| 02 | STRICT_TRANS_TABLES | 在该模式下，如果⼀个值不能插⼊到⼀个事物表中，则中断当前的操作，对⾮事物表不做限制 |
| 03 | NO_ZERO_IN_DATE | 在严格模式下，不允许⽇期和⽉份为零 |
| 04 | NO_ZERO_DATE | 设置该值，mysql数据库不允许插⼊零⽇期，插⼊零⽇期会抛出错误⽽不是警告 |
| 05 | ERROR_FOR_DIVISION_BY_ZERO | 在INSERT或UPDATE过程中，如果数据被零除，则产⽣错误⽽⾮警告。如 果未给出该模式，那么数据被零除时MySQL返回NULL |
| 06 | NO_ENGINE_SUBSTITUTION | 如果需要的存储引擎被禁⽤或未编译，那么抛出错误。不设置此值时，⽤默认的存储引擎替代，并抛出⼀个异常 |
| 07 | NO_AUTO_VALUE_ON_ZERO | 该值影响⾃增⻓列的插⼊。默认设置下，插⼊0或NULL代表⽣成下⼀个⾃增⻓值。如果⽤⼾希望插⼊的值为0，该列⼜是⾃增⻓的，那么这个选项就有用了。 |
| 08 | NO_AUTO_CREATE_USER | 禁⽌GRANT创建密码为空的用户。 |
| 09 | PIPES_AS_CONCAT | 将“||”视为字符串的连接操作符⽽⾮或运算符，这和Oracle数据库是⼀样的，也和字符串的拼接函数Concat相类似 |
| 10 | ANSI_QUOTES | 启⽤ANSI_QUOTES后，不能⽤双引号来引⽤字符串，因为它被解释为识别符 |
当进⾏数据库服务版本升级时，可能之前版本数据信息不能满⾜新版本数据库服务的 SQL_mode 信息设定，需要暂时设置 SQLmode 为空
mysql> set global sql_mode='';
# 配置完毕后，可以重新登录数据库服务进⾏检查确认
08 MySQL 数据库服务语句应⽤（实践）
(1) 操作管理语⾔获取帮助
在数据库服务中，SQL语句涉及到的语句⾮常的多，在实际应⽤过程中也未必都能记住，因此就需要掌握获取帮助的⽅法；
# 获取帮助信息_基本帮助信息
mysql> \h;
# 获取帮助信息_语句分类帮助
mysql> help contents;
mysql> ? contents;
# 获取帮助信息_具体语句帮助
mysql> ? create
mysql> ? create database;
(2) 操作管理语句应⽤实践（DDL）
利⽤数据定于语⾔（DDL），负责管理数据库的基础数据（不会对表的内容修改），⽐如增删库、增删表、增删索引、增删⽤⼾等；
数据定义语⾔对数据库定义
数据库中的库是数据库服务结构中的重要组成部分，⼀个库就像是⼀个excel⽂档，库⾥含有表,⼀个表就是⼀个excel的sheet；
因此，对于数据库管理操作SQL语句命令，属于⽐较基础的数据库操作能⼒，需要重点关注；
- 创建数据库信息：
# 创建新的数据库
mysql > create database oldboy;
# 创建新的数据库，并修改调整默认的字符编码
mysql > create database oldboy character set utf8mb4;
mysql > create database oldboy charset utf8 collate utf8_general_mysql500_ci;
# 查看是否已经创建好
mysql > show databases;
# 查看创建库的语句信息
mysql > show create database oldboy;
- 查看数据库信息：
# 查看所有数据库信息
mysql > show databases;
# 检索查看指定的数据库信息
mysql > show databases like '%xiao%';
# 查看创建库的语句信息
mysql > show create database oldboy;
数据库安装完毕后，默认的数据库说明：
| 序号 | 数据库名称 | 作用说明 |
|---|---|---|
| 01 | information_schema | 系统运⾏状态，性能等的库 |
| 02 | mysql | 授权权限、⽤⼾管理的库 |
| 03 | performance_schema | 系统运⾏状态，性能等的库 |
说明：以上三个是数据库系统中默认的数据库，可以⽤于管理应⽤。
- 修改数据库信息：
# 修改数据库服务字符集编码信息与字符编码排序规则信息
mysql > alter database test charset utf8mb4;
mysql > alter database test charset utf8 collate utf8_general_mysql500_ci;
- 删除数据库信息：
# 删除数据库信息（在⽣产环境⼀定慎⽤）
mysql > drop database test;
mysql > drop schema test;
- 切换数据库信息：
# 在已有数据库之间进⾏切换
mysql > use xiaoq
Database changed
# 查看当前所在数据库信息
mysql > select database();
+--------------+
| database() |
+--------------+
| xiaoq |
+--------------+
row in set ( . sec)
数据定义语句定义数据库规范说明：
- 创建数据库名称规范：要和业务有关，不要有⼤写字⺟（为了多平台兼容），不要数字开头，不要含有系统关键字信息；
- 创建数据库明确字符：创建数据库时明确（显⽰）的设置字符集信息，为了避免跨平台兼容性与不同版本兼容性问题；
- 删除数据库操作慎⽤：在对数据库进⾏删除操作时，⼀定要经过严格审计后再进⾏操作，并且数据库普通⽤⼾不能有drop权限；
数据定义语⾔对数据表定义
数据库中的表也是数据库服务结构中的重要组成部分；
- 创建数据表信息：
通过数据库服务管理⼯具，图形操作创建数据库表信息:
完整建表语句参考：
CREATE TABLE `student` (
`id` int NOT NULL COMMENT '学号信息',
`name` varchar(45) NOT NULL COMMENT '学⽣名',
`age` tinyint unsigned NOT NULL COMMENT '学⽣年龄',
`gender` enum('M','F','N') NOT NULL DEFAULT 'N' COMMENT '学⽣性别',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学⽣表'
创建表的基本语法格式：
create table <表名> (
<字段名1> <类型1> 字段部分约束 字段属性,
…
<字段名n> <类型n> 字段部分约束 字段属性，字段索引设置（主键索引 辅助索引 联合索引）
) 引擎设置 字符集设置 校队规则设置;
以上是创建表的具体格式信息，其中 create table 是关键字，不能更改，但是⼤⼩写可以变化。
- 查看数据表信息：
mysql > use mysql; # 切换到指定数据库中
mysql > show tables; # 查看数据库中所有表信息
mysql > desc stu1; # 查看数据库中指定表数据结构信息
mysql > show create table stu1; # 查看数据库中指定表创建语句信息
- 修改数据表信息：
修改数据表属性信息：
# 修改数据表名称信息
mysql > rename table stu1 to stu2;
或者
mysql > alter table stu2 rename stu3; # 利⽤上⾯种⽅式均可修改表名称信息
mysql > show tables; # 查看表名称信息是否修改
# 修改数据表编码信息
mysql > alter table stu1 charset utf8mb4; # 修改表结构中字符集编码信息
mysql > show create table stu1; # 查看表字符编码信息情况
修改数据表结构信息：
# 数据表结构调整命令语法
mysql > alter table <表名> add column <字段名称> <数据类型> <约束与属性> [comment '注释'] [选项参数];
-- 利⽤alter在数据表中添加新的表结构字段
mysql > alter table <表名> drop column <字段名称>;
-- 利⽤alter在数据表中删除已有表结构字段
mysql > alter table <表名> modify column <字段名称> <数据类型> <约束与属性> [comment '注释'] [选项参数];
-- 利⽤alter在数据表中修改已有表结构字段（数据类型 约束与属性）
mysql > alter table <表名> change column <旧字段名称> <新字段名称> <数据类型> <约束与属性>[comment '注释'] [选项参数];
-- 利⽤alter在数据表中修改已有表结构字段（字段名称 数据类型 约束与属性）
mysql > alter table <表名> drop index <字段名称> ;
-- 利⽤alter在数据表中删除已有表结构字段（约束与属性）
# 具体实际操作过程（添加新的表结构字段）
- 删除数据表信息：
# 数据表删除命令语法
mysql > drop table <表名>；
# 具体实际操作过程
mysql > drop table stu1;
-- 删除操作过程，会将定义的表结构和表中数据内容⼀并删除
mysql > truncate table stu1;
-- 删除操作过程，只是清空表中数据内容，但保留定义的表结构信息
数据定义语句定义数据表规范说明：
- 创建数据表名称规范：要和业务有关（含库前缀），不要有⼤写字⺟，不要数字开头，不要含有系统关键字信息，名称不要太⻓；
- 创建数据表属性规范：属性信息显⽰设置，引擎选择InnoDB，字符集选择utf8/utf8mb4，表信息添加注释；
- 创建数据列属性规范：名称要有意义，不要含有系统关键字信息，名称不要太⻓；
- 创建数据类型的规范：数据类型选择合适的、⾜够的、简短的；
- 创建数据约束的规范：每个表中必须都要有主键，最好是和业务⽆关列，实现⾃增功能，建议每个列都⾮空（避免索引失效）/加注释
- 删除数据表操作规范：对于普通⽤⼾不能具有删表操作，需要操作删表时需要严格审核
- 修改数据表结构规范：在数据库8.0之前，修改数据表结构需要在业务不繁忙时进⾏，否则会产⽣严重的锁
- 如果出现紧急修改表结构信息需求时，可以使⽤⼯具进⾏调整，⽐如使⽤：pt-osc、gh-ost，从⽽降低对业务影响
企业数据库⾯试题⽬分析练习：
01 请查看以下建表语句给出规范和优化建议：(物流公司⽇常⼯作表信息)
create table 't_area_distribution_cost' (
'id' bigint(20) not null auto_increment comment '主键',
'city_id' varchar(200)
'city_name' varchar(200),
'warehouse_id' varchar(200),
'warehouse_name' varchar(200),
'station_region_id' varchar(200),
'station_region_name' varchar(200),
'replenish_type' varchar(200),
'distribution_cost' varchar(200)
'c_t' varchar(200), default '0' comment '创建时间',
'create_user' varchar(200) default '0' comment '创建⼈ID',
'creater' varchar(200) comment '创建⼈',
'u_t' varchar(200) default '0' comment '修改时间',
'update_user' varchar(200) default '0' comment '修改⼈ID',
'updater' varchar(200),
'is_deleted' varchar(200) comment '删除标记(1 ，删除；0，不删除，有效)',
primary key ('id'),
key 'i_abc_city_id' ('city_id') comment '城市ID索引',
key 'i_abc_warehouse_id' ('warehouse_id'),
key 'i_abc_station_region_id' ('station_region_id')
) ENGINE=innodb default charset=utf8 comment='区域配送运费设置';
问题解答分析：
# 修改建议01：表明信息略⻓可以进⾏调整
create table 't_area_distribution_cost'
# 修改建议02：数据类型信息设定尽量合适
'city_id' varchar(200)
'city_name' varchar(200)
# 修改建议03：定义索引信息没有设置⾮空
'city_id' varchar(200)
'warehouse_id' varchar(200)
'station_region_id' varchar(200)
# 修改建议04：表中字段列信息可以加注释
'city_id' varchar(200)
'city_name' varchar(200)
'warehouse_id' varchar(200)
'warehouse_name' varchar(200)
'station_region_id' varchar(200)
02 研发同学需要紧急上线，需要 DBA 审核 SQL，请问以下语句需要如何评估后上线执⾏，请写审核 SQL 要点
alter table t_enter_cooperate_info add account_day INT not null default 0 comment '账期天数',
alter table t_enter_cooperate_info add account_detay_day INT not null default 0 comment '账期付款天数',
alter table t_pop_basic add account_day INT not null default 0 comment '账期天数',
alter table t_pop_basic add account_detay_day INT not null default 0 comment '账期付款天数
问题解答分析：
本⾝语句是没有任何问题的，但需要说明，尽量业务繁忙时不要进⾏发布，选择夜⾥业务不繁忙时进⾏发布；
在进⾏ SQL 语句信息审核时，需要了解 SQL 语句的含义和作⽤：
alter table t_enter_cooperate_info add account_day INT not null default 0 comment '账期天数',
alter table t_enter_cooperate_info add account_detay_day INT not null default 0 comment '账期付款天数',
-- 表⽰在t_enter_cooperate_info表中 添加两列信息 并设置相应属性和注释信息
alter table t_pop_basic add account_day INT not null default 0 comment '账期天数',
alter table t_pop_basic add account_detay_day INT not null default 0 comment '账期付款天数'
-- 表⽰在t_pop_basic add表中 添加两列信息 并设置相应属性和注释信息
通过对以上SQL语句信息解读，可以看出语句操作属于DDL操作，线上操作DDL语句可能会产⽣⽐较严重的锁进制等待（死锁问题）；
可以结合企业的数据业务存储的负载压⼒（TPS），可能当前时间段的TPS数值较⾼，原则上不建议进⾏线上操作；
但是考虑到业务需求的紧急情况，建议使⽤PT-osc⼯具进⾏数据库线上操作，减少对线上业务的影响，但不能提⾼操作效率；
(3) 操作管理语句应⽤实践（DML)
数据操作语⾔(DML)，主要针对数据库⾥的表⾥的数据进⾏操作，⽤来定义数据内容信息(数据)；
数据操作语⾔对数据信息调整
- 增加数据内容信息（insert）
添加命令语法格式：
# 数据表数据插⼊命令语法
mysql> insert into <表名> [( <字段名1>[,..<字段名n > ])] values ( 值1 )[, ( 值n )]；
-- 属于表内容信息变更操作，需要按照表结构预先定义好的字段信息插
实际操作命令演⽰：
# 具体实际操作过程
mysql> desc stu1;
mysql> insert into stu1(id,name,age,dept) values( 1,'oldboy',35,'net sec');
-- 插⼊单⾏信息标准⽅法（信息输⼊不要重复，且特定信息不要为空）
mysql> insert into stu1(id,name,age,dept) values(0,'oldboy',35,'net sec');
mysql> insert into stu1(id,name,age,dept) values(null,'oldboy',35,'net sec');
-- 插⼊单⾏信息标准⽅法（⾃增列信息可以填⼊0或null，表⽰默认实现⾃增效果）
mysql> insert into stu1 values(2,'oldgirl',25,'linux');
-- 插⼊单⾏信息可以不含有表字段信息
mysql> insert into stu values(03,'littlegirl',2,'net sec'),(04,'littleboy',1,'Linux');
-- 插⼊多⾏信息可以不含有表字段信息
mysql> insert into stu1 (name,age) values('oldboy',35);
-- 插⼊当⾏信息可以只含部分字段信息，但是省略字段信息必须具有⾃增特性 或 可以为空 或有默认值输⼊
mysql> insert into stu1 values(6,'⽼男孩',32,'python,linux,net sec');
-- 插⼊中⽂信息
- 修改数据内容信息（update）
修改命令语法格式：
# 数据表数据修改命令语法
mysql> update 表名 set 字段=新值,… where 条件；
-- 属于表内容信息变更操作，需要按照表结构预先定义好的字段信息修改，并且按照条件修改，默认全表修改
实际操作命令演⽰：
# 具体实际操作过程
mysql> update stu1 set name="zhangsan" where id=6;
-- 修改表数据内容标准⽅式，修改时⼀定要加条件信息（条件信息建议为主键或具有唯⼀性信息）
# 检查信息是否修改成功
mysql> select * from stu1;
知识扩展：禁⽌修改命令不加条件信息执⾏命令：
服务端禁⽌不带 where 条件操作数据库表有两种⽅法:
利⽤ sql_safe_updates 配置参数，表⽰在 delete,update 操作中：
没有 where 条件，当 where 条件中列没有索引可⽤，且⽆ limit 限制时会拒绝更新。
# ⽅法1：临时执⾏
mysql> set global sql_safe_updates=1;
-- 退出重新登陆⽣效
mysql> update stu set sname='oldboy';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
-- 配置效果展⽰
# ⽅法2：永久⽣效
[root@db ~]# vi /etc/my.cnf
[mysqld]
init-file=/opt/init.sql
# 新建脚本
echo 'set global sql_safe_updates=1;' >/opt/init.sql
chmod +x /opt/init.sql
/etc/init.d/mysqld restart
[root@db ~]# mysql -uroot -poldboy -e "select @@global.sql_safe_updates"
+-------------------------------------+
| @@global.sql_safe_updates |
+-------------------------------------+
| 1 |
+-------------------------------------+
客⼾端禁⽌不带 where 条件操作数据库表有两种⽅法:
# ⽅法⼀：把safe_updates=加⼊到my.cnf的client标签下
[root@db ~]# vim /etc/my.cnf
[mysql]
socket=/tmp/mysql.sock
safe_updates=1
-- 客⼾端配置信息
mysql> update stu set sname='oldboy01';
ERROR (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
-- 配置效果展⽰
# ⽅法2：设置数据库别名操作⽅式
alias mysql='mysql -U'-U, --safe-updates Only allow UPDATE and DELETE that uses keys
-- 表⽰以安全更新模式登录数据库，并放⼊/etc/profile永久⽣效。
mysql> update stu set sname='oldboy ';
ERROR (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
-- 配置效果展⽰
- 删除数据库中数据信息：
删除命令语法格式：
# 数据表数据删除命令语法
mysql> delete from 表名 where 表达式；
-- 属于表内容信息变更操作，需要按照表结构预先定义好的字段信息删除，并且按照条件删除，默认全表删除
实际操作命令演⽰：
# 具体实际操作过程
mysql> delete from stu1 where id=6;
mysql> delete from stu1 where id<3;
mysql> delete from stu1 where age=1 or age=2;
mysql> delete from stu1 ;
-- 删除表信息时，如果不加条件会进⾏逐⾏删除全表信息（效率⽐较慢）
# 检查信息是否删除成功
mysql> select * from stu1;
删除数据库信息扩展：伪删除操作
由于执⾏删除语句信息时，有可能会对⼀些业务数据造成影响，甚⾄可能会将表中所有数据清空，虽然可以通过⽇志信息恢复（闪回）
但是整体操作过程还是⽐较危险的，因此在进⾏数据信息删除操作时，可以利⽤伪删除操作代替真实删除操作；
⼀般在数据库中删除数据信息，是因为从业务层⾯有些数据不想被查询获取到，伪删除就是不让查询时可以获取想要删除的数据；
伪删除的本质：利用 update 替代 delete
可以在相应表中添加状态列信息，可以将状态列设置为：1表⽰存在 0表⽰不存在
在进⾏伪删除操作时，只是将状态列信息改为0，但是并没有把相应⾏的数据信息删除，但是在查询时可以忽略状态列为0的信息；
这样可以有效规避误删除操作对业务数据的影响，万⼀伪删除操作有问题，可以再将状态列信息0改为1即可
# 真实删除数据信息操作举例
mysql> delete from stu1 where id=6;
# 伪删除数据信息操作举例
mysql> alter table stu1 add state tinyint not null default 1;
-- 在原有表中添加新的状态列
mysql> update stu1 set state=0 where id=6;
-- 将原本删除列信息的状态改为0，实现伪删除效果
mysql> select * from stu1 where state=1;
-- 实现查询时不要获取状态为0的信息，即不查看获取伪删除数据信息
企业数据库⾯试题⽬分析练习：
01 请解释说明以下语句之间的区别？
drop table stu;
truncate table stu;
delete from stu;
| 区别分析 | drop table stu; | truncate table stu; | delete from stu; |
|---|---|---|---|
| 功能效果 | 删除表结构+数据 | 删除表数据（释放空间） | 删除表数据（标记删除） |
| 删除逻辑 | 彻底删除 | 物理删除（段区⻚层⾯删除） | 逻辑删除（逐⾏删除） |
| 删除效率 | 效率快（和数据量⽆关） | 效率快（和数据量⽆关） | 效率慢（和数据量有关） |
| ⾃增影响 | 新增⾃增序列 | 重置⾃增序列（释放⾼⽔位线） | 延续⾃增序列 |
| 数据恢复? | 利⽤⽇志⽂件恢复 | 利⽤备份恢复/延时从库恢复 | 利⽤⽇志⽂件恢复（快速） |
知识扩展：⾃增列信息值调整⽅法：
mysql> alter table stu auto_increment=10;
(4) 操作管理语句应⽤实战（DQL）
数据查询语⾔（DQL）,主要⽤来查询记录（数据），在实际应⽤过程中也会有多种查询使⽤⽅法：
查询获取服务配置信息
在利⽤select语句获取查询数据库服务的配置信息，可以理解为select命令单独使⽤；
查询命令语法格式：
# 查询获取信息命令语法
mysql> select @@配置参数信息;
mysql> show variables like '检索的配置信息'
实际操作命令演⽰：获取系统配置信息
### 查询获取参数配置信息
# 查询数据库服务端⼝配置信息
mysql> select @@port;
+--------+
| @@port |
+--------+
| 3306 |
+--------+
# 查询数据库服务套接字⽂件信息
mysql> select @@socket;
+-----------------+
| @@socket |
+-----------------+
| /tmp/mysql.sock |
+-----------------+
# 查询数据库服务⽐较⻓的配置参数信息
ysql> select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
| 1 |
+----------------------------------+
### 查询获取参数配置信息（模糊查找）
mysql> show variables; -- 查看数据库服务所有配置参数信息
mysql> show variables like 'po%'; -- 查看数据库服务配置信息模糊查找（查找po开头的信息）
mysql> show variables like '%po'; -- 查看数据库服务配置信息模糊查找（查找po结尾的信息）
mysql> show variables like '%po%'; -- 查看数据库服务配置信息模糊查找（查找含有po的信息）
数据库服务在线调整配置参数⽅法：
# 数据库配置参数在线调整
mysql > set session innodb_flush_log_at_trx_commit=1;
mysql > set sql_log_bin=0;
-- 表⽰在线临时调整配置参数，并且只是当前会话⽣效（session是默认⽅式，不是所有配置都可以调整）
mysql > set global innodb_flush_log_at_trx_commit=1;
-- 表⽰在线临时调整配置参数，并且将会影响所有连接（global是全局⽅式，可以进⾏所有配置调整）
说明：数据库服务配置参数在线调整参数，只是临时⽣效，数据库服务重启后配置会失效，想要永久⽣效需要修改配置⽂件信息
实际操作命令演⽰：获取函数输出信息
### 查询获取函数输出信息
# 查询数据库服务函数输出信息，获取服务版本信息
mysql> select version();
# 查询数据库服务函数输出信息，获取当前⽇期时间
mysql> select now();
# 查询数据库服务函数输出信息，获取拼接函数信息
mysql> select concat(123);
# 拼接函数应⽤演⽰
mysql> select concat(user,"@","'",host,"'") from mysql.user;
+-------------------------------+
| concat(user,"@","'",host,"'") |
+-------------------------------+
| oldboy_r@'%' |
| oldboy_rw@'%' |
| root@'%' |
| wanghao@'%' |
| mysql.infoschema@'localhost' |
| mysql.session@'localhost' |
| mysql.sys@'localhost' |
| oldboy@'localhost' |
| user01@'localhost' |
| user02@'localhost' |
| wh@'localhost' |
+-------------------------------+
查询获取服务数据信息（单表查询）
在利⽤ select 语句获取查询数据库服务的数据信息，可以理解为 select 命令与 from where 等其它⼦句结合使⽤；
查询命令语法格式：
# 数据表数据查询命令语法
mysql> select <字段1，字段2，...> from <表名> [WHERE 条件] group by <字段1，字段2，...> having 条件 order by 字段 limit 限制信息;
-- 属于表内容信息查询操作，可以获取表中数据⾏信息（⼦句信息需要按顺序书写）
# 查看数据库信息和数据表信息
mysql> show databases；
-- 可以查看到world数据库信息
mysql> use world;
mysql> show tables；
-- 查看数据库中表信息
mysql> desc city;
-- 查看数据库中表结构信息
# 查询数据库表的前⼏⾏记录信息
mysql> select * from city limit5;
实际操作命令演⽰：select+from 结合使⽤情况
use world;
select * from city; -- > 等价于 cat a.txt
select id,name,countrycode,district,population from city;
-- 进⼊world数据库中，查询数据库中city表所有内容信息（企业应⽤不要对⼤表查看所有数据）
select name,population from city; -- > 等价于 awk $1 $2
-- 查询city表中的部分字段信息，显⽰查看城市名称和⼈⼝数量
实际操作命令演⽰：select+from+where 结合使⽤情况
⽅式⼀：定义等值条件信息进⾏数据查询
# 查询中国的所有城市信息，中国代码信息 ”CHN”
mysql > SELECT * FROM city WHERE countrycode='CHN';
# 查询中国的所有城市信息，只关注城市名称和⼈⼝数量列信息
mysql > SELECT NAME,population FROM city WHERE countrycode='CHN';
⽅式⼆：定义区间条件信息进⾏数据查询
可使⽤区间条件表⽰⽅法：
| 序号 | 符号 | 解释说明 |
|---|---|---|
| 01 | < | 表⽰⼩于指定数值的信息作为条件 |
| 02 | > | 表⽰⼤于指定数值的信息作为条件 |
| 03 | <= | 表⽰⼩于等于指定数值的信息作为条件 |
| 04 | >= | 表⽰⼤于等于指定数值的信息作为条件 |
| 05 | != 或者 <> | 表⽰不等于指定数值的信息作为条件 |
# 查询⼤于700万⼈的所有城市信息
mysql > SELECT * FROM city WHERE population>7000000
# 查询⼩于等于1000⼈的所有城市信息
mysql > SELECT * FROM city WHERE population<=1000
⽅式三：定义逻辑条件信息进⾏数据查询
可以使⽤逻辑判断符号，进⾏条件设定查找相应数据信息：
| 序号 | 逻辑判断符号 | 解释说明 |
|---|---|---|
| 01 | and（并且）/ && | 表⽰多个条件均都满⾜才能被查找出来 |
| 02 | or（或者）/ | 表⽰多个条件之⼀满⾜就能被查找出来 |
| 03 | not (取反) / ! | 表⽰查找除过滤查找的信息以外的内容 |
# 查询中国境内，⼤于520万⼈⼝的城市信息
mysql> SELECT * FROM city WHERE countrycode='CHN' AND population>5200000;
# 查询中国和美国的所有城市
mysql> SELECT * FROM city WHERE countrycode='chn' OR countrycode='USA';
# 查询⼈⼝数在100w到200w之间的城市信息
mysql> SELECT * FROM city WHERE population>=1000000 AND population<=2000000;
⽅式四：定义模糊条件信息进⾏数据查询（like）
# 查询国家代号是CH开头的城市信息
mysql> SELECT * FROM city WHERE countrycode LIKE 'CH%';
# 查询国家代号含US内容的城市信息
mysql> SELECT * FROM city WHERE countrycode LIKE '%US%';
-- 在模糊查询时，%符号在前⾯进⾏检索数据时，是不会⾛索引信息进⾏检索的，查询性能较慢
⽅式五：特殊查询条件组合进⾏数据查询（配合in， not in， between and ）
# 查询中国和美国的所有城市
mysql> SELECT * FROM city WHERE countrycode in ('CHN','USA');
-- in的查询条件⽅式表⽰包含意思，实际应⽤更⼴泛
# 查询世界上的所有城市信息，但排除中国和美国的城市不查询
mysql> SELECT * FROM city WHERE countrycode not in ('CHN','USA');
-- not in的查询条件⽅式表⽰排除意思，实际应⽤⽐较少⻅，因为not in不能⾛索引扫描，查询检索性能较慢
# 查询⼈⼝数量在50w-100w之间的城市信息
mysql> SELECT * FROM city WHERE population between 500000 and 1000000;
-- between and的查询条件⽅式是包含边界取值信息的，即包含50w⼈⼝的城市，也包含100w⼈⼝的城市
⽅式六：查询数据信息取消重复信息（distinct）
mysql> select CountryCode from city where CountryCode='USA';
mysql> select Distinct CountryCode from city where CountryCode='USA';
+-----------------+
| CountryCode |
+-----------------+
| USA |
-- 列字段信息必须完全相同内容，才可以实现去重；
⽅式七：查询数据信息为空的内容（is null）
mysql> select * from city where CountryCode is null;
-- 查询国家编码字段为空的信息
mysql> select * from city where CountryCode not null;
-- 查询国家编码字段为⾮空的信息
实际操作命令演⽰：select+from+where+group by+聚合函数（统计函数） 结合使⽤情况
在利⽤select语句查询数据信息，结合group by⼦句可以实现分组查询，并且还必须配合聚合函数对分组查询的数据做相应处理；
数据库服务中常⽤的聚合函数（统计函数）：
| 序号 | 函数信息 | 解释说明 |
|---|---|---|
| 01 | count() | 此函数表⽰对数量信息进⾏统计 |
| 02 | sum() | 此函数表⽰对数值信息进⾏求和 |
| 03 | avg() | 此函数表⽰对数值信息进⾏求平均值 |
| 04 | min() | 此函数表⽰对数值信息进⾏取最⼩值 |
| 05 | max() | 此函数表⽰对数值信息进⾏取最⼤值 |
| 06 | group_concat() | 此函数表⽰输出信息⽆法匹配分组和聚合函数时，进⾏拼接整合显⽰ |
| 07 | distinct | 此指令表⽰作⽤是对表中的单个字段或多个字段去重操作 |
利⽤group by进⾏分组查询的执⾏逻辑分析：
- 根据查询语句信息，取出表中关注的列字段信息；
- 根据查询分组信息，将特定列字段信息进⾏排序，从⽽将分组的⼀致信息整合在⼀起（形成结果集）；
- 根据分组合并信息，结合使⽤的聚合函数，进⾏数值信息运算或统计（⽣成最终结果）；
- 根据分组聚合要求，分组信息输出时必须和分组信息⼀⼀对应，但特殊列⽆法⼀⼀对应输出时，可使⽤group_concat()拼接输出
实际操作命令演⽰：获取分组数据信息进⾏聚合函数处理实践：
# 查询统计每个国家的⼈⼝总数
mysql > select countrycode,sum(population) from world.city group by countrycode;
-- 根据国家信息分组聚合，在将分组后所有城市的⼈⼝数量进⾏sum求和运算，实现国家信息对应⼈⼝总数的1对1关系
# 查询统计每个省份的城市个数
mysql > select district,count(name) from city where countrycode='chn' group by district;
# 查询统计每个省份的城市个数，以及城市名称信息（经常⾯试题考到）
mysql> select district,count(name),name from city where countrycode='chn' group by district;
ERROR (42000): Expression # of SELECT list is not in GROUP BY clause and contains nonaggregated column 'world.city.Name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
-- 由于数据库sql_mode配置了only_full_group_by，由于输出的name信息不能和district信息实现1对1关系，因此报错
mysql> select district,count(name),group_concat(name) from city where countrycode='chn' group by district;
-- 利⽤group_concat()就可以实现没有出现在分组和聚合函数中的字段，采取拼接整合⽅式显⽰，满⾜分组1对1关系
实际操作命令演⽰：select+from+where+group by+聚合函数+having
在利⽤select语句查询数据信息，结合分组和聚合函数处理之后，可以将输出的信息再进⾏过滤处理（having）；
实际操作命令演⽰：对分组聚合后数据进⾏过滤处理
# 查询统计每个国家的⼈⼝总数，只显⽰⼈⼝数量超过1个亿的信息
mysql > select countrycode,sum(population) from world.city group by countrycode having sum(population)>100000000;
实际操作命令演⽰：select+from+where+group by+聚合函数+having+order by
在利⽤select语句查询数据信息，结合分组和聚合函数处理之后，并且再次经过筛选的数据，按照⼀定数值规律排序显⽰信息
实际操作命令演⽰：
# 查询统计每个国家的⼈⼝总数，只显⽰⼈⼝数量超过5千万的信息，并且按照国家⼈⼝总数排序显⽰
mysql > select countrycode,sum(population) from world.city group by countrycode having sum(population)>50000000 order by sum(population);
-- 实现了⼈⼝数量从⼩到⼤排序（升序/正序）
mysql > select countrycode,sum(population) from world.city group by countrycode having sum(population)>50000000 order by sum(population) desc;
-- 实现了⼈⼝数量从⼤到⼩排序（降序/逆序）
实际操作命令演⽰：select+from+where+group by+聚合函数+having+order by+limit
在利⽤select语句查询数据信息，结合分组和聚合函数处理之后经过筛选的数据，按照⼀定数值规律排序显⽰信息，并限制输出内容⾏数 实际操作命令演⽰：
查询获取服务数据信息（多表查询）
在对数据库中数据信息查询时，有些需求情况要获取的数据信息，是通过多个表的数据信息整合获取到的，就称为多表查询；
查询命令语法格式：
# 笛卡尔乘积连接多表：
select * from t ,t ;
# 内连接查询多表：
select * from t1,t2 where t1.列=t2.列;
select * from t1[inner] join t2 on t1.列=t2.列;
# 外连接查询多表：左外连接
select * from t1 left join t2 on t1.列=t2.列;
# 外连接查询多表：右外连接
select * from t1 right join t2 on t1.列=t2.列;
说明：多表查询的最终⽬的是将多张表的信息整合为⼀张⼤表显⽰，并将显⽰的结果信息可以做相应单表的操作处理；
上传加载测试环境：
官⽅数据库测试样例⽂件下载：https://dev.mysql.com/doc/index-other.html
# 创建多表查询所需模拟数据库和数据表信息
CREATE DATABASE school CHARSET utf8;
USE school;
CREATE TABLE student (
sno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname VARCHAR(20) NOT NULL COMMENT '姓名',
sage TINYINT UNSIGNED NOT NULL COMMENT '年龄',
ssex ENUM('f','m') NOT NULL DEFAULT 'm' COMMENT '性别'
) ENGINE=INNODB CHARSET=utf8;
CREATE TABLE course (
cno INT NOT NULL PRIMARY KEY COMMENT '课程编号',
cname VARCHAR(20) NOT NULL COMMENT '课程名字',
tno INT NOT NULL COMMENT '教师编号'
) ENGINE=INNODB CHARSET=utf8;
CREATE TABLE sc (
sno INT NOT NULL COMMENT '学号',
cno INT NOT NULL COMMENT '课程编号',
score INT NOT NULL DEFAULT 0 COMMENT '成绩'
) ENGINE=INNODB CHARSET=utf8;
CREATE TABLE teacher (
tno INT NOT NULL PRIMARY KEY COMMENT '教师编号',
tname VARCHAR(20) NOT NULL COMMENT '教师名字'
) ENGINE=INNODB CHARSET=utf8;
# 在数据库与数据表中插⼊模拟数据
INSERT INTO student(sno,sname,sage,ssex)
VALUES
( 1,'zhang3',18,'m'),
( 2,'zhang4',18,'m'),
( 3,'li4',18,'m'),
( 4,'wang5',19,'f'),
( 5,'zh4',18,'m'),
( 6,'zhao4',18,'m'),
( 7,'ma6',19,'f'),
( 8,'oldboy',20,'m'),
( 9,'oldgirl',20,'f'),
(10,'oldp',25,'m');
INSERT INTO teacher(tno,tname)
VALUES
(101,'oldboy'),
(102,' xiaoQ'),
(103,' xiaoA'),
(104,' xiaoB');
INSERT INTO course(cno,cname,tno)
VALUES
(1001,' linux',101),
(1002,' python',102),
(1003,' mysql',103),
(1004,' go',105);
INSERT INTO sc(sno,cno,score)
VALUES
(1,1001,80),
(1,1002,59),
(2,1002,90),
(2,1003,100),
(3,1001,99),
(3,1003,40),
(4,1001,79),
(4,1002,61),
(4,1003,99),
(5,1003,40),
(6,1001,89),
(6,1003,77),
(7,1001,67),
(7,1003,82),
(8,1001,70),
(9,1003,80),
(10,1003,96);
SELECT * FROM student;
SELECT * FROM teacher;
SELECT * FROM course;
SELECT * FROM sc;
多表查询⽅式类型：笛卡尔乘积
实现局域teacher表与course表进⾏多表关联;
mysql> desc teacher;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| tno | int | NO | PRI | NULL | |
| tname | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> desc course;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| cno | int | NO | PRI | NULL | |
| cname | varchar(20) | NO | | NULL | |
| tno | int | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
多表关联实际操作：
mysql> select * from teacher;
+-----+--------+
| tno | tname |
+-----+--------+
| 101 | oldboy |
| 102 | xiaoQ |
| 103 | xiaoA |
| 104 | xiaoB |
+-----+--------+
4 rows in set (0.00 sec)
mysql> select * from course;
+------+--------+-----+
| cno | cname | tno |
+------+--------+-----+
| 1001 | linux | 101 |
| 1002 | python | 102 |
| 1003 | mysql | 103 |
| 1004 | go | 105 |
+------+--------+-----+
4 rows in set (0.00 sec)
# 多表关联查询
# 默认⽅式多表查询时，会出现组合乘积效果（4*4=16）
mysql> select * from teacher,course;
+-----+--------+------+--------+-----+
| tno | tname | cno | cname | tno |
+-----+--------+------+--------+-----+
| 104 | xiaoB | 1001 | linux | 101 |
| 103 | xiaoA | 1001 | linux | 101 |
| 102 | xiaoQ | 1001 | linux | 101 |
| 101 | oldboy | 1001 | linux | 101 |
| 104 | xiaoB | 1002 | python | 102 |
| 103 | xiaoA | 1002 | python | 102 |
| 102 | xiaoQ | 1002 | python | 102 |
| 101 | oldboy | 1002 | python | 102 |
| 104 | xiaoB | 1003 | mysql | 103 |
| 103 | xiaoA | 1003 | mysql | 103 |
| 102 | xiaoQ | 1003 | mysql | 103 |
| 101 | oldboy | 1003 | mysql | 103 |
| 104 | xiaoB | 1004 | go | 105 |
| 103 | xiaoA | 1004 | go | 105 |
| 102 | xiaoQ | 1004 | go | 105 |
| 101 | oldboy | 1004 | go | 105 |
+-----+--------+------+--------+-----+
16 rows in set (0.00 sec)
多表查询的逻辑思路
for each row in a
for each row in b
合并成⼀⾏ print
多表查询⽅式类型：内连接（取交集）
可以基于笛卡尔乘积⽅式的结果集，将有意义的信息进⾏展⽰，并且是基于两张表⾥的相同含义字段，进⾏⽐较后输出相等的结果信息； 内连接查询的简单描述：两个表中有关联条件的行显示出来；
# ⽐较传统的SQL 的内连接标准⽅式
mysql> select * from teacher,course where teacher.tno=course.tno;
+-----+--------+------+--------+-----+
| tno | tname | cno | cname | tno |
+-----+--------+------+--------+-----+
| 101 | oldboy | 1001 | linux | 101 |
| 102 | xiaoQ | 1002 | python | 102 |
| 103 | xiaoA | 1003 | mysql | 103 |
+-----+--------+------+--------+-----+
# ⽐较新颖的SQL 的内连接使⽤⽅式
mysql> select * from teacher join course on teacher.tno=course.tno;
+-----+--------+------+--------+-----+
| tno | tname | cno | cname | tno |
+-----+--------+------+--------+-----+
| 101 | oldboy | 1001 | linux | 101 |
| 102 | xiaoQ | 1002 | python | 102 |
| 103 | xiaoA | 1003 | mysql | 103 |
+-----+--------+------+--------+-----+
多表查询的逻辑思路（内连接）
for each row in a
for each row in b
if ax=b.y print row
多表查询⽅式类型：外连接（应⽤更⼴泛）
利⽤外连接查询时，是可以进⾏性能优化处理的，因为内连接在底层查询时，是逐⾏进⾏⽐较后输出，整体数据查询检索的效率较低；
- 外连接可以细分为：左外连接-left join on
左外连接表⽰查询数据结构包含：左表所有数据⾏+右表满⾜关联条件的⾏；
# 左连接查询语法
a left join b on a.x = b.x
-- a表⽰左表，b表⽰右表，基于左表a建⽴关联
# 实际操作演⽰过程
# 包含了左表的所有数据⾏信息（teacher），包含了右表的关联数据⾏信息（course）
mysql> select * from teacher left join course on teacher.tno=course.tno;
+-----+--------+------+--------+------+
| tno | tname | cno | cname | tno |
+-----+--------+------+--------+------+
| 101 | oldboy | 1001 | linux | 101 |
| 102 | xiaoQ | 1002 | python | 102 |
| 103 | xiaoA | 1003 | mysql | 103 |
| 104 | xiaoB | NULL | NULL | NULL |
+-----+--------+------+--------+------+
外连接⽅式左连接与右连接区别举例：
# 会将左表作为驱动表，进⾏外层循环
for each row in a
for each row in b
i f a.x=b.y print row
else print a.x b.null
- 外连接可以细分为：右外连接-right join on
右外连接表⽰查询数据结构包含：右表所有数据⾏+左表满⾜关联条件的⾏；
# 右连接查询语法
a right join b on a.x = b.x
-- a表⽰左表，b表⽰右表，基于右表b建⽴关联
# 实际操作演⽰过程
mysql> select * from teacher right join course on teacher.tno=course.tno;
+------+--------+------+--------+-----+
| tno | tname | cno | cname | tno |
+------+--------+------+--------+-----+
| 101 | oldboy | 1001 | linux | 101 |
| 102 | xiaoQ | 1002 | python | 102 |
| 103 | xiaoA | 1003 | mysql | 103 |
| NULL | NULL | 1004 | go | 105 |
+------+--------+------+--------+-----+
外连接⽅式左连接与右连接区别举例：
# 会将右表作为驱动表，进⾏外层循环
for each row in b
for each row in a
i f b.y=a.x print row
else print b.y a.null
多表连接查询的步骤思路：
- 进⾏需求分析，根据查询需求找寻所有需要的表信息；
- 找寻表的关联，根据多张表字段信息获取关联的字段；（也可以查询间接关系）
- 组合后的需求，根据多张表组合后定义查询条件信息；
多表查询信息练习题⽬分析
01 统计zhang3，学习了⼏⻔课？
# 根据需求所需的表信息
student course sc
-- 需要先将student与sc合成⼀张表，才能在和course建⽴关联；
student sc
-- 根据题意也可以通过学⽣的成绩信息，统计学⽣所学的课程数量；
# 建⽴表之间关联
select * from student join sc on student.sno=sc.sno;
# 依据组合后的⼤表进⾏处理
mysql> select student.sname,count(*) from student join sc on student.sno=sc.sno group by student.sno;
mysql> select student.sname,count(*) from student join sc on student.sno=sc.sno group by student.sno having student.sname='zhang3';
mysql> select student.sname,count(*) from student join sc on student.sno=sc.sno where student.sname='zhang3' group by student.sno;
# 01 统计zhang3，学习了几门课？
步骤二：根据需求获取需要的表信息
student sc course
步骤三：将多张表信息进行拼接
select * from student
join sc
on student.sno=sc.sno
join course
on sc.cno=course.cno
步骤四：根据单表数据信息进行过滤
select student.sname,count(course.cname) from student
join sc
on student.sno=sc.sno
join course
on sc.cno=course.cno
where student.sname='zhang3';
# 02 查询zhang3，学习的课程名称有哪些？
步骤二：根据需求获取需要的表信息
student sc course
步骤三：将多张表信息进行拼接
select * from student
join sc
on student.sno=sc.sno
join course
on sc.cno=course.cno
步骤四：根据单表数据信息进行过滤
select student.sname,course.cname from student
join sc
on student.sno=sc.sno
join course
on sc.cno=course.cno
where student.sname='zhang3';
# 03 查询xiaoA老师教的学生名？
步骤二：根据需求获取需要的表信息
teacher course sc student
步骤三：将多张表信息进行拼接
select * from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
join student
on sc.sno=student.sno;
步骤四：根据单表数据信息进行过滤
select teacher.tname,student.sname from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
join student
on sc.sno=student.sno
where teacher.tname='xiaoA';
select teacher.tname,group_concat(student.sname) from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
join student
on sc.sno=student.sno
where teacher.tname='xiaoA'
group by teacher.tname;
# 04 查询xiaoA老师教课程的平均分数？
# 05 每位老师所教课程的平均分，并按平均分排序？
# 06 查询xiaoA老师教的不及格的学生姓名？
# 07 查询所有老师所教学生不及格的信息？
多表查询过程别名应⽤：
在进⾏数据信息查询时，有些表和有些字段会被经常调⽤到，⽽且⽣成环境中表明和字段名会⽐较的复杂，在调⽤时不是很⽅便；
⽽且有些时候，查询的SQL语句信息会出现在代码中，在编写代码时也会不太规范，同时也不⽅便阅读，因此出现了数据库别名概念；
在进⾏数据库别名应⽤时，会经常⽤到两种别名：
- 表别名：（应⽤更⼴泛）
列别名：
多表联合查询数据-纵向拼接
MySQL UNION ⽤于把来⾃多个 SELECT 语句的结果组合到⼀个结果集合中，并消去表中任何重复⾏；
MySQL UNION 内部的 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型；
同时，每条SELECT语句中的列的顺序必须相同
语法为：
SELECT column,... FROM table
UNION
SELECT column,... FROM table ...
在多个 SELECT 语句中，对应的列应该具有相同的字段属性，且第⼀个 SELECT 语句中被使⽤的字段名称也被⽤于结果的字段名称。
- UNION 结果集中的列名总是等于 UNION 中第⼀个SELECT 语句中的列名；
- UNION 内部的SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型，每条SELECT语句中的列的顺序必须相同；
UNION 与 UNION ALL 的区别
当使⽤ UNION 时，MySQL 会把结果集中重复的记录删掉，⽽使⽤ UNION ALL ，MySQL 会把所有的记录返回，且效率⾼于 UNION。 默认地，UNION 操作符选取不同的值，如果允许重复的值，请使⽤UNION ALL;
当ALL随UNION⼀起使⽤时（即 UNION ALL）,不消除重复⾏；
语法为：
SELECT column,... FROM table
UNION ALL
SELECT column,... FROM table ...
UNION 与 UNION ALL 的⽤法及注意事项
UNION 表⽰联合的意思，即把两次或多次查询结果合并起来;
两次查询的列数必须⼀致，列的类型可以不⼀样，但推荐查询的每⼀列，相对应的类型⼀样；
可以来⾃多张表的数据，多次SQL语句取出的列名可以不⼀致，此时以第⼀个SQL语句的列名为准；
如果不同的语句中取出的⾏，有完全相同（表⽰的是每个列的值都相同），那么union会将相同的⾏合并，最终只保留⼀⾏实现去重
如果不想去掉重复的⾏，可以使⽤union all；
如果⼦句中有order by、limit，需要括号包起来，推荐放在所有⼦句之后，即对最终合并的结果来排序或筛选
语法为：
(select * from a order by id) union (select * from b order id);
查询获取服务数据信息（嵌套查询）
在SQL语⾔中，⼀个SELECT...FROM...WHERE语句会产⽣⼀个新的数据集；
将⼀个查询语句完全嵌套到另⼀个查询语句中的的WHERE或HAVING的条件表达式中，这种查询称为嵌套查询；
通常把内部的、被另⼀个查询语句调⽤的查询称为⼦查询；
将调⽤⼦查询的查询语句称为⽗查询，⼦查询还可以可以调⽤⼦查询；
SQL语句允许由⼀系列简单查询构成嵌套结构，从⽽实现嵌套查询，极⼤增强了SQL的查询能⼒，使得⽤⼾视图的多样性提升；
不相关⼦查询：
查询所有全球所有国家中，城市⼈⼝数量⼤于中国北京的城市信息；
select Population from city where countrycode='CHN' and name='peking'; -- 7472000
select * from city where Population>7472000;
select * from city where Population>(select Population from city where countrycode='CHN' and name='peking');
相关⼦查询：
⼀条SQL语句含有多个select，先执⾏外查询，再执⾏⼦查询；
⼦查询不可以独⽴运⾏，就称为相关⼦查询；
- 相关⼦查询优势：操作简单，功能强⼤（⼀些使⽤不相关⼦查询不能实现或者实现繁琐的⼦查询，可以使⽤相关⼦查询实现）
- 相关⼦查询缺陷：语句操作稍难理解
# 查询最⾼⼯资的员⼯(不相关⼦查询)
select max(sal) from emp;
select * from emp where sal = (select max(sal) from emp);
⼦查询信息练习题⽬分析
09 MySQL 数据库服务索引知识
(1) 数据库索引相关概念
索引概念介绍：
索引是数据库中⽤来提⾼数据读取性能的常⽤⼯具，所有 mysql 列类型都可以被索引，对相关列使⽤索引；
可以是提⾼ select 操作性能的最佳途径，可以尽可能快的锁定要查询数据的范围，从⽽达到加速查询的⽬的（减少 IO 消耗）；
⼀般索引设置都是应⽤在⽐较⼤的数据表上，⽐如百万级别、千万级别或亿级别的数据表中，从⽽完成⼀些针对性优化；
可以简单理解：数据库索引相当于书的目录，可以借助索引有针对的查看相应数据的信息，避免了全盘检索带来的工作量；
主要利⽤ MySQL 中的索引，可以快速锁定查询范围，mysql 索引⽐较适合范围查找数据;
IOPS:每秒中 IO 消耗数量，此指标数值和硬件配置有关
(2) 数据库索引类型介绍
在 MySQL 数据库服务中，是有很多种索引类型的，但是⽐较常⽤的索引类型主要有：
| 序号 | 类型 | 说明 |
|---|---|---|
| 01 | B+Tree | 默认类型索引（擅于等值和区间查询数据） |
| 02 | Hash | 算法类型索引（擅于等值查询数据） |
| 03 | R+Tree | 空间类型索引（擅于区间查询数据） |
| 04 | Fulltext | 全⽂类型索引（擅于过滤数据查询） |
1）B+Tree ：擅于等值和区间查询数据
2）Hash ：擅于等值查询数据
3）R+Tree ：擅于区间查询数据
4）Fulltext ：擅于过滤查询数据 条件设置 like %ab% # 搜索引擎 技术博文资料
数据库数据查找算法演变：（ B+Tree 索引的由来）
这个举个简单的例⼦，假设现在⼀个教室中有100来号⼈，这时可以派发礼品，通过礼品引诱这100⼈来报名学习xiaoQ⽼师课程；
⽐如礼品是学习课程的1000元代⾦券，现在把这1000元的代⾦券随机放到了1到100号盒⼦其中的⼀个⾥⾯，只有我知道放置的号码；
下⾯要求这100个⼈尽量快的猜到1~100号盒⼦⾥⾯，哪个有放置代⾦券的盒⼦，当然，我会给予⼀些合适的提⽰信息；
这时在场的100号⼈就需要想⼀些办法，在我合适的配合下，定位有代⾦券的盒⼦，想的办法就等价于是查找算法：
⽅法⼀：根据定位的盒⼦编号顺序，询问是与否，这种⽅式就可以理解为是遍历算法（全扫描），也可以理解为随机性算法；
⽅法⼆：根据定位的盒⼦编号⽐较，询问⼤于⼩，这种⽅式就可以理解为是⼆分算法（定范围），也可以理解为⼆叉树算法；
看似这种⼆分算法⽐遍历算法，更加科学，但是如果代⾦券放在了第01号或第100号盒⼦⾥呢，或者⼆分节点两侧时呢？
所以采⽤⼆分法依然会存在数据查询不平衡的问题。
通过以上两种算法的介绍，了解到都存在⼀些缺陷或问题，因此数据库在检索数据信息时，最终采⽤的算法是B+Tree，其中的B表⽰平衡
并且BTREE还可以细分为B-tree或B+tree，以及B++tree，其中的加号就是表⽰增强版或优化版的BTree；
(3) 数据库索引模型结构
1）构建索引模型：hash (哈希表)
索引方式是一种以键-值（key-value）存储数据的结构，只要输入待查找的键即 key，就可以找到其对应的值即 value；
哈希表这种结构适用于只有等值查询的场景，比如 Memcached 及其他一些 NoSQL 引擎
参考教案模型结构和文字说明
2）构建索引模型：有序数组模型
可以根据存储数据信息，将数据进行有序的连续存储，可以进行范围查询数据
也可以进行等值查询数据，采用二分法等值查询数据，在查询过程中时间代价成本更高；
有序数组索引只适用于静态存储引擎，比如你要保存的是 2017 年某个城市的所有人口信息，这类不会再修改的数据。
3）构建索引模型：树型模型
二叉搜索树数据检索模型，存储数据信息特点
- 父节点左子树所有结点的值小于父节点的值；
- 父节点右子树所有结点的值大于父节点的值；