MySQL查询数据(六)

Published on in MySQL with 0 views and 0 comments

查询数据

基本查询语句

  • MySQL从数据表中查询数据的基本语句为SELECT语句。SELECT语句的基本格式是:

    SELECT
    	{* | <字段列表>}
    	[
            FROM <表1>,<表2>
            [
                WHERE <表达式>
                [ GROUP BY <group by definition> ]
                [ HAVING <expression> [{<operator> <expression>}...] ]
                [ ORDER BY <order by definition> ]
                [ LIMIT [offset,] <row count> 
            ]
        ];
    

    其中,各条子句的含义如下:

    • {*|<字段列表>} 包含星号通配符选字段列表,表示查询的字段,其中字段列至少包含一个字段名称,如果要查询多个字段,多个字段之间用逗号隔开,最后一个字段后不用加逗号。
    • FROM <表1>,<表2>...,表1和表2表示查询数据的来源,可以是单个或者多个
    • WHERE 子句是可选项,如果选择该项,就将限定查询行必须满足的查询条件
    • GROUP BY <字段>,该子句告诉MySQL如何显示查询出来的数据,并按照指定的字段分组
    • [ORDER BY <字段>],该子句告诉MySQL按什么样的顺序显示查询出来的数据,可以进行的排序有升序ASC)和降序DESC)。
    • [LIMIT [ < offset > ,] < row count >],该子句告诉MySQL每次显示查询出来的数据条数

单表查询

  • 单表查询是指从一张表数据中查询所需的数据。主要有查询所有字段、查询指定字段、查询指定记录、查询空值多条件的查询、对查询结果进行排序等。

查询所有字段

  1. 在SELECT语句中使用星号(*)通配符查询所有字段

    SELECT查询记录最简单的形式是从一个表中检索所有记录,实现的方法是使用星号(*)通配符指定查找所有列的名称。语法格式如下:

    SELECT * FROM 表名;
    
  2. 在SELECT语句中指定所有字段

    SELECT关键字后面的字段名为将要查找的数据,因此可以将表中所有字段的名称跟在SELECT子句后面,如果忘记了字段名称,可以使用DESC命令查看表的结构。

查询指定字段

  1. 查询单个字段

    查询表中的某一个字段,语法格式为:

    SELECT 列名 FROM 表名;
    
  2. 查询多个字段

    使用SELECT声明,可以获取多个字段下的数据,只需要在关键字SELECT后面指定要查找的字段的名称,不同字段名称之间用逗号(,)分隔开,最后一个字段后面不需要加逗号,语法格式如下:

    SELECT 字段名1,字段名2,...,字段名n FROM 表名;
    

查询指定记录

  • 数据库中包含大量的数据,根据特殊要求,可能只需要查询表中的指定数据,即对数据进行过滤。在SELECT语句中,通过WHERE子句可以对数据进行过滤,语法格式为:

    SELECT 字段名1,字段名2,...,字段名n
    FROM 表名
    WHERE 查询条件
    

    WHERE子句中,MySQL提供了一系列的条件判断符,如下表所示。

    操作符说明
    =相等
    <>,!=不相等
    <小于
    <=小于或者等于
    >大于
    >=大于或者等于
    BETWEEN AND位于两值之间

带IN关键字的查询

  • IN操作符用来查询满足指定范围内的条件的记录,使用IN操作符,将所有检索条件用括号括起来,检索条件之间用逗号分隔开,只要满足条件范围内的一个值即为匹配项。相反,可以使用关键字NOT来检索不在条件范围内的记录。

带BETWEEN AND的范围查询

  • BETWEEN AND用来查询某个范围内的值,该操作符需要两个参数,即范围的开始值结束值,如果字段值满足指定的范围查询条件,则这些记录被返回。BETWEEN AND操作符前可以加关键字NOT,表示指定范围之外的值。

带LIKE的字符匹配查询

  • 通配符是一种在SQLWHERE条件子句中拥有特殊意思的字符,SQL语句中支持多种通配符,可以和LIKE一起使用的通配符有 ‘%’ 和 ‘_’ 。
  1. 百分号通配符 ‘%’ ,匹配任意长度的字符,甚至包括零字符

    在搜索匹配时,通配符 ‘%’ 可以放在不同位置,例如 ’b%y‘ 可以查询以b开头、以y结尾的数据。

  2. 下划线通配符 ‘_’ ,一次只能匹配任意一个字符

    下划线通配符 ’ _ ‘ 的用法和 ’%‘ 相同,区别是 ’%‘ 可以匹配多个字符,而 ’ _ ‘ 只能匹配任意单个字符,如果要匹配多个字符,则需要使用相同个数的 ‘ _ ’ 。

查询空值

  • 数据表创建的时候,设计者可以指定某列中是否可以包含空值NULL)。空值不同于0,也不同于空字符串。空值一般表示数据未知、不适用或将在以后添加数据。在SELECT语句中使用IS NULL子句,可以查询某字段内容为空记录。与IS NULL相反的是IS NOT NULL,该关键字查找字段不为空的记录

带AND的多条件查询

  • 使用SELECT查询时,可以增加查询的限制条件,这样可以使查询的结果更加精确。MySQLWHERE子句中使用AND操作符限定只有满足所有查询条件的记录才会被返回。可以使用AND连接两个甚至多个查询条件,多个表达式之间用AND分开。

带OR的多条件查询

  • AND相反,在WHERE声明中使用OR操作符,表达只需要满足其中一个条件的记录即可返回。OR也可以连接两个甚至多个查询条件,多个条件表达式之间用OR分开。
  • 提示:OR可以和AND一起使用,但是在使用时要注意两者的优先级,由于AND的优先级高于OR,因此先对AND两边的操作数进行操作,再与OR中的操作数结合。

查询结果不重复

  • 在SELECT语句中,可以使用DISTINCT关键字指示MySQL消除重复的记录值。语法格式为:

    SELECT DISTINCT 字段名 FROM 表名;
    

对查询结果排序

  • MySQL可以通过在SELECT语句中使用ORDER BY子句,对查询的结果进行排序
  1. 单列排序

    通过ORDER BY子句,MySQL可以对单一列的数据按照字母表的顺序进行排序。语法格式如下:

    SELECT 字段名1 [,字段名2,...字段名n] FROM 表名 ORDER BY 其中一个字段名;
    
  2. 多列查询

    对多列数据进行排序,必须将需要排序的列之间用逗号隔开。语法格式如下:

    SELECT 字段名1 [,字段名2,...字段名n] FROM 表名 ORDER BY 字段名a,字段名b,...字段名n;
    

    提示:在对多列进行排序的时候,首先排序的第一列必须有相同的列值,才会对第二列进行排序。如果第一列数据中所有值都是唯一的,将不再对第二列进行排序。

  3. 指定排序方向

    默认情况下,查询数据按字母升序进行排序(A ~ Z),但数据的排序并不仅限于此,还可以使用 ORDER BY 对查询结果进行降序排序(Z ~ A),这可以通过关键字DESC实现。语法格式如下:

    SELECT 字段名1 [,字段名2,...字段名n] FROM 表名 ORDER BY 字段名a DESC(ASC) [,字段名b,...字段名n];
    

    提示:与DESC相反的是ASC升序排序),将字段列中的数据按字母表顺序升序排序,实际上,在排序的时候ASC默认的排序方式,所以加不加都可以。也可以对多列进行不同的顺序排序。如果要对多列都进行降序排序,必须要在每一列的列名后面加DESC关键字。

分组查询

  • 分组查询是对数据按照某个或多个字段进行分组,MySQL中使用GROUP BY关键字对数据进行分组,基本语法格式为:

    [GROUP BY 字段] [HAVING <条件表达式>]
    

    字段值为进行分组时所依据的列名称;“HAVING <条件表达式>” 指定满足表达式限定条件的结果将被显示。

  1. 创建分组

    GROUP BY关键字通常和集合函数一起使用,比如MAX()MIN()COUNT()SUM()AVG(),可以在分组过程中使用COUNT(*) 函数,对每个组进行集合运算。可以使用GROUP_CONCAT() 函数,将每个分组中各个字段的值显示出来。

  2. 使用HAVING过滤分组

    GROUP BY可以HAVING一起限定显示记录所需满足的条件,只有满足条件的分组才会被显示。

    提示:HAVING关键字与WHERE关键字都是用来过滤数据的,两者有什么区别呢?其中最重要的一点是,HAVING在数据分组之后进行过滤来选择分组,而WHERE分组之前用来选择记录。另外,WHERE排除的记录不再包括在分组中。

  3. 在GROUP BY子句中使用WITH ROLLUP

    使用WITH ROLLUP关键字之后,在所有查询出的分组记录之后增加一条记录,该记录计算查询出的所有记录的总和,即统计记录数量

  4. 多字段分组

    使用GROUP BY可以对多个字段进行分组,GROUP BY关键字后面跟需要分组的字段,MySQL根据多字段的值来进行层次分组,分组层次从左到右,即先按第1个字段分组,然后在第1个字段值相同的记录中,再根据第2个字段的值进行分组。

  5. GROUP BY和ORDER BY一起使用

    某些情况下需要对分组进行排序,ORDER BY用来对查询的记录排序,如果和GROUP BY一起使用可以完成对分组进行排序。

    提示:当使用ROLLUP时,不能同时使用ORDER BY子句进行结果排序,即ROLLUPORDER BY互相排斥的。

使用LIMIT限制查询结果的数量

  • SELECT返回所有匹配的行,有可能是表中所有的行,如仅仅需要返回第一行或者前几行,使用LIMIT关键字,基本语法格式如下:

    LIMIT [位置偏移量,] 行数
    

    第一个 “位置偏移量” 参数指示MySQL从哪一行开始显示,是一个可选参数,如果不指定 “位置偏移量” ,将会从表中的第一条记录开始(第一条记录的位置偏移量是0,第二天记录的位置偏移量是1,依次类推);第二个参数 “行数” 指示返回的记录条数

提示:MySQL 5.7中可以使用 “LIMIT 4 OFFSET 3” ,意思是获取从第5条记录开始后面的3条记录,和 “LIMIT 4,3” 返回的结果相同。

使用集合函数查询

  • 有时候并不需要返回实际表中的数据,而只是对数据进行总结,MySQL提供了一些查询功能,可以对获取的数据进行分析和报告。这些函数的功能有计算数据表中记录行数的总数。这些集合函数的名称和作用如下表所示。

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

COUNT()函数

  • COUNT() 函数统计数据表中包含的记录行的总数,或者根据查询结果返回列中包含的数据行数。其使用方法有两种:
    • COUNT(*) 计算中总的函数,不管某列有数值或者为空值
    • COUNT(字段名) 计算指定列下总的行数,计算时将忽略空值的行。

SUM()函数

  • SUM() 是一个求总和的函数,返回指定列值的总和
  • 提示:SUM() 函数在计算时,忽略列值为NULL的行。

AVG()函数

  • AVG() 函数通过计算返回的行数和每一行数据的和,求得指定列数据的平均值
  • 提示:AVG函数使用时,其参数为要计算的列名称,如果要得到多个列的多个平均值,则需要在每一列上使用AVG() 函数。

MAX()函数

  • MAX() 返回指定列中的最大值
  • MAX() 函数不仅适用于查找数值类型,也可应用于字符类型根据ASCII码值大小进行比较)。

MIN()函数

  • MIN() 返回查询列中的最小值。

连接查询

  • 连接关系数据库模型的主要特点连接查询关系数据库最主要的查询,主要包括内连接外连接等。通过连接运算符可以实现多个表查询。

内连接查询

  • 内连接INNER JOIN)使用比较运算符进行表间某(某些)列数据的比较操作,并列出这些表中与连接条件相匹配的数据行,组合成新纪录,也就是说,在内连接查询中,只有满足条件的记录才能出现在结果关系中。

    1. WHERE子句进行内连接查询,语法格式如下:

      SELECT 字段名1 [,字段名2,...字段名n]
      FROM 表名1,表名2
      WHERE 连接条件; --(表名1.var1 = 表名2.var1)
      
    2. 用INNER JOIN语法进行内连接查询,语法格式如下:

      SELECT 字段名1 [,字段名2,...字段名n]
      FROM 表名1 INNER JOIN 表名2
      ON 连接条件;
      

      在这里的查询语句中,两个表之间的关系通过INNER JOIN指定。使用这种语法时,连接的条件使用ON子句给出而不是WHEREONWHERE后面指定的条件相同。

  • 提示:使用WHERE子句定义连接条件比较简单明了,而INNER JOIN语法是ANSI SQL的标准规范,使用INNER JOIN连接语法能够确保不会忘记连接条件,而且,WHERE子句在某些时候会影响查询的性能。

  • 如果在一个连接查询中涉及的两个表都是同一个表,那么这种查询称为自连接查询。自连接是一种特殊的内连接,是指相互连接的表在物理上为同一张表,但可以在逻辑上分为两张表。语法格式如下:

    SELECT 字段名1 [,字段名2,...字段名n]
    FROM 表名1 AS b1,表名1 AS b2
    WHERE 连接条件;
    

外连接

  • 外连接查询将查询多个表中相关联的行,内连接时,返回查询结果集合中的仅是扶额查询条件和连接条件的行。但有时候需要包含没有关联的行中数据,即返回查询结果集合中的不仅包含符合连接条件的行,而且还包括左表左外连接或左连接)、右表右外连接或右连接)或两个边接表全外连接)中所有数据行。外连接分为左外连接或左连接右外连接或右连接
    • LEFT JOIN左连接):返回包括左表中的所有记录和右表中连接字段相等的记录。
    • RIGHT JOIN右连接):返回包括右表中的所有记录和左表中连接字段相等的记录。
  1. LEFT JOIN 左连接

    左连接的结果包括LEFT OUTER子句中指定的左表的所有行,而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果行中,右表的所有选择列表列均为空值。语法格式如下:

    SELECT 字段名1 [,字段名2,...字段名n]
    FROM 表名1 LEFT OUTER JOIN 表名2
    ON 连接条件;
    
  2. RIGHT JOIN 右连接

    右连接是左连接的反向连接,将返回右表的所有行,如果右表的某行在左表中没有匹配行左表将返回空值

    SELECT 字段名1 [,字段名2,...字段名n]
    FROM 表名1 RIGHT OUTER JOIN 表名2
    ON 连接条件;
    

复合条件连接查询

  • 复合条件连接查询是在连接查询的过程中,通过添加过滤条件限制查询的结果,使查询的结果更加准确。

子查询

  • 子查询是指一个查询语句嵌套在另一个查询语句内部的查询。在SELECT子句中先计算子查询,子查询结果作为外层另一个查询的过滤条件,查询可以基于一个表或者多个表。子查询中常用的操作符有ANYSOME)、ALLINEXISTS。子查询可以添加到SELECTUPDATEDELETE语句中,而且可以进行多层嵌套。子查询中也可以使用比较运算符,如 “<” “<=” “>” “>=”“!=” 等。

带ANY、SOME关键字的子查询

  • ANYSOME关键字是同义词,表示满足其中任一条件,它们允许创建一个表达式对子查询的返回值列表进行比较,只要满足内层子查询中的任何一个比较条件,就返回一个结果作为外层查询的条件。

  • ANY关键字接在一个比较操作符的后面,表示若与子查询返回的任何值比较为true,则返回true

    SELECT 字段名1 FROM 表名1 WHERE 字段名1 > ANY (SELECT 字段名2 FROM 表名2);
    

带ALL关键字的子查询

  • ALL关键字与ANYSOME不同,使用ALL时需要同时满足所有内层查询的条件。
  • ALL关键字接在一个比较操作符的后面,表示与子查询返回的所有值比较为true,则返回true

带EXISTS关键字的子查询

  • EXISTS关键字后面的参数是一个任意的子查询,系统对子查询进行运算以判断它是否返回行,如果至少返回一行,那么EXISTS的结果为true,此时外层查询语句将进行查询;如果子查询没有返回任何行,那么EXISTS返回的结果是false,此时外层语句将不进行查询。

    SELECT 字段名1 FROM 表名1 WHERE EXISTS (SELECT 字段名2 FROM 表名2 WHERE 查询条件);
    
  • NOT EXISTSEXISTS使用方法相同,返回的结果相反。子查询如果至少返回一行,那么NOT EXISTS的结果为false,此时外层查询语句将不进行查询;如果子查询没有返回任何行,那么NOT EXISTS返回的结果为true,此时外层语句将进行查询。

  • 提示:EXISTSNOT EXISTS的结果只取决于是否会返回行,而不取决于这些行的内容,所以这个子查询输入列表通常是无关紧要的。

带IN关键字的子查询

  • IN关键字进行子查询时,内层查询语句仅仅返回一个数据列,这个数据列里的值将提供给外层查询语句进行比较操作。通常用于一个表的查询需要另一个表的字段去匹配查询条件。

    SELECT 字段名1 FROM 表名1 WHERE 字段名2 IN (SELECT 字段名2 FROM 表名2 WHERE 查询条件);
    
  • SELECT语句中可以使用NOT IN关键字,其作用与IN正好相反。

带比较运算符的子查询

  • 子查询时还可以使用其他的比较运算符,如 “<” “<=” “>” “>=”“!=” 等。

    SELECT 字段名1 FROM 表名1 WHERE 字段名1 = (SELECT 字段名2 FROM 表名2 WHERE 查询条件);
    

合并查询结果

  • 利用UNION关键字,可以给出多条SELECT语句,并将它们的结果组合成单个结果集。合并时,两个表对应的列数数据类型必须相同。各个SELECT语句之间使用UNIONUNION ALL关键字分隔。UNION不使用关键字ALL,执行的时候删除重复的记录,所有返回的行都是唯一的;使用关键字ALL的作用是不删除重复行也不对结果进行自动排序。基本语法格式如下:

    SELECT column,... FROM table1
    UNION [ALL]
    SELECT column,... FROM table2;
    
  • UNION ALL关键字执行时所需要的资源少,所以尽可能地使用它,确定查询结果中不会有重复数据或者不需要去掉重复数据的时候,应当使用UNION ALL以提高查询效率。

为表和字段取别名

为表取别名

  • 当表名字很长或者执行一些特殊查询时,为了方便操作或者需要多次使用相同的表时,可以为表指定别名,用这个别名替代表原来的名称。为表取别名的基本语法格式为:

    表名 [AS] 表别名
    

    表名” 为数据库中存储的数据表的名称,“表别名” 为查询指定的表的新名称AS关键字为可选参数。

  • MySQL可以同时为多个表取别名,而且表别名可以放在不同的位置,如WHERE子句、SELECT列表、ON子句以及ORDER BY子句等。

  • 提示:在为表取别名时,要保证不能与数据库中的其他表的名称冲突。

为字段取别名

  • 在使用SELECT语句显示查询结果时,MySQL会显示每个SELECT后面指定的输出列,在有些情况下,显示的列的名称会很长或者名称不够直观,MySQL可以指定列别名、替换字段或表达式。为字段取别名的基本语法格式为:

    列名 [AS] 列别名
    

    列名” 为表中字段定义的名称,“列别名” 为字段新的名称,AS关键字为可选参数。

  • 也可以为SELECT子句中的计算字段取别名,例如,对使用COUNT聚合函数或者CONCAT等系统函数执行的结果字段取别名

  • 表别名只在执行查询的时候使用,并不在返回结果中显示,而列别名定义之后,将返回给客户端显示,显示的结果字段为字段列的别名。

使用正则表达式查询

  • 正则表达式通常用来检索或替换那些符合某个模式的文本内容,根据指定的匹配模式匹配文本中符合要求的特殊字符串。MySQL中使用REGEXP关键字指定正则表达式的字符匹配模式。下面列出了REGEXP操作符中常用字符匹配列表。

    选项说明例子匹配值示例
    ^匹配文本的开始字符‘^b’ 匹配以字母b开头的字符串book,big,banana,bike
    $匹配文本的结束字符‘st$’ 匹配以st结尾的字符串test,resist,persist
    .匹配任何单个字符‘b.t’ 匹配任何b和t之间有一个字符的字符串bit,bat,but,bite
    *匹配零个或多个在它前面的字符‘f*n’ 匹配字符n前面有任意个字符ffn,fan,faan,fabcn
    +匹配前面的字符1次或多次‘ba+’ 匹配以b开头后面紧跟至少有一个aba,bay,bare,battle
    <字符串>匹配包含指定的字符串的文本‘fa’fan,afa,faad
    [字符集合]匹配字符集合中的任何一个字符‘[xz]’ 匹配x或者zdizzy,zebra,x-ray,extra
    [^]匹配不在括号内的任何字符‘[ ^abc]’ 匹配任何不包含a、b或c的字符串desk,fox,f8ke
    字符串{n,}匹配前面的字符串至少n次b{2} 匹配2个或更多的bbbb,bbbb,bbbbb
    字符串{n,m}匹配前面的字符串至少n次、至多m次。如果n为0,此参数为可选参数b{2,4} 匹配最少2个、最多4个bbb,bbb,bbbb

查询以特定字符或字符串开头的记录

  • 字符 ‘^’ 匹配以特定字符或者字符串开头的文本。

查询以特定字符或字符串结尾的记录

  • 字符 ‘$’ 匹配以特定字符或者字符串结尾的文本。

用符号 “.” 来替代字符串中的任意一个字符

  • 字符 ‘.’ 匹配任意一个字符。例如 ‘a.g’ 匹配包含 ‘a’ 与 ‘g’ 且两个字母之间只有一个字母的记录。

使用 “*” 和 “+” 来匹配多个字符

  • 星号 ‘*‘ 匹配前面的字符任意多次,包括0次。加号 ’+’ 匹配前面的字符至少一次

匹配指定字符串

  • 正则表达式可以匹配指定字符串,只要这个字符串在查询文本中即可,如果要匹配多个字符串,那么多个字符串之间要使用分隔符 ‘|‘ 隔开。例如 ’on|ap' 查询包含on或者ap的字符串。

匹配指定字符串中的任意一个

  • 方括号 ”[]" 指定一个字符集合,只匹配其中任何一个字符,即为所查找的文本。另外,方括号 ”[]“ 还可以指定数值集合。匹配集合 ”[456]“ 也可以写成 ”[4-6]“ 即指定集合区间。例如,”[a-z]“ 表示集合区间为a~z的字母, ”[0-9]“ 表示集合区间为所有数字。

匹配指定字符以外的字符

  • ”[ ^字符集合]“ 匹配不在指定集合中的任何字符。例如 '[ ^a-e1-2]' 匹配包含字母a ~ e和数字1 ~ 2以外的字符的记录。只要包含一个不在范围里的字符即满足条件。

使用 {n,}或者 {n,m}来指导字符串连续出现的次数

”字符串{n,}“ 表示至少匹配n次前面的字符;”字符串{n,m}“ 表示匹配前面的字符串不少于n次,不多于m次。


标题:MySQL查询数据(六)
作者:wangdj
地址:https://www.dblog.cool/articles/2024/11/10/1731170324725.html