test3
查询test1表中所有员工入职离现在几天:
select sysdate-t.date from test_1 t;
查询出明天此刻
select sysdate + 1 from dual;
查询test1表中所有员工入职离现在几月:
select (sysdate-t.date)/30 from test_1 t;
select months_between(sysdate,t.hiredate) from test_1;
查询test1表中所有员工入职离现在几年:
select (sysdate-t.date)/365 from test_1 t;
select months_between(sysdate,t.hiredate) /12 from test_1;
查询距离现在几周:
select round((sysdate-t.date)/7 ) from test_1 t;
计算两列加减:
select e.sal*12 + nvl(e.tim2, 0) from test e;
工资倒叙后每页显示五条, 查询第二页：
select * from（
select rownum rn, e.* from (
select * from emp order by sal desc) e where rownum<11) where rn>5 ;
通过视图创建表：
create table emp as select * from emp;
在指定列上创建视图:
create view emp11 as select ename, job，from emp；
create view emp12 as select ename, job，from emp with read only； -- 只读
select * from emp11;
修改视图:(增删改)
uodate emp11 set 'job'='clerk' where ename = '甘宁';
commit;
单列索引
create index idx_ename on emp(ename);
触发条件:
select * from emp where ename ='甘宁';
复合索引
create index idx_enamejob on emp(ename,job);
select * from emp where ename ='甘宁' and job ='xx'; --- >>包含优先检索列的原始值, 触发
序列:
create sequence s_person;
select s_person.nextval from dual;
在执行:
select s_person.currval from dual;
insert intoperson(pid,pname)values(s_person.nextval,'小明');
commit; / rollback; 看主键变化情况
select * from person;
触发器:
create or replace tigger t1
update
on emp
for each row
declare
for each row
declare
begin
if :old.sal>:new.sal then
raise_application_error(-20001, '条件不和');
end if;
end;
触发t1
update emp set sal=sal-1 where empno=7788;
commit;
select * from emp where empno = 7788;
create or replace trigger auid
before
insert
on person
for each row
declare
begin
select s_person.nextval into :new.pid from dual;
end;
使用auid 实现主键自增:
insert into person(pname) values('a');
commit;
insert into person values(1,'b');
select *from person;
存储过程:
create or replace procedure p1(eno emp.empno%type)
is
begin
update emp set sal=sal+100 where empno=eno;
commit;
end;
declare
begin
p1(7788);
end;
select * from emp where empno=7788;
select (sysdate-t.date)/365 from test_1 t;
create or replace function times(eno emp.empno%type) return date
is
s date；
begin
select (sysdate-t.date)/365 into s from test_1 t where empno=eno;
return s;
end;
declare
s date；
begin
s := times(7788);
dbms_output.put_line(s);
end;
分区:
create table sales(
product_id varchar2(5),sales_count number(10,2))
partition by range(sales_count)(
partition p1 values less than(1000), --- p1 <1000
partition p2 values less than(2000), 02 >=1000<2000
partition p3 values less than(3000));
插入数据:
insert into sales values('1',600);
insert into sales values('2',1000);
insert into sales values('3',2300);
insert into sales values('4',6000);
查看分区是否创建成功：select * from user_tab_partitions;
select * from user_tab_partitions u where u.table_name='SALES'
查看分区数据是否插入成功:
select * from sales partition(p1);
增加分区:
alter table sales add partition p4 values less than (maxvalue);
hash 分区:
create table my_emp(
empno number, ename varchar2(10))
partition by hash(empno)(partition p1,partition p2);
select * from user_tab_partitions u where u.table_name='my_emp' ;
insert into my_emp values(1,'A');
insert into my_emp values(2,'B');
insert into my_emp values(3,'C');
插入后查看分区
select * from my_emp partition(p2);
list 分区:
create table personCity(
id number, name varchar2(10), city varchar2(10))
partition by list(city)(
partition 东 values('开封','商丘'),
partition 南 values('洛阳'),partition 西 values('许昌'),partition 北 values('新乡'));
insert into personCity values(1,'A','开封');
insert into personCity values(2,'B','商丘');
insert into personCity values(3,'C','洛阳');
select * from personCity partition(东);
create table student(sno number ,sname varchar2(10))partition by range(sno)subpartition by hash(sname)
subpartitions 4(
partition p1 values less than(1000),partition p2 values less than(2000),partition p3 values less than(maxvalue));
复合分区:
range + hash
list + range
create table student(
stu_id number primary key, stu_name varchar2(10), grade varchar2(10))
partition by range(stu_id)(
partition par_stu1 values less than(1000),
partition par_stu2 values less than(2000),
partition par_stu3 values less than(maxvalue));
创建子表:
create table score (
id number primary key, stu_id number not null, couse_name varchar(20),score number,constraint fk_score foreign key(stu_id) references student(stu_id))
partition by reference(fk_score);
insert into student values(1,'AA','一年级');
insert into student values(22,'BB','三年级');
insert into score values(1,1,'语文',70);
insert into score values(2,22,'数学',80);
创建后查看分区
select * from user_tab_partitions u where u.table_name in('student','score') ;
间隔分区:
create table sale_detail(
sale_detail_id number, product_id number, quality number,sale_date date)
partition by range(sale_date)
interval(numtoyminterval(1,'MONTH'))
(partition p_201006 values less than(to_date('20100601','yyyymmdd')));
insert into sale_detail values(1,100,20,to_date('20100121','yyyymmdd'));
insert into sale_detail values(2,100,30,to_date('20110121','yyyymmdd'));
insert into sale_detail values(3,100,40,to_date('20100621','yyyymmdd'));
insert into sale_detail values(4,100,50,to_date('20111231','yyyymmdd'));
日期分区:
年份分区:
表达式:
select e.ename,
case e.ename
when''甘宁' then '曹操'
when '周泰'then'杨洋'
end
from emp e;
SQL> select e.sal,
2 case
3 when e.sal>3000 then '高收入'
4 when e.sal>1500 then'低收入'
5 else '低收入'
6 end
7 from emp e;
SQL> select e.ename,
2 decode(e.ename,
3 '甘宁', '艳阳',
4 '周泰', '心心',
5 '无名')
6 from emp e;
聚合函数:
select count(1) from emp;
select sum(date) from emp;
select min(date) from emp;
select avg(date) from emp;
分组:
select e. deptno, avg(e.sal) sal
from emp e
where e.sal>800
group by e.deptno
having avg(e.sal)>200;
创建同义词:
create synonym synl for scott.emp; --- >>私有同义词,只能自己访问
select * from synl
create public synonym synl for scott.emp; --->scott 大家都能访问
创建用户:
create user test1 identified by test1;
conn test1/test1;
grant select on dept to test1; -->授权用户访问基表
aa创建视图:
create view view2 as
select * from studen1 where sno=1; --- >>创建普通视图
create view view2 as
select * from studen1 where sno=3 with check option; -- ->>不允许update
create view view2 as
select * from studen1 where sno=3 with read only; --->>创建只读视图
创建视图并排序;
create view view2 as
select * from studen1 order desc sno; --->>创建只读视图
连接视图
/*4. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。*/
/*
列：编号、姓名、部门名称
表：emp、dept
条件：hiredate < 领导.hiredate
emp表需要查。部门名称在dept表中，所以也需要查。领导的hiredate需要查，这说明需要两个emp和一个dept连接查询
即三个表连接查询
*/
SELECT e.empno, e.ename, d.dname
FROM emp e LEFT JOIN emp m
ON e.mgr=m.empno
LEFT JOIN dept d ON e.deptno=d.deptno
WHERE e.hiredate<m.hiredate;
/*1. 查询出部门编号为30的所有员工*/
/*
分析：
1). 列：没有说明要查询的列，所以查询所有列
2). 表：只一张表，emp
3). 条件：部门编号为30，即deptno=30
*/
SELECT * FROM emp WHERE deptno=30;
/*2. 所有销售员的姓名、编号和部门编号。*/
/*
分析：
列：姓名ename、编号empno、部门编号deptno
表：emp
条件：所有销售员，即job='销售员'
*/
SELECT ename,empno,deptno FROM emp WHERE job='销售员'
/*3. 找出奖金高于工资的员工。*/
/*
分析：
列：所有列
表：emp
条件：奖金>工资，即comm>sal
*/
SELECT * FROM emp WHERE comm>sal;
/*4. 找出奖金高于工资60%的员工。*/
/*
分析：
列：所有列
表：emp
条件：奖金>工资*0.6，即comm>sal*0.6
*/
SELECT * FROM emp WHERE comm>sal*0.6;
/*5. 找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料。*/
/*
分析：
列：所有列
表：emp
条件：部门编号=10并且job为经理，和部门编号=20并且job为销售员
*/
SELECT * FROM emp WHERE (deptno=10 AND job='经理') OR (deptno=20 AND job='销售员');
/*6. 找出部门编号为10中所有经理，部门编号为20中所有销售员，还有即不是经理又不是销售员但其工资大或等于20000的所有员工详细资料。*/
/*
分析：
列：所有列
表：emp
条件：deptno=10 and job='经理', depnto=20 and job='销售员', job not in ('销售员','经理') and sal>=20000
*/
SELECT * FROM emp
WHERE
(deptno=10 AND job='经理')
OR (deptno=20 AND job='销售员')
OR job NOT IN ('经理','销售员') AND sal>=20000;
/*10.查询2000年入职的员工。*/
/*
分析：
列：所有
表：emp
条件：hiredate like '2000%'
*/
SELECT * FROM emp WHERE hiredate LIKE '2000%';
/*7. 有奖金的工种。*/
/*
列：工作(不能重复出现)
表：emp
条件：comm is not null
*/
SELECT DISTINCT job FROM emp WHERE comm IS NOT NULL;
/*8. 无奖金或奖金低于1000的员工。*/
/*
分析：
列：所有列
表：emp
条件：comm is null 或者 comm < 1000
*/
SELECT * FROM emp WHERE comm IS NULL OR comm < 1000;
/*9. 查询名字由三个字组成的员工。*/
/*
分析：
列：所有
表：emp
条件：ename like '___'
*/
SELECT * FROM emp WHERE ename LIKE '___'
/*10.查询2000年入职的员工。*/
/*
分析：
列：所有
表：emp
条件：hiredate like '2000%'
*/
SELECT * FROM emp WHERE hiredate LIKE '2000%';
/*11. 查询所有员工详细信息，用编号升序排序*/
/*
分析；
列：所有
表：emp
条件：无
排序：empno asc
*/
SELECT * FROM emp ORDER BY empno ASC;
/**********************************************/
/*12. 查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序*/
/*
分析：
列：所有
表：emp
条件：无
排序：sal desc, hiredate asc
*/
SELECT * FROM emp ORDER BY sal DESC, hiredate ASC
/*13. 查询每个部门的平均工资*/
/*
分析：
列：部门编号、平均工资(平均工资就是分组信息)
表：emp
条件：无
分组：每个部门，即使用部门分组，平均工资，使用avg()函数
*/
SELECT deptno, AVG(sal) FROM emp GROUP BY deptno;
/*14. 求出每个部门的雇员数量。*/
/*
分析：
列：部门编号、人员数量（人员数量即记录数，这是分组信息）
表：emp
条件：无
分组：每个部门是分组信息，人员数量，使用count()函数
*/
SELECT deptno, COUNT(1) FROM emp GROUP BY deptno;
/*
15. 查询每种工作的最高工资、最低工资、人数
列：部门、最高工资、最低工资、人数（其中最高工资、最低工资、人数，都是分组信息）
表：emp
条件：无
分组：每种工资是分组信息，最高工资使用max(sal)，最低工资使用min(sal)，人数使用count(*)
*/
SELECT job, MAX(sal), MIN(sal), COUNT(1) FROM emp GROUP BY job;
/*16. 显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于50000，输出结果按月工资的合计升序排列*/
/*
列：工作名称、工资和(分组信息)
表：emp
条件：无
分组：从事同一工作的工资和，即使用job分组
分组条件：工资合计>50000，这是分组条件，而不是where条件
排序：工资合计排序，即sum(sal) asc
*/
SELECT job,SUM(sal) FROM emp GROUP BY job HAVING SUM(sal)>50000 ORDER BY SUM(sal) ASC;
/*1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。*/
/*
列：部门编号、部门名称、部门位置、部门人数(分组)
列：dept、emp(部门人数没有员工表不行)
条件：没有
分组条件：人数>1
部门编号、部门名称、部门位置在dept表中都有，只有部门人数需要使用emp表，使用deptno来分组得到。
我们让dept和（emp的分组查询），这两张表进行连接查询
*/
SELECT
z.*,d.dname,d.loc
FROM dept d, (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno) z
WHERE z.deptno=d.deptno;
/*2. 列出薪金比关羽高的所有员工。*/
/*
列：所有
表：emp
条件：sal>关羽的sal，其中关羽的sal需要子查询
*/
SELECT *
FROM emp e
WHERE e.sal > (SELECT sal FROM emp WHERE ename='关羽')
/*3. 列出所有员工的姓名及其直接上级的姓名。*/
/*
列：员工名、领导名
表：emp、emp
条件：领导.empno=员工.mgr
emp表中存在自身关联，即empno和mgr的关系。
我们需要让emp和emp表连接查询。因为要求是查询所有员工的姓名，所以不能用内连接，因为曾阿牛是BOSS，没有上级，内连接是查询不到它的。
*/
SELECT e.ename, IFNULL(m.ename, 'BOSS') AS lead
FROM emp e LEFT JOIN emp m
ON e.mgr=m.empno;
/*4. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。*/
/*
列：编号、姓名、部门名称
表：emp、dept
条件：hiredate < 领导.hiredate
emp表需要查。部门名称在dept表中，所以也需要查。领导的hiredate需要查，这说明需要两个emp和一个dept连接查询
即三个表连接查询
*/
SELECT e.empno, e.ename, d.dname
FROM emp e LEFT JOIN emp m
ON e.mgr=m.empno
LEFT JOIN dept d ON e.deptno=d.deptno
WHERE e.hiredate<m.hiredate;
/*5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。*/
/*
列：员工表所有列、部门名称
表：emp, dept
要求列出没有员工的部门，这说明需要以部门表为主表使用外连接
*/
SELECT e.*, d.dname
FROM emp e RIGHT JOIN dept d
ON e.deptno=d.deptno;
/*6. 列出所有文员的姓名及其部门名称，部门的人数。*/
/*
列：姓名、部门名称、部门人数
表：emp emp dept
条件：job=文员
分组：emp以deptno得到部门人数
连接：emp连接emp分组，再连接dept
*/
select e.ename,d.dname,z.m
from emp e inner join (select deptno, count(*)m from emp group by deptno) z on z.deptno = e.deptno
inner join dept d on d.deptno = e.deptno
where e.job = '文员'
/*7. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。*/
/*
列：工作，该工作人数
表：emp
分组：使用job分组
分组条件：min(sal)>15000
*/
SELECT job, COUNT(*)
FROM emp e
GROUP BY job
HAVING MIN(sal) > 15000;
/*8. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。*/
/*
列：姓名
表：emp, dept
条件：所在部门名称为销售部，这需要通过部门名称查询为部门编号，作为条件
*/
SELECT e.ename
FROM emp e
WHERE e.deptno = (SELECT deptno FROM dept WHERE dname='销售部');
/*9. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。*/
/*
列：员工所有信息(员工表)，部门名称(部门表)，上级领导(员工表)，工资等级(等级表)
表：emp, dept, emp, salgrade
条件：sal>平均工资，子查询
所有员工，说明需要左外
*/
SELECT e.*, d.dname, m.ename, s.grade
FROM emp e
NATURAL LEFT JOIN dept d
LEFT JOIN emp m ON m.empno=e.mgr
LEFT JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
WHERE e.sal > (SELECT AVG(sal) FROM emp);
/*10.列出与庞统从事相同工作的所有员工及部门名称。*/
/*
列：员工表所有列，部门表名称
表：emp, dept
条件：job=庞统的工作，需要子查询，与部门表连接得到部门名称
*/
SELECT e.*, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno AND e.job=(SELECT job FROM emp WHERE ename='庞统');
/*11.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。*/
/*
列：姓名、薪金、部门名称(需要连接查询)
表：emp, dept
条件：sal > all(30部门薪金)，需要子查询
*/
SELECT e.ename, e.sal, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno AND sal > ALL(SELECT sal FROM emp WHERE deptno=30)
/**************************************************/
/*12.列出在每个部门工作的员工数量、平均工资。*/
/*
列：部门名称, 部门员工数，部门平均工资
表：emp, dept
分组：deptno
*/
SELECT d.dname, e.cnt, e.avgsal
FROM (SELECT deptno, COUNT(*) cnt, AVG(sal) avgsal FROM emp GROUP BY deptno) e, dept d
WHERE e.deptno=d.deptno;
