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(45923,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 cs_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.idt1.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