PostgreSQL核心之SQL基础学习

一、SQL简介

SQL(Structured Query Language)是结构化查询语言的简称，是操作数据库的语言

SQL 是一门 ANSI 的标准计算机语言，用来访问和操作数据库系统。

SQL 可与数据库管理系统协同工作，比如 DB2、MS SQL Server、Oracle等

SQL 语句用于取回和更新数据库中的数据,原则上SQL语句都会使用分号结尾。

SQL根据操作目的可以分为DDL、DML和DCL。

（一）SQL语句及其种类

SQL 用关键字、表名、列名等组合而成的一条语句（SQL 语句）来描述操作的内容。根据对RDBMS 赋予的指令种类的不同，SQL 语句可以分为DML、DCL、DDL三类。

1、DDL

DDL（Data Definition Language，数据定义语言） 用来创建或者删除存储

数据用的数据库以及数据库中的表等对象。DDL 包含以下几种指令。

CREATE：创建数据库和表等对象

DROP：删除数据库和表等对象

ALTER：修改数据库和表等对象的结构

2、DML

DML（Data Manipulation Language，数据操纵语言） 用来查询或者变更表中的记录。

DML 包含以下几种指令：

SELECT：查询表中的数据

INSERT：向表中插入新数据

UPDATE：更新表中的数据

DELETE：删除表中的数据

3、DCL

DCL（Data Control Language，数据控制语言） 用来确认或者取消对数据库中的数据进行的变更。

除此之外，还可以对RDBMS 的用户是否有权限。操作数据库中的对象（数据库表等）进行设定。

DCL 包含以下几种指令：

COMMIT：确认对数据库中的数据进行的变更

ROLLBACK：取消对数据库中的数据进行的变更

GRANT：赋予用户操作权限

REVOKE：取消用户的操作权限

（二）SQL 的基本书写规则

常数的书写方式是固定的。字符串和日期常数需要使用单引号（’）括起来。

数字常数无需加注单引号（直接书写数字即可）。

字符串格式 ‘abc’

日期格式有多种（’26 Jan 2010’ 或’10/01/26’ 等），建议格式’2010-01-26’

数字格式不需要使用任何符号标识，写成1000 这样即可

单词需要用半角空格或者换行来分隔

SQL 语句的单词之间需使用半角空格或换行符来进行分隔。

如下这种未加分隔的语句会发生错误，无法正常执行。

○ CREATE TABLE Product（）

× CREATETABLE Product

× CREATE TABLEProduct

但是不能使用全角空格作为单词的分隔符，否则会发生错误，出现无法预期的结果。

（三）标准SQL和特定的SQL

每隔几年，ANSI（美国国家标准协会）或ISO（国际标准化组织）等便会修订SQL 的标准，进行语法的修订并追加新功能。

1986 年，ANSI 首次制定了SQL 的标准，之后又进行了数次修订。

修订后的标准以修订年份来命名，例如SQL:1999、SQL:2003、SQL:2008 等。

以这些标准为基准的SQL 就是标准SQL。

SQL 的标准并不强制“每种RDBMS 都必须使用”。虽然支持标准SQL的RDBMS 越来越多，但还是存在标准SQL 无法执行的情况。这时就需要使用只能在特定RDBMS 中使用的特殊SQL 语句。

1、SELECT

在PostgreSQL可以通过SQL命令select来完成用户数据的查询，基本语法如下：

1.    select ColumnName from TableName [where] ;
   

关于该命令，继续扩展常见使用：

1、字段输出别名：columnname可以使用as关键字定义输出时的别名

2、过滤：表名之后可以跟where条件进行过滤

3、分组、排序：可以通过group by、order by完成输出的分组、排序

4、限制输出：可以通过 limit .. offset ..完成输出个数的限制

列和表别名(Aliases)可以提高复杂SQL的可读性，可以减少输入量

示例:

1. SELECT ename, dname FROM emp e, dept d  WHERE e.deptno = d.deptno;
   

2、INSERT

在PostgreSQL可以通过SQL命令insert into来完成用户数据的插入

基本语法如下：

1. insert into TableName (Column1,..) values (Value1,..）；
   

备注：该命令中可以不写Column，在表名后直接跟Value关键字，这表示insert插入的数据按照创建表时定义的column顺序依次插入，这种情况下允许value值的个数小于字段个数，缺少的字段值以null替代插入

3、UPDATE

在PostgreSQL可以通过SQL命令update来完成用户数据的更新

基本语法如下：

1. update TableName set ColumnName = NewValue,…… [Where conditions];
   

关于该命令：

1、若不使用where条件进行过滤，则update命令会完成所有的目标字段值的更新

2、ColumnName = NewValue可以设置多组，相互之间以逗号分隔，从而同时完成多条更新

4、DELETE

在PostgreSQL可以通过SQL命令delete来完成用户数据的插入，基本语法如下：

1.    delete from TableName [Where conditions];
   

可以使用where条件过滤，这表示删除该表中的所有数据

（四）标识符和关键字

数据库名是一个标识符，表名也是一个标识符，还有一种标识符是：分隔符和引号。是由引号包围的任意字符组成的。

分隔符也是一种标识符，不是一个关键字，所以”select”可以作为一个表名或者列名。没有引号的select是一个关键字，当作为一个表名或列名的时候会导致解析错误。

引号标识符能包含任何的字符，除了编码为0的字符。

示例：以下两句sql是等效的。

UPDATE my_table SET a = 5;

UPDATE “my_table” SET “a” = 5;

(五)SQL Functions

可以在 SELECT 语句以及WHERE 中使用，包括

1. String Functions
   
2. Format Functions
   
3. Date & Time Fuctions
   
4. Aggregate Functions
   
5. **示例**
   
6. SELECT lower(name) FROM departments;
   
7. SELECT * FROM departments WHERE lower(name) = 'development';
   

（六）常量的转义

在字符串前写字母E (大写或者小写)的方法声明C风格的转义字符串常量

例如：SELECT E’\tfoo’; 将返回“__foo”

例如：select E’\‘; 将返回“\” 请问：SELECT ‘\’;返回什么？

位串常量：以B开头加单引号包括的0和1组成位串常量

例如：SELECT B’1001’::int; 将返回9

以X开头加十六进制数组成

请问：SELECT X’0B’::int; 将返回11