常规比较运算符
mysql>help comparison operators
怎么比较空值:
mysql>select * from t40 where id <=>null;
in、not in、between and操作符
between…and:加数值,表示在什么什么之间。
where year in(1920,1921)–》年在1920,或者在1921
like
- %:任意多个字符
- _:单个任意字符
- 转义字符:
where name like '%/_%' ESCAPE '/';
即查找名字当中带_的
regexp操作符
regexp是mysql特有的。正则表达式,功能强大,类似like。
eg:
select * from PLAYERS where BIRTH_DATE REGEXP ‘…[0,2,4,6,8]-.[0,2,4,6,8]-.[0,2,4,6,8]’;
—》
. :表示任意字符
[]:表示其中的任意一个字符
* :任意多个字符
^b:以b开头的
fy$:以fy结尾的
regexp ‘w‘:含有w的
上面的句子表示:前三个字符是任意,第四个字符是0或2或4或6或8
第五个字符是-,第六个字符是任意,第七个。。
逻辑操作符
与(and)或(or)非(not)异或(xor)
字符串函数
- lower(列名或者字符串)
将字符串参数值转换为全小写字母后返回 - upper(列名或者字符串)
将字符串参数值转换为全大写字母后返回 - concat(列名1,列名2…)
将多个字符串参数首尾相连后返回(紧紧相连,中间无分隔符) - concat_ws(指定分隔符,列1,列2)
- substr(字符串或者列名,开始位置,长度)
如果省略长度,则默认取到字符串的末尾
长度为负值,则为从字符串的尾部开始取。 - length(字符串或者列名)
返回字符串的存储长度
eg:select length(’你好’);
utf8编码时“你好”是占6个字节,gbk编码是占4个字节
7. char_length(字符串或列名)
返回字符串中字符的个数
8. instr(源字符串,子串)
从源字符串中返回子串所在的位置。
9. lpad(源字符串,指定长度,给定字符)
在源字符串的左边填充给定字符到指定的长度,返回填充后的字符串。
10. rpad(源字符串,指定长度,给定字符)
在源字符串的右边填充给定字符到指定的长度,返回填充后的字符串。
11. trim([{both | leading | trailing } [remstr] FROM] 源字符串),trim([remstr FROM]源字符串) –》裁剪命令
从源字符串中去掉两端、前缀或后缀字符并返回,如果不指定remstr,则去掉str两端的空格。不指定both、leading、trailing,则默认为both
(默认把左右的空格裁剪掉。)
12. replace(str,from str,to str)
在源字符串str中查找所有的子串form str (大小写敏感),找到后使用替代字符串to str替换它。返回的是替换后的字符串。
(e被替换成E)
13. ltrim(str),rtrim(str)
去掉字符串左边或者右边的空格。
14. repeat(str,count)
将字符串str重复count次后返回。
eg:select repeat(’mysql‘,3);
15. reverse(str)
将字符串str反转后返回。
eg:select reverse(‘abc’);
字符串_数字函数
- mysql> select ascii(97);
- mysql> select char(97);
3.
select char(77,121,83,81,76); ==char(77,121,83,81,76 using ascii);
4. format(数,小数位数,[语言])
语言默认为’en_US’
(四舍五入)
5. space(N)
返回由N个空格构成的字符串。
6. left(str,len)
返回最左边的len长度的子串。
7. right(str,len)
返回最右边的len长度的子串。
8. strcmp(str1,str2)
如果两个字符串相等则返回0,否则返回1。
9. round(X[,D])
将数字X四舍五入到指定的小数位数D,如果不指定D,默认为0,如果D为负数,则从小数点左边进行四舍五入。
10. truncate(X,D)
将数字X截断到指定的小数位数D,不四舍五入。
11. mod(N,M)
返回N/M后的余数。
12. ceil(X)
返回不小于x(小数)的最小整数。
13. floor(x)
返回不大于x(小数)的最大整数。
14. rand();
返回一个随机的浮点数。
rand(5);—》产生可重复5次的随机数列。
日期时间函数
一、
得到日期和时间:
mysql>select now(5);
等价于select sysdate(5);
得到时区:
mysql>select @@session.time_zone;
mysql>select @@session.time_zone=’+10:00’;
得到日期:select curdate();
得到时间:select curtime();
二、
①date_add
mysql>select now(),date_add(now(),interval 5 day); 加五天
mysql>select now(),date_add(now(),interval 100 minute);加100分钟
②date_sub
用法同date_add
③dateoff(’2015-12-16’,’2015-12-19’); 差多少天
④mysql>date(now()); 得到日期
year(now()); 得到年份
quarter(now()); 得到是哪个季度
week(now());
day(now());
hour(now());
……
⑤select extract(week from now()); 得到是第几周/年/月/。。。
select dayofweek(now()); 得到是一周的第几天
三、
类型转换:显式和隐式
☆尽量不要发生隐式类型转换!!
如何避免:
①定义列时,
存数字,则定义为decimal
存字符串时,则定义为varchar
存日期时,则定义为date
②select或者insert时,对常量来说:
数字:不加”(单引号)
字符串:一定要加” –》字符串不加单引号可能会出现灾难性后果!!!
日期:可加可不加,最好加”
cast函数
cast函数:显示类型转换
cast(列名 as 类型)
cast(常量 as 类型)
eg:
mysql>explain select * from t1 where name=cast(123 as char);
情况:
①字符串转成数字、日期等
②数字转换成字符串、日期等
③日期转换成数字、字符串等
ifnull函数
在mysql里碰见空值的时候有用。
例子:
mysql> select PLAYERNO,ifnull(LEAGUENO,'No LEAGUENO') from PLAYERS;
–》就是说,在LEAGUENO为空值时,用“No LEAGUENO”来替换空值显示。
if函数
①mysql> select if(LEAGUENO is not null,LEAGUENO,’Sorry No LEAGUENO’) from PLAYERS;
–》如果LEAGUENO 为空,就显示“Sorry No LEAGUENO”
②原来:
执行:
mysql>select name,if(year(BIRTH_DATE)<1950,’老队员’,’主力队员’),year(BIRTH_DATE) from PLAYERS;
–》如果出生年份<1950年,就显示为’老队员’,否则就显示’主力队员’
结果:
case函数
用法①:加上类似’M”F’的列属性
mysql> select name,case sex
-> when 'M' then '男性'
-> when 'F' then '女性'
-> else '无效值' end sex_format
-> from PLAYERS;
用法②:不加列属性的
mysql>select name,year(birth_date),case
->when year(birth_date)<1950 then '老队员'
-> when year(birth_date)<1960 then '成年队员'
-> when yaer(birth_date)<1970 then '年轻队员'
-> else '小队员' end grade
-> from PLAYERS;
聚集函数
distinct:去重,放在select里用–》select distinct 列 from 表;
desc:降序
select avg(ifnull(列名,0)) from 表名;
—》当列的属性值为NULL时,用0代替null进行计算
聚集函数:只能用在select和having语句中!!
sum():求和,null不参与计算
avg():求平均值,null不参与计算
count():求行数
max():求最大值
min():求最小值
group_concat():mysql里特有的分组函数,这些值一个挨一个的放置,例:
SQL基本查询语句
select [all | distinct] <目标列表达式> <聚集函数>
from <表名或视图名>
[where<条件>]
[group by<列名1>[having<条件>]] –》having是选择组的条件
[order by<列名1>[asc | desc]]
mysql独有的limit子句
例:得到编号最大的前四个球员的编号和名字
select playerno,name
from players
order by playerno DESC
limit 4
–》行先按照球员编号进行排序,然后使用limit从中挑出最前面的四行。如果没有order by子句,返回的四行就是不可预料的,所以,
limit常与order by一起用