MySQL学习笔记（第三部分）

3.3、逻辑运算符

• NOT或 ！、AND或&& 、OR或||
• 上面有举例场景，我这块就把语句重新打一遍即可

SELECT last_name,department_id
FROM employees
WHERE department_id = 10 OR department_id =20; #OR关键字可替换为||

SELECT last_name,department_id,salary
FROM employees
WHERE department_id = 60 AND salary > 8000;  #AND关键字可替换为&&

SELECT last_name,salary
FROM employees
WHERE salary NOT IN(1000);  #NOT关键字可替换为！

3.4、运算符的课后练习

#题目1:选择工资不在5000到12000的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 5000 AND 8000;

#题目二：选择在20或50号部门的员工姓名和部门号
SELECT last_name,department_id
FROM employees
WHERE department_id IN (20,50);

#题目三：选择公司中没有管理者的员工姓名和job_id
SELECT last_name,job_id,department_id
FROM employees
WHERE department_id IS NULL;

#题目四：选择公司中有奖金的员工姓名，工资和奖金级别
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

#题目五：选择员工姓名中第三个字母是a的员工姓名
SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';

#题目六：选择姓名中带有字母a和k的员工姓名
SELECT last_name
FROM employees
WHERE last_name RLIKE 'a' AND last_name RLIKE 'k';

#题目七：显示出表employees表中first_name 以'e'字符结尾的员工信息
SELECT first_name
FROM employees
WHERE first_name RLIKE 'e$';

#题目八：显示出表employees部门编号在80-100之间的姓名，工种
SELECT last_name,job_id,department_id
FROM employees
WHERE department_id BETWEEN 80 AND 100;

#题目九：显示出表employees的manager_id是100，101，110的员工姓名、工资、管理者id
SELECT last_name,salary,employee_id,manager_id
FROM employees
WHERE manager_id IN (100,101,110);

4、分页查询

4.1、排序数据

• 排序规则
  • 如果没有使用排序操作，默认情况下查询的数据是按照当时添加数据的顺序显示的
• 现在有一个需求：按照员工工资从高到低显示员工信息；写法如下：

SELECT salary,employee_id,last_name
FROM employees
ORDER BY salary;  #这块使用了ORDER BY关键字实现了用工资对员工信息进行排序，然后我们发现这是从低高，并不符合需求

SELECT salary,last_name,employee_id
FROM employees
ORDER BY salary DESC; #这块加上在ORDER BY关键字后加入关键字DESC后实现倒序

• 由此我们可得出ORDER BY 默认是正序，需要倒序的时候就在需要排序的字段后加入DESC即可完成操作

  • 我们也可以使用列的别名进行排序，写法如下：

  SELECT department_id,salary * 12 AS "annual_sal"
  FROM employees
  ORDER BY annual_sal; #此处就实现了用列的别名进行排序
  

• 当然实际场景一般都是需要将数据过滤出来，然后再进行排序，写法示例

SELECT department_id,salary AS "工资"
FROM employees
WHERE salary>12000
ORDER BY "工资";   #这块用了中文作为别名方便演示

• 也由此得出结论，WEHRE 的优先级是要大于ORDER BY的，先经过筛选，才能使用自定义的别名进行排序
• 但是现实场景是要比正常排序要复杂一些的，即使排序过后依旧会有某字段相同的情况，这个时候怎么决定谁先谁后呢，演示：

#需求：显示员工信息按照department_id的降序排列，再按照salary的升序排列
SELECT department_id ,salary
FROM employees
ORDER BY department_id DESC ,salary; #此处就使用了二级排序，即在指定字段中设置升降序即可

4.2、分页

• 分页的需求就是查询要返回的数据太多了，查看起来很不方便，这个时候就体现了分页的重要性，写法示范：

#分页：MySQL中使用LIMIT关键字来实现分页的功能
#需求：每页显示20条数据，此时显示第一页,这块的0代表的是默认数值即正序第一条数据
SELECT employee_id,last_name
FROM employees
LIMIT 0,20;
#需求二：每页查询20条数据，此时显示第二页
SELECT employee_id,last_name
FROM employees
LIMIT 20,20;
#需求三：每页查询20条数据，此时显示第三页
SELECT employee_id,last_name
FROM employees
LIMIT 40,20;

• 由此得出需求公式：每页显示pageSize条记录，此时显示第pageNum页：

• LIMIT (pageNum-1) * pageSize,pageSize;

• 关键字WHERE ORDER BY LIMIT声明顺序 ：

• 用一个案例来描述：写法如下：

SELECT employee_id,salary
FROM employees
WHERE salary>6000
LIMIT 0,10;   #即将查询到大于6000的数据进行过滤，将过滤好的数据进行分页，即每页查询10条数据，此时显示第一页

• 所以在实际应用中的应用场景往往是比较复杂一些的，需要自己去实际调整

• 场景练习：表里有107条数据记录，我们只想要显示第32、33条数据怎么办呢？

SELECT *
FROM employees
LIMIT 31,2;   #需要查询什么就写什么即可,需求中需要查询的是第32、33条记录，需要查的是32条，所以第一个参数偏移数是32-1需要显示的数据数是32、33条数据，所以后面的参数只需要写2即可实现该需求

4.3、分页的课后练习

#1、查询员工的姓名和部门号和年薪，按年薪降序，姓名升序显示
SELECT last_name,department_id,salary*12 AS "Annual salary"
FROM employees
ORDER BY `Annual salary` DESC ,last_name;

#2、选择工资不在8000到17000的员工的姓名和工资，工资按降序排序，并显示第21到40位置的数据
SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC
LIMIT 20,20;

#3、查询仓库中包含e的员工信息，先按邮箱的字节数降序，再按部门号升序
SELECT last_name,salary,department_id,email
FROM employees
WHERE email RLIKE 'e'
ORDER BY length(email) DESC , department_id;

5、多表查询

5.1、为什么需要多表查询

• 多表查询，也称为关联查询，指两个或更多个表一起完成查询操作
  • 前提条件：这些一起查询的表之间是有联系的（一对一、一对多），他们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键，比如：员工表和部门表，这两个表依靠“部门编号“进行关联
• 所以单表查询已经不能满足实际需求了，就需要进行多表查询；
• 这边通过案例去了解多表查询：案例：查询员工的姓名及其部门名称

#从案例中可以明确看出，需要查询的字段不只在一张表中，这个时候就需要进行多表查询了
#笛卡儿积错误的写法：这种多表查询的方法相当于使每一个员工与每个部门都匹配了一遍，就造成了一种数据重复的现象，显然这种方法是错误的
SELECT employee_id,department_name
FROM employees,departments;

• 这种错误的原因是什么呢？错误的原因是缺少了多表的连接条件
• 多表查询的正确方式：需要有连接条件，查询员工的姓名及其部门名称，写法如下：

SELECT employee_id,department_name
FROM employees,departments;
#两个表的连接条件
WHERE employees.department_id = departments.department_id; 
#由此可得出设置了连接条件后表与表之间就可以进行一个连接且不会出现笛卡尔积错误

• 还有一个实际场景：如果需要查询的字段在多张表种同时存在，如何解决，写法如下：

#如果从查询语句中出现了多张表都存在的字段，则必须指明此字段所在的表
SELECT employee_id,department_name,employees.department_id
FROM employees,departments
WHERE employees.department_id = departments.department_id;
#建议：从SQL优化角度，建议多表查询时，每个字段都指明其所在的表

SELECT departments.department_name,employees.employee_id,employees.department_id
FROM employees,departments
WHERE employees.department_id = departments.department_id;
#其结果是一样的，但是表一多起来就容易乱，维护起来也不容易，所以最好加上字段声明，这个字段来自于哪张表

• 我们在写多表查询的SQL语句中发现写的SQL语句很多，就导致可读性很差
• 解决方法：我们可以给表起别名在SELECT和WHERE关键字中使用表的别名，这样就能有效的增加SQL语句的可读性，写法：

SELECT ds.department_name,em.employee_id,em.department_id
FROM employees em,departments ds
WHERE em.department_id = ds.department_id;  #即给表修改了别名后SELECT和WHERE的语句中都可以用别名代替表名，增加可读性

• 需要注意的一个点是，如果给表起了别名，那么在SELECT和WHERE语句中就必须使用别名，否则就会报语法错误
  • 练习：查询员工的employee_id , last_name ,department_name ,city，写法如下：

SELECT emp.employee_id,emp.last_name,dep.department_name,loc.city
FROM employees emp,departments dep,locations loc
WHERE emp.department_id = dep.department_id
AND dep.location_id = loc.location_id; #表与表之间共同字段需要互相关联才不会出现笛卡尔积错误

• 结论：如果有n个表实现多表的查询，则至少需要n-1个连接条件