Python全栈开发之MySQL

No.1 数据库概念

什么是数据库？

数据库就是一种特殊的文件，内部存储着需要的数据

RDBMS

所谓关系数据库，是建立在关系模型基础的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据

SQL

SQL是结构化语言，是一种用来操作关系数据库的数据库语言，

SQL语句	说明	举例
DQL	数据查询语言	select
DML	数据操作语言	insert、update、delete
TPL	事物处理语言	begin、transaction、commit、rollback
DCL	数据控制语言	grant、revoke
DDL	数据定义语言	create、drop
CCL	指针控制语言	declare、cursor

MySQL

MySQL是一个关系数据库管理系统

特点：

• 使用C和C++编写，并使用了多种编译器进行测试，保证源代码的可移植性
• 支持多种操作系统，如 Linux 、Windows、AIX、FreeBSD、HP-UX、MacOS、NovellNetware、OpenBSD、OS/2 Wrap、Solaris等
• 为多种编程语言提供了API，如C、C++、 Python 、 Java 、 Perl 、 PHP 、Eiffel、 Ruby 等
• 支持多线程，充分利用CPU资源
• 优化的 SQL 查询算法，有效地提高查询速度
• 提供多语言支持，常见的编码如GB2312、BIG5、UTF8
• 提供TCP/IP、ODBC和JDBC等多种数据库连接途径
• 提供用于管理、检查、优化数据库操作的管理工具
• 大型的数据库。可以处理拥有上千万条记录的大型数据库
• 支持多种存储引擎
• MySQL 软件采用了双授权政策，它分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库
• MySQL使用标准的SQL数据语言形式
• Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的Mysql系统
• 在线DDL更改功能
• 复制全局事务标识
• 复制无崩溃从机
• 复制多线程从机

No.2 数据完整性

一个数据库就是一个完整的业务单元，可以包含多张表，在表中为了更加精准的存储数据，保证数据的正确性，可以在创建表的时候，为表增加一些强制性的验证，包括数据类型、约束

数据类型

• 整数：int，bit

• 小数：decimal

• 字符串：varchar,char

• 日期时间: date, time, datetime

• 枚举类型(enum)

• decimal表示浮点数，如decimal(5,2)表示共存5位数，小数占2位

• char表示固定长度的字符串，如char(3)，如果填充ab时会补一个空格

• varchar表示可变长度的字符串，如varchar(3)，填充ab时就会存储ab

• 字符串text表示存储大文本，当字符大于4000时推荐使用

• 对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径

数值类型	类型	字节大小	有符号范围(Signed)	无符号范围(Unsigned)
TINYINT	1	-128 ~ 127	0 ~ 255
SMALLINT	2	-32768 ~ 32767	0 ~ 65535
MEDIUMINT	3	-8388608 ~ 8388607	0 ~ 16777215
INT/INTEGER	4	-2147483648 ~2147483647	0 ~ 4294967295
BIGINT	8	-9223372036854775808 ~ 9223372036854775807	0 ~ 18446744073709551615

类型	字节大小	示例
CHAR	0-255	类型:char(3) 输入 'ab', 实际存储为'ab ', 输入'abcd' 实际存储为 'abc'
VARCHAR	0-255	类型:varchar(3) 输 'ab',实际存储为'ab', 输入'abcd',实际存储为'abc'
TEXT	0-65535	大文本

日期时间类型

类型	字节大小	示例
DATE	4	'2020-01-01'
TIME	3	'12:29:59'
DATETIME	8	'2020-01-01 12:29:59'
YEAR	1	'2017'
TIMESTAMP	4	'1970-01-01 00:00:01' UTC ~ '2038-01-01 00:00:01' UTC

约束

• 主键primary key：物理上存储的顺序
• 非空not null：此字段不允许填写空值
• 惟一unique：此字段的值不允许重复
• 默认default：当不填写此值时会使用默认值，如果填写时以填写为准
• 外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常

No.3 命令行脚本

数据库操作

show databases; 查看数据库
use 数据库名; 使用数据库
select database(); 查看当前使用的数据库
create database 数据库名 charset=utf8； 创建数据库
drop database 数据库名; 删除数据库

数据表操作

show tables; 查看当前数据库中的所有表
desc 表名; 查看表结构
创建表
create table students(
    id int unsingned primary key auto_increment not null,
    name varchar(20) default '',
    age int unsingned default 0,
    height descimal(3,2) default 1.8,
    gender enum('男','女') default '男'
)
alter table 表名 add 列名 类型; 添加字段
alter table 表名 change 类型及约束; 修改字段
alter table 表名 change 原名 新名 类型及约束; 修改字段(重命名字段)
alter table 表名 drop 列名; 删除字段
drop table 表名; 删除表
show create table 表名; 查看表的创建语句

数据增删改查

增

insert into 表名 values(); 全部列插入
insert into 表名(列1,...) values(); 部分列插入
insert into 表名(列1,...) values(),...; 插入多条数据

删

delete from 表名 where 条件; 删除删选出的数据

改

update 表名 set 列1=值1,列2=值2... where 条件; 修改数据

查

select 列1,列2,... from 表名; 查询数据

数据备份与恢复

备份

mysqldump –uroot –p 数据库名 > 数据库名.sql;

恢复

创建新的数据库
ysql -uroot –p 新数据库名 < 数据库名.sql

No.4 数据库设计

关系型数据库建议在E-R模型的基础上，我们需要根据产品经理的设计策划，抽取出来模型与关系，制定出表结构，这是项目开始的第一步，在开发中有很多设计数据库的软件，常用的如power designer，db desinger等，这些软件可以直观的看到实体及实体间的关系，设计数据库，可能是由专门的数据库设计人员完成，也可能是由开发组成员完成，一般是项目经理带领组员来完成

三范式

经过研究和对使用中问题的总结，对于设计数据库提出了一些规范，这些规范被称为范式

第一范式(1NF) 强调的是列的原子性，即列不能够再分成其他几列

第二范式(2NF) 首先是 1NF，另外包含两部分内容，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分

第三范式(3NF) 首先是 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况

E-R模型

• E表示entry，实体，设计实体就像定义一个类一样，指定从哪些方面描述对象，一个实体转换为数据库中的一个表

• R表示relationship，关系，关系描述两个实体之间的对应规则，关系的类型包括包括一对一、一对多、多对多

• 关系也是一种数据，需要通过一个字段存储在表中

• 实体A对实体B为1对1，则在表A或表B中创建一个字段，存储另一个表的主键值

• 实体A对实体B为1对多：在表B中创建一个字段，存储表A的主键值

• 实体A对实体B为多对多：新建一张表C，这个表只有两个字段，一个用于存储A的主键值，一个用于存储B的主键值

逻辑删除

• 对于重要数据，并不希望物理删除，一旦删除，数据无法找回
• 删除方案：设置isDelete的列，类型为bit，表示逻辑删除，默认值为0
• 对于非重要数据，可以进行物理删除
• 数据的重要性，要根据实际开发决定

No.5 MySQL查询

准备测试数据

创建数据库
create database python charset=utf8;
使用数据库
use python;
创建students表
create table students(
    id int unsigned primary key auto_increment not null,
    name varchar(20) default '',
    age tinyint unsigned default 0,
    height decimal(5,2),
    gender enum('男','女','中性','保密') default '保密',
    cls_id int unsigned default 0,
    is_delete bit default 0
);
创建classes表
create table classes (
    id int unsigned auto_increment primary key not null,
    name varchar(30) not null
);
向students表中插入数据
insert into students values
(0,'小明',18,180.00,2,1,0),
(0,'小月月',18,180.00,2,2,1),
(0,'彭于晏',29,185.00,1,1,0),
(0,'刘德华',59,175.00,1,2,1),
(0,'黄蓉',38,160.00,2,1,0),
(0,'凤姐',28,150.00,4,2,1),
(0,'王祖贤',18,172.00,2,1,1),
(0,'周杰伦',36,NULL,1,1,0),
(0,'程坤',27,181.00,1,2,0),
(0,'刘亦菲',25,166.00,2,2,0),
(0,'金星',33,162.00,3,3,1),
(0,'静香',12,180.00,2,4,0),
(0,'郭靖',12,170.00,1,4,0),
(0,'周杰',34,176.00,2,5,0);
向classes表中插入数据
insert into classes values (0, "python_01期"), (0, "python_02期");

查询所有字段

select * from students;

查询指定字段

select name from students;

使用as给字段起别名

select id as 序号,name as 姓名,gender as 性别 from students;

使用as给表起别名

select s.id,s.name from students as s;

消除重复行

select distinct gender from students;

条件

使用where子句对表中的数据筛选，结果为True的行会出现在结果集中

where后面支持比较运算符、逻辑运算符、模糊查询、范围查询、空判断

比较运算符

• 等于: =

• 大于: >

• 大于等于: >=

• 小于: <

• 小于等于: <=

• 不等于: != 或 <>

select * from students where id > 3; 查询id大于3的学生
select * from students where id <= 4; 查询小于等于4的学生
select * from students where name != '黄蓉'; 查询名字不是黄蓉的
select * from students where is_delete=0; 查询没有被删除的

逻辑运算符

• and
• or
• not

select * from students where id > 3 and gender=0; 查询id大于3的女同学
select * from students where id < 4 or is_delete=0; 查询id<4没被删除的同学

模糊查询

• like
• %表示任意多个任意字符
• _表示一个任意字符

select * from students where name like '黄%'; 查询姓黄的同学
select * from students where name like '李_'; 查询姓黄并且名字是两个字的同学
select * from students where name like '黄%' or name like '%泽'; 查询姓黄的或者最后一个字是泽的同学

范围查询

• in表示在一个非连续的范围内
• between ... and ...表示在一个连续的范围内

select * from students where id in(1,4,5); 查询id是1或者4或者5的同学
select * from students where id between 3 and 7; 查询id为3到8的同学

空判断

• 注意：null与''是不同的
• 判空is null

select * from students where height is null; 查询没有填写身高的同学
select * from students where height is not null; 查询填写了身高的同学

优先级

• 优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符
• and比or先运算，如果同时出现并希望先算or，需要结合()使用

排序

语法

select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]

说明

• 将数据先按照列1进行排序，如果列1相同，按照列2，以此类推
• asc从小到大排列，即升序(默认)
• desc从大到小排序，即降序

select * from students where gender=1 and is_delete=0 order by id asc; 查询未删除的男生升序排列
select * from students order by age desc,height desc；查询所有同学，按年龄排序，年龄相同的按身高排序

聚合

总数

select count(*) from students; 查询表中总共有多少条数据

最大值

select max(age) from students where gender=1; 查询所有男生中最老的

最小值

select min(age) from students where gender=2; 查询所有女生中年龄最小的

求和

select sum(age) from students; 查询所有同学的总年龄

平均值

select avg(height) from students where is_delete=0 and gender=2; 查询未删除的女生的平均身高

分组

group by 将查询结果按照一个或多个字段进行分组，字段值相同的为一组

SELECT gender FROM students GROUP BY gender;

group by + group_concat() group_concat(字段名)可以作为一个输出字段来使用，表示分组以后，根据分组查看某字段的集合

select gender,group_concat(name) from students group by gender;
+--------+-----------------------------------------------------------+
| gender | group_concat(name)                                        |
+--------+-----------------------------------------------------------+
| 男     | 彭于晏,刘德华,周杰伦,程坤,郭靖                               |
| 女     | 小明,小月月,黄蓉,王祖贤,刘亦菲,静香,周杰                      |
| 中性   | 金星                                                       |
| 保密   | 凤姐                                                       |
+--------+-----------------------------------------------------------+
select gender,group_concat(id) from students group by gender;
+--------+------------------+
| gender | group_concat(id) |
+--------+------------------+
| 男     | 3,4,8,9,14       |
| 女     | 1,2,5,7,10,12,13 |
| 中性   | 11               |
| 保密   | 6                |
+--------+------------------+

group by + 聚合函数 可以通过聚合函数来对这个值的集合做一些操聚合操作

select gender,avg(height) from students group by gender;
+--------+-------------+
| gender | avg(height) |
+--------+-------------+
| 男     |  177.750000 |
| 女     |  173.428571 |
| 中性   |  162.000000 |
| 保密   |  150.000000 |
+--------+-------------+

group by + having having表达式，用来分组以后设定条件筛选数据，功能和where一样，但是having只能用于group by

select gender,count(*) from students group by gender having count(*)>2;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男     |        5 |
| 女     |        7 |
+--------+----------+

group by + with rollup 在最后增加一行，计算该列的和

select gender,count(*) from students group by gender with rollup;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男     |        5 |
| 女     |        7 |
| 中性   |        1 |
| 保密   |        1 |
| NULL   |       14 |
+--------+----------+

分页

当数据量很大的时候，就不可能在一页中查看所有数据了，需要对它进行分页操作

语法

select * from 表名 limit start,count

说明

从start开始，获取count条数据

select * from where gender=1 limit 0,3; 查询前三条男生记录

连接

mysql支持三种连接查询

• 内连接 查询的结果为两个表匹配到的数据

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

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

语法

select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列

select * from students inner join classes on students.cls_id = classes.id; 使用内关联查询班级表和学生表
+----+--------+------+--------+--------+--------+-----------+----+-------------+
| id | name   | age  | height | gender | cls_id | is_delete | id | name        |
+----+--------+------+--------+--------+--------+-----------+----+-------------+
|  1 | 小明   |   18 | 180.00 | 女     |      1 |           |  1 | python_01期 |
|  2 | 小月月 |   18 | 180.00 | 女     |      2 |          |  2 | python_02期 |
|  3 | 彭于晏 |   29 | 185.00 | 男     |      1 |           |  1 | python_01期 |
|  4 | 刘德华 |   59 | 175.00 | 男     |      2 |          |  2 | python_02期 |
|  5 | 黄蓉   |   38 | 160.00 | 女     |      1 |           |  1 | python_01期 |
|  6 | 凤姐   |   28 | 150.00 | 保密   |      2 |          |  2 | python_02期 |
|  7 | 王祖贤 |   18 | 172.00 | 女     |      1 |          |  1 | python_01期 |
|  8 | 周杰伦 |   36 |   NULL | 男     |      1 |           |  1 | python_01期 |
|  9 | 程坤   |   27 | 181.00 | 男     |      2 |           |  2 | python_02期 |
| 10 | 刘亦菲 |   25 | 166.00 | 女     |      2 |           |  2 | python_02期 |
+----+--------+------+--------+--------+--------+-----------+----+-------------+
select * from students as s left join classes as c on s.cls_id = c.id; 使用左关联查询班级表和学生表
+----+--------+------+--------+--------+--------+-----------+------+-------------+
| id | name   | age  | height | gender | cls_id | is_delete | id   | name        |
+----+--------+------+--------+--------+--------+-----------+------+-------------+
|  1 | 小明   |   18 | 180.00 | 女     |      1 |           |    1 | python_01期 |
|  2 | 小月月 |   18 | 180.00 | 女     |      2 |          |    2 | python_02期 |
|  3 | 彭于晏 |   29 | 185.00 | 男     |      1 |           |    1 | python_01期 |
|  4 | 刘德华 |   59 | 175.00 | 男     |      2 |          |    2 | python_02期 |
|  5 | 黄蓉   |   38 | 160.00 | 女     |      1 |           |    1 | python_01期 |
|  6 | 凤姐   |   28 | 150.00 | 保密   |      2 |          |    2 | python_02期 |
|  7 | 王祖贤 |   18 | 172.00 | 女     |      1 |          |    1 | python_01期 |
|  8 | 周杰伦 |   36 |   NULL | 男     |      1 |           |    1 | python_01期 |
|  9 | 程坤   |   27 | 181.00 | 男     |      2 |           |    2 | python_02期 |
| 10 | 刘亦菲 |   25 | 166.00 | 女     |      2 |           |    2 | python_02期 |
| 11 | 金星   |   33 | 162.00 | 中性   |      3 |          | NULL | NULL        |
| 12 | 静香   |   12 | 180.00 | 女     |      4 |           | NULL | NULL        |
| 13 | 郭靖   |   12 | 170.00 | 男     |      4 |           | NULL | NULL        |
| 14 | 周杰   |   34 | 176.00 | 女     |      5 |           | NULL | NULL        |
+----+--------+------+--------+--------+--------+-----------+------+-------------+
select * from students as s right join classes as c on s.cls_id = c.id; 使用右关联查询班级表和学生表
+------+--------+------+--------+--------+--------+-----------+----+-------------+
| id   | name   | age  | height | gender | cls_id | is_delete | id | name        |
+------+--------+------+--------+--------+--------+-----------+----+-------------+
|    1 | 小明   |   18 | 180.00 | 女     |      1 |           |  1 | python_01期 |
|    2 | 小月月 |   18 | 180.00 | 女     |      2 |          |  2 | python_02期 |
|    3 | 彭于晏 |   29 | 185.00 | 男     |      1 |           |  1 | python_01期 |
|    4 | 刘德华 |   59 | 175.00 | 男     |      2 |          |  2 | python_02期 |
|    5 | 黄蓉   |   38 | 160.00 | 女     |      1 |           |  1 | python_01期 |
|    6 | 凤姐   |   28 | 150.00 | 保密   |      2 |          |  2 | python_02期 |
|    7 | 王祖贤 |   18 | 172.00 | 女     |      1 |          |  1 | python_01期 |
|    8 | 周杰伦 |   36 |   NULL | 男     |      1 |           |  1 | python_01期 |
|    9 | 程坤   |   27 | 181.00 | 男     |      2 |           |  2 | python_02期 |
|   10 | 刘亦菲 |   25 | 166.00 | 女     |      2 |           |  2 | python_02期 |
+------+--------+------+--------+--------+--------+-----------+----+-------------+
select s.name,c.name from students as s inner join classes as c on s.cls_id = c.id; 查询学生姓名及班级名称
+--------+-------------+
| name   | name        |
+--------+-------------+
| 小明   | python_01期 |
| 小月月 | python_02期 |
| 彭于晏 | python_01期 |
| 刘德华 | python_02期 |
| 黄蓉   | python_01期 |
| 凤姐   | python_02期 |
| 王祖贤 | python_01期 |
| 周杰伦 | python_01期 |
| 程坤   | python_02期 |
| 刘亦菲 | python_02期 |
+--------+-------------+

子查询

子查询 在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句

主查询 主要查询的对象,第一条 select 语句

主查询和子查询的关系

• 子查询是嵌入到主查询中
• 子查询是辅助主查询的,要么充当条件,要么充当数据源
• 子查询是可以独立存在的语句,是一条完整的 select 语句

子查询分类

• 标量子查询: 子查询返回的结果是一个数据(一行一列)
• 列子查询: 返回的结果是一列(一列多行)
• 行子查询: 返回的结果是一行(一行多列)

查询大于平均年龄的学生

select * from students where age > (select avg(age) from students);

查询还有学生在班的所有班级名字

select name from classes where id in (select cls_id from students);

需求: 查找班级年龄最大,身高最高的学生

select * from students where (height,age) = (select max(height),max(age) from students);

查询的执行顺序

from表名>where>group by>slect distinct>having>order by>limit

No.6 Python与MySQL交互

准备数据

创建京东数据库
create database jing_dong charset=utf8;
使用京东数据库
use jing_dong;
创建一个商品goods数据表
create table goods(
    id int unsigned primary key auto_increment not null,
    name varchar(150) not null,
    cate_name varchar(40) not null,
    brand_name varchar(40) not null,
    price decimal(10,3) not null default 0,
    is_show bit not null default 1,
    is_saleoff bit not null default 0
);
向goods表中插入数据
insert into goods values(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',default,default); 
insert into goods values(0,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999',default,default);
insert into goods values(0,'g150th 15.6英寸游戏本','游戏本','雷神','8499',default,default); 
insert into goods values(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799',default,default); 
insert into goods values(0,'x240 超极本','超级本','联想','4880',default,default); 
insert into goods values(0,'u330p 13.3英寸超极本','超级本','联想','4299',default,default); 
insert into goods values(0,'svp13226scb 触控超极本','超级本','索尼','7999',default,default); 
insert into goods values(0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','1998',default,default);
insert into goods values(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388',default,default); 
insert into goods values(0,'ipad mini 配备 retina 显示屏','平板电脑','苹果','2788',default,default); 
insert into goods values(0,'ideacentre c340 20英寸一体电脑 ','台式机','联想','3499',default,default); 
insert into goods values(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',default,default); 
insert into goods values(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',default,default); 
insert into goods values(0,'at7-7414lp 台式电脑 linux ）','台式机','宏碁','3699',default,default); 
insert into goods values(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288',default,default); 
insert into goods values(0,'poweredge ii服务器','服务器/工作站','戴尔','5388',default,default); 
insert into goods values(0,'mac pro专业级台式电脑','服务器/工作站','苹果','28888',default,default); 
insert into goods values(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',default,default); 
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default); 
insert into goods values(0,'x3250 m4机架式服务器','服务器/工作站','ibm','6888',default,default); 
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);

数据库设计

创建商品分类表

create table goods_cates(
    id int unsigned primary key auto_increment not null,
    name varchar(40) not null
);

创建商品品牌表

create table goods_brands (
    id int unsigned primary key auto_increment not null,
    name varchar(40) not null
);

创建商品表

create table goods(
    id int unsigned primary key auto_increment not null,
    name varchar(40) default '',
    price decimal(5,2),
    cate_id int unsigned,
    brand_id int unsigned,
    is_show bit default 1,
    is_saleoff bit default 0,
    foreign key(cate_id) references goods_cates(id),
    foreign key(brand_id) references goods_brands(id)
);

创建顾客表

create table customer(
    id int unsigned auto_increment primary key not null,
    name varchar(30) not null,
    addr varchar(100),
    tel varchar(11) not null
);

创建订单表

create table orders(
    id int unsigned auto_increment primary key not null,
    order_date_time datetime not null,
    customer_id int unsigned,
    foreign key(customer_id) references customer(id)
);

创建订单状态表

create table order_detail(
    id int unsigned auto_increment primary key not null,
    order_id int unsigned not null,
    goods_id int unsigned not null,
    quantity tinyint unsigned not null,
    foreign key(order_id) references orders(id),
    foreign key(goods_id) references goods(id)
);

PyMySQL操作流程

Connection对象

用于建立与数据库的连接

conn=connect(参数列表)
host：连接的mysql主机，如果本机是'localhost'
port：连接的mysql主机的端口，默认是3306
database：数据库的名称
user：连接的用户名
password：连接的密码
charset：通信采用的编码方式，推荐使用utf8
close() 关闭连接
commit() 提交
cursor() 返回cursor对象，执行sql并返回结果

Cursor对象

返回cursor对象，执行sql并返回结果

rowcount只读属性，表示最近一次execute()执行后受影响的行数
connection获得当前连接对象
close()关闭
execute(operation [, parameters ])执行语句，返回受影响的行数，主要用于执行insert、update、delete语句，也可以执行create、alter、drop等语句
fetchone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元组
fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回

增删改查

增删改

import pymysql

if __name__ == '__main__':
    conn = pymysql.connect(host='',port=3306,database='jing_dong',user='root',password='123456',charset='utf8')
    cursor = conn.cursor()
    # 增加
    count = cursor.execute('insert into goods(name,cate_name,brand_name) values("硬盘","","")')
    print(count)
    count = cursor.execute('insert into goods(name) values("光盘")')
    print(count)
    # 更新
    count = cursor.execute('update goods set name="机械硬盘" where name="硬盘"')
    # 删除
    count = cursor.execute('delete from goods where id=6')
    print(count)
    conn.commit()
    cursor.close()
    conn.close()

查询一条数据

import pymysql

if __name__ == '__main__':
    conn = pymysql.connect(host='',port=3306,database='jing_dong',user='root',password='123456',charset='utf8')
    cursor = conn.cursor()
    count = cursor.execute('select id,name from goods where id<=4')
    print(count)
    for i in range(count):
        ret = cursor.fetchone()
        print(ret)
    conn.commit()
    cursor.close()
    conn.close()

查询多条数据

import pymysql

if __name__ == '__main__':
    conn = pymysql.connect(host='',port=3306,database='jing_dong',user='root',password='123456',charset='utf8')
    cursor = conn.cursor()

    count = cursor.execute('select id,name from goods where id<=4')
    print(count)
    ret = cursor.fetchall()
    print(ret)
    conn.commit()
    cursor.close()
    conn.close()

参数化

sql语句的参数化，可以有效防止sql注入

import pymysql

if __name__ == '__main__':
    conn = pymysql.connect(host='',port=3306,database='jing_dong',user='root',password='123456',charset='utf8')
    cursor = conn.cursor()
    count = cursor.execute('select id,name from goods where id<=%s',4)
    print(count)
    ret = cursor.fetchall()
    print(ret)
    conn.commit()
    cursor.close()
    conn.close()

No.7 视图

视图是什么？

通俗的讲，视图就是一条SELECT语句执行后返回的结果集，所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上，视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变），方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性

定义视图

create view 视图名称 as select语句;

查看视图

show tables;

使用视图

select * from 视图名称;

删除视图

drop view 视图名称;

视图的作用

提高了重用性，就像一个函数

对数据库重构，却不影响程序的运行

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

让数据更加清晰

No.8 事务

什么是事务？

所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位，只有事务内的所有操作全部执行成功才会提交到数据库，只要有一条执行失败，也不会提交，广泛应用于订单系统、银行系统

事务四大特性

• 原子性(Atomicity)
• 一致性(Consistency)
• 隔离性(Isolation)

• 持久性(Durability)

  开启事务

  start transaction;

  提交事务

  commit;

  回滚事务

  rollback;

  No.9 索引

  什么是索引？

  索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针，索引就像一本书的目录，加快查找速度

  索引的使用

  查看索引

  show index from 表名;

  创建索引

  create index 索引名称 on 表名(字段名称(长度))

  删除索引

  drop index 索引名称 on 表名;

  No.10 权限

• 在生产环境下操作数据库时，绝对不可以使用root账户连接，而是创建特定的账户，授予这个账户特定的操作权限，然后连接进行操作，主要的操作就是数据的crud

• MySQL账户体系：根据账户所具有的权限的不同，MySQL的账户可以分为以下几种

  • 服务实例级账号：，启动了一个mysqld，即为一个数据库实例；如果某用户如root,拥有服务实例级分配的权限，那么该账号就可以删除所有的数据库、连同这些库中的表

  • 数据库级别账号：对特定数据库执行增删改查的所有操作

  • 数据表级别账号：对特定表执行增删改查等所有操作

  • 字段级别的权限：对某些表的特定字段进行操作

  • 存储程序级别的账号：对存储程序进行增删改查的操作
• 帐户的操作主要包括创建账户、删除账户、修改密码、授权权限等

No.11 主从

主从同步的定义

主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave），因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器，通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表

使用主从同步的好处：

• 通过增加从服务器来提高数据库的性能，在主服务器上执行写入和更新，在从服务器上向外提供读功能，可以动态地调整从服务器的数量，从而调整整个数据库的性能。
• 提高数据安全，因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据
• 在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能

主从同步的机制

Mysql服务器之间的主从同步是基于二进制日志机制，主服务器使用二进制日志来记录数据库的变动情况，从服务器通过读取和执行该日志文件来保持和主服务器的数据一致

在使用二进制日志时，主服务器的所有操作都会被记录下来，然后从服务器会接收到该日志的一个副本。从服务器可以指定执行该日志中的哪一类事件（譬如只插入数据或者只更新数据），默认会执行日志中的所有语句

每一个从服务器会记录关于二进制日志的信息：文件名和已经处理过的语句，这样意味着不同的从服务器可以分别执行同一个二进制日志的不同部分，并且从服务器可以随时连接或者中断和服务器的连接

主服务器和每一个从服务器都必须配置一个唯一的ID号（在my.cnf文件的[mysqld]模块下有一个server-id配置项），另外，每一个从服务器还需要通过CHANGE MASTER TO语句来配置它要连接的主服务器的ip地址，日志文件名称和该日志里面的位置（这些信息存储在主服务器的数据库里）

配置主从同步的步骤

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

详细配置主从同步的方法

主和从的身份可以自己指定，我们将虚拟机Ubuntu中MySQL作为主服务器，将Windows中的MySQL作为从服务器

1. 备份主服务器原有数据到从服务器

如果在设置主从同步前，主服务器上已有大量数据，可以使用mysqldump进行数据备份并还原到从服务器以实现数据的复制

• 在主服务器Ubuntu上进行备份，执行命令

mysqldump -uroot -p123456 --all-databases --lock-all-tables > ~/master_db.sql
-u ：用户名
-p ：密码
--all-databases ：导出所有数据库
--lock-all-tables ：执行操作时锁住所有表，防止操作时有数据修改
~/master_db.sql :导出的备份数据（sql文件）位置，可自己指定

• 在从服务器Windows上进行数据还原

mysql –uroot –p123456 < master_db.sql

2. 配置主服务器master

• 编辑设置mysqld的配置文件，设置log_bin和server-id

  vim /etc/mysql/mysql.conf.d/mysqld.cnf
  server-id           = 1
  log_bin             = /var/log/mysql/mysql-bin.log

• 重启mysql服务

  service mysql restart

• 登入主服务器Ubuntu中的mysql，创建用于从服务器同步数据使用的帐号

  mysql –uroot –p123456
  GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' identified by 'slave';
  FLUSH PRIVILEGES;

• 获取主服务器的二进制日志信息

  SHOW MASTER STATUS;
  -File: 使用的日志文件名字
  Position: 使用的文件位置

  3. 配置从服务器slave（Windows中的MySQL）
• 找到Windows中MySQL的配置文件
• 编辑my.ini文件，将server-id修改为2，并保存退出
• 重启MySQL服务

进入MySQL，设置连接到master主服务器

change master to master_host='10.211.55.5', master_user='slave', master_password='slave',master_log_file='mysql-bin.000006', master_log_pos=590;

• master_host：主服务器Ubuntu的ip地址
• master_log_file: 前面查询到的主服务器日志文件名
• master_log_pos: 前面查询到的主服务器日志文件位置

开启同步，查看同步状态

start slave; 开启同步
show slave status \G; 查看同步状态

测试主从同步

在主服务器上创建一个数据库，在从服务上查看