SQL SELECT(复杂查询) 之 等值查询 内外连接

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`