MySQL必知必会13-20章笔记

最新推荐文章于 2024-09-20 21:26:51 发布

ZW钟文

最新推荐文章于 2024-09-20 21:26:51 发布

阅读量262

点赞数 2

分类专栏：关系型数据库文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_51964209/article/details/125895773

版权

关系型数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

本文详细介绍了SQL查询中的子查询使用，包括嵌套子查询和标量子查询，强调了子查询在满足特定条件时的重要性。同时，讨论了表连接的概念，解释了为何需要表连接以及不同类型的连接（如内连接、外连接）的适用场景。此外，还涵盖了数据的插入、更新和删除操作，提醒读者在使用这些操作时需要注意数据安全。

摘要由CSDN通过智能技术生成

写好SQL的前提条件是自己对于数据库中表所存储的信息和表的结构以及表之间的关联信息足够了解，同时自己对于如何将所做的需求转化到数据库中具体的表中有所理解。

子查询

平常我们写的SQL语句只包含一个select子句，这是最普通的查询。但实际上在任何需要表出现的地方都可以使用select子句代替，如from子句后面需要接表名，我们可以将表名替换为需要的select子句，同时in关键字后面所需要的集合也可以用select子句代替。还有就是有的需要单个值的地方也可以用只能查出单个值的标量子查询。

子查询通常是由于一张表不足以查询出结果，需要子查询的结果作为额外条件
子查询通常是由内向外执行的（就是主观意识上的内和外）
对于嵌套的子查询通常没有数量限制，但是过多的子查询会影响性能，故不建议
子查询与它所在位置原本应该出现的列应该相匹配

技巧：逐渐增加子查询来建立查询，用子查询测试和调试查询很有技巧性，特别是在这些语句的复杂性不断增加的情况下更是如此。用子查询建立（和测试）查询的最可靠的方法是逐渐进行，这与MySQL处理它们的方法非常相同。首先，建立和测试最内层的查询。然后，用硬编码数据建立和测试外层查询，并且仅在确认它正常后才嵌入子查询。这时，再次测试它。对于要增加的每个查询，重复这些步骤。这样做仅给构造查询增加了一点点时间，但节省了以后（找出查询为什么不正常）的大量时间，并且极大地提高了查询一开始就正常工作的可能性

针对子查询两种情况的例题：

嵌套子查询：

月均完成试卷数不小于3的用户爱作答的类别（较难）

明确题目意思：某一个用户只要他在某一月完成的试卷数大于等于3，就将他在这个月做的所有试卷记录答案（包括未提交的），最后答案按标签次数降序。
先将没有提交的试卷去除，然后根据uid与月份进行分组，选出试卷数大于等于3的分组的uid，记录结果，这次查询也就是子查询
将试卷记录表和试卷详细信息表联结，保留uid在子查询结果中的元组，然后根据tag分类，最后排序得出结果

# in作用在子查询中时，甚至只需要数据类型匹配即可，这里即是e1.uid和uid同为整型就行
# 但是为了让我们自己更好的理解子查询还是取相同的名字较好
# 虽然下面的代码AC了，但我觉得题目意思有歧义，它是只有用户有一个月完成三张以上试卷
# 就把它所有次的试卷都算到答案中去还是只算这个月的呢。下面的代码是将所有次的都算进去，但我觉得
# 还是只能算那个月的，然后我又写了下面一个只算当月的SQL，但是怎么都是语法有问题，找不到啊找不到啊
select tag, count(*) as tag_cnt
from exam_record as e1 join examination_info on e1.exam_id = examination_info.exam_id
where e1.uid in (
select distinct uid
from exam_record
where submit_time is not null
group by uid, DATE_FORMAT(submit_time, '%m')
having count(*) >= 3)
group by tag
order by tag_cnt desc;

# 运行不了的SQL
select tag, count(*) as tag_cnt
from exam_record as e1 join examination_info on e1.exam_id = examination_info.exam_id
where (e1.uid, DATE_FORMAT(e1.submit_time, '%m')) in 
(
select uid, DATE_FORMAT(submit_time, '%m')
from exam_record
where submit_time is not null
group by uid, DATE_FORMAT(submit_time, '%m')
having count(*) >= 3
)
group by tag
order by tag_cnt desc;

标量子查询：

标量子查询一般是用在利用聚合函数查出单个值的地方，一般也是两张及以上表的时候，一些表提供信息，某张表根据提供信息进行标量子查询。暂时没有找到标量子查询的题目，所以直接用书上的示例代码为例吧，还是很好理解的。

SELECT cust_name, cust_state, (SELECT count(*) from oreders where orders.cust_id = customers.cust_id) as orders
from customers
order by cust_name;

上述的标量子查询在从customers中检索出一条数据的时候就会执行一遍。
实际上，很多用子查询能解决的题目用表连接也能解决，并且解决一个问题的子查询写法或者是表连接写法可能也不止一种。这方面还是得多练习，向我们学生没有实际场景应用的话就和做算法题一样做各大平台的SQL题进行练习。

表联结（连接）

为什么要用表连接？

关系数据库中关系（表）的设计决定的，通常一个需求所涉及到的数据并不只是存储在一张表中，这个时候我们就需要想办法将所需数据牵扯到的表连接起来。可能你会问，那为什么不讲需求所涉及到的数据存储在一张表中，这里主要是由于存储在一张表中可能会导致数据的冗余以及数据一致性的难以保证。当然连表过程中也会有多余时间的消耗，具体场景具体问题具体分析吧。

连接表不是物理实体，也就是连接所生成的表并不真实存在，而是只存在于查询的执行过程中。

笛卡尔积

连表形式：from t_A, t_B，表名之间以,隔开。这个t_A或者t_B它也不一定是数据库中存在的表，可能是某个连表过程产生的中间表。当然一般这样递归的次数不会多，因为连表过多的话会严重影响查询效率。
假设t-A有2列，分别是C和D，t_B也有两列，分别是E和F，两张表执行笛卡尔积的是t_A中每个元组与t_B中每个元组进行组合，得到最后的中间表，包括四个属性（C，D，E，F）即使中间有属性存在外键关系也还是会重复出现在中间表中。
最后笛卡尔积得到的结果元组是非常大的，因此我们需要用where子句对于数据进行过滤，而实际上DBMS也不会完全得到笛卡尔积的结果，而是得到尽可能满足where子句条件的结果元组。

自连接

表别名的使用：

表别名只在查询过程中使用，与列别名不一样，它不会返回给客户端
表别名可以缩短SQL语句，同时它可以允许在一条SELECT语句中多次使用同一张表

select p1.prod_id, p1.prod_name
from products as p1, products as p2
where p1.vend_id = p2.vend_id and p2.prod_id ='DTNTR'

# 与上面自连接等价的子查询SQL
select prod_id, prod_name
from products
where vend_id in (
  select vend_id
  from products
  where prod_id = 'DINTR'
 );

虽然上面自连接和子查询最终得到的结果是一致的，但是有时候表连接的速度要比子查询快很多，因此要综合考虑使用哪种方式。

外连接

默认的连表方式是内连接，即a join b on xxxx等同于a inner join b on xxx，内连接只会保留满足连接条件的元组，但是有时候的需求是左边或右边的表的某一行元组不满足连接条件，我们也要将它保留下来。这个时候就需要使用到外连接了。
外连接典型场景有：

统计客户的订单总数量，没有下单的客户也要统计（客户表，订单表）
列出所有产品信息以及其订购数量（产品表，订单表）

select customers.cust_id, count(orders.order_num)
from customers left outer join orders on customers.cust_id = orders.cust_id
group by customers.cust_id;
# 实际上left outer join 用 left join代替即可

这样最终连表形式的中间表就会包含左边表中不能匹配连接条件的行，同时该行中属于右边表的属性的值置位NULL。
右外连接与左外连接的写法相似，改为right join即可，此时右边表中不能匹配连接条件的行会出现在连接产生的中间表中。

组合查询（并集）

UNION操作符的使用，将多个SELECT语句的查询结果组合到一起。
使用组合查询的两种基本情况：

在单个查询中从不同的表返回结构相似的数据
对单个表进行多次查询，但单次返回数据

组合单个表的多次查询，实际上是对于单个表的多次查询的过滤条件不同。这时，在where字句中使用多个条件判断语句（||）也能起到UNION操作符的作用。

使用UNION操作符的一些注意事项：

n个SELECT语句的查询结果可以有n - 1个UNION操作符进行连接
UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）
UNION中每个查询之间相匹配的列的数据类型需要是可兼容的
UNION默认会去除并集中的重复元组，若想保留重复元组，需使用UNION ALL
若想对UNION操作符产生的结果进行排序，order by子句只能出现在最后一条SELECT语句中

数据插入

insert语句用来向表中插入行数据，具体有

插入完整的行
插入行的一部分
插入多行
插入某个select语句的结果集

可以针对每个表或每个用户，利用MySQL的权限机制，禁止某个用户对于某张表的insert权限

插入完整的一行

insert into customers values(NULL, '123', 456, 123, NULL)

这个时候，values后面的值元组的列类型需要跟customers表中的列类型从左到右一一匹配，values中的元素不能多一个也不能少一个，并且元素类型也需要和customers表中的元素类型一一推断。若对于customers表中某个属性，我们不想给出值，就在values元组中给出NULL。

insert into customers(column_1, column_2, column_3, column_4) 
values(NULL, '123', 456, 123, NULL)

此时，我们不需要记住customers表中列的次序，只需要表名后面的属性列表可以与values后面的值列表一一匹配即可。

插入行的一部分

插入行的一部分的前提条件是该数据库表允许其中某些列的值为NULL，或者会给出默认值或自动的值。

insert into customers(column_1, column_3) values(NULL, 456);

插入格式基本与给出表属性列表后插入完整一行一致，只不过它是取了完整属性列表的一个子集。

数据库经常被多个客户访问，对处理什么请求以及用什么次序处理进行管理是MySQL的任务。INSERT操作可能很耗时（特别是有很多索引需要更新时），而且它可能降低等待处理的SELECT语句的性能。
可以选择使用insert lower_priority into来降低insert语句的优先级

插入多行

insert into customers(column_1, column_2, column_3, column_4) 
values(NULL, '123', 456, 123, NULL);

insert into customers(column_1, column_2, column_3, column_4) 
values(NULL, '123', 456, 123, NULL);

insert into customers(column_1, column_2, column_3, column_4) 
values(NULL, '123', 456, 123, NULL)

# 如果多条insert语句的待插入列名和次序相同，则可以使用单条insert语句，并且一般情况下，
# 单条insert语句的效率比多条高
insert into customers(column_1, column_2, column_3, column_4) 
values(NULL, '123', 456, 123, NULL)，
values(NULL, '123', 456, 123, NULL)，
values(NULL, '123', 456, 123, NULL);

插入SELECT语句的结果集：
insert into customers(c_1, c_2, c_3) select c_1, c_2, c_3 from xxxx
将一张表中的数据导入另一张结构相似的表。

更新和删除数据

更新数据

更新整张表
更新某一行

update table_name set column_name1 = value1 and column_2 = value2 where condition，只更新满足条件的行。
在大部分情况下都是需要指定where子句的，因为很少会去更新整张表。同时，value1和value2处可以使用子查询，即用SELECT语句检索出来的内容更新数据。