函数：

 在select 命令里使用函数做查询的格式

第一种格式

SELECT  函数(表头名)  FROM  库名.表名；

 第二种格式

SELECT  函数(函数())  ；

 第三种格式：单独把函数的执行结果输出

SELECT  函数() ;

 

常用函数的使用：

1.1  字符函数的使用

作用:处理字符或字符类型的字段

LENGTH(str)           返字符串长度，以字节为单位

CHAR_LENGTH(str)     返回字符串长度，以字符为单位

UPPER(str)和UCASE(str)  将字符串中的字母全部转换成大写

LOWER(str)和LCASE(str)  将str中的字母全部转换成小写

SUBSTR(s, start,end)     从s的start位置开始取出到end长度的子串

INSTR(str,str1)      返回str1参数，在str参数内的位置

TRIM(s)           返回字符串s删除了两边空格之后的字符串

 

1.2  数学函数的使用  具体函数的使用见案例

作用:处理数据或数值类型的字段

ABS(x) 返回x的绝对值

PI()       返回圆周率π，默认显示6位小数

MOD(x,y)   返回x被y除后的余数

CEIL(x)、CEILING(x)  返回不小于x的最小整数 (x 是小数)

FLOOR(x）         返回不大于x的最大整数 (x 是小数)

ROUND(x)          返回最接近于x的整数，即对x进行四舍五入 (x 是小数)

ROUND(x,y)        返回最接近x的数，其值保留到小数点后面y位，若y为负              值，则将保留到x到小数点左边y位 (x 是小数)

1.3  聚集函数的使用  sum()  avg()  min()  max()  count()

mysql> system date  查系统时间日期

mysql> select now();

mysql> select curtime() ;

1.5  数学计算的使用  +   -     *   /     % 

   

1.6  流程控制函数 ： 查询表记录事可以加判断语句

 if语句 语法格式

 语法：

if(条件,v1,v2) 如果条件是TRUE则返回v1，否则返回v2

ifnull(v1,v2)  如果v1不为NULL，则返回v1，否则返回v2 

case语句 语法格式  (可以有多个判断添加)

如果字段名等于某个值，则返回对应位置then后面的结果，

如果与所有值都不相等，则返回else后面的结果

      

语法格式1

CASE 字段名             

WHEN 值1 THEN 结果

WHEN 值2 THEN 结果 

WHEN 值3 THEN 结果

ELSE 结果 

END

 语法格式2

CASE             

WHEN  判断条件 THEN 结果

WHEN  判断条件  THEN 结果 

WHEN  判断条件 THEN 结果

ELSE 结果 

END 

查询结果处理

语法格式1

 select    字段名列表  from   库.表    分组|排序|过滤|分页 ;

 语法格式2

 select    字段名列表  from   库.表   where  筛选条件    分组|排序|过滤|分页 ;

    分组 :  对查找到数据做分组处理(表头值相同为一组，只显示一次) 。  

命令    group by   字段名

   

排序  把查找到的数据排队 ，用来排队的字段数据类型应该是 数值类型

 order by   字段名   [asc];     从小到大排序（升序）默认的排序方式

order by   字段名   desc;   从大到小排序（降序）

过滤数据 having

作用对select 查询到的数据 再次做筛选 使用的命令是having 

 格式1   select  字段名列表  from   库.表     having   筛选条件;

 格式2  select  字段名列表  from   库.表  where   条件     having   筛选条件;

 分页 limit

作用:限制查询结果显示行数（默认显示全部的查询结果）

使用SELECT查询时，如果结果集数据量很大，比如1万行数据，放在一个页面显示的话数据量太大，

不如分页显示,比如每次只显示100行  100次显示完

 命令格式1  (只显示查询结果的头几行)

select  字段名列表  from   库.表  limit 数字；

select  字段名列表  from   库.表  where   条件  limit 数字；

命令格式2 （显示查询结果指定范围内的行）

select  字段名列表  from   库.表  limit 数字1，数字2；

select  字段名列表  from   库.表  where   条件  数字1，数字2；

 数字1   表示起始行  

第1行用数字0表示   第2行用数字1表示   第3行用数字2表示

 数字2  表示显示的总行数

     

连接查询

   连接查询分类

按功能分类

       内连接

       外连接

       交叉连接

SELECT 字段列表  FROM 表1 [AS] 别名  [连接类型]   JOIN   表2 [AS] 别名

ON 连接条件   WHERE 筛选条件 ；

 或

SELECT 字段列表  FROM 表1 [AS] 别名  [连接类型]   JOIN   表2 [AS] 别名

ON 连接条件  

WHERE 筛选条件 

GROUP BY 分组  |  HAVING 分组后筛选条件 |  ORDER BY 排序字段  

 连接查询 之 内连接 

语法格式

SELECT  字段列表

FROM  表1  别名

INNER JOIN  表2  别名  ON 连接条件  INNER JOIN  表3  别名  ON 连接条件

[WHERE 筛选条件]

[GROUP BY 分组]

[HAVING 分组后筛选]

[ORDER BY 排序列表]

      连接查询 之 内连接  根据连接条件的不同又分为：

等值连接：使用相等判断做连接条件

非等值连接：连接条件不是相等判断

自连接：  自己连接自己，把1张表当做2张表（需要给表定义别名）

外连接分类如下:

左外连接   LEFT JOIN     

左边表的记录全都显示出来 右边的表只显示与条件匹配记录，右边表比左边表少的记录使用NULL匹配

 

右外连接 RIGHT JOIN 

右边表的记录全都显示出来 左边的表只显示与条件匹配记录，左表比右边表少的记录使用NULL 匹配

 全外连接(mysql不支持，可以使用UNION实现相同的效果) ：合并查询结果

 外连接的应用场景：

1）比较2个表里记录的不同

2）者哪些数据当前表有而另一张表没有。

联合查询

也称联合查询，用来合并查询结果

可以合并同一张的表的查询记录（不同表的查询记录也可合并）

要求查询时，多个select语句的检索到的字段数量必须一致

每一条记录的各字段类型和顺序最好是一致的

UNION关键字默认去重，可以使用UNION ALL包含重复项

 语法格式 1  (SELECT语句 ) UNION (SELECT语句);

语法格式 2  (SELECT语句 ) UNION  ALL (SELECT语句);

 视图

简单

用户无需关心视图中的数据如何查询获得的

视图中的数据已经是过滤好的符合条件的结果集

 安全：用户只能看到视图中的数据

 数据独立

一旦视图结构确定，可以屏蔽表结构对用户的影响

 创建视图的语法格式 

create  view  库.视图名称  as  SQL查询;

create  view  库.视图名称(字段名列表) as  SQL查询;

use   viewdb;   #查看当前库里哪些表是视图

mysql> show table status  where  comment = "view" \G   

查看视图表里的数据是从哪个基表获取的

mysql> show create view viewdb.v1 \G

with  check option  (支持的检查选项)

选项 local   首先满足自身的限制 ，同时要满足基本的限制

选项 cascaded (默认值 )  满足视图自身限制即可

   默认情况下 通过视图修改数据是不受限制

    可以设置通过视图修改数据受限制：限制的方式如下

create view tarena.v21  as

    -> select name , uid from  tarena.user where uid > 10

    -> with check option;

 mysql存储过程

 1 、 什么是存储过程

说白了就是mysql服务的脚本，登录服务器后 要重复执行的命令写成存储过程

存储过程就是mysql服务的脚本。

 2、创建存储过程的命令格式

 语法：create procedure pria()

    -> begin

    -> select  count(*) from   tarena.salary  ;

    -> select  count(*) from   tarena.employees  ;

    -> end

    -> //

mysql> delimiter  ;

 执行 存储过程  pria()

 mysql> call pria();  或  call  tarena.pria();   

 mysql> show procedure status \G   #查看已有的存储过程

删除存储过程

mysql> drop  procedure  库.存储过程名 ;

存储过程进阶

1  变量的使用

    1.1 变量的分类 ：

           系统变量： mysql服务定义包括：

                         全局变量（任意用户连接服务查看到值都一样的）

                       会话变量：连接服务器的用户登录期间使用的变量

                         自定义变量： 连接数据库服务的用户定义包括：

                     用户变量：用户登录数据库服务器，自己定义的变量

                     局部变量 ：在begin 和 end 定义的变量   仅存储过程执行中有效

mysql> show global variables \G      查看所有的全局变量

mysql> show global variables  like  "version_compile_os" ;  #仅查看一个全局变量

修改全局变量的值    set   global   变量名="值"

mysql> select  @@version_compile_os;    输出某个全局变量的值

 show session variables \G    查看连接的所有变量

mysql> show session variables  like   "%关键字%"  \G

   mysql> show session variables  like  "%cache%" ;   仅查看与内存相关的变量

mysql> set session sort_buffer_size=50000;  修改

局部变量的使用 

declare  命令 用来的 定义局部变量    

用户变量的使用

#定义并赋值     mysql> set @age=19 , @name="pangljing" ;

2  参数的使用

create  procedure    名(参数,参数,......)

参数定义的语法格式      参数类型    变量名    数据类型

 参数类型分3种： in     out    inout

in      in类型的参数负责把数据传给存储过程

例如    create     proucedure  p2(  in   x   int )

        begin

          ......

       end

mysql> delimiter //

mysql> create procedure tarena.p3(in  dept_no int)

    -> begin

    -> select dept_id , count(*) as 总人数  from

-> tarena.employees where dept_id=dept_no group by  dept_id;

    -> end

    -> //

mysql> delimiter ;  

mysql> call  p3() ;    不给参数会报错

mysql> call  p3(1) ;  查看部门编号1  的员工人数

mysql> call  p3(3) ;  查看部门编号3  的员工人数

out类型的参数  负责接收存储过程的处理结果。

   存储过程执行结束后 可以调用 out类型的参数 获取存储过程的处理结果

create     proucedure  tarena.p31(  out   x   int )

          begin

          ......

          end

call   tarena.p31(@名)；

     select  @名；

使用INOUT参数(既有in参数的功能又有out参数的功能)

格式1  一个判断条件

IF 条件 THEN

  语句;

END IF;

 格式2  条件不成立的时候执行什么操作

IF 条件 THEN

  语句1;

ELSE

  语句2;

END IF; 

格式3  有多个判断条件

 IF 条件1 THEN

  语句1;

ELSEIF 条件2 THEN

  语句2;

ELSE

  语句3;

END IF;

case语句

 语法格式

CASE 变量|表达式|字段

WHEN 判断的值1 THEN 返回值1;

WHEN 判断的值2 THEN 返回值2;

... ...

ELSE 返回值n;

END CASE;

MySQL存储过程的循环结构的例子：

while 循环结构  条件判断成立时 重复执行同一段代码

              如果第1次判断条件就不成立 会直接结束循环

语法格式

while 判断条件 do     

     代码

end while;

loop  循环结构 

没有判断条件 重复执行同一段代码 只要不人为结束就一直执行 所以被称为死循环

语法格式

loop

代码

end loop；

终止循环

在mysql登录状态下 查看正在执行的命令

mysql>  show  processlist;

 在mysql登录状态下终止命令的执行

mysql>  kill   id号;

repeat循环的例子:

至少循环一次

因为先执行循环体 ，再判断条件（当判断条件成立时继续执行循环体（判断条件不成立为为真），反之结束循环）

 语法格式

repeat

   循环体

   until 判断条件

end repeat;

循环控制语句

leave  (结束循环)

iterate 终止当前循环并开始下次循环

备份与恢复

一、数据的备份与恢复

    1.1  相关概念

           数据备份： 就是把当前服务器的上数据拷贝一份 放到其他的存储设备里

          恢复数据： 放到其他的存储设备里备份 ， 还原丢失的数据。

        数据备份方式：物理备份  、逻辑备份

         数据备份策略 ：

           完全备份 ：备份所有数据：

                        可以是一台数据库服务器上的所有数据

                     也可以是 一个数据库下所有表

                     还可以仅一张表里的所有记录

                  增量备份:   备份上次备份后，新产生的数据。

           差异备份：  备份自完全备份后，新产生的数据。

1.2 备份策略的使用方式：

              完全备份+增量备份

              完全备份+差异备份

1.3 物理备份与恢复

 cp -r /var/lib/mysql  /bakdir/mysql.bak

1.4 逻辑备份与恢复

 mysqldump   -uroot    -p密码   库名   >  /目录名/备份文件名.sql

库名的表示方式：

        库名  表名                     #仅备份库下一张的所有记录  

        库名  表名1    表名2          #一起备份库下2张表的所有记录

         -B  库名                       #仅备份1个库里的所有表

        -B  库名1     库名2           #一起备份2个库里的所有表

        -A  或   --all-databases      #备份服务器上的所有库所有表

完全恢复数据命令格式

]# mysql   -uroot    -p密码   [库名] <  /目录名/备份文件名.sql

mysqldump的备份缺点： 

       1 mysqldump 在备份数据和恢复数据的时候会锁表 

        2  使用 mysqldump备份命令生成备份文件恢复数据，只能

           把数据恢复备份时刻的数据。

 

4实时备份  (启用mysql服务的binlog日志实现)

     binlog日志介绍

     也叫二进制日志 

     是MySQL服务日志文件的一种

     记录在数据库服务器上执行的除查询之外的sql命令

     启用日志能够达到对数据做备份的目的

     搭建MySQL主从同步存储结构的必要条件

     默认MySQL服务没有启用binlog日志

Binlog日志管理

    1、启用binlog日志

    启用数据库50主机的binlog日志文件

                                #vim  /etc/my.cnf

                                   [mysqld]

                                   server_id = 50

                                   log_bin

自定义日志信息

vim /etc/my.cnf            

[mysqld]

server_id =  50

log_bin=/mylog/db50

方法2  ：  创建新日志文件的个数个完全备份数据库的个数相同

例子

[root@host50 ~]# mysqldump  -uroot -p123456 --flush-logs  -B tarena 

> /bakdir/tarena.sql

 [root@host50 ~]# mysqldump  -uroot -p123456 --flush-logs  -B tarena 

 db1  > /bakdir/two.sql

  方法3   数据库管理员root 执行  创建新日志文件的命令

                        mysql> flush logs;

 

binlog日志相关管理命令

#查看正在使用binlog日志名和偏移量（记录sql命令的编号）

mysql>  show  master  status；

 #查看数据库服务器当前已有全部 binlog日志文件

mysql> show binary  logs;

 #删除编号之前的所有日志文件

mysql> purge master logs to "db50.000004";

 #删除当前所有的日志文件重新创建新日志文件和索引文件

mysql> reset master ;

查看日志文件内容 2 种方法：

方法1 使用系统命令 mysqlbinlog 查看

[root@host50 ~]# mysqlbinlog   /mylog/db50.000001   

方法2 使用sql命令查看

mysql> show binlog events in "db50.000001" ;

使用binlog日志文件恢复数据

 命令格式1  :  

]# mysqlbinlog   /目录名/日志文件名   |   mysql  -uroot   -p密码

命令格式2 :  

]# mysqlbinlog  选项   /目录名/日志文件名   |   mysql  -uroot   -p密码

   加了选项后会根据选项指定的范围查看记录的sql命令选项包括

#查看指定偏移量范围内记录的sql命令

--start-position=开始偏移量      --stop-position=结束偏移量

 #查看指定时间范围内记录的sql命令

--start-datetime="yyyy/mm/dd hh:mm:ss"   --stop-datetime="yyyy/mm/dd  hh:mm:ss"

 命令格式1

查看日志的所有内容恢复数据 （恢复所有数据）

适用于日志文件里只记录了 insert 和 update 命令 没有delete 命令的情况下 

]# mysqlbinlog   /目录名/日志文件名   |   mysql  -uroot   -p密码      

命令格式2 

查看日志的部分内容恢复数据（恢复部分数据） 适用于 既有insert 又有delete的情况

]# mysqlbinlog  选项   /目录名/日志文件名   |   mysql  -uroot   -p密码

查看日志文件的格式

mysql> show    variables  like    "binlog_format";

修改日志文件的格式

host50]# vim /etc/my.cnf

   [mysqld]

    binlog_format="mixed"   #自己添加 其他行不需要动

mysql> show variables like "binlog_format";  查看日志格式

 

通过 指定偏移量范围恢复数据

--start-position=偏移量      --stop-position=偏移量

 查看执行命令的偏移量范围

mysql> show binlog events in "db50.000003"; 

mysqlbinlog --start-position=658 --stop-position=2794 /mylog/db50.000003 | mysql -uroot -p654321

通过指定时间范围恢复数据

--start-datetime="yyyy/mm/dd  hh:mm:ss"   --stop-datetime="yyyy/mm/dd  hh:mm:ss"

#  mysqlbinlog    /目录名/日志文件名    #即可以看到命令执行的时间又可以命令执行的偏移量

host50]#  mysqlbinlog  /mylog/db50.000003

使用第三方软件percona提供的备份命令innobackupex 对数据做备份和恢复

       特点:在线热备不锁表 适合生产环境下备份业务。

 第一步：安装提供命令的软件

#yum -y install libev-4.15-1.el6.rf.x86_64.rpm percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm

备份数据命令格式

]#innobackupex  -uroot -p密码  /备份目录名  --no-timestamp

--no-timestamp     可选项，不用系统的日期做存放备份文件的目录名      

恢复数据命令格式

                  第1步 准备恢复数据

                    ]#innobackupex    --apply-log  /备份目录名            

                  第2步 拷贝数据

                    ]#innobackupex    --copy-back  /备份目录名

                   恢复数据的操作步骤：

                  1） 停止数据库服务

                  2） 清空数据库目录

                  3） 准备恢复数据

                  4） 拷贝数据

                  5） 修改数据库目录的所有者者组用户为mysql

                  6)  启动数据库服务

                  7）  管理员root用户登录服务查看数据

   

   恢复单张表

 1) 删除表空间 (表名.ibd) 用来存储表记录的文件 select * from  表;

                        删除表空间就是把数据库目录下没有记录的表名.idb文件删除

 mysql> ALTER  TABLE  库名.表名  DISCARD  TABLESPACE; 

mysql> alter  table  tarena.user discard tablespace;

 2) 导出表信息(生成备份目录下备份数据的信息文件)

]# innobackupex --apply-log --export   数据完全备份目录

host51~]# innobackupex  --apply-log --export  /allbak

3) 拷贝表信息文件到数据库目录下

[root@host51 db51]# cp /allbak/tarena/user.ibd  /var/lib/mysql/tarena/

[root@host51 db51]# cp /allbak/tarena/user.cfg  /var/lib/mysql/tarena/

[root@host51 db51]# cp /allbak/tarena/user.exp  /var/lib/mysql/tarena/

或

[root@host51 db51]# cp /allbak/tarena/user.{ibd,cfg,exp} /var/lib/mysql/tarena/

4) 修改表信息文件的所有者及组用户为mysql

[root@host51 db51]# chown  mysql:mysql /var/lib/mysql/tarena/user.*         

5) 导入表空间

mysql> ALTER  TABLE  库名.表名   IMPORT  TABLESPACE;  

mysql> alter  table  tarena.user  import  tablespace;

6) 删除数据库目录下的表信息文件

[root@host51 db51]# rm -rf /var/lib/mysql/tarena/user.cfg

[root@host51 db51]# rm -rf /var/lib/mysql/tarena/user.exp

7) 查看表记录        

mysql> select  * from tarena.user;  

增量备份数据的命令格式

--incremental  备份新数据并定义新数据存放的目录名

--incremental-basedir  备份新数据参考的备份目录名

]#innobackupex   -uroot     -p密码   --incremental   /目录名  

--incremental-basedir=/目录名   --no-timestamp

 增量恢复数据的命令格式

--incremental-dir 增量备份数据存放的目录名      

 #准备恢复数据

]#innobackupex   --apply-log   --redo-only   /首次备份目录名

 #合并数据 合并的次数与增量备份的顺序要一致

]#innobackupex   --apply-log   --redo-only   /首次备份目录名   --incremental-dir=/目录名

 #拷贝数据

]#innobackupex   --copy-back /完全备份目录

            使用增量备份文件恢复数据的具体操作步骤：

                  1） 停止数据库服务

                  2） 清空数据库目录

                  3）数据

                  4） 合并准备恢复数据 （合并的次数要增量备份的次数一样 并且合并的顺序也有与增量备份的顺序一致）

                  5） 恢复数据

                  6） 修改数据库目录的所有者和组用户为mysql

                  7)  启动服务

                  8） 查看数据