Mysql

原创

小慕萌仔 2019-03-19 16:08:12 博主文章分类：云计算文档 ©著作权

©著作权归作者所有：来自51CTO博客作者小慕萌仔的原创作品，请联系作者获取转载授权，否则将追究法律责任

Mysql MYSQL(Centos 6、5……系)/Mariadb(Centos 7系)要点: 1.mysql-server服务器的安装部署。[采用SQL语言管理的关系型数据库] 2.常见的mysql数据库、表的管理(增、删、改、查)语句。 3.mysql数据库、表的备份方法。 4.mysql的几种日志文件。 5.mysql的集群部署：主从复制、读写分离、高可用 6.部署一个LAMP环境的Web服务器。以论坛、电商网站作为网站实例。关系型数据库(SQL):采用SQL语言管理，采用"数据表格"存储数据。（前期必须将表格式设计合理，后期难以修改）通常用于企业的网站、OA信息系统。常见的关系型数据库系统有：mysql-server(SUN公司-->Oracle公司)、SQL Server(微软)、Oracle数据库 (商用，银行、金融公司会用) 非关系型数据库(No SQL):不采用SQL语言管理，采用"键值对"来存储数据。(不采用表存储数据，后期修改很灵活)通常用户大数据分析处理。常见的非关系型数据库系统有：redis、mongodb…… mysql的学习技巧：理解mysql的数据库、表的时候可以跟excel进行类比，可以让我们更容易理解和学懂mysql的操作。 mariadb-server服务器、mariadb客户端的安装部署： 1.查软件是否已安装。 rpm -q mariadb-server mariadb 2.安装软件 yum install -y mariadb-server mariadb 3.启动服务器端的服务，允许开机自动启动。 systemctl restart mariadb systemctl enable mariad 4.内测：用mariadb的root账号登录到本地mariadb-server服务器上。(以下常用SQL语句必须很熟练) mysql 等同于 mysql -uroot 在mysql服务器本地以root身份登录 exit 退出登录。 mysql -uroot -p -h 127.0.0.1 提示输入密码时，直接回车即可，因为默认的root初始密码为空 ? 或 \h 或 help 显示mysql的命令帮助 status 或 \s 显示mysql的工作状态 help show 显示show命令的帮助 show databases; 显示库文件列表 use mysql; 打开mysql这个数据库 show tables; 显示当前数据库中的所有表的名称 select user,host,password from mysql.user; 在mysql数据库文件中user表中查看user、hosts、 password字段的内容。 grant all on . to jin@'%' identified by 'jin' with grant option; 创建一个拥有全部权限的jin 用户账号 2/125 grant all on . to jin@'localhost' identified by 'jin' with grant option; 创建允许本地登录的jin账号 select user,host,password from mysql.user; 在mysql数据库文件中user表中查看user、hosts、 password字段的内容。 exit 或 \q 退出mysql登录 mysql -ujin -pjin 用jin用户登录到本地的mysql数据库服务器注意：切记，mariadb数据库服务的账号是独立的，根Linux系统的账号没有任何关系。用excel来类比mysql数据库的概念： 1.库文件(数据库database/db)：可以理解成一个excel文件。一个库(database)文件中可以有0~n张表 (table)。mysql的库文件默认保存在/var/lib/mysql目录中，库文件在linux系统中是以"目录的形式存在的", 库目录中存放的是此数据库的表。 2.表(table)：可以理解成excel文件中的一张表。一张表中可以有1~n个字段。表在linux系统中是文件名的形式保存在库的目录中。 3.字段(field)：可以理解成excel表中的表头的列标题。一个字段可以包含0~n行记录(record)。字段和记录是以正文的形式保存在表文件中的。 mysql应用场景： 1.用来存储网站的重要数据信息，如电商、论坛、博客、影视网站(如优酷、爱奇艺)等网站的用户账号id和密码信息、商品信息、影片信息等。注意：数据库中是不存储图片、视频文件的，只存文本信息，数据库中通常只存音视频、图片文件的路径文本信息(即元数据)。 2.用来存储一些手机APP(如淘宝app、京东app)的用户账号、商品、音视频的数据信息。 mysql和应用间的工作流程架构： 3/125 情况说明：对于访问量(并发量)不大的web应用程序，apache和mysql服务器软件可以用一台物理机或虚拟机来实现，访问量大的web应用程序，就得把apache和mysql业务分开。 Mysql语句默写 SQL语句(2019-03-13-AM-默写)：库的操作： 1.查库：show databases; 2.建库：create database 库名 [选项]; 3.开库：use 库名; 4.删库：drop database 库名; 4/125 表的操作： 1.查表(显示库中的表文件)：show tables; 2.建表：create table 表名(字段1 类型约束,字段2 类型约束,...); 3.查表结构：desc 表名; 4.表中新增一列(在最后)：alter table 表名 add 字段名类型约束; 5.删除表中的sex这个字段(列)：alter table 表名 drop sex; 6.删除表：drop table 表名; 记录操作： 1.查表中的所有记录：select * from 表名; 2.查test库中表tb1的id<=3的所有记录：select * from test.tb1 where id<=3; 3.在tb1表中插入一行记录：insert into tb1 values(值1,值2,...); 4.更新tb1表中name为jack的记录，将他的name值更新成jacker： update tb1 set name='jacker' where name='jack'; 5.删除tb1表中id<=3的记录：delete from tb1 where id<=3; 授权管理(用户安全管理)： 1.查mysql库中user表的user、host、password字段的数据记录：(查mysql系统中的用户账号) select user,host,password from mysql.user; 2.在mysql中创建一个跟root账号相同权限的新账号admin： grant all on . to admin@'%' identified by '密码' with grant option; 3.查看root账号的授权信息： show grants for root@'localhost'; 4.回收admin账号的insert、update、delete权限： revoke insert,update,delete on . from admin@'主机名或IP'; 5.删除admin账号： drop user admin@'主机名或IP'; Mysql的库、表、记录的增删改库(database)的增、删、改、查命令操作查：show databases; 增：create database 库名; 删：drop database 库名; 表(table)的增、删、改、查命令操作查：show tables; 增：create able 表名(字段名1 数据类型) 查表结构：desc 表名; 删：drop table 表名; 修改表：alter table 表名 add 字段名数据类型; alter table 表名 modify 字段名新数据类型 alter table 表名 change 旧字段名新字段名新数据类型 alter table 表名 drop 字段名记录的增、删、改、查命令操作查：select * from 表名 [where 条件] 增：insert into 表名[(字段1,字段2,.)] values(值1,值2,.),(值1,值2,.); 5/125 更新：update [库名.]表名 set 字段名='新值'[where 件]; 删：delete from [库名.]表名 [where 条件]; Mysql的root密码遗忘后的重设方法 1、修改/etc/my.cnf主配置文件。 vim /etc/my.cnf [mysqld] skip-grant-table 跳过权限表验证(添加此行) 2、重启mariadb服务，在mysql-server服务器本地免密码登录,做更新root密码的操作 3、修改/etc/my.cnf主配置文件。 vim /etc/my.cnf [mysqld] #skip-grant-table 注释或删除此行 4、重启mariadb服务 5、在mysql-server服务器本地使用root账号和新密码登录，登录成功，就说明密码重设成功 Mysql主从复制工作原理和配置步骤主从复制工作原理：在主库上把数据更改记录到二进制日志中，备库监测并读主库主机上的二进制日志文件新增的内容，且将新内容写入到备库主机自己的中继日志文件中，读取备库主机上中继日志文件中心的 SQL语句，并且自动执行这些SQL语句，最终在备库主机上创建了这个库。配置步骤： 1.在master主机上的/etc/my.cnf主配置文件中开启binlog二进制日志文件功能，并且给主机设置server-id 唯一标识编号，重启mariadb服务。 2.在master主机上创建用于主从复制的用户账号，并查看master状态中的binlog日志文件的position位置数值。 3.在slave主机上的/etc/my.cnf主配置文件中设置server-id唯一标识编号，重启mariadb服务。 4.在slave主机上用help change master查命令帮助，并用change master命令告诉slave主机他的master 主人的正确信息。 5.在slave主机上用start slave启动mariadb的随从服务，并用show slave status查看AB主从复制的数据同步状态，要确认两个线程的yes状态。数据库的逻辑备份和物理备份逻辑备份就是将数据中的表备份成create table、insert into等表和数据记录的SQL语句。物理备份就是就是将数据库、表的源文件(/var/lib/mysql/库名目录)复制或tar打包压缩一份到别的目录中。物理备份的模式：冷备份：首先，停止数据库的服务，然后用cp、tar命令对数据库文件进行备份。热备份：直接在数据库服务运行的状态下做备份，需要用mysql的第三方备份和恢复工具进行操作。单库全表备份： mysqldump -uroot -p'密码' 库名 > 备份文件名.sql 单库全表恢复： mysql -uroot -p'密码'-e 'create database 库名;show databases;' mysql -uroot -p456 库名 < 备份文件名.sql mysql -uroot -p'密码' -e 'use 库名;show tables;' 单库单表备份： mysqldump -uroot -p'密码' 库名表名 > 备份文件名.sql 单库单表恢复： 6/125 mysql -uroot -p'密码' -e 'create database 库名;show mysql -uroot -p'密码' 库名 < 备份文件名.sql mysql -uroot -p'密码' -e 'use 库名;show tables;select * from 表名' mysql库表基本操作练习：在mysql数据库中完成以下操作。 1.在mysql服务器本地登录进行数据库的管理。 2.查看服务器上的库文件名列表，创建一个名称为mydb的库文件。打开mydb这个库文件。 3.查看mydb库中的表，创建一个名称为tb1的表。表中有id、name、sex、qq、addr字段，字段的数据类型全部为char字符型，宽度为25个字符。 4.查看tb1表中的全部字段的全部记录，插入2条记录到表中，在查一次表的全部记录。 5.附加操作，退出数据库的登录。查看mydb数据库文件中的表。操作步骤参考： 1.在mysql服务器本地登录进行数据库的管理。 mysql 或 mysql -uroot 2.查看服务器上的库文件名列表，创建一个名称为mydb的库文件。打开mydb这个库文件。 show databases; create database mydb; use mydb; 3.查看mydb库中的表，创建一个名称为tb1的表。表中有id、name、sex、qq、addr字段，id字段的数据类型为int整型，其他字段的数据类型全部为char字符型，宽度为25个字符。 show tables; create table tb1( id int, name char(25), sex char(25), qq char(25), addr char(25) ); 4.显示tb1的表结构，查看tb1表中的全部字段的全部记录，插入3条记录到表中，在查一次表的全部记录。 desc tb1; select * from tb1; insert into tb1 values(1,'lucy','fmale','123456','qf'); 7/125 insert into tb1 values (2,'tom','male','1314520','qf'), (3,'jack','male','1213520','qf'); select * from tb1; 5.附加操作，退出数据库的登录。查看mydb数据库文件中的表。 exit ls -R /var/lib/mysql/mydb cat /var/lib/mysql/mydb/db.opt mydb库文件的选项(option)配置文件练习:在mysql服务器本地登录数据库系统，显示数据库文件列表，打开mydb库文件，创建一个名称为tb2 的表，表中包含id、name这两个字段，显示tb2表结构，在tb2表中插入2条记录。记录的内容自己看着办。 mysql远程管理的方式方法一：（linux系统管理员采用）首先用ssh远程登录到linux或windows系统，然后从linux或windows系统本地用mysql客户端命令登录到 mysql-server数据库服务器。方法二：(DBA[database administrator]数据库管理员采用) 直接在linux或windows系统的主机上用mysql的客户端命令或mysql的第三方客户端软件(如Navicat)来直接远程登录到mysql-server数据库服务器。 8/125 创建远程管理数据库的账号： grant all on . to 用户名@'客户端主机名或IP' identified by '密码' with grant option; 说明：用help grant查看grant命令的帮助信息 grant命令语法格式： grant 权限1,权限2,权限n on 库名称.表名称 to 用户名@'客户端主机名或IP' identified by '密码' with 选项 option; 权限：all 表示有跟root相同的所有权限，权限还可以是select、update、create、delete、drop等操作权限。库名称.表名称表示此用户允许操作的库和表。.就表示允许此用户操作所有的库和表。客户端主机名或IP的写法： localhost 表示本地主机(即本机自己) % 表示任何IP的客户主机 192.168.11.% 表示192.168.11.0网段的所有客户端主机 172.16.%.% 表示172.16.0.0网段的所有客户端主机 IP地址的A、B、C类取值范围： A类 1.0.0.0~127.255.255.255 B类 128.0.0.0~191.255.255.255 C类 192.0.0.0~223.255.255.255 with 选项：常见选项如下 grant option 表示此用户拥有用grant命令创建新用户账号的权限 MAX_QUERIES_PER_HOUR 90 每小时最多请求90次 MAX_UPDATES_PER_HOUR count 每小时最多更新count次 MAX_CONNECTIONS_PER_HOUR count 每小时最多登陆连接count次(即并发量) MAX_USER_CONNECTIONS count 每小时最多用户连接count次 9/125 MySQL数据库操作 MySQL数据库基本操作

一、初识SQL语言 SQL（Structured Query Language 即结构化查询语言） SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统，SQL语言由IBM开发。SQL语言分为3种类型： DDL语句数据库定义语言(Data Definition Language)：数据库、表、视图、索引、存储过程，例如 CREATE DROP ALTER DML语句数据库操纵语言(Data Manipulation Language)：插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT DCL语句数据库控制语言(Data Control Language)：例如控制用户的访问权限GRANT(授予权限 )、REVOKE(收回权限) 二、系统数据库(即mysql自带的库文件) information_schema：虚拟库，主要存储了系统中的一些数据库对象的信息，例如用户表信息、列信息、权限信息、字符信息等 performance_schema：主要存储数据库服务器的性能(performance)参数 mysql：授权库，主要存储系统用户的权限信息 test： MySQL数据库系统自动创建的测试数据库创建需要的业务主库(用户自己创建新库、新表)... 练习：给mysql-server的root用户分别设置本地、远程登录密码。 grant all on . to root@'localhost' identified by 'root'; grant all on . to root@'%' identified by 'root'; flush privileges; 刷新权限表 select user,host,password from mysql.user; 查询mysql库中user表的user、host、password这3列的数据记录信息。 exit 或 \q 再次登录测试： mysql 无密码登录，提示错误 mysql -uroot -proot 用root用户和密码登录，成功登录三、忘记MySQL密码

vim /etc/my.cnf 这是mariadb和mysql的主配置文件

[mysqld] skip-grant-table 此行为跳过权限表的验证，实现无密码访问数据库 #重启mariadb或mysqld数据库服务

systemctl restart mariadb 或 service mysqld restart

mysql 免密码登录

vim /etc/my.cnf

[mysqld] #skip-grant-table 注释或删掉此行

systemctl restart mariadb (centos 7版) 或 service mysqld restart (centos 6版)

Default options are read from the following files in the given order: /etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf 忘记MySQL密码忘记MySQL密码的解决

vim /etc/my.cnf 这是mariadb和mysql的主配置文件

[mysqld] skip-grant-table 此行为跳过权限表的验证，实现无密码访问数据库 #重启mariadb或mysqld数据库服务

systemctl restart mariadb 或 service mysqld restart

mysql 免密码登录

vim /etc/my.cnf

[mysqld] #skip-grant-table 注释或删掉此行

systemctl restart mariadb (centos 7版) 或 service mysqld restart (centos 6版)

再次登录测试： mysql 无密码登录，提示错误 mysql -uroot -p127 用root用户和密码登录，成功登录 13/125 MySQL表操作 MySQL表操作 DDL

表是数据库存储数据的基本单位，由若干个字段组成，主要用来存储数据记录。表的操作包括创建表、查看表、修改表和删除表。这些操作都是数据库管理中最基本，也是最重要的操作。本节内容包括: 创建表: create table 表名(字段1 数据类型,字段2 数据类型,...) 查看表结构: desc 表名 show create table 表名表完整性约束..... 主键：primary key 外键：foreign key …… 修改表 alter table 复制表 create table ... 删除表 drop table 五、复制表警告：要先准备一张有数据的源表，否则无法正常完成下面的实验。复制表结构和记录（key不会复制: 主键、外键和索引）命令格式：create table 新表名 select * from 源表名; 实例：下面是将service的表结构和数据复制到new_service表中。 mysql> create table new_service select * from service; 提示：复制表之后，请用show tables、desc和select命令查看表的相关信息。只复制表结构命令格式：create table 新表名 select * from 源表名 where 1=2； mysql> create table new1_service select * from service where 1=2; //由于1不等于2，条件为假，查不到任何记录复制表结构，包括Key 命令格式：create table 新表名 like 源表名; mysql> create table t4 like employees; 复制表记录：下面是只复制了某条记录命令格式：insert into 目标表名 select * from 源表名 where 字段名=值; mysql> insert into t4 select * from company.employees where id=1; 删除表中的所有记录：命令格式：delete from 表名; 复制表记录：下面是复制所有的条记录命令格式：insert into 目标表名 select * from 源表名; mysql> insert into t4 select * from company.employees; 六、删除表 DROP TABLE 表名; 14/125 七、删除库 drop database 库名; 一、创建表（表的基本操作）任务目标：创建school库，在school库中创建student1表，表中有id、name、sex、age这四个字段，最后在表中插入3条记录，查询表中的数据记录。表school.student1 字段1 字段2 字段3 字段4 id name sex age 1 tom male 23 记录1 2 jack male 21 记录2 3 alice female 19 记录3 语法： create table 表名( 字段名1 类型[(宽度) 约束条件], 字段名2 类型[(宽度) 约束条件], 字段名3 类型[(宽度) 约束条件] )[存储引擎字符集]; ==在同一张表中，字段名是不能相同 ==宽度和约束条件可选 ==字段名和类型是必须的 mysql> CREATE DATABASE school; //创建数据库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> create table test100(id int)engine=innodb; mysql> desc student1; //查看数据表student1的结构，结果如下 mysql> show tables; //查看表（当前所在库） 15/125 +------------------+ | Tables_in_school | +------------------+ | student1 | +------------------+ 1 row in set (0.00 sec)

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) 向表中插入内容语法： insert into 表名(字段1,字段2...) values(字段值列表...); 查看表结构顺序插入只向指定的字段插入值 16/125

表school.student2 id id int 姓名 name varchar(50) 出生年份 born_year year 生日 birthday date 上课时间 class_time time 注册时间 reg_time datetime mysql> create table student2( -> id int, -> name varchar(50), -> born_year year, 数据类型为年 -> birthday date, 数据类型为日期，即显示年、月、日 -> class_time time, 数据类型为时间，格式为时:分:秒 -> reg_time datetime 数据类型为日期+时间 -> ); mysql> desc student2; mysql> insert into student2 values(1,'tom',now(),now(),now(),now()); mysql> insert into student2 values(2,'jack',1982,19821120,123000,20140415162545); mysql> select * from student2; 说明：now()是mysql中内定的函数，功能是获取当前的系统时间。 mysql> alter table student2 add newf datetime; //在表中添加新字段newf mysql> desc student2; //显示student2的表结构(即表的属性) mysql> select * from student2; mysql> alter table student2 drop newf; //在表中删除字段newf 表school.student3 17/125 id int 姓名 name varchar(50) 性别 sex enum('male','female') 爱好 hobby set('music','book','game','disc') 说明： enum枚举型：相当于单选题，只能填其中一个选项。 set设置型(也称集合型)：相当于多选题，可以填其中的一个或多个选项。 mysql> create table student3( -> id int, -> name varchar(50), -> sex enum('male','female'), -> hobby set('music','book','game','disc') -> ); mysql> desc student3; mysql> show create table student3\G 查看student3表创建的过程 mysql> insert into student3 values (1,'tom','male','book,game'); mysql> insert into student3 values (2,'jack','male','film'); mysql> select * from student3; mysql> update student3 set hobby='music' where id=2; 更新数据更新前的数据记录更新后的数据记录

student3表的小结： 1、如果对字段的取值用set进行了范围设置，当插入数据时填写了范围以外的数据，将不被识别。例如上面红色的“film” 二、查看表结构 DESCRIBE查看表结构 DESCRIBE 表名; DESC 表名; SHOW CREATE TABLE查看表详细结构 SHOW CREATE TABLE 表名; 三、表完整性约束作用：用于保证数据的完整性和一致性

约束条件说明 PRIMARY KEY (PK) 标识此字段为此表的主键，可以唯一的标识记录，不可以为空 UNIQUE + NOT NULL FOREIGN KEY (FK) 标识此字段为此表的外键，实现子表与父表（父表主键/子表1外键/子表2 外键）之间的关联 18/125 NOT NULL 标识此字段不能为空,如网上注册账号时的必填项就要用此功能 UNIQUE KEY (UK) 唯一键,标识此字段的值是唯一的，可以为空，一个表中可以有多个 UNIQUE KEY AUTO_INCREMENT 标识此字段的值自动增长（整数类型，而且为主键）,通常用在“序号”字段 DEFAULT 为此字段设置默认值，用户不填写此信息是默认填写此值。 UNSIGNED 无符号，正数 ZEROFILL 使用0填充，如填写0000001这种记录是可以使用此约束说明：

是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值 sex enum('male','female') not null default 'male' age int unsigned NOT NULL default 20 必须为正值（无符号）不允许为空默认是20
是否是key 主键 primary key 外键 forengn key 索引 (index,unique...) ============================================================== ＝＝＝DEFAULT、NOT NULL default:指定默认值 not null:不允许为空。就类似于我们在网上注册账号时，有些内容必须要填写，空着就提交不了。表school.student4 19/125 mysql> insert into student4 values(1,'jack','m',20,'book'); Query OK, 1 row affected (0.00 sec) mysql> select * from student4; mysql> insert into student4(id,name) values(2,'robin'); 没定义的取默认值 Query OK, 1 row affected (0.00 sec) mysql> insert into student4 values(3,NULL,'m',40,'book'); ERROR 1048 (23000): Column 'name' cannot be null //错误提示：列‘name’不能为空＝＝＝设置唯一约束 UNIQUE 唯一约束：控制此列的值不允许重复，必须是唯一的一个值。表company.department1 mysql>create database company; mysql>CREATE TABLE company.department1 ( dept_id INT, dept_name VARCHAR(30) UNIQUE, comment VARCHAR(50) ); 20/125 表company.department2 CREATE TABLE company.department2 ( -> dept_id INT, -> dept_name VARCHAR(30), -> comment VARCHAR(50), -> CONSTRAINT uk_name UNIQUE(dept_name) -> ); 21/125 ===设置主键约束 PRIMARY KEY primary key字段的值是不允许重复，且不允许为空NULL（UNIQUE + NOT NULL）单列做主键多列做主键（复合主键）单列做主键表school.student6 方法一 mysql> use school; mysql> create table student6( id int primary key not null auto_increment, name varchar(50) not null, sex enum('male','female') not null default 'male', age int not null default 18 ); Query OK, 0 rows affected (0.00 sec) 说明：auto_increment 表示自动生成顺序编号表school.student7 方法二 mysql> create table student7( -> id int auto_increment not null, -> name varchar(50) not null, -> sex enum('male','female') not null default 'male', -> age int not null default 18, -> CONSTRAINT pk_id primary key(id) pk_id名称 22/125 -> ); Query OK, 0 rows affected (0.00 sec) MySQL 错误：there can be only one auto column and it must bedefined as a key mysql> insert into student6 values (1,'alice','female',22); mysql> insert into student6(name,sex,age) values -> ('jack','male',19), -> ('tom','male',23); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from student6; +----+-------+------+-----+ | id | name | sex | age | +----+-------+------+-----+ | 1 | alice | female | 22 | | 2 | jack | male | 19 | | 3 | tom | male | 23 | +----+-------+------+-----+ 3 rows in set (0.00 sec) 23/125 复合主键应用场合：需要用两列的值来标识数据的唯一性。表school.service host_ip 存储主机IP service_name 服务名 port 服务对应的端口 allow(allow,deny) 服务是否允许访问主键： host_ip + port = primary key mysql> create table service( host_ip varchar(15) not null, service_name varchar(10) not null, port varchar(5) not null, allow enum('Y','N') default 'N', primary key(host_ip,port) ); Query OK, 0 rows affected (0.00 sec) 24/125 mysql> insert into service values ('192.168.2.168','ftp','21','Y'); mysql> insert into service values ('192.168.2.168','httpd','80','Y'); mysql>select * from service; ===设置字段值增 AUTO_INCREMENT 表company.department3 CREATE TABLE department3 ( dept_id INT PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(30), comment VARCHAR(50) ); ===设置外键约束 FOREIGN KEY 父表company.employees(员工) mysql>create database company; mysql>use company; mysql> create table employees( -> name varchar(50) not null, -> mail varchar(20), 25/125 -> primary key(name) 声明name为主键（即主要关键字） -> )engine=innodb;

子表company.payroll(工资) mysql> create table payroll( -> id int not null auto_increment, -> pname varchar(50) not null, -> payroll float(10,2) not null, 数据类型为float浮点型(即小数) -> primary key(id), 声明id为主键 -> foreign key(pname) 声明payroll表中的pname字段为外键 -> references employees(name) 设置外键pname的数据来源为employess表中的name -> on update cascade 声明两张表中的数据会同步更新 -> on delete cascade 声明两张表中的数据会同步删除 -> )engine=innodb; 子表name外键，关联父表（employees 主键name），同步更新，同步删除 cascade 删除包含与已删除键值有参照关系的所有记录

#首先，在父表employees中插入tom、jack的记录。 mysql> insert into employees values('tom','163.com'), ('jack','126.net');

#然后，在子表payroll中插入tom、jack的记录。 mysql> insert into payroll(pname,payroll) values('lucy',16.8); 会提示错误，因为做了外键关联，并且父表中没有lucy的信息 mysql> insert into payroll(pname,payroll) values ('tom',15.6), ('jack',100.5);

26/125 mysql> update employees set name='tomaaa' where name='tom'; mysql> select * from emplyees; mysql> select * from payroll; mysql> delete from employees where name='jack'; 结论： 0、外键的作用就是实现在子表中引用父表中某列的值，这样子表中外键的值是依赖于父表的。 1、父表中的主键才能作为子表中的外键。 2、当父表中某个员工的记录修改时，子表也会同步修改 3、当父表中删除某个员工的记录，子表也会同步删除四、修改表ALTER TABLE 语法：

修改表名 ALTER TABLE 表名 RENAME 新表名; 小结：相当于excel中双击表的名字，然后输入新的表名。
增加字段（增加字段后请用"desc 表名"查看表结构） ALTER TABLE 表名 ADD 字段名数据类型 [完整性约束条件…], ADD 字段名数据类型 [完整性约束条件…]; ALTER TABLE 表名 ADD 字段名数据类型 [完整性约束条件…] FIRST; ALTER TABLE 表名 ADD 字段名数据类型 [完整性约束条件…] AFTER 字段名; 小结：增加字段就相当于excel表格中插入一列格子。
删除字段 ALTER TABLE 表名 DROP 字段名; 小结：相当于excel中删除一列格子。
修改字段 ALTER TABLE 表名 MODIFY 字段名数据类型 [完整性约束条件…]; ALTER TABLE 表名 CHANGE 旧字段名新字段名旧数据类型 [完整性约束条件…]; ALTER TABLE 表名 CHANGE 旧字段名新字段名新数据类型 [完整性约束条件…]; 小结：相当于Excel表格中修改表的列标题。示例：
修改存储引擎 mysql> alter table service -> engine=innodb; //engine=myisam|memore|.... 27/125 show create table school.service;
添加字段 mysql> create table student10 (id int); mysql> alter table student10 -> add name varchar(20) not null, -> add age int not null default 22;

mysql> alter table student10 -> add stu_num int not null after name; //添加name字段之后 mysql> alter table student10 -> add sex enum('male','female') default 'male' first; //添加到最前面 3. 删除字段 mysql> alter table student10 -> drop sex; mysql> alter table service -> drop mac; 4. 修改字段类型modify mysql> alter table student10 -> modify age tinyint;

mysql> alter table student10 -> modify id int not null primary key ; //修改字段类型、约束、主键 5. 增加约束（针对已有的主键增加auto_increment） mysql> alter table student10 modify id int not null primary key auto_increment; //错误，该字段已经是primary key ERROR 1068 (42000): Multiple primary key defined mysql> alter table student10 modify id int not null auto_increment; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 6. 增加复合主键:注意，此功能用于表中没有主键的情况下。 mysql> alter table service -> add primary key(host_ip,port); 7. 增加主键 mysql> alter table student1 -> add primary key(id); 8. 增加主键和自动增长 mysql> alter table student1 -> modify id int not null primary key auto_increment; 9. 删除主键 a. 删除自增约束（修改字段的类型即可） mysql> alter table student10 modify id int not null; 28/125 b. 删除主键 mysql> alter table student10 drop primary key; 主键处理的小结：如果表中已设置过主键，并且这个主键是“自动增长”的类型，此时想设置复合主键的话，必须先对此主键取消自动增长，然后删除此主键，最后再设置复合主键才行，否则会失败。

MySQL单表查询 MySQL单表查询 SELECT DML

简单查询通过条件查询查询排序限制查询记录数使用集合函数查询分组查询使用正则表达式查询表cmp.emp5 雇员编号 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 database cmp; mysql> use cmp; mysql> CREATE TABLE cmp.emp5( id int primary key AUTO_INCREMENT not null, name varchar(30) not null, sex enum('male','female') default 'male' not null, 29/125 hire_date date not null, post varchar(50) not null, job_description varchar(100), salary double(15,2) not null, office int, dep_id int ); mysql> insert into emp5 (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); 一、简单查询简单查询 SELECT * FROM emp5; 如果表的列过多，将此处的;分号换成\G分组显示 SELECT name, salary, dep_id FROM emp5; 说明：select 字段1,字段2,字段n from 表名; 避免重复DISTINCT 30/125 SELECT DISTINCT post FROM emp5; 通过四则运算查询 SELECT name, salary14 FROM emp5; SELECT name, salary14 AS Annual_salary FROM emp5; 取名年薪 SELECT name, salary14 Annual_salary FROM emp5; 定义显示格式 CONCAT() 函数用于连接字符串。将多列的值输出成一列。 SELECT CONCAT(name, ' annual salary: ', salary14) AS Annual_salary FROM emp5; 说明：中间“蓝色的字”是单独的一个字符串。并不是表中的列标题（即字段）。

二、单条件查询：相当于excel中的筛选功能，即查询所需要的数据条件中用and、between and、is null、not等关键字定义条件范围单条件查询 SELECT name,post FROM emp5 WHERE post='hr'; 31/125 多条件查询：and是与（同时满足这两个条件），or或（满足其中一个条件的记录）,not非(不满足此条件的记录) SELECT name,post,salary FROM emp5 WHERE post='hr' AND salary>4000; SELECT name,post,salary FROM emp5 WHERE post='hr' or post='sale'; SELECT name,post,salary FROM emp5 WHERE not post='hr';

32/125 关键字BETWEEN AND：bettween 起始值 and 截止值查询salary在5000到15000的记录 SELECT name,salary FROM emp5 WHERE salary BETWEEN 5000 AND 15000; 查询salary不在5000到15000之间的记录 SELECT name,salary FROM emp5 WHERE salary NOT BETWEEN 5000 AND 15000; 关键字IS NULL 查询job_description字段的值为空的记录 SELECT name,job_description FROM emp5 WHERE job_description IS NULL; SELECT name,job_description FROM emp5 WHERE job_description IS NOT NULL; 不为空的记录 SELECT name,job_description FROM emp5 WHERE job_description=''; ''并非是NULL，查的结果跟NULL不同 33/125 关键字IN集合查询查询salary值为4000、5000、6000或9000的记录 SELECT name, salary FROM emp5 WHERE salary=4000 OR salary=5000 OR salary=6000 OR salary=9000 ; SELECT name, salary FROM emp5 WHERE salary IN (4000,5000,6000,9000) ; 等同于上一行命令的功能（首选） SELECT name, salary FROM emp5 WHERE salary NOT IN (4000,5000,6000,9000) ; 不在这个范围内的记录 34/125 关键字LIKE模糊查询通配符’%’：表示任意一串字符，类似于shell脚本中的星号。 SELECT * FROM emp5 WHERE name LIKE 'al%'; 通配通配符’’：表示任意一个字符，类似于Shell脚本中的?问号。 SELECT FROM emp5 WHERE name LIKE 'al__'; 占位符（此处有三个占位符）三、查询排序按单列排序 select from 表名 order by 排序的字段排序方式(asc升序或desc降序); SELECT * FROM emp5 ORDER BY id desc; SELECT * FROM emp5 ORDER BY salary; 默认是按升序 SELECT name, salary FROM emp5 ORDER BY salary ASC; 升 SELECT name, salary FROM emp5 ORDER BY salary DESC; 降按多列排序：只有第1列数据中有相同的值时，第2列的排序才会有效果。 SELECT * FROM emp5 ORDER BY hire_date DESC,salary ASC; 参考表格如下： id type price 01 book 10 02 book 20 03 water 5 04 water 3 05 book 15 四、限制查询的记录数示例： SELECT * FROM emp5 LIMIT 5; SELECT * FROM emp5 ORDER BY salary DESC LIMIT 5; //默认初始位置为0 SELECT * FROM emp5 ORDER BY salary DESC LIMIT 0,5; SELECT * FROM emp5 LIMIT 3,5; //从第4条开始，共显示5条 35/125 五、使用集合函数查询示例： SELECT COUNT() FROM emp5; 总记录数（即共有多少行记录） SELECT COUNT() FROM emp5 WHERE dep_id=101;指定条件的数量(类似于excel中的countif函数) SELECT MAX(salary) FROM emp5; 查询指定字段的最大值 SELECT MIN(salary) FROM emp5;最小 SELECT AVG(salary) FROM emp5;平均(类似于excel中的average函数) SELECT SUM(salary) FROM emp5;求和 SELECT SUM(salary) FROM emp5 WHERE dep_id=101;条件求和(类似于excel中的sumif) mysql> select from emp5 where salary = (SELECT MAX(salary) FROM emp5); 显示salary最大值的记录测试一下：select max(salary) from emp5; mysql> select * from emp5 where salary = (SELECT MAX(salary) FROM emp5); 显示salary最小值的记录六、分组查询 GROUP BY关键字和GROUP_CONCAT()函数一起使用命令格式：select 字段1,字段2 from 表名 group by 分组字段名; 查出表中每个部门的人员名单。 SELECT dep_id,GROUP_CONCAT(name) FROM emp5 GROUP BY dep_id; 小结：由于表中的jack、tom、robin、alice的dep_id号都是100，所以显示出这样的分组结果。上题中的源表内容如下所示 dep_id name 100 jack 100 tom 36/125 100 robin 100 alice 101 tianyun 101 harry 102 emma 102 christine SELECT dep_id,GROUP_CONCAT(name) as emp_members FROM emp5 GROUP BY dep_id; GROUP BY与集合函数一起使用下面是统计每个部门的人数 SELECT dep_id,COUNT(dep_id) FROM emp5 GROUP BY dep_id; 显示表中的所有数据，跟上图进行比较七、使用REGEXP正则表达式查询正则表达式：可以简单理解成高级的匹配方法。 SELECT * FROM emp5 WHERE name REGEXP '^ali'; 以ali开头的信息,类似于al% SELECT * FROM emp5 WHERE name REGEXP 'yun$'; 以yun结尾的信息,类似于%yun SELECT * FROM emp5 WHERE name REGEXP 'm{2}'; 字符m出现2次的信息,类似于%mm% 37/125 小结：对字符串匹配的方式 WHERE name = 'tom'; WHERE name LIKE 'to%'; WHERE name REGEXP 'yun$';

Mysql多表查询 MySQL多表查询

多表连接查询复合条件连接查询子查询一、准备两张表表cmp.emp6 mysql> show databases; mysql> create database cmp; mysql> use cmp; mysql> create table emp6( emp_id int auto_increment primary key not null, emp_name varchar (50), age int, dept_id int); 38/125 insert into emp6(emp_name,age,dept_id) values ('tianyun',19,200), ('tom',26,201), ('alice',24,202), ('robin',40,200), ('natasha',28,204); 39/125 表cmp.dept6 mysql> create table dept6(dept_id int,dept_name varchar(100)); 40/125 insert into dept6 values (200, 'hr'), (201,'it'), (202,'sale'), (203,'fd'); 二、多表的连接查询交叉连接：生成笛卡尔积，它不使用任何匹配条件(很少用) 内连接：只连接匹配的行，需要用where指定条件外连接之左连接：会显示左边表内所有的值，不论在右边表内匹不匹配外连接之右连接：会显示右边表内所有的值，不论在左边表内匹不匹配全外连接：包含左、右两个表的全部行 =================交叉连接(很少用)======================= 简单用法：select * from 表1名,表2名; mysql> select emp6.emp_name,emp6.age,emp6.dept_id,dept6.dept_name from emp6,dept6; 命令格式：select 表1名.字段1,表1名.字段n,表2名.字段1,表2名.字段n from 表1名,表2名; 41/125 交叉连接的原理图 =================内连接（等值连接）======================= 只找出有部门的员工 (部门表中没有natasha所在的部门) mysql> select emp6.emp_id,emp6.emp_name,emp6.age,dept6.dept_name from emp6,dept6 where emp6.dept_id = dept6.dept_id; 命令格式：select 表1名.字段1,表1名.字段n,表2名.字段1,表2名.字段n from 表1名,表2名 where 条件; 注意：select 命令右侧的表1名和表2名可以省略。 42/125 内连接的工作原理图（等值连接） mysql> select emp_id,emp_name,age,dept_name from emp6,dept6 where emp6.dept_id = dept6.dept_id; 外连接语法： SELECT 字段列表 FROM 表1 LEFT|RIGHT JOIN 表2 ON 表1.字段 = 表2.字段; 43/125 左连接：select 字段列表 from 主表 left join 副表 on 条件; 右连接：select 字段列表 from 副表 right join 主表 on 条件; =================外连接(左连接 left join)======================= mysql> select emp_id,emp_name,dept_name from emp6 left join dept6 on emp6.dept_id = dept6.dept_id; 找出所有员工及所属的部门，包括没有部门的员工左连接的工作原理图 =================外连接(右连接right join)======================= mysql> select emp_id,emp_name,dept_name from emp6 right join dept6 on emp6.dept_id = dept6.dept_id; 以右边做参照找出所有部门包含的员工，包括空部门 =================全外连接(等同于交叉连接)======================= mysql> select * from emp6 full join dept6; 44/125 三、复合条件连接查询示例1：以内连接的方式查询emp6和dept6表，并且emp6表中的age字段值必须大于25 mysql> select emp_id, emp_name, age, dept_name from emp6,dept6 where emp6.dept_id = dept6.dept_id and age>25; 找出公司所有部门中年龄大于25岁的员工示例2：以内连接的方式查询emp6和dept6表，并且以age字段的降序方式显示 mysql> select emp_id, emp_name, age, dept_name from emp6,dept6 where emp6.dept_id = dept6.dept_id order by age desc; 四、子查询子查询是将一个查询语句嵌套在另一个查询语句中。 45/125 内层查询语句的查询结果，可以为外层查询语句提供查询条件。子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字还可以包含比较运算符：= 、 !=、> 、<等

带IN关键字的子查询 mysql> select * from emp6 where dept_id IN (select dept_id from dept6); 说明：查 emp6表中的所有数据，条件是dept_id必须是dept6表中存在的dept_id记录。带in子查询的工作原理图
带比较运算符的子查询 =、!=、>、>=、<、<=、<> mysql> select dept_id,dept_name from dept6 where dept_id IN (select DISTINCT dept_id from emp6 where age>=25); 查询员工年龄大于等于25岁的部门

带比较运算符的子查询的工作原理图 3. 带EXISTS关键字的子查询 EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。而是返回一个真假值。Ture或False 当返回Ture时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询 46/125 mysql> select * from emp6 WHERE EXISTS (SELECT dept_name from dept6 where dept_id=203); mysql> select * from emp6 WHERE EXISTS (SELECT dept_name from dept6 where dept_id=205); dept6表中不存在dept_id=205，False Empty set (0.00 sec)

MySQL数据类型 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 （用于实现单选功能） 47/125 集合类型： SET （用于实现多选功能）

时间和日期类型： DATE TIME DATETIME TIMESTAMP YEAR 数位数 7 6 5 4 3 2 1 0 二进制数 1 1 1 1 1 1 1 1 1位十进制数：如 0~9 这个范围的所有数 2位十进制数：如 0~99 这个范围的所有数 3位十进制数：如 0~999 这个范围的所有数

1bit就是1位二进数：如 0 ~ 1 这个取值范围的所有二进制数 0 、 1 2bit就是2位二进数：如 00 ~ 11 这个取值范围的所有二进制数 00 、01、10、11 3bit就是3位二进数：如 000 ~ 111 这个取值范围的所有二进制数 000、001、010、011、100、101、 110、111 8bit就是8位二进制数：如 0000 0000 ~ 1111 11111 这个取值范围的所有二进制数

1Byte=8bit=28 2Byte=28bit=16bit=216 3Bbye=38bit=24bit=224 4Bbye=48bit=32bit=232 8Bbye=88bit=64bit=264 二、数据类型测试 ===整数类型测试：tinyint,int 作用：用于存储用户的年龄、游戏的Level、经验值等。警告：以下测试是在test库中创建的表。use test; 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 | | +--------------+------------+------+-----+---------+-------+ 48/125 2 rows in set (0.01 sec) 说明：int(11)中的11是数值的位数(也称宽度)，int(11)表示能存11位的整数(11位中包括+-号的符号位)。如果要存的数的范围是-99~+99要用int(3)。 mysql> insert into test1 values (111,111); Query OK, 1 row affected (0.09 sec) mysql> select * from test1; +--------------+----------+ | tinyint_test | int_test | +--------------+----------+ | 111 | 111 | +--------------+----------+ mysql> insert into test1(tinyint_test) values(128); 数值超出了-128~+127这个范围 ERROR 1264 (22003): Out of range value for column 'tinyint_test' at row 1 mysql> insert into test1(int_test) values(99999999999); 数值超出了-2 147 483 648~+2 147 483 647这个范围 Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into test1(int_test) values(-99999999999); Query OK, 1 row affected, 1 warning (0.00 sec) //测试结果，默认有符号，超过存储范围出错。 tinyint的取值范围：-128~127 即-27~+27-1 int的取值范围：-2 147 483 648~+2 147 483 647 即-231~+231-1 LAB2: 无符号(unsigned)×××测试 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(99999); 数值超出了0~255这个范围 Query OK, 1 row affected, 1 warning (0.01 sec) 提示'1行被处理，1个警告' mysql> insert into test2(tinyint_test) values(-99999); 数值超出了0~255这个范围 Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from test2; 49/125 mysql> insert into test2(int_test) values(99999999999); 数值超出了0~4294967295这个范围 Query OK, 1 row affected, 1 warning (0.01 sec) 提示'1行被处理，1个警告' mysql> insert into test2(int_test) values(-99999999999); 数值超出了0~4294967295这个范围 Query OK, 1 row affected, 1 warning (0.01 sec) mysql> select * from test2; mysql> insert into test2 values(-20,-20); ERROR 1264 (22003): Out of range value for column 'tinyint_test' at row 1 附加练习：测试无符号的bigint的取值范围。 MariaDB [test]> create table t3(bigint_test bigint unsigned); Query OK, 0 rows affected (0.00 sec) MariaDB [test]> desc t3; +-------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------------+------+-----+---------+-------+ | bigint_test | bigint(20) unsigned | YES | | NULL | | +-------------+---------------------+------+-----+---------+-------+ 1 row in set (0.00 sec) MariaDB [test]> insert into t3 values (9999999999999999999999999999999999999999999999999999999999); Query OK, 1 row affected, 1 warning (0.00 sec) MariaDB [test]> insert into t3 values (-9999999999999999999999999999999999999999999999999999999999); Query OK, 1 row affected, 1 warning (0.01 sec) MariaDB [test]> select * from t3; +----------------------+ | bigint_test | +----------------------+ | 18 446 744 073 709 551 615 | 无符号bigint的最大值 | 0 | 无符号bigint的最小值 +----------------------+ 2 rows in set (0.00 sec) 测试整数类型的显示宽度 mysql> create table t1 ( -> id1 int, -> id2 int(6) -> ); 50/125 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) 实现以0开头的序号：zerofill 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 | 51/125 | 0000000003 | 2222222 | +------------+---------+ 2 rows in set (0.00 sec) 结论：×××的宽度仅为显示宽度，不是限制取值范围。因此建议×××无须指定宽度。 tinyint(1) 和 tinyint(3) 没什么区别，占用字节都是一位，存储范围都是一样的 init（number）后面不加 zerofill就没有任何区别增加糊显示的长度不一样 tinyint(3) zerofill ，当插入的数据少于3位的时候，左边自动补零，这才是限制显示长度 int(1) 和 tinyint(1) ，够用的情况下，优先选择tinyint(1)，因为占字节少、节省空间。 tinyint一个字节 smallint 两个字节 MEDIUMINT三个字节 int 4个字节 BIGINT 8个字节。 unsigned无符号无正负 varchar(5) 这里的5 限制的是储存字符的个数，字符不分贵贱（不分中文、英文、数字...）。 mysql的基本数据类型里几个int如下：类型大小范围（有符号）范围（无符号）用途 TINYINT 1字节 (-128~127) (0~255) 迷你整数值 SMALLINT 2 字节 (-32 768~32 767) (0~65 535) 小整数值 MEDIUMINT 3 字节 (-8 388 608~8 388 607) (0~16 777 215) 中等整数值 INT或INTEGER 4 字节 (-2 147 483 648~2 147 483 647) (0~4 294 967 295) 整数值 BIGINT 8 字节 (-9 233 372 036 854 775 808~9 223 372 036 854 775 807) (0~18 446 744 073 709 551 615) 大整数值 ===浮点数类型测试：作用：用于存储用户的身高、体重、薪水等浮点数和定点数都可以用类型名称后加(M,D)的方式来表示，(M,D)表示一共显示M位数字（整数位 +小数位），其中D位于小数点后面，M和D又称为精度和标度。例如：要存的数值是99.999，用无符号(无+-号)的浮点型如何表示。 float(5,3) unsigned create table flt(weight float(5,3) unsigned); insert into flt values(98.888),(9999.99999); select * from flt; 单精度浮点数（float）与双精度浮点数（double）的区别如下：（1）在内存中占有的字节数不同 • 单精度浮点数在机内占4个字节举例：最小值：0000 0000 0000 0000 .0000 0000 0000 0000 最大值：1111 1111 1111 1111 . 1111 1111 1111 1111 • 双精度浮点数在机内占8个字节 52/125 （2）有效数字位数不同 • 单精度浮点数有效数字8位 • 双精度浮点数有效数字16位（3）所能表示数的范围不同 • 单精度浮点的表示范围：-3.40E+38 ~ +3.40E+38（3.40E+38” 表示的数是3.4×10的38次方 +38强调指次方的指数是正数） • 双精度浮点的表示范围：-1.79E+308 ~ +1.79E+308 （4）在程序中处理速度不同一般来说，CPU处理单精度浮点数的速度比处理双精度浮点数快浮点数： float和double在不指定精度时，默认会按照实际的精度（由实际的硬件和操作系统决定）来显示，实例： create table t2(salary float(5,2)); 即整数+小数共5位，小数是2位 desc t2; insert into t2 values(100.5),(520.23),(5230.1289); select * from t2; 说明：上面的5230.1289超出了我们定义的数据长度，产生了溢出，所以查出来的结果有问题。如果只是小数位的长度超出了范围，会自动四舍五入。定点数： decimal（十进制小数）在不指定精度时，默认的整数位为10，默认的小数位为0 decimal 数据类型最多可存储 38 个数字，所有数字都能够放到小数点的右边。decimal 数据类型存储了一个准确（精确）的数字表达法；不存储值的近似值。定义 decimal 的列、变量和参数的两种特性如下：· p 小数点左边和右边数字之和，不包括小数点。如 123.45,则 p=5，s=2。指定精度或对象能够控制的数字个数。· s指定可放到小数点右边的小数位数或数字个数。p 和 s 必须遵守以下规则：0 <= s <= p <= 38。

p (有效位数) 可储存的最大十进位数总数，小数点左右两侧都包括在内。 s (小数位数) 小数点右侧所能储存的最大十进位数。所以能取得最大数是38位都在小数点左边即10的38次方-1，同理最小-10的38次方+1 定点数在MySQL内部以字符串形式存储，比浮点数更精确，适合用来表示货币等精度高的数据。 mysql> create table test4(float_test float(5,2)); //一共5位，小数占2位，整数占3位 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 53/125 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) MariaDB [mydb]> select * from t3; +-------+ | d | +-------+ | 70.25 | +-------+ 说明：插入数据时，当小数位超过我们所定义的位数时，会自动四舍五入。 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 十进制对应二进制 2^7 2^6 2^5 2^4 2^3 2^2 2^1 2^0 128 64 32 16 8 4 2 1 十进制4 ----》2进制为0100 16进制转换成二进制的方法是，取四合一 0 1 2 3 4 5 6 7 8 9 A B C D E F 0000 0001 0010 0011 0100 0101 0110 0111 1000 1001 1010 1011 1100 1101 1110 1111 16进制4---》2进制为0100 BIT(M)可以用来存放多位二进制数，M范围从1~64，如果不写默认为1位对于位字段可以使用函数读取： bin()显示为二进制 54/125 hex()显示为十六进制（0-9A-F） mysql> create table test_bit (id bit(4)); 创建数据表 Query OK, 0 rows affected (0.35 sec) mysql> desc test_bit; 显示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; 注意，当类型为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) 说明：bin是指明输出为2进制，hex输出成16进制。插入超过指定宽度的值：(表中id字段的数据类型为bit，宽度为4bit,即4位2进制) mysql> insert into test_bit values(8); 8的2进制是1000 Query OK, 1 row affected (0.05 sec) mysql> insert into test_bit values(9); 9的2进制是1001 Query OK, 1 row affected (0.06 sec) mysql> insert into test_bit values(16); 16的2进制是10000，超过了4bit,下面提示数据太长 ERROR 1406 (22001): Data too long for column 'id' at row 1 ===时间和日期类型测试：year、date、time、datetime、timestamp 作用：用于存储用户的注册时间，文章的发布时间，文章的更新时间，员工的入职时间等源贴：https://www.cnblogs.com/mxwz/p/7520309.html TIMESTAMP和DATETIME的相同点： 1> 两者都可用来表示YYYY-MM-DD HH:MM:SS[.fraction]类型的日期。 TIMESTAMP和DATETIME的不同点： 1> 两者的存储方式不一样对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将 55/125 其又转化为客户端当前时区(即会计算时差)进行返回。而对于DATETIME，不做任何改变，基本上是原样输入和输出。 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()); 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) 56/125 注意事项：其它的时间，按要求插入 ==插入年份时，尽量使用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则保留这些空格注(必记)：常用中文字符用utf-8编码占用3个字节字符所占字节数(详细)：https://blog.csdn.net/u013749540/article/details/84103883 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 | | 57/125 | 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是计算字符长度的函数 +-----------+-----------+ | length(v) | length(c) | +-----------+-----------+ | 4 | 2 | +-----------+-----------+ 1 row in set (0.00 sec) mysql> insert into vc values('abc ','abd '); mysql> select length(v),length(c) from vc; +-----------+-----------+ | length(v) | length(c) | +-----------+-----------+ | 4 | 2 | | 4 | 3 | +-----------+-----------+ mysql> select concat(v,'='), concat(c,'=') from vc; //在后面加字符'='，看的更清楚 +---------------+---------------+ | concat(v,'=') | concat(c,'=') | +---------------+---------------+ | ab = | ab= | +---------------+---------------+ 1 row in set (0.00 sec) 说明：concat是连接字符串的函数，即将多个单词（即字符）连成一句话。字符串类型测试：BINARY、VARBINARY BINARY 和 VARBINARY类似于CHAR 和 VARCHAR，不同的是它们包含二进制字符而不包含非二进制字符串，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。 BINARY和VARBINARY允许的最大长度一样，如同CHAR和VARCHAR，不同的是BINARY和VARBINARY的长度是字节长度而不是字符长度字符集：如日常生活中常见的标准字符有0~9、a-z、A~Z、汉字等。字节字符串：1汉字＝2字节字符字符串：1汉字＝1字符 mysql> create table binary_t (c binary(3)); Query OK, 0 rows affected (0.03 sec) 58/125 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; hex表示16进制 +------+--------+ | c | hex(c) | +------+--------+ | aaa | 616161 | +------+--------+ 1 row in set (0.00 sec) 当列数据项大小一致时应使用 binary。当列数据项大小不一致时应使用 varbinary。＝＝＝字符串类型＝＝＝ 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 test; 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) 59/125 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) ======================================================== 字符所占字节数字符所占字节数原文： https://blog.csdn.net/u013749540/article/details/84103883 英文字母：字节数 : 1;编码：GB2312 字节数 : 1;编码：GBK 字节数 : 1;编码：GB18030 字节数 : 1;编码：ISO-8859-1 字节数 : 1;编码：UTF-8 字节数 : 4;编码：UTF-16 60/125 字节数 : 2;编码：UTF-16BE 字节数 : 2;编码：UTF-16LE 中文汉字：字节数 : 2;编码：GB2312 字节数 : 2;编码：GBK 字节数 : 2;编码：GB18030 字节数 : 1;编码：ISO-8859-1 字节数 : 3;编码：UTF-8 由于1个汉字占3个字节，所以2个汉字占23个字节，即6个字节 char (6) 字节数 : 4;编码：UTF-16 字节数 : 2;编码：UTF-16BE 字节数 : 2;编码：UTF-16LE 练习：执行如下命令，熟悉utf8字符编码的汉字占用3个字节的标准。 echo '×××' |cut -c 1-3 结果如下中华人 echo '×××' |cut -b 1-9 结果如下中华人 echo '×××' |cut -b 1-8 结果如下中华▒ 由于utf8编码的每个汉字占3个字节，三个字要占9个字节，而此处只提取了8个字节，所以最后一个字出现乱码了练习：执行如下mysql的操作。 create table tk(name char(8))engine=innodb CHARACTER set UTF8; insert into tk values('刘涛'),('刘涛老公'); select * from tk; Mariadbz主从复制 AB主从复制：简称AB复制，在A主机上做create、update、insert、drop、delete等数据库、表、记录的增、删、改操作，B主机上会自动做数据库、表、记录的同步更新。 AB复制的工作原理(即工作过程)：

在主库上把数据更改记录到二进制日志（Binary Log）中。
备库将主库上的日志复制到自己的中继日志（Relay Log）中。 61/125
备库读取中继日志中的事件，将其重放到备库数据库之上。 AB复制的一主一从模式工作过程： A主机: create database db1 ----> 会将此命令自动写入本机的二进制日志文件中 B主机: I/O线程监测并读A主机上的二进制日志文件新增的内容，且将新内容写入到B主机自己的中继日志文件中 SQL线程读取B主机上中继日志文件中心的SQL语句，并且自动执行这些SQL语句。最终在B主机上创建了db1这个库。网络拓扑：(1主1从) mysql主服务器(master主人)：192.168.11.11 mysql从服务器(slave随从、奴隶)：192.168.11.12 项目准备：(在master、slave主机上分别做如下操作) 0.[选做]。安装mariadb-server服务器端软件、mariadb客户端软件，启动mariadb服务，查mariadb服务端口号3306。 yum install -y mariadb-server mariadb systemctl restart mariadb lsof -i :3306 或 netstat -atunlp | grep :3306 1.停止master、slave主机上的mariadb服务，并且清空/var/lib/mysql的所有数据。 systemctl stop mariadb rm -rfv /var/lib/mysql/* 62/125 2.重启master、slave主机上的mariadb服务。并且查库、test库中是否有表(无表就OK)。 systemctl restart mariadb mysql -uroot -e 'show databases;use test;show tables;' AB复制的配置思路： 1.在A主机(master)上的/etc/my.cnf主配置文件中开启binlog二进制日志文件功能，并且给主机设置 server-id唯一标识编号。重启mariadb服务。 2.在A主机上创建用于AB主从复制的用户账号，并查看master状态中的binlog日志文件的position位置数值。 3.在B主机上的/etc/my.cnf主配置文件中设置server-id唯一标识编号。。重启mariadb服务。 4.在B主机上用help change master查命令帮助，并用change master命令告诉B主机他的master主人的正确信息。 5.在B主机上用start slave启动mariadb的随从服务，并用show slave status查看AB主从复制的数据同步状态，要确认两个线程的yes状态。 AB复制的配置实施： 1.在A主机(master)上的/etc/my.cnf主配置文件中开启binlog二进制日志文件功能，并且给主机设置 server-id唯一标识编号。重启mariadb服务。 vim /etc/my.cnf 做如下修改 [mysqld] 找到此行,添加如下蓝色字的3行功能选项 server-id=11 指定server-id为11,通常用本机IP的最后一组数 log-bin=master-bin 添加此行，指定二进制日志文件名为master-bin skip_name_resolv=1 跳过域名解析功能重启mariadb服务: systemctl restart mariadb 2.在A主机上创建用于AB主从复制的用户账号，并查看master状态中的binlog日志文件的position位置数值。 mysql -uroot -e "grant replication slave on . to 'rep'@'192.168.11.%' identified by 'rep';" mysql -uroot -e 'select user,host,password from mysql.user;show master status;' 3.在B主机上的/etc/my.cnf主配置文件中设置server-id唯一标识编号。重启mariadb服务。 vim /etc/my.cnf 做如下修改 [mysqld] 找到此行,添加如下蓝色字的3行功能选项 server-id=12 指定server-id为12,通常用本机IP的最后一组数 #log-bin=slave-bin 添加此行，指定二进制日志文件名为slave-bin skip_name_resolv=1 跳过域名解析功能 63/125 重启mariadb服务: systemctl restart mariadb mysql -urep -prep -h192.168.11.11 -e 'status;' 测试rep用户是否能远程访问master主机的数据库服务 4.在B主机上用help change master查命令帮助，并用change master命令告诉B主机他的master主人的正确信息。 mysql help change master to; CHANGE MASTER TO MASTER_HOST='192.168.11.11', MASTER_USER='rep', MASTER_PASSWORD='rep', MASTER_PORT=3306, MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=542, MASTER_CONNECT_RETRY=10; //重试连接的时间为10秒金老师的环境： CHANGE MASTER TO MASTER_HOST='10.31.165.5', MASTER_USER='rep', MASTER_PASSWORD='rep', MASTER_PORT=3306, MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=542, MASTER_CONNECT_RETRY=10; 5.在B主机上用start slave启动mariadb的随从服务，并用show slave status查看AB主从复制的数据同步状态，要确认两个线程的yes状态。 mysql -uroot -p start slave; show slave status\G 注: \G 是分组(group)显示信息。注：change master to设置的信息默认保存在/var/lib/mysql/master.info文件中，relay中继日志的设置信 64/125 息默认保存在/var/lib/mysql/relay-log.info文件中。 ls /var/lib/mysql/ cat /var/lib/mysql/master.info cat /var/lib/mysql/relay-log.info cat /var/lib/mysql/mysql.sock 此套接字设备文件无法查看，是正常现象 5.测试主从复制的数据同步。首先，在A主机(192.168.11.11)上创建一个名称为db1的库，并查库。 mysql create database db1; show database; 然后，在B主机上查库，看到了db1库，说明AB主机的数据自动同步成功。 mysql -uroot -e 'show databases;'

MySQL读写分离 MySQL读写分离 MySQL的主从复制和MySQL的读写分离两者有着紧密联系，首先部署主从复制，只有主从复制完了，才能在此基础上进行数据的读写分离。简单来说，读写分离就是只在主服务器上写，只在从服务器上读，基本的原理是让主数据库处理事务性查询，而从数据库处理select查询，数据库复制被用来把事务性查询导致的改变更新同步到集群中的从数据库。

MySQL 读写分离技术

基于程序代码内部实现在代码中根据 select、insert 进行选择分类; 这类方法也是目前生产环境应用最广泛的。优点是性能较好, 因为在程序代码中实现,不需要增加额外的设备作为硬件开支。缺点是需要开发人员来实现，运维人员无从下手。
基于中间代理层实现代理一般是位于客户端和服务器之间, 代理服务器接到客户端请求后通过判断然后转发到后端数据库。目前主要有两个代表性程序: mysql-proxy: mysql-proxy 为 MySQL 开源项目,通过其自带的 lua 脚本进行 SQL 判断,虽然是 MySQL 官方产品，但是 MySQL 官方并不建议将 mysql-proxy 用到生产环境。 amoeba: 由陈思儒开发，作者曾就职于阿里巴巴,现就职于盛大。该程序由 java 语言进行开发,目前只听说阿里巴巴将其用于生产环境。另外,此项目严重缺少维护和推广。 65/125 mysql的读写分离集群架构图是啥样的？数据库代理软件(也称为中间件)： 1.mysql官方推出的代理软件：mysql-proxy 2.360团队开发的Atlas(是mysql-proxy的二次开发优化版) 3.阿里公司的开发团队开发的Amoeba(是变形虫的意思)：基于java的软件，必须要安装java的运行套件 jdk软件包。 4.mycat：是Amoeba的二次开发优化版，基于java的软件，必须要安装java的运行套件jdk软件包。mycat 可以实现分库、分表功能，很多互联网公司的mysql数据库集群都会用mycat作为集群管理的中间件。 5.mysql-router：mysql团队在2016年左右发布的一款新的数据库代理服务器软件。 mysql的官网：https://www.mysql.com/ mysql的社区版下载网址：https://dev.mysql.com/downloads/mysql/ • DOWNLOADS • MySQL Community Server 社区版服务器 • MySQL NDB Cluster NDB网络数据库集群(cluster) • MySQL Shell mysql的命令行工具 • MySQL Router mysql的集群代理软件 • MySQL Workbench 类似于navicat的图形界面客户端软件练习：下载ali阿里的网络yum源。 wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo yum repolist 练习：下载mysql官网的网络yum源，通过网络yum源安装mysql-workbench-community客户端软件。 cd wget https://repo.mysql.com//mysql80-community-release-el7-2.noarch.rpm rpm -ivh mysql80-community-release-el7-2.noarch.rpm rpm -ql mysql80-community-release yum search mysql yum install -y mysql-workbench-community mysql的相关软件下载网址：https://www.mysql.com/downloads/ MySQL Enterprise Edition (commercial) MySQL Enterprise Edition includes the most comprehensive set of advanced features and management tools for MySQL. • MySQL Database • MySQL Storage Engines (InnoDB, MyISAM, etc.) • MySQL Connectors (JDBC, ODBC, .Net, etc.) • MySQL Replication 复制功能 • MySQL Partitioning 分区功能，实现分库分表分区等功能 • MySQL Utilities 工具箱 • MySQL Workbench 图形界面(GUI)客户端程序,是类似于Navicat的软件 • MySQL Enterprise Backup 企业版备份软件 • MySQL Enterprise Monitor 企业版监控 • MySQL Enterprise HA 企业版高可用集群管理软件 • MySQL Enterprise Security 企业版安全管理软件 • MySQL Enterprise Transparent Data Encryption (TDE) 企业版透明数据加密 66/125 • MySQL Enterprise Firewall 企业版防火墙 • MySQL Enterprise Encryption 企业版机密软件 • MySQL Enterprise Audit 企业版审计软件 MySQL Proxy MySQL Proxy： ======================================================== MySQL_Proxy Master Slave1 Slave2 ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝= IP 192.168.1.250 192.168.1.27 192.168.1.215 192.168.1.66 Server_ID 6 215 66 实现步骤： MySQL主/备复制安装并配置MySQL Proxy 测试Proxy 一、MySQL主/备复制（略）二、安装并配置MySQL Proxy 注意：mysql-proxy代理服务器上是不用安装mysql软件的。
安装并配置 [root@MySQL_Proxy ~]# service mysqld stop [root@MySQL_Proxy ~]# chkconfig mysqld off [root@MySQL_Proxy ~]# rpm -qa |grep lua lua-5.1.4-4.1.el6.x86_64 67/125 [root@MySQL_Proxy ~]# tar xf mysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz -C /usr/local/ [root@MySQL_Proxy ~]# cd /usr/local/ [root@MySQL_Proxy ~]# ln -s mysql-proxy-0.8.4-linux-el6-x86-64bit mysql-proxy [root@MySQL_Proxy ~]# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua min_idle_connections = 1, max_idle_connections = 1,
启动mysql-proxy [root@MySQL_Proxy ~]# lsof -i TCP:3306 [root@MySQL_Proxy ~]# /usr/local/mysql-proxy/bin/mysql-proxy --help-proxy -P 指定proxy服务器工作的地址和端口，如-P 192.168.11.13:3306 -b 指定后端的写服务器的地址和端口, 如 -b 192.168.11.11:3306 -r 指定读read服务器的地址和端口，如-r 192.168.11.12:3306 -s 指定判断的脚本，如-s /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua --daemon 以后台进程的方式启动

调整最大打开的文件数 [root@MySQL_Proxy ~]# ulimit -a |grep 'open files' [root@MySQL_Proxy ~]# ulimit -n 10240 [root@MySQL_Proxy ~]# ulimit -a |grep 'open files' open files (-n) 10240 /usr/local/mysql-proxy/bin/mysql-proxy -P 10.31.165.7:3306 -b 10.31.165.5:3306 -r 10.31.165.6:3306 -s /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua --daemon #运行mysql-proxy数据库代理服务器软件，并指定代理服务器IP和端口号，以及后端的读、写服务器的IP 和端口号。 [root@MySQL_Proxy ~]# ln -s /usr/local/mysql-proxy/bin/mysql-proxy /bin/mysql-proxy 创建软链接文件 [root@MySQL_Proxy ~]# /usr/local/mysql-proxy/bin/mysql-proxy -P 192.168.1.250:3306 -b 192.168.1.27:3306 -r 192.168.1.215:3306 -r 192.168.1.66:3306 -s /usr/local/mysql-proxy/share/doc/ mysql-proxy/rw-splitting.lua --daemon 2014-02-13 17:15:54: (critical) plugin proxy 0.8.4 started [root@MySQL_Proxy ~]# netstat -tnlp |grep :3306 tcp 0 0 192.168.10.137:3306 0.0.0.0:* LISTEN 16620/mysql-proxy [root@MySQL_Proxy ~]# vim /etc/rc.local 添加如下内容(此操作是为了然系统启动时自动运行mysqlproxy代理服务器软件) ulimit -n 10240 /usr/local/mysql-proxy/bin/mysql-proxy -P 192.168.1.250:3306 -b 192.168.1.27:3306 -r 192.168.1.215:3306 -r 192.168.1.66:3306 -s /usr/local/mysql-proxy/share/doc/mysql-proxy/rwsplitting.lua --daemon 到此结束，以下内容暂时不做。

以下是采用mysql-proxy.cnf配置文件来指定代理服务器IP和端口号，以及后端的读、写服务器的IP和端口 68/125 号。然后在运行mysql-proxy服务时指定配置文件来加载相关功能。 cat /usr/local/mysql-proxy/mysql-proxy.cnf 此文件需要自己用vim创建,内容如下 [mysql-proxy] log-file = /usr/local/mysql-proxy/mysqlproxy.log proxy-address=192.168.1.253:3306 代理服务器的IP和端口号 proxy-backend-addresses=192.168.1.251:3306 后端可写服务器的IP和端口号 proxy-read-only-backend-addresses=192.168.1.252:3306 后端只读服务器的IP和端口号 proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua 读写分离的判断脚本运行测试：首先：杀mysql-proxy的进程，pkill -9 mysql-proxy 然后，执行 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/mysqlproxy.cnf & 最后，查3306端口： lsof -i :3306 或 netstat -tnlp |grep :3306 ++++++++++ [mysql-proxy] daemon = true pid-file = /var/run/mysql-proxy.pid log-file = /var/log/mysql-proxy.log log-level = debug max-open-files = 1024 plugins = admin,proxy user = mysql-proxy

#Proxy Configuration proxy-address = 0.0.0.0:3306 proxy-backend-addresses = 192.168.211.120:3306 主 proxy-read-only-backend-addresses = 从 192.168.211.130:3306,192.168.211.130:3307 #proxy-lua-script = proxy-lua-script = /usr/src/mysql-proxy-0.8.5/lib/rw-splitting.lua #proxy-skip-profiling = true

Admin Configuration

admin-address = 0.0.0.0:4041 admin-lua-script = /usr/local/lib/mysql-proxy/lua/admin.lua admin-username = admin admin-password = admin

chmod 600 /etc/mysql-proxy.cnf

mysql -uadmin -padmin -h192.168.211.140 -P4041 mysql> select * from backends;

三、测试

主库(在192.168.11.11主库上做) mysql mysql> grant ALL on . to bbs@'%' identified by 'tianyun'; mysql> flush privileges; mysql> create database bbs; mysql> create table bbs.t1 (name varchar(50)); 69/125
备库 mysql> stop slave; //暂时断掉和主库的连接
从客户端测试 a. 读 ====主 or 备 b. 写 ====主
备库 mysql> start slave; ======================================================= MYSQL代理服务器查询的状态结果客户机访问MYSQL代理服务器后的变化： Atlas ##采用Atlas实现MySQL读写分离 mysql的读写分离集群架构图： client----->mysql-proxy代理(数据库代理商)----->mysql-server服务器A(Read/Write可读可写) ----->mysql-server服务器B(ReadOnly可读) ----->mysql-server服务器C(ReadOnly可读) ----->mysql-server服务器D(ReadOnly可读) 一、基础介绍 ============================================================================== 1、背景描述目前我们的高可用DB的代理层采用的是360开源的Atlas，从上线以来，已稳定运行2个多月。无论是从性能上，还是稳定性上，相比其他开源组件（amoeba、cobar、MaxScale、MySQL-Proxy等），还是很出色的。当初我们之所以选择Atlas，主要看中它有以下优点： (1)、基于mysql-proxy-0.8.2进行修改，代码完全开源； (2)、比较轻量级，部署配置也比较简单； (3)、支持DB读写分离； (4)、支持从DB读负载均衡，并自动剔除故障从DB； 70/125 (5)、支持平滑上下线DB； (6)、具备较好的安全机制（IP过滤、账号认证）； (7)、版本更新、问题跟进、交流圈子都比较活跃。在测试期间以及线上问题排查过程中，得到了360 Atlas作者朱超的热心解答，在此表示感谢。有关更多 Atlas的介绍，我就不一一例举，可以参考以下链接： https://github.com/Qihoo360/Atlas/blob/master/README_ZH.md 3、系统环境 CentOS 7.6 x86_64 master主服务器（R/W可读可写）：192.168.11.11 slave从服务器(ReadOnly只读)：192.168.11.12 atlas-proxy前端代理(读写分离的代理):192.168.11.13 ##安装mysql-server主服务端(192.168.10.25) service firewalld stop chkconfig firewalld off setenforce 0 getenforce sed -i 's/^SELINUX=enforcing/SELINUX=permissive/' /etc/selinux/config yum install -y mariadb mariadb-server service mariadb restart chkconfig mariadb on sed -i '1aserver_id=25' /etc/my.cnf sed -i '2alog-bin=mysql-bin' /etc/my.cnf service mariadb restart mysql -uroot -e "grant all on . to admin@'%' identified by '01';flush privileges;" mysql -uroot -e "grant replication slave on . to rep@'%' identified by '01';flush privileges;" mysql -uroot -e "select user,host,password from mysql.user;" mysql -uroot -e "reset master;show master status;" ##安装mysql-server主服务端(192.168.10.26) service firewalld stop chkconfig firewalld off setenforce 0 getenforce sed -i 's/^SELINUX=enforcing/SELINUX=permissive/' /etc/selinux/config yum install -y mysql mysql-server service mysql restart chkconfig mysql on sed -i '1aserver_id=26' /etc/my.cnf sed -i '2alog-bin=mysql-bin' /etc/my.cnf service mysql restart mysql -uroot -e "grant all on . to admin@'%' identified by '01';flush privileges;" mysql -uroot -e "grant replication slave on . to rep@'%' identified by '01';flush privileges;" mysql -uroot -e "select user,host,password from mysql.user;" mysql -uroot -e "change master to master_host='192.168.100.25',master_user='rep',master_password='01',master_port=3306,master_log_file='mysqlbin.000001',master_log_pos=245;" sleep 30s mysql -uroot -e "start slave;show slave status\G" ##安装配置atlas读写分离(192.168.10.11) service firewalld stop 71/125 chkconfig firewalld off setenforce 0 getenforce sed -i 's/^SELINUX=enforcing/SELINUX=permissive/' /etc/selinux/config grep '^SELINUX=' /etc/selinux/config 安装和配置atlas软件 rpm -ivh Atlas-2.2.1.el6.x86_64.rpm rpm -ql Atlas echo "PATH=$PATH:/usr/local/mysql-proxy/bin/" > /etc/profile.d/atlas.sh source /etc/profile.d/atlas.sh 加载环境配置文件 ll /usr/local/mysql-proxy/ ##mysql-proxy文件功能说明： bin目录下放的都是可执行文件
“encrypt”是用来生成MySQL加密密码的，在配置的时候会用到
“mysql-proxy”是MySQL自己的读写分离代理软件
“mysql-proxyd”是360弄出来的mysql-proxy原版的新版本，后面有个“d”，服务的启动、重启、停止。都是用他来执行的 conf目录下放的是配置文件
“test.cnf”只有一个文件，用来配置代理的，可以使用vim来编辑 lib目录下放的是一些包，以及Atlas的依赖 log目录下放的是日志，如报错等错误信息的记录进入bin目录，使用encrypt来对数据库的密码进行加密，我的MySQL数据的用户名是admin，密码是01，我需要对密码进行加密 cd /usr/local/mysql-proxy/bin/ ./encrypt 01 生成加密密码,并复制此密码注：此处的密码01是mysql-server服务器上admin用户账号的密码，请在后端的mysql-server服务器上用grant all on . to admin@'%' identified by '01' with grant option;来创建admin这个用户，然后刷新权限表 flush privileges。在192.168.11.11和 192.168.11.12做 cd /usr/local/mysql-proxy/conf/ 切换到atlas的配置文件目录中 cp -av test.cnf test.cnf.bak //备份test.cnf配置文件 vi test.conf 修改后的读写分离的完整配置文件内容(以下红字是要改的内容) [mysql-proxy] admin-username = user admin-password = pwd proxy-backend-addresses = 192.168.100.25:3306 后端负责读写的服务器IP和端口号 proxy-read-only-backend-addresses = 192.168.100.26:3306@1,192.168.100.27:3306@2 pwds = admin:VFnEp9P4Vu4=, rep:VFnEp9P4Vu4= daemon = true keepalive = true event-threads = 8 log-level = message log-path = /usr/local/mysql-proxy/log proxy-address = 0.0.0.0:3306 admin-address = 0.0.0.0:2345 test.cnf读写分离配置文件功能说明： 1:[mysql-proxy] //读写分离代理配置 72/125 6:admin-username = user //管理接口的用户名 9:admin-password = pwd //管理接口的密码 12:proxy-backend-addresses = 192.168.100.25:3306 //主数据库的IP地址和端口号(可读可写) 15:proxy-read-only-backend-addresses = 192.168.100.26:3306@1,192.168.100.27:3306@2 //读服务器的ip、端口和权重 18:pwds = admin:VFnEp9P4Vu4=, rep:VFnEp9P4Vu4= //后端MYSQL的用户名和encrypt命令生成的加密密码 21:daemon = true //设置为守护进程模式(后台运行) 24:keepalive = true //允许keepalive 27:event-threads = 8 //工作线程数为8 30:log-level = message //日志等级为message消息 33:log-path = /usr/local/mysql-proxy/log //日志文件路径 45:proxy-address = 0.0.0.0:3306 //Atlas监听的管理接口IP和端口 48:admin-address = 0.0.0.0:2345 //Atlas监听的管理接口IP和端口启动atlas服务：/usr/local/mysql-proxy/bin/mysql-proxyd test start 查atlas端口号：lsof -i :3306 和 lsof -i :2345 重启atlas服务：/usr/local/mysql-proxy/bin/mysql-proxyd test restart 设置mysql-proxyd开机启动： echo "/usr/local/mysql-proxy/bin/mysql-proxyd test start" >> /etc/profile source /etc/profile tcpdump抓包： tcpdump -i eth0 port 3306 说明：抓取经过192.168.10.11代理主机的eth0网卡的3306端口的数据包，验证读写分离效果。访问atlas代理服务器的测试： yum install -y mariadb 安装mariadb客户端软件 mysql -uadmin -p01 -h 192.168.10.11 -P3306 登录到atlas管理端：mysql -uuser -ppwd -h 192.168.10.11 -P2345 mysql的读写分离集群架构图： client----->Atlas代理(数据库代理商)----->mysql-server服务器A(Read/Write可读可写) ----->mysql-server服务器B(ReadOnly可读) ----->mysql-server服务器C(ReadOnly可读) ----->mysql-server服务器D(ReadOnly可读) 73/125 Mycat Mysql事务事务：这个词的字面意思是什么？答：要做的一件事情和任务。mysql中采用事务功能可以实现有选择性胡对表中数据操作做回滚、提交。 MYSQL默认处理任务的原则：执行增、删、改操作会自动保存数据到库、表、记录中。 MYSQL事务的ACID原则(必看的经典段子)：https://blog.csdn.net/qin_jian_bo/article/details/78324839 MYSQL的事务处理主要有两种方法。 1、用begin,rollback,commit来实现 begin 开始一个事务，然后执行create、update、insert等操作 rollback 事务回滚（相当于word中的ctrl+Z撤消） commit 事务确认(提交，相当于word中的ctrl+s保存) 2、直接用set来改变mysql的自动提交模式 MYSQL默认是自动提交的，也就是你提交一个QUERY，它就直接执行！我们可以通过 show variables \G 查看MYSQL环境变量 show variables like 'autocom%'; 查看autocommit环境变量的状态在etc/my.cnf进行设置重启服务mariadb set autocommit=0 禁止自动提交(临时设置) （即自动开启事务功能，并不需要用begin开始事务，但是必须用commit提交操作，或用rollback撤消操作） set autocommit=1 开启自动提交（必须用begin开始一个事务，用commit或rollback结束事务）

来实现事务的处理。但注意当你用 set autocommit=0 的时候，你以后所有的SQL都将做为事务处理，直到你用commit确认或 rollback结束，注意当你结束这个事务的同时也开启了个新的事务！按第一种方法只将当前的作为一个事务！个人推荐使用第一种方法！ MYSQL中只有INNODB和BDB类型的数据表才能支持事务处理！其他的类型是不支持的！注意：事务功能只对表的insert、delete、update这些操作有效。

修改存储引擎 mysql> alter table 表名 engine=innodb; //engine=myisam|memore|.... 实例：打开test数据库文件，创建一张dbtest的表，查看表中的数据，开启事务功能，插入2条数据，提交事务，查看表中的数据。开启新事务，插入1条记录，回滚操作（撤消），查看表中的数据。 mysql> use test; Database changed 74/125 mysql> CREATE TABLE dbtest( -> id int(4) -> ) TYPE=INNODB; Query OK, 0 rows affected, 1 warning (0.05 sec) mysql> select * from dbtest -> ; Empty set (0.01 sec) mysql> begin; 开始事务 Query OK, 0 rows affected (0.00 sec) mysql> insert into dbtest value(5); Query OK, 1 row affected (0.00 sec) mysql> insert into dbtest value(6); Query OK, 1 row affected (0.00 sec) mysql> commit; 提交事务 Query OK, 0 rows affected (0.00 sec) mysql> select * from dbtest; +------+ | id | +------+ | 5 | | 6 | +------+ 2 rows in set (0.00 sec) mysql> begin; 开始事务 Query OK, 0 rows affected (0.00 sec) mysql> insert into dbtest values(7); Query OK, 1 row affected (0.00 sec) mysql> rollback; 回滚 Query OK, 0 rows affected (0.00 sec) mysql> select * from dbtest; +------+ | id | +------+ | 5 | | 6 | +------+ 2 rows in set (0.00 sec) mysql> MariaDB [(none)]> use test; Database changed MariaDB [test]> create table tbx(id int); Query OK, 0 rows affected (0.04 sec) 75/125 MariaDB [test]> insert into tbx values(1),(2); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [test]> select * from tbx; +------+ | id | +------+ | 1 | | 2 | +------+ 2 rows in set (0.01 sec) MariaDB [test]> begin; 开始一个事务 Query OK, 0 rows affected (0.00 sec) MariaDB [test]> insert into tbx values(3),(4); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [test]> select * from tbx; +------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | +------+ 4 rows in set (0.01 sec) MariaDB [test]> rollback; 回滚(相当于word中的ctrl+z撤消) Query OK, 0 rows affected (0.00 sec) MariaDB [test]> select * from tbx; +------+ | id | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec) MariaDB [test]> begin; 开始一个事务 Query OK, 0 rows affected (0.00 sec) MariaDB [test]> insert into tbx values(3),(4); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [test]> select * from tbx; +------+ | id | +------+ | 1 | | 2 | 76/125 | 3 | | 4 | +------+ 4 rows in set (0.01 sec) MariaDB [test]> commit; 提交(相当于word中的ctrl+s保存) Query OK, 0 rows affected (0.01 sec) MariaDB [test]> select * from tbx; +------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | +------+ 4 rows in set (0.00 sec) MariaDB [test]> Mysql触发器 MySQL触发器Triggers ======================================================== 触发器简介创建触发器查看触发器删除触发器触发器案例一、触发器简介触发器（trigger）是一个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对A表进行操作事件（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。例如，当学生表中增加了一个学生的信息时，学生的总数就应该同时改变。因此可以针对学生表创建一个触发器，每次增加一个学生记录时，就执行一次学生总数的计算操作，从而保证学生总数与记录数的一致性。二、创建Trigger 语法： CREATE TRIGGER 触发器名称 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROW BEGIN 77/125 触发器程序体; END <触发器名称> 最多64个字符，它和MySQL中其他对象的命名方式一样 { BEFORE | AFTER } 触发器时机 { INSERT | UPDATE | DELETE } 触发的事件 ON <表名称> 标识建立触发器的表名，即在哪张表上建立触发器 FOR EACH ROW 触发器的执行间隔：FOR EACH ROW子句通知触发器每隔一行执行一次动作，而不是对整个表执行一次 <触发器程序体> 要触发的SQL语句：可用顺序，判断，循环等语句实现一般程序需要的逻辑功能 example1
创建表 mysql> use test; mysql> create table stu( id int unsigned auto_increment primary key not null, name varchar(50) ); mysql> insert into stu(name) values('jack'); mysql> create table stu_total(total int); mysql> insert into stu_total values(1);
创建触发器stu_insert_trigger mysql> delimiter $$ 临时定义命令的结束符为$$号 mysql> create trigger stu_insert_trigger after insert -> on stu for each row -> BEGIN -> update stu_total set total=total+1; -> END$$ mysql> delimiter ; 临时定义命令的结束符为;号
创建触发器stu_delete_trigger mysql> delimiter $$ 临时定义命令的结束符为$$号 mysql> create trigger stu_delete_trigger after delete -> on stu for each row -> BEGIN -> update stu_total set total=total-1; -> END$$ mysql> delimiter ; 临时定义命令的结束符为;号三、查看触发器
通过SHOW TRIGGERS语句查看 SHOW TRIGGERS\G
通过系统表triggers查看 USE information_schema SELECT * FROM triggers\G SELECT * FROM triggers WHERE TRIGGER_NAME='触发器名称'\G 78/125 3.测试触发器说明：插入两条记录，然后查stu表、stu_total表中的数据变化，验证触发器功能是否生效。 insert into stu values(2,'tom'); insert into stu values(3,'alice'); select * from stu; select * from stu_total; delete from stu where id=3; select * from stu; select * from stu_total; 案例小结：以上案例是在stu表中插入一条记录，stu_total表中的total字段的值就会自动加1，在stu表中删除一条记录，stu_total表中的total字段的值就会自动减1。此例的触发器故障bug：如果stu_total表中的初始统计数据不正确，以上定义的这个触发器会导致 stu_total表中统计的total值跟stu表中的总记录数信息不对称，所以这个触发器是有问题的。正确的解法是用count函数来统计stu表中的记录数，不应该用加1或减1这种做法。优化过的正确触发器：触发器stu_insert_trigger_ok drop triggers stu_insert_trigger; delimiter $$ create trigger stu_insert_trigger after insert on stu for each row BEGIN update stu_total set total=(select count() from stu); END$$ delimiter ; 触发器stu__delete_trigger_ok drop trigger stu_insert_trigger; delimiter $$ create trigger stu_delete_trigger after delete on stu for each row BEGIN update stu_total set total=(select count() from stu); END$$ delimiter ; 四、删除触发器
通过DROP TRIGGERS语句删除 DROP TRIGGER 解发器名称 show triggers\G 查看触发器的状态五、触发器案例 example2 创建表tab1 DROP TABLE IF EXISTS tab1; CREATE TABLE tab1( id int primary key auto_increment, name varchar(50), sex enum('m','f'), 79/125 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 $$ delimiter和\d表示定义界定符，即命令的结束符 mysql> create trigger tab1_after_delete_trigger -> after delete on tab1 -> for each row -> BEGIN -> delete from tab2 where name=old.name; -> END$$

触发器tab1_after_update_trigger 作用：当tab1更新后，自动更新tab2 mysql> create trigger tab1_after_update_trigger -> after update on tab1 -> for each row -> BEGIN -> update tab2 set name=new.name -> where name=old.name; -> 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) 测试触发器功能说明：：插入两条记录，然后查stu表、stu_total表中的数据变化，验证触发器功能是否生效。 insert into tab1(name,sex) values('tom','m'); insert into tab1(name,sex) values('lucy','f'); select * from tab1; select * from tab2; delete from tab1 where id=2; select * from tab1; select * from tab2; 80/125 案例3.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) Mysql视图 MySQL视图VIEW

视图简介创建视图查看视图修改视图通过视图操作基表删除视图 81/125 一、视图简介通俗理解：就是将一张表中经常要查询的列和记录创建成一张虚拟的表。视图中看到的数据会随着原始表格的更新而动态更新。 MySQL视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。对其中所引用的基础表来说，MySQL视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。视图是存储在数据库中的SQL查询语句，它主要出于两种原因：安全原因，视图可以隐藏一些数据，如：一些敏感的信息使复杂的查询易于理解和使用。三、查看视图

SHOW TABLES 查看视图名 SHOW TABLES;
SHOW TABLE STATUS 示例：查看数据库mysql中视图及所有表详细信息 SHOW TABLE STATUS FROM mysql \G 示例：查看数据库mysql中视图名view_user详细信息 SHOW TABLE STATUS FROM mysql LIKE 'view_user' \G

命令格式：show table status from 库名 where 条件例如：show table status from shop where Comment='VIEW'\G 3. SHOW CREATE VIEW 示例：查看视图定义信息 SHOW CREATE VIEW 视图名\G 4. DESCRIBE 示例：查看视图结构 DESC 视图名; 四、修改视图方法一：删除后新创建 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; 82/125 五、通过视图操作基表查询数据SELECT SELECT * FROM view_user; 更新数据UPDATE 删除数据DELETE 六、删除视图语法： DROP VIEW view_name [,view_name]…; 示例： USE mysql; DROP VIEW view_user ; 二、创建视图语法一： CREATE [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE} ] VIEW 视图名 [(字段1,字段2…)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION ]; 语法二： CREATE VIEW 视图名 AS SELECT语句; 示例1： USE mysql CREATE VIEW view_user AS SELECT user,host,password FROM mysql.user; SELECT * FROM view_user; 示例2：创建视图案例(单表) mysql> CREATE TABLE t (qty INT, price INT); INSERT INTO t VALUES(3, 50); CREATE VIEW v AS SELECT qty, price, qtyprice AS value FROM t; qtyprice两者乘积 SELECT * FROM v; +------+-------+-------+ | qty | price | value | +------+-------+-------+ | 3 | 50 | 150 | +------+-------+-------+ 示例3：创建视图案例 (多表) 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, 83/125 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 as name, product.price as price, purchase.quantity as quantity, product.price * purchase.quantity as total_value from product,purchase where product.name = purchase.name; 等值连接（即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) 84/125 存储过程实例存储过程：定义数据存储的过程。存储过程中允许对库、表、记录做增、删、改、查等操作。存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。存储过程(procedure)和函数(function)的主要区别：存储过程是针对表中的数据记录进行处理的SQL语句集合，就类似于shell脚本。函数通常是针对记录中的某个字段的值进行处理。案例要求：创建一个名称为dba的库文件，在dba库中创建一张名称为tb1的表，表中有id、name这两个字段。创建一个名称为ad1的存储过程，ad1存储过程的功能是插入三条记录到tb1表中。 mysql>create database dba; mysql>use dba; mysql>create table tb1(id int,name char(20)); mysql> \d $$ 等同于delimiter $$，设置命令的界定符（也称结束符） mysql> create procedure ad1() 创建ad1这个存储过程（类似于shell脚本） -> BEGIN -> declare i int default 1; -> while(i<=3)do -> insert into dba.tb1 values(i,'ccc'); -> set i=i+1; -> end while; -> END$$ mysql> \d ; mysql> use dba Database changed mysql> select * from tb1; mysql> call ad1(); 调用此存储过程 mysql> select * from tb1; 练习：定义一个名称为proc007的存储过程，功能是实现用2条insert into语句在上文的tb1表中插入2条数据记录，并查tb1表中的数据。再定义一个名称为proc008的存储过程，功能是实现用delete命令将 proc007存储过程插入的2条数据记录删除，并查tb1表中的数据。分别测试这两个存储过程的调用。 \d $$ MariaDB [dba]> create procedure proc007() -> begin -> inset into tb1 values(4,'wl'); -> inset into tb1 values(5,'qqb'); -> end$$ \d ; call proc007(); select * from tb1; MariaDB [dba]> \d $$ MariaDB [dba]> create procedure proc008() -> begin 85/125 -> delete from tb1 where id=4; -> delete from tb1 where id=5; -> end$$ Query OK, 0 rows affected (0.00 sec) MariaDB [dba]> \d ; MariaDB [dba]> call proc008();select * from tb1; 练习：定义一个名称为ak的存储过程，功能是实现创建一个名称为dbak的库，在dbak库中创建tbak表， tbak表中有id、name这两个字段，在tbak表中插入2条记录1、tom和2、alice的信息。测试ak这个存储过程的调用。首先，创建存储过程。 \d $$ create procedure ak() BEGIN create database dbak; create table dbak.tbak(id int,name char(20)); desc dbak.tbak; insert into dbak.tbak values(1,'tom'),(2,'alice'); select * from dbak.tbak; END$$ \d ; 然后，调用存储过程：call ak();

常用函数查看当前的时间：select now(); 查看版本号：select version();

Mysql索引 MySQL索引问题：从中文字面上如何理解“索引”的意思？答：索引,就是检索引导的意思。是通过一定的算法将数据库中的记录按一定的规律进行分组，查信息时可以缩小数据的搜索范围，从而提高了查询效率。例如：现实生活中将火锅菜单上的各种菜进行分类，有海鲜、青菜、肉类等，就是为了让食客快速找到某个食品。超市中将商品进行类别区域划分，如化妆品、食品等。

创建索引创建表时创建索引 CREATE在已存在的表上创建索引 86/125 ALTER TABLE在已存在的表上创建索引查看并测试索引删除索引一、索引简介索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。二、索引的分类普通索引 index 唯一索引 unique index 全文索引 fulltext index 单列索引多列索引空间索引三、准备实验环境

准备表(可以不做此表) create table t2(id int,name varchar(20)); desc t2; 四、创建索引＝＝＝创建表时语法： CREATE TABLE 表名 ( 字段名1 数据类型 [完整性约束条件…], 字段名2 数据类型 [完整性约束条件…], [UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY [索引名] (字段名[(长度)] [ASC |DESC]) ); 创建INDEX普通索引示例： CREATE TABLE dept10 ( dept_id INT, dept_name VARCHAR(30) , comment VARCHAR(50), INDEX(dept_name) 将表中的dept_name字段指定为普通索引字段 ); 87/125 创建UNIQUE唯一索引示例： CREATE TABLE dept11 ( dept_id INT, dept_name VARCHAR(30) , comment VARCHAR(50), UNIQUE INDEX(dept_name) ); 创建全文索引示例：注意，只有MYISAM存储引擎支持全文索引，innodb存储引擎不支持全文索引。 CREATE TABLE dept12 ( dept_id INT, dept_name VARCHAR(30) , comment VARCHAR(50), log text, FULLTEXT INDEX index_log (log) )engine=myisam; 88/125 创建多列索引示例： CREATE TABLE dept13 ( 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 dept6 (dept_name); 89/125 创建唯一索引示例： CREATE UNIQUE INDEX index_dept_name ON dept6 (dept_name); 创建全文索引示例： CREATE FULLTEXT INDEX index_dept_name ON dept6 (dept_name); 创建多列索引示例： CREATE INDEX index_dept_name_ comment ON dept6 (dept_name, comment); ===ALTER TABLE在已存在的表上创建索引语法： ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (字段名[(长度)] [ASC |DESC]) ; 创建普通索引示例： ALTER TABLE dept ADD INDEX index_dept_name (dept_name); 命令格式：alter table 表名 add index 索引名 (字段名); 创建唯一索引示例： ALTER TABLE dept ADD UNIQUE INDEX index_dept_name (dept_name); 创建全文索引示例： ALTER TABLE dept ADD FULLTEXT INDEX index_dept_name (dept_name); 创建多列索引示例： ALTER TABLE dept ADD INDEX index_dept_name_comment (dept_name, comment); 四、管理索引查看索引 SHOW CRETAE TABLE 表名\G 测试示例 EXPLAIN SELECT * FROM dept WHERE dept_name='hr'; 删除索引 show create table employee6; DROP INDEX 索引名 ON 表名; 实例： create database company; use company; create table employee6(id int,emp_name varchar(20)); insert into employee6 values(1,'tom'),(2,'jack'); select * from company.employee6; EXPLAIN SELECT * FROM employee6 WHERE emp_name='tom'; MariaDB [company]> explain select * from employee6 where emp_name='tom'; +------+-------------+-----------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-----------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | employee6 | ALL | NULL | NULL | NULL | NULL | 2 | Using where | 90/125 +------+-------------+-----------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) alter table employee6 add index emp_name(emp_name); EXPLAIN SELECT * FROM employee6 WHERE emp_name='tom'; 五、Sphinx Sphinx是一个基于SQL的全文检索引擎，可以结合MySQL, PostgreSQL做全文搜索，它可以提供比数据库本身更专业的搜索功能，使得应用程序更容易实现专业化的全文检索。Sphinx特别为一些脚本语言设计搜索API接口，如PHP,Python,Perl,Ruby等，同时为MySQL也设计了一个存储引擎插件。Sphinx 单一索引最大可包含1亿条记录，在1千万条记录情况下的查询速度为0.x秒（毫秒级）。Sphinx创建索引的速度为：创建100万条记录的索引只需 3～4分钟，创建1000万条记录的索引可以在50分钟内完成，而只包含最新 10万条记录的增量索引，重建一次只需几十秒。 Coreseek中文分词 Coreseek 是一个可供企业使用的、基于Sphinx（可独立于Sphinx原始版本运行）的中文全文检索引擎，按照GPLv2协议发行。商业使用（例如, 嵌入到其他程序中）需要获得商业授权。 Coreseek是一个支持中文的全文搜索引擎，意图为其他应用提供高速、低空间占用、高相关度结果的中文全文搜索能力。CoreSeek可以非常容易的与SQL数据库和脚本语言集成。 Sphinx/Coreseek配置 91/125 索引测试实验： 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> delimiter $$ 设置命令的界定符（也称为结束符） mysql> create procedure autoinsert1() 创建autoinsert1这个存储过程（类似于shell脚本） -> 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> use school Database changed 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：命令的作用是查看查询优化器如何决定执行查询 92/125 花费时间比较：创建索引前 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日志管理 MySQL日志管理 ======================================================== mysql命令历史文件系统当前用户家目录下的.mysql_history ，如/root/.mysql_history 文件错误日志: 记录 MySQL 服务器启动、关闭及运行错误等信息二进制日志: 又称binlog日志，以二进制文件的方式记录数据库中除 SELECT 以外的操作查询日志: 记录查询的信息慢查询日志: 记录执行时间超过指定时间的操作(做mysql数据库系统优化时会看此日志) 中继日志：备库将主库的二进制日志复制到自己的中继日志中，从而在本地进行重放通用日志：审计哪个账号、在哪个时段、做了哪些事件事务日志或称redo日志，记录Innodb事务相关的如事务执行时间、检查点等 ======================================================== 93/125 一、bin-log
启用

vim /etc/my.cnf

[mysqld] log-bin[=dir/[filename]] //目录权限必须mysql用户可写

service mysqld restart

暂停(下面是在MYSQL登录后做的临时操作) //仅当前会话 SET SQL_LOG_BIN=0; 暂停 SET SQL_LOG_BIN=1; 激活 //查看BIN环境变量 show variables like '%BIN%';
查看二进制日志文件的方法 //在/var/lib/mysql目录中执行查看全部：

cd /var/lib/mysql ; ls

mysqlbinlog mysql.000002

按时间：

mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56"

mysqlbinlog mysql.000002 --stop-datetime="2012-12-05 11:02:54"

mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56" --stop-datetime="2012-12-05

11:02:54" 按字节数：

mysqlbinlog mysql.000002 --start-position=260

mysqlbinlog mysql.000002 --stop-position=260

mysqlbinlog mysql.000002 --start-position=260 --stop-position=930

截断bin-log（产生新的bin-log文件） a. 重启mysql服务器 b. # mysql -uroot -p123 -e 'flush logs'

二、查询日志启用通用查询日志

vim /etc/my.cnf

[mysqld] log[=dir[filename]]

service mysqld restart

三、慢查询日志启用慢查询日志

vim /etc/my.cnf

[mysqld] log-slow-queries[=dir[filename]] long_query_time=n

service mysqld restart

94/125 MySQL 5.6: slow-query-log=1 slow-query-log-file=slow.log long_query_time=3

Mysql权限体系 mysql 的权限体系介绍 MYSQL权限管理：是指数据库的管理员对某个用户账号在某台主机允许对某些库中的某些表执行某些操作。 grant授权命令： grant all on 库名.表名 to 用户名@'主机名或IP' identified by '密码' [ with 附加权限选项 ] revoke收回(注销)权限命令： revoke all on 库名.表名 to 用户名@'主机名或IP' mysql 的权限体系大致分为5个层级: 全局层级全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。GRANT ALL ON *.*和 REVOKE ALL ON *.*只授予和撤销全局权限。 95/125 数据库层级(即给库中的全部表授权) 数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db和mysql.host表中。GRANT ALL ON db_name.*和REVOKE ALL ON db_name.*只授予和撤销数据库权限。表层级(即给库中的某张表授权) 表权限适用于一个给定表中的所有列。这些权限存储在mysql.talbes_priv表中。GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限。列层级(即字段级) 列权限适用于一个给定表中的单一列。这些权限存储在mysql.columns_priv表中。当使用REVOKE时，您必须指定与被授权列相同的列。子程序层级 CREATE ROUTINE, ALTER ROUTINE, EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了CREATE ROUTINE外，这些权限可以被授予为子程序层级，并存储在 mysql.procs_priv表中。这些权限信息存储在下面的系统表中: mysql.user 用户表 mysql.db 库表 mysql.host 主机表 mysql.table_priv 表的权限表 mysql.column_priv 列的权限表当用户连接进来，mysqld会通过上面的这些表对用户权限进行验证！注意: 当后续目标是一个表、一个已存储的函数或一个已存储的过程时，object_type子句应被指定为TABLE、 FUNCTION或PROCEDURE。当从旧版本的MySQL升级时，要使用本子句，您必须升级您的授权表。请我们可以用 CREATE USER 或 GRANT 创建用户,后者还同时分配相关权限。而 REVOKE 则用于删除用户权限,DROP USER 删除账户。 MySQL 赋予用户权限命令语法为： grant 权限 on 数据库对象 to 用户; grant 权限 on 数据库对象 to 用户 identified by "密码"; grant 权限 on 数据库对象 to 用户@"ip" identified by "密码" GRANT 语法: GRANT privileges (columns) ON what TO user IDENTIFIED BY "password" WITH GRANT OPTION; （表示该用户可以为其他用户分配权限） privileges 列表:

ALTER: 修改表和索引。
CREATE: 创建数据库和表。
DELETE: 删除表中已有的记录。
DROP: 抛弃(删除)数据库和表。
INDEX: 创建或抛弃索引。
INSERT: 向表中插入新行。
REFERENCE:未使用。
SELECT: 检索表中的记录。 96/125
UPDATE: 修改现存表记录。
FILE: 读或写服务器上的文件。
PROCESS: 查看服务器中执行的线程信息或杀死线程。
RELOAD: 重载授权表或清空日志、主机缓存或表缓存。
SHUTDOWN: 关闭服务器。
ALL: 所有权限,ALL PRIVILEGES同义词。
USAGE: 特殊的 "无权限" 权限。 user 账户包括 "username" 和 "host" 两部分即是username@host,后者表示该用户被允许从何地接入。 user@'%' 表示用户user可以从任何地址访问本地的数据库,默认可以省略。还可以是 "user@10.250.7.%"、"user1@ %.abc.com" 等。数据库格式为 db.table,可以是 "test." 或 ".",前者表示 test 数据库的所有表,后者表示所有数据库的所有表。子句 "WITH GRANT OPTION" 表示该用户可以为其他用户分配权限。使用grant 命令创建用户或者进行授权之后，需要使用flush privileges刷新MySQL的系统权限相关表，否则会出现拒绝访问,或者重新启动mysql服务器，来使新设置生效。当然后者并不是一种好想法！比如: 一 grant普通数据用户yangql402查询、插入、更新、删除数据库(test)中所有表数据的权利。 grant select, insert, update, delete on test. to yangql402@'%'; 授权 show grants for yangql402@'%'; 查权二 grant数据库开发人员(yangql402)，创建表、索引、视图、存储过程、函数。。。等权限。 grant创建、修改、删除 MySQL 数据表结构权限。 grant create on test.* to yangql402@'10.250.7.225'; grant alter on test.* to yangql402@'10.250.7.225'; grant drop on test.* to yangql402@'10.250.7.225';

grant 操作 MySQL 外键权限，官方文档上说未使用！

grant references on test.* to yangql402@'10.250.7.225';

grant 操作 MySQL 临时表权限。 grant create temporary tables on test.* to yangql402@'10.250.7.225';

grant 操作 MySQL 索引权限。 grant index on test.* to yangql402@'10.250.7.225';

grant 操作 MySQL 视图、查看视图源代码权限。 grant create view on test.* to yangql402@'10.250.7.225';grant show view on test.* to yangql402@'10.250.7.225';

grant 操作 MySQL 存储过程、函数权限。 grant create routine on test.* to yangql402@'10.250.7.225'; grant alter routine on test.* to yangql402@'10.250.7.225'; grant execute on test.* to yangql402@'10.250.7.225'; 三 grant 普通DBA管理某个MySQL数据库(test)的权限。grant all privileges on test to dba@'localhost'其中，关键字 “privileges” 可以省略。四 grant 高级 DBA 管理 MySQL 中所有数据库的权限。grant all on . to dba@'localhost' 97/125 五 MySQL grant 权限，分别可以作用在多个层次上。 a. grant 作用在整个 MySQL 服务器上： grant select on . to dba@localhost; -- dba 可以查询 MySQL 中所有数据库中的表。 grant all on . to dba@localhost; -- dba 可以管理 MySQL 中的所有数据库 b. grant 作用在单个数据库上： grant select on test.* to dba@localhost; -- dba 可以查询 test 中的表。 c. grant 作用在单个数据表上： grant select, insert, update, delete on test.yql8 to dba@localhost; d. grant 作用在表中的列上： grant select(id, se, rank) on test.yql8 to dba@localhost; e. grant 作用在存储过程、函数上： grant execute on procedure test.yql8 to 'dba'@'localhost'; grant execute on function test.yql8 to 'dba'@'localhost'; 六查看用户权限查看当前用户自己的权限：show grants;查看其他 MySQL 用户权限：show grants for dba@localhost; 七撤销用户权限使用revoke 命令来注销用户的权限,具体语法：要撤销所有权限，需使用以下语法。此语法用于取消对于已命名的用户的所有全局层级、数据库层级、表层级和列层级的权限。REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...也可以指定具体的权限比如:REVOKE SELECT FROM yangql402@"10.250.7.249";注意:1 使用GRANT或REVOKE，操作者必须拥有GRANT OPTION权限，并且您必须用于您正在授予或撤销的权限。2 使用REVOKE撤销全部权限,操作者必须拥有mysql数据库的全局CREATE USER权限或UPDATE权限。八删除用户：DROP USER user;其中user 账户包括 "username" 和 "host" 两部分即是username@host; 如果创建的时候为 yangql@"10.250.7.225",则删除的时候必须使用drop user yangql@"10.250.7.225"，否则会报错！mysql> drop user yangql402;ERROR 1396 (HY000): Operation DROP USER failed for 'yangql402'@'10.250.7.225'mysql> drop user yangql402@'10.250.7.225';Query OK, 0 rows affected (0.01 sec) 参考; MYSQL 官方文档http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#accountmanagement-sql 98/125 权限权限级别权限说明 CREATE 数据库、表或索引创建数据库、表或索引权限 DROP 数据库或表删除数据库或表权限 GRANT OPTION 数据库、表或保存的程序赋予权限选项 REFERENCES 数据库或表 ALTER 表更改表，比如添加字段、索引等 DELETE 表删除数据权限 INDEX 表索引权限 INSERT 表插入权限 SELECT 表查询权限 UPDATE 表更新权限 CREATE VIEW 视图创建视图权限 SHOW VIEW 视图查看视图权限 ALTER ROUTINE 存储过程更改存储过程权限 CREATE ROUTINE 存储过程创建存储过程权限 EXECUTE 存储过程执行存储过程权限 FILE 服务器主机上的文件访问文件访问权限 CREATE TEMPORARY TABLES 服务器管理创建临时表权限 LOCK TABLES 服务器管理锁表权限 CREATE USER 服务器管理创建用户权限 PROCESS 服务器管理查看进程权限点击这里点击这里点击这里 99/125 RELOAD 服务器管理执行flush-hosts, flush-logs, flush-privileges, flush-status, flushtables, flush-threads, refresh, reload等命令的权限 REPLICATION CLIENT 服务器管理复制权限 REPLICATION SLAVE 服务器管理复制权限 SHOW DATABASES 服务器管理查看数据库权限 SHUTDOWN 服务器管理关闭数据库权限 SUPER 服务器管理执行kill线程权限点击这里点击这里点击这里 MYSQL的权限如何分布，就是针对表可以设置什么权限，针对列可以设置什么权限等等，这个可以从官方文档中的一个表来说明：点击这里点击这里权限分布可能的设置的权限表权限 'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter' 列权限 'Select', 'Insert', 'Update', 'References' 过程权限 'Execute', 'Alter Routine', 'Grant' 二、MySQL权限经验原则：权限控制主要是出于安全因素，因此需要遵循一下几个经验原则： 1、只授予能满足需要的最小权限，防止用户干坏事。比如用户只是需要查询，那就只给select权限就可以了，不要给用户赋予update、insert或者delete权限。 2、创建用户的时候限制用户的登录主机，一般是限制成指定IP或者内网IP段。 3、初始化数据库的时候删除没有密码的用户。安装完数据库的时候会自动创建一些用户，这些用户默认没有密码。 4、为每个用户设置满足密码复杂度的密码。 5、定期清理不需要的用户。回收权限或者删除用户。三、MySQL权限实战： 1、GRANT命令使用说明：先来看一个例子，创建一个只允许从本地登录的超级用户jack，并允许将权限赋予别的用户，密码为： jack. mysql> grant all privileges on . to jack@'localhost' identified by "jack" with grant option; Query OK, 0 rows affected (0.01 sec) GRANT命令说明： ALL PRIVILEGES 是表示所有权限，你也可以使用select、update等权限。 ON 用来指定权限针对哪些库和表。 . 中前面的号用来指定数据库名，后面的号用来指定表名。 TO 表示将权限赋予某个用户。 100/125 jack@'localhost' 表示jack用户，@后面接限制的主机，可以是IP、IP段、域名以及%，%表示任何地方。注意：这里%有的版本不包括本地，以前碰到过给某个用户设置了%允许任何地方登录，但是在本地登录不了，这个和版本有关系，遇到这个问题再加一个localhost的用户就可以了。 IDENTIFIED BY 指定用户的登录密码。 WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人。注意：经常有人在创建操作用户的时候不指定WITH GRANT OPTION选项导致后来该用户不能使用GRANT命令创建用户或者给其它用户授权。备注：可以使用GRANT重复给用户添加权限，权限叠加，比如你先给用户添加一个select权限，然后又给用户添加一个insert权限，那么该用户就同时拥有了select和insert权限。 2、刷新权限使用这个命令使权限生效，尤其是你对那些权限表user、db、host等做了update或者delete更新的时候。以前遇到过使用grant后权限没有更新的情况，只要对权限做了更改就使用FLUSH PRIVILEGES命令来刷新权限。 mysql> flush privileges; Query OK, 0 rows affected (0.01 sec) 3、查看权限查看当前用户的权限： mysql> show grants; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON . TO 'root'@'localhost' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+ 2 rows in set (0.00 sec) 查看某个用户的权限： mysql> show grants for 'jack'@'%'; +-----------------------------------------------------------------------------------------------------+ | Grants for jack@% | +-----------------------------------------------------------------------------------------------------+ | GRANT USAGE ON . TO 'jack'@'%' IDENTIFIED BY PASSWORD '9BCDC990E611B8D852EFAF1E3919AB6AC8C8A9F0' | +-----------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 4、回收权限 mysql> revoke delete on . from 'jack'@'localhost'; Query OK, 0 rows affected (0.01 sec) 5、删除用户 mysql> select host,user,password from user; +-----------+------+-------------------------------------------+ | host | user | password | +-----------+------+-------------------------------------------+ | localhost | root | | | rhel5.4 | root | | | 127.0.0.1 | root | | | ::1 | root | | | localhost | | | | rhel5.4 | | | | localhost | jack | 9BCDC990E611B8D852EFAF1E3919AB6AC8C8A9F0 | +-----------+------+-------------------------------------------+ 7 rows in set (0.00 sec) mysql> drop user 'jack'@'localhost'; Query OK, 0 rows affected (0.01 sec) 6、对账户重命名 101/125 mysql> rename user 'jack'@'%' to 'jim'@'%'; Query OK, 0 rows affected (0.00 sec) 7、修改密码 1、用set password命令 mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456'); Query OK, 0 rows affected (0.00 sec) 2、用mysqladmin [root@rhel5 ~]# mysqladmin -uroot -p123456 password 1234abcd 备注：格式：mysqladmin -u用户名 -p旧密码 password 新密码 3、用update直接编辑user表 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> update user set PASSWORD = PASSWORD('1234abcd') where user = 'root'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) 4、在丢失root密码的时候： [root@rhel5 ~]# mysqld_safe --skip-grant-tables & [1] 15953 [root@rhel5 ~]# 130911 09:35:33 mysqld_safe Logging to '/mysql/mysql5.5/data/rhel5.4.err'. 130911 09:35:33 mysqld_safe Starting mysqld daemon with databases from /mysql/mysql5.5/data [root@rhel5 ~]# mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.22 Source distribution Copyright (c) 2000, 2011, 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> \s

mysql Ver 14.14 Distrib 5.5.22, for Linux (i686) using EditLine wrapper Connection id: 2 Current database: Current user: root@ SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.5.22 Source distribution Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 102/125 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql.sock Uptime: 36 sec Threads: 1 Questions: 5 Slow queries: 0 Opens: 23 Flush tables: 1 Open tables: 18 Queries per second avg: 0.138

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> update user set password = PASSWORD('123456') where user = 'root'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) Mysql函数一、数学函数 ABS(x) 返回x的绝对值。例: select ABS(-15); BIN(x) 返回x的二进制（OCT返回八进制，HEX返回十六进制）.例select BIN(8); CEILING(x) 返回大于x的最小整数值 EXP(x) 返回值e（自然对数的底）的x次方 FLOOR(x) 返回小于x的最大整数值 GREATEST(x1,x2,...,xn)返回集合中最大的值 LEAST(x1,x2,...,xn) 返回集合中最小的值 LN(x) 返回x的自然对数 LOG(x,y)返回x的以y为底的对数 MOD(x,y) 返回x/y的模（余数） PI()返回pi的值（圆周率）。例: select PI(); RAND()返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。 ROUND(x,y)返回参数x的四舍五入的有y位小数的值 SIGN(x) 返回代表数字x的符号的值 SQRT(x) 返回一个数的平方根 TRUNCATE(x,y) 返回数字x截短为y位小数的结果 2的幂： 7 6 5 4 3 2 1 0 十进制：128 64 32 16 8 4 2 1 二、聚合函数(常用于GROUP BY从句的SELECT查询中) AVG(col)返回指定列的平均值 COUNT(col)返回指定列中非NULL值的个数 MIN(col)返回指定列的最小值 MAX(col)返回指定列的最大值 103/125 SUM(col)返回指定列的所有值之和 GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果三、字符串函数 ASCII(char)返回字符的ASCII码值 BIT_LENGTH(str)返回字符串的比特长度 CONCAT(s1,s2...,sn)将s1,s2...,sn连接成字符串 CONCAT_WS(sep,s1,s2...,sn)将s1,s2...,sn连接成字符串，并用sep字符间隔 INSERT(str,x,y,instr) 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果 FIND_IN_SET(str,list)分析逗号分隔的list列表，如果发现str，返回str在list中的位置 LCASE(str)或LOWER(str) 返回将字符串str中所有字符改变为小写后的结果 LEFT(str,x)返回字符串str中最左边的x个字符 LENGTH(s)返回字符串str中的字符数 LTRIM(str) 从字符串str中切掉开头的空格 POSITION(substr,str) 返回子串substr在字符串str中第一次出现的位置 QUOTE(str) 用反斜杠转义str中的单引号 REPEAT(str,srchstr,rplcstr)返回字符串str重复x次的结果 REVERSE(str) 返回颠倒字符串str的结果 RIGHT(str,x) 返回字符串str中最右边的x个字符 RTRIM(str) 返回字符串str尾部的空格 STRCMP(s1,s2)比较字符串s1和s2 TRIM(str)去除字符串首部和尾部的所有空格 UCASE(str)或UPPER(str) 返回将字符串str中所有字符转变为大写后的结果四、日期和时间函数 CURDATE()或CURRENT_DATE() 返回当前的日期 CURTIME()或CURRENT_TIME() 返回当前的时间 DATE_ADD(date,INTERVAL int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH); DATE_FORMAT(date,fmt) 依照指定的fmt格式格式化日期date值 DATE_SUB(date,INTERVAL int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH); DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7) DAYOFMONTH(date) 返回date是一个月的第几天(1~31) DAYOFYEAR(date) 返回date是一年的第几天(1~366) DAYNAME(date) 返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE); FROM_UNIXTIME(ts,fmt) 根据指定的fmt格式，格式化UNIX时间戳ts HOUR(time) 返回time的小时值(0~23) MINUTE(time) 返回time的分钟值(0~59) MONTH(date) 返回date的月份值(1~12) MONTHNAME(date) 返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE); NOW() 返回当前的日期和时间 QUARTER(date) 返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE); WEEK(date) 返回日期date为一年中第几周(0~53) YEAR(date) 返回日期date的年份(1000~9999) 一些示例：获取当前系统时间：SELECT FROM_UNIXTIME(UNIX_TIMESTAMP()); SELECT EXTRACT(YEAR_MONTH FROM CURRENT_DATE); SELECT EXTRACT(DAY_SECOND FROM CURRENT_DATE); SELECT EXTRACT(HOUR_MINUTE FROM CURRENT_DATE); 104/125 返回两个日期值之间的差值(月数)：SELECT PERIOD_DIFF(200302,199802); 在Mysql中计算年龄： SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthday)),'%Y')+0 AS age FROM employee; 这样，如果Brithday是未来的年月日的话，计算结果为0。下面的SQL语句计算员工的绝对年龄，即当Birthday是未来的日期时，将得到负值。 SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birthday, '%Y') -(DATE_FORMAT(NOW(), '00-%m-%d') <DATE_FORMAT(birthday, '00-%m-%d')) AS age from employee 五、加密函数 AES_ENCRYPT(str,key) 返回用密钥key对字符串str利用高级加密标准算法加密后的结果，调用 AES_ENCRYPT的结果是一个二进制字符串，以BLOB类型存储 AES_DECRYPT(str,key) 返回用密钥key对字符串str利用高级加密标准算法解密后的结果 DECODE(str,key) 使用key作为密钥解密加密字符串str ENCRYPT(str,salt) 使用UNIXcrypt()函数，用关键词salt(一个可以惟一确定口令的字符串，就像钥匙一样 )加密字符串str ENCODE(str,key) 使用key作为密钥加密字符串str，调用ENCODE()的结果是一个二进制字符串，它以 BLOB类型存储 MD5() 计算字符串str的MD5校验和 PASSWORD(str) 返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。 SHA() 计算字符串str的安全散列算法(SHA)校验和示例： SELECT ENCRYPT('root','salt'); SELECT ENCODE('xufeng','key'); SELECT DECODE(ENCODE('xufeng','key'),'key');#加解密放在一起 SELECT AES_ENCRYPT('root','key'); SELECT AES_DECRYPT(AES_ENCRYPT('root','key'),'key'); SELECT MD5('123456'); SELECT SHA('123456'); 六、控制流函数 MySQL有4个函数是用来进行条件操作的，这些函数可以实现SQL的条件逻辑，允许开发者将一些应用程序业务逻辑转换到数据库后台。 MySQL控制流函数： CASE WHEN[test1] THEN [result1]...ELSE [default] END如果testN是真，则返回resultN，否则返回 default CASE [test] WHEN[val1] THEN [result]...ELSE [default]END 如果test和valN相等，则返回resultN，否则返回default IF(test,t,f) 如果test是真，返回t；否则返回f IFNULL(arg1,arg2) 如果arg1不是空，返回arg1，否则返回arg2 NULLIF(arg1,arg2) 如果arg1=arg2返回NULL；否则返回arg1 这些函数的第一个是IFNULL()，它有两个参数，并且对第一个参数进行判断。如果第一个参数不是NULL，函数就会向调用者返回第一个参数；如果是NULL,将返回第二个参数。如：SELECT IFNULL(1,2), IFNULL(NULL,10),IFNULL(4NULL,'false'); NULLIF()函数将会检验提供的两个参数是否相等，如果相等，则返回NULL，如果不相等，就返回第一个参数。如：SELECT NULLIF(1,1),NULLIF('A','B'),NULLIF(2+3,4+1); 和许多脚本语言提供的IF()函数一样，MySQL的IF()函数也可以建立一个简单的条件测试，这个函数有三个参数，第一个是要被判断的表达式，如果表达式为真，IF()将会返回第二个参数，如果为假，IF()将会返回第三个参数。 105/125 如：SELECTIF(1<10,2,3),IF(56>100,'true','false'); IF()函数在只有两种可能结果时才适合使用。然而，在现实世界中，我们可能发现在条件测试中会需要多个分支。在这种情况下，MySQL提供了CASE函数，它和PHP及Perl语言的switch-case条件例程一样。 CASE函数的格式有些复杂，通常如下所示： CASE [expression to be evaluated] WHEN [val 1] THEN [result 1] WHEN [val 2] THEN [result 2] WHEN [val 3] THEN [result 3] ...... WHEN [val n] THEN [result n] ELSE [default result] END 这里，第一个参数是要被判断的值或表达式，接下来的是一系列的WHEN-THEN块，每一块的第一个参数指定要比较的值，如果为真，就返回结果。所有的WHEN-THEN块将以ELSE块结束，当END结束了所有外部的CASE块时，如果前面的每一个块都不匹配就会返回ELSE块指定的默认结果。如果没有指定ELSE块，而且所有的WHEN-THEN比较都不是真，MySQL将会返回NULL。 CASE函数还有另外一种句法，有时使用起来非常方便，如下： CASE WHEN [conditional test 1] THEN [result 1] WHEN [conditional test 2] THEN [result 2] ELSE [default result] END 这种条件下，返回的结果取决于相应的条件测试是否为真。示例： mysql>SELECT CASE 'green' WHEN 'red' THEN 'stop' WHEN 'green' THEN 'go' END; SELECT CASE 9 WHEN 1 THEN 'a' WHEN 2 THEN 'b' ELSE 'N/A' END; SELECT CASE WHEN (2+2)=4 THEN 'OK' WHEN(2+2)<>4 THEN 'not OK' END ASSTATUS; SELECT Name,IF((IsActive = 1),'已激活','未激活') AS RESULT FROMUserLoginInfo; SELECT fname,lname,(math+sci+lit) AS total, CASE WHEN (math+sci+lit) < 50 THEN 'D' WHEN (math+sci+lit) BETWEEN 50 AND 150 THEN 'C' WHEN (math+sci+lit) BETWEEN 151 AND 250 THEN 'B' ELSE 'A' END AS grade FROM marks; SELECT IF(ENCRYPT('sue','ts')=upass,'allow','deny') AS LoginResultFROM users WHERE uname = 'sue';#一个登陆验证七、格式化函数 DATE_FORMAT(date,fmt) 依照字符串fmt格式化日期date值 FORMAT(x,y) 把x格式化为以逗号隔开的数字序列，y是结果的小数位数 INET_ATON(ip) 返回IP地址的数字表示 INET_NTOA(num) 返回数字所代表的IP地址 TIME_FORMAT(time,fmt) 依照字符串fmt格式化时间time值其中最简单的是FORMAT()函数，它可以把大的数值格式化为以逗号间隔的易读的序列。示例： SELECT FORMAT(34234.34323432,3); SELECT DATE_FORMAT(NOW(),'%W,%D %M %Y %r'); SELECT DATE_FORMAT(NOW(),'%Y-%m-%d'); SELECT DATE_FORMAT(19990330,'%Y-%m-%d'); SELECT DATE_FORMAT(NOW(),'%h:%i %p'); SELECT INET_ATON('10.122.89.47'); SELECT INET_NTOA(175790383); 106/125 八、类型转化函数为了进行数据类型转化，MySQL提供了CAST()函数，它可以把一个值转化为指定的数据类型。类型有： BINARY,CHAR,DATE,TIME,DATETIME,SIGNED,UNSIGNED 示例： SELECT CAST(NOW() AS SIGNED INTEGER),CURDATE()+0; SELECT 'f'=BINARY 'F','f'=CAST('F' AS BINARY); 九、系统信息函数 DATABASE() 返回当前数据库名 BENCHMARK(count,expr) 将表达式expr重复运行count次 CONNECTION_ID() 返回当前客户的连接ID FOUND_ROWS() 返回最后一个SELECT查询进行检索的总行数 USER()或SYSTEM_USER() 返回当前登陆用户名 VERSION() 返回MySQL服务器的版本示例： SELECT DATABASE(),VERSION(),USER(); SELECTBENCHMARK(9999999,LOG(RAND()PI()));#该例中,MySQL计算LOG(RAND()*PI())表达式 9999999次。 Mysql存储引擎存储引擎在mysql中配置有许多不同的存储引擎，用户可以灵活的选择适用于服务器、数据库和表格的存储引擎，以便获得最大的性能和最大的灵活性。这也是 mysql不同于其他大型数据库以及mysql为何如此受欢迎的主要因素。一些大型的数据库仅采用了一种存储引擎，以一种尺码满足所有需求也就意味着会牺牲一些性能。存储引擎的作用：决定数据的存储和读取的处理方式。 107/125 在命令行中可以通过命令显示其支持的存储引擎： (1)MyISAM： Mysql的默认数据库，最为常用。拥有较高的插入，查询速度，但不支持事务 .frm: 存储表结构 .myd: 存储数据，MYData的缩写 .myi: 存储索引，MYIndex的缩写 (2)InnoDB ：事务型数据库的首选引擎，支持ACID事务，支持行级锁定 (3)BDB：源自Berkeley DB，事务型数据库的另一种选择，支持COMMIT和ROLLBACK等其他事务特性 (4)Memory ：所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在Mysql重新启动时丢失 (5)Merge：将一定数量的MyISAM表联合而成一个整体，在超大规模数据存储时很有用 (6)Archive ：非常适合存储大量的独立的，作为历史记录的数据。因为它们不经常被读取。Archive拥有高效的插入速度，但其对查询的支持相对较差 (7)Federated：将不同的Mysql服务器联合起来，逻辑上组成一个完整的数据库。非常适合分布式应用 Cluster/NDB ：高冗余的存储引擎，用多台数据机器联合提供服务以提高整体性能和安全性。适合数据量大，安全和性能要求高的应用 (8)CSV：逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个.CSV文件。这是一种普通文本文件，每个数据行占用一个文本行。CSV存储引擎不支持索引。 (9)BlackHole：黑洞引擎，写入的任何数据都会消失，一般用于记录binlog做复制的中继每种存储引擎都有各自的优势，不能笼统的说谁的性能更好，只有合适不合适。（1）MyISAM存储引擎：主要用于管理非事物表，它提供了高速的存储与检索，以及全文搜索能力。该存储引擎插入数据快，但是空间和内存的使用效率较低。如果表主要适用于插入新纪录和读出记录，那么选择MyISAM存储引擎可以实现处理的高效率。（2）InnoDB存储引擎：它主要用于事务处理应用程序，支持外键，同时还支持奔溃修复能力和并发控制。如果对事物的完整性要求比较高，要求实现并发通知，那么选择InnoDB存储引擎比较有优势。如果需要频繁的进行更新、删除操作，也可以选择该存储引擎，因为该存储引擎可以实现事物的提交和回滚。（3）Memory存储引擎：Memory存储引擎提供“内存中”表，该存储引擎的所有数据都存储在内存中，数据的处理速度很快但是安全性不高。

108/125 存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）在Oracle 和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。存储引擎存储引擎：不同的存储引擎可以给数据库带来不同的功能和性能。存储引擎是针对表的设置。 109/125 行锁：A用户操作表中的第3行数据时，B用户就不能操作这行数据。表锁：A用户操作表1时，B用户就不能操作表1。 B TREE树状算法(了解)：https://blog.csdn.net/chuixue24/article/details/80027689 选择存储引擎

查看存储引擎 SHOW ENGINES; SHOW ENGINES\G 查看MYSQL支持的存储引擎 SHOW VARIABLES LIKE 'storage_engine%'; 查看当前的存储引擎 SHOW VARIABLES LIKE 'auto_inc%'; 查看自增长的设置状态 show global variables like '%connet%' 查看connet环境变量设置 mysql> show variables\G 查看所有的环境变量 show variables当前的会话 show global variables\G全局
选择存储引擎方法1. mysql> create table innodb1( -> id int -> )engine=innodb; mysql> show create table innodb1; create table test100(id init)engine=innodb; 方法2. /etc/my.cnf [mysqld] 在此行下添加下面的一行内容 default-storage-engine=INNODB MySQL常用的存储引擎 MyISAM存储引擎 110/125 由于该存储引擎不支持事务、也不支持外键，所以访问速度较快。因此当对事务完整性没有要求并以访问为主的应用适合使用该存储引擎。 InnoDB存储引擎（MYSQL默认用此存储引擎）由于该存储引擎在事务上具有优势，即支持具有提交、回滚及崩溃恢复能力等事务特性，所以比MyISAM存储引擎占用更多的磁盘空间。因此当需要频繁的更新、删除操作，同时还对事务的完整性要求较高，需要实现并发控制，建议选择。 MEMORY MEMORY存储引擎存储数据的位置是内存，因此访问速度最快，但是安全上没有保障。适合于需要快速的访问或临时表。 BLACKHOLE 黑洞存储引擎，可以应用于主备复制中的分发主库。使用BLACKHOLE存储引擎的表不存储任何数据，但如果mysql启用了二进制日志，SQL语句被写入日志（并被复制到从服务器）。这样使用BLACKHOLE存储引擎的mysqld可以作为主从复制中的中继重复器或在其上面添加过滤器机制。练习：
建立四张表，存储引擎分别为MyISAM、InnoDB、MEMORY、BLACKHOLE。
通过show create table 表名查看表的创建过程
插入数据（插入前要查看表的结构） create database save; use save; show engines; create table isam1(id int,name varchar(20))engine=myisam; create table inno1(id int,name varchar(20))engine=innodb; create table mem1(id int,name varchar(20))engine=memory; create table blk1(id int,name varchar(20))engine=blackhole; show create table isam1; show create table inno1; show create table mem1; show create table blk1; desc isam1; insert into isam1(id,name)values(1,'jim'),(2,'tom'); ...... 警告：当创建表时指定的存储引擎不存在，系统会使用默认的存储引擎。警告：只有innodb存储引擎支持外键功能。外键：一个表的数据依赖于另一张表的主键列的数据，如果在主键列没有出现的值，是不能够出现在外键字段的。通俗理解：B表(子表)中的某列数据(外键)的值必须是A表(父表)中某列(主键)中的值。外键解决的问题：1、将多张表通过外键联系起来。 2、减少数据的冗余。 111/125 Mysql字符集最近，在项目组使用的mysql数据库中，插入数据出现乱码，关于这个问题做了下总结，我们从最基本的地方说起，到错误产生的深层次原因和解决办法。基本概念 • 字符(Character)是指人类语言中最小的表义符号。例如’A'、’B'等； • 给定一系列字符，对每个字符赋予一个数值，用数值来代表对应的字符，这一数值就是字符的编码 (Encoding)。例如，我们给字符’A'赋予数值0，给字符’B'赋予数值1，则0就是字符’A'的编码； • 给定一系列字符并赋予对应的编码后，所有这些字符和编码对组成的集合就是字符集(Character Set)。例如，给定字符列表为{’A',’B'}时，{’A'=>0, ‘B’=>1}就是一个字符集； • 字符序(Collation)是指在同一字符集内字符之间的比较规则； • 确定字符序后，才能在一个字符集上定义什么是等价的字符，以及字符之间的大小关系； • 每个字符序唯一对应一种字符集，但一个字符集可以对应多种字符序，其中有一个是默认字符序(Default Collation)； • MySQL中的字符序名称遵从命名惯例：以字符序对应的字符集名称开头；以_ci(表示大小写不敏感)、_cs (表示大小写敏感)或_bin(表示按编码值比较)结尾。例如：在字符序“utf8_general_ci”下，字符“a”和“A”是等价的； MySQL字符集设置 • 系统变量： – character_set_server：默认的内部操作字符集 – character_set_client：客户端来源数据使用的字符集 – character_set_connection：连接层字符集 – character_set_results：查询结果字符集 – character_set_database：当前选中数据库的默认字符集 – character_set_system：系统元数据(字段名等)字符集 – 还有以collation_开头的同上面对应的变量，用来描述字符序。 • 用introducer指定文本字符串的字符集： – 格式为：[_charset] ’string’ [COLLATE collation] – 例如： SELECT _latin1 ’string’; SELECT _utf8 ‘你好’ COLLATE utf8_general_ci; – 由introducer修饰的文本字符串在请求过程中不经过多余的转码，直接转换为内部字符集处理。 MySQL中的字符集转换过程
MySQL Server收到请求时将请求数据从character_set_client转换为character_set_connection；
进行内部操作前将请求数据从character_set_connection转换为内部操作字符集，其确定方法如下：

使用每个数据字段的CHARACTER SET设定值；
若上述值不存在，则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩展，非SQL标准)；
若上述值不存在，则使用对应数据库的DEFAULT CHARACTER SET设定值；
若上述值不存在，则使用character_set_server设定值。

将操作结果从内部操作字符集转换为character_set_results。 112/125 我们现在回过头来分析下我们产生的乱码问题： a 我们的字段没有设置字符集，因此使用表的数据集 b 我们的表没有指定字符集，默认使用数据库存的字符集 c 我们的数据库在创建的时候没有指定字符集，因此使用character_set_server设定值 d 我们没有特意去修改character_set_server的指定字符集，因此使用mysql默认 e mysql默认的字符集是latin1，因此，我们使用了latin1字符集，而我们character_set_connection 的字符集是UTF-8，插入中文乱码也再所难免了。常见问题解析 • FAQ-1 向默认字符集为utf8的数据表插入utf8编码的数据前没有设置连接字符集，查询时设置连接字符集为utf8 – 插入时根据MySQL服务器的默认设置，character_set_client、character_set_connection和 character_set_results均为latin1； – 插入操作的数据将经过latin1=>latin1=>utf8的字符集转换过程，这一过程中每个插入的汉字都会从原始的3个字节变成6个字节保存； – 查询时的结果将经过utf8=>utf8的字符集转换过程，将保存的6个字节原封不动返回，产生乱码。参考下图： • 向默认字符集为latin1的数据表插入utf8编码的数据前设置了连接字符集为utf8（我们遇到的错误就是属于这一种） – 插入时根据连接字符集设置，character_set_client、character_set_connection和 character_set_results均为utf8； --插入数据将经过utf8=>utf8=>latin1的字符集转换，若原始数据中含有\u0000~\u00ff范围以外的 Unicode字符，会因为无法在latin1字符集中表示而被转换为“?”(0×3F)符号，以后查询时不管连接字符集设置如何都无法恢复其内容了。转换过程如下图：检测字符集问题的一些手段 • SHOW CHARACTER SET; • SHOW COLLATION; • SHOW VARIABLES LIKE ‘character%’; • SHOW VARIABLES LIKE ‘collation%’; • SQL函数HEX、LENGTH、CHAR_LENGTH • SQL函数CHARSET、COLLATION 使用MySQL字符集时的建议 • 建立数据库/表和进行数据库操作时尽量显式指出使用的字符集，而不是依赖于MySQL的默认设置，否则MySQL升级时可能带来很大困扰； • 数据库和连接字符集都使用latin1时，虽然大部分情况下都可以解决乱码问题，但缺点是无法以字符为单位来进行SQL操作，一般情况下将数据库和连接字符集都置为utf8是较好的选择； • 使用mysql CAPI（mysql提供C语言操作的API）时，初始化数据库句柄后马上用mysql_options设定 MYSQL_SET_CHARSET_NAME属性为utf8，这样就不用显式地用SET NAMES语句指定连接字符集，且用 mysql_ping重连断开的长连接时也会把连接字符集重置为utf8； • 对于mysql PHP API，一般页面级的PHP程序总运行时间较短，在连接到数据库以后显式用SET NAMES语句设置一次连接字符集即可；但当使用长连接时，请注意保持连接通畅并在断开重连后用SET NAMES语句显式重置连接字符集。其他注意事项 • my.cnf中的default_character_set设置只影响mysql命令连接服务器时的连接字符集，不会对使用 libmysqlclient库的应用程序产生任何作用！ • 对字段进行的SQL函数操作通常都是以内部操作字符集进行的，不受连接字符集设置的影响。 • SQL语句中的裸字符串会受到连接字符集或introducer设置的影响，对于比较之类的操作可能产生完全 113/125 不同的结果，需要小心！总结根据上面的分析和建议，我们解决我们遇到问题应该使用什么方法大家心里应该比较清楚了。对，就是在创建database的时候指定字符集，不要去通过修改默认配置来达到目的，当然你也可以采用指定表的字符集的形式，但很容易出现遗漏，特别是在很多人都参与设计的时候，更容易纰漏。虽然不提倡通过修改mysql的默认字符集来解决，但对于如何去修改默认字符集，我这里还是给出一些方法，仅供大家参考。 MySQL默认字符集 MySQL对于字符集的指定可以细化到一个数据库，一张表，一列.传统的程序在创建数据库和数据表时并没有使用那么复杂的配置，它们用的是默认的配置. (1)编译MySQL 时，指定了一个默认的字符集，这个字符集是 latin1； (2)安装MySQL 时，可以在配置文件 (my.ini) 中指定一个默认的的字符集，如果没指定，这个值继承自编译时指定的； (3)启动mysqld 时，可以在命令行参数中指定一个默认的的字符集，如果没指定，这个值继承自配置文件中的配置,此时 character_set_server 被设定为这个默认的字符集； (4)安装 MySQL选择多语言支持，安装程序会自动在配置文件中把default_character_set 设置为 UTF-8，保证缺省情况下所有的数据库所有表的所有列的都用 UTF-8 存储。查看默认字符集 (默认情况下，mysql的字符集是latin1(ISO_8859_1)，如何查看在上面我们已经给出了相关命令修改默认字符集 (1) 最简单的修改方法，就是修改mysql的my.ini或/etc/my.cnf文件中的字符集键值，如 default-character-set = utf8 character_set_server = utf8 修改完后，重启mysql的服务 (2) 还有一种修改字符集的方法，就是使用mysql的命令 mysql> SET character_set_client = utf8 ; mysql> SET character_set_connection = utf8 ; mysql> SET character_set_database = utf8 ; mysql> SET character_set_results = utf8 ; mysql> SET character_set_server = utf8 ; mysql> SET collation_connection = utf8 ; mysql> SET collation_database = utf8 ; mysql> SET collation_server = utf8 ; 设置了表的默认字符集为utf8并且通过UTF-8编码发送查询，存入数据库的仍然是乱码。那connection 连接层上可能出了问题。解决方法是在发送查询前执行一下下面这句： SET NAMES 'utf8';它相当于下面的三句指令： SET character_set_client = utf8; SET character_set_results = utf8; SET character_set_connection = utf8; 技术回顾 Q：mysql的root密码忘了，如何处理？ A：首先，修改/etc/my.cnf主配置文件。 vim /etc/my.cnf 114/125 [mysqld] skip-grant-table 跳过权限表验证(添加此行) 然后，重启mariadb服务：systemctl restart mariadb 然后，在mysql-server服务器本地免密码登录,做更新root密码的操作。 mysql update mysql.user set password=password("127") where user='root' and host='localhost'; flush privileges; exit 然后,，修改/etc/my.cnf主配置文件。 vim /etc/my.cnf [mysqld] #skip-grant-table 注释或删除此行然后，重启mariadb服务：systemctl restart mariadb 然后，在mysql-server服务器本地使用root账号和新密码登录。 mysql -uroot -p127 登录成功，就说明密码重设成功 Q：mysql的库文件、表文件默认的保存路径是？分别是以什么类型的文件存储在硬盘上？ A：保存路径/var/lib/mysql 库文件类型：以目录的形式存储在/var/lib/mysql目录中表文件：以普通文件的形式存储字库的目录中 Q：SQL语言的三种类别的语句？ A：DDL 数据定义语言，如库、表的创建、删除、存储等 DML 数据操纵语言，如？ DCL 数据控制语言，如？ Q：库、表的增、删、改、查命令？数据记录的增、删、改、查命令？ A：库(database)的操作查：show databases; 增(创建)：create database 库名选项; 删：drop database 库名; 表(table)的操作查：show tables; 增(创建)：create table 表名(字段名1 数据类型,字段2 数据类型,...)engine=innodb 编码设置; 查表结构：desc 表名; 删：drop table 表名; 显示修改表的命令帮助：help alter table;(插入或删除一列，修改某列的属性[即字段名和数据类型]) 在表中最后新增指定的列字段：alter table 表名 add 字段名数据类型; 在表中第一列或字段名A列之后新增指定的列字段：alter table 表名 add 字段名数据类型 [first| after 字段名A]; 删除表中的某列字段：alter table 表名 drop 字段名; 修改某列的属性[即字段名和数据类型]) 修改某列的数据类型：alter table 表名 modify 字段名新数据类型 [first| after 字段名A];; 修改某列的字段名和数据类型：alter table 表名 change 旧字段名新字段名新数据类型 [first| after 字段名A];; 115/125 记录(record)的操作(插入一行)：查：select * from 表名 [where 条件] 增(插入)：insert into 表名[(字段1,字段2,...)] values(值1,值2,...),(值1,值2,...); 更新：update [库名.]表名 set 字段名='新值' [where 条件]; 删：delete from [库名.]表名 [where 条件]; mysql -uroot -p456 use test; create table tba(id int,name char(15)); desc tba; help alter table; alter table tba add sex char(25); desc tba; alter table tba add xxx int first; desc tba; alter table tba change xxx num char(30) after sex; desc tba; alter table tba modify sex char(50) after num; desc tba; alter table tba drop num; desc tba; Q：如何在select查询时对表中的某列数据进行排序(升序、降序)？ A：用order by 字段名 [asc升序|desc降序] 练习：在test库中创建tbx表，在tbx表中插入4条记录，分别按常规、降序、升序查询表中的数据。 use test; create table tbx(id int not null auto_increment primary key,name char(10))engine=innodb CHARACTER SET utf8; #在tbx表中插入4行记录。 insert into tbx(name) values ('tom'), ('jack'), ('lucy'), ('alice'); #常规、降序、升序查询表中的数据。[order by 字段名相当于excel中的"排序"功能] select * from tbx; select * from tbx order by id desc; 查询tbx表中的全部记录并且按id字段排降序 select * from tbx order by name asc; 查询tbx表中的全部记录并且按name字段排升序 #按指定的条件查询表中的数据记录。[where 条件相当于excel中的"筛选"功能] select * from tbx where id=3; select * from tbx where id>=3; select * from tbx where id<=3; select * from tbx where id<3; select * from tbx where name='jack'; select * from tbx where id=1 or id=3; select * from tbx where id between 2 and 7; 查询tbx表中id字段的值等于2~7之间的记录 select * from tbx where id>=2 and id<=7; select * from tbx where id!=2; 查询tbx表中id字段的值不等于2的记录 select * from tbx where not id=2; 查询tbx表中id字段的值不等于2的记录 select * from tby where id in (1,3,6,7); 查询tbx表中id字段的值为1、3、6、7的记录 select * from tby where not id in (1,3,6,7); 查询tbx表中id字段的值不等于1、3、6、7的记录 116/125 #在tbx表中插入5行记录。 insert into tbx(name) values ('周涛'), ('周子皓'), ('周杰伦'), ('李振'), ('李小龙'); #按指定的条件查询表中的数据记录。[在where中用like做值的模糊匹配,匹配任意单个字符,%匹配任意一串字符] select * from tbx where name like '周%'; 查询tby表中name字段的值以周开头的所有记录 select * from tbx where name like '周_'; 查询tby表中name字段的值以周开头且三个字的所有记录 select * from tbx where name like '%龙'; 查询tbx表中name字段的值以龙字结尾的信息的所有记录 #mysql库和表的逻辑备份工具mysqldump Q：什么是逻辑备份？什么是物理备份？ A：逻辑备份就是将数据中的表备份成create table、insert into等表和数据记录的SQL语句。物理备份就是就是将数据库、表的源文件(/var/lib/mysql/库名目录)复制或tar打包压缩一份到别的目录中。

物理备份的模式：冷备份：首先，停止数据库的服务，然后用cp、tar命令对数据库文件进行备份。热备份：直接在数据库服务运行的状态下做备份，需要用mysql的第三方备份和恢复工具进行操作。单库全表备份：mysqldump -uroot -p'密码' 库名 > 备份文件名.sql 例：mysqldump -uroot -p456 test > test_all.sql more test_all.sql 会发现备份文件中主要是create table、insert into等SQL语句单库全表恢复：首先，创建一个空库：mysql -uroot -p456 -e 'create database 库名;show databases;' 例：mysql -uroot -p456 -e 'create database test_all;show databases;' 然后，恢复所有表到库中：mysql -uroot -p456 库名 < 备份文件名.sql 例：mysql -uroot -p456 test_all < test_all.sql mysql -uroot -p456 -e 'use test_all;show tables;' 单库单表备份：mysqldump -uroot -p'密码' 库名表名 > 备份文件名.sql 例：mysqldump -uroot -p456 test tby > tby.sql more tby.sql 会发现备份文件中主要是create table、insert into等SQL语句单库单表恢复：首先，创建一个空库：mysql -uroot -p456 -e 'create database 库名;show databases;' 例：mysql -uroot -p456 -e 'create database ku_tby;show databases;' 然后，恢复tby表到ku_tby库中：mysql -uroot -p456 库名 < 备份文件名.sql 例：mysql -uroot -p456 ku_tby < tby.sql mysql -uroot -p456 -e 'use ku_tby;show tables;select * from tby;'

物理备份的模式：冷备份：首先，停止数据库的服务，然后用cp、tar命令对数据库文件进行备份。 117/125 热备份：直接在数据库服务运行的状态下做备份，需要用mysql的第三方备份和恢复工具进行操作。练习：将192.168.11.11主机上的mysql的所有库做冷备份，备份到/opt/mysql-2019-03-07目录中。 grep 'datadir' /etc/my.cnf 查看mysql主配置文件中的数据库文件存储路径 mkdir -pv /opt/data/mysql-$(date +%F) systemctl stop mariadb && systemctl status mariadb cp -arv /var/lib/mysql/* /opt/data/mysql-$(date +%F) #删除mysql的所有库和表，重启服务后，查库、查表，发现test库中没有表。() rm -rfv /var/lib/mysql/* systemctl restart mariadb mysql -uroot -e 'show databases;use test;show tables;' #冷恢复数据库、表。 systemctl stop mariadb \cp -afrv /opt/data/mysql-$(date +%F)/* /var/lib/mysql/ chown -Rv mysql:mysql /var/lib/mysql/* systemctl restart mariadb mysql -uroot -p456 -e 'show databases;use test;show tables;'

技术回顾：所讲的技术内容：触发器、视图、存储过程、索引、多表查询(连接查询、子查询……) Q.触发器(trigger)的作用是什么？ A.对A表做增insert、删delete、改update操作，B表自动会执行某个SQL操作。 Q.视图(view)的本质作用是什么？ A.视图其实就是将一条SQL的select查询语句取个名字保存起来，当用select语句查询这个视图时，数据库底层就是执行视图中所保存的select语句。可以用视图来提高数据库的安全性。 Q.存储过程(procedure)的本质作用是什么？ A.定义数据存储的过程。存储过程中允许对库、表、记录做增、删、改、查等操作。 Q.索引(index)为什么可以提高数据查询速度(效率高)？索引的类别有哪些？ A.索引底层的工作是会将表中的数据记录按一定算法(规律)进行分组，用户在查数据时会自动到所对应的组中去查，这样就缩小了查询范围，所以提高了效率。索引的类别：普通索引唯一索引全文索引单列索引多列索引空间索引 Q.什么是子查询(sub select)？ A.将一个select查询语句的结果作为另一个select查询语句的条件。例：select * from A表 where id in (select id from B表 where id=3) Q.连接查询(笛卡尔集[交叉连接]、左连接、右连接、等值连接)？ 118/125 A.在下方写出连接查询相关的命令：红帽认证的考试全部是上机考试，当场考完后考试系统会自动通过脚本出成绩结果。 RHCSA ： RedHat Certified system administrator红帽认证系统管理员。需要熟练掌握linux的系统安装、文件和目录管理、用户管理、权限、vim编辑器、磁盘存储、进程、rpm软件包、系统启动故障修复。 RHCE ：Red Hat Certified Engineer红帽认证工程师。需要熟练掌握Linux的网卡网络设置、网络服务(文件共享、apache、ssh、dhcp、tftp-server、dns、mariadb-server、postfix等)配置、shell脚本、firewalld 防火墙、tcpwrapper等。 RHCA ：Red Hat Certified Architect 红帽认证架构师。 Q：系统架构师？ A：系统的组织结构图，即体现系统中各种功能软件的结构图。 Q：公司架构？ A：公司的组织结构图，即公司的CEO、经理、员工等组成的结构。作业练习：编写一个名称为/sh/mariadb_install.sh的脚本，实现在本机上用yum自动部署mariadb-server服务器端，并给mariadb-server的root账号设置本地和远程登录管理的密码为rootpass，并自动做本地测试的查库、查mysql库中的user表的user、host、password字段的数据记录信息。 vim /sh/mariadb_install.sh #!/bin/bash #Author:jim #Date:2019-03-08 #company: http://jx.1000phone.net SOFT='mariadb-server mariadb' read -p 'input mariadb-server root user password:' PASS rpm -q $SOFT && echo "$SOFT exsits." || yum install -y $SOFT > /dev/null systemctl restart mariadb && systemctl enable mariadb || echo 'mariadb start error.' mysql -e "grant all on . to root@'%' identified by "$PASS";flush privileges;" mysql -e "update mysql.user set password=password("$PASS") where user='root' and host='localhost';flush privileges;" mysql -uroot -p -e 'show databases;select user,host,password from mysql.user;' 119/125 练习：编写mysql主从复制配置的脚本，实现在A主机上执行脚本能自动配置好mariadb的master主服务器设置，在B主机上执行脚本能自动配置好mariadb的slave从服务器设置，然后在master上用create database dbx建库，在slave上用show database查库来做手动做数据同步验证。首先，在master、slave主机上分别做ssh的双机互信(即用ssh的公钥和私钥认证功能) master主机上的操作： 1.生成秘钥对(即公钥和私钥)：echo | ssh-keygen -t rsa -P "" 2.上传公钥给slave主机：ssh-copy-id root@192.168.11.12 3.安装nfs服务： yum install -y nfs-utils rpcbind systemctl restart nfs && systemctl enable nfs chmod -v 777 /nfs echo '/nfs (rw,no_root_squash)' > /etc/exports exportfs -rav showmount -e 127.0.0.1 4.在master上远程给slave安装nfs服务： ssh root@192.168.11.12 'yum install -y nfs-utils rpcbind;systemctl restart nfs && systemctl enable nfs' ssh root@192.168.11.12 'mkdir -pv /gua_nfs;mount -t nfs 192.168.11.11:/nfs /gua_nfs' 5.在master主机上编写/nfs/mariadb_install.sh脚本。 vim /nfs/mariadb_install.sh 脚本内容如下 #!/bin/bash #Author:jim #Date:2019-03-08 #company: http://jx.1000phone.net SOFT='mariadb-server mariadb' read -p 'input mariadb-server root user password:' PASS rpm -q $SOFT && echo "$SOFT exsits." || yum install -y $SOFT > /dev/null systemctl restart mariadb && systemctl enable mariadb || echo 'mariadb start error.' mysql -e "grant all on . to root@'%' identified by "$PASS";flush privileges;" mysql -e "update mysql.user set password=password("$PASS") where user='root' and host='localhost';flush privileges;" mysql -uroot -p -e 'show databases;select user,host,password from mysql.user;' 6.在master主机上编写/nfs/master.sh脚本。 vim /nfs/master.sh 脚本内容如下 #!/bin/bash cat > /etc/my.cnf.d/master.cnf <<EOF [mysqld] server-id=11 log-bin=master-bin skip_name_resolv=1 EOF systemctl restart mariadb mysql -uroot -p -e "grant replication slave on . to rep@'%' identified by 'rep';flush privileges;reset master;show master status;" 7.在master主机上编写/nfs/slave.sh脚本。 vim /nfs/slave.sh 脚本内容如下 #!/bin/bash rpm -q mariadb-server mariadb && echo mariadb-server installed ||. /gua_nfs/mariadb_install.sh 120/125 cat > /etc/my.cnf.d/slave.cnf <<EOF [mysqld] server-id=12 log-bin=slave-bin skip_name_resolv=1 EOF systemctl restart mariadb mysql -uroot -e " CHANGE MASTER TO MASTER_HOST='192.168.11.11', MASTER_USER='rep', MASTER_PASSWORD='rep', MASTER_PORT=3306, MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=245, MASTER_CONNECT_RETRY=10;" mysql -uroot -e "start slave;show slave status\G" 8.在master上运行master.sh脚本。 chmod -v +x /nfs/.sh . /nfs/master.sh 9.在master上对slave主机远程执行/gua_nfs/slave.sh脚本。 ssh root@192.168.11.12 '. /gua_nfs/slave.sh' 10.在master上执行如下建库操作，远程执行从库的查库操作。 mysql -uroot -p -e 'create database dbx;show databases;' ssh root@192.168.11.12 "mysql -uroot -p -e 'show databases;';" 练习：编写一个名称为/sh/atlas.sh的脚本，实现全自动部署atlas代理服务器软件。 Mysql 单实例部署 PHP+Mysql LAMP环境准备：Linux+Apache+Mysql(mariadb)+P(PHP/Perl/Python) yum install -y httpd mariadb-server mariadb php* systemctl restart httpd mariadb systemctl enable httpd mariadb 案例：在MYSQL中创建一个名称为pdb的数据库文件，在数据库中创建一张名称为t1的表格。用PHP语言打开此数据库，并显示表中的记录。表格内容如下图。 #在服务器本地登录到mysql-server服务 121/125 mysql #库的查询、创建、打开 show databases; create database pdb; use pdb #表的查询、创建，在表中插入数据记录。 show tables; create table t1(id int,name char(20),tel int); desc t1; insert into t1 values (1,'tom',1234567890), (2,'jack',1358798), (3,'alice',1594893); select * from t1; 122/125 #给mariadb-server的root用户设置本地登录密码 mysql -e "grant all on . to root@'localhost' identified by '01';flush privileges;" mysql -uroot -p01 -e 'show databases;' 创建如下test.php测试页面： vi /var/www/html/test.php 网页代码如下 <?php phpinfo() ?> 访问此测试页面：elinks http://192.168.100.6/test.php 或 curl 192.168.100.6/test.php 用火狐浏览器访问：firefox 192.168.100.6/test.php & 用index.php网页查询pdb.t1的结果如下图所示： vi /var/www/html/index.php 网页代码如下，以下蓝色字应该根据数据库的实际情况来修改。 <html> <head> <title>welcome to my info system! </title> </head>  |<a rel="nofollow" href='index.php'>首页</a> | <a rel="nofollow" href='i.html'>新增</a> | <a rel="nofollow" href='u.html'>更新</a> | <a rel="nofollow" href='d.html'>删除</a> | <p> <?php //以下的$con是定义一个变量，用来存连接数据库服务器的操作值。 $con = mysql_connect("localhost","root","01"); if (!$con) { die('could not connect:' . mysql_error()); } //some code echo "连接成功"; mysql_select_db("pdb",$con); $result = mysql_query("select * from t1"); //以下是用php语言中的echo语句生成html语言中的网页表格的标题字段 echo "<table border='1'> <tr> <th>id</th> <th>name</th> <th>tel</th> </tr>"; //以下是用while循环来输出t1表中的数据记录 while($row = mysql_fetch_array($result)) 123/125 { echo "<tr>"; echo "<td>" . $row['id'] . "</td>"; echo "<td>" . $row['name']; "</td>"; echo "<td>" . $row['tel']; "</td>"; echo "</tr>"; } mysql_close($con); echo "成功关闭数据库及表。"; ?> </html> 启动httpd服务：service httpd restart 用命令测试访问：elinks 192.168.100.6/index.php 访问此网站:在浏览器中输入http://192.168.100.6/index.php －－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－案例2：用PHP语言在上面的数据表t1中插入新的记录。首先，创建i.html文件的表单网页。(网页前端设计) vim /var/www/html/i.html 网页代码内容如下 <html> <body>  <form action="insert.php" method="post"> id: <input type="text" name="id" /> name: <input type="text" name="name" /> tel: <input type="text" name="tel" /> <input type="submit" value="提交" /> </form> </body> </html> 然后，创建insert.php插入记录的后台代码vim /var/www/html/insert.php。(网页后端设计) <?php $con = mysql_connect("localhost","root","01"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("pdb", $con); $sql="INSERT INTO t1 (id,name,tel) VALUES ('$_POST[id]','$_POST[name]','$_POST[tel]')"; if (!mysql_query($sql,$con)) { die('Error: ' . mysql_error()); 124/125 } echo "1 record added"; mysql_close($con) ?> <?php //重定向浏览器 header("Location:./index.php"); //确保重定向后，后续代码不会被执行 exit; ?> －－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－案例3：用PHP语言修改(update)上面数据表中指定的记录。首先，创建up.html文件的表单网页。 <html> <body> <form action="update.php" method="post"> id: <input type="text" name="id" /> name: <input type="text" name="name" /> tel: <input type="text" name="tel" /> <input type="submit" /> </form> </body> </html> 然后，创建update.php修改数据记录的后台代码。(下面的代码要求用户必须填写已存在的正确id和name 才能更新tel的值) <?php $con = mysql_connect("localhost","root","01"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("pdb", $con); mysql_query("UPDATE t1 SET tel = '$_POST[tel]' WHERE id = '$_POST[id]' AND name = '$_POST[name]' "); echo "1 record update"; mysql_close($con); ?> <?php //重定向浏览器 header("Location:./index.php"); //确保重定向后，后续代码不会被执行 exit; 125/125 ?> －－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－案例4：用PHP语言在上面的数据表中删除指定的记录。首先，创建d.html文件的表单网页。 <html> <body> <form action="del.php" method="post"> id: <input type="text" name="id" /> name: <input type="text" name="name" /> tel: <input type="text" name="tel" /> <input type="submit" /> </form> </body> </html> 然后，创建del.php删除数据记录的后台代码。 <?php $con = mysql_connect("localhost","root","01"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("pdb", $con); mysql_query("DELETE FROM t1 WHERE id='$_POST[id]' "); echo "1 record delete..."; mysql_close($con); ?> <?php //重定向浏览器 header("Location:./index.php"); //确保重定向后，后续代码不会被执行 exit; ?> －－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－ html中的include包含语句的格式如下：

上一篇：主从复制及MariaDB读写分离

下一篇：bash命令

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

Mysql

Mysql

vim /etc/my.cnf 这是mariadb和mysql的主配置文件

systemctl restart mariadb 或 service mysqld restart

mysql 免密码登录

vim /etc/my.cnf

systemctl restart mariadb (centos 7版) 或 service mysqld restart (centos 6版)

vim /etc/my.cnf 这是mariadb和mysql的主配置文件

systemctl restart mariadb 或 service mysqld restart

mysql 免密码登录

vim /etc/my.cnf

systemctl restart mariadb (centos 7版) 或 service mysqld restart (centos 6版)

再次登录测试： mysql 无密码登录，提示错误 mysql -uroot -p127 用root用户和密码登录，成功登录 13/125 MySQL表操作 MySQL表操作 DDL

MySQL单表查询 MySQL单表查询 SELECT DML

Mysql多表查询 MySQL多表查询

MySQL数据类型 MySQL数据类型

Admin Configuration

chmod 600 /etc/mysql-proxy.cnf

mysql -uadmin -padmin -h192.168.211.140 -P4041 mysql> select * from backends;

常用函数 查看当前的时间：select now(); 查看版本号：select version();

vim /etc/my.cnf

service mysqld restart

cd /var/lib/mysql ; ls

mysqlbinlog mysql.000002

mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56"

mysqlbinlog mysql.000002 --stop-datetime="2012-12-05 11:02:54"

mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56" --stop-datetime="2012-12-05

mysqlbinlog mysql.000002 --start-position=260

mysqlbinlog mysql.000002 --stop-position=260

mysqlbinlog mysql.000002 --start-position=260 --stop-position=930

vim /etc/my.cnf

service mysqld restart

vim /etc/my.cnf

service mysqld restart

51CTO博客

常用函数查看当前的时间：select now(); 查看版本号：select version();