关系型数据库基础操作(二)

模糊查询(加通配符 % 或者 _)

% 表示省略一个或多个,
_ 表示占有一个字符的位置

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  

上一篇 关系型数据库基础操作(一)
下一篇 关系型数据库基础操作(三)