导学：
红色为了解即可（可跳过）
四大块
运维层面
sql开发层面
数据库架构
主从
主主
主主带多个从 + keeaplive
读写分离中间件
分库分表
数据库优化
第一章：数据库介绍：
mysql数据库管理软件：
本质是一个socket套接字程序
作用：管理本地文件
数据库管理软件提供的一些管理单位
库-------------》文件夹
表-------------》文件
记录------------》文件内的一行内容
关系型数据库与非关系型数据库
第二章：部署数据库
部署mariadb
部署mysql（从官网安装）
在windows上部署mysql（学开发时用）***
部署mysql多实例（***）
在一台机器之上，用同一个mysqld命令启动多个mysql实例，相当于多台mysql去用
1、修改配置文件让端口不同
2、指定各自的数据目录
第三章：mysql基本管理
mysql启动关闭
mysql设置密码
客户端链接工具
字符编码的统一配置：utf8mb4
mysql客户端快捷命令
sql语句(大多数都是通用的)
库
增
create database db1;
删
drop database db1;
改
查
表
增
删
改
查
记录
增
删
改
查
第四章：sql语句（***）
sql开发的能力（软件开发的能力之一）
现阶段：
过一遍（为sql打基础）
补充：视图、触发器、存储过程、函数、流程控制
mysql为了方便你开发sql语句，为你提供的一些现成的机制或功能
学开发的阶段：
拿出来练熟练（****）
第五章：权限管理
mysql.user
msyql.db
第六章：存储引擎（******）
mysql管理软件-----------存储引擎------》本地文件
文本编辑器--------》文本文件
图片编辑器--------》图片文件
视频操作软件-------》视频文件
buffer pool相关优化项
mysql数据库服务器压力大的情况下甚至会把60~70%的物理内存
都分配給buffer pool
innodb存储引擎执行流程
三个重要日志
1、undo：还原旧数据
2、redo：防止写数据过程中mysql崩溃，可以用redo继续完成数据的写入
3、binlog：用于主从同步
优化项目：
redo log刷盘策略
binlog刷盘策略
innodb存儲引起存儲單位
1、row
2、Page
3、extent
4、segment
5、tablespace
第七章节：索引与慢查询（******）
慢查询日志：定位到慢查询的sql
如果把数据库中的所有数据都比喻一本书的话
索引就是在就是在给这本书的做目录
索引的核心原理：
二叉树、平衡二叉树、b树、b+树
什么是聚集索引，什么是辅助索引
什么情况下叫覆盖了索引
什么情况下叫回表操作
什么是联合索引，最左前缀匹配原则
索引下推，查询优化
如何正确使用索引？
explain查询一个条sql的执行计划
面试题：https://www.cnblogs.com/linhaifeng/articles/14425413.html
第八章节：事务
事务=多条sql语句
特点：
原子性：要么同时执行成功、要么一条也别想成功
一致性
隔离性
持久性
第九章节：redo、undo日志
第十章节：mysql的读现象/问题
并发访问：读一份数据的过程中，有人在同时改动该数据
脏读
不可重复度
幻读
第11章节：数据锁机制
innodb存储引擎，锁的是索引
死锁问题的所有场景以及解决方案
1、逻辑上导致死锁问题
2、innodb存储引擎的特点是锁定索引，并发场景下，可能会导致死锁问题
第12章节：多版本并发控制MVCC（***）
快照读 (snapshot read)与当前读 (current read)
第13章节：事务隔离级别
Read uncommitted（读未提交）
Read committed（读已提交）
Repeatable read（可重复读，innodb引擎默认）：可以防脏读、不可重复读问题，但不能防幻读
Serializable（序列化、串行化）
在mysql中解决脏读、幻读、不可重复读
innodb存储引擎设置事务隔离级别为RR + 锁机制（间隙锁、next-key lock）
第14章节：小结
第15章节：mysql日志管理
慢查询日志
binlog日志
错误日志
事务日志
第16章节：mysql备份与恢复
备份指令：
mysqldump备份---》sql
xtrabackup备份----》备份的是硬盘数据
第17章节：mysql数据快速导入导出
场景：
1、excel表格数据导入mysql中
2、数据库升级背景下的数据迁移操作
第18章节：mysql主从
基于binlog日志（mixed、statement、row）来做主从
主从复制的两种方式
异步复制
半同步复制
主从的复制的架构：
主从
一主多从
双主
双主+ 多从 + keepalive
数据一致性问题
第19章节：mysql的高可用方案MHA
MHA 实现主从的自动故障恢复
第20章节：mysql中间件Atlas、mycat
client---------------atlas--------------》数据库集群
第21章节：mysql数据库优化
redis
一、数据库管理软件
1、数据库管理软件本质就是一个socket套接字软件
2、CS架构
1、数据库管理软件分为两大类
关系型：
mysql、oracle、db2、sqlserver、marriadb
存取都是在硬盘，组织好了数据之间的关系
优点：开发复杂度低
缺点：数据存取速度相对较慢
非关系型：
key:value形式
mongodb（后端数据库管理软件）
redis（多为缓存）
memcache（多为缓存）
存取都是在内存，没有组织数据之间的关系
缺点：开发复杂度高
优点：数据库本身设计复杂度低+内存读写=读写速度非常快
补充：sql语句通用
2、关系型数据库的基本概念
字段：表的标题
记录：文件中的一行内容
表：文件
库：文件夹
数据库管理软件： 本质就是一个socket软件
数据库服务器：运行数据库管理软件服务端的计算机
SQL语句 shell命令
Mysql服务端软件 shell解释器
Linux操作系统 Linux操作系统
计算机硬件 计算机硬件
二、Mysql数据库部署
源码安装
yum安装
二进制安装（二进制包---》已经编译好的源码包）
源码安装
#下载并安装依赖
[root@db01 ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.46.tar.gz
[root@db01 ~]# yum install -y ncurses-devel libaio-devel gcc gcc-c++ glibc cmake autoconf openssl openssl-devel
[root@db01 ~]# tar xf mysql-5.6.46.tar.gz
[root@db01 ~]# mkdir /service #创建目录
#生成cmake
[root@db01 ~]# cd mysql-5.6.46/ #程序存放位置
[root@db01 ~]# cmake . -DCMAKE_INSTALL_PREFIX=/service/mysql-5.6.46 \ #程序存放位置
-DMYSQL_DATADIR=/service/mysql-5.6.46/data \ #数据存放位置
-DMYSQL_UNIX_ADDR=/service/mysql-5.6.46/tmp/mysql.sock \ #socket文件存放位置
-DDEFAULT_CHARSET=utf8mb4 \ #使用utf8mb4字符集
-DDEFAULT_COLLATION=utf8_general_ci \ #校验规则
-DWITH_EXTRA_CHARSETS=all \ #使用其他额外的字符集
-DWITH_INNOBASE_STORAGE_ENGINE=1 \ #支持的存储引擎
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \ #禁用的存储引擎
-DWITH_ZLIB=bundled \ #启用zlib库支持（zib、gzib相关）
-DWITH_SSL=system \ #启用SSL库支持（安全套接层）
-DENABLED_LOCAL_INFILE=1 \ #启用本地数据导入支持
-DWITH_EMBEDDED_SERVER=1 \ #编译嵌入式服务器支持
-DENABLE_DOWNLOADS=1 \ # mysql5.6支持了google的c++mock框架了，允许下载，否则会安装报错。
-DWITH_DEBUG=0 #禁用debug（默认为禁用）
#编译安装，软链接
[root@db01 mysql-5.6.46]# make
[root@db01 mysql-5.6.46]# make install
[root@db01 ~]# ln -s /service/mysql-5.6.46 /service/mysql
[root@db01 ~]# useradd mysql -s /sbin/nologin -M #创建数据库用户
#1.进入配置文件和脚本的目录
[root@db01 ~]# cd /service/mysql/support-files/
#2.拷贝配置和启动文件
[root@db01 support-files]# cp my-default.cnf /etc/my.cnf
#1.配置system管理MySQL
[root@db01 ~]# vim /usr/lib/systemd/system/mysql.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/service/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
#2.重新加载启动文件列表
[root@db01 ~]# systemctl daemon-reload
#初始化数据库
#1.进入初始化目录
[root@db01 support-files]# cd /service/mysql/scripts/
#2.执行初始化命令
[root@db01 scripts]# ./mysql_install_db --user=mysql --basedir=/service/mysql --datadir=/service/mysql/data
#创建socket文件目录（二进制安装没有）
[root@db01 ~]# mkdir /service/mysql/tmp #先创建tmp目录
#授权数据库目录
[root@db01 ~]# chown -R mysql.mysql /service/mysql
[root@db01 ~]# chown -R mysql.mysql /service/mysql-5.6.46
[root@db01 ~]# systemctl start mysql
[root@db01 ~]# ps -ef | grep [m]ysql
[root@db01 ~]# netstat -lntp | grep 3306
#配置环境变量直接使用mysql
[root@db01 ~]# vim /etc/profile.d/mysql.sh
export PATH=/service/mysql/bin:$PATH
[root@db01 ~]# source /etc/profile
[root@db01 ~]# mysql -u root -p #默认root用户密码为空，有多种方式重置root密码
第一种：
MySQL > SET PASSWORD=PASSWORD('123456');
第二种：
MySQL > grant all privileges on *.* to root@'%' identified by 'oldboy'; #创建远程账户
mysql> flush privileges;
注意：两种重置密码方式有区别。
二进制安装
[root@db01 ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.46-linux-glibc2.12-x86_64.tar.gz
[root@db01 ~]# yum install -y ncurses-devel libaio-devel gcc gcc-c++ glibc cmake autoconf
[root@db01 ~]# tar xf mysql-5.6.46-linux-glibc2.12-x86_64.tar.gz
[root@db01 ~]# mkdir /service
#移动目录并改名
[root@db01 ~]# mv mysql-5.6.46-linux-glibc2.12-x86_64 /service/mysql-5.6.46
[root@db01 ~]# ln -s /service/mysql-5.6.46 /service/mysql
[root@db01 ~]# useradd mysql -s /sbin/nologin -M
#1.进入配置文件和脚本的目录
[root@db01 ~]# cd /service/mysql/support-files/
#2.拷贝配置文件
[root@db01 support-files]# cp my-default.cnf /etc/my.cnf
cp: overwrite ‘/etc/my.cnf’? y
#3.拷贝启动脚本
[root@db01 support-files]# cp mysql.server /etc/init.d/mysqld
#1.进入初始化目录
[root@db01 support-files]# cd /service/mysql/scripts/
#2.执行初始化命令
[root@db01 scripts]# ./mysql_install_db --user=mysql --basedir=/service/mysql --datadir=/service/mysql/data
#二进制安装到自定义目录的话----需要修改脚本目录
[root@db01 scripts]# sed -i 's#/usr/local#/service#g' /etc/init.d/mysqld /service/mysql/bin/mysqld_safe
[root@db01 ~]# chown -R mysql.mysql /service/mysql #授权
[root@db01 ~]# chown -R mysql.mysql /service/mysql-5.6.46
#启动
[root@db01 scripts]# /etc/init.d/mysqld start
#1.配置system管理MySQL
[root@db01 ~]# vim /usr/lib/systemd/system/mysql.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/service/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
#2.重新加载启动文件列表
[root@db01 ~]# systemctl daemon-reload
#配置my.cnf
#1.配置指定数据库安装目录与数据目录
[root@db01 scripts]# vim /etc/my.cnf
[mysqld]
basedir=/service/mysql
datadir=/service/mysql/data
#2.启动
[root@db01 scripts]# systemctl start mysql
[root@db01 ~]# vim /etc/profile.d/mysql.sh #配置环境变量直接使用mysql
export PATH=/service/mysql/bin:$PATH
[root@db01 ~]# source /etc/profile
yum安装
[root@db01 ~]# cat /etc/yum.repos.d/mysql-community.repo
# Enable to use MySQL 5.6
[mysql56-community]
name=MySQL 5.6 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.6-community/el/7/$basearch/
enabled=1
gpgcheck=0
[root@db01 ~]# yum repolist enabled | grep mysql
[root@db01 ~]# sudo yum module disable mysql
[root@db01 ~]# sudo yum install mysql-community-server
[root@db01 ~]# sudo service mysqld start
Starting mysqld (via systemctl): [ OK ]
[root@db01 ~]# mysql_secure_installation
三、mysql基本管理
systemctl list-units --type=service | grep -E 'mariadb|mysql' #查询是mysql还是mariadb
1、mysql客户端（/service/mysql/bin）
mysql
mysqladmin
mysqldump
2、mysqld一个二进制程序，后台的守护进程
单进程
多线程
###
客户端 修改配置只需要退出重进即可
服务端 修改配置后还需退出重启服务
/相当于.（数据库中的路径，一般使用绝对路径）
指令结束用；等价于\g
\c取消这条命令 \q相当于quit exit \G 以垂直格式显示 -- mysql中的注释（相当于#）
mysql 客户端 mysqld 服务端
客户端和服务端可以在一台机器上，也可以在不同机器上
/var/lib/mysql 根目录（yum安装的默认数据目录）
/etc/my.cnf 源码安装的默认配置文件路径
utf8mb4 增加表情符号
mysql的启动与关闭
#启动
1、/etc/init.d/mysqld start ---> mysql.server ---> mysqld_safe ---> mysqld
2、systemctl start mysql ---> mysqld_safe ---> mysqld
3、mysqld_safe --defaults-file=/etc/my.cnf ---> mysqld_safe ---> mysqld（安全启动，启动守护进程防止挂掉）
#关闭
/etc/init.d/mysqld stop
systemctl stop mysqld
# 如果是自己用命令如mysqld_safe启动的mysql，可以用mysqladmin来关闭
mysqladmin -uroot -p123 shutdown
# 无论如何都不建议使用
kill -9 pid
killall mysqld
pkill mysqld
# 直接kill数据库可能会出现问题：
1.如果在业务繁忙的情况下，数据库不会释放pid和sock文件
2.号称可以达到和Oracle一样的安全性，但是并不能100%达到
3.在业务繁忙的情况下，丢数据（补救措施，高可用）
#查看
[root@egon ~]# systemctl start mariadb #启动
[root@egon ~]# systemctl enable mariadb #设置开机自启动
[root@egon ~]# ps aux |grep mysqld |grep -v grep #查看进程，mysqld_safe为启动mysql的脚本文件，内部调用mysqld命令
mysql 3329 0.0 0.0 113252 1592 ? Ss 16:19 0:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
mysql 3488 0.0 2.3 839276 90380 ? Sl 16:19 0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
[root@egon ~]# netstat -an |grep 3306 #查看端口
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
[root@egon ~]# ll -d /var/lib/mysql #权限不对，启动不成功，注意user和group
drwxr-xr-x 5 mysql mysql 4096 Jul 20 16:28 /var/lib/mysql
密码设置
# 设置初始密码 由于原密码为空，因此-p可以不用
[root@egon ~]# mysqladmin -uroot password "123"
# 修改mysql密码,因为已经有密码了，所以必须输入原密码才能设置新密码
[root@egon ~]# mysqladmin -uroot -p"123" password "456"
连接方式
1.TCP/IP的连接方式
2.套接字连接方式，socket连接
#查看连接方式
mysql> status;
--------------
Connection: Localhost via UNIX socket
3.举例：
3.1.TCP/IP连接，通常带有-h选项的都是TCP/IP链接
mysql -uroot -p -h127.0.0.1 -P #端口号
mysql -uroot -p -h127.0.0.1 -S /tmp/mysql.sock #多实例登录指定socket
#在连接之前必须先创建root@"%"用户，并赋予权限grant all on *.* to root@"%";
CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'%';
GRANT ALL PRIVILEGES ON testdb.* TO 'newuser'@'%';
FLUSH PRIVILEGES;
3.2.socket连接
mysql -uroot -p123(默认连接方式,socket)
4.注意:
4.1.因为使用TCP/IP连接,需要建立三次握手
4.2.不一定-h都是tcp，-hlocalhost是socket连接
mysql -uroot -p -hlocalhost
重置密码
###1、直接删除授权库
[root@egon ~]# rm -rf /var/lib/mysql/mysql #所有授权信息全部丢失！！！
[root@egon ~]# systemctl restart mariadb
[root@egon ~]# mysql
###2、 skip-grant-tables会跳过mysql的授权表直接进入mysql修改密码
vim /etc/my.cnf
[mysqld]
skip-grant-tables #添加
systemctl restart mysqld
mysql #此时不用输入账号密码就可以登陆
flush privileges;
alter user root@"localhost" identiffied by "123";
###5.7.6之前修改密码
###update mysql.user set password=password("123") where user="root" and host="localhost";
flush privileges;
exit;
vim /etc/my.cnf
skip-grant-tables #删除
systemctl restart mysqld
mysql -uroot -p123 #登录测试
###3、
# 1、先关闭数据库服务
方式一
systemctl stop mysql
方式二
mysqladmin -uroot -p123 -S /tmp/mysql.sock shutdown
# 2、跳过授权表启动mysql
mysqld_safe --skip-grant-tables --skip-networking &
ps：还需要跳过网络，否则在操作过程中很不安全
# 3、登录并修改密码
[root@egon ~]# mysql
MariaDB [(none)]> update mysql.user set password=password("123") where user="root" and host="localhost";
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> \q
# 4、重启服务
systemctl start mysql
# 5、用修改后的密码登录即可
配置文件my.cnf
作用：1.影响服务端的启动（mysqld）
2.影响客户端的连接
1. 在执行mysqld命令时，下列配置会生效，即mysql服务启动时生效
[mysqld]
;skip-grant-tables
port=3306
character_set_server=utf8 #utf8mb4 在写sql语句时这种格式会受影响，加上--no-defaults解决
default-storage-engine=innodb
innodb_file_per_table=1
#解压的目录
basedir=E:\mysql-5.7.19-winx64 #安装目录
# data数据目录
datadir=E:\my_data #在mysqld --initialize时，就会将初始数据存入此处指定的目录，在初始化之后，启动mysql时，就会去这个目录里找数据
#2. 针对客户端命令的全局配置，当mysql客户端命令执行时，下列配置生效
[client]
port=3306
default-character-set=utf8
user=root
password=123
#3. 只针对mysql这个客户端的配置，2中的是全局配置，而此处的则是只针对mysql这个命令的局部配置
[mysql]
;port=3306
;default-character-set=utf8
user=egon #配置后使用mysql指令直接登录无需账号密码，登录账户为配置中的账户
password=4573
#！！！如果没有[mysql],则用户在执行mysql命令时的配置以[client]为准
# 更改服务端mysqld配置需要重启服务，更改mysql客户端配置则无需重启
统一字符编码
1、cmake时指定
cmake .
-DDEFAULT_CHARSET=UTF8 \
-DDEFAULT_COLLATION=UTF8_GENERAL_CI
2、配置文件指定
#1.修改配置文件
[mysqld]
character-set-server=utf8mb4
collation-server=utf8_general_ci
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
#2. 重启服务 systemctl restart mariadb
#3. 查看修改结果：
show variables like '%char%';
简单的sql语句
mysql -u root -pEgon123 -e 'select * from db1.t1' #在mysql之外运行sql语句 -e
#1. 操作文件夹----库
增：create database db1 charset utf8;
数据库已存在会报错，使用IF NOT EXISTS
create database if not exists db1 charset utf8;
查：show databases; show create database db1; select databases();
改：alter database db1 charset latin1;
删除: drop database db1;
#2. 操作文件----表
先切换到文件夹下：use db1
增：create table t1(id int,name char);
查：show tables； desc t1; show create table t1\G;
改：alter table t1 modify name char(3);
alter table t1 change name name1 char(2);
删：drop table t1;
#3. 操作文件中的内容/记录
增：insert into t1 values(1,'egon1'),(2,'egon2'),(3,'egon3');
查：select * from t1;
改：update t1 set name='sb' where id=2;
删：delete from t1 where id=1;
清空表：
delete from t1; #如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。
truncate table t1;数据量大，删除速度比上一条快，且直接从零开始
###
auto_increment 表示：自增
primary key 表示：约束（不能重复且不能为空）；加速查找
mysqladmin命令(服务端)
# 不用登陆Mysql即可执行，作为一个独立的管理工具直接在命令行执行
# mysql----客户端命令
1.修改密码,设置密码:password
[root@db01 ~]# mysqladmin -uroot -p旧密码 password '新密码'
2.关闭MySQL服务:shutdown
[root@db01 ~]# mysqladmin -uroot -p密码 -S socket文件 shutdown
3.库外建库:create
[root@db01 ~]# mysqladmin -uroot -p密码 create egon
[root@db01 ~]# mysql -uroot -p123456 -e 'create database egon'
4.库外删除数据库:drop
[root@db01 ~]# mysqladmin -uroot -p123456 drop egon
Do you really want to drop the 'egon' database [y/N] y
Database "egon" dropped
5.查看配置文件所有的默认参数:variables
[root@db01 ~]# mysqladmin -uroot -p123456 variables
[root@db01 ~]# mysqladmin -uroot -p123456 variables | grep server_id
6.检测MySQL进程是否存活:ping
[root@db01 ~]# mysqladmin -uroot -p123456 ping
7.查看数据库 慢查询,负载信息:status
[root@db01 ~]# mysqladmin -uroot -p123456 status
Uptime MySQL服务器已经运行的秒数
Threads 活跃线程（客户）的数量
Questions 从mysqld启动起来自客户问题的数量 已经发送给服务器的查询的个数
Slow queries 已经超过long_query_time秒的查询数量
Opens mysqld已经打开了多少表
Flush tables flush ..., refresh和reload命令数量
Open tables 现在被打开的表数量
Queries per second avg: 0.046 负载
8.重载授权表,刷新缓存主机:reload，相当于flush privileges
[root@db01 ~]# mysqladmin -uroot -p123456 reload
9.刷新binlog日志
[root@db01 ~]# mysqladmin -uroot -p123456 flush-log
多实例部署
遇到问题先授权再初始化
NGINX多实例就是多个配置文件
mysql多实例：同一个软件启动多次，就是多个进程，每个进程监听的端口不同
1.不同的数据目录
2.不同的端口
3.不同的socket文件
4.不同的日志文件
[root@db01 ~]# mkdir /data/{3307,3308,3309} -p #创建数据目录
## 配置文件
[root@db01 data]# vim /data/3307/my.cnf
[mysqld]
basedir=/service/mysql #安装目录
datadir=/data/3307/data #数据目录
port=3307 #端口
socket=/data/3307/mysql.sock #套接字
log-error=/data/3307/data/mysql.err
log-bin=/data/3307/data/mysql-bin
server_id=7
-------------------------------------------
[root@db01 data]# vim /data/3308/my.cnf
[mysqld]
basedir=/service/mysql
datadir=/data/3308/data
port=3308
socket=/data/3308/mysql.sock
log-error=/data/3308/data/mysql.err
log-bin=/data/3308/data/mysql-bin
server_id=8
--------------------------------------------
[root@db01 data]# vim /data/3309/my.cnf
[mysqld]
basedir=/service/mysql
datadir=/data/3309/data
port=3309
socket=/data/3309/mysql.sock
log-error=/data/3309/data/mysql.err
log-bin=/data/3309/data/mysql-bin
server_id=9
#初始化数据目录
[root@db01 scripts]# ./mysql_install_db --defaults-file=/data/3307/my.cnf --user=mysql --basedir=/service/mysql --datadir=/data/3307/data
[root@db01 scripts]# ./mysql_install_db --defaults-file=/data/3308/my.cnf --user=mysql --basedir=/service/mysql --datadir=/data/3308/data
[root@db01 scripts]# ./mysql_install_db --defaults-file=/data/3309/my.cnf --user=mysql --basedir=/service/mysql --datadir=/data/3309/data
#使用tree可以查看
[root@db01 scripts]# tree -L 3 /data
[root@db01 scripts]# chown -R mysql.mysql /data #授权目录
[root@db01 scripts]# mysqld_safe --defaults-file=/data/3307/my.cnf & #启动遇到问题先授权再初始化
[root@db01 scripts]# mysqld_safe --defaults-file=/data/3308/my.cnf &
[root@db01 scripts]# mysqld_safe --defaults-file=/data/3309/my.cnf &
[root@db01 scripts]# netstat -lntup|grep 330 #检查
tcp6 0 0 :::3307 :::* LISTEN 25550/mysqld
tcp6 0 0 :::3308 :::* LISTEN 25722/mysqld
tcp6 0 0 :::3309 :::* LISTEN 25894/mysqld
#设置密码
[root@db01 scripts]# mysqladmin -uroot -S /data/3307/mysql.sock password '3307'
[root@db01 scripts]# mysqladmin -uroot -S /data/3308/mysql.sock password '3308'
[root@db01 scripts]# mysqladmin -uroot -S /data/3309/mysql.sock password '3309'
#登录验证
#重新定义，便于使用
[root@db01 scripts]# vim /usr/bin/mysql3309
mysql -uroot -p3309 -S /data/3309/mysql.sock
[root@db01 scripts]# vim /usr/bin/mysql3308
mysql -uroot -p3308 -S /data/3308/mysql.sock
[root@db01 scripts]# vim /usr/bin/mysql3307
mysql -uroot -p3307 -S /data/3307/mysql.sock
[root@db01 scripts]# chmod +x /usr/bin/mysql*
### 指定套接字登录用于本地登录，指定ip+端口登录用于远程登录
5.6与5.7的初始化
#5.6初始化命令
/service/mysql/scripts/mysql_install_db --user= --basedir= --datadir=
#5.7初始化命令
/usr/local/mysql/bin/mysqld --initialize --user= --basedir= --datadir=
#初始--initialize会生成一个临时的随机密码，他会告诉你位置，你自己去找使用密码登录，而且修改密码也需要一个很复杂的密码，大小写数字特殊符号
#想不让他生成密码的话，还可以用另外一个参数--initialize-insecure
# yum安装完毕mysql5.7，首次初始启动msyql会生成root的随机密码
# 可以通过下述命令查看
grep "temporary password" /var/log/mysqld.log
# 查看到随机密码后，登录，注意密码用单引号引起来，因为随机密码中可能有特殊字符
mysql -uroot -p '随机密码'
# 设置密码，密码已经不能再用弱密码了，弱密码会报错
set password=password("Egon@123");
四、库
系统数据库（默认）
information_schema： 虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等
performance_schema： MySQL 5.5开始新增一个数据库：主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象
mysql： 授权库，主要存储系统用户的权限信息
test： MySQL数据库系统自动创建的测试数据库
操作文件夹----库
增：create database db1 charset utf8;
数据库已存在会报错，使用IF NOT EXISTS
create database if not exists db1 charset utf8;
查：show databases; show create database db1; select databases();
改：alter database db1 charset latin1;
删除: drop database db1;
五、表
操作文件----表
先切换到文件夹下：use db1
增：create table t1(id int,name char);
查：show tables； desc t1; show create table t1\G;
改：alter table t1 modify name char(3);
alter table t1 change name name1 char(2);
删：drop table t1;
存储引擎
定义
存储引擎------说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法，专门处理其对应的类型的表。
存储引擎决定了表的类型
存储引擎---------表
视频播放---------mp4
文本编辑器-------txt
#InnoDB 存储引擎
#MyISAM 存储引擎
不支持事务、表锁设计、支持全文索引,主要面向一些 OLAP 数 据库应用,在 MySQL 5.5.8 版本之前是默认的存储引擎(除 Windows 版本外)。它的缓冲池只缓存(cache)索引文件,而不缓存数据文件,这与 大多数的数据库都不相同。 （.frm表结构 .MYD表数据 .MYI表索引）
#NDB 存储引擎
特点是数据全部放在内存中(从 5.1 版本开始,可以将非索引数 据放在磁盘上),因此主键查找(primary key lookups)的速度极快,并且能够在线添加 NDB 数据存储节点(data node)以便线性地提高数据库性能。由此可见,NDB 存储引擎是高可用、 高性能、高可扩展性的数据库集群系统,其面向的也是 OLTP 的数据库应用类型。
#Memory 存储引擎
正如其名,Memory 存储引擎中的数据都存放在内存中,数据库重 启或发生崩溃,表中的数据都将消失。它非常适合于存储 OLTP 数据库应用中临时数据的临时表,也可以作为 OLAP 数据库应用中数据仓库的维度表。Memory 存储引擎默认使用哈希 索引,而不是通常熟悉的 B+ 树索引。
#Infobright 存储引擎
第三方的存储引擎。其特点是存储是按照列而非行的,因此非常 适合 OLAP 的数据库应用。
#BLACKHOLE
黑洞存储引擎，可以应用于主备复制中的分发主库。
查看
MariaDB [(none)]> show engines\G #查看所有支持的存储引擎
MariaDB [(none)]> show variables like 'storage_engine%'; #查看正在使用的存储引擎
使用
1、建表时，使用不同的存储引擎
create table t1(id int)engine=blackhole;
create table t2(id int)engine=memory;
create table t3(id int)engine=myisam;
create table t4(id int)engine=innodb;
2、修改配置文件指定默认的存储引擎
/etc/my.cnf
[mysqld]
default-storage-engine=INNODB #指定
innodb_file_per_table=1 # 让每个表都有自己独立的的ibd文件
innodb存储引擎概述
（默认）
----------------三大特性
事务
行级锁：innodb支持行级锁，myisam是表级锁，锁的粒度越小并发能力越强（一次只运行一个，保障安全性）
支持外键
MVCC 多版本并发控制
备份和恢复 innodb支持支持热备，myisam不支持
自动故障恢复 （CSR） Crash Safe Recovery
[root@egon db1]# cd /var/lib/mysql/db1/
[root@egon db1]# ls
db.opt innodb_t1.frm innodb_t1.ibd innodb_t2.frm innodb_t2.ibd
### .frm表的元数据文件（表结构） .ibd表的数据文件+索引文件
数据类型
表内存放的数据的不同类型
bit最小的数据单位----1字节byte=8bit，一个字符占用一个字节
1、整形数据
整形数据的存储宽度(所占字符数)不用指定，是默认的，指定的宽度是显示宽度（一般为默认不用指定）
tinyint int bigint
2、浮点型
float 单精度浮点数
double 双精度浮点数
decimal 用于精确数值的计算
3、日期类型
year
YYYY（1901/2155）
date
YYYY-MM-DD（1000-01-01/9999-12-31）
time
HH:MM:SS（'-838:59:59'/'838:59:59'）
datetime
YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y）
timestamp
YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）
### DATETIME使用8字节的存储空间，TIMESTAMP的存储空间为4字节。因此，TIMESTAMP比DATETIME的空间利用率更高。
### DATETIME的默认值为null；TIMESTAMP的字段默认不为空（not null）,默认值为当前时间（CURRENT_TIMESTAMP）
mysql> create table t1(x datetime not null default now()); # 需要指定传入空值时默认取当前时间
Query OK, 0 rows affected (0.01 sec)
mysql> create table t2(x timestamp); # 无需任何设置，在传空值的情况下自动传入当前时间
Query OK, 0 rows affected (0.02 sec)
4、字符串类型
char(4) 定长，简单粗暴，浪费空间，存取速度快
存储宽度不够用空格占位符自动补够，每个字段长度相同
varchar(4) 变长，精准，节省空间，存取速度慢
varchar自带一个字节的头，用于区分不同的字段，字段大小为实际大小，每个字段长度不定
5、枚举类型（enum）
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');
6、集合类型（set）
CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
约束条件
### 索引就是key
可选参数，用于保证数据的一致性和完整性
约束
primary key 标识该字段为该表的主键，可以唯一的标识记录（innodb一定要有主键）
foreign key 标识该字段为该表的外键
not null 标识该字段不能为空
unique key 标识该字段的值是唯一的
auto_increment 标识该字段的值自动增长（整数类型，而且为主键）
default 为该字段设置默认值
unsigned 无符号
zerofill 使用0填充
示例：
create table t19(
id int unsigned not null default 10
); #无符号不为空默认值为10
create table t20(
id int unique
); #改字段的值都是唯一的，不能重复
主键：
1、约束效果：not null unique
2、加速查询
如果没有主键会找一个不为空且唯一的字段作为主键
如果主键没有也找不到合适的，那么会自动创建一个隐藏主键
主键primary key是innodb存储引擎组织数据的依据，innodb称之为索引组织表，一张表中必须有且只有一个主键。（一般为id）
create table t21(
id int primary key,
name varchar(10),
age int
);
insert t21 values
(1,"egon",18),
(2,"tom",38),
(3,"jack",28),
(4,"lili",48),
(5,"kkkk",58);
select * from t21 where name="tom";
select * from t21 where age=38;
select * from t21 where age>35 and age < 39;
select * from t21 where id=2; # 主键: 能用主键字段作为查询条件就不用其它字段
==================多列做主键================ create table service( ip varchar(15), port char(5), service_name varchar(10) not null, primary key(ip,port) );
create table t23(
id int primary key auto_increment, #自增长，被约束的字段必须同时被key约束
name varchar(10)
);
insert t23(name) values
('egon1'),
('egon2'),
('egon3');
外键
foreign key () references ....() on update cascade on delete cascade
级联更新
级联删除
给表加一个对方的字段让两张表联系起来 同时也防止脏数据出现-------增加的记录中的对应关联字段的值必须在另外一张表对应字段的范围内，否则加入失败
表的类型必须是innodb存储引擎，且被关联的字段（references指的字段）必须保证唯一----unique key 或者 primary key
表与表之间的三种关系
一对一：foreign+unique
多对一：一张表的多条记录可以对上另外一张表的一条记录
多对多：两张表都互相存在多对一
多对一：单向的foreign key
建立多对一必须两张表都存在的情况下给一张表增加另一张表的一个字段
create table class(
id int primary key auto_increment,
name varchar(20),
room int
);
create table student(
id int primary key auto_increment,
name varchar(16),
age int,
gender enum("male","female"),
class_id int,
foreign key(class_id) references class(id)
on update cascade
on delete cascade
);
级联更新
级联删除
insert class(name,room) values
("python16期",403),
("linux12",503),
("linux13",603);
insert student(name,age,gender,class_id) values
("egon",18,"male",1),
("tom",19,"male",1),
("jack",20,"male",1),
("lili",19,"female",2),
("lxx",20,"female",2);
insert student(name,age,gender,class_id) values
("kk",21,"female",4444);
insert student(name,age,gender,class_id) values
("kk",21,"female",3);
多对多：建立中间表，双向的foreign key
对于多对多，由于只有两张表的话互相都进建不起来，所以要引入第三张关系表（先建两表，最后建关系表）
create table book(
id int primary key auto_increment,
name varchar(20)
);
create table author(
id int primary key auto_increment,
name varchar(16)
);
create table book2author(
id int primary key auto_increment,
book_id int,
author_id int,
foreign key(book_id) references book(id) on delete cascade on update cascade,
foreign key(author_id) references author(id) on delete cascade on update cascade
);
一对一：fk+unique
先建被关联得表，再建关联表选择关联字段
create table customer(
id int primary key auto_increment,
name varchar(20),
tel int
);
create table student(
id int primary key auto_increment,
class varchar(10),
customer_id int unique,
foreign key(customer_id) references customer(id) on update cascade on delete cascade
);
设计原则：
能用多对多就不要用多对一
能用多对一就不要用一对一
分析步骤：
#1、先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）
#2、再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）
#3、总结：
#多对一：
如果只有步骤1成立，则是左表多对一右表
如果只有步骤2成立，则是右表多对一左表
#多对多
如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系
#一对一:
如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可
修改表
语法：
1. 修改表名
ALTER TABLE 表名
RENAME 新表名;
2. 增加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…],
ADD 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
### 占用小的字段最好放到最前面
3. 删除字段
ALTER TABLE 表名
DROP 字段名;
4. 修改字段
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…];（不能改字段名）
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
复制表
复制表结构＋记录 （key不会复制: 主键、外键和索引）
mysql> create table new_service select * from service; #用查询到的再建一个表
只复制表结构
mysql> create table new1_service select * from service where 1=2; #条件为假，查不到任何记录所以只复制了结构
六、记录
增
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=’’;
单表查询
select distinct 字段1,字段2,... from 库.表
where 过滤条件（分组前）
group by 分组字段
having 过滤条件（分组后）
order by 排序字段 desc (降序) asc(升序，为默认排序一般不写)
limit 限制查询的记录数
### 先调用select接口，执行from从配置文件datedir找库和表读取记录依照where条件遍历进行过滤生成一张虚拟表到内存中，再进行分组如果没有分组则视整体为一组，对组中的记录再进行过滤，然后运行distinct进行去重最后对记录进行排序，输出结果时依照limit限制的结果显示条数进行输出。------优先级
### 在所有过滤条件完成后才进行一些功能字段的使用例如 distinct avg()
简单查询
例如 select distinct 字段1,字段2,... from 库.表;
#避免重复distinct
SELECT DISTINCT post FROM employee;
#通过四则运算查询
SELECT name, salary*12 FROM employee;
SELECT name, salary*12 AS Annual_salary FROM employee;
SELECT name, salary*12 Annual_salary FROM employee;
#定义显示格式
CONCAT() 函数用于连接字符串
SELECT CONCAT('姓名: ',name,' 年薪: ', salary*12) AS Annual_salary
FROM employee;
CONCAT_WS() 第一个参数为分隔符
SELECT CONCAT_WS(':',name,salary*12) AS Annual_salary
FROM employee;
#case语句------>多分支if判断
SELECT #可以借助这个语句进行有条件的数据迁移
id, #第一个字段
( # ()整体算第二个字段
CASE
WHEN name = 'egon' THEN
concat(name,"_vip")
WHEN name = 'alex' THEN
CONCAT(name,'_BIGSB') # 对字段进行拼接 concat函数
ELSE
name
END
) as new_name, #给这个字段起别名优化
age #第三个字段
FROM
employee;
where子句
1. 比较运算符：> < >= <= <> !=
2. between 80 and 100 值在10到20之间
3. in(80,90,100) 值是10或20或30
4. like 'egon%'
pattern可以是%或_，
%表示任意多字符
_表示一个字符
5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
select * from employee where id=3;
select * from employee where id>3;
select * from employee where id>=3;
select * from employee where id!=3;
select * from employee where id>3 and id<5;
select * from employee where id<3 or id>10;
select * from employee where id>=3 and id<=5;
select * from employee where id between 3 and 5;
select * from employee where id=3 or id=5 or id=7;
select * from employee where id in (3,5,7);
select * from employee where name like 'ji%'; #like模糊查询----正则表达式
select * from employee where name like 'ji_';
select * from employee where name like '__';
select * from employee where name regexp '^jin';
select * from employee where name regexp 'n$';
group by 分组/分类
取出每个部门的平均薪资
select post,avg(salary) from employee group by post;
### 语句没有写where默认where条件永远为真，将所有记录遍历进内存中
### 分组完成后无法查询单条语句，select只能看到分组字段以及聚合的结果
mysql> set global sql_mode='ONLY_FULL_GROUP_BY'; Query OK, 0 rows affected (0.00 sec)
聚合函数
--------分组完毕后，只能查看post字段，如果想查看组内信息，需要借助于聚合函数
avg
max
min
sum
count
group_concat（取分组后的一个或多个字段的所有值）
eg: select post,group_concat(name,":",age) from employee group by post;
取出每个部门的员工数
select post,count(id) from employee group by post;
求男人数与女人数
select sex,count(id) from employee group by sex;
求年龄在20岁以上的男人数与女人数
select sex,count(id) from employee where age > 20 group by sex;
求每个部门20岁以上人的平均薪资
select post,avg(salary) from employee where age > 20 group by post;
select post,avg(salary),max(salary),min(age),count(id),sum(age) from employee group by post;
having过滤
--------可以使用聚合函数
查出平均薪资在10000以上的部门
select post,avg(salary) from employee group by post having avg(salary) > 10000;
查出部门内男员工平均工资在3000以上的部门
select post,avg(salary) from employee where sex="male" group by post
having avg(salary) > 3000;
select max(age) from employee;
order by
select * from employee order by age asc;(默认为升序，asc可以不写)
select * from employee order by age desc;（降序）
select post,avg(salary) from employee where sex="male" group by post
having avg(salary) > 3000
order by avg(salary);
select * from employee order by age asc,id desc; #当一个排序分不出胜负的情况下可以再进行一次排序
limit
------------用于分页
取出薪资最高那个人的信息
select * from employee order by salary desc limit 1;
select * from employee limit 0,5; #从0开始往后取五条（1-5）
select * from employee limit 5,5; #从5开始往后取五条（6-10）
select * from employee limit 10,5;
select * from employee limit 15,5;
###1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
###2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
###3.小结：对字符串匹配的方式
WHERE name = 'egon';
WHERE name LIKE 'yua%';
WHERE name REGEXP 'on$';
多表查询（连接查询）
笛卡尔积
让左表的每一条记录与右表的所有记录都对一遍再从虚拟表中筛选出对应上的记录
### 两张表有重名的字段查询时需要带上表名
select * from employee,department where employee.dep_id = department.id;
select employee.name,department.name from employee,department where employee.dep_id = department.id;
### 查询到的虚拟表可以与已经存在的物理表进行连接 （对查询到的虚拟表使用别名as ）
### 物理表----真实存在的表 虚拟表----查询语句查出的表
表的连接方式
# 内连接：只保留有对应关系的记录
select * from employee inner join department on employee.dep_id = department.id;
# 左连接:在内连接的基础上优先保留左表的记录（左表中没对的上的记录也在）
select * from employee left join department on employee.dep_id = department.id;
# 右连接：在内连接的基础上优先保留右表的记录（右表中没对的上的记录也在）
select * from employee right join department on employee.dep_id = department.id;
# 全外连接：full join
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id;
#左右表合起来再进行去重
示例
create table author(
id int primary key auto_increment,
name varchar(20)
);
create table book(
id int primary key auto_increment,
name varchar(20)
);
create table author2book(
id int primary key auto_increment,
author_id int not null,
book_id int not null,
foreign key(author_id) references author(id) on delete cascade on update cascade,
foreign key(book_id) references book(id) on delete cascade on update cascade
);
insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');
insert into book(name) values
('九阳神功'),
('九阴真经'),
('九阴白骨爪'),
('独孤九剑'),
('降龙十巴掌'),
('葵花宝典')
;
insert into author2book(author_id,book_id) values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(2,1),
(2,6),
(3,4),
(3,5),
(3,6),
(4,1)
;
select * from author inner join author2book
on author.id = author2book.author_id
inner join book
on author2book.book_id = book.id;
子查询
先从一张表中查询结果，然后以该结果作为条件去查询另外一张
### 结果要用()作为条件，结果为单个时用= > < !=，为多个时用in any all等
#1：子查询是将一个查询语句嵌套在另一个查询语句中。
#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
#3：子查询中可以包含：in、not in、any、all、exists 和 not exists等关键字
#4：还可以包含比较运算符：= 、 !=、> 、<等
in：等于其中任意一个即可（可以跟具体的值也可以是查询语句）
any：表示对于任意一个值达成条件即可(和比较运算符搭配)------括号里必须是一个子查询语句
all：表示对于所有结果都要达成条件（一般比较最大，最小，也必须跟一个子查询语句）
exists：表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录，而是返回一个真假值。当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询。 in查询的效率快于exists的查询效率，not exists查询的效率远远高与not in查询的效率。
select * from t1 where id in (1,2,3); where id = any(1,2,3)
select * from t1 where id=1 or id=2 or id=3; ### 三者等价
select * from employee where age in
(select age from employee where age > 48);
select * from employee where age = any
(select age from employee where age > 48);
查询出那些薪资比所有部门的平均薪资都高的员工=》薪资在所有部门平均线以上的资本家
select * from employee where salary > all
(select avg(salary) from employee group by post);
查询出那些薪资比所有部门的平均薪资都低的员工=》薪资在所有部门平均线以下的无产阶级劳苦大众
select * from employee where salary < all
(select avg(salary) from employee group by post);
查询出薪资不垫底的员工=》薪资在任一部门平均线以上的员工
select * from employee where salary > any
(select avg(salary) from employee group by post);
查询出薪资不冒尖的员工=》薪资在任一部门平均线以下的员工
select * from employee where salary < any
(select avg(salary) from employee group by post);
示例
链表查询与子查询----查询每个部门最新入职的那位员工
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,
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
);
#查看表结构
mysql> desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(3) unsigned | NO | | 28 | |
| hire_date | date | NO | | NULL | |
| post | varchar(50) | YES | | NULL | |
| post_comment | varchar(100) | YES | | NULL | |
| salary | double(15,2) | YES | | NULL | |
| office | int(11) | YES | | NULL | |
| depart_id | int(11) | YES | | NULL | |
+--------------+-----------------------+------+-----+---------+----------------+
#插入记录
#三个部门：教学，销售，运营
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',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)
;
1、链表
SELECT
*
FROM
emp AS t1
INNER JOIN (
SELECT
post,
max(hire_date) max_date
FROM
emp
GROUP BY
post
) AS t2 ON t1.post = t2.post
WHERE
t1.hire_date = t2.max_date;
2、子查询
select t3.name,t3.post,t3.hire_date from emp as t3 where id in (select (select id from emp as t2 where t2.post=t1.post order by hire_date desc limit 1) from emp as t1 group by post);
七、视图（了解）
视图就是虚拟表，将虚拟表存到硬盘上（只存表结构不存数据）查询的时候可以直接使用
如果我们程序中使用的sql过分依赖数据库中的视图，即强耦合，那就意味着扩展sql极为不便，因此并不推荐使用
create view 视图名 as sql语句;
alter view teacher_view as select * from course where cid>3; #修改视图（重定义查询语句）
drop view teacher_view; #删除
八、触发器（了解）
可以定制用户对表进行【增、删、改】操作时前后的行为（针对于写操作）
CREATE TRIGGER tri_before_insert_tb1 BEFORE/AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
#创建触发器
delimiter // # 重定义结束符（还用；可能会提前结束）
CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW
BEGIN
IF NEW.success = 'no' THEN #等值判断只有一个等号
INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; #必须加分号
END IF ; #必须加分号
END//
delimiter ; #设置回来
九、函数、流程控制
函数
函数只能在sql语句中使用，不能独立调用
数学函数 聚合函数 字符串函数 日期和时间函数等
重点:
DATE_FORMAT(date,format) 根据format字符串格式化date值
mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
-> 'Sunday October 2009'
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
-> '22:23:00'
mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
-> '%D %y %a %d %m %b %j');
-> '4th 00 Thu 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
-> '%H %k %I %r %T %S %w');
-> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
-> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
-> '00'
自定义
#函数中不要写sql语句（否则会报错），函数仅仅只是一个功能，是一个在sql中被应用的功能
#若要想在begin...end...中写sql，请用存储过程
delimiter //
create function f1(
i1 int,
i2 int)
returns int
BEGIN
declare num int;
set num = i1 + i2;
return(num);
END //
delimiter ;
drop function func_name; #删除
流程控制
if (条件)
while repeat loop （循环）
delimiter //
CREATE PROCEDURE proc_if ()
BEGIN
declare i int default 0;
if i = 1 THEN
SELECT 1;
ELSEIF i = 2 THEN
SELECT 2;
ELSE
SELECT 7;
END IF;
END //
delimiter ;
delimiter //
CREATE PROCEDURE proc_while ()
BEGIN
DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO
SELECT
num ;
SET num = num + 1 ;
END WHILE ;
END //
delimiter ;
十、存储过程
存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql
优点：用于替代程序写的SQL语句，实现程序与sql解耦
基于网络传输，传别名的数据量小，而直接传sql数据量大
缺点：程序员扩展功能不方便
三种形式：
开发人员 DBA
调用存储过程 编写存储过程 （不常用但理论上最好的方案）
编写原生sql 管理好数据库即可
orm框架-》原生sql 管理好数据库即可 （常用）
创建使用：（无参）
delimiter //
create procedure p1()
BEGIN
select * from blog;
INSERT into blog(name,sub_time) values("xxx",now());
END //
delimiter ;
#在mysql中调用
call p1()
#在python中基于pymysql调用
cursor.callproc('p1')
print(cursor.fetchall())
创建使用：（有参）
#in 仅用于传入参数用
#out 仅用于返回值用
#inout 既可以传入又可以当作返回值
delimiter //
create procedure p4(
inout n1 int
)
BEGIN
select * from blog where id > n1;
set n1 = 1;
END //
delimiter ;
#在mysql中调用
set @x=3;
select @x;
call p4(@x);
drop procedure proc_name; #删除
十一、 sql注入
攻击者通过输入一些特殊符号（恶意代码）改变sql语句的运行逻辑（代码拼接后），从而跨过数据库的授权直接进行登录，达到非法访问、修改或删除数据库中数据的目的
（开发人员进行解决）
十二、权限管理
yum安装 /var/lib/mysql
源码安装 自己指定的----datedir=......(配置文件)
mysql库下的授权表
- mysql.user 针对所有数据、所有库下所有表、以及表下的所有字段
- mysql.db 针对某一数据库下所有表、以及表下的所有字段
- tables_priv 只针对某一张表、以及该表下的所有字段
- columns_priv 只针对某一个字段
授权
# all权限----赋予全部权限除了grant权限
grant all on *.* to yyy@'%' identified by '123' with grant option;
#all创建时要包含grant权限需要带上 with grant option
#grant 权限 on 数据库对象 to 用户
grant all on *.* to 'egon'@'%' identified by '123'; #mysql.user
grant all on db1.* to 'tom'@'%' identified by '123'; #mysql.db
grant all on db1.blog to 'lili'@'%' identified by '123'; #tables.priv
grant select (id,sub_time),update (id) on db1.blog to 'xxx'@'%' identified by '123';
#columns_priv 对id,sub_time字段赋予select权限，对id字段赋予更新权限
#查看用户权限
mysql> show grants; 查看当前用户（自己）权限：
mysql> show grants for root@'%';查看其他 MySQL 用户权限：
#撤销用户权限（show--revoke to--from）
revoke all on *.* from dba@localhost;
revoke select on db1.* from 'egon'@'%';
#扩展授权
max_queries_per_hour：一个用户每小时可发出的查询数量
max_updates_per_hour：一个用户每小时可发出的更新数量
max_connections_per_hour：一个用户每小时可连接到服务器的次数
max_user_connections：允许同时连接数量
mysql> grant all on *.* to test@'%' identified by '123' with max_user_connections 1;权限分配
- 普通用户（表中数据的增删改查）
grant select, insert, update, delete on testdb.* to common_user@'%'
- 开发人员
#1.首先进行沟通
1.你需要对哪些库、表进行操作
2.你从哪里连接过来
3.用户名有没有要求
4.密码要求
5.你要使用多长时间
6.发邮件
#2.一般给开发创建用户权限，建议不给delete权限
grant select,update,delete,insert on *.* to egon@'10.0.0.%'' identified by '123';
#3.表结构权限
grant create on testdb.* to developer@'192.168.0.%';
grant alter on testdb.* to developer@'192.168.0.%';
grant drop on testdb.* to developer@'192.168.0.%';
#4.其他权限
grant references on testdb.* to developer@'192.168.0.%';外键
grant create temporary tables on testdb.* to developer@'192.168.0.%';临时表
grant index on testdb.* to developer@'192.168.0.%';索引
grant create view on testdb.* to developer@'192.168.0.%';视图
grant show view on testdb.* to developer@'192.168.0.%';视图
存储过程、函数
grant create routine on testdb.* to developer@'192.168.0.%'; -- now, can show procedure status
grant alter routine on testdb.* to developer@'192.168.0.%'; -- now, you can drop a procedure
grant execute on testdb.* to developer@'192.168.0.%';
- 普通DBA（一整个数据库）
grant all privileges on testdb.* to dba@'localhost'- 高级DBA（所有数据库）
grant all on *.* to dba@'localhost'
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
