postgresql语法
一、数据类型
数值类型:interger 存储整数
字符串类型
char(size)
character(size)
varchar(size)
text
日期/时间数据类型
timestamp[(p)][不带时区]
timestamp[(p)]不带时区
date 日期(没有时间)
time[(p)][不带时区] 时间(无日期)
time[(p)] 带时区 仅限时间，带时区
其他数据类型
布尔类型 boolean 指定true或false的状态
货币类型 money 货币金额
几何类型
二、语法
创建数据库
create database database_name
删除数据库
drop database database_name
创建表
create table table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);
删除表
drop table table_name
插入语句
insert into table_name(column1,column2,column3,...)
values(value1,value2,value3,...);
查询语句
select column1,column2,... from table_name
select * from table_name
update table_name
set column1 = value1,column2 = value2,...
where [condition];
删除语句
delete from table_name
where [condition]
order by 用于按升序和降序对数据进行排序
select column-list
from table_name
[where condition]
[order by column1,column2,...][asc][desc]
column_list：它指定要检索的列或计算。
group by 分组
select column-list
from table_name
where [condition]
group by column1,column2,...
order by column1,column2,...
having 与group by 组合使用
select column1,column2,
from table1,table2
where [condition]
group by column1,column2
having [conditions]
order by column1,column2
条件查询
and or not like in not in between
内连接inner join
select table1.column1,table2.column2
from table1
inner join table2
on table.common_filed = table2.common_filed
左外连接 left outer join
select table1.columns, table2.columns
from table1
left outer join table2
on table1.common_filed = table2.common_field;
右外连接 right outer join
select table1.columns, table2.columns
from table1
right outer join table2
on table1.common_filed = table2.common_field;
全连接 full outer join
select table1.columns, table2.columns
from table1
full outer join table2
on table1.common_filed = table2.common_field;
跨连接 cross join
select coloums
from table1
cross join table2
python 连接 数据库
pip install python-psycopg2
import psycopg2
#连接数据库
conn = psycopg2.connect(database="testdb",
user="postgres",
password="123456",
host="127.0.0.1",
port="5432")
创建表
cur = conn.cursor()
cur.execute('''CREATE TABLE COMPANY
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL);''')
conn.commit()
conn.close()
插入操作
cur = conn.cursor()
cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (1, 'Paul', 32, 'California', 20000.00 )");
conn.commit()
conn.close()
查询操作
cur = conn.cursor()
cur.execute("SELECT id, name, address, salary from COMPANY")
rows = cur.fetchall()
for row in rows:
print "ID = ", row[0]
print "NAME = ", row[1]
print "ADDRESS = ", row[2]
print "SALARY = ", row[3], "\n"
conn.close()
更新操作
cur = conn.cursor()
cur.execute("UPDATE COMPANY set SALARY = 25000.00 where ID=1")
conn.commit
print "Total number of rows updated :", cur.rowcount
cur.execute("SELECT id, name, address, salary from COMPANY")
rows = cur.fetchall()
for row in rows:
print "ID = ", row[0]
print "NAME = ", row[1]
print "ADDRESS = ", row[2]
print "SALARY = ", row[3], "\n"
conn.close()
删除操作
cur.execute("DELETE from COMPANY where ID=2;")
conn.commit
cur.execute("SELECT id, name, address, salary from COMPANY")
rows = cur.fetchall()
for row in rows:
print "ID = ", row[0]
print "NAME = ", row[1]
print "ADDRESS = ", row[2]
print "SALARY = ", row[3], "\n"
conn.close()
