MySQL连接查询实例详解

数据库  /  管理员 发布于 7年前   266

本文实例讲述了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_priceFROM fruits ,suppliersWHERE fruits.s_id = suppliers.s_id;

使用 inner join 语法进行内连接查询

SELECT suppliers.s_id, s_name,f_name, f_priceFROM fruitsINNER 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_numFROM customers LEFT OUTER JOIN ordersON customers.c_id = orders.c_id;

右连接

SELECT customers.c_id, orders.o_numfrom customers RIGHT OUTER JOIN ordersON customers.c_id = orders.c_id;

复合条件连接查询

SELECT customers.c_id, orders.o_numFROM customers INNER JOIN ordersON customers.c_id = orders.c_id AND customers.c_id = 10001;SELECT suppliers.s_id, s_name,f_name, f_priceFROM fruits INNER JOIN suppliersON fruits.s_id = suppliers.s_idORDER BY fruits.s_id;

【例.46】在fruits表和suppliers表之间使用内连接查询，查询之前，查看两个表的结构

SELECT suppliers.s_id, s_name,f_name, f_priceFROM fruits ,suppliersWHERE fruits.s_id = suppliers.s_id;

【例.47】在fruits表和suppliers表之间使用INNER JOIN语法进行内连接查询

SELECT suppliers.s_id, s_name,f_name, f_priceFROM fruits INNER JOIN suppliersON fruits.s_id = suppliers.s_id;

【例.48】查询供应f_id='a1'的水果供应商提供的其他水果种类

SELECT f1.f_id, f1.f_nameFROM fruits AS f1, fruits AS f2WHERE f1.s_id = f2.s_id AND f2.f_id = 'a1';

【例.49】在customers表和orders表中，查询所有客户，包括没有订单的客户，SQL语法如下

SELECT customers.c_id, orders.o_numFROM customers LEFT OUTER JOIN ordersON customers.c_id = orders.c_id;

【例.50】在customers表和orders表中，查询所有订单，包括没有客户的订单

SELECT customers.c_id, orders.o_numfrom customers RIGHT OUTER JOIN ordersON customers.c_id = orders.c_id;

【例.51】在customers表和orders表中，使用INNER JOIN语法查询customers表中ID为10001、的客户的订单信息

SELECT customers.c_id, orders.o_numFROM customers INNER JOIN ordersON 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_priceFROM fruits INNER JOIN suppliersON fruits.s_id = suppliers.s_idORDER BY fruits.s_id;

更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》

希望本文所述对大家MySQL数据库计有所帮助。