首页 > Linux操作系统 > Linux操作系统 > 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 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;
三、数据库中分五类语言….
分别是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
-- 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/,如需转载,请注明出处,否则将追究法律责任。