MySQL 系列教程之（七）DQL：从 select 开始丨【绽放吧！数据库】

从SELECT开始

• 检索单个列

  select name from user

  如果没有明确排序查询结果（下一章介绍），则返回的数据的顺序没有特殊意义。

  返回数据的顺序可能是数据被添加到表中的顺序，也可能不是。只要返回相同数目的行，就是正常的

• 检索多个列

  select id,name,age,sex from user

  在选择多个列时，一定要在列名之间加上逗号，但最后一个列名后不加。

  如果在最后一个列名后加了逗号，将出现错误。

• 检索所有列

  select * from user

  使用通配符 一般，除非你确实需要表中的每个列，否则最好别使用*通配符。

  虽然使用通配符可能会使你自己省事，不用明确列出所需列，但检索不需要的列通常会降低检索和应用程序的性能。

  使用通配符有一个大优点。由于不明确指定列名（因为星号检索每个列），所以能检索出名字未知的列。

• 检索不同的行 DISTINCT

  select distinct classid from user

  DISTINCT关键字，顾名思义，此关键字指示MySQL只返回不同的值

  DISTINCT关键字应用于所有列而不仅是前置它的列。

  如果给出SELECT DISTINCT vend_id, prod_price，除非指定的两个列都不同，否则所有行都将被检索出来

• 限制结果 LIMIT

  select * from user limit 3,4

  LIMIT 3, 4的含义是从行3开始的4行.(跳过前3行,取4行)

  替代语法 LIMIT 4 OFFSET 3意为从行3开始取4行，就像LIMIT 3, 4一样。

• 使用完全限定的表名

  select name from user

  select user.name from user

  select user.name from itxdl.user

• 排序数据 ORDER BY

  select * from user order by age

  默认查询出的数据,并不是随机排序的,如果没有指定排序,数据一般将以它在底层表中出现的顺序显示

  关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有意义

  通常，ORDER BY子句中使用的列将是为显示所选择的列。

  但是，实际上并不一定要这样，用非检索的列排序数据是完全合法的。

• 按多个列排序

  select * from user order by classid,age

  在需要对多列数据进行排序时,使用逗号分隔列名,并会按照前后顺序依次对比排序

  order by的排序默认升序,可以使用DESC设置降序排列

  select * from user order by classid,age DESC

  以上语句就是先对classid进行升序排序,然后在结果中对age进行降序排序

  注意

  ORDER BY子句的位置 在给出ORDER BY子句时，应该保证它位于FROM子句之后。

  如果使用LIMIT，它必须位于ORDER BY之后。使用子句的次序不对将产生错误消息

• 过 滤 数 据 WHERE

  数据库表一般包含大量的数据，很少需要检索表中所有行。

  通常只会根据特定操作或报告的需要提取表数据的子集。

  只检索所需数据需要指定搜索条件（search criteria），搜索条件也称为过滤条件（filter
  condition）。
  在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤。

  select name from user where age = 22

  在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后，否则将会产生错误

• WHERE子句操作符

  操作符	说明
  =	等于
  <> !=	不等于
  <	小于
  <=	小于等于
  >	大于
  >=	大于等于
  BETWEEN … and…	指定两个值之间
  IS NULL	空值

• 组合WHERE子句

  MySQL允许给出多个WHERE子句。

  这些子句可以两种方式使用：以AND子句的方式或OR子句的方式使用。也称为逻辑操作符

  select name from user where age = 22 and sex = 'm'

  AND 用在WHERE子句中的关键字，用来指示检索满足所有给定条件的行

  select name from user where age = 22 or sex = 'm'

  OR 用来表示检索匹配任一给定条件的行

• AND与OR

  WHERE可包含任意数目的AND和OR操作符。允许两者结合以进行复杂和高级的过滤。

  但是，组合AND和OR带来了一个有趣的问题。

  例如:我需要在数据库中查询出18期或19期的学员,并且要求是女生

  select name from user where classid=18 or classid =19 and sex='m'

  以上语句不会按照预期检索出正确的数据,问题出在哪里?

  原因在于计算的次序。SQL在处理OR操作符前，优先处理AND操作符。

  当SQL看到上述WHERE子句时，它理解是 19期班级的所有女生,或者18期的所有学员,而不分性别。换句话说，由于AND在计算次序中优先级更高，操作符被错误地组合了

  因此想要解决就需要提升优先级,使用圆括号明确地分组相应的操作符

  select name from user where (classid=18 or classid =19) and sex='m'

  因为圆括号具有较AND或OR操作符高的计算次序，数据库首先过滤圆括号内的OR条件。

  这时，SQL语句变成了获取18期或19期的学员并且要求是女生

• IN与NOT

  IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。

  select name from user where classid in (18,19)

  IN WHERE子句中用来指定要匹配值的清单的关键字，功能与OR相当

  为什么要使用IN操作符？其优点具体如下。

  • 在使用长的合法选项清单时，IN操作符的语法更清楚且更直观。
  • 在使用IN时，计算的次序更容易管理（因为使用的操作符更少）。
  • IN操作符一般比OR操作符清单执行更快。
  • IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句。

  NOT WHERE子句中用来否定后跟条件的关键字

  select name from user where classid not in (18,19)

  为什么使用NOT？对于简单的WHERE子句，使用NOT确实没有什么优势。

  但在更复杂的子句中，NOT是非常有用的。

  例如，在与IN操作符联合使用时，NOT使找出与条件列表不匹配的行非常简单。

• LIKE与通配符

  前面介绍的所有操作符都是针对已知值进行过滤的。

  但是，这种过滤方法并不是任何时候都好用。

  例如，怎样搜索产品名中包含文本anvil的所有产品？用简单的比较操作符肯定不行，必须使用通配符。

  为在搜索子句中使用通配符，必须使用LIKE操作符。

  LIKE指示MySQL，后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。

  • 百分号（%）通配符 在搜索串中，%表示任何字符出现任意次数

    select name from user where name like 'a%'

    select name from user where name like '%a'

    select name from user where name like '%a%'

  • 下划线（_）通配符 下划线的用途与%一样，但下划线只匹配单个字符而不是多个字符

  • 使用通配符的技巧

    正如所见，MySQL的通配符很有用。

    但这种功能是有代价的：通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长。

    这里给出一些使用通配符要记住的技巧

    • 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
    • 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。
    • 把通配符置于搜索模式的开始处，搜索起来是最慢的。
    • 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据

  • 了解下MySQL的正则 REGEXP

    所有种类的程序设计语言、文本编辑器、操作系统等都支持正则表达式

    select name from user where name regexp '[0-5]abc'

• 字段的拼接,别名,计算

  存储在数据库表中的数据一般不是应用程序所需要的格式。下面举几个例子

  • 如果想在一个字段中既显示公司名，又显示公司的地址，但这两个信息一般包含在不同的表列中。
  • 城市、州和邮政编码存储在不同的列中（应该这样），但邮件标签打印程序却需要把它们作为一个恰当格式的字段检索出来。
  • 列数据是大小写混合的，但报表程序需要把所有数据按大写表示出来。
  • 物品订单表存储物品的价格和数量，但不需要存储每个物品的总价格（用价格乘以数量即可）。为打印发票，需要物品的总价格。
  • 需要根据表数据进行总数、平均数计算或其他计算

  计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT语句内创建的

  • 拼接 Concat

  vendors表包含供应商名和位置信息。假如要生成一个供应商报表，需要在供应商的名字中按照name(location)这样的格式列出供应商的位置

  解决办法是把两个列拼接起来。在MySQL的SELECT语句中，可使用Concat()函数来拼接两个列

  select concat(vend_name,'(',vend_country')') from vendors order by vend_name

  • 使用别名 AS

    SELECT语句拼接地址字段工作得很好。但此新计算列的名字是什么呢？实际上它没有名字，它只是一个值。

    如果仅在SQL查询工具中查看一下结果，这样没有什么不好。

    但是，一个未命名的列不能用于客户机应用中，因为客户机没有办法引用它。

    为了解决这个问题，SQL支持列别名。别名（alias）是一个字段或值的替换名。别名用AS关键字赋予

  select concat(vend_name,'(',vend_country')') as vend_title from vendors order by vend_name

  别名还有其他用途。常见的用途包括在实际的表列名包含不符合规定的字符（如空格）时重新命名它，在原来的名字含混或容易误解时扩充它，等等。

  • 计算

    在mysql中可以对列中的字进行计算,使用基本算术操作符,此外，圆括号可用来区分优先顺序。

    运算符	说明
    +	加
    -	减
    *	乘
    /	除

    SELECT提供了测试和试验函数与计算的一个很好的办法。

    虽然SELECT通常用来从表中检索数据，但可以省略FROM子句以便简单地访问和处理表达式。

    例如，SELECT 3*2;将返回6，SELECT Trim(‘abc’);将返回abc，而SELECT Now()利用Now()函数返回当前日期和时间。

    通过这些例子，可以明白如何根据需要使用SELECT进行试验

• 函数的使用

  与其他大多数计算机语言一样，SQL支持利用函数来处理数据。

  函数一般是在数据上执行的，它给数据的转换和处理提供了方便。

  • 文本处理函数

    函数	说明
    Left()	返回串左边的字符
    Length()	返回串的长度
    Locate()	找出串的一个子串
    Lower()	将串转换为小写
    LTrim()	去掉串左边的空格
    Right()	返回串右边的字符
    RTrim()	去掉串右边的空格
    SubString()	返回子串的字符
    Upper()	将串转换为大写
    Concat	拼接字符串

  • 日期和时间处理函数

    函数	说明
    AddTime()	增加一个时间（时、分等）
    CurDate()	返回当前日期
    CurTime()	返回当前时间
    Date()	返回日期时间的日期部分
    DateDiff()	计算两个日期之差
    Date_Add()	高度灵活的日期运算函数
    Date_Format()	返回一个格式化的日期或时间串
    Day()	返回一个日期的天数部分
    DayOfWeek()	对于一个日期，返回对应的星期几
    Hour()	返回一个时间的小时部分
    Minute()	返回一个时间的分钟部分
    Month()	返回一个日期的月份部分
    Now()	返回当前日期和时间
    Second()	返回一个时间的秒部分
    Time()	返回一个日期时间的时间部分
    Year()	返回一个日期的年份部分

  • 数值处理函数

    函 数	说 明
    Abs()	返回一个数的绝对值
    Cos()	返回一个角度的余弦
    Exp()	返回一个数的指数值
    Mod()	返回除操作的余数
    Pi()	返回圆周率
    Rand()	返回一个随机数
    Sin()	返回一个角度的正弦
    Sqrt()	返回一个数的平方根
    Tan()	返回一个角度的正切

• 聚集函数的使用

  我们经常需要汇总数据而不用把它们实际检索出来，为此MySQL提供了专门的函数。

  使用这些函数，MySQL查询可用于检索数据，以便分析和报表生成

  • 确定表中行数（或者满足某个条件或包含某个特定值的行数）。
  • 获得表中行组的和。
  • 找出表列（或所有行或某些特定的行）的最大值、最小值和平均值。

  上述例子都需要对表中数据（而不是实际数据本身）汇总。

  因此，返回实际表数据是对时间和处理资源的一种浪费

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

  注意

  在使用count时,如果指定列名，则指定列的值为空的行被忽略，但如果COUNT()函数中用的是星号（*），则不忽略

• 数据分组 GROUP BY与HAVING

  • GROUP BY

  SQL聚集函数可用来汇总数据。这使我们能够对行进行计数，计算和与平均数，获得最大和最小值而不用检索所有数据

  目前为止的所有计算都是在表的所有数据或匹配特定的WHERE子句的数据上进行的。

  下面的例子返回供应商1003提供的产品数目：

  select count(*) as num_prods from products where vend_id = 1003

  但如果要返回每个供应商提供的产品数目怎么办?或者返回只提供单项产品的供应商所提供的产品,或返回提供10个以上产品的供应商怎么办?

  此时就需要使用分组了,分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。

  select vend_id,count(*) as num_prods from products group by vend_id

  • HAVING

  除了能用GROUP BY分组数据外，MySQL还允许过滤分组，规定包括哪些分组，排除哪些分组。

  例如，可能想要列出至少有两个订单的所有顾客。为得出这种数据，必须基于完整的分组而不是个别的行进行过滤

  事实上，目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代。

  唯一的差别是WHERE过滤行，而HAVING过滤分组。

  select cust_id,count(*) as orders from orders group by cust_id;

  select cust_id,count(*) as orders from orders group by cust_id having count(*) >= 2;

总结SELECT子句及其顺序

【绽放吧！数据库】有奖征文火热进行中：https://bbs.huaweicloud.com/blogs/285617