MySQL安装部署
基础管理
- 密码相关设置
  - 管理员密码设定
  - 管理员密码忘记
- 客户端工具
- 基础SQL语句
MySQL体系结构
- C/S(客户端/服务端)模型介绍
- 连接方式
- 实例介绍
- 工作流程**
- 程序结构
- 逻辑结构
存储引擎
- 种类
- 存储引擎相关操作
SQL语句
- 用户管理
- 权限管理
- 表管理
表重命名
删除sex列
添加sex列
修改id宽度
修改name列的字段名
修改sex列的位置
修改id为自增主键
删除主键，可以看到删除一个自增主键会报错，要去除自增属性后再删除
添加联合主键
- 表约束规则
不允许向id列插入空元素
方法一：not null + unique
方法二：某一个字段后加上primary key
方法三：在所有字段后单独定义primary key
方法四：给已建成的表添加主键约束
在创建完表后，修改自增字段的起始值
也可以创建表时指定auto_increment的初始值，注意初始值的设置为表选项，应该放到括号外
sqlserver里auto_increment为设置步长
mysql自增补偿
基于会话级别
基于全局级别的
注意：如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset（初始值）的值会被忽略，这相当于第一步步子就迈大了，扯着了蛋
- 记录操作（插入、更新、删除）
- 查询数据
1:单条件查询
2:多条件查询
3:关键字BETWEEN AND
4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)
5:关键字IN集合查询
6:关键字LIKE模糊查询
- 多表连接查询
建表
插入数据
- 子查询
查询平均年龄在25岁以上的部门名
查看技术部员工姓名
查看不足1人的部门名(子查询得到的是有人的部门id)
数据类型
- 数值类型
- 日期时间类型
- 字符串类型
- ENUM和SET类型
索引管理
- 索引介绍
我们可以在创建上述索引的时候，为其指定索引类型，分两类
不同的存储引擎支持的索引类型也不一样
1. 准备表
2. 创建存储过程，实现批量插入记录
3. 查看存储过程
4. 调用存储过程
没有索引
建立索引
查询测试
补充
- 优化性能相关
事务特性
- 特点
- ACID特性
- 四个隔离等级
日志管理
- 错误日志
- 二进制日志
  - 案例，使用二进制日志文件进行数据恢复
- 慢日志slow_log
备份单个表
备份多个表
- 恢复
主从复制
- 复制过程
- 应用场景

MySQL安装部署

安装mariadb-server

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

启动mysql服务，初始化 ```bash [root@localhost ~]# systemctl start mariadb.service [root@localhost ~]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we’ll need the current password for the root user. If you’ve just installed MariaDB, and you haven’t set the root password yet, the password will be blank, so you should just press enter here.

Enter current password for root (enter for none): 当前root用户密码为空，所以直接敲回车 OK, successfully used password, moving on…

Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation.

Set root password? [Y/n] y 设置root密码 New password: Re-enter new password: Password updated successfully! Reloading privilege tables.. … Success!

By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment.

Remove anonymous users? [Y/n] 删除匿名用户 … Success!

Normally, root should only be allowed to connect from ‘localhost’. This ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n 禁止root远程登录 … skipping.

By default, MariaDB comes with a database named ‘test’ that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment.

Remove test database and access to it? [Y/n] n 删除test数据库 … skipping.

Reloading the privilege tables will ensure that all changes made so far will take effect immediately.

Reload privilege tables now? [Y/n] 刷新授权表，让初始化生效 … Success!

Cleaning up…

All done! If you’ve completed all of the above steps, your MariaDB installation should now be secure.

Thanks for using MariaDB!


- 启动数据库并查看
```bash
Thanks for using MariaDB!
[root@localhost ~]# systemctl start mariadb.service
[root@localhost ~]# 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 四 2021-03-11 20:28:46 CST; 11min ago
...

登录数据库 ```bash [root@localhost ~]# mysql -uroot -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 10 Server version: 5.5.68-MariaDB MariaDB Server

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]>


- 主配置文件（关注每一个参数的意义，参数的值可以根据实际场景更改）
```bash
[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 ~]# vim /etc/my.cnf.d/client.cnf
[client]
user=root
password=1
# This group is not read by mysql client library,
# If you use the same .cnf file for MySQL and MariaDB,
# use it for MariaDB-only client options
[client-mariadb]
[root@localhost ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.68-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>

基础管理

密码相关设置

管理员密码设定

[root@localhost ~]# mysqladmin -uroot -p password <new password>
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]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> grant all on *.* to root@'localhost' identified by '111';
Query OK, 0 rows affected (0.00 sec)

重启数据库
```
[root@localhost ~]# pkill mysqld
[root@localhost ~]# systemctl restart mariadb
```
ps：另外也可以通过修改USER表的password来修改密码
UPDATE USER SET PASSWORD=PASSWORD(‘111’) WHERE USER=’root’;

客户端工具
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-threads 重置线程池缓存
[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 关闭复制线程

说白了就是不用登录数据库也能操作，如果在/etc/my.cnf.d/client.cnf里配置了默认登录的用户名和密码，这里的-u、-p参数可以不用加。

在数据库语句中加的命令（最常见的是\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;
删：drop database db1;
改：alter database db1 charset latin1;
查：show databases;

操作文件（表）

增：create table t1(id int,name char);
删：drop table t1；
改：alter table t1 modify name char(3);
查:show tables;

操作文件内容（记录）

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

MySQL体系结构

C/S(客户端/服务端)模型介绍

C0WJB%Q@JBJ`JA4GKP6B99L.png

连接方式

TCP/IP连接

server1：192.168.80.132（作为服务器）
server2：192.168.80.129（作为客户端）

先在服务器上给server2的地址授权，这样才能从客户端登录服务器

MariaDB [(none)]> use mysql;
MariaDB [mysql]> grant all on *.* to root@'192.168.80.129' identified by '222';
MariaDB [mysql]> flush privileges;

关闭服务器端防火墙，否则可能屏蔽了端口号

[root@server1 ~]# systemctl stop firewalld
[root@server1 ~]# setenforce 0

再在server2上尝试连接server1

[root@server2 ~]# mysql -uroot -p111 -h 192.168.80.132 -P 3306

socket方式连接
```
[root@localhost ~]# mysql -uroot -p -S /tmp/mysql.sock
```
实例介绍
实例=MySQL后台守护进程+master thread+干活的thread+预分配内存
公司=老板+经理+员工+办公室
工作流程**

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

程序结构

把工作流程相同功能划分为一个结构里，可以形成MySQL的体系结构，如下图。值得一提的是MySQL存
储引擎层使用的是插件式结构，可以根据场景不同选择不同的存储引擎。不同的存储引擎对于上层的应
用程序或者是底层的文件系统是透明的。我们把MySQL的体系结构称为插件式存储引擎结构。
![M~J]VEDHN[3T0UB}TZ}I_M.png

连接层功能
- 提供连接协议：TCP/IP 、SOCKET
- 提供验证：用户、密码，IP，SOCKET
- 提供专用连接线程：接收用户SQL，返回结果
```
通过以下语句可以查看到连接线程基本情况
MariaDB [(none)]> show processlist;
```
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的⾮锁定读；
- 从MySQL5.6版本后支持全文索引；
- 从5.5.8版本开始，InnoDB存储引擎是默认的存储引擎；
- 将数据放在⼀个逻辑的表空间中，这个表空间就像⿊盒⼀样由InnoDB存储引擎⾃身进⾏管理；
MyISAM
- 不⽀持事务、表锁设计、支持全文索引，主要⾯向OLAP数据库应⽤；
- 它的缓冲池只缓存索引⽂件，不缓存数据⽂件；
- MyISAM存储引擎表由MYD和MYI组成，MYD⽤来存放数据⽂件，MYI⽤来存放索引⽂件。
MEMORY
- 在内存中存储所有数据，应用于对非关键数据由快速查找的场景。
- Memory类型的表访问数据非常快，因为它的数据是存放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失
- 应用场景：快速定位记录
ARCHIVE
- Archive存储引擎只⽀持INSERT和SELECT操作，从5.1开始⽀持索引；
- 使⽤zlib算法将数据⾏进⾏压缩后存储，压缩⽐⼀般可达1：10；
- ⾮常适合存储归档数据，如⽇志信息；
- 使⽤⾏锁来实现⾼并发的插⼊操作，但是其本身并不是事务安全的存储引擎，其设计主要⽬标是提供告诉的插⼊和压缩功能。
FEDERATED
- Federated存储引擎表并不存放数据，它只是指向⼀台远程MySQL数据库服务器上的表。这⾮常类似于 SQLServer的链接服务器和Oracle的透明⽹关，不同的是它只⽀持MySQL数据库表，不⽀持异构数据库表。
EXAMPLE
- 这种存储引擎用以保存阐明如何开始写新的存储引擎的 MySql 源码的例子。它主要针对于有兴趣的开发人员。这种存储引擎就是一个啥事也不做的 “存根”。你可以使用这种引擎创建表，但是你无法向其保存任何数据，也无法从它们检索任何索引。
BLACKHOLE
- 黑洞存储引擎，类似于 Unix 的 /dev/null，Archive 只接收但却并不保存数据。对这种引擎的表的查询常常返回一个空集。这种表可以应用于 DML 语句需要发送到从服务器，但主服务器并不会保留这种数据的备份的主从配置中。
MERGE
- 允许MySql DBA 或开发者将一系列相同的 MyISAM 表进行分组，并把它们作为一个对象进行引用。适用于超大规模数据场景，如数据仓库。
NDBCLUSTER
- 是⼀个集群存储引擎，类似于Oracle的RAC集群，不过其结构与Oracle的share everything不同的是，它使⽤的是share nothing的集群结构，可以提供更⾼的可⽤性；
- 数据全部放在内存中（从5.1版本开始，可以将⾮索引数据放在磁盘上）因此主键查找的速度极快，并且通过添加NDB数据存储节点可以线性地提⾼数据库性能，是⾼可⽤、⾼性能的集群系统；
- 其连接操作是在数据库层完成的，⽽不是在存储引擎层完成的。这意味着，复杂的连接操作需要巨⼤的⽹络开销，因此查询速度很慢。
CSV
- 它的表真的是以逗号分隔的文本文件。CSV 表允许你以 CSV 格式导入导出数据，以相同的读和写的格式和脚本和应用交互数据。由于 CSV 表没有索引，你最好是在普通操作中将数据放在 InnoDB 表里，只有在导入或导出阶段使用一下CSV 表。
Maria存储引擎
- 新开发的引擎，设计主要⽬标是⽤来取代原来的MyISAM存储引擎，从⽽成为MySQL的默认存储引擎；⽀持缓存数据和索引⽂件，应⽤了⾏锁设计，提供了MVCC功能，⽀持事务和⾮事务安全的选项，以及更好的BLOB字符类型的处理性能。
  存储引擎相关操作
查看MySQL支持的存储引擎

MariaDB [(none)]> show engines\G;

查看默认存储引擎

MariaDB [(none)]> SELECT @@default_storage_engine;

修改存储引擎

#在建表时指定
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

MVCC：https://www.jianshu.com/p/8845ddca3b23

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 server2@192.168.80.20 identified by '1';

查询用户

MariaDB [(none)]> select user,host,password from mysql.user;
+----------+---------------+-------------------------------------------+
| user     | host          | password                                  |
+----------+---------------+-------------------------------------------+
| root     | localhost     |                                           |
| root     | server3       |                                           |
| root     | 127.0.0.1     |                                           |
| root     | ::1           |                                           |
|          | localhost     |                                           |
|          | server3       |                                           |
| mhaadmin | %             | *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
| root     | %             | *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
| server2  | 192.168.80.20 | *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
+----------+---------------+-------------------------------------------+

修改用户密码

MariaDB [mysql]> update user set password=password('123') where user='mhaadmin' and host='%';
MariaDB [mysql]> select user,host,password from mysql.user;
+----------+---------------+-------------------------------------------+
| user     | host          | password                                  |
+----------+---------------+-------------------------------------------+
| root     | localhost     |                                           |
| root     | server3       |                                           |
| root     | 127.0.0.1     |                                           |
| root     | ::1           |                                           |
|          | localhost     |                                           |
|          | server3       |                                           |
| mhaadmin | %             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root     | %             | *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
| server2  | 192.168.80.20 | *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
+----------+---------------+-------------------------------------------+

删除用户

MariaDB [mysql]> drop user server2@192.168.80.20;

权限管理

授权

MariaDB [(none)]> grant all *.* 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：超级管理员才具备的，给别的用户授权的功能

权限作用范围

[数据库名].[表名]
表示所有, .*就是所有数据库的所有表

查看授权

MariaDB [mysql]> show grants for root@'localhost'\G
*************************** 1. row ***************************
Grants for root@localhost: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' 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 [mysql]> revoke delete on *.* from mhaadmin@'%';
```
案例：在本地Windows主机上使用navicat登录到虚拟机里的MySQL，通常会由于没有授权而被拒绝，这是就可以通过授权解决问题：grant all on *.* to root@'192.168.80.%' identified by '1';

为开发人员创建用户需要考虑的问题：

需要哪些权限
针对哪些库、表
从什么地方进行连接
密码有什么要求

提示：在MySQL8.0中grant命令添加新特性。
建用户和授权分开了，grant 不再支持自动创建用户了，不支持改密码。授权之前，必须要提前创建用户。

表管理

创建表

create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);

注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的

MariaDB [mysql]> create table staff_info(id int,name varchar(50),age int(3),sex enum('male','female'),phone bigint(11),job varchar(11));

插入数据

MariaDB [mysql]> insert into staff_info (id,name,age,sex,phone,job) values (1,'Alex',83,'female',13651054608,'IT');
MariaDB [mysql]> insert into staff_info values(2,'Egon',26,'male',13304320533,'Teacher');
MariaDB [mysql]> insert into staff_info values(3,'nezha',25,'male',13332353222,'IT'),(4,'boss_jin',40,'male',13332353333,'IT');

查看表结构

MariaDB [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    |       |
+-------+-----------------------+------+-----+---------+-------+
MariaDB [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=latin1

修改表结构的语法 ```bash

修改表名 ALTER TABLE 表名 RENAME 新表名;
增加字段 ALTER TABLE 表名 ADD 字段名数据类型 [完整性约束条件…], ADD 字段名数据类型 [完整性约束条件…];
删除字段 ALTER TABLE 表名 DROP 字段名;
修改字段 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 字段名; bash
表重命名
MariaDB [mysql]> alter table staff_info rename staff;

删除sex列

MariaDB [mysql]> alter table staff drop sex;

添加sex列

MariaDB [mysql]> alter table staff add sex enum(‘male’,’female’);

修改id宽度

MariaDB [mysql]> alter table staff modify id int(4);

修改name列的字段名

MariaDB [mysql]> alter table staff change name sname varchar(20);

修改sex列的位置

MariaDB [mysql]> alter table staff modify sex enum(‘male’,’female’) after sname;

修改id为自增主键

MariaDB [mysql]> alter table staff modify id int(4) primary key auto_increment;

删除主键，可以看到删除一个自增主键会报错，要去除自增属性后再删除

MariaDB [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 MariaDB [mysql]> alter table staff modify id int(4); MariaDB [mysql]> alter table staff drop primary key;

添加联合主键

MariaDB [mysql]> alter table staff add primary key (sname,id);


- 删除表
```bash
DROP TABLE 表名;

表约束规则

防止不规范的数据存储在数据库中，DBMS自动检测数据，按照一定约束规则确保数据库存储的数据正确有效。

分类
- NOT NULL：非空约束
- DEFAULT：当表中某一列有重复值，为了避免重复操作，可以将其设置为默认值。
- UNIQUE：唯一约束
- PRIMARY KEY：主键，唯一标识一条记录
- FOREIGN KEY：外键，从属于主表的一条记录
NOT NULL ```bash MariaDB [mysql]> create table t1 (id int not null); Query OK, 0 rows affected (0.01 sec)

不允许向id列插入空元素

MariaDB [mysql]> insert into t1 values(null); ERROR 1048 (23000): Column ‘id’ cannot be null MariaDB [mysql]> insert into t1 values(1);


- NOT NULL + DEFAULT
```bash
MariaDB [mysql]> create table t2 (id int(4) not null default 0,name varchar(11) not null);
MariaDB [mysql]> insert into t2(name) values('xiaoming');
MariaDB [mysql]> select * from t2;
+----+----------+
| id | name     |
+----+----------+
|  0 | xiaoming |
+----+----------+
# 可以发现，id设置默认值为0，在只插入name字段的情况下，id会使用默认值填充
# name字段不能为空且没有默认值，所以不能单独向id字段插入数据（报警告）
MariaDB [mysql]> insert into t2(id) values(111);
Query OK, 1 row affected, 1 warning (0.00 sec)

UNIQUE ```bash 方法一： create table department1( id int, name varchar(20) unique, comment varchar(100) ); 方法二： create table department2( id int, name varchar(20), comment varchar(100), unique(name) );

MariaDB [mysql]> create table department(id int,name varchar(20) unique); MariaDB [mysql]> insert into department values(1,’IT’),(2,’IT’); ERROR 1062 (23000): Duplicate entry ‘IT’ for key ‘name


- NOT NULL + UNIQUE
约束功能上与primary key相同，但是主键在索引中用来唯一标识一条记录。
- 联合唯一
```bash
MariaDB [mysql]> create table service(id int primary key auto_increment,name varchar(20),host varchar(20) not null,port int not null,unique(host,port));
MariaDB [mysql]> insert into service values(1,'nginx','192.168.80.10',80),(2,'haproxy','192.168.80.20',80),(3,'mysql','192.168.80.30',3306);
MariaDB [mysql]> insert into service values(4,'apache','192.168.80.10',80);
ERROR 1062 (23000): Duplicate entry '192.168.80.10-80' for key 'host'

PRIMARY KEY
- 主键为了保证表中的每一条数据的该字段都是表格中的唯一值
- 主键必须唯一，主键值非空；
- 可以是单一字段，也可以是多字段组合、单字段主键： ```bash
  方法一：not null + unique
  create table department1( id int not null unique, #主键 name varchar(20) not null unique, comment varchar(100) );

方法二：某一个字段后加上primary key

create table department2( id int primary key, #主键 name varchar(20), comment varchar(100) );

方法三：在所有字段后单独定义primary key

create table department3( id int, name varchar(20), comment varchar(100), primary key(id);

方法四：给已建成的表添加主键约束

alter table department4 modify id int primary key;


- AUTO_INCREMENT
```bash
# 不指定id，则自动增长
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);
mysql> desc student;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | YES | | male | |
+-------+-----------------------+------+-----+---------+----------------+
mysql> insert into student(name) values
-> ('egon'),
-> ('alex')
-> ;
mysql> select * from student;
+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | egon | male |
| 2 | alex | male |
+----+------+------+
# 也可以指定id
mysql> insert into student values(4,'asb','female');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values(7,'wsb','female');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+------+--------+
| id | name | sex |
+----+------+--------+
| 1 | egon | male |
| 2 | alex | male |
| 4 | asb | female |
| 7 | wsb | female |
+----+------+--------+
# 对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
mysql> delete from student;
Query OK, 4 rows affected (0.00 sec)
mysql> select * from student;
Empty set (0.00 sec)
mysql> insert into student(name) values('ysb');
mysql> select * from student;
+----+------+------+
| id | name | sex |
+----+------+------+
| 8 | ysb | male |
+----+------+------+
# 应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它
mysql> truncate student;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into student(name) values('egon');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | egon | male |
+----+------+------+
row in set (0.00 sec)

increment 和 offset ```bash
在创建完表后，修改自增字段的起始值
mysql> create table student( -> id int primary key auto_increment, -> name varchar(20), -> sex enum(‘male’,’female’) default ‘male’ -> ); mysql> alter table student auto_increment=3; mysql> show create table student; ……. ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 mysql> insert into student(name) values(‘egon’); Query OK, 1 row affected (0.01 sec) mysql> select * from student; +——+———+———+ | id | name | sex | +——+———+———+ | 3 | egon | male | +——+———+———+ row in set (0.00 sec) mysql> show create table student; ……. ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

也可以创建表时指定auto_increment的初始值，注意初始值的设置为表选项，应该放到括号外

create table student( id int primary key auto_increment, name varchar(20), sex enum(‘male’,’female’) default ‘male’ )auto_increment=3;

sqlserver里auto_increment为设置步长

create table t1（ id int。。。）engine=innodb,auto_increment=2 default charset=utf8 # 步长为2

mysql自增补偿

基于会话级别

set session auth_increment_increment=2 #修改会话级别的步长

基于全局级别的

set global auth_increment_increment=2 #修改全局级别的步长（所有会话都生效）

注意：如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset（初始值）的值会被忽略，这相当于第一步步子就迈大了，扯着了蛋


- FOREIGN KEY
   - 多表
   - 示例：员工信息表中的部分字段，重复且浪费资源
   - 唯一才可以作为外键
```bash
mysql> create table departments (dep_id int(4),dep_name varchar(11));
Query OK, 0 rows affected (0.02 sec)
mysql> desc departments;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| dep_id | int(4) | YES | | NULL | |
| dep_name | varchar(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)
# 创建外键不成功
mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign 
key(dep_id)
references departments(dep_id));
ERROR 1215 (HY000): Cannot add foreign key
# 设置dep_id非空，仍然不能成功创建外键
mysql> alter table departments modify dep_id int(4) not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc departments;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| dep_id | int(4) | NO | | NULL | |
| dep_name | varchar(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)
mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign 
key(dep_id)
references departments(dep_id));
ERROR 1215 (HY000): Cannot add foreign key constraint
# 当设置字段为unique唯一字段时，设置该字段为外键成功
mysql> alter table departments modify dep_id int(4) unique;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc departments;
+----------+-------------+------+-----+---------+------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| dep_id | int(4) | YES | UNI | NULL | |
| dep_name | varchar(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
rows in set (0.01 sec)
mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign 
key(dep_id)
references departments(dep_id));
Query OK, 0 rows affected (0.02 sec)

综合示例

#表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一
create table department(
id int primary key,
name varchar(20) not null
)engine=innodb;
#dpt_id外键，关联父表（department主键id），同步更新，同步删除
create table employee(
id int primary key,
name varchar(20) not null,
dpt_id int,
foreign key(dpt_id)
references department(id)
on delete cascade # 级连删除
on update cascade # 级连更新
)engine=innodb;
#先往父表department中插入记录
insert into department values
(1,'教质部'),
(2,'技术部'),
(3,'人力资源部');
#再往子表employee中插入记录
insert into employee values
(1,'yuan',1),
(2,'nezha',2),
(3,'egon',2),
(4,'alex',2),
(5,'wusir',3),
(6,'李沁洋',3),
(7,'皮卡丘',3),
(8,'程咬金',3),
(9,'程咬银',3)
;
#删父表department，子表employee中对应的记录跟着删
mysql> delete from department where id=2;
Query OK, 1 row affected (0.00 sec)
mysql> select * from employee;
+----+-----------+--------+
| id | name | dpt_id |
+----+-----------+--------+
| 1 | yuan | 1 |
| 5 | wusir | 3 |
| 6 | 李沁洋 | 3 |
| 7 | 皮卡丘 | 3 |
| 8 | 程咬金 | 3 |
| 9 | 程咬银 | 3 |
+----+-----------+--------+
rows in set (0.00 sec)
#更新父表department，子表employee中对应的记录跟着改
mysql> update department set id=2 where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from employee;
+----+-----------+--------+
| id | name | dpt_id |
+----+-----------+--------+
| 1 | yuan | 1 |
| 5 | wusir | 2 |
| 6 | 李沁洋 | 2 |
| 7 | 皮卡丘 | 2 |
| 8 | 程咬金 | 2 |
| 9 | 程咬银 | 2 |
+----+-----------+--------+
rows in set (0.00 sec)

记录操作（插入、更新、删除）

插入数据

插入完整数据（顺序插入）

语法一：
INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);
语法二：
INSERT INTO 表名 VALUES (值1,值2,值3…值n);

指定字段插入数据

语法：
INSERT INTO 表名(字段1,字段3,字段5…) VALUES (值1,值3,值5…);

插入多条记录

语法：
INSERT INTO 表名 VALUES
(值1,值2,值3…值n),
(值1,值2,值3…值n),
(值1,值2,值3…值n);

插入查询结果

语法：
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=’’;

查询数据

语法

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.限制结果的显示条数

简单查询

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约束

比较运算符：> < >= <= <> !=
between 80 and 100 值在80到100之间
in(80,90,100) 值是80或90或100
like ‘e%’

通配符可以是%或，
%表示任意多字符
表示一个字符

逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not ```bash
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模糊查询

通配符’%’ SELECT FROM employee WHERE empname LIKE ‘eg%’; 通配符’’ SELECT FROM employee WHERE empname LIKE ‘al_‘;


- group by
```bash
单独使用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:

如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义
多条记录之间的某个字段值相同，该字段通常用来作为分组的依据

聚合函数

#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组
示例：
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

Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。

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;#错误，分组后无法直接取到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 ```bash 按单列排序 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
```bash
示例：
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; #先查询出第6条，然后包含这一条在内往后查5条

正则表达式

SELECT * FROM employee WHERE emp_name REGEXP '^ale';    # 行首为ale
SELECT * FROM employee WHERE emp_name REGEXP 'on$';        # 行尾为on
SELECT * FROM employee WHERE emp_name REGEXP 'm{2}';    # 出现两次m
小结：对字符串匹配的方式
WHERE emp_name = 'egon';
WHERE emp_name LIKE 'yua%';
WHERE emp_name REGEXP 'on$';

多表连接查询

键表和准备数据 ```bash

建表
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)


- 语法
```bash
#重点：外链接语法
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;

子查询

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

带IN关键字的子查询 ```bash
查询平均年龄在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);


- 带比较运算符的子运算
```bash
#比较运算符：=、!=、>、>=、<、<=、<>
#查询大于所有人平均年龄的员工名与年龄
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=203，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=204);

数据类型

数值类型

INT整数

# 创建表一个是默认宽度的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)

日期时间类型

date/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)

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示例

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

索引管理

索引介绍

功能

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

常用索引

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

索引类型 ```bash
我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢 btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

不同的存储引擎支持的索引类型也不一样

InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，已经支持全文索引； MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引； Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引； NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引； Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

<a name="1qxqV"></a>
## 索引使用
- 创建/删除索引
```bash
# 方法一：创建表时
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

测试索引 ```bash

1. 准备表
create table s1( id int, name varchar(20), gender char(6), email varchar(50) );

2. 创建存储过程，实现批量插入记录

delimiter #声明存储过程的结束符号为 create procedure auto_insert1() BEGIN declare i int default 1; while(i<3000000)do insert into s1 values(i,’eva’,’female’,concat(‘eva’,i,’@oldboy’)); set i=i+1; end while; END #结束 delimiter ; #重新声明分号为结束符号

3. 查看存储过程

show create procedure auto_insert1\G

4. 调用存储过程

call auto_insert1();

没有索引

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创建索引

<a name="jmQJ6"></a>
## SQL模型
- ANSI QUOTES：：宽松模式，对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报warning警告。 双引号相当于反引号，只可以引用表名等字段名称，字符串只可以使用单引号引用；
- IGNORE_SPACE：在内建函数中忽略多余的空白字符；
- STRICT_ALL_TABLES：如果没有设置这个值，非法的数据都允许存入，但是会有一个警告提示。如果设置了，所有非法的数据都不允许填入，并且返回一个错误；
- STRICT_TRANS_TABLES：向一个支持事务的表中插入非法数据的时候不允许，并且返回一个错误；
- TRADITIONAL：严格模式，当向mysql数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报error错误。用于事物时，会进行事物的回滚
如何查看并修改sql模型？
```bash
SELECT @@[GLOBAL | SESSION].sql_mode;   显示值
set GLOBAL | SESSION sql_mode=[值];    修改值

优化性能相关

一定是为搜索条件的字段创建索引，比如select * from s1 where id = 333;就需要为id加上索引
在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快比如create index idx on s1(id);会扫描表中所有的数据，然后以id为数据项，创建索引结构，存放于硬盘的表中。建完以后，再查询就会很快了。
需要注意的是：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中只有使用了innodb存储引擎的数据库或表才支持事务
事务处理可以用来维护数据库的完整性，保证成批的sql语句要么全部执行，要么全部不执行。全部执行称为事务提交，全部不执行称为事务回滚。
事务主要用于管理insert、update、delete

ACID特性
A（Atomicity）：原子性或不可分割性，一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。
C（Consistency）：一致性，在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则
I（Isolation）：隔离性，数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
D（Durability）：持久性，事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

四个隔离等级
read uncommited（读未提交）：会出现脏读现象
read commited（读提交）：能解决脏读，会出现幻读现象
repeatable（可重复读）：能解决幻读
serializable（可串行化）：事务要按串行化解决（MVCC）

innodb存储引擎默认可重复读

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

日志管理

错误日志

错误日志是用来记录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)

二进制日志

备份恢复必须用二进制日志，主从环境必须用二进制日志。binlog是SQL层的功能，记录的是变更的sql语句，不记录查询语句。

开启二进制日志

vim /etc/my.cnf
server_id=6                                # 设置id              
log_bin=/data/binlog/mysql-bin             # 开启并指定二进制日志目录及前缀名                 
binlog_format=row                          # binlog的日志记录格式
修改完配置重启服务会自动生成二进制日志

日志记录方式
- SBR（statement based replication）：语句模式原封不动的记录当前DML，5.6默认。
- RBR(ROW based replication) ：记录数据行的变化(用户看不懂，需要工具分析)，5.7默认。
- mixed（混合）MBR(mixed based replication)模式：以上两种模式的混合
SBR模式与RBR模式的对比（面试题）*
- SBR：可读性较高，日志量少，但是不够严谨
- ROW：可读性很低，日志量大，足够严谨（建议使用）

event事件介绍

二进制日志的最小记录单元
对于DDL,DCL,一个语句就是一个event
对于DML语句来讲:只记录已提交的事务。
例如以下列子,就被分为了4个event
begin;      120  - 340
DML1        340  - 460
DML2        460  - 550
commit;     550  - 760

查看日志的开启情况

MariaDB [(none)]>show variables like '%log_bin%';
+---------------------------------+------------------------------+
| Variable_name                   | Value                        |
+---------------------------------+------------------------------+
| log_bin                         | ON                           |
| log_bin_basename                | /data/binlog/mysql-bin       |
| log_bin_index                   | /data/binlog/mysql-bin.index |
| log_bin_trust_function_creators | OFF                          |
| log_bin_use_v1_row_events       | OFF                          |
| sql_log_bin                     | ON                           |
+---------------------------------+------------------------------+
6 rows in set (0.01 sec)

查看二进制日志文件数量

MariaDB [(none)]>flush logs;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]>show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       201 |
| mysql-bin.000002 |       201 |
| mysql-bin.000003 |       154 |
+------------------+-----------+
3 rows in set (0.00 sec)

查看正在使用的二进制日志文件

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+---------------
----+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
Executed_Gtid_Set |
+------------------+----------+--------------+------------------+---------------
----+
| mysql-bin.000003 |      154 |              |                  |               
  |
+------------------+----------+--------------+------------------+---------------
----+

日志内容查看

MariaDB [binlog]>show binlog events in 'mysql-bin.000003';
+------------------+-----+----------------+-----------+-------------+-----------
-----------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info       
                          |
+------------------+-----+----------------+-----------+-------------+-----------
-----------------------------+
| mysql-bin.000003 |   4 | Format_desc    |         6 |         123 | Server 
ver: 5.7.20-log, Binlog ver: 4  |
| mysql-bin.000003 | 123 | Previous_gtids |         6 |         154 |           
                           |
| mysql-bin.000003 | 154 | Anonymous_Gtid |         6 |         219 | SET 
@@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000003 | 219 | Query          |         6 |         319 | create 
database binlog                 |
| mysql-bin.000003 | 319 | Anonymous_Gtid |         6 |         384 | SET 
@@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000003 | 384 | Query          |         6 |         486 | use 
`binlog`; create table t1 (id int) |
+------------------+-----+----------------+-----------+-------------+-----------
-----------------------------+
Log_name：binlog文件名
Pos：开始的position    *****
Event_type：事件类型
Format_desc：格式描述，每一个日志文件的第一个事件，多用户没有意义，MySQL识别binlog必要信息
Server_id：mysql服务号标识
End_log_pos：事件的结束位置号 *****
Info：事件内容*****

binlog详细内容查看

[root@localhost ~]# mysqlbinlog --base64-output=decode-rows -vvv 
/data/binlog/mysql-bin.000003

基于position进行二进制日志截取

[root@localhost ~]# mysqlbinlog --start-position=219 --stop-position=1347 
/data/binlog/mysql-bin.000003 >/tmp/bin.sql

案例，使用二进制日志文件进行数据恢复

故障模拟 ```bash 创建了一个库 db, 导入了表t1 ,t1表中录入了很多数据一个开发人员,drop database db; 没有备份,日志都在.怎么恢复? 思路:找到建库语句到删库之前所有的日志,进行恢复.(开启了GTID模式) 故障案例模拟: (0) drop database if exists db ; (1) create database db charset utf8;
(2) use db; (3) create table t1 (id int); (4) insert into t1 values(1),(2),(3); (5) insert into t1 values(4),(5),(6); (6) commit (7) update t1 set id=30 where id=3; (8) commit; (9) delete from t1 where id=4; (10)commit; (11)insert into t1 values(7),(8),(9); (12)commit; (13)drop database db;

需求：将数据库恢复到以下状态（提示第9步和第13步是误操作，其他都是正常操作）


- 查看正在使用的二进制日志
```bash
MariaDB [(none)]> show master status ;
......
mysql-bin.000006
......

查看事件

MariaDB [binlog]>show binlog events in 'mysql-bin.000006';

日志截取

[root@localhost ~]# mysqlbinlog --start-position=1568 --stop-position=1762 /data/mysql/mysql-bin.000006 >/tmp/bin2.sql

恢复

MariaDB [(none)]> set sql_log_bin=0;                # 恢复过程临时关闭日志记录
MariaDB [(none)]> source /tmp/bin1.sql
MariaDB [(none)]> source /tmp/bin2.sql
MariaDB [(none)]> set sql_log_bin=1;

慢日志slow_log

慢日志文件是记录运行比较慢的sql语句，将这些sql语句记录下来以便进一步优化。可以自行补充一些关于慢日志的分析。

修改配置文件开启慢日志 ```bash 开关: slow_query_log=1 文件位置及名字 slow_query_log_file=/data/mysql/slow.log 设定慢查询时间: long_query_time=0.1 没走索引的语句也记录: log_queries_not_using_indexes

[root@localhost ~]# vim /etc/my.cnf slow_query_log=1 slow_query_log_file=/data/mysql/slow.log long_query_time=0.1 log_queries_not_using_indexes [root@localhost ~]# systemctl restart mysqld

<a name="PvhtQ"></a>
# 备份与恢复
<a name="IJb9J"></a>
## 备份方式
- 按照备份数据位置分类
   - 冷备：数据库硬盘中的数据
   - 热备：硬盘及内存中的一些数据进行备份
- 按照备份后的文件内容
   - 逻辑备份：一般内容是sql语句
   - 裸文件备份：拷贝数据库的物理文件，一般内容是二进制数据
- 按照备份数据的内容
   - 完全备份：对数据库进行一个完整的备份
   - 增量备份：在上次的完全备份的基础上对更新的数据进行备份
   - 日志备份：二进制日志备份，当数据库宕机后恢复的依据
- 冷备份的优点：
   - 备份简单，只要拷贝文件即可
   - 易于跨平台
   - 恢复简单，只要把文件恢复到相关位置即可
   - 恢复速度快，不需要执行任何sql语句，也不需要重新建索引
- 冷备份缺点：
   - 冷备文件通常比逻辑文件大很多
   - 不是总可以轻易跨平台
- 逻辑备份：
   - mysqldump
   - selcet * into outfile 'path/to/file' from tbname;
<a name="pg6oq"></a>
## 使用mysqldump工具恢复
mysqldump备份数据库的原理是把数据（包括库表）从MySQL库里以sql语句的形式直接输出或者生成备份文件的过程，这种备份成sql的方式称为逻辑备份。
| 参数 | 解释 |
| --- | --- |
| -B | 可以同时接多个库名，备份多个数据库 |
| -A | 备份所有数据库 |
| -d | 只备份表结构，没有行数据 |
| -t | 只备份行数据，没有表结构 |
| -F | 刷新binlog |
| -I | 锁定的表为只读 |
- 不带参数备份单个数据库
```bash
[root@localhost ~]# mysqldump test > test_db_bak.sql
 # 可以打开看看备份的数据
 [root@localhost ~]# cat test_db_bak.sql  | grep -Ev "#|\*|--|^$"
DROP TABLE IF EXISTS `test_tb`;
CREATE TABLE `test_tb` (
  `id` int(11) DEFAULT NULL,
  `name` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
LOCK TABLES `test_tb` WRITE;
INSERT INTO `test_tb` VALUES (1,'??'),(2,'lisi'),(4,'尼古拉赵四'),(3,'王二麻'),
(5,'谢大脚'),(6,'谢广坤');
UNLOCK TABLES;

加-B参数备份多个数据库
- 加-B参数的作用是增加创建数据库和连接数据库的语句，后面可以直接接多个库名，同时备份多个数据库
```
使用-B参数备份的数据会增加两行语句
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET gbk */;
USE `test`;
```

使用gzip压缩备份数据

[root@localhost ~]# mysqldump -B test | gzip> test_db_bak.sql.gz
[root@localhost ~]# ll -h
总用量 12K
-rw-------. 1 root root 1.4K 12月  2 15:04 anaconda-ks.cfg
-rw-r--r--. 1 root root 2.1K 12月  3 23:33 test_db_bak.sql
-rw-r--r--. 1 root root  824 12月  3 23:38 test_db_bak.sql.gz

备份多个表 ```bash

备份单个表
mysqldump 数据库名表名 > 备份文件名

备份多个表

mysqldump 数据库名表名1 表名2 … > 备份文件名 ```

恢复

将sql语句重新执行一遍，完成数据恢复
可以选择在客户端使用客户端工具运行sql脚本
可以在数据库服务器里面执行source命令
主从复制
复制过程

主服务器上任何操作都会被写入二进制日志文件当中
从服务器上的IO线程检测主服务器的二进制日志文件变化
从服务器上的IO线程同步主服务器的二进制日志文件到笨的中继日志中
从服务器上的sql线程读取和执行中继日志中的sql语句

应用场景

一主多从会对应读写分离
- 写操作由主服务器
- 读操作由从服务器
主服务器上drop操作
- 要去做备份操作，在服务器上通过LVM快照进行备份
主服务器挂掉了用户不能进行写入操作
- 高可用模型：多主架构
- 任何一台服务器既是主服务器又是从服务器
高可用架构：MHA架构

MySQL

MySQL安装部署

基础管理

密码相关设置

管理员密码设定

管理员密码忘记

客户端工具

基础SQL语句

MySQL体系结构

C/S(客户端/服务端)模型介绍

连接方式

实例介绍

工作流程**

程序结构

逻辑结构

存储引擎

种类

存储引擎相关操作

SQL语句

用户管理

权限管理

表管理

表重命名

删除sex列

添加sex列

修改id宽度

修改name列的字段名

修改sex列的位置

修改id为自增主键

删除主键，可以看到删除一个自增主键会报错，要去除自增属性后再删除

添加联合主键

表约束规则

不允许向id列插入空元素

方法一：not null + unique

方法二：某一个字段后加上primary key

方法三：在所有字段后单独定义primary key

方法四：给已建成的表添加主键约束

在创建完表后，修改自增字段的起始值

也可以创建表时指定auto_increment的初始值，注意初始值的设置为表选项，应该放到括号外

sqlserver里auto_increment为设置步长

mysql自增补偿

基于会话级别

基于全局级别的

注意：如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset（初始值）的值会被忽略，这相当于第一步步子就迈大了，扯着了蛋

记录操作（插入、更新、删除）

查询数据

1:单条件查询

2:多条件查询

3:关键字BETWEEN AND

4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)

5:关键字IN集合查询

6:关键字LIKE模糊查询

多表连接查询

建表

插入数据

子查询

查询平均年龄在25岁以上的部门名

查看技术部员工姓名

查看不足1人的部门名(子查询得到的是有人的部门id)

数据类型

数值类型

日期时间类型

字符串类型

ENUM和SET类型

索引管理

索引介绍

我们可以在创建上述索引的时候，为其指定索引类型，分两类

不同的存储引擎支持的索引类型也不一样

1. 准备表

2. 创建存储过程，实现批量插入记录

3. 查看存储过程

4. 调用存储过程

没有索引

建立索引

查询测试

补充

优化性能相关

事务特性

特点

ACID特性