学习笔记——day23（组函数 约束 表连接）

表连接

-- 表连接
-- 92语法  99 语法
-- 内连接  外连接

-- 92语法：表连接

-- 笛卡尔积 
select * from emp,dept;
select * from emp;
select * from dept;


--等值连接
select * from emp,dept
where emp.deptno = dept.deptno

--员工名称及部门名称 
/*
 查询列 ename,dname
 查询表 emp,dept
 过滤条件： deptno
*/
select ename,dname
from emp,dept
where emp.deptno = dept.deptno;

select ename,dname
from emp e,dept d
where e.deptno = d.deptno;


--找出30部门的员工名称及部门名称
/*
 查询列  ename,dname
 查询表  emp,dept
 关联条件 deptno and deptno=30
*/
select ename,dname
from emp e,dept d
where e.deptno=d.deptno and e.deptno=30

-- 查询员工是30部门的员工信息
select emp.*
from emp
where emp.deptno = 30;


select d.dname,e.ename
from dept d,(select emp.*
              from emp
              where emp.deptno = 30) e
where d.deptno = e.deptno;

-- 非等值连接
--查询员工姓名，工资及等级 
/*
 查询列  ename,sal,grade
 查询表  emp,salgrade
 关联条件  emp.sal between salgrade.losal and salgrade.hisal;
*/
select * from salgrade;

select e.ename,e.sal,s.grade
from emp e,salgrade s
where e.sal between s.losal and s.hisal;

-- 自连接
-- 找出 员工姓名 及上级名称
select * from emp;
/*
  查询列  ename 员工表,ename 领导表
 查询表  emp e1, emp e2
 关联条件  e1.mgr = e2.empno
 */
select e1.ename,e2.ename
from emp e1,emp e2
where e1.mgr = e2.empno;
-- 以上所有都是内连接 内连接数无法查询到空值列的

----找出 所有的员工姓名 及上级名称

-- 外连接 是存在主从表关系
-- ＋号对面是主表
-- 将主表中的所有行记录全部查出，如果从表中没有对应的数据 通过null值填充
select e1.ename,e2.ename
from emp e1,emp e2
where e1.mgr = e2.empno(+);


-- 99语法
-- 笛卡尔积
select * 
from emp
cross join dept;


-- 等值连接
--员工名称及部门名称 
-- 多张表存在主外键关联 此时使用using 或者是多张表中存在同名列
select * 
from emp
join dept using(deptno);


-- join on
select * 
from emp
join dept on emp.deptno=dept.deptno;

-- 非等值连接
--查询员工姓名，工资及等级 
select e.ename,e.sal,s.grade 
from emp e
join salgrade s
on e.sal between s.losal and s.hisal;


-- 查询员工名称和上机领导名称
select e1.ename,e2.ename
from emp e1,emp e2
where e1.mgr = e2.empno;

select e1.ename,e2.ename
from emp e1
inner join emp e2
on e1.mgr = e2.empno;

-- 外联
-- left join 左侧是主表
-- right join 右侧是主表
select e1.ename,e2.ename
from emp e1
left outer join emp e2
on e1.mgr = e2.empno;

select e1.ename,e2.ename
from emp e1
right join emp e2
on e1.mgr = e2.empno;

-- 全连接 oracle的方言
select e1.ename,e2.ename
from emp e1
full join emp e2
on e1.mgr = e2.empno;

----部门编号为30 的员工名称 工资等级 
/*
 查询列 ename,grade
 查询表 emp,salgrade
 关联条件 emp.sal between salgrade.losal and salgrade.hisal and emp.deptno=30
*/
select ename,grade,deptno
from emp
join salgrade
on emp.sal between salgrade.losal and salgrade.hisal and emp.deptno=30;

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

/*
 查询列 dname,count(*)
 查询表 dept, select deptno,count(*) from emp group by deptno;
 关联条件 deptno
*/
-- 按照部门编号分组 查询每组员工的个数
select deptno,count(*) from emp group by deptno;

select d.dname,e.c
from dept d
join (select deptno,count(*) c from emp group by deptno) e
using(deptno);

----部门编号为30的员工 员工名称 部门名称 工资等级 上级名称
/*
 查询列 ename,dname,grade,ename
 查询表 emp e1,dept d,salgrade s,emp e2
 关联条件 
*/
select e1.ename,d.dname,s.grade,e2.ename
from emp e1
join dept d
on e1.deptno=d.deptno
join salgrade s
on e1.sal between s.losal and s.hisal
join emp e2
on e1.mgr = e2.empno;

/*
 1: 索引是用来提高查询效率的
 2：如何给表添加索引：
    sql  create index 索引名 on 表名(列名 ) 
    通过图形化界面
 3：给列添加索引的前提：
    a:列一定是频繁被查询的
    b：最好选择唯一性较好的列进行索引添加
    c：主键列会自动添加索引
*/

组函数

-- 函数
-- 日期函数：
-- 获取当前日期sysdate
select sysdate from dual;

-- 获取当前日期
select current_date from dual;

--修改日期 增加日期
select add_months(sysdate,2) from dual;
select sysdate+2 from dual;

--查询每个员工的转正日期
select ename,add_months(hiredate,3) from emp;


-- 获取两个月份之间的差值  获取入职员工的距今多少年
select ename,hiredate,months_between(sysdate,hiredate)/12 from emp;


-- 获取当前日期的最后一天
select last_day(sysdate) from dual;

-- 获取下个星期一的时间
select next_day(sysdate,'星期四') from dual;


--to_date(c,m)     字符串以指定格式转换为日期
--to_char(d,m)     日期以指定格式转换为字符串

select to_date('1999/12/12','yyyy/mm/dd') from dual;


select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh:mi:ss') from dual;

----查询 82的员工信息 
select * 
from emp
where hiredate between to_date('1982/01/01','yyyy/mm/dd') and to_date('1982/12/31','yyyy/mm/dd');

-- decode 10 员工涨薪10  20员工涨薪20  30员工涨薪30  
select ename,sal,deptno,
decode(
  deptno,
  10,
  sal*1.1,
  20,
  sal*1.2,
  30,
  sal*1.3
) as "涨薪之后的工资"
from emp;
 
select ename,sal,deptno,
(
   case deptno
   when 10 then sal*1.1
   when 20 then sal*1.2
   when 30 then sal*1.3
   end
) as "涨薪之后的工资"
from emp;


-- 组函数：不能查询其它列
-- 查询员工工资最大值
select max(sal) from emp;

select ename,sal
from emp
where sal = (select max(sal) from emp);

-- 查询最小工资
select min(sal) from emp;


-- 查询平均工资
select avg(sal) from emp;


-- 工资总和
select sum(sal) from emp;



--  count计数

select count(empno) from emp;
-- count 自动忽略null
select count(comm) from emp;


-- 组函数和分组一起使用
-- group by
-- 分组之后只能查询 分组列 分组函数
-- 按照员工的部门编号求平均工资

select deptno,avg(sal)
from emp
group by deptno;


-- 查询员工的工资高于当前部门平均薪资的员工信息
-- where 后不能跟分组函数
/*
查询列 select deptno, avg(sal)
查询表 from emp
过滤条件 where sal >  avg(sal)
分组条件 group by deptno
排序列 order by
排序规则 
*/
select deptno
from emp
where sal>avg(sal)
group by deptno;


----按 部门 查询 平均工资，且平均工资大于2000的部门编号
/*
 查询列：deptno
 查询表：emp
 分组：deptno
 组过滤：avg(sal) > 2000;
*/
select  deptno
from emp
group by deptno
having avg(sal) > 2000;


----查询 最低平均工资的部门编号 
-- 每个部门的平均工资
-- 求最小值
/*
 查询列：deptno
 查询表：emp
 分组：deptno
*/
select deptno 
from emp 
group by deptno 
having avg(sal) = (select min(avg(sal)) from emp group by deptno);


--查看 高于本部门平均薪水员工姓名
--- 查询部门的平均工资
select deptno,avg(sal) from emp group by deptno;
-- 查询每个员工的工资
select ename,sal,deptno from emp;
-- 查询每个员工的工资 高于 他自己部门的平均工资


select e1.ename,e1.sal,e1.deptno 
from emp e1
where sal > (select avg(sal) from emp e2  where e1.deptno= e2.deptno group by deptno) ;


/*
 查询列：ename
 查询表：emp
 分组：deptno
*/


--  rowid 它是一个伪列  oracle在添加数据时，会给当前提交的行记录 生产一个rowid的值，然后
-- rowid还是一个物理地址 所以可以通过rowid去唯一区分行记录 以及快速定位当前行

select emp.*,rowid from emp;


-- rownum 是oracle中分页的基础
select rownum ,emp.* from emp;

--  rownum是和排序
select rownum ,emp.* from emp order by sal desc;


-- 查询工资最高的前5个员工信息


select e.*,rownum
from (select emp.* 
      from emp
      order by sal desc) e
where  rownum<=5;


-- 查询工资最高的3-5个员工信息
select e.*,rownum
from (select emp.* 
      from emp
      order by sal desc) e
where  rownum<=5;

select e2.*,e2.rn
from
  (select e1.*,rownum rn
    from (select emp.* 
          from emp
          order by sal desc) e1
    where  rownum<=5) e2
where e2.rn>=3;


/*
实现分页：
      select 查询列
      from
        (select e1.*,rownum rn
          from (select 列 
                from 表
                order by 排序列 排序规则) e1
          where  rownum<=10) e2
      where e2.rn>=5;
      
      
具体分页的实现机制：

         首页  上一页  第一页  。。。。。。  第N也  下一页 尾页   
         if(current_page==1){
             第一页不让点
             隐藏上一页
         }
         
         查询  current_page的记录数  10
          select 查询列
          from
            (select e1.*,rownum rn
              from (select 列 
                    from 表
                    order by 排序列 排序规则) e1
              where  rownum<=current_page*page_size ) e2
          where e2.rn>=(current_page-1)*page_size+1;
         
            1： 总记录数  totle_size   count(*)      
            2:  每页显示的记录数   page_size   10/5
            3:  当前页码数   current_page  1 2 3 4 
               
     mysql的分页：
            
            select * from 表 where limit 数值1,数值2;
            数值1:第几条记录
            数值2：查询多少条记录
            
            select * from (select * from emp order by 排序列 排序规则) where limit 1,10; 
            
             select * from 表 where limit 11,10; 
       
         第current_page
         select * from 表 where limit (current_page-1)*page_size+1,page_size; 
       
         

*/

约束

-- 创建表
create table t_studnt(
 sid number(5),
 sname varchar(12),
 sage number(3)
);

-- dml语句：
-- 添加、删除、修改

--添加行
-- insert into 表名  values (值1,值2...);
insert into t_studnt values (1,'zhangsan',12);

select * from t_studnt;

insert into t_studnt values (1,'zhangsan',12);


-- 创建表给当前表中的数据增加一些约束用来 规范数据的格式
drop table t_studnt;


create table t_studnt(
 sid number(5) ,--可以添加约束
 sname varchar(12),
 sage number(3)
 -- 指定约束
);
-- 修改表中的约束


-- 主键约束、非空约束、唯一约束、检查约束、外键约束
create table t_studnt(
 sid number(5) primary key,
 sname varchar(12),
 sage number(3)
);
insert into t_studnt values (1,'zhangsan',12);

-- 如果添加的所有的数据项都存在 按照 创建表时的列的创建顺序依次添加
-- 如果添加的数据的列和常见表中列的个数顺序类型不同 直接给未指定列的项添加null值
-- insert into 表名  (列名1,列名2,....) values (值1,值2...);
-- 主键约束：非null 唯一
insert into t_studnt (sname,sage) values ('zhangsan',12);


-- not null 不能为null
-- unique 唯一约束
-- 添加默认值
-- 约束名称： 主键：pk  fk  uk ck  nk
create table t_studnt(
 sid number(5) primary key,
 sname varchar(12) unique,
 sage number(3) default 18 check(length(sage) between 1 and 2)
);
insert into t_studnt (sid,sname) values (1,'zhangsan');
select * from t_studnt;

insert into t_studnt (sid,sage,sname) values (2,12,'zhangsan');



-- 删除数据  delete 不会发生
-- delete from 表名;
-- delete from emp; 删除表数据 保留表结构

--delete from dept casecode;
-- delete from 表名 where xxx;


-- delete 删除表的时候 如果删除的表时主表 那么 不能删除
select * from emp;


select * from t_studnt;

create table t_studnt(
 sid number(5) primary key,
 sname varchar(12),
 sage number(3),
 isDelete number(2) default 1 
);

insert into t_studnt (sid,sname,sage) values(1,'123',12);
insert into t_studnt (sid,sname,sage) values(2,'234',23);

select * from t_studnt;

-- 修改操作  
-- update 表名 set 列名=列值 ; 该全表
-- update 表名 set 列名=列值  where 条件
update t_studnt set sage=22 where sid = 1;

update t_studnt set isDelete=0 where sid = 1;

select * from t_studnt where isDelete=1;


-- 凡是写操作都会产生事务 增加、删除、修改 
-- oracle中默认的事务隔离级别是  读未提交 脏读 
select * from t_studnt ;
insert into t_studnt (sid,sname,sage) values(3,'1234',42);
-- oracle 中默认是事务不提交 不会自动提交 mysql中事务是自动提交
-- 如何提交事务：
commit;

insert into t_studnt (sid,sname,sage) values(4,'124',52);
rollback;

-- delete from emp;
-- 某些sql语句 会自动将之前事务提交  dcl