数据库语法

92语法
	--多表连接  *****
	--92语法 
	--99语法
	
	--笛卡尔积
	select * from dept,emp;
	
	--等值连接  如果使用多个表相同的字段,注意指明字段的出处 ,等值连接的时候两个表中的字段类型必须相同,字段名可以不同
	-- 查询所有员工的信息以及所在部门的信息
	--数据:员工信息,部门信息
	--数据的来源:emp,dept
	select empno,ename,dept.deptno,dname from emp,dept where emp.deptno=dept.deptno;
	select empno,ename,e.deptno,dname from emp e,dept d where e.deptno=d.deptno; --别名  
	
	
	--找出30部门的员工名称及部门名称
	select ename,dname from emp e,dept d where e.deptno=d.deptno and e.deptno=30; --先连接后判断
	select ename from emp where deptno=30;
	select dname from dept where deptno=30;
	select * from (select ename from emp where deptno=30),(select dname from dept where deptno=30);--先判断后连接
	
	--非等值连接
	-- 2500 所在的工资等级是多少
	select * from salgrade where 2500 between losal and hisal;
	--查询员工姓名，工资及等级
	select ename,sal,grade from emp,salgrade where sal between losal and hisal;
	
	-- 10和30部门的员工信息,部门信息以及工资等级
	select ename,sal,grade from emp,salgrade where sal between losal and hisal and deptno !=20;
	select ename, sal, grade,emp.deptno,dname
	  from emp, salgrade, dept
	 where sal between losal and hisal
	   and emp.deptno = dept.deptno
	   and emp.deptno != 20;
	
	--外连接  主表:所有的数据都能显示,在连接条件上在主表对面的表上添加(+)
	--左外连接  观察主表在from emp,dept在逗号的左边就是左外连接
	--右外连接  观察主表在from emp,dept在逗号的左边就是右外连接
	--所有部门及其员工信息
	select * from emp,dept where dept.deptno=emp.deptno(+);
	
	--自连接
	-- 找出有上级的员工的名称和对应的上级名称
	--数据来源:员工表emp e1和上级表emp e2
	select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;
	-- 找出所有的员工的名称和对应的上级名称
	select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno(+);
	
	--找出 所有部门的员工数 及部门名称
	--找出 所有部门的员工数和部门 编号
	select count(1),deptno from emp group by deptno;
	select d.deptno, num
	  from dept d, (select count(1) num, deptno from emp group by deptno) e
	 where d.deptno = e.deptno(+);
	 
	select dname, nvl(num,0)
	  from dept d, (select count(1) num, deptno from emp group by deptno) e
	 where d.deptno = e.deptno(+);
99语法
	--99语法
	--笛卡尔积  cross join
	select * from emp cross join dept;
	
	-- 查询所有员工的信息以及所在部门的信息
	--等值连接
	--自然连接  natural join 自动匹配表中的同名字段主外键关系
	select deptno from emp natural join dept;  --同名列不能写限定词
	
	--join using(字段) 某个字段相同的等值连接
	select dept.deptno from emp join dept using(deptno);  --同名列不能写限定词
	
	
	--表1 join 表2 on 连接条件 可以做等值和非等值连接,自定义连接条件  同名字段的限定名必须写
	--找出30部门的员工名称及部门名称
	select ename,dname from emp inner join dept using(deptno) where deptno=30;
	select ename,dname from emp inner join dept on emp.deptno=dept.deptno where emp.deptno=30;  --先连接后判断
	
	select * from (select ename from emp where deptno=30) cross join (select dname from dept where deptno=30);--先判断后连接
	
	--非等值连接
	--查询员工姓名，工资及等级
	select ename,sal,grade from emp,salgrade where sal between losal and hisal;
	select ename,sal,grade from emp join salgrade on sal between losal and hisal;
	
	-- 10和30部门的员工信息,部门信息以及工资等级
	select ename "员工名称", dname "部门名称", grade "工资等级"
	  from emp
	  join dept
	    on emp.deptno = dept.deptno
	  join salgrade
	    on sal between losal and hisal where emp.deptno in(10,30);
	
	--外连接  主表:所有的数据都能显示
	--左外连接  观察主表在from emp dept在逗号的左边就是左外连接  left join
	--右外连接  观察主表在from emp dept在逗号的左边就是右外连接  right join
	--所有部门及其员工信息
	select * from emp right join dept on dept.deptno=emp.deptno;
	
	--自连接
	-- 找出有上级的员工的名称和对应的上级名称
	--数据来源:员工表emp e1和上级表emp e2
	select e1.ename,e2.ename from emp e1 join emp e2 on e1.mgr=e2.empno;
	-- 找出所有的员工的名称和对应的上级名称
	select e1.ename,e2.ename from emp e1 left join emp e2 on e1.mgr=e2.empno;
	
	--找出 所有部门的员工数 及部门名称
	--找出 所有部门的员工数和部门 编号
	select dname, nvl(num,0)
	  from dept d left join (select count(1) num, deptno from emp group by deptno) e
	 on d.deptno = e.deptno;
	
	select 1 no, 'a' "name" from dual
	union
	select 2 no, 'b' "name" from dual;
	
	select 1 no, 'c' "name" from dual
	union
	select 3 no, 'd' "name" from dual;
	
	--等值连接
	select *
	  from (select 1 no, 'a' "name"
	          from dual
	        union
	        select 2 no, 'b' "name"
	          from dual) d1
	  join (select 1 no, 'c' "name"
	          from dual
	        union
	        select 3 no, 'd' "name"
	          from dual) d2
	    on d1.no = d2.no;
	
	--左外连接
	select *
	  from (select 1 no, 'a' "name"
	          from dual
	        union
	        select 2 no, 'b' "name"
	          from dual) d1
	  left join (select 1 no, 'c' "name"
	          from dual
	        union
	        select 3 no, 'd' "name"
	          from dual) d2
	    on d1.no = d2.no;
	--右外连接
	select *
	  from (select 1 no, 'a' "name"
	          from dual
	        union
	        select 2 no, 'b' "name"
	          from dual) d1
	  right join (select 1 no, 'c' "name"
	          from dual
	        union
	        select 3 no, 'd' "name"
	          from dual) d2
	    on d1.no = d2.no;
	
	--全连接  两张表都作为主表
	select *
	  from (select 1 no, 'a' "name"
	          from dual
	        union
	        select 2 no, 'b' "name"
	          from dual) d1
	  full join (select 1 no, 'c' "name"
	          from dual
	        union
	        select 3 no, 'd' "name"
	          from dual) d2
	    on d1.no = d2.no;
rowid和rownum
	--rowid伪列,每一行记录的标识,在行记录插入表时就确定的,相当于对象的地址,不是真实的地址
	select deptno,dname,rowid from dept;
	select ename,rowid from emp;
	
	select * from tb_student;
	insert into tb_student values(3,'李四','语文',81);
	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(3,'李四','语文',81);
	insert into tb_student values(6,'王五','数学',100);
	insert into tb_student values(3,'李四','语文',81);
	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(3,'李四','语文',81);
	insert into tb_student values(4,'李四','数学',90);
	insert into tb_student values(5,'王五','语文',81);
	insert into tb_student values(6,'王五','数学',100);
	
	--没有主键的表中相同数据的去重
	--找到要保留的数据,多条相同数据中的任意一个
	select name,course,score,min(rowid) from tb_student group by name,course,score;
	--要删除,除了保留以外的其他数据
	select * from tb_student where not rowid in(select min(rowid) from tb_student group by name,course,score);
	
	delete from tb_student where not rowid in(select min(rowid) from tb_student group by name,course,score);
	
	--rownum 伪列  结果集的序号,从1开始
	--rownum和排序顺序:
	--       如果按照主键进行排序先排序后确定rownum
	--       如果按照非主键字段排序,先确定rownum再排序
	select deptno,dname,rownum from dept order by deptno desc;
	select empno,ename,sal,rownum from emp order by empno desc;
	
	--分页  应该对已经确定的并且不变的rownum,有规律的做判断
	--获取前3条数据
	select empno,ename,sal,rownum from emp where rownum<=4;
	--每页显示几条数据 num=4   当前的页数:i    要显示的数据的rownum的范围:   最小值: (页数i-1)*每页显示几个num+1          最大值: 页数i*每页显示几个num 
	select empno,ename,sal,rownum from emp;
	select empno,ename,deptno,rownum,n,sal
	  from (select empno, ename,deptno, sal, rownum n from emp order by sal desc) --确定有规律的不变的num
	 where n >= (2-1)*4+1
	   and n <= 2*4;
	
	
	--根据薪资进行降序排序找第二页的数据 
	select ename, sal
	  from (select ename, sal, rownum n
	          from (select empno, ename, deptno, sal, rownum
	                  from emp
	                 order by sal desc))
	 where n >= (2 - 1) * 4 + 1
	   and n <= 2 * 4;
	   
	--真分页:每次真实去数据库中查询一条数据
	--假分页:每次查出一批数据,按需求展示
视图和索引
	--视图是一个虚拟表  介于表和结果集之间的,不是真实存在的,根据需求进行添加提供使用效率
	--create or replace view 视图名 as select语句 [with read only];
	create or replace view vw_emp as select empno,ename,sal,deptno from emp where deptno in(10,30) with read only;
	--使用视图 操作视图不会修改原表中的数据
	select * from vw_emp;
	
	--删除
	drop view vw_emp;
	
	--查询出所有上级经理人的中平均薪资中最低的部门名称
	--先找出所有的上级经理人员工编号
	select distinct mgr from emp where mgr is not null;
	--上级经理人的员工信息
	select empno, ename, deptno, sal
	  from emp
	 where empno in (select distinct mgr from emp where mgr is not null);
	 
	--创建视图
	create or replace view vw_mgr as select empno, ename, deptno, sal
	  from emp
	 where empno in (select distinct mgr from emp where mgr is not null);
	select * from vw_mgr;
	
	--按照部们分组求平均薪资
	select avg(sal), deptno
	  from (select empno, ename, deptno, sal
	          from emp
	         where empno in (select distinct mgr from emp where mgr is not null))
	 group by deptno;
	--求最小平均数
	select min(avg(sal))
	  from (select empno, ename, deptno, sal
	          from emp
	         where empno in (select distinct mgr from emp where mgr is not null))
	 group by deptno;
	--部门经理人平均薪资最低的部门编号
	select deptno
	  from (select avg(sal) avg_sal, deptno
	          from (select empno, ename, deptno, sal
	                  from emp
	                 where empno in
	                       (select distinct mgr from emp where mgr is not null))
	         group by deptno)
	 where avg_sal =
	       (select min(avg(sal))
	          from (select empno, ename, deptno, 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 =
	       (select deptno
	          from (select avg(sal) avg_sal, deptno
	                  from (select empno, ename, deptno, sal
	                          from emp
	                         where empno in (select distinct mgr
	                                           from emp
	                                          where mgr is not null))
	                 group by deptno)
	         where avg_sal =
	               (select min(avg(sal))
	                  from (select empno, ename, deptno, 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 =
	       (select deptno
	          from (select avg(sal) avg_sal, deptno
	                  from (select * from vw_mgr)
	                 group by deptno)
	         where avg_sal =
	               (select min(avg(sal))
	                  from (select * from vw_mgr)
	                 group by deptno));
	
	--索引  
	--是数据库的对象之一
	--当存在大批量数据的查询,可以使用索引加快查询速度
	--如果频繁对索引字段做增删改效率会降低,对象需要oracle维护
	--相当于目录
	--是否存在索引对数据库中数据的使用没有任何区别
	--oracle 创建主键时会自动在该列上创建索引
	
	--create index 索引名 on表名 (字段列表...)
	create index index_emp_sal on emp(sal);
	--drop index 索引名
	drop index index_emp_sal;
	
	select * from emp where sal>800;
表设计
	--设计表首先应该按需遵循三范式
	--1、确定表名 2、确定字段名 类型 +约束(主键 外键 非空 默 检查认 唯一)
	--1.创建表  不添加约束
	create table student(
	       --学号
	       sid number(5),
	       --姓名
	       sname varchar2(15),
	       --年龄
	       sage number(3),
	       --性别
	       sgender char(3),
	       --qq号
	       sqq varchar2(30)
	);
	
	--2.已有表中拷贝结构 create table 表名 as select 字段列表 from 已有表 where 1!=1;
	--值拷贝表结构,不拷贝数据
	create table haha as select * from emp where 1!=1;
	--拷贝表结构包括数据
	create table haha as select * from emp;
	create table haha as select empno,ename,sal,deptno from emp where sal>1500;
	
	select * from haha;
	drop table haha;
	
	--3.创建表+约束
	--3,1 字段后直接添加约束(没有约束名)  不变与调试,但是简单
	create table student(
	       --学号 主键约束(非空+唯一)
	       sid number(5) primary key,
	       --姓名 非空约束
	       sname varchar2(15) not null,
	       --年龄 检查约束 1~150
	       sage number(3) check(sage>=1 and sage<=150),
	       --性别 检查约束 '女','男'
	       sgender char(3) check(sgender='女' or sgender='男'),
	       --qq号 唯一
	       sqq varchar2(30) unique
	);
	
	--3,2 字段后直接添加约束(指定约束名) 
	--3,3 创建表结构结束钱指定约束(执行约束名+指定添加约束的字段)
	--从表,子表
	create table student(
	       --学号 主键约束(非空+唯一)
	       sid number(5) constraints pk_student_sid  primary key,
	       --姓名 非空约束
	       sname varchar2(15) constraints student_sname_notNull not null,
	       --年龄 检查约束 1~150
	       sage number(3),
	       --性别 检查约束 '女','男'
	       sgender char(3),
	       --qq号 唯一
	       sqq varchar2(30),
	       --所属班级
	       cid number(6) references sxt_class(cid) on delete set null,
	       constraints stu_sage_check check(sage>=1 and sage<=150)
	       --constraint fk_stu_cid_class_cid foreign key(cid) references sxt_class(cid) on delete cascade,
	);
	
	--班级表  主表,父表
	create table sxt_class(
	       cid number(6) primary key,
	       object varchar2(45)
	)
	
	--3,4 创建表结构之后追加约束(指定约束名,+指定添加约束的字段)
	--alter table 表名 add constraint 约束名 unique|其他约束(字段);
	alter table student add constraint stu_sqq_uq unique(sqq);
	alter table student add constraint fk_stu_cid_class_cid foreign key(cid) references sxt_class(cid);
	--删除约束
	--alter table tb_user drop constraint uq_user_email cascade;
	alter table student drop constraint stu_sqq_uq;
	
	--插入值
	insert into student values(1,'zhangsan',20,'女','123456',1001);
	insert into student values(2,'lisi',25,'男','123456');
	
	insert into sxt_class values(1001,'java29班');
	insert into sxt_class values(1002,'java30班');
	
	--删除表  drop table 表名 cascade constraints
	drop table student; --从表
	drop table sxt_class cascade constraints; --主表
	select * from student;
	select * from sxt_class;
	
	--删除数据
	delete from sxt_class where cid=1001;
	delete from student where cid=1001;
	
	--如果存在主从表关系 删除表 1)先删除从表,再删除主表  2)级联删除主表以及主从表之间的主外键约束  默认
	--如果存在主从表关系 删除数据 1)删除引用了主表中的这个数据从表数据,再去删除主表数据  2)删除主表数据的同时给从表中引用了数据的字段值设置为null  on delete set null
	                            --3)主表数据以及引用了的从表数据全部删除  on delete cascate                                                
	--加入注释
	comment on table student is '学生表';
	comment on column student.sid is '学号,主键';
	comment on column student.sname is '学生姓名';
	comment on column student.sage is '年龄';
	comment on column student.sgender is '性别';