CodeWithMosh-SQL进阶课程-第一章（单表检索）
1.1、选择语句
mysql> USE sql_store; -- 进入指定库内
Database changed
mysql> SELECT DATABASE(); -- 查看当前所在库
+------------+
| DATABASE() |
+------------+
| sql_store |
+------------+
mysql> SELECT * FROM customers; -- 查询表中所有数据
+-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+
| 1 | Babara | MacCaffrey | 1986-03-28 | 781-932-9754 | 0 Sage Terrace | Waltham | MA | 2273 |
| 2 | Ines | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton | VA | 947 |
| 3 | Freddi | Boagey | 1985-02-07 | 719-724-7869 | 251 Springs Junction | Colorado Springs | CO | 2967 |
| 4 | Ambur | Roseburgh | 1974-04-14 | 407-231-8017 | 30 Arapahoe Terrace | Orlando | FL | 457 |
| 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 |
| 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 |
| 7 | Ilene | Dowson | 1964-08-30 | 615-641-4759 | 50 Lillian Crossing | Nashville | TN | 1672 |
| 8 | Thacher | Naseby | 1993-07-17 | 941-527-3977 | 538 Mosinee Center | Sarasota | FL | 205 |
| 9 | Romola | Rumgay | 1992-05-23 | 559-181-3744 | 3520 Ohio Trail | Visalia | CA | 1486 |
| 10 | Levy | Mynett | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue | Atlanta | GA | 796 |
+-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+
mysql> SELECT * FROM customers WHERE customer_id = 1; -- 通过条件查询匹配的数据
+-------------+------------+------------+------------+--------------+----------------+---------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+-------------+------------+------------+------------+--------------+----------------+---------+-------+--------+
| 1 | Babara | MacCaffrey | 1986-03-28 | 781-932-9754 | 0 Sage Terrace | Waltham | MA | 2273 |
+-------------+------------+------------+------------+--------------+----------------+---------+-------+--------+
mysql> SELECT * FROM customers ORDER BY first_name; -- 根据 first_name 列对数据进行排序显示
+-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+
| 4 | Ambur | Roseburgh | 1974-04-14 | 407-231-8017 | 30 Arapahoe Terrace | Orlando | FL | 457 |
| 1 | Babara | MacCaffrey | 1986-03-28 | 781-932-9754 | 0 Sage Terrace | Waltham | MA | 2273 |
| 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 |
| 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 |
| 3 | Freddi | Boagey | 1985-02-07 | 719-724-7869 | 251 Springs Junction | Colorado Springs | CO | 2967 |
| 7 | Ilene | Dowson | 1964-08-30 | 615-641-4759 | 50 Lillian Crossing | Nashville | TN | 1672 |
| 2 | Ines | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton | VA | 947 |
| 10 | Levy | Mynett | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue | Atlanta | GA | 796 |
| 9 | Romola | Rumgay | 1992-05-23 | 559-181-3744 | 3520 Ohio Trail | Visalia | CA | 1486 |
| 8 | Thacher | Naseby | 1993-07-17 | 941-527-3977 | 538 Mosinee Center | Sarasota | FL | 205 |
+-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+
语句执行顺序：SELECT --> FROM --> WHERE --> ORDER BY
可以去除 WHERE 直接执行 ORDER BY ， 但是顺序不能发生变化，不然会发生语法错误；
1.2、选择子句
mysql> SELECT last_name,first_name FROM customers; -- 选择指定列进行查询
+------------+------------+
| last_name | first_name |
+------------+------------+
| MacCaffrey | Babara |
| Brushfield | Ines |
| Boagey | Freddi |
| Roseburgh | Ambur |
| Betchley | Clemmie |
| Twiddell | Elka |
| Dowson | Ilene |
| Naseby | Thacher |
| Rumgay | Romola |
| Mynett | Levy |
+------------+------------+
mysql> SELECT last_name,first_name,points,points+10 FROM customers; -- 对某一列进行计算操作
+------------+------------+--------+-----------+
| last_name | first_name | points | points+10 |
+------------+------------+--------+-----------+
| MacCaffrey | Babara | 2273 | 2283 |
| Brushfield | Ines | 947 | 957 |
| Boagey | Freddi | 2967 | 2977 |
| Roseburgh | Ambur | 457 | 467 |
| Betchley | Clemmie | 3675 | 3685 |
| Twiddell | Elka | 3073 | 3083 |
| Dowson | Ilene | 1672 | 1682 |
| Naseby | Thacher | 205 | 215 |
| Rumgay | Romola | 1486 | 1496 |
| Mynett | Levy | 796 | 806 |
+------------+------------+--------+-----------+
# 对于列的计算可以分为：加减乘除（+-*/）、取余数（%）
mysql> SELECT last_name,first_name,points,points+10 FROM customers LIMIT 1;
+------------+------------+--------+-----------+
| last_name | first_name | points | points+10 |
+------------+------------+--------+-----------+
| MacCaffrey | Babara | 2273 | 2283 |
+------------+------------+--------+-----------+
mysql> SELECT last_name,first_name,points,points-10 FROM customers LIMIT 1;
+------------+------------+--------+-----------+
| last_name | first_name | points | points-10 |
+------------+------------+--------+-----------+
| MacCaffrey | Babara | 2273 | 2263 |
+------------+------------+--------+-----------+
mysql> SELECT last_name,first_name,points,points*10 FROM customers LIMIT 1;
+------------+------------+--------+-----------+
| last_name | first_name | points | points*10 |
+------------+------------+--------+-----------+
| MacCaffrey | Babara | 2273 | 22730 |
+------------+------------+--------+-----------+
mysql> SELECT last_name,first_name,points,points/10 FROM customers LIMIT 1;
+------------+------------+--------+-----------+
| last_name | first_name | points | points/10 |
+------------+------------+--------+-----------+
| MacCaffrey | Babara | 2273 | 227.3000 |
+------------+------------+--------+-----------+
mysql> SELECT last_name,first_name,points,points%10 FROM customers LIMIT 1;
+------------+------------+--------+-----------+
| last_name | first_name | points | points%10 |
+------------+------------+--------+-----------+
| MacCaffrey | Babara | 2273 | 3 |
+------------+------------+--------+-----------+
-- 根据括号调整运算顺序
mysql> SELECT last_name,first_name,points,(points+10)*10 FROM customers LIMIT 1;
+------------+------------+--------+----------------+
| last_name | first_name | points | (points+10)*10 |
+------------+------------+--------+----------------+
| MacCaffrey | Babara | 2273 | 22830 |
+------------+------------+--------+----------------+
---- 通过 AS 给列取别名
mysql> SELECT last_name,first_name,points,(points+10)*10 AS discount_factor FROM customers LIMIT 1;
+------------+------------+--------+-----------------+
| last_name | first_name | points | discount_factor |
+------------+------------+--------+-----------------+
| MacCaffrey | Babara | 2273 | 22830 |
+------------+------------+--------+-----------------+
-- 更改 customer_id 等于 1 的数据行的 state 字段为 VA
mysql> UPDATE customers SET state = 'VA' WHERE customer_id = 1;
mysql> SELECT * FROM customers WHERE customer_id = 1;
+-------------+------------+------------+------------+--------------+----------------+---------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+-------------+------------+------------+------------+--------------+----------------+---------+-------+--------+
| 1 | Babara | MacCaffrey | 1986-03-28 | 781-932-9754 | 0 Sage Terrace | Waltham | VA | 2273 |
+-------------+------------+------------+------------+--------------+----------------+---------+-------+--------+
-- 使用 DISTINCT 对字段进行去重操作，取出该字段每个值的一个
mysql> SELECT state FROM customers;
+-------+
| state |
+-------+
| VA |
| VA |
| CO |
| FL |
| TX |
| IL |
| TN |
| FL |
| CA |
| GA |
+-------+
mysql> SELECT DISTINCT state FROM customers;
+-------+
| state |
+-------+
| VA |
| CO |
| FL |
| TX |
| IL |
| TN |
| CA |
| GA |
+-------+
算数表达式中运算符的顺序，是基于数学中的运算符次序，如果不一样可以用括号改变次序
练习题： 在 products 表中写一段 SQL 查询，返回数据库的所有产品，结果集中只需要 name,unit price 和叫 new price 的新的一列，并且 new price 需要进行运算处理 unit price*1.1
-- 练习题 SQL
mysql> SELECT name,unit_price AS 'unit price',(unit_price*1.1) AS 'new price' FROM products;
+------------------------------+------------+-----------+
| name | unit price | new price |
+------------------------------+------------+-----------+
| Foam Dinner Plate | 1.21 | 1.331 |
| Pork - Bacon,back Peameal | 4.65 | 5.115 |
| Lettuce - Romaine, Heart | 3.35 | 3.685 |
| Brocolinni - Gaylan, Chinese | 4.53 | 4.983 |
| Sauce - Ranch Dressing | 1.63 | 1.793 |
| Petit Baguette | 2.39 | 2.629 |
| Sweet Pea Sprouts | 3.29 | 3.619 |
| Island Oasis - Raspberry | 0.74 | 0.814 |
| Longan | 2.26 | 2.486 |
| Broom - Push | 1.09 | 1.199 |
+------------------------------+------------+-----------+
1.3、WHERE 子句
mysql> SELECT * FROM customers WHERE points > 3000; -- 使用比较运算符对 points 字段进行过滤
+-------------+------------+-----------+------------+--------------+----------------+-----------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+-------------+------------+-----------+------------+--------------+----------------+-----------+-------+--------+
| 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 |
| 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 |
+-------------+------------+-----------+------------+--------------+----------------+-----------+-------+--------+
> 比较运算符包括：大于（>），大于等于（>=），小于（<），小于等于（<=），等于（=），不等于（!= 或者 <>）
---
**练习题：** 使用 orders 表，写段 SQL 查询语句，用来得到2018年下的订单都有哪些
```SQL
mysql> SELECT * FROM orders;
+----------+-------------+------------+--------+-----------------------------------------------------------------------+--------------+------------+
| order_id | customer_id | order_date | status | comments | shipped_date | shipper_id |
+----------+-------------+------------+--------+-----------------------------------------------------------------------+--------------+------------+
| 1 | 6 | 2019-01-30 | 1 | NULL | NULL | NULL |
| 2 | 7 | 2018-08-02 | 2 | NULL | 2018-08-03 | 4 |
| 3 | 8 | 2017-12-01 | 1 | NULL | NULL | NULL |
| 4 | 2 | 2017-01-22 | 1 | NULL | NULL | NULL |
| 5 | 5 | 2017-08-25 | 2 | | 2017-08-26 | 3 |
| 6 | 10 | 2018-11-18 | 1 | Aliquam erat volutpat. In congue. | NULL | NULL |
| 7 | 2 | 2018-09-22 | 2 | NULL | 2018-09-23 | 4 |
| 8 | 5 | 2018-06-08 | 1 | Mauris enim leo, rhoncus sed, vestibulum sit amet, cursus id, turpis. | NULL | NULL |
| 9 | 10 | 2017-07-05 | 2 | Nulla mollis molestie lorem. Quisque ut erat. | 2017-07-06 | 1 |
| 10 | 6 | 2018-04-22 | 2 | NULL | 2018-04-23 | 2 |
+----------+-------------+------------+--------+-----------------------------------------------------------------------+--------------+------------+
mysql> SELECT * FROM orders WHERE order_date >= '2018-01-01' AND order_date <= '2018-12-31';
+----------+-------------+------------+--------+-----------------------------------------------------------------------+--------------+------------+
| order_id | customer_id | order_date | status | comments | shipped_date | shipper_id |
+----------+-------------+------------+--------+-----------------------------------------------------------------------+--------------+------------+
| 2 | 7 | 2018-08-02 | 2 | NULL | 2018-08-03 | 4 |
| 6 | 10 | 2018-11-18 | 1 | Aliquam erat volutpat. In congue. | NULL | NULL |
| 7 | 2 | 2018-09-22 | 2 | NULL | 2018-09-23 | 4 |
| 8 | 5 | 2018-06-08 | 1 | Mauris enim leo, rhoncus sed, vestibulum sit amet, cursus id, turpis. | NULL | NULL |
| 10 | 6 | 2018-04-22 | 2 | NULL | 2018-04-23 | 2 |
+----------+-------------+------------+--------+-----------------------------------------------------------------------+--------------+------------+
1.4、AND,OR,NOT 运算符
-- AND 运算符，通过多个字段条件进行过滤
mysql> SELECT * FROM customers WHERE birth_date > '1990-01-01' AND points > 1000;
+-------------+------------+-----------+------------+--------------+-----------------+---------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+-------------+------------+-----------+------------+--------------+-----------------+---------+-------+--------+
| 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 |
| 9 | Romola | Rumgay | 1992-05-23 | 559-181-3744 | 3520 Ohio Trail | Visalia | CA | 1486 |
+-------------+------------+-----------+------------+--------------+-----------------+---------+-------+--------+
-- OR 运算符，多个字段条件，有一个符合即返回数据
mysql> SELECT * FROM customers WHERE birth_date > '1990-01-01' OR points > 1000;
+-------------+------------+------------+------------+--------------+----------------------+------------------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+-------------+------------+------------+------------+--------------+----------------------+------------------+-------+--------+
| 1 | Babara | MacCaffrey | 1986-03-28 | 781-932-9754 | 0 Sage Terrace | Waltham | VA | 2273 |
| 3 | Freddi | Boagey | 1985-02-07 | 719-724-7869 | 251 Springs Junction | Colorado Springs | CO | 2967 |
| 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 |
| 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 |
| 7 | Ilene | Dowson | 1964-08-30 | 615-641-4759 | 50 Lillian Crossing | Nashville | TN | 1672 |
| 8 | Thacher | Naseby | 1993-07-17 | 941-527-3977 | 538 Mosinee Center | Sarasota | FL | 205 |
| 9 | Romola | Rumgay | 1992-05-23 | 559-181-3744 | 3520 Ohio Trail | Visalia | CA | 1486 |
+-------------+------------+------------+------------+--------------+----------------------+------------------+-------+--------+
-- NOT Y运算符，否定字段条件进行过滤数据
mysql> SELECT * FROM customers WHERE birth_date > '1990-01-01' OR points > 1000;
+-------------+------------+------------+------------+--------------+----------------------+------------------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+-------------+------------+------------+------------+--------------+----------------------+------------------+-------+--------+
| 1 | Babara | MacCaffrey | 1986-03-28 | 781-932-9754 | 0 Sage Terrace | Waltham | VA | 2273 |
| 3 | Freddi | Boagey | 1985-02-07 | 719-724-7869 | 251 Springs Junction | Colorado Springs | CO | 2967 |
| 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 |
| 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 |
| 7 | Ilene | Dowson | 1964-08-30 | 615-641-4759 | 50 Lillian Crossing | Nashville | TN | 1672 |
| 8 | Thacher | Naseby | 1993-07-17 | 941-527-3977 | 538 Mosinee Center | Sarasota | FL | 205 |
| 9 | Romola | Rumgay | 1992-05-23 | 559-181-3744 | 3520 Ohio Trail | Visalia | CA | 1486 |
+-------------+------------+------------+------------+--------------+----------------------+------------------+-------+--------+
mysql> SELECT * FROM customers WHERE NOT ( birth_date > '1990-01-01' OR points > 1000 );
+-------------+------------+------------+------------+--------------+------------------------+---------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+-------------+------------+------------+------------+--------------+------------------------+---------+-------+--------+
| 2 | Ines | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton | VA | 947 |
| 4 | Ambur | Roseburgh | 1974-04-14 | 407-231-8017 | 30 Arapahoe Terrace | Orlando | FL | 457 |
| 10 | Levy | Mynett | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue | Atlanta | GA | 796 |
+-------------+------------+------------+------------+--------------+------------------------+---------+-------+--------+
运算符优先顺序：AND 优先于 OR 进行执行，如果需要进行调整运算顺序，需要添加括号
练习题： 从 order_items 表中，获取订单号为 6 的项目，并且项目的总价格大于 30
mysql> SELECT * FROM order_items;
+----------+------------+----------+------------+
| order_id | product_id | quantity | unit_price |
+----------+------------+----------+------------+
| 1 | 4 | 4 | 3.74 |
| 2 | 1 | 2 | 9.10 |
| 2 | 4 | 4 | 1.66 |
| 2 | 6 | 2 | 2.94 |
| 3 | 3 | 10 | 9.12 |
| 4 | 3 | 7 | 6.99 |
| 4 | 10 | 7 | 6.40 |
| 5 | 2 | 3 | 9.89 |
| 6 | 1 | 4 | 8.65 |
| 6 | 2 | 4 | 3.28 |
| 6 | 3 | 4 | 7.46 |
| 6 | 5 | 1 | 3.45 |
| 7 | 3 | 7 | 9.17 |
| 8 | 5 | 2 | 6.94 |
| 8 | 8 | 2 | 8.59 |
| 9 | 6 | 5 | 7.28 |
| 10 | 1 | 10 | 6.01 |
| 10 | 9 | 9 | 4.28 |
+----------+------------+----------+------------+
mysql> SELECT * FROM order_items WHERE order_id = 6 AND quantity * unit_price > 30;
+----------+------------+----------+------------+
| order_id | product_id | quantity | unit_price |
+----------+------------+----------+------------+
| 6 | 1 | 4 | 8.65 |
+----------+------------+----------+------------+
1.5、IN 运算
-- 使用 IN 运算符，查询一个字段的多个符合条件的数据
mysql> SELECT * FROM customers WHERE state IN ('VA','FL','GA');
+-------------+------------+------------+------------+--------------+------------------------+----------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+-------------+------------+------------+------------+--------------+------------------------+----------+-------+--------+
| 1 | Babara | MacCaffrey | 1986-03-28 | 781-932-9754 | 0 Sage Terrace | Waltham | VA | 2273 |
| 2 | Ines | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton | VA | 947 |
| 4 | Ambur | Roseburgh | 1974-04-14 | 407-231-8017 | 30 Arapahoe Terrace | Orlando | FL | 457 |
| 8 | Thacher | Naseby | 1993-07-17 | 941-527-3977 | 538 Mosinee Center | Sarasota | FL | 205 |
| 10 | Levy | Mynett | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue | Atlanta | GA | 796 |
+-------------+------------+------------+------------+--------------+------------------------+----------+-------+--------+
-- 使用 NOT INT 运算符，查询不符合这个字段条件值的数据
mysql> SELECT * FROM customers WHERE state NOT IN ('VA','FL','GA');
+-------------+------------+-----------+------------+--------------+----------------------+------------------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+-------------+------------+-----------+------------+--------------+----------------------+------------------+-------+--------+
| 3 | Freddi | Boagey | 1985-02-07 | 719-724-7869 | 251 Springs Junction | Colorado Springs | CO | 2967 |
| 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 |
| 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 |
| 7 | Ilene | Dowson | 1964-08-30 | 615-641-4759 | 50 Lillian Crossing | Nashville | TN | 1672 |
| 9 | Romola | Rumgay | 1992-05-23 | 559-181-3744 | 3520 Ohio Trail | Visalia | CA | 1486 |
+-------------+------------+-----------+------------+--------------+----------------------+------------------+-------+--------+
练习题： 写个查询，得到现货库存数量为49，38，72的产品
mysql> SELECT * FROM products;
+------------+------------------------------+-------------------+------------+
| product_id | name | quantity_in_stock | unit_price |
+------------+------------------------------+-------------------+------------+
| 1 | Foam Dinner Plate | 70 | 1.21 |
| 2 | Pork - Bacon,back Peameal | 49 | 4.65 |
| 3 | Lettuce - Romaine, Heart | 38 | 3.35 |
| 4 | Brocolinni - Gaylan, Chinese | 90 | 4.53 |
| 5 | Sauce - Ranch Dressing | 94 | 1.63 |
| 6 | Petit Baguette | 14 | 2.39 |
| 7 | Sweet Pea Sprouts | 98 | 3.29 |
| 8 | Island Oasis - Raspberry | 26 | 0.74 |
| 9 | Longan | 67 | 2.26 |
| 10 | Broom - Push | 6 | 1.09 |
+------------+------------------------------+-------------------+------------+
mysql> SELECT * FROM products WHERE quantity_in_stock IN (49,38,72);
+------------+---------------------------+-------------------+------------+
| product_id | name | quantity_in_stock | unit_price |
+------------+---------------------------+-------------------+------------+
| 2 | Pork - Bacon,back Peameal | 49 | 4.65 |
| 3 | Lettuce - Romaine, Heart | 38 | 3.35 |
+------------+---------------------------+-------------------+------------+
1.6、BETWEEN 运算符
-- 使用 BETWEEN 运算符，查找字段条件的范围内值包含临界值的数据
mysql> SELECT * FROM customers WHERE points BETWEEN 1000 AND 3000;
+-------------+------------+------------+------------+--------------+----------------------+------------------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+-------------+------------+------------+------------+--------------+----------------------+------------------+-------+--------+
| 1 | Babara | MacCaffrey | 1986-03-28 | 781-932-9754 | 0 Sage Terrace | Waltham | VA | 2273 |
| 3 | Freddi | Boagey | 1985-02-07 | 719-724-7869 | 251 Springs Junction | Colorado Springs | CO | 2967 |
| 7 | Ilene | Dowson | 1964-08-30 | 615-641-4759 | 50 Lillian Crossing | Nashville | TN | 1672 |
| 9 | Romola | Rumgay | 1992-05-23 | 559-181-3744 | 3520 Ohio Trail | Visalia | CA | 1486 |
+-------------+------------+------------+------------+--------------+----------------------+------------------+-------+--------+
mysql> SELECT * FROM customers WHERE points >= 1000 AND points <= 3000;
+-------------+------------+------------+------------+--------------+----------------------+------------------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+-------------+------------+------------+------------+--------------+----------------------+------------------+-------+--------+
| 1 | Babara | MacCaffrey | 1986-03-28 | 781-932-9754 | 0 Sage Terrace | Waltham | VA | 2273 |
| 3 | Freddi | Boagey | 1985-02-07 | 719-724-7869 | 251 Springs Junction | Colorado Springs | CO | 2967 |
| 7 | Ilene | Dowson | 1964-08-30 | 615-641-4759 | 50 Lillian Crossing | Nashville | TN | 1672 |
| 9 | Romola | Rumgay | 1992-05-23 | 559-181-3744 | 3520 Ohio Trail | Visalia | CA | 1486 |
+-------------+------------+------------+------------+--------------+----------------------+------------------+-------+--------+
# 上述这两个 SQL 等价，使用 BETWEEN 更为方便
练习题： 写一个查询，得到在 1990-01-01 到 2000-01-01 之间出生的顾客
mysql> SELECT * FROM customers;
+-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+
| 1 | Babara | MacCaffrey | 1986-03-28 | 781-932-9754 | 0 Sage Terrace | Waltham | VA | 2273 |
| 2 | Ines | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton | VA | 947 |
| 3 | Freddi | Boagey | 1985-02-07 | 719-724-7869 | 251 Springs Junction | Colorado Springs | CO | 2967 |
| 4 | Ambur | Roseburgh | 1974-04-14 | 407-231-8017 | 30 Arapahoe Terrace | Orlando | FL | 457 |
| 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 |
| 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 |
| 7 | Ilene | Dowson | 1964-08-30 | 615-641-4759 | 50 Lillian Crossing | Nashville | TN | 1672 |
| 8 | Thacher | Naseby | 1993-07-17 | 941-527-3977 | 538 Mosinee Center | Sarasota | FL | 205 |
| 9 | Romola | Rumgay | 1992-05-23 | 559-181-3744 | 3520 Ohio Trail | Visalia | CA | 1486 |
| 10 | Levy | Mynett | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue | Atlanta | GA | 796 |
+-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+
mysql> SELECT first_name,last_name FROM customers WHERE birth_date BETWEEN '1990-01-01' AND '2000-01-01';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Elka | Twiddell |
| Thacher | Naseby |
| Romola | Rumgay |
+------------+-----------+
1.7、LIKE 运算符
-- 使用 LIKE 运算符，对字段进行模糊匹配查出数据
mysql> SELECT * FROM customers WHERE last_name LIKE "B%";
+-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+
| 2 | Ines | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton | VA | 947 |
| 3 | Freddi | Boagey | 1985-02-07 | 719-724-7869 | 251 Springs Junction | Colorado Springs | CO | 2967 |
| 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 |
+-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+
LIKT 中正则表示：
% 可以匹配任意长度的字符串（包括空字符串）
_ 可以匹配任意单个字符
练习题：
1.获取顾客他们的地址，里面包含 trail 或者 avenue
2.获取手机号以 9 为结尾的顾客
mysql> SELECT * FROM customers;
+-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+
| 1 | Babara | MacCaffrey | 1986-03-28 | 781-932-9754 | 0 Sage Terrace | Waltham | VA | 2273 |
| 2 | Ines | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton | VA | 947 |
| 3 | Freddi | Boagey | 1985-02-07 | 719-724-7869 | 251 Springs Junction | Colorado Springs | CO | 2967 |
| 4 | Ambur | Roseburgh | 1974-04-14 | 407-231-8017 | 30 Arapahoe Terrace | Orlando | FL | 457 |
| 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 |
| 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 |
| 7 | Ilene | Dowson | 1964-08-30 | 615-641-4759 | 50 Lillian Crossing | Nashville | TN | 1672 |
| 8 | Thacher | Naseby | 1993-07-17 | 941-527-3977 | 538 Mosinee Center | Sarasota | FL | 205 |
| 9 | Romola | Rumgay | 1992-05-23 | 559-181-3744 | 3520 Ohio Trail | Visalia | CA | 1486 |
| 10 | Levy | Mynett | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue | Atlanta | GA | 796 |
+-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+
mysql> SELECT * FROM customers WHERE address LIKE '%Trail%' OR address LIKE '%Avenue%';
+-------------+------------+------------+------------+--------------+------------------------+---------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+-------------+------------+------------+------------+--------------+------------------------+---------+-------+--------+
| 2 | Ines | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton | VA | 947 |
| 9 | Romola | Rumgay | 1992-05-23 | 559-181-3744 | 3520 Ohio Trail | Visalia | CA | 1486 |
| 10 | Levy | Mynett | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue | Atlanta | GA | 796 |
+-------------+------------+------------+------------+--------------+------------------------+---------+-------+--------+
mysql> SELECT * FROM customers WHERE phone LIKE '%9';
+-------------+------------+-----------+------------+--------------+----------------------+------------------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+-------------+------------+-----------+------------+--------------+----------------------+------------------+-------+--------+
| 3 | Freddi | Boagey | 1985-02-07 | 719-724-7869 | 251 Springs Junction | Colorado Springs | CO | 2967 |
| 7 | Ilene | Dowson | 1964-08-30 | 615-641-4759 | 50 Lillian Crossing | Nashville | TN | 1672 |
+-------------+------------+-----------+------------+--------------+----------------------+------------------+-------+--------+
1.8、REGEXP 运算符
-- 使用 REGEXP 正则运算符，支持更多正则，代替 LIKE 使用
mysql> SELECT * FROM customers WHERE last_name LIKE '%field%';
+-------------+------------+------------+------------+--------------+------------------------+---------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+-------------+------------+------------+------------+--------------+------------------------+---------+-------+--------+
| 2 | Ines | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton | VA | 947 |
+-------------+------------+------------+------------+--------------+------------------------+---------+-------+--------+
mysql> SELECT * FROM customers WHERE last_name REGEXP 'field';
+-------------+------------+------------+------------+--------------+------------------------+---------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+-------------+------------+------------+------------+--------------+------------------------+---------+-------+--------+
| 2 | Ines | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton | VA | 947 |
+-------------+------------+------------+------------+--------------+------------------------+---------+-------+--------+
支持的正则符号：
^ 代表字符串开头
$ 代表字符串结尾
| 代表或者
[] 代表匹配任意在方括号里列举的单字符，方括号内单字符之间添加一个-，代表一个范围
. 代表匹配任意单个字符
练习题：
1.获取名字是 ELka 或者 Ambur 的顾客
2.返回姓氏以 ey 或者 on 结尾的顾客
3.获取姓氏以 my 打头或者包含 se 的顾客
4.返回顾客，他们的姓氏包含 b 接着 r 或者 u
-- 练习题 1
mysql> SELECT * FROM customers WHERE first_name REGEXP 'Elka|Ambur';
+-------------+------------+-----------+------------+--------------+---------------------+---------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+-------------+------------+-----------+------------+--------------+---------------------+---------+-------+--------+
| 4 | Ambur | Roseburgh | 1974-04-14 | 407-231-8017 | 30 Arapahoe Terrace | Orlando | FL | 457 |
| 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 |
+-------------+------------+-----------+------------+--------------+---------------------+---------+-------+--------+
-- 练习题 2
mysql> SELECT * FROM customers WHERE last_name REGEXP 'ey$|on$';
+-------------+------------+------------+------------+--------------+----------------------+------------------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+-------------+------------+------------+------------+--------------+----------------------+------------------+-------+--------+
| 1 | Babara | MacCaffrey | 1986-03-28 | 781-932-9754 | 0 Sage Terrace | Waltham | VA | 2273 |
| 3 | Freddi | Boagey | 1985-02-07 | 719-724-7869 | 251 Springs Junction | Colorado Springs | CO | 2967 |
| 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 |
| 7 | Ilene | Dowson | 1964-08-30 | 615-641-4759 | 50 Lillian Crossing | Nashville | TN | 1672 |
+-------------+------------+------------+------------+--------------+----------------------+------------------+-------+--------+
-- 练习题 3
mysql> SELECT * FROM customers WHERE last_name REGEXP '^My|se';
+-------------+------------+-----------+------------+--------------+---------------------+----------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+-------------+------------+-----------+------------+--------------+---------------------+----------+-------+--------+
| 4 | Ambur | Roseburgh | 1974-04-14 | 407-231-8017 | 30 Arapahoe Terrace | Orlando | FL | 457 |
| 8 | Thacher | Naseby | 1993-07-17 | 941-527-3977 | 538 Mosinee Center | Sarasota | FL | 205 |
| 10 | Levy | Mynett | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue | Atlanta | GA | 796 |
+-------------+------------+-----------+------------+--------------+---------------------+----------+-------+--------+
-- 练习题 4
mysql> SELECT * FROM customers WHERE last_name REGEXP 'b[ru]';
+-------------+------------+-----------+------------+--------------+---------------------+---------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+-------------+------------+-----------+------------+--------------+---------------------+---------+-------+--------+
| 4 | Ambur | Roseburgh | 1974-04-14 | 407-231-8017 | 30 Arapahoe Terrace | Orlando | FL | 457 |
+-------------+------------+-----------+------------+--------------+---------------------+---------+-------+--------+
1.9、IS NULL 运算符
-- 使用 IS NULL 运算符，查找该字段值为 NULL 的数据
mysql> SELECT * FROM customers WHERE phone IS NULL;
+-------------+------------+-----------+------------+-------+----------------+-----------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+-------------+------------+-----------+------------+-------+----------------+-----------+-------+--------+
| 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 |
+-------------+------------+-----------+------------+-------+----------------+-----------+-------+--------+
-- 也可以通过 IS NOT NULL ，查找该字段值不为 NULL 的数据
mysql> SELECT * FROM customers WHERE phone IS NOT NULL;
+-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+
| 1 | Babara | MacCaffrey | 1986-03-28 | 781-932-9754 | 0 Sage Terrace | Waltham | VA | 2273 |
| 2 | Ines | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton | VA | 947 |
| 3 | Freddi | Boagey | 1985-02-07 | 719-724-7869 | 251 Springs Junction | Colorado Springs | CO | 2967 |
| 4 | Ambur | Roseburgh | 1974-04-14 | 407-231-8017 | 30 Arapahoe Terrace | Orlando | FL | 457 |
| 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 |
| 7 | Ilene | Dowson | 1964-08-30 | 615-641-4759 | 50 Lillian Crossing | Nashville | TN | 1672 |
| 8 | Thacher | Naseby | 1993-07-17 | 941-527-3977 | 538 Mosinee Center | Sarasota | FL | 205 |
| 9 | Romola | Rumgay | 1992-05-23 | 559-181-3744 | 3520 Ohio Trail | Visalia | CA | 1486 |
| 10 | Levy | Mynett | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue | Atlanta | GA | 796 |
+-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+
练习题： 写一段查询，获取所有还没有发货的订单
mysql> SELECT * FROM orders;
+----------+-------------+------------+--------+-----------------------------------------------------------------------+--------------+------------+
| order_id | customer_id | order_date | status | comments | shipped_date | shipper_id |
+----------+-------------+------------+--------+-----------------------------------------------------------------------+--------------+------------+
| 1 | 6 | 2019-01-30 | 1 | NULL | NULL | NULL |
| 2 | 7 | 2018-08-02 | 2 | NULL | 2018-08-03 | 4 |
| 3 | 8 | 2017-12-01 | 1 | NULL | NULL | NULL |
| 4 | 2 | 2017-01-22 | 1 | NULL | NULL | NULL |
| 5 | 5 | 2017-08-25 | 2 | | 2017-08-26 | 3 |
| 6 | 10 | 2018-11-18 | 1 | Aliquam erat volutpat. In congue. | NULL | NULL |
| 7 | 2 | 2018-09-22 | 2 | NULL | 2018-09-23 | 4 |
| 8 | 5 | 2018-06-08 | 1 | Mauris enim leo, rhoncus sed, vestibulum sit amet, cursus id, turpis. | NULL | NULL |
| 9 | 10 | 2017-07-05 | 2 | Nulla mollis molestie lorem. Quisque ut erat. | 2017-07-06 | 1 |
| 10 | 6 | 2018-04-22 | 2 | NULL | 2018-04-23 | 2 |
+----------+-------------+------------+--------+-----------------------------------------------------------------------+--------------+------------+
mysql> SELECT * FROM orders WHERE shipped_date IS NULL;
+----------+-------------+------------+--------+-----------------------------------------------------------------------+--------------+------------+
| order_id | customer_id | order_date | status | comments | shipped_date | shipper_id |
+----------+-------------+------------+--------+-----------------------------------------------------------------------+--------------+------------+
| 1 | 6 | 2019-01-30 | 1 | NULL | NULL | NULL |
| 3 | 8 | 2017-12-01 | 1 | NULL | NULL | NULL |
| 4 | 2 | 2017-01-22 | 1 | NULL | NULL | NULL |
| 6 | 10 | 2018-11-18 | 1 | Aliquam erat volutpat. In congue. | NULL | NULL |
| 8 | 5 | 2018-06-08 | 1 | Mauris enim leo, rhoncus sed, vestibulum sit amet, cursus id, turpis. | NULL | NULL |
+----------+-------------+------------+--------+-----------------------------------------------------------------------+--------------+------------+
1.10、ORDER BY 子句
-- 使用 ORDER BY 子句，对指定字段进行排序操作
mysql> SELECT * FROM customers ORDER BY first_name;
+-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+
| 4 | Ambur | Roseburgh | 1974-04-14 | 407-231-8017 | 30 Arapahoe Terrace | Orlando | FL | 457 |
| 1 | Babara | MacCaffrey | 1986-03-28 | 781-932-9754 | 0 Sage Terrace | Waltham | VA | 2273 |
| 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 |
| 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 |
| 3 | Freddi | Boagey | 1985-02-07 | 719-724-7869 | 251 Springs Junction | Colorado Springs | CO | 2967 |
| 7 | Ilene | Dowson | 1964-08-30 | 615-641-4759 | 50 Lillian Crossing | Nashville | TN | 1672 |
| 2 | Ines | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton | VA | 947 |
| 10 | Levy | Mynett | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue | Atlanta | GA | 796 |
| 9 | Romola | Rumgay | 1992-05-23 | 559-181-3744 | 3520 Ohio Trail | Visalia | CA | 1486 |
| 8 | Thacher | Naseby | 1993-07-17 | 941-527-3977 | 538 Mosinee Center | Sarasota | FL | 205 |
+-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+
ASC 代表正序
DESC 代表倒序
练习题： 通过 order_items 表，得出 order_id 等于 2 的项目，冰洁根据项目的总价格进行倒叙排序
mysql> SELECT * FROM order_items;
+----------+------------+----------+------------+
| order_id | product_id | quantity | unit_price |
+----------+------------+----------+------------+
| 1 | 4 | 4 | 3.74 |
| 2 | 1 | 2 | 9.10 |
| 2 | 4 | 4 | 1.66 |
| 2 | 6 | 2 | 2.94 |
| 3 | 3 | 10 | 9.12 |
| 4 | 3 | 7 | 6.99 |
| 4 | 10 | 7 | 6.40 |
| 5 | 2 | 3 | 9.89 |
| 6 | 1 | 4 | 8.65 |
| 6 | 2 | 4 | 3.28 |
| 6 | 3 | 4 | 7.46 |
| 6 | 5 | 1 | 3.45 |
| 7 | 3 | 7 | 9.17 |
| 8 | 5 | 2 | 6.94 |
| 8 | 8 | 2 | 8.59 |
| 9 | 6 | 5 | 7.28 |
| 10 | 1 | 10 | 6.01 |
| 10 | 9 | 9 | 4.28 |
+----------+------------+----------+------------+
mysql> SELECT * FROM order_items WHERE order_id = 2 ORDER BY quantity*unit_price DESC;
+----------+------------+----------+------------+
| order_id | product_id | quantity | unit_price |
+----------+------------+----------+------------+
| 2 | 1 | 2 | 9.10 |
| 2 | 4 | 4 | 1.66 |
| 2 | 6 | 2 | 2.94 |
+----------+------------+----------+------------+
1.11、LIMIT 子句
-- 使用 LIMIT 子句，获取数据的前几行
mysql> SELECT * FROM customers LIMIT 6,3;
+-------------+------------+-----------+------------+--------------+---------------------+-----------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+-------------+------------+-----------+------------+--------------+---------------------+-----------+-------+--------+
| 7 | Ilene | Dowson | 1964-08-30 | 615-641-4759 | 50 Lillian Crossing | Nashville | TN | 1672 |
| 8 | Thacher | Naseby | 1993-07-17 | 941-527-3977 | 538 Mosinee Center | Sarasota | FL | 205 |
| 9 | Romola | Rumgay | 1992-05-23 | 559-181-3744 | 3520 Ohio Trail | Visalia | CA | 1486 |
+-------------+------------+-----------+------------+--------------+---------------------+-----------+-------+--------+
练习题： 获取出积分最多的前三名客户
mysql> SELECT * FROM customers ORDER BY points DESC LIMIT 3;
+-------------+------------+-----------+------------+--------------+----------------------+------------------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone | address | city | state | points |
+-------------+------------+-----------+------------+--------------+----------------------+------------------+-------+--------+
| 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 |
| 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 |
| 3 | Freddi | Boagey | 1985-02-07 | 719-724-7869 | 251 Springs Junction | Colorado Springs | CO | 2967 |
+-------------+------------+-----------+------------+--------------+----------------------+------------------+-------+--------+
浙公网安备 33010602011771号