Mosh Hamedani数据库学习笔记(2)【查询语句】

选择语句

-- 选择语句 
-- sql中有几条语句,用分号隔开
-- 子句 SELECE(查询),FROM,WHERE(筛选),ORDER BY(排序)  子句的顺序不能改变
-- 最好把每条子句放在新建的行里面

查询语句

--  查询语句
SELECT   first_name,
	last_name ,
	points,
	(points + 10) * 100   AS discount_factor
FROM customers
-- where customer_id = 1 
-- order by first_name
-- 给某列加上别名 用AS关键字
-- 别名的好处(可以给列和结果集一个描述性名称)
-- 给列名中加入空格 使用单引号或者双引号进行引用  eg:  "discount factor" 
-- 算数表达式中运算符的顺序 : x / + - 

-- 查询结果集里得到的一份洲的唯一列表,使用DISTINCT 关键字  所以使用distinct关键字可以删去重复项

SELECT  DISTINCT state
FROM customers
-- 拆分子句,把每列放到新的一行

-- eg 1: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子句

-- where子句  用于筛选数据

SELECT  *
FROM customers
WHERE points > 3000



-- 通常把数值用单引号封上,大小写无所谓

SELECT *
FROM customers
WHERE birth_date > '1990-01-01'  -- MySQL默认的日期表述形式


WHERE state = 'va' -- state = 'VA'
WHERE state != 'va'
-- 比较运算符
-- > , >= , < ,<= , = < != , <>

-- eg Get the orders placed this year 

-- solution(答案)
SELECT *
FROM orders
WHERE order_date >= '2019-01-01'

AND OR 和 NOT运算符

-- AND  OR 和 NOT运算符(operators)
-- 筛选数据时结合多条搜索条件 
-- 合并多个逻辑运算符时,需要注意运算符的顺序   优先级  AND > OR
SELECT *
FROM customers

-- 获取1990年出生或者至少有1000积分 并且位于va的顾客  带个括号
WHERE birth_date > '1990-01-01' OR 
	(points > 1000  AND state = 'va')
	--  加个括号,可以让读代码的人很容易理解查询目的

--   AND  获取生日在1990年1月1日后出生的顾客并且记分超过1000   两个条件必须都正确
WHERE birth_date > '1990-01-01' AND points > 1000

-- OR 只要有一个条件满足

WHERE birth_date > '1990-01-01' OR points > 1000

-- NOT 用于否定一个条件
-- 用NOT时,最好在条件外面加个括号
-- 查询不在当前结果集的顾客


SELECT *
FROM customers 
-- 在1990年前出生并且少于1000积分
WHERE NOT  (birth_date > '1990-01-01' OR points > 1000)
-- 在1990年前出生并且少于1000积分
WHERE bitrh_date <='1990-01-01' AND points <=1000

-- Exercise(练习)
-- FOR 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



-- OR 运算符合并多个条件 

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

IN运算符

--  IN 运算符

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


WHERE state IN ('GA','VA','FL')

-- Exercise

-- Return products with
-- quantity instock equal to 49,38 ,73

-- solution
SELECT *
FROM products
WHERE quantity_in_stock IN (49,38,72)

BETWEEN运算符

--  BETWEEN运算符 

SELECT *
FROM customers
WHERE points >= 1000 AND points <= 3000
WHERE prints BETWEEN 1000 AND 3000

-- Exercise
-- Return customers born
--		between 1/1/1990 and 1/1/2000

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

LIKE运算符

-- LIKE运算符 检索遵循特定字符串模式的行


SELECT *
FROM customers



WHERE last_name LIKE 'Brush%'
-- 获取B开头姓氏的顾客,而B之后有任意长度的字符 , B大小写无所谓
-- 这个%不一定非得在模式的末尾,它可以摆在任何地方
-- %b%  表示b的前后可以有任意字符数
WHERE last_name LIKE 'B%'
-- 下划线 _ 代表一个单字符
WHERE last_name LIKE '_____y'

-- % any number of characters
-- _ single character


-- Exercise 

--  Get the customers whose
-- addresses contain TRAIL OR AVENUE
-- phone numerous end with 9

-- solution
SELECT *
FROM customers
-- 获取顾客地址包含'trail' 或者 'avenue'
WHERE address LIKE '%trail%' OR
      address LIKE '%avenue%' 
-- 获取手机号以9结尾的顾客
WHERE phone LIKE '%9'

正则表达式(多看)

-- REGEXP运算符  regular expression(正则表达式的缩写)
-- 搜索字符串时十分强大,允许搜索更复杂的模式 

SELECT *
FROM customers
-- where last_name like '%field%'
WHERE last_name REGEXP 'field|mac'

-- 如果在field前放一个  ^   表示姓氏必须以field开头
-- 以美元符号$代表字符串末尾  表示姓氏必须以field结尾

-- 查询一些顾客 姓氏包含field或者mac

-- 如何在创建复杂模式时,合并多个特殊字符

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

-- 假设搜寻姓氏中有"e"的顾客
SELECT *
FROM customers
WHERE last_name REGEXP 'e'

-- 确保在字母"e"前面有"g"和"i"的两者之一
SELECT *
FROM customers
WHERE last_name REGEXP '[gim]e'

-- 查询结果:对应任何姓氏中有ge/ie/me的顾客

-- 注意 [] 可以放在任何地方表示不同含义

-- 在e后面有一段范围,eg:a-h的任意字母

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

-- ^ beginning   字符串开头
-- $ end 	 字符串结尾
-- | logical or  逻辑上的OR,这样就可以给出多个搜索模式
-- [abcd]  	 使用[]匹配仁义在括号里列举的单字符
-- [a-h]         使用[]和一个-代表一个范围  eg:a-h


-- Excrise
 -- Get the customers whose
 --  first names are ELKA or AMBUR
 
-- 获取名字是ELKA或者Ambur的顾客

SELECT *
FROM customers
WHERE first_name REGEXP 'elka|ambur'

 --  last names end with EY or ON
 -- 后缀姓氏以ry或者on结尾的顾客
 
SELECT *
FROM customers
-- 错误: WHERE last_name regexp '$ey|$on'
WHERE last_name REGEXP 'ey$|on$'
 
 
 --  last names startwith MY or contains SE
 -- 姓氏以my开头或者包含se的顾客
 SELECT *
FROM customers
WHERE last_name REGEXP '^my|se'
 
 --  last names contain B followed by R or U
 -- 后缀姓氏包含B接着r或者u
 SELECT *
FROM customers
WHERE last_name REGEXP 'b[ru]' -- 'br|bu'

IS NULL 运算符

-- IS NULL 运算符
-- 掌握如何搜索缺失了属性的记录
-- NULL意味着值缺失

-- eg 搜索没有电话号码的顾客

SELECT * 
FROM customers 
WHERE phone IS NULL
-- WHERE phone IS NOT NULL 

-- Exercise
-- Get the orders that are not shipped
-- 写一段查询获取所有还没发货的订单

-- Solution 

SELECT *
FROM orders
WHERE shipped_date IS NULL 
WHERE shipper_id IS NULL

ORDER BY子句

-- ORDER BY 子句
-- 如何用SQL查询为数据排序


SELECT * 
FROM customers 
ORDER BY first_name DESC -- 降序排序

-- 用多列给数据排序
-- eg 首先根据洲给顾客排序, 然后在每个洲里用名字排序


SELECT * 
FROM customers 
ORDER BY state DESC, first_name DESC
-- MySQL中可以用任何列排序数据,不管那列是不是在SELECT子句中
-- eg (也可以用别名排序,复杂的表达式也可以)
-- 其他数据库会出错

SELECT first_name , last_name , 10 AS points
FROM customers 
ORDER BY birth_date

-- Exercise
-- 选择所有order_id为2的项目,总价格按照降序排列

-- solution
SELECT * ,quantity * unit_price AS total_price
FROM order_items
WHERE order_id = 2
ORDER BY total_price  DESC

LIMIT子句(偏移量offset–涉及数据分页)

-- LIMIT 子句
-- 如何限定查询返回的记录

-- 查询前三个顾客的信息
SELECT *
FROM customers
-- 选择性添加一个偏移量(offset),
-- 在你想要给数据分页的时候会很有用
LIMIT 3
-- 如果这里的条件比查询返回的记录数还要多的话,就将返回全部数据
-- page 1: 1 - 3
-- page 2: 4 - 6
-- page 3: 7 - 9
-- 假设想要写查询获取第3页的顾客,怎么做呢?
-- 可以跳过前6条记录然后选择3条记录
SELECT *
FROM customers
LIMIT 6,3 
-- 6被称为偏移量
-- 也就是告诉了MySQL要跳过前6条记录然后获取3条记录
-- 获得的信息是 7,8,9

-- Excrise
-- Get the top three loyal customers
-- 获取排名前三的忠实顾客(有最多积分的顾客)

-- solution
SELECT *
FROM customers
ORDER BY points DESC
LIMIT 3
-- LIMIT子句永远要放在最后

-- 查询子句顺序 SELECT, FROM, WHERE, ORDER BY , LIMIT