北京网站优化招聘,wordpress 上传 七牛,做网站怎么导入源码,郴州公司注册文章目录 SQL语句进阶版MySQL查询数据的过程一、连接与身份验证二、查询缓存#xff08;MySQL 8.0之前版本#xff09;三、查询解析与优化四、查询执行五、返回结果 MySQL语句准备环境创建 location 表并插入数据创建 store_info 表并插入数据查询示例 语句示例SELECTDISTINC… 文章目录 SQL语句进阶版MySQL查询数据的过程一、连接与身份验证二、查询缓存MySQL 8.0之前版本三、查询解析与优化四、查询执行五、返回结果 MySQL语句准备环境创建 location 表并插入数据创建 store_info 表并插入数据查询示例 语句示例SELECTDISTINCTWHEREAND ORINBETWEEN通配符LIKEORDER BY函数数学函数聚合函数字符串函数 GROUP BYHAVING别名子查询EXISTS连接查询内连接INNER JOIN左连接LEFT JOIN右连接RIGHT JOIN使用聚合函数和内连接的示例 视图View详解视图的基本概念视图与表的区别视图的用途视图的创建与删除注意事项 UNION 和 UNION ALL 详细讲解UNION 操作符UNION ALL 操作符注意事项 交集值1. 使用INNER JOIN获取交集值2. 使用USING子句简化INNER JOIN3. 使用DISTINCT确保结果无重复4. 使用IN子句获取交集值5. 使用LEFT JOIN和IS NOT NULL条件获取交集值6. 使用子查询和GROUP BY7. 使用UNION ALL和HAVING条件获取交集值 差集值总结 CASE 表达式语法一简单CASE表达式语法二搜索CASE表达式示例 空值(NULL)和无值(空字符串)的区别SQL正则表达存储过程存储过程的优点创建存储过程调用存储过程查看存储过程存储过程的参数示例删除存储过程存储过程的控制语句调用存储过程 问答环节简要总结 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 表并插入数据
-- 创建 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 |
--------------------您已经列出了SQL查询中一些非常基础且重要的部分包括SELECT语句、DISTINCT关键字、WHERE子句、逻辑运算符AND和OR、IN操作符、BETWEEN操作符以及通配符的使用。下面我将对每部分进行更详细的讲解
语句示例
SELECT
SELECT语句用于从数据库表中检索数据。您可以指定要检索的字段或者使用*来选择所有字段。 示例
SELECT Store_Name FROM store_info; -- 仅选择Store_Name字段
SELECT * FROM store_info; -- 选择所有字段DISTINCT
DISTINCT关键字用于返回唯一不同的值。它通常与SELECT语句一起使用以消除结果集中的重复行。 示例
SELECT DISTINCT Store_Name FROM store_info; -- 仅返回不重复的Store_NameWHERE
WHERE子句用于过滤记录只返回满足指定条件的记录。 示例
SELECT Store_Name FROM store_info WHERE Sales 1000; -- 返回Sales大于1000的Store_NameAND OR
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注意在给出的示例中条件(Sales 500 AND Sales 200)实际上是一个不可能的情况因为没有一个数字能同时小于500且大于200。这里可能是为了演示逻辑运算符的用法而给出的示例。
IN
IN操作符允许您指定多个可能的值返回字段值等于这些值之一的记录。 示例
SELECT * FROM store_info WHERE Store_Name IN (Los Angeles, Houston); -- 返回Store_Name为Los Angeles或Houston的记录BETWEEN
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
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, 2SELECT 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返回-1xy、0xy或1xy。reverse(x): 将字符串x反转。
示例
SELECT concat(Region, , Store_Name) FROM location WHERE Store_Name Boston;
-- 假设Region为East则返回East BostonSELECT substr(Hello World, 7);
-- 返回World假设索引从1开始SELECT TRIM(LEADING New FROM New York);
-- 返回YorkSELECT 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
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与HAVING之间的区别 WHERE 作用: WHERE 子句用于在数据被分组或聚合之前过滤行。适用场景: 通常用于基于单行的条件来过滤数据。例如选择特定列的值满足某个条件的行。数据类型: 可以使用各种条件表达式如比较运算符、、、、、、逻辑运算符AND、OR、NOT等。执行顺序: 在GROUP BY之前执行。 示例: SELECT * FROM employees
WHERE age 30;这个查询会选择所有年龄大于30的员工。 HAVING 作用: HAVING 子句用于在数据被分组和聚合之后过滤组。适用场景: 通常用于基于聚合函数如SUM、AVG、COUNT、MAX、MIN的结果来过滤组。数据类型: 通常与聚合函数一起使用并且可以使用比较运算符和逻辑运算符。执行顺序: 在GROUP BY之后执行。 示例: SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department
HAVING COUNT(*) 10;这个查询会选择员工数量大于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
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;这条语句从location和store_info两个表中检索数据只返回那些Store_Name字段值在两个表中都存在的行。 另外内连接还可以使用WHERE子句来实现而不是使用INNER JOIN语法
SELECT * FROM location A, store_info B WHERE A.Store_Name B.Store_Name;这条语句与上面的INNER JOIN语句等效。
左连接LEFT JOIN
左连接返回包括左表位于JOIN操作左侧的表中的所有记录和右表中联结字段相等的记录。如果右表中没有与左表匹配的行则结果集中的这些行将包含NULL值。 示例
SELECT * FROM location A LEFT JOIN store_info B ON A.Store_Name B.Store_Name;这条语句从location表中检索所有行并尝试将它们与store_info表中的行匹配。如果store_info表中没有与location表中的Store_Name匹配的行则结果集中的这些store_info表的列将包含NULL值。
右连接RIGHT JOIN
右连接与左连接类似但它返回的是右表位于JOIN操作右侧的表中的所有记录和左表中联结字段相等的记录。 示例
SELECT * FROM location A RIGHT JOIN store_info B ON A.Store_Name B.Store_Name;这条语句从store_info表中检索所有行并尝试将它们与location表中的行匹配。如果location表中没有与store_info表中的Store_Name匹配的行则结果集中的这些location表的列将包含NULL值。
使用聚合函数和内连接的示例
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;这条语句首先使用内连接从location和store_info两个表中检索数据只返回那些Store_Name字段值在两个表中都存在的行。然后它使用GROUP BY子句按location表中的Region字段对结果进行分组。最后它使用SUM函数计算每个区域的总销售额并将结果集中的列重命名为REGION和SALES。
视图View详解
视图的基本概念
视图View是数据库中的一种虚拟表它并不存储实际的数据而是存储了一个查询的定义。当你查询视图时数据库会根据视图的定义动态地生成结果集就像查询一个实际的表一样。视图的主要作用是简化复杂查询、提高查询的可读性和安全性。
视图与表的区别
数据存储表是存储数据的实际结构而视图不存储数据只存储查询的定义。更新操作虽然视图可以像表一样进行查询操作但并非所有的视图都支持更新操作如插入、更新、删除。这取决于视图的定义是否允许这些操作。持久性表是持久存储数据的结构而视图在数据库中是持久的定义但不像临时表那样在用户会话结束后消失。
视图的用途
简化复杂查询通过视图可以将复杂的查询逻辑封装起来使得用户可以通过简单的查询语句获取所需的数据。数据抽象视图提供了一种从底层数据表中抽象出数据的方式使得用户无需关心底层表的结构和复杂性。安全性通过视图可以限制用户对数据的访问权限只暴露给用户需要的数据提高数据的安全性。
视图的创建与删除
创建视图使用CREATE VIEW语句创建视图。语法如下CREATE VIEW 视图表名 AS SELECT 语句;例如创建一个名为V_REGION_SALES的视图该视图显示每个地区的销售总额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语句删除视图。语法如下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 操作符合并了 location 表和 store_info 表中 Store_Name 列的结果集并去除了重复的商店名称。
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;这个查询通过INNER JOIN连接location和store_info两个表并基于Store_Name字段匹配记录。只有当两个表中都存在相同的Store_Name时该名称才会出现在结果集中。
2. 使用USING子句简化INNER JOIN
SELECT A.Store_Name
FROM location A
INNER JOIN store_info B
USING(Store_Name);这个查询与上一个查询功能相同但USING子句简化了连接条件因为它自动知道要基于哪个字段在本例中是Store_Name进行连接。
3. 使用DISTINCT确保结果无重复
SELECT DISTINCT A.Store_Name
FROM location A
INNER JOIN store_info B
USING(Store_Name);由于INNER JOIN已经确保了只有匹配的记录会出现在结果集中因此在这个特定查询中使用DISTINCT是多余的。不过在更复杂的查询中DISTINCT可能有助于去除重复项。
4. 使用IN子句获取交集值
SELECT DISTINCT Store_Name
FROM location
WHERE Store_Name IN (SELECT Store_Name FROM store_info);这个查询首先执行子查询SELECT Store_Name FROM store_info然后检查location表中的Store_Name是否存在于子查询的结果集中。DISTINCT用于确保结果中的每个Store_Name只出现一次。
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;这个查询使用LEFT JOIN来连接两个表并通过检查B.Store_Name是否为NULL来确保只选择那些在store_info表中也有匹配项的Store_Name。DISTINCT用于去除可能的重复项尽管在这个特定查询中可能是多余的因为LEFT JOIN加上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;这个查询首先执行一个子查询来获取交集值然后使用GROUP BY对结果进行分组。然而在这个特定情况下GROUP BY是多余的因为子查询已经确保了每个Store_Name只出现一次由于使用了INNER JOIN。
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;这个查询首先使用UNION ALL将两个表中的Store_Name合并起来然后在外层查询中使用GROUP BY和HAVING来找出那些在两个表中都出现的Store_Name。UNION ALL不会去除重复记录所以如果一个Store_Name在两个表中都出现它在合并后的结果集中会出现两次。HAVING COUNT(*) 1条件确保只有那些出现次数大于1的Store_Name被选中即两个表共有的Store_Name。
总结
在大多数情况下使用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 结果1WHEN 数值2 THEN 结果2...[ELSE default]END AS 别名
FROM 表名;在这个形式中CASE后面直接跟的是要比较的字段名。然后是一系列的WHEN子句每个子句都指定了一个可能的值以及当字段名等于该值时应该返回的结果。ELSE子句是可选的用于指定当没有任何WHEN子句匹配时的默认值。最后END标记了CASE表达式的结束AS 别名用于给结果列指定一个别名。
语法二搜索CASE表达式
SELECT CASEWHEN 公式1 THEN 结果1WHEN 公式2 THEN 结果2...[ELSE default]END AS 别名
FROM 表名;在这个形式中CASE后面不跟任何字段名而是直接跟一系列的WHEN子句。每个WHEN子句都包含了一个布尔表达式即“公式”当该表达式为真时返回相应的结果。同样ELSE子句是可选的END标记了表达式的结束AS 别名用于给结果列指定别名。
示例
SELECT Store_Name, CASE Store_Name WHEN Los Angeles THEN Sales * 2 WHEN Boston THEN 2000ELSE 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
----------SQL 查询
-- 查询NULL和空字符串以及普通字符串的长度
SELECT length(NULL) AS null_length, -- 返回NULL因为NULL的长度是未知的length() AS empty_string_length, -- 返回0因为空字符串的长度是0length(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 ;其中DELIMITER命令用于更改语句的结束符号以避免与存储过程体中的分号冲突。
调用存储过程
使用CALL语句调用存储过程
CALL 存储过程名([参数值]);查看存储过程
使用以下命令查看存储过程的信息
SHOW CREATE PROCEDURE [数据库.]存储过程名;查看存储过程的定义。SHOW PROCEDURE STATUS [LIKE %模式%] \G查看存储过程的状态信息其中\G表示以垂直格式显示结果。
存储过程的参数
存储过程的参数分为三种类型
IN 输入参数调用者向存储过程传入值可以是字面量或变量。OUT 输出参数存储过程向调用者传出值可以返回多个值但只能是变量。INOUT 输入输出参数既表示调用者向存储过程传入值又表示存储过程向调用者传出值值只能是变量。
示例
无参数存储过程
DELIMITER $$
CREATE PROCEDURE Proc()
BEGINSELECT * FROM store_info;
END $$
DELIMITER ;
CALL Proc;带IN参数的存储过程
DELIMITER $$
CREATE PROCEDURE Proc1(IN inname CHAR(16))
BEGINSELECT * FROM store_info WHERE Store_Name inname;
END $$
DELIMITER ;
CALL Proc1(Boston);带OUT参数的存储过程
DELIMITER $$
CREATE PROCEDURE Proc3(IN myname CHAR(10), OUT outname INT)
BEGINSELECT sales INTO outname FROM t1 WHERE name myname;
END $$
DELIMITER ;
CALL Proc3(yzh, out_sales);
SELECT out_sales;带INOUT参数的存储过程
DELIMITER $$
CREATE PROCEDURE Proc4(INOUT insales INT)
BEGINSELECT 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;这条语句会检查名为Proc的存储过程是否存在如果存在则将其删除。
存储过程的控制语句
存储过程中可以使用各种控制语句来实现复杂的逻辑包括条件语句和循环语句。 条件语句if-then-else … end if 条件语句允许根据条件执行不同的SQL语句。以下是一个使用if-then-else语句的存储过程示例
DELIMITER $$
CREATE PROCEDURE proc2(IN pro INT)
BEGINDECLARE var INT;SET var pro * 2;IF var 10 THENUPDATE t SET id id 1;ELSEUPDATE t SET id id - 1;END IF;
END $$
DELIMITER ;在这个例子中存储过程proc2接受一个输入参数pro计算var pro * 2然后根据var的值更新表t中的id字段。如果var大于或等于10id字段增加1否则id字段减少1。 循环语句while … end while 循环语句允许重复执行一段SQL代码直到满足某个条件为止。以下是一个使用while循环的存储过程示例
DELIMITER $$
CREATE PROCEDURE proc3()
BEGINDECLARE var INT DEFAULT 0;WHILE var 6 DOINSERT INTO t VALUES(var);SET var var 1;END WHILE;
END $$
DELIMITER ;在这个例子中存储过程proc3没有输入参数。它声明了一个变量var并初始化为0然后使用while循环将var的值插入到表t中直到var的值达到6为止。每次循环迭代后var的值都会增加1。
调用存储过程
要执行上述存储过程可以使用CALL语句
CALL proc2(6); -- 调用存储过程proc2传入参数6
CALL proc3(); -- 调用存储过程proc3不需要传入参数注意
在创建存储过程之前确保所使用的数据库是当前数据库。存储过程中的SQL语句应该遵循数据库的语法规则。如果存储过程中涉及到对表的更新或插入操作请确保具有相应的权限。在删除存储过程之前请确保没有其他应用程序或用户正在使用该存储过程以避免数据丢失或应用程序错误。
问答环节简要总结 MySQL查询数据的执行过程是什么 1客户端向 MySQL 服务器发送一条查询请求连接器负责处理连接并进行身份验证和权限控制。 2MySQL先检查查询缓存查询缓存在MySQL8.0中已被删除如果命中缓存则立刻返回存储在缓存中的结果否则使用查询解析器进行SQL语句解析、预处理再由优化器生成对应的执行计划。 3MySQL根据执行计划调用存储引擎来执行查询。 4将结果返回给客户端同时缓存查询结果。 SQL查询语句基本结构
SELECT 字段列表
FROM 表名
WHERE 字段 值 AND/OR 字段 值;SELECT 字段列表指定要查询的字段。FROM 表名指定要查询的表。WHERE 字段 值指定查询条件。
字段条件运算符 等于 () WHERE 字段 值不等于 (! 或 ) WHERE 字段 ! 值
WHERE 字段 值大于 () WHERE 字段 值大于等于 () WHERE 字段 值小于 () WHERE 字段 值小于等于 () WHERE 字段 值IN WHERE 字段 IN (值1, 值2, ...)BETWEEN WHERE 字段 BETWEEN 值1 AND 值2LIKE %任意长度的任意字符_一个任意字符 WHERE 字段 LIKE 通配符表达式示例 WHERE 字段 LIKE %abc% -- 包含 abc 的任意位置
WHERE 字段 LIKE _bc -- 第一个字符任意后面是 bcREGEXP正则表达式 WHERE 字段 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语句;删除视图DROP VIEW 视图表名;多表查询与分组
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 结果1WHEN 值2 THEN 结果2ELSE 默认结果 END AS case字段别名
FROM 表名;这种形式主要用于根据某个字段的具体值来决定新字段的值。 第二种形式基于条件的条件
SELECT CASE WHEN 字段值1 THEN 结果1WHEN 字段值2 THEN 结果2ELSE 默认结果 END AS case字段别名
FROM 表名;这种形式更灵活可以根据任何条件来决定新字段的值而不仅仅是基于某个字段的值。 无值’’ 和 空值NULL 的区别 无值’’ 的长度为 0不占用空间可以通过 字段名 ‘’ 字段名 !或 ‘’ 来过滤字段的值是否为无值的行指定字段使用函数 count(字段) 不会忽略无值的行 空值NULL 的长度为 NULL占用空间可以通过 字段名 is null 字段名 is not null 来过滤字段的值是否为NULL的行指定字段使用函数 count(字段) 会忽略NULL的行 Mysql输入数据的方法 1insert into 2load date infile ‘csv文件’ 3使用第三方客户端工具比如 navicat Mysql导出导入CSV文件 1修改mysql配置文件在[mysqld]配置项下面添加 secure_file_priv“” 重启服务 2创建导出目录并授权权限 mkdir mysql-output; chown mysql:mysql mysql-output/ 3select * 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 视图表名);
