任务 5 多表查询 课内例题 例创建数据表 orders, 并向表中添加记录 首先创建表 orders,sql 语句如下 : CREATE TABLE orders( o_num int NOT NULL AUTO_INCREMENT, o_date datetime NOT NULL, c_id int NOT NULL, PRIMARY KEY (o_num) ) ; 插入需要演示的数据,SQL 语句如下 : INSERT INTO orders(o_num, o_date, c_id) VALUES(30001, '2008-09-01', 10001), (30002, '2008-09-12', 10003),(30003, '2008-09-30', 10004), (30004, '2008-10-03', 10005),(30005, '2008-10-08', 10001); 1. 为表或字段取别名 例 1 为 orders 表取别名 o, 查询 30001 订单的下单日期,SQL 语句如下 : SELECT * FROM orders AS o WHERE o.o_num = 30001; 例 2 查询 fruits 表, 为 f_name 取别名 fruit_name,f_price 取别名 fruit_price, 为 fruits 表取别名 f1, 查询表中 f_price < 8 的水果的名称,SQL 语句如下 : SELECT f1.f_name AS fruit_name, f1.f_price AS fruit_price FROM fruits AS f1 WHERE f1.f_price < 8; 例 3 在 orderitems 表中, 查询订单价格大于 100 的订单号和总订单价格,SQL 语句如下 : SELECT o_num, SUM(quantity * item_price) as totalprice FROM orderitems GROUP BY o_num HAVING totalprice >= 100
ORDER BY totalprice; 2. 连接查询首先创建数据表 suppliers,sql 语句如下 : CREATE TABLE suppliers ( s_id int NOT NULL AUTO_INCREMENT, s_name char(50) NOT NULL, s_city char(50) NULL, s_zip char(10) NULL, s_call CHAR(50) NOT NULL, PRIMARY KEY (s_id) ) ; 插入需要演示的数据,SQL 语句如下 : INSERT INTO suppliers(s_id, s_name,s_city, s_zip, s_call) VALUES(101,'FastFruit Inc.','Tianjin','300000','48075'), (102,'LT Supplies','Chongqing','400000','44333'), (103,'ACME','Shanghai','200000','90046'), (104,'FNK Inc.','Zhongshan','528437','11111'), (105,'Good Set','Taiyuang','030000', '22222'), (106,'Just Eat Ours','Beijing','010', '45678'), (107,'DK Inc.','Zhengzhou','450000', '33332'); 例 4 在 fruits 表和 suppliers 表之间进行查询 ( 从 fruits 表中查询 f_name f_price 字段, 从 suppliers 表中查询 s_id s_name) 查询之前, 查看两个表的结构 : DESC fruits; DESC suppliers; 由结果可以看到,fruits 表和 suppliers 表中都有相同数据类型的字段 s_id, 两个表通过 s_id 字段建立联系 接下来从 fruits 表中查询 f_name f_price 字段, 从 suppliers 表中查询 s_id s_name,sql 语句如下 : SELECT suppliers.s_id, s_name,f_name, f_price
FROM fruits,suppliers WHERE fruits.s_id = suppliers.s_id; 说明 : 在两个表中有相同字段时, 在比较时需要完全限定表名 ( 格式为 : 表名. 列名 ), 如果只给出列名,MySQL 将不知道指的是哪一个, 并返回错误信息 例 5 在 fruits 表和 suppliers 表之间, 使用 INNER JOIN 语法进行内连接查询, SQL 语句如下 :( 内连接查询 ) SELECT suppliers.s_id, s_name,f_name, f_price FROM fruits INNER JOIN suppliers ON fruits.s_id = suppliers.s_id; 为表名取别名之后的 SQL 语句如下 : select s.s_id,s_name,f_name,f_price from suppliers as s inner join fruits as f on s.s_id=f.s_id; 注意 : 取别名之后, 再引用表名时需要使用别名, 否则会出错! 例 6 查询供应 f_id= a1 的水果供应商提供的其他水果种类,SQL 语句如下 : ( 自连接查询 ) SELECT f1.s_id, f1.f_id, f1.f_name FROM fruits AS f1, fruits AS f2 WHERE f1.s_id = f2.s_id AND f2.f_id = 'a1'; +------+------------+ f_id f_name +------+------------+ a1 apple b1 blackberry c0 cherry +------+------------+ 为防止产生二义性, 需要对表取别名 第一次出现的 fruits 表的别名是 f1, 第二次出现的别名是 f2 例 7 在 customers 表和 orders 表中, 查询所有客户, 包括没有订单的客户,SQL 语句如下 :( 左连接查询 ) SELECT customers.c_id, orders.o_num FROM customers LEFT OUTER JOIN orders
ON customers.c_id = orders.c_id; c_id o_num 10001 30001 10001 30005 10002 NULL 10003 30002 10004 30003 例 8 在 customers 表和 orders 表中, 查询所有订单, 包括没有客户的订单,SQL 语句如下 :( 右连接查询 ) SELECT customers.c_id, orders.o_num FROM customers RIGHT OUTER JOIN orders ON customers.c_id = orders.c_id; c_id o_num 10001 30001 10003 30002 10004 30003 NULL 30004 10001 30005 3. 子查询 (1) 带 ANY 或 SOME 关键字的子查询下面定义两个表 tbl1 和 tbl2: CREATE table tb1 ( num1 INT NOT NULL); CREATE table tb2 ( num2 INT NOT NULL); 分别向两个表中插入数据 : INSERT INTO tb1 values(1), (5), (13), (27); INSERT INTO tb2 values(6), (14), (11), (20); ANY 关键字接在一个比较操作符的后面, 表示若与子查询返回的任何值比较为 TRUE, 则返回 TRUE 例 9 返回 tb2 表的所有 num2 列, 然后将 tb1 中的 num1 的值与之进行比较, 只
要大于 num2 的任何 1 个值, 即为符合查询条件的结果 SELECT num1 FROM tb1 WHERE num1 > ANY (SELECT num2 FROM tb2); (2) 带 ALL 关键字的子查询 例 10 返回 tb1 表中比 tb2 表 num2 列所有值都大的值,SQL 语句如下 : SELECT num1 FROM tb1 WHERE num1 > ALL (SELECT num2 FROM tb2); (3) 带 EXISTS 关键字的子查询 例 11 查询 suppliers 表中是否存在 s_id=107 的供应商, 如果存在, 则查询 fruits 表中的记录,SQL 语句如下 : SELECT * FROM fruits WHERE EXISTS (SELECT s_name FROM suppliers WHERE s_id = 107); 例 12 查询 suppliers 表中是否存在 s_id=107 的供应商, 如果存在, 则查询 fruits 表中的 f_price 大于 10.20 的记录,SQL 语句如下 : SELECT * FROM fruits WHERE f_price>10.20 AND EXISTS (SELECT s_name FROM suppliers WHERE s_id = 107);
例 13 查询 suppliers 表中是否存在 s_id=107 的供应商, 如果不存在则查询 fruits 表中的记录,SQL 语句如下 : SELECT * FROM fruits WHERE NOT EXISTS (SELECT s_name FROM suppliers WHERE s_id = 107); 无符合条件的记录 (4) 带 IN 关键字的子查询 例 14 在 orderitems 表中查询 f_id 为 c0 的订单号, 并根据订单号查询具有订单号的客户 c_id,sql 语句如下 : SELECT c_id FROM orders WHERE o_num IN (SELECT o_num FROM orderitems WHERE f_id = 'c0'); 上述查询过程可以分步执行, 先用内层子查询在表 orderitems 中查询出符合条件的订单号,SQL 语句如下 SELECT o_num FROM orderitems WHERE f_id = 'c0'; 可以看到, 符合条件的 o_num 列的值有两个 :30003 和 30005, 然后执行外层查询, 在 orders 表中查询订单号等于 30003 或 30005 的客户 c_id 嵌套子查询语句还可以改写为如下形式, 实现相同的效果 : SELECT c_id FROM orders WHERE o_num IN (30003, 30005); 例 15 与前一个例子类似, 但是在 SELECT 语句中使用 NOT IN 关键字,SQL 语句如下 : SELECT c_id FROM orders WHERE o_num NOT IN
(SELECT o_num FROM orderitems WHERE f_id = 'c0'); SELECT * FROM orders; (5) 带比较运算符的子查询 例 16 在 suppliers 表中查询 s_city 等于 Tianjin 的供应商 s_id, 然后在 fruits 表中查询所有该供应商提供的水果的种类,SQL 语句如下 : SELECT s_id, f_name FROM fruits WHERE s_id = (SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin'); 例 17 在 suppliers 表中查询 s_city 等于 Tianjin 的供应商 s_id, 然后在 fruits 表中查询所有非该供应商提供的水果的种类,SQL 语句如下 : SELECT s_id, f_name FROM fruits WHERE s_id <> (SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin');
4. 合并查询结果 例 18 在 fruits 表中查询所有价格小于 9 的水果的信息, 查询 s_id 等于 101 和 103 所有的水果的信息, 使用 UNION 连接查询结果,SQL 语句如下 : SELECT s_id, f_name, f_price FROM fruits WHERE f_price < 9.0 UNION SELECT s_id, f_name, f_price FROM fruits WHERE s_id IN(101,103); 例 19 查询所有价格小于 9 的水果的信息, 查询 s_id 等于 101 和 103 的所有水 果的信息, 使用 UNION ALL 连接查询结果,SQL 语句如下 :
SELECT s_id, f_name, f_price FROM fruits WHERE f_price < 9.0 UNION ALL SELECT s_id, f_name, f_price FROM fruits WHERE s_id IN(101,103);