1. 读取某一列
SELECT
name
FROM
Table
2. 字符串的截取和拼接
- 字符串的截取：substring(字符串，起始位置，截取字符数）
- 字符串的拼接：concat(字符串1，字符串2，字符串3,...)
- 字母大写：upper(字符串）
upper(concat(substring(cust_name,``1``,``2``),substring(cust_city,``1``,``3``))) as user_login
3. 分组
SELECT
sum(quantity) as items_odered
FROM
OrderItems
GROUP BY
prod_id
HAVING
prod_id=``'BR01'``;
4. 排序
order by name # 正序
order by name desc # 逆序
5.子查询
select cust_id, order_date from Orders
where order_num in(
select order_num from OrderItems
where prod_id = "BR01"
)
order by order_date
6. 关联查询
SELECT
cust_id, order_date
FROM
Orders t
INNER JOIN
OrderItems t1
on t.order_num = t1.order_num
WHERE
prod_id = "BR01"
ORDER BY
order_date
7.内连接
SELECT
c.cust_name cust_name,
o.order_num order_num,
SUM(oi.quantity * oi.item_price) OrderTotal
FROM Customers c INNER JOIN Orders o ON
c.cust_id = o.cust_id INNER JOIN OrderItems oi ON
o.order_num = oi.order_num
GROUP BY
cust_name,
order_num
ORDER BY
cust_name,
order_num;
8. 外连接
SELECT
cust_name,order_num
FROM
Customers c
LEFT JOIN # 左外连接，以第一个表为主
Orders o
on c.cust_id= o.cust_id
# right join Oeders on Customers using(cust_id) 右连接写法
ORDER BY
cust_name;
