mysql函数及关键词操作

1.
AVG()
返回某列的平均值
Eg：SELECT AVG(id)from mytest;

COUNT()
返回某列的行数
Eg：SELECT count(id)from mytest;
使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

MAX()
返回某列的最大值
Eg：SELECT MAX(id)from mytest;

MIN()
返回某列的最小值
Eg：SELECT MIN(id)from mytest;

SUM()
返回某列所有值之和
Eg：SELECT SUM(id)from mytest;

MOD()
返回余数；
eg：SELECT mod(111,10); 结果为1
 
2.
删除mysql数据表中的内容,但是不删除表:
TRUNCATE TABLE +表名  

3.
mysql回滚:支持数据引擎innodb,不支持mysiam 后者性能更好
查看引擎：show engines; default为默认支持的 yes为支持的
查看某表的引擎：show table status like ‘表名’
Start transaction（begin）开启事物 +rollback 回滚 +commit 提交事物

4.
主键：方便操作和管理数据库，表示每行特定标识的列
特点：任意两行不能有相同的主键值；
      每一行都必须有一个主键值，不能为NULL；
      主键值不允许修改或者更新；
      某个主键值被删除后，再以后的新行不能再使用；(自增属性)
5.
DISTINCT：返回数据表中唯一不同的值
DISTINCT关键字作用于所有的列，不仅仅是跟在它后边的那一列，不能部分使用，除非两列数据相同，否则所有的行都会被检索出来。
警告：DISTINCT不能用于COUNT(*)
如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)。类似地，DISTINCT必须使用列名，不能用于计算或表达式
SELECT count(distinct name)from mytest ;

6.    限制检索行数用法
SQL server 和access:TOP
Eg:select x TOP 5 from y; 检索前5行
DB2：FETCH FIRST 5 ROWS ONLY
Eg:select x from y FETCH FIRST 5 ROWS ONLY;检索前5行
Oracle:ROWNUM（行计数器）
Eg：select x from y where rownum <=5; 检索前5行
MySQL.MariaDB.PostgreSQL.SQLite:limit
Eg:select x from y limit 5; 检索前5行
Eg:select x from y limit 5 offset 2 ; 偏移到第三行检索5行
Eg:select x from y limit 2,5 ;等同于上边的结果  <-----MySQL和MariaDB快捷键

7.    使用注释
行内注释：-- 连续两个连字符嵌在语句中 或者#注释
多行注释：/* --------*/ 中间的都会被注释掉

8.    数据排序
Oder by:可以跟一个或者多个列，多列排序按照先第一列，接着后一列，除非第一列完全不同，否则不会按照后一列排序，用于排序的列也可以不是检索的列
Eg：select x,y from z order by x,y; 先按照x去排序，x相同按照y排序
select x,y from z order by Z; 按照Z去排序
select x,y from z order by 1,2; 先按照x去排序，x相同按照y排序 <---按列的位置排序
数据排序方向有升序ASC（ASCENDING）和降序DESC（DESCENDING）排列，默认的是升序，不指定排列顺序则按默认的为升序排列
Eg:select x,y from z order by x desc,y; 先按照x去排序，y默认升序
  select x,y from z order by x desc,y desc; 先按照x去排序，x相同按照y排序

9.  WHERE 操作
在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤；
同时使用ORDER BY和WHERE时，应让ORDER BY位于WHERE之后，否则会产生错误
Eg：select * from pkey where age <=5 order by age desc ; 筛选<=5h后再降序排列
操作符 说 明
=      等于
< >    不等于
!=     不等于
<      小于
<=     小于等于
!      不小于
>      大于
>=     大于等于
!>     不大于
BETWEEN 在指定的两个值之间
IS NULL 为NULL值

将值与字符串类型的列进行比较，就需要限定引号，用来与数值列进行比较的值不用引号；
使用BETWEEN时，必须指定两个值—低端值和高端值，这两个值必须用AND关键字分隔；
AND操作符
用在WHERE子句中的关键字，用来指示检索满足所有给定条件的行，可以增加多个过滤条件，每个条件间都要使用AND关键字；
OR操作符
WHERE子句中使用的关键字，用来表示检索匹配任一给定条件的行；
SQL在处理OR操作符前，优先处理AND操作符，任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符；
Eg：where name=’z’ or name=’x’ and age<=20 <==>where name=’z’ or (name=’x’ and age<=20)
where (name=’z’ or name=’x’ )and age<=20 <------我们要的目的
IN操作符
IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。IN取一组由逗号分隔、括在圆括号中的合法值 作用同等于 OR
Where age in(3,6) <==>   where age=3 or age=6   相等
为什么要使用IN操作符？其优点为：
在有很多合法选项时，IN操作符的语法更清楚，更直观；
在与其他AND和OR操作符组合使用IN时，求值顺序更容易管理；
IN操作符一般比一组OR操作符执行得更快；
IN的最大优点是可以包含其他SELECT语句，能够更动态地建立WHERE子句；
NOT操作符
WHERE子句中的NOT操作符有且只有一个功能，那就是否定其后所跟的任何条件。
Eg:where not age=6  <==> where age <>6 <==> where age !=6 
MariaDB支持使用NOT否定IN、BETWEEN和EXISTS子句。大多数DBMS允许使用NOT否定任何条件
LIKE操作符
通配符本身实际上是SQL的WHERE子句中有特殊含义的字符，SQL支持几种通配符。为在搜索子句中使用通配符，必须使用LIKE操作符。
通配符搜索只能用于文本字段（串），非文本数据类型字段;
通配符可在搜索模式中的任意位置使用，并且可以使用多个通配符，区分大小写；
通配符%看起来可以匹配任何东西，但有个例外，这就是NULL。Where age like ‘%’;
下划线（_）通配符
下划线的用途与%一样，但它只匹配单个字符，而不是多个字符；
DB2不支持通配符_。如果使用的是Microsoft Access，需要使用?而不是_；
Eg:where name like ‘_123’ 一个_代表匹配一个字符，可以多个使用；
方括号（[ ]）通配符
方括号（[]）通配符用来指定一个字符集，它必须匹配指定位置的一个字符；
只有微软的Access和SQL Server支持集合；
Eg：where name like ‘[ab]%’    <------>匹配以a或者b开头的名字
    where name like ‘[^ab]%’   <------>匹配不以a或者b开头的名字 ^脱字符来否定
如果用的是Microsoft Access，需要用!而不是^来否定一个集合，使用的是[!ab]而不是[^ab]；

10.
NULL ：无值（no value），它与字段包含0、空字符串或仅仅包含空格不同

11.    Mysql时间处理函数
Select now(); 当前时间：2018-02-08 16:17:27
等同于 current_timestamp()，current_timestamp，localtime，localtime()，
localtimestamp()，localtimestamp

Select sysdate(); 当前时间：2018-02-08 16:31:55
与now()的区别是它是动态实时获取时间 now再次调用还是第一次的值
mysql> select now(),sleep(3),now();
+---------------------+----------+---------------------+
| now()               | sleep(3) | now()               |
+---------------------+----------+---------------------+
| 2018-02-08 16:32:38 |        0 | 2018-02-08 16:32:38 |
mysql> select sysdate(),sleep(3),sysdate();
+---------------------+----------+---------------------+
| sysdate()           | sleep(3) | sysdate()           |
+---------------------+----------+---------------------+
| 2018-02-08 16:33:26 |        0 | 2018-02-08 16:33:29 |

Select curdate(); 当前时间：2018-02-08
等同于：current_date()，current_date

Select curtime(); 当前时间：16:43:02
等同于：current_time()，current_time

Select utc_timestamp()；当前utc(世界标准时间)时间：2018-02-08 08:46:19
select utc_date();   当前时间：2018-02-08
select utc_time(); 当前时间：08:46:46 
|----------------------------------------------------------------------------------------------------------------|
set @dt='2018-02-09 09:44:01.1234';
select date(@dt);         --->2018-02-09         获取日期
select time(@dt);         --->09:44:00           获取时间
select year(@dt);         --->2018              获取年份
select month(@dt);        --->2                获取月份
select day(@dt);          --->9                 获取日
select hour(@dt);         --->9                 获取时
select minute(@dt);       --->44                获取分
sselect second(@dt);      --->1                 获取秒
select microsecond(@dt);  --->123400            获取微秒
select quarter(@dt);      --->1                 获取季度
select week(@dt);        --->5                 获取周
select weekofyear(@dt);   --->6                 获取周
select dayofyear(@dt);    --->40                 获取在年中的天数
select dayofmonth(@dt);  --->9                  获取在月中的天数
select dayofweek(@dt);   --->6                  获取在周中的天数周日为第一天
select weekday(@dt);     --->4                  获取在周中的天数 周一到周日(0-6)
select yearweek(@dt);    --->201805             获取年和周
|----------------------------------------------------------------------------------------------------------------|
MySQL Extract() 函数除了没有date(),time() 的功能外，其他功能一应具全
Eg:
select extract(year from @dt);    ---2018          获取年份
select extract(month from @dt);  ---2              获取月份
select extract(day from @dt);     ---9             获取日
select extract(hour from @dt);    ---9             获取时
select extract(minute from @dt);    ---44          获取分
select extract(second from @dt);    ---1            获取秒
select extract(microsecond from @dt);    ---123400  获取微秒
select extract(quarter from @dt);    ---1            获取季度
select extract(week from @dt);    ---5             获取周
select extract(year_month from @dt);    ---201802    获取年和月
select extract(day_hour from @dt);    ---909        获取日和时
select extract(day_minute from @dt);  ---90944      获取日时分
select extract(day_second from @dt);  ---9094401    获取日时分秒
select extract(day_microsecond from @dt);---9094401123400获取日时分秒微秒
select extract(hour_minute from @dt);   ---944      获取时和分
select extract(hour_second from @dt);   --94401     获取时分秒
select extract(hour_microsecond from @dt);---94401123400获取时分秒微秒
select extract(minute_second from @dt);   --4401     获取分和秒
select extract(minute_microsecond from @dt); ---4401123400 获取分秒微秒
select extract(second_microsecond from @dt);  ---1123400 获取秒微秒
|----------------------------------------------------------------------------------------------------------------|
select dayname(@dt);      ---Friday     获取星期的英文
select monthname(@dt);   ---February   获取月份英文
select last_day(@dt);      --- 2018-02-28 获取月份的最后一天
|----------------------------------------------------------------------------------------------------------------|
DATE_ADD(date,INTERVAL expr type) 从日期加上指定的时间间隔  
DATE_SUB(date,INTERVAL expr type) 从日期减去指定的时间间隔 
MySQL date_sub() 日期时间函数 和 date_add() 用法一致，并且可以用INTERNAL -1 xxx的形式互换使用；另外，MySQL 中还有两个函数 subdate(), subtime()，建议，用 date_sub() 来替代。
Eg:
select date_add(@dt,interval 1 year); ---2019-02-09 09:44:01.123400
select date_add(@dt,interval 1 quarter);---2018-05-09 09:44:01.123400
select date_add(@dt,interval 1 month); ---2018-03-09 09:44:01.123400
select date_add(@dt,interval 1 week);  ---2018-02-16 09:44:01.123400
select date_add(@dt,interval 1 day);   ---2018-02-10 09:44:01.123400
select date_add(@dt,interval 1 hour);  ---2018-02-09 10:44:01.123400
select date_add(@dt,interval 1 minute);---2018-02-09 09:45:01.123400
select date_add(@dt,interval 1 second);---2018-02-09 09:44:02.123400
select date_add(@dt,interval 1 microsecond);---2018-02-09 09:44:01.123401
Date_sub同理
select date_sub(@dt,interval -1 year); ---2019-02-09 09:44:01.123400
select date_sub(curdate(),interval 1 year); ---2017-02-09
|----------------------------------------------------------------------------------------------------------------|
MySQL 另类日期函数：period_add(P,N), period_diff(P1,P2)  
函数参数“P” 的格式为“YYYYMM” 或者 “YYMM”，第二个参数“N” 表示增加或减去 N
 MySQL period_add(P,N)：日期加/减去N月。  
SELECT PERIOD_ADD(201705,2), PERIOD_ADD(201705,-2);-- 201707  20170503  
SELECT PERIOD_ADD(1705,2), PERIOD_ADD(1705,-2);-- 201707  20170503 
 period_diff(P1,P2)：日期 P1-P2，返回 N 个月。  
SELECT PERIOD_DIFF(201706, 201703);--   
 datediff(date1,date2)：两个日期相减 date1 - date2，返回天数  
SELECT DATEDIFF('2017-06-05','2017-05-29');-- 7  
TIMEDIFF(time1,time2)：两个日期相减 time1 - time2，返回 TIME 差值  
SELECT TIMEDIFF('2017-06-05 19:28:37', '2017-06-05 17:00:00');-- 02:28:37  
|----------------------------------------------------------------------------------------------------------------|
MySQL日期转换函数  
SELECT TIME_TO_SEC('01:00:05'); -- 3605   时间->秒数
SELECT SEC_TO_TIME(3605);-- 01:00:05     秒数->时间
|----------------------------------------------------------------------------------------------------------------|
MySQL （日期、天数）转换函数：to_days(date), from_days(days)  
SELECT TO_DAYS('0000-00-00'); -- NULL   
SELECT TO_DAYS('2017-06-05'); -- 736850  
SELECT FROM_DAYS(0);           -- '0000-00-00'   
SELECT FROM_DAYS(736850);      -- '2017-06-05'  
 |----------------------------------------------------------------------------------------------------------------|
MySQL Str to Date （字符串转换为日期）函数：str_to_date(str, format)    
SELECT STR_TO_DATE('06.05.2017 19:40:30', '%m.%d.%Y %H:%i:%s');-- 2017-06-05 19:40:30  
SELECT STR_TO_DATE('06/05/2017', '%m/%d/%Y');                  -- 2017-06-05  
SELECT STR_TO_DATE('2017/12/3','%Y/%m/%d')             -- 2017-12-03  
SELECT STR_TO_DATE('20:09:30', '%h:%i:%s')             -- NULL(超过12时的小时用小写h，得到的结果为NULL)  
  
-- 日期时间格式化  
SELECT DATE_FORMAT('2017-05-12 17:03:51', '%Y年%m月%d日 %H时%i分%s秒');-- 2017年05月12日 17时03分51秒(具体需要什么格式的数据根据实际情况来;小写h为12小时制;)  
SELECT TIME_FORMAT('2017-05-12 17:03:51', '%Y年%m月%d日 %H时%i分%s秒');-- 0000年00月00日 17时03分51秒(time_format()只能用于时间的格式化)  
-- STR_TO_DATE()和DATE_FORMATE()为互逆操作  
  
-- MySQL 获得国家地区时间格式函数：get_format()  
-- MySQL get_format() 语法：get_format(date|time|datetime, 'eur'|'usa'|'jis'|'iso'|'internal'  
-- MySQL get_format() 用法的全部示例：  
SELECT GET_FORMAT(DATE,'usa');      -- '%m.%d.%Y'   
SELECT GET_FORMAT(DATE,'jis');      -- '%Y-%m-%d'   
SELECT GET_FORMAT(DATE,'iso');      -- '%Y-%m-%d'   
SELECT GET_FORMAT(DATE,'eur');      -- '%d.%m.%Y'   
SELECT GET_FORMAT(DATE,'internal');     -- '%Y%m%d'   
SELECT GET_FORMAT(DATETIME,'usa');      -- '%Y-%m-%d %H.%i.%s'   
SELECT GET_FORMAT(DATETIME,'jis');      -- '%Y-%m-%d %H:%i:%s'   
SELECT GET_FORMAT(DATETIME,'iso');      -- '%Y-%m-%d %H:%i:%s'   
SELECT GET_FORMAT(DATETIME,'eur');      -- '%Y-%m-%d %H.%i.%s'   
SELECT GET_FORMAT(DATETIME,'internal'); -- '%Y%m%d%H%i%s'   
SELECT GET_FORMAT(TIME,'usa');      -- '%h:%i:%s %p'   
SELECT GET_FORMAT(TIME,'jis');      -- '%H:%i:%s'   
SELECT GET_FORMAT(TIME,'iso');      -- '%H:%i:%s'   
SELECT GET_FORMAT(TIME,'eur');      -- '%H.%i.%s'   
SELECT GET_FORMAT(TIME,'internal');     -- '%H%i%s'  
  
  
-- MySQL 拼凑日期、时间函数：makdedate(year,dayofyear), maketime(hour,minute,second)  
SELECT MAKEDATE(2017,31);   -- '2017-01-31'   
SELECT MAKEDATE(2017,32);   -- '2017-02-01'  
SELECT MAKETIME(19,52,35);  -- '19:52:35'  
  
-- MySQL 时区（timezone）转换函数：convert_tz(dt,from_tz,to_tz)  
SELECT CONVERT_TZ('2017-06-05 19:54:12', '+08:00', '+00:00'); -- 2017-06-05 11:54:12  
  
  
-- MySQL （Unix 时间戳、日期）转换函数  
-- unix_timestamp(), unix_timestamp(date), from_unixtime(unix_timestamp), from_unixtime(unix_timestamp,format)  
-- 将具体时间时间转为timestamp  
SELECT UNIX_TIMESTAMP();-- 当前时间的时间戳：1494815779  
SELECT UNIX_TIMESTAMP('2017-05-15');-- 指定日期的时间戳：1494777600  
SELECT UNIX_TIMESTAMP('2017-05-15 10:37:14');-- 指定日期时间的时间戳：1494815834  
  
-- 将时间戳转为具体时间  
SELECT FROM_UNIXTIME(1494815834);-- 2017-05-15 10:37:14  
SELECT FROM_UNIXTIME(1494815834, '%Y年%m月%d日 %h时%分:%s秒');-- 获取时间戳对应的格式化日期时间  
  
-- MySQL 时间戳（timestamp）转换、增、减函数  
SELECT TIMESTAMP('2017-05-15');-- 2017-05-15 00:00:00  
SELECT TIMESTAMP('2017-05-15 08:12:25', '01:01:01');-- 2017-05-15 09:13:26  
SELECT DATE_ADD('2017-05-15 08:12:25', INTERVAL 1 DAY);-- 2017-05-16 08:12:25  
SELECT TIMESTAMPADD(DAY, 1, '2017-05-15 08:12:25');-- 2017-05-16 08:12:25; MySQL timestampadd() 函数类似于 date_add()。  
  
SELECT TIMESTAMPDIFF(YEAR, '2017-06-01', '2016-05-15');-- -1  
SELECT TIMESTAMPDIFF(MONTH, '2017-06-01', '2016-06-15');-- -11  
SELECT TIMESTAMPDIFF(DAY, '2017-06-01', '2016-06-15');-- -351  
SELECT TIMESTAMPDIFF(HOUR, '2017-06-01 08:12:25', '2016-06-15 00:00:00');-- -8432  
SELECT TIMESTAMPDIFF(MINUTE, '2017-06-01 08:12:25', '2016-06-15 00:00:00');-- -505932  
SELECT TIMESTAMPDIFF(SECOND, '2017-06-01 08:12:25', '2016-06-15 00:00:00');-- -30355945  

12.    Mysql拼接字段  concat  GROUP_CONCAT()
Mysql：
SELECT concat(name ,'(',sex,')')from mytest ORDER BY name; select concat('12',',34');
SELECT name ,GROUP_CONCAT(sex) from tmp where name in('b','c') GROUP BY name ;

13.    Group by
“多列分组”实际上就是就是按照多列（多字段）合并后的值进行分组；
Group by语句中select指定的字段必须是“分组依据字段”，其他字段若想出现在select中则必须包含在聚合函数(sum,max,min,avg,count)中；
GROUP BY子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组;
如果在GROUP BY子句中嵌套了分组，数据将在最后指定的分组上进行汇总；
GROUP BY子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）；
如果分组列中包含具有NULL值的行，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组；
GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前；
Eg：select name ,sex ,sum(id) from mytest group by name ,sex;
select name ,sex ,sum(id) from mytest where id>5 group by name ,sex ORDER BY sum(id) desc;
Where和Having区别：
Where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，where条件中不能包含聚组函数，使用where条件过滤出特定的行
Having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件过滤出特定的组，也可以使用多个分组标准进行分组。
Eg：select name ,sex ,sum(id) from mytest group by name ,sex having sum(id)>10;
Eg：select name ,sex ,sum(id) from mytest where id >1 group by name ,sex;
Eg：select name ,sex ,sum(id) from mytest where id >1 group by name ,sex having sum(id)>3;    
Eg: select name,sex,sum(id) from mytest
 where id >1 group by name,sex having sum(id)>2 order by id;

14.    Union
UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔;
UNION中的每个查询必须包含相同的列、表达式或聚集函数;
列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含转换的类型;
使用UNION时，重复的行会被自动取消;
用UNION组合查询时，只能用一条ORDER BY子句，它必须位于最后一条SELECT语句之后;

15.    插入和复制
插入数据：
INSERT INTO mytest (id, NAME, sex, idd) VALUES    (15, 'x', 'c', 6);
INSERT into mytest(id, NAME, sex, idd) SELECT id,name,sex,idd from tmp;
复制表：
CREATE table tmp as SELECT * from mytest;

16.    更新和删除数据
更新：
UPDATE mytest set name='v' ,sex='b' where id=17;
删除:但不删除表名
DELETE from mytest where id =17;
TRUNCATE table tmp;

17.    约束
主键：
主键是一种特殊的约束，用来保证一列（或一组列）中的值是唯一的，而且永不改动。换句话说，表中的一列（或多个列）的值唯一标识表中的每一行；
表中任意列只要满足以下条件，都可以用于主键：
任意两行的主键值都不相同；
每行都具有一个主键值（即列中不允许NULL值）；
包含主键值的列从不修改或更新；
主键值不能重用。如果从表中删除某一行，其主键值不分配给新行；

外键：
外键是表中的一列，其值必须列在另一表的主键中。外键是保证引用完整性的极其重要部分；