1、SQL 的执行顺序
FROM - ON - JOIN - WHERE - GROUP BY - WITH - HAVING - SELECT - DISTINCT - ORDER BY - LIMIT
在 SQL 中,查询语句的执行顺序通常是按照以下步骤进行的:
- FROM:首先,从指定的表(或视图)中获取数据,这是查询的基本数据源。
- WHERE:然后,根据 WHERE 子句中的条件筛选出满足条件的行。
- GROUP BY:如果查询包含 GROUP BY 子句,则将结果按照指定的列进行分组。
- HAVING:在分组后,根据 HAVING 子句中的条件筛选出满足条件的分组。HAVING 子句可以被认为是 WHERE 子句针对分组后的结果的过滤器。
- SELECT:选择要检索的列,并且对数据进行计算、转换等操作。
- DISTINCT:如果查询包含 DISTINCT 关键字,则去除结果集中重复的行。
- ORDER BY:按照指定的列对结果集进行排序。
- LIMIT / OFFSET:如果使用了 LIMIT 或 OFFSET 子句,最后根据这些子句来限制结果集的大小或移动结果集的起始位置。
需要注意的是,虽然这是一般情况下的执行顺序,但在实际执行过程中,数据库系统可能会对查询进行优化和重排,以提高性能。
2、表连接方式有哪些及其区别?
(1)一般情况下
在 SQL 中,连接是用于联合两个或多个表的操作,以便获取相关的数据。
有几种连接方式,主要包括 INNER JOIN、LEFT JOIN(或 LEFT OUTER JOIN)、RIGHT JOIN(或 RIGHT OUTER JOIN)和 FULL JOIN(或 FULL OUTER JOIN)。以下是它们的主要区别:
- INNER JOIN:INNER JOIN 返回两个表中符合连接条件的行。如果某一行在其中一个表中没有匹配行,则该行不会包含在结果中。
- LEFT JOIN (或 LEFT OUTER JOIN):LEFT JOIN 返回左表中的所有行,以及右表中符合连接条件的行。如果在右表中没有匹配的行,则结果集中右侧的列将包含 NULL 值。
- RIGHT JOIN (或 RIGHT OUTER JOIN):RIGHT JOIN 返回右表中的所有行,以及左表中符合连接条件的行。如果在左表中没有匹配的行,则结果集中左侧的列将包含 NULL 值。
- FULL JOIN (或 FULL OUTER JOIN):FULL JOIN 返回左右两个表中的所有行,如果在其中一个表中没有匹配的行,则另一个表中对应的列将包含 NULL 值。
在实际应用中,选择连接方式取决于你需要的结果。如果你只想获取两个表中匹配的行,可以使用 INNER JOIN。如果你想保留左表中的所有行,并且将右表中匹配的行加入,可以使用 LEFT JOIN。同样,RIGHT JOIN 是保留右表中的所有行,而 FULL JOIN 保留两个表中的所有行。
连接操作可以根据数据之间的关系和业务需求来选择,因此了解不同连接方式的特点对于写出准确的 SQL 查询语句是很重要的。
(2)若没有共同的字段,用什么连接?union 和 union all的区别?
如果没有共同的字段,你可以使用CROSS JOIN连接方式。CROSS JOIN会返回两个表的笛卡尔积,即两个表的所有可能组合。
union与union all的区别在于,union 进行去重,而union all不去重
3、窗口函数有哪些
窗口函数(Window Functions)是一种SQL中强大的工具,用于在查询结果集中执行聚合、分析和计算操作。下面是一些常见的窗口函数:
- ROW_NUMBER():为结果集中的每一行分配一个唯一的数字。
- RANK():为结果集中的每一行分配一个排名,相同的值将获得相同的排名,但是会跳过相同排名数量。
- DENSE_RANK():与RANK()类似,但是不会跳过相同排名数量,排名是连续的。
- NTILE(n):将结果集划分为n个相等大小的桶,并为每个桶中的行分配一个桶号。
- LEAD(column, offset, default):获取当前行之后第offset个行的值。
- LAG(column, offset, default):获取当前行之前第offset个行的值。
- FIRST_VALUE(column):获取分组中第一行的指定列的值。
- LAST_VALUE(column):获取分组中最后一行的指定列的值。
- SUM(), AVG(), COUNT(), MIN(), MAX():这些聚合函数也可以用作窗口函数,用于在窗口内执行聚合操作而不是整个结果集。
- PERCENT_RANK():为结果集中的每一行计算百分比排名。
- CUME_DIST():计算当前行在整个分组中的累积分布百分比。
- PERCENTILE_CONT():计算分组中指定百分位数的近似值。
- LAG() OVER (PARTITION BY ... ORDER BY ...):在指定分区内根据指定顺序获取前一行的值。
- LEAD() OVER (PARTITION BY ... ORDER BY ...):在指定分区内根据指定顺序获取后一行的值。
这些窗口函数可以在SELECT语句的SELECT列表、ORDER BY子句和GROUP BY子句中使用,并且通常与OVER子句一起使用,以定义窗口的边界和排序规则。窗口函数提供了强大的功能,可以在查询结果中进行复杂的分析和处理。
4.索引的作用?
索引:通过创建索引,可以保证表中数据的唯一性)。
索引主要建立在①经常搜索的列;②主键所在列;③外键所在列索引包括聚集索引与非聚集索引,它们的区别在于索引记录的顺序与表记录的顺序是否一致。
5.排名函数与排序函数?
排序函数: order by (默认asc升序,指定desc降序),例如将表格数据按照考试成绩从低到高排序。
排名函数: rank, dense rank, row number ,得到的成绩的排序后,根据成绩的高低对学生排名,100分对应第一名,99分第二名。
它们的区别在于:row number: 根据成绩排序生成连续的序列号,1,2,3,4,5……;
rank: 和row number 不同,rank 考虑了相同分数学生的排名问题。如第一名100分,第2名两个人并列99分,那么98分的同学排名第四。
dense rank: 和rank的区别在于,同样考虑了分区内的排名,但dense rank的输出结果是连续的。如第一名100分,第2名两个人并列99分,那么98分的同学排名第三。
6.on 和 where 的区别?
数据库在连接多张表返回记录时,都会生成一个中间临时表。在内连接中,使用on或者where没有区别。在外连接里,例如使用left join时:on是在生成临时表时使用的条件,不管on的条件是否为真,都会返回左边表中的全部记录。where条件是在临时表生成好后,再对临时表进行过滤的条件。
7.主键和外键?
主键是一张表中能够确定一条记录的唯一标志。
外键用于和另一张表进行关联。
8.向表中插入数据?
insert into table name values: 普通插入数据模式insert or ignore into: 如果没有则插入数据,如果有则忽略insert or replace into: 如果不存在就插入,存在就更新
9.删除表中数据?
delete : 删除表中数据,可以指定具体数据(where)
drop column/ drop table : 删除列数据,与delete 不同,drop函数会将数据以及表的结构全部删除。
truncate: 仅删除数据,且默认删除所有数据。和delete不同,truncate不能用where进行筛选,但删除速度比delete快
10.字符串常见操作函数?
concat(): 将多个字符串连接成一个字符串,连接符用“”包起来
concat_ws(); 将多个字符串连接成一个字符串,在最开始的位置指定连接符(指定一次即可)
group concat(): 将group by产生的同一个分组中的值连接起来,返回一个字符串。
like(): 需要与通配符一起使用('%'代表任意字符出现任意次数;'_'仅能匹配单个字符)
substr(): 用于从字段中提取相应位置的字符。
regexp() : 正则表达式匹配函数
11. In/exist的联系与区别
子查询过程中,In和exist函数效率比较:当进行连接的两个表大小相似,效率差不多;如果子查询的内表更大,则exist的效率更高;如果子查询的内表小,则in的效率高。
Exist的原理: 使用exist时,若子查询能够找到匹配的记录,则返回true,外表能够提取查询数据;使用 not exist 时,若子查询找不到匹配记录,则返回true,外表能够提取查询数据。