128 lines
1.7 KiB
Markdown
128 lines
1.7 KiB
Markdown
# 检索
|
|
|
|
1. 检索列
|
|
|
|
```sql
|
|
SELECT cust_id
|
|
FROM Customers
|
|
```
|
|
|
|
2. 检索列去重
|
|
|
|
```sql
|
|
SELECT DISTINCT prod_id
|
|
FROM OrderItems
|
|
```
|
|
|
|
3. 检索列排序
|
|
|
|
```sql
|
|
SELECT cust_name
|
|
FROM Customers
|
|
ORDER BY cust_name DESC
|
|
```
|
|
|
|
4. 检索列双重排序
|
|
|
|
```sql
|
|
SELECT cust_id, order_num
|
|
FROM Orders
|
|
ORDER BY cust_id, order_date DESC
|
|
```
|
|
|
|
```sql
|
|
SELECT quantity, item_price
|
|
FROM OrderItems
|
|
ORDER BY quantity DESC, item_price DESC
|
|
```
|
|
|
|
5. 等值查找
|
|
|
|
```sql
|
|
SELECT prod_id, prod_name
|
|
FROM Products
|
|
WHERE prod_price = 9.49
|
|
```
|
|
|
|
6. 范围查找
|
|
|
|
```sql
|
|
SELECT prod_id, prod_name
|
|
FROM Products
|
|
WHERE prod_price >= 9
|
|
```
|
|
|
|
7. 范围查找排序
|
|
|
|
```sql
|
|
SELECT prod_name, prod_price
|
|
FROM Products
|
|
WHERE prod_price BETWEEN 3 AND 6
|
|
ORDER BY prod_price
|
|
```
|
|
|
|
8. 分组筛选
|
|
|
|
```sql
|
|
SELECT order_num
|
|
FROM OrderItems
|
|
GROUP BY order_item
|
|
HAVING SUM(quantity) >= 100
|
|
```
|
|
|
|
9. 区域匹配
|
|
|
|
```sql
|
|
SELECT order_num, prod_id, quantity
|
|
FROM OrderItems
|
|
WHERE prod_id IN ('BR01', 'BR02', 'BR03') AND quantity >= 100
|
|
```
|
|
|
|
10. 模糊匹配
|
|
|
|
```sql
|
|
SELECT prod_name, prod_desc
|
|
FROM Products
|
|
WHERE prod_desc LIKE '%toy%'
|
|
```
|
|
|
|
11. 别名
|
|
|
|
```sql
|
|
SELECT vend_id, vend_name AS vname, vend_address AS vaddress, vend_city AS vcity
|
|
FROM Vendors
|
|
ORDER BY vname
|
|
```
|
|
|
|
12. 列计算
|
|
|
|
```sql
|
|
SELECT prod_id, prod_price, prod_price * 0.9 AS sale_price
|
|
FROM Products
|
|
```
|
|
|
|
13. 函数
|
|
|
|
```sql
|
|
SELECT cust_id, cust_name, UPPER(CONCAT(SUBSTRING(cust_contact, 1, 2), SUBSTRING(cust_city, 1, 3))) AS user_login
|
|
FROM Customers
|
|
```
|
|
|
|
```sql
|
|
SELECT order_num, COUNT(*) AS items
|
|
FROM OrderItems
|
|
GROUP BY order_num
|
|
HAVING items >= 3
|
|
ORDER BY items, order_num
|
|
```
|
|
|
|
|
|
14. 子查询
|
|
|
|
```sql
|
|
SELECT cust_id
|
|
FROM Orders
|
|
WHERE order_num IN (SELECT DISTINCT order_num
|
|
FROM OrderItems
|
|
where item_price >= 10)
|
|
``` |