查询(select)
select执行顺序
select 字段
from 表名
where ……
group by ……
having ……
order by ……
limit ……
执行顺序
- 执行where语句过滤原始数据
- 执行group by进行分组
- 执行having对分组数据进行操作
- 执行select选出数据
- 执行order by排序
- 执行limit选出数据
若是连接查询,则先经过on产生中间表,再经过where进行过滤,最后再由having进行筛选
select相关
select * from emp; #与java等关联时,不建议使用*,不容易维护
select sal*12 as yearsal from emp; #as可将字段重命名
select ename,sal from emp; #多个字段用逗号隔开
1、查询条件
运算符 | 说明 |
---|---|
= | 等于 |
<>或!= | 不等于 |
< | 小于 |
<= | 小于等于 |
> | 大于 |
>= | 大于等于 |
between...and... | 两个值之间,包含边界,等同于 >= and <= |
is null | 为null (is not null 不可空) |
and | 并且 |
or | 或者 |
in | 包含在内(not in 不包含在内) |
not | 取非,主要用在is或in中 |
like | 模糊查询,支持%或匹配,%匹配任意个字符,一个下划线匹配一个字符 |
2、排序
select * from emp order by deptno,job
;多个字段用逗号分开,先排前面再根据后面细排
select * from emp order by deptno asc;
asc表示升序,是默认排序,可不写
select * from emp order by deptno desc;
desc表示降序
select * from emp order by 3;
不建议使用字段编号,不健壮,不方便维护
3、单行处理函数
单行处理函数,也叫数据处理函数
函数 | 说明 |
---|---|
Lower | 转换小写 |
upper | 转换大写 |
left | 截取字符串左边部分,left(字符串,长度) |
right | 截取字符串右边部分,right(字符串,长度) |
substr | 取子串(substr(被截取的字符串,起始下标,截取的长度)) |
length | 取长度 |
trim | 去空格,可去除首尾空格,不会去除中间空格。rtrim(去掉右侧),ltrim(去掉左侧) |
str_to_date | 将字符串转换成日期,具体格式str_to_date (字符串,匹配格式) |
date_format | 格式化日期 |
format | 设置千分位,format(数据,小数位数) |
round | 四舍五入 round(数字,小数位数) |
rand() | 生成随机数 |
ifnull | 可以将null转换成一个具体值ifnull(原始数据,替换后的数据) |
concat() | 拼接字符串,concat(字段1,'字符串',字段2,'字符串') |
select lower(ename) from emp;//名字小写
select upper(job) from emp;//job大写
select * from emp where substr(ename,1,1)='M';//名字M开头的员工
select length(ename) from emp;//名字长度
select * from emp where job=trim(' MANAGER ');//去除首位空格,因此能查出结果
select * from emp where hiredate=str_to_date('02-20-1981',%m-%d-%Y);//查询此日期入职员工
select empno, ename, date_format(hiredate, '%Y-%m-%d %H:%i:%s') as hiredate from emp;//将入职日期格式化
select empno, ename, Format(sal, 2) from emp;//薪水加入千分位并保留两位小数
select round(123.56,1);//四舍五入,保留一位小数
select * from emp order by rand() limit 2;//随机抽取记录数
select ifnull(comm,0) from emp;//comm如果有null,替换为0
4、日期和时间
常用日期和时间处理函数
函数 | 说明 |
---|---|
AddDate() | 增加一个日期(天,周等),AddDate('日期',interval 数字 单位)或AddDate('日期',数字)--此时默认为day |
AddTime() | 增加一个时间(时,分等),AddTime('hh:mm:ss.xxxxx','hh:mm:ss.xxxxx') |
DateDiff() | 计算两个日期之差 |
now() | 返回当前日期和时间 |
CurDate() | 返回当前日期 |
CurTime() | 返回当前时间 |
Date() | 返回日期时间的日期部分 |
Time() | 返回日期时间的时间部分 |
Year() | 返回一个日期的年份部分 |
Month() | 返回一个日期的月份部分 |
Day() | 返回一个日期的天数部分 |
Hour() | 返回一个时间的小时部分 |
Minute() | 返回一个时间的分钟部分 |
Second() | 返回一个时间的秒部分 |
DayOfWeek() | 对于一个日期,返回其星期几 |
日期格式说明
日期格式 | 说明 |
---|---|
%Y | 4位年份 |
%y | 2位年份 |
%m | 月份(01......12) |
%c | 月份(1......12) |
%H | 小时(00......23) |
%h | 小时(01......12) |
%i | 分钟(00......59) |
%S或%s | 秒(00......59) |
%r | 时间,12小时制(hh:mm:ss [AP]M) |
%T | 时间,24小时制(hh:mm:ss) |
5、case语句
case...when...then...else...end
case具有两种格式,简单case函数与case搜索函数
a、简单case函数
格式
case 列名
when 条件值1 then 选择项1
when 条件值2 then 选择项2
......
else 默认值 end
select ename,deptno,(case deptno when 10 then 'aaaa' when 20 then 'bbbb' else 'cccc' end) as col from emp;
b、case搜索函数
格式
case
when 列名(=,<,>)条件值1 then 选择项1
when 列名(=,<,>)条件值2 then 选择项2
......
else 默认值 end
select ename,deptno,(case when deptno=10 then 'aaaa' when deptno=20 then 'bbbb' else 'cccc' end) as col from emp;
注意:Case函数只返回第一个符合条件的值,剩下的Case部分将会被自动忽略
6、多行处理函数
多行处理函数,又叫分组函数、聚合函数,聚合函数可在select中一起使用
函数 | 说明 |
---|---|
count | 取得记录数 |
sum | 求和 |
avg | 求平均 |
max | 求最大值 |
min | 求最小值 |
注意:分组函数自动忽略空值,无需where排除,且分组函数不能使用在where关键字后面
select count(*) from emp;//取得记录总数
select count(distinct(job)) from emp;//取得不同job数,distinct关键字可去重
select sum(sal+ifnull(comm,0)) from emp;//计算薪资总数,因为sum不计算含null记录,因此要先判空
select avg(sal) from emp;//取得平均薪水
select max(sal) from emp;//取得最大薪水
select ename,min(str_to_date(hiredate,'%Y-%m-%d')) from emp;//取得最早入职员工
7、分组查询
涉及到分组查询的主要是两个关键字:group by与having
group by进行分组
having对分组后的数据再进行过滤
SQL语句中若有group by 语句,那么在select语句后面只能跟分组函数+参与分组的字段
能在where中过滤的数据,尽量在where中过滤,效率较高,having的过滤是专门对分组之后的数据进行过滤的
select job,deptno,sum(sal) from emp group by job,deptno;//工作与部门进行分组,计算工资和
8、去重
使用关键字distinct去掉重复记录
# 列出所有不同的职位
select distinct job from emp;
# 查出所有部分都设置有什么职位
select distinct deptno,job from emp; #distinct必须放在所有字段之前,表示后面字段不全部一致时的结果