MySQL数据库目录
2021/6/29 2:22:10
本文主要是介绍MySQL数据库目录，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
Mysql
文章目录
- MySQL数据库的安装和配置
- MySQL数据库介绍
- MySQL安装部署YUM/编译
- 结构化查询语言SQL介绍
- 数据库基本操作
- MySQL存储引擎
- 创建并管理基本表
- MySQL数据库表操作
- MySQL表的操作DDL
- MySQL数据操作DML
- MySQL数据库的增删改查
- MySQL单表查询
- MySQL多表查询
- 数据类型
- 数值类型
- 字符串类型
- 时间类型
- 数据类型测试
- 约束
- 索引[开发]
- 视图[开发]
- 触发器[开发]
- 存储过程与函数[开发]
- 权限机制
- 安全机制
- 用户管理
- 创建用户并远程登录
- 权限管理
- 不区分大小写
- 日志管理
- MySQL数据库热备
- MySQL备份概述
- MySQL逻辑备份mysqldump
- mysqldump逻辑备份
- **1.常用备份选项**
- 2.备份表
- 3、备份库
- 4、恢复数据库和表
- 5、恢复库
- 6、恢复表
- 7、备份及恢复表结构
- 8、在库里数据的导入导出。没有表结构。
- Mysql bin-log日志恢复
- MySQL物理备份Innobackupex
- **percona-xtrabackup 物理备份 + binlog**
- 1、安装xtrabackup
- 2.完全备份流程:
- 3.增量备份流程
- 4、差异备份流程
- MySQL-Replication
- MySQL Replication
- Replication的原理
- 一主多从
- 关于二进制日志
- 配置Replication
- 配置步骤：
- 主服务器中有数据
- 主服务器中无数据
- 在从站上暂停复制
- 配置Replication(gtid方式)
- 主库配置
- 从库配置
- Replication故障排除
- 开启 GTID 后的导出导入数据的注意点
- UUID一致，导致主从复制I/O线程不是yes
- 数据库中间MyCAT读写分离实现 (重要！！！)
- 部署环境
- 部署Mycat
- 配置Mycat
- 配置 server.xml
- 配置schema.xml
- 启动 mycat
- 在真实的 master 数据库上给用户授权
- 测试
- mysql优化
- 引擎
- 引擎
- 字符集设置
- 慢查询配置
- client连接mysql慢
- mysql错误中文参照列表
MySQL数据库的安装和配置
MySQL数据库介绍
什么是数据库DB？
数据库无处不在
DB的全称是data base，即数据库的意思。数据库实际上就是一个文件集合，是一个存储数据的仓库，本质就是一个文件系统，数据库是按照特定的格式把数据存储起来，用户可以对存储的数据进行增删改查操作；
什么是sql？
SQL代表结构化查询语言(Structured Query Language)。SQL是用于访问数据库的标准化语言。
SQL包含三个部分：
数据定义语言包含定义数据库及其对象的语句，例如表，视图，触发器，存储过程等。
数据操作语言包含允许您更新和查询数据的语句。
数据控制语言允许授予用户权限访问数据库中特定数据的权限。
Mysql是什么？
My是MySQL的联合创始人 - Monty Widenius 的女儿的名字。MySQL是My和SQL的组合，这就是MySQL命名的由来。
MySQL的官方网址： http://www.mysql.com/ ，MySQL的社区版本下载地址为： http://dev.mysql.com/downloads/mysql/ ，在写本文时，当前的MySQL最新版本是：8.0 。
MySQL是一个数据库管理系统，也是一个关系数据库。它是由Oracle支持的开源软件。这意味着任何一个人都可以使用MySQL而不用支付一毛钱。 另外，如果需要，还可以更改其源代码或进行二次开发以满足您的需要。
什么是DBMS？
DBMS的全称是Database Management System，即数据库管理系统的意思，是一个软件，用来管理数据库文件的软件，用户可以访问DBMS对数据进行增删改查操作，常见DBMS有： MySQL、oracle、DB2、sqlite、sqlserver等
MySQL安装部署YUM/编译
硬件需求: 准备两台一样的机器
内存: 至少2G 磁盘: 至少20G CPU: 至少2核心
MySQL部署之yum安装
#初始化服务器 [root@mysql ~]# sed -ri s/SELINUX=enforcing/SELINUX=disabled/g /etc/selinux/config [root@mysql ~]# systemctl stop firewalld && systemctl disable firewalld #安装必要的软件包 [root@mysql ~]# yum -y groupinstall "Development Tools" 安装开发工具包； groupinstall 安装软件包（包含多个单软件及其依赖关系） [root@mysql ~]# wget https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm [root@mysql ~]# rpm -ivh mysql80-community-release-el7-1.noarch.rpm warning: mysql80-community-release-el7-1.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:mysql80-community-release-el7-1 ################################# [100%] [root@mysql ~]# #修改安装mysql的yum源文件 [root@mysql ~]# vim /etc/yum.repos.d/mysql-community.repo 把安装5.7的源打开, 关闭安装8.0的源 [root@mysql ~]# yum -y install mysql-community-server [root@mysql ~]# systemctl start mysqld && systemctl enable mysqld
[root@mysql ~]# grep "password" /var/log/mysqld.log 2018-12-26T22:41:24.218413Z 1 [Note] A temporary password is generated for root@localhost: %i+g10uS.dre #登陆数据库 [root@mysql ~]# mysql -uroot -p"%i+g10uS.dre" mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.24 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> alter user 'root'@'localhost' identified by "GZgz2101.."; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye [root@mysql ~]#
MySQL部署之源码安装
##所需要的依赖及安装mysql的包
$ yum -y update
$ yum -y groupinstall "Development Tools"
$ yum -y install ncurses ncurses-devel bison libgcrypt perl make cmake
$ wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-boost-5.7.24.tar.gz
##在系统中添加运行mysqld进程的用户mysql
[root@mysql_source ~]# groupadd mysql
[root@mysql_source ~]# useradd -M -g mysql -s /sbin/nologin mysql
##在系统中添加自定义mysql数据库目录及其他必要目录
[root@mysql_source ~]# mkdir -p /usr/local/mysqld/{data,mysql,log,tmp}
[root@mysql_source ~]# chown -R mysql:mysql /usr/local/mysqld/*
##将mysql-boost-5.7.24.tar.gz解压到当前目录,并执行部署操作
[root@mysql_source ~]# tar xvzf mysql-boost-5.7.24.tar.gz
[root@mysql_source ~]# cd mysql-5.7.24
[root@mysql_source mysql-5.7.24]#
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysqld/mysql \
-DMYSQL_DATADIR=/usr/local/mysqld/data \
-DWITH_BOOST=/usr/local/mysql-5.7.24/boost \
-DDEFAULT_CHARSET=utf8
......
-- Configuring done
-- Generating done
-- Build files have been written to: /root/mysql-5.7.24
[root@mysql_source mysql-5.7.24]# echo $?
0
[root@mysql_source mysql-5.7.24]# make -j `lscpu | awk 'NR==4{ print $2 }'`
......
[100%] Built target udf_example
[root@mysql_source mysql-5.7.24]# echo $?
0
[root@mysql_source mysql-5.7.24]# make install
......
-- Installing: /usr/local/mysqld/mysql/support-files/mysql.server
[root@mysql_source mysql-5.7.24]# echo $?
0
[root@mysql_source mysql-5.7.24]#
Congratulations Complete!
##初始化MySQL安装配置
1.提升MySQL命令为系统级别命令
[root@mysql_source ~]# cat /etc/profile.d/mysql.sh
MYSQL_HOME=/usr/local/mysqld/mysql
PATH=$PATH:$MYSQL_HOME/bin
export MYSQL_HOME PATH
[root@mysql_source ~]# source /etc/profile.d/mysql.sh
2.拷贝默认配置文件至/etc/my.cnf中
[root@mysql_source mysql]# chown -R mysql.mysql /usr/local/mysqld/*
[root@mysql_source ~]# cd /usr/local/mysqld/mysql/mysql-test/include
[root@mysql_source include]# cp /etc/{my.cnf,my.cnf.bak}
[root@mysql_source include]# cp default_mysqld.cnf /etc/my.cnf
cp：是否覆盖"/etc/my.cnf"？ y
[root@mysql_source include]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysqld/mysql
datadir = /usr/local/mysqld/data
tmpdir = /usr/local/mysqld/tmp
socket = /usr/local/mysqld/tmp/mysql.sock
pid_file = /usr/local/mysqld/tmp/mysqld.pid
log_error = /usr/local/mysqld/log/mysql_error.log
slow_query_log_file = /usr/local/mysqld/log/slow_warn.log
server_id = 11
user = mysql
port = 3306
bind-address = 0.0.0.0
character-set-server = utf8
default_storage_engine = InnoDB
3.执行数据库服务初始化操作
[root@mysql_source mysql]# mysqld --defaults-file=/etc/my.cnf --initialize --user='mysql'
[root@mysql_source mysql]#
4.启动mysqld服务
[root@mysql_source mysql]# mysqld_safe --defaults-file=/etc/my.cnf &
[1] 25705
2018-12-28T09:19:35.334751Z mysqld_safe Logging to '/usr/local/mysqld/log/mysql_error.log'.
2018-12-28T09:19:35.379829Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysqld/data
5.设置mysql.socket软链接到mysql命令指定的目录中
[root@mysql_source ～]# ln -s /usr/local/mysqld/tmp/mysql.sock /tmp/mysql.sock
6.配置mysqld服务的管理工具
[root@mysql_source support-files]# cd /usr/local/mysqld/mysql/support-files
[root@mysql_source support-files]# cp mysql.server /etc/init.d/mysqld
[root@mysql_source support-files]# chkconfig --add mysqld
[root@mysql_source support-files]# chkconfig mysqld on
##登录数据库并进行更改密码
[root@mysql_source mysql]# grep "password" /usr/local/mysqld/log/mysql_error.log
2018-12-28T09:18:34.214401Z 1 [Note] A temporary password is generated for root@localhost: ejhszb2:m3wJ
[root@mysql_source tmp]# mysql -uroot -p"ejhszb2:m3wJ"
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.24-log
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> alter user 'root'@'localhost' identified by "GZgz2101..";
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
企业中常用的MySQL部署参数:<参考使用>
-DCMAKE_INSTALL_PREFIX=/usr/local/mysqld/mysql \
-DMYSQL_DATADIR=/usr/local/mysqld/data \
-DDOWNLOAD_BOOST=1 \
-DWITH_BOOST=/root/mysql-5.7.24/boost \
-DSYSCONFDIR=/etc \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DENABLE_DTRACE=0 \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EMBEDDED_SERVER=1
s
结构化查询语言SQL介绍
数据查询语言（DQL:Data Query Language）：
其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。这些DQL保留字常与其他类型的SQL语句一起使用。
数据操作语言（DML：Data Manipulation Language）：
其语句包括动词INSERT，UPDATE和DELETE。它们分别用于添加，修改和删除表中的行。也称为动作查询语言。
事务处理语言（TPL）：
它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION，COMMIT和ROLLBACK。
数据控制语言（DCL）：
它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。
数据定义语言（DDL）：
其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。
指针控制语言（CCL）：
它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。
重点掌握：
DQL、DML、DCL、DDL
增删改查
库 存放数据的目录
表 文件(普通编辑器打不开)
修饰符
行叫记录
列叫字段 字段名称
神器 edit 在edit里边编辑，wq保存，出来敲一个分号；
--- 下边的都会详细讲
创建库
create database company；
查看库
show databases;
show create databse company； (更详细)
进入库
use company;
创建表
create table t1(id int auto_increment primary key,name char(20),age int);
字段 类型 自动增长 主键 字段 类型 (长度)，字段 类型 (字段后便的就是字段的修饰符(约束条件)
查看表
(show desc(describe) select)
查看表名称
show tables;
desc t1;(表的详细字段信息)
查看表创建过程
show create table t1; <-----
当表特别长的时候；该为\G ，则把表向左旋转90度，可以更清楚看到
第一列显示字段名称
第二列显示记录
查看表结构
desc t1；
查看表记录
select * from 表名;
select 字段,字段 from 表名;
查看表状态
show table status like '表名' ;
修改表
(alter) (add delete change)
修改表名
rename table 原表名 to 新表名;
rename table t11 to t9; alter table t9 rename to t1;
添加字段
alter table 表名 add 字段 修饰符;
alter stable t1 add gender char(20);
alter table t1 add grade char(20) after id;
alter table t1 add zhuanye char(20) first;
删除字段 alter table 表名 drop 字段;
修改字段 alter table 表名 change 旧字段 新字段 修饰符; change修改字段名称，类型，约束，顺序
alter table 表名 modify 字段 属性 修饰符； modify 不能修改字段名称
alter table t1 change zhuanye major varchar(20) after gender;
alter table t1 modify major varchar(20) after age;
修改记录
(inster update delete where)
添加记录
insert into 表名 values (),(),(),();
insert into 表名(字段，字段，字段) values (),(),();
insert into t1 values(1,'2','yingge',18,'12','male');
insert into t1(name,grade) values ('tigerfive','1');
insert into t1(name,grade) values ('youngfit','2'),('houzi','2');
inert into t1 set name='jianjian',grade='1';
修改记录（更新）
update 表名 set 字段=' ' where 主键字段=' ';
update t1 set grade=4 where name='yingge';
删除记录
delete from 表名 where 主键字段=' '; delete from 表名;
delete from t1 where id=5;
删除表 drop table 表名；
drop table t1;
删除库 drop databse 库名；
drop database company;
各种查询
select id,name from employee5 where id<=5;
select id,dep_id,id*dep_id from company.employee5 where id<=5;
select id*dep_id as "id and dep_id's sum" from company.employee5 where id<=5;
utf-8,gb2312
数据库基本操作
MySQL存储引擎
MySQL存储引擎介绍 文件系统： 操作系统组织和存取数据的一种机制。文件系统是一种软件。 类型：ext2 3 4 ，xfs 数据 不管使用什么文件系统，数据内容不会变化 不同的是，存储空间、大小、速度。 MySQL引擎： 可以理解为，MySQL的“文件系统”，只不过功能更加强大。 MySQL引擎功能： 除了可以提供基本的存取功能，还有更多功能事务功能、锁定、备份和恢复、优化以及特殊功能。和磁盘打交道，mysql中组织 存储引擎是一种插拔式的插件方式，提供了一套API标准；是指定在表之上的，即一个库中的每一个表都可以指定专用的存储引擎；不管表采用什么样的存储引擎，都会在数据区，产生对应的一个frm文件（表结构定义描述文件）。
mysql常见的储存引擎
MyISAM、InnoDB(事务型)、MEMORY(内存)、ARCHIVE(归档)等
MyISAM和InnoDB的区别
MyISAM和InnoDB的区别 (了解) 1.Mysql在V5.5之前默认存储引擎是MyISAM；在此开始默认存储引擎是InnoDB 2.InnoDB支持事务，而MyISAM不支持。InnoDB的AUTOCOMMIT默认是打开的，即每条SQL语句会默认被封装成一个事务，自动提交，这样会影响速度，所以最好是把多条SQL语句显示放在begin和commit之间，组成一个事务去提交。 3.InnoDB支持数据行锁定，MyISAM不支持行锁定，只支持锁定整个表。即 MyISAM同一个表上的读锁和写锁是互斥的，MyISAM并发读写时如果等待队列中既有读请求又有写请求，默认写请求的优先级高，即使读请求先到，所以 MyISAM不适合于有大量查询和修改并存的情况，那样查询进程会长时间阻塞。因为MyISAM是锁表，所以某项读操作比较耗时会使其他写进程饿死。 4.InnoDB支持外键，MyISAM不支持。 5.InnoDB的主键范围更大，最大是MyISAM的2倍。 6.InnoDB不支持全文索引，而MyISAM支持。全文索引是指对char、 varchar和text中的每个词（停用词除外）建立倒排序索引。MyISAM的全文索引其实没啥用，因为它不支持中文分词，必须由使用者分词后加入空 格再写到数据表里，而且少于4个汉字的词会和停用词一样被忽略掉。 MyISAM支持GIS数据，InnoDB不支持。即MyISAM支持以下空间数据对象：Point,Line,Polygon,Surface等。 没有where的count(*)使用MyISAM要比InnoDB快得多。因 为MyISAM内置了一个计数器，count(*)时它直接从计数器中读，而InnoDB必须扫描全表。所以在InnoDB上执行count(*)时一般 要伴随where，且where中要包含主键以外的索引列。为什么这里特别强调“主键以外”？因为InnoDB中primary index是和raw data存放在一起的，而secondary index则是单独存放，然后有个指针指向primary key。所以只是count(*)的话使用secondary index扫描更快，而primary key则主要在扫描索引同时要返回raw data时的作用较大。
创建并管理基本表
[root@mysql ~]# mysql -uroot -p"(Tiger..1228)" #登录数据库
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.24 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases; ##查询数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database tiger; ##创建数据库
Query OK, 1 row affected (0.00 sec)
mysql> flush privileges; ##刷新数据库及表的列表内容(常用于授权之后)
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| tiger |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> drop database tiger; ##删除数据库
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> 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
mysql> show tables; ##查看库中的表
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
mysql> select * from mysql.user\G ##查询表中的内容,“\G”为标准化输出,不加会乱码
*************************** 1. row ***************************
Host: localhost
User: root
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *7C80E5F2B2DC4B10B1D2CFF160F5C8240720B612
password_expired: N
password_last_changed: 2018-12-28 15:18:57
password_lifetime: NULL
account_locked: N
mysql> select User,Host,authentication_string from user; ##根据条件查询表中的内容
+---------------+-----------+-------------------------------------------+
| User | Host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | *5CD4C2B38AEF87547D2735419AECEC731E44C1B8 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)
mysql> update user set authentication_string=password('(Bavdu..1229)') where user='root' and Host='localhost';
Query OK, 1 row affected, 1 warning (0.00 sec) ##更新表中的内容
Rows matched: 1 Changed: 1 Warnings: 1
mysql> exit ##退出数据库
Bye
[root@mysql ~]#
用户修改密码
[root@mysql ~]# vim /etc/my.cnf
[mysqld]
skip-grant-tables=1
[root@mysql ~]# systemctl restart mysqld
[root@mysql ~]# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.24 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> alter user 'root'@'localhost' identified by "(tiger..1229)";
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> 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
mysql> show tables;
......
......
......
| user |
+---------------------------+
31 rows in set (0.00 sec)
mysql> select User,Host,authentication_string from user;
+---------------+-----------+-------------------------------------------+
| User | Host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | *C4571A0C807D96143700250EC4BA41780025A97F |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)
mysql> update user set authentication_string=password('(tiger@@1229)') where user='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
[root@mysql ~]# vim /etc/my.cnf
[mysqld]
#skip-grant-tables=1
[root@mysql ~]# systemctl restart mysqld
[root@mysql ~]# mysql -uroot -p"(tiger@@1229)"
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.24 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
思考题:
Q: 通常在企业内部我们部署完一台mysql服务器会很慢, 但是为了应对企业成百上千台的要求, 我们的部署显得很捉襟见肘, 那么我们在部署完一台机器之后如何避免漫长的等待快速部署很多台符合要求的mysql服务器呢？
MySQL数据库表操作
MySQL表的基本概念
在windows中有个程序叫做excel. 而Excel文件中存在了如sheet1、sheet2、sheet3的表, 所有的sheet都存储在这个Excel文件中, 在某个sheet中有相应的数据.
回到数据库和表的关系上来说, 这个Excel文件就是一个数据库, 所有的sheet就是存储在库中的表, 表中去存储数据, 而我们学习的MySQL程序就是Excel程序,它是用来集中管理这些Excel文件的工具. 而我们在工作中又称这种机制类型为: 关系型数据库
MySQL表的操作DDL
数据库DDL操作
系统数据库
information_schema： 虚拟库，主要存储了系统中的一些数据库对象的信息，例如用户表信息、列信息、权限信息、字符信息等
performance_schema： 主要存储数据库服务器的性能参数
mysql： 授权库，主要存储系统用户的权限信息
sys： 主要存储数据库服务器的性能参数
创建数据库：DDL
1. #mysqladmin -u root -p1 create db1
2. 直接去创建数据库目录并且修改权限
3. mysql> create database tiger;
数据库命名规则：
区分大小写
唯一性
不能使用关键字如 create select
不能单独使用数字
查看数据库
mysql> show databases;
mysql> show create database tiger;
mysql> select database();
切换数据库
mysql> use tiger;
mysql> show tables;
删除数据库
DROP DATABASE 数据库名;
表的DDL操作
这些操作都是数据库管理中最基本，也是最重要的操作。
内容包括:
创建表 create table
查看表结构 desc table, show create table
表完整性约束
修改表 alter table
复制表 create table ...
删除表 drop table
表是数据库存储数据的基本单位，由若干个字段组成，主要用来存储数据记录。
使用编辑器编辑指令
mysql> edit
mysql> \e
命令行操作数据库（脚本）
# mysql -u root -p1 -e "use db2;create table t1(name char(20),pass char(100));insert into t3 set name='tiger',pass=password('123')"
在mysql客户端内执行系统命令
mysql> system ls
mysql> \! ls
创建表
表:school.student1
字段 字段 字段
id name sex age
1 tom male 23 记录
2 jack male 21 记录
3 alice female 19 记录
语法：
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
)[存储引擎 字符集];
==在同一张表中，字段名是不能相同
==宽度和约束条件可选
==字段名和类型是必须的
mysql> CREATE DATABASE school;
mysql> use school;
mysql> create table student1(
-> id int,
-> name varchar(50),
-> sex enum('m','f'),
-> age int
-> );
Query OK, 0 rows affected (0.03 sec)
查看表（当前所在库）
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student1 |
+------------------+
1 row in set (0.00 sec)
mysql> desc student1;
mysql> show create table student1;
mysql> show table status like 'student1' \G
查看表内容（后面单独讲表查询操作）
mysql> select id,name,sex,age from student1; //查询表中所有字段的值
Empty set (0.00 sec)
mysql> select * from student1; //查询表中所有字段的值
Empty set (0.00 sec)
mysql> select name,age from student1; //查询表中指定字段的值
Empty set (0.00 sec)
修改表：
alter 修改表名称 修改字段名称 修改字段数据类型 修改字段的修饰符
insert 插入数据
delete 删除数据
update 更新数据
修改表名称
mysql> rename table emp to abc;
mysql> alter table abc rename emp;
添加新字段
mysql> alter table t1 add math int(10);
mysql> alter table t1 add (chinese int(10),english int(10));
修改字段数据类型、修饰符
mysql> alter table t1 modify chinese int(5) not null;
修改名称、数据类型、修饰符
mysql> alter table t1 change chinese china int(6);
first after
mysql> alter table t1 change english en int(6) after id;
mysql> alter table t1 modify en int(6) first;
删除字段
mysql> alter table t1 drop en;
插入数据(添加记录)
字符串必须引号引起来
mysql> insert into t1(id,name,math,china) values(1,"tigerfive",80,90);
mysql> insert into t1(id,name,math,china) values(2,"king",70,100),(3,"tom",50,70);
mysql> insert into t1 values(4,"xiaosan",50,100);
mysql> insert into t1(id,math) values(5,70);
mysql> insert into t1 set id=6,math=65; X
更新记录
mysql> update t1 set name="lili" where id=5;
删除记录
mysql> delete from t1 where id=6;
mysql> delete from t1; //删除所有记录
表复制：key不会被复制: 主键、外键和索引
复制一张表
mysql> create table t10(select * from t3);
mysql> create table t10(select id,name from t3);
复制表结构
mysql> create table t4(select * from t3 where 5=4);
mysql> create table t4(select id,name from t3 where 5=4);
复制记录
mysql> insert into t3 select * from t10 where id=9;
删除表
mysql> drop table t1;
删除库
mysql> drop database gnu;
了解(！！！)
表完整性约束
作用：用于保证数据的完整性和一致性
==============================================================
约束条件 说明
PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录，不可以为空 UNIQUE + NOT NULL
FOREIGN KEY (FK) 标识该字段为该表的外键，实现表与表（父表主键/子表1外键/子表2外键）之间的关联
NOT NULL 标识该字段不能为空
UNIQUE KEY (UK) 标识该字段的值是唯一的，可以为空，一个表中可以有多个UNIQUE KEY
AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT 为该字段设置默认值
UNSIGNED 无符号，正数
ZEROFILL 使用0填充，例如0000001
说明：
1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
sex enum('male','female') not null default 'male'
age int unsigned NOT NULL default 20 必须为正值（无符号） 不允许为空 默认是20
3. 是否是key
主键 primary key
外键 forengn key
索引 (index,unique...)
==============================================================
MySQL数据操作DML
在MySQL管理软件中，可以通过SQL语句中的DML语言来实现数据的操作，包括使用INSERT实现数据
的插入、DELETE实现数据的删除以及UPDATE实现数据的更新。
插入数据 insert
更新数据 update
删除数据 delete
一、插入数据INSERT
1. 插入完整数据（顺序插入）
语法一：
INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES (值1,值2,值3…值n);
语法二：
INSERT INTO 表名 VALUES (值1,值2,值3…值n);
2. 指定字段插入数据
语法：
INSERT INTO 表名(字段2,字段3…) VALUES (值2,值3…);
3. 插入多条记录
语法：
INSERT INTO 表名 VALUES
(值1,值2,值3…值n),
(值1,值2,值3…值n),
(值1,值2,值3…值n);
4. 插入查询结果
语法：
INSERT INTO 表1(字段1,字段2,字段3…字段n)
SELECT (字段1,字段2,字段3…字段n) FROM 表2
WHERE …;
mysql> inster into studen7 select * from studen6 where age > 17;
二、更新数据UPDATE
语法：
UPDATE 表名 SET
字段1=值1,
字段2=值2,
WHERE CONDITION;
示例：
三、删除数据DELETE
语法：
DELETE FROM 表名
WHERE CONITION;
示例：
DELETE FROM mysql.user
WHERE authentication_string=’’;
作业：
更新MySQL root用户密码Tianyun520^&*
注：表的修改作为课下作业
MySQL数据库的增删改查
MySQL数据库查询
MySQL单表查询
单表查询
======================================
测试表：company.employee5
雇员编号 id int
雇员姓名 name varchar(30)
雇员性别 sex enum
雇用时期 hire_date date
职位 post varchar(50)
职位描述 job_description varchar(100)
薪水 salary double(15,2)
办公室 office int
部门编号 dep_id int
mysql> CREATE TABLE company.employee5(
id int primary key AUTO_INCREMENT not null COMMENT "id",
name varchar(30) not null COMMENT "名字",
sex enum('male','female') default 'male' not null COMMENT "性别",
hire_date date not null COMMENT "入职时间",
post varchar(50) not null COMMENT "职位",
job_description varchar(100) COMMENT "工作描述",
salary double(15,2) not null COMMENT "薪资",
office int COMMENT "办公室",
dep_id int COMMENT "职位编号"
);
mysql> insert into company.employee5(name,sex,hire_date,post,job_description,salary,office,dep_id) values
('jack','male','20180202','instructor','teach',5000,501,100),
('tom','male','20180203','instructor','teach',5500,501,100),
('robin','male','20180202','instructor','teach',8000,501,100),
('alice','female','20180202','instructor','teach',7200,501,100),
('tianyun','male','20180202','hr','hrcc',600,502,101),
('harry','male','20180202','hr',NULL,6000,502,101),
('emma','female','20180206','sale','salecc',20000,503,102),
('christine','female','20180205','sale','salecc',2200,503,102),
('zhuzhu','male','20180205','sale',NULL,2200,503,102),
('gougou','male','20180205','sale','',2200,503,102);
======================================
mysql> select 字段名称,字段名称2 from 表名 where 条件
mysql>select column_name,column_2 from table where ...
简单查询：
mysql> select * from t3;
mysql> select name, salary, dep_id from employee5;
避免重复DISTINCT
SELECT post FROM employee5;
SELECT DISTINCT post FROM employee5;
注：不能部分使用DISTINCT，通常仅用于某一字段。
通过四则运算查询
运算：
mysql>select math*china-50 from t1;
mysql>select 437.4384/5
mysql>select 5>3;
SELECT name, salary, salary*14 FROM employee5;
SELECT name, salary, salary*14 AS Annual_salary FROM employee5;
SELECT name, salary, salary*14 Annual_salary FROM employee5;
定义显示格式
CONCAT() 函数用于连接字符串
SELECT CONCAT(name, ' annual salary: ', salary*14) AS Annual_salary FROM employee5;
单条件查询
mysql> select pass from t3 where name="tigerfive";
多条件查询
mysql>select math from db1.t1 where math>50 and math<600
mysql>mysqlselect math from db1.t1 where not math>50;
关键字BETWEEN AND
SELECT name,salary FROM employee5
WHERE salary BETWEEN 5000 AND 15000;
SELECT name,salary FROM employee5
WHERE salary NOT BETWEEN 5000 AND 15000;
关键字IS NULL
SELECT name,job_description FROM employee5
WHERE job_description IS NULL;
SELECT name,job_description FROM employee5
WHERE job_description IS NOT NULL;
SELECT name,job_description FROM employee5
WHERE job_description='';
NULL说明：
1、等价于没有任何值、是未知数。
2、NULL与0、空字符串、空格都不同,NULL没有分配存储空间。
3、对空值做加、减、乘、除等运算操作，结果仍为空。
4、比较时使用关键字用“is null”和“is not null”。
5、排序时比其他数据都小（索引默认是降序排列，小→大），所以NULL值总是排在最前。
关键字IN集合查询
SELECT name, salary FROM employee5
WHERE salary=4000 OR salary=5000 OR salary=6000 OR salary=9000 ;
SELECT name, salary FROM employee5
WHERE salary IN (4000,5000,6000,9000) ;
SELECT name, salary FROM employee
WHERE salary NOT IN (4000,5000,6000,9000) ;
排序查询
mysql> select china from t1 order by china;
mysql> select china from t1 order by china desc;
mysql> select china from t1 order by china desc limit 3;
mysql> select china from t1 order by china desc limit 1,3;
注：
ascending 美音 /ə'sɛndɪŋ/ 升序
descending 美音 /dɪ'sɛndɪŋ/ 降序
按多列排序:
入职时间相同的人薪水不同
SELECT * FROM employee5
ORDER BY hire_date DESC,
salary ASC;
先按入职时间，再按薪水排序
select * from emp ORDER BY hire_date DESC,salary DESC;
先按职位，再按薪水排序
select * from emp ORDER BY post,salary DESC;
限制查询的记录数
SELECT * FROM employee5 ORDER BY salary DESC
LIMIT 5; //默认初始位置为0
SELECT * FROM employee5 ORDER BY salary DESC
LIMIT 0,5;
SELECT * FROM employee5 ORDER BY salary DESC
LIMIT 3,5; //从第4条开始，共显示5条
分组查询
mysql> select count(gender),gender from t3 group by gender;
GROUP BY和GROUP_CONCAT()函数一起使用
SELECT dep_id,GROUP_CONCAT(name) FROM employee5 GROUP BY dep_id;
SELECT dep_id,GROUP_CONCAT(name) as emp_members FROM employee5 GROUP BY dep_id;
GROUP BY和集合函数一起使用
select dep_id,SUM(salary) FROM emp GROUP BY dep_id;
select dep_id,AVG(salary) FROM emp GROUP BY dep_id;
模糊查询(通配符)
_ 任意单个字符
% 所有字符
mysql> select * from t1 where china='1__';
mysql> select * from t1 where china like '%0%';
正则查询
mysql> select * from t1 where china regexp '10+';
SELECT * FROM employee5 WHERE name REGEXP '^ali';
SELECT * FROM employee5 WHERE name REGEXP 'yun$';
SELECT * FROM employee5 WHERE name REGEXP 'm{2}';
子查询
mysql> select name from t2 where math=(select max(math) from t2);
函数
count()
max()
min()
avg()
database()
user()
now()
sum()
password()
md5()
sha1()
power()
SELECT COUNT(*) FROM employee5;
SELECT COUNT(*) FROM employee5 WHERE dep_id=101;
SELECT MAX(salary) FROM employee5;
SELECT MIN(salary) FROM employee5;
SELECT AVG(salary) FROM employee5;
SELECT SUM(salary) FROM employee5;
SELECT SUM(salary) FROM employee5 WHERE dep_id=101;
MariaDB [company]> select password(5);
+-------------------------------------------+
| password(5) |
+-------------------------------------------+
| *7534F9EAEE5B69A586D1E9C1ACE3E3F9F6FCC446 |
+-------------------------------------------+
MariaDB [company]> select md5(5);
+----------------------------------+
| md5(5) |
+----------------------------------+
| e4da3b7fbbce2345d7772b0674a318d5 |
+----------------------------------+
1 row in set (0.00 sec)
MariaDB [company]> select sha1(5);
+------------------------------------------+
| sha1(5) |
+------------------------------------------+
| ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
+------------------------------------------+
1 row in set (0.00 sec)
别名
mysq>select name as xingming,pass from db2.t3
MySQL多表查询
多表查询
多表连接查询
复合条件连接查询
子查询
一、准备两张测试表
表company.employee6
mysql> create table employee6(
emp_id int auto_increment primary key not null COMMENT "id",
emp_name varchar(50) COMMENT "名字",
age int COMMENT "年龄",
dept_id int COMMENT "职位ID" );
mysql> desc employee6;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| emp_id | int(11) | NO | PRI | NULL | auto_increment |
| emp_name | varchar(50) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| dept_id | int(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
mysql> insert into employee6(emp_name,age,dept_id) values
('tianyun',19,200),
('tom',26,201),
('jack',30,201),
('alice',24,202),
('robin',40,200),
('natasha',28,204);
mysql> select * from employee6;
+--------+----------+------+---------+
| emp_id | emp_name | age | dept_id |
+--------+----------+------+---------+
| 1 | tianyun | 19 | 200 |
| 2 | tom | 26 | 201 |
| 3 | jack | 30 | 201 |
| 4 | alice | 24 | 202 |
| 5 | robin | 40 | 200 |
| 6 | natasha | 28 | 204 |
+--------+----------+------+---------+
表company.department6
mysql> create table department6(
dept_id int COMMENT "职位ID",
dept_name varchar(100) COMMENT "职位名字"
);
mysql> desc department6;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| dept_id | int(11) | YES | | NULL | |
| dept_name | varchar(100) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
mysql> insert into department6 values
(200,'hr'),
(201,'it'),
(202,'sale'),
(203,'fd');
mysql> select * from department6;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 200 | hr |
| 201 | it |
| 202 | sale |
| 203 | fd |
+---------+-----------+
注：
Financial department:财务部门 fd
二、多表的连接查询
交叉连接： 生成笛卡尔积，它不使用任何匹配条件 自己了解就好，这个生产用会把数据库跑死
内连接： 只连接匹配的行
外连接
左连接： 会显示左边表内所有的值，不论在右边表内匹不匹配
右连接： 会显示右边表内所有的值，不论在左边表内匹不匹配
全外连接： 包含左、右两个表的全部行
=================内连接=======================
两种方式：
方式1：使用where条件
方式2：使用inner join
只找出有部门的员工 (部门表中没有natasha所在的部门)
mysql> select employee6.emp_id,employee6.emp_name,employee6.age,departmant6.dept_name
from employee6,departmant6
where employee6.dept_id = departmant6.dept_id;
+--------+----------+------+-----------+
| emp_id | emp_name | age | dept_name |
+--------+----------+------+-----------+
| 1 | tianyun | 19 | hr |
| 2 | tom | 26 | it |
| 3 | jack | 30 | it |
| 4 | alice | 24 | sale |
| 5 | robin | 40 | hr |
+--------+----------+------+-----------+
使用别名：
> select a.emp_id,a.emp_name,a.age,b.dept_name from employee6 a,departmant6 b where a.dept_id = b.dept_id;
+--------+----------+------+-----------+
| emp_id | emp_name | age | dept_name |
+--------+----------+------+-----------+
| 1 | tianyun | 19 | hr |
| 2 | tom | 26 | it |
| 3 | jack | 30 | it |
| 4 | alice | 24 | sale |
| 5 | robin | 40 | hr |
+--------+----------+------+-----------+
使用inner join
select a.clu1,a.clu2,b.clu1,b.clu2 from a inner join b on a.clu=b.clu
> select a.emp_id,a.emp_name,a.age,b.dept_name from employee6 a inner join departmant6 b on a.dept_id = b.dept_id;
+--------+----------+------+-----------+
| emp_id | emp_name | age | dept_name |
+--------+----------+------+-----------+
| 1 | tianyun | 19 | hr |
| 2 | tom | 26 | it |
| 3 | jack | 30 | it |
| 4 | alice | 24 | sale |
| 5 | robin | 40 | hr |
+--------+----------+------+-----------+
======================================
外连接语法：
SELECT 字段列表
FROM 表1 LEFT|RIGHT JOIN 表2
ON 表1.字段 = 表2.字段;
=================外连接(左连接 left join)=======================
mysql> select emp_id,emp_name,dept_name from employee6 left join department6 on employee6.dept_id = department6.dept_id;
找出所有员工及所属的部门，包括没有部门的员工
=================外连接(右连接right join)=======================
mysql> select emp_id,emp_name,dept_name from employee6 right join department6 on employee6.dept_id = department6.dept_id;
找出所有部门包含的员工，包括空部门
=================全外连接(了解)=======================
> select * from employee6 full join departmant6;
+--------+----------+------+---------+---------+-----------+
| emp_id | emp_name | age | dept_id | dept_id | dept_name |
+--------+----------+------+---------+---------+-----------+
| 1 | tianyun | 19 | 200 | 200 | hr |
| 1 | tianyun | 19 | 200 | 201 | it |
| 1 | tianyun | 19 | 200 | 202 | sale |
| 1 | tianyun | 19 | 200 | 203 | fd |
| 2 | tom | 26 | 201 | 200 | hr |
| 2 | tom | 26 | 201 | 201 | it |
| 2 | tom | 26 | 201 | 202 | sale |
| 2 | tom | 26 | 201 | 203 | fd |
| 3 | jack | 30 | 201 | 200 | hr |
| 3 | jack | 30 | 201 | 201 | it |
| 3 | jack | 30 | 201 | 202 | sale |
| 3 | jack | 30 | 201 | 203 | fd |
| 4 | alice | 24 | 202 | 200 | hr |
| 4 | alice | 24 | 202 | 201 | it |
| 4 | alice | 24 | 202 | 202 | sale |
| 4 | alice | 24 | 202 | 203 | fd |
| 5 | robin | 40 | 200 | 200 | hr |
| 5 | robin | 40 | 200 | 201 | it |
| 5 | robin | 40 | 200 | 202 | sale |
| 5 | robin | 40 | 200 | 203 | fd |
| 6 | natasha | 28 | 204 | 200 | hr |
| 6 | natasha | 28 | 204 | 201 | it |
| 6 | natasha | 28 | 204 | 202 | sale |
| 6 | natasha | 28 | 204 | 203 | fd |
+--------+----------+------+---------+---------+-----------+
三、复合条件连接查询
示例1：以内连接的方式查询employee6和department6表，并且employee6表中的age字段值必须大于25
找出公司所有部门中年龄大于25岁的员工
select emp_id,emp_name,age,dept_name FROM employee6,department6 WHERE employee6.dept_id=department6.dept_id AND age > 25;
示例2：以内连接的方式查询employee6和department6表，并且以age字段的升序方式显示
SELECT emp_id,emp_name,age,dept_name FROM employee6,department6 where employee6.dept_id=depaartment6.dept_id ORDER BY age asc;
四、子查询
子查询是将一个查询语句嵌套在另一个查询语句中。
内层查询语句的查询结果，可以为外层查询语句提供查询条件。
子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
还可以包含比较运算符：= 、 !=、> 、<等
1. 带IN关键字的子查询
查询employee表，但dept_id必须在department表中出现过
SELECT * FROM employee6 WHERE dept_id IN (SELECT dept_id FROM department6);
2. 带比较运算符的子查询
=、!=、>、>=、<、<=、<>
查询年龄大于等于25岁员工所在部门（查询老龄化的部门）
SELECT dept_id,dept_name FROM department6 WHERE dept_id IN (SELECT DISTINCT dept_id FROM employee6 WHERER age >= 25);
3. 带EXISTS关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录，而是返回一个真假值。
True或False，当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
department表中存在dept_id=203，Ture
SELECT * from employee6 WHERE EXISTS (SELECT * FROM depratment6 WHERE dept_id=203);
后续会通过sql精进教程重点强化
数据类型
数据类型
在MySQL数据库管理系统中，可以通过存储引擎来决定表的类型。同时，MySQL数据库管理系统也
提供了数据类型决定表存储数据的类型。
常见的数据类型
姓名: char(10) varchar(10) 最多存10个
数值类型：
整数类型 TINYINT SMALLINT MEDIUMINT INT BIGINT
浮点数类型 FLOAT DOUBLE
定点数类型 DEC
位类型 BIT
字符串类型：
CHAR系列 CHAR VARCHAR
TEXT系列 TINYTEXT TEXT MEDIUMTEXT LONGTEXT
BLOB 系列 TINYBLOB BLOB MEDIUMBLOB LONGBLOB
BINARY系列 BINARY VARBINARY
枚举类型： ENUM
集合类型： SET
时间和日期类型： DATE TIME DATETIME TIMESTAMP YEAR
数值类型
数值类型
整型
作用：用于存储用户的年龄、游戏的Level、经验值等。
分类：tinyint smallint mediumint int bigint
有符号 ：
有正负数
无符号 ：
没有负数 类型后面使用unsigned和zerofill修饰符
tinyint（最短整型，后面(+宽度，默认存在为4，显示宽度)） 最多存储1个字节或者8个bit
smallint 2个字节 后面(+宽度，默认存在为6，显示宽度)
mediumint 3个字节 后面(+宽度，默认存在为9，显示宽度)
int 4个字节 后面(+宽度，默认存在为11，显示宽度)
bigint 5个字节 后面(+宽度，默认存在为20，显示宽度)
将有符号变成无符号：
unsigned:
mysql> create table t6(id bigint(2) unsigned);
zerofill:
mysql> create table t2 (
-> id1 int zerofill,
-> id2 int(6)-显示宽度 zerofill
-> );
存储数据大小范围：
1个字节 2个字节 3个字节 4个字节 8个字节
8bit 16 24 32 64
11111111
无符号：范围运算公式 0到2^n-1
比如：
tinyint:0到255
bigint: 0到2^64-1
有符号：范围运算公式 -2^(n-1)到2^(n-1)-1
比如：
tinyint:-128到127
显示宽度：
类型后面小括号内的数字是显示宽度，不能限制插入数值的大小
比如：bigint(2) 2是显示宽度
浮点型 ：单精度float(4)和双精度double
作用：用于存储用户的身高、体重、薪水等
float(5.3) 5宽度 3精度
宽度不算小数点
mysql> create table t12(id float(6,2));
double(5,3)
定点型
定点数在MySQL内部以字符串形式存储，比浮点数更精确，适合用来表示货币等精度高的数据。
decimal(5,3)
位类型
BIT(M)可以用来存放多位二进制数，M范围从1~64，如果不写默认为1位
对于位字段可以使用函数读取：
bin()显示为二进制
hex()显示为十六进制
mysql> create table test_bit (id bit(4)); //4bit能存储的最大值为15
mysql> desc test_bit;
+-------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id | bit(4) | YES | | NULL | |
+-------+--------+------+-----+---------+-------+
mysql> insert into test_bit values(4);
mysql> select * from test_bit;
+------+
| id |
+------+
| |
+------+
mysql> select bin(id),hex(id) from test_bit;
+---------+---------+
| bin(id) | hex(id) |
+---------+---------+
| 100 | 4 |
+---------+---------+
1 row in set (0.00 sec)
字符串类型
字符串类型
作用：用于存储用户的姓名、爱好、发布的文章等
======================================
字符类型 char varchar
char(10) 根据10，占10个.
列的长度固定为创建表时声明的长度： 0 ~ 255
varchar(10) 根据实际字符串长度占空间，最多10个
列中的值为可变长字符串，长度： 0 ~ 65535
default charset=utf8 改变字符集
在检索的时候，CHAR列删除了尾部的空格，而VARCHAR则保留这些空格
mysql> create table vc (
-> v varchar(4),
-> c char(4)
-> );
二进制类型 BINARY和VARBINARY
与 CHAR和VARCHAR类型有点类似，不同的是BINARY和VARBINARY存储的是二进制的字符串，而非字符型字符串。也就是说，BINARY和VARBINARY没有字符集的概念，对其排序和比较都是按照二进制值进行对比。
BINARY（N）和VARBINARY（N）中的N指的是字节长度，而CHAR（N）和VARCHAR（N）中N指的是的字符长度。对于BINARY（10） ，其可存储的字节固定为10，而对于CHAR（10） ，其可存储的字节视字符集的情况而定。
文本类型 BLOB和TEXT
能用varchar就不用text,长度不够的时候再使用text
blob与text的区别和binary与char的区别一样，blob以字节形式存储，text以字符形式存储
TINYBLOB
TINYTEXT
一个BLOB或TEXT列，最大长度为255(2^8-1)个字节或字符。
BLOB
TEXT
一个BLOB或TEXT列，最大长度为65535(2^16-1)个字节或字符。
MEDIUMBLOB
MEDIUMTEXT
一个BLOB或TEXT列，最大长度为16777215(2^24-1)个字节或字符。
LONGBLOB
LONGTEXT
一个BLOB或TEXT列，最大长度为4294967295(2^32-1)个字节或字符。
1.经常变化的字段用varchar
2.知道固定长度的用char
3.尽量用varchar
4.超过255字符的只能用varchar或者text
5.能用varchar的地方不用text
枚举类型 enum
mysql> create table t101(name enum('tigerfive','jim'));
只能从tigerfive,jim两个里面2选其1
集合类型 set
mysql> create table t204(name set('tigerfive','jim'));
mysql> insert into t204 set name="jim,tigerfive";
跟enum一样被限定范围，但是可以同插入多个数据
时间类型
日期类型
===时间和日期类型测试：year、date、time、datetime、timestamp
作用：用于存储用户的注册时间，文章的发布时间，文章的更新时间，员工的入职时间等
year
2018
date
00-69 21世纪
70-99 20世纪
time 838
050510
datetime
141125050510 insert into t11 set times=now();
timestamp 时间戳
141125050510
和datetime不同的是：当插入值为null的时候，显示值也会是当前的时间
mysql> insert into t values(null);
mysql> create table test_time( d date, t time, dt datetime );
mysql> insert into test_time values(now(),now(),now());
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql> select * from test_time;
+------------+----------+---------------------+
| d | t | dt |
+------------+----------+---------------------+
| 2018-01-11 | 10:59:57 | 2018-01-11 10:59:57 |
+------------+----------+---------------------+
now() year() month() day() minute() hour() second()
数据类型测试
MySQL数据类型
一、MySQL常见的数据类型
在MySQL数据库管理系统中，可以通过存储引擎来决定表的类型。同时，MySQL数据库管理系统也
提供了数据类型决定表存储数据的类型。MySQL数据库管理系统提供的数据类型：
数值类型：
整数类型 TINYINT SMALLINT MEDIUMINT INT BIGINT
浮点数类型 FLOAT DOUBLE
定点数类型 DEC
位类型 BIT
字符串类型：
CHAR系列 CHAR VARCHAR
TEXT系列 TINYTEXT TEXT MEDIUMTEXT LONGTEXT
BLOB 系列 TINYBLOB BLOB MEDIUMBLOB LONGBLOB
BINARY系列 BINARY VARBINARY
枚举类型： ENUM
集合类型： SET
时间和日期类型： DATE TIME DATETIME TIMESTAMP YEAR
二、数据类型测试
===整数类型测试：tinyint,int
作用：用于存储用户的年龄、游戏的Level、经验值等。
LAB1:
mysql> create table test1(
-> tinyint_test tinyint,
-> int_test int
-> );
mysql> desc test1;
+--------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------+------+-----+---------+-------+
| tinyint_test | tinyint(4) | YES | | NULL | |
| int_test | int(11) | YES | | NULL | |
+--------------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> insert into test1 values (111,111);
Query OK, 1 row affected (0.09 sec)
mysql> insert into test1(tinyint_test) values(128);
ERROR 1264 (22003): Out of range value for column 'tinyint_test' at row 1
mysql> insert into test1(int_test) values(2147483647);
Query OK, 1 row affected (0.05 sec)
mysql> insert into test1(int_test) values(2147483648);
ERROR 1264 (22003): Out of range value for column 'int_test' at row 1
//测试结果，默认有符号，超过存储范围出错。
LAB2: 无符号整形测试
mysql> create table test2(
-> tinyint_test tinyint unsigned, //约束条件unsigned限定只能存正值（无符号）
-> int_test int unsigned
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> desc test2;
+--------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| tinyint_test | tinyint(3) unsigned | YES | | NULL | |
| int_test | int(10) unsigned | YES | | NULL | |
+--------------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into test2(tinyint_test) values(255);
Query OK, 1 row affected (0.06 sec)
mysql> insert into test2(int_test) values(2147483648);
Query OK, 1 row affected (1.87 sec)
mysql> insert into test2 values(-20,-20);
ERROR 1264 (22003): Out of range value for column 'tinyint_test' at row 1
测试整数类型的显示宽度
mysql> create table t1 (
-> id1 int,
-> id2 int(6)
-> );
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id1 | int(11) | YES | | NULL | |
| id2 | int(6) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
mysql> insert into t1 values(1,1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+------+
| id1 | id2 |
+------+------+
| 1 | 1 |
+------+------+
1 row in set (0.00 sec)
mysql> create table t2 (
-> id1 int zerofill,
-> id2 int(6) zerofill
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> desc t2;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| id1 | int(10) unsigned zerofill | YES | | NULL | |
| id2 | int(6) unsigned zerofill | YES | | NULL | |
+-------+---------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into t2 values(2,2);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t2;
+------------+--------+
| id1 | id2 |
+------------+--------+
| 0000000002 | 000002 |
+------------+--------+
1 row in set (0.00 sec)
mysql> insert into t2 values(3,2222222); //插入大于宽度限制的值，仍然可以存储
Query OK, 1 row affected (0.03 sec)
mysql> select * from t2;
+------------+---------+
| id1 | id2 |
+------------+---------+
| 0000000002 | 000002 |
| 0000000003 | 2222222 |
+------------+---------+
2 rows in set (0.00 sec)
结论：整形的宽度仅为显示宽度，不是限制。因此建议整形无须指定宽度。
===浮点数类型测试：
作用：用于存储用户的身高、体重、薪水等
浮点数和定点数都可以用类型名称后加(M,D)的方式来表示，(M,D)表示一共显示M位数字（整数位
+小数位），其中D位于小数点后面，M和D又称为精度和标度。
float和double在不指定精度时，默认会按照实际的精度（由实际的硬件和操作系统决定）来显示，
而decimal在不指定精度时，默认的整数位为10，默认的小数位为0
定点数在MySQL内部以字符串形式存储，比浮点数更精确，适合用来表示货币等精度高的数据。
mysql> create table test4(float_test float(5,2)); //一共5位，小数占2位
Query OK, 0 rows affected (0.00 sec)
mysql> desc test4;
+------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| float_test | float(5,2) | YES | | NULL | |
+------------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into test4 values (10.2), (70.243), (70.246);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test4;
+------------+
| float_test |
+------------+
| 10.20 |
| 70.24 |
| 70.25 |
+------------+
3 rows in set (0.00 sec)
mysql> insert into test4 values (1111.2);
ERROR 1264 (22003): Out of range value for column 'float_test' at row 1
定点数decimal测试：
mysql> create table test5(decimal_test decimal(5,2));
mysql> insert into test5 values (70.245);
Query OK, 1 row affected, 1 warning (0.05 sec)
mysql> show warnings;
+-------+------+---------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------+
| Note | 1265 | Data truncated for column 'decimal_test' at row 1 |
+-------+------+---------------------------------------------------+
1 row in set (0.00 sec)
===位类型测试：BIT
BIT(M)可以用来存放多位二进制数，M范围从1~64，如果不写默认为1位
对于位字段可以使用函数读取：
bin()显示为二进制
hex()显示为十六进制
mysql> create table test_bit (id bit(4));
Query OK, 0 rows affected (0.35 sec)
mysql> desc test_bit;
+-------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id | bit(4) | YES | | NULL | |
+-------+--------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> insert into test_bit values(4);
mysql> select * from test_bit;
+------+
| id |
+------+
| |
+------+
1 row in set (0.00 sec)
mysql> select bin(id),hex(id) from test_bit;
+---------+---------+
| bin(id) | hex(id) |
+---------+---------+
| 100 | 4 |
+---------+---------+
1 row in set (0.00 sec)
插入超过指定宽度的值：
mysql> insert into test_bit values(8);
Query OK, 1 row affected (0.05 sec)
mysql> insert into test_bit values(9);
Query OK, 1 row affected (0.06 sec)
mysql> insert into test_bit values(16);
ERROR 1406 (22001): Data too long for column 'id' at row 1
===时间和日期类型测试：year、date、time、datetime、timestamp
作用：用于存储用户的注册时间，文章的发布时间，文章的更新时间，员工的入职时间等
mysql> create table test_time(
-> d date,
-> t time,
-> dt datetime
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> desc test_time;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d | date | YES | | NULL | |
| t | time | YES | | NULL | |
| dt | datetime | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> insert into test_time values(now(),now(),now());
Query OK, 1 row affected, 1 warning (0.02 sec)
mysql> select * from test_time;
+------------+----------+---------------------+
| d | t | dt |
+------------+----------+---------------------+
| 2013-12-18 | 00:06:10 | 2013-12-18 00:06:10 |
+------------+----------+---------------------+
1 row in set (0.00 sec)
mysql> create table t(id timestamp);
Query OK, 0 rows affected (0.01 sec)
mysql> desc t;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
1 row in set (0.00 sec)
mysql> insert into t values(null);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t;
+---------------------+
| id |
+---------------------+
| 2013-12-18 00:08:41 |
+---------------------+
1 row in set (0.00 sec)
注意事项：
其它的时间，按要求插入
==插入年份时，尽量使用4位值
==插入两位年份时，<=69，以20开头，比如65, 结果2065
>=70，以19开头，比如82，结果1982
mysql> create table t3(born_year year);
Query OK, 0 rows affected (0.40 sec)
mysql> desc t3;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| born_year | year(4) | YES | | NULL | |
+-----------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into t3 values
-> (12),(80);
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t3;
+-----------+
| born_year |
+-----------+
| 2012 |
| 1980 |
+-----------+
2 rows in set (0.00 sec)
＝＝＝字符串类型测试：CHAR、VARCHAR
作用：用于存储用户的姓名、爱好、发布的文章等
CHAR 列的长度固定为创建表时声明的长度： 0 ~ 255
VARCHAR 列中的值为可变长字符串，长度： 0 ~ 65535
注：在检索的时候，CHAR列删除了尾部的空格，而VARCHAR则保留这些空格
mysql> create table vc (
-> v varchar(4),
-> c char(4)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> desc vc;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| v | varchar(4) | YES | | NULL | |
| c | char(4) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into vc values('ab ','ab ');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from vc;
+------+------+
| v | c |
+------+------+
| ab | ab |
+------+------+
1 row in set (0.00 sec)
mysql> select length(v),length(c) from vc;
+-----------+-----------+
| length(v) | length(c) |
+-----------+-----------+
| 4 | 2 |
+-----------+-----------+
1 row in set (0.00 sec)
mysql> select concat(v,'='), concat(c,'=') from vc; //在后面加字符'='，看的更清楚
+---------------+---------------+
| concat(v,'=') | concat(c,'=') |
+---------------+---------------+
| ab = | ab= |
+---------------+---------------+
1 row in set (0.00 sec)
字符串类型测试：BINARY、VARBINARY
BINARY 和 VARBINARY类似于CHAR 和 VARCHAR，不同的是它们包含二进制字符而不包含
非二进制字符串
mysql> create table binary_t (c binary(3));
Query OK, 0 rows affected (0.03 sec)
mysql> desc binary_t;
+-------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| c | binary(3) | YES | | NULL | |
+-------+-----------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into binary_t set c='aaa';
Query OK, 1 row affected (0.00 sec)
mysql> select *,hex(c) from binary_t;
+------+--------+
| c | hex(c) |
+------+--------+
| aaa | 616161 |
+------+--------+
1 row in set (0.00 sec)
＝＝＝字符串类型
＝＝＝ENUM类型即枚举类型、集合类型SET测试
字段的值只能在给定范围中选择
常见的是单选按钮和复选框
enum 单选 只能在给定的范围内选一个值，如性别 sex 男male/女female
set 多选 在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3...）
表school.student3
姓名 name varchar(50)
性别 sex enum('m','f')
爱好 hobby set('music','book','game','disc')
mysql> use school
mysql> create table student3(
-> name varchar(50),
-> sex enum('m','f'),
-> hobby set('music','book','game','disc')
-> );
Query OK, 0 rows affected (0.31 sec)
mysql> desc student3;
+-------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------------------+------+-----+---------+-------+
| name | varchar(50) | YES | | NULL | |
| sex | enum('m','f') | YES | | NULL | |
| hobby | set('music','book','game','disc') | YES | | NULL | |
+-------+-----------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into student3 values
-> ('tom','m','book,game');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student3;
+------+------+-----------+
| name | sex | hobby |
+------+------+-----------+
| tom | boy | book,game |
+------+------+-----------+
1 row in set (0.00 sec)
mysql> insert into student3 values ('jack','m','film');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1
mysql> show create table student3\G
*************************** 1. row ***************************
Table: student3
Create Table: CREATE TABLE `student3` (
`name` varchar(50) default NULL,
`sex` enum('m','f') default NULL,
`hobby` set('music','book','game','disc') default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
========================================================
约束
约束（修饰符） 字段的属性
null
not null
create table t1(id int not null default 88, num int);
当一个字段被设置不能为空(not null)但是又没有给设置值的时候会按不同类型给与默认值
数值 0 (只适用于5.1/5.5)
字符串 空 (只适用于5.1/5.5)
enum 第一个预定义值
timestamp 当前时间
default
mysql> alter table t2 modify id int not null default 8;
unique（key）唯一的，独一无二的 避免重复的输入
mysql> alter table t2 modify id int unique;
mysql> alter table t200 drop index id; //删除
auto_increment 自增 每张表只有一个字段为自增
mysql> create table t4(id int unique auto_increment,name char(10));
primary key（key） 一个表中只能定义在一个主键，但是可以定义联合主键，不能两个字段同时重复
create table t2(id int,num int,primary kye(id))
create table t3(id int,num int,primary key(id,num)) 联合主键 两个字段以上都可以
每张表里只能有一个主键
不能为空，而且唯一
mysql> create table t7(hostname char(20) primary key,ip char(150));
mysql> create table t9(hostname char(20),ip char(150),primary key(hostname));
联合主键
mysql> create table t9(hostname char(20),ip char(150),primary key(hostname,ip));
mysql> alter table t101 drop primary key; //删除主键
index(key) 每张表可以有很多个index
索引 优化查询速度
创建索引：三种
mysql> create table t100(hostname char(20) primary key,ip char(150),index (ip));
mysql> create table t101(hostname char(20) primary key,ip char(150),index dizhi(ip));
mysql> create index dizhi on t105(ip);
删除索引：
mysql> alter table t101 drop index dizhi; //删除index索引
外键foreign key (key) rforeign key() references t1 ()
mysql> create table t1(id int,manager char(10) primary key)；
mysql> create table t2(id int,admin char(10),foreign key (admin) references t1 (manager)) ；
mysql> create table xingzheng(id int,admin char(10),foreign key (admin) references guanli(manager) on delete cascade) ; 强制删除
添加外键：
alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(从表的列名) references 主表(主键列名);
删除外键：
alter table 表名 drop foreign key 外键名称
索引[开发]
MySQL索引
========================================================
创建索引
创建表时创建索引
CREATE在已存在的表上创建索引
ALTER TABLE在已存在的表上创建索引
查看并测试索引
删除索引
一、索引简介
索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，
尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。
索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。
二、索引的分类
普通索引
唯一索引
全文索引
单列索引
多列索引
空间索引
三、准备实验环境
1. 准备表
MariaDB [test]> create table t2(id int,name varchar(20));
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2. 创建存储过程，实现批量插入记录
mysql> use test
mysql> delimiter $$ //设置分割符为$$
mysql> create procedure autoinsert1()
-> BEGIN
-> declare i int default 1;
-> while(i<200000)do
-> insert into test.t2 values(i,'ccc');
-> set i=i+1;
-> end while;
-> END$$
mysql> delimiter ;
查看存储过程的基本信息
MariaDB [test]> show procedure status\G
*************************** 1. row ***************************
Db: test
Name: autoinsert1
Type: PROCEDURE
Definer: root@localhost
Modified: 2020-09-18 09:51:25
Created: 2020-09-18 09:51:25
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
查看存储过程的详细信息
MariaDB [test]> show create procedure autoinsert1\G
*************************** 1. row ***************************
Procedure: autoinsert1
sql_mode:
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `autoinsert1`()
BEGIN
declare i int default 1;
while(i<200000)do
insert into test.t2 values(i,'ccc');
set i=i+1;
end while;
END
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
3. 调用存储过程
mysql> call autoinsert1();
四、创建索引
＝＝＝创建表时
语法：
CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[索引名] (字段名[(长度)] [ASC |DESC])
);
创建普通索引示例：
CREATE TABLE department10 (
dept_id INT,
dept_name VARCHAR(30) ,
comment VARCHAR(50),
INDEX index_dept_name (dept_name)
);
创建唯一索引示例：
CREATE TABLE department11 (
dept_id INT,
dept_name VARCHAR(30) ,
comment VARCHAR(50),
UNIQUE INDEX index_dept_name (dept_name)
);
创建全文索引示例：
CREATE TABLE department12 (
dept_id INT,
dept_name VARCHAR(30) ,
comment VARCHAR(50),
log text,
FULLTEXT INDEX index_log (log)
);
创建多列索引示例：
CREATE TABLE department13 (
dept_id INT,
dept_name VARCHAR(30) ,
comment VARCHAR(50),
INDEX index_dept_name_comment (dept_name, comment)
);
===CREATE在已存在的表上创建索引
语法：
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC |DESC]) ;
创建普通索引示例：
CREATE INDEX index_dept_name ON department (dept_name);
创建唯一索引示例：
CREATE UNIQUE INDEX index_dept_name ON department (dept_name);
创建全文索引示例：
CREATE FULLTEXT INDEX index_dept_name ON department (dept_name);
创建多列索引示例：
CREATE INDEX index_dept_name_ comment ON department (dept_name, comment);
===ALTER TABLE在已存在的表上创建索引
语法：
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名 (字段名[(长度)] [ASC |DESC]) ;
创建普通索引示例：
ALTER TABLE department ADD INDEX index_dept_name (dept_name);
创建唯一索引示例：
ALTER TABLE department ADD UNIQUE INDEX index_dept_name (dept_name);
创建全文索引示例：
ALTER TABLE department ADD FULLTEXT INDEX index_dept_name (dept_name);
创建多列索引示例：
ALTER TABLE department ADD INDEX index_dept_name_comment (dept_name, comment);
四、管理索引
查看索引
SHOW CRETAE TABLE 表名\G
测试示例
EXPLAIN SELECT * FROM department WHERE dept_name=‘hr’;
删除索引
show create table employee6;
DROP INDEX 索引名 ON 表名;
索引测试实验：
mysql> create table school.t2(id int,name varchar(30));
Query OK, 0 rows affected (1.33 sec)
mysql> desc school.t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> use school
Database changed
mysql> delimiter $$
mysql> create procedure autoinsert1()
-> BEGIN
-> declare i int default 1;
-> while(i<100000)do
-> insert into school.t2 values(i,'ccc');
-> set i=i+1;
-> end while;
-> END$$
mysql> delimiter ;
mysql> call autoinsert1();
未创建索引
mysql> explain select * from school.t2 where id=20000;
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 44848 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)
mysql> create index index_id on school.t2(id);
Query OK, 0 rows affected (0.91 sec)
Records: 0 Duplicates: 0 Warnings作用: 0
mysql> explain select * from school.t2 where id=20000;
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | t2 | ref | index_id | index_id | 5 | const | 1 | Using where |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)
SHOW CREATE TABLE 表名\G
EXPLAIN： 命令的作用是查看查询优化器如何决定执行查询
花费时间比较：
创建索引前
mysql> select * from school.t2 where id=20000;
+-------+------+
| id | name |
+-------+------+
| 20000 | ccc |
+-------+------+
1 row in set (0.03 sec)
创建索引后
mysql> create index index_id on school.t2(id);
Query OK, 0 rows affected (0.39 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from school.t2 where id=20000;
+-------+------+
| id | name |
+-------+------+
| 20000 | ccc |
+-------+------+
1 row in set (0.00 sec)
========================================================
视图[开发]
MySQL视图VIEW
========================================================
视图简介
创建视图
查看视图
修改视图
通过视图操作基表
删除视图
一、视图简介
MySQL视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行
数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所
引用的表，并且在引用视图时动态生成。对其中所引用的基础表来说，MySQL视图的作用类似于筛选。
定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。通过视图进行查询没有任
何限制，通过它们进行数据修改时的限制也很少。
视图是存储在数据库中的SQL查询语句，它主要出于两种原因：安全原因，视图可以隐藏一些数据，如：
一些敏感的信息，另一原因是可以使复杂的查询易于理解和使用。
二、创建视图
语法一：
CREATE [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE} ]
VIEW 视图名 [(字段1,字段2…)]
AS SELECT语句
[WITH [CASCADED | LOCAL] CHECK OPTION ];
语法二：
CREATE VIEW 视图名
AS SELECT语句;
示例1：创建视图案例 (单表)
USE mysql
mysql> CREATE VIEW u
-> AS SELECT user,host,authentication_string FROM mysql.user;
Query OK, 0 rows affected (0.00 sec)
mysql> desc mysql.u;
+-----------------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+----------+------+-----+---------+-------+
| user | char(16) | NO | | | |
| host | char(60) | NO | | | |
| authentication_string | text | NO | | NULL | |
+-----------------------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select * from mysql.u;
+-------+-----------+-----------------------+
| user | host | authentication_string |
+-------+-----------+-----------------------+
| root | localhost | |
| root | vm1 | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | vm1 | |
| tiger | % | |
+-------+-----------+-----------------------+
7 rows in set (0.00 sec)
示例2：创建视图案例 (多表)
mysql> create database shop;
Query OK, 1 row affected (0.21 sec)
mysql> use shop
Database changed
mysql> create table product(
-> id int unsigned auto_increment primary key not null,
-> name varchar(60) not null,
-> price double not null
-> );
mysql> insert into product(name,price) values
-> ('pear',4.3),
-> ('orange',6.5),
-> ('apple',5.0)
-> ;
mysql> create table purchase(
-> id int unsigned auto_increment primary key not null,
-> name varchar(60) not null,
-> quantity int not null default 0,
-> gen_time datetime not null
-> );
mysql> insert into purchase(name,quantity,gen_time) values
-> ('apple',7,now()),
-> ('pear',10,now())
-> ;
mysql> create view purchase_detail
-> as select
-> product.name, product.price,
-> purchase.quantity,
-> product.price * purchase.quantity as total_value
-> from product,purchase
-> where product.name = purchase.name;
mysql> select * from purchase_detail;
+-------+-------+----------+-------------+
| name | price | quantity | total_value |
+-------+-------+----------+-------------+
| pear | 4.3 | 10 | 43 |
| apple | 5 | 7 | 35 |
+-------+-------+----------+-------------+
2 rows in set (0.04 sec)
mysql> insert into purchase(name,quantity,gen_time) values ('orange',20,now());
Query OK, 1 row affected (0.01 sec)
mysql> select * from purchase_detail;
+--------+-------+----------+-------------+
| name | price | quantity | total_value |
+--------+-------+----------+-------------+
| apple | 5 | 7 | 35 |
| pear | 4.3 | 10 | 43 |
| orange | 6.5 | 20 | 130 |
+--------+-------+----------+-------------+
3 rows in set (0.00 sec)
三、查看视图
1. SHOW TABLES 查看视图名
SHOW TABLES;
2. SHOW TABLE STATUS
示例：查看数据库mysql中视图及所有表详细信息
SHOW TABLE STATUS FROM mysql \G
示例：查看数据库mysql中视图名view_user详细信息
SHOW TABLE STATUS FROM mysql
LIKE 'view_user' \G
3. SHOW CREATE VIEW
示例：查看视图定义信息
SHOW CREATE VIEW view_user\G
4. DESCRIBE
示例：查看视图结构
DESC view_user ;
四、修改视图
方法一：删除后新创建
DROP VIEW view_user ;
CREATE VIEW view_user
AS SELECT user,host FROM mysql.user;
SELECT * FROM view_user;
方法二：ALTER修改视图
语法：
ALTER VIEW 视图名
AS SELECT语句;
示例：
ALTER VIEW view_user
AS SELECT user,password FROM mysql.user;
五、通过视图操作基表
查询数据SELECT
SELECT * FROM view_user;
更新数据UPDATE
删除数据DELETE
六、删除视图
语法：
DROP VIEW view_name [,view_name]…;
示例：
USE mysql;
DROP VIEW view_user ;
触发器[开发]
MySQL触发器Triggers
========================================================
触发器简介
创建触发器
查看触发器
删除触发器
触发器案例
一、触发器简介
触发器（trigger）是一个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，
比如当对一个表进行操作（insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整
性约束和业务规则等。
例如，当学生表中增加了一个学生的信息时，学生的总数就应该同时改变。因此可以针对学生表创建一个触发
器，每次增加一个学生记录时，就执行一次学生总数的计算操作，从而保证学生总数与记录数的一致性。
二、创建Trigger
语法：
CREATE TRIGGER 触发器名称 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
触发器程序体;
END
<触发器名称> 最多64个字符，它和MySQL中其他对象的命名方式一样
{ BEFORE | AFTER } 触发器时机
{ INSERT | UPDATE | DELETE } 触发的事件
ON <表名称> 标识建立触发器的表名，即在哪张表上建立触发器
FOR EACH ROW 触发器的执行间隔：FOR EACH ROW子句通知触发器 每隔一行
执行一次动作，而不是对整个表执行一次
<触发器程序体> 要触发的SQL语句：可用顺序，判断，循环等语句实现一般程序需要的逻辑功能
example1
1. 创建表
mysql> create table student(
-> id int unsigned auto_increment primary key not null,
-> name varchar(50)
-> );
mysql> insert into student(name) values('jack');
mysql> create table student_total(total int);
mysql> insert into student_total values(1);
2. 创建触发器student_insert_trigger
mysql> delimiter $$
mysql> create trigger student_insert_trigger after insert
-> on student for each row
-> BEGIN
-> update student_total set total=total+1;
-> END$$
mysql> delimiter ;
3. 创建触发器student_delete_trigger
mysql> delimiter $$
mysql> create trigger student_delete_trigger after delete
-> on student for each row
-> BEGIN
-> update student_total set total=total-1;
-> END$$
mysql> delimiter ;
三、查看触发器
1. 通过SHOW TRIGGERS语句查看
SHOW TRIGGERS\G
2. 通过系统表triggers查看
USE information_schema
SELECT * FROM triggers\G
SELECT * FROM triggers WHERE TRIGGER_NAME='触发器名称'\G
四、删除触发器
1. 通过DROP TRIGGERS语句删除
DROP TRIGGER 解发器名称
五、触发器案例
example2
创建表tab1
DROP TABLE IF EXISTS tab1;
CREATE TABLE tab1(
id int primary key auto_increment,
name varchar(50),
sex enum('m','f'),
age int
);
创建表tab2
DROP TABLE IF EXISTS tab2;
CREATE TABLE tab2(
id int primary key auto_increment,
name varchar(50),
salary double(10,2)
);
触发器tab1_after_delete_trigger
作用：tab1表删除记录后，自动将tab2表中对应记录删除
mysql> \d $$
mysql> create trigger tab1_after_delete_trigger
after delete on tab1
for each row
BEGIN
delete from tab2 where name=old.name;//无法使用 //X
delete from tab2 where id=old.id; //删除必须通过primary key
END$$
触发器tab1_after_update_trigger
作用：当tab1更新后，自动更新tab2
mysql> create trigger tab11_after_update_trigger
after update on tab1
for each row
BEGIN
update tab2 set id=new.id,name=new.name,sex=new.sex,age=new.age //所有字段
where id=old.id;
END$$
Query OK, 0 rows affected (0.19 sec)
触发器tab1_after_insert_trigger
作用：当tab1增加记录后，自动增加到tab2
mysql> create trigger tab1_after_insert_trigger
after insert on tab1
for each row
BEGIN
insert into tab2(name,salary) values(new.name,5000);
END$$
Query OK, 0 rows affected (0.19 sec)
example3
mysql> create table t1(
-> id int primary key auto_increment,
-> name varchar(50),
-> salary float(10,2)
-> );
Query OK, 0 rows affected (0.63 sec)
mysql> create table t2(
-> id int primary key auto_increment,
-> total_num int, //员工总人数
-> total_salary float(10,2) //员工薪水总和
-> );
Query OK, 0 rows affected (0.64 sec)
mysql> \d $$
mysql> create trigger t1_after_insert_trigger
-> after insert on t1
-> for each row
-> BEGIN
-> update t2 set total_num=total_num+1, total_salary=total_salary+new.salary;
-> END$$
Query OK, 0 rows affected (0.54 sec)
mysql> insert into t2 values(0,0); //初始值
Query OK, 1 row affected (0.10 sec)
mysql> select * from t2;
+-----------+--------------+
| total_num | total_salary |
+-----------+--------------+
| 0 | 0.00 |
+-----------+--------------+
1 row in set (0.00 sec)
update
delete
存储过程与函数[开发]
procedure and function
==================================================
一、概述：
存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合。
存储过程和函数的区别：
• 函数必须有返回值，而存储过程没有。
• 存储过程的参数可以是IN、OUT、INOUT类型，函数的参数只能是IN
优点：
• 存储过程只在创建时进行编译；
而SQL语句每执行一次就编译一次，所以使用存储过程可以提高数据库执行速度。
• 简化复杂操作，结合事务一起封装。
• 复用性好
• 安全性高，可指定存储过程的使用权。
说明：
并发量少的情况下，很少使用存储过程。
并发量高的情况下，为了提高效率，用存储过程比较多。
二、创建与调用
创建存储过程语法 ：
create procedure sp_name(参数列表)
[特性...]过程体
存储过程的参数形式：[IN | OUT | INOUT]参数名 类型
IN 输入参数
OUT 输出参数
INOUT 输入输出参数
delimiter $$
create procedure 过程名(形式参数列表)
begin
SQL语句
end $$
delimiter ;
调用：
call 存储过程名(实参列表)
存储过程三种参数类型：IN, OUT, INOUT
===================NONE========================
mysql> \d $$
mysql> create procedure p1()
-> begin
-> select count(*) from mysql.user;
-> end$$
Query OK, 0 rows affected (0.51 sec)
mysql> \d ;
mysql> call p1();
mysql> create table school.t1(id int,cc varchar(100));
mysql> delimiter $$
mysql> create procedure autoinsert1()
-> BEGIN
-> declare i int default 1;
-> while(i<=20000)do
-> insert into school.t1 values(i,md5(i));
-> set i=i+1;
-> end while;
-> END$$
mysql> delimiter ;
====================IN==========================
mysql> create procedure autoinsert2(IN a int)
-> BEGIN
-> declare i int default 1;
-> while(i<=a)do
-> insert into school.t1 values(i,md5(i));
-> set i=i+1;
-> end while;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> call autoinsert1(10);
Query OK, 1 row affected (1.10 sec)
mysql> set @num=20;
mysql> select @num;
+------+
| @num |
+------+
| 20 |
+------+
1 row in set (0.00 sec)
mysql> call autoinsert1(@num);
mysql> select @a;
+------+
| @a |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
====================OUT=======================
mysql> delimiter $$
mysql> CREATE PROCEDURE p2 (OUT param1 INT)
-> BEGIN
-> SELECT COUNT(*) INTO param1 FROM mysql.user;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> select @a;
+------+
| @a |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
mysql> CALL p2(@a);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @a;
+------+
| @a |
+------+
| 3 |
+------+
===================IN 和 OUT=====================
作用：统计指定部门的员工数
mysql> create procedure count_num(IN p1 varchar(50), OUT p2 int)
-> BEGIN
-> select count(*) into p2 from company.employee5
-> where post=p1;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> \d ;
mysql> call count_num('hr',@a);
mysql>select @a;
作用：统计指定部门工资超过例如5000的总人数
mysql> create procedure count_num(IN p1 varchar(50), IN p2 float(10,2), OUT p3 int)
-> BEGIN
-> select count(*) into p3 from employee5
-> where post=p1 and salary=>p2;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> \d ;
mysql> call count_num('hr',5000,@a);
====================INOUT======================
mysql> create procedure proce_param_inout(inout p1 int)
-> begin
-> if (p1 is not null) then
-> set p1=p1+1;
-> else
-> select 100 into p1; //set p1=100;
-> end if;
-> end$$
Query OK, 0 rows affected (0.00 sec)
mysql> select @h;
+------+
| @h |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
mysql> call proce_param_inout(@h);
Query OK, 1 row affected (0.00 sec)
mysql> select @h;
+------+
| @h |
+------+
| 100 |
+------+begin
1 row in set (0.00 sec)
mysql> call proce_param_inout(@h);
Query OK, 0 rows affected (0.00 sec)
mysql> select @h;
+------+
| @h |
+------+
| 101 |
+------+
1 row in set (0.00 sec)
FUNCTION函数
=================================================
mysql> CREATE FUNCTION hello (s CHAR(20))
-> RETURNS CHAR(50) RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)
mysql> select hello('tianyun');
+------------------+
| hello('tianyun') |
+------------------+
| Hello, tianyun! |
+------------------+
root@(company)> select hello('tianyun') return1;
+-----------------+
| return1 |
+-----------------+
| Hello, tianyun! |
+-----------------+
1 row in set (0.00 sec)
mysql> create function name_from_employee(x int)
-> returns varchar(50)
-> BEGIN
-> return (select emp_name from employee
-> where emp_id=x);
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> select name_from_employee(3);
mysql> select * from employee where emp_name=name_from_employee(1);
+--------+----------+------+------------+------------+-----------------+---------+--------+--------+
| emp_id | emp_name | sex | hire_date | post | job_description | salary | office | dep_id |
+--------+----------+------+------------+------------+-----------------+---------+--------+--------+
| 1 | jack | male | 2019-02-02 | instructor | teach | 5000.00 | 501 | 100 |
+--------+----------+------+------------+------------+-----------------+---------+--------+--------+
1 row in set (0.00 sec)
==============================================
创建函数的语法：
create function 函数名(参数列表) returns 返回值类型
[特性...] 函数体
函数的参数形式：参数名 类型
delimiter $$
create function 函数名(参数列表) returns 返回值类型
begin
有效的SQL语句
end$$
delimiter ;
调用：
select 函数名(实参列表)
delimiter $$
create function fun1(str char(20)) returns char(50)
return concat("hello",str,"!");
$$
delimiter ;
select fun1(' function');
存储过程与函数的维护：
show create procedure pr1 \G;
show create function pr1 \G;
show {procedure|function} status {like 'pattern'}
drop {procedure|function} {if exists} sp_name
mysql变量的术语分类：
1.用户变量：以"@"开始，形式为"@变量名"，由客户端定义的变量。
用户变量跟mysql客户端是绑定的，设置的变量只对当前用户使用的客户端生效，当用户断开连接时，所有变量会自动释放。
2.全局变量：定义时如下两种形式，set GLOBAL 变量名 或者 set @@global.变量名
对所有客户端生效，但只有具有super权限才可以设置全局变量。
3.会话变量：只对连接的客户端有效。
4.局部变量：设置并作用于begin...end语句块之间的变量。
declare语句专门用于定义局部变量。而set语句是设置不同类型的变量，包括会话变量和全局变量
语法：declare 变量名[...] 变量类型 [default 值]
declare定义的变量必须写在复合语句的开头，并且在任何其它语句的前面。
变量的赋值：
直接赋值： set 变量名=表达式值或常量值[...];
用户变量的赋值：
1、set 变量名=表达式或常量值;
2、也可以将查询结果赋值给变量(要求查询返回的结果只能有一行)
例：set 列名 into 变量名 from 表名 where 条件;
3、select 值 into @变量名；
客户端变量不能相互共享。
delimiter $$
create procedure pr2()
begin
declare xname varchar(50);
declare xdesc varchar(100);
set xname="caiwu";
set xdesc="accouting";
insert into dept(name,desc) values(xname,xdesc);
end$$
delimiter ;
call pr2();
delimiter $$
create procedure pr3(in x int,in y int,out sum int)
begin
set sum=x+y;
end$$
delimiter ;
call pr3(3,4,@sum);
select @sum;
delimiter //
create function fun6(x int,y int) returns int
begin
declare sum int;
set sum=x+y;
return sum;
end//
delimiter ;
select fun6(4,3);
delimiter //
create function fun_add_rand(in_int int )
RETURNS int
BEGIN
declare i_rand int;
declare i_return int;
set i_rand=floor(rand()*100);
set i_return = in_int + i_rand;
return i_return;
END;
//
========================================================
权限机制
权限控制机制
刷新权限
mysql > flush privileges;
# mysqladmin flush-privileges -u root -p1
user表
登录认证：用户能否进mysql查看user
权限认证：如果能进，看user表的权限有没有，没有就看下一个db表，db表没有看tables_priv,tables_priv没有就去看columns_priv
当db表的host字段为空的时候才会用到host表
db或者host 任何一个select是N,都是没权限
使用命令授权：
mysql> grant select(id),insert(id) on tigerfive.t1 to 'xiaowu'@'172.16.70.%' identified by '123';
mysql> grant select,insert on tigerfive.t1 to 'xiaowu'@'172.16.70.%' identified by '123';
mysql> grant all on tigerfive.t1 to 'xiaowu'@'172.16.70.%' identified by '123';
mysql> grant all on *.* to 'xiaowu'@'172.16.70.%' identified by '123';
mysql> grant all on *.* to 'xiaowu'@'172.16.70.%' ；
mysql> grant all on *.* to 'xiaowu'@'%'；
撤销权限
mysql> revoke all on *.* from 'xiaowu'@'%';
删除账户：
mysql> drop user tigerfive;
创建账户：
mysql> create user tigerfive;
root账户没了或者root密码丢失：
# mysqld_safe --skip-grant-tables --user=mysql &
安全机制
MySQL安全机制 DDL DCL
========================================================
MySQL权限表
MySQL用户管理
MySQL权限管理
SSL加密连接
一、MySQL权限表
mysql.user Global level
用户字段
权限字段
安全字段
资源控制字段
mysql.db Database level
用户字段
权限字段
mysql.tables_priv Table level
mysql.columns_priv Column level
二、MySQL用户管理
1. 登录和退出MySQL
示例：
mysql -h192.168.5.240 -P 3306 -u root -p123 -e ‘select user,host from mysql.user’
-h 指定主机名 【默认为localhost】
-P MySQL服务器端口 【默认3306】
-u 指定用户名 【默认root】
-p 指定登录密码 【默认为空密码】
此处mysql为指定登录的数据库
-e 接SQL语句
2. 创建用户
方法一：CREATE USER语句创建
CREATE USER user1@'localhost' IDENTIFIED BY '23456';
方法二： GRANT语句创建
GRANT ALL ON *.* TO 'user3'@'localhost' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;
Example 1: Create an account that uses the default authentication
plugin and the given password. Mark the password expired so that the
user must choose a new one at the first connection to the server:
CREATE USER 'jeffrey'@'localhost'
IDENTIFIED BY 'new_password' PASSWORD EXPIRE;
Example 2: Create an account that uses the sha256_password
authentication plugin and the given password. Require that a new
password be chosen every 180 days:
CREATE USER 'jeffrey'@'localhost'
IDENTIFIED WITH sha256_password BY 'new_password'
PASSWORD EXPIRE INTERVAL 180 DAY;
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
ALTER USER 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
3. 删除用户
方法一：DROP USER语句删除
DROP USER 'user1'@'localhost';
方法二：DELETE语句删除
DELETE FROM mysql.user
WHERE user='user2' AND host='localhost';
FLUSH PRIVILEGES;
4. 修改用户密码
＝＝＝root修改自己密码
方法一：
# mysqladmin -uroot -p'123' password 'new_password' //123为旧密码
方法二：
mysql> UPDATE mysql.user SET authentication_string=password('new_password')
WHERE user='root' AND host='localhost';
FLUSH PRIVILEGES;
方法三：
mysql> SET PASSWORD=password('new_password');
==root修改其他用户密码
方法一：
SET PASSWORD FOR user3@’localhost’=password(‘new_password’);
方法二：
UPDATE mysql.user SET authentication_string=password(‘new_password’)
WHERE user=’user3’ AND host=’localhost’;
FLUSH PRIVILEGES;
＝＝＝普通用户修改自己密码
SET password=password(‘new_password’);
＝＝＝丢失root用户密码
# vim /etc/my.cnf
[mysqld]
skip-grant-tables
# service mysqld restart
# mysql -uroot
mysql> UPDATE mysql.user SET authentication_string=password(‘new_password’)
WHERE user=’root’ AND host=’localhost’;
mysql> FLUSH PRIVILEGES;
三、MySQL权限管理
权限应用的顺序：
user (Y|N) ==> db ==> tables_priv ==> columns_priv
语法格式：
grant 权限列表 on 库名.表名 to '用户名'@'客户端主机' [identified by '密码' with option参数];
==权限列表 all 所有权限（不包括授权权限）
select,update
==数据库.表名 *.* 所有库下的所有表 Global level
web.* web库下的所有表 Database level
web.stu_info web库下的stu_info表 Table level
SELECT (col1), INSERT (col1,col2) ON mydb.mytbl Column level
==客户端主机 % 所有主机
192.168.2.% 192.168.2.0网段的所有主机
192.168.2.168 指定主机
localhost 指定主机
with_option参数
GRANT OPTION： 授权选项
MAX_QUERIES_PER_HOUR： 定义每小时允许执行的查询数
MAX_UPDATES_PER_HOUR： 定义每小时允许执行的更新数
MAX_CONNECTIONS_PER_HOUR： 定义每小时可以建立的连接数
MAX_USER_CONNECTIONS： 定义单个用户同时可以建立的连接数
Grant示例：
GRANT ALL ON *.* TO admin1@'%' IDENTIFIED BY '(TianYunYang584131420)';
GRANT ALL ON *.* TO admin2@'%' IDENTIFIED BY '(TianYunYang584131420)' WITH GRANT OPTION;
GRANT ALL ON bbs.* TO admin3@'%' IDENTIFIED BY '(TianYunYang584131420)';
GRANT ALL ON bbs.* TO admin3@'192.168.122.220' IDENTIFIED BY '(TianYunYang584131420)';
GRANT ALL ON bbs.user TO admin4@'%' IDENTIFIED BY '(TianYunYang584131420)';
GRANT SELECT(col1),INSERT(col2,col3) ON bbs.user TO admin5@'%' IDENTIFIED BY '(TianYunYang584131420)';
回收权限REVOKE
查看权限
SHOW GRANTS\G
SHOW GRANTS FOR admin1@'%'\G
回收权限REVOKE
语法：
REVOKE 权限列表 ON 数据库名 FROM 用户名@‘客户端主机’
示例：
REVOKE DELETE ON *.* FROM admin1@’%’; //回收部分权限
REVOKE ALL PRIVILEGES ON *.* FROM admin2@’%’; //回收所有权限
REVOKE ALL PRIVILEGES,GRANT OPTION ON *.* FROM 'admin2'@'%';
删除用户:
5.6 revoke all privilege drop user
5.7 drop user
用户管理
MySQL用户管理
1. 登录和退出MySQL
#mysql -h192.168.5.240 -P 3306 -u root -p123 mysql -e ‘select user,host from user’
-h 指定主机名 【默认为localhost】
-P MySQL服务器端口 【默认3306】
-u 指定用户名 【默认root】
-p 指定登录密码 【默认为空密码】
此处mysql为指定登录的数据库
-e 接SQL语句
2. 创建用户
方法一：CREATE USER语句创建
mysql> create user tigerfive;
mysql> create user tigerfive@’%’ identified by '123456'; 这样可以直接从远程登录
方法二： GRANT语句创建
mysql> GRANT ALL ON *.* TO 'user3'@’localhost’ IDENTIFIED BY ‘123456’;
FLUSH PRIVILEGES;
Example 1: Create an account that uses the default authentication
plugin and the given password. Mark the password expired so that the
user must choose a new one at the first connection to the server:
mysql> CREATE USER 'jeffrey'@'localhost'
IDENTIFIED BY 'new_password' PASSWORD EXPIRE;
Example 2: Create an account that uses the sha256_password
authentication plugin and the given password. Require that a new
password be chosen every 180 days:
mysql> CREATE USER 'jeffrey'@'localhost'
IDENTIFIED WITH sha256_password BY 'new_password'
PASSWORD EXPIRE INTERVAL 180 DAY;
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
ALTER USER 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
3. 删除用户
方法一：DROP USER语句删除
DROP USER 'user1'@’localhost’;
方法二：DELETE语句删除
DELETE FROM mysql.user
WHERE user=’user2’ AND host=’localhost’;
FLUSH PRIVILEGES;
4. 修改用户密码
＝＝＝root修改自己密码
方法一：
# mysqladmin -uroot -p'123' password 'new_password' //123为旧密码
方法二：
mysql> update mysql.user set authentication_string=password('Qianfeng123!') where user='root' and host='localhost';
方法三：
SET PASSWORD=password(‘new_password’);
上面方法将会在后面的版本remove,使用下面方法
SET PASSWORD='new_password'; 直接设置密码
==root修改其他用户密码
方法一：
mysql> SET PASSWORD FOR user3@’localhost’=password(‘new_password’);
上面的方法会在将来remove，使用下面的方法：
mysql> SET PASSWORD FOR user3@’localhost’='new_password';
方法二：
UPDATE mysql.user SET authentication_string=password(‘new_password’)
WHERE user=’user3’ AND host=’localhost’;
＝＝＝普通用户修改自己密码
mysql> SET password=password('new_password');
mysql> alter user 'tigerfive'@'localhost' identified by 'Qianfeng123!@';
root账户没了或者root密码丢失：
关闭Mysql使用下面方式进入Mysql直接修改表权限
5.1/5.5版本 ：
# mysqld_safe --skip-grant-tables --user=mysql &
5.6/5.7版本：
# mysqld --skip-grant-tables --user=mysql &
# mysql
mysql> UPDATE mysql.user SET authentication_string=password('new_password')
WHERE user='root' AND host='localhost';
mysql> FLUSH PRIVILEGES; 刷新授权表
密码复杂度限制策略：
MySQL5.7默认安装了validate_password插件，若没有安装，则SHOW VARIABLES LIKE 'vali%'则会返回空。
1、查看现有的密码策略
mysql> SHOW VARIABLES LIKE 'validate_password%';
参数解释：
1).validate_password_dictionary_file 指定密码验证的文件路径;
2).validate_password_length 密码最小长度
3).validate_password_mixed_case_count 密码至少要包含的小写字母个数和大写字母个数;
4).validate_password_number_count 密码至少要包含的数字个数
5).validate_password_policy 密码强度检查等级，对应等级为：0/LOW、1/MEDIUM、2/STRONG,默认为1
0/LOW：只检查长度;
1/MEDIUM：检查长度、数字、大小写、特殊字符;
2/STRONG：检查长度、数字、大小写、特殊字符字典文件。
6).validate_password_special_char_count密码至少要包含的特殊字符数
2、创建用户时报错：
mysql> create user 'miner'@'192.168.%' IDENTIFIED BY 'miner123';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
报错原因：密码强度不够。
解决方法：(该账号为测试账号，所以采用降低密码策略强度)
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=4;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| validate_password_dictionary_file | |
| validate_password_length | 4 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | LOW |
| validate_password_special_char_count | 1 |
+--------------------------------------+-------+
6 rows in set (0.00 sec)
再次创建用户，成功
3.关闭validate_password插件:
在配置文件中加入以下并重启mysqld即可：
[mysqld]
validate_password=off
重启mysqld后通过SHOW PLUGINS可以查到：
+-------------------+----------+-------------------+----------------------+-----+
| validate_password | DISABLED | VALIDATE PASSWORD | validate_password.so | GPL |
+-------------------+----------+-------------------+----------------------+-----+
创建用户并远程登录
create user tigerfive@'%' identified by '123456';
或者
GRANT ALL ON *.* TO 'user3'@'%' IDENTIFIED BY '123456';
在MySQL 5.7 password字段已从mysql.user表中删除，新的字段名是“authenticalion_string”.
选择数据库：use mysql;
更新root的密码：update user set authentication_string=password('新密码') where user='root' and Host='localhost';
刷新权限：flush privileges;
权限管理
权限控制机制
user db tables_priv columns_priv
1.用户认证
查看user表，表里有这个用户才能进。
2.权限认证
以select权限为例
先看 user表里的select——priv权限
Y 不会直接查看其他的表 拥有查看所有库所有表的权限
N 接着看db表
db
Y 不会接着查看其他的表 拥有查看某一个库所拥有的权限
N 接着看tables——priv表
tables_priv表
table_priv：如果这个字段的只包括select 拥有查看这张表所有字段的权限
table_priv：如果这个字段的值里不包括select，接着看下一章张表
还需要有column_priv权限
columns_priv
cloumn_priv:有select 则只对某一列有select权限 没有则对所有的表没有任何权限
db:
mysql> insert into db(host,db,user,Select_priv) values("10.18.44.%",'data','ying','Y');
tables_priv:
mysql> insert into tables_priv(host,db,user,table_name,table_priv) values('10.18.44.%','data','ying','t1','Select,insert');
columns_priv:
mysql> insert into columns_priv(host,db,user,table_name,column_name,column_priv) values('10.18.44.%','data','ying','t1','id','select');
host db user sleect_priv
host db user table_name
host db user table_name column_name column_priv
grant select on data
刷新权限:两种方式
修改表之后需要刷新权限
方式1：
mysql > flush privileges;
方式2：
# mysqladmin flush-privileges -u root -p1
五表联动(5.7取消了host表)
user表
登录认证：用户能否进mysql查看user
权限认证：如果能进，看user表的权限有没有，没有就看下一个db表，db表没有看tables_priv,tables_priv没有就去看columns_priv
当db表的host字段为空的时候才会用到host表
db或者host 任何一个select是N,都是没权限
使用命令授权：grant
也可创建新账户(不过后面的版本会移除这个功能，建议使用create user)
语法格式：
grant 权限列表 on 库名.表名 to '用户名'@'客户端主机' identified by '密码' with option参数;
==权限列表 all 所有权限（不包括授权权限）
select,update
select(col1), insert(col1,col2) Column level
==数据库.表名 *.* 所有库下的所有表 Global level
web.* web库下的所有表 Database level
web.stu_info web库下的stu_info表 Table level
==客户端主机 % 所有主机
192.168.2.% 192.168.2.0网段的所有主机
192.168.2.168 指定主机
localhost 指定主机
with_option参数
GRANT OPTION： 授权选项
MAX_QUERIES_PER_HOUR： 定义每小时允许执行的查询数
MAX_UPDATES_PER_HOUR： 定义每小时允许执行的更新数
MAX_CONNECTIONS_PER_HOUR： 定义每小时可以建立的连接数
MAX_USER_CONNECTIONS： 定义单个用户同时可以建立的连接数
mysql> grant select(id),insert(id) on tigerfive.t1 to 'xiaowu'@'172.16.70.%' identified by '123';
mysql> grant select,insert on tigerfive.t1 to 'xiaowu'@'172.16.70.%' identified by '123';
mysql> grant all on tigerfive.t1 to 'xiaowu'@'172.16.70.%' identified by '123';
mysql> grant all on *.* to 'xiaowu'@'172.16.70.%' identified by '123';
mysql> grant all on *.* to 'xiaowu'@'172.16.70.%' ；
mysql> grant all on *.* to 'xiaowu'@'%'；
查看权限
看自己的权限：
SHOW GRANTS\G
看别人的权限：
SHOW GRANTS FOR admin1@'%'\G
撤销权限:revoke
语法：
REVOKE 权限列表 ON 数据库名 FROM 用户名@‘客户端主机’
mysql> revoke all on *.* from 'xiaowu'@'%';
mysql> revoke delete on *.* from admin1@’%’; //回收部分权限
REVOKE ALL PRIVILEGES ON *.* FROM admin2@’%’; //回收所有权限
REVOKE ALL PRIVILEGES,GRANT OPTION ON *.* FROM 'admin2'@'%';
不区分大小写
lower_case_table_names=1 不区分大小写 validate_password=off 密码规则关闭
日志管理
MySQL日志管理 /etc/my.cnf 作用 配置文件 error log 错误日志 排错 /var/log/mysqld.log【默认开启】 bin log 二进制日志 备份 增量备份 DDL DML DCL Relay log 中继日志 复制 接收 replication master slow log 慢查询日志 调优 查询时间超过指定值 Error Log log-error=/var/log/mysqld.log 编译安装的在/usr/lib/mysql/ Binary Log log-bin=/var/log/mysql-bin/slave2 server-id=2 [root@slave2 ~]# mkdir /var/lib/mysql-bin [root@slave2 ~]# chown mysql.mysql /var/lib/mysql-bin/ [root@slave2 ~]# systemctl restart mysqld 注： 1. 重启mysqld 会截断 2. flush logs 会截断 3. reset master 删除所有binlog rm -rf / 4. 删除部分 PURGE BINARY LOGS TO 'mysql-bin.010'; PURGE BINARY LOGS BEFORE '2016-04-02 22:46:26'; 5. 暂停 仅当前会话 SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=1; 6. 截取binlog all： # mysqlbinlog mysql.000002 datetime： # mysqlbinlog mysql.000002 --start-datetime="2018-12-05 10:02:56" # mysqlbinlog mysql.000002 --stop-datetime="2018-12-05 11:02:54" # mysqlbinlog mysql.000002 --start-datetime="2018-12-05 10:02:56" --stop-datetime="2018-12-05 11:02:54" position： # mysqlbinlog mysql.000002 --start-position=260 # mysqlbinlog mysql.000002 --stop-position=260 # mysqlbinlog mysql.000002 --start-position=260 --stop-position=930 Slow Query Log slow_query_log=1 slow_query_log_file=/var/log/mysql-slow/slow.log long_query_time=3 [root@slave2 ~]# mkdir /var/log/mysql-slow/ [root@slave2 ~]# chown mysql.mysql /var/log/mysql-slow/ [root@slave2 ~]# systemctl restart mysqld 查看慢查询日志 测试:BENCHMARK(count,expr) SELECT BENCHMARK(50000000,2*3); ----------- binlog sql乱码问题 添加如下配置 Binary Log log-bin=/var/log/mysql-bin/slave2 server-id=2 binlog_format = row # 行模式复制 binlog_row_image = full # 默认是FULL，记录每一行的变更，minimal只记录影响后的行 binlog_rows_query_log_events = 1 # 在binlog中记录原生SQL //用下列配置替换binlog_format= row 即可显示原始sql语句 transaction_isolation = REPEATABLE-READ binlog_format=MIXED mysqlbinlog --base64-output=DECODE-ROWS -v mysql.000002
MySQL数据库热备
MySQL备份概述
MySQL逻辑备份mysqldump
mysqldump逻辑备份
数据一致，服务可用。:如何保证数据一致，在备份的时候进行锁表会自动锁表。锁住之后在备份。
本身为客户端工具: 远程备份语法: # mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql 本地备份语法: # mysqldump -u用户名 -p密码 数据库名 > 备份文件.sql
1.常用备份选项
-A, --all-databases
备份所有库
-B, –databases bbs test mysql
备份多个数据库
–no-data，-d
不导出任何数据，只导出数据库表结构。
2.备份表
语法: # mysqldump -u root -p1 db1 t1 > /db1.t1.bak [root@mysql-server ~]# mkdir /home/back #创建备份目录 [root@mysql-server ~]# mysqldump -uroot -p'qf123' company employee5 > /home/back/company.employee5.bak mysqldump: [Warning] Using a password on the command line interface can be insecure. 备份多个表： 语法: mysqldump -u root -p1 db1 t1 t2 > /db1.t1_t2.bak [root@mysql-server ~]# mysqldump -uroot -p'qf123' company new_t1 new_t2 > /home/back/company.new_t1_t2.bak
3、备份库
备份一个库：相当于将这个库里面的所有表全部备份。 语法: # mysqldump -u root -p1 db1 > /db1.bak [root@mysql-server ~]# mysqldump -uroot -p'qf123' company > /home/back/company.bak 备份多个库（-B）： 语法：mysqldump -u root -p1 -B db1 db2 db3 > /db123.bak [root@mysql-server ~]# mysqldump -uroot -p'qf123' -B company testdb > /home/back/company_testdb.bak 备份所有的库： 语法：# mysqldump -u root -p1 -A > /alldb.bak [root@mysql-server ~]# mysqldump -uroot -p'qf123' -A > /home/back/allbase.bak
到目录下面查看一下：
4、恢复数据库和表
为保证数据一致性，应在恢复数据之前停止数据库对外的服务,停止binlog日志 因为binlog使用binlog日志恢复数据时也会产生binlog日志。
为实验效果先将刚才备份的数据库和表删除了。登陆数据库：
[root@mysql-server ~]# mysql -uroot -pqf123
mysql> show databases;
mysql> drop database company;
mysql> \q
5、恢复库
登陆mysql创建一个库 mysql> create database company; 恢复： [root@mysql-server ~]# mysql -uroot -p'qf123' company < /home/back/company.bak
6、恢复表
登陆到刚才恢复的库中将其中的一个表删除掉
mysql> show databases;
mysql> use company
mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| employee5 |
| new_t1 |
| new_t2 |
+-------------------+
mysql> drop table employee5;
开始恢复:
mysql> set sql_log_bin=0; #停止binlog日志（可选）
Query OK, 0 rows affected (0.00 sec)
mysql> source /home/back/company.employee5.bak; -------加路径和备份的文件
恢复方式二：
# mysql -u root -p1 db1 < db1.t1.bak
库名 备份的文件路径
7、备份及恢复表结构
1.备份表结构： 语法：mysqldump -uroot -p123456 -d database table > dump.sql [root@mysql-server ~]# mysqldump -uroot -p'qf123' -d company employee5 > /home/back/emp.bak 恢复表结构： 登陆数据库创建一个库 mysql> create database t1; 语法：# mysql -u root -p1 -D db1 < db1.t1.bak [root@mysql-server ~]# mysql -uroot -p'qf123' -D t1 < /home/back/emp.bak
登陆数据查看：
8、在库里数据的导入导出。没有表结构。
表的导出和导入只备份表内记录，不会备份表结构，需要通过mysqldump备份表结构，恢复时先恢复表结构，再导入数据。
mysql> show variables like "secure_file_priv"; ----查询导入导出的目录。（保证数据安全做共享）
修改安全文件目录： 1.创建一个目录：mkdir 路径目录 [root@mysql-server ~]# mkdir /sql 2.修改权限 [root@mysql-server ~]# chown mysql.mysql /sql 3.编辑配置文件： vim /etc/my.cnf 在[mysqld]里追加 secure_file_priv=/sql 4.重新启动mysql.
1.到出数据 登陆数据查看数据 mysql> show databases; #找到test库 mysql> use test #进入test库 mysql> show tables; #找到它t3表 mysql> select * from t3 into outfile '/sql/test.t3.bak'; 添加修饰的： mysql> select * from t3 into outfile '/sql/test.t3.bak1' fields terminated by ',' lines terminated by '\n'; 注： fields terminated by ',' ：字段以逗号分割 lines terminated by '\n'：结尾换行
2.数据的导入 先将原来表里面的数据清除掉，只保留表结构 mysql> delete from t3; mysql> load data infile '/sql/test.t3.bak' into table t3; 如果将数据导入别的表，需要创建这个表并创建相应的表结构。
Mysql bin-log日志恢复
需要开启binlog日志功能
加上配置之后重启服务，使其生效
[root@mysql-1 mysql]# systemctl restart mysqld
mysql> flush logs #刷新binlog日志，使下面的语句存放到下一个binlog日志中 mysql> create table testdb.t2(id int);
根据位置恢复
找到要恢复的sql语句的起始位置、结束位置
[root@mysql-1 mysql]# mysqlbinlog mysql-bin.000002 #查看binlog日志内容
mysql>drop table testdb.t2(id int); //删除表格。注意：增删改都会记录到binlog日志中。 查看日志[bin-log带加密的时候需要解密] # mysqlbinlog --base64-output=DECODE-ROWS -v log-file [数据恢复时不需要解密] [root@mysql-1 mysql]# mysqlbinlog --start-position 219 --stop-position 321 mysql-bin.000002 |mysql -uroot -p123 #恢复语句
作业：
根据binlog日志的时间点恢复
找到要恢复sql语句的起始时间、结束时间
mysqlbinlog --start-datetime=‘2019-8-21 23:55:54’ --stop-datetime=‘2019-8-21 23:56:541’ mylog.000001 | mysql -u root -p1
MySQL物理备份Innobackupex
percona-xtrabackup 物理备份 + binlog
是开源免费的支持MySQL 数据库热备份的软件，它能对InnoDB和XtraDB存储引擎的数据库非阻塞地备份。它不暂停服务创建Innodb热备份；
为mysql做增量备份；在mysql服务器之间做在线表迁移；使创建replication更加容易；备份mysql而不增加服务器的负载。
percona是一家老牌的mysql技术咨询公司。它不仅提供mysql的技术支持、培训、咨询，还发布了mysql的分支版本–percona Server。并围绕percona Server还发布了一系列的mysql工具。
1、安装xtrabackup
安装xtrabackup # wget http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm # rpm -ivh percona-release-0.1-4.noarch.rpm [root@mysql-server yum.repos.d]# vim percona-release.repo
修改如下内容：将原来的1改为0
[root@mysql-server yum.repos.d]# yum -y install percona-xtrabackup-24.x86_64 rpm安装方式 # wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.22/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm # yum localinstall -y percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm
2.完全备份流程:
创建备份目录： [root@mysql-server ~]# mkdir /xtrabackup/full -p 备份： [root@mysql-server ~]# innobackupex --user=root --password='123' /xtrabackup/full
可以查看一下: [root@mysql-server ~]# cd /xtrabackup/full/ [root@mysql-server full]# ls 2019-08-20_11-47-49 ======================================================= 完全备份恢复流程 1. 停止数据库 2. 清理环境 3. 重演回滚－－> 恢复数据 4. 修改权限 5. 启动数据库 1.关闭数据库： [root@mysql-server ~]# systemctl stop mysqld [root@mysql-server ~]# rm -rf /var/lib/mysql/* [root@mysql-server ~]# rm -rf /var/log/mysqld.log #可选操作 [root@mysql-server ~]# rm -rf /var/log/mysql-slow/slow.log #可选操作 2.恢复之前的验证恢复: [root@mysql-server ~]# innobackupex --apply-log /xtrabackup/full/2019-08-20_11-47-49 3.确认数据库目录： 恢复之前需要确认配置文件内有数据库目录指定，不然xtrabackup不知道恢复到哪里 # cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql 4.恢复数据： [root@mysql-server ~]# innobackupex --copy-back /xtrabackup/full/2019-08-20_11-47-49 5.修改权限： [root@mysql-server ~]# chown mysql.mysql /var/lib/mysql -R 启动数据库: [root@mysql-server ~]# systemctl start mysqld
3.增量备份流程
原理：每次备份上一次备份到现在产生的新数据
1.在数据库上面创建一个测试的库
1.完整备份:周一
[root@mysql-server ~]# rm -rf /xtrabackup/* [root@mysql-server ~]# innobackupex --user=root --password='123' /xtrabackup [root@mysql-server ~]# cd /xtrabackup/ [root@mysql-server xtrabackup]# ls 2019-08-20_14-51-35 [root@mysql-server xtrabackup]# cd 2019-08-20_14-51-35/ [root@mysql-server 2019-08-20_14-51-35]# ls backup-my.cnf ib_buffer_pool mysql sys testdb xtrabackup_info company ibdata1 performance_schema test xtrabackup_checkpoints xtrabackup_logfile
2、增量备份：周二 —— 周三
在数据库中插入周二的数据: mysql> insert into testdb.t1 values(2); #模拟周二 [root@mysql-server ~]# innobackupex --user=root --password='123' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2019-08-20_14-51-35/ [root@mysql-server ~]# cd /xtrabackup/ [root@mysql-server xtrabackup]# ls 2019-08-20_14-51-35 2019-08-20_15-04-29 ---相当于周二的增量备份
在数据库中插入周三的数据: mysql> insert into testdb.t1 values(3); #模拟周三 [root@mysql-server ~]# innobackupex --user=root --password='123' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2019-08-20_15-04-29/ #基于前一天的备份为目录 [root@mysql-server ~]# cd /xtrabackup/ [root@mysql-server xtrabackup]# ls 2019-08-20_14-51-35 2019-08-20_15-04-29 2019-08-20_15-10-56 ---相当于周三的增量备份
查看一下备份目录:
[root@mysql-server ~]# ls /xtrabackup/
2019-08-20_14-51-35 2019-08-20_15-04-29 2019-08-20_15-10-56
全备周一 增量周二 增量周三
增量备份恢复流程 1. 停止数据库 2. 清理环境 3. 依次重演回滚redo log－－> 恢复数据 4. 修改权限 5. 启动数据库
[root@mysql-server ~]# systemctl stop mysqld [root@mysql-server ~]# rm -rf /var/lib/mysql/* 依次重演回滚redo log: [root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_14-51-35 周二 --- 周三 [root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_14-51-35 --incremental-dir=/xtrabackup/2019-08-20_15-04-29 [root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_14-51-35 --incremental-dir=/xtrabackup/2019-08-20_15-10-56/ 恢复数据: [root@mysql-server ~]# innobackupex --copy-back /xtrabackup/2019-08-20_14-51-35/ 修改权限 [root@mysql-server ~]# chown -R mysql.mysql /var/lib/mysql [root@mysql-server ~]# systemctl start mysqld 登陆上去看一下:
4、差异备份流程
清理备份的环境:
[root@mysql-server ~]# rm -rf /xtrabackup/* 登陆数据库，准备环境 mysql> create database testdb; mysql> delete from testdb.t1; mysql> insert into testdb.t1 values(1); #插入数据1，模拟周一 mysql> select * from testdb.t1; +------+ | id | +------+ | 1 | +------+ mysql> \q 查看时间: [root@mysql-server ~]# date Tue Aug 20 15:39:59 CST 2019 1、完整备份：周一 [root@mysql-server ~]# innobackupex --user=root --password='123' /xtrabackup 2、差异备份：周二 —— 周三 语法: # innobackupex --user=root --password=888 --incremental /xtrabackup --incremental-basedir=/xtrabackup/完全备份目录（周一） 3.修改时间： [root@mysql-server ~]# date 08211543 Wed Aug 21 15:43:00 CST 2019 4.在登陆mysql： mysql> insert into testdb.t1 values(2); #插入数据2，模拟周二 差异备份周二的 [root@mysql-server ~]# innobackupex --user=root --password='123' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2019-08-20_15-42-02/ #备份目录基于周一的备份 5.再次登陆mysql mysql> insert into testdb.t1 values(3); #插入数据，模拟周三 6.在次修改时间 [root@mysql-server ~]# date 08221550 Thu Aug 22 15:50:00 CST 2019 7.在次差异备份 [root@mysql-server ~]# innobackupex --user=root --password='123' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2019-08-20_15-42-02/ #还是基于周一的备份 8.延申到周四 mysql> insert into testdb.t1 values(4); 9.修改时间 [root@mysql-server ~]# date 08231553 Fri Aug 23 15:53:00 CST 2019 10.差役备份周四 [root@mysql-server ~]# innobackupex --user=root --password='123' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2019-08-20_15-42-02/ #还是基于周一的备份 11.查看一下备份目录 [root@mysql-server ~]# ls /xtrabackup/ 2019-08-20_15-42-02 2019-08-21_15-46-53 2019-08-22_15-51-15 2019-08-23_15-53-28 周一 周二 周三 周四
差异备份恢复流程 1. 停止数据库 2. 清理环境 3. 重演回滚redo log（周一，某次差异）－－> 恢复数据 4. 修改权限 5. 启动数据库 6. binlog恢复 停止数据库 [root@mysql-server ~]# systemctl stop mysqld [root@mysql-server ~]# rm -rf /var/lib/mysql/* 1.恢复全量的redo log 语法: # innobackupex --apply-log --redo-only /xtrabackup/完全备份目录（周一） [root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_15-42-02/ 2.恢复差异的redo log 语法:# innobackupex --apply-log --redo-only /xtrabackup/完全备份目录（周一）--incremental-dir=/xtrabacku/某个差异备份 这里我们恢复周三的差异备份 [root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_15-42-02/ --incremental-dir=/xtrabackup/2019-08-22_15-51-15/ #我们恢复周三的差异备份 3.恢复数据 语法:# innobackupex --copy-back /xtrabackup/完全备份目录（周一） [root@mysql-server ~]# innobackupex --copy-back /xtrabackup/2019-08-20_15-42-02/ 修改权限： [root@mysql-server ~]# chown -R mysql.mysql /var/lib/mysql [root@mysql-server ~]# systemctl start mysqld
登陆mysql查看一下:
只有123.因为我们恢复的是周三的差异备份。
MySQL-Replication
MySQL Replication
主从复制（也称 AB 复制）允许将来自一个MySQL数据库服务器（主服务器）的数据复制到一个或多个MySQL数据库服务器（从服务器）。
复制是异步的 从站不需要永久连接以接收来自主站的更新。
根据配置，您可以复制数据库中的所有数据库，所选数据库甚至选定的表。
MySQL中复制的优点包括：
- 横向扩展解决方案 - 在多个从站之间分配负载以提高性能。在此环境中，所有写入和更新都必须在主服务器上进行。但是，读取可以在一个或多个从设备上进行。该模型可以提高写入性能（因为主设备专用于更新），同时显着提高了越来越多的从设备的读取速度。
- 数据安全性 - 因为数据被复制到从站，并且从站可以暂停复制过程，所以可以在从站上运行备份服务而不会破坏相应的主数据。
- 分析 - 可以在主服务器上创建实时数据，而信息分析可以在从服务器上进行，而不会影响主服务器的性能。
- 远程数据分发 - 您可以使用复制为远程站点创建数据的本地副本，而无需永久访问主服务器。
Replication的原理
前提是作为主服务器角色的数据库服务器必须开启二进制日志
主服务器上面的任何修改都会通过自己的 I/O tread(I/O 线程)保存在二进制日志 Binary log 里面。
从服务器上面也启动一个 I/O thread，通过配置好的用户名和密码, 连接到主服务器上面请求读取二进制日志，然后把读取到的二进制日志写到本地的一个Realy log（中继日志）里面。
从服务器上面同时开启一个 SQL thread 定时检查 Realy log(这个文件也是二进制的)，如果发现有更新立即把更新的内容在本机的数据库上面执行一遍。
每个从服务器都会收到主服务器二进制日志的全部内容的副本。
从服务器设备负责决定应该执行二进制日志中的哪些语句。
除非另行指定，否则主从二进制日志中的所有事件都在从站上执行。
如果需要，您可以将从服务器配置为仅处理一些特定数据库或表的事件。
重要: 您无法将主服务器配置为仅记录特定事件。
每个从站(从服务器)都会记录二进制日志坐标：
文件名
文件中它已经从主站读取和处理的位置。
由于每个从服务器都分别记录了自己当前处理二进制日志中的位置，因此可以断开从服务器的连接，重新连接然后恢复继续处理。
一主多从
如果一主多从的话，这时主库既要负责写又要负责为几个从库提供二进制日志。此时可以稍做调整，将二进制日志只给某一从，这一从再开启二进制日志并将自己的二进制日志再发给其它从。或者是干脆这个从不记录只负责将二进制日志转发给其它从，这样架构起来性能可能要好得多，而且数据之间的延时应该也稍微要好一些。工作原理图如下：
关于二进制日志
mysqld将数字扩展名附加到二进制日志基本名称以生成二进制日志文件名。每次服务器创建新日志文件时，该数字都会增加，从而创建一系列有序的文件。每次启动或刷新日志时，服务器都会在系列中创建一个新文件。服务器还会在当前日志大小达到max_binlog_size参数设置的大小后自动创建新的二进制日志文件 。二进制日志文件可能会比max_binlog_size使用大型事务时更大， 因为事务是以一个部分写入文件，而不是在文件之间分割。
为了跟踪已使用的二进制日志文件， mysqld还创建了一个二进制日志索引文件，其中包含所有使用的二进制日志文件的名称。默认情况下，它具有与二进制日志文件相同的基本名称，并带有扩展名'.index'。在mysqld运行时，您不应手动编辑此文件。
术语二进制日志文件通常表示包含数据库事件的单个编号文件。
术语 二进制日志 表示含编号的二进制日志文件集加上索引文件。
SUPER 权限的用户可以使用SET sql_log_bin=0语句禁用其当前环境下自己的语句的二进制日志记录
配置Replication
配置步骤：
- 在主服务器上，您必须启用二进制日志记录并配置唯一的服务器ID。需要重启服务器。
编辑主服务器的配置文件 my.cnf，添加如下内容
[mysqld] log-bin=/var/log/mysql/mysql-bin server-id=1
创建日志目录并赋予权限
[root@mysql ~]# mkdir /var/log/mysql [root@mysql ~]# chown mysql.mysql /var/log/mysql
重启服务
[root@mysql ~]# systemctl restart mysqld
注意：：
如果省略server-id（或将其显式设置为默认值0），则主服务器拒绝来自从服务器的任何连接。 为了在使用带事务的InnoDB进行复制设置时尽可能提高持久性和一致性， 您应该在master my.cnf文件中使用以下配置项： innodb_flush_log_at_trx_commit = 1 sync_binlog = 1 确保未在复制主服务器上启用skip-networking选项。 如果已禁用网络，则从站无法与主站通信，并且复制失败。
2.应该创建一个专门用于复制数据的用户
每个从服务器需要使用MySQL 主服务器上的用户名和密码连接到主站。
例如，计划使用用户 repl 可以从任何主机上连接到 master 上进行复制操作, 并且用户 repl 仅可以使用复制的权限。
在 主服务器 上执行如下操作
mysql> CREATE USER 'repl'@'%' mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' identified by '123'; mysql> flush privileges; mysql>
3.在从服务器上使用刚才的用户进行测试连接
[root@mysql ~]# mysql -urepl -p'123' -hmysql-master1 #mysql-master1做了本地解析的master
下面的操作根据如下情况继续
主服务器中有数据
- 如果在启动复制之前有现有数据需要与从属设备同步，请保持客户端正常运行，以便锁定保持不变。这可以防止进行任何进一步的更改，以便复制到从站的数据与主站同步。
- 在主服务器中导出先有的数据
如果主数据库包含现有数据，则必须将此数据复制到每个从站。有多种方法可以实现:
- 使用mysqldump工具创建要复制的所有数据库的转储。这是推荐的方法，尤其是在使用时
InnoDB。
[root@mysql ~]# mysqldump -u用户名 -p密码 --all-databases --master-data=1 > dbdump.db 这里的用户是主服务器的用户
如果不使用 --master-data 参数，则需要手动锁定单独会话中的所有表。
- 从主服务器中使用
scp或rsync等工具，把备份出来的数据传输到从服务器中。
在主服务中执行如下命令
[root@mysql ~]# scp dbdump.db root@mysql-slave1:/root/ 这里的 mysql-slave1 需要能被主服务器解析出 IP 地址，或者说可以在主服务器中 ping 通。
- 配置从服务器，并重启
在从服务器上编辑其配置文件my.cnf并添加如下内容
// my.cnf 文件 [mysqld] server-id=2
- 导入数据到从服务器，并配置连接到主服务器的相关信息
登录到从服务器上，执行如下操作
/*导入数据*/ mysql> source /root/fulldb.dump
在从服务器配置连接到主服务器的相关信息
mysql> CHANGE MASTER TO MASTER_HOST='mysql-master1', -- 主服务器的主机名(也可以是 IP) MASTER_USER='repl', -- 连接到主服务器的用户 MASTER_PASSWORD='123'; -- 到主服务器的密码
-
启动从服务器的复制线程
mysql> start slave; Query OK, 0 rows affected (0.09 sec)
检查是否成功
在从服务上执行如下操作，加长从服务器端 IO线程和 SQL 线程是否是 OK
mysql> show slave status\G
输出结果中应该看到 I/O 线程和 SQL 线程都是 YES, 就表示成功。
执行此过程后，在主服务上操作的修改数据的操作都会在从服务器中执行一遍，这样就保证了数据的一致性。
主服务器中无数据
主服务器中设置
my.cnf配置文件
[mysqld] log-bin=/var/log/mysql/mysql-bin server-id=1
创建日志目录并赋予权限
[root@mysql ~]# mkdir /var/log/mysql [root@mysql ~]# chown mysql.mysql /var/log/mysql
重启服务
mysql> CREATE USER 'repl'@'%' mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' identified by '123'; mysql> flush privileges; mysql>
3.在从服务器上使用刚才的用户进行测试连接
[root@mysql ~]# mysql -urepl -p'123' -hmysql-master1 #mysql-master1做了本地解析的master
下面的操作根据如下情况继续
从服务器设置
my.cnf配置文件
[mysqld] server-id=3
重启服务
- 查看主服务器的二进制日志的名称
通过使用命令行客户端连接到主服务器来启动主服务器上的会话，并通过执行以下 FLUSH TABLES WITH READ LOCK 语句来刷新所有表和阻止写语句：
mysql> FLUSH TABLES WITH READ LOCK;
mysql> show master status \G
****************** 1. row ****************
File: mysql-bin.000001
Position: 0
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
- 在从服务器的 mysql 中执行如下语句
mysql> CHANGE MASTER TO MASTER_HOST='mysql-master1', MASTER_USER='repl', MASTER_PASSWORD='123', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=0; mysql> start slave;
查看
在master上执行show binlog events命令，可以看到第一个binlog文件的内容。
mysql> show binlog events\G
*************************** 1. row ***************************
Log_name: mysql-bin.000001
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 107
Info: Server ver: 5.5.28-0ubuntu0.12.10.2-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql-bin.000001
Pos: 107
Event_type: Query
Server_id: 1
End_log_pos: 181
Info: create user rep
*************************** 3. row ***************************
Log_name: mysql-bin.000001
Pos: 181
Event_type: Query
Server_id: 1
End_log_pos: 316
Info: grant replication slave on *.* to rep identified by '123456'
3 rows in set (0.00 sec)
Log_name 是二进制日志文件的名称，一个事件不能横跨两个文件
Pos 这是该事件在文件中的开始位置
Event_type 事件的类型，事件类型是给slave传递信息的基本方法，每个新的binlog都以Format_desc类型开始，以Rotate类型结束
Server_id 创建该事件的服务器id
End_log_pos 该事件的结束位置，也是下一个事件的开始位置，因此事件范围为Pos~End_log_pos - 1
Info 事件信息的可读文本，不同的事件有不同的信息
在从站上暂停复制
您可以使用STOP SLAVE和 START SLAVE语句停止并启动从站上的复制 。
要停止从主服务器处理二进制日志，请使用 STOP SLAVE：
mysql> STOP SLAVE;
当复制停止时，从I / O线程停止从主二进制日志读取事件并将它们写入中继日志，并且SQL线程停止从中继日志读取事件并执行它们。您可以通过指定线程类型单独暂停I / O或SQL线程：
mysql> STOP SLAVE IO_THREAD; mysql> STOP SLAVE SQL_THREAD;
要再次开始执行，请使用以下START SLAVE语句：
mysql> START SLAVE;
要启动特定线程，请指定线程类型：
mysql> START SLAVE IO_THREAD; mysql> START SLAVE SQL_THREAD;
复制原理实现细节（了解） MySQL复制功能使用三个线程实现，一个在主服务器上，两个在从服务器上： Binlog转储线程 主设备创建一个线程，以便在从设备连接时将二进制日志内容发送到从设备。可以SHOW PROCESSLIST在主服务器的输出中将此线程标识为Binlog Dump线程。 二进制日志转储线程获取主机二进制日志上的锁，用于读取要发送到从机的每个事件。一旦读取了事件，即使在事件发送到从站之前，锁也会被释放。 从属 I/O线程 在从属服务器上发出 START SLAVE 语句时，从属服务器会创建一个 I/O 线程，该线程连接到主服务器并要求主服务器发送其在二进制日志中的更新记录。 从属 I/O线程读取主Binlog Dump线程发送的更新 （请参阅上一项）并将它们复制到包含从属中继日志的本地文件。 此线程的状态显示为 Slave_IO_running输出 SHOW SLAVE STATUS或 Slave_running输出中的状态SHOW STATUS。 从属SQL线程 从属设备创建一个SQL线程来读取由从属 I/O 线程写入的中继日志，并执行其中包含的事件。 当从属服务器从放的事件，追干上主服务器的事件后，从属服务器的 I/O 线程将会处于休眠状态，直到主服务器的事件有更新时，被主服务器发送的信号唤醒。 在前面的描述中，每个主/从连接有三个线程。具有多个从站的主站为每个当前连接的从站创建一个二进制日志转储线程，每个从站都有自己的I / O和SQL线程。 从站使用两个线程将读取更新与主站分开并将它们执行到独立任务中。因此，如果语句执行缓慢，则不会减慢读取语句的任务。例如，如果从服务器尚未运行一段时间，则当从服务器启动时，其I / O线程可以快速从主服务器获取所有二进制日志内容，即使SQL线程远远落后。如果从服务器在SQL线程执行了所有获取的语句之前停止，则I / O线程至少已获取所有内容，以便语句的安全副本本地存储在从属的中继日志中，准备在下次执行时执行奴隶开始。
配置Replication(gtid方式)
基于事务的Replication，就是利用GTID来实现的复制
GTID（全局事务标示符）最初由google实现，在MySQL 5.6中引入.GTID在事务提交时生成，由UUID和事务ID组成.uuid会在第一次启动MySQL时生成，保存在数据目录下的auto .cnf文件里，事务ID则从1开始自增使用GTID的好处主要有两点：
- 不再需要指定传统复制中的master_log_files和master_log_pos，使主从复制更简单可靠
- 可以实现基于库的多线程复制，减小主从复制的延迟
实验环境要求： 5.7.6 以上版本
主库配置
[mysqld] log-bin=/var/log/mysql/mysql-bin server-id=1 gtid_mode=ON enforce_gtid_consistency=1 # 强制执行GTID一致性。
重启服务
其他和之前的一样
- 创建专属用户并授权
- 假如有数据导出数据
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '123'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; mysql>
从库配置
测试用户有效性
mysql -urepl -p'123' -hmysql-master1
[mysqld] server-id=2 gtid_mode=ON enforce_gtid_consistency=1 # 可选项, 把连接到 master 的信息存到数据库中的表中 master-info-repository=TABLE relay-log-info-repository=TABLE
重启服务
假如有数据，先导入数据
mysql> source dump.db
Mysql 终端执行连接信息
mysql> CHANGE MASTER TO MASTER_HOST='172.16.153.10', MASTER_USER='repl', MASTER_PASSWORD='123', MASTER_AUTO_POSITION=1; > start slave;
Replication故障排除
开启 GTID 后的导出导入数据的注意点
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don’t want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events
意思是： 当前数据库实例中开启了 GTID 功能, 在开启有 GTID 功能的数据库实例中, 导出其中任何一个库, 如果没有显示地指定–set-gtid-purged参数, 都会提示这一行信息. 意思是默认情况下, 导出的库中含有 GTID 信息, 如果不想导出包含有 GTID 信息的数据库, 需要显示地添加–set-gtid-purged=OFF参数.
mysqldump -uroot -p --set-gtid-purged=OFF --all-databases > alldb.db
导入数据是就可以相往常一样导入了。
UUID一致，导致主从复制I/O线程不是yes
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work
致命错误：由于master和slave具有相同的mysql服务器uuid，从I/O线程将停止；这些uuid必须不同才能使复制工作。
问题提示主从使用了相同的server UUID，一个个的检查：
检查主从server_id
主库：
mysql> show variables like ‘server_id’;
±--------------±------+
| Variable_name | Value |
±--------------±------+
| server_id | 1 |
±--------------±------+
1 row in set (0.01 sec)
从库：
mysql> show variables like ‘server_id’;
±--------------±------+
| Variable_name | Value |
±--------------±------+
| server_id | 2 |
±--------------±------+
1 row in set (0.01 sec)
server_id不一样，排除。
检查主从状态：
主库：
mysql> show master status;
±-----------------±---------±-------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------±-----------------±------------------+
| mysql-bin.000001 | 154 | | | |
±-----------------±---------±-------------±-----------------±------------------+
1 row in set (0.00 sec)
从库：
mysql> show master status;
±-----------------±---------±-------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------±-----------------±------------------+
| mysql-bin.000001 | 306 | | | |
±-----------------±---------±-------------±-----------------±------------------+
1 row in set (0.00 sec)
File一样，排除。
最后检查发现他们的auto.cnf中的server-uuid是一样的。。。
[root@localhost ~]# vim /var/lib/mysql/auto.cnf
[auto]
server-uuid=4f37a731-9b79-11e8-8013-000c29f0700f
修改uuid并重启服务
数据库中间MyCAT读写分离实现 (重要！！！)
Mycat 是一个开源的分布式数据库系统，但是由于真正的数据库需要存储引擎，而 Mycat 并没有存 储引擎，所以并不是完全意义的分布式数据库系统。 那么 Mycat 是什么？Mycat 是数据库中间件，就是介于数据库与应用之间，进行数据处理与交互的中间服 务。
MyCAT 是使用 JAVA 语言进行编写开发，使用前需要先安装 JAVA 运行环境(JRE),由于 MyCAT 中使用了 JDK7 中的一些特性，所以要求必须在 JDK7 以上的版本上运行。
部署环境
1下载JDK
[root@mycat ~]# wget --no-cookies \ --no-check-certificate \ --header \ "Cookie: oraclelicense=accept-securebackup-cookie" \ http://download.oracle.com/otn-pub/java/jdk/8u181-\ b13/96a7b8442fe848ef90c96a2fad6ed6d1/jdk-8u181-linux-\ x64.tar.gz // --no-check-certificate 表示不校验SSL证书，因为中间的两个302会访问https，会涉及到证书的问题，不校验能快一点，影响不大.
2.解压文件
[root@mycat ~]# tar -xf jdk-8u181-linux-x64.tar.gz -C /usr/local/ [root@mycat ~]# ln -s /usr/local/jdk1.8.0_181/ /usr/local/java
3.配置环境变量
[root@mycat ~]# vim /etc/profile.d/java.sh export JAVA_HOME=/usr/local/java export PATH=$JAVA_HOME/bin:$PATH export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar 使环境变量生效 [root@mycat ~]# source /etc/profile.d/java.sh
部署Mycat
下载 [root@mycat ~]# wget http://dl.mycat.io/1.6.5/Mycat-server-1.6.5-release-20180122220033-linux.tar.gz 新地址: wget http://dl.mycat.org.cn/1.6.5/Mycat-server-1.6.5-release-20180122220033-linux.tar.gz 解压 [root@mycat ~]# tar xf Mycat-server-1.6.5-release-20180122220033-linux.tar.gz -C /usr/local/
配置Mycat
认识配置文件
MyCAT 目前主要通过配置文件的方式来定义逻辑库和相关配置:
/usr/local/mycat/conf/server.xml 定义用户以及系统相关变量，如端口等。其中用户信息是前端应用程序连接 mycat 的用户信息。
/usr/local/mycat/conf/schema.xml 定义逻辑库，表、分片节点等内容。
配置 server.xml
以下为代码片段
下面的用户和密码是应用程序连接到 MyCat 使用的，可以自定义配置
而其中的schemas 配置项所对应的值是逻辑数据库的名字，也可以自定义，但是这个名字需要和后面 schema.xml 文件中配置的一致。
vim server.xml
<!--下面的用户和密码是应用程序连接到 MyCat 使用的.schemas 配置项所对应的值是逻辑数据库的名字,这个名字需要和后面 schema.xml 文件中配置的一致。-->
<user name="mycatdb" defaultAccount="true">
<property name="password">1</property>
<property name="schemas">mycat_db</property>
<!-- 表级 DML 权限设置 -->
<!--
<privileges check="false">
<schema name="TESTDB" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
</user>
<!--下面是另一个用户，并且设置的访问 TESTED 逻辑数据库的权限是 只读
<user name="mycatuser">
<property name="password">123</property>
<property name="schemas">mycat_db</property>
<property name="readOnly">true</property>
</user>
-->
</mycat:server>
== 上面的配置中，假如配置了用户访问的逻辑库，那么必须在 schema.xml 文件中也配置这个逻辑库，否则报错，启动 mycat 失败 ==
配置schema.xml
以下是配置文件中的每个部分的配置块儿
逻辑库和分表设置
<schema name="mycat_db" // 逻辑库名称,与server.xml的一致
checkSQLschema="false" // 不检查
sqlMaxLimit="100" // 最大连接数
dataNode="tiger1"> // 数据节点名称
<!--这里定义的是分表的信息-->
</schema>
数据节点
<dataNode name="tiger1" // 此数据节点的名称
dataHost="localhost1" // 主机组
database="mycat_test" /> // 真实的数据库名称
主机组
<dataHost name="localhost1" // 主机组
maxCon="1000" minCon="10" // 连接
balance="0" // 负载均衡
writeType="0" // 写模式配置
dbType="mysql" dbDriver="native" // 数据库配置
switchType="1" slaveThreshold="100">
<!--这里可以配置关于这个主机组的成员信息，和针对这些主机的健康检查语句-->
</dataHost>
balance 属性
负载均衡类型,目前的取值有 3 种:
1. balance="0", 不开启读写分离机制,所有读操作都发送到当前可用的 writeHost 上。
2. balance="1", 全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且 M1 与 M2
互为主备),正常情况下,M2,S1,S2 都参与 select 语句的负载均衡。
4. balance="2", 所有读操作都随机的在 writeHost、readhost 上分发。
5. balance="3", 所有读请求随机的分发到 wiriterHost 对应的 readhost 执行,writerHost 不负担读压力,注意 balance=3 只在 1.4 及其以后版本有,1.3 没有。
writeType 属性
负载均衡类型,目前的取值有 3 种:
1. writeType="0", 所有写操作发送到配置的第一个 writeHost,第一个挂了切到还生存的第二个writeHost,重新启动后已切换后的为准.
2. writeType="1",所有写操作都随机的发送到配置的 writeHost,1.5 以后废弃不推荐。
健康检查
<heartbeat>select user()</heartbeat>
读写配置
<writeHost host="hostM1" url="192.168.19.176:3306" user="root" password="1">
<!-- can have multi read hosts -->
<readHost host="hostS2" url="192.168.19.177:3306" user="root" password="1" />
</writeHost>
以下是组合为完整的配置文件，适用于一主一从的架构
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="mycat_db"
checkSQLschema="false"
sqlMaxLimit="100"
dataNode="tiger1">
<!--这里定义的是分库分表的信息-->
</schema>
<dataNode name="tiger1"
dataHost="localhost1" database="mycat_test" />
<dataHost name="localhost1"
maxCon="1000" minCon="10"
balance="0"
writeType="0"
dbType="mysql" dbDriver="native"
switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.19.176:3306"
user="root" password="1">
<!-- can have multi read hosts -->
<readHost host="hostS2" url="192.168.19.177:3306"
user="root" password="1" />
</writeHost>
</dataHost>
</mycat:schema>
启动 mycat
[root@mycat ~]# /usr/local/mycat/bin/mycat start 支持一下参数 start | restart |stop | status
在真实的 master 数据库上给用户授权
mysql> grant all on mycat_test.* to root@'%' identified by '1'; mysql> flush privileges;
测试
在 mycat 的机器上测试用户权限有效性
测试是否能正常登录上 主服务器
mysql -uroot -p'123' -h192.168.19.176
继续测试是否能登录上从服务器
mysql -uroot -p'123' -h192.168.19.177
通过客户端进行测试是否能登录到 mycat 上
192.168.19.178 是 mycat 的主机地址
注意端口号是 8066
[root@mysqlclient ~]# mysql -umycatdb -p1 -h192.168.19.178 -P8066 MySQL [(none)]> show databases; +----------+ | DATABASE | +----------+ | mycat_db | +----------+ 1 row in set (0.00 sec)
继续测试读写分离策略
使用 mysql 客户端工具使用 mycat 的账户和密码登录 mycat ,
之后执行 select 语句。
之后查询 mycat 主机上 mycat 安装目录下的 logs/mycat.log 日志。
在日志重搜索查询的语句或者查询 从库的 ip 地址，应该能搜索到
log_slave_updates
此参数是从的从主的同步时，同时写入binlog日志，
mysql优化
引擎
引擎
myisam 速度快
.myi index 存储索引
.myd data 存储数据
.frm 存储表结构
可以把数据分开存储，比放在一块硬盘里访问速度快。
默认的数据都保存在var下面，可以人为的改变他们的存储位置
#mkdir data 最好是不同的硬盘上
#chown mysql data
mysql> create table tb1(name char(20) not null,email char(20) not null) data directory='/data' index directory='/data';
指定数据文件的位置 指定索引文件的位置
上面的那一条只适用于myisam类型，.frm文件必须得放在var下，不能指定放到别的地方
innodb 大数据库 事务 外键
大型数据库用innodb
[root@www var]# pwd
/usr/local/mysql/var
[root@www var]# ls ib*
ibdata1 ib_logfile0 ib_logfile1
innodb类型的表的数据存在ibdata1里面，不像myisam生成３个文件， ib_logfile0 ib_logfile1存放日志
初始是10M,每次增加8M ，初始大小可以指定，要修改配置文件
#vim /etc/my.cnf
innodb_data_file_path=ibdata1:20M:autoextend：max:1000M
设定初始大小是20M，增幅也是8M 设定峰值是1000M,就是指定最大能增加到1000M
innodb_data_home_dir=/data
指定他的存储数据的位置，也就是ibdata1的位置
memory
memory 速度快，比myisam快30％，当数据库重启之后，数据就会丢失，因为他是存在内存里的
mysql> create table t20(id int,name char(10)) type=memory; 创建一个memory类型的表
mysql> create table t1(id int) engine=innodb;
----------------------------------------------------
查看引擎：
mysql>show engines;
mysql>show create table t1;
mysql>show table status like 't1';
切换默认引擎：
[mysqld]
default-storage-engine = innodb
修改已经存在的表的引擎
mysql> alter table t2 engine=myisam;
----------------------------------------------------
默认上面那些引擎类型都是开启，所以如果不想他们启动的话，可以修改配置文件
#vim /etc/my.cnf
skip-mrg_myisam
skip-csv
skip-memory
不想用哪个，加上跳过哪个就可以了
重启mysql就可以了，这样也可以优化数据库。
-----------------------------------------------------
修改数据存储位置实例(此例为rpm方式安装)
MySQL默认的数据文件存储目录为/var/lib/mysql。假如要把目录移到/home/data下需要进行下面几步：
1、home目录下建立data目录
cd /home
mkdir data
2、把MySQL服务进程停掉：
mysqladmin -u root -p shutdown
3、把/var/lib/mysql整个目录移到/home/data
mv /var/lib/mysql /home/data/
这样就把MySQL的数据文件移动到了/home/data/mysql下
4、找到my.cnf配置文件
如果/etc/目录下没有my.cnf配置文件，请到/usr/share/mysql/下找到*.cnf文件，拷贝其中一个到/etc/并改名为my.cnf)中。命令如下：
[root@test1 mysql]# cp /usr/share/mysql/my-medium.cnf /etc/my.cnf
5、编辑MySQL的配置文件/etc/my.cnf
为保证MySQL能够正常工作，需要指明mysql.sock文件的产生位置。修改socket=/var/lib/mysql/mysql.sock一行中等号右边的值为：/home/mysql/mysql.sock 。操作如下：
#vi my.cnf (用vi工具编辑my.cnf文件，找到下列数据修改之)
# The MySQL server
[mysqld]
port = 3306
#socket = /var/lib/mysql/mysql.sock（原内容，为了更稳妥用“#”注释此行）
socket = /home/data/mysql/mysql.sock （加上此行）
6、修改MySQL启动脚本/etc/rc.d/init.d/mysql
最后，需要修改MySQL启动脚本/etc/rc.d/init.d/mysql，把其中datadir=/var/lib/mysql一行中，等号右边的路径改成你现在的实际存放路径：home/data/mysql。
[root@test1 etc]# vi /etc/rc.d/init.d/mysql
#datadir=/var/lib/mysql （注释此行）
datadir=/home/data/mysql （加上此行）
7、重新启动MySQL服务
/etc/rc.d/init.d/mysql start
或用reboot命令重启Linux
以下是mysql数据目录修改出现
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)错误的解决方案：
本例是将Mysql的数据目录移动到了/home/data/下，即mysql.sock所在的真实目录为，/home/data/mysql/mysql.sock
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
mysql有tcp连接和socket连接方式，而上面这种错误一般是因为mysql是使用rpm方式安装的，它会自动寻找 /var/lib/mysql/mysql.sock 这个文件，是一种socket连接方式。
常见解决办法如下：
1、
创建/修改文件 /etc/my.cnf文件，在[mysqld]上面添加
[client]
#password=your_password
port=3306
socket=/home/data/mysql/mysql.sock
#在这里写上你的mysql.sock的正确位置。
2、
指定IP地址，使用tcp方式连接mysql，而不使用本地socket方式
#mysql -h127.0.0.1 -uuser -ppassword
3、
为 mysql.sock 加个连接，比如说实际的mysql.sock在 /home/data/mysql/ 下，则
#ln -s /home/data/mysql/mysql.sock /var/lib/mysql/mysql.sock即可
第2种方法:
1.# mv /var/lib/mysql/[DB_name] [New Location] #移动数据库文件
2.# ln -s [New Location] /var/lib/mysql/[DB_name] #建立软链接
引擎
存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据 的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型） 在Oracle 和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySql数据库提供了多种存储引擎。 用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。 选择存储引擎 1. 查看存储引擎 SHOW ENGINES\G show engines\G SHOW VARIABLES LIKE '%storage_engine%'; 2. 选择存储引擎 方法1. mysql> create table innodb1( -> id int -> )engine=innodb; mysql> show create table innodb1; 方法2. /etc/my.cnf [mysqld] default-storage-engine=INNODB MySQL常用的存储引擎 MyISAM存储引擎 由于该存储引擎不支持事务、也不支持外键，所以访问速度较快。因此当对事务完整性没有要求并以访问为主的应用适合使用该存储引擎。 InnoDB存储引擎 由于该存储引擎在事务上具有优势，即支持具有提交、回滚及崩溃恢复能力等事务特性，所以比MyISAM存储引擎占用更多的磁盘空间。 因此当需要频繁的更新、删除操作，同时还对事务的完整性要求较高，需要实现并发控制，建议选择。 MEMORY MEMORY存储引擎存储数据的位置是内存，因此访问速度最快，但是安全上没有保障。适合于需要快速的访问或临时表。 BLACKHOLE 黑洞存储引擎，可以应用于主备复制中的分发主库。
字符集设置
字符集设置
临时：
mysql> create database db1 CHARACTER SET = utf8;
mysql> create table t1(id int(10)) CHARACTER SET = utf8;
永久：
5.1版本设置：
#vim /etc/my.cnf
[mysqld]
default-character-set = utf8
character_set_server = utf8
[mysql]
default-character-set = utf8
5.5/5.6/5.7版本设置：
[mysqld]
character_set_server = utf8
慢查询配置
5.1版本
#vim /etc/my.cnf
[mysqld]
log-slow-queries=/var/lib/mysql/sql_row.log
long_query_time=3
查看是否设置成功：
mysql> show variables like '%query%';
5.5版本
[mysqld]
slow-query-log=on
slow_query_log_file=/var/lib/mysql/slowquery.log
long_query_time=1
#log-queries-not-using-indexes=on //列出没有使用索引的查询语句
5.7版本
[mysqld]
slow_query_log=1
slow_query_log_file=/var/log/mysql-slow/slow.log
long_query_time=3
# mkdir /var/log/mysql-slow/
# chown mysql.mysql /var/log/mysql-slow/
# systemctl restart mysqld
查看慢查询日志
测试:BENCHMARK(count,expr)
mysql> SELECT BENCHMARK(500000000,2*3);
client连接mysql慢
客户端连接MySQL数据库速度慢的问题 修改my.cnf配置，关闭DNS的反向解析参数 [mysqld] skip-name-resolve
mysql错误中文参照列表
1005：创建表失败 1006：创建数据库失败 1007：数据库已存在，创建数据库失败 1008：数据库不存在，删除数据库失败 1009：不能删除数据库文件导致删除数据库失败 1010：不能删除数据目录导致删除数据库失败 1011：删除数据库文件失败 1012：不能读取系统表中的记录 1020：记录已被其他用户修改 1021：硬盘剩余空间不足，请加大硬盘可用空间 1022：关键字重复，更改记录失败 1023：关闭时发生错误 1024：读文件错误 1025：更改名字时发生错误 1026：写文件错误 1032：记录不存在 1036：数据表是只读的，不能对它进行修改 1037：系统内存不足，请重启数据库或重启服务器 1038：用于排序的内存不足，请增大排序缓冲区 1040：已到达数据库的最大连接数，请加大数据库可用连接数 1041：系统内存不足 1042：无效的主机名 1043：无效连接 1044：当前用户没有访问数据库的权限 1045：不能连接数据库，用户名或密码错误 1048：字段不能为空 1049：数据库不存在 1050：数据表已存在 1051：数据表不存在 1054：字段不存在 1065：无效的SQL语句，SQL语句为空 1081：不能建立Socket连接 1114：数据表已满，不能容纳任何记录 1116：打开的数据表太多 1129：数据库出现异常，请重启数据库 1130：连接数据库失败，没有连接数据库的权限 1133：数据库用户不存在 1141：当前用户无权访问数据库 1142：当前用户无权访问数据表 1143：当前用户无权访问数据表中的字段 1146：数据表不存在 1147：未定义用户对数据表的访问权限 1149：SQL语句语法错误 1158：网络错误，出现读错误，请检查网络连接状况 1159：网络错误，读超时，请检查网络连接状况 1160：网络错误，出现写错误，请检查网络连接状况 1161：网络错误，写超时，请检查网络连接状况 1062：字段值重复，入库失败 1169：字段值重复，更新记录失败 1177：打开数据表失败 1180：提交事务失败 1181：回滚事务失败 1203：当前用户和数据库建立的连接已到达数据库的最大连接数，请增大可用的数据库连接数或重启数据库 1205：加锁超时 1211：当前用户没有创建用户的权限 1216：外键约束检查失败，更新子表记录失败 1217：外键约束检查失败，删除或修改主表记录失败 1226：当前用户使用的资源已超过所允许的资源，请重启数据库或重启服务器 1227：权限不足，您无权进行此操作 1235：MySQL版本过低，不具有本功能
cat /var/lib/mysql/auto.cnf [auto] server-uuid=cc2cd450-009d-11eb-a16f-000c297416d8
安装过程出现yum无法缓存mysql问题 1. 时区 ntpdate 2. gpgcheck = 0 3. ssl校验不通过 vim /etc/yum.conf sslverify=false gpgcheck=0
#socket = /var/lib/mysql/mysql.sock（原内容，为了更稳妥用“#”注释此行）
socket = /home/data/mysql/mysql.sock （加上此行）
6、修改MySQL启动脚本/etc/rc.d/init.d/mysql
最后，需要修改MySQL启动脚本/etc/rc.d/init.d/mysql，把其中datadir=/var/lib/mysql一行中，等号右边的路径改成你现在的实际存放路径：home/data/mysql。
[root@test1 etc]# vi /etc/rc.d/init.d/mysql
#datadir=/var/lib/mysql （注释此行）
datadir=/home/data/mysql （加上此行）
7、重新启动MySQL服务
/etc/rc.d/init.d/mysql start
或用reboot命令重启Linux
以下是mysql数据目录修改出现
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)错误的解决方案：
本例是将Mysql的数据目录移动到了/home/data/下，即mysql.sock所在的真实目录为，/home/data/mysql/mysql.sock
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)
mysql有tcp连接和socket连接方式，而上面这种错误一般是因为mysql是使用rpm方式安装的，它会自动寻找 /var/lib/mysql/mysql.sock 这个文件，是一种socket连接方式。
常见解决办法如下：
1、
创建/修改文件 /etc/my.cnf文件，在[mysqld]上面添加
[client]
#password=your_password
port=3306
socket=/home/data/mysql/mysql.sock
#在这里写上你的mysql.sock的正确位置。
2、
指定IP地址，使用tcp方式连接mysql，而不使用本地socket方式
#mysql -h127.0.0.1 -uuser -ppassword
3、
为 mysql.sock 加个连接，比如说实际的mysql.sock在 /home/data/mysql/ 下，则
#ln -s /home/data/mysql/mysql.sock /var/lib/mysql/mysql.sock即可
第2种方法:
1.# mv /var/lib/mysql/[DB_name] [New Location] #移动数据库文件
2.# ln -s [New Location] /var/lib/mysql/[DB_name] #建立软链接
### 引擎
存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据
的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）
在Oracle 和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySql数据库提供了多种存储引擎。
用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。
选择存储引擎
-
查看存储引擎
SHOW ENGINES\G
show engines\G
SHOW VARIABLES LIKE ‘%storage_engine%’; -
选择存储引擎
方法1.
mysql> create table innodb1(
-> id int
-> )engine=innodb;
mysql> show create table innodb1;
方法2.
/etc/my.cnf
[mysqld]
default-storage-engine=INNODB
MySQL常用的存储引擎
MyISAM存储引擎
由于该存储引擎不支持事务、也不支持外键，所以访问速度较快。因此当对事务完整性没有要求并以访问为主的应用适合使用该存储引擎。
InnoDB存储引擎
由于该存储引擎在事务上具有优势，即支持具有提交、回滚及崩溃恢复能力等事务特性，所以比MyISAM存储引擎占用更多的磁盘空间。
因此当需要频繁的更新、删除操作，同时还对事务的完整性要求较高，需要实现并发控制，建议选择。
MEMORY
MEMORY存储引擎存储数据的位置是内存，因此访问速度最快，但是安全上没有保障。适合于需要快速的访问或临时表。
BLACKHOLE
黑洞存储引擎，可以应用于主备复制中的分发主库。
### 字符集设置
字符集设置
临时：
mysql> create database db1 CHARACTER SET = utf8;
mysql> create table t1(id int(10)) CHARACTER SET = utf8;
永久：
5.1版本设置：
#vim /etc/my.cnf
[mysqld]
default-character-set = utf8
character_set_server = utf8
[mysql]
default-character-set = utf8
5.5/5.6/5.7版本设置：
[mysqld]
character_set_server = utf8
### 慢查询配置
5.1版本
#vim /etc/my.cnf
[mysqld]
log-slow-queries=/var/lib/mysql/sql_row.log
long_query_time=3
查看是否设置成功： mysql> show variables like '%query%';
5.5版本
[mysqld]
slow-query-log=on
slow_query_log_file=/var/lib/mysql/slowquery.log
long_query_time=1
#log-queries-not-using-indexes=on //列出没有使用索引的查询语句
5.7版本
[mysqld]
slow_query_log=1
slow_query_log_file=/var/log/mysql-slow/slow.log
long_query_time=3
# mkdir /var/log/mysql-slow/ # chown mysql.mysql /var/log/mysql-slow/ # systemctl restart mysqld
查看慢查询日志
测试:BENCHMARK(count,expr)
mysql> SELECT BENCHMARK(500000000,2*3);
### client连接mysql慢
客户端连接MySQL数据库速度慢的问题
修改my.cnf配置，关闭DNS的反向解析参数
[mysqld]
skip-name-resolve
## mysql错误中文参照列表
1005：创建表失败
1006：创建数据库失败
1007：数据库已存在，创建数据库失败
1008：数据库不存在，删除数据库失败
1009：不能删除数据库文件导致删除数据库失败
1010：不能删除数据目录导致删除数据库失败
1011：删除数据库文件失败
1012：不能读取系统表中的记录
1020：记录已被其他用户修改
1021：硬盘剩余空间不足，请加大硬盘可用空间
1022：关键字重复，更改记录失败
1023：关闭时发生错误
1024：读文件错误
1025：更改名字时发生错误
1026：写文件错误
1032：记录不存在
1036：数据表是只读的，不能对它进行修改
1037：系统内存不足，请重启数据库或重启服务器
1038：用于排序的内存不足，请增大排序缓冲区
1040：已到达数据库的最大连接数，请加大数据库可用连接数
1041：系统内存不足
1042：无效的主机名
1043：无效连接
1044：当前用户没有访问数据库的权限
1045：不能连接数据库，用户名或密码错误
1048：字段不能为空
1049：数据库不存在
1050：数据表已存在
1051：数据表不存在
1054：字段不存在
1065：无效的SQL语句，SQL语句为空
1081：不能建立Socket连接
1114：数据表已满，不能容纳任何记录
1116：打开的数据表太多
1129：数据库出现异常，请重启数据库
1130：连接数据库失败，没有连接数据库的权限
1133：数据库用户不存在
1141：当前用户无权访问数据库
1142：当前用户无权访问数据表
1143：当前用户无权访问数据表中的字段
1146：数据表不存在
1147：未定义用户对数据表的访问权限
1149：SQL语句语法错误
1158：网络错误，出现读错误，请检查网络连接状况
1159：网络错误，读超时，请检查网络连接状况
1160：网络错误，出现写错误，请检查网络连接状况
1161：网络错误，写超时，请检查网络连接状况
1062：字段值重复，入库失败
1169：字段值重复，更新记录失败
1177：打开数据表失败
1180：提交事务失败
1181：回滚事务失败
1203：当前用户和数据库建立的连接已到达数据库的最大连接数，请增大可用的数据库连接数或重启数据库
1205：加锁超时
1211：当前用户没有创建用户的权限
1216：外键约束检查失败，更新子表记录失败
1217：外键约束检查失败，删除或修改主表记录失败
1226：当前用户使用的资源已超过所允许的资源，请重启数据库或重启服务器
1227：权限不足，您无权进行此操作
1235：MySQL版本过低，不具有本功能
cat /var/lib/mysql/auto.cnf
[auto]
server-uuid=cc2cd450-009d-11eb-a16f-000c297416d8
安装过程出现yum无法缓存mysql问题
- 时区 ntpdate
- gpgcheck = 0
- ssl校验不通过
vim /etc/yum.conf
sslverify=false
gpgcheck=0
这篇关于MySQL数据库目录的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
- 2024-11-20部署MySQL集群入门：新手必读指南
- 2024-11-20部署MySQL集群教程：初学者指南
- 2024-11-20部署MySQL集群项目实战：新手教程
- 2024-11-20部署MySQL集群资料：新手入门教程
- 2024-11-20MySQL集群部署教程：入门级详解
- 2024-11-20MySQL集群教程：入门与实践指南
- 2024-11-20部署MySQL集群教程：新手入门指南
- 2024-11-20MySQL读写分离教程：轻松入门
- 2024-11-20部署MySQL集群入门：一步一步搭建你的数据库集群
- 2024-11-19部署MySQL集群学习：入门教程
