ch03 transact-sql.sql

以Microsoft SQL Server为代表的数据库产品具有超大容量的数据存储、高效率的数据查询算法、方便易用的向导和工具及友好亲切的用户接口

USE AdventureWorks
SELECT *
FROM HumanResources.Employee 


 

USE ElecTravelCom
CREATE TABLE SaleManager.ContactDetail(
contactID INT NOT NULL,
productID INT NOT NULL,
amount INT,
unitprice NUMERIC(8, 2),
otherInfo VARCHAR(256))

USE ElecTravelCom
ALTER TABLE SaleManager.ContactDetail
ADD productName VARCHAR(128) NULL
GO
SELECT *
FROM SaleManager.ContactDetail







USE ElecTravelCom
SELECT *
FROM SaleManager.ContactDetail
GO 
DROP TABLE SaleManager.ContactDetail
GO
SELECT *
FROM SaleManager.ContactDetail




USE ElecTravelCom
CREATE TABLE SaleManager.ContactDetail(
contactID INT NOT NULL,
productID INT NOT NULL,
productName VARCHAR(128),	
amount INT,
unitprice NUMERIC(8, 2),
otherInfo VARCHAR(256))








USE ElecTravelCom
INSERT INTO SaleManager.ContactDetail VALUES(69, 330, N'旅行箱', 2500, 1800, N'旅游时使用的旅行箱')
INSERT INTO SaleManager.ContactDetail VALUES(72, 351, N'望远镜', 2800, 1225, NULL)
INSERT INTO SaleManager.ContactDetail VALUES(75, 362, N'照相机', 3000, 3080, NULL)
GO
SELECT *
FROM SaleManager.ContactDetail











USE ElecTravelCom
UPDATE SaleManager.ContactDetail
SET amount = 3000,
	unitPrice = 990
WHERE productID = 330
GO
SELECT *
FROM SaleManager.ContactDetail








USE ElecTravelCom
DELETE SaleManager.ContactDetail
WHERE productID = 351
GO
SELECT *
FROM SaleManager.ContactDetail



USE ETravelCom
GRANT SELECT ON G


CREATE TABLE Person.HRInfo(empID INT)




USE ElecTravelCom
GRANT SELECT ON SaleManager.ContactDetail TO Cleon
GO
GRANT SELECT ON SaleManager.ContactDetail TO GManager
GO
REVOKE SELECT ON SaleManager.ContactDetail FROM Cleon
GO
DENY SELECT ON SaleManager.ContactDetail TO Cleon
GO



CREATE TABLE accounting(
accountID INT,
customerName VARCHAR(30),
amount DECIMAL(8, 2),
actionDate DATETIME)

USE ElecTravelCom
INSERT INTO accounting VALUES(201, N'北京丽音科技公司', 36100, N'2009-11-11')
INSERT INTO accounting VALUES(202, N'全达快递公司', 9200, N'2009-11-12')
INSERT INTO accounting VALUES(203, N'博闻咨询公司', 22390, N'2009-11-12')
INSERT INTO accounting VALUES(204, N'客满多零售公司', 62300, N'2009-11-16')
INSERT INTO accounting VALUES(205, N'日日升公司', 88600, N'2009-11-18')



USE ElecTravelCom
SELECT *
FROM accounting






USE ElecTravelCom
BEGIN TRANSACTION
UPDATE accounting SET amount = amount - 8000, actionDate = getdate() WHERE accountID = 202
IF (@@error = 0)
	BEGIN 
	 	UPDATE accounting SET amount = amount + 8000, actionDate = getdate() WHERE accountID = 205
		IF (@@error = 0)
		 	COMMIT TRANSACTION     -- 两次操作都成功时提交事务
		ELSE
			ROLLBACK TRANSACTION   -- 第二次操作失败时取消事务
	END
ELSE
	ROLLBACK TRANSACTION           -- 第一次操作失败时取消事务




UPDATE accounting SET  actionDate = N'2009-11-25' WHERE accountID = 202
UPDATE accounting SET  actionDate = N'2009-11-25' WHERE accountID = 205




USE ElecTravelCom
SELECT *
FROM accounting






SET QUOTED_IDENTIFIER OFF
GO
CREATE TABLE "Employee Info"(
empName VARCHAR(256))




DROP TABLE "Employee Info"



SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE "Employee Info"(
empName VARCHAR(256))





DECLARE @yearCounter INT
SET @yearCounter = 1
WHILE (@yearCounter < 2020) SET @yearCounter = @yearCounter + 1
PRINT @yearCounter




SELECT 12.0/5.0, 12/5, 12.0/15.0, 12/15, 12%7





USE AdventureWorks
SELECT *
FROM person.Contact
WHERE contactID <10 AND Title = 'Mr.' OR FirstName = 'Tom'




DECLARE @Ename VARCHAR(128)
SET @Ename = 'Cleon'
GO
SELECT FIRSTNAME = FirstName, LASTNAME = LastName
FROM person.Contact



USE AdventureWorks
SELECT FullInfo = Title + ' ' + FirstName + ' ' + LastName + ', Telephone number is ' + Phone
FROM person.Contact





SELECT 20&12, 20|12, 20^12




SELECT ContactID, FirstName
FROM person.Contact
WHERE ContactID < 10 AND FirstName > 'Kim'
SELECT ContactID, FirstName
FROM person.Contact
WHERE ContactID < 10 AND FirstName <= 'Kim'





USE AdventureWorks
SELECT NameList = Title + ' ' + SUBSTRING(FirstName, 1, 1) + '. ' + LastName
FROM person.Contact





WHILE (SELECT AVG(ListPrice) FROM Production.Product) < 500
BEGIN
	UPDATE Production.Product
		SET ListPrice = ListPrice * 2
	SELECT MAXPrice = MAX(listPrice) FROM Production.Product
	IF (SELECT MAX(ListPrice) FROM Production.Product) > 800
		BREAK
	ELSE
		CONTINUE
END




BEGIN TRY
	SELECT 3/0                -- 显示以0为除数的错误
END TRY
BEGIN CATCH                   -- 处理错误逻辑
	SELECT	ERROR_NUMBER()    AS N'错误号',
			ERROR_SEVERITY()  AS N'错误严重等级',
			ERROR_STATE()     AS N'错误状态',
			ERROR_PROCEDURE() AS N'产生错误的过程名称',
			ERROR_LINE()      AS N'产生错误的行号',
			ERROR_MESSAGE()   AS N'错误消息文本'
END CATCH





SELECT * FROM not_found_table
GO
SELECT @@ERROR AS N'第一个错误号'
GO
SELECT 5 + 6   AS N'两个数之和'
GO
SELECT @@ERROR AS N'第二个错误号'
GO




/*
程序名称：创建ContactHead表的脚本
作者：微软公司
最后一次修改日期：2005年12月10日
程序描述：用户存储合同的基本概况信息
*/
USE ElecTravelCom                   -- 当前数据库名称
CREATE TABLE Contacts(              -- 表名称ContactHead
ContactID INT NOT NULL PRIMARY KEY, -- 合同编码，每一个合同只能有一个唯一性的编码
customerName VARCHAR(128),          -- 签订合同的客户名称
salePersonName VARCHAR(36),         -- 签订合同的员工名称
issueDate DATETIME,                 -- 合同签订日期
other VARCHAR(128))                 -- 备注



USE ElecTravelCom
CREATE TABLE test_integer_datatype(
col1 INT)
GO
INSERT INTO test_integer_datatype VALUES(12345678987654321)







USE ElecTravelCom
CREATE TABLE test_money_datatype(
col1 MONEY, col2 MONEY, col3 MONEY, col4 MONEY, col5 MONEY)
GO
INSERT INTO test_money_datatype VALUES(123, $123, 123.456, 123.45672, 123.45678)
GO
SELECT *
FROM test_money_datatype


USE ElecTravelCom
CREATE TABLE test_datetime_datatype(
col1 DATETIME)
GO
SET DATEFORMAT mdy
INSERT INTO test_datetime_datatype VALUES('05/12/2009')
GO
SET DATEFORMAT ymd
INSERT INTO test_datetime_datatype VALUES('2009/05/12')
GO
SELECT *
FROM test_datetime_datatype




USE ElecTravelCom
CREATE TABLE test_binary_datatype(
col1 BINARY(10), col2 BINARY(10), col3 BINARY(10))
GO
INSERT INTO test_binary_datatype VALUES(0, 16, 9876543210)
GO
SELECT *
FROM test_binary_datatype





USE AdventureWorks
SELECT N'人员数量' = COUNT(*), 
       N'最高奖金额' = MAX(Bonus), 
       N'最低奖金额' = MIN(Bonus), 
       N'平均奖金额' = AVG(Bonus), 
       N'奖金额方差' = Stdev(Bonus)
FROM Sales.SalesPerson







SELECT N'语言标识符' = @@LANGID, 
	   N'语言名称'   = @@LANGUAGE, 
       N'系统版本号' = @@VERSION, 
       N'服务名称'   = @@SERVICENAME, 
       N'服务器名称' = @@SERVERNAME



SELECT N'自然对数' = LOG(10), 
       N'指数'     = EXP(12), 
       N'符号'     = SIGN(2), 
       N'圆周率'   = PI(), 
       N'正弦'     = SIN(PI()/2.0), 
       N'余弦'     = COS(PI()/2.0)









USE AdventureWorks
SELECT i.LocationID, i.Quantity,
       RANK() OVER (PARTITION BY i.LocationID order by i.Quantity) as RANK,
       DENSE_RANK() OVER (PARTITION BY i.LocationID order by i.Quantity) as DENSE_RANK,
       i.ProductID, p.Name
FROM Production.ProductInventory i JOIN Production.Product p 
ON i.ProductID = p.ProductID 
ORDER BY i.LocationID





USE AdventureWorks
SELECT FirstName, 
       N'名称长度' = LEN(FirstName), 
       N'名称前3个字母的大写形式' = UPPER(SUBSTRING(FirstName, 1, 3)), 
       N'名称复制3遍' = REPLICATE(FirstName, 5)
FROM Person.Contact





USE AdventureWorks
SELECT N'带有头衔的名单' = FirstName + ' ' + LastName + CASE Title 
                                                          WHEN 'Mr.' THEN N' 先生'
                                                          WHEN 'Ms.' THEN N' 女士'
                                                        END
FROM Person.Contact