有关与数据库的一些基础知识(待完善)

create table emp_hanc(
       empno int(4),
       ename varchar(10),
       job varchar(9),
       mgr int(4),
       hiredate date,
       sal double(7,2),
       comm double(7,2),
       deptno int(2)
);
commit;
create table dept_hanc(
       deptno int(2),
       dname varchar(14),
       loc varchar(13)
);
commit;
INSERT INTO emp_hanc
(empno , ename , job , mgr , hiredate , sal ,deptno)
VALUES
(7369,'SMITH','CLERK',7902,DATE('1980-12-17'),800.00,20);
INSERT INTO emp_hanc
(empno , ename ,job ,mgr, hiredate, sal, comm ,deptno)
VALUES
(7499,'ALLEN','SALESMAN',7698,DATE('1981/2/20'),1600.00,300.00,30);
INSERT INTO emp_hanc
(empno , ename ,job ,mgr, hiredate, sal, comm ,deptno)
VALUES
(7521,'WARD','SALESMAN',7698,DATE('1981/2/22'),1250.00,500.00,30);
INSERT INTO emp_hanc
(empno , ename ,job ,mgr, hiredate, sal,deptno)
VALUES
(7566,'JONES','MANAGER',7839,DATE('1981/4/2'),2975.00,20);
INSERT INTO emp_hanc
(empno , ename ,job ,mgr, hiredate, sal,comm,deptno)
VALUES
(7654,'MARTIN','SALESMAN',7698,DATE('1981/9/28'),1250.00,1400.00,30);
INSERT INTO emp_hanc
(empno , ename ,job ,mgr, hiredate, sal,deptno)
VALUES
(7698,'BLAKE','SALESMAN',7839,DATE('1981/5/1'),2850.00,30);
commit;


INSERT INTO emp_hanc VALUES(7782,'CLARK','MANAGER',7839,date('1981-8-7'),2450,NULL,10);
INSERT INTO emp_hanc VALUES(7788,'SCOTT','ANALYST',7566,date('1987-4-19'),3000,NULL,20);
INSERT INTO emp_hanc VALUES(7839,'KING','PRESIDENT',NULL,date('1981-11-17'),5000,NULL,10);
INSERT INTO emp_hanc VALUES(7844,'TURNER','SALESMAN',7698,date('1981-9-8'),1500,0,30);
INSERT INTO emp_hanc VALUES(7876,'ADAMS','CLERK',7788,date('1987-9-7'),1100,NULL,20);
INSERT INTO emp_hanc VALUES(7900,'JAMES','CLERK',7698,date('1981-8-2'),950,NULL,30);
INSERT INTO emp_hanc VALUES(7902,'FORD','ANALYST',7566,date('1981-12-3'),3000,NULL,20);
INSERT INTO emp_hanc VALUES(7934,'MILLER','CLERK',7782,date('1982-1-23'),1300,NULL,10);


INSERT INTO dept_hanc VALUES
        (10,'ACCOUNTING','NEW YORK');
    INSERT INTO dept_hanc VALUES 
       (20,'RESEARCH','DALLAS');
    INSERT INTO dept_hanc VALUES
        (30,'SALES','CHICAGO');
    INSERT INTO dept_hanc VALUES
        (40,'OPERATIONS','BOSTON');
commit;
别名
当查询的字段名是函数或表达式时,查询结果集
对应的该列的字段名就是这个函数的表达式,
这样的可读性差,所以,通常我们会为函数或表达的
字段添加别名,别名不可以是关键字.别名可以使用双引号括起来,
这样可以区分大小写和添加空格.


select ename ,sal*12 ,sal from emp_hanc;


AND 优先级高于OR
select ename , sal , job from emp_hanc
where sal > 1000
AND(job='SALESMAN' or job='CLERK');


select * from emp_hanc;


LIKE 可以进行模糊匹配字符串支持两个通配符:
%:0-多个字符
_:单一的一个字符
查看名字第二个字母是A的员工:
select ename,sal,deptno 
from emp_hanc
where ename like '_A%';


in(list) not in(list)
在列表中或不在列表中
select ename , job from emp_hanc
where job in('MANAGER','CLERK');


SELECT ename ,job from emp_hanc
where deptno not in(10,20);


between...and...
判断在一个范围内
select ename , sal from emp_hanc
where sal between 1500 and 3000;


any , all
用于配合比较运算符:>,>=,<=,=使用
>any(list):大于列表其中之一即可
>all(list):大于列表所有
列表通常不会给确定值,否则没意义,一般是在子查询中使用
select empno, ename,job,sal ,deptno from emp_hanc
where sal > any(3500,4000,4500);


使用函数或表达式判断
select ename , sal ,job from emp_hanc
where ename = upper('scott');


select ename, sal ,job from emp_hanc
where sal*12 >50000;


DISTINCT 关键字
用于去除后面指定字段相同的查询结果集中的记录


查看公司那些职位
select distinct job from emp_hanc;


对多字段去重
指定字段值的组合没有重复记录
select DISTINCT job,deptno from emp_hanc;


排序结果集
order by 子句
order by 可以根据后面指定的字段按照升序或降序排列
其中升序为ASC,可以不写,默认就是升序,DESC为降序


查看公司工资排名
select ename , sal 
from emp_hanc
order by sal desc;


order by 可以根据多字段排序,但是排序有优先级顺序,
先按照第一个字段排序,当第一个字段值相同的情况下,
再按照第二个字段排序,以此类推
select ename , deptno,sal
from emp_hanc
order by deptno desc ,sal desc;


若排序的字段中含有NULL值,那么NULL被当做最大值
select ename,comm
from emp_hanc
order by comm;


聚合函数,又称为分组函数,多行函数作用是
将结果集按照指定字段进行统计然后得到一条记录
max 与min
统计指定字段的最大值与最小值
select max(sal),min(sal) from emp_hanc;


AVG 与 sum
统计指定字段的平均值与总和
select AVG(SAL),sum(sal) from emp_hanc;


count
统计指定字段不为null的记录总数
select count(ename) from emp_hanc;


聚合函数忽略null值
select AVG(NVL(comm,0)) from emp_hanc;


查看一张表中的记录数据常用count(*)
select count(*) from emp_hanc;


分组
group by 子句
group by 可以将结果集按照指定的字段值
相同的记录进行分组,配合聚合函数可以实现
对每组记录进行统计


当select中含有聚合函数时,那么不是聚合函数
的其他单独字段都需要出现在group by 子句中
但是反过来则不是必须的.


查看每个部门的平均工资?
select avg(sal),deptno from emp_hanc group by deptno;


每个职位的工资总和?
select sum(sal),job from emp_hanc group by job;


group by 按照多字段分组原则:
这些字段的组合相同的看做一组


查看每个部门每种职位的平均工资
select AVG(SAL),deptno,job from emp_hanc group by job,deptno;


查看部门人数高于3人的这些部门的平均工资?
select avg(sal) , deptno from emp_hanc where count(*)>3 group by deptno;这个是错误的


where 中不允许使用聚合函数作为过滤条件,
原因在于时机不对.
where是在查询表中每条数据是进行过滤的,所以
where决定着表中那条数据可以被查询出来.
而分组统计是在表中数据查询出来后基于结果集进行的.
所以根据分组统计的结果作为过滤条件是不能在where中使用的.


HAVING子句
HAVING 子句不能独立存在,必须跟在group by子句之后,
having可以使用聚合函数作为过滤条件,他是用来根据统计结果决定保留那些分组的


查看部门人数高于3人的这些部门的平均工资?
select avg(sal),deptno
from emp_hanc
group by deptno
having count(*)>3;


查看每种职位的人数,前提是该职位最低工资要高于1000
select count(*),job from emp_hanc group by job having min(sal)>1000;


关联查询查看每个员工的名字及所在部门的名字?
select * from dept_hanc;
select e.ename,d.dname 
from emp_hanc e, dept_hanc d
where e.deptno=d.deptno;


在关联查询中,过滤条件要与关联跳间同时成立
查看sales部门的员工信息?
select e.ename , e.sal,d.dname
from emp_hanc e,dept_hanc d
where e.deptno=d.deptno
AND d.dname='SALES';


SELECT e.Ename
from emp_hanc e,dept_hanc d
where e.deptno = d.deptno
AND d.loc = 'NEW YORK';


查看每个地区工作的人数?
select count(*),d.loc
from emp_hanc e , dept_hanc d
where e.deptno = d.deptno
group by d.loc;


在关联查询中,若不指定连接条件,则会产生笛卡尔积,
该结果集会将每张表中的每条记录分别连接一次并组成
一条记录,开销巨大,通常是一个没有一个没有意义的结果集
结果集条数是表中记录数的乘积
select e.ename,d.dname from emp_hanc e ,dept_hanc d;


n张表查询应当有N-1个连接


join内连接
也可以实现关联查询
select e.ename , d.dname 
from emp_hanc e join dept_hanc d
on e.deptno = d.deptno
where d.dname = 'SALES';


无论是关联查询还是内连接,都胡洛不满足连接条件的记录


外连接
外连接可以在关联查询中将不满足连接条件的记录也查询出来
外连接分为左外连接,右外连接,和全外连接
以左外连接为例:
左外连接以join左侧的表作为驱动表(主要显示数据的表),该表中的所有记录
都会被查询出来,那么当某条记录不满足连接条件时,那么来自join右侧表的字段全部以null作为值


select e.ename ,d.dname 
from emp_hanc e left outer join dept_hanc d
on e.deptno = d.deptno;


select e.ename ,d.dname
from emp_hanc e ,dept_hanc d
where e.deptno(+)=d.deptno;


自连接
用于解决相同类型数据,但是有存在上下级关系的树状结构的存储
自连接,当前表的记录与当前表的其他记录有对应关系.


查看每个员工名字的名字以及其上司的名字?
select e.ename ,m.ename 
from emp_hanc e ,emp_hanc m
where e.mgr = m.empno(+);


查看每个员工的领导在哪里工作?
列出3个字段,分别是 员工名,上司名,上司工作所在地?
select e.ename , m.ename , d.loc
from emp_hanc e ,emp_hanc m, dept_hanc d
where e.mgr= m.empno
and m.deptno = d.deptno
and e.ename='SMITH';


SELECT e.ename , m.ename ,d.loc
from emp_hanc e join emp_hanc m
on e.mgr = m.empno
join dept_hanc d 
on m.deptno = d.deptno
where e.ename = 'SMITH';
查看工资高于2000的员工
SELECT ename,sal  from emp_hanc where sal>2000;
查看不是'clerk'职位的员工
select ename ,job from emp_hanc where job not in('CLERK');
查看工资在1000-2500之间的员工
SELECT ename ,job,sal from emp_hanc where sal between 1000 and 2500;
查看名字是以K结尾的员工
select ename  from emp_hanc where ename like '%K'; 
查看20,30号部门的员工
select ename,deptno from emp_hanc where deptno in(20,30); 
查看奖金为null的员工
select ename ,max(comm) from emp_hanc order by comm;
select ename ,comm  from emp_hanc where comm is null;
select * from emp_hanc;
查看年薪高于20000的员工
select ename , sal*12 from emp_hanc where sal*12>20000;
查看公司有公有多少种职位
select distinct job from emp_hanc;
按部门号从小到大排列查看员工
select ename,deptno from emp_hanc   order by deptno ;
查看每个部门的最高,最低,平均工资,和工资总和
select max(sal),min(sal),avg(sal),sum(sal), deptno from emp_hanc group by deptno;
查看平均工资高于2000的部门的最低薪水
select min(sal) ,deptno from emp_hanc group by deptno having avg(sal)<2000;
查看在NEWYORK工作的员工
select e.ename ,d.loc from emp_hanc e , dept_hanc d where e.deptno = d.deptno and d.loc='NEW YORK';
select * from dept_hanc;
查看所有员工及所在部门信息,若该员工没有部门,则部门信息为null显示
select e.ename , d.dname from emp_hanc e join dept_hanc d on e.deptno = d.deptno;
select * from emp_hanc;
查看二Allen的上司是谁
select e.ename , m.ename from emp_hanc m join emp_hanc e on e.empno=m.mgr where m.ename='ALLEN' ;


视图
create view v_emp_10
as 
select empno , ename ,sal ,deptno
from emp_hanc
where deptno = 10;


select * from v_emp_10;
desc v_emp_10;


视图是数据库对象之一,数据库中所有数据对象
命名不能重复,所以通常视图的名字以v_开头.
视图并非真实存在的表,它只是对应一条select
语句的查询结果集.
drop view v_emp_avg_sal;
create view v_emp_avg_sal
as
select avg(sal) avg_sal,deptno
from emp_hanc 
group by deptno;
select * from v_emp_avg_sal;


查看高于自己所在部门平均工资的员工
select e.ename , e.sal,e.deptno
from emp_hanc e ,v_emp_avg_sal t
where e.deptno=t.deptno
and e.sal>t.avg_sal;


视图分为简单视图和复杂视图
简单视图:该视图对应的select语句不含有对数据做加工的操作,
例如函数表达式,分组等,且数据只来自单一的一张表.
复杂视图:除了简单视图的情况外都是复杂视图
简单视图可以进行DML操作,但是实际上对视图的DML操作,就是对该
视图数据来源的基础表的DML操作,而复杂视图不能进行DML操作


视图对应的子查询的字段若含有函数或
表达式必须制定别名.那么别名就是当前视图对应的名字
create or replace view v_emp_10
as
select empno id,ename name, sal salary ,deptno
from emp_hanc
where deptno = 10;


select * from v_emp_10;


对视图进行dml操作
insert into v_emp_10
(id,name,salary,deptno)
values 
(1001,'JACK',5000,10);


SELECT * FROM v_emp_10;
select * from emp_hanc;


修改视图数据
update v_emp_10
set salary = 8000
where id= 1001;


对视图的DML操作可能对基表数据进行污染,对视图进行的DML操作的数据影响了
基表对应的数据,但是视图对这些数据不可见


insert into v_emp_10
(id,name,salary,deptno)
values
(1002,'ROSE',3000,20);


SELECT * FROM v_emp_10;
select * from emp_hanc;


--索引
create index idx_emp_ename_hanc on emp_hanc(ename);


DROP INDEX idx_emp_ename_hanc;


create index idx_emp_job_sal_hanc on emp_hanc(job,sal);


SELECT ENAME ,EMPNO ,SAL,JOB from emp_hanc order by job desc,sal DESC;