数据库

一．分组（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) 分组依据可以有多个：

1. --查询每个部门中每个工种的最高薪资

select department_id,job_id,max(salary) from employees group by department_id,job_id;

d) 查询1997年每个月份入职员工的个数：

1. 查询1997年入职员工的个数：

       select count(*) from employees where to_char(hire_date,'yyyy')='1997';

1. 将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）

1. 平均薪资大于5000的部门有哪些。

select department_id,avg(salary) from employees

       group by department_id having avg(salary)>5000;

1. 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语句（子句），该子句一定会返回结果值。

1. 单值where子查询
   1. 查询last_name是Abel的员工的部门名称

select department_name from departments

where department_id=(select department_id from employees where last_name='Abel');

 

1. 多值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) 伪列的特点：

1. 不需要要程序员手动创建，oracle会自动创建
2. 如果不发起主动查询，我们是看不到伪列中的数据

b) rowid（了解）：可以作为表中每一行的唯一标识。rowid的值表示的实该行数据在磁盘上存储的物理空间。

1. 给表起别名：可以在表名之后跟一个表的别名。（给表起别名一定不能使用as）
2. rowid的作用：提高查询效率。

c) rownum：该伪列中存储的数据值是将当前查询结果进行编号（编号从1开始依次累加）。

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;

1. 总结：rownum只能做<或者<=的操作。
2. 如果使用rownum实现真正的翻页功能又该如何实现呢？

四．多表操作

a) 外键：能够体现出多表之间的唯一关联的那一列就是外键列。列中的数据就是外键。

1. 在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