所有分类
  • 所有分类
  • 未分类

MySQL语句-查询语句大全(有示例)

简介

本文介绍MySQL查询语法(命令)的使用。包括:基本查询、运算条件、排序、分页、分组、联结、组合、子查询、distinct等。

SQL查询语句官网:MySQL :: MySQL 8.0 Reference Manual :: 13.2.10 SELECT Statement

基本查询

操作命令示例/说明
查询所有列SELECT * FROM 表名SELECT * FROM table_name;
查询指定列SELECT 列1,列2,…FROM 表名;SELECT id,name FROM table_name;
使用 AS  给字段起别名SELECT id AS  序号, name AS  名字, gender AS  性别 FROM students;AS 不写也可以,跟写AS 一样效果。
通过 AS  给表起别名SELECT col1,col2 FROM table_name AS  xxxSELECT s.id,s.name,t.gender FROM students AS  s, teachers AS  t;

SELECT子句必须遵循的顺序(从左到右对应从上到下)

子句说明是否必须使用
SELECT要返回的列或表达式
FROM从中检索数据的表仅在从表选择数据时使用
WHERE行级过滤
GROUP BY分组说明仅在按组计算聚集时使用
HAVING组级过滤
ORDER BY输出排序顺序
LIMIT要检索的行数

运算条件

WHERE 后面支持多种运算符,进行条件的处理

比较运算符、逻辑运算符、模糊查询、范围查询、空判断

运算符详情示例
比较等于: = 大于: > 大于等于: >= 小于: < 小于等于: <= 不等于: != 或 <>例:查询编号大于3的学生:          SELECT * FROM students WHERE id > 3; 例:查询编号不大于4的学生:       SELECT * FROM students WHERE id <= 4; 例:查询姓名不是“黄蓉”的学生:   SELECT * FROM students WHERE name != ‘黄蓉’; 例:查询没被删除的学生:            SELECT * FROM students WHERE is_delete=0;
逻辑AND,OR,NOT //sql 会首先执行 AND 条件,再执行 OR 语句。除非加括号例:查询编号大于3的女同学:               SELECT * FROM students WHERE id > 3 AND gender=0; 例:查询编号小于4或没被删除的学生: SELECT * FROM students WHERE id < 4 OR is_delete=0; 例:查询编号小于4,或大于10而且没被删除的学生: SELECT * FROM students WHERE id < 4 or id > 10 AND is_delete=0; 例:查询编号小于4或大于10,而且没被删除的学生: SELECT * FROM students WHERE (id < 4 or id > 10) AND is_delete=0;
模糊查询LIKE % 任意多个任意字符 _ 一个任意字符例:查询姓黄的学生:                           SELECT * FROM students WHERE name LIKE ‘黄%’; 例:查询姓黄并且“名”是一个字的学生:SELECT * FROM students WHERE name LIKE ‘黄_’; 例:查询姓黄或叫靖的学生:SELECT * FROM students WHERE name LIKE ‘黄%’ OR name LIKE ‘%靖’;
范围查询分为 连续/非连续 范围查询 IN:非连续 BETWEEN … AND …:连续     (包含端点)例:查询编号是1或3或8的学生: SELECT * FROM students WHERE id in(1,3,8); 例:查询编号为3至8的学生:       SELECT * FROM students WHERE id BETWEEN 3 AND 8; 例:查询编号是3至8的男生:    SELECT * FROM students WHERE (id BETWEEN 3 AND 8) AND gender=1;
空判断判断为空/非空 注意: null与’’是不同的例:查询没有填写身高的学生:SELECT * FROM students WHERE height IS NULL; 例:查询填写了身高的学生:   SELECT * FROM students WHERE height IS NOT NULL; 例:查询填写了身高的男生:   SELECT * FROM students WHERE height IS NOT NULL AND gender=1;

优先级

优先级由高到低的顺序为:小括号 > NOT > 比较运算符 > 逻辑运算符

AND比OR先运算,如果同时出现并希望先算or,需要结合()使用

排序

排序查询语法

SELECT * FROM 表名 ORDER BY 列1 ASC|DESC[,列2 ASC|DESC,…]

语法说明

  • 将行数据按照列1进行排序,如果某些行 列1 的值相同时,则按照 列2 排序,以此类推
  • asc:从小到大排列,即升序;desc从大到小排序,即降序
  • 默认按照列值从小到大排列(即asc关键字)

示例

  • 查询未删除男生信息,按学号降序
    • SELECT * FROM students WHERE gender=1 and is_delete=0 ORDER BY id desc;
  • 查询未删除学生信息,按名称升序
    • SELECT * FROM students WHERE is_delete=0 ORDER BY name;
  • 显示所有的学生信息,先按照年龄从大–>小排序,当年龄相同时 按照身高从高–>矮排序
    • SELECT * FROM students  ORDER BY age desc,height desc;

分页

语法

SELECT * FROM 表名 LIMIT start,count

说明

  • 从start开始,获取count条数据,start默认值为0。
  • 需要获取数据的前n条的时候可以直接写上 xxx limit n;
  • 在sql语句中limit后不可以直接加公式

示例

  • 查询前3行男生信息
    • SELECT * FROM students WHERE gender=1 LIMIT 0,3;
  • 每页显示m条,显示第n页
    • SELECT * FROM students WHERE deleted LIMIT (n-1)*m,m

分组

语法

SELECT xxx GROUP BY { <列名> | <表达式> | <位置> } [ASC | DESC]

示例

每个厂家提供多种商品。表结构如下:

字段名字段含义
vendor_id厂家id
product_id商品id
product_count该商品的数量

例1:按厂家id去重,每个厂家id只返回一条数据

SELECT * FROM products GROUP BY vendor_id

例2:查询每个厂家的商品种类的数量。

SELECT vendor_id, COUNT(*) AS number_product_type FROM products GROUP BY vendor_id

例3:查询每个厂家的商品种类的数量以及每个厂家所有商品的数量总和

SELECT vendor_id, COUNT(*), SUM(product_count) AS number_product_type FROM products GROUP BY vendor_id

例4:查询每个厂家的每种商品的商品数量。 

SELECT vendor_id, product_id, COUNT(*) AS number_product FROM products GROUP BY vendor_id, product_id

注意事项

  • GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套,为数据分组提供更细致的控制。
  • 如果在GROUP BY子句中嵌套了分组,数据将在最后规定的分组上进行汇总。换句话说,在建立分组时,指定的所有列都一起计算(所以不能从个别的列取回数据)。
  • GROUP BY子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在SELECT中使用表达式,则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
  • 除聚集计算语句外, SELECT语句中的每个列都必须在GROUP BY子句中给出。
  • 如果分组列中具有NULL值,则NULL将作为一个分组返回。如果列中有多行NULL值,它们将分为一组。
  • GROUP BY子句必须出现在WHERE子句之后, ORDER BY子句之前

高级用法

语句作用
GROUP BY + HAVINGHAVING 条件表达式:用来过滤分组结果。 HAVING和WHERE类似,但HAVING用于过滤GROUP BY后的数据,而WHERE用于过滤GROUP BY以前的单行数据。 示例:厂家提供多种商品,商品表中有厂家id。查出商品数量大于2的每个厂家的商品种类数量。
SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING num_prods > 2
GROUP BY + GROUP_CONCAT()GROUP_CONCAT(字段名):根据分组结果,放置每一个分组中某字段的集合
GROUP BY + 聚合函数通过GROUP_CONCAT()的启发,通过集合函数来对这个值的集合做一些操作
GROUP BY + WITH ROLLUPWITH ROLLUP的作用是:在最后新增一行,来记录当前表中该字段对应的操作结果,一般是汇总结果,比如把某一列求和。

联结

语法

SELECT * FROM 表1 INNER/OUTER/LEFT/RIGHT JOIN 表2 ON 表1.列 运算符 表2.列

对于外联结 outer关键字可以省略

示例

  • 使用内连接查询班级表与学生表
    • SELECT * FROM students INNER JOIN classes ON students.cls_id = classes.id;
  • 使用内连接查询班级表与学生表(多个条件)
    • SELECT * FROM students INNER JOIN classes ON students.cls_id = classes.id AND students.delete_flag = 0 AND classes.delete_flag = 0;
  • 使用左连接查询班级表与学生表(此处使用了AS为表起别名,目的是编写简单)
    • SELECT * FROM students AS s LEFT JOIN classes AS c ON s.cls_id = c.id;
  • 使用右连接查询班级表与学生表
    • SELECT * FROM students AS s RIGHT JOIN classes AS c ON s.cls_id = c.id;
  • 查询学生姓名及班级名称
    • SELECT s.name,c.name FROM students AS s INNER JOIN classes AS c ON s.cls_id = c.id;

组合

如果union要与limit或者order by一起使用,查询子句需要用括号,例如:

(SELECT *
 FROM t_visitor_record
 WHERE substr(t_visitor_record.create_time, 12, 8) BETWEEN '00:00:00' AND '11:59:59'
 ORDER BY t_visitor_record.create_time)
UNION
(SELECT *
 FROM t_visitor_record
 WHERE substr(t_visitor_record.create_time, 12, 8) BETWEEN '12:00:00' AND '23:59:59'
 ORDER BY t_visitor_record.create_time)

union与union all

union会去重,如果在一个条件中返回了,下一个条件中就算是有也不会返回。union all不会去重, 如果在一个条件中返回了,下一个如果有重复的,也会返回。

子查询

定义

一个 select 语句中,嵌入另外一个 select 语句, 被嵌入的 select 语句称为子查询语句,外部select语句则称为主查询。

主查询和子查询的关系

  • 子查询是嵌入到主查询中
  • 子查询是辅助主查询的,要么充当条件,要么充当数据源
  • 子查询是可以独立存在的语句,是一条完整的 select 语句

不相关子查询

分为三种:标量子查询,列级子查询,行级子查询。

标量子查询

示例:查询班级学生平均年龄

先查询班级学生平均年龄,再查询大于平均年龄的学生

SELECT * FROM students WHERE age > (SELECT AVG(age) FROM students);

列级子查询

示例:查询学生所在班级的所有班级名字

先找出学生表中所有的班级 id,再找出班级表中对应的名字

SELECT name FROM classes WHERE id IN (SELECT cls_id FROM students);

行级子查询

示例1:查找班级年龄最大而且身高最高的学生。

SELECT * FROM students WHERE (height, age) = (SELECT MAX(height), MAX(age) FROM students);

示例2:查询所有年龄为20的学生的姓名和身高(本处刻意用这个方法,实际不需要)

SELECT * FROM students WHERE (name, height) = (SELECT * FROM students WHERE age = 20);

示例3:查找班级有相同的身高和年龄的学生的所有数据。

SELECT * FROM students WHERE (height, age) IN (SELECT height, age FROM students GROUP BY height, age HAVING COUNT(*) > 1);

相关子查询

子查询步骤示例
作为计算字段查找班里每个学生的名字及成绩。 步骤: 先从t_student查找所有学生 再统计每个学生的成绩(t_grade)SELECT name, (SELECT score FROM t_grade WHERE t_grade.student_id = t_student.id) FROM t_student

distinct

表的数据

表A:

表B:

1.作用于单列

SELECT DISTINCT name FROM A

执行后结果如下:

2.作用于多列

示例2.1

SELECT DISTINCT name, id FROM A

执行后结果如下:

示例2.2

SELECT DISTINCT xing, ming FROM B

 返回如下结果:

返回的结果为两行,这说明distinct并非是对xing和ming两列“字符串拼接”后再去重的,而是分别作用于了xing和ming列。

3.COUNT统计

查表中name去重后的数目, SQL Server支持,而Access不支持

SELECT COUNT(DISTINCT name) FROM A;

 count是不能统计多个字段的,下面的SQL在SQL Server和Access中都无法运行。

SELECT COUNT(DISTINCT name, id) FROM A;

若想使用,请使用嵌套查询,如下: 

SELECT COUNT(*) FROM (SELECT DISTINCT xing, name FROM B) AS M;

4.distinct必须放在开头

select id, distinct name from A;   --会提示错误,因为distinct必须放在开头

5.其他

distinct语句中select显示的字段只能是distinct指定的字段,其他字段是不可能出现的。例如,假如表A有“备注”列,如果想获取distinc name,以及对应的“备注”字段,想直接通过distinct是不可能实现的。但可以通过其他方法实现。 ​

NOT EXISTS

例如:查询在t_user表里存在但在t_user_detail不存在的数据

SELECT
    t_user.* 
FROM
    t_user 
WHERE
    t_user.`username` = 'aaa' 
    AND t_user.`delete_flag` = '0' 
    AND EXISTS (
    SELECT
        1 
    FROM
        t_user_detail 
    WHERE
        t_user.id = t_user_detail.user_id 
        AND t_user_detail.delete_flag = '0'
    )
0

评论0

请先

显示验证码
没有账号?注册  忘记密码?

社交账号快速登录