CodeWithMosh学习笔记第二章

【第二章】在单一表格中检索数据

选择语句

-- 表示单行注释，后面有个空格

/*
多行注释
*/

-- sql关键字忽略大小写，但最好关键字用大写，其他用小写
USE sql_store;

SELECT *       -- "*"表示检索全部，也可以是检索常数等
FROM customers   -- 从customers表中检索

/*
SQL中换行符、大间隔和tab键在执行SQL语句时没有作用
SELECT * FROM customers也是可以的
但当语句复杂时，最好还是把每条子句放在新建行里
*/

-- WHERE customer_id = 1      -- WHERE"表示横向筛选行
ORDER BY first_name  -- 根据姓来排序

-- 注：语句顺序可能会产生语法错误

选择子句

SELECT   -- 选择特定列，查询出的顺序跟SELECT后的输入顺序一致
	last_name, 
	first_name, 
	(points + 10) * 100 AS discount_factor -- "AS"定义别名
	-- 如果别名要将空格，需要将别名加上单引号或双引号，如"discount factor"
-- 查询语句可以用算术表达式
FROM customers

-- 在表格中可以双击修改表内容，右下角有"Apply"和“Revert"两个键，可以应用和还原

SELECT DISTINCT state   -- 关键字"DISTINCT"，去除重复
FROM customers

练习：

-- 要求：Return all the products
-- 		name
-- 		unit price
-- 		new price(unit price * 1.1)

--  解答：
SELECT
	name,
    unit_price,
    unit_price * 1.1 AS new_price
FROM products

WHERE子句

SELECT *
FROM customers
-- WHERE points > 3000  --关键词"WHERE"
WHERE state <> 'VA'   -- VA在这里是字符串，要用引号，忽略大小写，用'va'也一样
-- WHERE birth_date > '1990-01-01'  查询生日1990-01-01以后的用户

运算符：

>:大于

>=:大于等于

<:小于

<=:小于等于

=:等于

!=:不等于

<>:不等于

练习：

-- 要求：Get the orders placed this year

-- 解答：
SELECT *
FROM orders
WHERE order_date >= "2019-01-01"

AND, OR, NOT运算符

SELECT *
FROM orders
WHERE order_date >= "2019-01-01" OR points > 1000 AND state = 'VA' 
-- "AND"相当于“且”, "OR"相当于“或者”
-- 优先级：AND > OR

练习：

-- 要求：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 quantity * unit_price > 30

IN运算符

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

练习：

-- 要求：Return products with
-- 			quantity in stock equal to 49, 38, 72

-- 解答：
SELECT *
FROM products
WHERE quantity_in_stock IN (49, 38, 72)

BETWEEN运算符

SELECT *
FROM customers
WHERE points BETWEEN 1000 AND 3000  -- 在...之间

练习：

-- 要求：Return customers born 
-- 			between 1/1/1990 and 1/1/2000

-- 解答：
SELECT *
FROM customers
WHERE birth_date BETWEEN '1990-01-01' AND '2000-01-01'

LIKE运算符

SELECT *
FROM customers
WHERE last_name LIKE 'b%'-- "LIKE"运算符检索遵循特定字符串模式的行
-- 查询以“B”开头姓氏，"B"之后可能有任意长度的字符串，用"%"表示任意字符
-- WHERE last_name LIKE '%b%'  表示任意包含"B"的姓氏
-- WHERE last_name LIKE '_____y'

-- '%' 任意长度字符
-- '_' 单个字符

练习：

-- 要求：Get the customers whose
-- 		addresses contain TRAIL or AVENUE

SELECT *
FROM customers
WHERE address LIKE '%TRAIL%' OR
	  address LIKE '%AVENUE%'     -- 注：要写完整表达式，否则是布尔值，没有算件判断

-- 		phone numbers end with 9
SELECT *
FROM customers
WHERE phone LIKE '%9'

REGEXP运算符（正则表达式）

• REGEXP : regular expression的缩写（正则表达式）

SELECT *
FROM customers
WHERE last_name REGEXP 'field'
-- WHERE last_name LIKE '%field%'  -- 两者相等
-- WHERE last_name REGEXP 'field|mac' -- 表示包含field或mac
-- '|'表示多个搜索模式

SELECT *
FROM customers
WHERE last_name REGEXP '^field'
-- "^":表示以field为开头
-- WHERE last_name REGEXP 'field$'
-- "$":表示以field为结尾

SELECT *
FROM customers
WHERE last_name REGEXP '[gim]e'  -- 满足包含方框内任意字母在e之前的姓，可以是'ge','ie', 'me'

SELECT *
FROM customers
WHERE last_name REGEXP '[a-h]e' -- 范围a-h的任意字母在e之前

总结：

-- ^ beginning开头
-- $ end末尾
-- | logical or或者
-- [abcd]任意字母
-- [a-]范围中任意字母

练习：

-- 要求：Get the customers whose
-- 			first names are ELKA or AMBUR
SELECT *
FROM customers
WHERE last_name = 'elka|ambur'

-- 			last names and with EY or ON
SELECT *
FROM customers
WHERE last_name REGEXP 'ey$|on$'

-- 			last names start with MY or contains SE
SELECT *
FROM customers
WHERE last_name REGEXP '^my|se'

-- 			last names contain B followed by R or U
SELECT *
FROM customers
WHERE last_name REGEXP 'b[ru]'   -- 'br|bu'

IS NULL 运算符

SELECT *
FROM customers
WHERE phone IS NULL

练习：

-- 要求：Get the orders that are not shipped

-- 解答：
SELECT *
FROM orders
WHERE shipped_date IS NULL

ORDER BY运算符

SELECT *
FROM customers
ORDER BY first_name DESC   -- 'DESC'表示降序，默认为升序
-- ORDER BY state DESC, first_name 可以多列排序

-- mysql比起其他数据库管理系统独有的特点是可以根据非查询列来排序
SELECT first_name, last_name
FROM customers
ORDER BY birth_date
-- 在mysql中有效，其他数据库管理系统中无效

SELECT first_name, last_name, 10 AS points
FROM customers
ORDER BY 1, 2   -- 序号对应查询列的顺序，即1--first_name, 2--last——name, 但尽量不要用这种方法

练习：

查询结果:

order_id	product_id	quantity	unit_price
2	1	2	9.10
2	4	4	1.66
2	6	2	2.94
NULL	NULL	NULL	NULL

详细要求：

只查询order_id为2的订单物品，根据product_id升序，在根据总价降序排列

SELECT *
FROM order_items
WHERE ORDER_ID = 2
ORDER BY product_id, quantity * unit_price DESC

LIMIT子句

SELECT *
FROM customers
LIMIT 3     -- 返回前3位顾客
-- 注：如果这里的条件超过记录数，就将返回全部记录

SELECT *
FROM customers
LIMIT 6, 3   -- 6为偏移量，即跳过前6条记录然后获取3条记录即获得7，8,9三条记录

练习：

-- Get the top three loyal customers

SELECT *
FROM customers
ORDER BY points DESC
LIMIT 3

-- 子句顺序：
SELECT
FROM
WHERE
ORDER
LIMIT  -- 'LIMIT'子句永远在最后
-- 顺序错，mysql会报错