mysql 日期搜索_mysql入门到精通

本教程需要有一定的linux同学学习。如不了解附带linux教程链接

https://editor.csdn.net/md/?articleId=108202553

目标：了解mysql服务器常见概念;

mysql服务器安装及相关配置文件实战：

安装mysql数据库并去处安全隐患实战：

mysql是一个关系型数据库管理系统。mysql是一个关联性数据库管理系统，关联性数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库中，这样就增加了速度并提高了灵活性。

常见LAMP架构：linux+apache+mysql+php

MariaDByum install -y httpd mariadb-server mariadb php php-servercd /run/media/xiao/Packages/lsmariadb-5.5.64-1.el7.x86_64.rpmmariadb-bench-5.5.64-1.el7.x86_64.rpmmariadb-devel-5.5.64-1.el7.x86_64.rpmmariadb-libs-5.5.64-1.el7.x86_64.rpmmariadb-server-5.5.64-1.el7.x86_64.rpmmariadb-test-5.5.64-1.el7.x86_64.rpmmarisa-0.2.4-4.el7.x86_64.rpmrpm -pqi mariadb-5.5.64-1.el7.x86_64.rpm

#查看软件包的版本信息，官方网址，作用

rpm -qf `which mysql` #查看命令来自哪个包mysql -V #查看mysql版本httpd -v #查看apache版本systemctl start httpd   #启动apachevim /etc/httpd/conf/httpd.conf   #配置配置文件

将配置文件内的内容改为下面的内容#!#95:ServerName www.exaple.com:80ServerName localhost:80:wq  #保存编辑

注：安装完mysql-server会提示可以运行mysql_sercure_installation,运行mysql_sercure_installation会执行几个设置：

1.为root用户设置密码

2.删除匿名账号

3.取消root用户远程登陆

4.刷新授权表，是修改生效通过这几项的设置能够提高mysql库的安全，建议生产环境中mysql安装完成后一定要运行一次mysql_sercure_installation

如果不做安全配置，如何修改root密码

mysqladmin -u root password "123456" #改密rootmysql -u root -p123456:show databases;create database HA;show create database HA;

#能够显示字符集默认不能创建特殊字符的数据库,加上反引号就能

create database `HA-test`#创建HA-TEST数据库ls /var/lib/mysql/ #该目录存放数据库名字的信息select database(); #查询当前操作哪个库select now(); #查询时间select user(); #查询用户drop database HA; #删除数据库drop　database is exists HA；#不报错create table student; #建表desc student; #查看student表explain student; #查看表结构show cloumns from student; #查看表结构desc mysql.student; #查看mysql库下的student表show create table student \G; #以行形式查看创建student表详细信息create table student2 (id int(2),name varchar(40),age int(12))ENGINE=MYISAM DEFAULT CHARSET=utf8;#创建表的时候指定搜索引擎，和默认字符集drop table student2; #删除表名mysql -uroot -p123456 -A #-A参数能够屏蔽信息alter table student rename test;#将student表改成testalter table test modify id int(10);#将id字段的int数据类型的长度改为(10)；modify作用alter table test chage name stname char(20);#将字段name改为stname，数据类型改为char(20)；change作用alter table test add sex enum('M','W'); #增加字段性别，数据类型为枚举，add作用。alter table test add uid int first; #将uid字段添加到第一alter table test add address char(40) after age; #将address字段添加到age字段后边alter table test drop address; #将address的字段删除insert into test values(1,1,'zhangsan',25,'M');#插入所有数据,insert into test values(1,1,'zhangsan',25,'M'),(2,2,'lisi',27,'W');#插入两行insert into test values(1,1,'zhangsan',25,'M')select * from HA.test; #跨库查找delete from test wherer age="";#删除test表中age为空的数据列update test set sex='W' where id=1; #将id为1的列的sex数据改为Wselect distinct stname from test; #排除stname列中重复数据select * from test where id=2 and age=25; #同时满足条件select * fron test where id=2 or age=25; #或者的关系，只能满足其一select * from test where id=2 and (age=15 or age=24);#多条件同时满足,先执行括号里的select * from test where binary stname='stname'; #binary，强制区分大小写select * from test order by desc; #降序select * from test order by asc ; #升序，默认是升序select distinct uid from test order by uid desc; #结合distinct和order by使用。

数据类型：三大类：数值，日期/时间,字符串数值类型 MySQL支持所有标准SQL数值数据类型。 这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。 关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。 BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。 作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

类型 大小 范围(有符号) 范围(无符号) 用途

TINYINT 1 byte (-128，127) (0，255) 小整数值

SMALLINT 2 bytes (-32 768，32 767) (0，65 535) 大整数值

MEDIUMINT 3 bytes (-8 388 608，8 388 607) (0，16 777 215) 大整数值

INT或INTEGER 4 bytes (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值

BIGI 8 bytes (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值FLOAT 4 bytes (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度 浮点数值

DOUBLE 8 bytes (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度 浮点数值

DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

日期和时间类型 表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。 每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。 TIMESTAMP类型有专有的自动更新特性，将在后面描述。类型 大小( bytes) 范围 格式 用途DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间YEAR 1 1901/2155 YYYY 年份值DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值TIMESTAMP 4 1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07YYYYMMDD HHMMSS 混合日期和时间值，时间戳字符串类型 字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型 大小 用途

CHAR 0-255 bytes 定长字符串

VARCHAR 0-65535 bytes 变长字符串

TINYBLOB 0-255 bytes 不超过 255 个字符的二进制字符串

TINYTEXT 0-255 bytes 短文本字符串

BLOB 0-65 535 bytes 二进制形式的长文本数据

TEXT 0-65 535 bytes 长文本数据 MEDIUMBLOB 0-16 777 215 bytes 二进制形式的中等长度文本数据

MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据 LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据

LONGTEXT 0-4 294 967 295 bytes 极大文本数据 注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。 CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。 BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。 BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。 有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

select curdate(); #打印当前时间help show(); #帮助show variables; #打印信息show global variables like "%version%";#模糊查询version相关信息show status;#查看状态数据的导出和导入导入数据库前必须创建一个空数据库show databases; #查看数据库create database book; #建库yum -y install lrzsz;root#：mysql -uroot -p123456 bookuse HA;source /root/book_utf.sql; #将绝对目录下book_utf.sql文件数据导入到HA数据库mysqldump -u 用户名 -p password  导出的文件名；root#:mysqldump -uroot -p123456 HA>ha.sql #导出数据库vim ha.sqlroot#:mysqldump -uroot -p123456 -B HA >ha.sql #-B导出整个数据库包含建库语句select * from category into outfile '/tmp/123.txt' #将查询结果导出到/tmp/123.txt文件内，注：该目录必须权限若无权限exitvim /etc/my.cnf追加secure-file-priv="/":wqsystemctl restart mysqldselect bname '图书名称',publishing '出版社',price '价格' from books where price=30;#设置别名select bname as '图书名称',publishing as '出版社',price '价格' from books;

逻辑运算符：算数运算符 andornot-------

=<>不等于

>大于

>=大于等于

<=小于等于

select * from where price in(50,60,70);#属性in，在范围内select * from where price !in(50,60,70);#匹配不在范围内的select bname,price from books where price in(60,70) order by price asc;#匹配范围，排序，升序between...andselect bname.price from books where price not between 30 and 60 order by price desc;#查询价格不在30到60之内的bname和price列。模糊查询%任意字符select bname,price from books where bname like '%程序%';#查询bname列含有程序的数据子查询在select 的 where 中又出现了select.select bname ,btypeid from books where btypeid=(select btypeid from category where btypename='网络技术')；select * from category limit 1,5;注：limit字句可以被用于强制select语句返回指定的参数，limit接受一个或两个数字参数，参数必须是一个整数常亮，如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目，初始记录行的偏移量是0；select bname,price from books order by price desc limit 0,3;#查询最贵的三本书连接查询：以一个共同的字段，求两张表当中符合条件的并集，通过共同字段把两张表连接起来。内连接：根据表中的共同字段进行匹配外连接：左外连接，右外连接语法：select 字段 from 表1 inner join 表2 on 表1.字段=表2.字段;select a.bname,a.price,b.btypename from books a inner join category b on a.btypeid=b.btypeid;左连接：select 字段 from a表 left join b表 on 连接条件;select a.bname,a.price,b.btypename from books a left join category b on a.btypeid=b.btypeid;右连接：select a.bname,b.* from books a right join category b on a.btypeid=b.btypeid;主表内容全都有，从表内没有显示nullselect sum(price) from books;#sum求和，avg求平均，max求最大值，min求最小值，count统计数量select bname,price from books where price=(select max(price) from books);select count(distinct price) from books where price>40;substr截取select substr(Btypenamme,1,7) from category where btypeid=10;#从第一个字符串开始截取7个字符concat拼接select concat(bName,publishing) from books;#将bname和publishing字段的内容拼接到一起显示upper()大写输出 lower()小写输出select upper(bname) from books where bid=9;select lower(bname) from books where bid=9;索引：一种特殊文件(数据表上的索引是表空间的一个组成部分)数据库索引好比是一本书的目录，能够加快数据库的查询速度。1.但是索引是以文件存储，如果索引过多，占磁盘空间较大，而且他影响insert,update,delete执行时间2.索引中数据必须与数据表数据同步，如果索引过多，当表中数据更新时，索引也要同步更新，这就降低了效率。索引的类型：`1.普通索引：不具备唯一性。2.唯一性索引3.主键索引4.复合索引`创建普通索引：create table 表名(列定义，index 索引名称(字段)idnex 索引名称 (字段))；create table demo(id int(4),name varchar(20),pwd varchar(20),index(pwd),index(name));#创建索引，index可以换成keydesc demo #查看表结构show create table demo; #查看索引的建表过程生成之后的表添加索引alter table 表名 add index 索引名称(字段1，字段2);#添加索引alter table demo add index(id);#将demo表中的id添加索引alter table demo drop key id; #删除demo表中的id索引`唯一索引`：索引列的所有值都只能出现一次，必须唯一，用来约束内容，字段只能出现一次create table demo3(id int(4) auto_increment primary key,uname varchar(20),upwd varchar(20),unique index (uname));#创建表demo3，自增长，主键，唯一索引uname。要修改表时要查看表结构。alter table demo3 add unique index_upwd (upwd);#增加唯一索引upwd`主键索引`：查询数据库，按主键查询是最快的，每个表只能有一个主键列，要求必须唯一，不允许为空。创建表后添加如果生产的数据无法保证唯一，创建索引会失败。create table demo4(id int(4) not null auto_increment,name varchar(20) default null,primary key(id));注：如果有自增长，不能删除索引，所以，先删除自增长alter table demo4 change id id int(4) not null;#修改列，取消自增长alter table demo4 drop primary key; #删除主键alter table deo4 change id id int(4) not null primary key auto_increment; #改变id列结构，添加主键，自增长`复合索引`：索引可以包含一个，两个或更多个列，create table firewall(host varchar(15) not null,port smallint(4) not null, access enum('deny','allow') not null,primary key (host,port));#联合主键，即为联合索引。`全文索引`：是目前搜索引擎使用的一种关键技术，他能够利用分词技术等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能的筛选出我们想要的搜索结果fulltext index 5.7之后支持中文,内置naram全文索引插件varchar text创建全文索引：方法1：创建表时创建create table 表名(列定义fulltext key 索引名(字段)；)方法二：修改表时添加alter table 表名 add fulltext 索引名(字段)；alter table books add fulltext author(author);#添加索引只能支持myisam搜索引擎

索引设计原则：

1.索引并非越多越好

2.数据量不大不需要建立索引

3.列中的值变化不多不需要建立索引 row id

4.经常排序(order by 字段)和分组(group by 字段)的列需要建立索引

5.唯一性约束对应唯一性索引

`外键约束`foreign key 就是表与表主键的某种约定关系。

创建外键约束必须满足以下4个条件

1.确保参照的表和字段存在

2.组成外键的字段被索引

3.必须使用ENGINE指定存储引擎为：innodb

4.外键字段和关联字段，数据类型必须一致

create table `user` (id int(11) not null auto_increment,name varchar(50) not null default '',sex int(1) not null default '0',primary key(id))ENGINE=innodb;#创建user表create table `order`(o_id int(11) auto_increment,u_id int(11) default '0',username varchar(50),money int(11),primary key(o_id),index(u_id),foreign key order_f_key(u_id) references user(id) on delete cascade on update cascade) ENGINE=innodb; #创建表，表order中的u_id与user表中的id关联,约束名称order_f_key，设置级联删除,主键o_id,索引u_id,insert into user(name,sex)values('HA',1),('lb',2),('HPC',1);insert into order (u_id,username,money)values(1,'HA',234),(2,'LB',146),(3,'hpc',256);alter table `order` drop foreign key order_f_key;#删除外键约束select * from user;select * from order;delete from user where id=1; #order表中的id为1的也被删除update user set id=6 where id=2;isnert into user values (5,find,1);insert into order (u_id,username,money) values(5,find,345);#插入时会检查用户里是否有id=5的数据，如果有才能插入视图：存在于数据库中的虚拟表视图本身没有数据，只能通过执行相应的select查询create view ba as` select b.name,n.price,c.btypename from books as b left join category as c on b.btypeid=c.btypeid`; #创建视图select * from ba;#查询视图alter view ba as select name from books;#将视图ba修改字段修饰符：1.null和not null注：空值不占空间，null值是占空间的2.default设定字段默认值：create table test2(name varchar(8) not null,dept varchar(25) default "sos");select * from test2;3.auto_increment字段约束，自增长create table items (id int not null auto_increment primary key,label varchar(20) not null);#创建items表insert into items(label) values("'kkk'");select * from items;清空表记录方法一：delete不加where条件，会清空所有表数据，delete不会清空自增长的值方法二：truncate table items;#会把自增长的值也清除。

索引

外键

视图

触发器：是一种特殊的存储过程，它在插入，删除或修改待定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。触发器的作用：

1.安全性：可以基于数据库的值使用户具有操作数据库的某种权利。可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据可以基于数据库中数据限制用户的操作，例如不允许股票的价格的涨幅一次超过10%。

2.审计：可以跟踪用户对数据库的操作审计用户操作数据库的语句把用户对数据库的操作写入审计表

3.实现复杂的数据完整性规则实现非标准的数据库完整性检查和约束，触发器可以产生比负责更加复杂的限制，与规则不同，触发器可以引用或数据库对象。例如：触发器可回退任何企图吃进超过自己保证金的期货。

4.实现复杂的非标准的数据库相关完整性规则。触发器可以对数据库中相关的表进行连环更新例如：在auths表author_code列中删除触发器可导致相应删除在其他表中的与之匹配的行，触发器能够拒绝或回退那些破坏相关完整性的变化，取消视图进行数据更新的事务。

5.实时同步的肤质表中的数据

6.自动计算数据值如果数据值达到了一定的要求，则进行特定的处理如果公司的账号上的资金低于5万元则立即给财务人员发送警报。创建触发器;

#语法：

create trigger 触发器名称 触发的时机 触发的动作 on 表名 for each row 触发器状态。

参数：

触发器名称：自己定义

触发的时机：before/after 在执行动作之前或者之后

触发的动作:指的激发触发程序的语句类型

each row：操作第一行我都监控着

触发器创建语法四要素：`1.监视地点(table)2.监视事件(insert/update/delete)3.触发时间(after/before)4.触发事件(isnert/update/delete)`

例：当category表中，删除一个btypeid=3的图书分类时，books表中也要删除对应分类的图书信息(类似级联删除)use book在category执行删除前，查看btypeid=3的图书分类。

select bnam,btypeid from books where btypeid=3;create trigger delcategory after delete on category for each rowdelete from books where btypeid=3;delete from category where btypeid=3;select * from books;select * from category;show create trigger delcategory \G;#查看触发器delcategory详细信息drop trigger delcategory;#删除触发器事务

数据库事务：事务时由一组sql语句组成的逻辑处理单元，要不全成功，要不全失败注：mysql中只有innodb和dbd类型的数据表才能支持事务处理，其他的类型不支持。

show create table books;#能够查看引擎alter table category engine=innodb;#修改引擎

事务处理：可以确保非事务性单元的多个操作都能成功完成，否则不会更新数据资源。银行汇款1000元。事务必须满足4个事务：1.原子性2.一致性3.隔离性4.持久性

set autocommit=0;#关闭自动提交start transaction; #开始事务update books set bname='ccc' where bid=1;update books set bname='ddd' where bid=2;//为提交之前，可回滚rollback;//回滚commit; #提交事务

`存储过程`：大多数sql语句都是针对一个或多个表的单条语句，并非所有操作都这么简单，经常会有一个完整的操作需要多条才能完成。存储过程是在大型数据库系统中，一组为了完成特定功能的sql功能集，存储在数据库中经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它，存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

优点：

1.增强sql语言的功能和灵活性;存储过程可以用来控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

2.标准组件式编程，存储过程被创建后，可以再程序中多次调用，而不必重新编写该存储过程的sql语句，而且数据库专业人员可以随时对存储过程修改，对应用程序源代码毫无影响，

3.较快的执行速度，如果某一操作包含大量的transaction-sql代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多，因为存储过程是预编译的，在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划，而批处理的transaction-sql语句每次运行时都要进行编译和优化，速度相对要慢一些。

4.减少网络流量，针对同一个数据库对象的操作，如果这一操作所涉及的transaction-sql语句被组织进存储过程，那么当在客户计算机上调用该存储过程时，网络传送的只是该调用语句，从而大大减少了网络流量并降低了网络负载。

5.作为一种安全机制来充分利用，通过对执行某一存储过程的权限进行限制，能够实现对相应的数据额访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

缺点：可移植性差，对于简单的sql语句，存储过程没优势，如果存储过程不一定会减少网络传输如果只有一个用户使用数据库，那么存储过程对安全也没什么影响在大并发量访问的情况下，不宜写过多涉及运算的存储过程业务逻辑复杂时，特别是涉及到对很大的表进行操作的时候，不如在前端先简化业务逻辑。

定义存储过程语法：create procedure 过程名(参数1，参数2.....)beginsql语句end创建存储过程之前我们必须修改mysql语句默认结束符，要不然我们不能创建成功使用delimiter我们可以修改执行符号delimiter是分隔符的意思，因为mysql默认以“；”为分隔符，如果我们没有声明分隔符，那么编译器会把存储过程当做sql语句进行处理，则存储过程的编译过程会报错，所以要事先用delimiter关键字申明当前段分隔符，这样mysql才会将“；”当做存储过程中的代码，不会执行这些代码，用完要把分隔符归还。

delimiter % #修改sql结束符为%create procedure selcg()beginselect * from category;end%

#创建存储过程成功

call selcg()% #查看存储过程存储过程的参数类型create prcedure xx(in abc int)beginselect abc;set abc=3;select abc;end %set @y=1 #定义mysql局部变量call xx(@y);#查看存储过程，将@y当做存储过程的变量传递查出两个值1，和3

in 参数 传入参数特点：读取外部了、变量值，且有效范围仅限存储过程内部

create procedure getonebook(in b int)beginselect bid,bname,price from books where bid=b;end %call getonebook(3)%

out参数，传出参数：不读取外部变量值，在存储过程执行完毕后保留新值。

create procedure pout(out p_out int)beginselect p_out;set p_out=2;select p_out;end %set @p_out=1% #局部变量没有用call pout(@p_out)%

#存储过程的参数变量是传出的，局部变量是不能传进的。

create procedure demo(out pb varchar(200))beginselect bname into @pa from books where bid=3;select @pa as '图书名';end%call demo(@a);

%inout参数：特点：读取外部变量，在存储过程执行完后保留新值传进来传出去。

create procedure pinout(inout p_inout int)beginselect p_inout;set p_inout=2;select p_inout;end%set @p_inout=1%call pinout(@p_inout)%1和2create table t2(id int)% #建表create procedure t2(n1 int) #创建存储过程beginset @x=0;repeatset @x=@x+1;insert into t2 values(@x);until @x>n1;end repeat;end%call t2(5)% #查看存储过程select * from t2% #查询t2表数据

存储过程的变量使用mysql中使用declare进行变量的定义变量定义：

declare variable_name [variable_name...] datatype [default value];

变量赋值：set 变量名=表达式值[variable_name=epression...]

变量赋值可以再不同的存储过程中继承

create procedure decl()begindeclare name varchar(200); #创建变量nameset name=(select bname from books where bid=2);select name;end%call decl% #报错，不支持中文create procedure decl2()begindeclare name int(200);set name=(select bid from books where bid=2);select name;end%call decl2% #查询存储过程decl2

变量的作用域：内部的变量在其作用域范围内享有更高的优先权，当执行到end。变量时，内部变量消失，此时已经在其作用域外，变量不在可见了，因为在存储过程外再也找不到这个申明的变量，但是你可以通过out参数或者将其指派给回话变量来保存其值。存储过程流程控制语句：

1.if-then -else

delimiter // #修改程序结束控制符create procedure proc2(in parameter int)begindeclare var int;set var=parameter+1;if var=0 theninsert into t values(17);end if;if parameter=0 thenupdate t set s1=s1+1;elseupdate t set s1=s1+2;end if;end;//delimiter ; #修改修饰符

；2.case语句

delimiter // #修改程序结束控制符create procedure proc3(in parameter int)begindeclare var int;set var=parameter+1;case varwhen 0 theninsert into t values(17);when 1 theninsert into t values(18);elseinsert into t values(19);end case;end;//delimiter ; #修改程序结束修饰符为；

循环语句：3.while......end

whiledeliniter //create procedure proc4()begin;declare var int;set var=0;while var<6 doinsert into t values(var);set var=var+1;end while;end;//delimiter ;

循环语句loop......end

looploop循环不需要初始条件，这点和while循环相似，同时和repeat循环一样不需要结束条件，leave语句的意义是离开循环。

delimiter //create procedure proc6()begindeclare v int; #定义变量vset v=0;loop_lable:loop #k开始循环insert into t values(v);set v=v+1;if v >=5 then #开始循环leave loop_lable; #离开循环loop_lable是条件end if; #结束if循环end loop; #结束loopend;//delimiter ;show create procedure proc6 \G; #查看存储过程show procedure status \G; #查看所有存储过程drop procedure demo // #删除存储过程drop procedure if exits demo //#判断是否存在

mysql数据库常见字符集介绍：gbk len(2) desc(不是国际标准)utf-8 len(3) desc(中英文混合环境，国际标准)latin1 len(1) desc(mysql默认字符集)utf8mb4 len(4) desc(utf-8 uniconde)

show character set ; #查看字符集show variables like 'character_set%' #模糊查询vim /etc/my.cnf#!character_set_server=utf-8;#!:wq

#修改mysql配置文件

systemctl restart mysql #重新登陆

出现乱码，要改字符集实战：迁移数据,(数据量大不适合)背景：公司业务数据book由于之前建表没注意字符集的问题，导致之前写入的数据出现乱码，现在要将之前的数据和现在的数据的字符集保持一致，并且已经存在的记录不乱吗。步骤：

1.建库及建表的语句导出，sed批量修改为utf8

2.导出之前所有的数据

3.修改mysql服务端和客户端编码为utf8

4.删除原有的库表及数据

5.导入新的建库及建表语句

6.导入之前的数据root

#:mysqldump -uroot -p123456 --default-character-set=latin1 -d test>booktable.sql;#导出数据，指定默认字符集，导出到booktable.sql文件中lsvim booktable.sql:%s/latin1/utf9/g #替换所有:wq #保存退出root#:mysqldump -uroot -p123456 --quick --no-create-info --extended-insert --default-character-set=latin1 test >bookdata.sql

参数说明：--quick:用于转储大的表，强制mysqldump从服务器一次一行的检索而不是检索所有行，并输出当前cache到内存中。--no-create-info:不要创建create table语句--extended-insert使用包括几个values列表的多行insert语法，这样文件更小，io也更小，导入数据会非常快。--default-character-set=latin1;按照原有的字符集导出数据，这要导出的文件中，所有中文都可以看见。不会导致乱码。

lsvim bookdata.sql

将SET NAMES latin1改为utf8

：wqcreate database test2 default charset utf8; #建库的时候指定字符集为utf8use test2;source /root/booktable.sql #导入表结构source /root/bookdata.sql #导入表内数据

exit;root#:mysql_secure_installation #执行mysql安全注：做了安全设置后mysql命令不能用；test库被删除测试php与apachecd /var/www/htmlvim index.php#!<?phpphpinfo ();php>#!systemctl restart httpd

`mysql日常维护工具`：mysqlcheck修复工具

root#:mysqlcheck --help #调用mysqlcheck命令使用方法root#:mysqlcheck -uroot -p123456 test books #检查test库下的books表root#:mysqlcheck -uroot -p123456 test #检查test库下的所有表#-c检查表 -r修复表 -A所有mysqlcheck -uroot -p123456 -A -r #修复所有库mysql备份恢复

mysql备份类型：按照备份时对数据库的影响范围分：

hot backup(热备) cold backup(冷备) warm backup(温备)

hot backup:指在数据库运行中直接备份，对正在运行的数据库没有任何影响，(online backup)官方手册为在线备份cold backup:指在数据库停止的情况下进行备份(offline backup)官方手册为离线备份

warm backup:备份同样在数据库运行时进行，但是会对数据库的操作有所影响，例如加一个全局读锁以保证备份数据的一致性。按照备份后的文件内容分类：1.逻辑备份工具：mysqldump

2.裸文件备份

按照备份数据库的内容来分，又可以分为

1.完全备份：对数据库完整的备份

2.增量备份：在上一次完全备份基础上，对更新的数据进行备份(xtrabackup)

3.日志备份：二进制日志备份。主从备份

逻辑备份工具：mysqldump使用的时候mysql当要导入或者导出数据量大的库时，用phpmyadmin甚至mysql administrator这些工具都会力不从心，这时只能使用mysql所提供的命令行工具mysqldump进行备份恢复，数据量大的时候不推荐使用，可支持myisam，inndbmysql数据的导入和导出工具：mysqldump导出：

mysqldump -uroot -p123456 -A >all.sqlmysqldump -uroot -p123456 --all-databases >all2.sqlmysqldump -uroot -p123456 book >book.sql #导出数据库bookmysqldump -uroot -p123456 book books >books.sql #导出book库books表mysqldump -uroot -p123456 -d book >booktable.sql #只导出book库的表结构mysqldump -uroot -p123456 -t book >booktable.sql #只导出book库中的数据mysqldump -urot -p123456 -B book2 > book2.sql #导出数据库，并自动生成库的创建语句在有-B参数的时候导入数据库的方法可以不指定库 mysql -uroot -p123456 1.mysql -uroot -p123456 book 2.使用source导入create database book;use book;source /root/book.sql;导入表drop table books;source /root/book.sql #导入表时不需要建表，要先进入到相应的数据库中select * from books; #查询数据

mysqlhostcopy :裸文件备份 注：5.7版本以上已经去除此命令mysqlhostcopy使用lock tables,flush tables和cp或scp来快速备份数据库，它是备份数据库或单个表最快的途径，完全属于物理备份，但只能用于备份myisam存储引擎和运行在数据库目录所在的机器上，与mysqldump备份不同，mysqldump属于逻辑备份，备份时是执行的sql语句。

完全备份：[www.percona.com](http://www.percona.com/)下载工具网址

innobackupex --user=root --passwrod=123456 --no-timestamp /tmp;#不需要创建时间戳innobackupex --user=root --password=123456 /tmp #完全备份ls /tmpinnobackupex --user=root --passwrod=123456 --no-timestamp /tmp 2>>/tmp/db_backup.log#2是混合输出，将其他信息输出到db_backup.log文件中去还原：ls /var/lib/mysql/#存放数据库的目录ll !$systemctl stop mariadbcd /var/lib/mysql/rm -rf ./* #删除当前目录下的所有数据innobackupex --copy-back /tmp/ #恢复数据llchown -R mysql.mysql /var/lib/mysql/ #递归方式修改所属组和用户llsystemctl start mariadbmysql -uroot -p123456show database;增量备份：create table t1(id int) ENGINE=innodb; #建表指定引擎show create table t1; #查看表结构，insert into t1 values(20); #插入数据isnert into t1 values(30); #插入数据root#:innobackupex --user=root --password=123456 --no-timestamp /tmp/full2#完全备份root#:cat /tmp/full2/strabackup_checkpointsmysql -uroot -p123456use test;insert into t1 values(30); #插入数据select * from t1; #查询t1表innobackupex --user=root --password=123456 --incremental /tmp --incremental-basedir=/tmp/full2#在全备的情况下增量备份，--incremental-basedir指定全备的目录，--incremental指定增量备份的目录。ls /tmpcat /tmp/时间戳 #查看增量备份的数据，查看日志序列号cat /tmp/full2/xtrabackup_checkpoints #查看完全备份的数据还原，先还原全备的数据

`mysql主从`

mysql replication

replication可以实现将数据从一台数据库服务器(master)复制到一台或多台数据库服务器(slave)默认情况下属于异步复制，无需维持常连接通过配置，可以复制所有的库或者几个库，甚至库中的一些表是mysql自建的，本身自带的。replication的原理简单来说就是master将数据库的改变写入二进制日志，slave同步这些二进制日志，并根据这些二进制日志进行数据操作从服务器抓取主服务器的二进制文件

DML:SQl操作语句，update,insert,delete

relay log:中继日志

replication的作用。

1.replication ：故障切换

2.backup server备份服务，无法对sql语句执行产生的故障恢复，有限的备份

3.high performance高性能，可以多台slave，

实现读写分离整体来说，复制有3个步骤

1.master将改变记录到二进制日志(binary log)中(这些记录叫做二进制日志事件，brinary log events)；2.slave将master的binary log events拷贝到它的中继日志(relay log)

3.slave重做中继日志中的事件，修改salve上的数据。

```m-s-s```

使用一台slave作为中继，分担master压力，slave中继需要开启bin-log并配置log-slave-updates slave中继可以使用block-hole存储引擎，不会把数据存储到磁盘，只记录二进制日志

m-m双主互备(互为主从)很多人误认为这样可以做到mysql负载均衡，实际没什么好处，每个服务器需要做同样的同步更新，破坏了事务的隔离性和数据的一致性。

m-m-m监控三台机器互相做对方的master天生的缺陷，复制延迟，slave上同步慢于master，如果大并发的情况要延迟更严重mysql5.6已经自身可以实现fail over故障切换one -slave muti master

一从多主s好处：节省成本，将多个master数据自动化整合缺陷：对库和表数据的修改较多部署mysql主从同步

M-Smaster:192.168.164.133

salve :192.168.164.132

create database ha;create table test(id int,name varchar(20));grant replication slave on *.* to slave@'192.168.164.%' identified by "123456"; #授权mysqldump -uroot -p123456 -B ha>ha.sql #导出数据库，含有建库语句lsscp ha.sql 192.168.164.132:/root#传输ha.sql到主机192.168.164.132的/root目录下vim /etc/my.cnf #修改mysql主从配置文件#![mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socksymbolic-links=0validate-password=offlog-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidsecure-file-priv="/"character_set_server=utf-8log-bin=mysql-bin-master #启用二进制server-id=1 #服务器id，不能重复binlog-do-db=ha #需要同步的库binlog-ignore-db=mysql #不需要同步的库mysql:wqsystemctl restart mysqldmysql -uroot -p123456show master status; set sql_log_bin=off; #暂时关掉同步grant replication slave on *.* to slave@'192.168.164.%' identified by "123456";#授权创建账号flush privileges; #刷新权限表ll /var/lib/mysql #能够查看创建的主从myslq>show binlog events \G #查看二进制文件#测试主从insert into values(4,'wu');从库：mysql -uroot -p123456 mysql -uroot -p123456 -e "show databses" #查看数据库vim /etc/my.cnf#！server-id=2:wqmysql -uslave -p123456 -h 192.168.164.133stop slave;change master to master_host='192.168.164.133',master_user='slave',master_password="123456";#start slave;show slave status;select * from test;#注判断主从是否成功slave_io_running:yes :一个负责与主机的io通信slave_sql_running:yes：负责自己的slave mysql通信注：centos7下如果不成功检查防火墙是否关闭systemctl status firewalldsystemctl stop firewalldM-M搭建双主服两台机器都开启二进制传输都同步数据库

mss搭建环境：

master(青龙) 192.168.164.132

slave中继(白虎) 192.168.164.133

slave(朱雀)1921.68.164.134尽量版本一致。

```master(青龙)：192.168.164.132```scp ha.sql 192.168.164.133:/root #传输文件主：mysql>grant replication slave on *.* to repl@'192.168.164.133' identified by "123456";#在192.168.164.133主机上创建repl用户授权，密码为123456flush privileges; #刷新用户列表vim /etc/my.cnf#!添加五行log-bin=mysql-bin-master#开启二进制文件server-id=1binlog-do-db=ha #需要同步的库binlog-ignore-db=mysql #不同步的库sync-binlog=1 #同步二进制binlog-format=row #以行的格式化输出:wqsystemctl restart mariadbmysql>show master status#出现两个yes即为 成功use ha;select * from test;insert into test values (1,"test");select * from test; #若成功，在另外两台mysql上也能查到数据中继：```白虎192.168.164.133```mysql>grant replication slave on *.* to repl@'192.168.164.134' identified by "123456";exitroot#:vim /etc/my.cnf#!log-bin=mysql-bin-slave1 #开启二进制文件server-id=2log-slave-updates=1 #开启日志更新,即把它从relay-log当中读取出来的二进制日志并且这本机上执行的操作也记录这自己的二进制日志里面，这样才能使第三台slave读取到相应数据变化。binlog_format=row #以行的格式输出::wqsystemctl restart mariadbmysql -uroot -p123456mysql>stop slave;#停掉从change master to master_host='192.168.164.132',master_user='repl',master_password='123456';#告知master，允许去master—host上抓取二进制文件mysql>start slave;show slave status \G;#出现两个yes即为 成功show create table test; #查看表结构以及搜索引擎select * from test; #若成功，则能查到数据#设置黑洞引擎mysql>set sql_log_bin=off;#关掉二进制记录功能alter table test engine=blackhole;#g改为黑洞引擎mysql>set sql_log_bin=on;#开启二进制记录功能在查看表信息，为空，被黑洞吞噬，但不影响从服务器的数据同步\#排错:mysql>show slave status;查看last_sql_error:看错误信息mysql>show binlog events in 'mysql_bin_master.000002' from 107 \G #查看具体执行过程。由上面的结果可知，我们需要跳过两个事物173，288然后salve中断操作slave stop;set global sql_slave_skip_counter=2;跳过两个事物slave start;或者：提供了一个session粒度选项，通过关闭这个选项可以不让主库将打开这个选项或关闭连接前的sql语句写入binlogset sql_log_bin=off; #关闭二进制记录功能alter table aaa add column xxx int default 1 after yyy;#修改表的结构注：主从mysql的库以及表的结构都必须一致从：set sql_log_bin=on;question:slave不同步在从上mysql>stop slaechannge master to master_host='192.168.164.132',master_user='repl',master_password='123456';master_log_file='mysql-bin-slave1.000002',mster_log_pos=415;#该数据与show master status的数据同步mysql>start slave;mysql>show slave status;```朱雀192.168.164.134‘’‘vim etc/my.cnf#!log-bin=mysql-bin-slave2 #启用二进制日志server-id=3binlog_format=row:wqsystemctl restart mariadbps -ef|grep mariadb #新版本要手动kill进程kill-9 XXXsystemctl restart mariadbmysql -uroot -p123456stop slave;change master to master_host="192.168.164.133",master_user="repl",master_password='123456';start slave;show slave status \G;#出现两个yes即为 成功select * from test; #若成功。则能查到数据