ch07 table.sql

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

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

 

 
DROP TABLE students



USE ElecTravelCom
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 ElecTravelCom
CREATE TABLE productSales(
productName VARCHAR(32) NOT NULL,
unitPrice DECIMAL(10, 2) NOT NULL,
qty INT NOT NULL,
amount AS unitPrice*qty )     -- amount列是由表示单价的unitPrice列和表示数量的qty列计算得到



DROP TABLE productSales


USE ElecTravelCom
CREATE TABLE productSales(
productName VARCHAR(32) NOT NULL,
unitPrice DECIMAL(10, 2) NOT NULL,
qty INT NOT NULL,
amount AS unitPrice*qty PERSISTED)     -- amount列是由表示单价的unitPrice列和表示数量的qty列计算得到




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





USE ElecTravelCom
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 ElecTravelCom
GO
CREATE TABLE abc_table (column_a INT) 
GO
INSERT INTO abc_table VALUES(1)
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 ElecTravelCom
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 ElecTravelCom
GO
CREATE TABLE abc_table (column_a INT, column_b VARCHAR(20) NULL) 
GO
ALTER TABLE abc_table ALTER COLUMN column_a VARCHAR(128)
ALTER TABLE abc_table ALTER COLUMN column_b INT
GO

DROP TABLE abc_table


USE ElecTravelCom
GO
CREATE TABLE abc_table (column_a INT, column_b VARCHAR(20) NULL) 
GO
INSERT INTO abc_table VALUES(1, N'管理表示例')
GO
ALTER TABLE abc_table ALTER COLUMN column_a VARCHAR(128)
ALTER TABLE abc_table ALTER COLUMN column_b INT
GO



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




DROP TABLE DCInfo


USE ElecTravelCom
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