MySQL
开源的MySQL
库表结构：增删改查
数据类型：
1.结构化：表格数据，字段，固定格式
2.非结构化：图片，视频文件
3.半结构化：HTML，XML等，JSON格式的数据--->其实就是一种数据的格式
用户--->DBMS--->数据库，使用DBMS显示数据结构化的形式，可控制权限，DBMS管理工具
java应用程序---调用MySQL内的数据---显示到web页面上
常见数据库产品
关系型：MySQL，postgresql(一些软件常见，只支持postgresql)，ORACLE(商业化的关系型数据库)，SQL server(微软商业版数据库)
非关系型：Mongo DB文档数据库
缓存数据库：redis，键值key-value
搜索引擎数据库：elasticsearch
常见数据库属性
行row：表中的每一行，称为记录record，一条记录里面，(姓名：XXX，年龄25，性别男，学号123)
列column：表中的每一列，称为字段，属性(数据的属性：比如姓名，年龄，性别，学号等)，域也可以，awk就是输出域信息(列信息)
主键primary key
一般是一个或者是多个字段的集合，主键内的内容必须是唯一的，不能有重复字样出现(不能出现两个张三+年龄等)，一般为多张表的一致的属性，多张表的主要关系
类似学生基本信息+家长+学习成绩，唯一的关系字段就是学生姓名/年龄等，主键字段不能为空NULL
作用在多列上：复合主键，ID/姓名等可以作为主键
唯一键(unique key)
作用在某一个字段，字段内的数值只能唯一，不能重复，比如年龄age字段，age必须唯一，age就是unique key，唯一键可以有多个
域domain：数据的取值范围，数据的合法性，比如age年龄为0-100岁，sex只能为男/女等
外键(foreign key)：1对N的关系
一个表的一个字段依赖于另一个表的字段，称为外键，可以设置这个为外键，两个表之间存在依赖关系，B表必须有主键或者是唯一键(UK)，一对多的关系
N对N的关系：主键+外键关系
优点：表与表之间的逻辑关系比较清晰
缺点：查询比较麻烦，需要组合查询才能查到
MySQL的范式
sql表的规范
第一范式1NF：同一列中不能有多个值，即不能出现tom,jerry这样的字符，同时同一个属性不能有多个字段，比如
editor book
user01 k8s
user01 linux
user02 python
不能是这样，k8s和linux同属于这个范式
editor book1 book2
user01 k8s linux
user02 python
第二范式：属性完全依赖于主键，主键需要独立
复合主键：必须依赖于多个列，多个字段
yum安装MySQL--->前提：一定要连公网，读取yum源
1.安装Cent OS 8版本mysql 8.0
1.机器上已经自带mysql8.0，新的Yum源安装mysql的时候会检查yum源的正确性，关闭gpgcheck
sed -i 's/gpgcheck=1/gpgcheck=0/g' base.repo
2.安装mysql-server，默认8.0
yum -y install mysql-server
systemctl enable --now mysqld
##MySQL初始化，可能需要输入temporary pass
mysql_insure_installation
cat /var/log/mysql/mysqld.log | grep temporary
3.进入到mysql，创建一个远程授权用户
##创建一个授权用户
use mysql
mysql> CREATE USER 'root'@'%' IDENTIFIED BY '123';
Query OK, 0 rows affected (0.04 sec)
mysql> grant all privileges on *.* to 'root'@'%';
Query OK, 0 rows affected (0.03 sec)
flush privileges;
4.navicat连接验证
2.Cent OS 7安装mysql 8.0
1.去到MySQL对应的yum仓库地址，下载你对应发行版版本的仓库
2.下好后安装mysql源，自动在机器上安装yum源
ls /etc/yum.repos.d/ | grep mysql
mysql-community.repo
mysql-community-source.repo
##原有的mysql 5.7之类的可以卸载，也可以disabled
##安装MySQL社区版的rpm包
yum -y install mysql80-community-release-el7-6.noarch.rpm
##查看现有mysql仓库
yum repolist all | grep mysql
mysql-cluster-7.5-community/x86_64 MySQL Cluster 7.5 C disabled
mysql-cluster-7.5-community-source MySQL Cluster 7.5 C disabled
mysql-cluster-7.6-community/x86_64 MySQL Cluster 7.6 C disabled
mysql-cluster-7.6-community-source MySQL Cluster 7.6 C disabled
mysql-cluster-8.0-community/x86_64 MySQL Cluster 8.0 C disabled
mysql-cluster-8.0-community-debuginfo/x86_64 MySQL Cluster 8.0 C disabled
mysql-cluster-8.0-community-source MySQL Cluster 8.0 C disabled
mysql-connectors-community/x86_64 MySQL Connectors Co enabled: 199
mysql-connectors-community-debuginfo/x86_64 MySQL Connectors Co disabled
mysql-connectors-community-source MySQL Connectors Co disabled
mysql-tools-community/x86_64 MySQL Tools Communi enabled: 92
mysql-tools-community-debuginfo/x86_64 MySQL Tools Communi disabled
mysql-tools-community-source MySQL Tools Communi disabled
mysql-tools-preview/x86_64 MySQL Tools Preview disabled
mysql-tools-preview-source MySQL Tools Preview disabled
mysql57-community/x86_64 MySQL 5.7 Community disabled
mysql57-community-source MySQL 5.7 Community disabled
mysql80-community/x86_64 MySQL 8.0 Community enabled: 364 ##目前只有mysql80-community是up的
mysql80-community-debuginfo/x86_64 MySQL 8.0 Community disabled
mysql80-community-source MySQL 8.0 Community disabled
3.因为MySQL安装的时候，会检查mysql.repo源的gpgcheck，所以把gpgcheck关了
ls /etc/yum.repos.d/ | grep mysql
mysql-community-debuginfo.repo
mysql-community.repo
mysql-community-source.repo
##sed -i替换掉文件内部的内容
##sed xxx：只是在bash上显示
cd /etc/yum.repos.d/
sed -i 's/gpgcheck=1/gpgcheck=0/g' mysql-community.repo \
sed -i 's/gpgcheck=1/gpgcheck=0/g' mysql-community-source.repo \
sed -i 's/gpgcheck=1/gpgcheck=0/g' mysql-community-debuginfo.repo
sed -i 's/gpgcheck=1/gpgcheck=0/g' base.repo
##安装mysql8.0
yum -y install mysql-server
4.初始化mysql
systemctl enable --now mysqld
##初始化，8.0往后一般是不用执行了
mysql_insure_installation
##查看mysqld.log内的临时pass
cat /var/log/mysqld.log | grep temporary
2022-08-07T03:37:10.803391Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: W6S2wp;pXiyf
5.因为Cent OS 7安装mysql 8.0的话，会有密码策略的显示，是mysql内的一个变量，卸载validate_password密码验证组件
##进入到mysql cli界面
mysql> UNINSTALL COMPONENT 'file://component_validate_password';
##创建远程用户
use mysql
mysql> CREATE USER 'root'@'%' IDENTIFIED BY '123';
Query OK, 0 rows affected (0.04 sec)
mysql> grant all privileges on *.* to 'root'@'%';
Query OK, 0 rows affected (0.03 sec)
flush privileges;
##登录
mysql -uroot -p123
6.Navicat验证
3.ubuntu 20.04安装mysql 8.0
查看apt list | grep mysql-server
安装
apt install mysql-server -y
systemctl status mysql
systemctl status mysql
4.rpm二进制包安装MySQL(Linux generic通用版)--->已编译
通用安装脚本：需要先在官网下载好MySQL 二进制包(最新)
需要在测试机上测试一下
#!/bin/bash
#
#********************************************************************
#Author: wangxiaochun
#QQ: 29308620
#Date: 2020-02-12
#FileName： install_offline_mysql5.7or8.0_for_centos.sh
#URL: http://www.wangxiaochun.com
#Description： The test script
#Copyright (C): 2020 All rights reserved
#********************************************************************
#MySQL Download URL: https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz
#http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
#http://mirrors.163.com/mysql/Downloads/MySQL-8.0/mysql-8.0.23-linux-glibc2.12-x86_64.tar.xz
. /etc/init.d/functions
SRC_DIR=`/data/mysql`
##需要先在官网下载好MySQL 二进制包(最新)
MYSQL='mysql-8.0.30-linux-glibc2.12-x86_64.tar.xz'
#MYSQL='mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz'
#MYSQL='mysql-8.0.23-linux-glibc2.12-x86_64.tar.xz'
MYSQL_ROOT_PASSWORD=123
COLOR='echo -e \E[01;31m'
END='\E[0m'
check (){
if [ $UID -ne 0 ]; then
action "当前用户不是root,安装失败" false
exit 1
fi
cd $SRC_DIR
if [ ! -e $MYSQL ];then
$COLOR"缺少${MYSQL}文件"$END
$COLOR"请将相关软件放在${SRC_DIR}目录下"$END
exit
elif [ -e /usr/local/mysql ];then
action "数据库已存在，安装失败" false
exit
else
return
fi
}
install_mysql(){
$COLOR"开始安装MySQL数据库..."$END
$COLOR"安装相关依赖"$END
yum -y -q install libaio numactl-libs
$COLOR"解压目录"$END
#将MYSQL 2进制包解压到/usr/local，这个是写好的；tar xf xxx -C：解压到对应的目录
tar xf $MYSQL -C /usr/local/
$COLOR"制作软连接"$END
#做一个软连接，将包link到/usr/local下，这里作为默认目录，而不是去修改他的包名
##sed打印出包名
##[root@rocky ~]#echo mysql-8.0.30-linux-glibc2.12-x86_64.tar.xz | sed -nr 's/^(.*[0-9]).*/\1 /p'
##mysql-8.0.30-linux-glibc2.12-x86_64
MYSQL_DIR=`echo $MYSQL| sed -nr 's/^(.*[0-9]).*/\1/p'`
ln -s /usr/local/$MYSQL_DIR /usr/local/mysql
$COLOR"创建mysql用户"$END
##安装mysql必须要用roo$COLOR"制作软连接"$ENDt用户/用户组，chown -R表示递归修改权限
chown -R root.root /usr/local/mysql/
id mysql &> /dev/null || { useradd -s /sbin/nologin -g mysql -r mysql ; action "创建mysql用户"; }
$COLOR"准备mysql路径"$END
##准备mysql客户端的指令，包括路径等
##准备环境变量
echo 'PATH=/usr/local/mysql/bin/:$PATH' > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh
ln -s /usr/local/mysql/bin/* /usr/bin/
cat > /etc/my.cnf <<EOF
##往my.cnf内写好对应的参数变量，比如对应的数据目录，日志目录，运行Pid
##server-id可以指定为IP的最后一位
##[mysqld]服务器端配置
[mysqld]
server-id=`hostname -I|awk '{print $1}'`
log-bin ##开启binlog
datadir=/data/mysql ##指定数据目录dir
socket=/data/mysql/mysql.sock ##指定本地socket连接
log-error=/data/mysql/mysql.log ##错误日志
pid-file=/data/mysql/mysql.pid ##mysql.pid进程
##客户端配置，或者叫mysql端，[mysql]
[client]
socket=/data/mysql/mysql.sock ##本地客户端通过socket连接到本地localhost用户
EOF
##创建data数据目录
[ -d /data ] || mkdir /data
mysqld --initialize-insecure --user=mysql --datadir=/data/mysql
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld ##启动mysqld
chkconfig --add mysqld
chkconfig mysqld on
service mysqld start
[ $? -ne 0 ] && { $COLOR"数据库启动失败，退出!"$END;exit; }
#MYSQL_OLDPASSWORD=`awk '/A temporary password/{print $NF}' /data/mysql/mysql.log`
#mysqladmin -uroot -p$MYSQL_OLDPASSWORD password $MYSQL_ROOT_PASSWORD &>/dev/null
sleep 3
mysqladmin -uroot password $MYSQL_ROOT_PASSWORD &>/dev/null
action "数据库安装完成"
}
check
install_mysql
##修改MySQL内的显示prompt
cd /root
echo export MYSQL_PS1="(\\u@\\h) [\\d]\\>" >> .bashrc
source .bashrc
Linux通用版，已经经过源码编译的包进行安装
MySQL程序安装分为程序路径(APP)和数据路径(DATA)
1.下载好对应的二进制包，下载社区版的服务器端
下载这个tar包，注意服务器的架构位数
2.安装相关依赖包，创建mysql服务用户
yum -y install libaio numactl-libs
##建立MySQL相关用户
-r：建立系统账号，系统账号在0-1000号之间
-g mysql：属于Mysql组
-s:用户登陆后使用的shell环境
useradd -r -g mysql -s /sbin/nologin mysql
3.解压数据包到对应路径
tar xf
二进制包安装可能出现的问题
1.缺少lib依赖，在登陆mysql的时候
解决办法：先检查libaio依赖包是否有安装
rpm -q libaio numactl-libs
发现安装的是6.1版本，系统内只认so.5包，所以执行ln -s软连接，将我们新安装的包ln -s到系统的so.5 lib依赖中
ln -s /usr/lib64/libtinfo.so.6.1 /usr/lib64/libtinfo.so.5
成功
2.无法实现登录到caching
在/etc/my.cnf内写入skip-grant-tables
重启mysql服务
实现使用systemd管理mysqld.service：编写服务脚本
nginx.service编写案例
###写的路径，也可以写在/lib，/lib就是/usr/lib的link
# /usr/lib/systemd/system/nginx.service
[Unit]
Description=The nginx HTTP and reverse proxy server ##服务说明
After=network-online.target remote-fs.target nss-lookup.target ##依赖关系：有网络network，remote远程主机；需要先启动这些服务
Wants=network-online.target ##必须依赖：网络服务
[Service]
Type=forking
PIDFile=/run/nginx.pid
# Nginx will fail to start if /run/nginx.pid already exists but has the wrong
# SELinux context. This might happen when running `nginx -t` from the cmdline.
# https://bugzilla.redhat.com/show_bug.cgi?id=1268621
ExecStartPre=/usr/bin/rm -f /run/nginx.pid ##启动进程，启动前需要做什么--->先删除掉原有的nginx.pid进程
ExecStartPre=/usr/sbin/nginx -t ##检查nginx的配置文件是否有问题：nginx -t--->检查的是nginx.conf，检查语法错误
ExecStart=/usr/sbin/nginx ##执行systemctl start xxx的时候，执行的就/usr/sbin/nginx这个可执行文件，后续如果是源码编译安装想放到systemd执行，可以写成例如/usr/local/nginx/sbin/nginx或者/usr/local/tomcat/bin/startup.sh
ExecReload=/usr/sbin/nginx -s reload ##执行reload，nginx执行文件就是 nginx -s reload
KillSignal=SIGQUIT
TimeoutStopSec=5
KillMode=process
PrivateTmp=true
[Install]
WantedBy=multi-user.target
进入到对应目录，创建.service文件，授权644
配置完成后，记得重新加载守护进程，systemctl daemon-reload
预检查：ExecStartPre：没有这个mysqld
cd /usr/lib/systemd/system
touch mysqld.service
chmod 644 mysqld.service
[Unit]
Description=MySQL Community Server
After=network.target ##服务依赖，显示在bash上面
After=syslog.target
[Install]
WantedBy=multi-user.target
Alias=mysqld.service
[Service]
##这里是整个service
User=mysql
Group=mysql
PIDfile=/data/mysql/mysql.pid
# Execute pre and post scripts as root
PermissionsStartOnly=true
# Needed to create system tables etc.
#ExecStartPre=/usr/bin/mysql-systemd-start pre
# Start main service
#ExecStart=/usr/bin/mysqld_safe --basedir=/usr
#ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/mysql/mysql.pid $MYSQLD_OPTS
##启动脚本，启动对应的执行文件，类似nginx
ExecStart=/usr/local/mysql/bin/mysqld
# Don't signal startup success before a ping works
#ExecStartPost=/usr/bin/mysql-systemd-start post
# Give up if ping don't get an answer
TimeoutSec=600
Restart=always
PrivateTmp=false
编辑PS1
cd /root
echo export MYSQL_PS1="(\\u@\\h) [\\d]\\>" >> .bashrc
source .bashrc
5.源码包安装MySQL--->需要先进行编译cmake
编译要求：编译机器的内存必须足够大，就像在java环境编译java包一样，同样是很吃PC机的内存的
建议调整vmware虚拟机：8c16g
6.docker安装MySQL
yum -y install docker-ce
docker search mysql:8
MySQL安全加固
ODBC：微软调用数据库的接口
JDBC：JAVA语言调用SQL的语法，jdbc：3306等等
MySQL和mariadb的关系：mariadb是MySQL的一个分支，在Cent OS 7上自带mariadb数据库，语法结构，数据库结构完全一致
存储引擎：innodb，intradb
##yum info mysql-server
##yum info mariadb
##apt list mysql-server
Cent OS 7：自带mariadb Version : 5.5.68
Cent OS 8(rocky 8.6)：自带MySQL 8.0.26
ubuntu 20.04：自带MySQL 8.0.30
sed -i 's/DNS/#DNS/g' /etc/sysconfig/network-scripts/ifcfg-ens33 ; systemctl restart network
mysql> status ##查看MySQL状态，可以看MySQL一些版本，字符集等
mysql> status
--------------
mysql Ver 8.0.30 for Linux on x86_64 (MySQL Community Server - GPL)
Connection id: 8
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.30 MySQL Community Server - GPL
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4 ##指定字符集
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/lib/mysql/mysql.sock
Binary data as: Hexadecimal
Uptime: 5 min 4 sec
Threads: 2 Questions: 5 Slow queries: 0 Opens: 119 Flush tables: 3 Open tables: 38 Queries per second avg: 0.016
--------------
MySQL8.0修改密码
1.如果忘记密码，使用root登录，进入/etc/my.cnf的mysqld修改
echo "skip-grant-tables" >> /etc/my.cnf
systemctl restart mysqld
2.进入到服务器，修改密码，本地登录用户
mysql -uroot
alter user 'root'@'localhost' identified by '123';
alter user 'root'@'%' identified by '123';
flush privileges;
select version();
+-----------+
| version() |
+-----------+
| 8.0.30 |
+-----------+
1 row in set (0.00 sec)
3.成功登录
4.注释掉skip-grant-tables，恢复本地localhost访问
mysql_secure_installation：系统自带安全加固脚本
生产环境中建议执行的脚本
在mysql5.5中会存在匿名用户，anoymous用户，脚本中可以remove掉；删除掉test的数据库
匿名账号：可以直接登陆到MySQL内，只有test库的操作权限，不能让所有人都有权限登录
作用：
设置root密码
禁止root远程登录(也可以允许)，allow root login on remotely
删除匿名用户
删除test数据库
在老版本(5.5 5.6 5.7左右)，需要执行一下安全脚本，在8.0以上得到安全加固了，比较稳定
##查看user和host(用户和主机)，从Mysql库的User表内查询
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| root | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
##查看mysql的版本
mysql> select version();
##查看现有的库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
mysql-prompt：修改MySQL的命令行.bashrc显示
可以将mysql-prompt的变量写入到系统的变量中
vim .bashrc
export MYSQL_PS1="(\\u@\\h) [\\d]\\_"
\\u：代表当前登录MySQL的用户
\\h：当前登录的主机host，现在为local后使用
[\\d]：表示default database，用户自行选择的库
显示效果：
(root@localhost) [(none)]>use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
(root@localhost) [mysql]>
MySQL服务端配置文件：my.cnf
cat /etc/my.cnf
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
主要的选项，一般都是写在mysqld服务端的命令指令
[mysqld] 服务端
[mysql] 客户端
通过禁用远程连接，实现本地socket连接
一般用于数据库维护的阶段
##先查看mysql的业务端口
ss -ntl | grep 3306
LISTEN 0 128 [::]:3306 [::]:*
LISTEN 0 70 [::]:33060 [::]:*
##写配置
vim /etc/my.cnf
[mysqld] --->服务端指令
skip-networking=on
systemctl restart mysqld--->重启服务
一般来讲，vim /etc/my.cnf还是选择#注释掉好一些，也可以用on/off 1/0 true/false来代替
##再次查看端口，发现是没有端口了，但是通过本地的socket套接字还是可以连接的
[root@master ~]#ss -ntl | grep 3306
[root@master ~]#mysql -uroot -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
--->证明是socket连接的
(root@localhost) [(none)]>status
UNIX socket: /var/lib/mysql/mysql.sock
##禁用看下status，看下MySQL的连接状态
发现这里是不能连接的证明起作用了，因为端口已经down了
mysql脚本文件：直接写SQL语句
vim test.sql
use mysql
select version()
select user,host from user;
mysql test.sql：执行该脚本
MySQL系统库：mysql库
MySQL默认的存储数据的目录：/var/lib/mysql
其实MySQL在Linux内就是一个个文件夹，一个库就是一个目录，比如说mysql库就是一个目录
##创建一个库，test1
(root@localhost) [(none)]>create database test1 charset utf8;
Query OK, 1 row affected, 1 warning (0.00 sec)
##删除一个库，删除了磁盘上对应的目录
drop database test1
有test了
MySQL常见的客户端命令
1.mysqladmin
-uroot -p123：这个需要知道MySQL的密码，没有的话不起作用
##1.查看MySQL的性能指标，线程数，处理的任务数，慢查询等等一些性能指标
mysqladmin -uroot -p123 status
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Uptime: 2198 Threads: 3 Questions: 52 Slow queries: 0 Opens: 180 Flush tables: 3 Open tables: 99 Queries per second avg: 0.023
##2.计划任务实现MySQL故障重启
mysqladmin -uroot -p123 ping
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqld is alive
##如果返回的$?为1，不正常，则重启MySQL(不常用)
mysql -uroot -p123 ping || systemctl restart mysqld
2.查看表的状态
show table status like 'emp';
SQL语言规范
sql脚本：test.sql
1.SELECT * FROM mysql.user;
SELECT，FROM都为关键字，mysql.user是库.表名，关键字可以大写
/*多行注释*/
##多个子句
SELECT * #子句
FROM mysql.user #子句
where user='root' #子句
声明式语言
基本分类：
1.DDL语句：数据定义，包括CREATE创建，DROP删除，ALTER替换
2.DML语句：数据操作，INSERT插入，DELETE删除，UPDATE更新，增删改
3.DQL语句：查询，SELECT
4.DCL语句：数据控制语言，GRANT授权，REVOKE收回权限
5.TCL语句：事务控制语言，COMMIT提交事务，ROLLBACK回滚，SAVEPOINT存储点吧
查看SQL语句帮助
##查看SQL帮助
mysql> help select
(root@localhost) [mysql]>help select
Name: 'SELECT'
Description:
Syntax:
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr] ...
[into_option]
做修改前，或者是做重大变更，记得做一个快照
1.字符集配置
字符集的影响：在于你是否能够插入中文的记录到表内，表内的字段是否支持中文
在MySQL8.0默认版本：utf8mb4
全世界通用字符：uft8
简体中文：gb2312
##MySQL8.0都是用的utf8mb4，默认是用的
[mysql]>show variables like '%character%';
+--------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/local/mysql-8.0.30-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------------+
##MySQL 5.6服务端/客户端都是使用的不标准的字符集，服务端还是latin拉丁字符集
mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
Linux系统内输出字符集
[root@rocky ~]#echo $LANG
zh_CN.UTF-8
查看MySQL数据库可用字符集，常用的几个
mysql>show character set;
(root@localhost) [mysql]>show character set;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| binary | Binary pseudo charset | binary | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| utf8mb3 | UTF-8 Unicode | utf8mb3_general_ci | 3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 |
+----------+---------------------------------+---------------------+--------+
配置字符集：服务端、客户端等
vim /etc/my.cnf
[mysqld]
default-character-set=utf8mb4
[mysql]/[client]
default-character-set=utf8mb4
查看排序规则，基本用的都是uft8mb4的排序规则，一般来说，字符集绑定了排序规则使用；排序规则也可以修改，涉及到你查询表内的记录，数据是如何排序的
show variables like 'collation%'
(root@localhost) [mysql]>show variables like 'collation%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database | utf8mb4_0900_ai_ci |
| collation_server | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)
2.管理数据库，字符集，排序
show：查看
##查看数据库，表
show databases
show tables
CREATE：创建，不能创建已经存在的库
##创建数据库，指定字符集
create database catyer character set utf8mb4;
##创建数据库，指定字符集，指定排序规则，不同的服务可能排序规则不同
utf8_bin：忽略大小写
create database catyer character set utf8mb4 collate utf8_bin;
alter修改数据库的字符集
##先show查看数据库的属性，默认字符集为utf8mb4
show create database catyer
catyer | CREATE DATABASE `catyer` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
##修改字符集
alter database catyer charset utf8
show create database catyer
DROP：删除数据库
DROP database catyer;
3.数据类型
整数
常见的数据类型：根据业务的需求，业务数据的需求选择数据类型定义
INT：整数，unsigned：表示非负数
tinyint：0-128数字，2的8-1=7次方
float：浮点数
char：字符串，定长字符串
varchar：变长字符串
date、time：日期，时间
各种修饰符：空NULL，非空NOT NULL，DEFAULT默认值等
主键PK，唯一键UK等
AUTO_INCREMENT：定义数字自动增长
char和varchar的区别
char是定长，如果超过字符，则截断；存abcd，占4个字节，存abcdef，只能存到abcd
varchar：变长，存abcd，则存abcd，占5个字节
所以还是看业务数据类型的，比如定长数据，可以使用char(5)，如果是不确定的长度，还是用varchar好一点
几种INT对比
tinyint：占一个字节，也就是2的8次方个B，0-256数字
smallint：2个字节，可以放2的16次方=65536个数字
INT：占4个字节，2的32次方
4.管理库表DDL
一个表在磁盘上就是一个数据库目录内的一个文件，需要到对应的数据库目录下才能看到，这里就是表文件
查看表show
##查询
select 字段 from table
create table xxx
create database xxx
##以竖行的形式显示所有表的信息状态，可以查看一些例如存储引擎，创建时间，字符集+排序规则等
show table status\G;
*************************** 1. row ***************************
Name: student
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2022-08-15 21:47:56
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
基本的创建表操作
一般来说都是规定好表结构，字段数据类型等
##管理表
show tables;
##删除表
drop table catyer;
##创建一个学生表
##id字段tinyint0-256数字，非负，非空，还是主键，自增长
##名字：字符4个，非空
##性别：male or female，6个字符
##年龄：0-256，非空
create table student (
id tinyint unsigned primary key auto_increment,
name char(4) not null,
sex char(6) not null,
age tinyint unsigned
);
##查看表结构：desc，这个表有4个字段，4个域field，字段类型，是否为空，默认值是什么，EXTRA额外的属性：比如自增长
(root@localhost) [catyer]>desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | tinyint unsigned | NO | PRI | NULL | auto_increment |
| name | char(4) | NO | | NULL | |
| sex | char(6) | NO | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
AS：根据另一张表的查询结果创建目标表，等于将查询到的特定字段的值创建成了表，包括复制表结构和查询出来的数据
AS可以省略
##在catyer库内创建user表
create table user select user,host from mysql.user;
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
(root@localhost) [catyer]>select * from user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| root | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
LIKE：创建一个表结构一样的表，LIKE表没有数据，只有一样的结构
create table user_like like user;
##两张表的表结构是一致的，HOST定义是255个字符
(root@localhost) [catyer]>desc user;
+-------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| user | char(32) | NO | | | |
| host | char(255) | NO | | | |
+-------+-----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
(root@localhost) [catyer]>desc user_like;
+-------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| user | char(32) | NO | | | |
| host | char(255) | NO | | | |
+-------+-----------+------+-----+---------+-------+
2 rows in set (0.01 sec)
表、库重命名rename
ALTER TABLE table_name RENAME TO new_table_name
ALTER TABLE students RENAME TO stu;
ALTER database students RENAME TO stu;
5.DML语句
insert插入语句
一定要与表内的字段匹配，包括插入的顺序，可以先DESC一下表格，一般都是跑.sql的脚本
注意字符集：如果是latin，那仅仅只支持英文输入；如果是uft8mb4，则支持中文英文，是世界通用的字符集
##基本语法
insert into table_name;
insert table; into可以省略
desc table_name;
(root@localhost) [catyer]>desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | tinyint unsigned | NO | PRI | NULL | auto_increment |
| name | char(4) | NO | | NULL | |
| sex | char(6) | NO | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
##要和字段匹配，以及char字符的限制，不能超过char(4)的限制
(root@localhost) [catyer]>insert into student values(1,'cat','M','25');
Query OK, 1 row affected (0.00 sec)
(root@localhost) [catyer]>select * from student;
+----+------+-----+------+
| id | name | sex | age |
+----+------+-----+------+
| 1 | cat | M | 25 |
+----+------+-----+------+
1 row in set (0.00 sec)
##批量导入，将查询结果导入，插入
##要求：表结构一致
insert into student1 select * from student
insert student1 SELECT * from student;
有关自增长字段的用法，需要指明插入set的字段
##在insert这里需要在表中指明不添加id的字段，才可以实现id的自增，如果增加id字段，则需要写好id的值
insert student(name,sex,age) VALUES('xiao','male',20);
> Affected rows: 1
> 时间: 0.003s
##如果在insert中不指定字段，则VALUES默认插入所有，不写ID的值就会报错
create table test (id auto_increment)
update语句：修改语句
##修改学生的年龄
update table_name set 字段名 where(条件判断，一般是id)
##update
update student set age=30 where id=3;
delete语句：删除语句
##delete
delete from student where id=4;
truncate：清空表，同时释放磁盘空间--->非常危险，慎用
保留表结果，高危指令，同时也会释放磁盘空间
truncate table test;
##执行一个.sql插入10w条记录的脚本
##创建一个表，id自增主键，名字10个字符，工资默认20
create table testlog (id int auto_increment primary key,name char(10),salary int default 20);
##声明一个函数
delimiter $$
create procedure sp_testlog()
begin
declare i int;
set i = 1;
while i <= 100000
do insert into testlog(name,salary) values (concat('wang',FLOOR(RAND() * 100000)),FLOOR(RAND() * 1000000));
set i = i +1;
end while;
end$$
delimiter ;
##执行函数，用了49s
call sp_testlog;
CALL sp_testlog;
> OK
> 时间: 49.073s
使用truncate实验，清空表数据，同时释放磁盘空间，不常用
truncate table testlog
6.DQL语句：单表查询(重点掌握)
一般DML语句都是修改的，通过.sql脚本进行增删查改，或者是项目之初建库的时候，直接执行.sql的语句进行批量业务数据的插入
其实SQL的查询语句就类似英文句子
单表查询
count(*)：只显示有值的，NULL空是没有的
##查询对应的列(域field)
select Name,Age from students;
##给输出的字段加别名，可以不这么用
select name 姓名 from students;
##查询表内一共有多少条记录：count(*)，有25条，只显示有值的，如果为NULL则没有
select count(*) from students;
##空或者非空，存在或者非存在
NULL
is not NULL
exist
is not exist
select+where查询
limit：可以限制首页可以查看多少条记录，过了就要翻下一页
##精确查询到某一行，and并列
select * from students where age <20 and gender='F';
##or或者
select * from students where age <20 or gender='F';
##相同列不同的取数，同一列不同值
从1班和3班中挑出女生
select * from students where classid in (1,3) and gender='M';
##between之间
1班和3班中取出年龄在20-30岁的学生
select * from students where classid in (1,3) and age between 20 and 30;
select limit 限制查看的条目数
##直接看前3条记录
select * from students limit 3;
select * from students limit 10;
select * from students order by age desc limit 5;
25 Sun Dasheng 100 M
3 Xie Yanke 53 M 2 16
6 Shi Qing 46 M 5
13 Tian Boguang 33 M 2
4 Ding Dian 32 M 4 4
##limit跳过前3行，从第4行开始显示6行
select * from students limit 3,6;
4 Ding Dian 32 M 4 4
5 Yu Yutong 26 M 3 1
6 Shi Qing 46 M 5
7 Xi Ren 19 F 3
8 Lin Daiyu 17 F 7
9 Ren Yingying 20 F 6
where判断是否为空
select * from students where classid is null;
select * from students where classid is not null;
模糊查询like
##查询以name ma开头的记录
select * from students where name like "%ma%";
##查询包含姓名内包含x的记录，可以作为在网站内查询某个商品，一般都是模糊查询，比如我要查罗技，但是这个品牌下有很多记录，会显示所有的模糊查询记录，然后按照一定顺序(比如说购买量)显示出来
select * from students where name like "%x%";
聚合函数
##统计平均年龄，总数
select count(*) from students;
select avg(age) from students;
select sum(age) from students;
##男生女生中年龄最大的，以性别分组
select max(age) from students group by gender;
sql注入攻击
绕过数据库的安全监测，导致黑客成功登录到网站的后台/网站的数据库，从而窃取数据；利用数据库的user&password的漏洞
select group by分组统计
group by语法：如果使用了group by的写法，前面select的字段就只能是group by的条件字段，或者是聚合函数
不是group by的东西
##group by分组统计，按照性别
select gender,avg(age) from students group by gender;
M 33.0000
F 19.0000
##添加多一个字段，就报错
select gender,avg(age),stuid from students group by gender
> 1055 - Expression
#3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hellodb.students.StuID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
##group by后继续过滤条件：having或者是where，两种写法；from后面加where过滤条件，或者是group by后面加having
select gender,avg(age) from students group by gender;
M 33.0000
F 19.0000
select gender,avg(age) from students where gender='F' group by gender;
select gender,avg(age) from students group by gender having gender='M';
##多个字段Group by，过滤掉空行
select ClassID,gender,avg(age) from students WHERE ClassID is not null group by gender,ClassID;
select order by排序
##正序倒序排序，正序从小到大，倒序从大到小，类似sort，sort默认由小到大，sort -r从大到小
##排序
select * from students order by age;
select * from students order by age desc;
7.SQL多表查询(重点掌握)
子查询：嵌套SQL语句
SQL语句中嵌套了别的查询结果
##子查询，实现修改老师年龄为学生最大年龄
select max(age) from students;
update teachers set age=(select max(age) from students) where tid='1';
1 Song Jiang 100 M
2 Zhang Sanfeng 94 M
3 Miejue Shitai 77 F
4 Lin Chaoying 93 F
##子查询作为条件
select * from teachers where age=(select max(age) from students);
联合查询union(字段数量相同)
将多张表合并成一张表，联合查询这个大表
需要相同字段数量，不能字段数量不统一，最好是字段的数据类型类似或者相同
##联合查询，凡是查询都可以加别名
select stuid,name,age,gender from students union select * from teachers;
select stuid ID,name 姓名,age 年龄,gender 性别 from students union select * from teachers;
1 Shi Zhongyu 22 M
2 Shi Potian 22 M
3 Xie Yanke 53 M
4 Ding Dian 32 M
5 Yu Yutong 26 M
6 Shi Qing 46 M
7 Xi Ren 19 F
8 Lin Daiyu 17 F
9 Ren Yingying 20 F
10 Yue Lingshan 19 F
横向合并，交叉连接cross join，乘积算法，不常用，因为出来的结果太大了
表1：学生表，25条记录
表2：老师表，4条记录，老师表和学生表逐一组合，形成4*25=100条记录
##cross join
select * from students cross join teachers;
内连接inner join：交集
前提：两张表最好有类似的字段，一样的数据，不然无法筛选的
取两张表的相同字段的值，也可以指定查询的字段，显示出来，可以给查询的表写别名来区分不同的表的字段
##案例：学生表和老师表共同在于teacherid和tid有相同的
1.先列出需要查询显示的字段，students s teachers t，可用这样的表示法来写明哪张表
2.inner join内连接
3.on 条件判断相等
select s.StuID,s.name,s.TeacherID,t.name,t.tid from students s INNER JOIN teachers t on s.TeacherID=t.TID;
inner join相同条件的记录数只有3条
多次取内连接
##成绩表
##显示学生成绩
select s.stuid,s.name,sc.CourseID,sc.Score from scores sc inner join students s on sc.StuID=s.StuID;
##三张表内连接取相同字段，在前面用select显示需要查询的字段，能显示出课程名称
##显示学生id，姓名(student)，课程名(course)，分数(score)
select st.stuid,st.name,co.Course,sc.Score from scores sc inner join students st on sc.StuID=st.StuID inner JOIN courses co on sc.CourseID=co.CourseID;
左外连接和右外连接：left join
保存左表(右表)的全部数据，条件on写对应两张表的条件，如果查询的左表(右表)记录数不符合，则填NULL代替；兼顾不同边
为什么right join老师表只有4条记录，有一条为空？--->因为有一个teacher ID对不上t ID，所以为NULL
##left join
##因为stu有25条记录，teacher有4条记录，所以除了ID对应好的那几条，其余的teacher表都为NULL
select * from students s left JOIN teachers t on s.TeacherID=t.TID;
##right join
##因为stu有25条记录，teacher有4条记录，所以除了ID对应好的那几条，其余的stu表都为NULL
select * from students s right JOIN teachers t on s.TeacherID=t.TID;
左外连接和右外连接取反：去掉对应条件，想想交集和并集就行
##left join取反
select * from students s left JOIN teachers t on s.TeacherID=t.TID where t.TID is null;
##right join取反
select * from students s right JOIN teachers t on s.TeacherID=t.TID;
完全外连接：左连接+union+右连接，可以去重
##完全外连接，结合左外连接+右外连接，去重
select * from students s left JOIN teachers t on s.TeacherID=t.TID
union
select * from students s right JOIN teachers t on s.TeacherID=t.TID;
自连接：自己表连接自己，筛选记录
create table emp (id int,empname varchar(10),leaderid int);
desc emp;
insert into emp values(1,'magedu',null),(2,'wang',1),(3,'zhang',2);
select * from emp;
1 magedu
2 wang 1
3 zhang 2
需求：查询每个人对应领导的名字，ID
##自己的表也可以配置两个别名
select * from emp e inner join emp l on e.id=l.leaderid
##自己的表也可以配置两个别名，跳出对应的列
select * from emp e inner join emp l on e.id=l.leaderid
##ifnull空函数，逻辑是员工表的领导ID=领导表他们自己的ID，先对应领导
##left join左连接，显示左表所有的值，如果是Inner join，则是显示一定有的，magedu就没了，不是交集，因为magedu没有leaderid
select e.name 员工姓名,IFNULL(l.name,'大boss') 领导姓名 from emp e left join emp l on e.leaderid=l.id
有关MySQL数据库的其他功能过程
1.视图view
功能作用：类似表，可以将一段查询语句的结果当做一个view来执行，后续可以查看
##创建视图
create view v_leader as select e.name 员工姓名,IFNULL(l.name,'大boss') 领导姓名 from emp e left join emp l on e.leaderid=l.id
select * from v_leader;
##删除视图
drop view v_leader;
2.存储过程procedure
类似脚本，主要是可以将进入到数据库内的操作先写成一系列的操作，是.sql的脚本，比如建库建表，插入数据等等，可以写类似while循环
里面类似function函数，声明一个变量i，每条语句都要加分号
结束符使用$$或者//都行，自定义的结束符，可以生成随机数
##创建表，生成10w条记录
create table if not exist testlog (id int auto_increment primary key,name char(10),salary int default 20);
delimiter $$ ##定义结束符
create procedure sp_testlog()
begin
declare i int;
set i = 1;
while i <= 100000
do insert into testlog(name,salary) values (concat('wang',FLOOR(RAND() * 100000)),FLOOR(RAND() * 1000000));
set i = i +1;
end while;
end$$
delimiter ;
##执行存储过程
call sp_testlog;
##查看表的记录
show tables;
select count(*) from testlog;
##查看库内的存储过程
show procedure status \G;
3.trigger触发器
定义：当某个事件触发的时候(insert加数据，delete删除数据)，自动修改表内的数目，称为触发器trigger
注意：需要先在count表内归0，不然里面默认是NULL
###创建表
CREATE table student_info(
stu_id int NOT null PRIMARY key auto_increment ,
stu_name varchar(255) DEFAULT null
);
CREATE table stu_count(
stu_count int default 0
);
##查看测试表
show tables;
##创建trigger，加减的触发器，每新加一条记录(each row)，count就会+1；每减少一条记录，count就会-1
create trigger trigger_incre
after insert
on student_info for each row
update stu_count set stu_count=stu_count+1;
create trigger trigger_decre
after delete
on student_info for each row
update stu_count set stu_count=stu_count-1;
##查看库内的触发器个数
show triggers;
##查询表内的内容，也可以写存储过程批量化的插入表记录
select * from student_info;
select * from stu_count;
insert into student_info values(1,'mary')，(2,'mary')，(3,'mary');
insert into stu_count values(0);
##delete，会自动减少count的值.where查询后delete
delete from student_info where stu_id>=2;
##清空表
truncate stu_count;
truncate student_info;
4.EVENT事件---计划任务
show variables like event-schduler
可以定时执行对应的任务操作
##开启event事件调度，定时执行任务
select @@event_scheduler; ON
show processlist;
MySQL用户管理权限
系统默认账户，可以新创建用户，指定某些IP段可以访问
[mysql]>select user,host from user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| root | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
create user catyer@'10.0.0.%' identified by '123';
flush privileges;
在MySQL的skip-grant-tables下修改密码
当忘记了MySQL的密码后，需要在my.cnf的[mysqld]上面添加一个skip-grant-tables来跳过密码
vim /etc/my.cnf
#skip-grant-tables
systemctl restart mysqld
进入到数据库后，发现更改密码的操作无法执行
(root@localhost) [(none)]>alter user root@'localhost' identified by '123';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
需要直接去更改表操作
##之前的报错
update user set authentication_string=password'123456' where user='root';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''123456' where user='root'' at line 1
--->这个其实是mysql8.0之前的写法，后续8.0不使用=password这样的写法了
##查询一下这个表的东西，可以看到，不同的user@host的密码是不同的；如果需要改root要先区分好改哪个root，是本地root还是远程root
select user,host,authentication_string from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+
| user | host | authentication_string |
+------------------+-----------+------------------------------------------------------------------------+
| root | % | $A$005$G]@">|wLG%Oe3PK61VSgxpemuc0kWZZt87GwaA0XbsKK1M4DumIT8rKijO2 |
| catyer | 10.0.0.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
##mysql8.0修改默认密码
1.设置为空
update user set authentication_string='' where user=root
2.将配置文件内的skip注释掉，重启服务
vim /etc/my.cnf
#skip-grant-tables
systemctl restart mysqld
3.使用空密码登录到DB
mysql
4.修改本地用户密码
alter user root@'localhost' identified by '123';
flush privileges;
5.登录
mysql -uroot -p123
解决MySQL8.0无法使用远程登录的问题caching_sha2_password
在创建好一个用户后，因为MySQL8.0默认的密码策略，导致我们一时间没法直接连接，出现这个报错
ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
原因就在于：8.0默认使用的认证方式是caching_sha2_password，我们设置的密码过于简单，实验环境下，需要在指定给远程用户的时候写上
在8.0以前(5.7、5.6等)，都是mysql_native_password这个参数
update
##查看默认的认证插件
show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.00 sec)
##最佳做法：可以在赋予密码的时候，加上mysql_native_password，记住这种写法，需要设置简单密码的时候，能不改全局尽量不改全局
alter user catyer@'10.0.0.%' IDENTIFIED WITH mysql_native_password by '123';
flush privileges;
create user 'wpuser'@'localhost' identified by '123';
grant all privileges on wordpress.* to 'wpuser'@'%' with grant option;
##登录尝试成功
mysql -ucatyer -p123 -h10.0.0.132
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.30 MySQL Community Server - GPL
MySQL用户业务数据库授权
授权某个库的管理员操作权限
##先看下每个用户的权限
Grant_priv：具有授权的权限
Super_priv：拥有超级权限
SELECT host,user,Grant_priv,Super_priv FROM mysql.user;
+-----------+------------------+------------+------------+
| host | user | Grant_priv | Super_priv |
+-----------+------------------+------------+------------+
| % | root | N | Y |
| 10.0.0.% | catyer | N | N |
| localhost | mysql.infoschema | N | N |
| localhost | mysql.session | N | Y |
| localhost | mysql.sys | N | N |
| localhost | root | Y | Y |
+-----------+------------------+------------+------------+
6 rows in set (0.00 sec)
##授权操作，还得是本地localhost用户来，Navicat是远程用户，不具备授权能力
grant all on zabbix.* to catyer@'10.0.0.%';
flush privileges;
grant (操作权限:insert,create,delete...) on db.table_name to 'user'@'host'
grant all on hellodb(代表哪个库).* to catyer@'10.0.0.%';
grant all on hellodb.students to catyer@'10.0.0.%'; ##授权只能访问修改这个表，可以给别的开发使用，细粒度的授权
show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| students |
+-------------------+
1 row in set (0.00 sec)
##授权远程用户有授权能力，可以在navicat内编辑
update mysql.user set Grant_priv='Y' where user='root';
revoke回收权限
##查看catyer这个用户的权限
show grants for 'catyer'@'10.0.0.%';
+-----------------------------------------------------------+
| Grants for [email protected].% |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO `catyer`@`10.0.0.%` |
| GRANT ALL PRIVILEGES ON `zabbix`.* TO `catyer`@`10.0.0.%` |
+-----------------------------------------------------------+
##回收删除权限
revoke delete on hellodb.* from catyer@'10.0.0.%';
flush privileges;
##回收所有权限
revoke all on hellodb.* from catyer@'10.0.0.%';
flush privileges;
MySQL的基本架构和优化
基本的MySQL架构
1.连接器connectors：负责给到各种程序进行对接，连接，例如C，JAVA(JDBC，JAVA OPEN DATABASE CONNECTION)，ODBC(Oracle database connection等)，.net等windows的程序
2.连接池，会做各种的校验，比如程序链接过来是有哪个库的权限的，或者哪个库的表权限，都需要去指定用户名/密码，会分配一个线程给到对应的用户来使用，线程是可以复用的
SQL接口：负责各种语句程序，存储过程的实现，比如DML修改(insert，update，delete，alter)，DDL(create，drop)等，视图(select)，存储过程(procedure等)
对于某个表的授权
cache & buffer：将热点数据保存到读缓存(cache)和写缓存(buffers)里面
3.存储引擎：决定了MySQL在服务器上的保存方式，一般使用Innodb的存储引擎--->innodb
4.MySQL服务器的管理：备份工具，恢复工具，主从同步工具，配置编译工具等
存储引擎
主要的两种默认引擎：myisam和innodb的对比
主流还是innodb为主
show engine
innodb的优点：支持事务，行级别的锁(允许修改访问级别到行)，和外键
不同用户进到表里面进行修改，精确到行(row-level)
##查看MySQL数据库的存储引擎
默认使用innodb
show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
myIAMS和innodb存储引擎的区别
innodb
支持：innodb支持事务(transaction)，行级别的锁(row-level)，外键(foreign key)，支持聚簇索引
缺点：库支持最大存储磁盘级别为64TB，但是库的大小到一定级别就会性能很差，需要分库分表执行
myiams:
支持：表级别的锁，适用于只读场景
缺点：不支持事务，外键，不支持聚簇索引
存储文件格式：mysql.ibd模式，可以进到一个库的目录里面看
每个表的存储格式：.ibd格式，创建一个表，生成一个.ibd文件
管理数据库引擎(一般不随便动)
##查看所有存储引擎
show engines
##查看当前的存储引擎，包括临时表也是innodb
show variables like '%storage_engine%';
+---------------------------------+-----------+
| Variable_name | Value |
+---------------------------------+-----------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| disabled_storage_engines | |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+
##在系统上指定默认的存储引擎
vim /etc/my.cnf
[mysqld]
default_storage_engine=InnoDB
##查看某张表的存储引擎，like后面加上表名
show table status like 'testlog' \G
*************************** 1. row ***************************
Name: testlog
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 98399
Avg_row_length: 48
Data_length: 4734976
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 100001
Create_time: 2022-08-20 16:37:08
Update_time: NULL
Check_time: NULL
Collation: utf8mb3_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
MySQL中的系统数据库
最好是不要修改，动他，一般都是mysql库修改用户等
新的库一般包含：
information_schema
mysql
performance_schema
sys
##mysql库：一般都需要进到mysql库内去操作用户，用户权限授权
use mysql
create user xxx@'%' identified by 'xxx'
grant all on wordpress.* to xxx@'%'
flush privileges;
information_schema：元数据存储
performance_schema：性能表
sys：performance_schema精简版
MySQL变量和配置选项
变量和配置选项不同
##服务器选项和变量
分类：
1.global变量
2.session变量(单会话框)
3.dynamic动态变量，可以动态修改，在内存内直接修改
常见的服务器变量，一般是配置常见的服务器变量，比如id，binlog开启，数据目录，服务器sooket，错误日志，pid进程(关乎到启动脚本)等
##一般是进入到[mysqld]服务器内进行配置
vim /etc/my.cnf
[mysqld]
server-id=132
log-bin
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
#skip-grant-tables
#default_authentication_plugin=mysql_native_password
[client]
socket=/data/mysql/mysql.sock
##mysql --verbose --help
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
----------------------------------------- --------------------------------
auto-rehash TRUE
auto-vertical-output FALSE
bind-address (No default value)
binary-as-hex FALSE
character-sets-dir (No default value)
column-type-info FALSE
MySQL索引
提高数据库查询的效率，主要还是查询，要查询的内容加上次序，这个表建立索引一般在读操作远远大于写操作的基础上，如果进行频繁修改的话，索引需要不断的更新
优点：
1.可以降低扫描的次数，提高IO
2.进行良好的排序
缺点：占用额外磁盘空间，如果写操作次数多余读操作次数，则需要不停的去更新索引，从而影响了库的性能
常见数据结构索引类型
B+tree索引：常见的索引架构，任意字段的数据，不限制
hash索引：精确查找
主键索引：基于主键做的索引，比如订单表，就是以订单编号(假设auto_increment)，根据这个来查询
聚簇索引：数据+索引放在一起，数据为.idb类型的文件，索引为.idx类型的文件
二叉树形态
有树根的情况，一直往下建立索引查询，二分法，以树根为基准，比如说左边是小于树根的，右边是大于树根的，然后两种依次往下排，每个分支节点都是这么分的排序方法，依次往下排
每当有大于或者小于的数字，分为放在右边和左边
Binary Search Tree Visualization (usfca.edu)
B-tree索引：balance index
特点：平衡性好，比纯二叉树的话，覆盖到所有的数据；
硬盘1（页1--->16K的大小）：存放17号和35号的学生信息，有p1，p2，p3三个指针指向三个range：<17，17<x<35，x>35，三个范围内出现三个硬盘快，再依次往下分，直到不能分为止
优点：比二叉树索引更加平衡，能找到所有范围内的数据
缺点：需要遍历太多次了，如果有些数据在最底层，影响程序性能，看查询的数据是什么数据
B+tree索引(mysql使用)
关系型数据库常用的索引算法
特点：根节点+分支节点(一共两层)，存放的都是索引的东西，而不存放数据，索引的内容比如是1个数字+指针
5+p1,20+p2,60+p3,
5-20又可以分为5-p1，10-P2，20-p3等等，在这里往下第三层才开始放数据，1条记录假设占用1K的空间
优势：树的层数一般就是2-3层，IO快，适用于普遍的情况，同时表下面加上了链表，二层每个索引下面的数据都是有链接关系的，可以直接跳转，不用又从根上开始找，大大提升找查询的效率
查询路径：根据索引查询
查询条件限定：左前缀排序法，因为索引一般都是根据一定的排序方式进行建立的，比如查询所有1000开头的订单号记录ok，查询包含520的订单记录，就不行，或者查询某个用户的订单，OK
面试题：innbodb中一个B+树可以放多少条记录？
假设：2层架构+数据
根节点：1个索引=数字+指针，10个字节，存放一个索引的页为16K大小，可以放1.6K条索引
分支节点：
一条记录：1K的占用，1页能放：16条记录=16K，1条索引对应16条记录，1个分支可放：1.6k*16=25600条记录
一共有包括根节点：1.6k*25600记录=40960000条记录
一个MySQL库最多能放这么多条记录，但是实际生产环境下，超过千万条就最好是分库了
管理索引语句
适用场景：一个表内，最好是2-3个索引就OK，一般用于经常查询的字段，比如ID，货品名称等，索引多了也会带来额外的损耗
索引---->一定是基于某张表的，不同的表可以建立属于表自己的索引
MySQL库有自己的优化器，一般会选择用不用自建的索引来查询，一般来说对于单表都会使用的，基本都是B+TREE索引
##创建索引，查看某张表的索引，删除索引
create index name on testlog(name);
show indexes from testlog\G; show index from testlog;
drop index idex_salary on testlog;
##查看表结构，stuid为主键，自增长
desc students
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| StuID | int unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(50) | NO | | NULL | |
| Age | tinyint unsigned | NO | | NULL | |
| Gender | enum('F','M') | NO | | NULL | |
| ClassID | tinyint unsigned | YES | | NULL | |
| TeacherID | int unsigned | YES | | NULL | |
+-----------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
##查找某个表的索引，索引类型为btree索引，默认都是btree索引，默认都是b+tree索引
##主键索引+STUID为主键
show index from students\G;
*************************** 1. row ***************************
Table: students
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: StuID
Collation: A
Cardinality: 25
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
1 row in set (0.01 sec)
查看是否有利用索引来进行查询：explain
explain select * from hellodb.students where stuid=6;
key：是primary，证明就是主键索引，有利用索引的
rows：表示扫描了多少行得出来的
查询年龄(age字段)
##没有加索引的查询，rows=25意味着遍历了25条数据才查出来有年龄=22的记录
rows=25
type=ALL--->全表扫描
explain select * from hellodb.students where age=22;
创建索引：explain查看索引使用
MySQL内会有一定的优化，即使创建了索引也不一定使用，有时候全表查询快过索引的
select * from students;
create INDEX idx_stuname(索引名称) on students(name)(表名(字段名)); ##想截取字段名前几位，可以写(name(5))
explain select * from hellodb.students where name='Shi Zhongyu';
##结果：遍历了1行就出来了，利用了索引进行查询
姓名：一般都是左前缀匹配，这里查询以s开头的条目，也是利用了索引的，key这里都有用到idx_stuname
select * from hellodb.students where name like 's%';
explain select * from hellodb.students where name like 's%';
2 Shi Potian 22 M 1 7
6 Shi Qing 46 M 5
1 Shi Zhongyu 22 M 2 3
25 Sun Dasheng 100 M
10万条记录表创建索引优化查询速度
大表尽量避免做全表扫描
之前由存储过程生成的10w条记录的表testlog
##普通查询
select * from testlog;
99995 wang12462 573325
99996 wang49273 743714
99997 wang24035 970637
99998 wang13211 748677
99999 wang34703 489141
100000 wang40460 555592
##加入where条件
select * from testlog where salary=489141;
explain select * from testlog where salary=489141;
##创建salary的索引
create index idx_salary on testlog(salary);
##查看sql语句的执行情况，可以看到以索引来查询比没有索引的速度快了将近20倍
set profiling=ON;
show profiles;
show profiles for query ID;
遍历100万条记录查询出一个salary的值，使用索引是0.0003s，不使用索引是0.01s，速度有很大的差别了
(root@localhost) [hellodb]>show profiles;
+----------+------------+-------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------+
| 1 | 0.00030300 | select * from testlog where salary=489141 |
| 2 | 0.00007425 | drop index idx_salary from testlog |
| 3 | 0.00987600 | drop index idx_salary on testlog |
| 4 | 0.01724175 | select * from testlog where salary=489141 |
+----------+------------+-------------------------------------------+
MySQL并发控制：锁
手动添加锁分为
读锁：对表/行加锁，别人能读，但是不能更改
写锁：对表/行加锁，别人不能读/写，加上排他性，自己掌握控制权
lock tables students lock_type(read/write)
lock tables students read
##加锁后的效果
update students set age=50 where stuid=1;
ERROR 1099 (HY000): Table 'students' was locked with a READ lock and can't be updated
##解锁后就可以写了
unlock tables;
update students set age=50 where stuid=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
写锁：自己独占表/行的控制权，别的用户不能读或者写
lock tables students write;
select / update /delete等更新操作都不行
##查看MySQL的进程正在干嘛
[hellodb]>show processlist;
+----+-----------------+-----------+---------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+---------+---------+------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 96 | Waiting on empty queue | NULL |
| 8 | root | localhost | hellodb | Query | 0 | init | show processlist |
+----+-----------------+-----------+---------+---------+------+------------------------+------------------+
kill +id ##杀掉对应的blocking进程
unlock tables;
select * from students where stuid=1;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 50 | M | 2 | 3 |
+-------+-------------+-----+--------+---------+-----------+
1 row in set (6.30 sec)
死锁问题：事务执行没完成/未提交/未回滚
user1在改user2的记录，由于innodb的特性，修改某一条记录的时候，会在这条row上面加锁，所以user1就无法修改，造成死锁；
user2修改user1正在改的数据，也会造成死锁
解决：MySQL内的机制会直接杀掉一个进程，unlock死锁
可以看到哪个user在搞
像这个使用了select * 查询表的，因为可能没做索引，所以需要遍历整张表，造成商城数据库性能达到瓶颈，已经不给操作了(读/写)--->应该是对表加了写锁--->死锁，别人读/写这张表都不行了，显示超时timeout之类的；
他是select *，不知道是对表级别的还是对行级别的，总之类似下单表之类的都无法执行读写了，即用户(相当于库的其他访问用户)无法下单，更新库的信息了，不能对表进行读/修改
show processlist;
kill +query id;
死锁案例
##如果加where条件，就是单挑记录；如果不加where，代表是整张表的修改--->不一样的
update stu set age=10;
update stu set age=10 where stuid=90;
##user1在执行这个事务，begin，还没commit，人走了
[hellodb]>begin;update students set age=10 where stuid=1;
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
commit;
##user1 commit后，才可以
##user2，修改stuid的值，发现一只卡住，就是死锁了，因为user1访问的事务还没提交，导致row级别加了写锁，导致无法读写操作，user2对于这条row的操作一直在等待
##报错:timeout
[hellodb]>update stu set age=20 where stuid=1;
[hellodb]>update stu set age=20 where stuid=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction ##尝试重新提交事务
[hellodb]>update stu set age=20 where stuid=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
##观察存储引擎
show innodb engine status;
##sleep 673代表一直在等待，干掉
##daemon代表守护进程
[hellodb]>show processlist;
+----+-----------------+-----------+---------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+---------+---------+------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 3065 | Waiting on empty queue | NULL |
| 11 | root | localhost | hellodb | Sleep | 673 | | NULL |
| 13 | root | localhost | hellodb | Query | 0 | init | show processlist |
+----+-----------------+-----------+---------+---------+------+------------------------+------------------+
3 rows in set (0.00 sec)
MySQL事务
事务：一组由SQL语句组成的整体，或者独立工作的单元---面试重点
##面试重点
事务的4个特性
A：原子性，整个事务中要么全部执行成功，要么全部失败回滚，唯一性，不可分割的步骤
C：一致性，必须遵循能量守恒定律，比如银行转账1000块，对方银行必定增加1000块，不存在转账后，1000块没了，双方平衡，双方加起来钱还是一致的
I：隔离性，即事务还未提交的时候，(还没显示结果)，别人看不到你提交了一半的事务的过程--->dirty data脏数据，没有具体落地的东西
D：持久型，一旦事务提交，修改永久保存在数据库中
事务的生命周期：
1.开始事务
2.执行其中的增删改查等SQL语句--->一般为DML语句
3.提交(commit)/回滚(rollback)
4.更新到DB中
具体执行过程，一定要分开执行事务才行，每一条都是一个语句来的，要用分号
begin; ##begin启动事务
insert into teachers(name,age,gender) VALUES('catyer',10,'M'); ##DML语句对表进行操作
COMMIT; ##提交
rollback; ##回滚事务，回滚自己这个用户的操作
##本地终端(操作用户)已经看到有记录生成了，别的终端还没看到
[hellodb]>select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 100 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | catyer | 10 | M |
| 6 | catyer | 10 | M |
| 7 | catyer11 | 11 | F |
+-----+---------------+-----+--------+
7 rows in set (0.00 sec)
1 Song Jiang 100 M
2 Zhang Sanfeng 94 M
3 Miejue Shitai 77 F
4 Lin Chaoying 93 F
5 catyer 10 M
6 catyer 10 M
##执行commit，别的用户可以看到了
1 Song Jiang 100 M
2 Zhang Sanfeng 94 M
3 Miejue Shitai 77 F
4 Lin Chaoying 93 F
5 catyer 10 M
6 catyer 10 M
7 catyer11 11 F
注意：在未提交或者回滚后，别人是看不到这个结果的，事务的隔离性，还未提交，例如我在网站上填写一个表单，还没点击提交
利用事务提高DML语句的执行速度
事务可以将待提交的SQL语句写入到MySQL的缓冲区中，类似git commit提交到暂存区，对比直接执行存储过程的速度
##批量化插入10w条记录，用了大概2s
[hellodb]>begin;call sp_testlog;commit;
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (2.35 sec)
Query OK, 0 rows affected (0.05 sec)
##普通，用了50多s
call sp_testlog;
(root@localhost) [hellodb]>call sp_testlog;
Query OK, 1 row affected (57.04 sec)
##一共有30w条记录，没问题
[hellodb]>select count(*) from testlog;
+----------+
| count(*) |
+----------+
| 300000 |
+----------+
1 row in set (0.03 sec)
rollback回滚：不能回滚DDL语句，即create/drop等操作，可以回滚DML
##delete from可以撤销，drop table不能撤销
(root@localhost) [hellodb]>begin;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [hellodb]>delete from teachers;
Query OK, 7 rows affected (0.00 sec)
(root@localhost) [hellodb]>select * from teachers;
Empty set (0.00 sec)
##对于delete操作，可以撤销的
[hellodb]>rollback;
Query OK, 0 rows affected (0.00 sec)
[hellodb]>select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 100 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | catyer | 10 | M |
| 6 | catyer | 10 | M |
| 7 | catyer11 | 11 | F |
+-----+---------------+-----+--------+
7 rows in set (0.00 sec)
执行了begin未提交的事务：脏数据dirty data
begin;
update stu set age=50 where stuid=1; --->脏数据
不同DB类型的事务提交：autocommit
MySQL：自动提交，commit
PG：自动提交
SQL server：自动提交
Ooracle：不会自动提交，执行了DML，再执行commit才能提交--->安全
select @@autocommit;
set autocommit=1
select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
##防止自动提交，全局设置非自动提交
set autocommit=0;
set global autocommit=0;
事务的隔离级别
MySQL支持四种事务隔离级别
读未提交read uncomimited：可读取到未提交的数据
读提交read commited：可读取提交的数据，但是未提交的数据不可读，导致每次读取的数据不一致(比如不同用户再执行，看到的都是自己执行的事务修改，别的人修改后的最终数据看不到)
幻读(可重复读)：永远看到是最初的样子，没经过修改的，别人提交多少次，我这个用户都看不到(MySQL默认事务隔离级别)
创新：读操作加读锁，写操作加写锁
##查询事务隔离级别，默认是幻读
[hellodb]>select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
1 row in set (0.00 sec)
##配置MySQL的事务隔离级别，读未提交
vim /etc/my.cnf
transaction-isolation=read-uncommited
transaction-isolation=read-commited ##读提交，commit后能看到
重启服务
##幻读级别：适用于备份场景
repeatable-read--->保证我在备份的时候，备份的是原始数据，不会在别人操作的时候提交事务，而修改了原数据
MySQL日志管理
事务日志：记录每次执行事务的操作
事务日志执行逻辑：
1.想要修改表，update操作，磁盘内先加载到内存中要执行
2.在内存中执行完update
3.结果先写入到事务日志，这一条操作确认是已经完成的
4.如无网络断电原因，在从内存中写入到磁盘，数据落盘
##事务日志文件是否会写满？
不会，达到一定的大小后会覆盖写
事务日志分类：
redo log：记录某数据块被修改后的值，DML语句，可以用于导入会系统，恢复未写入的数据
undo log：记录某数据块被修改前的值，用于rollback操作
查看事务日志，最大值：50M
[hellodb]>show variables like '%innodb_log%';
+------------------------------------+----------+
| Variable_name | Value |
+------------------------------------+----------+
| innodb_log_buffer_size | 16777216 |
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_log_spin_cpu_abs_lwm | 80 |
| innodb_log_spin_cpu_pct_hwm | 50 |
| innodb_log_wait_for_flush_spin_hwm | 400 |
| innodb_log_write_ahead_size | 8192 |
| innodb_log_writer_threads | ON |
+------------------------------------+----------+
11 rows in set (0.00 sec)
ls /data/mysql
[root@rocky mysql]#ll | grep innodb
drwxr-x---. 2 mysql mysql 4096 8月 28 15:13 #innodb_redo
drwxr-x---. 2 mysql mysql 187 8月 28 15:13 #innodb_temp
事务日志性能优化
执行DML语句时写入性能修改
默认的事务日志性能级别1，遵从ACID，但是并发的性能差
1.为默认值，日志缓冲区写入文件，事务提交后落盘
0.性能好
2.性能比0差一点，但是安全性高，建议高并发业务选择2
[hellodb]>select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
| 1 |
+----------------------------------+
1 row in set (0.00 sec)
标签：set,##,MySQL,mysql,root,select
From： https://www.cnblogs.com/catyer/p/16633317.html