导图社区 MySQL操作

MySQL操作

这是由EdrawMax（亿图图示）精心制作的MySQL 全栈知识体系思维导图模板，采用树状结构与图文结合的形式，完整覆盖从基础入门到高级进阶的 MySQL 核心知识点，是数据库领域学习、开发与面试备考的高效参考工具。模板以mysql-all为核心，拆解为六大核心模块：基本数据类型模块详细梳理关键字、各类字段类型及约束规则；数据设计模块包含表结构设计、范式理论、ER 图绘制与 SQL 语句实操，搭配示例表直观展示表关系；SQL 语句操作分类模块清晰划分 DQL 数据查询、DML 数据操作、DDL 数据定义、DCL 数据控制、TCL 事务控制等核心语法；复杂业务场景求解模块提供事务处理、索引优化、锁机制应用等实战案例；高级特性模块深入讲解存储引擎、索引优化、SQL 调优、主从复制、读写分离等进阶技术；同时附环境搭建与基础认知指引，帮助使用者构建完整的 MySQL 知识框架。该模板为思维导图绘图类型，逻辑严谨、重点突出，适合计算机专业学生、后端开发工程师、数据库管理员（DBA）、IT 校招 / 社招面试备考者、大数据开发人员、数据库性能优化工程师等人群使用。

编辑于2026-03-13 13:58:10

mysql
sql
数据库设计
数据库操作
复杂业务思路求解

yll10243

他的近期作品查看更多>>

MySQL操作
这是由EdrawMax（亿图图示）精心制作的MySQL 全栈知识体系思维导图模板，采用树状结构与图文结合的形式，完整覆盖从基础入门到高级进阶的 MySQL 核心知识点，是数据库领域学习、开发与面试备考的高效参考工具。模板以mysql-all为核心，拆解为六大核心模块：基本数据类型模块详细梳理关键字、各类字段类型及约束规则；数据设计模块包含表结构设计、范式理论、ER 图绘制与 SQL 语句实操，搭配示例表直观展示表关系；SQL 语句操作分类模块清晰划分 DQL 数据查询、DML 数据操作、DDL 数据定义、DCL 数据控制、TCL 事务控制等核心语法；复杂业务场景求解模块提供事务处理、索引优化、锁机制应用等实战案例；高级特性模块深入讲解存储引擎、索引优化、SQL 调优、主从复制、读写分离等进阶技术；同时附环境搭建与基础认知指引，帮助使用者构建完整的 MySQL 知识框架。该模板为思维导图绘图类型，逻辑严谨、重点突出，适合计算机专业学生、后端开发工程师、数据库管理员（DBA）、IT 校招 / 社招面试备考者、大数据开发人员、数据库性能优化工程师等人群使用。

MySQL操作

社区模板帮助中心，点此进入>>

yll10243

他的近期作品查看更多>>

MySQL操作
这是由EdrawMax（亿图图示）精心制作的MySQL 全栈知识体系思维导图模板，采用树状结构与图文结合的形式，完整覆盖从基础入门到高级进阶的 MySQL 核心知识点，是数据库领域学习、开发与面试备考的高效参考工具。模板以mysql-all为核心，拆解为六大核心模块：基本数据类型模块详细梳理关键字、各类字段类型及约束规则；数据设计模块包含表结构设计、范式理论、ER 图绘制与 SQL 语句实操，搭配示例表直观展示表关系；SQL 语句操作分类模块清晰划分 DQL 数据查询、DML 数据操作、DDL 数据定义、DCL 数据控制、TCL 事务控制等核心语法；复杂业务场景求解模块提供事务处理、索引优化、锁机制应用等实战案例；高级特性模块深入讲解存储引擎、索引优化、SQL 调优、主从复制、读写分离等进阶技术；同时附环境搭建与基础认知指引，帮助使用者构建完整的 MySQL 知识框架。该模板为思维导图绘图类型，逻辑严谨、重点突出，适合计算机专业学生、后端开发工程师、数据库管理员（DBA）、IT 校招 / 社招面试备考者、大数据开发人员、数据库性能优化工程师等人群使用。

相似推荐
大纲

互联网9大思维
- 37.2k
- 961
- 2.4k
- 400
- 0
MindMaster
组织架构-单商户商城webAPP 思维导图。
- 16.7k
- 3
- 186
- 9
- 1
Kacyun
域控上线
- 3.0k
- 169
- 11
- 4
- 0
jackrao
python思维导图
- 7.4k
- 548
- 242
- 7
- 0
(*^▽^*)
css
- 2.6k
- 1
- 43
- 3
- 0
A张舫
CSS
- 4.8k
- 270
- 189
- 33
- 0
journey
计算机操作系统思维导图
- 6.1k
- 350
- 206
- 18
- 0
journey
计算机组成原理
- 2.8k
- 98
- 70
- 8
- 0
journey
IMX6UL(A7)
- 1.6k
- 41
- 5
- 0
- 0
Handler XU
考试学情分析系统
- 2.2k
- 51
- 10
- 1
- 0
蒋龙

mysql-all

资料收集

关键字收集

https://dev.mysql.com/doc/refman/9.3/en/keywords.html

https://www.postgresql.org/docs/current/static/sql-keywords-appendix.html

收集

https://juejin.cn/post/7579673620939620402

文档手册

https://dev.mysql.com/doc/refman/8.4/en/

sqlite的关键字

https://www.sqlite.org/lang_keywords.html

环境搭建和整体认识

环境搭建

win11安装mysql8

安装流程

1，下载mysql8.xx.64.zip的64位的zip压缩文件

2，解压并创建文件my.ini

[mysqld] # 设置3306端口 port=3306 # 设置mysql的安装目录 ----------是你的文件路径------------- basedir=C:\application\mysql-8.0.28 # 设置mysql数据库的数据的存放目录 ---------是你的文件路径data文件夹自行创建 datadir=C:\application\mysql-8.0.28\data # 允许最大连接数 max_connections=200 # 允许连接失败的次数。 max_connect_errors=10 # 服务端使用的字符集默认为utf8mb4 character-set-server=utf8mb4 # 创建新表时将使用的默认存储引擎 default-storage-engine=INNODB # 默认使用“mysql_native_password”插件认证 #mysql_native_password default_authentication_plugin=mysql_native_password [mysql] # 设置mysql客户端默认字符集 default-character-set=utf8mb4 [client] # 设置mysql客户端连接服务端时默认使用的端口 port=3306 default-character-set=utf8mb4

3，配置环境变量

1，新建变量

key MYSQL_HOME

value C:\application\mysql-8.0.28

2，在path中添加

%MYSQL_HOME%\bin

4，以管理员启动终端执行初始化

mysqld --initialize --console

会生成一个随机的密码

5，安装服务并启动

mysqld --install mysql

6，启动服务

net start mysql

net stop mysql

7，登录

mysql -uroot -p

密码是第5步生成的密钥

修改密码 ALTER USER 'root'@'localhost' IDENTIFIED BY 'root123456';

忘记密码的操作

1，mysqld --console --skip-grant-tables --shared-memory进行启动终端

2，新开一个管理员权限的终端

mysql -uroot -p 不用密码登录

3，选择使用数据库

use mysql;

4，密码重置

update user set authentication_string='' where user='root';

5，刷新权限

flush privileges;

6，密码修改

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

7，exit或quit退出终端

8，关闭第一步打开的终端

常用操作(以管理员权限启动)

net start mysql

net stop mysql

mysql -uroot -p

linux的mysql5的参考核心流程

环境准备

上传mysql-5.6.10-linux-glibc2.5-x86_64.tar.gz

依赖安装

yum -y install perl

yum -y install perl-devel

yum -y install 'perl(Data::Dumper)'

安装5.6.10

解压

tar -zxvf mysql-5.6.10-linux-glibc2.5-x86_64.tar.gz

初始化安装

创建目录

初始化

cd /usr/local/mysql/scripts

./mysql_install_db --user=root --basedir=/usr/local/mysql --datadir=/data/mysql --pid-file=/data/mysql/mysql.pid --tmpdir=/tmp --explicit_defaults_for_timestamp=true

配置my.cnf文件

vi /etc/my.cnf basedir = /usr/local/mysql datadir = /data/mysql socket = /tmp/mysql.sock log-error = /data/mysql/error.log pid-file = /data/mysql/mysql.pid user = root tmpdir = /tmp

修改环境变量

vi /etc/profile export MYSQL_HOME=/usr/local/mysql export PATH=$MYSQL_HOME/bin:$PATH source /etc/profile

启动mysql

service mysql start

登录mysql，修改root密码

mysql>use mysql; mysql>update user set password=password('root') where user='root' and host='localhost'; mysql>flush privileges;

允许远程访问

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

flush privileges;

关闭防火墙

systemctl stop firewalld.service

systemctl disable firewalld.service

多实例安装

解压缩到不同的目录，比如mysql3306,mysql3307,mysql3308

basedir = /home/mysql/mysql3306 datadir = /home/mysql/mysql3306/data port = 3306 server_id = 1

5.6升级5.7

启动命令

关闭命令

安装5.7.9

准备工作

单实例安装

shell> bin/mysqld --initialize --user=mysql # MySQL 5.7.6 and up

shell> bin/mysql_ssl_rsa_setup # MySQL 5.7.6 and up

shell> bin/mysqld_safe --user=mysql &

多实例安装

[mysqld1] server-id = 11

[mysqld2] server-id = 12 socket = /tmp/mysql.sock2

连接数配置

show variables like '%max_connections%';

mysql的配置

https://dev.mysql.com/doc/refman/5.7/en/option-files.html

# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the # *** default location during install, and will be replaced if you # *** upgrade to a newer version of MySQL. [mysqld] # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. # basedir = ..... # datadir = ..... # port = ..... # server_id = ..... # socket = ..... # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

其它平台的参考

[client] port = 3306 socket = /tmp/mysql.sock [mysqld] ###############################基础设置##################################### #Mysql服务的唯一编号每个mysql服务Id需唯一 server-id = 1 #服务端口号默认3306 port = 3306 #mysql安装根目录 basedir = /opt/mysql #mysql数据文件所在位置 datadir = /opt/mysql/data #临时目录比如load data infile会用到 tmpdir = /tmp #设置socke文件所在目录 socket = /tmp/mysql.sock #主要用于MyISAM存储引擎,如果多台服务器连接一个数据库则建议注释下面内容 skip-external-locking #只能用IP地址检查客户端的登录，不用主机名 skip_name_resolve = 1 #事务隔离级别，默认为可重复读，mysql默认可重复读级别（此级别下可能参数很多间隙锁，影响性能） transaction_isolation = READ-COMMITTED #数据库默认字符集,主流字符集支持一些特殊表情符号（特殊表情符占用4个字节） character-set-server = utf8mb4 #数据库字符集对应一些排序等规则，注意要和character-set-server对应 collation-server = utf8mb4_general_ci #设置client连接mysql时的字符集,防止乱码 init_connect='SET NAMES utf8mb4' #是否对sql语句大小写敏感，1表示不敏感 lower_case_table_names = 1 #最大连接数 max_connections = 400 #最大错误连接数 max_connect_errors = 1000 #TIMESTAMP如果没有显示声明NOT NULL，允许NULL值 explicit_defaults_for_timestamp = true #SQL数据包发送的大小，如果有BLOB对象建议修改成1G max_allowed_packet = 128M #MySQL连接闲置超过一定时间后(单位：秒)将会被强行关闭 #MySQL默认的wait_timeout 值为8个小时, interactive_timeout参数需要同时配置才能生效 interactive_timeout = 1800 wait_timeout = 1800 #内部内存临时表的最大值，设置成128M。 #比如大数据量的group by ,order by时可能用到临时表， #超过了这个值将写入磁盘，系统IO压力增大 tmp_table_size = 134217728 max_heap_table_size = 134217728 #禁用mysql的缓存查询结果集功能 #后期根据业务情况测试决定是否开启 #大部分情况下关闭下面两项 query_cache_size = 0 query_cache_type = 0 #####################用户进程分配到的内存设置BEGIN############################# ##每个session将会分配参数设置的内存大小 #用于表的顺序扫描，读出的数据暂存于read_buffer_size中，当buff满时或读完，将数据返回上层调用者 #一般在128kb ~ 256kb,用于MyISAM #read_buffer_size = 131072 #用于表的随机读取，当按照一个非索引字段排序读取时会用到， #一般在128kb ~ 256kb,用于MyISAM #read_rnd_buffer_size = 262144 #order by或group by时用到 #建议先调整为2M，后期观察调整 sort_buffer_size = 2097152 #一般数据库中没什么大的事务，设成1~2M，默认32kb binlog_cache_size = 524288 ########################用户进程分配到的内存设置END############################ #在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中 #官方建议back_log = 50 + (max_connections / 5),封顶数为900 back_log = 130 ############################日志设置########################################## #数据库错误日志文件 log_error = error.log #慢查询sql日志设置 slow_query_log = 1 slow_query_log_file = slow.log #检查未使用到索引的sql log_queries_not_using_indexes = 1 #针对log_queries_not_using_indexes开启后，记录慢sql的频次、每分钟记录的条数 log_throttle_queries_not_using_indexes = 5 #作为从库时生效,从库复制中如何有慢sql也将被记录 log_slow_slave_statements = 1 #慢查询执行的秒数，必须达到此值可被记录 long_query_time = 8 #检索的行数必须达到此值才可被记为慢查询 min_examined_row_limit = 100 #mysql binlog日志文件保存的过期时间，过期后自动删除 expire_logs_days = 5 ############################主从复制设置##################################### #开启mysql binlog功能 log-bin=mysql-bin #binlog记录内容的方式，记录被操作的每一行 binlog_format = ROW #对于binlog_format = ROW模式时，减少记录日志的内容，只记录受影响的列 binlog_row_image = minimal #master status and connection information输出到表mysql.slave_master_info中 master_info_repository = TABLE #the slave's position in the relay logs输出到表mysql.slave_relay_log_info中 relay_log_info_repository = TABLE #作为从库时生效,想进行级联复制，则需要此参数 log_slave_updates #作为从库时生效,中继日志relay-log可以自我修复 relay_log_recovery = 1 #作为从库时生效,主从复制时忽略的错误 slave_skip_errors = ddl_exist_errors #####################redo log和binlog的关系设置BEGIN######################### #(步骤1) prepare dml相关的SQL操作，然后将redo log buff中的缓存持久化到磁盘 #(步骤2)如果前面prepare成功，那么再继续将事务日志持久化到binlog #(步骤3)如果前面成功，那么在redo log里面写上一个commit记录 #当innodb_flush_log_at_trx_commit和sync_binlog都为1时是最安全的， #在mysqld服务崩溃或者服务器主机crash的情况下，binary log只有可能丢失最多一个语句或者一个事务。 #但是都设置为1时会导致频繁的io操作，因此该模式也是最慢的一种方式。 #当innodb_flush_log_at_trx_commit设置为0，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。 #当innodb_flush_log_at_trx_commit设置为2，只有在操作系统崩溃或者系统掉电的情况下，上一秒钟所有事务数据才可能丢失。 #commit事务时,控制redo log buff持久化磁盘的模式默认为1 innodb_flush_log_at_trx_commit = 2 #commit事务时,控制写入mysql binlog日志的模式默认为0 #innodb_flush_log_at_trx_commit和sync_binlog都为1时，mysql最为安全但性能上压力也是最大 sync_binlog = 1 ####################redo log和binlog的关系设置END############################ ############################Innodb设置##################################### #数据块的单位8k，默认是16k，16kCPU压力稍小，8k对select的吞吐量大 #innodb_page_size的参数值也影响最大索引长度，8k比16k的最大索引长度小 #innodb_page_size = 8192 #一般设置物理存储的60% ~ 70% innodb_buffer_pool_size = 1G #5.7.6之后默认16M #innodb_log_buffer_size = 16777216 #该参数针对unix、linux，window上直接注释该参数.默认值为NULL #O_DIRECT减少操作系统级别VFS的缓存和Innodb本身的buffer缓存之间的冲突 innodb_flush_method = O_DIRECT #此格式支持压缩, 5.7.7之后为默认值 innodb_file_format = Barracuda #CPU多核处理能力设置，假设CPU是2颗4核的，设置如下 #读多，写少可以设成2:6的比例 innodb_write_io_threads = 4 innodb_read_io_threads = 4 #提高刷新脏页数量和合并插入数量，改善磁盘I/O处理能力 #默认值200（单位：页） #可根据磁盘近期的IOPS确定该值 innodb_io_capacity = 500 #为了获取被锁定的资源最大等待时间，默认50秒，超过该时间会报如下错误: # ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction innodb_lock_wait_timeout = 30 #调整buffer pool中最近使用的页读取并dump的百分比,通过设置该参数可以减少转储的page数 innodb_buffer_pool_dump_pct = 40 #设置redoLog文件所在目录, redoLog记录事务具体操作内容 innodb_log_group_home_dir = /opt/mysql/redolog/ #设置undoLog文件所在目录, undoLog用于事务回滚操作 innodb_undo_directory = /opt/mysql/undolog/ #在innodb_log_group_home_dir中的redoLog文件数, redoLog文件内容是循环覆盖写入。 innodb_log_files_in_group = 3 #MySql5.7官方建议尽量设置的大些，可以接近innodb_buffer_pool_size的大小 #之前设置该值较大时可能导致mysql宕机恢复时间过长，现在恢复已经加快很多了 #该值减少脏数据刷新到磁盘的频次 #最大值innodb_log_file_size * innodb_log_files_in_group <= 512GB,单文件<=256GB innodb_log_file_size = 1024M #设置undoLog文件所占空间可以回收 #5.7之前的MySql的undoLog文件一直增大无法回收 innodb_undo_log_truncate = 1 innodb_undo_tablespaces = 3 innodb_undo_logs = 128 #5.7.7默认开启该参数控制单列索引长度最大达到3072 #innodb_large_prefix = 1 #5.7.8默认为4个, Inodb后台清理工作的线程数 #innodb_purge_threads = 4 #通过设置配置参数innodb_thread_concurrency来限制并发线程的数量， #一旦执行线程的数量达到这个限制，额外的线程在被放置到对队列中之前，会睡眠数微秒， #可以通过设定参数innodb_thread_sleep_delay来配置睡眠时间 #该值默认为0,在官方doc上，对于innodb_thread_concurrency的使用，也给出了一些建议: #(1)如果一个工作负载中，并发用户线程的数量小于64，建议设置innodb_thread_concurrency=0； #(2)如果工作负载一直较为严重甚至偶尔达到顶峰，建议先设置innodb_thread_concurrency=128, ###并通过不断的降低这个参数，96, 80, 64等等，直到发现能够提供最佳性能的线程数 #innodb_thread_concurrency = 0 #强所有发生的死锁错误信息记录到error.log中，之前通过命令行只能查看最近一次死锁信息 innodb_print_all_deadlocks = 1 ############################其他设置######################################## [mysqldump] quick max_allowed_packet = 128M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 20M sort_buffer_size = 256k read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [mysqld_safe] #增加每个进程的可打开文件数量 open-files-limit = 28192

mysql架构

概述

好处

提高可用性

提高性能

集群的可用方案

mysql-Cluster

优势：可用性非常高，性能非常好

维护非常复杂，存在部分Bug

线上系统，所以不推荐

DRBD磁盘网络镜像

Distributed Replicated Block Device

看做一种网络RAID

mysql-Replication

通过简单的增加价格低廉的硬件设备成倍甚至成数量级地提高了原有系统的性能

图示

连接器内部

mysql复制

what-is

将主数据库的 DDL和 DML 操作通过二进制日志传到复制服务器

DML（data manipulation language）数据操纵语言

SELECT、UPDATE、INSERT、DELETE

DDL（data definition language）数据库定义语言

CREATE、ALTER、DROP

原理

主库在事务提交时会把数据变更作为事件 Events 记录在二进制日志文件Binlog中

推送二进制日志文件 Binlog中的事件到从库的中继日志 Relay Log

从库根据中继日志 Relay Log重做数据变更操作

三个线程

Binlog Dump线程跑在主库上

I/0线程和 SQL线程跑在从库上

SHOW PROCESSLIST命令

各类文件解析

日志文件

二进制日志文件( Binlog)和中继日志文件( Relay Log)

不会记录 Select操作

SQL线程会自动删除当前中继日志文件 Relay Log

三种复制技术

Statement:基于 SQL语句级别的 Binlog

默认

Row:基于行级别,记录每一行数据的变化

Mixed:混合Statement和Row模式

Archive

组成

以zlib对表数据进行压缩，磁盘I/O更少

数据存储在ARZ为后缀的文件中

特点

只支持insert和select操作

只允许在自增ID列上加索引

场景

日志和数据采集应用

Memory

也称HEAP存储引擎，所有数据保存在内存中

特性

支持HASH索引和BTree索引

所有字段都是固定长度 varchar(10) = char(10)

Row_format=fixed

不支持Blog和Text等大字段

Memory存储引擎使用表级锁

最大大小由max_heap_table_size参数决定

临时表

系统使用临时表

超过限制用Myisam

未超限制使用Memory

自己建立临时表

create temporary table

使用场景

hash索引用于查找或者是映射表（邮编和地区的对应表）

用于保存数据分析中产生的中间表

用于缓存周期性聚合数据的结果表

Ferderated

默认禁止，启用需要再启动时增加federated参数

mysql://user_name[:password]@hostname[:port_num]/db_name/table_name

执行过程

1，连接器

建立和维护连接

连接器负责与客户端的通信，其使用的是半双工模式（一种通信方式，其中通信双方可以交替地发送和接收数据，但不能同时进行发送和接收）。首先是与访问的客户端建立TCP连接，服务器有专门的TCP连接池，采用长连接模式复用TCP连接，经过三次握手建立连接成功后，之后会对TCP传输过来的账号密码做身份认证、权限获取。在服务器内部，每个client连接都有自己的线程，即TCP连接都会分配给一个线程去执行后续的流程。这些线程轮流运行在某一个CPU内核(多核CPU)或者CPU中，缓存了线程，因此不需要为每个client连接单独创建和销毁线程。

认证和权限获取

完成与访问客户端的TCP连接后，会对TCP传递过来的账号密码进行身份认证，如果账户和密码错误，会报错Access denied for user 'root'@'localhost' (using password: YES)

user表：存放用户账户信息以及全局级别（所有数据库）权限，决定了来自哪些主机的哪些用户可以访问数据库实例

db表：存放数据库级别的权限，决定了来自哪些主机的哪些用户可以访问此数据库

tables_priv表：存放表级别的权限，决定了来自哪些主机的哪些用户可以访问数据库的这个表

columns_priv表：存放列级别的权限，决定了来自哪些主机的哪些用户可以访问数据库表的这个字段

先从user表中的Host，User，Password这3个字段中判断连接的IP、用户名、密码是否存在，存在则通过验证。通过身份认证后，进行权限分配，按照user，db，tables_priv，columns_priv的顺序进行验证。即先检查全局权限表user，如果user中对应的权限为Y，则此用户对所有数据库的权限都为Y，将不再检查db，tables_priv，columns_priv；如果为N，则到db表中检查此用户对应的具体数据库，并得到db中为Y的权限；如果db中为N，则检查tables_priv中此数据库对应的具体表，取得表中的权限Y，以此类推。如果在任何一个过程中权限验证不通过，都会返回相应的错误。

2，缓存

缓存主要是针对MySQL的查询语句进行的，如果是查询语句，MySQL服务器会将查询字符串作为key，查询结果作为value缓存到内存中。经过连接器，此时MySQL服务器已经获得到了SQL字符串，如果是查询语句，服务器会使用该查询字符串作为key，去缓存中获取，如果命中缓存，直接返回结果（返回前需要做权限的验证），未命中则执行后面的逻辑。并且，在匹配的缓存的过程中，查询字符串需要完全与key匹配才算命中（即空格、注释、大小写、某些系统函数）。当所取的数据的基表发生任何数据变化后，MySQL服务器会自动使对应的缓存失效。在读写比例非常高的应用系统中，缓存对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。从MySQL 5.6的版本中已经默认关闭，5.7.20开始，不推荐使用查询缓存，并在MySQL 8.0中删除。

3，分析器

因为客户端发送过来的只是一段文本字符串，因此MySQL服务器还需要对该文本字符串进行解析，这个解析过程就是在分析器中完成的。分析器对客户端发过来的SQL语句进行分析，包括预处理与解析过程，在这个阶段会解析SQL语句的语义，并进行关键词和非关键词进行提取、解析，并创建一个内部数据结果（解析树）。具体的关键词包括不限定于以下：select/update/delete/or/in/where/group by/having/count/limit等。如果分析到语法错误，会直接给客户端抛出异常：ERROR:You have an error in your SQL syntax.。除了提取关键词外，还会对其中的表进行校验，如果不存在该表，同样也会报错：unknown column in field list.。通过了分析器，那么就说明客户端发送过来的文本字符串是符合SQL标准语义规则，之后MySQL服务器就要开始执行SQL语句了。

4，优化器

优化器不仅会生成SQL执行的计划，还会帮助优化SQL语句。如外连接转换为内连接、表达式简化、子查询转为连接、连接顺序、索引选择等一堆东西，优化的结果就是执行计划。MySQL会计算各个执行方法的最佳时间，最终确定一条执行的SQL交给最后的执行器。

5，执行器

开始执行SQL的时候，要先判断一下对这个表有没有相应的权限，如果没有，就会返回权限错误。如果权限校验通过后，会调用存储引擎的API，API会调用存储引擎(存储引擎API只是抽象接口，下面还有个存储引擎层，具体实现还是要看表选择的存储引擎)，主要有以下存储的引擎(常用的还是myisam和innodb)：

存储引擎，之前又叫表处理器，负责对具体的数据文件进行操作，对SQL的语义比如select或者update进行分析，执行具体的操作。在执行完以后会将具体的操作记录到binlog中，需要注意的一点是：select不会记录到binlog中，只有update/delete/insert才会记录到binlog中。而update会采用两阶段提交的方式，记录都redolog中。

binlog

作用：Binlog记录了对MySQL数据库进行的所有更改操作，例如INSERT、UPDATE、DELETE等，以二进制格式记录在磁盘上。

功能：主要用于数据恢复、数据复制和数据迁移。通过分析Binlog，可以重放数据库中的更改操作，从而实现数据的恢复或者将更改操作应用到其他MySQL实例上。

使用场景：常用于数据库备份、故障恢复、数据同步等场景。

redolog

作用：Redo Log记录了数据库引擎执行的每个事务的修改操作，以便在数据库发生崩溃时，可以通过重放Redo Log来恢复事务的更改。

功能：主要用于保证数据库的持久性和事务的原子性，即使在发生故障时也能确保事务的完整性。

使用场景：主要用于崩溃恢复和事务回滚。

SQL语句操作分类

认识

SQL 简介

英文：Structured Query Language，简称 SQL 结构化查询语言，一门操作关系型数据库的编程语言定义操作所有关系型数据库的统一标准对于同一个需求，每一种数据库操作的方式可能会存在一些不一样的地方，我们称为“方言”

SQL 通用语法

1，SQL 语句可以单行或多行书写，以分号结尾。 2，MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。 3，注释单行注释: -- 注释内容或 #注释内容(MySQL 特有) 多行注释: /* 注释 */

操作图

数据定义语言（DDL）

认识

DDL(Data Definition Language) 数据定义语言，用来定义数据库对象：数据库，表，列等

基础结构

create表示创建数据表。

create table 表名称( 字段名字段类型 [Default 默认值] ... )

alter表示修改数据表。

alter table <表名>

alter column <字段名> <字段类型(长度)>

drop表示删除数据表

drop table <表名>

操作收集

操作库

1，查询

SHOW DATABASES;

2，创建

CREATE DATABASE 数据库名称;

CREATE DATABASE IF NOT EXISTS 数据库名称;

3，删除

DROP DATABASE 数据库名称;

DROP DATABASE IF EXISTS 数据库名称;

4，使用数据库

查看当前使用的数据库 SELECT DATABASE();

使用数据库 USE 数据库名称;

操作表

查询表

当前数据库下所有的表名称 SHOW TABLES;

查询表结构 DESC 表名称;

创建表

CREATE TABLE 表名 ( 字段名1 数据类型1, 字段名2 数据类型2, … 字段名n 数据类型n );

最后一行末尾，不能加逗号

删除表

DROP TABLE 表名;

DROP TABLE IF EXISTS 表名;

修改表

修改表名 ALTER TABLE 表名 RENAME TO 新的表名;

添加一列 ALTER TABLE 表名 ADD 列名数据类型;

修改数据类型 ALTER TABLE 表名 MODIFY 列名新数据类型;

修改列名和数据类型 ALTER TABLE 表名 CHANGE 列名新列名新数据类型;

删除列 ALTER TABLE 表名 DROP 列名;

约束

概念&分类

概念

约束是作用于表中列上的规则，用于限制加入表的数据例如：我们可以给id列加约束，让其值不能重复，不能为null值。

约束的存在保证了数据库中数据的正确性、有效性和完整性添加约束可以在添加数据的时候就限制不正确的数据，年龄是3000，数学成绩是-5分这样无效的数据，继而保障数据的完整性。

分类

注意：mysql不支持检查约束

非空约束

非空约束用于保证列中所有数据不能有NULL值关键字是 NOT NULL

添加约束

-- 创建表时添加非空约束 CREATE TABLE 表名( 列名数据类型 NOT NULL, … );

-- 建完表后添加非空约束 ALTER TABLE 表名 MODIFY 字段名数据类型 NOT NULL;

删除约束

ALTER TABLE 表名 MODIFY 字段名数据类型;

唯一约束

唯一约束用于保证列中所有数据各不相同关键字是 UNIQUE

添加约束

-- 创建表时添加唯一约束 CREATE TABLE 表名( 列名数据类型 UNIQUE [AUTO_INCREMENT], -- AUTO_INCREMENT: 当不指定值时自动增长 … ); CREATE TABLE 表名( 列名数据类型, … [CONSTRAINT] [约束名称] UNIQUE(列名) );

-- 建完表后添加唯一约束 ALTER TABLE 表名 MODIFY 字段名数据类型 UNIQUE;

删除约束

ALTER TABLE 表名 DROP INDEX 字段名;

主键约束

主键是一行数据的唯一标识，要求非空且唯一，一张表只能有一个主键关键字是 PRIMARY KEY

添加约束

-- 创建表时添加主键约束 CREATE TABLE 表名( 列名数据类型 PRIMARY KEY [AUTO_INCREMENT], … ); CREATE TABLE 表名( 列名数据类型, [CONSTRAINT] [约束名称] PRIMARY KEY(列名) );

-- 建完表后添加主键约束 ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

删除约束

ALTER TABLE 表名 DROP PRIMARY KEY;

默认约束

保存数据时，未指定值则采用默认值关键字是 DEFAULT

添加约束

-- 创建表时添加默认约束 CREATE TABLE 表名( 列名数据类型 DEFAULT 默认值, … );

-- 建完表后添加默认约束 ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;

删除约束

ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;

检查约束

保证列中的值满足某一条件。关键字是 CHECK

外键约束

外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性关键字是 FOREIGN KEY

添加约束

-- 创建表时添加外键约束 CREATE TABLE 表名( 列名数据类型, … [CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名) );

-- 建完表后添加外键约束 ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

外键设置CASCADE、NO ACTION、RESTRICT、SET NULL

CASCADE：父表delete、update的时候，子表会delete、update掉关联记录；

SET NULL：父表delete、update的时候，子表会将关联记录的外键字段所在列设为null，所以注意在设计子表时外键不能设为not null；

RESTRICT(默认)：如果想要删除父表的记录时，而在子表中有关联该父表的记录，则不允许删除父表中的记录；

NO ACTION：同 RESTRICT，也是首先先检查外键；

CREATE TABLE `test_2` ( `id` int(10) NOT NULL AUTO_INCREMENT, `user_id` int(10) DEFAULT NULL, PRIMARY KEY (`id`), KEY `uid` (`user_id`), CONSTRAINT `uid` FOREIGN KEY (`user_id`) REFERENCES `test_1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

删除约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

举例

-- 删除表 DROP TABLE IF EXISTS emp; DROP TABLE IF EXISTS dept; -- 部门表 CREATE TABLE dept( id int primary key auto_increment, dep_name varchar(20), addr varchar(20) ); -- 员工表 CREATE TABLE emp( id int primary key auto_increment, name varchar(20), age int, dep_id int, -- 添加外键 dep_id,关联 dept 表的id主键 CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id) );

重写添加外键 alter table emp add CONSTRAINT fk_emp_dept FOREIGN key(dep_id) REFERENCES dept(id);

删除外键 alter table emp drop FOREIGN key fk_emp_dept;

举例

场景的例子

--未约束的员工表 CREATE TABLE emp ( id INT, -- 员工id，主键且自增长 ename VARCHAR(50), -- 员工姓名，非空且唯一 joindate DATE, -- 入职日期，非空 salary DOUBLE(7,2), -- 工资，非空 bonus DOUBLE(7,2) -- 奖金，如果没有将近默认为0 );

-- 添加约束的员工表 CREATE TABLE emp ( id INT PRIMARY KEY, -- 员工id，主键且自增长 ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名，非空并且唯一 joindate DATE NOT NULL , -- 入职日期，非空 salary DOUBLE(7,2) NOT NULL , -- 工资，非空 bonus DOUBLE(7,2) DEFAULT 0 -- 奖金，如果没有奖金默认为0 );

INSERT INTO emp(id,ename,joindate,salary,bonus) values(1,'张三','1999-11-11',8800,5000);

验证主键约束，非空且唯一

INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'张三','1999-11-11',8800,5000);

验证非空约束

INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,null,'1999-11-11',8800,5000);

验证唯一约束

INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,'李四','1999-11-11',8800,5000);

验证默认约束

INSERT INTO emp(id,ename,joindate,salary) values(3,'王五','1999-11-11',8800);

视图

认识

定义

视图（View）是一个虚拟表，基于SQL查询的结果集。它不存储数据，而是通过查询动态生成数据。

作用

简化复杂查询：将多表关联、过滤条件等封装成简单视图。

数据安全性：隐藏敏感字段，仅暴露必要数据。

逻辑抽象：提供统一的数据接口，减少重复代码。

命名规范

使用v_或view_前缀（如v_order_summary）

创建视图

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = user] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS SELECT ... [WITH [CASCADED | LOCAL] CHECK OPTION];

OR REPLACE：若视图已存在，则替换

ALGORITHM：指定视图处理算法（默认UNDEFINED，由MySQL自动选择）

WITH CHECK OPTION：确保通过视图插入/修改的数据满足视图的过滤条件

访问权限SQL SECURITY 选项

DEFINER（默认）：以视图创建者的权限执行

INVOKER：以调用者的权限执行

举例

CREATE VIEW sensitive_data AS SELECT name, phone FROM employees WHERE department = 'HR' WITH SQL SECURITY INVOKER; -- 调用者需有基表权限

增删改查

创建

create view tempXXX as select * from courses;

更新

使用CREATE OR REPLACE VIEW直接覆盖。

CREATE OR REPLACE VIEW tempXXX as select * from courses WHERE course_id<3;

使用ALTER VIEW（仅修改元数据，不重建数据）。

ALTER view tempXXX as select * from courses WHERE course_id<3;

删除

结构

DROP VIEW [IF EXISTS] view_name;

DROP VIEW IF EXISTS tempXXX;

查询

select * from tempXXX;

应用场景

简化复杂查询

-- 复杂查询 SELECT u.username, p.product_name, COUNT(*) AS purchase_count FROM users u JOIN orders o ON u.user_id = o.user_id JOIN order_items oi ON o.order_id = oi.order_id JOIN products p ON oi.product_id = p.product_id GROUP BY u.user_id, p.product_id; -- 封装为视图 CREATE VIEW user_purchase_stats AS SELECT ...; -- 上述查询

数据脱敏

-- 隐藏用户的密码和手机号 CREATE VIEW public_user_info AS SELECT user_id, username, CONCAT(LEFT(email, 3), '****', RIGHT(email, 4)) AS masked_email FROM users;

权限分层

-- 仅允许部门经理查看本部门员工 CREATE VIEW department_employees AS SELECT * FROM employees WHERE department_id = CURRENT_DEPARTMENT_ID(); -- 假设为自定义函数

电商系统视图设计

-- 1. 用户活跃度视图 CREATE VIEW active_users AS SELECT user_id, username, last_login, COUNT(order_id) AS order_count FROM users LEFT JOIN orders USING (user_id) WHERE last_login >= CURDATE() - INTERVAL 30 DAY GROUP BY user_id; -- 2. 商品销售排行榜 CREATE VIEW product_sales_ranking AS SELECT product_id, product_name, SUM(quantity) AS total_sold, RANK() OVER (ORDER BY SUM(quantity) DESC) AS rank FROM order_items JOIN products USING (product_id) GROUP BY product_id;

常用系统表与命令

查看视图定义

SHOW CREATE VIEW view_name;

列出所有视图

SHOW FULL TABLES WHERE TABLE_TYPE LIKE 'VIEW';

元数据查询

SELECT * FROM information_schema.VIEWS WHERE TABLE_SCHEMA = '数据库名';

性能优化

避免多层嵌套：嵌套视图可能导致查询优化器难以生成高效执行计划。

结合索引：确保视图的底层查询字段已建立索引。

限制返回列：仅选择必要的字段，减少数据传输开销。

物化视图替代方案：MySQL原生不支持物化视图，但可通过定时刷新表模拟

CREATE TABLE order_summary_materialized AS SELECT * FROM order_summary; -- 定期更新此表

数据查询语言（DQL）

认识

DQL(Data Query Language) 数据查询语言，用来查询数据库中表的记录(数据) 对表中的数据进行查询

基础结构

select表示查询结果

from表示从哪个表中查找数据

where表示查询条件

操作收集

查询语法

SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段 HAVING 分组后条件 ORDER BY 排序字段 LIMIT 分页限定

基础查询

查询多个字段 SELECT 字段列表 FROM 表名; SELECT * FROM 表名; -- 查询所有数据

去除重复记录 SELECT DISTINCT 字段列表 FROM 表名;

使用distinct要写在所有要查询字段的前面，后面有几个字段，就代表修饰几个字段，而不是紧随distinct的字段

起别名 AS: AS 也可以省略

条件查询(where)

条件查询语法 SELECT 字段列表 FROM 表名 WHERE 条件列表;

分组查询(group by)

分组查询语法

SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];

注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义

where 和 having 区别：

执行时机不一样：where 是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤。可判断的条件不一样：where 不能对聚合函数进行判断，having 可以。

执行顺序： where > 聚合函数 > having

排序查询(order by)

排序查询语法 SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1 [排序方式1],排序字段名2 [排序方式2] …;

排序方式

ASC升序排序(默认值)

DESC降序排序

如果有多个排序条件，当前面的条件值一样时，才会根据第二个条件进行排序，依次类推

分页查询(limit)

查询语法

SELECT 字段列表 FROM 表名 LIMIT 起始索引 , 查询条目数;

起始索引：从0开始

计算公式：起始索引 = (当前页码-1) * 每页显示的条数

提示

分页查询 limit 是MySQL数据库的方言 Oracle 分页查询使用 rownumber SQL Server分页查询使用 top

聚合函数

将一列数据作为一个整体，进行纵向计算

聚合分类

count(列名)-统计数量(一般选用不为null的列)

max(列名)-最大值

min(列名)-最小值

sum(列名)-求和

avg(列名)-平均值

聚合函数语法

SELECT 聚合函数名(列名) FROM 表;

注意:null值不参与所有聚合函数运算

筛选过滤条件

概述

用于筛选出符合特定条件的数据。常见的过滤条件使用在 SELECT、UPDATE、DELETE 等语句中

where

select,update,delete语句中使用

select * from customers where age > 18;

表达式

比较操作符(=、<、>、<=、>=)

逻辑操作符(AND、OR、NOT)

IN 子句

BETWEEN...AND 子句

LIKE 子句

group by 分组

用于根据一个或多个列对结果集进行分组。分组后，可以对每个分组应用聚合函数（如 SUM、COUNT、AVG、MAX、MIN 等），以计算每个分组的汇总值。

SELECT customer_id, SUM(order_amount) AS total_amount FROM orders GROUP BY customer_id;

max 最大值

SELECT MAX(price) FROM products; SELECT category, MAX(price) FROM products GROUP BY category;

min 最小值

SELECT MIN(price) FROM products; SELECT category, MIN(price) FROM products GROUP BY category;

sum 总和

SELECT SUM(price) FROM products; SELECT category, SUM(price) FROM products GROUP BY category;

count 行数

SELECT COUNT(*) FROM products; SELECT category, COUNT(*) as num_total FROM products GROUP BY category;

group_concat 合并

将查询结果集中的多个行合并成一个字符串，并以指定的分隔符分隔各个值。

SELECT student_id, GROUP_CONCAT(course_name SEPARATOR '|==|') AS courses FROM courses GROUP BY student_id;

SEPARATOR关键字，后面设置分割符，默认是","

group_concat(name,':',salary)

distinct 唯一

从查询结果中去除重复的行，只返回唯一的行

SELECT DISTINCT column1, column2, ... FROM table_name;

order by 排序

用于对查询结果集进行排序，可以按照一个或多个列的值进行升序(ASC)或降序(DESC)排序。

结构

SELECT column1, column2, ... FROM table_name ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

使用

SELECT product_name, price FROM products ORDER BY price ASC;

limit

用于限制查询结果返回的行数，可以用于控制返回的结果集的大小

结构

SELECT column1, column2, ... FROM table_name LIMIT number_of_rows;

使用

SELECT * FROM students LIMIT 10; //前10条

SELECT * FROM students LIMIT 10 OFFSET 10; //第11到第20条

正则

使用正则表达式进行模式匹配。正则表达式允许你根据特定的模式来匹配文本，并且支持在查询中使用 REGEXP 或 RLIKE 运算符。

举例

SELECT column1, column2, ... FROM table_name WHERE column_name REGEXP 'pattern';

匹配方式

元字符

匹配字符串开头

^abc（以 "abc" 开头）

匹配字符串结尾

xyz$（以 "xyz" 结尾）

匹配任意单个字符

a.c（如 "abc", "a2c"）

[...]

匹配括号内的任意字符

[aeiou]（匹配元音字母）

[^...]

匹配不在括号内的任意字符

[^0-9]（非数字字符）

或操作

apple | banana（匹配 "apple" 或 "banana"）

量词

匹配前一个元素零次或多次

a*（匹配 "", "a", "aa"）

匹配前一个元素一次或多次

a+（匹配 "a", "aa"）

匹配前一个元素零次或一次

a?（匹配 "", "a"）

{n}

匹配前一个元素恰好 n 次

a{3}（匹配 "aaa"）

{n,}

匹配前一个元素至少 n 次

a{2,}（匹配 "aa", "aaa"）

{n,m}

匹配前一个元素 n 到 m 次

a{2,4}（匹配 "aa", "aaa", "aaaa"）

转义字符

正则中的特殊字符（如 .、*、?、+、() 等）需用 \\ 转义

常见正则

匹配以大写字母开头的名称

^[A-Z]

匹配中文字符

[\\x{4e00}-\\x{9fa5}]

邮箱

^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$

查找包含连续4位数字的字符串

[0-9]{4}

匹配日期格式（YYYY-MM-DD）

^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[12][0-9]|3[01])$

多表和子查询

总的一个参考

inner join left join right join outer join

多表查询简介

取 A,B集合所有组合情况

认识：从多张表查询数据

连接查询

内连接：相当于查询A B交集数据

外连接：左外连接：相当于查询A表所有数据和交集部分数据右外连接：相当于查询B表所有数据和交集部分数据

子查询

查询中嵌套查询，称嵌套查询为子查询

子查询根据查询结果不同，作用不同

单行单列多行单列多行多列

准备环境

DROP TABLE IF EXISTS emp; DROP TABLE IF EXISTS dept; # 创建部门表 CREATE TABLE dept( did INT PRIMARY KEY AUTO_INCREMENT, dname VARCHAR(20) ); # 创建员工表 CREATE TABLE emp ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10), gender CHAR(1), -- 性别 salary DOUBLE, -- 工资 join_date DATE, -- 入职日期 dep_id INT, FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外键，关联部门表(部门表的主键) ); -- 添加部门数据 INSERT INTO dept (dNAME) VALUES ('研发部'),('市场部'),('财务部'),('销售部'); -- 添加员工数据 INSERT INTO emp(NAME,gender,salary,join_date,dep_id) VALUES ('孙悟空','男',7200,'2013-02-24',1), ('猪八戒','男',3600,'2010-12-02',2), ('唐僧','男',9000,'2008-08-08',2), ('白骨精','女',5000,'2015-10-07',3), ('蜘蛛精','女',4500,'2011-03-14',1), ('小白龙','男',2500,'2011-02-14',null);

多表查询语句 select * from emp , dept; -- 从emp和dept表中查询所有的字段数据

select * from emp , dept where emp.dep_id = dept.did;

内连接

查询语法

-- 隐式内连接 SELECT 字段列表 FROM 表1,表2… WHERE 条件; -- 显示内连接 SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;

内连接相当于查询 A B 交集数据

举例

隐式举例

SELECT t1. NAME,t1.gender,t2.dname FROM emp t1,dept t2 WHERE t1.dep_id = t2.did;

显示举例

select * from emp inner join dept on emp.dep_id = dept.did; -- 上面语句中的inner可以省略，可以书写为如下语句 select * from emp join dept on emp.dep_id = dept.did;

外连接

查询语法

-- 左外连接 SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件; -- 右外连接 SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;

左外连接：相当于查询A表所有数据和交集部分数据右外连接：相当于查询B表所有数据和交集部分数据

举例

左外连接

select * from emp left join dept on emp.dep_id = dept.did;

右外连接

select * from emp right join dept on emp.dep_id = dept.did;

子查询

查询中嵌套查询，称嵌套查询为子查询

单行单列：作为条件值，使用 = != > <等进行条件判断

SELECT 字段列表 FROM 表 WHERE 字段名 = (子查询);

多行单列：作为条件值，使用 in 等关键字进行条件判断

SELECT 字段列表 FROM 表 WHERE 字段名 in (子查询);

多行多列：作为虚拟表

SELECT 字段列表 FROM (子查询) WHERE 条件;

举例

查询 '财务部' 和 '市场部' 所有的员工信息

-- 查询 '财务部' 或者 '市场部' 所有的员工的部门did select did from dept where dname = '财务部' or dname = '市场部'; select * from emp where dep_id in (select did from dept where dname = '财务部' or dname ='市场部');

查询入职日期是 '2011-11-11' 之后的员工信息和部门信息

-- 查询入职日期是 '2011-11-11' 之后的员工信息 select * from emp where join_date > '2011-11-11' ; -- 将上面语句的结果作为虚拟表和dept表进行内连接查询 select * from (select * from emp where join_date > '2011-11-11' ) t1, dept where t1.dep_id = dept.did;

多表查询案例

分析步骤

1，分析数据分别来自于哪些表

2，分析这些表直接的关联关系

3，分析使用什么样的查询方式

案例

环境准备

DROP TABLE IF EXISTS emp; DROP TABLE IF EXISTS dept; DROP TABLE IF EXISTS job; DROP TABLE IF EXISTS salarygrade; -- 部门表 CREATE TABLE dept ( id INT PRIMARY KEY PRIMARY KEY, -- 部门id dname VARCHAR(50), -- 部门名称 loc VARCHAR(50) -- 部门所在地 ); -- 职务表，职务名称，职务描述 CREATE TABLE job ( id INT PRIMARY KEY, jname VARCHAR(20), description VARCHAR(50) ); -- 员工表 CREATE TABLE emp ( id INT PRIMARY KEY, -- 员工id ename VARCHAR(50), -- 员工姓名 job_id INT, -- 职务id mgr INT , -- 上级领导 joindate DATE, -- 入职日期 salary DECIMAL(7,2), -- 工资 bonus DECIMAL(7,2), -- 奖金 dept_id INT, -- 所在部门编号 CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id), CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id) ); -- 工资等级表 CREATE TABLE salarygrade ( grade INT PRIMARY KEY, -- 级别 losalary INT, -- 最低工资 hisalary INT -- 最高工资 ); -- 添加4个部门 INSERT INTO dept(id,dname,loc) VALUES (10,'教研部','北京'), (20,'学工部','上海'), (30,'销售部','广州'), (40,'财务部','深圳'); -- 添加4个职务 INSERT INTO job (id, jname, description) VALUES (1, '董事长', '管理整个公司，接单'), (2, '经理', '管理部门员工'), (3, '销售员', '向客人推销产品'), (4, '文员', '使用办公软件'); -- 添加员工 INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES (1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20), (1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30), (1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30), (1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20), (1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30), (1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30), (1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10), (1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20), (1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10), (1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30), (1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20), (1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30), (1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20), (1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10); -- 添加5个工资等级 INSERT INTO salarygrade(grade,losalary,hisalary) VALUES (1,7000,12000), (2,12010,14000), (3,14010,20000), (4,20010,30000), (5,30010,99990);

查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述

/* 分析： 1. 员工编号，员工姓名，工资信息在emp 员工表中 2. 职务名称，职务描述信息在 job 职务表中 3. job 职务表和 emp 员工表是一对多的关系 emp.job_id = job.id */ -- 隐式内连接 SELECT emp.id, emp.ename, emp.salary, job.jname, job.description FROM emp, job WHERE emp.job_id = job.id; SELECT * FROM emp; SELECT * FROM job; -- 显式内连接 SELECT emp.id, emp.ename, emp.salary, job.jname, job.description FROM emp INNER JOIN job ON emp.job_id = job.id;

查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

/* 分析： 1. 员工编号，员工姓名，工资信息在emp 员工表中 2. 职务名称，职务描述信息在 job 职务表中 3. job 职务表和 emp 员工表是一对多的关系 emp.job_id = job.id 4. 部门名称，部门位置来自于部门表 dept 5. dept 和 emp 一对多关系 dept.id = emp.dept_id */ -- 隐式内连接 SELECT emp.id, emp.ename, emp.salary, job.jname, job.description, dept.dname, dept.loc FROM emp, job, dept WHERE emp.job_id = job.id and dept.id = emp.dept_id ; -- 显式内连接 SELECT emp.id, emp.ename, emp.salary, job.jname, job.description, dept.dname, dept.loc FROM emp INNER JOIN job ON emp.job_id = job.id INNER JOIN dept ON dept.id = emp.dept_id ;

查询员工姓名，工资，工资等级

/* 分析： 1. 员工姓名，工资信息在emp 员工表中 2. 工资等级信息在 salarygrade 工资等级表中 3. emp.salary >= salarygrade.losalary and emp.salary <= salarygrade.hisalary */ SELECT emp.ename, emp.salary, t2.* FROM emp, salarygrade t2 WHERE emp.salary >= t2.losalary AND emp.salary <= t2.hisalary

查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

/* 分析： 1. 员工编号，员工姓名，工资信息在emp 员工表中 2. 职务名称，职务描述信息在 job 职务表中 3. job 职务表和 emp 员工表是一对多的关系 emp.job_id = job.id 4. 部门名称，部门位置来自于部门表 dept 5. dept 和 emp 一对多关系 dept.id = emp.dept_id 6. 工资等级信息在 salarygrade 工资等级表中 7. emp.salary >= salarygrade.losalary and emp.salary <= salarygrade.hisalary */ SELECT emp.id, emp.ename, emp.salary, job.jname, job.description, dept.dname, dept.loc, t2.grade FROM emp INNER JOIN job ON emp.job_id = job.id INNER JOIN dept ON dept.id = emp.dept_id INNER JOIN salarygrade t2 ON emp.salary BETWEEN t2.losalary and t2.hisalary;

查询出部门编号、部门名称、部门位置、部门人数

/* 分析： 1. 部门编号、部门名称、部门位置来自于部门 dept 表 2. 部门人数: 在emp表中按照dept_id 进行分组，然后count(*)统计数量 3. 使用子查询，让部门表和分组后的表进行内连接 */ select * from dept; select dept_id, count(*) from emp group by dept_id; SELECT dept.id, dept.dname, dept.loc, t1.count FROM dept, ( SELECT dept_id, count(*) count FROM emp GROUP BY dept_id ) t1 WHERE dept.id = t1.dept_id

数据操作语言（DML）

认识

DML(Data Manipulation Language) 这类语句主要用于数据的增加（往表中新增数据）、删除（删除表中的数据）、修改（修改表中的数据）。

基础结构

insert表示向表中插入数据

insert into 表名(字段1,字段...) values(数值1,数组2...)

update表示对表中数据进行修改

delete表示删除表中数据

操作收集

添加

给指定列添加数据 INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…);

给全部列添加数据 INSERT INTO 表名 VALUES(值1,值2,…);

批量添加数据 INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…; INSERT INTO 表名 VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;

修改

修改表数据 UPDATE 表名 SET 列名1=值1,列名2=值2,… [WHERE 条件] ;

修改语句如果不加条件，则将所有数据都修改

删除

删除数据 DELETE FROM 表名 [WHERE 条件] ;

删除语句中如果不加条件，则将所有数据都删除!

数据库权限（DCL）

认识

DCL(Data Control Language) 数据控制语言，用来定义数据库的访问权限和安全级别，及创建用户

操作收集

授权-用户标识

用户+IP

权限表

User的一行记录代表一个用户标识

db的一行记录代表对数据库的权限

table_priv的一行记录代表对表的权限

column_priv的一行记录代表对某一列的权限

复杂业务思路求解

子查询

子查询就是在from子句中直接写SQL查询语句，也就是将多个SQL查询语句嵌套在一起。这个嵌套的SQL查询语句就是子查询。

某一课成绩第二

select max(distinct 成绩） from 成绩表 where 课程='语文' and 成绩 <（select max(distinct 成绩） from成绩表 where 课程='语文'）

多表查询万能模板

场景举例

窗口函数

认识

窗口函数也叫作OLAP（Online Analytical Processing，联机分析处理）函数，可以对数据库中的数据进行复杂分析。

通用语法

<窗口函数> over（partition by<用于分组的列名>order by<用于排序的列名>)

窗口函数

专用的窗口函数

rank()

dense_rank()

row_number()

汇总函数

sum()

avg()

count()

max()

min()

partition by

按某列分组

order by

对分组后的结果按某列排序

通常是对where或group by子句处理后的结果进行操作，窗口函数原则上只能写在select字句中

经典问题

排名问题

万能公式

select *，row_number（）over (partition by<要分组的列名> order by<要排序的列名> ）as 排名 from <表名>;

去除最大值，最小值后取平均值

select a.部门编号,avg(a.薪水）as 平均薪水 from (select *, rank(）over(partition by 部门编号 order by 薪水 desc）as rank_1, rank(）over(partition by 部门编号 orderby 薪水）as rank_2 from薪水表 ) as a where a.rank_1>1 and a.rank_2>1 group by a.部门编号；

Top N问题

万能公式

select * from ( select *,row_number() over(partition by <要分组的列名> order by <要排序的列名> desc) as 排名 from <表名> ) as a

排在前两名的工资

百分之N问题

累计问题

每组内比较问题

连续问题

高级用法

收集

letcode的sql进阶 https://www.processon.com/view/6446957d84b4b71c14020001 https://blog.csdn.net/weixin_39381833/article/details/136413190

https://blog.csdn.net/u010565545/article/details/100785261

https://blog.csdn.net/zhou_zzzzhou/article/details/135122962

https://blog.csdn.net/weixin_52010459/article/details/135439112

case表达式

写法

简单 CASE 表达式

CASE sex WHEN '1' THEN '男' WHEN '2' THEN '女' ELSE '其他' END

搜索 CASE 表达式

CASE WHEN sex = '1' THEN '男' WHEN sex = '2' THEN '女' ELSE '其他' END

注意点

统一各分支返回的数据类型

不要忘了写END

最好有else的子句习惯

一旦WHEN有满足后面语句就不再执行

CASE WHEN col_1 IN ('a', 'b') THEN '第一' WHEN col_1 IN ('a') THEN '第二' ELSE '其他' END

与聚合函数(SUM或AVG)和GROUP BY子句一起使用

将已有编号方式转换为新的方式并统计

-- 把县名转换成地区名(1) SELECT CASE pref_name WHEN '德岛' THEN '四国' WHEN '香川' THEN '四国' WHEN '爱媛' THEN '四国' WHEN '高知' THEN '四国' WHEN '福冈' THEN '九州' WHEN '佐贺' THEN '九州' WHEN '长崎' THEN '九州' ELSE '其他' END AS district, SUM(population) FROM PopTbl GROUP BY CASE pref_name WHEN '德岛' THEN '四国' WHEN '香川' THEN '四国' WHEN '爱媛' THEN '四国' WHEN '高知' THEN '四国' WHEN '福冈' THEN '九州' WHEN '佐贺' THEN '九州' WHEN '长崎' THEN '九州' ELSE '其他' END;

参考二

-- 按人口数量等级划分都道府县 SELECT CASE WHEN population < 100 THEN '01' WHEN population >= 100 AND population < 200 THEN '02' WHEN population >= 200 AND population < 300 THEN '03' WHEN population >= 300 THEN '04' ELSE NULL END AS pop_class, COUNT(*) AS cnt FROM PopTbl GROUP BY CASE WHEN population < 100 THEN '01' WHEN population >= 100 AND population < 200 THEN '02' WHEN population >= 200 AND population < 300 THEN '03' WHEN population >= 300 THEN '04' ELSE NULL END;

多条件的统计

SELECT pref_name, -- 男性人口 SUM( CASE WHEN sex = '1' THEN population ELSE 0 END) AS cnt_m, -- 女性人口 SUM( CASE WHEN sex = '2' THEN population ELSE 0 END) AS cnt_f FROM PopTbl2 GROUP BY pref_name;

窗口函数

自连接

三值逻辑和NULL

EXISTS谓词的用法

HAVING子句的力量

用窗口函数进行行间比较

外连接的用法

用SQL进行集合运算

用SQL处理数列

让SQL飞起来

SQL编程方法

消除所有null(方案收集)

编号:使用异常编号

如性别：1男性，2女性，0未知，9不适用

名字：使用“无名氏”

如"未知","UNKNOWN"

数值：用 0 代替

日期：用最大值(9999-12-31)或最小值(0000-01-01)代替

关键字

is null

is not null

where条件中的字段限定可null或不可null

distinct

查询的列进行去重

select distinct 列名1,列名2,... form 表名

多列处理？

聚合函数

函数

count(列名)

求某列的行数

sum(列名)

求某列数据求和(只数值类型)

avg(列名)

求某列数据平均值(只数值类型)

max(列名)

求某列的最大值

min(列名)

求某列的最小值

举例

排重后聚合 select sum(distinct 用户ID) as 用户总数,avg(用户年龄) from 用户登录信息表

聚合和去重多列

group by 列名,...

有多个列的话列都相同才聚合在一起

多列的处理

SELECT id as '编号', num as "订单数", count(num) as "个数" from test_order GROUP BY num desc,id desc

多列值同时相同为一组

排序是依次从左排序

select的字段必须是分组中的字段，否则报错找到字段

having

和group一起使用，对分组后的结果进行条件处理

select 列名 from 表名 group by 列名 having count(列名) >1

子查询

select count（distinct 顾客ID） from销售订单表 where 顾客ID in（select distinct 顾客ID from 销售订单表 where 产品='ProductA'） and 顾客ID in（select distinct 顾客ID from 销售订单表 where 产品='ProductB'） and 顾客ID not in（select distinct 顾客ID from 销售订单表 where 产品='ProductC'）

any

只需满足ang(子查询)里的任意一个条件就可以需要和比较运算符，包括“大于（>）”“小于（<）”“不等于（<>）”等

select * from 表格B where 数字=any（select 数字 from 表格A）

all

表示要满足all(子查询)里所有条件需要和比较运算符，包括“大于（>）”“小于（<）”“不等于（<>）”等

select * from 表格B where 数字<>all（select 数字 from 表格A）

with..as

语法规则

with 临时表名称1 as 子查询语句1，临时表名称2 as 子查询语句2， ...

with a as（select distinct 顾客ID from 销售订单表 where 产品='ProductA’）， b as（select distinct 顾客ID from 销售订单表 where 产品='ProductB’）， c as（select distinct 顾客ID from 销售订单表 where 产品='ProductC’） select count（distinct顾客ID) from 销售订单表 where 顾客ID in（select*from a) and 顾客ID in（select*from b) and 顾客ID not in（select *from c);

create view

认识

创建视图

定义的语法

create view 视图名称 as sql查询语句

举例

创建视图

create view myview as select * from 真实源表 where a=.....

使用视图

select .... form 视图名称

case

语法格式

case when<条件判断表达式>then<结果表达式> when<条件判断表达式〉then<结果表达式> ... else<结果表达式> end

使用场景

多条件判断

select 学号,课程号,成绩， (case when 成绩>=60 then '及格' when 成绩<60 then '不及格' else null end）as 是否及格 from 学生成绩表；

按区间统计

select 学生编号,分数, (case when 分数<60 then '不及格' when 分数>=60 and 分数<=70 then '及格' when 分数>=71 and 分数<=85 then '良好' else '优秀' end）as 等级 from学生分数表；

分组

select 等级,count (*) as 学生人数 from (此处为上面的区间sql) as a group by 等级

行列互换

select A, --第2步，在行列互换结果表中，其他列里的值分别使用case和max来获取 max(case B when 'm' then C else 0 end）as 'm', max(case B when 'n' then Celse 0 end )as 'n' from cook --第1步，在行列互换结果表中按第1列分组 group by A;

mod

求余函数

where mod(序号,2)=1

序号%2=1

UNION

认识

合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行

结果集中的列名总是等于第一个 SELECT 语句中的列名

使用条件

内部的 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同

UNION ALL

和UNION用法一致，不消除重复行

数学函数

ABS(x)返回x的绝对值

BIN(x)返回x的二进制（OCT返回八进制，HEX返回十六进制）

CEILING(x)返回大于x的最小整数值

EXP(x)返回值e（自然对数的底）的x次方

FLOOR(x)返回小于x的最大整数值

GREATEST(x1,x2,...,xn)返回集合中最大的值

LEAST(x1,x2,...,xn)返回集合中最小的值

LN(x)返回x的自然对数

LOG(x,y)返回x的以y为底的对数

MOD(x,y)返回x/y的模（余数）

PI()返回pi的值（圆周率）

RAND()返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。

ROUND(x,y)返回参数x的四舍五入的有y位小数的值

SIGN(x)返回代表数字x的符号的值

SQRT(x)返回一个数的平方根

TRUNCATE(x,y)返回数字x截短为y位小数的结果

聚合函数(常用于GROUP BY从句的SELECT查询中)

AVG(col)返回指定列的平均值

COUNT(col)返回指定列中非NULL值的个数

MIN(col)返回指定列的最小值

MAX(col)返回指定列的最大值

SUM(col)返回指定列的所有值之和

GROUP_CONCAT(col)返回由属于一组的列值连接组合而成的结果

字符串函数

ASCII(char)返回字符的ASCII码值

BIT_LENGTH(str)返回字符串的比特长度

CONCAT(s1,s2...,sn)将s1,s2...,sn连接成字符串

CONCAT_WS(sep,s1,s2...,sn)将s1,s2...,sn连接成字符串，并用sep字符间隔

INSERT(str,x,y,instr)将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果

FIND_IN_SET(str,list)分析逗号分隔的list列表，如果发现str，返回str在list中的位置

LCASE(str)或LOWER(str)返回将字符串str中所有字符改变为小写后的结果

LEFT(str,x)返回字符串str中最左边的x个字符

LENGTH(s)返回字符串str中的字符数

LTRIM(str)从字符串str中切掉开头的空格

POSITION(substr,str)返回子串substr在字符串str中第一次出现的位置

QUOTE(str)用反斜杠转义str中的单引号

REPEAT(str,srchstr,rplcstr)返回字符串str重复x次的结果

REVERSE(str)返回颠倒字符串str的结果

RIGHT(str,x)返回字符串str中最右边的x个字符

RTRIM(str)返回字符串str尾部的空格

STRCMP(s1,s2)比较字符串s1和s2

TRIM(str)去除字符串首部和尾部的所有空格

UCASE(str)或UPPER(str)返回将字符串str中所有字符转变为大写后的结果

日期和时间函数

https://blog.csdn.net/daxiong0816/article/details/125263849

基本数据类型

整数类型

tinyint

范围

有符号（Signed）：-128 到 127

无符号（Unsigned）：0 到 255

存储

1字节

举例

（性别：0-未知，1-男，2-女）

smalint

范围

Signed：-32,768 到 32,767

Unsigned：0 到 65,535

存储

2字节

举例

年龄范围

mediumint

范围

存储

3字节

举例

中等规模计数器

int

integer

bigint

浮点与精确小数

float

double

decimal(numeric)

日期与时间类型

date

time

datetime

timestamp

year

字符串类型

定长与变长字符串

char

varchar

binary

varbinary

文本与二进制大对象

tinytext/tinyblob

text/blob

mediumtext/mediumblob

longtext/longblob

枚举与集合

enum

set

JSON类型

json

空间数据类型（GIS）

geometry

geometrycollection

bit

linestring

multilinestring

multipolygon

point

polygon

real

mysql中的数据类型

数据类型

Int

TinyInt

1字节

SmallInt

2字节

MediumInt

3字节

Int

4字节

BigInt

8字节

Char，Varchar

binary,varbinary

tinyblob,blob,mediumblob,longblob

tinytext,text,mediumtext,longtext

日期

Datetime

8字节

与时区无关

Date

3字节

Timestamp

4字节

与时区有关

year

1字节

time

3字节

JSON类型

json_extract抽取

json_object-将对象转json

json_merge

数据库设计

数据库设计简介

软件的研发步骤

数据库设计概念

数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型。建立数据库中的表结构以及表与表之间的关联关系的过程。有哪些表？表里有哪些字段？表和表之间有什么关系？

数据库设计的步骤

1，需求分析（数据是什么? 数据具有哪些属性? 数据与属性的特点是什么）

2，逻辑分析（通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统）

3，物理设计（根据数据库自身的特点把逻辑设计转换为物理设计）

4，维护设计（1.对新的需求进行建表；2.表优化）

表关系之一对多

一个部门对应多个员工，一个员工对应一个部门如:部门和员工

实现方式：在多的一方建立外键，指向一的一方的主键

举例

-- 删除表 DROP TABLE IF EXISTS tb_emp; DROP TABLE IF EXISTS tb_dept; -- 部门表 CREATE TABLE tb_dept( id int primary key auto_increment, dep_name varchar(20), addr varchar(20) ); -- 员工表 CREATE TABLE tb_emp( id int primary key auto_increment, name varchar(20), age int, dep_id int, -- 添加外键 dep_id,关联 dept 表的id主键 CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES tb_dept(id) );

表关系之多对多

一个商品对应多个订单，一个订单包含多个商品如:商品和订单

实现方式：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

举例

-- 删除表 DROP TABLE IF EXISTS tb_order_goods; DROP TABLE IF EXISTS tb_order; DROP TABLE IF EXISTS tb_goods; -- 订单表 CREATE TABLE tb_order( id int primary key auto_increment, payment double(10,2), payment_type TINYINT, status TINYINT ); -- 商品表 CREATE TABLE tb_goods( id int primary key auto_increment, title varchar(100), price double(10,2) ); -- 订单商品中间表 CREATE TABLE tb_order_goods( id int primary key auto_increment, order_id int, goods_id int, count int );

-- 建完表后，添加外键 alter table tb_order_goods add CONSTRAINT fk_order_id FOREIGN key(order_id) REFERENCES tb_order(id); alter table tb_order_goods add CONSTRAINT fk_goods_id FOREIGN key(goods_id) REFERENCES tb_goods(id);

表关系之一对一

一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能如：用户和用户详情

实现方式：在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE)

拆分之前的表

拆分后的关系

举例

create table tb_user_desc ( id int primary key auto_increment, city varchar(20), edu varchar(10), income int, status char(2), des varchar(100) ); create table tb_user ( id int primary key auto_increment, photo varchar(100), nickname varchar(50),age int, gender char(1), desc_id int unique, -- 添加外键 CONSTRAINT fk_user_desc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id) );

数据库设计案例

过时标准业务设计(只参考思路)

逻辑设计

范式设计

完全符合范式化的设计有时并不能得到良好得SQL查询性能

三大范式

No.1

表中的所有字段都只具有单一属性

No.2

要求表中只具有一个业务主键

不能存在非主键列只对部分主键的依赖关系

NO.3

每一个非主属性既不部分依赖于也不传递依赖于业务主键

问题

对于查询需要对多个表进行关联

更难进行索引优化

优点

可以尽量得减少数据冗余

范式化的更新操作比反范式化更快

范式化的表通常比反范式化的表更小

反范式设计

反范式化就是使用空间来换取时间

允许存在少量得冗余

优点

可以减少表的关联

可以更好的进行索引优化

缺点

存在数据冗余及数据维护异常

对数据的修改需要更多的成本

物理设计

命名规范

遵循可读性原则

遵守表意性原则

遵守长名原则

存储引擎选择

数据类型选择

优先考虑数字类型

其次是日期、时间类型

最后是字符类型

对于相同级别的数据类型应该优先选择占用空间小的数据类型

锁和事务

锁

锁是计算机协调多个进程或线程并发访问某一资源的机制

mysql锁机制-最显著的特点是不同的存储引擎支持不同的锁机制

表级锁

更适合于以查询为主，只有少量按索引条件更新数据的应用，如OLAP系统

行级锁

适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用

如一些在线事务处理（OLTP）系统

MySQL的表级锁有两种模式

表共享读锁（Table Read Lock）

lock table 表名 read

表独占写锁（Table Write Lock）

lock table 表名 write

总结

读操作，不会阻塞其他用户对同一表的读请求

读操作，不会阻塞当前session对表读，当对表进行修改会报错

lock read: 可以查询锁定表中的记录，但更新或访问其他表都会提示错误

写操作，则会阻塞其他用户对同一表的读和写操作

写操作，当前session可以对本表做CRUD,但对其他表进行操作会报错

InnoDB引擎支持行锁

共享锁又称：读锁

select * from 表 where 条件 lock in share mode

排它锁又称：写锁

select * from 表 where 条件 for update

注意

1.两个事务不能锁同一个索引。

2.insert ，delete ， update在事务中都会自动默认加上排它锁。

3.行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。

事务

事务简介

数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败事务是一个不可分割的工作逻辑单元

事务操作

语法

-- 开启事务 START TRANSACTION; 或者 BEGIN; -- 提交事务 COMMIT; -- 回滚事务 ROLLBACK;

举例

环境准备

DROP TABLE IF EXISTS account; -- 创建账户表 CREATE TABLE account( id int PRIMARY KEY auto_increment, name varchar(10), money double(10,2) ); -- 添加数据 INSERT INTO account(name,money) values('张三',1000),('李四',1000);

不加事务执行

-- 转账操作 -- 1. 查询李四账户金额是否大于500 -- 2. 李四账户 -500 UPDATE account set money = money - 500 where name = '李四'; 出现异常了... -- 此处不是注释，在整体执行时会出问题，后面的sql则不执行 -- 3. 张三账户 +500 UPDATE account set money = money + 500 where name = '张三';

发现李四账户少了500

添加事务

-- 开启事务 BEGIN; -- 转账操作 -- 1. 查询李四账户金额是否大于500 -- 2. 李四账户 -500 UPDATE account set money = money - 500 where name = '李四'; 出现异常了... -- 此处不是注释，在整体执行时会出问题，后面的sql则不执行 -- 3. 张三账户 +500 UPDATE account set money = money + 500 where name = '张三'; -- 提交事务 COMMIT; -- 回滚事务 ROLLBACK;

事务四大特征

原子性（Atomicity）: 事务是不可分割的最小操作单位，要么同时成功，要么同时失败

一致性（Consistency） :事务完成时，必须使所有的数据都保持一致状态

隔离性（Isolation） :多个事务之间，操作的可见性

持久性（Durability） :事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

mysql事务默认自动提交

-- 查看事务的默认提交方式 SELECT @@autocommit; -- 1 自动提交 0 手动提交 --修改事务提交方式 set @@autocommit = 0;

事务

协调一个线程并发访问多个资源的机制

cmd

show engines;

show variables like '%storage_engine%';

Create table .... type=InnoDB； Alter table table_name type=InnoDB;

4个属性：原子性、一致性、隔离性、持久性

隔离级别：

未提交读（READ UNCOMMITED）脏读

set SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

已提交读（READ COMMITED）不可重复读

可重复读（REPEATABLE READ）

幻读

不可重复读侧重于修改，幻读侧重于新增或删除

解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

默认的事务隔离级别

可串行化（SERIALIZABLE）

总结

事务隔离级别为可重复读时，如果有索引（包括主键索引）的时候，以索引列为条件更新数据，会存在间隙锁间、行锁、页锁的问题，从而锁住一些行；如果没有索引，更新数据时会锁住整张表

语法

开启

1、begin

2、START TRANSACTION（推荐）

3、begin work

提交

commit

回滚

rollback

还原点

savepoint

sql执行顺序

语法顺序

select[distinct] from join（如left join） on where group by having + 聚合函数（数据筛选） union order by limit

执行顺序

(select distinct sum(a.OrderPrice) As order1,sum(d.OrderPrice) As order2 -- 7 distinct(8) from orders a -- 1，通过form找到表 left join (select c.* from Orders c) d -- 3 on a.O_Id = d.O_Id -- 2，on where a.Customer='Bush' or a.Customer = 'Adams' or a.Customer = 'Carter' -- 4 group by a.Customer -- 5 having sum(a.OrderPrice) > 1500 ) -- 6 union -- 9 (select distinct sum(a.OrderPrice) As order1,sum(e.OrderPrice) As order2 -- 7 distinct(8) from orders a -- 1，通过form找到表 left join (select c.* from Orders c) e -- 3 on a.O_Id = e.O_Id -- 2 where a.Customer='Bush' or a.Customer = 'Adams' or a.Customer = 'Carter' -- 4 group by a.Customer -- 5，聚合 having sum(a.OrderPrice) > 2000 ) -- 6 order by order1 -- 10 limit 1; -- 11

from on join where group by having + 聚合函数 (数据筛选) select distinct union order by limit

子查询执行顺序

概述

从内往外。先执行括号里面的语句

内部子查询：MySQL通常会首先执行子查询，生成临时结果集，然后将其用于外部查询。内部子查询是指嵌套在主查询中的子查询，通常作为主查询的一部分。

关联子查询：如果子查询是一个关联子查询（即与外部查询相关联），MySQL可能会根据优化器的策略选择在何处执行子查询。有时候，MySQL可能会将关联子查询转换为连接查询来提高性能。

外部查询：一旦子查询的结果可用，MySQL会将其与外部查询组合起来执行，产生最终的结果集。

https://mp.weixin.qq.com/s?__biz=MjM5NzEyMzg4MA==&mid=2649468111&idx=6&sn=a87a37d675039f92dfd1df76a65c8a5f&chksm=bec1ce8889b6479ef314f7ea1aa4204eb9b1d4037847bf3ef8bdffd7a5f2f96ad34095bc4996&scene=27