Mysql

翔叔搞技术

已于 2023-04-12 21:43:47 修改

阅读量346

点赞数 1

文章标签： mysql 数据库

于 2023-03-22 17:56:13 首次发布

本文链接：https://blog.csdn.net/m0_63963588/article/details/129705132

版权

DBA简介

DBA工作内容
在这里插入图片描述 - 数据管理
- 增删改查

用户管理
- root，运维用户ops，程序连接用户(只读用户，读写用户)
集群管理
数据备份、恢复
- 逻辑备份
- 物理备份
- 冷备
- 热备
- 温备
- 全备
- 增量备份
- 差异备份
监控
- 进程，端口
- 集群状态
- 主从复制延时情况
- SQL读写速率
- slowlog

数据库管理系统种类

RDBMS：以多张二维表的方式来存储，又给多张表建立了一定的关系
NoSQL：非关系型数据库（redis,mongodb）
功能对比
特点对比
关系型数据库（RDBMS）的特点：
- 二维表
- 典型产品Oracle传统企业，MySQL互联网企业
- 数据存取是通过SQL（Structured QueryLanguage结构化查询语言）
- 最大特点数据安全性方面强（ACID）
非关系型数据库（NoSQL：Not only SQL）的特点：
- 不是否定关系型数据库，而是做关系型数据库的补充

部署mariadb（和mysql其实是一个东西）

安装mariadb-server

[root@node1 ~]# yum install mariadb mariadb-server -y

初始化

[root@node1 ~]# systemctl start mariadb.service
[root@node1 ~]# mysql_secure_installation
Enter current password for root (enter for none): 当前root用户密码为空，所以直接
敲回车
OK, successfully used password, moving on...
Set root password? [Y/n] y 设置root密码
New password:
Re-enter new password:
Password updated successfully!
Remove anonymous users? [Y/n] y 删除匿名用户
... Success!
Disallow root login remotely? [Y/n] y 禁止root远程登录
... Success!
Remove test database and access to it? [Y/n] y 删除test数据库
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reload privilege tables now? [Y/n] y 刷新授权表，让初始化生效
... Success!

启动并查看数据库状态

[root@node1 ~]# systemctl start mariadb.service
[root@node1 ~]# systemctl status mariadb.service
● mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor
preset: disabled)
Active: active (running) since 一 2020-09-14 22:38:17 CST; 10s ago
Process: 1634 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID
(code=exited, status=0/SUCCESS)
Process: 1550 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n
(code=exited, status=0/SUCCESS)
Main PID: 1633 (mysqld_safe)
......

登录数据库

[root@node1 ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
MariaDB [(none)]>

按exit可以退出

主配置文件（主要关注每一个参数的意义，参数的值可以根据实际场景修改）

[root@node1 ~]# vim /etc/my.cnf
[client] # 客户端基本配置
port = 3306 #客户端默认连接端口
socket = /tmp/mysql.sock #用于本地连接的socket套接字
[mysqld] # 服务端基本配置
port = 3306 # mysql监听端口
socket = /tmp/mysql.sock #为MySQL客户端程序和服务器之间的本地通讯指定一个套接
字文件
user = mariadb # mysql启动用户
basedir = /usr/local/mariadb # 安装目录
datadir = /data/mysql # 数据库数据文件存放目录
log_error = /data/mysql/mariadb.err #记录错误日志文件
pid-file = /data/mysql/mariadb.pid #pid所在的目录
skip-external-locking #不使用系统锁定，要使用myisamchk,必须关闭服务器
...

基础管理

密码相关的设置
管理员密码的设定

[root@localhost ~]# mysqladmin -uroot -p password 1
Enter password: <首次设置直接回车，第二次修改密码时此处输入旧密码>

管理员密码忘记

关闭数据库

[root@localhost ~]# systemctl stop mariadb.service

跳过授权登录
这一步很重要

mysqld_safe --skip-grant-tables --skip-networking &

登录mysql并修改root密码

[root@localhost ~]# mysql
MariaDB [(none)]> use mysql
MariaDB [mysql]> grant all on *.* to root@'localhost' identified by '777';
ERROR 1290 (HY000): The MariaDB server is running with the --skip-granttables option so it cannot execute this statement
MariaDB [mysql]> flush privileges;（刷新）
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> grant all on *.* to root@'localhost' identified by '777';
Query OK, 0 rows affected (0.00 sec)

重启数据库

[root@localhost ~]# pkill mysqld
[root@localhost ~]# systemctl restart mariadb

客户端工具

mysql可以用来连接数据库
- –user , -u
- –host , -h
- –password ,-p
- –port --protocol
- –database DATABASE , -D 登录指定数据库
mysqladmin(可以通过客户端直接执⾏服务器上的命令)

[root@localhost ~]# mysqladmin -uroot -p1 create hellodb
[root@localhost ~]# mysqladmin -uroot -p1 ping 检查服务端存活状态的
[root@localhost ~]# mysqladmin -uroot -p1 status 服务器详细运行状态
[root@localhost ~]# mysqladmin -uroot -p1 status 服务器状态 --sleep 2 --count
10 每两秒钟显示
⼀次服务器实时状态⼀共显示10次
[root@localhost ~]# mysqladmin -uroot -p1 extended-status 显示状态变量
[root@localhost ~]# mysqladmin -uroot -p1 variables 显示服务器变量
[root@localhost ~]# mysqladmin -uroot -p1 flush-privileges 数据库重读授权表，等
同于reload
[root@localhost ~]# mysqladmin -uroot -p1 flush-tables 关闭所有已经打开的表
[root@localhost ~]# mysqladmin -uroot -p1 flush-threds 重置线程池缓存
[root@localhost ~]# mysqladmin -uroot -p1 flush-status 重置⼤多数服务器状态变量
[root@localhost ~]# mysqladmin -uroot -p1 flush-logs ⽇志滚动。主要实现⼆进制和中
继⽇志滚动
[root@localhost ~]# mysqladmin -uroot -p1 flush-hosts 清楚主机内部信息
[root@localhost ~]# mysqladmin -uroot -p1 kill 杀死线程
[root@localhost ~]# mysqladmin -uroot -p1 refresh 相当于同时执⾏flush-hosts
flush-logs
[root@localhost ~]# mysqladmin -uroot -p1 shutdown 关闭服务器进程
[root@localhost ~]# mysqladmin -uroot -p1 version 服务器版本以及当前状态信息
[root@localhost ~]# mysqladmin -uroot -p1 start-slave 启动复制，启动从服务器复制
线程
[root@localhost ~]# mysqladmin -uroot -p1 stop-slave 关闭复制线程

在数据库的语句中可以加的命令（最常见是\G，将结果以竖排方式显示）
- \C：提前终⽌语句执⾏
- \d：修改默认结束符
- \g：⽆论语句结束符是什么，直接将此语句送⾄服务器执⾏
- \G：⽆论语句结束符是什么，直接将结果送⾄服务端执⾏，⽽且结果以竖排⽅式显示
- !：执⾏shell命令
- \W：显示语句结束后显示警告信息
- \w：不显示警告信息默认输出的格式是表的格式。登录数据库时可以添加参数 --html -H 或者–xml -X

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [(none)]> show databases\G
*************************** 1. row ***************************
Database: information_schema
*************************** 2. row ***************************
Database: mysql
*************************** 3. row ***************************
Database: performance_schema
3 rows in set (0.00 sec)

基础SQL语句

操作文件夹（库）

增：create database db1 charset utf8;
查：show databases;
改：alter database db1 charset latin1;
删除: drop database db1;

操作文件（表）

先切换到文件夹下：use db1
增：create table t1(id int,name char);
查：show tables;
改：alter table t1 modify name char(3);
alter table t1 change name name1 char(2);
删：drop table t1;

操作文件中的内容（记录）

增：insert into t1 values(1,'xwz'),(2,'cs'),(3,'tj');
查：select * from t1;
改：update t1 set name='sb' where id=2;
删：delete from t1 where id=1;
清空表：
delete from t1; #如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。
truncate table t1;数据量大，删除速度比上一条快，且直接从零开始，

MYSQL体系结构

C/S(客户端/服务端)模型介绍
在这里插入图片描述

连接方式

TCP/IP链接
适用于远程连接

[root@localhost ~]# mysql -uroot -p777 -h 192.168.80.128 -P3306 1

socket方式连接
适用于本地连接

[root@localhost ~]# mysql -uroot -p -S /tmp/mysql.sock

工作流程

在这里插入图片描述

首先，最上层的服务并不是MySQL独有的，大多数给予网络的客户端/服务器的工具或者服务都有
类似的架构。比如：连接处理、授权认证、安全等。
第二层的架构包括大多数的MySQL的核心服务。包括：查询解析、分析、优化、缓存以及所有的内
置函数（例如：日期、时间、数学和加密函数）。同时，所有的跨存储引擎的功能都在这一层实
现：存储过程、触发器、视图等。
第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。服务器通过API和存储引擎进
行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明化。存储
引擎API包含十几个底层函数，用于执行“开始一个事务”等操作。但存储引擎一般不会去解析
SQL（InnoDB会解析外键定义，因为其本身没有实现该功能），不同存储引擎之间也不会相互通
信，而只是简单的响应上层的服务器请求。
第四层包含了文件系统，所有的表结构和数据以及用户操作的日志最终还是以文件的形式存储在硬
盘上。

程序结构

把工作流程相同功能划分为一个结构里，可以形成MySQL的体系结构，如下图。值得一提的是MySQL存储引擎层使用的是插件式结构，可以根据场景不同选择不同的存储引擎。不同的存储引擎对于上层的应用程序或者是底层的文件系统是透明的。我们把MySQL的体系结构称为插件式存储引擎结构。
在这里插入图片描述 - 连接层功能
- 提供连接协议：TCP/IP、SOCKET
- 提供验证：用户、密码，IP,SOCKET
- 提供专用连接线程：接受用户SQL，返回结果

通过以下语句可以查看到连接线程基本情况
MariaDB [(none)]> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+-
---------+
| Id | User | Host | db | Command | Time | State | Info |
Progress |
+----+------+-----------+------+---------+------+-------+------------------+-
---------+
| 2 | root | localhost | NULL | Sleep | 6792 | | NULL |
0.000 |
| 4 | root | localhost | NULL | Query | 0 | NULL | show processlist |
0.000 |
+----+------+-----------+------+---------+------+-------+------------------+-
---------+

SQL层（重点）
- 接受上层传送的sql语句
- 语法验证模块：验证语句语法是否满足SQL_MODE
- 语义检查：判断sql语句的类型
  - DDL：数据定义语言
  - DCL ：数据控制语言
  - DML ：数据操作语言
  - DQL：数据查询语言
- 权限检查：用户对库表有没有权限
- 解析器：对语句执行前，进行预处理，生成解析树(执行计划)，说白了就是生成多种执行方案
- 优化器：根据解析器得出的多种执行计划，进行判断，选择最优的执行计划。代价模型：资源（CPU IO MEM）的耗损评估性能好坏
- 执行器：根据最优执行计划，执行SQL语句，产生执行结果
- 提供查询缓存（默认是没开启的），会使用redis tair替代查询缓存功能
- 提供日志记录（日志管理章节）：binlog，默认是没开启的。
存储引擎层
- 负责根据SQL层执行的结果，从磁盘上拿数据
- 将16进制的磁盘数据，交由SQL结构化化成表
- 连接层的专用线程返回给用户

逻辑结构

库：
库名
属性
用文件系统的目录来存储

[root@localhost mysql]# ll
总用量 28700
-rw-rw----. 1 mysql mysql 16384 9月 6 20:31 aria_log.00000001
-rw-rw----. 1 mysql mysql 52 9月 6 20:31 aria_log_control
-rw-rw----. 1 mysql mysql 18874368 9月 6 20:31 ibdata1
-rw-rw----. 1 mysql mysql 5242880 9月 6 20:31 ib_logfile0
-rw-rw----. 1 mysql mysql 5242880 9月 6 19:45 ib_logfile1
drwx------. 2 mysql mysql 4096 9月 6 19:45 mysql
srwxrwxrwx. 1 mysql mysql 0 9月 6 20:31 mysql.sock
drwx------. 2 mysql mysql 4096 9月 6 19:45 performance_schema
drwx------. 2 mysql mysql 6 9月 6 19:45 test
=====================================================================
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+

表
表名
属性
列，列名，列属性
数据行
用文件系统的文件来存储

[root@localhost mysql]# cd mysql/
[root@localhost mysql]# ll
总用量 1000
-rw-rw----. 1 mysql mysql 8820 9月 6 19:45 columns_priv.frm
-rw-rw----. 1 mysql mysql 0 9月 6 19:45 columns_priv.MYD
-rw-rw----. 1 mysql mysql 4096 9月 6 19:45 columns_priv.MYI
-rw-rw----. 1 mysql mysql 9582 9月 6 19:45 db.frm
-rw-rw----. 1 mysql mysql 880 9月 6 19:45 db.MYD
-rw-rw----. 1 mysql mysql 5120 9月 6 19:45 db.MYI
-rw-rw----. 1 mysql mysql 10239 9月 6 19:45 event.frm
-rw-rw----. 1 mysql mysql 0 9月 6 19:45 event.MYD
......
-rw-rw----. 1 mysql mysql 10630 9月 6 19:45 user.frm
-rw-rw----. 1 mysql mysql 544 9月 6 20:22 user.MYD
-rw-rw----. 1 mysql mysql 2048 9月 6 20:30 user.MYI
=====================================================================
MariaDB [(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
MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
......
| user |
+---------------------------+

存储引擎（重点）

功能：
数据读写
数据安全
提高性能
热备份
自动故障恢复
高可用方面支持

种类：

InnoDB（对数据安全性更高的场景）
- ⽀持事务，主要⾯向在线事务（OLTP）处理的应⽤；
- ⾏锁设计、⽀持外键、⽀持类似于Oracle的⾮锁定读；
- 从5.5.8版本开始，InnoDB存储引擎是默认的存储引擎；
- 将数据放在⼀个逻辑的表空间中，这个表空间就像⿊盒⼀样由InnoDB存储引擎⾃身进⾏管理；
MyISAM（适合去做查询比较多的场景）
- 不⽀持事务、表锁设计、⽀持全⽂索引，主要⾯向OLAP数据库应⽤；
- 它的缓冲池只缓存索引⽂件，不缓存数据⽂件；
- MyISAM存储引擎表由MYD和MYI组成，MYD⽤来存放数据⽂件，MYI⽤来存放索引⽂件。
MEMORY
ARCHIVE
FEDERATED
EXAMPLE
BLACKHOLE
CSV

存储引擎相关操作

查看Mysql支持的存储引擎

MariaDB [(none)]> show engines\G;
*************************** 1. row ***************************
Engine: InnoDB
Support: DEFAULT
.....
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
.....
*************************** 3. row ***************************
Engine: MyISAM
Support: YES
.....
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
.....
*************************** 5. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
.....
*************************** 6. row ***************************
Engine: CSV
Support: YES
.....
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
.....
*************************** 8. row ***************************
Engine: MEMORY
Support: YES
.....
*************************** 9. row ***************************
Engine: FEDERATED
Support: YES
.....
*************************** 10. row ***************************
Engine: Aria
Support: YES

查看默认存储引擎

MariaDB [(none)]> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB |
+--------------------------+
1 row in set (0.00 sec)

修改存储引擎

#在建表时指定
MariaDB [(none)]> create table ai(id bigint(12),name varchar(200))
ENGINE=MyISAM;
MariaDB [(none)]> create table country(id int(4),cname varchar(50))
ENGINE=InnoDB;
##也可以使用alter table语句，修改一个已经存在的表的存储引擎。
MariaDB [(none)]> alter table ai engine = innodb;
#在配置文件中指定
[root@node1 ~]# cat /etc/my.cnf
[mysqld]
default-storage-engine=INNODB

InnoDB与MyISAM存储引擎区别（面试题）
- InnoDB支持事务，支持行级别锁，支持热备，支持自动故障恢复，MVCC
- MyISAM 不支持事务，支持表级锁，支持温备份，不支持自动故障恢复和MVCC

SQL语句

用户管理

用户名形式，用户名@‘白名单’

eagle@'192.168.80.%'
eagle@'%'
eagle@'192.168.80.1'
eagle@'localhost'
eagle@'server1'
eagle@'192.168.80.5%'
eagle@'192.168.80.0/255.255.254.0'

创建用户

MariaDB [(none)]> create user eagle@192.168.33.128 identified by '1';
Query OK, 0 rows affected (0
.00 sec)

查询用户

MariaDB [(none)]> select user,host,password from mysql.user;
+-------+----------------+-------------------------------------------+
| user | host | password |
+-------+----------------+-------------------------------------------+
| root | localhost | *E8D868B7DA46FC9F996DC761C1AE01754A4447D5 |
| root | 127.0.0.1 | *7FB1F1B8AD1B4CFD578E76ABC1B6ADFF70D04FA0 |
| root | ::1 | *7FB1F1B8AD1B4CFD578E76ABC1B6ADFF70D04FA0 |
| eagle | 192.168.33.128 | *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
+-------+----------------+-------------------------------------------+

修改用户密码

MariaDB [(none)]> use mysql
MariaDB [mysql]> update user set password=password('2') where user='eagle'
and host='192.168.33.128';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

删除用户

MariaDB [(none)]> drop user eagle@192.168.33.128;
Query OK, 0 rows affected (0.00 sec)

权限管理

授权

MariaDB [(none)]> grant all on *.* to eagle@'%' identified by '123';

常用权限介绍

ALL:
SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE,
REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES,
LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW,
SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE
TABLESPACE
ALL : 以上所有权限，一般是普通管理员拥有的
with grant option：超级管理员才具备的，给别的用户授权的功能

权限作用范围

*.* # 针对所有数据库里的所有表
db1.* # 针对db1数据库里的所有表
db1.tb1 # 针对db1数据库里的表tb1

查看权限

MariaDB [(none)]> show grants for root@'localhost'\G;
*************************** 1. row ***************************
Grants for root@localhost: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost'
IDENTIFIED BY PASSWORD '*E8D868B7DA46FC9F996DC761C1AE01754A4447D5' WITH GRANT
OPTION
*************************** 2. row ***************************
Grants for root@localhost: GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH
GRANT OPTION
2 rows in set (0.00 sec)

回收权限

MariaDB [(none)]> revoke delete on *.* from eagle@'%'；

案例1，在本地Windows主机上使用navicat登录到虚拟机里的MySQL，通常会由于没有授权而被拒绝

MariaDB [(none)]> grant all on *.* to root@'192.168.33.%' identified by'777';

为开发人员开用户需要考虑哪些问题
- 需要哪些权限
- 针对哪些库、表
- 从什么地址进行连接
- 密码有什么要求
在MySQL8.0中grant命令添加新特性

建用户和授权分开了
grant 不再支持自动创建用户了，不支持改密码
授权之前，必须要提前创建用户。

表管理

创建表的语法

#语法：
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);
#注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的

建表

mysql> create table staff_info (
id int,name varchar(50),
age int(3),
sex enum('male','female'),
phone bigint(11),
job varchar(11)
);
Query OK, 0 rows affected (0.02 sec)

插入数据

mysql> insert into staff_info (id,name,age,sex,phone,job) values
(1,'xwz',83,'female',13651054608,'IT');
Query OK, 1 row affected (0.00 sec)
mysql> insert into staff_info values
(2,'cs',26,'male',13304320533,'Teacher');
Query OK, 1 row affected (0.00 sec)
mysql> insert into staff_info values (3,'tj',25,'male',13332353222,'IT'),
(4,'fcy',40,'male',13332353333,'IT');
Query OK, 2 rows affected (0.00 sec)

查看表结构

mysql> describe staff_info;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
# 更全面的表定义信息
mysql> show create table staff_info\G;
*************************** 1. row ***************************
Table: staff_info
Create Table: CREATE TABLE `staff_info` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`age` int(3) DEFAULT NULL,
`sex` enum('male','female') DEFAULT NULL,
`phone` bigint(11) DEFAULT NULL,
`job` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

修改表结构的语法

1. 修改表名
ALTER TABLE 表名
RENAME 新表名;
2. 增加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…],
ADD 字段名 数据类型 [完整性约束条件…];
3. 删除字段
ALTER TABLE 表名
DROP 字段名;
4. 修改字段
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
5.修改字段排列顺序/在增加的时候指定字段位置
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
ALTER TABLE 表名
CHANGE 字段名 旧字段名 新字段名 新数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…] AFTER 字段名;

修改表的各个实例

mysql> desc staff_info;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
# 表重命名
mysql> alter table staff_info rename staff;
Query OK, 0 rows affected (0.00 sec)
mysql> desc staff;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
# 删除sex列
mysql> alter table staff drop sex;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc staff;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
# 添加列
mysql> alter table staff add sex enum('male','female');
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 修改id的宽度
mysql> alter table staff modify id int(4);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc staff;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
# 修改name列的字段名
mysql> alter table staff change name sname varchar(20);
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> desc staff;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| sname | varchar(20) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
# 修改sex列的位置
mysql> alter table staff modify sex enum('male','female') after sname;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc staff;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| sname | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
# 创建自增id主键
mysql> alter table staff modify id int(4) primary key auto_increment;
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> desc staff;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| sname | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
# 删除主键，可以看到删除一个自增主键会报错
mysql> alter table staff drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto
column and it
must be defined as a key
# 需要先去掉主键的自增约束，然后再删除主键约束
mysql> alter table staff modify id int(11);
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> desc staff;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| sname | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> alter table staff drop primary key;
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0
# 添加联合主键
mysql> alter table staff add primary key (sname,age);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 删除主键
mysql> alter table staff drop primary key;
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
# 创建主键id
mysql> alter table staff add primary key (id);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc staff;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| sname | varchar(20) | NO | | | |
| sex | enum('male','female') | YES | | NULL | |
| age | int(3) | NO | | 0 | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
# 为主键添加自增属性
mysql> alter table staff modify id int(4) auto_increment;
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> desc staff;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| sname | varchar(20) | NO | | | |
| sex | enum('male','female') | YES | | NULL | |
| age | int(3) | NO | | 0 | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

删除表语法

DROP TABLE 表名;

表的约束规则

为了防止不规范的数据存放在数据库中，DBMS自动安装一定的约束条件对数据进行检测，确保数据库中存储的数据正确有效。
分类：
- NOT NULL：非空约束
- Default：当表中某一列有重复内容，避免频繁操作，可以为其设置默认值
- UNIQUE：唯一约束
- PRIMARY KEY:主键，唯一标识一条记录
- FOREIGN KEY:外键，从属于主表的一条记录

记录操作

插入数据

1. 插入完整数据（顺序插入）
语法一：
INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);
语法二：
INSERT INTO 表名 VALUES (值1,值2,值3…值n);
2. 指定字段插入数据
语法：
INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);
3. 插入多条记录
语法：
INSERT INTO 表名 VALUES
(值1,值2,值3…值n),
(值1,值2,值3…值n),
(值1,值2,值3…值n);
4. 插入查询结果
语法：
INSERT INTO 表名(字段1,字段2,字段3…字段n)
SELECT (字段1,字段2,字段3…字段n) FROM 表2
WHERE …;

更新数据

语法：
UPDATE 表名 SET
字段1=值1,
字段2=值2,
WHERE CONDITION;
示例：
UPDATE mysql.user SET password=password(‘123’)
where user=’root’ and host=’localhost’;

删除数据

语法：
DELETE FROM 表名
WHERE CONITION;
示例：
DELETE FROM mysql.user
WHERE password=’’;

truncate和delete区别：
truncate会将之前自增的字段清空，而delete不会将之前自增的字段清空，会继续延续。

PRIMARY KEY
- 主键为了保证表中的每一条数据的该字段都是表格中的唯一值
- 主键必须唯一，主键值非空；
- 可以是单一字段，也可以是多字段组合单字段主键：

查询语句

语法

SELECT DISTINCT 字段1,字段2... FROM 表名
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数

优先级

from
where
group by(将查询的结果按照一定条件去分组)
select
distinct（去重，将重复的结果只显示一个）
having（分组过后进行过滤）
order by（按照一定的条件进行排序）
limit
1.找到表:from
2.拿着where指定的约束条件，去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4.执行select（去重）
5.将分组的结果进行having过滤
6.将结果按条件排序：order by
7.限制结果的显示条数

从其他数据库中搜索出内容
MariaDB [mysql]> select * from db1.s_info;
+------+------+------+------+---------+------+
| id   | name | age  | sex  | dianhua | job  |
+------+------+------+------+---------+------+
|    1 | xwz  |   83 | NULL |    NULL | NULL |
+------+------+------+------+---------+------+

简单查询

company.employee
员工id id int
姓名 emp_name varchar
性别 sex enum
年龄 age int
入职日期 hire_date date
岗位 post varchar
职位描述 post_comment varchar
薪水 salary double
办公室 office int
部门编号 depart_id int
#建表
create table employee(
id int not null unique auto_increment,
emp_name varchar(20) not null,
sex enum('male','female') not null default 'male',
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int,
depart_id int
);
#插入数据
insert into
employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
('dapeng','male',18,'20170301','boss',7300.33,401,1),
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),
('歪歪','female',48,'20150311','sale',3000.13,402,2),
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),
('张野','male',28,'20160311','operation',10000.13,403,3),
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);
#简单查询
SELECT * FROM employee;
SELECT emp_name,salary FROM employee;
#避免重复
SELECT DISTINCT post FROM employee;
#四则运算查询
SELECT emp_name, salary*12 AS Annual_salary FROM employee;

where约束

1. 比较运算符：> < >= <= <> !=
2. between 80 and 100 值在80到100之间
3. in(80,90,100) 值是80或90或100
4. like 'e%'
通配符可以是%或_，
%表示任意多字符
_表示一个字符
5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
#1:单条件查询
SELECT emp_name FROM employee
WHERE post='sale';
#2:多条件查询
SELECT emp_name,salary FROM employee
WHERE post='teacher' AND salary>10000;
#3:关键字BETWEEN AND
SELECT emp_name,salary FROM employee
WHERE salary BETWEEN 10000 AND 20000;
SELECT emp_name,salary FROM employee
WHERE salary NOT BETWEEN 10000 AND 20000;
#4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)
SELECT emp_name,post_comment FROM employee
WHERE post_comment IS NULL;
SELECT emp_name,post_comment FROM employee
WHERE post_comment IS NOT NULL;
SELECT emp_name,post_comment FROM employee
WHERE post_comment=''; 注意''是空字符串，不是null
ps：
执行
update employee set post_comment='' where id=2;
再用上条查看，就会有结果了
#5:关键字IN集合查询，可以理解为散列值查询，精准查询。
SELECT emp_name,salary FROM employee
WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;
SELECT emp_name,salary FROM employee
WHERE salary IN (3000,3500,4000,9000) ;
SELECT emp_name,salary FROM employee
WHERE salary NOT IN (3000,3500,4000,9000) ;
#6:关键字LIKE模糊查询
通配符’%’，表示匹配eg开头后面跟上所有。
SELECT * FROM employee
WHERE emp_name LIKE 'eg%';
通配符’_’，下划线只精准单个字符匹配。
SELECT * FROM employee
WHERE emp_name LIKE 'al__';

group by（分组）

单独使用GROUP BY关键字分组
SELECT post FROM employee GROUP BY post;
注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，
需要借
助函数
GROUP BY关键字和GROUP_CONCAT()函数一起使用
SELECT post,GROUP_CONCAT(emp_name) FROM employee GROUP BY post;#按照岗位分组，
并查看组内成
员名
SELECT post,GROUP_CONCAT(emp_name) as emp_members FROM employee GROUP BY
post;
GROUP BY与聚合函数一起使用
select post,count(id) as count from employee group by post;#按照岗位分组，并查
看每个组有多
少人
PS:
1.如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义
2.多条记录之间的某个字段值相同，该字段通常用来作为分组的依据

聚合函数

#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组
示例：
SELECT COUNT(*) FROM employee;
SELECT COUNT(*) FROM employee WHERE depart_id=1;
SELECT MAX(salary) FROM employee;
SELECT MIN(salary) FROM employee;
SELECT AVG(salary) FROM employee;
SELECT SUM(salary) FROM employee;
SELECT SUM(salary) FROM employee WHERE depart_id=3;

having过滤

！！！执行优先级从高到低：where > group by > having
#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字
段,可以使用聚合
函数
mysql> select @@sql_mode;
+--------------------+
| @@sql_mode |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+
row in set (0.00 sec)
mysql> select * from emp where salary > 100000;
+----+------+------+-----+------------+---------+--------------+------------
+--------+------
-----+
| id | emp_name | sex | age | hire_date | post | post_comment | salary |
office |
depart_id |
+----+------+------+-----+------------+---------+--------------+------------
+--------+------
-----+
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 |
1 |
+----+------+------+-----+------------+---------+--------------+------------
+--------+------
-----+
row in set (0.00 sec)
mysql> select post,group_concat(emp_name) from emp group by post having
salary > 10000;#错having是在select之后结果进行筛选，此时select选项并没有salary
误，分组后无法直接取到salary字段
ERROR 1054 (42S22): Unknown column 'salary' in 'having clause'
mysql> select post,group_concat(emp_name) from emp group by post having
avg(salary) > 10000;
+-----------+-------------------------------------------------------+
| post | group_concat(emp_name) |
+-----------+-------------------------------------------------------+
| operation | 程咬铁,程咬铜,程咬银,程咬金,张野 |
| teacher | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex |

order by

按单列排序
SELECT * FROM employee ORDER BY salary;
SELECT * FROM employee ORDER BY salary ASC;升序
SELECT * FROM employee ORDER BY salary DESC;逆序
按多列排序:先按照age排序，如果年纪相同，则按照薪资排序
SELECT * from employee
ORDER BY age,
salary DESC;

limit

示例：
SELECT * FROM employee ORDER BY salary DESC
LIMIT 3; #默认初始位置为0
SELECT * FROM employee ORDER BY salary DESC
LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条
SELECT * FROM employee ORDER BY salary DESC
LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条

正则表达式

SELECT * FROM employee WHERE emp_name REGEXP '^ale';
SELECT * FROM employee WHERE emp_name REGEXP 'on$';
SELECT * FROM employee WHERE emp_name REGEXP 'm{2}';
小结：对字符串匹配的方式
WHERE emp_name = 'egon';
WHERE emp_name LIKE 'yua%';
WHERE emp_name REGEXP 'on$';

多表连接查询

建表和准备数据

#建表
create table department(
id int,
name varchar(20)
);
create table employee2(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');
insert into employee2(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)

语法

#重点：外链接语法
SELECT 字段列表
FROM 表1 INNER|LEFT|RIGHT JOIN 表2
ON 表1.字段 = 表2.字段;

交叉连接（会将每张表的一行都结合在一起）

MariaDB [test]> select * from employee2,department;

内连接，交叉运算后只保留两张表都存在的记录

MariaDB [test]> select
employee2.id,employee2.name,employee2.age,employee2.sex,department.name from
employee2 inner
join department on employee2.dep_id=department.id;
MariaDB [test]> select
employee2.id,employee2.name,employee2.age,employee2.sex,department.name from
employee2,department where employee2.dep_id=department.id;
+----+-----------+------+--------+--------------+
| id | name | age | sex | name |
+----+-----------+------+--------+--------------+
| 1 | egon | 18 | male | 技术 |
| 2 | alex | 48 | female | 人力资源 |
| 3 | wupeiqi | 38 | male | 人力资源 |
| 4 | yuanhao | 28 | female | 销售 |
| 5 | liwenzhou | 18 | male | 技术 |
+----+-----------+------+--------+--------------+

左连接，交叉运算后优先显示左表全部记录

MariaDB [test]> select employee2.id,employee2.name,department.name as
depart_name from
employee2 left join department on employee2.dep_id=department.id;
+----+------------+--------------+
| id | name | depart_name |
+----+------------+--------------+
| 1 | egon | 技术 |
| 5 | liwenzhou | 技术 |
| 2 | alex | 人力资源 |
| 3 | wupeiqi | 人力资源 |
| 4 | yuanhao | 销售 |
| 6 | jingliyang | NULL |
+----+------------+--------------+

右连接，交叉运算后优先显示右表全部记录

MariaDB [test]> select employee2.id,employee2.name,department.name as
depart_name from
employee2 right join department on employee2.dep_id=department.id;
+------+-----------+--------------+
| id | name | depart_name |
+------+-----------+--------------+
| 1 | egon | 技术 |
| 2 | alex | 人力资源 |
| 3 | wupeiqi | 人力资源 |
| 4 | yuanhao | 销售 |
| 5 | liwenzhou | 技术 |
| NULL | NULL | 运营 |
+------+-----------+--------------+

全外连接，显示左右两个表全部记录

MariaDB [test]> select * from employee2 left join department on
employee2.dep_id =
department.id
-> union
-> select * from employee2 right join department on employee2.dep_id =
department.id;
+------+------------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+------------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 6 | jingliyang | female | 18 | 204 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 203 | 运营 |
+------+------------+--------+------+--------+------+--------------+
#注意 union与union all的区别：union会去掉相同的纪录

示例1：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门

select employee.name,department.name from employee inner join department
on employee.dep_id = department.id
where age > 25;

#示例2：以内连接的方式查询employee和department表，并且以age字段的升序方式显示

select employee.id,employee.name,employee.age,department.name from
employee,department
where employee.dep_id = department.id
and age > 25
order by age asc;

子查询（可以理解为嵌套查询

解释

#1：子查询是将一个查询语句嵌套在另一个查询语句中。
#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#4：还可以包含比较运算符：= 、 !=、> 、<等

带IN关键字的子查询

#查询平均年龄在25岁以上的部门名
select id,name from department
where id in
(select dep_id from employee group by dep_id having avg(age) > 25);
#查看技术部员工姓名
select name from employee
where dep_id in
(select id from department where name='技术');
#查看不足1人的部门名(子查询得到的是有人的部门id)
select name from department where id not in (select distinct dep_id from
employee);

带比较运算符的子查询

#比较运算符：=、!=、>、>=、<、<=、<>
#查询大于所有人平均年龄的员工名与年龄
mysql> select name,age from emp where age > (select avg(age) from emp);
+---------+------+
| name | age |
+---------+------+
| alex | 48 |
| wupeiqi | 38 |
+---------+------+
2 rows in set (0.00 sec)
#查询大于部门内平均年龄的员工名、年龄
select t1.name,t1.age from emp t1
inner join
(select dep_id,avg(age) avg_age from emp group by dep_id) t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age;

带EXISTS关键字的子查询

#department表中存在dept_id=200，Ture
mysql> select * from employee
-> where exists
-> (select id from department where id=200);
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+
#department表中存在dept_id=205，False
mysql> select * from employee
-> where exists
-> (select id from department where id=205);

# ```数据类型
![在这里插入图片描述](https://img-blog.csdnimg.cn/f41d68e9993c4136ac85d784ed51ce90.png)
- INT整数示范

```c
# 创建表一个是默认宽度的int，一个是指定宽度的int(5)
mysql> create table t1 (id1 int,id2 int(5));
Query OK, 0 rows affected (0.02 sec)
# 像t1中插入数据1，1
mysql> insert into t1 values (1,1);
Query OK, 1 row affected (0.01 sec)
# 可以看出结果上并没有异常
mysql> select * from t1;
+------+------+
| id1 | id2 |
+------+------+
| 1 | 1 |
+------+------+
row in set (0.00 sec)
# 那么当我们插入了比宽度更大的值，会不会发生报错呢？
mysql> insert into t1 values (111111,111111);
Query OK, 1 row affected (0.00 sec)
# 答案是否定的，id2仍然显示了正确的数值，没有受到宽度限制的影响
mysql> select * from t1;
+------------+--------+
| id1 | id2 |
+------------+--------+
| 0000000001 | 00001 |
| 0000111111 | 111111 |
+------------+--------+
rows in set (0.00 sec)
# 修改id1字段 给字段添加一个unsigned表示无符号
mysql> alter table t1 modify id1 int unsigned;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t1;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id1 | int(10) unsigned | YES | | NULL | |
| id2 | int(5) | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
rows in set (0.01 sec)
# 当给id1添加的数据大于214748364时，可以顺利插入
mysql> insert into t1 values (2147483648,2147483647);
Query OK, 1 row affected (0.00 sec)
# 当给id2添加的数据大于214748364时，会报错
mysql> insert into t1 values (2147483647,2147483648);
ERROR 1264 (22003): Out of range value for column 'id2' at row 1

小数示范

# 创建表的三个字段分别为float，double和decimal参数表示一共显示5位，小数部分占2位
mysql> create table t2 (id1 float(5,2),id2 double(5,2),id3 decimal(5,2));
Query OK, 0 rows affected (0.02 sec)
# 向表中插入1.23，结果正常
mysql> insert into t2 values (1.23,1.23,1.23);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t2;
+------+------+------+
| id1 | id2 | id3 |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
+------+------+------+
row in set (0.00 sec)
# 向表中插入1.234，会发现4都被截断了
mysql> insert into t2 values (1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from t2;
+------+------+------+
| id1 | id2 | id3 |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
+------+------+------+
rows in set (0.00 sec)
# 向表中插入1.235发现数据虽然被截断，但是遵循了四舍五入的规则
mysql> insert into t2 values (1.235,1.235,1.235);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from t2;
+------+------+------+
| id1 | id2 | id3 |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
| 1.24 | 1.24 | 1.24 |
+------+------+------+
rows in set (0.00 sec)
# 建新表去掉参数约束
mysql> create table t3 (id1 float,id2 double,id3 decimal);
Query OK, 0 rows affected (0.02 sec)
# 分别插入1.234
mysql> insert into t3 values (1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.00 sec)
# 发现decimal默认值是(10,0)的整数
mysql> select * from t3;
+-------+-------+------+
| id1 | id2 | id3 |
+-------+-------+------+
| 1.234 | 1.234 | 1 |
+-------+-------+------+
row in set (0.00 sec)
# 当对小数位没有约束的时候，输入超长的小数，会发现float和double的区别
mysql> insert into t3 values
(1.2355555555555555555,1.2355555555555555555,1.2355555555555555555555);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from t3;
+---------+--------------------+------+
| id1 | id2 | id3 |
+---------+--------------------+------+
| 1.234 | 1.234 | 1 |
| 1.23556 | 1.2355555555555555 | 1 |
+---------+--------------------+------+
rows in set (0.00 sec)

日期时间类型

在这里插入图片描述 - data/time/datatime示例

mysql> create table t4 (d date,t time,dt datetime);
Query OK, 0 rows affected (0.02 sec)
mysql> desc t4;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d | date | YES | | NULL | |
| t | time | YES | | NULL | |
| dt | datetime | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
rows in set (0.01 sec)
mysql> insert into t4 values (now(),now(),now());
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from t4;
+------------+----------+---------------------+
| d | t | dt |
+------------+----------+---------------------+
| 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 |
+------------+----------+---------------------+
row in set (0.00 sec)
mysql> insert into t4 values (null,null,null);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t4;
+------------+----------+---------------------+
| d | t | dt |
+------------+----------+---------------------+
| 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 |
| NULL | NULL | NULL |
+------------+----------+---------------------+
rows in set (0.00 sec)

timestamp示例

mysql> create table t5 (id1 timestamp);
Query OK, 0 rows affected (0.02 sec)
mysql> desc t5;
+-------+-----------+------+-----+-------------------+-----------------------------
+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------
+
| id1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------
+
row in set (0.00 sec)
# 插入数据null，会自动插入当前时间的时间
mysql> insert into t5 values (null);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t5;
+---------------------+
| id1 |
+---------------------+
| 2018-09-21 14:56:50 |
+---------------------+
row in set (0.00 sec)
#添加一列 默认值是'0000-00-00 00:00:00'
mysql> alter table t5 add id2 timestamp;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t5 \G;
*************************** 1. row ***************************
Table: t5
Create Table: CREATE TABLE `t5` (
`id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
`id2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
row in set (0.00 sec)
ERROR:
No query specified
# 手动修改新的列默认值为当前时间
mysql> alter table t5 modify id2 timestamp default current_timestamp;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t5 \G;
*************************** 1. row ***************************
Table: t5
Create Table: CREATE TABLE `t5` (
`id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
`id2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8
row in set (0.00 sec)
ERROR:
No query specified
mysql> insert into t5 values (null,null);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t5;
+---------------------+---------------------+
| id1 | id2 |
+---------------------+---------------------+
| 2018-09-21 14:56:50 | 0000-00-00 00:00:00 |
| 2018-09-21 14:59:31 | 2018-09-21 14:59:31 |
+---------------------+---------------------+
rows in set (0.00 sec)

timestamp示例2

mysql> create table t6 (t1 timestamp);
Query OK, 0 rows affected (0.02 sec)
mysql> desc t6;
+-------+-----------+------+-----+-------------------+-----------------------------
+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------
+
| t1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------
+
row in set (0.01 sec)
mysql> insert into t6 values (19700101080001);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t6;
+---------------------+
| t1 |
+---------------------+
| 1970-01-01 08:00:01 |
+---------------------+
row in set (0.00 sec)
# timestamp时间的下限是19700101080001
mysql> insert into t6 values (19700101080000);
ERROR 1292 (22007): Incorrect datetime value: '19700101080000' for column
't1' at row 1
mysql> insert into t6 values ('2038-01-19 11:14:07');
Query OK, 1 row affected (0.00 sec)
# timestamp时间的上限是2038-01-19 11:14:07
mysql> insert into t6 values ('2038-01-19 11:14:08');
ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 11:14:08' for
column 't1' at row 1

year示例

mysql> create table t7 (y year);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t7 values (2018);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t7;
+------+
| y |
+------+
| 2018 |
+------+
row in set (0.00 sec)

字符串类型

在这里插入图片描述

字符串示例

mysql> create table t9 (v varchar(4),c char(4));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t9 values ('ab ','ab ');
Query OK, 1 row affected (0.00 sec)
# 在检索的时候char数据类型会去掉空格
mysql> select * from t9;
+------+------+
| v | c |
+------+------+
| ab | ab |
+------+------+
row in set (0.00 sec)
# 来看看对查询结果计算的长度
mysql> select length(v),length(c) from t9;
+-----------+-----------+
| length(v) | length(c) |
+-----------+-----------+
| 4 | 2 |
+-----------+-----------+
row in set (0.00 sec)
# 给结果拼上一个加号会更清楚
mysql> select concat(v,'+'),concat(c,'+') from t9;
+---------------+---------------+
| concat(v,'+') | concat(c,'+') |
+---------------+---------------+
| ab + | ab+ |
+---------------+---------------+
row in set (0.00 sec)
# 当存储的长度超出定义的长度，会截断
mysql> insert into t9 values ('abcd ','abcd ');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from t9;
+------+------+
| v | c |
+------+------+
| ab | ab |
| abcd | abcd |
+------+------+
rows in set (0.00 sec)

ENUM和SET类型

ENUM中文名称叫枚举类型，它的值范围需要在创建表时通过枚举方式显示。ENUM只允许从值集合中选取单个值，而不能一次取多个值。
SET和ENUM非常相似，也是一个字符串对象，里面可以包含0-64个成员。根据成员的不同，存储上也有所不同。 set类型可以允许值集合中任意选择1或多个元素进行组合。对超出范围的内容将不允许注入，而对重复的值将进行自动去重。
示例

mysql> create table t10 (name char(20),gender enum('female','male'));
Query OK, 0 rows affected (0.01 sec)
# 选择enum('female','male')中的一项作为gender的值，可以正常插入
mysql> insert into t10 values ('nezha','male');
Query OK, 1 row affected (0.00 sec)
# 不能同时插入'male,female'两个值，也不能插入不属于'male,female'的值
mysql> insert into t10 values ('nezha','male,female');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
mysql> create table t11 (name char(20),hobby set('抽烟','喝酒','烫头','翻车'));
Query OK, 0 rows affected (0.01 sec)
# 可以任意选择set('抽烟','喝酒','烫头','翻车')中的项，并自带去重功能
mysql> insert into t11 values ('yuan','烫头,喝酒,烫头');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t11;
+------+---------------+
| name | hobby |
+------+---------------+
| yuan | 喝酒,烫头 |
+------+---------------+
row in set (0.00 sec)
# 不能选择不属于set('抽烟','喝酒','烫头','翻车')中的项，
mysql> insert into t11 values ('alex','烫头,翻车,看妹子');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1

索引管理

索引介绍

功能

1. 索引的功能就是加速查找
2. mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束
的功能

常用索引

普通索引INDEX：加速查找
唯一索引：
-主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）
-唯一索引UNIQUE:加速查找+约束（不能重复）
联合索引：
-PRIMARY KEY(id,name):联合主键索引
-UNIQUE(id,name):联合唯一索引
-INDEX(id,name):联合普通索引

索引类型

#我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
#不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 #方法一：创建表
时
CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[索引名] (字段名[(长度)] [ASC |DESC])
);
#方法二：CREATE在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC |DESC]) ;
#方法三：ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名 (字段名[(长度)] [ASC |DESC]) ;
#删除索引：DROP INDEX 索引名 ON 表名字; 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

索引使用

创建/删除索引语法

#方法一：创建表时
CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[索引名] (字段名[(长度)] [ASC |DESC])
);
#方法二：CREATE在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC |DESC]) ;
#方法三：ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名 (字段名[(长度)] [ASC |DESC]) ;
#删除索引：DROP INDEX 索引名 ON 表名字;

创建/删除索引示例

#方式一
create table t1(
id int,
name char,
age int,
sex enum('male','female'),
unique key uni_id(id),
index ix_name(name) #index没有key
);
create table t1(
id int,
name char,
age int,
sex enum('male','female'),
unique key uni_id(id),
index(name) #index没有key
);
#方式二
create index ix_age on t1(age);
#方式三
alter table t1 add index ix_sex(sex);
alter table t1 add index(sex);
#查看
mysql> show create table t1;
| t1 | CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` char(1) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` enum('male','female') DEFAULT NULL,
UNIQUE KEY `uni_id` (`id`),
KEY `ix_name` (`name`),
KEY `ix_age` (`age`),
KEY `ix_sex` (`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

测试索引

#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);
#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert2()
BEGIN
declare i int default 1;
while(i<3000000)do
insert into s1 values(i,'eva','female',concat('eva',i,'@eagleslab'));
set i=i+1;
end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号
#3. 查看存储过程
show create procedure auto_insert2\G
#4. 调用存储过程
call auto_insert2();
#没有索引
select * from s1 where id=333333333;
#建立索引
create index a on s1(id);
#查询测试
select * from s1 where id=333333333;
#补充
1.mysql先去索引表里根据b+树的搜索原理很快搜索到id等于333333333的记录不存在，IO大大降
低，因而速度明
显提升
2.我们可以去mysql的data目录下找到该表，可以看到占用的硬盘空间多了
3.如果我们通过email查询，速度依旧很慢，因为没有对eamil创建索引

SQL模型

ANSI QUOTES：：宽松模式，对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报warning警告。双引号相当于反引号，只可以引用表名等字段名称，字符串只可以使用单引号引用；

IGNORE_SPACE：在内建函数中忽略多余的空白字符；
STRICT_ALL_TABLES：如果没有设置这个值，非法的数据都允许存入，但是会有一个警告提示。如果设置了，所有非法的数据都不允许填入，并且返回一个错误；
STRICT_TRANS_TABLES：向一个支持事务的表中插入非法数据的时候不允许，并且返回一个错误；
TRADITIONAL：严格模式，当向mysql数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报error错误。用于事物时，会进行事物的回滚。

如何查看并修改sql模型？

SELECT @@[GLOBAL | SESSION].sql_mode; 显示值
set GLOBAL | SESSION sql_mode=[值]; 修改值

优化性能相关

总结

1. 一定是为搜索条件的字段创建索引，比如select * from s1 where id = 333;就需要为id加
上索引
2. 在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快
比如create index idx on s1(id);会扫描表中所有的数据，然后以id为数据项，创建索引结构，
存放于硬盘的
表中。
建完以后，再查询就会很快了。
3. 需要注意的是：innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单独的索引文
件
table1.MYI
MySAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在innodb中，表数据文件本
身就是按照
B+Tree（BTree即Balance True）组织的一个索引结构，这棵树的叶节点data域保存了完整的数据
记录。这个索
引的key是数据表的主键，因此innodb表数据文件本身就是主索引。
因为inndob的数据文件要按照主键聚集，所以innodb要求表必须要有主键（Myisam可以没有），如果
没有显式定
义，则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则mysql
会自动为
innodb表生成一个隐含字段作为主键，这字段的长度为6个字节，类型为长整型.

正确使用索引
- 并不是说我们创建了索引就一定会加快查询速度，若想利用索引达到预想的提高查询速度的效果，我们在添加索引时，必须遵循以下问题
- 范围问题，或者说条件不明确，条件中出现这些符号或关键字：>、>=、<、<=、!= 、between…and…、like
- 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。
- 索引列不能在条件中参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time =unix_timestamp(’2014-05-29’)
- 最左前缀匹配原则（详见第八小节），非常重要的原则，对于组合索引mysql会一直向右匹配直到遇到范围查询 (>、<、between、like)就停止匹配(指的是范围大了，有索引速度也慢)，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
注意事项（有很多可以优化MySQL，以下只是部分操作）

避免使用select * ‐ 使用count(*)
创建表时尽量使用 char 代替 varchar
表的字段顺序固定长度的字段优先
组合索引代替多个单列索引（由于mysql中每次只能使用一个索引，所以经常使用多个条件查
询时更适合使用组 合索引）
尽量使用短索引
使用连接（JOIN）来代替子查询(Sub‐Queries)
连表时注意条件类型需一致
索引散列值（重复少）不适合建索引，例：性别不适合

事务特性（事务只会去管理数据可能发生修改的语句）

特定
把数据库从一种一致性状态转换为另一种一致性状态，来保证数据库的完整性
主要用于处理操作量大，复杂高的数据
在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
事务处理可以用来维护数据库的完整性，保证成批的sql语句要么全部执行，要么全部不执行。全部执行称为事务提交，全部不执行称为事务回滚。
事务主要用于管理insert、update、delete

ACID特性（重要）

一般来说，事务必须满足四个特性（ACID）
A：原子性，事务必须是执行任务的最小原子单位，事务要么成功要么撤回
C：一致性，事务是把数据库从一个一致性状态转化为另一个一致性状态
I：隔离性，每个事务之间是隔离的
D：持久性，事务一旦执行，数据的修改是永久的

四个隔离等级

read uncommitted（未提交读）：出现脏读的现象
read conmitted （提交读）：能够解决脏读，修改数据时会加锁。会出现幻读现象
repeatable read （可重读）：能够解决幻读现象
serializable（可串行化读）：事务需要按串行化解决（MVCC）

Innodb存储引擎默认可重复读

mysql>begin； ＃开始一个事务
mysql>insert into a (a) values(555);
mysql>rollback; #回滚，这样数据是不会写入的
mysql>commit; #提交事务

事务案例

创建一个表并插入数据

MariaDB [db1]> create table user(id int primary key auto_increment,memory
int unsigned);
Query OK, 0 rows affected (0.00 sec)
MariaDB [db1]> insert into user values (0,500), (0,100), (0,0);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [db1]> select * from user;
+----+--------+
| id | memory |
+----+--------+
| 1 | 500 |
| 2 | 100 |
| 3 | 0 |
+----+--------+
3 rows in set (0.00 sec)

查看事务变量

MariaDB [db1]> show variables like '%commit%';
+-------------------------------------------+-------+
| Variable_name | Value |
+-------------------------------------------+-------+
| aria_group_commit | none |
| aria_group_commit_interval | 0 |
| autocommit | ON |
| innodb_commit_concurrency | 0 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_use_global_flush_log_at_trx_commit | ON |
+-------------------------------------------+-------+
6 rows in set (0.00 sec)
# 如果需要关闭自动提交的话可以在会话端通过 set autocommit=0或者在配置文件中添加参数
autocommit=0

开启事务，开启事务时不会自动提交，操作处于内存当中

MariaDB [db1]> begin;
Query OK, 0 rows affected (0.00 sec)
MariaDB [db1]> update user set memory=memory - 250 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [db1]> select * from user;
+----+--------+
| id | memory |
+----+--------+
| 1 | 250 |
| 2 | 100 |
| 3 | 0 |
+----+--------+
3 rows in set (0.01 sec)
MariaDB [db1]> rollback;
Query OK, 0 rows affected (0.00 sec)
MariaDB [db1]> select * from user;
+----+--------+
| id | memory |
+----+--------+
| 1 | 500 |
| 2 | 100 |
| 3 | 0 |
+----+--------+
3 rows in set (0.00 sec)

同时开启两个事务，另一个事务会卡住，这是事务的隔离性决定的

MariaDB [db1]> begin;
Query OK, 0 rows affected (0.00 sec)
MariaDB [db1]> update user set memory=memory - 250 where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

结束事务的两种方式是commit或者rollback

日志管理

错误日志（log_error）
错误日志是用来记录MySQL数据库的启动、关闭、日常运行过程中、状态信息、警告、错误等信息

查看错误日志文件位置

MariaDB [(none)]> show variables like 'log_error';
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| log_error | /var/log/mariadb/mariadb.log |
+---------------+------------------------------+
1 row in set (0.00 sec)