马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。
您需要 登录 才可以下载或查看，没有账号？立即注册
×
SQL语句进阶版
MySQL查询数据的过程
一、毗连与身份验证
- 客户端请求：客户端（如应用程序、数据库管理工具等）向MySQL服务器发送一条查询请求。
- 毗连器处理：MySQL的毗连器（Connector）负责处理这个毗连请求。毗连器是毗连客户端和MySQL服务器的一个重要组件，其主要功能是处理毗连请求、验证客户端身份、协商客户端和服务器之间的协议等。
- 毗连池管理：在高并发环境下，毗连池管理器会预先创建肯定数目标毗连，以便客户端能够快速地获取可用的毗连。当客户端请求毗连时，毗连池管理器会查抄毗连池中是否有空闲毗连，假如有，则将其提供给客户端；假如没有，则会创建新的毗连。
- 身份验证：毗连器会验证客户端提供的用户名、密码等身份信息，以确保客户端具有访问MySQL服务器的权限。
- 权限控制：在身份验证通过后，MySQL还会查抄客户端是否具有执行该查询的权限。这通常涉及查询对象的权限验证，如数据表、数据列的访问权限等。
二、查询缓存（MySQL 8.0之前版本）
注意：在MySQL 8.0版本中，查询缓存已被删除，因此以下步调仅实用于MySQL 8.0之前的版本。
- 查抄缓存：MySQL会起首查抄查询缓存，看是否有之前执行过的相同查询及其结果。这是通过哈希查找来实现的，哈希查找只能进行全值查找（即SQL语句必须完全一致）。
- 缓存命中：假如缓存命中，MySQL会立即返回存储在缓存中的结果，而无需进行后续的剖析、优化和执行步调。这可以大大提高查询性能。
- 缓存未命中：假如缓存未命中，MySQL则会进入后续的查询剖析、优化和执行步调。
三、查询剖析与优化
- 查询剖析：
- 语法剖析：MySQL剖析器通过关键字将SQL语句进行剖析，并生成对应的剖析树。剖析器会使用MySQL语法规则验证和剖析查询，如验证是否使用了错误的关键字、关键字的顺序是否精确、引号是否前后匹配等。
- 预处理：预处理器会根据一些MySQL规则进一步查抄剖析树是否正当。比方，查抄数据表和数据列是否存在、剖析名字和别名是否有歧义等。同时，预处理器还会验证用户权限。
- 查询优化：
- 优化器作用：优化器的目标是找到最好的执行计划。一条查询可以有多种执行方式，但最终都会返回相同的结果。优化器的作用就是选择其中资本最小的一种执行方式。
- 生成执行计划：优化器会将MySQL语句转换为执行计划。这个执行计划表明了应该使用哪些索引执行查询、表之间的毗连顺序等。MySQL使用基于资本的优化器（CBO），会猜测一个查询使用某种执行计划的资本，并选择资本最小的一个。
四、查询执行
- 调用存储引擎：MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。存储引擎是MySQL中负责存取真实数据的组件，它吸收上层传下来的指令，对表中的数据进行读取或写入操作。
- 执行过程：查询执行引擎根据执行计划来完成整个查询。执行计划是一个数据布局（指令树），MySQL会根据执行计划给出的指令逐步执行。在执行过程中，有大量的操作需要调用存储引擎实现的接口来完成，这些接口即为“handler API”。
五、返回结果
- 结果返回：MySQL将查询结果集返回给客户端。这是一个增量逐步返回的过程，即当查询生成第一条结果时，MySQL就可以开始向客户端逐步返回结果了。这样可以节省服务端内存，并让客户端第一时间获得返回结果。
- 缓存查询结果（MySQL 8.0之前版本）：假如查询可以被缓存（在MySQL 8.0之前的版本中），MySQL会在这个阶段将结果存放到查询缓存中，以便后续相同查询可以直接从缓存中获取结果。但在MySQL 8.0及之后的版本中，由于查询缓存已被删除，因此不会再进行这一步操作。
MySQL语句
准备环境
创建 location 表并插入数据
- -- 创建 location 表
- create table location (
- Region char(20),
- Store_Name char(20)
- );
- -- 插入数据到 location 表
- insert into location values('East','Boston');
- insert into location values('East','New York');
- insert into location values('West','Los Angeles');
- insert into location values('West','Houston');
- -- location 表格
- +----------+--------------+
- | Region | Store_Name |
- |----------+--------------|
- | East | Boston |
- | East | New York |
- | West | Los Angeles |
- | West | Houston |
- +----------+--------------+
- -- 创建 store_info 表
- create table store_info (
- Store_Name char(20),
- Sales int(10),
- Date char(10)
- );
- -- 插入数据到 store_info 表
- insert into store_info values('Los Angeles','1500','2020-12-05');
- insert into store_info values('Houston','250','2020-12-07');
- insert into store_info values('Los Angeles','300','2020-12-08');
- insert into store_info values('Boston','700','2020-12-08');
- insert into store_info values('Washington','1000','2020-12-09');
- insert into store_info values('Chicago','800','2020-12-10');
- -- store_info 表格
- +--------------+---------+------------+
- | Store_Name | Sales | Date |
- |--------------+---------+------------|
- | Los Angeles | 1500 | 2020-12-05 |
- | Houston | 250 | 2020-12-07 |
- | Los Angeles | 300 | 2020-12-08 |
- | Boston | 700 | 2020-12-08 |
- | Washington | 1000 | 2020-12-09 |
- | Chicago | 800 | 2020-12-10 |
- +--------------+---------+------------+
- -- 查询每个地区的总销售额
- select l.Region, sum(s.Sales) as Total_Sales
- from location l
- join store_info s on l.Store_Name = s.Store_Name
- group by l.Region;
- -- 结果
- +--------+------------+
- | Region | Total_Sales|
- +--------+------------+
- | East | 1400 |
- | West | 2050 |
- +--------+------------+
语句示例
SELECT
SELECT语句用于从数据库表中检索数据。您可以指定要检索的字段，或者使用*来选择所有字段。
示例：
- SELECT Store_Name FROM store_info; -- 仅选择Store_Name字段
- SELECT * FROM store_info; -- 选择所有字段
DISTINCT关键字用于返回唯一不同的值。它通常与SELECT语句一起使用，以消除结果会合的重复行。
示例：
- SELECT DISTINCT Store_Name FROM store_info; -- 仅返回不重复的Store_Name
WHERE子句用于过滤记录，只返回满足指定条件的记录。
示例：
- SELECT Store_Name FROM store_info WHERE Sales > 1000; -- 返回Sales大于1000的Store_Name
AND和OR是逻辑运算符，用于在WHERE子句中组合多个条件。AND要求所有条件都为真，而OR要求至少有一个条件为真。
示例：
- SELECT Store_Name FROM store_info WHERE Sales > 1000 OR (Sales < 500 AND Sales > 200); -- 返回Sales大于1000或（Sales小于500且大于200）的Store_Name
IN
IN操作符答应您指定多个可能的值，返回字段值即是这些值之一的记录。
示例：
- SELECT * FROM store_info WHERE Store_Name IN ('Los Angeles', 'Houston'); -- 返回Store_Name为'Los Angeles'或'Houston'的记录
BETWEEN操作符用于选取在某个范围内的值，范围包罗界限值。
示例：
- SELECT * FROM store_info WHERE Date BETWEEN '2020-12-06' AND '2020-12-10'; -- 返回Date在'2020-12-06'和'2020-12-10'之间的记录
通配符通常与LIKE操作符一起使用，用于在WHERE子句中搜索列中的特定模式。
- %：代表零个、一个或多个字符。
- _：代表单个字符。
示例：
- SELECT * FROM store_info WHERE Store_Name LIKE 'L%'; -- 返回Store_Name以'L'开头的所有记录
- SELECT * FROM store_info WHERE Store_Name LIKE '_os%'; -- 返回Store_Name第二个字符为'o'，且以's'后跟任意字符结尾的所有记录
'A_Z'：所有以 ‘A’ 起头，另一个任何值的字符，且以 ‘Z’ 为结尾的字符串。比方，‘ABZ’ 和 ‘A2Z’ 都符合这一个模式，而 ‘AKKZ’ 并不符合 (因为在 A 和 Z 之间有两个字符，而不是一个字符)。
'ABC%': 所有以 ‘ABC’ 起头的字符串。比方，‘ABCD’ 和 ‘ABCABC’ 都符合这个模式。
'%XYZ': 所有以 ‘XYZ’ 结尾的字符串。比方，‘WXYZ’ 和 ‘ZZXYZ’ 都符合这个模式。
'%AN%': 所有含有 'AN’这个模式的字符串。比方，‘LOS ANGELES’ 和 ‘SAN FRANCISCO’ 都符合这个模式。
'_AN%'：所有第二个字母为 ‘A’ 和第三个字母为 ‘N’ 的字符串。比方，‘SAN FRANCISCO’ 符合这个模式，而 ‘LOS ANGELES’ 则不符合这个模式。
LIKE
LIKE操作符用于在WHERE子句中搜索列中的特定模式。它通常与通配符（如%和_）一起使用。
示例：
- SELECT * FROM store_info WHERE Store_Name LIKE '%os%'; -- 返回Store_Name中包含'os'的所有记录
ORDER BY子句用于对结果集进行排序。您可以按一个或多个列进行排序，并指定升序（ASC，默认）或降序（DESC）。
示例：
- SELECT Store_Name, Sales, Date FROM store_info ORDER BY Sales DESC; -- 按Sales降序排序
数学函数
数学函数用于执行数值计算。
- abs(x): 返回x的绝对值。
- rand(): 返回0到1之间的随机数。
- mod(x, y): 返回x除以y的余数。
- power(x, y): 返回x的y次方。
- sqrt(x): 返回x的平方根。
- round(x): 返回离x近来的整数。
- round(x, y): 返回x保留y位小数四舍五入后的值。
- truncate(x, y): 返回x截断为y位小数的值，不进行四舍五入。
- ceil(x): 返回大于或即是x的最小整数。
- floor(x): 返回小于或即是x的最大整数。
- greatest(x1, x2, ...): 返回集合中的最大值。
- least(x1, x2, ...): 返回集合中的最小值。
示例：
- SELECT abs(-1), rand(), mod(5, 3), power(2, 3), round(1.89);
- -- 返回: 1, (随机数), 2, 8, 2
- SELECT round(1.8937, 3), truncate(1.235, 2), ceil(5.2), floor(2.1), least(1.89, 3, 6.1, 2.1);
- -- 返回: 1.894, 1.23, 6, 2, 1.89
聚合函数用于计算一组值的统计信息。
- avg(x): 返回x的平均值。
- count(x): 返回x中非NULL值的个数。count(*)返回所有行的个数。
- min(x): 返回x的最小值。
- max(x): 返回x的最大值。
- sum(x): 返回x的总和。
示例：
- SELECT avg(Sales) FROM store_info; -- 返回Sales的平均值
- SELECT count(Store_Name) FROM store_info; -- 返回Store_Name中非NULL值的个数
- SELECT count(*) FROM City; -- 返回City表中所有行的个数
- SELECT max(Sales) FROM store_info; -- 返回Sales的最大值
- SELECT sum(Sales) FROM store_info; -- 返回Sales的总和
字符串函数用于操作字符串数据。
- concat(x, y): 将x和y拼接成一个字符串。
- substr(x, y): 从字符串x的第y个位置开始获取子字符串（注意：在某些数据库中，索引可能从1开始，也可能从0开始，这取决于详细的数据库系统）。
- substr(x, y, z): 从字符串x的第y个位置开始获取长度为z的子字符串。
- length(x): 返回字符串x的长度。
- replace(x, y, z): 将字符串x中的y更换为z。
- trim(): 返归去除指定格式（如空格）的值。可以指定从字符串的起头、结尾或起头及结尾移除的字符。
- upper(x): 将字符串x转换为大写。
- lower(x): 将字符串x转换为小写。
- left(x, y): 返回字符串x的前y个字符。
- right(x, y): 返回字符串x的后y个字符。
- repeat(x, y): 将字符串x重复y次。
- space(x): 返回x个空格构成的字符串。
- strcmp(x, y): 比力x和y，返回-1（x<y）、0（x=y）或1（x>y）。
- reverse(x): 将字符串x反转。
示例：
- SELECT concat(Region, ' ', Store_Name) FROM location WHERE Store_Name = 'Boston';
- -- 假设Region为'East'，则返回'East Boston'
- SELECT substr('Hello World', 7);
- -- 返回'World'（假设索引从1开始）
- SELECT TRIM(LEADING 'New ' FROM 'New York');
- -- 返回'York'
- SELECT REPLACE('Hello World', 'World', 'SQL');
- -- 返回'Hello SQL'
- 在使用substr函数时，请注意不同数据库系统中字符串索引的起始值可能不同（从0或1开始）。
- TRIM函数的语法可能因数据库系统而异。上述示例中的语法是通用的，但详细实现可能需要根据您使用的数据库系统进行调整。
- 在执行SQL查询之前，请确保您已经精确毗连到了数据库，而且表名和列名与您的数据库架构相匹配。
- 以下是对您提供的SQL查询相干内容的整理，包罗GROUP BY、HAVING、别名、子查询、EXISTS以及毗连查询的详细解释和示例。
GROUP BY
GROUP BY用于对查询结果进行分组，通常与聚合函数（如SUM、COUNT、AVG等）一起使用。其原则如下：
- 在GROUP BY后面出现的字段，必须在SELECT后面出现。
- 在SELECT后面出现且未在聚合函数中使用的字段，必须出现在GROUP BY后面。
示例：
- SELECT Store_Name, SUM(Sales) AS TotalSales FROM store_info GROUP BY Store_Name ORDER BY TotalSales DESC;
HAVING用于过滤GROUP BY语句返回的记录集，通常与GROUP BY一起使用。它答应使用聚合函数作为过滤条件，这是WHERE子句所不具备的。
示例：
- SELECT Store_Name, SUM(Sales) AS TotalSales FROM store_info GROUP BY Store_Name HAVING SUM(Sales) > 1500;
WHERE
- 作用: WHERE 子句用于在数据被分组或聚合之前过滤行。
- 实用场景: 通常用于基于单行的条件来过滤数据。比方，选择特定列的值满足某个条件的行。
- 数据类型: 可以使用各种条件表达式，如比力运算符（=、<、>、<>、<=、>=）、逻辑运算符（AND、OR、NOT）等。
- 执行顺序: 在GROUP BY之前执行。
示例:
- SELECT * FROM employees
- WHERE age > 30;
HAVING
- 作用: HAVING 子句用于在数据被分组和聚合之后过滤组。
- 实用场景: 通常用于基于聚合函数（如SUM、AVG、COUNT、MAX、MIN）的结果来过滤组。
- 数据类型: 通常与聚合函数一起使用，而且可以使用比力运算符和逻辑运算符。
- 执行顺序: 在GROUP BY之后执行。
示例:
- SELECT department, COUNT(*) AS num_employees
- FROM employees
- GROUP BY department
- HAVING COUNT(*) > 10;
总结
- WHERE 用于在数据分组前过滤行。
- HAVING 用于在数据分组和聚合后过滤组。
注意
- WHERE 子句不能包含聚合函数，而 HAVING 子句则可以。
- HAVING 通常与 GROUP BY 一起使用，而 WHERE 不肯定需要 GROUP BY。
别名
别名分为字段别名和表格别名，用于简化查询结果或提高可读性。
字段别名示例：
- SELECT Store_Name AS Store, SUM(Sales) AS TotalSales FROM store_info;
- SELECT A.Store_Name, SUM(A.Sales) AS TotalSales FROM store_info AS A GROUP BY A.Store_Name;
子查询是在另一个SQL查询中嵌套另一个SQL查询。子查询可以出现在WHERE子句或HAVING子句中。
示例：
- SELECT SUM(Sales) FROM store_info WHERE Store_Name IN (SELECT Store_Name FROM location WHERE Region = 'West');
EXISTS用于查抄子查询是否返回任何结果。假如子查询返回至少一行结果，则外部查询的结果将包含该行。
示例：
- SELECT * FROM store_info A WHERE EXISTS (SELECT 1 FROM location B WHERE B.Store_Name = A.Store_Name);
毗连查询用于从多个表中检索数据。常见的毗连类型包罗内毗连（INNER JOIN）、左毗连（LEFT JOIN）和右毗连（RIGHT JOIN）。
毗连查询用于从多个表中检索相干数据。在关系型数据库中，数据通常分布在多个表中，每个表包含特定的信息。毗连查询通过联结字段（通常是主键和外键）将这些表关联起来，从而答应用户在一个查询中从多个表中获取数据。
内毗连（INNER JOIN）
内毗连只返回两个表中联结字段相等的行。假如联结字段在两个表中不匹配，则这些行不会出现在结果会合。
示例：
- SELECT * FROM location A INNER JOIN store_info B ON A.Store_Name = B.Store_Name;
别的，内毗连还可以使用WHERE子句来实现，而不是使用INNER JOIN语法：
- SELECT * FROM location A, store_info B WHERE A.Store_Name = B.Store_Name;
左毗连（LEFT JOIN）
左毗连返回包罗左表（位于JOIN操作左侧的表）中的所有记录和右表中联结字段相等的记录。假如右表中没有与左表匹配的行，则结果会合的这些行将包含NULL值。
示例：
- SELECT * FROM location A LEFT JOIN store_info B ON A.Store_Name = B.Store_Name;
右毗连（RIGHT JOIN）
右毗连与左毗连类似，但它返回的是右表（位于JOIN操作右侧的表）中的所有记录和左表中联结字段相等的记录。
示例：
- SELECT * FROM location A RIGHT JOIN store_info B ON A.Store_Name = B.Store_Name;
使用聚合函数和内毗连的示例
- SELECT A.Region AS REGION, SUM(B.Sales) AS SALES
- FROM location A
- INNER JOIN store_info B ON A.Store_Name = B.Store_Name
- GROUP BY A.Region;
视图（View）详解
视图的基本概念
视图（View）是数据库中的一种虚拟表，它并不存储实际的数据，而是存储了一个查询的定义。当你查询视图时，数据库会根据视图的定义动态地生成结果集，就像查询一个实际的表一样。视图的主要作用是简化复杂查询、提高查询的可读性和安全性。
视图与表的区别
- 数据存储：表是存储数据的实际布局，而视图不存储数据，只存储查询的定义。
- 更新操作：虽然视图可以像表一样进行查询操作，但并非所有的视图都支持更新操作（如插入、更新、删除）。这取决于视图的定义是否答应这些操作。
- 持久性：表是持久存储数据的布局，而视图在数据库中是持久的定义，但不像临时表那样在用户会话结束后消失。
视图的用途
- 简化复杂查询：通过视图，可以将复杂的查询逻辑封装起来，使得用户可以通过简单的查询语句获取所需的数据。
- 数据抽象：视图提供了一种从底层数据表中抽象出数据的方式，使得用户无需关心底层表的布局和复杂性。
- 安全性：通过视图，可以限制用户对数据的访问权限，只暴露给用户需要的数据，提高数据的安全性。
视图的创建与删除
- 创建视图：使用CREATE VIEW语句创建视图。语法如下：
- CREATE VIEW "视图表名" AS "SELECT 语句";
- CREATE VIEW V_REGION_SALES AS SELECT A.Region AS REGION, SUM(B.Sales) AS SALES
- FROM location A
- INNER JOIN store_info B ON A.Store_Name = B.Store_Name
- GROUP BY A.Region;
- 查询视图：创建视图后，可以使用SELECT语句查询视图，就像查询一个实际的表一样：
- SELECT * FROM V_REGION_SALES;
- 删除视图：使用DROP VIEW语句删除视图。语法如下：比方，删除V_REGION_SALES视图：
- DROP VIEW V_REGION_SALES;
注意事项
- 性能：虽然视图可以简化查询，但在某些环境下，使用视图可能会降低查询性能，因为每次查询视图时，数据库都需要执行视图定义中的查询。
- 更新限制：并非所有的视图都支持更新操作。假如视图涉及多表毗连、聚合函数、子查询等复杂操作，那么视图可能不支持更新。
- 权限管理：通过视图，可以精细地控制用户对数据的访问权限，提高数据的安全性。
视图是数据库中的一种强大工具，通过它可以简化复杂查询、提高查询的可读性和安全性。但在使用视图时，也需要注意其可能带来的性能问题和更新限制。
UNION 和 UNION ALL 详细解说
UNION 操作符
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。这些 SELECT 语句必须返回相同数目标列，而且这些列的数据类型必须兼容。UNION 操作符会自动去除结果会合的重复行。
语法：
- [SELECT 语句 1] UNION [SELECT 语句 2];
- SELECT Store_Name FROM location
- UNION
- SELECT Store_Name FROM store_info;
UNION ALL 操作符
UNION ALL 操作符与 UNION 类似，也用于合并两个或多个 SELECT 语句的结果集。但是，UNION ALL 不会去除结果会合的重复行。
语法：
- [SELECT 语句 1] UNION ALL [SELECT 语句 2];
- SELECT Store_Name FROM location
- UNION ALL
- SELECT Store_Name FROM store_info;
- 使用 UNION 的示例中，假设 location 表和 store_info 表中有一些相同的 Store_Name 值，这些值在最终的结果会合只会出现一次。
- 使用 UNION ALL 的示例中，假设 location 表和 store_info 表中有一些相同的 Store_Name 值，这些值在最终的结果会合会出现多次，每次出现都表示它们分别来自哪个表。
在这个例子中，UNION ALL 操作符合并了 location 表和 store_info 表中 Store_Name 列的结果集，并保留了所有重复的商店名称。
注意事项
- 列数和数据类型：使用 UNION 或 UNION ALL 时，每个 SELECT 语句必须返回相同数目标列，而且这些列的数据类型必须兼容。
- 排序：默认环境下，UNION 和 UNION ALL 操作符的结果集是按照列的顺序进行排序的，但这并不意味着结果集是按照某个特定的列排序的。假如需要排序，可以使用 ORDER BY 子句。
- 性能：UNION ALL 通常比 UNION 更快，因为 UNION 需要执行额外的步调往复除重复行。
- NULL 值：在 UNION 或 UNION ALL 的结果会合，NULL 值被视为相同的值。因此，假如两个 SELECT 语句的结果会合都有 NULL 值，这些 NULL 值在 UNION 的结果会合只会出现一次（除非使用 UNION ALL）。
交集值
1. 使用INNER JOIN获取交集值
- SELECT A.Store_Name
- FROM location A
- INNER JOIN store_info B
- ON A.Store_Name = B.Store_Name;
2. 使用USING子句简化INNER JOIN
- SELECT A.Store_Name
- FROM location A
- INNER JOIN store_info B
- USING(Store_Name);
3. 使用DISTINCT确保结果无重复
- SELECT DISTINCT A.Store_Name
- FROM location A
- INNER JOIN store_info B
- USING(Store_Name);
4. 使用IN子句获取交集值
- SELECT DISTINCT Store_Name
- FROM location
- WHERE Store_Name IN (SELECT Store_Name FROM store_info);
5. 使用LEFT JOIN和IS NOT NULL条件获取交集值
- SELECT DISTINCT A.Store_Name
- FROM location A
- LEFT JOIN store_info B
- USING(Store_Name)
- WHERE B.Store_Name IS NOT NULL;
6. 使用子查询和GROUP BY
- SELECT A.Store_Name
- FROM (SELECT B.Store_Name FROM location B INNER JOIN store_info C ON B.Store_Name = C.Store_Name) A
- GROUP BY A.Store_Name;
7. 使用UNION ALL和HAVING条件获取交集值
- SELECT A.Store_Name
- FROM
- (SELECT DISTINCT Store_Name FROM location
- UNION ALL
- SELECT DISTINCT Store_Name FROM store_info) A
- GROUP BY A.Store_Name
- HAVING COUNT(*) > 1;
总结：
- 在大多数环境下，使用INNER JOIN或IN子句是获取两个查询结果交集的最简单且最高效的方法。
- DISTINCT在INNER JOIN查询中通常是多余的，因为毗连条件已经确保了唯一性。
- 制止使用不必要的复杂查询，如结合UNION ALL和GROUP BY来模拟INNER JOIN的举动。
差集值
表现第一个SQL语句的结果，且与第二个SQL语句没有交集的结果，且没有重复
- SELECT DISTINCT Store_Name
- FROM location
- WHERE Store_Name NOT IN (SELECT Store_Name FROM store_info);
- 这个查询从location表中选择所有不在store_info表中的Store_Name。
- DISTINCT关键字确保结果中没有重复的Store_Name。
- NOT IN子句用于过滤掉那些在store_info表中存在的Store_Name。
- SELECT DISTINCT A.Store_Name
- FROM location A
- LEFT JOIN store_info B USING(Store_Name)
- WHERE B.Store_Name IS NULL;
- 这个查询使用左毗连（LEFT JOIN）来毗连location和store_info表，基于Store_Name字段。
- USING(Store_Name)表示毗连条件是基于两个表中的Store_Name字段。
- WHERE B.Store_Name IS NULL这个条件确保了只有那些在location表中存在但在store_info表中不存在的Store_Name被选中。
- DISTINCT关键字同样用于确保结果中没有重复的Store_Name。
- SELECT A.Store_Name
- FROM
- (SELECT DISTINCT Store_Name FROM location UNION ALL SELECT DISTINCT Store_Name FROM store_info) A
- GROUP BY A.Store_Name
- HAVING COUNT(*) = 1;
- 这个查询起首通过UNION ALL将location和store_info表中的Store_Name合并到一个临时表A中。注意这里使用UNION ALL而不是UNION，因为UNION会默认去除重复值，而UNION ALL不会，但随后通过外层查询的DISTINCT（只管在这个子查询的上下文中没有直接写出，但明白其逻辑时考虑这一点很重要）。然而，在这个特定查询中，由于GROUP BY和HAVING COUNT(*) = 1的使用，UNION ALL与UNION的结果相同，因为最终只选择了在任一表中唯一出现的Store_Name。
- 然后，通过GROUP BY A.Store_Name将结果按Store_Name分组。
- HAVING COUNT(*) = 1这个条件确保了只有那些在合并后的结果会合只出现一次的Store_Name被选中，即那些只在location或store_info表中存在的Store_Name。
- 值得注意的是，虽然这个查询逻辑上没有使用DISTINCT（在子查询中），但由于GROUP BY和HAVING的使用，最终结果中不会有重复的Store_Name。
总结
- 功能等价性：这三个查询在功能上是等价的，它们都用于找出仅在location表中存在而不在store_info表中的Store_Name，且结果中没有重复。
- 性能考虑：在实际应用中，不同数据库系统对NOT IN、LEFT JOIN和UNION/GROUP BY/HAVING的处理服从可能有所不同。通常，LEFT JOIN和NOT EXISTS（虽然这里未使用）可能在某些数据库系统中比NOT IN更高效，尤其是在处理大型数据集时，因为NOT IN可能会受到子查询返回大量结果时性能降落的影响（称为“子查询的爆炸”）。
- 可读性和维护性：从可读性和维护性的角度来看，LEFT JOIN和NOT EXISTS通常被以为比NOT IN更直观，因为它们更明白地表达了“查找在A中但不在B中的记录”这一逻辑。而UNION/GROUP BY/HAVING的方法虽然强大且机动，但在这个特定用例中可能稍显复杂。
CASE 表达式
在SQL中，CASE表达式确实被用作实现类似IF-THEN-ELSE逻辑的工具，它答应在查询中根据条件来返回不同的值。
CASE表达式有两种主要情势：简单CASE表达式和搜索CASE表达式。
语法一：简单CASE表达式
- SELECT CASE "字段名"
- WHEN "数值1" THEN "结果1"
- WHEN "数值2" THEN "结果2"
- ...
- [ELSE "default"]
- END AS "别名"
- FROM "表名";
语法二：搜索CASE表达式
- SELECT CASE
- WHEN "公式1" THEN "结果1"
- WHEN "公式2" THEN "结果2"
- ...
- [ELSE "default"]
- END AS "别名"
- FROM "表名";
示例
- SELECT Store_Name, CASE Store_Name
- WHEN 'Los Angeles' THEN Sales * 2
- WHEN 'Boston' THEN 2000
- ELSE Sales
- END AS "New Sales", Date
- FROM store_info;
- Store_Name 和 Date 是从 store_info 表中直接选择的列。
- CASE Store_Name 实际上是一个搜索CASE表达式的简写情势，因为这里比力的是Store_Name字段的值。
- 当Store_Name为’Los Angeles’时，New Sales列的值为Sales字段的两倍。
- 当Store_Name为’Boston’时，New Sales列的值为2000。
- 对于其他所有Store_Name值，New Sales列的值为原始的Sales值。
- AS "New Sales"给CASE表达式的结果列指定了一个别名New Sales。
注意
- 在使用CASE表达式时，确保每个WHEN子句的条件都是互斥的，即它们之间不应该有重叠，否则只会返回第一个匹配的THEN子句的结果。
- ELSE子句是可选的，但假如没有提供且没有任何WHEN子句匹配，CASE表达式将返回NULL。
- 在给结果列指定别名时，使用双引号（如"New Sales"）可以确保别名中的空格和特殊字符被精确处理。不过，不是所有的数据库系统都要求这样做，有些系统（如MySQL）答应在不使用双引号的环境下使用别名中的空格，但最好遵照标准SQL的约定。
空值(NULL)和无值(空字符串'')的区别
- 存储与长度：
- 无值（空字符串''）：长度为0，但实际上在数据库中它仍然需要占用肯定的存储空间来存储结束符（比方，在C风格的字符串中，\0）。不过，这个空间通常非常小。
- NULL值：在数据库中，NULL是一个特殊的标志，用于表示未知或缺失的值。它的长度不是0，也不是任何详细的数字，而是NULL。NULL的存储实现依赖于详细的数据库系统，但通常它需要一个额外的位或字节来标志字段是否为NULL。
- 判断方式：
- 判断是否为NULL：使用IS NULL或IS NOT NULL。
- 判断是否为空字符串：使用=''（即是空字符串）或<>''（不即是空字符串）。
- 在COUNT()函数中的举动：
- COUNT(*)：计算表中的总行数，包罗所有字段为NULL的行。
- COUNT(列名)：计算指定列中非NULL值的行数。
City 表格
- +----------+
- | name |
- |----------|
- | beijing |
- | nanjing |
- | shanghai |
- | <null> | -- 注意：在实际数据库中，NULL不会以<null>显示，这里仅用于说明
- | <null> |
- | shanghai |
- | | -- 这是空字符串，不是NULL
- +----------+
- -- 查询NULL和空字符串以及普通字符串的长度
- SELECT length(NULL) AS null_length, -- 返回NULL，因为NULL的长度是未知的
- length('') AS empty_string_length, -- 返回0，因为空字符串的长度是0
- length('1') AS one_char_length; -- 返回1，因为字符串'1'的长度是1
- -- 查询name字段为NULL的行
- SELECT * FROM City WHERE name IS NULL; -- 返回两行，其中name字段为NULL
- -- 查询name字段不为NULL的行
- SELECT * FROM City WHERE name IS NOT NULL; -- 返回五行，其中name字段不为NULL
- -- 查询name字段为空字符串的行
- SELECT * FROM City WHERE name = ''; -- 返回一行，其中name字段为空字符串（在实际数据库中可能看起来是空白的）
- -- 查询name字段不为空字符串的行
- SELECT * FROM City WHERE name <> ''; -- 返回六行，其中name字段不为空字符串（包括NULL，但NULL不会匹配这个条件，因为它不是字符串）
- -- 计算City表中的总行数
- SELECT COUNT(*) FROM City; -- 返回7，因为表中有7行
- -- 计算name字段中非NULL值的行数
- SELECT COUNT(name) FROM City; -- 返回5，因为有两行的name字段为NULL
- 在实际数据库中，NULL值不会以<null>的情势表现。当您查询包含NULL值的字段时，结果通常会表现为NULL（无引号）。
- 空字符串''和单个空格' '是不同的。在上面的示例中，有一行name字段看起来是空的（在实际表格中可能表现为空白），但实际上它可能包含一个或多个空格字符，而不是空字符串。要准确区分这两者，您可能需要使用TRIM()函数往复除空格并进行比力。
- 当使用<>（不即是）运算符与NULL进行比力时，结果总是NULL，因为NULL与任何值的比力结果都是未知的。因此，您不能使用<>来查找NULL值；必须使用IS NOT NULL。
SQL正则表达
匹配模式形貌实例SQL查询示例^匹配文本的开始字符‘^bd’ 匹配以 bd 开头的字符串SELECT * FROM store_info WHERE Store_Name REGEXP '^bd';$匹配文本的结束字符‘qn$’ 匹配以 qn 结尾的字符串SELECT * FROM store_info WHERE Store_Name REGEXP 'qn$';.匹配任何单个字符‘s.t’ 匹配任何 s 和 t 之间有一个字符的字符串SELECT * FROM store_info WHERE Store_Name REGEXP 's.t';*匹配零个或多个在它前面的字符‘fo*t’ 匹配 t 前面有任意个 oSELECT * FROM store_info WHERE Store_Name REGEXP 'fo*t';+匹配前面的字符 1 次或多次‘hom+’ 匹配以 ho 开头，后面至少一个m 的字符串SELECT * FROM store_info WHERE Store_Name REGEXP 'hom+';字符串匹配包含指定的字符串‘clo’ 匹配含有 clo 的字符串SELECT * FROM store_info WHERE Store_Name REGEXP 'clo';p1管道符p2匹配 p1 或 p2‘bg管道符fg’ 匹配 bg 或者 fgSELECT * FROM store_info WHERE Store_Name REGEXP 'bg管道符fg';[...]匹配字符集合中的任意一个字符‘[abc]’ 匹配 a 或者 b 或者 cSELECT * FROM store_info WHERE Store_Name REGEXP '[abc]';[^...]匹配不在括号中的任何字符‘[^ab]’ 匹配不包含 a 或者 b 的字符串SELECT * FROM store_info WHERE Store_Name REGEXP '[^ab]';{n}匹配前面的字符串 n 次‘g{2}’ 匹配含有 2 个 g 的字符串SELECT * FROM store_info WHERE Store_Name REGEXP 'g{2}';{n,m}匹配前面的字符串至少 n 次，至多 m 次‘f{1,3}’ 匹配 f =最少 1 次，最多 3 次SELECT * FROM store_info WHERE Store_Name REGEXP 'f{1,3}'; `
存储过程
存储过程是一组为了完成特定功能的SQL语句集合。它答应将常用的或复杂的SQL操作封装起来，存储于数据库中，以便将来重复使用。通过使用存储过程，可以显著提高数据库操作的服从、简化客户端应用程序的开辟和维护。
存储过程的长处
- 提高执行服从：存储过程经编译和优化后存储在数据库服务器中，执行时不需要再次编译，且生成的二进制代码驻留在缓冲区中，提高了执行服从。
- 机动性强：存储过程结合了SQL语句和控制语句（如条件判断、循环等），使得复杂操作变得更加机动和方便。
- 降低网络负载：由于存储过程存储在服务器端，客户端调用时只需发送调用请求，不需要传输整个SQL语句，从而降低了网络负载。
- 可重用性和可维护性：存储过程可以被多次调用，且可以随时修改而不影响客户端的调用。这提高了代码的可重用性和可维护性。
- 安全性：通过存储过程，可以严格控制数据库的访问权限，确保只有授权的用户才能执行特定的操作。
创建存储过程
创建存储过程的基本语法如下：
- DELIMITER $$
- CREATE PROCEDURE 存储过程名([参数列表])
- BEGIN
- -- 存储过程体（SQL语句和控制语句）
- END $$
- DELIMITER ;
调用存储过程
使用CALL语句调用存储过程：
查看存储过程
使用以下命令查看存储过程的信息：
- SHOW CREATE PROCEDURE [数据库.]存储过程名;：查看存储过程的定义。
- SHOW PROCEDURE STATUS [LIKE '%模式%'] \G：查看存储过程的状态信息，其中\G表示以垂直格式表现结果。
存储过程的参数
存储过程的参数分为三种类型：
- IN 输入参数：调用者向存储过程传入值，可以是字面量或变量。
- OUT 输出参数：存储过程向调用者传出值，可以返回多个值，但只能是变量。
- INOUT 输入输出参数：既表示调用者向存储过程传入值，又表示存储过程向调用者传出值，值只能是变量。
示例
- DELIMITER $$
- CREATE PROCEDURE Proc()
- BEGIN
- SELECT * FROM store_info;
- END $$
- DELIMITER ;
- CALL Proc;
- DELIMITER $$
- CREATE PROCEDURE Proc1(IN inname CHAR(16))
- BEGIN
- SELECT * FROM store_info WHERE Store_Name = inname;
- END $$
- DELIMITER ;
- CALL Proc1('Boston');
- DELIMITER $$
- CREATE PROCEDURE Proc3(IN myname CHAR(10), OUT outname INT)
- BEGIN
- SELECT sales INTO outname FROM t1 WHERE name = myname;
- END $$
- DELIMITER ;
- CALL Proc3('yzh', @out_sales);
- SELECT @out_sales;
- DELIMITER $$
- CREATE PROCEDURE Proc4(INOUT insales INT)
- BEGIN
- SELECT COUNT(sales) INTO insales FROM t1 WHERE sales < insales;
- END $$
- DELIMITER ;
- SET @inout_sales = 1000;
- CALL Proc4(@inout_sales);
- SELECT @inout_sales;
在MySQL中，删除存储过程使用DROP PROCEDURE语句。假如该存储过程不存在，而你又不想产生错误，可以在DROP PROCEDURE语句前加上IF EXISTS条件。这样，假如存储过程存在，它将被删除；假如不存在，则不会产生任何错误。
- DROP PROCEDURE IF EXISTS Proc;
存储过程的控制语句
存储过程中可以使用各种控制语句来实现复杂的逻辑，包罗条件语句和循环语句。
条件语句（if-then-else … end if）
条件语句答应根据条件执行不同的SQL语句。以下是一个使用if-then-else语句的存储过程示例：
- DELIMITER $$
- CREATE PROCEDURE proc2(IN pro INT)
- BEGIN
- DECLARE var INT;
- SET var = pro * 2;
- IF var >= 10 THEN
- UPDATE t SET id = id + 1;
- ELSE
- UPDATE t SET id = id - 1;
- END IF;
- END $$
- DELIMITER ;
循环语句（while … end while）
循环语句答应重复执行一段SQL代码，直到满足某个条件为止。以下是一个使用while循环的存储过程示例：
- DELIMITER $$
- CREATE PROCEDURE proc3()
- BEGIN
- DECLARE var INT DEFAULT 0;
- WHILE var < 6 DO
- INSERT INTO t VALUES(var);
- SET var = var + 1;
- END WHILE;
- END $$
- DELIMITER ;
调用存储过程
要执行上述存储过程，可以使用CALL语句：
- CALL proc2(6); -- 调用存储过程proc2，传入参数6
- CALL proc3(); -- 调用存储过程proc3，不需要传入参数
- 在创建存储过程之前，确保所使用的数据库是当前数据库。
- 存储过程中的SQL语句应该遵照数据库的语法规则。
- 假如存储过程中涉及到对表的更新或插入操作，请确保具有相应的权限。
- 在删除存储过程之前，请确保没有其他应用程序或用户正在使用该存储过程，以制止数据丢失或应用程序错误。
问答环节+简要总结
MySQL查询数据的执行过程是什么
1）客户端向 MySQL 服务器发送一条查询请求，毗连器负责处理毗连，并进行身份验证和权限控制。
2）MySQL先查抄查询缓存（查询缓存在MySQL8.0中已被删除），假如命中缓存，则立即返回存储在缓存中的结果；否则使用查询剖析器进行SQL语句剖析、预处理，再由优化器生成对应的执行计划。
3）MySQL根据执行计划，调用存储引擎来执行查询。
4）将结果返回给客户端，同时缓存查询结果。
SQL查询语句基本布局
- SELECT 字段列表
- FROM 表名
- WHERE 字段 = 值
- AND/OR 字段 = 值;
- SELECT 字段列表：指定要查询的字段。
- FROM 表名：指定要查询的表。
- WHERE 字段 = 值
：指定查询条件。
字段条件运算符
- 即是 (=)：
- 不即是 (!= 或 <>)：
- WHERE 字段 != 值
- WHERE 字段 <> 值
- 大于 (>)：
- 大于即是 (>=)：
- 小于 (<)：
- 小于即是 (<=)：
- IN：
- WHERE 字段 IN (值1, 值2, ...)
- BETWEEN：
- WHERE 字段 BETWEEN 值1 AND 值2
- LIKE：
示例：
- WHERE 字段 LIKE '%abc%' -- 包含 'abc' 的任意位置
- WHERE 字段 LIKE '_bc' -- 第一个字符任意，后面是 'bc'
- REGEXP（正则表达式）：
示例：
- WHERE 字段 REGEXP '^abc' -- 以 'abc' 开头
- WHERE 字段 REGEXP 'xyz$' -- 以 'xyz' 结尾
- WHERE 字段 REGEXP '.bc' -- 任意字符后跟 'bc'
- WHERE 字段 REGEXP 'a.*c' -- 以 'a' 开头，任意字符，以 'c' 结尾
SQL基本操作
- 去重
- SELECT DISTINCT 字段 FROM 表;
- 排序
- SELECT 字段列表 FROM 表 [WHERE 条件] ORDER BY 字段 ASC|DESC;
- 分组与聚合
- SELECT 字段1, 聚合函数(字段2) FROM 表 GROUP BY 字段1;
- SELECT 字段1, 聚合函数(字段2) FROM 表 GROUP BY 字段1 HAVING 条件表达式;
MySQL函数
- 聚合函数：AVG(), SUM(), MIN(), MAX(), COUNT(), COUNT(*)
- 数学函数：RAND(), ROUND(x), ROUND(x,y), TRUNCATE(x,y), GREATEST(), LEAST(), MOD(x,y), POWER(x,y)
- 字符串函数：CONCAT(), SUBSTR(), REPLACE(), LENGTH(), UPPER(), LOWER(), LEFT(), RIGHT()
别名
- SELECT 字段 [AS] 字段别名 FROM 表 [AS] 表别名;
- SELECT 字段 FROM 表1 WHERE 字段 IN (SELECT 字段 FROM 表3 WHERE 条件表达式);
- SELECT 字段 FROM 表1 WHERE EXISTS (SELECT 字段 FROM 表3 WHERE 条件表达式);
- 内毗连：
- SELECT A.字段 FROM 左表 A INNER JOIN 右表 B ON A.字段 = B.字段;
- 左毗连：
- SELECT A.字段 FROM 左表 A LEFT JOIN 右表 B ON A.字段 = B.字段;
- 右毗连：
- SELECT A.字段 FROM 左表 A RIGHT JOIN 右表 B ON A.字段 = B.字段;
集合操作
- 并集（去重）：
- SELECT 字段 FROM 表1 UNION SELECT 字段 FROM 表2;
- 并集（不去重）：
- SELECT 字段 FROM 表1 UNION ALL SELECT 字段 FROM 表2;
差集
- 左表差集：
- SELECT A.字段 FROM 左表 A LEFT JOIN 右表 B ON A.字段 = B.字段 WHERE B.字段 IS NULL;
- 右表差集：
- SELECT B.字段 FROM 左表 A RIGHT JOIN 右表 B ON A.字段 = B.字段 WHERE A.字段 IS NULL;
视图
- 创建视图：
- CREATE VIEW 视图表名 AS <SELECT语句>;
- 删除视图：
多表查询与分组
- SELECT A.字段 FROM (SELECT DISTINCT 字段 FROM 左表 UNION ALL SELECT DISTINCT 字段 FROM 右表) AS A GROUP BY A.字段 HAVING COUNT(A.字段) > 1;
假如定义的select语句查询的字段是没有被处理过的源表字段，则可以通过视图表修改源表的数据
假如定义的select语句查询的字段是被函数或group by等命令处理过的字段，则不能直接修改视图表的数据
常见的CASE语句用法：
第一种情势：基于字段值的条件
- SELECT
- CASE 字段名
- WHEN '值1' THEN 结果1
- WHEN '值2' THEN 结果2
- ELSE 默认结果
- END AS 'case字段别名'
- FROM 表名;
第二种情势：基于条件的条件
- SELECT
- CASE
- WHEN 字段='值1' THEN 结果1
- WHEN 字段='值2' THEN 结果2
- ELSE 默认结果
- END AS 'case字段别名'
- FROM 表名;
无值’’ 和 空值NULL 的区别？
无值’’ 的长度为 0，不占用空间；可以通过 字段名 = ‘’ 字段名 !=或<> ‘’ 来过滤字段的值是否为无值的行；指定字段使用函数 count(字段) 不会忽略无值的行
空值NULL 的长度为 NULL，占用空间；可以通过 字段名 is null 字段名 is not null 来过滤字段的值是否为NULL的行；指定字段使用函数 count(字段) 会忽略NULL的行
Mysql输入数据的方法？
1）insert into
2）load date infile ‘csv文件’
3）使用第三方客户端工具，比如 navicat
Mysql导出导入CSV文件
1）修改mysql配置文件，在[mysqld]配置项下面添加 secure_file_priv=“” ，重启服务
2）创建导出目次，并授权权限 mkdir mysql-output; chown mysql:mysql mysql-output/
3）select * into outfile ‘/opt/test/xy103.csv’ fields terminated by ‘,’ enclosed by ‘"’ lines terminated by ‘\n’ from xy103; #导出表数据到CSV文件中
load data infile ‘/opt/test/xy103.csv’ into table xy103 fields terminated by ‘,’ enclosed by ‘"’ lines terminated by ‘\n’; #导入CSV文件数据到mysql表中
导入CSV文件时，可能会遇到这些参数。用于指定CSV文件的格式，以确保数据能够精确地被剖析和导入
- fields terminated by ',' #指定CVS文件的字段分隔符
- enclosed by '"' #指定CVS文件的字段内容边界符
- lines terminated by '\n' #指定CVS文件的行分隔符
仅保留一条：
create view 视图表名 as select min(id) from 表 group by 重复的字段名;
delete from 表 where id not in (select 字段 from 视图表名);
一条不留：
create view 视图表名 as select 重复的字段名 group by 重复的字段名 having count(字段) > 1;
delete from 表 where 重复的字段名 in (select 字段 from 视图表名);
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
|
发表于 2024-10-21 21:23:07
