吉林大学 数据库系统概念 第三部分 SQL语言初步(简单且分值高） 只求及格的同学必须要掌握

已于 2023-06-07 16:35:04 修改
阅读量1.4k 收藏 41
点赞数 8
分类专栏： 数据库 吉林大学 文章标签： 数据库 sql
于 2023-05-25 16:01:23 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_61197809/article/details/130865295
版权

吉林大学 数据库系统概念 第三部分 SQL语言初步

3.1SQL查询语言概览

SQL语言有以下几个部分

  • 数据定义语言（DDL）：DDL提供定义关系模式，删除关系以及修改关系模式的命令。
  • 数据操纵语言（DML）：DML提供从数据库中查询信息，以及在数据库中插入元组、删除元组、修改元组的能力。
  • 完整性：SQL DDL包括完整性约束的命令，保存在数据库中的数据必须满足所定义的完整性约束。
  • 视图定义：DDL包括定义视图的命令。
  • 事务控制：SQL包括定义事务的开始和结束。
  • 授权：DDL包括定义对关系和视图的访问权限的命令。

3.2 SQL数据定义

数据库的关系集合必须有数据定义语言（DDL）指定给系统，SQL的DDL不仅定义一组关系，还能定义每个关系的信息。

  • 每个关系的模式
  • 每个关系的取值集合
  • 完整性约束
  • 每个关系维护的索引集合
  • 每个关系的安全性和权限信息
  • 每个关系的物理信息

3.2.1 基本类型

  • char（n）：固定长度的字符串，用户指定长度为n
  • varchar（n）：可变长的字符串，用户指定最大长度为n
  • int：整数类型
  • smallint：小整数类型
  • numeric(p，d):定点数，精度由用户指定。这个数p为数字，d为小数
  • real，double precision ：浮点数与双浮点数
  • float（n）：精度至少为n位的浮点数

3.2.2基本模式定义 （非常非常重要‼️，一定会考）

  • create table 命令
		create table r
			(A1	D1,
			 A2	D2,
			 ...,
			 An	Dn,
			 <完整性约束1>
			 ...,
			 <完整性约束2>
			)；

Ai是关系r中的一个属性名，Di是Ai的一个域。
完整性约束

    • primary key（A1，A2，…,An)，主码约束。主码必须非空且唯一。
    • foregin key（ A1，A2，…,An）ferencens：外码约束，表明任意属性（A1，A2，…,An）元素的取值必须对应与关系s中某元组在主码属性上的值
    • not null 一个属性上的not null 约束表明该属性不允许空值。
  • insert命令 将数据加载到关系中
		insert into instructor
			values(1021,'smith','biology',66000);
  • delete 命令 从关系中删除元组
		delete from instructor
  • drop table 命令。drop是从sql去掉一个关系
		delete table r

delete 是比drop更强的命令，delete不仅删除所有元组，还删除模式

  • alter table 命令为已有表增加或删除属性
		alter table add A D；
		alter table drop A；

3.3 SQL查询的基本结构

SQL查询的基本结构由三个字句构成：select、from、where。查询的输入是在from子句中的关系，在这些关系还是那个进行select、where的运算。

3.3.1 单表查询

		select name
		from instructor
		where dept_name = 'Comp. Sci.'and salary > 70000;

select 后可加distinct关键字，删除重复。也可加all关键字保留重复，一般默认保留重复。
where 子句中使用逻辑连词 and or 和not.逻辑连词的对象是可以包括比较运算符的表达式.

3.3.2多表查询

		select A1,A2,..,An
		from r1,r2,..,rn
		where P;

运算顺序from ，where，select
我们可以用以下的迭代过程来理解from语句

		for each 元组 t1 in 关系 r1
			for each 元组 t2 in 关系 r2
				...
				for each 元组 tn in 关系 rn
				把t1,t2,...tn连接成单个元组t
				把t加入到结果关系中

一个sql查询的含义可以理解如下
1.from产生笛卡尔积
2.在1的结果上找出符合where 语句的
3.对2的结果只输出select查询的内容

3.3.3自然连接

自然连接运算作用于两个关系，并产生一个关系作为结果。自然连接只考虑
那写两个关系模式都出现的属性相同的元组对进行连接。

		select A1,A2,...,An
		from r1 natural join r2 ... natural join rm
		where P

如果有多个属性相同可用join using 运算指定相同的名称

		select name ,title
		from (instructor natural join student) natural join 			coures using（course_id）;

3.4 附加的基本运算

更名运算
as语句 ：old_name as new_name
** 字符串**
like操作符 百分号（%）匹配任意子串 下划线（-）：匹配任意一个字符。
escape 关键字可定义转义字符。
排序元组的显示次序
order by 子句默认升序，我们可以用desc表示降序，asc表示升序。
where子句谓语
between and 子句 以及元组表示
元组表示

		select name，cousre_id
		from instructor,teaches
		where (instructor.id,dept_name) = (teache.id,'biology);

3.5集合运算

SQL作用在关系上的Union 、intersect、except 对应于数学的交并查集。
与select语句不同的是，这些操作结果自动去重。

3.6 空值

空值给关系运算带来了特殊的问题，包括算术运算，比较运算和集合运算

  • 算术运算

如果算术表达式的任一输入为空，则该算术表达式（涉及诸如 +、-、* 或 / 的算术运算）结果为空。

例：如果查询中有一个表达式是r.A + 5, 并且对于关系中某个特定的元组， r.A为空，那么对此元组来说，该表达式的结果也为空。

  • 比较运算

SQL将涉及空值的任何比较运算的结果视为unknown（既不是谓词is null，也不是is not null）。unknown是在SQL中除true和false之外的第三个逻辑值。

补充：由于在where子句的谓词中可以对比较结果使用诸如and、or和not的布尔运算，将布尔运算扩展到可以处理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 unknown = unknown
  • 集合运算

在集合运算中，{(‘A’, null), (‘A’, null)}中的两个元素会被认为是相同的元素。因此在使用distinct子句只会保留这样的相同的元组的一份拷贝。

3.7 聚集函数（非常重要必考！！）

以一个集合输入，返回多个值的函数。
固有的聚集函数

  • 平均值 ：avg
  • 最小值： min
  • 最大值：max
  • 总和： sum
  • 计数： count
    sum 、avg输入必须是数字集

3.7.1基本聚集函数

查询1. 平均数

找出Computer Science 系教师的平均工资。

select avg(salary)
from instructor
where dept_name = 'Comp. Sci.';

查询2. as 重命名平均数

将查询1中的查询属性赋予一个有意义的名字

select avg(salary) as avg_salary
from instructor
where dept_name = 'Comp. Sci.';

注意： 在计算平均数时，保留重复元组很重要。

查询3. distinct 使用的情况。

找出在2010年春季学期讲授一门课程的教师总数：

select count(distinct ID)
from teaches
where semester = 'Spring' and year = 2010;

有些情况下，计算聚集函数需要先删除重复元组。可以使用distinct。

3.7.2分组聚集

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

查询4.
每个系在2010年春季学期讲授一门课程的教师人数

select dept_name, count(distinct ID) as instr_count
from instructor natural join teaches
where semester = 'Spring' and year = 2010
group by dept_name;

注意：出现在select语句中但没有被聚集的属性，只能是那些出现在group by 子句中那些的属性。换句话说，任何没有出现在group by 中的属性，如果出现在select子句中，它只能出现在聚集函数内部，否则这样的查询就是错误的。

错误查询

select dept_name, ID, avg(salary)
from instructor
group by dept_name;

ID出现在select子句中，且不是聚集函数的参数，却没出现在group by，查询错误。换个思路，一个分组有多个教师，每个教师有不同的ID，每个分组只输出一个元组，那么就无法确定输出哪一个ID。

3.7.3 having 子句

任何出现在having子句中，但没有被聚集的属性必须出现在group by 子句中，否则查询就被当成错误的。

包含聚集、group by或having子句的查询的含义可通过下述操作序列来定义：

  1. 最先根据 from 子句计算出一个关系；
  2. 如果出现 where 子句， where 子句 中的谓词将应用到 from子句 的结果关系上；
  3. 如果出现了group by 子句， 满足 where 谓词的元组通过group by 子句形成分组。 如果没有 group by 子句，满足where谓词的整个元组集被当做一个分组。
  4. 如果出现了 having子句，它将应用到每个分组上；不满足having子句谓词的分组将被抛弃。
  5. select 子句 利用剩下的分组产生出查询结果中的元组，即在每个分组上应用聚集函数来得到单个结果元组。

3.7.4对空值和布尔值的聚集

  • 空值被忽略有可能造成参加函数运算的输入值集合为空。
  • 规定空集的count运算值为0 ，其他所有聚集运算在输入为空集的情况下返回一个空值。

3.8 嵌套子查询

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

3.8.1集合成员资格

  • in：测试元组是否是集合中的成员
  • not in：测试元组是否不是集合中的成员
    单属性关系中测试成员资格
	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);

in和 not in也能作用于枚举集合

select distinct name
from instructor
where name not in ('Mozart','Einsten');

测试任意关系的成员资格

select count(distinct ID)
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集合的比较

some：至少比某一个要大（any和some的用法大致相同）

找出满足下面条件的所有教师的姓名，他们的工资至少比Biology系某一个教师的工资要高

select name
from instructor
where salary >some (select salary
                    from instructor
                    where dept_name = 'Biology');
  • SQL同时也允许<some，<=some，>=some，=some，<>some的比较，=some等价于in,然而<>some并不等价于not in
  • > all比所有都大
  • SQL同时也允许<all，<=all，>=all，=all ，<>all的比较，<>all等价于not in,然而=all并不等价于in

3.8.3空关系测试

相关子查询
构造子查询的查询条件时需要用到父查询的某一个属性列，这样的查询称为相关子查询。

相关子查询是无法独立执行的，因为它必然含有对外部查询表中元组分量的引用。
其执行过程为：
（1）按顺序从外部查询中取出一个元组，将元组的相关分量值传递给子查询；
（2）执行子查询，得到结果值；
（3）外部查询根据子查询返回的结果或结果集确定取出的这一行元组是否满足条件；若外层的where子句返回真值，说明符合；否则不符合，舍弃。
（4）重复步骤1-3，直到外部查询表中的所有元组都被处理完毕。

最常见的相关子查询是EXISTS引导的子查询。
EXISTS 引导的子查询不返回数据，而是返回是否存在满足子查询的外层查询元组的判断(真/假)，主查询根据该判断来逐条取舍元组。

  • exists：测试子查询结果中是否存在元组，在作为参数的子查询非空时返回true值
  • not exists：测试子查询结果中是否不存在元组，在作为参数的子查询为空时返回true值

3.8.4重复元组存在性测试

  • unique：测试子查询结果是否存在重复元组，如果没有出现重复元组，返回true
  • not unique：测试子查询结果是否存在重复元组，如果出现重复元组，返回true

3.8.5 from子句中的子查询

任何select-from-where表达式返回的结果都是关系，因而可以被插入到另一个select-from-where中任何关系可以出现的位置
  
原先 having子句的查询

select dept_name,avg(salary)
from instructor 
group by dept_name
having avg(salary) > 42000;

可变为

select dept_name,avg_salary
from (select dept_name,avg(salary) as avg_salary
      from instructor
      group by dept_name)
      as dept_avg(dept_name,avg_salary)
where avg_salary > 42000;

3.8.6 with 字句

with：提供定义临时关系的方法，只对包含with子句的查询有效

with max_budget(value)as
    (select max(budget)
     from department)
select budget
from department,max_budget
where department.budget = max_budget.value;

3.8.7 标量子查询

SQL允许子查询出现在返回单个值的表达式能够出现的任何地方，只要该子查询只返回包含单个属性的单个元组。
列出所有的系以及他们拥有的教师数

select dept_name，
    (select count(*)
     from instructor
     where department.dept_name = instructor.dept_name)
     as num_instructors
from department;
一只大小菜
关注
  • 8
    点赞
  • 踩
  • 41
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值