use StudentCourse
–【例.1】建立一个名称为Test1的数据库。
create datebase test1–【例.2】建立一个数据库（StudentCourse）,并指定主文件和事务日志文件的保存位置。
CREATE DATABASE StudentCourse /*数据库名*/
ON ( NAME='Students_Data', /*主数据文件逻辑名*/
FILENAME='D:\sql\Students_Data.mdf' /*主数据文件物理名*/
)
LOG ON /*事务日志文件*/
( NAME='Students_Log', /*事务日志文件逻辑名*/
FILENAME='D:\sql\Students_Log.Ldf' /*事务日志文件物理名*/
)–【例.3】建立一个数据库（Test2）。
–数据库的其初始大小为MB，最大不受限制，
–允许数据库自动增长，增长方式是按％比例增长；
–日志文件初始为MB，最大可增长到MB，按MB增长。CREATE DATABASE Test2 /*数据库名*/
ON ( NAME='Test2_Data', /*主数据文件逻辑名*/
FILENAME='D:\sql\Test2_Data.mdf', /*主数据文件物理名*/
SIZE=5, /*初始大小*/
MAXSIZE=UNLIMITED, /*最大大小*/
FILEGROWTH=10% ) /*增长方式*/
LOG ON /*事务日志文件*/
( NAME='Test2_Log', /*事务日志文件逻辑名*/
FILENAME='D:\sql\Test2_Log.Ldf', /*事务日志文件物理名*/
SIZE=1MB,
MAXSIZE=5MB,
FILEGROWTH=1MB )–【例.4】创建一个名为Test3的数据库。
–要求它有个数据文件，其中
–主数据文件为MB，最大大小为MB，每次增长MB；
–辅数据文件属于FGroup文件组，文件为MB，最大大小不受限制，每次增长％；
–事务日志文件大小为MB，最大大小为MB，每次增长MB。CREATE DATABASE Test3 /*数据库名*/
ON
PRIMARY /*主文件组*/
( NAME='Test3_Data', /*主数据文件逻辑名*/
FILENAME='D:\sql\Testdatat1.Mdf', /*主数据文件物理名*/
SIZE=10MB,
MAXSIZE=50MB,
FILEGROWTH=2MB),
FILEGROUP FGroup /*文件组*/
( NAME='Test3_data2', /*辅数据文件逻辑名*/
FILENAME='D:\sql\Testdata2.ndf', /*辅数据文件物理名*/
SIZE=6MB,
MAXSIZE=UNLIMITED, /*增长不受限制*/
FILEGROWTH=10%)
LOG ON /*事务日志文件*/
( NAME='Test3_logl', /*事务日志文件逻辑名*/
FILENAME='D:\sql\Test.1df', /*事务日志文件物理名*/
SIZE=5MB,
MAXSIZE=50MB,
FILEGROWTH=2MB)------修改数据库
-------1. ALTER DATABASE语句的语法规则
ALTER DATABASE 数据库名称 /指定数据库逻辑名/
{ Add File <文件组> [ ,…n ]
[ To FileGroup 文件组名|DEFAULT] /在文件组中增加数据文件/
｜Add Log File <文件说明> [ ,…n ] ／*增加事务日志文件
| Remove File逻辑文件名 ／*删除数据文件
| Add FileGroup文件组名称 ／*增加文件组
| Remove FileGroup文件组名称 ／*删除文件组
| Modify File ／修改文件属性/
| Modify Name=新数据库名称 ／数据库更名/
| Modify FileGroup文件组名称｛文件说明Name=新文件组名称｝ /修改文件组/
SET<选项说明>[，,…n] ／设置修改数据库的选项/
COLLATE <排序规则名> ／*修改数据库排序规则
}–【例.5】修改数据库Test2现有数据文件的属性，将主数据文件的最大大小改为不限制，增长方式改为按每次MB增长。
ALTER DATABASE Test2
MODIFY FILE
( NAME=Test2_Data,
MAXSIZE=Unlimited,
FILEGROWTH=5MB )–【例.6】为数据库Test2增加数据文件TestBAK。
ALTER DATABASE Test2
ADD FILE
( NAME=TestBAK,
FILENAME='d:\sql\TestBAK_data.ndf',
SIZE=10MB,
MAXSIZE=50MB,
FILEGROWTH=5%)------删除数据库的语法格式如下：
DROP DATABASE 数据库名称［,…n］–【例.7 】删除数据文件TestBAK。
ALTER DATABASE Test2
REMOVE FILE TestBAK–【例.8】为数据库Test2添加文件组FGROUP，并为此文件组添加两个大小均为MB的数据文件。
ALTER DATABASE Test2
ADD FILEGROUP FGROUP
GO
ALTER DATABASE Test2
ADD FILE
( NAME=Test2_DATA2,
FILENAME=‘d:\sql\Test2_Data2.ndf’,
SIZE=10MB ),
( NAME=Test2_DATA3,
FILENAME=‘d:\sql\Test2_Data3.ndf’,
SIZE=10MB )
TO FILEGROUP FGROUP–【例.9】将Test2数据库的文件组FGROUP删除。
ALTER DATABASE Test2
REMOVE FILE Test2_DATA2
GO
ALTER DATABASE Test2
REMOVE FILE Test2_DATA3
GO
ALTER DATABASE Test2
REMOVE FILEGROUP FGROUP
GO–【例.10】将数据库Test2的名称改为MyTest。注意进行此操作时必须保证该数据库不被其他任何用户使用。
ALTER DATABASE Test2
MODIFY NAME=MyTest
GO–【例.11】在StudentCourse数据库中创建学生情况表Students、课程表Courses、选课表SC
–首先创建StudentCourse数据库，均取默认值–（）建立Students表的代码如下：
USE StudentCourse /设置StudentCourse为当前数据库/
CREATE TABLE Students
( Sno CHAR(7) Primary Key,
Sname CHAR(8) NOT NULL,
Ssex CHAR(2) NOT NULL default ‘男’ check (Ssex in(‘男’,‘女’)),
Sbirthday smalldatetime NULL,
Sdept CHAR(8) NULL,
Memo text
)–（）建立Course表的代码如下：
CREATE TABLE Courses
( Cno CHAR(3) Constraint PK_Cno PRIMARY KEY ,
Cname varchar(20) NOT NULL,
PreCno CHAR(3) NULL,
Credit tinyint
)–（）建立SC表的代码如下：
CREATE TABLE SC
(Sno CHAR(7) NOT NULL ,
Cno CHAR(3) NOT NULL ,
Grade smallint Constraint CK_Grade check (Grade between 0 AND 100),
Constraint PK_SC primary key clustered(Sno,Cno),
Constraint FK_Sno Foreign key(Sno) references Students(Sno),
Constraint FK_Cno Foreign key(Cno) references Courses(Cno),
)–【例.12】在表Students中增加一个新列——籍贯（address）。///修改基本表用alter table
ALTER TABLE Students
ADD address CHAR(40)–【例.13】修改Students表，将“address”列的数据类型由原来的char(40)改为varchar(40)。
///修改列的数据类型 ALTER COLUMN 列名 { 新类型 [约束] } [,…n
ALTER TABLE Students
ALTER COLUMN address varchar(40)–【例.14】修改Students表，将“address”列删除。
/// | DROP { 约束名|COLUMN 列名} [,…n]
alter table students
drop column address–【例.15】为Courses表的Credit列添加检查约束，要求Credit大于且小于。
alter table courses
add constraint ck_credit check (credit>0 and credit<20)–【例.16】删除Courses表中已添加的约束CK_Credit。
alter table courses
drop constraint ck_credit/5.3.2 单表查询/
–1. 选择表中的若干列
–1）查询指定列
–【例.17】查询全体学生的姓名与学号。
select sname sno
from Students–2）查询全部列
–【例.18】查询全体学生的详细记录。select *
from Students–3）查询经过计算的值
–【例.19】查询全体学生的姓名及其年龄。
/// //year函数得大写
SELECT Sname ,YEAR(GETDATE())-YEAR(Sbirthday)
FROM Students–4）设置列的别名
–例.19可以改为：SELECT Sname ,YEAR(GETDATE())-YEAR(Sbirthday) AS Age
FROM Students–2．选择表中的若干元组
–1）消除取值重复的行
–【例.20】对学生表只选择所在系名，消除结果集中的重复行。
select distinct sdept
from Students–2）限制结果返回行数
–【例.21】查询学生表前个学生的信息。
select top 5 *
from Students–若查询学生表前%的学生信息，则所用语句为：
select top 5 percent *
from Students–3）查询满足条件的元组
–①比较大小
–【例.22】查询计算机系学生的名单。
select sname
from Students
where Sdept=‘计算机’–【例.23】查询考试成绩有不及格的学生的学号。
select distinct sno
from sc
where grade<60–②确定范围（between。。。。and）
–【例.24】查询－年之间出生的学生的姓名。
SELECT Sname
FROM Students
WHERE YEAR(Sbirthday) BETWEEN 1986 AND 1988–③确定集合
–【例.25】查询机电工程系、计算机系的学生姓名。
SELECT Sname
FROM Students
WHERE Sdept IN (‘机电工程’,‘计算机’)–④字符匹配
–【例.26】查询所有姓“李”的学生的姓名和学号。
SELECT Sname,Sno
FROM Students
WHERE Sname LIKE ‘李%’–【例.27】查询姓张、李或林的姓名和学号。
SELECT Sname,Sno
FROM Students
WHERE Sname LIKE ‘[张李林]%’–【例.28】查询课程表中课程名含有“DB_”的课程情况。
SELECT *
FROM Courses
WHERE Cname LIKE ‘%DB_%’–⑤涉及空值的查询（null 或者not null
–【例.29】查询课程表中先修课为空的课程名。
SELECT Cname
FROM Courses
WHERE PreCno IS NULL–⑥多重条件查询(and 或者or)
–【例.30】查询计算机系的女生姓名。
SELECT Sname
FROM Students
WHERE Sdept=‘计算机’ AND Ssex=‘女’–【例.31】求选修了C1课程或C2课程的学生的学号及成绩。
SELECT Sno,Grade
FROM SC
WHERE Cno=‘C1’ OR Cno=‘C2’–3.使用聚合函数
–【例.32】查询学生总人数。
select COUNT(*)
from Students–【例.33】查询选修了课程的学生人数。
SELECT COUNT(DISTINCT Sno)
FROM SC–【例.34】查询选修了“C1”号课程的学生最高分数。
SELECT MAX(Grade)
FROM SC
WHERE Cno=‘C1’–4.对查询结果分组//分组group by
–【例.35】求各个课程号及相应的选修人数。
SELECT Cno,COUNT(Sno)
FROM SC
GROUP BY Cno–【例.36】输出每个学生的学号及其各门课程的总成绩。
SELECT Sno ‘学号’,Sum(grade) ‘总成绩’
FROM SC
GROUP BY Sno—SQL的执行顺序：
----–第一步：执行FROM
----–第二步：WHERE条件过滤
-----–第三步：GROUP BY分组
----–第四步：执行SELECT投影列，聚集函数
–------第五步：HAVING条件过滤
/-----–第六步：执行ORDER BY 排序–【例.37】查询选修了门以上课程的学生学号与课程数。
select Sno,COUNT(Sno)
FROM SC
GROUP BY Sno
HAVING COUNT(*)>2–5．对查询结果排序
–【例.38】查询学生选课成绩，查询结果按成绩降序排列，相同成绩的按照学号升序排列。
SELECT * FROM SC
ORDER BY Grade DESC,Sno ASC–【例.39】求选修课程数大于等于的学生的学号、平均成绩和选课门数，并按平均成绩降序排列。
SELECT Sno AS ‘学号’,AVG(Grade)AS ‘平均成绩’, COUNT()AS ‘选课门数’
from sc
GROUP BY Sno HAVING COUNT()>=2
ORDER BY AVG(Grade) DESC/5.3.3 连接查询/
–1.谓词连接
–【例.40】查找每个学生及其选修课程的情况。
SELECT Students., SC.
FROM Students, SC
WHERE Students.Sno=SC.Sno–【例.41】自然连接查询。
SELECT Students.*, SC.Cno, Grade
FROM Students,SC
WHERE Students.Sno=SC.Sno–【例.42】查询选修了“数据库原理”的计算机系的学生学号和姓名。
SELECT Students.Sno, Sname
FROM Students, Courses, SC
WHERE Students.Sno=SC.Sno AND Courses.Cno=SC.Cno
AND Cname=‘数据库原理’ AND Sdept=‘计算机’–2.以JOIN关键字指定的连接
–(1)内连接
–【例.43】用JOIN关键字表达下列查询：查询每个学生的情况以及选修的情况。
SELECT Students.* ,Cno ,Grade
FROM Students JOIN SC ON Students.Sno=SC.Sno–【例.44】用JOIN关键字表达下列查询：查询选修了“数据库原理”的计算机系的学生学号和姓名。
SELECT Students.Sno,Sname
FROM Students JOIN SC ON Students.Sno=SC.Sno
JOIN Courses ON SC.Cno=Courses.Cno
WHERE Cname=‘数据库原理’ AND Sdept=‘计算机’–查询计算机系学生人数
select COUNT(sno)
from Students
where Sdept =‘计算机’–查询各个专业选了课的学生人数
–计算机系男生/女生各多少人
–（）自身连接
–【例.45】查询每一门课的间接先修课（即先修课的先修课）。
SELECT A.Cno, B.PreCno
FROM Courses A , Courses B
WHERE A.PreCno=B.Cno–(3) 外连接
–【例.46】查找所有学生情况及其选修的课程号，若学生未选修任何课程，也要包括其基本情况。
SELECT Students.*, Cno
FROM Students LEFT OUTER JOIN SC
ON Students.Sno=SC.Sno–【例.47】查找被选修的课程的情况和所有课程的名字。
SELECT SC.*, Cname
FROM SC RIGHT JOIN Courses
ON SC.Cno=Courses.Cno–(4)交叉连接
–【例.48】列出学生所有可能的选修情况。
SELECT Sno, Sname, Cno, Cname
FROM Students CROSS JOIN Courses/5.3.4 嵌套查询/
–1) 使用IN操作符的嵌套查询
–【例.49】查询与“钟文辉”在同一个系学习的学生的学号与姓名。
SELECT Students.*, SC.Cno, Grade
FROM Students,SC
WHERE Students.Sno=SC.Sno–【例.50】求选修了“数据库原理”的学生学号和姓名。
SELECT Students.Sno, Sname 此处的student不能省略,否则列名不明确
FROM Students, Courses, SC
WHERE Students.Sno=SC.Sno AND Courses.Cno=SC.Cno
AND Cname=‘数据库原理’ AND Sdept=‘计算机’–2) 使用比较符的嵌套查询
–【例.49】查询与“钟文辉”在同一个系学习的学生的学号与姓名。
select sno sname
from Students
where Sdept in (select Sdept----------------此处in和=效果一样
from students
where Sname =‘钟文辉’)—例.50】求选修了“数据库原理”的学生学号和姓名。
SELECT Sno,Sname
FROM Students
WHERE Sno IN
( SELECT Sno FROM SC
WHERE Cno IN
(SELECT Cno FROM Courses WHERE Cname=‘数据库原理’))–3) 使用ANY或ALL操作符的嵌套查询
–【例.51】求其他系中比计算机系某一学生年龄小(即出生日期大)的学生。
SELECT *
FROM Students
WHERE Sbirthday >ANY( SELECT Sbirthday FROM Students WHERE Sdept=‘计算机’)
AND Sdept <>‘计算机’;–【例.52】求其他系中比计算机系学生年龄都小的学生。
SELECT *
FROM Students
WHERE Sbirthday >ALL ( SELECT Sbirthday
FROM Students
WHERE Sdept=‘计算机’)
AND Sdept <>‘计算机’–找出每个学生超过他选修课程平均成绩的课程号。
–(4) 使用EXISTS操作符的嵌套查询
–【例.53】求选修了C1课程的学生姓名。
SELECT Sname
FROM Students
WHERE EXISTS( SELECT *
FROM SC
WHERE Students.Sno=SC.Sno
AND Cno=‘C1’) ;–【例.54】求没有选修C1课程的学生姓名。
SELECT Sname
FROM Students
WHERE NOT EXISTS( SELECT *
FROM SC
WHERE Students.Sno=SC.Sno AND Cno=‘c1’–【例.55】查询选修了全部课程的学生的姓名。
SELECT Sname
FROM Students
WHERE NOT EXISTS( SELECT *
FROM Courses
WHERE NOT EXISTS
( SELECT * FROM SC
WHERE Students.Sno=SC.Sno
AND Courses.Cno=SC.Cno)) ;–查询至少选修了学生选修的全部课程的学生号码。
/5.3.5 组合查询/–1.将两个查询结果进行并运算
–【例.56】求选修了C1课程或选修了C2课程的学生学号。
SELECT Sno FROM SC WHERE Cno=‘C1’
UNION
SELECT Sno FROM SC WHERE Cno= ‘C2’–2．将两个查询结果进行交运算
–【例.57】查询选修了C1课程并且也选修了C2课程的学生学号。
SELECT Sno FROM SC WHERE Cno=‘C1’
INTERSECT
SELECT Sno FROM SC WHERE Cno= ‘C2’–本例不可表示为：
SELECT Sno FROM SC WHERE Cno=‘C1’ AND Cno=‘C2’–3．将两个查询结果进行差运算
–【例.58】查询选修了C1课程但没有选修了C2课程的学生学号。
SELECT Sno FROM SC WHERE Cno=‘C1’
EXCEPT
SELECT Sno FROM SC WHERE Cno=‘C2’–上述查询语句不等价于：
SELECT Sno
FROM SC
WHERE Cno=‘c1’ AND Cno!=‘C2’/5.4 数据更新/
–【例.59】向Students中插入( 0601001,赵林,男,1985-9-8,计算机, 爱好:航模) 记录
INSERT
INTO Students
VALUES (‘0601001’,‘赵林’,‘男’,‘1985-9-8’,‘计算机’,‘爱好:航模’)–【例.60】向SC表中添加一条记录（,C2,NULL）
INSERT INTO SC
VALUES (‘0601001’,‘C2’,NULL)
或:
INSERT INTO SC(Sno,Cno)
VALUES (‘0601001’,‘C2’)–2．INSERT SELECT语句
– INSERT [TOP(n) [PERCENT]] 表名子查询
–【例.62】INSERT…SELECT语句的使用。
–用如下的CREATE语句建立表StudentBAK1。
CREATE TABLE StudentBAK1
( Sno CHAR(7) NOT NULL,
Sname CHAR(7) NOT NULL,
Sdept nchar(20) NULL)
–用如下的INSERT语句向StudentBAK1表中插入数据：
INSERT TOP(2)
INTO StudentBAK1
SELECT Sno,Sname,Sdept
FROM Students WHERE Sdept=‘计算机’
–3.SELECT．．．INT0语句
–SELECT [TOP(n) PERCENT]] <列名[，列名，…列名n]>
–INTO 新表名FROM 表[,表,…表n
–WHERE 条件子句–【例.63】SELECT．．．INTO语句的使用。
SELECT Sno,Sname
INTO StudentBAK2
FROM Students
WHERE Sbirthday>‘1984-12-31’
–【例.64】将赵林同学的所在系改为“机电系”，出生日期改为“-5-1”。UPDATE Students
SET Sdept='机电系',Sbirthday='1990-5-1'
WHERE Sname='赵林'–【例.65】将选修了课程名为“数据库”的学生成绩加分
UPDATE SC
SET Grade=Grade+10
WHERE Cno=(
SELECT Cno
FROM Courses
WHERE Cname=‘数据库’)–【例.66】将选课表SC中前个学生的分数乘以.1后仍存在选课表SC中
UPDATE TOP(20) SC
SET Grade=Grade*1.1–1.使用DELETE语句删除数据
– DELETE [TOP(n) PERCENT]]
– FROM {表名| 视图名}
– [WHERE {条件} ]
–【例.67】将StudentCourse数据库的StudentBAK1表中前个姓赵的同学记录删除。
DELETE TOP(2)
FROM StudentBAK1
WHERE Sname LIKE ‘赵%’–【例.68】删除计算机系选修成绩不及格的学生选修记录
DELETE
FROM SC
WHERE Sno IN (SELECT Sno
FROM Students
WHERE Sdept=‘计算机’)
AND Grade<60–【例.69】将StudentCourse数据库的Students BAK2所建)表中的所有行删除。
DELETE
Students BAK2/*
2．使用TRUNCATE TABLE语句删除表数据
其语法格式为：TRUNCATE 表名
【例】删除表StudentBAK1中的所有行。
TRUNCATE TABLE StudentBAK1
功能：TRUNCATE TABLE删除了指定表中的所有行，且无法恢复。
TRUNCATE TABLE在功能上与不带WHERE子句的DELETE语句相同，二者均删除表中的全部行。但TRUNCATE TABLE执行速度比DELETE快，且使用的系统和事务日志资源少。
DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项。而TRUNCATE TABLE通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。*/
–【例.1】建立计算机系学生的视图。
CREATE VIEW ‘计算机系’
AS
SELECT Sno,Sname,Ssex,Sbirthday
FROM Students
WHERE Sdept=‘计算机’–【例.2】建立计算机系学生的视图，
–并要求进行修改和插入操作时仍需保证该视图只有计算机系的学生。
CREATE VIEW vwComputerStu2
AS
SELECT Sno,Sname,Ssex,Sbirthday,Sdept
FROM Students
WHERE Sdept=‘计算机’
WITH CHECK OPTION------------–【例.3】建立计算机系选修了C2号课程的学生视图。
CREATE VIEW vwComputerStu3
AS
SELECT SC.Sno,Sname,Ssex,Sbirthday,Sdept
FROM SC,vwComputerStu2
WHERE SC.Sno=vwComputerStu2.Sno AND Cno=‘C2’–【例.4】将学生的学号及他的平均成绩定义为一个视图
CREATE VIEW vwStudentGrade(Sno,Savg)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno–2．执行T-SQL语句查询视图
–【例.5】在计算机系学生视图中查找年之后出生的学生。
SELECT Sno, Sname
FROM vwComputerStu1
WHERE YEAR(Sbirthday)>1986–本例转换后的查询语句为：
SELECT Sno,Sname
FROM Students
WHERE Sdept=‘计算机’ AND YEAR(Sbirthday)>1985–T-SQL提供了视图修改语句ALTER VIEW
–【例.6】把例.3创建的视图改为选了C1号课程的学生视图。
ALTER VIEW vwComputerStu3
AS
SELECT SC.Sno,Sname,Ssex,Sbirthday,Sdept
FROM SC,vwComputerStu2
WHERE SC.Sno=vwComputerStu2.Sno AND Cno=‘C1’–6.1.4 修改视图定义----alter view 或者drop view
/*
其语法格式如下：
DROP VIEW <视图名>;
注意：视图删除后视图的定义将从数据字典中删除。但是由该视图导出的其他视图定义仍在数据字典中，不过该视图已失效。用户使用时会出错，要用DROP VIEW语句将它们一一删除。
*/
–例如要删除视图vwComputerStu2，则执行如下SQL命令即可：----删除视图用drop
DROP VIEW vwComputerStu2–6.1.5 更新视图
–【例.8】向学生视图vwComputerSt2u中插入—个新的计算机系学生记录，其中学号为，姓名为吴新，出生日期为-9-7。
INSERT
INTO vwComputerStu2
VALUES(‘0601005’,‘吴新’,‘男’,‘1987-9-7’,‘计算机’)–说明：若把一个不属于计算机系的学生视图通过插入数据到Students表中，则系统提示所插入的数据不符合CHECK OPTION 约束。
–【例.9】删除计算机系学生视图vwComputerStu1中学号为的记录。
DELETE
FROM vwComputerStu1
WHERE Sno=‘0601005’–6.2.1 创建索引
–【例.10】在StudentBak1表的学号Sno列上建立一个聚集索引，而且学生表中的记录将按照姓名值的升序存放。
CREATE CLUSTER INDEX ixSname ON StudentBak1 (Sname)–【例.11】根据StudentBak1表的姓名Cname、所在系Sdept创建一个名为ixNameDept索引。
------其语法格式如下：
------- CREATE [UNIQUE] [CLUSTERED] [NONCLUSTERED
---- INDEX 索引名
------ON <表|视图> (列名[ASC] [DESC][,…n] )
----[ INCLUDE ( 包含性列[,…n] ) ]
---- [ WITH (索引选项) ]
----- [ON {分区架构|文件组|default} ]CREATE UNIQUE NONCLUSTEREDINDEX ixNameDept
ON StudentBak1 (Sname ASC ,Sdept DESC )–2．利用T-SQL语句删除索引
–其语法格式如下：
–DROP INDEX 表名.索引名
–【例.12】删除例.9创建的索引。DROP INDEX StudentBak1.ixSname
–【例.1】使用T-SQL语句创建一个带多个参数的存储过程
–创建存储过程增加Courses表数据的过程)的T-SQL语句如下：
CREATE PROC procAddCourse
@Cno CHAR(3),@Cname VARCHAR(20),
@PreCno CHAR(3),@Credit TINYINT
AS
INSERT INTO Courses(Cno,Cname, PreCno, Credit)
VALUES (@Cno,@Cname, @PreCno, @Credit)
–执行存储过程的T-SQL语句如下：
EXEC procAddCourse
@Cno=‘C5’, @Cname=‘C#程序设计’, @PreCno=‘C2’, @Credit=4
或
EXEC procAddCourse ‘C5’, ‘C#程序设计’, ‘C2’, 4–【例.2】创建带有通配符参数的存储过程。存储过程从学生表中返回指定些学生(提供名字和姓氏)的信息.
CREATE PROCEDURE procGetStudentByName1 @name char(8)=’%’
AS
SELECT * FROM Students
WHERE Sname LIKE RTRIM(@name);
部分执行的方法如下：
EXECUTE procGetStudentByName; --返回所有学生的信息
或者
EXECUTE procGetStudentByName ‘吴%’; --返回所有姓吴的学生信息
或者
EXECUTE procGetStudentByName ‘王冲瑞’ --返回王冲瑞同学的信息–【例.3】使用T-SQL语句创建带Output参数输出的存储过程,下面存储过程返回参数给定学号的学生的平均成绩。
CREATE PROCEDURE procGetAvgGradeByNum
@Sno char(7), @avgGrade SMALLINT OUTPUT
AS
SELECT @avgGrade =AVG(Grade)
FROM SC
WHERE Sno=@Sno;
–通过以下代码执行，得到该学生的平均成绩。
DECLARE @averageGrade SMALLINT
EXEC procGetAvgGradeByNum ‘0602001’, @averageGrade OUTPUT
PRINT ‘0602001的平均成绩为’+ STR(@averageGrade)–【例.4】创建一个DELETE触发器。当删除“学生表”中的记录时，自动删除“成绩”表中对应学号的记录。
CREATE TRIGGER triStudentDelete --定义名称为triStudentDelete的触发器
ON Students --定义触发器所附着的表的名称Students
FOR DELETE --定义触发器的类型
AS
BEGIN
DECLARE @Sno CHAR(7)
SELECT @Sno=deleted.Sno FROM deleted
DELETE SC WHERE Sno=@Sno
END
–删除数据验证触发器
DELETE FROM Students WHERE Sno=‘0703001’–【例.5】使用DDL触发器来防止StudentCourse数据库中的任一表被删除或修改
CREATE TRIGGER safetyDB
ON DATABASE
FOR DROP_TABLE,ALTER_TABLE
AS
PRINT ‘禁止删除或修改数据库中的表’
ROLLBACK;–【例.6】将triSCInsert触发器中FOR INSERT修改为INSTEAD OF INSERT。
ALTER TRIGGER triSCInsert
ON SC
INSTEAD OF INSERT
AS
BEGIN
DECLARE @Sno char(7)
SELECT @Sno=inserted.Sno FROM inserted
IF NOT EXISTS (SELECT Sno FROM Students WHERE Sno=@Sno)
PRINT ‘学生表中没有该学号的同学!’
ELSE
INSERT INTO SC SELECT * FROM inserted --如果满足条件则插入数据
END–【例.5】利用CREATE LOGIN创建SQL Server登录账号。
–创建带密码的PassUser 登录名。DEFAULT_DATABASE指定将指派给登录的默认数据库。
CREATE LOGIN PassUser
WITH PASSWORD = ‘PassWord123456’,
DEFAULT_DATABASE=StudentCourse ;–【例.6】SQL Server登录账号的取消。
–同样可以通过ALTER LOGIN来启用或禁用SQL Server登录账号，也可以通过DROP LOGIN来删除SQL Server登录账号。
ALTER LOGIN PassUser DISABLE --禁用登录名PassUser
ALTER LOGIN PassUser ENABLE --启用登录名PassUser
DROP LOGIN PassUser --删除登录名PassUser–【例.8】将SQL Server的登录账号‘PassUser’添加为StudentCourse数据库的账户，并取名为：dbPassUser
CREATE USER dbPassUser FOR LOGIN PassUser–将windows登录帐号‘HJL\admin’添加为StudentCourse数据库的账户，并取名为：HJL_admin。
CREATE USER HJL_admin FOR LOGIN [HJL\admin]–【例.9】删除数据库StudentCourse的数据库用户dbPassUser。
–（）展开【数据库】|【StudentCourse】|【用户】，右击要删除的数据库用户，在弹出的快捷菜单中执行【删除】命令即可。
–（）使用T-SQL语句DROP USER来删除数据库用户
DROP USER dbPassUser–【例.11】将Windows用户user添加到sysadmin固定服务器角色中。
EXEC sp_addsrvrolemember [HJL\admin],‘sysadmin’–【例.12】从sysadmin固定服务器角色中删除登录账号’ HJL\admin’。
EXEC sp_dropsrvrolemember [HJL\admin],‘sysadmin’–【例.18 】设置数据库用户/角色创建数据库对象的权限。
–给user用户及Role1角色授予备份数据库、日志文件和创建表的权限GRANT Backup DATABASE,BACKUP LOG, CREATE TABLE
TO [user],Role1注意：user为系统关键字，所以引用时要加[]。
–对用户HJL_admin用户及Role1角色不允许授予备份数据库、日志文件和创建表的权限
DENY Backup DATABASE,BACKUP LOG, CREATE TABLE
TO HJL_admin,Role1–取消对用户HJL_admin用户及Role1角色授予或禁止备份数据库、日志文件和创建表的权限
REVOKE Backup DATABASE,BACKUP LOG, CREATE TABLE
TO HJL_admin,Role1
–【例.19】设置数据库用户/角色的表操作权限。
–为Role1角色分配访问Students对象的权限。
GRANT ALL ON Students To Role1
–拒绝Role1角色访问Student
DENY ALL ON Students TO Role1
–撤消Role1角色用户访问Student的权限
REVOKE ALL ON Students From Role1–【例.20】让数据库用户user不再属于Role1角色，而且user和Role1不拥有访问Students的任何权限；
–然后为user和Role1分配访问Students的SELECT和INSERT权限；
–Role1角色对Students表有SELECT权利，对Sdept字段具有更新权力；
–最后撤消数据库用户user访问Student的任何权限。–从Role1角色删除数据库用户login_name
EXEC sp_droprolemember Role1, [user]
–撤消数据库用户user和和Role1访问Students的任何权限。
REVOKE ALL ON Students TO [user],Role1
–为login_name和Role1分配访问Students的SELECT和INSERT权限.
GRANT SELECT,INSERT ON Students TO Role1, [user]
–向Role1分配访问Students 的Select和Sdept字段具有更新。
GRANT SELECT,UPDATE(Sdept) ON Students TO Role1
–撤消数据库用户user访问Student的任何权限。
REVOKE ALL ON Students TO [user]本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
