mysql日常笔记–小铁练练手

2年前 (2015-08-30) admin mysql 0评论 已收录 371℃

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回复。