SQL语句细节 - Githubissues

1.SQL语句主要分为哪几类

数据定义语言DDL（Data Ddefinition Language）CREATE，DROP，ALTER 主要为以上操作即对逻辑结构等有操作的，其中包括表结构，视图和索引。

数据查询语言DQL（Data Query Language) SELECT 这个较为好理解即查询操作，以select关键字。各种简单查询，连接查询等都属于DQL。

数据操纵语言DML（Data Manipulation Language） INSERT，UPDATE，DELETE 主要为以上操作即对数据进行操作的，对应上面所说的查询操作 DQL与DML共同构建了多数初级程序员常用的增删改查操作。而查询是较为特殊的一种被划分到DQL中。

数据控制功能DCL（Data Control Language） GRANT，REVOKE，COMMIT，ROLLBACK 主要为以上操作即对数据库安全性完整性等有操作的，可以简单的理解为权限控制等。

2.SQL语句的语法执行顺序和为什么where不能操作聚合函数

1.FROM：确定要查询的表； 2.WHERE：对表中的行进行筛选； 3.GROUP BY：对筛选后的结果进行分组； 4.HAVING：对分组后的结果进行筛选； 5.SELECT：选择要显示的列； 6.ORDER BY：对最终结果进行排序

2.1.执行顺序

在数据库执行SQL查询时，并不会按照我们编写的顺序去执行。在查询执行时，首先需要知道是从哪个表（FROM）获取数据，然后对表的行进行筛选（WHERE），接着对筛选后的数据进行分组（GROUP BY）和进一步筛选（HAVING），最后再选择要返回哪些列（SELECT）

2.2.为什么where不能操作聚合函数

从这个执行顺序可以看出，WHERE子句是在GROUP BY子句之前执行的。在WHERE子句执行时，还没有进行分组，也就没有聚合操作，因此不能引用聚合函数。

而HAVING子句则是在GROUP BY子句之后执行的。在HAVING子句执行时，已经进行过分组和聚合操作了，因此可以引用聚合函数。

所以，简单来说，WHERE不能筛选聚合函数的原因，是因为在WHERE子句执行时，还没有进行聚合操作。而HAVING能筛选聚合函数的原因，是因为在HAVING子句执行时，已经进行过聚合操作了

3.超键、候选键、主键、外键分别是什么

超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。

候选键：是最小超键，即没有冗余元素的超键。

主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。

外键：在一个表中存在的另一个表的主键称此表的外键。

4.SQL 约束有哪几种

1.NOT NULL: 用于控制字段的内容一定不能为空（NULL）。 2.UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。 3.PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。 4.FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。 5.CHECK: 用于控制字段的值范围。 6.AUTO_INCREMENT:来实现主键自增长

5.六种关联查询

交叉连接（CROSS JOIN）

内连接（INNER JOIN）

外连接（LEFT JOIN/RIGHT JOIN）

联合查询（UNION与UNION ALL）

全连接（FULL JOIN）

6. mysql中 in 和 exists 区别

mysql中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。 1.如果查询的两个表大小相当，那么用in和exists差别不大。 2.如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in 1650299074(1) 1650299087(1)

补充：Join和in的区别

JOIN和IN都是SQL中用于关联查询的技术，但它们之间存在一些关键区别。

JOIN

JOIN用于将两个或多个表基于某种相关性合并在一起。JOIN的主要类型包括INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL JOIN。

INNER JOIN: 返回两个表中存在匹配的行。 LEFT JOIN: 返回左表的所有行，以及与之匹配的右表的行。如果没有匹配的行，则右表的部分为NULL。 RIGHT JOIN: 返回右表的所有行，以及与之匹配的左表的行。如果没有匹配的行，则左表的部分为NULL。 FULL JOIN: 返回左表和右表的所有行。如果没有匹配的行，则另一表的部分为NULL。 IN

IN是一种条件操作符，用于检查某个值是否在一组值中。通常，IN用于WHERE子句中。例如，以下查询将返回ID为1、2或3的员工的姓名：

区别

1.JOIN用于将两个或多个表的行结合在一起，基于它们之间的某种关联。而IN用于检查某个值是否存在于一组值中。 2.JOIN操作通常比IN操作更复杂，因为它涉及到多个表，而IN操作只涉及到一个表。 3.对于大数据集，JOIN操作通常比IN操作更有效率。这是因为JOIN操作在处理大数据集时可以使用更优化的算法。 4.IN操作比较简单易懂，特别是对于初学者。而JOIN操作需要对表的结构和关联关系有深入理解。 5.使用JOIN时，可以通过调整JOIN类型（例如INNER JOIN、LEFT JOIN等）来调整查询结果。而使用IN时，只能获取满足某个特定条件的行。

7.varchar与char的区别

char的特点

char表示定长字符串，长度是固定的；

如果插入数据的长度小于char的固定长度时，则用空格填充；

因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；

对于char来说，最多能存放的字符个数为255，和编码无关

varchar的特点 varchar表示可变长字符串，长度是可变的；

插入的数据是多长，就按照多长来存储；

varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；

对于varchar来说，最多能存放的字符个数为65532

总之，结合性能角度（char更快）和节省磁盘空间角度（varchar更小），具体情况还需具体来设计数据库才是妥当的做法。

8.mysql中int(10)和char(10)以及varchar(10)的区别

1.int(10)的10表示显示的数据的长度，不是存储数据的大小；chart(10)和varchar(10)的10表示存储数据的大小，即表示存储多少个字符。 2.char(10)表示存储定长的10个字符，不足10个就用空格补齐，占用更多的存储空间 3.varchar(10)表示存储10个变长的字符，存储多少个就是多少个，空格也按一个字符存储，这一点是和char(10)的空格不同的，char(10)的空格表示占位不算一个字符

9.drop、delete与truncate的区别

1650299442(1) 因此，在不再需要一张表的时候，用drop；在想删除部分数据行时候，用delete；在保留表而删除所有数据的时候用truncate。

10.模糊查询（%abc 和 abc%）是否用到索引，索引失效的场合

在MySQL中，我们常常使用LIKE关键字来进行模糊查询。LIKE关键字后面可以跟一个包含通配符（%或_）的字符串。%表示任意长度的字符，包括0个字符；_表示一个字符。

abc%：这个查询是可以使用索引的。因为它表示所有以"abc"开头的字符串，所以可以在索引树上按顺序查找满足条件的记录，这种方式被称为"范围查询"。

%abc：这个查询则通常不能使用索引。因为它表示所有以"abc"结尾的字符串，MySQL必须查找表中的每一行来检查哪些行满足这个条件，这种方式被称为"全表扫描"。

%abc%：这种情况也是不能使用索引的，因为它表示所有包含"abc"的字符串，MySQL同样需要进行全表扫描。 https://github.com/lukaliou123/lukaliou123.github.io/issues/22#issuecomment-1110850169

11.最左匹配原则

在MySQL中，最左前缀匹配原则是指在处理复合索引（包含多列的索引）时，会按照索引中的字段顺序从左到右进行匹配。

假设我们有一个复合索引 (A, B, C)，那么最左前缀匹配原则可以满足下列查询条件：

WHERE A=xx WHERE A=xx AND B=yy WHERE A=xx AND B=yy AND C=zz 但是，如果你的查询条件中，从左到右的某个字段没有在WHERE条件中出现，那么这个字段右边所有的字段都无法利用到索引。比如：

WHERE B=yy AND C=zz：在这个查询中，A字段并没有出现在查询条件中，所以B和C字段无法使用索引。 WHERE A=xx AND C=zz：在这个查询中，B字段并没有出现在查询条件中，所以C字段无法使用索引。这就是所谓的最左前缀匹配原则，这个原则对于复合索引的查询优化非常重要，需要我们在设计数据库和编写查询语句时予以充分考虑。

12.通过什么命令查看SQL语句性能

性能从最优到最差的排序：system > const > eq_ref > ref > range > index > all

对java程序员来说，若保证查询至少达到range级别或者最好能达到ref则算是一个优秀而又负责的程序员。 all：（full table scan）全表扫描无疑是最差，若是百万千万级数据量，全表扫描会非常慢。 index：（full index scan）全索引文件扫描比all好很多，毕竟从索引树中找数据，比从全表中找数据要快。 range：只检索给定范围的行，使用索引来匹配行。范围缩小了，当然比全表扫描和全索引文件扫描要快。sql语句中一般会有between，in，>，< 等查询。 ref：非唯一性索引扫描，本质上也是一种索引访问，返回所有匹配某个单独值的行。比如查询公司所有属于研发团队的同事，匹配的结果是多个并非唯一值。 eq_ref：唯一性索引扫描，对于每个索引键，表中有一条记录与之匹配。比如查询公司的CEO，匹配的结果只可能是一条记录， const：表示通过索引一次就可以找到，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快，若将主键至于where列表中，MySQL就能将该查询转换为一个常量。

system：表只有一条记录（等于系统表），这是const类型的特列，平时不会出现，了解即可 possible_keys：表示可能应用在这张表中的索引。如果为空，没有可能的索引。 key：实际使用的索引。如果为NULL，则没有使用索引。 rows：MySQL认为必须检查的用来返回请求数据的行数。

13.怎么看QPS？

对于MySQL，我们可以通过查看状态变量来获取QPS值，具体方法如下 ST5M4EB6UWNFBS@QJ_~N~N4

假设现在的结果是1000。然后，你等待一段时间，比如1分钟，再次执行同样的命令，这次的结果假设是2000。

那么，在这一分钟内，Queries的增长值为1000，所以QPS = 1000 Queries / 60秒 = 16.67。

另外，show status like 'Com_select'也可以获取类似的结果，这个命令返回的是从服务器启动到现在所有执行的SELECT命令的数量。

14.SHOW PROFILE

首先，需要设置 profiling 为 1 来开启性能分析功能： )FEHI0WA_KO37NG7GWI6ZN7 SHOW PROFILES 命令会返回一个表，列出你从开启 profiling 以来所执行的每一个查询的详细信息，包括查询编号，查询执行的时间以及查询的SQL语句等。

而如果你想要查看某一个具体查询的更详细的信息，可以使用 SHOW PROFILE FOR QUERY query_number;，其中 query_number 是你想要查看的查询的编号。

这种方法可以帮助你了解查询中哪部分花费的时间最长，以此来进行优化。记住，使用完之后记得关闭 profiling 功能，因为这个功能会额外占用一些服务器资源，关闭的命令是 SET profiling = 0;。

当你执行 SHOW PROFILES;，你可能会看到如下的输出 YGS$T9Y7A S17ZRM_$1CG_V 执行 SHOW PROFILE FOR QUERY query_number; 后，MySQL将会返回一张表，这张表详细描述了这条查询中的各个步骤的执行时间。 P1TKL8YYPTMEE0WFCU8U6_Y

15.唯一索引可以存在多少个

在MySQL中，一张表可以有多个唯一索引。虽然理论上，一张表可以有任意多个唯一索引，但是实际上由于唯一索引需要消耗存储空间和计算资源，在保证数据一致性和查询效率的同时，过多的唯一索引可能会影响写入性能，因此需要在实际应用中做好权衡。如果能确定某个数据列只包含彼此各不相同的值，那么在为这个数据列创建索引时，就应该使用关键字UNIQUE把它定义为一个唯一索引。

16.InnoDB的锁机制

nnoDB支持多种类型的锁，主要有以下几种：

共享锁（S Locks）：允许一个事务去读一行，阻止其他事务对同一行进行写操作。 排他锁（X Locks）：允许获取锁的事务更新数据，阻止其他事务取得同一行的读锁和写锁。 意向锁（Intention Locks）：是一种表级锁，表明一个事务将要对表中的行进行读取或者更新操作。同时，InnoDB实现了一种叫做“多版本并发控制（MVCC）”的机制，它可以使得多个用户在同一时间访问同一行数据而不会互相干扰，大大提高了并发性能。

17.InnoDB修改某行数据怎么加锁

当InnoDB需要修改某行数据时，它会尝试获取对应行的排他锁（X Lock）。这种锁可以使得获取锁的事务能够修改数据，并阻止其他事务对同一行数据进行读取或者修改。如果当前行已经被其他事务获取了共享锁（S Lock），则获取排他锁的事务需要等待，直到共享锁被释放。获取到排他锁的事务在修改了数据之后，在事务提交或回滚时，该锁会被自动释放。

18.SQL查询慢怎么解决优化和调优

1.索引优化：适当添加或调整索引是优化SQL查询的一个重要手段。在适当的列上添加索引，可以极大地提高查询速度。但是也要注意，索引并不是越多越好，索引的添加和删除都需要谨慎。

2.查询语句优化：避免全表扫描，尽可能让搜索引擎使用索引，避免在列上进行运算，避免使用不等于或<>操作符，优化WHERE子句中的内容等。

3.使用Explain分析查询：Explain可以帮助我们理解MySQL如何执行查询。我们需要关注的一些关键字段包括：type（表示MySQL在表中找到所需行的方式，或者访问类型）, key（MySQL能够使用哪个索引，在没有索引的情况下，显示为NULL）, rows（MySQL认为必须检查的用来返回请求数据的行数）等。我们可以通过分析Explain的输出，来对查询进行进一步的优化。

4.数据库结构优化：合理的数据库设计也是保证查询效率的重要因素。比如说，我们可以进行数据库的正规化设计来消除数据的冗余，也可以进行反正规化来提高数据的读取效率。

5.使用更高级的技术：如分表分库、读写分离、缓存技术等。

19.一些会导致全局搜索的例子

1.对列进行函数操作：比如 SELECT * FROM users WHERE YEAR(birth_date) = 1992;，这个查询中，在birth_date列上使用了YEAR函数，这就会导致MySQL无法使用索引，从而进行全表扫描。

2.使用了!=或<>操作符：比如 SELECT * FROM users WHERE age != 25;，这个查询将会对age列进行全表扫描，因为MySQL无法确定非25岁的用户在哪里。

3.LIKE语句以通配符开头：比如 SELECT * FROM users WHERE name LIKE '%Smith';，在这个查询中，MySQL将会对name列进行全表扫描，因为MySQL无法确定以'Smith'结尾的用户在哪里。

4.IS NULL或IS NOT NULL操作：比如 SELECT * FROM users WHERE address IS NULL;，如果address列上没有索引，MySQL将会进行全表扫描。

20.数据表数据量很大考虑用什么排序算法，稳定性问题？

1.归并排序：这是一种稳定的排序算法，它以O(n log n)的时间复杂度进行排序，而且可以处理大规模数据。它的主要缺点是需要与输入数据等大的额外空间。

2.快速排序：这是一种在平均情况下具有O(n log n)时间复杂度的排序算法，常常被认为是实际应用中最快的排序算法。然而，它的最坏情况时间复杂度是O(n²)，并且它不是稳定的。

3.堆排序：这也是一种O(n log n)的排序算法。与快速排序不同，堆排序的最坏情况时间复杂度也是O(n log n)。然而，它并不稳定。

4.对于大规模数据的排序，数据库经常使用一种称为外部排序的技术。其中最常见的算法就是多路归并排序，它是归并排序的一个变种，可以处理存储在外部存储器（如磁盘）中的大规模数据。

具体使用哪种排序算法，取决于具体的情况和需求。例如，如果稳定性是一个重要的考虑因素，那么归并排序可能是一个好的选择。如果内存空间有限，可能需要使用像堆排序这样的原地排序算法。如果数据已经存储在外部存储器中，可能需要使用像多路归并排序这样的外部排序算法。

多路排序 多路归并排序是归并排序的一种扩展，适用于处理存储在外部存储器（例如硬盘）上的大规模数据集。与常规的归并排序一样，多路归并排序也是一种分而治之的算法，但在归并阶段，它一次可以归并多个（超过两个）已排序的序列。多路归并排序算法通常与一个称为最小堆的数据结构一起使用，以实现高效的选择操作。

21.SQL建表时候的约束

SQL 约束（Constraints） SQL 约束用于规定表中的数据规则。

如果存在违反约束的数据行为，行为会被约束终止。

约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

在 SQL 中，我们有如下约束：

NOT NULL - 指示某列不能存储 NULL 值。 UNIQUE - 保证某列的每行必须有唯一的值 PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。 FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。 CHECK - 保证列中的值符合指定的条件。 DEFAULT - 规定没有给列赋值时的默认值。

1690784020827 1690784117364

lukaliou123 / lukaliou123.github.io

SQL语句细节 #19