第0章绪论搭建SQL的学习环境

0-1 PostgreSQL的安装和连接设置

在命令行使用postgreSQL需要登录 postgres 账户:

1	`sudo su postgres`

进入 sql 命令行执行:

$ psql

postgres 账户的目录在 /var/lib/postgresql.

官方Ubuntu上使用postgresql教程

reference

配置文件所在位置: /etc/postgresql/<version>/main, 目录中. 我的在: /etc/postgresql/14/main.

连接 default PostgreSQL template database:

1	`sudo -u postgres psql template1`

To configure the password for the user postgres:

1	`ALTER USER postgres with encrypted password 'your_password';`

Restarting the PostgreSQL service to initialize the new configuration:

1	`sudo systemctl restart postgresql.service`

0-1

listen_addresses = '*' 意味着允许所有远程主机进行连接。
listen_addresses = 'localhost' 意味着只允许本地机器进行连接。

0-2 通过PostgreSQL执行SQL语句

psql 是 PostpreSQL 提供的通过命令行执行 SQL 语句的工具。

psql 会把 SQL 语句发送给 PostgreSQL, 然后再将接收到的执行结果显示出来。

“;” 是 SQL 的结束符，如果没有输入的话，即使按下回车键，SQL语句也不会执行。

数据库的名称只能是小写字母.

创建学习用的数据库

1	`create database shop;`

postgres 是安装 PostgreSQL 时自动创建的示例数据库。

连接学习用的数据库(登录)

1	`psql -U postgres -d shop`

“-d shop” 是指定 “数据库shop” 的意思。

“-U username” 是 connect to the database as the user username instead of the default.

第1章数据库和SQL

1-1 数据库是什么

数据库(Database, DB)是将大量数据保存起来，通过计算机加工而成的可以进行高效访问的数据集合。

用来管理数据库的计算机系统称为Database Management System, DBMS.

系统使用者通常无法直接接触到数据库。

DSMS的种类

通过数据的保存格式(数据库的种类)来分类，现阶段主要有以下5种类型:

Hierarchical Database, HDB
Relational Database, RDB
Object Oriented Database, OODB
XML Database, XMLDB
Key-Value Store, KVS
使用SQL语言的数据库管理系统，也就是关系数据库管理系统(RDBMS).

1-2 数据库的结构

RDBMS 的常见系统结构

最常见的是客户端/服务器类型(C/S类型).

RDBMS 既可以和其客户端安装在同一台计算机上，也可以分别安装在不同的计算机上。

表的结构

用来管理数据的二维表在关系数据库中简称为表.

表存储在由 RDBMS 管理的数据库中，一个数据库中可以存储多个表。

根据 SQL 语句的内容返回的数据同样必须是二维表的形式。

表的列（垂直方向）称为字段，它代表了保存在表中的数据项目。

表的行（水平方向）称为记录，它相当于一条数据。

关系数据库必须以行为单位进行数据读写。

行和列交汇的方格称为单元格。一个单元格中只
能输入一个数据。单元格只是这本书特有的表述方式.

1-3 SQL 概要

标准 SQL

SQL 语句及其种类

SQL 用关键字、表名、列名等组合而成的一条语句（SQL 语句）来描述操作的内容。

SQL 语句可以分为以下三类:

DDL, Data Definition Language, 数据定义语言。其包含: create, drop, alter.
DML, Data Manipulation Language, 数据操纵语言. 包含: select, insert, update, delete.
DCL, Data Control Language, 数据控制语言。包含: commit, rollback, grant, revoke.

SQL 的基本书写规则

以分号”;”结尾
SQL 语句不区分大小写, 表中的数据区分大小写
常数的书写方式是固定的，在SQL语句中直接书写的字符串、日期或者数字等称为常数, 字符串和日期用单引号”‘“标识。
单词需要用半角空格或者换行符来分隔

1-4 表的创建

数据库的创建(create database语句)

语法:

1	`create database <数据库名称>;`

表的创建(create table语句)

语法:

create table <表名>
(<列名1> <数据类型> <该列所需约束>,
 <列名2> <数据类型> <该列所需约束>,
 <列名3> <数据类型> <该列所需约束>,
 <列名4> <数据类型> <该列所需约束>,
            .
            .
            .
 <该表的约束1>, <该表的约束2>, ......);

NOT NULL 约束只能以列为单位进行设置。

命名规则

只能使用半角英文字母、数字、下划线作为数据库、表和列的名称。

名称必须以半角英文字母开头。

在同一个数据库中不能创建两个相同名称的表。

数据类型的指定

所有列都必须指定数据类型。

数据类型包括:

integer, 数字型, 不能存储小数
char, 字符型, char(10), 指定长度, 其为定长字符串
date, 日期型
varchar, 可变长字符串, 即使未达到最大长度，也不会用半角空格补足
每一列都不能存储与该列数据类型不符的数据。

约束的设置

约束是除了数据类型之外，对列中存储的数据进行限制或者追加条件的功能。

NULL 代表可以输入空白。

NOT NULL 代表不能输入空白.

1	`primary key (product_id)`

给product_id列设置主键约束。

主键约束 = 非空约束 + 唯一约束

1-5 表的删除和更新

表的删除(drop table)

语法:

1	`drop table <表名>;`

删除的表无法恢复。

表定义的更新(alter table)

语法:

1	`alter table <表名> add column <列的定义>;`

删除列的alter table 语句:

1	`alter table <表名> drop column <列名>;`

alter table 执行后也无法恢复。

向Product表中插入数据

begin transaction;

insert into <表名> values ();

commit;

表的修改

修改表名，不同数据库指令不同:

1	`alter table <originalname> rename to <name>`

第2章查询基础

查询时可以指定一个或多个查询条件。

2-1 select 语句基础

列的查询

通过 select 语句查询并选取出必要数据的过程称为匹配查询或查询(query).

基本语法:

1 2	`select <column name>, ... from <table name>;`

如:

1 2	`select product_id, product_name, purchase_price from Product;`

查询多列时，需要逗号分隔。

查询出表中所有列

1 2	`select * from <tablename>;`

使用 * 无法设定列的显示顺序。

SQL 语句可以随意使用换行符。

为列设定别名

使用as关键词。

1 2	`select product_id as id from Product`

别名若要使用中文需要用双引号(“)括起来。

从结果中删除重复行

通过 select 子句中使用 distinct:

1 2	`select distinct product_type from Product;`

使用 distinct 时，null 也被视为一类数据。

根据 where 语句来选择记录

select 语句通过 where 子句来指定查询数据的条件。

语法:

1
2
3

select <column name>, ...
 from <table name> 
 where <expr>;

首先通过 where 子句查询出符合指定条件的记录，然后再选取出 select 语句指定的列。

SQL 中子句的书写顺序是固定的，where 子句必须紧跟在 from 子句之后。

注释的书写方法

两种:

1行注释，用--
多行注释，用/* 和 */

2-2 算术运算符和比较运算符

算术运算符

如:

1
2
3

select product_name, sale_price,
       sale_price * 2 as "sale_price_x2"
  from Product;

四则运算符号:

需要注意NULL

所有包含 null 的计算，结果肯定是 null.

比较运算符

使用 = .

“不等于”比较运算符<>.

其他比较运算符:

>=
>
<=
<

对字符串使用不等号时的注意事项

在对字符串类型的数据进行大小比较时，使用的是和数字比较不同的规则。

按照字典顺序进行比较。以相同字符开头的单词比不同字符开头的单词更接近。

不能对null使用比较运算符

SQL 提供了专门用来判断是否为 null 的 is null 运算符。

如:

1
2
3

select product_name, purchase_price
    from Product
  where purchase_price is null;

若不是 null, 用 is not null 运算符。

2-3 逻辑运算符

not 运算符

需要和其他查询条件组合使用。

and 运算符和 or 运算符

and 运算符是“与”运算。

or 运算符是”或”运算。

通过括号强化处理

and 运算符优先于 or 运算符。

日期的一种格式 2009-09-11.

逻辑运算符和真值

比较运算符会把运算结果以真值的形式返回。

使用 and 运算符进行的逻辑运算称为逻辑积.

使用 or 运算符进行的逻辑运算称为逻辑和.

不能对 null 使用比较运算符。

含有 null 时的真值

真值中除真假之外的第三种值–不确定(unknown).

与通常的逻辑运算被称为二值逻辑.

SQL 中的逻辑运算被称为三值逻辑.

级别: 假 > 不确定 > 真.

尽量不使用 null, 其会使真值表更复杂。

第3章聚合与排序

3-1 对表进行聚合查询

聚合函数

SQL中用于汇总的函数:

count: 计算行数
sum: 列数据的合计值
avg: 列数据的平均值
max: 列数据的最大值
min: 列数据的最小值
用于汇总的函数称为聚合函数或者聚集函数.

所谓聚合, 就是将多行汇总于一行.

计算表中数据的行数

计算全部数据的行数:

1 2	`select count(*) from Product;`

计算 null 之外的数据的行数

将对象列设定为参数而不是 *.

1 2	`select count(purchase_price) from Product;`

使用星号是count函数特有的，其他函数不能将其作为参数。

计算合计值

1 2	`select sum(sale_price) from Product;`

所有的聚合函数，如果以列名为参数，那么在计算之前就已经把 null 排除在外了，因此无论有多少个 null 都会被无视。

计算平均值

1 2	`select avg(sale_price) from Product;`

计算最大值和最小值

1 2	`select max(sale_price), min(purchase_price) from Product;`

max/min 函数原则上可以适用于任何数据类型的列。

sum/avg 函数只适用于数值类型。

使用聚合函数删除重复值(关键字distinct)

1 2	`select count(distinct product_type) from Product;`

在参数中使用 distinct. 所有的聚合函数都可以使用。

3-2 对表进行分组

语法结构:

1
2
3

select <column name1>, <column name2>, <column name3>, ......
    from <table name>
  group by <column name1>, <column name2>, <column name3>, ......;

group by 子句中指定的列称为聚合键或者分组列.

group by 子句应写在 from 语句之后，有 where 语句也应该在 where 语句之后.

聚合键中包含 null 的情况

在结果中以空行的形式表现出来。

与聚合函数和 group by 子句有关的常见错误

使用聚合函数时，select 子句中只能存在以下三种元素:

常数
聚合函数
group by 子句中指定的列名
使用 group by 子句时，select 子句中不能出现聚合键之外的别名.

group by 子句也不能使用别名。

group by 子句结果的显示是无序的。

在 where 子句中不能使用聚合函数。只有 select 子句和 having 子句(以及order by子句) 中能够使用聚合函数。

distinct 和 group by 都能够删除重复数据。

3-3 为聚合结果指定条件

having 子句

where 只能指定记录(行)的条件，而不能用来指定组的条件.

having 子句用于指定集合的条件。

语法:

select <column name1>, <column name2>, <column name3>, ......
  from <table name>
  group by <column name1>, <column name2>, <column name3>, ......
  having <expr>;

顺序: select -> from -> where -> group by -> having

如:

select product_type, count(*)
  from Product
  group by procuct_type
  having count(*) = 2;

having 子句的构成要素

和包含 group by 子句时的 select 子句一样:

常数
聚合函数
group by 子句中指定的列名

相对于 having 子句，更适合写在 where 子句中的条件

where 子句 = 指定行所对应的条件
having 子句 = 指定组所对应的条件

where 子句和 having 子句的执行速度

通常情况下，将条件写在 where 子句中要比写在 having 子句中的处理速度快。

通过 WHERE 子句指定条件时，由于排序之前就对数据进行了过滤，因此能够减少排序的数据量。但 HAVING 子句是在排序之后才对数据进行分组的.

3-4 对查询结果进行排序

order by 子句

通常，从表中抽取数据，如果没有指定顺序，则为随机。

在 select 语句末尾添加 order by 子句来指定排列顺序:

1
2
3

select <column name1>, <column name2>, <column name3>, ......
  from <table name>
  order by <排序基准列1>, <排序基准列2>, ......;

书写顺序: select -> from -> where -> group by -> having -> order by

指定升序或降序

降序，在列名之后使用 desc(descendent) 关键词:

1
2
3

select product_id, product_name, sale_price, purchase_price
  from Product
  order by sale_price desc;

升序，使用 asc(ascendent) 关键词。

未指定时默认升序排序。

指定多个排序键

规则是优先使用左侧的键，如果该列存在相同值的话，再接着参考右侧的键.

null 的顺序

使用含有 null 的列作为排序键时，null 会在结果的开头或末尾汇总显示。

在排序键中使用显示用的别名

order by 子句中允许使用别名。

如:

1
2
3

select product_id as id, product_name, sale_price as sp, purchase_price
  from Product
  order by sp, id;

使用 having 子句时 select 语句的顺序:

from -> where -> group by -> having -> select -> order by

order by 子句中可以使用的列

可以使用在 select 子句中未使用的列和聚合函数。

不要使用列编号

列编号是指 select 子句中的列按照从左到右的顺序进行排序时所对应的编号(1, 2, 3,…).

第4章数据更新

4-1 数据的插入(insert语句的使用方法)

什么是 insert

create table 语句只负责创建表，但创建出的表中并没有数据。

insert 语句的基本语法

语法:

1	`insert into <table name> (column1, column2, ...) values (value1, value2, ...);`

将列名和值用逗号分开，分别括在()内，这种形式称为清单.

表名后面的列清单和 value 子句中的值清单的列数必须保持一致。

原则上，执行一次 insert 语句会插入一行数据。

列清单的省略

对表进行全列 insert 时，可以省略表名后的列清单。

插入 null

直接在 values 子句的值清单中写入 null.

想要插入 null 的列一定不能设置 not null 约束.

插入默认值

在 create table 语句中设置 default 约束。

默认值的使用方法通常有显式和隐式两种.

显式，在 values 子句中指定 default 关键字。 values ('0007', default, ...)
隐式，省略设定了默认值的列，列和值都要忽略
一般使用显式。

从其他表复制数据

执行 insert ... select 语句。

1
2
3

insert into Productcopy (product_id, product_name, product_type, sale_price)
select product_id, product_name, product_type, sale_price
from Product;

insert 语句的 select 语句中，可以使用 where 子句或者 group by 子句等人和语法(使用 order by 子句并不会产生任何效果).

4-2 数据的删除(delete语句的使用方法)

drop table 语句和 delete 语句

drop table 语句可以将表完全删除
delete 语句会留下表(容器), 而删除表中的全部数据

delete 语句的基本语法

语法:

1	`delete from <table name>`

指定删除对象的 delete 语句(搜索型delete)

语法:

1 2	`delete from <table name> where <expr>;`

delete 语句中不能使用 group by、having和 order by.

4-3 数据的更新(update语句的使用方法)

update 语句的基本语法

用于更改数据.

语法:

1 2	`update <table name> set <column name> = <expr>;`

将更新对象的列和更新后的值都记述在set子句中。

设置一整列.

指定条件的 uupdate 语句(搜索型update)

使用 where 子句:

1
2
3

update <table name>
  set <column name> = <expr>
  where <expr>;

使用 null 进行更新

将赋值表达式右边的值直接写为 null。

使用 update 语句可以将值清空为 null(但只限于未设置 not null 约束的列).

多列更新

两种写法:

第一种:

update <table name>
  set <column name> = <value>
  set <column name> = <value>
              .
              .
              .
  where <expr>

第二种:

1
2
3

update <table name>
  set (<column name>, <column name>, ...) = (<value>, <value>, ...)
  where <expr>

通常使用第一种方法。

4-4 事务

什么是事务(transaction)

事务是需要在同一个处理单元中执行的一系列更新处理的集合。

一个处理单元就是表之类的，一系列更新处理就是修改操作。

创建事务

语法:

事务开始语句 ;
  DML 语句① ;
  DML 语句② ;
  DML 语句③ ;
      .
      .
      .
事务结束语句（COMMIT 或者 ROLLBACK）;

各个 DBMD 都定义有事务的开始语句:

PostgreSQL: begin transaction

事务结束语句只有两种且通用:

commit
rollback
事务开始有默认开始的时间点.

commit 提交处理

相当于文件处理中的覆盖处理。

rollback 取消处理

相当于文件处理中的放弃保存。

事务处理何时开始

实际上，几乎所有的数据库产品的事务都无需开始指令.

区分事务:

每条SQL语句就是一个事务(自动提交模式)
直到用户执行commit或者rollback为止算作一个事务

默认使用自动提交模式的 DBMS 有 SQL Server、PostgreSQL和MySQL等.

ACID 特性

DBMS 的事务都遵循四种特性，将这四种特性的首字母结合起来统称为 ACID 特性。

原子性 Atomicity

指事务结束时，其中包含的更新要么全部执行，要么完全不执行。

一致性 Consistency

指事务中包含的处理，要满足数据库提前设置的约束, 如主键约束或者 not null 约束等。

隔离性 Isolation

指的是保证不同事务之间互不干扰的特性。

持久性 Durability

指在事务结束后，DBMS 能够保证该时间点的数据状态会被保存的特性。

常见为将执行记录(日志)保存在存储介质.

第5章复杂查询

5-1 视图

视图和表

视图和表的区别: 是否保存了实际数据。

使用视图时并不会将数据保存到存储设备中, 而且也不会将数据保存到其他任何地方.

视图保存的是从表中取出数据所使用的 select 语句.

视图的优点

无需保存数据
将频繁使用的 select 语句保存成视图，就不用重新书写

创建视图的方法

使用 create view 语句:

1
2
3

create view 视图名称(<视图列名1>, <视图列名2>, ......) 
as
<select语句>

select 语句中列的排列顺序和视图中列的排列顺序相同.

视图可以写在 select 语句的 from 中.

1 2	`select <column name1>, <column name2>, ... from <view name>;`

使用视图的查询

通常为两个步骤:

执行定义视图的 select 语句
根据得到的结果，执行在 from 子句中使用的视图的 select 语句

也就是说使用视图就是把视图放在 from 之后。

多重视图会降低 SQL 的性能.

视图的限制1 – 定义视图时不能使用 order by 子句

视图的限制2 – 对视图进行更新

定义视图的 select 语句满足某些条件，视图就可以被更新(即对视图使用 insert、delete、update等), 几个具有代表性的条件:

select 子句中未使用 distinct
from 子句中只有一张表
未使用 group by 子句
未使用 having 子句
视图和表需要同时进行更新，因此通过汇总得到的视图无法进行更新.

PostgreSQL 中的视图会被初始设定为只读, 若要允许更新:

create or replace rule insert_rule
as on insert
to <view name> do instead
insert  into ...

删除视图

使用 drop view 语句:

1	`drop view <view name>(<视图列名1>, ...)`

删除多重视图，即存在关联的视图:

1	`drop view <view name> cascade;`

cascade 是重叠的意思。

5-2 子查询

子查询就是一次性视图。

子查询将用来定义视图的 select 语句直接用于 from 子句中.

select product_type, cnt_product
  from (
    select product_type, count(*) as cnt_product
        from Product
        group by product_type
      ) as ProductSum;

这里的 Productsum 也是一次性名称.

select 语句的执行顺序，先执行 from 子句中的 select 语句，然后才执行外层的 select 语句。

增加子查询的层数

子查询的层数原则上没有限制.

尽量避免嵌套，其会使可读性和性能变差。

子查询的名称

原则上子查询必须设定名称, 需使用 as 关键词.

标量子查询 scalar subquery

什么是标量

标量就是单一的意思.

标量子查询的特殊限制为: 必须而且只能返回1行1列的结果。

也就是返回表中某一行的某一列的值。

标量子查询的返回值可以用在 = 或者 <> 这样需要单一值的比较运算符中.

在 where 子句中使用标量子查询

解决在 where 子句中不能使用聚合函数的问题.

select product_id, product_name, sale_price
  from Product
  where sale_price > (select avg(sale_price)
                        from Product);

执行顺序，首先执行内层的子查询，然后执行外层的查询。

标量子查询的书写位置

通常任何可以使用单一值的位置都可以使用。几乎所有的地方都可以使用.

使用标量子查询时的注意事项

即该子查询不能返回多行结果。

5-3 关联子查询

普通子查询和关联子查询的区别

使用关联子查询的解决方案

在子查询中添加的 where 子句的条件.

关联子查询也是用来对集合进行切分的

结合条件一定要写在子查询中

关联子查询就是有一个关联条件。

关联名称存在作用域.

第6章函数、谓词、CASE表达式

6-1 各种各样的函数

函数的种类

大致可以分为:

算数函数
字符串函数
日期函数
转换函数
聚合函数

算数函数

+
-
*
/
numeric数据类型, 格式为(全体位数，小数位数).

ABS – 绝对值

ABS(数值)

abs 是计算绝对值(absolute value)的函数.

abs 计算出某一列的绝对值。

绝大多数函数对于 null 都返回null.

MOD – 求余

1	`MOD(被除数，除数)`

只能对整数类型的列使用 MOD 函数。

ROUND – 四舍五入

1	`ROUND(对象数值，保留小数的位数)`

字符串函数

|| – 拼接

1	`字符串1 \|\| 字符串2`

LENGTH – 字符串长度

1	`LENGTH(字符串)`

LENGTH 是以字节为单位.

LOWER – 小写转换

1	`LOWER(字符串)`

UPPER – 大写转换

1	`UPPER(字符串)`

REPLACE – 字符串替换

1	`REPLACE(对象字符串，替换前字符串，替换后字符串)`

SUBSTRING – 字符串截取

1	`SUBSTRING(对象字符串 from 截取的起始位置 for 截取的字符数)`

日期函数

CURRENT_DATE – 当前日期

1	`current_date`

CURRENT_TIME – 当前时间

1	`current_time`

CURRENT_TIMESTAMP – 当前日期和时间

1	`current_timestamp`

EXTRACT – 截取日期元素

1	`EXTRACT(日期元素 from 日期)`

year, month, day, hour, minute, second.

转换函数

转换在 SQL 中主要有两层意思:

数据类型的转换，即 cast

值的转换

1	`cast(转换前的值 as 想要转换的数据类型)`

COALESCE – 将 null 转换为其他值

1	`coalesce(数据1，数据2，...)`

返回可变参数中左侧开始第一个不是 null 的值。

感觉不是转换。

6-2 谓词

什么是谓词(predicate)

谓词是函数的一种，是需要满足特定条件的函数，该条件就是返回值是真值(true)。

=, <, >, <> 等比较运算符，其正式名称就是比较谓词.

LIKE 谓词 – 字符串的部分一致查询

部分一致大体可以分为:

前方一致
中间一致
后方一致
部分一致，即字符串中的一部分相同。

前方一致查询

用来查询的字符串，与查询对象的起始部分相同。

中间一致

用来查询的字符串，与查询对象的部分相同。

后方一致

用来查询的字符串，与查询对象的末尾部分相同。

1
2
3

select * 
  from SampleLike
  where strcol like 'ddd%'

% 代表”0字符以上的任意字符串”.

_ 代表”任意一个字符”.

BETWEEN 谓词 – 范围查询

使用3个参数:

1
2
3

select product_name, sale_price
  from Product
 where sale_price BETWEEN 100 AND 1000;

IS NULL、IS NOT NULL – 判断是否为 NULL

IN 谓词 – OR 的简便用法

1
2
3

select product_name, purchase_price
  from Product
 where purchase_price IN(320, 500, 5000)

其否定形式为 NOT IN.

IN 和 NOT IN 无法取出 NULL 数据。

使用子查询作为 IN 谓词的参数

IN 和子查询

IN(NOT IN)谓词，可以使用子查询作为其参数。

select product_name, sale_price
  from Product
 where product_id IN (select product_id 
                        from ShopProduct
                       where shop_id = '000C');

NOT IN 和子查询

语法和 IN 一致。

EXIST 谓词

EXISTS谓词的使用方法

谓词的作用就是 “判断是否存在满足某种条件的记录”

EXIST 的参数

EXIST 左侧并没有参数.

EXIST 是只有1个参数的谓词，右侧参数通常为一个子查询。

子查询中的 select *

EXIST 只关心记录是否存在。

在 EXIST 的子查询中，常常书写 select *.

NOT EXIST

与 EXIST 相反，当”不存在”满足子查询中指定条件的记录时返回真。

6-3 CASE 表达式

什么是 CASE 表达式

case 作为条件分支。

CASE 表达式的语法

CASE 表达式的语法分为:

简单 case 表达式

搜索 case 表达式
搜索表达式包含了简单 case 表达式的全部功能。

case when <求值表达式> then <表达式>
     when <求值表达式> then <表达式>
     when <求值表达式> then <表达式>
                .
                . 
                . 
     else <表达式>
end

when 子句中的”<求值表达式>”就是类似”列 = 值”

直到返回为真为止.

CASE 表达式的使用方法

else子句省略不写时会默认为else null.

CASE 表达式的书写位置

CASE 是一个表达式，可以写在任意位置。

简单 CASE 表达式

会受条件的约束.

语法:

case <表达式>
     when <表达式> then <表达式>
     when <表达式> then <表达式>
     when <表达式> then <表达式>
                .
                . 
                . 
     else <表达式>
end

和搜索 case 表达式的不同之处在于，求值表达式变成了 case 后的表达式和 when 后面的表达式.

特定的 case 表达式

第7章集合运算

7-1 表的加减法

什么是集合运算

集合在数据库中表示记录的集合.

集合运算就是对满足同一规则的记录进行的加减等四则运算。

通过集合运算，可以得到两张表中记录的集合或者公共记录的集合。

用于集合运算的运算符称为集合运算符.

表的加法 – UNION

select product_id, product_name
  from Product
UNION
select product_id, product_name
  from Product2

结果会包含两张表的全部内容。也就是并集.

集合运算符也会除去重复的记录。

集合运算的注意事项

1 作为运算对象的记录的列数必须相同

也就是 select 后的列数。

2 作为运算对象的记录中列的类型必须一致

相同位置上的列必须是同一数据类型。

3 可以使用任何 select 语句，但 order by 子句只能在最后使用一次。

包含重复行的集合运算 – ALL 选项

在 UNION 后加 ALL 就会保留重复行.

select product_id, product_name
  from Product
UNION ALL
select product_id, product_name
  from Product2;

选取表中公共部分 – INTERSECT

选取两个记录集合中公共部分的交集(INTERSECT).

select product_id, product_name
  from Product
INTERSECT
select product_id, product_name
  from Product2
order by product_id;

保留重复项同样可以使用 INTERSECT ALL.

记录的减法 – EXCEPT

select product_id, product_name
  from Product
EXCEPT
select product_id, product_name
  from Product2
order by product_id;

结果只包含Product表中记录除去Product2表中记录之后的剩余部分。

7-2 联结(以列为单位对表进行联结)

什么是联结

UNION 和 INTERSECT 等会导致记录行数的增减。

联结(join)运算就是将其他表中的列添加过来。

内联结 – INNER JOIN

内联结要点1 – from 子句

1	`from ShopProduct AS SP INNER JOIN Product AS P`

内联结要点2 – on 子句

1	`ON SP.product_id = P.product_id`

起到 where 的作用.

ON 子句需书写到 from 和 where 之间。

内联结要点3 – select 子句

由于多表联结时容易发生混乱, 因此采用 “<表的别名>.<列名>” 的形式来指定列。

其实只有同时存在于两张表中的列必须使用这种形式。

内联结和 where 子句结合使用

select SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
  from ShopProduct AS SP INNER JOIN Product AS P
    ON SP.product_id = P.product_id
  where SP.shop_id = '000A';

使用联结运算将满足相同规则的表联结起来，WHERE、GROUP BY、HAVING、ORDER BY等工具都可以正常使用。

这张表只在 select 语句执行期间存在，select 语句执行之后就会消失。如果希望继续使用这张表，需将其创建为视图.

外联结 – OUTER JOIN

实现”从两张表中取出”的就是联结功能。

外联结也是通过 ON 子句的联结键将两张表进行联结，并从两张表中同时选取相应的列。

外联结要点1 – 选取出单张表中全部的信息

内联结只能选取同时存在于两张表中的数据。

对于外联结，只要数据存在于某一张表中，就能够读取出来。

外联结名称的又来也跟 null 有关，即”结果中包含原表中不存在的信息”.

外联结要点2 – 每张表都是主表吗?

最终的结果中会包含主表的所有数据。

指定主表的关键词是 left 和 right.

3张以上的表的联结

原则上联结表的数量并没有限制。

from ShopProduct as SP INNER JOIN Product as P
  ON SP.product_id = P.product_id
      INNER JOIN InventoryProduct as IP
          ON SP.product_id = IP.product_id

交叉联结 – CROSS JOIN(笛卡尔积)

交叉联结是所有联结的基础.

交叉联结时无法使用内联结和外联结中所有的 ON 子句，这是因为交叉联结是对两张表中的全部记录进行交叉组合。

结果中的记录数通常是两张表中行数的乘积。

联结的特定语法和过时语法

关系除法

集合运算中的除法通常称为关系除法.

第8章 SQL高级处理

8-1 窗口函数

什么是窗口函数

窗口函数也称为OLAP函数(Online Analytical Processing).

窗口函数的语法

1 2	`<窗口函数> OVER ([PARTITION BY <列清单>] ORDER BY <排序用列清单>)`

其中，[]中的内容可以省略。

能够作为窗口函数使用的函数

窗口函数大体分为两种:

能够作为窗口函数的聚合函数(SUM、AVG、COUNT、MAX、MIN)
RANK、DENSE_RANK、ROW_NUMBER等专用窗口函数

语法的基本使用方法 – 使用 RANK 函数

PARTITION BY 设定排序的对象范围。用于在横向上对表进行分组。

ORDER BY 指定按照哪一列、何种顺序进行排序. 与 select 语句末尾的 order by 一样，可以通过关键词 ASC/DESC 来指定升序和降序。决定纵向排序的规则。

通过 PARTITION BY 分组后的记录的集合可以称为窗口。窗口的意思为范围。

窗口函数兼具分组和排序两种功能。

select product_name, product_type, sale_price,
       RANK() OVER (PARTITION BY product_type
                         ORDER BY sale_price) AS ranking
  FROM Product;

无需指定 PARTITION BY

不指定时将整个表作为一个大的窗口使用.

1 2	`SELECT product_name, product_type, sale_price, RANK() OVER (ORDER BY sale_price) AS ranking FROM Product;`

将表中数据分为多个部分时使用 PARTITION BY.

专用窗口函数的种类

代表性的专用窗口函数:

RANK函数, 计算排序时，如果存在相同位次的记录，则会跳过之后的位次。如: 1位, 1位, 1位, 4位
DENDE_RANK, 不会跳过位次.如:1位, 1位, 1位, 2位
ROW_NUMBER函数，赋予唯一的连续位次. 如: 1位, 2位, 3位

专用窗口函数无需参数，因此通常括号中都是空的。

窗口函数的适用范围

原则上窗口函数只能在 select 子句中使用。

在 select 子句之外”使用窗口函数是没有意义的”.

作为窗口函数使用的聚合函数

1 2	`SELECT product_id, product_name, sale_price, SUM (sale_price) OVER (ORDER BY product_id) AS current_sum FROM Product;`

形成累计的统计方法。

以”自身记录(当前记录)”作为基准进行统计，就是将聚合函数作为窗口函数使用的最大特征。

计算移动平均

在窗口中指定更加详细的汇总范围的备选功能，即框架.

也就是可以指定那几行进行汇总。

指定框架

使用 ROW 和 PRECEDING 两个关键词，将框架指定为 “截止到之前~行”.

如”ROW 2 PRECEDING”:

自身(当前记录)
之前1行的记录
之前2行的记录
这样的统计方法称为移动平均(moving average).

关键词FOLLOWING指”截止到之后~行”.

主要是要找到当前行。

将当前记录的前后行作为汇总对象

同时使用 PRECEDING 和 FOLLOWING.

两个 ORDER BY

使用窗口函数时必须要在 OVER 子句中使用 ORDER BY. 但其只是用来决定窗口函数按照什么样的顺序进行计算。

在 select 语句的最后使用 order by 子句保证排序记录。

8-2 GROUPING 运算符

同时得到合计行

ROLLUP – 同时得出合计和小计

GROUPING 运算符包含以下3种:

ROLLUP
CUBE
GROUPING SETS

ROLLUP 的使用方法

1
2
3

SELECT product_type, SUM(sale_price) AS sum_price
    FROM Product
  GROUP BY ROLLUP(product_type);

GROUP BY() 被称为超级分组记录(super group row).

超级分组记录默认使用 NULL 作为聚合键。

将”登记日期”添加到聚合键中

GROUPING函数 – 让NULL更加容易分辨

GROUPING函数 – 用来判断超级分组记录的 NULL 的特定函数。

在其参数列的值为超级分组记录所产生的 NULL 时返回1，其他情况返回0.

SELECT GROUOING(product_type) AS product_type
        GROUPING(regist_date) AS regist_date, SUM(sale_price) AS sum_price
        FROM Product
      GROUP BY ROLLUP(product_type, regist_date);

CUBE – 用数据来搭积木

第9章通过应用程序连接数据库

9-1 数据库世界和应用程序世界的连接

数据库和应用程序之间的关系

系统 = 应用 + 数据库

驱动 – 两个世界之间的桥梁

驱动，实际上就是连接的意思。

驱动，用于解决更换编程语言或者DBMS而导致从头开始修改应用和SQL语句。

驱动是用来连接应用和数据库的非常小的特殊程序。

驱动的种类

DBMS 和编程语言的不同，使用的驱动程序也不一样。

现在广泛使用的驱动标准主要有 ODBC(Open DataBase Connectivity) 和 JDBC(Java Data Base Connectivity).

下载 Driver 的网站

.jar 扩展名是 Java 的可执行文件的扩展名。

9-2 Java 基础知识

src 是 源代码(source) 的简称。

SQL 中的字符串是使用单引号(‘)括起来的。

编译和程序执行

使用 javac 进行编译:

1	`javac *.java`

编译成功后会生成.class文件，其为可执行文件，称为”类文件”.

使用 java 命令执行程序

1	`java <类名>`

常见错误

大小写错误

Java 区分大小写。

使用全角空格

源代码中不能使用全角空格。

源文件的文件名和类名不一致

文件名必须和源代码第1行的类名一致。

命令名和文件名错误

9-3 通过 Java 联结 PostgreSQL

执行 SQL 语句的 Java 程序

Java 是如何从数据库中获取数据的

SQL

SQL基础教程 Notes

http://example.com/2022/08/10/SQL基础教程-Notes/

作者

Jie

发布于

2022年8月10日

许可协议

Ubuntu 22.04 无法调节亮度上一篇

图灵机下一篇