ORACLE笔记.docx
- 文档编号:26448466
- 上传时间:2023-06-19
- 格式:DOCX
- 页数:27
- 大小:25.34KB
ORACLE笔记.docx
《ORACLE笔记.docx》由会员分享,可在线阅读,更多相关《ORACLE笔记.docx(27页珍藏版)》请在冰豆网上搜索。
ORACLE笔记
第一章SQLPlus常用命令
1.connect建立连接connectscott/tiger;
2.password修改密码;
3.spoold:
\c,sqkl;
4.set环境变量值
Setlinesize100;
Setpagesize100;
创建和管理表
Orcle的数据类型:
varchar2(size)可变长的字符串,number(p,s)可变长的数值数据,date日期,long可变长字符数据最大2G,raw可变长二进制数据类型最大4KB,longraw可变长二进制数据,最大2GB,clob字符数据最大4GB,bfile存储外部文件的二进制数据最大4GB,rowid行地址,
Orcle数据表的命名规则:
必须以字母开头,必须在1–30个字符之间,必须只能包含A–Z,a–z,0–9,_,$,和#,必须不能和用户定义的其他对象重名,必须不能是Oracle的保留字
表的创建
创建空表CREATETABLEstu(
sidvarchar2(8)PrimaryKey,
namevarchar2(20)NotNull,
agenumber(3),
addrvarchar2(40)
);
拷贝一张表的全部信息
CREATETABLEnewtablename
ASSELECT*FROMexisttablename;
只拷贝已存在表的结构,不拷贝表的内容
CREATETABLEemp2ASSELECT*FROMempWHERE1=2;
修改表
添加新列(给emp2表增加新列)
ALTERTABLEemp2ADD(remarkVARCHAR2(100));
更改现有列的数据类型和字段宽度(修改remark列宽为200字节)
ALTERTABLEemp2MODIFY(remarkVARCHAR2(200));
更改列名(更改emp2表中remark列名为email)
ALTERTABLEemp2RENAMECOLUMNremarkTOemail;
删除表中现有列(删除emp2表中的email列)
ALTERTABLEemp2DROPCOLUMNemail;
添加或删除完整性约束条(将emp1表中的empno列设置为主键)
ALTERTABLEemp1ADDCONSTRAINTPK_emp2PRIMARYKEY(empno);
删除主键或唯一键约束(删除emp2表中的主键约束)
ALTERTABLEemp2DROPCONSTRAINTPK_emp2;
重命名表将(emp2表的表名修改为newemp;)RENAMEemp2TOnewemp;
删除表(删除emp2表)DROPTABLEemp2;
添加数据
INSERTINTOtablename(field1,field2,…)Values(val1,val2,…);
注意:
插入null和日期型字段
修改数据
UPDATEtablenameSETfield1=val1,field2=val2,…[WHERE子句];注意:
置空值
删除数据
1.DELETEFROMtablename[WHERE子句];注意:
WHERE子句中的空值判断
2TRUNCATETABLEtablename;
两种方法的区别:
DELETE删除,需要写日志和回滚段,能恢复。
TRUNCATE删除,不写日志和回滚段,不能恢复,但速度快。
第三章表及数据操作
表相关操作
查看用户定义的表
SELECTtable_nameFROMuser_tables;
查看用户定义的各种数据库对象
SELECTobject_name,object_typeFROMuser_objects;
查看用户定义的表,视图,同义词和序列
SELECT*FROMuser_catalog;
插入数据
向表中插入日期型值
1)SYSDATE记录当前系统的日期和时间
2)to_date()函数。
to_date('1982-01-01','yyyy-mm-dd')
3.事务
以第一个DML语句的执行作为开始
以下面的其中之一作为结束:
COMMIT或ROLLBACK语句
DDL或DCL语句(自动提交)
用户会话正常结束
系统异常终了
COMMIT和ROLLBACK语句的优点
1)确保数据完整性。
2)数据改变被提交之前预览。
3)将逻辑上相关的操作分组。
回滚到保留点
使用SAVEPOINT语句在当前事务中创建保存点。
使用ROLLBACKTOSAVEPOINT语句回滚到创建的保存点。
?
所有员工工资涨10%
Updateempsetsal=sal*1.1;
?
更新7369号员工的工作和工资使其与7876号员工相同。
updateempset(job,sal)=(selectjob,salfromempwhereempno=7876)wheredeptno=7369;
?
把SMITH的部门号更新为70。
updateempsetdeptno=70whereename=‘SMITH’
?
删除部门名为SALES的员工信息。
deletefromempwheredeptno=(selectdeptnofromdeptwheredname='SALES')
?
删除COMM为空的员工信息。
deletefromempwherecommisnull;
commit;
?
删除部门号为10的部门。
deletefromdeptwheredeptno=10;
第四章简单查询
1.select*fromempwheredeptnoin(10,20);
2.%代表任意多个字符,_代表一个字符
3.使用ORDERBY子句排序
ASC:
升序默认
DESC:
降序
3.
?
查询SMITH的薪水、工作、所在部门
selectsal,job,deptnofromemp1whereename='SMITH';
?
如何显示工资高于3000的员工
select*fromempwheresal>3000;
?
如何查找1982年以后入职的员工
select*fromemp1wherehiredate>to_date('1982-01-01','yyyy-mm-dd');
?
如何显示工资在2000到2500之间的员工
select*fromempwheresalbetween200and2500
注:
字符和日期要包含在单引号中,字符大小写敏感
?
如何显示每个雇员的年薪
selectename年龄,sal*12年薪fromemp1;
?
如何显示部门号为10和20的员工信息
select*fromemp1wheredeptnoin(10,20);
4.员工名字按小(大)写方式显示
selectlower(ename),upper(ename)fromemp;
显示名字正好为5个字符的员工姓名
selectenamefromempwherelength(ename)=5;
显示所有员工姓名的前三个字符
selectsubstr(ename,1,3)fromemp;
以首字母小写的方式显示员工姓名
selectlower(substr(ename,1,1))||substr(ename,2,length(ename))fromemp;
?
如何显示首字母是S的员工姓名、工资
selectename,salfromempwhereenamelike'S%';
?
如何显示第三个字符为大写O的员工姓名和工资
selectename,salfromempwhereenamelike'__O%';
?
查找工资高于1000或者岗位是MANAGER,同时名字首字母为J的员工信息
select*fromempwhere(sal>1000orjob='MANAGER')andenamelike'J%';
?
按工资从低到高的形式显示雇员信息
select*fromemporderbysal;
?
按部门升序、工资降序排序
select*fromemp1orderbydeptno,saldesc;
?
按年薪升序排序
select*fromemp1orderbysal*12desc;
?
Round函数
?
SELECTround(45.923,2),round(45.923,0),round(145.923,-2)FROMdual;
?
显示在1个月为30天的情况下,所有员工的日薪,忽略余数
selectround(sal/30)日薪fromemp1;
trim(leading/trailing/both‘子串’FROM‘字符串或列’)
----把子串从字符串或列的头/尾/两端删除,如缺省则默认为从两端删除
SQL>SELECTtrim(leading's'FROM'servicesservices')FROMdual;结果:
ervicesservices
5.数值函数
SELECTround(45.923,2),round(45.923,0),round(145.923,-2)FROMdual;结果:
45.9246100
TRUNC(45.926,2)截断MOD(1600,300)求余FLOOR(45.926)向下去整CEIL(45.926)向上去整
6.日期类型
SYSDATE:
取系统日期函数
默认的日期格式是DD-MON-RR,更改默认的日期格式:
SQL>altersessionsetnls_date_format='yyyy-mm-dd';
MONTHS_BETWEEN返回两个指定日期之间的月数
显示入职30个月以上的员工信息Select*fromempwheremonths_between(sysdate,hiredate)>30
ADD_MONTHS返回给指定的日期加上指定的月数后的日期值(显示入职30个月以上的员工信息)
select*fromempwhereadd_months(hiredate,30) NEXT_DAY返回指定日期以后的第一个星期几的日期 返回当前日期的下一个星期二的日期 selectnext_day(sysdate,2)fromdual LAST_DAY 返回指定日期当月的最后一天的日期值 selectlast_day(sysdate)fromemp1; Selectround(sysdate,’year’)fromdual; Selectround(sysdate)fromdual; ? 显示入职满10年的员工信息 select*fromempwhereadd_months(hiredate,120) ? 显示各月倒数第三天受雇的所有员工 select*fromempwhere(last_day(hiredate)-hiredate)=3 select*fromempwherehiredate=(last_day(hiredate)-2) ? 显示员工进入公司的天数 selectename,sysdate-hiredatefromemp; (在日期上加上或减去一个数字结果仍为日期。 两个日期相减返回日期之间相差的天数。 ) 7.转换函数TO_CHAR,TO_DATE,TO_NUMBER selectto_char(sysdate,’yyyy-mm-dd’)fromdual; selectto_char(1210.73,'$9,999.00')fromdual; selectto_date(sysdate,’yyyy-mm-dd’)fromdual; selectto_char(sysdate,'ccyear-mon-dd: hh: mi: ssday')fromdual; 8.通用函数 NVL(expr1,expr2)如果expr1为null,则返回expr2。 如果expr1不为null,则返回expr1 selectnvl(comm,0)fromemp; NVL2(expr1,expr2,expr3)如果expr1为null,则返回expr3。 如果expr1不为null,则返回expr2 selectnvl(comm,comm||”元”,0)fromemp; NULLIF(expr1,expr2)如果expr1=expr2,则返回null; 否则返回expr1。 selectnullif(ename,’SCOTT’)fromemp; 第五章复杂查询 1.分组函数AVGCOUNTMAXMINSUM SELECT[column,]group_function(column),... FROMtable [WHEREcondition] [GROUPBYcolumn] [ORDERBYcolumn]; ? 查询最高工资和最低工资 selectmax(sal),min(sal)fromemp; ? 查询员工的平均工资和工资总和 selectavg(sal),sum(sal)fromemp; ? 共有多少员工 selectcount(*)fromemp; ? 工资最高的员工的名字、工作岗位 selectename,jobfromempwheresal=(selectmax(sal)fromemp); ? 工资高于平均工资的员工信息 select*fromempwheresal>(selectavg(sal)fromemp); ? 工资低于平均工资且入职时间在80年前的涨10% updateempsetsal=sal*1.1wheresal<(selectavg(sal)fromemp)andhiredate ? 每个部门的平均工资和最高工资 selectavg(sal),max(sal)fromempgroupbydeptno; ? 每个部门各岗位的平均工资和最高工资 selectavg(sal),max(sal)fromempgroupbydeptno,job; ? 平均工资低于2000的部门号和平均工资 selectdeptno,avg(sal)fromempgroupbydeptnohavingavg(sal)<2000 不能在WHERE子句中使用组函数 可以在HAVING子句中使用组函数 ? 想知道某员工所在的部门名,该怎么办 selectdnamefromemp,deptwhereemp.deptno=dept.deptnoandename='SMITH'; ? 显示SALES部门位置及其员工姓名 Selectloc,enamefromemp,deptwhereemp.deptno=dept.deptnoanddname='SALES'; ? 查找雇员名、工资及所在部门的名称 selectename,sal,dnamefromemp,deptwheredept.deptno=emp.deptno; ? 显示员工姓名、部门名,按部门号排序 selectename,dnamefromemp,deptwheredept.deptno=emp.deptnoorderbyemp.deptno 左外连接: wheret1.column=t2.column(+); 左表不变,右表+null 右外连接: wheret1.column(+)=t2.column; 右表不变,左表+null ? 显示员工姓名、工资及工资级别 selecta1.ename,a1.sal,a2.gradefromempa1,salgradea2wherea1.salbetweena2.losalanda2.hisal; ? 显示某员工上级的名字 selectp1.ename,p2.enamefromempp1,empp2wherep1.mgr=p2.empno; ? 显示FORD的上级 selectp1.ename,p2.enamefromempp1,empp2wherep1.mgr=p2.empnoandp1.ename='FORD'; ? 查询部门ACCOUNTING中薪水大于2000的员工信息。 select*fromemp,deptwhereemp.deptno=dept.deptnoanddname='ACCOUNTING'andsal>2000 ? 谁的工资比SCOTT高? select*fromempwheresal>(selectsalfromempwhereename='SCOTT'); ? 显示与SMITH同一部门的所有员工 select*fromempwheredeptno=(selectdeptnofromempwhereename='SMITH'); ? 查询与部门10的工种相同的员工姓名、工种 selectename,jobfromempwherejobin (selectjobfromempwheredeptno=10); ? 查找工资比部门30所有员工工资高的员工信息 select*fromempwheresal>all (selectsalfromempwheredeptno=30); ? 查找比部门30任意一个员工的工资高的员工 select*fromempwheresal>any (selectmin(sal)fromempwheredeptno=30); ? 查询与SMITH部门和岗位完全相同的员工 select*fromempwhere(job,sal)= (selectjob,salfromempwhereename='SMITH'); ? 查询高于自己部门平均工资的员工信息 selecta.*fromempa,(selectavg(sal)avgsal,deptnofromempgroupbydeptno)bwherea.deptno=b.deptnoanda.sal>b.avgsal; ? 查询高于自己部门平均工资的员工信息 select*fromempawhere sal>(selectavg(sal)fromempbwhereb.deptno=a.deptno) selecta.*fromempa,(selectavg(sal)avgsal,deptnofromempgroupbydeptno)bwherea.deptno=b.deptnoanda.sal>b.avgsal; ? 如何从某个雇员往他的上级列出该雇员的层次结构(从底到顶) selectlpad('',4*(level-1))||ename name,empno,mgrfromemp startwithempno=7369 connectbypriormgr=empno; (colnamefora30) ? 创建视图 createorreplaceviewv_emp_20 as select*fromempwheredeptno=20 第六章其他数据库对象 1.视图: 创建视图 CREATEVIEWempvu80 ASSELECTemployee_id,last_name,salary FROMemployees WHEREdepartment_id=80; Withcheckoption字句能够确保DML只能在特定的范围内执行 当视图定义中包含以下元素之一时不能使用delete: 组函数 GROUPBY子句 DISTINCT关键字 ROWNUM伪列 当视图定义中包含以下元素之一时不能使用update: 组函数 GROUPBY子句 DISTINCT关键字 ROWNUM伪列 列的定义为表达式 当视图定义中包含以下元素之一时不能使用insert: 组函数 GROUPBY子句 DISTINCT关键字 ROWNUM伪列 列的定义为表达式 表中非空的列在视图定义中未包括 删除视图只是删除视图的定义,并不会删除基表的数据 DROPVIEWview;(DROPVIEWempvu80;) 2.序列: 创建序列 Createsequenceemp_sequence Incrementby1 Startwith1 Nomaxvalue Nocycle Cache10; 修改序列 Altersequenceemp_sequence Startwith1 Incrementby2 nomaxvalue Cycle Cache20; Nextval返回序列中下一个有效的值currval存放序列的当前值 (Selectemp_sequence.nextvalfromdual;) NEXTVAL和CURRVAL伪列不可用于下列场合: (1)视图定义; (2)带DISTINCT关键字的SELECT语句; (3)带GROUPBY、HAVING或ORDERBY子句的 SELECT语句; (4)SELECT、DELETE、UPDATE语句的子查询。 3.索引: ✓一种数据库对象 ✓通过指针加速Oracle服务器的查询速度 ✓通过快速定位数据的方法,减少磁盘I/O ✓索引与表相互独立 ✓Oracle服务器自动使用和维护索引 既然索引能加快查询速度,是否索引数量越多越好呢? 为什么? 答: 不是,索引使用于数据量大的表,减少系统查找时间,从而优化系统,要是索引数量过多,不仅会占据大量物理空间,还极大的浪费系统资源。 以下情况适合创建索引: •列中数据值分布范围很广 •列中包含大量空值 •列经常在WHERE子句或连接条件中出现 •表经常被访问而且数据量很大,访问的数据大概占数据总量的2%到4% 下列情况不适合创建索引: •表很小 •列不经常作为连接条件或出现在WHERE子句中 •查询的数据大于2%到4% •表经常更新 •加索引的列包含在表达式中 D
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ORACLE 笔记
![提示](https://static.bdocx.com/images/bang_tan.gif)