一、关键词
1、unsigned : 非负数,定义非负,可以在定义主键的自增列时使用。
column_name int UNSIGNED AUTO_INCREMENT
2、desc :降序,从大到小;
asc 或 缺省 :为升序,从小到大,和order by 配合使用。
order by column_name desc
3、union :连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
column_name 为列名,table 为表名,[] 为可选条件,where conditions 为检索条件,
all 结果集中包含重复数据,distinct 为默认 结果集中不包含重复数据
select column_name1,column_name2...
from table1
[where conditions]
union [all | distinct]
select column_name1,column_name2...
from table2
[where conditions]
4、is null : 当列的值是 NULL,此运算符返回 true;
is not null :当前列的值不为空,运算符返回true。
select * from table column_name is null;
select * from table column_name is not null;
5、inner join、join :内连接,获取两个表中字段匹配关系的记录;
left join :左连接,获取左表所有记录,即使右表没有对应匹配的记录;
right join :右连接, 与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。
select table1.column_name1, teable2.column_name2...
from table1
left join table2 on table1.column_name1= table2.column_name2;
6、between :配合WHERE使用,选取介于value1和value2之间的数据范围,值可以是数值、文本或者日期。如需过滤范围之外的,可在 between 前加 not 关键词(not between value1and value2)
SELECT column_name FROM teable WHERE column_name BETWEEN value1 AND value2;
二、函数
(一)、处理字符串
1、replace(str,from_str,to_str)
把str中的from_str替换为to_str。
from_str 为null时会将str置为null,str不局限于字符型,但是to_str的类型需要和str一致。
replace('abcd','b',',') -- a,cd
2、substring_index(str, delimiter, number)
返回从字符串 str 的第 number 个出现的分隔符 delimiter 之后的子串。
如果 number 是正数,返回第 number 个字符左边的字符串。
如果 number 是负数,返回第(number 的绝对值(从右边数))个字符右边的字符串。
substring_index('aa*bb' , '*' , 1) -- aa
substring_index('aa*bb' , '*' , -1) -- bb
3、substr(str, start, length)
从字符串 str 的 start 位置截取长度为 length 的子字符串
下标从1开始包括start和length []闭合区间。
substr('abcd',2,2) -- bc
4、trim(str)
去掉字符串 str 开始和结尾处的空格。
trim(' qwer ') -- qwer
5、position(str1 IN str)
从字符串 str 中获取 str1 的开始位置
从1开始,返回位置包括 str1。
position( 'aad' in 'qweraadqwre') -- 5
6、character_length(str)
返回str的字符数。
character_length('asdf') -- 4
character_length('嘎嘎') -- 2
7、length(str)
返回str的字节数
utf8编码下,一个汉字3个字节,一个数字或字母1个字节
gbk编码下,一个汉字2个字节,一个数字或字母1个字节。
length('哈哈!') -- 9
length('qwer') -- 4
8、instr(field, str)
返回串 str 的位置,没找到返回0,从1开始
第一个参数 field 是字段,第二个参数 str 是要查询的串。
instr('qwer','e') -- 3
9、concat(str1,str2…)
返回多个字符串的连接值,
如果有任何一个参数为null,则返回值为null。
select concat('aa','-','bb') -- aa-bb
select concat('a',',','b',',','c') -- a,b,c
10、concat_ws(separator,str1,str2…)
和concat()一样,将多个字符串连接成一个字符串,
但是可以一次性指定分隔符,str参数为null时可正常返回连接值,
separator参数:
分隔符,如果分隔符为null,则返回值为null。
select concat_ws('-','a','b') -- a-b
select concat_ws(',','a','b','c') -- a,b,c
select concat_ws(',','a','b','c',null,'d',null,null) -- a,b,c,d
11、group_concat(column_name1,column_name2…)
将group by产生的同一个分组中的值连接起来,返回一个字符串结果。分组字符串连接。
注意:有最大长度的限制,超过最大长度就会被截断掉,默认长度为1024字符。
获取当前设置的长度
select @@global.group_concat_max_len;
show variables like "group_concat_max_len";
修改全局变量设置的长度,之后重启MySQL服务。
SET GLOBAL group_concat_max_len=10240;
SET SESSION group_concat_max_len=10240;
或者在MySQL配置文件中my.conf或my.ini中添加:
#[mysqld]
group_concat_max_len=10240
group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator 'separator'] )
[]为选参数,
distinct,去重,通过使用distinct可以排除重复值;
order by 排序字段 asc/desc ,如果要对连接结果集中的值进行排序,可以使用order by子句;
separator 'separator',是一个字符串值(可以是换行符),如果缺省不写,默认为一个逗号。
12、find_in_set(str,strlist)
查询str于strlist中的位置,下标从1开始,如在strlist中未找到,则返回0
str参数:
如为null,则返回null;如参数包含“,”,则无法进行使用,返回0。
strlist参数:
以“,”分隔,如:(1,2,6,8,10,22)、"1,2,3";如为null,则直接返回null。
也可以为具体某个字段。
select find_in_set(null,"1,2,3") -- null
select find_in_set("","1,2,3") -- 0
select find_in_set("1,2","1,2,3") -- 0
select find_in_set(0,"1,0,3") -- 2
select find_in_set(2,null) -- null
(二)、处理日期
1、timediff(time1 , time2)
返回time1,time2两个时间相减得到的差值;返回差值格式为,时:分:秒
time1-time2。
timediff( '2021-11-12 10:00:00' , '2021-11-12 18:00:00') -- -08:00:00
2、time_to_sec(time1)
返回time1的秒值,
时间值转换为秒值,可配合timediff()使用。
time_to_sec( '8:00:00' ) -- 28800
time_to_sec ( timediff( '2021-11-12 10:00:00' , '2021-11-12 18:00:00') ) -- -28800
3、timestampdiff(interval, time1,time2)
timediff的升级版,可以定义返回单位,
返回time1,time2两个时间相减得到的差值,结果单位由interval参数给出,
time2-time1,
interval参数:
frac_second 毫秒(低版本不支持MySQL 5.6之后才支持毫秒的记录和计算),
second 秒,minute 分钟,hour 小时,day 天,week 周,month 月,quarter 季度,year 年。
timestampdiff(second, '2021-11-12 10:00:00' , '2021-11-12 18:00:00') -- 28800
4、date_sub(date,INTERVAL expr type)、date_add(date,INTERVAL expr type)
date_sub() 函数从日期减去指定的时间间隔,
date_add() 函数从日期加上指定的时间间隔,
date 参数是合法的日期表达式,
expr 参数是希望添加的时间间隔(可以为负),
type 参数:常用参数
Second 秒,Minute 分钟,Hour 小时,Day 天,Week 周,Month 月,Quarter 季度,Year 年...
select now(),date_add(now(),interval 60 second) --当前时间加60秒
select now(),date_sub(now(),interval -60 second) --当前时间加60秒
5、timestampadd(interval, expr ,date )
与date_add()相仿,从日期加上指定的时间间隔,
interval参数:
frac_second 毫秒(低版本不支持MySQL 5.6之后才支持毫秒的记录和计算),
second 秒,minute 分钟,hour 小时,day 天,week 周,month 月,quarter 季度,year 年。
expr 参数是希望添加的时间间隔(可以为负),
date 参数是合法的日期表达式。
select now(),timestampadd(second,60,now()) --当前时间加60秒
(三)、服务器信息
1、version()
返回mysql服务器信息,
mycat也可以使用。
mycat:
version() -- 5.5.8-mycat-1.5.1-RELEASE-20160816173057
mysql:
version() -- 5.7.26-log
三、运算符