oracle 字符串截取函数_自学Oracle数据库day01

-- 创建表空间

create tablespace liqinggang --表空间名字

datafile 'F:oracleoradataorclliqinggang.dbf' --表空间位置

size 100m --创建表空间初始大小

autoextend on --自动扩展大小

next 10m;

--删除表空间

drop tablespace liqinggang

--创建用户

create user lqg --登录的用户名

identified by lqg --登录的密码

default tablespace liqinggang --该用户出生的表空间

--给用户授权

--oracle数据库中常用角色

connect --连接角色，基本角色

resource --开发者角色

dba --超级管理员角色

--给lqg用户授予dba角色

grant dba to lqg

--切换到lqg用户下

---创建一个person表

create table person(

pid number(20),

pname varchar2(10)

);

---修改表结构

---添加一列

alter table person add (gender number(1));

---修改列类型

alter table person modify gender char(1);

---修改列名称

alter table person rename column gender to sex;

---删除一列

alter table person drop column sex;

---查询表中记录

select * from person;

----添加一条记录

insert into person (pid, pname) values (1, '小明');

commit;

----修改一条记录

update person set pname = '小马' where pid = 1;

commit;

----三个删除

--删除表中全部记录

delete from person;

--删除表结构

drop table person;

--先删除表，再次创建表。效果等同于删除表中全部记录。

--在数据量大的情况下，尤其在表中带有索引的情况下，该操作效率高。

--索引可以提供查询效率，但是会影响增删改效率。

truncate table person;

----序列不真的属于任何一张表，但是可以逻辑和表做绑定。

----序列：默认从1开始，依次递增，主要用来给主键赋值使用。

----dual：虚表，只是为了补全语法，没有任何意义。

create sequence s_person;

select s_person.nextval from dual;

----添加一条记录

insert into person (pid, pname) values (s_person.nextval, '小明');

commit;

select * from person;

----scott用户，密码tiger。

--解锁scott用户

alter user scott account unlock;

--解锁scott用户的密码【此句也可以用来重置密码】

alter user scott identified by tiger;

--切换到scott用户下

--单行函数：作用于一行，返回一个值。

---字符函数

select upper('yes') from dual;--YES

select lower('YES') from dual;--yes

----数值函数

select round(56.16, -2) from dual;---四舍五入，后面的参数表示保留的位数

select trunc(56.16, -1) from dual;---直接截取，不在看后面位数的数字是否大于5.

select mod(10, 3) from dual;---求余数

----日期函数

----查询出emp表中所有员工入职距离现在几天。

select sysdate-e.hiredate from emp e;

----算出明天此刻

select sysdate+1 from dual;

----查询出emp表中所有员工入职距离现在几月。

select months_between(sysdate,e.hiredate) from emp e;

----查询出emp表中所有员工入职距离现在几年。

select months_between(sysdate,e.hiredate)/12 from emp e;

----查询出emp表中所有员工入职距离现在几周。

select round((sysdate-e.hiredate)/7) from emp e;

----转换函数

---日期转字符串

select to_char(sysdate, 'fm yyyy-mm-dd hh24:mi:ss') from dual;

---字符串转日期

select to_date('2018-6-7 16:39:50', 'fm yyyy-mm-dd hh24:mi:ss') from dual;

----通用函数

---算出emp表中所有员工的年薪

----奖金里面有null值，如果null值和任意数字做算术运算，结果都是null。

select e.sal*12+nvl(e.comm, 0) from emp e;

---条件表达式

---条件表达式的通用写法，mysql和oracle通用

---给emp表中员工起中文名

select e.ename,

case e.ename

when 'SMITH' then '曹贼'

when 'ALLEN' then '大耳贼'

when 'WARD' then '诸葛小儿'

--else '无名'

end

from emp e;

---判断emp表中员工工资，如果高于3000显示高收入，如果高于1500低于3000显示中等收入，

-----其余显示低收入

select e.sal,

case

when e.sal>3000 then '高收入'

when e.sal>1500 then '中等收入'

else '低收入'

end

from emp e;

----oracle中除了起别名，都用单引号。

----oracle专用条件表达式

select e.ename,

decode(e.ename,

'SMITH', '曹贼',

'ALLEN', '大耳贼',

'WARD', '诸葛小儿',

'无名') "中文名"

from emp e;

--多行函数【聚合函数】：作用于多行，返回一个值。

select count(1) from emp;---查询总数量

select sum(sal) from emp;---工资总和

select max(sal) from emp;---最大工资

select min(sal) from emp;---最低工资

select avg(sal) from emp;---平均工资

---分组查询

---查询出每个部门的平均工资

---分组查询中，出现在group by后面的原始列，才能出现在select后面

---没有出现在group by后面的列，想在select后面，必须加上聚合函数。

---聚合函数有一个特性，可以把多行记录变成一个值。

select e.deptno, avg(e.sal)--, e.ename

from emp e

group by e.deptno;

---查询出平均工资高于2000的部门信息

select e.deptno, avg(e.sal) asal

from emp e

group by e.deptno

having avg(e.sal)>2000;

---所有条件都不能使用别名来判断。

--比如下面的条件语句也不能使用别名当条件

select ename, sal s from emp where sal>1500;

---查询出每个部门工资高于800的员工的平均工资

select e.deptno, avg(e.sal) asal

from emp e

where e.sal>800

group by e.deptno;

----where是过滤分组前的数据，having是过滤分组后的数据。

---表现形式：where必须在group by之前，having是在group by之后。

---查询出每个部门工资高于800的员工的平均工资

---然后再查询出平均工资高于2000的部门

select e.deptno, avg(e.sal) asal

from emp e

where e.sal>800

group by e.deptno

having avg(e.sal)>2000;

---多表查询中的一些概念

---笛卡尔积

select *

from emp e, dept d;

---等值连接

select *

from emp e, dept d

where e.deptno=d.deptno;

---内连接

select *

from emp e inner join dept d

on e.deptno = d.deptno;

---查询出所有部门，以及部门下的员工信息。【外连接】

select *

from emp e right join dept d

on e.deptno=d.deptno;

select * from emp;

---查询所有员工信息，以及员工所属部门

select e1.ename,e2.ename,e2.ename,d2.dname

from emp e1,emp e2,dept d1,dept d2

where e1.mgr = e2.empno

and e1.deptno=d1.deptno

and e2.deptno=d2.deptno;

--子查询

--子查询返回一个值

--查询出工资和scott一样的员工信息

select * from emp where sal in

(select sal from emp where ename='SCOTT');

--子查询返回一个集合

--查询出工资和10号部门任意员工一样的员工信息

select * from emp where sal in

(select sal from emp where deptno = 10);

--子查询返回一张表

--查询出每个部门最低工资，和最低工资员工姓名，和该员工所在部门名称

select deptno,min(sal) msal

from emp

group by deptno;

--2.三表联查，得到最终结果

select t.deptno,t.msal,e.ename,d.dname

from (select deptno,min(sal) msal

from emp

group by deptno) t,emp e,dept d

where t.deptno =e.deptno

and t.msal = e.sal

and e.deptno = d.deptno;

--oracle分页

--rownum行号：当我们做select操作的时候，

--每查询出一行记录，就会在该行号上加上一个行号，

--行号从1开始，依次递增，不能跳着走

select rownum,e.* from emp e order by e.sal desc;

--如果涉及到排序，但是还是要使用rownum的话，我们可以再次嵌套查询。

select rownum,t.* from(5

select rownum,e.* from emp e order by e.sal desc) t;

--emp表工资倒序排列后，每条五个记录，查询第二页。

select * from(

select rownum rn,e.* from(

select * from emp order by sal desc

)e where rownum<11

)where rn>5;