/*******************************************************************************************/

一、 SQL简介

SQL: 结构化查询语言

SQL 是一门 ANSI 的标准计算机语言，用来访问和操作数据库系统。

SQL 语句用于取回和更新数据库中的数据。 SQL 可与数据库程序协同工作，比如 MS Access 、 DB2 、 Informix 、 MS SQL Server 、 Oracle 、 Sybase 以及其他数据库系统。

注释：除了 SQL 标准之外，大部分 SQL 数据库程序都拥有它们自己的私有扩展！

SQL 语法基础：

select... 查询内容

from... 在哪里查询

where... 使用什么样的条件

group by...(sql 的入门 ) 按什么进行分组

having... 做分组时按什么条件过滤

order by... 按什么来排序

/*******************************************************************************************/

二、基本的操作

0. 显示当前用户： SQL> show user;

1. 查看当前用户下，有几个表的指令： select * from tab;//tab 是数据字典，记录的是 scott 用户下有哪些表

2. 查看表结构：

desc dept(dept 部门表 )

DEPTNO 部门编号 ( 这些查看信息都可以用 select * from dept 来看 )

DNAME 部门名称

LOC 地点

desc emp(emp 员工表 )

EMPNO 员工号

ENAME 员工姓名

JOB 工种

MGR 经理

HTREDATE 入职日期

SAL 薪水

COMM 奖金

DEPTNO 部门编号（两个表都有的字段，这个是关系型数据库的特点，两个表可以有同一个字段）

3. 查询命令可以使用 select * from dept

4. 其他

select * from emp// 结果出来很多东西，格式很乱，解决方法：

set linesize 140;// 包括空格一行的大小为 140, 也就是设置一行的宽度

/ 扛表示执行上一条命令，也就是查询命令

set pagesize 120;// 设置一页的宽度

也可以把命令写入到如下两个配置文件里，这样登录退出后也会生效，

C:\app\Administrator\product\11.2.0\client_1\sqlplus\admin\glogin.sql

C:\app\Administrator\product\11.2.0\dbhome_1\sqlplus\admin\glogin.sql

select * from dept;

col deptno for(format) 9999;// 设置 deptno 列宽度为 4 个 9 这么宽 ( 这个是数值型的设置 )

col dname for a20;// 设置 dname 列宽度为 20( 这个是字符型的设置 )

清屏命令： host cls

/*******************************************************************************************/

三、基本的查询

1.查询的语法

select *|{[distinct]column|expression[alias],..} from table;

基本查询：可以查询全部列，部分列 , 表达式，别名，去除重复

2.示例：

1). 基本查询

-- 查询员工表信息

SQL> select * from emp;

2).as 后面是别名 , 别名的意思是查询后的结果不显示原来的名字显示定义的别名。其中 as 可以省略。

-- 查询员工号，姓名，月薪，奖金，年薪 -- 需要注意别名 , 如果不使用双引号 , 不能有空格和不可见字符

SQL> select empno as " 员工号 ",ename " 姓 名 ",sal 薪水 ,comm 奖金 ,sal*14 年薪 from emp;

3).select * 与 select 列的区别

推荐写指定列 , 效率高 .

4).null( 空，也就是项目里面没有内容 ) 与表达式运算，结果为 null ，为防止结果为空，则

使用滤空函数： nvl(a,b) ，含义是如果 a 为 null 则结果为 b ，如果 a 不为 null 则结果为 a

-- 查询员工号，姓名，月薪，奖金，年薪，年收入 -null 捣蛋鬼

null 与表达式运算 , 结果为 null , 使用 nvl 函数虑空函数 nvl(a,b) 如果 a 为 null, 结果为 b, 如果 a 不为 null, 结果为 a

SQL> select empno as " 员工号 ",ename " 姓 名 ",sal 薪水 ,comm 奖金 ,sal*14 年薪 ,sal*14+nvl(comm,0) from emp;

5). 使用 distinct 关键字去除重复行 ,

-- 查看员工表不同的部门编号 distinct 关键字 , 作用域 : 整行

SQL> select distinct deptno from emp;// 得到不同的部门编号

注意 ,distinct 作用域是整行，而不是某一列，

比如 select distinct deptno,job from emp;

这是得到的是 deptno 和 job 组合起来不同的结果，也就是整体 ( 行 ) 不同的结果，而不是单独 deptno 列或者 job 列不同的结果

6).sql 错误修改方法：

出错时使用 edit 或者 ed 指令，这是会弹出一个记事本，修改记事本中的内容也就是上一条指令，然后保存退出，再次使用 / 来执行上一条指令即可，这样便于修改比较长的指令

--sql 错误修改方法

ed(it) 指令

7).sql 也可以来计算表达式以及显示当前日期等，

-- 输出计算表达式 3+20*5 ，显示当前日期

SQL> select 3+20*5, sysdate from emp;// 这个就会有结果显示，但是显示的结果是很多行的，行数等于 emp 中的行数。

// 如何解决这个多行的问题，就要使用到伪表 ( 即空表 ) 和伪列 ( 是本来就存在的列，但不依赖于某一个表 ) ，即 dual( 计算或者函数情况时使用 ) ，

伪表和伪列 dual ( 计算或者函数情况时使用 ) , 伪列是本来就存在的列 , 不依赖于某一个表

SQL> select 3+20*5, sysdate from dual;

8).SQL 语句使用注意事项

SQL 语言大小写不敏感。

SQL 可以写在一行或者多行

关键字不能被缩写也不能分行

各子句一般要分行写。

使用缩进提高语句的可读性。

9). 连接符

Oracle 中定义了一个连接符 ’ || ’ 用来连接字符串。

显示 “ xxx 是一个 xxx ”怎么显示呢？

SQL> select ename || ‘ is a ’ || ‘ job ’ from emp

但是如果显示一个 “ hello world ”应该怎么显示呢？

(1). 使用 concat 函数来实现： SQL> select concat( ‘ hello ’ , ‘ world ’ ) from ????

这两个字符串不是任何一个表的内容，在 Oracle 中，定义一张“伪表” dual 用来满足 SQL99 语法（ ANSI ）。

语法规定： select 后 必须接 from 。

可以这样： SQL> select concat('aa', concat('bb', 'cc')) from dual;

(2). 使用连接符 || 来实现 : SQL> select 'hello' || ' world' || ' Oracle' as " 你好 " from dual

再如： SQL> select 3 + 2 from dual;

‘ || ’就是用来代替 concat 函数的。 SQL> select ename || ‘ is a ’ job “员工描述” from emp

10).SQL 和 sqlplus

sqlplus 是登录工具

sql 命令是敲出来，

如何区别，注意操作本地的就是属于 sqlplus 的命令，也就是说在客户端执行的命令就是 sqlplus 的命令，如果是对 oracle 实例进行操作的那就是 sql 的命令。

也就是说 ,sql 指令是需要和数据库进行交互的， sqlplus 是本地更改

desc ql

edit sqlplus

select sql

那么 sqlplus 有哪些指令呢，可以在 sqlplus 下执行 ? topic 即可。

区分的意义在于，如果换了数据库那么 sqlplus 的命令就不一样了，因为客户端是其他数据库的了，但是 sql 指令是一样的。

sql 指令可以换行写，但是第一行不能缺少 select

/*******************************************************************************************/

四、 where过滤

where 过滤条件

基本语法 select ... from ... where condl

其中这个条件就涉及到运算符

其中，比较运算符要注意 <> 就是不等的意思 between and 是一对整体

1.比较运算符 = != <> < > <= >= between and

-- 查询 10 号部门的员工信息

SQL> select * from emp where deptno=10;

-- 查询员工名字为 king 的员工信息

SQL> select * from emp where ename='KING';// 注意在 sql 中单引号代表字符串，双引号代表别名 , 注意字符串要全匹配也就是说区分大小写

-- 查找薪水不等于 1250 员工的信息

SQL> select * from emp where sal != 1250;

SQL> select * from emp where sal <> 1250;

-- 查询入职日期为 1981 年 11 月 17 日的员工信息 --- 日期型的查询

注意日期型的查询，也就是如何匹配日期，注意日期格式敏感

首先查看数据库的参数配置，也就是查看日期格式的设置： select * from v$nls_parameters;

然后查询的时候比较的字符串格式要与设置的一致

或者设置日期格式： alter session set NLS_DATE_FORMAT='yyyy-mm-dd';, 然后按照该格式输入字符串查询 , 注意登录的是会话所以设置的是所属于会话的。

所以如果有人修改了设置，查询会报错。所以一般不会轻易去改动

SQL> select * from emp where hiredate='1981-11-17';

select * from emp where hiredate='1981-11-17'

第 1 行出现错误 :

ORA-01861: 文字与格式字符串不匹配

-- 查看日期设置 select * from v$nls_parameters; year- 月份 - 日期 yyyy-mm-dd

见图 1 ：

SQL> select * from emp where hiredate='17-11 月 -81';-- 日期格式敏感

SQL> alter session set NLS_DATE_FORMAT='yyyy-mm-dd';

SQL> select * from emp where hiredate ='1981-11-17';

带来一个问题 , 如果有人修改了设置了 , 我们的语句将报错 !!!

SQL> alter session set NLS_DATE_FORMAT='dd-mon-rr';-- 恢复原状

2.前面都是一个条件，如果需要多个条件的就需要用到逻辑运算符：逻辑运算符 and or not

-- 查询 10 号部门或者 20 部门的员工信息

SQL> select * from emp where deptno =10 or deptno = 20;

-- 查询 10 号部门员工工资为 1300 的员工信息

SQL> select * from emp where deptno =10 and sal=1300;

-- 查找工资介于 1000-2000 之间的员工信息，

SQL> select * from emp where sal >=1000 and sal <= 2000;

等同于： SQL> select * from emp where sal between 1000 and 2000 ;

注意， between 1000 and 2000 这个是闭区间，即 []

-- 查询 81 年 2 月 ( 含 2 月 ) 至 82 年 2 月 ( 不含 2 月 ) 入职的员工信息 81-2-1 -->82-1-31

SQL>select * from emp where hiredate between '1981-2-1' and '1982-1-31';

-- 查询奖金为空的员工信息 -null

注意，查询为空的记录时，也就是 null 时，要注意，

null 重要结论，不能用 = !=, 也就是说 columnname=null 或者 !=null 均永远为假

正确的写法 :SQL>select *from emp where comm is null;

或者 SQL>select *from emp where comm is not null;

-- 多个条件时怎么写更优？

注意， sql 查询记录的时候，是读取一条条记录然后和 where 条件进行比对，所以查询语句越少效率越高。

当一条查询语句中有多个条件时，这时 sql 是从 where 后面最右边一个条件开始比对，直到 where 最近的一个条件。所以为了效率最高，如果最后的条件就能 ( 退出比对 ) 直接返回

结果，那么就是最优的写法， (and 的时候 , 易假的放在右侧 ,or 与之相反 .)

-- 多个逻辑运算符的执行顺序

注意出现多个逻辑运算符的执行顺序，也就是优先级，从前面我们可以得到从右边开始比对，所以逻辑运算是从右开始执行，

当一个结果计算好后再由右向左与下一个进行运算。如果想打破这种顺序，就需要用 () 将先执行的包括起来：

SQL>select * from emp where (deptno=10 or deptno=30) and sal=1250

3.in：在集合中

-- 查询部门号是 10 或者 20 的员工信息：

SQL>select * from emp where deptno in (10,20);

-- 查询部门号不是 10 或者 20 的员工信息：

SQL>select * from emp where deptno not in (10,20);

注意，如果想查询部门号不是 10 或者 20 也不为空的员工信息：

select * from emp where deptno not in (10,20,null);

这样是错误的，因为 null 不能用来进行判断的，否则为假，这个前面也说了这个重要结论了

所以结论是 ： not in 的集合不能有 null

4.like:模糊查询

知识点： ‘ % ’匹配任意多个字符。‘ _ ’匹配一个字符

-- 查询员工首字母是 S 的员工信息

SQL>select * from emp where ename like 'S%';

-- 查询四个字母的员工信息

SQL>select * from emp where ename like '____';// 四个下划线即可

-- 查询带有下划线的，涉及到转义，要使用关键字 escape

SQL>select * from emp where ename like '%\_%' escape'\';// 把 _ 转义为普通字符，注意 escape 用来定义转义字符

SQL>select * from emp where ename like '%/_%' escape '/';

select ... 指定列

from ... 指定表

where... 指定条件

作用是为了实例可以解析 sql 命令

null 总结 :

1.null 在表达式结果为 null , 要用虑空函数 nvl(a,b) 如果 a 为 null, 结果为 b, 否则结果为 a

2.not in 的集合中不能有 null

3.null 不能用 = ,!= (<>)

/*******************************************************************************************/

完整 sql 语句

select...

from...

where...

group by...

having...

order by...

排序可以 列名，表达式，别名，序号，语法是 order by col|alias|number 默认是 asc 模式，升序， desc 降序

其中，这个序号的意思是属于查询结果集中第几个的意思，如果查询结果集是 * 那么序号就是第几列了。

-- 员工信息按入职日期排序

SQL>select * from emp order by hiredate;

2. 排序（序号）

-- 员工薪水按从大到小排序（序号）

SQL>select * from emp order by sal desc;

SQL>select * from emp order by 6 desc;// 第 6 列降序排序， 6 等价于 sal

SQL>select empno,ename,sal from emp order by 3 desc;// 等价于前面的，还是按照第 6 列 (sal) 降序排序

3. 多种条件排序

-- 员工信息按部门、薪水排列

// 两种或多种条件排序，条件越靠前面的越优先进行排序，即当前面排序条件相同时再用后面的条件排序，即

排序的原则 : 先按第一个字段 , 然后再按第二个字段 ...

SQL>select * from emp order by deptno,sal;// 先按 deptno 排序， deptno 中一样的再按 sal 排序。

4. 作用范围

--asc 和 desc 作用范围是它之前的一个字段

SQL>select * from emp order by deptno,sal desc;//desc 只作用于 sal, 即 sal 是按递减排序的。

5.null 代表无穷大

-- 员工信息按奖金逆序 (nulls last --null)

null 代表无穷大 , 排序默认排在最后 ,

如果在降序中则在最前面，要想把 null 的放在最后则要在语句后面加上 nulls last ，强制放到最后

或者使用滤空函数把空的变为最小再排序：

SQL>select * from emp order by  nvl(comm,-1) desc;// 可能数据库的数据里有 0 的，所以填 -1 来保证最小 ( 一旦 comm 为 null 就等于 -1

/*******************************************************************************************/

六、单行函数

单行函数 : 只对一行进行变换，产生一个结果。函数可以没有参数，但必须要有返回值。如： concat 、 nvl

操作数据对象

接受参数返回一个结果

只对一行进行变换

每行返回一个结果

可以转换数据类型

参数可以是一列或一个值

1.字符函数

--lower 小写， upper 大写， initcap 首字母大写

SQL>select lower('Hello wOrld') 一 ,upper('Hello wOrld') 二 ,initcap('Hello wOrld') 三 from dual;

concat( 连接符 ||) ， substr ， length ， lengthb ， instr

--concat 函数 , 字符串连接 , 只能传入两个参数，多个字符连接可以使用 concat 中嵌套 concat 实现，或者使用这个符号： || ，这个对于个数没有限制。

SQL>select concat('hello',' world') from dual;

SQL>select 'hello'||' world'|| ' 1111'||'2222' from dual;

--length 求字符长度，注意汉字当一个字符， lengthb 求字节数，一个汉字占两个字节。

SQL> select length('hello 中国 '),lengthb('hello 中国 ') from dual;

LENGTH('HELLO 中国 ') LENGTHB('HELLO 中国 ') --- 需要注意 lengthb 求字节数 ,gbk 编码汉字 2 个字节

---------------------------------------

--substr

SQL>select substr('helloworld',1,3) 一 ,substr('helloworld',1) 二 ,substr('helloworld',-3) 三 from dual;

--instr 函数返回的是子字符串所在的位置，位置从 1 开始，若不存在这样的子字符则返回 0

SQL>select instr('helloworld','owo') 一 ,instr('helloworld','owow') 二 from dual;-- 查找 instr(a,b) 判断 b 是否在 a 中 , 如果存在 , 返回 b 在 a 中的位置 , 如果不存在返回 0

--l(r)pad(str,length,C)

返回 length 长度的字符串 , 如果 str 不够 , 则在左 ( 右 ) 补充 C 字符

lpad 有三个参数，第一个参数为字符串，第二个参数为长度，第三个为字符，意思是返回满足第二参数表示的长度的

字符串 ( 是第一个参数中的 ) ，不够则用第三个参数在左边补全。

rpad 有三个参数，第一个参数为字符串，第二个参数为长度，第三个为字符，意思是返回满足第二参数表示的长度的

字符串 ( 是第一个参数中的 ) ，不够则用第三个参数在右边补全。

--trim 函数，

trim 函数是去空格的 , 去掉字符串左边和右边的空格 , 字符串中间的空格是不会去的 . 如果不是去掉指定字符串的首尾的空格，

而是去掉字符串首尾的其他字符，则原先的写法由 trim(' 字符串 ') 改为 trim(' 字符 ' from ' 字符串 ')

SQL>select 'aaa'||trim(' hello world ')||'bbb' from dual;

SQL>select trim('H' from 'HHHHHelloHHHworldHHHHH') from dual;--trim(C from str) 去掉 str 首尾为 C 的字符

--replace 函数，

SQL>select replace('helloworld','llo','kk') from dual;

SQL>select replace('helloworld','llo','') from dual;

// 表示的是将 helloword 中的 llo 替换为 kk ，如果 kk 不写得话就相当于去掉了其中的 llo

2.数值函数

数值函数 正、负表示小数点之后，或小数点以前的位数

--round(a,b) 函数：四舍五入，

SQL> select round(45.926, 2) 一 , round(45.926, 1) 二 , round(45.926, 0) 三 , round(45.926, -1) 四 , round(45.926, -2) 五 from dual;

--trunc(a,b) 函数：直接舍掉，

其中 b 为正数表示取小数点后 b 位，为负数表示取小数点前 b 位。

SQL> select trunc(45.926, 2) 一 , trunc(45.926, 1) 二 , trunc(45.926, 0) 三 , trunc(45.926, -1) 四 , trunc(45.926, -2) 五 from dual;

--mod(a,b), 取余， a 除以 b 的余数

SQL> select mod(1600,600),mod(600,1600) from dual;

MOD(1600,600) MOD(600,1600)

--------------------------

400 600

--ceil(a/b) ， a 除以 b 商向上取整。 floor(a/b) ， a 除以 b 商向下取整

SQL> select ceil(121/60),floor(121/60) from dual;

3.转换函数

(1). 显示转换，就是使用函数转换，

函数包括 to_char to_number to_data ，注意这字符，数字，日期这三种类型之间的转换，

字符是可以和其他互相转换的，但是数字和日期之间无法直接转换，也就是说字符串起中间作用，具体见图 2 ：

(2). 隐式转换就是用等号就转换了类型

具体见图 3 ：

隐式转换前提 : 能转换。

当能够显示转换的时候尽量显示转换 .

(3). 转换函数的使用格式：

字符函数（待转换内容，转换的格式）

其中转换的格式都是可逆的。

--to_char 与 to_number 互相转换

注意 to_char 函数中使用到的格式，

-- 把薪水转换为本地货币字符型 , 见图 4 ：

SQL> select sal,to_char(sal,'L9,999') from emp;// 表示将原来数值型的薪水都转换为带有货币符号和四位数值

以及千位符这种格式的字符型

SQL> select to_number(' ￥ 1,250','L9,999') from dual;// 转换回去，转换的格式还是一样的

TO_NUMBER(' ￥ 1,250','L9,999')

-----------------------------

--to_char 和 to_date 互相转换

注意 to_date 函数中使用到的格式，具体见图 5 ：

例如查找日期型数据：

SQL> select * from emp where hiredate=to_date('1981-11-17','yyyy-mm-dd');// 将字符类型按照日期类型转换后用作条件查找

to_char 与 to_date 显示 "yyyy-mm-dd hh24:mi:ss 今天是 星期几 "

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss " 今天是 " day') from dual;

// 其中，格式化字符串中的非格式化字符用双引号，系统时间所在的伪列为 sysdate,

将上述输出字符串反转回日期

select to_date('2017-02-13 16:06:04 今天是 星期一 ','yyyy-mm-dd hh24:mi:ss " 今天是 " day') from dual;

4.日期函数

(1). 显示昨天，今天，明天

SQL> select sysdate-1 昨天， sysdate-1 今天， sysdate-1 明天 from dual;//oracl 日期格式可以加数字。注意日期之间可以

相减表示天数，但是不能相加。 // 其中昨天，今天，明天是列的别名，也就是显示出来的列名用逗号后面的别名不用前面的

-- 计算员工工龄 可以按日，周，月，年 日期差减方法

SQL> select sysdate-hiredate 日 ,(sysdate-hiredate)/7 周 ,(sysdate-hiredate)/30 月 ,(sysdate-hiredate)/365 年 from emp;

(2). 日期函数 mounts_between(a,b)     add_months

参数类型是日期类型，表示 a 和 b 之间间隔几个月。 sql 中的乘除都是可以带小数的，这个函数如果结果不是整数返回的也是小数。

用这种函数算比自己用天数来除以 12 更准确。

SQL> select months_between(sysdate,to_date('2016-12-13','yyyy-mm-dd')) from dual;

SQL> select months_between(sysdate,hiredate) 月 ,months_between(sysdate,hiredate)/12 年 from emp;

(3). 日期函数 last_day

求给定月份的最后一天

SQL> select last_day(sysdate) from dual;

(4). 日期函数 next_day

求给定日期的下一个星期几

SQL> select next_day(sysdate,' 星期六 ') from dual;// 求当前日期的下一个星期六

/*******************************************************************************************/

七、条件表达式

sql 中没有 if else, 所以判断条件一个用的是 case...end, 还有一个是 decode

1.case...end             -------sql99 标准

CASE expr WHEN comparison_expr1THEN return_expr1

[WHENcomparison_expr2 THENreturn_expr2

WHENcomparison_exprn THENreturn_exprn

ELSE else_expr]

case 变量 when 变量值 then 该变量值下要做的动作

(when 变量值 then 该变量值下要做的动作 )

(else 其他条件要做的动作 )

SQL> select empno,ename,job,sal 涨前薪水 ,case job when 'PRESIDENT' then sal+0

when 'MANAGER' then sal+200

else sal+1000 end 涨后薪水

from emp;

2.decode 函数 ------oracle 特有

decode ：是一个函数，其语法为：

DECODE(col|expression, search1, result1

[, search2, result2,...,]

[, default])

语法中文说明：

decode( 变量，变量值，该变量值下要做的动作，变量值，该变量值下要做的动作，其他条件要做的动作 );

即参数位置含义固定

SQL> select empno,ename,job,sal 涨前薪水 ,decode(job,'PRESIDENT',sal+1000,'MANAGER',sal+800,sal+400) 涨后薪水

from emp

/*******************************************************************************************/

八、分组多行 (函数)与分组数据

1.分组函数

分组函数是针对前面单行函数说的，前面学的函数都是单行函数。多行函数也叫组函数

即分组函数是对多行进行操作的，作用的范围是多行的。

分组函数包括： max,min,avg ， sum,count( 求数量 ),

1).sum( 求和 )

-- 求员工的工资总额

SQL> select sum(sal) from emp;

2).count( 求数量 )

-- 员工人数

SQL> select count(*) from emp;// 求员工人数，即行数

-- 求 emp 表中的工种数

SQL> select count(distinct job) from emp;// 求工种数目，即去除了重复

3).avg( 求平均 )

-- 求员工的平均工资（两种方式）

SQL> select avg(sal) 一 ,sum(sal)/count(*) 二 from emp;

-- 求员工的平均奖金（三种方式）

SQL> select avg(comm) 一 ,sum(comm)/count(*) 二 ,sum(comm)/count(comm) 三 from emp;

一 二 三

------------------------------

550 157.142857 550

结果：只有第一个和第三个一样。

原因是， count(*) 计算的是行数即奖金不管是否为空都计算， count(comm) 计算的是奖金不为空的数目

即组函数都有自动的滤空功能。

2.分组数据

分组数据的含义就是把数据进行分组，然后按组进行处理，使用 GROUP BY 子句数据分组：

按照 group by 后给定的表达式，将 from 后面的 table 进行分组。针对每一组，使用组函数 ( 分组数据的含义就是把分出来的各个组的数据一起显示出来 ) 。

group by 中出现的也必须在在 select 中出现，这样才能选中显示出来 ( 在 select 中出现的非组函数的列则必须有分组即 group by... ，

同时在 select 中出现的非组函数的列 , 必须在 group by 中出现 .)

统计各个部门的平均工资，先统计 10 号的，再统计 20 的，最后统计 30 的

SQL> select deptno,avg(sal) from emp group by deptno;// 按部门号计算每个部门的平均工资，即按部门号分组，一号一组，再计算每组的平均工资， ( 再按部门号列出来 )

SQL> select a,avg(X) from tablename group by a

SQL> select a,b,avg(X) from tablename group by a,b

SQL> select a,b,c,avg(X) from tablename group by a,b,c

结论： ( 首先出现 a,avg(x) 则必须有分组即 group by... ，同时在 select 中出现的非组函数的列，必须在 group by 中出现 ) 。

-- 查询各部门平均工资

SQL> select deptno,avg(sal) from emp group by deptno

-- 查询各部门各工种平均工资

SQL> select deptno,job,avg(sal) from emp group by deptno,job;

3.having

使用 HAVING 子句过滤分组结果集。

使用 HAVING 过滤分组 :

1). 行已经被分组。

2). 使用了组函数。

3). 满足 HAVING 子句中条件的分组将被显示。

where 后不能使用分组函数 . having 的作用对分组进行过滤

-- 查询平均薪水大于 2000 的部门

SQL> select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

-- 求 10 号部门员工的平均薪水

select deptno,avg(sal) from emp group by deptno having deptno=10;

select deptno,avg(sal) from emp where deptno=10 group by deptno;

having 与 where 哪个好？

如果都能用的情况下，优先使用 where ，因为先过滤再分组 (sql 语句是顺序执行的 ) 效率更高。

4.常见的非法使用组函数的情况

-- 缺失 group by 字句 , 语法不符合规范 .

select 查询可以全部列 , 部分列 , 表达式 , 别名

group by 分组的要求：在 select 中出现的非组函数的列，必须在 group by 后出现， -- 数据按什么分组，数据如何显示？。

分组函数也叫组函数，或者 聚合函数

like 效率比较低，能少用就少用。

/*******************************************************************************************/

九、多表查询 (基于oracle)

1.理论基础:笛卡尔全集

笛卡尔全集 : 两个表中的每条记录两两组合形成新的表，

所以新的表的列数等于两个表列数相加，新的表的行数等于两个表行数相乘 :

全集的行数 = 表 1 的行数 * 表 2 的行数

全集的列数 = 表 1 的列数 + 表 2 的列数

组合后还要看新的表中的记录是否有意义，即两个表中相同的列中的元素相等的记录才有意义，即连接条件 ( 相同的列 ) 相等的记录才有意义

没有意义的是垃圾数据 , 需要使用连接条件过滤，如下图 6 ，图 7 中画下划线的部分就是垃圾数据：

2 张表连接的条件至少是 1 个

3 张表连接的条件至少是 2 个

===>N 张表的连接条件至少是 N-1

2.连接条件写法:1.表名.列名    2.给表起别名 别名.列名

推荐用第二种，因为当表名很长的时候 别名更方便。

select * from emp,dept;//emp,dept 就是卡尔全集 :

select * from emp e,dept d where e.deptno=d.deptno;//e d 是别名，连接条件是 deptno 相等的

3.根据连接条件的不同可以划分为：等值连接，不等值连接，外连接，自连接

Oracle 连接 :

Equijoin ：等值连接

Non-equijoin ：不等值连接

Outer join ：外连接

Self join ：自连接

SQL: 1999

Cross joins

Natural joins

Using clause

Full or two sided outer joins

1).等值连接(使用等号，在mysql中称为内连接)

-- 查询员工信息：员工号 姓名 月薪 (emp) 和部门名称 dept

SQL> select empno,ename,sal,dname from emp e, dept d where e.deptno = d.deptno ;

注意，都有的列不能放在 select 后面，这样会有冲突，必须要指定时属于哪张表的列，放在连接条件内其实就不用指定了

2).不等值连接(不使用等号)

-- 查询员工信息：员工号 姓名 月薪 和 薪水级别 (salgrade 表 )

SQL> select empno,ename,sal,grade

from emp e,salgrade s

where e.sal>=s.losal and e.sal<=s.hisal;

-- 可以用 between and

SQL> select empno,ename,sal,grade

from emp e,salgrade s

where e.sal between s.losal and s.hisal;

注意，当前薪水符合范围的，这样的记录中的薪水级别才是对的。

也就是说，记录要符合连接条件，也就是经过连接条件过滤后的记录是要有意义的。

3).外连接

当连接条件中的内容两个表不一致时 , 或者说连接重组后的表中 , 有的内容是只有一张表中有的 , 其他的表没有，如图 8 中的 40 号部门这一行：

这时如果需要统计这条记录时 , 则要特别处理 .

-- 按部门统计员工人数，显示如下信息：部门号 部门名称 人数 , 注意有的部门没有人

SQL> select d.deptno,d.dname,count(empno)//count(empno) 统计有人的部门，所以不能用 * 。由于不知道用哪一列，以及不知道列的别名是什么，所以这里最后写

from dept d,emp e

where e.deptno(+)=d.deptno// 需要包含那条数据不完整的记录，也就是想保留等号右边的， (+) 放在等号左边，称为右外连接

group by d.deptno,d.dname;

-- 左外连接 : 想保留等号左边的 ,(+) 放在右边

SQL> select d.deptno,d.dname,count(empno)

from dept d,emp e

where d.deptno=e.deptno(+)

group by d.deptno,d.dname

order by 1

-- 右外连接 : 想保留等号右边的 ,(+) 放在左边

SQL> select d.deptno,d.dname,count(empno)

from dept d,emp e

where e.deptno(+)=d.deptno

group by d.deptno,d.dname

order by 1

4).自连接

一个表中的两条记录需要组合起来或者说需要相连接起来 , 这时就需要自连接 , 也就是把一张表当作两张表 .

所以自连接时特殊的外连接。

-- 查询员工信息： xxx 的老板是 yyy

分析：可以把员工表 emp 当成两个表，一个是员工表，一个是老板表。

SQL> select e.ename||'''s boss is' || nvl(b.ename,'himself')// 字符串内部还需要显示单引号，则直接加单引号即可

from emp e,emp b

where e.magr=b.empno(+)

// 老板没有上级，所以这个条件不成立，但是为了把这条不成立的记录保留下来，也就是需要保留这条不完整的记录，这条记录

只有左边的，即只有左边的自己没有老板，所以保留左边的。

-- 下面的查询结果少了大老板 , 因为连接条件不成立

select e.ename||'''s boss is'||b.ename

from emp e,emp b

where e.mgr = b.empno

自连接的使用条件 :

(1). 数据都在一个表中

(2). 数据不在同一行

自连接的弊端 :

自连接对应的笛卡尔集合是平方的关系 , 所以行数很多 ( 数量级平方的增长 ). 所以最好在小表中使用

/*******************************************************************************************/

十、子查询

什么情况下使用子查询 ? 一个查询不能完成的时候。

子查询的本质： sql 嵌套 sql

查询比 scott 工资更高的员工信息

分析：首先找到 scott 的工资，然后条件大于这个工资的。

SQL> select * from emp where sal>(select sal from emp where ename='scott');

注意事项：

1. 合理的书写风格 ( 如上例，当写一个较复杂的子查询的时候，要合理的添加换行、缩进 )

2. 小括号 ( )

3. 主查询和子查询可以是不同表，只要子查询返回的结果主查询可以使用即可

-- 查询部门名称是“ SALES ”的员工信息

分析 : 1. 先找到 SALES 部门名称对应的编号 2. 用这个编号做条件过滤员工

select deptno from dept where dname ='SALES';

select * from emp where deptno = 30;

SQL> select * from emp where deptno = (select deptno from dept where dname ='SALES');

4. 可以在主查询的 where,select,having,from 后都可以放置子查询，即 " 两个 by" 后面不行。

select ..

from …

where …

group by … err

having …

order by … err

1).select 后

-- 查询 10 号部门员工号，员工姓名，部门编号，部门名称

(1). 等值连接写法

SQL> select e.empno,e.ename,e.deptno,d.dname

from emp e,dept d

where e.deptno=d.deptno and e.deptno=10;

(2). 子查询写法

SQL> select empno,ename,deptno,(select dname from dept where deptno=10)

from emp

where deptno=10;

2).from 后

-- 查询员工的姓名，薪水和年薪

SQL> select * from (select empno,ename,sal*14 from emp);

3).where 后

-- 查询与 ward 相同 job 并且薪水比他高的员工信息

先找到 ward 的工种和薪水 , 然后用作过滤条件

select sal,job from emp where ename ='WARD';

select * from emp where job='SALESMAN' and sal > 1250;

==> 变成子查询

SQL> select * from emp where job=(select job from emp where ename ='WARD') and sal > (select sal from emp

where ename ='WARD');

4).having 后

-- 查询高于 30 号部门最低薪水的部门及其最低薪水

SQL> select deptno, min(sal)

from emp

group by deptno

having min(sal) > (select min(sal) from emp where deptno = 30)

5. 不可以在主查询的 group by 后面放置子查询 (SQL 语句的语法规范 )

6. 强调：在 from 后面放置的子查询 (***) from 后面放置是一个集合 ( 表、查询结果 )

7. 单行子查询只能使用单行操作符；多行子查询只能使用多行操作符

单行的查询只能使用返回结果是单行的子查询 ( 单行子查询就是该条子查询执行结束时，只返回一条记录 ( 一行数据 )),

即整个查询语句是单行的则括号里面返回的结果也要是单行的。多行子查询 ( 子查询的结果是多行的 )

这个规范的意思也就是说，结果要能组合起来。 // 其实主要意思是多行操作符操作的多行，所以多行操作符后面得是多行的集合，也就是多行子查询 ( 子查询的结果是多行的集合 )

单行操作符：

= 、 > 、 >= 、 < 、 <= 、 <>

多行操作符有：

IN 等于列表中的任意一个

ANY 和子查询返回的任意一个值比较

ALL 和子查询返回的所有值比较

1). 多行操作符 IN ， in 后面为多行子查询 ( 子查询的结果是多行的 )

-- 查询部门名称为 SALES 和 ACCOUNTING 的员工信息

分析 : 先找到部门编号 , 再通过编号查找

select * from dept where dname in ('SALES','ACCOUNTING');

select * from emp where deptno in (10,30);

==> 变成子查询

SQL> select * from emp where deptno in (select deptno from dept where dname in

('SALES','ACCOUNTING'));

2). 多行操作符 ANY ， any 后面为多行子查询 ( 子查询的结果是多行的 )

-- 查询薪水比 30 号部门任意一个 (any 某一个，即薪水最低的 ) 员工高的员工信息

-- 多行子查询写法， any 后面为多行子查询 ( 子查询的结果是多行的 )

SQL> select * from emp where sal > any (select sal from emp where deptno=30);

-- 单行子查询写法

SQL> select * from emp where sal > (select min(sal) from emp where deptno=30);

3). 多行操作符 ALL ， all 后面为多行子查询 ( 子查询的结果是多行的 )

-- 查询薪水比 30 号部门所有人工资高的员工信息

-- 多行子查询写法， all 后面为多行子查询 ( 子查询的结果是多行的 )

SQL> select * from emp where sal > all (select sal from emp where deptno=30);

-- 单行子查询写法

SQL> select * from emp where sal > (select max(sal) from emp where deptno=30);

8. 子查询中的 null 值

判断一个值等于、不等于空，不能使用 = 和 != 号，而应该使用 is 和 not

-- 查询不是老板的员工信息

思路 先找到谁是老板，取反

-- 得到老板

select * from emp where empno in( select mgr from emp);

-- 取反

SQL> select * from emp where empno not in( select mgr from emp where mgr is not null);//not in 里面不能出现有空值，一旦有，那么结果始终为假

9. 一般先执行子查询 ( 内查询 ) ，再执行主查询 ( 外查询 ) ；但是相关子查询除外

10. 一般不在子查询中使用 order by, 但在 Top-N 分析问题中，必须使用 order by

/*******************************************************************************************/

十一、配置与配置文件 (oracle)

登陆命令 sqlplus scott/11@orcl222 中的 orcl222 来自于

配置文件 tnsnames.ora ，在配置文件中成为服务名

增加新的登陆服务，可以参考该文件中的其他登陆服务来填写服务名， ip ，端口号，实例名

也可以使用工具来完成这个增加的操作 ：

工具名称： oracle net configuration assistant

登陆数据库的工具： pl/sql developer 中的 drop 命令表示的是删除的意思 。

/*******************************************************************************************/

十二、集合运算

1. 集合运算符

集合运算的操作符：

1).A 并 B 并集 对应 sql 关键字 :union( 集合的交集只保留一份 ), union all( 两个集合的内容全部保留 )

SQL> select * from emp where deptno in(10,20) union select * from emp where deptno in(20,30)

SQL> select * from emp where deptno in(10,20) union all select * from emp where deptno in(20,30)

2).A 交 B 交集 对应 sql 关键字 : intersect

SQL> select * from emp where deptno in(10,20) intersect select * from emp where deptno in(20,30)

3).A-B 差集 对应 sql 关键字 : minus

SQL> select * from emp where deptno in(10,20) minus select * from emp where deptno in(20,30)

(from 后面放的是一个集合 ( 表 ))

具体见图 9

2. 集合运算需要注意的问题：

1). 参与运算的各个集合必须列数相同，且类型一致。注意如果不一致则要处理，一般以列数最多的集合为准，

其他集合相对这个列数最多的结合用 null 来补充对应的列，注意列的类型要一致。

2). 采用第一个集合的表头作为最终使用的表头。 别名也只能在第一个集合上起

3). 可以使用括号先执行其中的语句。

3. 示例，按照图 10 显示的样式输出结果：

数据分成三块 ;

1). 各个部门各个工种的工资总额 集合

2). 各个部门的工资总额

3). 总额

SQL> select deptno,job,sum(sal) from emp group by deptno,job union

SQL> select deptno,to_char(null),sum(sal) from emp group by deptno union

SQL> select to_number(null),to_char(null),sum(sal) from emp;

SQL> break on deptno skip 2;// 设置为 去掉相同的部门号两个，这个是报表的处理技巧

SQL> break on null;// 恢复回去 break on null

/*******************************************************************************************/

十三、数据处理

SQL 语言类型：

DML data manlpulation language 数据操纵语言： insert,select,update,delete ，所有的 dml 语句中都可以使用子查询，同时子查询的规则同样适用

DDL data definition language 数据定义语言 :    create,truncate

DCL data control language 数据控制语言 : grant( 赋权 )/revoke( 回收权限 )

1.insert插入数据

语法格式：

INSERT INTO table[(column[,colum...])] VALUES (value{,value...});

value 与 column ( 数目 ) 要对应

1). 可以插入全部列，即所有的列都有新数据的插入，此时不用指明列名

insert into dept values(50,'50name','50loc');

2). 插入部分列

insert into dept(deptno,dname) values(51,'51name');// 隐式插入 Null

insert into dept(deptno,dname,loc) values(51,'51name',null);// 显式插入 Null

3). 地址符号 & 的作用

& 号会提示用户来输入内容，用于插入

insert into dept values(&dpt,&dnm,&loc);

如图 11 ：

这样的好处在于 :

使用 / 来执行上一条语句 (& 号的语句 ) ，则直接提示用户输入数据，这样就不用输入指令就可以插入数据了，

加快了插入数据的速度，

同时还可以用格式化输入，比如加入引号后，则用户输入就不需要再输入引号了：

insert into dept values(&dpt,'&dnm','&loc');

4). 批量新增数据

create table emp10 as select * from emp where 1=2;

复制 emp 表的结构，由于 1=2 条件不会成立，所以表的数据不会被复制

批量插入 10 号部门的信息

insert into emp10 select * from emp where deptno=10;// 将查询返回的结果插入到新表中。

5).insert 也可以使用选择一个集合的方式来插入

insert into dept select 57,'57name','57loc' from dual;

其中 表示选中 列的内容分别为 的记录 ( 特定的一条记录 )

从伪表中去内容等同于直接输入指。

insert into dept values(57,'57name','57loc');

2.update 更新数据

语法格式：

update tablename set column1=val1,column2=val2,... where cond;

对于更新操作来说 , 一般会有一个 where 条件 , 如果没有这个限制条件 , 更新的就是整张表 .

update dept set loc ='51loc',dname='51dname' where deptno=51;

update dept set loc ='51loc',dname='51dname' where deptno=&dpt;// 由用户输入来决定该得的是哪个编号的行

update dept set dname='52name',loc=null where deptno=52;//set 中的等值可以有 null

子查询的规则和注意事项在 DML 语句都适用 !!

3.delete 删除数据

1). 语法格式：

delete from tablename where cond...;

2). 例子：

delete from dept where deptno=52;

3). 注意：

做事情之前要做好备份：

批量新增就是备份了或者用客户端工具拷贝到 excl( 可以拷贝回来 ) 。

4).delete 删除的是表内容，不会删除表结构， // 删除表： drop 命令

注意，删除一条记录后，后面的记录将会移动到这条记录的位置上。

如果说删除的记录还想插回的是原来的位置，则需要开启行移动。

5).delete 和 truncate

delete 和 turncat 两者都可以删除数据

(1).delete 是逐条删除数据 ,truncate 是先摧毁表 , 再重建 . 表的数据会被清空 ..

删除整个表时，效果都一样，但是：

当前数量级的情况下， delete 更快，当数据量大 ( 比如一百万条记录时 ) 的时候， truncate 更快

所以工作的时候，还是用 turncate 。

( 由于 delete 使用频繁， Oracle 对 delete 优化后 delete 快于 truncate, 即 delete 更快的原因是 oracle 对 delete 进行了优化，但是 mysql 里面实际上是 truncate 更快 )

(2).delete 是 DML 语句， truncate 是 DDL 语句。

DML 语句可以闪回 (flashback) ， DDL 语句不可以闪回 ( 回滚 ) 。

（闪回： 做错了一个操作并且 commit 了，对应的撤销行为。了解）

(3). 由于 delete 是逐条操作数据，所以 delete 会产生碎片， truncate 不会产生碎片。

（同样是由于 Oracle 对 delete 进行了优化，让 delete 不产生碎片）。

两个数据之间的数据被删除，删除的数据 ——碎片，整理碎片，数据连续，行移动 【图示】

(4).delete 不会释放空间， truncate 会释放空间

用 delete 删除一张 10M 的表，空间不会释放。而 truncate 会。所以当确定表不再使用，应 truncate

(5).delete 可以回滚 rollback ， truncate 不可以回滚 rollback 。

(6). 【做实验 sql.sql 】：验证 delete 和 truncate 的时效性

关闭时间开关就不会显示 : 已用时间 :xxxx

关闭回显开关就不会显示 : 已选择 x 行

@ 符号代表要执行哪个脚本

语句执行时间记录开关： set timing on/off

回显开关： set feedback on/off

SQL> set feedback off

SQL> set timing off

SQL> @c:\sql.sql

SQL> select count(*) from testdelete;

COUNT(*)

----------

SQL> delete from testdelete;

SQL> rollback;

SQL> select count(*) from testdelete;

COUNT(*)

----------

SQL> set timing on

SQL> set feedback on

SQL> delete from testdelete;

已删除 5000 行。

已用时间 : 00: 00: 00.03

SQL> select count(*) from testdelete;

COUNT(*)

----------

SQL> set feedback off

SQL> set timing off

SQL> @c:\sql.sql

SQL> select count(*) from testdelete;

COUNT(*)

----------

SQL> set timing n

SP2-0265: timing 必须设置为 ON 或 OFF

SQL> set timing on

SQL> set feedback on

SQL> truncate table testdelete;

表被截断。

已用时间 : 00: 00: 00.31

SQL> select count(*) from testdelete;

COUNT(*)

----------

已选择 1 行。

结论：当前数量级的情况下， delete 更快，当数据量大 ( 比如一百万条记录时 ) 的时候， truncate 更快

所以工作的时候，还是用 turncate 。

/*******************************************************************************************/

十四、事务

1. 事务：

若干个操作指令的集合 (DML, 即若干个 DML 语句组成就是事务 )

事物特点：集合中的操作要么一起成功，要么一起失败

所以才有 commit/rollback , 即所有都成功了则提交，否则回滚

2. 事务开启的标志： DML 语句开始

3. 事务结束的标志：

提交结束：

1. 显示提交 commit

2. 隐式提交

执行 DDL 语句后会自动提交

正常退出 :quit 命令等 会自动提交

回滚结束：

1. 显示回滚 rollback

2. 隐式回滚 异常退出，掉电，宕机等情况会自动回滚

4. 事务的 4 大特性 (ACID): 原子性，一致性，隔离性，持久性

原子性 (Atomicity) ：事务中的全部操作在数据库中是不可分割的，要么全部完成，要么均不执行。

一致性 (Consistency) ：几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致。

隔离性 (Isolation) ：事务的执行不受其他事务的干扰，当数据库被多个客户端并发访问时，隔离它们的操 作，防止出现：脏读、幻读、不可重复读。

持久性 (Durability) ：对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出

5. 隔离级别

1). 对于同时运行的多个事务 , 当这些事务访问数据库中相同的数据时 , 如果没有采取必要的隔离机制 , 就会导致各种并发问题 :

(1). 脏读 : 对于两个事物 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段 . 之后 , 若 T2 回滚 , T1 读取的内容就是临时且无效的 .

(2). 不可重复读 : 对于两个事物 T1, T2, T1 读取了一个字段 , 然后 T2 更新了该字段 . 之后 , T1 再次读取同一个字段 , 值就不同了 .

(3). 幻读 : 对于两个事物 T1, T2, T1 从一个表中读取了一个字段 , 然后 T2 在该表中插入了一些新的行 . 之后 , 如果 T1 再次读取同一个表 , 就会多出几行 .

2). 数据库事务的隔离性 : 数据库系统必须具有隔离并发运行各个事务的能力 , 使它们不会相互影响 , 避免各种并发问题 .

3). 一个事务与其他事务隔离的程度称为隔离级别 . 数据库规定了多种事务隔离级别 , 不同隔离级别对应不同的干扰程度 , 隔离级别越高 , 数据一致性就越好 , 但并发性越弱

SQL99 定义 4 中隔离级别：

(1).Read Uncommitted 读未提交数据。

(2).Read Commited 读已提交数据。 （ Oracle 默认）

(3).Repeatable Read 可重复读。 （ MySQL 默认）

(4).Serializable 序列化、串行化。 （查询也要等前一个事务结束）

具体说明见图 12 ：

这 4 种 MySQL 都支持

Oracle 支持的隔离级别： Read Commited （默认）和 Serializable ，以及 Oracle 自定义的 Read Only 三种。

(2).Read Commited 读已经提交，意思是如果没有提交，其他人是读不到的。

级别越高越好，最高级别也就是 (4).Serializable ，三种不希望出现的读 都不会出现，但是这种没人用，因为是串行的，效率太低

Read Only ：由于大多数情况下，在事务操作的过程中，不希望别人也来操作，但是如果将别人的隔离级别设置为 Serializable （串行），但是单线程会导致数据库的性能太差。是应该允许别人来进行 read 操作的。

6. 事务也是关系型数据库的特点，也就是关系型数据库一般都支持事务，非关系型的则不支持

7. 控制事务

除了 commit rollback 外还有 savepoint( 保存点 )

保存点，类似还原点，即可以使用回滚的方式恢复到那一时刻 ( 保存的时刻 ) 。如果都不是自己

要回滚的保存点，那就可以用 rollback 回到最初，也就是直接回滚到上一次 commit 的时候。

如果没有回滚，觉得事务结束了，则直接提交即可，代表当前的事务结束了。

具体见图 13 ：

使用示例：

savepoint aaa;

rollback to savepoint aaa;// 回滚到保存点 aaa

如果保存点设置名称重复，则会删除之前的那个保存点。

一但 commit 之后， savepoint 将失效。

回退到某个保存点后，则不能在回退到该保存点之后的保存点了。也就是说，不会回退到比当前保存点更新的保存点。

/*******************************************************************************************/

十五、 Top-N问题 相关子查询

1. 【第一题】：找到员工表中工资最高的前三名，要求按排名递增的顺序输出

——涉及 Top-N 分析问题

Top-N 问题 : 取前 3 名

分页问题：取 5-8 名

1).Top-N 问题分析

(1). 一般不在子查询中使用 order by, 但是在 top-n 分析问题中，必须使用 order by

(2).rownum 行号 ( 伪列 ) ，也就是查询这个就会显示行号出来。

(3).rownum 随着集合的创建就会存在，不会随着后面修改，也就是无论怎么排序显示出来，记录对应的行号是不会变的

(4). 由于 rownum 是按照默认顺序生成，所以只能使用 <,<= 符号，不能使用 >,>= 符号。

2).Top-N 问题答案

SQL> select rownum,empno,ename,sal from ( select * from emp order by sal desc ) e where rownum <=3;

3). 分页问题分析

分页问题：取 5-8 名

对新产生的集合 (rownum 小于 8 的 ) 中的 rownum 取别名 R, 这样新产生的列 R 就不是伪列了，就可以使用 R>4 来过滤了。

4). 分页问题答案

SQL> select *

from (select rownum r, empno, ename, sal

from (select * from emp order by sal desc) e

where rownum <= 8)

where r > 4;

2. 【第二题提示】：找到 emp 表中薪水大于本部门平均薪水的员工

1). 两张表中没有相同的列，即没有冲突，所以直接选择查询即可：

先得到各个部门的平均薪水

select deptno,avg(sal) from emp group by deptno

把上述结果当成一个集合 ( 表 )

SQL>select empno,ename,sal,avgsal

from (select deptno,avg(sal) avgsal from emp group by deptno ) a ,emp e

where a.deptno = e.deptno

and e.sal > a.avgsal

2). 相关子查询写法 :

相关子查询先执行主查询，再执行子查询

原因在于，子查询的执行需要的东西由主查询产生，即子查询依赖于主查询。

--- 求 10 号部门 大于 10 号部门平均薪水的员工

SQL>select empno,ename,sal,(select avg(sal) from emp where deptno=10) avgsal

from emp

where deptno=10

and sal > (select avg(sal) from emp where deptno=10)

===> 将 10 号部门替换为 emp 表每条记录的部门：这个就是相关子查询

SQL>select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal

from emp e

where sal > (select avg(sal) from emp where deptno= e.deptno)// 注意依赖： e.deptno

多表查询与子查询都可以解决的时候哪个更好？

一般情况下是多表查询，多表查询是以空间换时间，多表查询一下把数据都加载过来，子查询和数据库需多次的交互，

很明显多表查询和数据库的交互更少，一般情况下 sql 优化的原则都是尽量减少和数据库的交互

3. 【第三题提示】：统计每年入职的员工个数

——员工的入职年份是已知条件—— 1980 、 1981 、 1982 、 1987 这 4 个。

要统计每年入职的人数，一定要知道每个员工的入职日期，可以通过查询 hiredate 列来得到

结合查询结果，以 1981 年为例，如何统计出 81 年入职的有多少个人呢？可以从写 C 程序的角度入手。

思路：定义一个计数器 count=0; 有一个 81 年的员工，就 +1 ，不是 81 的就 +0; 最后查看 count 的值就可以了。

求和，使用 sum 函数，内部逻辑： sum(if 是 81 年 then  +1  else  +0)

也就是取员工的 hiredate 列的“年”那一部分，与 81 比较，进行判断。

to_char(hiredate, ‘ yyyy ’ ) 得到年，与‘ 1981 ’进行比较

select to_char(hiredate,'yyyy') from emp ;

select decode(to_char(hiredate,'yyyy'),'1981',1,0) from emp ;

select sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981" from emp ;-- 搞定一列

SQL>select count(*) "Total",

sum(decode(to_char(hiredate, 'yyyy'), '1980', 1, 0)) "1980",

sum(decode(to_char(hiredate, 'yyyy'), '1981', 1, 0)) "1981",

sum(decode(to_char(hiredate, 'yyyy'), '1982', 1, 0)) "1982",

sum(decode(to_char(hiredate, 'yyyy'), '1987', 1, 0)) "1987"

from emp;

数字不能用作别名，得转换为字符型，即加上双引号。

集合的效率一般比较低，即多表组成集合的方式这种效率一般比较低。

具体见《课堂练习 —子查询 _ 多表查询 .docx 》

4. 统计部门人数小于 4 的部门情况，显示部门编号，名称，部门人数

select d.deptno,d.dname,count(empno)

from emp e,dept d

where e.deptno(+)=d.deptno

group by d.deptno,d.name

having count(empno) < 4

order by 1;

//1 表示按照第一列进行排序

复习的时候可以从多表查询进行复习

/*******************************************************************************************/

十六、表的管理

表的管理包括：创建表，删除表，修改表，重命名表，表的约束。

1.创建表

表是由行和列组成的。所以创建表的时候就要决定有哪些列。

1). 列 ( 里面的 ) 的数据类型，（表示列里面的内容的数据类型）

创建表时， 列所使用的数据类型 (oracl) ：

VARCHAR2 不定长字符，定义了 10 ，存了 5 个，则只占用 5

CHAR 定长字符，定义了 CHAR10 ，则占用的就是 10 个内存

NUMBER( 整数，小数 )

ROWID 行地址，是一个伪列 ( 用的时候直接取就行了，就在那里了 ) 。

具体见图 14 ：

2). 创建表的前提条件：表空间 + 权限

Oracle 默认存储是都存为大写，意思是表名默认存储在数据库里的是大写的，即：

select * from tab;// 看到的表名里都是大写的

也就是说，由于对于字符串大小写敏感，所以查找表名的时候要大写，即：

select * from tab where tname = 'EMP'

3). 示例：

(1). 创建一个表

create table t1(id number(4),tname varchar2(30),hiredate date default sysdate);//t1 表名， id 是第一列的列名 ,number

// 是第一列列内容的类型为数值型，并指定长度为 4 ，同时 defalut 的意思是， insert 时不想放入数据时，会使用这个默认值

// 即第三列插入时不用填： insert into t1(id,tname) values(1,'1234sdf');

SQL> create table t1(id number(4),tname varchar2(30),hiredate date default

sysdate);

表已创建。

SQL> desc t1

名称 是否为空 ? 类型

----------------------------------------------------- -------- ----------------

--------------------

ID NUMBER(4)

TNAME VARCHAR2(30)

HIREDATE DATE

SQL> insert into t1(id,tname) values(1,'yekai');

已创建 1 行。

SQL> select * from t1;

ID TNAME HIREDATE

---------- ------------------------------ --------------

1 yekai 16-2 月 -17

(2). 创建一个与 t1 相同表结构的表

create table t2 as select * from t1 where 1=2;//1=2 不成立，集合为空，所以内容不会拷贝过去，只是把表结构 ( 格式 ) 拷贝过去

2.删除表

// 这个前面说过了

当表被删除：

数据和结构都被删除

所有正在运行的相关事物被提交

所有相关索引被删除

DROP TABLE 语句不能回滚，但是可以闪回

1).drop table t2;// 这个是删除到回收站里面了。此时查询这个表也是查询不到的。

oracle 有回收站机制 ，所以会看到： BIN$A8EsHYSkSiKQfnHrOw4E7g==$0

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

BIN$A8EsHYSkSiKQfnHrOw4E7g==$0 TABLE

2). 查看回收站

SQL> show recyclebin;//sql plus 工具里才看得到结果

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

---------------- ------------------------------ ------------ -------------------

T2 BIN$A8EsHYSkSiKQfnHrOw4E7g==$0 TABLE 2017-02-16:09:45:54

3). 闪回表， (flashback 闪回，需要开启行移动，但是表的闪回不需要 )

flashback table t2 to before drop;//oracle 10g 才支持的功能，从回收站里恢复 ( 闪回 )

注意：并不是所有的用户都有 “回收站”，对于没有回收站的用户（管理员）来说，删除操作是不可逆的。

4). 不经过回收站删除

SQL> drop table t3 purge;// 不经过回收站删除，类似 win ： shift+delete;

SQL> purge recyclebin;// 清空回收站 , 注意回收站是在服务器中的，如果清除回收站，则有可能把别人的也清除了。

回收站已清空。

3.修改表

alter table t1 add email varchar2(30);//t1 表增加一列 ,email 是新列的名字， varchar2(30) 列内容的类型

修改列属性 ( 内容的类型 )

alter table t1 modify email varchar2(40);// 如果表非空，长度往小改，这时如果有数据长度大雨想要修改的长度值，则会修改失败

alter table t1 rename column email to address;

alter table t1 drop column address;

4.重命名表

rename  t1 to t3;// 表是数据库的对象， rename 是针对对象的。

5.表的约束

1). 表的约束的含义：对录入进来的数据有要求

2). 作用：保持数据的一致性。

3). 有 5 种约束 ( 要求 ) ：

(1).Primary Key 主键约束

要求：唯一 + 非空

(2).Foreign Key 外键约束

要求：主表里有这个东西，子表才可以引用这个东西。比如部门表就是主表，员工表就是子表，员工表依赖于部门表，比如

有个 10 号部门的员工，则必须先有 10 号部门。

外键还有一个要求：作为子表里的外键 ( 比如部门编号 ) 的字段必须是主表里面的主键。

外键也是关系型数据库的特点，即两个表之间有关联。

(3). Unique 唯一性约束

要求：不能重复。是主键要求内的一种要求。

(4).Not Null 非空约束

要求：不能为空。是主键要求内的一种要求。

(5).Check 检查性约束

检查输入数据的格式，是否符合要求。

4).constraint 关键字，用于创建约束时，给约束起名

5). 示例 :

create table student(sid number(4) constraint student_PK primary key ,

sname varchar2(30) not null,

sex  varchar2(30) check (sex in (' 男 ',' 女 ')),

sal number check (sal > 10000),

deptno  number(2) references dept(deptno) on delete cascade

//constraint 是约束关键字表示后面是约束， student_PK 是约束名 ( 规范 : 表名 _ 约束类型 ) ， primary key 表示这个约束是主键

//not null 表示约束非空， check 是约束中的检查 ,Foreign Key( 可以省略 , 由 references 去指定依赖是哪个表哪一列 ) 外键约束

例如：部门表 dept 和员工表 emp ，不应该存在不属于任何一个部门的员工。用来约束两张表的关系。

注意：外键约束会限制删除操作，即如果父表的记录被子表引用的话，父表的记录默认不能删除。

默认方式要删除的解决方法：

1 ） 先将子表的内容删除，然后在删除父表。

2 ） 将子表外键一列设置为 NULL 值，断开引用关系，然后删除父表。

无论哪种方法，都要在两个表进行操作。所以定义外键时，可以通过 references 指定如下参数：

—— ON DELETE CASCADE ：当删除父表时，如发现父表内容被子表引用， ( 级联 ) 删除子表所有引用该内容的记录。

—— ON DELETE SET NULL ：当删除父表时，会先把子表中对应外键值置空 (SET NULL) ，再删除父表。

多数情况下，使用 SET NULL 方法，防止子表列被删除，数据出错。

推荐使用的外键方式 : 默认 , 先删除子表 , 再删除主表 .

6). 约束演示 :

(1). 违反外键约束

SQL> insert into student values(1,'yekai',' 男 ',100000,50);

insert into student values(1,'yekai',' 男 ',100000,50)

第 1 行出现错误 :

ORA-02291: 违反完整约束条件 (SCOTT.SYS_C0011218) - 未找到父项关键字

//SYS_C0011218 系统自动生成的约束名

(2). 违反主键约束

SQL> insert into student values(1,'fuhongxue',' 男 ',100000,40);

insert into student values(1,'fuhongxue',' 男 ',100000,40)

第 1 行出现错误 :

ORA-00001: 违反唯一约束条件 (SCOTT.STUDENT_PK)//STUDENT_PK 自己起的约束名，最好约束名都自己起这样便于自己去查找

SQL> insert into student values(2,'fuhongxue',' 男 ',10000,40);

insert into student values(2,'fuhongxue',' 男 ',10000,40)

第 1 行出现错误 :

ORA-02290: 违反检查约束条件 (SCOTT.SYS_C0011216)

SQL> insert into student values(2,'fuhongxue',' 男 ',10001,40);

(3). 违反性别检查

SQL> insert into student values(3,'luxiaojia',' 无 ',10001,40);

insert into student values(3,'luxiaojia',' 无 ',10001,40)

第 1 行出现错误 :

ORA-02290: 违反检查约束条件 (SCOTT.SYS_C0011215)

-- 违反非空检查

SQL> insert into student values(3,null,' 无 ',10001,40);

insert into student values(3,null,' 无 ',10001,40)

第 1 行出现错误 :

ORA-01400: 无法将 NULL 插入 ("SCOTT"."STUDENT"."SNAME")

insert into student values(3,'luxiaojia',' 男 ',10001,40);

(4). 级联删除

SQL> delete from dept where deptno=40;

已删除 1 行。

SQL> select * from student;

推荐使用的外键方式 : 默认 , 先删除子表 , 再删除主表 .

7). 查询表里面的约束：

select constraint_name,constraint_Type,search_condition from user_constraints where table_name = 'STUDENT';

表删除后，这些约束就没了。即约束的生命周期是表存在。

/*******************************************************************************************/

十七、其他数据库对象

数据库的对象：表、视图、索引、序列、同义词

：存储过程、存储函数、触发器、包、包体、数据库链路 (datalink) 、快照。 (12 个 )

表 基本的数据存储集合，由行和列组成。

视图 从表中抽出的逻辑上相关的数据集合。

序列 提供有规律的数值。

索引 提高查询的效率

同义词 给对象起别名

数据库对象中的表相关的前面已经讲了，接下来是其他数据库对象

视图：从表中抽出的逻辑上相关的数据集合。

所以： 1. 视图基于表。 2. 视图是逻辑概念。 3. 视图本身没有数据。

使用示例：

1). 创建 ( 视图只能创建、删除、替换。（不能修改，修改则要加括号里的 替换 replace ，

同时还有一个作用如果创建的这个视图已经存在加了这个替换关键字就会替换掉旧的 ( 其实也就是相当于修改旧的视图了 ) ，就不会报错了）

如：刚刚创建的 empincomeview ，其他语句不变，将 create 一行改写成 ) ：

create (or replace) view empincomeview  as

select e.empno, e.ename, e.sal, e.sal*12 annualsal, e.sal*12+nvl(comm, 0) income, d.dname

rom emp e, dept d

where e.deptno = d.deptno;

2). 使用：

select * from empincomeview;

没有权限 , 需要先登录 dba

C:\Users\Administrator>sqlplus sys/sys@orcl100 as sysdba

授权给 scott

grant create view to scott;

视图本身没有数据 , 逻辑概念 , 数据依赖于表 . 即数据是从表里面来的。

3). 作用 : 简化复杂查询 , 隔离数据访问 ( 从视图中获取，也就是查询时不知道具体的查询语句，只有创建者才知道 ) 。

相当于作了替换，用视图替换复杂的查询语句，

即执行查询视图，相当于执行查询视图中的语句

总结一句话：不通过视图做 insert 、 update 、 delete 操作。因为视图提供的目的就是为了简化查询。

4). 删除视图： SQL> drop view testview; // 不加“ purge ”关键字 ( 因为视图本身都没有数据，即不会经过回收站 ) 。

序列 提供有规律的数值。

1). 可以理解成数组：默认，从 [1] 开始，长度 [20] [1, 2, 3, 4, 5, 6, … , 20] 在内存中。

20 个是在内存中，如果用完了则会再加载 20 个，也就是缓冲长度是 20 *

由于序列是被保存在内存 ( 即实例中 ) 中，访问内存的速率要高于访问硬盘的速率。所以序列可以提高效率

2).create sequence myseq;//myseq 为序列的名字

3). 序列的 2 个值： nextval,currval , 引用时语法，序列名 .xxxval ,:

获取序列会使用到这两个值，注意，可以理解为序列里面蕴含着一个指针，

使用 nextval 获取序列值的时候，获取的是序列中指针指向的下一个元素，同时获取后指针会加一，即

此时再使用 currval 获取的就是刚才 nextval 获取的值，因为指针移动了，

同时 下次再使用 nextval 获取序列值的时候，获取的是下下一个元素。

当表刚创建好时，此时指针指向的是第一个元素的前一个位置，即使用 currval 获取不到任何元素，

要使用 nextval 才可以开始使用

SQL> create sequence myseq;

SQL> select myseq.currval from dual;// 获取当前序列元素

select myseq.currval from dual

第 1 行出现错误 :

ORA-08002: 序列 MYSEQ.CURRVAL 尚未在此会话中定义

SQL> select myseq.nextval from dual;// 获取下一个序列元素

NEXTVAL

----------

drop sequence myseq;// 删除序列

4). 序列的作用 : 一般给主键使用字段使用

insert into dept values(myseq.nextval,myseq.nextval||'name',myseq.nextval||'loc');// 序列给主键字段使用

循环执行这个语句，即可实现插入不同的数据，也就是可以插入唯一的主键

注意上述语句是一条语句，所以 nextval 是一样的值，也就是引用 nextval 是在一条语句里面跑的时候值是一样的。

5). 查询哪一列是主键的其他方式：

ed dept;//dept 是表名，执行这个命令后再选择 keys 时 则会有对应列信息的展示。

具体见图 15 ：

6). 什么情况下主键不连续 ?

一个序列多个表使用

执行 rollback , 序列不进行回滚 ( 原因是序列在实例中，所以回滚不了 ) ，

所以记录恢复到以前了，即之前获取到的序列值没了，同时序列没有恢复，也就是再次获取的序列值

是回滚删除掉的记录中的序列的下一个，那么这个序列值是和删除掉的序列值连续的，因为是删除掉的，所以此时

就不连续了。

1). 索引需要占用空间 , 需要权限 .

作用，提高查询效率， , 提高查询效率的原因就是有序

由于原有的数据是不能进行排序的 ( 不能修改数据 ) ，所以需要创建一个有序的，这个创建的有序对象就是索引

2). 关于索引的创建：

对索引值进行排序，然后再把索引值和行地址关联起来，这样 ( 即创建索引后 ) 就可以，

通过索引确定行地址 (rowid ，代表当前记录在内存中地址 ), 通过行地址得到数据 .

3). 创建索引：

--create index indexname on tablename(columnname1,columnname2,...);

create index myinde on emp(ename);//create index 创建索引， myinde 索引名， on emp(ename) 使用 emp(ename) 作为索引

索引一旦创建就形成了索引表 , 由 oracle 自行维护 . 默认索引是 btree 索引，还有一种索引是位图索引

4). 删除索引：

drop index  indexname;

5). 使用索引：

在查询条件里有索引的条件，即要使用 作为索引值的列 来进行查询，

如上诉例子中就要使用 ename 来进行查询才会用好索引。

create index indexname on tablename(columnname1,columnname2,...);

// 创建了多个列作为索引，称为联合索引，联合索引的使用是多个索引值都要作为查询条件，即

columnname1,columnname2,... 都要作为查询条件。

6). 索引是为了提高查询效率的，所以：

以下情况可以创建索引 :

列中数据值分布范围很广

列经常在 WHERE 子句或连接条件中出现

表经常被访问而且数据量很大 ，访问的数据大概占数据总量的 2% 到 4%

下列情况不要创建索引 ( 没有意义，浪费空间 ):

列不经常作为连接条件 ( 查询条件 ) 或出现在 WHERE 子句中

查询的数据大于 2% 到 4% // 这个指标只是建议值

表经常更新

4.同义词

给对象起别名

1). 同义词的意思

实际生产环境 ( 工作环境，即数据库的数据都是真实的 ) 中，一般开发者之类的用户都是只有查询权限，

如果想要修改则必须通过公司开发的修改工具去修改，修改工具会记录具体谁去修改了，以及修改的内容都会记录。

并且当前用户并不是所有的数据都可以查询的，如果想查询其他用户的数据，要 select * from 用户名 . 表名

但是有时候用户名过长，所以一般在查询用户建立同义词。

同义词的意思是，在当前用户下建立一个和其他用户相同名字的对象，这个对象名叫同义词

比如在当前用户下创建一个也叫 emp 的 ( 可以认为是表，但是实际上是同义词 ) ，然后查询的时候就可以直接 select * from emp;

相当于 select * from 用户名 . 表名，即相当于作了一个替换。

所以同义词的作用也是 : 简化复杂查询 , 隔离数据访问

2). 创建示例： scott 用户访问 hr 用户中的表，

(scott 用户看不到 数据库中 hr 用户能看到的表。默认情况下创建一个用户并在该用户下创建的表其他用户是看不到的，

即使用 select * from 其他用户名 . 表名 也无法访问，

想看到的话，需要其他用户或者系统管理员去授予权限：

grant select,update,insert on employees to 其他用户 ;

// 给予其他用户 select,update,insert 当前用户下 employees 表的权限 , 此时就可以通过 select * from 其他用户名 . 表名 查看了 )

(1). 在 hr 用户下给 scott 用户赋权： grant select on employees to scott;

能查看其他用户的表的时候，就可以建立同义词了：

(2).scott 用户 ( 查询用户 ) 下：

此时可以查看 hr 用户 employees, 因为已经赋权

SQL> select * from hr.employees;

创建同义词

SQL> create synonym employees for hr.employees;

//create synonym 创建同义词 ， employees 同义词名， for hr.employees 等同于 hr 用户下的 employees 表

(3). 创建权限不足 , 需要 dba 用户

给 scott 创建同义词的权限：

grant create synonym to scott;

3). 删除同义词

drop synonym employees;

4). 新创建一个用户 , 再创建一个查询用户 , 如何在查询用户批量创建新创建用户的同义词 ?

思路 : tab 字典 (tab 字典下可以看到当前用户下所有的表 )===> 批量生成 create 语句 .

//tab 字典下可以看到当前用户下所有的表，然后考虑如何用这些表批量生成 create 语句：

reate synonym employees for hr.employees;