SQL DML语言之 SELECT 复杂查询 |
---|
本文解析SQL的复杂查询包括:
一、模糊查询 二、排序查询
三、等值和非等值查询 四、内连接查询
以下举例所用表
employees表:字段如下
employee_id, first_name, last_name, email, phone_number
job_id, salary, commission_pct, manage_id, department_id
一、模糊查询
1、 like
<1> 通配符: % 任意多个字符(包含0个字符);
<2> _任意单个字符
案例:
/*
//查询 last_name 以 ‘k’开头的员工信息
SELECT * FROM employees WHERE last_name LIKE 'k%'
//查询 last_name 以 ‘King’开头的员工信息
SELECT * FROM employees WHERE last_name LIKE 'King%'
/*
//查询第五位为c的名字
SELECT * FROM employees
WHERE last_name LIKE '____c%';
//查询第五位或第三为为c的名字
SELECT * FROM employees
WHERE last_name LIKE '____c%' OR last_name LIKE '__c%';
//查询 姓名 中 第4个字符为_,第5个字符为c的信息
SELECT * FROM employees
WHERE last_name LIKE '___$_c%' ESCAPE '$';
2、 between and 查询条件为某个区间
语法:
between 值 and 值 在某个连续的区间,包含边界值
注意: 判断的类型 是 数值型(整数和小数)
案例:工资在8000-10000
SELECT * FROM employees
WHERE salary BETWEEN 8000 AND 10000;
//效果等同于
SELECT * FROM employees WHERE salary>=8000 AND salary<=10000;
注意:(两个区间值的位置不可写错)
错误的写法★★★
SELECT * FROM employees
WHERE salary BETWEEN 10000 AND 8000;
3、 in (值,值,值)
在某个 不连续的范围之内只要有一个满足即可
注: 值的类型 可以为任意类型
案例:
职位 是 IT_PROG, PU_MAN,SA_MAN的员工信息
SELECT * FROM employees WHERE job_id IN('IT_PROG','PU_MAN','SA_MAN');
//等效与如下:
SELECT * FROM employees
WHERE job_id='IT_PROG' OR job_id='PU_MAN' OR job_id='SA_MAN'
//查询字段depa_id 为30 或 50
SELECT * FROM employees
WHERE department_id IN(30,50);
4 is null
//查询奖金率 为null的姓名
SELECT last_name,commission_pct FROM employees
WHERE commission_pct IS NULL;
//查询奖金率 不为null的姓名
SELECT last_name,commission_pct FROM employees
WHERE NOT( commission_pct IS NULL);
或
SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NOT NULL;
二、排序查询
1、关键字: order by 排序 的字段名 [asc/desc]
注意:order by子句一般要放在查询语句最后,除了分页查询
2、可以按 字段名、表达式、别名排序
3、按指定的单个字段进行排序
语法:按工资升序
SELECT * FROM employees ORDER BY salary;-- 默认升序
SELECT * FROM employees ORDER BY salary ASC;-- asc 代表升序
SELECT * FROM employees ORDER BY salary DESC;-- desc 代表降序
示例:
查询 部门编号为80 的 工资、姓名 、部门编号,同时按 薪水 升序
SELECT salary,last_name,department_id
FROM employees
WHERE department_id = 80
ORDER BY salary ASC;
查询 职位在it_prog或fi_account 的员工编号、员工姓名、年薪,同时按 年薪降序
//如果没有奖金率返回0.00
SELECT commission_pct,IFNULL(commission_pct,0) FROM employees;
//如果没有奖金率为空返回1
SELECT commission_pct,ISNULL(commission_pct) FROM employees;
//所以实现如下:
SELECT employee_id,last_name,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
WHERE job_id IN('IT_PROT','FI_ACCOUNT')
ORDER BY 年薪 DESC;
4、多列排序
先按第一个字段排序,如果第一个字段的值相同,则按第二个字段排序,依次类推
SELECT * FROM employees ORDER BY salary ,last_name DESC;
三、等值查询和非等值查询
描述:
连接查询: 要查询的数据来自于多个表
1、笛卡尔集的出现
原因:
没有有效的连接条件,最终的结果集行数为 两个表的行数乘积
执行过程:
依次取第一个表的一条记录与第二个表的每一条记录产生关系
解决办法:添加有效的连接条件
# 案例:查询 员工姓名、部门名称(各在一个表)
SELECT last_name,department_name
FROM employees,departments
#查看每个表中的行数
SELECT COUNT(*) FROM employees; //107
SELECT COUNT(*) FROM departments; //27
SELECT COUNT(*) ,
FROM employees,departments //107*27
2.等值——非等值
2-1 等值连接
两表连接:
/*
语法:
select 列名,列名
from 表1 别名,表2 别名
where 表1的别名.字段=表2的别名.字段
注意:<1>为表加上别名为了更有效的区分,以及语法简单
<2>如果查询的字段为某两表共有则应该用表名.字段名标识
*/
案例:查询 员工姓名、部门名称
SELECT e.last_name,d.department_name,e.department_id
FROM employees e,departments d
WHERE e.department_id=d.department_id;
案例: 查询员工编号、员工当前工资、职位名称,职位的最高工资
SELECT employee_id,salary,job_title,max_salary
FROM employees e,jobs j
WHERE e.job_id = j.job_id;
三表连接
案例:查询员工的姓名、部门名、部门所在城市
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;
3、非等值连接
判断条件不是一个固定的值而是一个范围
案例: 查询员工的姓名、编号以及工资级别
/*
1、要查询的员工姓名、编号来自employees表
工资级别grade_level 来自于 job_grades表
如:
lowest_sal=1000 highest_sal=2999 级别为A
lowest_sal=3000 highest_sal=5999 级别为B
*/
SELECT last_name,employee_id,salary,grade_level
FROM employees e,job_grades g
WHERE e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
四、内连接——外连接
(一)、内连接
1、sql99 语法(sql1999 )
JOIN…ON
inner join 表 on条件 内连接(inner可以省略)
outer join 表 on 条件 外连接(outer可以省略)
left outer join 表 on 条件 左外连接
right outer join 表 on 条件 右外连接
2、内连特性
1.效果 等价于 等值连接
2.join左右两个表的位置没有顺序关系
3.为表起别名
4. 查询结果为 多表 关系列的值相等的,也就是匹配的结果
2-1两表连接
案例:查询 员工姓名、部门名称
SELECT last_name,department_name
FROM departments d
INNER JOIN employees e
ON e.`department_id` = d.`department_id`
2-2三表连接
n个表连接,需要n-1个连接条件
案例: 查询 员工姓名、部门名称、部门所在的城市,
SELECT last_name,department_name,city
FROM employees e
INNER JOIN departments d ON e.`department_id`= d.department_id
INNER JOIN locations l ON d.location_id = l.location_id
案例: 查询部门所在城市 中包含 ‘s’的 员工姓名、部门名称、部门所在的城市,
SELECT last_name,department_name,city
FROM employees e
INNER JOIN departments d ON e.`department_id`= d.department_id
INNER JOIN locations l ON d.location_id = l.location_id
WHERE city LIKE '%s%';
(二)外连接
/*
1.查询出 多个表中匹配的数据以及 不匹配的数据
作用:看匹配后主表对应的从表哪些不存在
A: 1 2 3 4, B: 3 4 5 6
外联:
左外 1 2 3 4 或 右外 3 4 5 6
全外连:1 2 3 4 5 6(mysql不支持)
2、左外:★★join 左边的表是主表,
查询的结果: 主表的全部=交集+主表有而从表没有(null)
3、右外:★★join 右边的表是主表,
查询的结果: 主表的全部=交集+主表有而从表没有(null)
4、左右两表换位置后影响效果(会改变主表)
full join on 全外连接(Mysql不支持)
*/
1、左外连接
案例:查询 员工编号,员工名,部门名
SELECT employee_id,last_name,department_name,d.`department_id`
FROM departments d
LEFT OUTER JOIN employees e ON e.`department_id`=d.`department_id`
SELECT * FROM departments;
/*
delete from employees where department_id = 30;*/
案例:使用左连接 哪个部门没有员工
SELECT employee_id,department_name,d.`department_id`
FROM departments d
LEFT OUTER JOIN employees e ON e.`department_id`=d.`department_id`
WHERE employee_id IS NULL;
2、右外连接
案例:查询 员工编号,员工名,部门名
//departments 为主表 主要看departments表与employees连接的
多余数据中对应employees的记录中哪些为空
SELECT employee_id,last_name,department_name,d.`department_id`
FROM employees e
RIGHT OUTER JOIN departments d ON e.`department_id`=d.`department_id`