MySQL基本概念及PyMySQL *** 作

一、数据库概念及其分类

数据库：存储数据的软件，长期存储在计算机内，有组织的数据集合。

数据表：表是数据库存储数据的基本单位，数据按照分类存储到不同的表中，能够非常高效的的查询其中数据

数据库分类：

1.关系型数据库:

以数据表为核心，将数据的关系用数据库表的形式表达，并将数据存储在表格中，以便查询

数据库	应用	注：关系型数据库核心元素
Oracle	大型项目中使用，例如银行、电信等项目;	·数据行（一条记录） ·数据列（字段） ·数据表（数据行的集合） ·数据库（数据表的集合，一个数据库中 能够有n多个数据表
MySQL	web项目中使用最广泛的关系型数据库;
Microsoft SQL Server	微软项目中使用
SQlite	轻量级数据库，主要应用于移动平台

2.非关系型数据库：

不存在数据表的概念，将数据以Key，value、文本、图片等形式存储数据构成；

·Redis

·MongoDB

二、SQL:Structured Query Lanaguage结构化查询语言;

SQL语言分类：

类型	应用	案例
DQL	数据查询语言，用于对数据查询	select
DML	数据 *** 作语音，对数据进行增加、修改、删除	insert、update、delete
DCL	数据控制语音，进行授权与权限收回	grant，revoke
DDL	数据定义语言，进行数据库、表的管理等	create、drop
注意	在MySQL中，默认对SQL语法不区分大小写；SQL语言默认支持 *** 作所有的关系型数据库；

三、SQL语句常用数据类型与约束

常用数据类型：

整数：int  有符号范围；unsigned 无符号范围；

小数：decimal，例如decimal（5,2）表示共存5位小数，小数占2位，整数占3位；

字符串：varchar，范围（0~65533），例如：varchar（3）表示最多存3个字符,一个中文或一个字母都占一个字符。

日期时间：datetime，范围（1000-01-01  00:00:00~9999-12-31   23:59:59），例如：'2020-01-01  12:29:59'

约束：

约束	释意
主键（primary key）	能够唯一标志表中每一条记录的属性组
非空（notnull）	此字段不允许填写空值
唯一（unique）	此字段不允许重复
默认值	如果不填写此值会使用默认值，如果填写则以填写为准
外键	一个表中的一个字段引用另一个表的主键

四、python *** 作数据库的方式

pymysql：纯python开发，支持python2与python3，简单易用；

pymysql 的安装与验证：

安装：pip install PyMsql

验证：pip show PyMySQL 

python *** 作数据库的流程：

1.创建连接

2.获取游标对象

游标：游标是sql的一种数据访问机制，是结果集的一个指针，其查询的结果都是在上一条结果的基础上产生的，而不是原表格。

3.执行SQL语句

        查询 *** 作（select）

        非查询 *** 作（insert/delete/update)

                ·事务提交（连接对象.commit()) 

                ·数据回滚(连接对象.rollback())

4.关闭游标cursor

5.关闭连接

案例背景：数据库为books，库里有两张表t_book和t_hero

CREATE DATABASE if not EXISTS books DEFAULT charset utf8;
use books;

Drop TABLE if EXISTS t_book;

CREATE TABLE `t_book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(20) NOT NULL COMMENT '图书名称',
  `pub_date` date NOT NULL COMMENT '发布日期',
  `read` int(11) NOT NULL DEFAULT '0' COMMENT '阅读量',
  `comment` int(11) NOT NULL DEFAULT '0' COMMENT '评论量',
  `is_delete` tinyint(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='图书表';

INSERT INTO `t_book` VALUES('1','射雕英雄传','1980-05-01','12','34','0');
INSERT INTO `t_book` VALUES('2','天龙八部','1986-07-24','36','40','0');
INSERT INTO `t_book` VALUES('3','笑傲江湖','1995-12-24','20','80','0');


DROP TABLE IF EXISTS `t_hero`;
CREATE TABLE `t_hero`(
	`id` int(11) not null auto_increment,
	`name` VARCHAR(20) not null comment '姓名',
	`gender`SMALLINT(6) not null COMMENT '性别',
	`description`VARCHAR(200) DEFAULT NULL COMMENT '描述',
	`is_delete`TINYINT(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除',
	`book_id`INT(11) not null comment'所属图书id',
	PRIMARY key(`id`),
	key `t_hero_book_id`(`book_id`) 
)ENGINE = INNODB DEFAULT CHARSET=UTF8 COMMENT='英雄人物表';
INSERT into `t_hero` VALUES('1','郭靖','1','降龙十八掌','0','1');
INSERT into `t_hero` VALUES('2','黄蓉','2','打狗棍法','0','1');
INSERT into `t_hero` VALUES('3','乔峰','1','降龙十八掌','0','2');
INSERT into `t_hero` VALUES('4','令狐冲','1','独孤九剑','0','3');
INSERT into `t_hero` VALUES('5','任盈盈','0','d琴','0','3');
 

创建连接 *** 作：

"""
需求：查询当前mysql版本
"""
#导包
import pymysql
# 创建连接
conn=pymysql.connect(host="localhost",
                     port=3306,
                     user="root",
                     password="root",
                     database="books")
# 获取游标
cursor=conn.cursor()
# 执行sql
cursor.execute("select version()")
result=cursor.fetchall()
print(result)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

数据库select查询 *** 作

"""
1）连接到数据库（host：localhost user：root password：root，database：books
2）查询图书表的数据（包括：图书id，图书名称，阅读量，评论量）
3）获取查询结果的总记录数
4）获取查询结果的第一条数据
5）获取全部的查询结果
"""
# 创建连接
import pymysql

conn = pymysql.Connect(host="localhost",
                       port=3306,
                       user="root",
                       password="root",
                       database="books")
# 获取游标
cursor = conn.cursor()
# 执行sql
# 2)查询图书表的数据（包括：图书id，图书名称，阅读量，评论量）
sql = "select id ,title, `read`,`comment`from t_book;"
cursor.execute(sql)
# 3)获取查询结果的总记录数
print("获取的查询结果记录数行数为：", cursor.rowcount)
# 4)获取查询结果的第一条数据
print(cursor.fetchone())
# 5）获取全部的查询结果
# cursor.rownumber = 0
print(cursor.fetchall())
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

数据库的新增 *** 作insert

注意：使用insert into/update/delete等语句时，创建连接时必须有autocommit=true，即自动提交事务。

"""
1）连接到数据库（host：localhost user：root password：root，database：books
2）新增一条图书数据（id：4，title：西游记，pub_date：1986-01-01）
"""
# 创建连接
import pymysql

conn = pymysql.Connect(host="localhost",
                       port=3306,
                       user="root",
                       password="root",
                       database="books",
                       autocommit=True)
# 获取游标
cursor = conn.cursor()
# 执行sql
# 2）新增一条图书数据（id：4，title：西游记，pub_date：1986-01-01）
sql = "insert into t_book(`id`,`title`,`pub_date`) values(4,'西游记','1986-01-01');"
cursor.execute(sql)
# 3)获取受影响的记录数
print('受影响的记录数为:', cursor.rowcount)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

数据库的更新update *** 作

"""
数据库更新 *** 作
1）连接到数据库
2）更新西游记图书名称为东游记（title：东游记）
"""
import pymysql

# 创建连接
conn = pymysql.connect(host="localhost",
                       port=3306,
                       user="root",
                       password="root",
                       database="books",
                       autocommit=True)
# 获取游标
cursor = conn.cursor()
# 执行sql
sql = "update t_book set title = '东游记' where title = '西游记';"
cursor.execute(sql);
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

数据库的删除delete *** 作

"""
数据库更新 *** 作
1）连接到数据库
2）删除图书名称为东游记（title：东游记）
"""
import pymysql

# 创建连接
conn = pymysql.connect(host="localhost",
                       port=3306,
                       user="root",
                       password="root",
                       database="books",
                       autocommit=True)
# 获取游标
cursor = conn.cursor()
# 执行sql
sql = "delete from t_book where title ='东游记';"
cursor.execute(sql)
# 返回受影响的行数
print(cursor.rowcount)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

小结：查询与非查询

相同点:基本 *** 作流程一致，创建连接--获取游标--执行sql语句--关闭游标--关闭连接；

不同点：执行的sql语句不同，非查询 *** 作需要开启事务（创建连接时指定参数autocommit）；