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)
);

-- 创建fruits
CREATE TABLE fruits
(
f_id CHAR(10) not null,
s_id int not null,
f_name CHAR(255) not NULL,
f_price DECIMAL(8,2) not NULL,
PRIMARY KEY(f_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‘,‘20000‘,‘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‘);

INSERT INTO fruits
(f_id,s_id,f_name,f_price)
VALUES
(‘a1‘,101,‘apple‘,5.2),
(‘b1‘,101,‘blackberry‘,10.2),
(‘bs1‘,102,‘orange‘,11.2),
(‘bs2‘,105,‘melon‘,8.2),
(‘t1‘,102,‘banana‘,10.3),
(‘t2‘,102,‘grape‘,5.3),
(‘o2‘,103,‘cocount‘,9.2),
(‘co‘,101,‘cherry‘,3.2),
(‘a2‘,103,‘apricot‘,2.2),
(‘l2‘,104,‘lemon‘,6.4),
(‘b2‘,104,‘berry‘,7.6),
(‘m1‘,106,‘mango‘,16.5),
(‘m2‘,105,‘xbabay‘,2.6),
(‘t4‘,107,‘xbababa‘,3.6),
(‘m3‘,105,‘xxtt‘,11.6),
(‘b5‘,107,‘xxxx‘,3.6)

SELECT f_id,suppliers.s_id,fruits.s_id,s_name,f_name,f_price from fruits,suppliers where fruits.s_id=suppliers.s_id

SELECT f_id,suppliers.s_id,fruits.s_id,s_name,f_name,f_price from fruits
INNER JOIN suppliers on fruits.s_id=suppliers.s_id

SELECT f1.f_id,f1.f_name,f1.s_id,f2.s_id,f2.f_id,f2.f_name from fruits as f1,fruits as f2
where f1.s_id=f2.s_id and f2.f_id=‘a1‘;

SELECT * from fruits where s_id=101

-- 创建表customers
CREATE TABLE customers
(
c_id int not null auto_increment,
c_name CHAR(50) not NULL,
c_address CHAR(50) NULL,
c_city CHAR(50) NULL,
c_zip CHAR(50) NULL,
c_contact CHAR(50) NULL,
c_email CHAR(255) NULL,
PRIMARY KEY(c_id)
);

-- 插入数据
insert into customers
(c_id,c_name,c_address,c_city,c_zip,c_contact,c_email)
VALUES
(10001,‘RedHook‘,‘200 Street‘,‘Tianjin‘,‘300000‘,‘LiMing‘,‘LMing@163.com‘),
(10002,‘Stars‘,‘333 Fromage Lane‘,‘Dalian‘,‘116000‘,‘Zhangbo‘,‘jerry@hotmail.com‘),
(10003,‘Nethood‘,‘1 Sunny Place‘,‘Qingdao‘,‘266000‘,‘LuoCong‘,null),
(10004,‘JOTO‘,‘829 Riverside Drive‘,‘Haikou‘,‘570000‘,‘YangShan‘,‘sam@hotmail.com‘);