从零开始学PostgreSQL (七) & PostgreSQL SQL入门

最新推荐文章于 2024-04-30 20:30:42 发布
最新推荐文章于 2024-04-30 20:30:42 发布
阅读量346 收藏 11
点赞数 6
文章标签： postgresql sql 数据库 oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhiqi_l163991102/article/details/136225861
版权

一、SQL语句语法简介

1. 语句的分类

SQL命令一般分为DQL、DML、DDL三类。

  • DQL：数据查询语句，基本就是SELECT查询命令，用于数据查询。
  • DML：Data Manipulation Language，即数据操纵语言，主要用于插入、更新、 删除数据，所以也分为INSERT、UPDATE、DELETE三种语句。
  • DDL：Data Definition Language，即数据定义语言，简单来说，是用于创建、删除、修改表、索引等数据库对象的语言。

2. 词法结构

每次执行的SQL可以由多条SQL命令组成。多条SQL命令之间由分号()分隔。

SQL命令由一系列记号组成，这些记号可以由关键字、标识符、双引号包围的标识符、常量和单引号包围的文本常量、特殊的字符等组成。SQL命令中可以有注释，这些注释在PostgreSQL中等同于空白。

举例来说，下面的命令从SQL语法上来说是合法的：

SELECT * FROM OSDBA_TABLE01; UPDATE OSDBA_TABLE SET COL1 = 614; INSERT INTO OSDBA_TABLE VALUES (232, 'hello osdba");

该SQL由3条命令组成。在SQL中，多行命令也可以写在一行中，当然也可以写在多行中，单条命令也可以占用多行。

SQL命令并未像计算机语言一样严格地明确标识哪些是命令、哪些是操作数或参数。SQL语法可以帮助用户比较直观地理解其意思。比如，查询一个表的数据的命令就是由 “SELECT+要查询的各列+FROM表”这样的语法组成的。后面会详细叙述SQL的用法。

二、DDL语句

1. 建表语句

表是关系型数据库中最基本的对象，数据库中的表与实际生活中的二维表格很相似， 表中有很多列也有很多行，每一列有一个名称，不同的列有不同的数据类型，比如，列可能是数字、文本字符串，也可能是日期类型。建表语句的简单语法如下：

CREATE TABLE table_name ( col01_namme data_type, col02_namme data_type, col03_namme data_type, col04_namme data_type, );

其中“CREATE”“TABLE”为关键字，是不变的，从字面上意思也很好理解，表示创建表，“tablename”表示表名，“col01name”“col02name”“col03name”“col04- name”分别表示列名。“datatype”表示数据类型，不同的数据库系统有不同的数据类型名称，即使是同一种整数类型，在不同的数据库系统中类型名称也有所不同。变长的字符串在大多数数据库中都可以使用varchar类型，比如PostgreSQL、MySQL和Oracle 数据库等。整型数据在PostgreSQL和MySQL中都可以使用int类型。日期类型的类型名称一般为“date”。例如，要创建一张分数表“score” ，包括“学生名称(studentname)”“语文成绩(chinesescore)”“数学成绩(mathscore)”“考试日期(test_date)”4列， 创建该表的SQL语句如下：

CREATE TABLE score ( student_name varchar(40), chinese_score int, math_score int, test_date date );

如果已按前面介绍的安装步骤安装了数据库，现在就可以使用psql工具连接到PostgreSQL数据库了，执行上面的建表语句，命令如下：

postgres=# CREATE TABLE score ( postgres(#   student_name varchar(40), postgres(#   chinese_score int, postgres(#   math_score int, postgres(#   test_date date postgres(# );

在psql中，可使用“\d”显示数据库中有哪些表，示例如下：

postgres=# \d         List of relations Schema | Name | Type | Owner   --------+-------+-------+---------- public | score | table | postgres (1 row) ​ postgres=#

上述结果就是我们所建的表。

使用“\d score”可以显示这张表的定义情况：

postgres=# \d score                         Table "public.score"   Column     |         Type         | Collation | Nullable | Default ---------------+-----------------------+-----------+----------+--------- student_name | character varying(40) |           |         | chinese_score | integer               |           |         | math_score   | integer               |           |         | test_date     | date                 |           |         | ​ postgres=#

显示列的类型“character varying(40)”实际上等同于“varchar(40)”， “int”的意思也与“integer”是一样的。

建表时可以指定表的主键，主键是表中行的唯一标识，这个唯一标识是不能重复的。

在创建表的语句中，可以在列定义后面用“primary key”主键来指定该列为主键，如下面的学生表所示：

CREATE TABLE student(no int primary key, student_name varchar(40), age int);

一个隐含的索引“student_pkey”

postgres=# \d student                       Table "public.student"   Column   |         Type         | Collation | Nullable | Default --------------+-----------------------+-----------+----------+--------- no           | integer               |           | not null | student_name | character varying(40) |           |         | age         | integer               |           |         | Indexes:    "student_pkey" PRIMARY KEY, btree (no)

2. 删除表语句

删除表的语法比较简单，语法格式如下：

DROP TABLE table_name;

其中“table_name”表示要删除的表的表名。假设要删除前面建的表“student”，则可以使用下面的SQL语句：

DROP TABLE student;

三、DML语句

DML语句用于插入、更新和删除数据，主要包含INSERT语句、UPDATE语句、 DELETE语句。

1. 插入语句

使用下面的语句可以向前面建的学生表(student)中插入数据：

INSERT INTO student VALUES(1, '张三', 14)；

由此可以看出，INSERT语句的语法为：以“INSERT INTO”关键字为首，后面跟表名，然后再跟“VALUES”关键字，最后是由小括号括起来的以逗号分隔的各列数据，数据的顺序与定义表时表列的顺序相同。当然，也可以在表名后指定要插入的数据列的顺序，SQL语句如下：

INSERT INTO student(no, age, student_name) VALUES(2, 13, '李四');

在插入数据时，也可以不为某些列插入数据，此时这些列的数据会被置空，SQL语句如下：

INSERT INTO student(no, student_name) VALUES(2, '王二');

如果在psql中执行了下面的语句，就可以使用SELECT语句查询数据了：

SELECT * FROM student;

查询内容如下

postgres=# SELECT * FROM student; no | student_name | age ----+--------------+----- 2 | 李四 | 13 3 | 王二 | (2 rows)

从上面的查询结果可以看出，在插入数据时，未提供的列数据会被置为NULL。

2. 更新语句

假设要把student表中所有学生的年龄(age)更新为“15”，则更新语句如下：

UPDATE student SET age = 15;

从上面的语句可以看出，更新语句以“UPDATE”关键字开始，后面跟表名，然后是 “SET”关键字，表示要设置的数据，再后面就是要设置的数据表达式“age=15“ ，设置数据的表达式也很简单，格式是“列名=数据”。

实际执行的效果如下：

``` postgres=# UPDATE student SET age = 15; UPDATE 2 postgres=# SELECT * FROM student; no | student_name | age ----+--------------+----- 2 | 李四 | 15 3 | 王二 | 15 (2 rows)

postgres=# ```

在更新数据时，还可以指定过滤表达式“WHERE”，从而指定更新哪条或哪些数据， 比如，要将学号(no)为“3”的学生的年龄更新为14岁，则使用如下语句：

UPDATE student SET age =14 WHERE no = 3;

执行结果如下

``` postgres=# UPDATE student SET age =14 WHERE no = 3; UPDATE 1 postgres=# SELECT * FROM student; no | student_name | age ----+--------------+----- 2 | 李四 | 15 3 | 王二 | 14 (2 rows)

postgres=# ```

在SET子句中，还可以同时更新多个列的值，如下所示：

UPDATE student SET age =13, student_name='王五' WHERE no = 3;

执行结果如下

``` postgres=# UPDATE student SET age =13, studentname='王五' WHERE no = 3; UPDATE 1 postgres=# SELECT * FROM student; no | studentname | age ----+--------------+----- 2 | 李四 | 15 3 | 王五 | 13 (2 rows)

postgres=# ```

3. 删除语句

删除学号(no)为“3”的学生的记录的语句如下：

DELETE FROM student WHERE no = 3;

由此可见，删除语句比较简单，以“DELETE FROM”开始，后面跟表名，然后再加一个“WHERE”子句用于指定要删除的记录。

当然也可以没有“WHERE”子句，这表明要删除整个表的数据。删除表student表中所有数据的语句如下：

DELETE FROM student;

四、DQL语句

1. 单表查询语句

查询student表中所有数据的语句如下：

select no, student_name, age from student;

由此可见，“SELECT”是关键字，表示查询，后面跟多个列名，各列之间使用逗号分隔；其后的“FROM”是关键字，后面跟表名。列可以是表的列名，也可以是一个表达式：

select age+5 from student;

表达式中可以包括表的列，也可以与表列无关：

select no, 3+5 from student;

当表达式与表列无关时，在PostgreSQL和MySQL中可以不使用“FROM表名”，这样一来就可以作为计算器使用了：

select 55+88;

如果想查询表中所有列的数据，则可以使用“*”代表所有列，如下所示：

select * from student；

2. 过滤条件查询

SELECT语句后面可以指定WHERE子句，用于指定要查询哪条或哪些记录，比如， 要查询学号为3的学生记录，其SQL语句如下：

SELECT * FROM student where no=3；

WHERE子句中也可以使用大于、小于表达式，比如，想查询年龄大于或等于15岁的学生记录，查询语句如下：

SELECT * FROM student where age >= 15;

3. 排序

使用排序子句可以对查询结果进行排序，排序子句是在SELECT语句后面加上 “ORDER BY”子句，比如，想将查询结果按年龄排序，则查询语句如下：

SELECT * FROM student ORDER BY age；

排序子句"ORDER BY"应该在WHERE子句之后,如果顺序错了,执行时会报错:

``` SELECT * FROM student ORDER BY age WHERE age >= 15;

ERROR: syntax error at or near "WHERE" LINE 1: SELECT * FROM student ORDER BY age WHERE age >= 15; ```

把"ORDER BY"子句放到"WHERE"子句后面就不会报错了:

SELECT * FROM student WHERE age >= 15 ORDER BY age;

还可以按多个列对查询结果进行排序，比如，根据“age”和“student_name”两个列来排序：

SELECT * FROM student ORDER BY age,student_name;

也可以在排序子句的列名后加“DESC”进行倒序排序：

SELECT * FROM student ORDER BY age DESC;

4. 分组查询

如果需要统计不同年龄的学生人数，可以使用分组查询，分组查询子句的关键字为 “GROUP BY”，用法如下：

SELECT age, count(*) FROM student GROUP BY age;

从上面的查询语句中可以看出，使用“GROUP BY”语句时需要使用聚合函数，常用的聚合函数有“count”“sum”等。

5. 多表关联查询

多表关联查询也称表join。假设有一张班级表“class” ’，建表语句如 CREATE TABLE class(no int primary key， class_name varchar(40))；

表中的“no”表示班级编号，“class_name”表示班级名称。

现插入一些测试数据：

``` postgres=# INSERT INTO class VALUES(1，'初二(1)班')；

INSERT 0 1

postgres=# INSERT INTO class VALUES(2，'初二(2)班')；

INSERT 0 1

postgres=# INSERT INTO class VALUES(3，'初二(3)班')；

INSERT 0 1

postgres=# INSERT INTO class VALUES(4，'初二(4)班')；

INSERT 0 1

postgres=# SELECT * FROM class； ```

还有一张学生表"student",建表语句如下:

CREATE TABLE student(no int primary key, student_name varchar(40), age int, class_no int);

同样插入一些测试数据:

``` osdba=# INSERT INTO student VALUES(1, '张三', 14, 1); INSERT 0 1

osdba=# INSERT INTO student VALUES(2, '吴二', 15, 1); INSERT 0 1

osdba=# INSERT INTO student VALUES(3, '李四' , 13, 2); INSERT 01

osdba=# INSERT INTO student VALUES(4, '吴三', 15, 2); INSERT 0 1

osdba=# INSERT INTO student VALUES(5, '王二', 15, 3); INSERT 0 1

osdba=# INSERT INTO student VALUES(6, '李三', 14, 3); INSERT 0 1

osdba=# INSERT INTO student VALUES(7, '吴二', 15, 4); INSERT 01

osdba=# INSERT INTO student VALUES(8, '张四', 14, 4); INSERT 01

osdba=# SELECT * FROM student; ```

假设想查询每个学生的名字与班级名称的关系，那么就需要关联查询两张表：

SELECT student_name， class_name FROM student， class WHERE student.class_no = class.no；

表关联查询就是在WHERE子句中加上需要关联的条件(两张表关联)：

WHERE student.class_no = class.no；

由于两张表中有些列的名称相同，如在表“student”中“no”表示学生号，而在表 “class”中表示班级号，所以在关键条件中要明确使用“表名”加“列名”来唯一定位某一列。如果输入的表名比较长，可以给表起个别名，SQL语句如下：

SELECT student_name, class_name FROM student a, class b WHERE a.class_no = b.no;

在上面的语句中， 表“student”的别名为“a”，表“class”的别名为“b”，此时条件表达式中“b。no”就代表表“class”中的“no”列。

还可以在关联查询的WHERE子句中加上其他过滤条件，如下所示：

SELECT student_name, class_name FROM student a, class b WHERE a.class_no = b.no AND a.age > 14;

6. 子查询

当一个查询是另一个查询的条件时，称之为子查询。主要有4种语法的子查询：

  • 带有谓词IN的子查询：expression ［NOT］ IN (sqlstatement)。
  • 带有EXISTS谓词的子查询：［NOT］ EXISTS (sqlstatement)。
  • 带有比较运算符的子查询：comparison(>，<，=，=)(sqIstatement)。
  • 带有ANY(SOME)或ALL谓词的子查询：comparison ［ANYIALLISOME］(sqlstatement)。

我们还用前面例子中班级表“class”和学生表“studtent”中的数据。

下面用带有谓词IN的子查询来查询“初二(1)班”的学生记录：

SELECT * FROM student WHERE class_no in (select no FROM class where class_name = '初二(1)班')；

上面的查询也可以用带EXISTS谓词的子查询来实现:

SELECT * FROM student s WHERE EXISTS (SELECT 1 FROM class c WHERE s.class_no=c.no AND c.class_name = '初二(1)班');

此查询还可以用带有比较符(这里用的是“=”)的子查询来实现：

SELECT * FROM student WHERE class_no = (SELECT no FROM class c WHERE class_name = '初二(1)班')

此查询还可以用带有ANY(SOME)或ALL谓词的子查询来实现：

SELECT * FROM student WHERE class_no = any(SELECT no FROM class c WHERE class_nam e= '初二(1)班')

但如果我们要查询两个班级的学生记录，不能使用带有等于“=”比较符的子查询：

``` SELECT * FROM student WHERE no = (SELECT no FROM class c WHERE class_name in ('初二(1)班'，'初二(2)班')；

ERROR： more than one row returned by a subquery used as an expression ```

上面的查询报错说子查询不能返回多行。这种不能返回多行的子查询也称标量子查询，标量子查询不仅能嵌套在WHERE子句中，也可以嵌套在SELECT的列表中，如我们要查询每个班级学生的最大年龄，则可以用如下SQL语句：

SELECT no, class_name, (SELECT maxage) as max_age FROM student s WHERE s.no= c.no) a s max_age FROM class c;

查询两个班级的学生记录时用带有ANY(SOME)谓词的子查询就没有问题了，示例如下：

SELECT * FROM student WHERE class_no = any(SELECT no FROM class c WHERE class_nam e in(初二(1)班，初二(2)班)；

五、其他SQL语句

1. INSERT...SELECT语句

使用INSERT。SELECT语句可以把一张表中的数据插入另一张表中，该语句属于 DML语句。

假设创建了一张学生表的备份表“student_bak”，建表语句如下：

CREATE TABLE student_bak(no int primary key， student_name varchar(40)， age int， class_no int)；

可以使用下面的语句把数据备份到备份表中：

INSERT INTO student_bak SELECT * FROM student；

演示如下：

``` INSERT INTO student_bak SELECT * FROM student； INSERT 0 8

SELECT * FROM student_bak； ```

2. UNION语句

使用UNION语句可以把从两张表中查询出来的数据合在一个结果集下，示例如下：

SELECT * FROM student WHERE no = 1 UNION SELECT * FROM student_bak where no = 2；

上面的语法比较简单，把两个SQL语句用“UNION”关键字连接起来就可以了。

查询结果如下：

SELECT * FROM student WHERE no = 1 UNION SELECT * FROM student_bak where no = 2；

如果不想合并结果集中的相同记录，可以使用UNION ALL语句，示例如下：

SELECT * FROM student WHERE no = 1 UNION ALL SELECT * FROM student_bak where no = 1；

3. TRUNCATE TABLE语句

TRUNCATE TABLE语句的用途是清空表内容。不带WHERE条件子句的DELETE语句也表示清空表内容，从执行结果来看，两者实现了相同的功能，但两者实现的原理是不一样的。TRUNCATE TABLE语句是DDL语句，即数据定义语句，相当于用重新定义一个新表的方法把原表的内容直接丢弃了，所以TRUNCATE TABLE执行起来会很快；

而DELETE语句是DML语句，我们可以认为DELETE语句是把数据一条一条地删除，所以DELETE语句删除多行数据时执行起来比较慢。

如果想把student_bak表中的数据清除，可以使用如下命令：

TRUNCATE TABLE student_bak；

「已注销」
关注
  • 6
    点赞
  • 踩
  • 11
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
PostgreSQL9从零开始学
07-23
资源名称：PostgreSQL 9从零开始学内容简介：PostgresQL9是一个开源的、全功能的对象关系型数据库。刘增杰、张少军编写的这本《PostgreSQL9从零开始学(视频教学版)》循序渐进地介绍了PostgresQL9数据库应用与开发的...
从零开始学PostgreSQL (六) & PostgreSQL psql工具
CoderChronicle的博客
02-19 878
SQL命令一般分为DQL、DML、DDL三类。DQL：数据查询语句，基本就是SELECT查询命令，用于数据查询。DML：Data Manipulation Language，即数据操纵语言，主要用于插入、更新、 删除数据，所以也分为INSERT、UPDATE、DELETE三种语句。DDL：Data Definition Language，即数据定义语言，简单来说，是用于创建、删除、修改表、索引等数据库对象的语言。
从零开始学PostgreSQL (一) & 初步认识PostgreSQL数据库
优秀小白熊的博客
01-16 873
theme: juejin 1.1 什么是Postgresql PostgreSQL 数据库是功能强大的开源数据库，它支持丰富的数据类型(如JSON和 JSONB类型、数组类型)和自定义类型。PostgreSQL数据库提供了丰富的接口，可以很方便地扩展它的功能，如可以在GIST框架下实现自己的索引类型，支持使用C语言写自定义函数、触发器，也支持使用流行的编程语言写自定义函数。PL/Perl...
PostgreSQL学习总结（1）—— PostgreSQL 入门简介与安装
热门推荐
科技D人生
07-28 3万+
PostgreSQL 是一个免费的对象-关系数据库服务器(ORDBMS)，在灵活的BSD许可证下发行。PostgreSQL 开发者把它念作post-gress-Q-L。PostgreSQL 的 Slogan 是 "世界上最先进的开源关系型数据库"。 什么是数据库？ 数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。所以，现在我们使用关.
Ubuntu 使用VSCode中的PostgreSQL插件从零开始学习PostgreSQL
熹微橙光的博客
11-02 638
Ubuntu 使用VSCode中的PostgreSQL插件从零开始学习PostgreSQL一句话总结加载PostgreSQL数据库使用VSCode PostgreSQL插件 一句话总结 本文讲述了Ubuntu20.04如何使用VSCode中的PostgreSQL插件以及PostgreSQL Tutorial（需要fanqiang）提供的配套数据库文件学习PostgreSQL的基本操作 加载PostgreSQL数据库 PostgreSQL Tutorial提供了完整的数据库文件，名为dvdrental（DVD
PostgreSQL 9从零开始学
03-05
PostgreSQL 9从零开始学
PostgreSQL 9 从零开始学.pdf
05-23
PostgreSQL 9 从零开始学.pdf PostgreSQL 9 从零开始学.pdf
017、Python+fastapi，第一个Python项目走向第17步：ubuntu24.04 无界面服务器版下安装nvidia显卡驱动
最新发布
浪淘沙jkp的专栏
04-30 1304
新的ubuntu24.04正式版发布了，前段时间玩了下桌面版，感觉还行，先安装一个服务器无界面版本吧安装时有一个openssh选择安装，要不然就不能ssh远程，我就是没选，后来重新安装ssh。另外一个就是安装过程中静态ip设置下在etc/netplan 文件夹下，有一个yaml文件，我的是50-cloud-init.yaml，先用ip a看看network:ethernets:enp3s0:routes:version: 2。
攻防演练作为红方：postgresql提权之UDF提权 ，及其好用的工具
keyboard专栏
04-28 437
在 PostgreSQL 中，用户定义的函数（UDF）提权是一种常见的提升权限的方法，尤其是当你已经获得了数据库的访问权限但需要更高级别系统权限时。UDF 提权涉及在数据库中创建或使用函数，这些函数可以执行系统级别的命令，从而允许攻击者在数据库服务器上执行任意代码。
SQL底层执行过程
qq_14886109的博客
04-28 840
SQL底层执行过程，INNODB，二阶段提交
【SQL Server】入门教程-基础篇(二)
m0_74229735的博客
04-29 802
本章要讲的就是Sql Server的高级语言，还有后续。
Postgresql源码（127）投影ExecProject的表达式执行分析
InternalsOf
04-30 786
无论是投影还是别的计算，表达式执行的入口和计算逻辑都是统一的，这里已投影为分析表达式执行的流程。
PostgreSQL 怎么查看记录数据的查看或者变更的数据库日志
keyboard专栏
04-28 606
在 PostgreSQL 中，可以通过多种方式配置和查看记录数据变更或查询的日志。
【SQL】ACID事务与隔离级别
KX_52517的博客
04-29 905
本文是对数据库事务acid特性和隔离级别的介绍。
sql面试题
qq_24099547的博客
04-28 580
表student中记录学生的成绩信息, 要求只保留学生的最新一条成绩记录。
【SQL】SQL语句执行顺序
Just Do IT
04-25 534
用一个例子说明SQL语句在逻辑执行计划时候的执行顺序，实际物理计划可能由于不同数据库优化策略的不同而略有差异。今天写一个相当基础的知识点，主要是之前对这块比较模糊，也没有细究，这次一并记录一下。
SQL常见约束及外键约束删除更新行为
fjf_666的博客
04-28 1136
SQL常见约束及外键约束删除更新行为案例
postgresql中&&
07-08
在PostgreSQL中，"&&"运算符表示数组的重叠操作符。它用于判断两个数组是否有重叠的元素。以下是一个示例： ```sql SELECT array[1, 2, 3] && array[2, 3, 4]; ``` 在上述示例中，"array[1, 2, 3] && array[2, 3, 4]"表示判断两个数组中是否有共同的元素。如果有重叠的元素，则返回true，否则返回false。 此外，"&&"运算符也可用于范围类型的重叠比较。例如： ```sql SELECT '[1,5]'::int4range && '[3,7]'::int4range; ``` 上述示例中的范围"[1,5]"和"[3,7]"有重叠部分，因此返回true。 需要注意的是，"&&"运算符只能用于数组和范围类型的比较，不能直接用于其他数据类型。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

「已注销」

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值