新建_文本文.txt

这是我们小组的sql server 2000培训笔记,主要是查询、修改、增加、删除等方面的内容

begin tran
commit tran

-- 删除数据库
drop database sample
  (正在删除数据库文件 'd:\Program Files\Microsoft SQL Server\MSSQL\data\sample_log.LDF'。
   正在删除数据库文件 'd:\Program Files\Microsoft SQL Server\MSSQL\data\sample.mdf'。
  )

-- 创建数据库
create database sample
  (CREATE DATABASE 进程正在磁盘 'sample' 上分配 0.75 MB 的空间。
   CREATE DATABASE 进程正在磁盘 'sample_log' 上分配 0.49 MB 的空间。
  )

-- 创建“字符数据”表  
use sample
go
create table chardata(char_string char(5),varchar_string varchar(5),text_string text)
go

-- 向“字符数据”表中添加两行数据
INSERT INTO chardata
VALUES ('test', 'test', 'this is a test for text data type')
go
INSERT INTO chardata
VALUES ('this is a test for char data type', 'this is a test for varchar data type', 'this is a test for text data type')
go

-- -- 从“字符数据”表中检索数据
SELECT *
FROM chardata
GO

-- 创建“整型数据”表
CREATE TABLE intnumber
	(bigint_number bigint,
	int_number int,
	smallint_number smallint,
	tinyint_number tinyint)
GO

-- 向“整型数据”数据表中添加两行数据
INSERT INTO intnumber
VALUES (9223372036854775807, 2147483647, 32767, 255)
go
INSERT INTO intnumber
VALUES (-9223372036854775808, -2147483648, -32768, 0)
GO

-- 从“整型数据”数据表中检索数据
SELECT *
FROM intnumber
GO

-- 创建“数字数据”表
CREATE TABLE numericd
	(
        decimal_number decimal (8, 6),
	numeric_number numeric (5, 3)
	)
GO

-- 向“数字数据”数据表中添加两行数据
INSERT INTO numericd
VALUES (99.99999788，50)
GO
INSERT INTO numericd
VALUES (99.999999，300)
GO

-- 从“数字数据”数据表中检索数据
SELECT *
FROM numericd
GO

--创建“货币数据”数据表
CREATE TABLE moneydata
(
money_number money,
smallmoney_number smallmoney
)
go

-- 向数据表中添加两条合理记录
INSERT INTO moneydata
VALUES ($123.45, $1)
go
INSERT INTO moneydata
VALUES ($123.45, $111111.45)
GO


-- 向数据表中添加带有逗号分隔符的货币数据
INSERT INTO moneydata
VALUES ($123.45, CAST('$111,111.45' AS MONEY) )
GO

-- 从数据表中检索数据
SELECT * FROM moneydata
GO 

--创建“二进制数据”数据表
CREATE TABLE binarydata
(
binary_number binary(6),vbinary_number varbinary(6)
)
GO

-- 向数据表中添加三条记录
INSERT INTO binarydata
VALUES (0xABCDE, 0xABCDE)
go
INSERT INTO binarydata
VALUES (0xABCDEA, 0x46F)
go
INSERT INTO binarydata
VALUES (0xABCDEABCEeff, 0x46F)
GO

-- 从数据表中检索数据
SELECT * FROM binarydata

--创建“特殊数据”数据表
CREATE TABLE specdata
(
sql_variant_data sql_variant
)
GO

-- 向数据表中添加几条带有不同数据类型的记录
INSERT INTO specdata
VALUES ('This is a sql_variant data type test')
go
INSERT INTO specdata
VALUES (123456)
go
INSERT INTO specdata
VALUES ('April 15, 1998')
GO

-- 从数据表中检索数据
SELECT * FROM specdata
GO 



-- 定义变量(temp table)
-- 向该变量中添加两行内容
DECLARE @Table_Example TABLE
  (Col_num int PRIMARY KEY,
   Col_text char (50))

INSERT INTO @Table_Example VALUES (1, 'this is a')

INSERT INTO @Table_Example VALUES (2, 'table data type example')

-- 从临时表中检索
SELECT * FROM @Table_Example
GO


-- 创建birthday 用户定义数据类型，它使用了 datetime 基本数据类型，允许 NULL
USE sample
EXEC sp_addtype birthday, datetime, 'NULL'
GO
-- 在数据表定义时使用birthday 用户定义数据类型
CREATE TABLE espidata
(
char_data char(20),
birthday_data birthday
)
GO
-- 向数据表中添加几条记录
INSERT INTO espidata
VALUES ('Sarah', '02/22/1976')
go
INSERT INTO espidata
VALUES ('Tina', '04/15/1998')
GO
-- 从数据表中检索数据
SELECT * FROM espidata
GO
-- 删除birthday 用户定义数据类型(delete field) first)
EXEC sp_droptype birthday

example:
CREATE DATABASE sales
ON
(NAME = 'sales_dat',
FILENAME = 'd:\program files\microsoft sql server\mssql\data\sales_dat.mdf',
SIZE = 5,
MAXSIZE = 300,
FILEGROWTH = 2
)

LOG ON 
(NAME = 'sales_log',
FILENAME = 'd:\program files\microsoft sql server\mssql\data\sales_log.ldf',
SIZE = 2MB,
MAXSIZE = 20MB,
FILEGROWTH = 2MB
)

------------------------------
CREATE DATABASE Report
ON
PRIMARY
(NAME = Rep1,
FILENAME = 'd:\program files\microsoft sql server\mssql\data\rep1_dat.mdf',
SIZE = 5MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB),
(NAME = Rep2,
FILENAME = 'd:\program files\microsoft sql server\mssql\data\rep2_dat.ndf',
SIZE = 5MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB),
(NAME = Rep3,
FILENAME = 'd:\program files\microsoft sql server\mssql\data\rep3_dat.ndf',
SIZE = 5MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB)

LOG ON
(NAME = Rep1_log,
FILENAME = 'd:\program files\microsoft sql server\mssql\data\rep1_log.ldf',
SIZE = 5MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB),
(NAME = Rep2_log,
FILENAME = 'd:\program files\microsoft sql server\mssql\data\rep2_log.ldf',
SIZE = 5MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB)
GO

-------------------------
EXEC sp_dboption 'sales', 'SINGLE USER', True
EXEC sp_renamedb 'sales', 'product'
EXEC sp_dboption 'product', 'SINGLE USER', False