数据库支持

一.python DB api
1.全局变量
变 量 名          描 述
apilevel          使用的Python DB API版本
threadsafety      模块的线程安全程度如何
paramstyle        在SQL查询中使用哪种参数风格

>>> import psycopg2          #(PostgreSQL)
>>> psycopg2.apilevel
'2.0'
>>> psycopg2.threadsafety
2
>>> psycopg2.paramstyle
'pyformat'
>>>

2.异常
Python DB API指定的异常
3.连接和游标
函数connect的常用参数
连接对象的方法
游标对象的方法
游标对象的属性
4.类型
DB API构造函数和特殊值
二.sqlite3和PostgreSQL
1.sqlite3
创建连接
>>> import sqlite3
>>> conn = sqlite3.connect('somedatabase.db')

从连接获得游标
>>> curs = conn.cursor()

提交修改才会保存到文件中
>>> conn.commit()

关闭连接
>>> conn.close()

2.PostgreSQL
import psycopg2
conn=psycopg2.connect(database='mydb',user='postgres',password='12345',host='127.0.0.1',port='54321')
cur=conn.cursor()
cur.execute('select id,name,salary from tb')
for id,name,salary in cur.fetchall():
    print(id,':',name,':',salary)

python操作PostgreSQL

0

SQL语句5:触发器(PostgreSQL)

一.什么是触发器和触发器函数
触发器:一种声明,告诉数据库应该在执行特定操作时执行特定的函数
触发器函数:一个没有参数并且返回trigger类型的函数
1.创建触发器函数
create function fun_name() returns trigger as $f fun_name$
begin
     函数执行代码
end;
$ fun_name $ language plpgsql;
分类:
before触发器:在语句开始做任何事情之前开始触发
after触发器:在语句结束后触发

二.创建触发器
 create trigger 触发器名 before|after 触发事件
on 表名 for each row execute procedure 触发器函数
释:触发事件参数:insert,update,delete,可以多参数
for each row:表示任何一条记录上的操作满足触发事件都会触发触发器

三.查看和修改触发器
pgAdmin

四.删除触发器
drop trigger trigger_name on schema_name(数据库名)

五.触发器的使用
创建一个account表,然后创建一个触发器,用于检测account表的name列的插入数据是否为空;
如果为空,将弹出错误提示.
1.创建数据库库表:
create table account
 (
     id int,
     name char(20)
 )
2.创建触发器函数(全局,属于整个数据库的)
create function fun() returns trigger as $fun$
 begin
 if new.name is null then
 raise exception 'name can not be null';
 end if;
 end;
 $fun$ language plpgsql;

3.创建一个before触发器(属于某个表)
 create trigger fun before insert on account
 for each row execute procedure fun()

4.插入空值
insert into account values (10) (name字段为空值)
0

SQL语句4:视图

一.含义和作用
虚拟表:可使用insert,update,delete语句
作用:保障原表的安全

二.创建视图
1.在单表上创建视图
create view view_name (column_list) as select column from table
column_list为自定义显示列,相当于重命名as

2.在多表上创建视图
create view view_name (column_list) as select table1.column1,table2.column2 from table1,table2

三.查看视图
:select * from view_name

四.删除视图
drop view [if exists] view_name1,view_name2...
0

SQL语句3:索引

一.索引分类(PostgreSQL)
B-tree索引,Hash索引,Gist索引,GIN索引

二.创建索引(单列索引)
1.创建普通索引
create index index_name on table_name(col_name)

2.创建唯一索引
create unique index index_name on table_name(col_name)

3.创建组合索引
create index index_name on table_name(col_name1,col_name2...)

三.重命名索引
alter index index_name rename to new_name

四.删除索引
drop index index'_name
0

SQL语句2:查询

一.简单查询
1.基本查询语句
select {* | <字段列表>} 
         [
          from <表1>,<表2>
          [where <condition>]
          [group by <group by definition>]
          [having <expression> [{<operator><expression>}...]]
          [order by <order by definition>]
          [limit [<offset>,] <row count>]
           ]

2.单表查询
select * from table_name

3.查询指定字段
select column1,column2 from table_name

4.查找指定记录
select 字段1,字段2
from 表名
where 查询条件

5.带in关键字的查询(枚举)
where (not) in (value1,value2)

6.带between and的范围查询(范围)
where age (not) between 10 and 90

7.带like的字符匹配查询
通配符         描述
%                 替代一个或多个字符
_                  仅替代一个字符
[charlist]     字符列中的任何单一字符
[^charlist]
或者            不在字符列中的任何单一字符
[!charlist]
where column like '%c'   以字符c结尾

8.查询空值
where column is null

9.带and或or的多条件查询
where condition1 and(or) condition2

10.查询结果不重复
select distinct 字段名 from 表名

二.对查询结果排序:
11.单列(多列)排序
order by 列名1,列名2(先按列名1,再按列名2)

12.指定排序方向
asc升序(默认),desc降序
order by column desc

13.分组查询
[group by 字段] [having <条件表达式>]
group by :常和聚合函数一起使用:max(),min(),count(),sum(),avg(),结果按字段分组
having count(column)>1 过滤分组

ps:
having和where的区别:
having在数据分组之后进行过滤来选择分组,而where在分组之前用来选择记录.另外where排除的记录不再包括在分组中

14.group by和order by一起使用
group by column1 having count(column2)>1
order by count(column2)

15.用limit限制查询结果
limit 行数
select * from limit 4 显示前4行

16.聚合函数查询
avg() 返回某列的平均值
sum() 返回某列值的和
count() 返回某列的行数
max() 返回某列的最大值
min() 返回某列的最小值

三.链接查询:
17.内连接:表间满足条件的数据行进行组合
select column1,column2 
from table1 inner join table2
on table1.column-x=table2.column-y (on相当于where)

18.外连接
左连接left join:返回包含左表中的所有记录和由表中连接字段相等的记录,即使在右表 (table_name2) 中没有匹配的行(显示为null)
右连接right join:返回右表中的所有记录和左表中连接字段相等的记录,即使在左表 (table_name2) 中没有匹配的行(显示为null)
select column1,column2 
from table1 
left(right) join table2
on table1.column-x=table2.column-y 

19.复合条件连接查询

四.子查询:
20.带any,some关键字的子查询:同义词,表示满足其中任一条件
select num1 from tb1 
where num1 > any(select num2 from tb2)
返回tb1中num1大于tb2中的任意num2的所有结果

21.带all关键字的查询
select num1 from tb1 
where num1 > all(select num2 from tb2)
返回tb1中num1大于tb2中的所有num2的所有结果

22.带exists关键字的子查询
where exists (select column from table)

23.带in关键组的子查询
select column1 from table1 where column-x
in (select column-x from table2)
或者
枚举(见前面)

24.带比较运算符的子查询
where  > < =....

五.合并查询:
25.合并查询:两个表对应的列数和数据类型必须相同
select column,... from table1
union [all] (all的作用是不删除重复行,也不对结果进行自动排序)
select column,...from table2

为表和字段取别名
26.为表取别名
from table1 as tb

27.为字段取别名
select column as cm

六.正则表达式查询:
操作符
~:匹配正则表达式,区分大小写
~*:匹配正则表达式,不区分大小写
!~:不匹配正则表达式,区分大小写
!~*:不匹配正则表达式,不区分大小写

28.查询以字符或者字符串开头的记录
select * from table where column ~ '^b'

*29.查询以特定字符或字符串结尾的记录
select * from table where column ~'t'
查询以t结尾的记录

30.用字符'.'来替代字符串中的任意一个字符
select * from table where column ~'a.g'
匹配字符串中包含a和g,且两个字符之间包含单个字母,但不限定匹配字符的位置或者总字符串的长度;
因此orange和mango都符合匹配条件

31.使用*和+来匹配多个字符
*匹配前面字符次数>=0;+匹配前面字符次数>=1
select * from table where column ~'^ba*'(以b开头):匹配结果berry,blackberry,banana
select * from table where column ~'^ba*':匹配结果banana
注:第二个字符是a

32.匹配指定字符串
select * from table where column ~'ba | ca':查询字段column中包含字符串ba或者ca的记录
ps:
like和~区别:like匹配的字符串如果在文本中间出现,则找不到;

PostgreSQL模式匹配关键字similar to 和 like 的区别


33.匹配指定字符以外的字符
select * from table where column !~'ba | ca':查询字段不包含ba和ca的

34.使用{m}或者{m,n}来指定字符串连续出现的次数
{m,}:最少出现m次
select * from table where column ~'ba{1,}':匹配字符串ba至少出现1次

 

0