数据库学习以及PyMSQL使用

一、数据库介绍

1. 定义

数据库就是一些具有特殊格式的数据文件的集合，比如网页商品信息，图书信息，学校学生信息等等。

2.分类

(1)关系型数据库

即数据之间是有关系的，比如学生管理系统中的学生信息，有姓名，性别等等信息。关系型数据库一般为二维表格。

常见数据库：MySQL、Oracle、SQL server等等

(2)非关系型数据库

又称NoSQL(Not Only SQL)，数据是非关联型的，强调key+value存储。

以下主要是关系型数据库

3.数据库管理系统

数据库管理系统（DBMS）是管理数据库的系统。

组成：

• 数据库文件集合：存储的数据
• 数据库服务器：对数据文件和文件中的数据进行管理
• 数据库客户端：和服务端进行通信，向服务器传输数据或者从服务端接收数据

4.SQL语言

**SQL：**结构化查询语言，数据库客户端是使用SQL语言去和服务器进行通信的。注意：SQL语言是不区分大小写的，并且以";"结束

5.关系型数据库数据表

学号	姓名	性别	班级	联系电话
1	张三	男	1	18500000000
2	李四	男	2	13500000000

每列称为一个字段，并且数据类型相同

每行称为一个记录，记录一个事物的完整信息

数据表中有一列（如学号），是主键，唯一标识一行数据的

若干个字段和记录组成数据表，若干数据表组成数据库

6.MYSQL

(1)服务端：

Ubuntu中：

sudo apt-get install mysql-server #安装服务端
sudo service mysqL start 		  #启动 mysql 服务
sudo service mysql stop   		  #停止 mysql 服务
sudo service mysql restart		  #重启 mysql 服务
ps ajx|grep mysql    #命令查看 MySQL 数据库启动状态。
#ps: 查看当前系统进程 -a 显示所有用户进程 -j 任务格式显示进程 -x显示无控制终端进程

Windows中：

1. 登录官网https://www.mysql.com/
2. 选择导航栏中的DOWNLOADS
3. 向下滑动页面，点击MySQL Community (GPL) Downloads »
4. 选择版本，推荐选择MySQL Installer for Windows
5. 选择要下载的点击下载
6. 这时会进入页面让你登录或者注册，不想操作的可以直接点击**No thanks, just start my download.**
7. 下载后直接运行安装即可

(2)客户端：（Ubuntu中）

sudo apt-get install mysql-client #安装服务端
mysql -u用户名 -p密码    		  #连接 mysql 服务端，或-p回车后再输入密码也可以
exit 或 quit  #退出链接

(3)配置文件

/etc/mysql/mysql.conf.d/mysql.cnf

bind-address 表示服务器绑定ip，默认为127.0.0.1
port 表示端口号，默认3306
datadir 表示数据库目录，默认/var/log/mysql/mysql.log
log_error表示错误日志，默认为/var/log/mysql/error.log

7.客户端Navicat的使用

Navicat可以方便完成 MySQL 的管理任务。Navicat 是以
直觉化的图形用户界面而建的, 让你可以以安全并且简单的方式对数据库进行操作。

(1)创建数据库在左侧空白栏右击鼠标，选择“新建数据库”即可创建

(2)删除数据库左侧栏右键数据库，点击删除

(3)修改数据库右键数据库，选择“编辑数据库”即可

(4)创建数据表点击工具栏中的“表”再点击“新建表”；然后输入表的详细信息

(5)删除数据表数据点击需要删除的单元格，再点击底部“-”

(6)修改数据表数据点击需要修改的单元格即可修改，修改完成点击底部“√”

8.SQL数据类型

常用：int（整型）、float\double（浮点型）、char\varchar（字符串）、text（存放文字）、enum（枚举型gender enum{‘man’,‘woman’}）、date（年-月-日）、datetime（年-月-日 时:分:秒）、time（时:分:秒）、year（年）、decimal（定点数，decimal(3,1)表示3位数字，其中1位是小数）

char和varchar：char为定长字符串，创建表时就已经固定其大小，而varchar为变长字符串，创建表时大小不固定，是按照其内容大小而决定的，等于内容长度+1（因为最后有"\0"结尾），所以使用时尽量用varchar

超过255字节后只能用varchar和text，但是能用varchar的地方不用text

9.约束性设置

约束类型	说明
not null	非空约束（非空性）
primary key	主键约束（唯一性、非空性）
unique key	唯一约束（唯一性）
default	默认约束（该数据的默认值）
foreign key	外键约束（建立与其他表的联系）

二、数据库基本操作

1.登录退出

SQL语句	说明
mysql -u用户名 -p密码	连接数据库
exit或quit或ctrl+d	退出数据库
select version();	显示版本
select now();	显示时间

2.数据库操作

SQL语句	说明
show databases;	查看所有数据库
select database();	查看当前数据库
create database 数据库名 charset=utf-8;	创建数据库并设置编码为utf-8
use 数据库名;	使用数据库
drop database 数据库名;	删除数据库

3.数据表操作

SQL语句	说明
create table 表明(字段1 数据类型1 约束1, 字段2 数据类型2 约束2,…);	创建数据表
show tables;	查看所有表
desc 表名;	查看表结构
show create table 表名;	使用数据库
drop table 表名;	删除表

4.表结构（字段）修改

SQL语句	说明
alter table 表名 add 列名 类型及约束;	添加字段
alter table 表名 change 原名 新名 类型及约束;	重命名字段
alter table 表名 modify 列名 类型及约束;	修改字段类型
alter table 表名 drop 列明;	删除字段

5.表数据操作

SQL语句	说明
insert into 表名 values (…);	全列插入：值的顺序与表结构字段顺序对应
insert into 表名 (列1,…) values (值1,…);	部分列插入：值的顺序与给出列顺序对应
insert into 表名 values (…),(…),…;	一次性插入多行数据
insert into 表名 (列1,…) values (值1,…),(…),…;	部分列插入多行数据
select * from 表名;	查询所有列数据
select 列1，列2，… from 表名;	查询指定列数据
update 表名 set 列1=值1,列2=值2,… where 条件;	修改数据
delete from 表名 where 条件;	删除数据

注意：insert不支持where，想要部分列的部分记录添加数据，使用updata

6.as关键字——起别名

我们可以通过as关键字给字段或者表起别名，当我们设计数据库时，字段和表名一般都会使用英文简写，为了输出到屏幕上为了更清楚，可以使用as关键字：

# 给students表的字段起别名
select name as 名字,gender as 性别 from students;
# 给表起别名
select s.name from students as s;

7.distinct关键字——排除重复数据行

比如想要从教师表中查看都有哪些科目的老师，因为有科目相同的老师，所以可以使用distinct关键字进行排除重复数据行

select distinct course from teachers;

8.外键约束

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

• 添加外键约束:

alter table 从表 add foreign key(外键字段) references 主表(主键字段);

• 删除外键约束:

alter table 表名 drop foreign key 外键名;

在创建数据表时设置外键约束:

create table teacher(
    id int not null primary key auto_increment, 
    name varchar(10), 
    class_id int not null, 
    foreign key(class_id) references class(id)
);

三、where条件查询

当我们不需要查看所有数据时，需要使用where条件查询进行筛选。

select * from 表名 where 条件;

where语句支持的运算符:

1.比较运算符

select * from students where age = 15;

注意：

• 在SQL中等于是=，并不是==

• 不等于!=还可以用<>表示

2.逻辑运算符 — and/or/not

select * from students where (age > 15 and gender = 0) or teacher='张老师';

可以使用括号来进行分组判断

3.模糊查询 — like模糊查询/%任意多字符/_一个任意字符

# 查询姓郭的老师
select * from teachers where name like "郭%";
# 查询姓郭并且名字只有两个字的老师
select * from teachers where name like "郭_";

4.范围查询 —between...and.../in

# 查询分数在60-70之间的学生
select * from students where score between 60 and 70;
# 查询郭老师、张老师、王老师的学生
select * from students where teacher in("郭老师","张老师","王老师");

5.空判断—is null/is not null

# 查询没有分配到老师的学生
select * from students where teacher is null;

注意：NULL并不等于空字符串，并不能使用teacher=null来判定

6.排序—order by

排序需要使用order by关键字，并且参数asc表示升序，desc表示降序，默认按照升序排序

# 按照学生分数进行排序，并且分数相同，按数学分数升序排序
select * from students where order by score asc, math asc;
# 按照学生分数进行排序，并且分数相同，按数学分数降序排序
select * from students where order by score asc, math desc;

7.分页查询—limit

当数据太多，为了更方便显示，可以使用分页查询对所查数据查看。

select * from 表名 limit start,count;

• limit是分页查询的关键字
• start表示开始行索引，默认是0，即第一个数据行数为0
• count表示查询条数，即一页显示多少行数据

# 查询前三行男生学生信息
select * from students where gender=1 limit 0,3;  #也可以直接limit 3
# 每页显示5行数据，获取第2页数据
select * from students limit 5,5; #(2-1)*5,5，即第二页第一个为5，因为从0开始
# 若每页显示m条数据，第n页数据为：limit (n-1)*m,m
# 即开始索引行为第(n-1)*m

四、聚合函数

聚合函数又称组函数，对表中的数据进行统计和计算。

常用的聚合函数:

1.count(字段名): 表示求指定列的总行数

参数为*表示含NULL数据，非*表示该字段中非NULL值的记录

# 计算name非NULL的数据的行数
select count(name)from students;
# 返回总行数（含NULL值记录）
select count(*)from students;

2.max(字段名): 表示求指定列的最大值

# 计算score字段中的最大值
select max(score)from students;

3.min(字段名): 表示求指定列的最小值

# 计算score字段中的最小值
select min(score)from students;

4.sum(字段名): 表示求指定列的和

# 计算score字段的总和
select sum(score)from students;

5.avg(字段名): 表示求指定列的平均值

# 计算score字段的平均值
select avg(score)from students;

注意：聚合函数默认忽略字段为null的记录 要想列值为null的记录也参与计算，必须使用ifnull函数对null值做替换

# 计算score字段的平均值,使用ifnull函数后，其中的NULL值会默认为0
select avg(ifnull(score,0)from students;

五、特殊查询

1.分组查询

分组查询就是将查询结果按照指定字段进行分组，数据相同的为一组

group by 字段名[having 条件表达式][with rollup]

• 列名: 是指按照指定字段的值进行分组。
• having 条件表达式: 用来过滤分组后的数据。
• with rollup：在所有记录的最后加上一条记录，显示select查询时聚合函数的统计和计算结果

(1)group by

group by 可用于单个字段分组，也可用于多个字段分组

# 根据字段1,字段2,...进行分组
select 字段1,字段2,... from 表名 group by 字段1,字段2,...;

(2)group by + group_concat()

group_concat(字段名): 统计每个分组指定字段的信息集合，每个信息之间使用逗号进行分割

# 根据字段1进行分组，查询字段1和分组的字段2字段信息
select 字段1,group_concat(字段2) from 表名 group by 字段1;

(3)group by + 聚合函数

# 先通过字段1进行分组，然后对每一分组执行聚合函数操作
select 字段1,聚合函数(字段2) from 表名 group by 字段1;

(4)group by + having

having作用和where类似都是过滤数据的，但having是过滤分组数据的，只能用于group by

# 先通过字段1进行分组，然后对每一分组执行having筛选
select 字段1 from 表名 group by 字段1或字段2 having 筛选条件;
# eg:根据gender字段进行分组，统计分组条数大于2的
select gender,count(*) from students group by gender having count(*)>2;

(5)group by + with rollup的使用

with rollup的作用是：在最后记录后面新增一行，显示select查询时聚合函数的统计和计算结果

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

执行顺序：group_concat()/聚合函数>group by>having>with rollup

6.连接查询

连接查询可以连接多个表进行查询，查询来自不同表的字段数据

(1)内连接inner join on

查询两个表中符合条件的共同记录，即找两表交集

select 字段 from 表1 inner join 表2 on 表1.字段1=表2.字段2;
# eg:查询学校所有男学生和男老师
select * from students as s inner join teachers as t on s.gender=t.gender;

• inner join 就是内连接查询关键字
• on 就是连接查询条件

(2)左连接lefft join on

以左表为主根据条件查询右表，若根据条件查询右表数据不存在则使用null填充

select 字段 from 表1 left join 表2 on 表1.字段1=表2.字段2;

(3)右链接right join on

以右表为主根据条件查询左表，若根据条件查询左表数据不存在则使用null填充

select 字段 from 表1 right join 表2 on 表1.字段1=表2.字段2;

(4)自身连接inner join on

• 自身连接查询就是把一张表模拟成左右两张表，然后进行连表查询。
• 自身连接就是一种特殊的内连接方式，连接的表还是本身这张表
• 自身连接查询必须对表起别名，即对一个表起两个别名进行操作

select c.id, c.title, c.pid, p.title from areas as c inner join areas as p on c.pid = p.id where p.title = '山西省';

7.子查询

一个select语句中嵌套另一个select语句，那么就称嵌套的为子查询语句，外部的select为主查询语句

主查询和子查询的关系:

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

# eg:查询大于平均年龄的学生：
select * from students where age > (select avg(age) from students);
# eg:查找年龄最大,身高最高的学生:
select * from students where (age, height) =  (select max(age), max(height) from students);

六、数据库设计

1.三范式

1. 1NF：强调的是列的原子性，即列不能够再分成其他几列。
2. 2NF：满足 1NF，另外包含两部分内容，一是表必须有一个主键；二是非主键字段必须完全依赖于主键，而不能只依赖于主键的一部分。
3. 3NF：满足 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

2.E-R模型

E-R模型（实体-关系模型），就是描述数据库存储数据的结构模型。

• 实体: 矩形
• 属性: 椭圆
• 关系: 菱形，并在菱形两边写入关系是几对几
  • 一对一
  • 一对多
  • 多对多

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MkCVBRFP-1651593171079)(D:\文档表格\17.人工智能\1.笔记\image\图片1.png)]

七、PyMySQL

1.事务

(1)定义：

事务就是用户定义的一系列执行SQL语句的操作, 它是一个不可分割的工作执行单元。事务能够保证数据的完整性和一致性，让用户的操作更加安全。(比如银行转账操作)

(2)四大特性：

• 原子性(Atomicity)：不可分割
• 一致性(Consistency)：一个事务改变，那么其对应的对方的事务也要同时改变
• 隔离性(Isolation)：一个事务所做的修改操作在提交事务之前，对于其他事务来说是不可见的。
• 持久性(Durability)：数据会一直在数据库中

(3)事务的使用

表的存储引擎说明:

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

查看MySQL数据库支持的表的存储引擎:

-- 查看MySQL数据库支持的表的存储引擎
show engines;

说明:

• 常用的表的存储引擎是 InnoDB 和 MyISAM
• InnoDB 是支持事务的
• MyISAM 不支持事务，优势是访问速度快，对事务没有要求或者以select、insert为主的都可以使用该存储引擎来创建表
• 修改表的存储引擎使用: alter table 表名 engine = 引擎类型;
• 开启事务使用begin或者 start transaction;
• 回滚事务使用 rollback;
• PyMySQL 里面的 conn.commit() 操作就是提交事务
• PyMySQL 里面的 conn.rollback() 操作就是回滚事务

2.索引

(1)定义

索引在MySQL中也叫做“键”，它是一个特殊的文件，它保存着数据表里所有记录的位置信息，更通俗的来说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

(2)MySQL中索引的优点和缺点和使用原则

• 优点：

  加快数据的查询速度

• 缺点：

  创建索引会耗费时间和占用磁盘空间

• 使用原则：

  1. 通过优缺点对比，不是索引越多越好，而是需要自己合理的使用。
  2. 对经常更新的表就避免对其进行过多索引的创建，对经常用于查询的字段应该创建索引，
  3. 数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。
  4. 在一字段上相同值比较多不要建立索引，比如在学生表的"性别"字段上只有男，女两个不同值。相反的，在一个字段上不同值较多可是建立索引。

(3)索引的使用

# 查看表中已有索引,主键列会自动创建索引
show index from 表名;
# 创建索引，索引名不指定，默认使用字段名
alter table 表名 add index 索引名[可选](列名, ..);
# 删除索引的语法格式，如果不知道索引名，可以查看show create table 表名;
alter table 表名 drop index 索引名;

3.PyMySQL安装

我们可以使用 PyMySQL去连接数据库

# 安装pymysql:
sudo pip3 install pymysql
# 卸载pymysql:
sudo pip3 uninstall pymysql
# 查询信息
pip3 show pymysql

4.连接数据库

(1)导入 pymysql 包

import pymysql

(2)创建连接对象—connect()函数

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

(3)获取游标对象

获取游标对象的目标就是要执行sql语句，完成对数据库的增、删、改、查操作。代码如下:

 # 调用连接对象的cursor()方法获取游标对象   
 cursor=conn.cursor()

(4)执行SQL语句,，返回值就是SQL语句在执行过程中影响的行数

count=cursor.execute("SQL语句")

注：

python中添加字符串时并不能直接%s，因为SQL语句中字符串需要引号引起来，所以：

# 错：
sql = "insert into student_table (name,gender,class) value (%s,%s,%s);"%(name, gender, int(No_class))
cursor.execute(sql)
# 对：
sql = "insert into student_table (name,gender,class) value ('%s','%s','%s');"%(name, gender, int(No_class))
cursor.execute(sql)

(5)取出数据

cursor.fetchone()：获取查询结果集中的一条数据

cursor.fetchall()获取查询结果集中的所有数据

返回的数据是按元组保存的，比如(1,“张三”,“男”)

# 取出结果集中一行数据
result=cursor.fetchone()
# 取出结果集中的所有数据
result=cursor.fetchall()

(6)将修改操作提交到数据库

执行完修改操作后将数据提交到数据库

# 提交数据到数据库
conn.commit()

(7)回滚数据

以防执行SQL的代码有误，可以使用try...except进行判断，若出错则回滚数据，即撤销刚刚的SQL语句操作

try:
    # 执行SQL语句
    # 执行相关语句
    # 提交数据到数据库
except Exception as e:
    # 回滚数据，撤销操作
    conn.rollback()

(8)关闭游标

cursor.close()

(9)关闭连接

conn.close()

ble (name,gender,class) value (‘%s’,‘%s’,‘%s’);"%(name, gender, int(No_class))

cursor.execute(sql)

(5)取出数据

cursor.fetchone()：获取查询结果集中的一条数据

cursor.fetchall()获取查询结果集中的所有数据

返回的数据是按元组保存的，比如(1,“张三”,“男”)

# 取出结果集中一行数据
result=cursor.fetchone()
# 取出结果集中的所有数据
result=cursor.fetchall()

(6)将修改操作提交到数据库

执行完修改操作后将数据提交到数据库

# 提交数据到数据库
conn.commit()

(7)回滚数据

以防执行SQL的代码有误，可以使用try...except进行判断，若出错则回滚数据，即撤销刚刚的SQL语句操作

try:
    # 执行SQL语句
    # 执行相关语句
    # 提交数据到数据库
except Exception as e:
    # 回滚数据，撤销操作
    conn.rollback()

(8)关闭游标

cursor.close()

(9)关闭连接

conn.close()