Oracle 入门到精通
ORACLE入门到精通
第1章 Oracle数据库基础
1.1 数据库的几个重要概念
数据（data）：是指所有能输入到计算机中并被计算机程序处理的符号的总称，一个数据库可以管理多种数据。
数据库（database）：是指以一定方式存储在一起，能为多个用户共享，具有尽可能小的数据冗余特点，能与程序彼此独立的数据集合。一个数据库管理系统可以管理多个database。
数据库管理系统（DBMS）：是一种管理数据库的软件。在oracle数据库内，数据库仅仅是指文件集合，这些文件是用来存储和管理相关数据，包括数据文件。控制文件以及重做日志文件。
数据库管理的主要功能有：数据库定义功能（DDL），数据存储功能（DML），数据库运行管理，数据库的建立和维护功能。
1234567
1.2 数据库本地连接
1.2.1 免密登录
1. win+r快捷键进入cmd.exe,输入 sysplus / as sysdba,即conn[etc] 用户名/密码 as sysdba
注：在运行PLSQL Developer之前，需要启动计算机管理服务中的OracleOraDb11g_home1TNSListener和OracleServiceORCL。
其中：sys用户是权限最高用户，只能以’sysdba’或’sysoper’,不能用’normal’。 System用户拥有普通的sba，只能用normal。Scott用户是数据库中的一个示例用户。
如果报错：insufficient privileges
步骤：右键我的电脑管理计算机管理系统工具->本地用户和组->组>右边找找“ora_dba” ->右键之属性->添加->高级->立即查找->找电脑的名称->确定->应用。
12345
1.2.2 修改密码
把system的密码改为admin
代码：alter user system indentified by admin
12
1.2.3 登录
a)、登录数据库：sqlplus system/admin
b)、登录指定服务器的数据库
实例：sqlplus system/admin@localhost:1521/orcl
c)、常用数据库端口号：oracle-1521;mysql-3306;sql serwer-1433
即：sqlplus system/admin@localhost:1521/orcl
sqlplus system/admin@localhost:3306/orcl
sqlplus system/admin@localhost:1433/orcl
退出sql登录：exit
12345678
第2章 编写简单的查询语句
2.1 结构化查询语言分类
a).数据查询语言（DQL:Data Query Language）：语句主要包括SELECT，用于从表中检索数据。
b).数据操作语言（DML：Data Manipulation Language）：语句主要包括INSERT，UPDATE和DELETE，用于添加，修改和删除表中的行数据。
在oracle数据表中，删除一个表，那么建立在该表上的索引同时删除。
c).事务处理语言（TPL:Transaction Process Language）： 语句主要包括COMMIT和ROLLBACK，用于提交和回滚。
d).数据控制语言（DCL:Data Control Language）：语句主要包括GRANT和REVOKE，用于进行授权和收回权限。
e).数据定义语言（DDL:Data Definition Language）：语句主要包括CREATE、DROP、ALTER，用于定义、销毁、修改数据库对象。
执行一个DDL或DCL语句后，事务自动回滚，其中，系统崩溃，事务也会自动回滚。
1234567
2.2 数据查询语言DQL
2.2.1 基于select命令查询
一条完整的select命令查询语句是：
select 查询需要 from 数据表名
select子句表示所需检索的数据列，from子句表示检索的数据列来自哪个表。
Select语句不能单独存在，必须跟有from语句。所以当from后面没有表格时，如查询sysdate，可以需用虚表dual使语句完整。dual虚表使虚拟存在的表，在表空间实际上不存在，但可以直接使用。当我们不希望从任何表中读取数据，同时又想利用SQL引擎中的计算表达式的能力帮我们运算时，就可以使用dual表。
其中，select * from 表名表示查询所有的列，但在进行项目时不建议使用，使用指定列效率更高。
select 对表数据不对操作的数据添加锁。
123456
2.2.2 数据查询语句
2.2.2.1 Distinct去重复查询
在select字句中使用关键字distinct可消除重复行。
1
例：select distinct(deptno) from emp;
1
2.2.2.2 四则运算
在oracle中使用加、减、乘、除改变输出结果。
其中，四则运算符合乘法优于加减法；相同优先权的表达式按照从左至右的顺序依次计算；括弧可以提高优先权，并使表达式的描述更为清晰。
但空值不参与四则运算，任何包含空值的算术表达式运算后的结果都为空值NULL，需要用nvl函数进行处理。而空值是指一种无效的、未赋值、未知的或不可用的值，不同于零或者空格。
1234
如：
select ename,sal,2*(sal+3) from emp;
1
2.2.2.3 列别名
列别名是表示对已存在的列重新命名的显示标题，如果select语句中包含计算列，通常使用列别名来重新定义列标题。
使用列别名的方法有两种，M1：列名 列别名，M2：列名 as 列别名。
12
例1：select ename,(sal*6+sal*(1+0.2)*6) 年薪 from emp;
例2：select ename,(sal*6+sal*(1+0.2)*6) as 年薪 from emp;
12
以下三种情况列别名两侧需要添加双引号：
1）列别名中包含有空格
2）列别名中要求区分大小写
3）列别名中包含有特殊字符
1234
例1：select ename,sal "收 入" from emp;
例2：select ename,sal "SAl" from emp;
例3：select ename,sal "收入!!" from emp;
123
注：表别名只有M1：列名 列别名一种形式，不可用as。在查询时如果查询指定列时，再使用*查询其他列必须进行别名指定。如：select hiredate,e.* from emp e;
1
2.2.2.4 连接操作符与原义字符串
a).连接操作符是用于连接列与列、列和字符，以两个||为形式的用于创建字符表达式结果列。如：select ename || ' 的工资是 ' || sal from emp;
b).原义字符串是包含在select列表中的一个字符、一个数字或一个日期。而日期和字符字面值必须用单引号引起来，并且原义字符串都会在每个数据行输出中出现。
12
2.3 练习
--1. 员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的年薪所得（不考虑奖金部分,年薪的试用期6个月的月薪+转正后6个月的月薪).
select ename,(sal*6+sal*(1+0.2)*6) from emp;
--2. b).员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的所有收入（需考虑奖金部分)，要求显示格式为:XXX的第一年总收入为XXX。
select ename || ' 第一年的总收入为 ' || (sal*6+sal*(1+0.2)*6+nvl(comm,0)) from emp;
--3. 员工转正后,月薪上调20%，请查询出所有员工转正后的月薪
select ename || '转正后月薪为' || sal*(1+0.2) from emp;
--4. 员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的所有收入（需考虑奖金部分)，要求显示列标题为员工姓名，工资收入，奖金收入，总收入.
select ename 员工姓名,sal 工资收入, comm 奖金收入,
(sal*6+nvl(comm,0)*6) 总收入 from emp;
123456789
第3章 限制数据和对数据进行排序
3.1 使用where子句
限定数据行的原因是要根据某些条件，提高查询效率和缩短查询时间。使用where子句可以返回限定的数据行，其中，where子句紧跟from子句之后。
select 查询需要 from 数据表名
where condition(s);
condition(s)表示条件表达式，通常格式为：列名 比较操作符 要比较的值
1234
3.1.1 比较字符型和数值型数据
字符型数据作为要比较的值时，必须使用单引号引起来，并且字符型比较数据大小写敏感，即要区分大小写
1
--例：查询工作不是'SALESMAN'得员工信息。
Ans:select ename,job from emp where job <>'SALESMAN';
12
3.1.2 比较日期型数据
日期型数值作为被比较的值时，必须用单引号引起来，并且日期有固定日期表达式，默认格式：‘dd-mon-yy’，‘1-5月-1981’，并且用单引号。
1
--例1：查找入职日期大于1981年5月1日的数据。
Ans：select ename,hiredate from emp where hiredate>'1-5月-1981';
12
3.2 特殊比较运算符
3.2.1 between…and…
使用between…and..运算符判断要比较的值是否在某个范围内。在涉及端点问题时，要注意是否覆盖端点。
1
--例1: 查询入职日期在1981年1月1号至1981年6月30号的 员工信息。
Ans：select ename,hiredate from emp where hiredate>='1-1月-1981' and hiredate<'30-6月-1982';
12
3.2.2 in
使用in运算符判断要比较的值是否和集合列表中的任何一个值相等。
1
--例：查询经理编号为7902, 7566, 7788的员工姓名，经理编号。
Ans: select ename,mgr from emp where mgr in (7902,7566,7788);
12
3.2.3 like和escape
使用LIKE运算符判断要比较的值是否满足部分匹配，也叫模糊查询。模糊查询中两个通配符：% 代表零或任意更多的字符; _ 代表一个字符。
1
--例：查询所有员工姓名中倒数第二字字母为E的员工信息
Ans: select ename,sal from emp where ename like '%E_';
12
当%与_组合使用，可以使用escape标识符实现对“%”和 “_”的查找，即此时的“%”和 “_”含义是字符本身的字符。当查询的信息含有特殊符号时，需要进行转移。转移时先 使用escape声明转义符号（任意符号），再到使用转义符号对特殊符号进行转义。
常见需要转义的字符有：下划线_,百分号%
12
--例：询名称中包含'_'事务员工姓名，职位，上司编号。
Ans: select ename,job,mgr from emp where ename like '%@_%' escape '@';
12
在like查询时，如果没有声明下划线或百分号进行匹配，则相当于等值查询。
1
3.2.4 is null
使用 is null运算符来判断要比较的值是否为空值null。
1
--例：查询奖金为空的员工姓名，工资，奖金。
select ename,sal,comm from emp where comm is null;
12
3.3 逻辑运算符
3.3.1 and,or,not
a).and要求两个条件都为真,结果才为真.
1
--例：查询工资大于1000并且职位为CLERK的员工姓名，职位，工资。
select ename,job,sal from emp where sal>1000 and job='CLERK';
12
b). or是只需要两个条件中的一个为真,结果就返回真。
1
-例：查询工资大于1000或职位为CLERK的员工姓名，职位，工资。
select ename,job,sal from emp where sal>1000 or job='CLERK';
12
c).not是逻辑非，not运算符有几种运算场景，①集合运算：not in，②范围运算：not between and，③非空运算：is not null，④模糊匹配：not like。其中not null只能定义在列级定义上,称为列级约束。
1
3.3.2 逻辑运算符的优先级
多条件查询时需要注意运算符号的优先级别，可以使用小括号修改运算条件的优先级别，括号（）优于其他操作符。
优先级 运算分类 运算符举例
1 算术运算符 + ,- ,* ,/,
2 连接运算符 ||
3 比较运算符 =,<>,<,>,<=,>=
4 特殊比较运算符 Between…and…,in,like,is null
5 逻辑非 not
6 逻辑与 and
7 逻辑或 or
123456789
--例1：查询职位是SALESMAN并且工资大于2000，或者职位是MANAGER的员工姓名，职位，工资。
select ename,job,sal from emp where job='SALESMAN' or job='MANAGER' and sal>2000;
12
释：and的优先级大于or，所以先计算job='SALESMAN'和sal>1000的与内容
1
--例2：查询职位是SALESMAN或MANAGER，并且职位超过2000的员工姓名，职位，工资。
select ename,job,sal from emp where (job='SALESMAN' or job='MANAGER') and sal>2000;
12
释：括号（）的优先级大于and，所以先计算括号内(job='SALESMAN' or job='MANAGER')的内容，再计算and sal>1000的结果。结果为：
1
3.4 order by 子句
使用ORDER BY子句能对查询结果集进行排序，语法如下：
select 查询需要 from 数据表名
order by 列名|表达式|列别名|列序号 [asc|desc]
Order by 子句必须跟在select语句的最后，其中，可以按照列名，表达式，列别名，列序号进行排序，排序有降序和升序两种，asc代表升序，一般默认排序为升序；desc为降序。
1、数字升序排列小值在前，大值在后。即按照数字大小顺序由小到大排列。
2、日期升序排列相对较早的日期在前，较晚的日期在后。例如：’01-SEP-06’在’01-SEP-07’前。
3、字符升序排列按照字母由小到大的顺序排列。即由A-Z排列；中文升序按照字典顺序排列。
4、空值在升序排列中排在最后，在降序排列中排在最开始，空值在牌组中可认为是’最大值’。
5、参与排序的多列都可以指定升序或者降序，且ORDER BY子句中可以写没在SELECT列表中出现的列。
6、多列排序时，多列排序时，先按照第一列排序，当第一列数据有重复时，重复的数据按照第二列进行重新排序，由此类推。与此同时，可以针对每一个拍序列指定拍序列指定排序规则。
12345678910
--例：查询工资在2000-3000之间，部门不在10号的员工姓名，部门编号，工资，并按照部门升序，工资降序排序。
select ename,deptno,sal from emp where sal between 2000 and 3000 and deptno <>10 order by deptno,sal desc;
12
3.5 练习
1. 查询职位为SALESMAN的员工编号、职位、入职日期。
select empno,job,hiredate from emp where job='SALESMAN';
2. 查询1985年12月31日之前入职的员工姓名及入职日期。
select ename,hiredate from emp where hiredate<'31-12月-1985';
3. 查询部门编号不在10部门的员工姓名、部门编号。
select ename,deptno from emp where deptno<>10;
4. 查询入职日期在82年至85年的员工姓名，入职日期。
select ename,hiredate from emp where hiredate>='1-1月-1982' and hiredate<='31-12月-1985';
5. 查询月薪在3000到5000的员工姓名，月薪。
select ename,sal from emp where sal between 3000 and 5000;
6. 查询部门编号为10或者20的员工姓名，部门编号。
select ename,deptno from emp where deptno in (10,20);
7. 查询员工姓名以W开头的员工姓名。
select ename from emp where ename like 'W%';
8. 查询员工姓名倒数第2个字符为T的员工姓名。
select ename from emp where ename like '%T_';
9. 查询部门在10或者20，并且工资在3000到5000之间的员工姓名、部门、工资。
select ename,deptno,sal from emp where deptno in(10,20) and sal between 3000 and 5000;
10. 查询入职日期在81年，并且职位不是SALES开头的员工姓名、入职日期、职位。
M1: select ename,hiredate,job from emp where job not like 'SALES%' and hiredate>='1-1月-1981' and hiredate<='31-12月-1982';
M2: select ename,hiredate,job from emp where job not like 'SALES%' and to_char(hiredate,'yy')='81';
11. 查询职位为SALESMAN或MANAGER，部门编号为10或者20，姓名包含A的员工姓名、职位、部门编号。
select ename,job,deptno from emp where job in('SALESMAN','MANAGER') and deptno in (10,20) and ename like '%A%';
12. 查询入职日期在82年至83年之间，职位以SALES或者MAN开头的员工姓名，入职日期，职位，并按照入职日期降序排序。
select ename,hiredate,job from emp where hiredate>='1-1月-1981' and hiredate<='31-12月-1983' and job like 'SALES%' or job like 'MAN%' order by hiredate desc;
13. 查询入职时间在1982-7-9之后，并且不从事SALESMAN工作的员工姓名，入职时间，职位。
select ename,hiredate,job from emp where hiredate>='9-7月-1982';
14. 查询没有上级的员工(经理号为空)的员工姓名。
select ename from emp where mgr is null;
1234567891011121314151617181920212223242526272829
第4章 单行函数
单行函数特征：①单行函数对单行操作，②每行返回一个结果，③有可能返回值与原参数数据类型不一致，④单行函数可以写在SELECT、WHERE、ORDER BY子句中，⑤有些函数没有参数，有些函数包括一个或多个参数，⑥函数可以嵌套。
1
4.1 字符函数
字符函数主要指参数类型是字符型，但不同函数返回值可能是字符型或数值型。
1
4.1.1 Lower,upper,initcap函数
lower,upper，initcap是大小写转换函数，其中,
lower(列名|表达式)：将大写或大小写混合的字符转换成小写;
upper(列名|表达式):将小写或大小写混合的字符转换成大写;
initcap(列名|表达式):将每个单词的第一个字母转换成大写，其余的字母都转换成小写.
例：select lower('BeauTiFul')l,upper('WonDerFul') u,initcap('confiDent') i from dual;结果为：
12345
4.1.2 字符处理函数
4.1.2.1 concat,substr,length函数
concat(列名1|表达式1,列名2|表达式2)：连接两个值，等同于||。
substr(列名|表达式,n1,n2)：表示截取从第一个列名|表达式的参数中，从第n1位开始，长度为n2的子串。如果n1是负值，则表示从第一个参数的后面的第abs(n1)位开始，向右取长度为n2的子串。
length(列名|表达式) :计算列名|表达式的字符长度。
123
--例：写一个查询,用首字母大写，其它字母小写显示雇员的 ename，显示名字的长度，并给每列一个适当的标签，条件是满足所有雇员名字的开始字母是J、A 或 M 的雇员，并对查询结果按雇员的ename升序排序。（提示：使用initcap、length、substr）。
select initcap(ename) name,length(ename)lengthname from emp where substr(ename,1,1) in ('J','A','M') order by ename;
12
4.1.2.2 instr,lpad,rpad,trim,replace函数
instr(s1,s2,n1,n2)：表示查询在s1中，子串s2（一般认为s1包含s2）从第n1开始一直遍历，直到第n2次出现时的位置。n1,n1默认值为1。
lpad(s1,n1,s2)：返回s1被s2从左面填充到n1长度后的字符串，n1的长度为s1+s2的长度。
rpad(s1,n1,s2)：返回s1被s2从右面填充到n1长度后的字符串。
trim(字符串)：去除字符串头部或尾部的字符。
Replace(s1,s2,s3):把s1中的s2用s3替换。
函数 结果
123456
instr('beautiful','t') 5
lpad('good',8,'*') ****good
rpad('good',8,'*') good****
trim(' well ') well
replace('nonono','n','l') lololo
12345
4.2 数值函数
4.2.1 round,trunc,mod函数
round(列名|表达式,n)：将列或表达式的数值四舍五入到小数点后的第n位。如果n小 于0，则数值将在小数点前的第abs(n)位开始四舍五入取值。
trunc(列名|表达式,n)：将列或表达式的数值截取到小数点后的第n位。如果n小于0，则数值将在小数点前的第abs(n)前截取。
12
例：select round(44.223, -1),
trunc(44.223, -1),
round(24.456, -2),
trunc(24.456, -2)
from dual;
结果：44.2
44.2
24.46
24.45
123456789
round和trunc都用于求取数据精度，但round会四舍五入，而trunc不会 四舍五入。当精度大于0时，表示保留几位小时，精度等于0时，表示取整。其中，round只针对精度的后一位进行处理。
Mod(列名|表达式,n)：取列或表达式除以n后得到的余数。
12
例1：写一个查询，分别计算100.456 四舍五入到小数点后第2位，第1位，整数位的值。
Ans: select round(100.456,2),round(100.456,1),round(100.456,0) from dual;
结果为：100.46 100.5 100
例2：写一个查询，分别计算100.456 从小数点后第2位，第1位，整数位截断的值。
Ans：select trunc(100.456,2),trunc(100.456,1),trunc(100.456,0) from dual;
结果为：100.45 100.4 100
1234567
4.2.2 round和trunc计算日期
round和trunc会以天为单位进行取整，去除时间保留日期。其中
round 会根据时间产生进位，以上午和下午为分界，下去会进入会增加一天,默认的操作单位是DD。
12
例：select sysdate,round(sysdate),trunc(sysdate) from dual;
结果为：
12
① round 计算yyyy会以上半年和下半年为界，下半年会增加一年，返回增加年的第一月。
② round 计算mm会以上半月和下半月为界，下半月会增加一月，返回增加月的第一天。
③ round 计算dd会以上半天和下半天为界，下半天会增加一天。
④ round 计算day会以’周一至周三‘和’周四至周日‘为界，周一至周三’返回上周日，‘周四至周日’返回该周日。
1234
例1：select sysdate,round(sysdate,'yyyy'),trunc(sysdate,'yyyy') from dual;
结果1：
例2：select sysdate,round(sysdate,'mm'),trunc(sysdate,'mm') from dual;
结果2：
例3：select sysdate,round(sysdate,'dd'),trunc(sysdate,'mm') from dual;
结果3：
例4：select sysdate,round(sysdate,'day'),trunc(sysdate,'day') from dual;
结果4：
1234567891011
4.3 日期函数
Oracle是一种内部的数值形式存储日期的，即：世纪、年、月、日、小时、分、秒。
默认的日期格式为：dd-mon-rr
12
4.3.1 RR日期格式
用来判定按照DD-MON-RR格式给定的日期实际代表的日期是多少。所以RR的代表的日期格式判定标准如下表：
如果指定两位数年份是：
0-49 50-49
如果当前年份的最后两位数是： 0-49 返回的日期是当前世纪的日期 返回的日期是上一个世纪的日期
50-49 返回的日期是下一个世纪的日期 返回的日期是当前世纪的日期
12345
--例1：当前日期为2015年，指定日期格式为DD-MON-RR,指定日期为01-1月-95，该日期实际所代表的日期为？
select to_date('01-1月-95','DD-MON-RR') from dual;
释：如果当前日期为2015年，RR格式结果为1995/1/1，显示为上一个世纪
--例2：当前日期为1998年，指定日期格式为DD-MON-RR,指定日期为01-1月-01，该日期实际所代表的日期为？
select to_date('01-1月-01','DD-MON-RR') from dual;
释：如果当前日期为1998年，RR格式结果为2001/1/1，显示为下一世纪。
123456
4.3.1 常用日期函数
a) Sysdate：返回系统日期。
b) months_between(m1,m2)：返回两个日期类型数据之间间隔的自然月数，一般认为m2的日期早于m1。
c) add_months(日期,月份数): 返回指定日期加上相应的月数后的日期。
d) next_day(‘某一日期’,’指定日期’)：返回某一日期的下一个指定日期,指定日期可以为’星期一’等。
e) last_day：返回指定日期当月最后一天的日期。
f) round(date,’ftm’)将date按照fmt指定的格式进行四舍五入，fmt为可选项，如果没有指定fmt，则默认为dd，将date四舍五入为最近的天。其指定格式一般为为【year,month,day】。
格式码：世纪cc,年yy，月mm,日dd，小时hh24，分mi，秒ss。
g) trunc(ftm指定格式 from date)：将date按照fmt指定的格式进行截断，fmt为可选项，如果没有指定fmt，则默认为‘DD’，将date截取为最近的天。
h) extract：返回日期类型数据中的年份、月份或者日。
123456789
例1：months_between函数演示——查询所有员工服务的月数。
Ans: select ename,round(months_between(sysdate,hiredate)) months from emp;
例2：add_months 函数演示——查询82年后入职的员工转正日期，按照3个月试用期考虑
Ans: select ename,hiredate,add_months(hiredate,3) new_hiredate from emp where hiredate>='1-1月-1982';
例3：next_day 函数演示——返回在02-2月-06之后的下一个周以是什么日期。
Ans：select next_day('02-2月-06','星期一') from dual;
例4：last_day函数演示——返回06年2月2日所在月份的最后一天。
Ans：select last_day('2-2月-06') from dual;
例5：round函数演示——查询81年入职的员工姓名，入职日期按月四舍五入的日期。
M1：select ename,round(hiredate,'mm') from emp where hiredate>='1-1月-1981' and hiredate<='31-12月-1981';
M2：select ename,round(hiredate,'month') from emp where substr(hiredate,-2,2)='81';
M3：select ename,round(hiredate,'mm') from emp where to_char(hiredate,'yy')='81';
例6：trunc函数演示——查询81年入职的员工姓名，入职日期按月截断的日期。
Ans：select ename,trunc(hiredate,'mm') from emp where to_char(hiredate,'yy')='81';
例7：extract函数演示——查询编号是10的部门中所有员工入职月份。
Ans：select ename,extract(month from hiredate) from emp where deptno=10;
12345678910111213141516
4.4 转换函数
4.4.1 隐式数据类型转换
不同的数据类型之间关联，如果不使用显式转换函数，则它会根据以下规则对数据进行隐式转换：
1 对于insert和update操作，oracle会把插入值或者更新值隐式转换为字段的数据类型。
2 对于select语句，oracle会把字段的数据类型隐式转换为变量的数据类型。
3 当比较一个字符型和数值型的值时，oracle会把字符型的值隐式转换为数值型。
4 当比较字符型和日期型的数据时，oracle会把字符型转换为日期型。
5 用连接操作符(||)时，oracle会把非字符类型的数据转换为字符类型。
6 如果字符类型的数据和非字符类型的数据(如number、date、rowid等)作算术运算，则oracle会将字符类型的数据转换为合适的数据类型，这些数据类型可能是number、date、rowid等。
1234567
4.4.2 显示数据类型转换
显式数据类型转换通常是在字符类型、日期类型、数值类型之间进行显式转换。主要有3个显式转换函数：to_char，to_number，to_date。
4.4.2.1 to_char函数
to_char函数是把时间转换为字符串进行判断，可用于日期型和数值型。
① to_char用于日期型
用于日期型数据时的格式为to_chart(date,’ftm’)，可以包含任何有效的日期元素，使 用逗号将日期型数据与日期型格式模型分隔'。
注：to_char的日期格式是区分大小写的，即大小写敏感，所以必须用单引号引起来。但如果在注册oracle数据时设置了中文系统，则大小写不敏感。如若在中文系统下要大小写敏感，可在语句中添加’nls_date_language=english’把oracle中文系统改为英文系统。
123456
例：select to_char(sysdate,'DY','nls_date_language=english') from dual;
1
其日期型元素有：
yyyy 完整的年份数字表示
year 年份的英文表示
mm 用两位数来表示月份（03）
month 月份的全名（3月）
mon 缩写混合大小写月份名（3月）
day 星期几
dy 用3个英文字符缩写来表示星期几
dd 一个月里的日子
hh24 0-23小时数
mi 00-59分钟
ss 00-59秒
123456789101112
例1：select to_char(sysdate,'dd-month-year') from dual;
结果：20-7月 -twenty nineteen
例2：select to_char（sysdate,'dd-month-yyyy hh24-mi-ss') from dual;
结果：20-7月 -2019 18-25-52
1234
to_char用于日期型的元素，可用时间元素格式化日期的时间部分，也可通过使用双引号可以添加字符串。
用时间元素格式化时间的部分：
hh24:mi:ss am 18:33:24 pm
通过使用双引号添加字符串：
dd “of” month 12 of July
12345
例1：select to_char（sysdate,'dd-month-yyyy hh24-mi-ss AM') from dual;
例2：select to_char(sysdate,'dd "of" month') from dual;
12
② to_char用于数值型
通过在to_char使用以下形式可以把数值型数据转化成变长的字符串：
9 一位数字
0 显示前导零
$ 显示美元符号
L 显示本地货币符号
. 显示小数点
, 显示千位符
例：select to_char(sal,'L999999.99') from emp;
注意：进行数字类型到字符型转换时，格式中的宽度一定要超过实际列宽度，否则会显示为####。
12345678910
4.4.2.2 to_date函数
to_date是把字符串转换为时间进行判断，其格式为to_date(‘字符串’,’时间格式’)。
1
注意：to_date要转换的数据必须是可以转换为日期的字符，格式码的格式要和原数据的格式匹配。
例1：查询在1981年10月1日之后入职的员工姓名，入职日期，薪资。
M1：select ename,hiredate,sal from emp where hiredate>to_date('1-10月-1981','dd-month-yyyy');
M2：select ename,hiredate,sal from emp where to_char(hiredate,'dd-mm-yyyy')>'1-10-1981';
M3：select ename,hiredate,sal from emp where hiredate>'1-10月-1981';
1234
4.4.2.3 to_number函数
to_number是将一个字符串转换为数值型数据，注意to_number要转换的数据必须是有数字组成字符串，其相应的格式要与原数据中的格式相匹配。
1
例1：select to_number('56.5') from dual;
例2：select ename,hiredate from emp where to_number(to_char(hiredate,'yyyy')) between 1997 and 2008;
12
4.5 通用函数
4.5.1 与null相关函数
空值函数是与空值null相关的一些函数，完成对空值null的一些操作，从而对空值进行处理。主要包括：nvl，nvl2，nullif，coalesce函数。
a).nvl(expr1,expr2)：如果expr1是空值，则返回expr2。
b).nvl2(expr1,expr2,expr3)：如果expr1不是空值，返回expr2，否则返回expr3。
c).nullif(expr1,expr2)：比较两个表达式，如果相等，返回null，否则，返回第一个表达式。
d).coalesce (表达式1, 表达式2, ... 表达式n)函数是对NVL函数的扩展。COALESCE 函数的功能是返回第一个不为空的参数，参数个数不受限制。
12345
例1：select ename,sal+nvl(comm,0) totalsal from emp;
例2：select nvl2(null,'8','fd') from dual;
例3：select nullif(28,28),nullif(3,2) from dual;
例4：select coalesce(null,'5','f') from dual;
1234
4.5.2 case,decode条件处理函数
a).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
b).decode语法：DECODE(字段|表达式, 条件1,结果1[,条件2,结果2…，][,缺省值])
123456789
例1：select ename,deptno,
(case deptno
when 10 then '销售部'
when 20 then '管理部'
else '无' end)deptname
from emp;
例2：select ename,deptno,
decode(deptno,
10,'销售部',
20,'管理部',
'无')deptname
from emp;
123456789101112
补充：
函数的嵌套，单行行数可以嵌套于任何层，嵌套的函数是从最里层向最外层的顺序计算的。
例：select ename,hiredate from emp where to_number(to_char(hiredate, 'yyyy')) between 1982 and 1985;
123
4.6 练习
1. 查询员工姓名中中包含大写或小写字母A的员工姓名。
M1: select ename from emp where lower(ename) like '%a%';
M2: select ename from emp where upper(ename) like '%A%';
M3: select ename from emp where ename like '%a%' or ename like '%A%';
2. 询部门编号为10或20，入职日期在81年5月1日之后，并且姓名中包含大写字母A的员工姓名，员工姓名长度（提示，要求使用INSTR函数，不能使用like进行判断)。
Ans：select ename,hiredate,length(ename) lengthname from emp where deptno in (10,20) and hiredate>'1-5月-1981' and instr(ename,'A')>0;
3. 查询每一个职工的编号，姓名，工资，要求将查询到的数据按照一定的格式合并一个字符串。其中，前10位编号不足部分用*填充，左对齐；中间10位，姓名不足部分用*填充，左对齐；后10位工资不足部分用*填充右对齐。
Ans：select concat(lpad(deptno,10,'*'),lpad(ename,10,'*'))|| rpad(sal,10,'*') from emp;
4. 查询部门10,20的员工截止到2000年1月1日，工作了多少个月，入职的月份。(提示：使用months_between,extract)。
Ans：select ename,deptno,extract(month from hiredate) new_month,months_between('1-1月-2000',hiredate) num_month,hiredate from emp where deptno in (10,20);
5. 如果员工试用期6个月，查询职位不是MANAGER的员工姓名，入职日期，转正日期，入职日期后的第一个星期一,入职当月的最后一天日期。（提示：使用add_months,next_day,last_day)。
Ans：select ename,hiredate,add_months(hiredate,6) 转正日期,next_day(hiredate,'星期一'),last_day(hiredate) from emp where job<>'MANAGER';
6. 显示服务器系统当前时间，格式为2007-10-12 17:11:11(提示：使用to_char函数)。
Ans：select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
7. 显示ename、hiredate 和 雇员开始工作日是星期几，列标签DAY(提示：使用to_char函数)。
Ans：select ename,hiredate,to_char(hiredate,'day') from emp;
8. 查询员工姓名，工资，格式化的工资（￥999,999.99）(提示：使用to_char函数)。
Ans：select ename,sal,to_char(sal,'L999,999.99') from emp;
9. 把字符串2015-3月-18 13:13:13 转换成日期格式，并计算和系统当前时间间隔多少天。 (提示：使用to_date函数)。
Ans：select to_date('2015-3月-18 13:13:13','yyyy-mon-dd hh24:mi:ss')日期,round(sysdate-(to_date('2015-3月-18 13:13:13','yyyy-mon-dd hh24:mi:ss'))) 时间差 from dual;
10. 计算2000年1月1日到现在有多少月，多少周（四舍五入）。
Ans：select round(months_between(sysdate,'01-1月-2000')) 月数,round((sysdate-to_date('1-1月-2000','dd-mon-yyyy'))/7) 周数 from dual;
11. 使用trim函数将字符串‘hello’、‘ Hello ’、‘bllb’、‘ hello ’分别处理得到下列字符串ello、Hello、ll、hello。
Ans：
select trim('h' from 'hello') from dual;
select trim(' Hello ') from dual;
M1:select trim('b' from 'bllb') from dual;
M2:select replace('bllob','b','') from dual;
select trim(' hello ') from dual;
12. 将员工工资按如下格式显示：123,234.00 RMB。
Ans：select to_char(sal,'999,999.99') || 'RMB' from emp;
13. 查询员工的姓名及其经理编号，要求对于没有经理的显示“No Manager”字符串。
Ans：select ename,mgr,nvl(to_char(mgr),'No Manager') nmgr from emp;
14. 将员工的参加工作日期按如下格式显示：月份/年份。
Ans：select ename,to_char(hiredate,'mm/yy') from emp;
15. 在员工表中查询出员工的工资，并计算应交税款：如果工资小于1000,税率为0，如果工资大于等于1000并小于2000，税率为10％，如果工资大于等于2000并小于3000，税率为15％，如果工资大于等于3000，税率为20％。
M1：select ename,sal,
(case when sal<1000 then sal*0
when sal<2000 then sal*0.1
when sal<3000 then sal*0.15
else sal*0.2 end)税款
from emp;
16. 创建一个查询显示所有雇员的 ename和 sal。格式化sal为 15 个字符长度，用 $ 左填充，列标签 SALARY。
Ans：select ename,sal,lpad(sal,15,'$') SALARY from emp;
1234567891011121314151617181920212223242526272829303132333435363738394041424344
第5章 多表连接
5.1 多表连接概述
连接是在多个表之间通过一定的连接条件，使表之间发生关联，进而能从多个表之间获取数据。
语法为：
12
Select table1.column,table2.column
From table1,table2
Where table1.column1=table2.column2
123
其中，在where子句中书写连接条件，若果多个表中出现相同的列名，则需要使用表名作为来自该表的列名的前缀。
n个表相连时，至少需要n-1个连接条件。
笛卡尔积：是第一个表中的所有行和第二个表中的所有行都发生连接，其在连接条件被省略或无效的情况下产生。为了避免笛卡尔积的产生，通常需要在where子句中包含一个有效的条件。例：笛卡尔积=emp*dept。
注意：连接查询需要避免笛卡尔积，交叉连接会产生笛卡尔积，需要避免使用。
1234
在多表关联查询时，如果有列名重复，必须使用表前缀或表的别名进行制 定。通过使用表前缀可以提高性能，通过使用列的别名可以区分来自不同 表但是名字相同的列。
例1：select emp.ename,emp.deptno,dept.loc from emp,dept where emp.deptno=dept.deptno;
例:2：select e.ename,e.deptno,d.loc from emp e,dept d where e.deptno=d.deptno
12
多表连接的写法：
1.分析要查询的列来自哪些表，构成from子句
2.分析这些表之间的关联关系，如果表之间没有直接关联关系，而是通过另一个中间表关联，则也要在FROM子句中补充中间关联表;
3.接下来在WHERE子句中补充表之间的关联关系，通常N个表，至少要有N-1个关联关系;
4.分析是否还有其它限制条件，补充到WHERE子句的表关联关系之后，作为限制条件；
5.根据用户想要显示的信息，补充SELECT子句；
6.分析是否有排序要求，如果排序要求中还涉及到其它表，则也要进行第2步补充排序字段所在的表，并且添加表之间的关联关系。
1234567
例：查询每个员工的编号，姓名，工资，工资等级，所在工作城市，按照工资等级进行升序排序。
Ans：select e.empno,e.ename,e.sal,s.grade,d.loc
from emp e,salgrade s,dept d
where e.deptno=d.deptno and e.sal between losal and hisal
order by s.grade;
12345
5.2 Oracle自有连接方法
5.2.1 等值连接
① 两个表之间的连接
1
例1：查询员工姓名，部门编号和工作地点。
Ans：select emp.ename,emp.deptno,dept.loc from emp,dept where emp.deptno=dept.deptno;
在连接查询时，可使用and运算符增加其他查询条件。
例2：查询工作地点在NEW YORK 的姓名，部门编号，工作地点。
Ans：select emp.ename,emp.deptno,dept.loc from emp,dept where emp.deptno=dept.deptno and dept.loc='NEW YORK';
12345
② 多个表之间的连接
多个表之间的连接和两个表连接一样，在构造SQL语句时，需要多考虑表和表之间的关联条件。
例：查询出马化腾购买的所有商品信息。
Ans: select c.name,g.goodsid,g.goods_name, g.goods_price
from goods g, ord o, customer c
where g.goodsid = o.goodsid and o.custid = c.custid and c.name = '马化腾';
123456
5.2.2 不等值连接
不等值连接：是指表关联关系不相等或使用不等值方式关联。
1
例：查询员工的姓名，薪资，薪资级别。
M1：select e.ename,e.sal,s.grade from emp e ,salgrade s where e.sal between s.losal and s.hisal;
M2：select e.ename,e.sal,s.grade from emp e ,salgrade s where e.sal>=s.losal and e.sal<=s.hisal;
123
5.2.3 外部连接
在多表连接时，可以使用外部连接来查看哪些行，按照连接条件有没有被匹配上。外部连接的符号是 (+)，(+)在关联关系的右边则表示左连接；(+)在关联关系的左边，表示右连接。
外部连接就好像是为了符号(+)所在边的表增加一个“万能”的行，这个行全部由空值组成。它可以和另外一边的表中所不满足连接条件的行进行连接。
12
例1：查询所有雇员姓名，部门编号，部门名称，包括没有员工的部门也要显示出来。
Ans: select e.ename,d.deptno,d.dname from emp e,dept d
where e.deptno(+)=d.deptno;
123
5.2.4 自身连接
自身连接，也叫自连接，是一个表通过某种条件和本身进行连接的一种方式，就如同多个表连接一样。自连接,对连接的列，如果有同行数据就把同行数据连接。
例：查询每个员工的姓名和直接上级姓名。
Ans: select e.ename 员工,e.mgr,e.empno,m.ename 经理 from emp e,emp m
where e.mgr=m.empno(+);
结果：
12345
5.3 标准连接语法
5.3.1 外连接
外连接是以连接的一方作为参考，查询出所有的数据，再根据关联关系出查询出其他关联的数据，如果参考方有数据没有关联，则使用空行进行占位。外连接包括左连接，右连接和全连接。
注意：outer外连接是有方向的，方向必须是一致的，要么使用左连接，要么使用右连接，不能左右连接同时使用。
a).左外连接：以from子句中的左边表为基表，该表所有行数据按照连接条件无论是 否与右边能匹配上，都会被显示出来。
例：select e.ename,e.comm,e.deptno,d.loc from emp e
left outer join dept d
on e.deptno=d.deptno;
结果：
b).右外连接：以from子句中的右边表为基表，该表所有行数按照连接条件无论是否与左边表能匹配上，都会被显示出来。
例：select e.ename,e.comm,e.deptno,d.loc from emp e
right outer join dept d
on e.deptno=d.deptno;
结果：
c).全外连接返回两个表等值连接结果，以及两个表中所有等值连接失败的记录。
例：select e.ename,e.comm,e.deptno,d.loc from emp e
full outer join dept d
on e.deptno=d.deptno;
123456789101112131415161718
5.3.2 交叉连接
交叉连接会产生两个表的交叉乘积，和两个表之间的笛卡尔积是一样的。使用cross join子句完成。
例：select emp.empno,emp.ename,emp.sal,emp.deptno,dept.loc
from emp
cross join dept;
1234
5.3.3 自然连接,using,on子句
自然连接是对两个表之间相同名字和数字类型的列进行的等值连接。即自动根据相同的列名进行关联数据。输出数据时，相同数据只显示一列。使用natural join句来完成。
例：select * from emp e natural join dept d;
using子句可以指定用某个或某几个相同名字和数据类型的列作为连接条件。
例：select * from emp e join dept using(deptno);
使用using子句创建连接时，应注意以下几点：
1 如果有若干个列名称相同但数据类型不同，自然连接子句可以用using子句来替换，以指定产生等值连接的列。
2 如果有多于一个列都匹配的情况，使用using子句只能指定其中的一列。
3 using子句中的用到的列不能使用表名和别名作为前缀。
4 natural join子句和using子句是相互排斥的，不能同时使用。自然连接条件基本上是具有相同列名的表之间的等值连接；
5 如果要指定任意连接条件,或指定要连接的列，则可以使用on子句；
6 用on将连接条件和其它检索条件分隔开，其它检索条件写在where子句；
7 on子句可以提高代码的可读性。
123456789101112
例：select e.empno,e.ename,d.loc,m.ename from emp e
join dept d
on e.deptno=d.deptno
join emp m
on e.mgr=m.empno;
12345
5.4 练习
1. 写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金。
Ans：select e.ename,d.loc,e.comm from emp e,dept d where e.deptno=d.deptno and d.loc='CHICAGO' and e.comm is not null;
2. 写一个查询，显示所有姓名中含有A字符的员工姓名，工作地点。
Ans: select emp.ename,dept.deptno from emp,dept where emp.deptno=dept.deptno and emp.ename like '%A%';
3. 查询所有工作在NEW YORK和CHICAGO的员工姓名，员工编号，以及他们的经理姓名，经理编号。
Ans：select e.ename 员工,e.empno 员工编号,m.ename 经理,d.loc 工作地点,m.empno 经理编号 from emp e,emp m,dept d
where e.mgr=m.empno(+) and d.loc in ('NEW YORK','CHICAGO');
4. 查询所有员工编号，姓名，部门名称，包括没有部门的员工也要显示出来。
Ans：select e.empno,e.ename,d.dname from emp e,dept d
where e.deptno=d.deptno(+);
5. 查询出所有购买过康帅博的客户信息以及商品信息。
Ans：select c.custid,c.name, g.goods_name,g.goods_price from customer c,goods g,ord o
where g.goods_name='康帅博' and g.goodsid=o.goodsid and
o.custid=c.custid;
结果：
6. 查询所有的客户信息，如果客户购买过商品，把其购买过的商品信息也查询出来。
M1-左连接查询：
select c.custid,c.name,g.goodsid,g.goods_name,g.goods_price
from customer c
left outer join ord o on c.custid=o.custid
left outer join goods g on o.goodsid=g.goodsid;
M2-右连接查询：
select c.custid,c.name,g.goodsid,g.goods_name,g.goods_price
from goods g
right outer join ord o on g.goodsid=o.goodsid
right outer join customer c on o.custid=c.custid;
外连接查询时outer可以省略，即左连接，右连接都是外连接。若果省略了outer，left，right默认是内连接，内连接只查询有关联关系的数据。
M3-外连接查询：
select c.custid,c.name,g.goodsid,g.goods_name,g.goods_price
from goods g
right join ord o on g.goodsid=o.goodsid
right join customer c on o.custid=c.custid;
7. 查询出所有的商品信息，如果商品有销售记录，把购买商品的客户信息也查询出来。
M1：
select g.goodsid,g.goods_name,g.goods_price,c.name,c.custid
from goods g
left outer join ord o on g.goodsid=o.goodsid
left outer join customer c on o.custid=c.custid;
M2：
select g.goodsid,g.goods_name,g.goods_price,c.name,c.custid
from customer c
right outer join ord o on c.custid=o.custid
right outer join goods g on g.goodsid=o.goodsid;
1234567891011121314151617181920212223242526272829303132333435363738394041424344
5.5 创建表格
使用create table 创建表，创建表格的方法有三种。
a).创建表格方法1，先创建表格，后添加约束。
创建表格步骤filenewtable再分别设置general,columns,keys等设置约束条件。
1234
create table customer
(
cutid number,
name nvarchar2(20) not null
)
12345
b).创建表格方法2，在创建表格，定义列同时添加约束。
1
create table customer2(
custid number primary key,
name nvarchar2(30) not null);
123
C).创建表格方法3，在创建表格后，添加约束。
1
create table customer3(
custid number,
name nvarchar2(50) not null,
constraint pk_customer3 primary key(custid)
);
12345
表约束的分类：表级约束和行级约束
表约束的分类2：主键约束，唯一约束，外键约束，非空约束，检查约束
12
修改表的方法：
alter table customer add constraint pk_customer primary key (SUSTID)
例：创建客户表customer，商品表goods和订单表ord。
Ans：
create table customer
(
custid number,
name nvarchar2(20) not null
);
create table goods(
goodsid number,
goods_name nvarchar2(200),
goods_price number(7,2),--7个有效数字保留两位小数
constraint pk_item primary key(goodsid)
);
drop table ord;
create table ord(
ordid number,
custid number,
goodsid number,
constraint pk_ord primary key(ordid,custid),
constraint fk_ord_customer foreign key (custid ) references customer(custid),
constraint fk_ord_goods foreign key(goodsid) references goods(goodsid)
);
创建表格后对新建的表格更新数据。
例：往客户表customer，商品表goods和订单表ord插入5行记录。
insert into customer(custid,name) values(10000,'马化腾');
insert into customer(custid,name) values(10001,'马云');
insert into customer(custid,name) values(10002,'刘强东');
insert into customer(custid,name) values(10003,'霍金');
insert into customer(custid,name) values(10004,'巴菲特');
insert into goods(goodsid,goods_name,goods_price) values(1,'QQ会员',10.5);
insert into goods(goodsid,goods_name,goods_price) values(2,'绿钻',13.2);
insert into goods(goodsid,goods_name,goods_price) values(3,'康帅博',7.5);
insert into goods(goodsid,goods_name,goods_price) values(4,'二手电脑',22.5);
insert into goods(goodsid,goods_name,goods_price) values(5,'蓝钻',8.5);
insert into ord(custid,ordid,goodsid) values(10000,1,1);
insert into ord(custid,ordid,goodsid) values(10002,2,1);
insert into ord(custid,ordid,goodsid) values(10003,3,3);
insert into ord(custid,ordid,goodsid) values(10002,4,2);
insert into ord(custid,ordid,goodsid) values(10002,5,2);
commit;
--rollback;
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647
第6章 分组函数
6.1 分组函数概述
分组函数也叫统计函数，一般用于数据统计使用
分组函数是对数据行的集合进行操作并按组给出一个结果，这个结果可直接输出，或者用来做判断条件。
分组函数是对表中一组记录进行操作，每组只返回一个结果，即首先要对表记录进行分组，然后再进行操作汇总，每组返回一个结果，分组时可能是整个表分为一组， 也可能根据条件分成多组。其对数据集合处理的函数，可以处理多行数据。
分组查询必须设定分组条件，根据分组条件进行统计。组函数是对分组后 的集合进行计算，按分组条件分组后的数据应该有多条，使用分组后的函 数才有意义
例1：查询出每个部门的最高工资
Ans: select deptno,max(sal) from emp
group by deptno;
结果：
12345678
6.2 count,sum,avg,max,min分组函数
a). count函数的主要功能是返回满足条件的每组记录条数。
b). sum和avg函数分别返回每组的总和及平均值, 都是只能够对数值类型的列或表达式操作。
c). min和max函数主要是返回每组的最小值和最大值，并且可以用于任何数据类型。并且，min和max统计字符类型时，会跟进字符顺序进行统计。
例：
select min(ename),max(ename) from emp;
结果：
123456
1. 查询10号部门的最高工资，最低工资，平均工资。
select max(sal) 最高工资,min(sal) 最低工资,avg(sal) 平均工资 from emp where deptno=10;
2. 查询入职日期最早和最晚的日期
select max(hiredate),min(hiredate) from emp;
3. 查询入职日期在1981年6月后的员工的数量，及平均工资。
select count(empno) 员工数量,avg(sal) 平均工资 from emp where hiredate>=to_date('6月-1981','mon-yyyy');
4. 查询出公司每月的工资支出是多少
select sum(sal) 每月工资总和,substr(to_char(hiredate,'dd-mm-yyyy'),4,2) 月份 from emp
group by substr(to_char(hiredate,'dd-mm-yyyy'),4,2)
order by 月份;理解错题目，入职日期并不是发工资时间
select sum(sal) from emp;正确答案
5. 公司员工普提工资上调50%，查询工资上调前后，每月的工资支出。
select sum(sal) 上调前，sum(sal*(1+0.5)) 上调后 from emp;
d).其中，组函数中distinct会消除重复记录后再使用组函数。
例：
查询所有员工的部门数量
select count(deptno) from emp;错误
select count(distinct deptno) from emp;
结果：
12345678910111213141516171819
e). 分组函数中空值处理, 除了COUNT（*）之外，其它所有分组函数都会忽略列中的空
值，然后再进行计算。但在分组函数中使用NVL函数强制包含含有空值的记录。
例1:查询员工的平均奖金，没有奖金的视为0.
select avg(comm) from emp;
select avg(nvl(comm,0)) from emp;
结果：
123456
6.3 group by和having子句
通过 group by 子句可将表中满足where条件的记录按照指定的列划分成若干个小组,其中group by子句指定要分组的列。带group by的分组查询，执行顺序是先分组group by后查询 select。
例1：查询每个部门的编号，平均工资。
select deptno,avg(sal) from emp group by deptno;
例2：查询工资最高的员工的姓名，入职日期，及工资
select ename, hiredate, sal
from emp
where sal = (select max(sal) from emp);
分组列可以不在select子句中显示，但必须在group by 分组中。因为 group by子句中的关系要和查询的语句一一对应，在某些情况下只是为了 能让所查询的语句在select中显示。
即：在select列表中除了分组函数那些项，所有列都必须包含在group by子句中。而group by所指定的列并不是出现在select列表中。
123456789
例3：查询每个部门的部门编号，部门名称，部门人数，最高工资，最 低工资，工资总和，平均工资。
select d.deptno 部门编号,
d.dname 部门名称,
count(e.empno) 部门人数,--查询员工表中的相同部门下的员工数
max(e.sal) 最高工资,
min(e.sal) 最低工资,
sum(e.sal) 工资总和,
avg(e.sal) 平均工资
from emp e, dept d
where e.deptno = d.deptno
group by d.deptno, d.dname
结果：
123456789101112
在上述中，因为需要查询dname的结果值，在分组查询中，分组列要在group by子句中出现，所以需要在group by子句中添加dname的值。而 group by是根据deptno分组后，在根据deptno值相同的情况下通过dname 值分组。
例4：查询出每个部门的最高工资
Ans：select max(sal) from emp
group by deptno;
结果：
如果在查询中使用了组函数，任何不在组函数中的列或表达式都必须包含在GROUP BY子句中 。即在select子句中的非组函数列，都必须添加到group by分组中。
例1：查询入职日期在1982年2月后的员工信息，每个部门的平均工资，并按照部门进行分组。
select deptno, avg(sal)
from emp
where hiredate >= to_date('1982-2', 'yyyy-mm')
group by deptno;
例2：查询每个部门每个岗位的工资总和
select deptno,job,sum(sal) from emp group by deptno,job;
不能在where子句中限制组，可以通过having子句限制组。使用having子句限制组的：
记录已经分组
使用过组函数
与having子句匹配的结果才输出
例5：查询每个部门最高工资大于2900的部门编号，最高工资
select deptno, max(sal) from emp group by deptno having max(sal) > 2900;
重点：
SELECT语句执行过程：
1.通过FROM子句中找到需要查询的表；
2.通过WHERE子句进行非分组函数筛选判断；
3.通过GROUP BY子句完成分组操作；
4.通过HAVING子句完成组函数筛选判断；
5.通过SELECT子句选择显示的列或表达式及组函数；
6.通过ORDER BY子句进行排序操作。
即sql执行顺序为：from-->where-->group by-->having-->select--order by。
例6：按入职年份进行统计和排序，查询出每各年份最高工资和最低工资。
select max(sal), min(sal), to_char(hiredate,'yyyy') year
from emp
group by to_char(hiredate,'yyyy')
order by year;
12345678910111213141516171819202122232425262728293031323334353637
第7章 子查询
括号内的查询叫做子查询，也叫内部查询，先于主查询执行。从结构上看子查询是嵌套在其他查询语句中的。子串可以嵌套在：from ,where,having子句中。
子查询的结果被主查询（外部查询）使用
• expr operator包括比较运算符。
单行运算符：>、=、>=、<、<>、<=
多行运算符： in、any、all
其中，根据子查询返回的行和列数量可以分为三类：
单行子查询：返回一行一列数据
多行子查询：返回多行一列数据
多列子查询：返回一行或多行且多列数据
为子查询的在使用过程中要注意：－子查询要用括号括起来，–将子查询放在比较运算符的右边，–对于单行子查询要使用单行运算符，–对于多行子查询要使用多行运算符。
1234567891011
7.1 单行子查询
单行子查询只返回一行一列，使用单行运算符。
并且，在单个子句中，可以编写多个子查询。
123
例1：显示和雇员7369从事相同工作并且工资大于雇员7876的雇员的姓名和工作,雇员编号。
select ename, job,empno
from emp
where job = (select job from emp where empno = 7369)
and sal > (select sal from emp where empno = 7876);
例2：查询工资最低的员工姓名，岗位及工资
M1：select ename,job,sal from emp where sal=(select min(sal) from emp);
M2：select ename,job,sal from emp where sal in (select min(sal) from emp);
M3：select ename,job,sal from emp where sal=any(select min(sal) from emp);
子查询子句不仅可以嵌套在where中，也可以嵌套在having子句中。检查条件中是否有：最大值，最小值，平均值，数量，和值；考虑子查询编写到having字句中。
例：
select deptno, min(sal)
from emp
group by deptno
having min(sal) > (select min(sal) from emp where deptno = 20);
123456789101112131415
7.2 in,any,all多行子查询
子查询返回记录的条数 可以是一条或多条。和多行子查询进行比较时，需要使用多行操作符，多行操作符包括：in，any，all。
a).in操作符和以前介绍的功能一致，判断是否与子查询的任意一个返回值相同。
例1：查询是经理的员工姓名，工资
select ename,sal from emp where empno in(select mgr from emp);
释：mgr是经理的编号。
b). any表示和子查询的任意一行结果进行比较，有一个满足条件即可。
any一共有3中运算：>any满足最小值,<any满足最大值,=any满足其中之一，与in相同。
1234567
例1：查询是经理的员工姓名，工资
select ename,sal from emp where empno=any(select mgr from emp);
例2：查询部门编号不为10，且工资比10部门任意一名员工工资高的员工编号，姓名，职位，工资。
M1：
select deptno, ename, job, sal
from emp
where deptno <> 10
and sal > any (select sal from emp where deptno = 10);
M2：
select deptno, ename, job, sal
from emp
where deptno <> 10
and sal > (select min(sal) from emp where deptno = 10);
12345678910111213
c). all表示和子查询的所有行结果进行比较，每一行必须都满足条件。
all一共有3中运算：>all满足最大值，<all满足最小值，=all等于所有值，通常无意义。
12
例3：查询部门编号不为10，且工资比10部门所有员工工资高的员工编号，姓名，职位，工资。
M1：
select deptno, ename, job, sal
from emp
where deptno <> 10
and sal > all (select sal from emp where deptno = 10);
M2：
select deptno, ename, job, sal
from emp
where deptno <> 10
and sal > (select max(sal) from emp where deptno = 10);
1234567891011
7.3 in多列子查询
多列子查询可以在一个条件表达式内同时和子查询的多个列进行比较。多列子查询通常用IN操作符完成。
1
例1:查询出和1981年入职的任意一个员工的部门和职位完全相同员工姓名、部门、职位、入职日期,不包括1981年入职员工.
select ename,deptno,job,hiredate
from emp
where (deptno,job) in
(select deptno,job from emp where to_char(hiredate,'yyyy')='1981')
and to_char(hiredate,'yyyy')<>'1981';
例2：查询出和1981年入职的任意一个员工的部门或职位相同员工姓名、部门、职位、入职日期,不包括1981年入职员工。
select ename,deptno,job,hiredate
from emp
where (deptno in(select deptno from emp where to_char(hiredate,'yyyy')='1981')
or job in(select deptno job from emp where to_char(hiredate,'yyyy')='1981'))
and to_char(hiredate,'yyyy')<>'1981';
123456789101112
7.4 rownum伪行号
rownum是oracle数据库具有的一个特性，它针对每一个查询（包括子查询）都会生成一个rownum用于对该次查询的数据进行编号。每个rownum只针对当前select查询有效，可以使用别名进行显示。rownum在执行完from子句后就存在，可以在其他子句中使用。
对于rownum只能执行<、<=运算，不能执行>、>=或一个区间运算Between..And等，但rownum的别名支持。
rownum常用于分页查询。
rownum和order by一起使用时，因为rownum在记录输出时生成，而order by子句在最后执行，所以当两者一起使用时，需要注意rownum实际是已经被排了序的rownum。
1234
例1：
select rownum,e.* from emp e;--如果使用*查询时还查询别的列，需要用别名进行指定。
select rownum,emp.* from emp;--使用表名指定列。
例2：查询出工资收入最高的前5名员工信息。--先排序后筛选
select * from (select * from emp order by sal desc) where rownum<=5;
释：从sql执行顺序：fromwheregroup byhavingselectorder by中可知道，要通过子查询先对sal进行排序，然后再通过rownum进行筛选。
例3：查询出工资收入最高的前5名员工信息。
select * from(select * from emp order by sal) where rownum<=5;
注意：查询排行榜时可能遇到边界问题，即可能存在多个相同值。
例4：查询工资水平在前5名的员工姓名和工资
本例包含3个查询，共生成3个rownum。
select ename, sal
from emp
where sal in (select *
from (select sal from emp order by sal desc)
where rownum <= 5);
释：注意边界问题，工资水平前5个和工资排名前5个是不同意义的值，工资水平前5个可能存在多个相同的值。
1234567891011121314151617
7.5 分页查询
分页计算参数:第几页，分页大小，计算分页。页方法：rownum和rownum别名
1
rownum不支持>和>=判断，但rownum的别名支持。
例1：分页查询员工姓名，工资，入职日期，每页显示5条，分别查询第1,2,3,页数据，
用两种方法实现
方法一：
先在子查询中查询前N页，在二次查询第N页；
需要在子查询中查询rownum的别名，通过别名进行分页。优先使用这种方法，效率更高。
select *
from (select rownum rn, emp.* from emp where rownum <= 1 * 5)
where rn > (1 - 1) * 5;第1页
select *
from (select rownum rn, emp.* from emp where rownum <= 2 * 5)
where rn > (2 - 1) * 5;第2页
select *
from (select rownum rn, emp.* from emp where rownum <= 3 * 5)
where rn > (3 - 1) * 5;第3页
方法二：
现在子查询中查询出所有数据和所有数据的rownum，并使用别名表示。在二次子查询中通过别名进行分页控制。
select * from (select rownum rn,emp.* from emp)
where rn<=3*5 and rn>(3-1)*5;
select * from (select rownum rn,emp.* from emp)
where rn<=2*5 and rn>(2-1)*5;
select * from (select rownum rn,emp.* from emp)
where rn<=1*5 and rn>(1-1)*5;
例2：按照入职日期早晚进行排序，并分页查询员工姓名，工资，入职日期，每页显示5条，分别查询第1,2,3,页数据。
select *
from (select rownum rn, e.*
from (select * from emp order by hiredate) e
where rownum <= 1 * 5)
where rn > (1 - 1) * 5;
select *
from (select rownum rn, e.*
from (select * from emp order by hiredate) e
where rownum <= 2 * 5)
where rn > (2 - 1) * 5;
select *
from (select rownum rn, e.*
from (select * from emp order by hiredate) e
where rownum <= 3 * 5)
where rn > (3 - 1) * 5;
1234567891011121314151617181920212223242526272829303132333435363738
7.6 练习
1.查询入职日期最早的员工姓名，入职日期
select ename,hiredate from emp where hiredate=(select min(hiredate) from emp);
2.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称
select e.ename, e.sal, d.dname
from emp e, dept d
where e.deptno = d.deptno
and e.sal > (select sal from emp where ename = 'SMITH')
and d.loc = 'CHICAGO';
3.查询入职日期比20部门入职日期最早的员工还要早的员工姓名，入职日期
select ename,hiredate from emp
where hiredate>(select min(hiredate) from emp where deptno=20);
4.查询部门人数小于所有部门平均人数的的部门编号，部门名称，部门人数
select deptno,dname,count(*) from dept
group by deptno,dname
having count(*)<(select avg(count(*)) from emp group by deptno);
5.查询入职日期比10部门任意一个员工晚的员工姓名、入职日期，不包括10部门员工
M1：select ename, hiredate
from emp
where hiredate < any (select hiredate from emp)
and deptno <> 10;
M2：select ename,hiredate
from emp
where hiredate<(select max(hiredate) from emp)
and deptno<>10;
6.查询入职日期比10部门所有员工晚的员工姓名、入职日期，不包括10部门员工
M1：select ename,hiredate
from emp
where hiredate<all(select hiredate from emp)
and deptno<>10;
M2：select ename,hiredate
from emp
where hiredate<(select min(hiredate) from emp)
and deptno<>10;
7.查询职位和10部门任意一个员工职位相同的员工姓名，职位,不包括10部门员工
select ename, job
from emp
where job = any (select job from emp)
and deptno <> 10;
8.查询职位及经理和10部门任意一个员工职位及经理相同的员工姓名，职位，不包括10部门员工。
select ename,job,mgr from emp
where (job,mgr) in
(select job,mgr from emp where deptno=10)
and deptno<>10;
9.查询职位及经理和10部门任意一个员工职位或经理相同的员工姓名，职位，不包括10部门员工。
select ename,job,mgr from emp
where (job in (select job from emp where deptno=10)
or mgr in (select mgr from emp where deptno=10))
and deptno<>10;
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849
第8章 集合运算
集合运算：并，交差
并：uninon和union all
交：intersect
差：minus
union：合并两个集合并去重，多行null只保留一行。
12345
1. 查询工作在CHICAGO或1981年入职的员工信息。
select ename, sal
from emp e, dept d
where d.deptno = e.deptno
and d.loc = 'CHICAGO'
union
select ename,sal from emp where to_char(hiredate, 'yyyy') = '1981';
union all 不去重
select ename, sal
from emp e, dept d
where d.deptno = e.deptno
and d.loc = 'CHICAGO'
union all
select ename,sal from emp where to_char(hiredate, 'yyyy') = '1981';
intersect
2. 查询工作在CHICAGO且1981年入职的员工信息。
select ename, sal
from emp e, dept d
where d.deptno = e.deptno
and d.loc = 'CHICAGO'
intersect
select ename,sal from emp where to_char(hiredate, 'yyyy') = '1981';
minus注意减法的方向，相减运算不忽略空值。
查询工作在CHICAGO且不是在1981年入职的员工信息。
select ename, sal
from emp e, dept d
where d.deptno = e.deptno
and d.loc = 'CHICAGO'
minus
select ename,sal from emp where to_char(hiredate, 'yyyy') = '1981';
123456789101112131415161718192021222324252627282930
第9章 高级子查询
9.1 相关子查询
相关子查询中子查询不可以单独执行的查询，一般是先执行主查询，把主查询查询到的（条件）数据送到子查询中进行数据过滤。主查询每查询到一条记录，都需要执行一次子查询进行判断。
1
例1：查询比本部门平均工资高的员工编号，姓名，薪资，部门编号
方法1：相关子查询
select empno, ename, deptno, sal
from emp
outer where sal > (select avg(sal) from emp where deptno = outer.deptno);
方法2：嵌套子查询
嵌套子查询，子查询可以单独执行
先执行子查询，把子查询的查询结果跟主查询合并后再进行数据过滤（后执行主查询）；子查询只执行一次
select empno, ename, e.deptno, sal, avgsal
from emp e, (select deptno, avg(sal) avgsal from emp group by deptno) b
where e.deptno = b.deptno
and e.sal > avgsal;
例2：查询所有部门名称和人数
M1：
select d.dname 部门名称, count(nvl(e.empno,0)) 部门人数
from emp e, dept d
where d.deptno = e.deptno
group by d.deptno, d.dname;
M2：
select dname, (select count(empno) from emp where deptno = d.deptno)
from dept d;
例3：查询哪些员工是经理?
M1：
select * from emp where empno in (select mgr from emp);
M2：
select *
from emp e
where 0 < (select count(empno) from emp where e.empno = mgr);
12345678910111213141516171819202122232425262728
9.2 exists和not exists操作符
exists和not exists
执行相关查询时，可以使用exists或not exists是否存在查询结果，并根据判断选择 主查询的数据。可以试用exists进行判断，如果子查询有数据则会把主查询当前的数据 添加到显示列表（查询结果）中。
12
例1：查询比本部门平均工资高的员工编号，姓名，薪资，部门编号
M1：
select empno, ename, sal, deptno
from emp m
where exists
(select '1' from emp where deptno = m.deptno having m.sal > avg(sal));
M2：
select empno, ename, sal, deptno
from emp e
where sal > (select avg(sal) from emp where e.deptno = deptno);
M3：
select empno, ename, sal, e.deptno
from emp e, (select deptno, avg(sal) avgsal from emp group by deptno) b
where e.deptno = b.deptno
and sal > avgsal;
例2：查询比本部门平均工资低的员工编号，姓名，薪资，部门编号
如果相关子查询判断数据时，如果子查询不满足或没有数据返回时，则需要输出主查询的记录，可以使用not exists进行判断。
select empno, ename, sal, deptno
from emp m
where not exists
(select '1' from emp where deptno = m.deptno having m.sal > avg(sal));
123456789101112131415161718192021
9.3 练习
a).练习1
1
1.查询比所在职位平均工资高的员工姓名，职位。
注意是部门相同还是工作相同
方法1：
select ename, job
from emp
outer where sal > any (select avg(sal) from emp where job = outer.job);
方法2：
select ename, job
from emp e, (select deptno, avg(sal) avgsal from emp group by deptno) b
where e.deptno = b.deptno
and e.sal > b.avgsal;
2.查询工资为其部门最低工资的员工编号，姓名，工资。
方法1：相关子查询
select deptno, ename, sal
from emp
outer where sal = (select min(sal)
from emp
where deptno = outer.deptno
group by deptno);
b).方法2：
select deptno, ename, sal
from emp
where sal in (select min(sal) from emp group by deptno);
练习2
1.查询所有雇员编号，名字和部门名字。
select empno,ename,dname from emp e,dept d
where e.deptno=d.deptno;
2.查询哪些员工是经理？
M1：
select ename from emp
where empno in(select mgr from emp);
M2：
select m.ename from emp m
where 0<(select count(empno) from emp where m.empno=mgr);
3.查询哪些员工不是经理？
4.查询每个部门工资最低的两个员工编号，姓名，工资。
select deptno,ename,sal
from emp e
where(select count(*)
from emp m
where e.deptno=m.deptno
and m.sal<e.sal)<2
order by deptno,sal;
c).练习3
如下练习，用exists或not exists完成
1.列出至少有一个雇员的所有部门名称。
select dname
from dept d
where exists (select '1'
from emp
where deptno = d.deptno
group by deptno
having count(empno) > 0);
2.列出一个雇员都没有的所有部门名称。
select dname
from dept d
where not exists (select '1'
from emp
where deptno = d.deptno
group by deptno
having count(empno) > 0);
d).课后练习
1.查询薪水多于他所在部门平均薪水的雇员名字，部门号。
M1：
select ename, deptno
from emp m
where sal >
(select avg(sal) from emp where deptno = m.deptno group by deptno);
M2：
select ename,e.deptno,avgsal from emp e,
(select deptno,avg(sal) avgsal from emp group by deptno)b
where e.deptno=b.deptno and e.sal>avgsal;
2.查询员工姓名和直接上级的名字。
select e.ename 员工姓名, m.ename 上级姓名
from emp e, emp m
where e.mgr = m.empno;
3.查询每个部门工资最高的员工姓名，工资。
M1：
select ename,sal from emp
where sal in(select max(sal) from emp group by deptno);
M2:
select ename,sal from emp m
where sal=(select max(sal) from emp where deptno=m.deptno);
4.查询每个部门工资前两名高的员工姓名，工资。
解题思路：只有一个工资比自己工资水平高
select deptno,ename,sal
from emp e
where(select count(*)
from emp m
where e.deptno=m.deptno
and m.sal>e.sal)<2
order by deptno,sal;
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293
第10章 层次查询
层次查询必须指定start with，需要指定连接方向，通过连接方向控制向上查询或向下查询，可以使用level指定遍历的层级。
1
例1：
select empno, ename, mgr, job
from emp
start with empno = 7839
connect by prior empno=mgr;
使用level显示分级
例2：
select level,empno, ename, mgr, job
from emp
start with empno = 7839
connect by prior empno=mgr;
1234567891011
第11章 数据操作与事务控制
① 事务
也称工作单元，是由一个或多个sql语句所组成的操作序列，这些sql语句是完整的工作单元，要么全部执行成功，要么全部执行失败。在数据库中通过事务来保证数据的一致性。在共一个窗口中，第一次执行DML语句会打开数据库事务
数据库事务有4大特性：
a).原子性：在同一个事务中的操作不可以拆分，要么同时成功，要么同时失败。
b).隔离性：没有提交的事务中的更新操作对其他事务是隔离的。在当前事务客场，其他事务不可查。
c).一致性：数据开启前或事务结束后，每次查询出的数据都是一致的。
d).持久性：事务一旦提交，数据将被持久化到数据库系统中，发生意外时程序崩溃退出，数据仍然存在。
事务处理语言TPL：主要用来对事务的DML语句的操作结果进行确认或取消。
确认就是使DML操作生效，提交commit命令实现。以成功的方式结束事 务，组成事务的DML语句操作全部生效。取消就是使DML操作失效，提 交rollback命令实现。以失败的方式结束事务，组成事务的DML语句操作 全部被取消。所以commit和rollback是显示结束语句。
在开启事务后，执行DDL也会提交事务，在sqlplus中，正常退出程序也会提交事务。程序或系统的异常或崩溃时，会回滚事务。事务的结束不 可以使用TCL语句。
② 锁的概念
锁用来在多用户并发访问和操作数据库时,保证数据的一致性的一种机 制; 锁由Oracle自动管理，如一个DML操作，ORACLE默认的机制。是在 DML操作影响的行记录上自动加锁; 锁在被相关的操作申请并持有后，会 一直保持到事务的结束，事务结束后，锁才会被释放; 查询语句不会锁定 任何记录，如果在查询语句后面加FORUPDATE子句会锁定查询所影响的 行记录;
③ 提交(COMMIT)或回滚(ROLLBACK)前的数据状态
数据变化前的状态可以被恢复; 当前会话可以使用SELECT语句来验证 DML操作后的结果; 其它会话不能查看由当前用户的DML操作结果; 受影 响记录被锁定，也就是其它用户不能改变受影响记录中的数据;
④ 提交(COMMIT) 后的数据状态
在数据库中数据变化成为永久性的，先前的数据状态永久性的消失； 所有用户/会话都可以查询到提交COMMIT后的结果； 锁定的记录被释放，可以有效地被其他用户操作； 所有的存储节点被清除；
⑤ 回滚(ROLLBACK)后的数据状态
先前的数据状态被恢复; 锁定的记录被释放; 所有的存储节点被清除;
⑥ 事务中的锁机制
在事务中，如果修改了数据库的数据，则会事务结束前数据会被锁定。
死锁：在数据库事务中，如有两个或两个以上事务，进行多组事务更新 操作。这写操作可能进行互锁，造成死锁。在操作数据库时，或多线程编 程时要注意避免出现事务的死锁。
行级锁和表级锁
行级锁：只锁定单行数据，使该行数据不被其他事务修改
表级锁：常见的表级锁会锁表结构，在事务中如果修改了数据，在事务 结束前不可以修改表结构
⑦ 数据事务的隔离级别
read uncommitted 读未提交,read committed 读已提交,
repeatable read 重复读,serialiable 序列化（串行化）。
不通的数据库隔离级别可能造成：脏读，幻读，不可重复读
12345678910111213141516171819202122232425262728
11.1 插入数据
插入数据的结构为：
insert into 表名(列名1,列名2,…)
values (值1,值2,…)
列名列表和值列表必须在个数及数据类型上保持一致。其中，列名列表 部分可以省略，如果省略，默认包括该表的所有列。列名列表可以指定部 分非空的列，注意值列表必须和列名俩表对应。
字符和日期数据必须要用单引号括起来。
12345
例1：向部门表新增一个部门，部门编号为50，部门名称为HR，工作地点为SY。
insert into dept(deptno,dname,loc) values (50,'HR','SY');
select * from dept;
select * from emp;
例2：向员工表中新增一个员工，员工编号为8888，姓名为BOB，岗位为CLERK，经理为号7788，入职日期为1985-03-03，薪资3000，奖金和部门为空。
insert into emp
(empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (8888,'BOB','CLERK','7788',to_date('03-03-1985', 'dd-mm-yyyy'),
'3000',null,null);
123456789
11.2 修改数据
修改数据的结构为：
update 表名
set 限定修改行
where 限定修改列
1234
例1：修改部门20的员工信息，把82年之后入职的员工入职日期向后调整10天
select sysdate+10 from dual;
select * from emp;
update emp
set hiredate = hiredate + 10
where deptno = 20
and hiredate >to_date(1982,'yyyy');
例2：修改奖金为null的员工，奖金设置为0
update emp
set comm=0
where comm is null;
例3：修改工作地点在NEW YORK或CHICAGO的员工工资，工资增加500
M1：
update emp
set sal=sal+500
where ename in(select e.ename from emp e,dept d
where e.deptno=d.deptno and d.loc in ('NEW NEWYORK','CHICAGA'));
M2：
update emp e
set sal = sal + 500
where (select loc
from dept
where e.deptno = deptno) in ('NEW YORK', 'CHACAGO');
可以使用相关子查询来更新在一个表中的行，该表中的行基于另一个表的行。
例4：在emp表中增加一个列dname, 来存储部门名称.
添加数据
alter table 表名
add(添加列 数据类型);
alter table emp
add(dname varchar2(20));
例5：使用相关子查询更新dname列为正确的部门名称。
update emp e
set dname=(select dname from dept where deptno=e.deptno);
123456789101112131415161718192021222324252627282930313233
11.3 删除数据
删除数据的结构为：
delete from 表名
where 限定条件
123
例1:删除是员工编号是8888的员工信息
delete from emp
where empno=8888;
可以使用子查询基于另一个表删除本表的记录
例2：删除部门SALES的员工记录
delect from emp
where deptno=(select deptnp from dept where dname='SALES');
可以使用相关子查询删除那些已经存在另一张表的行
例3：删除没有员工的部门记录
delete from dept d
where not exists(select empno from emp where deptno=d.deptno);
练习：
1.删除经理编号为7566的员工记录
delete from emp
where deptno=7566;
2.删除工作在SY的员工记录
delete from emp
where deptno in (select deptno from dept where loc='SY');
3.删除工资大于所在部门平均工资的员工记录
delete from emp
where sal>(select avg(sal) from emp group by deptno);
123456789101112131415161718192021
第12章 创建和维护表
第13章 约束
第14章 视图
视图是数据库的一种对象，通常用于做数据的表现，可以把某个查询数据汇总在一 个视图中。可以从视图中查询数据，一般不执行dml（oracle有物理视图，可以对视图 数据进行修改；mysql等数据库不支持修改）。
应用场景：高铁航班表，飞机航班表，交易市场的交易表。
在oracle中，需要用户具有create view权限才能创建视图。授权时可以使用with admin option配置授予系统权限，此时student可以把create view权限授予给其他用户。授权时可以使用with grant option配置授予系统权限，此时student可以把insert，delete，update等对象权限授予给其他用户。
其中，with admin option配置不会级联，with grant option会级联，级联指收回权限时是否会级联收回。即A给权限B，B再把权限C。级联是A收回B权限的同时，B给C的权限也被收回；不会级联就是A收回B的权限，但收不回B已经授权给C的权限。
例1：
grant create view to student with admin option
语法：create or replace view 视图名称 as 子查询
表格会占用数据库的表空间，数据不会跟随源表的数据更新，而视图不会占据数据库的表空间，数据会根据源表的数据更新而更新。
123456789
14.1 视图创建
create or replace view claview as
select * from cla;
视图创建后一般只用于查询
select * from claview
1234
14.2 视图修改
create or replace view claview as
select claid，claname from cla;把查询修改掉就可以了
12
14.3 视图删除
drop view claview;
1
第15章 序列、索引、同义词
15.1 序列
序列是数据库中一种对象，用于生成一串不重复的编号，可以递增或递减。
最简单的序列结构：create sequence seq_cla;
一般一个序列只在一个表格中使用，即每个表格会使用一个序列。
123
例1：
create sequence seq_stu
minvalue 10000最小值
maxvalue 100000最大值
start with 10000起始值
increment by 1增量/补偿，可以输正数或负数，默认是1
cache 20;批量生成多少序列，使用完后再生成一批，用于序列的创建优化。
例2：
create sequence seq_subject
minvalue 1
maxvalue 100000
start with 100
increment by 1
cache 20;
1234567891011121314
序列的使用：
序列在使用，可以使用序列的两个伪列：currval,textval。currval取得序列的当前 值，nextval取得序列的下一个值。序列创建，currval是没有值，必须先使用nextval； 必须先使用nextval获取下一个值，currval才会拥有数值。
例：
select seq_subject.nextval from dual;
select seq_subject.currval from dual;
select * from cla;
123456
15.2 索引
索引是rowid数据库的一种对象，作用提高数据查询性能。一般针对查询 比较多的列进行创建。有大量重复性的数据的列，不适合创建索引。一个列独立创建索引，也可以同时跟其他列一起创建索引。一般在数据量比较少时，不建议创建索引，在数据量较大时，如果添加索引后，可以明显优化查询性能，这是有效索引。包含大量空值的列不适合创建索引，索引的数据量在2%-4%适合创建索引，索引的缺点会占用空间和降低DML的操作速度。
例：
create bitmap index IDX_MYCLASS_CLANAME on MYCLASS (claname);
123
15.3 同义词
create [public] synonym 同义词 for [schema.]对象名;把cla表共享给其他用户使用。
例：
create public synonym publiccla for cla;
123
第16章 用户、权限和角色
16.1 用户管理
16.1.1 新增用户
新增用户：
-tablespace表空间指数据存储的位置
create user student
identified by "123456"
default tablespace USERS
temporary tablespace TEMP;
123456
16.1.2 修改用户
修改用户：
modify the user
alter user STUDENT indentified by "123456";修改账户密码
alter user STUDENT account lock;锁定账户
alter user STUDENT account unlock;解锁账户
查询系统的所有用户，dba_users是系统表，保存了所有用户的账号。
select * from dba_users;
1234567
16.1.3 删除用户
drop user student;
1
16.2 权限管理
DCL：数据控制语言
oracle用户必须拥有session会话权限才能登录数据库。
oracle用户的权限分3种：对象权限，角色权限，系统权限。
常用的开发账号权限：连接权限，资源权限和无限表空间权限，这三种 权限已经包含session权限。
1234
例1：授予权限
grant connect to STUDENT;
grant resource to STUDENT;
grant unlimited tablespace to STUDENT;
例2：删除权限
revoke connect to STUDENT;
revoke resource to STUDENT;
1234567
16.3 数据对象管理
DDL 数据对象管理语句
1
16.3.1 创建表格对象
Create table 表格名称(列名 数据类型 约束，列名 数据类型 约束);
create table cla
(
clano number,
claid nvarchar2(20),
claname nvarchar2(20)
);
1234567
16.3.2 修改表格约束
① 修改表格，添加唯一约束
alter table cla
add constraint PK_CLA primary key (CLANO);
alter table cla
add constraint UN_CLA_CLAID unique (CLAID);
alter table cla
add constraint UN_CLA_CLANAME unique (CLANAME);
② 修改表格,删除主键约束
alter table cla
drop constraint PK_CLA cascade;
③ 修改表格，删除唯一约束
alter table 表格名称 drop constraint 约束名 cascade
cascade级联操作，可理解为强行删除。
alter table cla
drop constraint UN_CLA_CLAID cascade;
alter table cla
drop constraint UN_CLA_CLANAME cascade;
④ 修改数据类型
alter table 表格名称 modify 列名 新数据类型
alter table cla modify clano nvarchar(20);
⑤ 修改列名
alter table 表名 rename column 旧列名 to 新列名
注意：如果该列有约束，需要同步更新。
alter table CLA rename column claname to claname2;
⑥ 修改表格表名
alter table 旧表格名称 to 新表格名称
注意：重命名需要同步修改约束
alter table cla rename to cla2;
12345678910111213141516171819202122232425262728
16.3.3 删除表格
drop table 表格名称;
注意：删除主键表前需要删除外键表上的引用关系。
12
16.3.4 查询表格
select table_name from user_tables;
Create table
create table STU
(
sno number,
sid nvarchar2(20),
sname nvarchar2(20),
sex char(2),
indate date,
classid number
);
Create/Recreate primary, unique and foreign key constraints
alter table STU
add constraint PK_STU primary key (SNO);
alter table STU
add constraint UN_STU_SID unique (SID);
① 修改表格，添加外键约束
alter table STU
add constraint FK_STU_CLA foreign key (CLASSID)
references cla (CLANO);
Create/Recreate check constraints
② 修改表格，添加检车约束
alter table STU
add constraint CK_STU_SEX
check (sex in ('男','女'));
③ 修改表格，添加非空约束
Add/modify columns
alter table SUBJECT modify subname not null;
1234567891011121314151617181920212223242526272829
ddl补充
1.在定义列时同时定义约束
主键的特性：非空且唯一；一个表格只能一个主键，一个主键可以包含多个列。（联合主键/组合主键）；联合主键必须先定义列在定义主键。
create table T(
id number primary key 什么约束如果没有指定名称，则系统默认生成一个名称
);
create table T1(
id number constraint PK_T1 primary key，
(声明约束)constraint UN_T1_NAME--是对约束进行指定岳书铭，如果不指定可省略
NAME VARCHAR(20) constraint UN_T1_NAME unique not null,唯一和非空
sex char(2) check(sex in ('男','女')),
tid number references T(id)
);
reference 和 references 的区别
references可以在定义列的同时引用主键表，reference不可以.
create table T2(
id number not null,not null只能在定义列的后面,称为列级约束
name varchar(20),
sex char(2),
tid number,
constraint PK_T2 primary key(id),先定义列后编写的约束称为表级约束
constraint UN_T2_NAME unique(name),
constraint CK_T2_SEX check (sex in('男','女')),
在表格后使用外键时需要使用foreign key指定外键列
constraint FK_T1_T foreign key(tid) references T(id)
);
create table T3(
id number primary key,
name varchar2(20) unique not null,
sex char(2) check (sex in('男','女')),
tid number references T(id)
);
char和varchar varchar2 nvarchar2
create table T4(
id number,
sid number,
name nvarchar2(20),
name2 nvarchar2(20),
constraint PR_K3 primary key(id,sid),联合主键
constraint UN_T3_NAME_NAME2 unique (name,name2）联合唯一
);
123456789101112131415161718192021222324252627282930313233343536373839404142
扩展
declare
--声明变量的
--v_sal number(10) :=0;
--v_sal emp.sal%type;
Begin
--语句块
dbms_output.put_line(‘ta d gongz’ || v_sal);
exception
--抛异常
end;
12345678910
1、 使用%type：使用 %type 定义变量，动态的获取数据的声明类型 其类型已经定义的类型一致。例如： v_sal emp.sal%type == v_sal number(10);
优点：所引用的数据类型可以不必知道。
所应用的数据类型会跟随已定义的数据类型实时改变。
输出(打印)：dbms_output.put_line();
2、关系运算符
= 就是等于。
<>，!=，^=不等于
:= 赋值
=>关系号（游标）
3、连接 用 | |
4、赋值 一般要求赋值的变量要与select的列明要一一对应
1234567891011
5、（1）
--声明变量(也可以声明多个变量)，定义一个变量，打印出员工编号7369的变量名字。
declare
v_name emp.ename%type;
begin
select ename into v_name from emp where empno=7369;
--语句块
dbms_output.put_line('他的名字叫：'||v_name);
end;
12345678
（2）自定义记录类型（把多个变量装起来）
1
--定义一个记录类型 包含两个变量，将一个变量赋值为“java11班”，另一个变量为数值19，并打印出来。
declare
--定义一个记录类型
type emp_record is record(
v_name emp.ename%type,
v_sal emp.sal%type,
v_job emp.job%type);
--声明自定义记录类型的变量
v_emp_record emp_record;
begin
--通过 select ... into ... 语句为变量赋值
select ename, sal, job into v_emp_record
from emp
where empno= 7369;
dbms_output.put_line(v_emp_record.v_name || ', ' ||
v_emp_record.v_sal || ', ' || v_emp_record.v_job);
end;
123456789101112131415161718
6、流程控制
两种条件分支：
（1） if ..变量条件 then满足结果…elsif… then else…end if;
12
要求: 查询出 7369号 员工的工资, 若其工资大于或等于 3000 则打印 'sal >=3000';
若在1000 到3000 之间, 则打印 '1000<= sal < 3000'; 否则打印 'sal < 1000'
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=7369;
dbms_output.put_line(v_sal);
if v_sal>=3000 then dbms_output.put_line('sal >=3000');
elsif v_sal>=1000 then dbms_output.put_line('1000<= sal < 3000');
else dbms_output.put_line('sal < 1000');
end if;
end;
(2)使用 CASE ...条件的结果
WHEN ..结果. THEN ...
ELSE ...
END
完成上题
declare
v_sal emp.sal%type;
v_mgs varchar2(20);
begin
select sal into v_sal from emp where empno=7369;
dbms_output.put_line(v_sal);
v_mgs :=
case trunc(v_sal/1000)
when 0 then '<1000'
when 1 then '1000<= sal < 3000'--不能再dbms_
when 2 then '1000<= sal < 3000'
else '3000<= sal'
end;
dbms_output.put_line(v_mgs);
end;
12345678910111213141516171819202122232425262728293031323334
作业要求: 查询出 7788 号员工的工资, 若其值为5000, 则打印 'GRADE: A';
'3000', 打印 'GRADE B',
'2000', 打印 'GRADE C';
否则打印 'GRADE D'
1234
7、循环结构
三种循环体
（1）
（初始化也就是声明变量 相当于int i）
LOOP
（.循环体 ）
EXIT WHEN（结束循环条件）
（迭代条件）
...
END LOOP
123456789
--例题：使用循环语句打印 1 - 100.
declare
v_i number(10):=1;
begin
loop
dbms_output.put_line(v_i);
exit when v_i=100;
v_i := v_i+1;
end loop;
end;
1234567891011
（2）WHILE .循环条件.. LOOP
（循环体）
...
END LOOP
1234
--例题：使用循环语句打印 1 – 100（用while）.
declare
v_i number(10):=1;
begin
while v_i<=100 loop
dbms_output.put_line(v_i);
v_i := v_i+1;
end loop;
end;
12345678910
（3）for 变量 in 开始值..结束值 loop
（循环体）
..
end loop；（最方便）
1234
--例题：使用循环语句打印 1 – 100（用for）
begin
for i in 1..100 loop
dbms_output.put_line(i);
end loop;
end;
123456
（4）使用 goto
1
--例：打印1——100的自然数，当打印到50时，跳出循环，输出“打印结束”（三个循环随意一个）
begin
for i in 1..100 loop
dbms_output.put_line(i);
if (i=50) then goto ends;
end if;
end loop;
<<ends>>
dbms_output.put_line('打印结束');
end;
12345678910
8、游标：游标的作用就是用于临时存储从数据库中提取的数据块，它返回多行多列，
游标是一个指向上下文的句柄（handle）或指针。
显示游标的四个步骤：
（1）、定义游标 cursor 游标名 is sql语句（你要在哪里游）
（2）、打开游标 open 游标名（不能重复打开一个游标）
(3)、提取游标数据 fetch 游标名 into 定义的变量
(4)、关闭游标 close
For循环可以节省2.3.4条。
1234567
--例题：要求: 打印出 20 部门的所有的员工的工资:sal: xxx
declare
cursor sal_cur is select sal from emp where deptno=20;
v_sal emp.sal%type;
begin
open sal_cur;
fetch sal_cur into v_sal;
while sal_cur%found loop
dbms_output.put_line('sal: '|| v_sal);
fetch sal_cur into v_sal;
end loop;
close sal_cur;
end;
12345678910111213
2.使用for循环打印上题
1
declare
cursor sal_cur is select sal from emp where deptno=20;
begin
for i in sal_cur loop
dbms_output.put_line('sal: '|| i.sal);
end loop;
end;
游标课后练习：
打印出 empno 为 7788 的员工的 ename, job, sal 信息(使用游标, 记录类型)
123456789
隐式游标：与显式的区别
--例题: 更新指定编号员工 sal(涨工资 10)，如果该员工没有找到，则打印”查无此人” 信息
declare
begin
update emp set sal=sal+10 where empno=110;
if sql%notfound then dbms_output.put_line('查无此人');
else dbms_output.put_line('加鸡腿！！');
end if;
end;
12345678910
9、异常处理
异常基本语法：
Declare
Begin
Exception
When 异常名 then 打印出异常结果
1234567
--查出工资大于1000的员工工资。定义一个异常，打印出'输出的行数太多了'；
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where sal>1000;
dbms_output.put_line(v_sal);
exception
when TOO_MANY_ROWS then
dbms_output.put_line('输出的行数太多了');
end;
12345678910
10、存储函数
[存储函数：有返回值，创建完成后，通过select function() from dual;执行]
1
存储函数的基本结构：
(1)、写一个无参数存储函数hello_word，返回一个“hello function！”
create or replace function hello_word
return varchar2
as
begin
return 'hello function';
end ;
两种测试方法：
Test window ：
Command window：
(2)、写一个带参数存储函数hello_word，返回一个“hello function！”
create or replace function hello_word(v_h in varchar2)
return varchar2
as
begin
return 'hello function '|| v_h;
end ;
(3)、写一个带参数存储函数hello_word，返回一个“hello function！”
注意输出顺序
--3.创建一个存储函数，返回当前系统时间
create or replace function get_sysdatetime
return date
as
v_time date;
begin
v_time :=sysdate;
return v_time;
end ;
--4.定义两个带参数的函数：两个数相加
create or replace function get_sum(g_a number,g_b number)
return number
as
v_sum number(10);
begin
v_sum :=g_a+g_b;
return v_sum;
end ;
--5.定义一个函数：获取 给定部门的工资 总和，要求：部门号定义为参数，工资总额定义为返回值。
create or replace function sumal(dept_no number)
return number
as
v_sum number(10):=0;
cursor sal_curs is select sal from emp where deptno=dept_no;
begin
for i in sal_curs loop
v_sum :=v_sum+i.sal;
end loop;
dbms_output.put_line('工资总和为：');
return v_sum;
end ;
--6.关于OUT型的参数：因为函数只能有一个返回值，PL/SQL程序可以通过OUT型的参数实现有多个返回值
--要求：定义一个函数：获取给定部门的工资总和 和 该部门的员工总数（定义为OUT类型的参数）（工资总和作为返回值）
--部门号定义为参数，工资总额定义为返回值。
create or replace function sumal1(dept_no number,sal_sum out number)
return number
as
v_sum number(10):=0;
cursor sal_curs is select sal from emp where deptno=dept_no;
begin
sal_sum:=0;
for i in sal_curs loop
v_sum :=v_sum+i.sal;
sal_sum :=sal_sum+1;
end loop;
return v_sum;
end ;
调试：
declare
v_ount number(10) :=0;
begin
dbms_output.put_line(sumal1(20,v_ount));
dbms_output.put_line(v_ount);
end;
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586
11、存储过程
--存储过程：由于没有返回值，创建完成后，不能使用select语句，只能使用pl/sql块执行
--1.定义一个存储过程：获取给定部门的工资总和（通过out参数），要求：部门号和工资总额定义为参数
create or replace procedure get_sumsal(dept_no number,sum_sal out number)
is
cursor salsum_curs is select sal from emp where deptno=dept_no;
begin
sum_sal:=0;
for i in salsum_curs loop
sum_sal := sum_sal+i.sal;
end loop;
dbms_output.put_line(sum_sal);
end ;
调试：
declare
v_sums number(20);
begin
get_sumsal(20,v_sums);
end;
--2.自定义一个存储过程完成以下操作：对给定部门（作为输入参数）的员工进行加薪操作，若其入职时间在（?,95）期间，为其加薪5%
[95,98) 3%
[98,?) 1%
--得到一下返回结果：为此次加薪公司每月需要额外付出多少成本（定义一个OUT型的输出参数）。
create or replace procedure get_sumsal2(dept_no number,comm_sal out number)
is
v_addsal number(10,2):=0;
cursor salsum2_curs is select empno,sal,hiredate from emp where deptno=dept_no;
begin
comm_sal:=0;
for i in salsum2_curs loop
if (to_char(i.hiredate,'yyyy')<'1995') then v_addsal :=0.05;
elsif (to_char(i.hiredate,'yyyy')<'1998') then v_addsal :=0.03;
else v_addsal :=0.01;
end if;
update emp set sal=sal*(1+v_addsal) where empno =i.empno;
comm_sal := comm_sal+ i.sal*v_addsal;
end loop;
dbms_output.put_line(comm_sal);
end ;
调试：
declare
v_sums2 number(20);
begin
get_sumsal2(20,v_sums2);
end;
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152
IN OUT示例
--给某个员工加工资
create or replace procedure add_sals(emp_no in emp.empno%type,emp_name out emp.ename%type,add_sal_s in out emp.sal%type)
is
begin
update emp set sal=sal+add_sal_s where empno=emp_no;
select ename,sal into emp_name,add_sal_s from emp where empno=emp_no;
end ;
调试：
declare
v_name emp.ename%type;
v_addsum number(20) :=500;
begin
add_sals(7369,v_name,v_addsum);
dbms_output.put_line('员工名'||v_name ||'加薪后'||v_addsum);
end;
12345678910111213141516171819
11、删除存储函数/过程
Drop function/procedure 函数名/ 过程名
转载自：[努力迈向java大牛](https://me.csdn.net/weixin_41600307)https://blog.csdn.net/weixin_41600307/article/details/99307959?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.channel_param&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.channel_param
