检索数据

SQL 语句中的检索语句时 SELECT

检索单个列: SELECT product_name FROM products;

注意: 这里得到的数据是未排序的

检索多个列: SELECT pro_id, prod_name, prod_price FROM products;

检索所有列: SELECT * FROM products;

注意: 除非是确实需要每个列的数据, 否则不要使用通配符 *, 因为这会降低检索和应用程序的性能

检索不同行: SELECT DISTINCT ven_id FROM products;

注意: DISTINCT 的作用域是全部列, 比如 SELECT DISTINCT ven_id, ven_name FROM products;, 这样只会去掉两列都相同的数据

限制结果: SELECT prod_name FROM products LIMIT 5;

此语句表示返回结果的前 5 个, 另外由于 mysql 查询结果的顺序不确定 (其实是可以找出规律的, 但是没必要), 所以最好加上 ORDER BY

带有偏移值的限制结果: SELECT prod_name FROM products LIMIT 5, 5;

此语句表示返回从第 5 行开始的 5 行, 第一个数字为起始行数, 第二个为选取行数 (因为容易搞混, 所以在 MySQL5 中提供个形如 LIMIT 5 OFFSET 5的写法, 含义相同) 注意: 行数从 0 开始

使用完全限定的表名: SELECT products.prod_name FROM crash_course.products;

选取 crush_course数据库中products表的prod_name列

排序检索数据

mysql 中检索出来的数据一般按照他在底层的顺序显示, 可能是最初插入的顺序, 但是如果数据进行过更新或删除, 则此顺序会收到影响

子句(clause): sql 语句由子句构成, 例如 select 语句里面有 from 子句

按单个列排序: SELECT prod_name FROM products ORDER BY prod_name;

按多个列排序: SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name;

多个列排序时, 排序按照给定顺序执行. 例如上述语句, 先对 prod_price 排序, 当 prod_price 相同时再对 prod_name排序

规定排序方向: SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name;

DESC 表示按照降序排列, 该规则只适用于他前面的一个字段. 例如上述语句的结果是prod_price 按照降序排列, prod_name 按照默认的升序排列. 如果多个字段都需要降序排列, 则每一个字段后面都应该加上 DESC

过滤数据

使用 WHERE 子句过滤数据: SELECT prod_name, prod_price FROM products WHERE prod_price = 2.5;

WHERE子句的操作符:

检查属性是否为 NULL: SELECT prod_name FROM products WHERE prod_price IS NULL;

创建表的时候尽量让每个字段都有一个初始值, 避免字段因为各种情况出现意外的 NULL (比如使用 gorm 时, 当字段为 0 值时则不会写入表中, 此时该字段会出现意外的空值)

数据过滤

对多个列同时过滤: SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1003 AND prod_price <= 100;

检索匹配任一条件的行: SELECT prod_id, prod_name, prod_price FROM products WHERE vend_id = 1003 OR prod_price <= 100;

AND 的优先级比 OR 高, 在二者同时出现时建议加括号

AND 和 OR 同时出现: SELECT prod_name, prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 100;

对指定范围内的每个条件进行匹配: SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002, 1003) ORDER BY prod_name;

上述句子等同于: SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 ORDER BY prod_name; > IN 要比 OR 执行地更快, 并且易读性更好 另外, IN 里面可以包含其他 SELECT 子句, 这会在后面介绍

否定操作符 NOT: SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN (1002, 1003) ORDER BY prod_name;

MySQL 支持 NOT 对 IN、BETWEEN 和 EXISTS 取反

用通配符进行过滤

通配符 (wildcard): 用来匹配值的一部分的特殊字符

搜索模式 (search pattern): 由字面值、通配符或两者组合构成的搜索条件

要在搜索子句中使用通配符, 就需要用到 LIKE 操作符. 并且从技术上来讲, LIKE 应当是谓词而不是操作符.

% 表示任何字符出现任意次数: SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%';

% 可以出现在搜索模式的任意位置: SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%a%b%c';

% 通配符无法匹配到 NULL

_ 匹配单个字符: SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil';

上述语句只会匹配到类似与 ‘1 ton anvil’ 的词语, 像 ‘12 ton anvil’ 则不会匹配


使用通配符的技巧:

  1. 不要过度使用通配符, 因为很慢;
  2. 在必须使用通配符时, 尽量不要把它放在搜索模式的开始处, 因为这样最慢;
  3. 注意通配符的位置, 避免放错位置导致结果错误.

用正则表达式进行搜索

检索 $prod\underbar{ }name$ 中包含文本 1000 的所有行: SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;

区分大小写的正则匹配: SELECT prod_name FROM products WHERE prod_name REGEXP BINARY 'JetPack .000';

.000 是正则表达式, 以上语句会匹配 ‘JetPack 100’、‘JetPack 2000’ 等

OR 匹配： SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;

可以匹配到 a1000、b2000

匹配几个字符之一： SELECT prod_name FROM products WHERE prod_name REGEXP '[123] ton' ORDER BY prod_name;

可以匹配到 1 ton、2 ton、3 ton

匹配范围: SELECT prod_name FROM products WHERE prod_name REGEXP '[1-3] ton' ORDER BY prod_name;

与上一条语句结果相同

匹配特殊字符: SELECT prod_name FROM products WHERE prod_name REGEXP '\\.' ORDER BY prod_name;

匹配含有 ‘.’ 的行

匹配字符类

匹配多个实例: SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)' ORDER BY prod_name;

以上匹配结果可以是: (0 stick)、(1 sticks)等

定位符

寻找以数字或者小数点开头的行: SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name;

可以使用使用 SELECT 在不使用数据库表的情况下检验正则表达式是否正确 语法为: SELECT 'hello' REGEXP '[0-9]'; 匹配成功返回 1, 否则返回 0, 显然上述例子返回 0