SQLpostgresql

完整数据类型参考

• 数值数据类型
  • 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

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('插入成功')