一.引入
1.SQL书写规范
每条命令以 “;“、”/g“、”/G” 结束。
关键字不能被缩写或分行。
字符串类型和日期时间类型的数据可以使用单引号(‘ ‘)表示。
列的别名，尽量使用双引号(“ “)。
大小写常用规范：
注意：MySQL在windows环境下是大小写不敏感的。
- 数据库名、表名、字段名等都采用小写。
- SQL关键字、函数名、绑定变量都采用大写。
2.SQL的分类
I.DDL语句
DDL（Data Definition Language)，即数据定义语言，用于定义数据库、表、试图、索引等数据库对象，以及创建、删除和修改数据库。
CREATE \ ALTER \ DROP \ RENAME \ TRUNCATE
II.DML语句
DML (Data Manipulation Language)，即数据操作语言，用于添加、删除、更新和查询数据库记录，并检查完整性。
INSERT \ DELETE \ UPDATE \ SELECT
III.DCL语句
DCL (Data Control Language)，即数据控制语言，用于定义数据库对象的访问权限和安全级别。
COMMIT \ ROLLBACK \ SAVEPOINT \ GRANT \ REVOKE
3.基础语句
I.导入
将已有的sql文件导入MySQL
source /*文件路径*/;
二.基础语句
一.Select语句
DUAL（伪表），本质是一个空表，用于实现语法的完整性，在不需要选取任何表时可以替代表来使语法完整。
1.无子句的SELECT
SELECT 1;
SELECT 9/2;
SELECT 2*3 FROM DUAL;
2.from语句
SELECT ... FROM ...
省略号分别表示 选择哪些列 和 从哪个表中选择 。
如：
SELECT employee_id,first_name FROM employees;
3.别名
列的别名有两种定义方法：
- AS （alias) 语句，即别名语句，通过 原名 AS 别名 来定义一个列名的别名。
- 列的别名也可以使用一对 “ “ 来引用起来，如 原名 “别名”。
- 若别名是一个整体单词（无空格等非法字符），则可省去双引号 “ “ 。
SELECT last_name AS ln FROM employees; -- 1
SELECT last_name "ln" FROM employees; -- 2
SELECT last_name ln FROM employees; -- 3
4.去除重复行
如在表employees中查询department_id
SELECT department_id FROM employees;
则去除重复的字段可以使用DISTINCT语句
SELECT DISTINCT department_id FROM employees;
若同时选择多列进行去重，则DBMS会同时满足多列的去重需求，因此不能保证任意一列完全去重。
5.null
空值NULL在表中显示为 (NULL) 。
SELECT employee_id,salary "lunar salary",salary * (1 + commission_pct) * 12 "annual salary" FROM employees;
则在Null参与运算时，其运算结果也为Null。
6.着重号
若数据库名、表名等与SQL的关键字一致，则需要加着重号 ( `) 。
SELECT * FROM `ORDER`
7.查询常数
SELECT 123,employee_id,last_name FROM employees;
则每个行在显示是会增添一列123。
8.显示表结构
DESCRIBE employees;
DESC employees;
一般显示六项内容：
FIELD -- 字段的名称
TYPE -- 字段的数据类型
NULL -- 该字段这一列是否存在空值NULL
KEY -- 关键字段
DEFAULT -- Default的值
EXTRA -- 额外的内容
9.过滤
SELECT * FROM employees WHERE department_id = 90;
WHERE关键字可以选取对应列的值，以增加过滤条件。
WHERE关键字类似其他程序语言中的if语句，可限制SELECT查询的内容。
MySQL的WHERE语句也存在大小写不敏感的性质。
二.运算符
1.算数运算符
同常见程序语言一致，SQL中的算术运算符有+ - * / %五种。
除也可以写作div，取余也可写作mod。
加法会自动将字符串转换为数值
SELECT 100 + '1' FROM DUAL; -- output:101
SELECT 100 + 'a' FROM DUAL; -- output:100
此时输出101，即100 + 1的结果，若无法转换则数值为0。
除法和取余计算：
- SQL会将除法结果自动转化为浮点型。
- 除数为0时输出Null
- 取模时结果的符号只与被取模数有关。
2.比较运算符
I.运算符
常见比较运算符包括：=、<=>、<>、<、>、<=、>=
其中 <> 也可以写作 != 。
比较运算符在比较时也存在字符串的隐式转换，转换失败则改为数值0。
若两边都为字符串时，则将字符串改为其ACSII码进行比较。
只要有Null参与判断，则返回Null
SELECT 1 = NULL,NULL=NULL FROM DUAL; -- output:Null,Null
因此不能使用 WHERE + 比较运算符”=” 筛选值为Null的行。
反之，若使用 安全等于(<=>) ，则可进行数值与Null的比较。
SELECT 1 <=> NULL,NULL <=> NULL FROM DUAL;-- output 0,1
II.判断语句
a).IS和IS NOT
SELECT employee_id FROM employees WHERE employee_id IS NOT NULL;
如上，IS NULL和IS NOT NULL也可用于比较Null而不返回Null值。
同理，可以用运算符达成同样的效果：
SELECT employee_id FROM employees WHERE NOT employee_id <=> NULL;
b).LEAST和GREATEST
LEAST和GREATEST可以用于比较一系列数据中的最小值和最大值。
SELECT LEAST(1,2,3,4,5) FROM DUAL; -- output:1
c).BETWEEN AND
BETWEEN ... AND ...用于查询数值是否在某个区间内部（包括边界值），且AND前的数值需小于AND后的数值。
SELECT last_name,salary FROM employees WHERE salary BETWEEN 6000 AND 8000;
也可以在BETWEEN ... AND ...前加上NOT来达到否的效果。
d).IN和NOT IN
判断数值是否是IN后集合中的元素。
SELECT last_name,department_id FROM employees WHERE salary NOT IN (6000,7000,8000);
e).模糊查询LIKE
- 下述SQL代码用于查询last_name中含有字母’a’的员工信息
SELECT last_name FROM employees WHERE last_name LIKE '%a%';
其中，%是一种特殊表达，用于表示若干个的字符来模糊筛选的条件，%a%表示a的左边和右边可能有0到多个字符，由此来实现字符串中含有a的效果。
- 下述SQL代码用于查询last_name的第二个字母为’a’的员工的信息
SELECT last_name FROM employees WHERE last_name LIKE '_a%'
下划线 _ 用于替代一个未知字符，若想打出字符 ‘_’ ，则加转义符，写成\_ 。
3.正则表达式
主要运用 REGEXP 和 RLIKE 来实现。
SELECT 'OuterCyrex' REGEXP '^Outer' REGEXP 'Cyrex$','OuterCyrex' REGEXP 'terCy' -- output:1,1,1
其中，^表示以…内容开头，$表示以…内容结尾。
三.逻辑运算符
主要是 NOT、AND、OR、XOR 等逻辑运算语句及其对应的运算符。
NOT即!，AND即&&，OR即||，还有XOR即异或运算。
四.位运算符
位运算符主要包括&、|、^、~、>>、<<，分别代表 按位与、按位或、按位异或、按位取反、按位右移、按位左移。
运算原理与C++的流式计算一致。
五.排序与分页
1.排序ORDER BY语句
若没有使用排序操作，则SELECT * FROM employees显示的顺序为将数据添加到表employees时的时间顺序。
若未声明排序顺序，则使用ORDER BY语句时自动采取升序操作。
即Default为DESC。
升降序分别为：
-
ASC(Ascend) 即升序排列
-
**DESC(Descend) **即降序排列
SELECT employee_id,salary,salary * 12 "annual salary" FROM employees ORDER BY "annual salary";
上述语句用于计算年薪并按年薪从高到低的降序排列来给结果的行排序。
列的别名只能在ORDER BY中使用，不能在WHERE中使用。
可以理解为WHERE语句是与SELECT语句为一体的，但SELECT语句也用于定义字段的别名，因此WHERE中的别名尚未结束定义，所以WHERE中不能使用同一语句中的别名。
SELECT employee_id,salary,department_id FROM employees ORDER BY department_id,salary ASC;
同时存在多个ORDER字段时，采用从先到后的优先顺序来进行排序。
以上方代码为例，即优先以department_id排序，若department_id相同时，再按照salary排序，并可以此类推。
2.分页limit语句
以例子展示：
SELECT employee_id,last_name FROM employees LIMIT 0,20;
此代码的运行结果会显示从表employees从第1行到第20行的employee_id列和last_name列的所有数据。
LIMIT /*偏移量*/ /*显示的列数*/
偏移量即显示的第一列的列序号，显示时是不显示偏移量对应的列的。
下列语句可实现分页效果。
LIMIT (pageNum - 1) * pageSize,pageSize;
若LIMIT的偏移量为0，则偏移量定义可以省略，即
LIMIT pageSize;
也可以写作
LIMIT /*显示的列数*/ OFFSET /*偏移量*/
注：仅支持MySQL 8.0
3.声明顺序
SELECT employee_id,last_name FROM employees ORDER BY salary DESC LIMIT 0,1;
-- output salary列数值最高的人的employee_id和last_name
由此可见ORDER BY的优先级高于LIMIT。
三.多表查询
一.引入
1.多表
通过建立多个表来去除冗余数据导致的大量无效空间被占用，以及便于归类和查询。但多个表的查询需要多表查询的语句。
2.笛卡尔积错误
笛卡尔积（又称交叉连接）错误，会导致大量重复无效数据。如在行数为n的表和行数为m的表中，同时查询第一个表的某一字段和第二个表中与其对应的字段，会导致查询出m*n条数据。
如：
SELECT employee_id,department_name FROM employees,departments;
SELECT employee_id,department_name FROM employees CROSS JOIN departments;
则输出时，每个employee_id将与departments表中的每一个department_name都对应一遍，导致出现m*n个字段。
二.多表查询
1.关联查询
关联查询，即通过增加连接条件，可以减少重复数据。
SELECT employee_id,department_name FROM employees,departments WHERE employees.`department_id` = departments.department_id;
WHERE语句限定了，只有当employees表中的department_id和departments表中的department_id相等时，才会将这一行输出。
通过限定对应表的相交部分的数据（即连接条件）相等，来限定输出字段的内容，防止笛卡尔积错误。
SELECT 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`;
上述语句实现了三个表的重命名，并用WHERE限制了输出。
其中的限制条件为
WHERE e.`department_id` = d.`department_id` AND d.`location_id` = l.`location_id`;
注：若有n个表需要多表查询，则需要至少n-1个连接条件。
2.Ambigous错误
当一个字段在多个表中都存在时，会导致Ambigous错误，即没有指明输出哪个表中的数据。
解决方法：
在字段前加入对应表的名称
SELECT employees.department_id FROM employees,departments;
此时即指明了输出的数据应为employees表中的department_id列的数据。
3.表的别名
给表起别名时，可以减少表输入时的字符输入量，便于输入。
SELECT emp.employee_id,dept.department_name,emp.department_id FROM employees emp,departments dept WHERE emp.`department_id` = dept.department_id;
上述语句给employees起了emp的别名，给departments起了dept的别名。
但注意，一旦给表起了别名，就必须在SELECT和WHERE中使用其别名，不能再使用其原名。
4.JOIN和UNION语法
I.JOIN语法
JOIN语句可让两个表基于某种连接条件进行多表查询， table_A JOIN table_B ON ... 可以理解为，将A表与B表按照ON后的连接条件进行连接（JOIN本身有连接的意思）。
如：
SELECT e.last_name,d.department_name FROM employees e JOIN departments d ON e.`department_id` = d.`department_id`;
上述语句实现了，将employees和departments表 按照 e.department_id = d.department_id 的连接条件进行多表查询。
在进行内连接时，INNER JOIN的INNER可省略。
在进行外连接是，已声明LEFT和RIGHT的OUTER JOIN语句，其OUTER也可省略。
II.UNION语法
UNION语句 将多个表的查询结果汇聚。
UNION将选取A和B-A的并集，UNION ALL将选取A和B的并集。
在求满外连接中，常使用UNION ALL将A和B-A联合 或 A-B和B联合。
5.多表查询的分类
多表查询常常分为 等值连接 和 非等值连接，或被分为 自连接 和 非自连接 ，以及 内连接 和 外连接。
I.等值连接和非等值连接
- 等值连接
等值连接 即上述已经举例过的代码。
SELECT 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`;
WHERE后的筛选条件为若干个 等于关系 。
- 非等值连接
SELECT e.last_name,e.salary,j.grade_level FROM employees e,job_grades j WHERE e.`salary` >= j.`lowest_sal` AND e.`salary` <= j.`highest_sal`;
上述的连接条件为非等值连接条件
II.自连接和非自连接
- 自连接
自连接 即在同一个表中互相有关联的字段的数据。
如，要查询员工及其管理者的id和姓名
SELECT employee_id,last_name,employee_id,last_name FROM employees emp,employees mgr WHERE emp.`manager_id` = mgr.`employee_id`;
这里将employees同时起别名为emp和mgr，然后通过在emp和mgr之间建立连接条件，来使员工信息和管理者信息一一对应。
- 非自连接
非自连接 即不同表之间建立连接条件并查询的方法。
III.内连接和外连接
1.内连接
内连接 (Inner Join)，即合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行。
即不满足连接条件的字段数据一律不输出（如连接条件在某表内存在数据为Null的行，则该数据无法被输出）。
上述所有举过例子的多表连接的实现都是内连接。
SELECT e.last_name,d.department_name FROM employees e INNER JOIN departments d ON e.`department_id` = d.`department_id`;
注：INNER可省略。
2.外连接
外连接 (Outer Join) ，即在内连接的基础上，加入了左表和右表中不匹配的行。
外连接又根据其添加的不匹配行的来源（来自左表还是右表），分为左外连接，右外连接，满外连接。
如，查询所有员工的last_name,department_name信息（包括Null）。
下述代码使用了 SQL99 的JOIN ... ON ...语法 实现
a).左外连接
SELECT last_name,department_name,city FROM employees e LEFT OUTER JOIN department d ON e.`department_id` = d.`department_id`
在employees表中加入department d中满足 e.department_id = d.department_id 的字段。
b).右外连接
SELECT last_name,department_name,city FROM employees e RIGHT OUTER JOIN department d ON e.`department_id` = d.`department_id`
c).满外连接
MySQL不支持 FULL OUTER JOIN 语法，所以实现满外连接时使用UNION ALL语句将A和B-A联合 或 A-B和B联合。
- 用A和B-A求A和B的满外连接
SELECT employee_id,department_name FROM employees e LEFT JOIN departments d ON e.`department_id` = d.`department_id`
UNION ALL
SELECT employee_id,department_name FROM employees e RIGHT JOIN departments d ON e.`department_id` = d.`department_id` WHERE e.`department_id` IS NULL;
A即A与B的左外连接，B-A即A与B的右外连接去掉A与B的内连接。
6.自然连接
自然连接是SQL99语法的特性。
NATURAL JOIN可以自动查询两张连接表中所有相同的字段，并进行等值连接。
SELECT employee_id,last_name,department_name FROM employees e NATURAL JOIN departments d;
上述语句等于
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`;
（假定employees表和departments表中同时存在相同的字段department_id和manager_id）
7.USING语句
JOIN ... ON ... 字段可以用JOIN ... USING ... 语句简写
- 给出一个
JOIN ... ON ...的连接语句：
SELECT employee_id,last_name,department_name FROM employees e JOIN departments d ON e.`department_id` = d.`department_id`;
- 可以使用
JOIN ... USING ...进行简化：
SELECT employee_id,last_name,department_name FROM employees e JOIN departments d USING department_id;
前提是employees和departments中共有department_id的字段。
