数据查询

本文共--字 阅读约--分钟 | 浏览: -- Last Updated: 2021-04-18

一、SELECT语句

语法：

SELECT
[ALL|DISTINCT|DISTINCTROW]
select_expr [, select_expr ...]
FROM table_references
[WHERE where_condition]
[GROUP BY { col_name|expr|position}
  [ASC|DESC],… [WITH ROLLUP] ]
[HAVING where_condition]
[ORDER BY { col_name|expr|position}
  [ASC|DESC],… ]
[LIMIT { [offset,] row_count | row_count OFFSET offset}];

语法说明：

  • SELECT: 要返回的列或表达式；必选；
  • FROM: 从中检索数据的表，仅在从表选择数据时使用；
  • WHERE: 行级过滤；可选；
  • GROUP BY: 分组说明；仅在按组计算聚合时使用；
  • HAVING: 组级过滤；可选；
  • ORDER BY: 输出排序顺序；可选；
  • LIMIT: 要检索的行数；可选；
  • DISTINCT/DISTINCTROW: 用于消除结果集中的重复行。需要注意的的是，比较是否重复时，是比较所有的列，而不是筛选的部分列；

基本使用：

SELECT cust_name,cust_sex,cust_address FROM mysql_test.customers;

SELECT * FROM mysql_test.customers;

二、列的选择与指定

针对于 select_expr 的说明：

1.选择指定的列

  • 选择多列时用逗号隔开；
  • 结果集中各列的次序是按 SELECT 语句中指定列的次序给出的；
  • * 通配符表示查询所有列；
  • 可以直接给出该列的名称，也可以使用完全限定的列名方式：tbl_name.col_name；

2.定义并使用列的别名

语法 COLUMN_NAME [AS] column_alias

3.替换查询结果集中的数据

需要用到CASE 表达式。

CASE WHEN 条件 1 THEN 表达式 1
	WHEN 条件 2 THEN 表达式 2
	…
ELSE 表达式
END [AS] column_alias

举例：选择 cust_name 和 cust_sex 列，如果 cust_sex列 为’M’ 输出 ‘男’ 否则输出’女’，将cust_sex 列用别名’性别’标注；

SELECT cust_name,
	CASE WHEN cust_sex = 'M' THEN '男'
	ELSE '女'
	END AS 性别
FROM mysql_test.customers;

4.计算列值

可以输出对列值计算后的值

SELECT cust_id + 100
FROM mysql_test.customers;

5.聚合函数

聚合函数是数据库系统中一类系统内置函数，常用于对一组值进行计算，然后返回单个值；通常与 GROUP BY 子句一起使用；除 COUNT 函数外，聚合函数都会忽略空值NULL；

常用函数：

  • COUNT：求组中项数，返回 INT 类型整数；
  • MAX：求最大值；
  • MIN：求最小值；
  • SUM：返回表达式所有值的和；
  • AVG：求组中值的平均值；
  • STD/STDDEV：返回给定表达式中所有值的标准值；
  • VARIANCE：返回给定表达式中所有值的方差；
  • GROUP_CONCAT：返回由属于一组的列值连接组合而成的结果；
  • BIT_AND: 逻辑与；
  • BIT_OR: 逻辑或；
  • BIT_XOR1: 逻辑异或；

三、FROM子句与多表连接查询

一个查询同时涉及两个或两个以上的表，称之为 多表连接查询；分解与组合数据的方法，使得数据库系统能够更有效地存储数据，更方便地处理数据，且可获得更大的可伸缩性；

1.交叉连接

又称笛卡尔积。通过在FROM子句中使用关键字 CROSS JOIN 来连接两张表。FROM 子句也可以省略关键字 CROSS JOIN，而使用逗号分隔交叉连接的两张表。对于存在大量数据的表，应该避免使用交叉连接。

语法：

SELECT * FROM tbl1 CROSS JOIN tbl2;
SELECT * FROM tbl1,tbl2;

2.内连接

通过在查询中设置连接条件的方式来移除查询结果集中某些数据行之后的交叉连接。通过在 FROM 子句中使用关键字 INNER JOIN( 可省略为JOIN) 连接两张表，并使用 ON 子句设置连接条件的方式来实现内连接。

语法：

SELECT some_columns 
FROM table1 INNER JOIN table2
ON some_conditions;

说明：

  • 等值连接：ON子句的连接条件中使用运算符=。
  • 非等值连接：ON子句的连接条件中使用除=以外的比较运算符。
  • 自连接：将一个表与它自身进行连接。

举例：根据学生表和成绩表，使用内连接查询每个学生及其选课成绩的详细信息:

-- 等值连接
SELECT *
FROM tb_student INNER JOIN tb_score 
ON tb_student.studentNo = tb_score.studentNo;

3.外连接

外连接，可以在表中没有匹配记录的情况下仍返回记录，可分为 左外连接 和 右外连接。

  • 左外连接: 关键字 LEFT OUTER JOIN 或 LEFT JOIN。
  • 右外连接: 关键字 RIGHT OUTER JOIN 或 RIGHT JOIN。

结果集中除了匹配的行之外，左外连接则指左表有，右表没有，依然选中，右表中被选择的列置为NULL；右外连接则相反；

举例：使用左外连接查询每个学生及其选课成绩的详细信息。

SELECT *
FROM tb_student LEFT JOIN tb_score
ON tb_student.studentNo = tb_score.studentNo;

四、WHERE子句与条件查询

1.比较运算

用于比较两个表达式的值。

举例：

SELECT *
FROM mysql_test.customers
WHERE cust_sex = '男'

2.判定范围

2.1、BETWEEN…AND

语法：

expression [NOT] BETWEEN expression1 AND expression2

当不使用NOT时，如果表达式 expression 的值在表达式 expression1 与 expression2 之间（包括这两个值），则返回 TRUE，否则返回 FALSE；使用NOT的时候就相反；

举例：查询客户id号在 903 至 912 之间的十个客户的信息。

SELECT *
FROM mysql_test.customers
WHERE cust_id BETWEEN 903 AND 912;

2.2、IN

语法：

expression IN (expression[1,…n])

当要判断的值与该表中任意一个值匹配时会返回TRUE，否则返回 FALSE 。

举例：查询客户id号分别为903、906和908的三个客户的信息。

SELECT *
FROM mysql_test.customers
WHERE cust_id IN (903,906,908);

3.判定空值

语法：expression IS [NOT] NULL

当需要判定一个表达式的值是否为空值时使用；

举例：查询是否存在没有填写客户联系方式的客户。

SELECT *
FROM mysql_test.customers
WHERE cust_contact IS NULL;

4.子查询

子查询是可嵌套在其他SELECT查询中的SELECT查询。

  • 表子查询：即子查询返回的结果集是一个表。
  • 行子查询：即子查询返回的结果集是带有一个或多个值的一行数据。
  • 列子查询：即子查询返回的结果集是一列数据，该列可以有一行或多行，但每行只能有一个值；
  • 标量子查询：即子查询返回的结果集是仅仅是一个值。

4.1、结合关键字IN使用的子查询

主要用于判定一个给定值是否存在于子查询的结果集中；

语法：expression [NOT] IN (subquery)

通常这里的 subquery 子查询只返回一列数据。

举例：使用子查询的方式查询任意所选课程成绩高于 80 分的 学生的学号和姓名信息。

SELECT studentNo,studentName
FROM tb_student
WHERE studentNo IN (SELECT studentNo FROM tb_score WHERE score>80);

需要注意的是使用子查询第一个WHERE后的列名一般就是外键；

4.2、结合比较运算符使用的子查询

语法格式：expression {=|<|<=|>|>=|<=>|<>|!=}{ALL|SOME|ANY}(subquery)

说明：

  • ALL: 用于指定表达式需要与子查询结果集中的每个值都进行比较；
  • SOME 和 ANY：同义词，表示表达式只要与子查询结果集中的某个值满足比较关系时，就返回 TRUE ，否则返回 FALSE
  • <> ALL 等同 'NOT IN'；
  • = SOME 等价 'IN'；

4.3、结合关键词 EXIST 使用的子查询

EXIST 主要用于判定子查询的结果集是否为空；

语法格式：EXIST(subquery)

如果子查询的结果集不为空，则返回 TRUE，否则返回 FALSE

五、GROUP BY子句与分组数据

功能：将结果集中的数据行根据选择列的值进行逻辑分组 ，以便能汇总表内容的子集，即实现对每个组的聚集计算。即根据列名将相同的数据聚合起来；

语法：

GROUP BY
{col_name|expr|position} [ASC|DESC],…[WITH ROLLUP]

说明：

  • col_name：指定用于分组的选择列，多个列间用逗号分隔；
  • expr：指定用于分组的表达式。
  • position：指定用于分组的选择列在SELECT语句结果集中的位置，通常是一个正整数。
  • ASC|DESC：升序 和 降序，默认ASC。
  • WITH ROLLUP：用于指定在结果集中不仅包含由 GROUP BY 子句分组后的数据行，还包括各分组的汇总行，以及所有分组的整体汇总行。是否展示那些汇总行(NULL列）；

注意点：

  • GROUP BY 子句可以包含任意数目的列，使得其可对分组进行嵌套；

  • 如果在 GROUP BY 子句中嵌套了分组，那么将按 GROUP BY子句中列的排列顺序的逆序方式依次汇总，并在最后规定的分组上进行一个完全汇总；(ps.见下面例子中WITH ROLLUP中的，先汇总性别为NULL，再汇总地址为NULL)

  • GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式，但不能是聚合函数；

  • 除聚合函数之外，SELECT语句中的每个列都必须在GROUP BY子句中给出；聚合函数中，不忽略空值的是COUNT；

  • 如果用于分组的列中有 NULL 值，则NULL将作为一个单独的分组返回；如果存在多个 NULL 值，则将这些NULL值所在的行分为一组。

举例：在数据库 mysql_test 的表 customers 中获取一个数据结果集，要求该结果集中分别包含每个相同地址的男性客户人数和女性客户人数。即找出相同住址，相同性别的人；

SELECT cust_address, cust_sex, count(*)  AS 人数
FROM mysql_test.customers
GROUP BY cust_address, cust_sex;

如果上面 sql 语句的结果是

grouo by 1

那么，加上 WITH ROLLUP就是，注意这里就是先汇总 cust_sex 列为 NULL，在汇总 cust_address 为 NULL；

grouo by 2

六、HAVING 子句

可以使用 HAVING 子句来过滤分组，即在结果集中规定包含哪些分组和排除哪些分组。必须结合GROUB BY子句使用；

语法：HAVING where_condition

举例：在数据库 mysql_test 的表 customers 中查找这样一类客户信息：要求在返回的结果集中，列出相同客户地址中满足客户人数少于 3 的所有客户姓名及其对应地址；

SELECT cust_name, cust_address
FROM mysql_test.customers
GROUP BY cust_name, cust_address
HAVING count(*) < 3;

除聚合函数之外，SELECT 语句中的每个列都必须在 GROUP BY 子句中给出；

HAVING 子句和 WHERE 子句的区别：

  • HAVING子句过滤分组，WHERE子句过滤数据行；
  • HAVING子句包含聚合函数，WHERE子句不可以包含聚合函数；
  • HAVING子句在数据分组后过滤，WHERE子句在数据分组前过滤；

七、ORDER BY 子句

作用：可以使用 ORDER BY 子句将结果集中的数据行按一定的顺序进行排列。

语法：ORDER BY { col_name|expr|position } [ASC|DESC]

举例：在数据库 mysql_test 的表 customers 中依次按照客户姓名和地址的降序方式，输出客户的姓名和性别。

SELECT cust_name,cust_sex
FROM mysql_test.customers
ORDER BY cust_name,cust_address DESC;

注意点：

  • ORDER BY 子句中可以包含子查询。
  • 当对空值进行排序时 ORDER BY 子句会将该 空值作为最小值对待。
  • 若在 ORDER BY子句中指定多个列进行排序，则在 MySQL 中会按照这些列从左到右所罗列的次序依次进行排序。
  • 在使用GROUP BY子句时，通常也会同时使用 ORDER BY 子句。

ORDER BY 子句和 GROUP BY 子句的区别：

  • ORDER BY排序产生输出，GROUP BY分组行，但输出可能不是分组的排序；
  • ORDER BY任意列都可以使用，GROUP BY只可以使用 SELECT 后面指定的列或表达式列；
  • 在使用聚合函数时则必须使用GROUP BY，ORDER BY 则没有这样的限制；

八、LIMIT 子句

作用：为了便于用户对结果数据的浏览和操作，可以使用 LIMIT 子句来限制被 SELECT 语句返回的行数。

语法：LIMIT {[offset,] row_count|row_count OFFSET offset}

举例：在数据库 mysql_test 的表 customers 中查找从第5位客户开始的3位客户的证号和姓名信息。

SELECT cust_id,cust_name
FROM mysql_test.customers
ORDER BY cust_id
LIMIT 4,3;

也可以写成是 LIMIT 3 OFFSET 4，从0开始计数，所以 OFFSET 是 4，LIMIT 长度是 3；