当前位置:   article > 正文

mysql笔记:9. 数据查询_mysql 数据查询

mysql 数据查询


数据库管理系统的一个最重要功能就是数据查询,数据查询不应只是简单返回数据库中存储的数据,还应该根据需要对数据进行筛选,以及确定数据以什么样的格式显示。MySQL提供了功能强大、灵活的语句来实现这些操作。

一、SELECT语句

从数据库表中查询数据的基本语句为SELECT语句,语法如下:

SELECT 属性列表
FROM 表名和视图
{WHERE 条件表达式}
{GROUP BY 属性名}
{HAVING 条件表达式}
{ORDER BY 属性名 ASC | DESC}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

所有被使用的子句必须按语法说明中显示的顺序严格地排序。

二、简单查询

1. 查询表所有数据

  1. 列出表中所有字段
    在SELECT语句的属性列表中列出查询表的所有字段,从而可以查询表中所有数据。
  2. 使用星号通配符
    SELECT语句的属性列表可以为星号*,语法格式:
SELECT * FROM table_name;
SELECT * FROM table_name\G;
  • 1
  • 2

在执行SQL语句时,有时返回的数据中字段非常多,无法在CMD窗口的一行全部展示,而如果将字段名称显示在多行,会导致字段下的数据不能和字段名称展示在同一列,显示结果非常混乱。
使用结束符\G,可以将结果以纵向结构显示,在字段非常多时,能让显示结果更加整齐美观。

2. 查询部分数据

在SELECT语句的属性列表中指定要查找的字段名称,可以有针对性的查询结果。

MySQL中的SQL语句是不区分大小写的。因此,SELECT和select是相同的。很多书籍推荐将关键字大写,表名和数据列使用小写。
我认为,应该全部使用小写,并且我在平时的工作中也是这么做的。理由如下:
一、使用大写+小写需要切换输入法,降低开发速度,所有全部小写可以更快的编写SQL语句。
二、时间相同的情况下,单词小写的识别正确率高于大写的正确率,所以小写的SQL语句更容易识别和维护。

3. 计算结果

在查询时,可以根据需要使用算术运算符或逻辑运算符对查询结果进行处理。
例如,表emp中有员工姓名name和当前工资salary,可以查询员工工资和调薪20%以后的工资。

SELECT name, salary, salary + salary*0.2 AS salary_new FROM emp;
  • 1

4. 列别名

显示查询结果时,选择的列通常是以原表中的列名作为标题。但列名通常比较简短、甚至在一些情况下含义也模糊。为改善查询结果的显示,可以在SELECT语句的列名后使用AS 新列名重新命令该列。

AS:关键字。可有可无。

5. 去除重复项

使用DISTINCT选项可以在查询结果中删除重复项。

6. 表别名

如果要查询的数据表名称较长时使用不太方便,可以为表取一个别名来代替,简化查询SQL。

7. LIMIT限制数据

当数据表中包含大量数据时,可以通过指定显示记录数限制返回结果集中的行数。
LIMIT用法有两种:

  1. 指定初始位置
    LIMIT offset, row_count
    offset指定返回的第一行的偏移量。初始行的偏移量是0,不是1。
    row_count指定返回的行数的最大值。
  2. 不指定初始位置
    LIMIT row_count

三、WHERE子句

WHERE子句用于给定源表和视图中记录的筛选条件,只能符合筛选条件的记录才能入选结果集。

1. 比较查询条件

在查询条件中使用比较运算符进行比较。

2. BETWEEN AND范围查询

使用BETWEEN AND可以进行范围查询,可以指定范围的开始值和结束值。如果记录的字段值满足指定的范围,则记录被返回。
相反,可以在BETWEEN AND前加关键字NOT,表示指定范围之外的值。

BETWEEN匹配范围中的所有值,包括开始值和结束值。

3. IN查询

使用IN查询时,将所有检索条件放进括号里,不同条件用逗号分隔,只要满足条件范围内的任意一个值即为匹配项。
相反,可以使用关键字NOT检索不在条件范围内的记录。

4. LIKE匹配

使用LIKE可以匹配字符串是否相等。

[NOT] LIKE '字符串'
  • 1

字符串参数可以是一个完整的字符串,也可以是包含%或_的通配符。

  • %:匹配任意长度的字符串,可以是空字符串。
  • _:匹配任意单个字符。

5. 空数据查询

数据表中的某些列可能包含空值,它不同于0,也不同于空字符串。查询时可以使用IS NULL子句。
相反,可以使用IS NOT NULL查询不为空的记录。

6. AND多条件查询

AND关键字可以用来联合多个条件进行查询,只能同时满足所有条件的记录才会被查询出来。

7. OR多条件查询

OR关键字也可以用来联合多个条件进行查询,只要满足任意一个条件,记录就会被查询出来。

四、操作查询结果

1. 排序

使用ORDER BY可以对记录进行排序。

ORDER BY column_name [ASC | DESC]
  • 1

默认情况下,按照ASC方式进行排序。
如果要排序的字段值是空值时,按升序排序时,含空值的记录将最先显示,可以理解为空值是该字段的最小值;而按降序排序时,字段为空值的记录将最后显示。

2. 分组

分组查询是对数据按照某个或多个字段进行分组。基本语法:

[GROUP BY column_name] [HAVING <条件表达式>]
  • 1
  • HAVING子句指定分组显示时需要满足的限定条件
  • GROUP BY子句通常和集合函数一起使用。

五、聚合函数统计

有时候并不需要返回实际表中的数据,只是对数据进行统计。

函数作用
AVG()返回某列的平均值
COUNT()返回某列的行数
MAX()返回某列的最大值
MIN()返回某列的最小值
SUM()返回某列值的和

COUNT()函数统计查询结果中包含的记录行的总数。其使用有三种:

  • COUNT(1):统计表的所有记录数,包含字段为NULL的记录
  • COUNT(*):统计表的所有记录数,无论某列是否为空值
  • COUNT(column_name):计算指定列的总行数,忽略空值的行

一般情况下,COUNT(1)的执行效率要优于COUNT(*)。因为COUNT(1)不需要遍历整个表,因为它只需要统计满足条件的行数,而COUNT(*)要扫描整个表。
另外,MyISAM引擎不支持事务,它的锁是表级锁,在该引擎内使用COUNT(*)更快。而InnoDB支持事务,支持行级锁,使用COUNT(1)更快。

六、多表嵌套

多表嵌套查询又称为子查询,在SELECT子句中先计算子查询,其结果作为外层另一个查询的过滤条件,查询可以基于一个表或者多个表。

1. 比较运算符

嵌套查询中可以使用的比较运算符有<、<=、=、>=、!=等。

2. IN

使用IN关键字嵌套查询时,内层查询语句仅仅返回一个数据列。这个列里的值将提供给外层查询语句进行比较。

3. ANY

通常使用比较运算符来连接ANY得到的结果,用于比较某一列的值是否全部大于 ANY后面子查询中的最小值或小于ANY后面嵌套查询中的最大值。

4. ALL

使用ALL关键字时需要同时满足所有内层查询条件。

5. SOME

SOME的用法与ANY类似,但意义不同。SOME通常用于比较满足查询结果中的任意一个值,而ANY要满足所有值才可以。
在SOME前面使用=时,等价于IN的用途。

6. EXISTS

EXISTS关键字是存在的意思,应用于嵌套查询中,只要嵌套查询返回的结果为空,返回结果就是TRUE,此时外层查询语句将进行查询;否则就是返回FALSE,外层语句将不进行查询。
NOT EXISTS与EXISTS使用方法相同,但结果相反。

EXIST和NOT EXIST的结果只取决于是否会返回行,而不取决于这些行的内容,所以子查询输入列表通常是无关紧要的。

七、多表内连接

连接是关系数据库模型的主要特点,连接查询是关系数据库中最主要的查询,主要包括内连接、外连接等。
语法格式如下:

SELECT column_name1, column_name2, ...
FROM table_name1 INNER JOIN table_name2
ON conditions;
  • 1
  • 2
  • 3

八、多表外连接

几乎所有的查询语句,查询结果全部都是需要符合条件才能查询出来的。换句话说,如果执行查询语句后没有符合条件的结果,那么在结果中就不会有任何记录。外连接查询则与之相反,通过外连接查询,可以在查询出符合条件的结果后显示出某张表中不符合条件的数据。

SELECT column_name1, column_name2, ...
FROM table_name1 LEFT|RIGHT|FULL OUTER JOIN table_name2
ON conditions;
  • 1
  • 2
  • 3
  • LEFT OUTER JOIN(左连接):左外连接,使用左外连接时得到的查询结果中,除了符合条件的查询结果外,还要加上左表中余下的数据。
  • RIGHT OUTER JOIN(右连接):右外连接,使用右外连接时得到的查询结果中,除了符合条件的查询结果外,还要加上右表中余下的数据。
  • FULL OUTER JOIN(全连接):全外连接,使用全外连接时得到的查询结果中,除了符合条件的查询结果外,还要加上左表和右表中余下的数据。

九、排序函数

在MySQL中,可以对返回的查询结果排序,排序函数提供了一种按升序的方式组织输出结果集。

1. ROW_NUMBER()

ROW_NUMBER()函数为每条记录增添递增的顺序数值序号,即便存在相同的值也是递增序号。
基本语法:

ROW_NUMBER() OVER (
    [PARTITION BY <expression_list>] 
    ORDER BY <expression_list>
)
  • 1
  • 2
  • 3
  • 4
  • PARTITION BY:可选项,用于将结果集分隔成不同的分区。每个分区都会从1重新开始编号。
  • ORDER BY:必选项,用于指定结果集的排序顺序。

2. RANK()

如果两个或多个行与一个排名关联,则每个关联行将得到相同的排名。RANK()函数并不总返回连续整数。

3. DENSE_RANK()

DENSE_RANK()函数为结果集或分区中的每一行分配排名,在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一。即相同的数据序号相同,接下来顺序递增。

DENSE_RANK() OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
) 
  • 1
  • 2
  • 3
  • 4

4. NTILE()

NTILE(N)函数用来将查询结果中的记录分为N组。各个组有编号,编号从1开始。对于每一行,NTILE()函数将返回此行所属的组的编号。

十、正则表达式

正则表达式是一种文本模式,包括普通字符和特殊字符(称为元字符)。正则表达式的查询能力比普通字符的查询能力更强大,而且更加灵活,因此可以应用于非常复杂的数据查询。
语法如下:

属性名 REGEXP '匹配方式'
  • 1

匹配方式 参数中有很多模式匹配字符,它们分别表示不同的意思。

字符描述
^匹配字符串开始的位置
$匹配字符串结束的位置
.匹配字符串中的任意一个字符,包括回车和换行
[集合]匹配集合中的任何一个字符
[^集合]匹配除了集合以外的任意一个字符
*代表多个该符号之前的字符,包括0和1个
+代表多个该符号之前的字符,包括1个
字符串{n}字符串出现n次
字符串{m, n}字符串至少出现m次,最多n次

十一、补充:怎样复制表

实际工作中,我们偶尔会遇到复制一个表及其数据的情况,应该怎样操作呢?

1. show create table

mysql> SHOW CREATE TABLE table_name;
  • 1

使用show create table语句可以查看一个表的建表语句。复制出来,并修改其中的表名即得到新表的建表语句。

该方式只能复制表结构,不能同步表数据。

# 两个表完全一样的情况下,同步表table_name1的数据到table_name2
mysql> INSERT INTO table_name2 SELECT * FROM table_name1;
  • 1
  • 2

2. create table like

mysql> CREATE TABLE new_table_name LIKE table_name;
  • 1

使用create table like也可以拷贝旧表结构,同样也不能同步数据。

如需要同步表数据,使用insert into select语句执行一次即可。

3. create table select

mysql> CREATE TABLE new_table_name SELECT * FROM table_name;
  • 1

该语句创建了一个字段和原表一样的新表,并且将数据页同步了过来。但是这种方式会丢失原表的自增、主键、索引等信息,需要额外处理。

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/煮酒与君饮/article/detail/754284
推荐阅读
相关标签
  

闽ICP备14008679号