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