一、伪列
-
rowid:当前数据在数据库中的物理位置。
特点:不存在任何表中,能查出来。
代码案例:
- select employee_id,first_name,salary, rowid from employees
-
rownum:对满足条件的查询结果进行编号,从 1 开始。
案例1:查询员工表中员工信息,要求带有编号,从1开始。
- select employee_id,first_name,salary, rownum from employees
案例2:查询薪资大于10000的员工信息:工号、名字、薪资、序号(rownum)
- select employee_id,first_name,salary, rownum from employees
where salary > 10000;
案例3:查询员工表中,前10个员工的信息(工号,姓名,薪资,序号)
- select employee_id,first_name,salary, rownum from employees
where rownum <=10;
案例4:查询员工表中,6~10编号的员工信息。
- select employee_id,first_name,salary, rownum from employees
where ronum>=6 and rownum <=10; // 没有数据
注意:rownum只能进行 < <= =1 >=1 <=1的运算,不能进行 > >= = 运算
-
补充:表别名
SQL:select *,employee_id,rownum from employees; —>错误,不明确的 *的含义
解决方法1:使用 表名.*
- select employees.*,employee_id,rownum from employees
解决方法2:使用 表别名.*
- select es.*,employee_id,rownum from employees es
总结:表别名语法
- select … from 表名 别名
- 注意:中间没有 as 关键字
二、子查询
-
概念:一个查询语句执行过程中,用到另一个查询语句的执行结果,即一个select语句中
嵌套另一一个select语句,外面的select称为主查询,嵌套的称为子查询。
-
子查询结果是单个值(一行一列)
特点:将子查询的结果作为主查询的条件判断。
案例1:查询具有最高薪资的员工信息(工号、姓、工资)
思路:
-
查询员工的最高薪资:maxSalary
- select max(salary) from employees —>maxSalary
-
查询salary = maxSalary 的员工信息
- select employee_id,last_name,salary from employees
where salary = maxSalary
- select employee_id,last_name,salary from employees
-
合成SQL
- select employee_id,last_name,salary from employees
where salary = (select max(salary) from employees)
- select employee_id,last_name,salary from employees
案例2:查询工资大于所有员工平均工资的员工信息。
思路:
-
查询平均工资:avgSalary
- select avg(salary) from employees;
-
查询 salary > avgSalary 的员工
-
select employee_id,last_name,salary
from employees where salary > avgSalary
-
-
合成Sql
- select employee_id,last_name,salary from employees
where salary > (select avg(salary) from employees)
- select employee_id,last_name,salary from employees
-
-
子查询的结果是多个值(多行一列)
案例1:查询和姓 King的员工同部门的员工信息。
思路:
-
查询姓King的员工所在部门:det_id
-
select department_id from employees
where last_name = ‘King’
-
-
查询部门为 det_id 的员工信息;
- select employee_id,last_name,salary from employees
where department_id in det_id
- select employee_id,last_name,salary from employees
-
合成sql:
-
select employee_id,last_name,salary from employees
where department_id in
(select department_id from employeeswhere last_name = ‘King’)
-
-
-
子查询的结果是多行多列
特点:直接将子查询当成表进行再次查询。
案例:查询工资最高的前5名员工信息。
错误的写法:
-
select employees.*,rownum from employees
where rownum<=5 order by salary;
思路分析:
-
先按照工资降序排列:tab1
- select * from employees order by salary desc;
-
在tab1中查前5名员工:
- select * from tab1 where rownum <= 5;
-
合成SQL:
- select * from (select * from employees order by salary desc)
where rownum<=5;
- select * from (select * from employees order by salary desc)
可以给表起别名:
- select * from (select * from employees order by salary desc)tab1
where rownum<=5;
-
-
分页查询
关键点:
- 分页时,有排序,排序必须先行,构成虚表后进行2次查询
- 查询符合条件的前n行数据,并给rownum起别名 rn
- 查 rn >= m 的数据
案例:查询最高工资的第6~10名员工。
分析:
-
对所有的员工按照工资进行降序排列:tab1
- select * from employees order by salary desc; --tab1
-
查询符合条件的前 10行数数据,同时对 rownum起别名rn : tab2
-
select tab1.*,rownum rn from tab1
where rownum<=10 --tab2
-
-
在 tab2中查 rn>=6的数据
- select * from tab2 where tab2.rn>=6
-
合成SQL
-
select * from
(select tab1.*,rownum rn from
(select * from(select * from employees order by salary desc)
where rownum<=10)tab1 where rownum<=10)tab2
where tab2.rn>=6
-
三、表连接
-
概念:将多张表的数据合并成一张表,然后进行查询处理。
注意:表连接后,有冲突的字段时,需要 表名.字段名 或是 表别名.字段名 加以区分
-
内连接【重点】
**语法 : select … from 表1 [inner] join 表2 on 表1.字段名 = 表2.字段名 **
where … group by … having … order by …
特点:
- 查询结果:左表和右表基于连接条件,都符合/匹配的上的数据
- 必须有连接条件
- 左表和右表没有顺序要求
案例1:查询工号、姓、工资、员工所在部门的编号、所在部门名称。
思路:
-
表连接员工表和部门表为一张表,确定连接依据:员工表.部门的id = 部门表.部门的id
- employees e join departments d on e.department_id = d.department_id
-
对连接后的表进行查询即可:
-
select e.employee_id,e.last_name,e.salary,e.department_id,
d.department_name
from
employees e join departments d on e.department_id = d.department_id
-
-
外连接
分类:左外连接、右外连接、全外连接
左外连:【重点】
-
语法:select … from 表1 left [outer] join 表2 on 连接规则
-
特点:
- 以左表为主,左表和右表匹配上的数据+左表中没有匹配上的数据
- 左表中没有匹配上的数据在显示时,右表字段补空
-
案例:查询工号、姓、工资、员工所在部门的编号、所在部门名称
select e.employee_id,e.last_name,e.salary,e.department_id,d.department_name from employees e left join departments d on e.department_id = d.department_id
-
思路结果图:
右外连:
-
语法:select … from 表1 right [outer] join 表2 on 连接规则
-
特点:
- 以右表为主,左表和右表匹配上的数据+右表中没有匹配上的数据
- 右表中没有匹配上的数据在显示时,左表字段补空
-
案例:查询工号、姓、工资、员工所在部门的编号、所在部门名称
-
select e.employee_id,e.last_name,e.salary,e.department_id,
d.department_name
from
employees e right join departments don e.department_id = d.department_id
-
-
思路结果图:
全外连:
-
语法:select … from 表1 full [outer] join 表2 on 连接规则
-
特点:
- 左表数据和右表匹配上的数据+左表中没有匹配上的数据+右表中没有匹配上的数据
- 没有对应的匹配数据在显示时,补空
-
案例:查询工号、姓、工资、员工所在部门的编号、所在部门名称
- select e.employee_id,e.last_name,e.salary,e.department_id,d.department_name
from
employees e full join departments d on e.department_id = d.department_id
- select e.employee_id,e.last_name,e.salary,e.department_id,d.department_name
-
思路结果图:
-
-
多表连接
语法: select from 表1 join 表2 on 连接条件1 join 表3 on 连接条件2
案例:查询员工的工号、姓、工资、所在部门名称、所在街道地址。
思路:
- 先进行表连接:员工表(employees)+部门表(departments)+位置表(locations)
employees e join departments d on e.department_id=d.department_id
join locations l on d.location_id = l.location_id
- 对表连接之后进行查询
-
自连接
自连接:是特殊的表连接,通常采用内连接方式。
特殊在:参与表连接的2张或是多张表都是同一张表(自己)
案例1:查询员工的工号、名、工资、上级领导的工号、上级领导的名、上级领导的 工资。
-
select e1.employee_id,e1.first_name,e1.salary, e2.employee_id 领导id,e2.first_name 领导名,e2.salary from employees e1 join employees e2 on e1.manager_id = e2.employee_id
案例2:查询工资相同的员工的工号、名、工资。(两个人都查询这些信息)
-
select e1.employee_id,e1.first_name,e1.salary, e2.employee_id,e2.first_name,e2.salary from employees e1 join employees e2 on e1.salary = e2.salary where e1.employee_id < e2.employee_id
分析思路结果图:
-
四、SQL的分类
-
SQL:structure querey language,结构化的查询语言,用来操作管理所有数据库数据的命令。
-
SQL的分类:
DQL: Data Query Language,数据查询语言(select)
DML: Data Manipulation Language,数据从操作语言(insert/delete/update)
DDL: Data Definition Language,数据定义语言 (create/alter/drop)
DCL: Data Controller Language,数据控制语言(grant/revoke)–>和DBA数据库管理员相关
TCL: Transaction Controller Language,事务控制语言(commit/rollback)