PostgreSQL基础篇

PostgreSQL基础篇

基础语法

创建数据库

//方法一
CREATE DATABASE testdb;//创建名为test的数据库

//方法二
createdb [option...] [dbname [descripton]]
//option参数说明
//1.指定数据库默认表空间。			 -D tablespace
//2.将cratedb生成的命令发送到服务端。-e
//3.指定数据库的编码。			   -E encoding
//4.指定数据库的语言环境。		   	  -I locale
//5.指定创建此数据库的模板。	   	 -T template
//6.指定服务器的主机名。			   -h host
//7.指定服务器监听的端口。			  -p port
//8.连接数据库的用户名。             -U username
//9.连接时强制要求输入密码。          -W
//10.忽略输入密码。                 -w
eg:createdb -h 192.168.1.1 -p 5432 -U posthres testdb

选择数据库

使用\l查看已经存在的数据库。

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

postgres=# 

方法一：通过\c + 数据库名称进入数据库

postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# 

方法二：使用系统命令窗口

[postgres@pg14 ~]$ psql -h localhost -p 5432 -U postgres testdb -W
Password: ****
psql (14.6)
Type "help" for help.

testdb=# 

删除数据库

PostgreSQL删除数据库非图形化有两种方式：

• 使用DROP DATABASESQL语句来删除。
  • DROP DATABASE会删除数据库的系统目录项并且包含数据的文件目录。
  • DROP DATABASE只能由超级管理员或数据库拥有者执行。
  • DROP DATABASE命令需要再PostgreSQL命令窗口来执行。
• 使用dropdb命令来删除。
  • dropdb是DROP DATABASE的包装器。
  • dropdb用于删除PostgreSQL数据库。
  • dropdb命令只能由超级管理员或数据库拥有者执行。

//方法一
postgres=# drop database testdb;
DROP DATABASE
//方法二
[postgres@pg14 ~]$ dropdb -h localhost -p 5432 -U postgres testdb
//参数说明
//1.显示dropdb生成的命令并发送到数据库服务器。	 -e
//2.在做删除的工作之前发出一个验证提示。           -i
//3.打印dropdb版本并退出。                      -V
//4.如果数据库不存在则发出提示信息，而不是错误信息。 --if-exists
//5.指定运行服务器的主机名。                     -h host
//6.指定服务器监听的端口，或者socket文件。         -p port
//7.连接数据库的用户名。                         -U username
//8.删除数据库时指定连接的数据库，默认为postgres，如果不存在使用template1. --maintence-db=dbname

• dropdb -h localhost -U postgres -p 5432 testdb1
• dropdb -h localhost -U postgres -p 5432 --maintenance-db=testdb testdb1
• 两者的区别：指定要连接的数据库的名称，以便删除目标数据库。如果不指定，将默认使用postgres数据库；如果不存在(或者正在删除数据库)，则使用template1。

创建数据表

语法：

CREATE TABLE table_name(
    column1 datatype,
    column2 datatype,
    ...
    columnN datatype,
    PRIMARY datatype(一个或者多个列)
)

• CREATE TABLE是一个数据库关键字，用于告诉数据库系统需哟创建一个数据表。
• CREATE TABLE在当前数据库中创建一个新的空白表，该表由此命令的发起者所有。

eg:

testdb=# create table student(
testdb(# Id serial,
testdb(#     username char(20) not null,
testdb(#     birthday date
testdb(# );
CREATE TABLE

#插入几条测试数据
insert into student(username,birthday) values('zhangsan','2023-08-19');
insert into student(username,birthday) values('lisi','2023-08-19');
insert into student(username,birthday) values('wangwu','2023-08-19');
       
#查询
testdb=# select * from student;
 id |       username       |  birthday  
----+----------------------+------------
  1 | zhangsan             | 2023-08-19
  2 | lisi                 | 2023-08-19
  3 | wangwu               | 2023-08-19
(3 rows)

testdb=#

删除数据表

**语法：**DROP TABLE table_name;

使用DROP TABLE语句来删除表格，包含表格数据、规则、触发器等。

testdb=# \d
               List of relations
 Schema |      Name      |   Type   |  Owner   
--------+----------------+----------+----------
 public | student        | table    | postgres
 public | student_id_seq | sequence | postgres
(2 rows)

testdb=# 

#删除表操作
testdb=# drop table student;
DROP TABLE
testdb=# \d
Did not find any relations.
testdb=# 

SCHEMA（模式）

PostgreSQL模式可以看做一个表的集合。包含视图、索引、数据类型、函数和操作符等。

相同的对象名称可以用于不同的模式中，而不会出现冲突。

• 允许多个用户使用一个数据库并不会互相干扰。
• 将数据库对象组织称逻辑组方便管理。
• 第三方应用的对象可以存放在独立的模式中，不会与其他对象的名称发生冲突。

testdb=# create schema myschema;
CREATE SCHEMA
testdb=# create table myschema.student(Id serialname varchar(15),birthday date,primary key(Id));
CREATE TABLE
//查询表
testdb=# select * from myschema.student;
 id | name | birthday 
----+------+----------
(0 rows)

删除模式

//删除一个为空的模式（其中的所有对象已经被删除）
testdb=# drop schema myschema;
//删除一个模式以及其中包含的所有对象
testdb=# drop schema myschema cascade;

//cascade关键字
//在父表中update/update记录时，同步update/delete子表的记录
//在子表中update/update记录时，父表记录不变

Insert

insert into table_name(column1,column2,...,columnN) values (value1,value2,...,valueN);

• column1,column2,…,columnN为表中字段名称。
• value1,value2,…,valueN为字段对应的值。

使用insert into 语句是，字段列和数据值数量要相等，而且顺序也要对应。

如果向表中插入所有字段插入值，可以不需要指定字段，只需要指定插入的值。

insert into table_name values (value1,value2,...,valueN);

示例

insert into student(name,birthday) values ('zhangsan','2023-01-01');
INSERT 0 1
testdb=# select * from student;
 id |   name   |  birthday  
----+----------+------------
  1 | zhangsan | 2023-01-01
(1 row)
testdb=# 

update

update table_name set column1=value1,...,cloumnN=valueN where [condition];

• 可以同时更新一个或者多个字段。
• where子句中可以指定任何条件。

示例

testdb=# select * from student;
 id |   name   |  birthday  
----+----------+------------
  1 | zhangsan | 2023-01-01
testdb=# update student set birthday = '2001-01-01';
UPDATE 1
//修改后
testdb=# select * from student;
 id |   name   |  birthday  
----+----------+------------
  1 | zhangsan | 2001-01-01

delete

delete from table_name where [condition];

• 若没有指定where子句，会将table_name表中的所有记录删除。

delete from student;//删除整个表的数据
delete from student where name = 'lisi';//删除指定条件的数据
testdb=# select * from student;
 id |   name   |  birthday  
----+----------+------------
  1 | zhangsan | 2001-01-01
  3 | wangwu   | 2008-09-08
(2 rows)
testdb=#

单表查询语句

testdb=# select * from student;
 id |   name   |  birthday  
----+----------+------------
  1 | zhangsan | 2001-01-01
  2 | lisi     | 0208-02-02
testdb=#

select是关键字，表示查询，后边可以跟*或者多个列名，各列明之间使用逗号分隔。from后边跟表的名称。各个列是可以表的列名，也可以是一个表达式。

示例

testdb=# select age+5 from student;
 ?column? 
----------
       23
(1 row)

//当表达式与表的列无关时，在PostgresSQL和MySQL中可以不适用from+表名，充当计算器使用。
testdb=# select 56+78;
 ?column? 
----------
      134
(1 row)

过滤条件的查询

select语句后面可以通过指定where子句来指定要查询那条记录或那些记录。

//查询出生日期等于2023-01-01
testdb=# select * from student where birthday = '2023-01-01';
 id | age |   name   |  birthday  
----+-----+----------+------------
  1 |  18 | zhangsan | 2023-01-01
(1 row)
//查询出生日期大于
testdb=# select * from student where birthday > '2023-01-01';
 id | age |  name  |  birthday  
----+-----+--------+------------
  2 |  19 | lisi   | 2023-02-01
  3 |  22 | wangwu | 2023-03-02
(2 rows)

排序

使用排序子句对查询出的数据进行排序，排序子句是在select语句后面再加上order by子句。

示例

//出生日期正序排列
testdb=# select * from student order by birthday;
 id | age |   name   |  birthday  
----+-----+----------+------------
  1 |  18 | zhangsan | 2023-01-01
  3 |  22 | wangwu   | 2023-01-09
  2 |  19 | lisi     | 2023-02-01
(3 rows)
//出生日期倒序序排列
testdb=# select * from student order by birthday desc;
 id | age |   name   |  birthday  
----+-----+----------+------------
  2 |  19 | lisi     | 2023-02-01
  3 |  22 | wangwu   | 2023-01-09
  1 |  18 | zhangsan | 2023-01-01
(3 rows)

排序子句order by应该在where子句之前，如果顺序错了，会报错：

ERROR: syntax error at or near "where" LINE 1: select * from student order by birthday where age>19;

分组查询

testdb=# select age,count(*) from student group by age;
 age | count 
-----+-------
  22 |     1
  19 |     1
  18 |     2
(3 rows)

• 使用group by语句时，需要搭配使用聚合函数。例如：count()、sum()

DISTINCT

distinct关键字与select语句一起使用，用于去除重复记录，只获取唯一的记录。

select distinct cloumn1,column2,...,columnN from table_name where [condition];

示例

testdb=# select name from student;	|	testdb=# select distinct name from student;
   name   							|	   name   
----------							|	----------
 zhangsan							|	 lisi
 lisi								|	 wangwu
 wangwu								|	 zhangsan
 zhouliu							|	 zhouliu
 liming								|	 liming
 zhangsan							|	(5 rows)
(6 rows)							|