MySql基础（一）
SQL分类
sql语言在功能上主要分为如下3大类：
- DDL（Data Definition Languages、数据定义语言）*，这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。
- 主要语句包括
CREATE、DROP、ALTER等。
- 主要语句包括
- DML*（Data Manipulation Language、数据操作语言），用于添加、删除、更新和查询数据库记录(eg:表中的某行记录)，并检查数据完整性。
- 主要关键字包括
INSERT、DELETE、UPDATE、SELECT等（SELECT最重要）。
- 主要关键字包括
- DCL（Data Control Language、数据控制语言），用于定义数据库、表、字段、用户的访问权限和安全级别。
- 主要的语句关键字包括
GRANT、REVOKE、COMMIT、ROLLBACK、SAVEPOINT等。
- 主要的语句关键字包括
因为查询语句使用的非常的频繁，所以很多人把查询语句单拎出来一类：DQL（数据查询语言）。
还有单独将
COMMIT、ROLLBACK取出来称为TCL（Transaction Control Language，事务控制语言）。
SQL语言规则与规范
基本规则
- SQL 可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进。
- 每条命令以 ; 或 \g 或 \G 结束。
- 关键字不能被缩写也不能分行。
- 关于标点符号
- 必须保证所有的()、单引号、双引号是成对结束的。
- 必须使用英文状态下的半角输入方式。
- 字符串型和日期时间类型的数据可以使用单引号（’ ‘）表示。
- 列的别名，尽量使用双引号（” “），而且不建议省略as 。
SQL大小写规范
- MySQL 在 Windows 环境下是大小写不敏感的。
- MySQL 在 Linux 环境下是大小写敏感的。
- 数据库名、表名、表的别名、变量名是严格区分大小写的。
- 关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。
- 推荐采用统一的书写规范：
- 数据库名、表名、表别名、字段名、字段别名等都小写。
- SQL 关键字、函数名、绑定变量等都大写。
SQL注释
可以使用如下格式的注释结构
单行注释：#注释文字(MySQL特有的方式)
单行注释：– 注释文字(–后面必须包含一个空格。)
多行注释：/* 注释文字 */
命名规则
- 数据库、表名不得超过30个字符，变量名限制为29个
- 必须只能包含 A–Z, a–z, 0–9, _共63个字符
- 数据库名、表名、字段名等对象名中间不要包含空格
- 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
- 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`（着重号）引起来
- 保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据
- 类型在一个表里是整数，那在另一个表里可就别变成字符型了
eg：
#以下两句是一样的，不区分大小写
show databases;
SHOW DATABASES;
#创建表格
#create table student info(...); #表名错误，因为表名有空格
create table student_info(...);
#其中order使用``飘号，因为order和系统关键字或系统函数名等预定义标识符重名了
CREATE TABLE `order`(
id INT,
lname VARCHAR(20)
);
select id as "编号", `name` as "姓名" from t_stu; #起别名时，as都可以省略
select id as 编号, `name` as 姓名 from t_stu; #如果字段别名中没有空格，那么可以省略""
select id as 编 号, `name` as 姓 名 from t_stu; #错误，如果字段别名中有空格，那么不能省略""
SQL之SELECT
基本的SELECT语句
SELECT…
SELECT 1; #没有任何子句
SELECT 9/2; #没有任何子句
SELECT…FROM
语法：
SELECT 标识选择哪些列 FROM 标识从哪个表中选择选择全部列
SELECT * FROM 表名;一般情况下，除非需要使用表中所有的字段数据，最好不要使用通配符‘*’。使用通配符虽然可以节
省输入查询语句的时间，但是获取不需要的列数据通常会降低查询和所使用的应用程序的效率。通
配符的优势是，当不知道所需要的列的名称时，可以通过它获取它们。
在生产环境下，不推荐你直接使用 SELECT * 进行查询。
选择特定列
SELECT 列名1, 列名2 FROM 表名;
列的别名
语法
紧跟列名，也可以在列名和别名之间加入关键字AS，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。
SELECT 列名 AS 别名 FROM 表名SELECT 列名 "别名" FROM 表名
去除重复行
默认情况下，查询会返回全部行，包括重复行。在SELECT语句中使用关键字DISTINCT去除重复行。
SELECT DISTINCT [列名1], [列名2] FROM [表名];
这里有两点需要注意：
DISTINCT 需要放到所有列名的前面，如果写成 SELECT [列名1], DISTINCT [列名2] FROM [表名] 会报错。
DISTINCT 其实是对后面所有列名的组合进行去重。
空值参与运算
所有运算符或列值遇到null值，运算的结果都为null
在 MySQL 里面， 空值不等于空字符串。一个空字符串的长度是 0，而一个空值的长度是空。而且，在 MySQL 里面，空值是占用空间的。
查询常数
SELECT 查询还可以对常数进行查询。对的，就是在 SELECT 查询结果中增加一列固定的常数列。这列的取值是我们指定的，而不是从数据表中动态取出的。
你可能会问为什么我们还要对常数进行查询呢？
SQL 中的 SELECT 语法的确提供了这个功能，一般来说我们只从一个表中查询数据，通常不需要增加一个固定的常数列，但如果我们想整合不同的数据源，用常数列作为这个表的标记，就需要查询常数。
eg：我们想对 employees 数据表中的员工姓名进行查询，同时增加一列字段 corporation ，这个字段固定值为“xxx”，可以这样写:
SELECT 'xxx' as corporation, last_name FROM employees;
显示表结构
使用DESCRIBE 或 DESC 命令，表示表结构。
DESCRIBE 表名;
或
DESC 表名;
eg:
DESC student;
其中，各个字段的含义分别解释如下：
- Field：表示字段名称。
- Type：表示字段类型，这里 barcode、goodsname 是文本型的，price 是整数类型的。
- Null：表示该列是否可以存储NULL值。
- Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分；UNI表示该列是UNIQUE索引的一
- 部分；MUL表示在列中某个给定值允许出现多次。
- Default：表示该列是否有默认值，如果有，那么值是多少。
- Extra：表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等。
过滤数据
语法：
SELECT 字段1,字段2 FROM 表名 WHERE 过滤条件使用WHERE 子句，将不满足条件的行过滤掉
WHERE子句紧随FROM子句
排序
使用ORDER BY 字句排序
SELECT 列名1、列名2 FROM 表名 ORDER BY 列名3、列名4 [ASC]ASC（ascend)升序
DESC（descend）降序
ORDER BY 子句在SELECT语句的结尾
可以使用不在SELECT列表中的列排序。
在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第
一列数据中所有值都是唯一的，将不再对第二列进行排序。
分页
背景1：查询返回的记录太多了，查看起来很不方便，怎么样能够实现分页查询呢？
背景2：表里有 4 条数据，我们只想要显示第 2、3 条数据怎么办呢？
分页原理：所谓分页显示，就是将数据库中的结果集，按需求一段一段地显示出来。
MySQL****中使用 LIMIT 实现分页
格式：
LIMIT [位置偏移量,] 行数 #从表中第PageNo行开始显示PageSize条记录 SELECT * FROM table LIMIT(PageNo - 1)*PageSize,PageSize;- 注意：LIMIT 子句必须放在整个SELECT语句的最后！
第一个“位置偏移量”参数指示MySQL从哪一行开始显示，是一个可选参数，如果不指定“位置偏移量”，将会从表中的第一条记录开始（第一条记录的位置偏移量是0，第二条记录的位置偏移量是1，以此类推）；第二个参数“行数”指示返回的记录条数。
MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”，意思是获取从第5条记录开始后面的3条记录，和“LIMIT 4,3;”返回的结果相同。
小总结：
SELECT ...,...,...
FROM ...
WHERE ... AND/OR/NOT ...
ORDER BY ... (ASC/DESC),...
LIMIT ...,...
多表查询（重要）
多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。
前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。
等值连接VS非等值连接
等值连接
示例
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;1、省略多个表的连接条件或连接条件失效会产生笛卡尔积错误，为了避免笛卡尔积， 可以在WHERE 加入有效的连接条件。
2、对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或
表名）进行限定。
3、如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原有的表名，否则就会报错。
- 连接n个表，至少需要n-1个连接条件
非等值连接
示例
SELECT e.last_name, e.salary, j.grade_level FROM employees e, job_grades j WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;
自连接VS非自连接
自连接：当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两
个表再进行内连接，外连接等查询。
示例
SELECT CONCAT(worker.last_name ,' works for ' , manager.last_name) FROM employees worker, employees manager WHERE worker.manager_id = manager.employee_id;
非自连接：当table1和table2不是同一张表。
内连接VS外连接
内连接（INNER JOIN）: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行（交集）
基本语法
SELECT table1.column, table2.column,table3.column FROM table1 JOIN table2 ON table1 和 table2 的连接条件 JOIN table3 ON table2 和 table3 的连接条件 它的嵌套逻辑类似与FOR循环
- 可以使用ON字句指定额外的连接条件，这个连接条件和其他条件是分开的
- 关键字JOIN、INNER JONI、CROSS JOIN的含义是一样的，都表示内连接
外连接（OUTER JOIN）：两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
- 左外连接（LEFT OUTER JOIN）语法：
#实现查询结果是A SELECT 字段列表 FROM A表 LEFT JOIN B表 ON 关联条件 WHERE 等其他子句;- 右外连接（RIGHT OUTER JOIN）语法：
#实现查询结果是B SELECT 字段列表 FROM A表 RIGHT JOIN B表 ON 关联条件 WHERE 等其他子句;满外连接（FULL OUTER JOIN）
SELECT 字段列表 FROM A表 LEFT JOIN B表 ON 关联条件 WHERE 等其他子句; UNION ALL SELECT 字段列表 FROM A表 RIGHT JOIN B表 ON 关联条件 WHERE 等其他子句;满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT JOIN代替
1、LEFT JOIN 和 RIGHT JOIN 只存在于 SQL99 及以后的标准中，在 SQL92 中不存在，只能用 (+) 表示。
2、在 SQL92 中采用（+）代表从表所在的位置。即左或右外连接中，(+) 表示哪个是从表。
3、在 SQL92 中，只有左外连接和右外连接，没有满（或全）外连接。
4、Oracle 对 SQL92 支持较好，而 MySQL 则不支持 SQL92 的外连接。
UNION的使用
合并查询结果 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并
时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION
ALL关键字分隔。
语法格式：
SELECT column,... FROM table1 UNION [ALL] SELECT column,... FROM table2UNION操作符
- UNION 操作符返回两个查询的结果集的并集，去除重复记录。
UNION ALL操作符
- UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。
注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。
7种SQL JOINS的实现
示例：
#左中图：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#左下图：满外连接 # 左中图 + 右上图 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`;#右下图 #左中图 + 右中图 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
子查询（重要）
MySql中的函数
聚合函数作用于一组数据，并对一组数据返回一个值。
常用聚合函数
语法：
SELECT [column,] group function(column),... FROM table [WHERE condition] [GROUP BY column] [ORDER BY column];AVG(): 求平均值，可以对数值型数据使用。
SUM(): 求和，可以对数值型数据使用。
MAX() / MIN():求最大/小值，可以对任意数据类型的数据使用 MIN 和 MAX 函数。
COUNT(): COUNT(*)返回表中记录总数，适用于任意类型，COUNT(expr) 返回expr不为空的记录总数。
- 如果需要统计表中的记录数COUNT()、COUNT(1)、COUNT(具体字段)，建议使用COUNT()和COUNT（1）。
聚合函数不能嵌套使用。比如不能出现类似“AVG(SUM(字段名称))”形式的调用。
GROUP BY：将表中的数据分成若干组
SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column];在SELECT列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中。GROUP BY中声明的子句可以不出现在SELECT中。
GROUP BY 按照分组字段先后顺序依次分组。
GROUP BY 后面使用WITH ROLLUP 关键字之后，
在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。
注意：
当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。
HAVING: 过滤分组
SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group condition] [ORDER BY column];行已经被分组。
使用了聚合函数。
满足HAVING 子句中条件的分组将被显示。
HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。
- WHERE是先筛选数据再关联，执行效率高，但不能使用分组中的计算函数进行筛选。
- HAVING在最后的结果集中进行刷选，执行效率较低，可以使用分组中的计算函数。
- WHERE和HAVING也不是相互排斥的，我们可以在一个查询中同时使用WHERE和HAVING。包含分组统计函数用HAVING，普通条件用WHERE。
小总结：
SQL92语法：
SELECT ...,...,...(存在聚合函数)
FROM ...,...,...
WHERE 多表的连接条件
AND 不包含聚合函数的过滤条件
GROUP BY ...,...
HAVING 包含聚合函数的过滤条件
ORDER BY ...,...(ASC/DESC)
LIMIT ...,....
SQL99语法：
SELECT ...,...,...(存在聚合函数)
FROM ... (LEFT / RIGHT)JOIN ...
ON 多表的连接条件
(LEFT / RIGHT)JOIN ...
ON...
WHERE 不包含聚合函数的过滤条件
AND / OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含聚合函数的过滤条件
ORDER BY ...,...(ASC/DESC)
LIMIT ...,....
#其中：
#（1）from：从哪些表中筛选
#（2）on：关联多表查询时，去除笛卡尔积
#（3）where：从表中筛选的条件
#（4）group by：分组依据
#（5）having：在统计结果中再次筛选
#（6）order by：排序
#（7）limit：分页
#关键字顺序：
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...
#SELECT语句执行顺序：
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT
数值函数
基本函数
角度与弧度互换函数
三角函数
指数与对数
进制间的转换
字符串函数
注意：MySQL中，字符串的位置是从1开始的。
日期和时间函数
- 获取日期、时间
日期与时间戳的转换
获取月份、星期、星期数、天数等
日期的操作函数
时间和秒钟转换函数
计算日期和时间的函数
日期的格式化与解析
流程控制函数
流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。
MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。
加密与解密函数
加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取。这些函数在保证数据库安全时非常有用。在实际开发中数据安全问题在客户端就已经解决，该函数在数据库中用的较少。
MySql信息函数
MySQL中内置了一些可以查询MySQL信息的函数，这些函数主要用于帮助数据库开发或运维人员更好地对数据库进行维护工作。
其他函数
MySQL中有些函数无法对其进行具体的分类，但是这些函数在MySQL的开发和运维过程中也是不容忽视的。
子查询（重要）
子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。
SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集
合）进行比较。
子查询的基本语法结构
SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table);子查询（内查询）在主查询之前一次执行完成，子查询的结果被主查询（外查询）使用。
- 注意：1、子查询要包含在括号内 2、将子查询放在比较条件的右侧 3、单行操作符对应单行子查询，多行操作符对应多行子查询。
子查询的分类
我们按内查询的结果返回一条还是多条记录，将子查询分为 单行子查询 、 多行子查询 。
单行比较操作符：
多行比较操作符：
我们按内查询是否被执行多次，将子查询划分为 相关(或关联)子查询 和 不相关(或非关联)子查询 。
eg：
#查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id #方式一：相关子查询 SELECT last_name,salary,department_id FROM employees outer WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = outer.department_id); #方式二：from中使用子查询 SELECT last_name,salary,e1.department_id FROM employees e1,(SELECT department_id,AVG(salary) dept_avg_sal FROM employees GROUP BY department_id) e2 WHERE e1.`department_id` = e2.department_id AND e2.dept_avg_sal < e1.`salary`;
from型的子查询：子查询是作为from的一部分，子查询要用()引起来，并且要给这个子查询取别名， 把它当成一张“临时的虚拟的表”来使用。
EXISTS 与 NOT EXISTS关键字
关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
- 如果在子查询中不存在满足条件的行：条件返回FALSE,继续在子查询中查找。
- 如果在子查询中存在满足条件的行：不在子查询中查找,条件返回TRUE。
NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。
SQL之运算符
算数运算符
算术运算符主要用于数学运算，其可以连接运算符前后的两个数值或表达式，对数值或表达式进行加（+）、减（-）、乘（*）、除（/）和取模（%）运算。
一个整数类型的值对整数进行加法和减法操作，结果还是一个整数；
一个整数类型的值对浮点数进行加法和减法操作，结果是一个浮点数；
在Java中，+的左右两边如果有字符串，那么表示字符串的拼接。但是在MySQL中+只表示数值相加。如果遇到非数值类型，先尝试转成数值，如果转失败，就按0计算。（补充：MySQL中字符串拼接要使用字符串函数CONCAT()实现）
一个数乘以整数1和除以整数1后仍得原数；
一个数乘以浮点数1和除以浮点数1后变成浮点数，数值与原数相等；
一个数除以整数后，不管是否能除尽，结果都为一个浮点数；
一个数除以另一个数，除不尽时，结果为一个浮点数，并保留到小数点后4位；
乘法和除法的优先级相同，进行先乘后除操作与先除后乘操作，得出的结果相同。
在数学运算中，0不能用作除数，在MySQL中，一个数除以0为NULL。
比较运算符
比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果为假则返回0，其他情况则返回NULL。比较运算符经常被用来作为SELECT查询语句的条件来使用，返回符合条件的结果记录。
逻辑运算符
逻辑运算符主要用来判断表达式的真假，在MySQL中，逻辑运算符的返回结果为1、0或者NULL。 MySQL中支持4种逻辑运算符如下：
- 逻辑非（NOT或!）运算符表示当给定的值为0时返回1；当给定的值为非0值时返回0；当给定的值为NULL时，返回NULL。
- 逻辑与（AND或&&）运算符是当给定的所有值均为非0值，并且都不为NULL时，返回1；当给定的一个值或者多个值为0时则返回0；否则返回NULL。
- 逻辑或（OR或||）运算符是当给定的值都不为NULL，并且任何一个值为非0值时，则返回1，否则返回0；当一个值为NULL，并且另一个值为非0值时，返回1，否则返NULL；当两个值都为NULL时，返回NULL。
- 逻辑异或（XOR）运算符是当给定的值中任意一个值为NULL时，则返回NULL；如果两个非NULL的值都是0或者都不等于0时，则返回0；如果一个值为0，另一个值不为0时，则返回1。
位运算符
位运算符是在二进制数上进行计算的运算符。位运算符会先将操作数变成二进制数，然后进行位运算，最后将计算结果从二进制变回十进制数。
运算符优先级
正则表达式查询
正则表达式通常被用来检索或替换那些符合某个模式的文本内容，根据指定的匹配模式匹配文本中符合要求的特殊字符串。例如，从一个文本文件中提取电话号码，查找一篇文章中重复的单词或者替换用户输入的某些敏感词语等，这些地方都可以使用正则表达式。正则表达式强大而且灵活，可以应用于非常复杂的查询。MySQL中使用REGEXP关键字指定正则表达式的字符匹配模式。下表列出了REGEXP操作符中常用字符匹配列表：
