练习了一下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())