数据类型
- 数值数据类型
- integer 存储整数。使用这个类型可存储典型的整数 4字节 -2147483648 至 +2147483647
- double 可变精度，不精确 8字节 15位数字精度
- serial 自动递增整数 4字节 1 至 2147483647
- 字符串数据类型
- character varying(size) 这里size是要存储的字符数。 可变长度字符串。
- text 可变长度字符串。
- 日期/时间数据类型
- timestamp(6) without time zone
- date 日期(没有时间) 4字节 4713 bc 5874897 ad 1微秒/14位数
- 其他数据类型
- boolean 它指定true或false的状态。 1字节
- money 货币金额 8字节 -92233720368547758.08 至 +92233720368547758.07
创建表
CREATE TABLE eric_staff(
name character varying NOT NULL,
sex character varying,
birthy date,
CONSTRAINT eric_staff_pkey PRIMARY KEY (name)
)
WITH(
OIDS=FALSE
);
ALTER TABLE eric_staff OWNER TO postgres;
创建模式（架构）
CREATE SCHEMA
创建表
CREATE TABLE table_name()
删除表
DROP TABLE table_name
INSERT
INSERT INTO eric.eric_staff(name,sex,birthy)VALUES
('eric','man','2017-10-01'),
('jane','women','2017-10-03'),
('moon','women','2017-10-02');
SELECT
SELECT (name) FROM eric.eric_staff WHERE birthy = '2017-10-01';
UPDATE
UPDATE eric.eric_staff SET birthy = '1991.10.12' WHERE name = 'eric';
DELETE
DELETE FROM eric.eric_staff WHERE name = 'moon';
ORDER BY
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
ASC--结果按升序排列
DESC--结果按降序排列
SELECT * FROM eric.eric_staff ORDER BY birthy DESC;
GROUP BY
INSERT INTO eric.eric_staff(
name, sex, birthy, money)
VALUES ('eric', 'man', '2017-10-12', 12.3),
('jane', 'women', '2017-10-11', 13.2),
('frank', 'man', '2017-10-12', 5),
('eric', 'man', '2017-10-15', 23),
('eric', 'man', '2017-10-10', 13.3);
SELECT name, SUM(money)
FROM eric.eric_staff GROUP BY name;
微信截图_20171116213514.png
HAVING
SELECT name FROM eric.eric_staff GROUP BY name HAVING count(name)>1;
# 选出name字段出现一次以上的数据
WHERE
SELECT * FROM eric.eric_staff WHERE (NAME = 'eric' AND NAME = 'jane') OR MONEY = '13.3';
# 筛选出名字为eric的数据
SELECT * FROM eric.eric_staff WHERE money NOT IN('13.0', '5.0');
# 选出数值不在数组范围内的数据
SELECT * FROM eric.eric_staff WHERE name LIKE '%e%';
# 选出name中含有e的数据
SELECT * FROM eric.eric_staff WHERE money BETWEEN '6' AND '14';
# 选出钱数在6到14范围之间的数据
连接
- 内连接（简单连接）
# 选出两个表中name sex一样的数据
SELECT eric.eric_staff.name, eric.eric_staff.sex, eric.eric_other.name, eric.eric_other.sex
FROM eric.eric_other
INNER JOIN eric.eric_staff
ON eric.eric_staff.sex = eric.eric_other.sex AND eric.eric_staff.name = eric.eric_other.name;
- 左连接
# 以FROM表为基础全部显示，另一张表只显示匹配项。
SELECT eric.eric_staff.name, eric.eric_staff.sex, eric.eric_other.name, eric.eric_other.sex
FROM eric.eric_other
LEFT OUTER JOIN eric.eric_staff
ON eric.eric_staff.name = eric.eric_other.name;
图片.png
- 右连接
# 以另一张表为基础，FROM表只显示匹配项
SELECT eric.eric_staff.name, eric.eric_staff.sex, eric.eric_other.name, eric.eric_other.sex
FROM eric.eric_other
RIGHT OUTER JOIN eric.eric_staff
ON eric.eric_staff.name = eric.eric_other.name;
图片.png
- 全外连接
# 左右表全显示，不匹配的留空。
SELECT eric.eric_staff.name, eric.eric_staff.sex, eric.eric_other.name, eric.eric_other.sex
FROM eric.eric_other
FULL OUTER JOIN eric.eric_staff
ON eric.eric_staff.name = eric.eric_other.name;
图片.png
创建视图
- 其实就是将操作后的结果生成一张表，下面两条语句结果一样
CREATE OR REPLACE VIEW eric.view_table AS
SELECT eric_other.name,
eric_other.sex
FROM eric.eric_other;
SELECT eric_other.name,
eric_other.sex
FROM eric.eric_other;
- 删除视图
DROP VIEW view_table;
python 连接postgresql数据库
import psycopg2
conn = psycopg2.connect(database="test_db", user="postgres", password="tuji2013", host="127.0.0.1", port="5432")
print ("Opened database successfully")
import psycopg2
conn = psycopg2.connect(database="test_db", user="postgres", password="tuji2013", host="127.0.0.1", port="5432")
cur = conn.cursor()
cur.execute("INSERT INTO eric.eric_staff (name, sex, birthy, money) VALUES ('test_python', 'man', '2017-11-19', '12')")
conn.commit()#提交事务
print('插入成功')
