【ORACLE-学习笔记】 常用命令

//***************************** Day1：基础操作和数据库安全 ***********************************

// 基本操作

进入SQL*plus：

SQLPLUS - > user和password

查看当看的用户：

SHOW USER;

查看数据库里面的数据库表：  

SELECT table_name FROM user_tables;

修改自身的密码：

PASSW

退出/断开：

EXIT/DISC

切换用户：

CONN user/password

// 用户管理

创建用户：

CREATE USER user IDENTIFIED BY password;

修改用户密码：

PASSWORD user;

修改用户：

ALTER USER user IDENTIFIED BY newpassword

删除用户：

DROP USER user [CASCADE]

// 权限管理（系统权限、对象）

授予系统权限（对系统级的操作）：

GRANT system_privilege|ALL_PRIVILEGES TO user|role

授予对象权限（对表、视图操作）：

GRANT object_privilege|ALL ON scheme.object TO user|role

回收授予的系统权限：

REVOKE system_privilege|ALL_PRIVILEGES FROM user|role

回收授予的对象权限：

REVOKE object_privilege|ALL ON schema.object FROM user|role

系统权限传递：

GRANT system_privilege|ALL_PRIVILEGES TO user|role [WITH ADMIN OPTION]

1）如果system给zhangsan授予CREATE SESSION时加上了WITH ADMIN OPTION,

此时zhangsan也可以给其它用户授予CREATE SESSION的权限，

如果zhangsan又给lisi授予CREATE SESSION权限，

那么lisi肯定是可以登录了。

2）如果system回收了zhangsan的权限，那么lisi还能登录吗？还是能登录

        3）system可以直接回收lisi的CREATE SESSION权限

对象权限传递：

GRANT object_privilege|ALL ON scheme.object TO user|role [WITH GRANT OPTION]

1）如果scott给zhangsan授予SELECT时加上了WITH GRANT OPTION,

此时zhangsan也可以给其它用户授予SELECT的权限，

如果zhangsan又给lisi授予SELECT权限，

那么lisi肯定是可以查询了：SELECT * FROM scott.emp;

2）如果scott回收了zhangsan的SELECT权限，那么lisi还能查询吗？不能查询了

3）scott不可以直接回收lisi的SELECT权限

// 角色管理

角色分为两类：

第一类是内置角色（CREATE SSSION、CONNECT、RESOURCE、DBA）

第二类是自定角色：

1）角色创建：

CREATE ROLE role;

2）把角色授权权限

GRANT system_privilege|ALL_PRIVILEGES TO role [WITH ADMIN OPTION]

3）把角色给用户

GRANT role TO user;

4）角色删除：

DROP ROLE role;

// PROFILE

专门做口令管理（它只对用户设置，不能对角色设置）

实例：

指定scott密码只能尝试输入3次，如果超过限制就锁定2天？

1）创建PROFILE

  CREATE PROFILE profile_name LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 2;

  具体的参数设置，请在网上找一下参数详解！

2）把创建好的PROFILE指定对scott

  ALTER USER scott PROFILE profile_name

3) 现在scott就会使用这个PROFILE的限制（3次过后会锁2天）

4）如果要解锁

ALTER USER scott ACCOUNT UNLOCK;

5）删除PROFILE

DROP PROFILE profile_name [CASCADE];

//***************************** Day2：SQL基础 ***********************************

1、SQL分类：

DDL、DML、DQL、DCL

2、数据类型：

   1）字符型

      变长：VARCHAR2(0-4000)、

      定长：CHAR(0-2000)

      如果我们在设计数据库时，已经确定某些字符串长度是固定的，尽量使用CHAR，提高效率！

      举例：二代身份证CHAR(18)，MD5 CHAR(32)

   2）数字型

     NUMBER(1) 0-9

     NUMBER(5) 0-99999

     NUMBER(5, 2) 555.22

   3）时间类型

     date 

     timestamp：精确到毫秒，但是我们在一般的应用里用不上

     注：可直接使用sysdate取得当前时间！

   4）其他类型

     blob，最大存储为4G，专存二进制

     clob，最大存储为4G，专存字符串

 3、DDL

  a）CREATE

CREATE TABLE sys_user (

id NUMBER(10) PRIMARY KEY,

username VARCHAR2(50) NOT NULL,

password CHAR(32) NOT NULL,

birthday date,

salary NUMBER(6,2)

);

b）ALTER

  添加列：

   ALTER TABLE table_name ADD 列描述

  修改列：

   ALTER TABLE table_name MODIFY 列描述

     注：如果修改时涉及到约束,如非空约束，必须要保证数据库里没有数据才可成功更改！

  删除列：

   ALTER TABLE table_name DROP COLUMN 列名;

  修改表名：

          ALTER TABLE table_name RENAME TO new_table_name;

       修改列名：

         ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name

    c）DROP

       删除列：

          ALTER TABLE table_name DROP COLUMN 列名;

       删除表：

         DROP TABLE table_name;

4）五大约束

  CREATE TABLE sys_user (

id NUMBER(10) PRIMARY KEY,

username VARCHAR2(50) NOT NULL UNIQUE,

password CHAR(32) NOT NULL,

age NUMBER(3) CHECK(age>=18 AND age=120), 

birthday date,

salary NUMBER(6,2)

);

CREATE TABLE sys_user (

id NUMBER(10),

username VARCHAR2(50) NOT NULL,

password CHAR(32) NOT NULL,

age NUMBER(3), 

birthday date,

salary NUMBER(6,2),

CONSTRAINTS sys_user_id_pk PRIMARY KEY(id),

CONSTRAINT sys_user_username_unique UNIQUE(username),

CONSTRAINT sys_user_age_check CHECK(age>=18 AND age<=120)

);

CREATE TABLE sys_user (

id NUMBER(10),

username VARCHAR2(50) NOT NULL,

password CHAR(32) NOT NULL,

age NUMBER(3), 

birthday date,

salary NUMBER(6,2)

);

ALTER TABLE sys_user ADD CONSTRAINTS sys_user_id_pk PRIMARY KEY(id);

ALTER TABLE sys_user ADD CONSTRAINT sys_user_username_unique UNIQUE(username);

ALTER TABLE sys_user ADD CONSTRAINT sys_user_age_check CHECK(age>=18 AND age<=120);

对后期添加的约束做移除：

ALTER TABLE sys_user DROP CONSTRAINT constraint_name;

特殊（Not null）：

NOT NULL没有Constraint的写法，它的添加和删除如下：

添加：

ALTER TABLE table_name MODIFY column_name NOT NULL;

删除：

ALTER TABLE table_name MODIFY column_name NULL;

    建议：直接在列后面追加，这样更直观！

5）外键约束

   主表：

CREATE TABLE sys_role(

   id NUMBER(10) PRIMARY KEY,

   name VARCHAR2(10) NOT NULL

)

    从表：

CREATE TABLE sys_user(

   id NUMBER(10) PRIMARY KEY,

   name VARCHAR2(10) NOT NULL,

   role_id NUMBER(10) REFERENCES sys_role(id) [ON DELETE CASCADE]

)

CREATE TABLE sys_user(

   id NUMBER(10) PRIMARY KEY,

   name VARCHAR2(10) NOT NULL,

   role_id NUMBER(10),

   CONSTRAINT sys_user_role_id_fk FOREIGN KEY(role_id) REFERENCES sys_role(id) [ON DELETE CASCADE]

)

ALTER TABLE sys_user ADD CONSTRAINT sys_user_role_id_fk FOREIGN KEY(role_id) REFERENCES sys_role(id) [ON DELETE CASCADE];

注意：

  a）创建表：先建主表，再建从表

  b）新增数据：先新增主表数据，再新增从表数据

  c）删除数据：

        如果直接删除主表的数据，会提示我们先去删除被从表引用的记录，再删除主表的记录

        如果在外键上指定了ON DELETE

        CASCADE，那么我们在删除主表记录的同时会级联删除相应的子表记录，不建议使用！

注意2：要不要使用外键？

1）使用外键：

  优点：使用约束，强制保持数据的一致性

  缺点：后期难以维护

        性能问题

2）不使用外键（推荐）

  优点：解决了使用外键的缺点

  缺点：数据的一致性，需要程序去控制关系，容易出错！

//*************************** Day3：简单查询和条件查询基础 ****************************

1、SQL结构（注意顺序）

SELECT 

        [DISTINCT|ALL] select_list

        FROM table_list

    [where_clause]

        [group_by_clause]

        [HAVING condition]

        [order_by_clause]

2、简单查询

   1）在数据量大时，必须指定部分字段进行查询，而不是使用*查询。

   2）使用别名代替表中的字段名

     SELECT ename 名称, job "工作", sal AS 工资, comm AS "补助" FROM emp;

     注：

        a）使用别名时不能使用单引号

      b）如果要给emp（表）给别名，不能使用AS方式！

   3）去除重复记录

     SELECT DISTINCT job FROM emp;

      SELECT DISTINCT job, sal FROM emp;

   4）表达式（||+、-、*、/等等）

     SELECT sal*13 + nvl(comm,0)*12 FROM emp;

   5）使用函数操作查询的字段

     SELECT max(sal),min(sal),avg(sal),sum(sal) FROM emp; 

 SELECT sysdate FROM dual; 

   6）排序

      a）遇NULL，指定排前（NULLS FIRST，默认）还是排后（NULLS LAST）

         SELECT comm FROM emp ORDER BY comm DESC NULLS LAST;

      b）别名排序同直接使用列名效果一样，针对长表达式使用别名更简单！

      SELECT sal as "工资" FROM emp ORDER BY "工资";

      c）利用多个字段进行排序（如果第一个字段相同，再参考第二个字段）

         SELECT ename,sal,empno FROM emp ORDER BY sal ASC, empno DESC;

3、条件查询

   1）WHERE条件操作符

   可以使用的操作符主要有关系操作符、比较操作符和逻辑操作符：

      · 关系操作符：<、<=、>、>=、=、!=、<>

      · 比较操作符：IS NULL、LIKE、BETWEEN...AND...、IN

      · 逻辑操作符：AND、OR、NOT

   2）查询值大小写敏感且字符串需用单引号（跟MySQL不同）

     SELECT * FROM emp WHERE ename=‘ALLEN‘

   3）查询条件中使用函数

      SELECT * FROM emp WHERE nvl(comm,0)=0;

   4）模糊查询

      _：可以替代一个字符

  %：可以替代多个字符

  SELECT * FROM emp WHERE job LIKE ‘_A%‘

   5）专门针对NULL值的查询

      SELECT * FROM emp WHERE comm IS NULL;

   6）日期比较

      SELECT * FROM emp WHERE hiredate<‘01-MAY-1981‘;

     SELECT ename,hiredate FROM emp WHERE hiredate>to_date(‘1982-01-01‘, ‘yyyy-mm-dd‘);

 注：Oracle日期格式化：yyyy-mm-dd hh24:mi:ss

   7）GROUP BY子句和HAVING子句

      GROUP BY都是跟在WHERE后面，如果没有WHERE条件，就直接跟在FROM后面

      HAVING，主要就是对GROUP BY的信息再进行筛选，它必须要跟在GROUP BY后面

      例：按部门的平均工资分组，然后筛选出平均工资大于等于2000的部门的所有员工信息？

 a)使用子查询

   SELECT * FROM emp WHERE deptno in(SELECT deptno FROM emp GROUP BY deptno HAVING AVG( sal)>=2000);

 b)把子查询后结果当做临时表

   SELECT * FROM emp,(SELECT deptno FROM emp GROUP BY deptno HAVING AVG(sal)>=2000) temp WHERE emp.deptno=temp.deptno; 

//************************* Day4：子查询、多表查询、联合查询 ****************************

1、子查询

   1）几道题？

     a）查询出emp表中工资最高的员工的名字和工作岗位？

        SELECT ename,job FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);

     b）查询工资高于平均工资的员工？

        SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp);

     c）查询部门名称里有A的员工信息？

        多表：

         SELECT * FROM emp e,dept d WHERE e.deptno=d.deptno AND d.dname LIKE ‘%O%‘;

      子查询：

      SELECT * FROM emp WHERE deptno IN(SELECT deptno FROM dept WHERE dname LIKE ‘%O%‘);

     d）查询部门名称里有A的员工信息和部门名称？

        多表：

         SELECT e.*,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno AND d.dname LIKE ‘%O%‘;

      把子查询后结果当做临时表：

      SELECT * FROM emp e, (SELECT * FROM dept WHERE dname LIKE ‘%O%‘) d WHERE e.deptno=d.deptno;

      注：能直接使用多表就直接使用多表，子查询的使用是要看情况的！

   2）返回单列和返回多列两种情况

     a）返回单列，用=

     b）返回多列，用in

   3）分页

     查询前10条的数据：

       SELECT e.*,ROWNUM FROM emp e WHERE ROWNUM <= 10;

     查询5到10条的数据：

       SELECT * FROM (SELECT e.*,ROWNUM r FROM emp e WHERE ROWNUM <= 10) temp WHERE r>=5;

    扩展：

    显示比自己部门的平均工资还高的员工信息？

    SELECT e.*,salary.avg_sal FROM emp e,(SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno) salary WHERE e.deptno=salary.deptno AND e.sal>salary.avg_sal;

2、多表查询

   1）传统查询

      a）笛卡尔积的产生和消除

         注：在消除笛卡尔积时，消除条件至少等于表的个数-1

      b）多表查询时，为了区别字段，建议使用别名。

   2）内连接

      a）等值连接

      SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;

      等同于：

      SELECT * FROM emp e, dept d WHERE e.deptno=d.deptno;

      b）不等值连接

      SELECT * FROM emp e INNER JOIN dept d ON e.deptno>d.deptno;

      考虑：显示各个员工的姓名工资及其工资的级别？

      SELECT e.ename,e.sal,s.grade FROM emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;

      等同于：

SELECT e.ename,e.sal,s.grade FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

      注：INNER关键字可以省略！

   3）自连接

     考虑：显示FORD的上级领导的姓名和工资？

     SELECT employee.ename,manager.ename,manager.sal 

FROM emp employee JOIN emp manager 

ON employee.mgr=manager.empno 

WHERE employee.ename=‘SCOTT‘;

等同于：

 SELECT employee.ename,manager.ename,manager.sal 

   FROM emp employee, emp manager 

   WHERE employee.mgr=manager.empno AND employee.ename=‘SCOTT‘;

   4）外连接

      a）左外连接

         使用左外连接的查询，返回的结果不仅仅是符合连接条件的行记录，还包          含了左边表中的全部记录。也就是说，如果左表的某行记录在右表中没有          匹配项，则在返回结果中右表的所有选择列表列均为空！

         SELECT e.ename,d.dname FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno;

      b）右外连接

         右外连接跟左外连接是相反的，它以右边的表为主表。

         SELECT e.ename,d.dname FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;

      c）全外连接（注：使用传统连接代替不了外连接）

         SELECT e.ename,d.dname FROM emp e FULL JOIN dept d ON e.deptno=d.deptno; 

      d）(+)，Oracle特有用法，它可以实现左外连接和右外连接，但不能实现全连接，了解即可！

   5）联合查询

  合并处理多个查询语句的结果！

1、UNION

     用于取得两个结果集的并集，并会自动去掉结果集中的重复行。

     SELECT * FROM emp e1 WHERE e1.sal>2500 UNION SELECT * FROM      emp e2 WHERE e2.job=‘MANAGER‘;

2、UION ALL

     使用UNION ALL并不会自动去掉结果集中的重复行！

     SELECT * FROM emp e1 WHERE e1.sal>2500 UNION ALL SELECT *      FROM emp e2 WHERE e2.job=‘MANAGER‘;

3、INTERSECT

     Oracle特有操作符，用于取得两个结果集的交集。

     SELECT * FROM emp e1 WHERE e1.sal>2500 INTERSECT SELECT *      FROM emp e2 WHERE e2.job=‘MANAGER‘;

4、MINUS

     Oracle特有操作符，用于取得两个结果集的差集。第一个集合减去第二个集合，      相同集去除。

     SELECT * FROM emp e1 WHERE e1.sal>2500 MINUS SELECT * FROM      emp e2 WHERE e2.job=‘MANAGER‘;

注：让MySQL支持INTERSECT和MINUS：http://www.jb51.net/article/51411.htm

//************************* Day5：其他常用操作 ****************************

1、序列

a）Oracle提供了Sequence对象，由系统提供自增长的序列号,通常用于生成数           据库数据记录的自增长主键或序号的地方。

b）创建Sequence

  你首先要有CREATE SEQUENCE或者CREATE ANY SEQUENCE权限。

     CREATE SEQUENCE 序列名

        [INCREMENT BY n] // 步长，默认为1

        [START WITH n] // 开始，默认为1

        [{MAXVALUE/ MINVALUE n|NOMAXVALUE}] // 最大/小值|没有最大或最小值

        [{CYCLE|NOCYCLE}] // 达到限制是否循环

        [{CACHE n|NOCACHE}]; // 存序列的内存块大小，默认20

例如：

CREATE SEQUENCE sys_user

INCREMENT BY 1

START WITH 1

MINVALUE 0

NOCYCLE

c）得到Sequence值

  · 当前值：序列名.currval

  · 下一个值：序列名.nextval

d）删除Sequence

DROP SEQUENCE 序列名

    e）修改Sequence

       ALTER SEQUENCE sys_user_seq MAXVALUE 999999;

       // 改变序列的初始值只能通过删除序列之后重建序列的方法实现

       // ALTER SEQUENCE sys_user_seq START WITH 1;

2、事务

1）要么成功，要么失败

    事务在数据库中主要用于保证数据的一致性，防止出现错误数据。在事务内     的语句都会被看成一个单元，一旦有一个失败，那么所有的都会失败，转账     是一个非常典型的例子。

2）事务的常见操作：

    SET TRANSACTION：设置事务属性（并发比较高时可能会用到只读事务）

    SAVEPOINT：设置保存点

    ROLLBACK TO SAVEPOINT：回滚至保存点

    ROLLBACK：回滚事务

    COMMIT：提交事务

     3）事务的ACID特性

    事务有4个特性，它们分别是原子性、一致性、分离性、持久性。

   a）原子性（Atomicity）：原子意味着不可分割，不管有多少程序，只要在同         一个事务中，那么它们就是一个整体

   b）一致性（Consistency）：事务执行的前后数据库都必须处于一致性状态，         只有在事务完成后才能被所有使用都看到，保证了数据的完整性。

   c）分离性（Isolation）：事务之间不相干扰，一个事务操作的数据不会被其         他事务看到和操作

   d）持久性（Durabilily）：一旦事务提交完成，那么这将是对数据永久的修改