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 或 50SELECT * 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 = 80ORDER BY salary ASC;

查询 职位在it_prog或fi_account 的员工编号、员工姓名、年薪，同时按 年薪降序

//如果没有奖金率返回0.00SELECT commission_pct,IFNULL(commission_pct,0) FROM employees;//如果没有奖金率为空返回1SELECT commission_pct,ISNULL(commission_pct) FROM employees;//所以实现如下：SELECT employee_id,last_name,salary*12*(1+IFNULL(commission_pct,0)) 年薪FROM employeesWHERE 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; //107SELECT COUNT(*) FROM departments; //27SELECT 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_idFROM employees e,departments dWHERE e.department_id=d.department_id；

案例： 查询员工编号、员工当前工资、职位名称，职位的最高工资

SELECT employee_id,salary,job_title,max_salaryFROM employees e,jobs jWHERE e.job_id = j.job_id；

三表连接

案例：查询员工的姓名、部门名、部门所在城市

SELECT last_name,department_name,cityFROM employees e,departments d,locations lWHERE e.department_id = d.department_idAND 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_nameFROM departments dINNER JOIN employees eON e.`department_id` = d.`department_id`

2-2三表连接

n个表连接，需要n-1个连接条件

案例： 查询 员工姓名、部门名称、部门所在的城市,

SELECT last_name,department_name,cityFROM employees eINNER 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,cityFROM employees eINNER JOIN departments d ON e.`department_id`= d.department_id  INNER JOIN locations l ON d.location_id = l.location_idWHERE 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 dLEFT 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 dLEFT 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 eRIGHT OUTER JOIN departments d ON e.`department_id`=d.`department_id`