explain操作符和函数

1.创建数据库

show databases//查看当前存在什么数据库

use database_name//访问一个数据库

create database  test//创建数据库

2.创建表

show  tables//查看当前数据库所有的表

create table   test(
id int primary key auto_increment,
username varchar(10),
insert_time timestamp not null
)

describe table_name//可以查看表的列名称和类型

3.插入数据

load data local infile '/path/tet.txt' into table test

insert into test  values(1,'jack','2015-12-12 12:12:12');

insert into test  values(1,'jack','2015-12-12 12:12:12'),(2,'jack','2015-12-12 12:12:12');//多条插入

4.查询

select * from test where (id=3 and username='jack') or (id=5 and username='hello')//这个语句只为举例

       AND和OR可以混用，但AND比OR具有更高的优先级。如果你使用两个操作符，使用圆括号指明如何对条件进行分组是一个好主意。

select distinct username from test 

      如果查询结果一列出现多次。为了使输出减到最少，增加关键字DISTINCT检索出每个唯一的输出记录。

select distinct username from test order by id desc

     默认排序是升序，最小的值在第一。要想以降序排序，在你正在排序的列名上增加DESC（降序 ）关键字。

SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;//输出0,1,0,1

5.模拟匹配

    在 MySQL中，SQL的模式默认是忽略大小写的。注意使用SQL模式时，不能使用=或!=；而应使用LIKE或NOT LIKE比较操作符。

select * from test where name like 'a_%'//_表示一个字符，%表示0或多个字符

select * from test where name REGEXP '^a'//正则表达式

    当你对这类模式进行匹配测试时，使用REGEXP和NOT REGEXP操作符(或RLIKE和NOT RLIKE，它们是同义词)。 
      

explain:获取SELECT相关信息

explain select * from test where username='jack'

查询出来的属性

id:SELECT识别符。这是SELECT的查询序列号。

select_type:SELECT类型,

      simple:简单SELECT(不使用UNION或子查询)   

      primary:最外面的SELECT

      UNION:UNION中的第二个或后面的SELECT语句

      DEPENDENT UNION:UNION中的第二个或后面的SELECT语句，取决于外面的查询

      UNION RESULT:UNION的结果。

      SUBQUERY:子查询中的第一个SELECT

      DEPENDENT SUBQUERY:子查询中的第一个SELECT，取决于外面的查询

      DERIVED:导出表的SELECT(FROM子句的子查询)

table:输出的行所引用的表。

type:联接类型。下面给出各种联接类型，按照从最佳类型到最坏类型进行排序：      

     system:表仅有一行(=系统表)。这是const联接类型的一个特例。

     const:表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。const表很快，因为它们只读取一次.

     eq_ref:对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。

    ref:对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。

    ref_or_null:该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。

    index_merge:该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。

    unique_subquery:unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。

    index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引

    range:只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。

    index:该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。

    all:对于每个来自于先前的表的行组合，进行完整的表扫描。

possible_keys:possible_keys列指出MySQL能使用哪个索引在该表中找到行。

key:key列显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。

key_len:key_len列显示MySQL决定使用的键长度。

ref:ref列显示使用哪个列或常数与key一起从表中选择行。

rows:rows列显示MySQL认为它执行查询时必须检查的行数。

Extra:该列包含MySQL解决查询的详细信息。

      Distinct:MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。

     Not exists:MySQL能够对查询进行LEFT JOIN优化，发现1个匹配LEFT JOIN标准的行后，不再为前面的的行组合在该表内检查更多的行。

     range checked for each record (index map: #):MySQL没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。

     Using filesort:MySQL需要额外的一次传递，以找出如何按排序顺序检索行。

     Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。

     Using temporary:为了解决查询，MySQL需要创建一个临时表来容纳结果。

     Using where:WHERE子句用于限制哪一个行匹配下一个表或发送到客户。
     Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。
     Using index for group-by类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。


操作符和函数

COALESCE(value,...)：返回值为列表当中的第一个非 NULL值，在没有非NULL 值得情况下返回值为 NULL 。

GREATEST(value1,value2,...)：当有2或多个参数时，返回值为最大(最大值的)参数。比较参数所依据的规律同LEAST()相同。

expr IN ( value,...): 若 expr 为IN列表中的任意一个值，则其返回值为 1 , 否则返回值为0。

ISNULL(expr):如expr 为NULL，那么ISNULL() 的返回值为 1，否则返回值为 0。

INTERVAL(N,N1,N2,N3,...)假如N < N1，则返回值为0；假如N < N2 等等，则返回值为1；假如N 为NULL，则返回值为 -1 

LEAST(value1,value2,...)在有两个或多个参数的情况下， 返回值为最小 (最小值) 参数。

控制流程函数

1.CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END 

CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END 

      在第一个方案的返回结果中， value=compare-value。而第二个方案的返回结果是第一种情况的真实结果。如果没有匹配的结果值，则返回结果为ELSE后的结果，如果没有ELSE 部分，则返回值为 NULL。

2.IF(expr1,expr2,expr3)

   如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值

3.IFNULL(expr1,expr2)

假如expr1 不为 NULL，则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。

4. NULLIF( expr1 , expr2 )

如果expr1 = expr2  成立，那么返回值为NULL，否则返回值为 expr1。

字符串函数

1.ASCII(str)

 返回值为字符串str 的最左字符的数值。假如str为空字符串，则返回值为 0 。假如str 为NULL，则返回值为 NULL。 ASCII()用于带有从 0到255的数值的字符。

2. BIN( N )

返回值为N的二进制值的字符串表示，其中  N 为一个longlong (BIGINT) 数字。这等同于 CONV(N,10,2)。假如N 为NULL，则返回值为 NULL。

3. CHAR( N ,... [USING charset ])

CHAR()将每个参数N理解为一个整数，其返回值为一个包含这些整数的代码值所给出的字符的字符串。NULL值被省略。

4. CHAR_LENGTH( str )

返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。

5. CONCAT( str1 , str2 ,...)                       

返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。或许有一个或多个参数。 如果所有参数均为非二进制字符串，则结果为非二进制字符串。 

CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。   第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。

6.CONV(N,from_base,to_base)

不同数基间转换数字。返回值为数字的N字符串表示，由from_base基转化为 to_base 基。如有任意一个参数为NULL，则返回值为 NULL。

7. ELT( N , str1 , str2 , str3 ,...)

若N = 1，则返回值为  str1 ，若N = 2，则返回值为 str2 ，以此类推。   若N 小于1或大于参数的数目，则返回值为 NULL 。

8. FIELD( str , str1 , str2 , str3 ,...)

返回值为str1, str2, str3,……列表中的str 指数。在找不到str 的情况下，返回值为 0 。

9. FIND_IN_SET( str , strlist )

假如字符串str 在由N 子链组成的字符串列表strlist 中， 则返回值的范围在 1 到 N 之间 

10. FORMAT( X , D )

将number X设置为格式 '#,###,###.##', 以四舍五入的方式保留到小数点后D位, 而返回结果为一个字符串。

11. HEX( N_or_S )

如果N_OR_S 是一个数字，则返回一个 十六进制值 N 的字符串表示，在这里，   N 是一个longlong (BIGINT)数。这相当于 CONV(N,10,16)。

如果N_OR_S 是一个字符串，则返回值为一个N_OR_S的十六进制字符串表示， 其中每个N_OR_S 里的每个字符被转化为两个十六进制数字。

12.INSERT(str,pos,len,newstr)

返回字符串 str, 其子字符串起始于 pos 位置和长期被字符串 newstr取代的len 字符。  如果pos 超过字符串长度，则返回值为原始字符串。 假如len的长度大于其它字符串的长度，则从位置pos开始替换。若任何一个参数为null，则返回值为NULL。

13 INSTR( str , substr )

返回字符串 str 中子字符串的第一个出现位置。

14. LEFT( str , len )

返回从字符串str 开始的len 最左字符。 

15. LENGTH( str )

返回值为字符串str 的长度，单位为字节

16. LOCATE( substr , str ) , LOCATE( substr , str , pos )

第一个语法返回字符串 str中子字符串substr的第一个出现位置。第二个语法返回字符串 str中子字符串substr的第一个出现位置, 起始位置在pos。如若substr 不在str中，则返回值为0。

17. LPAD( str , len , padstr )

返回字符串 str, 其左边由字符串padstr 填补到len 字符长度。假如str 的长度大于len, 则返回值被缩短至 len 字符。

18 LTRIM( str )

返回字符串 str ，其引导空格字符被删除。

19MAKE_SET(bits,str1,str2,...)

返回一个设定值 (一个包含被‘,’号分开的字字符串的字符串) ，由在bits 组中具有相应的比特的字符串组成。str1 对应比特 0, str2 对应比特1,以此类推。str1, str2, ...中的 NULL值不会被添加到结果中。

20 REPEAT( str , count )

返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。 若 count <= 0,则返回一个空字符串。若str 或 count 为 NULL，则返回 NULL 。

21 REPLACE( str , from_str , to_str )

返回字符串str 以及所有被字符串to_str替代的字符串from_str 。

22 REVERSE( str )

返回字符串 str ，顺序和字符顺序相反。 

23 RIGHT( str , len )

从字符串str 开始，返回最右len 字符。

24 RIGHT( str , len )

从字符串str 开始，返回最右len 字符。

25RTRIM(str)

返回字符串 str ，结尾空格字符被删去。

26 SPACE( N )

返回一个由N 间隔符号组成的字符串。

27 SUBSTRING( str , pos ) , SUBSTRING( str FROM pos ) SUBSTRING( str , pos , len ) , SUBSTRING( str FROM pos FOR len )

不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。

28 SUBSTRING_INDEX( str , delim , count )

在定界符 delim 以及count 出现前，从字符串str返回自字符串。若count为正值,则返回最终定界符(从左边开始)左边的一切内容。若count为负值，则返回定界符（从右边开始）右边的一切内容。

29 TRIM([{BOTH | LEADING | TRAILING} [ remstr ] FROM] str ) TRIM( remstr FROM] str )

返回字符串 str ， 其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项，在未指定情况下，可删除空格。

30 UPPER( str )

返回字符串str， 以及根据最新字符集映射转化为大写字母的字符 (默认为cp1252 Latin1). 

30 expr LIKE pat [ESCAPE ' escape-char ']

模式匹配，使用SQL简单正规表达式比较。返回1 (TRUE) 或 0 (FALSE)。 若 expr 或 pat 中任何一个为 NULL,则结果为 NULL。 

31 STRCMP( expr1 , expr2 )

若所有的字符串均相同，则返回STRCMP()，若根据当前分类次序，第一个参数小于第二个，则返回  -1，其它情况返回 1 。

数值函数

若发生错误，所有数学函数会返回 NULL 。

1.ABS(X)

返回X 的绝对值。

2. ACOS( X )

返回X 反余弦, 即, 余弦是X的值。若X 不在-1到 1的范围之内，则返回 NULL 。

3. ASIN( X )

返回X 的反正弦，即，正弦为X 的值。若X  若X 不在-1到 1的范围之内，则返回 NULL 。

4. ATAN( X )

返回X 的反正切，即，正切为X 的值。

5. CEILING( X ) CEIL( X )

返回不小于X 的最小整数值。

6 COS( X )

返回X 的余弦，其中X在弧度上已知。

7.DEGREES(X)

返回参数 X, 该参数由弧度被转化为度。 

8 EXP( X )

返回e的X乘方后的值(自然对数的底)。

9 FLOOR( X )

返回不大于X的最大整数值 。

10 FORMAT( X , D )

将数字X 的格式写成'#,###,###.##'格式, 即保留小数点后 D位，而第D位的保留方式为四舍五入，然后将结果以字符串的形式返回。

11 LN( X )

返回X 的自然对数,即, X 相对于基数e 的对数。

12 LOG( X ) LOG( B , X )

若用一个参数调用，这个函数就会返回X 的自然对数。

13 LOG2( X )

返回X 的基数为2的对数。

14LOG10(X)

返回X的基数为10的对数。

15 MOD( N , M ) , N % M N MOD M

模操作。返回N 被 M除后的余数。

16PI()

返回 ϖ (pi)的值。

17 POW( X , Y ) , POWER( X , Y )

返回X 的Y乘方的结果值。

18 RADIANS( X )

返回由度转化为弧度的参数 X,  (注意 ϖ 弧度等于180度）。  

19 RAND() RAND( N )

返回一个随机浮点值 v ，范围在 0 到1 之间 (即, 其范围为 0 ≤ v ≤ 1.0)。若已指定一个整数参数 N ，则它被用作种子值，用来产生重复序列。  

20ROUND(X) ROUND(X,D)

返回参数X, 其值接近于最近似的整数。在有两个参数的情况下，返回 X ，其值保留到小数点后D位，而第D位的保留方式为四舍五入。若要接保留X值小数点左边的D 位，可将 D 设为负值。 

21SIGN(X)

返回参数作为-1、 0或1的符号，该符号取决于X 的值为负、零或正。

22SIN(X)

返回X 正弦，其中 X 在弧度中被给定。

23 SQRT( X )

返回非负数X 的二次方根。

24TAN(X)

返回X 的正切，其中X 在弧度中被给定。

25TRUNCATE(X,D)

返回被舍去至小数点后D位的数字X。若D 的值为 0, 则结果不带有小数点或不带有小数部分。可以将D设为负数,若要截去(归零) X小数点左起第D位开始后面所有低位的值.  

日期和时间函数

1.ADDDATE(date,INTERVAL expr type) ADDDATE(expr,days)

当被第二个参数的INTERVAL格式激活后， ADDDATE()就是DATE_ADD()的同义词。

SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);

2 ADDTIME( expr , expr2 )

ADDTIME()将 expr2添加至expr 然后返回结果。 expr 是一个时间或时间日期表达式，而expr2 是一个时间表达式。

3.CONVERT_TZ(dt,from_tz,to_tz)

CONVERT_TZ() 将时间日期值dt 从from_tz 给出的时区转到to_tz给出的时区，然后返回结果值。

4CURDATE()

将当前日期按照'YYYY-MM-DD' 或YYYYMMDD 格式的值返回

5 CURTIME()

将当前时间以'HH:MM:SS'或 HHMMSS 的格式返回

6. DATE( expr )

提取日期或时间日期表达式expr中的日期部分。

7DATEDIFF(expr,expr2)

DATEDIFF() 返回起始时间 expr和结束时间expr2之间的天数。Expr和expr2 为日期或 date-and-time 表达式。

8.DATE_ADD(date,INTERVAL expr type) DATE_SUB(date,INTERVAL expr type)

这些函数执行日期运算。 date 是一个 DATETIME 或DATE值，用来指定起始时间。 expr 是一个表达式，用来指定从起始日期添加或减去的时间间隔值。  Expr是一个字符串;对于负值的时间间隔，它可以以一个 ‘-’开头。 type 为关键词，它指示了表达式被解释的方式。   

9 DATE_FORMAT( date , format )

根据format 字符串安排date 值的格式。

10 DAYNAME( date )

返回date 对应的工作日名称。

11DAYOFMONTH(date)

返回date 对应的该月日期，范围是从 1到31。

12DAYOFWEEK(date)

返回date (1 = 周日, 2 = 周一, ..., 7 = 周六)对应的工作日索引。这些索引值符合 ODBC标准。

13DAYOFYEAR(date)

返回date 对应的一年中的天数，范围是从 1到366。

14EXTRACT( type FROM date)

EXTRACT()函数所使用的时间间隔类型说明符同 DATE_ADD()或DATE_SUB()的相同,但它从日期中提取其部分，而不是执行日期运算

15FROM_DAYS(N)

给定一个天数  N, 返回一个DATE值。

16 HOUR( time )

返回time 对应的小时数。对于日时值的返回值范围是从 0 到 23 。

17LAST_DAY(date)

获取一个日期或日期时间值，返回该月最后一天对应的值。若参数无效，则返回NULL。

18MAKEDATE(year,dayofyear)

给出年份值和一年中的天数值，返回一个日期。dayofyear 必须大于 0 ，否则结果为 NULL。

19MAKETIME(hour,minute,second)

返回由hour、 minute和second 参数计算得出的时间值。

20MICROSECOND(expr)

从时间或日期时间表达式expr返回微秒值，其数字范围从 0到 999999。

21MINUTE(time)

返回 time 对应的分钟数,范围是从 0 到 59。

22MONTH(date)

返回date 对应的月份，范围时从 1 到 12。

23MONTHNAME(date)

返回date 对应月份的全名。

24NOW()

返回当前日期和时间值，其格式为 'YYYY-MM-DD HH:MM:SS' 或YYYYMMDDHHMMSS 

25PERIOD_ADD(P,N)

添加 N 个月至周期P (格式为YYMM 或YYYYMM)，返回值的格式为 YYYYMM。注意周期参数 P 不是日期值。 

26PERIOD_DIFF(P1,P2) 

返回周期P1和 P2 之间的月份数。P1 和P2 的格式应该为YYMM或YYYYMM。注意周期参数 P1和P2 不是日期值。

27QUARTER(date)

返回date 对应的一年中的季度值，范围是从 1到 4。

28SEC_TO_TIME(seconds)

返回被转化为小时、 分钟和秒数的seconds参数值, 其格式为 'HH:MM:SS' 或HHMMSS

29STR_TO_DATE(str,format)

这是DATE_FORMAT() 函数的倒转。它获取一个字符串 str 和一个格式字符串format。

30SUBDATE(date,INTERVAL expr type) SUBDATE(expr,days)

当被第二个参数的 INTERVAL型式调用时, SUBDATE()和DATE_SUB()的意义相同。

31SUBTIME(expr,expr2)

SUBTIME()从expr 中提取expr2 ，然后返回结果。expr 是一个时间或日期时间表达式，而xpr2 是一个时间表达式。

32SYSDATE()

返回当前日期和时间值，格式为'YYYY-MM-DD HH:MM:SS' 或YYYYMMDDHHMMSS

33TIME(expr)

提取一个时间或日期时间表达式的时间部分，并将其以字符串形式返回。

34TIMEDIFF(expr,expr2)

TIMEDIFF() 返回起始时间 expr 和结束时间expr2 之间的时间。 expr 和expr2 为时间或 date-and-time 表达式,两个的类型必须一样。

35 TIMESTAMP(expr) , TIMESTAMP(expr,expr2)

对于一个单参数,该函数将日期或日期时间表达式 expr 作为日期时间值返回.对于两个参数, 它将时间表达式 expr2 添加到日期或日期时间表达式 expr 中，将theresult作为日期时间值返回。

36TIMESTAMPADD(interval,int_expr,datetime_expr)

将整型表达式int_expr 添加到日期或日期时间表达式 datetime_expr中。 int_expr 的单位被时间间隔参数给定，该参数必须是以下值的其中一个： FRAC_SECOND、SECOND、 MINUTE、 HOUR、 DAY、 WEEK、 MONTH、 QUARTER或 YEAR。

37TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)

返回日期或日期时间表达式datetime_expr1 和datetime_expr2the 之间的整数差。其结果的单位由interval 参数给出。interval 的法定值同TIMESTAMPADD()函数说明中所列出的相同。

38TIME_FORMAT(time,format)

其使用和 DATE_FORMAT()函数相同, 然而format 字符串可能仅会包含处理小时、分钟和秒的格式说明符。其它说明符产生一个NULL值或0。

39TO_DAYS(date)

给定一个日期date, 返回一个天数 (从年份0开始的天数 )。

40UTC_DATE, UTC_DATE()

返回当前 UTC日期值，其格式为 'YYYY-MM-DD' 或 YYYYMMDD

41UTC_TIMESTAMP, UTC_TIMESTAMP()

返回当前UTC日期及时间值，格式为 'YYYY-MM-DD HH:MM:SS' 或YYYYMMDDHHMMSS

42WEEK(date[,mode])

该函数返回date 对应的星期数。

43WEEKDAY(date)

返回date (0 = 周一, 1 = 周二, ... 6 = 周日)对应的工作日索引 

44WEEKOFYEAR(date)

将该日期的阳历周以数字形式返回，范围是从1到53。它是一个兼容度函数，相当于WEEK(date,3)。

45YEAR(date)

返回date 对应的年份,范围是从1000到9999。

46YEARWEEK(date), YEARWEEK(date,start)

返回一个日期对应的年或周。start参数的工作同 start参数对 WEEK()的工作相同。结果中的年份可以和该年的第一周和最后一周对应的日期参数有所不同。