数据库
一.分组(group by)
a) 查询每个部门中员工的个数:group by+分组依据。
select department_id,count(*) from employees group by department_id;
在employees表中的department_id有几种不同值,就会分成几组。
b) 查询每个部门中的平均薪资/最高薪资/最低薪资
select department_id,avg(salary) from employees group by department_id;
c) 分组依据可以有多个:
- --查询每个部门中每个工种的最高薪资
select department_id,job_id,max(salary) from employees group by department_id,job_id;
d) 查询1997年每个月份入职员工的个数:
- 查询1997年入职员工的个数:
select count(*) from employees where to_char(hire_date,'yyyy')='1997';
- 将1997年入职员工的个数按照月份进行分组:
select count(*),to_char(hire_date,'mm') from employees where to_char(hire_date,'yyyy')='1997'
group by to_char(hire_date,'mm');
e) 注意:
--1.只有在group by后面出现的列名,才可以放在select后面
--2.如果列名出现在了组函数中,则该组函数可以出现在select后面
--3.如果内置函数出现在了group by后面,则也可以出现在select后面
f) 分组之后的条件判断(having)
- 平均薪资大于5000的部门有哪些。
select department_id,avg(salary) from employees
group by department_id having avg(salary)>5000;
- having使用依据:如果在条件判断中,涉及到应用组函数的条件判断,则必须使用having。其他条件判断使用where
g) 查询1997年每个月入职人数大于2的月份及其具体员工个数
select to_char(hire_date,'mm'),count(*) from employees where to_char(hire_date,'yyyy')='1997'
group by to_char(hire_date,'mm')
having count(*)>2;
二.子查询:在一个sql语句中嵌套使用另外的sql语句
a) where子查询:在where后面嵌套使用另外一个sql语句(子句),该子句一定会返回结果值。
- 单值where子查询
- 查询last_name是Abel的员工的部门名称
select department_name from departments
where department_id=(select department_id from employees where last_name='Abel');
- 多值where子查询
select department_name from departments where department_id
in(select department_id from employees where last_name='King');
b) from子查询:
select * from employees;
select * from (select * from employees);
三.伪列:Oracle数据库中专有的特性
a) 伪列的特点:
- 不需要要程序员手动创建,oracle会自动创建
- 如果不发起主动查询,我们是看不到伪列中的数据
b) rowid(了解):可以作为表中每一行的唯一标识。rowid的值表示的实该行数据在磁盘上存储的物理空间。
- 给表起别名:可以在表名之后跟一个表的别名。(给表起别名一定不能使用as)
- rowid的作用:提高查询效率。
c) rownum:该伪列中存储的数据值是将当前查询结果进行编号(编号从1开始依次累加)。
- 如下几种情景使用rownum会导致查询结果为空:
将employees表中前5条数据查询出来
select rownum,e.* from employees e where rownum<=5;
--查询第二页的数据5-10条数据(无效因为rownum只能查询小于或者小于等于的)
select rownum,e.* from employees e where rownum>5 and rownum<=10;
--查询第10条数据(无效)
select rownum,e.* from employees e where rownum=10;
--查询非前10条数据
select rownum,e.* from employees e where rownum>10;
- 总结:rownum只能做<或者<=的操作。
- 如果使用rownum实现真正的翻页功能又该如何实现呢?
四.多表操作
a) 外键:能够体现出多表之间的唯一关联的那一列就是外键列。列中的数据就是外键。
- 在employees表和departments表中存在这一种一对多(一个部门可以对应多个员工)
b) 主表/父表:少(departments表)
c) 辅表/子表:多(employees表)
外键:一张表(主表)中的主键作为另一张(子表)表中的外键进行存储
d) 一张表中最多可以有几个(0-1)主键列和外键列(0-n)?
五.多表连接的语法
a) 内连接(inner join):只会连接两张表中可以连接的数据。
查询last_name为King这个员工的所有信息。
select e.*,d.* from employees e inner join departments d on e.department_id=d.department_id
where last_name='King';
b) 左外连接(left outer join):左边的表中的数据全部查询出来,右边表中能连接的连接出来,不能连接的补null;
select e.*,d.* from employees e
left outer join
departments d on
e.department_id=d.department_id;
c) 右外链接(right outer join):右边表中的数据全部查询出来,左表能连接的数据查询出来,连接不上的补null。
--右外链接
select e.*,d.* from employees e
right outer join
departments d on
e.department_id=d.department_id;
d) 自连接:
--将每个员工及其上级员工的姓名查询出来
select e.first_name,m.first_name from employees e
inner join employees m on e.employee_id=m.manager_id;
e) 多连接
employees --department_id-- departments
departments --location_id-- locations