【数据库04】中级开发需要掌握哪些SQL进阶玩法_sql开发对数据库概论有要求吗

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

需要这份系统化资料的朋友，可以戳这里获取

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

更一般地，from子句可以写为如下形式。

from E1,E2...En

每个Ei可以是单个关系或者一个涉及自然连接的表达式。例如，假设我们要查询“列出学生的姓名和他们所选择的课程的名称”。可以写。

select name,title
from student natural join takes, course
where takes.course_id = course.course_id;

受限计算studnet和takes的自然连接，再计算该结果与course的笛卡尔积，where字句从结果中再过滤，过滤条件是course_id相匹配。

思考如下sql与上面的sql是否会等价。

select name,title
from student natural join takes natural join course

不是！！！

请注意student和takes做自然连接后包含的属性是(ID，name，dept_name,tot_cred,course_id,sec_id),而course包含的属性是(course_id,titile,dept_name,credits)。上面二者做自然连接，不仅需要course_id取值相同，还需要dept_name取值相同。

为了避免这样的错误出现，我们可以这样做。

select name,title
from (student natural join takes) join course using (course_id);

1.2 连接条件

除了上面的join using外，还可以使用on关键字指定连接条件。

select \* 
from student join takes on student.ID = takes.ID; 

这与自然查询的结构是一样的，唯一的区别在于查询结果ID出现两次，一次是student中的，一次是takes结果中的。如果希望ID只出现一次，可以这么做。

select student.ID as ID,name,dept_name,tot_cred,
	course_id,sec_id,semester,year,grade
from student join takes on student.ID = takes.ID; 

爱思考的读者会发现，on关键字似乎可以被where所替代，那它是不是一个冗余的语法？实际上，on关键字在外连接中与where表现是不同的，其次，如果使用on作为连接条件，并在where字句中出现其余的条件，sql查询会更加清晰易懂。

1.3 外连接

假设我们希望查询所有学生的个人信息与选修的课程，可能会想到如下检索。

select \* from student natural join takes

不过如果一个学生没有选修课程，就不会出现在这个结果中。

我们可以改用外连接来实现我们的需求。外连接与我们已经学习过的连接运算类似，但是它会通过在结果中创建包含空值的元组，来保留那些在连接中会丢失的元组。

外连接分为三种，

• 左外连接。只保留连接关键字之前的关系的元组。
• 右外连接。只保留连接关键字之后的关系的元组。
• 全外连接。保留出现在两个关系中的元组。

相比较而言，我们之前学习的不保留未匹配元组的连接运算被称为内连接运算。

比如"查询所有学生的个人信息与选修的课程"可以这样用左外连接实现。

select \* 
from student natural left outer join takes;

查询“一门课程也没有选修的学生”。

select ID
from student  natural left outer join takes
where course_id is null;

左外连接与右外连接是对称的，用右外连接实现"查询所有学生的个人信息与选修的课程"。

select \* 
from take natural right outer join student ;

全外连接可以看做左外连接与右外连接的并运算。考虑查询，“显示Comp.Sci系中所有学生以及他们在2017年春季选修的所有课程段的列表。在2017年春季选修的所有课程段都必须显示。”

select \*
from (select \* 
	from student
	where dept_name = 'Comp.Sci')
	natural full outer join
	(select \*
	from takes
	where semester = 'Spring' and year = 2017);

在外连接中，where和on关键字表现是不同的。on会作为外连接声明的一部分，而where却不是。使用where时不会补全具有空值的元组，使用on则会。

另外，常规连接也被称为内连接，可以使用缺省的关键字inner。

2.视图

让所有用户看到数据库关系中的完整集合并不合适，我们可以通过SQL授权来限制对关系的访问，但是如果仅需要向用户隐藏一个关系中的特定数据，可以使用视图。

除了安全型的考虑，视图还可以通过定制化更好的匹配特定用户的需求。

2.1 视图定义

创建视图语法是。

create view v as <查询表达式>;

考虑需要访问instrutor除了salary外的所有数据的职员。

create view faculty as
	select ID,name,dept_name
	from instructor;

视图在概念上包含查询结果中的元组，但是不进行预计算和存储。我通俗的理解成，创建视图是创建了一个规则，使用视图时再根据规则进行计算。

2.2 在SQL查询中使用视图

创建视图后可以像使用数据表一样使用视图。如。

select ID from faculty ;

可以显示的指定视图的属性名称。

create view department_total_salary(dept_name, total_salary) as
	select dept_name,sum(salary)
	from instructor
	group by dept_name;

直观的说，任意给定时刻，视图关系几种的元组集都是使用定义视图查询表达式求值的结果，因此如果定义并存储一个视图关系，一旦定义视图的关系被修改，那么视图就会过期。

一个视图还可以被用到另一个视图的定义中去。

2.3 物化视图

某些数据库系统中的视图关系保证：如果定义视图的实际关系发生改变，则视图也跟着修改以保持更新，这样的视图被称为物化视图。如果视图是物化的，则其计算结果会被存储在计算机中，从而在使用视图时可以更快的运行。

既然物化视图会预计算并存储，那么就需要保持物化视图的更新，保持物化视图一直在最新的状态的过程被称为物化视图维护，或者视图维护。这种维护策略可以是实时维护，周期维护，惰性维护(被使用时才更新)，人工维护等，支持的策略与数据库产品有关。

物化视图对于频繁使用的视图有帮助，对大型关系的聚集运算也较为适用，需要平衡其存储代价与性能开销。

2.4 视图更新

对视图进行增删改可能会带来严重的问题，因为用视图表达的修改必须被翻译为对数据库关系的实际修改。一般不允许对视图进行更新。不同的数据库可能会指定不同的条件，在满足这些条件的前提下可以对视图进行更新，具体可以参考其系统手册。

一般说来，如果定义视图的查询满足下面条件，那么称SQL视图是可更新的。

• from字句中只有一个数据库关系
• select子句中只包含关系的属性名，并不包含任何的表达式、聚集或者distinct声明。
• 没有出现在select子句中的任何属性都可以取null值。也就是说，这些属性没有非空约束，也不构成主码的一部分。
• 查询中不包含有group by或者having子句。

不过要注意，即使满足上面的限制条件，仍然不一定可以将数据顺利插入视图。定义如下视图。

create view history_instructors as
select \*
from instructor
where dept_name = 'History';

考虑如下查询。尝试向history_instructors视图中插入元组(‘25566’,‘Brown’,‘Biology’,10000)。这个元组可以被插入instructor关系中，但是不满足视图的选择要求dept_name = 'History'。他不应该出现在视图history_instructors中。

我们可以在视图定义的末尾添加with check option子句做到这一点，如果新值满足where子句的条件，就可以插入视图，否则，数据库系统会拒绝该插入操作。

SQL:1999对于视图有更加复杂的规则集。这里不讨论。

触发器机制提供了另外一种视图修改数据库的机制，它更加可取，后续文章将详细介绍。

3.事务

事务有查询或者更新语句的序列组成。SQL标准规定当一条SQL语句被执行时，就隐式的开始了一个事务。下列SQL语句之一会结束该事务。

• commit work，事务提交。一个事务提交后就在数据库中称为了永久性的，会自动开始一个新的事务。
• rollback work，事务回滚，也就是它会撤销事务中的SQL语句执行的所有更新。

关键字work在两条语句中都是可选的。

考虑下面场景，如果一个学生成功修完了一个课程，需要对takes关系进行更新，也需要对student关系进行更新。如果更新完其中一个关系，却没有更新完另外一个关系就出现了系统故障，会出现数据不一致的情况。这种情况不应该出现，两个关系要么同时被更新，要么同时不被更新。这就是事务的原子性,

在包括Mysql和PostgreSQL在内的很多SQL实现中，在缺省方式下每条SQL自动组成一个事务，且语句一旦执行完立刻提交该事务。很多数据库实现支持关闭自动提交。

一种更好的备选方案是，作为SQL：1999标准的一部分，允许多条SQL语句被包含在关键字begin atomic ... end之间，这样关键字之间的语句就构成了一个单一的事务，如果执行到end语句，则该事务被默认提交。只有诸如SQL Server的某些数据库支持上述语法。诸如MySQL和PostgreSQL的其他几个数据库支持的是begin语句，该语句启动包含所有后续SQL语句的事务，但是并不支持end语句，事务必须通过commiy work或者rollback work命令来结束。

但是使用诸如Oracle的数据库，自动提交并不是DML语句的缺省设置，请保证在添加或者修改数据后发出commit命令。不过Oracle会自动提交DDL语句。虽然Oracle已经关闭了自动提交，不过期缺省设置可能被本地设置所覆盖。

4.完整性约束

完整性约束保证授权用户对数据库所做的修改不会导致数据一致性的丢失。他可以在数据库关系定义是作为create table的一部分被声明。也可以通过使用alter table table-name add constraint命令将完整性约束添加到已有关系上。

4.1 非空约束

我们可以用非空约束限制属性非空。在创建表时就可以声明属性非空约束，语法是。

name varchar(20) not null

主码中禁止出现空值，不需要显示的指定非空约束。

4.2 唯一性约束

可以采用unique约束属性唯一,注意唯一性约束允许属性为null。请回忆一下，空值不等于其他任何值。

4.3 check子句

check§可以制定一个谓词P，灵活的给所有元组增加约束。

比如在crate table命令中的check（budget>0）子句将保证budget的取值非负。

check子句不能够限制属性非空，因为当check子句的计算结果为未知时，也被认为满足谓词P(结果不是false即满足)，需要限制非空必须指定单独的非空约束。

根据定义，check子句中的谓词可以任意，不过当前还没有一个被广泛使用的数据库允许其包含子查询的谓词。

4.4 引用完整性

我们常常希望一个关系中的给定属性在另外一个关系中也出现。这就是引用完整性约束。外码是引用完整性约束的一种形式，其中被引用的属性构成被引用关系的主码。

比如对course表的定义有一个声明"foreign key(dept_name) references department ".这个约束说明，对于每个课程元组，元组中指定的系名必须在department关系中存在，这样即可避免为一门课程指定一个并不存在的系名。

在缺省情况下(Mysql 并不支持缺省)，SQL中外码引用的是被引用表的主码属性。SQL还支持显示指定被引用关系的属性列表的引用子句版本。例如，course关系的外码声明可以指定为

foreign key(dept_name) references department(dept_name)

注意被指定的属性列表必须声明为被引用关系的超码，要么使用主码约束，要么使用唯一性约束来进行这种声明。

在更为普遍的引用完整性约束里，被引用的属性不必是候选码，但是这样的形式不能在SQL中直接声明。SQL标准为其提供了更为普遍的结构，但是，任何广泛使用的数据库系统都不支持这些替代结构。

当违反引用完整性约束时，通常的处理时拒绝执行破坏完整性的操作(即执行更新操作的事务回滚)。但是，在外码子句中可以显示指定其他策略。请考虑course关系上一个完整性约束的如下定义。

create table course
(...
foreign key (dept_name) references department
			on delete casecade
			on update casecase,
...);

上面制定了外码声明相关联的级联删除子句，如果删除department中的一个元组导致违反了这种引用完整性约束，则系统并不拒绝该删除，而是进行级联删除，即删除引用了被删除的系(department)的课程(course)元组。

除了级联，还可以指定其他策略，比如使用set null将引用域(这里是dept_name)置为null，或者置为该域的缺省值(set default)

如果存在跨多个关系的外码依赖链，则在链的一端所做的删除或者更新可能级联传递至整个链上。

空值会使SQL的引用完整性约束变得更加复杂，这里我们暂时不讨论。

4.5 给约束赋名

我们可以使用关键字contranit为完整性约束赋名，这样在删除约束时很有用。比如。

salary numeric(8,2), constraint minsalary check(salary > 29000),

当我们删除约束时，可以

alter table drop constraint minsalary;

如果名称缺失，就需要用特定于系统的功能来识别出约束的系统分配名称。并非所有系统都支持这样的功能，但是在比如Oracle中，系统表user_constraints就包含了这样的信息。

4.6 事务中对完整性约束的违反

事务可能包含多个步骤，在某一步也许会暂时违反完整性约束，但是后面的某一步也许就会消除这个违反。例如，假设我们有一个主码为name的person关系，还有一个spouse属性，并且假设spouse是在person上的一个外码。也就是说，该约束要求spouse属性必须是在person表中出现的姓名。假设我们在关系中插入两个元组，一个是关于John的，另一个是关于Mary的，他们互为配偶，无论先插入哪个，都会导致违反该外码约束，直到另一个元组也被插入。

为了处理这样的情况，SQL标准允许将initially deferred子句加入约束声明中，这样约束就不是在事务的中间步骤去检查，而是在事务的结束时去检查。一个约束可以被指定为可延迟的(deferrable)，这样在缺省情况它会被立即检查，但是在需要时可以延迟检查。对于这种约束，将使用set constraints constraint-list deferred语句的的执行将作为事务的一部分，从而导致对约束的检查被延迟到事务结束时执行。在约束列表中出现的约束必须指定名称。缺省方式是立刻检查约束，并且许多数据库实现不支持延迟约束检查。

如果spouse可以置为null，可以在插入John和Mary元组时，将其spouse属性置为null，后面再更新值，但这会加大编程量，但属性必须非空时，这种方法就不可行。

4.7 复杂check条件与断言

在SQL标准中还有其它结构用于指定大多数系统当前不支持的完整性约束。可以通过check子句中复杂谓词实现更复杂的数据完整性需求，这里我们不赘述。

一个断言就是一个谓词，他表达我们希望数据库总能满足的一个条件，比如：每个教师不能在同一个学期的同一个时间段在两个不同的教室授课。

断言和复杂check条件都需要相当大的开销，如果系统支持触发器，可以使用触发器实现等价的功能。

5.SQL的数据类型与模式

5.1 SQL中的日期和时间类型

SQL中支持的日期和时间相关的数据类型有：

• 日期(date)。年月日。必须按照2018-04-05这种格式指定。
• 时间(time)。时分秒。可以使用变量time(p)来指定秒的小数点后的数字位数(缺省值为0)，通过指定time with timezone，还可以把时区信息连同时间一起存储。必须按照09:20:00格式指定，秒后的小数点位数可以变长。
• 时间戳(timestamo):date和time的结合。可以使用变量timesamp(p)来指定秒的小数点后的数字位数(缺省值为6)。通过指定with timezone，可以把时区信息连同时间一起存储。必须按照2018-04-25 10:29:01.45格式指定，秒后的小数点位数可以变长。

我们可以利用extract(field from d)来从date或time值d中提取出单独的域，这里的域(field)可以是year，month，day，hour，minute或者second中的一种。时区信息可以使用timezone_hour和timezone_minute来提取。

SQL定义了一些函数来获取当前的日期和时间。例如，current_date返回当前日期，current_time返回当前时间(带有时区)，还有localtime返回当前的本地时间(不带时区)。时间戳(日期加上时间)由current_timestamp(带有时区)以及localtimestamp(本地，不带时区)返回。

保留Mysql在内的某些系统提供了datetime数据类型用来表示时区不可调整的时间。在实践中，时间规范会有许多的特殊情况。

SQL还支持interval数据类型，它表示时间区间。

5.2 类型转换和格式化函数

我们可以使用形如cast(e as t)的表达式来将表达式e转换为类型t。可能需要数据类型转换来执行特定的操作或者强制保证特定的排序次序。例如，请考虑instructor的ID属性，我们已经将其指定为字符串(varchar(5))，如果我们按此属性排序输出，则ID11111位于ID9之前，因为第一个字符‘1’在‘9’之前。我们可以强转获得我们想要的排序。

select cast(ID as numeric(5)) as inst_id
from instructor
order by inst_id;

作为查询结果显示的数据可能需要不同类型的转换，例如，我们可能希望数值以特定位的数字显示，或者数据以特定格式来显示。显示格式的转换并不是数据类型的转换，而是格式的转换。不同数据库产品提供了不同的格式化函数。Mysql提供了format函数,Oracle和PostgreSQL提供了一组函数，to_char,to_number和to_date.SQL Server提供了convert函数。

结果显示的另一个问题就是处理空值，在本书中，我们使用null来使阅读更清晰，但是大多数系统的缺省设置只是将字段留空。可以使用coalesce函数来选择在查询结果中输出空值的方式。该函数接受任意数量的参数(所有参数必须是相同的类型)，并返回第一个非空参数。例如，如果我们希望显示教师的ID和工资，但是将空工资显示为0，我们会写：

select ID,coalesce(salary, 0) as salary
from instructor

coalesce的一个限制就是所有参数必须是相同的类型，如果我们希望将空工资显示为N/A以表示为不可用，就无法使用coalesce。诸如Oracle提供了**解码(decode)**函数允许这种转换，解码的一般形式是：

decode(value, match-1,replacement-1,match-2,replacement-2,...,match-n,replacement-n,default-replacement);

它将value与match值进行比较，匹配则替换，有点类似程序语言中的switch-case结构。

我们可以这样实现前面的需求

select ID,decode(salary, null, 'N/A', salary) as salary
from instructor;

5.3 缺省值

可以在创建表时指定属性缺省值。

create table student
(ID varchar(5),
name varchar(20) not null,
to_cred numeric(3,0) default 0,
primary key(ID));

5.4 大对象类型

SQL为字符型数据(clob)和二进制数据(blob)提供了大对象数据类型(large-object data type)。例如，

book_review clob(10KB)
image blob(10MB)

把一个大对象放入内存是非常低效而且不显示的，应用程序通常用一个SQL查询出大对象的"定位器"，然后用宿主语言操作这个对象，例如JDBC应用程序接口允许获取一个定位器，用这个定位器来一点一点取出这个大对象，而不是一次全部取出。

5.5 属性的时态有效性

在某些情况下可能需存储历史数据，比如，我们希望不仅存储每位教师的当前工资，而且存储整个工资历史，可通过向instructor关系模式添加两个属性来指定给定工资值的开始时间和结束时间，这些开始日期和结束日期被称为相对应工资值的有效时间值。

请注意在这种情况下，instructor关系中可能存在不止一个具有相同ID值得元组，后续文章将讨论在时态数据的上下文中特定主码和外码约束的问题。

对支持这种时态结构的数据库系统来说，第一步就是提供语法来支持指定特定属性来定义有效的时间区间，比如我们使用Oracle12的语法示例。使用如下的period声明来扩充instructor的SQL DDL，以表明start_date和end_date属性指定了一个有效的时间区间。

create table instructor
(
...
start_date date,
end_date date,
period for valid_time(start_date, end_date),
...
);

在进行查询时则使用as of period for结构，以仅获取其特定有效时间的那些元组。比如

select name,salary,start_date,end_date
from instructor as of period for valid_time '20-JAN-2014';

结合between and可以查找时间段。

5.6 用户自定义类型

SQL支持两种形式的用户自定义数据类型，第一种是独特类型(distinct type)，另一种是结构化数据类型(structured data type),允许创建具有嵌套记录结构、数组和多重集的复杂数据结构，本文介绍前者，后续文章将介绍结构化数据类型。

教师姓名和系的姓名都是字符串，然而我们通常并不认为把一个教师的姓名赋给一个系名，把一个以美元表示的货币值与以英镑表示的货币值进行直接比较是合法的。一个好的类型系统应该能够检测出这类赋值或者比较，为了支持这种检测，SQL提供了distinct type的概念，可以定义新类型，如：

create type Dollars as numeric(12,2) final;
create type Pounds as numeric(12,2) final;

这样我们就可以创建下表。

create table department(
dept_name varchar(20),
building varchar(15),
budget Dollars);

由于强类型转换，表达还(department.budget + 20)将不被接受，可以将budget 强转为numeric，但是如果需要存回Dollars类型的属性中，又需要转换为Dollars类型。

可以使用drop type和alter type子句删除或者修改以前创建过的类型。

SQL-1999提出创建类型前，还有一个类似概念，创建域domain，二者其实有重大差异，不过大多数数据库实现并没有支持创建类型和创建域，这里不做展开。

一个创建域的例子是。

create domain degree_level varchar(10)
	constraint degree_level_test
		check (value in 'Bachelors','Masters','Dectorate'));

5.7 生成唯一码值

数据库系统提供了生成唯一码值的自动管理，具体语法依赖于数据库实现。这里我们展示的语法接近Oracle和DB2的语法。

ID number(5) generated always as identity

当使用always选项是，在insert语句是必须避免为相关属性指定值。如果使用by default则可以选择是否指定我们自己挑选的ID。

在PostgreSQL中，我们可以将ID类型定义为serial，它告诉PostgreSQL要自动生成标识。在Mysql中，我们使用auto_increment来实现自动生成唯一自增码值。

此外，许多数据库都支持创建序列结构，该结构创建域任何关系分离的序列计数器对象，并允许SQL查询从序列中获得下一个值，每次获得的值递增。这样，多个关系之间的（如student.ID与instructor.ID）也可以保持唯一。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

需要这份系统化资料的朋友，可以戳这里获取

entity

当使用`always`选项是，在insert语句是必须避免为相关属性指定值。如果使用`by default`则可以选择是否指定我们自己挑选的ID。


在PostgreSQL中，我们可以将ID类型定义为`serial`，它告诉PostgreSQL要自动生成标识。在Mysql中，我们使用`auto_increment`来实现自动生成唯一自增码值。


此外，许多数据库都支持**创建序列**结构，该结构创建域任何关系分离的序列计数器对象，并允许SQL查询从序列中获得下一个值，每次获得的值递增。这样，多个关系之间的（如student.ID与instructor.ID）也可以保持唯一。


[外链图片转存中...(img-Vqw0pdeQ-1715367674293)]
[外链图片转存中...(img-VmKJpTY7-1715367674294)]
[外链图片转存中...(img-8PCYbH8Q-1715367674294)]

**既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！**

**由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新**

**[需要这份系统化资料的朋友，可以戳这里获取](https://bbs.csdn.net/forums/4f45ff00ff254613a03fab5e56a57acb)**