# 检索

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)
```