MySQL学习笔记

本文是宋红康MySQL课程学习笔记，学习视频链接：MySQL数据库入门到大牛--宋红康

一、数据库概述与MySQL安装

第01章：数据库概述

1.1、为什么要用数据库？

• 持久化(persistence)：把数据保存到可掉电式存储设备中以供之后使用。大多数情况下，特别是企业级应用，数据持久化意味着将内存中的数据保存到硬盘上加以”固化”，而持久化的实现过程大多通过各种关系数据库来完成。
• 持久化的主要作用是将内存中的数据存储在关系型数据库中，当然也可以存储在磁盘文件、XML数据文件中。

1.2、数据库与数据库管理系统

• 数据库DB（Database）：即存储数据的“仓库”，其本质是一个文件系统。它保存了一系列有组织的数据。
• 数据库管理系统DBMS（DatabaseManagementSystem）：是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制。用户通过数据库管理系统访问数据库中表内的数据。如MySQL、Oracle等。
• 结构化查询语言SQL（StructuredQueryLanguage）：专门用来与数据库通信的语言。

DB和DBMS的关系：

数据库管理系统(DBMS)可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体用户的数据。

总之DBMS是程序，是软件，而DB里存的是数据，是文件。

1.3、常见的DBMS

目前互联网上常见的数据库管理软件有Oracle、MySQL、MSSQLServer、DB2、PostgreSQL、Access、Sybase、Informix这几种。本文要学习的就是MySQL，因为它：

1. 开放源代码，使用成本低。
2. 性能卓越，服务稳定。
3. 软件体积小，使用简单，并且易于维护。
4. 历史悠久，社区用户非常活跃，遇到问题可以寻求帮助。
5. 许多互联网公司在用，经过了时间的验证

另外，Oracle也是一个很常用的DBMS。是收费的，Oracle更适合大型跨国企业的使用，因为他们对费用不敏感，但是对性能要求以及安全性有更高的要求。

1.4、RDBMS和非RDBMS

RDBMS：就是关系型数据库。占据DBMS的绝对主流。

• 代表RDBMS：Oracle、MySQL和SQLServer等都是RDBMS。
• 实质：就是把复杂的数据结构归结为简单的二元关系，即二维表格形式。以行和列形式存储。
• 查询语言：SQL就是关系型数据库的查询语言
• 优势：复杂查询可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。事务支持使得对于安全性能很高的数据访问要求得以实现。

非RDBMS：非关系型数据库。可以用NoSQL泛指。

        实质：可看成传统关系型数据库的功能阉割版本，基于键值对存储数据，不需要经过SQL层的解析，性能非常高。同时，通过减少不常用的功能，进一步提高性能。

        NoSQL对SQL做出了很好的补充，比如实际开发中，有很多业务需求，其实并不需要完整的关系型数据库功能，非关系型数据库的功能就足够使用了。这种情况下，使用性能更高成本更低的非关系型数据库当然是更明智的选择。比如：日志收集、排行榜、定时器等。

常见的非RDBMS有如下几种：

• 键值型数据库：通过Key-Value键值的方式来存储数据。Redis最流行的键值型数据库。
• 文档型数据库：MongoDB是最流行的文档型数据库。此外，还有CouchDB等。
• 搜索引擎型数据库：典型产品Solr、Elasticsearch、Splunk等。
• 列式数据库：典型产品HBase等。
• 图形数据库：典型产品Neo4J、InfoGrid等。

1.5、DBMS的设计规则：

• 将数据放到表中，表再放到库中。
• 一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有唯一性。
• 表具有一些特性，这些特性定义了数据在表中如何存储，类似Java和Python中“类”的设计。

一个实体集（class）对应于数据库中的一个表（table），一个实体（instance）则对应于数据库表中的一行（row），也称为一条记录（record）。一个属性（attribute）对应于数据库表中的一列（column），也称为一个字段（field）。

表的关联关系有一对一关联、一对多关联、多对多关联、自我引用4种

第02章：MySQL环境搭建

2.1、卸载

1. 步骤1：停止MySQL服务。打开“任务管理器”对话框，可以在“服务”列表找到“MySQL8.0”的服务，如果现在“正在运行”状态，可以右键单击服务，选择“停止”选项停止MySQL8.0的服务
2. 步骤2：软件的卸载。
3. 步骤3：残余文件的清理。如果再次安装不成功，可以卸载后对残余文件进行清理后再安装。包括数据目录和服务目录。（数据目录默认是C:\ProgramData\MySQL）
4. 步骤4：清理注册表（选做）。如果前几步做了，再次安装还是失败，那么可以清理注册表。
5. 步骤5：删除环境变量配置。找到path环境变量，将其中关于mysql的环境变量删除，切记不要全部删除。（控制面板--系统和安全--系统--高级--环境变量）

2.2、安装

下载地址，官网：MySQL官网下载。完成安装与配置（主机类型、端口号、主机名mysql80、账号密码等）。

安装有两个路径可填写：

• 服务目录（默认C:\ProgramFiles\MySQL\MySQL Server 8.0），是程序是软件。
• 数据目录（默认C:\ProgramData\MySQL\MySQL Server 8.0\Data）。是数据是文件。

安装完后要添加环境变量：如果不配置MySQL环境变量，就不能在命令行直接输入MySQL登录命令。添加方法：

1. 步骤1：在桌面上右击【此电脑】图标，在弹出的快捷菜单中选择【属性】菜单命令。 
2. 步骤2：打开【系统】窗口，单击【高级系统设置】链接。 
3. 步骤3：打开【系统属性】对话框，选择【高级】选项卡，然后单击【环境变量】按钮。 
4. 步骤4：打开【环境变量】对话框，在系统变量列表中选择path变量。 
5. 步骤5：单击【编辑】按钮，在【编辑环境变量】对话框中，将MySQL应用程序的bin目录（C:\ProgramFiles\MySQL\MySQL Server 8.0\bin）添加到变量值中，用分号将其与其他路径分隔开。 (高级系统设置-高级-环境变量-系统变量-添加)
6. 步骤6：添加完成之后，单击【确定】按钮，这样就完成了配置path变量的操作，然后就可以直接输入MySQL命令来登录数据库了。

2.3、登录

服务启动与停止：可以使用图形界面工具启动停止。也可以用命令提示符启动和停止：

• 启动 MySQL 服务命令：net start MySQL服务名  (如mysql80)
• 停止 MySQL 服务命令：net stop MySQL服务名
• start和stop后面的服务名应与之前配置时指定的服务名一致。
• 如果当你输入命令后，提示“拒绝服务”，请以 系统管理员身份 打开命令提示符界面重新尝试。

登录与退出：

1、mysql自带客户端：开始菜单 → 所有程序 → MySQL → MySQL 8.0 Command Line Client  （这样可以直接输密码登录）

2、或者使用cmd窗口执行命令登录：

        格式：mysql -h 主机名 -P 端口号 -u 用户名 -p密码

        举例：mysql -h localhost -P 3306 -u root -ptest123

注意：

• -p与密码之间不能有空格，其他参数名与参数值之间可以有空格也可以没有空格
• 密码建议在下一行输入，这样可以不明文显示保证安全
• 客户端和服务器在同一台机器上，所以输入localhost或者IP地址127.0.0.1。同时，因为是连接本机： -hlocalhost就可以省略，如果端口号没有修改：-P3306也可以省略
• 如果命令行操作出现乱码，可能是编码设置的问题，需要手动设置成utf8。然后重启服务。（从MySQL 8.0开始，数据库的默认编码改为 utf8mb4 ，从而避免了乱码问题）

命令exit或quit退出登录

登录前查看版本信息

        c:\> mysql -V

        c:\> mysql --version

登录后查看版本信息

        mysql> select version();

2.3、MySQL的使用演示

• 在登录成功后，可以执行如下操作：
• 查看所有数据库  show databases;
• 显示某个表的结构  DESC 表名;  或者  DESCRIBE 表名;
• 创建新数据库  create database 数据库名;
• 使用新数据库  use 数据库名; （如果没有use语句，后面的操作也没有加数据库名的限定就会报错。使用了use语句，后面的操作不带数据库名就会默认是对当前这个数据库操作的）
• 查看某个库的所有表格  show tables from 数据库名;
• 创建新的表格：create table 表名(值列表);   如下图

#注意，最后一个字段不需要加逗号
create table 表名(
    字段名 数据类型,
    字段名 数据类型
);

• 查看一个表的数据  select * from 表名称;
• 添加一条表记录  insert into 表名称 values(值列表);
• 查看表的创建信息  show create table 表名称;
• 查看数据库的创建信息 show create database 数据库名;
• 删除表格  drop table 表名称;
• 删除数据库 drop database 数据库名;

注意：如果是5.7的版本，填入中文会报错，因为默认是Latin不支持中文，需要手动修改设置字符集为UTF8。

2.4、图形化管理工具

MySQL 图形化管理工具极大地方便了数据库的操作与管理，常用的图形化管理工具有： MySQL

Workbench 、 phpMyAdmin 、 Navicat Preminum 、 MySQLDumper 、 SQLyog 、 dbeaver 、 MySQL ODBC Connector。

2.5、MySQL目录结构与源码

目录结构：

MySQL 的目录结构	说明
bin 目录	所有 MySQL 的可执行文件。如： mysql.exe
MySQLInstanceConfig.exe	数据库的配置向导，在安装时出现的内容
data 目录	系统数据库所在的目录
my.ini 文件	MySQL 的主要配置文件
c:\ProgramData\MySQL\MySQL Server 8.0\data\	用户创建的数据库所在的目录

源码：

首先要进入 MySQL下载界面。 这里你不要选择用默认的“Microsoft Windows”，而是要通过下拉栏，找到“Source Code”，在下面的操作系统版本里面， 选择 Windows（Architecture Independent），然后点击下载。接下来，把下载下来的压缩文件解压，我们就得到了 MySQL 的源代码。MySQL 是用 C++ 开发而成的。

二、MySQL的查询语句SELECT

第*章：SELECT语句总结

1、语法汇总

SQL92语法整理：

SELECT 字段1,字段2      
        ----查询哪个属性的数据，可以对字段进行变化，可以通过别名显示。select的是什么，就显示什么。
FROM 表1,表2                                                      
        ----查哪几个表，可以起别名。
WHERE 多表的连接条件 AND/OR/NOT 不包含聚合函数的过滤条件        
        ----通过条件进行筛选
GROUP BY 字段1,字段2                     
        ----分组进行统计
HAVING 包含聚合函数的过滤条件              
        ----1.用HAVING的前提是必须有GROUP BY。2.当过滤条件中有聚合函数时，必须用HAVING。没有时，优先用WHERE，也可以用HAVING。
ORDER BY  字段1 ASC/DESC,字段2 ASC/DESC                  
        ----通过哪个属性升序/降序显示，默认是升序
LIMIT m,n;                                                      
        ----分页（8.0版本新增了一个OFFSET ---- LIMIT m,n 相当于LIMIT n OFFSET m）
        ----（显示从第m行开始的n条结果，注意不是从m行到n行。第一条是0，m默认值也是0）
        ----（公式：每页显示pageSize条记录，显示第pageNo页。LIMIT (pageNo-1)*pageSize,pageSize）

SQL99语法整理：

SELECT 字段1,字段2          
        ----查询哪个属性的数据，可以对字段进行变化，可以通过别名显示
FROM 表1 （LEFT/RIGHT）JOIN 表2 ON 多表的连接条件                                                                             
        ----FROM JOIN查哪两个表（去除迪卡尔积），有内连接、左外、右外等区别。也可以同一个表自己和自己连接。ON通过多表之间的连接关系筛选
（LEFT/RIGHT）JOIN ... ON ...
WHERE 不包含聚合函数的过滤条件             
        ----通过不包含聚合函数的条件进行筛选
GROUP BY 字段1,字段2                     
        ----分组进行统计
HAVING 包含聚合函数的过滤条件              
        ----1.用HAVING的前提是必须有GROUP BY。2.当过滤条件中有聚合函数时，必须用HAVING。没有时，优先用WHERE，也可以用HAVING。
ORDER BY  字段1 ASC/DESC,字段2 ASC/DESC            
        ----通过哪个属性升序/降序显示，默认是升序
LIMIT m,n;                                                     
        ----分页（8.0版本新增了一个OFFSET ---- LIMIT m,n 相当于LIMIT n OFFSET m）
        ----（显示从第m行开始的n条结果，注意不是从m行到n行。第一条是0，m默认值也是0）
        ----（公式：每页显示pageSize条记录，显示第pageNo页。LIMIT (pageNo-1)*pageSize,pageSize）

一个注意点：

• 当一个字段在两个表中都有时，一定要规定是哪个表的该字段，例如：

#如果表1表2都有同一个字段2的话，下面语句会报错
SELECT 字段1,字段2    
FROM 表1 JOIN 表2  
#要写成：
SELECT 表1.字段1,表2.字段2     
FROM 表1 JOIN 表2    
#太长了影响阅读的话，就利用表的别名：
SELECT a.字段1,b.字段2     
FROM 表1 a JOIN 表2 b

关于DUAL：

• DUAL是MySQL中的一个虚拟表，用于返回一个行。它只有一行一列。
• 常常在没有真实表的情况下用它来执行一些查询操作，用于测试和调试SQL语句。
• 当你SELECT的字段并不存在的时候就可以使用它。
• 总之，SELECT 字段1 FROM 表1    或    SELECT 任意值 FROM DUAL

2、关于别名和SQL执行过程

• SELECT可以对字段起别名，FROM可以对表起别名。字段和表都可以有多个别名。
• 如果FROM给表起了别名，那SELECT和WHERE的时候就必须用别名才行。
• WHERE筛选时可以使用表的别名，但不能使用字段的别名。
• 虽然WHERE不能对字段别名进行筛选，但ORDER BY可以对字段别名的数据排序。
• 因为实际执行的顺序是：FROM--WHERE--SELECT--ORDER BY，所以WHERE的时候字段还没有别名呢。

有时对同一个字段需要查询多次同时显示，那么SELECT的时候可以对同一个字段查询多次，给不同的别名进行区分就可以。例如SELECT 字段1 别名1 字段1 别名2

涉及自连接和一个字段多个别名的例题：

#查询所有工资比ABLE高的员工的姓名和工资。
SELECT e2.name,e2.salary
FROM employees e1,employees e2   --一个表起2个别名
WHERE e2.`salary` > e1.`salary`    --多表的连接条件，也可以自己和自己连接
AND e1.name = 'Able'

#这题也可以用子查询，但自连接的方法更好，很多DBMS的自连接速度比子查询快。

3、关于嵌套和子查询

• 单行函数可以嵌套其他函数，聚合函数不能嵌套其他函数，比如 MIN(AVG(salary))是错的，但是ROUND(AVG(xxx))是对的。（很正常，因为聚合函数的输入是多个值，而函数只输出一个值不能作为聚合函数的输入）。
• 如果子查询较简单，建议从外往里写。一旦子查询的结构较复杂，则建议从里往外写。
• 如果是相关子查询，通常从外往里写。（因为子查询会用到主查询的东西）
• 子查询嵌套不仅可以嵌套进WHERE和HAVING等筛选条件中，还可以当成一个表嵌套进FROM里。
• 实际上在查询中，除了GROUP BY分组和LIMIT分页之外，其他的所有位置（包括SELECT、FROM、WHERE、HAVING、ORDER BY）都可以声明子查询。
• 如果子查询时作为一个表嵌套进FROM里的时候，那么一定要给它起个别名。

例题：

查询平均工资最低的部门id
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (   --嵌套1，把查询最低工资部门的语句嵌套进HAVING进行筛选
                        SELECT MIN(avg_sal)
                        FROM (    --嵌套2，把查询每个部门平均工资的语句的结果当成一个表给FROM进行查询。
                            SELECT AVG(salary) avg_sal   --给字段一个别名，这样外面查询才能使用MIN()。如果直接MIN(AVG(salary))是错的
                            FROM employees
                            GROUP BY department_id
                            ) t_dept_avg_sal        --这里必须给表一个别名
                    );

4、关于分组GROUP BY中的SELECT

在下方的章节8.2分组中，提到了“在SELECT列表中所有未包含在组函数中的列都应该包含在 GROUP BY子句中”，这句话该怎么理解：

当你SELECT多个字段，而却只用GROUP BY通过一个字段来分组的时候，另一个字段的显示结果一定是不全的。比如说下面的例子，

#查询部门信息和工资信息：
SELECT department_id,salary
FROM employees
GROUP BY department_id

#通过部门id进行了分组，但salary既没有分组也没有在组函数内
#但每个部门都有很多员工，也有很多的工资信息。
#这样写的话，结果就是每个部门id一行数据，每行数据只显示了部门id和这个部门内其中一个员工的工资
#（哪一个员工的不一定，GROUP BY并不关心组内的顺序），所以这种显示一定没有实际意义

#所以正确情况就是根据实际需求给salary加上组函数
#比如MIN()求部门内工资最低的，AVG()求部门平均工资等等

#根据SELECT执行过程，是先GROUP BY分组再SELECT查询。
#也就是先将所有数据分组后，再对每个组内的数据进行组函数处理，将一组值变成一个值，显示出来才有意义
#重点在于处理的是“每个组内的数据”

总之：

1. 分组的语句中，SELECT的所有字段，要么在GROUP BY后面用来分组，要么在组函数内。
2. 如果查询的某个字段既不分组也不在组函数内，MySQL中可以执行但没有实际意义，而语法更严格的Oracle则会直接报错。
3. 语句执行顺序是GROUP BY--HAVING--SELECT，所以分组后无论是SELECT中还是HAVING中使用max、avg等组函数，都是分别对每个组内的数据的处理。如果想处理不同组之间的数据，要用别的办法。
4. 实际执行会发现，当select的字段是一一对应（比如id和姓名，通常一个id就只有一个名称，一个名称也只有一个id）的时候，如果只通过一个来分组，另一个即使不放进组函数也可以正常执行（因为他们一一对应，所以根据A分组也就相当于根据B分组了，也就有实际意义了）。如果是Oracle这种语法严格的，则可以直接通过全部字段分组来解决（反正一一对应，根据一个字段分组和根据多个字段分组结果是一样的）

举例：找出仅在春季卖出的产品id和名称

#方法1：在MySQL中可以这样写，因为id和name一一对应，name即使不分组也不放进组函数仍有有实际意义
#但这个写法在Oracle中会报错
SELECT P.product_id,P.product_name
FROM Product P JOIN Sales S
ON P.product_id = S.product_id
GROUP BY P.product_id
HAVING MAX(S.sale_date) <= '2019-03-31'
AND MIN(S.sale_date) >= '2019-01-01';

#方法2：必须把所有字段都放进分组中，这样写语法更严谨，MySQL中和Oracle中都不会报错
#而且因为id和name一一对应，所以这样分组和方法一那样分组，分组结果是一样的
SELECT P.product_id,P.product_name
FROM Product P JOIN Sales S
ON P.product_id = S.product_id
GROUP BY P.product_id,P.product_name
HAVING MAX(S.sale_date) <= '2019-03-31'
AND MIN(S.sale_date) >= '2019-01-01';

5、关于分组GROUP BY中的HAVING

• HAVING必须和GROUP BY一起使用。
• MySQL中单独用HAVING可以执行但无意义，因为逻辑上根本不通。但Oracle中则会直接报错。
• 聚合函数只能用在HAVING内，HAVING必须和GROUP BY同用，得出结论：必须在有分组的时候才能拿聚合函数当筛选条件。（当然只是说当筛选条件的情况，只对比HAVING和WHERE，不包括SELECT）

也就是说：

• WHERE COUNT()用法是错的
• 直接HAVING COUNT()用法是错的
• 分组后HAVING COUNT()是对的
• SELECT COUNT()也是对的

举例：找出没有重复出现的数字中最大的一个，如果没有不重复的就返回NULL

#方法1：错误，找出了最大值，但没有考虑是否重复出现
SELECT MAX(num) AS num
FROM MyNumbers

#方法2：语法错误直接报错，WHERE后面不能跟聚合函数
SELECT MAX(num) AS num
FROM MyNumbers
WHERE COUNT(*) = 1

#方法3：逻辑完全错误。它相当于是在判断num的个数是否等于1，个数等于1就返回最大值
#（因为只有一个值，也就是返回他自己），否则无返回值
#这也就是为什么HAVING必须和GROUP BY一起用，因为逻辑上根本不通，毫无意义
#这种写法在MySQL中可以执行但无意义，语法更严格的Oracle中会直接报错
SELECT MAX(num) AS num
FROM MyNumbers
HAVING COUNT(num) = 1

#方法4：错误，他把所有不重复的数字都找出来了
#max在这里没有起作用，因为每个组内本来就只有一个数
SELECT MAX(num) AS num
FROM MyNumbers
GROUP BY num
HAVING COUNT(*) = 1

#方法5：错误，没有考虑无重复值时返回null的情况
SELECT num
FROM MyNumbers
WHERE num = (
            SELECT num
            FROM MyNumbers
            GROUP BY num
            HAVING COUNT(*) = 1
            ORDER BY num DESC
            LINIT 1
            );

#方法6：正确。通过select max()处理null值
#select 字段名 from 空，输出为空。  select 空字段名，输出为null
SELECT max(num) AS num
FROM (
        SELECT num
        FROM MyNumbers
        GROUP BY num
        HAVING COUNT(num) = 1
      ) AS t;

#或者
SELECT max(num) AS num
FROM MyNumbers
WHERE num in (
            SELECT num
            FROM MyNumbers
            GROUP BY num
            HAVING COUNT(*) = 1
            );

6、关于NULL值

有NULL值的时候，很多运算符是取不到NULL值的。

比如：找出所有id不为2的值。如果写WHERE ID !=2，虽然NULL值不等于2，但它也被排除在外了。应该根据实际是否需要null值的情况，写成WHERE ID !=2 OR id IS NULL，或者也可以写成WHERE NOT referee_id <=> 2

同样的例子，找出奖金小于1000的也要写成 WHERE bonus < 1000 OR bonus is null

总之，对于NULL值的判断，要用专门用于检查字段是否为NULL的IS NULL 和IS NOT NULL这两个操作父符，或者用<=>

7、经典例题

题目：找出平均工资最低的部门信息

#方法1：三层嵌套，三层子查询。找出各个部门平均工资->找出最低的平均工资->找出最低工资的部门id->找出该部门信息
SELECT *
FROM departments
WHERE department_id = (
                    SELECT department_id
                    FROM employees
                    GROUP BY departmen_id
                    HAVING AVG(salary) = (
                                            SELECT MIN(avg_sal)
                                            FROM (
                                                   SELECT AVG(salary) avg_sal
                                                   FROM employees
                                                   GROUP BY department_id
                                                 ) t_dept_avg_sal   #嵌套FROM必须起别名
                                         )
                      );


#方式2：对方式1的优化，通过<=ALL()的方式找出最小的值，减少一层嵌套
SELECT *
FROM departments
WHERE department_id = (
                        SELECT department_id
                        FROM employees
                        GROUP BY departmen_id
                        HAVING AVG(salary) <= ALL(
                                                   SELECT AVG(salary) 
                                                   FROM employees
                                                   GROUP BY department_id
                                                 )
                      );

#方式3：对方式1的优化，通过ORDER BY升序排序然后LIMIT取第一个值来找到最小值，减少一层嵌套
SELECT *
FROM departments
WHERE department_id = (
                        SELECT department_id
                        FROM employees
                        GROUP BY departmen_id
                        HAVING AVG(salary) = (
                                              SELECT AVG(salary) avg_sal
                                              FROM employees
                                              GROUP BY department_id
                                              ORDER BY avg_sal ASC
                                              LIMIT 1
                                              )
                      );

#方式4：一个SELECT子查询同时查出最低工资和其部门id，然后整个输出当一个表嵌套进FROM，和另一个表进行多表连接
SELECT d.*
FROM departments d,(
                    SELECT department_id,AVG(salary) avg_sal
                    FROM employees
                    GROUP BY department_id
                    ORDER BY avg_sal ASC
                    LIMIT 0,1
                   ) t_dept_avg_sal
WHERE d.`department_id` = t_dept_avg_sal.department_id

8、其他的一些总结

• 日期是可以比大小的，>'2019-03-31' 就表示时间晚于这个日期的，也可以用MIN() MAX()等函数找出最早、最晚的
• 起别名的时候建议不要省略AS，尽量使用引号。但引用别名的时候不能带引号。

第03章：基本的SELECT语句

3.1、SQL概述

SQL（结构化查询语言）：是使用关系模型的数据库语言。有SQL-92、SQL99等多种标准。像MySQL、Oracle等DBMS都使用SQL这种语言，但是又都有自己的特有内容。

SQL语言在功能上分为几类：

• DDL数据定义语言：这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以创建、删除、修改数据库和数据表结构。比如CREATE、DROP、ALTER等
• DML数据操作语言：用于添加、删除、更新和查询数据库记录，并检查数据完整性。比如INSERT、DELETE、UPDATE、SELECT等。其中SELECT最重要。
• DCL数据控制语言：用于定义数据库、表、字段、用户的访问权限和安全级别。比如GRANT、REVOKE、COMMIT、ROLLBACK、SAVEPOINT等。

3.2、SQL语言规范

1、基本规则

• SQL可以写一行或多行。分行写再加上缩进可提高可读性。
• 每条命令以;或\g或\G结束
• 关键字不能缩写不能分行
• 所有的()、单引号、双引号都要成对，必须英文状态半角输入
• 字符串和日期时间可以使用单引号('')表示
• 列的别名，尽量使用双引号("")，而且不建议省略as

2、大小写和注释

• MySQL在Windows下大小写不敏感，在linux下大小写敏感。
• linux下：数据库名、表名、表别名、变量名严格区分大小写。关键字、函数名、列名（字段名）、列的别名忽略大小写。
• 推荐采用统一的标准：数据库名、表名、表别名、字段名、字段别名都小写。SQL关键字、函数名、绑定变量都大写。
• 单行注释：#注释文字
• 单行注释：-- 注释文字 （--后面必须包含一个空格）
• 多行注释：/* 注释文字 */

3、命名规则

• 数据库、表名不得超过30个字符，变量名限制为29个
• 必须只能包含A-Z，a-z，0-9，_共63个字符
• 数据库名、表名、字段名等对象名中间不要包含空格
• 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
• 必须保证字段没有和保留字、数据库系统或常用方法冲突。如果坚持要用，需要使用着重号``引起来
• 保持字段名和类型一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了。

4、数据导入指令

登录MySQL后，使用source命令导入。例如 source d:\mysqldb.sql

3.3、基本SELECT语句

• SELECT 1;   -- 没有任何子句，他表示将1这个字段放到查出的表里
• SELECT 9/2; --没有任何子句，他表示将9/2这个算式的结果作为字段放到查出的表里
• SELECT 字段1,字段2 FROM 表1;  --表示从表1中查询出特定两个字段的数据
• SELECT * FROM 表1; --表示从表1中查询出全部的字段数据。不推荐，因为会查询出不需要的列数据降低效率
• SELECT DISTINCT 字段1 FROM 表1; --表示查询表1的字段1数据，并去掉重复行后显示。如果有多个字段，DISTINCT关键字必须在所有字段前面，DISTINCT会将所有字段的组合进行去重
• 空值参与运算：所有运算符或列值遇到null值，运算结果都为null。空值不等于空字符串。空字符串长度0，空值长度时空。空值在MySQL里是占用空间的。
• 着重号：必须保证字段没有和保留字、数据库系统或常用方法冲突。如果坚持要用，需要使用着重号``引起来。例如：

  SELECT * FROM `ORDER`;  #这个表名和SQL自带的方法ORDER重名了

• DESCRIBE 表1;  或  DESC 表1; --表示查询表结构。查询结果中字段含义：
  • Field：字段（列）名称
  • Type：表示字段类型
  • Null：表示该列是否可以存储Null值
  • Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分，UNI表示该列是UNIQUE索引的一部分，MUL表示在列中某个给定值允许出现多次
  • Default：表示该列是否有默认值，以及默认值是多少
  • Extra：表示可获取的与给定列有关的附加信息，例如AUTO_INCREMENT等

列的别名：

• 作用是给列（字段）重命名以便于计算
• 可以用关键字AS，也可以不用。（as的全称alias）
• 别名可以使用双引号以便别名中用空格或特殊字符
• 别名建议简短，要见名知意
• 举例1：

  SELECT last_name AS name,commission_pct comm
  FROM employees;

• 举例2：(下面例子中将月薪的值乘以12后的值作为新字段并起别名“年薪”)

  SELECT last_name "Name",salary*12 "Annual Salary"
  FROM employees;

3.4、过滤数据

SELECT 字段1,字段2 FROM 表1 WHERE 过滤条件;  --表示查询字段，将不满足条件的行过滤掉

WHERE子句要紧随FROM子句

第04章：运算符

4.1、算术运算符

算术运算符用于数学运算，可以计算两个数值或表达式的结果。有以下5种：

运算符	名称	作用	示例
+	加	计算和	SELECE A + B
-	减	计算差	SELECT A - B
*	乘	计算乘积	SELECT A * B
/或DIV	除	计算商	SELECT A / B或SELECT A DIV B
%或MOD	求模（求余）	计算余数	SELECT A % B或 SELECT A MOD B

        （实际做题发现：MOD(a,b)求余数比a%b求余数效率高很多）

加减法运算符：

• 两个整个相加减，结果还是整数
• 整数和浮点数相加减，结果是浮点数
• 加减法优先级相同
• +只表示数值相加，不像其他一些语言可以表示字符串拼接。如果+号前后是非数值的类型，就先尝试转换成数值，转失败了就按0计算 （MySQL的拼接是用concat()）

乘除法运算符：

• 一个数乘除整数1仍得整数。一个数乘除浮点数1后变成浮点数，值相等
• 一个数除以整数，不管是否能除尽，结果都是浮点数
• 一个数除以另一个数，除不尽时，结果为浮点数，并保留到小数后4位
• 乘除法优先级相同
• 数学中0不能做除数，但MySQL中，一个数除以0后变成NULL

4.2、比较运算符

比较运算符用来进行比较，结果为真返回1，为假返回0，其他结果返回NULL。常用来作为SELECT查询语句的条件使用，返回符合条件的结果记录。

运算符	名称	作用	示例
=	等于	判断两个值、字符串或表达式是否相等	SELECT C FROM TABLE WHERE A=B
<=>	安全等于	判断是否相等，主要用于处理NULL	SELECT C FROM TABLE WHERE A<=>B
<>或!=	不等于	判断两个值、字符串或表达式是否不相等	SELECT C FROM TABLE WHERE A<>B
<	小于	判断前面的值、字符串或表达式是否小于后面的	SELECT C FROM TABLE WHERE A<B
<=	小于等于	判断前面的值、字符串或表达式是否小于等于后面的	SELECT C FROM TABLE WHERE A<=B
>	大于	判断前面的值、字符串或表达式是否大于后面的	SELECT C FROM TABLE WHERE A>B
>=	大于等于	判断前面的值、字符串或表达式是否大于等于后面的	SELECT C FROM TABLE WHERE A>=B

其中=等于运算符:

• 两边相等返回1，不等返回0
• 如果两边都是字符串，则按照字符串比较（比较字符的ANSI编码）
• 如果两边都是整数，则按照整数比较大小
• 如果一边是整数，一边是字符串，则会将字符串转化为数字进行比较
• 如果其中一边为NULL，则比较结果为NULL
• 和其他语言不同，=是等于运算不是赋值。SQL中的赋值使用的是  :=

• 注意：0 = 'a'得到1，因为字符串存在隐式转换，转换数值不成功的时候看做0。'a' = 'b'得到0，因为两边都是字符串的话，按照ANSI比较规则比较。

<=>安全等于运算符：

• 与=的区别就在于可以对NULL进行判断，=的两边只要有一个NULL就返回NULL
• <=>两边都为NULL时，返回1
• <=>其中一边为NULL时，返回0

<>或!=不等于运算符：

• 判断是否不相等，不相等返回1，相等返回0
• 两边只要有一个NULL，就返回NULL

此外还有非符号类型的运算符：

运算符	名称	作用	说明
IS NULL	为空	判断是否为空	判断一个值是NULL返回1，不是NULL返回0
IS NOT NULL	不为空	判断是否不为空	判断一个值不是NULL返回1，是NULL返回0
LEAST	最小值	多个值中返回最小值	返回几个值中最小/最大那个， 举例 LEAST(1,0,2)  GREATEST('a','b','c') 当两边是数字，返回最小/最大的数。 当两边是字符串，返回字母表中靠前/靠后值。 当有NULL时，返回值为NULL
GREATEST	最大值	多个值中返回最大值
BETWEEN AND	两值之间	判断一个值是否在两值的范围内	WHERE C BETWEEN A AND B 当C在A与B之间时返回1
ISNULL	为空	判断是否为空	和IS NULL一样。用法不同 举例   'a' IS NULL 和ISNULL('a')
IN	属于	判断值是否在一个列表内	如果值在给定的列表内返回1，不在返回0。如果值为NULL或列表中有NULL就返回NULL
NOT IN	不属于	判断值是否不在一个列表内	如果值不在给定的列表内返回1，在返回0。
LIKE	模糊匹配	判断一个值是否符合模糊匹配	模糊匹配字符串，满足返回1，不满足返回0。如果给定值或匹配条件为NULL则返回NULL 通常使用如下通配符： "%" --匹配0个或多个字符 "_" --只能匹配一个字符 如果就使用%和_原本的意思而不代表通配符，就要配合转义符来使用。举例：要找出"IT_"开头的所有值： 转义符1：LIKE 'IT\_%'    转义符2：LIKE 'IT$_%' escape '$'
REGEXP	正则表达式	判断一个字符串是否符合正则规则	expr REGEXP 匹配条件 字符串expr满足条件返回1，不满足返回0。如果expr或匹配条件任一为NULL则返回NULL
RLIKE	正则表达式	判断一个值是否符合正则规则

REGEXP常用以下几种通配符：

• （1）‘^’匹配以该字符后面的字符开头的字符串。
• （2）‘$’匹配以该字符前面的字符结尾的字符串。
• （3）‘.’匹配任何一个单字符。
• （4）“[...]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”或“b”或“c”。为了命名字符的范围，使用一个‘-’。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。
• （5）‘*’匹配零个或多个在它前面的字符。例如，“x*”匹配任何数量的‘x’字符，“[0-9]*”匹配任何数量的数字，而“*”匹配任何数量的任何字符。

注意：

• WHERE salary IN （2000,5000）不代表2000-5000这个范围，而是指2000,5000这两个数字
• BETWEEN 2000 AND 5000才是这个范围。或者salary >=2000 AND salary <= 5000.
• 判断空的时候，用 IS NULL，也可以用 <=> NULL，不要用 = NULL

4.3、逻辑运算符

逻辑运算符用来判断表达式真假，返回1、0或者NULL

运算符	作用	说明
NOT或!	逻辑非	判断给定值为0是返回1，为1时返回0，NULL时返回NULL
AND或&&	逻辑与	有0就返回0，全部非0非空才返回1，非0但有NULL返回NULL  （简记：0高于NULL高于1）
OR或||	逻辑或	有非0就返回1，全部0且非空才返回0，有0也有空返回NULL （简记：1高于NULL高于0）
XOR	逻辑异或	任一个值为NULL时返回NULL，两个非空值都是0或都是非0返回0，一个0一个非0返回1

AND优先级高于OR

4.4、位运算符

位运算符是对二进制数进行计算的。会先将操作数变成二进制，再进行位运算，最后变回十进制。

运算符	作用	说明
&	按位与（位AND）	将给定值对应的二进制逐位进行逻辑与运算。二进制位都为1时返回1，否则返回0
|	按位或（位OR）	将给定值对应的二进制逐位进行逻辑或运算。二进制位有一个或两个1返回1，否则返回0
^	按位异或（位XOR）	将给定值对应的二进制逐位进行逻辑异或运算。二进制位的数值不同时按位返回1，否则返回0
~	按位取反	将给定的值的二进制逐位取反，即1变0,0变1
>>	按位右移	将给定的值的二进制所有位右移指定位数，然后右边低位数值移出丢弃，左边高位空出的位置用0补齐
<<	按位左移	将给定的值的二进制所有位左移指定位数，然后左边高位数值移出丢弃，右边低位空出的位置用0补齐

4.5、运算符的优先级

运算符优先级从低到高依次是：

•  := , = (赋值)
• || , OR , XOR
• &&,AND
• NOT
• BETWEEN,CASE,WHEN,THEN,ELSE
• =（比较运算符）,<=>,>=,>,<=,<,<>,!=,IS,LIKE,REGEXP,IN
• |
• &
• <<与>>
• +和-
• *,/,DIV,%和MOD
• ^
• -（负号）和~（按位取反）
• !
• ()

拓展：正则表达式查询

第05章：分页与排序

5.1、排序数据

• 使用ORDER BY子句排序，在SELECT语句的结尾
• ASC升序，DESC降序。（不写的话默认是升序）
• ORDER BY 字段1;  --  单列排序
• ORDER BY 字段1,字段2 DESC;  --  多列降序排序

注意：

• 排序的字段可以不在SELECT范围内（例如我SELECT查询学生学号，然后根据成绩排名列出来）
• 在对多列排序的时候，首先按第一列排序，第一列相同的值再按第二列排序

5.2、分页展示

分页显示就是将SELECT查询到的结果集，一段一段显示出来的条件。第一个值是偏移量，表示从第几个开始（默认是0，也就是从第一个开始），第二个值是行数，代表返回多少条记录。

LIMIT 位置偏移量，行数

举例：

• SELECT * FROM 表名 LIMIT 10;  --  表示显示前10条记录
• SELECT * FROM 表名 LIMIT 10,10;  --表示显示第11到第20条记录

注意：

• LIMIT必须放到整个SELECT语句的最后
• LIMIT的好处是约束返回结果数量，减少网络传输提升查询效率
• 分页显示公式：(当前页数-1)*每页条数，每页条数   (例如每页显示10条记录，查看第5页，也就是LIMIT40,10;)
• 不同DBMS中关键字可能不同

第06章：多表查询

多表查询也叫关联查询。指两个或多个表一起完成查询操作。前提条件是一起查询的表之间有关联（一对一，一对多），互相之间一定有关联字段。

6.1、多表连接

什么是笛卡尔积：

• 如果直接查询来自不同表的多个字段，会出现笛卡尔积的情况（也叫交叉连接，英文CROSS JOIN）
• 笛卡尔积的意思就是把两个集合的所有组合情况都列出，组合个数也就是元素个数的乘积数。
• SQL99中使用CROSS JOIN来表示两个表的交叉连接，它可以把任意表进行连接，尽管他们不相关。
• 举例：SELECT last_name,department_name FROM employees CROSS JOIN departments;
• 在多表无连接条件、连接条件无效或表中所有行互相连接的情况下会出现

但是在两个表有连接条件的时候，这不是我们想要的结果，就可以使用WHERE后跟连接条件来过滤：

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;  #连接条件

如果两个表中有同名的列，注意要在列名前加上表名前缀，举例查询员工的姓名及其部门名称：
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

6.2、多表查询的分类

多表查询：需要通过where对多表的字段之间进行连接

1、等值连接VS非等值连接

• 等值连接：WHERE 表1.字段1 = 表2.字段1
• 非等值连接举例：WHERE 表1.字段1 >= 表2.字段1
• 多个表中有相同列时，必须在列名前加上表名前缀来区分。
• 为了简化表名的使用，可以给表起别名，和字段的别名一样。
• 表有了别名后就必须用别名不能用原名。
• 可连接多个表，连接n个表至少需要n-1个连接条件。
• 使用表别名的等值连接举例，查询员工的工号、姓名、部门和地址信息：

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;

• 非等值连接举例，找出工资在工作级别表中最高与最低之间的员工：

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;

2、自连接VS非自连接

• 非自连接非常多，就是不同表之间的连接。
• 自连接较少，就是一个表自己跟自己的连接。WHERE 表1.字段1 = 表1.字段2
• 为了区分一个表代表的不同意义，可以给一个表起多个别名分开使用
• 当table1和table2其实是同一个表，只是用取别名的方式虚拟成两个表以代表不同意义。然后两个表内连接外连接等，这就是自连接。举例：查询员工表，返回“xx员工works for xx经理”，因为经理本人也在员工表内，所以连接方法就是这个表自连接，连接条件是员工工号=另一个员工的经理的工号

SELECT CONCAT(worker.last_name ,' works for ', manager.last_name)
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;

3、内连接vs外连接

有些字段在两个表内不一定是一一对应的。比如有的员工还没有部门，有的部门还没有员工，这时就需要用到外连接。除了查询满足条件的记录以外，外连接还可以查询某一方不满足条件的记录。

• 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
• 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
• 如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表。
• 如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表。
• SQL92使用(+)创建连接，(+)代表从表所在位置，Oracle支持SQL92，但MySQL不支持

6.3、SQL99语法多表查询

• 在SQL99中，使用JOIN...ON的子句创建连接的语法结构

  SELECT table1.column, table2.column,table3.column
  FROM table1
  	JOIN table2 ON table1和table2的连接条件
  		JOIN table3 ON table2和table3的连接条件

• ON子句指定额外的连接条件，其他条件是分开的，ON子句使语句具有更高的易读性
• 关键字JOIN、INNER JOIN、CROSS JOIN的含义一样，都代表内连接
• 内连接的语法

  SELECT 字段列表
  FROM A表 INNER JOIN B表
  ON 关联条件
  WHERE 等其他子句;

• 左外连接的语法

  #实现查询结果是A
  SELECT 字段列表
  FROM A表 LEFT JOIN B表
  ON 关联条件
  WHERE 等其他子句;

• 右外连接的语法

  #实现查询结果是B
  SELECT 字段列表
  FROM A表 RIGHT JOIN B表
  ON 关联条件
  WHERE 等其他子句;

• 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
• SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
• 但是MySQL不支持FULL JOIN，可以用 LEFT JOIN UNION RIGHT JOIN代替。

内连接、左外连接、右外连接举例：

有一个员工表记录了员工id、姓名、部门id等信息
有一个部门表记录了部门id、部门名称等信息
但有的员工没分配部门，所以部门id是空。有的部门还没有员工。

#内连接，它会查询出所有有部门id的员工信息
SELECT e.employee_id, e.last_name, e.department_id,d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id);

#左外连接，他会查询出所有的员工姓名（不管有没有部门id），有部门信息的写信息，没部门信息的是NULL
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

#右外连接，它会查询出所有部门名称（不管部门id有没有员工），有信息的写信息，没信息的是NULL
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

6.4、UNION的使用

UNION关键字可以把多个SELECT语句的查询结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。语法

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

• UNION返回两个查询结果集的并集去重，UNION ALL合并后不去除重复部分
• 但UNION ALL用的资源少，所以如果明知合并数据中没有重复的，就尽量使用UNION ALL

举例，查询部门编号>90或邮箱包含a的员工信息：

方法1：

SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;

方法2：

SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;

6.5、七种SQL JOINS的实现

除了中间图片是内连接、左上角图片是左外连接、右上角图片是右外连接之外，剩余四张图总结如下：

• 左中图

  #实现A - A∩B
  select 字段列表
  from A表 left join B表
  on 关联条件
  where 从表关联字段 is null and 等其他子句;

• 右中图

  #实现B - A∩B
  select 字段列表
  from A表 right join B表
  on 关联条件
  where 从表关联字段 is null and 等其他子句;

• 左下图，其实就是满外连接，但MySQL不支持FULL JOIN，所以需要用到UNION

  #实现查询结果是A∪B
  #就是左中图 + 右上图
  select 字段列表
  from A表 left join B表
  on 关联条件
  where 从表关联字段 is null and 等其他子句;
  union
  select 字段列表
  from A表 right join B表
  on 关联条件
  where 等其他子句;

• 右下图

  #左中图 + 右中图
  #使用左外的 (A - A∩B) union 右外的（B - A∩B）
  select 字段列表
  from A表 left join B表
  on 关联条件
  where 从表关联字段 is null and 等其他子句
  union
  select 字段列表
  from A表 right join B表
  on 关联条件
  where 从表关联字段 is null and 等其他子句

6.6、SQL99语法新特性

自然连接NATURAL JOIN。会帮你自动查询两张连接表中所有相同的字段，然后做等值连接。

USING连接，可以指定数据表里的同名字段进行等值连接，只能配合JOIN一起用。

6.7、总结

连接表的约束条件有三种方式：WHERE、ON、USING

• WHERE适用所有关联查询
• ON必须和JOIN一起用，只能写关联条件。多个ON比一个WHERE可读性好
• USING必须和JOIN一起用，而且要求两个关联字段名称一致，只能表示关联字段值相等

第07章：单行函数

7.1、函数理解

SQL语言中的函数和其他计算机语言的函数一样，都是将常用的代码封装起来，需要的时候直接调用即可。可以分成内置函数和自定义函数。

DBMS之间的差异性很大，极少有函数可以在所有DBMS都支持的，所以SQL函数可移植性很差。

内置函数分很多类，后面章节都是讲内置函数。简单的可分为单行函数和聚合函数

单行函数的特点是：

• 操作数据对象
• 接收参数返回一个结果
• 只对一行进行变换
• 每行返回一个结果
• 可以嵌套
• 参数可以是一列或一个值

7.2、数值函数

基本函数

函数	用法
ABS(x)	返回x的绝对值
SIGN(X)	返回x的符号，正数返回1，负数返回-1,0返回0
PI()	返回圆周率的值
CEIL(x)，CEILING(x)	返回大于等于某个值的最小整数
FLOOR(x)	返回小于等于某个值的最大整数
LEAST(e1,e2,e3…)	返回列表中的最小值
GREATEST(e1,e2,e3…)	返回列表中的最大值
MOD(x,y)	返回x除以y的余数
RAND()	返回0-1的随机值
RAND(x)	返回0-1的随机值，x作为种子值，相同的x会产生相同的随机数
ROUND(x)	返回一个对x四舍五入后最接近于x的整数
ROUND(x,y)	返回一个对x四舍五入后最接近于x的值，并保留到小数点后y位
TRUNCATE(x,y)	返回数字x截断为y位小数的结果
SQRT(x)	返回x的平方根。当x为负数时返回NULL

角度弧度函数

函数	用法
RADIANS(x)	将角度转化为弧度，其中，参数x为角度值
DEGREES(x)	将弧度转化为角度，其中，参数x为弧度值

三角函数

函数	用法
SIN(x)	返回x的正弦值，其中，参数x为弧度值
ASIN(x)	返回x的反正弦值，即获取正弦为x的值。如果x的值不在-1到1之间，则返回NULL
COS(x)	返回x的余弦值，其中，参数x为弧度值
ACOS(x)	返回x的反余弦值，即获取余弦为x的值。如果x的值不在-1到1之间，则返回NULL
TAN(x)	返回x的正切值，其中，参数x为弧度值
ATAN(x)	返回x的反正切值，即返回正切值为x的值
ATAN2(m,n)	返回两个参数的反正切值
COT(x)	返回x的余切值，其中，X为弧度值

指数对数函数

函数	用法
POW(x,y)，POWER(X,Y)	返回x的y次方
EXP(X)	返回e的X次方，其中e是一个常数，2.718281828459045
LN(X)，LOG(X)	返回以e为底的X的对数，当X <= 0 时，返回的结果为NULL
LOG10(X)	返回以10为底的X的对数，当X <= 0 时，返回的结果为NULL
LOG2(X)	返回以2为底的X的对数，当X <= 0 时，返回NULL

进制转换函数

函数	用法
BIN(x)	返回x的二进制编码
HEX(x)	返回x的十六进制编码
OCT(x)	返回x的八进制编码
CONV(x,f1,f2)	返回f1进制数变成f2进制数

7.3、字符串函数

注意：MySQL中字符串的位置是从1开始的

函数	用法
ASCII(S)	返回字符串S中的第一个字符的ASCII码值
CHAR_LENGTH(s)	返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同
LENGTH(s)	返回字符串s的字节数，和字符集有关
CONCAT(s1,s2,......,sn)	连接s1,s2,......,sn为一个字符串
CONCAT_WS(x,s1,s2,......,sn)	同CONCAT(s1,s2,...)函数，但是每个字符串之间要加上x
INSERT(str, idx, len,replacestr)	将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr
REPLACE(str, a, b)	用字符串b替换字符串str中所有出现的字符串a
UPPER(s) 或 UCASE(s)	将字符串s的所有字母转成大写字母
LOWER(s) 或LCASE(s)	将字符串s的所有字母转成小写字母
LEFT(str,n)	返回字符串str最左边的n个字符
RIGHT(str,n)	返回字符串str最右边的n个字符
LPAD(str, len, pad)	用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str ,len, pad)	用字符串pad对str最右边进行填充，直到str的长度为len个字符
LTRIM(s)	去掉字符串s左侧的空格
RTRIM(s)	去掉字符串s右侧的空格
TRIM(s)	去掉字符串s开始与结尾的空格
TRIM(s1 FROM s)	去掉字符串s开始与结尾的s1
TRIM(LEADING s1 FROM s)	去掉字符串s开始处的s1
TRIM(TRAILING s1 FROM s)	去掉字符串s结尾处的s1
REPEAT(str, n)	返回str重复n次的结果
SPACE(n)	返回n个空格
STRCMP(s1,s2)	比较字符串s1,s2的ASCII码值的大小
SUBSTR(s,index,len)	返回从字符串s的index位置其len个字符，作用与SUBSTRING(s,n,len)、MID(s,n,len)相同
LOCATE(substr,str)	返回字符串substr在字符串str中首次出现的位置，作用于POSITION(substrIN str)、INSTR(str,substr)相同。未找到，返回0
ELT(m,s1,s2,…,sn)	返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回sn
FIELD(s,s1,s2,…,sn)	返回字符串s在字符串列表中第一次出现的位置
FIND_IN_SET(s1,s2)	返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串
REVERSE(s)	返回s反转后的字符串
NULLIF(value1,value2)	比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1

7.4、日期和时间函数

获取日期时间、日期与时间戳的转换函数

函数	用法
CURDATE() ，CURRENT_DATE()	返回当前日期，只包含年、月、日
CURTIME() ， CURRENT_TIME()	返回当前时间，只包含时、分、秒
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() /LOCALTIMESTAMP()	返回当前系统日期和时间
UTC_DATE()	返回UTC（世界标准时间）日期
UTC_TIME()	返回UTC（世界标准时间）时间
UNIX_TIMESTAMP()	以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() ->1634348884
UNIX_TIMESTAMP(date)	将时间date以UNIX时间戳的形式返回
FROM_UNIXTIME(timestamp)	将UNIX时间戳的时间转换为普通格式的时间

获取月份、星期、星期数、天数等函数

函数	说明
YEAR(date) / MONTH(date) / DAY(date)	返回具体的日期值
HOUR(time) / MINUTE(time) /SECOND(time)	返回具体的时间值
MONTHNAME(date)	返回月份：January，...
DAYNAME(date)	返回星期几：MONDAY，TUESDAY.....SUNDAY
WEEKDAY(date)	返回周几，注意，周1是0，周2是1，。。。周日是6
QUARTER(date)	返回日期对应的季度，范围为1～4
WEEK(date) ， WEEKOFYEAR(date)	返回一年中的第几周
DAYOFYEAR(date)	返回日期是一年中的第几天
DAYOFMONTH(date)	返回日期位于所在月份的第几天
DAYOFWEEK(date)	返回周几，注意：周日是1，周一是2，。。。周六是7

日期的操作函数

函数	说明
EXTRACT(type FROM date)	返回指定日期中特定的部分，type指定返回的值

    其中上表的type的取值与含义：

时间和秒针转换函数、计算时间和日期的函数

函数	说明
TIME_TO_SEC(time)	将 time 转化为秒并返回结果值。转化的公式为： 小时*3600+分钟*60+秒
SEC_TO_TIME(seconds)	将 seconds 描述转化为包含小时、分钟和秒的时间
DATE_ADD(datetime, INTERVAL expr type)，ADDDATE(date,INTERVAL expr type)	返回与给定日期时间相差INTERVAL时间段的日期时间
DATE_SUB(date,INTERVAL expr type)，SUBDATE(date,INTERVAL expr type)	返回与date相差INTERVAL时间间隔的日期

    上述函数中type的取值与含义：

计算时间和日期的函数

函数	说明
ADDTIME(time1,time2)	返回time1加上time2的时间。当time2为一个数字时，代表的是秒，可以为负数
SUBTIME(time1,time2)	返回time1减去time2后的时间。当time2为一个数字时，代表的是秒，可以为负数
DATEDIFF(date1,date2)	返回date1 - date2的日期间隔天数
TIMEDIFF(time1, time2)	返回time1 - time2的时间间隔
FROM_DAYS(N)	返回从0000年1月1日起，N天以后的日期
TO_DAYS(date)	返回日期date距离0000年1月1日的天数
LAST_DAY(date)	返回date所在月份的最后一天的日期
MAKEDATE(year,n)	针对给定年份与所在年份中的天数返回一个日期
MAKETIME(hour,minute,second)	将给定的小时、分钟和秒组合成时间并返回
PERIOD_ADD(time,n)	返回time加上n后的时间

日期的格式化与解析

函数	说明
DATE_FORMAT(date,fmt)	按照字符串fmt格式化日期date值
TIME_FORMAT(time,fmt)	按照字符串fmt格式化时间time值
GET_FORMAT(date_type,format_type)	返回日期字符串的显示格式
STR_TO_DATE(str, fmt)	按照字符串fmt对str进行解析，解析为一个日期

    上述非GET_FORMAT函数中fmt参数常用的格式符：

    GET_FORMAT函数中date_type和format_type参数取值如下：

7.5、流程控制函数

流程处理函数可以根据不同条件，执行不同的处理流程，可以在SQL语句实现不同的条件选择。
MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。

SQL的查询其实自带循环了，所以流程控制函数中只有分支（if、case等），没有循环。

函数	说明
IF(value,value1,value2)	如果value的值为TRUE，返回value1，否则返回value2
IFNULL(value1, value2)	如果value1不为NULL，返回value1，否则返回value2
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2.... [ELSE resultn] END	相当于Java的if...else if...else...
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 .... [ELSE 值n] END	相当于Java的switch...case...

7.6、加密解密函数

加密解密函数主要用于对数据库中数据进行加密解密处理，防止被窃取。其中ENCODE(value,password_seed)函数与DECODE(value,password_seed)函数互为反函数。

函数	说明
PASSWORD(str)	返回字符串str的加密版本，41位长的字符串。加密结果不可逆，常用于用户的密码加密
MD5(str)	返回字符串str的md5加密后的值，也是一种加密方式。若参数为NULL，则会返回NULL
SHA(str)	从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL。SHA加密算法比MD5更加安全。
ENCODE(value,password_seed)	返回使用password_seed作为加密密码加密value
DECODE(value,password_seed)	返回使用password_seed作为加密密码解密value

7.7、MySQL信息函数

函数	说明
VERSION()	返回当前MySQL的版本号
CONNECTION_ID()	返回当前MySQL服务器的连接数
DATABASE()，SCHEMA()	返回MySQL命令行当前所在的数据库
USER()，CURRENT_USER()、SYSTEM_USER()，SESSION_USER()	返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名”
CHARSET(value)	返回字符串value自变量的字符集
COLLATION(value)	返回字符串value的比较规则

7.8、其他函数

函数	说明
FORMAT(value,n)	返回对数字value进行格式化后的结果数据。n表示四舍五入后保留到小数点后n位
CONV(value,from,to)	将value的值进行不同进制之间的转换
INET_ATON(ipvalue)	将以点分隔的IP地址转化为一个数字
INET_NTOA(value)	将数字形式的IP地址转化为以点分隔的IP地址
BENCHMARK(n,expr)	将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费的时间
CONVERT(value USINGchar_code)	将value所使用的字符编码修改为char_code

第08章：聚合函数

8.1、什么是聚合函数

聚合函数是对一组数据进行汇总的函数，输入一组数据，输出单个值。有以下5个：

• AVG()  --对数值型数据求平均值
• SUM()  --对数值型数据求和
• MAX()  --对任意数据类型求最大值
• MIN()  --对任意数据类型求最小值
• COUNT(*) --对任意数据类型返回表中的记录总数
• COUNT(expr)  --返回expr不为空的记录总数

注意：

• 要统计一个表中共有几条数据，可以用COUNT(*)，也可以用任意的数字比如COUNT(1)，但不能用COUNT(某字段)。因为字段的值有可能是NULL，NULL是不被统计的。
• COUNT(某字段)代表该字段出现了几次，当该字段没有空值的时候，才有COUNT(*)=COUNT(该字段)=表中的数据数量
• AVG和SUM也都是会自动过滤掉空值的，所以某个字段的 AVG = SUM / COUNT是成立的。

所以，有些情况下想求平均值直接用AVG的话是错误的。因为他只会计算非空的数据的平均值，而实际我们想要的是所有数据的平均值。举例：公司有100人，其中50人有年终奖，如果我们想计算公司年终奖平均值，直接用AVG的话就成了 总奖金/50，而实际分母应该是100，正确答案应该是：

• SUM(奖金) / COUNT(人数)   --  两个函数的输入不一样
• AVG(IFNULL(奖金,0))   --相当于将空值当做0来计算

8.2、GROUP BY 分组

• GROUP BY可以通过某字段将查询结果分组

• SELECT column, group_function(column)
  FROM table
  [WHERE condition]
  [GROUP BY group_by_expression]
  [ORDER BY column];

• 在SELECT列表中所有未包含在组函数中的列都应该包含在 GROUP BY子句中

• SELECT department_id, AVG(salary)
  FROM employees
  GROUP BY department_id ;

• 包含在 GROUP BY 子句中的列不必包含在SELECT 列表中

• SELECT AVG(salary)
  FROM employees
  GROUP BY department_id ;

• 也可以通过多个列分组

• SELECT department_id dept_id, job_id, SUM(salary)
  FROM employees
  GROUP BY department_id, job_id ;

• 使用WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。注意：当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。

• SELECT department_id,AVG(salary)
  FROM employees
  WHERE department_id > 80
  GROUP BY department_id WITH ROLLUP;

8.3、HAVING

HAVING子句的作用是过滤：

1. 筛选条件中有聚合函数
2. 筛选显示的是满足HAVING子句中条件的分组。
3. HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。

WHERE和HAVING的对比：

• HAVING必须和GROUP BY同用，WHERE不需要
• HAVING的筛选条件可以有聚合函数，但WHERE不行
• HAVING可以使用分组中的计算函数当筛选条件，但WHERE不行（因为查询语句中的执行顺序是WHERE--GROUP BY--HAVING）
• 如果要通过连接从关联表中获取需要的数据，WHERE是先筛选后连接，而HAVING是先连接后筛选。（所以WHERE效率高，占资源少）

总结WHERE和HAVING的选择：

• 在一个查询语句中，WHERE和HAVING可以同时使用
• 普通条件用WHERE，因为效率高
• 包含分组统计函数的条件用HAVING，因为WHERE做不到啊

另外，WHERE排除的记录也不再包括在分组中

8.4、SELECT的执行过程

SELECT查询语句的两个顺序：

• 语句的关键字的顺序是SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...
• 语句的执行顺序是FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT  （简单记忆，其实就是先做表连接条件筛选过滤，再做字段查询得到结果，最后做结果排序分页等）

SQL的执行原理：

1. SELECT 是先执行 FROM 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：
   1. 首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；
   2. 通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
   3. 添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。
   4. 当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据。
2. 然后进入第三步和第四步，也就是 GROUP 和 HAVING 阶段。在这个阶段中，实际上是在虚拟表 vt2 的基础上进行分组和分组过滤，得到中间的虚拟表 vt3 和 vt4 。
3. 当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到 SELECT 和 DISTINCT阶段。
4. 首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表vt5-1 和 vt5-2 。
5. 当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是 ORDER BY 阶段，得到虚拟表 vt6 。
6. 最后在 vt6 的基础上，取出指定行的记录，也就是 LIMIT 阶段，得到最终的结果，对应的是虚拟表vt7 。

当然我们在写 SELECT 语句的时候，不一定存在所有的关键字，相应的阶段就会省略。同时因为 SQL 是一门类似英语的结构化查询语言，所以我们在写 SELECT 语句的时候，还要注意相应的关键字顺序，所谓底层运行的原理，就是我们刚才讲到的执行顺序。

第09章：子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询。SELECT语句经常需要从一个查询结果集中去查询，所以子查询大大增强了SELECT的能力。

9.1、子查询

1、子查询的语法结构：

SELECT 字段
FROM 表
WHERE 筛选语句 (
                SELECT 字段
                FROM 表
               );

2、注意：

• 执行顺序是先执行完子查询，然后进行主查询
• 主查询(外查询)使用子查询(内查询)的结果
• 子查询语句要写到括号内
• 子查询要放到比较条件的右侧
• 单行操作符对应单行子查询，多行操作符对应多行子查询

3、子查询的分类：

• 内查询返回一条结果叫单行子查询，子查询返回多条结果叫多行子查询
• 子查询的结果只用一次后作为主查询的条件执行，叫不相关子查询。如果子查询需要执行多次，用循环的方式，从外部查询开始每次都传入子查询，这种嵌套的执行方式叫相关子查询。

9.2、单行子查询

单行比较操作符有：>、=、>=、<、<=、<>

代码示例：

#例题1：查询工资大于149号员工工资的员工的信息
SELECT last_name
FROM employees
WHERE salary > 
                (SELECT salary
                 FROM employees
                 WHERE employee_id = 149);


#HAVING中的子查询：先执行子查询，返回结果给HAVING子句
#例题2：查询最低工资大于50号部门最低工资的部门id
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >
                     (SELECT MIN(salary)
                     FROM employees
                     WHERE department_id = 50);


#CASE中的子查询
#例题3：显示员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800的department_id相同，则location为’Canada’，其余则为’USA’
SELECT employee_id, last_name,CASE department_id 
    WHEN (SELECT department_id FROM departments WHERE location_id = 1800)
    THEN 'Canada' 
    ELSE 'USA' END "location"
FROM employees;

#子查询中的空值
#例题4：子查询不返回任何行
SELECT last_name, job_id
FROM employees
WHERE job_id =
                (SELECT job_id
                 FROM employees
                 WHERE last_name = 'Haas');

#非法使用子查询
#例题5：下面错误出在了单行比较符却用了多行子查询
SELECT employee_id, last_name
FROM employees
WHERE salary =
                (SELECT MIN(salary)
                 FROM employees
                 GROUP BY department_id);

9.3、多行子查询

多行比较符有：

• IN  --等于列表中任一个
• ANY --要和单行比较符一起用，和子查询返回的某一个值比较
• ALL --要和单行比较符一起用，和子查询返回的所有值比较
• SOME --和ANY一样，一般常使用ANY

例题：

#查询平均工资最低的部门id
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
                            SELECT AVG(salary) avg_sal
                            FROM employees
                            GROUP BY department_id
                           );

9.4、相关子查询

1、如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询。相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。

2、示例：

#查询员工中工资大于本部门平均工资的员工的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
               );

#在这个例子中，子查询用到了外查询的表别名为outer

3、EXISTS 与 NOT EXISTS关键字：

• 关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
• 如果在子查询中不存在满足条件的行：条件返回 FALSE、继续在子查询中查找
• 如果在子查询中存在满足条件的行：不在子查询中继续查找、条件返回 TRUE
• NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

例题：

#查询公司管理者的employee_id，last_name，job_id，department_id信息
SELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHERE EXISTS ( SELECT *
               FROM employees e2
               WHERE e2.manager_id = e1.employee_id);

4、相关更新：还可以使用相关子查询依据一个表中的数据更新另一个表的数据。

例题：

#在employees中增加一个department_name字段，数据为员工对应的部门名称

# 1）
ALTER TABLE employees
ADD(department_name VARCHAR2(14));

# 2）
UPDATE employees e
SET department_name = (SELECT department_name
                       FROM departments d
                       WHERE e.department_id = d.department_id);