SQL|创建表 JOIN 视图

Q&A:如何实现多个表链式连接?

-- CREATE SCHEMA SQLCOURSE

CREATE TABLE SQLCOURSE.tblFood
(
    /*
    自增id
    产品标准号
    食品名
    配料表
    营养成分表
    生产商编号
    价格
    生产日期tblFood
    保质期
    */
    fid int auto_increment,
    fStdNum varchar(15),
    fName varchar(20) not null,
    fIngrelist varchar(300),
    fNutriLabel varchar(300),
    fManuCode varchar(5),
    fPrice decimal,
    fProdDate varchar(25),
    fShelfLife varchar(25),
    primary key(fid,fStdNum)
);

-- drop table SQLCOURSE.tblFood

CREATE TABLE SQLCOURSE.tblManu
(   
    mid int auto_increment,
    fManuCode varchar(5),
    fManuInfo varchar(50),
    primary key(mid,fManuCode)
);

INSERT 
INTO SQLCOURSE.tblFood(fStdNum,fName,fManuCode)
VALUE('GB17401','上好佳薯条番茄味','R');

INSERT
INTO SQLCOURSE.tblManu(fManuCode,fManuInfo)
VALUE('R','湖北上好佳食品工业有限公司');

SELECT tblFood.*,tblManu.fManuInfo
FROM tblFood
LEFT JOIN tblManu
ON tblFood.fManuCode = tblManu.fManuCode

CREATE TABLE SQLCOURSE.tblEquipObject
(
    /*
        自增id
        装备名称
        缩略图
        部位代码
        限制等级
        限制职业
        装备品级
        基本属性
        获得方式
        说明
    */
    eid int auto_increment,
    eName varchar(30) not null,
    eImg longblob,
    ePartCode varchar(2),
    eLimitLevel int,
    eLimitCareer varchar(2),
    eGrade varchar(2),
    eProperty varchar(50),
    eGainMethod varchar(50),
    eDesc varchar(100),
    primary key(eid,eName)
);

CREATE TABLE SQLCOURSE.tblPartInfo
(
    pid int auto_increment,
    ePartCode varchar(2),
    ePartInfo varchar(20),
    primary key(pid,ePartCode)
);

CREATE TABLE SQLCOURSE.tblPlayer
(
    playerid int auto_increment,
    pStdId varchar(20),
    eName varchar(12),
    primary key(playerid,pStdId)
);

-- drop table tblPlayer

INSERT 
INTO tblPlayer(pStdId,eName)
VALUE('0001','修好的帝国短袍');

insert
into tblPartInfo(ePartCode,ePartInfo)
value('B','BodyDefense');

insert 
into tblEquipObject(
    eName,
    ePartCode,
    eLimitCareer,
    eGrade,
    eProperty,
    eGainMethod,
    eDesc
    )
value('修好的帝国短袍','B','NA','S','PHYS:6,MAGIC:13','任务奖励','道具分解:裁衣匠');

create view vwPlayerEquip
as
select tblPlayer.pStdId,tblPlayer.eName,tblEquipObject.eProperty,tblEquipObject.ePartCode
from tblPlayer
left join tblEquipObject 
on (tblPlayer.eName = tblEquipObject.eName) 
inner join tblPartInfo C
on (tblEquipObject.ePartCode = C.ePartCode)