Python 高级笔记第二部分：数据库的概述和MySQL数据表操作

@ 作者：达内 Python 教学部，吕泽
@ 编辑：博主，Discover304

:star2:数据存储

人工管理阶段
文件管理阶段（.txt .doc .xls）
数据库管理阶段：数据组织结构化降低了冗余度,提高了增删改查的效率,容易扩展,方便程序调用处理

:star2:基本概念

数据库：按照数据一定结构，存储管理数据的仓库。数据库是在数据库管理系统管理和控制下，在一定介质上的数据集合。
数据库管理系统：管理数据库的软件，用于建立和维护数据库。像是MySql

:star2:关系型数据库和非关系型数据库

关系型：采用关系模型（二维表）来组织数据结构的数据库，如Oracle 、SQL_Server、 MySQL
非关系型：不采用关系模型组织数据结构的数据库，如：MongoDB、Redis。其中MangoDB是类似Json的树状结构

:star2:MySQL

官网地址：https://www.mysql.com/

MySQL特点

是开源数据库，使用C和C++编写

能够工作在众多不同的平台上

提供了用于C、C++、Python、Java、Perl、PHP、Ruby众多语言的API

存储结构优良，运行速度快

功能全面丰富

:star2:MySQL安装

Ubuntu安装MySQL服务

终端执行: sudo apt install mysql-server

配置文件：/etc/mysql

数据库存储目录：/var/lib/mysql

Windows/MacOS安装MySQL

下载MySQL安装包 https://dev.mysql.com/downloads/windows/installer/8.0.html

直接运行安装文件安装

:star2:MySQL服务的启动和连接

服务端启动

查看MySQL状态 : sudo service mysql status

启动/停止/重启服务：sudo service mysql start/stop/restart

连接数据库
1
mysql    -h  主机地址   -u  用户名    -p  
注意：

回车后输入数据库密码（我们设置的是123456）

如果链接自己主机数据库可省略 -h 选项
h

关闭连接
1
2
ctrl-D
exit

:star2:MySQL数据库结构

数据元素 –> 记录 –>数据表 –> 数据库

数据表（table）：存放数据的表格
字段（column）：每个列，用来表示该列数据的含义
记录（row）：每个行，表示一组完整的数据

:star2:SQL语言

SQL: 结构化查询语言(Structured Query Language)，一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

SQL语言特点

SQL语言基本上独立于数据库本身

各种不同的数据库对SQL语言的支持与标准存在着细微的不同

每条命令以 ; 结尾

SQL命令（除了数据库名和表名）关键字和字符串可以不区分字母大小写

:star2:数据库管理

查看已有库：show databases;
创建库：create database 库名 [character set utf8];
切换库：use 库名;
查看当前所在库：select database();
删除库：drop database 库名;

注意：库名的命名

数字、字母、下划线,但不能使用纯数字

库名区分字母大小写。这一点一定要注意，因为SQL是不分大小写的。

不要使用特殊字符和mysql关键字

:star2:数据表管理

基本思考过程：

确定存储内容
明确字段构成
确定字段数据类型

:star:基础数据类型

:sparkles:数字类型

整数类型：INT，SMALLINT，TINYINT，MEDIUMINT，BIGINT
浮点类型：FLOAT，DOUBLE，DECIMAL
比特值类型：BIT

对于准确性要求比较高的东西，比如money，用decimal类型减少存储误差。声明语法是DECIMAL(M,D)。M是数字的最大数字位数，D是小数点右侧数字的位数。比如 DECIMAL(6,2)最多存6位数字，小数点后占2位,取值范围-9999.99到9999.99。
比特值类型指0，1值表达2种情况，如真，假

:sparkles:字符串类型

普通字符串： CHAR，VARCHAR
存储文本：TEXT
存储二进制数据： BLOB
存储选项型数据：ENUM，SET

char：定长，即指定存储字节数后，无论实际存储了多少字节数据，最终都占指定的字节大小。默认只能存1字节数据。存取效率高。
varchar：不定长，效率偏低，但是节省空间，实际占用空间根据实际存储数据大小而定。必须要指定存储大小 varchar(50)
enum用来存储给出的多个值中的一个值,即单选，enum(‘A’,’B’,’C’)
set用来存储给出的多个值中一个或多个值，即多选，set(‘A’,’B’,’C’)

:sparkles:时间类型数据

日期： DATE YYYY-MM-DD
日期时间： DATETIME YYYY-MM-DD HH:MM:SS ，TIMESTAMP YYYY-MM-DD HH:MM:SS
时间： TIME HH:MM:SS
年份：YEAR

日期时间函数：now() 返回服务器当前日期时间,格式对应datetime类型

时间操作：时间类型数据可以进行比较和排序等操作，在写时间字符串时尽量按照标准格式书写。

1
2
3

e.g.
select * from marathon where birthday>='2000-01-01';
select * from marathon where birthday>="2000-07-01" and performance<="2:30:00";

:star:表的基本操作

创建表
1
2
3
4
5
6
create table 表名(
  字段名 数据类型 字段约束,
  字段名 数据类型 字段约束,
  ...,
  字段名 数据类型 字段约束
  );
约束效果

UNSIGNED 设置数字为无符号

NOT NULL 在操作数据库时如果输入该字段的数据为NULL ，就会报错

DEFAULT [?] 设置一个字段的默认值

COMMENT [?] 增加字段说明

AUTO_INCREMENT 定义列为自增的属性，一般用于主键，数值会自动加1

PRIMARY KEY 定义列为主键。主键的值不能重复,且不能为空。

约束	效果
UNSIGNED	设置数字为无符号
NOT NULL	在操作数据库时如果输入该字段的数据为NULL ，就会报错
DEFAULT [?]	设置一个字段的默认值
COMMENT [?]	增加字段说明
AUTO_INCREMENT	定义列为自增的属性，一般用于主键，数值会自动加1
PRIMARY KEY	定义列为主键。主键的值不能重复,且不能为空。

补充

查看数据表：show tables;

查看表结构：desc 表名;

查看数据表创建信息：show create table 表名;

删除表：drop table 表名;

:star2:表数据基本操作

:star:插入(insert)

1 2	insert into 表名 values(值1，值2...),(值1，值2...),...; insert into 表名 (字段1,...) values (值1，值2...),...;

:star:查询(select)

select * from 表名 [where 条件];
select 字段1,字段2 from 表名 [where 条件];

e.g. 找班上分数在90到100分且年龄大于9的男生
select * from class where sex='m' and score between 90 and 100 and age>9 ;

where子句

where子句在sql语句中扮演了重要角色，主要通过一定的运算条件进行数据的筛选，在查询，删除，修改中都有使用。

算数运算符

比较运算符

逻辑运算符

运算符优先级

:star:更新表记录(update)

update 表名 set 字段1=值1,字段2=值2,... [where 条件];

e.g.
update class set age=18,score=91 where name="Abby";
update class set sex='m' where sex is null;
update class set age=age+1;

注意: update语句后如果不加where条件，意思是所有记录全部更新，这个操作很少见。

:star:删除表记录（delete）

delete from 表名 where 条件;

e.g.
delete from class where score=0 and sex='m';

注意:delete语句后如果不加where条件,所有记录全部清空

:star:表字段的操作(alter)

1	语法：alter table 表名执行动作;

添加字段(add)

alter table 表名 add 字段名 数据类型;
alter table 表名 add 字段名 数据类型 first;
alter table 表名 add 字段名 数据类型 after 字段名;

e.g. 
alter table hobby add phone char(10) after price;

删除字段(drop)

alter table 表名 drop 字段名;

e.g. 
alter table hobby drop level;

修改数据类型(modify)

alter table 表名 modify 字段名 新数据类型;

e.g. 
alter table hobby modify phone char(16);

修改字段名(change)

alter table 表名 change 旧字段名 新字段名 新数据类型;

e.g. 
alter table hobby change phone tel char(16);

:star:高级查询语句

模糊查询
LIKE用于在where子句中进行模糊查询，SQL LIKE 子句中使用百分号 %来表示任意0个或多个字符，下划线_表示任意一个字符。
1
2
3
4
5
6
7
8
SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1

e.g. 
select * from class where name like "T%";
select * from class where name like "____";
select * from hobby where hobby like "%draw%";

as 用法
在sql语句中as用于给字段或者表重命名

select name as 姓名,score as 分数 from class;

e.g.
select cls.name,cls.score from class as cls where cls.score>80;

排序
ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。
使用 ORDER BY 子句将查询数据排序后再返回数据：
1
2
SELECT field1, field2,...fieldN from table_name1 where field1
ORDER BY field1 [ASC [DESC]]
默认情况ASC表示升序，DESC表示降序
1
2
select * from class order by score desc;
select * from class where sex='m' order by score;
复合排序：对多个字段排序，即当第一排序项相同时按照第二排序项排序
1
select * from class order by age,score desc;

限制
LIMIT 子句用于限制由 SELECT 语句返回的数据数量或者 UPDATE,DELETE语句的操作数量
带有 LIMIT 子句的 SELECT 语句的基本语法如下：
1
2
3
4
5
6
7
8
9
10
11
SELECT column1, column2, columnN 
FROM table_name
WHERE field
LIMIT [num] [OFFSET num]

e.g.
update class set score=83 limit 1;
--男生第一名
select * from class where sex='m' order by score desc limit 1;
--男生第二名
select * from class where sex='m' order by score desc limit 1 offset 1;

去重语句
distinct语句，不显示字段重复值
1
2
3
4
eg1 : 表中都有哪些国家
  select distinct country from sanguo;
eg2 : 计算一共有多少个国家
  select count(distinct country) from sanguo;
注意: distinct和from之间所有字段都相同才会去重

联合查询
UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
UNION 操作符语法格式：
1
2
3
4
5
6
7
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
默认UNION后卫 DISTINCT表示删除结果集中重复的数据。如果使用ALL则返回所有结果集，包含重复数据。
1
2
3
4
5
6
7
8
9
10
--可以查询不同字段，但是字段数量必须一致
select name,age,score from class where score>80
union
select name,hobby,price from hobby;

--order by只能加在最后表示对union结果一起排序
select * from class where sex='m'
union all
select * from class where score>80
order by score;

子查询
定义：当一个语句中包含另一个select 查询语句，则称之为有子查询的语句
子查询使用位置
from 之后，此时子查询的内容作为一个新的表内容，再进行外层select查询
1
2
select * from (select * from class where sex='m') as man 
where score > 80;
where子句中，此时select查询到的内容作为外层查询的条件值
1
2
3
4
5
6
7
8
 --查询与tom同岁的学生
select * from class
where age=(select age from class where name='Tom');
  
**注意**
1.  需要将子查询结果集重命名一下，方便where子句中的引用操作
2. 子句结果作为一个值使用时，返回的结果需要一个明确值，不能是多行或者多列。
3. 如果子句结果作为一个集合使用，即where子句中是in操作，则结果可以是一个字段的多个记录。

查询过程
通过之前的学习看到，一个完整的select语句内容是很丰富的。下面看一下select的执行过程：

(5) SELECT DISTINCT <select_list>                     
(1) FROM <left_table> <join_type> JOIN <right_table> ON <on_predicate>
(2) WHERE <where_predicate>
(3) GROUP BY <group_by_specification>
(4) HAVING <having_predicate>
(6) ORDER BY <order_by_list>
(7) LIMIT <limit_number>

:star:聚合操作

聚合操作指的是在数据查找基础上对数据的进一步整理筛选行为，实际上聚合操作也属于数据的查询筛选范围。

:sparkles:聚合分组：group by

给查询的结果进行分组

e.g. : 计算每个国家的平均攻击力

1	select country,avg(attack) from sanguo group by country;

注意：使用分组时select 后的字段为group by分组的字段和聚合函数，不能包含其他内容。group by也可以同时依照多个字段分组，如group by A，B 此时必须A,B两个字段值均相同才算一组。

:sparkles:聚合函数

方法	功能
avg(字段名)	该字段的平均值
max(字段名)	该字段的最大值
min(字段名)	该字段的最小值
sum(字段名)	该字段所有记录的和
count(字段名)	统计该字段记录的个数

注意：聚合分组的 select 后只能写聚合函数，无法查找其他字段，除非该字段值全都一样。

:sparkles:聚合筛选：having

对分组聚合后的结果进行进一步筛选

e.g. ：统计平均攻击力大于250的国家的英雄数量

1
2
3

select country,count(*) from sanguo
group by country
having avg(attack)>250;

注意

having语句必须与group by联合使用。

having语句存在弥补了where关键字不能与聚合函数联合使用的不足,where只能操作表中实际存在的字段。

:star2:索引操作

:star:概述

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。能够加快数据检索速度,提高查找效率。但是会占用数据库物理存储空间，当对表中数据更新时,索引需要动态维护,降低数据写入效率。

注意

通常我们只在经常进行查询操作的字段上创建索引

对于数据量很少的表或者经常进行写操作而不是查询操作的表不适合创建索引

:star:索引分类

普通索引(MUL)：字段值无约束,KEY标志为 MUL
唯一索引(UNI)：字段值不允许重复,但可为 NULL,KEY标志为 UNI
主键索引(PRI)：一个表中只能有一个主键字段, 主键字段不允许重复,且不能为NULL，KEY标志为PRI。通常设置记录编号字段id,能唯一锁定一条记录

:star:索引创建

创建表时顺带创建索引
1
2
3
4
5
6
create table 表名(
字段名 数据类型，
primary key(字段名),
index 索引名(字段名),
unique 索引名(字段名)
);
注意：也可以在声明数据类型的时候写primary key

为已有表的字段创建索引
1
create [unique] index 索引名 on 表名(字段名);

主键索引添加

1 2	alter table 表名 add primary key(id);

:star:查看索引

1 2	desc 表名; --> KEY标志为：MUL 、UNI。 show index from 表名;

:star:删除索引

1
2
3
4
drop index 索引名 on 表名;

alter table 表名
drop primary key;
注：可以这样记忆：主键没有名称，所以没有办法用第一种方法

:star2:外键约束和表关联关系

:star:外键约束：foreign key

建立表与表之间的某种约束的关系，由于这种关系的存在，能够让表与表之间的数据，更加的完整，关连性更强，为了具体说明创建如下部门表和人员表。

注：约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、关联性

外键约束分主表和从表：若同一个数据库中，B表的外键与A表的主键相对应，则A表为主表，B表为从表。

:sparkles:创建外键

[CONSTRAINT 外键名称] 
FOREIGN KEY 从表外键
REFERENCES 主表名(主表主键) -- 从表的外键字段数据类型与指定的主表主键应该相同。
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用

e.g.
-- 创建表时直接建立外键
CREATE TABLE person (
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(32) NOT NULL,
age tinyint unsigned,
salary decimal(10,2),
dept_id int ,
constraint dept_fk 
foreign key(dept_id) 
references dept(id)
);

-- 建立表后增加外键
alter table person add
constraint dept_fk
foreign key(dept_id)
references dept(id);

注意：并不是任何情况表关系都需要建立外键来约束，如果没有类似上面的约束关系时也可以不建立。

:sparkles:解除外键约束

通过外键名称解除外键约束

alter table 表名
drop foreign key 外链名;

drop index 索引名 on 表名

注意：删除外键后发现desc查看 MUL 索引标志还在，其实外键也是一种索引，需要将外键名称的索引删除之后才可以。

:sparkles:级联动作：on delete / on update

restrict(默认) : 当主表删除或修改记录时，如果从表中有相关联记录则不允许主表变动
cascade ：当主表删除记录或更改被参照字段的值时,从表会级联更新
set null：当主表记录变动时，从表外键字段值变为null
no action：不进行级联操作

:star:表关联关系

当我们应对复杂的数据关系的时候，数据表的设计就显得尤为重要，认识数据之间的依赖关系是更加合理创建数据表关联性的前提。一对多和多对多是常见的表数据关系：

一对多关系
一张表中有一条记录可以对应另外一张表中的多条记录；但是反过来，另外一张表的一条记录
只能对应第一张表的一条记录，这种关系就是一对多或多对一

举例：一个人可以拥有多辆汽车，每辆车登记的车主只有一人。

多对多关系
一对表中（A）的一条记录能够对应另外一张表（B）中的多条记录；同时B表中的一条记录
也能对应A表中的多条记录

举例：一个运动员可以报多个项目，每个项目也会有多个运动员参加,这时为了表达多对多关系需要单独创建关系表。

:sparkles:E-R模型图

E-R模型(Entry-Relationship)即实体-关系数据模型,用于数据库设计,用简单的图(E-R图)反映了现实世界中存在的事物或数据以及他们之间的关系

E-R模型图包括：实体、属性、关系

实体

描述客观事物的概念

表示方法：矩形框

示例：一个人、一本书、一杯咖啡、一个学生

属性

实体具有的某种特性

表示方法：椭圆形

示例
学生属性：学号、姓名、年龄、性别、专业 …
感受属性：悲伤、喜悦、刺激、愤怒 …

关系

实体之间的联系

一对多关联(1:n)

多对多关联(m:n)

:sparkles:E-R图的绘制

矩形框代表实体,菱形框代表关系,椭圆形代表属性

:star:表关联查询

如果多个表存在一定关联关系，可以多表在一起进行查询操作，其实表的关联整理与外键约束之间并没有必然联系，但是基于外键约束设计的具有关联性的表往往会更多使用关联查询查找数据。

简单多表查询
多个表数据可以联合查询，语法格式如下：

select  字段1,字段2... 
from 表1,表2... [where 条件]

e.g.
select name,salary,dname
from person,dept 
where person.dept_id = dept.id and salary>=20000;

笛卡尔积现象就是将A表的每一条记录与B表的每一条记录强行拼在一起。所以，如果A表有n条记录，B表有m条记录，笛卡尔积产生的结果就会产生n*m条记录。

内连接
内连接查询只会查找到符合条件的记录，其实结果和表关联查询是一样的,官方更推荐使用内连接查询。
1
2
3
select 字段列表
from 表1 inner join  表2
on 表1.字段 = 表2.字段;

左连接
左表全部显示，显示右表中与左表匹配的项
1
2
3
select 字段列表
from 表1 left join  表2
on 表1.字段 = 表2.字段;

右连接
右表全部显示，显示左表中与右表匹配的项
1
2
3
select 字段列表
from 表1  right join  表2
on 表1.字段 = 表2.字段;

注意：根据官方说法，我们使用数据量大的表作为基准表,放在left或者right前面。

:star2:补充

性能查看

1 2	set profiling = 1；打开功能（项目上线一般不打开） show profiles 查看语句执行信息