Oracle笔记.doc

三版Oracle(共五天) day1 （陆晓伟）
一．lesson 1（视频课件35.1和35.2）
Oracle版本：------7i—>8—>8i—>9i—>10g(i：Internet g:网格)
1.关系数据库三要素：
1).实体（表）————class
2).属性（列）————property属性/field
3).关系（pk和fk）————对应Java中的关联关系，继承关系
pk:无序不重复，唯一，not null。（每个实体只有一个主键）
fk:可以重复，可以为null ，必须指向pk, fk的值必须在pk里取
2. SQL（结构化查询语言）：所有数据库的通用语言。(各别地方，不同DB有区别！)
PLSQL：Oracle公司对SQL的扩展，Oracle数据库的特有语言。
SQLPLUS：Oracle公司提供的工具，用来写/发送SQL语句的。
注 SQLPLUS语句和SQL之间的比较：
相同：不区分大小写
区别：1.SQLPLUS语句可以简写，而SQL不可以；
2.SQLPLUS语句发送后不能存入Buffer，而SQL或PLSQL语句可以；
3.SQLPLUS语句后的；号不要求，而SQL必须以分号结尾。
3.创建普通用户并授权
SQL>create user 用户名 identified by
2 default tablespace users
3 temporary tablespace temp;
User created.
SQL>grant resource,connect to 用户名;
Grant succeeded.
SQL> alter user 用户名 identified by 新密码;
4.三种登陆sqlplus工具的方式：
1）SQL>sqlplus 回车
username:
password:
2）SQL>sqlplus username回车
password：
3）SQL>sqlplus username/password 回车
5.SQLPLUS命令：
SQL>describe(desc)+表名: 查看表的结构，可看到：columnname,是否not null,type
Oracle中的几种数据类型：number(数字类型),date,varchar2(字符串类型,变长),varchar 。
SQL>list(L):显示buffer的内容，buffer属于当前的SQLPLUS，里面只能存放一条SQL命令
SQL> /<====>run(r): 运行buffer里的命令
SQL> column（col）------SQLPLUS命令
1.col[umn] 列名 heading ‘别名’ ：对列进行预定义，----->此为用SQLPLUS命令为列起别名。
2.col[umn] 列名 format an或 L或$999,999.00 :对某列数据的 显示字长或格式 进行预定义。
注：an：仅适用于字符型或日期型的列，用于控制每行数据显示长度
$999,999.00：仅适用于number类型的列，控制每行数据的显示格式
L：表示根据你的数据库方言去选择货币符号，这里如果用L，则显示的为￥.
如：col last_name format a10 ：表示使last_name列的数据占10个字符长度来显示。
col name heading 'na' format a10
注 用SQLPLUS命令col为列起别名 与 用sql命令起别名的区别：
col预定义: 作用的时间长,且只要不退出sqlplus，对所有表中叫这个列名的列都生效。
sql:当次生效，且仅对当前sql的当前表中的这个列生效。
SQL> change(c):改变缓冲区中命令
1. SQL> c/old串/new串
2. SQL> 1 select * (当命令得某一行中需要改动较多时，使用该命令来更改某一具体行)
3. SQL> edit(ed)------>vi：unix系统
ed------>记事本：windows
SQL> save ：buffer----->file 将buffer里的内容保存出去：
1.new ------->save filename ：保存并新建
2.override---->save filename replace(rep) ：保存并覆盖
SQL> get filename：file------>buffer 把文件的内容导回缓冲区
SQL> start(@) filename ======get + run(/) 把文件内容导入缓冲区并且执行。
SQL> spool filename------>spool off：抓屏，将抓到的所有内容都保存到filename文件中
二．lesson 2
1.order by子句: 排序
select id,last_name,dept_id from s_emp
1)order by dept_id;
order by title; //注：可以不按照要查询的列进行排序，只要表中有的列就可以。
2)order by last_name,dept_id;先按名字升序排,名字相同再按dept_id升序排。不指明时默认按照升序(ASC)排列
3)order by last_name desc;
order by dept_id,last_name desc; //dept_id升序,last_name降序
4)null //注：null在oracle里是最大的值。
5)order by 2; <===> order by last_name //注：这里的2，表示select里面的第二列。
6)按列的别名排序
select id,last_name name from s_emp order by NAME;
2.where子句：条件判断
select id,last_name,dept_id,start_date from s_emp
1)where dept_id=41;
where last_name='Smith'; ---->严格区分大小写
where start_date='08-mar-90';---->必须这么写，dd-mon-yy是Oracle数据库中默认的日期格式。
2)where salary>1000;
3)关键字：between --- and---
where dept_id between 41 and 45; ---包括边界值
where dept_id between 45 and 41; ---无语法错误，但查不出结果！注:必须值小的在前,反了,将查不出记录。
4)where dept_id in(41,43,45); ----枚举，用in
5)where commission_pct=null;----查不出记录，错误！因为表达式中出现null，结果一定为null！
where commission_pct is null;-----正确！！
where commission_pct is not null;
6)where last_name like 'S%';----模糊查询
unix中通配符: *（表0到多个） ?（有且仅有一个）
oracle中通配符: %（表0到多个） _（有且仅有一个）
例：查出last_name是以'S_'开始的：
where last_name like 'S_%'; ----> error！！！
where last_name like 'S\_%' escape '\'; ---->right！！！
注：oracle中没定义固定的转义字符，需要用转义字符时，须用escape指明谁是转义字符！！！
7)非: != <> not between…and not in not like is not null
8)执行优先级：括号>and > or && > ||
Oracle day2
一．lesson 3（day2 上午 视频课件36.1）
单行函数（Single Row Function）:应用于每一个值，并且产生每一个结果。
1.针对varchar2型的:（LOWER、upper、initcap单词首字母大写、concat、substr、length、nvl）
1) 处理空值函数 nvl('hello','world') --等价---> 'hello' (由于hello串永远都不为null，所以等价于’hello’)
nvl(null,'world') --等价---> 'world'
2) select id,last_name,dept_id from s_emp where lower(last_name)='smith'; ---LOWER应用很多！
3) select id,concat(last_name,first_name),dept_id from s_emp;
concat(last_name,first_name)--->result1;
concat(result1,title); ----等价---> concat(concat(last_name,first_name),title);
注：||连接多个列的情况较常用，因为concat连接多个串时需要函数的嵌套。
4）select substr('String',-4,3) from dual; //显示结果：rin ---SUBSTR应用很多！
注：当所查的列不在任何表中或不清楚它能从哪个表中查到时，就可以用dual（dummy table）盲(哑)表,它的存在是为了维护select的完整性
例：substr可辅助进行模糊查询
select id,last_name,dept_id from s_emp
where last_name like 'S%'; ---等价于--> where substr(last_name,1,1)='S';
2.针对number型的:
round：四舍五入的截取数字值 Round(45.923,2)-->45.92 Round(45.923,0)-->46 Round(45.923,-1)-->50
trunc：直接截取数字值 trunc(45.923,2)-->45.92 trunc(45.923,0)-->45 trunc(45.923,-1)-->40
3.针对date型的:
months_between(’01-SEP-95’,’11-jan-94’) ------>19.774194 :两个日期之间的月份数
add_months (’11-JAN-94’,6)----->’11-JUL-94’:Add calendar months to date.
next_day(’19-jun-08’,Friday) : 接下来第一个星期五的日期（从当前天往后查，找第一个符合的）
last_day(’01-sep-95’)---->’30-sep-95’ :本月的最后一天的日期。
Round:四舍五入的截取日期
round(’25-MAY-95’,’MONTH’)---->01-JUN-95
round(’25-MAY-95’,’YEAR’) ---->01-JAN-95
Trunc :直接截取日期
trunc(’25-MAY-95’,’MONTH’)---->01-MAY-95
trunc(’25-MAY-95’,’YEAR’) ---->01-JAN-95
1） SQL> select sysdate from dual; 查看当前系统时间
SYSDATE
--------------
22-7月 -09
2）SQL> select to_char(sysdate,'dd-mon-yyyy,hh24:mi:ss pm') sdate from dual;//设置显示格式
SDATE
----------------------------------------
22-7月 -2009,09:58:28 上午
注：Oracle中默认的日期显示格式是DD-MON-YY。
3）标准的日期格式:
年: yy 08
yyyy 2008
rr 08（为解决2000年出现的千年虫问题而出现的）
rrrr 2008
year two thousand and eight
月: mm 06
mon JUN
month june
日: dd 19
ddth 19th
ddsp ninteen
ddspth ninteenth
星期: d 4
dy thu
day thursday
小时: hh24 22
hh 10
am pm
分钟: mi
秒: ss
4）RR Date Format
注：1950—2050,分成前50年和后50年，RR格式对于小于50的年份，前补20--；对于大于50的，前补19--。
Current Year
Specified Date
RR Format
YY Format
1995
27-oct-95
1995
1995
1995
27-oct-17
2017
1917
2001
27-oct-17
2017
2017
2001
27-oct-95
1995
2095
4.类型转换函数： （注：日期和字符串之间的转化是重点！！！）
to_char：converts a number or date string to a character string. 注：to_char 多在select显示日期时用。
to_number：converts a character string containing digits to a number.
to_date：converts a character string of a date to a date value. 注：to_date多用于insert和update时
1）select id,last_name,dept_id,to_char(start_date,'yyyy-month-dd,hh24:mi:ss') "sdate" //"sdate"为别名。
from s_emp where dept_id=41;
//由于系统中的日期只有年月日，所以如需同时显示时分秒，需自己把日期转为字符串类型格式来显示。
2）update s_emp set start_date=to_date('19-jun-08,11:24:56','dd-mon-yy,hh24:mi:ss') where id=100;
//由于系统中的日期只有年月日，所以如想修改为带有时分秒的日期，需要将新日期转化为日期格式，以让系统在修改时按新的日期格式'dd-mon-yy,hh24:mi:ss' 来做日期。
update s_emp set start_date= '19-jun-08' where id=100;新日期与系统日期格式相同时,直接赋值无需转换即可
3）update s_emp set start_date=to_date('19-jun-90','dd-mon-yy') where last_name='S_abc';改后的查询语句如下：
select id,last_name, to_char(start_date,’dd-mon-rrrr’) from s_emp where last_name=’S_abc’;//结果19-jun-2090
4）select id,last_name,to_char(salary,'$99,999.00') from s_emp;//把数字转化为字符串
5）select id,last_name,to_char(start_date,'fmddspth “of ” Month YYYY fmhh:mi:ss am') "hiredate" from s_emp;
注：1)fm作用: 格式化时间，在不引起歧义的情况下去掉前置的零和空格。
它类似于unix中开关变量,第一次写时打开功能,再写时关闭。
2)双引号在Oracle中的两处用途：
a)为列取的别名 b)引号中再需要用引号时，用双引号把里面的串括起来。
二．lesson 4 Displaying Data from Multiple Tables （day2 下午 视频课件36.2）
Join:数据来源于多张表,叫多表联合查询,多个表之间要做连接。（重点！！！）
1.等值连接(内连接) 条件: fk-----pk
例：打印出员工的名字,以及所在部门的名字。
select s_emp.last_name,s_emp.dept_id,s_dept.id,s_dept.name from s_emp,s_dept where s_emp.dept_id=s_dept.id;
注：可以给表起别名(表的别名使用简单字符即可，因为表的别名不显示在屏幕中)
Oracle里的写法：
select e.last_name,e.dept_id,d.id,d.name
from s_emp e,s_dept d
where e.dept_id=d.id;
标准sql的写法:
select e.last_name,e.dept_id,d.id,d.name
from s_emp e inner join s_dept d //inner join表示 内连接，并且可简写成join。
on e.dept_id=d.id //on后只能为连接条件
where e.last_name='Smith'; //普通条件要放在where之后
例：打印出员工的名字,所在部门的名字,以及所在地区的名字。
Oracle里的写法：
select e.last_name,d.name,r.name
from s_emp e,s_dept d,s_region r
where e.dept_id=d.id and d.region_id=r.id;
标准sql的写法:
select e.last_name,d.name,r.name
from s_emp e join s_dept d
on e.dept_id=d.id
join s_region r
on d.region_id=r.id;
注意：等值连接可以连接两张表,但是不能处理空值，而外连接可以处理空值！！-->此为外连接和内连接的区别.
2.外连接(左外,右外,全外) ：当fk为空值时，需要补空值的连接。
例：打印所有的客户名字,以及所对应的销售代表的名字
1)左外连接:以左表为主,而右表负责补空值.
oracle:
select c.name,c.sales_rep_id,e.id,e.last_name
from s_customer c,s_emp e
where c.sales_rep_id=e.id(+); //客户表内容都显示，e.id(+)代表员工表如果没有值，就负责补一个空值。
标准sql:
select c.name,c.sales_rep_id,e.id,e.last_name
from s_customer c left outer join s_emp e // left outer join表左外连接,注意要把主要的表放在左侧.outer可省
on c.sales_rep_id=e.id;
2)右外连接:以右表为主,而左表负责补空值.
oracle: select c.name,c.sales_rep_id,e.id,e.last_name
from s_customer c,s_emp e where e.id(+)=c.sales_rep_id;
标准sql: select c.name,c.sales_rep_id,e.id,e.last_name
from s_emp e right join s_customer c on c.sales_rep_id=e.id;
3)全外连接：（只支持标准SQL写法）
select c.name,c.sales_rep_id,e.id,e.last_name
from s_customer c full outer join s_emp e
on c.sales_rep_id=e.id;
注意:对于全外,Oracle不支持两边都补空值的写法,而只支持标准SQL写法.
3.自连接: 本表的fk指向本表的pk，通过一个表起两个别名来解决。
例1: 打印出员工的姓名和他的经理的名字
select e.last_name,m.last_name
from s_emp e,s_emp m //同一张表起两个别名
where e.manager_id=m.id;
例2: 打印出所有员工的姓名和他的经理的名字
select e.last_name,m.last_name
from s_emp e,s_emp m
where e.manager_id=m.id(+); //左外连接,m.id(+)代表 经理如果没有就补一个空值。
4.非等值连接：左边是一个值，而右边是一个范围。
(使用它的情况：t1和t2没有任何pk和fk关联时,却还想连接t1,t2时使用非等值连接)
例：table1: id value table2: id value
1 A 1 A
2 B 2 B
3 C 3 C
4 D 4 D
1）请写出一句sql语句,打印输出： AB AC AD BC BD CD
select t1.value,t2.value
from table1 t1,table2 t2
where t1.id<t2.id; //左边是一个值，而右边是一个范围。
2）请写出一段sql语句,打印输出： AB AC AD BA BC BD CA CB CD DA DB DC
select t1.value,t2.value
from table1 t1,table2 t2
where t1.id!=t2.id;
5.笛卡儿连接(不给任何连接条件)--------->因两张表没有条件的连接没有意义，所以应极力避免使用！！！
3）请写出一段sql语句,打印输出： AA AB AC AD BA BB BC BD CA CB CC CD DA DB DC DD
select t1.value,t2.value
from table1 t1,table2 t2;

lesson 6 Subqueries子查询 （视频课件36.2）
SubQuery子查询:
1.请打印出和Smith在同一部门的员工的姓名,start_date,dept_id。
1) select dept_id from s_emp where last_name='Smith'; -----> result1
select last_name,dept_id,start_date from s_emp where dept_id=result1;
2) select last_name,dept_id,start_date from s_emp
where dept_id=( select dept_id from s_emp where last_name='Smith');
2.主查询和子查询之间靠值联系。并且要求：
1) 值的类型要相同
select last_name,dept_id,start_date from s_emp
where dept_id=( select last_name from s_emp where last_name='Smith');---错，值last_name与dept_id类型不同。
select last_name,dept_id,start_date from s_emp
where dept_id=(select salary from s_emp where last_name='Smith'); ---正确，值salary和dept_id虽意思上不合适，但二者类型一致，语法上是正确的。
select last_name,dept_id,start_date from s_emp
where dept_id=( select id from s_region where id=1); ---正确，这里只要类型一致就行。

2)值的个数要一致
select last_name,dept_id,start_date from s_emp
where dept_id=( select dept_id,last_name from s_emp where last_name='Smith'); --->错误，个数不一致。
select last_name,dept_id,start_date from s_emp
where dept_id=(select dept_id from s_emp where last_name='Smith' or id=3); ---->error 语法检测时出现错误
-------->上一个错误可改进如下：
select last_name,dept_id,start_date from s_emp
where dept_id in ( select dept_id from s_emp where last_name='Smith' or id=3);
----->注：不管id=3时对应的dept_id是否与Smith所在的 dept_id相同，都要有in。
注：Oracle数据库运行SQL命令的机制： sql命令——>dbsv（数据库服务器）
1)编译：a.权限检测 b.语法检测 c.把sql命令翻译成内部指令
2)运行内部指令
3.哪些地方可以用子查询：任何需要值的地方就可以用子查询！！！
1) select 不可以（因为select里面只是若干列名(字符串型)，不涉及到需要值的地方；除非有单行函数）
from 可以（视图的应用，详见下面示例）
where 可以
group by 不可以（因为group by 后是若干列名，不涉及到值）
having 可以
order by 可以(前提：按select里列的下标值排序)
例：from后可以用子查询的重要应用：
select * from (select e.last_name,e.dept_id,d.id,d.name from s_emp e,s_dept d where e.dept_id=d.id) e;
注：这里from后的子查询语句，相当于一个虚拟的表。如果给该虚拟的表起个名字，就叫视图。
jobs作业:
1.SQL-1.pdf (lesson 4的习题)
1). Write a report containing each employee’s last name, department number, and name of their department.
SQL> select e.last_name,e.dept_id,d.name from s_emp e join s_dept d on e.dept_id=d.id;
2.请打印出公司入职最早的五个员工的姓名,dept_id,start_date。 ————>面试笔试题（重要！！！）
select * from ( select last_name,dept_id,start_date
from s_emp order by start_date ) e //先对整个表按照start_date排序。
where rownum between 1 and 5; //从排好顺序的虚拟表中拿出最前面的五个数据，此rownum是外层主查询的。
以上命令的执行过程及结果：
1）SQL> select id,last_name,rownum,start_date
2 from s_emp
3 order by start_date;
ID LAST_NAME ROWNUM START_DATE
---------- ---------- ---------- --------------
1 Velasquez 1 03-3月 -90
5 Ropeburn 5 04-3月 -90
17 Smith 17 08-3月 -90
2 Ngao 2 08-3月 -90
4 Quick-To-S 4 07-4月 -90
11 Magee 11 14-5月 -90
2）SQL> select id,last_name,rownum,start_date
2 from (select id,last_name,start_date from s_emp
3 order by start_date ) e
4 where rownum between 1 and 5;
ID LAST_NAME ROWNUM START_DATE
---------- ---------- ---------- --------------
1 Velasquez 1 03-3月 -90
5 Ropeburn 2 04-3月 -90
17 Smith 3 08-3月 -90
2 Ngao 4 08-3月 -90
4 Quick-To-S 5 07-4月 -90
伪列:
Rownum (该列在数据库中实际不存在：体现数据从数据库中拿出来的同时现编的号，边拿边打号，
并且从1开始按先后顺序编号)
Rowid (该列在数据库中真实存在的：保存的是每条记录在数据库中的物理地址，
而且表明该记录被记入表中的顺序) ------将来在学索引（index）时会涉及到它！！！
1）若想查询该两个伪列，则必须明确指出，才能查寻得到。
用select * from s_dept;或 desc s_dept;无法查询出来。如：
SQL> select id,last_name,rownum,rowid from s_emp;
ID LAST_NAME ROWNUM ROWID
---------- ---------- ---------- -----------------
1 Velasquez 1 AAADVwAAEAAAAD/AAA
2 Ngao 2 AAADVwAAEAAAAD/AAB
3 Nagayama 3 AAADVwAAEAAAAD/AAC
4 Quick-To-S 4 AAADVwAAEAAAAD/AAD
5 Ropeburn 5 AAADVwAAEAAAAD/AAE
2）对数据库表中的数据进行分页显示:需使用子查询和rownum
思考题：打印表里面第五到第十条记录。
Select *
From
(select
注：rownum使用时，只能从第一个开始。这里不能直接用where rownum between 5 and 10;会一条都查不到。
因为rownum是数据从库中拿出来时，边拿边打的编号，第一个rownum一定是1，而这里上来就从5开始查，所以什么都查不到，并且不是真实存在的。所以只能从第一个数据开始“边拿边打号”。而where dept_id between 41 and 45;是正确的，因为它是在库中是真实存在的，怎么取值都可以！！！
Oracle Day3
lesson 5 Group function（视频课件37.1）
组函数:应用于每一个组，并且产生一个结果。
1.select avg(salary),max(salary),min(salary),sum(salary) from s_emp;
select count(*) from s_emp; ----->查看员工的人数（统计非空行数）
select count(id) from s_emp; ----->每行的id列不可能为null
select count(commission_pct) from s_emp;-->统计commission_pct列不为空的值的个数，即有提成的人的个数。
例：统计公司中经理的人数。
SQL>select count(distinct manager_id) from s_emp; ----->注：distinct关键字表唯一、不重复。
组函数应用的类型：
number型: max min sum avg count