连表查询、视图和索引、表设计、创建表、表的其他操作

一、连表查询

1.92表连接

当我们获取的数据不是来自于同一张表而是来自于多张表时就需要使用到表连接。

1）笛卡尔积

连接两个表：

--非*必须区分使用表名或别名. 区分
select * from emp , dept;
select ename , dname from emp , dept;
select ename, dname, e.deptno from emp e, dept d

2）等值连接(在笛卡尔积基础上取条件列相同的值)

--找出30部门的员工名称及部门名称:先关联后过滤
select *
  from emp e, dept d
 where e.deptno = d.deptno
   and e.deptno = 30;

--记录很多时:先过滤后关联
--1.先找出员工表里部门编号为30的员工
select * from emp where deptno = 30;
--2.找出部门表里部门编号为30的部门名称
select * from dept where deptno = 30;

select *
  from (select * from emp where deptno = 30) e,
       (select * from dept where deptno = 30) p
 where e.deptno = p.deptno;

3）非等值连接> < != <>between and

--查询员工姓名，工资及等级
--900 属于哪个等级
select grade from salgrade where 900 >losal and 900<hisal;
select grade from salgrade where 900 between losal and hisal;

--查询员工姓名，工资及等级
--数据源：emp e, salgrade s
--字段：ename, grade, sal
--sal between losal and hisal
select ename, sal, grade
  from emp e, salgrade s
 where sal between losal and hisal;

4）自连接: 特殊的等值连接(来自于同一张表)

--找出存在上级的员工姓名及上级名称
--数据来源：emp e, emp m
--字段：e.ename, m.ename
--条件：e.mgr=m.empno
select e.ename, m.ename from emp e, emp m where e.mgr = m.empno;

5）外连接

看+号，有+号的是从表, 主表在左边就叫左外连接，主表在右边叫右外连接

-- 找出所有员工的名称以及其对应的上级名称（不管员工是否有上级，员工必须要出现）
select * from emp;
select e.ename "员工名称", m.ename "上级名称"
  from emp e, emp m
 where e.mgr = m.empno(+);--e是主表

2.99连接

• 交叉连接cross join —>笛卡尔积
• 自然连接(主外键、同名列) natural join -->等值连接
• join using连接(同名列) -->等值连接
• [inner]join on连接–>等值连接非等值自连接(解决一切) 关系列必须区分
• left|right [outer] join on|using -->外连接
• full join on|using -->全连接满足直接匹配，不满足相互补充null ，确保所有表的记录都至少出现一次

1）交叉连接

select * from emp cross join dept;

2）自然连接

select * from emp natural join dept;

3）using 连接

select deptno,e.ename,d.dname from emp e join dept d using(deptno);

4）on 连接

--natrual 等值
--找到部门编号为30的员工姓名和部门名称
select ename, dname from emp natural join dept where deptno = 30;

--using
select ename, dname from emp join dept (using) deptno where deptno =30;

--on
select ename, dname
  from emp
  join dept
    on emp.deptno = dept.deptno
 where emp.deptno = 30;

on 非等值连接、自连接
--部门编号为30的员工名称工资等级
select ename, grade
  from emp e
  join salgrade s
    on e.sal between s.losal and s.hisal
   and e.deptno = 30;

--部门编号为30的员工名称上级名称
select e.ename, m.ename,e.deptno
  from emp e
  join emp m
    on e.mgr = m.empno
   and e.deptno = 30;

--部门编号为30的员工员工名称、部门名称、工资等级、上级名称
select e.ename, s.grade, m.ename, d.dname
  from emp e, salgrade s, emp m, dept d

 where e.sal between losal and hisal
   and e.mgr = m.empno
   and e.deptno = d.deptno
   and e.deptno = 30;

5）外连接

--所有部门的部门名称，员工数

select deptno, count(1) n from emp group by deptno;
--左外
select dname, n
  from dept d
  left outer join (select deptno, count(1) n from emp group by deptno) i
    on d.deptno = i.deptno;

select dname, n
  from dept d
  left outer join (select deptno, count(1) n from emp group by deptno) i
 using (deptno);

--右外
select dname, n
  from (select deptno, count(1) n from emp group by deptno) i
 right outer join dept d
    on d.deptno = i.deptno;

6）全连接

二、试图和索引

1.视图

视图：建立在表|结果集|视图上的虚拟表，有以下作用：

• 简化：select 查询语句
• 重用：封装select语句命名
• 隐藏：内部细节
• 区分：相同数据不同查询

不是所有的用户都有创建视图的权限：

如何授权：
1）用sys权限登录

2）授权：rant dba to scott；

3）回收：revoke dba from scott；

4）重新登录sqlpuls

2.视图的创建

create or replace view 视图名as select语句[with read only];
要求：所有列必须存在名称。

对视图的删除不会删除原有表的数据

删除视图：drop view 视图名；

--相当于又建立了一个表，不影响原表的数据
--1)、部门经理人-->mgr
create or replace view vw_emp_mgr as select distinct mgr from emp where mgr is not null;
--2)、部门经理人的薪水
create or replace view vw_emp_mgr_sal as select *from emp where empno in (select mgr from vw_emp_mgr);

3.索引（了解）

索引：提高查询速度的一种手段–>目录

• 唯一性较好字段适合建立索引
• 大数据量才有效果
• 主键|唯一：唯一索引

1）创建索引

create index 索引名on表名(字段列表…)
删除索引：drop index 索引名

create index idx_emp on emp(sal,ename);

drop index idx_emp;

三、表设计

1.设计表

设计表首先应该按需遵循三范式：

• 确定表名
• 确定字段名类型+约束(主键外键非空默检查认唯一)

主键：唯一标识一条记录（唯一并且非空）
唯一：唯一
非空：不能为空
默认：当没给值时使用给定一个默认值
外键：参考其他表（自己）的某个（某些）字段
检查：自定义的规则

2.创建表（不加约束）

1）创建新表

create table 表名(
字段名类型(长度) 约束,
...其他字段...
...约束...
);

--表名tb_user
create table tb_user(
userid number(5),--userid 流水号
username varchar2(30),--username 用户名
userpwd varchar2(20),--userpwd 密码
age number(3) ,--age 年龄
gender char(2) ,--gender 性别
email varchar2(30),--email 邮箱
regtime date--regtime 注册日期
);

2）已有表中拷贝结构

create table 表名 as select 字段列表 from 已有表 where1!=1;
--拷贝结构 emp
create table emp_his as select ename,sal from emp where 1!=1;
--拷贝结构 emp +数据
create table emp_his2 as select ename,sal from emp where sal>2000;

3）删除表

cascade constraints：是在父子表中存在外键关联的时候 drop 父表的时候级联drop 子表的外键。

drop table 表名 cascade constraints;
--删除表
drop table tb_user;

drop table emp_his cascade constraints;

四、创建表

1.创建表（同时创建约束+默认名称）

这种在创建表的同时创建约束并使用默认约束名称的方式，后期不方便排错，所以不推荐使用。其主要的优点是简单。

表名tb_user
主键userid
字段名      中文       类型         为空   默认值   其他说明
userid     流水号   number(5)       否             主键
username   用户名   varchar2(30)    否            长度在4-20
userpwd    密码    varchar2(20)     否            长度在4-18
age        年龄    number(3)              18      大于>=18
gender     性别    char(2)                男      男 or 女
email      邮箱    varchar2(30)                   唯一
regtime   注册日期   date               sysdate

create table tb_user(
userid number(5) primary key,
username varchar2(30) check(length(username) between 4 and 20) not null ,
userpwd varchar2(20) not null check(length(userpwd) between 4 and 18),
age number(3) default(18) check(age>=18),
gender char(2) default('男') check(gender in('男','女')),
email varchar2(30) unique,
regtime date default(sysdate)
);

--加入注释
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 '年龄';
comment on column tb_user.gender is '性别';
comment on column tb_user.email is '邮箱';
comment on column tb_user.regtime is '注册日期';

2.创建表（同时创建约束+指定名称）

创建表的同时创建约束并指定约束的名称，后期方便排错，推荐使用。

表名 tb_txt
主键 txtid
字段名   中文      类型         为空     默认值    其他说明
txtid  流水号   number(10)      否                 主键
title   标题   varchar2(32)     否               长度在4-30
txt     正文   varchar2(1024)
pubtime 发布时间 date                 sysdate
userid  发布人  number(5)      外键，参考tb_user的userid列

create table tb_txt(
txtid number(10),
title varchar2(32),
txt varchar2(1024),
pubtime date,
userid number(5)
);

--追加约束
alter table tb_txt add constraint pk_txt_id primary key(txtid);
alter table tb_txt add constraint ck_txt_id check(length(title)>=4 and length(title)<=30);
--注意非空默认
alter table tb_txt modify (title constraint nn_txt_title not null) ;
alter table tb_txt modify (pubtime default(sysdate));

--注释
comment on table tb_txt is '文章表';
comment on column tb_txt.txtid is '流水号,主键';
comment on column tb_txt.title is '标题';
comment on column tb_txt.txt is '正文';
comment on column tb_txt.pubtime is '发布时间';
comment on column tb_txt.userid is '发布人,外键，参考tb_user的userid列';
--删除（先删除从表再删除主表；同时删除约束）
drop table tb_txt cascade constraints;
drop table tb_user cascade constraints;

3.约束（了解）

在oracle 中所有的一切都是对象，约束也是一个个的对象，除了能创建约束我们还能对约束进行一些其他的操作。

1）查看某个用户的约束

select constraint_name, constraint_type
from user_constraints
where owner = upper('scott');

2）查看表的约束

select constraint_name,constraint_type
from user_constraints
where table_name=upper('tb_user');

3）查看字段名+约束

select constraint_name, column_name
from user_cons_columns
where table_name = upper('tb_user');

4）约束的禁用与启用

ALTER TABLE tb_user disable constraint nn_user_name;
ALTER TABLE tb_user enable constraint nn_user_name;

5）删除约束

alter table tb_user drop constraint uq_user_email cascade;

6）修改约束

--非空
alter table tb_user modify (username varchar2(20));
--默认
alter table tb_user modify (age default null);

五、表的其他操作

1.修改表结构

1、修改表名:  rename 表名 to 表名
2、修改列名: alter table 表名 rename column 列名 to 列名
3、修改类型: alter table 表名 modify(字段 类型)
4、修改约束: 先删除后添加
5、添加列: alter table 表名 add 字段 类型
6、删除列: alter table 表名 drop column 字段

--修改表名
rename tb_txt to tb_txt_new;
--修改列名
alter table tb_txt_new rename column txtid to tid;
--修改类型
alter table tb_txt_new modify(tid varchar2(20));
--添加列
alter table tb_txt_new add col varchar2(30);
--删除列
alter table tb_txt_new drop column col;

2.删除表

先删除从表再删除主表。

删除表的同时删除约束。

drop tabletb_user cascade constraints;

3.截断数据

truncate：截断所有的数据，如果截断的是主表，结构不能存在外键关联
截断数据同时 从结构上检查

create table emp_his as select * from emp where 1=1;
select * from emp_his;
--截断所有的数据
truncate table emp_his;
--不能截断: 
truncate table dept;

4.序列

使用工具|程序管理流水号，序列在创建时没有与表关联,在操作数据时与表关联。

1）创建

create sequence 序列名 start with 起始值 increment by 步进;

步进：就是说比如从一百开始100，101，102…或者100，102，104…
就是数字增长之间的间隔。

2）使用

在操作数据 添加 更新-->主键
1)、currval :当前值
2)、nextval:下个值
create sequence seq_tb_user start with 2 increment by 2;
drop sequence seq_tb_user;
--创建一个序列
create sequence seq_dept start with 50 increment by 10;
--使用
insert into dept (deptno) values (seq_dept.nextval);

3）删除

drop sequence 序列名