Python学习之路 | 数据分析篇【2】

数据库操作

数据库（database），简称DB，实际上就是一个文件集合，是一个存储数据的仓库，本质就是一个文件系统，数据是按照特定的格式把数据存储起来，用户可以对存储的数据进行增删改查等操作

特点：

• 持久化存储
• 读写速度极高
• 保证数据的有效性（正整数）
• 对程序支持非常好，容易扩展

数据库分类：

• 关系型数据库：可以保存现实生活中的各种关系数据，数据库中存储数据以表为单位；主流库有MySQL，Oracle，SqlServer
• 非关系型数据库：通常用来结局某些特定的需求，比如高并发访问。非主流库有Redis，Mongodb，memacache等

MySQL

MySQL是一种开放源代码的关系型数据库管理系统（RDBMS），使用最常用的数据库管理语言——结构化查询语言（SQL）进行数据库管理。MySQL因为其速度，可靠性和适应性而备受关注。大多数人都认为在不需要事务化处理的情况下，MySQL是管理内容最好的选择

图形界面管理工具

常用工具：phpMyAdmin，Navicat，SQLyog

SQLyog介绍:
SQLyog是一个快速而简洁的图形化管理MYSQL数据库的工具，它能够在任何地点有效地管理数据库，也是一个开源软件
开源软件（open source software），简称为OSS，公开源代码的软件。因此开源软件具备可以免费试用和公布源代码的特征
注意：SQLyog只是一个连接数据库的工具，并不是数据库

数据库：Excel文件（工作簿）
数据表：Excel里边的表（工作表）
列：字段
主键（PK）：不重复，标识唯一的字段

SQL语句及数据类型

SQL是结构化查询语言，是一种用来操作RDBMS（关系型数据库管理系统）的数据库语言，当前关系型数据库都支持使用SQL语言进行操作，也就是说可以通过SQL操作Oracle，sql，server，mysql等关系型数据库

SQL语句主要分为：

• DQL：数据查询语言，用于对数据进行查询
• DML：数据操作语言，对数据进行增加，修改，删除
• DDL：数据定义语言，进行数据库、表的管理等

数据完整性：
在表中为了更加准确的存储数据，保证数据的正确有效，可以在创建表的时候，为表添加一些强制性的验证，包括数据字段的类型、约束。

数据类型

常见数据类型：

• 整数：int
• 小数：decimal
• 字符串：varchar，char
• 日期时间：date、time、datetime
• 枚举类：enum（有下拉框选项）
  Number类型：
  数据类型 有符号范围 无符号范围
  TINYINT(size） -128~127 0 ~255
  SMALLINT(size）-32668 ~32767 0 ~65535
  MEDIMINT(size）
  INT(size）

常用TINYINT和INT

FLOAT(size,d)，带有浮动小数点的小数字，在括号中规定最大位数，在d参数中规定小数点（size表示这个数字一共有几位，d表示其中小数占了几位）
DOUBLE(size,d)带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。

CHAR 0-255字节
VARCHAR 0-255字节
TEXT 0-65535字节 大文本，一般超过四千才用它

注意：
• decimal表示浮点数，如decimal(5,2)表示共存5位数，小数占2位
• char表示固定长度的字符串，如char(3)，如果填充’ab’时会补一个空格为’ab ’
• varchar表示可变长度的字符串，如varchar(3)，填充’ab’时就会存储’ab’
• 字符串text表示存储大文本，当字符大于4000时推荐使用
• 对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径
约束：
• 主键primary key：物理上存储的顺序
• 非空not null：此字段不允许填写空值
• 惟一unique：此字段的值不允许重复(身份证号,银行卡号等)
• 默认default：当不填写此值时会使用默认值，如果填写时以填写为准
• 外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常

SQL语法

操作数据库（DDL）

创建数据库：

CREATE DATABASE 数据库名； #一定要以英文分号;结尾
CREATE DATABASE 数据库名 charset=utf-8;

查看创建数据库的命令

SHOW CREATE DATABASE 数据库名;

查看已经创建的数据库

SHOW DATABASES;

查看当前使用的数据库

SELECT DATABASE;

使用数据库

USE 数据库名;

删除数据库

DROP DATABASE 数据库名;

注意
• SQL语句不区分大小写
• 每行必须以英文状态下分号作为结束
• 当数据库名称有特殊字符时，使用``包括。如：‘student’
SQLyog用 – 注释
选中代码，按F9运行，F5刷新
新建表内容，最后一行不能加逗号

操作数据表

查看当前数据库中所有表

SHOW TABLES;

创建表

CREATE TABLE 数据表名(字段 类型 约束[, 字段 类型 约束])

查看表的创建语句

SHOW CREATE TABLE 数据表名;

查看表描述信息

DESC 数据表名;

添加表字段

ALTER TABLE 数据表名 ADD 字段 类型;

修改表字段

ALTER TABLE 数据表名 MODIFY  字段 类型;                           -- 不重命名
ALTER TABLE 数据表名 CHANGE 原字段名 新字段名 类型及约束;    -- 将字段重命名

删除表字段

ALTER TABLE 数据表名 DROP 字段;

操作数据

新增数据

整行插入

INSERT INTO 数据表名 VALUES(值1,值2,值3...);

指定列中插入数据

INSERT INTO tabname (字段1, 字段2,...) VALUES (值1,值2,....);

指定列中插入多条数据

INSERT INTO 数据表名 (字段1, 字段2,...) VALUES (值1,值2,...),(值1,值2,...);

修改数据

UPDATE 数据表名 SET 字段1=新值，字段2=新值 [WHERE 条件];

删除数据

DELETE FROM 数据表名 [WHERE 条件];

查询数据

查询整个表数据

SELECT * FROM 数据表名;

查询指定字段数据

SELECT 字段1,字段2 FROM 数据表名;

查询指定字段数据，并给字段起别名

SELECT 字段1 as 别名,字段2 as 别名 FROM 数据表名;

查询指定字段并去重

SELECT DISTINCT 字段1 FROM 数据表名;

where子句
where子句通常结合增删改查使用，用于做筛选的条件。
比如，查询当id=1的数据

select * from Student where id=1

经常结合运算符使用，同pycharm运算符

模糊查询
like关键字用来进行模糊查询，并且结合%以及_使用。
• % 表示任意多个任意字符
• _ 表示一个任意字符

范围查询
关键字in，表示在一个非连续的范围内
关键字between…and…，表示在一个连续的范围内

空判断
关键字is null，判断空
关键字is not null，判断非空

Python操作MySQL

操作步骤

开始→创建connection→获取cursor→执行查询，执行命令，获取数据，处理数据→关闭cursor→关闭connection→结束

connection对象，用于建立与数据库的连接
pymysql.connect(host=None, port=0, database=None, user=None, password="",charset=’’)
• host：连接数据库地址 # ‘local host’
• port：连接端口 # 3306（int）
• database：连接数据库 # student
• user：连接用户名 # ‘root’
• password：连接密码 # ‘root’
• charset：字符集编码 #‘utf-8’

实际上，我们可以通过conn这个对象进行关闭连接，提交等。具体方法如下
• conn.close() 关闭连接
• conn.commit() 提交
• conn.cursor() 返回cursor对象,用于执行sql语句并获得结果

cursor对象
conn.cursor()从连接对象中获取游标对象

cs = conn.cursor()

实际上，我们可以通过cs这个对象关闭游标，执行sql语句等。具体方法如下
• cs.close() 关闭游标
• cs.execute(operation [, parameters ]) 执行语句，返回受影响的行数
• cs.fetchone() 执行查询语句时，获取结果集第一行数据，且返回元组
• fetchall() 执行查询语句时，获取结果集的所有行，且为二维元组

查询数据：

import pymysql

conn = pymysql.connect(host="localhost", port=3306, database="Logic_edu", user="root", password="root", charset="utf8")
cs = conn.cursor() 

res = cs.execute("select * from student")
# res1 = cs.fetchall()    # 获取所有
# res2 = cs.fetchone()    # 获取一行
res3 = cs.fetchmany(2)    # 获取多行

cursor.close()               
conn.close()

新增数据：

import pymysql

conn = pymysql.connect(host="localhost",port=3306,database="logic_edu",user="root",password="root",charset="utf8")

sql = 'insert into student (name) values("张三")'   
cursors = conn.cursor()
res = cursors.execute(sql)

conn.commit()  

修改数据：

import pymysql

conn = pymysql.connect(host="localhost",port=3306,database="logic_edu",user="root",password="root",charset="utf8")

sql = 'update student set name = "老王" where id = 1'                   
cursors = conn.cursor()
cursors.execute(sql)

conn.commit()

删除数据：

import pymysql

conn = pymysql.connect(host="localhost",port=3306,database="logic_edu",user="root",password="root",charset="utf8")

sql = 'delete from student where id=1'
cursors = conn.cursor()
cursors.execute(sql)

conn.commit()