软件开发工具：第12章 PostSQL 9

1、郑州大学信息工程学院第12章 PostSQL 9点击添加文本郑州大学信息工程学院12.1 PostgreSQL 简介12.2 PostgreSQL 9的安装与配置12.3 数据库的基本操作12.4 数据表的基本操作点击添加文本12.6 数据查询12.7 综合练习）12.8 相关阅读：数据备份与还原12.9 本章小结12.5 插入、更新与删除数据12.1 PostgreSQL 简介PostgreSQL 是一个自由的对象-关系数据库服务器(数据库管理系统)，它在灵活的 BSD-风格许可证下发行。它提供了相对其他开放源代码数据库系统(比如 MySQL 和 Firebird)，和专有系统(比如 Ora

2、cle、Sybase、IBM 的 DB2 和 Microsoft SQL Server)之外的另一种选择。PostgreSQL 不寻常的名字导致一些读者停下来尝试拼读它，特别是那些把SQL拼读为sequel的人。PostgreSQL 开发者把它拼读为 post-gress-Q-L。它也经常被简略念为 postgres。12.2 PostgreSQL 9的安装与配置12.2.1 安装【例12.1】下载并安装PostgreSQL 9。使用浏览器打开/download/，这是PostgreSQL官方下载地址，选择安装平台，本书以Windows版PostgreSQL为例，所以点击【Windows】超链

3、接按钮。（2）在打开的新页面中，点击【Download】超链接按钮，找到最新版版本下方的图标这两个图标分别可以下载Windows 32位、64位版PostgreSQL，请根据自己的系统选择对应版本。双击安装程序图标，默认安装即可。12.2.2 使用pgAdmin III连接PostgreSQLgAdminIII是PostgreSQL默认的图形化管理软件，通过此管理软件可以连接本地和远程的PostgreSQL数据库，支持增删查改数据库及其表、视图等操作。下面介绍如何使用pgAdminIII连接默认的PostgreSQL数据库。【例12.2】使用pgAdmin III连接PostgreSQL。（1

4、）打开pgAdminIII。从开始菜单中找到PostgreSQL程序组，找到并打开pgAdminIII。打开后的界面如图12-1所示。软件界面功能区分为以下几个部分：菜单栏、快捷按钮栏、对象浏览器、标签栏、SQL窗口栏、状态栏。12-1 pgAdmin 主界面12-2 输入密码12-3 对象浏览器（2）找到对象浏览器下的【服务器组】-【服务器】-【PostgreSQL9.4(localhost：5432)】，双击该条目，打开如图12-2所示的对话框。输入安装时设置的密码，点击【确定】进行连接，此处可以勾选【保存密码】，这样下次连接时可以不用再次输入密码。（3）如果密码输入正确，此时的对象浏览器

5、更新，展开后出现如图12-3所示的界面。至此，我们已经成功连接到默认的数据库引擎，并且可以看到此时的数据库引擎中，有一个数据库，名为postgres，这个数据库下无任何表、触发器或者视图等内容。12.3 数据库的基本操作我们一般为不同的应用操作各自的数据库，所以下面介绍如何在pgadminIII界面下，创建、修改和删除数据库。12.3.1 创建数据库在创建数据库前，首先要打开pgAdminIII，并连接上数据库服务器。【例12.3】使用pgAdminIII创建数据库。（1）右键对象浏览器中的【数据库】，在弹出菜单中选择【新建数据库】。（2）填写【新建数据库】对话框中的【属性】标签。【名称】填写

6、“my_db”，【所有者】选择“postgres”，【注释】中填写任意文本即可。（3）填写【新建数据库】对话框中的【定义】标签。所有参数可以不用修改。【字符排序】和【字符分类】可以选择“Chinese (Simplified)_Peoples Republic of China.936”。【连接数限制】的“-1”代表了不限制连接数。（4）填写【新建数据库】对话框中的【变量】标签。用户如有需要可以自定义一些变量及其值，变量名必须是菜单中已经存在的变量。（5）填写【新建数据库】对话框中的【权限】标签。用户如有需要可以修改不同用户或组对本数据库的权限。（6）填写【新建数据库】对话框中的【SQL】标签

7、。用户可以在这里查看到创建数据的SQL语句。点击【确定】创建数据库，创建成功后，在对象浏览器中即可查看到。12.3.2 修改数据库修改数据库与创建类似，首先在对象浏览器中，找到需要修改的数据库，点击鼠标右键，在弹出的菜单中选择【属性】，即可打开数据库的属性页面，如图18.27所示。修改的过程与创建过程相同，这里不再重复。12.3.3 删除数据库首先选中要删除的数据，点击鼠标右键，在菜单中选择【删除/移除】，在弹出的确认对话框中，选择【是】，即可删除。12.4 数据表的基本操作12.4.1 创建数据表【例12.4】使用pgAdminIII创建数据表。下面，将以建立表12-1所示的表为例，介绍如何

8、在PostgresSQL中创建数据表。（1）在创建数据表前，先要确认是在哪个数据库内。我们选择数据库“my_db”，找到该数据库，然后依次展开“my_db”“架构”“public”，找到【数据库表】，单击鼠标右键，在菜单中，选择【新建数据表】选项。（2）填写【新建数据表】对话框中的【属性】标签。【名称】填写“my_table”，【所有者】选择“postgres”，【注释】任意填写。（3）填写【新建数据表】对话框中的【字段】标签。点击【添加】。填写弹出的【添加字段】对话框。按照表12-1中的字段要求，填写【名称】，选择【数据类型】和【长度】（如果长度是可变的话）。（4）填写【新建数据表】对话框中

9、的【约束】标签。PostgresSQL可以添加的约束包括主键、外键、排他、唯一和检查约束。下面以添加主键为例，将数据表“my_table”中的“id”字段设置为主键约束。在【新建数据表】对话框中，单击【约束】标签，选择底部下拉菜单的【主键】选项，点击【添加】。在【新建主键.】对话框中，【名称】填写“pk_id”，【注释】任意填写。切换到【字段】标签，在【字段】下拉框中选择“id”，点击【添加】按钮，将该字段加入到列表。点击【确定】按钮完成主键的添加。【注意】主键是必须的，如果没有主键，pgAdminIII将无法正常对数据表进行编辑；主键可以有多个，组成联合主键，只要在字段中添加即可。（5）完成

10、数据表的创建。填写完所有的设置后，点击“新建数据表”对话框的右下侧的【确定】按钮，完成创建。完成创建后，在对象浏览器中可以看到该表。12.4.2 修改数据表修改表的方法与新建数据表类似，首先要选择要操作的表，然后单击鼠标右键，在弹出的菜单中选择【属性】，打开表属性标签组窗口，该窗口的内容与新建数据表相同，这里不再赘述。如果只是为了增加数据表的属性，pgAdminIII提供了另外一种修改数据表的方法：在对象浏览器中，选中“my_table”，鼠标单击右键，在弹出的菜单中，展开【新建对象】子菜单，该子菜单中包括了新建字段、新建主键、新建外键、新建排他约束、新建唯一约束等一系列操作，只需要选择需要的

11、选项进行操作即可。12.4.3 删除数据表删除数据表方法与删除数据库类似，首先要选择要操作的表，然后单击鼠标右键，在弹出的菜单中选择【删除/移除】选项，在弹出的确认对话框中，选择【确定】即可完成操作。12.5 插入、更新与删除数据12.5.1 插入【例12.5】使用pgAdminIII插入一行数据。1、使用pgAdminIII插入（1）在对象浏览器中，选中“my_table”，在快捷工具栏上点击图标，打开如图12-4所示的编辑数据窗口。 12-4 为数据表插入数据 12-5 填写数据并保存（2）填写数据。在编辑数据窗口中，有一空白行，该行第一列有*号，代表可以录入数据。在该行内，输入符合字段格

12、式的数据。完成后，点击快捷工具栏上的按钮保存该行数据。2、使用SQL语句插入插入的SQL关键词是INSERT ，插入的基本语法如下：INSERT INTO 要插入的表名称 (字段1,字段2,) VALUES (字段1对应的值, 字段2对应的值,) , (字段1对应的值, 字段2对应的值,)例如图12-4中插入的数据可以用下列语句实现：INSERT INTO my_table (id,name,sex,birthday,class_id,height) VALUES (1,张三,男,1989-01-01,1,165.5)如果要插入多条数据，直接在VALUES后输入多条数据，中间用逗号隔开即可，例

13、如下面的例子：INSERT INTO my_table (id,name,sex,birthday,class_id,height) VALUES (1,张三,男,1989-01-01,1,165.5), (2,李四,女,1988-01-01,2,165.5)如果要插入数据不写列名，则后方插入的值必须与数据表中的所有字段匹配，否则报错，例如下面的例子：INSERT INTO my_tableVALUES (1,张三,男,1989-01-01,1,165.5), (2,李四,女,1988-01-01,2,165.5)12.5.2 更新【例12.6】使用pgAdminIII更新一行数据。1、使用p

14、gAdminIII更新更新操作与插入新数据一样，在图12-4所示的编辑数据窗口，找到需要修改的行与字段，双击该字段，修改完数据，点击保存修改。2、使用SQL更新更新的SQL关键词是UPDATE，更新的基本语法如下：UPDATE 要更新的表名称 SET 字段1=新的值, 字段2=新的值, WHERE 条件如果WHERE条件不输入，则表中所有条目都更新为设置的值，否则只更新符合条件的条目。12.5.3 删除【例12.7】使用pgAdminIII插入一行数据。1、使用pgAdminIII删除更新操作与插入新数据一样，在图12-4所示的编辑数据窗口，找到需要删除的行，双击选中该行。在行数列的位置，点击

15、鼠标右键，在弹出的菜单中选择【删除】。在弹出的确认对话框中，选择【确定】即可完成删除。2、使用SQL删除更新的SQL关键词是DELETE，更新的基本语法如下：DELETE FROM 表名称 WHERE 条件如果WHERE条件不输入，则表中所有条目都删除，否则只删除符合条件的条目。例如，如果要将姓名为张三的所有相关记录删除，则可用下列语句实现：DELETE FROM my_table WHERE name=张三12.6 数据查询数据库操作的重要操作就是数据的查询，从简单的单表无条件查询到复杂的带条件的连接查询。本节将对数据查询展开相关介绍。12.6.1 查询语句PostgresSQL使用SELE

16、CT作为查询语句。SELECT的基本语法：SELECT * | , FROM ,WHERE AND|OR ORDER BY DESC|ASCGROUP BY LIMIT, OFFSET 起始行(偏移量)参数简介： * | , 大括号内的代表查询返回的字段。查询可以是*代表所有字段；也可以是指定一个或多个字段。FROM ,FROM是关键词必须要有，大括号内代表的是从哪些表或者视图中查询。WHERE AND|OR 中括号内代表的查询条件。WHERE是关键词，如果需要限制条件，则这是必须的。后面的是条件，可以有多个条件，不同的条件用AND或者OR连接。ORDER BY DESC|ASC对结果进行排序

17、，ORDER BY是关键词，如果需要进行排序，则必须添加。是排序的对象，可以有多个字段进行排序。DESC|ASC表示排序的顺序是降序还是升序。GROUP BY 对结果进行分组，GROUP BY是关键词，如果需要分组，则必须添加。是分组的对象，可以有多个分组的字段。LIMIT, OFFSET 起始行(偏移量)限制读取行数。LIMIT是关键词，如需限制读取行数，则必须添加。OFFSET 起始行(偏移量)是指查询起始的第一条行号或者叫距离第一行的偏移量，该参数不是必要的，如果不填则默认是0，即从第一行开始；指的是读取多少行。下面几小节，我们将利用12.4.1中创建的表格进行操作，为了方便相关操作，我

18、们先对该表格插入一些数据。INSERT INTO my_table (id,name,sex,birthday,class_id,height) VALUES(1,张三,男,1989-01-02,1,165.5),(2,李四,女,1988-02-02,2,160),(3,王五,男,1985-03-02,3,180.5),(4,赵六,女,1982-04-02,1,161),(5,钱七,男,1981-05-02,2,175),(6,孙八,女,1986-06-02,3,156.5),(7,杨九,男,1986-07-02,1,177),(8,吴十,女,1982-01-02,2,163),(9,张三风,

19、男,1983-09-02,3,170.5),(10,张四民,女,1984-10-02,1,156),(11,张五济,男,1990-11-02,2,169),(12,张六霞, 女,1991-12-02,3,167)12.6.2 单表查询1、查询所有字段PostgresSQL中，查询所有字段有2种方法，第一种使用“*”，第二种将所有字段都列出。【例12.8】查询所有字段。SELECT * FROM my_tableSELECT id,name,sex,birthday,class_id FROM my_table2、查询指定字段查询指定字段可以是表中的一个或多个字段，也可以包括PostgresSQ

20、L支持的函数。【例12.9】查询当前世界和my_table的id与name。SELECT now(),id,name FROM my_table3、简单条件查询一般情况下，在进行数据查询时，都是需要带条件的。因为数据库的数据量非常大，要从中获取有效数据，就需要进行筛选过滤。PostgresSQL中通过WHERE子句来实现条件查询。表18.19所示的是PostgresSQL支持的条件判断符。【例12.10】查询my_table中，符合姓名为张三的出生年月。SELECT birthday FROM my_table WHERE name=张三【例12.11】查询my_table中，符合班级clas

21、s_id为1且性别为女的所有信息。SELECT * FROM my_table WHERE class_id=1 AND sex=女BETWEEN AND操作符可以用来查询符合字段介于BETWEEN后面的值与AND后面的值的条目，或者在IN前加NOT查询不等于的条目。【例12.12】查询my_table中，符合出生年月介于19851988年且性别为女的所有信息。SELECT * FROM my_table WHERE birthday BETWEEN 1985-01-01 AND 1988-12-31IN操作符可以用来查询符合字段等于IN后括号内所包括的值的条目。或者在IN前加NOT查询不等于

22、IN后括号内包括的值的条目。【例12.13】查询my_table中，符合id不等于（1,3,5,7,9,11）的所有信息。SELECT * FROM my_table WHERE id NOT IN (1,3,5,7,9,11)LIKE操作符可以用于模糊匹配要查询的字段。使用“%”作为通配符，“%”可以被添加在LIKE后的匹配值的任意位置，例如“%a”代表任意以a结尾的字符串，“%a%”代表中间带有a的字符串。【例12.14】查询my_table中，查找姓张的所有信息。SELECT * FROM my_table WHERE name LIKE 张%4、合并查询结果的重复项每个字段在数据表中，

23、可能有很多条数据都有相同的值，当我们查询的结果不需要重复项的时候，可以使用DISTINCT关键字来排除重复项。【例12.15】查询my_table中，查找班级编号class_id哪几个。SELECT DISTINCT class_id FROM my_table5、排序查询查询的结果如果不人工设定排序依据，一般都是按照数据存储的顺序返回结果。当我们需要自定义排序的时候，就需要使用ORDER BY来设计排序方法。ORDER BY可以指定一个或多个排序字段，还可以设置排序规则是升序（ASC）还是降序（DESC）。【例12.16】查询my_table中，查找结果按照出生年月降序排列。SELECT *

24、 FROM my_table ORDER BY birthday DESC【例12.17】查询my_table中，查找结果按照首先按照性别排序其次按照出生年月排序。SELECT * FROM my_table ORDER BY sex,birthday6、分组查询 分组查询是指对数据按照一个或多个字段进行分组后返回结果，PostgresSQL使用GROUP BY分组。【例12.18】查询my_table中，根据class_id进行分组，并获取每个分组的条数。SELECT class_id,count(*) FROM my_table GROUP BY class_id上述例子的分组结果没有任何

25、条件筛选，如果需要对结果进行筛选，PostgresSQL提供了HAVING关键字实现筛选。【例12.19】查询my_table中，根据class_id进行分组，并获取每个分组的条数，并筛选出class_id不为1的部分。SELECT class_id,count(*) FROM my_table GROUP BY class_id HAVING class17、限制查询行数由于数据表的查询结果可能条数过多，查询时间可能过长，并且对于应用来说一次也不能显示过多条数，所以对查询结果一般进行条数限制。PostgresSQL提供了LIMIT关键字实现读取有限行数据。【例12.20】查询my_table

26、，仅获取前5行。SELECT * FROM my_table LIMIT 5【例12.21】查询my_table，从第6行起，获取5行。SELECT * FROM my_table LIMIT 5 OFFSET 5【注意】OFFSET用于记录偏移量，该偏移量从0开始，0代表第1行。12.6.2 集合函数查询数据表进行查询时，返回的结果可以是字段也可以是其他的数据，例如查询结果的行数。在PostgresSQL中，这些非字段的数据查询可以通过集合函数来实现。1、COUNT()【例12.22】查询my_table一共有多少行数据。SELECT COUNT (*) FROM my_table 2、AV

27、G() 【例12.23】查询my_table，计算平均身高。SELECT AVG(height) FROM my_table3、MIN()和MAX()【例12.24】查询my_table，获取最高和最低身高。SELECT MAX(height),MAX(height) FROM my_table4、SUM()【例12.25】查询my_table，求所有人身高的总和。SELECT SUM(height) FROM my_table12.6.3 连接查询连接查询是数据库查询中最常用的查询方法之一，包括内连接、外连接等。使用连接查询可以将多个表格连接到一起，获取表中有用的数据，筛除无效的数据。1、内

28、连接查询内连接查询使用INNER JOIN关键字操作，使用时INNER JOIN左右两侧分别列出两张表，然后使用ON限定连接条件。【例12.26】在my_table和my_class表之间使用内连接查询。首先，建立my_class表，并插入数据。CREATE TABLE my_class(class_id INT PRIMARY KEY,class_name character varying(10),grade_name character varying(10),class_teacher character varying(25)INSERT INTO my_class (class_i

29、d,grade_name,class_name,class_teacher) VALUES(1,Grade1, Class1,David),(2,Grade2, Class1,John),(3,Grade1, Class2,Mary),(4,Grade2,Class2,Cindy)SQL语句如下：SELECT my_table.id,name,sex,birthday,height,grade_name,class_name,class_teacherFROM my_tableINNER JOIN my_class ON my_table.class_id=my_class.class_id【

30、注意】上述例子中，其实可以使用WHERE也能够实现相同的查询结果。WHERE使用时，查询的表都置于FROM后，连接条件置于WHERE后。INNER JOIN则将需要连接的表置于INNER JOIN后，连接条件置于ON后。SELECT my_table.id,name,sex,birthday,height,grade_name,class_name,class_teacherFROM my_table,my_class WHERE my_table.class_id=my_class.class_id2、外连接查询内连接进行查询时，有一个限制，查询的结果只能是符合查询条件和连接条件的行。如果需

31、要查询没有关联的数据，就需要使用外连接来实现。外连接分为左连接（LEFT JOIN）和右连接（RIGHT JOIN）。LEFT JOIN：返回左表所有记录，以及右表中连接字段相等的记录。RIGHT JOIN：返回右表所有记录，以及左表中连接字段相等的记录。下面的例子需要用到一张新数据表，先建立my_score表，并插入数据。CREATE TABLE my_score(course_id INT PRIMARY KEY,course_name character varying(10),course_score float(5),student_id INT)INSERT INTO my_sco

32、re (course_id, course_name, course_score, student_id) VALUES(1,English,90,1), (2,English,70.5,2), (3,English,82,3), (4,Chinease,90,4),(5,Chinease,90,5), (6,Chinease,70,6) , (7,Chinease,90.5,7), (8,Math,90,8),(9, Math,85,9), (10, Math,79 ,1) , (11, Math,65 ,10) , (12, Math,59.5 ,12)关于LEFT JOIN：【例12.2

33、7】在my_table和my_score表之间使用左连接查询成绩。SELECT my_table.id,my_,my_score.course_name,my_score.course_scoreFROM my_table LEFT JOIN my_score ON my_table.id=my_score.student_id关于RIGHT JOIN：【例12.28】在my_table和my_score表之间使用右连接查询成绩。SELECT my_table.id,my_,my_score.course_name,my_score.course_scoreFROM my_table RIGHT JOIN