ch05.sql

SQLServer2005基础教程,清华大学出版社

USE AdventureWorks
SELECT *
FROM person.Contact 

DROP TABLE person.ContactDetail

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


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






USE ETravelCom
SELECT *
FROM GManager.ContactDetail
GO 
DROP TABLE GManager.ContactDetail
GO
SELECT *
FROM GManager.ContactDetail





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

INSERT INTO GManager.ContactDetail VALUES(2, 115, N'自动铅笔', 2500, 7, N'考试用铅笔，也可以用于其他')
INSERT INTO GManager.ContactDetail VALUES(2, 119, N'文具袋', 2800, 12, NULL)
GO
SELECT *
FROM GManager.ContactDetail

USE ETravelCom
UPDATE GManager.ContactDetail
SET amount = 2950,
	unitPrice = 9.1
WHERE productID = 115
GO
SELECT *
FROM GManager.ContactDetail





USE ETravelCom
DELETE GManager.ContactDetail
WHERE productID = 119
GO
SELECT *
FROM GManager.ContactDetail

USE ETravelCom
GRANT SELECT ON G


CREATE TABLE Person.HRInfo(empID INT)


USE ETravelCom
GRANT SELECT ON Person.HRInfo TO Peter
GO
GRANT SELECT ON Person.HRInfo TO GManager
GO
REVOKE SELECT ON Person.HRInfo FROM Peter
GO
DENY SELECT ON Person.HRInfo TO Peter
GO


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

USE ETravelCom
INSERT INTO accounting VALUES(101, N'维兴公司', 6100, N'2008-12-12')
INSERT INTO accounting VALUES(102, N'达津公司', 39200, N'2008-12-15')
INSERT INTO accounting VALUES(103, N'博策公司', 23190, N'2008-12-15')
INSERT INTO accounting VALUES(104, N'金利多公司', 22300, N'2008-12-21')
INSERT INTO accounting VALUES(105, N'海利公司', 586100, N'2008-12-22')


USE ETravelCom
SELECT *
FROM accounting


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


UPDATE accounting SET  actionDate = N'2008-12-25' WHERE accountID = 102
UPDATE accounting SET  actionDate = N'2008-12-25' WHERE accountID = 105


USE ETravelCom
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 < 2010) 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 = 'Tom'
PRINT @Ename
GO
SELECT FIRSTNAME = FirstName
FROM person.Contact

USE AdventureWorks
SELECT 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 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 MAX(listPrice) FROM Production.Product
	IF (SELECT MAX(ListPrice) FROM Production.Product) > 800
		BREAK
	ELSE
		CONTINUE
END


BEGIN TRY
	SELECT 2/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


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


CREATE TABLE test_integer_datatype(
col1 INT)
GO
INSERT INTO test_integer_datatype VALUES(9876543210)





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


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


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 COUNT(*), MAX(Bonus), MIN(Bonus), AVG(Bonus), Stdev(Bonus)
FROM Sales.SalesPerson


SELECT @@LANGID, @@LANGUAGE, @@VERSION, @@SERVICENAME, @@SERVERNAME

SELECT LOG(10), EXP(12), SIGN(2), PI(), SIN(PI()/2.0), COS(PI()/2.0)






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, LEN(FirstName), UPPER(SUBSTRING(FirstName, 1, 5)), REPLICATE(FirstName, 8)
FROM Person.Contact



USE AdventureWorks
SELECT FirstName + ' ' + LastName + CASE Title 
                                        WHEN 'Mr.' THEN N' 先生'
                                        WHEN 'Ms.' THEN N' 女士'
                                    END
FROM Person.Contact