oracle笔记（三）,Oracle学习笔记（3）

一、多表连接

1)等值连接

2)非等值连接

3)外连接(已经不建议使用)

4)自连接

用的是oracle中本身用户hr中表employees:

等值连接

--(1)

select employees.last_name,jobs.job_title

from jobs,employees

where jobs.job_id = employees.job_id

and employees.last_name like 'D%';

--(2)

select employees.last_name,jobs.job_title

from jobs,employees

where employees.last_name like 'D%'

and jobs.job_id = employees.job_id;

区别：(1)比(2)效率高，第二种先要全部匹配后，再进行等值连接

自连接

select e1.last_name||'work for'||e2.last_name

from employees e1,employees e2

where e1.manager_id =

e2.employee_id;

非等值连接

create table job_grades

(

grade_level varchar2(10),

min_salary number(8,2),

max_salary number(8,2)

);

begin

insert into job_grades

values('A',10000,30000);

insert into job_grades values('B',7000,9000);

insert into job_grades

values('C',40000,60000);

insert into job_grades values('D',2000,4000);

commit;

end;

select * from job_grades;

select e.last_name, e.salary,g.grade_level

from employees e, job_grades g

where e.salary between

g.min_salary and g.max_salary;

下面是新标准下的连接方式(oracle 9i以上版本才能用)

1)交叉连接cross join

select d.department_name,l.city

from departments d cross join

locations l;

2) natural

join自然连接

select e.last_name,j.job_title

from employees enatural join jobs j;

注意：若两张表中有两个(或两个以上)相同，都要自然连接。

eg:

Select

e.last_name,d.department_name

from employees e natural join

departments d;

3) join using

select e.last_name,d.department_name

from employees e join departments d

using (department_id);

4) join on (等值连接)

select e.last_name,d.department_name, j.job_title

from employees e

join departments d

on (e.department_id = d.department_id)

joinjobs j

on (e.job_id = j.job_id)

where j.job_id like 'A%';

5)outer join外连接

<1>left outer join

<2>right outer join

<3>full outer join

select e.last_name,d.department_id

from employees eleft outer

join departments d

on (e.department_id = d.department_id);

二、在这里介绍一下rownum

对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，而且rownum不能以任何表的名称作为前缀。

select * from emp where rownum <=5;

注意:在rownum中只能使用

补充一点，可以用=,在下面这种情况

select * from emp  where rownum = 1;

查询工资最高的前六位员工姓名和工资:

select last_name,salary

from (select * from employees order by

salary desc)

where rownum <=6;

三、数据库中分五类语言….

分别是SQL，DML,TCL，DDL,DCL。

已经写过sql；

DML & TCL一般一起配合使用：

DML

(1)Insert(2) update(3) delete(4) merge

TCL

(1)Commit (2)rollback(3) savepoint

------- - -----只为了记录，还没说到约束。

create table test

(

id number(4),

name varchar2(20)

);

insert into test values(1,'Deadmonth');

insert into test(id,name)

values(2,'Jarry');

insert into test(name,id)

values('Tom',3);

commit;

update test set id =

4 where name = 'Jarry';

说明update多个字段是，字段之间用逗号。

update

test1 set address='beijing' ,birthday='10-7月-90' where id = 2;

delete from test;

执行commit;才有效或rollback回滚到删除前。当然，commit执行后，rollback就没用了。

--merge合并(oracle 9i以上版本才有)

--merge into表名别名

--using表名/视图名/子查询别名

--on (条件)

-- when matched then

--updateset

--column1 = value1,column2 = value2,...

-- when not matched then

--insert values(value1,value2,...)

;

DDL & DCL

DDL

(1)Create

(2) alter (3)drop(4) truncate

DCL

(1)Grant

(2) Revoke

--(1)创建表空间

必须是系统级别的system才创建…….

create

tablespace myspace

datafile

'd:\oracle\product\10.2.0\oradata\orcl\myspace.dbf'

size

300m;

查看表空间是否创建成功

select *

from user_tablespaces;

--(2)创建用户

create

user jack

identified

by jack

default

tablespace myspace

quota

100m on myspace

account

unlock;

--查看用户是否创建成功

select *

from dba_users;

--(3)授权给用户

--现在到sqlplus中sql>connjack/jack失败，未授权

grant

connect to jack;

grant

create table to jack with admin option;

grant dba

to jack with admin option;

drop

tablespace myspace;注意：还要到所在文件夹手动删除原文件。

drop user

jack;

create创建表

都知道的就写过了，写点不是都知道

create

table employees as select * from hr.employees;

要求修改表中的字段名称

create

table departments(id,name,) as

select

department_id,department_name, from hr.departments;

推荐使用别名

create

table departments as

select

department_idid ,department_namename from hr.departments;

在上面方法的基础上，只要表结构，不要数据，这为常用方法

create

table emp as select * from scott.emp where 1=2;

修改表名

rename employees toemp;

修改字段名

alter table departments rename column name to dname;

修改表结构

alter table test add address varchar2(200);

alter table testmodify address varchar2(30);

alter table testdrop column address;

注意：delete与truncate的区别！………………………………….

Delet table test;

Truncat table test;

四、约束primary key, foreign

key, unique key, check, not null;