DBMS
1. 绪论
1.1. 数据库系统概述
1.1.1. 数据库的发展历史
1.1.2. 数据库技术的基本术语
1.1.3. 数据库管理技术的发展
1.2. 数据模型
1.2.1. 数据的3个范畴
1.2.2. 数据模型的组成要素及分类
1.2.3. 常用的数据模型
1.3. 数据库系统结构
1.3.1. 三级模式结构
1.3.2. 数据库系统的二级独立性
1.3.3. 数据库系统的二级映像
1.4. 数据库管理系统
1.4.1. DBMS的主要功能
1.4.2. DBMS的组成概述
1.4.3. DBMS的工作过程
2. 关系数据库
2.1. 关系数据模型
2.1.1. 关系数据模型概述
2.1.1.1. 关系数据结构
2.1.1.2. 关系操作集合
关系语言，是一种高度非过程化的语言，用户不必请求DBA为其建立特殊的存取路径，
存取路径的选择由 DBMS 的优化机制来完成
此外，用户不必求助于循环结构就可以完成数据操作
另外还有一种介于 关系代数 和 关系演算 之间的语言-SQL，
SQL 不仅具有丰富的查询功能，而且具有 数据定义 和 数据控制功能，是集查询、DDL、DML和 DCL于一体的关系数据语言。
它充分体现了关系数据语言的特点和有点，是 关系数据库 的 标准语言
2.1.1.3. 关系完整性约束
2.1.2. 基本术语
2.1.3. 关系的性质
2.2. 关系的完整性
2.3. 关系代数
2.3.1. 传统的集合运算
2.3.2. 专门的关系运算
2.3.3. 关系代数运算的应用实例
2.3.4. 关系代数的扩充操作
2.4. 关系演算
2.4.1. 元组关系演算
2.4.2. 域关系演算
2.4.3. 关系演算的安全约束和等价性
3. 结构化查询语言
3.1. SQL概述
3.1.1. SQL语言的发展
有关标准:
SQL/86
SQL/89
SQL/92
SQL/99
SQL2003
SQL2006
现状: 大部分DBMS产品都支持SQL, 称为数据库的标准语言, 是一个通用的, 功能极强的关系数据库语言
3.1.2. SQL语言的特点
1, 综合同一
集书库定义语言DDL, 数据操作语言DML 和 数据控制语言DCL于一体, 语言风格统一, 可以独立完成数据库声明周期中的全部活动
2, 高度非过程化
用户只需提出 “做什么”, 至于 “怎么做”, 由DBMS解决;
用户无需了解存取路径, 存取路径的选择以及SQL语句的操作过程由系统自动完成
数据定义(DDL)
- 定义, 删除, 修改关系模式
- 定义, 删除视图(view)
- 定义, 删除索引(index)
数据操纵(DML)
- 数据查询
- 数据增, 删, 改
数据控制(DCL)
- 用户访问权限的授予, 收回
3, 面向集合的操作方式
每一个SQL的操作对象是一个 或 多个关系, 操作的结果也是关系
4, 以同一种语法结构提供两种使用方式
即可独立使用, 又可嵌入到 高级语言中使用, 具有 自主型 和 嵌入型 两种特点, 且在两种使用方式下, SQL语言的语法结构基本一致
5, 语言简洁, 易学易用
核心功能只有9个动词, 语法简单, 接近英语
| SQL功能 | 操作符 |
|---|---|
| 数据查询 | select |
| 数据定义 | create, drop, alter |
| 数据操纵 | insert, update, delete |
| 数据控制 | grant, revoke |
数据定义语句
SQL的数据定义语句
| 创建 | 删除 | 修改 | |
| 表 | create table | drop table | alter table |
| 视图 | create view | drop view | |
| 索引 | create index | drop index |
注: SQL通常不提供 修改视图定义 和 索引定义 的操作
定义数据库
create database <数据库名>
[
on / log on
(
name = <数据库/日志文件名>,
filename = <数据库/日志文件的存储位置>,
size = <数据库/日志文件的初始大小>,
maxsize = <数据库/日志文件的最大值>,
filegrowth = <文件递增值>
)
]
3.1.3. SQL语言的基本概念
3.2. 数据定义语句
3.2.1. 基本表的定义
遵循规则
1, 列级完整性约束
2, 表级完整性约束
1, Salvaging_抢修工程计划表
create table Salvaging(
--列级完整性约束，prj_num是主码
prj_num char(8) primary key,
prj_name varchar(50),
start_date datetime,
end_date datetime,
prj_status bit,
--表级完整性约束
check(end_date >= start_date)
--系统自动给起名字
);
添加check约束
Alter table salvaging add constraint
可以在可视化软件中，右键单击，删除约束
2, Stock_配电物资库存记录表
create table Stock(
mat_num char(8) primary key,
mat_name varchar(50) not null,
speci varchar(50) not null,
warehouse varchar(50),
amount int check(amount > 0),
unit decimal(18, 2),
total as ([amount] * [unit])
--as为自动计算字段，不能输入值，表示：总金额=数量*单价
);
3、Out_stock_配电抢修物资领料出库表
create table Out_stock(
prj_num char(8),
mat_num char(8),
amount int,
get_date datetime,
department varchar(100),
primary key(prj_num, mat_num),
--主码由2个属性构成，必须作为表级完整性约束
foreign key (prj_num) references Salvaging(prj_num),
foreign key (mat_num) references Stock(mat_num)
);
属性是数字，也常用 定长字符，而不用数字
char 字符
varchar 可变长字符
3.2.2. 基本表的修改
[例3.4] 向抢修工程计划表 Salvaging 中增加 "工程项目负责人"列, 数据类型为 字符型
alter table Salvaging
add prj_director varchar(10);
注意: 无论基本表中原来是否已有数据, 新增加的列一律为空值
[例3.5] 删除抢修工程计划表 Salvaging 中 “项目负责人” 的属性列
alter table Salvaging
drop column prj_director;
[例3.6] 将配电物资领料出库表 Out_stock 中领取数量的数据类型由 字符型(假设原来的数据类型是字符型) 改为正数
alter table Out_stock
alter column amount int;
注意:
修改原有的列定义可能会破坏已有数据
3.2.3. 基本表的删除
[例3.7] 删除配电物资领料出库表 Out_stock
drop table Out_stock;
注意: 基本表的删除是有限制条件的, 要删除的基本表不能被其他表的约束(check, foreign key) 所引用,
如果存在这些依赖该表的对象, 则此表不能被删除
一旦删除基本表, 不仅表中的数据和此表的定义将被删除, 而且在该表上建立的索引, 视图, 触发器等有关对象也被删除,
因此用户执行删除基本表的操作一定要格外小心.
删除表中某个字段
alter table Student drop column stature;
3.3. 查询
3.3.1. 单表查询
单表查询, 指仅涉及一张表的查询
3.1.1.1. 选择表中的若干列
1) 查询指定的列
【例3.8】 查询所有配电物资的物资编号, 物资名称, 规格
select mat_num, mat_name, speci
from stock;
【例3.9】 查询所有配电物资的 物资名称, 物资编号, 规格 和 所在仓库名称
select mat_name, mat_num, speci, warehouse
from stock;
2) 查询全部列
【例3.10】 查询所有配电物资的记录
select *
from stock;
-- 等价于下面的内容
select mat_num, mat_name, speci, warehouse, amount, unit, total
from stock;
3) 经过计算的值
【例3.11】 查询所有抢修功能的抢修天数
select prj_name, start_date, end_date, datediff(day, start_date, end_date)
from salvaging;
SQL Server2005 提供时间函数, 可以对日期 和 时间输入值执行操作, 并返回一个字符串, 数字 或 日期 和 时间值
| 函数 | 功能 |
|---|---|
| getdate() | 返回系统当前的日期 和 函数 |
| year(date) | 返回一个整数, 表示指定日期中的年份 |
| month(date) | 返回一个整数, 表示指定日期中的月份 |
| day(date) | 返回一个整数, 表示指定日期中的天数 |
| datediff(datepart, date1, date2) | 返回date1 和 date2的时间间隔, 其单位由datepart参数指定 |
【例3.12】 查询所有抢修工程的抢修天数, 并在实际抢修天数列前加入一个列, 此列的每行数据均为 “抢修天数” 常量值
select prj_name, '抢修天数', datadiff(day, start_date, end_date)
from salvaging;
可以看到经过计算的列, 函数的列和常量列的显示结果都没有列标题, 用户可以通过指定别名来改变查询结果的列标题
用户可以通过指定 别名 来改变 查询结果的列标题,
语法格式为
select prj_name 项目名称, start_date 开始日期, end_date 结束日期, datediff(day, start_date, end_date) 抢修天数
from salvaging;
3.3.1.2. 选择表中的若干元组
- 消除取值相同的行
【例3.13】 在配电物资库存记录表中 查询出 所有的仓库名称, 并去掉结果表中的 重复行
--保留表中取值重复的行
select warehouse
from stock;
--删除表中取值重复的行
select distinct warehouse
from stock;
distinct关键字在select之后, 目标列表表达式之前.
如果没有指定distinct短语, 则默认为all, 即保留结果表中取值重复的行
- 查询满足条件的元组
查询满足条件的元组是通过 where子句实现的, where子句常用的查询条件如表所示
查询满足条件的元素(where)
| 查询条件 | 谓词 |
|---|---|
| 比较 | =,>,<,>=,<=,!=,<>,!>,!<, NOT+上述比较运算 |
| 确定范围 | BETWEEN AND, NOT BETWEEN AND |
| 确定集合 | IN, NOT IN |
| 字符匹配 | LIKE, NOT LIKE |
| 空值 | IS NULL, IS NOT NULL |
| 多重条件 | AND, OR, NOT |
(1) 比较大小的查询
【例3.14】 查询供电局1#仓库存放的所有物资编号, 物资名称, 规格 以及 数量
select mat_num, mat_name, speci, amount
from stock
where warehouse = '供电局1#仓库'
书上有关于, 数据量比较大时, 查询原理, 可能会在warehouse列上建立索引, 这样就不会全表扫描, 会提高查询速度
【例3.15】 查询所有单价小于 80的物资名称, 数量 及其 单价
select mat_name, amount, unit
from stock
where unit < 80;
--或者下面
select mat_name, amount, unit
from stock
where not unit >= 80;
确定范围(between and, not between and)
【例3.16】 查询单价在50-100之间的物资名称, 数量 及其单价
select mat_name, amount, unit
from stock
where unit between 50 and 100;
--等价于
select mat_name, amount, unit
from stock
where unit >= 50 and unit <= 100;
【例3.17】 查询单价不在 50-100之间的物资名称, 数量 及其 单价
select mat_name, amount, unit
from stock
where unit not between 50 and 100;
--等价于
select mat_name, amount, unit
from stock
where unit < 50 or unit > 100;
确定集合(in, not in)
【例3.18】 查询存放在 供电局1#仓库 和 供电局2#仓库 的物资名称, 规格 及其 数量
select mat_name, speci, amount
from stock
where warehouse in('供电局1#仓库', '供电局2#仓库');
--等价于
select mat_name, speci, amount
from stock
where warehouse = '供电局1#仓库' or warehouse = '供电局2#仓库'
【例3.19】 查询既没有存放在 供电局1#仓库, 也没有存放在 供电局2#仓库 的物资名称, 规格 及其数量
select mat_name, speci, amount
from stock
where warehouse not in('供电局1#仓库', '供电局2#仓库');
--等价于
select mat_name, speci, amount
from stock
where warehouse != '供电局1#仓库' and warehouse != '供电局2#仓库';
字符匹配(like, not like, 模糊查询)
找出 满足 给定匹配条件的字符串, 其格式为:
[NOT] LIKE ‘<匹配串>’[ESCAPE ‘<换码字符>’]
匹配规则：
“%” ：代表任意长度（0个或多个）字符串。
“_”：代表任意单个字符。
escape ：定义换码字符，以去掉特殊字符的特定含义，使其被作为普通字符看待。如escape ‘\’，定义了 \ 作为换码字符，则可用\%去匹配%，用\＿去匹配＿，用\ \去匹配 \ 。
【例3.20】 查询存放在 供电局1#仓库 的 物资的详细情况
select *
from stock
where warehouse like '供电局1#仓库';
--等价于
select *
from stock
where warehouse = '供电局1#仓库';
【例3.21】 查询所有绝缘电线的 物资编号, 名称 和 规格
select mat_num, mat_name, speci
from stock
where mat_name like '%绝缘电线';
【例3.22】 查询物资名称中 第三, 四个字 为 “绝缘” 的物资编号, 名称 和 规格
select mat_num, mat_name, speci
from stock
where mat_name like '_绝缘';
【例3.23】 查询所有不带 绝缘 两个字的物资编号, 名称 和 规格
select mat_num, mat_name, speci
from stock
where mat_name not like '%绝缘'
【例3.24】 查询物资名称为 ‘断路器_户外真空’ 物资信息
select *
from stock
where mat_name like '断路器\_户外真空' escape '\';
说明:
escape’’ 短语表示 “” 为 换码字符, 这样匹配串中 紧跟在 “” 后面的字符 “" 不再具有 通配符 的含义, 转义为 普通的 "” 字符
涉及控制的查询(is null, is not null)
判断 取值为空 的语句格式为: 列名 is null
判断 取值不为空 的语句格式为: 列名 is not null
【例3.25】 查询无库存单价的 物资编号 及其 名称
select mat_num, mat_name
from stock
where unit is null;
注意: 这里的 “is” 不能用 等号(=) 代替
多重条件查询(and, or)
【例3.26】 查询规格为 BVV-120 的 护套绝缘电线的 物资编号, 库存数量 及 库存地点
select mat_num, warehouse, amount
from stock
where mat_name = '护套绝缘电线' and speci = 'BVV-120'
对查询结果排序(order by <列名> [ASC | DESC]
【例3.27】 查询 “护套绝缘电线” 的物资编号 及其 单价, 查询结果 按单价 降序排列
select mat_name, unit
from stock
where mat_name = '护套绝缘电线'
order by unit desc;
【例3.28】 查询所有物资的信息, 查询结果按 所在仓库名 降序排列, 同一仓库的 物资 按库存量 升序排列
select *
from stock
order by warehouse;
备注
1, 默认为升序
2, 认为 空值 是 最小的
top子句的 用法
top n子句: 在查询结果中 输出 前面的n条记录;
top n percent子句: 在查询结果中输出 前面占结果记录总数的n%条记录
【例3.29】 显示stock表中, 库存量最大的两条记录
select top 2 *
from stock
order by amount desc;
【例3.30】 显示stock表中占总数20%的记录
select top 20 percent *
from stock;
注意: top子句 不能和 distinct关键字 同时使用
聚集函数
【例3.31】 统计领取了 物资的 抢修工程项目数
select count(disdinct prj_num)
from out_stock;
【例3.32】 查询使用m001号 物资的抢修工程的 最高领取数量, 最低领取数量 以及 平均领取数量
select max(amount), min(amount), avg(amount)
from out_stock
where mat_num = 'm001';
注意:
聚集函数中 除 count外, 其他函数在 计算过程中均 忽略null值;
where子句中 是 不能使用 聚集函数 作为 条件表达式的
查询结果分组(group by)
group by子句 可以将 查询结果表 按一列 或 多列取值相等的原则 进行分组
分组的目的:
为了细化集函数的 作用对象
如果 未对 查询结果分组, 集函数将 作用于 整个查询结果, 即 整个查询结果 只有 一个函数值.
如果用 group分了组, 集函数将 作用域 每一个组, 即 每一组 都有一个函数值
【例3.33】 查询每个抢修工程项目号 及 使用的 物资种类
select prj_name 项目号, count(*) 物资种类
from out_stock
group by prj_num;
having
可以针对集函数的结果值 进行筛选, 它是作用于 分组计算的 结果集
跟在 group by子句的后面, 有group by才有 having
【例3.34】 查询使用了 2种以上物资的 抢修工程项目号
select prj_num 项目号
from out_stock
group by prj_num having count(*) > 2;
注:
1, where作用于 基本表 或 视图, 从中选择 满足条件的元组;
2, having作用于组, 从中选择满足条件的组
compute by 子句
获得统计数据 及 相应统计的明细数据
语法格式:
[ COMPUTE
{ AVG | COUNT | MAX | MIN | SUM }
( 表达式) } [ ,...n ]
[ BY 表达式 [ ,...n ] ]
]
其中:
avg | count | max | min | sum表示可以使用的 聚集函数
表达式 表示 计算的列名, 必须出现在 选择列表中.
by表达式 表示 在 结果集中 生成 控制中断 和 小计
SQL Server也支持带rollup 以及 cube关键字的分类汇总 这种用法在数据统计中经常用到, 尤其是在制作报表时.
其中, rollup 按照分组顺序, 先对第一个分组字段分组, 在组内进行统计, 最后给出合计;
cube 则会对所有的分组字段进行统计, 最后给出总的合计.
二者的区别就是 rollup 不会统计 group by子句的最后一个分组字段的小计
【例3.38】 统计存放于 供电局2#仓库的 所有物资的总价值
select mat_name, speci, amount, unit, total
from stock
where warehouse = '供电局2#仓库'
compute sum(total);
【例3.36】 统计存放于 各个仓库的 物资总价值, 并查询 物资名称, 规格, 单价, 数量等, 按 仓库分组显示 查询结果
select mat_name, speci, unit, amount, total, warehouse
from stock
--按什么分组, select后面必须有 对应要分组的属性的名字
order by warehouse
compute sum(total) by warehouse;
练习: 4个表
–
–学生学号, 学生姓名, 学生性别,
Student(Sno , Sname , Ssex , Sage , Sclass)
–
Teacher(Tno,Tname,Tsex,Tage, Tprof, Tdept)
–
Course(Cno , Cname ,Tno)
–选修课程表
–(学号, 课程号), 共同构成 主码, 单个 叫做 外码
SC(Sno , Cno , Grade)
1, 查询所有学生的姓名, 性别 和 班级
select Sname, Ssex, Sclass
from Student;
2, 查询该学校所有的系名
select distinct Tdept
from Teacher;
3, 查询考试成绩在60到80之间的 学生记录
select *
from SC
where Grade between 60 and 80;
4, 查询成绩为80, 85, 90的学生记录, 并按学号升序, 课号降序排列
select *
from SC
where Grade in(80, 85, 90)
order by Sno, Cno desc;
5, 统计95031班全体学生人数
select count(*)
from Student
where Sclass = '95031';
6, 查询 "3-105"号课程的平均分, 并赋予别名
select avg(Grade)
from SC
where Cno = '3-105';
7, 查询至少有2名学生选修的, 并以3开头的课程号 及 平均分数
select Cno, avg(Grade)
from SC
where Cno like '3%'
group by Cno having count(*) >= 2;
8, 显示最低分大于 70, 最高分小于 90的课程号
select Cno
from SC
group by Cno having min(Grade) > 70 and max(Grade) < 90;
3.2.2. 连接查询
【例3.40】- 【例3.43】
在查询中, 同时涉及两个或两个以上的表, 要根据表中数据的情况作连接查询
连接两个表的条件称为 连接条件 或 连接谓词
连接两个表的条件 称为 连接条件 或 连接谓词
一般格式为:
[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>
--或
[<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2>
AND [<表名2>.]<列名3>
其中比较运算符主要有：=、>、<、>=、<=、!=（或<>）。
连接操作 的 执行过程
1, 首先在表1中找到 第一个元组, 从头开始扫描表2, 逐一查找满足 连接条件的元组, 找到后 将表1中的 第一个元组 与 该元组 拼接起来, 形成结果表中 一个元组
2, 表2全部查找完后, 再找表1中的第二个元组, 然后 再从头开始扫描 表2, 逐一查找满足连接条件的元组, 找到后就将表1中的第二个元组 与 该元组拼接起来, 形成结果表中一个元组
3, 重复上述操作, 直到表1中的全部元组都处理完毕
1, 等值 与 非等值连接查询
连接运算符为=, 则为 等值连接, 其他为 非等值连接
连接条件中的 连接字段 必须可比, 但不必相同
【例3.38】对例3.37用自然连接完成
select salvaging.prj_num, prj_name, start_date, end_date, prj_status, mat_num, amount, get_date, department
from salvaging, out_stock
where salvaging.prj_num = out_stock.prj_num;
注: 任何子句中引用 表1 和 表2中同名属性时, 都必须加表前缀;
若属性名 在 参加连接的表中是 唯一的, 则可以省略表名前缀
2, 外连接
外连接: 把舍弃的元组 也 保存在结果关系中, 而在 其他属性上填空值
外连接 与 普通连接 的区别
1, 普通连接操作 只输出满足 连接条件的 元组
2, 外连接操作 以指定某表为 连接主体, 将主体表中不满足 连接条件的元组, 一并输出
工作原理
指定非主体表;
非主体表有一 “万能” 的虚行, 该行全部由 空值组成
虚行 可以和主体表中 所有不满足 连接条件的元组, 进行连接
由于虚行各列全部是 空值, 因此与虚行连接的结果中, 来自非主体标的属性值 全部是 空值
分类
1, 左外连接: left outer on, 列出左边关系中 所有元组
2, 右外连接: right outer on, 列出右边关系中 所有元组
3, 全外连接: full outer on, 列出 左边关系 和 右边关系 中 所有元组
3, 复合条件连接
where子句中 包含多个 连接条件
3.3.2.1. 等值与非等值连接查询
【例3.40】查询每个抢修工程及其领料出库的情况
--第1个连接查询
select Salvaging.*, Out_stock.*
from Salvaging, Out_stock
where Salvaging.prj_num = Out_stock.prj_num;
【例3.41】对例3.40用自然连接完成
这个, 没有看出来, 和上面那个, 有什么区别啊,
什么事自然连接啊
select Salvaging.prj_num, prj_name, start_date, end_date, prj_status, mat_num, amount, get_date, department
from Salvaging, Out_stock
where Salvaging.prj_num = Out_stock.prj_num;
在本例中, 由于prj_name, start_date, end_date, prj_status, mat_num, amount, get_date和department属性列在Salvaging表与Out_stock表中是唯一的,
因此引用时可以去掉表名前缀; 而prj_num在两个表中都出现了, 因此引用时必须加上表名前缀
3.3.2.2. 外连接查询
左外连接, 右外连接, 全外连接
1, 左外连接, LEFT OUTER JOIN, 其结果是列出左边关系中所有的元组, 而不仅仅是列出连接属性所匹配的元组
2, 右外连接, RIGHT OUTER JOIN, 其结果是列出右边关系中所有的元组
3, 全外连接, FULL OUTER JOIN, 其结果是列出左边关系和右边关系中的所有元组
【例3.42】把例3.41中的等值连接改为左外连接
select Salvaging.prj_num, prj_name, start_date, end_date, prj_status, mat_num, amount, get_date, department
from Salvaging LEFT OUTER JOIN Out_stock on(Salvaging.prj_num = Out_stock.prj_num);
外连接就好像是为符号*所在边的表(本例是 Out_stock表)增加一个 “万能” 的行, 这个行全部由空值组成
它可以和另一边的表(本例中是Salvaging表)中不满足连接条件的元组进行连接.
由于这个"万能"行的各列全部是空值, 因此在本例的连接结果中有一行来自 Out_stock表的属性值全部是空值
3.3.2.3. 复合条件连接查询
在上面的各个连接查询中, where子句中只有一个条件, 即连接谓词,
where子句中可以有多个连接条件, 称为复合条件连接
【例3.43】查询项目号为 "20100015"抢修项目 所使用的 物资编号, 物资名称, 规格 和 使用数量
-- where子句中可以有多个连接条件, 称为复合条件连接
select out_stock.mat_num, mat_name, speci, out_stock.amount
from stock, out_stock
where stock.mat_num = out_stock.mat_num and prj_num = '20100015';
【例3.44】查询使用了护套绝缘电线的 所有抢修项目编号 及 名称
该查询涉及3个表
select out_stock.prj_num, prj_name
from stock, out_stock, salvaging
where stock.mat_num = out_stock.mat_num
and salvaging.prj_num = out_stock.prj_num
and mat_name = '护套绝缘电线';
3.3.2.4. 自身连接查询
连接操作不仅可以在两个表之间进行, 同一个表也可以与自己进行连接, 这种连接称为表的自身连接
连接操作是 一个表 与其自己进行的
必须给表起别名以示区别, 且由于所有属性 都是 同名属性, 则必须加前缀
【例3.45】查询同时使用了 物资编号为m001 和 m002的 抢修工程的 工程号 与工程名
select A.prj_num
-- 给2个Out_stock表起别名 A, B
from out_stock A, out_stock B
-- where子句中, 多个and, 叫做复合条件查询
where A.prj_num = B.prj_num and A.mat_um = 'm001' and B.mat_num = 'm002';
有时间的话, 看看这个题目, 还有其他的方式吗
3.3.3. 嵌套查询
在SQL语言中, 一个select…from…where语句称为一个查询块
将一个查询块嵌套在另一个查询块的where子句或having短语的条件中的查询称为 嵌套查询
select prj_name /*外层查询或父查询*/
from Salvaging
where prj_num in( /*内层查询或子查询*/
select prj_num
from out_stock
where mat_num = 'm003'
);
上层查询块称为外层查询或父查询
下层查询块称为内存查询或子查询
SQL语言允许多层嵌套查询, 即一个子查询中还可以嵌套其他子查询
子查询的限制
需要特别指出的是, 子查询的select语句不能使用 order by子句, order by子句只能对最终查询结果排序
嵌套查询使用户可以用多个简单查询构成复杂的查询, 从而增强SQL的查询能力
以层层嵌套的方式构造程序是SQL(Structured Query Language)中 “结构化” 的含义所在
有些嵌套查询 可以用 连接运算替代
执行过程: 无论单层嵌套 还是 多层嵌套, 执行过程都是 由里向外
3.3.3.1. 带谓词in的嵌套查询
子查询的结果 是一个集合
【例3.46】查询与规格为 “BVV-120” 的 “护套绝缘电线” 在同一个仓库 存放的 物资名称, 规格 和 数量
也不要总按照书上的步骤去做, 要自己分析
以后, 拿到一个题, 先考虑使用什么方法, 先把使用方法写出来
先实现出来, 再考虑下效率问题
查询与规格为 “BVV-120” 的 “护套绝缘电线” 在同一个仓库 存放的 物资名称, 规格 和 数量
首先查询到 规格为"BVV-120"所在的仓库号
然后查询在这个仓库中的 物资名称, 规格, 数量
分析出一个基本的问题: 嵌套查询
嵌套查询包含(外层查询 和 内层查询)
先分别把外层查询 和 内层查询查询出来, 再合起来
外层查询
select mat_name, speci, amount
from stock;
内层查询
select warehouse
from stock
where mat_name = '护套绝缘电线' and speci = 'BVV-120';
合起来, 试一下, OK
select mat_name, speci, amount
from stock
where warehouse in(
select warehouse
from stock
where mat_name = '护套绝缘电线' and speci = 'BVV-120'
);
本例中的查询, 也可以用 自身连接 来完成
自己完成, 试一下
题目为: 查询与规格为 “BVV-120” 的 “护套绝缘电线” 在同一个仓库 存放的 物资名称, 规格 和 数量
select warehouse
from stock
where speci = 'BVV-120' and mat_name = '护套绝缘电线';
上面这个, 只能查询出 供电局1#仓库
但实现不了, 查询在这个仓库中的其他物资的 物资名称, 规格, 数量
实现这个需求, 还需要再写一些SQL语句
select mat_name, speci, amount
from stock
where warehouse = '供电局1#仓库';
select A.mat_name, A.speci, A.amount
from stock A, stock B
where A.mat_num = B.mat_num;
【例3.47】查询工程项目为 “观澜站光缆抢修” 抢修所使用的 物资编号 和 名称
3层嵌套
select mat_num, mat_name
from stock
where mat_num in(
select mat_num
from stock
where prj_num in(
select prj_num
from salvaging
where prj_name = '观澜站光缆抢修';
);
);
也可用 连接查询 实现
select stock.mat_num mat_name
from stock, out_stock, salvaging
where stock.mat_num = out_stock.mat_num
and out_stock.prj_num = salvaging.prj_num
and prj_name = '观澜站光缆抢修';
不相关子查询
子查询的条件 不依赖于 父查询
求解方法: 由里向外 逐层处理
即每个子查询在 上一级 查询处理之前求解, 子查询的结果 用于 建立其父查询的查找条件
3.3.3.2. 带有比较运算符的子查询
确定 子查询返回的是 单值, 则 子查询 与 父查询 之间可用 比较运算符连接
【例3.45】查询出库存量 超过 该仓库物资平均库存量的 物资编号, 名称, 规格 及 数量
select mat_num, mat_name, speci, amount
from stock s1
where amount > (
select avg(amount)
from stock s2
where s2.warehouse = s1.warehouse;
);
相关子查询
- 子查询的 查询条件 依赖于 父查询
求解方法
1, 首先取外层查询中表的 第一个元组, 根据它与内层查询相关的属性值 处理 内层查询,
若 where子句返回值为真, 则取此元组放入 结果表
2, 然后再取 外层表的 下一个元组
3, 重复这一过程, 直至 外层表 全部检查完为止
3.3.3.3. 带有any 或 all谓词的子查询
- 必须同时使用 比较运算符
| >any | 大于子查询结果中某个值 | >all | 大于子查询结果中所有值 |
|---|---|---|---|
| <any | 小于 | <all | 小于 |
| >=any | 小于 | >=all | 大于等于 |
| <=any | 大于等于 | <=all | 小于等于 |
| =any | 小于等于 | =all | 等于 |
| !any <>any | 不等于 | !=all <>all | 不等于 |
3.3.3.4. 带有EXISTS谓词的子查询
3.3.4. 集合查询
3.3.5. 查询数据
select max(amount) 最大值, min(amount) 最小值, avg(amount) 平均值
from Out_Stock
where mat_no = 'm001'
--where子句中，不能使用聚集函数
select prj_no 项目编号, count(*) 使用的材料种类数
--count(*)，也可以写成count(mat_no)
from Out_Stock
group by prj_no
--按项目号分组，一定要写项目号
--每种材料使用的项目有几个
select mat_no 材料编号, count(*) 该种材料使用到的项目数目
from Out_Stock
--按项目编号分组
group by mat_no
【例3.34】 查询使用了2种以上物资的抢修工程项目号。
--按工程项目编号分组
select prj_no 项目编号, count(*) 使用的材料种类数
--count(*)，也可以写成count(mat_no)
from Out_Stock
group by prj_no
having count(*)>2
--按项目号分组，一定要写项目号
--主提取条件，先分组，先分组的基础上，。。。
【例3.35】统计存放于供电局2#仓库的所有物资的总价值。
select mat_name, speci, amount, unit, total
from Stock
where warehouse = '供电局2#仓库'
compute sum(total)
--总计与小计
--计算每一个仓库的物资总价值
select warehouse, mat_name, speci, amount, unit, total
from Stock
--按仓库分组
order by warehouse
compute sum(total) by warehouse
【例3.8】查询所有配电物资的物资编号、物资名称、规格。
SELECT mat_no, mat_name, speci
FROM Stock;
【例3.9】查询所有配电物资的物资名称、物资编号、规格和所在仓库名称**
select mat_name, mat_no, speci, warehouse
from Stock;
【例3.10】查询所有配电物资的记录。
select *
from Stock;
【例3.11】查询所有抢修工程的抢修天数。
select prj_name, start_date, end_date, datediff(day, start_date, end_date)
from Salvaging;
【例3.12】查询所有抢修工程的抢修天数，并在实际抢修天数列前加入一个列，此列的每行数据均为抢修天数常量值。
SELECT prj_name,'抢修天数',datediff(day, start_date,end_date )
FROM salvaging;
--常量表达式，
**用户可以通过指定别名来改变查询结果的列标题，语法格式为：列名|表达式 **[AS]列标题
**或：列标题=列名|**表达式
SELECT prj_name 项目名称, start_date 开始日期, end_date 结束日期, datediff(day, start_date,end_date ) 抢修天数
FROM salvaging;
--重命名，用新的标题，替换原来的列标题，as可以有，可以没有
select distinct warehouse
from Stock;
导入sql脚本的时候，需要在可视化软件中，单击右键，先创建 sampleDB 数据库
打开脚本，全选内容，复制
选中 sampleDB , 新建查询， 粘贴复制内容， 执行
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-29gVTbBO-1632438748641)(DBMS.assets/image-20210408142035664.png)]
BETWEEN AND 闭区间
select mat_name, amount, unit
from stock
where unit not between 50 and 100;
--不在50-100
sql语句，不区分大小写not , NOT
select mat_name, speci, amount
from Stock
where warehouse in('供电局1#仓库','供电局2#仓库');
等价于
select mat_name, unit
from Stock
where mat_name = '护套绝缘电线'
order by unit
--order by后面可以添加，多个关键字，相同成绩，按学号的升序或降序
3.3.6. 学生信息例子练习
练习用的数据
–
–学生学号, 学生姓名, 学生性别,
Student(Sno , Sname , Ssex , Sage , Sclass)
–
Teacher(Tno,Tname,Tsex,Tage, Tprof, Tdept)
–
Course(Cno , Cname ,Tno)
–选修课程表
–(学号, 课程号), 共同构成 主码, 单个 叫做 外码
SC(Sno , Cno , Grade)
create database school;
create table Student(
Sno primary key, /*学生学号*/
Sname char(8), /*学生姓名*/
Ssex char(2), /*学生性别*/
Sage int, /*学生年龄*/
Sclass /*学生班级*/
);
create table Teacher(
Tno primary key, /*教师编号*/
Tname char(8), /*教师姓名*/
Tsex char(2), /*教师性别*/
Tage int, /*教师年龄*/
Tprof cha(8), /*教师职称*/
Tdept /*教师所属系名*/
);
create table Course(
Cno primary key, /*课程编号*/
Cname, /*课程名字*/
Tno /*教师编号*/
);
create table SC(
Sno, /*学生学号*/
Cno, /*课程编号*/
Grade /*成绩*/
);
Create table sc(
sno char(8) foreign key references student(sno) on update cascade
on delete cascade,
cno char(8) foreign key references sc(cno) on update cascade,
grade int check(grade>=0 and grade<=100)
Create table sc(
--添加完 级联删除的时候, 就不需要先删除 sc中的信息, 才能删除 student中的学生信息
sno char(8) foreign key references student(sno) on update cascade
on delete cascade,
cno char(8) foreign key references sc(cno) on update cascade,
grade int check(grade>=0 and grade<=100)
);
数据库 的 数据 是关联的
主表中的数据, 不能随便删除成功
级联更新, 级联删除
1.将姓名为王娜，女，20岁，学号为200，班级为96100的学生记录加入Student表中。
insert
--如果下面这行代码, 不写Student后面的内容, 默认 按创建的表的 属性顺序
--年龄 int
into Student(prj_num, prj_name, start_date, end_date, prj_status)
values();
2.把 平均成绩大于80 的 所有男学生 的 学号 和 平均成绩 存入 另一个基本表M_Grade(Sno,Avg_Grade) 中
1, 先创建表
create table M_Grade(
Sno char(10) primary key,
Avg_Grade smallint
);
2, 插入数据, 再进行 2张表 的连接
核心: 查询语句
insert
into M_Grade(Sno,Avg_Grade)
(
-- 对男同学 的 成绩, 求平均值
select SC.Sno, avg(Grade)
--先写数据源, 连接2张表, 联成一张 大表
FROM Student, SC
--where语句中, 不可以跟 具体函数
WHERE Student.Sno = SC.Sno
--把性别='男' 的元组, 挑选出来
and Ssex='男'
--分组
group by SC.Sno having avg(Grade)>80;
);
2张表, 最基本, 连接
3.将选修”3-105”课程的学生成绩增加5分.
--set, 是赋值语句
update SC
set Grade = Grade + 5
where ='3-105'
3.删除 选修“3-105”课程 中成绩 低于 该门课程平均成绩的选课记录.
1, 先求 平均成绩
select avg(Grade)
from SC
where SC.Cno = '3-105'
2, 删除
delete
from SC
where Cno = '3-105' and grade < (
--核心, select语句
select avg(Grade)
from SC
where SC.Cno = '3-105'
);
5, 删除计算机导论这门课程以及对应的选课记录。
1, 先删 sc表中的 引用的表
delete
from SC
where Cno = (
select Cno
from Course
where Cname= '计算机导论'
);
2, 然后再 Course表中, 删除 这门课程
delete
from Course
where Cname= '计算机导论';
主表中的数据, 不能随便删除
另一
练习: 4个表
Student(Sno , Sname , Ssex , Sage , Sclass)
Teacher(Tno,Tname,Tsex,Tage, Tprof, Tdept)
Course(Cno , Cname ,Tno)
SC(Sno , Cno , Grade)
1, 查询所有选课学生的 姓名, 课程号 和 成绩
select Sname, Cno, Grade
from Student, SC
where Student.Sno = SC.Sno;
2, 查询95031班同学 所选 各门课程的 课号 及 平均分
select Cno, avg(Grade)
from SC, Student
where Sclass = '95031' and Student.Sno = SC.Sno
group by Cno;
3, 查询选修 “3-105” 号 课程的 成绩高于 "109"号 同学 的 所有同学记录
select X.Sno, X.Cno, X.Grade
from SC X, SC Y
where X.Cno = '3-105' and X.Grade > Y.Grade and Y.Sno = '109' and Y.Cno = '3-105';
3.4. 数据更新
3.4.1. 插入数据
1, 向 Salavging_抢修工程计划表, 添加数据
insert
into salvaging(prj_num, prj_name, start_date, end_date, prj_status)
values
('20100015', '220kV清经线接地箱及接电线被盗抢修', '2010-10-12', '2010-10-13',1),
('20100016', '沙河站2#公变出线电缆老化烧毁抢修', '2010-11-05', '2010-11-05',1),
('20100001', '西丽站电缆短路烧毁抢修工程', '2011-01-03', '2011-01-03',1),
('20100002', '西丽站电缆接地抢修', '2011-01-03', '2011-11-05',1),
('20100003', '观澜站光缆抢修', '2011-02-10', '2011-02-11',1),
('20100004', '小径墩低压线被盗抢修', '2011-02-15', '2011-02-15',1),
('20100005', '明珠立交电缆沟盖板破损抢修', '2011-03-02', '2011-03-05',0),
('20100010', '朝阳围公变低压线被盗抢修', '2011-03-08', '2011-03-10',0);
2, 向stock_配电物资库存记录表, 添加数据
insert
into Stock(mat_num, mat_name, speci, warehouse, amount, unit)
values
('m001', '护套绝缘电线', 'BVV-120', '供电局1#仓库', 220, 89.80),
('m002', '架空绝缘导线', '10kV-150', '供电局1#仓库', 30, 17.00),
('m003', '护套绝缘电线', 'BVV-35', '供电局2#仓库', 80, 22.80),
('m004', '护套绝缘电线', 'BVV-50', '供电局2#仓库', 283, 32.00),
('m005', '护套绝缘电线', 'BVV-70', '供电局2#仓库', 130, 40.00),
('m006', '护套绝缘电线', 'BVV-150', '供电局3#仓库', 46, null),
('m007', '架空绝缘导线', '10kV-120', '供电局3#仓库', 85, 14.08),
('m009', '护套绝缘电线', 'BVV-16', '供电局3#仓库', 90, null),
('m011', '护套绝缘电线', 'BVV-95', '供电局3#仓库', 164, null),
('m012', '交联聚乙烯绝缘电缆', 'YJV22-15KV', '供电局4#仓库', 45, 719.80),
('m013', '户外真空断路器', 'ZW12-12', '供电局4#仓库', 1, 13600.00);
3, 向out_stock_配电抢修物资领料出库表, 添加数据
insert
into Out_stock(prj_num, mat_num, amount, get_date, department)
values
('20100015', 'm001', 2, '2010-10-12', '工程1部'),
('20100015', 'm002', 1, '2010-10-12', '工程1部'),
('20100016', 'm001', 3, '2010-11-05', '工程1部'),
('20100016', 'm003', 10, '2010-11-05', '工程1部'),
('20100001', 'm001', 2, '2011-01-03', '工程2部'),
('20100002', 'm001', 1, '2011-01-03', '工程2部'),
('20100002', 'm013', 1, '2011-01-03', '工程2部'),
('20100003', 'm001', 5, '2011-02-11', '工程1部'),
('20100003', 'm012', 1, '2011-02-11', '工程1部'),
('20100004', 'm001', 3, '2011-02-15', '工程1部'),
('20100004', 'm004', 20, '2011-02-15', '工程1部'),
('20100005', 'm001', 2, '2011-03-02', '工程1部'),
('20100005', 'm003', 10, '2010-10-12', '工程2部'),
('20100005', 'm006', 3, '2010-10-12', '工程2部'),
('20100010', 'm001', 5, '2010-10-12', '工程1部');
3.4.2. 修改数据
删除表中记录
--将1#仓库所有物资的领取数量设置为0
--前面2个,是带子查询的连接
--法3, 表的连接
-- update out_stock, stock
-- set out_stock.amount = 0
-- where stock
--删除项目号为 ""的抢修工程领取的编号
delete
from out_stock
where prj_num = '20110001'
3.4.3. 删除数据
1, 先选择到 要删除的 表 所在的 数据库
use Test;
2, 然后, 先删除管理所的表, 才能再删除 基本表
drop table Salvaging;
3.5. 视图
视图的特点:
虚表, 是从一个或几个基本表(或视图)导出的表:
值存放视图定义; 不存放数据, 因此不会出现数据冗余;
基表中的数据一旦发生变化, 从视图中查询出的数据也随之改变:
用户可通过视图这样的窗口, 看到数据库中感兴趣的数据
基于视图的操作
- 查询
- 删除
- 受限更新
- 定义基于该视图的新视图
定义视图
create view <视图名>[(<列名>[,<列名>]…)] as<子查询>[with check option];
注: 视图是用一个查询块的结果定义的, 但子查询中通常不允许含有 order by子句 和 distinct短语
存储模式
模式,
删除, 指的是 单个基本表 生成的 视图 (行列子集视图)
受限更新
全部省略:
由子查询中select目标列中的各个字段组成
全部指定：以下情况必须明确指定视图的所有列名:
(1) 某个目标列是集函数或列表达式；
(2) 多表连接时选出了几个同名列作为视图的属性名；
(3) 需要在视图中为某个列启用新的更合适的名字。
码 == 主键
3.5.0.1. 建立在单个基本表上的视图
行列子集视图：
从单个基本表导出的，只是去掉了基本表的某些行和某些列，但保留了码的视图。
【例3.67】建立供电局1#仓库所存放物资的视图。
create view s1_stockasselect mat_num, mat_name, speci, amount, unitfrom stockwhere warehouse = '供电局1#仓库';
1, 列出供电局1号仓库的全部信息
create view s1_stockasselect mat_num, mat_name, speci, amount, unitfrom stockwhere warehouse = '供电局1#仓库';
注: DBMS执行 create view语句时, 只是把视图的定义存入数据字典, 并不执行其中的select语句.
只是在对视图进行查询时, 才按视图的定义从基本表中将数据查出
【例3.68】建立供电局1#仓库所存放物资的视图，
并要求进行 修改 和 插入操作时 仍需保证该视图 只有 供电局1#仓库所存放的物资。
create view s2_stockasselect mat_num, mat_name, speci, amount, unitfrom stockwhere warehouse = '供电局1#仓库' --下面这句, 保证 满足where语句 WITH CHECK OPTION;
注意: with check option 表示通过视图进行增, 删, 改操作时, 不得破坏视图定义中的谓词条件(即子查询中的条件表达式)
3.5.0.2. 建立在多个基本表上的视图
对于多个基本表生成的视图, 除了查询 操作, 其他删除, 修改 操作, 不可以
【例3.69】 建立由抢修工程项目名称（prj_name）、出库物资名称（mat_name）、规格（speci）及领取数量（amount）的视图。
1, 先写一个 单表连接
3张表的连接
select prj_name, mat_name, speci, amountfrom salvaging, stock, out_stock;
连接有2种方式
1, from, 要求不严格
2, join, 有顺序
2, 创建视图
create vies s2_stockasselect prj_name, mat_name, speci, amountfrom salvaging, stock, out_stock;
直接写
create view s1_stockasselect prj_name, mat_name, speci, out_stock.amountfrom stock, salvaging, out_stockwhere out_stock.prj_num = salvaging.prj_num and out_stock.mat_num = stock.mat_num;
上面这个题, 就是要考察3个表连接的
prj_name, 来源于salvaging
mat_name, 来源于stock
amount, 来源于out_stock
3.5.0.3. 基于视图的视图
[例3.70] 建立供电局1#仓库所存放物资库存数量不少于50的视图
这个题目, 基于[例3.68]之前已经创建的视图s1_stock
create view s3_stockasselect mat_num, mat_name, speci, warehouse, amountfrom s1_stockwhere amount >= 50;
3.5.0.4. 带表达式的视图
带虚拟列 (基本表上并不实际存在的列) 的视图
[例3.71] 建立一个体现抢修工程项目实际抢修天数的视图
-- 体会这里使用了前面讲的, 指定了视图要显示的字段, 而不是默认使用子查询的字段create view s1_salvaging(prj_name, start_date, end_date, days)asselect prj_name, start_date, end_date, datediff(day, start_date, end_date)from salvaging;
注意: 本例中由于select子句的目标列中含有表达式, 因此必须在create view视图名后面 明确说明视图的各个属性列名
3.5.0.5. 建立分组视图
用带集函数 和 group by 子句的查询来定义的视图
[例3.72] 将仓库名称与其仓库内所存放物资的种类(数目)定义为一个视图
create view s4_stock(warehouse, counts)asselect warehouse, count(mat_num)from stockgroup by warehouse;
查看视图定义的文本信息的存储过程
sp_helptext s4_stock-- 等价于, 下面的select textfrom sysobjects s1, syscomments s2where name = 's4_stock' and type = 'V' and s1.id = s2.id;
视图加密
create view s4_stock(warehouse, counts)with encryption --加密asselect warehouse, count(mat_num)from stockgroup by warehouse;-- 执行下面这条语句sp_helptext s4_stock-- 会显示-- "对象s4_stock的文本已加密", 看不到 s4_stock的定义
3.5.0.6. 一类不易扩充的视图
以 select * 方式创建的视图可扩充性差, 应尽可能避免
[例3.73] 将所有已按期完成的抢修工程定义为一个视图
create view s4_salvaging(prj_num, prj_name, start_date, end_date, prj_status)asselect *from salvagingwhere prj_status = 1;
3.5.0.7. 删除视图
drop view <视图名>;
注:
1, 该语句从数据字典中删除指定的视图定义
2, 由该视图导出的其他视图定义仍在数据字典中, 但已不能使用, 必须显式删除.
3, 删除基表时, 由该基表导出的所有视图定义均已不能使用, 都必须显式删除
[例3.74] 删除视图s1_stock
drop view s1_stock;drop view s3_stock;
注:
s1_stock 和 s3_stock必须同时删除, 否则虽然s3_stock的定义仍在数据字典中, 但已无法正常使用
3.5.0.8. 查询视图
从用户角度:
查询视图与查询基本表相同
注:
1, (一般情况下), 视图可以像基本表那样使用.
2, 视图名可以出现在关系名可以出现的地方
3, DBMS将视图转换成对基本表的操作
DBMS实现视图查询的方法:
视图消解法(View Resolution)
1, 进行有效性检查, 检查查询的基本表, 视图是否存在.
2, 如果存在, 从数据字典中取出视图的定义, 把视图定义中的子查询与用户的查询结合起来, 转成等价的对基本表的查询
3, 执行修正后的查询
[例3.75] 在供电局1#仓库的物资视图s1_stock中找出单价小于20的物资名称, 规格 和 单价
select mat_name, speci, unitfrom s1_stockwhere unit < 20;-- 等价于下面的操作(不等价, 自己写错了)select mat_name, speci, unitfrom ( select mat_name, speci, unit from stock where warehouse = '供电局1#仓库';)where unit < 20;
转换后的查询语句为(正确):
select mat_name, speci, unitfrom stockwhere warehouse = '供电局1#仓库' and unit < 20;
[例3.76] 查询使用了供电局1#仓库 物资(视图)的 抢修工程项目号
自己的错误操作
select prj_numfrom s1_out_stock, stockwhere s1_out_stock.mat_num = stock.mat_num;-- 等价于下面的select prj_numfrom s1_out_stock, stockwhere warehouse = '供电局1#仓库' and s1_out_stock.mat_num = stock.mat_num;
正确的操作
select distinct prj_numfrom s1_stock, out_stockwhere s1_stock.mat_num = out_stock.mat_num;
转换后的查询语句为:
select distinct prj_numfrom stock, out_stockwhere warehouse = '供电局1#仓库' and stock.mat_num = out_stock.mat_num;
视图消解法的局限:
有些情况下, 视图消解法不能转换成正确查询, 则查询时会出现问题
[例3.77] 查询所存物资种类大于2的仓库名称
select warehousefrom s4_stockwhere counts > 2;-- 转换后, 如下select warehousefrom stockgroup by warehouse having count(mat_num) > 2;
用户角度: 更新视图与更新基本表相同
DBMS实现视图更新的方法:
视图消解法(View Resolution)
指定 with check option子句后
DBMS在更新视图时会进行检查, 防止用户通过视图对不属于视图范围内的基本表数据进行更新
[例3.78] 将供电局1#仓库的物资视图s1_stock中编号为m001的物资库存量改为100
update s1_stockset amount = 100where mat_num = 'm001';-- 转换后的语句为update stockset amount = 100where warehouse = '供电局1#仓库' and mat_num = 'm001';
[例3.79] 向供电局1#仓库的物资视图s1_stock中插入一个新的物资记录,
其中物资编号为 “m021”,
物资名称为 “护套绝缘电线”,
规格为 “BVV-150”,
数量为100,
单价为13.5
insertinto s1_stockvalues('m022', '护套绝缘电线', 'BVV-150', 100, 13.5);
注意:
SQL Server2000中不可以执行, 而SQL Server 2005则可以执行
[例3.80] 删除供电局1#仓库的物资视图 s1_stock中, 编号为 m001 的物资的记录
deletefrom s1_stockwhere mat_num = 'm001';-- 转换后, 如下deletefrom stockwhere warehouse = '供电局1#仓库' and mat_num = 'm001';
SQL Server中规定以下视图无法更新:
- 视图的字段来自聚集函数
- 视图定义中含有 group by子句
- 视图定义中含有 distinct短语
- 一个不允许更新的视图上定义的视图也不允许更新
视图的作用
1, 能够简化用户的操作
当视图中的数据不是直接来自某个基本表, 而是基于多张表的连接而形成的视图,
定义视图能够简化用户的数据查询操作
2, 能够使用户以多种角度看待同一数据
视图机制能使不同用户以不同方式看待同一数据, 适应数据库共享的需要
3, 能够对机密数据提供安全保护
对不同用户定义不同视图, 使每个用户只能看到他有权看到的数据;
对数据库的重构提供了一定程度的逻辑独立性
例: 配电物资库存记录表
stock(mat_num, mat_name, speci, warehouse, amount, unit, total),--"垂直"地分成两个基本表:s1(mat_num, mat_name, speci, warehouse)s2(mat_num, amount, unit, total)
通过建立一个视图 stock
create view stock(mat_num, mat_name, speci, warehouse, amount, unit, total)asselect s1.mat_num, s1.mat_name, s1.speci, s1.warehouse, s2.amount, s2.unit, s2.totalfrom s1, s2where s1.mat_num = s2.mat_num;
使用户的外模式保持不变, 从而对原Stock表的查询程序, 不必修改
小结
了解SQL语言的发展及特点;
熟练而正确的使用SQL语言完成表, 视图的创建, 数据库的查询, 插入, 删除 和 修改等操作,
特别是各种各样的查询, 要求重点掌握SQL语言强大的查询功能
练习用的数据
–
–学生学号, 学生姓名, 学生性别,
Student(Sno , Sname , Ssex , Sage , Sclass)
–
Teacher(Tno,Tname,Tsex,Tage, Tprof, Tdept)
–
Course(Cno , Cname ,Tno)
–选修课程表
–(学号, 课程号), 共同构成 主码, 单个 叫做 外码
SC(Sno , Cno , Grade)
创建一个视图, 来源于成绩表, 包含姓名, 课程名, 成绩
多表连接
create view V_Gradeasselect Sname, Cname, Gradefrom Student, Course, SCwhere Student.Sno = SC.Sno and Course.Cno =
接下来
基于视图的 视图
对于 行列子集表, 可以对其CRUD
而 对于多张基本表 生成的 视图, 只能 查询
create view s3_stockasselect *from s1_stockwhere amount >=50
s3_stock, zuowei
select *from s3_stock;
当把 s1_stock, 删除后, s3_stock,
本质
select本身有表达式, 而不是 视图有表达式
【例3.71】建立一个体现抢修工程项目实际抢修天数的视图。
--可以写成汉字CREATE VIEW s1_salvaging(prj_name, start_date, end_date,days)AS--datediff(), 数据库自带的函数, day, 是个参数, 的位置, 可以些其他select prj_name, start_date, end_date, datediff(day, start_date, end_date)from salvaging;
【例3.72】将仓库名称与其仓库内所存放物资的种类定义为一个视图。
CREATE VIEW s4_stock(warehouse,counts)AS SELECT warehouse, COUNT(mat_num) FROM stock GROUP BY warehouse;
作业
第3张 课后全部习题
下星期交
3.5.1. 视图的定义与删除
3.5.2. 查询视图
速度快, 效率高
视图的核心 一个 select语句
视图消解法（View Resolution）
1**）进行有效性检查，检查查询的基本表、视图是否存在；**
2**）如果存在，从数据字典中取出视图的定义，把视图定义中的子查询与用户的查询结合起来，转换成等价的对基本表的查询；**
不复杂, 即便这样, 也比直接操作 基本表, 快
3**）执行修正后的查询。**
查询出, 最新的数据
【例3.75】 在供电局1#仓库的物资视图s1_stock中找出单价小于20的物资名称、规格和单价。
select mat_name, speci, unit--s1_stock, 数据源from s1_stockwhere unit < 20;
--转换后的查询语句为：SELECT mat_name,speci, unitFROM stockWHERE warehouse ='供电局1#仓库' AND unit<20;
体会
2**）如果存在，从数据字典中取出视图的定义，把视图定义中的 子查询 与 用户的查询 结合起来，转换成等价的对基本表的查询；
建立视图, 就是为了 (方便)使用
s1_stock, 1#仓库的全部物资
可以把s1_stock, 看做表 来处理
把s1_stock , out_stock连接
【例3.76】 查询使用了 供电局1#仓库 物资的 抢修工程项目号
1, 先建立视图
create view s1_stockasselect *from stockwhere warehouse = '供电局1#仓库';
select prj_numfrom s1_stock, out_stock--这2张表关联, 是通过 mat_nowhere s1_stock.mat_no = out_stock.mat_no;
视图
0, 完全作为 基本表
2, 作为 表 和 其他表连接, 关联
1, 可以作为 数据源
【例3.77】 查询所存物资种类大于2的仓库名称。
第1步, 列出 各个仓库(分组查询) , 物资的种类的 视图
涉及到的表: stock
一般视图名称, V开头
create view s4_stock(仓库名称, 种类)asselect warehouse, count(mat_num)from stock--分组查询, 结果是一个函数--每个仓库, 用group bygroup by warehouse;
第2步
select 仓库名称from s4_stockwhere 种类 > 2;
where 后面, 不能跟 聚集函数 count(mat_num)
INSERTINTO s1_stockVALUES('m022', '护套绝缘电线', 'BVV-150', 100,13.5);
这里执行错误, 因为当初 生成 s1_stock视图的时候, 使用了 *,
可见 * 的 效果不好
正确做法, 生成s1_stock视图的时候, 不使用 *, 甚至不加上 total属性
只有课程号, 可以唯一地标识一门课程, 因为 同一课程名, 不同学时, 属于 不同课程
下周, 最晚下下周, 期中考试
期末, 理论, 闭卷
master, 系统数据库
3.5.3. 更新视图
SQL SERVER中规定以下视图无法更新：
视图的字段来自聚集函数；
视图定义中含有GROUP BY子句；
视图定义中含有DISTINCT短语;
一个不允许更新的视图上定义的视图也不允许更新。
3.5.4. 视图的作用
1, 能够简化用户的操作
当视图中的数据不是直接来自某个基本表，而是基于多张表的连接而形成的视图，定义视图能够简化用户的数据查询操作。
2, 能够使用户以多种角度看待同一数据
视图机制能使不同用户以不同方式看待同一数据，适应数据库共享的需要。
3, 能够对机密数据提供安全保护
对不同用户定义不同视图，使每个用户只能看到他有权看到的数据；
4, 对数据库的重构提供了一定程度的逻辑独立性
例：配电物资库存记录表
stock(mat_num,mat_name,speci,warehouse, amount,unit,total),“垂直”地分成两个基本表： s1(mat_num,mat_name,speci,warehouse) s2(mat_num,amount,unit, total )
后面会讲, 基本表的拆分, 范式相关内容, 冗余相关内容
| 类型表示 | 类型说明 | |
|---|---|---|
| 数值型 | int或integer | 长整数(-2147483648~2147483647) |
| smallint | 短整数(-32768~32767) | |
| real | 浮点数 | |
| float(n) | 浮点数，精度至少为n位数字 | |
| decimal(p, s) | 定点数，由p位数字（不包括符号、小数点）组成，小数点后面有d为数字 | |
| 字符串型 | char(n) | 长度为n的定长字符串(1~8000) |
| varchar(n) | 具有最大长度为n的边长字符串(1~8000) | |
| text | 转用于存储数量庞大的变长字符串 | |
| 位数据类型 | bit | 常作为逻辑变量，表示真假(0或1) |
| 日期时间型 | datatime | 格式为YYYY-MM-DD-HH:MM:SS， 日期从1689年1月1日到9999年12月31日 |
| smalldatetime | 格式为YYYY-MM-DD-HH:MM:SS 日期从1900年1月1日到2079年12月31日 | |
表约束条件
创建表语句中常用完整性约束
1、主码约束：RPIMARY KEY
2、唯一性约束：UNIQUE
3、非空值约束：NOT NULL
4、参照完整性约束：POREGIN KEY REFERENCES 引用表名(引用表)
5、检查约束：CHECK(检查表达式)
6、默认值约束：DEFAULT 默认值
关系(表)完整性约束
1、实体完整性
2、参照完整性
3、用户定义的完整性
3.6. Others
3.6.1. 注释
–注释1
/注释2/
1, 获取所有的数据库名
select namefrom master.dbo.sysdatabasesorder by name;
2, 获取所有表名
select namefrom sysobjectswhere xytpe='U'order by name;--xtype='U', 表示所有用户表--xtype='S', 表示所有所有系统表
3, 获取所有字段名
select namefrom syscolumnswhere id=object_id('bcustomer');
往数据库中添加表数据的时候, 记得 先use Sample; 先选中要操作的数据库
否则, 要添加的表, 就添加到了 master 中了
4_23_作业
1, 课后所有习题
2, 第3章, 所有例题, 整理, 抄一遍, 整理笔记, 整理到笔记本上
如何定义, 创建3张表
查询
第3章
select语句, 可以嵌入到 高级语言中,
3.6.2. 表字段默认值
https://blog.csdn.net/longzhiten98/article/details/7694148/
https://blog.csdn.net/qq_15237993/article/details/70212545
1, 如果表字段已经建好
alter table 表名 ADD constraint DF_TABLEName_FieldName DEFAULT 1 FOR 字段名
2, 如果表字段没有建
alter table 表名 add 字段名 int default(1)
–sqlserver建表时设置字段初始默认值完整的示例
1, 创建表时设置字段的默认值
create table 表(id int,name varchar(10) default '张三',age int)
2, 添加字段时设置字段的默认值
alter table 表 add sex char(2) default '男'
3, 为表中现有的字段设置默认值
alter table 表 add constraint DF_age_表 default(20) for age
3.7. 学生信息例子
/*
Student(Sno , Sname , Ssex , Sage , Sclass)
Teacher(Tno,Tname,Tsex,Tage, Tprof, Tdept)
Course(Cno , Cname ,Tno)
SC(Sno , Cno , Grade)
*/
1, 查询所有学生的姓名、性别和班级。
select Sname, Ssex, Sclassfrom Student;
2, 查询该学校所有的系名。
select distinct Tdeptfrom Teacher;
3, 查询考试成绩在60到80之间的学生记录。
select *from Studentwhere grade between 60 and 80;
3, 查询成绩为80，85，90的学生记录并按学号升序课号降序排列。
select
5, 统计95031班全体学生人数。
select count(*)from Student--不用分组，用where语句where Sclass = '95031';
拓1，统计每个班的人数
select Sclass, count(*)from Student--按什么(Sclass)分组，在select后面加上某个字段Sclassgroup by Sclass
拓2
select Sclass, Ssexfrom Studentgroup by Sclass, Ssex
拓3，统计每个班女生人数
select Sclass, Ssex , count(*)where Ssex = '女'group by Ssex
6, 查询“3-105”号课程的平均分,并赋予别名。
select
7, 查询 至少有2名学生 选修的 并 以3开头的 课程号 及 平均分数。
select Cno, avgfrom SC--找到以3开头where Cno like '3%'--再分组--在分组的基础上，提取group by Cno haivng count(*)>=2
8, 显示最低分大于70，最高分小于90的课程号。
select Cnofrom SC--主提取group by Cno--按课程号分组，在having中，设置70， 90having min(Grade)>70 and max(Grade)<90;--列出每个学生的选课情况， 计算每名同学的总成绩，用compute byselect Sno, Cno, Gradefrom SC--按学号进行分组order by Sno;compute sum(grade) by Sno;--多表和单表一样，只是 多了一个连接
select，类似于 投影中的 π
all, 所有
distinct ，去掉重复的值
最基本的 select form
where，可选项, where,相当于选择运算
GROUP BY 分组
order by, 把输出的结果，排序
asc，升序，默认就是升序
desc，降序
多张表，才做 笛卡尔积
4. 存储过程、触发器和数据完整性
4.1. SQL Server编程语法
4.1.1. 变量
局部变量
全局变量(系统变量)
变量 为 存储过程 做准备
mysql只能在某种 数据库引擎 引擎下, 支持外检
4.1.2. 显示信息
4.1.3. 注释语句
4.1.4. 批处理
4.1.5. 流程控制语句
4.2. 存储过程
供电局1#仓库所有物资的信息
select *from stockwhere warehouse = '供电局1#仓库';
存储过程
create procedure exp2asselect *from stockwhere warehouse = '供电局1#仓库';
带参数的存储过程
create procedure exp2(arg1)asselect *from stockwhere warehouse = arg1;
select mat_name, speci, prj_name, prj_statusfrom salvaging, stock, out_stock
带默认参数的存储过程
create procedure exp4 @mat_name @asselect mat_name, speci, prj_name, prj_statusfrom salvaging, stock, out_stock
项目201111领取的物资的总数量
create procedure p_exp5 @num char(8), output @s int outputasselect @s = sum(amount)from out_stockwhere prj_num = @num;
工程1部, 在指定的时间段, 完成的项目总数
select sum()from salvaging, stock, out_stock
【例4.11】
创建一个存储过程，
根据输入的工程部门 及 起始时间段，
统计汇总该部门在对应时间段内所参与抢修的工程项目总数 以及 领取物资的总成本， 并要求输出
CREATE PROCEDURE exp5 @department varchar(50),@start_date datetime,@end_date datetime, @count_prj int OUTPUT, @sum_cost decimal(18,2) OUTPUT ASSELECT @count_prj=COUNT(salvaging.prj_num), @sum_cost=SUM(Out_stock.amount*Stock.unit)FROM salvaging,out_stock,stock WHERE out_stock.prj_num =salvaging.prj_num and out_stock.mat_num =Stock.mat_num and department =@department and get_date between @start_date and @end_date
作业
employee(emp_id,emp_name,sex,birth,level) 其中 level 的值为low/mid/high
Salary(emp_id,salary,subsidy)
创建一存储过程，根据输入的emp_id计算该职工的subsidy:对应于level的subsidy分别是：1000、2000、3000
整理3, 4章笔记
后面会做综合实验
下次大作业的时候, 使用上 存储过程
while(unit)
continue
4.2.1. 存储过程的基本概念
存储在数据库服务器中的一组编译成单个执行计划的SQL语句.
在使用Transact-SQL语言编程的过程中, 可以将某些需要多次调用以实现某个特定任务的代码编写成一个过程, 将其保存在数据库中, 并由SQL Server服务器通过过程名调用, 称为存储过程
优点:
1, 运行效率高, 提供了在服务器端快速执行SQL语句的有效途径
2, 降低了客户机和服务器之间的通信量
3, 方便实施企业规则
4.2.2. 创建存储过程
创建存储过程的SQL语句格式为:
create proedure 存储过程名 [; 版本号][{@参数 数据类型} [VARYING] [= 默认值][OUTPUT],... ]
4.2.3. 使用SQL Server管理控制台执行存储过程
4.2.4. 修改和删除存储过程
4.3. 触发器
也是一种 存储过程
insert, update(delete, insert), delete
混合的,
4.3.1. 触发器的基本概念
4.3.2. DML触发器的工作原理
4.3.3. 创建触发器
4.3.4. 修改和删除触发器
4.4. 数据库的完整性
约束, 默认值, 规则, 都是数据库对象, 体会 数据库对象 这个说法
还有, 对于数据库对象删除, 使用 drop; 不能使用 delete
数据库对象, 应该就是指 约束, 默认值, 规则, 这3个, 本质也是 一个个的 数据库
4.4.1. 约束
4.4.2. 默认值
4.4.3. 规则
4.4.4. 用户定义的数据完整性
下节课, 上机考试
存储过程, 写到后台, 而不是写到Java程序中, 否则, Java代码就复杂了
5. 关系数据库设计理论
5.1. 问题的提出
5.2. 基本概念
5.2.1. 函数依赖
5.2.2. 码
5.3. 规范化
5.3.1. 第一范式
5.3.2. 第二范式
5.3.3. 第三范式
5.3.4. BC范式
5.3.5. 多值依赖与第四范式
5.3.6. 关系模式的规范化
5.4. 函数依赖的公理系统
5.4.1. Armstrong公理系统
5.4.2. 闭包
5.4.3. 函数依赖集的等价和最小化
5.5. 模式分解
5.5.1. 模式分解的准则
5.5.2. 分解的函数依赖保持性和无损连接性
5.5.3. 模式分解的算法
6. 索引
6.1. 索引的概念
6.1.1. 聚集索引
6.1.2. 非聚集索引
6.1.3. 唯一索引
6.1.4. 何时应该创建索引
6.1.5. 系统如何访问表中的数据
6.2. SQL Server2008中的索引
6.2.1. 索引的结构
6.2.2. 管理索引
7. 数据库设计
7.1. 数据库设计概述
7.1.1. 数据库的生命周期
7.1.2. 数据库的设计方法
7.2. 数据库的设计过程
7.3. 需求分析阶段
7.4. 概念设计阶段
7.4.1. 概念模型的特点
7.4.2. 实体-联系模型
7.4.3. 概念结构设计的方法与步骤
7.5. 逻辑设计阶段
7.6. 物理设计阶段
7.7. 数据库实现阶段
7.8. 数据库的运行与维护阶段设计
7.9. 数据库设计实例：电网设备抢修物资管理数据库设计
7.9.1. 需求分析
7.9.2. 概念模型
7.9.3. 逻辑模型
8. 数据库安全
8.1. 安全性概述
8.1.1. 用户标识与鉴别
8.1.2. 存取控制
8.1.3. 视图机制
8.1.4. 审计
8.1.5. 数据加密
8.2. SQL Server的安全性
8.2.1. SQL Server2008的身份验证模式
8.2.2. SQL Server2008的安全机制
8.3. 用户管理与角色管理
8.3.1. 登录用户和数据库用户
8.3.2. 用户管理
8.3.3. 角色管理
8.3.4. SQL Server的固定角色
8.4. 权限管理
8.4.1. 授予权限
8.4.2. 收回权限
8.4.3. 禁止权限
9. 数据库保护
9.1. 事务
9.1.1. 事务的定义
9.1.2. 事务的ACID性质
9.1.3. 事务的状态
9.2. 并发控制
9.2.1. 并发操作与数据的不一致性
9.2.2. 封锁
9.2.3. 并发操作的调度
9.3. 数据库的恢复
9.3.1. 存储器的结构
9.3.2. 故障的种类
9.3.3. 数据转储技术
9.3.4. 使用日志的数据库恢复技术
9.3.5. 缓冲技术
9.3.6. 检查点技术
9.3.7. 恢复策略
9.4. SQL Server数据库备份与恢复
9.4.1. 数据库备份方法
9.4.2. 数据库恢复
10. 数据库技术的进展与应用
10.1. 现代数据库的特点
10.2. 数据库新技术
10.2.1. 面向对象数据库
10.2.2. 对象-关系数据库
10.2.3. 实时数据库技术
10.2.4. 分布式数据库
10.2.5. NoSQL数据库
10.2.6. XML数据库
10.2.7. 空间数据库
10.2.8. 模糊数据库
10.2.9. 科学统计数据库
10.2.10. 工程数据库
10.2.11. 时态数据库
10.3. 数据仓库
10.3.1. 数据仓库简介
10.3.2. 数据仓库技术
10.3.3. 数据仓库的几个重要概念
10.3.4. 数据仓库的结构
10.3.5. 数据仓库的多维数据模型
10.3.6. 数据仓库的系统设计
10.3.7. 数据仓库的未来
10.4. 数据挖掘
10.4.1. 数据挖掘的流程
10.4.2. 数据挖掘的分析方法
10.4.3. 数据挖掘常用的基本技术
10.4.4. 数据挖掘技术实施的步骤
10.4.5. 数据挖掘技术的发展
10.5. 大数据概述
10.5.1. 大数据的定义
10.5.2. 大数据的发展历史
10.5.3. 大数据技术的现状
10.5.4. 大数据的未来发展
10.6. 结语
11. 实验
要想学好数据库, 就把数据库玩烂, 用大量数据, 模拟, 测试
11.1. 实验一 通过SQL Server Management Studio创建及管理数据库
11.1.1. 实验目的
（1）熟悉SQL Server Management Studio。
（2）掌握通过SQL Server Management Studio管理数据库的方法。
（3）掌握数据库及其物理文件的结构关系。
（4）掌握通过SQL Server Management Studio管理数据表的方法。
11.1.2. 实验内容
1．通过SQL Server Management Studio创建数据库
创建一个名称为学生管理信息Student_info数据库，创建数据库Student_info的具体参数如表B.1所示。
表B.1 数据库参数
| 参数名称 | 参考参数 |
|---|---|
| 数据库名称 | Student_info |
| 数据库逻辑文件名称 | Student_info_data |
| 数据库物理文件名称 | Student_info_data.mdf |
| 数据文件初始大小 | 20MB |
| 数据文件大小的最大值 | 300MB |
| 数据文件增长增量 | 5MB |
| 日志逻辑文件名称 | Student_info_log |
| 日志物理文件名称 | Student_info_log.ldf |
| 日志文件初始大小 | 5MB |
| 日志文件大小最大值 | 50MB |
| 日志文件增长增量 | 1MB |
2．查看、验证创建的数据库
方法1：执行sp_helpdb系统存储过程查看Student_info数据库的信息。
方法2：在SQL Server Management Studio中查看。
3．修改数据库的属性
通过SQL Server Management Studio查看数据库，将数据库Student_info数据文件的初始大小改为30MB，最大值改为300MB，数据增长改为5%，日志文件的初始大小改为20MB，最大值改为30MB，数据增长改为6%。
4．数据库的分离及附加
（1）将Student_info数据库从数据库服务器分离。
（2）将Student_info数据库再次附加到服务器中。
5．通过SQL Server Management Studio在Student_info数据库中创建表
Student_info数据库包含三张表：Student表、Course表及SC表，分别代表学生信息、课程信息及学生选课信息。三张表的结构及其约束见表B.2、表B.3和表B.4。
表B.2 Student表结构和约束
| 列 名 称 | 类 型 | 宽度 | 允许空值 | 默认值 | 约束 | 主键 | 说 明 |
|---|---|---|---|---|---|---|---|
| Sno | Char | 8 | 否 | 是 | 学号 | ||
| Sname | Varchar | 8 | 否 | 学生姓名 | |||
| Sex | Char | 2 | 否 | 男 | 性别 | ||
| Birth | Smalldatetime | 否 | 出生年月 | ||||
| Classno | Char | 3 | 否 | 班级号 | |||
| Entrance_date | Smalldatetime | 否 | 入学时间 | ||||
| Home_addr | Varchar | 40 | 是 | 家庭地址 |
表B.3 Course表结构和约束
| 列 名 称 | 类 型 | 宽度 | 允许空值 | 默认值 | 约 束 | 主键 | 说明 |
|---|---|---|---|---|---|---|---|
| Cno | Char | 3 | 否 | 是 | 课程号 | ||
| Cname | Varchar | 20 | 否 | 课程名称 | |||
| Total_perior | Smallint | 是 | 大于0 | 总学时 | |||
| Credit | Tinyint | 是 | 大于0，小于等于6 | 学分 |
表B.4 SC表结构和约束
| 列 名 称 | 类 型 | 宽度 | 允许空值 | 默认值 | 约 束 | 主键 | 外键 | 说明 |
|---|---|---|---|---|---|---|---|---|
| Sno | Char | 8 | 否 | 是 | 是 | 学号 | ||
| Cno | Char | 3 | 否 | 是 | 是 | 课程号 | ||
| Grade | Tinyint | 是 | 大于等于0，小于等于100 | 否 | 成绩 |
create table Student( Sno char(8) not null primary key, Sname varchar(8) not null default '男', Sex char(2) not null, Birth smalldatetime not null, Classno char(3) not null, Entrance_date smalldatetime not null, Home_addr varchar(40));
create table Course( Cno char(3) primary key, Cname varchar(20), Total_perior smallint check(Total_perior > 0), Credit tinyint check(Credit > 0 and Credit <= 6));
create table SC( Sno char(8), Cno char(3), Grade Tinyint check(Grade >= 0 and Grade <= 100), primary key(Sno, Cno), foreign key (Sno) references Student(Sno), foreign key (Cno) references Course(Cno));
6．通过SQL Server Management Studio管理表结构
（1）添加和删除列
a．给Student表增加身高（以米单位）stature列，类型为numeric(4，2)，允许为空值，且身高值需小于3.0米。
b．给Student表增加所在系Sdept列，字符型，长度为20，允许为空值。
请读者考虑：如果Sdept列不允许为空值，结果会如何？如何才能实现Sdept列不允许为空值？
c．给Student表增加邮政编码Postcode列，字符型，长度为6，可以为空，若不为空时，则要求其值只能出现数字，不能是其他字符。
(前面要求为 字符型, 后面又要求为 数字, 矛盾吗, 是分2种情况写吗?)
d．删除Student表中身高stature列。
alter table Student add stature numeric(4, 2) check(stature > 0 and stature < 3.0);
alter table Student add Sdept char(20);
请读者考虑：如果Sdept列不允许为空值，结果会如何？如何才能实现Sdept列不允许为空值？
alter table Student add Sdept char(20) not null default '计算机';
alter table Student add Postcode char(6);
alter table Student add Postcode int(6) not null;
alter table Student drop column stature;
（2）添加和删除约束
a．在Student表添加约束：入学时间必须在出生年月之后。
b．给SC表的成绩grade列增加默认值约束，默认值为0。
c．删除grade列的默认值约束。
alter table Student add check(Entrance_date > Birth);
有时间, 再看一下, 下面这条语句
alter table SC add constraint a_df default 0 for grade;
alter table SC drop constraint a_df;
7．通过SQL Server Management Studio对表添加、修改、删除数据
（1）插入数据，Student表、Course表、SC表的记录见表B.5、表B.6、表B.7。
表B.5 Student表
| Sno | Sname | Sex | Birth | Classno | Entrance_date | Home_addr | Sdept | Postcode |
|---|---|---|---|---|---|---|---|---|
| 20110001 | 张虹 | 男 | 1992/09/11 | 051 | 2011/09/01 | 南京 | 计算机系 | 200413 |
| 20110002 | 林红 | 女 | 1991/11/12 | 051 | 2011/09/01 | 北京 | 计算机系 | 100010 |
| 20110103 | 赵青 | 男 | 1993/05/11 | 061 | 2011/09/01 | 上海 | 软件工程 | 200013 |
表B.6 Course表
| Cno | Cname | Total_perior | Credit |
|---|---|---|---|
| 001 | 高数 | 96 | 6 |
| 002 | C语言程序设计 | 80 | 5 |
| 003 | Java语言程序设计 | 48 | 3 |
| 004 | Visual_Basic | 48 | 4 |
表B.7 SC表
| Sno | Cno | Grade |
|---|---|---|
| 20110001 | 001 | 89 |
| 20110001 | 002 | 78 |
| 20110001 | 003 | 89 |
| 20110002 | 002 | 60 |
| 20110103 | 001 | 80 |
insertinto Student(Sno, Sname, Sex, Birth, Classno, Entrance_date, Home_addr, Sdept, Postcode)values('20110001', '张虹', '男', '1992-09-11', '051', '2011-09-01', '南京', '计算机系', '200413'),('20110002', '林红', '女', '1991-11-12', '051', '2011-09-01', '北京', '计算机系', '100010'),('20110103', '赵青', '男', '1993-05-11', '061', '2011-09-01', '上海', '软件工程', '200013');
insertinto Course(Cno, Cname, Total_perior, Credit)values('001', '高数', 96, 6),('002', 'C语言程序设计', 80, 5),('003', 'Java语言程序设计', 48, 3),('004', 'Visual_Basic', 48, 4);
insertinto SC(Sno, Cno, Grade)values('20110001', '001', 89),('20110001', '002', 78),('20110001', '003', 89),('20110002', '002', 60),('20110103', '001', 80);
其他数据可自行添加。要求Student表和SC表中数据包括了每位同学自己的学号。
（2）修改数据
a．将Student表中的学号为’20110103’的同学的出生年月改为1993年10月1日。
b．将Course表中的课程号为’002’的学分改为4，总学时改为64。
update Studentset Birth = 1993-10-1where Sno = '20110103';
update Courseset Credit = 4 and Total_perior = 64where Cno = '002';
（3）删除数据（请注意约束的限制）
a．删除SC表中20110103同学的选课记录。
b．删除Course表中课程号为002的记录。如果不能成功删除该记录，请分析原因。
deletefrom SCwhere Sno = '20110103';
deletefrom Coursewhere Cno = '002';--不能删除, 因为SC表中, 有引用Course表中002这一条数据
11.2. 实验二 通过SQL语句创建与管理数据表
11.2.1. 实验目的
（1）掌握查询分析器的使用。
（2）掌握通过SQL语句创建表的方法。
（3）掌握通过SQL语句修改表结构的方法。
（4）掌握通过SQL语句添加、修改、删除表数据的方法。
11.2.2. 实验内容
1．通过SQL语句删除表
用SQL语句在数据库Student_info中删除实验一创建的Student表、Course表、SC表。
use Student_info;
drop table SC;drop table Student;drop table Course;
2．通过SQL语句创建表
用SQL语句在数据库Student_info中创建实验一中的Student表、Course表、SC表，表结构如实验一中表B.2、表B.3、表B.4所示。
create table Student( Sno char(8) not null primary key, Sname varchar(8) not null default '男', Sex char(2) not null, Birth smalldatetime not null, Classno char(3) not null, Entrance_date smalldatetime not null, Home_addr varchar(40));
create table Course( Cno char(3) primary key, Cname varchar(20), Total_perior smallint check(Total_perior > 0), Credit tinyint check(Credit > 0 and Credit <= 6));
create table SC( Sno char(8), Cno char(3), Grade Tinyint check(Grade >= 0 and Grade <= 100), primary key(Sno, Cno), foreign key (Sno) references Student(Sno), foreign key (Cno) references Course(Cno));
3．通过SQL语句管理表结构
（1）添加和删除列
a．给Student表增加身高（以米单位）stature列，类型为numeric（4，2），允许为空值，且身高值需小于3.0米。
b．给Student表增加所在系Sdept列，字符型，长度2，不允许为空值。
c．给Student表增加邮政编码Postcode列，字符型，长度为6，可以为空，若不为空时，则要求其值只能出现数字，不能是其他字符。
d．删除Student表中身高stature列。
alter table Student add stature numeric(4, 2) check(stature > 0 and stature < 3.0);
alter table Student add Sdept char(20) not null;
请读者考虑：如果Sdept列不允许为空值，结果会如何？如何才能实现Sdept列不允许为空值？
alter table Student add Sdept char(20) not null default '计算机';
alter table Student add Postcode char(6);
alter table Student add Postcode char(6) not null;
alter table Student drop column stature;-- 因为我之前使用的是这一句 alter table Student add stature numeric(4, 2) check(stature > 0 and stature < 3.0);-- 所以删除的时候, 提示删除失败, 提示 有在上面添加过约束-- 所以, 我先把对stature的 check约束, 先删除, 再测试一下-- 所以先执行 alter table Student drop constraint check; 试一下
（2）添加和删除约束
a．在Student表添加约束：入学时间必须在出生年月之后。
b．给SC表的成绩grade列增加默认值约束，默认值为0。
c．删除grade列的默认值约束。
alter table Student add check(Entrance_date > Birth);
有时间, 再看一下, 下面这条语句
alter table SC add constraint a_df default 0 for grade;
alter table SC drop constraint a_df;
4．通过SQL语句添加、修改、删除表中数据
（1）插入数据
a．Student表、Course表、SC表的记录见实验一的表B.5、表B.6、表B.7，其他数据可自行添加。要求Student表和SC表中数据包括了每位同学自己的学号。
b．执行如下语句：insert into student（sno，sname，sex）values（‘20101101’，‘赵青’，‘男’），该语句能成功执行吗？为什么？
c．执行如下语句：insert into sc values（‘20110103’，‘005’，80），该语句能成功执行吗？为什么？
insertinto Student(Sno, Sname, Sex, Birth, Classno, Entrance_date, Home_addr, Sdept, Postcode)values('20110001', '张虹', '男', '1992-09-11', '051', '2011-09-01', '南京', '计算机系', '200413'),('20110002', '林红', '女', '1991-11-12', '051', '2011-09-01', '北京', '计算机系', '100010'),('20110103', '赵青', '男', '1993-05-11', '061', '2011-09-01', '上海', '软件工程', '200013');
insertinto Course(Cno, Cname, Total_perior, Credit)values('001', '高数', 96, 6),('002', 'C语言程序设计', 80, 5),('003', 'Java语言程序设计', 48 3),('004', 'Visual_Basic', 48, 4);
insertinto SC(Sno, Cno, Grade)values('20110001', '001', 89),('20110001', '002', 78),('20110001', '003', 89),('20110002', '002', 60),('20110103', '001', 80);
insert into student（sno，sname，sex）values（'20101101'，'赵青'，'男'）;
上面这条不能执行, 首先一个问题, 标点符号是英文下的, 下面这个修改为英文下
insert into student(sno,sname,sex)values('20101101', '赵青', '男');
报错内容:
消息 515，级别 16，状态 2，第 1 行
不能将值 NULL 插入列 ‘Sdept’，表 ‘Student_info.dbo.Student’；列不允许有 Null 值。INSERT 失败。
语句已终止。
不能成功, 首先表Student,是大写, sql server中, 可能不区分大小写, 这点没问题
Birth, Classno, Entrance_date, 设置为了 not null, 所以不能添加成功
insert into sc values（'20110103'，'005'，80）;
不能添加成功, Course表中, 只有4条数据, 4门课程, 没有Cno = ‘005’ 的课程, 引用失败, 所以不能添加成功
（2）修改数据
a．使用T-SQL语句，将Course表中的课程号为’002’的学分改为4，总学时改为64。
b．使用T-SQL语句，将SC表中的选修了’002’课程的同学的成绩*80%。
update Courseset Credit = 4, Total_perior = 64where Cno = '002';
update SCset Grade = Grade * 0.8where Cno = '002';
（3）删除数据
a．使用T-SQL语句，删除选修了“C语言程序设计”的学生的选课记录。
b．使用T-SQL语句，删除所有的学生选课记录。
说明：删除后，请重新插入SC表中的记录。
deletefrom SCwhere Cno = '002';
deletefrom SC;
insertinto SC(Sno, Cno, Grade)values('20110001', '001', 89),('20110001', '002', 78),('20110001', '003', 89),('20110002', '002', 60),('20110103', '001', 80);
11.3. 实验三 单表查询
11.3.1. 实验目的
掌握简单SQL查询语句的应用，包括like、top、order by、compute 和聚集函数的应用。
11.3.2. 实验内容
1．基本查询
（1）查询Student表中全体学生的全部信息。
（2）查询全体学生的学号、姓名。
select *from Student;
select Sno, Snamefrom Student;
2．查询时改变列标题的显示
查询全体学生的学号、姓名、家庭地址信息，并分别加上“学生”、“学号”、“家庭地址”的别名信息。
select Sname 学生, Sno 学号, Home_addr 家庭地址from Student;
3．条件查询
（1）查询成绩大于80分的学生的学号及课程号、成绩。
（2）查询成绩介于75～80分的学生的学号及课程号、成绩。(体会, 介于2个字, 就是推荐使用 between and)
（3）查询选修了课程号为“002”，且成绩大于80的学生的学号。
（4）某些学生选修某门课程后没有参加考试，所以有选课记录，但没有考试成绩，请查询缺少成绩的学生的学号和相应的课程号。
select Sno, Cno, Gradefrom SCwhere Grade > 80;
select Sno, Cno, Gradefrom SCwhere Grade between 75 and 80;-- 等价于select Sno, Cno, Gradefrom SCwhere Grade >= 75 and Grade <= 80;
select Snofrom SCwhere Cno = '002' and Grade > 80;
先向其中添加一些数据, 这次不指定Grade 为 null, 即不给Grade 直接赋值为 null
insertinto SC(Sno, Cno)values('20190213', '001'),('20190213', '002'),('20190213', '003'),('20190213', '004');
select Sno, Cnofrom SCwhere Grade is null;-- 下面这个是错误的select Sno, Cnofrom SCwhere Grade = null;
4．基于IN子句的数据查询
从Course表中查询出“高数”、“C语言程序设计”的所有信息。
select *from Coursewhere Cname in('高数', 'C语言程序设计');
5．基于BETWEEN…AND子句的数据查询
查询所有成绩在70～80之间的学生选课信息。
select *from SCwhere Grade between 70 and 80;
6．基于LIKE子句的查询
（1）从Student表中分别检索出姓张的所有同学的资料。
（2）检索名字的第二个字是“红”或“虹”的所有同学的资料。
（3）查询课程名为Visual_Basic的课程的学分。（提示：使用ESCAPE短语）
select *from Studentwhere Sname like '张%';
select *from Studentwhere Sname like '_[红, 虹]%';-- 下面这个是错误的--select *--from Student--where Sname like '_红%' or '_虹%';
select Creditfrom Coursewhere Cname like 'Visual\_Basic' escape '\'; -- 如果不要求 escape, 下面这个也可以select Creditfrom Coursewhere Cname = 'Visual_Basic';
7．使用TOP关键字查询
（1）从选课表中检索出前3个课程信息。
（2）从选课表中检索出前面20%的课程信息。
select top 3 *from SC-- order by, 默认升序order by Cno;
select top 20 percent *from SCorder by Cno;
一些原理类的知识点, 还是需要理解的, 比如上面这个是, 先使用 order by 排序一下, 再从其中选择
可以联想excel中的, 排序, 筛选查找
8．消除重复行
检索出学生已选课程的课程号，要求显示的课程号不重复。
select distinct Cnofrom SC;
9．查询经过计算的值
查询全体学生的姓名及其年龄。（提示：利用系统函数getdate()）
select Sname 姓名, datediff(day, Birth, getdate())/365 年龄from Student;
10．使用ORDER BY语句 对 查询的结果 进行排序 (体会 order by, 对查询的结果排序, 还是, 先排序, 再筛选查询出来)
（1）显示所有学生的基本信息，按班号排列，班号相同则再按学号排列。
（2）查询全体学生的姓名及其年龄，并按学生的年龄的降序排列。
select *from Studentorder by Classno, Sno;
select Sname 姓名, datediff(day, Birth, getdate())/365 Sagefrom Studentorder by Sage desc;
有时间, 查询一下升序排列的 关键字 asc, 默认就是升序
11．使用聚合函数 (去查询一下, 什么是聚合函数)
（1）查询学生总人数。
（2）计算“002”号课程的(所有的)学生平均成绩、最高分、最低分。
select count(Sno)from Student;
select avg(Grade) 平均成绩, max(Grade) 最高分, min(Grade) 最低分from SCwhere Cno = '002';
体会分步解决问题
1, 先找到选择了 "002"课程的学生
下面这个错误
select Sno, avg(Grade) 平均成绩, max(Grade) 最高分, min(Grade) 最低分from SCwhere Cno = '002';
消息 8120，级别 16，状态 1，第 1 行
选择列表中的列 ‘SC.Sno’ 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。
12．使用GROUP子句进行查询
（1）查询各班级学生总人数。(那么就需要, 按 班级号 分组)
（2）汇总总分大于150分的学生的 学号 及 总成绩。
（3）查询各个课程号相应的选课人数。
select Classno, count(Sno) 班级总人数from Studentgroup by Classno;
错误写法
select Classno, count(Sno)from Studentorder by Classno;
报错
消息 8120，级别 16，状态 1，第 1 行
选择列表中的列 ‘Student.Classno’ 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。
(2)
1, 先按学号 group by, 再计算每个学生的总成绩
select Snofrom Studentgroup by Sno;
select Sno, sum(Grade)from SCwhere sum(Grade) > 150;
最后写法
-- （2）汇总总分大于150分的学生的 学号 及 总成绩。select Sno, sum(Grade)from SCgroup by Sno having sum(Grade) > 150;
13．使用compute 和 compute by子句进行查询
（1）汇总每个学生的学号及总成绩。
（2）按学号汇总出每个学生的学号及总成绩、最高分、最低分以及所有学生总成绩。
观察使用 compute 子句 和 compute by子句执行结果有何不同？
select Sno, sum(Grade)from Studentgroup by Sno;
select Sno, sum(Grade), max(Grade), min(Grade)from Student
11.4. 实验四 复杂查询
11.4.1. 实验目的
掌握两个表以上的连接查询的应用，包括嵌套查询。
11.4.2. 实验内容
1．同一数据库中的多表查询
（1）查询比“林红”年纪大的男学生信息。
（2）查询所有学生的选课信息，包括学号、姓名、课号、课程名、成绩。
select Student.Sno, Student.Sname, Course.Cno, Course.Cname, SC.Gradefrom Student, Course, SCwhere Student.Sno = SC.Sno and Course.Cno = SC.Cno;
（3）查询已选课学生的学号、姓名、课程名、成绩。
select Student.Sno, Student.Sname, Course.Cname, SC.Gradefrom Student, Course, SCwhere Student.Sno = SC.Sno and Course.Cno = SC.Cno;
（4）查询选修了“C语言程序设计”的学生的学号和姓名。
select Student.Sno, Student.Snamefrom Student, Course, SCwhere Student.Sno = SC.Sno and Course.Cno = SC.Cnoand Course.Cname = 'C语言程序设计';
（5）查询与“张虹”在同一个班级的学生学号、姓名、家庭住址。
select Sno, Sname, Home_addrfrom Studentwhere Classno = ( select Classno from Student where Sname = '张虹');
（6）查询其他班级中比“051”班所有学生年龄大的学生的学号、姓名。
select Sno, Snamefrom Studentwhere Sage > ( select max(Sage) from Student where classno = '051');
（7）（选做）查询选修了全部课程的学生姓名。
（8）（选做）查询至少选修了学生“20110002”选修的全部课程的学生的学号、姓名。
（9）查询学生的学号、姓名、学习课程名及课程成绩。
select Student.Sno, Student.Sname, Cname, Gradefrom Student, Course, SCwhere Student.Sno = SC.sno and Course.Cno = SC.Cno;
（10）查询选修了“高数”课且成绩至少高于选修课程号为“002”课程的学生的学号、课程号、成绩，并按成绩从高到低次序排列。
select A.Sno, A.Cno, A.Gradefrom Course, SC A, SC Bwhere Course.Cno = A.Cno and A.Sno = B.Snoand Course.Cname = '高等数学AI' and B.Cno = '002' and A.Grade >= B.Gradeorder by A.Grade desc;
（11）查询选修3门以上课程的学生的学号、总成绩（不统计不及格的课程），并要求按总成绩的降序排列出来。
select Sno 学号, sum(Grade) 总成绩from SCwhere Grade >= 60group by Sno having count(*) > 3order by sum(Grade) desc;
（12）查询多于3名学生选修的并以3结尾的课程号的平均成绩。
select Cno, avg(Grade) 平均成绩from SCwhere Cno like '%3'group by Cno having count(*) > 3;
（13）查询最高分与最低分之差大于5分的学生的学号、姓名、最高分、最低分。(应该指的是, 这个学生的选修的几门课程中)
中间对比测试代码
select *from Student, SC A, SC Bwhere Student.Sno = A.Sno and A.Sno = B.Sno and A.Cno = B.Cno;
最终代码
select A.Sno, Sname, max(A.Grade) 最高分, min(A.Grade) 最低分from Student, SC A, SC Bwhere Student.Sno = A.Sno and A.Sno = B.Sno and A.Cno = B.Cnogroup by A.Sno, Sname having (max(A.Grade) - min(B.Grade)) > 5;
（14）创建一个表student_other，结构同Student，输入若干记录，部分记录和Student表中的相同。
a．查询同时出现在Student表和student_other表中的记录。
b．查询Student表和student_other表中的全部记录。
create table Student_Other( Sno varchar(11) primary key, Sname varchar(20), Sex char(4), Classno varchar(20), Home_addr varchar(255), Sage int);
a,
select Student.*from Student, Student_Otherwhere Student.Sno = Student_Other.Sno;
b,
好像, 能够找出, 多对一的情况下, 笛卡尔积的次数
最终, 找到解决方案了, 做到了左连接 和 右连接, 集合查询, 使用全连接, 会出现 有4个元组, 不能显示,
所以最终采用, 左连接和右连接, 再加上 集合查询的方式
select Student.*from Student left outer join Student_Other on(Student.Sno = Student_Other.Sno)unionselect Student_Other.*from Student right outer join Student_Other on(Student.Sno = Student_Other.Sno);
2．多个数据库间的多表查询
（选做）创建一个数据库student_info_other，参数自定。
（1）当前数据库为Student_info，将student_info数据库中的表student_other复制到student_info_other中。
（2）查询同时出现在Student表和student_info_other数据库student_other表中的记录。
3．外连接查询
（1）查询所有课程信息及其选课信息，包含未被学生选修的课程。
而不使用左外连接, 而使用普通连接, 显示为1416行(不满足题目要求)
select *from Course, SCwhere Course.Cno = SC.Cno;
使用到了左外连接, 这个显示1417行(正确)
select *from Course left outer join SC on (Course.Cno = SC.Cno);
（2）查询所有学生信息，所有课程信息及其选课信息，包含未选修课程的学生及未被学生选修的课程。
11.5. 实验五 视图的创建与使用
11.5.1. 实验目的
（1）理解视图的概念。
（2）掌握创建视图、测试、加密视图的方法。
（3）掌握更改视图的方法。
（4）掌握用视图管理数据的方法。
11.5.2. 实验内容
1．创建视图
（1）创建一个名为stuview1的水平视图，从Student_info数据库的student表中查询出所有男学生的资料。并在创建视图时使用with check option。
（2）创建一个名为stuview2的投影视图，从数据库Student_info的Course表中查询学分大于3的所有课程的课程号、课程名、总学时，并在创建时对该视图加密。
（3）创建一个名为stuview3的视图，能检索出“051”班所有女生的学号、课程号及相应的成绩。
（4）创建一个名为stuview4的视图，能检索出每位选课学生的学号、姓名、总成绩。
2．查询视图的创建信息及视图中的数据
（1）查看视图stuview1的创建信息。
方法1：通过系统存储过程sp_help查看。
方法2：通过查询表sysobjects。
（2）查看视图的定义脚本。
方法1：通过系统存储过程sp_helptext。
方法2：通过查询表sysobjects和表syscomments。
（提示：视图的名称保存在表sysobjects的name列，定义脚本保存在表syscomments的text列）。
（3）查看加密视图stuview3的定义脚本。
3．修改视图的定义
修改视图stuview2，使其从数据库Student_info的Course表中查询总学时大于60的所有课程的课程号、课程名、学分。
4．视图的更名与删除
（1）将视图stuview4更名为stuv4。
（2）将视图stuv4删除。
5．管理视图中的数据
（1）从视图stuview1查询出班级为“051”、姓名为“张虹”的资料。
（2）向视图stuview1中插入一行数据。学号：20110005，姓名：许华，班级：054，性别：男，家庭住址：南京，入学时间：2011/09/01，出生年月：1983/01/09。
原Student表中的内容有何变化？
思考：如向视图stuview1中插入一行数据。学号：20110006，姓名：赵静，班级：054，性别：女，家庭住址：南京，入学时间：2011/09/01，出生年月：1983/11/09。会出现什么样的结果？原Student表中的内容有何变化？
（3）修改视图stuview1中的数据。
将stuview1中054班、姓名为“许华”同学的家庭地址改为“扬州市”。
原Student表中的内容有何变化？
（4）删除视图stuview1中班级为054、姓名为“许华”的同学的记录。
原Student表中的内容有何变化？
11.6. 实验六 存储过程
11.6.1. 实验目的
（1）掌握T-SQL流控制语句。
（2）掌握创建存储过程的方法。
（3）掌握存储过程的执行方法。
（4）掌握存储过程的管理和维护。
11.6.2. 实验内容
1．创建简单存储过程
创建一个名为stu_pr的存储过程，该存储过程能查询出051班学生的所有资料，包括学生的基本信息、学生的选课信息（含未选课同学的信息）。要求在创建存储过程前判断该存储过程是否已创建，若已创建则先删除，并给出“已删除！”信息，否则就给出“不存在，可创建！”的信息。
2．创建带参数的存储过程
（1）创建一个名为stu_proc1的存储过程，查询某系、某姓名的学生的学号、姓名、年龄、选修课程名、成绩。系名和姓名在调用该存储过程时输入，其默认值分别为“%” 与“林%”。执行该存储过程，用多种参数加以测试。
（2）创建一个名为student_sc的存储过程，可查询出某学号段的同学的学号、姓名、总成绩。（学号起始号与终止号在调用时输入，可设默认值。）执行该存储过程。
3．创建带输出参数的存储过程
（1）创建一个名为course_average的存储过程，可查询某门课程考试的平均成绩。总成绩可以输出，以便进一步调用。
（2）创建一执行该存储过程的批处理，要求当平均成绩小于60时，显示信息为：“XX课程的平均成绩为：XX，其平均分未达60分”。超过60时，显示信息为：“XX课程的平均成绩为：XX”。
4．创建带重编译及加密选项的存储过程
创建一个名为update_sc、并带重编译及加密选项的存储过程，可更新指定学号、指定课程号的学生的课程成绩。（学号、课程号在调用时输入）
5．使用T-SQL语句管理和维护存储过程
（1）使用sp_helptext查看存储过程student_sc的定义脚本。
（2）使用select语句查看student_sc存储过程的定义脚本。
（3）将存储过程stu_pr改为查询学号为2011001的学生的详细资料。
（4）删除存储过程stu_pr。
6．使用SQL Server Management Studio管理存储过程
（1）在SQL Server Management Studio中重新创建刚删除的存储过程stu_pr。
（2）查看存储过程stu_pr，并将该过程修改为查询051班女生的所有资料。
（3）删除存储过程stu_pr。
11.7. 实验七 触发器
11.7.1. 实验目的
（1）理解触发器的用途、类型和工作原理。
（2）掌握利用T-SQL语句创建和维护触发器的方法。
（3）掌握利用SQL Server Management Studio创建、维护触发器的方法。
11.7.2. 实验内容
1．创建AFTER触发器
（1）创建一个在插入时触发的触发器sc_insert，当向SC表插入数据时，须确保插入的学号已在Student表中存在，并且还须确保插入的课程号在Course表中存在；若不存在，则给出相应的提示信息，并取消插入操作，提示信息要求指明插入信息是学号不满足条件还是课程号不满足条件。（注：Student表与SC表的外键约束要先取消。）
（2）为Course表创建一个触发器Course_del，当删除了Course表中的一条课程信息 时，同时将表SC中相应的学生选课记录也删除。
（3）在Course表中添加一个平均成绩avg_grade字段（记录每门课程的平均成绩），创建一个触发器grade_modify，当SC表中的某学生的成绩发生变化时，则Course表中的平均成绩也能及时相应发生改变。
（4）测试上述三个触发器。
2．创建INSTEAD OF 触发器
（1）创建一视图student_view，包含学号、姓名、课程号、课程名、成绩等属性，在student_view上创建一个触发器grade_modify，当对student_view中的学生的成绩进行修改时，实际修改的是SC中的相应记录。
（2）在Student表中插入一个getcredit字段（记录学生所获学分的情况），创建一个触发器ins_credit，当更改（注：含插入时）SC表中的学生成绩时，如果新成绩大于等于60分，则该生可获得这门课的学分，如果新成绩小于60分，则该生未能获得这门课的学分。
（3）测试上述两个触发器。
3．使用T-SQL语句管理和维护触发器
（1）用系统存储过程sp_helptrigger查看触发器grade_modify的相关信息。
（2）使用sp_helptext查看触发器grade_modify中的定义内容。
（3）使用select语句查看触发器grade_modify的定义内容。
（4）用系统存储过程sp_depends查看触发器grade_modify的相关性（即该触发器涉及哪些基本表）。
（5）将sc_insert触发器改为instead of 触发器，实现的功能不变。
（6）将触发器sc_insert删除。
4．使用SQL Server Management Studio管理触发器
（1）在SQL Server Management Studio中重新创建刚删除的触发器sc_insert。
（2）查看触发器sc_insert 的内容。
（3）删除触发器sc_insert。
11.8. 实验八 实现数据完整性
11.8.1. 实验目的
（1）了解实现数据完整性的概念及实施数据完整性的重要性。
（2）掌握数据完整性的分类。
（3）掌握完整性约束的添加、删除方法。
（4）掌握默认值的创建、实施与删除方法。
（5）掌握规则的创建、实施与删除方法。
（6）掌握级联删除、级联修改方法。
11.8.2. 实验内容
1．完整性约束的添加、删除
（1）使用SQL Server Management Studio实施约束。
a．为表Student的Birth字段创建检查约束，使输入的生日日期小于系统日期。
b．为表Student的Sdept字段，设置默认值约束，默认值取’计算机系’。
c．为Student表的Sname字段添加唯一性约束。
d．为SC表的Sno,Cno字段设置外键约束，约束名自己取，并允许级联删除与级联更新。若已存在外键约束，请先删除。
（2）使用Transact-SQL语句实施约束。
a．为student表的Sno字段添加一个check约束，使学号满足如下条件：学号前四位为2011，学号后四位为数字字符。
b．为student表中的Birth字段添加一个约束，规定生日应小于入学时间。
c．禁用（a）中实施的Check约束。
d．重新启用Check约束。
e．删除（a）所设置的check约束。
f．将Student表中的Classno字段设置为允许空。
g．为SC表中的Sno，Cno添加外键约束，约束名自取；并允许级联删除与级联更新。若已存在外键约束，请先删除。
h．为Course表中的Cname字段添加唯一性约束。
2．默认值的实施
（1）通过SQL Server Management Studio实现。
a．为数据库Student_info创建一个默认的邮政编码，名称自取，值为：200093。
b．将该默认值绑定到Student表中的Postcode列。
c．将Postcode列上的绑定解除，并删除该默认值。
（2）用Transact-SQL语句重做（1）中的（a）、（b）、（c）。
3．规则的实施
（1）通过SQL Server Management Studio实现。
a．为数据库Student_info创建一个关于性别的取值规则，规则名自取，要求字段的取值仅能为’男’或’女’。
b．将该规则绑定到Student表的Sex字段上。
c．解除Student表的sex列上的绑定，并删除该规则。
（2）使用T-SQL重做（1）中的（a）、（b）、（c）。
11.9. 实验九 索引及数据库安全
11.9.1. 实验目的
（1）理解索引的概念与类型。
（2）掌握使用SQL Server Management Studio创建与维护索引的方法。
（3）掌握T-SQL语句创建与维护索引的方法。
（4）掌握SQL Server下的数据库安全管理机制。
11.9.2. 实验内容
请分别通过SQL Server Management Studio和T-SQL语句完成该实验。
1．索引
（1）为Student表创建一个以Sno为索引关键字的唯一聚簇索引，索引名为sno_index。若索引已存在，请先删除。
（2）为Student表创建以sname、sex为索引关键字的非聚簇索引，对Sname以升序排列，Sex以降序排列，索引名为ss_index。
（3）将索引ss_index删除。
（4）针对下列4条select语句，在查询分析器中查看这些语句的预执行计划，分析预执行计划的不同点及原因。
a．Select * from student。
b．Select * from student where sno=‘20110001’。
c．Select * from student where sname like ‘张%’ and sex=‘女’。
d．Select * from student where Classno=‘051’。
2．数据库安全
（1）注册一个“登录”（loginin），登录名为自己的学号，并将该登录加入服务器角色“system administrators”。
（2）注册一个“登录”（loginin），登录名为自己的姓名，该登录不属于任何服务器 角色。
（3）在数据库Student_info下创建一个用户，用户名为自己的学号，并将它和登录名为自己的学号的登录连在一起，察看该用户属于哪个数据库角色，对数据库对象有哪些操作权限。
（4）在数据库Student_info下创建一个用户，用户名为自己的姓名，并将它和登录名为自己的姓名的登录连在一起，查看该用户属于哪个数据库角色；编辑修改该用户属性，并为该用户分配数据库中各对象的操作权限：
a．对Student表拥有全部权限。
b．对Course只有select 权限。
c．对SC表的Sno、Cno列具有select权限，对grade列没有任何权限。
（5）创建一个自定义角色“学生”，并将以自己姓名命名的用户添加为其成员。
（6）断开原来的链接，用学号重新登录，进入Student_info数据库，测试用学号登录后，是否拥有对数据库的全部操作权限（注意：该登录属于system administrators组）。
（7）断开原来的链接，用姓名登录，进入学生成绩数据库，测试用姓名登录后，拥有对数据库的哪些操作权限。
mysql, 有各种约束
mysql有许多存储引擎, 有的不支持外键,
11.10. 期中测试
2、利用查询分析器完成对上次实验建立的“学生管理”数据库的各项查询，在查询分析器中输入查询语句，并进行调试，给出运行结果。
Q0. 查询全体学生的学号、姓名，要求按学号进行升序排列。
select SNO, SNAMEfrom Sorder by SNO;
Q1. 查询所有课程信息，要求列的顺序与Course表中的列序一样。
select *from C;
Q2. 查询SC表中所有学生的学号，消除结果集中的重复行。
select distinct SNOfrom SC;
Q3. 查询选修了c03号课程且成绩在70分以上的学生学号与成绩。
select SNO, GRADEfrom SCwhere CNO = 'c03' and Grade > 70;
Q4. 查询c03号课程成绩在70~80之间的学生学号。
1, 查询选择了c03号课程的学生学号
2, 在过滤出来 成绩在70~80之间的学生学号
分析:
课程成绩, 学生学号, 都在SC表中有
可以考虑使用嵌套查询(子查询), 或者先建立视图, 再将视图作为 源, 从视图中查找
select SNOfrom SCwhere SNO exists ( select * from SC where GRADE between 70 and 80);
下面这个, 有错, 查询不到
select SNOfrom SCwhere CNO = 'c03' and Grade between 70 and 80;
Q5. 查询“计算机系”、“信管系”学生的全部信息。
select *from Swhere DEPA = '计算机系' or DEPA = '信息系';
Q6. 查询名字中含有“王”字的学生信息。
select *from Swhere SNAME like '%王%';
Q7. 查询课程名中不含“数据”字样的课程信息。
select *from Cwhere CNAME not like '%数据%';
Q8. 查询c03号课程成绩加上10分后的学生学号和成绩，要求给出临时标题。
select
Q9. 查询信管系学生的最大年龄和最小年龄。
select max(AGE), min(AGE)from Swhere DEPA = '信息系';
Q10.查询各个课程号及相应的选课人数。
Tips: 使用到聚合函数count()
select CNOfrom Cgroup by CNO;
Q11.查询选修了2门以上课程的学生学号。
Tips: 首先计算出每人的选修课程数
再利用 限制条件 >= 2
select SNOfrom SCgroup by SNO;
Q12.查询每个学生及其选修课程的情况。
select
Q13.查询选修了课程c04且成绩在80分以上的所有学生的学号和姓名。
select SNO, SNAMEfrom SC, Swhere CNO = 'c04' and GRADE >= 80
Q14.用两种方法查询选修了课程名为“计算机网络”的学生学号和姓名。
Q15.查询没有选修课程c03的学生姓名。
Q16.查询计算机系的学生及年龄不大于20岁的学生。
12. studentDB
12.1. 收集数据
12.2. 创建数据库
create database studentDB;
12.3. 创建数据表
create table Student( Sno varchar(11) primary key, Sname varchar(20), Sex char(4), Classno varchar(20), --Sdept varchar(20));create table Course( Cno varchar(10) primary key, Cname varchar(30), CPno varchar(10), Chours int, Credit decimal(18, 1));create table SC( Sno varchar(11), Cno varchar(10), Grade decimal(18, 2), primary key(Sno, Cno), foreign key (Sno) references Student(Sno), foreign key (Cno) references Course(Cno));
insertinto Course(Cno, Cname, CPno, Chours, Credit)values('EN01', '大学英语I', '英语系', 64, 4),('DM01', '离散数学', '计算机系', 64, 4),('C01', 'C语言程序设计', '计算机系', 72, 4.5),('MC01', '思想道德修养和法律基础', '法学系', 48, 3),('MY01', '马克思主义基本原理', '法学系', 48, 3),('HM01', '高等数学AI', '数学系', 88, 5.5);
insertinto SC(Sno, Cno, Grade)values
12.4. 插入数据
insertinto Student()
12.5. 查询数据
查询数学成绩最高分的学生姓名, 学号, 成绩
查询自己的各科成绩
select *from Student, SCwhere Student.Sno = SC.Sno and Student.Sname = '张三';
查询自己的总分成绩
select Student.Sno, sum(Grade) 总成绩from Student, SCwhere Student.Sno = SC.Sno and Student.Sname = '张三'group by Student.Sno;
查询自己的最高分数
select Student.Sno, max(Grade) 最高分数from Student, SCwhere Student.Sno = SC.Sno and Student.Sname = '张三'group by Student.Sno;
查询自己的最低分数
select Student.Sno, min(Grade) 最低分数from Student, SCwhere Student.Sno = SC.Sno and Student.Sname = '张三'group by Student.Sno;
查询所有学生的总成绩, 按降序排列
select Student.Sno, Student.Sname, sum(Grade) SGradefrom Student, SCwhere Student.Sno = SC.Snogroup by Student.Sno, Student.Snameorder by SGrade desc;
越来越体会到, 学习数据库, 最重要的是数据, 没有数据, 你要练习什么啊
有一些真实的数据, 练习起来, 会体会很深刻
1719
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
