mysql DBA必杀技

2年前 (2016-10-17) admin mysql 0评论 已收录 568℃
                                          mysql DBA必杀技

mysql日常笔记

0-1:myql清屏命令
    linux: ctrl + L  或 \! clear
    windows : ctrl + L
0、更改mysql密码方法:
      1>进入mysql中mysql>更改密码: SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘新密码’);  (未设置密码的更改)
      2>mysqladmin -u root password “新密码”;  
       [root@redhat amoeba]# mysqladmin -uroot password “123456”;  解决办法:
       mysqladmin: connect to server at ‘localhost’ failed
       error: ‘Access denied for user ‘root’@’localhost’ (using password: NO)’
        –>service mysqld stop
        –>mysqld_safe –skip-grant-tables
        –> mysql -uroot -p –>直接回车进入,按第1、3中的更改密码
        –>update user set password=password(‘123456′) where user =’root’;
        –>flush privileges;
      3>mysql -u root  –>use mysql;–>UPDATE user SET     Password = PASSWORD(‘新密码’) WHERE user = ‘root’;
        –>FLUSH PRIVILEGES;  (适用于丢失root密码时候)
      4>忘记本地root的登录密码解决方法
         –>vim /etc/my.cnf
           在[mysqld]配置部分添加一行
          
            skip-grant-tables
         –>service mysqld restart
 
1、查看当前所有存在的数据库:show databases;   
   查询数据库中的所有表:show tables;
2、查看创建好的数据库定义:
   show create database 数据库名; 或show create database 数据库名\G;
3、删除数据库: drop database 数据库名;
   如果数据库存在则删除:drop database if exists 数据库名;   
4、查看mysql所支持的数据库存储引擎:
   show engines; 或 show engines \g;
5、创建数据库:  create database 数据库名;
6、查看默认存储引擎
 
   show variables like ‘storage_engine’;
7、修改默认存储引擎
   打开mysql安装目录中的my.ini文件,
   修改: default-storage-engine=”InnoDB”  
   改为: default-storage-engine=”MyISAM”
   重启mysql即可。
8、Mysql支持的存储引擎
   MySQL5.5支持的引擎有:InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、BLACKHOLE  
      修改表的存储引擎时,会重建表,结构文件,数据文件,索引文件等文件。如果转化为myisam存储引擎,
   mysql会在datadir/db_name/目录下,生成:
   临时表结构文件(.frm)     临时数据文件(.MYD)   临时索引文件(MYI)
   存储引擎也可以称为表类型(即存储和操作此表的类型)
   .ISAM引擎
        ISAM是一个定义明确且历经时间考验的数据表格管理方法,它在设计之时就考虑到数据库被查询的次数要
    远远大于更新的次数。
        优点:ISAM执行读取操作的速度很快,而且不占用大量的内存和存储资源。
        缺点:不支持事务处理,也不能够容错。
    .MyISAM引擎
       MyISAM是MySQL的ISAM扩展格式和缺省的数据库引擎。它不支持事务,行级锁和外键约束的功能。
       1]静态MyISAM
         数据表中的各数据列的长度都是预先固定好的,服务器将自动选择这种表类型。
       2]动态MyISAM
  
         如果数据表中出现varchar、xxxtext或xxxBLOB字段时,服务器将自动选择这种类型。
       这种表存储空间比较小,每条记录的长度不一,多次修改数据后,数据表中的数据可能
       离散的存储在内存中。而进导致执行效率下降。同时内存中也可能会出现很多碎片。
          它提供高速存储和检索,以及全文搜索能力。
       #压缩MyISAM
          静态和动态MyISAM两种类型的标都可以用myisamchk工具压缩。这种类型的标进一步减少了占用的存储,
      但是这种表压缩之后不能再被修改。另外,因为是压缩数据。所以这种表在读取的时候要先实行解压缩。
    .MyISAM Merge引擎
      这种类型是MyISAM类型的一种变体。合并表是将几个相同的MyISAM表合并为一个虚表。常用于日志和数据仓库。
    .InnoDB引擎
     InnoDB引擎可以看做是对MyISAM的进一步更新产品。
     –>它提供了事务(ACID)、行级锁机制和外键约束的功能
     –>它为处理巨大数据量时的最大性能设计,它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。
     –>它为了在主内存中缓存数据和索引而维持它自己的缓冲池。
     –>它存储表&索引在一个单独的表空间中,表空间可以包含数个文件。
     –>InnoDB表可以使任何尺寸,即使在文件尺寸被限制位2GB的操作系统上。
     –>它的查询速度比ISAM和MyISAM引擎慢很多。
     –>memory(heap)引擎
       HEAP允许只驻留在内存里的临时表格(这种类型的数据表值存在于内存中)。
    它使用散列索引,驻留在内存里让HEAP要比ISAM和MYISAM都快,但是它所管理的数据是不稳定的,
    而且如果在关机之前没有进行保存,那么所有的数据都会丢失。在数据行被删除的时候,HEAP也不会浪费大。
    .archive引擎
     这种类型只支持select和 insert语句,而且不支持索引。常应用于日志记录和聚合分析方面。
    .MyISAM与InnoDB总结
     *MyISAM适合: 1】做很多count的计算; 2】插入不频繁,查询非常频繁。 3】没有事务。
     *InnoDB适合: 1】可靠性要求比较高,或者要求事务。 2】表更新和查询都相当的频繁,并且表锁定的机会比较大的情况。
                   不支持FULL TEXT类型的索引。
     *InnoDB中不保存表的具体行数,执行select count(*) from table时,InnoDB要扫描一遍整个表来计算有多少行,但是MyISAM
      只要简单的读出保存好的行数即可。
    
     *对于Auto_increment类型的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中可以和其它字段一起建立联合索引。
     *delete from table时,InnoDB不会重新建立表,而是一行一行的删除。
     *load table from master操作对InnoDB不起作用。
       
     *InnoDB表的行锁也不是绝对的,假如在执行一个SQL语句时MySQl不能确定要扫描的范围,InoDB表同样会锁全表。
      update table set num=1 where name like “%aaa%”
     总结: MyISAM与InnoDB两种最主要的差别就是Innodb支持事务处理与外键和行级锁,而MyISAM不支持,所以MyISAM往往就容易被人认为
        只适合在小项目中使用。
    .查看与更改存储引擎
     show create table 表名;  显示表的创建语句
     show table status like ‘表名’\G; 显示表的当前状态值
     alter table 表名 engine=存储引擎名;
                  
   1>查看系统所支持的引擎
 
    show engines \G;  或  show engines;
   2>查看默认存储引擎
     show variables like ‘storage_engine’;
   3>修改表的存储引擎
     alter table 表名 engine=要修改的引擎;
     或修改配置文件:default-storage-engine= MyISAM
     设置当前绘画的默认存储引擎:SET storage_engine=InnoDB;
   4>指定表的存储引擎:
     create table t_innodb(id int(3))
     engine = innodb;
     –>查看表的详细信息: show table status like ‘t_i%’\G;
9、查看数据表:  show tables;
10、设置联合主键
    create table emp
    (
     name varchar(25),
     deptld int(11),
     salary float,
     primary key(name,deptld)
    );
以XML格式显示表:    
mysql test -u test -X -e  ‘ select * from employees where empid = 1’
11、显示警告: show warnings;
12、查看数据库状态: show status;
13、创建临时表
    create temporary table 表名(name varchar(10));
14、查看表结构:desc 表名/describe 表名;
    查看表详细结构:show create table 表名;  (还可查看存储引擎和字符编码)
                    show create table 表名\G;  加G可使显示结果更直观,不混乱。
    显示表列结构: show columns from 表名;
15、查询时间:select now();
16、查询当前用户:select user();
17、查询数据库版本:select version();
18、查询当前使用的数据库:select database();
19、导入数据库或sql语句
    source f:\a.sql    
20-1、创建主键
     1>创建表并定义主键
      create table tb_emp2
    -> (
    -> id int(11) primary key,
    -> name varchar(25), 
    -> deptId int(11),
    -> salary float
    -> );
     2>创建表,最后定义主键
     create table tb_emp3
    -> (
    ->  id int(11),
    ->  name varchar(25),
    ->  deptId int(11),
    ->  salary float,
    ->  primary key(id)
    -> );
     3>多字段联合主键
     
    create table tb_emp4
    -> (
    ->  name varchar(25),
    ->  deptId int(11),
    ->  salary float,
    ->  primary key(name,deptId)
    -> );
20-2、创建外键约束
     外键用来在两个表的数据之间建立链接,它可以使一列或者多列,一个表可以有一个或多个外键。
     外键对应的是参照完整性,一个表的外键可以是空值,若不为空值,则每一个外键值必须等于另一个表中主键的某个值。
     外键它首先是表中的一个字段,它可以不是本表的主键,但对应另一个表的主键。
    create table tb_emp
    (
     id int(11) primary key,
     name varchar(25),
     deptld int(11),  
     salary float,   
     constraint 外键约束名 foreign key (从表字段) references 主表名(主表字段)
    );  
   例如,创建外键,并在表上创建外键约束
     create table tb_dept1  (主表)
    -> (
    -> id int(11) priamry key,
    -> name varchar(22) not null,
    -> location varchar(50)
    -> );
     create table tb_emp5   (从表)
    -> (
    -> id int(11) primary key,
    -> name varchar(25),
    -> deptId int(11),
    -> salary float,
    -> constraint fk_emp_dept1 foreign key(deptId) references tb_dept1(id)
    -> );
                         
21、增加一个字段
    alter table 表名 add column 列名 列类型;
    增加多个字段
    alter table 表名 add column 列名1 列类型,add columns 列名2 列类型;
   
22、增加一个管理员帐户
    grant all on *.* to 用户名@localhost identified by “password”;
  
23、删除数据库中的数据表
     drop table 表名/ rm -f 数据库/表名.*  (未成功)
    删除数据表中所有数据:delete from 表名;
24、备份数据库 (光标处于盘符下,而不是mysql里)
    mysqldump -u root -p 数据库名>f:\test.sql   –>输入密码即可。
    备份数据库中的表
    
    mysqldump -u root -p 数据库名 表名>f:\test.sql   –>输入密码即可。
25、导入备份的数据库
    mysql -u root -p 数据库名<f:\test.sql   –>输入密码即可
    (注:导入数据库前必须先创建此数据库)
26、创建表时先判断表是否存在
    create table if not exists 表名(…字段);
27、创建新表,并从已有的表中复制表结构
    create table 新表名 select * from 已存表名 where 1<>1;
28、复制表:  create table 新表名 select * from 要复制的表名;
29、修改列的类型
    alter table 表名 modify 列名 新列类型;
    修改列名及列数据类型
    alter table 表名 change 旧列名 新列名  数据类型;
30、约束
   . 非空约束:not null,例如:name varchar(20) not null,
      例如:
    create table tb_emp6
    -> (
    ->  id int(11) primary key,
    ->  name varchar(25) not null,    此字段设置非空约束
    ->  deptId int(11),
    ->  salary float,
    ->  constraint fk_emp_dept2 foreign key (deptId) references tb_dept(id)
    -> );
   .唯一约束(Unique Constraint):unique,要求该列唯一,允许为空,但只能出现一个空值。
            唯一约束可以确保一列或者几列不出现重复值。  
            例如:   name varchar(20) unique,
               或者:constraint sth unique(name)
      例如:直接设置唯一约束
      create table tb_dept2
    -> (
    ->  id int(11) primary key,
    ->  name varchar(22) unique,   设置name字段为唯一
    ->  location varchar(50)
    -> );
     例如:设置完字段后,最后设置唯一约束
    -> create table test1                                                      
    -> (
    ->  id int(11) primary key,
    ->  name varchar(22),
    ->  location varchar(50),
    ->  constraint un_name unique(name)    –设置name字段为唯一约束
    -> );
   .默认约束 default,例如 sex char(2) default ‘男’,   
31、设置表的属性值自动增长
     create table 表名 (id, int(11) primary key auto_increment,name varchar(20));
    插入值:insert into 表名(除id外的列) values(“值1”),(“值2”);
    insert into stu3 (id) values (2);
32、修改表名: alter table 旧表名 rename to 新表名;
33、添加字段: alter table 表名 add column 新列 列类型;
    添加字段并设置新添加的字段为第一个字段:alter table 表名 add 列 列类型 first;
    添加字段并设置新添加的字段在某个字段的后面:alter table 表名add 列 列类型 after 已存在的列;
34、删除字段: alter table 表名 drop 字段名;
35、修改字段的排列位置: alter table 表名 modify 要移动的字段名 字段类型 after 移动到哪个字段名后;
    格式:alter table 表名 modify 要移动的字段名 要移动的字段类型  after  要移动到哪个字段后面:
  
          将age字段移动到name字段后
    例如:alter table 表名 modify age int  after name ;  
    
    Fnumber   varchar(20)
    Fname     varchar(20)
    Fsalary   decimal(10,2)
    Page      int(11)
    移动字段为第一个位置: alter table 表名 modify 字段名  字段类型 first;
36、更改表的存储引擎
    MySQL支持的存储引擎有:MRG_MYISAM、MyISAM、BLACKHOLE、CSV、MEMORY、ARCHIVE、
         默认为:InnoDB,其它有:PERFORMANCE_SCHEMA
    更改方法: alter table 表名 engine=存储引擎;
37、删除表的外键约束
    alter table 从表名 drop foreign key 外键约束名;
    
   (查询外键:show create table 从表名);

38、mysql数据类型
    (1)数值数据类型:tinyint、smallint、mediumint、int(integer)、bigint
       存储需求:     1个字节、2个字节、 3个字节 、    4个字节   、8个字节 
 
       注:1个字节=8bits
       不同整数类型的取值范围
       数据类型               有符号                          无符号
       tinyint                -128~127                        0-255
       smallint               -32768~32767                    0~65535
       mediumint              -8388608~8388607                0~16777215
       int(integer)           -2147483648~2147483647          0~4294967295
 
       bigint       -9223372036854775808~9223372036854775807  0~18446744073709551615   
    (2)日期/时间类型:year、time、date、datetime和 timestamp
       类型名称       日期格式               日期范围                                         存储需求
       year            YYYY                  1901~2155                                         1字节
       time            HH:MM:SS              -838:59:59~838:59:59                              3字节
 
       date            YYYY-MM-DD            1000-01-01~9999-12-3                               3字节
       datetime        YYYY-MM-DD HH:MM:SS   1000-01-01 00:00:00~9999-12-21 23:59:59            8字节
   
       timestamp       YYYY-MM0DD HH:MM:SS   1970-01-01 00:00:01 UTC ~2038-01-18 03:14:07 UTC   4字节  
       .插入系统当前时间:insert into 表名 values(current_time),(now());
       .插入系统当前日期:insert into 表名 values(current_date()),(now());
       .插入日期:insert into 表名 values(‘1998-08-08’),(‘19980808’),(‘20101010’);
       .插入日期和时间:insert into tmp6 values(’98-12-31 11:30:45′),(‘98.12.31 11+30+45’),
                        (’98/12/31 11*30*45′),(’98@12@31 11^30^45′);
       .更改当前时区为东10区
  
         set time_zone=’+10:00′;  (默认为中国东8区,更改后时间比原先增加了2小时)
         (timestamp值的存储是以UTC(世界标准时间)格式保存的,存储时对当前时区进行转换,检索时再转换回当前时区。)
    (3)字符串类型: char、varchar、binary、varbinary、blob、text、enum和set等。
    .浮点数类型和定点数类型
     浮点类型包括:单精度浮点类型(Float)和双精度浮点类型(double)
     定点类型1种:decimal
     浮点类型和定点类型可以用(M,N)来表示,其中M称为精度,N称为标度,表示小数的位数。
     MySQL中的小数类型
  
     float              单精度浮点数            4个字节
     double             双精度浮点数            8个字节
     decimal(M,D),DEC   压缩的“严格”定点数    M+2个字节  
    .mysql中字符串数据类型
     ——————————————————————————————————–
     类型名称              说明                         存储需求
     char(m)       固定长度非二进制字符串               M字节,1<=M<=255
  
     varchar(M)    变长非二进制字符串                   L+1字节,在此L<=M和1<=M<=255
     tinytext      非常小的非二进制字符串               L+1字节,在此L<2^8
     text          小的非二进制字符串                   L+2字节,在此L<2^16
     mediumtext    中等大小的非二进制字符串             L+3字节,在此L<2^24
     longtext      大的非二进制字符串                   L+4字节,在此L<2^32
     enum          枚举类型,只能有一个枚举字符串值     1或2个字节,取决于枚举值的数目(最大值65535)
     set           一个设置,字符串对象可以有零个或     1,2,3,4或8个字节,取决于集合成员的数量(最大64个成员)
                   多个set成员
     ————————————————————————————————————
    (4)二进制类型
       mysql支持两类字符型数据:文本字符和二进制字符串。
       ———————————————————————
       .mysql中的二进制数据类型
        类型名称            说明                        存储需求
        bit(M)              位字段类型                  大约(M+7)/8个字节
        binary(M)           固定长度二进制字符串        M个字节
        varbinary(M)        可变长度二进制字符串        M+1个字节
        tinyblob(M)         非常小的BLOB                L+1字节,在此L<2^8
        blob(M)             小BLOB                      L+2字节,在此L<2^16
        mediumblob(M)       中等大小的BLOB              L+3字节,在此L<2^24
        longblob(M)          非常大的BLOB               L+4字节,在此L<2^32        
        ——————————————————————–
39、常见运算符
     (1)算术运算符
           
         加(+)、减(-)、乘(*)、除(/)、求余(模运算,%)
     (2) 比较运算符
         大于(>)、小于(<)、等于(=)、大于等于(>=)、小于等于(<=)、不等于(!=)
         以及in、between、and、is null、greatest、least、like、regexp等
         .mysql中的比较运算符
          —————————————————————————————–
          运算符             作用         |  运算符             作用         
                                          |
           =                 等于         |  is null            判断一个值是否为null
                                          |
           <=>               安全的等于   |  is not null        判断一个值是否不为null
                                          |
           <>(!=)            不等于       |  least              在有两个或多个参数时,返回最小值
                                          |
           <=                小于等于     |  greatest           当有2个或多个参数时,返回最大值
                                          |
           >=                大于等于     |  between and        判断一个值是否落在两个值之间
                                          |
           >                 大于         |  isnull             与is null作用相同
                                          |
                                          |  in                 判断一个值是in列表中的任意一个值
                                          |  
                                          |  not in             判断一个值不是in列表中的任意一个值
                                          |  
                                          |  like               通配符匹配
                                          |
                                          |  regexp             正则表达式
          ——————————————————————————————–
    (3)逻辑运算符
         1(true)、0(false)、not(!)、and 或者&&、逻辑或(or或者||)、逻辑异或(xor)
    (4)位操作运算符
         与(&)、位或(|)、位非(~)、位异或(^)、左移(<<)、右移(>>)6种
    (5)运算符的优先级
         —————————————————————————————
         优先级              运算符
          最低                =(赋值运算) , :=
                              || , or
                              xor
                              && , and
                              not
                              between, case, when, then, else
                              =(比较运算) , <=> , >=, >, <=, <. <>, !=, is, like, regexp, in
                             
                              |
                              &
                              <<, >>
                              -, + 
                         
                              *, /(DIV), %(MOD)
 
                              ^
                              -(负号), ~(位反转)
          最高                !     
          ———————————————————————————————
40、mysql函数
    .mysql函数分类:数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数和加密函数等其它函数。
    (1)数学函数
         .绝对值函数ABS
           select ABS(2),ABS(-3.3),ABS(-33),结果:2, 3.3 ,33
         .返回圆周率: select pi(); 结果:3.1415927 (保留了7位小数位数)
         .平方根sqrt
 
           select sqrt(9),sqrt(40),sqrt(-49);  结果:3, 6.324555320336759 , null(负数没有平方根)  
         .求余函数MOD(x,y),返回x被y除后的余数。
           select MOD(31,8),MOD(234,10),MOD(45.5,6); 结果:7 , 4 , 3.5
          
         .获取整数的函数
           . ceil(x)、ceiling(x):返回不小于x的最小整数值,返回值转化为一个bigint
             select ceil(-3.35),ceiling(3.35); 结果:-3  , 4
           .floor(x) 返回不大于x的最大整数值,返回值转换为一个bigint。
        
             select floor(-3.35),floor(3.35); 结果:-4 , 3
        .获取随机数的函数rand()和rand(x)
         rand(x)返回一个随机浮点数v,范围在0到1之间(即0≤v≤1.0)。若已指定一个整数参数x,
         则它被用作种子值,用来产生重复序列。
           
         mysql> select rand(),rand(),rand();
         +———————+——————–+——————–+
         | rand()              | rand()             | rand()             |
         +———————+——————–+——————–+
         | 0.06951149932063325 | 0.6171274684529076 | 0.8771028750362833 |
         +———————+——————–+——————–+
        
         mysql> select rand(10),rand(10),rand(11);
        +——————–+——————–+——————-+
        | rand(10)           | rand(10)           | rand(11)          |
        +——————–+——————–+——————-+
        | 0.6570515219653505 | 0.6570515219653505 | 0.907234631392392 |
        +——————–+——————–+——————-+
      
        .round(x):对操作数进行四舍五入操作。
        mysql> select round(-1.14),round(-1.67),round(1.14),round(1.66);
        +————–+————–+————-+————-+
        | round(-1.14) | round(-1.67) | round(1.14) | round(1.66) |
        +————–+————–+————-+————-+
        |           -1 |           -2 |           1 |           2 |
        +————–+————–+————-+————-+
        .round(x,y):对操作数进行四舍五入操作,保留小数点后面指定y位
         mysql> select round(1.38,1),round(1.38,0),round(232.38,-1),round(232.38,-2);
         +—————+—————+——————+——————+
         | round(1.38,1) | round(1.38,0) | round(232.38,-1) | round(232.38,-2) |
         +—————+—————+——————+——————+
         |           1.4 |             1 |              230 |              200 |
         +—————+—————+——————+——————+
         
        .truncate(x,y):返回被舍去至小数点后y位的数字x。若y的值为0,则结果不带有小数点或
         不带有小树部分。若y设为负数,则截去(归零)x小数点左起y位开始后面所有低位的值。
      
         truncate(x,y),直接截取值,并不进行四舍五入。
         
          mysql> select truncate(1.31,1),truncate(1.99,1),truncate(1.99,0),truncate(19.99,-1);
          +——————+——————+——————+——————–+
          | truncate(1.31,1) | truncate(1.99,1) | truncate(1.99,0) | truncate(19.99,-1) |
          +——————+——————+——————+——————–+
          |              1.3 |              1.9 |                1 |                 10 |
          +——————+——————+——————+——————–+
         
        .符号函数sign(x)
             
         sign(x)返回参数的符号,x的值为负、零或正时返回结果依次为-1、0或1
        .幂运算函数pow(x,y)、power(x,y)和exp(x)
         pow(x,y)或者power(x,y)函数返回x的y此乘方的结果值。
         mysql> select pow(2,2),power(2,2),pow(2,-2),power(2,-2);
         +———-+————+———–+————-+
         | pow(2,2) | power(2,2) | pow(2,-2) | power(2,-2) |
         +———-+————+———–+————-+
         |        4 |          4 |      0.25 |        0.25 |
         +———-+————+———–+————-+
         
         exp(3)返回以e为底的3次方
         e是一个常数为:2.71828
         mysql> select exp(3),exp(-3),exp(0);
         +——————–+———————-+——–+
         | exp(3)             | exp(-3)              | exp(0) |
         +——————–+———————-+——–+
         | 20.085536923187668 | 0.049787068367863944 |      1 |
         +——————–+———————-+——–+         
         
         .对数运算函数Log(x)和Log10(x)
          Log(x)返回x的自然对数,x相当于基数e的对数。对数定义域不能为负数。
         
         mysql>  select log(3),log(-3);
         +——————–+———+
         | log(3)             | log(-3) |
         +——————–+———+
         | 1.0986122886681098 |    NULL |
         +——————–+———+
         mysql> select log10(2),log10(100),log10(-100);
         +——————–+————+————-+
         | log10(2)           | log10(100) | log10(-100) |
         +——————–+————+————-+
         | 0.3010299956639812 |          2 |        NULL |
         +——————–+————+————-+
         
         .角度与弧度相互转换的函数radians(x)和degrees(x)
          radians将角度转换为弧度
         mysql> select radians(90),radians(180);
         +——————–+——————-+
         | radians(90)        | radians(180)      |
         +——————–+——————-+
         | 1.5707963267948966 | 3.141592653589793 |
         +——————–+——————-+  
         
          degrees将弧度转换为角度
         mysql> select degrees(pi()),degrees(pi()/2);
         +—————+—————–+
         | degrees(pi()) | degrees(pi()/2) |
         +—————+—————–+
         |           180 |              90 |
         +—————+—————–+
          
        .正弦函数sin(x)和反正弦函数asin(x)
         mysql> select sin(1),round(sin(pi()));
         +——————–+——————+
         | sin(1)             | round(sin(pi())) |
         +——————–+——————+
         | 0.8414709848078965 |                0 |
         +——————–+——————+
         asin(x)返回x的反正弦,即正弦为x的值,若x不在-1到1的范围之内,则返回null.
         函数asin和sin互为反函数,asin(3)中的参数3超出了正弦值的范围。
         mysql> select asin(0.8414709848078965),asin(3);
         +————————–+———+
         | asin(0.8414709848078965) | asin(3) |
         +————————–+———+
         |                        1 |    NULL |
         +————————–+———+
        .正切函数(tan)、反正切函数(atan)和余切函数(cot)
         tan(x)返回x的正切,其中x为给定的弧度值。
         mysql> select tan(0.3),round((pi()/4));
         +———————+—————–+
         | tan(0.3)            | round((pi()/4)) |
         +———————+—————–+
         | 0.30933624960962325 |               1 |
         +———————+—————–+
         atan(x)返回x的反正切,即正切为x的值
          
         mysql> select atan(0.30933624960962325),atan(1);
         +—————————+——————–+
         | atan(0.30933624960962325) | atan(1)            |
         +—————————+——————–+
         |                       0.3 | 0.7853981633974483 |
         +—————————+——————–+ 
         
         cot(x)返回x的余切,cot()函数
         mysql> select cot(0.3),1/tan(0.3),cot(pi()/4);
         +——————–+——————–+——————–+
         | cot(0.3)           | 1/tan(0.3)         | cot(pi()/4)        |
         +——————–+——————–+——————–+
         | 3.2327281437658275 | 3.2327281437658275 | 1.0000000000000002 |
         +——————–+——————–+——————–+      
    (2)字符串函数         
           
         计算字符串长度函数、字符串合并函数、字符串替换函数、
         字符串比较函数、查找指定字符串位置函数
         .计算字符串字符数的函数和字符串长度的函数
           char_length(str)返回值为字符串str所包含的字符个数。一个多字节字符算作一个单字符。
           mysql> select char_length(‘date’),char_length(‘egg’);
           +———————+——————–+
           | char_length(‘date’) | char_length(‘egg’) |
           +———————+——————–+
           |                   4 |                  3 |
           +———————+——————–+
 
            length(str)返回值为字符串的字节长度,使用utf8(unicode的一种变长字符编码,又称万国码)
            编码字符集时,一个汉字是3个字节,一个数字或字母算一个字节。
            mysql> select length(‘date’),length(‘egg’);
            +—————-+—————+
            | length(‘date’) | length(‘egg’) |
            +—————-+—————+
            |              4 |             3 |
            +—————-+—————+
         .合并字符串函数concat(s1,s2,…)、concat_ws(x,s1,s2,…)   
           
          concat(s1,sw,…)返回结果为连接参数产生的字符串,或许有一个或多个参数。如有任何一个参数
          为null,则返回值为null。如果所有参数均为非二进制字符串,则结果为非二进制字符串。如果自变量
          中含有任一二进制字符串,则结果为一个二进制字符串。
          
          mysql> select concat(‘My SQL’,’5.5′),concat(‘My’,null);
          +————————+——————-+
          | concat(‘My SQL’,’5.5′) | concat(‘My’,null) |
          +————————+——————-+
          | My SQL5.5              | NULL              |
          +————————+——————-+ 
          
          concat_ws(x,s1,s2,…),代表concat with separator,是concat()的特殊形式。
           第一个参数x是其它参数的分隔符,分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串,
           也可以是其它 参数。如果分隔符为null,则结果为null。函数会忽略任何分割符参数后的null值。
           mysql> select concat_ws(‘_’,’lst’,’2nd’,’3rd’),concat_ws(‘*’,’lst’,null,’3rd’);
           +———————————-+———————————+
           | concat_ws(‘_’,’lst’,’2nd’,’3rd’) | concat_ws(‘*’,’lst’,null,’3rd’) |
           +———————————-+———————————+
           | lst_2nd_3rd                      | lst*3rd                         |
           +———————————-+———————————+          
         .替换字符串的函数insert(s1,x,len,s2)
          insert(s1,x,len,s2)返回字符串s1,其子字符串起始于x位置和被字符串s2取代的len字符。
          如果x超过字符串长度,则返回值为原始字符串。假如len的长度大于其它字符串的长度,则从
          位置x开始替换。若任何一个参数为null,则返回值为null。
          
          mysql> select insert(‘Quest’,2,4,’What’) AS col1,
          -> insert (‘Quest’,-1,4,’What’) AS col2,
          -> insert(‘Quest’,3,100,’Wh’) AS col3;
          +——-+——-+——+
          | col1  | col2  | col3 |
          +——-+——-+——+
          | QWhat | Quest | QuWh |
          +——-+——-+——+
         .字母大小写转换函数
         lower(str)或者lcase(str)可以将字符串str中的字母全部转换成小写字母。
     
         mysql> select lower(‘BEAUTIFUL’),lcase(‘Well’);
         +——————–+—————+
         | lower(‘BEAUTIFUL’) | lcase(‘Well’) |
         +——————–+—————+
         | beautiful          | well          |
         +——————–+—————+
         
         upper(str)或者ucase(str)可以将字符串str中的字母字符全部转换成大写字母
         mysql> select upper(‘black’),ucase(‘BLacK’);
         +—————-+—————-+
         | upper(‘black’) | ucase(‘BLacK’) |
         +—————-+—————-+
         | BLACK          | BLACK          |
         +—————-+—————-+
  
        .获取指定长度的字符串的函数left(s,n)和right(s,n)
         left(s,n)返回字符串s开始的最左边n个字符。
         mysql> select left(‘football’,5);
        +——————–+
        | left(‘football’,5) |
        +——————–+
        | footb              |
        +——————–+      
        
         right(s,n)返回字符串str最右边n个字符。
         mysql> select right(‘football’,4);
         +———————+
         | right(‘football’,4) |
         +———————+
         | ball                |
         +———————+
     
        .填充字符串的函数lpad(s1,len,s2)和rpad(s1,len,s2)
         lpad(s1,len,s2)返回字符串s1,其左边字符串s2填补到len字符长度。假如
         
         s1的长度大于len,则返回值被缩短至len字符。
         mysql> select rpad(‘hello’,4,’?’),rpad(‘hello’,10,’?’);
         +———————+———————-+
         | rpad(‘hello’,4,’?’) | rpad(‘hello’,10,’?’) |
         +———————+———————-+
         | hell                | hello?????           |
         +———————+———————-+  
        .删除空格的函数ltrim(s)、rtrim(s)和trim(s)
         ltrim(s)返回字符串s,字符串左侧空格字符被删除,而右侧的空格不会被删除。
         mysql> select ‘( book )’,concat(‘(‘,LTRIM(‘ book ‘),’)’);
         +———-+———————————+
         | ( book ) | concat(‘(‘,LTRIM(‘ book ‘),’)’) |
         +———-+———————————+
         | ( book ) | (book )                         |
         +———-+———————————+    
 
         rtrim(s)返回字符串s,字符串右侧空格字符被删除。
        mysql> select concat(‘(‘, RTRIM(‘ book  ‘),’)’);
        +———————————–+
        | concat(‘(‘, RTRIM(‘ book  ‘),’)’) |
        +———————————–+
        | ( book)                           |
        +———————————–+
         
        trim(s)删除字符串s两侧的空格
        mysql> select  concat(‘(‘,  TRIM(‘ book ‘),’)’);
        +———————————-+
        | concat(‘(‘,  TRIM(‘ book ‘),’)’) |
        +———————————-+
        | (book)                           |
        +———————————-+
        .删除指定字符串的函数trim(s1 from s)
         trim(s1 from s)删除字符串s中两端所有的子字符串s1。s1为可选项,
         在未指定情况下删除空格。
        mysql> select trim(‘xy’ from ‘xyxboxyokxxyxy’);
        +———————————-+
        | trim(‘xy’ from ‘xyxboxyokxxyxy’) |
        +———————————-+
        | xboxyokx                         |
        +———————————-+
        .重复生成字符串的函数repeat(s,n)
         repeat(s,n)返回一个由重复的字符串s组成的字符串,字符串s的数目等于n。若n<=0,
         则返回一个空字符串。若s或n为null,则返回null。
        
         mysql> select repeat(‘MySQL’,3);
         +——————-+
         | repeat(‘MySQL’,3) |
         +——————-+
         | MySQLMySQLMySQL   |
         +——————-+
         
        .空格函数space(n)和替换函数replace(s,s1,s2)
         space(n)返回一个由n个空格组成的字符串
         mysql> select concat(‘(‘,space(6),’)’);
         +————————–+
         | concat(‘(‘,space(6),’)’) |
         +————————–+
         | (      )                 |
         +————————–+
        
         replace(s,s1,s2)使用字符串s2替代字符串s中所有的字符串s1。
         mysql> select replace(‘xxx.mysql.com’,’x’,’w’);
         +———————————-+
         | replace(‘xxx.mysql.com’,’x’,’w’) |
         +———————————-+
         | www.mysql.com                    |
         +———————————-+
        .比较字符串大小的函数strcmp(s1,s2)
         strcmp(s1,s2)若所有的字符串均相同,则返回0;若根据当前分类次序,第一个参数小于
         第二个,则返回-1,其它情况返回1.
         mysql> select strcmp(‘txt’,’txt2′),strcmp(‘txt2′,’txt’),strcmp(‘txt’,’txt’);
        +———————-+———————-+———————+
        | strcmp(‘txt’,’txt2′) | strcmp(‘txt2′,’txt’) | strcmp(‘txt’,’txt’) |
        +———————-+———————-+———————+
        |                   -1 |                    1 |                   0 |
        +———————-+———————-+———————+
        .获取子串的函数substring(s,n,len)和mid(s,n,len)
         substring(s,n,len)带有len参数的格式,从字符串s返回一个长度同len字符相同的子字符串,
         起始于位置n。也可能对n使用一个负值。假若这样,则子字符串的位置起始于字符串结尾的n
         字符,即倒数第n个字符,而不是字符串的开头位置。
     
         mysql> select substring(‘breakfast’,5) AS coll,
         -> substring(‘breakfast’,5,3) AS col2,
         -> substring(‘lunch’,-3) AS col3,
         -> substring(‘lunch’,-5,3) AS col4;
         +——-+——+——+——+
         | coll  | col2 | col3 | col4 |
         +——-+——+——+——+
         | kfast | kfa  | nch  | lun  |
         +——-+——+——+——+
         
         使用mid()函数获取指定位置处的子字符串,mid和substring的结果是一样的。
         mysql> select mid(‘breakfast’,5) as col1,
         -> mid(‘breakfast’,5,3) as col2,
         -> mid(‘lunch’,-3) as col3,
         -> mid(‘lunch’,-5,3) as col4;
         +——-+——+——+——+
         | col1  | col2 | col3 | col4 |
         +——-+——+——+——+
         | kfast | kfa  | nch  | lun  |
         +——-+——+——+——+   
        .匹配子串开始位置的函数
 
         locate(str1,str)、position(str1 in str)和instr(str,str1)3个函数作用相同,返回子字符串
         str1在字符串str中的开始位置。
         mysql> select locate(‘ball’,’football’),position(‘ball’ in ‘football’),instr(‘football’,’ball’);
         +—————————+——————————–+————————–+
         | locate(‘ball’,’football’) | position(‘ball’ in ‘football’) | instr(‘football’,’ball’) |
         +—————————+——————————–+————————–+
         |                         5 |                              5 |                        5 |
         +—————————+——————————–+————————–+
         
        .字符串逆序的函数reverse(s)
         reverse(s)将字符串s反转,返回的字符串的顺序和s字符串顺序相反。
      
         mysql> select reverse(‘abc’);
         +—————-+
         | reverse(‘abc’) |
         +—————-+
         | cba            |
         +—————-+
       .返回指定位置的字符串的函数
        elt(n,字符串1,字符串2,字符串3,…,字符串N)若N=1,则返回值为字符串1,若N=2,则返回值
        为字符串2,依次类推。若N小于1或者大于参数的数目,则返回值为null
        mysql> select elt(3,’lst’,’2nd’,’3rd’),elt(3,’net’,’os’);
        +————————–+——————-+
        | elt(3,’lst’,’2nd’,’3rd’) | elt(3,’net’,’os’) |
        +————————–+——————-+
        | 3rd                      | NULL              |
        +————————–+——————-+
       .返回指定位置的字符串的函数field(s,s1,s2,…)
        field(s,s1,s2,…)返回字符串s在列表s1,s2,…中第一次出现的位置,在找不到s的情况下,返回值为0.
        如果s为null,则返回值为0,原因是null不能同任何值进行同等比较。
        mysql> select field(‘Hi’,’hihi’,’Hey’,’Hi’,’bas’) as col1,
         -> field(‘Hi’,’Hey’,’Lo’,’Hilo’,  ‘foo’) as col2;
         +——+——+
         | col1 | col2 |
         +——+——+
         |    3 |    0 |
         +——+——+
       .返回子串位置的函数find_in_set(s1,s2)
        find_in_set(s1,s2)返回字符串s1在字符串列表s2中出现的位置,字符串列表时一个由多个逗号‘,’分开
       的字符串组成的列表。如果s1不在s2或s2为空字符串,则返回值为0。如果任意一个参数为null,则返回值为
       null。这个函数在第一个参数包含一个逗号‘,’时将无法正常运行。
       mysql> select find_in_set(‘Hi’,’hihi,Hey,Hi,bas’);
       +————————————-+
       | find_in_set(‘Hi’,’hihi,Hey,Hi,bas’) |
       +————————————-+
       |                                   3 |
       +————————————-+     
       .选取字符串的函数make_set(x,s1,s2,…)
        make_set(x,s1,s2,…)返回由x的二进制数指定的相应位的字符串组成的字符串,s1对应比特1,s2对应比特
        01依次类推。s1,s2…中的null值不会被添加到结果中。
    (3)日期和时间函数
       .获取当前日期的函数和获取当前时间的函数
        curdate()和current_date()函数作用相同,将当前日期按照’YYYY-MM-DD’或YYYYMMDD格式的值返回,具体格式
        根据函数用在字符串或是数字语境中而定。
       mysql> select curdate(),current_date(),curdate()+0;
       +————+—————-+————-+
       | curdate()  | current_date() | curdate()+0 |
       +————+—————-+————-+
       | 2013-10-05 | 2013-10-05     |    20131005 |
       +————+—————-+————-+
        使用时间函数获取系统当前时间
       mysql> select curtime(),current_time(),curtime()+0;
      +———–+—————-+————–+
      | curtime() | current_time() | curtime()+0  |
      +———–+—————-+————–+
      | 08:39:04  | 08:39:04       | 83904.000000 |
      +———–+—————-+————–+
       .获取当前日期和时间的函数
       current_timestamp()、localtime()、now()和sysdate()4个函数的作用相同,均返回当前日期和时间值,
       格式为‘YYYY-MM-DD HH:MM:SS’或 YYYYMMDDHHMMSS,具体格式根据函数用在字符串或数字语境中而定。
       mysql> select current_timestamp(),localtime(),now(),sysdate();
       +———————+———————+———————+———————+
       | current_timestamp() | localtime()         | now()               | sysdate()           |
       +———————+———————+———————+———————+
       | 2013-10-05 08:44:45 | 2013-10-05 08:44:45 | 2013-10-05 08:44:45 | 2013-10-05 08:44:45 |
       +———————+———————+———————+———————+     
       .UNIX时间戳函数
       unix_timestamp(date)若无参数调用,则返回一个Unix时间戳(‘1970-01-01 00:00:00’ GMT之后的秒数)作为
       无符号整数。其中GMT(Green wich mean time,为格林尼治标准时间)。若用date来调用UNIX_TIMESTAMP(),它
       会将参数值以‘1970-01-01 00:00:00’GMT后的秒数的形式返回。date可以是一个dae字符串、datetime字符串、
       timestamp或一个当地时间的YYMMDD或YYYYMMDD格式的数字。
       mysql> select unix_timestamp(),unix_timestamp(now()),now();
       +——————+———————–+———————+
       | unix_timestamp() | unix_timestamp(now()) | now()               |
       +——————+———————–+———————+
       |       1380934422 |            1380934422 | 2013-10-05 08:53:42 |
       +——————+———————–+———————+
       from_unixtime(date)函数把unix时间戳转换为普通格式的时间,与unix_timestamp(date)函数互为反函数
       mysql> select from_unixtime(‘1311476091’);
       +—————————–+
       | from_unixtime(‘1311476091’) |
       +—————————–+
       | 2011-07-24 10:54:51         |
       +—————————–+
       .返回UTC日期的函数和返回UTC时间的函数
        UTC_DATE()函数返回当前UTC(世界标准时间)日期值,其格式为‘YYYY-MM-DD’或YYYYMMDD,具体格式取决于
        函数是否用在字符串或数字语境中。
        mysql> select utc_date(),utc_date()+0;
        +————+————–+
        | utc_date() | utc_date()+0 |
        +————+————–+
        | 2013-10-05 |     20131005 |
        +————+————–+       
       
        UTC_TIME()返回当前UTC时间值,其格式为’HH:MM:SS’或HHMMSS,具体格式取决于函数是否用在字符串或数字语境中。
        mysql> select utc_time(),utc_time()+0;
        +————+————–+
        | utc_time() | utc_time()+0 |
        +————+————–+
        | 01:35:21   | 13521.000000 |
        +————+————–+
       .获取月份的函数month(date)和monthname(date)
        month(date)函数返回date对应的月份,范围值从1~12
        mysql> select month(‘2011-02-13’);
        +———————+
        | month(‘2011-02-13’) |
        +———————+
        |                   2 |
        +———————+
        monthname(date)函数返回日期date对应月份的英文全名
 
        mysql> select monthname(‘2011-02-13’);
        +————————-+
        | monthname(‘2011-02-13’) |
        +————————-+
        | February                |
        +————————-+
     
        使用monthname()函数返回指定日期中的月份的名称。
        mysql> select monthname(‘2011-02-13’);
        +————————-+
        | monthname(‘2011-02-13’) |
        +————————-+
        | February                |
        +————————-+
       .获取星期的函数dayname(d)、dayofweek(d)和weekday(d)
       
        dayname(d)函数返回d对应的工作日的英文名称
        mysql> select dayname(‘2011-02-13’);
        +———————–+
        | dayname(‘2011-02-13’) |
        +———————–+
        | Sunday                |
        +———————–+
        
        dayofweek(d)函数返回d对应的一周中的索引(位置)。1表示周日,
        2表示周一,..,7表示周六
        
        mysql> select dayofweek(‘2011-02-13’);
        +————————-+
        | dayofweek(‘2011-02-13’) |
        +————————-+
        |                       1 |
        +————————-+
        使用weekday(d)返回d对应的工作日索引。0表示周一,1表示周二,…6表示周日
        mysql> select weekday(‘2013-10-05 10:10’),weekday(‘2013-10-04’);
        +—————————–+———————–+
        | weekday(‘2013-10-05 10:10’) | weekday(‘2013-10-04’) |
        +—————————–+———————–+
        |                           5 |                     4 |
        +—————————–+———————–+
       .获取星期数的函数week(d)和weekofyear(d)
        week(d)计算日期d是一年中的第几周。week()的双参数形式允许指定该星期是否起始于周日
        或周一,以及返回值的范围是否为从0-53或从1-53。若mode参数被省略,则使用default_week_format
        系统自变量的值。
        ——————————————————————-
                                 week函数中mode参数取值
        Mode     一周的第一天      范围           Week 1为第一周…
     
         0           周日          0-53           本年度中有一个周日
         1           周一          0-53           本年度中有3天以上
         2           周日          1-53           本年度中有一个周日
         3           周一          1-53           本年度中有3天以上
         4           周日          0-53           本年度中有3天以上
         5           周一          0-53           本年度中有一个周一
         6           周日          1-53           本年度中有3天以上
         7           周一          1-53           本年度中有一个周一
         ———————————————————————
        使用week()函数查询指定日期是一年中的第几周。
        mysql> select week(‘2011-02-20’),week(‘2011-02-20’),week(‘2011-02-20’,1);
        +——————–+——————–+———————-+
        | week(‘2011-02-20’) | week(‘2011-02-20’) | week(‘2011-02-20’,1) |
        +——————–+——————–+———————-+
        |                  8 |                  8 |                    7 |
        +——————–+——————–+———————-+
 
        第二个参数为1,指定一周的第一天为周一,返回值为7,可以看到,第二个参数的不同,
 
        返回的结果页不同,使用不同的参数的原因是不同地区和国家的习惯不同,每周的第一天并不相同。
        mysql>  select week(‘2011-02-20’,3),weekofyear(‘2011-02-20’);
        使用weekofyear()查询指定日期是一年中的第几周。
        +———————-+————————–+
        | week(‘2011-02-20’,3) | weekofyear(‘2011-02-20’) |
        +———————-+————————–+
        |                    7 |                        7 |
        +———————-+————————–+
       .获取天数的函数dayofyear(d)和dayofmonth(d)
        dayofyear(d)函数返回d是一年中的第几天,范围是从1~366
        mysql> select dayofyear(‘2011-02-20’);
        +————————-+
        | dayofyear(‘2011-02-20’) |
        +————————-+
        |                      51 |
        +————————-+
        dayofmonth(d)函数返回d是一个月中的第几天,范围是从1~31
        mysql> select dayofmonth(‘2011-02-20’);
        +————————–+
        | dayofmonth(‘2011-02-20’) |
        +————————–+
        |                       20 |
        +————————–+
       .获取年份、季度、小时、分钟和秒数的函数
        year(date)返回date对应的年份,范围是1970-2069,00-69转换为‘2000-2069’,
        ‘70-99’转换为‘1970-1999’
        mysql> select year(’11-02-03′),year(’96-02-03′);
        +——————+——————+
        | year(’11-02-03′) | year(’96-02-03′) |
        +——————+——————+
        |             2011 |             1996 |
        +——————+——————+
        
        quarter(date)返回date对应的一年中的季度值,范围从1-4.
        mysql> select quarter(’11-04-01′);
        +———————+
        | quarter(’11-04-01′) |
        +———————+
        |                   2 |
        +———————+
       
        minute(time)返回time对应的分钟数,范围是从0~59
        mysql> select minute(’11-02-03 10:10:03′);
        +—————————–+
        | minute(’11-02-03 10:10:03′) |
        +—————————–+
        |                          10 |
        +—————————–+
        second(time)返回time对应的秒数,范围是从0~59
        mysql> select second(’10:05:03′);
        +——————–+
        | second(’10:05:03′) |
        +——————–+
        |                  3 |
        +——————–+
       .获取日期的指定值的函数extract(type form date)
        extract(type from date)函数所使用的时间间隔类型说明符同date_add或
        date_sub()的相同,但它从日期中提取一部分,而不是执行日期运算。
        mysql> select extract(year from ‘2011-07-02’) as col1,
        -> extract(year_month from ‘2011-07-12 01:02:03’) as col2,
        -> extract(day_minute from ‘2011-07-12 01:02:03’) as col3;
        +——+——–+——–+
        | col1 | col2   | col3   |
        +——+——–+——–+
        | 2011 | 201107 | 120102 |
        +——+——–+——–+
       .时间和秒钟转换的函数
        time_to_sec(time)返回已转换为秒的time参数,转换公式为:小时*3600+分钟*60+秒。
       
        mysql> select time_to_sec(’23:23:00′);
        +————————-+
        | time_to_sec(’23:23:00′) |
        +————————-+
        |                   84180 |
        +————————-+
        sec_to_time(seconds)返回被转化为小时、分钟和秒数的seconds参数值,其格式为’HH:MM:SS’
        或HHMMSS,具体格式根据该函数是否用在字符串或数字语境中而定。
        mysql> select sec_to_time(2345),sec_to_time(2345)+0,
        -> time_to_sec(’23:23:00′),sec_to_time(84180);
        +——————-+———————+————————-+——————–+
        | sec_to_time(2345) | sec_to_time(2345)+0 | time_to_sec(’23:23:00′) | sec_to_time(84180) |
        +——————-+———————+————————-+——————–+
        | 00:39:05          |         3905.000000 |                   84180 | 23:23:00           |
        +——————-+———————+————————-+——————–+
 
      .计算日期和时间的函数
        计算日期和时间的函数有:date_add()、adddate()、date_sub()、subdate()、addtime()、subtime()
        和date_diff()。
     
        date_add(date,interval expr type)和date_sub(date,interval expr type),其中date是一个datetime或
        date值,用来指定起始时间。expr是一个表达式,用来指定从起始日期添加或减去的时间间隔值。expr是一个
        字符串:对于负值的时间间隔,它可以以一个负号‘_’开头。type为关键词,它指示了表达式被解释的方式。
        —————————————————————————–
                              mysql中计算日期和时间的格式
         type值                          预期的 expr格式
 
         microsecond                      microseconds
         second                           seconds
         minute                           minutes
         hour                             hours
         day                              days
         week                             weeks
         month                            months
         quater                           quarters
         year                             years
         second_microsecond               ‘seconds.microseconds’
         minute_microsecond               ‘minutes.microseconds’
         minute_second                    ‘minutes:seconds’
         hour_microsecond                 ‘hours.microseconds’
         hour_second                      ‘hours:minutes:seconds’
         hour_minute                      ‘hours:minutes’
         day_microsecond                  ‘days.microseconds’
         day_second                       ‘days hours:minutes:seconds’
         day_minute                       ‘days hours:minutes’
         day_hour                         ‘days hours’
         year_month                       ‘years-monthsr’
         ———————————————————————
         
         date_add(date,interval expr type)或者adddate(date,interval expr type),两个函数
         作用相同,执行日期的加运算。
         mysql> select date_add(‘2010-12-31 23:59:59’,interval 1 second) as col1,
         -> adddate(‘2010-12-31 23:59:59’,interval 1 second) as col2,
         -> date_add(‘2010-12-31 23:59:59’,interval ‘1:1’ minute_second) as col3;
         +———————+———————+———————+
         | col1                | col2                | col3                |
         +———————+———————+———————+
         | 2011-01-01 00:00:00 | 2011-01-01 00:00:00 | 2011-01-01 00:01:00 |
         +———————+———————+———————+
         date_add和date_sub在指定修改的时间段时,也可以指定负值,负值代表相减,即返回以前
         的日期和时间。
      
         addtime(date,expr)函数将expr值添加到date,并返回修改后的值,date是一个日期或者日期
         时间表达式,而expr是一个时间表达式。         
         mysql> select addtime(‘2000-12-31 23:59:59′,’1:1:1′),addtime(’02:02:02′,’02:00:00’);
         +—————————————-+——————————–+
         | addtime(‘2000-12-31 23:59:59′,’1:1:1′) | addtime(’02:02:02′,’02:00:00’) |
         +—————————————-+——————————–+
         | 2001-01-01 01:01:00                    | 04:02:02                       |
         +—————————————-+——————————–+
 
         subtime(date,expr)函数中date减去expr值,并返回修改后的值,date是一个日期或者日期时间表达式,
         而expr是一个时间表达式。
         mysql> select subtime(‘2000-12-31 23:59:59′,’1:1:1′),subtime(’02:02:02′,’02:00:00’);
         +—————————————-+——————————–+
         | subtime(‘2000-12-31 23:59:59′,’1:1:1′) | subtime(’02:02:02′,’02:00:00’) |
         +—————————————-+——————————–+
         | 2000-12-31 22:58:58                    | 00:02:02                       |
         +—————————————-+——————————–+
      
         datediff(date1,date2)返回起始时间date1和结束时间date2之间的天数。date1和date2为日期或date-and-time
         表达式,计算中只用到这些值的日期部分。datediff()函数返回date1-date2后的值。
      .将日期和时间格式化的函数
       date_format(date,format)根据format指定的格式显示date值,
       ————————————————————–
                                 date_format时间日期格式
       说明符                说明   
 
          %a                 工作日的缩写名称(Sun..Sat)
          %b                 月份的缩写名称(Jan..Dec)
          %c                 月份,数字形式(0..12)
          %D                 带有英语后缀的该月日期(0th,1st,2nd,3rd,…)
          %d                 该月日期,数字形式(00..31)
          %e                 该月日期,数字形式(0..31)
          %f                 微秒(000000..999999)
          %H                 以2位数表示24小时(00..23)
 
          %h,%I              以2位数表示12小时(01..12)
          %i                 分钟,数字形式(00..59)
          %j                 一年中的天数(001..366)
          %k                 以24小时(0..23)
          %l                 以12小时(1..12)
  
          %M                 月份名称(January..December)
 
          %m                 月份,数字形式(00..12)
          %p                上午(AM)或下午(PM)
          %r                时间,12小时制(小时 hh:分钟 mm:秒数 ss 后加 AM 或 PM)
          %S,%s             以2位数形式表示秒(00..59)
          %T                时间,24小时制(小时 hh:分钟 mm:秒数 ss)
          %U                周(00..53),其中周日为每周第第一天
          %u                周(00..53),其中周一为每周的第一天
          %V                周(01..53),其中每日为每周的第一天:和%X同时使用
          %v                周(01..53),其中周一为每周的第一天:和%x同时使用
          %W                工作日名称(周日..周六)
          %w                一周中的每日(0=周日..6=周六)
          %X                该周的年份,其中周日为每周的第一天:数字形式,4位数:和%V同时使用
          %x                该周的年份,其中周一为每周的第一天:数字形式,4位数:和%v同时使用
          %Y                4位数形式表示年份
          %y                2位数形式表示年份
          %%                ‘%’文字字符w
        ——————————————————————————————–
        使用date_format()函数格式化输出日期和时间值。
        mysql> select date_format(‘1997-10-04 22:23:00′,’%W %M %Y’) as col2,
        -> date_format(‘1997-10-04 22:23:00′,’%D %y %a %d %m %b %j’) as col2;
        +———————–+————————–+
        | col2                  | col2                     |
        +———————–+————————–+
        | Saturday October 1997 | 4th 97 Sat 04 10 Oct 277 |
        +———————–+————————–+
        mysql> select  date_format(‘1997-10-04 22:23:00′,’%H:%i:%s’) as col3,
        -> date_format(‘1999-01-01′,’%X %V’) as col4;
        +———-+———+
        | col3     | col4    |
        +———-+———+
        | 22:23:00 | 1998 52 |
        +———-+———+
        time_format(time,format)根据format字符串安排time值的格式。format字符串可能仅会
        处理包含小时、分钟和秒的格式说明符,其它说明符产生一个null值或0。若time值包含一个
        大于23的小时部分,则%H和%k小时格式说明符会产生一个大于0..23的通常范围的值。
        mysql> select time_format(’16:00:00′,’%H%k%h%I%l’);
        +————————————–+
        | time_format(’16:00:00′,’%H%k%h%I%l’) |
        +————————————–+
        | 161604044                            |
        +————————————–+
        
        get_format(val_type,format_type)返回日期时间字符串的显示格式,val_type表示日期数据类型,
        包括date、datetime和time:format_type表示格式化显示类型,包括eur、interval、iso、jis、usa。
        get_format根据两个值类型组合返回的字符串显示格式。
        ————————————————————————-
                            get_format返回的格式字符串
         值类型                格式化类型                显示格式字符串
         date                     eur                     %d.%m.%Y
         date                     interval                %Y%m%d
         date                     iso                     %Y-%m-%d
         date                     jis                     %Y-%m-%d
         date                     usa                     %m.%d.%Y
         time                     eur                     %H.%i.%s
         time                     interval                %H%i%s
         time                     iso                     %H:%i:%s
         time                     jis                     %H:%i:%s
         time                     usa                     %h:%i:%s %p
         datetime                 eur                     %Y-%m-%d %H.%i.%s
         datetime                 interval                %Y%m%d%H%i%s
         datetime                 iso                     %Y-%m-%d %H:%i:%s
         datetime                 jis                     %Y-%m-%d %H:%i:%s
         datetime                 usa                     %Y-%m-%d %H.%i.%s
        ————————————————————————–
        
        使用get_format()函数显示不同格式化类型下的格式字符串
        mysql> select get_format(date,’eur’),get_format(date,’usa’);
        +————————+————————+
        | get_format(date,’eur’) | get_format(date,’usa’) |
        +————————+————————+
        | %d.%m.%Y               | %m.%d.%Y               |
        +————————+————————+
        在date_format()函数中,使用get_format函数返回的显示格式字符串来显示指定的日期值。
         
        mysql> select date_format(‘2000-10-05 22:23:00′,get_format(date,’usa’));
        +———————————————————–+
        | date_format(‘2000-10-05 22:23:00′,get_format(date,’usa’)) |
        +———————————————————–+
        | 10.05.2000                                                |
        +———————————————————–+
    (4)条件判断函数
      .if(expr,v1,v2)函数
       if(expr,v1,v2)如果表达式expr是true(expr<>0 and expr <>null),则if()的返回值为v1;
       否则返回值为v2,if()的返回值为数字值或字符串值,如果v1或v2中只有一个明确是null,
       则if()函数的结果类型为非null表达式的结果类型,具体情况视其所在语境而定。
        mysql> select if(1>2,2,3),
        -> if(1<2,’yes’,’no’),
        -> if(strcmp(‘test’,’test1′),’no’,’yes’);
        +————-+——————–+—————————————+
        | if(1>2,2,3) | if(1<2,’yes’,’no’) | if(strcmp(‘test’,’test1′),’no’,’yes’) |
        +————-+——————–+—————————————+
        |           3 | yes                | no                                    |
        +————-+——————–+—————————————+
      .ifnull(v1,v2)函数
       ifnull(v1,v2)假如v1不为null, 则ifnull()的返回值为v1;否则其返回值为v2。ifnull()的
       返回值是数字或是字符串,具体情况取决于其所在的语境。
       mysql> select ifnull(1,2),ifnull(null,10),ifnull(1/0,’wrong’);
       +————-+—————–+———————+
       | ifnull(1,2) | ifnull(null,10) | ifnull(1/0,’wrong’) |
       +————-+—————–+———————+
       |           1 |              10 | wrong               |
       +————-+—————–+———————+
      .case函数
       case expr when v1 then r1 [when v2 then r2][else rn]end
       该函数表示,如果expr值等于某个vn,则返回对应位置then后面的结果。如果与所有值都不相等,
       则返回else后面的rn。
       mysql>  select case 2 when 1 then ‘one’ when 2 then ‘two’ else ‘more’ end;
       +————————————————————+
       | case 2 when 1 then ‘one’ when 2 then ‘two’ else ‘more’ end |
       +————————————————————+
       | two                                                        |
       +————————————————————+
       
       mysql> select case when  1<0 then ‘true’ else ‘false’ end;
       +———————————————+
       | case when  1<0 then ‘true’ else ‘false’ end |
       +———————————————+
       | false                                       |
       +———————————————+   
    (5)系统信息函数
      .获取mysql版本号、连接数和数据库名的函数
 
       查看当前mysql版本号
       mysql> select version();
       +————————+
       | version()              |
       +————————+
       | 6.0.11-alpha-community |
       +————————+
       查看当前用户的连接数,每个连接都有各自唯一的ID。
       mysql> select connection_id();
       +—————–+
       | connection_id() |
       +—————–+
       |               6 |
       +—————–+
       show processlist;show full processlist;
       processlist命令的输出结果显示了有哪些线程在运行,不仅可以查看当前所有
       的连接数,还可以查看当前的连接状态,帮助识别出有问题的查询语句等。
       mysql> show processlist;
       +—-+——+—————–+——-+———+——+——-+——————+
       | Id | User | Host            | db    | Command | Time | State | Info             |
       +—-+——+—————–+——-+———+——+——-+——————+
       |  6 | root | localhost:57621 | test2 | Query   |    0 | NULL  | show processlist |
       +—-+——+—————–+——-+———+——+——-+——————+
       
          .Id列,用户登录MySQL时,系统分配的“connection id”
          .User列,显示当前用户。如果不是root,这个命令就只显示用户权限范围内的sql语句。
          .Host列,显示这个语句是从哪个ip的哪个端口上发出的,可以用来追踪出现问题语句用户。
          .db列,显示这个进程目前连接的是哪个数据库。
          .Command列,显示当前连接的执行的命令,一般取值为休眠(Sleep)、查询(Query),连接(Connect)
          .Time列,显示这个状态持续的时间,单位是秒
          .State列,显示使用当前连接的sql语句的状态。
          .Info列,显示这个sql语句,是判断问题语句的一个重要依据。
       database()和schema()函数返回使用utf8字符集的默认(当前)数据库名。
       查看当前使用的数据库
       mysql> select database(),schema();
       +————+———-+
       | database() | schema() |
       +————+———-+
       | test2      | test2    |
       +————+———-+
      .获取用户名的函数
      user()、current_user、current_user()、system_user()和session_user()这几个函数返回
      当前被mysql服务器验证的用户名和主机名组合。这个值符合确定当前登录用户存取权限的mysql账户。
      一般情况下,这几个函数的返回值是相同的。
       获取当前登录用户名称
       mysql> select user(),current_user(),system_user();
       +—————-+—————-+—————-+   
       | user()         | current_user() | system_user()  |
       +—————-+—————-+—————-+
       | root@localhost | root@localhost | root@localhost |
       +—————-+—————-+—————-+
      .获取字符串的字符集和排序方式的函数
       charset(str)返回字符串str自变量的字符集
  
       mysql> select charset(‘abc’),
       -> charset(convert(‘abc’ USING utf8)),
       -> charset(version());
       +—————-+————————————+——————–+
       | charset(‘abc’) | charset(convert(‘abc’ USING utf8)) | charset(version()) |
       +—————-+————————————+——————–+
       | gbk            | utf8                               | utf8               |
       +—————-+————————————+——————–+
      
       collation(str)返回字符串str的字符排列方式
       mysql> select collation(‘abc’),collation(convert(‘abc’ USING utf8));
       +——————+————————————–+
       | collation(‘abc’) | collation(convert(‘abc’ USING utf8)) |
       +——————+————————————–+
       | gbk_chinese_ci   | utf8_general_ci                      |
       +——————+————————————–+
      .获取最后一个自动生成的ID值的函数
       last_insert_id()自动返回最后一个insert或update为auto_increment列设置的第一个发生的值
       1、一次插入一条记录
       mysql> insert into worker values(null,’jimy’);
       mysql> insert into worker values(null,’Tom’);
       mysql> select * from worker;
       +—-+——+
       | Id | name |
       +—-+——+
       |  1 | jimy |
       |  2 | Tom  |
       +—-+——+
       2、一次同时插入多条记录
       
       mysql> insert into worker values
       -> (null,’Kevin’),(null,’Michal’),(null,’Nick’);
       mysql> select * from worker;
       +—-+——–+
       | Id | name   |
       +—-+——–+
       |  1 | jimy   |
       |  2 | Tom    |
       |  3 | Kevin  |
       |  4 | Michal |
       |  5 | Nick   |
       +—-+——–+
       使用last_insert_id()查看最后自动生成的id值。(一次插入多条记录时算作一个记录)
       mysql> select last_insert_id();
       +——————+
       | last_insert_id() |
       +——————+
       |                3 |
       +——————+
    (6)加密函数
      .加密函数password(str)
       mysql> select password(‘newpwd’);
       +——————————————-+
       | password(‘newpwd’)                        |
       +——————————————-+
       | *1FA85AA204CC12B39B20E8F1E839D11B3F9E6AA4 |
       +——————————————-+
       .加密函数md5(str)
       md5(str)为字符串算出一个MD5 128比特校验和。该值以32位十六进制数字的二进制字符串
       形式返回,若参数为null,则会返回null。
       mysql> select md5(‘mypwd’);
       +———————————-+
       | md5(‘mypwd’)                     |
       +———————————-+
       | 318bcb4be908d0da6448a0db76908d78 |
       +———————————-+   
       .加密函数encode(str,pswd_str)
        encode(str,pswd_str)使用pswd_str作为密码,加密str。使用decode()解密结果,结果是一个
        和str长度相同的二进制字符串。
       mysql> select encode(‘secret’,’cry’),length(encode(‘secret’,’cry’));
       +————————+——————————–+
       | encode(‘secret’,’cry’) | length(encode(‘secret’,’cry’)) | 
       +————————+——————————–+
       | 鷋 ?                |                              6    |
       +————————+——————————–+  
       .解密函数decode(crypt_str,pswd_str)
        decode(crypt_str,pswd_str)使用pswd_str作为密码,解密加密字符串crypt_str,crypt_str是由
        encode()返回的字符串。
        mysql> select decode(encode(‘secret’,’cry’),’cry’);
        +————————————–+
        | decode(encode(‘secret’,’cry’),’cry’) |
        +————————————–+
        | secret                               |
        +————————————–+
    (7)其它函数
       .格式化函数format(x,n)
        format(x,n)将数字x格式化,并以四舍五入的方式保留小数点后n位,结果以字符串的形式返回,若n为0,
        结果以字符串的形式返回。若n为0,则返回结果函数不含小数部分。
        mysql> select format(12332.123456,4),format(12332.1,4),format(123322,0);
        +————————+——————-+——————+
        | format(12332.123456,4) | format(12332.1,4) | format(123322,0) |
        +————————+——————-+——————+
        | 12,332.1235            | 12,332.1000       | 123,322          |
        +————————+——————-+——————+
       .不同进制的数字进行转换的函数
        conv(N,from_base,to_base)函数进行不同进制数间的转换。返回值为数值N的字符串表示,由from_base进制
        转化为to_base进制。如有任意一个参数为null,则返回值为null。自变量N被理解为一个整数,但是可以被指定
        为一个整数或字符串。最小基数为2,而最大基数则为36。
        mysql> select conv(‘a’,16,2),
        -> conv(15,10,2),
        -> conv(15,10,8),
        -> conv(15,10,16);
        +—————-+—————+—————+—————-+
        | conv(‘a’,16,2) | conv(15,10,2) | conv(15,10,8) | conv(15,10,16) |
        +—————-+—————+—————+—————-+
        | 1010           | 1111          | 17            | F              |
        +—————-+—————+—————+—————-+
       .IP地址与数字相互转换的函数
        inet_aton(expr)给出一个作为字符串的网络地址的点地址表示,返回一个代表该地址数值的整数。地址可以是4或
        8比特地址。
        mysql> select inet_aton(‘209.207.224.40’);
        +—————————–+
        | inet_aton(‘209.207.224.40’) |
        +—————————–+
        |                  3520061480 |
        +—————————–+
        计算方法:209*256(3)+207*256(2)+224*256+40
        inet_ntoa(expr)给定一个数字网络地址(4或8比特),返回作为字符串的该地址的点地址表示。
        mysql> select inet_ntoa(3520061480);
        +———————–+
        | inet_ntoa(3520061480) |
        +———————–+
        | 209.207.224.40        |
        +———————–+
       .加锁函数和解锁函数
            get_lock(str,timeout)设法使用字符串str给定的名字得到一个锁,超时未timeout秒。若成功得到
        锁,则返回1;若操作超时,则返回0;若发生错误,则返回null。假如有一个用get_lock()得到的锁,
        当执行release_lock()或连接断开(正常或非正常)时,这个锁就会解除。
            release_lock(str)解开被get_lock()获取的,用字符串str锁命名的锁。若锁被解开,则返回1;若
        该线程尚未创建锁,则返回0(此时锁没有被解开);若命名的锁不存在,则返回null。若该锁从未被get_lock()
        的调用获取,或锁已经被提前解开,则该锁不存在。
           is_free_lock(str)检查名为str的锁是否可以使用(换言之,没有被封锁)。若锁可以使用,则返回1(没有人在用
        这个锁);若这个锁正在被使用,则返回0;出现错误,则返回null,(诸如不正确的参数)
           is_used_lock(str)检查名为str的锁是否正在被使用(换言之,被封锁)。若被封锁,则返回使用该锁的客户端的连接
        标识符(connection ID);否则,返回null.
        mysql> select get_lock(‘lock1’,10) as getlock,
        -> is_used_lock(‘lock1’) as isusedlock,
        -> is_free_lock(‘lock1’) as isfreelock,
        -> release_lock(‘lock1’) as releaselock;
       +———+————+————+————-+
       | getlock | isusedlock | isfreelock | releaselock |
       +———+————+————+————-+
       |       1 |          1 |          0 |           1 |
       +———+————+————+————-+
       .重复执行指定操作的函数
        benchmark(count,expr)函数重复count次执行表达式expr。它可以用于计算mysql处理表达式的速度。结果值通常为0
       (0只是表示处理过程很快,并不是没有花费时间)。另一个作用是它可以在mysql客户端内部报告语句执行的时间。
        mysql> select password(‘newpwd’);
        +——————————————-+
        | password(‘newpwd’)                        |
        +——————————————-+
        | *1FA85AA204CC12B39B20E8F1E839D11B3F9E6AA4 |
        +——————————————-+
        mysql> select benchmark(500000,password(‘newpwd’));
        +————————————–+
        | benchmark(500000,password(‘newpwd’)) |
        +————————————–+
        |                                    0 |
        +————————————–+
       .改变字符集的函数
        convert(… using …)带有using的convert()函数被用来在不同的字符集之间转换数据。
        mysql> select charset(‘string’),charset(convert(‘string’ using latin1));
        +——————-+—————————————–+
        | charset(‘string’) | charset(convert(‘string’ using latin1)) |
        +——————-+—————————————–+
        | gbk               | latin1                                  |
        +——————-+—————————————–+
       .改变数据类型的函数
 
        cast(x, AS type)和convert(x,type)函数将一个类型的值转换为另一个类型的值,可转换
        的type有:binary、char(n)、date、time、datetime、decimal、signed、unsigned
        mysql> select cast(100 as char(2)),convert(‘2010-10-01 12:12:12’,time);
        +———————-+————————————-+
        | cast(100 as char(2)) | convert(‘2010-10-01 12:12:12’,time) |
        +———————-+————————————-+
        | 10                   | 12:12:12                            |
        +———————-+————————————-+
41、查看当前字符集
       mysql> show variables like ‘character_set_%’;
       +————————–+———————————————————+
       | Variable_name            | Value                                                   |
       +————————–+———————————————————+
       | character_set_client     | gbk                                                     |
       | character_set_connection | gbk                                                     |
       | character_set_database   | latin1                                                  |
       | character_set_filesystem | binary                                                  |
       | character_set_results    | gbk                                                     |
       | character_set_server     | latin1                                                  |
       | character_set_system     | utf8                                                    |
       | character_sets_dir       | D:\Program Files\MySQL\MySQL Server 6.0\share\charsets\ |
       +————————–+———————————————————+
42、索引
    索引:是对数据库表中一列或多列的值进行排序的一种结构,使用索引可提高数据库中特定数据的查询速度。
    mysql中索引的存储类型有两种:btree和hash,具体和表的存储引擎相关;MyISAM和InnoDB存储引擎只支持
    btree索引;memory/heap存储引擎可以支持HASH和BTREE索引。
    (1)索引的分类
         .普通索引和唯一索引、单列索引和组合索引、全文索引、空间索引。
             全文索引,类型为fulltext,在定义索引的列上支持值的全文查找,允许在这些索引列中插入重复值和
         空值。全文索引可以在char、varchar或者text类型的列上创建。mysql中只有MyISAM存储引擎支持全文索引。
             空间索引,是对空间数据类型的字段建立的索引,mysql中的空间数据类型有4种,分别是:
             geometry、point、linestring和polygon。创建空间索引的列,必须将其声明为not null,空间索引只能
             在存储引擎为MyISAM的表中创建。
    (2)创建索引
         .创建表的时候创建索引
          unique、fulltext和spatial为可选参数,分别表示唯一索引、全文索引和空间索引。
          1)创建普通索引
          mysql> create table book
          -> (
          ->   bookid int not null,
          ->   booname varchar(255) not null,
          ->   authors varchar(255) not null,
          ->   info    varchar(255) not null,
          ->   comment varchar(255) not null,
          ->   year_publication year not null,
          ->   index(year_publication)
          -> );
            
          mysql> show create table book \G:     (查看表结构)
          *************************** 1. row ***************************
          Table: book
          Create Table: CREATE TABLE `book` (
         `bookid` int(11) NOT NULL,
         `booname` varchar(255) NOT NULL,
         `authors` varchar(255) NOT NULL,
         `info` varchar(255) NOT NULL,
         `comment` varchar(255) NOT NULL,
         `year_publication` year(4) NOT NULL,
         KEY `year_publication` (`year_publication`)   (索引year_publication`)
         ) ENGINE=InnoDB DEFAULT CHARSET=latin1
         查看索引是否正在使用:
         mysql> explain select * from book where year_publication=1990\G;
         *************************** 1. row ***************************
           id: 1
           select_type: SIMPLE
           table: book
           type: ref
           possible_keys: year_publication
           key: year_publication
           key_len: 1
           ref: const
           rows: 1
           Extra: Using index condition
           EXPLAIN语句输出结果解释如下;
  
           <1>select_type行指定所使用的select查询类型,这里值为simple,表示简单的select,不使用
           union或子查询。其它可能的取值有:primary、union、subquery等
           <2>table行指定数据库读取的数据表的名字,它们按读取的先后顺序排列。
           <3>type行指定了本数据表与其它数据表之间的关联关系,可能的取值有 system、const、eq_ref、
           ref、range、index和All。
           <4>possible_keys行给出了mysql在搜索数据记录时可选用的各个索引。
           <5>key行是mysql实际选用的索引。
           <6>key_len行给出索引按字节计算的长度,key_len数值越小,表示越快。
           <7>ref行给出了关联关系中另一个数据表里数据列的名字。
           <8>rows行是mysql在执行这个查询时预计会从这个数据表里读出的数据行的个数。
           <9>extra行提供了与关联操作有关的信息。
         2)创建唯一索引
           使用unique关键字创建唯一索引
           mysql> create table t1
           -> (
           ->  id int not null,
           ->  name char(30) not null,
           ->  unique index uniqIdex(id)
           -> );
           
          查看表结构:
          mysql> show create table t1\G;
          *************************** 1. row *******************
          Table: t1
          Create Table: CREATE TABLE `t1` (
           `id` int(11) NOT NULL,
           `name` char(30) NOT NULL,
           UNIQUE KEY `uniqIdex` (`id`)               ( 唯一索引 UNIQUE KEY `uniqIdex` (`id`))
          ) ENGINE=InnoDB DEFAULT CHARSET=latin1
          1 row in set (0.00 sec)
         3)创建单列索引
         单列索引是在数据表中的某一个字段上创建的索引,一个表中可以创建多个单列索引。
         mysql> create table t2    (创建表)
         -> (
         ->   id int not null,
         ->    name char(50) null,
         ->    index SingleId(name(20))
         -> );
         mysql> show create table t2 \G;(查看表结构)
         *************************** 1. row ***************************
         Table: t2
         Create Table: CREATE TABLE `t2` (
         `id` int(11) NOT NULL,
         `name` char(50) DEFAULT NULL,
         KEY `SingleId` (`name`(20))          (单列索引:SingleId` (`name`(20)),索引长度为20)
         ) ENGINE=InnoDB DEFAULT CHARSET=latin1
         1 row in set (0.00 sec)
         4)创建组合索引
         组合索引是在多个字段上创建一个索引。
         mysql> create table t3
         -> (
         ->   id int not null,
         ->   name char(30) not null,
         ->   age int not null,
         ->   info varchar(255),
         ->   INDEX MultiIdx(id,name, info(100))
         -> );
         
        mysql> show create table t3 \G; (查看表结构)
        *************************** 1. row ***************************
        Table: t3
        Create Table: CREATE TABLE `t3` (
        `id` int(11) NOT NULL,
        `name` char(30) NOT NULL,
        `age` int(11) NOT NULL,
        `info` varchar(255) DEFAULT NULL,
        KEY `MultiIdx` (`id`,`name`,`info`(100))  (组合索引)
        ) ENGINE=InnoDB DEFAULT CHARSET=latin1
        1 row in set (0.00 sec)
        
        在表中,查询id和name字段,使用EXPLAIN语句查看索引的使用情况
  
        mysql> explain select * from t3 where id=1 and name=’joe’\G;
        *************************** 1. row ***************************
        id: 1
        select_type: SIMPLE
        table: t3
        type: ref
        possible_keys: MultiIdx
        key: MultiIdx
        key_len: 34
        ref: const,const
        rows: 1
        Extra: Using index condition
        1 row in set (0.05 sec)
 
         5)创建全文索引
        .fulltext全文索引可以用于全文搜索。只有MyISAM存储引擎支持fulltext索引,并且
         只为char、varchar和text列。索引总是对整个列进行,不支持局部(前缀)索引。
         mysql> create table t4
         -> (
         -> id int not null,
         -> name char(30) not null,
         -> age int not null,
         -> info  varchar(255),
         -> fulltext index FullTxtIdx(info)
         -> )ENGINE=MyISAM;
         查看表结构 
         mysql> show create table t4 \G;
         *************************** 1. row ***************************
         Table: t4
         Create Table: CREATE TABLE `t4` (
         `id` int(11) NOT NULL,
         `name` char(30) NOT NULL,
         `age` int(11) NOT NULL,
         `info` varchar(255) DEFAULT NULL,
         FULLTEXT KEY `FullTxtIdx` (`info`)  (全文索引)
         ) ENGINE=MyISAM DEFAULT CHARSET=latin1
         1 row in set (0.00 sec)
         6)创建空间索引
         空间索引必须在MyISAM类型的表中创建,且空间类型的字段必须为非空。
         mysql> create table t5
         -> (
         ->  g geometry not null,
         ->  spatial index spatIdx(g)
         -> )ENGINE=MyISAM;
        mysql> show create table t5 \G;  (查看表结构)
        *************************** 1. row ***************************
        Table: t5
        Create Table: CREATE TABLE `t5` (
        `g` geometry NOT NULL,
        SPATIAL KEY `spatIdx` (`g`)             (空间索引)
        ) ENGINE=MyISAM DEFAULT CHARSET=latin1
        1 row in set (0.00 sec)
         .在已经存在的表上创建索引
         1)使用alter table语句创建索引   
         在book表中的boo·name字段上建立名为BkNameIdx的普通索引。
         mysql> alter table book add index BkNameIdx (booname(30));   
         查看指定表中创建的索引
        mysql> show index from book \G;
        *************************** 1. row ***************************
        Table: book
        Non_unique: 1
        Key_name: year_publication       (索引的名称)
        Seq_in_index: 1
        Column_name: year_publication    (索引的列字段)
        Collation: A
        Cardinality: 0
        Sub_part: NULL
        Packed: NULL
        Null:
        Index_type: BTREE
        Comment:
        Index_Comment:
        *************************** 2. row ***************************
        Table: book
        Non_unique: 1
        Key_name: BkNameIdx             (索引的名称)
        Seq_in_index: 1
        Column_name: booname            (索引的列字段)
        Collation: A
        Cardinality: 0
        Sub_part: 30
        Packed: NULL
        Null:
        Index_type: BTREE
        Comment:
        Index_Comment:
        2 rows in set (0.11 sec)
        其中各个主要参数的含义为:
         
        | Table  表示创建索引的表
        | Non_unique 表示索引非唯一,1代表是非唯一索引,0代表唯一索引。
  
        | Key_name 表示索引的名称
        | Seq_in_index 表示该字段在索引中的位置,单列索引该值为1,组合索引为每个字段
                       在索引定义中的顺序。
        | Column_name 表示定义索引的列字段
        | Sub_part 表示索引的长度
        | Null 表示该字段是否能为空值
        | Index_type 表示索引类型。          
        ——————————————————————————–
        .在book表的bookId字段上建立名称为 UniqidIdx的唯一索引
         mysql> alter table book add unique index UniqidIdx(bookid);
         查看表中的索引
         mysql> show index from book \G;
         *************************** 1. row ***************************
         Table: book
         Non_unique: 0
         Key_name: UniqidIdx         (索引的名称)
         Seq_in_index: 1
         Column_name: bookid         (索引的列字段)
         Collation: A
         Collation: A
         Cardinality: 0
         Sub_part: NULL
         Packed: NULL
         Null:
         Index_type: BTREE
         Comment:
         Index_Comment:
        .在表的comment字段上建立单列索引。
         mysql> alter table book add index BkcmtIdx(comment(50));
         使用show index语句查看表中的索引
         show index from book \G;
         
         *************************** 4. row ***************************
         Table: book
         Non_unique: 1
         Key_name: BkcmtIdx
         Seq_in_index: 1
         Column_name: comment
         Collation: A
         Cardinality: 0
         Sub_part: 50
         Packed: NULL
         Null:
         Index_type: BTREE
         Comment:
         Index_Comment:
         4 rows in set (0.00 sec)
        .在book表的authors和info字段上建立组合索引。
         mysql> alter table book add index BkAuAndInfoldx( authors(20),info(50));
         
         mysql> show index from book \G; (查看索引)
         *************************** 5. row ***************************
         Table: book
         Non_unique: 1
         Key_name: BkAuAndInfoldx         (索引的名称)
         Seq_in_index: 1
         Column_name: authors             (索引的列字段)
         Collation: A
         Cardinality: 0
         Sub_part: 20
         Packed: NULL
         Null:
         Index_type: BTREE
         Comment:
         Index_Comment:
         *************************** 6. row ***************************
         Table: book
         Non_unique: 1
         Key_name: BkAuAndInfoldx          (索引的名称)
         Seq_in_index: 2
         Column_name: info                 (索引的列字段)
         Collation: A
         Cardinality: 0
         Sub_part: 50
         Packed: NULL
         Null:
         Index_type: BTREE
         Comment:
         Index_Comment:
         6 rows in set (0.00 sec)
        .在t6表上使用alter table 创建全文索引
         mysql> create table t6
            -> (
            ->   id int not null,
            ->   info char(255)
            -> )engine=MyISAM;
        注意:修改engine参数为MyISAM,MySQL默认引擎InnoDB不支持全文检索。
        
        使用alter table语句在info字段上创建全文索引。
        mysql> alter table t6  add fulltext index infoFTIdx(info);
      
         mysql> show index from t6 \G;       查看索引:
         *************************** 1. row ***************************
         Table: t6
         Non_unique: 1
         Key_name: infoFTIdx        (索引的名称)
         Seq_in_index: 1
         Column_name: info          (索引的列字段)
         Collation: NULL
         Cardinality: NULL
         Sub_part: NULL
         Packed: NULL
         Null: YES
         Index_type: FULLTEXT
         Comment:
         Index_Comment:
         1 row in set (0.00 sec)
        .创建表t7,在t7的空间数据类型字段g上创建名为spatIdx的空间索引。
         mysql> create table t7
         -> (
         ->  g geometry not null
         -> )engine=MyISAM;
         使用alter talbe 在表t7的g字段建立空间索引
         mysql> alter table  t7 add spatial index spatIdx(g);
         查看索引:
         mysql> show index from t7 \G;
         *************************** 1. row ***************************
         Table: t7
         Non_unique: 1
         Key_name: spatIdx     (索引的名称)
         Seq_in_index: 1
         Column_name: g        (索引的列字段)
         Collation: A
         Cardinality: NULL
         Sub_part: 32
         Packed: NULL
         Null:
         Index_type: SPATIAL
         Comment:
         Index_Comment:
         1 row in set (0.01 sec)
         2)使用create index创建索引
         create index语句可以在已经存在的表上添加索引,MysSQL中create index被映射到一个
         alter table语句上。
        .在book2表中的bookname字段上建立名为BkNameIdx的普通索引。
         mysql> create index BkNameIdx on book2(bookname);
        .在book2表的bookId字段上建立名称为UniqidIdx的唯一索引。
         mysql> create unique index UniqidIdx on book2(bookid);
        .在book2表的comment字段上建立单列索引。
         mysql> create index BkcmtIdx on book2(comment(50));
        .在book表的authors和info字段上建立组合索引。
         mysql> create index BkAuAndInfoIdx on book2 (authors(20),info(50));
        .使用create index在t6表的info字段上创建名称为infoFTIdx的全文索引
         mysql> create fulltext index infoFTIdx on t6(info);
        .在表t7的g字段建立空间索引
          mysql> create spatial index  spatIdx on t7(g);
    (2)删除索引
         1)使用alter table 删除索引
         删除book表中的名称为UniqidIdx的唯一索引。
         
         mysql> alter table book drop index UniqidIdx;
        
         (注意:添加auto_increment约束字段的唯一索引不能被删除)
         2)使用drop index语句删除索引
         删除book表中名称为BkAuAndInfoIdx的组合索引。
         mysql> drop index BkAuAndInfoldx on book;
        (删除表中的列时,如果要删除的列为索引的组成部分,则该列也会从索引中删除,如果组成索引
          的所有列都被删除,则整个索引将被删除)
    (3) 其它
         创建表及索引:
         mysql> create table test_table1
           -> (
           ->  id int not null primary key auto_increment,
           ->  name char(100) not null,
           ->  address char(100) not null,
           ->  description char(100) not null,
           ->  unique index UniqIdx(id),
           ->   index MultiColldx(name(20),address(30)),
           ->  index ComIdx( description(30))
           -> );
         查看创建的索引:
         mysql> show create table test_table1 \G;
         *************************** 1. row ***************************
         Table: test_table1
         Create Table: CREATE TABLE `test_table1` (
         `id` int(11) NOT NULL AUTO_INCREMENT,
         `name` char(100) NOT NULL,
         `address` char(100) NOT NULL,
         `description` char(100) NOT NULL,
         PRIMARY KEY (`id`),
         UNIQUE KEY `UniqIdx` (`id`),                     唯一索引
         KEY `MultiColldx` (`name`(20),`address`(30)),    组合索引
         KEY `ComIdx` (`description`(30))                 普通索引
        ) ENGINE=InnoDB DEFAULT CHARSET=latin1
43、存储过程
        存储过程:是一条或者多条SQL语句的集合,可视为批文件,但是其作用不仅限于批处理。
        存储程序可以分为:存储过程和函数
        创建存储过程:create procedure
        创建函数: create function
        使用call语句来调用存储过程,只能用输出变量返回值。存储过程还可以调用其他存储过程。
       (1)创建存储过程
        创建存储过程,需要使用 create procedure语句。
        sp_name为存储过程的名称,proc_parameter为指定存储过程的参数列表。
        [IN | OUT | INOUT ] param_name type
        其中,in表示输入参数,out表示输出参数,inout表示既可以输入也可以输出;
        
        param_name表示参数的名称;type表示参数的类型,该类型可以是mysql数据库中的任何类型。
        characteristics指定存储过程的特性:有以下取值:
            <.language sql:说明routine_body部分是由sql语句组成的,当前系统支持的语言为sql,sql是language特性的唯一值。
            <.[not] deterministic:指明存储过程执行的结果是否正确。
               deterministic表示结果是确定的。每次执行存储过程时,相同的输入会得到相同的的输出。
               not deterministic:表示结果是不确定的,相同的输入可能得到不同的输出。
               如果没有指定任意一个值,默认为not deterministic。
            <.{ contains sql|no sql|reads sql data|modifies sql data};
              指明子程序使用sql语句的限制。
              contains sql表明子程序包含SQL语句,但是不包含读写数据的语句。
              no sql表明子程序不包含SQL语句;
              reads sql data说明子程序包含读数据的语句。
              modifies sql data表明子程序包含写数据的语句。默认情况下,系统会指定为contains sql。
            <.sql security {definer | invoker};
             指明谁有权限来执行。definer表示只有定义者才能执行。
             invoker表示拥有权限的调用者可以执行。默认情况下,系统指定为definer。
            <.comment ‘string’:注释信息,可以用来描述存储过程或函数。
             routine_body是SQL代码的内容,可以用begin..end来表示SQL代码的开始和结束。
        .创建查看fruits表的存储guocheng
         mysql> delimiter //            (界定符)
         mysql> create procedure proc()
             -> begin
             -> select * from fruits;
             -> end //
        .创建名为CountProc的存储过程
         mysql> delimiter //
         mysql> create procedure CountProc(out param1 int)
             -> begin
             -> select count(*) into param1 from fruits;   //把计算结果放入参数param1中
             -> end //
       (2)创建存储函数
            创建存储函数,需要使用create function语句
        .创建存储函数,名称为NameByZip,该函数返回select语句的查询结果,数值类型为字符串型。
         mysql> delimiter //
         mysql> create function NameByZip()
             -> returns char(50)
             -> return (select  s_name from suppliers where s_call=’48075′);
             -> //
       (3)变量的使用
            变量可以在子程序中声明并使用,这些变量的作用范围是begin..end程序中。
           1)定义变量
           .定义名称为myparam的变量,类型为int类型,默认值为100.
            mysql> delimiter //
            mysql> create procedure a0()
                -> begin
                ->  declare myparam int default 100;
                -> end //
           2)为变量赋值
           .声明3个变量,分别为var1、var2和var3,数据类型为int,使用set为变量赋值。
            mysql> delimiter //
            mysql> create procedure a()
                -> begin
                ->  declare var1,var2,var3 int;
                ->  set var1=10,var2=20;
                ->  set var3=var1+var2;
                -> end //
           .声明变量fruitname和fruitprice,通过select…into语句查询指定记录并为变量赋值。
            mysql> create procedure b()
                -> begin
                -> declare fruitname char(50);
                -> declare fruitprice decimal(8,2);
                -> select f_name,f_price into fruitname,fruitprice
                -> from fruits where f_id=’a1′;
                -> end //
       (4)定义条件和处理程序
           1)定义条件
           .定义“Error 1148(4200)”错误,名称为command_not_allowed,可以用两种不同的方法
            来定义。
            方法一:使用sqlstate_value
           2)定义处理程序
              格式:
              declare handler_type handler for condition_value[,…] sp_statement
              handler_type:
                 continue | exit |undo
              condition_value
         
                  sqlstate [value] sqlstate_value
               | condition_name
     
               | SQLWARNING
    
               | NOT FOUND
   
               | SQLEXCEPTION
               | mysql_error_code
               其中,handler_type为错误处理方式,参数取3个值:
                    continue、exit和undo。
                    continue:表示遇到错误不处理,继续执行。
                    exit: 遇到错误马上退出。
                    undo:表示遇到错误后撤回之前的操作。
               condition_value表示错误类型,可以有以下取值:
                   SQLSTATE[VALUE]sqlstate_value 包含5个字符的字符串错误值;
                   condition_name表示 declare condition 定义的错误条件名称;
 
                   SQLWARNING 匹配所有以01开头的SQLSTATE错误代码;
                   NOT FOUND 匹配所有以02开头的SQLSTATE错误代码;
                   SQLEXCEPTION匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码。
                   mysql_error_code 匹配数值类型错误代码。
                 sp_statement参数为程序语句段,表示在遇到定义的错误时,需要执行的存储过程或函数。
           .定义处理程序的几种方式。
            方法一:捕获sqlstate_value
            mysql> delimiter //
            mysql> create procedure d()
                -> begin
                ->   declare continue handler for sqlstate ’42S02′ set @info=’NO_SUCH_TABLE’;
                -> end //
            
            方法二:捕获mysql_error_code
            
            
            
           
     
80、权限管理
    权限管理主要是对登录到mysql的用户进行权限验证,所有用户的权限都存储在mysql的权限表中,
不合理的权限规划会给mysql服务器带来安全隐患。
   mysql的权限系统的主要功能是证实连接到一台给定主机的用户,并且赋予该用户在数据库上的
select 、insert、update和delete权限。
  1>mysql的各种权限
  账户权限信息被存储在mysql数据库的user,db、host、tables  
  2>grant授权的语法
   grant <privileges> on <what> to <user> [identified by “<password>”] [with grant option]
   其中:–privileges代表权限。
         –what代表对象,如:库,表,字段,视图等。
         –user代表用户
         –identified by为可选项,指定用户密码
         –with grant option为可选项,代表本用户可以把权限再次授给其他用户。
    实例:–> grant select,insert,delete,update on test2.* to test2 identified by ‘ test2’;
                                                   数据库.表   数据库               新账户默认密码
       例如:grant all privileges on test.*  to ‘yhq’@’192.168.18.70’ identified by “yhq..”;  允许192.168.18.70主机访问本机192.168.18.60中的test数据库。
           –>flush privilges;
           –>ssh 192.168.18.70  (18.60连接18.70)
           –>mysql -u yhq -p 123456 -h 192.168.18.60  (18.70连接18.60)
      创建用户并设置密码:create user ‘用户名’@’localhost或IP’ identified by ‘密码’;
81、事务
  1>事务
      –事务简介/特性
      –Innodb引擎中的事务日志
      –Log buffer中的日志向硬盘上刷新的条件
      –重做(事务)日志文件
   2>操作事务
      –事务处理方法
      –事务的提交与回滚
      –php中调用事务
   3>事务隔离级别
      –事务隔离级别介绍
      –各隔离级别可能引发的问题
      –查看与设置事务隔离级别。
   4>事务(transaction)
     事务都应该具备ACID特征。
    .所谓ACID是:   .Atomic(原子性)   .aConsisten(一致性)    .Isolated(隔离性)    .Durable(持续性)  四个词的首字母缩写。
    .MyISAM: 不支持事务,用于只读程序提高性能。
    .InnoDB: 支持ACID事务、行级锁,并发。
    .Berkeley DB:支持事务。
   5>事务特性
    .原子性:组成事务处理的语句形成了一个逻辑单元,不能只执行其中的一部分。换句话说,事务是不可分割的最小单元。
    .一致性:在事务处理执行前后,数据库是一直的。即事务应该正确的转换系统状态。
    .隔离性:一个事务处理对另一个事务处理没有影响,即任何事务都不可能看到一个处在不完整状态下的事务。
    .持续性:事务处理的效果能够被永久保存下来。即事务应当能够承受所有的失败,包括服务器、进程、通信以及媒体失败等。
   6>Innodb引擎中的事务日志
    .InnoDB引擎中的事务日志通过重做(redu)和日志缓冲(InnoDB Log Buffer)实现。
        –事务开始时:记录该事务一个LSN(log sequence number);
        –事务执行时:往InnoDB Log Buffer重插入事务日志;
        –事务提交时:将InnoDB Log Buffer中的日志刷新到磁盘上(默认的实现,变量innodb_flush_log_at_trx_commit=1).
                      即在写数据前先写日志,这种方式成为预写日志方式,通过这样来保证事务的完整性。(show variables like ‘%commit%’;)
   7> Log buffer中的日志向硬盘上刷新的条件
     .在主线程不论事务是否提交,每秒就会将重做日志缓冲写入磁盘的重要日志文件中
     .另一个出发刷新过程是由参数Innodb_flush_log_at_trx_commit控制。它表示在提交时,处理重做日志的方式。
      它的值可以由0、1、2;
          0—->代表当提交事务时,并不将事务的重做日志写入磁盘上的日志文件,而是等待主线程每秒的刷新。
          1—->是在commit时将重做日志缓冲同步写到磁盘;
          2—->是重做日志异步写到磁盘,即不能完全保证commit时肯定会写入重做日志文件,只是有这个动作。
                它仅仅调用了操作系统文件系统的写入,由于文件系统的缓存机制,所以也不能保证即刻写入磁盘。
 
     .值得注意的一点:因为重做日志有个capacity变量,该值代表了最后的检查点不能超过这个阀值。
     .提示
         –事务日志增加了整体IO量,但是相对于保证数据的安全是值得的,而且日志的记录经过了精心安排,做到了最省;
         –磁盘操作最耗时间的就是磁头的寻址,连续写入的好处是相比如随机写入,磁头寻址动作耗费少。
         –事务日志的创建之初申请的是连续的物理空间,而且每次事务都是一个递增的日志序列号,每次写入都是紧接着前面
           的一次写入,基本上是一个顺序的写入过程。
   8> 事务日志文件
      .ib_logfile0 和 ib_logfile1
       这两个文件就是重做日志文件,或者事务日志,mysql默认文件。
      .作用:防止实例或者介质失败。  
        –每个InnoDB存储引擎至少有一个重做日志文件组,每个文件组下至少有2个重做日志文件。如默认的ib_logfile0  ib_logfile1
          InnoDB存储引擎先写重做日志文件1,当达到文件的最后时,会切换至重做日志文件2,当重做日志文件2也被写满时,会再被切换
          到重做日志文件1中。
        –影响重做日志的参数: Innodb_log_file_size 、innodb_log_files_in_group、innodb_log_group_home_dir等。
           日志缓冲
              |
              |                        重做日志组
          ———————————————
            —>重做日志1 —->重做日志2
            |                          |
            |                          |
            —————————-
   9> 事务处理方法
      .显式事务
        –begin 或 start transaction开始一个事务
        –rollback事务回滚
 
        –commit事务确认
      .隐式事务
        –mysql默认是自动提交的,即用户提交一个query,它就直接执行!
      .可以用set来改变mysql的自动提交模式
        –set autocommit=0  禁止自动提交
        –set autocommit=1  开启自动提交
      .注意使用set autocommit=0时,以后所有的SQL都将做为事务处理,直到用commit确认或rollback结束。
   10>事务的提交与回滚实例
      use test;
      
      create table stu(id int(4)) type=innoDB;
      select * from stu;
      begin;
       
      insert into stu value(5);
      insert into stu value(6);
  
      commit;
      select * from stu;
      begin
      insert into stu values(7);
      rollback;
      select * from stu;   表中没有7,只有5、6两个值。
   11>php中调用事务
     <?php
        $conn=mysql_connect(“localhost”,”root”,”123456″);
        mysql_select_db(“test”);
        
        mysql_query(“set autocommit=0”);   //设置为不自动提交
        mysql_query(“begin”);  //开始事务定义
        if(!mysql_query(“insert into stu (id) values(‘2’)”)){
           mysql_query(“roolback”);  //判断当执行失败时回滚
        }
        if(!mysql_query(“insert into stu (id) values(‘4’)”)){
             
           mysql_query(“roolback”);  //判断执行失败回滚
        }
        mysql_query(“commit”);  //提交事务
        mysql_close($conn);
      ?>
49、备份还原
    1>备份:
  
      使用mysqldump备份:
      格式:mysqldump -u user -h host -ppassword dbname[tbname,[tbname]] >filename.sql
      –host:表示登陆用的主机名称    –dbname:需要备份的数据库名
      –tbname:为数据库需要备份的数据表,可以指定多个需要备份的表
      .备份数据库:
 
       mysqldump -u root -p 数据库名 >/tmp/booksDB.sql
      . 备份数据库中的表:
    
       mysqldump -u root -p 数据库名 表名 > /tmp/books__201401201411.sql  
      .备份多个数据库
       mysqldump -u root -p –databases 数据库1 数据库2 >/tmp/ku1_ku2_201401201428.sql
     
      .备份系统中所有数据库
                 
50、日志
    1>查询日志设置
      
      show variables like ‘log_%’;
    2>
        
          
 51、mysql二进制logs
     binary log是mysql中一个重要的部分,主要作用有两个:
 
        1]Replication:在master端开启binary log后,log会记录所有数据库的改动,然后slave端获取这个
        Log文件内容就可以在slave端进行同样的操作。
 
        2]备份:在某个时间点a做了一次备份,然后利用binary log记录从这个时间点a后的所有数据库的改动,
     
     然后下一次还原的时候,利用时间点a的备份文件和这个binary log文件,就可以将数据还原。
  
     1>Binary logs位置
     log_bin=/var/mydb/bin-log
     如果该设置没带路径,就放在datadir=/var/lib/mysql 下
        
     2>到数据库查看是否开启binary log 功能:
       mysql> show variables like ‘log_bin’; 
       +—————+——-+
       | Variable_name | Value |
       +—————+——-+
       | log_bin            | ON    | 
       +—————+——-+
       1 row in set (0.00 sec)
       mysql> show variables like ‘log_bin’;
       +—————+——-+
       | Variable_name | Value |
       +—————+——-+
       | log_bin             | OFF   | 
       +—————+——-+
       1 row in set (0.01 sec)
       如果是OFF就没有以下工作了:)
     3>查看当前工作的logfile名及大小: show binary logs /show master logs;
       mysql> show binary logs;
       +—————–+———–+
       | Log_name           | File_size |
       +—————–+———–+
       | bin-log.000011 |    148329 | 
       +—————–+———–+
       mysql> show master logs;
       +—————–+———–+
       | Log_name           | File_size |
       +—————–+———–+
       | bin-log.000011 |    148329 | 
       +—————–+———–+
       mysql> show binary logs;
       ERROR 1381 (HY000): You are not using binary logging ###没有开启binary logs
     4>清除所有binary logs;
       1)
       mysql> show master logs;
       +—————–+———–+
       | Log_name        | File_size |
       +—————–+———–+
       | log-bin.000001 |    259548 | 
       | log-bin.000002 |     37200 | 
       | log-bin.000003 |     74219 | 
       +—————–+———–+
       3 rows in set (0.00 sec)
       mysql> reset master; (在slave上,用reset slave,之前应stop slave,之后再start slave)
       Query OK, 0 rows affected (0.00 sec)
       mysql> show master logs;
       +—————–+———–+
       | Log_name        | File_size |
       +—————–+———–+
       | log-bin.000001 |        98 | 
       +—————–+———–+
       1 row in set (0.00 sec)
     5>清除指定部分logs:
       mysql>purge binary logs to ‘log-bin.000012’;
       将log-bin.000012之前的binary logs清掉;
       mysql>purge binary logs before ‘2011-05-28 12:05:38’;
       将指定时间之前的binary logs清掉;
     6>查看当前binary log的情况:
       mysql>show master status;
     7>查看binary logs的内容:
       mysql>show binlog events;
       命令行下:
       #mysqlbinlog /var/log/mysql/log-bin.000140; 或者
       #mysqlbinlog –start-datetime=’2011-07-01 00:00:00′ –stop-datetime=’2010-07-15 00:00:00′ /var/log/mysql/log-bin.000020 > ./tmp.log
     8>在my.cnf/my.ini中设定binary logs回滚天数:
     7>expire_logs_days = 7
      
         
博主

让学习成为习惯,坚持-共享-开源-自由! 成功者决不放弃,放弃者绝不成功!

相关推荐

嗨、骚年、快来消灭0回复。