作者: 18届 KDB
日期: 2020-10-2
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 Prof
FROM T
WHERE TN = '刘伟')
例: 用含有EXISTS的语句查询讲授课程号为C5的教师姓名
SELECT TN
FROM T
WHERE EXISTS ( SELECT *
FROM TC
WHERE 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])
}[;]
- REBUILD:删除索引并且重新生成索引
- PARTITION:指定只重新生成或重新组织索引的一个分区
- DISABLE:将索引标记为禁用,从而不能由数据库引擎使用
- 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