python开发数据库程序_Python编程-数据库

1、MySQL

程序：

socket客户端

2. 根据IP和端口进行连接

3. 发送指令：

xxx

7. 接收结果

socket服务端

运行起来

获取指令(recv)

xxx

解析命令

文件夹操作：

...

文件操作：

...

文件内容操作：

...

返回结果

数据库管理系统(软件)

客户端

- Java写的

- Python写的

- xxx

- MySQL自带的(mysql -uxxx -p xxx)

PS:发送指令规则,SQL语句

服务端

- mysqld

SQL语句：

文件夹操作(数据库)：

创建:

create database db_name;

create database db_name default charset utf8;

删除:

drop database db_name;

进入文件夹：

use db_name

文件操作(数据表)：

创建:

单表：

create table user_info(

id int not null auto_increment primary key,

name char(20),

age int,

gender char(1)

)engine = innodb default charset=utf8;

insert into user_info(name,age,gender) values('alex',19,'男');

列名

数据类型

是否可以问空(null,not null)

默认值(default 1)

自增(auto_increment) 一个表只能有一个自增列

主键(primary key)

约束：数据不能为空、数据不能重复。

索引：加速查找(B树结构)

外键:

对某列数据的内容进行约束。

约束:只能是某个表中某列已经存在的数据。

constraint 约束名称 foregin key(deparment_id) references dep_info(id)

含外键的关系成为1对多。

多表:

一对多：

create table user_info(

id int not null auto_increment primary key,

name char(20),

age int,

gender char(1),

deparment_id int,

constraint 约束名称 foregin key(deparment_id) references dep_info(id)

)engine = innodb default charset=utf8;

create table dep_info(

id int not null auto_increment primary key,

title char(32),

)engine=innode default charset=utf8;

多对多：(关系表)

create table boy(

id int not null auto_increment primary key,

name char(32)

)engine = innodb default charset=utf8;

create table girl(

id int not null auto_increment primary key,

name char(32)

)engine = innodb default charset=utf8;

create table b2g(

id int not null auto_increment primary key,

b_id int,

g_id int,

constraint 约束名称1 foregin key(b_id) references boy(id),

constraint 约束名称2 foregin key(g_id) references girl(id)

)engine = innodb default charset = utf8;

应用场景：

1、根据具体业务

2、HTML的场景

创建的时候只需要输入数据的情况下：单表

创建的时候必须要我们选择一个其他数据的场景下：一对多

创建的时候针对某些字段可以进行多选的情况下：多对多

删除:

drop table table_name;

文件内容操作(数据行)：

插入：

insert into table_name(field) values(value),(value2) --> 两个(value)表示插如多行数据

insert into table_name(cname) select field from table_name --> 把select查到的结果,当作数据来赋值给value

删除:

清空表：

delete from table_name --> 自增列会继续之前的ID

truncate table table_name --> 物理删除，速度快，重新计算ID

删除某一条：

delete from table_name where filed = values and/or ... --> 只删除符合条件的数据

delete from table_name where filed in (1,2,3,4)

delete from table_name where id between 5 and 10

修改:

update table_name set field = 'value' --> 更新所有数据的field字段的值，加 where 只修改匹配到的行

update table_name set id = 8 , name = 'daxin' where age = 18;

查询：

select * from table_name where id > 2

select field as '别名' from table_name --> 加别名

select * from table_name where id in (1,2)

select * from table_name where cid in (select tid from teacher)

条件：

select * from table_name order by field asc/desc(正序/倒序)

select * from table_name order by field asc limit 1 取第一个值

select * from table_name limit 1,2(起始位置,找几个)

select * from table_name where field like '%key%' --> 查找field字段包含key的数据% 表示任意个任意字符， _表示任意一个字符

分组:

select * from table_name group by field --> 分组显示,会去重,需要使用聚合函数来统计重复的次数

select field,count(id) from table_name group by field --> 对id字段进行聚合(其他的还有min(),max(),sum(),avg()等)

例子：

1、获取每个班级多少人

SELECT class.caption,count(sid) from class

LEFT join student on student.class_id = class.cid

group by class.caption

2、获取每个班级有多少人并且选出认识大于2的班级，

注意：如果针对 group by 的结果进行筛选,那么需要使用 having 不能在使用 where 了.

SELECT class.caption,count(sid) as number from class

LEFT join student on student.class_id = class.cid

group by class.caption

HAVING number >= 2

3、每个课程的不及格的人数。

select course.cname,count(sid) from score

left join course on score.corse_id = course.cid

where number < 60

group by course.cname

连表：

select student.sid,student.sname,class.caption from student LEFT JOIN class on student.class_id = class.cid ;

把class表中的字段放在student表的左边,并且进行 student.class_id = class.cid 匹配后显示，数据量以from指定的表为基准

left join:

以 from 指定的表为基准,对数据进行显示

right join: -->不常用

以 join 后面的表为基准进行显示。

inner join:(join 使用的就是)

只保留连个表中都有数据的条目

例子：

1、id=1的老师任教的课程名称

2、老师姓名瞎猫任教的课程命令

3、已选课程id=1,所有学生的姓名

4、已选体育课，所有学生的姓名

5、已选波多任教任意课程,所有学生姓名

union:

把两个SQL的结果进行组合(上下合并)

select * from student

union / union all

select * from teacher;

注意上下两个表中的列数要统一

注意：

1、如果所有数据都一致，那么union会对结果进行去重

2、union all ,会保存所有的

MySQL的扩展：wupeiqi 博客

基本数据类型：

MySQL的数据类型大致分为：数值、时间 和 字符串。

数字：

整数

tinyint 小整数,数据类型用于保存一些范围的整数数值范围。

smallint

int

bigint

小数

float 浮点型(长度越长越不精准)

double 浮点型(双精度,精度比float稍高) 范围比float更大

decimal 精准(内部使用字符串进行存储的) -> 适合对精度有要求的

字符串

char(19)[字符长度] 定长字符串 --> 占用空间大，但是效率高

varchar(19)[字符长度] 不定长字符串 --> 占用空间是可变的，但是效率低

注意：最大可以存放255个字符

text() 65535个字符

mediumtext() 16777215个字符

longtext() 4294967254个字符

二进制：

TinyBlob

Blob

MediumBlob

LongBlob

存文件:虽然可以用二进制进行存储,但是一般是存储文件在服务器上的路径(URL)

时间：

date YYYY-MM-DD

time HH:MM:SS

year YYYY

DATETIME YYYY-MM-DD HH:MM:SS -->常用

TIMESTAMP 时间戳格式

Python操作MySQL

python 3.x 中使用 pymysql

python 2.x 中使用 mysqldb

方法和使用规则是一模一样的。

用户登录改造

sql拼接,引出SQL注入问题

提交 commit

cursor.lastrowid 获取上一个增加的数据的自增ID

指定查询的数据类型

cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)