数据库基础

1. 数据库认识

数据库:C/S架构的操作数据文件的一个管理软件

1. 帮助我们解决并发问题
2. 能够帮助我们用更简单更快速的方式完成数据的增删改查
3. 能够给我们提供一些容错、高可用的机制
4. 权限的认证

数据库优势:

1.程序稳定性 ：这样任意一台服务所在的机器崩溃了都不会影响数据和另外的服务。

2.数据一致性 ：所有的数据都存储在一起，所有的程序操作的数据都是统一的，就不会出现数据不一致的现象

3.并发 ：数据库可以良好的支持并发，所有的程序操作数据库都是通过网络，而数据库本身支持并发的网络操作，不需要我们自己写socket

4.效率 ：使用数据库对数据进行增删改查的效率要高出我们自己处理文件很多

数据（Data）
数据库（DataBase，简称DB）
据库管理系统（DataBase Management System 简称DBMS）专门用来管理数据文件，帮助用户更简洁的操作数据的软件
数据库管理员 DBA（Database Administrator）

1.1 数据库分类

关系型数据库:

• mysql主要用于大型门户，例如搜狗、新浪等，它主要的优势就是开放源代码，因为开放源代码这个数据库是免费的，他现在是甲骨文公司的产品。
• oracle主要用于银行、铁路、飞机场等。该数据库功能强大，软件费用高。也是甲骨文公司的产品。
• sql server是微软公司的产品，主要应用于大中型企业，如联想、方正等。
• sqllite

非关系型数据库:

• redis
• mongodb
• memcache

关系型数据库需要有表结构，非关系型数据库是key-value存储的，没有表结构

1.2 mysql安装

mysql的安装、启动和基础配置 —— linux版本

mysql的安装、启动和基础配置 —— mac版本

mysql的安装、启动和基础配置 —— windows版本

• net start mysql （启动数据库服务）提示服务启动成
• net stop mysql （停止数据库服务）

1.3 sql语言简介

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

　　SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。SQL语言分为3种类型：

　　1、DDL语句 数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER

　　2、DML语句 数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT

　　3、DCL语句 数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE

1.4 表的存储方式

1. MyISAM:MySQL5.5以下 默认存储方式

   适合做读 插入数据比较频繁的，对修改和删除涉及少的

   • 存储的文件个数：表结构、表中的数据、索引
   • 支持表级锁
   • 不支持行级锁 不支持事务 不支持外键

2. InnoDB: MySQL5.55.6以上 默认存储方式

   适合并发比较高的，对事务一致性要求高的，相对更适应频繁的修改和删除操作

   • 存储的文件个数：表结构、表中的数据
   • 支持行级锁、支持表锁
   • 支持事务
   • 支持外键

3. MEMORY :数据存在内存中，表结构存在硬盘上，查询速度快，重启数据丢失

   • 存储的文件个数：表结构
   • 优势 ：增删改查都很快
   • 劣势 ：重启数据消失、容量有限

• 查看配置项:

  mysql> show variables like '%engine%';
  +----------------------------+--------+
  | Variable_name              | Value  |
  +----------------------------+--------+
  | default_storage_engine     | InnoDB |
  | default_tmp_storage_engine | InnoDB |
  | storage_engine             | InnoDB |
  +----------------------------+--------+
  3 rows in set (0.00 sec)
  

• 设置存储引擎方式

  # 1.创建时设置
  mysql> create table ai(id bigint(12),name varchar(200)) ENGINE=MyISAM; 
  mysql> create table country(id int(4),cname varchar(50)) ENGINE=InnoDB;
  
  # 2.也可以使用alter table语句，修改一个已经存在的表的存储引擎。
  mysql> alter table ai engine = innodb;
  
  # 3.my.ini配置文件
  [mysqld]
  default-storage-engine=INNODB
  

2. 账号操作

登录退出修改密码

mysql  # 进入mysql客户端
select user();  # 查看当前用户
exit      # 也可以用\q quit退出
# 默认用户登陆之后并没有实际操作的权限，需要使用管理员root用户登陆
	
mysql -uroot -p   # mysql5.6默认是没有密码的,遇到password直接按回车键
set password = password('123');   # 给当前数据库设置密码
mysql -uroot -p123 -h 192.168.10.3         # 远程登陆

创建账号

create user 'yhp'@'192.168.12.%   IDENTIFIED BY '123';	# 指示网段
create user 'yhp'@'192.168.12.13';  					# 只是某机器可以连接
create user 'yhp'@'%';                   				# 指示所有机器都可以连接

账号授权

show grants for 'yhp'@'192.168.12.13';				# 查看某个用户的权限
grant all on *.* to 'yhp'@'%';						# 给账号授权 all所有权限,`*.*`库.表
flush privileges;   								# 刷新使授权立即生效
grant all on *.* to 'yhp'@'%' identified by '123'	# 创建账号并授权	*****

3. 库操作

增：create database db1 charset utf8;
查：show databases;
改：alter database db1 charset latin1;
删: drop database db1;
use 数据库的名字; # 切换到这个数据库下

4. 表操作

4.1 建表

#语法：
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);

#注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的
create table t1(id int,name char);

4.2 表的数据类型

1. 数字

• 整数 int

  create table t4 (id1 int(4),id2 int(11));
  1. int默认是有符号的
  2. 它能表示的数字的范围不被宽度约束
  3. 它只能约束数字的显示宽度
  create table t5 (id1 int unsigned,id2 int);
  #  无符号整形
  

• 小数double float

  create table t2 (id1 float(5,2),id2 double(5,2),id3 decimal(5,2));
  参数表示总位数,小数位数
  

类型	大小	范围（有符号）	范围（无符号）unsigned约束
INT	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)
BIGINT	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)
FLOAT	4 字节float(255,30)	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)
DOUBLE	8 字节double(255,30)	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)

2. 日期和时间

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性。

类型	大小 (字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	年月日
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时分秒
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	年月日时分秒
TIMESTAMP	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

# year 年
# date 年月日
# time 时分秒
# datetime、timestamp 年月日时分秒

create table time(
y year,
d date,
t time,
dt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
ts timestamp);

insert into time values (now(),now(),now(),now(),now());
+------+------------+----------+---------------------+---------------------+
| y    | d          | t        | dt                  | ts                  |
+------+------------+----------+---------------------+---------------------+
| 2019 | 2019-06-09 | 17:10:21 | 2019-06-09 17:10:21 | 2019-06-09 17:10:21 |
+------+------------+----------+---------------------+---------------------+

mysql> insert into time(ts) value (20180926122010);
mysql> insert into time(ts) value ('2018-9-26 12:20:10');

3. 字符串

• char

  • 在检索的时候char数据类型会去掉空格
  • 长度为定义时长度
  • 检索速度快

• varchar

  • 变长字符串

  • 宽度限定最大字符

4. ENUM和SET类型

create table t10 (name char(20),gender enum('female','male'));

# 选择enum('female','male')中的一项作为gender的值，可以正常插入
insert into t10 values ('jack','male');
# 不能同时插入'male,female'两个值，也不能插入不属于'male,female'的值

create table t11 (name char(20),hobby set('抽烟','喝酒','烫头'));

# 可以任意选择set中的项，并自带去重功能
insert into t11 values ('jack','烫头,喝酒,烫头');
select * from t11;
+------+---------------+
| name | hobby        |
+------+---------------+
| jack | 喝酒,烫头     |
+------+---------------+
row in set (0.00 sec)
# 不能选择不属于set('抽烟','喝酒','烫头','翻车')中的项

4.3 约束

• unsigned 设置某一个数字无符号

• not null 某一个字段不能为空

• default 给某个字段设置默认值

  # not null
  # default
  create table t2(
     id int not null,
     name char(12) not null,
     age int default 18,
     gender enum('male','female') not null default 'male'
   )
  

• unique 设置某一个字段不能重复

  create table t3(
      id int unique,
      username char(12) unique,
      password char(18)
  );
  

  联合唯一：不能同时相同

  create table t4(
      id int,
      ip char(15),
      server char(10),
      port int,
      unique(ip,port)
  );
  

• auto_increment 设置某一个int类型的字段 自动增加

  自增字段 必须是数字 且 必须是唯一的

  create table t5(
      id int unique auto_increment,
      username char(10),
      password char(18)
  )
  insert into t5(username,password) values('yhp','123')
  

• primary key 主键：非空且不能重复

  一张表只能设置一个主键，也最好设置一个主键，约束这个字段 非空（not null） 且 唯一（unique）

  create table t6(
      id int not null unique,     # 指定的第一个非空且唯一的字段会被定义成主键
    name char(12) not null unique
  )
  

  create table t7(
      id int primary key,     
    name char(12) not null unique
  )
  

  联合主键

  create table t4(
      id int,
    ip char(15),
      server char(10),
      port int,
      primary key(ip,port) #联合主键 非空且不能重复
  );
  

• foreign key 外键,涉及到两个表

  # 员工表
  create table staff(
  id  int primary key auto_increment,
  age int,
  gender  enum('male','female'),
  salary  float(8,2),
  hire_date date,
  post_id int,
  foreign key(post_id) references post(pid)
  )
  
  # 部门表
  #pid postname post_comment post_phone
  create table post(
      pid  int  primary key,
      postname  char(10) not null unique,
      comment   varchar(255),
      phone_num  char(11)
  )
  

  级联删除和更新

  # 级联删除和级联更新
  create table staff2(
  id  int primary key auto_increment,
  age int,
  gender  enum('male','female'),
  salary  float(8,2),
  hire_date date,
  post_id int,
  foreign key(post_id) references post(pid) on delete cascade  # 级连删除
  										  on update cascade  # 级连更新
  )
  

4.4 修改表

alter table 表名 add 添加字段
alter table 表名 drop 删除字段
alter table 表名 modify 修改已经存在的字段  的类型 宽度 约束
alter table 表名 change 修改已经存在的字段  的类型 宽度 约束 和 字段名字

alter table 表名 add 字段名 数据类型(宽度) 约束 first/after name
alter table 表名 drop 字段名
alter table 表名 modify name varchar(12) not null
alter table 表名 change name new_name varchar(12) not null

id name age
alter table 表名 modify age int not null after id;
alter table 表名 modify age int not null first;

4.5 查看表

• show tables;

• desc 表名; # 查看表结构

• show create table 表名\G 能够看到和这张表相关的所有信息

\G选项的含义是是的记录能够竖向排列，以便更好的显示内容较长的记录。

4.6 删除表

drop table t1;

4.7 多表结构的创建与分析

分析步骤：
#1、先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）

#2、再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）

#3、总结：
#多对一：
如果只有步骤1成立，则是左表多对一右表
如果只有步骤2成立，则是右表多对一左表

#多对多
如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系

#一对一:
如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可

5. 数据操作

5.1 增加

insert into 表名 values (值....)[,(值),(值)]
# 按所有字段顺序加入一行数据或多行数据
insert into 表名(字段1，字段2...) values (值1,值2....)[,(值1,值2....),(值1,值2....)...]
#按选择字段添加一行或多行数据

#values多行,value只能加一行

5.2 删除

delete from 表 where 条件;
# 不加条件默认整个表,如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。
truncate table t1;
# 数据量大，删除速度比上一条快，且直接从零开始

5.3 更改

update 表 set 字段=新的值 where 条件；

5.4 单表查询

select * from 表
select 字段,字段.. from 表
select distinct 字段,字段.. from 表  # 按照查出来的字段去重
select 字段*5 from 表  # 按照查出来的字段操作
select 字段  as 新名字,字段 as 新名字 from 表  # 查出来的字段按照新名字显示
select 字段 新名字 from 表  # 省去as效果相同

SELECT DISTINCT 字段1,字段2... FROM 表名
                              WHERE 条件
                              GROUP BY field
                              HAVING 筛选
                              ORDER BY field
                              LIMIT 限制条数

关键字执行的优先级

from
where
group by
select
distinct
having
order by
limit
1.找到表:from
2.拿着where指定的约束条件，去表中取出一条条记录
3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4.执行select（去重）
5.将分组的结果进行having过滤
6.将结果按条件排序：order by
7.限制结果的显示条数

where语句

• 比较运算 > < = >= <= != <>

• 范围筛选

  • 多选一 :字段名 in (值1,值2，值3)

    select * from employee where salary in (20000,30000,3000,19000,18000,17000)
    

  • 在一个模糊的范围里 between 数字 and 数字

    select emp_name from employee where salary between 10000 and 20000;
    

  • 字符串的模糊查询 like

    • %多个字符
    • _单个字符

  • 正则匹配 regexp

    更加细力度的匹配的时候

    select * from 表 where 字段 regexp 正则表达式

    select * from employee where emp_name regexp '^j[a-z]{5}'
    

• 逻辑运算 - 条件的拼接

  • 与 and

  • 或 or

  • 非 not

    select * from employee where salary not in (20000,30000,3000,19000,18000,17000)
    # 查看岗位是teacher且名字是jin开头的员工姓名、年薪
    select emp_name,salary*12 from employee where post='teacher' and emp_name like 'jin%'
    select emp_name,salary*12 from employee where post='teacher' and emp_name regexp '^jin.*'
    

• 身份运算 - 关于null is null /is not null

  # 查看岗位描述不为NULL的员工信息
  select * from employee where post_comment is not null;
  

分组&聚合

# 单独使用GROUP BY关键字分组
    SELECT post FROM employee GROUP BY post;
    注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数

# GROUP BY关键字和GROUP_CONCAT()函数一起使用
    SELECT post,GROUP_CONCAT(emp_name) FROM employee GROUP BY post;#按照岗位分组，并查看组内成员名
    SELECT post,GROUP_CONCAT(emp_name) as emp_members FROM employee GROUP BY post;

# GROUP BY与聚合函数一起使用
    select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人

#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组

示例：
    SELECT COUNT(*) FROM employee;
    SELECT COUNT(*) FROM employee WHERE depart_id=1;
    SELECT MAX(salary) FROM employee;
    SELECT MIN(salary) FROM employee;
    SELECT AVG(salary) FROM employee;
    SELECT SUM(salary) FROM employee;
    SELECT SUM(salary) FROM employee WHERE depart_id=3;

求部门的最高薪资或者求公司的最高薪资都可以通过聚合函数取到

但是要得到对应的人，就必须通过多表查询

总是根据会重复的项来进行分组

分组总是和聚合函数一起用 最大 最小 平均 求和 有多少项

having语句

！！！执行优先级从高到低：where > group by > having 
1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) <20000;

ORDER BY 查询排序

按单列排序
    SELECT * FROM employee ORDER BY salary;
    SELECT * FROM employee ORDER BY salary ASC;
    SELECT * FROM employee ORDER BY salary DESC;

按多列排序:先按照age排序，如果年纪相同，则按照薪资排序
    SELECT * from employee
        ORDER BY age,
        salary DESC;

LIMIT 限制查询的记录数

SELECT * FROM employee ORDER BY salary DESC 
        LIMIT 3;                    #默认初始位置为0 
    
SELECT * FROM employee ORDER BY salary DESC
        LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条

SELECT * FROM employee ORDER BY salary DESC
        LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条

5.5 多表查询

外链接语法

SELECT 字段列表
    FROM 表1 INNER|LEFT|RIGHT JOIN 表2
    ON 表1.字段 = 表2.字段;

交叉连接

不适用任何匹配条件。生成笛卡尔积

内连接

只连接匹配的行

找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果

外链接之左连接

优先显示左表全部记录

在内连接的基础上增加左边有右边没有的结果

外链接之右连接

优先显示右表全部记录

在内连接的基础上增加右边有左边没有的结果

全外连接

显示左右两个表全部记录

在内连接的基础上增加左边有右边没有的和右边有左边没有的结果

select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id
;

子查询

#1：子查询是将一个查询语句嵌套在另一个查询语句中。
#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#4：还可以包含比较运算符：= 、 !=、> 、<等

5. 索引

5.1 索引原理

什么是索引 -- 目录

索引在MySQL中也叫是一种“键”，是存储引擎用于快速找到记录的一种数据结构。

为什么要有索引?

一般的应用系统，读写比例 ： 10：1,所以读（查询）的速度就至关重要了

索引太多，应用程序的性能可能会受到影响。而索引太少，对查询性能又会产生影响，要找到一个平衡点，这对应用程序的性能至关重要。

索引原理

本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

• block 磁盘预读原理

  每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。读硬盘的io操作的时间非常的长，比CPU执行指令的时间长很多,尽量的减少IO次数才是读写数据的主要要解决的问题.

• 搜索树

  

根结点 : A

父节点 : A是B,C的父节点

叶子节点：D,E是叶子节点

树的深度/树的高度：高度为3

• B+树是通过二叉查找树，再由平衡二叉树，B树演化而来

1. 分支节点和根节点都不再存储实际的数据了,让分支和根节点能存储更多的索引的信息,所有的实际数据都存储在叶子节点中,就降低了树的高度.b+树索引的高度都基本控制在3层,io操作的次数非常稳定

2. 在叶子节点之间加入了双向的链式结构,方便在查询中的范围条件

• 什么会影响索引的效率 —— 树的高度

  1.对哪一列创建索引，选择尽量短的列做索引

  2.对区分度高的列建索引，重复率超过了10%那么不适合创建索引

• 聚集索引和辅助索引

  # 在innodb中 聚集索引和辅助索引并存的
       # 聚集索引 - 主键 更快
             # 数据直接存储在树结构的叶子节点
       # 辅助索引 - 除了主键之外所有的索引都是辅助索引 稍慢
             # 数据不直接存储在树中
  # 在myisam中 只有辅助索引，没有聚集索引
  

5.2 索引的种类

普通索引INDEX：加速查找

唯一索引：
    -主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）
    -唯一索引UNIQUE:加速查找+约束（不能重复）

联合索引：
    -PRIMARY KEY(id,name):联合主键索引
    -UNIQUE(id,name):联合唯一索引
    -INDEX(id,name):联合普通索引

5.3 创建索引

#方式一
create table t1(
    id int,
    name char,
    age int,
    sex enum('male','female'),
    unique key uni_id(id),
    index ix_name(name) #index没有key
);
create table t1(
    id int,
    name char,
    age int,
    sex enum('male','female'),
    unique key uni_id(id),
    index(name) #index没有key
);


#方式二
create index ix_age on t1(age);


#方式三
alter table t1 add index ix_sex(sex);
alter table t1 add index(sex);

#查看
mysql> show create table t1;
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` char(1) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` enum('male','female') DEFAULT NULL,
  UNIQUE KEY `uni_id` (`id`),
  KEY `ix_name` (`name`),
  KEY `ix_age` (`age`),
  KEY `ix_sex` (`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

#删除索引
DROP INDEX 索引名 ON 表名字;

5.4正确使用索引

索引不生效的原因:

1. 要查询的数据的范围大
2. 如果一列内容的区分度不高，索引也不生效
3. 索引列不能在条件中参与计算
4. 对两列内容进行条件查询

什么时候用联合索引

只对 a 对abc 条件进行索引,而不会对b，对c进行单列的索引

单列索引

覆盖索引

合并索引

6. 数据库备份

表备份:mysqldump -uroot -p123 day40 > 文件路径

库备份:mysqldump -uroot -p123 --databases new_db > D:\code\db.sql

7. 事务

# begin;  # 开启事务
# select * from emp where id = 1 for update;  # 查询id值，for update添加行锁；
# update emp set salary=10000 where id = 1; # 完成更新
# commit; # 提交事务

8. pymysql模块

pip install PyMySQL#安装PyMySQL

8.1 插入操作

import pymysql
 
# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
 
# 使用cursor()方法获取操作游标 
cursor = db.cursor()
 
# SQL 插入语句
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
   cursor.execute(sql) # 执行sql语句
   db.commit()         # 提交到数据库执行
except:
   db.rollback()       # 如果发生错误则回滚
 
# 关闭数据库连接
db.close()

8.2 查询操作

Python查询Mysql使用 fetchone() 方法获取单条数据, 使用fetchall() 方法获取多条数据。

• fetchone(): 该方法获取下一个查询结果集。结果集是一个对象
• fetchall(): 接收全部的返回结果行.
• rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数。

import pymysql
 
# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
 
# 使用cursor()方法获取操作游标 
cursor = db.cursor()
 
# SQL 查询语句
sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > %s" % (1000)
try:
   
   cursor.execute(sql)# 执行SQL语句
   results = cursor.fetchall()# 获取所有记录列表
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
       # 打印结果
      print ("fname=%s,lname=%s,age=%s,sex=%s,income=%s" % \
             (fname, lname, age, sex, income ))
except:
   print ("Error: unable to fetch data")
 
# 关闭数据库连接
db.close()

9. sql注入

自己做sql语句拼接，会遇到sql注入的可能，如下操作：

username = input('user >>>')
password = input('passwd >>>')
sql = "select * from userinfo where name = '%s' and password = '%s'"%(username,password)
print(sql)

sql中--表示注释，当遇到如下操作就发生了sql注入：

select * from userinfo where name = 'yhp' ;-- and password = '792164987034';
select * from userinfo where name = 'xxx' or 1=1 ;-- and password = '792164987034';

pymysql中，execute进行了字符串的处理，防止注入发生。

import pymysql

conn = pymysql.connect(host = '127.0.0.1',user = 'root',
                       password = '123',database='test')
cur = conn.cursor()
username = input('user >>>')
password = input('passwd >>>')
sql = "select * from userinfo where name = %s and password = %s" # %s不需要加引号
cur.execute(sql,(username,password)) #后面加可迭代对象
print(cur.fetchone())
cur.close()
conn.close()