oracle学习2Word文档格式.docx
《oracle学习2Word文档格式.docx》由会员分享，可在线阅读，更多相关《oracle学习2Word文档格式.docx（23页珍藏版）》请在冰豆网上搜索。
取出部门中姓名为clirk的记录（注意取出过程中ename用单引号隔开）
17.selectename,salfromempwheresal>
1500;
取出部门中薪水大于1500的人的姓名
18.selectename,sal,deptnofromempwheredeptno<
>
10取出部门中的部门号不等于10的
19.selectename,sal,deptnofromempwhereename>
'
CBA'
取出部门中员工名字大于CBA的员工（实际比较的是ACIIS码）
20.selectename,salfromempwheresalbetween800and1500
selectename,salfromempwheresal>
=800andsal<
=1500;
（取出800和1500之间的数）
21.selectename,sal,commfromempwherecommisnull（选出其中的空值）
selectenmae,sal,commfromempwherecommisnotnull（选出其中的非空值）
22.selectename,sal,commfromempwheresalin（800,1500,2000）;
取出这3者之中的
selectename,sal,commfromempwhereenamein（'
simth'
）;
23.selectename,sal,hiredatefromempwherehiredate>
3-4月-81'
取出符合条件的日期，月前面的数字只能为1，2，3，4，5，6，7，8，9，10，11，12；
日期且要加单引号。
24.selectename,sal,fromempwheresal>
1000ordeptno=10;
找出工资薪水大于1000或者部门号等于10的员工
25.selectename,salfromempwheresalnotin（500,1000）;
查找薪水不是500和1000的员工姓名和月薪
selectename,salfromempwheresalnotbetween500and1000;
查找薪水不在500到1000的员工姓名和月薪
26.selectename,salfromempwhereenamelike'
%ALL%'
selectename,salfromempwhereenamelike'
_%A%'
selectename,salfromempwhereenamelike'
_A%'
__A%'
查找姓名中含有ALL的客户信息，一个横线代表一个通配符
27.selectename,salfromempwhereenamelike'
_%$%%'
escape'
$'
自己指定转义字符，当搜索含有%时，加\或$。
_%\%%'
查找中间含有%相匹配的客户信息，运用转义字符
28.select*fromdeptorderbydeptno对表中元素按部门号排序
select*fromdeptorderbydeptnodesc默认为升序，asc省略，可以用desc按降序
29.selectename,salfromempwheresal<
1000orderbysaldesc按照查询条件来查询，并排序（asc升序排列）
30.selectename,sal*12fromempwhereenamenotlike'
andsal>
800orderbysaldesc
31.selectlower（ename）fromemp将ename都转化为小写lower是函数能将字母转化为小写
32.selectenamefromempwherelower（ename）like'
_%a%'
找出ename中所有的含有a的字符
33.selectename,substr（ename,2,3）fromemp;
从第2个字符开始截取3个字符
34.selectchr（65）fromdual;
将65转化为字符
35.selectascii（'
A'
）fromdual;
将ACSII码转化为字符串
36.selectround（23.565）fromdual四舍五入
36.selectround（23,4565,2）fromdual四舍五入到第二位
37.selectto_char（sal,'
$999,999,999'
）fromemp;
按指定格式输出
selectto_char（sal,'
L99,999,9999'
L代表本地字符
38.selecthiredatefromemp
selectto_char（hiredate,'
YYYY-MM-DDHH:
MI:
SS'
时间格式的显示
selectto_char（sysdate,'
ss'
十二小时制显示系统时间
YYYY-MM-DDHH24:
二四小时制显示系统时间
39.selectename,hiredatefromemp
wherehiredate<
to_date（'
2005-2-312:
32:
23'
'
40selectsalfromempwheresal>
to_number（'
$1,250.00'
$9,999.99'
取出比它大的一切字符串（把特定格式的数字转化成字符）
41selectename,sal+nvl（comm,0）fromemp;
comm值为空的用0来替换，单行函数（以一条记录为条件）一条对一条
42.selectMax（sal）fromemp;
selectMin（sal）fromemp;
selectavg（sal）fromemp;
selectsum（sal）fromemp;
selectcount（*）fromemp;
查看表中一共有多少条记录
selectcount（*）fromempwheredeptno=10;
查找部门10一共有多少人；
43.selectavg（sal）,deptnofromempgroupbydeptno;
按部门号进行分组
selectdeptno,job,max（sal）fromempgroupbyjob,deptno;
按工作和部门号进行分组；
44.selectenamefromempwheresal=（selectmax（sal）fromemp）;
子查询，查找部门中薪水最高的员工姓名
45.groupby注意：
出现在select列表中的字段，如果没有出现在组函数中必须出现在groupby子句中
46.selectavg（sal）,deptnofromempgroupbydeptnohavingavg（sal）>
2000;
选出部门中平均薪水大于2000的部门，
47.select*fromempwheresal>
100groupbydeptnohaving..........orderby........
先取数据--过滤数据------分组----对分组限制-------排序
48.selectavg（sal）fromempwheresal>
2000groupbydeptnohavingavg（sal）>
1500orderbyavg（sal）desc;
查找部门中平均薪水打印2000的员工并按部门号进行排序，查询分组后的平均薪水必须大于1500，查询结果按平均薪水从低到高排列
49.selectenamefromempwheresal>
（selectavg（sal）fromemp）;
查找出员工中薪水位于部门平均薪水之上的所有员工
50.selectename,sal,emp.deptnofromemp
join（selectmax（sal）max_sal,deptnofromemp
groupbydeptno）t
on（emp.sal=t.max_salandemp.deptno=t.deptno）;
查找每个部门中薪水最高的
连接
51.selecte1.ename,e2.enamefromempe1,empe2wheree1.mgr=e2.empno;
表的自连接
52.selectdname,enamefromempcrossjoindept交叉连接，笛卡尔
SQL99中的新语法
53.selectename,dnamefromempjoindepton（emp.deptno=dept.deptno）;
54.selectename,dnamefromempjoindeptusing（deptno）;
查找emp和dept表中deptno相同的部分。
55.selectename,dname,gradefromempejoindeptdon（e.deptno=d.deptno）
joinsalgradeson（e.salbetweens.losalands.hisal）--（三表查找）
whereenamenotlike'
56.selecte1.ename,e2.enamefromempe1joinempe2on（e1.mgr=e2.deptno）;
57.selecte1.ename,e2.enamefromempe1leftjoinempe2on（e1.mgr=e2.deptno）左外表连接
selectename,dnamefromemperightjoindeptdon（e.deptno=d.deptno）右外连接
selectename,dnamefromempefulljoindeptdon（e.deptno=d.deptno）全连接
58.求部门中薪水最高的
selectename,salfromempjoin（selectmax（sal）max_sal,deptnofromempgroupbydeptno）t
on（emp.sal=t.max_salandemp.deptno=t.deptno）;
59.求部门中薪水等级的平均值
selectdeptno,avg（grade）from（selectdeptno,ename,grade,fromempjoinsalgradeson（emp.salbetweens.losalands.hisal））tgroupbydeptno;
60.查找雇员中哪些是经理人
selectenamefromempwhereempnoin（selectmgrfromemp）;
61.selectdistincte1.salfromempe1joinempe2on（e1.sal<
e2.sal）;
自连接（不用组函数求出最高薪水）
selectdistinctsalfromemp
wheresalnotin（selecte1.salfromempe1joinempe2on（e1.sal<
e2.sal））;
62.selectdeptnofrom（selectavg（sal）max_sal,deptnofromempgroupbydeptno）
wheremax_sal=（selectmax（avg_sal）
from（selectavg（sal）avg_sal,deptnofromempgroupbydeptno））;
selectdeptnofrom（selectsum（sal）sum_sal,deptnofromempgroupbydeptno）
wheresum_sal=（selectmax（sum_sal）
from（selectsum（sal）sum_sal,deptnofromempgroupbydeptno））;
查找部门中部门薪水最大的部门号
63.求平均薪水最大的部门的部门编号
selectdeptno,avg_salfrom（selectavg（sal）avg_sal,deptnofromempgroupby
deptno）whereavg_sal=（selectmax（avg（sal））fromempgroupbydeptno）;
DML语句：
更、删、改、查
创建权限，connsys/adminassysdba
grantcreatetable,createviewtoscott;
首先在C:
下面建个文件夹备份文件
1.createNewUser方法
1.--backupscott
exp
2.createuser（创建用户）用超级管理员模式进入
createuseryunidentifiedbykang1234defaulttablespaceusersquota10Monusers;
grantcreatesession,createtable,createviewtokafei（给kafei这个用户授予权限）
3.importthedata（导入备份数据）
imp
2.insert
insertintodeptvalues（50,'
game'
bj'
）插入一条记录
insertintodept2（deptno,dname）values（78,'
games'
插入指定的几条记录
insertintodept2select*fromdept插入指定的表（表结构要一样）
rollback;
回退
createtableemp2asselect*fromemp;
创建数据库表2来备份emp这张表
3.updateemp2setsal=sal*12wheredeptno=10;
update的用法
4.deletefromdept2wheredeptno<
25;
删除语句的用法
--28
DDL语言
1.创建表:
createtablet（avarchar2（10））;
2.droptablet删除表
mit所有的提交，所有修改都结束了。
对于rollback无效，一个事务开始于第1条DML语句
碰到执行DDLDCL语句事务自动提交对于rollback无效
建表语句
建学生信息表：
createtablestu
（idnumber（6）,
namevarchar2（20）constraintstu_name_nnnotnull,
sexnumber
（1）,
agenumber（3）,
sdatedate,
gradenumber
（2）default1,
classnumber（4）,
emailvarchar2（50）unique（唯一约束）
非空唯一主键外键chick
（idnumber（6）primarykey,（主键约束）
namevarchar2（20）constraintstu_name_nnnotnull,（非空约束）
emailvarchar2（50）,
constraintstu_name_uuiunique（email,name）组合性约束
--29
主键约束方法二
classnumber（4）referencesclass（id）,（参考class这张表，参考字段）
emailvarchar2（50）,
constraintstu_id_pkprimarykey（id）,
外键约束
createtableclass
（idnumber（4）primarykey,（id为被参考字段,被参考的字段必须是主键）
namevarchar2（20）notnull
）
（
idnumber（6）,
classnumber（4）
constraintstu_class_fkforeignkey（class）referencesclass（id）,
像外键中插入关键字，
1.insertintoclassvalues（1000,'
c1'
2.insertintostu（id,name,class,email）values（1,'
a'
1000,'
3.altertablestuadd（addrvarchar（20））;
添加表的结构
4.altertablestudrop（addr）;
删除表结构
5.altertablestumodify（addrvarchar2（150））;
修改精度
6.altertablestudropconstraintstu_class_fk;
删除约束条件
7.altertablestuaddconstraintstu_class_fkforengnkey（class）referencesclass（id）,添加约束条件
查找当前用户下有哪些表和哪些视图及哪些约束
8.selecttable_namefromuser_names
9.selectview_namefromview_names
10.selectconstraint_name,table_namefromuser_constraints;
descdictionary数据字典表
descuser_tables当前用户下面有多少张表
selecttable_namefromuser_tables;
查找当前用户有多少张表
索引：
创建索引
createindexidx_stu_emailonstu（email）;
dropindexidx_stu_email;
查找索引
selectindex_namefromuser_indexes;
索引读的速度快了，插入速度变慢
view视图
视图赠加了维护的量
序列：
createtablearcticle
（idnumber,
titlevarchar2（1024）,
contlong
序列的创建sequence产生独一无二的序列，而且是oracle独有的
createsequenceseq;
selectseq.nextvalfromdual;
查找序列号
insertintoarcticlevalues（seq.nextval,'
b'
往表中插入序列
--35
数据库设计的3范式
第一范式：
设计任何表都要有主键，列不可分
第二范式：
如果有2个主键的话，不能存在部分依赖
第三范式，不能存在传递依赖
PL-sql
例子1：
SQL>
setserveroutputon;
begin（必要的--程序开始执行）
2
升级会员 