2021-07-29

99链表查询

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

交叉连接：

select * from emp cross join dept;

自然连接：自动做等值连接

select * from emp natural join dept;
--在指定列过程中同名列归共同所有(*除外)
select deptno,e.ename,d.dname from emp e natural join dept d;

using 连接： 指定同名字段做等值连接

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

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;

外连接：

--所有部门的 部门名称，员工数
--左外
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;

全连接：

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);

行转列

rowid 与 rownum

视图与索引

视图view：

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

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];

​ 要求:所有列必须存在名称。

索引（了解）

前提 : 设计表首先应该按需遵循三范式

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

索引: 提高查询速度的一种手段 -->目录
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. 确定字段名 类型 +约束(主键 外键 非空 默 检查认 唯一)
   主键： 唯一标识一条记录（唯一并且非空）
   唯一： 唯一
   非空：不能为空
   默认： 当没给值时使用给定一个默认值
   外键：参考其他表（自己）的某个（某些）字段
   检查：自定义的规则

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

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

创建表（追加创建约束+指定名称）
推荐， 便于后期排错

已有表中拷贝结构

create table 表名 as select 字段列表 from 已有表 where 1!=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;

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

查看某个用户的约束

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

查看表的约束

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

查看 字段名+约束

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

约束的禁用与启用

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

删除约束

alter table tb_user drop constraint uq_user_email cascade;

修改约束

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

DDL

​ （Data Definition Language 数据定义语言）

​ 用于操作对象和对象的属性

create 创建数据表，alter 可以更改该表的字段，drop 可以删除这个
表,从这里我们可以看到，DDL 所站的高度，他不会对具体的数据进行操作。

语句	作用
create	可以创建数据库和数据库的一些对象
drop	可以删除数据表、索引、条件约束等
alter	修改数据表定义及属性

删除表

drop table 表名 (cascade constraints)
--删除表
drop table emp_his;
--主从表关系下删除表
--先删除从表 再删除主表 ；同时删除约束
drop table tb_txt cascade constraints;
drop table tb_user cascade constraints;
--删除主表的同时级联删除约束
drop table emp_his cascade constraints;

修改表结构

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;
select * from tb_txt_new;

DML

（Data Manipulation Language 数据操控语言）用于操作数据库对象中包含的数据，也就是说操作的单位是记录。

DML 的主要语句（操作）

语句	作用
Insert	向数据表张插入一条记录
Delete	删除数据表中的一条或多条记录，也可以删除数据表中的所有记录，但是，它的操作对象仍是记录
Update	用于修改已存在表中的记录的内容

使用场景

insert	注册
update	修改密码
delete	退出、删除、剔除会员
select	登录|查看会员