oracle复习一

--1、表空间和权限
/**
create tablespace xx;

sys : 超级管理员 change_on_install
system : 普通管理员 manager
scott : 标准用户 tiger
connect resource dba
*/
--创建表空间
create tablespace yc_ts
datafile ''
size 100M;


--临时表空间
create temporary tablespace yc_tts
tempfile ''
size 50M;


--创建用户
create user yc identified by yc
default tablespace yc_ts
temporary tablespace yc_tts;


--授权
grant resource, connect to yc;


--撤销权限
revoke resource, connect from yc;


--锁账号
alter user yc account lock;


--解账号
alter user yc account unlock;




--2、表的创建和约束
/**
create table table_name(
       column_name column_type,
       ...
)


emp, dept, bonus, salgrade
*/


--创建一个与emp结构相同的表，不要数据
conn scott/a;
grant select scott.emp on yc;
conn yc/yc;
create table emp1
as
select * from scott.emp where 1 = 0;


--查看表结构
desc table_name;


--删除表结构
drop table table_name;


--修改表结构(添加字段)
alter table table_name
add email varchar2(20);


--修改表结构(修改字段)
alter table table_name
modify email varchar(10);


--修改表结构(删除字段)
alter table table_name
drop column email;


--约束
/**
     1、主键约束: pramry key
     2、默认值约束: default
     3、检查约束: check
*/
alter table table_name
add constraint constraint_name constraint_type;
/**
     4、唯一约束: unique
     5、非空约束: not null
*/
alter table table_name
modify column_name constraint_type;
/**
     6、外键约束: foreign key
     子表：含有外键的表
     主表：外键与主键对应的表
     
     约束建立在子表中：
*/
alter table sub_table_name
add constraint constraint_name foreign key(foreignKey_name) 
references main_table_name(primarykey_name);


--3、数据操作
/**
     insert: insert into table_name(column_name,...) values(column_value,...);
--向emp1表中插入emp表中deptno=20部门的员工信息
*/
insert into emp1 select * from scott.emp where deptno=20;
/**
     delete: delete from table_name where ...
     update: update tabel_name set column_name=column_value, ... where ...;
*/
create table stuInfo(
    id number,
    name varchar(20),
    sex varchar(4),
    age number
);


insert into stuInfo
select 1,'xw','男',18 from dual union
select 2,'mc','女',16 from dual union
select 3,'ww','男',21 from dual union
select 4,'xw','男',18 from dual;


--删除除了学号（自动编号）字段以外，其它字段都相同的冗余记录！
delete from stuInfo where id not in(
       select min(id) from stuInfo group by name, sex, age);


--4、基础操作
/**
     select: select column_name, ... from table_name 
     where ... group by ... having ... order by ...;
     
     查询所有列用：*;
     
     where是筛选分组前的数据
     having是筛选分组后的数据
     查询语句的执行先后顺序：
     -- from --> where --> group by --> having --> select --> order by
     筛选数值要比字符速度快
*/
--面试题：比较效率(第一条比第二条快)
select * from emp where deptno = 10 and ename like '%A%';
select * from emp where ename like '%A%' and deptno = 10;


--5、高级查询
conn scott/a;
grant select scott.dept on yc;
conn yc/yc;
/**
       连接查询：
            内连接查询：（ [inner] join ） emp, dept
                select ename, loc from emp e join dept d on e.deptno = d.deptno;
            外连接查询： ( [outer] join )  驱动表 和 匹配表
                左外连接：left join 左边的表为驱动表，右边的表为匹配表
                    select * from emp e left join dept d on e.deptno = d.deptno;
                右外连接：right join 右边的表为驱动表，左边的表为匹配表
                    select * from emp e right join dept d on e.deptno = d.deptno;
            全连接查询：( full join )
                先左外连接 再右外连接，然后 union(并集)结果
                select * from emp e full join dept d on e.deptno = d.deptno;
            交叉查询：( cross join )笛卡尔集
                select * from emp, dept;
            自连接查询：(自己操作自己)
                --查询每个员工的名字和经理的名字
                select e1.ename, e2.ename from emp e1 join emp e2 on e1.mgr = e2.empno;
*/                
--查询没有员工的部门名字
select dname from dept d left join emp e on d.deptno = e.deptno where e.deptno is null;


select dname from dept d left join emp e on d.deptno = e.deptno where e.deptno is null;--(错)
/** where 是连接后筛选，and 是边筛选边筛选 */


--不准用组合函数，求薪水的最高值
select max(sal) from emp;


select distinct sal from emp where sal not in(
       select distinct e1.sal from emp e1 join emp e2 on e1.sal < e2.sal);