MySQL8.1超详细教程（持续更新建议收藏！）

一土~

已于 2023-08-16 02:55:39 修改

阅读量4.4k

点赞数 11

文章标签： mysql 数据库数据库系统

于 2023-08-07 04:22:15 首次发布

本文链接：https://blog.csdn.net/Leo_J_A_Smith/article/details/132129509

版权

第一章初识数据库

1.1 什么是数据库

数据： 描述事务的符号记录，可以是多种形式包括但不限于数字、文字、图像等，这些数据都可以经过数字化后存入计算机。
数据库： 存储数据的仓库，是长期存放在计算机内、有组织、可共享的大量数据的集合。数据库中的数据按照一定数据模型组织、描述和存储，具有较小的冗余度，较高的独立性和易扩展性，并为各种用户共享，总结为以下几点：

数据结构化
数据的共享性高，冗余度低，易扩充
数据独立性高
数据由DBMS统一管理和控制（安全性、完整性、并发控制、故障恢复）
DBMS（数据库管理系统）
DBMS是所有数据的知识库，并对数据的存储、安全、一致性、并发操作、恢复和访问负责。DBMS有一个数据字典（有时被称为系统表），用于贮存它拥有的每个事物的相关信息，例如名字、结构、位置和类型，这种关于数据的数据也被称为元数据（metadata）。

1.2 数据库分类

关系型数据库
MySQL、DB2、SQL server、武汉达梦、人大金仓
通过表与表之间，行与列之间的关系进行数据的存储的数据库
通过外键来建立表和表之间的关系
关系模型指的是二维表格模型
非关系型数据库
指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定
轻量、开源、不兼容 SQL 功能的数据库
强调 Key-Value 存储和文档数据库的优点

1.3 MySQL体系结构

MySQL由SQL接口，解释器，优化器，缓存，存储引擎组成
连接： 指的是不同语言中与SQL的交互
管理系统： 系统管理和控制工具
连接池： 管理缓冲用户连接，线程处理等需要缓存的需求
SQL接口： 接受用户的SQL命令，并且返回用户需要查询的结果。
解析器： SQL命令传递到解析器的时候会被解析器验证和解析。
查询优化器： SQL语句在查询之前会使用查询优化器对查询进行优化。
查询缓存： 如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据.
存储引擎： 存储引擎是MySql中具体的与文件打交道的子系统。

第二章 MySQL的安装部署

官网下载链接：https://dev.mysql.com/downloads/mysql/
不同版本和操作系统的安装方式各不相同，

2.1 Windows系统的MySQL安装

Windows系统下，常用MSI和ZIP压缩包来进行安装
在这里插入图片描述
MSI文件下载后双击即可安装，在安装完成后系统会调用MySQL的服务配置器。
MySQL配置器参考手册https://dev.mysql.com/doc/refman/8.1/en/mysql-configurator-workflow-server.html
这里以ZIP压缩包安装举例：
在官网下载压缩包，并解压后可见到如下相关文件；
在这里插入图片描述
在该文件夹下新建一个名为data的文件夹和my.ini文件

#my.ini文件写入以下内容；这里因为我安装了两个MySQL8.1 故端口使用3307
[mysqld]
#设置端口
port=3307
# 安装目录，以实际为准
basedir=G:/mysql-8.1.0-winx64
# 设置mysql数据库的数据的存放目录
datadir=G:/mysql-8.1.0-winx64/data
#允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
#设置最大连接数
max_connections=10
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3307
default-character-set=utf8

然后打开管理员的cmd或者右键开始菜单，选择终端管理员

#在终端页面输入mysqld  --initialize --console初始化数据库，这时查看data文件可见到许多文件
#若安装了多个MySQL则加上--defaults-file=[对应安装路径下的my.ini]
PS G:\mysql-8.1.0-winx64\bin> mysqld --defaults-file=G:\mysql-8.1.0-winx64\my.ini --initialize --console
.............
2023-08-06T13:16:14.567438Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: **********

==A temporary password ==这一行为数据库的初始密码，等会要用。需要提前复制。
下面注册MySQL服务

#使用--install选项来注册服务
mysqld  --install 服务名

启动MySQL

net start 服务名

登录MySQL

#使用-u指定登录用户 -p密码 -P端口
 mysql -u root -p -P3306

在这里插入图片描述
上图可以看到两个不同端口的数据库都是可以正常登录的

2.2 Linux系统安装

Linux版本可以通过yum仓库或者rpm软件包的方式安装。这里基于redhat9.2系统进行yum仓库安装演示，其他其他步骤大同小异只需要更换对应的软件包即可：
指令请尽量以管理员用户运行

2.2.1 yum仓库安装方式

下载官方的yum仓库

#链接请以实际环境和系统版本为准
wget https://dev.mysql.com/get/mysql80-community-release-el9-3.noarch.rpm
[root@SQL ~]$ ls
anaconda-ks.cfg  mysql80-community-release-el9-3.noarch.rpm

安装yum仓库

#出现complete表示安装好了
[root@SQL ~]# yum install mysql80-community-release-el9-3.noarch.rpm
#……………………………………………………………………………………………………………………………………
Installed:
  mysql80-community-release-el9-3.noarch                                                     
Complete!

检查仓库安装是否成功

#检查是否有MySQL仓库
[root@SQL ~]$ yum repolist enabled | grep "mysql.*-community.*"
#检查仓库里是否有MySQL软件
[root@SQL ~]$ yum repolist all |grep mysql

安装MySQL

[root@SQL ~]$ yum install mysql-community-server

启动MySQL

[root@SQL ~]$  systemctl start mysqld
[root@SQL ~]$  systemctl status mysqld

查看初始密码并登录

#查看初始密码
[root@SQL ~]$  sudo grep 'temporary password' /var/log/mysqld.log
#登录；注意：此处会提示输入密码，但是密码不回显，注意不是没敲上，就是不显示密码，盲输或者复制
[root@SQL ~]$  mysql -uroot -p
#登录后修改初始密码；替换MyNewPass4!为你要修改的密码，注意符合密码规则，包含大小写字母和数字并为8位及以上，后面会写如何更改密码策略
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';

目前MySQL官方的yum仓库似乎还没有8.1的版本，这里在介绍一下使用RPM软件包的安装

2.2.2 RPM软件包安装方式

首先下载对应系统版本的RPM安装包
下载其他系统或者版本可以进入https://dev.mysql.com/get/Downloads/

wget https://dev.mysql.com/get/Downloads/MySQL-8.1/mysql-8.1.0-1.el9.x86_64.rpm-bundle.tar

解压归档包

tar -xvf mysql-8.1.0-1.el9.x86_64.rpm-bundle.tar

安装相关软件包
这里可以使用rpm -ivh或者yum install 命令来完成安装，我在按照官方文档使用yum install命令进行安装的时候出现了依赖不存在的报错。但是经过检查tar包并没有缺少文件所以我通过下面的命令强行安装

#首先进入到存放MySQL安装包的目录，并保证该目录没有别的rpm包
rpm  -ivh  *.rpm --nodeps --force

启动MySQL，并查看初始密码

#启动MySQL
systemctl start mysqld
#通过日志来查看MySQL的初始密码，该密码存放在/var/log/mysqld.log文件内
#通常是一串不规则字符例如下面的LksHhdQl>8;b就是我的密码，复制这个，一会有用
grep 'temporary password' /var/log/mysqld.log
2023-08-09T13:30:20.363109Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: LksHhdQl>8;b

登录MySQL，并重置密码

[root@SQL ~]# mysql -uroot -p
Enter password: #此处输入密码，不回显，粘贴刚才复制的初始密码
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.1.0

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
#此时已经进入了MySQL页面，我们修改初始密码
mysql> alter user 'root'@'localhost' identified by '123456';
#可以看到现在是不允许使用简单密码的，因为这不符合MySQL的安全策略。
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
#我们尝试修改密码，这不被允许，提示要重置你的密码才可以
mysql> show variables like '%password%';
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
#先修改一个符合密码策略的复杂密码
mysql> alter user 'root'@'localhost' identified by 'Lks##hdQl@8';
Query OK, 0 rows affected (0.01 sec)

2.2.3 如何修改密码策略

首先查看当前的密码策略

#关于密码策略的相关解释请参考后面的文章
mysql> show variables like '%validate_password%';
+-------------------------------------------------+--------+
| Variable_name                                   | Value  |
+-------------------------------------------------+--------+
| validate_password.changed_characters_percentage | 0      |
| validate_password.check_user_name               | ON     |
| validate_password.dictionary_file               |        |
| validate_password.length                        | 8      |
| validate_password.mixed_case_count              | 1      |
| validate_password.number_count                  | 1      |
| validate_password.policy                        | MEDIUM |
| validate_password.special_char_count            | 1      |
+-------------------------------------------------+--------+
8 rows in set (0.00 sec)
#这是测试环境我们设置简单密码，请不要在生产环境这么做。
#设置密码策略为low只校验长度，长度为6
mysql> set global validate_password.policy=low,validate_password.length=6;
Query OK, 0 rows affected (0.00 sec)
mysql> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

MySQL密码策略的解释：
validate_password.changed_characters_percentage#当前密码的字符在新密码重复使用的百分比
validate_password.check_user_name#是否将密码与当前会话的有效用户帐户的用户名部分进行比较
validate_password.dictionary_file#用于检查字典文件的路径
validate_password.length#密码的最小字符数
validate_password.mixed_case_count#字母的最小数量。
validate_password.number_count#最小的数字数量。
validate_password.policy#密码策略validate_password.special_char_count#密码策略为strong时需要密码的最小非字母数字字符数。

策略	校验方式
0或LOW	长度
1或MEDIUM	长度;数字、小写/大写和特殊字符
2或STRONG	长度;数字、小写/大写和特殊字符;字典文件

2.3 如何在8.1版本重置root密码

windows版本

首先使用net stop 服务名 命令来停止MySQL服务
新建一个.txt文本并插入下面这段代码：

#注意将MyNewPass替换为新密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

使用init_file系统启动MySQL服务器变量设置为命名文件

  #注意=后面为自己的文件路径
  mysqld --init-file=C:\\password.txt

Linux版本
流程与windows差不多是一样的

#结束进程
$> kill `cat /mysql-data-directory/host_name.pid`
$> vim  password
#将下面这段话写入password文件
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
#使用init_file系统启动MySQL服务器
$> mysqld --init-file=/home/me/password &

更多方式请参阅MySQL官网8.1手册：
https://dev.mysql.com/doc/refman/8.1/en/resetting-permissions.html

第三章 MySQL库表操作

3.1 SQL语句

SQL语句的分类

DDL(Data Definition Language):数据定义语言，定义对数据库对象(库、表、列、索引)的操作。
  CREATE、DROP、ALTER、RENAME、 TRUNCATE 
DML(Data Manipulation Language):数据操作语言，定义对数据库记录的操作。 
  INSERT、DELETE、UPDATE、SELECT 
DCL(Data Control Language): 数据控制语言，定义对数据库、表、字段、用户的访问权限和安全级别。     
  GRANT、REVOKE
Transaction Control:事务控制 
  COMMIT、ROLLBACK、SAVEPOINT`

SQL语句的书写规范

SQL语句不区分大小写(建议用大写) 。
字符串常量区分大小写。
SQL语句可单行或多行书写，最后以“;”结尾。
关键词不能跨多行或简写。
子句通常位于独立行，便于编辑，提高可读性。
注释：
/* */多行注释
“–” 单行注释
MySQL注释：“#”

3.2 数据库操作

3.2.1 查看

show databases [like  '字符串']；
#可使用like加字符串的方式进行模糊查询；通配符：'%'、'_'

MySQL自带数据库：
Information_schema: 主要存储了系统中的一些数据库对象信息：如用户表信息、列信息、权限信息、字符集信息、分区信息等。(数据字典表)
performance_schema：主要存储数据库服务器的性能参数
mysql: 存储了系统的用户权限信息及帮助信息。
sys: 5.7新增，之前版本需要手工导入。这个库是通过视图的形式把information_schema 和performance_schema结合起来，查询出更加令人容易理解的数据
test: 系统自动创建的测试数据库，任何用户都可以使用。

3.2.2 创建

 CREATE DATABASE [IF NOT EXISTS]数据库名;

如果数据库已经存在，发生一个错误。
查看创建数据库：

SHOW CREATE DATABASE <数据库名>;
#character set设置字符集；collate设置校验规则
create database school DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

3.2.3 删除

DROP DATABASE [IF EXISTS] 数据库名;

3.2.4 切换

使用USE选用数据库
   语法：
       USE 数据库名;
功能：把指定数据库作为默认(当前)数据库使用，用于后续语句。

3.2.5 基本查看操作

	查看当前连接的数据库
     	SELECT DATABASE();
	查看数据库版本
     	SELECT VERSION();
	查看当前用户
     	SELECT USER();
	查看所有用户
        SELECT User,Host,Password FROM mysql.user;

3.3 字符集

MySQL字符集包括字符集（CHARACTER）和校对规则（COLLATION）两个概念：

    latin1支持西欧字符、希腊字符等
    gbk支持中文简体字符
    big5支持中文繁体字符
    utf8几乎支持世界所有国家的字符。
    utf8mb4是真正意义上的utf-8

查看默认字符集

SHOW VARIABLES like 'character%';

查看校对规则

SHOW COLLATION;

字符集命名规则：
以字符序对应的字符集名称开头，以国家名居中（或以general居中）ci、cs或bin结尾。

ci表示大小写不敏感
cs表示大小写敏感
bin表示按二进制编码值比较

character_set_client：MySQL客户机字符集。
character_set_connection：数据通信链路字符集，当MySQL客户机向服务器发送请求时，请求数据以该字符集进行编码。
character_set_database：数据库字符集。
character_set_filesystem：MySQL服务器文件系统字符集，该值是固定的binary。
character_set_results：结果集的字符集，MySQL服务器向MySQL客户机返回执行结果时，执行结果以该字符集进行编码。
character_set_server：MySQL服务实例字符集。
character_set_system：元数据(字段名、表名、数据库名等) 的字符集，默认值为utf8。

3.3.1 utf8和utf8mb4的区别

MySQL在5.5.3之后增加了这个utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换。当然，为了节省空间，一般情况下使用utf8也就够了。
既然utf8能够存下大部分中文汉字，那为什么还要使用utf8mb4呢？原来mysql支持的 utf8 编码最大字符长度为 3 字节，如果遇到 4 字节的宽字符就会插入异常了。三个字节的 UTF-8 最大能编码的 Unicode 字符是 0xffff，也就是 Unicode 中的基本多文种平面(BMP)。也就是说，任何不在基本多文本平面的 Unicode字符，都无法使用 Mysql 的 utf8 字符集存储。包括 Emoji 表情(Emoji 是一种特殊的 Unicode 编码，常见于 ios 和 android 手机上)，和很多不常用的汉字，以及任何新增的 Unicode 字符，如表情等等(utf8的缺点)。

3.4 数据库对象的命名规则

必须以字母开头
可包括数字和三个特殊字符（# _ $）
不要使用MySQL的保留字
同一Schema下的对象不能同名

3.5 表的基本操作

3.5.1 创建表

标准的建表（table）语法(列定义之间以英文逗号,隔开)：
数据表的每行称为一条记录（record），每一列称为一个字段（field）。
主键列：唯一能够标识每条记录的列。
CREATE TABLE [schema.]table (column datatype[DEFAULT expr] ) ENGINE = 存储机制
CREATE TABLE
简单语法：
    CREATE TABLE 表名（
    列名 列类型，
    列名 列类型
    ）;
功能：在当前数据库中创建一张表

3.5.2 表物理存储结构

表的物理存储结构：

MyISAM（一种引擎）的表：
[root@node1 ~]# cd /var/lib/mysql/mysql/
[root@node1 mysql]# ls -l user*
# 描述表结构文件，字段长度等，frame框架
-rw-r----- 1 mysql mysql 10816 7月  16 17:39 user.frm  
 # 数据信息文件，存储数据信息(如果采用独立表存储模式) data
-rw-r----- 1 mysql mysql  384 7月  16 17:52 user.MYD  
# 索引信息文件，index
-rw-r----- 1 mysql mysql  4096 7月  16 17:54 user.MYI  

InnoDB(默认的存储引擎)的表：
[root@node2 employess]# ls -l t4*
-rw-r----- 1 mysql mysql  8586 7月  16 20:31 t4.frm
-rw-r----- 1 mysql mysql 98304 7月  16 20:32 t4.ibd
ttt
t.frm：存储列相关信息，描述表结构文件，字段长度等
t.ibd：数据行+索引，如果采用独立表存储模式，data\a中还会产生b.ibd文件（存储数据信息和索引信息）
    如果采用共存储模式的，数据信息和索引信息都存储在ibdata1中
    如果采用分区存储，还会有一个t.par文件（用来存储分区信息）

3.5.3 数据类型

在 MySQL 中，有三种主要的类型：文本、数字和日期/时间类型。
Text 类型：

数据类型	描述
CHAR(size)	保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符。
VARCHAR(size)	保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。注释：如果值的长度大于 255，则被转换为 TEXT 类型。
TINYTEXT	存放最大长度为 255 个字符的字符串。
TEXT	存放最大长度为 65,535 个字符的字符串。
BLOB	用于 BLOBs (Binary Large OBjects)。存放最多 65,535 字节的数据。
MEDIUMTEXT	存放最大长度为 16,777,215 个字符的字符串。
MEDIUMBLOB	用于 BLOBs (Binary Large OBjects)。存放最多 16,777,215 字节的数据。
LONGTEXT	存放最大长度为 4,294,967,295 个字符的字符串。
LONGBLOB	用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。
ENUM(x,y,z,etc.)	允许你输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。注释：这些值是按照你输入的顺序存储的。可以按照此格式输入可能的值： ENUM(‘X’,‘Y’,‘Z’)
SET	与 ENUM 类似， SET 最多只能包含 64 个列表项，不过 SET 可存储一个以上的值。

Number 类型：

数据类型	描述
TINYINT(size)	-128 到 127 常规。 0 到 255 无符号*。在括号中规定最大位数。
SMALLINT(size)	-32768 到 32767 常规。 0 到 65535 无符号*。在括号中规定最大位数。
MEDIUMINT(size)	-8388608 到 8388607 普通。 0 to 16777215 无符号*。在括号中规定最大位数。
INT(size)	-2147483648 到 2147483647 常规。 0 到 4294967295 无符号*。在括号中规定最大位数。
BIGINT(size)	-9223372036854775808 到 9223372036854775807 常规。 0 到18446744073709551615 无符号*。在括号中规定最大位数。
FLOAT(size,d)	带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DOUBLE(size,d)	带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DECIMAL(size,d)	作为字符串存储的 DOUBLE 类型，允许固定的小数点。

注意：这些整数类型拥有额外的选项 UNSIGNED。通常，整数可以是负数或正数。如果添加 UNSIGNED属性，那么范围将从 0 开始，而不是某个负数。
Date 类型：

数据类型	描述
DATE()	日期。格式： YYYY-MM-DD 注释：支持的范围是从 ‘1000-01-01’ 到 ‘9999-12-31’
DATETIME()	日期和时间的组合。格式： YYYY-MM-DD HH:MM:SS 注释：支持的范围是’1000-01-01 00:00:00’ 到 ‘9999-12- 31 23:59:59’
TIMESTAMP()	时间戳。 TIMESTAMP 值使用 Unix 纪元(‘1970-01-01 00:00:00’ UTC) 至今的描述来存储。格式： YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-09 03:14:07’ UTC
TIME()	时间。格式： HH:MM:SS 注释：支持的范围是从 ‘-838:59:59’ 到 ‘838:59:59’
YEAR()	2 位或 4 位格式的年。注释： 4 位格式所允许的值： 1901 到 2155。 2 位格式所允许的值： 70 到69，表示从 1970 到 2069

常用数据类型：

分类	数据类型
二进制数据类型	BLOB
文本数据类型	char、varchar、text
日期和时间	time、date、datetime
数值型数据	int、smalint、float、double
货币数据类型	decimal
bit数据类型	bit

示例：创建一个较为复杂的表

需求：现在公司要求对网络相关的课程数据进行归类整理，设计一个数据表来完成，

CREATE TABLE net_books (
    title CHAR(50) NOT NULL 
	COMMENT '教材的标题',
    description TEXT 
	COMMENT '教材的详细描述',
    summary TINYTEXT 
	COMMENT '教材的总览',
    videos MEDIUMBLOB 
	COMMENT '学习资源的视频',
    teacher_name VARCHAR(100) 
	COMMENT '讲师或作者的姓名',
    teacher_image BLOB 
	COMMENT '讲师或作者图片',
    feedback LONGTEXT 
	COMMENT '关于教材的评论',
    feedback_files LONGBLOB 
	COMMENT '评论的附件',
    leave ENUM('HCIA', 'HCIP', 'HCIE') 
	COMMENT '教材的难度级别',
    tags SET('network', 'router', 'switch') 
	COMMENT '与教材相关的标签',
    duration TINYINT(3) 
	COMMENT '教材的学习时长',
    lectures SMALLINT(5) 
	COMMENT '教材中的讲座数量',
    players INT(10) 
	COMMENT '教材中已注册的学员数量',
    rating FLOAT(4, 2) 
	COMMENT '教材的平均评分',
    price DOUBLE(8, 2) 
	COMMENT '教材的价格',
    discount DECIMAL(5, 2) 
	COMMENT '教材的折扣百分比',
    start_date DATE 
	COMMENT '教材的开始日期',
    created_at DATETIME 
	COMMENT '教材的创建日期和时间',
    last_updated TIMESTAMP 
	COMMENT '教材的最后更新日期和时间',
    timetable TIME 
	COMMENT '面授课程的时间表',
    year_created YEAR 
	COMMENT '教材创建年份'
)engine=innodb CHARACTER set utf8mb4 COLLATE utf8mb4_zh_0900_as_cs ;

在这里插入图片描述

3.5.4 查看表

#显示当前数据库中已有的数据表的信息
SHOW TABLES[FROM 数据库名][LIKE wild];

#查看表中列的信息
DESC 表名 [列名];
show columns from 表名称;

#查看全面的表定义信息
SHOW CREATE TABLE 表名\G

3.5.5 删除表

#删除指定表
	DROP TABLE [IF EXISTS] 表名;

3.5.6 修改表的结构

修改列类型
	ALTER TABLE 表名 MODIFY 列名 列类型;
增加列
	ALTER TABLE 表名 ADD 列名 列类型;
删除列
	ALTER TABLE 表名 DROP 列名;
列改名
	ALTER TABLE 表名 CHANGE 旧列名 新列名 列类型;
更改表名
	ALTER TABLE 表名 RENAME 新表名;
	RENAME TABLE 表名 TO 新表名;

3.5.7 复制表的结构

复制一个表结构的实现方法有两种
#只复制表结构
create table 新表名 like 源表
#复制结构和内容
create table 新表名 select * from 源表
#存在相同结构的表，只复制数据
insert into 表 select * from 源表;

3.5.8 数据库字典

由information_schema数据库负责维护

tables－存放数据库里所有的数据表、以及每个表所在数据库。
schemata－存放数据库里所有的数据库信息
views－存放数据库里所有的视图信息。
columns－存放数据库里所有的列信息。
triggers－存放数据库里所有的触发器。
routines－存放数据库里所有存储过程和函数。
key_column_usage－存放数据库所有的主外键
table_constraints－存放数据库全部约束。
statistics－存放了数据表的索引。

3.5.9 表的约束

约束是在表上强制执行的数据校验规则。约束主要用于保证数据库的完整性。当表中数据有相互依赖性时,可以保护相关的数据不被删除。大部分数据库支持下面6个约束:

NOT NULL非空
UNIQUE Key唯一键
PRIMARY KEY主键
FOREIGN KEY外键
CHECK检查
默认值约束

约束作为数据库对象，存放在系统表中，也有自己的名字
创建约束的时机
    在建表的同时创建
    建表后创建（修改表）
可定义列级或表级约束
有单列约束和多列约束
定义约束的语法：

列级约束：在定义列的同时定义约束
	语法：列定义 约束类型,
	
表级约束：在定义了所有列之后定义的约束
	语法：
    列定义 [CONSTRAINT 约束名] 约束类型(列名)
约束名的取名规则
	推荐采用：表名_列名_约束类型简介
约束可以在创建表时就定义，也可以在创建完后再添加
语法：
	alter table 表名 add constraint 约束名 约束类型(要约束的列名)

3.5.10 存储引擎

MyISAM：应用于以读写操作为主，很少更新、删除，并对事务的完整性、并发性要求不高的情况
InnoDB:：应用于对事务的完整性要求高,在并发条件下要求数据的一致性的情况。现在的默认值。
MEMORY：表的数据存放在内存中，访问效率高，但一旦服务关闭，表中的数据全部丢失。
MERGE: 是一组MyISAM表的组合。可以突破对单个MyISAM表大小的限制，并提高访问效率

CREATE TABLE (...)  ENGINE=InnoDB;  
CREATE TABLE 表名（
	列名 列类型 [AUTO_INCREMENT] [DEFAULT 默认值][列约束]
	...
	[表约束]
) [ENGINE=表类型] [DEFAULT] CHARSET=字符集;

列类型： 该列的数据的存储类型
AUTO_INCREMENT: 自动增尙只能是数值类型的列
DEFAULT 默认值： 设置该列的默认值
约束： 对列的一些限制
ENGINE: 表类型， 也叫表的存储引擎
CHARSET： 设置表的字符篥

3.6 MySQL用户授权和撤销授权

MySQL中授权(grant)和撤销授权(revoke）。

方法1：create和grant结合
    help CREATE USER;
    命令：CREATE USER <'用户名'@'地址'> IDENTIFIED BY ‘密码’;
    查看用户权限： help SHOW GRANTS;
    命令：show grants  for '用户名'@'地址'；
    授权：help GRANT;

方法2：直接grant
    收回权限：REVOKE
    删除用户：DROP USER username
    
 生产环境授权用户建议：
 	1、博客，CMS等产品的数据库授权
        select,insert,update,delete,create
        库生成后收回create权限
    2、生产环境主库用户授权
   		select,insert,update,delete
    3、生产环境从库授权
    	select


创建用户方法（推荐使用方法三）:
方法一：CREATE USER语句创建
	CREATE USER user1@’localhost’ IDENTIFIED BY ‘123456’;
	
方法二： INSERT语句创建
INSERT INTO mysql.user(user,host, authentication_string,ssl_cipher,
                       x509_issuer,x509_subject)
	VALUES('user2','localhost',password('ABCabc123!'),'','','');
	
FLUSH PRIVILEGES;

方法三： GRANT语句创建
	GRANT SELECT ON *.* TO user3@’localhost’ IDENTIFIED BY ‘123456’;
	
	FLUSH PRIVILEGES;
	
语法格式：
  grant 权限列表  on 库名.表名 to 用户名@'客户端主机' 
  		[identified by '密码'  with option参数];
  		
  如：
   grant select on testdb.* to common_user@'%' 
   grant insert on testdb.* to common_user@'%'  
   grant update on testdb.* to common_user@'%'  
   grant delete on testdb.* to common_user@'%' 
   grant select, insert, update, delete on testdb.* to common_user@'%'
   
   grant create on testdb.* to developer@'192.168.0.%';  
   grant alter  on testdb.* to developer@'192.168.0.%';  
   grant drop   on testdb.* to developer@'192.168.0.%';  
   grant all    on *.* to dba@localhost; -- dba 可以管理 MySQL 中的所有数据库  
   
   show grants;  -- 查看当前用户（自己）权限
   show grants for dba@localhost;  
   grant  all on *.* to   dba@localhost; 
   
   # 移除权限
   # revoke 跟 grant 的语法差不多，只需要把关键字 “to” 换成 “from” 即可
   revoke all on *.* from dba@localhost; 
   
  		
with_option参数
     GRANT OPTION： 授权选项
     MAX_QUERIES_PER_HOUR： 定义每小时允许执行的查询数
     MAX_UPDATES_PER_HOUR： 定义每小时允许执行的更新数
     MAX_CONNECTIONS_PER_HOUR： 定义每小时可以建立的连接数
     MAX_USER_CONNECTIONS： 定义单个用户同时可以建立的连接数

第四章数据操作语句

4.1 INSERT语句

INSERT INTO table [(column [, column...])] VALUES(value [, value...]);
默认情况下，一次插入操作只插入一行

一次性插入多条记录：
INSERT INTO table [(字段1 [, 字段2...])] 
	VALUES(value [, value...])，(value [, value...])
	
如果为每列都指定值，则表名后不需列出插入的列名
如果不想在表名后列出列名，可以为那些无法指定的值插入null
可以使用如下方式一次插入多行
	insert into 表名[(列名,…)]
如果需要插入其他特殊字符，应该采用\转义字符做前缀

4.2 REPLACE语句

replace语句的语法格式有三种语法格式。

语法格式1：
	replace into 表名 [（字段列表）] values （值列表）

语法格式2：
	replace [into] 目标表名[(字段列表1) select (字段列表2) from 源表 where 条件表达式
	
语法格式3：
	replace [into] 表名 set 字段1=值1, 字段2=值2

REPLACE与INSERT语句区别：

replace语句的功能与insert语句的功能基本相同，不同之处在于：使用replace语句向表插入新记录时，如果新记录的主键值或者唯一性约束的字段值与已有记录相同，则已有记录先被删除（注意：已有记录删除时也不能违背外键约束条件），然后再插入新记录。
使用replace的最大好处就是可以将delete和insert合二为一(效果相当于更新)，形成一个原子操作，这样就无需将delete操作与insert操作置于事务中了

4.3 UPDATE语句

UPDATE table
SET column = value [, column = value] 
[WHERE condition];

修改可以一次修改多行数据，修改的数据可用where子句限定，where子句里是一个条件表达式，只有符合该条件的行才会被修改。没有where子句意味着where字句的表达式值为true。也可以同时修改多列，多列的修改中间采用逗号(,)隔开

4.4 DELETE和TRUNCATE语句

DELETE FROM table_name [where 条件];
TRUNCATE TABLE table_name

DROP、TRUNCATE、DELETE的区别：
delete： 删除数据，保留表结构，可以回滚，如果数据量大，很慢
truncate： 删除所有数据，保留表结构，不可以回滚，一次全部删除所有数据，速度相对很快
drop： 删除数据和表结构,删除速度最快。

4.5 SELECT语句

简单的SELECT语句：

简单的SELECT语句：
SELECT {*, column [alias],...}
FROM table;
说明：
–SELECT列名列表。*表示所有列。
–FROM 提供数据源(表名/视图名)
–默认选择所有行

SELECT语句中的算术表达式：

对数值型数据列、变量、常量可以使用算数操作符创建表达式（+ - * /）
对日期型数据列、变量、常量可以使用部分算数操作符创建表达式（+ -）
运算符不仅可以在列和常量之间进行运算，也可以在多列之间进行运算。
	SELECT last_name, salary, salary*12
	FROM employees;
补充：+说明
-- MySQL的+默认只有一个功能：运算符
SELECT 100+80; # 结果为180
SELECT '123'+80; # 只要其中一个为数值，则试图将字符型转换成数值，转换成功做预算，结果为203
SELECT 'abc'+80; # 转换不成功，则字符型数值为0，结果为80
SELECT 'This'+'is'; # 转换不成功，结果为0
SELECT NULL+80; # 只要其中一个为NULL，则结果为NULL

运算符的优先级：

乘法和除法的优先级高于加法和减法
同级运算的顺序是从左到右
表达式中使用括号可强行改变优先级的运算顺序
    SELECT last_name, salary, salary*12+100
    FROM employees;
    
    SELECT last_name, salary, salary*(12+100)
    FROM employees;

NULL值的使用：

空值是指不可用、未分配的值
空值不等于零或空格
任意类型都可以支持空值
包括空值的任何算术表达式都等于空
字符串和null进行连接运算，得到也是null.

补充说明：

安全等于<=>
1.可作为普通运算符的=
2.也可以用于判断是否是NULL 如：where salary is NULL/(is not NULL) ->where salary
<=>NULL
示例1：查询emp表奖金为空的员工信息。
	select * from emp where comm <=> NULL;

示例2：查询emp表奖金为50000的员工信息
	select * from emp where comm <=> 50000;

定义字段的别名：

改变列的标题头
用于表示计算结果的含义
作为列的别名
如果别名中使用特殊字符,或者是强制大小写敏感,或有空格时,都可以通过为别名添加加双引号实现。
    SELECT last_name as “姓名”, salary “薪水”
    FROM employees;
    
    SELECT last_name, salary*12 “年薪”
    FROM employees;

重复记录：

默认情况下查询显示所有行,包括重复行
    SELECT department_id
    FROM employees;
    
使用DISTINCT关键字可从查询结果中清除重复行
    SELECT DISTINCT department_id
    FROM employees;

DISTINCT的作用范围是后面所有字段的组合
    SELECT DISTINCT department_id , job_id
    FROM employees;

限制所选择的记录：

使用WHERE子句限定返回的记录
WHERE子句在FROM 子句后
	SELECT[DISTINCT] {*, column [alias], ...}
	FROM table–[WHEREcondition(s)];
	
WHERE中的字符串和日期值
*****字符串和日期要用单引号扩起来*****
*****字符串是大小写敏感的,日期值是格式敏感的*****
    SELECT last_name, job_id, department_id
    FROM employees
    WHERE last_name = "king";

WHERE中比较运算符:

    SELECT last_name, salary, commission_pct
    FROM employees
    WHERE salary<=1500;    
    
其他比较运算符
使用BETWEEN运算符显示某一值域范围的记录
    SELECTlast_name, salary
    FROM employees
    WHERE salary BETWEEN 1000 AND 1500;

IN运算符

使用IN运算符获得匹配列表值的记录
    SELECTemployee_id, last_name, salary, manager_id
    FROM employees
    WHERE manager_id IN (7902, 7566, 7788);

LIKE运算符

  使用LIKE运算符执行模糊查询
  查询条件可包含文字字符或数字
  (%) 可表示零或多个字符
  ( _ ) 可表示一个字符
    SELECT last_name
    FROM employees
    WHERE last_name LIKE '_A%';

IS NULL运算符

  查询包含空值的记录
      SELECT last_name, manager_id
      FROM employees
      WHERE manager_id IS NULL;

逻辑运算符：

AND运算符

  AND需要所有条件都是满足T
  SELECT employee_id, last_name, job_id, salary
  FROM employees
  WHERE salary>=1100–4 AND job_id='CLERK';

OR运算符

  OR只要两个条件满足一个就可以
  SELECT employee_id, last_name, job_id, salary
  FROM employees
  WHERE salary>=1100 OR job_id='CLERK';

NOT运算符

   NOT是取反的意思
   	SELECT last_name, job_id
       FROM employees
       WHERE job_id NOT IN ('CLERK','MANAGER','ANALYST');

使用正则表达式：REGEXP
<列名> regexp ‘正则表达式’
select * from product where product_name regexp ‘^2018’;

数据分组–GROUP BY

GROUP BY子句的真正作用在于与各种聚合函数配合使用。它用来对查询出来的数据进行分组。
  分组的含义是：把该列具有相同值的多条记录当成一组记录处理，最后只输出一条记录。
  分组函数忽略空值,。
  
  SELECT column, group_function
  FROM table
  [WHERE condition]
  [GROUP BY group_by_expression]
  [ORDER BY column];
  #如果select语句中的列未使用组函数，那么它必须出现在GROUP BY子句中
  #而出现在GROUP BY子句中的列，不一定要出现在select语句中
  SELECT deptno,AVG(sal),MAX(sal),MIN(sal),SUM(sal),COUNT(1)  FROM TB_EMP
  GROUP BY deptno #根据部门编号分组
  #每个部门每个职位的平均工资
  SELECT deptno,job,AVG(sal) FROM TB_EMP GROUP BY deptno,job
  分组函数重要规则
  如果使用了分组函数，或者使用GROUP BY 的查询：
  要么出现在SELECT列表中的字段，
  要么出现在组合函数里，
  要么出现在GROUP BY 子句中。
  GROUP BY 子句的字段可以不出现在SELECT列表当中。
  使用集合函数可以不使用GROUP BY子句，此时所有的查询结果作为一组。

数据分组–限定组的结果：HAVING子句


    HAVING子句用来对分组后的结果再进行条件过滤。
    SELECT column, group_function
    FROM table
    [WHERE condition]
    [GROUP BY group_by_expression]
    [HAVING group_condition]
    [ORDER BYcolumn];
    HAVING子句用来对分组后的结果再进行条件过滤。
    #查询部门平均工资大于2000的
    #分组后加条件 使用having
    #where和having都是用来做条件限定的,但是having只能用在group by之后
    SELECT deptno,AVG(sal),MAX(sal),MIN(sal),SUM(sal),COUNT(1)
    FROM TB_EMP
    GROUP BY deptno
    HAVING AVG(sal) > 2000
    HAVING与WHERE的区别
    WHERE是在分组前进行条件过滤， HAVING子句是在分组后进行条件过滤，WHERE子句中不能使用聚合函数，
    HAVING子句可以使用聚合函数。
    组函数的错误用法
     不能在WHERE 子句中限制组.
     限制组必须使用HAVING 子句.
     不能在WHERE 子句中使用组函数
    补充：MySQL 多行数据合并 GROUP_CONCAT
    Syntax: GROUP_CONCAT(expr)
    注意：使用 GROUP_CONCAT（）函数必须对源数据进行分组，否则所有数据会被合并成一行

对结果集排序

  查询语句执行的查询结果，数据是按插入顺序排列
  实际上需要按某列的值大小排序排列
  按某列排序采用order by 列名[desc],列名…
  设定排序列的时候可采用列名、列序号和列别名
  如果按多列排序，每列的asc，desc必须单独设定

联合查询

-- 中国或美国城市信息
SELECT * FROM city
WHERE countrycode IN ('CHN' ,'USA');
SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA'
说明:一般情况下,我们会将 IN 或者 OR 语句 改写成 UNION ALL,来提高性能
UNION   去重复
UNION ALL 不去重复
查询结果限定
在SELECT语句最后可以用LIMLT来限定查询结果返回的起始记录和总数量。MySQL特有。
SELECT … LIMIT offset_start,row_count;
offset_start:第一个返回记录行的偏移量。默认为0.
row_count:要返回记录行的最大数目。
例子：
SELECT * FROM TB_EMP LIMIT 5;/*检索前5个记录*/
SELECT * FROM TB_EMP LIMIT 5,10;/*检索记录行6-15*/

MySQL中的通配符：
MySQL中的常用统配符有三个：
   %：用来表示任意多个字符，包含0个字符
   _ : 用来表示任意单个字符
   escape：用来转义特定字符

示例2：单表查询

首先创建一张名为worker的表包含部门号，职工号，工作时间，工资，政治面貌，姓名，出生日期等字段

#创建表
mysql> CREATE TABLE worker (
    ->          `部门号` INT(11) NOT NULL,
    ->          `职工号` INT(11) NOT NULL PRIMARY KEY,
    ->          `工作时间` DATE NOT NULL,
    ->          `工资` DECIMAL(8,2) NOT NULL,
    ->          `政治面貌` ENUM('群众','团员','党员') NOT NULL,
    ->          `姓名` VARCHAR(20) NOT NULL,
    ->          `出生日期` DATE NOT NULL,
    ->          `性别`  enum('男','女') not null  default '男'
    ->      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected, 3 warnings (0.01 sec)
#插入数据
mysql> INSERT INTO worker values
    -> (103, 1003, '2017-09-30', 2800.75, '党员', '陈山', '1988-11-22'),
    -> (104, 1004, '2018-06-10', 5000.25, '党员', '赵天', '1991-03-14'),
    -> (101, 1005, '2014-11-20', 3200.80, '群众', '田乐', '1993-09-05'),
    -> (102, 1006, '2019-08-02', 3700.00, '团员', '王耀', '1990-01-30'),
    -> (103, 1007, '2020-03-25', 4500.50, '党员', '张琪', '1989-06-12'),
    -> (104, 1008, '2016-12-08', 2900.25, '团员', '刘菲', '1994-02-27'),
    -> (101, 1009, '2017-07-17', 5200.75, '党员', '李琦', '1993-10-10'),
    -> (102, 1010, '2018-04-05', 3800.80, '群众', '王龙', '1987-08-15');
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

习题：
1、显示所有职工的基本信息。
分析：显示所有职工的基本信息即为查看所有字段

mysql> select *from worker;
+-----------+-----------+--------------+---------+--------------+--------+--------------+--------+
| 部门号     | 职工号    | 工作时间     | 工资      | 政治面貌      | 姓名   | 出生日期      | 性别   |
+-----------+-----------+--------------+---------+--------------+--------+--------------+--------+
|       103 |      1003 | 2017-09-30   | 2800.75 | 党员         | 陈山   | 1988-11-22   | 男     |
|       104 |      1004 | 2018-06-10   | 5000.25 | 党员         | 赵天   | 1991-03-14   | 男     |
|       101 |      1005 | 2014-11-20   | 3200.80 | 群众         | 田乐   | 1993-09-05   | 女     |
|       102 |      1006 | 2019-08-02   | 3700.00 | 团员         | 王耀   | 1990-01-30   | 男     |
|       103 |      1007 | 2020-03-25   | 4500.50 | 党员         | 张琪   | 1989-06-12   | 女     |
|       104 |      1008 | 2016-12-08   | 2900.25 | 团员         | 刘菲   | 1994-02-27   | 女     |
|       101 |      1009 | 2017-07-17   | 5200.75 | 党员         | 李琦   | 1993-10-10   | 男     |
|       102 |      1010 | 2018-04-05   | 3800.80 | 群众         | 王龙   | 1987-08-15   | 男     |
+-----------+-----------+--------------+---------+--------------+--------+--------------+--------+
8 rows in set (0.00 sec)

2、查询所有职工所属部门的部门号，不显示重复的部门号。
分析：单独查看部门号这个字段，并使用distinct函数去重

mysql> select distinct 部门号 from worker;
+-----------+
| 部门号    |
+-----------+
|       103 |
|       104 |
|       101 |
|       102 |
+-----------+
4 rows in set (0.00 sec)

3、求出所有职工的人数。
使用count计数函数

mysql> select count(*) as 总人数 from worker;
+-----------+
| 总人数    |
+-----------+
|         8 |
+-----------+
1 row in set (0.00 sec)

4、列出最高工和最低工资。
使用max和min函数

mysql> select max(工资),min(工资) from worker;
+-------------+-------------+
| max(工资)   | min(工资)   |
+-------------+-------------+
|     5200.75 |     2800.75 |
+-------------+-------------+
1 row in set (0.00 sec)

5、列出职工的平均工资和总工资。
使用avg和sum函数

mysql> select avg(工资),sum(工资) from worker;
+-------------+-------------+
| avg(工资)   | sum(工资)   |
+-------------+-------------+
| 3888.012500 |    31104.10 |
+-------------+-------------+
1 row in set (0.00 sec)

6、创建一个只有职工号、姓名和参加工作的新表，名为工作日期表。
创建一张表，来自select查出来的数据

mysql> create table 工作日期表 as select 职工号,姓名,工作时间 from worker;
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> show tables;
+--------------------+
| Tables_in_0810work |
+--------------------+
| worker             |
| 工作日期表         |
+--------------------+
2 rows in set (0.00 sec)

mysql> select *from 工作日期表;
+-----------+--------+--------------+
| 职工号    | 姓名   | 工作时间     |
+-----------+--------+--------------+
|      1003 | 陈山   | 2017-09-30   |
|      1004 | 赵天   | 2018-06-10   |
|      1005 | 田乐   | 2014-11-20   |
|      1006 | 王耀   | 2019-08-02   |
|      1007 | 张琪   | 2020-03-25   |
|      1008 | 刘菲   | 2016-12-08   |
|      1009 | 李琦   | 2017-07-17   |
|      1010 | 王龙   | 2018-04-05   |
+-----------+--------+--------------+
8 rows in set (0.00 sec)

7、显示所有女职工的年龄。

mysql> select 姓名, timestampdiff(year, 出生日期, '2023-08-10') as 年龄 from worker where 性别 = '女';
+--------+--------+
| 姓名   | 年龄   |
+--------+--------+
| 田乐   |     29 |
| 张琪   |     34 |
| 刘菲   |     29 |
+--------+--------+
3 rows in set (0.00 sec)

8、列出所有姓刘的职工的职工号、姓名和出生日期。

mysql> select 职工号,姓名,出生日期 from worker where 姓名 like '刘%';
+-----------+--------+--------------+
| 职工号    | 姓名   | 出生日期     |
+-----------+--------+--------------+
|      1008 | 刘菲   | 1994-02-27   |
+-----------+--------+--------------+
1 row in set (0.01 sec)

9、列出1960年以前出生的职工的姓名、参加工作日期。

#year 用来取date格式的年
mysql> select 姓名,工作时间 from worker where year(出生日期)<1960;
+--------+--------------+
| 姓名   | 工作时间     |
+--------+--------------+
| 李乐   | 2017-09-30   |
+--------+--------------+
1 row in set (0.00 sec)

10、列出工资在1000－2000之间的所有职工姓名。

#between and 是一个左闭右闭的
mysql> select 姓名,工资 from worker where 工资 between 1000 and 2000;
+--------+---------+
| 姓名   | 工资    |
+--------+---------+
| 张星   | 1500.12 |
| 陈超   | 1800.12 |
| 张扬   | 1999.12 |
| 长风   | 2000.00 |
| 王晨   | 1000.00 |
+--------+---------+
5 rows in set (0.00 sec

11、列出所有陈姓和李姓的职工姓名。

mysql> select 姓名 from worker where 姓名 like '陈%' or 姓名 like '李%';
+--------+
| 姓名   |
+--------+
| 陈山   |
| 李琦   |
| 李乐   |
| 陈超   |
| 李晨   |
+--------+
5 rows in set (0.00 sec)

12、列出所有部门号为2和3的职工号、姓名、党员否。

#使用if函数判断是否为党员
mysql> select 职工号,姓名,if(政治面貌='党员','是','否') as '是否党员' from worker
    -> where 部门号 in(102,103);
+-----------+--------+--------------+
| 职工号    | 姓名   | 是否党员     |
+-----------+--------+--------------+
|      1003 | 陈山   | 是           |
|      1006 | 王耀   | 否           |
|      1007 | 张琪   | 是           |
|      1010 | 王龙   | 否           |
+-----------+--------+--------------+
4 rows in set (0.00 sec)

13、将职工表worker中的职工按出生的先后顺序排序。

mysql> select 姓名,出生日期 from worker order by 出生日期;
+--------+--------------+
| 姓名   | 出生日期     |
+--------+--------------+
| 李乐   | 1959-11-22   |
| 王龙   | 1987-08-15   |
| 陈山   | 1988-11-22   |
| 张琪   | 1989-06-12   |
| 王耀   | 1990-01-30   |
| 赵天   | 1991-03-14   |
| 田乐   | 1993-09-05   |
| 李琦   | 1993-10-10   |
| 刘菲   | 1994-02-27   |
| 张扬   | 1999-09-02   |
| 长风   | 1999-09-02   |
| 王晨   | 1999-09-02   |
| 李晨   | 1999-09-02   |
| 张星   | 1999-10-02   |
| 陈超   | 1999-10-02   |
+--------+--------------+
15 rows in set (0.00 sec)

14、显示工资最高的前3名职工的职工号和姓名。

#order by 用于排序，默认为升序，使用desc则降序，limit 用于限定查询结果数量
mysql> select 姓名,工资 from worker order by 工资 desc limit 3;
+--------+---------+
| 姓名   | 工资    |
+--------+---------+
| 李琦   | 5200.75 |
| 赵天   | 5000.25 |
| 张琪   | 4500.50 |
+--------+---------+
3 rows in set (0.00 sec)

15、求出各部门党员的人数。

mysql> select 部门号,count(*) as '党员人数' from worker where 政治面貌='党员' group by 部门号 ;
+-----------+--------------+
| 部门号    | 党员人数     |
+-----------+--------------+
|       103 |            2 |
|       104 |            2 |
|       101 |            1 |
+-----------+--------------+
3 rows in set (0.00 sec)

16、统计各部门的工资和平均工资。

#sum 求和 avg 求平均
mysql> select 部门号,sum(工资) as 总工资,avg(工资) as 平均工资 from worker group by
部门号 ;
+-----------+-----------+--------------+
| 部门号    | 总工资    | 平均工资     |
+-----------+-----------+--------------+
|       103 |   7301.25 |  3650.625000 |
|       104 |  10701.25 |  3567.083333 |
|       101 |  18700.91 |  2337.613750 |
|       102 |   7500.80 |  3750.400000 |
+-----------+-----------+--------------+
4 rows in set (0.00 sec)

17、列出总人数大于4的部门号和总人数。

#使用having子句对分组后结果进行过滤
mysql> select 部门号,count(*) as 总人数 from worker 
    -> group by 部门号 having count(*)>4;
+-----------+-----------+
| 部门号    | 总人数    |
+-----------+-----------+
|       101 |         8 |
+-----------+-----------+
1 row in set (0.00 sec)

4.6 多表关联查询

1. inner join：代表选择的是两个表的交差部分。
 内连接就是表间的主键与外键相连，只取得键值一致的，可以获取双方表中的数据连接方式。语法如下：
 SELECT 列名1,列名2... FROM 表1 INNER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;
 
2. left join：代表选择的是前面一个表的全部。
左连接是以左表为标准，只查询在左边表中存在的数据，当然需要两个表中的键值一致。语法如下：
SELECT 列名1 FROM 表1 LEFT OUTER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;

3. right join：代表选择的是后面一个表的全部
同理，右连接将会以右边作为基准，进行检索。语法如下：
SELECT 列名1 FROM 表1 RIGHT OUTER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;

4.自连接
自连接顾名思义就是自己跟自己连接，参与连接的表都是同一张表。(通过给表取别名虚拟出)

5.交叉连接：不适用任何匹配条件。生成笛卡尔积

示例3：多表联合查询

首先创建student和score表

CREATE  TABLE student (
	id  INT(10)  NOT NULL  UNIQUE  PRIMARY KEY ,
	name  VARCHAR(20)  NOT NULL ,
	sex  VARCHAR(4) ,
	birth  YEAR,
	department  VARCHAR(20) ,
	address  VARCHAR(50)
  );

CREATE  TABLE score (
	id  INT(10)  NOT NULL  UNIQUE  PRIMARY KEY  AUTO_INCREMENT ,
	stu_id  INT(10)  NOT NULL ,
	c_name  VARCHAR(20) ,
	grade  INT(10)
  );

1.查询student表的所有记录

mysql> select *from student;
+-----+--------+------+-------+--------------+--------------------+
| id  | name   | sex  | birth | department   | address            |
+-----+--------+------+-------+--------------+--------------------+
| 901 | 张鹏   | 男   |  1985 | 计算机系     | 北京市海淀区       |
| 902 | 李平   | 男   |  1986 | 中文系       | 北京市昌平区       |
| 903 | 李飞   | 女   |  1990 | 中文系       | 湖南省永州市       |
| 904 | 王晨   | 男   |  1990 | 英语系       | 辽宁省阜新市       |
| 905 | 王菲   | 女   |  1991 | 英语系       | 福建省厦门市       |
| 906 | 张震   | 男   |  1988 | 计算机系     | 湖南省衡阳市       |
+-----+--------+------+-------+--------------+--------------------+
6 rows in set (0.00 sec)

2.查询student表的第2条到4条记录

mysql> select *from student limit 1,3;
+-----+--------+------+-------+------------+--------------------+
| id  | name   | sex  | birth | department | address            |
+-----+--------+------+-------+------------+--------------------+
| 902 | 李平   | 男   |  1986 | 中文系     | 北京市昌平区       |
| 903 | 李飞   | 女   |  1990 | 中文系     | 湖南省永州市       |
| 904 | 王晨   | 男   |  1990 | 英语系     | 辽宁省阜新市       |
+-----+--------+------+-------+------------+--------------------+
3 rows in set (0.00 sec)

3.从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息

mysql> select id,name,department from student;
+-----+--------+--------------+
| id  | name   | department   |
+-----+--------+--------------+
| 901 | 张鹏   | 计算机系     |
| 902 | 李平   | 中文系       |
| 903 | 李飞   | 中文系       |
| 904 | 王晨   | 英语系       |
| 905 | 王菲   | 英语系       |
| 906 | 张震   | 计算机系     |
+-----+--------+--------------+
6 rows in set (0.00 sec)

4.从student表中查询计算机系和英语系的学生的信息

mysql> SELECT * FROM student WHERE department IN ('计算机系', '英语系');
+-----+--------+------+-------+--------------+--------------------+
| id  | name   | sex  | birth | department   | address            |
+-----+--------+------+-------+--------------+--------------------+
| 901 | 张鹏   | 男   |  1985 | 计算机系     | 北京市海淀区       |
| 904 | 王晨   | 男   |  1990 | 英语系       | 辽宁省阜新市       |
| 905 | 王菲   | 女   |  1991 | 英语系       | 福建省厦门市       |
| 906 | 张震   | 男   |  1988 | 计算机系     | 湖南省衡阳市       |
+-----+--------+------+-------+--------------+--------------------+
4 rows in set (0.00 sec)

5.从student表中查询年龄18~22岁的学生信息

mysql> select *from student where (2023-birth) between 18 and 22;
+-----+--------+------+-------+--------------+--------------------+
| id  | name   | sex  | birth | department   | address            |
+-----+--------+------+-------+--------------+--------------------+
| 902 | 李平   | 男   |  2001 | 中文系       | 北京市昌平区       |
| 903 | 李飞   | 女   |  2002 | 中文系       | 湖南省永州市       |
| 904 | 王晨   | 男   |  2005 | 英语系       | 辽宁省阜新市       |
| 905 | 王菲   | 女   |  2004 | 英语系       | 福建省厦门市       |
| 906 | 张震   | 男   |  2003 | 计算机系     | 湖南省衡阳市       |
+-----+--------+------+-------+--------------+--------------------+
5 rows in set (0.00 sec)

6.从student表中查询每个院系有多少人

mysql> select department,count(*) as 总人数 from student group by department;
+--------------+-----------+
| department   | 总人数    |
+--------------+-----------+
| 计算机系     |         2 |
| 中文系       |         2 |
| 英语系       |         2 |
+--------------+-----------+
3 rows in set (0.00 sec)

7.从score表中查询每个科目的最高分

mysql> SELECT c_name, max(grade) FROM score GROUP BY c_name;
+-----------+------------+
| c_name    | max(grade) |
+-----------+------------+
| 计算机    |         98 |
| 英语      |         94 |
| 中文      |         95 |
+-----------+------------+
3 rows in set (0.00 sec)

8.查询王晨的考试科目（c_name）和考试成绩（grade）

mysql> select student.name,score.c_name,score.grade from student inner join score on student.id = score.stu_id where student.name ='王晨';
+--------+-----------+-------+
| name   | c_name    | grade |
+--------+-----------+-------+
| 王晨   | 计算机    |    70 |
| 王晨   | 英语      |    92 |
+--------+-----------+-------+
2 rows in set (0.00 sec)

9.用连接的方式查询所有学生的信息和考试信息

mysql> select student.*,score.c_name,score.grade from student inner join score on student.id = score.stu_id;
+-----+--------+------+-------+--------------+--------------------+-----------+-------+
| id  | name   | sex  | birth | department   | address            | c_name    | grade |
+-----+--------+------+-------+--------------+--------------------+-----------+-------+
| 901 | 张鹏   | 男   |  2000 | 计算机系     | 北京市海淀区          | 计算机    |    98 |
| 901 | 张鹏   | 男   |  2000 | 计算机系     | 北京市海淀区          | 英语      |    80 |
| 902 | 李平   | 男   |  2001 | 中文系       | 北京市昌平区          | 计算机    |    65 |
| 902 | 李平   | 男   |  2001 | 中文系       | 北京市昌平区          | 中文      |    88 |
| 903 | 李飞   | 女   |  2002 | 中文系       | 湖南省永州市          | 中文      |    95 |
| 904 | 王晨   | 男   |  2005 | 英语系       | 辽宁省阜新市          | 计算机    |    70 |
| 904 | 王晨   | 男   |  2005 | 英语系       | 辽宁省阜新市          | 英语      |    92 |
| 905 | 王菲   | 女   |  2004 | 英语系       | 福建省厦门市          | 英语      |    94 |
| 906 | 张震   | 男   |  2003 | 计算机系     | 湖南省衡阳市          | 计算机    |    90 |
| 906 | 张震   | 男   |  2003 | 计算机系     | 湖南省衡阳市          | 英语      |    85 |
+-----+--------+------+-------+--------------+--------------------+-----------+-------+
10 rows in set (0.00 sec)

10.计算每个学生的总成绩

mysql> select student.id,student.name,sum(score.grade) as 总成绩 from student 
    -> inner join score on student.id = score.stu_id 
    -> group by student.id,student.name;
+-----+--------+-----------+
| id  | name   | 总成绩    |
+-----+--------+-----------+
| 901 | 张鹏   |       178 |
| 902 | 李平   |       153 |
| 903 | 李飞   |        95 |
| 904 | 王晨   |       162 |
| 905 | 王菲   |        94 |
| 906 | 张震   |       175 |
+-----+--------+-----------+
6 rows in set (0.00 sec)

11.计算每个考试科目的平均成绩

mysql> select c_name,avg(grade) from score group by c_name;
+-----------+------------+
| c_name    | avg(grade) |
+-----------+------------+
| 计算机    |    80.7500 |
| 英语      |    87.7500 |
| 中文      |    91.5000 |
+-----------+------------+
3 rows in set (0.00 sec)

12.查询计算机成绩低于95的学生信息

mysql> select student.*,score.c_name,grade from student inner join score on student.id = score.stu_id where score.c_name='计算机' and score.grade<=95;
+-----+--------+------+-------+--------------+--------------------+-----------+-------+
| id  | name   | sex  | birth | department   | address            | c_name    | grade |
+-----+--------+------+-------+--------------+--------------------+-----------+-------+
| 902 | 李平   | 男   |  2001 | 中文系       | 北京市昌平区       | 计算机    |    65 |
| 904 | 王晨   | 男   |  2005 | 英语系       | 辽宁省阜新市       | 计算机    |    70 |
| 906 | 张震   | 男   |  2003 | 计算机系     | 湖南省衡阳市       | 计算机    |    90 |
+-----+--------+------+-------+--------------+--------------------+-----------+-------+
3 rows in set (0.00 sec)

13.查询同时参加计算机和英语考试的学生的信息

#在同一个查询中不能对同一张表使用相同的表名，这将导致解析错误。
mysql> select student.*
    -> from student
    -> inner join score as tb1 on student.id = tb1.stu_id and tb1.c_name = '计算机'
    -> inner join score as tb2 on student.id = tb2.stu_id and tb2.c_name = '英语';
+-----+--------+------+-------+--------------+--------------------+
| id  | name   | sex  | birth | department   | address            |
+-----+--------+------+-------+--------------+--------------------+
| 901 | 张鹏   | 男   |  2000 | 计算机系     | 北京市海淀区       |
| 904 | 王晨   | 男   |  2005 | 英语系       | 辽宁省阜新市       |
| 906 | 张震   | 男   |  2003 | 计算机系     | 湖南省衡阳市       |
+-----+--------+------+-------+--------------+--------------------+
3 rows in set (0.00 sec)

14.将计算机考试成绩按从高到低进行排序

mysql> select c_name,grade from score where c_name='计算机' order by grade desc;
+-----------+-------+
| c_name    | grade |
+-----------+-------+
| 计算机    |    98 |
| 计算机    |    90 |
| 计算机    |    70 |
| 计算机    |    65 |
+-----------+-------+
4 rows in set (0.00 sec)

15.从student表和score表中查询出学生的学号，然后合并查询结果

mysql> select id from student
    -> union
    -> select stu_id from score;
+-----+
| id  |
+-----+
| 901 |
| 902 |
| 903 |
| 904 |
| 905 |
| 906 |
+-----+
6 rows in set (0.00 sec)

16.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩

mysql> select student.name,student.department,score.c_name,score.grade from student 
	-> inner join score on student.id = score.stu_id 
	-> where student.name like '张%' or student.name like '王%';
+--------+--------------+-----------+-------+
| name   | department   | c_name    | grade |
+--------+--------------+-----------+-------+
| 张鹏   | 计算机系     | 计算机    |    98 |
| 张鹏   | 计算机系     | 英语      |    80 |
| 王晨   | 英语系       | 计算机    |    70 |
| 王晨   | 英语系       | 英语      |    92 |
| 王菲   | 英语系       | 英语      |    94 |
| 张震   | 计算机系     | 计算机    |    90 |
| 张震   | 计算机系     | 英语      |    85 |
+--------+--------------+-----------+-------+
7 rows in set (0.00 sec)

17.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩

mysql> select student.name,year(current_date) - student.birth as age,student.department,score.c_name,score.grade,student.address from student 
    -> inner join score on student.id = score.stu_id 
    -> where student.address like '湖南%';
+--------+------+--------------+-----------+-------+--------------------+
| name   | age  | department   | c_name    | grade | address            |
+--------+------+--------------+-----------+-------+--------------------+
| 李飞   |   21 | 中文系       | 中文      |    95 | 湖南省永州市       |
| 张震   |   20 | 计算机系     | 计算机    |    90 | 湖南省衡阳市       |
| 张震   |   20 | 计算机系     | 英语      |    85 | 湖南省衡阳市       |
+--------+------+--------------+-----------+-------+--------------------+
3 rows in set (0.00 sec)

4.7 聚合函数

聚合函数对一组值进行运算，并返回单个值。

count(*),count(列名),COUNT(1)#统计行数
AVG()#求平均
SUM()#求和
MAX()#最大值
MIN()#最小值

4.7.1 **count(*),count(列名),COUNT(1)的区别：**

当表的数据量大些时，对表作分析之后，使用count(1)还要比使用count(*)用时多！
从执行计划来看，count(1)和count(*)的效果是一样的。但是在表做过分析之后，count(1)会比count(*)的用时少些（1w以内数据量），不过差不了多少。
如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。
因为count(*),自动会优化指定到那一个字段。所以没必要去count(1)，用count(*)，sql会帮你完成优化的因此：count(1)和count(*)基本没有差别！
count(1) and count(列名)
两者的主要区别是
（1） count(1) 会统计表中的所有的记录数，包含字段为null的记录。
（2） count(列名)会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。
count(*) 和 count(1)和count(列名)区别
执行效果上：
count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为NULL的计数。

执行效率上：
列名为主键，count(列名)会比count(1)快
列名不为主键，count(1)会比count(列名)快
如果表多个列并且没有主键，则 count(1)的执行效率优于count(*)
如果有主键，则select count(主键)的执行效率是最优的
如果表只有一个字段，则 select count(*)最优。
来自chatAI的回答

count(*), count(列名), 和 COUNT(1) 都是在 SQL
查询中使用的聚合函数，用于计算行的数量。它们之间的区别如下：

COUNT(*)：
COUNT(*) 是一种常见的用法，用于计算匹配查询条件的所有行的数量，不考虑任何特定的列或表达式。它会计算表中所有行的数量，包括重复值和 NULL 值。

COUNT(列名)：
COUNT(列名) 用于计算指定列中非 NULL 值的数量。它只计算指定列中有值的行数，不会计算 NULL 值。这在需要统计特定列中的非 NULL 值数量时很有用。

COUNT(1)：
COUNT(1) 是一种常见的优化技巧，它用于计算匹配查询条件的所有行的数量，但不实际提取表中的数据。通过使用 1 作为参数，避免了提取实际数据并在执行查询时能够更快地完成计算。这在你只关心行数而不关心实际数据内容时很有用。

总之，区别在于：
COUNT(*) 计算所有行的数量，包括重复值和 NULL 值。
COUNT(列名) 计算指定列中非 NULL 值的数量。
COUNT(1) 计算匹配查询条件的所有行的数量，但不实际提取数据。

选择使用哪个取决于你的需求：
如果你需要计算所有行的数量，包括 NULL 值和重复值，可以使用 COUNT(*)。
如果你只关心特定列中非NULL 值的数量，使用 COUNT(列名)。
如果你只关心行数而不关心实际数据内容，可以使用 COUNT(1) 来获得更好的性能。

4.8 数值型函数

ABS求绝对值

mysql> SELECT ABS(-5); 
+---------+
| ABS(-5) |
+---------+
|       5 |
+---------+
1 row in set (0.00 sec)

POW返回参数的幂次方

mysql> SELECT POW(2, 3);
+-----------+
| POW(2, 3) |
+-----------+
|         8 |
+-----------+
1 row in set (0.00 sec)

SQRT求平方根

mysql> SELECT SQRT(9); 
+---------+
| SQRT(9) |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)

MOD求余数

mysql> SELECT MOD(15, 4); 
+------------+
| MOD(15, 4) |
+------------+
|          3 |
+------------+
1 row in set (0.00 sec)

CEIL向上取整

mysql> SELECT CEIL(4.3);
+-----------+
| CEIL(4.3) |
+-----------+
|         5 |
+-----------+
1 row in set (0.00 sec)

FLOOR向下取整

mysql> SELECT FLOOR(4.3); 
+------------+
| FLOOR(4.3) |
+------------+
|          4 |
+------------+
1 row in set (0.00 sec)

RAND生成0-1之间的随机数，传入整数参数是用来产生重复序列

mysql> SELECT RAND(); 
+---------------------+
| RAND()              |
+---------------------+
| 0.14832024848863506 |
+---------------------+
1 row in set (0.00 sec)

ROUND四舍五入

mysql> SELECT ROUND(3.75); 
+-------------+
| ROUND(3.75) |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)
mysql> SELECT ROUND(3.4); 
+------------+
| ROUND(3.4) |
+------------+
|          3 |
+------------+
1 row in set (0.00 sec)

SIGN返回参数的符号

在这里插入代码片mysql> SELECT SIGN(-5);
+----------+
| SIGN(-5) |
+----------+
|       -1 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT SIGN(5);
+---------+
| SIGN(5) |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

4.9 字符串函数

LENGTH()返回字符串的字节长度

mysql> SELECT LENGTH('Hello');  
+-----------------+
| LENGTH('Hello') |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH('世界');
+------------------+
| LENGTH('世界')   |
+------------------+
|                6 |
+------------------+
1 row in set (0.00 sec)

CHAR_LENGTH返回字符串的字符长度

mysql> SELECT CHAR_LENGTH('世界');  
+-----------------------+
| CHAR_LENGTH('世界')   |
+-----------------------+
|                     2 |
+-----------------------+
1 row in set (0.00 sec)
mysql> SELECT CHAR_LENGTH('hello');
+----------------------+
| CHAR_LENGTH('hello') |
+----------------------+
|                    5 |
+----------------------+
1 row in set (0.00 sec)

CONCAT将多个字符串连接起来生成新的字符串

mysql> SELECT CONCAT('Hello', ' ', 'World');
+-------------------------------+
| CONCAT('Hello', ' ', 'World') |
+-------------------------------+
| Hello World                   |
+-------------------------------+
1 row in set (0.00 sec)

INSERT(str, pos, len, newstr)在指定位置插入字符串

mysql> select insert('Hello',3,0,'XYZ');
+---------------------------+
| insert('Hello',3,0,'XYZ') |
+---------------------------+
| HeXYZllo                  |
+---------------------------+
1 row in set (0.00 sec)
mysql> select insert('Hello',3,2,'XYZ');
+---------------------------+
| insert('Hello',3,2,'XYZ') |
+---------------------------+
| HeXYZo                    |
+---------------------------+
1 row in set (0.00 sec)

LOWER&UPPER大小写转换

mysql> SELECT UPPER('World');  
+----------------+
| UPPER('World') |
+----------------+
| WORLD          |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT lower('HELLO');
+----------------+
| lower('HELLO') |
+----------------+
| hello          |
+----------------+
1 row in set (0.00 sec)

LEFT&RIGHT返回左侧或者右侧指定数量的字符串

mysql> select left('hello',3);
+-----------------+
| left('hello',3) |
+-----------------+
| hel             |
+-----------------+
1 row in set (0.00 sec)

mysql> select right('hello',3);
+------------------+
| right('hello',3) |
+------------------+
| llo              |
+------------------+
1 row in set (0.00 sec)

TRIM去除字符串两侧的空格或指定的字符

mysql> SELECT TRIM('  Hello  ');
+-------------------+
| TRIM('  Hello  ') |
+-------------------+
| Hello             |
+-------------------+
1 row in set (0.00 sec)
#可搭配其他函数使用
mysql> SELECT TRIM(LEADING '0' FROM '00012345');
+-----------------------------------+
| TRIM(LEADING '0' FROM '00012345') |
+-----------------------------------+
| 12345                             |
+-----------------------------------+
1 row in set (0.00 sec)

REPLACE(s, s1, s2)把字符串中子字符串 s1 替换为 s2

mysql> select replace('hello,world','o','e');
+--------------------------------+
| replace('hello,world','o','e') |
+--------------------------------+
| helle,werld                    |
+--------------------------------+
1 row in set (0.00 sec)

SUBSTRING(s,n,len)返回从字符串s的位置n开始len长度的字符串

mysql> SELECT SUBSTRING('Hello World', 2, 4);
+--------------------------------+
| SUBSTRING('Hello World', 2, 4) |
+--------------------------------+
| ello                           |
+--------------------------------+
1 row in set (0.00 sec)

REVERSE反转字符串顺序

mysql> select reverse('hello');
+------------------+
| reverse('hello') |
+------------------+
| olleh            |
+------------------+
1 row in set (0.00 sec)

STRCMP(expr1, expr2)比较字符串表达式，返回整数结果

字符串根据其字符的ASCII码或Unicode码来排序。例如，"apple"在字典顺序中排在"banana"之前，因为字母"A"的ASCII码小于字母"B"的ASCII码。

mysql> select strcmp('d','e');
+-----------------+
| strcmp('d','e') |
+-----------------+
|              -1 |
+-----------------+
1 row in set (0.00 sec)
mysql> select strcmp('z','a');
+-----------------+
| strcmp('z','a') |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)
mysql> select strcmp('z','z');
+-----------------+
| strcmp('z','z') |
+-----------------+
|               0 |
+-----------------+
1 row in set (0.00 sec)

LOCATE(substr,str[,pos])&INSTR(str,substr)在str中查找子串substr的位置，可指定pos为起始搜索位置

mysql> select locate('el','hello');
+----------------------+
| locate('el','hello') |
+----------------------+
|                    2 |
+----------------------+
1 row in set (0.00 sec)
mysql> select instr('el','hello');
+---------------------+
| instr('el','hello') |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0.00 sec)
mysql> select instr('hello','el');
+---------------------+
| instr('hello','el') |
+---------------------+
|                   2 |
+---------------------+
1 row in set (0.00 sec)

4.10 日期和时间函数

CURDATE() / CURRENT_DATE() / CURRENT_DATE&CURTIME / CURRENT_TIME()获取当前日期/时间

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2023-08-15 |
+------------+
1 row in set (0.00 sec)
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 20:25:13  |
+-----------+
1 row in set (0.00 sec)

NOW&SYSDATE获取当前系统的日期和时间

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2023-08-15 20:25:59 |
+---------------------+
1 row in set (0.00 sec)

DATE&TIME分别提取日期和时间

mysql> SELECT DATE(NOW());
+-------------+
| DATE(NOW()) |
+-------------+
| 2023-08-15  |
+-------------+
1 row in set (0.00 sec)
mysql> SELECT TIME(NOW());
+-------------+
| TIME(NOW()) |
+-------------+
| 20:32:54    |
+-------------+
1 row in set (0.00 sec)

MONTH/MONTHNAME返回月份或月份名称

mysql> SELECT MONTH(NOW());
+--------------+
| MONTH(NOW()) |
+--------------+
|            8 |
+--------------+
1 row in set (0.01 sec)
mysql> SELECT MONTHNAME(NOW());
+------------------+
| MONTHNAME(NOW()) |
+------------------+
| August           |
+------------------+
1 row in set (0.00 sec)

DAYNAME指定日期的周几名称

mysql> SELECT DAYNAME('2023-08-14');
+-----------------------+
| DAYNAME('2023-08-14') |
+-----------------------+
| Monday                |
+-----------------------+
1 row in set (0.00 sec)
mysql> SELECT DAYNAME(NOW());
+----------------+
| DAYNAME(NOW()) |
+----------------+
| Tuesday        |
+----------------+
1 row in set (0.00 sec)

YEAR指定日期的年份

mysql> SELECT YEAR(NOW());
+-------------+
| YEAR(NOW()) |
+-------------+
|        2023 |
+-------------+
1 row in set (0.00 sec)

DAYOFWEEK指定日期是周几/DAYOFYEAR日期在年份中的天数/DAYOFMONTH&DAY指定日期的月份中的天数

mysql> SELECT DAYOFWEEK(NOW());#从周天开始算的
+------------------+
| DAYOFWEEK(NOW()) |
+------------------+
|                3 |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT DAYOFYEAR(NOW());
+------------------+
| DAYOFYEAR(NOW()) |
+------------------+
|              227 |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT DAYOFMONTH(NOW());
+-------------------+
| DAYOFMONTH(NOW()) |
+-------------------+
|                15 |
+-------------------+
1 row in set (0.01 sec)
mysql> SELECT DAY(NOW());
+------------+
| DAY(NOW()) |
+------------+
|         15 |
+------------+
1 row in set (0.01 sec)

WEEK指定日期在年份中的周数

mysql> select week(now());
+-------------+
| week(now()) |
+-------------+
|          33 |
+-------------+
1 row in set (0.00 sec)

DATEDIFF(expr1,expr2)计算日期之间的天数差

mysql> SELECT DATEDIFF('2023-08-20', '2023-08-15');
+--------------------------------------+
| DATEDIFF('2023-08-20', '2023-08-15') |
+--------------------------------------+
|                                    5 |
+--------------------------------------+
1 row in set (0.00 sec)

SEC_TO_TIME&TIME_TO_SEC秒转时间或者时间转秒

mysql> select sec_to_time(3685);
+-------------------+
| sec_to_time(3685) |
+-------------------+
| 01:01:25          |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT TIME_TO_SEC('01:01:25');
+-------------------------+
| TIME_TO_SEC('01:01:25') |
+-------------------------+
|                    3685 |
+-------------------------+
1 row in set (0.00 sec)

4.11 流程控制函数

CASE: CASE 表达式允许您在 SQL 查询中执行条件性操作。它可以根据不同的条件返回不同的值。

SELECT name, 
       CASE 
         WHEN age < 18 THEN 'Minor'
         WHEN age >= 18 AND age < 65 THEN 'Adult'
         ELSE 'Senior'
       END AS age_group
FROM users;

在这个示例中，根据用户的年龄，我们使用 CASE 表达式创建了一个名为 age_group 的列，用于表示用户的年龄段。

IFNULL(v1, v2): IFNULL 函数用于判断第一个表达式是否为 NULL，如果为 NULL，则返回第二个表达式的值，否则返回第一个表达式的值。

SELECT name, IFNULL(email, 'N/A') AS email
FROM users;

在这个示例中，我们从 users 表中选择用户的姓名和电子邮件地址。如果电子邮件地址为 NULL，则将返回 'N/A'。

IF(expr, v1, v2): IF 函数根据一个条件表达式返回两个值中的一个。如果条件表达式为真，则返回第一个值 v1，否则返回第二个值 v2。

SELECT name, IF(age >= 18, 'Adult', 'Minor') AS age_group
FROM users;

在这个示例中，我们使用 IF 函数根据用户的年龄是否大于等于 18 来确定用户的年龄段。

第五章事务

在 MySQL 中，事务特性和隔离机制是确保数据库操作的一致性和隔离性的关键概念。ACID 是事务特性，表示原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。隔离级别则是定义了事务之间的隔离程度，以确保多个事务同时执行时的数据一致性。

ACID特性：

**原子性（atomicity）：**原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

**一致性（consistency）：**事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

** 隔离性（isolation) ：**事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

持久性（durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

事务的隔离级别，

	脏读	不可重复读	幻读
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable read	×	×	√
Serializable	×	x	x

数据库共定义了四种隔离级别：

Serializable（串行化）：可避免脏读、不可重复读、虚读情况的发生。
Repeatable read（可重复读）：可避免脏读、不可重复读情况的发生。
Read committed（读已提交）：可避免脏读情况发生。
Read uncommitted(读未提交)：最低级别，以上情况均无法保证。

数据一致性问题：

脏读：

脏读指的是一个事务读取了另一个事务尚未提交的数据。这意味着，一个事务读取了其他事务的临时修改，但如果其他事务最终回滚，读取的数据就是无效的。脏读可能导致事务基于错误或不一致的数据做出决策。

不可重复读：

不可重复读指的是在同一个事务内，对同一行数据进行了多次读取，但得到的结果不同。这可能是因为另一个事务修改了该行数据并已提交，导致第一个事务获取不同的结果。不可重复读可能导致数据的连续性问题。

幻读：

幻读指的是在同一个事务内，多次执行一个查询，但结果集的行数不同。这可能是因为另一个事务在查询之间插入了新的数据行，导致第一个事务获取了不同的结果。幻读问题主要与范围查询有关，例如在一个范围内查询了多次。

示例4：隔离级别的作用

READ UNCOMMITTED的演示

#查看当前的隔离级别
mysql>  show variables like '%isolation%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.01 sec)
#设置当前会话的隔离级别为读未提交
mysql> set session transaction isolation level READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql>  show variables like '%isolation%';
+-----------------------+------------------+
| Variable_name         | Value            |
+-----------------------+------------------+
| transaction_isolation | READ-UNCOMMITTED |
+-----------------------+------------------+
1 row in set (0.00 sec)

两个会话同时设置未读未提交，并开启事务
在这里插入图片描述

步骤一：会话1/2查表可见1006为教学部，开启事务；
步骤二：会话1更新1006为开发部，会话2查表可见1006也为开发部，可见2查到了1未提交的数据；
步骤三：会话1回滚，会话2又查到了1006为教学部；没解决这三个问题
在这里插入图片描述

#会话1语句
mysql>  show variables like '%isolation%';
+-----------------------+------------------+
| Variable_name         | Value            |
+-----------------------+------------------+
| transaction_isolation | READ-UNCOMMITTED |
+-----------------------+------------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from department;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|    1001 | 科技部    |
|    1002 | 后勤部    |
|    1003 | 财务部    |
|    1004 | 行政部    |
|    1005 | 法务部    |
|    1006 | 教学部    |
+---------+-----------+
6 rows in set (0.00 sec)

mysql> update department set dept_name='开发部' where dept_id=1006;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

#会话2语句
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select dept_id,dept_name from department where dept_id=1006;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|    1006 | 教学部    |
+---------+-----------+
1 row in set (0.00 sec)

mysql> select dept_id,dept_name from department where dept_id=1006;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|    1006 | 开发部    |
+---------+-----------+
1 row in set (0.00 sec)

mysql> select dept_id,dept_name from department where dept_id=1006;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|    1006 | 教学部    |
+---------+-----------+
1 row in set (0.00 sec)

READ COMMITTED的演示

步骤1：设置会话1/2的隔离级别为READ COMMITTED；

步骤2：会话1更新数据1006为开发部，会话二查1006仍为教学部，可见事务B无法读到A未提交的数据，解决脏读的问题；

步骤3：事务A提交数据，此时B查表可以看到1006为开发部；解决了脏读；

在这里插入图片描述

#事务A
mysql> set session transaction isolation level READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql>  show variables like '%isolation%';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update department set dept_name='开发部' where dept_id=1006;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
#事务B
mysql> set session transaction isolation level READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%isolation%';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select dept_id,dept_name from department where dept_id=1006;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|    1006 | 教学部    |
+---------+-----------+
1 row in set (0.00 sec)

mysql> select dept_id,dept_name from department where dept_id=1006;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|    1006 | 教学部    |
+---------+-----------+
1 row in set (0.00 sec)

mysql> select dept_id,dept_name from department where dept_id=1006;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|    1006 | 开发部    |
+---------+-----------+
1 row in set (0.00 sec)

REPETABLE READ的演示

步骤1；设置隔离级别REPETABLE READ，开启事务

步骤2：事务B查表，可见为开发部

步骤3：事务A更新表1006为运维部，并提交；事务B查表还是开发部

解决不可重复读的问题
在这里插入图片描述

#事务A
mysql> set session transaction isolation level REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

mysql>  show variables like '%isolation%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.01 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update department set dept_name='运维部' where dept_id=1006;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
#事务B
mysql> set session transaction isolation level REPEATABLE READ;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like '%isolation%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select dept_id,dept_name from department where dept_id=1006;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|    1006 | 开发部    |
+---------+-----------+
1 row in set (0.00 sec)

mysql> select dept_id,dept_name from department where dept_id=1006;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|    1006 | 开发部    |
+---------+-----------+
1 row in set (0.00 sec)

mysql> select dept_id,dept_name from department where dept_id=1006;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|    1006 | 开发部    |
+---------+-----------+
1 row in set (0.00 sec)

SERIALIZABLE的演示

步骤一：事务A/B设置隔离级别，开启事务
步骤二：事务AB同时查表
步骤三：事务A执行更新操作，此时无法更新，需要事务B提交后才可以执行
在这里插入图片描述

#事务A
mysql>  show variables like '%isolation%';
+-----------------------+--------------+
| Variable_name         | Value        |
+-----------------------+--------------+
| transaction_isolation | SERIALIZABLE |
+-----------------------+--------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select dept_id,dept_name from department where dept_id=1006;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|    1006 | 就业部    |
+---------+-----------+
1 row in set (0.00 sec)

mysql> update department set dept_name='企划部' where dept_id=1006;
Query OK, 1 row affected (33.38 sec)
Rows matched: 1  Changed: 1  Warnings: 0
#事务B
mysql> show variables like '%isolation%';
+-----------------------+--------------+
| Variable_name         | Value        |
+-----------------------+--------------+
| transaction_isolation | SERIALIZABLE |
+-----------------------+--------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select dept_id,dept_name from department where dept_id=1006;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|    1006 | 就业部    |
+---------+-----------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)