oracle基础

关系图

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 ;
      • 2. 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。
• 权限分类

- 权限

	- 系统权限

		- 用户对数据库的相关权限
		- 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。
包：包头+包体
其中 包头，相当于 接口； 包体，相当于实现类。

创建包头：右键程序包-->新建
创建包体：右键包头-->创建主体

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