表连接,视图和索引，表单设计

92语法

笛卡尔积

例:
select * from emp,dept order by sal;

等值连接

可以是两个表中的相同字段做连接,可以是不同字段做连接,但是类型要保持一致
例:

select * from emp,dept where emp.deptno=dept.deptno;
select * from emp,dept where emp.ename=dept.dname;

非等值连接

例:

select *　from emp,salgrade where sal between losal and hisal;

先连接后过滤

select empno,ename,e.deptno,dname from emp e,dept d where e.deptno=d.deptno and e.deptno=30;

先过滤后连接,效率较高

例:

select empno, ename, e.deptno, dname
  from (select * from emp where deptno = 30) e,
       (select * from dept where deptno = 30) d;

自连接

例:

select * from emp e1,emp e2 where e1.mgr=e2.empno;

外链接

做为主表的表中的数据全部显示
在连接条件的位置,在主表对面的表的连接条件后添加(+)

例:
emp e1,emp e2 主表的位置确定,主表在左边叫做左连接,主表在右边,叫做右连接

select * from emp e1,emp e2 where e1.mgr=e2.empno(+); --左连接
select * from emp e2,emp e1 where e1.mgr=e2.empno(+); --右连接

99语法

自然连接

natural join 自动匹配两张表的主外键关系的字段,或同名字段,做等值连接。
注意:自然连接同名字段不能出现限定词
例：

select dname,deptno,empno,ename from dept inner natural join emp

等值连接

join…using(等值连接的字段) 指定使用哪一个字段做等值连接

select dname,deptno,empno,ename from dept inner join emp using(deptno);

连接条件–>等值|非等值

表1 join 表2 on 连接条件
例:

select * from emp join salgrade on sal between losal and hisal;

select emp.deptno,ename from emp inner join dept on emp.deptno=dept.deptno;

全连接

查询语句1 union 查询语句2

select 1 no, 'a' "name" from dual
union
select 2 no, 'b' "name" from dual;

内连接

满足连接条件的显示
dual表示虚表, 1和’a’代表段落,no和"name"代表段落名.
例:

select *
  from (select 1 no, 'a' "name"
          from dual
        union
        select 2 no, 'b' "name"
          from dual) s1
   join (select 1 no, 'c' "name"
          from dual
        union
        select 3 no, 'd' "name"
          from dual) s2
    on s1.no = s2.no;

左连接

例:

select *
 from (select 1 no, 'a' "name"
         from dual
       union
       select 2 no, 'b' "name"
         from dual) s1
  left join (select 1 no, 'c' "name"
         from dual
       union
       select 3 no, 'd' "name"
         from dual) s2
   on s1.no = s2.no;

右连接

例:

select *
  from (select 1 no, 'a' "name"
          from dual
        union
        select 2 no, 'b' "name"
          from dual) s1
   right join (select 1 no, 'c' "name"
          from dual
        union
        select 3 no, 'd' "name"
          from dual) s2
    on s1.no = s2.no;    

全连接 两张表都作为主表
例

select *
  from (select 1 no, 'a' "name"
          from dual
        union
        select 2 no, 'b' "name"
          from dual) s1
   full join (select 1 no, 'c' "name"
          from dual
        union
        select 3 no, 'd' "name"
          from dual) s2
    on s1.no = s2.no;      

视图

试图是建立在 表和结果集之间的一个虚拟表,操作试图不会修改原数据库表中的内容的
适当的创建试图可以提高执行效率和简化sql

create or replace view 视图名 as select语句 [with read only];
例:

create or replace view vw_emp as select empno,ename,sal from emp where deptno=30 order by sal desc;

drop view 视图名
例:

drop view vw_emp

索引

数据库的对象之一,对象本身是需要维护的
索引本身是透明的,是否创建对于数据库表的本身,和使用上没有任何影响
对大量数据,常用于查询,会提高执行效率,执行速度
对添加索引的字段大量的做增删改,需要更新维护索引对象,降低效率
相当于字典的目录
oracle会自动为主键添加索引

create index 索引名 on表名 (字段列表…)
例:

create index index_emp_sal on emp(sal);

drop index 索引名
例:

drop index index_emp_sal;

表设计 DDL

create table 表名(字段名 字段类型,字段名 字段类型…)
例:

create table tb_user(
       userid number(5),
       username varchar2(3 char),  --varchar2(3)默认指定(数字->字节数)    varchar2(3 char) 3个字符
       userpwd number(6,2),   --6位数字,精确到2位小数.
       age number(3),
       gender char(3),
       email varchar2(30),
       regtime date

主从表,主外键关系
创建表:先创建主表,再创建从表
插入数据,从表中插入数据的时候,外键字段值必须在主表中已存在
删除表:默认先删除从表,再删除主表
drop table 表名 cascade constraints; 删除主表,级联删除约束

约束

 --非空 唯一 -->主键约束
       sid number(5) constraints pk_sid primary key,
       
       --非空约束
       sname varchar2(20) constraints ck_sname_notnull not null,
       
       --检查约束 check()
       age number(3) ,--check(age>0 and age<=150),
       
       --检查约束  不是男就是女
       gender char(3) check(gender='男' or gender='女'),
       
       --唯一 
       sqq number(15)  unique,
       
       --默认 当前日期
       birthday date default(sysdate),
       
       --外键关联班级表的主键cid
       cid number(5) references sxt_class(cid) on delete cascade    或: on delete set null,
       
       --表结构的最后添加 指定约束的名字,指定添加的字段,指定约束
       constraints ck_age check(age between 0 and 150),
       constraints ck_gender check(gender in ('男','女'))

加入注释

例:

comment on table tb_user is '用户表';
comment on column tb_user.userid is '流水号,主键';
comment on column tb_user.username is '用户名';
comment on column tb_user.userpwd is '密码';
comment on column tb_user.age is '年龄';

删除表

drop table 表名;
例:

drop table tb_user;

插入数据

例:

insert into tb_user values(1,null,123456,25,'女'); --或
insert into student(sid,sname,age,gender,sqq,cid) values(04,'XXX',18,'女');

拷贝结构,不拷贝数据

create table 表名 as select 字段列表 from 已有表 where 1!=1;
例:

create table haha_emp as select * from emp where 1!=1;

添加约束的方式:
1.创建表的同时字段的后面直接+约束(没有名字,简单,但是提示不直观)
2.创建表的同时字段的后面直接+约束(指定约束的名字)
3.创建表结构的最后,非字段的后面,添加约束

追加约束

例:

alter table student add constraints unique_sqq unique(sqq);

删除约束

例:

alter table student drop constraints unique_sqq;

删除数据

删除数据:
1)默认先删除从表中引用了这个主表的这一条数据的那些从表数据,再删除主表的这一条数据
2)删除主表中这条数据的同时给从表中那些引用了这一条数据的从表数据的外键字段设置为null
3)删除主表中这条数据的同时给从表中那些引用了这一条数据的从表数据一起删掉
例:

delete from sxt_class where cid=1;