oracle7天学习经典笔记

--************************************************ --2010.9.26 oracle第2天2课

--建表语句

create table student( id number(4), name varchar(20), sex char(2), birthday date, sal number(7,2) );

--修改表的信息

--（1）添加字段

alter table student add (score number); --(2)修改字段长度

alter table student modify (score number(3,1)); --(3)删除一个字段

alter table student drop column score;

--查看表的结构（注意，此命令只有用在命令窗口才能够显示表的结构信息） desc student

--打开显示时间的操作开关（注意，此命令只有用在命令窗口才能够显示表的结构信息, --此命会将后面的查询操作执行时间显示出来） set timing on

--查询

select *from student

--如果查询某个字段为空的信息使用 is关键字 select *from student where birthday is null

--插入（注意：时间的插入写法）

insert into student values(1,'yoyo1','男','1-2月-1988','123.11') --转换时间插入的格式输入法，为中国人习惯型的输入格式 alter session set nls_date_format='yyyy-mm-dd' --转换时间输入格式后的插入写法

insert into student values(1,'yoyo1','男','1988-3-2','123.11')

--修改

update 表名 set 字段名1=值1，字段名2=值2... where 条件

--设置一个保存点 savepoint a --删除表信息

delete from student --回滚 rollback

--回滚后可以查询刚刚删除的信息 select *from student

--删除

--(1)删除表的内容，不会删除自动增长列已经增长到的某个值的记录 delete from student --（2）删除表结构 drop table student

--（3）删除表的内容，速度快，删除后没有日志记录,自动增长列会从1重新开始记录 truncate table student

--********************************************** --2010.9.26 oracle第2天3课

select *from emp select *from dept --查询速度的区别

--使用 * 的速度比指明 字段名 的速度 慢

--查询smith的工作，薪水和部门（注意：SMITH 内容的大小写，关键字不区分大小写） select deptno,job,sal from emp where ename='SMITH'

--查询职工一年的工资（sal*13）

--查询结果一样，问题(如果查询字段中有一个为null值，那么该字段跟其他字段合起来查询就为null值)

select (sal*13)+(comm*13) 年工资 from emp select sal*13+comm*13 年工资 from emp

--解决( nvl(comm,0)意思：如果comm字段为空，那么该字段值就为0，如果不为空那么就以该字段值为准进行下一步运算 )

select sal*13+nvl(comm,0)*13 年工资 from emp

--如何显示工资高于3000的员工

select ename,sal from emp where sal>3000

--如何显示工资在800到1600的员工(注意：使用between 800 and 1600，查询出的结果包含800，和1600的值的)

select ename,sal from emp where sal between 800 and 1600

--如何查找1982.1.1后入职的员工(注意： 时间输入 及使用 大于 符合) select ename,hiredate from emp where hiredate>'1-1月-1982' --或者

alter session set nls_date_format='yyyy-mm-dd'

select ename,hiredate from emp where hiredate>'1982-1-1'

--如何使用like操作符

-- %表示：任意0-多个字符，_表示：任意单个字符，?表示：所有 select ename,sal from emp where ename like 'S%' --查询员工名字第3个字符是O的员工

select ename,sal from emp where ename like '__O%'--此处是2条_(下划线)

--where条件中使用in

--查询员工号为：7369,7499,7521

select ename,empno from emp where empno in(7369,7499,7521) --查询没有上级的员工

select *from emp where mgr is null

--查询工资大于500或者职位为MANAGER，并且名字是以S开头的员工 select *from emp where (sal>500 or job='MANAGER') and ename like 'S%'

--order by (默认asc：低到高，高到底：desc) select *from emp sal order by sal desc

--按照部门号升序而雇员的工资降序排列 select *from emp order by empno asc , sal desc --按照部门号升序而雇员的入职降序排列

select *from emp order by empno asc , hiredate desc

--使用列的别名排序，使用年薪排名

select ename,sal*12 年薪 from emp order by 年薪 asc

select ename,sal*12+nvl(comm,0)*12 年薪 from emp order by 年薪 asc

--分页查询

--复杂查询

--如何显示所有员工的最高，最低工资 select MAX(sal) from emp select MIN(sal) from emp

--查询最高工资，最低工资员工的详细信息(注意:使用子查询--把查询出的结果看做一个结果集，来筛选)

select *from emp where sal=(select max(sal) from emp) --查询工资高于平均工资的员工信息

select *from emp where sal>(select avg(sal) from emp)

--grop by 和 having子句

--如何显示每个部门的最高工资，最低工资,并按照部门编号降序排列

select avg(sal),max(sal),deptno from emp group by deptno order by deptno desc

--显示每个部门的每种岗位的平均工资和最低工资

select avg(sal),min(sal),deptno,job from emp group by deptno,job

--显示评价工资低于2000的部门号和它的平均工资

select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000

--分组统计的规律

--分组函数只能出现在选择列表、having、order by子句中 --顺序：group by,having,order by

--笛卡尔集：多表查询的条件是，至少不能少于表的个数减1

--查询员工的姓名，工资，工资级别 select *from salgrade select *from emp select *from dept

select a.ename,a.sal,b.grade from emp a,salgrade b where a.sal between b.losal and b.hisal

--显示雇员名，工资，及所在部门名字，并按部门排序

select a.ename,a.sal,b.dname from emp a,dept b where a.deptno=b.deptno order by b.deptno

--自连接(单独一个表多次利用自己)

--如何显示某个员工的上级领导的名字(难点!!! 把一张表当2张表使用) select a.ename,b.ename from emp a,emp b where a.mgr=b.empno

--子查询又称嵌套查询（一个查询语句里面出现多个select关键字，也就是一个以查询结果为条件的语句）

--如何显示与smith同一部门的所有员工

--数据库在执行sql语句时，是从左到右扫描执行，而写sql语句又是从右往左写的

--写sql语句时，如果条件是多个，那么把第一次查询出来结果最多的那个放在左边，提高查询性能。最好从多的开始查询再到少的查询

select ename 名字,deptno 同一部门编号 from emp where deptno=(select deptno from emp where ename='SMITH')

--多行子查询

--如何查询和部门10相同的雇员的名字、岗位、工资、部门号

--返回的是多行的语句，那么条件使用in关键字，而不用 ‘ = ’ select *from emp where job in (select job from emp where deptno=10)

--使用all关键字

--如何查询工资比部门30的所有员工的工资高的员工的姓名、工资、部门号 --方法一

select ename,sal,deptno from emp where sal >all (select sal from emp where deptno=30) --方法二(查询快点:原因是在第二次筛选结果集中他的筛选的范围要小店)

select ename,sal,deptno from emp where sal >all (select max(sal) from emp where deptno=30)

--any关键字的使用

--如何显示工资比部门30号的任意一个员工工资高的员工姓名，工资，部门号 --方法一

select ename,sal,deptno from emp where sal >any (select sal from emp where deptno=30) --方法二(查询快点:原因是在第二次筛选结果集中他的筛选的范围要小店)

select ename,sal,deptno from emp where sal >any (select max(sal) from emp where deptno=30)

--多列子查询(如果根据查询的结果集里面包含2列，而不是1列) --查询与smith的部门和岗位完全相同的所有雇员 --注意：列匹配顺序要一样

select *from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH')

--如何显示高于自己部门平均工资的员工的信息(--难点 --)

--1.首先查询各个部门的平均工资和部门号（其中 a 代表平均工资的 别名） select deptno,avg(sal) a from emp group by deptno

--2.把上面的查询结果看做是一张 子表 ,并给他取个别名(其中 a1 代表上面所查询的结果的表的别名)

--给表取别名不能加as，给列取别名可以加as

select ename,a2.sal,a2.deptno,a1.a from emp a2,(select deptno,avg(sal) a from emp group by deptno) a1

where a2.deptno=a1.deptno and a2.sal>a1.a order by deptno

--分页查询（oracle分页一共有3种方式） --按雇员的id号升序查询 --方法一：rownum分页

select a1.*,rownum rn from (select *from emp) a1

--问题：如何显示第6条到10条的记录？使用分页显示 --1.rownum分页( select *from emp )

--2.显示 rownum[oracle分配的] 行号( select a1.*,rownum rn from (select *from emp) a1 ) --3.显示某一部分的信息（rownum只能用一次，不能用多次，使用多次试不会查询出数据结果的）

select a1.*,rownum rn from (select *from emp) a1 where rownum<=10

--不能写成如下这样：select a1.*,rownum rn from (select *from emp) a1 where rownum<=10 and rownum>=6

--4.完成分页效果机制 select *from

(select a1.*,rownum rn from (select *from emp) a1 where rownum<=10) where rn>=6

--5.几个查询变化

--a.如果指定查询列，只需修改最底层的查询：select *from emp select ename,sal from emp

--b.如果是排序，只需修改最底层的查询:select *from emp select ename,sal from emp order by sal

--方法二：（最快） --根据ROWID来分

select *from t_xiaoxi where rowid in (select rid from

(select rownum rn,rid from

(select rowid rid,cid from t_xiaoxi order by cid desc) where rownum<10000) where rn>0080) order by cid desc

--方法三：按分析函数来分（最慢） select *from

(select t.*,row_number() over (order by cid desc) rk from t_xiaoxi t)

where rk<10000 and rk>9980

--查询总共有多少记录：使用count()函数

--用查询结果创建表（常用技巧）

create table myemp2 (id,name,sal) as select empno,ename,sal from emp --查看表的结构：desc myemp2 select *from myemp2

--复杂查询，合并查询，使用关键字：union ,union all,intersect,minus

--(1)union 该操作符用于取得2个结果集的并集，使用时会自动去掉结果集中的重复行 --集合查询速度比and，between等快

select ename,sal,job from emp where sal>2500 union select ename,sal,job from emp where job='MANAGER' --(2)union all 会出现重复，取并集

select ename,sal,job from emp where sal>2500 union all

select

ename,sal,job from emp where job='MANAGER' --(3)intersect 取交集 --(4)minus 取差集

--创建数据库有2种方法 --(1)使用工具；

--dbca【数据库创建配置助手】

--(2)。。。

*******************************************************************

--2010年10月1日 --第3章 事务与加锁

--时间转换函数to_date('1988-12-1','yyyy-mm-dd')

--设置回滚点：注意（只有在事务没有提交前管用） savepoint a1; rollback to a1;

--在java程序中如何使用事务

--加入事务处理(在java代码中写入)

--conn.setAutoCommit(false);//设置不能提交点

--只读事务：指只允许执行查询的操作，而不允许执行dml（增，删，改）操作的事务 --使用只读事务可以确保用户只能取得某时点的数据 set transaction read only

--1.sql函数的使用

lower(char):将字符串转换为小写 upper():--大写 length():

substr(char,m,n):截取字符，m 代表从第几的个字符开始截取 ，n 代表取3个

--以首字母大写方式显示所有员工的信息 --第一步：取首写字母大写

select upper(substr(ename,1,1)) from emp --第二步：取后面小写部分

select lower(substr(ename,2,length(ename)-1)) from emp --合并：使用 || 符合

select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) from emp

--2.sql函数的使用

replace(char1,search_string,replace_string)--替换字符串 instr(char1,char2,[,n[,m]])--找函数的位置

--3.sql（数学）函数的使用:n 代表字字符 round(n,[m]):四舍五入 trunc(n,[m])：截取数字 mod(m,n)：取模

floor(n)：返回<=n的最大整数 ceil(n)：返回>=n的最大整数

--表dual是用来做测试用的表 select mod(10,3) from dual

--显示在一个月为30天的情况所有员工的日新金，忽略余数 使用floor,trunc...

--日期函数

--默认情况：dd-mm-yy(即12-10月-2010) sysdate：获取系统时间

add_months(时间或者字段,所要加的月份数) last_day():返回指定日期所在月份的最后一天 --dual表位测试表

select sysdate from dual--查询当前时间 --查询入职超过300个月的员工

select *from emp where sysdate>add_months(hiredate,300) --查询员工入职的天数

select sysdate-hiredate \入职天数\--去掉小数

select trunc(sysdate-hiredate) \入职天数\--找出各月倒数第3天受雇的所有员工 select hiredate,last_day(hiredate) from emp

select hiredate,ename from emp where last_day(hiredate)-2=hiredate

--转换函数（隐形转换）

to_char(要转换的时间字符,转换成什么格式):

select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp

--在插入时间数据时，如果插入需要精确到分钟，秒，那么显示的时候就需要用该函数转换一下，才会显示到时、分、秒 --to_char()用于人民币的格式转换

select to_char(sal,'L99,999.99') from emp select to_char(sal,'$99,999.99') from emp

--显示1980年,12月份入职的员工

select *from emp where to_char(hiredate,'yyyy')=1980 select *from emp where to_char(hiredate,'mm')=12

--to_date()函数...

--系统函数 sys_context()：

--(1)terminal:当前会话客户所对应的终端的标识符

select sys_context('USERENV','db_name') from dual--查询当前使用的数据库 --(2):language

select sys_context('USERENV','language') from dual --(3):session_user 当前用户

select sys_context('USERENV','session_user') from dual

--(4):current_schema 当前方案名（方案名与用户名一样）用户和方案的关系 --用户和方案的关系

select sys_context('USERENV','current_schema') from dual

--数据库管理员

sys(高于后者)与system用户的区别: (1)存储的数据的重要性不同 (2)权限不同

-- sys用户必需以as sysdba或者as sysoper身份登录

-- system用户可以以normal登录，但是就是一个普通用户

--注意：system可以以sysdba登录，但是如果以这样登录的话，那么就是一个sys用户了，具有他的所有权限

sysdba(高于后者)与sysoper的区别：sysoper>dba(普通用户)

前者可以打开、关闭数据库，创建和删除，后者不行，前者数据恢复可以是部分，可以是全部，而后者是全部恢复 --shutdown（关闭数据库） --startup（启动数据库）

--管理初始化的参数

--show parameter(该行没有运行出来，有点问题)

数据库表的逻辑备份与恢复 --逻辑备份和物理备份

前者：是将数据库的对象导出到一个磁盘里面存放（备份）

将磁盘的信息导入到数据库（恢复）--数据库在运行状态才能恢复

--导出：使用命令exp命令来完成

--注意：在导入导出的时候要到oracle的bin目录下EXP.exe中去操作********** 一、导出表

（1）导出表 a.导出自己的表

exp userid=scott/tiger@ORCL tables=(emp,dept) file=e:\\save1.dmp

b.导出其他方案的表(用户需要具有dba或者exp_full_database的权限) exp userid=scott/tiger@ORCL tables=(scott.emp,scott.dept) file=e:\\save2.dmp

（2）导出表的结构

exp userid=scott/tiger@ORCL tables=(emp,dept) file=e:\\save3.dmp rows=n

（3）使用直接导出方式(处理大表，速度快)

exp userid=scott/tiger@ORCL tables=(emp,dept) file=e:\\save4.dmp direct=y

二、导出方案，使用export工具导出一个方案或者多个方案中对象的表、索引、约束 (1)导出自己的方案

exp scott/tiger@ORCL owner=scott file=e:\\sava4.dmp

(2)导出其他的方案(用户需要具有dba或者exp_full_database的权限) exp system/manager@ORCL owner=(system,scott) file=e:\\sava4.dmp

三、导出数据库(利用export导出所有数据库中的对象及数据，用户需要具有dba或者exp_full_database的权限)

exp userid=system/manage@ORCL full=y inctype=complete file=e:\\save5.dmp --inctype=complete:增量备份 --full=y ：全部导出

--导入省略...第4天3讲10分钟

--数据字典（基表，存放静态的数据）和动态性能视图（动态的数据） 数据字典的所有者是sys用户 数据字典只能执行select语句

user_xxx，all_xxx,dba_xxx --当前用户所建的所有表

select table_name from user_tables --当前用户可以访问到的所有表 select table_name from all_tables

--当前系统各个用户的所有表（只有是dba角色和拥有查询所有表权限的用户才可以访问） select table_name from dba_tables

--用户名，权限，角色

查询dba_users可以显示所有数据库用户的详细信息 select username,password from dba_users

查询dba_sys_privs可以显示用户具有的系统权限

本文来源：https://www.bwwdw.com/article/d5i3.html