PostgreSQL基础

发表于 更新于 分类于 ， ， ， 阅读次数： 本文字数： 18k 阅读时长 ≈ 16 分钟

1. 数据库基础知识

1.1. 什么是数据库

数据库(Database, DB)是一个长期存储在计算机内的, 有组织的, 有共享的, 统一管理的数据集合.

  • 保管数据的”仓库”
  • 数据管理的方法和技术

1.2. 数据表

数据库表: 是一系列二维数组的集合

  • 横向的行(记录, 行)
  • 纵向的列(字段, 属性, 列)

1.3. 数据类型

常用的数据类型有:

  • 整数数据类型
  • 浮点数数据类型
  • 日期/时间数据类型
  • 字符串数据类型
  • 二进制数据类型

1.4. 数据库系统构成

数据库系统有 3 个主要的组成部分:

  • 数据库
  • 数据库管理系统
  • 数据库应用程序

1.5. SQL 语言

sql 是对数据库进行查询和修改操作语言.
SQL 语言包括 4 个部分
DDL: DROP, CREATE, ALTER 等语句.
DML: INSERT, UPDATE, DELETE 语句.
DQL: SELECT 语句.
DCL: GRANT, REVOKE, COMMIT, ROLLBACK 等语句.

1.6. 常用的数据库访问技术

目前流行的数据库访问技术如下:

  • ODBC: Open Database Connectivity(开放数据库连接)
  • JDBC: Java Database Connectivity(Java 数据库连接)
  • ADO.NET: .NET 框架中用于和数据库交互的类库
  • PDO: PHP Data Object(PHP 语言访问数据库技术)

2. PostgreSQL 数据库介绍

2.1. PostgreSQL 数据库发展历程

  • 1973年，美国国防部与加州大学伯克利分校合作开发了一个研究项目，叫Ingres。
  • 1985年，研究项目终止，随后开源，并且命名叫Postgre
  • 1986 年: 加州大学伯克利分校开发了一个叫 Postgre 的关系数据库服务器
  • 1994 年: 增加了 SQL 语言解析器, 命名为 Postgres95
  • 1996 年: 更名为 PostgreSQL, 版本号从 6.0 开始
  • 2005 年: 发布 8.0 版本, 开始支持 Windows 系统环境
  • 2011 年: 发布 9.1 版本, 增加了很多创新性功能, 具备强大的可扩展性
  • 目前: 已经发布到 9.4 版本, 增加对于 NoSQL 的支持

2.2. PostgreSQL 数据库特点

PostgreSQL 数据库的主要特点如下:

  • 免费
  • 速度快
  • 平台可移植性
  • 丰富的接口
  • 面向对象特性
  • 安全性
  • 配合的开源软件很多

3. PostgreSQL 数据库环境搭建

3.1. PostgreSQL 数据库软件下载

PostgreSQL 官网: https://www.postgresql.org

下载地址: https://www.postgresql.org/download/

3.2. 在 Windows 平台搭建 PostgreSQL 环境

3.2.1. 前置条件

要使用图形安装向导执行安装，您必须具有超级用户或管理员权限。
以下部分将指导您在 Windows 主机上安装 PostgreSQL。
要启动安装向导，请具有足够的权限并双击安装程序图标；如果出现提示，请提供密码。
注意，在某些版本的 Windows 中，要使用管理员权限调用安装程序，需要右键单击安装程序图标并从上下文菜单中选择以管理员身份运行。

3.2.2. 下载安装包

访问地址: https://www.postgresql.org/download/windows/, 点击”Download the installer”超链接进入 postgreSQL 安装包下载页面.
或者直接打开 https://www.enterprisedb.com/downloads/postgres-postgresql-downloads 进入下载页面

当前教程使用的版本:postgresql-14.6-1-windows-x64

3.2.3. 安装步骤

  1. 双击打开安装向导,点击下一步

  2. 修改 Installation directory 或者使用默认安装位置

  3. 选择需要安装的组件, 默认全选, 初学者可以使用默认配置, 有经验的用户可以根据自己的需求选择要安装的组件

    • Postgresql Server
    • pgAdmin: Postgresql 客户端工具
    • Stack builder
    • Command line Tools: 命令行工具

  4. 选择数据存储目录

  5. 设置超级管理员密码

  6. 设置端口, 可使用默认端口 5432

  7. 选择 locale, 可以使用默认 locale

  8. 安装程序将会提示安装信息

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    Installation Directory: C:\Program Files\PostgreSQL\14
    Server Installation Directory: C:\Program Files\PostgreSQL\14
    Data Directory: C:\Program Files\PostgreSQL\14\data
    Database Port: 5432
    Database Superuser: postgres
    Operating System Account: NT AUTHORITY\NetworkService
    Database Service: postgresql-x64-14
    Command Line Tools Installation Directory: C:\Program Files\PostgreSQL\14
    pgAdmin4 Installation Directory: C:\Program Files\PostgreSQL\14\pgAdmin 4
    Stack Builder Installation Directory: C:\Program Files\PostgreSQL\14
    Installation Log: C:\Users\{user}\AppData\Local\Temp\install-postgresql.log

  9. 点击下一步, 直到安装完成

3.2.4. 开启或关闭 postgreSQL 服务

可以在 windows 服务管理界面, 将 PostgreSQL 服务设置为手动启动, 自动启动, 或关闭.

3.3. 在 Linux 平台搭建 PostgreSQL 环境

查看 PostgreSQL 版本

1
2
3

postgres --version

3.4. 在 Mac 平台搭建 PostgreSQL 环境

4. pgAdmin 客户端工具使用

4.1. 使用 pgAdmin 工具连接 PostgreSQL 数据库

一些检测检查

检查 postgresql.conf 配置文件

连接远程数据库服务需要注意如下一些问题:

  • 关闭服务器机器防火墙
  • 修改服务器端连接监听(postgresq.conf 配置文件)
  • 修改连接认证方式(pg_hba.conf 配置文件)

4.2. pgAdmin 工具常用功能介绍

  • query tools: sql 查询工具

5. PostgreSQL 数据库, 数据表, 数据类型和运算符介绍

5.1. 使用 pgadmin 工具操作数据库对象

如何创建数据库

  1. 点击右侧导航树, 选择数据库 node, 右击弹出菜单, 在菜单中选择新建数据库
  2. 填充数据库名, 选择所有者
  3. 点击创建

如何修改数据库

  1. 点击右侧导航树,选中要修改的数据库, 右击弹出菜单, 在菜单中选择新建数据库
  2. 在弹出的界面中, 修改数据库属性
  3. 点击应用

如何删除数据库

  1. 点击右侧导航树,选中要修改的数据库, 右击弹出菜单, 点击删除

5.2. 使用 SQL 语言操作数据库对象

如何创建数据库

1
2
3

create database test_database;

创建数据库时指定参数

1
2
3

create database test_database with owner= postgre encoding = 'utf-8';

如何修改数据库

1
alter database test_database rename to dev_database;

如何修改数据库参数

1
alter database dev_database connection limit 20;

如何删除数据库

1
drop database dev_database;

5.3. 使用 pgAdmin 工具操作数据表对象

如何创建数据表

  • 选择 数据库>模式>public> 数据表, 右击菜单, 选择新建数据表

  • 在弹出的对话框中数据数据库表名称, 所有者, 模式,

  • 添加字段

  • 点击确定.

如何修改数据表

  • 在数据库导航栏中, 选择修改数据表, 按图像界面指引操作.

如何删除数据表

  • 选择数据包, 右击菜单, 选择删除

5.4. 使用 SQL 语言操作数据表对象

如何创建数据表

1
2
3
4
5
6
create table student (
    id int,
    name varchar(30),
    birthday data,
    score numeric(5,2)
)

如何修改数据表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 修改表名称

alter table student rename to student1;

-- 修改数据表中字段名称

alter table student rename id to bh;

-- 修改数据表中字段类型

alter table student alter column name type varchar(40);

-- 修改数据表中的字段

alter table student drop column birthday;

-- 在数据表中添加字段

alter table student add column address varchar(200);


如何删除数据表

1
2
3
4
5
6
7
8
9

-- 如何删除数据表

drop table student

-- 删除表前检查表是否存在, 从而避免sql错误信息
drop table if exists student


5.5. PostgreSQL 常用数据类型介绍

5.5.1. 数值类型

数值类型由 2 字节、4 字节或 8 字节的整数以及 4 字节或 8 字节的浮点数和可选精度的十进制数组成。

下表列出了可用的数值类型。

名字存储长度描述范围
smallint2 字节(16bit)小范围整数-32768 到 +32767
INT(INTEGER)4 字节(16bit)常用的整数-2147483648 到 +2147483647
bigint8 字节(16bit)大范围整数-9223372036854775808 到 +9223372036854775807
decimal可变长用户指定的精度，精确小数点前 131072 位；小数点后 16383 位
NUMERIC(m, n)可变长用户指定的精度，精确, 任意精度类型小数点前 131072 位；小数点后 16383 位
real4 字节(32bit)可变精度，不精确6 位十进制数字精度
double precision8 字节(64bit)可变精度，不精确15 位十进制数字精度
smallserial2 字节(16bit)自增的小范围整数1 到 32767
serial4 字节(32bit)自增整数1 到 2147483647
bigserial8 字节(64bit)自增的大范围整数1 到 9223372036854775807

5.5.2. 布尔类型

PostgreSQL 支持标准的 boolean 数据类型。

boolean 有”true”(真)或”false”(假)两个状态， 第三种”unknown”(未知)状态，用 NULL 表示。

名称存储格式描述
boolean1 字节true/false

5.5.3. 日期与时间类型

下表列出了 PostgreSQL 支持的日期和时间类型。

名字存储空间描述最低值最高值分辨率
timestamp [ (p) ] [ without time zone ]8 字节日期和时间(无时区)4713 BC294276 AD1 毫秒 / 14 位
timestamp [ (p) ] with time zone8 字节日期和时间，有时区4713 BC294276 AD1 毫秒 / 14 位
date4 字节只用于日期4713 BC5874897 AD1 天
time [ (p) ] [ without time zone ]8 字节只用于一日内时间00:00:0024:00:001 毫秒 / 14 位
time [ (p) ] with time zone12 字节只用于一日内时间，带时区00:00:00+145924:00:00-14591 毫秒 / 14 位
interval [ fields ] [ (p) ]12 字节时间间隔-178000000 年178000000 年1 毫秒 / 14 位

5.5.4. 字符串类型

CHAR/CHARACTER(N) //固定长度字符串, 不足补空格
VARCHAR(n)/ CHARACTER VARING(n) // 变长字符串, 有长度限制
TEXT //变长字符串, 无长度限制

5.5.5. 货币类型

money 类型存储带有固定小数精度的货币金额。

numeric、int 和 bigint 类型的值可以转换为 money，不建议使用浮点数来处理处理货币类型，因为存在舍入错误的可能性

名字存储容量描述范围
money8 字节货币金额-92233720368547758.08 到 +92233720368547758.07

5.5.6. 选择正确的数据类型

主要目的: 优化存储, 提高数据库性能

  • 正确使用整数类型和浮点数类型
  • 日期与时间类型
  • CHAR 和 VARCHAR 之间的特点和选择

5.6. PostgreSQL 运算符介绍

5.6.1. 运算符概述

5.6.2. 算术运算符

算术运算符包含内容:

  • 加 (+)
  • 减 (-)
  • 乘 (*)
  • 除 (/)
  • 求余 (模运算, %)

5.6.3. 比较运算符

比较运算符常常用于比较大小

运算符作用
=等于
<>(!=)不等于
<=小于等于
>=大于等于
>大于
<小于
LEAST在有两个或者多个参数时, 返回最小值
GREATEST在有两个或者多个参数时, 返回最大值
BETWEEN AND判断一个值是否落在两个值之间
IN判断一个值是否是列表中的任意一个值
LIKE通配符匹配

示例:

1
2
3
4
5
6
7
8
9
10
11

select 1=0, '2', 1<>2, 4!=4, 2>=1;

select 2 between 1 and 3;

select 2 in (2, 3, 4), 2 in (4,5)

select 'abc' like 'a%', 'abc' like '_b_', 'abc' not like '%d'

select least(6,2, 3),  greatest(6,2, 3)

5.6.4. 逻辑运算符

逻辑运算符包含内容:

  • NOT (逻辑非)
  • AND (逻辑与)
  • OR (逻辑或)

示例

1
2
3
4
5
6
7
8
9

-- 一般在Postgresql中, 1作为真来处理, 0作为假来处理
-- n作为真来处理, y作为假来处理
select not '1', not 'y', not '0', not 'n';

select '1' and 'y', '1' and '0', '0' and 'n';

select '1' or 'y', '1' or '0', '0' or 'n';

5.6.5. 运算符优先级

优先级运算符
最低= (赋值运算符)
OR
AND
NOT
BETWEEN, CASE, WHEN, THEN, ELSE
=(比较运算符), >=, >, <=, <, <>, !=, IS, LIKE, IN
-(减号), +
*, /, %
- (负号)
最高!

6. PostgreSQL 函数, 索引和视图

6.1. PostgreSQl 常用函数介绍

6.1.1. 常用的数值函数

函数名称函数作用
AVG()返回某列的平均值
COUNT()返回某列行数
MAX()返回某列的最大值
MIN()返回某列的最小值
SUM()返回某列的值之和

6.1.2. 常用的字符串函数

函数名称函数作用
LENGTH(S)计算字符串长度
CONCAT(S)字符串合并函数
LTRIM(s)/RTRIM(s)/TRIM(s)删除字符串空格函数
REPLACE(s, s1, s2)字符串替换函数
SUBSTRING(s,n, len)获取子串函数

6.1.3. 常用的日期和时间函数

函数名称函数作用
EXTRACT(type FROM D)获取日期指定值函数
CURRENT_DATE获取当前日期函数
CURRENT_TIME获取当前时间函数
NOW()获取当前日期时间函数

示例:

1
2
3
4
5

select current_date, current_time, now();

select extract(year from birthDate), extract(month from birthDate), extract(day from birthDate) from employee;

6.1.4. 自定义函数语法

基本语法格式:

1
2
3
4
5
6
7
8

CREATE FUNCTION               //声明创建函数
    add(integer, integer)     //定义函数名称, 参数类型
RETURNS integer               //定义函数返回值
AS 'select $1 + $2;'          //定义函数体
LANGUAGE SQL                  //用以实现函数的语言
RETURN NULL ON NULL INPUT;    //定义参数为NULL时处理情况

示例

1
2
3
4
5
6
7
8
9
-- 定义函数
create or replace function concat_string(integer, varchar, data)
return varchar as
'SELECT $1 || $2 || $3'
language sql
returns null on null input
-- 调用函数
select e_no, e_name, e_hiredate, concat_string(e_no, e_name, e_hiredate)
from employee;

6.2. PostgreSQL 数据库索引介绍

6.2.1. 什么是索引？

索引（Index）是帮助PostgreSQL高效获取数据的数据结构，就好比书的目录，加快数据库的查询速度。

6.2.2. 索引的作用

在数据库系统中建立索引主要有以下作用：

1.快速读取数据

2.保证数据记录的唯一性

3.实现表与表之间的参照完整性

4.在使用orderby ，group by子句进行检索时，索引可以减少排序和分组的时间。

6.2.3. 索引的分类

6.2.3.1. 按存储结构分类
索引名称使用场景
B-tree 索引适合处理那些能够按顺序存储数据
Hash 索引只能处理简单的等于比较
GiST 索引一种索引架构
GIN 索引反转索引, 处理包含多个值的键
6.2.3.2. 按数据的存储方式（物理结构）分类
  • 聚集索引：聚簇索引的顺序就是数据的物理存储顺序，索引与数据存放在同一个文件中。
  • 非聚集索引：非聚簇索引的顺序与数据的物理存储顺序不同，索引与数据存放在不同的文件。
6.2.3.3. 按应用层次分类

  1. 普通索引

    最基本的索引，它没有任何限制，用于加速查询

  2. 唯一索引

    索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一

  3. 主键索引

    是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。

  4. 组合索引

    指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

  5. 全文索引

    主要用来查找文本中的关键字，而不是直接与索引中的值相比较。

    fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。

    fulltext索引配合match against操作使用，而不是一般的where语句加like。

    它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。

6.2.4. 什么情况下该创建索引？

  • 频繁作为查询条件的字段应该创建索引；

  • 查询中与其他表有关联的字段，例如外键关系；

  • 在经常需要排序(order by),分组(group by)和的distinct 列上加索引，可以加快排序查询的时间,

6.2.5. 什么情况下不创建索引？

  1. 查询中很少使用到的字段 不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求.

  2. 值重复率高的字段不适合建索引（比如性别、百万级数据时比如26个字母）
    数据很少的字段也不应该建立索引,比如 一个性别字段 0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率

  3. 定义为text和image和bit数据类型的列不应该增加索引,

  4. 当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系

6.2.6. 索引的创建和删除

示例:

1
2
3
4
5
6
7
--创建索引, 默认是B-tree索引
create index emp_name_index on employee(e_name);
--删除索引
drop index emp_name_index;
--创建索引
create index emp_name_index on employee(e_name);

6.2.7. 使用索引的优点和缺点

优点:

  • 提高数据的查询速度
  • 加速与表之间的连接
  • 在分组和排序字句进行数据检索,可以减少查询时间中分组和排序时所消耗的时间

缺点:

  • 创建和维护索引需要消费时间
  • 需要占用磁盘空间
  • 不合适的索引或索引过多，会降低增删改的效率

一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快)

6.3. PostgreSQL 数据库视图介绍

6.3.1. 视图的含义

视图是从一个或几个基本表（或视图）组成的虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

6.3.2. 视图的创建

示例:

1
2
3
4
5
6
7
8
9
10
--创建视图
create view v_emp_dev as
select e_no, e_name, e_salary, e_hireDate
from  employee
where dept_no = 10 order by e_salary desc;

-- 使用视图

select * from v_emp_dev;

6.3.3. 视图的删除

1
drop view v_emp_dev

6.3.4. 视图的作用

视图的作用:

  • 简单化
  • 安全性
  • 逻辑数据独立性

7. PostgreSQL 数据操作和数据表约束介绍

7.1. 简单数据插入操作

7.1.1. 向单表中插入数据

示例

1
insert into student values(1, '张三', '1990-01-01', 3.85)

7.1.2. 向数据表中指定字段插入数据

示例:

1
insert into student (id, name, birthday) values (2, '李四', '1990=01-02');

7.2. 批量数据插入操作

使用 INSERT 语句批量向数据表中插入数据

1
2
3
4
5
6

insert into student(id, name, birthday) values
(3, '张三1', '1990-01-01'),
(4, '张三2', '1990-01-02'),
(5, '张三3', '1990-01-03');

使用 SELECT 语句向数据表中插入数据

1
2
3
4
5
6
7
8
-- 不字段特定字段
insert into student_new
select * from student

-- 指定字段
insert into student_new(id, name, birthday)
select id, name, birthday from student

7.3. 数据更新操作

7.3.1. 指定条件更新数据

1
update student set name='李四' where id = 2;

7.3.2. 批量更新数据

1
update student set score = 0;

7.3.3. 将指定结果更新到对应字段

1
update student set score = 1.1+2.3 where id = 1;

7.4. 数据删除操作

7.4.1. 数据删除操作

示例:

1
2
3
4
5
6
7
8
9

delete from student where id = 4;

delete from student where birthday between '1990-01-01' and '1990-02-01'

-- 清空数据表

delete from student_new

7.4.2. TRUNCATE 数据清空操作

1
truncate table student_new;

7.4.3. DELETE 与 TRUNCATE 操作区别

DELETETRUNCATE
执行速度慢快
可指定条件可以不可以
语句分类DMLDDL
可以回滚事务可以不可以
删除操作记录日志记录不记录

7.5. PostgreSQL 数据表主键和外键介绍

7.5.1. 如何定义主键约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14

create table emp(
   id int primary key,
   name varchar(30),
   salary numeric(9,2)
)
-- 或者
create table emp(
   id int,
   name varchar(30),
   salary numeric(9,2),
   constraint pk_emp primary key(id)
)

7.5.2. 如何定义外键约束

示例:

1
2
3
4
5
6
7
8
9
10
11
12
13
create table dept (
   id int primary key,
   name varchar(40)
)

create table employee(
   id int primary key,
   name varchar(30),
   salary numeric(9,2),
   deptId int,
   constraint fk_emp_dept foreign key(deptId) references dept(id)
)

7.5.3. 主键约束和外键约束的作用

主键约束作用:

  • 唯一标识一条记录
  • 提高数据的检索效率

外键约束作用:

  • 保证数据的完整性
  • 提高数据的检索效率

7.6. PostgreSQL 数据表非空约束, 唯一约束和默认值约束介绍

7.6.1. 如何定义非空约束

1
2
3
4
5
create table emp4 (
   id int primary key,
   name varchar(30) not null,
   salary numeric(9,2)
)

7.6.2. 如何定义唯一约束

1
2
3
4
5
6
create table emp4 (
   id int primary key,
   name varchar(30) not null,
   phone varchar(30) unique,
   salary numeric(9,2)
)

7.6.3. 如何定义默认值约束

1
2
3
4
5
create table emp4 (
   id int primary key,
   name varchar(30) not null,
   salary numeric(9,2) default 0.0
)

7.6.4. 非空约束, 唯一约束和默认值约束的作用

约束作用:

  • 维护数据的完整性
  • 在业务层面保证数据正确性

8. PostgreSQL 数据查询操作

8.1. 简单数据查询操作

8.1.1. 查询语句语法

基本语法格式

1
2
3
4
5
6
7
8
9
SELECT
    {*|<字段列表>}                                   //查询结果字段内容
FROM
   <表1>,<表2>...                                   // 查询数据表
   [WHERE <表达式>]                                 // where 查询条件表达式
   [GROUP BY <group by definition>]                 //group by 数据分组
   [HAVING <expression> [{<operator><expression>}]]
   [ORDER BY <order by definition>]                  //查询结果排序
   [LIMIT [<offset>,] <row count>]                  //限制结果显示数量

准备数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
-- 创建演示数据表结构
-- 创建dept表
CREATE TABLE dept (
   d_no INT PRIMARY KEY,  -- 部门编号
   d_name VARCHAR(30),    --部门名称
   d_location VARCHAR(300) -- 部门所在地址
)

-- dept 表初始化数据
INSERT INTO dept VALUES(10, '开发部', '北京市海淀区');
INSERT INTO dept VALUES(20, '测试部', '北京市东城区');
INSERT INTO dept VALUES(30, '销售部', '上海市');
INSERT INTO dept VALUES(40, '财务部', '广州市');
INSERT INTO dept VALUES(50, '运维部', '武汉市');
INSERT INTO dept VALUES(60, '集成部', '南京市');

-- 创建employee表
CREATE TABLE employee (
   e_no INT PRIMARY KEY,
   e_name VARCHAR(30) NOT NULL,
   e_gender CHAR(2) NOT NULL,
   dept_no INT,
   e_job VARCHAR(50) NOT NULL,
   e_salary NUMERIC(9, 2),
   e_hireDate Date,
   constraint fk_emp_deptno FOREIGN KEY (dept_no) REFERENCES dept(d_no)
)

-- 初始化employee表
INSERT INTO employee VALUES(100, '赵志军', 'f', 10, '开发工程师', 5000, '2010-01-01');
INSERT INTO employee VALUES(101, '张铭雨', 'f', 10, '开发工程师', 6000, '2012-04-04');
INSERT INTO employee VALUES(102, '许峰', 'f', 10, '开发经理', 6000, '2012-04-04');
INSERT INTO employee VALUES(103, '王嘉琪', 'm', 20, '测试工程师', 4500, '2012-04-04');
INSERT INTO employee VALUES(104, '李江新', 'f', 20, '测试工程师', 5000, '2012-04-04');
INSERT INTO employee VALUES(105, '张海影', 'f', 20, '测试经理', 5000, '2012-04-04');
INSERT INTO employee VALUES(106, '马恩波', 'f', 30, '销售人员', 3000, '2012-04-04');
INSERT INTO employee VALUES(107, '李惠敏', 'm', 30, '销售人员', 5000, '2012-04-04');
INSERT INTO employee VALUES(108, '马爽爽', 'm', 30, '销售经理', 9000, '2012-04-04');
INSERT INTO employee VALUES(109, '史晓云', 'm', 30, '销售高级经理', 12000, '2012-04-04');
INSERT INTO employee VALUES(110, '刘燕凤', 'm', 40, '财务人员', 3000, '2012-04-04');
INSERT INTO employee VALUES(111, '王科', 'f', 40, '财务人员', 3500, '2012-04-04');
INSERT INTO employee VALUES(112, '李林英', 'f', 40, '财务经理', 5000, '2012-04-04');
INSERT INTO employee VALUES(113, '李杨', 'f', 10, '实习工程师', NULL, '2012-04-04');
INSERT INTO employee VALUES(114, '李刚', 'f', NULL, '实习工程师', NULL, '2012-04-04');
INSERT INTO employee VALUES(115, '王林', 'f', NULL, '实习工程师', NULL, '2012-04-04');

8.2. 简单查询操作

1
2
3
4
5
6
7
8
9
10
11
12
13

-- 查询employee中所有字段内容
select * from employee;

-- 查询指定字段数据

select e_no, e_name,dept_no, e_job from employee;

-- 为表和字段指定别名

select e_no, e.e_name as name,dept_no, e_job from employee as e;


8.3. 单表指定条件查询操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

-- 工资少于5000的雇员
select e_no, e.e_name as name,dept_no, e_job from employee as e where e_salary <5000 ;
-- 女性雇员信息
select e_no, e.e_name as name,dept_no, e_job from employee as e where gender = 'F';

-- 雇员测试和销售部门的

select e_no, e.e_name as name,dept_no, e_job from employee as e where dept_no in (20,30);

-- 查找姓李的
select e_no, e.e_name as name,dept_no, e_job from employee as e where e_name like '李%';

-- 查找入职日期在2012-04-01到2012-04-30的雇员
select e_no, e.e_name as name,dept_no, e_job from employee as e where e_hireDate between  '2012-04-01' and '2012-04-30';

8.4. 单表指定条件复杂查询操作

8.4.1. 查询空值内容

1
2
3
select e_no, e.e_name as name,dept_no, e_job from employee as e where e_salary is null ;

select e_no, e.e_name as name,dept_no, e_job from employee as e where e_salary is not null ;

8.4.2. AND, OR 多条件查询

1
2
3
4
5
6
7
8
select e_no, e.e_name as name,dept_no, e_job from employee as e
where e_gender = 'f' and dept_no = 10;

select e_no, e.e_name as name,dept_no, e_job from employee as e
where e_gender = 'f' and dept_no in (10, 20);

select e_no, e.e_name as name,dept_no, e_job from employee as e
where e_gender = 'f' and (dept_no =10 or dept_no =20);

8.4.3. 查询结果集排序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

select e_no, e.e_name as name,dept_no, e_job from employee as e
order by e_salary; -- 默认是升序(asc)排序


select e_no, e.e_name as name,dept_no, e_job from employee as e
order by e_salary desc;


select e_no, e.e_name as name,dept_no, e_job from employee as e
order by e_salary asc, e_hireDate desc;

-- 空值显示在最前面
select e_no, e.e_name as name,dept_no, e_job from employee as e
order by e_salary asc nulls first;


-- 空值显示在最后面
select e_no, e.e_name as name,dept_no, e_job from employee as e
order by e_salary asc nulls last;

8.4.4. LIMIT 关键字查询

1
2
3
4
5
6
7
8
9

-- 显示前5条数据
select e_no, e.e_name as name,dept_no, e_job from employee as e
limit 5

-- 从第5条后的5条数据
select e_no, e.e_name as name,dept_no, e_job from employee as e
limit 5 offset 5

8.5. 多表连接查询操作

8.5.1. INNER JOIN 连接查询操作

1
2
3
4
5
6
7
-- 隐式内连接
select e_no, e.e_name as name,dept_no, d.name, e_job from employee as e, dept as d
where dept_no = d_no;

-- 显式内连接
select e_no, e.e_name as name,dept_no, d.d_name, e_job from employee as e inner join dept as d on dept_no = d_no

8.5.2. LEFT JOIN 连接查询操作

1
2
3
4
5
6
-- 左连接(left join)
select e_no, e.e_name as name,dept_no, d.d_name, e_job from employee as e left join dept as d on dept_no = d_no

-- 左连接(left outer join)
select e_no, e.e_name as name,dept_no, d.d_name, e_job from employee as e left outer join dept as d on dept_no = d_no

8.5.3. RIGHT JOIN 连接查询操作

1
2
3
4
5
6
7

-- 右连接(right join)
select e_no, e.e_name as name,dept_no, d.d_name, e_job from employee as e right  join dept as d on dept_no = d_no

-- 右连接(right outer join)
select e_no, e.e_name as name,dept_no, d.d_name, e_job from employee as e right outer  join dept as d on dept_no = d_no

8.6. 子查询操作

8.6.1. EXISTS 关键字子查询操作

1
2
3
4
5
6
7
8
9
-- exists
select * from employee as e where exists
(select d_no from dept where d_no = e.dept_no and d_name='开发部')

-- not exists

select * from employee as e where not exists
(select d_no from dept where d_no = e.dept_no and d_name='开发部')

8.6.2. IN 关键字子查询操作

1
2
3
4

select * from employee as e where  dept_no in
(select d_no from dept where d_name='开发部')

8.6.3. 标量子查询操作

标量子查询是圆括号中的常规 SELECT 查询，仅返回一个值：带有一个列的一行。

执行此查询，返回值将在外部查询中使用。如果子查询返回零行，则子查询表达式的值为 null。如果它返回多行，将返回错误.

子查询可引用父查询中的变量，这将在子查询的任何一次调用中充当常量。

您可在需要表达式的大部分语句中使用标量子查询。标量子查询在下列情况下是无效表达式：

  • 作为表达式的默认值

  • 在 GROUP BY 和 HAVING 子句中

1
2
3

select e_no, e_name, (select d_name || ' ' || d_location from dept where dept_no= d_no)  from employee as e

8.7. 查询结果集合合并操作

8.7.1. 使用 UNION ALL 对查询结果进行合并

1
2
3
select e_no, e_name, dept_no, e_salary from employee where dept_no in (10, 20)
union all
select e_no, e_name, dept_no, e_salary from employee where e_salary > 5000

8.7.2. 使用 UNION 对查询结果进行合并

1
2
3
select e_no, e_name, dept_no, e_salary from employee where dept_no in (10, 20)
union
select e_no, e_name, dept_no, e_salary from employee where e_salary > 5000

union 会去掉重复的记录

8.7.3. 使用 UNION ALL 和 UNION 之间的区别

union 会去掉重复的记录 而 union all 只是简单的合并结果集
union 合并操作效率更高

UNIONUNION ALL
去除重复记录可以不可以
执行速度慢快