Mysql查询语句select
MySQL从数据表中查询数据的基本语句为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]
[FROM table_references
[PARTITION partition_list]]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
[HAVING where_condition]
[WINDOW window_name AS (window_spec)
[, window_name AS (window_spec)] ...]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[into_option]
[FOR {UPDATE | SHARE}
[OF tbl_name [, tbl_name] ...]
[NOWAIT | SKIP LOCKED]
| LOCK IN SHARE MODE]
[into_option]
-- into_option: {
INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name] ...
}
-- WHERE子句是可选项，如果选择该项，将限定查询行必须满足的查询条件。
-- GROUP BY <字段>，该子句告诉MySQL如何显示查询出来的数据，并按照指定的字段分组。
-- [ORDER BY <字段>]，该子句告诉MySQL按什么样的顺序显示查询出来的数据，可以进行的排序有升序（ASC）、降序（DESC）。
-- [LIMIT [<offset>,] <row count>]，该子句告诉MySQL每次显示查询出来的数据条数。
Select语法可以省略from字句
⚡ 即SELECT 也可用于检索不参考任何表的计算行。
直接使用表达式 select select_expr
mysql> SELECT 1 + 1;
-> 2
-- 可以将DUAL指定为虚拟表
mysql> SELECT 1 + 1 FROM DUAL;
-> 2
设置别名
可以使用AS设置别名，页可以省略AS
SELECT CONCAT(last_name,', ',first_name) AS full_name
FROM mytable ORDER BY full_name;
SELECT CONCAT(last_name,', ',first_name) full_name
FROM mytable ORDER BY full_name;
-- 当设置别名后，可以使用别名作为字段前缀
SELECT t1.name, t2.salary FROM employee AS t1, info AS t2
WHERE t1.name = t2.name;
SELECT t1.name, t2.salary FROM employee t1, info t2
WHERE t1.name = t2.name;
1.查询所有字段
-- 推荐使用 * 通配符
select * from table_name;
2.查询指定字段
select id,col1 from tb1;
-- 结果
id|col1|
--+----+
1| 101|
2| 200|
3. 使用where限定条件
select id,col1 from tb1 where col1>110;
-- 结果
id|col1|
--+----+
2| 200|
4.带IN关键字的查询
-- IN操作符用来查询满足指定范围内的条件的记录，使用IN操作符，将所有检索条件用括号括起来，检索条件之间用逗号分隔开
select id,col1 from tb1 where col1 in (99,200);
-- 结果
id|col1|
--+----+
2| 200|
select id,col1 from tb1 where col1 in (101,200);
--结果
id|col1|
--+----+
1| 101|
2| 200|
5. 带BETWEEN AND的范围查询
-- BETWEEN AND用来查询某个范围内的值，该操作符需要两个参数，即范围的开始值和结束值，如果字段值满足指定的范围查询条件，则这些记录被返回。
select id,col1,col2 from tb1 where col1 between 99 and 300;
-- 结果
id|col1|col2|
--+----+----+
1| 101| 301|
2| 200| 400|
6.带LIKE的字符匹配查询
-- 通配符是一种在SQL的WHERE条件子句中拥有特殊意思的字符。SQL语句中支持多种通配符，可以和LIKE一起使用的通配符有‘%’和‘_’
-- 百分号通配符‘%’，匹配任意长度的字符，甚至包括零字符
-- 下划线通配符‘_’，一次只能匹配任意一个字符
-- 例如，返回以J开头的所有col3列
select col3 from tb1 where col3 like 'j%'
-- 结果
col3 |
-------+
Jack |
Jackson|
-- 例如，返回以‘j’开头并以‘k’结尾的col3列
select col3 from tb1 where col3 like 'j%k'
-- 结果
col3|
----+
Jack|
-- 查询以k结尾，且前面有三个字符的col3列
select col3 from tb1 where col3 like '___k'
-- 结果
col3|
----+
Jack|
luck|
7. 查询空值
-- 空值不同于0，也不同于空字符串。空值一般表示数据未知、不适用或将在以后添加数据。在SELECT语句中使用IS NULL子句，可以查询某字段内容为空记录。
select id,col3 from tb1 where col3 is null;
-- 结果
id|col3|
--+----+
1| |
2| |
8.带AND的多条件查询
-- 使用AND操作符限定只有满足所有查询条件的记录才会被返回。
select id,col3 from tb1 where col3 is null and id = 1;
-- 结果
id|col3|
--+----+
1| |
9.带OR的多条件查询
-- 与AND相反，在WHERE声明中使用OR操作符，表示只需要满足其中一个条件的记录即可返回。OR也可以连接两个甚至多个查询条件，多个条件表达式之间用OR分开。
select id,col2 from tb1 where id = 1 or id = 2
-- 结果
id|col2|
--+----+
1| 301|
2| 400|
OR操作符和IN操作符很多时候使用后的结果是一样的，它们可以实现相同的功能，但是使用IN操作符使得检索语句更加简洁明了，并且IN执行的速度要快于OR。
更重要的是，使用IN操作符可以执行更加复杂的嵌套查询
10.查询结果不重复
-- 有时出于对数据分析的要求，需要消除重复的记录值，该如何操作呢？在SELECT语句中，可以使用DISTINCT关键字指示MySQL消除重复的记录值。
-- 即DISTINCT字段名
select col2 from tb1;
-- 结果，发现200 有两个
col2|
----+
301|
400|
200|
200|
500|
select distinct col2 from tb1;
-- 结果
col2|
----+
301|
400|
200|
500|
11.查询结果排序
-- 降序
select id, col2 from tb1 order by col2 desc;
-- 结果
id|col2|
--+----+
5| 500|
2| 400|
1| 301|
3| 200|
4| 200|
12.使用分组查询
-- 分组查询是对数据按照某个或多个字段进行分组。MySQL中使用GROUP BY关键字对数据进行分组
-- 分组语法 GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
-- GROUP BY关键字通常和集合函数一起使用，比如MAX()、MIN()、COUNT()、SUM()、AVG()。
操作演示：
-- 创建测试版和测试数据
CREATE TABLE `fruit` (
`id` tinyint unsigned NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
`s_id` varchar(100) DEFAULT NULL COMMENT '供应商ID',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
-- 测试数据
INSERT INTO fruit (name,s_id) VALUES
('橘子','1'),
('苹果','1'),
('香蕉','2'),
('梨子','2'),
('桃子','2'),
('苹果','3'),
('火龙果','4'),
('桂圆','3');
e.g.
--1.根据供应商s_id进行分组
select s_id,count(*) as total from fruit group by s_id ;
-- 结果
s_id|total|
----+-----+
1 | 2|
2 | 3|
3 | 2|
4 | 1|
--2. 使用上述分组后，发现如果需要显示name字段，则会出现异常
select s_id,name,count(*) as total from fruit group by s_id ;
-- 结果只出现第一个水果名称
s_id|name|total|
----+----+-----+
1 |橘子 | 2|
2 |香蕉 | 3|
3 |苹果 | 2|
4 |火龙果 | 1|
-- 3. 解决方法：在MySQL中，可以在GROUP BY子句中使用GROUP_CONCAT()函数，将每个分组中各个字段的值显示出来。
select s_id,count(*) as total,group_concat(name) as names from fruit group by s_id;
-- 结果
s_id|total|names |
----+-----+--------+
1 | 2|橘子,苹果 |
2 | 3|香蕉,梨子,桃子|
3 | 2|苹果,桂圆 |
4 | 1|火龙果 |
-- 4.使用HAVING过滤分组
-- 在最初的语法中，我们可以看到group by的完整语法为
GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]] [HAVING where_condition]
-- GROUP BY可以和HAVING一起限定显示记录所需满足的条件，只有满足条件的分组才会被显示。
--问题？ 我们我们要查询供应商提供的水果名称大于2的，如果查询呢，可以使用HAVING进行过滤
select s_id,count(*) as total,group_concat(name) as names from fruit group by s_id having count(name) >2;
-- 结果
s_id|total|names |
----+-----+--------+
2 | 3|香蕉,梨子,桃子|
-- 5. 在GROUP BY子句中使用WITH ROLLUP 统计总数
-- 使用WITH ROLLUP关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。
select s_id,count(*) as total from fruit group by s_id with rollup ;
-- 结果
s_id|total|
----+-----+
1 | 2|
2 | 3|
3 | 2|
4 | 1|
| 8|
-- 6.多字段分组 (分组后还有重复数据)
-- 使用GROUP BY可以对多个字段进行分组，GROUP BY关键字后面跟需要分组的字段，MySQL根据多字段的值来进行层次分组，分组层次从左到右，
-- 即先按第1个字段分组，然后在第1个字段值相同的记录中再根据第2个字段的值进行分组，以此类推。
-- 7. GROUP BY和ORDER BY一起使用
-- 上述我们使用goup by 分组后，数据默认是按照s_id 进行排序的，如
s_id|total|
----+-----+
1 | 2|
2 | 3|
3 | 2|
4 | 1|
-- 我们也可以结合ORDER BY指定排序
select s_id,count(*) as total from fruit group by s_id order by total;
-- 结果
s_id|total|
----+-----+
4 | 1|
1 | 2|
3 | 2|
2 | 3|
HAVING关键字与WHERE关键字都是用来过滤数据的，两者有什么区别呢？
其中重要的一点是，HAVING在数据分组之后进行过滤来选择分组，
而WHERE在分组之前来选择记录，
另外，WHERE排除的记录不再包括在分组中。
13.使用LIMIT限制查询结果的数量
SELECT返回所有匹配的行，有可能是表中所有的行，若仅仅需要返回第一行或者前几行，可使用LIMIT关键字，基本语法格式如下：
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
-- 即
LIMIT [位置偏移量] 行数
-- 或（MySQL8
LIMIT 行数 OFFSET [位置偏移量]
位置偏移量参数指示MySQL从哪一行开始显示，是一个可选参数，如果不指定位置偏移量，将会从表中的第一条记录开始
第一条记录的位置偏移量是0，第二条记录的位置偏移量是1，以此类推;
第二个参数行数指示返回的记录条数。
e.g.
-- 查询所有数据
select id,name from fruit;
-- 结果
id|name|
--+----+
1|橘子 |
2|苹果 |
3|香蕉 |
4|梨子 |
5|苹果 |
6|火龙果 |
7|桂圆 |
8|桃子 |
-- 查询2条数据
select * from fruit limit 2;
-- 结果
id|name|s_id|
--+----+----+
1|橘子 |1
2|苹果 |1
-- 从第一行开始，返回2个
select * from fruit limit 1,2;
-- 等价
select * from fruit limit 2 offset 1;
-- 结果
id|name|s_id|
--+----+----+
2|苹果 |1 |
3|香蕉 |2 |
