99语法
select 数据(*|字段,字段|伪列) from 数据源(表|结果集|视图) where 行过滤条件 group by 分组字段 having 组过滤条件 order by 排序字段
多表连接
查询30部门员工信息以及员工所在部门信息以及员工薪资登记信息
select * from emp e,dept d,salgrade s where e.deptno=d.deptno and e.sal between s.losal and s.hisal and e.deptno = 3099链表查询
笛卡尔积 cross join
select * from emp e,dept d; --92
select deptno from emp e cross join dept d; --99等值连接
自然连接 natural join 自动根据同名字段,主外键关联关系 字段做等值连接
select deptno from emp e natural join dept d;
注意: 在自然连接中同名字段不能指明出处
join using(自动做等值连接的同名字段)...
select deptno from emp e inner join dept d using(deptno);
注意: 在join using中同名字段不能指明出处
非等值连接
数据源1 join 数据源2 on 表连接条件 可以做等值,可以做非等值
select e.deptno from emp e join dept d on e.deptno = d.deptno;
--查询员工信息以及员工工资等级信息
select * from emp e inner join salgrade s on e.sal between s.losal and s.hisal;
--查询员工信息以及所在部门信息以及员工工资等级信息,以及上级经理人信息
select *
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 e2
on e.mgr = e2.empno
where deptno in (10,30);内连接 : 满足连接条件才能显示 (inner) join
外连接 : 有的表中的数据不满足连接条件也显示
主表: 不满足连接条件也显示
左连接 : left join
右连接 : right join
全连接 : full join 两张表都作为主表
select * from emp e1 full join emp e2 on e1.mgr=e2.empno; decode(判定字段,值1,结果1,值2,结果2...,默认值)
select deptno,dname,loc, decode(deptno,10,'十',20,'二十',30,'三十','四十') 中文名称 from dept;case when then else end
select deptno,dname,loc, (case deptno when 10 then '十' when 20 then '二十' else '其他' end)中文名称 from dept;在下面的数据源做查询
/* id name course score
1 张三 语文 81
2 张三 数学 75
3 李四 语文 81
4 李四 数学 90
5 王五 语文 81
6 王五 数学 100
7 王五 英语 90*/
create table tb_student(
id number(4) ,
name varchar2(20),
course varchar2(20),
score number(5,2)
);
insert into tb_student values(1,'张三','语文',81);
insert into tb_student values(2,'张三','数学',75);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(8,'李四','英语',90);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(7,'王五','英语',90);
commit;
drop table tb_student cascade constraints;--使用一条sql语句，查询每门课都大于80分的学生姓名
select * from tb_student;
--分析:
--数据: 学生姓名
--来源: tb_student
--条件: 1)有几门可就考了几门课 2)没门课程分数>80 以人名为单位,每组的最小分数>80
--一共存在几门课程
select count(distinct course) from tb_student;
select name
from tb_student
group by name
having min(score) > 80 and count(1) = (select count(distinct course)
from tb_student);
--行转列 -->分组,判定函数
select name,
decode(course, '语文', score) 语文,
decode(course, '数学', score) 数学,
decode(course, '英语', score) 英语
from tb_student;
select name,
max(decode(course, '语文', score)) 语文,
min(decode(course, '数学', score)) 数学,
min(decode(course, '英语', score)) 英语
from tb_student group by name;
rowid 与 rownum 都做伪列存在
rowid : 行记录的地址,行的唯一标识,数据插入到表中的是决定
可以实现去重: 对没有主键|唯一字段的表中多条完全重复的数据,根据rowid实现去重
去重: 如果表中存在主键或者唯一字段,直接 根据主键或者唯一字段去重
select rowid,rownum from dept;
/*
insert into tb_student values(1,'张三','语文',81);
insert into tb_student values(2,'张三','数学',75);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(8,'李四','英语',90);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(7,'王五','英语',90);
commit;
*/
select id,name,course,score from tb_student;
--查询到要保留的数据
select max(rowid) from tb_student group by id,name,course,score; --要保留的8条数据的rowid
select *
from tb_student
where rowid in
(select max(rowid) from tb_student group by id, name, course, score);
--查询到要删除的数据
select *
from tb_student
where not rowid in
(select max(rowid) from tb_student group by id, name, course, score);
--删除这些数据
delete from tb_student
where not
rowid in
(select max(rowid) from tb_student group by id, name, course, score);
rownum 结果集的序号
--规律: 从1开始,每次+1
select * from (select empno,ename,job,mgr,rownum num from emp) where num >=5;
--一个select查询语句就有一个结果集,每一个结果集中数据都有自己的序号-->rownum
--注意: 当需要使用rownum进行一些判定,需要对一个已经确定的结果集的rownum进行判定--rownum与排序:
/*
一般规律为:
根据非主键字段排序,先确定rownum再排序
根据主键字段排序,先排序,后确定rownum
*/
select empno,ename,sal,rownum from emp order by sal;
select deptno,dname,loc,rownum from dept order by deptno desc;
--如何实现在有排序存在的情况下,保证rownum是规律的,从小到大的
select empno,ename,sal,n,rownum num from (select empno,ename,sal,rownum n from emp order by sal);
--分页
select *
from (select empno, ename, sal, rownum num
from (select empno, ename, sal from emp order by sal))
where num >= 7
and num <= 9;
视图与索引
视图 : 表与结果集之间
逻辑视图: 不会存储数据,数据来资源原始表结构,起到封装,简化,提高复用性的作用
物理视图: 真实存储数据
作用: sql语句的封装与简化,提高复用性,安全性
授权:
a)、sys登录
b)、授权: grant dba to SCOTT;
回收: revoke dba from SCOTT;
c)、重新登录--create or replace view 视图名 as select语句 [with read only];
create or replace view vw_haha as select empno,ename,sal,comm,deptno from emp where deptno = 20 with read only;
select * from vw_haha;
--修改视图中的数据其实是修改原有表机构中的数据,视图不存储数据
update vw_haha set sal=8000 where empno = 7369;
select * from emp;
update emp set sal=800 where empno = 7369;
--删除视图
drop view vw_haha;
索引
- 帮助提高查询效率
- 索引为表中的字段添加索引
- 大量数据的前提下,通过索引对某个字段的数据做查询,效率会大大提高
- 索引也是对象,需要通过是数据库维护
- 索引类似与目录
- 如果大量做根据字段做查询,少量做增删适合使用索引
- 如果大量根据字段做增删,少量查询,每次增删数据需要根据维护索引,会降低效率
--oracle自动为主键添加索引
select * from emp where sal>1500;
--为emp表的sal字段添加索引
--create index 索引名 on表名 (字段列表...)
create index index_emp_sal on emp(sal);
--drop index 索引名
drop index index_emp_sal;
--注意: 唯一性较好字段适合建立索引
--查询每一个部门中领导人的平均薪资最低的部门编号
--1)找到所有的领导人
select * from emp where empno in(select distinct mgr from emp where mgr is not null);
--2)按照部门分组找到领导的最低平均工资
select deptno,avg(sal) from emp where empno in(select distinct mgr from emp where mgr is not null) group by deptno;
--最低平均工资
select min(avg(sal)) from emp where empno in(select distinct mgr from emp where mgr is not null) group by deptno;
--3)找到平均工资与最低平均工资相等的部门编号
select deptno,avg(sal) from emp where empno in(select distinct mgr from emp where mgr is not null) group by deptno having avg(sal) = (最低平均工资);
--部门编号
select deptno
from emp
where empno in (select distinct mgr from emp where mgr is not null)
group by deptno
having avg(sal) = (select min(avg(sal))
from emp
where empno in
(select distinct mgr from emp where mgr is not null)
group by deptno);
--部门名称
select dname
from dept
where deptno in
(select deptno
from emp
where empno in (select distinct mgr from emp where mgr is not null)
group by deptno
having avg(sal) = (select min(avg(sal))
from emp
where empno in (select distinct mgr
from emp
where mgr is not null)
group by deptno));表设计
根据业务需求,满足三范式要求
三范式:
1)列不可再分,字段的原子性
2)定义主键(唯一+非空),确定唯一
3)拆分表,通过主外键约束定义两张表的关系 ,消除传递依赖,避免数据的过渡冗余
表名
确定表中的字段
字段: 字段名 数据类型 字段的约束(要求)
数据
表与表之间的关系
一对一 : 用户 身份证 -->主外键
一对多|多对一 : 学生 与 班级 -->主外键
多对多 : 订单与商品 学生与课程 --> 中间表
DDL create创建表
create 表名(
字段名 字段类型,
字段名 字段类型,
.....
字段名 字段类型
)
--班级表
create table yjx_class(
cid number(5) constraint pk_cls primary key,
cname varchar2(15) not null
)
--学生表
create table yjx_student(
sid number(5) primary key,
sname varchar2(15) not null, --(2 char) 2个字符 否则默认字节数
sgender char(3) check(sgender in ('男','女')),
birthday date default(sysdate),
cid number(5) ,--references yjx_class(cid)
--为指定的字段添加约束
constraint fk_cls_id foreign key(cid) references yjx_class(cid)
)
--表 结构之后追加约束
--alter table tb_user add constraint uq_user_email unique(email);
select * from t_user;添加约束
- 主键约束 : 唯一+非空 primary key
- 唯一约束 : unique
- 非空约束 : not null
- 默认约束 : default(默认值)
- 外键约束 : references
- 检查约束 : check(检查条件)
在创建表时候添加约束
- 创建表结构的时候,字段的后面直接添加约束 --> 方便简单,不需要指定字段名,不能指定约束名
- 创建表结构的时候,字段的后面直接添加约束，同时指定约束名
- 创建表结构的结束之前，字段的定义之后位置，为某个字段添加某种约束，可以指定约束名
- 表结构创建之后追加约束
注意： 不建议为表结构中的字段大量添加约束
物理约束 ： 表的字段上添加约束 ： 主外键约束
逻辑约束 ： 代码层面上添加约束 ： 检查约束
注释
comment on table yjx_student is '学生表';
comment on column yjx_student.sid is '学生编号，主键';
--create table 表名 as select 字段列表 from 已有表 where 1!=1;
--创建表+拷贝数据
create table haha as select empno,ename,sal from emp;
--创建表+不拷贝数据
create table haha as select empno,ename,sal from emp where 1!=1;
select * from haha;
--约束的禁用与启用
ALTER TABLE yjx_student disable constraint fk_cls_id;
ALTER TABLE yjx_student enable constraint fk_cls_id;
insert into yjx_student values(1001,'zhangsan','男',sysdate,900);
select * from yjx_student;
--删除约束 ,可以级联删除
--alter table tb_user drop constraint uq_user_email cascade;
alter table yjx_class drop constraint SYS_C007316 cascade;DDL : 数据定义语言
drop 删除表
drop table 表名;
注意: 如果两张表具有主外键关联关系,从表可以直接删除
删除主表: 1) 默认先删除从表,再删除主表
2)级联删除 : 删除主表的同时,级联删除主外键约束
drop table haha;
drop table yjx_student;
drop table yjx_class cascade constraints;
--alter 修改表结构
--create 创建
select * from t_user;
--修改列名
alter table t_user rename column userid to id;DML 数据管理语言
数据的增insert 删delete 改update
数据的查询 DQL
在对表中数据做增删改自动开启事务,需要提交或者回滚
--insert
--insert into 表名 values(值列表 );
insert into t_user values (1001,'zhangsan','女',sysdate);
--insert into 表(指定列) values(值列表);
insert into t_user(id,username) values(seq_user_id.nextval,'lisi');
--insert into 表名 select 查询列 from 源表 where 过滤数据;
insert into t_user select empno,ename,hiredate from emp where deptno = 30;
--insert into 表(指定列) select 查询列 from 源表 where 过滤数据;
insert into t_user(id,username) select deptno,dname from dept;序列工具
帮助管理类似主键字段的值
序列与表与字段不绑定
序列第一次需要获取nextval
--创建 create sequence 序列名 start with 起始值 increment by 步进;
create sequence seq_user_id start with 1003 increment by 1;
--获取当前序列工具的当前值
select seq_user_id.currval from dual;
--获取当前序列工具的下一个值|最新值
select seq_user_id.nextval from dual;
--drop sequence 序列名;
drop sequence seq_user_id;
----删除列
alter table t_user drop column gender;
--修改 update 表名 set 字段=值 [,....] where 过滤行记录;
update t_user set regdate=sysdate where 1=1;-- 修改所有数据的指定字段值
update t_user set username='zhangsanfeng' where id = 1001; ---- 修改满足条件数据的指定字段值
117
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
