第一章

1.1 数据库介绍

数据库的介绍
数据库就是存储和管理数据的仓库，数据按照- -定的格式进行存储，用户可以对数据库中的数据进行增加、修改、删除、查询等操作。
数据库的分类
●关系型数据库
●非关系型数据库
关系型数据库
是指采用了关系模型来组织数据的数据库，简单来说，关系模型指的就是二维表格模型，好比Excel文件中的表格，强调使用表格的方式存储数据。

非关系型数据库
非关系型数据库，又被称为NoSQL (Not Only SQL)，意为不仅仅是SQL，对NoSQL最普遍的定义是“非关联型的”，强调Key-Value的方式存储数据。

1.2 关系型数据库管理系统

关系型数据库管理系统的介绍
数据库管理系统(英语全拼: Relational Database Management System，简称RDBMS)是为管理关系型数据库而设计的软件系统，如果大家想要使用关系型数据库就需要安装数据库管理系统，其实就是一一个应用软件。
关系型数据库管理系统可以分为
●关系型数据库服务端软件
●关系型数据库客户端软件
关系型数据库服务端软件
主要负责管理不同的数据库，而每个数据库里面会有一系列数据文件，数据文件是用来存储数据的,其实数据库就是一系列数据文件的集合。
关系型数据库客户端软件
主要负责和关系型数据库服务端软件进行通信,向服务端传输数据或者从服务端获取数据

说明
1.用户操作关系型数据库客户端，实现数据库相关操作。
2.关系数据库客户端借助网络使用SQL语言和关系型数据库服务端进行数据通信
3.关系型数据库服务端管理着不同的数据库， 每个数据库会有-系列的数据文件，数据都保存在数据文件里面，每个数据库可以理解成是一个文件夹。

SQL的介绍
SQL(Structured Query Language)是结构化查询语言，是一种用来操作RDBMS的数据库的语言。也就是说通过SQL可以操作oracle,sql server,mysql,sqlite 等关系型的数据库SQL的作用是实现数据库客户端和数据库服务端之间的通信，SQL就是通信的桥梁
SQL语言主要分为:
●DQL :数据查询语言，用于对数据进行查询，如select
●DML :数据操作语言，对数据进行增加、修改、删除，如insert、update、delete
●TPL:事务处理语言，对事务进行处理，包括begin transaction、commit、rollback

●DCL :数据控制语言，进行授权与权限回收，如grant、revoke
●DDL:数据定义语言，进行数据库、表的管理等，如create、drop
说明
●对于程序员来讲，重点是数据的增、删、改、查，必须熟练编写DQL、DML，能够编写DDL完成数据库、表的操作，其它操作如TPL、DCL了解即可.
●SQL语言不区分大小写

1.3 MySQL数据库

MySQL数据库的介绍
MySQL是一个关系型数据库管理系统，在WEB应用方面，MySQL是最好的RDBMS (Relational DatabaseManagement System，关系数据库管理系统)应用软件，它是由瑞典MySQL AB公司开发，目前属于Oracle旗下产品,MySQL是最流行的关系型数据库管理系统中的一个。
MySQL的特点

1. MySQL是开源的，所以你不需要支付额外的费用。
2. MySQL支持大型的数据库可以处理拥有，上千万条 记录的大型数据库。
3. MySQL使用标准的SQL数据语言形式。
4. MySQL可以安装在不同的操作系统，并且提供多种编程语言的操作接口。这些编程语言包括C、C++、Python、Java、Ruby等等

MySQL数据库的安装
●MySQL数据库服务端软件的安装
●MySQL数据库客户端软件的安装
MySQL数据库服务端软件的安装
在Ubuntu中打开终端，输入下面的命令:
sudo apt-get install mysql-server

查看MySQL服务状态
sudo service mysql status
停止MySQL服务
sudo service mysql stop
启动MySQL服务
sudo service mysql start
重启MySQL服务
sudo service mysql restart
MySQL配置文件的介绍
配置文件路径为:/etc/mysql/mysql.conf.d/mysqld.cnf

ps说明
●ps查看当前系统中的进程
●-a表示所有用户
●-u表示显示用户名
●-x表示显示所有的执行程序

主要配置信息说明

port表示端口号，默认为3306 
bind-address表示服务器绑定的ip，默认为127.0.0.1
datadir表示数据库保存路径，默认为/var/1ib/mysql
1og_error表示错误日志，默认为/var/1og/mysql/error.1og

1.4 Mysql的基本使用

--登录数据库
mysql -uroot -p

--显示当前时间
select now();

--登出(退出)数据库
exit/quit/ctr+d

--查看所有数据库
show databases;

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

--使用数据库
use python41;

--查看当前使用的数据库
select database();

--删除数据库-慎重
drop database python41;

--查看当前数据库中所有表
show tables;

--创建表
create table students(id int unsigned primary key auto_increment not null, name varchar(10) not null, age tinyint default 0, gender enum("男", "女") default "男");

--修改表-添加birthday字段
alter table students add birthday datetime not null;

--修改表-修改字段类型
alter table students modify birthday date;

--修改表-修改字段名和字段类型
alter table students change birthday birth datetime not null;

--修改表-删除birthday字段
 alter table students drop birth;

--查看表结构
desc students;

--查看创表SQL语句
show create table students;

--查看创库SQL语句
show create database python41;

--删除表
drop table students;

--查询所有列数据
select * from students;

--查询指定列数据
select name, age from students;
--添加数据--全列插入
insert into students values(0, '张三', 18, default);
主键列表插入数据的时候可以指定: 0、default、null
这里的default表示使用该字段默认值


--添加数据--部分列插入
insert into students(name, age) values('郭靖',30);

--添加数据--全列多行插入
insert into students values(0, '黄蓉', 28, '女'),(0,'黄老邪',50,default); 

--添加数据--部分列多行插入
insert into students(name, age) values('杨过', 20),('周伯通',55);

--修改数据
update students set age=18, gender = '女' where id = 3;

--删除数据
delete from students where id = 8;

--删除数据可以使用逻辑删除，添加一个标识字段
alter table students add is_del tinyint default 0;
这里删除数据其实修改标识字段
update students set is_del = 1 where id = 7;

--as关键字，用户给表的字段和表设置别名
select name as n, age as a from students as s;
提示: as 关键字可以省略，也表示设置别名


--distinct关键字， 用于去除重复的数据行
select distinct age, gender from students;

1.5 条件查询、范围查询、模糊查询、排序、分页查询

--查询编号大于3的学生
 select * from students where id > 3;

--查询编号不大于4的学生
select * from students where id <= 4;

--查询姓名不是“黄蓉”的学生
select * from students where name <> '黄蓉';
select * from students where name != '黄蓉';

--查询没被删除的学生
select * from students where is_del = 0;

--查询编号大于3的女同学
select * from students where id > 3 and gender = '女';

--查询编号小于4或没被删除的学生
select * from students where id < 4 or is_del = 0;

--查询年龄不在10岁到15岁之间的学生
select * from students where not (age >= 10 and age <= 15);

--查询姓黄的学生
select * from students where name like '黄%';
--查询姓黄并且“名”是一个字的学生
select * from students where name like '黄_';
select * from students where name like '黄__';
%: 表示任意多个字符
_: 表示任意一个字符

--查询姓黄或叫靖的学生
select * from students where name like '黄%' or name like '%靖';

--查询编号为3至8的学生
select * from students where id >=3 and id <= 8;
select * from students where id between 3 and 8;

--查询编号不是3至8的男生
select * from students where not (id between 3 and 8) and gender='男';

--查询编号是3、5、7的学生
select * from students where id in (3, 5, 7);

--查询编号不是3、5、7的学生
select * from students where id not in (3, 5, 7);


--查询没有填写身高的学生
select * from students where height is null;

--查询填写身高的学生
select * from students where height is  not null;


--查询未删除男生信息，按学号降序
select * from students where is_del = 0 and gender = '男' order by id desc;

--显示所有的学生信息，先按照年龄从大-->小排序，当年龄相同时 按照身高从高-->矮排序
select * from students order by age desc, height desc;
默认是asc 不用指定。

--查询前3行男生信息
select * from students where gender='男' limit 0, 3;
简写方式，第一个参数是开始行索引，默认是0可以不指定， 第二个参数是查询条数
select * from students where gender='男' limit 3;

--查询学生表，获取第n页数据的SQL语句
select * from students limit (n-1) * m, m;

-- 清屏
system clear

1.6 聚合函数、分组查询

聚合函数又叫组函数，通常是对表中的数据进行统计和计算，一般结合分组(group by)来使用，用于统计和计算分组数据
常用的聚合函数

1. count(col): 表示求指定列的总行数
2. max(col): 表示求指定列的最大值
3. min(col): 表示求指定列的最小值
4. sum(col): 表示求指定列的和
5. avg(col): 表示求指定列的平均值

-- 查询学生的个数
select count(height) from students;
注意点: 聚合函数不会对空值进行统计
一般如果要是指定列名，那么就是主键字段
select count(id) from students;
通用的写法
select count(*) from students;

-- 查询女生的编号最大值
select max(id) from students where gender = '女';

-- 查询未删除的学生最小编号
select min(id) from students where is_del = 0;

-- 查询男生的总身高
select sum(height) from students where gender='男';

-- 求男生的平均身高
select sum(height) / count(*) from students where gender='男';
select avg(height) from students where gender = '男';
select avg(ifnull(height, 0)) from students where gender = '男';
注意点: 聚合函数不会对空值进行统计
ifnull函数判断指定的字段是否是空值，如果是空值使用默认值0

-- 查询性别的种类
select distinct gender from students;
select gender from students group by gender;

-- 根据name和gender字段进行分组, 查看name和gender的分组信息
select gender,name from students group by gender,name;

-- 根据gender字段进行分组， 查询每个分组的姓名信息
select gender,group_concat(name) from students group by gender;
group_concat：统计每个分组指定字段的信息集合，信息之间使用逗号进行分割

-- 统计不同性别的平均年龄
select gender,avg(age) from students group by gender;

-- 统计不同性别的人的个数
select gender,count(*) from students group by gender;

-- 根据gender字段进行分组，统计分组条数大于2的
select gender,count(*) from students group by gender having count(*) > 2;

-- 对分组数据进行过滤使用having


-- 根据gender字段进行分组，汇总总人数
select gender,count(*) from students group by gender with rollup;

-- 根据gender字段进行分组，汇总所有人的年龄
select gender,group_concat(age) from students group by gender with rollup;

1.7 连接查询

连接查询的介绍
连接查询可以实现多个表的查询，当查询的字段数据来自不同的表就可以使用连接查询来完成
连接查询可以分为
1.内连接查询:查询两个表中符合条件的共有记录select 字段 from 表1 inner join 表2 on表1.字段1 =表2.字段2,inner join就是内连接查询关键字,on就是连接查询条件

2.左连接查询:以左表为主根据条件查询右表数据，如果根据条件查询右表数据不存在使用null值填充,select 字段 from 表1 left join 表2 on 表1.字段1 =表2.字段2

3.右连接查询:以右表为主根据条件查询左表数据，如果根据条件查询左表数据不存在使用null值填充,select 字段 from 表1 right join 表2 on 表1.字段1 =表2.字段2

4.自连接查询:左表和右表是同一个表，根据连接查询条件查询两个表中的数据。select c.id, c.title, c.pid, p.title from areas as C inner join areas as p on c.pid = p.id where

执行sq|文件给areas表导入数据
source areas.sql;

-- 使用内连接查询学生表与班级表
select s.name, c.name from students s inner join classes c on s.c_id = c.id;

-- 使用左连接查询学生表与班级表
select * from students s left join classes c on s.c_id = c.id;
左连接查询，根据左表查询右表，如果右表数据不存在使用null填充
left左边是左表，left右边是右表


-- 使用右连接查询学生表与班级表
select * from students s right join classes c on s.c_id = c.id;
右连接查询，根据右表查询左表，如果左表数据不存在使用null填充
right左边是左表，right右边是右表

-- 使用自连接查询省份和城市信息
select c.id, c.title, c.pid, p.title from areas c inner join areas p on c.pid = p.id where p.title = '河北省';

1.8 子查询

子查询的介绍
在一个select语句中,嵌入了另外-个select语句,那么被嵌入的select语句称之为要查询语句，外部那个select语句则称为主查询.
主查询和子查询的关系:
1.子查询是嵌入到主查询中
2.子查询是辅助主查询的,要么充当条件,要么充当数据源
3.子查询是可以独立存在的语句,是一 条完整的select语句

-- 查询大于平均年龄的学生
select * from students where age > (select avg(age) from students);

-- 查询学生在班的所有班级名字
select * from classes where id in (select c_id from students where c_id is not null);

-- 查找年龄最大,身高最高的学生
 select * from students where age = (select max(age) from students) and height = (select max(height) from students);

简写:
select * from students where (age, height) = (select max(age), max(height) from students);
子查询是一个完整的查询语句，子查询的执行顺序，先执行子查询然后主查询根据子查询的结果再执行

第二章

2.1 数据库设计之三范式

数据库设计之三范式的介绍
范式:对设计数据库提出的一-些规范，目前有迹可寻的共有8种范式，一般遵守3范式即可。
●第一范式(1NF) :强调的是列的原子性，即列不能够再分成其他几列。
●第二范式(2NF) :满足1NF，另外包含两部分内容，一是表必须有一个主键;二是非主键字段必须完全依赖于主键，而不能只依赖于主键的一部分。
●第三范式(3NF) :满足2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在非主键列A依赖于非主键列B，非主键列B依赖于主键的情况。

E-R模型的介绍
E-R模型即实体关系模型，E-R模型就是描述数据库存储数据的结构模型。
E-R模型的使用场景:
1.对于大型公司开发项目，我们需要根据产品经理的设计，我们先使用建模工具,如:power designer , dbdesinger等这些软件来画出实体-关系模型(E-R模型)
2.然后根据三范式设计数据库表结构

2.2 外键约束SQL语句的编写

外键约束作用
外键约束:对外键字段的值进行更新和插入时会和引用表中字段的数据进行验证，数据如果不合法则更新和插入会失败，保证数据的有效性

为cls_ id字段添加外键约束alter table students add foreign key(cls_id) references classes(id);

-- 为学生版的cls_id字段添加外键约束
alter table students add foreign key(cls_id) references classes(id);

-- 删除外键
alter table 表名 drop foreign key 外键名;

-- 查询类型cate_name为 '超级本' 的商品名称、价格
select name, price from goods where cate_name = '超级本';

-- 显示商品的分类
select distinct cate_name from goods;
select cate_name from goods group by cate_name;

-- 求所有电脑产品的平均价格,并且保留两位小数
select round(avg(price),2) from goods;

-- 显示每种商品的平均价格
select cate_name, avg(price) from goods group by cate_name;

select cate_name, round(avg(price),2) from goods group by cate_name;

-- 查询每种类型的商品中 最贵、最便宜、平均价、数量
select cate_name, max(price), min(price), avg(price), count(*) from goods group by cate_name

-- 查询所有价格大于平均价格的商品，并且按价格降序排序
select * from goods where price > (select avg(price) from goods) order by price desc;

-- 创建商品分类表
create table goods_cates(
    -> id int unsigned not null primary key auto_increment,
    -> name varchar(50) not null);

-- 查询goods表中商品的分类信息
 select cate_name from goods group by cate_name;

-- 将查询结果插入到good_cates表中
 insert into goods_cates(name) select cate_name from goods group by cate_name;

-- 添加移动设备分类信息
insert into goods_cates(name) values('移动设备');

-- 查看goods表中的商品分类名称对应的商品分类id
 select * from goods g inner join goods_cates gs on g.cate_name = gs.name;

-- 将goods表中的分类名称更改成商品分类表中对应的分类id，连接更新表中的某个字段

 update goods g inner join goods_cates gs on g.cate_name = gs.name set g.cate_name = gs.id;

-- 查询品牌信息
select brand_name from goods group by brand_name;

-- 通过create table ...select来创建商品品牌表并且同时插入数据
create table goods_brands( id int unsigned not null primary key auto_increment, name varchar(50) not null) select brand_name  as name from goods group by brand_name;

-- 插入双飞燕品牌
 insert into goods_brands(name) values('双飞燕'); 

-- 查看goods表中的商品品牌对应的商品品牌id
select * from goods g inner join goods_brands gs on g.brand_name = gs.name;

-- 将goods表中的品牌更改成品牌表中对应的品牌id，连接更新表中的某个字段
 update goods g inner join goods_brands gs on g.brand_name = gs.name set g.brand_name = gs.id;

-- 通过alter table语句修改表结构,把cate_name改成cate_id，把brand_name改成brand_id
alter table goods change cate_name cate_id int not null, change brand_name brand_id int not null;

第三章

3.1PyMySQL的使用

创建连接对象

调用pymysql|模块中的connect0函数来创建连接对象,代码如下:

conn=connect(参数列表)
*参数host :连接的mysq1主机，如果本机是'localhost'
*参数port :连接的mysq1主机的端口，默认是3306
*参数user :连接的用户名
*参数password :连接的密码
*参数database :数据库的名称
*参数charset :通信采用的编码方式，推荐使用utf8

# 1.导包
import pymysql

# 2.创建连接对象
conn=pymysql.connect(host='localhost',port=3306,user='root',password='liuhuande123',database='girls',charset='utf8')


# 3.获取游标，目的就是要执行sql语句
cursor=conn.cursor()
# 4.执行sql语句
sql='select * from beauty;'
cursor.execute(sql)
# 查询执行结果 返回一个元组的数据类型
# fetchone()返回一个元组含一条数据，fetchall()返回一个元组含多条数据
row=cursor.fetchone()
row=cursor.fetchall()
print(row)

# 5.关闭游标
cursor.close()
# 6.关闭连接
conn.close()

插入数据

执行完插入语句时，需要将数据提交到数据库conn.commit(),若出现异常则撤销执行conn.rollback

try:
	cursor.excute(sql)
	conn.commit()
except Exception as e:
	conn.rollback

防止SQL注入
什么是SQL注入?
用户提交带有恶意的数据与SQL语句进行字符串方式的拼接， 从而影响了SQL语句的语义，最终产生数据泄露的现象。
如何防止SQL注入?
SQL语句参数化
。SQL语言中的参数使用%s来占位，此处不是python中的字符串格式化操作
。将SQL语句中%s占位所需要的参数存在一个列表中，把参数列表传递给execute方法中第二个参数

sql="select * from beauty where name='%s';" %"黄蓉'or 1=1 or '"
print(sql)

sql="select * from beauty where name=%s;"
cursor.execute(sql,("柳岩",))

3.2 索引

索引的介绍
索引在MySQL中也叫做“键”，它是- -个特殊的文件，它保存着数据表里所有记录的位置信息，更通俗的来说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
应用场景
当数据库中数据量很大时，查找数据会变得很慢，我们就可以通过索引来提高数据库的查询效率
索引的使用
查看表中已有索引:show index from 表名;
说明

●主键列会自动创建索引

索引的创建

--创建索引的语法格式
--alter table 表名 add index 索引名[可选](列名，..)
-- 给name字段添加索引
alter table classes add index my_name (name);

说明
●索引名不指定，默认使用字段名
索引的删除:

  -- 删除索引的语法格式
  -- alter table 表名drop index 索引名
  -- 如果不知道索引名，可以查看创表sq1语句
  show create table classes;
  alter table classes drop index my_ name ;

验证索引性能操作

开启运行时间监测:
set profiing=1; 
--查找第1万条数据ha-99999
select * from test_index where title= 'ha-99999' ;
查看执行的时间:
show profiles;
给title字段创建索引:
alter table test_index add index (title); 
再次执行查询语句
select * from test_ index where title= 'ha-99999' ;
--再次查看执行的时间
show profiles;

联合索引
联合索引又叫复合索引，即一个索引覆盖表中两个或者多个字段，一般用在多个字段一起查询的时候。

-- 创建teacher表
create table teacher(
id int not nu1l primary key auto_increment,
name varchar(10),
age int
);
--创建联合索引
alter table teacher add index (name , age);

联合索引的好处
●减少磁盘空间开销，因为每创建一个索引，其实就是创建了-一个索引文件，那么会增加磁盘空间的开销。

联合索引的最左原则
在使用联合索引的时候，我们要遵守-一个最左原则,即ndex(name,age)支持name、name和age组合查询,而不支持单独age查询，因为没有用到创建的联合索引。
最左原则示例

--下面的查询使用到了联合索引
select * from stu where name= '张三’-- 这里使用了联合索引的name部分
select * from stu where name='李四’ and age=10 -- 这里完整的使用联合索引，包括name 和age部分
--下面的查询没有使用到联合索引
select * from stu where age=10 --因为联合索引里面没有这个组合，只有name | name age这两种组合

说明
●在使用联合索引的查询数据时候一 定要保证联合索引的最左侧字段出现在查询条件里面，否则联合索引失效

3.3 事务

事务的介绍
事务就是用户定义的一系列执行SQL语句的操作,这些操作要么完全地执行， 要么完全地都不执行，它是一个不可分割的工作执行单元。
事务的使用场景
在日常生活中，有时我们需要进行银行转账王这个银行转账操作背后就是需要执行多个SQL语句，假如这些SQL执行到一半突然停电了，那么就会导致这个功能只完成了一半，这种情况是不允许出现，要想解决这个问题就需要通过事务来完成。
事务的四大特性
●原子性(Atomicity)
●一致性(Consistency)
●隔离性(lsolation)
●持久性(Durability)
原子性
一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性

一致性
数据库总是从一个一致性的状态转换到另一个一致性的状态。(在前面的例子中，一致性确保了，即使在转账过程中系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中)
隔离性
通常来说，一个事务所做的修改操作在提交事务之前，对于其他事务来说是不可见的。(在 前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外的一个账户汇总程序开始运行，则其看到支票帐户的余额并没有被减去200美元。)
持久性
一旦事务提交，则其所做的修改会永久保存到数据库

事务的使用
在使用事务之前，先要确保表的存储引擎是InnoDB类型,只有这个类型才可以使用事务，MySQL数据库中表的存储引擎默认是InnoDB类型。

表的存储引擎说明

表的存储引擎就是提供存储数据-种机制，不同表的存储引擎提供不同的存储机制●

查看Mysql数据库支持的存储引擎

show engines

说明:
●常用的表的存储引擎是InnoDB和MyISAM
●InnoDB 是支持事务的
●MyISAM不支持事务，优势是访问速度快，对事务没有要求或者以select、insert为主的都可以使用该存储引擎来创建表

修改存储引擎：alter table students engine = 'MyISAM';

开启事务：begin;、start transaction;

说明:
●开启事务后执行修改命令，变更数据会保存到MySQL服务端的缓存文件中，而不维护到物理表中
●MySQL数据库默认采用自动提交(autocommit)模式，如果没有显示的开启-一个事务,那么每条sq|语句都会被当作一个事务执行提交的操作
●当设置autocommit=0就是取消了自动提交事务模式，直到显示的执行commit和rollback表示该事务结束。set autocommit= 0表示取消自动提交事务模式，需要手动执行commit完成事务的提交