Retrieving Data From a Single Table 

 

选择语句 The SELECT Statement

 

选择语句整体的基本用法。

 

USE sql_store;


SELECT * / 1, 2  -- *纵向筛选所有列，甚至可以是常数
FROM customers  -- 选择表
WHERE customer_id = 1 -- 横向筛选行
ORDER BY first_name  -- 排序


-- 单行注释

 

选择子句 The SELECT Clause

 

SELECT 语句可以用*号来查询所有的列，或者我们指定想要返回的列。可用AS关键字给每列和结果设置别名，可以用DISTINCT去除重复内容。

 

SELECT last_name, first_name, points, points+10
FROM customers

 

如果每行太长可以拆分子句，把每列放到新的一行

 

 如果想要一个清晰的描述性名称,可以给这一列一个别名，AS语句可以命名新添加的列。如果单词中间想用空格记得加双引号，不加双引号就只能像下面一样写成discount_factor。

 

USE store;
SELECT 
    last_name, 
    first_name, 
    points, 
    points*10+100 AS discount_factor
FROM customers

 

DISTINCT去除重复内容，通过SELECT DISTINCT state可以在customers表格里获取一份州的唯一列表。

 

USE store;
SELECT DISTINCT state
FROM customers

 

WHERE子句 The WHERE Clause

 

WHERE 是用于指定一个状态，同时取出由单表中的数据，或连接多个表。

 

筛选数值大于3000的积分的顾客

SELECT*
FROM Customers
WHERE points>3000

 

筛选地点在VA的顾客

SELECT*
FROM Customers
WHERE state=''va''

 

练习：写段查询，得到2019年下的订单。基于WHERE子句查询。

 

SELECT*
FROM orders
WHERE order_date>='2019-01-01'

 

AND, OR, NOT 运算符 The AND, OR, NOT Operator

 

用逻辑运算符AND、OR、NOT对（数学和）比较运算进行组合实现多重条件筛选

执行优先级：数学→比较→逻辑

 

AND运算符表示并列关系。

 

SELECT*
FROM Customers
WHERE birth_date>'1990-01-01' AND points>1000

 

OR表示只要有一个条件是正确的，那行就会在结果集里被返回

 

SELECT*
FROM Customers
WHERE birth_date>'1990-01-01' OR (points>1000 AND
      state='VA')

 

AND和OR同时运行的话，AND优先级在前。

 

NOT

 

SELECT*
FROM Customers
WHERE NOT(birth_date>'1990-01-01' OR points>1000)

 

 

练习

--FROM the order-items table, get the items

-- for order #6

-- where the total price is greater than 30

 

答案

SELECT *
FROM order_items
WHERE order_id=6 AND unit_price*quantity>30

 

IN运算符 The IN Operator

当有多个条件要检查的时候，可以用IN 运算符代替它，它在WHERE子句中指定多个值。实质是多重相等比较运算条件的简化。

 

选出'va'、'fl'、'ga'三个州的顾客

SELECT *
FROM Customers
WHERE state='VA' OR state='GA' OR state='FL'

 

可以采用另一种简单的方式。

SELECT *
FROM Customers
WHERE state IN('VA','GA','FL')

 

如果想知道这三个州以外的顾客，可以用NOT子句。

SELECT *
FROM Customers
WHERE state NOT IN('VA','GA','FL')

 

练习

--Return products with

-- quantity in stock equal to 49, 38, 72

选出库存量刚好为49、38或72的产品

 

答案

SELECT*
FROM Products
WHERE quantity_in_stock IN(49, 38, 72)

 

BETWEEN 运算符 The BETWEEN Operator

BETWEEN 运算符用于选取介于两个值之间的数据范围内的值，值可以是数字、文本或日期。

 

假设想获得积分介于1000和3000之间的顾客

SELECT *
FROM Customers
WHERE points>=1000 AND points<=3000

 

BETWEEN 运算符的用法得出相同的结果

SELECT *
FROM Customers
WHERE points BETWEEN 1000 AND 3000

 

练习

 

--Return customers born

--between 1/1/1990 and 1/1/2000

找到出生在1990年1月1日到2000年1月1日的顾客。

 

答案

 

SELECT*
FROM Customers
WHERE birth_date BETWEEN '1990-01-01'AND '2000-01-01'

 

LIKE运算符 The LIKE Operator

 

使用LIKE运算符在与指定的模式匹配中的字段中查找值。通常在WHERE子句中使用它来搜索列中的指定模式。

 

找到姓氏首字母为b的顾客

 

SELECT*
FROM Customers
WHERE last_name LIKE 'b%'


--%any number of characters
--_single character

 

%表示b后面的字符，可以是一位，也可以是多位。

如果想找姓氏里面带b的顾客，可以写成%b%。

同理，如果想找姓氏里以y结尾的顾客，可以写成%y。

_表示一个单字符，5个_表示五个字符，%可以表示任意字符。

如果想找以b开头y结尾的五位数姓氏的顾客，可以写成b___y

 

练习

--Get the customers whose

-- address contain TRAIL or AVENUE

-- phone numbers end with 9

 

SELECT*
FROM Customers
WHERE address LIKE '%trail%' OR
      address LIKE '%avenue%'

 

PAY ATTENTION!

• 字符加引号。
• 地址中含有trail和avenue的可能前后都含有其他字符，所以前后都需要加上%，比如：'%trail%'。
• 不要忘记第二个address LIKE。
• 为了美观可以考虑换行。

 

SELECT*
FROM Customers
WHERE phone LIKE '%9'

 

如果想找其他所有手机号尾数不为9的顾客，可以选择用NOT

 

SELECT*
FROM Customers
WHERE phone NOT LIKE '%9'

 

REGEXP 运算符 REGEXP Operator

 

REGEXP 是表达式regular expression的缩写，我们可以用它搜索更复杂的模式

 

如果想找名字里含有field的顾客，可以用LIKE运算符。

 

SELECT*
FROM Customers
WHERE last_name LIKE '%field%'

 

也可以用REGEXP运算符达到同样的效果。

 

SELECT*
FROM Customers
WHERE last_name REGEXP 'field'

 

^可以表示字符串的开头，^field表示姓氏必须以field打头。

 

$可以放在字符串的结尾，field$表示姓氏必须以field结尾。

 

可以用 | 搜索多个单词。比如想查询一些顾客，他们的姓氏包含field或者mac或者rose，可以这样表示。

 

SELECT*
FROM Customers
WHERE last_name REGEXP 'field|mac|rose'

 

假如找姓氏里字母e前有g和i和m的顾客的话，需要写成'[gim]e'。

 

SELECT*
FROM Customers
WHERE last_name REGEXP '[gim]e'

 

同理，假如找姓氏里字母e后有f和q和m的顾客的话，需要写成'e[fmq]'。

 

SELECT*
FROM Customers
WHERE last_name REGEXP 'e[fmq]'

 

如果找e前面有a-h任意字母的姓氏的顾客，可以写成'[a-h]e'。

 

SELECT*
FROM Customers
WHERE last_name REGEXP '[a-h]e'

 

总结：几种特殊符号用法

--^ beginning
--$ end
--| logical or
--[abcd]
--[-]

 

练习

--Get the customers whose

--first names are ELKA or AMBUR

--last names end with EY or ON

--last names start with MY or contains SE

--last names contain B followed by R or U

 

答案

SELECT *
FROM Customers
WHERE last_name REGEXP 'elka|ambur'

 

SELECT *
FROM Customers
WHERE last_name REGEXP 'ey$|on$'

 

SELECT *
FROM Customers
WHERE last_name REGEXP '^my|se'

 

SELECT *
FROM Customers
WHERE last_name REGEXP 'b[ru]'

IS NULL运算符 The IS NULL Operator

IS NULL运算符用于找出空值或某些属性缺失的记录

 

找出电话号码缺失的顾客。

USE sql_store;


SELECT * 
FROM customers
WHERE phone is null/is not null

注意是 IS NULL 和 IS NOT NULL 这里 NOT 不是前置于布林值，而是更符合英语语法地放在了be动词后

 

练习

找出还没发货的订单（在线商城管理员的常见查询需求）

USE sql_store;


SELECT * 
FROM orders
WHERE shipper_id is null

回顾

3~9 节全在讲WHERE子句中条件的具体写法 :

第3节：比较运算 > < = >= <= !=第4节：逻辑运算 AND、OR、NOT5~9节：特殊的比较运算（是否符合某种条件）：IN 和 BETWEEN、LIKE 和 REGEXP、IS NULL

所以总的来说WHERE条件就是

数学运算 → 比较运算（包括特殊的比较运算）→ 逻辑运算

逻辑层次和执行优先级也是按照这三个的顺序来的。

ORDER BY子句 The ORDER BY Clause

小结

排序语句，和 SELECT …… 很像：

可多列可以是列间的数学表达式可包括任何列，包括没选择的列（MySQL特性，其它DBMS可能报错），可以是之前定义好的别名列（MySQL特性，甚至可以是用一个常数设置的列别名）任何一个排序依据列后面都可选加 DESC

注意

最好别用 ORDER BY 1, 2（表示以 SELECT …… 选中列中的第1、2列为排序依据） 这种隐性依据，因为SELECT选择的列一变就容易出错，还是显性地写出列名作为排序依据比较好

注：workbench 中扳手图标可打开表格的设计模式，查看或修改表中各列（属性），可以看到谁是主键。省略排序语句的话会默认按主键排序

实例

USE sql_store;


SELECT name, unit_price * 1.1 + 10 AS new_price 
FROM products
ORDER by new_price desc, product_id
-- 这两个分别是 别名列 和 未选择列，都用到了 MySQL 特性

练习

订单2的商品按总价降序排列:

法1. 可以以总价的数学表达式为排序依据

SELECT *
FROM order_items 
WHERE order_id = 2
ORDER by quantity * unit_price desc
-- 列间数学表达式

法2. 或先定义总价别名，在以别名为排序依据

SELECT *, quantity * unit_price AS total_price 
FROM order_items 
WHERE order_id = 2
ORDER by total_price desc
-- 列别名

LIMIT子句 The LIMIT Clause

小结

限制返回结果的记录数量，“前N个” 或 “跳过M个后的前N个”

实例

USE sql_store;


select * from customers
limit 3 / 300 / 6, 3

6, 3 表示跳过前6个，取第7~9个，6是偏移量，

如：网页分页 每3条记录显示一页 第3页应该显示的记录就是 limit 6, 3

练习

找出积分排名前三的死忠粉

USE sql_store;


select *
from customers
order by points desc 
limit 3

回顾

SELECT 语句完结了，里面的子句顺序固定要记牢，顺序乱会报错

select from where + order by limit

纵选列，确定表，横选行（各种条件写法和组合要清楚熟悉），最后再进行排序和限制