ch06.sql

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

USE ETravelCom
CREATE TABLE students(
studentNumber INT NOT NULL,
studentName VARCHAR(32) NOT NULL,
gender CHAR(2) NULL,
birthday DATETIME NULL,
hometown VARCHAR(32) NULL,
telphone_no VARCHAR(32) NULL,
address VARCHAR(128) NULL,
others VARCHAR(256) NULL)
 
DROP TABLE students


USE ETravelCom
CREATE TABLE orders(
order_no CHAR(10) NOT NULL,
customer_no CHAR(10) NOT NULL,
product_no VARCHAR(20) NOT NULL,
amount MONEY NOT NULL,
order_date DATETIME NOT NULL)


USE ETravelCom
CREATE TABLE productSales(
productName VARCHAR(32) NOT NULL,
unitPrice DECIMAL(10, 2) NOT NULL,
qty INT NOT NULL,
amount AS unitPrice*qty )

DROP TABLE productSales


USE ETravelCom
CREATE TABLE productSales(
productName VARCHAR(32) NOT NULL,
unitPrice DECIMAL(10, 2) NOT NULL,
qty INT NOT NULL,
amount AS unitPrice*qty PERSISTED)



USE ETravelCom
CREATE TABLE ##studentsInfo(
studentNumber INT NOT NULL,
studentName VARCHAR(32) NOT NULL,
gender CHAR(2) NULL,
birthday DATETIME NULL,
hometown VARCHAR(32) NULL,
telphone_no VARCHAR(32) NULL,
address VARCHAR(128) NULL,
others VARCHAR(256) NULL)


USE ETravelCom
GO
CREATE TABLE abc_table (column_a INT) 
GO
EXEC sp_help abc_table
GO
ALTER TABLE abc_table ADD column_b VARCHAR(20) NULL
GO
EXEC sp_help abc_table
GO

DROP TABLE abc_table





USE ETravelCom
GO
CREATE TABLE abc_table (column_a INT) 
GO
INSERT INTO abc_table VALUES(123)
GO
EXEC sp_help abc_table
GO
ALTER TABLE abc_table ADD column_b VARCHAR(20) NOT NULL
GO
EXEC sp_help abc_table
GO


USE ETravelCom
GO
CREATE TABLE abc_table ( column_a INT, column_b VARCHAR(20) NULL) 
GO
EXEC sp_help abc_table
GO
ALTER TABLE abc_table DROP COLUMN column_b
GO
EXEC sp_help abc_table
GO

DROP TABLE abc_table

USE ETravelCom
GO
CREATE TABLE abc_table (column_a INT, column_b VARCHAR(20) NULL) 
GO
INSERT INTO abc_table VALUES(1, 'Database')
GO
ALTER TABLE abc_table ALTER COLUMN column_a VARCHAR(128)
ALTER TABLE abc_table ALTER COLUMN column_b INT
GO


USE ETravelCom
CREATE TABLE DCInfo(
DCSerial INT IDENTITY(1, 1),
DCName VARCHAR(32),
others VARCHAR(128))

DROP TABLE DCInfo


USE ETravelCom
CREATE TABLE DCInfo(
DCSerial UNIQUEIDENTIFIER DEFAULT NEWID(),
DCName VARCHAR(32),
others VARCHAR(128))

USE ETravelCom
SELECT COLUMNPROPERTY(OBJECT_ID('DBO.DCInfo'),'DCSerial','IsRowGuidCol'),
	   COLUMNPROPERTY(OBJECT_ID('DBO.DCInfo'),'DCName','IsRowGuidCol')
GO

sp_help DCInfo