数据库学习---单表查询

单一表中检索数据

Select子句

-- 最好每个语句一行
use sql_store

SELECT *
FROM customers
WHERE customer_id = 1
ORDER BY first_name

-- 检索特定的列
SELECT 
	`name`,
	unit_price,
	unit_price*1.1 as "new price"
FROM products

-- 去重
SELECT DISTINCT state 
FROM customers

where子句

SELECT *
from customers
-- WHERE state != 'va'
-- WHERE points > 3000
WHERE birth_date > '1990-01-02'

-- 查询今年所有订单
SELECT *
FROM orders
WHERE shipped_date > '2018-01-01'

and、or、not

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

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

-- 寻找订单号为5并且总价格大于30的
SELECT * 
FROM order_items
WHERE order_id = 6 AND (quantity * unit_price) > 30

IN操作符

-- 简化or操作
SELECT *
FROM customers
WHERE state NOT IN ('VA','FA','GA')

-- 找出quantity_in_stock包含49或38或72
SELECT *
FROM products
WHERE quantity_in_stock IN (49,38,72)

BETWEEN（范围）

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

LIKE（% _）

-- %:无限长度	_:一个长度
SELECT *
FROM customers
WHERE address LIKE '%trail%' or '%avenue%'	-- 包含有这两个字符串的的地址
WHERE phone NOT LIKE '%9'		-- 不以9结尾的电话
WHERE last_name LIKE 'b____y'		-- 包含6个字符,以b开头，y结尾

REGEHP（正则表达式查询）

-- 正则表达式
SELECT *
FROM customers
-- WHERE last_name LIKE '%field%' 
-- 包含filed即可:等同于
WHERE last_name REGEXP 'field'
-- ^:表示以这里开始		这里表示名字必须以field开头
WHERE last_name REGEXP '^field'
-- $:表示以这里结尾		这里表示名字必须以field结尾
WHERE last_name REGEXP 'field$'
-- |:以指定多种种检索条件		这里表示带有field或mac或rose
WHERE last_name REGEXP 'field|mac|rose'
-- 组合使用
WHERE last_name REGEXP '^field|mac|rose$'
-- 前面的指定:这里找到的是包含ge、ie、或me的 
WHERE last_name REGEXP '[gim]e'
-- 同理放后面
WHERE last_name REGEXP 'e[gim]'
WHERE last_name REGEXP '[a-h]e'

-- 练习
-- 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 first_name REGEXP 'elka|ambur'
WHERE last_name REGEXP 'ey$|on$'
WHERE last_name REGEXP '^my|se'
WHERE last_name REGEXP 'b[ru]'

IS NULL（检索缺少数据）

SELECT *
FROM orders
WHERE shipper_id IS NULL

Order By

-- ========分页 limit 与排序 ORDER BY===========

-- 排序：升序ASC 降序 DESC
-- ORDER BY
-- 查询的结果根据bookid排序
SELECT * FROM borrow
ORDER BY bookid ASC

-- 分页：limit 起始位置，页面的限制条数
-- 第1页:LIMIT 0,5 (1-1)*5
-- 第2页:LIMIT 5,5 (2-1)*5
-- 公式
-- 第n页:LIMIT n,5 
-- (n-1)*pageSize,pageSize
-- n:当前页
-- 总页数:数据总数除以页面大小
SELECT * FROM borrow
ORDER BY bookid ASC
LIMIT 0,5;


-- 排序补充
-- 以state和first_name降序排列
SELECT * 
FROM customers
ORDER BY state DESC, first_name DESC

-- mysql特性:可以不查询那个元素，但是拿他来排序
SELECT first_name,last_name
FROM customers
ORDER BY state DESC

-- 自己添加一个里面没有的
SELECT first_name,last_name,10 AS point
FROM customers
ORDER BY state DESC

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

LIMIT

SELECT *
FROM customers
-- 第一个数:偏移量:表示跳过前6个数据，然后返回3条记录
LIMIT 6,3

-- page 1:1 - 3
-- page 2:4 - 6
-- page 3:7 - 9

SELECT *
FROM customers
ORDER BY points DESC
LIMIT 3

查找顺序

select *
from xxx
where xxx
order by xxx
limit xxx