MySQL连接查询实例详解
本文实例讲述了MySQL连接查询。分享给大家供大家参考，具体如下：
创建表suppliers：
CREATETABLEsuppliers ( s_idintNOTNULLAUTO_INCREMENT, s_namechar(50)NOTNULL, s_citychar(50)NULL, s_zipchar(10)NULL, s_callCHAR(50)NOTNULL, PRIMARYKEY(s_id) ); INSERTINTOsuppliers(s_id,s_name,s_city,s_zip,s_call) VALUES(101,'FastFruitInc.','Tianjin','300000','48075'), (102,'LTSupplies','Chongqing','400000','44333'), (103,'ACME','Shanghai','200000','90046'), (104,'FNKInc.','Zhongshan','528437','11111'), (105,'GoodSet','Taiyuang','030000','22222'), (106,'JustEatOurs','Beijing','010','45678'), (107,'DKInc.','Zhengzhou','450000','33332');
内连接
SELECTsuppliers.s_id,s_name,f_name,f_price FROMfruits,suppliers WHEREfruits.s_id=suppliers.s_id;
使用innerjoin语法进行内连接查询
SELECTsuppliers.s_id,s_name,f_name,f_price FROMfruits INNERJOINsuppliersONfruits.s_id=suppliers.s_id;
创建表orders：
CREATETABLEorders ( o_numintNOTNULLAUTO_INCREMENT, o_datedatetimeNOTNULL, c_idintNOTNULL, PRIMARYKEY(o_num) ); INSERTINTOorders(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);
左连接
SELECTcustomers.c_id,orders.o_num FROMcustomersLEFTOUTERJOINorders ONcustomers.c_id=orders.c_id;
右连接
SELECTcustomers.c_id,orders.o_num fromcustomersRIGHTOUTERJOINorders ONcustomers.c_id=orders.c_id;
复合条件连接查询
SELECTcustomers.c_id,orders.o_num FROMcustomersINNERJOINorders ONcustomers.c_id=orders.c_idANDcustomers.c_id=10001; SELECTsuppliers.s_id,s_name,f_name,f_price FROMfruitsINNERJOINsuppliers ONfruits.s_id=suppliers.s_id ORDERBYfruits.s_id;
【例.46】在fruits表和suppliers表之间使用内连接查询，查询之前，查看两个表的结构
SELECTsuppliers.s_id,s_name,f_name,f_price FROMfruits,suppliers WHEREfruits.s_id=suppliers.s_id;
【例.47】在fruits表和suppliers表之间使用INNERJOIN语法进行内连接查询
SELECTsuppliers.s_id,s_name,f_name,f_price FROMfruitsINNERJOINsuppliers ONfruits.s_id=suppliers.s_id;
【例.48】查询供应f_id='a1'的水果供应商提供的其他水果种类
SELECTf1.f_id,f1.f_name FROMfruitsASf1,fruitsASf2 WHEREf1.s_id=f2.s_idANDf2.f_id='a1';
【例.49】在customers表和orders表中，查询所有客户，包括没有订单的客户，SQL语法如下
SELECTcustomers.c_id,orders.o_num FROMcustomersLEFTOUTERJOINorders ONcustomers.c_id=orders.c_id;
【例.50】在customers表和orders表中，查询所有订单，包括没有客户的订单
SELECTcustomers.c_id,orders.o_num fromcustomersRIGHTOUTERJOINorders ONcustomers.c_id=orders.c_id;
【例.51】在customers表和orders表中，使用INNERJOIN语法查询customers表中ID为10001、的客户的订单信息
SELECTcustomers.c_id,orders.o_num FROMcustomersINNERJOINorders ONcustomers.c_id=orders.c_idANDcustomers.c_id=10001;
【例.52】在fruits表和suppliers表之间使用INNERJOIN语法进行内连接查询，并对查询结果排序
SELECTsuppliers.s_id,s_name,f_name,f_price FROMfruitsINNERJOINsuppliers ONfruits.s_id=suppliers.s_id ORDERBYfruits.s_id;
更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》
希望本文所述对大家MySQL数据库计有所帮助。