--创建数据库 create database GoodsSystem go --使用数据库 use GoodsSystem go --创建商品类型表 create table GoodsType ( IO int primary key identity(1,1), typename varchar(10)not null ) go --创建商品信息表 create table Goods ( Id int primary key identity(1,1), Typeld int foreign key references GoodsType(IO), Name varchar(20)not null, Price decimal(10,2) not null, ProductionDate datetime not null, Amount int not null ) go
插入数据
insert into GoodsType values ('家电'), ('电子'), ('食品'), ('生活用品') insert into Goods values ('1','冰箱',3344,'2017-06-03',100), ('1','电视',1777,'2016-06-03',100), ('1','微波炉',333,'2017-02-26',100), ('2','手机',4500,'2017-05-07',100), ('2','显示器',1777,'2016-12-04',100), ('2','主机',1500,'2017-03-09',100), ('3','老干妈',9,'2017-07-06',100), ('3','爽口榨菜',3.6,'2017-06-08',100)
查询:
select * from GoodsType select * from Goods
--内连接(inner可以不写) select g1.name,g2.Name,price,productiondate,g2.Amount FROM GoodsType g1 inner join Goods g2 on g1.IO=g2.Typeld select * from Goods select * from GoodsType --外连接分(左外连接)和(右外连接) --左外连接( left join) select g2.Name,Price,ProductionDate,Amount,g1.name FROM Goods G1 left join GoodsType G2 on G1.Typeld=G2.IO --右外连接(right join--空值的会显示出来) select g2.Name,Price,ProductionDate,Amount,g1.name FROM Goods G1 right join GoodsType G2 on G1.Typeld=G2.IO --全外连接(full outer(可以不写) join--空值的会显示出来) select g1.name,g2.Name,price,productiondate,g2.Amount FROM GoodsType g1 full outer join Goods g2 on g1.IO=g2.Typeld --交叉连接(笛卡尔积)查询所有的值 select g1.name,g2.Name,price,productiondate,g2.Amount FROM GoodsType g1 cross join Goods g2 where g1.IO=g2.Typeld