PostgreSQL 常用操作

数据库的结构：
Databases > Schemas > Tables

一、数据库&模式&表的操作

1.1、数据库操作

创建数据库 CREATE DATABASE database_name;
删除数据库DROP DATABASE

1.2、表操作

创建表

-- 标准格式
CREATE TABLE table_name(  
   column1 datatype,  
   column2 datatype,  
   column3 datatype,  
   .....  
   columnN datatype,  
   PRIMARY KEY( one or more columns )  
);

CREATE TABLE study.student
(
  id integer NOT NULL,
  name character(100),
  subjects character(1),
  CONSTRAINT student_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE study.student
  OWNER TO postgres;
COMMENT ON TABLE study.student
  IS '这是一个学生信息表2';

create table study.employees
(
id integer not NULL,
"name" character(100),
"age" Integer,
"address" character(100),
"salary" Double Precision,
CONSTRAINT employee_key PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE study.employees
  OWNER TO postgres;
COMMENT ON TABLE study.employees
  IS '这是一个员工信息表';

-- 如果某表存在则删除
DROP TABLE IF EXISTS "study"."house_lianjia_communities";
-- 建立表，还有主键
CREATE TABLE "study"."house_lianjia_communities" (
  "community_name" Character Varying(255) ,
  "plate" Character Varying(255) ,
  "site" Character Varying(100) ,
  "age" Integer,
  "building_density" Double Precision,
  "building_type" Character Varying(255) ,
  "house_count" Integer,
  "building_count" Integer,
  "green_rate" Double Precision,
  "avr_price" Double Precision,
  "develop_company" Character Varying(255) ,
  "community_id" Character Varying(255) UNIQUE,
  "lat" Double Precision,
  "lng" Double Precision,
  "growth" Double Precision,
  "address" Character Varying(255)[]
  );

UNIQUE表示主键

删除表DROP TABLE table_name;

1.3、模式操作

模式(也叫架构)是指定的表集合。 它还可以包含视图，索引，序列，数据类型，运算符和函数。
创建模式 CREATE SCHEMA schema_name;
创建表的格式：模式.表

二、表的增删改查

2.1、插入数据（INSERT语句）

-- 不必对应全部字段写入，不写的会有默认值 
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)  VALUES (value1, value2, value3,...valueN);

INSERT INTO study.employees ( ID, NAME, AGE, ADDRESS, SALARY)  
VALUES
(1, 'Maxsu', 25, '海口市人民大道2880号', 109990.00 ), 
(2, 'minsu', 25, '广州中山大道 ', 125000.00 ), 
(3, '李洋', 21, '北京市朝阳区', 185000.00),   
(4, 'Manisha', 24, 'Mumbai', 65000.00), 
(5, 'Larry', 21, 'Paris', 85000.00);

2.2、查询数据（SELECT语句）

SELECT "column1", "column2".."column" FROM "table_name";

SELECT id,name FROM EMPLOYEES;

SELECT * FROM "table_name";

2.3、更新数据（UPDATE语句）

UPDATE table_name  
SET column1 = value1, column2 = value2...., columnN = valueN  
WHERE [condition];

update study.employees
set age=29, salary=9800
where id=1;

2.4、删除数据（DELETE语句）

DELETE FROM table_name  
WHERE [condition];

delete from study.employees
    where id =6

不用where限制则，全部删除

2.5、ORDER BY子句

SELECT column-list  
FROM table_name  
[WHERE condition]  
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

select * from study.employees
 order by age asc

多列排序 ORDER BY

select * from study.employees
order by age, name asc;

2.6、分组（GROUP BY子句）

依据by后字段，合并相应的数据

SELECT column-list  
FROM table_name  
WHERE [conditions ]  
GROUP BY column1, column2....columnN  
ORDER BY column1, column2....columnN

SELECT NAME, SUM(SALARY)   
FROM study.employees   
GROUP BY NAME;

额外插入新数据,有重复的name，便于sum函数的结果显示

INSERT INTO study.employees VALUES 
(6, '李洋', 24, '深圳市福田区中山路', 135000),
(7, 'Manisha', 19, 'Noida', 125000),
(8, 'Larry', 45, 'Texas', 165000);

2.7、Having子句

用于「字段的函数结果」满足某些条件的特定行

SELECT column1, column2  
FROM table1, table2  
WHERE [ conditions ]  
GROUP BY column1, column2  
HAVING [ conditions ]  
ORDER BY column1, column2

SELECT NAME   
FROM study.employees  
GROUP BY NAME HAVING COUNT (NAME) < 2;

目前的数据，「李洋」、「Larry」、「 Manisha」三位名字都重复2次。

select name, count(name)
from study.employees
group by name having count(name) > 1;

三、条件查询(对 WHERE语句 进一步限定范围)

AND 条件
OR 条件
AND & OR 条件
NOT 条件
LIKE 条件
IN 条件
NOT IN 条件
BETWEEN 条件

3.1

四

---

【1】PostgreSQL教程_易百