第二章 选择语句

第二章 选择语句

p7 选择语句√

在单一表格中检索数据。

USE sql_store;
-- sql是不区分大小写的语言，但最好的做法是大写关键字。
SELECT *   -- 纵向筛选列
FROM customers  -- 选择表
WHERE customer_id < 4  -- 横向筛选行
ORDER BY first_name;  -- 排序

-- 单行注释

/*
多行注释
*/

p8 选择子句√

如果返回全部数据，会给MySQL和网络带来很大负担，因此可以使用select返回指定列。

-- 使用select返回指定列
-- 可以对列使用算数表达式
SELECT last_name, first_name, points + 10 -- 可以根据积分计算折扣力度
From customers;

last_name, first_name, points, points + 10
'MacCaffrey', 'Babara', '2273', '2283'

-- 可以用AS关键字设置列别名
SELECT 
	last_name, 
	first_name, 
	points, 
	points * 0.01 as discount_factor -- as可省略
	-- 如果列别名要使用空格那么要使用单引号或者双引号 
	-- points * 0.01 as 'discount factor'
From customers;

# last_name, first_name, points, discount_factor
Brushfield, Ines, 947, 9.47

-- 去重 注意，如果选择了两列，那么只有当两个字段都相同时才会去重
SELECT Distinct first_name, state
From customers;

exercise

SELECT name, 
unit_price, 
unit_price * 1.1 'new price'
From products;

p9 where子句√！（区分大小写问题）

WHERE 是行筛选条件，实际是一行一行\一条条记录依次验证是否符合条件，进行筛选。

SELECT * 
FROM customers
where 
	points > 2000 and  -- 比较运算符 > , >= , < , <= , = , != 、<>
	state = 'VA' and -- 每当要处理字符序列时，要用单引号或双引号  !!不区分大小写
	birth_date > '1980-01-01'; -- 日期虽然不是字符串但也要加引号

如果想要区分大小写，有三种办法：

• 查询语句上加binary：

  USE sql_store;
  SELECT first_name, last_name, state 
  FROM customers
  where 
  	binary state = 'va';
  

• 建表时加binary：

  create table t {
      code varchar(10)  binary
  }
  ALTER TABLE t_order CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
  

exercise

-- Get the order placed this year
SELECT * 
FROM orders
where 
    order_date > '2019-01-01';

p10 AND, OR, NOT 运算符√

用逻辑运算符AND、OR、NOT对（数学和）比较运算进行组合实现多重条件筛选
执行优先级：括号 > * / > + - > 比较运算符 > NOT > AND > OR

SELECT *
FROM customers
WHERE 
	NOT (birth_date > '1990-01-01' OR points > 1000)
	--等价于
	-- WHERE birth_date <= '1990-01-01' AND points <= 1000

exercise

-- 订单6中总价大于30的商品
USE sql_store;

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

P11 IN运算符√

用IN运算符将某一属性与多个值（一系列值）进行比较
实质是多重相等比较运算条件的简化。

举例：选出’va’、‘fl’、'ga’三个州的顾客。

USE sql_store;
SELECT * FROM customers
WHERE state = 'va' OR state = 'fl' OR state = 'ga';

• 不能 state = 'va' OR 'fl' OR 'ga' 因为数学和比较运算优先于逻辑运算;
• 加括号 state = ('va' OR 'fl' OR 'ga') 也不行，SQL中逻辑运算符只能连接布尔值。

-- 用 IN 操作符简化该条件
USE sql_store;

SELECT * FROM customers
WHERE state IN ('va', 'fl', 'ga');
-- 同时可以加NOT
WHERE state NOT IN ('va', 'fl', 'ga')

练习

-- 库存量刚好为49、38或72的产品
USE sql_store;

select * from products
where quantity_in_stock in (49, 38, 72)；

p12 between运算符√

用于表达范围型条件

注意

• 用AND而非括号
• 闭区间，包含两端点
• 也可用于日期，毕竟日期本质也是数值，日期也有大小（早晚），可比较运算
• 同 IN 一样，BETWEEN 本质也是一种特定的 多重比较运算条件 的简化

案例

-- 选出积分在1k到3k的顾客
USE sql_store;

select * from customers
where points >= 1000 and points <= 3000;

等效简化为：

-- 选出积分在1k到3k的顾客
USE sql_store;

select * from customers
where points BETWEEN 1000 AND 3000

练习

-- 选出90后的顾客
SELECT * 
FROM customers
WHERE birth_date BETWEEN '1990-01-01' AND '2000-01-01'

p13 LIKE运算符√

模糊查找，查找具有某种模式的字符串的记录/行。

注意

• 过时用法（但有时还是比较好用，之后发现好像用的还是比较多的），下节课的正则表达式更灵活更强大。
• 注意和正则表达式一样都是用引号包裹表示字符串。

USE sql_store;

SELECT * FROM customers
WHERE last_name like 'brush%' / 'b____y'

两种通配符：

• % 任何个数（包括0个）的字符（用的更多）
• _ 单个字符

练习

-- 地址包含 'TRAIL' 或 'AVENUE'
USE sql_store;

select * 
from customers
where address like '%Trail%' or 
      address like '%avenue%';

-- 电话号码以 9 结束

USE sql_store;

select * 
from customers
where phone like '%9'
      

p14 REGEXP运算符（Regular Expression 正则表达式）√

正则表达式，在搜索字符串方面更为强大，可搜索更复杂的模板。

实例

USE sql_store;

select * from customers
where last_name like '%field%'; -- 等效于
where last_name REGEXP 'field';

-- 用^表示必须以接下来的字符串作为开头 e.g. ^abc
-- 用$表示必须以接下来的字符串作为结尾e.g. abc$
-- where last_name REGEXP 'field|mac'; 包含'field' or 'mac'
-- REGEXP '^field|mac|rose'; 以field开头 or 包含'field' or 'mac'
-- REGEXP '[gim]e'; 查找last_name中有'e'，且e前面必须有'g' or 'i' or 'm'
-- REGEXP '[a-h]e'; []中可以提供字符范围,'e'前面可以出现'a'-'h'中的任意字符

练习

分别选择满足如下条件的顾客：

1. first names 包含 ELKA 或 AMBUR；

2. last names 以 EY 或 ON 结束；

3. last names 以 MY 开头 或包含 SE；

4. last names 包含 BR 或 BU；

select * 
from customers
where first_name regexp 'elka|ambur';
-- where last_name regexp 'ey$|on$';
-- where last_name regexp '^my|se';
-- where last_name regexp 'b[ru]'/'br|bu';

p15 IS NULL 运算符√

找出有某些属性缺失的记录。

实例

找出电话号码缺失的顾客，也许发个邮件提醒他们之类的。

USE sql_store;

select * from customers
where phone is NULL;
--where phone is NOT NULL; 找到不空的号码

练习

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

USE sql_store;

select * from orders
where shipper_id IS NULL;

P16 ORDER BY 子句√

默认是按照主键递增排序的，但不一定总是。

MySQL SELECT 默认排序是按照物理存储顺序显示。（不进行额外排序）也就是说SELECT * FROM 会产生“表扫描”。如果表没有增，删，改操作，记录会显示为插入的顺序。这就是初始为什么像是递增的原因。

那么，当我们进行增，删，改以后不难发现会乱序，这便是问题所在。增删改是惯用功能，因此必须注意这个细节。当我们不进行ORDER BY来规定排序时，MySQL将会以最快的形式（物理存储顺序）展示数据，导致乱序。

USE sql_store;

select * 
FROM customers
ORDER BY first_name; 
-- 默认升序 想要降序的话 + DESC descend
ORDER BY first_name DESC;

-- 如果想要多列排序 比如先对用户所在州降序排序，在一个州内在对用户升序排序：
select * 
FROM customers
ORDER BY state DESC, first_name;

-- MYSQL中可以用任何列排序数据 不管那列是不是在SELECT子句中。(其他DBSM中会报错)
-- 举例 只显示first_name 和 last_name，但是根据birth_date排序
select first_name , last_name
FROM customers
ORDER BY birth_date;

-- 避免用数字代替列！因为如果将来表的结构变了，那么数字指代的列会失效！

练习

-- 查询order_id为2的订单，按照总收入降序排序
select *, quantity * unit_price as total_price
FROM order_items
where order_id = 2
ORDER BY quantity * unit_price DESC;

p17 LIMIT子句 √

select *, quantity * unit_price total_price
FROM order_items
where order_id = 2
ORDER BY quantity * unit_price DESC
LIMIT 2; -- 只显示两条

/*
偏移量的作用：假设现在我要向用户分页展示数据，每页展示3个：
page 1： 1 - 3
page 2： 4 - 6
page 3： 7 - 9
那么当前要展示第三页的话：
*/
SELECT * 
FROM customers
LIMIT 6, 3; -- 6就是偏移量，告诉MYSQL要跳过前6条记录然后获取三条记录

练习

-- get the top three loyal customers
SELECT * 
FROM customers
order by points DESC
limit 3;