数据处理

1. 文件处理

1.1 引入

- 普通的英文字符字符串常量可以在前面加b转换为字节串，例如：b'hello'
- 变量或者包含非英文字符的字符串转换为字节串方法 ：str.encode()
- 字节串转换为字符串方法 : bytes.decode() 

注意：python字符串用来表达utf8字符，因为并不是所有二进制内容都可以转化为utf8字符，所以不是所有字节串都能转化为字符串，但是所有字符串都能转化成二进制，所以所有字符串都能转换为字节串。

1.2 文件读写操作

使用程序操作文件，无外乎对文件进行读或者写

对文件实现读写的基本操作步骤为：打开文件，读写文件，关闭文件。

1.2.1 打开文件

file_object = open(file_name, access_mode='r', buffering=-1，encoding=None)
功能：打开一个文件，返回一个文件对象。
参数：file_name  文件名；
     access_mode  打开文件的方式,如果不写默认为‘r’ 
     buffering  1表示有行缓冲，默认则表示使用系统默认提供的缓冲机制。
     encoding='UTF-8'  设置打开文件的编码方式，一般Linux下不需要
返回值：成功返回文件操作对象。
打开模式效果
r以读方式打开，文件必须存在
w以写方式打开，文件不存在则创建，存在清空原有内容
a以追加模式打开，文件不存在则创建，存在则继续进行写操作
r+以读写模式打开 文件必须存在
w+以读写模式打开文件，不存在则创建，存在清空原有内容
a+追加并可读模式，文件不存在则创建，存在则继续进行写操作
rb以二进制读模式打开 同r
wb以二进制写模式打开 同w
ab以二进制追加模式打开 同a
rb+以二进制读写模式打开 同r+
wb+以二进制读写模式打开 同w+
ab+以二进制读写模式打开 同a+

注意 ：

  1. 以二进制方式打开文件，读取内容为字节串，写入也需要写入字节串
  2. 无论什么文件都可以使用二进制方式打开，但是二进制文件则不能以文本方式打开，否则后续读写会报错。

1.2.2 读取文件

read([size])
功能： 来直接读取文件中字符。
参数： 如果没有给定size参数（默认值为-1）或者size值为负，文件将被读取直至末尾，给定size最多读取给定数目个字符（字节）。
返回值： 返回读取到的内容

注意：文件过大时候不建议直接读取到文件结尾，读到文件结尾会返回空字符串。

readline([size])
功能： 用来读取文件中一行
参数： 如果没有给定size参数（默认值为-1）或者size值为负，表示读取一行，给定size表示最多读取制定的字符（字节）。
返回值： 返回读取到的内容
readlines([sizeint])
功能： 读取文件中的每一行作为列表中的一项
参数： 如果没有给定size参数（默认值为-1）或者size值为负，文件将被读取直至末尾，给定size表示读取到size字符所在行为止。
返回值： 返回读取到的内容列表
# 文件对象本身也是一个可迭代对象，在for循环中可以迭代文件的每一行。

for line in f:
     print(line)

1.2.3 写入文件

write(data)
功能: 把文本数据或二进制数据块的字符串写入到文件中去
参数：要写入的内容
返回值：写入的字符个数

注意： 如果需要换行要自己在写入内容中添加\n

writelines(str_list)
功能：接受一个字符串列表作为参数，将它们写入文件。
参数: 要写入的内容列表

1.2.4 关闭文件

打开一个文件后我们就可以通过文件对象对文件进行操作了，当操作结束后可以关闭文件操作

file_object.close()
  1. 可以销毁对象节省资源，（当然如果不关闭程序结束后对象也会被销毁）。
  2. 防止后面对这个对象的误操作。

1.2.5 with操作

python中的with语句也可以用于访问文件，在语句块结束后会自动释放资源。

with context_expression [as obj]:
    with-body
with open('file','r+') as f:
    f.read()

注意 ： with语句块结束后会自动释放f所以不再需要close().

1.2.6 缓冲区

  1. 缓冲区被写满
  2. 程序执行结束或者文件对象被关闭
  3. 程序中调用flush()函数
file_obj.flush()

1.2.7 文件偏移量

1.3 os模块

os模块是Python标准库模块，包含了大量的文件处理函数。

os.path.getsize(file)
功能： 获取文件大小
参数： 指定文件
返回值： 文件大小
os.listdir(dir)
功能： 查看文件列表
参数： 指定目录
返回值：目录中的文件名列表
os.path.exists(file)
功能： 查看文件是否存在 
参数： 指定文件
返回值：存在返回True，不存在返回False
os.path.isfile(file)
功能： 判断文件类型 
参数： 指定文件
返回值：普通文件返回True，否则返回False
os.remove(file)
功能： 删除文件 
参数： 指定文件

2. 正则表达式

2.1 概述

  1. 文本数据处理已经成为常见的编程工作之一
  2. 对文本内容的搜索，定位，提取是逻辑比较复杂的工作
  3. 为了快速方便的解决上述问题，产生了正则表达式技术

即文本的高级匹配模式，其本质是由一系列字符和特殊符号构成的字串，这个字串即正则表达式。

通过普通字符和有特定含义的字符，来组成字符串，用以描述一定的字符串规则，比如：重复，位置等，来表达某类特定的字符串，进而匹配。

  1. 熟练掌握正则表达式元字符
  2. 能够读懂常用正则表达式，编辑简单的正则规则
  3. 能够熟练使用re模块操作正则表达式

2.2 元字符使用

匹配规则：每个普通字符匹配其对应的字符

e.g.
In : re.findall('ab',"abcdefabcd")
Out: ['ab', 'ab']

注意：正则表达式在python中也可以匹配中文

元字符: |

匹配规则: 匹配 | 两侧任意的正则表达式即可

e.g.
In : re.findall('com|cn',"www.baidu.com/www.tmooc.cn")
Out: ['com', 'cn']

元字符：.

匹配规则：匹配除换行外的任意一个字符

e.g.
In : re.findall('张.丰',"张三丰,张四丰,张五丰")
Out: ['张三丰', '张四丰', '张五丰']

元字符： [字符集]

匹配规则: 匹配字符集中的任意一个字符

表达形式:

[abc#!好] 表示 [] 中的任意一个字符 [0-9],[a-z],[A-Z] 表示区间内的任意一个字符 [_#?0-9a-z] 混合书写，一般区间表达写在后面

e.g.
In : re.findall('[aeiou]',"How are you!")
Out: ['o', 'a', 'e', 'o', 'u']

元字符：[^字符集]

匹配规则：匹配除了字符集以外的任意一个字符

e.g.
In : re.findall('[^0-9]',"Use 007 port")
Out: ['U', 's', 'e', ' ', ' ', 'p', 'o', 'r', 't']

元字符: ^

匹配规则：匹配目标字符串的开头位置

e.g.
In : re.findall('^Jame',"Jame,hello")
Out: ['Jame']

元字符: $

匹配规则: 匹配目标字符串的结尾位置

e.g.
In : re.findall('Jame$',"Hi,Jame")
Out: ['Jame']

规则技巧: ^ 和 $必然出现在正则表达式的开头和结尾处。如果两者同时出现，则中间的部分必须匹配整个目标字符串的全部内容。

元字符: *

匹配规则：匹配前面的字符出现0次或多次

e.g.
In : re.findall('wo*',"wooooo~~w!")
Out: ['wooooo', 'w']

元字符：+

匹配规则： 匹配前面的字符出现1次或多次

e.g.
In : re.findall('[A-Z][a-z]+',"Hello World")
Out: ['Hello', 'World']

元字符：?

匹配规则： 匹配前面的字符出现0次或1次

e.g. 匹配整数
In [28]: re.findall('-?[0-9]+',"Jame,age:18, -26")
Out[28]: ['18', '-26']

元字符：{n}

匹配规则： 匹配前面的字符出现n次

e.g. 匹配手机号码
In : re.findall('1[0-9]{10}',"Jame:13886495728")
Out: ['13886495728']

元字符：{m,n}

匹配规则： 匹配前面的字符出现m-n次

e.g. 匹配qq号
In : re.findall('[1-9][0-9]{5,10}',"Baron:1259296994") 
Out: ['1259296994']

元字符： \d \D

匹配规则：\d 匹配任意数字字符，\D 匹配任意非数字字符

e.g. 匹配端口
In : re.findall('\d{1,5}',"Mysql: 3306, http:80")
Out: ['3306', '80']

元字符： \w \W

匹配规则: \w 匹配普通字符，\W 匹配非普通字符

说明: 普通字符指数字，字母，下划线，汉字。

e.g.
In : re.findall('\w+',"server_port = 8888")
Out: ['server_port', '8888']

元字符： \s \S

匹配规则: \s匹配空字符，\S 匹配非空字符

说明：空字符指 空格 \r \n \t \v \f 字符

e.g.
In : re.findall('\w+\s+\w+',"hello    world")
Out: ['hello    world']

元字符： \b \B

匹配规则： \b 表示单词边界，\B 表示非单词边界

说明：单词边界指数字字母(汉字)下划线与其他字符的交界位置。

e.g.
In : re.findall(r'\bis\b',"This is a test.")
Out: ['is']

注意： 当元字符符号与Python字符串中转义字符冲突的情况则需要使用r将正则表达式字符串声明为原始字符串，如果不确定那些是Python字符串的转义字符，则可以在所有正则表达式前加r。

类别元字符
匹配字符. [...] [^...] \d \D \w \W \s \S
匹配重复* + ? {n} {m,n}
匹配位置^ $ \b \B
其他`

2.3 匹配规则

2.3.1 特殊字符匹配

e.g. 匹配特殊字符 . 时使用 \. 表示本身含义
In : re.findall('-?\d+\.?\d*',"123,-123,1.23,-1.23")
Out: ['123', '-123', '1.23', '-1.23']

2.3.2 贪婪模式和非贪婪模式

贪婪模式: 默认情况下，匹配重复的元字符总是尽可能多的向后匹配内容。比如: * + ? {m,n}

非贪婪模式(懒惰模式): 让匹配重复的元字符尽可能少的向后匹配内容。

在对应的匹配重复的元字符后加 ’?’ 号即可

*  ->  *?
+  ->  +?
?  ->  ??
{m,n} -> {m,n}?
e.g.
In : re.findall(r'\(.+?\)',"(abcd)efgh(higk)")
Out: ['(abcd)', '(higk)']

2.3.3 正则表达式分组

在正则表达式中，以()建立正则表达式的内部分组，子组是正则表达式的一部分，可以作为内部整体操作对象。

e.g.  改变 +号 重复的对象
In : re.search(r'(ab)+',"ababababab").group()
Out: 'ababababab'

e.g. 改变 |号 操作对象
In : re.search(r'(王|李)\w{1,3}',"王者荣耀").group()
Out: '王者荣耀'

捕获组本质也是一个子组，只不过拥有一个名称用以表达该子组的意义，这种有名称的子组即为捕获组。

格式：(?P<name>pattern)

e.g. 给子组命名为 "pig"
In : re.search(r'(?P<pig>ab)+',"ababababab").group('pig')
Out: 'ab'

2.3.4 正则表达式匹配原则

  1. 正确性,能够正确的匹配出目标字符串.
  2. 排他性,除了目标字符串之外尽可能少的匹配其他内容.
  3. 全面性,尽可能考虑到目标字符串的所有情况,不遗漏.

2.4 Python re模块使用

2.4.1 基础函数使用

 re.findall(pattern,string,flags = 0)
 功能: 根据正则表达式匹配目标字符串内容
 参数: pattern  正则表达式
      string 目标字符串
      flags  功能标志位,扩展正则表达式的匹配
 返回值: 匹配到的内容列表,如果正则表达式有子组则只能获取到子组对应的内容
 re.split(pattern,string,max，flags = 0)
 功能: 使用正则表达式匹配内容,切割目标字符串
 参数: pattern  正则表达式
      string 目标字符串
      max 最多切割几部分
      flags  功能标志位,扩展正则表达式的匹配
 返回值: 切割后的内容列表
 re.sub(pattern,replace,string,count,flags = 0)
 功能: 使用一个字符串替换正则表达式匹配到的内容
 参数: pattern  正则表达式
      replace  替换的字符串
      string 目标字符串
      count  最多替换几处,默认替换全部
      flags  功能标志位,扩展正则表达式的匹配
 返回值: 替换后的字符串

2.4.2 生成match对象

 re.finditer(pattern,string,flags = 0)
 功能: 根据正则表达式匹配目标字符串内容
 参数: pattern  正则表达式
      string 目标字符串
      flags  功能标志位,扩展正则表达式的匹配
 返回值: 匹配结果的迭代器
re.match(pattern,string,flags=0)
功能：匹配某个目标字符串开始位置
参数：pattern 正则
	string  目标字符串
返回值：匹配内容match object
re.search(pattern,string,flags=0)
功能：匹配目标字符串第一个符合内容
参数：pattern 正则
	string  目标字符串
返回值：匹配内容match object

2.4.3 match对象使用

2.4.4 flags参数扩展

注意：同时使用多个flag，可以用竖线连接 flags = re.I | re.A

3. 数据库

3.1概述

  1. 人工管理阶段

    缺点 ： 数据存储量有限，共享处理麻烦，操作容易混乱

  2. 文件管理阶段 （.txt .doc .xls）

    优点 ： 数据可以长期保存,可以存储大量的数据,使用简单。

    缺点 ： 数据一致性差,数据查找修改不方便,数据冗余度可能比较大。

  3. 数据库管理阶段

    优点 ： 数据组织结构化降低了冗余度,提高了增删改查的效率,容易扩展,方便程序调用处理

    缺点 ： 需要使用sql 或者其他特定的语句，相对比较专业

非开源：Oracle、DB2、SQL_Server

3.2 MySQL

1996年，MySQL 1.0发布,作者Monty Widenius, 为一个叫TcX的公司打工，当时只是内部发布。到了96年10月，MySQL 3.11.1发布了，一个月后，Linux版本出现了。真正的MySQL关系型数据库于1998年1月发行第一个版本。MySQL是个开源数据库，后来瑞典有了专门的MySQL开发公司，将该数据库发展壮大，在之后被Sun收购，Sun又被Oracle收购。

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

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

3.3 SQL语言

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

3.4 数据库管理

  1. 查看已有库

show databases;

  1. 创建库

create database 库名 [character set utf8];

e.g. 创建stu数据库，编码为utf8
create database stu character set utf8;
create database stu charset=utf8;

注意：库名的命名

  1. 数字、字母、下划线,但不能使用纯数字
  2. 库名区分字母大小写
  3. 不要使用特殊字符和mysql关键字
  1. 切换库

use 库名;

e.g. 使用stu数据库
use stu;
  1. 查看当前所在库

select database();

  1. 删除库

drop database 库名;

e.g. 删除test数据库
drop database test;

3.5 数据表管理

3.5.1 基础数据类型

注意：

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

注意：

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

3.5.2 表的基本操作

create table 表名(字段名 数据类型 约束,字段名 数据类型 约束,…字段名 数据类型 约束);

e.g.  创建班级表
create table class_1 (id int primary key auto_increment,name varchar(32) not null,age tinyint unsigned not null,sex enum('w','m'),score float default 0.0);

e.g. 创建兴趣班表
create table interest (id int primary key auto_increment,name varchar(32) not null,hobby set('sing','dance','draw'),level char not null,price decimal(6,2),remark text);

3.6 表数据基本操作

3.5.1 插入(insert)

insert into 表名 values(值1),(值2),...;
insert into 表名(字段1,...) values(值1),...;
e.g. 
insert into class_1 values (2,'Baron',10,'m',91),(3,'Jame',9,'m',90);

insert into class_1 (name,age,sex,score) values ('Lucy',17,'w',81);

3.6.2 查询(select)

select * from 表名 [where 条件];
select 字段1,字段2 from 表名 [where 条件];
e.g. 
select * from class_1;
select name,age from class_1;

3.6.3 where子句

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

e.g.
select * from class_1 where age % 2 = 0;
e.g.
select * from class_1 where age > 8;
select * from class_1 where between 8 and 10;
select * from class_1 where age in (8,9);
e.g.
select * from class_1 where sex='m' and age>9;

3.6.4 更新表记录(update)

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

注意:update语句后如果不加where条件,所有记录全部更新
e.g.
update class_1 set age=11 where name='Abby';

3.6.5 删除表记录（delete）

delete from 表名 where 条件;

注意:delete语句后如果不加where条件,所有记录全部清空
e.g.
delete from class_1 where name='Abby';

3.6.6 表字段的操作(alter)

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

* 添加字段(add)
    alter table 表名 add 字段名 数据类型;
    alter table 表名 add 字段名 数据类型 first;
    alter table 表名 add 字段名 数据类型 after 字段名;
* 删除字段(drop)
    alter table 表名 drop 字段名;
* 修改数据类型(modify)
    alter table 表名 modify 字段名 新数据类型;
* 修改字段名(change)
    alter table 表名 change 旧字段名 新字段名 新数据类型;
* 表重命名(rename)
    alter table 表名 rename 新表名;
e.g. 
alter table interest add tel char(11) after name;

3.5.7 时间类型数据

e.g.
create table marathon (id int primary key auto_increment,athlete varchar(32),birthday date,registration_time datetime,performance time);
  select * from marathon where birthday>='2000-01-01';
  select * from marathon where birthday>="2000-07-01" and performance<="2:30:00";

3.7 高级查询语句

select * from class_1 where sex='m' UNION ALL select * from class_1 where age > 9;

通过之前的学习看到，一个完整的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>

3.8 聚合操作

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

3.8.1 聚合函数

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

eg1 : 找出表中的最大攻击力的值？

select max(attack) from sanguo;

eg2 : 表中共有多少个英雄？

select count(name) as number from sanguo;

eg3 : 蜀国英雄中攻击值大于200的英雄的数量

select count(*) from sanguo where attack > 200;

注意： 此时select 后只能写聚合函数，无法查找其他字段。

3.8.2 聚合分组

给查询的结果进行分组

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

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

e.g. : 对多个字段创建索引，此时多个字段都相同时为一组

select age,sex,count(*) from class1 group by age,sex;

e.g. : 所有国家的男英雄中 英雄数量最多的前2名的 国家名称及英雄数量

select country,count(id) as number from sanguo 
where gender='M' group by country
order by number DESC
limit 2;

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

3.8.3 聚合筛选

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

eg1 : 找出平均攻击力大于105的国家的前2名,显示国家名称和平均攻击力

select country,avg(attack) from sanguo 
group by country
having avg(attack)>105
order by avg(attack) DESC
limit 2;

注意

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

3.8.4 去重语句

不显示字段重复值

eg1 : 表中都有哪些国家
  select distinct name,country from sanguo;
eg2 : 计算一共有多少个国家
  select count(distinct country) from sanguo;

注意: distinct和from之间所有字段都相同才会去重

3.8.5 聚合运算

运算符 ： + - * / %

eg1: 查询时显示攻击力翻倍
  select name,attack*2 from sanguo;
eg2: 更新蜀国所有英雄攻击力 * 2
  update sanguo set attack=attack*2 where country='蜀国';

3.9 索引操作

3.9.1 概述

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

注意 ：

  1. 通常我们只在经常进行查询操作的字段上创建索引
  2. 对于数据量很少的表或者经常进行写操作而不是查询操作的表不适合创建索引

3.9.2 索引分类

普通索引 ：字段值无约束,KEY标志为 MUL

唯一索引(unique) ：字段值不允许重复,但可为 NULL,KEY标志为 UNI

一个表中只能有一个主键字段, 主键字段不允许重复,且不能为NULL，KEY标志为PRI。通常设置记录编号字段id,能唯一锁定一条记录

3.9.3 索引创建

create table 表名(
字段名 数据类型，
字段名 数据类型，
index 索引名(字段名),
index 索引名(字段名),
unique 索引名(字段名)
);
create [unique] index 索引名 on 表名(字段名);
e.g.
create unique index name_index on cls(name);
alter table 表名 add primary key(id);
1、desc 表名;  --> KEY标志为：MUL 、UNI。
2、show index from 表名;
drop index 索引名 on 表名;
alter table 表名 drop primary key;  # 删除主键
set  profiling = 1； 打开功能 （项目上线一般不打开）

show profiles  查看语句执行信息

3.10 外键约束和表关联关系

3.10.1 外键约束

# 创建部门表
CREATE TABLE dept (id int PRIMARY KEY auto_increment,dname VARCHAR(50) not null);
# 创建人员表
CREATE TABLE person (
  id int PRIMARY KEY AUTO_INCREMENT,
  name varchar(32) NOT NULL,
  age tinyint DEFAULT 0,
  sex enum('m','w','o') DEFAULT 'o',
  salary decimal(8,2) DEFAULT 250.00,
  hire_date date NOT NULL,
  dept_id int
) ;

上面两个表中每个人员都应该有指定的部门，但是实际上在没有约束的情况下人员是可以没有部门的或者也可以添加一个不存在的部门，这显然是不合理的。

3.10.2 表关联设计

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

一张表的一条记录一定只能与另外一张表的一条记录进行对应，反之亦然。

举例 : 学生信息和学籍档案，一个学生对应一个档案，一个档案也只属于一个学生

create table student(id int primary key auto_increment,name varchar(50) not null);

create table record(id int primary key auto_increment,
comment text not null,
st_id int unique,
constraint st_fk foreign key(st_id) references student(id) 
on delete cascade 
on update cascade
);

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

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

create table person(
  id varchar(32) primary key,
  name varchar(30),
  sex char(1),
  age int
);

create table car(
  id varchar(32) primary key,
  name varchar(30),
  price decimal(10,2),
  pid varchar(32),
  constraint car_fk foreign key(pid) references person(id)
);

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

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

CREATE TABLE athlete (
  id int primary key AUTO_INCREMENT,
  name varchar(30),
  age tinyint NOT NULL,
  country varchar(30) NOT NULL,
  description varchar(30)
);

CREATE TABLE item (
  id int primary key AUTO_INCREMENT,
  rname varchar(30) NOT NULL
);

CREATE TABLE athlete_item (
   id int primary key auto_increment,
   aid int NOT NULL,
   tid int NOT NULL,
   CONSTRAINT athlete_fk FOREIGN KEY (aid) REFERENCES athlete (id),
   CONSTRAINT item_fk FOREIGN KEY (tid) REFERENCES item (id)
);

3.10.3 E-R模型

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

​ 实体

1、描述客观事物的概念
2、表示方法 ：矩形框
3、示例 ：一个人、一本书、一杯咖啡、一个学生

​ 属性

1、实体具有的某种特性
2、表示方法 ：椭圆形
3、示例
   学生属性 ：学号、姓名、年龄、性别、专业 ... 
   感受属性 ：悲伤、喜悦、刺激、愤怒 ...

​ 关系

1、实体之间的联系
2、一对一关联(1:1)
3、一对多关联(1:n)
4、多对多关联(m:n)

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

3.10.4 表连接

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

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

select  字段1,字段2... from 表1,表2... [where 条件]
e.g.
select * from dept,person where dept.id = person.dept_id;

内连接查询只会查找到符合条件的记录，其实结果和表关联查询是一样的,官方更推荐使用内连接查询。

SELECT 字段列表
    FROM 表1  INNER JOIN  表2
ON 表1.字段 = 表2.字段;
select * from person inner join  dept  on  person.dept_id =dept.id;

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

select * from person inner join dept;
SELECT 字段列表
    FROM 表1  LEFT JOIN  表2
ON 表1.字段 = 表2.字段;
select * from person left join  dept  on  person.dept_id =dept.id;

# 查询每个部门员工人数
select dname,count(name) from dept left join person on dept.id=person.dept_id group by dname;
SELECT 字段列表
    FROM 表1  RIGHT JOIN  表2
ON 表1.字段 = 表2.字段;
select * from person right join  dept  on  person.dept_id =dept.id;

注意：我们尽量使用数据量大的表作为基准表，即左表

3.11 视图

视图是存储的查询语句,当调用的时候,产生结果集,视图充当的是虚拟表的角色。其实视图可以理解为一个表或多个表中导出来的表，作用和真实表一样，包含一系列带有行和列的数据 视图中，用户可以使用SELECT语句查询数据，也可以使用INSERT，UPDATE，DELETE修改记录，视图可以使用户操作方便，并保障数据库系统安全，如果原表改名或者删除则视图也失效。

语法结构：

CREATE [OR REPLACE] VIEW [view_name] AS [SELECT_STATEMENT];

释义：

CREATE VIEW： 创建视图
OR REPLACE : 可选，如果添加原来有同名视图的情况下会覆盖掉原有视图
view_name ： 视图名称
SELECT_STATEMENT ：SELECT语句

e.g.
create view  c1 as select name,age from class_1;

3.12 函数和存储过程

存储过程和函数是事先经过编译并存储在数据库中的一段sql语句集合，调用存储过程和函数可以简化应用开发工作，提高数据处理的效率。

3.12.1 函数创建

delimiter 自定义符号　　-- 如果函数体只有一条语句, begin和end可以省略, 同时delimiter也可以省略

　　create function 函数名(形参列表) returns 返回类型　　-- 注意是retruns

　　begin

　　　　函数体　　　　-- 函数语句集,set @a 定义变量

　　　　return val

　　end  自定义符号

delimiter ;

释义：
delimiter 自定义符号 是为了在函数内些语句方便，制定除了;之外的符号作为函数书写结束标志,一般用$$或者//
形参列表 ： 形参名 类型   类型为mysql支持类型
返回类型:  函数返回的数据类型,mysql支持类型即可
函数体： 若干sql语句组成，如果只有一条语句也可以不写delimiter和begin,end
return: 返回指定类型返回值
e.g. 无参数的函数调用
delimiter $$
create function st() returns int 
begin 
return (select score from class_1 order by score desc limit 1); 
end $$
delimiter ;

select st();
e.g. 含有参数的函数调用
delimiter $$
create function queryNameById(uid int(10)) 
returns varchar(20)
begin
return  (select name from class_1 where id=uid);
end $$
delimiter ;

select queryNameById(1);

3.12.2存储过程创建

创建存储过程语法与创建函数基本相同，但是没有返回值。

delimiter 自定义符号　

　　create procedure 存储过程名(形参列表)

　　begin

　　　　存储过程　　　　-- 存储过程语句集,set @a 定义变量

　　end  自定义符号

delimiter ;

释义：
delimiter 自定义符号 是为了在函数内些语句方便，制定除了;之外的符号作为函数书写结束标志
形参列表 ：[ IN | OUT | INOUT ] 形参名 类型
          in 输入，out  输出，inout 可以输入也可以输出
存储过程： 若干sql语句组成，如果只有一条语句也可以不写delimiter和begin,end

e.g. 存储过程创建和调用
delimiter $$
create procedure st() 
begin 
    select name,age from class_1; 
    select name,score from class_1 order by score desc; 
end $$
delimiter ;

call st();
e.g. : 分别将参数类型改为IN OUT INOUT 看一下结果区别
delimiter $$
create procedure p_out ( OUT num int )
begin
    select num;
    set num=100;
    select num;
end $$

delimiter ;

set @num=10;
call p_out(@num)

3.12.3 存储过程和存储函数操作

  1. 调用存储过程

语法：

call 存储过程名字（[存储过程的参数[,……]])
  1. 调用存储函数

语法：

select 存储函数名字（[存储过程的参数[,……]])
  1. 使用show status语句查看存储过程和函数的信息

语法：

show {procedure|function} status [like’存储过程或存储函数的名称’]

显示内容：数据库、名字、类型、创建者、创建和修改日期

  1. 使用show create语句查看存储过程和函数的定义

语法：

show create  {procedure|function}  存储过程或存储函数的名称

  1. 查看所有函数或者存储过程

    select name from mysql.proc where db='stu' and type='[procedure/function]';

  2. 删除存储过程或存储函数

语法：

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

3.12.4 函数和存储过程区别

  1. 函数有且只有一个返回值，而存储过程不能有返回值。
  2. 函数只能有输入参数，而存储过程可以有in,out,inout多个类型参数。
  3. 存储过程中的语句功能更丰富，实现更复杂的业务逻辑，可以理解为一个按照预定步骤调用的执行过程，而函数中不能展示查询结果集语句，只是完成查询的工作后返回一个结果，功能针对性比较强。
  4. 存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用。

3.13 事务控制

3.13.1 事务概述

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，如果操作就必须同时操作成功，如果有一个不成功则所有数据都不动。这时候数据库操作语句就构成一个事务。事务主要处理数据的增删改操作。

一件事从开始发生到结束的过程

确保数据操作过程中的安全。

3.13.2 事务操作

  1. 开启事务
   mysql>begin; # 方法1
  1. 开始执行事务中的若干条SQL命令（增删改）
  2. 终止事务，若begin之后使用commit提交事务或者使用rollback进行事务回滚。
   mysql>commit; # 事务中SQL命令都执行成功,提交到数据库,结束!
   mysql>rollback; # 有SQL命令执行失败,回滚到初始状态,结束!

注意：事务操作只针对数据操作。rollback不能对数据库，数据表结构操作恢复。

3.13.3 事务四大特性

  1. 原子性（atomicity）

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

  1. 一致性（consistency）

事务完成时，数据必须处于一致状态，数据的完整性约束没有被破坏。

  1. 隔离性（isolation）

数据库允许多个并发事务同时对其数据进行读写和修改的能力，而多个事务相互独立。隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

  1. 持久性（durability）

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

3.13.4 事务隔离级别

事务四大特性中的隔离性是在使用事务时最为需要注意的特性，因为隔离级别不同带来的操作现象也有区别

3.14 数据库优化

3.14.1 数据库设计范式

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式。

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

各种范式呈递次规范，越高的范式数据库冗余越小。但是范式越高也意味着表的划分更细，一个数据库中需要的表也就越多，此时多个表联接在一起的花费是巨大的，尤其是当需要连接的两张或者多张表数据非常庞大的时候，表连接操作几乎是一个噩梦，这严重地降低了系统运行性能。所以通常数据库设计遵循第一第二第三范式，以避免数据操作异常,又不至于表关系过于复杂。

范式简介：

3.14.2 MySQL存储引擎

1、查看所有存储引擎
   mysql> show engines;
2、查看已有表的存储引擎
   mysql> show create table 表名;
3、创建表指定
   create table 表名(...)engine=MyISAM;
4、已有表指定
   alter table 表名 engine=InnoDB;

3.14.3 字段数据类型选择

字符串在查询比较排序时数据处理慢
占用空间少，数据库占磁盘页少，读写处理就更快

3.14.4 键的设置

3.14.5 explain语句

使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。通过explain命令可以得到:

explain select * from class_1 where id <5;

EXPLAIN主要字段解析：

type中包含的值：
- system、const： 可以将查询的变量转为常量. 如id=1; id为 主键或唯一键.
- eq_ref： 访问索引,返回某单一行的数据.(通常在联接时出现，查询使用的索引为主键或唯一键)
- ref： 访问索引,返回某个值的数据.(可以返回多行) 通常使用=时发生 
- range： 这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西，并且该字段上建有索引时发生的情况
- index： 以索引的顺序进行全表扫描，优点是不用排序,缺点是还要全表扫描 
- ALL： 全表扫描，应该尽量避免

3.14.6 SQL优化

3.14.7 表的拆分

垂直拆分 ： 表中列太多，分为多个表，每个表是其中的几个列。将常查询的放到一起，blob或者text类型字段放到另一个表

水平拆分 ： 减少每个表的数据量，通过关键字进行划分然后拆成多个表

3.15 数据库备份和用户管理

3.15.1 表的复制

  1. 表能根据实际需求复制数据
  2. 复制表时不会把KEY属性复制过来

语法

create table 表名 select 查询命令;

3.15.2 数据备份

  1. 备份命令格式

mysqldump -u 用户名 -p 源库名 > ~/stu.sql

  1. 恢复命令格式

mysql -u root -p 目标库名 < stu.sql

3.15.3 用户权限管理

开启MySQL远程连接

更改配置文件，重启服务！
1.cd /etc/mysql/mysql.conf.d
2.sudo vi mysqld.cnf  找到43行左右,加 # 注释
   # bind-address = 127.0.0.1
   
3.保存退出
4.sudo service mysql restart
5.进入mysql修改用户表host值 
  use mysql;
  update user set host='%' where user='root';
6.刷新权限
  flush privileges;

添加授权用户

1. 用root用户登录mysql
   mysql -u root -p
2. 添加用户 % 表示自动选择可用IP
   CREATE USER 'username'@'host' IDENTIFIED BY 'password';
3. 权限管理

   # 增加权限
   grant 权限列表 on 库.表 to "用户名"@"%" identified by "密码" with grant option;
   
   # 删除权限
   revoke insert,update,select on 库.表 from 'user'@'%';
   
4. 刷新权限
   flush privileges;
5. 删除用户
   drop user "用户名"@"%"

权限列表

all privileges 、select 、insert ，update，delete，alter等。
库.表 ： *.* 代表所有库的所有表

示例

1. 创建用户
  mysql>create user  'work'@'%'  identified by '123';
2. 添加授权用户work,密码123,对所有库的所有表有所有权限
  mysql>grant all privileges on *.* to 'work'@'%' identified by '123' with grant option;
  mysql>flush privileges;
3. 添加用户duty，密码123,对books库中所有表有查看，插入权限
  mysql>grant select,insert on books.* to 'duty'@'%' identified by '123' with grant option;
  mysql>flush privileges;
4. 删除work用户的删除权限
  mysql>revoke delete on *.* from "work"@"%";
5. 删除用户duty
  drop user "duty"@"%";

3.16 pymysql模块

pymysql是一个第三方库，如果自己的计算机上没有可以在终端使用命令进行安装。

sudo pip3 install pymysql
  1. 建立数据库连接(db = pymysql.connect(…))
  2. 创建游标对象(cur = db.cursor())
  3. 游标方法: cur.execute(“insert …”)
  4. 提交到数据库或者获取数据 : db.commit()/cur.fetchall()
  5. 关闭游标对象 ：cur.close()
  6. 断开数据库连接 ：db.close()
db = pymysql.connect(参数列表)
功能: 链接数据库

host ：主机地址,本地 localhost
port ：端口号,默认3306
user ：用户名
password ：密码
database ：库
charset ：编码方式,推荐使用 utf8
cur = db.cursor() 
功能： 创建游标
返回值：返回游标对象,用于执行具体SQL命令
cur.execute(sql,list_) 
功能： 执行SQL命令
参数： sql sql语句
      list_  列表，用于给sql语句传递参量
      
cur.executemany(sql命令,list_)
功能： 多次执行SQL命令，执行次数由列表中元组数量决定
参数： sql sql语句
      list_  列表中包含元组 每个元组用于给sql语句传递参量，一般用于写操作。
cur.fetchone() 获取查询结果集的第一条数据，查找到返回一个元组否则返回None
cur.fetchmany(n) 获取前n条查找到的记录，返回结果为元组嵌套元组， ((记录1),(记录2))，查询不到内容返回空元组。
cur.fetchall() 获取所有查找到的记录，返回结果形式同上。
cur.close() 关闭游标对象
db.commit() 提交到数据库执行
db.rollback() 回滚，用于当commit()出错是回复到原来的数据形态
db.close() 关闭连接