SQL常见问题整理（一）

1.从表中查询数据

（1）指定查询的行与列
where子句
（2）指定查询结果中列的别名
as
注：不能在where子句中使用列别名
（3）查询结果排序
order by
降序排序在排序字段后加DESC
自定义排序：使用field()将列值映射到一个序列中，该序列可以按照所需的排序为列赋值order by field(name,‘Henry’,‘Suzi’,‘Ben’)
（4）过滤重复行
distinct
统计唯一结果的数量 count(distinct A)
（5）处理NULL值
is null、is not null、<=>、<>
（6）多表查询
join…on…
（7）截取查询结果的开头、末尾或中间部分
limit，根据需要与order by结合使用。先order by 再limit，order by可以使用列别名。
选取结果集中间的某些行，可以使用带有两个参数的limit，这样可以选择任意行，表示跳过的行数和返回的行数，如limit2,1 表示跳过两行然后返回下一行
可以完成诸如“第三小或第三大的值”等查询，min()或max()无法完成这类的查询

2.表管理

（1）克隆表
create table…like来克隆表的结构，将原表的部分或全部数据复制到新表，可以使用insert into … select
新表结构与原表相同，新表没有数据：create table new_table like original_table
新表拥有与原表相同的数据（先create创建表new_table再insert into）：insert into new_table select * from original_table
（2）使用临时表
temporary

3.字符串

（1）转换字符串的大小写
upper()或lower()函数
如果upper()或lower()不起作用，可能字符串是二进制字符串，需要将它转换成拥有字符集和排序规则的非二进制字符串
只把首字母大写：concat(upper(left(str,1)),mid(str,2))
（2）使用正则表达式进行模式匹配
以特定子字符串开头的字符串：select name from metal where name regexp ‘^me’
以特定子字符串结尾的字符串：select name from metal where name regexp ‘d$’
任意位置包含特定子字符串的字符串：select name from metal where name regexp ‘in’
特定位置包含子字符串的字符串：select name from metal where name regexp ‘^…at’
（3）分割或连接字符串
连接：concat(A,’@’,B)
提取：
从左侧开始数，起始字符串开始返回左侧所有内容（包括起始字符串）：left(A,4)
从右侧开始数，从起始字符串开始返回右侧所有内容（包括起始字符串）：right(A,2)
从左侧开始数，起始字符串右侧所有内容（包括起始字符串）：substring(A,6)或mid(A,6)
从左侧开始数，起始字符串右侧指定字符串数量（包括起始字符串）：
substring(s,n,len)或mid(s,n,len)
substring(A,6,2)或mid(A,6,2)
返回指定字符串左侧或右侧的所有内容：substring_index(str,c,n) str字符串中第n次出现的字符c
substring(str,’@’,1) ‘@‘左侧全部内容
substring(str,’@’,-1) '@'右侧全部内容
（4）搜索子字符串
locate()或模式匹配
返回子串出现的位置，如果不存在返回0：locate(‘in’,name)
第三个可选参数表示从字符串的哪个位置开始查找：locate(‘in’,name,3)

4.日期与时间

（1）选择时间数据类型
日期：date()
时间值：time()
日期和时间的结合值：datetime()、timestamp()
（2）更改MySQL的日期格式
date_format(d,’%M %d,%Y’)
两个参数，第一个是date,datetime,timestamp类型的值，另一个是显示格式的字符串。
time_format(dt,’%r’)
两个参数，第一个是date,datetime,timestamp类型的值，另一个是显示格式的字符串，但只处理格式字符串中与时间相关的部分

格式化字符	含义
%Y	年份，四位数字
%y	年份，后两位数字
%M	完整的月份名称（January…December）
%b	月份名称，头三个字母（Jan…Dec）
%m	月份，两位数字（01…12）
%c	月份（1…12）
%d	月份中的天数，两位数字（01…31）
%e	月份中的天数,（1…31）
%W	星期的名字（Sunday-Saturday）
%r	时间，12小时制，以AM或PM结尾
%T	时间,24小时制
%H	小时，两位数字（00…23）
%i	分钟,两位数字（00…59）
%s	秒钟,两位数字（00…59）
%%	字母“%”

（3）获取当前的日期或时间
当前的日期或时间值：curdate()、curtime()、now()
utc时间值：utc_date()、utc_time()、utc_timestamp()
（4）追踪行的修改时间
使用timestamp和datetime数据类型的自动初始化和自动更新属性
（5）提取日期或时间值的各部分
提取时间值函数：

函数	返回值
YEAR()	返回年份
MONTH()	返回月份 （1…12）
MONTHNAME()	返回月份全名（January…December）
DAYOFMONTH()	返回月份中的天数（1…31）
DAYNAME()	返回星期的名字（Sunday…Saturday）
DAYOFWEEK	返回星期中的天数（Sunday…Saturday分别为1…7）
WEEKDAY()	返回星期中的天数（Monday…Sunday分别为0…6）
DAYOFYEAR()	返回一年中的天数（1…366）
HOUR()	返回小时（00…23）
MINUTE()	返回分钟（00…59）
SECOND()	返回秒钟（00…59）
EXTRACT()	返回日期中的相应部分 （year、month、day等都可以）

与日期相关的函数适用date、datetime、timestamp
与时间相关的函数适用time、datetime、timestamp
格式化函数：
格式化日期：date_format(dt,’%Y-%m-%d’)
格式化时间：time_format(dt,’%H’)
（6）合成日期或时间值
根据小时、分和秒合成time值：maketime(10,30,58) 返回 10:30:58
利用现有的值，再加上想替换的部分，共同组成新的日期或时间：
date_format(d,’%Y-%m-01’)
time_format(t1,’%H:%i:00’)
使用日期提取函数将所需部分抽出来，再通过concat()重新组合在一起：
concat(year(d),’-’,lpad(month(d),2,‘0’),’-01’) （lpad函数用于填充0，保证月份用两位数展现）
（7）时间值和基本单位之间的转换
时间值和秒之间转换：time_to_sec()、sec_to_time()
日期值和天之间转换：to_days()、from_days()
时间值与秒之间转换：unix_timestamp()、from_unixtime()
（8）计算日期或时间之间的间隔
时间差函数：datediff(d1,d2) d1-d2
datediff(d2,d1) d2-d1
将时间值转换成基本单位再求差：timestampdiff(unit,val1,val2) unit是间隔单位，val1和val2是计算间隔的值val1-val2，unit可以指定为microsecond、second、minute、hour、day、week、month、quarter或year
（9）日期或时间值的加法运算
①使用时间加法函数：
两个时间或时间与日期时间值的总和（不能加日期）：
addtime(t1,t2) t1+t2
addtime(dt,t) dt+t
时间与日期，时间与日期时间值的总和（可以加日期）：
timestamp(d,t) d+t
timestamp(dt,t) dt+t
②使用+interval或-interval运算符
unit是间隔单位，val有多少个单位
加：date_add(d,interval val unit)
减：date_sub(d,interval val unit)