Mysql 基础

1、MySQL的编码设置
问题再现：命令行操作sql乱码问题
mysql> INSERT INTO t_stu VALUES(1,'张三','男');
ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'sname' at
row 1
解决办法：
修改mysql的数据目录下的my.ini配置文件，添加以下内容
[mysq] # 大概在63行左右，在其下添加
default-character-set=utf8 #默认字符集
[mysqld] # 大概在76行左右，在其下添加
...
character-set-server=utf8
collation-server=utf8_general_ci
2、列的别名有下面三种方式，推荐第三种“”的方式
SELECT last_name AS name, commission_pct comm, salary*12 "Annual Salary"
FROM employees;
3、只要有NULL参与判断，结果就是NULL
SELECT 1=NULL,NULL=NULL FROM DUAL;
<=>安全等于运算符为NULL而生
使用安全等于运算符<=>时，两边的操作数的值都为NULL时，返回的结果为1而不是NULL，其他
返回结果与等于运算符相同。

image.png
4、排序与分页
使用 ORDER BY 子句排序
ASC（ascend）: 升序
DESC（descend）:降序
ORDER BY 子句在SELECT语句的结尾。
多个排序条件
SELECT employee_id,last_name,department_id,salary
FROM employees
WHERE department_id IN(80,90,100)
ORDER BY department_id DESC,salary ASC;
MySQL中使用 LIMIT 实现分页。
MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”，意思是获取从第5条记录开始后面的3条记录，和MySQL 5.7版本中“LIMIT
4,3;”返回的结果相同。
5、多表查询
建议多表查询时，每个字段前都指明其所在表
SELECT employees.last_name, departments.department_name,employees.department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;
.拓展：表的别名
使用别名可以简化查询。
列名前使用表名前缀可以提高查询效率。
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e , departments d
WHERE e.department_id = d.department_id;
连接 n个表,至少需要n-1个连接条件。比如，连接三个表，至少需要两个连接条件。

#实现多表查询自连接
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;
#实现多表查询内连接
SELECT e.employee_id,d.`department_name`
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`;
#SQL99语法实现多表查询内连接
SELECT employee_id,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`;

UNION的使用
UNION 操作符返回两个查询的结果集的并集，去除重复记录。
UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。
注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据
不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

image.png
image.png

上图两个表，左侧员工信息表右侧部门表，员工信息表有117人有一人没有部门，部门表有22个部门，其中还116人分属6个部门有16个部门是空的。
内连接值得是两侧都有数据的是重叠的116条数据；
左外连接是以左表员工信息表为主表部门表为从表，其中一个人没有部门和116个有部门一共117条数据；
右外连接是以部门表为主表员工信息表为从表，其中16个部门是空和116个人分属6个部门一共122条数据。
满外连接是三种情况累加一共123条数据

#中图：内连接 A∩B
SELECT 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 LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;
#右上图：右外连接
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
#左中图：A - A∩B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
#右中图：B-A∩B
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL
#左下图
#左上图+ 右中图
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL #没有去重操作，效率高
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
#右下图
#左中图 + 右中图 A ∪B- A∩B 或者 (A - A∩B) ∪ （B - A∩B）
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把
自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段 ，然后进行 等值
连接 。
当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配
合JOIN一起使用。

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`;
# sql99用自然连接，效果和上述一样
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;
# sql99用USING 连接，效果和上述一样
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时， 保
证被关联的字段需要有索引。