数据库的结构：
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 条件
