SQL server学习笔记

SQLSERVER笔记

alter table table_name add primary key(字段1，字段2)   添加联合主键

SELECT
	dbo.PO.FNUMBER AS 订单编号,
	dbo.Supply.FSupplyName AS 供应商名称,
	dbo.Supply.FSupplyTel AS 联系电话,
	dbo.PO.FDATE AS 订货日期,
	dbo.Materiel.FMName AS 物料名称,
	dbo.Materiel.FSpec AS 物料规格,
	dbo.Unit.FUnitNo AS 单位,
	dbo.Unit.FUnitName AS 单位名称,
	dbo.PO_Entry.FQTY AS 数量,
	dbo.PO_Entry.FPRICE AS 单价,
	dbo.PO_Entry.FAMOUNT AS 金额 
FROM
	dbo.Supply
	INNER JOIN dbo.Unit
	INNER JOIN dbo.PO_Entry
	INNER JOIN dbo.PO ON dbo.PO_Entry.FID = dbo.PO.FID
	INNER JOIN dbo.Materiel ON dbo.PO_Entry.FMID = dbo.Materiel.FMID ON dbo.Unit.FUnitID = dbo.Materiel.FUnitID ON dbo.Supply.FSupplyID = dbo.PO.FSUPPLYID

SELECT * INTO unit_bak FROM Unit   备份表

将一条表中数据插入到另一个表中

INSERT INTO unit_bak(FUnitID,FUnitNo,FUnitName) SELECT FUnitID,FUnitNo,FUnitName  FROM Unit

内连接

SELECT * FROM unit_bak as b INNER JOIN Unit as u on b.FUnitID = u.FUnitID

左连接

左表全部，右表符合条件

SELECT * FROM unit_bak as b LEFT JOIN Unit as u on b.FUnitID = u.FUnitID

将一个表中数据更新到另一个表中

SELECT * FROM unit_bak as b INNER JOIN Unit as u on b.FUnitID = u.FUnitID


UPDATE b SET b.FUnitName = u.FUnitName FROM unit_bak as b INNER JOIN Unit as u on b.FUnitID = u.FUnitID

全连接

SELECT * FROM unit_bak as b FULL JOIN Unit as u on b.FUnitID = u.FUnitID