你好赵伟

这个屌丝很懒，什么也没留下！

热门标签

迎娶富婆攻略_数据库原理在文件管理方式中可以做到允许某个人查询和修改数据

作者：你好赵伟 | 2024-06-12 21:03:04

踩

数据库原理在文件管理方式中可以做到允许某个人查询和修改数据

第一章数据库概述

1.1一些基本概念

数据是数据库中存储的基本对象
文本、图形、图象、音频、视频、、商品销售情况等都是数据
可以将数据定义为：数据是描述事物的符号记录
数据有多种表现形式，可以经过数字化后保存在计算机中

 数据
- 数据需经过解释才能明确其表达的含义
- 数据和解释不可分
  - 数据和解释是对数据的说明
  - 数据的含义称为数据的语义
数据库
- 是存放数据的仓库
- 永久存储在计算机存储设备上
- 按一定的格式存储
- 是有组织的、可共享的大量数据的集合

1.2数据库管理技术的发展

数据库管理系统

Database Management System——DBMS
是一个专门用于实现对数据进行管理和维护的系统软件

数据库管理系统主要功能

数据库的建立与维护
数据定义
数据组织、存储和管理
数据操作
事务的管理和运行

数据库系统

一般由数据数据库管理系统（及相关实用工具）、应用程序、数据可管理员组成

1.3数据独立性

文件管理方式

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-65G5v1LY-1648904975205)(C:\Users\86187\Desktop\1647439250(1)].png)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zrjn9W1V-1648904975207)(C:\Users\86187\AppData\Roaming\Typora\typora-user-images\image-20220316220322078.png)]
文件管理系统的缺点
- 编写应用程序不方便
  - 应用程序编写者必须清楚的了解所有文件的逻辑及物理结构
    - 如文件中包含多少个字段
    - 每个字段的数据类型
    - 采用何种逻辑结构和物理存储结构
  - 对文件的查询、修改等处理都必须在应用程序中编程实现
- 数据冗余不可避免
  - 数据冗余所带来的问题不仅仅是存储空间的浪费，更为严重的是造成了数据的不一致
  - 如：某学生所学的专业发生了变化，如果只在F1文件中进行修改，而忘记在F3中进行修改。则会造成同一名学生在两个文件中的“专业”不一样
- 应用程序依赖性
  - 应用程序对数据的操作依赖于存储数据的文件的结构
  - 文件和记录的结构通常是应用程序代码的一部分，如C程序的struct
  - 文件结构的每一次修改，都将导致应用程序的修改
- 不支持对文件的并发访问
  - 文件最初是作为程序的附属数据出现的，它一般不支持多个应用程序同时对同一个文件进行访问
  - 例如：某用户打开了一个excel文件，当第二个用户在的一个用户未关闭此文件前打开此文件时，只能以制度方式打开此文件，而不能对此文件进行修改
- 数据间联系弱
  - 文件与文件是彼此独立、毫不相关的，文件之间的联系必须通过程序来实现的
  - 比如F3文件中的学号、姓名等学生的基本信息必须是F1文件中已存在的（即选课学生必须是已存在的学生）
  - 数据之间的联系是世纪应用当中所要求，单文件本身不具备自定实现这些联系的功能
- 难以按用户视图表示数据
  - 不同用户关注的数据往往不同
  - 例如，对于学生基本信息，
    - 分配学生宿舍的部门可能只关心学生的学号、姓名、性别和班号
    - 教务部门可能关心的是学号、姓名、所在系、专业和班号
  - 需要为每个用户建立一个文件，这势必造成很多的数据冗余
- 无安全控制功能
  - 在文件管理方式中，很难控制某个人对文件能够进行的操作，如:
    - 只允许某个人查询和修改数据但不能删除数据
    - 或对文件中的某个或者某些字段不能修改
  - 在实际应用中，数据的安全性是非常重要且不可忽视的

数据库管理

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KqLgcNjx-1648904975208)(C:\Users\86187\AppData\Roaming\Typora\typora-user-images\image-20220316222942258.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-W0Q3lZET-1648904975208)(C:\Users\86187\AppData\Roaming\Typora\typora-user-images\image-20220316223013777.png)]
数据库管理的优点
- 相关联的数据的集合
  - 数据库中的数据不是孤立的，数据与数据之间是相互关联的。也就是说，在数据库中不仅要能够表示数据本身，还要能够表示数据本身，还要能够表示数据与数据的联系
- 较少的数据冗余
  - 数据库中的数据被统一管理，合理组织，任何对数据的操作都由数据可管理系统DBMS完成。
- 程序与数据相互独立
  - 数据独立性是指数据的组织和存储方法与应用程序互不依赖、彼此独立的特性
- 保证数据的安全、可靠
  - [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ht57mJjY-1648904975209)(C:\Users\86187\AppData\Roaming\Typora\typora-user-images\image-20220317211950736.png)]
- 最大限度的保证数据的正确性
  - 保证数据正确的特性在数据库中称为数据的完整性。
- 数据可以并发使用并能保证一致性
  - 在多个用户同时使用数据可时，能够保证不产生冲突和矛盾，保证数据的一致性和正确性。

数据独立性

指应用程序不会因数据的物理表示方式和访问技术的改变而改变
即应用程序不依赖于任何特定的物理表示方法和访问技术
包含两方面
- 逻辑独立性
  - 指当数据的存储位置或存储结构发生变化时，不影响应用程序的特性
- 物理独立性
  - 指当表达显示世界的信息内容发生变化时，如：增加信息、删除无用信息等
  - 不影响应用程序的特性

1.4数据库系统的组成

数据库系统的组成

数据库是数据的汇集，它以一定的组织形式保存在存储介质上
数据库管理系统是管理数据库的系统软件，它可以实现数据库系统的各种功能
应用程序专指以数据库数据为基础的程序
数据库管理员负责整个数据库系统的正常运行

硬件

由于数据库中的数据量一般都比较大，而且DBMS自身的规模也比较大（如：SQL SERVER 2005的完整安装大约需要700MB以上的硬盘空间和至少512MB以上的内存）
必须要有足够大的内训，来存放操作系统、数据库管理系统、数据缓冲区和应用程序，而且还要足够大的硬盘空间来存放数据库数据，最好还有足够的存放备份数据的磁带、硬盘或光盘

软件

数据库管理系统是整个数据库系统的核心，是建立、使用和维护数据库的系统软件
支持数据库管理系统运行的操作系统
具有数据库访问接口的高级语言及其编程环境，以便于开发应用程序
实用工具，一般是数据库厂商提供的随数据库管理系统软件一起发行。

人员

数据库管理员
系统分析人员
数据库设计人员
应用程序编程人员
最终用户

第二章数据库系统结构

2.1数据和数据模型

2.1.1数据与信息

描述事物的符号记录称为数据
将从数据中获得的有意义的内容称为信息
数据有一定的格式，这些格式的规定是数据的语法，而数据的含义是数据的予以
数据是信息存在的一种形式，只有通过解释或处理才能称为有用信息
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dW7DCFvf-1648904975210)(C:\Users\86187\AppData\Roaming\Typora\typora-user-images\image-20220317215956774.png)]

数据的静态特征

数据的基本结构
- 学生的学号、姓名、性别、出生日期
数据间的联系
- 学生选课中的学号与学生基本信息中的学号
数据取值范围约束
- 考试成绩在0—100分之间

动态特征

指对数据可以进行d的操作以及操作规则
对数据库数据的操作主要有
- 查询数据
- 更改数据：插入、删除和更新
一般将对数据的静态特征和动态特征的描述称为数据模型三要素

2.12数据模型

数据模型即要面向现实世界，又要面向机械世界，因此需满足三个要求：
- 能够真实模拟显示世界
- 容易被人们理解
- 能够方便的在计算机上实现
数据模型实际上是模型化数据和信息的工具。根基模型的应用的不同目的，可以将模型分成两大类：
- 概念层数据模型（概念模型），从数据的玉莹视角来城区模型，是按用户的管带你来对数据和信息进行建模
- 组织层数据模型（组织模型），从数据的组织层次来描述数据

2.2概念层数据模型

从数据的予以视角来抽取现实世界中有价值的数据并按用户的观点对数据进行建模
主要用在数据的设计阶段
与具体的数据库管理系统无关
与具体的实现方式无关
从数据的组织方式来描述数据。主要有：
- 层次模型
- 网状模型
- 关系，偶性
- 对象—关系模型
是从计算机系统的管带你对数据进行建模
与所使用的数据库管理系统有关
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9waueCQB-1648904975211)(C:\Users\86187\AppData\Roaming\Typora\typora-user-images\image-20220317221512754.png)]
概念层次模型实际上是现实世界到机械世界的一个中间层次
概念层次模型：抽象显示系统中有应用价值的元素机器关联，反映现实系统中有应用价值的信息结构，不依赖于数据的组织结构
概念模型用于信息世界的建模，是现实世界到信息世界的第一层抽象
是数据库设计人员和用户之间进行交流的工具
是面向用户、面向现实世界的数据模型，是与DBMS无关
常用的概念模型：实体—联系模型、语义对象模型

实体—联系模型

实体
- 具有公共性质的可相互区分的现实世界对象的集合
- 可以是具体的事物，也可以是抽象的概念或联系
- 具体的事物：学生、课程、职工
- 抽象的联系：学生选课
- 表达方式：矩形框
联系
- 联系是数据之间的关联集合，是客观存在的语义链
- 实体内部的联系：一个实体内属性之间的联系
  - 职工实体内部的职工号和此职工的部门经理号
- 实体之间的联系：不同实体之间的联系。
  - 课程实体和学生实体之间存在选课联系
- 表达方式：菱形
  
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lxO9CEHx-1648904975211)(C:\Users\86187\AppData\Roaming\Typora\typora-user-images\image-20220319194309532.png)]
- 联系的种类：一对一、一对多、多对多
  
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4I5juSI4-1648904975212)(C:\Users\86187\AppData\Roaming\Typora\typora-user-images\image-20220319194452883.png)]
属性：实体所具有的特征或性质：身高、年龄、性别…

2.3组织层数模型

2.3.1关系数据模型的数据结构

吧数据看作成二维表中的元素，而这个二维表就是关系
用关系（表格数据）表示实体和实体之间的联系的模型称为关系数据模型
实例：

学号姓名性别年龄所在系
9512101 李勇男 19 计算机系
9512102 刘晨男 20 计算机系
9512103 王敏女 20 计算机系
9521101 张立男 22 信息系
9521102 吴宾女 21 信息系
一些基本术语
- 关系：就是二维表
  - 性质：每一列都是不可再分的基本属性
    
    行列次序并不重要
- 元组：表中的每一行乘坐是一个元组，它相当于一个记录值
- 属性：表中的每一列是一个属性值的集合，类可以明明，称为属性名
- 主码：表中用于唯一的确定一个元组的一个属性或最小的属性组
- 域：属性的取值范围。如性别的域为（‘男’，‘女’）
- 关系模式：二维表的结构称为关系模式，即，关系模式是二维表的表框架或表头结构
  - 一般表示为：关系名（属性1，属性2…属性n）
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2NSaMajz-1648904975212)(C:\Users\86187\AppData\Roaming\Typora\typora-user-images\image-20220319200612336.png)]

学号	姓名	性别	年龄	所在系
9512101	李勇	男	19	计算机系
9512102	刘晨	男	20	计算机系
9512103	王敏	女	20	计算机系
9521101	张立	男	22	信息系
9521102	吴宾	女	21	信息系

2.3.2关系数据模型的数据操作

关系模式的操作对象是集合（也就是关系）
非关系数据可系统中典型操作是一次一行或一次一个记录
因此，集合处理能力是关系系统区别于其他系统的一个重要特征
关系数据模型的数据操作主要包括四种：增删改查

2.3.3关系数据模型的数据完整性约束

实体完整性
- 主码可识别且唯一
参照完整性
- 指多个实体之间的关联关系
  - 一般用外码实现：取本表（子表）属性之一的外表主码
  - 定义外码与主码之间的引用规则
  - 对于外码要求：
    - 或者值为空
    - 或者等于其所对应的关系中的某个元组的主码值
用户定义的完整性
- 按应用语义、属性数据有：
  - 类型与长度限制：方便计算机操作
  - 取值范围限制：防止属性值与应用语义矛盾

2.4数据库系统的结构

2.4.1三级模式结构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PLlkiziu-1648904975213)(C:\Users\86187\AppData\Roaming\Typora\typora-user-images\image-20220319201925990.png)]
外模式
- 也称用户模式、子模式
- 用户对现实系统中感兴趣整体的局部数据结构的描述
- 是DB整体数据结构的子集或局部重构
- 是外模式定义语言表达
- 是保证数据库安全的一个措施
概念模式
- 也称逻辑模式或模式，是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据描述
- 是数据库系统结构的中间层
- 用模式定义语言表达。例，对关系数据库，定义：表头结构、属性取值范围
内模式
- 也称存储模式
- 数据的内部存储结构描述（数据的组织与存储）。
- 用内模式定义语言描述，例，对RDB,定义数据文件的位置、索引的依据等

2.4.2模式映像与数据独立性

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-finDmj2G-1648904975214)(C:\Users\86187\AppData\Roaming\Typora\typora-user-images\image-20220319202809257.png)]

模式间的影响（DBMS基本功能之一）

维持DB数据与DB应用程序相互独立，从而使DB应用程序不随DB数据的逻辑或存储结构的变动而变动

模式/内模式映像

当存储结构改变时，可（由DBA）用内模式定义语句，调整内模式定义，从而保持模式不变

数据与程序物理独立（存储独立）

外模式/模式映像

当模式改变时，可用外模式/模式定义语句，调整外模式/模式映像定义，从而使外模式不变

数据和程序逻辑独立（概念独立）

2.5数据库管理系统

DBMS是处理数据库访问的系统软件
处理过程：
- 用户使用数据库语言发出一个访问请求；
- DBMS接受请求并分析
- DBMS检查用户外模式、相应的外模式/概念模式间的映像、概念模式、概念模式/内模式间的映像和存储结构定义
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CXwDnEZ9-1648904975215)(C:\Users\86187\AppData\Roaming\Typora\typora-user-images\image-20220319203854125.png)]
数据库管理系统的功能
- 数据定义
- 数据操作
- 优化和执行
- 数据安全和完整性
- 数据恢复和并发
- 数据字典
- 性能

第三章SQL语言基础及数据定义功能

3.1基本概念

SQL功能	命令动词
数据查询	select
数据定义	create、drop、alter
数据操纵	insert、update、delete
数据控制	grate、revoke

3.2 SQL的数据类型

数值型

准确型

整数

类型字节数
bigint 8字节
int 4字节
smallint 2字节
tinyint 1字节，0-255整数
bit 1位，存储1或0
小数

numeric（p,q）或decimal(p,q),

其中：p为数字位长度，q为小数位长度
近似型

float：8字节

real:4字节

类型	字节数
bigint	8字节
int	4字节
smallint	2字节
tinyint	1字节，0-255整数
bit	1位，存储1或0

字符串型

普通编码

char(n)：定长存储，n<=8000
varchar(n)：不定长存储（按实际），长度不超过n,n<=8000（注：n为字符个数）
text：存储大于8000字节的文本

统一字符编码

nchar(n)：定长存储，n<=4000
nvchar（n）：不定长存储，长度最大不超过n,n<=4000
ntext：存储大于8000字节的文本

特点：每个字符占两个字节

二进制字符

binary(n)：固定长度，n<=8000
varbinary(n)：可变长度，n<=8000

注：n为二进制数据的字节数
image：大容量、可变长二进制字符数据，可用于存储文件

日期时间型

datetime：8字节，年月日时分秒毫秒
smalldatetime：4字节，年月日时分

3.3 数据定义功能

3.3.1基本表的定义和删除

1.定义基本表

create table<表名>
（<列名><数据类型>[列级完整性约束定义]
{，<列名><数据类型>
[列级完整性约束定义]...}
[，表级完整性约束定义]
）
1
2
3
4
5
6

列级完整性约束定义处可定义的约束
- not null限制列取值非空
- default 给定列的默认值
- unique限制列取值不重
- check限制列的取值范围
- primary key制定本列为主码
- foreign key 定义本列为引用其他表的外码
  
  使用形式：[foreign key(<外码列名>)]
  
  references<外表名>(<外表列名>)
  
  几点说明：
约束定义
- 1.列取值非空约束
  
  <列名><类型> not null
  
  ex: sname char(10) not null
- 2.表主码约束
  - 在定义列时定义主码（仅用于单列主码）
    
    列定义 primary key
    
    ex: sno char(7) primary key
  - 在定义完列后定义主码（用于单/多列主码）
    
    primary key(<列名序列>)
    
    ex: primary key(sno)
    
    primary key(sno,cno)
- 3.外码引用约束
  
  指明本表外码列引用的表及表中的主码列
  
  [foreign key(<本表列名>)]
  
  references<外表名>(<外表主码列名>)
  
  ex: foreign key(sno) references 学生表(sno)
创建表

2.删除表

drop table <表名>{[,<表名>]…}

ex:删除test表的语句为：

drop table test
删除表时会将与表有关的所有对象一起删掉，包括表中的数据

3.3.2修改表结构

注：在增删改列定义时，可以使用 alter table语句实现

alter table<表名>
{alter column <列名><新数据类型>--修改列定义
| add<列名><数据类型><约束>--增加新列
|drop column<列名>--删除列
|add [constraint<约束名>]约束定义--添加约束
|drop [constraint]<约束名>}--删除约束
1
2
3
4
5
6

例:为SC表添加“修课类别”列，此列的定义为：XKLB char(4)

alter table SC

add XKLB char(4) null

将新添的XKLB的类型改为char(6)

alter table SC

alter column XKLB char(6)

删除course表的period列

alter table course

drop column period

3.4 数据完整性

3.4.1完整性约束条件的作用对象

列级约束

元组约束

关系约束

3.4.2实现数据完整性

声明完整性
- 在表定义时声明
  - 使用约束、缺省值（default）等
- 由sql server自动加以保证
过程完整性
- 在客户端或服务器端用编程语言或工具来实现
  - 在server端用触发器（trigger）来实现

实现约束

1. primary key 约束

保证实体完整性
每个表有且只有一个primary key 约束
格式

alter table 表名

add [constraint 约束名]

primary key (列名[,…n])
例

2. unique 约束

确保在非主键列中不输入重复值
应用在客观具有唯一性质的列上，如身份证号、社会保险号等
格式

alter table 表名

add [constraint 约束名]

unique(<列名>[,…n])
例

3. foreign key 约束

用于建立和加强两个表数据之间的连接的一列或多列
格式

alter table 表名

add [constraint 约束名]

foreign key(<列名>)

references 引用表名(<列名>)
例

4. default 约束

当向表中插入数据时，如果没有定义为定义了default的列提供值，则此列使用默认值
一个default只能约束一列
格式

alter table 表名

add [constraint 约束名]

default 默认值 for 列名
例

5. check 约束

通过限制输入到列中的值来强制域的完整性
可定义同表多列之间的约束关系
格式

alter table 表名

add [constraint 约束名]

check （逻辑表达式）
例

综合起来

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gOZdDp2F-1648904975215)(C:\Users\86187\AppData\Roaming\Typora\typora-user-images\image-20220322210300864.png)]

第四章数据操作

4.1 数据查询功能

4.1.1 查询语句的基本结构

select <目标列名序列>  --需要哪些列
	from <数据源>     --来自哪些表
	[where <检索条件表达式>] --根据什么条件
	[group by <分组依据列>]
	[having <组提取条件>]
	[order by <排序依据列>]
1
2
3
4
5
6

注：书写规则

4.1.2 简单查询

1.选择表中若干列

--擦汗寻表中用户感兴趣的部分属性列
--查询全体学生的学号和姓名
select sno,sname from student

--查询全体学生的记录
select sno,sname,ssex,sage,sdept from student
--等价于
select *from student

--查询经过计算的列
--查询全体学生的姓名及出生年份并命名“出生年份”
select sname,'出生年份：'，2021-sage
from student

--改变列标题
--语法：
	列名 | 表达式[as]列标题
	或
	列标题=列名 | 表达式
例：
select sname as 姓名,'year of birth'出生年份,2020-sage 年份 
from student
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

2. 选择表中若干元组

--消除取值相同的行
--查询选秀了课程的学生的学号
select distinct sno from sc
1
2
3

查询满足条件的元组

查询条件	谓词
比较运算符	=,>,>=,<,<=,!=
确定范围	(not) between…and
确定集合	(not) in
字符匹配	(not) like
空值	is (not) null
多重条件	and,or

--比较大小
--查询计算机系全体学生的姓名
select sname form student
where sdept='计算机系'

--查询年龄在20岁以下的学生的姓名及年龄
select sname,sage form student
where sage<20
1
2
3
4
5
6
7
8

--确定范围
--查询年龄才20至23岁之间的学生的姓名、所在系和年龄
select sname,sdept,sage from student
where sage betwewn 20 and 23
1
2
3
4

--确定集合
--查询信息系、数学系和计算机系学生的姓名和性别
select sname,ssex from student
where sdept in ('信息系','数学系','计算机系')
1
2
3
4

--字符匹配
--查询姓‘张’的学生的详细信息
select *from student
where sname like'张%'

--查询姓‘张’、‘李’、‘刘’的学生的详细信息
select *from student
where sname like'[张李刘]%'

--擦汗寻名字中第2个字为‘小’或‘大’的学生的姓名和学号
select sname,sno from student
where sname like'_[小大]%'

--查询所有不姓王也不姓张的学生的姓名
select sname from student
where sname not like'[王张]%'
或者
select sname from student 
where sname like'[^王张]%'
或者
select sname from student 
where sname not like'王%'
and sname not like'张%'

--查询姓王且名字是2个字的学生姓名
select sname from student
where sname like '王_'

--查询姓王且名字是3个字的学生姓名
select sname from student
where rtrim(sname) like '王__'//尾随空格处理
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

使用like运算符
一般形式为：列名 [not] like <字符串>
匹配串中可包含如下四种通配符：
- _ 匹配一个字符
- % 匹配0个或多个字符
- [] 匹配[]中的任意一个字符，对连续字母的匹配，例如匹配[abcd]可简写为[a-d]
- [^] 不匹配[]中的任意一个字符

--涉及空值查询
--查询没有考试成绩的学生的学号和相应的课程号
select sno,cno from sc
where grade is null

--查询所有有考试成绩的学生的学号和课程号
select sno,cno from sc
where grade is not null
1
2
3
4
5
6
7
8

--多重条件查询
--查询计算机系年龄在20岁以下的学生姓名
select sname fron student
where sdept='计算机系' and sage<20

--查询计算机系和信息系年龄大于等于20岁的学生姓名、所在系和年龄
select sname,sdept,sage from student
where sdepT IN ('计算机系'，'信息系')
and sage>=20
1
2
3
4
5
6
7
8
9

3.对查询结果进行排序

排序句为：

order by 列名 [asc|desc] [,<列名>…]

说明：按列名称进行升序（asc）或降序（desc）排

--按年龄进行升序
select *from student 
order by sage

--查询选修了‘c02’号课程的学生的学号及其成绩，查询结果按成绩降序排列
select sno,grade from sc
where cno='c02'
order by grade desc

--查询全体学生的信息，查询结果按所在系的系名升序排列，统一系的学生按年龄降序排列
select *from student
order by sdept,sage desc
1
2
3
4
5
6
7
8
9
10
11
12

4.使用计算函数汇总数据

--sql提供的计算函数有
count(*)  --统计表中元组个数
count([distinct]<列名>)  --计算本列值的个数
sum([distinct]<列名>)  --计算本列值总和
ave([distinct]<列名>)  --计算本列平均值
max([distinct]<列名>)  --求本列最大值
min([dsitinct]<列名>)  --求本列最小值
注：除count(*)外，其他函数均忽略null值
1
2
3
4
5
6
7
8

--统计学生总人数
select count(*) from student

--统计选修了课程的学生的人数
select count(distinct sno)
from sc

--计算9512101号学生的考试成绩之和
select sum(drade) from sc
where sno='9512101'

--计算‘c01’号课程学生的考试平均成绩
select ave(grade) from sc
where cno='c01'

--查询选修了‘c01’号课的学生的最高分和最低分
select max(grade),min(grade)
from sc where cno='c01'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

注：计算函数不能出现在where语句中

5.对查询结果进行分组计算

语句：

group by <分组条件>

having <组过滤条件>

--1.使用group by
--统计每门课程的选课人数，列出课程号和人数
select cno 课程号，count(sno) 选课人数
from sc group by cno
//对查询结果按cno的值分组，所有具有相同cno值的元组为一组，然后再对每一组使用count计算，求得每组得学生人数

--查询美名学生得选课门数和平均成绩
celect sno 学号，count(*) 选课门数，ave(grade) 平均成绩
from sc
group by sno

--查询每个系得女生人数
select sdept,count(*)女生人数
from student
where ssex='女'
group by sdept

--统计每个系的男生人数和女生人数，男生最大年龄，女生最大年龄，结果按系名升序排列
select sdept,ssex,count(*) 人数，max(sage) 最大年龄
from student
group by sdept,ssex
order by sdept
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

--2.使用having
--having用于对分组的自身进行限制，有点像where子句，但它用于组而不是对单个记录
--查询修了3门以上课程的学生的学号
select sno from sc
group by sno
having count(*)>3

--查询修课门数等于或者大于4门的学生的平均成绩和选课门数
select sno,avg(grade) 平均成绩，count(*) 选课门数
from sc
group by sno
having count(*)>=4
1
2
3
4
5
6
7
8
9
10
11
12

执行顺序：where->group by->having

4.1.3 多链表查询

若一个查询同时涉及两个或谅额以上的表，则称之为连接查询
内连接、外连接和交叉连接
连接查询中用于连接两个表的条件称为连接条件或连接谓词
格式：

[<表名1.>] [<列名1>] <比较运算符>[<表名2.>] [<列名2>]

1.内连接

如果两个表的相关字段满足连接条件，则从这两个表中提取数据并组成新的记录

语法：

select…

from

表名 [inner] join 被连接表 on 连接条件

--查询计算机系学生的修课情况，要求列出学生的名字、所修课的课程号和成绩
select sname.cno,grade
from student join sc
on student.sno=sc.sno
where sdept='计算机系'

--查询信息系修了VB课程的学生的修课成绩，要求列出学生姓名、课程名和成绩
select sname,cname,grade
from student s join sc
on s.sno=sc.sno
join course c on c.cno=sc.cno
where sdept='信息系'
and cname='VB'

--查询所有修了VB课程的学生的修课情况，要求列出学生姓名和所在的系
select sname,sdept
from student s join sc
on s.sno=sc.sno
join course c on c.cno=sc.cno
where cname='VB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

自连接

特殊的内连接
仙湖连接的表物理上是同一个表
必须为两个表取别名，使之在逻辑上称为两个表

--查询与刘晨在同一个系学习的学生的姓名和所在系
select s2.sname,s2.sdept
from student s1 join student s2
on s1.sdept=s2.sdept
where s1.sname='刘晨' and s2.sname!='刘晨'
1
2
3
4
5

2.外连接

只限制一张表中的数据必须满足连接条件，而另一张表中的数据可以不满足连接条件
ansi方式的外连接的语法
- from 表1 left | right [outer]
  
  join 表2 on <连接条件>

theta方式的外连接的语法

左外连接

from 表1，表2 where[表1.]列名(+) = [表2.]列名

右外连接

from 表1，表2 where[表1.]列名 =[表2.]列名(+)

--查询学生的修课情况，包括修了课程的学生和没有修课的学生
select student.sno,sname,cno,grade
from student left outer join sc
on student.sno=sc.sno

--查询哪些课程没有人选，列出其课程名
select cname
from course c left outer join sc
on c.cno=sc.cno
where sc.cno is null
1
2
3
4
5
6
7
8
9
10

4.1.4 子查询

使用top限制结果集
- 作用：列出结果集中的前几行结果
- 格式：top n [percent] [with this]
  
  top n:查询结果的前n行
  
  top percent：查询结果的前n%行数据
  
  with this：包括并行的结果
在sql语言中，一个select-from-where语句中称为一个查询块
子查询是一个select查询，它嵌套在语句的where或having子句中，或者其他子查询中
子查询的select查询总用圆括号括起来
格式

列名 [not] in (子查询)

--查询与刘晨在同一个系得学生
select sno,sname,sdept
  from student
    where sdept in
	  (select sdept from student
		where sname='刘晨')
	  and sname!='刘晨' 
	  
--查询成绩为90分得学生得学号和姓名
select sno,sname from student
  where sno in
   (select sno from sc
    where grade>90)
1
2
3
4
5
6
7
8
9
10
11
12
13

4.2 数据更改功能

4.2.1 插入数据

插入单行记录的insert语句格式

insert into 表名（列表值）

values（表值）
功能

新增一个符合表结构的数据行，将值表数据按表中列定义顺序[或列名表顺序]赋给对应列名
值列表中的值与列名表中的列按位置顺序对应，它们的数据类型必须一致
如果<表名>后面没有指明列名，则新插入记录的值的顺序必须与表中列的定义顺序一致，且每一个列均有值（可以为空）

--例1.将新生记录（9521105，陈东，男，信息系，18岁）插入到student表中
insert into student
values('9521105','陈东'，'男',18,'信息系')

--例2.在sc表中插入一段新记录，成绩暂缺
insert into sc(sno,cno,xklb)
values('9521105','c01','必修')
实际插入：
('9521105','c01',null,'必修')
1
2
3
4
5
6
7
8
9

4.2.2 更改数据

用update语句实现
格式：

update 表名

set 列名=表达式[,…n]

[where 更新条件]

--无条件更新
--将所有学生的年龄加1
update student
	set sage = sage+1
1
2
3
4

--有条件更新
--1.基于本表条件更新
--将‘9512101’学生的年龄改为21岁
update student set sage=21
	where sno='9512101'
1
2
3
4
5

--基于其他表条件的更新
--将计算机系全体学生的成绩加5
（1）用子查询
update sc set grade=grade+5
where sno in
(select sno from student
where sdept='计算机系')
（2）用多链表
update sc set grade=grade+5
from sc join student on sc.sno=student.sno
where sdept='计算机系'
1
2
3
4
5
6
7
8
9
10
11

4.2.3 删除数据

用delete语句实现
格式：

delete from 表名

[where 删除条件]

--无条件删除
--删除所有学生的选课记录
delete from sc
1
2
3

--有条件删除
--（1）基于本表条件的删除
--删除所有不及格学生的选课记录
delete from sc
where grade<60

--（2）基于其他表条件的删除
--删除计算机系不及格学生的选课记录
（1）用子查询
delete from sc
where grade<60 and sno in(
select sno from student
where sdept='计算机系')
（2）用多链表
delete from sc
from sc join student on sc.cno=student.sno
where sdept='计算机系' and grade<60
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

4.3 查询语句扩展

4.3.1 将查询结果保存到新表中

格式

select 查询列表序列 into <新表名>

from 数据源

<新表名>：存放查询结果的表名
- 这个语句包含两个功能
- 根据查询语句产生的列名和类型创建一个新表
- 执行查询语句并将查询结果保存到新表中

--查询计算机系学生的姓名、选修的课程名和成绩，并将查询结果保存到永久表scg中
select sname,cname,grade
into scg
from student s join sc on s.sno=sc.sno
join course c on c.cno=sc.cno
where sdept='计算机系'

--统计每个系的学生人数，并将结果保存到永久表deptcnt中
select sdept,count(*) as 人数 into deptcnt
from student
group by sdept
1
2
3
4
5
6
7
8
9
10
11

4.3.2 case表达式

一种多分支表达式，它可以根据条件列表的值返回多个可能的结果表达式中的一个

case表达式可用在任何允许使用表达式的地方，但它不是一个完整的T-SQL语句，因此不能单独执行，只能作为一个可以单独执行的语句的一部分来使用

简单case表达式

case input_expression

when when_expression then result_expression

[…n]

[else else_result_expression]

end

--查询选了Java课程的学生的学号、姓名、所在系和成绩，并对所在系进行如下处理
 当所在系为”计算机系“时，在查询结果中显示”sc“
 当所在系为”信息系“时，在查询结果中显示”im“
 当所在系为”数学系“时，在查询结果中显示”com“
select s.sno 学号,sname 姓名,
case sdept
when '计算机系' then 'sc'
when '信息系' then 'im'
when '数学系' then 'com'
end as 所在系,grade 成绩
from student s join sc on s.sno=sc.sno
join course c on c.cno=sc.cno
where cname='java'
1
2
3
4
5
6
7
8
9
10
11
12
13

搜索case表达式

case

when boolean_expr then result_expr