Oracle-上
1. 基本使用
-
创建表空间,并给表空间指定用户
--创建表空间 create tablespace tbspace --指定表空间名称 datafile 'c:\tbspace.dbf' --指定表空间存储文件 size 100m --指定初始大小 autoextend on --指定自增长 next 10m; --指定每次自增长大小 --创建用户 create user tobing --指定用户名 identified by tobing --指定用户密码 default tablespace tbspace; --指定使用的表 --给用户授权 --Oracle中常用角色 connect --连接角色(基本角色) resource--开发者角色 dba --超级管理员角色 grant dba to tobing; -- 给tobing授权超级管理员角色
-
表结构的操作
--创建表 create table person( -- 创建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,'tobing'); commit; ---修改一条记录 update person set pname ='扶上梁' where pid = 1; commit; --三个删除 ---删除表中所有记录 delete from person; ---删除表结构 drop table person; ---先删除表,再创建表效果等于删除表中所有记录 truncate table person;
- 在对表中数据进行增删改操作的时候,需要手动将事务提交,否则可能会导致操作的丢失。Oracle的默认事务隔离级别和MySQL是不一样的。
- delete和truncate两种方式删除表中所有数据底层的实现原理是不一样的。delete是直接删除表中的记录,而truncate是先删除整个表结构,然后再创建一样的表结构。
- 在数据量比较大,特别是含有索引的表中,使用truncate方式效率远大于delete方式。
-
序列
序列不真的属于任何一张表,但是可以逻辑和表做绑点。
默认从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用户
scott是Oracle提供给初学者研究学习的一张表。
--解锁scott表 alter user scott account unlock; --解锁scoot密码【也可以用于重置密码】 alter user scott identified by tiger;
2. 单行函数
单行函数作用于一行,放回一个值
-
字符函数
-- upper():将字符转换为大写 select upper("tobing") from dual; -- 输出TOBING -- lowwer():将字符转换为小写 select lowwer("TOBING") from dual; -- 输出tobing
-
数值函数
-- round(number,n) 将数值保留n位(负数表示小数点前面)四舍五入 select round(12,56,0) from dual; -- 输出13 -- trunc(number,n) 直接将数值截断,保留n位 select trunc(12,56,0) from dual; -- 输出12 -- mod(number,n) 将number对n取余数 select mod(10,3) from dual; -- 输出1
-
日期函数
-- 查询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 sysdate-e.hiredate/7 from emp e;
-
转换函数
-- to_char(date,regex) 日期转字符串 -- fm:表示不用0补位 -- mi:分钟,由于不区分大小写,需要使用mi与mm区别 -- hh24:使用24小时方式显示 select to_char(sysdate,"fm yyyy-mm-dd hh24:mi:ss" ) from dual; -- to_date(char,regex) 字符串转日期 select to_date("2018-12-12 12:12:12","fm yyyy-mm-dd hh24:mi:ss") from dual;
-
通用函数
-- 算出emp表中所有员工的年薪(月新*12+奖金) -- 存在问题:有点员工奖金为null,直接跟null运算结果是null -- 如何解决:使用nvl(value,number),如果value为null,使number替换 select e.sal*12+nvl(e.comm,0) from emp e;
3. 条件表达式
-
通用用法
适用于Oracle和mysql
-- 需求1:给emp表中的员工名称起中文名 select e.ename, case e.ename when 'SMITH' then '张三丰' when 'ALLEN' then '张无忌' when 'WARD' then '张翠山' else '无名' -- 可以省略 end -- 结束标记,不要忘记 from emp e; -- 需求2:判断emp表中员工,>3000为高收入,1500-3000为中等收入,其余为低收入 select e.sal case then e.sal>3000 then '高收入' then e.sal>1500 then '中等收入' else '低收入' end from emp e;
-
Oracle特有
oracle中除了取别名,动用单引号
-- 需求1:给emp表中的员工名称起中文名 select e.ename decode(e.name,'SMITH','张三丰','WARD','张无忌','无名') 中文名 from emp e;
4. 多行函数
多行函数【聚合函数】:作用于多行,返回一个值。
select count(1) from emp; -- 查询总数量
select max(sal) from emp; -- 查询最高工资
select min(sal) from emp; -- 查询最低工作
select sum(sal) from emp; -- 查询工作总和
select avg(sal) from emp; -- 查询平均工资
5. 分组查询
分组查询常常可以结合聚合函数进行查询
-
查询出每个部门的平均工资
select e.deptno, avg(e.sal) from emp e group by e.deptno;
分组查询中,出现在group by后面的原始列才可以出现在select后面。
分组查询中,没有出现在group by后面的列,想在select之后出现,只能加上聚合函数。
select e.deptno, e.sal from emp e group by e.deptno;
以上写法是错误的。因为分组后的结果是多行的,无法判断是哪一行的sal。
-
查询出平均工资高于2000的部门信息
select e.deptno,avg(e.sal) from emp e group by e.deptno having avg(e.sal)>2000;
having用于过滤分组之后的数据
-
查询出每个部门工资高于800的平均工资
select e.deptno,avg(e.sal) from emp e where e.sal>800 group by e.deptno
where用于过滤分组之前的数据
-
查询出每个部门员工工资高于800的平均工资且平均工资高于2000的部门
select e.deptno,e.sal from emp e where e.sal>800 group by e.deptno having avg(e.sal)>200
where 必须位于group by之前,having 必须位于group by之后
6. 多表查询
0. 笛卡尔积
直接的无条件多表查询返回的结果是笛卡尔积(表间数据类的排列组合),笛卡尔积的很多内容往往是无效的,需要通过某些条件进行过滤。
1. 内连接和等值连接
-
等值连接
-- 查询出emp表中员工所有信息(员工的部门信息在dept表中) select * from emp e,dept d where e.deptno = d.deptno
使用where作为条件判断关键词。【推荐使用】
-
内连接
-- 查询出emp表中员工所有信息(员工的部门信息在dept表中) select * from emp e inner join dept d on e.deptno = d.deptno
早期的写法,推荐使用等值连接的方式将其替换
2. 外连接
外连接可以解决多表中,要查询某一个表中的所有数据,而该数据与其他表无映射关系时的问题。
-
通用外连接
外连接有左外连接和右外连接之分。左右只是相对的概念。
-- 查询出所有部门信息,以及部门下的员工【有的部门可能没有对应员工】 select * from emp e right join dept d on e.deptno = d.dpetno; -- 查询出所有员工信息,已经员工对应的部门【员工可能没有对应部门】 select * from emp e left join dept d on e.deptno = d.deptno;
-
Oracle特有外连接
-- 查询出所有部门信息,以及部门下的员工【有的部门可能没有对应员工】 select * from emp e,dept d where e.deptno(+) = d.deptno -- 查询出所有员工信息,已经员工对应的部门【员工可能没有对应部门】 select * from emp e,dept d where e.deptno = d.deptno(+)
注意**(+)**的位置
3. 自连接
自连接:就是站在不同角度把一张表看成多张表
-- 查询出员工姓名,员工领导姓名【员工,员工领导在同一张表】
select e1.ename,e2.ename
from emp e1,emp e2
where e1.mgr = e2.empno
-- 查询出员工姓名,员工部门名称,员工领导姓名,员工领导部门名称
select e1.enam, d1.dname,e2.ename,d2 dname
from emp e1,dept d1,emp e2,dept d2
where e1.mgr = e2.empno
and e1.deptno = d1.deptno
and e2.deptno = d2.deptno;
3. 子查询
-
子查询返回一个值
--查询与scott一样工资的员工的信息 select * from emp where name in( select sal from emp where name ='scott' );
为避免scott不唯一导致问题,使用in而不是=
-
子查询返回一个集合
--查询出工资和10号部门任意员工一样的员工信息 select * from emp where sal in( select sal from emp where depton = 10 );
-
子查询返回一张表
--查询出每个部门的最低工资,和最低工资员工姓名,和该员工所在部门名称 --1. 查询出每个部门及其对应的最低工资 select e.deptno,min(e.sal) from emp e group by e.deptno --2. 三表联查,得到最后结果 select t.deptno, t.msal e.ename, dept.dname from (select deptno,min(e.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;
4. 分页查询
-
行号rownum
当我们使用select操作的时候,每查询出一行记录,就会在该行上加上一个行号。
行号从1开始,依次递增,不能跳着走。
--以下sql语句是先查询出来数据,再排序 --由于查询数据时rownum已经存在,再排序会导致rownum是乱序的 select rownum,e.* from emp e order by sal desc; --解决办法:嵌套查询 select rownum,e.* from (select * from emp order by sal desc) e;
-
分页查询
--emp表工资倒序排列后,每页显示5条,查询第2页 select * from( select rownum rn,tt.* from( select * from emp order by sal desc )tt where rownum<11 )where rn>5
rownum行号不能写上大于一个正数