Oracle-上

tobing
理论学习

发布于：Jan 19, 2020
次浏览

Oracle学习第一弹。

Oracle-上

1. 基本使用

  1. 创建表空间，并给表空间指定用户

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    --创建表空间
    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授权超级管理员角色

  2. 表结构的操作

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    --创建表
    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;				--删除一列

  3. 表数据的操作

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    --查询表中记录
    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;
    1. 在对表中数据进行增删改操作的时候，需要手动将事务提交，否则可能会导致操作的丢失。Oracle的默认事务隔离级别和MySQL是不一样的。
    2. delete和truncate两种方式删除表中所有数据底层的实现原理是不一样的。delete是直接删除表中的记录，而truncate是先删除整个表结构，然后再创建一样的表结构。
    3. 在数据量比较大，特别是含有索引的表中，使用truncate方式效率远大于delete方式。

  4. 序列

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

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

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

    1
    2
    3
    4
    5
    6
    7
    create sequence s_person;
    select s_person.nextval from dual;
    --序列使用
    insert into person(pid,pname) values(s_person.nextval,'小明');
    commit;
    --查询效果
    select * from person;

  5. scott用户

    scott是Oracle提供给初学者研究学习的一张表。

    1
    2
    3
    4
    --解锁scott表
    alter user scott account unlock;
    --解锁scoot密码【也可以用于重置密码】
    alter user scott identified by tiger;

2. 单行函数

单行函数作用于一行，放回一个值

  1. 字符函数

    1
    2
    3
    4
    -- upper()：将字符转换为大写
    select upper("tobing") from dual;	-- 输出TOBING
    -- lowwer()：将字符转换为小写
    select lowwer("TOBING") from dual;	-- 输出tobing

  2. 数值函数

    1
    2
    3
    4
    5
    6
    -- 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

  3. 日期函数

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    -- 查询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;

  4. 转换函数

    1
    2
    3
    4
    5
    6
    7
    -- 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;

  5. 通用函数

    1
    2
    3
    4
    -- 算出emp表中所有员工的年薪（月新*12+奖金）
    -- 存在问题：有点员工奖金为null，直接跟null运算结果是null
    -- 如何解决：使用nvl(value,number),如果value为null，使number替换
    select e.sal*12+nvl(e.comm,0) from emp e;

3. 条件表达式

  1. 通用用法

    适用于Oracle和mysql

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    -- 需求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;

  2. Oracle特有

    oracle中除了取别名，动用单引号

    1
    2
    3
    4
    -- 需求1：给emp表中的员工名称起中文名
    select e.ename
    	decode(e.name,'SMITH','张三丰','WARD','张无忌','无名') 中文名
    from emp e;

4. 多行函数

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

1
2
3
4
5
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. 分组查询

分组查询常常可以结合聚合函数进行查询

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

    1
    2
    3
    select e.deptno, avg(e.sal) 
    from emp e
    group by e.deptno;

    分组查询中，出现在group by后面的原始列才可以出现在select后面。

    分组查询中，没有出现在group by后面的列，想在select之后出现，只能加上聚合函数。

    1
    2
    3
    select e.deptno, e.sal
    from emp e
    group by e.deptno;

    以上写法是错误的。因为分组后的结果是多行的，无法判断是哪一行的sal。

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

    1
    2
    3
    4
    select e.deptno,avg(e.sal)
    from emp e
    group by e.deptno
    having avg(e.sal)>2000;

    having用于过滤分组之后的数据

  3. 查询出每个部门工资高于800的平均工资

    1
    2
    3
    4
    select e.deptno,avg(e.sal)
    from emp e
    where e.sal>800
    group by e.deptno

    where用于过滤分组之前的数据

  4. 查询出每个部门员工工资高于800的平均工资且平均工资高于2000的部门

    1
    2
    3
    4
    5
    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. 内连接和等值连接

  1. 等值连接

    1
    2
    3
    4
    -- 查询出emp表中员工所有信息（员工的部门信息在dept表中）
    select *
    from emp e,dept d
    where e.deptno = d.deptno

    使用where作为条件判断关键词。【推荐使用】

  2. 内连接

    1
    2
    3
    4
    -- 查询出emp表中员工所有信息（员工的部门信息在dept表中）
    select *
    from emp e inner join dept d
    on e.deptno = d.deptno

    早期的写法，推荐使用等值连接的方式将其替换

2. 外连接

外连接可以解决多表中，要查询某一个表中的所有数据，而该数据与其他表无映射关系时的问题。

  1. 通用外连接

    外连接有左外连接和右外连接之分。左右只是相对的概念。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    -- 查询出所有部门信息，以及部门下的员工【有的部门可能没有对应员工】
    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;

  2. Oracle特有外连接

    1
    2
    3
    4
    5
    6
    7
    8
    9
    -- 查询出所有部门信息，以及部门下的员工【有的部门可能没有对应员工】
    select *
    from emp e,dept d
    where e.deptno(+) = d.deptno

    -- 查询出所有员工信息，已经员工对应的部门【员工可能没有对应部门】
    select *
    from emp e,dept d
    where e.deptno = d.deptno(+)

    注意（+）的位置

3. 自连接

自连接：就是站在不同角度把一张表看成多张表

1
2
3
4
5
6
7
8
9
10
-- 查询出员工姓名，员工领导姓名【员工，员工领导在同一张表】
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. 子查询

  1. 子查询返回一个值

    1
    2
    3
    4
    --查询与scott一样工资的员工的信息
    select * from emp where name in(
        select sal from emp where name ='scott'
    );

    为避免scott不唯一导致问题，使用in而不是=

  2. 子查询返回一个集合

    1
    2
    3
    4
    --查询出工资和10号部门任意员工一样的员工信息
    select * from emp where sal in(
    	select sal from emp where depton = 10	
    );

  3. 子查询返回一张表

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    --查询出每个部门的最低工资，和最低工资员工姓名，和该员工所在部门名称
    --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. 分页查询

  1. 行号rownum

    当我们使用select操作的时候，每查询出一行记录，就会在该行上加上一个行号。

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

    1
    2
    3
    4
    5
    6
    7
    8
    --以下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;

  2. 分页查询

    1
    2
    3
    4
    5
    6
    --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行号不能写上大于一个正数

博客内容遵循 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 协议

本文永久链接是：https://tobing.top/2020/01/19/Oracle-%E4%B8%8A/

更新于：Jan 15, 2021

Oracle

评论