文章目录1.SELECT 语句2.SELECT 子句3.FROM 子句4.WHERE 子句5.GROUP BY 子句6.HAVING 子句7.ORDER BY 子句8.LIMIT 子句9.DISTINCT 子句10.UNION 子句11.查看数据表记录数12.检查查询语句的执行效率13.查看 SQL 执行时的警告14.查看自增主键最大值参考文献1.SELECT 语句MySQL 的 SELECT 语句用于从数据库表中检索数据。功能强大语句结构复杂多样。不过基本的语句格式像下面这个样子。SELECT[列名称]FROM[表名称]WHERE[条件]一个完整的 SELECT 语句包含一些可选的子句。SELECT 语句定义如下SELECTclause[FROMclause][WHEREclause][GROUPBYclause][HAVINGclause][ORDERBYclause][LIMITclause]SELECT 子句是必选的其它子句是可选的。一个 SELECT 可以在不引用任何表的情况下进行计算也就是没有其他任何字句只有 SELECT 子句。SELECT11ASsum;-----|sum|-----|2|-----一个 SELECT 语句中子句的顺序是固定的。如 GROUP BY 子句不会位于 WHERE 子句前面。SELECT 语句不同子句的执行顺序开始FROM子句WHERE子句GROUPBY子句HAVING子句SELECT子句ORDERBY子句LIMIT子句最终结果每个子句执行后都会产生一个中间数据结果即所谓的临时视图供接下来的子句使用如果不存在某个子句则跳过。需要注意的是不同的数据库管理系统可能会有一些差异但一般情况下上述顺序适用于大多数SQL查询。MySQL 和标准 SQL 执行顺序基本是一样的。2.SELECT 子句SELECT 子句用于指定要选择的列或使用表达式生成新的值。对于所选数据还可以添加一些修饰比如使用 DISTINCT 关键字用于去重。一个完整的 SELECT 子句组成如下。SELECT[ALL|DISTINCT|DISTINCTROW][HIGH_PRIORITY][STRAIGHT_JOIN][SQL_SMALL_RESULT][SQL_BIG_RESULT][SQL_BUFFER_RESULT][SQL_NO_CACHE][SQL_CALC_FOUND_ROWS]select_expr[,select_expr]...[into_option]into_option: {INTOOUTFILEfile_name[CHARACTERSETcharset_name]export_options|INTODUMPFILEfile_name|INTOvar_name[,var_name]...}其中 select_expr 是必选的表示要查询的列、表达式或使用 * 表示所有列。SELECT*FROMt1INNERJOINt2...可以对列使用函数进行运算并使用 AS 关键字对结果列命名AS 是可选的可以省略。SELECTAVG(score)ASavg_score,t1.*FROMt1...# 或SELECTAVG(score)avg_score,t1.*FROMt1...3.FROM 子句FROM 子句指示要从中检索行的表。如果为多个表命名则执行连接。对于指定的每个表您可以选择指定一个别名。FROMtable_references[PARTITIONpartition_list]SELECT 支持显式分区选择使用 PARTITION 子句在 table_references 表的名称后面跟着一个分区或子分区列表(或两者都有)在这种情况下只从列出的分区中选择行而忽略表的任何其他分区。关于分区可参考 Chapter 24 Partitioning。4.WHERE 子句如果给定 WHERE 子句则指示行必须满足的一个或多个条件才能被选中。where_condition 是一个表达式对于要选择的每一行其计算结果为 true 才会被选择。如果没有 WHERE 子句将选择所有行。[WHERE condition]下面的运算符可在 WHERE 子句的条件表达式中使用。运算符描述等于! 或 不等于大于小于大于等于小于等于BETWEEN AND在某个范围内闭区间LIKE搜索某种模式AND多个条件与OR多个条件或1WHERE IN 的用法IN 在 WHERE 子句中的用法主要有两种IN 后面是子查询产生的记录集注意子查询结果数据列只能有一列且无需给子查询的结果集添加别名。SELECT*FROMtbl_name1WHEREcol_name1IN(SELECTcol_name2FROMtbl_name2);IN 后面是数据集合。SELECT * FROM tbl_name WHERE col_name IN (foo, bar, baz, qux);注意如果数据类型是字符串一定要将字符串用单引号引起来。5.GROUP BY 子句GROUP BY 子句是 SQL 中用于对数据进行分组聚合的核心功能。它将查询结果按照一个或多个列的值分成不同的组然后对每一组应用聚合函数如 SUM、AVG、COUNT、MAX、MIN 等最终每个组返回一行汇总结果。核心作用数据分组将具有相同值的记录归类到同一组中为聚合计算划定范围聚合函数会在每个分组内独立计算而不是针对整个表减少结果集行数原始表中的多行数据按分组条件压缩成一行汇总结果。基本语法SELECT分组列1,分组列2,聚合函数(列名)AS别名FROM表名WHERE条件GROUPBY分组列1,分组列2;执行顺序WHERE → GROUP BY → 聚合计算 → HAVING → SELECT → ORDER BY。示例-- 选择发起加好友请求次数超过10次的 QQ(uin)被加方to_uin只会显示第一个SELECTuin,to_uin,count(*)AScntfrominner_raw_add_friend_20170514GROUPBYuinHAVINGcnt10;如果指定多列使用逗号分隔。GROUPBYcol1,col2,...注意GROUP BY 子句中的数据列应该是 SELECT 指定的所有数据列除非这列用于聚合函数。但是如果 SELECT 指定的数据列没有用于聚合函数也不在 GROUP BY 子句中按理说会报错但是 MySQL 会选择第一条显示在结果集中。6.HAVING 子句HAVING 和 WHERE 子句一样用于指定选择条件。但 HAVING 和 WHERE 子句的用法上却有明显的区别。作用的对象不同。WHERE 作用于表和视图HAVING 作用于组。# 查询 QQ 3585076592 和 3585075773 在 20170514 当天加好友请求次数且请求次数10SELECTuin,count(*)AScntFROMinner_raw_add_friend_20170514WHEREuin3585076592ORuin3585075773GROUPBYuinHAVINGcnt10;作用的阶段不同。WHERE 在分组和聚集计算之前选取输入行因此它控制哪些行进入聚集计算而 HAVING 在分组和聚集之后选取分组。因此WHERE 子句不能包含聚集函数因为试图用聚集函数判断哪些行输入给聚集运算是没有意义的。 相反HAVING 子句一般包含聚集函数。当然也可以使用 HAVING 对结果集进行筛选但不建议这样做同样的条件可以更有效地用于 WHERE 阶段。# 查询指定 QQ 加好友请求信息where作用于输入阶段的数据集SELECT*FROMinner_raw_add_friend_20170514WHEREuin3585078528;# 作用等同于 WHERE 但 HAVING 作用于结果阶段的结果集SELECT*FROMinner_raw_add_friend_20170514HAVINGuin3585078528;7.ORDER BY 子句ORDER BY 子句用于根据指定的列对结果集进行排序。[ORDERBY{col_name|expr|position}[ASC|DESC],...[WITH ROLLUP]]ORDER BY 语句默认按照升序 ASCascend对记录进行排序。如果希望按照降序排序可以使用 DESCdescend关键字随机使用随机数函数RAND()。在指定待排序的列时不建议使用列位置从1开始因为该语法已从SQL标准中删除。比如以 QQ 号码降序排序。SELECT*FROMinner_raw_add_friend_20170514ORDERBYuinDESC;8.LIMIT 子句LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。[LIMIT{[offset,]row_count|row_countOFFSEToffset}]LIMIT 接受一个或两个数值参数。参数必须是一个整数常量。如果给定两个参数有两种用法。offset,row_count# 或row_countOFFSEToffsetoffset 为返回记录行的开始偏移量从 0 开始row_count 为返回记录行的最大数目。只给一个参数表示返回记录行的 Top 最大行数起始偏移量默认为 0。返回从起始偏移量开始返回剩余所有的记录可以使用一些值很大的第二个参数。如检索所有从第 96 行到最后一行。SELECT*FROMtblLIMIT95,18446744073709551615;注意MySQL目前不支持使用 -1 表示返回从偏移量开始剩余的所有记录即下面的写法是错误的SELECT*FROMtblLIMIT95,-19.DISTINCT 子句DISTINCT 关键字用于查询结果中去除重复的行只返回唯一的行。1利用 DISTINCT 结合 COUNT() 函数可以统计不重复记录的数量。# 选择每一个 QQ 发起加好友请求涉及到的不同的 QQ 数SELECTuin,count(distinctto_uin)cFROMadd_friendGROUPBYuin;2DISTINCT 用于选择不同的记录且只能放在所选列的开头作用于紧随其后的所有列。# 查询 uin 和 to_uin 不重复的加好友请求SELECTDISTINCTuin,to_uinFROMadd_friend;# 示例数据表uin to_uin10000123456100001212121000112121210001131313# 结果集uin to_uin10000123456100001212121000112121210001131313如果想使 DISTINCT 的功能作用于第二列的 to_uin使用 DISTINCT 是无望了因为 MySQL 语法尚不支持可以使用 GROUP BY 取而代之。SELECTuin,to_uinFROMadd_friendWHEREGROUPBYto_uin;# 结果集uin to_uin100001234561000012121210001131313该奇技淫巧只能用在 MySQL因为标准的 SQL 语法规定非聚合函数中的列一定要在 GROUP BY 子句中。MySQL 规定当非聚合函数中的列不存在于 GROUP BY 子句中则选择每个分组的第一行。3COUNT DISTINCT 统计符合条件的记录数量。如果像对符合条件的记录进行 COUNT DISTINCT那么如何添加条件呢参见 MySQL distinct count if conditions unique可以使用下面的方法。COUNT(DISTINCTCASEWHEN条件THEN字段END)参见 mysql count if distinct也可以使用下面这种方法。COUNT(DISTINCTcol_name1,IF(col_name21,true,null))10.UNION 子句UNION 的作用是将两次或多次查询结果纵向合并起来。query_expression_bodyUNION[ALL|DISTINCT]query_block[UNION[ALL|DISTINCT]query_expression_body][...]下面是一个示例。mysqlSELECT1,2;------|1|2|------|1|2|------mysqlSELECTa,b;------|a|b|------|a|b|------mysqlSELECT1,2UNIONSELECTa,b;------|1|2|------|1|2||a|b|------使用 UNION 需要注意以下几点。1UNION 的使用条件UNION 只能作用于结果集不能直接作用于原表。结果集的列数相同就可以即使字段类型不相同也可以使用。值得注意的是 UNION 后字段的名称以第一条 SQL 为准。2UNION 与 UNION ALL 的区别UNION 用于合并两个或多个 SELECT 语句的结果集并消去合并后的重复行。UNION ALL 则保留重复行。3关于 UNION 的排序有两张表内容如下# table1 uin nickname 10001 monkey 10002 monkey king # table2 uin nickname 20000 cat 20001 dog对两个结果集按照 uin 进行降序排序后再联合。(SELECT*FROMtable1ORDERBYuinDESC)UNION(SELECT*FROMtable2ORDERBYuinDESC);uin nickname10001monkey10002monkey king20000cat20001dog可以发现内层排序没有发生作用那现在试试在外层排序。SELECT*FROMtable1UNIONSELECT*FROMtable2ORDERBYuinDESC;uin nickname20001dog20000cat10002monkey king10001monkey可见外层排序发生了作用。那是不是内层排序就没有用了呢其实换个角度想想内层先排序如果外层又排序明显内层排序显得多余所以 MySQL 优化了 SQL 语句不让内层排序起作用。要想内层排序起作用必须要使内层排序的结果能影响最终的结果如加上 LIMIT。(SELECT*FROMtable1ORDERBYuinDESCLIMIT2)UNION(SELECT*FROMtable2ORDERBYuinDESCLIMIT2);uin nickname10002monkey king10001monkey20001dog20000cat此外UNION 与 JOIN 在使用时有一个本质区别我们必须知道。UNION 只能作用于 SELECT 结果集不能直接作用于数据表而 JOIN 则恰恰相反只作用于数据表不能直接作用于 SELECT 结果集可以将 SELECT 结果集指定别名作为派生表。11.查看数据表记录数查看数据表行数有多种方法。使用 COUNT(*)SELECTCOUNT(*)FROMtbl_name;对于 MyISAM 数据表很快建议使用因为 MyISAM 数据表事先将行数缓存起来可直接获取。InnoDB 数据表不建议使用当数据表行数过大时因需要扫描全表查询较慢。查看系统表 information_schema.TABLESSELECTtable_rowsFROMinformation_schema.TABLESWHERETABLE_SCHEMAdb_nameANDTABLE_NAMEtbl_name;information_schema 是 MySQL 中的一个系统数据库它包含了关于数据库、表、列等元数据信息。可以通过查询 information_schema.TABLES 表可以获取指定数据表的记录数。使用 SHOW TABLE STATUS 命令SHOWTABLESTATUSLIKEtbl_name;需要注意的是SHOW TABLE STATUS 命令返回的行数是一个近似值并不是实时的准确值。这是因为 MySQL 在某些情况下会对行数进行估算而不是实时计算。如果需要准确的行数建议使用 COUNT(*) 函数或查询 information_schema.TABLES 视图。12.检查查询语句的执行效率EXPLAIN 是一个用于查询优化的工具它可以提供有关 SELECT 查询的执行计划的详细信息。通过使用 EXPLAIN 命令可以了解 MySQL 是如何执行查询的包括使用的索引、连接类型、扫描的行数等。{EXPLAIN|DESCRIBE|DESC} select_statement;EXPLAIN 命令的输出结果包含以下列id查询的标识符用于标识查询中的每个步骤。 select_type查询的类型如 SIMPLE简单查询、PRIMARY主查询、SUBQUERY子查询等。 table查询涉及的表。 partitions查询涉及的分区。 type访问表的方式如 ALL全表扫描、INDEX使用索引扫描、RANGE范围扫描等。 possible_keys可能使用的索引。 key实际使用的索引。 key_len使用的索引的长度。 ref与索引比较的列或常量。 rows扫描的行数。 filtered过滤的行百分比。 Extra额外的信息如使用了临时表、使用了文件排序等。13.查看 SQL 执行时的警告SHOW WARNINGS 是一个用于查看最近一次执行的语句产生的警告信息的命令。在 MySQL 中警告Warning是一种表示潜在问题或异常情况的消息它不会导致语句的执行失败但可能会影响到查询结果或性能。SHOWWARNINGS;SHOW WARNINGS 命令的输出结果包含以下列Level警告的级别如 Warning、Note 等。 Code警告的代码。 Message警告的具体消息。通过查看警告信息可以了解到语句执行过程中可能存在的问题或异常情况如截断数据、丢失数据等。根据警告信息可以进行相应的调整和处理以确保查询的正确性和性能。14.查看自增主键最大值使用 MAX 函数。SELECTMAX(id)FROMyour_table_name;查看表状态SHOWTABLESTATUSLIKEyour_table_name;在查询结果中您可以查找 Auto_increment 列它将显示自增主键的下一个值。参考文献MySQL 8.0 Reference Manual :: 13.2.13 SELECT StatementMySQL 8.0 Reference Manual :: 13.2.18 UNION ClauseMySQL 8.0 Reference Manual :: 13.2.13.2 JOIN ClauseMySQL 8.0 Reference Manual :: 13.8.2 EXPLAIN Statement8.8.1 Optimizing Queries with EXPLAIN