Oracle话题三：Oracle要点

1.SQL常用的命令分类
2.查询：连接查询、子查询
3.oracle常用对象
4.常用系统表
*************************
1.SQL常用的命令分类
 (1)．数据定义语言DDL：create、alter、drop。
  create table t_class(
   classno number(2) primary key,
   classname varchar2(20)
  );

  create table t_student(
   no number(3) primary key,--列级约束
   name varchar2(20) not null unique,
   age  number(2) check(age<30 and age>15),
   classno number(3)  references class(classno) ON DELETE set null
   );

  create table t_student(
   no number(3) ,
   name varchar2(20) not null,
   age  number(2),
   classno number(3),
   --表级约束
   constraint s_c check(age<30 and age>15),
   constraint s_u unique(name),
   constraint s_pk primary key(no),
   constraint s_c_fk foreign key(classno) references class(classno) ON DELETE cascade
   );

  小结：
   约束的种类：主键约束、外键约束、唯一性约束、非空约束、check约束

  drop table student;

  alter table
   增加列: alter table book add author char(10); 当表有数据时，不能增加非空列 not null
   修改列: alter table book modify bname varchar2(40);
     当表有数据时，只能加长列，不能缩短；
     没有数据时，可以任意加长或缩短。
   删除列: alter table book drop column author;
   添加约束: alter table book add constraint pk_book1 primary key(sno);
         alter table book add constraint  uk_book1  unique(bname);
         alter table student add constraint fk_class_student foreign key(classno)
      references class(classno);
         alter table book modify bookcata not null
         alter table book add constraint  ck_book1  check(price between 30 and 50);
   删除约束: alter table book drop  primary key;
         alter table book drop constraint ck_book1;
   禁止和使用约束:  alter table book disable constraint ck_book1;
          alter table book enable constraint ck_book1;

 (2)．数据操纵语言DML：insert、select、delete、update。
  insert into student(no,name,age) values(1,'narci',30);
  delete from student where no=1;
  udpate student set age=30 where no=1;
 (3)．事务控制语言TCL：commit、savepoint、rollback。
 (4)．数据控制语言DCL：grant、revoke。

2.查询：连接查询、子查询(相关子查询、非相关子查询)
1)简单查询
 select 字段
 from  表名
 where 条件
 group by 分组
 having 分组条件
 order by 排序条件
2)连接查询
 (1)内连接：可用于两张表或多张表的连接
  等值连接
   例子：显示每个员工的部门名称
     select e.name,d.name,d.deptno from s_dept d,s_emp e
      where d.id=e.dept_id;
    显示Ben所在部门名称
    select ename,dname,d.deptno from dept d,emp e
     where d.deptno=e.deptno AND ename='SCOTT';
  不等值连接
   例子：每个员工的工资级别：
    Select e.ename,e.sal,s.grade
    from emp e,salgrade s
    where e.sal between s.losal and s.hisal;

 (2)外连接：只能用于两张表的连接
  左外连接
   例子：显示哪个部门没有员工
    select d.*,e.ename,e.empno,e.deptno
    from dept d,emp e
    where d.deptno=e.deptno(+) and e.deptno is null;

    查询每个员工的领导
    Select a.last_name employee, b.last_name manager
    From s_emp a,s_emp b
    Where a.manager_id= b. id(+);

    Select a.last_name employee, b.last_name manager
    From s_emp a
    left outer join s_emp b
    on a.manager_id= b.id;

  右外连接
   例子：查询每个员工的领导
    select a.last_name employee, b.last_name manager
    from s_emp a,s_emp b
    where a.manager_id(+)= b.id;

    Select a.last_name employee, b.last_name manager
    From s_emp a
    right outer join s_emp b
    on a.manager_id= b.id; 
    
 (3)自连接：与本表相连
   例子：查询工资高于3雇员工资的雇员信息
    select e1.*,e2.id
    from s_emp e1,s_emp e2
    where e1.salary>e2.salary and e2.id=3;

3)子查询
 (1)非相关子查询
  例子：select dept_id,avg(salary)
   from s_emp1
   group by dept_id having avg(salary)> (
     select sum(salary)
     from s_emp1
     where dept_id=10);

 (2)相关子查询: 内部查询（子）引用外部（主）查询的列
  例子：查询工资高于他在部门平均工资的雇员信息
         select *
         from s_emp1 x
         where salary>(select avg(salary)
     from s_emp1 y
       where x.dept_id=y.dept_id)
 
 (3)出现的地方： where ,having , from , create , insert,update
  from :所在部门平均工资高于 1500 的员工名字
   select a.dept_id,a.first_name,a.salary
   from s_emp a,(
        select dept_id
        from s_emp
        group by dept_id
        having avg(salary)>1500
       )b
   where a.dept_id=b.dept_id;
  where:所在部门平均工资高于 1500 的员工名字
    select dept_id,first_name,salary
   from s_emp
    where dept_id in (
    select a.dept_id
    from s_emp a group
    by a.dept_id having avg(a.salary)>1500)
  having: 查询各个部门的平均工资高于部门号是41部门平均工资的部门号和平均工资
  *****
   【group by子句就是这样的 什么select后面的字段中如果没有使用组函数，就必须放在group by子句里面】
   select dept_id,avg(salary)
   from s_emp1
   group by dept_id
   having avg(salary)>(
    select avg(salary)
    from s_emp1
    where dept_id=41)

3.oracle其它对象
1)序列 ： 生成唯一数字序号
  sequence 的参数：
  increment by n   起始值
  start with n     递增量
  maxvalue n       最大值 
  minvalue n       最小值
  cycle|no cycle     循环 
  cache n          缓存(第一次取时会一次取多少个id存起来)
 (1) 创建序列
  create sequence seq_dept
  INCREMENT BY 1
  START WITH 51
  MAXVALUE 70
    CACHE 10
    CYCLE

 (2) 使用序列 ： 伪列nextval,currval
  序列名.currval:返回序列初始值 51
  序列名.nextval:第一次使用时与初始值相同，下次增量
 (3)删除序列：drop sequence seq_dept;

2)索引:提高查询速度
 (1)索引的类型：唯一索引，非一索引索引，组合索引

 (2)创建索引： 
  非唯一索引 create index  idx_s_emp on s_emp(id)
  唯一索引 create unique index  idx_s_emp1 on s_emp(id)
  组合索引 create  index  idx_s_emp1 on s_emp(id,title) 
 (3)查看索引：
  user_indexes
  user_ind_columns
 (4)删除索引：
  drop index index_name
 (5)创建规则：
  a.在查询中经常出现的列
  b.出现在where子句中的列
  c.在order by子句中的列
  d.在多表联接中出现在字段
  e.主键上自动创建索引

  注：表越大，查询的记录越少，索引的效率就越高.

3)视图
 (1)简单视图: 视图的列取自一个表，可以增，删，改
   1）只读视图,加with read only
    create or replace view v_emp as
    select id,first_name,title from s_emp with read only;
    2) with check option,类似于check约束，对视图的操作要满足where语句的要求
     create  or replace view v_emp1 as
     select  id,first_name,title,dept_id from s_emp where dept_id in (41,45)
     with check option ;
     update v_emp1 set dept_id=41 where id=2;-不允许
 (2)复杂视图: 带有多表连结和聚合函数及distinct, 不能修改
   create or replace view v_emp2 as
   select dept_id,sum(salary) sumsal from s_emp group by dept_id;
 (3)删除视图：drop view v_emp_dept;

4.常用系统表
 user_tables
 user_constraints
 user_sequences
 user_views