#第五章 排序与分页#1.排序#如果没有使用排序操作，默认情况下查询返回的数据是按照添加数据的顺序显示的。SELECT*FROM employees;#1.1基本使用#使用 ORDER BY 对查询到的数据进行排序操作#升序：ASC （ascend）#降序：DESC (descend)#练习：按照salary从高到低的顺序显示员工信息SELECT employee_id,last_name,salary
FROM employees
ORDERBY salary DESC;#练习：按照salary从低到高的顺序显示员工信息SELECT employee_id,last_name,salary
FROM employees
ORDERBY salary ;#如果在ORDER BY 之后没有显式指名排序的方式的话，则默认按照升序排序。#2.我们可以使用列的别名，进行排序SELECT employee_id,salary,salary*12 annual_sal
FROM employees
ORDERBY annual_sal;#列的别名只能在ORDER BY中使用，不能在WHERE中使用SELECT employee_id,salary,salary *12 annual_sal
FROM employees
WHERE annual_sal >81600;#会报错#3.强调格式：WHERE需要声明在FROM后，ORDER BY之前SELECT employee_id,salary
FROM employees
WHERE department_id IN(50,60,70)ORDERBY department_id DESC;#4.二级排序#练习：显示员工信息，按照department_id的降序排列,salary的升序排列SELECT employee_id,salary,department_id
FROM employees
ORDERBY department_id DESC,salary ASC;#2.分页#2.1mysql使用limit实现数据的分页显示#需求1：每页显示20条记录，此时显示第一页SELECT employee_id,last_name
FROM employees
LIMIT0,20;#需求2：每页显示20条记录，显示第2页SELECT employee_id,last_name
FROM employees
LIMIT20,20;#需求3：每页显示20条记录，显示第3页SELECT employee_id,last_name
FROM employees
LIMIT40,20;#需求：每页显示pageSize条记录，此时显示配置第PageNo条#公式：LIMIT (pageNo - 1) * pageSize#2.2 WHERE ...ORDER BY... LIMIT声明顺序#LIMIT 的格式：严格来说：LIMIT位置偏移量，条目数#"LIMIT 0,条目数"等价于"LIMIT 条目数"SELECT employee_id,last_name,salary
FROM employees
WHERE salary >6000ORDERBY salary DESC#LIMIT 0,10;LIMIT10;#练习：表里有107条数据，我们只想要显示第32、33条数据SELECT employee_id,last_name
FROM employees
LIMIT31,2;#下标从0开始#2.3 MySQL8.0新特性：LiMIT...OFFSET...#练习：表里有107条数据，我们只想要显示第32、33条数据SELECT employee_id,last_name
FROM employees
LIMIT2OFFSET31;#练习：查询员工表中工资最高的员工信息SELECT employee_id,last_name,salary
FROM employees
ORDERBY salary DESC#limit 0,1;LIMIT1;#2,4 LIMIT 可以使用在MySQL，PGSQL，MariaDB，SQLite等数据库中使用，表示分页# 不能使用在SQL Server、DB2、Oracle！
练习
#1. 查询员工的姓名和部门号和年薪，按年薪降序,按姓名升序显示SELECT last_name,department_id, salary *12 annuan_sal
FROM employees
ORDERBY annuan_sal DESC,last_name ASC;#2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序，显示第21到40位置的数据SELECT last_name,salary
FROM employees
WHERE salary NOTBETWEEN8000AND17000ORDERBY salary DESCLIMIT20,20;#3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序SELECT*FROM employees
#where email like "%e%"WHERE email REGEXP'[e]'ORDERBY LENGTH(email)DESC,department_id ASC;
多表查询
内容
#多表查询#1.熟系常见的几个表DESC employees;DESC departments;DESC locations;#查询员工名为'Abel'的人在哪个城市工作SELECT*FROM employees
WHERE last_name ='Abel';SELECT*FROM departments
WHERE department_id =80;SELECT*FROM locations
WHERE location_id =2500;#2.出现笛卡尔积的错误#错误的原因：缺少了多表的链接条件#错误的实现方式：每个员工都与每个部门匹配了一遍。SELECT employee_id,department_name
FROM employees,departments;#查询出2889条记录#错误的方式SELECT employee_id,department_name
FROM employees CROSSJOIN departments;SELECT*FROM employees;#107条记录SELECT2889/107FROM DUAL;#27SELECT*FROM departments;#27条记录#3.多表查询的正确方式：需要有连接条件SELECT employee_id,department_name
FROM employees,departments
#两个表的连接条件WHERE employees.department_id = departments.department_id;#如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表SELECT employees.employee_id,departments.department_name,employees.department_id
FROM employees,departments
WHERE employees.department_id = departments.department_id;#建议：从sql优化的角度，建议多表查询时，每个字段前都指明其所在的表#5.可以给表起别名，在select和where中使用表的别名。SELECT emp.employee_id,dept.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.department_id = dept.department_id;#如果给表起了别名，一旦在select或where中使用表名的话，则必须使用表的别名，而不能再使用表的原名。#如下的错误SELECT emp.employee_id,dept.department_name,emp.department_id
FROM employees emp,departments dept#会报错WHERE emp.department_id = departments.department_id;#6.如果有n个表实现多表的查询，则需要至少n-1个连接条件#练习：查询员工的employee_id,last_name,department_name,citySELECT e.employee_id,e.last_name,d.department_name,l.city,e.department_id,l.location_id
FROM employees e,departments d,locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;#7.多表查询的分类/*角度1：等值连接 vs 非等值连接角度2：自连接 vs 非自连接角度3：内连接 vs 外连接*/#7.1等值连接 vs 非等值连接#非等值连接的例子：SELECT*FROM job_grades;#对应工资范围的等级SELECT last_name,salary,grade_level
FROM employees e, job_grades j
#where e.salary between j.lowest_sal and j.highest_sal;WHERE e.salary >= j.lowest_sal AND e.salary <= j.highest_sal;#7.2 自连接 vs 非自连接SELECT*FROM employees;#自连接的例子#练习：查询员工id，员工姓名及其管理者的id和姓名SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
FROM employees emp,employees mgr
WHERE emp.manager_id = mgr.employee_id;#7.3内连接 vs 外连接#内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行SELECT employee_id,department_name
FROM employees e,departments d
#只有106条记录WHERE e.department_id = d.department_id;#外连接：合并具有同一列的两个以上的表的行，结果集中除了包含一个表与另一个表不匹配的行# 还查询到了左表或右表中不匹配的行#外连接的分类：左外连接、右外连接、满外连接#左外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行，这种连接称为左外连接#右外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行，这种连接称为右外连接#练习：查询所有的员工的last_name，department_name信息SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id;#需要使用左外连接#SQL92语法实现内连接：见上，略#SQL92语法实现外连接：使用 + -------MySQL不支持SQL92语法中外连接的写法！SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id(+);#会报错#SQL99语法中使用JOIN...ON的方式实现多表的查询。这种方式也能解决外连接的问题，MySQL是支持这种方式的#SQL99语法如何实现多表的查询#SQL99语法实现内连接；SELECT last_name,department_name
FROM employees e INNERJOIN departments d
ON e.department_id = d.department_id;#inner可以省略SELECT last_name,department_name,city
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id;#SQL99语法实现外连接#练习：查询所有的员工的last_name，department_name信息#左外连接：SELECT last_name,department_name
FROM employees e LEFTOUTERJOIN departments d#OUTER可以省略ON e.department_id = d.department_id;#右外连接：SELECT last_name,department_name
FROM employees e RIGHTOUTERJOIN departments d#OUTER可以省略ON e.department_id = d.department_id;#满外连接：mysql 不支持 FULL OUTER JOINSELECT last_name,department_name
FROM employees e FULLOUTERJOIN departments d#OUTER可以省略ON e.department_id = d.department_id;#8.UNION和UNION ALL的使用#UNION：会执行去重操作#UNION ALL：不会执行去重操作#结论：如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据#则尽量使用 UNION ALL语句，以提高数据查询的效率。#9.7中JOIN的实现：#内连接 SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;#左上图：左外连接 SELECT employee_id,department_name
FROM employees e LEFTJOIN departments d
ON e.department_id = d.department_id;#右上图：右外连接 SELECT employee_id,department_name
FROM employees e RIGHTJOIN departments d
ON e.department_id = d.department_id;#左中图：SELECT employee_id,department_name
FROM employees e LEFTJOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id ISNULL;#右中图：SELECT employee_id,department_name
FROM employees e RIGHTJOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id ISNULL;#左下图：满外连接 #方式1：左上图 UNION ALL 右中图 SELECT employee_id,department_name
FROM employees e LEFTJOIN departments d
ON e.department_id = d.department_id
UNIONALLSELECT employee_id,department_name
FROM employees e RIGHTJOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id ISNULL;#方式2：左中图 UNION ALL 右上图SELECT employee_id,department_name
FROM employees e RIGHTJOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id ISNULLUNIONALLSELECT employee_id,department_name
FROM employees e RIGHTJOIN departments d
ON e.department_id = d.department_id;#右下图：左中图 UNION ALL 右中图SELECT employee_id,department_name
FROM employees e LEFTJOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id ISNULLUNIONALLSELECT employee_id,department_name
FROM employees e RIGHTJOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id ISNULL;#10.SQL99语法的新特性1：自然连接SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id
AND e.manager_id = d.manager_id;#NATURAL JOIN：它会帮你自动查询两张连接表中的所有相同的字段，然后进行等值连接SELECT employee_id,last_name,department_name
FROM employees NATURALJOIN departments;#11.SQL99语法的新特性2：USINGSELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING(department_id);#拓展：SELECT last_name,job_title,department_name
FROM employees INNERJOIN departments INNERJOIN jobs
ON employees.department_id = departments.department_id
AND employees.job_id = jobs.job_id;
练习
SELECT*FROM employees, departments;# 1.显示所有员工的姓名，部门号和部门名称。SELECT last_name,e.department_id,department_name
FROM employees e LEFTJOIN departments d #所有员工，外连接ON e.department_id = d.department_id;SELECT*FROM employees;SELECT*FROM departments;# 2.查询90号部门员工的job_id和90号部门的location_idSELECT job_id,location_id
FROM employees e JOIN departments d
#on e.department_id = 90;#错误ON e.department_id = d.department_id #不能少了连接条件 WHERE d.department_id =90;# 3.选择所有有奖金的员工的 last_name , department_name , location_id , citySELECT e.last_name,commission_pct,d.department_name,l.location_id,city
FROM employees e LEFTJOIN departments d
ON e.department_id = d.department_id
LEFTJOIN locations l
ON d.location_id = l.location_id
WHERE e.commission_pct ISNOTNULL;#添加一句WHERE，也应该是35条记录SELECT*FROM employees
WHERE commission_pct ISNOTNULL;#35条# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_nameSELECT last_name,job_id,e.department_id,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id #切记：不要忘了关联条件WHERE l.city ='Toronto';#切记，不要少了where#SQL92的语法SELECT last_name,job_id,e.department_id,department_name
FROM employees e,departments d,locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND l.city ='Toronto';SELECT*FROM departments;SELECT*FROM locations;SELECT*FROM employees;# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’SELECT department_name,street_address,last_name,job_id,salary
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id
AND d.department_name ='Executive';#where d.department_name = 'Executive';SELECT*FROM employees;# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employees Emp# manager Mgr#
kochhar 101 king 100SELECT emp.last_name "employees",emp.employee_id "Emp#",mgr.last_name "manager",mgr.employee_id "Mgr#"FROM employees emp LEFTJOIN employees mgr
ON emp.manager_id = mgr.employee_id;#不会写的题目：7,8,9# 7.查询哪些部门没有员工SELECT d.department_id
FROM departments d LEFTJOIN employees e
ON d.department_id = e.department_id
WHERE e.department_id ISNULL;#本题也可以使用子查询：暂时不讲# 8. 查询哪个城市没有部门SELECT l.location_id,l.city
FROM locations l LEFTJOIN departments d
ON l.location_id = d.location_id
WHERE d.location_id ISNULL;# 9. 查询部门名为 Sales 或 IT 的员工信息SELECT e.employee_id,e.last_name,d.department_id
FROM employees e JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_name IN('Sales','IT');
