马上注册，结交更多数据大咖，获取更多知识干货，轻松玩转大数据
您需要 登录 才可以下载或查看，没有帐号？立即注册
x
Oracle数据库笔记 Jack Chaing作者：Jack Chaing 如果大家想看Word版本的可以去下载：Word排版比较清晰一些。
http://download.csdn.net/detail/jack__chiang/9810532此笔记是作者本人去年开始从一个DBA新人的学习笔记，积累至今，希望拿出来给那些对DBA有兴趣的童孩学习，大家一起努力嘛。
此笔记记录了作者工作学习中从零基础的学习的记录，和从中遇见的问题与问题的解决！很高兴大家来疯狂源代码大家庭交流学习！
感谢支持，鞠躬！！转载请注明作者哦！！谢谢。
此文章不断更新！！谢谢大家支持！！ 一、Oracle的使用
1)、启动
[AppleScript] 纯文本查看 复制代码 *DQL：数据查询语言
*DML：数据操作语言
*DDL：数据定义语言
DCL：数据控制语言
TPL：事务处理语言
CCL：指针控制语言
![]()
1、登录 Win+R—cmd—>sqlplus “/as sysdba” //以sysdba用户登录，这样可以管理权限，添加用户等
Win+R—cmd—>sqlplus username/password //以指定用户名密码登录
win+R —> cmd —–> sqlplus //按照提示，输入用户名密码
2、创建用户
create user username identified by password;
![]()
3、给与权限
grant resource,connect to username;
resource:
create trigger
create sequence
create type
create procedure
create cluster
create operator
create indextype
create table
connect:
create session 4、切换用户显示当前用户
conn username/password 切换用户
show user 显示当前用户名 5、使用$后面跟window命令符可以使用window命令
cls清屏cls清屏 exit退出 6、在oracle软件中，启动数据库，需要以管理员身份启动 7、查询当前系统时间
select sysdate from dual;
8、导入数据文件（sql语句表数据之类的）
因为数据文件默认语言环境为英语，当前系统是中文，中英文时间显示不一致，导致倒入失败，所以 需要先修改语言环境
alter session set nls_date_language=english;
alter session set nls_language=english;
然后开始导入文件使用：@ 文件全路径名 或者 start 文件全路径名
9、查看当前用户的所有表
select table_name from user_tables;
10、查看当前表结构
desc 表名字; 11、Oracle服务意思
![]()
1.OracleDBConsoleorcl oem控制台的服务进程
2.OracleJobSchedulerXE 定时器的服务进程
3.OracleOraDb10g_home1iSQL*Plus isql*plus的服务进程
4.OracleXETNSListener 监听器的服务进程
5.OracleServiceORCL 数据库服务进程 2)、Select 基本操作1、distinct关键字
distinct跟在select后面，代表去除重复的，这个重复是整体重复的。
select 子句后面指定要查询的列
from 后面跟要查询的表
2、select语句可以对指定的列的所有值进行算术运算。
语法：
select 要计算列的名字 运算符 数字from 表名; 3、取别名
select 列名 要修改的名字 from 表明
select 列名 as 要修改的名字 from 表明 4、使用||合并多个列之间添加字符串
要添加字符靠近列名的那一边需要加||，并且要添加的字符串需要用单引号
select ‘找到的id为：’||id||’ 找到的名字为’||last_name as Name from s_emp;
![]() 5、对空的值进行替换（替换的值一定是要同类型的）
select nvl(要判断是否有空值的列名,要替换列的同类型的值) from 表名
select nvl(要判断是否有空值的列名,不为空显示的值,为空显示的值) from 表名
![]() 3)、sqlplus命令1、在当前操作的命令行追加内容
a 追加的内容
2、在当前操作的命令行修改内容
c /命令行存在的内容/要被修改的内容
3、清空当前缓存的命令
clear buffer 清空当前缓存咋命令
4、删除当前操作行
del 要删除的行号
5、当前操作命令的下一行插入内容
i 直接i回车就可以在下一行添加内容
6、查看缓存命令
l 行号 ：查看指定行号的缓存内容
直接输入l显示的是缓存中所有的
7、输入系统终端命令
Linux环境下使用 ！后面跟着终端命令
Window环境下使用 后面跟终端命令例如调用window下的清屏使用 后面跟终端命令 例如调用window下的清屏使用cls
8、执行缓存命令
使用/就可以执行sql语句
9、修改对应行的内容
输入要修改行号，后面直接跟要修改内容就可以
1 要修改的内容
10、退出缓存输入
直接在要准备添加行回车就好
11、保存你缓存的命令到文件中
save 要保存的文件路径 append(这个代表追加到当前要保存的文件路径里面)
12、从文件提取到缓存
get 文件路径
13、执行文件中的sql命令
start 文件路径 或者 @ 文件路径
14、编辑文档
edit 文件路径 用系统默认的东西打开文件
15、保存你的sql语句和执行结果保存到文件（是追加保存的）
你从开始执行spool开始就开始记录了，知道spool off 关闭为止，所输入的所有东西在文件都有记录
spool 文件路径
sql1
result1
sql2
result2
…
spool off 关闭spool功能 4)、格式化输出结果1、定义要格式化的列或者给列起的名字
使用column关键字
colu 要修改的列名 format a15 //代表列显示的时候最多为15个子节长 2、colu first_name heading ‘Employ|Name’ format a5
给first_name 取了个名字叫做Employ|Name，|表示换行，a5表示5个字节 3、column salary JUSTIFY LEFT FORMAT $99,990.00
![]()
Justify left代表列名向左对其
999,999.999代表每一位为0−9之间的数“，”是代表分割符，千分位999,999.999代表每一位为0-9之间的数“，”是代表分割符，千分位 000,000.000代表每一位为0-9之间的数，不足的用0补齐 4、column start_date format a8 null ‘not date’;
![]()
代表列名如果为空，就用not date文本代替
format后面不能直接跟null 在a8后面写
这里的和替换空和nvl是有区别的，nvl必须要求类型匹配才可以替换 5、column显示所有对列格式的设置
![]() 6、显示指定列的设置情况
column 列名 7、删除指定列的设置和删除全部列的设置
column 列名 clear
clear column //删除全部 8、出错的时候
出错的时候不能显示就只显示####### set linesize(line) 设置sqlplus输出的最大行宽 –
set pagesize 设置页面的最大行数
set newpage 设置页面之间的空行数
spool sqlplus屏幕的文件输入输出命令
edit 使用自定义的编辑器编辑指定文件 其实也可以使用ed.修改后直接使用/即可执行
save 保存当前session最近的sql语句至指定的文件中
host 返回到操作系统环境，类似!
start或@ 执行文件中的命令
edit 使用自定义的编辑器编辑指定文件
define_editor 自定义sqlplus里的编辑器
exit或quit 退出sqlplus column
column是sqlplus里最实用的一个命令，很多时候sql语句输出的列宽度不合适而影响查看，都需要
用到这个命令来更改select语句中指定列的宽度和标题。大部分时候，我们可以简写column为col即
可，主要有以下两种用法：
?修改列宽度
column c1 format a20 –将列c1（字符型）显示最大宽度调整为20个字符
column c1 format 9999999 –将列c1（num型）显示最大宽度调整为7个字符
?修改列标题
column c1 heading c2 –将c1的列名输出为c2 5)、select条件查询Where子句的使用（如果报from找不到，就要注意from是不是和某个列写到一起了！） [AppleScript] 纯文本查看 复制代码 语法：
select 要查询的列名
from 表名
where 列名或别名
逻辑操作符
表达式 。。。
1、作用：对SQL语句返回的数据集进行筛选;
2、位置：紧跟在from子句后
3、 内容：由一至多个限定条件组成，限定条件由表达式, 比较符, 字面值组成。
4、 所有字符串和日期要用单引号括起来，数值不需要单引号。
日期在Oracle里有特定的格式，’DD-MON-YY’（具体看日期的显示格式），
否则作为一个字符串。
5、 几种常见的操作符：
1》逻辑比较操作符
= > < >= <= != <> ^=（后面三种都是不等于）
需求：查找工资大于1000的所有员工的last_name和工资。
需求：查找不在45号部门工作的所有员工的id，last_name和dept_id,并且按照dept_id 升序进行排序
select id,last_name,dept_id
from s_emp
where dept_id<>45
order by dept_id;
2》sql比较操作符（在where后面写，order by 前面）
between and：在什么范围之内
in(xx,xx,xx):在一个列表中的值
like:模糊查询，即值不是精确的值的时候使用
通配符，即可以代替任何内容的符号
%：通配0到多个字符
![]()
_: 当且仅当通配一个字符
转义字符：
默认为,可以指定 指定的时候用escape 符号指明即可,转义字符只能转义后面的一 个字符 ,下面这个代表是以_开头后面任意字符的last_name
![]()
between 1 and 4: 包括起止值。限定内容为1到4。
![]()
in (1,2,4): 限定内容为1,2,4。
is null：对null值操作特定义的操作符，不能使用=否定的Is not nul，not in ,not like ,not butween
3》逻辑操作符
当条件有多个的时候使用
and：且逻辑
or：或逻辑
注意：and逻辑比or逻辑要高
not：非逻辑
需求：
1.查找员工id在[5,20]之间的所有员工的id和last_name
![]()
or
![]()
2.查找员工id不在[5,20]之间的所有员工的id和last_name
![]()
or
这个需要注意的是not匹配的是最近的一样，优先级相对于and是高的，所以加括号
![]()
3.查找在43或者44号部门的员工的id和last_name;
![]()
or
![]()
需求：查看员工名字以C字母开头的员工的id，工资。
![]() 练习：查看员工名字长度不小于5，且第四个字母为n字母的员工id和工资
![]() 需求：查看员工名字中包含一个_的员工id和工资
![]()
需求：查看员工提成为为空的员工的id和名字
![]() 需求：查看员工部门id为41且职位名称为Stock Clerk（存库管理员）的员工id和名字
![]() 练习：查看员工部门为41 或者 44号部门 且工资大于1000的员工id和名字
![]() 查看员工部门为41且工资大于1000 或者 44号部门的员工id和名字
![]() 6)、预定义函数函数：这里的函数相当于java中写好的一些方法，有名字，可以传递参数，实现某一项具体功能。
函数分为:
1.单行函数
1.字符函数
2.日期函数
3.数字函数
4.转换函数
2.分组函数(后面的章节再做学习)
学前须知：
哑表dual
dual是一个虚拟表，辅助查找和运算。通常用在select语句中，作为查询的目标表结构，oracle保证dual里面永远只有一条记录。
例如:
显示1+1的结果,可以看出,dual很多时候是为了构成select的标准语法
select 1+1 from dual;
![]() 1、字符函数
1) LOWER:转换成小写
2) UPPER:转换成大写
3) INITCAP:首字母变成大写，其余都小写
4) CONCAT:字符串的连接
5) SUBSTR(str,start,length)或者SUBSTR(str,start):字符串的截取
6) LENGTH:求字符串的长度
7) NVL : 转换null的值。(前边已经用过)
nvl2:
8) DECODE： [AppleScript] 纯文本查看 复制代码 **LOWER 把字符转为小写**
例如:把'HELLO'转换为小写
select lower('HELLO')
from dual;
例如:把s_emp表中的last_name列的值转换为小写
select lower(last_name)
from s_emp;
upper 把字符转换为大写
例如:把’world’转换为大写
select upper(‘world’)
from dual; 例如:把s_emp表中的last_name列的值转换为大写
select upper(last_name)
from s_emp; [AppleScript] 纯文本查看 复制代码 例如:查询s_emp表中名字为Ngao的人信息
这样是查不到:
select last_name,salary,dept_id
from s_emp
where last_name='NGAO';
这样就可以查询到了:
select last_name,salary,dept_id
from s_emp
where upper(last_name)='NGAO';
initcap 把字符串首字母大写
例如:把’hELLO’转换为首字母大写,其余字母小写
select initcap(‘hELLO’)
from dual; concat 把俩个字符串连接在一起(类似之前的||的作用)
例如:把’hello’和’world’俩个字符串连接到一起,并且起个别名为msg
select concat(‘hello’,’world’) msg
from dual;
[AppleScript] 纯文本查看 复制代码 例如:把first_name和last_name俩个列的值连接到一起
select concat(first_name,last_name)
from s_emp;
substr 截取字符串
例如:截取’hello’字符串,从第2个字符开始,截取后面的3个字符
select substr(‘hello’,2,3)
from dual;
可以找到第二个字母开头的是什么字母，其他同理
![]() length 获得字符串长度
例如:获得’world’字符串的长度
select length(‘world’)
from dual; [AppleScript] 纯文本查看 复制代码 例如:获得s_emp表中last_name列的每个值的字符长度
select length(last_name)
from s_emp;
nvl 替换列中为null的值
在前面的章节已经使用过了
nvl(要输出的列名，为空的时候要被替换的值) //要替换的值类型必须要和之前保持一致
![]()
例子：
1.查找last_name全小写的值为velasquez的员工的lastname
![]()
2.查找last_name的长度>10的所有员工的last_name
![]() instr查找字符串
instr(‘1234;5678’,’;’,1,1)-1
解释：1. ‘1234;5677’==>可以是表达式，也可以是具体数据
2. ‘;’==>为分离的标志，这里为两组数据中的“;”号
3. 第一个1为从左边开始，如果为-1，则从右边开始。
4. 第二个1为“;”出现的第几次。 2、数字函数 1) ROUND:四舍五入
2) TRUNC:截取，不进行四舍五入
3) MOD:取余 **round 四舍五入**
（切记-1代表保存小数点后一位，0保留到各位，1保留到10 round(arg1,arg2)第一个参数表示要进行四舍五入操作的数字
[AppleScript] 纯文本查看 复制代码 第二个参数表示保留到哪一位(负数代表小数点之前，0，正数代表小数点之后)0代表保留到个位！！-1代表保存到十位
select round(45.67) from dual; 46
select round(45.67,1) from dual; 45.7
select round(45.67,2) from dual; 45.67
select round(45.67,-1) from dual; 50
select round(45.67,-2) from dual; 0
select round(55.67,-2) from dual; 100
例如
保留到小数点后面2位
select round(45.923,2)
from dual;
保留到个位 (个十百千万...)
select round(45.923,0)
from dual;
保留到十位 (个十百千万...)
select round(45.923,-1)
from dual;
**trunc 截取到某一位**
trunc(arg1,arg2)
和round的用法一样,但是trunc只舍去不进位
select trunc(45.67) from dual; 45
select trunc(45.67,1) from dual; 45.6
select trunc(45.67,2) from dual; 45.67
select trunc(45.67,-1) from dual; 40
select trunc(45.67,-2) from dual; 0
select trunc(55.67,-2) from dual; 0
[AppleScript] 纯文本查看 复制代码 例如:
截取到小数点后面2位
select trunc(45.923,2)
from dual;
截取到个位 (个十百千万...)
select trunc(45.923,0)
from dual;
截取到十位 (个十百千万...)
select trunc(45.923,-1)
from dual;
mod 取余
mod(arg1,arg2)
第一个参数表示要进行取余操作的数字
第二个参数表示参数1和谁取余
“` 例如: 把10和3进行取余 (10除以3然后获取余数) select mod(10,3) from dual;
3、日期函数
1) MONTHS_BETWEEN:两个日期之间的月数，如果是正数前面的值大于后面的值
2) ADD_MONTHS:在指定日期上增加月数
3) NEXT_DAY:指定日期的下一个星期几是哪天
4) LAST_DAY:指定日期的最后一天
5) ROUND:对指定日期进行四舍五入
6) TRUNC:对指定日期进行截取 sysdate关键字
表示系统的当前时间
例如:
显示时间:当前时间
![]() 注意:sysdate进行加减操作的时候,单位是天 例如: 显示时间:明天的这个时候
![]()
例如: 显示时间:昨天的这个时候
![]()
例如: 显示时间:1小时之后的时候
![]()
months_between 俩个时间点之间相差多少个月(单位是月)
例如:
30天之后和现在相差多少个月
![]() add_months 返回一个日期数据:表示一个时间点,往后推x月的日期
例如:
‘01-2月-2016’往后推2个月
![]() 例如:
当前时间往后推4个月 ![]()
next_day 返回一个日期数据:表示一个时间点后的下一个星期几在哪一天
例如:
当前时间的下一个星期5是哪一个天
![]()
注意:
如果要使用’FRIDAY’,那么需要把当前会话的语言环境修改为英文 last_day 返回一个日期数据:表示一个日期所在月份的最后一天
例如:
当前日期所在月份的最后一天
![]() round 对日期进四舍五入,返回操作后的日期数据。逢16日往月份进一,逢7月往年份进一
round(sysdate,’year/y/yy/yyy/yyyy’) 年 7月节点
round(sysdate,’mm/month’) 月 16号节点
round(sysdate,’d/day’) 星期 星期四节点
round(sysdate,’dd’) : 天 -》 12点节点
例如:
把当前日期四舍五入到月（年月日.时 分 秒 把这个看错数字就可以了）
今天2016年9月5日四舍五入到月，就要看日是否大于16？大于进一，不大于不进一，同时舍弃为1
![]()
把当前日期四舍五入到年
大致算一下，今天已经9月了，所以满足大于节点7进一位，同时舍弃年前面的值
![]() trunc 对日期进行截取 和round类似,但是只舍弃不进位
[AppleScript] 纯文本查看 复制代码 trunc（sysdate,'yyyy/year'） --返回当年第一天。
trunc（sysdate,'mm/month'） --返回当月第一天。
trunc（sysdate,'d/day'） --返回当前星期的第一天。
trunc（sysdate,'dd'）--返回当前年月日
截取和round基本是一样的只是，不进位而已。 4、类型转换函数
1).TO_CHAR 将日期或者数值转换成字符串
2).TO_NUMBER 将字符串转换成数字
3).TO_DATE 将日期字符串转换成日期 to_char 把日期转换为字符
例如:
把当前日期按照指定格式转换为字符串
![]()
[AppleScript] 纯文本查看 复制代码 日期格式：
yyyy/YYYY：四位数的年份
rrrr：四位数的年份
yy：两位数的年份
rr：两位数的年份
mm：两位数的月份（数字）
D：一周的第几天
DD：一月的第几天
DDD ：一年的第几天
YEAR：英文的年份
MONTH：英文全称的月份
mon：英文简写的月份
ddsp：英文的第几天
ddspth：英文序列数的第几天
DAY：全英文的星期
DY：简写的英文星期
hh：小时
mi：分钟
ss：秒
AM:上午
PM:下午
练习：显示当前时间，查询当前时间是这年的第几天？是这个星期的第几天？是这个月的第几天？ select '这年的第一几天：'||to_char(sysdate,'DDD')||' 这个星期的第几天：' ||to_char(sysdate,'D')||' 这个月的第几天：'||to_char(sysdate,'DD') as 结果from dual;
(注意拼接字符串，只有一个别名，是一个整体)
例如:
测试常见的一些日期数据转换为字符串的格式 select to_char(sysdate,'yyyy MM D DD DDD YEAR MONTH ddsp ddspth DAY DY') from dual;select to_char(sysdate,'dd-mm-yy')from dual;select to_char(sysdate,'dd-mm-yy HH24:MI:SS AM')from dual;
千年虫:
[AppleScript] 纯文本查看 复制代码 在早期的计算机的程序中规定了的年份仅用两位数来表示。也就是说，假如是1971年，在计算机里就会被表示为71，但是到了2000年的时候这个情况就出现了问题，计算机就会将其年份表示为00。这样的话计算机内部对年份的计算就会出现问题。这个事情当时被称为千年虫
数据库中表示日期中年份的有俩种: yy和rr
之前一直使用的时候yy格式,后来才有的rr格式
yy表示使用一个俩位数表示当前年份:
1990 ---yy数据库格式---> 90
1968 ---yy数据库格式---> 68
1979 ---yy数据库格式---> 79
![]()
[AppleScript] 纯文本查看 复制代码 如果日期中的年份采用的格式为rr，并且只提供了最后2位年份，那么年份中的前两位数字就由两部分共同确定：提供年份的两位数字(指定年)，数据库服务器上当前日期中年份的后2位数字(当年)。确定指定年所在世纪的规则如下：
规则1 如果指定年在00~49之间，并且当前年份在00~49之间，那么指定年的世纪就与当前年份的世纪相同。因此，指定年的前两位数字就等于当前年份的前两位数字。例如，如果指定年为15，而当前年份为2007，那么指定年就是2015。
规则2 如果指定年在50~99之间，并且当前年份在00~49之间，那么指定年的世纪就等于当前年份的世纪减去1。因此，指定年的前两位数字等于当前年份的前两位数字减去1。例如，如果指定年为75，而当前年份为2007，那么指定年就是1975。
规则3 如果指定年在00~49之间，并且当前年份在50~99之间，那么指定年的世纪就等于当前年份的世纪加上1。因此，指定年的前两位数字等于当前年份的前两位数字加上1。例如，如果指定年为15，而当前年份为2075，那么指定年就是2115。
规则4 如果指定年在50~99之间，并且当前年份在50~99之间，那么指定年的世纪就与当前年份的世纪相同。因此，指定年的前两位数字就等于当前年份的前两位数字。例如，如果指定年为55，而当前年份为2075，那么指定年就是2055。
注意:rr格式并没有完全的解决俩位数年份保存的问题,思考里面还有哪些问题存在。
to_char 把数字转换为字符
L : 本地货币符号
: :
. : 小数点
, : 千分符
9 : 0-9
0 : 0-9, 如果位数不足，强制补0 例如: select to_char(salary,'$999,999.00') from s_emp;
![]()
fm表示去除结果显示中的开始的空格
![]()
L表示系统本地的货币符号 select to_char(salary,'fmL999,999.00') from s_emp;
![]()
to_number 把字符转换为数字
例如: select to_number('1000') from dual;
![]()
//这个写法是错的 abc不能转换为数字
select to_number(‘abc’)
from dual;
![]()
to_date 把字符转换为日期
.TO_DATE(char, [‘fmt’]):例如
select TO_DATE (‘10-September-1992’,’dd-Month-YYYY’) from dual
.使用format的元素格式
![]()
例如:
select to_date(‘10-12-2016’,’dd-mm-yyyy’)
from dual; select to_date('25-5月-95','dd-month-yy') from dual; //session语言环境设置为英文下面可以运行 select to_date('25-MAY-95','dd-MONTH-yy') from dual;
![]()
这个是不能改用什么连接的
![]()
oracle数据库中表示一个日期数据的几种方式 1.sysdate 2.oracle默认的日期格式 例如:'25-MAY-95' 3.to_date函数转换
5、extract()函数oracle中extract()函数从oracle 9i中引入,用于从一个date或者interval类型中截取到特定的部分
//语法如下：
[AppleScript] 纯文本查看 复制代码 { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }
| { TIMEZONE_HOUR | TIMEZONE_MINUTE }
| { TIMEZONE_REGION | TIMEZONE_ABBR } FROM { date_value | interval_value } )
//我们只可以从一个date类型中截取 year,month,day(date日期的格式为yyyy-mm-dd);
//我们只可以从一个 timestamp with time zone 的数据类型中截取TIMEZONE_HOUR和TIMEZONE_MINUTE;
select extract(year from date'2011-05-17') year from dual;
YEAR
----------
2011
select extract(month from date'2011-05-17') month from dual;
MONTH
----------
5
select extract(day from date'2011-05-17') day from dual;
DAY
----------
17
//获取两个日期之间的具体时间间隔，extract函数是最好的选择 [AppleScript] 纯文本查看 复制代码 select extract(day from dt2-dt1) day
,extract(hour from dt2-dt1) hour
,extract(minute from dt2-dt1) minute
,extract(second from dt2-dt1) second
from (
select to_timestamp('2011-02-04 15:07:00','yyyy-mm-dd hh24:mi:ss') dt1
,to_timestamp('2011-05-17 19:08:46','yyyy-mm-dd hh24:mi:ss') dt2
from dual)
/ DAY HOUR MINUTE SECOND
102 4 1 46
–
[AppleScript] 纯文本查看 复制代码 ,extract(month from systimestamp) month
,extract(day from systimestamp) day
,extract(minute from systimestamp) minute
,extract(second from systimestamp) second
,extract(timezone_hour from systimestamp) th
,extract(timezone_minute from systimestamp) tm
,extract(timezone_region from systimestamp) tr
,extract(timezone_abbr from systimestamp) ta
from dual /
YEAR MONTH DAY MINUTE SECOND TH TM TR TA
2011 5 17 7 14.843 8 0 UNKNOWN UNK
// 6、exists和 not exists用法EXISTS(包括 NOT EXISTS )子句的返回值是一个BOOL值。 EXISTS内部有一个子查询语句(SELECT … FROM…)， 我将其称为EXIST的内查询语句。其内查询语句返回一个结果集。 EXISTS子句根据其内查询语句的结果集空或者非空，返回一个布尔值。 一种通俗的可以理解为：将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行行可作为外查询的结果行，否则不能作为结果。 Exists是子查询的一种条件形式，通过判断Exists的选择表达式（括号内的部分）的结果，如果存在一行或多行结果记录，则Exists整个子查询结果为真，否则为假。由于我们采用Exists来实现子查询，只需要关心是否存在满足条件的记录，所以选择表达式的选择列表采用*来实现，当然，你也可以在选择列表指明具体的某些列，但这些列将在整个搜索过程中被忽略。 Exists实例 SQL 代码 select Resc_id from dbo.Res_Coachwhere EXISTS (select * from Res_Coach where Resc_id is null)
查询原理：
遍历dbo.Res_Coach每一条，同时处理where条件（EXISTS (select * from Res_Coach where Resc_id=0) 判断结果为true或者false），为true时拿出该条，false时，放弃该条记录。 SQL 代码 – 1、 where条件中的子查询和主查询没关系 select Resc_idfrom dbo.Res_Coachwhere EXISTS (select Rese_id from dbo.Res_Excellent where Rese_id Is null )
– 2、 where条件中得子查询和主查询有关系 select Resc_idfrom dbo.Res_Coachwhere EXISTS (select Resc_id from dbo.Res_Coach where Resc_id Is null )
实例备注：不管where条件中得子查询和主查询有没有关系，遍历主查询中得每一条时，判断where条件，exists结果为真，where条件返回true，拿出该条记录，where条件返回false， 不返回该记录。 Exists 和 In 的选择
如果查询的两个表大小相当，那么用in和exists差别不大。如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in 7、sys.wm_concat无效问题，自己写一个运行project后后台报错：
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select wmsys.wm_concat(RoleID) from dlsys.tcrole a,dlsys.tcUnit b,dlsys.tcHuman c where a.UnitID = b.UnitID and b.UnitID = c.UnitID and c.HumanID = 161 and RoleID not in(152)]; nested exception isJava.sql.SQLException: ORA-00904: “WMSYS”.”WM_CONCAT”: 标识符无效 原因：
11gr2和12C上已经摒弃了wm_concat函数，当时我们很多程序员在程序中确使用了该函数，导致程序出现错误，为了减轻程序员修改程序的工作量，只有通过手工创建个wm_concat函数，来临时解决该问题，但是注意，及时创建了该函数，在使用的过程中，也需要用to_char(wm_concat())方式，才能完全替代之前的应用。 解决办法：
一.解锁sys用户 alter user sys account unlock;
二.创建包、包体和函数
以sys用户登录数据库，执行下面的命令
[AppleScript] 纯文本查看 复制代码 CREATE OR REPLACE TYPE WM_CONCAT_IMPL AS OBJECT
-- AUTHID CURRENT_USER AS OBJECT
(
CURR_STR VARCHAR2(32767),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
P1 IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,
SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER
);
/
–定义类型body: [AppleScript] 纯文本查看 复制代码 CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL
IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL)
RETURN NUMBER
IS
BEGIN
SCTX := WM_CONCAT_IMPL(NULL) ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
P1 IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
IF(CURR_STR IS NOT NULL) THEN
CURR_STR := CURR_STR || ',' || P1;
ELSE
CURR_STR := P1;
END IF;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURNVALUE := CURR_STR ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,
SCTX2 IN WM_CONCAT_IMPL)
RETURN NUMBER
IS
BEGIN
IF(SCTX2.CURR_STR IS NOT NULL) THEN
SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR ;
END IF;
RETURN ODCICONST.SUCCESS;
END;
END;
/
--自定义行变列函数:
CREATE OR REPLACE FUNCTION wm_concat(P1 VARCHAR2)
RETURN VARCHAR2 AGGREGATE USING WM_CONCAT_IMPL ;
/
三.创建同义词并授权 [AppleScript] 纯文本查看 复制代码 create public synonym WM_CONCAT_IMPL for sys.WM_CONCAT_IMPL
/
create public synonym wm_concat for sys.wm_concat
/
grant execute on WM_CONCAT_IMPL to public
/
grant execute on wm_concat to public
/
已经实践过证明可行再使用该函数的时候不能带用户名
create table test(id number,name varchar2(20));
insert into test values(1,'a');
insert into test values(1,'b');
insert into test values(1,'c');
insert into test values(2,'d');
insert into test values(2,'e');
insert into test values(3,'f');
insert into test values(3,'g');
四.使用子定义的wm_concat select id,wm_concat(name) from test group by id;
9、Oracle DECODE函数
使用方法：
1、比较大小 select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值
sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1
例如：
变量1=10，变量2=20
则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。 其实decode第一参数是if判断语句的第一个参数，第二个参数是if判断语句的第二个参数
If(12-12==-1)else
12
if else(12-12==1)else
1…. 1、插入测试数据 insert into student_score (name,subject,score)values('zhang san','Chinese',90);
insert into student_score (name,subject,score)values('zhang san','Mathematics',80);
insert into student_score (name,subject,score)values('zhang san','English',79);
测试一： select name,subject,decode(subject, 'Chinese',score,0) from student_score; 如果是中文课程的话， 显示分数， 其他课程分数为零。
这条SQL 看上去使用意义不大。 测试二： select name,sum(decode(subject, 'Chinese',score,0)) as CHINESE from student_score group by name;
统计中文课程的分数。看上去有点意义。
总体看来, decode 的使用看上去和case when 有点类似。如果只是用作以上两种状况，看上去意义不大。
select name,sum(decode(subject, ‘Chinese’,score,0)) as CHINESE from student_score group by name;
select name,score as CHINESE from student_score;
使用的两句使用后的效果一样，看上去使用decode 多此一举。 2、行转列-有意义的使用
往以上table 再插入一些其他学生的成绩：
[AppleScript] 纯文本查看 复制代码 insert into student_score (name,subject,score)values('li shi','Chinese',96);
insert into student_score (name,subject,score)values('li shi','Mathematics',86);
insert into student_score (name,subject,score)values('li shi','English',76);
insert into student_score (name,subject,score)values('wang wu','Chinese',92);
insert into student_score (name,subject,score)values('wang wu','Mathematics',82);
insert into student_score (name,subject,score)values('wang wu','English',72);
使用以下SQL: select name, sum(decode(subject, 'Chinese', nvl(score, 0), 0)) "Chinese", sum(decode(subject, 'Mathematics', nvl(score, 0), 0)) "Mathematics", sum(decode(subject, 'English', nvl(score, 0), 0)) "English" from student_score group by name;
将行的数据转化为列， 是不是很有意义了。
使用case then 也可以达到相同的效果。 [AppleScript] 纯文本查看 复制代码 select name,
sum(decode(subject, 'Chinese', nvl(score, 0), 0)) "Chinese",
sum(decode(subject, 'Mathematics', nvl(score, 0), 0)) "Mathematics",
sum(decode(subject, 'English', nvl(score, 0), 0)) "English"
from student_score
group by name;
7)、多表查询概念：所谓多表查询，又称表联合查询，即一条语句涉及到的表有多张，数据通过特定的连接进行联合显示。语法： select column_name,.... from table1,table2 条件。。。。
学前须知：
笛卡尔积
在数学中，两个集合X和Y的笛卡尓积（Cartesian product），又称直积，表示为X × Y.
假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。
在数据库中,如果直接查询俩张表,那么其查询结果就会产生笛卡尔积
例如: select *from 表1,表2;
链接查询分类：
为了避免笛卡尔基的产生，我们可以使用连接查询来解决这个问题。
连接查询分为： 1、等值连接2、不等值连接3、外连接 a)左外连接 b)右外连接 c)全链接4、自链接
1、等值连接：
利用一张表中的某个列的值和另外一张表中的某个列的值相等的关系，把两张表连接起来 语法：select tb_name.col_name,tb_name.col_name,...from tb_name,tb_name,...where tb_name.col_name = tb_name.col_nameand tb_name.col_name = tb_name.col_name...
![]()
需求：查看每个员工的id，last_name以及所属部门名称
（要用别名，就用表的别名，在用列明名的话会有问题的）
![]()
需求：查找员工工资所属的等级名称
![]()
如果想使用关键字进行连接查询在from后面写join on(on想当与where)
![]()
2、不等值连接
使用的是除=以外的操作符号的多表查询
例如：使用between and
select t1.col_name,t2.col_namefrom t1,t2where t1.col_name between t2.col_nameand t2.col_name;
3、外连接
Outer是可以省略的。加号放在值少的一方，就可以让少的一方值显示，一般来说+所在列的位置，的相反位置就是当前的连接是什么连接！反过来
当使用一个表的记录在另外一张表中不存在的时候，我们仍旧需要显示，使用外链接就可以了
平常规律：加号写法跟在
外连接分为：
右外连接（right join/rigth outer join）
左外连接（right join/left outer join）
全外连接（full join /full outer join） 右外连接的语法： Select这是是Join right的写法，join left是反过来的，就不写了
![]()
这个是+的写法
![]()
加号只能写一个，写两个不代表全部外连接；
![]()
全外连接：就是两个表中不存在的都不会显示
4、自链接
自己和自己连
![]() 5、集合连接
:对查询结果集的操作。 union:将上下结果取并集，去除掉重复的记录（重复的只显示一次）
![]()
union all：将上下结果全部显示
![]()
minus：取差集 A-B可以用来做分页查询
![]()
intersect：取交集（取相同的结果以上前提条件是：两个结果集中查询的列要完全一致。
![]()
6、oracle伪列 rownum
伪列rownum，就像表中的列一样，但是在表中并不存储。伪列只能查询，不能进行增删改操作。它会根据返回的结果为每一条数据生成一个序列化的数字。rownum是oracle才有的伪列
在 Oracle 里面 ROWNUM 主要有可以完成两个任务： · 取得第一行数据；· 取得前 N 行数据。 范例：查询第一行数据 rownum 只能等于1 如果让其等于其他数 则查不到数据例如: select last_name from s_emp where rownum=1
范例：查询前 N 行记录
rownum 大于0 可以查到所有的数 如果让其大于其他数 则查不到数据 例如: select last_name from s_emp where rownum>0 rownum 可以小于任何数 例如: select last_name from s_emp
范例：取出 emp 表之中的 5~10 条 SELECT * FROM (SELECT empno,ename,job,ROWNUM rnFROM empWHERE ROWNUM<=10) tempWHERE temp.rn>5 ;
![]()
7、Oracle伪列RowID
现在每一行的记录都发现有自己的数据列，而除了这些数据列之外，还存在有每一行数据的唯一的物理地址，而这 个物理地址就只能够通过 ROWID 取得。 那么每一个 ROWID 数据都是包含有存储数据的，以：“AAAR3qAAEAAAACHAAC”为例做一个简单解释：
· “AAAR3q”：数据的对象编号；
· “AAE”：数据保存的相对文件编号；
· “AAAACH”：数据保存的块号；
· “AAC”：保存的数据行号。 数据库之中的所有数据都是在磁盘之中，保存，所以来讲，根据不同的数据会分配不同的空间，而 ROWID 就可以清楚的记录这些空间的信息。 面试题：现在有一张数据表，由于设计的时候缺少一些限制，同时后期使用的过程之中出现了大量的重复数据，要求将重复的数据删除掉，只保留最原始增加的数据。 准备过程：
1、 为了方便观察问题，首先将 dept 表复制为 mydept 表； CREATE TABLE mydept AS SELECT * FROM dept ;
2、 观察现在的 mydept 表之中的数据和 ROWID（这个时候的数据就是最重要保存的数据）。 SELECT ROWID ,deptno,dname,loc FROM mydept ;
![]()
4、向 mydept 数据表之中增加重复数据。 INSERT INTO mydept(deptno,dname,loc) VALUES (10,'ACCOUNTING','NEW YORK') ; INSERT INTO mydept(deptno,dname,loc) VALUES (10,'ACCOUNTING','NEW YORK') ; INSERT INTO mydept(deptno,dname,loc) VALUES (30,'SALES','CHICAGO') ;INSERT INTO mydept(deptno,dname,loc) VALUES (30,'SALES','CHICAGO') ;
5、 INSERT INTO mydept(deptno,dname,loc) VALUES (30,'SALES','CHICAGO') ;
正式问题：
将 mydept 表之中现在所有的重复删除掉，保留最早增加的数据。 SELECT ROWID ,deptno,dname,loc FROM mydept ;
![]()
解决问题：
1、在 mydept 表之中可以发现重复数据，而且重复数据都重复的很有规律，在 deptno、dname、loc 都重复，于是换个思路，查询出所有数据之中最早的 ROWID，（MIN(ROWID)）
SELECT deptno,dname,loc,MIN(ROWID) FROM mydept GROUP BY deptno,dname,loc ;2、 不在此范围的数据删除就行了，使用 NOT IN，而且之前的查询返回的是多行单列。 DELETE FROM mydept WHERE ROWID NOT IN ( SELECT MIN(ROWID) FROM mydept GROUP BY deptno,dname,loc) ;
![]()
PS：如果你没遇见此类题目，那么你就不用复习了，如果觉得会遇见，你就好好看。
在以后的分析之中，ROWID 依然会出现，只需要记住，ROWID 就像身份证一样，是作为一行具体数据的唯一的物 理标记出现的。
1.Rowid的显示形式
我们从rowid伪列里select出来的rowid是基于base64编码，一共有18位，分为4部分：
OOOOOOFFFBBBBBBRRR
其中：
OOOOOO： 六位表示data object id，根据object id可以确定segment。关于data object id和object id的区别，请参考http://www.orawh.com/index.php/archives/62
FFF： 三位表示相对文件号。根据该相对文件号可以得到绝对文件号，从而确定datafile。关于相对文件号和绝对文件号，请参考http://blog.itpub.net/post/330/22749
BBBBBB：六位表示data block number。这里的data block number是相对于datafile的编号，而不是相对于tablespace的编号。
RRR：三位表示row number。
Oracle提供了dbm_rowid来进行rowid的一些转换计算。
SQL> create table test(id int,name varchar2(30));
Table created.
SQL> insert into test values(1,’a’);
1 row created.
SQL> commit;
Commit complete.
SQL> select rowid from test; ROWIDAAAGbEAAHAAAAB8AAA
SQL> select dbms_rowid.rowid_object(rowid) obj#,
2 dbms_rowid.rowid_relative_fno(rowid) rfile#,
3 dbms_rowid.rowid_block_number(rowid) block#,
4 dbms_rowid.rowid_row_number(rowid) row#,
5 dbms_rowid.rowid_to_absolute_fno(rowid,’SYS’,’TEST’) file#
6 from test;
OBJ# RFILE# BLOCK# ROW# FILE#
26308 7 124 0 7 2. 如何从rowid计算得到obj#,rfile#,block#,row#
rowid是base64编码的，用A~Z a~z 0~9 + /共64个字符表示。A表示0，B表示1，……，a表示26，……，0表示52，……，+表示62，/表示63可以将其看做一个64进制的数。
所以，
obj#=AAAGbE=6*64^2+27*64+4=26308
rfile#=AAH=7
block#=AAAAB8=64+60=124
row#=AAA=0 3. 如何从obj#,rfile#,block#,row#计算得到rowid
实际上就是将十进制数转化成64进制数，当然，从二进制转化的规则比较简单点。
将二进制数从右到左，6个bit一组，然后将这6个bit组转成10进制数，就是A~Z a~z 0~9 + /这64个字符的位置（从0开始），替换成base64的字符即可。
obj#=26308=110 011011 000100=6 27 4=G b E，补足成6位base64编码，左边填0，也就是A，结果为AAAGbE
rfile#=7=111=7=H，补足成3位，得到AAH
block#=124=1 111100=1 60=B 8，补足成6位，得到AAAAB8
row#=0，3位AAA
合起来就是AAAGbEAAHAAAAB8AAA 4. Rowid的内部存储格式
虽然我们从rowid伪列中select出来的rowid是以base64字符显示的，但在oracie内部存储的时候还是以原值的二进制表示的。一个扩展rowid采用10个byte来存储，共80bit，其中obj#32bit,rfile#10bit,block#22bit,row#16bit。所以相对文件号不能超过1023，也就是一个表空间的数据文件不能超过1023个（不存在文件号为0的文件），一个datafile只能有2^22=4M个block,，一个block中不能超过2^16＝64K行数据。而一个数据库内不能有超过2^32=4G个object。
SQL> select dump(rowid,16) from test;
DUMP(ROWID,16) Base64编码说明
Base64编码要求把3个8位字节（3*8=24）转化为4个6位的字节（4*6=24），之后在6位的前面补两个0，形成8位一个字节的形式。 如果剩下的字符不足3个字节，则用0填充，输出字符使用’=’，因此编码后输出的文本末尾可能会出现1或2个’=’。
为了保证所输出的编码位可读字符，Base64制定了一个编码表，以便进行统一转换。编码表的大小为2^6=64，这也是Base64名称的由来。 8)、组查询概念：
所谓组查询即将数据按照某列或者某些列相同的值进行分组，然后对该组的数据进行组函数运用，针对每一组返回一个结果。
note:
1.组函数可以出现的位置： select子句和having 子句
2.使用group by 将将行划分成若干小组。
3.having子句用来限制组结果的返回，只能出现在Group by语句中。
语法： select ...from ...where ...group by col_name,col_namehaving ...order by...group by col_name:即将数据按照col_name相同值进行分组
组函数
常见有5个：
avg：求平均值
count：求总数
max：最大值
min：最小值
sum：求和
stddev：求标准差
ariance：求方差 avg([distinct] column )/sum([distinct] column) :可以作用在存储数字数据的列上。 max(),min():可以作用在任意类型的数据之上。
对字符型数据的最大值，是按照首字母由A～Z的顺序排列，越往后，其值越大。当然，对于汉字则是按照其全拼拼音排列的，若首字符相同，则比较下一个字符，以此类推。 count([distinct] column | *) : count(*) ： 统计表中所有的行数count(column) : 返回所有非空行的行数
练习：
查看各个部门的最高工资
![]()
查看各个部门的员工数
![]()
查看各个部门的平均工资
![]()
查看各个部门的最低工资
![]() group by 子句：（注意：如果select查询语句中有组函数出现，并且还查询了其他的列，则此列必须出现在group by子句中！！）
1.用来将表中的行划分成若干更小的组
2.出现在select子句中，但是没有出现在组函数中的列必须出现在group by子句中
3.出现在group by中的列不一定出现在select子句中。
4.group by子句中的列出现在select结果中，表意性比较强。
5.当group by子句中出现多列的时候，表示按照从左至右的顺序进行分组，即先按照第一列分组， 然后再第一列分好的组里面 按照第二列进行分组，以此类推。
6.限制组结果的返回一定使用having不能使用where。 需求：
1.查看各部门，职称相同的人的平均工资。
![]()
2.查找部门平均工资>1000的所有部门的id和平均工资
目标： 部门id , avg(salary)
from : s_emp
分组： 部门平均工资
group by dept_id
组结果的返回：
部门平均工资>1000
组合：
![]()
需求：查看所有部门的部门工资总和，按照部门工资的降序排序
![]() having: 限制组结果的返回。
1.如果希望限制组结果的返回，那么直接使用having子句跟在group by 子句之后。 需求：查看部门平均工资大于1000的部门id
![]()
需求：
1、查看职称不以VP开头的所有员工，
![]() 2.并且将他们以职称分组，
![]() 3.求各职称的工资总和，
![]() 4.将工资总合>5000的职称和工资总合显示出来。
![]() 9)、排序1、排序Order By（注意：order by后面的元素可以给每一个指定升序和降序）
Order by语句始终位于select语句的最后面 2、order by后可以跟什么：
列名，列的别名，表达式，
排序不指明的会按照select后面的列名，按顺序排序 3、order by后面可以跟多列
表示先按第一列排序，
如第一列有相同值再按
第二列进行排序，如前二列
均有相同值，则按第三列进行排序… 4、默认是asc升序
指明desc是降序的 5、空值排序是最大的
升序排序的时候，空值放在最后
降序排序的时候，空值放在最前面的 6、出现在order by后面的列，不一定出现在select查询的列里面 10)、子查询所谓子查询，就是一个select语句中嵌套了另外一个或者多个select语句 [AppleScript] 纯文本查看 复制代码 应用场景：
1、一条查询语句的查询条件依赖另外一条查询语句的查询结果
2、一条查询的结果作为另外一个查询语句的查询表（查询依据，也就是和1是反过来）
3、在DML操作中使用查询（后期介绍）
子查询的基本原则：
1、在查询中可以有单行查询和多行子查询。
2、在子查询中可以出现操作符的左边或者右边
3、子查询在很多的sql命令中都可以使用。
4、嵌套查询（子查询）先执行，然后将结果递给主查询。
一、比较值不确定，需要另外一个select语句执行后才能得到，使用子查询 [AppleScript] 纯文本查看 复制代码 语法：
select ...
from ...
where col_name 比较操作符 (
select ...
from ...
where ...
group by ...
having...
)
group by ...
having...
order by ...
1、单值子查询：子查询的结果为1个
需求：
1.查询和Simith职称相同的所有员工的last_name和职称
分析步骤： [AppleScript] 纯文本查看 复制代码 1.确定最终查询结果(目标/主查询)：查询员工的last_name和title
from : s_emp
条件 ： title = Smith的职称
select last_name,title
from s_emp
where title = ?
2.确定条件(子查询)：Smith的职称
from : s_emp
条件 ：last_name = 'Smith';
select title
from s_emp
where last_name = 'Smith';
3.组合
![]()
2.查看工资大于Chang员工工资的所有员工的id和名字。
![]()
3.查看员工工资小于平均工资的所有员工的id和名字
![]() [AppleScript] 纯文本查看 复制代码 例如：查找和Smith同一个部门的员工的id和last_name
1.最终查询目标 ： 员工的id,last_name
from : s_emp
查询条件： s_emp.dept_id = Smith所在部门id
select id,last_name
from s_emp
where s_emp.dept_id = ?;
2.子查询的目标：部门id
from s_emp
where last_name = 'Smith';
select dept_id
from s_emp
where last_name = 'Smith';
3. 组合：
![]()
2、多值子查询：子查询的结果为多个 需求：
1.查询所在区域为2号区域的所有部门的员工的id和last_name [AppleScript] 纯文本查看 复制代码 1.确定最终查询结果： 员工的id, last_name
from : s_emp
条件 ：s_emp.dept_id in (?);
select id,last_name
from s_emp
where dept_id in ?
2.确定条件：所在区域为2号部门
子查询：部门id
from : s_dept
条件: region_id = 2;
select id
from s_dept
where region_id = 2;
3.组合：
![]()
问题：比如上面的题这样写，就会出现问题，他把所有dept_id不为空的全部打印出来了
![]()
如果子查询的列写成了主查询的条件，不会报错！会把这个列不为空的查询出来
子查询出现情况二：
查询的内容作为一个表存在 语法： select .... from (select .... from ....) b where ......
练习：查询各部门的id，name 和部门员工的平均工资 [AppleScript] 纯文本查看 复制代码 1、查询目标：
需要部门的id，部门的name ------ 从 s_dept表中
部门员工的平均工资 avg(salary) --------- salary只有s_emp表中有
条件 ： 部门id，name和部门 员工，因此要求部门的id跟员工所在部门的id相等才 连接
select id,name, 平均工资
from s_dept , ?
where s_dept.id = ?.dept_id;
2、查询条件
select(dept_id,avg(salary) sal)
from s_emp
group by dept_id;
3、组合
![]()
3、子查询返回多行单列
如果说子查询返回的是多行单列数据，实际上就相当于提供了一个数据的查询范围。那么如果要想针对于范围查询， 则要使用三个判断符号：IN、ANY、ALL。
1、IN操作：
指的是在指定范围内。也可以使用 NOT IN 不在范围之内 2、ANY操作（不看） =ANY：此功能和IN的操作完全相同>ANY：比查询的最小值要大<ANY：比查询的最大值要小3、ALL操作（不看） >ALL：此功能比子查询返回的最大值还要大<ALL：此功能比子查询返回的最小值还要小11)、运行时变量1、定义：运行的时变量可以让我们和sql语言之间有个交互，容许我们动态传递参数
2、语法 &var
3、运行时变量可以出现在任意位置 例如： select &colName1,&colName2 from &tbName where &colname = &colValue; 例如： select id,last_name from s_emp where id = &id; 运行时，服务器会提示： 输入 id 的值: 当输入完成按下回车： 原值 3: where id = &id 新值 3: where id = 2![]()
例如： select id,last_name from s_emp where last_name = &name;（直接这样写是不需要转义的）
![]()
note:
1.&代表取值。&varName，代表取varName这个变量的值，如果这个变量值，之前不存在，那么系统会提示输入这个变量的值。如果存在，直接取值。
2.set verify on ，打开交互提示，如果打开，会显示old和new value。现在默认都是打开的。使用set verify off，关闭 4.定义变量：
define[def] varName = value;
![]()
查看定义的变量：
define[def] [varName]; 例如： 1. define name=zhangsan 定义一个变量名字为name,值为zhangsan 运行select语句时，如果语句中遇到&name会自动替换为zhangsan 例如： select id,last_name from s_emp where last_name='&name';只要变量存在的值，会被自动替换 ![]()
2.def/define : 查看当前环境中定义的所有变量
![]()
3.def/define name : 查看变量name的值。
![]()
如果不想在select语句中&name的外边使用”，则可以在定义变量name时写成definename=”’zhangsan”’;‘可以用来转义’（其实写一个双引号，里面写一个单引号也是可以的”‘zhangsan’”）
![]() 存在def里面的值是这样的显示
![]() 5.使用accept用来定义带数据类型和提示符的变量 accept varName : 代表定义一个变量名字为varName,当按下回车时需要用户输入值。accept varName dataType : 代表定义一个带数据类型的变量例如：accept varNum number :代表定义一个变量名字为varNum，类型为Number，当输入类型不是数字类型是报错，提示继续输入。（类型要和你输入的类型保持一致，如果不设置类型默认的是字符）
![]()
prompt：当输入变量时，给用户的提示信息。
accept myNum number prompt 区域id： 定义一个Number类型的变量myNum，当按下回车时提示区域id：
accept nyName char prompt 名字:（注意日期传的是当前系统的格式dd-mon-rr mon是简写的月份）
![]() 如果希望插入的值是隐藏的，可以在后边写上HIDE：accept num number prompt 密码： HIDE![]()
6.取消变量的定义（和define的取消是一样的）
undefine varName; 7.向脚本文件传递参数（也就是读取文件的时候，在后赋值）
在文件中参数使用&n(n代表数字)来表示取第几个参数值，然后再调用文件执行的时候使用
@file val….(多个参数值使用空格分开。) 文件内容
![]() SQL
![]() 当然比较方便的一种就是把，accept写到文件的开始，先提示用户赋值，然后在通过赋的值，找。
![]() 12)、数据模型和数据库设计软件开发的步骤可大致分为:
1.需求分析
2.系统设计
3.编码实现
4.系统测试
5.运行维护
student 老师，辅导员class 多对一 sno name age gender id clsname 辅导老师，。。1 1 jd1613 xxx2 1 jd1613 xxx系统设计中一个重要的环节就是数据库设计数据库设计的时候需要先进行数据建模(实体关系图 E-R图)数据建模的依据就是前期所做的需求分析数据建模
1.Model of system in client’s mind
2.Entity model of client’s model
3.Table model of entity model
4.Tables on disk 实体-关系图
实体-关系图(Entity Relationship Diagram),也称为E-R图,提供了表示实体类型、属性和关系的方法，用来描述现实世界的概念模型。 构成E-R图的基本要素是实体、属性和关系 实体(Entity)：实体用来表示具有相同特征和性质的事物(类似于java的类(域对象))，实体由实体名和实体属性来表示。 属性(Attribute)：实体所具有的某一特性，一个实体可以有若干个属性 关系(Relationship)：实体彼此之间相互连接的方式称为关系。一般可分为以下 3 种类型:
一对一关系 (1 ∶ 1) 这种关系比较少见 维护关系：随意选择一方构建外键 例如：Wife and Husband w1 h1 wife husband id name h_id id name 一对多关系 (1 ∶ N) 比较常见： 维护关系：在多的一方维护一方的标示列作为外键 比如： student class sno sname age c_id id name 老师 1 1 2 1多对多关系 (M ∶ N) 维护关系：构建桥表 student(学生) course(课程) sno sname c_id id name student_course_brige sno c_id 1 1 1 2 1 3may-be 和 must-be 在实体与实体之间的关系中,都会存在着may-be和must-be这俩种情况,例如:系统中有订单和顾客俩个实体(N:1关系),一个顾客对应多个订单,一个订单对应一个顾客,而且一个顾客可(以may be)没有订单和他对应,一个订单一定(must be)会有顾客和它对应.ER图中符号的表示 1) #：主要标识 (#):次要标识 2) * : 非空 #*:表示主键。 3) o : 可有可无 4) 虚线: may be 顾客这边虚线，顾客可能没有订单 5) 实线: must be 订单这边实线，订单一定是属于某个客户。 6) 竖杠(|): UID Bar代表要强制在(|)一方建立一个联合主键，将对方ID拿过来做联合主键 简单点说就是外键同时做了当前表的主键，也就是说|的这一方有两个主键。 7) 伞状图标代表多的一方,不是伞状图标则代表一的一方![]()
自己和自己关联，比如经理自己
![]()
![]() 数据库设计
数据建模完成之后,可以把ER图转换成数据中的表
1.实体的名字转换为表的名字
2.实体的属性转换为表中的列
3.具有唯一特点的属性设置为表中的主键
4.根据实体之间的关系设置为表中某列为外键列(主外键关联) 设计关系数据库时，遵从不同的规范要求，才能设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。 目前关系数据库有六种范式： 第一范式(1NF) 第二范式(2NF) 第三范式(3NF) 巴斯-科德范式(BCNF) 第四范式(4NF) 第五范式(5NF，又称完美范式) 注:满足最低要求的范式是第一范式(1NF)。在第一范式的基础上进一步满足更多规范要求的称为第二范式(2NF)，其余范式以次类推。一般说来，数据库只需满足第三范式(3NF)就行了第一范式: 一个表中,每个列里面的值是不能再分割的. 例如:我们设计的表中有一个列是:爱好 这个列的值可能会是这样:足球篮球乒乓球 但是这值是可以再分割的:足球、篮球、乒乓球 所以这种设计是不满足第一范式第二范式: 第二范式是在满足第一范式的基础上 表中的非主键列都必须依赖于主键列 例如: 订单表: 订单编号 是主键 订单编号 订单名称 订单日期 订单中产品的生产地 这几个非主键列中,产品生产地是不依赖于订单编号的,所以这种设计是不满足第二范式第三范式: 第三范式是在满足第二范式的基础上 表中的非主键列都必须直接依赖于主键列,而不能间接的依赖. (不能产生依赖传递) 例如: 订单表: 订单编号 是主键 订单编号 订单名称 顾客编号 顾客姓名 顾客编号依赖于订单编号,顾客姓名依赖于顾客编号,从而顾客姓名间接的依赖于订单编号,那么这里产生了依赖传递,所以这个设计是不满足第三范式的总之要保证每个表属于一个范畴，表里面的列要完全依赖主键，和主键有间接依赖关系的设置为外键而存在 13)、主键和外键联合主键联合外键只能是表级约束 主键: 1.能做主键的列必要满足非空唯一的特点 2.只要满足非空唯一的列都可以做主键 3.可以让表中一个有意义的列做主键,比如说学号,它既表示学生学号又作为表中的主键,因为这个列满足非空唯一的条件 4.也可以找一个没有意义的列做主键,就是用来唯一标识一行记录的 5.我们可以让多个列联合在一起做表中的主键,那么它就是联合主键,要求这几个列的值联合在一起是非空唯一的 外键: 1.表中的某一个列声明为外键列,一般这个外键列的值都会引用于另外一张表的主键列的值(有唯一约束的列就可以,不一定非要引用主键列) 2.另外一张表的主键列中出现过的值都可以在外键列中使用 3.外键列值也可以为空的,提前是这个外键列在表中不做主键,因为我们也可以把表中的外键列当做主键来使用(只有满足非空唯一的要求就可以) 4.如果把B表中的联合主键的值引用到A表中做外键,因为是俩个列在B表中做联合主键,那么A表引用过来的时候也要把俩个列的值都引用过来,那么它们在A表中就会作为一个联合外键出现完整性约束： 实体完整性：主键非空且唯一 引用完整性：或者匹配主键的值或者为空 列级完整性：当前所定义列的类型，和长度是必须要遵守的 用户自定义：自定义规则，例如只能写男和女Nn：代表not null
U：代表unique
Pk：代表primary key
Fk：代表foreign key
![]()
建表：
1.映射实体—-表
2.映射属性—-列
3.添加约束
4.描述关系信息(外键) 14)、创建表和约束问题1.oracle数据库中的多种数据结构：
1.表结构 存储数据
2.视图 一张表或多张表中数据的字节
3.sequence 主要用来生成主键值
4.index 提高检索性能
我们需要学会创建数据结构 2.表结构：
1.表结构可以随意创建
2.表结构不需要预先申请空间
3.可以在线修改。 3.创建语法：
创建表的释放有两种：基本操作 子查询 3.1基本操作3.1.1 语法： create table [schema.]tb_name( col_name datatype [default value] [colum_constraints], ..., [table_constraint], ... ); note : 1.create table 关键字，固定写法， schema，在oracle数据库中代表用户名 2.tb_name代表表名，可以自定义：但是需要遵循命名规则(详见3.1.2命名规则)： 3.列名一般也要求遵循命名规则(详见3.1.2命名规则) 4.dataType，列所属的数据类型，详见(3.1.3 oracle支持的数据类型)3.1.2 命名规则 1.字母开头2.长度为1-303.只能有大小写英文,数字和_ $ #4.同一个用户下的对象名不能重复5.不能使用关键词作为表名（如：select group等等）3.1.3 oracle支持的数据类型： 类型名 描述 VARCHAR2(size) 可变长字符串, CHAR(size) 定长字符串 NUMBER 数字类型 NUMBER(p,s) 数字类型 DATE 日期类型 CLOB 字符大数据对象 BLOB 二进制大数据对象 note：1.char，varchar2，varchar
用法: char(size),varchar2(size) varchar(size) size用来指明所能保存字符值的上限。区别：char：定长字符 即一旦确定了()中的字符个数，在保存数据的时候，不论你保存的字符个数为多少个，所占空间大小为固定的()中的字符个数。 如char(2) 保存 a ab都占用2个字符空间varchar , varchar2:不定长字符 即在保存数据的时候，会先判断字符个数，然后再分配对应的空间进行保存。 如varchar(2) 保存a 占用1字符空间 保存ab 占用两2字符空间在oracle数据库中，指定变长字符串首选varchar2. 2.number(p,s): p确定数字的有效位数 s确定数字的小数点位数 number(4,2)最大值和最小值为多少？ -99.99~99.993.date： 日期类型 系统默认日期类型：'DD-MON-YY' 操作字符类型和日期类型数据的时候，一定要放到''中间3.1.4 default：设置默认值 1.作用：设置在往表中插入数据时，如果没有指定该列的值，默认插入的值。 2.默认值可以是合法的字面值（根据定义的列的数据类型来赋值），表达式，或者是sysdate和user等合法的sql函数。 create table test( start_date date default sysdate); 3.默认值不能使用其他表的列或者不存在的列/伪列3.1.5 约束 定义：所谓约束就是强制表中的数据列必须遵循的一些规则。 而且如果表中存在依赖约束，可以阻止一些不合理的删除操作。 分类： 表级约束：定义在表级别的约束(即在列的完整定义完成后，才定义的约束) column dataType , unique(column)列级约束：直接跟在列完整性定义后边的约束
column dataType unique, 种类： 约束名 描述 分类 NOT NULL ：非空 列级 UNIQUE ：唯一 列级/表级 PRIMARY KEY ：主键 列级/表级 FOREIGN KEY ：外键 列级/表级 CHECK ：自定义 列级/表级 创建时间： 1.创建表的同时定义约束 2.表创建完成之后，通过修改表结构(后期章节描述) 创建语法： 列级： column [CONSTRAINT constraint_name] constraint_type, 表级： column,...(列完整定义结束) [CONSTRAINT constraint_name] constraint_type (column, ...),.... 详细介绍： 1.not Null：值不允许为null，阻止null值输入 note:只能是列级约束 例如： create table test( id number constraint test_nn_id not null); create table test( id number not null); 2.unique：唯一值约束，要求值必须唯一，不能重复。 note： 1.可以设置单列唯一，或者组合列唯一 2.如果unique约束单列，此列可以为null 3.可以是列级，也可以是表级约束 4.对于unique列，oracle会自动创建唯一值索引。 例如： create table test(id number constraint test_un_id unique);也可以这样写
![]() create table test( id number, constraint test_un_id unique(id) );![]()
create table test(id number unique);//代表不能同时相同，联合唯一！ create table test( id number, name varchar2(10), constraint test_un_id_name unique(id,name) ); create table test( id number, name varchar2(10), unique(id,name) );3.1.6 Primary key:主键 note: 1.主键用来给表中的每一行数据设置唯一标识符。主键只能有一个。 2.主键可以是单列，也可以是组合列。 3.强制非空且唯一，如果由多列组成，组合唯一且列的每一部分都不能为null。 4.可以表级，可以列级。 5.自动创建唯一值索引。 例如： create table test(id number constraint test_pk_id primary key); create table test( id number, constraint test_pk_id primary key(id) );//列级定义 create table test(id number primary key);//表级定义
//可以联合主键 create table test( id number, name varchar2(10), constraint test_pk_id_name primary key(id,name) ); create table test( id number, name varchar2(10), primary key(id,name) );3.1.7 .foreign key：外键
一般在设计表与表之间的关系时，为了减少数据冗余，一般做的操作是在其中一张表中设置 一列(组合列)，这一列(组合列)的值可以唯一的确定另外一种表中和当前表相关联的一行数据。那么这个列成为外键。 note：
1.可以是单列，也可以是组合列
2.引用当前表或者其他表中(只要想和当前表建立关系的表) 的主键列或者unique列
3.可以是表级别/列级别
4.值必须是引用的列的值或者为null
5.有外键约束时，如果想要删除的父表(被引用的表)中的某一条数据时，必须保证在子表(引用表)中没有和这条数据相关联的数据存在。
6.ON DELETE CASCADE ,指明在删除父表中数据时可以级联删除子表中数据
例如： create table emp(id number primary key);---->父表 1:m/m:1 create table test( id number constraint test_fk_emp_id references emp(id)); 1:1 create table test(id number references emp(id) unique); create table test( id number, constraint test_fk_emp_id foreign key(id) references emp(id) ); create table test(id number references emp(id)); create table emp( id number, name varchar2(10), primary key(id,name) ); //这里需要注意的是，如果是联合主键，要引用的表只需要引用其中一个作为 //主键或者unique的列，但是他作为两个存在！，这时在引用的时候就必须， //引用两个。 create table test( id number, name varchar2(10), constraint test_fk_emp_id_name foreign key(id,name) references emp(id,name) ); create table test( id number, name varchar2(10), foreign key(id,name) references emp(id,name) on delete cascade );
1234567891011121314151617181920212223242526272829303132- 33
3.1.8 check ： 自定义约束，定义每一行必须遵循的规则
note：
1.可以是表级/列级约束
例如： create table test( gender varchar2(2) constraint test_check_gender check(gender in ('F','M')), age number check (age>=15 and age<=20) ); create table test( gender varchar2(2), constraint test_check_gender check(gender in ('F','M')) ); create table test( gender varchar2(2), check(gender in ('F','M')) );子查询建表
一般使用子查询建表，要将另外一张表中的某些数据存放到一张新的表格中。(相当于将原来打印在控制台上的信息，现在直接定义成一张新的表格。) 语法： create table tb_name[(column,...)] as select ...复制一个表的指定列数，或者全部 ![]()
note：
1.在用子查询建表时，只有not Null约束会被复制。
2.创建表时可以指定列名，也可以不指定，但是一定不指定列的数据类型
3.创建表的列跟子查询表的列数要保持一致
5、常见数据类型 数据表的组成就是行和列的 集合，而且每一列 都有其对应的类型 ，所以在进行数据 表创建之前，首先 需要来了解一 下在系统之中常用的数据类型。
![]() 虽然 BLOB 字段可以保存电影数据，但是一般正常人没人这么做。而且在一般的开发之中，99%选用的数据类型：
VARCHAR2（VARCHAR）、NUM BER、DATE、CLOB（像 VARCHAR 一样的方式操作）。 6、闪回操作（理解）
从 Oracle 10 g 起为了方便用户进行数据表的恢复。为 Oracle 增加了一个类似 于 windows 的回收站功能，等于是所有 删除是数据表，首先保存在回收站之中，如果用户有需要也可以进行恢复。
范例： 查看回收站 show recyclebin ;
在回收站里面可以发现所有被删除的数据表。
从 Oracle 11g 开始为了方便用户查看数据，提供了一个 SQL Develop er 工具，但是此工具需要配置 java 环境。
JDK 目录：D:\ap p \Teacher\p roduct\11.2.0\dbhome_1\jdk\bin\java.exe
![]()
进入到工具之后首先需要配置一个 scott/tiger 用户的数据库连接。
![]() 范例： 通过回收站恢复 emp 10 数据表 FLASHBACK TABLE emp 10 TO BEFORE DROP ;
范例： 彻底删除数据表，不让删除的数据表经过回收站，在删除语句之后增加一个 PURGE DROP TABLE emp 10 PURGE ;
范例： 删除回收站之中的一个表 PURGE TABLE emp null ;
范例： 清空回收站 PURGE RECYCLEBIN ;
对于闪回技术，只需要有一定的了解即可，而且也只有 oracle 有此特性。 7、表空间
![]()
1、创建表空间
/分为四步 /
/第1步：创建临时表空间 / create temporary tablespace user_temp tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local; /第2步：创建数据表空间 / create tablespace user_data logging datafile 'D:\oracle\oradata\Oracle9i\user_data.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local; /第3步：创建用户并指定表空间 / create user username identified by password default tablespace user_data temporary tablespace user_temp; /第4步：给用户授予权限 / grant connect,resource,dba to username;2、表数据库实例表空间联系
1、数据库 数据库顾名思义是数据的集合，而Oracle则是管理这些数据集合的软件系统，它是一个对象关系型的数据库管理系统。 2、表空间 表空间是Oracle对物理数据库上相关数据的逻辑映射。一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少有一个表空间(称之为system表空间)。每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件(datafile)。一个数据文件只能属于一个表空间。3、实例 每一个运行中的数据库都对应着一个Oracle实例。当服务器上的Oracle数据库启动时，Oracle首先会在内存中分配一片区域－称之为System Global Area(SGA)，然后启动一个或多个的Oracle进程。SGA和这些进程合起来就称为一个Oracle的实例。实例中的内存和进程管理器用来高效地访问数据库的数据，同时为单个或多个的用户提供服务。一个数据库可以由多个实例打开，但任何时刻一个实例只能打开一个数据库。多个实例可以同时运行在同一个机器上，它们彼此访问各自独立的物理数据库。 15)、数据字典1、字典，就是用来帮助人们查看一些信息，查看一些内容
2、数据字典的概述： a)数据字典在数据库被创建的时候被创建。b)被数据库服务器自动更新和维护c) Oracle的数据字典就是oracle存放有关数据信息的地方，用途就是用来描述数据的d) 比如一个表的创建者信息，创建时间信息，所属空间的信息，用户权限信息等。e) 数据库字典是一组表和视图结构，他们存放在system表的空间里面f) 当用户对数据库中的数据进行操作的时候遇到困难就可以访问数据字典来查看详细的信息3、用户可以通过sql语句访问
Select table_name from dictionary
根据查询结果的不同按照前缀分为四类！
1、以user开头的数据字典：
包含用户拥有的相关的对象信息。能够查到对象的所有者是当前的所有对象 user_tables;user_views;user_sequences;user_constraints;2、以all开头的数据字典：
能够查询当前能所有有访问权限的对象信息
3、当dba开头的数据字典：
只能有dba权限的用户查询，能查到数据库中所有的对象。
4、以v$开头的是动态服务性能视图。 4、数据字典内容包括： 1,数据库中所有模式对象的信息，如表、视图、簇、及索引等。 当前的用户对象的信息存放在user_objects表中 2,分配多少空间，当前使用了多少空间等。 3,列的缺省值。 4,约束信息的完整性。 5,Oracle用户的名字。 6,用户及角色被授予的权限。 7,用户访问或使用的审计信息。 8,其它产生的数据库信息。例如：
1.查看当前用户的拥有的对象名和对象类型 Select Object_name ,Object_type from user_objects;![]()
2、查看当前用户下的所有表
Select table_name from user_tables;![]()
3、查看当前用户所定义的所有的约束的姓名和类型 Select constraint_name,constraint_type table_name,R_OWNERFrom user_constraintsWhere table_name = ‘S_EMP’;查询是e_emp表的所有约束![]()
4、查找约束姓名和关联列的名字 Select constraint_name,column_name From user_cons_columnsWhere table_name =’S_EMP’;![]()
16)、DML数据操作语言DML数据操作语言：
insert update delete
事务控制语言：
commit ：提交事务
rollback ：回滚事务
Savepoint：设置回滚点 1.insert语句
两种格式：
直接插入
子查询插入 直接插入基本语法： insert into tb_name[(col_name,...)] values(val1,....);note： 1.如果给每个列都插入数据，可以省略table后边的列，并且插入值的顺序和定义表的顺序一致 2.如果插入的顺序和表定义的顺序不同，或者只插入某几列的值table_name后边必须跟上列名 3.字符串和日期类型的值必须使用‘’引起来 4.insert语句一次只插入一条数据 5.插入的值可以使用系统函数，例如---user,sysdate 6.插入的值可以使用运行时参数。 例如： create table test( id number primary key, name varchar2(10), gender varchar2(2) check (gender in ('F','M')), birthday date); 插入： 1.insert into test(id,name) values (3,'zs'); //传统的指明了赋值，但非空值必须赋值 2.insert into test(id,name,birthday) values (1,'lisi','09-9月-10'); 3. insert into test(id,name,birthday) values //可以使用预定义参数 (2,user,sysdate); 4. insert into test(id,name,birthday) values //可以进行参数传值 (&id,'ss',sysdate);
1234567891011121314151617181920- 21
2.使用子查询插入,
和子查询创建不同的是，这里不需要as 关键字，并且不需要出现values insert into table_name[(col_name,....)] select .......note： 1.不出现values关键字2.插入的列的名字和子查询返回结果的列相匹配。 例如： 1. insert into test2 select * from test; 2. insert into test2(id,gender,name,birthday) select id,gender,name,birthday from test;2.delete语句：
语法：delete [from] tbl_name [where option…]
note:在加外键约束的时候，如果想在删除主表的记录的同时对外键表中已经存在的关联关系记录进行操作可以使用下面两个关键词：
on delete cascade:级联删除,删除主表记录，外键表的关联记录一块儿删除
on delete set null：删除主表记录，外键表的关联记录该列的值变成null 例如： 1.从test2表中删除id = 1 delete from test2 where id = 1; 2.create table test3( //设置级联删除主表记录，外键表关联记录一块删除 id number references test2(id) on delete cascade);![]()
create table test3( id number references test2(id) on delete set null);如果关联的外键被删除，本表外键的位置会被弄成空，而不会被删除
![]() 3.update 语句：
语法：update table_name set column=value,[column=value]
[where condition…];
note：
1.如果不加条件，默认修改表中所有的行。 例如： 1. update test set name = 'wangwu',gender='M' where id = 3; 只更改id=3的行。 2.update test set gender = 'F';所有行都改。17)、Oracle事务1、事务的基本
当一个sql命令执行一个事务就开始了，当遇到一下情况，事务自动完成 1.commit或者rollback2.DDL（数据库定义语言）或者DCL命令执行 3.错误，退出，或者系统崩溃 note：
commit：提交事务，提交事务是指让这个事务里面的所有操作都生效到数据库中
rollback：回滚事务，回滚事务是指让这个事务里面的所有操作都撤销 测试: 使用两个终端窗口,同一个账号登录到数据库中,观察事务是否提交对用户查看数据的影响 注:一个用户对A表某一列做了DML操作,但是没有提交事务,这时候别的用户是不能对A表这一列再做其他的DML操作。(为了保证数据的安全和一致性) 例如1: insert ....产生事务A update ... 这个操作是事务A中的操作 insert .. 这个操作是事务A中的操作 commit; 让事务A里面的三个操作生效、事务A结束 delete ... 产生新的事务B insert .. 这个操作是事务B中的操作 insert .. 这个操作是事务B中的操作 insert .. 这个操作是事务B中的操作 rollback; 让事务B中的四个操作都撤销,事务B结束 例如2: insert ....产生事务A update ... 这个操作是事务A中的操作 insert .. 这个操作是事务A中的操作 DDL语句; 事务A会被提交 rollback; 这时候回滚已经对事务A不起作用,因为事务A以及被提交了 注:create语句 drop语句 alter语句等属于DDL语句
123456789101112131415161718192021222324- 25
事务控制使用:commit,savepoint,rollback; 2、事务的ACID原则： 1、原子性：要不全成功，要不全失败2、一致性：从一个一致性状态到达另外一个一致性状态3、隔离性：事务之间互不影响 第一类丢失更新： 事务A撤销事务时，将事务B已经提交的事务覆盖了。 第二类丢失更新： 事务A和事务B同时修改某行的值， 1.事务A将数值改为1并提交 2.事务B将数值改为2并提交。 这时数据的值为2，事务A所做的更新将会丢失。 不可重复读：（例如修改） 在同一事务中，两次读取同一数据，得到内容不同 事务1：查询一条记录 -------------->事务2：更新事务1查询的记录 -------------->事务2：调用commit进行提交 事务1：再次查询上次的记录 此时事务1对同一数据查询了两次，可得到的内容不同，称为不可重复读 幻想读：（例如插入） 同一事务中，用同样的操作读取两次，得到的记录数不相同 事务1：查询表中所有记录 -------------->事务2：插入一条记录 -------------->事务2：调用commit进行提交 事务1：再次查询表中所有记录 此时事务1两次查询到的记录是不一样的，称为幻读 脏读：事务A读到事务B未提交的数据。
1234567891011121314151617181920212223242526272829- 30
4、事务隔离级别
为了处理这些问题，SQL标准定义了以下几种事务隔离级别
READ UNCOMMITTED 幻想读、不可重复读和脏读都允许。
READ COMMITTED 允许幻想读、不可重复读，不允许脏读
REPEATABLE READ 允许幻想读，不允许不可重复读和脏读
SERIALIZABLE 幻想读、不可重复读和脏读都不允许
Oracle数据库支持READ COMMITTED 和 SERIALIZABLE这两种事务隔离级别。所以 Oracle不支持脏读 5、持久性：事务提交后，能够持久性影响数据库。 6、隐式事务提交：
1.DDL语句（create..），执行commit
2.DCL语句（gant….），执行commit
3.正常退出终端。 note：如果系统崩溃，或者sqlplus不正常退出，事务回滚。7.事务提交或者回滚之前的状态
1.因为数据库缓存区的存在，数据前一次的状态可以被回复
2.当前用户可以会看使用DML操作的数据的结果，但是其他用户不能看到当前用户的DML操作结果
3.所有受影响的行会被锁定，其他用户不能修改。 8.显示结束事务
commit：之前所做的所有会影响数据库的操作，都会对数据库产生持久的影响。
rollback：取消之前所做的所有操作 note：事务一旦提交，不能rollbacksavepoint: 保存回滚点 savepoint point_name;rollback to point_name;回滚到指定的标记点。标记点之后所做的所有操作都会被取消，但是之前的不受影响。![]()
18)、修改表结构和约束添加列不需要关键字column,删除列需要关键字column，修改列用的modify
重命名列的时候rename也需要加关键字column
涉及到的约束添加，删除,约束生效，无效，都需要constraint 目标： 1.添加和修改列 2.添加，enable，disable，或者remove约束 3.删除表 4.删除表中所有数据并回到表定义的初始状态（截断表） 5.修改对象的名字 6.给对象添加注释，从数据字典中查看注释用到的命令： 1.Alter table ： 1.添加和修改列 2.添加和删除约束 3.enable，disable约束 2.drop table命令移除表中所有行和表结构 3.rename，truncate，comment 4.当执行以上DDL语句时，事务自动提交功能：
1.增加列
语法： alter table tb_name add column datatype [default val] constraint ..... note: 1.如果添加not null(primary key约束要求值也不能为null)约束，需要保证当前表中没有数据存在。 2.新添加的列，相当于表定义中最后一个定义的列。 例如：//添加新的一列 alter table test add name varchar2(10) default 'test' not null ;//添加新的多列
![]() alter table s_stu add (sname varchar2(20),sage number);//添加带有约束的列
![]() alter table husband add sage number constraint husband_sage_check check(sage<=100);![]()
2.删除列：
语法：alter table tableName drop column column_name; 例如：alter table test drop column name;3.修改列属性：(数据类型和约束使用modify) 语法：ALTER TABLE table MODIFY (column datatype [DEFAULT expr][NOT NULL] [, column datatype]...); note： 修改列的规则：(反正就是只能往大了改不能往小了改（没有数据除外）) 1.可以增加字段的宽度或者精度 2.如果列的值为null或者表中没有数据，可以降低宽度和精度 3.给当前列，后续添加的数据指定默认值。Default 4.当且仅当当前列中没有null值时，可以定义当前列为not null. 5.当前列中的值为null时，可以修改列的数据类型 6.如果需要给某个字段添加not null约束，只能使用modify。 例如： alter table test modify id number constraint test_pk_id primary key;可以间接性的设置约束，前提是约束必须是不违背当前表里面已有的值 ![]()
alter table test modify id char(20);4.增加约束
语法：alter table tb_name add 约束的完整定义 note： 1.只能增加能够使用表级约束的约束 2.不能修改约束 例如： alter table test add constraint test_pk_id primary key(id); alter table test add check(gender in ('F','M'));5.删除约束：只能通过约束名字删
语法：alter table tb_name drop 约束名。 例如： alter table test drop constraint test_pk_id;（截图是TEST1，有值，但是刚才的约束已经没）![]()
删除主键约束时，同时删除和他依赖的外键约束
alter table test drop constraint test_pk_id cascade;![]()
![]()
6.使一个约束失效：
语法：alter table tb_name disable constraint constraint_name [cascade];
note：添加cascade表明要让所有的依赖约束都失效。
![]() 7.是一个约束生效：
语法：alter table tb_name enable constraint constraint_name;
note：
1.当启用unique和primary key约束时，会自动创建索引。 例如：alter table test enable constraint test_id_pk;//但需要注意的是，数据必须不能违反主键要求！否则不能启用
![]()
//只有让原来的数据满足约束条件，才可以开启
![]() 8.删除表： drop table tb_name [cascade constraint]; note： 1.删除表中所有数据 2.所有的索引被删除 3.使用cascade constraint,级联删除所有的依赖完整性约束 例如：//先为一个表添加父类的外键，然后删除，外键约束直接被删掉了
![]() ![]()
![]()
drop table test cascade constraint; 删除之后，可以通过： select column_name,constraint_name from user_cons_columns； 查看是否约束还在。9.重命名：rename
重命名表： rename old_tb_name to new_tb_name;![]()
重命名列：
alter table tb_name rename column old_col_name to new_col_name;![]()
note: 1.重命名可以用来修改table，view，sequence，synonym 2.只有是这个对象的拥有者，才能重命名。 例如： rename emp to emp2; 将表名重命名为emp2 alter table emp rename column id to eid;10.截断表：truncate
语法：truncate table tb_name 语法和drop一样，作用却和delete一样
note: 1.清空表记录 2.释放当前表所占用的表空间。返回建表初始状态 3.是一个DDL命令。 4.一旦删除，事务不能回滚。 例如：truncate table emp; delete和truncate的比较： delete：可以指定删除某些列，也可以清空表，但是不释放表空间，在事务没有提交之前可以回滚。 truncate：只能清空表，释放表空间，不能回滚。![]()
11.给表加注释：comments
语法：
comments on 表名 is ’内容’
Comments on column表明.列名 is ‘内容’ 给表添加注释 comment on table talbe_name is '注释内容'![]()
给列添加注释
comment on column table_name.column_name is '注释内容';![]()
note:
1.添加的注释可以在如下数据字典中查看
ALL_COL_COMMENTS所有列的注解
![]()
![]() USER_COL_COMMENTS当前用户列的注解
![]() ALL_TAB_COMMENTS当前所有的表的注释
![]()
![]() USER_TAB_COMMENTS当前用户表的注释
![]()
例如： comment on table emp is '测试'; comment on column emp.eid is 'haha';19)、创建序列（自动增长）
概念：
所谓序列，在oracle中就是一个对象，这个对象用来提供一个有序的数据列，这个有序的数据列的值都不重复。 1.序列可以自动生成唯一值2.是一个可以被共享的对象3.典型的用来生成主键值的一个对象4.可以替代应用程序代码5.当sequence的值存放在缓存中时可以提高访问效率。查看当前用户的sequence表
![]()
创建序列语法： CREATE SEQUENCE name [INCREMENT BY n] [START WITH n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}]note: 1.increment by n:表明值每次增长n(步长) 2.start with n: 从n开始 3.{MAXVALUE n | NOMAXVALUE}： 设置最大值 4.{MINVALUE n | NOMINVALUE}： 设置最小值，start with不能小于最小值。 5.CYCLE | NOCYCLE ： 是否循环，建议不使用 6.CACHE n | NOCACHE ： 是否启用缓存。![]()
代表当前的初始值为1，每次增长为1，缓存三个
例如： create sequence emp_id_seq start with 1 increment by 1 nomaxvalue nominvalue nocycle nocache;note:可以通过数据字典user_sequences查看当前用户所拥有的序列信息。例如： select sequence_name,min_value,max_value,last_number from user_sequences where sequence_name = 'EMP_ID_SEQ';序列的属性(伪列)：
1.nextval : 返回下一个可用的序列值。
通过序列名.nextval获得 就算是被不同的用户调用，每次也返回一个唯一的值。每次调用都会返回下一个值![]()
2.currval ：获取序列当前的值。 在currval调用之前，必须保证nextval已经获取过一次值。没有获取就会报错![]()
通过数据字典查定义的序列 select last_number,min_value from user_sequences where sequence_name='AOTO_ID1' ;默认缓存是这么大的，默认缓存大小是20
![]() 当使用缓存的时候，每次调用序列的nextval就可以让当前值加一，直到当前值等于缓存值的时候，缓存在加上定义缓存的值
![]() 使用sequence： 例如：
1.向表中插入数据 insert into emp values(emp_id_seq.nextval);![]()
2.查看序列的当前值
![]()
select emp_id_seq.currval from dual;3.获取序列的下一个值。
![]() select emp_id_seq.nextval from dual;缓存:
使用缓存可以提高sequence的访问效率 修改sequence： ALTER SEQUENCE name[INCREMENT BY n] [{MAXVALUE n | NOMAXVALUE}][{MINVALUE n | NOMINVALUE}][{CYCLE | NOCYCLE}][{CACHE n | NOCACHE}] 只要从新修改了序列，缓存大小，就又开始从1开始自增加了
![]() note： 1.必须是序列的拥有者，或者具有alter权限 2.修改后的序列，只对之后的值起作用。 3.不能修改start with，如果想改，只能删除，重新创建，启动。删除sequence： drop sequence seq_name;例如： drop sequence emp_id_seq;![]() 20)、创建视图View
概念：
视图：所谓视图就是提取一张或者多张表的数据生成一个映射，管理视图可以同样达到操作原表 的效果，方便数据的管理以及安全操作。 视图其实就是一条查询sql语句，用于显示一个或多个表或其他视图中的相关数据。视图将 一个查询的结果作为一个表来使用，因此视图可以被看作是存储查询结果的一个虚拟表。视图来 源于表，所有对视图数据的修改最终都会被反映到视图的基表中，这些修改必须服从基表的完整 性约束。视图的存储: 与表不同，视图不会要求分配存储空间，视图中也不会包含实际的数据。视图只是定义了一个查询，视图中的数据是从基表中获取，这些数据在视图被引用时动态的生成。由于视图基于数据库中的其他对象，因此一个视图只需要占用数据字典中保存其定义的空间，而无需额外的存储空间。视图的优势： 1.信息隐藏 比如s_emp表中有工资，可以创建视图，隐藏工资信息。（可以配合权限，让某个用户只能查看视图，不能查看表。） 2.使复杂查询变得简单。 3.数据独立 4.相同数据的不同展示形式。视图的分类： 1.简单视图 2.复杂视图 比较： 简单视图 复杂视图 涉及到的表个数 1 1个或多个 包含函数 不包含 包含 包含组数据 不包含 包含 通过视图使用DML 可以 不可以视图的创建：(说到这里，在as关键字出来在别名的时候用，在子查询建表的时候也会用到) CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name[(alias[, alias]...)]AS select 。。。。[WITH CHECK OPTION [CONSTRAINT constraint]][WITH READ ONLY]![]()
note:
要想创建view不需要有权限，创建权限需要是dba身份下
grant create view to jd1613; 1.or replace:代表修改view 2.force| noforce: 即使基表不存在也要建立该视图 | 基表不存在就不建立此视图，默认值。代表不存在也要创建
![]() 3.alias: 视图中的列的名字(相当于给子查询的结果列起别名) 4.子查询中可以包含复杂的查询语法，这些细节都对用户隐藏。 5.子查询中不能包含order by子句。 6.WITH CHECK OPTION 插入或修改的数据行必须满足视图定义的约束；换句话说，加上该关键词表示对view进行dml操作的时候，只能操作select语句 中where条件限制的内容 7.WITH READ ONLY ：该视图只读，不能在这个视图上进行任何DML操作。![]()
8.查看视图结构： desc view_name;例如： create or replace view myView as select id,last_name,start_date from s_emp where id <= 4;![]()
感觉类似于子查询创建表一样，只不过把table 变成了or replace view
此时可以使用：
1.查看视图中所有数据信息 select * from myView；2.执行插入：(创建的时候不加with check option，是可以操作除视图查询条件以外的值) insert into myView values(111,'haha','03-5月-16'); 插入成功！3.再次查看，找不到刚插入的数据，因为这条数据不满足id<=4,但是查看原始表s_emp，有这条数据。 如果： create or replace view myView (id,name,s_date) as select id,last_name,start_date from s_emp where id <= 4 with check option;![]()
注意可以取别名，但是要注意，uid也属于关键字！
此时可以使用：
1.查看视图中所有数据信息
select * from myView；2.执行插入： insert into myView values(121,'haha','03-5月-16'); 插入失败！，因为视图的约束时id<=4,现在插入的id值为121，所以失败！![]()
create or replace view myView(id,name,s_date)asselect id,last_name,start_datefrom s_emp;or create or replace view myViewasselect id,last_name,start_date s_datefrom s_emp;myView中列的名字都为id,name,s_Date.创建复杂视图：
子查询不许包含order by，而且复杂视图不能做DML操作
复杂视图可能包含分组，组函数，多表连接等。
例如：
CREATE or replace VIEW myView (name, minsal, maxsal, avgsal) AS SELECT d.name, MIN(e.salary), MAX(e.salary), AVG(e.salary) FROM s_emp e, s_dept d WHERE e.dept_id = d.id GROUP BY d.name;![]()
查看视图信息
可以使用数据字典user_views;
![]()
Select view_name from user_views;删除视图对象： drop VIEW view_name; ![]()
|
/1 
发表于 2017-11-30 10:43:34
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
转播
分享
淘帖
赞
踩
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
照妖镜