完整sql语句_sql查询工资范围的员工-CSDN博客

本文链接：https://blog.csdn.net/weixin_43524834/article/details/107311420

第三章限定和排序
/*

select 列名1， 列名2 ……                  ------定义查询数据所在的列
from 表名1， 表名2……                      -------定义查询数据所在的表

where 限定条件1 and/or 限定条件2…… ------定义查询数据的条件范围（行）
order by 列名1 asc/desc, 列名2 asc/desc -----定义查询结果的排序方式
*/

限定：限制某一查询所得的记录
排序：给查询结果按照一定顺序排列
1、**where**子句
-----where子句后面是一个完整的逻辑表达式，结果是一个逻辑判断，要么是真，要么是假
-----此处的逻辑表达式指的是一个比较运算，=，>,<,>=,<=,<>(!=)
----将某一列的数据作为条件，与手动输入的数据进行比较，找到满足条件的结果
-----表达式
-----1）手动输入数据作为表达式显示在查询结果中,数字类型可以直接写
-----2)日期累心也可以显示在查询结果中，但是日期需要特定的函数进行转换
----3）字符类型的表达式需要单引号引起来，并且区分大小写

SELECT ename, sal, 1000, 'String' FROM emp;

-----查找出30号部门的员工信息

SELECT * FROM emp
WHERE deptno = 30;

----查询出工资高于2000的员工信息

SELECT * FROM emp
WHERE sal >2000;

-----查找职位是manager的员工信息

SELECT * FROM emp
WHERE job = 'MANAGER';

-----手动输入字符串，要加单引号，并且区分大小写
-----where子句后面必须加完整的比较运算

SELECT * FROM emp
WHERE 12*sal;

-----查询出年薪大于30000的员工信息
----where 子句中不能出现列的别名

SELECT ename, job, 12*sal nianxin FROM emp
WHERE 12*sal >30000;

----where子句中作为条件的列可以不出现在select后面
----但是为了增强句子的可读性，最好将作为条件的列写在select后面

SELECT ename, job, deptno, sal FROM emp
WHERE sal>2000;

----where子句中可以比较两列数据，比较运算符两边的数据类型必须一致

SELECT * FROM emp
WHERE sal>comm;

2、特殊的比较运算符
—-in（value1，value2，……valueN)多列匹配
----查询出职位是salesman，manager，clerk的员工信息

SELECT * FROM emp
WHERE job IN ('SALESMAN', 'MANAGER', 'CLERK');

----between 最小值 and 最大值
----查询薪资在1500到3000范围内的员工信息

SELECT * FROM emp
WHERE sal BETWEEN 1500 AND 3000;

----like, 模糊匹配
----_表示任意单个字符
----%表示任意个任意字符

SELECT * FROM emp
WHERE ename LIKE 'S%';

-----姓名以S开头的

SELECT * FROM emp
WHERE ename LIKE '%S';

  -----姓名以S结尾的

SELECT * FROM emp
WHERE ename LIKE '%A%';

-----姓名含有A的

SELECT * FROM emp
WHERE ename LIKE '_A%';

-----第二位字母为A的名字

SELECT * FROM emp
WHERE ename LIKE '____';

  -----名字位数为4个字母的结果

----is null 匹配空值不能写成=null

SELECT * FROM emp
WHERE comm IS NULL;

3、逻辑运算符
----and, 与运算，所有条件都满足才能返回结果
-----查询部门编号为20且工资大于2000的员工信息

SELECT * FROM emp
WHERE deptno =20
AND sal >2000;

----or,或运算，满足其中任意一个条件就可以返回结果

SELECT * FROM emp
WHERE deptno =20
OR sal >2000;

----not, 非运算，返回不满足条件的结果
-----查询出薪资不大于2000的员工信息

SELECT * FROM emp
WHERE NOT sal >2000;

-----查出职位不是manager和salesman的员工信息

SELECT * FROM emp
WHERE job NOT IN ('MANAGER','SALESMAN');

4、运算优先级：数学运算>比较运算>not>and>or

SELECT * FROM emp
WHERE sal >1500
OR deptno = 20
AND job IN ('MANAGER','CLERK');

SELECT * FROM emp
WHERE (sal >1500
OR deptno = 20)
AND job IN ('MANAGER','CLERK');

5、order by 排序
----asc升序，默认可以不写
----desc降序
----order by永远写在查询语句的最后

SELECT * FROM emp
ORDER BY sal;

----order by中排序的列可以不出现在select后面
----但是order by后面接查询列可以增强句子的可读性

SELECT ename, job, deptno FROM emp
ORDER BY sal DESC;

----order by 后面可以包含别名

SELECT ename, job, 12*sal nianxin FROM emp
ORDER BY nianxin;

----多列排序

SELECT * FROM emp
ORDER BY deptno, sal;

SELECT * FROM emp
ORDER BY deptno, sal DESC;

SELECT * FROM emp
ORDER BY deptno,job, sal DESC;

【练习】
1.查询emp表，显示薪水大于等于1500，且工作类别是SALESMAN的雇员信息

SELECT * FROM emp
WHERE sal >=1500
AND job = 'SALESMAN';

2.查询emp表，显示年薪大于30000，工作类别不是MANAGER的雇员信息

SELECT emp.*, 12*emp.sal nianxin FROM emp
WHERE 12*sal >30000
AND job <> 'MANAGER';

3.查询emp表，显示薪水在1500到3000之间，工作类别以“M”开头的雇员信息

SELECT * FROM emp
WHERE sal BETWEEN 1500 AND 3000
AND job LIKE 'M%';

4.查询emp表，显示佣金为空并且部门号为20或30的雇员信息
(佣金=薪水SAL+津贴COMM)

SELECT ename, job, sal, comm, sal+comm yongjin, deptno FROM emp
WHERE sal +comm IS NULL
AND deptno IN (20,30);

5.查询emp表，显示佣金不为空或者部门号为20的雇员信息，
要求按照薪水降序排列 (佣金=薪水+津贴)

SELECT ename, job, sal, comm, sal+comm yongjin, deptno FROM emp
WHERE NOT (sal +comm) IS  NULL 
OR deptno = 20
ORDER BY sal DESC;

6.查询emp表，显示年薪大于30000工作类别不是MANAGER，且部门号不是10或40的雇员信息，要求按照雇员姓名进行排列。

SELECT ename, job, 12*sal nianxin, deptno FROM emp
WHERE 12*sal >30000
AND job <> 'MANAGER'
AND deptno NOT IN (10,40)
ORDER BY ename;

第四章单行函数
----函数：是一种预定义的代码块，它可以接收一个或多个参数，然后返回一个单独的值作为输出。
----单行函数：就是接收处理每一个记录都会返回一个结果
----多行函数：就是处理一组行或一个类但是只返回一个结果
----数字函数、字符函数、日期函数、转换函数、其他函数
1、数字函数
----round(m,n) 将数字m精确到小数点后n位

 SELECT 365, 12*30*24*60 FROM emp;

----手动输入的表达式与表中的数据无关，也可以用函数进行运算和查询
----和表中的数据的行数有关系，有多少行数据，表达式就会显示多少行

 SELECT ROUND(123.456,1) FROM emp;

----dual表，oracle中默认存在的一张空表，用作辅助查询或者运算。
----任何用户均可以读取，常用在没有目标表的select语句中

 SELECT * FROM dual;
SELECT ROUND(123.456,1) FROM dual;

----n可以不写，默认精确到个位

SELECT ROUND(123.456,1), ROUND(123.456,2),ROUND(123.456,0),ROUND(123.456) FROM dual;

----n可以为负数，如果n为负数则精确到小数点左边n位

SELECT ROUND(123.456,-1), ROUND(123.456,-2) FROM dual;

----trunc(m,n)将数字m截取到小数点后n位，其他规则和round函数一致

SELECT TRUNC(123.456,1), TRUNC(123.456,2) FROM dual;

----mod(m,n)返回m除以n的余数

SELECT MOD(23,5), MOD(45.3,6.7), MOD(-54,9) FROM dual;
SELECT MOD(empno,5), ename FROM emp;

2、字符函数
----upper(x) 将字符串x转换成大写
----lower(x) 将字符串x转换成小写

SELECT UPPER('abcdefg') FROM dual;
SELECT LOWER('ABCDEFG') FROM dual;
SELECT LOWER(ename) FROM emp;
SELECT * FROM emp
WHERE LOWER(job) ='manager';

----length(x) 返回字符串x的长度

SELECT LENGTH('abcdefg') FROM dual;
SELECT ename, LENGTH(ename) FROM emp;

SELECT * FROM emp
WHERE LENGTH(ename) =4;

----substr(x,m,n) 返回从字符串x第m位开始长度为n的子字符串

SELECT SUBSTR('abcdefg',3,3) FROM dual;
SELECT * FROM emp
WHERE SUBSTR(ename,1,1) = 'S' ;

----n可以不写，默认会截取后面所有的字符

SELECT ename, SUBSTR(ename,3) FROM emp；

----截取员工姓名的最后一个字母

SELECT ename, SUBSTR(ename,LENGTH(ename)) FROM emp;

----m可以为负数，如果m为负数则从右向左截取m个字符

SELECT ename, SUBSTR(ename,-2) FROM emp;

----lpad(x,m,n),将字符串x用字符n从左到右补充直到字符串长度为m

SELECT LPAD('bbb',10,'abc') FROM dual;

----rpad(x,m,n)将字符串x用字符n从右到左补充直到字符串长度为m

SELECT RPAD('bbb',10,'a') FROM dual;

----concat只能连接两个字符串 ||
SELECT CONCAT(ename,job) FROM emp;

3、日期函数
----日期对格式很敏感，一个完整的日期包括世纪、年、月、日、时、分、秒
----oracle中日期默认显示的格式是：DD-MON-RR
----sysdate

SELECT SYSDATE FROM emp;
SELECT * FROM emp;

----两个日期之间可以做减法，表示的是两个日期之间相差的天数

SELECT ename, hiredate, TRUNC((SYSDATE-hiredate)/365) gongling FROM emp;

----last_day(date):返回日期date所在的月份的最后一天

SELECT hiredate, LAST_DAY(hiredate) FROM emp;
SELECT LAST_DAY(SYSDATE) FROM emp;

----add_months(date,n) 给日期date加上n个月

SELECT ADD_MONTHS(SYSDATE,5) FROM dual;
SELECT ename, job, hiredate, ADD_MONTHS(hiredate,3) FROM emp;

4、转换函数
-----oracle中数据类型的转换分2中，一种是明确的数据类型转换，一种是隐含的数据类型转换
----明确的数据类型转换就是使用转换函数
----隐含的数据类型转换是指系统自动完成的转换，主要是数字和字符类型的转换
----数字可以直接看做是字符，具有数字特征的字符串可以默认转换为数字

SELECT * FROM emp
WHERE deptno ='10' ;

----to_char(date,'format')将日期date转换成格式为format的字符串
----YYYY表示四位年份数字，YEAR表示年份拼写
----MM表示两位月份数字，MONTH表示月份拼写
----DD表示两位天数，DAY表示星期的拼写
----可以自定义分隔符（：；.，-_ 空格）

SELECT SYSDATE, TO_CHAR(SYSDATE,'YYYYMMDAY') FROM dual;

----年月日的拼写和数字可以混合使用

SELECT SYSDATE, TO_CHAR(SYSDATE,'YYYY:MM:DAY') FROM dual;

----年月日的分隔符也可以混搭

SELECT hiredate, TO_CHAR(hiredate,'YEAR-MONTH DAY') FROM emp;

----年月日的顺序可以自定义
----fm可以去掉结果中没有意义的空格和0

SELECT hiredate, TO_CHAR(hiredate,'fmMONTH:DD:YYYY') FROM emp;

----年、月、日可以不完整

SELECT hiredate, TO_CHAR(hiredate,'YYYY') FROM emp;
SELECT hiredate, TO_CHAR(hiredate,'MONTH') FROM emp;

----查询1981年6月1日之前入职的员工信息

SELECT ename FROM emp
WHERE TO_CHAR(hiredate,'YYYYMMDD') <19810601;

----to_char(n,'format')将数字N转换成格式为format的字符串
----9代表一位数字
----0代表一位数字，如果位数不全会用0补齐
----.表示小数点，只能出现在中间
----，表示分隔符，只能出现在整数的中间部分

SELECT ename, sal, TO_CHAR(sal,'99999.99') FROM emp;
SELECT ename, sal, TO_CHAR(sal,'00000.00') FROM emp;

-----分隔符的位置可以自定义

SELECT ename, sal, TO_CHAR(sal,'9,9,9,99.99') FROM emp;

-----$表示货币单位，L表示本地货币

SELECT ename, sal, TO_CHAR(sal,'L9,9,9,99.99') FROM emp;

----to_date(x,‘format’)将字符串x按照format定义的形式转换成日期

SELECT TO_DATE('20180709','YYYYMMDD') FROM dual;

—年月日的顺序可以自定义

SELECT TO_DATE('20180713','YYYYDDMM') FROM dual;

-----如果年月日不完整，系统会自动补齐为一个完整的日期
-----如果不定义年份，则用当前系统时间所在的年份补齐

SELECT TO_DATE('0912','DDMM') FROM dual;

-----如果不定义月份，则用当前系统时间所在的月份补齐

SELECT TO_DATE('200612','YYYYDD') FROM dual;

-----如果不定义天数，则会用当月第一天补齐

SELECT TO_DATE('200612','YYYYMM') FROM dual;

-----to_date是手动输入日期的唯一方式

-----找出1981年6月1日之前入职的员工信息

SELECT * FROM emp
WHERE TO_DATE('19810601','YYYYMMDD')>hiredate;

5、其他函数
----nvl(x,y) 判断x的值，如果x不为空则返回x，如果x为空则返回y
----求出员工的年薪（年薪=12*(sal+comm))

SELECT ename, job, sal, comm, 12*(sal+NVL(comm,0)) nianxin FROM emp;

----如果mgr为空，则这个员工就是boss，查找员工信息

SELECT * FROM emp;

----NVL当中的x和y数据类型必须一致

SELECT ename, job, mgr, sal, NVL(TO_CHAR(mgr),'BOSS') FROM emp;
SELECT * FROM emp
WHERE NVL(mgr,0)=0;

----nvl2(x,y,z)判断x的值，如果x不为空则返回y，如果x为空则返回z
----y和z的数据类型必须一致
----判断mgr这一列，为空则赋予他boss，否则赋予它employee

SELECT ename, job, mgr, sal, NVL2(mgr,'EMPLOYEE','BOSS') zhiwei FROM emp;
/*decode(x, value1, result1,
                        value2, result2,
                        ……
                        valueN, resultN,
                    default
                    ）

   判断x的值，如果x=value1,则返回result1，
                      如果x=value2,则返回result2，
                      ……
                      如果x=valueN,则返回resultN，
                      如果以上条件都不满足，则返回default

*/
-----如果职位是clerk则加薪300，如果是manager则加薪200，如果是salesman则加薪100

SELECT empno, ename, sal, deptno,job,
DECODE(job,'CLERK',sal+300,
                      'MANAGER',sal+200,
                      'SALESMAN',sal+100,
                      sal) addsal 
FROM emp;

第五章多表查询
----查询员工信息和其所在部门的信息
1、笛卡尔积
-----集合A和集合B所有元素组成的有序对，叫做笛卡尔积
-----连接在where子句，一个表中的某一列和另一个表中的某一列的对应关系：表1.列名1=表名2.列名2
-----查询n张表，至少要有n-1个连接条件
2、多表查询的写作规范

SELECT ename, job, sal, deptno, dname, loc FROM emp, dept;

-----多表查询时，需要用表名限定列名，如果不用表名限定列名，重复的列就会报错
-----限定了列名可以提高查询效率
-----可以给表名起别名，用别名限定列名
-----多表查询，先写from，作为单独一行编写
-----如果多个表中有相同的列，起不同的别名加以区分

SELECT e.ename, e.job, e.sal, e.deptno edno, d.deptno ddno, d.dname, d.loc 
FROM emp e, dept d;

3、等值连接
-----两张表的数据关系是相等的关系
----比较常见的是主键和外键的对应关系
----先生成笛卡尔积，再在笛卡尔积中找出满足条件的结果

SELECT e.ename, e.job, e.sal, e.deptno edno, d.deptno ddno, d.dname, d.loc 
FROM emp e, dept d
WHERE e.deptno =d.deptno;

-----如果需要限定普通条件，使用and连接在where子句的后面
-----查询出工资高于2000的员工和其所在部门的信息

SELECT e.ename, e.job, e.sal, e.deptno edno, d.deptno ddno, d.dname, d.loc 
FROM emp e, dept d
WHERE e.deptno =d.deptno
AND e.sal >2000；

4、不等值连接
----查询员工的信息和其所在的工资等级

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

5、自连接
----当一张表中的外键参照了本张表的主键，即一类对象的内部有数据的关联关系时，如果要把这种联系体现出来
----就使用自连接
----将一张表看做是两张表，起不同的别名，再将主键和外键对应

SELECT * FROM emp;

-----查询出员工的名字和其上级的名字

SELECT  e.ename, m.ename manager
FROM emp e, emp m
WHERE m.empno = e.mgr;

6、外链接
----如果多表查询时，需要显示某张表中不满足连接条件的数据，使用外链接
----要显示哪张表的所有数据，将（+）放在连接条件的另一侧

----查询员工的信息和其所在部门的信息，没有员工的部门也显示

SELECT e.ename, e.job, e.sal, e.deptno edno, d.deptno ddno, d.dname, d.loc 
FROM emp e, dept d
WHERE e.deptno(+) =d.deptno;

----查询员工的名字和其上级的名字，没有上级的员工也显示

SELECT  e.ename, m.ename manager
FROM emp e, emp m
WHERE e.mgr=m.empno(+) ;

7、全外连接
----查询员工信息和其所在部门信息，没有员工的部门也显示，没有部门的员工也显示

SELECT e.*, d.dname, d.loc
FROM emp e FULL OUTER JOIN dept d
ON e.deptno = d.deptno;

第四章
【作业】
1.用一个sql语句完成在字符串”hello”左右各添加5个*，
使其最终返回hello(用2种方法实现)

SELECT RPAD(LPAD('hello',10,'*'),15,'*') FROM dual;
SELECT '*****'||'hello'||'*****' FROM dual;
SELECT CONCAT('*****',CONCAT('hello','*****')) exe FROM dual;

2.写一条sql语句返回”abcdefg”的后三位字符“efg”,并转换成大写

SELECT UPPER(SUBSTR('abcdefg',-3)) FROM dual;

3.查询emp表，显示删除掉第一个字符后的员工姓名
（如员工ALLEN，显示为LLEN）（2种方法实现）

SELECT ename, SUBSTR(ename, 2) FROM emp;
SELECT ename, SUBSTR(ename,1-LENGTH(ename)) FROM emp;
SELECT ename, SUBSTR(ename,-(LENGTH(ename)-1)) FROM emp;
SELECT ename, SUBSTR(ename,NVL2(ename,2,0)) FROM emp;

4.查询emp表，显示姓名中不包含字符’A’的员工信息

SELECT ename FROM emp
WHERE ename NOT LIKE '%A%' ;

5.查询emp表，使用员工姓名的第一个字符将员工姓名从左边补齐到长度为10

SELECT ename, LPAD(ename, 10, SUBSTR(ename,1,1)) FROM emp;

6.查询emp表，显示6月份入职的员工信息

SELECT * FROM emp
WHERE TO_CHAR(hiredate,'MM')=06;
SELECT TO_CHAR(hiredate, 'MONTH') FROM emp; 
SELECT * FROM emp
WHERE TO_CHAR(hiredate,'MONTH') LIKE '%JUNE%';

7.查询在某个月倒数第三天入职的员工信息

SELECT * FROM emp
WHERE hiredate = LAST_DAY(hiredate)-2;

8.写一条sql语句查询员工编号、员工姓名、工资、部门号，
要求当员工在10号部门时，显示’财务部’，20号部门时显示’研发部’，30号部门时显示’销售部’，
其余部门显示’未知部门’

SELECT ename, empno, sal, deptno,
DECODE(deptno, 10, 'Financ',
                              20, 'Research',
                              30, 'Sales',
                              'Unknown') deptname
FROM emp;

第五章
【作业】
1.查询EMP、DEPT表，
输出的列包含员工姓名、工资、部门编号、部门名称、部门地址.

SELECT e.ename, e.sal, e.deptno, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno;

2.查询工资等级为3/4/5级的员工姓名，工资，工资等级

SELECT e.ename, e.sal, s.grade
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal
AND s.grade IN (3,4,5);

3.显示职位是CLERK的员工姓名，工资，工资等级，部门名称

SELECT e.ename, e.sal, s.grade, d.dname
FROM emp e, salgrade s, dept d
WHERE e.deptno = d.deptno
AND e.sal BETWEEN s.losal AND s.hisal
AND e.job = 'CLERK';

4.查询emp表，显示员工姓名及其上级的姓名，没有上级的员工也需要显示

SELECT e.ename, m.ename 
FROM emp e, emp m
WHERE m.empno(+) = e.mgr;

5.列出EMP表中部门名称和这些部门的员工信息，同时列出那些没有员工的部门

SELECT d.dname, e.*
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno;

第六章多行函数（分组函数）
----多行函数：针对多行数据进行运算，返回一个结果，又叫分组函数
/*
select 列名1，列名2……列名n ------定义查询数据所在的列
from 表名1，表名2……表名n ------定义查询数据所在的表
where 限定条件1 and/or限定条件2 -------定义查询数据的条件范围
group by 列名1，列名2……列名n -------定义分组函数计算的分组方式
having 分组限定条件1 and /or 分组限定条件2 ------定义分组函数的限定条件
order by 列名1 asc/desc, 列名2 asc/desc…… ------定义查询结果的排序方式
*/
1、常见的多行函数
----sum()求和

SELECT SUM(sal) FROM emp;
SELECT SUM(ename) FROM emp;

----avg()求平均值

SELECT AVG(sal) FROM emp;
SELECT AVG(ename) FROM emp;

----count()统计行数（计数）

SELECT COUNT(sal) FROM emp;
SELECT COUNT(*) FROM emp;

----max()最大值min()最小值

SELECT MAX(sal), MIN(sal) FROM emp;
SELECT * FROM emp;
SELECT MAX(ename), MIN(ename) FROM emp;
SELECT MAX(hiredate), MIN(hiredate) FROM emp;

----sum和avg只能对数字进行运算
----max和min可以对任何数据类型进行运算
----多行函数会忽略空值

SELECT SUM(comm), AVG(comm), MAX(comm), MIN(comm), COUNT(comm) FROM emp;

----nvl(x,y)函数可以让空值参与运算

SELECT SUM(comm), AVG(comm), AVG(NVL(comm,0)),MAX(comm), MIN(comm), 
             COUNT(comm), COUNT(NVL(comm,0)) FROM emp;

----普通列不能直接和多行函数一起查询
SELECT ename, AVG(sal) FROM emp;
-----count()统计整张表的数据，但是可以通过where子句限定范围来统计
-----计算30号部门的员工人数

SELECT COUNT(*) FROM emp
WHERE deptno = 30;

2、group by 分组
----统计每个部门的平均工资

SELECT AVG(sal) FROM emp
GROUP BY deptno;

----统计每个职位的平均薪资

SELECT job, AVG(sal) FROM emp
GROUP BY job;

-----select 后面的普通列必须出现在group by中

SELECT job, AVG(sal) FROM emp
GROUP BY deptno;

-----分组的列应该是有大量重复的列，针对没有重复数据的列分组是没有意义

SELECT ename, MAX(sal) FROM emp
GROUP BY ename;

-----计算每个部门工资大于2000的人数

SELECT deptno, COUNT(ename) FROM emp
WHERE sal > 2000
GROUP BY deptno;

-----多列分组
-----计算出每个部门各个职位的最高工资、最低工资和人数

SELECT deptno, job, COUNT(deptno), MAX(sal), MIN(sal)
FROM emp
GROUP BY deptno, job
ORDER BY deptno;

3、having子句
------对分组函数的结果进行限定时，不能使用where子句，只能使用having子句
-----having子句必须写在group by后面
-----如果使用了group by可以不用having，但是如果使用了having则必须使用group by分组
-----计算平均工资高于2000的部门

SELECT deptno FROM emp
GROUP BY deptno
HAVING AVG(sal)>2000;

-----select后面的分组函数可以和having中的分组函数不一样
----计算部门人数大于等于5人的部门的平均工资

SELECT deptno, AVG(sal) FROM emp
GROUP BY deptno
HAVING COUNT(*)>=5;

4、分组函数是可以嵌套的
----求出各个部门平均薪资的最大值
----嵌套的分组函数不可以和普通列一起查询

SELECT MAX(AVG(sal)) FROM emp
GROUP BY deptno;

----求出各个部门最高薪资的平均值

SELECT AVG(MAX(sal)) FROM emp
GROUP BY deptno;

【练习】
1.查询每个职位的职位名称及平均工资，按平均工资升序排列.

SELECT job, AVG(sal)  
FROM emp
GROUP BY job
ORDER BY AVG(sal);

2.查询每个部门的各个职位的平均工资，并按部门编号升序、平均工资降序排序。

SELECT deptno, job, AVG(sal)
FROM emp
GROUP BY deptno, job
ORDER BY deptno, AVG(sal) DESC;

3.查询每个工资等级所对应的人数

SELECT s.grade, COUNT(e.ename)
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal
GROUP BY s.grade
ORDER BY s.grade;

4.查询EMP表，要求写一条sql语句统计出如下图所示结果

10号部门人数 20号部门人数 30号部门人数

    3                             5                        6

SELECT SUM(DECODE(deptno,10,1,0)) dept10,
             SUM(DECODE(deptno,20,1,0)) dept20,
             SUM(DECODE(deptno,30,1,0)) dept30
FROM emp;

SELECT COUNT
(DECODE(deptno,10,'dept10',
                              20,'dept20',
                              30,'dept30',
                              deptno)),
DECODE(deptno,10,'dept10',
                              20,'dept20',
                              30,'dept30',
                              deptno)
FROM emp
GROUP BY deptno;

SELECT 
DECODE(deptno,10,'dept10',
                              20,'dept20',
                              30,'dept30',
                              deptno),
COUNT(DECODE(deptno,10,'dept10',
                              20,'dept20',
                              30,'dept30',
                              deptno))
FROM emp
GROUP BY deptno;

SELECT A.S "10bumenrenshu", B.S "20bumenrenshu", C.S "30bumenrenshu"
  FROM (SELECT DEPTNO, COUNT(EMPNO) S
          FROM EMP
         WHERE DEPTNO = 10
         GROUP BY DEPTNO) A,
       (SELECT DEPTNO, COUNT(EMPNO) S
          FROM EMP
         WHERE DEPTNO = 20
         GROUP BY DEPTNO) B,
       (SELECT DEPTNO, COUNT(EMPNO) S
          FROM EMP
         WHERE DEPTNO = 30
         GROUP BY DEPTNO) C

select  (select count(empno) from emp 
                where deptno =10) as dept10, 
            (select count(empno) from emp 
                where deptno =20) as dept20,
            (select count(empno) from emp 
                where deptno =30) as dept30 from dual;

第七章子查询
-----查询出比Allen工资高的员工信息

SELECT sal FROM emp
WHERE ename ='ALLEN';

SELECT * FROM emp
WHERE sal>1600;

------当查询条件不是客观条件，而是表中的数据时，通常使用子查询
------将子查询嵌套在主查询的语句中，将子查询的结果作为主查询的输入条件
------子查询必须用括号括起来，代表运算的优先级
------为了语句结构的可读性更强，子查询单独占一行
------习惯上，子查询作为比较条件是，将子查询放在比较运算符的右边
------主查询中作为条件的列和子查询中的查询结果数据类型必须保持一致
-----查询出比Allen工资高的员工信息

SELECT * FROM emp
WHERE sal>
(SELECT sal FROM emp
WHERE ename ='ALLEN');

1、单行子查询
-----子查询返回的结果是一行数据
-----查询跟Allen部门和职位都相同的员工信息

SELECT * FROM emp
WHERE deptno =
(SELECT deptno FROM emp WHERE ename ='ALLEN')
AND
job=
(SELECT job FROM emp WHERE ename ='ALLEN');

-----查询Allen所在部门的名称（两种方式查询）
a)多表查询

SELECT d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.ename = 'ALLEN';

------先生成笛卡尔积，一共是14*4=56行数据，再查找ename是Allen的要查找56次
b)子查询

SELECT dname FROM dept
WHERE deptno =
(SELECT deptno FROM emp WHERE ename='ALLEN');

------先在emp表中查询，查询14次，再在dept表中查询4次，一共查询14+4=18次
-----如果查询结果来自多张表，只能用多表查询
-----如果查询的结果来自一张表，只是用到其他表的数据作为条件时，多表查询和子查询都可以实现
-----推荐使用子查询，因为子查询的效率通常高于多表查询

-----having子句中也可以出现子查询
-----找出部门平均工资高于所有员工平均工资的部门

SELECT deptno, AVG(sal) FROM emp
GROUP BY deptno
HAVING AVG(sal)>
(SELECT AVG(sal) FROM emp);

/SELECT * FROM emp
WHERE sal>
(SELECT AVG(sal) FROM emp
GROUP BY deptno);/

2、多行子查询
-----子查询返回的结果是多行结果
-----in列表匹配

SELECT dname FROM dept
WHERE deptno IN 
(SELECT deptno FROM emp
GROUP BY deptno
HAVING COUNT(*)>=5)

-----any 满足任意一个条件就能返回结果

SELECT * FROM emp
WHERE sal>ANY
(SELECT AVG(sal) FROM emp
GROUP BY deptno);

-----大于any表示大于最小值，小于any表示小于最大值

-----all,满足所有条件才能返回结果

SELECT * FROM emp
WHERE sal>ALL
(SELECT AVG(sal) FROM emp
GROUP BY deptno);

-----大于all表示大于最大值，小于all表示小于最小值

3、多列子查询
-----子查询返回的结果是多列数据
----主查询中的条件和子查询的结果中的每一列都要是一一对应的，数量、数据类型、顺序
----查询跟Allen部门和职位都相同的员工信息

SELECT * FROM emp
WHERE (deptno,job) IN
(SELECT deptno, job FROM emp WHERE ename ='ALLEN');

4、将子查询的结果看做是一张表再进行查询

SELECT ename, job, nianxin FROM
(SELECT ename, job, 12*sal nianxin, deptno FROM emp)
WHERE job ='SALESMAN'
AND nianxin >15000;

5、分页子查询
-----将一个查询结果中的一部分找出来，作为最终的结果
-----rownum，oracle会给每一个查询结果中的默认分配行号，从1开始
-----rownum是系统分配的伪列，
-----如果结果需要从中间开始开始分页，需要将rownum转换成实际的列，再进行查询

-----找出emp表中工资排名前三的员工信息

SELECT * FROM 
(SELECT * FROM emp
ORDER BY sal DESC)
WHERE ROWNUM<=3

----找出emp表中工资排名第五位到第八位的员工信息

SELECT * FROM 
(SELECT * FROM emp
ORDER BY sal DESC)
WHERE ROWNUM BETWEEN 5 AND 8;
SELECT * FROM 
(SELECT ROWNUM rm, e.* FROM 
(SELECT * FROM emp
ORDER BY sal DESC) e)
WHERE rm BETWEEN 5 AND 8;

第八章集合操作
-----将查询结果看做是集合，多个查询结果之间就可以进行集合操作
-----参与集合操作的查询语句所选择的列必须一致
-----集合操作的效率通常高于条件组合的查询语句
----交集 intersect

SELECT ename, sal, job, deptno FROM emp
WHERE deptno =20
INTERSECT
SELECT ename, sal, job, deptno FROM emp
WHERE sal >1500

----并集 union
-----返回两个集合取并操作后的结果
-----集合操作不包括两个集合中重复的行，即凡是重复记录只取其中一条

SELECT ename, sal, job, deptno FROM emp
WHERE deptno =20
UNION
SELECT ename, sal, job, deptno FROM emp
WHERE sal >1500

----并集 union all
----如果多个查询结果中有重复的数据，重复多少次显示多少次

SELECT ename, sal, job, deptno FROM emp
WHERE deptno =20
UNION ALL
SELECT ename, sal, job, deptno FROM emp
WHERE sal >1500

----差集 minus
-----用于取得两个结果的差集，它只会显示存在于第一个集合中，而不存在与第二个集合中的数据
-----第一个句子查询的数据结果都被第二个句子的查询结果包含，返回的结果就是空值

SELECT ename, sal, job, deptno FROM emp
WHERE deptno =20
MINUS
SELECT ename, sal, job, deptno FROM emp
WHERE sal >1500;

DML语句
使用DML语句可以执行：
1、插入新数据
2、修改已有数据
3、删除数据
事务：是DML语句的逻辑工作单元
1、新增 insert
/*

insert into 表名
values（value1，value2，……valueN）

insert into 表名（列名1，列名2……列名n）
values(value1，value2，……valueN)
*/

INSERT INTO dept
VALUES(60,'HR','CHONGQING');

COMMIT;----提交
ROLLBACK;----回滚（insert，update，delete，在commit之前使用rollback都可以恢复数据修改之前的状态）

SELECT * FROM dept;
----DML语句执行后会生成事务，事务需要提交才能永久生效，可以回滚撤销操作，但是要在提交之前才可以回滚
------未定义的列，默认填入空值，可以使用函数插入数据

INSERT INTO emp(empno,ename,job,hiredate,sal)
VALUES(8000,'LEISHEN','BOSS',TO_DATE('19990801','YYYYMMDD'),20000);
SELECT * FROM emp

;
----null表示的是空值

INSERT INTO dept
VALUES(70,'SUP',NULL);
SELECT * FROM dept;

-----insert …select …将查询的结果放入另外一张表，复制数据

SELECT * FROM bonus;
INSERT INTO bonus
SELECT ename, job, sal, comm FROM emp
WHERE comm IS NOT NULL;

2、update修改
/*
update 表名
set 列名=value
[where…]
*/
-----如果不写where子句，会将该列全部修改

UPDATE emp
SET sal =3000

UPDATE emp
SET sal =2000
WHERE ename = 'ALLEN';

-----部门编号为20的员工涨薪200
-----可以以表中原有数据为基础，再次进行修改

UPDATE emp
SET sal =sal +200
WHERE deptno =20

-----在set后面和where后面都可以接子查询
-----将Smith的部门和职位改成和Allen一样的

UPDATE emp
SET (deptno, job) =
(SELECT deptno, job FROM emp WHERE ename ='ALLEN')
WHERE ename ='SMITH';

-----将所有工资低于平均工资的员工的工资加500

UPDATE emp
SET sal = sal+500
WHERE sal<
(SELECT AVG(sal) FROM emp);
SELECT * FROM emp;

3、delete删除
/*
delete [from] 表名
[where…]
*/
-----如果不加where子句会删除所有数据

DELETE emp;
SELECT * FROM emp;

DELETE emp
WHERE ename ='ALLEN';

4、truncate快速删除数据
-----truncate属于DDL语句，直接生效不需要手动提交，不能回滚
-----站在表的层面上快速删除所有数据，不能定义范围
-----truncate table 表名
TRUNCATE TABLE emp;

第七章

使用子查询，找出哪个部门下没有员工

SELECT * FROM dept
WHERE deptno NOT IN
(SELECT DISTINCT deptno FROM emp);

使用子查询，列出领导是BLAKE的所有员工

SELECT * FROM emp
WHERE mgr =
(SELECT empno FROM emp
WHERE ename = 'BLAKE');

使用子查询，列出在New York工作的员工信息

SELECT * FROM emp
WHERE deptno =
(SELECT deptno FROM dept
WHERE loc ='NEW YORK');

使用子查询，找出那些工资低于平均工资的员工

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

使用子查询，找出那些工资低于任何一个部门的平均工资的员工

SELECT * FROM emp
WHERE sal < ANY
(SELECT AVG(sal) FROM emp GROUP BY deptno);

使用sql语句查出各个部门工资最高的员工的部门编号、员工姓名及其工资的信息

SELECT deptno, ename, sal FROM emp
WHERE (deptno,sal) IN
(SELECT deptno, MAX(sal) FROM emp GROUP BY

deptno);
7. 列出所有部门的相应信息和部门人数，没有员工的部门则部门人数显示为0
（a)多表查询

SELECT COUNT(*) FROM emp
WHERE deptno =40
SELECT d.*, COUNT(e.ename)
FROM emp e, dept d
WHERE e.deptno(+) =d.deptno
GROUP BY d.deptno, d.dname, d.loc
ORDER BY d.deptno;
select deptno,dname,loc,
decode(deptno,10,(select count(deptno) from emp where deptno=10 group by deptno),
              20,(select count(deptno) from emp where deptno=20 group by deptno),
              30,(select count(deptno) from emp where deptno=30 group by deptno),
              0) NNT
from dept;

(b)先构造一张符合查询要求的表，再和其他表进行多表查询

select d.*,nvl(c.pop,0) 
	from dept d,(select count(empno) as pop,deptno from emp group by deptno) c
		where  d.deptno=c.deptno(+);
select d.*,c.pop
	from dept d,(select count(empno) as pop,deptno from emp group by deptno) c
		where  d.deptno=c.deptno(+);

查询高于自己部门平均工资的员工名字,部门号,工资,平均工资（保留2位小数）

SELECT e.ename, e.deptno, e.sal, a.asal
FROM emp e,
(SELECT deptno, ROUND(AVG(sal),2) asal
FROM emp
GROUP BY deptno) a
WHERE e.deptno =a.deptno
AND e.sal > a.asal
ORDER BY e.deptno;

select ename,deptno,sal,
decode(deptno,10,(select round(avg(sal),2)  from emp where deptno=10 group by deptno), 
              20,(select round(avg(sal),2)  from emp where deptno=20 group by deptno),
              30,(select round(avg(sal),2)  from emp where deptno=30 group by deptno),
              null) average
from emp
where deptno=10 and 
sal>
(select round(avg(sal),2) average from emp where deptno=10 group by deptno)
or deptno=20 and
sal>
(select round(avg(sal),2) average from emp where deptno=20 group by deptno)
or deptno=30 and 
sal>
(select round(avg(sal),2) average from emp where deptno=30 group by deptno)
order by deptno;

查询入职日期排名后3位的员工信息

SELECT * FROM
(SELECT * FROM emp 
ORDER BY hiredate DESC)
WHERE ROWNUM <=3;

第八章
【练习】

在EMP表中，增加一名员工，员工信息参照现有员工构造.

INSERT INTO emp
VALUES()

员工SMITH调动到SALES部门，请编写SQL语句更新员工信息.

UPDATE emp
SET deptno =
(SELECT deptno FROM dept WHERE dname ='SALES')
WHERE ename = 'SMITH';

3.员工JAMES已经离职，请编写SQL语句更新数据库.

DELETE emp
WHERE ename ='JAMES';

第九章 DDL语句
-----对表的操作
/*
包括

create：创建表
alter：修改表
drop：删除表
truncate：快速删除表中所有数据

/
1、创建表 create
----创建表需要有创建表的权限和表空间
----查看是否有创建表的权限可以通过该语句查询select * from session_privs
----表名必须以字母开头，可以包含数字和符号
----表名不能用系统的关键字
/
create table 表名
（
列名1 数据类型，
列名2 数据类型，
……
列名n 数据类型
）；
/
/
number(m,n)定义最大为数位m，精确到小数点后n位的数字类型，m的最大值是38
n可以不写，如果不写就默认为整数
n是会占用m的位数，number(6,2)表示4位整数2位小数

char(n)定义长度为n的固定长度的字符类型，n的最大值是2000个字节
varchar2(n)定义最大长度为n的可变长度的字符类型，n的最大值是4000个字节

char(n)如果存储的字符长度不到n，会使用空格补齐到n个字节进行存储
varchar2(n)按照实际输入的字符长度进行存储

char浪费空间，查询效率高
varchar2节省空间，查询效率低

date 日期类型公元前4712年1月1日到公元后4712年12月31日
*/

CREATE TABLE test1
(
testid NUMBER(4),
testname VARCHAR2(20),
testage NUMBER(2),
testgender CHAR(1),
testdate DATE
);
SELECT * FROM test1;
INSERT INTO test1
VALUES(1111,'11QI',1,'M',SYSDATE);

-----创建表时可以给列定义默认值
-----如果使用insert插入数据时，某一列没有定义就会使用默认值
-----默认值和该列的数据类型要一致，默认值不能是其他列的列名
/*
create table 表名
（
列名1 数据类型 default 默认值，
列名2 数据类型 default 默认值，
……
列名n 数据类型 default 默认值
）；
*/

CREATE TABLE test2
(
testid NUMBER(4),
testname VARCHAR2(20),
testage NUMBER(2) DEFAULT 18,
testgender CHAR(1) DEFAULT 'M',
testdate DATE DEFAULT SYSDATE
);

SELECT * FROM test2;
INSERT INTO test2(testid, testname)
VALUES(001,'xiaoqiang');
INSERT INTO test2
VALUES(1000,'xiaoqiang',NULL,NULL,NULL);

-----create table ...as select …将查询的结果新建成一张表

CREATE TABLE deptno20
AS
SELECT * FROM emp
WHERE deptno=20;
SELECT * FROM deptno20;

2、修改表alter
-----一般情况下，推荐在初期就将表的结构设计好，尽量不在使用后再频繁的修改表

-----添加/删除列
----alter table 表名 add 列名数据类型；添加列
----alter table 表名 drop column 列名；删除列

ALTER TABLE test2 ADD testage NUMBER(2);
ALTER TABLE test2 DROP COLUMN testage;

-----修改列名/数据类型
-----alter table 表名 rename column 原列名 to 新列名；修改列名
-----alter table 表名 modify 列名数据类型；修改数据类型
-----（当该列有数据时，数据类型只能改大不能改小）

-----添加/修改/删除默认值
-----alter table 表名 modify 列名 default 默认值; 添加或修改默认值(在新添加数据的时候会体现出来）
-----alter table 表名 modify 列名 default null; 删除默认值（将默认值设置为空）
3、删除表drop
-----drop table 表名
DROP TABLE deptno20;

-----delete 属于DML语句，需要提交才能永久生效，可以回滚撤销操作，
-----可以删除表中的所有数据，也可以指定范围删除数据，保留表的结构

-----truncate属于DDL语句，直接生效不需要提交，也不能回滚。
-----快速删除表中所有数据，不能指定范围删除，保留表的结构

-----drop 属于DDL语句，直接生效不需要提交，也不能回滚。
-----删除的是数据和表的结构

4、重命名表rename
-----rename 原表名 to 新表名；
RENAME test2 TO testnew;
SELECT * FROM testnew;

5、约束
----约束是定义表中的数据应该遵循的规则或者满足的条件
----约束是建立在列上的，让某一列或者某几列数据之间有约束
----可以在创建表的同时创建约束，也可以在创建表之后创建约束
----约束分为表级约束和列级约束，方式不同但效果相同
----列级约束：列名数据类型【default 默认值】列级约束1，列级约束2……
-----表级约束：constraint 约束名称约束类型（列名）
----表级约束的名称可以自定义，列级约束的名称是由系统自动分配SYS_CN
----not null非空约束只能定义列级约束，不能定义表级约束
-----如果是多列之间的数据约束，只能定义表级约束

/*
主键约束 —— primary key
外键约束 ——foreign key
非空约束 ——not null
唯一约束 ——unique
检查约束 ——check
*/

CREATE TABLE test3
(
testid NUMBER(4) PRIMARY KEY,
testname VARCHAR2(20) NOT NULL,
testdept NUMBER(2) REFERENCES dept(deptno),

----外键的列级约束：references 表名（列名）
----外键的表级约束： constraint 约束名 foreign key（列名） references 表名（列名）
testphone NUMBER(11) UNIQUE,
testage NUMBER(2) CHECK(testage >=18),
testgender CHAR(1) CHECK(testgender IN(‘M’,‘F’)),
testdate DATE DEFAULT SYSDATE
);
-----确认约束的创建

SELECT * FROM user_constraints;
INSERT INTO test3
VALUES(1112,'LISI',20,13265768757,19,'M',SYSDATE);

SELECT * FROM test3;
------表级约束
----表级约束：constraint 约束名称约束类型（列名）

CREATE TABLE test4
(
sid NUMBER(2),
cid NUMBER(2),
score1 NUMBER(4,1),
score2 NUMBER(4,1),
CONSTRAINT pri_sid PRIMARY KEY(sid),
CONSTRAINT s_pk CHECK(score1>score2)
);

SELECT * FROM test4;
INSERT INTO test4
VALUES(01,01,100,99);

-----创建表之后再创建约束或者删除约束
-----ALTER TABLE 表名 MODIFY 列名 NOT NULL; 添加非空约束
-----ALTER TABLE 表名 ADD CONSTRAINT 约束名称约束类型（列名）；添加其他约束

-----ALTER TABLE 表名 MODIFY 列名 NULL; 删除非空约束
-----ALTER TABLE 表名 DROP CONSTRAINT 约束名称；删除其他类型约束

创建班级表（grade）和学生表（student），结构如下:
班级表：
列名数据类型
grade_id（主键） number(4)
grade_name（非空） varchar2(10)
grade_director（班主任，非空） varchar2(20)

学生表：
列名数据类型
st_id（主键） number(8)
st_name（非空） varchar2(14)
st_age（18岁以上） number(2)
st_phno（不能重复） number(11)
st_date（默认为当前系统时间） date
st_gender（非空，只能为男或女，默认为男） char(2)
st_grade（外键，关联班级表主键） number(4)

CREATE TABLE grade
(
grade_id NUMBER(4) PRIMARY KEY, 
grade_name VARCHAR2(10) NOT NULL, 
grade_director VARCHAR2(20) NOT NULL
);

SELECT * FROM grade;

CREATE TABLE student
(
st_id NUMBER(8) PRIMARY KEY, 
st_name VARCHAR2(14) NOT NULL, 
st_age NUMBER(2) CHECK (st_age>=18), 
st_phno NUMBER(11) UNIQUE, 
st_date DATE DEFAULT SYSDATE, 
st_gender CHAR(2) DEFAULT 'M' NOT NULL CHECK (st_gender IN ('M','F')), 
st_grade NUMBER(4) REFERENCES grade(grade_id)
);

SELECT * FROM student;

第十章视图
-----视图（view），就是存储在数据库中的一条查询语句，也是查询语句的快捷方式
-----视图可以帮助用户更快更方便的对数据进行访问
-----创建视图需要相应的权限
/*

create (or replace) view 视图名
as
select....
(with read only)

or replace：如果不写，遇到相同的视图会抱错；如果写or replace则会替换原有的视图
with read only:表示只读视图，不能修改视图中的数据
*/
1、区分权限
----部门经理只能访问自己部门的员工信息

CREATE OR REPLACE VIEW dept10
AS
SELECT * FROM emp
WHERE deptno =10;

-----用管理员身份登录给scott用户赋予创建视图的权限

GRANT CREATE VIEW TO scott;
SELECT * FROM dept10;

-----对视图中数据的修改等同于对源表中数据的修改

UPDATE dept10
SET sal =2450
WHERE empno =7782;

SELECT * FROM emp
WHERE empno =7782

;

CREATE VIEW v_7782
AS
SELECT * FROM emp
WHERE empno = 7782
WITH READ ONLY;

UPDATE v_7782
SET sal =2450
WHERE empno =7782;

2、简化查询语句

-----查询research 部门的人数
a)多表查询

SELECT COUNT(e.ename)
FROM emp e, dept d
WHERE e.deptno =d.deptno
AND d.dname ='RESEARCH';

b)子查询

SELECT COUNT(*) FROM emp
WHERE deptno=
(SELECT deptno FROM dept
WHERE dname ='RESEARCH');

CREATE VIEW emp_dept
AS
SELECT e.*,d.deptno dno, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno;

SELECT COUNT(*) FROM emp_dept WHERE dname ='RESEARCH';

-----删除视图
DROP VIEW 视图名称

第十一章索引
-----索引（index），是建立在某一列数据上的目录，可以提高查询效率
----索引分两部分：rowID和键值
----rowID是每行数据存储的物理位置，键值就是具体数据的取值
-----常见索引有两种：B树索引和位图索引

1、B树索引
-----B树索引是oracle中默认的索引类型，是一个树状结构的目录
-----create index 索引名称 on 表名（列名）

CREATE INDEX index_ename ON emp(ename);

----确认创建了索引

SELECT * FROM user_indexes;

-----索引创建好之后，当使用相关列查询数据时，索引会自动帮助查询语句提高查询效率
-----当表中的数据发生变化时，索引会自动更新，不需要手动维护
-----当数据大量变动后，索引会产生一些碎片文件，占用空间影响查询效率
-----可以定期对索引进行重建，清除碎片文件

2、位图索引
----B树索引适合某列中有大量不同的数据
----当某一列有大量重复数据时，就不适合B树索引了，应该使用位图索引
----位图索引不适合数据变化过于频繁的列
----create bitmap index 索引名称 on 表名（列名）；

CREATE BITMAP INDEX index_deptno ON emp(deptno);

删除索引
drop index 索引名称 on 表名

第十二章触发器
-----触发器（trigger），与某些特定操作绑定的操作，不会主动实施，但当条件满足时
-----会在绑定的操作触发时同步操作。
/*

create (or replace) trigger 触发器名称
before/after
insert/update/delete
(of列） on 表
（for each row)
(when(条件范围））
begin 
    触发的操作；
end;

before/after:定义触发的时间在操作之前还是操作之后
insert/update/delete:定义触发的操作
（of列） on 表：定义对哪张表的哪个列操作时触发
for each row ：定义是否每行数据都触发一次
when：定义对哪些行的数据操作时触发
写for each row的时候可以不写when，但是如果写了when则必须写for each row
when中间作为条件的列，必须用old和new来限定，表示修改之前的列还是修改之后的列
*/

1、记录操作
----如果对emp表中30号部门的员工职位进行修改
----则将修改的员工姓名、原职位、新职位和修改时间记录下来
/*
触发的时间：before
触发的操作：update
触发的对象：emp表job列
触发的范围：deptno=30
*/

CREATE TABLE record_dept30
(
ename VARCHAR2(20),
old_job VARCHAR2(20),
new_job VARCHAR2(20),
rec DATE
);

SELECT * FROM record_dept30;

CREATE TRIGGER tri1
BEFORE
UPDATE
OF job ON emp
FOR EACH ROW
WHEN (old.deptno=30)
BEGIN
INSERT INTO record_dept30
VALUES(:old.ename, :old.job, :new.job, SYSDATE);

----begin调用表中的数据时，要使用:old和:new来限定列
END;

UPDATE emp
SET job ='MANAGER'
WHERE deptno =30;

2、阻止操作
-----不允许对manager的工资进行修改
/*
触发时间：before
触发操作：update
触发的对象：emp表的sal
触发的范围：职位是manager
/
/

阻止操作使用raise_application_error(错位id,错误提示）方法强制提交一个错误
错误id：自定义但是有范围-20000~-29999之间的数字
错误提示：自定义文本
*/

CREATE OR REPLACE TRIGGER tri2
BEFORE
UPDATE
OF sal ON emp
FOR EACH ROW
WHEN (old.job='MANAGER')
BEGIN
 RAISE_APPLICATION_ERROR(-20001,'can not change the salary of manager');
END;

UPDATE emp
SET sal =5000
WHERE job ='MANAGER';

删除触发器
DROP TRIGGER 触发器名字；

第十三章用户和用户权限
1、创建用户
-----create user user_name identified by passwd;

CREATE USER eee IDENTIFIED BY 123456;

2、赋予权限
----grant privilege to user;

GRANT CREATE SESSION TO eee;

3、收回权限
----revoke privilege on table_name from user