ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle学习笔记(3)

Oracle学习笔记(3)

原创 Linux操作系统 作者:Deadmonth 时间:2011-07-14 21:41:55 0 删除 编辑

 

 

一、多表连接

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 e  natural 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)

join  jobs 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 e  left 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;

 

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

分别是SQLDML  ,TCLDDL  ,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

--    update  set 

--       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>conn  jack/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_id  id ,department_name  name  from hr.departments;

 

 

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

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

 

 

修改表名

rename employees to  emp;

修改字段名

alter table departments rename column name  to  dname;

修改表结构

alter table test  add address varchar2(200);

alter table test  modify address varchar2(30);

alter table test  drop column address;

 

注意:delete truncate 的区别!………………………………….

Delet table test;

Truncat table test;

四、约束 primary key, foreign key, unique key, check, not null;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25436591/viewspace-702230/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-04-02

  • 博文量
    18
  • 访问量
    44930