Oracle概念及常用语句(二)

4.7 多表连接

4.7.1 语法

SELECT table.column, table.column FROM table1, table2 WHERE table1.column1 = table2.column2;

4.7.2 示例

-- 笛卡尔结果:两个表直接连接，没有连接条件 
select * from emp,dept 
-- 但这样连接后会有很多错误数据
-- 所以我们要在笛卡尔结果基础上过滤出正确的记录
-- 找到表与表之间的关联属性，然后用where子句和=创建一个条件
select * from emp,dept where emp.deptno = dept.deptno 
-- 然后就查询需要的列 
-- 查询员工工号、姓名、部门号、工作地 
-- 列名前加上表名可以提高查询效率 select emp.empno,emp.ename,emp.deptno,dept.loc from emp,dept where emp.deptno = dept.deptno

-- 表别名 
-- 格式：表名 别名 
select e.empno,e.ename,e.deptno,d.loc from emp e,dept d where e.deptno = d.deptno
-- 这样可以提高效率

-- 等值连接：连接条件使用等号 
-- 查询KING的工号，部门号，部门名，工作地 
select e.empno,e.deptno,d.dname,d.loc from emp e,dept d where e.deptno = d.deptno and e.ename='KING'

-- 三表连接 
-- 查询学生姓名、课程名、成绩
select s.sname,c.cname,sc.grade from student s,score sc,course c where s.sid = sc.sid and sc.cid = c.cid

-- 不等连接：连接条件使用除等号之外其他比较运算符(>/>=/between and) 
-- 查询员工姓名、工资及工资级别 
select e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal

4.7.3 自连接

⾃连接：将⼀张表看成两张表，然后两张表连接

-- 查询员工姓名及其主管的姓名 
select worker.ename 员工,manager.ename 主管 from emp worker,emp manager where worker.mgr = manager.empno；
-- 查询入职时间比自己的主管早的员工
select w.* from emp w,emp m where w.mgr = m.empno and w.hiredate< m.hiredate

4.7.4 外连接

• 内连接(inner join)：将满⾜连接条件的记录查询出来
• 外连接(outer join)：将不满⾜连接条件的记录也查询出来
• 左外连接(left join…on)：将左边中的记录全部查询出来
• 右外连接(right join…on)：将右表中的记录全部查询 出来

-- 查询员工姓名及其主管的姓名，将没有主管的员工也显示出来
select w.ename 员工,m.ename 主管 from emp w left join emp m on w.mgr = m.empno; 
-- 查询员工工号、姓名、部门号、部门名、工作地，将没有员工的部门也显示出来 
select e.empno,e.ename,d.deptno,d.dname,d.loc from emp e right join dept d on e.deptno =d.deptno 
-- 查询每门功课的考试人数，将没有人考试的功课也显示出来 
select c.cid,c.cname,count(sc.sid) from score sc right join course c on sc.cid = c.cid group by c.cid,cname
-- 查询部门名称及部门人数，将没有员工的部门也显示出来 
select d.dname,count(e.empno) from emp e right join dept d on e.deptno = d.deptno group by d.dname

4.8 子查询

4.8.1 语法

SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table);

4.8.2 单⾏⼦查询

⼦查询结果返回⼀⾏

-- 查询工资最高的员工
select * from emp where sal = (select max(sal) from emp); 
-- 查询工资比JONES高的员工 
select * from emp where sal>(select sal from emp where ename='JONES'); 
-- 使用子查询，查询主管为KING的员工 
select * from emp where mgr=(select empno from emp where ename='KING') 
-- 查询每种职位的最低工资，只显示最低工资高于SALESMAN最低工资的这些职位 
select job,min(sal) from emp group by job having min(sal)> (select min(sal) from emp where job='SALESMAN')

4.8.3 多⾏⼦查询

子查询结果返回多个

-- 将有员工的部门号、部门名、工作地查询出来 
select * from dept where deptno in (select distinct deptno from emp); 
-- 查询工资高于部门20所有人的工资员工 
select * from emp where sal>(select max(sal) from emp where deptno=20)

多行子查询的运算符

• <all 小于最小值
• >all 大于最大值
• <any 小于最大值
• >any 大于最小值

-- 查询工资高于部门20所有人的工资员工
select * from emp where sal>all(select sal from emp where deptno=20);
-- 从emp表中查询工资小于CLERK的最高工资的且职位不是CELRK的员工
select * from emp where sal<(select max(sal) from emp where job='CLERK') and job<>'CLERK'; 
select * from emp where sal<any(select sal from emp where job='CLERK') and job<>'CLERK';

4.8.4 非关联子查询

子查询可以单独执行，子查询的结果给主查询用

--使用子查询，哪些学生学过SHCO01这门课程，查询学生姓名
--非关联子查询：子查询可以单独执行，子查询的结果给主查询用 select sname from student where sid in (select sid from score where cid='SHCO01');

4.8.5 关联子查询

子查询不能单独执行，主查询的数据给子查询用

select sname from student s where 'SHCO01' in (select cid from score sc where sc.sid = s.sid);

5. SQL其它

5.1 rownum

• 伪列，实际表中并不存在这一列，Oracle在查询结果中动态生成rownum

• 查询第一条记录，可以使用rownum=1作为条件。如果想查询第2条记录，使用rownum=2结果查不到数据。因为rownum都是从1开始。 ·

• 查询从第二行记录以后的记录，当使用rownum>2是查不出记录的，原因是由于rownum是一个总是从1开始的伪列，Oracle 认为rownum> n(n>1的自然数)这种条件依旧不成立，所以查不到记录。 ·rownum对于rownum<n（(n>1的自然数）的条件认为是成立的，所以可以找到记录。

--查询前五行
select * from 表名 where rownum<6;
--查询10行后的记录
select * from (select rownum rn,emp.* from emp) t where t.rn>10;

5.2 union/union all

UNION 操作符⽤于合并两个或多个 SELECT 语句的结果集。

select * from 表名 where condition
union --去重
select * from 表名 where condition; 

select * from 表名 where condition 
union all --不去重
select * from 表名 where condition;

5.3 minus

相当于减法

select * from 表名 where condition 
minus
select * from 表名 where condition;

6. DML语句

1. 是在内存中操作，如果要写到表中，需要提交(commit)

2. DDL语句和DCL语句是⾃动提交，DML语句⼿动提交

6.1 insert 增加、插入

6.1.1 语法

insert into 表名[(列名，列名，....)] values（值，值，....）;
insert into 表名 values（值，值，....）;
-- 表后不写列名，表示插入所有值，插入空值需要写null

6.1.2 示例

-- 第一种方式 表名后面跟列名 
insert into dept(deptno,dname,loc) values(50,'TEST50','nanjing') 
-- 如果列名不写，插入空值 
insert into dept(deptno,dname) values(60,'TEST60') 
-- 第二种方式，表名后不写列名，表示要插入所有的值 
insert into dept values(70,'TEST70','shanghai') 
-- 插入空值，需要写NULL 
insert into dept values(80,'TEST80',null)

6.2 update 修改

6.2.1 语法

update 表名 set 列名=值[,列名=值,列名=值,...] [where condition];

6.2.2 示例

-- 将部门50的工作地修改为BEIJING 
update dept set loc='BEIJING' where deptno=50;
-- 将部门50的工作地修改跟部门70一样 
update dept set loc=(select loc from dept where deptno=70) where deptno=50
-- 将部门50的部门名修改为TEST50，工作地修改为BEIJING 
update dept set dname='TEST50',loc='BEIJING' where deptno=50;
-- 将SMITH的工资修改比KING多500
update emp set sal = (select sal+500 from emp where ename='KING') where ename='SMITH';

6.3 delete 删除

6.3.1 语法

delete [from] 表名 [where condition];

6.3.2 示例

-- 将部门50删除 
delete from dept where deptno=50; 
-- 将部门60、70、80删除
delete dept where deptno in (60,70,80);
-- 不加条件表示将表中全部记录删除,不要使用delete * 
delete from dept; 
--删不了因为部门号在员工表中使用 
delete emp;

6.4 savepoint 存档点

insert into dept(deptno,dname) values(60,'TEST60');

--存档 saveponint 
savepoint insert_done 
--insert into dept values(70,'TEST70','SH') 

--回退 
rollback to insert_done
rollback --回退到初始状态

7. DDL语句

• Oracle数据类型
  • VARCHAR2(size) 可变⻓字符串
  • CHAR(size) 定⻓字符串
  • NUMBER(p,s) 数字值
  • DATE ⽇期值
  • INTEGER 整数
  • FLOAT ⼩数

7.1 创建表

• 语法

create table 表名 (列名 数据类型,列名 数据类型,列名 数据类型...)

• 示例

-- 学生表（学号、姓名、性别、出生日期）
create table student ( 
    sid varchar2(10) primary key, 
    sname varchar2(20) not null, 
    ssex char(2) default 'M', 
    birthday date )

7.2 CTAS建表

使⽤⼦查询创建表

-- CTAS（create table as 子查询） 建表 
create table emp1 as select empno,ename,job,deptno from emp; 
-- 复制emp表 create table emp2 as select * from emp; 
-- 复制emp表结构，不要复制数据 
create table emp3 as select * from emp where 1=2;

7.3 删除表drop

drop table emp1;

• 删除表数据及表结构
• 表中所有数据将被删除
• 事务被提交
• 所有索引被删除
• 不能回退

7.4 删除表中全部记录truncate

truncate table emp2;

delete可以选择删除部分记录，truncate只能删除全部记录

delete是DML语句，可以回滚，truncate是DDL语句，不能回滚

truncate删除速度⽐delete快

truncate会回收空间，delete不会

7.5 修改表 Alter

增加列 alter table 表名 add (列名 数据类型，列名 数据类型);
修改列 alter table 表名 modify(列名 数据类型，列名 数据类型) ;
修改列名 alter table 表名 rename column 列名 to (修改名称) ;
删除列 alter table 表名 drop column 列名；

-- 增加列 phone varchar2(11) 
alter table student add (phone varchar2(11))

-- 修改一列 phone 13位 
-- 1.可以修改数据类型，长度，默认值 
-- 2.修改的时候不能影响原有的数据 
alter table student modify (phone varchar2(13))

-- 修改列名
-- phone改成mobile 
alter table student rename column phone to mobile;

-- 删除列 
-- 删除mobile列
alter table student drop column mobile;

8. 约束

• 约束是表级强制执⾏的规则

• 保护表中数据的完整性和⼀致性

• 约束类型

  • NOT NULL ⾮空约束 C
  • UNIQUE Key 唯⼀约束 U
  • PRIMARY KEY 主键约束 P
  • FOREIGN KEY 外键约束 R
  • CHECK 检查约束 ⽤⼾可以⾃⼰定义检查的内容 C

• 约束英⽂ constraint

-- 查询约束 
select * from user_constraints where table_name='STUDENT' 
-- 创建主键约束 sid 表级约束 
-- 非空约束 sname 只能创建列级
-- 唯一约束 phone 表级约束 
create table student (
    sid varchar2(20) , 
    sname varchar2(10) not null,--非空
    ssex char(1), 
    birthday date, 
    phone varchar2(11), 
    constraint student_sid_pk primary key(sid), 
    constraint student_phone_uk unique (phone) ---没有key )
alter table order_status add constraint order_status_id_fk foreign key(id) references employees(employee_id);

9. 视图

1. 视图是⼀个虚表，来源表或者视图

2. 视图存储是SQL语句

3. 使⽤视图可以保护数据，简化复杂SQL

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
	[(alias[, alias]...)] 
AS subquery 
[WITH CHECK OPTION [CONSTRAINT constraint]] 
[WITH READ ONLY]

-- 创建视图 
create view empvu10 as select empno,ename,job from emp where deptno=10; select * from empvu10; 
-- 创建视图使用列别名
create view empvu20 as select empno 工号,ename 姓名,job 职位 from emp where deptno=20;
select * from empvu20; 
-- 创建视图指定列名 
create view empvu30(employee_number,employee_name,job_title) as select empno 工号,ename 姓名,job 职位 from emp where deptno=30;
select * from empvu30; 
-- 修改视图 
create or replace view empvu10 as select empno,ename,job,hiredate,sal from emp where deptno=10; 
select * from empvu10; 
-- 复杂视图 
-- 创建视图dept_sum_vu,显示部门名，部门最低工资、最高工资、平均工资 
create view dept_sum_vu as select d.dname,min(e.sal) minsal,max(e.sal) maxsal,avg(e.sal) avgsal from emp e,dept d where e.deptno =d.deptno group by d.dname select * from dept_sum_vu;
-- 删除视图,不影响基表中的数据 drop view empvu10; 
-- 通过视图修改基表中的数据 delete from empvu20 where 姓名='SMITH';
-- emp表中SMITH也会被删除 
-- 在EMP表上创建一个视图emp_vu20，要求包括RESEARCH部门所有员工的信息
create view emp_vu20 as select * from emp where deptno = (select deptno from dept where dname='RESEARCH');
select * from emp_vu20; 
-- 分别创建两个视图emp_deptview、emp_jobview，按部门、职位分别统计员工的年薪和
create view emp_deptview as select deptno,sum(sal*12) totalsal from emp group by deptno; 
select * from emp_deptview; 

create view emp_jobview as select job,sum(sal*12) totalsal from emp group by job; 
select * from emp_jobview; 
--通过对视图emp_vu20来更新员工的工资为原来的1.1倍 update emp_vu20 set sal=sal*1.1;

10.索引

什么是索引

• 是⼀种对象
• 使⽤指针加快记录访问速度
• 减⼩硬盘 I/O
• 索引独⽴于表⽽存在
• 数据库⾃动使⽤和维护

适合创建索引的条件

• 索引列应该经常在 WHERE ⼦句中，或是连接条件.
• 此列值域⽐较⼴.
• 此列不包含⼤量空值. 下列的表不适合建索引:
  • 表很⼩
  • 列不经常在WHERE⼦句中使⽤
  • 表经常被修改

--查询索引 
select * from user_indexes where table_name='STUDENT';
--查看索引所在的列
select * from user_ind_columns where table_name='STUDENT'; 
--sname列创建索引 
create index student_sname_idx on student(sname); 
--删除索引 
drop index student_sname_idx;

11.用户及权限

--创建用户jack 密码jack 
create user jack identified by jack; 

--修改用户密码 
alter user jack identified by test123;

--授予权限
grant create session to jack; 

--创建角色 manager 
create role manager;

--分配权限给manager 
grant create table,create view to manager; 

--将角色分配给用户 
grant manager to jack; 

--将scott的emp表查询权限分给jack 
grant select on scott.emp to jack; 

--收回权限 
revoke select on scott.emp from jack;