SQL检索数据的超全教程 1

举报
阿柠 发表于 2022/09/27 20:33:02 2022/09/27
【摘要】 检索数据写在前面，sql语句是不区分大小写的，SELECT和select是一样的，但是许多的SQL开发人员习惯对sql关键字大写，而对所有的列和表名使用小写。 select语句 检索单个列select prod_name from products; 检索多个列SELECT prod_id , prod_name,prod_price FROM products; 检索所有列SELECT...

检索数据

写在前面，sql语句是不区分大小写的，SELECT和select是一样的，但是许多的SQL开发人员习惯对sql关键字大写，而对所有的列和表名使用小写。

select语句

检索单个列

select prod_name from products;
xn_2022-08-24_09-11-27

检索多个列

SELECT prod_id , prod_name,prod_price  FROM products;
xn_2022-08-24_09-18-29

检索所有列

SELECT * FROM products;
xn_2022-08-24_09-22-32

检索不同行

使用DISTINCT关键字使得返回的信息是不同的行信息。

xn_2022-08-24_09-25-29

但是你不能这样：

xn_2022-08-24_09-27-25

除非两个列相同，否则所有行信息都会被展示出来。

限制结果

SELECT语句返回所有匹配的行。为了返回第一行或者前几行，可使用LIMIT子句

SELECT prod_name  FROM  products  LIMIT 5;
xn_2022-08-24_09-40-56 
SELECT prod_name  FROM  products  LIMIT 5,5;

LIMIT 5 , 5代表的是从行5开始的5行。

行0：检索出来第一行为行0而不是行1。LIMIT 1，1检索出来的是第二行，而不是第一行。

行不够时，如LIMIT 10,5, 。如果只有13行时，mysql只能返回他能返回的行数了就。

MySQL 5的LIMIT语法。LIMIT 4 OFFSET 3 表示从行3开始取4行。

使用完全限定的表明

这里其实就是指，把列名通过表明限定，甚至把表明通过数据库名来完全限定。

SELECT products.prod_name FROM bizhibihui.products;
xn_2022-08-24_09-53-11

排序检索数据 （ORDER BY）

排序数据

子句：之前写到的FROM就算是SELECT的一个子句。为了明确排序顺序所以我们可以使用 ORDER BY子句。

SELECT prod_name FROM products ORDER BY prod_name;
xn_2022-08-24_09-59-37

这样上面的搜索结果就实现了字母排序。

上面ORDER BY 子句使用的列是检索列，其实不使用检索的列进行排序也是完全合法的。

按多个列排序

我们在现实生活中也会遇到这样的情况：比如你说按姓名排序，当姓一样的时候，你就需要按名字来排序

SELECT prod_id , prod_price ,prod_name 
FROM  products
ORDER BY prod_price,prod_name;
xn_2022-08-24_10-09-49

上面的结果就可以观察到，当价格一样的时候，就会按照产品名称排序。

指定排序方向 （DESC 降序）

数据排序不仅限于升序排序（从A到Z），这只是默认的排序顺序。我们还可以使用降序排序关键字DESC

SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price DESC;
xn_2022-08-24_10-17-15

上面的图片就实现了价格的降序排序。

打算对多个列排序怎么办呢？

SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price DESC,prod_name;
xn_2022-08-24_10-20-34

上面就实现了先对价格降序，然后价格相同的按名字升序。

DESC关键词只应用到直接位于其前面的列名。所以如果你想对多个列进行降序排序，必须每个列指定DESC关键字。

ASC是升序关键词，但其实是没有什么用处的，因为默认就是升序呀。

例子：找出最昂贵的物品

SELECT prod_price
FROM products
ORDER BY prod_price DESC
LIMIT 1;
xn_2022-08-24_10-55-03

过滤数据 （WHERE 子句）

使用WHERE 子句

我们很少需要检索表中的所有行，通常会根据特定操作或报告的需要提取数据的子集。

在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤。WHERE子句在表名（FROM子句）之后给出。

SELECT prod_name ,prod_price
FROM products
WHERE prod_price=2.50;
xn_2022-08-24_14-09-41

只展示价格为2.50的产品。

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

WHERE子句操作符

WHERE子句操作符 说明
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
BETWEEN 在指定的两个值之间

检查单个值：

SELECT prod_name,prod_price
FROM products
WHERE prod_name = 'fuses';
xn_2022-08-24_14-20-17 
SELECT  prod_name,prod_price
FROM products
WHERE prod_price < 10;
xn_2022-08-24_14-23-02

不匹配检查：

SELECT vend_id,prod_name
FROM products
WHERE vend_id<>1003;
xn_2022-08-24_14-25-23

范围值检查:

SELECT prod_name ,prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;
xn_2022-08-24_14-28-14

从上面的结果可以看出来这个BETWEEN的范围是闭区间

空值检查：

SELECT cust_id
FROM customers
WHERE cust_email IS NULL;
xn_2022-08-24_14-31-07

:heavy_exclamation_mark:一定要注意一点，空值不是0。

:heavy_exclamation_mark:过滤与不匹配：在通过过滤选择出不匹配值的行中，你可能希望可以返回具有NULL值的行，但是，不行。

数据过滤

组合WHERE子句

之前的WHERE子句都是单一的条件，其实是可以使用多个WHERE子句的，这些子句可以使用AND和OR子句的方式使用。

AND操作符

为了通过不止一个列进行过滤，可使用AND操作符给WHERE子句附加条件。

SELECT prod_id , prod_price, prod_name
FROM products
WHERE vend_id = 1003 AND prod_price <= 10;
xn_2022-08-24_14-54-49

OR操作符

检索匹配任一条件的行。

SELECT prod_name,prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003;
xn_2022-08-24_14-57-58

AND的计算次序优先级更高

为了解决这一问题，方法就是加圆括号（）

IN操作符

IN操作符用来指定条件范围，范围内的每个条件都可以进行匹配，IN取合法值的由逗号隔开。

SELECT prod_name ,prod_price
FROM products
WHERE vend_id IN (1002,1003)
ORDER BY prod_name;
xn_2022-08-24_15-11-50

如果你认为IN和OR功能相同，这种想法是正确的。下面就是晚上上面的同样的工作。

SELECT prod_name ,prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003
ORDER BY prod_name;

那为什么选择IN呢？

在使用长的合法选项清单时，IN操作符的语法更清楚更直观

在使用IN时，计算次序更容易管理

IN操作符一般比OR操作符清单执行的更快

IN的最大优点是可以包含其他SELECT 语句，使得能够更动态地建立WHERE子句。

NOT操作符

NOT功能：否定他之后所跟的任何条件。

SELECT prod_name,prod_price
FROM products
WHERE vend_id NOT IN (1002,1003)
ORDER BY prod_name;
xn_2022-08-24_15-26-31

用通配符进行过滤

LIKE操作符

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

%百分号通配符

%表示任何字符出现任意次数。、

例：为了找出以词jet起头的产品：

SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE 'jet%';

ea0d38e5a6a34cd39580f5b386364a1c_tplv-k3u1fbpfcp-zoom-1

例：通配符可以在搜索模式的任意位置使用，并且可以使用多个通配符。

SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE '%anvil%';

85339bd49e0d4221b0622ef7a900af74_tplv-k3u1fbpfcp-zoom-1

🏳️‍🌈需要注意的是，%是可以匹配0个字符的。%代表搜索模式中给定位置的0个，1个，或多个字符。

🉐还需要注意的一点是，尾部空格是会干扰通配符的匹配的。如果在保存anvil时尾部有几个空格，‘%anvil’就没有办法匹配成功的。解决方法就是你在末尾也加上一个%。或者更好的方法是使用函数。

🏢虽然似乎%可以匹配任何东西，但是有一个例外，那就是NULL。即使是WHERE prod_name LIKE '%'也不能匹配用值为NULL的产品名。

_下划线通配符

下划线只匹配单个字符。

SELECT prod_id ,prod_name 
FROM products
WHERE prod_name LIKE '_ ton anvil';

289be010f5c04325823d826f29b7474c_tplv-k3u1fbpfcp-zoom-1

📧和%不一样，下划线只能匹配一个字符，不能多也不能少。

使用通配符的技巧

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

用正则表达式进行搜索

使用MySQL正则表达式

基本字符匹配

例：检索列prod_name 包含文本1000的所有行。

SELECT prod_name 
FROM products 
WHERE prod_name REGEXP '1000'
ORDER BY prod_name;

20565ab7054c4fb09cc43b28b4740920_tplv-k3u1fbpfcp-zoom-1

💛REGEXP告诉MySQL，它后面跟着的是正则表达式。

SELECT prod_name 
FROM products 
WHERE prod_name REGEXP '.000'
ORDER BY prod_name;

5699b639ca5143daa6a3d5af135151a6_tplv-k3u1fbpfcp-zoom-1

⚠️这个 . 可以说是正则表达式的一部分用途了。它表示匹配任一一个字符。

LIKE 和 REGEXP的区别

  • LIKE是匹配整个列，完全一样或者带通配符才可以匹配成功
  • REGEXP是只要列中包含文本就可以匹配成功。当然REGEXP也可以匹配整个列值。

区分大小写

  • 正则表达式的匹配是不区分大小写的。为区分大小写可使用BINARY关键字。

    • 如：WHERE prod_name REGEXP BINARY ‘JetPack .000’

进行OR匹配

使用符号 |

SELECT prod_name 
FROM products
WHERE prod_name REGEXP '1000|2000'
ORDER BY prod_name;

c15544f4801342c3a89448a31b3900ba_tplv-k3u1fbpfcp-zoom-1

匹配几个字符串之一

如果你只想匹配特定的字符，可以通过指定一组用 [ ] 括起来的字符完成。

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[123] Ton'
ORDER BY  prod_name;

714b54d7e9c54c6ba05c6fd269d739e6_tplv-k3u1fbpfcp-zoom-1

上面这个结果，还想说明一个问题，就是REGEXP虽然是可以匹配尾巴和头部的空格，但是你在单引号之间的空格还是需要你自己写上去的，单引号之间的空格你不写可是匹配不到结果的。

如上面结果所示，[]其实就是另一种方式的or语句，【123】其实也就是【1|2|3】。当然你写后者也是没有任何问题的。

下面我们再看一个例子：

SELECT prod_name
FROM products
WHERE prod_name REGEXP '1|2|3 Ton'
ORDER BY  prod_name;

44a4d29031e8450797dd3279d7ca534c_tplv-k3u1fbpfcp-zoom-1

从结果上我们不难看出，你不用【】括起来，MySQL就会理解为1or2or3 Ton这样的形式

而下面那三行能匹配到也就是因为里面有1or2。

⚠️字符集也可以被否定：[ ^123 ]的意思就是匹配除了这些以外的任何东西。

这个 ^ 在【】括号里，就是除了的1,2,3,的意思，但是如果没有【】，那就是定位符是文本的开始位置的意思。

你比如说下面这两结果就是一样的哟：

fd8a9843f0184d4a8594f4e25286e906_tplv-k3u1fbpfcp-zoom-1

79b1fd00bbe64cebb04e6bbcacf1ff03_tplv-k3u1fbpfcp-zoom-1

你再看一个理解定位符^

a0d5898d027f4c90ae4a0cf644169324_tplv-k3u1fbpfcp-zoom-1

当时我有一个这样的例子不明白，后来想明白了。

9e42fc93c7004e0fa581ed2c69181d25_tplv-k3u1fbpfcp-zoom-1

上面这个当时我就不明白，为什么都不包含1了，红框里还有，这就是REGEXP，你不包含1or2or3.你有其他的元素呀，所以你就得显示出来。

匹配范围

集合可以用来定义要匹配的一个或者多个字符。

例如：下面的集合将匹配数字0到9

【0123456789】

为了简化这种类型的集合，可使用 - 来定义一个范围。

【0-9】

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[1-5] Ton'
ORDER BY prod_name;

7a25403dc3d9499a9e672a19a965fc98_tplv-k3u1fbpfcp-zoom-1

匹配特殊字符

也就是转义。我们要匹配特殊字符必须用\为前导。

SELECT vend_name
FROM vendors
WHERE vend_name REGEXP '\.'
ORDER BY vend_name;

53533b1c006c41f6a96df8db93d32d74_tplv-k3u1fbpfcp-zoom-1

⚠️多数的正则表达式实现使用单个反斜杠转义特殊字符，以便能使用这些字符本身。但是mysql要求两个反斜杠（mysql自己解释一个，正则表达式库解释一个）。

匹配字符类

存在找出你自己经常使用的数字，所有字母字符或所有数字字母字符等的匹配。为方便工作，可以使用预定义的字符集，称为字符类。

类 说明
【：alnum：】 任意字母和数字（同【a-zA-Z0-9】）
【：alpha：】 任意字符【a-zA-Z】
【：blank：】 空格和制表【\t】
[:cntrl:] ASCII控制字符（ASCII0到31和127）
【：digit：】 任意数字（【0-9】）
【：graph：】 与【：print：】相同，但不包括空格
【：lower：】 任意小写字母【a-z】
【：print：】 任意可打印字符
【：punct：】 既不在【：alnum：】也不在【：cntrl：】中的任意字符
【：space：】 包括空格在内的任意空白符（同【\f\n\r\t\v】）
[:upper:] 任意大写字母【A-Z】
【：xdigit：】 任意十六进制数字（同【a-fA-F0-9】）

匹配多个实例

重复元字符

元字符 说明
* 0个或者多个匹配
+ 1个或多个匹配（等于{1，}）
？ 0个或1个匹配（等于{0,1}）
{n} 指定数目的匹配
{n，} 不少于指定数目的匹配
{n,m} 匹配数目的范围 
SELECT prod_name
FROM products
WHERE prod_name REGEXP '\([0-9] sticks?\)'
ORDER BY prod_name;

c8f0e6bcc4d64973b98a866553f8f13e_tplv-k3u1fbpfcp-zoom-1

🎃我觉得难理解的点就在于，那个？的匹配（s后的？使s可选，因为？匹配他前面的的任何字符0次或者1次）。这也就是会有第一条结果的原因。

例：匹配连在一起的的4位数字。

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[[:digit:]]{4}'
ORDER BY prod_name;

f3af85051c144c3692d852396ab647be_tplv-k3u1fbpfcp-zoom-1

定位符

之前的例子都是匹配任一一个串中任意位置的文本。为了匹配特定位置的文本，我们需要使用定位符。

元字符 说明
^ 文本的开始
$ 文本的结束
[[:<:]] 词的开始
[[:>:]] 词的结束

例：找一个以数或小数点开始的所有产品

SELECT prod_name
FROM products
WHERE prod_name REGEXP '^[0-9\.]'
ORDER BY prod_name;

f0bc2e7a62f34a989fcb509d1bda68ec_tplv-k3u1fbpfcp-zoom-1

创建计算字段

计算字段

存储在表中的数据都不是应用程序所需要的。我们需要直接从数据库中检索出转换计算，格式化后的数据。这就是计算字段发挥作用的所在了。计算字段并不实际存在于数据库表中。计算字段是在运行SELECT时语句内创建的。

拼接字段

我们需要输出这样的一个格式 name（location），但数据库中是没有这样的字段的，所以我们需要拼接列来显示一个这样的信息。

解决方法：我们使用Concat函数来拼接两个列

SELECT Concat(vend_name,'(',vend_country,')')
FROM vendors
ORDER BY vend_name;
xn_2022-08-26_14-13-01

RTrim函数去掉值右边的所有空格，通过使用RTrim（）。各个列都进行了整理。

SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')')
FROM vendors
ORDER BY vend_name;

:parasol_on_ground:LTrim呢，显而易见就是去除串左边的空格。

:sailboat:Trim，去除串左右两边的空格。

刚刚的输出我们可以看到那个表头是很不美观的，所以我们可以使用别名

AS别名

SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')') AS vend_title
FROM vendors
ORDER BY vend_name;
xn_2022-08-26_14-35-54

执行算术计算

我们可以在SELECT语句中就添加一些算数计算，以计算一些列的值。

SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;
xn_2022-08-26_14-44-45

使用数据处理函数

函数

函数没有SQL的可移植性强

使用函数

文本处理函数

:mag:Upper（）函数:将文本转化为大写

SELECT vend_name ,Upper(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;
xn_2022-08-26_15-12-41

常用的文本处理函数

函数 说明
Left（） 返回串左边的字符
Length 返回串的长度
Locate（） 找出串的一个子串
Lower（） 将串转换为小写
LTrim（） 去掉串左边的空格
Right（） 返回串右边的字符
RTrim（） 去掉串右边的空格
Soundex（） 返回串的SOUNDEX值 （这玩意是发音类似匹配）
SubString（） 返回子串的字符
Upper（） 将串转换为大写

日期和时间处理函数

常用日期和时间处理函数

函数 说明
AddDate（） 增加一个日期（天，周等）
AddTime（） 增加一个时间（时，分等）
CurDate（） 返回当前日期
CurTime（） 返回当前时间
Date（） 返回日期时间的日期部分
DateDiff（） 计算两个日期之差
Date_Add() 高度灵活的日期运算函数
Date_Format() 返回一个格式化的日期或时间串
Day（） 返回一个日期的天数部分
DayOfWeek（） 对于一个日期返回对应的星期几
Hour（） 返回一个时间的小时部分
Minute（） 返回一个时间的分钟部分
Month（） 返回一个日期的月份部分
Now（） 返回当前日期和时间
Second（） 返回一个时间的秒部分
Time（） 返回一个日期时间的时间部分
Year（） 返回一个日期的年份部分 
SELECT cust_id,order_num
FROM orders
WHERE Date(order_date) = '2005-09-01';
xn_2022-08-26_15-59-41

加Date（）是一个好习惯，即使你知道表里只有日期，但是这样的方式可以万无一失，万一以后表里加入时间，也不会出现匹配不到的错误。

例:如果你想检索出2005年9月下的所有订单。

法一：

SELECT cust_id,order_num
FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01'AND '2005-09-30';
xn_2022-08-26_16-18-45

法二：

SELECT cust_id ,order_num
FROM orders
WHERE Year(order_date) = 2005 AND Month(order_date) = 9;
xn_2022-08-26_16-22-46

数值处理函数

常用的数值处理函数

函数 说明
Abs（） 返回一个数的绝对值
Cos（） 返回一个角度的余弦
Exp（） 返回一个数的指数值
Mod（） 返回除操作的余数
Pi（） 返回圆周率
Rand（） 返回一个随机数
Sin（） 返回一个角度的正弦
Sqrt（） 返回一个数的平方根
Tan() 返回一个角度的正切
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
MySQL SQL
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入