MySql简单学习

MySql-增删改查之查(三)

子查询(subquery)

子查询,即嵌套在其他查询中的查询。

#首先在orderitems表中查询商品(prod_id)为TNT2的订单号(order_num),再从orders表
#中找出相应order_num的客户号(cust_id),最后从customers表中找出相应cust_id的
#cust_name...

SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id     # 子查询一般与IN操作符结合使用,但也可以用
                                     # 于等于=和不等于<>等;
                  FROM orders
                  WHERE order_num IN(SELECT order_num
                                     FROM orderitems
                                     WHERE prod_id = 'TNT2'));

作为计算字段使用子查询

#统计一个客户的订单数量
SELECT COUNT(*) AS orders
FROM orders
WHERE cust_id = 10001; 

#统计所有客户的订单数量
SELECT cust_name, 
       (SELECT COUNT(*)
       FROM orders
       WHERE orders.cust_id = customers.cust_id) AS orders   # 完全限定列名
FROM customers
ORDER BY cust_name;

联结表(WHERE、JOIN…ON)

使用外键定义关系的多个表,在SELECT中可以使用联结返回一组多个表的数据。

SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE venord.vend_id = products.vend_id  #用WHERE子句进行联结
ORDER BY vend_name, prod_name;

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON venord.vend_id = products.vend_id  #用JOIN..ON进行联结
ORDER BY vend_name, prod_name;

联结类型有自联结、内部联结、外部联结(左联结、右联结)和自然联结(排除多次出现,使每个列只返回一次)。

# 自联结
# 找出产品id为DTNTR的供应商的所有产品
SELECT prod_id, pros_name
FROM products
WHERE vend_id = (SELECT vend_id
                 FROM products
                 WHERE pord_id = 'DTNTR');
# 右联结
# 检索所有客户,包括那些没有订单的客户
SELECT customers.cust_id, orders.order_num
FROM custmors LEFTER OUTER JOIN orders
ON customers.cust_id = orders.cust_id;

组合查询(UNION)

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION                                # 自动去除重复的行,
SELECT vend_id, prod_id, prod_price  # 如要返回所有行使用'UNION ALL'
FROM products
WHERE vend_id IN (1001,1002);  

CASE WHEN

发表评论

电子邮件地址不会被公开。

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据