oracle


oracle基础

关系图

image

SQL包含四种程序设计语言类别的语句:

  • 数据定义语言(DDL)

    • 用来定义数据库对象:数据库,表,列等。关键字。也可以定义索引(键),规定表之间的链接,以及施加表间的约束。

      • 数据库

        • CREATE DATABASE – 创建新数据库
        • ALTER DATABASE – 修改数据库
        • CREATE TABLE – 创建新表
        • ALTER TABLE – 变更(改变)数据库表
        • DROP TABLE – 删除表
      • 索引

        • CREATE INDEX – 创建索引(搜索键)
        • DROP INDEX – 删除索引
      • 约束

      • 视图

  • 数据操作语言(DML)

    • 用来对数据库中表的数据进行增删改

      • UPDATE – 更新数据库表中的数据
      • DELETE – 从数据库表中删除数据
      • INSERT INTO – 向数据库表中插入数据
  • 数据查询语句(DQL)

    • 用来查询数据库中表的记录(数据):SELECT
  • 数据控制语言(DCL)

    • 用来定义数据库的访问权限和安全级别,及创建用户

      • GRANT – 授权
      • REVOKE – 撤销授权
      • DENY – 拒绝授权
  • 事务控制语言(TCL)

    • SAVEPOINT – 设置保存点
    • ROLLBACK – 回滚
    • COMMIT – 提交

基础知识

  • 实体:Java类(人、猫、企鹅)
  • 记录:Java对象,行。
  • 字段:Java属性,列。
  • 表:所有数据的集合,称之为表。
  • 表、视图、触发器 统称数据对象

数据类型

  • 字符型

    • char

      • 定长,最大2000字符

        • char(10) 小韩”前四个字符放“小韩”后添6个空格补全
    • varchar2(20)

      • 变长,最大4000字符

        • varchar2(10) 小寒oracle分配四个字符这样可以节省空间
    • clob(character large object)

      • 字符型大对象,最大4G
  • 数字型

    • number

      • 范围 -10^38 – 10^38
      • 可以表示整数,也可以表示小数
    • number(5,2)

      • 表示一个小数有5位有效数,2位小数
      • 范围 -999.99 999.99
    • number(5)

      • 表示一个五位整数
      • 范围-99999 99999
  • 日期

    • date

      • 包含年月日和时分秒
    • timestamp

      • 这是oracle9i对date数据类型的扩展
    • oracle默认日期格式‘DD-MON-YY’

      • dd日 mon月 yy 2位的年
    • 改日期默认格式

      • alter session set nls_date_format = ‘yyyy-mm-dd’;
  • 图片

    • blob

      • 二进制数据 可以存放图片、声音

        • 4G

运算符

  • 关系运算符 > < >= <= = !=(或<>)

    • 如果是null,则使用is null或is not null.
  • 逻辑运算符 or and not

    • select * from emp where empno=7788 or empno=7369
    • select * from emp where deptno=20 and job =’CLERK’ ;
    • select * from emp where not ( empno=7788 or empno=7369 )
  • 算数运算

    • 四则运算

    • null参与运算时,结果永远为null–>解决nvl/nvl2

      • nvl(a,b):如果a为null,则值为b –>if
      • nvl2(a,b,c):如果a是null,则值为c,否则为b; –if..else..
  • 连接符 concat、||

    • select concat(‘hello’,’123′) from dual ;
    • select ‘hello’||’123’ from dual ;

函数

  • 单行函数:

    • ① 字符函数

      • a.lower /upper/initcap

        • select ‘Hello World’ 原始, lower(‘Hello World’) 小写,upper(‘Hello World’) 大写, initcap(‘hello world’) 首字母大写 from dual ;
      • b.substr(str,begin):将str从begin开始截取

        • select substr(‘Hello world’,3) from dual ;

          • –Oracle从1开始数
      • c.substr(str,begin,len) :将str从begin开始截取,截取的长度是len

        • select substr(‘Hello world’,3,3) from dual ;
      • d.length/lengthb

        • 获取字符个数、字节数

          • length :求取字符个数
          • lengthb:获取字节数,一个中文占2/3个字节数。
            – utf-8:一个中文占3个字节数。
            – gbk:一个中文占2个字节数。
          • 查看oracle字符集: select * from nls_database_parameters ;查看NLS_CHARACTERSET字段值
        • select length(‘Hello world’) 字符数, lengthb(‘Hello world’) 字节数 from dual ;

        • select length(‘西安’) 字符数, lengthb(‘西安’) 字节数 from dual ;

      • e.instr(a,b) :在a中,查找b的位置(从1开始数)。如果不存在,返回0 ;

        • select instr(‘hello world’,’ll’) from dual ;–3
        • select instr(‘hello world’,’llw’) from dual ;–0
      • f.lpad左填充、rpad右填充

        • select lpad(‘hello’,10,’‘) 左填充, rpad(‘hello’,10,’‘) from dual ;
      • g.trim()

        • trim():去掉左右两端空格
        • trim(‘X’ from str)去掉str左右两段的X
        • select trim(‘X’ from ‘XXhello worldXXX’) from dual ;
      • h.replace()

        • select replace(‘Hello world’,’l’,’*’) from dual ;

          • 将l变成*
    • ②数值函数

      • a. round(数字,n) :对数字的第n位小数进行四舍五入。

        • select round(67.183,2) 一, round(67.183,1) 二, round(67.183,0) 三,round(67.183,-1) from dual ;
        • 可以是负数
      • b.trunc(数字,n):截取(舍尾)

        • select trunc(67.183,2) 一,trunc(67.183,1) 二, trunc(67.183,0) 三, trunc(67.183,-1) from dual ;
      • c.mod():求余

        • select mod(124,3) from dual ;
    • ③ 日期函数

      • a.关键字sysdate:获取当前时间

        • select sysdate from dual ;
      • b.格式化时间 to_char(时间,格式)

        • select to_date(‘2005-01-01 13:14:20′,’yyyy-MM-dd HH24:mi:ss’) from dual;
      • c.日期 可以+/-数字

        • select (sysdate-1) 昨天,sysdate 今天,sysdate+1 明天 from dual ;
      • d.日期只能-日期,不能+日期

        • select sysdate-hiredate 工作天数 from emp ;
      • e. months_between(日期1,日期2) :相差的月数。日期1- 日期2

        • select months_between(sysdate,hiredate) 工作月数 from emp ;
      • f: add_months(时间,月数):增加月份

        • select add_months(sysdate,3) from dual ;
      • g. last_day(时间) :当月的最后一天

        • select last_day(sysdate) from dual ;
      • i. next_day(时间,’星期N’):下一个星期N是哪一天

        • select next_day(sysdate,’星期五’) from dual ;
      • j. 四舍五入

        • select round(sysdate,’month’) 月,round(sysdate,’year’) from dual ;
      • h.截取(舍尾)

        • select trunc(sysdate,’month’) 月,trunc(sysdate,’year’) from dual ;
      • 日期格式

        • yy:两位数字的年份 2004–04
        • yyyy:四位数字的年份 2004年
        • mm:两位数字的月份 8月–08
        • dd:两位数字的天数 30号–30
        • hh24:二十四小时制8点–20
        • hh12:十二小时制8点–08
        • mi,ss–显示分钟/秒
        • day显示星期几
        • month显示几月
        • year显示年
    • ④通用函数

      • a.nvl/nvl2

      • b.nullif(a,b) :如果a=b,则返回null;否则返回a

        • select nullif(‘hello’,’hello’) from dual ;–null
        • select nullif(‘hello’,’world’) from dual ;–hello
      • c. coalesce :从左往右,寻找第一个不为null的值

        • select coalesce(null,null,’c’) from dual ;
      • d.条件判断函数

        • 1)decode(字段,条件1,表达式1,条件2,表达式2,…,条件n,表达式n,其他)

          • select ename, sal 涨前,job,decode(job,’PRESIDENT’,sal+1000,’MANAGER’,sal+800,sal+400) 涨后 from emp ;
          1. case表达式(case…end)
          • select ename, sal 涨前,job, case job
            when ‘PRESIDENT’ then sal+1000
            when ‘MANAGER’ then sal+800
            else sal+400
            end 涨后
            from emp ;
    • ⑤转换函数

      • 数字/日期<–>字符串
  • 多行函数

    • count(*) :求总数量

      • select deptno from emp ;
      • 去掉重复 select distinct deptno from emp ;
      • select distinct job, deptno from emp order by job ;
      • select count(*) 一, count(empno) 二, count(comm) 三 from emp ;
      • 多行函数(组函数)自动过滤掉null ,如何屏蔽自动滤空null ?
      • select count(*) 一, count(empno) 二, count( nvl(comm,0)) 三 from emp ;
    • sum 求和

    • avg 求平均值

    • max最大值

    • min最小值

    • select sum(sal) 总工资,avg(sal) 平均工资, max(sal) 最高工资,min(sal) 最低工资 from emp ;

基本设置

在Oracle中调用dos命令: host dos命令;

  • 清屏 host cls

连接命令

  • conn[ect]命令

    • conn 用户名/密码@网络服务名 [as sysdba/sysoper]

      • 当用特权用户身份连接时,必须带上 as sysdba 或是 as sysoper
  • disc[onnect]命令

    • 该命令用来断开与当前数据库的连接
  • passw[ord]

    • 该命令用于修改用户的密码.如果要想修改其它用户的密码,需要用sys/system登录
  • show user

    • 显示当前用户名
  • exit

    • 该命令会断开与数据库的连接,同时会退出sql plus

文件操作命令

  • start和@

    • 运行sql脚本

      • sql>@ d:/a.sql
      • sql>START d:/a.sql
  • edit

    • 该命令可以编辑指定的sql脚本

      • sql>edit d:/a.sql
  • spool

    • 该命今可以将sg1*plus屏幕上的内容输出到指定文件中去。

      • sql> spool d:/b.sql
      • sql> spool off

显示和设置环境变量

  • 可以用来控制输出的各种格式,set show

    • 如果希望永久的保存相关的设置,可以去修改g10gin.sql脚本
  • linesize

    • 设置显示行的宽度,默认是80个字符

    • 显示行宽

      • show linesize ;
    • 设置行宽

      • set linesize 160;
  • pagesize

    • 设置每页显示的行数目,默认是14
    • set pagesize 40;–设置每页显示40条数据
  • 设置列宽

    • 设置类型为字符串的列:

      • col 列名 for a长度 ;
      • col ename for a10 ;
    • 设置类型为数字的列:

      • col 列名 for a9个数; (实际长度为9的个数+1)
  • 显示运行时间

    • set timing on;

修改写错的SQL

  • ed

    • 注意 ①ed中没有分号; ②修改完后,/执行
  • c /错误的命令/正确的命令

追加命令 a命令(append):在上一次的sql后追加语句,追加完毕回车 /

  • select * from emp;

  • a order by sal ;

  • 过程:

    • select * from emp order by sal;
  • -注意:a后面有2个空格。

基本设定

  • 序号

    • Oracle/Sql Server :从1开始数
    • MySql:从0开始数
  • 是否区分大小写?

    • 对于关键字(非数据):不区分
    • 对于数据:区分
  • 注释

    • 单行注释 —
    • 多行注释 /* ..*/

伪表

  • dual 存储单行单列,用于学习、测试使用
  • select ‘hello’||’world’ from emp;
  • select ‘hello’||’world’ from dual;

DDL

操作数据库

操作表

  • C

    • 建表

      • create table student
        (sno number(3),
        sname varchar2(20))
  • R

    • 查看当前用户所有

      • select * form tab;
    • 查看表的结构

      • desc 表名
  • U

    • 修改表名

      • rename 表名 to 新表名
    • 增加字段

      • alter table 表名 add 列名 列的类型
    • 修改字段名

      • alter table 表名 rename column 列名 to 新列名
    • 修改字段的长度

      • alter table 表名 modify 列名 类型(大小)
    • 删除字段

      • alter table 表名 drop column 列名
  • D

    • 删除表:drop table 表名;
    • 清空回收站purge recyclebin;

约束6个

  • 6个约束

    • 主键约束Primary Key: 唯一,不能为null

      • 主键约束、和唯一约束不能同时设置
    • 唯一约束Unique :唯一,允许为null

      • 主键约束、和唯一约束不能同时设置
    • 检查约束Check:对字段的数据范围进行限制( 年龄 age>0 and age<100)

    • 外键约束Foreign Key:两张表之间

      • 用于定义主表和从表之间的关系,外键约束要定义在从表上,主要则必须具有主键约束或是unique约束,当定义外键约束后,要求外键列数据必须在主表的主键列存在或是为NULL。
    • 默认约束Default:对字段进行默认值的设置

    • 非空约束Not Null:对字段 是否可以为null进行限制

  • 主键和唯一键的区别:

    • a.主键不能为null,唯一键可以为null
    • b.一张表只能设置一次主键(主键可以是一个,也可以是复合主键),但可以设置多次唯一键
  • 作为范围可以分为两大类

    • 二者的区别:

      • 列级约束

        • 6个约束类型(4个+默认、非空)
        • 定义在列的后面
        • 空格
        • 只能应用于一个列
      • 表级约束

        • 4个(主键、外键、唯一、检查)
        • 定义在表的后面
        • 逗号
        • 应用于一个列或多个列
    • 列级约束

      • create table student
        (
        stuNo number(3) primary key, –主键约束
        stuName varchar(20) unique ,–非空约束not null、唯一约束
        stuAddress varchar(50) default ‘陕西西安’ check( length(stuAddress)>2 and length(stuAddress)<20 ),–默认约束、check约束
        subId number(3)
        )

      • 注意事项

        • ①如果一个列有多个约束(且包含default约束),则default约束必须放在第一个

        • ②唯一约束:不适用于null

        • ③check约束:

          • 怎么在where后面写限制语句,就怎么在check()中写。
          • check(gender in (‘男’,’女’));
          • check(age>=0 and age<=100) ;
        • ④ 不论违反的是 主键约束,还是唯一约束,在重复插入数据时,都报错:违反唯一约束条件

      • 约束命名

        • constraint 约束类型_约束列

        • 命名规则

          • 给主键约束命名:constraint PK_stuno
          • 给检查约束命名:constraint CK_stuaddress
          • 唯一约束… : constraint UQ_stuName
          • 非空..: constraint NN_stuName
          • 外键… : constraint FK_子表的名字_父表的名字
          • 默认约束..: 一般不需要命名
        • 注意事项: 约束名 在当前用户下的所有表之间 共用。

        • 创建带约束的表

          • create table student1
            (
            stuNo number(3) constraint PK_stuno primary key, –主键约束
            stuName varchar(20) constraint NN_stuName not null constraint UQ_stuName unique ,–非空约束、唯一约束
            stuAddress varchar(50) default ‘陕西西安’ constraint CK_stuAddress check( length(stuAddress)>2 and length(stuAddress)<20 ),–默认约束、check约束
            subId number(3)
            )
    • 表级约束

      • create table student2
        (
        stuNo number(3),
        stuName varchar2(20),
        stuAddress varchar2(50),
        subId number(3),
        constraint PK_stuNo1 primary key(stuNo) ,–表级约束
        constraint UQ_stuName_subId unique(stuName,subId) ,//唯一约束作用于两列
        constraint CK_stuAddress1 check(length(stuAddress)>2)
        );
  • 外键(完整约束条件)

    • 创建外键

      • create table sub
        (
        sId number(3) primary key, –外键所指向的列 必须是主键或唯一键
        sname varchar(20)
        );
      • create table student3
        (
        stuNo number(3),
        stuName varchar2(20),
        subId number(3),
        constraint FK_student3_sub foreign key(subId) references sub(sId) –表级外键约束
        );
    • 要求外键列数据必须在主表的主键列存在或是为NULL。

    • 表级约束

      • CREATE TABLE table_name
        (column_1 datatype ,
        column_2 datatype ,

        CONSTRAINT fk_column FOREIGN KEY (column_1, column_i, … column_n) REFERENCES parent_table (column_1, column_i, … column_n)
        );
    • 列级约束

      • CREATE TABLE table_name
        (column_1 datatype ,
        column_2 datatype CONSTRAINT fk_column REFERENCES parent_table (column_name),

        );
    • 级联置空、级联删除

      • 级联删除

        • on delete cascade 当父表中的列被删除时,同时会自动删除子表中对应的记录。
      • 级联置空

        • on delete set null 当父表中的列被删除时,同时会将子表中对应字段设置为null。
      • create table student4
        (
        stuNo number(3),
        stuName varchar2(20),
        subId number(3),
        constraint FK_student4_sub foreign key(subId) references sub(sId)
        –级联删除on delete cascade
        on delete set null — 级联置空
        );

      • 在子表中设置

    • 使用外键的一些设置

      • 1)当附表中没有对应的记录时,不要将记录添加到子表中
      • 2)不要更改附表中的值,而导致子表中的外键孤立
      • 3)子表存在与父表对应的记录时,删除父表记录时 应该级联删除 或级联置空。
      • 4)删除父表前,建议先删除子表的数据
  • 追加约束

    • ① alter table 表名 add constraint 约束名 具体的约束

      • 追加唯一约束:

        • alter table student5 add constraint UQ_stuname5 unique(stuName);
      • 追加主键约束:

        • alter table student5 add constraint PK_stuno5 primary key(stuno);
      • 追加检查约束:

        • alter table student5 add constraint CK_stuname5 check( length(stuname)>2 and length(stuname)<20 );
      • 追加外键约束:

        • alter table student5 add constraint FK_student5_sub foreign key(subid) references sub(sid) ;
    • ② alter table 表名 modify 约束名 具体的约束

      • 追加默认约束(默认值):

        • alter table student5 modify stuname default ‘未命名’ ;

          • //追加 默认约束 默认值是’未命名’
      • 追加非空约束

        • alter table student5 modify stuname constraint nn_stuname5 not null ;
  • 删除约束

    • alter table 表名 drop constraint 约束名;

      • alter table student5 drop constraint CK_stuname5;
    • 如何删除默认约束?

      • alter table student5 modify stuname default null ;将默认值设置为null,就相当于删除了默认约束。
  • 完整性约束

    • 域完整性:对“列”的数据进行限制
    • 实体完整性:对“行”的数据进行限制
    • 引用完整性:多张表的数据 进行限制(外键)
    • 自定义完整性:开发者自己定义的 约束条件 (通过触发器实现)

DML

添加数据

  • insert into student(sno,sname) values(1,’zs’) ;
  • insert into student values(2,’ls’) ;

删除数据(不是删除表)

  • delete from student where sno=1;

    • 删除一条数据
  • delete from 表名;

    • 删除所有记录,表结构还在,写日志,可以恢复的,速度慢
  • drop table student;

    • 删除表结构和数据
  • truncate table student;

    • (截断表)删除表中的所有记录,表结构还在,不写日志,无法找回删除

修改数据

  • update student set sname=’lisi’,sno=20 where sno =2;

DQL

基本select查询

  • select * from 表名;

    • select * from emp;
  • 基础查询

    • 多字段查询

      • select hiredate from emp;

      • 查询全部列

        • select * from emp;
        • select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp ;
        • 推荐使用第二种。
    • 别名

      • select ename as “姓名”,job as “工作” from emp;

      • select ename “姓名”,job “工作” from emp;

      • select ename 姓名 ,job 工作 from emp; –不推荐,如下是错的:

        • select ename “from” ,job 工作 from emp;
    • distinct去重

      • select distinct job,deptno from emp ;
    • 列的计算

      • select ename,sal*12 from emp;
  • 条件查询where

    • 运算符

      • between ..and.. 数字/日期

        • select * from emp where sal between 2000 and 3000

        • select * from emp where hiredate between ’01-1月-81′ and ’10-11月-81′ ;

          • 日期默认格式: ’01-1月-81′
      • 模糊查询:like

        • 通配符

          • % :若干个任意字符 0-N
          • _ :必须是1个任意字符
        • 查询姓名中 第二个字母是M的员工

          • select * from emp where ename like ‘_M%’ ;
        • 查询姓名长度>=6的员工。

          • select * from emp where ename like ‘______%’ ;
        • 转义符:ename: A_B

          • select * from emp where ename like ‘A_B’ escape ” ; A_B
      • in

        • 相当于多个or
        • deptno =10 or deptno =20 等价于 deptno in(10,20)
  • 排序查询

    • select * from …. where… order by 字段名|别名|表达式|数字 asc(默认)|desc ;

    • 多个字段进行排序

      • ..order by 字段名1,字段名2,….,字段N ;
    • 如果排序存在null:在Oracle中Null最大。如果需要null排在最后,使用nulls last ;

  • 分组

    • 查询时,不在组函数(多行函数)中的列,必须在group by后面

    • 筛选 行:where

    • 筛选 组:having

    • 查询每个部门的人数:按部门分组

      • select deptno, job,count(*) from emp group by deptno,job ;
    • 查询每个部门的人数,只查询人数大于5的组

      • select deptno,count() from emp group by deptno having count()>5 ;

多表查询

  • 连接查询

    • 内连接

      • select e.empno,e.ename,e.deptno,d.deptno,d.dname
        from emp e,dept d
        where e.deptno = d.deptno
      • select e.empno,e.ename,e.deptno,d.deptno,d.dname
        from emp e
        inner join dept d
        on e.deptno = d.deptno;
    • 外连接

      • 左外

        • select e.empno,e.ename,e.deptno,d.deptno,d.dname
          from emp e,dept d
          where e.deptno = d.deptno(+) ;
        • select e.empno,e.ename,e.deptno,d.deptno,d.dname
          from emp e
          left outer join dept d
          on e.deptno = d.deptno;
      • 右外

        • select e.empno,e.ename,e.deptno,d.deptno,d.dname
          from emp e,dept d
          where e.deptno(+) = d.deptno
        • select e.empno,e.ename,e.deptno,d.deptno,d.dname
          from emp e
          right outer join dept d
          on e.deptno = d.deptno;
      • 完全

        • select e.empno,e.ename,e.deptno,d.deptno,d.dname
          from emp e
          full outer join dept d
          on e.deptno = d.deptno;
    • 自连接

      • 将一张表,通过别名“视为”不同的表

        • select e.ename 员工, b.ename 领导 from emp e,emp b where e.mgr = b.empno ;
  • 集合运算

    • select * from emp where deptno =10
      union
      select * from emp where deptno =20;
    • union:并集,返回各个查询的所有记录,重复部分,只出现一次。
    • union all:并集,返回各个查询的所有记录,重复部分 不受影响。
    • intersect,交集:返回两个集合 共用的记录。
    • minus,补集:返回 包含在第一个查询集合中,但同时不包含在第二个查询集合中的记录。

分页SQL

TCL事务

事务流程

  • 创建保存点

    • savepoint aa;
  • 回滚至保存点

    • rollback to aa;
  • 提交

    • commit

DCL

用户管理

  • 创建用户

    • create user 用户名 identified by 密码;

      • 在oracle中要创建一个新的用户使用create user语句,一般是具有dba(数据库管理员)的权限才能使用
      • 密码必须以字母开头,不能以数字开头
      • 管理员 system
      • conn 用户名/密码;
  • 修改密码

    • 给自己修改密码

      • sql> password 用户名

        • 自己可以修改自己的密码
    • 给别人修改密码

      • sql> alter user 用户名 identified by 新密码

        • 需要具有dba的权限,或是拥有alter user 的系统权限
        • System可以修改别人的密码
  • 删除用户

    • drop user 用户名 [cascade]

      • 一般以dba的身份去删除某个用户,如果用其它用户去删除用户则需要具有drop user的权限
    • 级联删除

      • 在删除户时,注意:如果要删除的用户,已经创建了表,那么就需要在删除时带一个参数cascade

权限管理

  • 创建的新用户是没有任何权限的,甚至连登录的数据库的权限都没有,需要为其指定相应的权限。给一个用户赋权限使用命令grant,回收权限使用全令revoke。
  • 权限分类

image

- 权限

	- 系统权限

		- 用户对数据库的相关权限
		- 140多个系统权限

	- 对象权限

		- 用户对其他用户的数据对象操作的权限
		- Oracle拥有25个对象权限

- 角色(一组权限)

	- 预定义角色
	- 自定义角色
  • 角色

    • 从系统权限中挑出来的权限
    • Connect角色:一些基本权限
    • Dba角色
    • Resource角色:让用户在任何表空间建表
  • 授予权限

    • grant select on emp to xiaoming
  • 查询

    • select * from scott.emp;
  • 收回权限

    • 谁授予的权限,由谁收回
    • scott希望收回xiaoming对emp表的查询权限
    • revoke select on emp from xiaoming
  • 权限维护

    • xiaoming用户可以去查询scott的emp表/还希望小明可以把这个权限继续给别人。

      • 如果是对象权限,就加入with grant option

        • grant select on emp to xiaoming with grant option
      • 系统权限,system给xiaoming权限时:

        • grant connect to xiaoming with admin option
    • 如果scott把xiaoming对emp表的查询权限回收,那么xiaohong会怎样

      • 采用级联回收的机制管理权限

口令管理

  • 使用profile管理用户口令

    • profile是口令限制,资源限制的命今集合,当建立数据库时,oracle会自动建立名称为default的profile.当建立用户没有指定profile选项,那oracle就会将default分配给用户。
  • 账户锁定

    • 指定该帐户(用户)登陆时最多可以输入密码的次数,也可以指定用户锁定的时间(天)一般用dba的身份去执行该命令

      • 例子:指定scott这个用户最多只能尝试3次登陆,锁定时间为2天,让我们看看怎么实现

        • sql> create profile lock_account limit failed_login_attempts 3 password_lock_time 2;
          sqI>alter user profile lock_account;
  • 给账户(用户)解锁

    • sql> alter user tea account unlock;
  • 终止口令

    • 为了让用户定期修改密码可以使用终止口令的指令来完成,同样这个命令也需要dba身份来操作

      • 例子:给前面创建的用户tea创建一个profile文件,要求该用户每隔10天要修改自家的登陆密码,宽限期为2天。

        • sql> create profile myprofile limit password_life_time 10 password_grace_time 2;
          sqI> alter user tea profile myprofile;
  • 口令历史

    • 如果希望用户在修改密码时,不能使用以前使用过的密码,可使用口令历史,这样oracle就会将口令修改的信息存放到数据字典中,这样当用户修改密码时,0racle就会对新旧密码进行比较,当发现新旧密码一样时,就提示用户重新输入密
      码。

      • 1)建立profile
        sql> create profile password_history limit password_life_time 10 password-grace-time 2 password_reuse_time 10
        //password_reuse_time//指定口今可重用时间即10天后就可以重用
      • 分配给某一个用户
  • 删除profile

    • 当不需要某个profile文件时,可以删除该文件
      sql> drop profile password_history [cascade]

数据库设计

三大范式

  • 1NF

    • 确保每列原子性(不可分),可以避免数据冗余(重复)
  • 2NF

    • 宏观:确保每张表 只描述一件事情
    • 微观:除了主键以外的其他列,都必须依赖于主键
  • 3NF

    • 除了主键意外的其他列,都必须 不传递依赖于主键
  • 注意

    • a.要满足第N范式,必须先满足第N-1范式
    • b.数据库范式 仅仅是一个 建议而已,实际开发需要综合考量 性能等其他影响(比如2F 3F,必须拆表)

如果查询的数据包含null,使用范围查询in和Not in时需要注意:

in

  • 等价于1

    • select …from emp where comm in (a,b) ;
    • select …from emp where comm=a or comm=b ;
  • 等价于2

    • select * from emp where comm in (300,null) ;
    • select * from emp where comm =300 or comm = null ;

on

  • 等价于

    • select * from emp where comm not in (300,null) ;
    • select * from emp where comm != 300 and comm !=null ;

(判断是否是Null: is null , is not null)

  • null!=null –>false
  • null=null –>false

复制表:批量增加 –>创建表myemp, 批量增加数据

create table myemp as select empno,ename,job from emp;

如何增加海量数据:

a. SQL Loader工具 b.数据泵 c.外部表

序列

可以实现自动增长

序列的两个属性:

  • nextval:指向下一个元素
  • currval:指当前一个元素

语法

  • create sequence 序列名
    increment by 步长
    start with 初始值
    maxvalue|nomaxvalue
    minvalue|nominvalue
    cycle|nocycle
    cache 3|no cache

示例:

  • create sequence myseq ; — [1] 2 3 4 5 6

裂缝

  • 使用序列,有可能产生裂缝:
  • 系统异常、回滚、多表使用同一个序列

修改序列

  • 【alter】 sequence 序列名
    increment by 步长2
    start with 初始值
    maxvalue|nomaxvalue
    minvalue|nominvalue
    cycle|nocycle
    cache 3|no cache
  • 注意:修改序列 只会影响修改以后的值。
  • insert into student values(myseq.nextval ,….) ;

删除序列

  • drop sequence 序列名;
  • drop sequence myseq;

删除重复数据

准备重复数据

  • create table mystudent
    (
    stuno number ,
    stuname varchar2(20),
    stuage number
    );
    insert into mystudent values(1,’zs’,23);
    insert into mystudent values(1,’zs’,23);

insert into mystudent values(2,’ls’,24);
insert into mystudent values(2,’ls’,24);

insert into mystudent values(3,’ww’,55);

尝试1:distinct

  • delete from mystudent where stuno in (
    select distinct stuno from mystudent );

尝试2:伪列(rownum,rowid)

  • 重复数据可以通过rowid删除,但不能通过rowNum删除。

  • 删除全部数据

    • delete from 表名;
    • truncate table 表名; –效率高
  • 分析

    • select rowid, s.* from mystudent s;发现数据插入的越晚,rowid越大;数据插入越早,rowid越小。
      rowid的组成AAAST+ AAE AAAAJV AAA:
      数据对象号(6位):AAAST+
      相关数据文件号(3位):AAE
      数据块号(6位):AAAAJV
      数据块中行号(3位):AAA
  • 删除重复的行:

    • 将重复数据分为一组:

      • select min(rowid) from mystudent
        group by stuno,stuname,stuage ;
    • 数据插入越早,rowid越小。

      • 1组
        insert into mystudent values(1,’zs’,23); –rowid:AAAST+AAEAAAAJVAAA –第一条
        insert into mystudent values(1,’zs’,23); –rowid:AAAST+AAEAAAAJVAAB –x
        2组
        insert into mystudent values(2,’ls’,24); –rowid:AAAST+AAEAAAAJVAAC –第二条
        insert into mystudent values(2,’ls’,24);–rowid:AAAST+AAEAAAAJVAAD –y
        3组
        insert into mystudent values(3,’ww’,55);–rowid:AAAST+AAEAAAAJVAAE –第三条
    • 最终SQL:

      • delete from mystudent where rowid
        not in(
        select min(rowid) from mystudent
        group by stuno,stuname,stuage) ;
    • 思路

      • 将相同数据分为一组–>在每组中 找到最小的rowid,即min(rowid) –>删除不是最小的rowid,即delete from …not in (…)
    • 尝试:

      • 查询每种工作的最低工资,以及领取该工资的员工姓名。

        select min(sal),job from emp
        group by job ;

        select e.ename ,t.job, t.minsal from emp e , (select min(sal) minsal ,job from emp
        group by job) t
        where e.empno = t.empno ;
         
        –where t. minsal = e.sal and t.job = e.job ;

        t
        minsal, job
        a     b
        c     d
        e      f
         
        查询出工资不超过2500的人数最多的部门名称。
        分析:
        select d.deptno,d.dname from dept d ,emp e
        where d.deptno = e.deptno and e.sal<=2500
        group by d.deptno,d.dname
        having count() = ( select max(count()) from emp
                            where sal<=2500
                            group by deptno
                           );
         
        查询出管理 员工人数最多的人的名字   和 他管理的人的名字。
         
        先查出管理最多的员工数 (最多管理几个人)
        select max(cn) from
        (select count(mgr) cn, mgr from emp
        group by mgr) t ;
         
        再查出与最多员工 相对应的MRG
        select mgr from emp
        group by mgr
        having count(mgr) = (select max(cn) from
        (select count(mgr) cn, mgr from emp
        group by mgr) t );
         
         
        最终:
        select e.ename 员工 , b.ename 领导 from emp e ,emp b
        where e.mgr = b.empno
        and e.mgr = (select mgr from emp 
        group by mgr
        having count(mgr) = (select max(cn) from
        (select count(mgr) cn,mgr from emp
        group by mgr) t ));

视图

从表中抽取出的逻辑上相关的数据集合(字段、列)

员工编号、年薪、部门名称

select empno,sal*12,dname from emp e,dept d where e.deptno = d.deptno ;

select * from emp ;

创建视图

  • create view 视图名
    as
    查询语句 ;
create  view myempview
as 
select empno 编号,sal*12 年薪,dname 部门名称 from emp e,dept d
where e.deptno = d.deptno ;
  • –>普通用户第创建视图时,权限不足。
  • 必须通过SYS授权 : grant create view to scott;
  • 注意:视图中 的查询语句中,如果存在 字段的表达式,则必须给该表达式起别名.

小结:

  • a.简化查询
  • b.数据安全 :用户名、密码、身高、体重、手机号

对视图的增删改

  • 一般建议:视图值建议用于查询、分析,不要用于增删改。
  • with read only
    • 确保只能对视图进行查询,不能增删改
create view baseview
as 
select empno,ename,job from emp
with read only;//设置只读
  • with check option
    • 确保对视图的操作,必须满足where子句
create view empdeptno20
as
select * from emp where deptno=20
with check option;

视图分类

  • 简单视图

    • 表的数量 1个
    • 函数没有
    • 分组没有
  • 复杂视图

    • 表的数量 1个/多个
    • 函数有
    • 分组有

视图不建议用于DML。但如果非要用DML,则必须满意以下条件:

  • ①当出现以下之一时,不能Insert/update

    • 组函数、group by、distinct、rownum、列的定义为表达式sal*12
  • ②当出现以下之一时,不能delete

    • 组函数、group by、distinct、rownum
  • create view testview
    as
    select avg(sal) 平均工资,sum(sal) 总工资,job from emp group by job ;
    –没有出现在组函数中的列,必须出现在group by后。

DQL:查询语句

DML:增删改语句

DDL:数据库定义语言,create table/view/sequence

索引(index)

并不是所有情况都适合建立索引:

  • 不合适:

    • 数据集中的列,不经常在where中使用的列,数据量小,经常更新

Oracle中的索引类型: B树索引(默认)、位图索引(矩阵索引)

创建索引

  • create index myindex on emp(deptno);

如果要观察是否使用了索引:查看SQL执行计划

删除索引 drop index myindex;

同义词

在scott用户中查询hr用户中的 employees表:

  • select * from hr.employees; –需要先授权 :sys账户中 grant select on hr.employees to scott;

私有同义词:谁创建谁使用。

  • create puyblic synonym hremp for hr.employees;

公有同义词:无论谁创建,任何人都可以使用。

  • 需要先授权 :sys账户中 grant create public synonym to scott;
  • 创建公有同义词:
    • 给hr.employees起别名:
create synonym hremp  for hr.employees;
--需要先授权:sys账户中grant create synonym to scott;

删除同义词 drop synonym hremp ;

撤销权限:

  • grant ….to scott;
  • revoke …from scott;
  • revoke create synonym from scott;

存储过程

语法

  • create procedure 名字(参数列表 ) as PLSQL语句 ;–没有declare
create procedure myPro
as
  pnum number:=10;
begin
  dbms_output.put_line('hello');
  dbms_output.put_line('world'||pnum);
end;

调用:

  • 方法一:

    • exec myPro();
  • 方法二:

    • begin
      myPro();
      myPro();
      end;

有参:存储过程中,参数 in/out

存储函数

最大区别:

  • 存储函数有return ;存储过程没return ;
  • 存储过程只能通过 out返回值;存储函数 可以通过out和reutrn返回值。

语法

  • create function 名字(参数列表 )
    return 类型
    as
    PLSQL语句 ;–没有declare
    return 值;
  • 虽然return和out可以混用,但一般建议:如果返回值 有1个,则用retrun;否则(多个 或0个)用out.

小节

查询某个部门的 所有员工信息。

输入参数:deptno
输出参数:所有员工信息 — List — 返回一个光标

存储过程 /存储函数 不能直接返回光标。 — package
小结:要返回光标,必须先建包package。
包:包头+包体
其中 包头,相当于 接口; 包体,相当于实现类。

创建包头:右键程序包–>新建
创建包体:右键包头–>创建主体

可以在包中 建立存储过程。

原创文章,作者:306829225,如若转载,请注明出处:https://blog.ytso.com/267219.html

(0)
上一篇 2022年6月14日
下一篇 2022年6月14日

相关推荐

发表回复

登录后才能评论