MySQL连接查询实例详解资源-CSDN文库

mysql创建存储过程

需积分: 14 8 浏览量 2020-12-16 12:39:39 上传评论 1 收藏 43KB PDF 举报

资源详情

资源评论

资源推荐

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

本内容试读结束，登录后可阅读更多

下载后可阅读完整内容，剩余1页未读，立即下载

评论0

内容反馈

weixin_38743506

粉丝: 349
资源: 2万+

最新资源

资源上传下载、课程学习等过程中有任何疑问或建议，欢迎提出宝贵意见哦~我们会及时处理！点击此处反馈

feedback-tip