数据库SQL命令学习笔记
一、创建一个数据库
1.1数据库的组成
- 数据文件
- 主要数据文件.mdf
- 次要数据文件.ndf
- 事物日志文件.ldf
文件组（方便管理数据文件引入）
数据文件的集合形成的整体
- 主要文件组+次要文件组
- 一个数据文件只能存在于一个文件组中，一个文件也只能被一个数据库使用
注意： 日志文件不分组，不属于任何文件组
数据库操作：
- C(Create):创建
- R(Retrieve)：查询
- U(Update):修改
- D(Delete):删除
1.2数据库的创建
create database 数据库名称
on
(
name = 逻辑名称_Data，
filename = "路径\数据库名_Data.mdf"
size = 初始大小，
maxsize = 最大容量，
filegrowth = 增长限制
)
log on
(
name = 逻辑名称_log，
filename = "路径\数据库名_log.ndf"
size = 文件大小，
maxsize = 增长限制，
filegrowth = 文件增长幅度
)
小结：
creat database 名
on
（
n _data,
fn="_data.mdf",
size =
maxsize =
filegrowth
）
1.3修改数据库操作
- 修改文件操作（修改文件的大小增长率，文件名）
alter database 数据库名称
modify file
(
name = 逻辑文件名
size = 文件大小
)
--例：需求将教学管理数据库JXGL增加容量，原来数据库文件JXGL.mdf的厨师大小为3MB，现将增加问10MB
alter database JXGL
modify file
(
name = JXGL，
size = 10MB
)
--例5.7将已存在的数据库JXGL改名为GXJXGL.
ALTER DATABASE JXGL
MODIFY NAME= GX_JXGL
-
增加操作（增加数据文件或日志文件）
alter database 数据库名称 add file|add log file ( name = 逻辑文件名， filename = 物理文件名， size = 大小 maxsize = 增长限制， filegrowth =文件增长幅度 ) --例:为数据库JXGL增加辅助数据文件JXGL_ 1.NDF， 初始大小为5MB，最大长度为30MB， 按照5%增长。 ALTER DATABASE JXGL ADD FILE (NAME=JXGL_ 1, FILENAME='D:\JXGL SYSIDATAJXGL_ 1 .dnf', SIZE=5, MAXSIZE=30, FIL EGROWTH=5% ) -
删除数据库文件
alter database 数据库名称 remove file 逻逻辑文件名 --例5.6删除数据库JXGL中的辅助数据文件JXGL_ 1.ndf。 ALTER DATABASE JXGL REMOVE FILE JXGL_1小结：数据的修改操作主要为增、删和改
开头 都以alter database 数据库名
(add file/log file ) / modify file (name = 文件名, 改啥) /remove file
1.4删除数据库
drop database 数据库名称
数据库总结：
-
数据库的组成，数据文件（主要.mdf+次要.ndf） 日志文件ldf
-
数据库的操作：
1. 创建 2. 修改 + 增 + 删 + 改 3. 删除
1.5数据类型
-
字符型
-
Unicode
一个字符两个字节，nchar（定长的） nvarchar(非定长)
-
ASCLL
一个字符一个字节，char(定长的) ,varchar(非定长)
-
二、数据表
- 数据库操作：
- C(Create):创建
- R(Retrieve)：查询
- U(Update):修改
- D(Delete):删除
2.1创建数据表
create table 表名
<列名><数据类型>[[default][{列约束}]] --注意带[]的是可有可无的
--例如使用SQL命令创建一个学生表S
create table S
(
Sno char(6),
Sn varchar(10),
Sex nchar(1) default'男'，--表示默认性别为男
age int,
dept nvarchar(20)
)
2.2定义数据表约束
-
列约束
-
表约束
--完整性约束的基本语法格式
[constraint<约束名>]<约束类型>
--约束名可省略
约束类型有五种
null/not null 注意：主键列不允许出空值
unique 唯一约束
primary key 主键约束
foreign key 外键约束
check
-
null / not null
create table S ( Sno char(6) constraint S_cons not null, --Sno char(6) not null Sn varchar(10), Sex nchar(1) default'男'，--表示默认性别为男 age int, dept nvarchar(20) ) -
unique(唯一约束)
用于定义列和表约束
unique允许约束的字段为null值
一个表允许有多个unique约束
--[例]建立一个S表，定义SN+Sex为唯一键，此约束为 表约束。 CREATE TABLE S ( SNo VARCHAR(6), SN NVARCHAR(10) UNIQUE, Sex NCHAR(1), Age INT, Dept NVARCHAR(20), CONSTRAINT S_ UNIQ UNIQUE(SN, Sex) ) -
primary key （主键约束）
用于定义列和表约束
不能重复，不能为空
--[例]建立一个S表，定义SNo为S的主键，建立另外-个数据表C, 定义CNo为C的主键。 定义数据表S: CREATE TABLE S ( SNo VARCHAR(6) CONSTRAINT S_ Prim PRIMARY KEY, SN NVARCHAR(10) UNIQUE, Sex NCHAR(1), Age INT, Dept NVARCHAR(20) ) --定义数据表C: CREATE TABLE C ( CNo VARCHAR(6) CONSTRAINT C_ Prim PRIMARY KEY, CN NVARCHAR(20), CT INT ) --[例]建立-个SC表，定义SNo+CNo为SC的主键。 CREATE TABLE SC ( SNo VARCHAR(6) NOT NULL, CNo VARCHAR(6) NOT NULL， Score NUMERIC(4,1), CONSTRAINT SC_ Prim PRIMARY KEY(SNo,CNo) )-
foreign key( /ˈfɒrən/)（外键约束）
用于另一基本表的主键，起唯一表示作用
--[例|建立- -个S表，定义SNo为S的主键，建立另外-个数据表C, --定义CNo为C的主键。 --定义数据表S: CREATE TABLE S ( SNo VARCHAR(6) CONSTRAINT S_ Prim PRIMARY KEY, SN NVARCHAR(10) UNIQUE, Sex NCHAR(1), Age INT, Dept NVARCHAR(20) ) --定义数据表C: CREATE TABLE C ( CNo VARCHAR(6) CONSTRAINT C_ _Prim PRIMARY KEY, CN NVARCHAR(20), CT INT ) --[例]建立-个SC表，定义SNo+CNo为SC的主键。 CREATE TABLE SC . ( SNo VARCHAR(6) NOT NULL, CNo VARCHAR(6) NOT NULL, Score NUMERIC(4,1), CONSTRAINT SC_ Prim PRIMARY KEY(SNo,CNo) ) --|例]建立-个SC表，定义SNo, CNo为SC的外部键。 CREATE TABLE SC ( SNo VARCHAR(6) NOT NULL CONSTRAINT S Fore 中 FOREIGN KEY REFERENCES S(SNo), CNo VARCHAR(6) NOT NULL CONSTRAINT C_ Fore FOREIGN KEY REFERENCES C(CNo)， Score NUMERIC(4,1), . CONSTRAINT S_ C_ Prim PRIMARY KEY (SNo,CNo) )-
check（检查约束）
用来检查字段值所允许的范围
[constratin<约束名>]check(<条件>)
--[例]建立-个SC表，定义Score的取值范围为0~ 100之间。 CREATE TABLE SC ( SNo VARCHAR(6), CNo VARCHAR(6), Score NUMERIC(4,1) CONSTRAINT Score_ Chk CHECK(Score>=0 AND Score <=100) )
-
-
2.3修改数据表
1.alter table 表名
add <列定义>|<完整性约束定义>
–用SQL命令修改数据表结构
[例]在S表中增加一个班号列和住址列。
ALTER TABLE S
ADD
Class_ No VARCHAR(6),
Address NVARCHAR(20)
–注意：使用此方式增加的新列自动填充NULL值，所以不能为增加的新列指定NOT NULL约束。
2.alter table 表名
alter conlumn<列名>< 数据类型 >
–[例]把S表中的SN列加宽到12个字符。
ALTER TABLE S
ALTER COLUMN
SN NVARCHAR(12)
–不能改变列名;
–不能将含有空值的列的定义修改为NOT NULL约束;
–若列中已有数据，则不能减少该列的宽度，也不能改变其数据类型;
–只能修改NULLNOT NULL约束，其他类型的约束在修改之前必须先将约束删除，然后再重新添加修改过的约束定义。
3.alter table 表名
drop constratin <约束名>
–[例]删除S表中的主键。
ALTER TABLE S
DROP CONSTRAINT S Prim
### 2.4单关系表的数据查询
+ 无条件查询
只包含 select from 只对关系表进行投影操作
~~~sql
--例如： 查询全体学生的学号、姓名和年龄。
select Sno,Sn,Sex
from S
-
条件查询
当需要查找满足某些条件的行时，需要使用where子句指定查询条件。
-
比较大小
--[例]查询选修课程号为'C1'的学生的学号和成绩。。 SELECT SNo,Score FROM SC WHERE CNo = 'C1' -
多重条件查询（and、or、not）
--NOT、AND、OR高->低 --(用户可以使用括号改变优先级) --[例]查询选修C1或C2且分数大于等于85分学生的学号、课程号和成绩。 SELECT SNo, CNo, Score FROM SC WHERE (CNo= 'C1' OR CNo = 'C2') AND (Score>= 85) -
确定范围（between and）
--[例]查询] C资在1000元~ 1500元之间的教师的教师号、姓名及职称。 SELECT TNo,TN,Prof FROM T WHERE Sal BETWEEN 1000 AND 1500 --[例]查询工资不在1000元~ 1500元间的教师的教师号、姓名及职称。 SELECT TNo,TN,Prof FROM T WHERE Sal NOT BETWEEN 1000 AND 1500 -
确定集合（in）
--[例]查询选修C1或C2的学生的学号、课程号和成绩。 SELECT SNo, CNo, Score FROM SC WHERE CNo IN('C1'，'C2') --此语句也可以使用逻辑运算符"OR"实现。WHERE CNo='C1'OR CNo= 'C2' --利用"NOT IN"可以查询指定集合外的元组。 -
字符匹配
不知道精确值时，使用like或not like进行部分匹配查询
<属性名>like<字符串常量>
--[例]查询所有姓张的教师的教师号和姓名。 SELECT TNo, TN FROM T WHERE TN LIKE'张%' --[例]查询姓名中第个汉字是 “力”的教师号和姓名。 SELECT TNo, TN FROM T WHERE TN LIKE'_力%' -
空值查询
- 某个字段没有值称为具有空值null
- 空值不是0和空格，不占存储空间
---[例]查询没有考试成绩的学生的学号和相应的课程号。 SELECT SNo, CNo FROM SC WHERE Score IS NULI查询小结：
-
常用库函数及统计汇总查询
平均avg、总和sum、列最大max、列最小min、列个数count
总结：和平大小数
--[例]求学号为S1的学生的总分和平均分。 SELECT SUM(Score) AS TotalScore, AVG(Score) AS AvgScore FROM SC WHERE (SNo= 'S1') --[例]求选修C1号课程的最高分、最低分及之间相差的分数。 SELECT MAX(Score) AS MaxScore, MIN(Score) AS MinScore, MAX(Score) - MIN(Score) AS Diff FROM SC WHERE (CNo= 'C1') --[例]求计算机系学生的总数。 SELECT COUNT (SNo) FROMS WHERE Dept='计算机' --[例]求学校中共有多少个系。 --DISTINCT消去重复行 SELECT COUNT(DISTINCT Dept) AS DeptNum FROM S --[例]统计有成绩同学的人数。 SELECT COUNT (Score) FROM SC --其中，成绩为0的同学也计算在内，没有成绩(即为空值)的不计算。 --[例]利用特殊函数COUNT(*)求计算机系学生的总数。 SELECT COUNT(*) FROM S WHERE Dept='计算机' --COUNT (*) 用来统计元组的个数，不消除重复行，不允许使用DISTINCT关键字。小结：使用函数
1.先select开始对列进行投影
2.然后from选择投影那张表
3.最后看有无需要满足的条件。
4.最终显示选择的列
-
分组查询
GROUP BY子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的值。[例]查询各个教师的教师号及其任课的门数。
--[例]查询各个教师的教师号及其任课的门数。 SELECT TNo,COUNT(*)AS C Num FROM TC GROUP BY TNo --注意：count(*)可以统计元组个数即有多少行TNo C_Num T1 2 T2 3 T3 4 分组之后还要按照一定条件进行筛选，则需要使用having子句(将分组再次进行筛选)
--[例]查询选修两门以上(含两门)课程的学生的学号和选课门数。 SELECT SNo, COUNT(*) AS SC_ Num FROM SC GROUP BY SNo HAVING (COUNT(*)>= 2)SNo SC_Num S1 2 S2 4 S3 3 查询的结果的排序
使用order by子句，必须出现在其他子句之后。DESC为降序，ASC为升序（缺省）
--[例]查询选修C1的学生学号和成绩，并按成绩降序排列。 SELECT SNo, Score FROM SC WHERE (CNo= 'C1') ORDER BY Score DESC --[例]查询选修C2, C3, C4或C5课程的学号、课程号和成绩。查询结果按学号升序排列，学号相同再按成绩降序排列。 SELECT SNo, CNo, Score FROM SC WHERE CNo IN (C2', 'C3', 'C4, 'C5') ORDER BY SNo, Score DESC
-
-
排序查询
select语句的order 子句用于对查询结果按升序（默认或ASC）或（DESC）排列行，
格式：order by 属性字段 [ASC|DESC]
可以利用TOP语句输出查询结果集的前面若干行元组。也可以利用INTO语句将查询结果集输出到一个新建的数据表中。
2.5多关系表的数据查询
+ 引入： 当查询关系同时设计两个及两个以上的表时，称为连接查询。
+ 连接查询是通过各个表之间的共同字段的关联性来查询数据的。这种字段称为链接字段。
表的连接方法有两种
- 表之间满足一定条件的行进行连接时，from子句知名进行连接的表名，where子句指明连接的列名及其 连接条件。
- 利用关键字JOIN进行连接：当将JOIN关键词放于FROM子句时，应有关键词ON与之对应，已表明连接的条件。
JOIN分类
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Dhax8ZnM-1600047332353)(C:\Users\10512\AppData\Roaming\Typora\typora-user-images\image-20200428225758433.png)]
- 内连接查询
--[例] 查询“刘伟”老师所讲授的课程，要求列出教师号、教师姓名和课程号
--方法一
SELECT T.TNo,TN,CNo
FROM T,TC
WHERE (T.TNo = TC.TNo) AND (TN=刘伟')
--这里TN=刘伟'为查询条件，而T.TNo = TC.TNo为连接条件，TNo为连接字段。
--方法二
SELECT T.TNo, TN, CNo
FROM T INNER JOIN TC
ON T.TNo = TC.TNo
WHERE (TN = '刘伟')
--[例] 查询所有选课学生的学号、姓名、选课名称及成绩。
SELECT S.SNo,SN,CN,Score
FROM S,C,SC
WHERE S.SNo=SC.SNoAND SC.CNo=C.CNo
-
外连接查询、
-
左外连接
(1) 左外连接
左外连接是对连接条件左边的表不加限制。当左边表元组与右边表元组不匹配时，与右边表的相应列值取NULL。语句格式如下：
SELECT <目标列表达式>[, … n]
FROM <表1>LEFT[OUTER]JOIN <表2>[, … n]
ON <连接条件表达式>
--【例7.29】采用左外连接查询教师任课情况。 USE stsc SELECT teacher.tname, course.cname FROM teacher LEFT JOIN course ON (teacher.tno=course.tno) 查询结果： tname cname ---------- ---------------- 刘林卓 数字电路 周学莉 NULL 吴波 数据库系统 王冬琴 微机原理 李伟 高等数学 -
右外连接
(2) 右外连接
右外连接是对连接条件右边的表不加限制。当右边表元组与左边表元组不匹配时，与左边表的相应列值取NULL。语句格式如下：
SELECT <目标列表达式>[, … n]
FROM <表1> RIGHT [OUTER] JOIN <表2>[, … n]
ON <连接条件表达式>
--【例7.30】采用右外连接查询教师任课情况。 USE stsc SELECT teacher.tname, course.cname FROM teacher RIGHT JOIN course ON teacher.tno=course.tno 查询结果： tname cname ---------- ------------- 刘林卓 数字电路 吴波 数据库系统 王冬琴 微机原理 NULL 计算机网络 李伟 高等数学 -
全连接
全外连接是对连接条件的两个表都不加限制。当一边表元组与另一边表元组不匹配时，与另一边表的相应列值取NULL。语句格式如下：
SELECT <目标列表达式> [, … n]
FROM <表1> FULL [OUTER] JOIN <表2>[, … n]
ON <连接条件表达式>
--【例7.31】采用全外连接查询教师任课情况。 USE stsc SELECT teacher.tname, course.cname FROM teacher FULL JOIN course ON teacher.tno=course.tno 查询结果： tname cname --------- ------------- 刘林卓 数字电路 周学莉 NULL 吴波 数据库系统 王冬琴 微机原理 李伟 高等数学 NULL 计算机网络
符合连接条件的数据将直接返回到
结果集中，对那些不符合连接条件
的列，将被填上NULL值后再返回到
结果集中。 -
--[例] 查询所有学生的学号、姓名、选课名称及成绩（没有选课的同学的选课信息显示为空）
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
- 交叉查询
交叉查询（CROSS JOIN）对连接查询的表没有特
殊的要求，任何表都可以进行交叉查询操作。
[例] 对学生表S和课程表C进行交叉查询。
SELECT *
FROM S CROSS JOIN C
-
自连接查询
--[例] 查询所有比“刘伟”工资高的教师姓名、工资和刘伟的工资。 --方法1： SELECT X.TN,X.Sal AS Sal_a,Y.Sal AS Sal_b FROM T AS X ,T AS Y WHERE X.Sal>Y.Sal AND Y.TN='刘伟' --方法2： SELECT X.TN, X.Sal,Y.Sal FROM T AS X INNER JOIN T AS Y ON X.Sal>Y.Sal AND Y.TN='刘伟'
2.6数据操纵语言
-
插入语句
insert into <表名>[<列名>,[,...n]] values (<常量表达式>) --例 S表中有属性 Sno Sname Sex Age St 插入一条记录（‘001’，‘小徐’，‘男’，‘18’，‘计算机网络老师’） use S insert into S values（'001'，'小徐’，'男'，‘18’，‘计算机网络老师’） --注意：插入多条以上记录只需用‘，’分隔即可 --例 S表中只插入 Sno Sname insert into S(Sno,Sname) values('001','小徐') -
修改语句
update 用于修改数据表或者视图的数据
格式：
--格式：update 表 set <列名>=<表达式>[,...] [where<逻辑表达式> ] -
删除语句
delete from 表名
where <逻辑表达式>
--[例7.14]删除学号为“2006”的学生记录。 DELETE student WHERE stno='122006'
2.7子查询
-
普通子查询
-
返回一个值得普通子查询 使用比较运算符
（=, >, <, >=, <=, !=）--[例] 查询与“刘伟”老师职称相同的教师号、姓名。 SELECT TNo,TN FROM T WHERE Prof= ( SELECT Prof FROM T WHERE TN= '刘伟') -
返回一组值的普通子查询 使用ANY
--[例] 查询讲授课程号为C5的教师姓名。 SELECT TN FROM T WHERE (TNo = ANY (SELECT TNo --= any可以使用IN替换 FROM TC WHERE CNo = 'C5')) --方法二 SELECT TN FROM T,TC WHERE T.TNo=TC.TNo AND TC.CNo= 'C5 ' --[例] 查询其他系中比计算机系某一教师工资高的教师的姓名和工资 SELECT TN, Sal FROM T WHERE (Sal > ANY ( SELECT Sal FROM T WHERE Dept = '计算机')) AND (Dept <> '计算机') --方法二 SELECT TN, Sal FROM T WHERE Sal > ( SELECT MIN(Sal) FROM T WHERE Dept = '计算机') AND Dept <> '计算机' -
返回一组值的普通子查询 ——使用ALL
--[例] 查询其他系中比计算机系所有教师工资都高的教师的姓名和工资。 SELECT TN, Sal FROM T WHERE (Sal > ALL ( SELECT Sal --Sal > ( SELECT MAX(Sal) FROM T WHERE Dept = '计算机')) AND (Dept <> '计算机')
小结：顺序先执行子查询 再执行父查询
-
-
相关子查询
执行顺序
（1）选取父查询表中的第一行记录，内部的
子查询利用此行中相关的属性值进行查询；
（2）父查询根据子查询返回的结果判断此行
是否满足查询条件。如果满足条件，则把该
行放入父查询的查询结果集合中。
（3）重复执行这一过程，直到处理完父查询
表中的每一行数据。-
使用EXISTS
不会返回实际数据 只得到逻辑值 真或假
当子查询的查询结果为非空时，外层的where子句返回真值，否则返回假值。
--[例] 用含有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 AND CNo = 'C5’)) -
2.8 其他类型查询
合并查询
- 合并查询是使用UNION操作符将来自
不同查询的数据组合起来，形成一个具有
综合信息的查询结果，UNION操作会自动
将重复的数据行剔除。 - 参加合并查询的各个子查询使用的表结
构应该相同，即各个子查询中的字段数目
和对应的数据类型都必须相同。
--[例] 从SC数据表中查询出学号为“S1”同学的学号和总分，再从SC数据表中查询出学号为“S5”的同学的学号和总分，然后将两个查询结果合并成一个结果集。
SELECT SNoAS 学号, SUM(Score) AS 总分
FROM SC
WHERE (SNo = 'S1')
GROUP BY SNo
UNION
SELECT SNoAS 学号, SUM(Score) AS 总分
FROM SC
WHERE (SNo = 'S5')
GROUP BY SNo
查询结果保存到表中
- 使用SELECT…INTO 语句可以将查询结果存
储到一个新建的数据库表或临时表中 。
--[例]从SC数据表中查询出所有同学的学号和总分，并将查询结果存放到一个新的数据表Cal_Table中。
SELECT SNoAS 学号, SUM(Score) AS 总分
INTO Cal_Table
FROM SC
GROUP BY SNo
2.9数据表中数据的操纵
3.10视图
视图是从一个或几个表导出来的表，它不是真实存在的基本表而是一张虚表，视图所对应的数据并不实际地以视图结构存储在数据库中，而是存储在视图所引用的表中。
-
创建视图
使用视图前，必须先创建视图，创建视图要遵守以下原则：
（1）只有在当前数据库中才能创建视图，视图命名必须遵循标识符规则。
（2）不能将规则、默认值或触发器与视图相关联。
（3）不能在视图上建立任何索引。
CREATE VIEW <视图名>[(<列名>[, … n ])]
AS
<SELECT查询子句>
[WITH CHECK OPTION]
--【例8.2】 使用CREATE VIEW语句，在stsc数据库中创建st2_comm视图，包括学号、姓名、课程号、成绩，且专业为通信。 USE stsc GO CREATE VIEW st2_comm AS SELECT student.stno, student.stname, score.cno, score.grade FROM student, score WHERE student.stno=score.stno AND student.speciality= '通信' WITH CHECK OPTION GO -
查询视图
查询视图使用SELECT语句，使用SELECT语句对视图进行查询与使用SELECT语句对表进行查询一样
--查询通信专业学生的学号、姓名、课程名。 USE stsc SELECT stno, stname, cname FROM st_comm --查询结果： --stno stname cname --------- ----------- ------------- 121001 李贤友 数字电路 121001 李贤友 微机原理 121001 李贤友 高等数学 121002 周映雪 数字电路 121002 周映雪 微机原理 121002 周映雪 高等数学 121005 刘刚 数字电路 121005 刘刚 微机原理 121005 刘刚 高等数学 -
更新视图
--【例8.6】 在stsc数据库中，以student为基表，创建专业为计算机的可更新视图st_cp。创建视图st_cp语句如下： USE stsc GO CREATE VIEW st_cp AS SELECT * FROM student WHERE speciality= '计算机' GO-
插入数据
---【例8.7】 向st_cp视图中插入一条记录：('2009','董智强','男','1992-11-23','计算机',50)。 INSERT INTO st_cp VALUES ('2009','董智强','男','1992-11-23','计算机',50) -
修改视图
--【例8.8】将st_cp视图中学号为122009的学生的总学分增加2分。 UPDATE st_cp SET tc=tc+2 WHERE stno=‘122009' -
删除数据
--【例8.9】 删除st_cp视图中学号为122009的记录。 DELETE FROM st_cp WHERE stno=‘122009‘
-
-
修改视图定义
--【例8.11】 将例8.2定义的视图st2_comm进行修改，取消专业为通信的要求。 ALTER VIEW st2_comm AS SELECT student.stno, student.stname, score.cno, score.grade FROM student, score WHERE student.stno=score.stno WITH CHECK OPTION -
删除视图
使用T-SQL的DROP VIEW语句修改视图，DROP VIEW语句的语法格式如下：
语法格式：
DROP VIEW [ schema_name . ] view_name [ …,n ] [ ; ]
其中view_name是视图名，使用DROP VIEW可删除一个或多个视图。
--【例8.15】 将视图st_view2删除。 USE stsc DROP VIEW st_view2
3.11创建于使用索引
-
概述
索引是一种可以加快检索的数据库结构，通过创建良好的索引，可以显著提高数据库查询和应用程序的性能。索引一旦创建，将由DBMS自动管理和维护，当操作数据时，DBMS会自动更新索引。要避免在一个表中创建大量的索引，否则会影响数据库操作的性能，降低系统的响应速度。
-
索引类型
-
- 聚集索引
- 非聚集索引
- 唯一索引
- 视图索引
- 全文索引
- XML索引
-
-
操作
-
创建索引
--[例] 为表SC在SNo和CNo上建立惟一索引。 CREATE UNIQUE INDEX SCI ON SC(SNo,CNo) --[例] 为教师表T在TN上建立聚集索引。 CREATE CLUSTER INDEX TI ON T(TN)
2. 修改索引 ~~~sql --（1）REBUILD：删除索引并且重新生成索引。 --（2）PARTITION：指定只重新生成或重新组织索引的一个分区。 --（3）DISABLE：将索引标记为禁用，从而不能由数据库引擎使用。 --（4）REORGANIZE：重新组织索引。 ~~~ 3. 删除索引 ~~~sql --用SQL语句删除索引 DROP INDEX <table or view name>.<index name> DROP INDEX <index name> ON <table or view name ~~~ 4. 查看索引 ~~~sql --[例] 查看表SC的索引。 --用Sp_helpindex存储过程查看索引 EXEC Sp_helpindex SC ~~~ 5. 创建与使用索引 ~~~sql --如果要更改索引名称，可利用Sp_rename存储过程更改，其语法如下： --Sp_rename '数据表名.原索引名', '新索引名' --[例] 更改T表中的索引TI名称为T_Index。 EXEC Sp_rename 'T.TI', 'T_Index' ~~~ -
3.12索引
- 索引是对数据库表中的一个或多个列的值进行排序的结构，其主要目的是提高SQL Server系统的性能，加快数据的查询速度和减少系统的响应时间。
- 索引表时与基本表关联的一种数据结构，它包含基本表中的一列或多列的索引键和基本表中包含各个索引键的行所在的存储位置。不论基本表总是否按索引键有序，但索引中总是按索引键有序的。、
- 小结：对数据库中的一个或多个列的值进行排序，提高系统性能，加快数据查询速度减少系统响应时间。
索引的优点：
创建索引可以大大提高系统的性能。其优点主要表现在：
(1) 通过创建唯一性索引，保证记录的唯一性。
(2) 可以大大加快数据的检索速度。
(3) 可以加速表和表之间的连接。
(4) 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
(5) 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
缺点：索引的存在也让系统付出一定的代价，主要表现在：
(1) 创建索引和维护索引要耗费时间。
(2) 索引需要占用物理空间，每一个索引还要占用一定的物理空间。
(3) 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
创建索引虽然可以提高查询速度，但是他需要牺牲一定的系统性能。因此，哪些列适合创建索引，哪些列不适合创建索引，需要进行详细的考察。
-
索引的分类
-
聚集索引和非聚集索引
聚集索引会对基本表进行物理排序，对查询非常有效，一个基本表只能有一个聚集索引。当建立主键约束时，如果基本表中没有聚集索引，SQL Server会用主键列作聚集索引键。
-
唯一索引和非唯一索引
唯一索引确保在被索引的列中，所有数据都是唯一的，不包含重复的值。
-
简单索引和复合索引
只针对基本表的一列建立的索引，这种索引称为简单索引（single index）。针对多个列（最多包含16列）建立的索引称为复合索引或组合索引（composite index）。
创建索引的基本原则：
(1)大量的索引，会影响INSERT、UPDATE和DELETE语句的性能。
(2)若基本表的数据量大，且对基本表的更新操作较少而查询操作较多时，可以创建多个索引来提高性能。
(3)当视图包含统计函数、表连接或两者组合时，在视图上创建索引可以显著地提高性能。
(4)可以对唯一列或非空列创建聚集索引。
(5)每个表只能创建一个聚集索引。
-
-
索引的创建
CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX <索引名> ON <表名或视图名>(<列名>[ASC|DESC][, … n]) [WITH PAD_INDEX [[,]FILLFACTOR=<填充因子>] [,IGNORE_DUP_KEY] [[,]DROP_EXISTING] … ] UNIQUE：--指定创建的索引为唯一索引。 CLUSTERED | NONCLUSTERED：--用于指定创建的索引为聚集索引/非聚集索引。 ASC|DESC：--用于指定索引列升序/降序，默认设置为ASC。 PAD_INDEX：--指定索引填充。取值为ON|OFF，默认值为OFF。PAD_INDEX选项只用来连接FILLFACTOR，它指定在索引的中间级别页打开的自由空间特定的百分比。例如： WITH (PAD_INDEX=ON,FILLFACTOR=50) 在这个例子中，填充因子配置为50%，为新行保留50%的索引页自由空间。同时也启用了 PAD_INDEX，因此中间索引页也将保留一半的自由空间。 FILLFACTOR：--指定填充因子的大小。使用FILLFACTOR是读与写之间的一个平衡操作。 IGNORE_DUP_KEY：--当向唯一聚集索引或唯一非聚集索引中插入重复数据时，用于忽略重复值输入。此子句要与UNIQUE保留字同时使用。 DROP_EXISTING：--指定应删除并重新创建同名的先前存在的聚集索引或非聚集索引。例 为教学管理数据库的课程表C的列CNAME创建名为I_CNAME的唯一索引。 USE JXGL GO CREATE UNIQUE INDEX I_CNAME ON C(CNAME) GO 例 为选修课程表SC的CNO、GRADE列创建名为I_CNO_GRADE的复合索引。其中CNO为升序，GRAD为降序。 USE JXGL GO CREATE INDEX I_CNO_GRADE ON SC(CNO ASC,GRADE DESC) GO 例 为C表创建输入成批数据时忽略重复值的索引，索引名为I_CNAME_TNAME。填充因子取60。 USE JXGL GO CREATE UNIQUE NONCLUSTERED INDEX I_CNAME_TNAME ON C(CNAME ASC,TNAME ASC) WITH PAD_INDEX, FILLFACTOR=60, IGNORE_DUP_KEY GO 【例9.2】 在stsc数据库中score表的grade列上，创建一个非聚集索引idx_grade。 USE stsc CREATE INDEX idx_grade ON score(grade) 【例9.3】 在stsc数据库中score表的sno列和cno列，创建一个唯一聚集索引idx_sno_cno。 USE stsc CREATE UNIQUE CLUSTERED INDEX idx_sno_cno ON score(stno,cno) -
查看和修改索引属性
ALTER INDEX <索引名>|ALL ON <表> REBUILD [WITH ([[,]PAD_INDEX=ON|OFF] [[,]FILLFACTOR=<填充因子>] [[,]SORT_IN_TEMPDB=ON|OFF])] --其中，SORT_IN_TEMPDB = ON|OFF指定是否在 tempdb 中存储排序结果。 默认值为 OFF。 --例 【例9.6】 修改例9.2创建的索引idx_grade,将填充因子(FILLFACTOR)改为80 USE stsc ALTER INDEX idx_grade ON score REBUILD WITH (PAD_INDEX=ON, FILLFACTOR=80) -
索引的删除
--语法格式： DROP INDEX { index_name ON table_or_view_name [ ,...n ] | table_or_view_name.index_name [ ,...n ] } --【例9.8】删除已建索引idx_grade。 USE stsc DROP INDEX score.idx_grade
3.13完整性
-
完整性分类
-
域完整性
指输入数据的有效性可以通过check约束、default余数、not null约束、数据类型和规则等实现。
-
实体完整性
实体完整性要求表中有一个主键，其值不为空且能唯一的表示对应的记录，又称行完整性，可以通过primary key约束、unique余数、索引或identity属性来实现。
-
参照完整性
保证主表的数据与从表数据的一致性，又称为引用完整性，可以通过定义主键与外键之间的对应关系来实现参照完整性，参照完整性确保键值在所有表中一致。
-
用户自定义完整性
可以定义不属于其它任何完整性类别的特定业务规则，所有完整性类别都支持用户定义完整性，包括 CREATE TABLE 中所有列级约束和表级约束、存储过程以及触发器。
-
-
域完整性
-
实体完整性
-
参照完整性
-
综合训练
3.14T-SQL程序
-
用户自定义数据类型
-
创建用户自定义数据类型
create type 名称 from 数据类型 not null
-
删除用户自定义数据类型
drop type 名称
-
-
用户自定义表数据类型
- create type 名称 as table（字段名1 类型1，…）
-
标识符、常量、变量
-
标识符
标识符用于定义服务器、数据库、数据库对象、变量等的名称，包括常规标识符和分隔标识符两类。
-
常规标识符
常规标识符就是不需要使用分隔标识符进行分隔的标识符，它以字母、下划线()、@或#开头，可后续一个或若干个ASCII字符、Unicode字符、下划线()、美元符号（$）、@或#，但不能全为下划线(_)、@或#。
-
分隔标识符
包含在双引号 (") 或者方括号 ([ ]) 内的常规标识符或不符合常规标识符规则的标识符。
标识符允许的最大长度为128个字符，符合常规标识符格式规则的标识符可以分隔、也可以不分隔，对不符合标识符规则的标识符必须进行分隔。
-
-
常量
-
数值常量
- 整型常量
- 实型常量
-
字符串常量
用一对单引号括起来的若干个合法的字符称为字符串常量
-
日期和时间常量
用一对单引号括起来的符合日期（时间）格式的字符串称为日期（时间）常量。如： ’2017-03-15’、’06:39:23’
-
变量
-
局部变量
由用户声明和使用，局部变量名称前哟@符号
- 定义 ： declare @变量名 类型[ = value] [, …n]
-
全局变量
有SQL Server系统定义和使用的变量，也称为系统变量，通过@符号区别于局部变量，全局变量有系统定义，不能自己定义。
-
局部变量的赋值
-
set 语句 或 select语句
-
格式：set @ 变量名称 = 有效的表达式
-
--【例11.4】 创建两个局部变量并赋值，然后输出变量值。 DECLARE @var1 char(10),@var2 char(20) SET @var1='曹志' SET @var2='是计算机学院的学生' SELECT @var1+@var2 --------------------------------------- 曹志 是计算机学院的学生 -
--【例11.6】将查询结果赋给局部变量。 USE stsc DECLARE @snm char(8) SET @snm=(SELECT stname FROM student WHERE stno= '121002') SELECT @snm --该语句定义局部变量后，将查询结果赋给局部变量。 --------- 周映雪
-
-
输出语句print
-
语法：PRINT <文本串>|<@局部变量>|<@@函数>|<字符串表达式>
-
--例 用PRINT显示变量并生成字符串。 DECLARE @x CHAR(10) SET @x='LOVING' PRINT @x PRINT '最喜爱的歌曲是:'+@x -
--（2）使用SELECT语句赋值，其语法格式如下： 语法格式： SELECT {@local_variable=expression} [,…n] --【例11.7】 使用SELECT语句赋值给变量。 USE stsc DECLARE @no char(5), @name char(10) SELECT @no=stno,@name=stname FROM student WHERE speciality='通信' PRINT @no+''+@name --运行结果： ------------ 121005 刘刚 --【例11.8】 使用排序规则在查询语句中为变量赋值。 USE stsc DECLARE @no char(5), @name char(10) SELECT @no=stno,@name=stname FROM student WHERE speciality='通信' ORDER BY stno DESC PRINT @no+''+@name -- 该语句使用排序规则在SELECT语句中赋值给变量。 -- 运行结果： ----------- 121001 李贤友 -- 【例】 使用SELECT语句从student表中检索出学号为121001的行，再将学生的名字赋给变量@student。 DECLARE @student varchar(8) SELECT @student=stname FROM student WHERE stno='121001' --【例11.9】 使用聚合函数语为变量赋值。 USE stsc DECLARE @hg int SELECT @hg=MAX(grade) FROM score WHERE grade IS NOT NULL PRINT '最高分' PRINT @hg --运行结果： 最高分 95 --【例】 计算student表的记录数并赋值给局部变量@rows。 DECLARE @rows int SET @rows=(SELECT COUNT(*) FROM student) SELECT @rows
-
-
-
-
-
-
运算符与表达式
+ 算数运算符 + 位运算符 + 比较运算符 + 逻辑运算符 + 字符串连接运算符 + 赋值运算符 + 一元运算符 + 运算符优先级 -
流程控制语句
-
if else
-
while
- break
-
continue
-
retuen
-
begin end
--【例11.14】 BEGIN…END语句示例。 BEGIN DECLARE @me char(20) SET @me = '移动电子商务' BEGIN PRINT '变量@me的值为:' PRINT @me END END --运行结果： 变量@me的值为: 移动电子商务 -
IF…ELSE****语句
--【例11.15】IF…ELSE语句示例。 USE stsc GO IF (SELECT AVG(grade) FROM score WHERE cno='102')>80 BEGIN PRINT '课程:102' PRINT '平均成绩良好' END ELSE BEGIN PRINT '课程:102' PRINT '平均成绩一般' END --运行结果： 课程:102 平均成绩良好
-
-
-
系统内置函数
-
用户定义函数
-
标量函数
-
标量函数的定义
--（2） 创建用户定义标量函数spe_av，按专业计算当前学生平均年龄。 USE stsc IF EXISTS(SELECT name FROM sysobjects WHERE name='spe_av' AND type='FN') DROP FUNCTION spe_av GO /*创建用户定义标量函数spe_av，@spe为该函数的形参，对应实参为'通信'或'计算机'专业*/ CREATE FUNCTION spe_av(@spe char(12)) RETURNS int /*函数的返回值类型为整数类型*/ AS BEGIN DECLARE @av int /*定义变量@av为整数类型*/ SELECT @av= /*由实参指定的专业传递给形参@spe作为查询条件，查询统计出该专业的平均年龄 */ ( SELECT avg(age) FROM st_age WHERE specialist=@spe ) RETURN @av /*返回该专业平均年龄的标量值 */ END GO-
标量函数的调用
- 用select语句调用
-
架构名.函数名(实参1,…,实参n)
-
--【例11.35】使用SELECT语句，对上例定义的spe_av函数进行调用。 USE stsc DECLARE @spe char(12) DECLARE @comm int SELECT @spe = '通信' SELECT @comm=dbo.spe_av(@spe) SELECT @comm AS '通信专业学生平均年龄' -- 通信专业学生平均年龄 --------------------------------------- 24
-
execute (exec) 语句调用
用EXECUTE（EXEC）语句调用标量函数的调用形式：
EXEC 变量名=架构名名.函数名 实参1,…,实参n
或
EXEC变量名=架构名.函数名 形参名1=实参1,…, 形参名n=实参n
-
--【例11.36】使用EXEC语句,对上例定义的spe_av函数进行调用。 DECLARE @cpt int EXEC @cpt=dbo.spe_av @spe = '计算机' SELECT @cpt AS '计算机专业学生平均年龄' 运行结果： 计算机专业学生平均年龄 -------------------------------------- 24
-
-
-
-
标志函数
- 内联表值函数 RETURN子句中包含单个SELECT语句。
+
+ - 多表值函数 在 BEGIN…END 语句块中包含多个SELECT语句
- 内联表值函数 RETURN子句中包含单个SELECT语句。
-
-
游标
-
3.15存储过程
-
存储过程概述
- 是T-SQL语句和流程控制语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量，有条件执行以及强大的编程能力。
- 优势
- 高处理能力
- 增强了代码的复用率和共享性
- 减少网络中的数据流量
- 可作为安全机制使用
- 不足
- 可移植性差
- 很多存储过程不支持面向对象的设计
- 代码可读性差，难维护。
- 需要更改时，一般比较繁琐。
-
创建存储过程
--使用T-SQL语句 CREATE PROCEDURE|PROC<存储过程名>[;n] [<@形参名> <数据类型1>[, … n] [<@变参名> <数据类型2>[OUTPUT][, … n] [FOR REPLICATION] AS <T-SQL语句>|<语句块> <@形参名>：过程中的参数。 <@变参名>：指定作为输出参数支持的结果集。 FOR REPLICATION：使用该选项创建的存储过程可用作存储过程的筛选器，且只能在复制过程中执行。 -- 简单的存储过程 存储过程不使用任何参数。 --例 利用STSC数据库“SCORE”表，返回学号为“121001”的学生的成绩情况。 CREATE PROCEDURE S3_Grade AS SELECT * FROM SCORE WHERE STNO='121001' --带输入参数的存储过程 存储过程可以使用输入参数，将值传进存储过程。 --例 利用STSC数据库的三个基本表，创建一个存储过程PS_GRADE，输出指定学生的姓名及课程名称、成绩信息。 CREATE PROCEDURE PS_GRADE @S_NAME CHAR(8) AS SELECT STNAME,CNAME,GRADE FROM STUDENT S JOIN SCORE SC ON S.STNO=SC.STNO AND STNAME=@S_NAME JOIN COURSE C ON SC.CNO=C.CNO @S_NAME作为输入参数，为存储过程传送指定学生的姓名。 EXEC PS_GRADE '李贤友' -- 带输出参数的存储过程 OUTPUT用于指明参数为输出参数。 -- 例 利用STSC数据库的三个基本表，创建一个存储过程PV_GRADE，输入一个学生姓名，输出该学生所有选修课程的平均成绩。 CREATE PROCEDURE PV_GRADE @S_NAME CHAR(8),@S_AVG REAL OUTPUT AS SELECT @S_AVG=AVG(GRADE) FROM STUDENT JOIN SCORE ON STUDENT.STNO=SCORE.STNO AND STNAME=@S_NAME DECLARE @stu_avg real EXEC PV_GRADE '李贤友', @stu_avg OUTPUT SELECT '平均分'=@stu_avg -
调用存储过程
--在需要执行存储过程时，可以使用T-SQL 语句EXECUTE（可以简写为EXEC）。 [EXEC|EXECUTE] { [<@整型变量>=] <存储过程名>[,n] [[<@过程参数>]=<参数值>|<@变参名>[OUTPUT]|[DEFAULT]] [,… n] [WITH RECOMPILE] } <@整型变量>：是一个可选的整型变量，保存存储过程的返回状态。 <存储过程名>：要调用的存储过程名称。 OUTPUT：指定存储过程必须返回一个参数。 --DEFAULT：根据过程的定义，提供参数的默认值。 --WITH RECOMPILE：强制在执行存储过程时对其进行编译，并将其存储起来，以后执行时不再编译。 --例 调用定义存储过程PS_GRADE。 DECLARE @NAME CHAR(9) SET @NAME='马常友' EXEC PS_GRADE @NAME --例 调用存储过程PV_GRADE。 DECLARE @S_AVG REAL EXEC PV_GRADE '姜云',@S_AVG OUTPUT PRINT '姜云平均成绩为：'+STR(@S_AVG) -
管理存储过程
--DEFAULT：根据过程的定义，提供参数的默认值。 WITH RECOMPILE：强制在执行存储过程时对其进行编译，并将其存储起来，以后执行时不再编译。 例 调用定义存储过程PS_GRADE。 DECLARE @NAME CHAR(9) SET @NAME='马常友' EXEC PS_GRADE @NAME 例 调用存储过程PV_GRADE。 DECLARE @S_AVG REAL EXEC PV_GRADE '姜云',@S_AVG OUTPUT PRINT '姜云平均成绩为：'+STR(@S_AVG) --删除存储过程 (1) 利用SSMS图形方式 删除存储过程的步骤如下： ① 在“对象资源管理器”中，展开要删除存储过程的数据库。 ② 依次展开“数据库”、存储过程所属的数据库以及“可编程性”。 ③ 展开“存储过程”，右击要删除的存储过程，在弹出的快捷菜单中选择“删除”菜单项，出现“删除对象”对话框，单击“确定”按钮即可。 (2) 使用T-SQL语句 DROP PROCEDURE<存储过程名>[, … n] 例 删除存储过程SC_GRADE DROP PROCEDURE SC_GRADE
S_NAME JOIN COURSE C ON SC.CNO=C.CNO
@S_NAME作为输入参数，为存储过程传送指定学生的姓名。
EXEC PS_GRADE ‘李贤友’
-- 带输出参数的存储过程
OUTPUT用于指明参数为输出参数。
– 例 利用STSC数据库的三个基本表，创建一个存储过程PV_GRADE，输入一个学生姓名，输出该学生所有选修课程的平均成绩。
CREATE PROCEDURE PV_GRADE
@S_NAME CHAR(8),@S_AVG REAL OUTPUT
AS
SELECT @S_AVG=AVG(GRADE)
FROM STUDENT JOIN SCORE ON STUDENT.STNO=SCORE.STNO AND STNAME=@S_NAME
DECLARE @stu_avg real
EXEC PV_GRADE ‘李贤友’, @stu_avg OUTPUT
SELECT ‘平均分’=@stu_avg
3. 调用存储过程
~~~sql
--在需要执行存储过程时，可以使用T-SQL 语句EXECUTE（可以简写为EXEC）。
[EXEC|EXECUTE]
{
[<@整型变量>=]
<存储过程名>[,n]
[[<@过程参数>]=<参数值>|<@变参名>[OUTPUT]|[DEFAULT]]
[,… n]
[WITH RECOMPILE]
}
<@整型变量>：是一个可选的整型变量，保存存储过程的返回状态。
<存储过程名>：要调用的存储过程名称。
OUTPUT：指定存储过程必须返回一个参数。
--DEFAULT：根据过程的定义，提供参数的默认值。
--WITH RECOMPILE：强制在执行存储过程时对其进行编译，并将其存储起来，以后执行时不再编译。
--例 调用定义存储过程PS_GRADE。
DECLARE @NAME CHAR(9)
SET @NAME='马常友'
EXEC PS_GRADE @NAME
--例 调用存储过程PV_GRADE。
DECLARE @S_AVG REAL
EXEC PV_GRADE '姜云',@S_AVG OUTPUT
PRINT '姜云平均成绩为：'+STR(@S_AVG)
-
管理存储过程
--DEFAULT：根据过程的定义，提供参数的默认值。 WITH RECOMPILE：强制在执行存储过程时对其进行编译，并将其存储起来，以后执行时不再编译。 例 调用定义存储过程PS_GRADE。 DECLARE @NAME CHAR(9) SET @NAME='马常友' EXEC PS_GRADE @NAME 例 调用存储过程PV_GRADE。 DECLARE @S_AVG REAL EXEC PV_GRADE '姜云',@S_AVG OUTPUT PRINT '姜云平均成绩为：'+STR(@S_AVG) --删除存储过程 (1) 利用SSMS图形方式 删除存储过程的步骤如下： ① 在“对象资源管理器”中，展开要删除存储过程的数据库。 ② 依次展开“数据库”、存储过程所属的数据库以及“可编程性”。 ③ 展开“存储过程”，右击要删除的存储过程，在弹出的快捷菜单中选择“删除”菜单项，出现“删除对象”对话框，单击“确定”按钮即可。 (2) 使用T-SQL语句 DROP PROCEDURE<存储过程名>[, … n] 例 删除存储过程SC_GRADE DROP PROCEDURE SC_GRADE
1033
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
