-- 最好每个语句一行use sql_store
SELECT*FROM customers
WHERE customer_id =1ORDERBY first_name
-- 检索特定的列SELECT`name`,
unit_price,
unit_price*1.1as"new price"FROM products
-- 去重SELECTDISTINCT state
FROM customers
where子句
SELECT*from customers
-- WHERE state != 'va'-- WHERE points > 3000WHERE 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 <=100SELECT*FROM customers
WHERE birth_date >'1990-01-01'OR(points >100AND state ='VA')-- 寻找订单号为5并且总价格大于30的SELECT*FROM order_items
WHERE order_id =6AND(quantity * unit_price)>30
IN操作符
-- 简化or操作SELECT*FROM customers
WHERE state NOTIN('VA','FA','GA')-- 找出quantity_in_stock包含49或38或72SELECT*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'
-- 正则表达式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或roseWHERE 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 USELECT*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 ISNULL
Order By
-- ========分页 limit 与排序 ORDER BY===========-- 排序:升序ASC 降序 DESC-- ORDER BY-- 查询的结果根据bookid排序SELECT*FROM borrow
ORDERBY 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
ORDERBY bookid ASCLIMIT0,5;-- 排序补充-- 以state和first_name降序排列SELECT*FROM customers
ORDERBY state DESC, first_name DESC-- mysql特性:可以不查询那个元素,但是拿他来排序SELECT first_name,last_name
FROM customers
ORDERBY state DESC-- 自己添加一个里面没有的SELECT first_name,last_name,10ASpointFROM customers
ORDERBY state DESC-- 练习SELECT*, quantity * unit_price AS total_price
FROM order_items
WHERE order_id =2ORDERBY total_price DESC