八、MySql的使用

一、安装与启动

1.1 Ubuntu下的使用

(1) 安装
sudo apt-get install mysql-server

(2) 启动服务
sudo service mysql start

(3) 停止服务
sudo service mysql stop

(4) 重启服务
sudo service mysql restart

1.2 Mac下的使用

(1) 安装
下载地址: https://downloads.mysql.com/archives/community/
选择适合你机器的版本和CPU的型号

使用向导安装

一路next, 直到配置的时候选择Use Legacy Password Encryption

下一步输入root用户的密码, 必须8位

查看是否安装成功, 可以在系统偏好设置里面查看, 有mysql就是安装成功了.

(2) 配置mysql

进入配置文件:vi ~/.bash_profile
加入语句：PATH=$PATH:/usr/local/mysql/bin
使配置的语句生效：source ~/.bash_profile

如果配置成功，那么输入命令：mysql -uroot -p，运行效果如下：

输入安装时设定的root用户密码即可登录成功.

二、MySQL图形化管理工具的使用

MySQL Workbench下载地址: https://dev.mysql.com/downloads/workbench/
选择适合MySQL版本的Workbench下载安装即可

安装后启动可以通过下面的方式创建数据库

创建表

三、数据完整性

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

3.1 数据类型

• 数据类型如下：
  • 整数类型: TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT
  • 浮点数类型: FLOAT 和 DOUBLE
  • 定点数类型: DECIMAL
  • 日期/时间类型: YEAR、TIME、DATE、DATETIME 和 TIMESTAMP。
  • 字符串类型: CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET 等。
  • 二进制类型: BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。
• 特别说明的类型如下：
  • decimal表示定点数，如decimal(5,2)表示共存5位数，小数占2位
  • char表示固定长度的字符串，如char(3)，如果填充’ab’时会补一个空格补够3位, 如: 'ab ’
  • varchar表示可变长度的字符串，如varchar(3)，填充’ab’时就会存储’ab’, 这里的可变的意思是小于上限的时候按实际长度来填充.
  • 字符串text表示存储大文本，当字符大于4000时推荐使用
  • 对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径.
• 常用数据类型的数据长度

  • 整数
    

  • 浮点数
    

  • 字符串
    

  • 日期时间
    

3.2 约束

• 主键primary key：物理上存储的顺序
• 非空not null：此字段不允许填写空值
• 惟一unique：此字段的值不允许重复
• 默认default：当不填写此值时会使用默认值，如果填写时以填写为准
• 外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常
• 说明：虽然外键约束可以保证数据的有效性，但是在进行数据的crud（增加、修改、删除、查询）时，都会降低数据库的性能，所以不推荐使用，那么数据的有效性怎么保证呢？答：可以在逻辑层进行控制

四、常用的sql语句

4.1 数据库操作

(1) 查看数据库
show databases;

(2) 使用数据库
use 数据库名;

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

(4) 创建数据库
create database 数据库名 charset=utf8;

(5) 删除数据库
drop database 数据库名;

4.2 数据库表操作

1.查看当前数据库中所有表

show tables;
首先需要先使用use命令进入对应的数据库,然后再使用show tables命令查看选中的数据库下的所有表.

如果不想切换到其他数据库去查看表, 也可以这样查看
show tables from 要查看的数据库名;

2.查看建表的数据结构

desc 表名;

3.创建表

create table 表名(
    字段1 字段类型 约束,
    字段2 字段类型 约束,
    ...
    字段N 字段类型 约束,
);

例如创建一张students表

4.添加字段

alter table 表名 add 字段名 字段类型 约束;
例如修改students表,添加birthday 字段.

5.修改字段

change的方式:
alter table 表名 change 旧字段名  新字段名 新字段类型及约束;

modify的方式:
alter table 表名 modify 旧字段名  新的字段类型及约束;

例如将students表中的birthday字段修改为datetime字段

6. 删除字段

alter table 表名 drop 字段名;
例如将students的birth字段删除.

7. 查看表的创建语句

show create table 表名;

8. 删除表

drop table 表名;
例如删除students表.

9. 外键操作

(1) 添加外键
alter table goods add foreign key (brand_id) references goods_brands(id);
(2) 删除外键
alter table goods drop foreign KEY brand_id;

4.3 数据库curd操作

1. 查询基本使用

(1) 查询所有列
select * from 表名;
(2) 查询指定的字段, 可以使用as为字段定别名
select 字段1,字段2 as xxx,...from 表名;
例:
select name as 名字, age as 年龄 from students;

2. 插入内容

主键列是自动增长，但是在全列插入时需要占位，通常使用0或者 default 或者 null 来占位，插入成功后以实际数据为准.
(1) 全字段插入: 值的插入顺序与表中的字段的顺序对应

insert into 表名 values(值1,值2,..);

例:
insert into students values(null,'李四', 32);

现有的students表的结构如下:

全列插入一条记录

(2) 部分列插入
值的顺序与给出列的顺序对应.

insert into 表名(字段1,...) values(值1,...);

例:
insert into students(name) values('王五');

(3) 同时插入多条记录
前2种语句一次可以向表中插入一行记录，下面这种方式可以一次性插入多行记录，这样可以减少与数据库的通信.

insert into 表名 values(值1,值2...),(值1,值2...)....;

例:
insert into students values(null,'小红',20),(null,'小明',30),(null,'小王',25);

3. 修改

update 表名 set 字段1=值1,字段2=值2...where 条件

例:
update students set name='小小红' age=18 where id=4;

4. 删除

delete from 表名 where 条件

例:
delete from students where id=4;

4.4 数据库的备份与恢复

1. 备份

mysqldump -uroot -p 数据库名 >要保存的数据库名(带路径).sql

例:
mysqldump -uroot -p mydb > ~/mydb_bck.sql

2. 恢复

mysql -uroot -p 新的数据库名字(必须存在) < 已备份的数据库名(带路径).sql

例:
mysql -uroot -p mydb_bck < ~/mydb_bck.sql

注意: 在恢复数据库的时候,新的数据库不存在的时候需要提前创建.

五、常用查询

5.1 数据准备

1. 创建数据库、数据表

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

-- 使用数据库
use python_test_1;

-- students表
create table students(
    id int unsigned primary key auto_increment not null,
    name varchar(20) default '',
    age tinyint unsigned default 0,
    height decimal(5,2),
    gender enum('男','女','中性','保密') default '保密',
    cls_id int unsigned default 0,
    is_delete bit default 0
);

-- classes表
create table classes (
    id int unsigned auto_increment primary key not null,
    name varchar(30) not null
);

2. 准备数据

-- 向students表中插入数据
insert into students values
(0,'小明',18,180.00,2,1,0),
(0,'小月月',18,180.00,2,2,1),
(0,'彭于晏',29,185.00,1,1,0),
(0,'刘德华',59,175.00,1,2,1),
(0,'黄蓉',38,160.00,2,1,0),
(0,'凤姐',28,150.00,4,2,1),
(0,'王祖贤',18,172.00,2,1,1),
(0,'周杰伦',36,NULL,1,1,0),
(0,'程坤',27,181.00,1,2,0),
(0,'刘亦菲',25,166.00,2,2,0),
(0,'金星',33,162.00,3,3,1),
(0,'静香',12,180.00,2,4,0),
(0,'郭靖',12,170.00,1,4,0),
(0,'周杰',34,176.00,2,5,0);
-- 向classes表中插入数据
insert into classes values (0, "python_01期"), (0, "python_02期");

效果图:

5.2 普通查询

1. 查询所有字段

select * from 表名;
例：
select * from students;

2. 查询指定字段

select 列1,列2,... from 表名;
例:
select name from students;

3. 使用 as 给字段起别名

select id as 序号, name as 名字, gender as 性别 from students;

4. 可以通过 as 给表起别名

-- 如果是单表查询 可以省略表名
select id, name, gender from students;
-- 表名.字段名
select students.id,students.name,students.gender from students;
-- 可以通过 as 给表起别名 
select s.id,s.name,s.gender from students as s;

5. 消除重复行

在select后面列前使用distinct可以消除重复的行

select distinct 列1,... from 表名;
例：
select distinct gender from students;

5.3 条件查询

使用where子句对表中的数据筛选，结果为true的行会出现在结果集中

select * from 表名 where 条件;
例：
select * from students where id=1;

where后面支持多种运算符，进行条件的处理

• 比较运算符
• 逻辑运算符
• 模糊查询
• 范围查询
• 空判断

1.比较运算符

• 等于: =
• 大于: >
• 大于等于: >=
• 小于: <
• 小于等于: <=
• 不等于: != 或 <>

例1：查询编号大于3的学生

select * from students where id > 3;

例2：查询编号不大于4的学生

select * from students where id <= 4;

例3：查询姓名不是“黄蓉”的学生

select * from students where name != '黄蓉';

例4：查询没被删除的学生

select * from students where is_delete=0;

2. 逻辑运算符

• and
• or
• not

例5：查询编号大于3的女同学

select * from students where id > 3 and gender=0;

例6：查询编号小于4或没被删除的学生

select * from students where id < 4 or is_delete=0;

3. 模糊查询

• like
• %表示任意多个任意字符
• _表示一个任意字符

例7：查询姓黄的学生

select * from students where name like '黄%';

例8：查询姓黄并且“名”是一个字的学生

select * from students where name like '黄_';

例9：查询姓黄或叫靖的学生

select * from students where name like '黄%' or name like '%靖';

4. 范围查询

• in表示在一个非连续的范围内
• between … and …表示在一个连续的范围内

例10：查询编号是1或3或8的学生

select * from students where id in(1,3,8);

例11：查询编号为3至8的学生

select * from students where id between 3 and 8;

例12：查询编号是3至8的男生

select * from students where (id between 3 and 8) and gender=1;

5. 空判断

• 注意：null与’'是不同的
• 判空is null
• 判非空is not null

例13：查询没有填写身高的学生

select * from students where height is null;

例14：查询填写了身高的学生

select * from students where height is not null;

例15：查询填写了身高的男生

select * from students where height is not null and gender=1;

6. 优先级

• 优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符
• and比or先运算，如果同时出现并希望先算or，需要结合()使用

5.4 结果排序

语法:
select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]
说明

• 将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推
• 默认按照列值从小到大排列（asc）
• asc从小到大排列，即升序
• desc从大到小排序，即降序

例1：查询未删除男生信息，按学号降序

select * from students where gender=1 and is_delete=0 order by id desc;

例2：查询未删除学生信息，按名称升序

select * from students where is_delete=0 order by name;

例3：显示所有的学生信息，先按照年龄从大–>小排序，当年龄相同时 按照身高从高–>矮排序

select * from students  order by age desc,height desc;

5.5 聚合函数

为了快速得到统计数据，经常会用到如下5个聚合函数

• 计算总数
• 计算最大值
• 计算最小值
• 求和
• 求平均值

1. 求总数

count(*)表示计算总行数，括号中写星与列名，结果是相同的
例1：查询学生总数

select count(*) from students;

2. 求最大值

max(列)表示求此列的最大值
例2：查询女生的编号最大值

select max(id) from students where gender=2;

3. 求最小值

min(列)表示求此列的最小值
例3：查询未删除的学生最小编号

select min(id) from students where is_delete=0;

4. 求和

sum(列)表示求此列的和
例4：查询男生的总年龄

select sum(age) from students where gender=1;

-- 平均年龄
select sum(age)/count(*) from students where gender=1;

5. 求平均值

avg(列)表示求此列的平均值
例5：查询未删除女生的编号平均值

select avg(id) from students where is_delete=0 and gender=2;

5.6 分组

• group by的含义:将查询结果按照1个或多个字段进行分组，字段值相同的为一组
• group by可用于单个字段分组，也可用于多个字段分组

不分组的结果如下:

分组后结果:

根据gender字段来分组，gender字段的全部值有4个’男’,‘女’,‘中性’,‘保密’，所以分为了4组 当group by单独使用时，只显示出每组的第一条记录, 所以group by单独使用时的实际意义不大

1. group by + group_concat()

• group_concat(字段名)可以作为一个输出字段来使用，
• 表示分组之后，根据分组结果，使用group_concat()来放置每一组的某字段的值的集合

select gender,group_concat(name) from students group by gender;

2. group by + 集合函数

通过group_concat()的启发，我们既然可以统计出每个分组的某字段的值的集合，那么我们也可以通过集合函数来对这个值的集合做一些操作

select gender,avg(age) from students group by gender;

select gender,count(*) from students group by gender;

3. group by + having

• having 条件表达式：用来分组查询后指定一些条件来输出查询结果
• having作用和where一样，但having只能用于group by

select gender,count(*) from students group by gender having count(*)>2;

4. group by + with rollup

with rollup的作用是：在最后新增一行，来记录当前列里所有记录的总和

select gender,count(*) from students group by gender with rollup;

5.7 分页

当数据量过大时，在一页中查看数据是一件非常麻烦的事情, 可以通过limit来分页查询结果
语法:

select * from 表名 limit start,count

说明
从start开始，获取count条数据

例1：查询前3行男生信息

select * from students where gender=1 limit 0,3;

5.8 连接查询

当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回
mysql支持三种类型的连接查询，分别为：

• 内连接查询：查询的结果为两个表匹配到的数据
• 右连接查询：查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充
• 左连接查询：查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充

语法:
select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列

1. 内连接查询

使用内连接查询班级表与学生表

select * from students 
inner join classes 
on students.cls_id = classes.id;

2. 左连接查询

使用左连接查询班级表与学生表
此处使用了as为表起别名，目的是编写简单

select * from students as s 
left join classes as c 
on s.cls_id = c.id;

3. 右连接查询

使用右连接查询班级表与学生表

select * from students as s 
right join classes as c 
on s.cls_id = c.id;

5.9 自关联

所谓自关联就是一个表中的一个列引用了该表中另一个列的数据，但这些列的业务逻辑含义是不同的。例如，在一个员工表中，每个员工都有一个经理，该经理也是该表中的员工，这意味着每个员工行的 manager_id 列将引用该表中另一行的 id 列（即 manager_id 是外键，参照该表的 id 列）。但是，这两个列的业务逻辑含义是不同的。id 列表示每个员工的唯一标识符，而 manager_id 列是该员工的直接经理的唯一标识符。
在这种情况下，可以使用自关联来处理这种层次关系的数据结构。例如，在 SELECT 语句中，可以使用自连接来查找某个员工的经理的名字：

SELECT e.name AS employee_name, m.name AS manager_name 
FROM employees e JOIN employees m ON e.manager_id = m.id 
WHERE e.name = 'John';

在此示例中，“employees e”和“employees m”表示同一个表的两个实例，其中“e.manager_id = m.id”表示使用自连接筛选经理的名字，然后使用“e.name = ‘John’”筛选需要的员工 John 的数据。

5.10 子查询

在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句; 主要查询的对象,第一条 select 语句.

• 子查询是辅助主查询的,要么充当条件,要么充当数据源
• 子查询是可以独立存在的语句,是一条完整的 select 语句

子查询可以分为以下四类：

1. 标量子查询

返回单一值，例如：

SELECT name, salary 
FROM employees 
WHERE salary = (SELECT MAX(salary) FROM employees);

2. 行子查询

返回多行数据，但不能独立使用，仅能够在 IN 和 NOT IN 子句中使用，例如：

SELECT name, salary 
FROM employees 
WHERE id IN (SELECT employee_id FROM departments WHERE department_name='IT');

3. 列子查询

返回一列或多列的多行数据，可用于 SELECT、WHERE 或 HAVING 子句中，例如：

SELECT name, 
  (SELECT AVG(salary) FROM employees WHERE department_id = d.id) AS avg_salary
FROM departments d;

4. 表子查询

返回一个临时表，可在其他查询中被引用，例如：

SELECT d.name, COUNT(e.id) AS employee_count
FROM departments d
LEFT JOIN (SELECT * FROM employees WHERE hire_date > '2019-01-01') e 
ON d.id = e.department_id 
GROUP BY d.name;

六、视图

6.1 视图的基本操作

1. 创建视图
   create view 视图名称 as select语句;
2. 查看视图
   查看表会将所有的视图也列出来
   show tables;
3. 使用视图
   视图的用途就是查询
   select * from 视图名称;
4. 删除视图
   drop view 视图名称;

6.2 视图使用示例

比如需要将students和classes这2张表的内连接查询创建视图

mysql> create view v_students_classes_info as
    -> select s.id as student_id,s.name as student_name,s.age,s.height,s.gender, c.id as class_id,c.name as class_name 
    -> from students as s
    -> inner join classes as c
    -> on s.cls_id=c.id;

至于为什么要给students和classes的id和name起别名是因为视图中的字段不允许存在同名的字段.

查询创建好的视图v_students_classes_info就比较简单了.

select * from v_students_classes_info;

七、事务

所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。
例如，银行转帐工作：从一个帐号扣款并使另一个帐号增款，这两个操作要么都执行，要么都不执行。所以，应该把他们看成一个事务。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性

7.1 事务的四大特征

• 原子性（atomicity）
  一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性
• 一致性（consistency）
  数据库总是从一个一致性的状态转换到另一个一致性的状态。（一致性确保了，即使在执行第三、四条语句之间时系统崩溃，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。）
• 隔离性（isolation）
  通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。（当执行完第三条语句、第四条语句还未开始时，此时有另外的sql程序开始运行，则其看到还是原来的数据。）
• 持久性（durability）
  一旦事务提交，则其所做的修改会永久保存到数据库。（此时即使系统崩溃，修改的数据也不会丢失。）

7.2 事务命令

表的引擎类型必须是innodb类型才可以使用事务，这是mysql表的默认引擎, 通过查看表的创建语句可以查看表的引擎, 例如:
show create table goods;

1. 开启事务

开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中

begin;
--或者
start transaction;

2. 提交事务

将缓存中的数据变更维护到物理表中

commit;

3. 回滚事务

放弃缓存中变更的数据

rollback;

注意

• 修改数据的命令会自动的触发事务，包括insert、update、delete
• 而在SQL语句中有手动开启事务的原因是：可以进行多次数据的修改，如果成功一起成功，否则一起会滚到之前的数据

八、Python中使用MySql

8.1 引入模块

在py文件中引入pymysql模块, 如果不存在pymysql需要先下载

from pymysql import *

8.2 创建Connection对象

conn=connect(参数列表)

参数列表:

• 参数host：连接的mysql主机，如果本机是’localhost’
• 参数port：连接的mysql主机的端口，默认是3306
• 参数database：数据库的名称
• 参数user：连接的用户名
• 参数password：连接的密码
• 参数charset：通信采用的编码方式，推荐使用utf8

对象的方法:

• close()关闭连接
• commit()提交
• cursor()返回Cursor对象，用于执行sql语句并获得结果

8.3 Cursor对象

• 用于执行sql语句，使用频度最高的语句为select、insert、update、delete
• 获取Cursor对象：调用Connection对象的cursor()方法

cs1 = conn.cursor()

对象的方法:

• close()关闭
• execute(operation [, parameters ])执行语句，返回受影响的行数，主要用于执行insert、update、delete语句，也可以执行create、alter、drop等语句
• fetchone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元组
• fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回

8.4 Python中实现MySql的增删改查操作

1. 增删改

from pymysql import *


def main():
    # 创建Connection连接
    conn = connect(host='localhost', port=3306, database='python_test_1', user='root', password='00000000',
                   charset='utf8')
    # 获得Cursor对象
    cs1 = conn.cursor()
    # 执行insert语句，并返回受影响的行数：添加一条数据
    # 增加
    count = cs1.execute('insert into goods_cates(name) values("硬盘")')
    # 打印受影响的行数
    print(count)

    count = cs1.execute('insert into goods_cates(name) values("光盘")')
    print(count)

    #  更新
    count = cs1.execute('update goods_cates set name="机械硬盘" where name="硬盘"')
    print(count)

    #  删除
    count = cs1.execute('delete from goods_cates where id=1')
    print(count)

    # 提交之前的操作，如果之前已经之执行过多次的execute，那么就都进行提交
    conn.commit()

    # 关闭Cursor对象
    cs1.close()
    # 关闭Connection对象
    conn.close()


if __name__ == '__main__':
    main()

2. 查询一条记录

from pymysql import *


def main():
    # 创建Connection连接
    conn = connect(host='localhost', port=3306, database='python_test_1', user='root', password='00000000',
                   charset='utf8')
    # 获得Cursor对象
    cs1 = conn.cursor()

    # 执行select语句,并返回受影响的行数
    count = cs1.execute("select id,name from goods_cates where id >=1")
    # 打印受影响的行数
    print("查询到%d条数据" % count)

    for i in range(count):
        # 获取查询结果
        result = cs1.fetchone()
        # 打印结果
        print(result)

    # 关闭Cursor对象
    cs1.close()
    # 关闭Connection对象
    conn.close()


if __name__ == '__main__':
    main()

打印结果:

查询到5条数据
(2, '光盘')
(3, '机械硬盘')
(4, '光盘')
(5, '机械硬盘')
(6, '光盘')

可见, 结果都是元组类型.

3. 查询多行数据

from pymysql import *


def main():
    # 创建Connection连接
    conn = connect(host='localhost', port=3306, database='python_test_1', user='root', password='00000000',
                   charset='utf8')
    # 获得Cursor对象
    cs1 = conn.cursor()

    # 执行select语句,并返回受影响的行数
    count = cs1.execute("select id,name from goods_cates where id >=1")
    # 打印受影响的行数
    print("查询到%d条数据" % count)

    # for i in range(count):
    #     # 获取查询结果
    #     result = cs1.fetchone()
    #     # 打印结果
    #     print(result)

    result = cs1.fetchall()
    print(result)

    # 关闭Cursor对象
    cs1.close()
    # 关闭Connection对象
    conn.close()


if __name__ == '__main__':
    main()

打印结果:

查询到5条数据
((2, '光盘'), (3, '机械硬盘'), (4, '光盘'), (5, '机械硬盘'), (6, '光盘'))

可见多行数据也是一个元组, 只不过里面的每条数据又是独立的元组.

8.5 参数化

• sql语句的参数化，可以有效防止sql注入, 例如在where语句后面添加 " or 1=1 or " (双引号也要输入) ,导致条件永远为真。
• 注意：此处不同于python的字符串格式化，全部使用%s占位

from pymysql import *


def main():
    find_name = input("请输入物品名称：")

    # 创建Connection连接
    conn = connect(host='localhost', port=3306, database='python_test_1', user='root', password='00000000',
                   charset='utf8')
    # 获得Cursor对象
    cs1 = conn.cursor()

    # # 非安全的方式
    # # 输入 " or 1=1 or "   (双引号也要输入)
    # sql = 'select * from goods_cates where name="%s"' % find_name
    # print("""sql===>%s<====""" % sql)
    # # 执行select语句，并返回受影响的行数：查询所有数据
    # count = cs1.execute(sql)

    # 安全的方式
    # 构造参数列表
    params = [find_name]
    # 执行select语句，并返回受影响的行数：查询所有数据
    count = cs1.execute('select * from goods_cates where name=%s', params)
    # 注意：
    # 如果要是有多个参数，需要进行参数化
    # 那么params = [数值1, 数值2....]，此时sql语句中有多个%s即可

    # 打印受影响的行数
    print(count)
    # 获取查询的结果
    result = cs1.fetchall()
    # 打印查询的结果
    print(result)
    # 关闭Cursor对象
    cs1.close()
    # 关闭Connection对象
    conn.close()


if __name__ == '__main__':
    main()

九、索引

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度

9.1 索引的使用

1. 查看索引

show index from 表名;

2. 创建索引

• 如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
• 字段类型如果不是字符串，可以不填写长度部分

create index 索引名称 on 表名(字段名称(长度))

3. 删除索引

drop index 索引名称 on 表名;

9.2 索引的使用示例

1. 创建测试表

create table test_index(title varchar(10));

2. 使用Python插入10万条测试数据

from pymysql import *


def main():
    # 创建Connection连接
    conn = connect(host='localhost', port=3306, database='python_test_1', user='root', password='00000000',
                   charset='utf8')
    # 获得Cursor对象
    cs1 = conn.cursor()

    # 获得Cursor对象
    cursor = conn.cursor()
    # 插入10万次数据
    for i in range(100000):
        cursor.execute(f"insert into test_index values('测试数据-{i}')")
    # 提交数据
    conn.commit()

    # 关闭Cursor对象
    cs1.close()
    # 关闭Connection对象
    conn.close()


if __name__ == '__main__':
    main()

3. 检测普通查询的耗时

set profiling=1;
select * from test_index where title='测试数据-99999';
show profiles;

4. 创建索引

为表title_index的title列创建索引

create index title_index on test_index(title(10));

5. 使用索引查询查看耗时

注意:
建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。
建立索引会占用磁盘空间

十、账户管理

• 在生产环境下操作数据库时，绝对不可以使用root账户连接，而是创建特定的账户，授予这个账户特定的操作权限，然后连接进行操作，主要的操作就是数据的crud
• MySQL账户体系：根据账户所具有的权限的不同，MySQL的账户可以分为以下几种
  • 服务实例级账号：，启动了一个mysqld，即为一个数据库实例；如果某用户如root,拥有服务实例级分配的权限，那么该账号就可以删除所有的数据库、连同这些库中的表
  • 数据库级别账号：对特定数据库执行增删改查的所有操作
  • 数据表级别账号：对特定表执行增删改查等所有操作
  • 字段级别的权限：对某些表的特定字段进行操作
  • 存储程序级别的账号：对存储程序进行增删改查的操作
• 账户的操作主要包括创建账户、删除账户、修改密码、授权权限等, 进行账户操作时，需要使用root账户登录，这个账户拥有最高的实例级权限

10.1 授予权限

1. 查看所有用户

• 所有用户及权限信息存储在mysql数据库的user表中
• 查看user表的结构
  desc user;
• 主要字段说明：
  • Host表示允许访问的主机
  • User表示用户名
  • authentication_string表示密码，为加密后的值

查看所有用户

use mysql; 
select host,user,authentication_string from user;

结果:

2. 创建账户和授权

-- mysql8.0.11之前的版本
grant 权限列表 on 数据库 to '用户名'@'访问主机' identified by '密码';

--mysql8.0.11之后的版本
create user '用户名'@'访问主机' identified by '密码';
grant select on `数据库名称`.* to '用户名'@'访问主机';
--如果要授予所有权限
grant all privileges on `数据库名称`.* to '用户名'@'访问主机';

例如创建一个laowang的账号，密码为123456，只能通过本地访问, 并且只能对python_test_1数据库中的所有表进行读操作

查看用户具有的权限,可以这样:

show grants for laowang@localhost;

3. 使用授权用户登录数据库

4. 修改密码

使用root登录，修改mysql数据库的user表
使用password()函数进行密码加密

-- mysql8.0.11之前的版本
update user set authentication_string=password('新密码') where user='用户名';
例：
update user set authentication_string=password('123') where user='laowang';

--mysql8.0.11之后的版本
alter user '用户名'@'主机' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '你要修改的密码';
例:
alter user 'laowang'@'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '123456';

注意修改完成后需要刷新权限

flush privileges;

5. 删除账户

drop user '用户名'@'主机';
--例：
drop user 'laowang'@'localhost';

--或者
delete from user where user='用户名';
--例：
delete from user where user='laowang';
-- 操作结束之后需要刷新权限
flush privileges;