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