1.单行处理函数
1.1 lower大写变小写
mysql> select lower(sname) from student;
1.2 upper小写变大写
mysql> select upper(sname) from student;
1.3 substr截取
mysql> select substr(sname,1,3) from student;
找出首字母位X的姓名
mysql> select sname from student where substr(sname,1,1)='X';
1.4 concat字符串拼接
mysql> select concat(sname,' ',sage) from student;
1.5 length取长度
mysql> select length(sname) as snameLength from student;
1.6 trim去除空格
mysql> select * from student where sname = trim(' Lisa ');
1.7 round四舍五入
mysql> select round('24.56') from student;
结果保留两位小数
mysql> select round(26.4562,2) from student;
1.8 rand生成随机数
生成10以内的随机数
mysql> select rand(10) from student;
生成10以内的随机数(整数)
mysql> select round(rand()*10,0) from student;
1.9 ifnull(数据,被当作什么值)可以将null转换为一个具体值
问题:查询esal 和ecomm的和
此次发现只要有一个数据为null,其结果直接为null
问题:计算每个员工的年薪?
mysql> select ename,(esal + ifnull(ecomm,0)) * 12 as '年薪' from emploee;
1.10 case. . when. . then. . when. . then. .else. . end
问题:当员工岗位为B的时候工资上调10%,为C的时候上调50%,其他岗位的工资不变
mysql> select
-> ename,
-> esal as oldsal,
-> (case ejob when 'B' then esal*1.1 when 'C' then esal*1.5 else esal end) as newsal
-> from
-> emploee;
2.多行处理函数(分组函数)
5个:
1.1 count计数,统计该字段下不为null的个数
mysql> select count(ename) from emploee;
统计该字段所有的个数
mysql> select count(*) from emploee;
1.2 sum求和
mysql> select sum(esal) from emploee;
1.3 avg平均值
mysql> select avg(esal) from emploee;
1.4 max最大值
mysql> select max(esal) from emploee;
1.5 min最小值
mysql> select min(esal) from emploee;
注意:分组查询不能直接用于where子句中;