淘宝网站建设杭州,外贸网站自我建设与优化,最权威的做网站设计公司价格,我厂有大量手袋订单外发文章目录 1.SELECT 语句2.SELECT 子句3.FROM 子句4.WHERE 子句5.GROUP BY 子句6.HAVING 子句7.ORDER BY 子句8.LIMIT 子句9.DISTINCT 子句10.JOIN 子句11.UNION 子句12.查看数据表记录数13.检查查询语句的执行效率14.查看 SQL 执行时的警告参考文献 1.SELECT 语句
MySQL 的 SE… 文章目录 1.SELECT 语句2.SELECT 子句3.FROM 子句4.WHERE 子句5.GROUP BY 子句6.HAVING 子句7.ORDER BY 子句8.LIMIT 子句9.DISTINCT 子句10.JOIN 子句11.UNION 子句12.查看数据表记录数13.检查查询语句的执行效率14.查看 SQL 执行时的警告参考文献 1.SELECT 语句
MySQL 的 SELECT 语句用于从数据库表中检索数据。功能强大语句结构复杂多样。不过基本的语句格式像下面这个样子。
SELECT [列名称] FROM [表名称] where [条件]一个完整的 SELECT 语句包含一些可选的子句。SELECT 语句定义如下
SELECT clause
[FROM clause]
[WHERE clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause]
[LIMIT clause]SELECT 子句是必选的其它子句是可选的。
一个 SELECT 可以在不引用任何表的情况下进行计算也就是没有其他任何字句只有 SELECT 子句。
SELECT 1 1 AS sum;
-----
| sum |
-----
| 2 |
-----一个 SELECT 语句中子句的顺序是固定的。如 GROUP BY 子句不会位于 WHERE 子句前面。SELECT 语句不同子句的执行顺序
开始 FROM子句 WHERE子句 GROUP BY子句 HAVING子句 SELECT子句 ORDER BY子句 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: {INTO OUTFILE file_name[CHARACTER SET charset_name]export_options| INTO DUMPFILE file_name| INTO var_name [, var_name] ...
}其中 select_expr 是必选的表示要查询的列、表达式或使用 * 表示所有列。
SELECT * FROM t1 INNER JOIN t2 ...可以对列使用函数进行运算并使用 AS 关键字对结果列命名AS 是可选的可以省略。
SELECT AVG(score) AS avg_score, t1.* FROM t1 ...# 或
SELECT AVG(score) avg_score, t1.* FROM t1 ...3.FROM 子句
FROM 子句指示要从中检索行的表。如果为多个表命名则执行连接。对于指定的每个表您可以选择指定一个别名。
FROM table_references [PARTITION partition_list]SELECT 支持显式分区选择使用 PARTITION 子句在 table_references 表的名称后面跟着一个分区或子分区列表(或两者都有)在这种情况下只从列出的分区中选择行而忽略表的任何其他分区。关于分区可参考 Chapter 24 Partitioning。
4.WHERE 子句
如果给定 WHERE 子句则指示行必须满足的一个或多个条件才能被选中。where_condition 是一个表达式对于要选择的每一行其计算结果为 true 才会被选择。如果没有 WHERE 子句将选择所有行。
[WHERE where_condition]下面的运算符可在 WHERE 子句的条件表达式中使用。
运算符描述等于! 或 不等于大于小于大于等于小于等于BETWEEN AND在某个范围内LIKE搜索某种模式AND多个条件与OR多个条件或
1WHERE IN 的用法
IN 在 WHERE 子句中的用法主要有两种
IN 后面是子查询产生的记录集注意子查询结果数据列只能有一列且无需给子查询的结果集添加别名。
SELECT * FROM tbl_name1 WHERE col_name1 IN (SELECT col_name2 FROM tbl_name2); IN 后面是数据集合。
SELECT * FROM tbl_name WHERE col_name IN (foo, bar, baz, qux); 注意如果数据类型是字符串一定要将字符串用单引号引起来。
5.GROUP BY 子句
GROUP BY 子句中的数据列应该是 SELECT 指定的数据列中的所有列除非这列是用于聚合函数如 SUM()、AVG()、COUNT()等。
但是如果 SELECT 指定的数据列没有用于聚合函数也不在 GROUP BY 子句中按理说会报错但是 MySQL 会选择第一条显示在结果集中。
# 选择发起加好友请求次数超过10次的QQ(uin)被加方to_uin只会显示第一个
SELECT uin, to_uin, count(*) AS cnt from inner_raw_add_friend_20170514 GROUP BY uin HAVING cnt10;6.HAVING 子句
HAVING 和 WHERE 子句一样用于指定选择条件。
但 HAVING 和 WHERE 子句的用法上却有明显的区别。
作用的对象不同。WHERE 子句作用于表和视图HAVING 子句作用于组。
# 选取 QQ 3585076592 和 3585075773 在 20170514 当天发出加好友请求次数且满足次数10
SELECT uin,count(*) AS cnt FROM inner_raw_add_friend_20170514 where uin3585076592 or uin3585075773 GROUP BY uin HAVING cnt10;作用的阶段不同。
WHERE 在分组和聚集计算之前选取输入行因此它控制哪些行进入聚集计算而 HAVING 在分组和聚集之后选取分组。因此WHERE 子句不能包含聚集函数因为试图用聚集函数判断哪些行输入给聚集运算是没有意义的。 相反HAVING 子句一般包含聚集函数。当然也可以使用 HAVING 对结果集进行筛选但不建议这样做同样的条件可以更有效地用于 WHERE 阶段。
# 查询指定 QQ 加好友请求信息where作用于输入阶段的数据集
SELECT * FROM inner_raw_add_friend_20170514 WHERE uin3585078528;# 作用等同于 WHERE 但 HAVING 作用于结果阶段的结果集
SELECT * FROM inner_raw_add_friend_20170514 HAVING uin3585078528;7.ORDER BY 子句
ORDER BY 子句用于根据指定的列对结果集进行排序。
[ORDER BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]]ORDER BY 语句默认按照升序 ASCascend对记录进行排序。如果希望按照降序排序可以使用 DESCdescend关键字随机使用随机数函数RAND()。
在指定待排序的列时不建议使用列位置从1开始因为该语法已从SQL标准中删除。
比如以 QQ 号码降序排序。
SELECT * FROM inner_raw_add_friend_20170514 ORDER BY uin DESC;8.LIMIT 子句
LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。
[LIMIT {[offset,] row_count | row_count OFFSET offset}]LIMIT 接受一个或两个数值参数。参数必须是一个整数常量。如果给定两个参数有两种用法。
offset,row_count
# 或
row_count OFFSET offsetoffset 为返回记录行的开始偏移量从 0 开始row_count 为返回记录行的最大数目。
只给一个参数表示返回记录行的 Top 最大行数起始偏移量默认为 0。
返回从起始偏移量开始返回剩余所有的记录可以使用一些值很大的第二个参数。如检索所有从第 96 行到最后一行。
SELECT * FROM tbl LIMIT 95,18446744073709551615;注意MySQL目前不支持使用 -1 表示返回从偏移量开始剩余的所有记录即下面的写法是错误的
SELECT * FROM tbl LIMIT 95,-19.DISTINCT 子句
DISTINCT 关键字用于查询结果中去除重复的行只返回唯一的行。
1利用 DISTINCT 结合 COUNT() 函数可以统计不重复记录的数量。
# 选择每一个 QQ 发起加好友请求涉及到的不同的 QQ 数
SELECT uin, count(distinct to_uin) c FROM add_friend GROUP BY uin;2DISTINCT 用于选择不同的记录且只能放在所选列的开头作用于紧随其后的所有列。
# 查询 uin 和 to_uin 不重复的加好友请求
SELECT DISTINCT uin, to_uin FROM add_friend;# 示例数据表
uin to_uin
10000 123456
10000 121212
10001 121212
10001 131313# 结果集
uin to_uin
10000 123456
10000 121212
10001 121212
10001 131313如果想使 DISTINCT 的功能作用于第二列的 to_uin使用 DISTINCT 是无望了因为 MySQL 语法尚不支持可以使用 GROUP BY 取而代之。
SELECT uin, to_uin FROM add_friend WHERE GROUP BY to_uin;# 结果集
uin to_uin
10000 123456
10000 121212
10001 131313这个奇怪的技巧只能存在于 MySQL 中因为标准的 SQL 语法规定非聚合函数中的列一定要存在于 GROUP BY 子句中。MySQL 规定当非聚合函数中的列不存在于 GROUP BY 子句中则选择每个分组的第一行。
3COUNT DISTINCT 统计符合条件的记录数量。
如果像对符合条件的记录进行 COUNT DISTINCT那么如何添加条件呢
参见 MySQL distinct count if conditions unique可以使用下面的方法。
COUNT(DISTINCT CASE WHERE 条件 THEN 字段 END)参见 mysql count if distinct也可以使用下面这种方法。
COUNT(DISTINCT col_name1, IF(col_name21, true, null))10.JOIN 子句
MySQL 支持 SELECT 语句以及多表 DELETE 和 UPDATE 语句中使用 JOIN。
MySQL 支持三种 JOIN。
内连接 INNER JOININNER 可省略。
内连接返回两个表中满足连接条件的记录。
SELECT columns
FROM table1
INNER JOIN table2 ON table1.column table2.column;左连接 LEFT JOIN。
左连接返回左表中所有记录以及与右表中满足连接条件的记录。如果右表中没有匹配的记录对应位置将显示 NULL。
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column table2.column;右连接 RIGHT JOIN。
右连接与左连接类似但是返回右表中所有记录以及与左表中满足连接条件的记录。如果左表中没有匹配的记录对应位置将显示 NULL。
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column table2.column;关于 JOIN 一些问题需要注意一下。
多表查询与 INNER JOIN 的区别。
实际测试一下可以看出区别以 a 和 b 表为例。
SELECT * FROM a;
------------
| id | col |
------------
| 1 | 11 |
| 2 | 12 |
------------SELECT * FROM b;
------------
| id | col |
------------
| 2 | 22 |
| 3 | 23 |
------------SELECT * FROM a,b;
------------------------
| id | col | id | col |
------------------------
| 1 | 11 | 2 | 22 |
| 2 | 12 | 2 | 22 |
| 1 | 11 | 3 | 23 |
| 2 | 12 | 3 | 23 |
------------------------SELECT* FROM a JOIN b ON a.idb.id;
------------------------
| id | col | id | col |
------------------------
| 2 | 12 | 2 | 22 |
------------------------从结果可以看出SELECT FROM 两个表的结果是两张表记录的笛卡尔乘积INNER JOIN 则只连接含有相同字段的记录。
如果多表查询时指定与 ON 相同的条件则查询结果一致二者没有任何区别。
SELECT * FROM a,b WHERE a.idb.id;
------------------------
| id | col | id | col |
------------------------
| 2 | 12 | 2 | 22 |
------------------------在性能上使用 EXPLAIN SQL 查看查询情况二者也是一样的。也就说多表查询与 INNER JOIN 并无区别只是写法上不同而已。
2关于 CROSS JOIN。
实际上在 MySQL 中仅限于 MySQL CROSS JOIN 与 INNER JOIN 的表现是一样的在不指定 ON 条件得到的结果都是笛卡尔积反之取得两个表各自匹配的结果。
3关于 OUTER JOIN。
外连接就是求两个集合的并集。从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录然后加上左表中剩余的记录最后加上右表中剩余的记录。
MySQ L不支持 OUTER JOIN但是我们可以对左连接和右连接的结果做 UNION 操作来实现。
11.UNION 子句
UNION 的作用是将两次或多次查询结果纵向合并起来。
query_expression_body UNION [ALL | DISTINCT] query_block[UNION [ALL | DISTINCT] query_expression_body][...]下面是一个示例。
mysql SELECT 1, 2;
------
| 1 | 2 |
------
| 1 | 2 |
------
mysql SELECT a, b;
------
| a | b |
------
| a | b |
------
mysql SELECT 1, 2 UNION SELECT a, 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 * FROM table1 ORDER BY uin DESC) UNION (SELECT * FROM table2 ORDER BY uin DESC);uin nickname
10001 monkey
10002 monkey king
20000 cat
20001 dog可以发现内层排序没有发生作用那现在试试在外层排序。
SELECT * FROM table1 UNION SELECT * FROM table2 ORDER BY uin DESC;uin nickname
20001 dog
20000 cat
10002 monkey king
10001 monkey可见外层排序发生了作用。那是不是内层排序就没有用了呢其实换个角度想想内层先排序如果外层又排序明显内层排序显得多余所以 MySQL 优化了 SQL 语句不让内层排序起作用。要想内层排序起作用必须要使内层排序的结果能影响最终的结果如加上 LIMIT。
(SELECT * FROM table1 ORDER BY uin DESC LIMIT 2) UNION (SELECT * FROM table2 ORDER BY uin DESC LIMIT 2);uin nickname
10002 monkey king
10001 monkey
20001 dog
20000 cat此外UNION 与 JOIN 在使用时有一个本质区别我们必须知道。
UNION 只能作用于 SELECT 结果集不能直接作用于数据表而 JOIN 则恰恰相反只作用于数据表不能直接作用于 SELECT 结果集可以将 SELECT 结果集指定别名作为派生表。
12.查看数据表记录数
查看数据表行数有多种方法。
使用 COUNT(*)
SELECT COUNT(*) FROM tbl_name;对于 MyISAM 数据表很快建议使用因为 MyISAM 数据表事先将行数缓存起来可直接获取。InnoDB 数据表不建议使用当数据表行数过大时因需要扫描全表查询较慢。
查看 information_schema.tables 视图
SELECT table_rows FROM information_schema.tables WHERE TABLE_SCHEMA DatabaseName and table_nameTableName; information_schema 是 MySQL 中的一个系统数据库它包含了关于数据库、表、列等元数据信息。可以通过查询 information_schema.tables 视图获取指定数据表的记录数。
使用 SHOW TABLE STATUS 命令
SHOW TABLE STATUS LIKE table_name;需要注意的是SHOW TABLE STATUS 命令返回的行数是一个近似值并不是实时的准确值。这是因为 MySQL 在某些情况下会对行数进行估算而不是实时计算。如果需要准确的行数建议使用 COUNT(*) 函数或查询 information_schema.tables 视图。
13.检查查询语句的执行效率
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额外的信息如使用了临时表、使用了文件排序等。14.查看 SQL 执行时的警告
SHOW WARNINGS 是一个用于查看最近一次执行的语句产生的警告信息的命令。在 MySQL 中警告Warning是一种表示潜在问题或异常情况的消息它不会导致语句的执行失败但可能会影响到查询结果或性能。
SHOW WARNINGS;SHOW WARNINGS 命令的输出结果包含以下列
Level警告的级别如 Warning、Note 等。
Code警告的代码。
Message警告的具体消息。通过查看警告信息可以了解到语句执行过程中可能存在的问题或异常情况如截断数据、丢失数据等。根据警告信息可以进行相应的调整和处理以确保查询的正确性和性能。 参考文献
MySQL 8.0 Reference Manual :: 13.2.13 SELECT Statement MySQL 8.0 Reference Manual :: 13.2.18 UNION Clause MySQL 8.0 Reference Manual :: 13.2.13.2 JOIN Clause MySQL 8.0 Reference Manual :: 13.8.2 EXPLAIN Statement 8.8.1 Optimizing Queries with EXPLAIN
