表连接和表设计

一、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;
–在指定列过程中同名列归共同所有(*除外)
select deptno,e.ename,d.dname from emp e natural join dept d;
3、using 连接
select deptno,e.ename,d.dname from emp e join dept d
using(deptno);
4、on 连接
– natrual 等值
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
where deptno=30;
–部门编号为30的员工名称 上级名称
select e.ename,m.ename mname from emp e join emp m
on e.mgr =m.empno where e.deptno =30;
–部门编号为30的员工 员工名称 部门名称 工资等级 上级名称
select e.ename, dname, grade, m.ename mname
from emp e
join dept d
on e.deptno = d.deptno
join salgrade s
on e.sal between s.losal and s.hisal
join emp m
on e.mgr = m.empno
where e.deptno = 30;
5、外连接
–所有部门的 部门名称，员工数
–左外
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、全连接
select *
from (select 1 no, ‘a’ “name”
from dual
union
select 2 no, ‘b’ “name” from dual) a
full join (select 1 no, ‘c’ “name”
from dual
union
select 3 no, ‘d’ “name” from dual) b
on a.no=b.no;
select *
from (select 1 no, ‘a’ “name”
from dual
union
select 2 no, ‘b’ “name” from dual) a
full join (select 1 no, ‘c’ “name”
from dual
union
select 3 no, ‘d’ “name” from dual) b
using(no);
二、视图
视图:建立在表|结果集|视图上的虚拟表，有以下作用
1、简化:select 查询语句
2、重用:封装select语句 命名
3、隐藏:内部细节
4、区分:相同数据不同查询
不是所有的用户都有创建视图的权限
1、前提： create view -->组 connect resource dba
2、授权: -->sqlplus /nolog
a)、sys登录 conn sys/123456@orcl as sysdba
b)、授权: grant dba to scott;
回收: revoke dba from scott;
c)、重新登录
create or replace view 视图名 as select语句 [with read only];
要求:所有列必须存在名称。
对视图的删除不会删除原有表的数据
drop view 视图名
五、索引（了解）
 索引是数据库对象之一，用于加快数据的检索，类似于书籍的索引。在数据库中索引可以减少数据
库程序查询结果时需要读取的数据量，类似于在书籍中我们利用索引可以不用翻阅整本书即可找到
想要的信息。
 索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检
索方式，从而提高检索效率
 索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表；
 索引一旦建立，在表上进行 DML 操作时（例如在执行插入、修改或者删除相关操作时），oracle 会
自动管理索引，索引删除，不会对表产生影响
 索引对用户是透明的，无论表上是否有索引，sql 语句的用法不变
 oracle 创建主键时会自动在该列上创建索引
索引: 提高查询速度的一种手段 -->目录
1、唯一性较好字段适合建立索引
2、大数据量才有效果
3、主键|唯一: 唯一索引
create index 索引名 on表名 (字段列表…)
drop index 索引名
create index idx_emp on emp(sal,ename);
drop index idx_emp;
select * from emp order by sal,ename;

一、设计表
设计表首先应该按需遵循三范式
1、确定表名 2、确定字段名 类型 +约束(主键 外键 非空 默 检查认 唯一)
主键： 唯一标识一条记录（唯一并且非空）
唯一： 唯一
非空：不能为空
默认： 当没给值时使用给定一个默认值
外键：参考其他表（自己）的某个（某些）字段
检查：自定义的规则
用户表
表名 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
备注

文章表
表名 tb_txt
主键 txtid
字段名 中文 类型 为空 默认值 其他说明
txtid 流水号 number(10) 否 主键
title 标题 varchar2(32) 否 长度在4-30
txt 正文 varchar2(1024)
pubtime 发布时间 date sysdate
userid 发布人 number(5) 外键，参考tb_user的userid列
备注
二、创建表
表名必须唯一，如果存在 ，必须删除
1、创建表（不加约束）
1)、创建新表
create table 表名(
字段名 类型(长度) 约束,
…其他字段…
…约束…
);

表名 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

2、创建表（同时创建约束+默认名称）
这种在创建表的同时创建约束并使用默认约束名称的方式，后期不方便排错， 所以不
推荐使用。其主要的优点是简单。
–删除 （先删除从表 再删除主表 ；同时删除约束）
drop table tb_txt cascade constraints;
drop table tb_user cascade constraints;

表名 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

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

–删除 （先删除从表 再删除主表 ；同时删除约束）
drop table tb_txt cascade constraints;
drop table tb_user cascade constraints;

3、创建表（同时创建约束+指定名称）
创建表的同时创建约束并指定约束的名称，后期方便排错，推荐使用
–删除 （先删除从表 再删除主表 ；同时删除约束）
drop table tb_txt cascade constraints;
drop table tb_user cascade constraints;

表名 tb_user
主键 userid
字段名 中文 类型 为空 默认值 其他说明
userid 流水号 number(5) 否 主键
username 用户名 varchar2(30) 否 长度在4-20