系列文章目录
数据库的建立与管理
对数据库/表/索引/视图的创建、删除+数据的增删查改
一、数据库定义和维护
1.创建数据库
CREATE DATABASE 数据库名
ON PRIMARY(//数据文件、主文件
NAME=数据库名,
FILENAME='存储地址+文件名',
SIZE=初始大小,
MAXSIZE=最大大小,
FILEGROWTH=增长率
)
LOG ON(//日志文件
NAME=日志文件名,
FILENAME='XXX.ldf',
SIZE=XXX
)
FOR RESTORE//能重建一个数据库,可以用于数据恢复
例:建立STU_COURSE(学生_课程库):
定义数据文件:逻辑数据文件名为STU_COURSE;物理数据文件存储在D盘;文件大小为4MB,最大为50MB;增长率为5MB
CREATE DATABASE STU_COURSE
ON PRIMARY(
NAME=STU_COURSE,
FILENAME='D:\STU_COURSE.mdf',
SIZE=5MB,
MAXSIZE=50MB,
FILEGROWTH=5MB)
GO
其中:
数据文件定义:ON PRIMARY( )
PRIMARY指明主文件名(.mdf)
可以定义多个数据文件,默认第一个为主文件
创建文件组:
CREATE DATABASE 数据库名
ON (PRIMARY)/*主文件组*/ (
NAME=主文件名1,//主文件组中的主文件1
FILENAME=***,
SIZE=***,
...),
(NAME=文件名2,//主文件组中的文件2
FILENAME=***,
SIZE=***,
...),
FILEGROUP 第2文件组名(
NAME=***,//第二个文件组中的文件1
FILENAME=***,
...),
(
NAME=***,//第二个文件组中的文件2
FILENAME=***,
...)
LOG ON(NAME=日志文件1,
...),
(NAME=日志文件2,
...
)
例:创建具有两个文件组的数据库TEST3。要求:
- 主文件组包含两个数据文件:TEST3_data1:初始大小为20MB、最大为60MB,按5MB增长;2:初始大小2MB、最大为20MB、增长为10%
- 有一个文件组名为TEST3GROUP1,包含文件TEST3_data2:初始大小10MB、最大为30MB、增长为10%
CREATE DATABASE TEST3
ON PRIMARY(
NAME='TEST3_data1',
FILENAME='D:\TEST3_data1.mdf',
SIZE=20MB,
MAXSIZE=60MB,
FILEGROWTH=5MB),
(NAME='2',
FILENAME='D:\TEST3_2.mdf',
SIZE=2MB,MAXSIZE=20MB,
FILEGROWTH=10%),
FILEGROUP TEST3GROUP1(
NAME='TEST3_data2',
FILENAME='D:\TEST3DATA2.ndf',
SIZE=10MB,
MAXSIZE=30MB,
FILEGROWTH=10%)
LOG ON(
NAME='TEST3_LOG',
FILENAME='D:\LOG.ldf',
SIZE=50MB,
MAXSIZE=100MB,
FILEGROWTH=10MB)
GO
2.选择数据库
USE 数据库名USE STU_COURSE
3.删除数据库
DROP DATABASE 数据库名DROP DATABASE STU_COURSE
4.修改数据库
修改文件大小/增长方式
ALTER DATABASE 数据库名
MODIFY FILE//修改数据文件/日志文件都适用
(
NAME=要修改的文件名,
MAXSIZE=修改值,
FILEGROWTH=修改值
)
例:修改数据库TEST3中的文件TEST3_1,使文件最大大小为无限大并以5MB增长
ALTER DATABASE TEST3
MODIFY FILE
(NAME='TEST3_1',
MAXSIZE=UNLIMITED,//无限大的表示
FILEGROWTH=5MB)
GO
增加/删除文件/文件组
增加、删除文件/日志文件
ALTER DATABASE 数据库名
ADD FILE/LOG FILE//数据文件/日志文件
(
文件定义内容
)
REMOVE FILE 删除的文件名
增加、删除文件组
ALTER DATABASE 数据库名
ADD FILE
(
文件定义内容//文件组中的文件1
),
(
文件定义内容//文件2
)
TO FILEGROUP 新增文件组名
REMOVE FILEGROUP 删除的文件组名
二、表的定义和维护
1.表的建立
CREATE TABLE 表名(
列名/属性 数据类型 (字段约束),
...,
CONSTRAINT 约束名 记录约束,
...)
2.约束
- 字段约束
列约束条件
可选项:
字段约束式 | 含义 |
---|---|
NOT NULL/NULL | 不允许/允许字段为空 |
PRIMARY KEY (CLUSTERED/NON CLUSTERED) | 定义字段为主码(并建立聚集索引/非聚集索引) |
REFERENCE 参照表(对应字段) | 定义该字段为外码,参照表中的对应字段 |
DEFAULT 默认值 | 定义字段默认值 |
CHECK(条件) | 定义字段满足的条件 |
IDENTITY(初始值,步长) | 定义字段为数值型数据,并指出初始值和逐步增加的步长值 |
UNIQUE | 定义不允许重复值 |
- 记录约束
格式CONSTRAINT 约束名 约束式
包括记录中、表间数据约束
约束式 | 含义 |
---|---|
PRIMARY KEY(列名组) | 定义表的主码 |
PRIMARY KEY CLUSTERED/NONCLUSTERED KEY(列名组) | 定义表的主码并建立主码的聚集/非聚集索引 |
FOREIGN KEY(外码) REFERENCES 参照表(对应列) | 指出表的外码和参照表、对应列 |
CHECK(条件表达式) | 定义记录满足的条件 |
UNIQUE(列组) | 定义不允许重复值的字段组 |
例:建立STU_COURSE中的表:
学生(SNO学号char(5),姓名char(8),年龄smallint,性别char(2),所在系char(20))
课程(课程号char(5),课程名varchar(20),先行课char(5))
选课(学号,课程号,成绩smallint)
学号不能为空且不能重复;姓名不能为空;年龄默认为20;性别必须为‘男’或‘女’
课程表中定义了课程号为主码的表级约束条件
选课表中定义学号和课程号为主码的约束;成绩在0-100之间的约束。
学号为外码,参照表为学生表,对应学生表中的学号约束。课程号为外码,参照表为课程表,对应课程表中的课程号约束。
USE STU_COURSE
GO
CREATE TABLE STUDENT(
SNO CHAR(5) NOT NULL UNIQUE,
SNAME CHAR(8) NOT NULL,
SAGE SMALLINT DEFAULT 20,
SEX CHAR(2) CHECK(SEX IN ('男', '女')),
DEPART CHAR(20)
)
GO
CREATE TABLE COURSE(
CNO CHAR(5) PRIMARY KEY,
CNAME VARCHAR(20),
PRE CHAR(5)
)
GO
CREATE TABLE PICK(
SNO CHAR(5),
CNO CHAR(5),
SCORE SMALLINT CHECK(SCORE BETWEEN 0 AND 100),
CONSTRAINT C1 PRIMARY KEY(SNO,CNO),
CONSTRAINT C2 FOREIGN KEY(SNO) REFERENCES STUDENT(SNO),//定义主码、外码后均加()
CONSTRAINT C3 FOREIGN KEY(CNO) REFERENCES COURSE(CNO)
)
GO
3.表的修改
3.1 修改字段
一般不允许修改数据类型或减少字段宽度
ALTER TABLE 表名
ALTER COLUMN 列名 (修改为)数据类型 (约束)
例:修改STUDENT表中学号SNO的定义,将其字段宽度增加为char(6)
ALTER TABLE STUDENT
ALTER COLUMN SNO CHAR(6) NOT NULL UNIQUE
3.2增加字段(列)和表约束
ALTER TABLE 表名
ADD 列名 类型 (约束),//增加列
ADD CONSTRAINT 约束名 约束式//增加约束
例:在学生表中加入GRADE,数据类型为int,允许空值
ALTER TABLE STUDENT ADD GRADE INT NULL
在学生表中加入年龄>18的约束
ALTER TABLE STUDENT
ADD CONSTRAINT C4 CHECK(SAGE > 18)
3.3 删除字段或约束
仅输入列名/约束名
ALTER TABLE 表名
DROP COLUMN 列定名
DROP CONSTRAINT 约束名
例:
删除学生表中的年级列
ALTER TABLE STUDENT
DROP COLUMN GRADE
删除学生表中的约束C4(年龄>18)
ALTER TABLE STUDENT
DROP CONSTRAINT C4
3.4 使约束有效/无效
ALTER TABLE 表名
CHECK/NOCHECK CONSTRAINT 约束名(:ALL/约束名组)
例:使PICK表中的C2约束无效
ALTER TABLE PICK
NOCHECK CONSTRAINT C2
使PICK表中的C2约束有效
ALTER TABLE PICK
CHECK CONSTRAINT C2
3.5 删除表
DROP TABLE 表名
4.创建、管理索引
4.1 创建索引
创建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(列名)
创建聚集/非聚集索引
CREATE CLUSTERED/NONCLUSTERED INDEX 索引名 ON 表名(列名)
例:在STUDENT表中创建一个名为STU_NAME的唯一聚集索引
CREATE UNIQUE CLUSTERED INDEX STU_NAME ON STUDENT(SNAME)
创建一个STUDENT表中DEPART和GRADE的复合索引
CREATE INDEX DEP_GRADE ON STUDENT(DEPART,GRADE)
4.2 删除索引
DROP INDEX 表名.索引名
三、数据的增删查
1.数据插入
INSERT INTO 表名/表名(列名1,列名2)
VALUES(常量/常量1,常量2...)
例:
插入数据均有对应
将新学生记录(学号:98010,姓名:张三,年龄:20,性别:男,所在系:计算机系)插入到STUDENT表中
INSERT INTO STUDENT VALUES('98010','张三',20,'男','计算机系')
插入部分数据
插入一条选课记录(学号:98011,课程号:C10,成绩不详)
INSERT INTO PICK(SNO,CNO) VALUES('98011','C10')
2.数据修改
UPDATE 表名 SET 列名=表达式
(WHERE 条件)
例:将STUDENT表中所有学生年龄+2
UPDATE STUDENT
SET SAGE=SAGE+2
GO
将PICK表中的数据库课程的成绩乘以0.8
UPDATE PICK
SET SCORE=SCORE*0.8 WHERE CNO='C11'
GO
3.数据删除
DELETE FROM 表名
WHERE 条件
例:删除李斯数据库的选课记录
DELETE FROM PICK
WHERE SNO='98011' AND CNO='C11'
GO
4.数据的查询
4.1 选择列
选择所有列:
SELECT * FROM 表名
选择一个表中特定的列:
SELECT 列名 FROM 表名
(WHERE 条件)//看情况加
定义列别名
在列名之后使用AS子句更改查询结果的列标题名
例:查询PICK表中选修C语言的同学的学号和成绩,并将结果中各列的标题指定为’学号’、‘C语言成绩’
SELECT SNO AS '学号',SCORE AS 'C语言成绩'
FROM PICK WHERE CNO='C10'
GO
结果:
消除重复行
SELECT DISTINCT 列名 FROM 表名
限制结果集返回行数
SELECT TOP expression 列名 FROM 表名
expression可以是数字或百分比
4.2 聚合函数
求总值、平均值
SELECT SUM/AVG(所求列名) (AS 更改列名) FROM 表名
WHERE 条件
例:求选修C10课程的学生的平均成绩,并在结果中改列名
SELECT AVG(SCORE) AS '平均成绩' FROM STUDENT
WHERE SNO IN(SELECT SNO FROM PICK WHERE CNO='C10')
求最大值、最小值
SELECT MAX/MIN(列名) FROM 表名
WHERE 条件
求统计总数
SELECT COUNT(expression /*) FROM 表名
WHERE
COUNT忽略NULL值
但使用COUNT(*)时会返回检索行的总数目,不论是否包含空值
例:统计成绩不为空的学生数
SELECT COUNT(SCORE) FROM PICK//自动不计入空值
4.3 WHERE子句
WHERE子句必须紧跟在FROM子句之后
模式匹配LIKE
查询中的运用:
SELECT 列名 FROM 表名
WHERE 列名 LIKE 匹配的模式串(ESCAPE 转义字符)
通配符列表
通配符 | 含义 |
---|---|
% | 0或多个字符 |
_ | 代表单个字符 |
[ ] | 指定范围中的任何单个字符(:[a-f]、[0-9]或集合[abcdefg]) |
[^] | 指定不属于范围中的任何单个字符([^0-9]) |
例:查学生表中王姓学生的情况
SELECT * FROM STUDENT
WHERE SNAME LIKE '王%'
查询姓“张”且单名的学生情况:
......
SNAME LIKE '张_'
查询STUDENT表中学号倒数第3个数字为1,且倒数第1个数在1~5之间的学生学号、姓名及所在系
SELECT SNO,SNAME,DEPART FROM STUDENT
WHERE SNO LIKE '%1_[1-5]'
查询STUDENT表中名字包含下划线的学生学号和姓名
SELECT SNO,SNAME FROM STUDENT
WHERE SNAME LIKE '%#_%' ESCAPE '#'//转义符中有'_',ESCAPE指定'#'表示让'_'转义
范围比较、空值比较、比较子查询
BETWEEN、IN:
表达式的值 BETWEEN/NOT BETWEEN 值1 AND 值2
表达式的值 IN(值表、集合/子查询...)
空值比较:
IS NULL
SELECT 列名 FROM 表名
WHERE 列名 IS NULL
比较子查询ALL/SOME/ANY:
列名 比较运算符(><=/!=/!>/!</...) ALL/SOME/ANY(SELECT ... //子查询)
- ALL指定表达式要与子查询结果集中的每个值都进行比较,当表达式与每个值都满足比较的关系时,才返回TRUE,否则返回FALSE;
- SOME或ANY表示表达式只要与子查询结果集中的某个值满足比较的关系,就返回TRUE,否则返回FALSE。
例:查找C语言课程成绩不低于数据库最低成绩的学生学号
SELECT SNO FROM PICK
WHERE CNO='C10' AND SCORE !< ANY
(
SELECT SCORE FROM PICK
WHERE CNO='C11'
)
EXISTS子查询
测视子查询的结果是否为空表,若不为空,返回TRUE;否则,返回FALSE
也可以前面加NOT
SELECT 列名 FROM 表名
WHERE (NOT)EXISTS(SELECT...//子查询)
4.4 简单查询
ORDER BY子句
SELECT 列名 FROM 表名
(WHERE 条件)
ORDER BY 排序项 ASC/DESC
例:将PICK表中学生的C语言成绩降序排列
SELECT SCORE FROM PICK
WHERE CNO='C10'
ORDER BY SCORE DESC
GO
连接子句
- 连接谓词:
在SELECT语句的WHERE子句中使用比较运算符给出连接条件对表进行连接
若选择的字段名在各个表中不唯一,则字段名前要加对应的表名表名.字段名
若是唯一的,则可以省略字段名前的表名
例:查找STU_COURSE数据库每个学生的情况以及选修的课程情况
SELECT STUDENT.*,PICK.* FROM STUDENT,PICK
WHERE STUDENT.SNO=PICK.SNO
- 内连接:
ON指定连接条件
SELECT 列名 FROM 表名 INNER JOIN 表名
ON 比较表达式(:表名.列名 比较运算符><= 表名.列名)
(WHERE 条件)
例: 用FROM子句的JOIN关键字表达下列查询:查找选修了’C10’号课程且成绩在80分以上的学生姓名及成绩。
SELECT SNAME,SCORE FROM STUDENT INNER JOIN PICK
ON STUDENT.SNO=PICK.SNO//连接条件
WHERE CNO='C10' AND SCORE > 80//查询条件
内连接可以同时进行,将n个表连接在一起
例:用FROM子句的JOIN关键字表达下列查询:查找选修了“C语言”课程且成绩在80分以上的学生学号、姓名、课程名及成绩
SELECT STUDENT.SNO,SNAME,CNAME,SCORE
FROM STUDENT INNER JOIN PICK ON STUDENT.SNO=PICK.SNO
INNER JOIN COURSE ON PICK.CNO=COURSE.CNO
WHERE PICK.CNO='C10' AND SCORE > 80
- 外连接:
除满足连接条件的行外,还应包括表中所有行
SELECT 列名 FROM 表名 LEFT/RIGHT/FULL OUTER JOIN 表名
ON 比较表达式(:表名.列名 比较运算符><= 表名.列名)
(WHERE 条件)
- 交叉连接
实际上是将两个表进行笛卡尔积运算,结果表是由第一个表的每一行与第二个表的每一行拼接后形成的表,因此结果表的行数等于两个表的行数之积
HAVING子句
与WHERE子句的查询条件类似,不过HAVING子句中可以使用聚合函数
SELECT 列名 FROM 表名
HAVING 条件(可以用AVG/SUM/MAX/MIN/COUNT(子查询))
例:查找选修课程超过2门且成绩都在80分以上的学生的学号
SELECT SNO FROM PICK
WHERE SCORE > 80
GROUP BY SNO
HAVING COUNT(*)>2
4.5 SELECT子句其他用法
INTO子句
将SELECT查询所得结果保存到新建的表
SELECT 列名
INTO 新表
FROM 要查询的表名
(WHERE 条件/HAVING 条件)
集合查询UNION、INTERSECT和EXCEPT
UNION 并
将两个或多个SELECT查询的结果合并为一个结果集
基本规则:
- 所有查询的列数、列的顺序相同
- 数据类型兼容
SELECT 列名1,2,... FROM 表名
WHERE 条件
UNION ALL
SELECT 列名1,2,... FROM 表名
WHERE 条件
INTERSECT 交
EXCEPT 差
EXCEPT和INTERSECT比较两个查询的结果,返回非重复值
SELECT ...查询1
EXCEPT/INTERSECT
SELECT ...查询2
EXCEPT从左边查询中返回右边查询没有找到的非重复值
INTERSECT返回左右两边查询都返回的所有非重复值
EXCEPT和INTERSECT返回的结果集的列名均与左边查询相同
例:STUDENT表中查找专业为计算机系但是性别不为男的学生信息
SELECT * FROM STUDENT
EXCEPT
SELECT * FROM STUDENT WHERE SEX='男'
GO
四、SQL附加语句
1.变量的定义和使用
局部变量
定义:
DECLARE @变量名 数据类型
赋值:
SET @变量名=表达式
SELECT @变量名=表达式/列表达式(列名 FROM 表名 ...)
输出:
PRINT '字符串'/@局部变量/@@全局变量
数据转换:
CAST(表达式 AS 数据类型/长度)
CONVERT(数据类型/长度,表达式/列名)
例:检索C语言成绩在80-90分之间的学生姓名,并将成绩数据类型转化为CHAR(5)
SELECT SNAME,SCORE AS 'C语言成绩'
FROM STUDENT INNER JOIN PICK
ON STUDENT.SNO=PICK.SNO
WHERE CNO='C10' AND CAST(SCORE AS CHAR(5)) BETWEEN 80 AND 90
GO
2.流程控制语句
分支语句IF…ELSE
IF(条件表达式)
执行表达式/语句块
ELSE
执行表达式
块语句BEGIN…END
BEGIN
语句/语句块
END
循环语句WHILE
WHILE(条件表达式)
循环体
CASE表达式
CASE
WHEN 条件1 THEN 表达式1
WHEN 条件2 THEN 表达式2
...
ELSE 表达式
END
例:查询计算机系学生的各选课成绩,并给予等级评定(>90为优秀)
SELECT SCORE=
CASE
WHEN SCORE>90 THEN '优秀'
ELSE '不优秀'
END
FROM PICK
WHERE SNO IN(SELECT SNO FROM STUDENT WHERE DEPART ='计算机系')
GO
3.综合应用
例:将学号为98010的学生的总学分使用循环修改到60,每次只加2,并判断循环了多少次
DECLARE @NUM INT
SET @NUM=0
WHILE (SELECT MARK FROM STUDENT WHERE SNO='98010')<60
BEGIN
UPDATE STUDENT
SET MARK=MARK+2 WHERE SNO='98010'
SET @NUM=@NUM+1
END
SELECT @NUM AS '循环次数'
五、视图的创建、修改、删除、查询
1.视图的创建
CREATE VIEW语句
CREATE VIEW 视图名(列名1,2,...)
AS
SELECT 列名 FROM 表名
(条件)
例:创建CS视图,包含计算机系各学生的学号、姓名、选修课程号及成绩
CREATE VIEW CS
AS
SELECT STUDENT.SNO,SNAME,CNO,SCORE FROM STUDENT,PICK
WHERE DEPART='计算机系'
GO
2.视图的修改
ALTER VIEW语句修改视图
ALTER VIEW 视图名(列名)
AS
SELECT表达式
3.删除视图
DROP VIEW 视图名
4.视图的查询
例:查找平均成绩在80分以上的学生的学号和平均成绩。
CREATE VIEW NO_AVG(NO,AVGSCORE)
AS
SELECT SNO,AVG(SCORE) FROM PICK
GROUP BY SNO
GO//先建立视图
SELECT * FROM NO_AVG
WHERE AVG_SCORE > 80
GO//再对视图进行查询
六、触发器
创建触发器
CREATE TRIGGER 触发器名
ON 表名 AFTER DELETE/INSERT/UPDATE
AS
SQL语句组//触发器的执行语句
例:创建触发器DEL,当删除STUDENT中的学生记录时,将PICK表中对应的选课记录也删除
CREATE TRIGGER DEL
ON STUDENT AFTER DELETE
AS
BEGIN
DELETE FROM PICK
WHERE SNO IN(SELECT SNO FROM DELETED)
END
GO
删除触发器
DROP TRIGGER 触发器名组