Skip to content

Lecture 3 SQL语言part1

:material-circle-edit-outline: 约 355 个字 :material-clock-time-two-outline: 预计阅读时间 1 分钟

表定义、基本SQL查询

主要内容：

SQL(Structured Query Language) 是关系数据库标准语言,包括数据定义、数据操纵、数据控制一体化管理功能。SQL是一种陈述式的语言。讲授SQL的表定义，包括：SQL基本数据类型、primary key、foreign key和check定义，以及SQL DML语句的基本用法，包括select、from、where、group by、having各子句。

作业3：书后习题 3.8、3.9

关系代数只是用来描述，实际还是用sql写

历史

sql标准非强制，主要是指导

书上的例子语法不一定正确（方言）

数据类型

image-20240311135045462

image-20240311142503255

image-20240311133746664

Table Construct

image-20240311134726815

image-20240311134744995

image-20240311134943531

image-20240311135909888

reference 表示被引用时执行

cascade （一个过程中前一个引发后一个的）连续阶段

属于动作，类似递归这类的描述

还有三种动作（红色字体）

重新听一下考试考过的地方

image-20240311140605628

*SQL and Relational Algebra

image-20240311142133260

image-20240311142137645

image-20240311142145043

Basic Query Structure

image-20240311142154501

The select Clause

image-20240311142225118

image-20240311142231366

image-20240311142237315

The where Clause

image-20240311142246147

Where Clause Predicates

image-20240311142300300

The from Clause

image-20240311142312889

Joins

image-20240311143542109

Natural Joins

image-20240311143745173

image-20240311143956178

image-20240311143949690

image-20240311144003274

The Rename Operation

image-20240311144019262

String Operations

image-20240311144037598

image-20240311144310628

Ordering the Display of Tuples

image-20240311144321804

The limit Clause

image-20240311144742658

Duplicates

Set Operations

image-20240311145257102

image-20240311145313573

Null Values

image-20240311145159546

image-20240311145340241

Aggregate Functions

image-20240311145844985

image-20240311145859109

Aggregate Functions – Group By

image-20240311145917516

image-20240311145936973

Aggregate Functions – Having Clause

image-20240311150004453

上面这个例子包含了sql六大基本语句

这一部分都没听

Null Values and Aggregates

image-20240311151609154

Arithmetric expression with Aggregate functions

image-20240311151638157

Nested Subqueries

image-20240311151653610

Set Membership

image-20240311151707278

image-20240311152723751

Set Comparison

image-20240311152741995

image-20240311152800126

Definition of Some Clause

image-20240311152750543

Definition of all Clause

image-20240311152813979

Scalar Subquery

image-20240311152825696

Test for Empty Relations

image-20240311152834265

Correlation Variables

image-20240311152843459

Not Exists

image-20240311152851448

Test for Absence of Duplicate Tuples

image-20240311152858917

image-20240311152922445

image-20240311153444815

*Subqueries in the From Clause

image-20240311154857390

image-20240311154911174

*With Clause

image-20240311154919126

*Complex Queries using With Clause

image-20240311154932260