08.Mysql中常用指令集合的学习笔记

在Pycharm中操作MySQL学习笔记

一级目录

二级目录

三级目录

1.介绍MySQL:

MySQL 是一款开源的关系型数据库管理系统（RDBMS）。

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

1.数据类型：

• 可以通过查看帮助文档查阅所有支持的数据类型

• 使用数据类型的原则是：够用就行，尽量使用取值范围小的，而不用大的，这样可以更多的节省存储空间

• 常用数据类型如下：

  • 整数：int，bit

  • 小数：decimal

  • 字符串: varchar,char

  • 日期时间: date, time, datetime

  • 枚举类型: enum

• 特别说明的类型如下：

  • decimal表示浮点数，如decimal(5,2)表示共存5位数，小数占2位
  • char表示固定长度的字符串，如char(3)，如果填充'ab'时会补一个空格为'ab '
  • varchar表示可变长度的字符串，如varchar(3)，填充'ab'时就会存储'ab'
  • 字符串text表示存储大文本，当字符大于4000时推荐使用
  • 对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径

• 更全的数据类型可以参考：http://blog.csdn.net/anxpp/article/details/51284106

2.约束：

约束

• 主键primary key：物理上存储的顺序
• 非空not null：此字段不允许填写空值
• 惟一unique：此字段的值不允许重复
• 默认default：当不填写此值时会使用默认值，如果填写时以填写为准
• 外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常
• 说明：虽然外键约束可以保证数据的有效性，但是在进行数据的crud（增加、修改、删除、查询）时，都会降低数据库的性能，所以不推荐使用，那么数据的有效性怎么保证呢？答：可以在逻辑层进行控制

2.数据操作：

!!!注意：接下来的演示样例全是在pycharm专业版里实现的，社区版是没有以下的功能的!!!,如果是社区版用户的话，就到MySQL控制台里去操作就行。

我这里使用pycharm来实现与MySQL的链接：

打开pycharm右侧的数据库；找到加号添加Mysql，接着会跳出以下界面：

这里随便输入一个名称，然后输入自己的用户名与密码，用户名默认是root，若下方提示你要下载驱动程序的话就安装，安装完毕之后测试链接，成功后点击‘应用’并‘确定’即实现了pycharm与MySQL数据库的链接。

1.数据库基本操作

注意：初始的MySQL自带四个库，这四个库不要去改动！！！

1.查询数据库版本：

show version;

2.查看所有数据库

show databases;

3.创建自己的数据库：

create database 数据库名 charset=utf8;

-- 例：
create database daemon charset=utf8;

创建 daemon 数据库成功！

charset是字符集的缩写，在数据库中主要用来定义如何存储和比较字符！

4.使用数据库:

use 数据库名称;

这里我选择students这个数据库：

打了一个绿色的勾说明操作已实现。

5.查看当前使用的数据库：

select database();

6.数据库的删除（慎用）

drop database 数据库名;

-- 例：
drop database python;

2.表格的基本操作：

1.查看当前数据库中所有表：

use daemon;
show tables ;

结果：

由于我没有在数据库里面添加任何表单，所以这里啥table都没显示。接下来我们要创建表单。

2.创建表单：

CREATE TABLE table_name(
    column1 datatype contrai,
    column2 datatype,
    column3 datatype,
    .....
    columnN datatype,
    PRIMARY KEY(one or more columns)
);

例子：

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

其中，auto_increment表示自动增长。这个时候查询table就会有table出现了。

3.查询表结构：

有时候我们需要知道表里面每个参数的属性，那么我们就要需要用到查询表结构的方法。

desc 表名;

4.修改表：

1.添加字段：

alter table 表名 add 列名 类型;
例：
alter table classes add birthday datetime;

结果如下图：

这表示birthday字段已经添加到classes表单中了。

2.修改字段：

1.重命名字段：

alter table 表名 change 原名 新名 类型及约束;
例：
alter table classes change birthday birth datetime not null;

2.不重命名字段：

alter table 表名 modify 列名 类型及约束;
例：
alter table vlasses modify birth date not null;

date和datetime的区别：

1. 存储范围:
   • DATE 类型仅存储日期，范围从 ‘1000-01-01’ 到 ‘9999-12-31’。
   • DATETIME 类型同时存储日期和时间，范围从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’。
2. 存储格式:
   • DATE 类型的格式为 ‘YYYY-MM-DD’。
   • DATETIME 类型的格式为 ‘YYYY-MM-DD HH:MM:SS’。
3. 存储大小：
   • DATE 类型存储需要3字节。
   • DATETIME 类型存储需要8字节。

3.删除字段：

alter table 表名 drop 列名;
例：
alter table classes drop birthday;

4.删除表单：

drop table 表名;
例：
drop table classes;

5.查看表的创建：

 show create table 表名;
 例：
 show create table classes;

3.数据的增删改查:

1.数据的查询：

1.查询所有列：

select * from 表名;
例：
select * from classes;

2.查询指定列：

select 列1,列2,... from 表名;
例：
select id,name from classes;

2.数据的增添:

1.全列插入：

格式: INSERT [INTO] tb_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),...

insert into 表名 values(...)
例：
insert into classes values (0, '郭靖', '2004-01-01');

这里的0表示一个占位符，实际上id是从1开始不断自增的。

2.部分列插入：

insert into 表名(列1,...) values(值1,...)
例：
insert into classes(birth) values('2016-3-2');

3.全列多行插入：值的顺序与给出的列顺序对应：

insert into 表名 values(...),(...)...;
例：
insert into classes values (3,'杨康','2000-01-01'),(4,'杨过','2001-02-28'),(5,'小龙女','2008-09-09')...;

注意插入数据的时候设置的id值不要与前面的数据重复了。

3.数据的修改：

update 表名 set 列1=值1,列2=值2... where 条件
例：
update classes set name='洪七公',birth='2003-08-30' where id=5;

现在把小龙女的字段给修改一下：

4.数据的删除：

DELETE FROM tb_name [where 条件判断]

delete from 表名 where 条件
例：
delete from classes where id=5;

3.数据表数据的增删改查专项训练

创建数据库、数据表

-- 创建数据库
create database python_test_1 charset=utf8;

-- 使用数据库
use python_test_1;

-- 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期");

在Navicat中显示如下：

• 查询所有字段

select * from 表名;
例：
select * from students;

• 查询指定字段

select 列1,列2,... from 表名;
例:
select name from students;

• 使用 as 给字段起别名

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

• 可以通过 as 给表起别名

-- 如果是单表查询 可以省略表明
select id, name, gender from students;

-- 表名.字段名
select students.id,students.name,students.gender from students;

-- 可以通过 as 给表起别名 
select s.id,s.name,s.gender from students as s;

消除重复行

• 在select后面列前使用distinct可以消除重复的行

select distinct 列1,... from 表名;
例：
select distinct gender from students;

条件查询

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

• 语法如下：

select * from 表名 where 条件;
例：
select * from students where id=1;

• where后面支持多种运算符，进行条件的处理
  • 比较运算符
  • 逻辑运算符
  • 模糊查询
  • 范围查询
  • 空判断

比较运算符

• 等于: =
• 大于: >
• 大于等于: >=
• 小于: <
• 小于等于: <=
• 不等于: != 或 <>

例1：查询编号大于3的学生

select * from students where id > 3;

例2：查询编号不大于4的学生

select * from students where id <= 4;

例3：查询姓名不是“黄蓉”的学生

select * from students where name != '黄蓉';

例4：查询没被删除的学生

select * from students where is_delete=0;

逻辑运算符

• and
• or
• not

例5：查询编号大于3的女同学

select * from students where id > 3 and gender=0;

例6：查询编号小于4或没被删除的学生

select * from students where id < 4 or is_delete=0;

模糊查询

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

例7：查询姓黄的学生

select * from students where name like '黄%';

%表示一个占位符，如果table中有人叫“黄xx”或者叫“黄x”又或者是叫“黄”，都会被视为满足 name like '黄%'。

例8：查询姓黄并且“名”是一个字的学生

select * from students where name like '黄_';

_表示这个人的名字只能由“黄x”组成，“黄蓉”可以，“黄晓明”则不行。

例9：查询姓黄或叫靖的学生

select * from students where name like '黄%' or name like '%靖';

同理

范围查询

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

例10：查询编号是1或3或8的学生

select * from students where id in(1,3,8);

• between … and …表示在一个连续的范围内

例11：查询编号为3至8的学生

select * from students where id between 3 and 8;

包括id是3和8

例12：查询编号是3至8的男生

select * from students where (id between 3 and 8) and gender=1;

空判断

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

例13：查询没有填写身高的学生

select * from students where height is null;

• 判非空is not null

例14：查询填写了身高的学生

select * from students where height is not null;

例15：查询填写了身高的男生

select * from students where height is not null and gender=1;

优先级

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

排序

为了方便查看数据，可以对数据进行排序

语法

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

说明

• 将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推
• 默认按照列值从小到大排列（asc）
• asc从小到大排列，即升序
• desc从大到小排序，即降序

降序

例1：查询未删除男生信息，按学号降序

select * from students where gender=1 and is_delete=0 order by id desc;

升序

例2：查询未删除学生信息，按名称升序

select * from students where is_delete=0 order by name;

组合排序

例3：显示所有的学生信息，先按照年龄从大到小排序，当年龄相同时，按照身高从高到矮排序

select * from students  order by age desc,height desc;

聚合函数

为了快速得到统计数据，经常会用到如下5个聚合函数

总数

• count(*)表示计算总行数，括号中写星与列名，结果是相同的

例1：查询学生总数

select count(*) from students;

最大值

• max(列)表示求此列的最大值

例2：查询女生的编号最大值

select max(id) from students where gender='女';

接下来的示例结果都是可以通过字面意思知道的，不再赘述。

最小值

• min(列)表示求此列的最小值

例3：查询未删除的学生最小编号

select min(id) from students where is_delete=0;

求和

• sum(列)表示求此列的和

例4：查询男生的总年龄

select sum(age) from students where gender=1;

-- 平均年龄
select sum(age)/count(*) from students where gender=1;

平均值

• avg(列)表示求此列的平均值

例5：查询未删除女生的编号平均值

select avg(id) from students where is_delete=0 and gender=2;

分组

group by

1. group by的含义:将查询结果按照1个或多个字段进行分组，字段值相同的为一组
2. group by可用于单个字段分组，也可用于多个字段分组

select * from students;
+----+-----------+------+--------+--------+--------+-----------+
| id | name      | age  | height | gender | cls_id | is_delete |
+----+-----------+------+--------+--------+--------+-----------+
|  1 | 小明      |   18 | 180.00 | 女     |      1 |           |
|  2 | 小月月    |   18 | 180.00 | 女     |      2 |          |
|  3 | 彭于晏    |   29 | 185.00 | 男     |      1 |           |
|  4 | 刘德华    |   59 | 175.00 | 男     |      2 |          |
|  5 | 黄蓉      |   38 | 160.00 | 女     |      1 |           |
|  6 | 凤姐      |   28 | 150.00 | 保密   |      2 |          |
|  7 | 王祖贤    |   18 | 172.00 | 女     |      1 |          |
|  8 | 周杰伦    |   36 |   NULL | 男     |      1 |           |
|  9 | 程坤      |   27 | 181.00 | 男     |      2 |           |
| 10 | 刘亦菲    |   25 | 166.00 | 女     |      2 |           |
| 11 | 金星      |   33 | 162.00 | 中性   |      3 |          |
| 12 | 静香      |   12 | 180.00 | 女     |      4 |           |
| 13 | 周杰      |   34 | 176.00 | 女     |      5 |           |
| 14 | 郭靖      |   12 | 170.00 | 男     |      4 |           |
+----+-----------+------+--------+--------+--------+-----------+

select gender from students group by gender;
(等价于：select distinct gender from students;)
+--------+
| gender |
+--------+
| 男     |
| 女     |
| 中性   |
| 保密   |
+--------+

根据gender字段来分组，gender字段的全部值有4个’男’,‘女’,‘中性’,‘保密’，所以分为了4组。当group by单独使用时，只显示出每组的第一条记录, 所以group by单独使用时的实际意义不大

group by + group_concat()

1. group_concat(字段名)可以作为一个输出字段来使用，
2. 表示分组之后，根据分组结果，使用group_concat()来放置每一组的某字段的值的集合

select gender from students group by gender;
+--------+
| gender |
+--------+
| 男     |
| 女     |
| 中性   |
| 保密   |
+--------+

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 + 集合函数

通过group_concat()的启发，我们既然可以统计出每个分组的某字段的值的集合，那么我们也可以通过集合函数来对这个值的集合做一些操作

select gender,group_concat(age) from students group by gender;
+--------+----------------------+
| gender | group_concat(age)    |
+--------+----------------------+
| 男     | 29,59,36,27,12       |
| 女     | 18,18,38,18,25,12,34 |
| 中性   | 33                   |
| 保密   | 28                   |
+--------+----------------------+


-- 分别统计性别为男/女的人年龄平均值
select gender,avg(age) from students group by gender;
+--------+----------+
| gender | avg(age) |
+--------+----------+
| 男     |  32.6000 |
| 女     |  23.2857 |
| 中性   |  33.0000 |
| 保密   |  28.0000 |
+--------+----------+

-- 分别统计性别为男/女的人的个数
select gender,count(*) from students group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男     |        5 |
| 女     |        7 |
| 中性   |        1 |
| 保密   |        1 |
+--------+----------+

group by + having

1. having条件表达式：用来分组查询后指定一些条件来输出查询结果
2. having作用和where一样，但having只能用于group by

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

group by + with rollup

with rollup的作用是：在最后新增一行，来记录当前列里所有记录的总和

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


select gender,group_concat(age) from students group by gender with rollup;
+--------+-------------------------------------------+
| gender | group_concat(age)                         |
+--------+-------------------------------------------+
| 男     | 29,59,36,27,12                            |
| 女     | 18,18,38,18,25,12,34                      |
| 中性   | 33                                        |
| 保密   | 28                                        |
| NULL   | 29,59,36,27,12,18,18,38,18,25,12,34,33,28 |
+--------+-------------------------------------------+

分页

获取部分行

当数据量过大时，在一页中查看数据是一件非常麻烦的事情

语法

select * from 表名 limit start,count

说明

• 从start开始，获取count条数据

例：查询前3行男生信息

select * from students where gender=1 limit 0,3;

示例代码：

-- 限制查询出来的数据个数
	select * from students where gender=1 limit 2;

	-- 查询前5个数据
	select * from students limit 0, 5;

	-- 查询id6-10（包含）的顺序
	select * from students limit 5, 5;


	-- 每页显示2个，第1个页面
	select * from students limit 0,2;

	-- 每页显示2个，第2个页面
	select * from students limit 2,2;

	-- 每页显示2个，第3个页面
	select * from students limit 4,2;

	-- 每页显示2个，第4个页面
	select * from students limit 6,2; -- -----> limit (第N页-1)*每页的个数, 每页的个数;

	-- 每页显示2个，显示第6页的信息, 按照年龄从小到大排序
	-- 失败select * from students limit 2*(6-1),2;
	-- 失败select * from students limit 10,2 order by age asc;
	select * from students order by age asc limit 10,2;

	select * from students where gender=2 order by height desc limit 0,2;

连接查询

当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回

mysql支持三种类型的连接查询，分别为：

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

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

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

语法

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

例1：使用内连接查询班级表与学生表

select * from students inner join classes on students.cls_id=classes.id;

这里从id11开始，cls_id是3，4，5，这些值在classes.id中不存在，所以不会显示。

例2：使用左连接查询班级表与学生表

• 此处使用了as为表起别名，目的是编写简单

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

左查询的时候students是基准表。

基准表: 在查询时会将基准表中的数据全部查询出来, 对应的从表信息如果无法匹配则显示为None。

例3：使用右连接查询班级表与学生表

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

这里classes是基准表。

例4：查询学生姓名及班级名称

select s.name,c.name from students as s inner join classes as c on s.cls_id=c.id;

子查询

子查询概念

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

主查询概念

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

主查询和子查询的关系

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

子查询分类

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

标量子查询

1. 查询班级学生平均年龄
2. 查询大于平均年龄的学生

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

列级子查询

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

1. 1. 找出学生表中所有的班级 id
   2. 找出班级表中对应的名字

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);

视图

问题

对于复杂的查询，往往是有多个数据表进行关联查询而得到，如果数据库因为需求等原因发生了改变，为了保证查询出来的数据与之前相同，则需要在多个地方进行修改，维护起来非常麻烦

解决办法：定义视图

视图是什么

通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）；

方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性

定义视图

建议以v_开头

create view 视图名称 as select语句;

查看视图

查看表会将所有的视图也列出来

show tables;

使用视图

视图的用途就是查询

select * from v_stu_score;

删除视图

drop view 视图名称;
例：
drop view v_stu_sco;

视图的实际使用

-- 查询陕西省之下所有的城市
select city.* from tb_areas as city inner join tb_areas as province on city.pid=province.aid where province.atitle='山西省';

-- 创建视图
create view v_city_shanxi as select city.* from tb_areas as city inner join tb_areas as province on city.pid=province.aid where province.atitle='山西省';

-- 使用视图
select * from v_city_shanxi;

视图的作用

1. 提高了重用性，就像一个函数
2. 对数据库重构，却不影响程序的运行
3. 提高了安全性能，可以对不同的用户
4. 让数据更加清晰

事务

为什么要有事务

事务广泛的运用于订单系统、银行系统等多种场景

例如：

A用户和B用户是银行的储户，现在A要给B转账500元，那么需要做以下几件事：

1. 检查A的账户余额>500元；
2. A 账户中扣除500元;
3. B 账户中增加500元;

正常的流程走下来，A账户扣了500，B账户加了500，皆大欢喜。

那如果A账户扣了钱之后，系统出故障了呢？A白白损失了500，而B也没有收到本该属于他的500。

以上的案例中，隐藏着一个前提条件：A扣钱和B加钱，要么同时成功，要么同时失败。事务的需求就在于此

所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

例如，银行转帐工作：从一个帐号扣款并使另一个帐号增款，这两个操作要么都执行，要么都不执行。所以，应该把他们看成一个事务。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性

事务四大特性(简称ACID)

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

以下内容出自《高性能MySQL》第三版，了解事务的ACID及四种隔离级有助于我们更好的理解事务运作。

下面举一个银行应用是解释事务必要性的一个经典例子。假如一个银行的数据库有两张表：支票表（checking）和储蓄表（savings）。现在要从用户Jane的支票账户转移200美元到她的储蓄账户，那么至少需要三个步骤：

1. 检查支票账户的余额高于或者等于200美元。
2. 从支票账户余额中减去200美元。
3. 在储蓄帐户余额中增加200美元。

上述三个步骤的操作必须打包在一个事务中，任何一个步骤失败，则必须回滚所有的步骤。

可以用START TRANSACTION语句开始一个事务，然后要么使用COMMIT提交将修改的数据持久保存，要么使用ROLLBACK撤销所有的修改。事务SQL的样本如下：

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

一个很好的事务处理系统，必须具备这些标准特性：

• 原子性（atomicity）

一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性

• 一致性（consistency）

数据库总是从一个一致性的状态转换到另一个一致性的状态。（在前面的例子中，一致性确保了，即使在执行第三、四条语句之间时系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。）

• 隔离性（isolation）

通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。（在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外的一个账户汇总程序开始运行，则其看到支票帐户的余额并没有被减去200美元。）

• 持久性（durability）

一旦事务提交，则其所做的修改会永久保存到数据库。（此时即使系统崩溃，修改的数据也不会丢失。）

事务命令

表的引擎类型必须是innodb类型才可以使用事务，这是mysql表的默认引擎

查看表的创建语句，可以看到engine=innodb

-- 选择数据库
use python_test_1;
-- 查看students表
show create table students;

开启事务，命令如下：

• 开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中

begin;
或者
start transaction;

提交事务，命令如下

• 将缓存中的数据变更维护到物理表中

commit;

回滚事务，命令如下：

• 放弃缓存中变更的数据

rollback;

注意

1. 修改数据的命令会自动的触发事务，包括insert、update、delete
2. 而在SQL语句中有手动开启事务的原因是：可以进行多次数据的修改，如果成功一起成功，否则一起会滚到之前的数据

事务提交

• 为了演示效果，需要打开两个终端窗口，使用同一个数据库，操作同一张表

连接

• 终端1：查询班级信息

select * from classes;

增加数据

• 终端2：开启事务，插入数据

begin;
insert into classes(name) values('python_03期');

• 终端2：查询数据，此时有新增的数据

select * from classes;

查询

• 终端1：查询数据，发现并没有新增的数据

select * from classes;

提交

• 终端2：完成提交

commit;

查询

• 终端1：查询，发现有新增的数据

select * from classes;

事务回滚

• 为了演示效果，需要打开两个终端窗口，使用同一个数据库，操作同一张表

连接

• 终端1

select * from classes;

增加数据

• 终端2：开启事务，插入数据

begin;
insert into classes(name) values('python_04期');

• 终端2：查询数据，此时有新增的数据

select * from classes;

查询

• 终端1：查询数据，发现并没有新增的数据

select * from classes;

回滚

• 终端2：完成回滚

rollback;

查询

• 终端1：查询数据，发现没有新增的数据

select * from classes;

4.Pymysql

前面我们介绍了数据库的基本概念，本节具体介绍数据库的连接及增、删、改、查操作。

下面的示例数据库为TEST，表名为EMPLOYEE，EMPLOYEE表字段为FIRST_NAME、LAST_NAME、AGE、SEX、INCOME和CREATE_TIME。

连接数据库TEST使用的用户名为root，密码为root。

在系统上确保安装了Python PyMySQL模块。

pip install pymysql -i https://pypi.douban.com/simple

数据库连接

import pymysql


def db_connect():
    # 打开数据库连接
    db = pymysql.connect(host="localhost", user="root", password="root", db="test")
    # 使用cursor()方法创建一个游标对象cursor
    cursor = db.cursor()
    # 使用execute()方法执行SQL查询
    cursor.execute("SELECT VERSION()")
    # 使用 fetchone() 方法获取单条数据
    data = cursor.fetchone()
    # data返回值为一个元组
    # print(data)
    print(f"Database version : {data[0]} ")
    # 关闭数据库连接
    db.close()


def main():
    db_connect()


if __name__ == "__main__":
    main()

创建数据库表

import pymysql


def create_table():
    db = pymysql.connect(host="localhost", user="root", password="root", db="test")
    # 使用 cursor() 方法创建一个游标对象cursor
    cursor = db.cursor()
    # 使用 execute() 方法执行 SQL，如果表存在就删除
    cursor.execute("drop table if exists employee")
    # 使用预处理语句创建表
    sql = """
        create table employee (            
            first_name varchar(20) not null,            
            last_name varchar(20),            
            age int,            
            sex varchar(1),            
            income float,            
            create_time datetime
            );
            """
    try:
        cursor.execute(sql)
        print("CREATE TABLE SUCCESS.")
    except Exception as ex:
        print(f"CREATE TABLE FAILED,CASE:{ex}")
    finally:
        # 关闭数据库连接
        db.close()


def main():
    create_table()


if __name__ == "__main__":
    main()

数据库插入

import pymysql
import datetime


def insert_record():
    db = pymysql.connect(host='localhost', user='root', password='root', db='test')

    # 获取游标
    cursor = db.cursor()

    # SQL 插入语句
    sql = "insert into employee (first_name, last_name, age, sex, income, create_time) values " \
          "('%s', '%s', %d, '%s', %d, '%s')" % ('小', '王', 22, '男', 30000, datetime.datetime.now())
    # 执行 SQL 语句
    try:
        cursor.execute(sql)
        # 提交到数据库执行
        db.commit()
        print('数据插入成功...')
    except Exception as e:
        print(f'数据插入失败: {e}')
        # 如果发生错误就回滚
        db.rollback()
    finally:
        # 关闭数据库连接
        db.close()


if __name__ == '__main__':
    insert_record()

数据库查询

Python查询MySQL使用fetchone()方法获取单条数据，使用fetchall()方法获取多条数据。

• fetchone()：该方法获取下一个查询结果集。结果集是一个对象。
• fetchall()：接收全部返回结果行。
• rowcount：这是一个只读属性，返回执行execute()方法后影响的行数。

import pymysql


def query_data():
    # 打开数据库连接
    db = pymysql.connect(host='localhost', user='root', password='root', db='test')
    # 获取游标
    cursor = db.cursor()
    # 查询语句
    sql = "select * from employee where income > %d" % 10000
    try:
        cursor.execute(sql)
        # 获取所有记录列表
        results = cursor.fetchall()
        for row in results:
            first_name = row[0]
            last_name = row[1]
            age = row[2]
            sex = row[3]
            income = row[4]
            create_time = row[5]
            # 打印结果
            print(f'first_name: {first_name}, last_name: {last_name}, age: {age}, sex: {sex}, income: {income}, '
                  f'create_time: {create_time}')
    except Exception as e:
        print(f'查询错误: {e}')
    finally:
        db.close()


if __name__ == '__main__':
    query_data()

数据库更新

下面的示例将emplyee表中sex字段值为’男’的记录的age字段值增加1

import pymysql


def update_table():
    db = pymysql.connect(host='localhost', user='root', password='root', db='test')
    cursor = db.cursor()
    sql = "update employee set age=age+1 where sex='%s'" % '男'
    try:
        cursor.execute(sql)
        db.commit()
        print('数据更新成功...')
    except Exception as e:
        print(f'更新失败: {e}')
        db.rollback()
    finally:
        db.close()


if __name__ == '__main__':
    update_table()

数据库删除

删除操作用于删除数据表中对应的数据。

• 下面演示删除数据表employee中age大于22的所有数据

import pymysql


def delete_record():
    db = pymysql.connect(host='localhost', user='root', password='root', db='test')
    cursor = db.cursor()
    sql = "delete from employee where age > %d" % 22
    try:
        cursor.execute(sql)
        db.commit()
        print('数据删除成功...')
    except Exception as e:
        print(f'数据删除失败: {e}')
        db.rollback()
    finally:
        db.close()


if __name__ == '__main__':
    delete_record()

至此，在Pycharm中简单的MySQL指令基本学习完成，更多详细的内容请见：https://juejin.cn/post/6899727238392971277。

大一下课程压力较大，时间紧张，近来整理的内容内容量庞大，可能会有误，请谅解！

学海无涯，唯勤是岸。共勉！！！