数据库系统原理（三）

最新推荐文章于 2021-04-12 08:27:03 发布
最新推荐文章于 2021-04-12 08:27:03 发布
阅读量575 收藏 5
点赞数 2
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43633784/article/details/108830494
版权

SQL查询语言概览

在这里插入图片描述
目前支持的是SQL-92
标准符合度中有很多标准项，有多少标准项被满足就叫做这个SQL的标准符合度。
在这里插入图片描述
有的时候不是技术上的原因导致不能实现四个等级，而是出于标准中的某些功能可能影响系统的安全性等其他因素的考虑。

SQL语言的划分

在这里插入图片描述
DDL：提供定义关系模式、删除关系以及修改关系的命令
DML：提供从数据库中查询信息，以及增删改查
完整性：DDL包括定义完整性约束的命令，保存在数据库中的数据必须满足所定义的完整性约束，破坏完整性约束的更新是不允许的。
视图定义
事务控制
嵌入式SQL和动态SQL
授权：对关系和视图的访问权限的命令

SQL数据定义

在这里插入图片描述
DDL不仅能定义关系还能定义每个关系的信息：

  1. 每个关系的模式
  2. 每个属性的取值类型
  3. 完整性约束
  4. 每个关系维护的索引集合
  5. 每个关系的安全性和权限信息
  6. 每个关系在磁盘上的物理存储结构
基本类型

固有类型：

char(n):固定长度字符串，用户指定长度为n，如果没满会追加空格来使其达到相应长度。
varchar(n):可变长度字符串，用户指定最大长度为n，没有达到n不会追加空格。
//当比较两个长度不同的char型字符串时，会在较短的字符串后面追加空格使二者长度相同然后再进行比较；
//但是如果是一个char和一个varchar，不一定在varchar后面增添空格，依赖于具体系统，那么就有可能出现存储相同数据但是比较结果是false
//一般使用varchar
int:整数类型（和机器相关的整数类型的子集）
smallint:小整数类型
numeric(p,d):定点数，精度由用户指定。整个数共p位（算符号位），小数点右边有d位。
real,double precision:双精度浮点数
float(n):精度至少为n位的浮点数
null:每种类型都可以包含一个特殊值，null，可以申明属性不为空

日期，时间，时间戳：
在这里插入图片描述
这些都是标准的DBMS的基本类型，在实际不同的DBMS中有所区别。
比如在SQL Server中只有datetime一个数据类型

相关函数：标准化程度不高，不同DBMS的函数不一样，具体函数名称记不住使用DBMS的帮助文件。
在这里插入图片描述

基本模式定义
  1. create table
    在这里插入图片描述
    补充：少数完整性约束，SQL禁止任何破坏完整性约束的任何数据库更新
not null//该属性不允许空值
primary key(A1,A2,...,An)//括号中的内容构成关系的主码，主码属性必须非空且唯一，虽然主码是可选的，但是为每个关系选择一个主码更好
//指定之后会自动进行是否重复的判断，如果不满足主码的要求不允许插入
//插入数据的primary key中的元素的属性值是否和table中已有的元素的属性值相同，如果相同不允许插入
foreign key(A1,A2,...,An)references//除了要给出作为外键属性之外，还要给出在哪个具体的table中，引用哪张表属性的外键，任意元组在属性A1，A2等上的取值必须对应于关系中某元组主码属性上的取值
//插入的外键的值在被引用的值中是否存在
check(P)//P是谓词条件，提供一些完整性约束，那些完整性约束即可表示成一个谓词条件，当插入数据的时候如果不满足这个谓词条件则不能插入
super_key
candidate key

在这里插入图片描述
在SQL Server2000中，是可以使用空值的，check条件只要不是false都是可以的，不必非得是true

  1. drop table
    在这里插入图片描述
  2. alter table
    在这里插入图片描述
    增加了一个新的属性之后，那些原本存在的元祖的该属性为空
    alter中可以有add、drop、modify；
    比如有些属性不用不必要非得删除这些属性因为在删除的过程中可能造成一些误操作，比如这个属性在其他的table里是primary key，或者作为其他table的foreign key就会出现error
  3. insert into
  4. delete
delete from students;
//从students关系中删除所有元组
//其他格式的指令允许删除特定元组，但是delete from只能全删？？

drop 和 delete 的区别：

drop table t;//不仅删除r的所有元组，还删除r的模式，一旦被删除，只能create table重建r，否则没有元组可以插入
delete from t;//保留关系r，但是删除r中的所有元组

summary：

在这里插入图片描述

SQL查询的基本结构

在这里插入图片描述

select子句

from后面的关系要进行笛卡尔积，然后根据选择条件p进行选择，然后选择出来的属性在select后面的列表中进行投影。
在这里插入图片描述
在关系模型的形式化数学定义中，关系是一个集合，重复的关系不会出现在关系中，在实践中，去除重复相当费时，所以SQL允许关系以及SQL表达式中出现重复。
在这里插入图片描述

在这里插入图片描述

where子句

找出满足特定条件的元组
在这里插入图片描述
在这里插入图片描述

from子句

把table放在from后面就将两个table作笛卡尔积
在这里插入图片描述

3.4 附加的基本运算

3.4.1 更名运算

使用更名运算的原因：

  1. from子句的两个关系中可能存在同名属性，结果中就会出现重复的属性名
  2. 如果在select子句中使用算数表达式结果属性就没有名字
  3. 长的关系名替换成短的，方便查询
select T.name ,S.course_id
from instructor as T,teaches as S
where T.ID = S.ID
  1. 为了适用于需要比较同一个关系中的元组的情况
//找出工资比biology系每个老师工资都高的教师的名字
//T和S相当于instructor的两个拷贝，实际上是别名，用来进行类似的比较
select distinct T.name
from instructor as T,instruct as S
where T.salary > S.salary and S,dept_name = 'biology'

//使用as子句进行更名操作
old-name as new-name
as子句既可以出现在select子句中也可以出现在from子句中

被用来重命名关系的标识符在SQL标准中被称作相关名称/表别名/相关变量/元组变量。

3.4.2 字符串运算

  1. 字符串的表示，如果是普通的字符串使用单引号扩起来表示字符串，如果其中含有单引号，单引号前再添加一个单引号。标准SQL是大小写敏感的，但是到实际的DBMS可以根据实际情况进行修改
  2. 字符串函数集
    不同的DBMS上的字符串函数集是不同的，一般有串联（||），大小写转换（upper/lower），去掉字符串后面的空格（trim）等
  3. 模式匹配
    标准SQL使用like实现模式匹配（跟正则表达式差不多）
    使用两个特殊的字符表示模式：
    • % 匹配任意字串
    • _ 匹配任意一个字符

模式匹配大小写敏感，例：

select dept_name
from department 
where building like '%Waston%';
  1. 允许定义转义字符
    使用escape定义转义字符，例：
like 'ab\%cd%' escape '\';
//匹配所有ab%cd开头的字符串
  1. 允许使用not like比较运算符搜索不匹配项

3.4.3 select子句中的属性说明

*代表选择所有属性

select instructor.*
from instructor ,teaches
where instructor.ID = teaches.ID;

3.4.4 排列元组的显示次序

  1. 可以使用order by使查询结果按照排列顺序显示
  2. 默认升序，可以使用desc表示降序，使用asc表示升序
select *
from instructor
order by salary desc,name asc;
//首先按照salary降序排列，当salary相等的时候按照name升序排列

3.4.5 where子句谓词

  1. 使用between and选择在一个闭区间内的查询
select name
from instructor 
where salary between 9000 and 10000;
  1. 使用元组(v1,v2,v3...,vn)
    在SQL中可以使用元组之间的比较，要求比较的两个元组之间的任意一个分量都对应满足相应的关系。
select name ,course_id
from instructor
where (instructor.name , instructor.salary) = ('biology','9000');

3.5 集合运算

和数学中的集合运算一致

3.5.1 并运算

union操作自动去重，如果想要保留所有结果，使用union all

(select course_id
from section
where semester = 'Fall' and year = 2009;)
union//union all
(select course_id
from section
where semeter = 'Fall' and year = 2010;)

3.5.2 交运算

intersect操作自动去重，如果想要保留所有结果使用intersect all

(select course_id
from section
where semester = 'Fall' and year = 2009;)
intersect//intersect all
(select course_id
from section
where semeter = 'Fall' and year = 2010;)

3.5.3 差运算

更换同上

3.6 空值

  1. SQL将任何涉及到空值的运算结果都视为unknown
    （除了false和true之外的第三个逻辑值）
  2. 常用涉及unknown的逻辑运算
and
true and unknown = unknown
false and unknown = false
unknown and unknown = unknown

or 
true or unknown = true
false or unknown = unknown
unknown or unknown = unknown 

not 
not known = known
  1. 判断结果是否为unknown，使用is unknown / is not unknown
  2. 在使用select distinct是如果二者非空相等或者二者均为空值，则认为可以匹配上，eg('A',null)=('A',null)。但是select子句中的判断和正常的谓词判断结果不同，谓词null=null返回的结果不是true而是unknown
  3. 如果元组在所有属性上的取值相等，那么它们就被当作相同元组，即使某些值为空

3.7 聚集函数

聚集函数是以值的一个集合（多重集或者是集）为输入，返回单个值的函数

avg 平均值
min 最小值
max 最大值
sum 求和 
count 计数

⚠️忽略空值，avg和sum必须使用数字集

3.7.1 基本聚集

//计算平均值时不去重
//如下面计算工资平均，如果两个人的salary重复被去掉，工资总和减少，结果错误
select avg(salary) as avg_salary
from instructor
where dept_name = 'Comp.Sci';

//使用聚集函数之前需要去重
//因为每个老师无论教几门课都算作上了一次课，所以要去重进行计数
select count(distinct ID)
from teaches
where semester = 'Spring' and year = 2000;

//使用count计算关系中元组的个数
//不允许在count时使用distinct，但是可以在min和max中使用
//但是结果没有区别，也可以用all显示说明，但是all时默认的，所以写不写都可
select count(*)
from course;

3.7.2 分组聚集

将聚集函数作用到一组元组集上，group by子句中给出的一个或多个属性是用来构造分组的，在group by子句中的所有属性上取值相同的元组被分在一个组中。

//查询每个系在2010年春季学期讲授一门课程的教师人数
//把dept_name相同的算在一组中计算相关的聚集函数的值
//每个group中只输出一个元组
select dept_name ,count(distinct ID)as instr_count
from instructor natural join teaches
//在teaches中只是每个老师教授什么课程，但是没有相关院系信息
//所以要于instructor进行自然连接，找到对应的院系信息
where semeter = 'Spring' and year = 2000
group by dept_name;

⚠️没有出现在group by中的属性不能出现在select子句中，或者只能出现在select子句的聚集函数内，否则出现错误

select dept_name,ID,count(distinct ID)as instr_count
from instructor 
group by dept_name;
//这样的结果是错误的，因为每个老师都有唯一一个ID
//而使用dept_name作为分组属性的话，每个组中无法对ID的信息做一个整体的统计
//每个ID都可以作为一个组中的唯一进行输出，SQL不允许出现这种情况

3.7.3 having子句

  1. having子句出现的条件：
    对分组限定条件比对元组吸限定条件更有用，通常出现一些查询条件不是针对一个单个的元组而是针对使用group by划分好的分组上更有意义，having子句在产生分组之后才产生作用。
    ⚠️任何出现在having子句中，但是没有被聚集的属性必须出现在group by子句中，否则查询错误
//对于在2009年讲授的每个课程段，如果该课程段至少有两个学生选课，找出选修该课程段的所有学生的总学分
select course_id,semester,year,see_id,avg(tot_cred)
from takes natural join students 
where year  = 2009
group by course_id,semester,year,see_id
having count(students.ID)>2;
  1. 涉及到几个子句的执行顺序
    根据from子句计算出一个关系
    将where子句的谓词应用到from子句的结果上
    如果出现group by，则根据条件将通过上面两步的元组进行分组，如果没有group by则每个元组都是一个单独的分组
    如果出现having分组，则将其所带谓词应用在上一步group by形成的分组上，如果不满足所带的谓词的关系则舍弃
    select子句利用留存下来的分组，在其上进行相关聚集函数的操作得出最后的结果

3.7.4 对空值和布尔值的聚集

  1. null表示属性信息缺失，可以使用特殊关键字is null 或者is not null进行测试。
    如果使用到聚集函数中，一般集合中的空值被忽略，但是在count(*)计算中不忽略null。
  2. count(null)=0;其他所有聚集函数如果输入是空集则输出都是null

3.8 嵌套子查询

子查询是嵌套在另一个查询中的select-from-where表达式。子查询通常在where子句中，通常用于对集合的成员资格、集合的比较和集合的基数进行检查。

3.8.1 集合成员资格

  1. in/not in测试元组是否是集合中的元素（集合由一系列select子句构成）
select distinct course_id
from section
where semester='Fall' and year = 2009 and 
    course_id in (select distinct course_id
                  from section
                  where semester='Spring' and year = 2010;);
  1. 选出元组出现在另一个已知元组中使用，也可以用于枚举集合
select name 
from instructor
where instructor.name not in('Mazart','Peter');
  1. 不仅可以用于单元素的集合成员资格判断，还可以用于测试任意关系成员之间的集合成员关系的判断
//查找出不同的学生数，选修了ID为10101的教师所讲授的课程段
select count(distinct ID)//要求不同的学生，所以使用distinct去除重复
from takes
where(course_id,sec_id,semester,year)in (select course_id,sec_id,semester,year
        from teaches
        where teaches.ID=10101;);

3.8.2 集合的比较

之前使用的查询写法是：

select distinct T.name
from instructor as T,instructor as S
where T.salary > S.salary and S.dept_name = 'Biology';

另一种方式，不使用重命名：

select name
from instructor 
where salary > some( select salary 
                     from instructor
                     where dept_name = 'Biology');

常用比较形式
在这里插入图片描述
在这里插入图片描述

>some/all 至少大于其中的某一个;大于其中所有
<some/all
<=some/all
>=some/all
=some/all
<>some/all

=some <=> in
<>some !<=> not in 
=all !<=> in 
<>all <=> not in 

//找出平均工资最高的系
select dept_name
from instructor
group by dept_name//找出工资最高的系在主查询关系中也得根据dept_name进行分组
where avg(salary)>= all(select avg(salary)
                        from instructor 
                        group by dept_name;);

3.8.3 空关系测试

主要是用来判断一个集合是空集还是非空集合

  1. 可测试一个子查询的结果中是否存在元组。exsit结构在作为参数的子查询非空时返回true，not exsit查询结果集中是否不存在元组。
  2. 相关子查询：来自外层查询的一个相关名称可以用在where子句的子查询中。使用了来自外层查询相关名称的子查询被称作相关子查询。（作用域规则）
  3. 模拟集合包含操作（超集）
    关系A包含关系B：not exsit(B except A)
select S.ID,S.name
from student as S
where not exists ((select course_id
        		   from course 
        		   where dept_name ='Biology')//在Biology系开设的所有课程集合
        		   except
        		   (select T.course_id
        		   from takes as T
        		   where S.ID = T.ID)); //找出S.ID选修的所有课程
      
//X-Y=nullptr ->x是y的子集

3.8.4 重复元组存在性测试

在这里插入图片描述

如果在09年开设多次会在select的结果中出现>1次的ID，这里的select的结果返回如果大于1就会返回true，会保留在select之后的关系中，然后再去course关系中根据课程ID查找相应课程。
在这里插入图片描述

3.8.5 from子句中的子查询

注意这里的select的关系一定要用as重命名

在这里插入图片描述

3.8.6 with子句

在这里插入图片描述

在这里插入图片描述
后面的dept_total A里 as可以省略，就是临时构建两个关系

3.9 数据库的修改

在这里插入图片描述

只能删除整个不能删除部分的元组在这里插入图片描述
因为select的结果是一个value，所以可以直接写在where子句中，当删除的时候一个的时候平均工资会改变的，SQL如果没有涉及到括号外面的内容的话，会首先在没有删除的时候将平均值计算出来，然后将这个value保存下来不变，再进行选择。

3.10 数据库的修改——插入

在这里插入图片描述
insert into 关系的名字，属性的列表，values后面接的是属性的具体值。

在这里插入图片描述
插入元组的时候也可以插入空值。插入空值的方法主要有两种：一个是直接使用null进行插入，另一个是在属性列表中未写出的属性名都是null

在这里插入图片描述
按照条件在表中找到相应的学生，然后再把找到的学生插入到table中

3.11 数据库的修改——更新

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述
在这里插入图片描述

pudding_art
关注
专栏目录
数据库系统原理 04735.rar
11-17
数据库系统原理是计算机科学中的核心课程之一，尤其对于专升本的学生来说，深入理解这一领域至关重要。本压缩包“数据库系统原理 04735.rar”包含了一份珍贵的学习资源——"数据库系统原理 04735.pdf"，这份资料很...
大学数据库创建与查询实战——查询
weixin_51970555的博客
07-17 5531
查询表中指定的字段 查询表中的某一个字段的语法格式 GROUP BY子句
数据库原理（三）
NicolasHe的专栏
01-05 2156
SQL Sql查询语言概览 Sql最早的版本是由IBM开发，它最初的被叫做Sequel，在20世纪70年代早期作为System R项目的一部分。Sequel语言一直发展至今，其名称已经变为SQL(结构化查询语言)。Sql已经很明显的确立了自己作为标准的关系数据库语言的地位。 Sql语言包括一下几个部分： l 数据定义语言（Data-Definition Language,DDL）:SQL
数据库Mysql基本语句总结
zhsihui429的博客
01-16 534
目录 1、查询所有的数据库 2、指明使用哪个数据库 3、新建表（ create table） 4、插入数值 insert 5、删除表 6、修改属性alter 7、更新：使得不改变整个元组的情况下改变其部分属性的值 update 8、更名运算----重命名 as 9、字符串运算 10、排列元组的显示次序 11、集合运算 12、空值：SQL将涉及空值的任何比较运算的结果视...
SQL语言（二）数据库查询语言第二部分（嵌套子查询）
qq_42537795的博客
06-25 577
在Biology系 开设的所有课 程集合
DBMS第四篇（下）：SQL 查询语言 (第三章)——SQL语句-自然连接、补充基础操作、集合操作、聚集函数...
m0_46156900的博客
04-12 1462
1.Natural Join 自然连接操作对两个关系进行操作，并生成一个关系作为结果 看代码，1.要求是找到教员教的课程 select name, course_id from instructor natural join teaches; 这俩代码一个意思 select name, course_id from instructor, teaches where instructor.ID = teaches.ID; 注意： 自然连接只考虑在两种关系的模式中出现的属性上具有相同值
数据库系统原理
03-15
### 数据库系统原理 #### 一、数据库基本概念 1. **数据** - 定义：数据是指描述事物的符号记录，是用物理符号记录下来的信息。 - 表现形式：包括数字、字母、文字、特殊字符、图形、图像、动画、影像、声音、...
数据库系统原理课后答案
12-05
数据库系统原理是计算机科学中的核心课程之一，主要探讨如何有效地存储、管理和检索数据。这份"数据库系统原理课后答案"提供了对课程作业和习题的详尽解答，可以帮助学习者深入理解和掌握数据库系统的概念、设计与...
数据库系统原理实验四.sql
05-07
SQL语言-数据安全控制操作 用户张新具有修改这两个表的结构的权力
数据库系统原理chPPT课件.ppt
最新发布
11-23
"数据库系统原理" 数据库系统原理是指构造和管理数据仓库的原则和方法， Database System是指一个存储、管理和提供数据访问的系统。数据库系统原理包括数据库设计、数据库实现、数据库管理和数据库安全等方面的知识...
集合成员和关系
zgqtxwd的专栏
04-28 630
<!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--><script type="text/javascript"
数据库原理概述
IT1995的博客
02-09 8113
数据库原理概述
数据库原理
HugeStaff
05-04 3916
一提到关系型数据库，我禁不住想：有些东西被忽视了。关系型数据库无处不在，而且种类繁多，从小巧实用的 SQLite 到强大的 Teradata 。但很少有文章讲解数据库是如何工作的。你可以自己谷歌/百度一下『关系型数据库原理』，看看结果多么的稀少【译者注：百度为您找到相关结果约1,850,000个…】 ，而且找到的那些文章都很短。现在如果你查找最近时髦的技术（大数据、NoSQL或JavaScript
数据库原理（一）—— 关系代数
04-03 7158
关系代数：Relational Algebra。 什么叫代数？代数是一个较为基础的数学分支。它的研究对象有许多。诸如数、数量、代数式、关系、方程理论、代数结构等等都是代数学的研究对象。初等代数一般在中学时讲授，介绍代数的基本思想：研究当我们对数字作加法或乘法时会发生什么，以及了解变数的概念和如何建立多项式并找出它们的根。代数的研究对象不仅是数字，还有各种抽象化的结构。例如整数集作为一个带有加法、乘法
数据库系统概念笔记——第3章 SQL
绝尘
05-17 880
第3章 SQL SQL查询语言概览 SQL语言主要有以下的几个部分： 数据定义语言（Data Definition Language，DDL）：SQL DDL提供定义关系模式，删除关系以及删除关系模式的命令。 数据操纵语言（Data Manipulation Language，DML）：SQL DML提供从数据库中查询信息，以及在数据库中插入元组、删除元组和修改元组的能力。 完整性（Integrity）:SQL DDL包括定义完整性约束的命令，保存在数据库汇中的数据必须满足所有定义的完整性约束。 视图定义
数据库系统原理（二）关系型数据库
很注重数学和408
09-05 1960
引言 DBMS是由相互关联的数据集合以及一组用于访问这些数据集合的程序组成。数据描述特定的企业。 数据库系统设计用来存储大量的信息，所以不仅要考虑数据的存储结构还要考虑如何管理这些数据结构以及如何查询，同时还要保证数据存储的安全性和稳定性。 数据模型（Data model）一个用于描述数据、数据之间联系、数据语义和数据约束的概念工具的集合。 关系数据模型是最广泛使用的讲述君存储到数据库中的模型，其他数据模型有面向对象模型，对象-关系模型和半结构化的数据模型 数据操纵语言（DML）：非过
关系型数据库理论
Code-lover's Learning Notes
09-24 2744
关系模型数据库系统的数据结构简单，表示数据及其数据之间的联系均采用关系（二维表），同时关系模型有扎实的数学理论做基础，使得数据的操作可以通过关系的运算来完成。关系及关系模型关系是数学上的一个概念，建立在日常生活中所论及的关系概念之上，例如我们通常所说的邻里关系、朋友关系、学生与所选修的课程及该课程的成绩关系等。在这里我们所论及的朋友关系涉及到了互为朋友的双方，在数学上可表示为(张,李)；邻里关系也涉
数据库系统原理详解与应用
"数据库系统原理 计算机信息管理" 在深入探讨《数据库系统原理》这门课程之前，我们先来理解其核心内容。数据库系统原理是计算机科学中的关键组成部分，它涉及如何有效地管理和处理数据。这门课程旨在让学生理解...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值