文章目录
注： 此处跳转到习题总练习
一、数据定义
1、数据库的定义
1. 数据库的创建
//注：接下来模板中的[ ]表示非必要语句.
- 模板
CREATE DATABASE <数据库名>
[ON [PRIMARY][(NAME=<逻辑数据文件名>，]
FILENAME=‘<操作数据文件路径和文件名>’
[，SIZE=<文件长度>]
[，MAXSIZE=<最大长度>]
[，FILEGROWTH=<文件增长率>] ) [，…n]]
[LOG ON ([NAME= <逻辑日志文件名> ，]
FILENAME=‘<操作日志文件路径和文件名>’
[，SIZE= <文件长度>]
[，MAXSIZE=<最大长度>] [，…n]]
[FOR RESTORE]
- 举例
建立一个学生选课表 S_C，并将数据库文件放在指定位置。
CREATE DATABASE S_C
ON PRIMARY(
NAME='s_c_data', /*逻辑数据文件名，注意格式一般_data结尾*/
FILENAME='D:\Retro_sql\s_c_data.mdf' /*操作数据文件路径和文件名，注意格式一般data.mdf结尾(primary data file)*/
--，SIZE= ，MAXSIZE= /*文件长度等等其他操作*/
)
LOG ON(
NAME='s_c_log', /*逻辑日志文件名，注意格式一般_log结尾*/
FILENAME='D:\Retro_sql\s_c_log.ldf' /*操作日志文件路径和文件名，注意格式一般log.ldf结尾(Log data files)*/
)
/*exec sp_helpdb S_C 此代码可以查看数据库信息*/
该数据库信息查询如下：
2. 数据库的使用和删除
- 模板
USE〈数据库名〉 /*使用数据库*/
DROP DATABASE <数据库名> /*删除数据库*/
- 举例
USE S_C /*使用数据库*/
DROP DATABASE S_C /*删除数据库*/
2、表的定义
- 模板
CREATE TABLE <表名>
(< 列 名 >< 数 据 数 型 >[< 列 级 完 整 性 约 束 条 件 >]
[,……n] [,<表级完整性约束>]);
*如果约束条件涉及多个属性列，则必须定义为表级完整性约束。
*如果约束条件只是涉及一个属性列，则既可以定义在表级完整性约束，也可以定义在列级，
*接下来做举例：
- 举例
例子一：建表，设置主键、唯一键、not null 约束 和其他自定义约束，设置默认值。
/* 建学生表 */
/*建表语句一：列级完整性约束写法*/
CREATE TABLE Student
(
Sno CHAR(10) PRIMARY KEY, /*列级完整性约束，主键*/
Sname CHAR(20) NOT NULL UNIQUE, /*列级完整性约束，不可为空且唯一*/
Ssex CHAR(2) NOT NULL, /*如果不加 NOT NULL，默认可以为NULL*/
Sage SMALLINT NOT NULL,
Sdept CHAR(20) NOT NULL
);
/*建表语句二：表级约束写法*/
CREATE TABLE Student
(
Sno CHAR(10),
Sname CHAR(20),
Ssex CHAR(2) NOT NULL,
Sage SMALLINT,
Sdept CHAR(20) ,
CONSTRAINT SnoKey PRIMARY KEY(Sno),/*定义在表级完整性*/
UNIQUE (Sname) /*定义在表级完整性*/
);
/* 建表语句三：统一表级定义键与自定义约束，并加上默认值 */
/* 一般我习惯这么写，既能清晰统一，还能自定义约束名，不过默认值为了方便就直接接上了 */
CREATE TABLE Student
(
Sno CHAR(10) NOT NULL,
Sname CHAR(20) NOT NULL,
Ssex CHAR(2) NOT NULL,
Sage SMALLINT NOT NULL DEFAULT (18), /* 设置默认值 */
Sdept CHAR(20) NOT NULL DEFAULT ('计算机学院'),
CONSTRAINT SsexKey CHECK (Ssex IN ('男','女')), /* 性别只能为男or女 */
CONSTRAINT SageKey CHECK (Sage > 0), /* 年龄>0 */
CONSTRAINT SnoKey PRIMARY KEY(Sno), /* 设置主键 */
CONSTRAINT UqSname UNIQUE (Sname) /* 设置唯一键 */
);
/* 同理建课程表 */
CREATE TABLE Course
(
Cno CHAR(10) NOT NULL,
Cname CHAR(16) NOT NULL,
Ccredit SMALLINT NOT NULL,
Cpno CHAR(3) NULL,
CONSTRAINT CnoKey PRIMARY KEY(Cno)
);
这里可以看到一个区别，就是第二个建表语句主键加了 “CONSTRAINT SnoKey” 语句后可以自定键名，由下图可以观察到区别：
例子二：建表，设置多属性主键、外键
CREATE TABLE Sc
(
Sno CHAR(10) NOT NULL,
Cno CHAR(10) NOT NULL,
Grade SMALLINT NULL,
CONSTRAINT ck_Grade CHECK ( Grade<= 100),
CONSTRAINT SCnoKey PRIMARY KEY(Sno,Cno),/* 多属性主键设置 */
CONSTRAINT fk_Sno FOREIGN KEY(Sno) REFERENCES Student(Sno), /* 外键键设置 */
CONSTRAINT fk_Cno FOREIGN KEY(Cno) REFERENCES Course(Cno)
);
3、表的修改
- 模板
ALTER TABLE <表名 > --注意ALTER表时不需要加括号，与CREATE不同
[ ADD <新列名> <数据类型> [完整性约束] ]
[ ADD [CONSTRAINT <约束名>] <完整性约束>]
[ DROP <完整性约束> ]
[ DROP COLUMN< 列名> ]
[ ALTER COLUMN <列名><数据类型>]；
- 举例
例一：添加列
/* （1）添加列 */
/* 添加一列，学生表增加专业，默认为“计算机科学与技术”*/
ALTER TABLE Student
ADD Smajor CHAR(20) DEFAULT('计算机科学与技术')
例二：删除列
/* （2）删除列 */
/* 删除学生表专业列*/
ALTER TABLE Student
DROP COLUMN Smajor
例三：更改列
/* （3）更改列 */
/* 修改选课表的'GRADE'字段为INT;（列上存在约束时可能无法修改）*/
ALTER TABLE Sc
ALTER COLUMN Grade INT
例四：添加完整性约束
/* （4）添加实体完整性 */
/* 为学生表添加主键约束 */
ALTER TABLE Student
ADD CONSTRAINT PK_Sno PRIMARY KEY(Sno)
/* 为选课表添加主键约束 */
ALTER TABLE Sc
ADD CONSTRAINT SCnoKey PRIMARY KEY(Sno,Cno)
/* （5）添加参照完整性 */
/* 为选课表添加外键约束 */
ALTER TABLE Sc
ADD CONSTRAINT fk_Sno FOREIGN KEY(Sno) REFERENCES Student(Sno)
ALTER TABLE Sc
ADD CONSTRAINT fk_Cno FOREIGN KEY(Cno) REFERENCES Course(Cno)
/* （6）添加用户自定义完整性 */
/* 为一列增加约束，约束学生表性别只能为男or女 */
ALTER TABLE Student
ADD CONSTRAINT CK_Ssex CHECK(Ssex IN ('男','女'))
/* 为一列增加约束，约束学生表年龄>0 */
ALTER TABLE Student
ADD CONSTRAINT CK_Sage CHECK(Sage > 0)
/* 为一列增加默认，默认学生表专业为“计算机科学与技术” */
ALTER TABLE Student
ADD CONSTRAINT DF_Smajor DEFAULT('计算机科学与技术') FOR Smajor
例五：删除完整性约束
/* （7）删除完整性约束 */
/* 删除选课表主键SCnoKey */
ALTER TABLE Sc
DROP SCnoKey
4、表的删除
- 模板
DROP TABLE <表名> [RESTRICT|CASCADE];
--RESTRICT会考虑到约束、依赖等关系，默认为RESTRICT
--但CASCADE会不考虑而全都删除（比如视图）
二、数据查询
- 模板
SELECT [ALL|DISTINCT] <目标列名> [,<目标列名>...]
FROM <表名或视图名> [,<表名或视图名>...]
WHERE <条件表达式> --查询条件
GROUP BY <列名1> [HAVING <条件表达式>] --分组
ORDER BY <列名1> [ASC|DESC] --排序，升序|降序
注：这里很多时候会用到GROUP BY 和 ORDER BY语句，这里就不多赘述了，可以从具体例子中体会。
0、查询条件语句和聚集函数
- 上述模板中WHERER子句一般有如下常用常用查询条件语句
- 比较：< , > , = , <> 等;
- 确定范围：[NOT] BETWEEN AND;
- 确定集合：[NOT] IN;
- 字符匹配：[NOT] LIKE;
- 空值判断：IS [NOT] NULL;
- 多重条件：AND , OR , NOT
- 其中SELECT和GROUP BY 的 HAVING 可以用聚集函数（WHERE不行）
- COUNT(*)：统计元组数量;
- COUNT([DISTINCT|ALL] <列名>)：统计一列中值的个数;
- SUM([DISTINCT|ALL] <列名>) 计算一列中值的总和;
- AVG([DISTINCT|ALL] <列名>) 计算一列中值的平均值;
- MAX([DISTINCT|ALL] <列名>) 计算一列中值的最大值;
- MIN([DISTINCT|ALL] <列名>) 计算一列中值的最小值;
注：聚集函数里也可以加上条件，如：
SELECT COUNT(CASE WHEN 选课.Grade>80 THEN 1 ELSE NULL END) 成绩大于80的人数
1、单表查询
-
单表查询，顾名思义也就是在一个表内查询，是最简单的查询方式，对应最简单的需求。
-
举例
例一：查询数学系男学生的学号和姓名；
SELECT Sno,Sname
FROM Student
WHERE Sdent='数学学院' AND
Ssex='男';
2、连接查询
- 连接查询需要多表的数据，一般将多表连接起来后根据查询条件完成查询。
（1）一般连接
- 举例
例一：查询选修了1号课程的学生学号
SELECT Sc.Sno
FROM Student,Sc
WHERE Student.Sno=Sc.Sno AND
Sc.Cno='001';
例二：查询成绩在80~90分之间的学生学号和成绩
SELECT Sc.Sno,Sc.Grade
FROM Student,Sc
WHERE Student.Sno=Sc.Sno AND
Sc.Grade BETWEEN 80 AND 90;
例三：查询选修了1号课程且成绩在80~90分之间的学生学号和成绩，并将成绩乘以系数0.8输出
SELECT Sc.Sno,0.8*Sc.Grade 折算成绩
FROM Student,Sc
WHERE Student.Sno=Sc.Sno AND
Sc.Cno='001' AND
Sc.Grade BETWEEN 80 AND 90;
例四：查询数学系或计算机系姓张的学生的信息
SELECT *
FROM Student
WHERE (Sdent='数学学院' OR
Sdent='计算机学院') AND
Sname LIKE '张%'; --%表示任意长度，_表示单个字符，由于无法确认名长度，所以用%
（2）连接+排序
- 举例
例一：查询选修了1号课程的学生学号和成绩，并要求结果按成绩降序排列
SELECT Sc.Sno,Sc.Grade
FROM Student,Sc
WHERE Student.Sno=Sc.Sno AND
Sc.Cno='001'
ORDER BY Grade DESC;
例一：查询选修了1号课程的学生学号和成绩，并要求结果按成绩降序排列，如果成绩相同，则按学号升序排列
SELECT Sc.Sno,Sc.Grade
FROM Student,Sc
WHERE Student.Sno=Sc.Sno AND
Sc.Cno='001'
ORDER BY Grade DESC , Sc.Sno ASC;
（3）外连接
例一：查询学生的学号、姓名、选修的课程名称及成绩；（要求：没有选课的也查出来）
SELECT Student.Sno,Sname,Cname,Grade
FROM Student
LEFT OUTER JOIN Sc
ON Student.Sno=Sc.Sno
LEFT OUTER JOIN Course
ON Sc.Cno=Course.Cno;
3、嵌套查询
- 除了连接查询，嵌套查询也是非常实用的查询语句。
- 嵌套时的子查询有谓词ANY 和 ALL，如WHERE Grade> ALL(…),记得注意这俩个区别。
- 同时谓词EXIST也是一个小难点，使用WHERE EXIST(…)后，如果子查询未查到值，就直接返回false，否则相反；NOT EXIST同理。下面进阶嵌套里有具体例子。
（1）基础嵌套
例一： 查询选修了“高等数学”的学生学号和姓名
SELECT Sno,Sname
FROM Student
WHERE Sno IN
(SELECT Sno
FROM Sc
WHERE Cno IN
(SELECT Cno
FROM Course
WHERE Cname='高等数学'))
例二： 查询其他系中年龄小于计算机系年龄最大者的学生
SELECT *
FROM Student
WHERE Sdent<>'计算机学院' AND
Sage<
(SELECT MAX(Sage)
FROM Student
WHERE Sdent='计算机学院');
例三： 查询其他系中比计算机系学生年龄都小的学生
SELECT *
FROM Student
WHERE Sdent<>'计算机学院' AND
Sage<ALL
(SELECT Sage
FROM Student
WHERE Sdent='计算机学院');
（2）进阶嵌套(EXISITS)
例一： 查询没有选修“信息系统”课程的学生姓名
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT Sno
FROM Sc
WHERE Sc.Sno=Student.Sno AND
Sc.Cno IN
(SELECT Cno
FROM Course
WHERE Cname='信息系统'
));
例二：查询选修了全部课程的学生姓名
这种需求我们可以这么理解：当我们要查询选修了所有课程的学生，那么对于其中一个学生来说，没有一门课是他没选的，这样就巧妙地将全称量词转化为了存在量词，即EXISTS。
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM Course
WHERE NOT EXISTS
(SELECT *
FROM Sc
WHERE Sc.Cno=Course.Cno AND
Sc.Sno=Student.Sno));
例三：查询至少选修了学号为“95002”的学生所选修的全部课程的学生学号和姓名
SELECT Sno,Sname
FROM Student
WHERE Sno<>'95002' AND
NOT EXISTS
(SELECT *
FROM Course
WHERE Cno IN(
SELECT Cno
FROM Sc
WHERE Sc.Sno='95002'
)
AND
NOT EXISTS
(SELECT *
FROM Sc
WHERE Sc.Cno=Course.Cno AND
Sc.Sno=Student.Sno ));
--或者
SELECT Sno,Sname
FROM Student
WHERE Sno<>'95002' AND
NOT EXISTS
(SELECT *
FROM Sc SCX
WHERE SCX.Sno='95002' AND
NOT EXISTS
(SELECT *
FROM Sc SCY
WHERE SCY.Cno=SCX.Cno AND
SCY.Sno=Student.Sno));
4、集合查询
- 集合查询概述：SELECT查询后得到的是元组的集合，即n行数据，那么对于这些已经查询得到的集合，我们可以对其进行集合操作，即集合查询。
- 集合查询前提：各元组集合列数相同，数据类型相应相等。
- 集合查询操作
（1）并操作：UNION
（2）交操作：INTERSECT
（3）差操作：EXCEPT
直接将俩个查询间加上集合操作即可。
5、派生表查询
- 派生表查询：子查询一般出现在WHERE中，但当我们把子查询放到FROM子句中，这时FROM子句下的就是子查询生成的派生表，派生表是临时的是一种很灵活的写法。
- 举例
由于学习阶段不常用，我这里只举一个例子：
查询每个学生超过自己选修课平均成绩的课程号，用派生表查询实现
SELECT Sno,Cno
FROM Sc,(SELECT Sno,AVG(Grade) FROM Sc GROUP BY Sno) AS AVG_Sc(Avg_Sno,Avg_Grade)
WHERE Sc.Sno = AVG_Sc.Avg_Sno
AND Sc.Grade >= AVG_Sc.Avg_Grade
--下面给派生表一个特写方便理解
(SELECT Sno, AVG(Grade) FROM Sc GROUP BY Sno)
AS AVG_Sc(Avg_Sno,Avg_Grade)
三、数据更新（数据维护）
1、插入数据
- 模板
INSERT
INTO<表名>[(<属性列1>,[<属性列2>...])]
VALUES(<常量1>[,<常量2>...]);
其中：
- 若是在INTO后指明了属性列表，则VALUES后要插入的常量就要按照指明的顺序和内容写。一一对应；
- 若是没有指明任何属性列，则VALUES后必须要严格对照该表原有属性顺序，且不可以有缺少。
- 举例
在Student表内插入一段数据：
INSERT
INTO Student (Sno,Sname,Ssex,Sage,Sdent,Smajor)
VALUES ('95001','RETRO','男','20','计算机学院','码农的养成');
INSERT
INTO Student (Sno,Sname,Ssex,Sage,Sdent,Smajor)
VALUES ('95002','RETRO2','男','20','计算机学院','码农的养成');
查询结果：
2、修改数据
- 模板
UPDATE Student
SET <列名> = <表达式>[, <列名> = <表达式>...]
[ WHERE<条件语句> ]
- 注意事项
- 不能直接删除带约束的字段
应该先删除字段上相关的约束- 不能直接更改字段名
应该先添加新的字段名，并且将数据重新插入，再删除旧字段。- 修改数据最重要的不是修改本身，而是修改的对象，修改很简单只要把SET为新值就行，但要修改哪些对象则需要考虑如何查询。
- 数据的修改是UPDATE 配合 SET 或者 DELETE，注意与表的结构修改 ALTER ADD DROP 作区分。
- 举例
将学号95002的学生名字改为‘frog’，Sdept改为‘计算机学院隔壁’
UPDATE Student
SET Sname = 'frog',
Sdept = '计算机学院的隔壁' --注意这里就不用加逗号，
--原则上逗号只是用来在相似语句间作分割
WHERE Sno='95002'
3、删除数据
- 模板
DELETE
FROM <表名>
[WHERE <条件语句>]
- 举例
DELETE
FROM Student
四、视图操作
- 视图概述：视图（VIEW）是一个表（TABLE）或者多个表（或视图）导出来的新表，但本质上是一个虚表，不是基本表，数据库内部不存放视图的数据，只存放其结构的定义，这就使数据库和用户间有了一个“窗口”，既保护了数据库，又方便了操作。
1、定义视图
- 模板
CREATE VIEW<视图名>[(<列名>[,<列名>])] --列名要么全都指定，要么全都隐含在SELECT中
AS <子查询>
[WITH CHECK OPTION] --加上后对视图更新数据时必须符合SELECT中条件
- 举例
例一：建立男学生的视图（Male_Student），属性包括学号、姓名、选修课程名和成绩，且要求修改和插入时人需保证该视图只有男学生
CREATE VIEW Male_Student
AS
SELECT Student.Sno,Student.Sname,Course.Cname,Sc.Grade
FROM Student,Sc,Course
WHERE Student.Sno=Sc.Sno AND
Sc.Cno=Course.Cno AND
Student.Ssex='男'
WITH CHECK OPTION;--加上后对视图更新数据时会自动加上 Student.Ssex='男' 的约束
作为视图，很多时候他的列属性不是单纯的从其他表中选出来的，而是由其他表计算推导出来的，一般会用到聚集函数如AVG、SUM。根据模板内注释，在这种情况下一定要指定视图的所有列名。接下来给出一个例子：
例二：将学生的学号及平均成绩定义为一个视图
CREATE VIEW S_G(Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM Sc
GROUP BY Sno
2、查询视图
与基本表的查询操作类似。
3、更新视图
- 与基本表的更新操作类似，但要特别注意不是所有视图都能更新。
- 由于视图是不实际存数据的，也就是说对视图的更新时根据对基本表的更新实现的，所以当我们做一些无法转化为基本表更新的操作时，就会出现不可更新的现象。-
- 比如上面例子中的平均成绩，我们无法根据视图中的平均成绩转化为每一科的成绩，所以S_G是不可更新的
4、删除视图
- 模板
DROP VIEW <视图名> [CASCADE];
--加上CASCADE后会连带其导出的所有视图一起删除
五、存储过程和函数
- 我们知道一般来看SQL是高度非过程化的语言，但SQL存储过程和函数的概念在SQL99标准被支持，在过程化的SQL中每一个程序都是由块构成的，每一个块完成逻辑操作。
- 过程化SQL块有命名块和匿名块之分，命名块在被编译后保存在数据库中，是持久性存储模块，可以反复快速调用。过程和函数就是命名块，这里主要介绍存储过程。
注：存储过程有很多内容，我学习的部分都是网上和书上七零八碎找的，只是为了实现某些功能，所以这里只是冰山一角。但是由于我并不是专业的所以这些只是作简单的学习仅当了解，如要系统学习还有很长很长的路要走。
- 模板
--创建过程
CREATE PROC[EDURE] 过程名 [<参数1> [,<参数>]...]
AS<过程化SQL块>
--更新过程
ALTER PROC[EDURE] 过程名 [<参数1> [,<参数>]...]
AS<过程化SQL块>
--调用过程
EXEC 过程名 [参数列表]
--删除过程
DROP PROC[EDURE] 过程名
- 局部变量赋值
SET @变量名=<表达式>
SELECT @变量名=<表达式>[列表达式组]
[FROM……]
- print输出
PRINT '<字符串>'+ CONVERT(<数据类型>[（长度）],<表达式>)
- 举例
忽视例子里的名字中英文区别
例一：设计并调用存储过程，输出学生选修总学分
--作查询
CREATE PROC pro1
AS
SELECT 学生.Sno 学生学号,SUM(课程.Ccredit) 总学分
FROM 学生,课程,选课
WHERE 学生.Sno=选课.Sno AND 选课.Cno=课程.Cno
GROUP BY 学生.Sno
EXEC pro1
--或者用输入学生号做print
--创建过程
CREATE PROC pro
@THESNO CHAR(20)
AS
DECLARE @SUMCREDIT SMALLINT
SELECT @SUMCREDIT = SUM(Course.Ccredit)
FROM Student,Sc,Course
WHERE Student.Sno = Sc.Sno AND Sc.Cno = Course.Cno AND Student.Sno=@THESNO
GROUP BY Student.Sno
PRINT 'CREDIT: '+ convert(varchar(10),@SUMCREDIT)
--调用过程
EXEC pro
@THESNO = '95001'
例二：设计并调用存储过程，输出课程选修总人数
--作查询
CREATE PROC pro2
AS
SELECT 课程.Cno 课程号,COUNT(*) 选修人数
FROM 学生,课程,选课
WHERE 学生.Sno=选课.Sno AND 选课.Cno=课程.Cno
GROUP BY 课程.Cno
EXEC pro2
--或者用输入课程号做print
--创建过程
CREATE PROC pro2
@THECNO CHAR(20)
AS
DECLARE @SUM INT
SELECT @SUM = COUNT(*)
FROM Student,Sc,Course
WHERE Student.Sno = Sc.Sno AND Sc.Cno = Course.Cno AND Sc.Cno=@THECNO
GROUP BY Course.Cno
PRINT 'SUM: '+ convert(varchar(10),@SUM)
--调用过程
EXEC pro2
@THECNO = '001'
例三：设计并调用存储过程，输出课程成绩平均分、最高分和最低分
--作查询
CREATE PROC pro3
AS
SELECT 课程.Cno 课程号,AVG(Grade) 成绩平均分 ,MAX(Grade)最高分,MIN(Grade) 最低分
FROM 学生,课程,选课
WHERE 学生.Sno=选课.Sno AND 选课.Cno=课程.Cno
GROUP BY 课程.Cno
EXEC pro3
--或者用输入课程号做print
--创建过程
CREATE PROC pro3
@THECNO CHAR(20)
AS
DECLARE
@MAX INT,
@MIN INT,
@AVG INT
SELECT @MAX = MAX(Sc.Grade),@MIN = MIN(Sc.Grade),@AVG = AVG(Sc.Grade)
FROM Student,Sc,Course
WHERE Student.Sno = Sc.Sno AND Sc.Cno = Course.Cno AND Sc.Cno=@THECNO
GROUP BY Course.Cno
PRINT 'MAX: '+ convert(varchar(10),@MAX)
PRINT 'MIN: '+ convert(varchar(10),@MIN)
PRINT 'AVG: '+ convert(varchar(10),@AVG)
--调用过程
EXEC pro3
@THECNO = '001'
最后：此处跳转到习题总练习
3084
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
