python学习之数据库操作-白红宇

                               
一、数据库基本操作
数据库操作:
查看存储引擎: show engines;
查看数据库: show databases; 或者show create database oldboy\G
创建数据库: create database oldboy default charset
=
utf8;
删除数据库: drop database oldboy;
进入数据库:  use oldboy;
数据表操作:
创建数据表:
create table tb_emp1 (
    
-
> 
id
int
(
11
),
    
-
> name varchar(
25
),
    
-
> deptId 
int
(
11
),
    
-
> salary 
float
    
-
> )engine
=
innodb default charset
=
utf8;
primary key:
create table tb_emp2 (                                                                     
    
-
> 
id
int
(
11
) primary key,
    
-
> name varchar(
25
),
    
-
> deptId 
int
(
11
),
    
-
> salary 
float
    
-
> )engine
=
innodb default charset
=
utf8;
    
多个primary key:
create table tb_emp3 ( 
id
int
(
11
), name varchar(
25
), deptId 
int
(
11
), salary 
float
,
 
primary key(name, deptId) )engine
=
innodb default charset
=
utf8;  
 
外键约束foreign key:
主表:
create table tb_dept1 (
    
-
> 
id
int
(
11
) primary key,
    
-
> name varchar(
22
) 
not
null,
    
-
> location varchar(
50
)
    
-
> )engine
=
innodb default charset
=
utf8;  
    
从表:
create table tb_emp5 (
    
-
> 
id
int
(
11
) primary key,
    
-
> name varchar(
25
),
    
-
> deptId 
int
(
11
),
    
-
> salary 
float
,
    
-
> constraint fk_emp_dept1 foreign key(deptId) references tb_dept1(
id
)
    
-
> )engine
=
innodb default charset
=
utf8;
    
非空约束
not
null:
create table tb_emp6 (
    
-
> 
id
int
(
11
) primary key,
    
-
> name varchar(
25
) 
not
null,    
#非空
    
-
> deptId 
int
(
11
),
    
-
> salary 
float
,
    
-
> constraint fk_emp_dept2 foreign key(deptId) references tb_dept1(
id
)
    
-
> )engine
=
innodb default charset
=
utf8;
唯一性约束unique,要求该列唯一，允许为空，但只能有一个值为空:
create table tb_dept2 (
    
-
> 
id
int
(
11
) primary key,
    
-
> name varchar(
22
) unique,
    
-
> location varchar(
50
)
    
-
> )engine
=
innodb default charset
=
utf8;
    
默认值default:
create table tb_emp7 (
    
-
> 
id
int
(
11
) primary key,
    
-
> name varchar(
25
) 
not
null,
    
-
> deptId 
int
(
11
) default 
1111
,
    
-
> salary 
float
,
    
-
> constraint fk_emp_dept3 foreign key(deptId) references tb_dept1(
id
)
    
-
> )engine
=
innodb default charset
=
utf8;
    
自增主键auto_increment:
create table tb_emp8 (
    
-
> 
id
int
(
11
) primary key auto_increment,
    
-
> name varchar(
25
) 
not
null,
    
-
> deptId 
int
(
11
),
    
-
> salary 
float
,
    
-
> constraint fk_emp_dept5 foreign key(deptId) references tb_dept1(
id
)
    
-
> )engine
=
innodb default charset
=
utf8;
插入tb_emp8三条数据:
insert into tb_emp8(name,salary) values(
'Lucy'
, 
1000
),(
'lura'
, 
1200
),(
'Kevin'
,
1500
);
id
自增
查看表结构: desc tb_emp8; 或者show create table tb_emp8\G
修改数据表alter
修改表名: alter table tb_dept2 rename tb_deptment3;
修改字段类型: alter table tb_dept1 modify name varchar(
30
);
修改字段名: alter table tb_dept1 change location loc varchar(
50
);
添加字段: alter table tb_dept1 add managerId 
int
(
10
);
添加有约束条件的字段: alter table tb_dept1 add column1 varchar(
12
) 
not
null;
在某个位置添加字段: alter table tb_dept1 add column2 
int
(
11
) first;
在某个字段后面添加新字段: alter table tb_dept1 add column3 
int
(
11
) after name;
删除字段: alter table tb_dept1 drop column2;
修改表的存储引擎: alter table tb_deptment3 engine
=
MyISAM;
删除外键约束: alter table tb_emp9 drop foreign key fk_emp_dept;
删除数据表: drop table 
if
exists tb_dept2;
删除外键关联的主表，需要首先取消外键关联，否则删除主表失败
数据类型
整形: TINYINT 
1
个字节  
2
*
*
8
-
1
=
255
个值
整形: smallint 
2
个字节
整形: 
int
4
个字节
整形: bigint 
8
个字节
浮点数
单精度: 
float
4
个字节
双精度: double 
8
个字节
decimal: 不固定，一般用于财务系统
日期时间类型
year: 
1
个字节,格式 YYYY
time: 
3
个字节,格式 HH:MM:SS
date: 
3
个字节,格式 YYYY
-
MM
-
DD
datetime: 
8
个字节，格式 YYYY
-
MM
-
DD HH:MM:SS
timestamp: 
4
个字节，格式YYYY
-
MM
-
DD HH:MM:SS
字符串类型
char(n): 固定长度字符串    
#浪费内存，但查询速度快
varchar(n): 非固定长度字符串    
#节省内存，但查询速度慢
text: 存放文本
longtext: 存放大数据文本
between .. 
and
.. 关键字使用
select 
4
between 
4
and
6
, 
4
between 
4
and
6
, 
12
between 
9
and
10
;
in
, 
not
in
关键字使用
select 
2
in
(
1
,
3
,
5
,
'thks'
), 
'thks'
in
(
1
,
3
,
5
,
'thks'
);
like用来匹配字符串
'%'
: 匹配任何数目的字符
'_'
: 只能匹配一个字符
select查询数据
create table fruits (
    
-
> f_id char(
10
) 
not
null,
    
-
> s_id 
int
not
null,
    
-
> f_name char(
255
) 
not
null,
    
-
> f_price decimal(
8
,
2
) 
not
null,
    
-
> primary key(f_id)
    
-
> ) engine    
-
> ) engine
=
innodb 
=
utf8;
    
插入字段:
insert into fruits(f_id,s_id,f_name,f_price) values
    
-
> (
'a1'
,
101
,
'apple'
,
5.2
),
    
-
> (
'b1'
,
102
,
'blackberry'
,
10.2
),
    
-
> (
'bs1'
,
102
,
'orange'
,
11.2
),
    
-
> (
'bs2'
,
105
,
'melon'
,
8.2
),
    
-
> (
't1'
,
102
,
'banana'
,
10.3
),
    
-
> (
't2'
,
102
,
'grape'
,
5.3
),
    
-
> (
'o2'
,
103
,
'coconut'
,
9.2
),
    
-
> (
'c0'
,
101
,
'cherry'
,
3.2
),
    
-
> (
'a2'
,
103
,
'apricot'
,
2.2
),
    
-
> (
'l2'
,
104
,
'lemon'
,
6.4
),
    
-
> (
'b2'
,
104
,
'lemon'
,
7.6
),
    
-
> (
'm1'
,
106
,
'mango'
,
15.6
),
    
-
> (
'm2'
,
105
,
'xbabay'
,
2.6
),
    
-
> (
't4'
,
107
,
'xbababa'
,
3.6
),
    
-
> (
'm3'
,
105
,
'xxtt'
,
11.6
),
    
-
> (
'b5'
,
107
,
'xxxx'
,
3.6
);
单表查询:
查询表: select f_id,f_name 
from
fruits;
查询条件where: 
select f_id,f_name 
from
fruits where f_price
=
10.2
;    
#等号 =
select 
*
from
fruits where f_price < 
10
;        
#小于
select 
*
from
fruits where s_id 
in
(
101
,
102
) order by f_name (desc);    
#in关键字,按f_name排序，desc降序，asc升序
select 
*
from
fruits where f_price between 
2.00
and
10.20
;    
#between and
select 
*
from
fruits where f_name like 
'b%'
;    
#like关键字匹配, %匹配任何多个字符
select 
*
from
fruits where f_name like 
'_____y'
;    
#_匹配任意一个字符
select 
*
from
fruits where s_id
=
'101'
and
f_price >
=
5
;    
#and多条件匹配
select 
*
from
fruits where s_id
=
'101'
or
s_id
=
102
;    
#or多条件匹配
order by查询结果排序
select 
*
from
fruits order by f_name;
select 
*
from
fruits order by f_price desc;    
#desc倒叙排列
group by分组
select s_id,count(
*
) as Total 
from
fruits group by s_id; 
#根据s_id分组,s_id相同的数量
select s_id,count(
*
) as Total 
from
fruits group by s_id having count(f_name) > 
1
;    
#having后面加上查询条件
limit限制查询的数量
select 
*
from
fruits limit 
4
;    
#查询四条
select 
*
from
fruits limit 
4
,
3
;    
#索引为4，从第五条开始返回3条
inner join 内连接,返回两表中都有的记录
create table suppliers (
    
-
> s_id 
int
(
11
) 
not
null auto_increment primary key,
    
-
> s_name char(
50
) 
not
null,
    
-
> s_city char(
50
),
    
-
> s_zip char(
10
),
    
-
> s_call char(
50
) 
not
null
    
-
> )engine
=
innodb default charset
=
utf8;
以下操作是fruits和suppliers关联
select suppliers.s_id, s_name, f_name, f_price 
from
fruits inner join suppliers on 
    
-
> fruits.s_id 
=
suppliers.s_id;    
#on后面是条件查询，
left join    返回包括左表中的所有记录和右表连接字段的所有记录
select s_name,f_price 
from
fruits left join suppliers on fruits.s_id 
=
suppliers.s_id;
子查询:
select s_id, f_name 
from
fruits where s_id
=
(select s1.s_id 
from
suppliers as s1 where s1.s_city
=
'Tianjin'
);
union合并查询结果并去重
union 
all
合并查询不去重
select s_id ,f_name,f_price 
from
fruits where f_price < 
9.0
union 
all
select s_id,f_name,f_price 
from
fruits where s_id 
in
(
101
,
103
);
          
        

                               
二、pymysql模块操作数据库
例子:
#!/usr/bin/python
# --*-- coding:utf-8 --*--
import
pymysql
conn 
=
pymysql.Connect(host
=
'127.0.0.1'
,user
=
'root'
,password
=
'aixocm'
,port
=
3306
,database
=
'oldboy'
,charset
=
'utf8'
)
cursor 
=
conn.cursor()
v 
=
cursor.execute(
'select * from student'
)
print
(v)
#cursor.fetchone()    #获取一条数据
#cursor.fetchmany(2)    #获取多条数据
result 
=
cursor.fetchall()    
#获取所有数据
print
(result)
cursor.close()
conn.close()
#!/usr/bin/python
# --*-- coding:utf-8 --*--
import
pymysql
conn 
=
pymysql.Connect(host
=
'127.0.0.1'
,user
=
'root'
,password
=
'aixocm'
,port
=
3306
,database
=
'oldboy'
,charset
=
'utf8'
)
cursor 
=
conn.cursor()
#v = cursor.execute('insert into student(name) values("test")')
#print(v)
v 
=
cursor.execute(
'delete from student where sid=7'
)
conn.commit()    
#提交事务
cursor.close()
conn.close()
#!/usr/bin/python
# --*-- coding:utf-8 --*--
import
pymysql
num
=
8
conn 
=
pymysql.Connect(host
=
'127.0.0.1'
,user
=
'root'
,password
=
'aixocm'
,port
=
3306
,database
=
'oldboy'
,charset
=
'utf8'
)
cursor 
=
conn.cursor()
#v = cursor.execute('insert into student(name) values("test")')
#print(v)
v 
=
cursor.execute(
'delete from student where sid=%d'
%
(num))  
#防止sql注入
conn.commit()
cursor.close()
conn.close()
          
        

本文转自小白的希望 51CTO博客，原文链接：http://blog.51cto.com/haoyonghui/1945907

，如需转载请自行联系原作者