• SQLServer相关操作
  • 创建数据库
  • 修改数据库
  • 删除数据库
  • 查看数据库信息
  • 创建数据表
  • 修改数据表
  • 删除数据表
  • 查询数据表
  • 添加数据
  • 创建视图
  • 修改视图
  • 删除视图
  • 创建索引
  • 修改索引
  • 删除索引
  • 查询索引
  • 更改索引
  • 创建数据库用户账号

创建数据库

CREATE DATABASE 数据库名称
[ON
[FILEGROUP 文件组名称]
(NAME = 数据库文件逻辑名,FILENAME = '路径 + 数据库文件名',SIZE = 数据文件初始大小,MAXSIZE = 数据文件最大容量,FILEGROWTH = 数据文件自动增长容量,)]
[LOG ON
(NAME = 日志文件逻辑名称,FILENAME = '路径 + 日志文件名',SIZE = 日志文件初始大小,MAXSIZE = 日志文件最大容量,FILEGROWTH = 日志文件自动增长容量)]
[COLLATE 数据库校验方式名称]
[FOR ATTACH]

例：新建一个数据库Teach，数据文件的逻辑名称为Teach_Data，数据文件存放在E盘根目录下，文件名称为Teach_Data.mdf；日志文件的逻辑名称为Teach_Log，日志文件的物理地存放在E盘根目录下，文件名为TeachData.ldf

CREATE DATAVASE Teach
ON
(NAME = Teach_Data,FILENAME = 'E:\TeachData.mdf',SIZE = 10,MAXSIZE = 500,FILEGROWTH = 10)
LOG ON 
(NAME = Teach_Log,
FILENAME = 'E:\TeachData.ldf',
SIZE = 5,
MAXSIZE = 500,
FILEGROWTH = 5)

---

修改数据库

ALTER DATABASE 数据库名称
ADD FILE(具体文件格式) [,...n]
[TO FILEGROUP 文件组名]
[ADD LOG FILE(具体文件格式) [,...n]
|REMOVE FILE 文件逻辑名称
|MODIFY FILE(具体文件格式)
|ADD FILEGROUP 文件组名
|REMOVE FILEGROUP 文件组名
|MODIFY FILEGROUP 文件组名
{READ_ONLY|READ_WRITE,|DEFAULT,|NAME = 新建文件组名}
]

例： 修改Teach数据库中的Teach_Data文件增容方式为一次增加20MB

ALTER DATABASE Teach
MODIFY FILE
(NAME = Teach_Data,FILEGROWTH = 20)

例： 用SQL命令修改数据库Teach，增加一个次要数据文件，逻辑名称为Teach_Datanew，存放在E盘的根目录下，文件名为Teach_Datanew.ndf

ALTER DATABASE Teach
ADD FILE(NAME = Teach_Datanew,FILENAME = 'E:\Teach_Datanew.ndf',SIZE = 100,MAXSIZE = 200,FILEGROWTH = 10)

例： 使用SQL命令，从Teah数据库中删除次要数据文件

ALTER DATABASE Teach
REMOVE FILE Teach_Datanew

---

删除数据库

DROP DATABASE 数据库名称[,...n]

例：删除数据库Teach

DROP DATABASE Teach

---

查看数据库信息

Sp_helpdb[[@dbname=]'name']
Sp_helpfile[[@filename=]'name']
Sp_helpfilegroup[[@filegroupname=]'name']

例： 用系统存储过程显示数据库结构

EXEC Sp_helpdb AdventureWorks2012

例： 用系统存储过程显示文件信息

EXEC Sp_helpfile Address

例： 用系统存储过程显示文件组信息

USE AdventureWork2012
EXEC Sp_helpfilegroup

---

创建数据表

CREATE TABLE <表名>
(<列定义>[{, <列定义>|<表约束>}])

例： 建立一个学生表S

CREATE TABLE S
(SNo CHAR(6),SN VARCHAR(10),Sex NCHAR(1) DEFAULT '男',Age INT,Dept NVARCHAR(20))

---

修改数据表

ALTER TABLE <表名>
ADD <列定义> | <完整性约束定义>ALTER TABLE <表名>
ALTER COLUMN <列名> <数据类型>
[NULL | NOT NULL]ALTER TABLE <表明>
DROP CONSTERAINT <约束名>

例： 在S表中增加一个班号列和住址列

ALTER TABLE S
ADD
Class_No VARCHAR(6),
Address NVARCHAR(20)

例： 在SC表中增加完整性约束定义，使Score在0~100之间

ALTER TABLE SC
ADD 
CONSTRAINT Score_Chk CHECK(Score BETWEEN 0 AND 100)

例： 把S表中的SN列加宽到12个字符

ALTER TABLE S
ALTER COLUMN
SN NVARCHAR(12)

例： 删除S表中的主键

ALTER TABLE S
DROP CONSTRAINT S_Prim

---

删除数据表

DROP TABLE <表名>

---

查询数据表

SELECT [ALL|DISTINCT][TOP N [PERCENT][WITH TIES]]
<列名> [AS 别名1][{, <列名>[AS 别名2]}]
FROM <表名> [[AS]表别名]
[WHERE <检测条件>]
[GROUP BY <列名1>[HAVING <条件表达式>]]
[ORDER BY <列名2>[ASC|DESC]]

例： 查询学生的全部信息

SELECT * FROM S

例： 查询选修了课程的学生学号

SELECT DISTINCT SNo FROM SC

例： 查询"刘伟"老师所讲授的课程，要求列出教师号、教师姓名和课程号

SELECT T.TNo, TN, CNo 
FROM T, TC
WHERE (T.TNo = TC.TNo) AND (TN = '刘伟')

例： 查询所有选课学生的学号、姓名、选课名称及成绩

SELECT S.SNo, SN, CN, Sore
FROM S, C, SC
WHERE S.SNo = SC.SNo AND SC.CNo = C.CNo

例： 查询所有学生的学号、姓名、选课名称及成绩

SELECT S.SNo, SN, CN, Score
FROM S
LEFT OUTER JOIN SC
ON S.SNo = SC.SNo
LEFT OUTER JOIN C
ON C.CNo = SC.CNo

例： 对学生表S和课程表C进行交叉查询

SELECT * FROM S CROSS JOIN C

例： 查询与"刘伟"老师职称相同的教师

SELECT TNo, TN FROM T 
WHERE Prof = ( SELECT ProfFROM TWHERE TN = '刘伟')

例： 用含有EXISTS的语句查询讲授课程号为C5的教师姓名

SELECT TN
FROM T
WHERE EXISTS ( SELECT * FROM TCWHERE TNo = T.TNo AND CNo = 'C5')

例： 查询没有讲授课程号为C5的教师姓名

SELECT TN
FROM T
WHERE (NOT EXISTS (SELECT * FROM TC WHERE TNo = T.TNo = T.TNo AND CNo = 'C5'))

例： 从SC数据表中查询出学号为S1同学的学号和总分，再从SC数据表中查询出学号为S5的同学的学号和总分，然后将两个查询结果合并成一个结果集

SELECT SNo AS 学号, SUM(Score) AS 总分
FROM SC
WHERE (SNo = 'S1')
GROUP BY SNo
UNION
SELECT SNo AS 学号, SUM(Score) AS 总分
FROM SC
WHERE (SNo = 'S5')
GROUP BY SNo

例： 从SC数据表中查询出所有同学的学号和总分，并将查询结果存放到一个新的数据表Cal_Table中

SELECT SNo AS 学号, SUM(Score) AS 总分
INTO Cal_Table
FROM SC
GROUP BY SNo

---

添加数据

INSERT INTO <表名>[(<列名1>[, <列名2>])] 子查询

例： 在SC表中添加一条选课记录(‘S7’, ‘C1’)

INSERT INTO SC(SNO， CNo) VALUES('S7', 'C1')

例： 求出各系主任教师的平均工资，把结果存放在新AvgSal中

CREATE TABLE AvgSal
(Department VARCHAR(20),Average SMALLINT)

---

更新数据

UPDATE <表名> SET <列名> = <表达式> [, <列名> = <表达式>]...
[WHERE <条件>]

例： 把刘伟老师转到信息系

UPDATE T
SET Dept = '信息'
WHERE SN = '刘伟'

例： 将所有学生的年龄增加1岁

UPDATE S SET Age = Age + 1

例： 把教师表中工资小于或等于1000元的讲师的工资提高20%

UPDATE T SET Sal = 1.2 * Sal
Where (Prof = '讲师')
AND Sal <= 1000

---

创建视图

CREATE VIEW view_name[(column[,...n])] AS select_statement

例： 创建一个计算机系教师情况的视图Sub_T

CREATE VIEW Sub_T
AS SELECT TNo, TN, Prof
FROM T WHERE Dept = '计算机'

例： 创建一学生情况视图S_SC_C

CREATE VIEW S_SC_C(SNo, SN, CN, Score)
AS SELECT S.SNo, SN, CN, Scroe
FROM S, C, SC WHERE S.SNo = SC.SNo
AND SC.CNo = C.CNo

例： 创建一个学生平均成绩的视图S_Avg

CREATE VIEW S_Avg(SNo, Avg)
AS SELECT SNo, Avg(Score) FROM SC GROUP BY SNo

---

修改视图

ALTER VIEW <视图名>[(<视图列表>)] AS <子查询>

例： 修改学生情况视图S_SC_C

ALTER VIEW S_SC_C(SN, CN, Score)
AS SELECT SN, CN, Score
FROM S, C, SC
WHERE S.SNo = SC.SNo AND SC.CNo = C.CNo

---

删除视图

DROP VIEW <视图名>

例： 删除计算机系教师情况的视图Sub_T

DROP VIEW Sub_T

---

创建索引

CREATE [UNIQUE][CLUSTERED|NONCLUSTERED] INDEX index_name
ON table_or_view_name(column_name[ASC|DESC][,...n])
[WITH <index_option>[,...n]]
[ON {filegroup_name|"default"}]

例： 为表SC在SNo和CNo上建立唯一索引

CREATE UNIQUE INDEX SCI ON SC(SNo, CNo)

例： 为教师表T在TN上建立聚集索引

CREATE CLUSTER INDEX TI ON T(TN)

---

修改索引

ALTER INDEX {index_name | ALL}
ON table_or_view_name
{REBUILD[[PARTITION = ALL][WITH(<rebuild_index_option[,...n]>)]|[PARTITION = partition_number[WITH(<single_partition_rebuild_index_option>[,...n])]]]|DISABLE|REORGANIZE[PARTITION = partition_number][WITH(LOB_COMPACTION = {ON | OFF})]|SET(<set_index_option>[,...n])
}[;]

1. REBUILD:删除索引并且重新生成索引
2. PARTITION:指定只重新生成或重新组织索引的一个分区
3. DISABLE:将索引标记为禁用，从而不能由数据库引擎使用
4. REORGANIZE:重新组织索引

---

删除索引

DROP INDEX <table or view name><index name>
DROP INDEX <index name> ON <table or view name>

---

查询索引

Sp_helpindex [@objname=]'name'

例： 查询表SC的索引

EXEC Sp_helpindex SC

---

更改索引

Sp_rename'数据表名.原索引名','新索引名'

例： 更改T表中的索引T1名称为T_Index

EXEC Sp_rename 'T.T1','T_Index'

创建数据库用户账号

CREATE USER user_name
[{FOR|FROM}{LOGIN login_name|CERTIFICATE cert_name|ASYMMETRIC KEY asym_key_name}|WITHOUT LOGIN]

---

规则

例： 创建学生年龄规则

CREATE RULE age_rule
AS @age >= 18 AND @age <= 50

• 绑定规则

Sp_bindrule[@rulename=]'rule'.[@objname=]'object_name'[,'futureonly']

例： 绑定规则age_rule到S表的字段Age

EXEC Sp_bindrule'age_rule'.'S.Age'

• 解除绑定

Sp_unbindrule[@objname=]'object_name'[,'futureonly']

例： 解除已绑定到S表的字段Age的规则age_rule

EXEC Sp_unbindrule 'S.Age'

• 删除命令

DROP RULE{rule_name}[,...n]

例： 删除age_rule规则

DROP RULE age_rule

• 默认创建

CREATE DEFAULT default_name AS constant_expression

例： 创建生日日期默认birthday_defa

CREATE DEFAULT birthday_defa AS '1987-1-1'

• 查看默认
  例 查看默认birthd_defa

EXEC Sp_helptext birthday_defa

• 绑定与解除默认

Sp_bindefault[@defname=]'default'
[@objname=]'object_name'[,'futureonly']Sp_unbindefault[@objname=]'object_name'[,'futureonly']

例： 绑定默认birthd_defa到数据表S的Birthday列

EXEC Sp_bindefault 'birthday_defa,' 'S.Birthday'

例： 解除默认birthd_defa与表S的Birthday列的绑定

EXEC Sp_unbindefault 'S.Birthday'

• 删除默认

DROP DEFAULT{default_name}[,..n]

例： 删除学生生日默认birthd_defa

DROP DEFAULT birthday_defa