sql0001
001. https://blog.csdn.net/qinshi965273101/article/details/81907658
002. https://blog.csdn.net/hejh123456789/article/details/79437644
在给scott用户赋予操作视图权限时，登录system用户遇到错误如下： ORA-12560: TNS:protocol adapter error错误 https://bbs.csdn.net/wap/topics/330080263 解决上述问题之后，给scott赋予创建视图的权限： grant create view to scott; 给scott赋予创建同义词的权限： grant create synonym to scott; 单行函数： 一、字符函数 1、大小写控制函数 函数 结果 备注 LOWER('SQL Course') sql course 全部转化成小写 UPPER('SQL Course') SQL COURSE 全部转化成大写 INITCAP('SQL Course') Sql Course 所有单词首字母大写 2、字符控制函数 函数 结果 备注 CONCAT('Hello', 'World') HelloWorld 拼接两个字符串 SUBSTR('HelloWorld',1,5) Hello 截取字符串 LENGTH('HelloWorld') 10 统计字符串长度 INSTR('HelloWorld', 'W') 6 获取字符在字符串中的第几个位置出现 LPAD(salary,10,'*') *****24000 共显示十位，不足十位的在左边补充‘*’ RPAD(salary, 10, '*') 24000***** 共显示十位，不足十位的在右边补充‘*’ TRIM('H' FROM 'HelloWorld') elloWorld 去掉字符串首尾的某一个字符（只能去掉首尾字符，不能去除中间字符） REPLACE(‘abcd’,’b’,’m’) amcd 用‘m’替换‘abcd’中的‘b’ 二、数字函数 函数 结果 备注 ROUND(45.926,2) 45.93 四舍五入 TRUNC(45.926,2) 45.92 截断数字 MOD(1600,300) 100 求余 三、日期函数 1、在日期上加上或减去一个数字结果仍然是日期 2、两个日期相减返回日期之间相差的天数；日期之间不能做加法，无意义 3、可以用数字除24来向日期中加上或减去天数 函数 示例 结果 描述 备注 MONTHS_BETWEEN MONTHS_BETWEEN('01-SEP-95','11-JAN-94') 19.6774194 两个日期相差的月数 ADD_MONTHS ADD_MONTHS('11-JAN-94',6) '11-JUL-94' 向指定日期中加上若干月数 NEXT_DAY NEXT_DAY('01-SEP-95','FRIDAY') '08-SEP-95' 指定日期的下一个星期*对应的日期 LAST_DAY LAST_DAY('01-FEB-95') '28-FEB-95' 本月的最后一天 ROUND ROUND(sysdate,'MONTH') 2018/10/1 日期四舍五入 ROUND(sysdate,'YEAR') 2019/1/1 TRUNC TRUNC(sysdate,'MONTH') 2018/10/1 日期截断 TRUNC(sysdate,'YEAR') 2018/1/1 四、转换函数 1、隐性 源数据类型 目标数据类型 VARCHAR2 or CHAR NUMBER VARCHAR2 or CHAR DATE NUMBER VARCHAR2 DATE VARCHAR2 2、显性 函数 示例 结果 描述 TO_CHAR(date,'format model') TO_CHAR(sysdate,'yyyy-mm-dd hh:mi:ss') 2018-10-11 09:47:35 对日期的转换 TO_CHAR(number,'format model') TO_CHAR(12345,'$99,999.00') $12,345.00 对数字的转换 TO_DATE(char,'Format Model') TO_DATE('2018/10/11','yyyy-mm-dd') 2018-10-11 对字符的转换 TO_NUMBER(CHAR,'Format model') TO_NUMBER('234234.4350','999999.0000') 234234.435 字符串转化成数字 日期格式元素 yyyy 2018 YEAR TWO THOUSAND AND FOUR MM 02 MONTH JULY MON JUL DY MON DAY MONSAY DD 02 五、通用函数 适用于任何数据类型，同时也适用于空值 函数 示例 结果 备注 NVL(expr1,expr2) NVL(null,'this is null') this is null 如果expr1是空，就显示expr2 NVL2(expr1,expr2,expr3) NVL2('123','not null','this is null') not null 如果expr1是空，就显示expr3，否则显示expr2 NULLIF(expr1,expr2) NULLIF('abcd','abc') abcd 如果expr1和expr2相等，返回空；否则反回expr2 COALESCE(expr1,expr2,...exprn) COALESCE(null,null,'rwrw') rwrw 如果expr1是null，就取expr2，expr2是null，就取expr3，以此类推，如果全为null，就显示null 六、条件表达式 1、CASE表达式 语法： CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END 示例： select case 1 when 0 then 'this is 0' when 1 then 'this is 1' when 2 then 'this is 2' else 'this is others' end as theResult from dual; 2、DECODE函数 语法： DECODE(col|expression, search1, result1 , [, search2, result2,...,] [, default]) 示例： select decode(1,0,'this is 0', 1,'this is 1', 2,'this is 2', 'this is others') as theResult from dual; 七、嵌套函数 以上的各个单行函数是可以进行嵌套使用的。 示例： 将阿拉伯数字转化成英文 select to_char(to_date(trunc(4000.12),'J'),'JSP') || ' and ' || to_char(to_date(to_number(substr(4000.12,instr(4000.12,'.')+1)),'J'),'JSP') FROM DUAL 多表查询 一、笛卡尔集（叉集）（少用） 笛卡尔集和叉集其实是相同的，都是两个表没有联合查询却没有查询条件，查询出来的结果数量就是联合查询的几个表的数据条数的乘积 笛卡尔集示例： select e.last_name,d.department_name from employees e,departments d 叉集示例： select last_name,department_name from employees cross join departments 二、等值连接 等值连接示例： select e.last_name,d.department_name from employees e,departments d where e.department_id=d.department_id 三、非等值连接 四、内连接 合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行。 五、外连接 两个表在连接过程中出了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行。 没有匹配的行时，结果表中相应的列为NULL。外连接的WHERE字句条件类似于内部连接，但连 接条件中没有匹配行的表的列后面要加外连接运算符，即(+)。 右外连接：右边的表所有数据都显示，对应左边表没数据的左边表就显示NULL 方式一： SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column(+) = table2.column; 方式二： select e.last_name,e.department_id,d.department_name from employees e left outer join departments d on e.department_id=d.department_id 左外连接：左边的表所有数据都显示，对应右边表没数据的左边表就显示NULL 方式一 SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column = table2.column(+); 方式二： select e.last_name,e.department_id,d.department_name from employees e right outer join departments d on e.department_id=d.department_id 满外连接：左表和右表的数据均都显示出来，对方表没有对应的数据就显示NULL 示例： select e.last_name,e.department_id,d.department_name from employees e full outer join departments d on e.department_id=d.department_id 六、自连接 例如： select worker.last_name || 'works for ' || manager.last_name from employees worker,employees manager where worker.manager_id=manager.employee_id 七、自然连接： 使用NATURAL JOIN子句，会以两个表中具有相同名字的列为条件创建等值连接；如果列名相同而数据类型不同，就会产生错误 示例： select department_id,department_name,location_id,city from departments NATURAL JOIN locations 八、使用USING子句创建连接 1、在使用NATURAL JOIN子句创建等值连接时，可以使用USING子句子句指定等值连接中需要用到的列； 2、使用USING可以在有多个列满足条件时进行选择 3、不要给选中的列加上表名前缀或别名 4、JOIN和USING子句经常同时使用 示例： select e.last_name,d.department_name from employees e JOIN departments d USING(department_id) 九、使用ON子句创建连接 1、自然连接中是一具有相同名字的列作为连接条件的，可以使用ON子句指定额外的连接条件 2、ON子句使语句具有更高的可读性 示例1： select e.employee_id,e.last_name,e.department_id,d.department_id,d.location_id from employees e join departments d on (e.department_id=d.department_id) 示例2： select employee_id,city,department_name from employees e join departments d on e.department_id=d.department_id join locations l on d.location_id=l.location_id 分组函数 一、分组函数 分组函数作用于一组数据，并对一组数据返回一个值 1、分组函数类型 函数 用途 AVG 求平均值 COUNT 求总条数 MAX 求最大值 MIN 求最小值 STDDEV SUM 求和 2、分组函数语法 SELECT [column,] group_function(column), ... FROM table [WHERE condition] [GROUP BY column] [ORDER BY column]; 示例： select avg(salary),max(salary),min(salary),sum(salary) from employees where job_id like '%REP%' 3、在分组函数中使用NVL函数 NVL函数使分组函数无法忽略空值。 select avg(NVL(commission_pct,0)) from employees 4、DISTINCT关键字 count(DISTINCT expr)返回expr非空且不重复的记录总数 select count(DISTINCT department_id) from employees 5、GROUP BY子句 在select列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中。 包含在GROUP BY子句中的列不必包含在SELECT列表中。 使用一个列分组示例： select department_id,AVG(salary) from employees GROUP BY department_id 使用多个列分组： select department_id dept_id,job_id,AVG(salary) from employees GROUP BY department_id,job_id 6、过滤分组 不能在WHERE子句中使用组函数 可以在HAVING子句中使用组函数 使用HAVING过滤分组 语法： SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column]; 示例： select department_id,AVG(salary) from employees GROUP BY department_id HAVING MAX(salary)>10000 7、嵌套组函数 示例： select MAX(AVG(salary)) from employees GROUP BY department_id 子查询 一、子查询： 注意事项： 1、子查询要包含在括弧内 2、讲子查询放在比较条件的右侧 3、单行操作符对应单行子查询，多行操作符对应多行子查询 示例： select last_name from employees where salary > (select salary from employees where last_name='Abel') 二、单行子查询和多行子查询 当行子查询使用比较操作符：=、>、>=、<、<=、<> 例如： select last_name from employees where salary > (select salary from employees where last_name='Abel') 多行子查询使用多行比较操作符：IN（等于列表中的任意一个）、ANY（和子查询返回的某一值比较）、ALL（和子查询返回的所有值比较） 示例1： select employee_id,last_name,job_id,salary from employees where salary < any(select salary from employees where job_id='IT_PROG') and job_id<>'IT_PROG' 示例2： select employee_id,last_name,job_id,salary from employees where salary < all(select salary from employees where job_id='IT_PROG') and job_id<>'IT_PROG' 创建和管理表 一、常见的数据库对象 对象 描述 表 基本的数据存储集合，由行和列组成 视图 从表中抽出的逻辑上相关的数据集合 序列 提供有规律的数值 索引 提高查询效率 同义词 给对象起别名 二、表名和列名命名规则 1、必须以字母开头 2、必须在1-30个字符之间 3、必须只能包含A-Z、a-z、0-9、_、$和# 4、必须不能和用户定义的其他对象重名 5、必须不能是ORACLE的保留字 三、ORACLE中的数据类型 数据类型 描述 varchar2(size) 可变长字符数据 char(size) 定长字符数据 number(p,s) 可变长数值数据 date 日期型数据 long 可变长字符数据，最大可达2G clob 字符数据，最大可达4G raw(long raw) 原始的二进制数据 blob 二进制数据，最大可达4G bfile 存储外部文件的二进制数据，最大可达4G roeid 行地址 四、创建表： 1、create table语句创建表 语法： create table table_name( column0 dataType [default expr], column1 dataType [default expr], ... columnn dataType [default expr] ); 示例： create table testTable( id varchar2(10) not null, name varchar2(50) default 'no name', age number(5) ); 2、使用子查询创建表 子查询后面可以跟查询条件。 CREATE TABLE table_name [(column, column...)] AS sub_query; 示例： create table emp1 as select * from employees; 五、ALTER TABLE语句 1、给表追加新的列 ALTER TABLE table_name ADD (column0 dataType [DEFAULT expr], column1 dataType [DEFAULT expr], ... columnn dataType [DEFAULT expr],) 2、修改表现有的列 ALTER TABLE table_name MODIFY (column0 dataType [DEFAULT expr], column1 dataType [DEFAULT expr], ... columnn dataType [DEFAULT expr],) 3、为新追加的列定义默认值 4、删除表的一个列 ALTER TABLE table_name DROP COLUMN column_name; 5、重命名表的一个列名 ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name; 六、删除表和清空表 1、删除表 数据和结构都被删除 所有正在运行的相关事务被提交 所有相关索引被删除 DROP TABLE语句不能回滚 语句：DROP TABLE table_name; 2、清空表 删除表中的所有数据 释放表的存储空间 语句：DELETE TABLE table_name; TRUNCATE语句不能回滚，DELETE可以回滚 七、改变对象的名称 可以改变表、视图、序列、或同义词的名称 RENAME old_object_name TO new_object_name; 数据处理 这里简单的增删改数据就不做记录了。主要看一下数据库事务。 一、数据库事务 事务：一组逻辑操作单元，使数据从一种状态变换到另一种状态。 数据库事务由一下部分组成： 1、一个或多个DML语句。DML：数据操纵语句，增删改查 2、一个DDL语句。DDL：数据定义语言，定义不同的数据段、数据库、表、列、索引等数据库对象 3、一个DCL语句。DCL：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句 数据库事务以第一个DML语句的执行作为开始，以COMMIT、ROLLBACK、DDL语句（自动提交）、用户会话正常结束、系统异常终止之一结束。 使用COMMIT和ROLLBACK语句的优点： 确保数据完整性、数据改变被提交之前预览、将逻辑上相关的操作分组。 使用 SAVEPOINT 语句在当前事务中创建保存点。 使用 ROLLBACK TO SAVEPOINT 语句回滚到创建的保存点。 示例： begin insert into test values('3','333'); savepoint insert_3; insert into test values('4','444'); rollback to insert_3; commit; end; 二、事务进程 自动提交在以下情况中执行: 1、DDL 语句。 2、DCL 语句。 3、不使用 COMMIT 或 ROLLBACK 语句提交或回滚，正常结束会话。 4、会话异常结束或系统异常会导致自动回滚。 三、数据状态 提交或回滚前的数据状态： 1、改变前的数据状态是可以恢复的 2、执行 DML 操作的用户可以通过 SELECT 语句查询之前的修正 3、其他用户不能看到当前用户所做的改变，直到当前用户结束事务。 4、DML语句所涉及到的行被锁定， 其他用户不能操作。 提交后的数据状态： 1、改变前的数据状态是可以恢复的 2、执行 DML 操作的用户可以通过 SELECT 语句查询之前的修正 3、其他用户不能看到当前用户所做的改变，直到当前用户结束事务。 4、DML语句所涉及到的行被锁定， 其他用户不能操作。 数据回滚后的状态： 1、数据改变被取消。 2、修改前的数据状态被恢复。 3、锁被释放。 约束 一、几种约束 约束是表级的强制规定 一共五种：NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK 二、使用约束时的几个注意事项 1、如果不指定约束名，Oracle自动按照SYS_Cn的格式指定约束名 2、创建和修改约束：建表的同时和建表之后 3、可以在表级和列级定义约束 4、可以通过数据字典视图查看约束 三、定义约束 语法格式： CREATE TABLE table_name (column datatype [DEFAULT expr] [column_constraint], ... [table_constraint][,...]); 示例： CREATE TABLE employees( employee_id NUMBER(6), first_name VARCHAR2(20), ... job_id VARCHAR2(10) NOT NULL, CONSTRAINT emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID)); 四、FOREIGN KEY 约束的关键字 1、FOREIGN KEY在表级指定子表的列 2、REFERENCES表示在父表中的列 3、ON DELETE CASCADE（级联删除）：当父表中的列被删除时，子表中相应的列也被删除 4、ON DELETE SET NULL（级联置空）：子表中相应的列置空 五、添加约束的语法 使用ALTER TABLE语句： 1、添加或删除约束，但是不能修改约束 2、有效化或无效化余数 3、添加NOT NULL约束时要用MODIFY语句 当定义或激活UNIQUE或PRIMARY KEY约束时系统会自动创建UNIQUE或PRIMARY KEY索引 添加约束示例： ALTER TABLE employees ADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id); 删除约束示例： ALTER TABLE employees DROP CONSTRAINT emp_manager_fk; 无效化约束示例： ALTER TABLE employees DISABLE CONSTRAINT emp_emp_id_pk; 激活约束示例： ALTER TABLE employees ENABLE CONSTRAINT emp_emp_id_pk; 六、查询约束 1、查询约束，查询数据字典USER_CONSTRAINTS 示例： SELECT constraint_name, constraint_type,search_condition FROM user_constraints WHERE table_name = 'EMPLOYEES'; 2、查询定义约束的列，查询数据字典USER_CONS_COLUMNS 示例： SELECT constraint_name, column_name FROM user_cons_columns WHERE table_name = 'EMPLOYEES'; 视图： 一、视图介绍 什么是视图： 1、视图是一种虚表 2、视图建立在已有表的基础上，视图赖以建立的这些表称之为基表 3、向视图提供数据内容的语句为SELECT语句，可以将视图理解为存储起来的SELECT语句 4、视图向用户提供基表数据的另一种表现形式 为什么使用视图： 1、控制数据访问 2、简化查询 3、避免重复访问相同的数据 二、创建、修改和删除视图 创建或修改视图语法： CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]]; 描述视图结构语法： describe view_name; 删除视图语法： drop view view_name; 简单的示例一： create or replace view empview as select e.employee_id emp_id,e.last_name name,d.department_name from employees e,departments d where e.department_id = d.department_id 略微复杂的示例二： create or replace view empview(name,minsal,maxsal,avgsal) as select d.department_name,min(e.salary),max(e.salary),avg(e.salary) from employees e,departments d where e.department_id = d.department_id group by d.department_name 三、视图中使用DML的规定 当视图定义中包含以下元素之一时不能使用delete： 1、组函数 2、GROUP BY子句 3、DISTINCT关键字 4、ROWNUM伪列 当视图定义中包含以下元素之一时不能使用update: 1、组函数 2、GROUP BY子句 3、DISTINCT 关键字 4、ROWNUM 伪列 5、列的定义为表达式 当视图定义中包含以下元素之一时不能使insert: 1、组函数 2、GROUP BY 子句 3、DISTINCT 关键字 4、ROWNUM 伪列 5、列的定义为表达式 6、表中非空的列在视图定义中未包括 四、屏蔽DML操作 可以使用WITH READ ONLY选项屏蔽对视图的DML操作，屏蔽之后，任何DML操作都会返回一个ORACLE server错误 示例： create or replace view empview(name,minsal,maxsal,avgsal) as select d.department_name,min(e.salary),max(e.salary),avg(e.salary) from employees e,departments d where e.department_id = d.department_id group by d.department_name with read only 五、Top-N分析 Top-N，根据某一规则进行排序，然后取其前N行数据。 查询最大的几个值的Top-N语法： SELECT [column_list], ROWNUM FROM (SELECT [column_list] FROM table ORDER BY Top-N_column) WHERE ROWNUM <= N; 注意：对 ROWNUM 只能使用 < 或 <=, 而用 =, >, >= 都将不能返回任何数据。 示例（查询工资最高的三名员工）： select rownum as rank, last_name, salary from (select last_name,salary from employees order by salary desc) where rownum<=3 其他数据库对象 一、序列 序列：可供多个用户来产生唯一数值的数据库对象 1、自动提供唯一的数值 2、共享对象 3、主要用于提供主键值 4、将序列值装入内存可以提高访问效率 定义序列： CREATE SEQUENCE sequence [INCREMENT BY n] --每次增长的数值 [START WITH n] --从哪个值开始 [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] --是否需要循环 [{CACHE n | NOCACHE}]; --是否缓存登录 创建序列示例： create sequence test_id increment by 10 start with 1 maxvalue 9999 nocache nocycle; 查询序列示例： select sequence_name,min_value,max_value,increment_by,last_number from user_sequences 在查询序列时，如果指定了nocache选项，则列last_number会显示序列中下一个有效的值 NEXTVAL和CURRVAL伪列： 1、NEXTVAL 返回序列中下一个有效的值，任何用户都可以引用 2、CURRVAL 中存放序列的当前值 3、NEXTVAL 应在 CURRVAL 之前指定 ，否则会报CURRVAL 尚未在此会话中定义的错误。 select test_id.currval from dual;返回序列当前值 select test_id.nextval from dual;返回序列下一个有效值 使用序列： 1、将序列值装入内存可提高访问效率 2、序列在下列情况下出现裂缝: a、回滚 b、系统异常 c、多个表同时使用同一序列 3、如果不将序列的值装入内存(NOCACHE), 可使用表 USER_SEQUENCES 查看序列当前的有效值 修改序列： alter sequence test_id increment by 20 maxvalue 99999 nocache nocycle 删除序列： drop sequence test_id; 二、索引 索引介绍： 1、一种独立于表的模式对象, 可以存储在与表不同的磁盘或表空间中 2、索引被删除或损坏, 不会对表产生影响, 其影响的只是查询的速度 3、索引一旦建立, Oracle 管理系统会对其进行自动维护, 而且由 Oracle 管理系统决定何时使用索引。用户不用在查询语句中指定使用哪个索引 4、在删除一个表时,所有基于该表的索引会自动被删除 5、通过指针加速 Oracle 服务器的查询速度 6、通过快速定位数据的方法，减少磁盘 I/O 创建索引： 1、自动创建：在定义PRIMARY KEY或UNIQUE约束后系统自动在相应的列上创建唯一索引 2、手动创建：用户可以在其他列上创建非唯一索引，以加速查询。 创建索引语法： CREATE INDEX index ON table(column[, column]...); 示例: create index emp_last_name_idx on employees(last_name) 什么时候创建索引： 1、列中数据值分布范围很广 2、列经常在WHERE子句或连接条件中出现 3、表经常被访问而且数据量很大，访问的数据大概占数据总量的2%到4% 什么时候不要创建索引： 1、表很小 2、列不经常作为连接条件或出现在WHERE子句中 3、查询的数据大于2%到4% 4、表经常更新 查询索引： 此处查询时ic.table_name = 'EMPLOYEES'区分大小写。 select ic.index_name, ic.column_name,ic.column_position col_pos,ix.uniqueness from user_indexes ix, user_ind_columns ic where ic.index_name = ix.index_name and ic.table_name = 'EMPLOYEES' 删除索引： 1、只有索引的拥有者或拥有DROP ANY INDEX 权限的用户才可以删除索引 2、删除操作是不可回滚的 语法： drop index index_name; 三、同义词-synonym 使用同义词访问相同的对象 1、方便访问其他用户的对象 2、缩短对象名字的长度 创建同义词语法： CREATE [PUBLIC] SYNONYM synonym FOR object; 示例： CREATE SYNONYM e for employees; 此时可以直接使用：select * from e;来查询employees表的数据 删除同义词： drop sysnonym e; D:\工作&学习&软件\学习\尚硅谷视频\Oracle\[尚硅谷]_宋红康_oracle_sql_plsql课件_章节练习_资料\[尚硅谷]_宋红康_oracle_sql_plsql课件_章节练习_资料\sql扩充知识 创建用户权限 一、创建用户 创建用户： CREATE USER user_name IDENTIFIED BY password; 示例：create user mfcuser identified by mfcuser; 赋予用户权限： grant create session,create table, create sequence,create view to mfcuser; 创建用户表空间 ALTER USER atguigu01 QUOTA UNLIMITED ON users 创建角色： CREATE ROLE manager; 为角色赋予权限： GRANT create table,create view TO manager; 将角色赋予用户： GRANT manager TO mfcuser; 修改密码： ALTER USER mfcuser IDENTIFIED BY mfc123; 删除用户： DROP USER mfcuser; 二、分配对象权限 分配表employees的查询权限： 只有对象所在的用户才能分配该对象的权限 grant select on employees to mfcuser; 分配表中各个列的更新权限 GRANT update ON scott.departments TO mfcuser 三、WITH GRANT OPTION和PUBLIC关键字 WITH GRANT OPTION 使用户同样具有分配权限的权利： GRANT select, insert ON departments TO scott WITH GRANT OPTION; 向数据库中所有用户分配权限： GRANT select ON alice.departments TO PUBLIC; 四、查询权限分配情况 数据字典视图 描述 ROLE_SYS_PRIVS 角色拥有的系统权限 ROLE_TAB_PRIVS 角色拥有的对象权限 USER_ROLE_PRIVS 用户拥有的角色 USER_TAB_PRIVS_MADE 用户分配的关于表对象权限 USER_TAB_PRIVS_RECD 用户拥有的关于表对象权限 USER_COL_PRIVS_MADE 用户分配的关于列的对象权限 USER_COL_PRIVS_RECD 用户拥有的关于列的对象权限 USER_SYS_PRIVS 用户拥有的系统权限 五、收回对象权限 1、使用 REVOKE 语句收回权限 2、使用 WITH GRANT OPTION 子句所分配的权限同样被收回 语法： REVOKE {privilege [, privilege...]|ALL} ON object FROM {user[, user...]|role|PUBLIC} [CASCADE CONSTRAINTS]; 示例： REVOKE select, insert ON departments FROM scott; SET 运算符 一、UNION操作符 UNION 操作符用于合并两个或多个 SELECT 语句的结果集，去掉重复数据 示例： SELECT employee_id, job_id FROM employees UNION SELECT employee_id, job_id FROM job_history; 二、UNION ALL操作符 UNION ALL 操作符用于合并两个或多个 SELECT 语句的结果集，不去掉重复数据 示例： SELECT employee_id, job_id FROM employees UNION ALL SELECT employee_id, job_id FROM job_history; 三、INTERSECT操作符 INTERSECT 操作符返回两个结果集的交集 示例： SELECT employee_id, job_id FROM employees INTERSECT SELECT employee_id, job_id FROM job_history 四、MINUS操作符 MINUS操作符：返回两个结果集的差集 示例： SELECT employee_id, job_id FROM employees MINUS SELECT employee_id, job_id FROM job_history 高级子查询： 一、多列子查询 -- 问题：查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id, manager_id, department_id -- 成对比较 SELECT employee_id, manager_id, department_id FROM employees WHERE (manager_id, department_id) IN (SELECT manager_id, department_id FROM employees WHERE employee_id IN (141,174)) AND employee_id NOT IN (141,174); -- 不成对比较 SELECT employee_id, manager_id, department_id FROM employees WHERE manager_id IN (SELECT manager_id FROM employees WHERE employee_id IN (174,141)) AND department_id IN (SELECT department_id FROM employees WHERE employee_id IN (174,141)) AND employee_id NOT IN(174,141); 二、在FROM子句中使用子查询 -- 问题：返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资 SELECT a.last_name, a.salary, a.department_id, b.salavg FROM employees a, (SELECT department_id, AVG(salary) salavg FROM employees GROUP BY department_id) b WHERE a.department_id = b.department_id AND a.salary > b.salavg; -- 问题：返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资 select last_name,department_id,salary, (select avg(salary)from employees e3 where e1.department_id = e3.department_id group by department_id) avg_salary from employees e1 where salary > (select avg(salary) from employees e2 where e1.department_id = e2.department_id group by department_id ) 三、单列子查询表达式 -- 问题：显示员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800的department_id相同，则location为’Canada’,其余则为’USA’。 SELECT employee_id, last_name, (CASE WHEN department_id = (SELECT department_id FROM departments WHERE location_id = 1800) THEN 'Canada' ELSE 'USA' END) location FROM employees; -- 问题：查询员工的employee_id,last_name,要求按照员工的department_name排序 SELECT employee_id, last_name FROM employees e ORDER BY (SELECT department_name FROM departments d WHERE e.department_id = d.department_id); 四、相关子查询 -- 问题：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id SELECT last_name, salary, department_id FROM employees outer WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = outer.department_id); -- 问题：若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name和其job_id SELECT e.employee_id, last_name,e.job_id FROM employees e WHERE 2 <= (SELECT COUNT(*) FROM job_history WHERE employee_id = e.employee_id); 五、Exists操作符 1、EXISTS 操作符检查在子查询中是否存在满足条件的行 2、如果在子查询中存在满足条件的行: a、不在子查询中继续查找 b、条件返回 TRUE 3、如果在子查询中不存在满足条件的行: a、条件返回 FALSE b、继续在子查询中查找 -- 问题：查询公司管理者的employee_id,last_name,job_id,department_id信息 SELECT employee_id, last_name, job_id, department_id FROM employees outer WHERE EXISTS ( SELECT 'X' FROM employees WHERE manager_id = outer.employee_id); -- 问题：查询departments表中，不存在于employees表中的部门的department_id和department_name SELECT department_id, department_name FROM departments d WHERE NOT EXISTS (SELECT 'X' FROM employees WHERE department_id = d.department_id); 六、相关更新 -- 先在employees表中添加一个字段 ALTER TABLE employees ADD(department_name VARCHAR2(14)); -- 相关更新示例 UPDATE employees e SET department_name = (SELECT department_name FROM departments d WHERE e.department_id = d.department_id); 相关删除 -- 问题：删除表employees中，其与emp_history表皆有的数据 DELETE FROM employees E WHERE employee_id = (SELECT employee_id FROM emp_history WHERE employee_id = E.employee_id); 七、WITH子句 1、使用 WITH 子句, 可以避免在 SELECT 语句中重复书写相同的语句块 2、WITH 子句将该子句中的语句块执行一次并存储到用户的临时表空间中 3、使用 WITH 子句可以提高查询效率 -- 问题：查询公司中各部门的总工资大于公司中各部门的平均总工资的部门信息 WITH dept_costs AS ( SELECT d.department_name, SUM(e.salary) AS dept_total FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY d.department_name), avg_cost AS ( SELECT SUM(dept_total)/COUNT(*) AS dept_avg FROM dept_costs) SELECT * FROM dept_costs s WHERE dept_total > (SELECT dept_avg FROM avg_cost) ORDER BY department_name;
人怂胆子小，手拿大刀跑。
