1 单条件查询
select ……
from ……
where 条件
-- = > >= < <= != <>
-- 单引号用于数据表示字符串
-- 双引号用于数据库对象名,列名,表名……
-- 数据区分大小写,数据库不区分大小写
2 多条件查询
--与and 或or 非not
select * from emp where deptno in (10,20);
-- in表示deptno取值在10和20其中一个,not in表示不在集合中;如果结果中含有NULL,不能使用not in,但可以使用in。
select * from emp where sal between 1000 and 2000;
-- between and 是包括边界值
--SQL在解析where时,是从右向左解析的;使用and时应将易假的值放在右侧,or时应将易真的值放在右侧;
3 模糊查询
select ...
from ...
where 列名 like '模式字符串'
-- % 表示若干个字符
-- _ 表示一个字符
select * from emp where ename like '%\_%' escape '\'; -- 以反斜杠作为转义字符
4 排序
select ...
from ...
where 条件
order by 列名1 asc|desc,列名2....
-- asc表示升序(不填则默认是升序)
-- desc表示降序
select * from emp order by 2; --按照输出结果集的第2列来进行排序
select ename 名字,sal 月薪 from emp order by 月薪
5 单行函数
单行函数:只对一行数据进行计算,产生一个结果。函数可以没有参数,但必须要有返回值。
5.1 字符函数
lower --将字符串转小写
upper --将字符串转大写
initcap --将首字母变成大写
concat --字符串连接
select concat(concat('hello','world'),'123') --支持两个参数
select 'hello'||'world'||123 --字符串拼凑
substr --提取子串
select substr('helloworld',3), --从第三个字符开始一直取子串到最后
select substr('helloworld',3,5) --从第三个字符开始取5个字符
instr --查找某个字符串是否另一个字符串的子串
select instr('hello world', 'llo') from dual --结果为3
lpad,rpad --左右填充
select lpad('hello','10','#') from dual;
--填充字符到达指定的长度
--往hello左边填充字符#,使字符串长度到达10
--如果字符串长度大于指定长度,就截取
trim --裁剪
select trim ( ' Hello world ') from dual; --默认裁剪空白字符
select trim ( 'H' from 'Hello worldHHHH') from dual; --左右裁剪H字符
replace --替换子串
select replace('Hello world','l','#') from dual;
length --长度,length(last_name)
5.2 数值函数
round --四舍五入
trunc --截取
ceil --向上取整
floor --向下取整
mod --取模 %
select round(45.926,2) from dual; --45.93
5.3 转换函数
to_char --数字转字符串
select ename ,sal ,to_char(sal,'L9999') from emp;
--L 表示本地货币字符
--9 表示一位数字
to_number --字符串转数字
to_date --字符串转日期,以什么格式转化为字符串,就以什么格式转回date类型
select to_char(sysdate,'dd-mm-yyyy dy hh24:mi:ss') from dual;
select to_date('30-05-2019 星期四 17:01:57','dd-mm-yyyy dy hh24:mi:ss') from dual;
5.4 日期函数
now --mysql中获取系统当前时间
sysdate --oracle中获取系统当前时间
select sysdate from dual ;
select sysdate-1 昨天,sysdate 今天 ,sysdate +1 明天 from dual;
months_between --计算两个时间相差的月份,自动计算大小月
select
(sysdate-hiredate)/30 月,
months_between(sysdate,hiredate)
from emp;
add_months --计算明年今日
select add_months(sysdate,12) from dual;
last_day --获取当前月份最后一天
select last_day(sysdate) from dual
next_day --获取下一个星期几是哪一天
select next_day(sysdate,'星期二') from dual
5.5 通用函数
nvl(exp,val) --如果exp为null,那么返回val,否则返回exp的值
nvl2(exp,val1,val2) --如果exp为null,返回val2 ,否则返回val1
select distinct deptno from emp; --去重
--distinct作用于后面所有列;
dual --虚表
select 3+2 from dual;
nullif(exp1,exp2) --当exp1=exp2时返回null,不等时返回exp1值
coalesce(a,b,c...,n) --从左到右找参数中第一个不为空的值
5.6 条件语句
case when then else end
--总裁决定给大家涨工资,主管涨1000,销售涨500,其他涨200
select ename,job,sal 涨前工资,
case job
when 'MANAGER' then sal+1000
when 'SALESMAN' then sal+500
else sal + 200
end 涨后工资
from emp
decode(expr,val1,val2,val3,val4,....,defaultValue)
--如果expr 值是val1 那么返回val2,如果是val3,那么返回val4,以此类推,都不是,那么就返回最后一个defaultValue的值
6 多行函数
6.1 统计函数
sum --总和
select sum(sal) from emp
count 计数函数,某行数据有值,那么就计数+1
--*表示该行数据不管哪列有数据都会统计
--最终只统计comm该列不为空的数量
--null不会参与统计函数的计算
select count(comm) 员工数量 from emp;
max/min --求最高和最低
select max(sal) 最高工资,min(sal) 最低工资 from emp
avg --求平均
select avg(sal) from emp
6.2 分组统计
select ...
from ...
where cond
group by 列名1[,列名2,....]
having cond
order by ...
--按照group by后给定的表达式,将from后面的table进行分组。
--在select列表中,所有没有包含在组函数中的列,都必须在group by的后面出现。
--统计函数,在没有分组的情况,统计的是全表,在有分组的情况下,统计的是对应的分组
--where里边不允许使用分组函数,改用having
--having里边能够使用统计函数的结果进行比较
--能使用where不要使用having
group_concat(xxx)
select gender,group_concat(name, age, id) from students where gender=1 group by gender;
where 和 having区别
从表集合中查询数据,首先要满足where条件 cond1 ,满足的数据就筛选出来,成为结果集1
从结果集1中使用group by 指定的列进行分组,计算统计函数的值,成为结果集2
结果集2继续进行筛选,筛选条件就是having的条件cond2 ,最终通过筛选之后,成为结果集3
结果集3经过order by 的条件进行排序 ,得到最终的结果集
数据越早能够得到筛选越好,对于后面计算量控制有优势
7 字符格式
9
数字
0
零
$
美元符
L
本地货币符号
.
小数点
,
千位符
8 分页
--limit start, count
-- 限制查询出来的数据个数
select * from students where gender=1 limit 2;
-- 查询前5个数据
select * from students limit 0, 5;