MySQL连接查询实例详解
本文实例讲述了MySQL连接查询。分享给大家供大家参考,具体如下:
站在用户的角度思考问题,与客户深入沟通,找到丹东网站设计与丹东网站推广的解决方案,凭借多年的经验,让设计与互联网技术结合,创造个性化、用户体验好的作品,建站类型包括:网站设计制作、成都网站设计、企业官网、英文网站、手机端网站、网站推广、域名申请、网络空间、企业邮箱。业务覆盖丹东地区。
创建表suppliers:
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) ) ; 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');
内连接
SELECT suppliers.s_id, s_name,f_name, f_price FROM fruits ,suppliers WHERE fruits.s_id = suppliers.s_id;
使用 inner join
语法进行内连接查询
SELECT suppliers.s_id, s_name,f_name, f_price FROM fruits INNER JOIN suppliers ON fruits.s_id = suppliers.s_id;
创建表orders:
CREATE TABLE orders ( o_num int NOT NULL AUTO_INCREMENT, o_date datetime NOT NULL, c_id int NOT NULL, PRIMARY KEY (o_num) ) ; 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);
左连接
SELECT customers.c_id, orders.o_num FROM customers LEFT OUTER JOIN orders ON customers.c_id = orders.c_id;
右连接
SELECT customers.c_id, orders.o_num from customers RIGHT OUTER JOIN orders ON customers.c_id = orders.c_id;
复合条件连接查询
SELECT customers.c_id, orders.o_num FROM customers INNER JOIN orders ON customers.c_id = orders.c_id AND customers.c_id = 10001; SELECT suppliers.s_id, s_name,f_name, f_price FROM fruits INNER JOIN suppliers ON fruits.s_id = suppliers.s_id ORDER BY fruits.s_id;
【例.46】在fruits表和suppliers表之间使用内连接查询,查询之前,查看两个表的结构
SELECT suppliers.s_id, s_name,f_name, f_price FROM fruits ,suppliers WHERE fruits.s_id = suppliers.s_id;
【例.47】在fruits表和suppliers表之间使用INNER JOIN
语法进行内连接查询
SELECT suppliers.s_id, s_name,f_name, f_price FROM fruits INNER JOIN suppliers ON fruits.s_id = suppliers.s_id;
【例.48】查询供应f_id='a1'的水果供应商提供的其他水果种类
SELECT 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';
【例.49】在customers表和orders表中,查询所有客户,包括没有订单的客户,SQL语法如下
SELECT customers.c_id, orders.o_num FROM customers LEFT OUTER JOIN orders ON customers.c_id = orders.c_id;
【例.50】在customers表和orders表中,查询所有订单,包括没有客户的订单
SELECT customers.c_id, orders.o_num from customers RIGHT OUTER JOIN orders ON customers.c_id = orders.c_id;
【例.51】在customers表和orders表中,使用INNER JOIN
语法查询customers表中ID为10001、的客户的订单信息
SELECT customers.c_id, orders.o_num FROM customers INNER JOIN orders ON customers.c_id = orders.c_id AND customers.c_id = 10001;
【例.52】在fruits表和suppliers表之间使用INNER JOIN
语法进行内连接查询,并对查询结果排序
SELECT suppliers.s_id, s_name,f_name, f_price FROM fruits INNER JOIN suppliers ON fruits.s_id = suppliers.s_id ORDER BY fruits.s_id;
更多关于MySQL相关内容感兴趣的读者可查看本站专题:《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》
希望本文所述对大家MySQL数据库计有所帮助。
分享文章:MySQL连接查询实例详解
路径分享:http://ybzwz.com/article/jccehe.html