Python（MySQL数据库）

Python高级
-05-数据库

一、RDBMS数据库简介

1、mysql：关系型数据库-适合做网站用
    

安装：

（1）下载mysql数据库- https://dev.mysql.com/downloads/mysql/
（2）cmd 切换到解压的bin目录（或者添加环境变量），执行相关命令
（3）命令

• ①安装: mysql  --install
• ②初始化：mysql --initalize --console
• ③开启服务： net start mysql
• ⑤关闭服务：net stop mysql
• ⑥登录mysql: mysql -u root -p

Enter PassWord:(密码)或者直接在-p后面输入密码

• ⑦修改登录密码： alter user  "root"@"localhost" identified by 新密码
• ⑧标记删除mysql服务：sc delete mysql

安装操作工具navicat ：Navicat for MySQL是一套快速、数据库管理工具，专为简化数据库的管理及降低系统管理成本而设。具有简洁美观的界面，支持数据传输和导入导出等功能，Navicat for MySQL可以连接到任何本机或远程服务器、提供一些实用的数据库工具如数据模型、数据传输、数据同步、结构同步、导入、导出、备份、还原、报表创建工具及计划以协助管理数据。
2、redis：做缓存用
3、mongdb：非关系型数据库-适合做爬虫

二、SQL语言

1、作用：结构化查询语言，操作RDBMS数据库语言，不区分大小写；sql语言相当于沟通RDBMS客户端与RDBMS服务器之间的一种语言

 2、语法
注释：        -- 
        .sql文件里面表示注释
语句结束：        ；
        --sql语句最后需要分号“；”结尾

 （1）数据类型

数字类型：

字符串类型：

时间和日期类型:

枚举类型：enum

注：对于图片、音频等文件，不存在数据库中，而是上传到某个服务器，然后在表中储存这个文件的保存路径
注：尽量取值小，够用就行，节省空间

 (2)约束（对字段的值进行一定限制）

（1）not null ：非空约束，保证字段的值不能为空

（2）default：默认约束，保证字段总会有值，即使没有插入值，都会有默认值

（3）unique：唯一，保证唯一性但是可以为空

（4）primary key ：主建约束，同时保证唯一性和非空

（5）foreign key：外键约束，用于限制两个表的关系，保证从表该字段的值来自于主表相关联的字段的值，注：关联后虽然能保证数据的有效性，但是会降低性能
（6）auto_increment：自动增长

3、数据库常用操作
连接数据库：mysql -u root -p登录密码

退出数据库：exit或quit

（1）对数据库操作

1、增
--创建数据库
create database 数据库名 charset=utf8;
例：create database python2;  (不指定编码)
例：create database python3 charset=utf8;

2、查

--查看所有数据库
show databases;
--显示时间
select now();
--显示数据库版本
select version();
--查看创建的数据库
show create database 数据库名；
--查看当前使用的数据库
select database();

3、删
--删除数据库
drop database 数据库名;

4、使用某数据库
--使用数据库
use 数据库名;

(2)对数据表操作

1、增
--创建表
create table 数据表名字(字段 类型 约束[, 字段 类型 约束]）；
例：创建students表（id, name,age,high,gender,cls_id）
create table students(
    id int unsigned primary key not null auto_increment,
    name varchar(10),
    age tinyint unsigned,
    high decimal(5,2),
    gender enum("男","女","保密") default "保密",
    cls_id int unsigned
);

2、查
--查看数据库中左右表的名字
show tables;
--查看数据表结构
desc 数据表的名字；
--查看表的创建语句
show create table 表名字；

3、删

--删除表
drop table 表名；

（3）对数据的操作

 1、对字段操作

添加字段
alter table 表名 add 字段 [类型 约束]；
例：alter table students birthday datatime
修改字段
（1）修改类型约束
alter table 表名 modify 字段 类型及约束；
（2）修改字段名类型及约束
alter table 表名 change 原名 新名 类型及约束；
删除字段
alter table 表名 drop 字段；

2、对数据操作

（1）增

①单个插入
--全列插入，必须把数据填满
insert into 表名 values(...);
注：主键字段可以用0 null default 来占位；枚举类型，下标从1开始。添加枚举类型可以用下标代替
例：insert into students values(1, "枫叶", 18, 175, "男", 5);
--部分插入，可以指定只填部分数据（除主键外，不能为空的数据必须填
insert into 表名(列1，.....) values(值1，....)；
注：没有填的有默认值使用默认值，没有默认值数据库自动给你设置为null , 0 等
例：insert into students(name, gender) values("枫叶", "男");

②多行插入
--全列
insert into 表名 values(...), (...)；
--部分
insert into 表名(列1，.....) values(值1，....), (值1...)；

（2）改

--修改数据，不写where条件表示全部修改
update 表名 set 列1=值1，列2=值2... where 条件；
例：update students set gender="女" where name="鸡蛋";

（3）查

--查询表中所有数据
--select * from 表名；
--根据条件查询数据
select * from 表名 where 条件；
--查询指定列
select 列1,列2... from 表名
--为列或者表指定别名
select 字段[as 别名], 字段[as 别名] from 表名 where ...

（4）删

--物理删除，不加条件where，就是全删
delete from 表名 where 条件
--逻辑删除，用一个字段来表示这条信息已经不能在用了，给表添加一个is_delete字段 bit 类型，0表示存在，1表示删了
alter table students add is_delete bit default 0;
update students set is_delete=1 where id=6;

4、数据查询（进阶）

1、普通查询
查询所有字段
select  *  from  表名；
查询指定字段
select  列1,列2...  from 表名;
使用as给字段起别名
select 字段 as  别名,  字段  as  别名  from 表名 
通过as给表起别名
select 表名.字段，表名.字段 ... from 表名
select 别名.字段，别名.字段 ... from 表名  as  别名
查询时消除重复行
distinct  字段
例：select  distinct 字段  from 表名;

2、条件查询

比较运算符： >  、 <  、>=  、<= 、= 、!= 、<>（不等于）
逻辑运算符：and 、 or 、 not

例：

--18到28岁之间的学生信息
select * from students where age>18 and age<28;
--18岁以上的女性
select * from students where age>18 and sex="女";
搞不清优先级时，就使用括号（）,括号里面优先级高
--不在  18岁以上的女性这个范围
select * from students where not （age>18 and sex="女"）;
--18岁及以下的女性
select * from students where （not age>18） and sex="女";
--18以上或者身高180以上（包含180）
select * from students where age>18 or high>=180;

3、模糊查询

like：字段 like  "xx%xx_xx"
%含义：替换0个、一个或者多个
_含义：替换一个
例：查询名字中以‘小’开头的名字
select name from students where name like "小%";
这种查询方式效率低
rlike：rlike 正则
例：：查询以‘周’开头的名字
select name from students where name rlike "^周.*"
4、范围查询

in：in  xxx   一个非连续范围
例：查询年龄为16 ， 18 ，25的信息
select name,age from students where age in (16, 18, 25);
not in：not  in  xxx 不非连续范围内
例：查询年龄不为16， 18， 25的信息
select name age from students where not in (16, 18, 25)

between  ...  and ...  ：表示在一个连续范围内
例:查询年龄在16-18之间的信息
select name, age from students where age between 16 and 18
not between  ...  and ...  :表示在不一个连续范围内
例:查询年龄不在16-18之间的信息
select name, age from students where age not between 16 and 18

5、空判断

is null：判断为空
例：查询身高为空的信息
select * from students where high is null
is not null：判断不为空
例：查询身高不为空的信息
select * from students where high is not null

5、数据操作 

1、排序
（1）order by 字段  升降序
asc从小到大排列，即升序，默认
desc从大到小排列，即降序
例：查询年龄18-34岁之间的男性，按照年龄从小到大排排序
select * from students where (age between 18 and 34) and sex="男" order by age asc;
      查询年龄18-34之间的女性，按照身高从大到小排序
select * from students where (age between 18 and 34) and sex="女" order by age desc;
（2）order by 多个字段
若有相同，默认按照主键排，此时可以设置多个排序字段
例：查询年龄18-34之间的女性，按照身高从大到小排序，若身高相同，按照年龄从小到大排
select * from students where (age between 18 and 34) and sex="女" order by age desc, age asc;

2、聚合函数
（1）统计总数：count()
例：查询男性有多少人
select count(*) from students where sex="男";
        起别名
select count(*) as 男性人数 from students where sex="男";
（2）最大值：max()
例：查询最大的年龄
select max(age) from students;
（3）最小值:min()

（4）求和:sum()
例:计算所有人年龄和
select sum(age) from students;
（5）平均值:avg()
例:计算年龄的平均值
        select avg(age) from students;
计算年龄的平均值(select 后面可以放表达式)
        select sum(age)/count(*) from students；
（6）四舍五入:round(13.3, 1)  保留一位小数
例:计算所有人的平均年龄，保留2位小数
select round(avg(age), 2) from students;

3、分组

group by

把所有的数据按照某个字段进行分组，字段具有相同数据的分在一个组
select xx from students group by xx
注：前面的字段xx必须和后面字段xx一致；分组的意义在于和聚合函数一起使用；分组后聚合函数的值，仅仅来自于对应的组
例：按照性别分组，并查询各组性别的人数
select sex count(*) from students by sex;

group_concat(字段或字符串，字段或字符串....)函数
作用：查询同一个分组字段的数据，把所有的内容是连在一起的
例：查询年龄大于16，同种性别中所有人的姓名
select sex, group_concat(name) from students where age>16 group by sex;

注：条件写在分组的前面
having:对分组进行过滤
例:查询平均年龄超过30岁的性别，以及姓名。 having avg(age) > 30
select sex, group_concat(name) from students group by sex having avg(age)>30;
where、having区别:where 写在分组前，having 写在分组后，where是对原数据表进行条件判断，having是对分组进行条件判断

4、分页（限制查询个数）

limit start, count
start -查询起始下标，从0开始（对应第一个数据）
count - 需要查询的个数
limit count
count - 需要查询的个数（从第一个数据开始查询）
例：查询前5个数据
select * from students  limit 5;
select * from students  limit 0, 5;

例：分页

每页显示2个，第一个页面
select * from students limit 0,2;
每页显示2个，第二个页面
select * from students limit 2,2;
每页显示2个，第三个页面
select * from students limit 4,2;
规律:limit (第n页-1)*每页个数,  每页的个数

limit写在所有关键字（where 、order by...）最后

5、连接查询

（1）内连接查询(inner join ... on)

inner join 
inner join ... on

select ... from 表A inner join 表B；(把两个表合起来，A表里面每个数据与B表里面每个数据合一遍)
select ... from 表A inner join 表B on 条件；
例：查询有能够对应班级的，只要学生姓名以及班级名称信息, 并按照班级排序
select s.name as 学生姓名, c.cls_name as 班级名称 from students as s inner join classes as c on s.cls_id=c.id;

 (2)左连接查询
left join ... on

以左边的表为基准合并表，右边表找不到数据默认为null
查询的结果，可用having进行条件判断取值
查询的结果相当于一个新表，可用where进行条件判断取值(不推荐)
从原表找数据一般用where,查询结果中找数据having
例：查询没有对应班级的信息
select * from students as s left join classes as c on s.cls_id=c.id having c.id is null;
（3）右连接查询
right join ... on

以右边的表为基准合并表，左边表找不到数据默认为null
常用左连接，互换表顺序即可做到右连接

 6、自关联：一个表里面的字段关联另一个字段
通过as对表起不同的别名，就可以把一张表当做多张表来使用
select * from areas as province inner join areas as city on province.aid=city.pid having province.atitle="山东省"
应用：省市县，部门行政级别

7、子查询：一个select语句中嵌套了另一个select语句
例：查询高出平均身高的信息
select * from students where high>(select avg(age) from students);
查询最高男生的信息
select * from students where sex="男" and high=(select max(high) from students where sex="男");
注：子查询会降低查询的性能

6、其他操作
 （1）if not exists
创建表时判定是否存在，不存在则创建

create table if not exists 表名(
    字段 类型 约束，
    字段 类型 约束，
    ...
); 

（2）将分组结果写入表

insert into 表名A(字段)  select 字段1 from 表名B group by 字段1  

（3）update与连接查询结合使用更新表

update 表A inner join 表b on 条件 set 表A.字段=表B.字段

（4）外键

限制填写的数据：把两个表通过字段关联起来，外键的值不能是对应关联以外的数据
alter table 表名A add foreign key(A里面字段) references 表B(B里面字段)
A里面字段与B里面字段字段类型应该相同
注：若外键的值 存在对应关联数据以外的值，外键添加会失败
取消外键：外键会降低数据库性能，尽量少用外键

步骤（1）：查看外键的名字
show create table 表名；

步骤（2）：删除外键
alter table 表名 drop foreign key 有外键的字段

（5）创建表时顺便插入数据

create table goods_brands(
    id int unsigned  primary key auto_increment,
    name varchar(10) not null) select 字段1 as name from 表名 group by 字段1

（6）alter修改重命名多个字段

alter table goods
change cate_name cate_id int unsigned not null,
change brand_name brind_id int unsigned not null;

三、数据库设计原则

范式（Normal From）：经过设计和研究，对数据库的设计提出的一些规范

1NF:

强调列的原子性，即列不能够拆分成多个列
2NF:

(1)表必须有主键

(2)没有包含在主键中的列必须完全依赖主键，而不能依赖主键的一部分
3NF:

(1)2NF前提下
(3)另外非主键必须直接依赖于主键，不能存在传递依赖。即不能存在非主键A依赖于非主键B,非主键B依赖于主键

E（entry实体）-R（relationship关系）模型

一对一:

随便修改那个

多对一:

修改多

多对多:

新建表进行关联

四、Python操作数据库（pymysql模块）

pymysql模块作用：相当于把python当做数据库的客户端，把sql语句发送给数据库服务器
服务器把查询结果返回给python程序

1、查询数据（不修改数据库）

 使用步骤：
一、创建connect
import pymysql
conn = pymysql.connect(......)

• pymysql.Connect()参数说明
• host(str): MySQL服务器地址（本地用localhost）
• port(int): MySQL服务器端口号(3306)
• user(str): 用户名（root）
• passwd(str): 密码
• database(str):  数据库名称
• charset(str):   连接编码

例：

import pymysql
connection = pymysql.connect(user="root", host="localhost", \
port=3306,database="fy_goods", password="123456",charset="utf8")

• connection对象支持的方法
• cursor()        使用该连接创建并返回游标
• commit()        提交当前事务
• rollback()      回滚当前事务
• close()         关闭连接

二、获取游标对象cursor
cs= conn.cursor()

• cursor对象支持的方法
• execute(op)     执行一个数据库的查询命令
• fetchone()      取得结果集的下一行
• fetchmany(size) 获取结果集的下几行
• fetchall()      获取结果集中的所有行
• rowcount()      返回数据条数或影响行数
• close()         关闭游标对象

三、游标对象的execute()方法执行查询sql语句
count = cs.execute("sql语句")
返回值：查询到的数据条数
四、利用对应游标取数据
取一条：fetchone()
取指定条：fetchmany(num)；参数：num-条数
全部取：fetchall()
五、关闭cursor、关闭connection
cs.close()
conn.close()

2、增删改（会修改数据库） 

使用步骤：
一、创建connection
二、获取游标对象cursor
三、execute执行增删改sql语句
count = cs.execute("sql语句")
count = cs.execute("sql语句"，parm_list)
sql语句里面%s,%d等需要的填充的数据，放在parm_list列表内,让execute()方法自动去填充，可以防止sql注入
若进行了误操作
提交前可以使用conn.rollback()进行撤销
四、提交修改数据（ 之前执行的增删改立马生效）
conn.comit()
五、关闭cursor、关闭connection

3、sql注入
写入查询数据时写sql语句，获取信息
防止sql注入：count = cs.execute("sql语句"，parm_list)
sql语句里面%s等需要的填充的数据，放在parm_list列表内数据全部,让execute()方法自动去填充
填充数据全部写成%s, 写成%d、%f会报错。内部会把parm_list里面全部数据转成字符串去自动填充。

五、MySQL高级

1、视图

就是一条SELECT语句执行后返回的结果集，视图是对若干基本表的引用，一张虚表，查询语句执行结果

• 是一个虚拟表，并不是创建一个备份，什么时候用，才去查
• 修改了创建视图的表，虚拟表查询结果也跟着变。

作用：

（1）隔离了数据库，数据库变的情况下，保证程序语句不变，视图的目的是为了方便查询数据，不能用于改数据

（2）提高了重用性

（3）对数据库重构，却不影响程序运行

（4）提高了安全性能，可以对不同的用户

（5）让数据更加清晰

使用：
创建视图
    create view 视图名 select ....
删除视图
    drop view 视图名

2、事务 

他是一个操作序列，这些操作要么都执行，要么都不执行，是一个不可分割的单位

事务的四大特性：

• 原子性(Atomicity)：不可分割
• 一致性(Consistency)：要么成功，要么不成功
• 隔离性(Isolation)：一个事务没有提交以前，对其他事务是不可见的，保证数据稳定（不会因为多个用户操作，而导致错乱）
• 持久性(Durability)：一旦事务提交，其所作修改永久保存在数据库（即使系统崩溃，修改的数据也不会丢失）

开启事务：

方式一

start transaction;
...
之间的代码要么都成功，要么都失败
...
commit;

方式二

begin;
...
之间的代码要么都成功，要么都失败
...
commit;

说明：
start transaction或begin开启事务
只要开启了事务，没有提交数据，数据库不会真正修改数据
commit提交事务
rollback回滚事务，放弃缓存中变更的数据

对增删改起作用，python默认自动开启了事务

3、 索引
作用：解决当数据库数据量很大时，查找数据会变慢的问题，即提高查询数据的效率
什么是索引：是一种特殊的文件（InnoDB数据表上的索引是表空间的一个组成部分），他们包含着对数据表里所有记录的引用指针，通俗说，数据库索引好比是一本书前面的目录，能加快数据库查询速度

补充：
开启时间检测（数据库语句执行时间检测）
        set profiling = 1
查看语句执行时间
        show profiles

索引相关操作：
（1）查看索引

show index from 表名;
注：外键、主键自动创建了索引

（2）创建索引

create index 索引名称 on 表名(字段名称(长度))；
注：如果字段为字符串，需要指定长度，建议长度与定义字段长度一致
字段类型不是字符串，可以不填写长度部分
创建索引以后，以后查询数据就可以利用索引名查询数据
select  *  from 索引名 where  索引关联字段="xx";

（3）删除索引

drop index 索引名称 on 表名；

注意：
1、建立太多的索引会影响更新插入速度
2、索引占用磁盘空间
数据很大，才建议给常用字段添加索引       

 4、账户管理

查看所有用户
所有用户及权限信息储存在pysql数据库user表中
查看user结构
desc user;
主要字段说明：
Host-表示允许访问的主机
User-表示用户名
authentication_string-表示密码，为加密后的值

（1）创建用户、授权

grant 权限列表 on 数据库 to '用户名'@'主机号'  identified by 密码
例：创建一个laowang账号，密码为123456，只能通过本地访问，并且只能对fy_goods数据中所有表进行读操作
操作步骤
1、使用root登录
            mysql -u root -p
2、创建账户并授予权限
            grant  select on  fy_goods.*  to  'laowang'@'loacalhost'  identify  by  '123456'
说明
可以操作的表： fy_goods.*
访问主机通常使用%,表示此账户可以使用任何ip的主机登录访问此数据库
访问主机设置成localhost或指定ip，表示只允许本机或特定主机访问
all privileges: 赋予所有权限
grant  all privileges  on  fy_goods.*  to  'laoli'@'loacalhost'  identify  by  '123456'
3、退出root登录
            quit;

（2）查看用户有哪些权限

show  grants  for  '用户名'@'主机号' 

（3）修改权限

grant  新权限名称 on 数据库  to  账户@主机  with  grant  option;
注：修改完成后需要刷新权限，flush privileges;

（4）修改密码

使用password()函数进行加密
update  user  set  authentication_string=password('新密码')   where  user='用户名'；
注：修改完成后需要刷新权限，flush privileges;

（5）远程登录（危险慎用）

指定登录某一个远程数据库：mysql -u用户名 -p密码 -h远程登录ip
详细操作：略

（6）删除账户

方式一（推荐）：
drop user  '账户'@'主机'
方式二：
delete from user where user='用户名'；
注：修改完成后需要刷新权限，flush privileges;

5、主从
作用：

• 使读写分明
• 负载均衡
• 数据备份

图示：

 配置主从同步的基本步骤：

1、主服务器上，必须开启二进制日志机制和配置一个独立的ID
2、在每一个从服务器上配置一个唯一的ID,创建一个专门复制主服务器数据的账号
3、在开始复制进程前，在主服务器上记录二进制文件的位置信息
4、如果在开始复制数据之前，数据库已有数据，就必须先创建一个数据快照（可以使用mysqldump导出数据库或直接复制数据文件）
5、配置服务器要连接的主服务器的IP地址和登录授权，二进制日志文件名和位置