《SQL必知必会》学习总结2

总结2

上一节课总结到第6课数据过滤，本章将总结完剩下的两大主体高级检索数据和跨表操作数据。

高级检索数据

拼接字段：

1
2
3
SELECT prod_name + '(' + prod_id + ')' FROM products;
SELECT prod_name || '(' || prod_id + ')' FROM products;
SELECT Concat(prod_name, '(', prod_id, ')') FROM products;

使用别名：

1
2
3
SELECT prod_name + '(' + prod_id + ')' as prod_title FROM products;
SELECT prod_name || '(' || prod_id + ')' as prod_title FROM products;
SELECT Concat(prod_name, '(', prod_id, ')') as prod_title FROM products;

算数计算：

1
2
3
4
5
SELECT prod_id, order_price, quantity,
        order_price*quantity as order_total
        quantity0+quantity1 as quantity_sum
FROM orders
where order_id = '20005';

函数计算：

每个DBMS中基本没有一致的命名，本处不再写。

聚集函数：

1
2
3
4
5
6
7
SELECT AVG(order_price) as price_avg,
        COUNT(order_price) as price_count,
        MAX(order_price) as price_max,
        MIN(order_price) as price_min,
        SUM(order_price) as price_sum
FROM Orders
WHERE order_price > 9;

数据分组：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT AVG(order_price) as price_avg,
        COUNT(order_price) as price_count,
        MAX(order_price) as price_max,
        MIN(order_price) as price_min,
        SUM(order_price) as price_sum
FROM Orders
GROUP BY prod_id;

SELECT AVG(order_price) as price_avg,
        COUNT(order_price) as price_count,
        MAX(order_price) as price_max,
        MIN(order_price) as price_min,
        SUM(order_price) as price_sum
FROM Orders
GROUP BY prod_id
HAVING order_price > 9;

跨表操作数据

利用子查询过滤：

1
2
3
4
5
6
SELECT cust_name, cust_contact
FROM cust_id IN (SELECT cust_id
                 FROM Orders
                 WHERE order_num IN (SELECT order_num
                                    FROM orderItems
                                    WHERE prod_id = 'RGAN01'))

作为计算字段的子查询：

1
2
3
4
5
6
7
SELECT cust_name,
        cust_state,
        (SELECT COUNT(*)
        from Orders
        WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;

联结
内联结

1
2
3
select vend_name, prod_name, prod_price
from vendors, products
WHERE products.vend_id = vendors.vend_id;

或者

1
2
3
select vend_name, prod_name, prod_price
from vendors inner join products 
     on vendors.vend_id = products.vend_id;

内联结（多表）

1
2
3
4
select vend_name, prod_name, prod_price, order_num
from vendors, products, orderitems 
where vendors.vend_id = products.vend_id and
	products.prod_id = orderitems.prod_id; 
1
2
3
4
5
select cust_name, cust_contact
from customers as C, orders AS O, orderitems AS OI
where C.cust_id = O.cust_id 
	and O.order_num = OI.order_num
    and prod_id = 'RGAN01';

自联结：

1
2
3
4
select c1.cust_id, c1.cust_name, c2.cust_contact
from customers as c1, customers as c2
where c1.cust_name = c2.cust_name
	and c1.cust_contact = 'jim jones';

自然联结：

1
2
3
4
5
6
select C.*, O.order_num,O.order_date, 
		OI.item_price, OI.prod_id, OI.quantity
from customers as C, orders AS O, orderitems AS OI
where C.cust_id = O.cust_id 
	and O.order_num = OI.order_num
    and prod_id = 'RGAN01';

外联结：

1
2
3
select customers.cust_id, customers.cust_name, orders.order_num
from customers left outer join orders
	on customers.cust_id = orders.cust_id;
1
2
3
select customers.cust_id, customers.cust_name, orders.order_num
from  orders RIGHT outer join customers
	on customers.cust_id = orders.cust_id;

组合查询：

1
2
3
4
5
6
7
8
select cust_name, cust_contact, cust_email
from customers
where cust_state IN ('IL','IN','MI')
union
select cust_name, cust_contact, cust_email
from customers
where cust_name = 'Fun4All'
order by cust_name;

到这里本系列真正的结束了，明天开始《MySQL必知必会》。