oracle.docx
《oracle.docx》由会员分享，可在线阅读，更多相关《oracle.docx（33页珍藏版）》请在冰豆网上搜索。
oracle
秦荣义
qinry@
1.关系型数据库理论
1）数据库是由一系列的对象组成的，如table，view，index
2）可以通过一系列的操作（SQL）作用于这些对象
3）为了保证数据的完整性，保存数据要遵循一定的规则（约束）
2.数据库中的约束
1）主键
2）外键
3）唯一
4）非空
5）check（自定义）
3.SQL,SQL*Plus,andPL/SQL
SQL：
结构化的查询语言
第一代语言--->机器语言
第二代语言--->汇编语言
第三代语言--->高级语言
第四代语言--->智能语言
ANSI（美国国家标准委员会）
SQL*Plus：
是oracle的一个客户端软件，也是一个简单的报表编辑器，对查询获得的结果进行简单编辑，维护着一个buffer，缓存着最后一次执行的SQL语句或者是PL/SQL脚本.
PL/SQL:
oracle公司在标准的SQL基础上增加了一些过程化的控制语句形成的一种语言。
只适用于Oracle数据库。
4.SQL语言的分类
1）数据获取语言（select）
2）数据操纵语言（insert，update，delete）
3）数据定义语言（create，alter，drop，rename，truncate）
4）事务控制语言（commit，rollback，savepoint）
5）数据控制语言（grant，revoke）
telnet172.16.0.220
briup
briup
sqlplus
jd1001
jd1001
第一章
1.查询
select[distinct]column,...,*fromtable;
distinct:
去掉重复的记录
位置：
1）紧跟在select关键字后面
2）出现在组函数中
2.书写SQL语句的规则
1）命令可以出现在一行或者多行
2）TABS或者空格键用来提高可读性
3）英文单词不能缩写或者拆分开
4）大小写不敏感
5）SQL会写入buffer中
3.给列指定别名
在列的名字后面加上空格，然后跟上别名
selectfirst_namenamefroms_emp;
4.将多个列的内容连接在一起？
在列与列中间加上"||"
selectfirst_name||last_namefroms_emp;
selectfirst_name||''||last_namefroms_emp;
select'Briup_'||first_namefroms_emp;
5.null值的处理
没有分配值的列就是null，在一个表达式中，如果其中一个列为null，那么整个表达式就是null。
nvl（col,val）：
空值置换函数，有两个参数,第一个参数是可能为空的列名，地二个参数就是出现空值要替换的值，但是要注意两个参数的类型要一致。
selectfirst_name,
salary+salary*nvl（commission_pct,0）/100sal
froms_emp;
6.SQL*PLUS中的一些常用命令
1）查看表的结构
desc[ribe]table_name;
2）常用的数据类型
a）数字类型
number
number（p）
number（p,s）
p:
精度s:
刻度
number可以存放任意数字
number（3）===number（3,0）
number（5,2）345.456345.46
number（3,-1）20092009
number（3,4）0.00456
number（3,-2）20009
b）字符串
char定长的字符串
varchar变长的字符串
varchar2
char==char
（1）
char（5）'abc'
varchar（5）'abc'
c）日期
date：
世纪，年，月，日，时，分，秒
‘
第二章
1.orderby子句
（1）作用：
对结果集进行排序
（2）位置：
永远在select语句的最后面
（3）分类：
升序（asc，默认）和降序（desc）
（4）可以出现的内容：
1）列名
2）列的别名
3）列出现的位置
4）表达式
（5）orderby后面跟多个列：
先按照第一个列排序，如果第一个列不能区分大小，在按照第二个列进行排序，以此类推。
（6）null值永远是最大的，升序出现在最后，降序在最前。
selectfirst_name,salary
froms_emp
orderbysalary[asc];
selectfirst_name,salary
froms_emp
orderbysalarydesc;
2.where子句
1）作用：
筛选结果集
2）位置：
紧跟在from语句之后
3）可以出现的内容：
列名，表达式，常量，比较操作符，字面值
3.常用的SQL比较操作符号
1）between...and....：
表示在一个区间
2）in：
表示在一个集合中
selectid,first_name,salary
froms_emp
whereidin（1,3,5,7）;
3）like：
模糊匹配
%：
通配0到多个任意字符
_：
通配任意一个字符
wherelast_namelike'N\_'escape'\';
4）isnull：
是否为空
4.逻辑操作符
1）not:
取反
2）and：
与
3）or：
或
selectfirst_name,salary
froms_emp
wheresalary>1000;
第三章
1.单行函数
1）操作字符串
LOWER:
变成小写字母
UPPER:
变成大写字母
INITCAP:
首字母大写，其他字母小写
CONCAT:
连接字符串
SUBSTR:
截取字符串
LENGTH:
求字符串长度
NVL:
替换空值
2）操作数字的单行函数
round：
四射五入
trunc：
直接舍去
mod：
取余数
3）操作日期的函数
MONTHS_BETWEEN：
两个日期相差多少个月
ADD_MONTHS：
给指定日期加上几个月
NEXT_DAY：
离给定日期最近的下一个星期几
LAST_DAY：
给定日期所在月的最后一天
ROUND：
超过一半进一，否则舍去
TRUNC：
直接舍去
2.类型转换函数
to_date（）
to_char（）
to_number（）
3.“千年虫”问题
YY：
所给日期与当前日期处于同一世纪
RR：
结合当前日期与给定日期联合计算
在oracle中默认采用的是RR机制
TO_CHAR（32432,'L99,999.00'）
----------------------------------------
￥32,432.00
第四章
1.从多张表中查询内容
selectfirst_name,salary,name
froms_emp,s_dept;
selects_emp.first_name,s_emp.salary,s_dept.name
froms_emp,s_dept;
selecte.first_name,e.salary,d.name
froms_empe,s_deptd;
2.笛卡尔积
现象：
一张表中的所有记录与另外一张表中的所有记录匹配的结果
原因：
没有连接条件或者连接条件失效
1zs11dept1
2ls22dept2
3.等连接
selecte.first_name,e.salary,d.name
froms_empe,s_deptd
wheree.dept_id=d.id;
4.非等连接
selectename,sal,grade
fromemp,salgrade
wheresal>=losalandsal<=hisal;
selectename,sal,grade
fromemp,salgrade
wheresalbetweensalandhisal;
5.外连接
查询所有的员工以及其部门的信息？
selecte.name,d.name
fromt_empe,t_deptd
wheree.dept_id=d.id（+）;
selecte.name,d.name
fromt_empeleftouterjoint_deptdone.dept_id=d.id;
查询所有的部门以及对应员工的信息？
selecte.name,d.name
fromt_empe,t_deptd
wheree.dept_id（+）=d.id;
查询所有的部门以及所有的员工的信息？
selecte.name,d.name
fromt_empefullouterjoint_deptdone.dept_id=d.id;
selecte.name,d.name
fromt_empe,t_deptd
wheree.dept_id=d.id（+）union
selecte.name,d.name
fromt_empe,t_deptd
wheree.dept_id（+）=d.id;
6.集合操作
union，unionall，minus，intersect
7.自连接
查询所有有领导的员工
selecte.first_name,e.salary
froms_empe,s_empm
wheree.manager_id=m.id;
查询所有的领导
selectm.first_name,m.salary
froms_empe,s_empm
wherem.id=e.manager_id;
1zs1zs
2ls12ls1
3ww23ww2
第五章
1.组函数
1）只能操作数字
sum（）:
求和
avg（）:
求平均数
2）三种类型都可以使用的
count（）:
计算表中的记录数
max（）:
求最大值
min（）:
求最小值
2.计算表中的记录数
count（column）
count（*）
注意：
count函数不处理空值，任何组函数都不处理null值
3.groupby
作用：
分组，按照groupby子句后面的列进行分组，相同的分成一组，然后进行组函数的计算。
selectdept_id,avg（salary）avgsal
froms_emp
groupbydept_id;
4.having子句
作用：
类似于where子句，用来筛选结果集，但是可以使用组函数
selectdept_id,avg（salary）avgsal
froms_emp
groupbydept_id
havingavg（salary）>1400;
selectdept_id,avg（salary）avgsal
froms_emp
havingavg（salary）>1400
groupbydept_id;
selectdept_id,avg（salary）avgsal
froms_emp
groupbydept_id
havingavg（salary）>1400anddept_id>32;
selectdept_id,avg（salary）avgsal
froms_emp
wheredept_id>32
groupbydept_id
havingavg（salary）>1400;
selectdept_id,avg（salary）avgsal
froms_emp
wheredept_id>32
groupbydept_id
havingavg（salary）>1400
orderby2desc;
5.在一个查询语句中，如果使用了组函数（select语句，having语句，orderby语句），那么在select后面没有使用组函数的列，一定要出现在groupby子句中。
selectfirst_name,dept_id,avg（salary）
froms_emp
groupbyfirst_name,dept_id;
6.如果在一个查询语句中有where子句，groupby子句，having子句，orderby子句以及组函数，他们执行的先后顺序？
1）按照where语句进行筛选
2）按照groupby进行分组
3）计算组函数
4）按照having进行筛选
5）按照orderby子句进行排序输出
问题：
查询工资大于公司平均工资的员工的
first_name,salary?
selectfirst_name,salary
froms_emp
groupbyfirst_name,salary
havingsalary>avg（salary）;
第六章
1.子查询出现的位置？
1）from子句
2）where子句
3）having子句
selectfirst_name,salaryfroms_emp
wheresalary>（selectavg（salary）froms_emp）;
2.子查询的外围语句
1）select语句
2）createtable...
3）insertinto...
问题：
查询工资大于"smith"所在部门平均工资的员工的first_name,salary?
（1）selectdept_idfroms_emp
wherelower（first_name）='smith';
（2）selectavg（salary）froms_emp
wheredept_id=
（1）
（3）selectfirst_name,salaryfroms_emp
wheresalary>
（2）
3.rownum
伪列，能够使用的比较操作符：
<、<=、between1and?
selectfirst_name,salary
from（selectfirst_name,salary,rownumr）
whererbetween10and20;
问题1：
查询工资前n名的人（并列的算一个）？
问题2：
查询与smith有相同工资等级的员工的平均工资？
selectfirst_name,salary
froms_emp,（
selectsalarys,rownumfrom（
selectdistinctsalary
froms_emporderbysalarydesc
）whererownumbetween1and6
）wheresalaryins;
selectavg（sal）fromemp
where（selectgradefromsalgrade
wheresalbetweenlosalandhisal
）=（
selectgradefromsalgrade
where（selectsalfromemp
whereename='SMITH'
）betweenlosalandhisal
）
480014800
480024700
470034600
460044500
460054400
4600
4500
4400
第七章
1.定义一个变量
"&"+var_name
第八章
1.约束
（1）主键（priamrykey）：
取值必须唯一，且非空。
如果主键约束同时加在多个列上，称为联合主键，要求联合唯一，单列非空。
通常分为两类：
1）自然主键：
有逻辑含义，需更新
2）代理主键：
没有实际意义，无需更新。
（建议使用）
（2）外键（foreignkey）：
取值要么为空，要么为所关联表（可以是本表，或者其他表）对应列的列值。
（3）非空（notnull）：
对应列必须有值，不能为空
（4）唯一（unique）：
对应的列不能有重复的值
（5）自定义（check）：
取值必须满足自定义的规则
2.E-R图
1）属性
#*：
主键约束
*：
非空
o：
没有约束
（#）*:
唯一，非空约束
2）关系
————：
必须有值
----：
可能为空
伞状符号：
额外的增加一个外键列
--|--:
表示外键做联合主键
3.范式
1）第一范式一张表的行和列的交叉处只能有一个值
2）第二范式一个非UID的列取决于UID列的值
3）第三范式一个非UID的列不能取决于其他非UID的列
book
--------------------------------------
idnamepricequantitycost
1ibm5000210000
2acer450029000
第九章
1.创建表
CREATETABLE[schema.]table
（columndatatype[DEFAULTexpr]
[column_constraint],
...
[table_constraint]）;
su-oracle
oracle
id
sqlplus'/assysdba'
startup
2.命名规则
1）必须以字母开头
2）长度在1到30个字符之间
3）名字中可以包含A-Z,a-z,0-9,_,$,#
4）同一个scehma下不能有同名的对象
5）名字不能是oracle中的关键字
3.创建表的时候加上约束
1）列级别列与约束中间没有逗号，空格隔开
2）表级别列与约束中间有用逗号隔开
CREATETABLE[schema.]table
（columndatatype[DEFAULTexpr]
[[constraintcon_name]con_type],
...
[[constraintcon_name]con_type（col,...）]）;
只能在列级别上加：
notnull
只能在表级别上加：
联合唯一，联合主键，联合外键，check约束涉及到多个列
4.通过子查询创建表
CREATETABLE[schema.]table
[（column,....）]
ASsubquery;
通过子查询创建表只能拷贝notnull约束，其他约束不能拷贝，要通过添加约束的命令来添加其他约束。
第十章
1.数据字典表的分类
1）以"user_"开头：
当前用户所创建的对象的信息
user_tables
user_indexes
user_views
user_sequences
user_objects
2）以"all_"开头：
当前用户所创建的对象的信息以及授权给当前用户的对象的信息
all_tables
all_indexes
all_views
all_sequences
all_objects
3）以"dba_"开头：
所有对象的信息
dba_tables
dba_indexes
dba_views
dba_sequences
dba_objects
4）以"v$"开头：
数据本身的一些信息以及动态生成的一些信息
v$version:
数据库的版本信息
v$session:
当前session的一些信息
v$database:
数据库本身的信息，如字符集等
2.常用的几张数据字典表
dictionary：
数据字典的字典
table_privileges：
表的权限
ind：
user_indexes的别名
tabs：
user_tables的别名
user_constraints:
用户所创建的约束
user_cons_columns:
用户所创建约束对应列的信息
dict_columns:
数据字典表的列的信息
问题：
查询给定表上的所有的约束的名称，类型，以及对应列的名称？
user_constraints
-------------------------------------------------
table_nameconstraint_nameconstraint_type
s_emps_emp_id_pkp
s_emps_emp_last_name_nnc
user_cons_columns
------------------------------------------------
table_nameconstraint_namecolumn_name
s_emps_emp_id_pkid
s_emps_emp_last_name_nnlast_name
selectcon.constraint_name,
con.constraint_type,
col.column_name,
col.table_name
fromuser_constraintscon,
user_cons_columnscol
wherecon.constraint_name=col.constraint_name;
第十一章
1.给表中插入数据
1）一次插入一条记录
insertintotable_name[（column,...）]
values（value,...）;
2）一次插入多条记录
insertintotable_name[（column,...）]
subquery;
insertintoqinry_temp（id,name）
selectid,first_namefroms_empwhereid>10;
2.更新表的中记录
updatetable_namesetcolumn=value,c
升级会员 