Python开发学习笔记（11）————Mysql数据库

MySQL数据库

1.数据库的基本使用

数据库：持久化存储、读写速度极高、保证数据的有效性、对程序支持性非常好，容易扩展。
数据表中，每一列称为字段，每一行称为记录。
如果能够唯一标记某个字段，那么就是主键
mysql属于关系型数据库。

1）RDBMS

通过表来表示关系型
所谓的关系型数据库RDBMS，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。
关系型数据库：
oracle：以前的大型项目中使用，银行、电信等项目
mysql：web时代使用最广泛的关系型数据库
ms sql server：在微软的项目中使用
sqlite：轻量级数据库，主要应用在移动平台

2）SQL

sql是结构化查询语言，是一种用来操作RDBMS的数据库语言，当前关系型数据库都支持sql语言进行操作，也就是说可以通过sql操作oracle，sql server，mysql，sqlite等等所有的关系型的数据库。
SQL语句主要分为：
DQL：数据查询语言，用于对数据进行查询，如select
DML：数据操作语言，对数据进行增加、修改、删除，如insert、update、delete
TPL：事务处理语言，对事务进行处理，包括begin transaction、commit、rollback
DCL：数据控制语言，进行授权与权限回收，如grant、revoke
DDL：数据定义语言，进行数据库、表的管理等，如create、drop
CCL：指针控制语言，通过控制指针完成表的操作，如declare cursor
其中，重点是增删改查，必须熟练编写DQL，DML，能够编写DDL完成数据库、表的操作，其他了解即可。
SQL不区分大小写。

3）MySQL

关系型数据库管理系统
特点：
1>使用C和C++编写，并使用了多种编译器进行测试，保证源代码的可移植性
2>支持多种操作系统
3>为多种编程语言提供了API
4>支持多线程，充分利用CPU资源
5>优化的SQL查询算法，有效提高查询速度
6>提供多语言支持，常见的编码如GB2313、BIG5、UTF8
7>提供TCP/IP、ODBC和JDBC等多种数据库连接途径
8>提供用于管理、检查、优化数据库操作的管理工具
9>大型的数据库，可以处理拥有上千万条记录的大型数据库
10>支持多种存储引擎
11>成本低
12>MySQL使用标准的SQL数据语言形式
13>MySQL是可以定制的，采用GPL协议，可以修改源码
14>在线DDL更改功能
15>复制全局事务标识
16>复制无崩溃从机
17>复制多线程从机

4）数据类型、约束

数据完整性
为了更加准确的存储数据，保证数据的正确有效，可以在创建表的时候，为表添加一些强制性的验证，包括数据字段的类型、约束。
a。数据类型
原则：够用就行、尽量使用取值范围小的，节省存储空间
类型：整数int，bit；小数decimal；字符串varchar长度可变的字符串，char长度固定的字符串；日期时间date，time，datetime；枚举类型enum
存储视频图片时，不会直接存在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径。
b。约束
主键：物理上存储的顺序
非空：此字段不允许填写空值
惟一：此字段的值不允许重复
默认：当不填写此值时会使用默认值，如果填写时以填写为准
外键：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常。

5）命令行脚本

链接数据库
mysql -uroot -p
退出数据库
exit/quit/ctrl+d
显示时间
select now()；
查看所有数据库
show databases；
显示数据库版本
select version()；
创建数据库
create database 数据库名；
create database 数据库名 charset=utf8;
查看创建数据库的语句
show create database 数据库名;  
删除数据库
drop database 数据库名;
查看当前使用的数据库
select database();
使用数据库
use 数据库名
显示所有的表
show tables
创建表
create table 数据表名字（字段 类型 约束[，字段 类型 约束]）；
create table xxx(id int, name varchar(30))
create table yyy(id int primary key not null auto_increment, name varchar(30))
create table yyy(
id int primary key not null auto_increment, 
name varchar(30)
);

例子：

create table students(
	id int unsigned not null auto_increment primary key,
	name varchar(30),
	age tinyint unsigned default 0,
	high decimal(5,2),  #小数5位，保留两位小数
	gender enum(“男”，“女”，“中性”，“保密”) default “保密”，#枚举
	cls_id int unsigned
);
insert into students values(0, “laowang”,18,188.88,“男”，0)
查看表的结构
desc 表名字
修改表-添加字段
alter table students add birthday datetime;
修改表-修改字段：不重名版
alter table students modify birthday date;
修改表-修改字段：重名版
alter table students change birthday birth date default;
修改表-删除字段
alter table students drop high;
删除数据库
drop database 数据库名
删除表
drop table 表名
查看表的创建语句
show create table 表名字

6）增删改查

1>增加

insert into 表名 values(要插入的信息)；

枚举中的下标从1开始
自动增长列可以写0，null或default，添加数据后，该列会自动增长。
部分插入：

insert into 表名（列1……） values（值1，…）;

insert into students(name, gender) values(“dkfls”, 2);
多行插入
部分多行插入
insert into students (name, gender) values(“laowang”,5),(“laoli”,6);
全部多行插入

insert into 表名 values（对象1的数据），（对象2的数据）

2>修改

update 表名 set 列1=值1，列2=值2… where 条件；

不写where的话，表里全部数据都会被改。
where处写对应数据的主键。

3>删除

物理删除：

delete from 表名 where 条件；

逻辑删除：
增加一个字段，默认值时表示没删，不是默认值，表示删除

alter table 表名 add is_delete default 0;

4>查询

select * from 表名
select * from 表名 where 条件；
select 列 1，列2，。。。 from 表名
select 字段1 as 别名1， 字段2 as 别名2 from 表名 #为列指定别名输出

上面根据字段的书写顺序，可以调整最终的数据输出顺序

2.数据库查询

1）数据准备

创建一个数据库
create database 数据库名 charset=utf8；
使用数据库
use 数据库名；
显示使用的当前数据库是哪个
select database()；

2)查询

查询所有字段
select * from 表名；  #选择所有字段
查询指定字段
select 列1，列2，… from 表名；
使用as给字段起别名
select 字段1 as 别名1，字段2 as 别名2 from 表名；
select 表名.字段 … from 表名；
使用as给表名起别名
select s 字段1，s.字段2 from 表名 as s；
消除重复行
select distinct 字段 from 表名；
将所查字段进行去重

3）条件查询

比较运算符
select …… from 表名 where 条件；
>, <, >=, <=, =,
!=,<> 不等于
逻辑运算符
and or not
使用not时，not放在字段前面，并且要注意否定的部分，在否定的部分加括号

4）模糊查询

like  % 替换1个或多个   _替换1个

select 字段 from 表名 where 字段 like “小%”；

查询字段以“小”开头的字段

select 字段 from 表名 where 字段 like “%小%”；

查询字段中 有“小”的所有字段

select 字段 from 表名 where 字段 like “__”； 两个下划线

查询字段中，字段包含两个字

select 字段 from 表名 where 字段 like “__%”;

查询字段中，字段中至少有2个字
rlike 正则

select 字段 from 表名 where 字段 relike “^周.*”

字段以周开头

5）范围查询

select 字段 from 表名 where 字段 in （范围）

查询在范围内的字段，范围是非连续，如 （1，3，8） 查询 1 或3 或8。不在就用not in

select 字段 from 表名 where 字段 between 值1 and 值2

查询在值1和值2之间的字段（连续范围）；不在一个连续范围，not between 值1 and 值2
空判断
判空 is null
select * from 表名 whiere 字段 is null；
查询指定字段为0的数据
判非空 is not null

6）排序

order by 字段   asc 从小到大排，desc从大到小排

select * from 表名 where 条件 order by 字段；
按照指定字段来排序，默认为asc
select * from 表名 where 条件 order by 字段1 asc，字段2 asc；
字段1有相同情况下，按照字段2排
select * from 表名 order by 字段 无条件时的排序。

7）聚合函数

总数

select count(*) from 表名 where 条件；

满足条件的字段的总行数
最大值

select max(字段) from 表名 where 条件；

满足条件字段中的最大值
最小值
min
求和 sum

select sum(字段) from 表名 where 条件；

平均值 avg

select avg(字段) from 表名 where 条件；
select sum（字段）/count（*） from 表名 where 条件；

四舍五入

round（123.23，1） 结果保留1位小数
select round（sum（字段）/count（*），3） from 表名 where 条件；

默认保留4位小数，第二个参数设置保留的位数

8）分组

group by

select 字段1 from 表名 group by 字段1  按照指定字段分组

前后字段必须相同

select 字段1， count(*) from 表名 group by 字段1

显示每个组的总数

group_concat 查看组里的内容

select 字段1， group_concat（字段2） from 表名 group by 字段1

查询分组后，每组中字段2的具体内容

select 字段1， count(*) from 表名 where 条件 group by 字段1

分组后 满足条件的组的总数

group_concat（） 参数会连接在一个
group_concat(name, “_”, age, “ “, id) 显示结果为  名字_年龄 id

having

select 字段1，group_concat(字段2) from 表名 group by 字段1 having count(*)>3

分组后，组里总数大于3的组
查询student表中每种性别中的人数多于2个的信息

select gender,group_concat(name) from students group by gender having count(*)>2

9)分页

limit 限制查询出来的数据个数

select * from 表名 where 条件 limit 2；

limet start，count start从0开始，count 显示的个数
limit语句在最后

10）连接查询

多个表的关联查询
内连接查询：查询的结果为两个表匹配到的数据
右连接查询：查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充
左连接查询：查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充

inner join … on  语句
select * from 表1 inner join 表2 on 表1.字段1 = 表2.字段2；

将两个表的字段对应
左连接

select * from students as s left join classes as c on s.cls_id=c.id;

以左边表为基准，有就显示，没有就将右边的表填充为null

11）自关联

表中的某一列，关联了这个表中的另外一列，但是它们的业务逻辑含义是不一样的。

source sql文件名  导入sql文件

表内可以通过连接查询得到需要的信息

12）子查询

一个select里嵌套一个select

select * from students where height = （select max（height） from students）；

13）数据库设计

三范式
第一范式（1NF）：强调的是列的原子性，即列不能够再分成其他几列
第二范式（2NF）：首先是1NF，另外包含两部分内容，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。
第三范式（3NF）：首先是2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖，即不能存在：非主键列A依赖于非主键B，非主键列B依赖于主键的情况。
E-R模型
1对1时，在两个表中任意一个表添加关联字段（另一个表的主键）即可
1对多时，在多的表中添加关联字段（另一个表的主键）
多对多时，新加一张表，作为中间表

3.MySQL与Python交互

创建外键

alter table 表名1 add foreign key 表1中要设置为外键的字段 references 表名2（字段）

删除外键

alter table 表名 drop foreign key 外键名称；

实际开发中，尽量少用外键。

1）查询流程

开始->创建connection->获取cursor->执行查询、执行命令、获取数据、处理数据->关闭cursor->关闭connection->结束

创建connection连接
conn = connect(host=’localhost’,prot=3306,user=’root’,password=’mysql’,database=’jing_dong’,charset=’utf8’)
创建cursor对象
csl=conn.cursor()
执行select语句，查询数据
count = csl.execute(‘select id,name from goods where id>=4’)
csl.fetchone（）   返回一行数据
csl.fetchmany(数字)  根据所传参数，返回相应行数据
csl.fetchall()  显示全部数据
进行其他操作
关闭cursor对象
csl.close()
conn.close()

2）使用python进行增删改

csl.execute(“增删改的sql语句”)
csl.commit()   这条语句必不可少
csl.rollback()   不提交，回滚 

3）sql注入

对用户输入的内容不加限制的话，用户可能通过sql注入查询到所有的数据
防止措施

self.cursor.execute(sql,[find_name])   构造参数列表

4.MySQL高级

1）视图

视图就是一条select语句执行后返回的结果集。
视图是对若干张基本表的引用，一张虚表，查询语句执行的结果。
定义视图
create view 视图名字 as sql语句 #把sql语句得到的数据保存为视图
视图作用：提高了重用性，类似函数；对数据库重构，不影响程序运行；提高了安全性能，可以针对不同的用户；让数据更清晰。

2）事务

一个操作序列，这些操作要么执行，要么都不执行，它是一个不可分割的工作单位。
四大特性：原子性、一致性、隔离性、持久性。
引例，银行转账
流程

1.start transaction;
2.select balance from checking  where customer_id =10231;
3.update checking set balance = balance -200 where customer_id = 10231;
4.update savings set balance = balance +200 shere customer_id = 10231;
5.commit;