关系型数据库基础操作（二）
模糊查询（加通配符 % 或者 _）
% 表示省略一个或多个，
_ 表示占有一个字符的位置
select * from 表名 where 字段名 like '%模糊部分字符%';
例如：
select * from orange where name like '%l%';
select * from orange where name like 'l%';
select * from orange where name like '%l';
select * from emp where ename like '%A%';
select * from emp where ename like '_A%';
别名
别名可以加 as 也可以不加
别名代指其前面的字段名或者表名
例如：
select ename,(sal+1000)*12+1000 as sals from emp;
select ename,(sal+1000)*12+1000 sals from emp;
select e.ename,(sal+1000)*12+1000 sals from emp e;
等值连接（从两个表中查询）
select ename,dname from emp,dept where emp.deptno=dept.deptno;
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
如果是 ename 还会在表 dept 中查询该字段
如果是 e.ename 只会在 emp 表中查询
自连接（将一张表作为多张表查询）（左右都不可以为空）
select e.ename,m.ename from emp e,emp m where e.mgr=m.empno;
select e.ename,m.ename from emp e join emp m on e.mgr=m.empno;
外连接
左外连接 (以左边为主体，右边可以为空)
A left outer join B on...
select e.ename,m.ename from emp e left outer join emp m on e.mgr=m.empno;
右外连接 (以右边为主体，左边可以为空)
A right outer join B on...
select e.ename,m.ename from emp e right outer join emp m on e.mgr=m.empno;
排序
默认升序排列（asc）
select * from emp order by empno;
升序 (order by birthday asc;)
select * from student order by birthday ;
select * from emp order by empno desc;
降序
select * from student order by birthday desc;
select * from emp order by empno desc,sal asc;
按 birthday 降序 按 xh 升序 (asc/ 默认)
select * from student order by birthday desc,xh asc;
常见函数
分组函数 返回值是多条记录
- group by
- sum
- avg
select sum(totalUsed) from t_plane;
单行函数
字符函数
concat 连接 ||
显示 dname 和 loc 中间用 - 分隔
select deptno,dname||'----'||loc from dept;
select empno ,job||'---'||sal from emp;
dual 哑元表 没有表需要查询的时候 可以用它
select 'Hello World' from dual;
select 'Hello' from dual;
select 1+1 from dual;
select 1+5.999 from dual;
查询系统时间
select sysdate from dual;
initcap 首字母大写
select ename,initcap(ename) from emp;
lower 转换为小写字符
select ename,lower(ename) from emp;
upper 转换为大写
update dept set loc=lower(loc);
update dept set loc=upper(loc);
LTRIM 去除左边的空格
RTRIM 去除右边的空格
ALLTRIM 去除两边的空格
replace 替换
translate 转换
select ename,replace(ename,'S','s') from emp;
用 's' 去替换 ename 中的 'S'
select ename,translate(ename,'S','a') from emp;
ASCII 求 ASC 码
chr asc 码变字符
select ascii('A') from dual;
select chr(97) from dual;
select 'Hello'||chr(9)||'World' from dual;
'\t' ascii 码是 9
'\n' ascii 码是 10
select 'Hello'||'\t'||'World' from dual;
substr 字符截取函数
从第 1 个位置开始 显示 3 个字符
select ename,substr(ename,1,3) from emp;
从第 4 个位置开始显示后面所有的字符
select ename,substr(ename,4) from emp;
instr 测试字符串出现的位置
'S' 第 1 次出现的位置
select ename,instr(ename,'S') from emp;
从第 1 个位置开始 测试 'T' 第 2 次出现的位置
select ename,instr(ename,'T',1,2) from emp;
length 字符串的长度
select ename,length(ename) from emp;
日期和 时间函数
sysdate 系统时间
select sysdate from dual;
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
select to_char(sysdate,'DDD') from dual;
select to_char(sysdate,'D') from dual;
select to_char(sysdate,'DAY') from dual;
select to_char(sysdate,'yyyy-mm-dd') from dual;
select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;
select '''' from dual;
从今天零点以后的秒数
select to_char(sysdate,'SSSSS') from dual;
ADD_MONTHS 添加月份 得到一个新的日期
select add_months(sysdate,1) from dual;
select add_months(sysdate,-1) from dual;
select trunc(sysdate)-to_date('20050101','yyyymmdd') from dual;
一年以后的今天
select add_months(sysdate,12) from dual;
一年以前的今天
select add_months(sysdate,-12) from dual;
trunc(sysdate) 截取年月日
select sysdate+2 from dual;
数字代表的是天数
两个日期之间的差值代表天数
last_day 某月的最后一天
select last_day(sysdate) from dual;
本月第 3 天的日期
select add_months(last_day(sysdate)+3,-1) from dual;
months_between 两个日期之间的月数
select months_between(sysdate,'2005-02-01') from dual;
sysdate - '2005-02-01'
select months_between('2005-02-01',sysdate) from dual;
oracle 转换函数
to_char 把日期或数字类型变为字符串
select to_char(sysdate,'hh24:mi:ss') from dual;
select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;
mysql 转换函数
date_format 把日期或数字类型变为字符串
select date_format(start_time, '%Y%m%d%H%i%s') from write_log where 1=1 and id=1;
L 本地货币
select sal,to_char(sal,'L9,999') from emp;
to_number 把字符串变成数字
select to_number('19990801') from dual;
to_date 把字符串变成日期
select to_date('19800101','yyyymmdd') from dual;
select to_char(to_date('19800101','yyyymmdd'),'yyyy"年"mm"月"dd"日"') from dual;
数学函数
ceil(x) 不小于 x 的最小整数
ceil（12.4) 13
ceil(-12.4) -12
floor(x) 不大于 x 的最大整数
floor(12.5) 12
floor(-12.4) -13
round(x) 四舍五入
round(12.5) 13
round(12.456,2) 12.46
trunc(x) 舍去尾数
trunc(12.5) 12
trunc(12.456,2) 12.45
舍去日期的小时部分
select to_char(trunc(sysdate),'yyyymmdd hh24:mi:ss') from dual;
mod(x,n) x 除以 n 以后的余数
mod(5,2) 1
mod(4,2) 0
power(x,y) x 的 y 次方
select power(3,3) from dual;
混合函数
求最大值
select greatest(100,90,80,101,01,19) from dual;
求最小值
select least(100,0,-9,10) from dual;
空值转换函数 nvl(comm,0) 字段为空值 那么就返回 0 否则返回本身
comm 类型和 值的类型是 一致的
select comm,nvl(comm,0) from emp;
复杂的函数
decode 选择结构 (if ... elseif .... elesif ... else 结构)
要求:
sal=800 显示低工资
sal=3000 正常工资
sal=5000 高工资
只能做等值比较
select sal,decode(sal,800,'低工资',3000,'正常工资',5000,'高工资','没判断')
from emp;
表示如下的 if else 结构
if sal=800 then
'低工资'
else if sal =3000 then
'正常工资'
else if sal = 5000 then
'高工资'
else
'没判断'
end if
判断正负
sign(x) x是正 1
x是负 -1
x是0 0
select sign(-5) from dual;
做大于小于的比较
sal<1000 显示低工资 sal-1000<0 sign(sal-1000) = -1
1000<=sal<=3000 正常工资
3000<sal<=5000 高工资
select sal,decode(
sign(sal-1000),-1,'低工资',
decode(sign(sal-3000),-1,'正常工资',
0,'正常工资',1,
decode(sign(sal-5000),-1,'高工资','高工资')
)) as 工资状态 from emp;
一般的情况 decode(x,y1,z1,y2,z2,z3)
if x= y1 then
z1
else if x = y2 then
z2
else
z3
end if