本篇主要整理检索数据、排序数据以及过滤数据相关的SQL语句。
一、检索数据
关键字:SELECT语句
- 功能:从一个表中检索特定某列或某几列
输入:SELECT 列名(,列名2,列名3…列名n)
FROM 表名;
/ SELECT 表名.列名
FROM (数据库名.)表名(完全限定格式)
SQL语句本身不区分大小写,但有些标识符(如数据库名、表名、列名)可能不同,所以按照大小写的惯例,且使用时保持一致
处理SQL语句时,其中所有空格都被忽略。但将SQL语句分成多行更容易阅读和调试;多条SQL语句必须以分号(;)
分隔。
- 功能:从一个表中检索所有列
输入:SELECT * FROM 表名;
列的顺序一般是列在表定义中出现的顺序。但表的模式的变化(如添加或删除列)可能会导致顺序的变化。
- 功能:只返回不同(唯一)的行
输入:SELECT DISTINCT 列名(,列名2,列名3,…列名n)
FROM 表名;
DISTINCT关键字应用于所有列
- 功能:返回指定开始行和行数的检索结果
输入:SELECT 列名
FROM 表名
LIMIT (开始行数,)行数;
/ SELECT 列名
FROM 表名
LIMIT 开始行数 OFFSET 行数(MySQL 5开始支持)
LIMIT 5,5指示MySQL返回从行5开始的5行。即第一个数为开始位置,第二个数为要检索的函数。若仅有一个参数,则从第一行开始,给出的数为返回的行数。
检索出的第一行为行0而不是行1。LIMIT 1,1检索为第二行而不是第一行
二、排序数据
子句:SQL语句由子句构成,有些子句是必须的,而有的是可选的。一个子句通常由一个关键字和所提供的数据组成。
排序一般使用ORDER BY子句。ORDER BY 子句取一个或多个列的名字,据此对输出排序。
- 功能:按字母顺序对检索的一个(或一组)特定列的数据排序输出
输入:SELECT prod_name(列名)
FROM 表名
ORDER BY prod_name(,prod_price);
可以使用非检索的列排序数据,即ORDER BY子句中使用的列可以为非检索的列
按多个列排序时,排序完全按所规定的顺序进行。
- 功能: 指定排序方向
输入:SELECT prod_id,prod_price,prod_name(列名)
FROM 表名
ORDER BY prod_price DESC/ASC, prod_name;
DESC、ASC关键字只应用到直接位于其前面的列名,一般默认为升序,所以ASC作用不大
如果想在多个列上进行降序排序,必须对每个列指定DESC关键字
- 功能:找出最高/最低值
输入:SELECT prod_price
FROM 表名
ORDER BY prod_price DESC/ASC
LIMIT 1;
ORDER BY子句必须位于FROM子句之后,LIMIT子句必须位于ORDER BY子句之后,使用子句的次序不对将产生错误消息
三、过滤数据
关键字:WHERE子句、通配符、正则表达式
- 功能:从指定表中检索指定列后,返回特定条件的行
输入:SELECT prod_name,prod_price(列名)
FROM 表名
WHERE prod_price = 2.50;(搜索条件)
数据也可以在应用层过滤,但让客户机应用(或开发语言)处理数据库的工作将会极大地影响应用的性能,并且使所创建的应用完全不具备可伸缩性。此外,如果在客户机上过滤数据,服务器不得不通过网络发送多余的数据,这将导致网络带宽的浪费
若同时使用WHERE子句和ORDER BY子句时,ORDER BY 应位于WHERE之后
where子句操作符
操作符说明=等于<> / !=不等于<小于<=小于等于>大于BETWEEN AND在指定的两个值之间(包括指定的开始值和结束值)
- 功能:空值检查
输入:SELECT prod_name
FROM 表名
WHERE cust_email IS NULL;
在通过过滤选择出不具有特定值的行时,不会返回具有NULL值的行。因为未知具有特殊的含义,数据库不知道它们是否匹配,所以在匹配过滤或不匹配过滤时不返回它们。
因此,在过滤数据时,一定要验证返回数据中确实给出了被过滤列具有NULL的行
- 功能:多个过滤条件检索的结果
输入:SELECT prod_id,prod_price,prod_name
FROM 表名
WHERE vend_id = 1003 AND prod_price <= 10;
组合WHERE子句操作符
操作符说明优先级AND用来指示检索满足所有给定条件的行高OR用来指示检索匹配任一条件的行低IN用来指定条件范围,范围中的每个条件都可以进行匹配,IN取合法值的由逗号分隔的清单,**全都括在圆括号中**,作用同OR NOT否定其之后所跟的任何条件(MySQL支持其对IN、BETWEEN和EXISTS子句取反)LIKE指示后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。
最常使用的通配符是百分号(%)。在搜索串中,%表示任何字符出现任意次数。
使用示例:SELECT * FROM book WHERE NAME LIKE ‘Java%’使用IN操作的优点:
1、 在使用长的合法选项清单时,IN操作符的语法更清楚且更直观。
2、 在使用IN时,计算的次序更容易管理(因为使用的操作符更少)。
3、 IN操作符一般比OR操作符清单执行更快。
4、 IN的最大优点是可以包含其他SELECT语句,使得能够更动态地建立WHERE子句。
- 通配符:用来匹配值的一部分的特殊字符。其可在搜索模式中任意位置使用,并且可以使用多个通配符。
搜索模式:由字面值、通配符或两者组合构成的搜索条件。 - 功能:匹配任何位置包含文本anvil的值
输入:SELECT prod_id,prod_name
FORM 表名
WHERE prod_name LIKE ‘%anvil%’
搜索模式’%anvil%’表示匹配任何位置包含文本anvil的值,而 不论它之前或之后出现什么字符。
- 功能:匹配以s起头以e结尾的所有产品
输入:SELECT prod_name
FORM 表名
WHERE prod_name LIKE ‘s%e’
通配符可以出现再搜索模式的中间
尾空格可能会干扰通配符匹配。例如,在保存词anvil时,如果它后面有一个或多个空格,则子句WHERE prod_name LIKE ‘%anvil’将不会匹配它们。解决这个问题的一个简单的办法是在搜索模式最后附加一个%。一个更好的办法是使用函数去掉首尾空格。
除了一个或多个字符外,%还能匹配0个字符。%代表搜索模式中给定位置的0个、1个或多个字符。但%不能匹配NULL
下划线(_)通配符与%用途一样,但是能且只能匹配一个字符。在确实需要使用通配符时,除非绝对有必要,否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处,搜索起来是最慢的。
复杂过滤条件
正则表达式:用来匹配文本的特殊的串(字符集合)
应用场景:从一个文本中提取电话号码、查找名字中间有数字的所有文件、再一个文本块中找到所有重复的单词、替换一个页面中的所有URL为这些URL的实际HTML链接等
MySQL仅支持多数正则表达式实现的一个很小的子集
- 功能:检索列prod_name包含文本1000的所有行
输入:SELECT prod_name
FROM 表名
WHERE prod_name REGEXP ‘1000’
ORDER BY prod_name;
输出:| prod_name |
| JetPack 1000 |
用LIKE可以实现相同功能
输入:SELECT prod_name
FROM 表名
WHERE prod_name LIKE ‘%1000’
ORDER BY prod_name;
- 功能:检索列prod_name包含文本‘000’的所有行
输入:SELECT prod_name
FROM 表名
WHERE prod_name REGEXP ‘.000’
ORDER BY prod_name;
.是正则表达式语言中一个特殊的字符。它表示匹配任意一个字符,因此,1000和2000都匹配且返回。
现在版本的MySQL中的正则表达式匹配不区分大小写(即,大写和小写都匹配)。为区分大小写,可使用BINARY关键字,如WHERE prod_name REGEXP BINARY ‘JetPack .000’。
- 功能:OR匹配
输入:SELECT prod_name
FROM 表名
WHERE prod_name REGEXP ‘1000|2000’
ORDER BY prod_name; - 功能:匹配特定字符
输入:SELECT prod_name
FROM 表名
WHERE prod_name REGEXP ‘[123] Ton’
ORDER BY prod_name;
输出:| prod_name |
| 1 ton anvil |
| 2 ton anvil |
[123]定义一组字符,为[1|2|3]的缩写,作用同OR,它的意思是匹配1或2或3;也可以用[1-3]这种方式来表达
字符集合否定用^,它将匹配除指定字符外的任何东西。 如[^123]匹配除这些字符外的任何东西。
- 功能:匹配特殊字符
输入:SELECT prod_name
FROM 表名
WHERE prod_name REGEXP ‘\.’
ORDER BY prod_name;
输出:| prod_name |
| tommy Inc. |
转义字符\与C、C++用法一样,也可用来引用元字符(如\\r回车、\\f换页等)
多数正则表达式实现使用单个反斜杠转义特殊字符, 以便能使用这些字符本身。但MySQL要求两个反斜杠(MySQL 自己解释一个,正则表达式库解释另一个)。
为方便工作,可以使用预定义的字符集,称为字符类 (character class)。下表列出字符类以及它们的含义。
类 | 说明 |
[: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:] | 任意可打印字符 |
[:space:] | 包括空格在内的任意空白字符(同[\\f\\n\\r\\t\\v]) |
[:upper:] | 任意大写字母(同[A-Z]) |
[:xdigit:] | 任意十六进制数字(同[a-fA-F0-9]) |
- 功能:匹配多个实例
输入:SELECT prod_name
FROM 表名
WHERE prod_name REGEXP ‘\([0-9] sticks?\)’
ORDER BY prod_name;
输出:| prod_name |
| TNT (1 stick) |
| TNT (1 sticks) |
双斜杠+( 匹配(, [0-9]匹配任意数字,sticks?匹配stick 和sticks(s后的?使s可选,因为?匹配它前面的任何字符的0次或1次出现),双斜杠+)匹配)
重复元字符:
元字符说明*0个或多个匹配+1个或多个匹配(等于{1,})?0个或1个匹配(等于{0,1}){n}指定数目的匹配{n,}不少于指定数目的匹配{n,m}匹配数目的范围(m不超过255)
- 功能:匹配连在一起的4位数字
输入:SELECT prod_name
FROM 表名
WHERE prod_name REGEXP ‘[[:digit:]]{4}’
ORDER BY prod_name;
输出:| prod_name |
| JetPack 1000 |
| JetPack 2000 |
[:digit:]匹配任意数字,因而它为数字的一个集合。{4}确切地要求它前面的字符(任意数字)出现4次,所以 [[:digit:]]{4}匹配连在一起的任意4位数字。
另一表述为:
SELECT prod_name
FROM 表名
WHERE prod_name REGEXP ‘[0-9][0-9][0-9][0-9]’
ORDER BY prod_name;
- 功能:检索出以小数点开始的所有产品
输入:SELECT prod_name
FROM 表名
WHERE prod_name REGEXP ‘^[0-9\.]’
ORDER BY prod_name
定位元字符
元字符说明^文本的开始$文本的结尾[:<:]词的开始[:>:]词的结束
LIKE与REGEXP的区别:
LIKE匹配整个串而REGEXP匹配子串。利用定位符,通过用^开始每个表达式,用$结束每个表达式,可以使REGEXP与LIKE作用一样。