练习了一下SQL SERVER，主要是建表建约束和时间函数

use SqlTest

create table Categories

(  

CategoryID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,  

CategoryName VARCHAR(100) ) GO create table Products (  ProductID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,  

ProductName VARCHAR(300) NOT NULL,  CategoryID INT NOT NULL,

 CONSTRAINT FK_PRODUCTS_TOCATEGORIES FOREIGN KEY(CategoryID) REFERENCES Categories(CategoryID),

 ProductNum INT DEFAULT(0) NOT NULL,  

MfgDate DATETIME DEFAULT(GETDATE()) NOT NULL,  ExpDate DATETIME DEFAULT (GETDATE()) NOT NULL )

INSERT INTO Categories VALUES('饮品')

INSERT INTO Categories VALUES('家电')

INSERT INTO Categories VALUES('生鲜')

DELETE FROM Categories INSERT INTO Products VALUES('果粒橙',4,500,DEFAULT,'2013-12-2')

INSERT INTO Products VALUES('阆中酱油',6,500,DEFAULT,'2013-12-2')

INSERT INTO Products VALUES('格力空调',5,500,DEFAULT,'2013-12-2')

SELECT*FROM Categories SELECT*FROM Products

GO /*指定日期加上一段时间基础*

/ SELECT MFGDATE,ExpDate,DateADD(N,2,ExpDate) as ChangeExp FROM Products

/*返回两个日期的时间边界数*/

SELECT DATEDIFF(HOUR,MFGDATE,EXPDATE) FROM Products

/*返回指定日期的指定部分整数*/

SELECT DATEPART(HOUR,EXPDATE) FROM Products

/*返回代表指定日期部分的字符串*/

SELECT DATENAME(W,Expdate) From Products

SELECT 当前日期=CONVERT(varchar(8),GETDATE(),126), 当前时间=CONVERT(Varchar(8),getdate(),114)

SELECT DATENAME(DW,'2004-10-15') SELECT 本年第几周=DATENAME(WEEK,GETDATE()) ,今天是周几=DATENAME(weekday,GETDATE())

转载于:https://www.cnblogs.com/jasonyang/archive/2013/04/25/3042091.html