MySQl数据库的使用

一、环境安装及运行

  • 安装（ubuntu系统）

    sudo apt-get install mysql-server mysql-client

     根据提示，设置root用户的密码

  • 管理服务

    sudo service mysql start #开启
sudo service mysql stop #关闭
sudo service mysql restart #重启

  • 允许远程连接

    • 修改配置文件

      sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

      将bind-address=127.0.0.1注释

    • 重启mysql服务

      sudo service mysql restart

    • 登陆mysql

      mysql -uroot -p

      然后输入root账户密码

    • mysql中依次输入如下命令

      grant all privileges on *.* to 'root'@'%' identified by '安装时设置的密码' with grant option;
flush privileges;

    • 退出

      exit

    • 重启mysql

二、用户操作

  • 创建用户

    • 通过root账号登陆mysql（用户操作均在root账号下进行）

      mysql -uroot -p

      然后输入root账号密码

    • 创建用户，并赋予相应权限

      grant all privileges on 数据库名.表名 to '用户名'@'主机名' identified by '密码' with grant option;
flush privileges;
      • 数据库名：*表示所有
      • 表名：*表示该数据库下的所有表
      • 数据库名.表名：*.*表示root权限，即满权限
      • 主机名：localhost表示仅允许本地连接，%表示本地和远程均可连接
      • flush privileges；表示刷新权限，使授权生效

    • 示例：新建test用户，授予该用户的权限是仅能操作test_database数据库，密码‘123’

      grant all privileges on test_database.* to 'test'@'%' identified by '123' with grant option;
flush privileges;

    • 如果忘记identified by后面这个密码，也可以按照如下操作：

      use mysql;
select Host, User from user;
update user set Host='%' where User='root';
flush privileges;

  • 修改用户密码

    • 使用mysql数据库

      use mysql;

    • 修改用户密码(更新user这个表)

      update user set authentication_string = password('新密码') where user = '用户名' and host = '主机名';

      password()为mysql自身的一个加密函数

    • 示例：修改test用户密码为'456'

      update user set authentication_string = password('456') where user = 'test' and host = '%'；

  • 撤销用户权限

    revoke all on 数据库名.表名　from '用户名'@'主机名';
flush privileges;

  • 删除用户

    drop user '用户名'@'主机名';

三、数据库操作

  • 创建数据库

    create database 数据库名 charset=utf8;

  • 删除数据库

    drop database 数据库名;

  • 切换数据库

    use 数据库名;

  • 查看所有数据库

    show databases;

  • 查看当前所在数据库

    select database();

  • 数据库备份

    • 切换至超级管理员

      sudo -s

    • 进入mysql库目录

      cd /var/lib/mysql

    • 运行mysqldump命令

      mysqldump -uroot -p 需要备份的数据库名 > /path/备份文件名.sql #后面重定向的路径自定义

  • 数据库恢复

    • 连接数据库

      mysql -uroot -p

    • 创建数据库

      create database 恢复后数据库名 charset=utf8;

    • 退出

      exit

    • 从备份数据库中恢复

      mysql -uroot –p 恢复后数据库名 < /path/备份文件.sql

四、表操作

  • 查看当前数据库中所有表

    show tables;

  • 创建表

    • 语法

      create table 表名 (字段1 类型 约束，字段2 类型 约束...)；

    • 常见的字段类型

      • float：单精度浮点型
      • double：双精度浮点型
      • decimal（m,d）：比如decimal(5,2)表示最多5位数，小数位2位，可以存储1.2 但不能存储1000.21
      • tinyint：小整数值，1byte，范围(0,255)
      • int：大整数值，4bytes，范围(0,232-1)
      • bigint：极大整数值，8bytes，范围(0,264-1)
      • date：日期值，比如2020-10-10
      • time：时间值，比如23:59:59
      • datetime：混合日期和时间值，比如2020-10-10 23:59:59
      • timestamp：时间戳
      • bit：布尔类型，0和1两种状态
      • char(n)：定长字符串，不够位数，右侧补空格
      • varchar(n)：不定长字符串，最多能存储n个字符
      • tinytext：短文本数据
      • text：长文本数据
      • tinyblob：不超过255个字符的二进制字符串
      • blob：二进制形式的长文本数据

    • 约束

      • 主键：primary key
      • 非空：not null
      • 唯一性：unique
      • 默认：default
      • 外键：foreign key

    • 示例：创建用户信息表

      CREATE TABLE user_info (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(20) NOT NULL,
  age tinyint(4) NOT NULL,
  phone_num char(11) NOT NULL,
  gender bit(1) DEFAULT b'1',
  create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `phone_num` (`phone_num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户信息表';

      create_time和update_time的值由数据库帮我们自动维护

  • 修改表的结构

    • 添加字段

      alter table 表名 add 字段名 类型;

    • 修改字段名

      alter table 表名 change 原字段名 新字段名 新字段类型;

    • 修改字段类型

      alter table 表名 modify 字段名 新类型；

    • 删除字段

      alter table 表名 drop 字段名;

  • 查看表结构

    desc 表名;

  • 查看表的创建语句

    show create table 表名;

  • 更改表名

    rename table 原表名 to 新表名;

  • 删除表

    drop table 表名;

五、数据操作

  • 查询

    • 基本语法

      select * from 表名;
      • from关键字后面写表名，表示数据来源于是这张表
      • select后面写表中的列名，如果是*表示在结果中显示表中所有列
      • select后面的列名部分，可以使用as为列起别名(as也可以省去)，这个别名出现在结果集中
      • 如果要查询多个列，之间使用逗号分隔

    • 消除重复行

      • 在select后面列前使用distinct可以消除重复的行 
        select distinct gender as '性别' from user_info;
      • 如果distinct后面有多个列，那么只有多个列整行的内容都相同时才会消除重复 
        select distinct id,gender from user_info; #由于id不可能相同，所以不会消除重复
      • 如果消除重复的语句后面有根据字段排序，则排序的字段也需要列在distinct后面（注：排序在后面有介绍） 
        select distinct name from user_info order by gender desc; #错误写法
select distinct name，gender from user_info order by gender desc; #正确写法

    • 条件

      • 使用where子句对表中的数据筛选，结果为true的行会出现在结果集中 
        select * from 表名 where 条件;
      • 比较运算符
        • 等于：=
        • 大于：>
        • 大于等于：>=
        • 小于：<
        • 小于等于：<=
        • 不等于：!=
        • 示例： 
          select * from students where isdelete=0; #查询没被删除的学生
select * from students where id>3; #查询编号大于3的学生
select * from subjects where id<=4; #查询编号不大于4的科目
select * from students where sname!='黄蓉'; #查询姓名不是“黄蓉”的学生
      • 逻辑运算符
        • and
        • or
        • not
        • 示例： 
          select * from students where gender=0 and id>3; #查询编号大于3的女同学
select * from students where isdelete=0 or id<4; #查询编号小于4或没被删除的学生
      • 正则表达式
        • regexp
          • 匹配模式
            • ^：匹配文本的开始字符
            • $：匹配文本的结束字符
            • .(点)：匹配任意单个字符
            • 其它略，参照正则
            • 示例： 
              # 匹配name字段以'张'开头的数据
select * from my_table where name regexp '^张';

# 匹配name字段以'伟'结尾的数据
select * from my_table where name regexp '伟$';

# 匹配name字段包含'红'的数据
select * from my_table where name regexp '红';

# 匹配name字段包含'红'或者'岩'的数据
select * from my_table where name regexp '红|岩';

# 匹配name字段以'汪'、'姜'开头的数据
select * from my_table where name regexp '^['汪','姜']';

              需要注意的是，传入具体的字符串，则匹配包含该字符串的结果

      • 模糊查询
        • like
        • %表示任意多个任意字符，如果需要查询'%'这个字符，需要在前面加上'\'，即'\%'
        • _表示一个任意字符
        • 示例： 
          select * from students where sname like '黄%'; #查询姓黄的学生
select * from students where sname like '黄_'; #查询姓黄并且名字是一个字的学生
select * from students where sname like '黄%' or sname like '%靖%'; #查询姓黄或者名字中含有靖的学生
      • 范围查询
        • in表示在一个非连续的范围内
        • between ... and ...表示在一个连续的范围内
        • 示例： 
          select * from students where id in(1,3,8); #查询编号是1，3，8的学生
select * from students where id between 3 and 8; #查询编号是3-8的学生
      • 空判断
        • 注意：null与空字符串''是不同的
        • 判空is null
        • 判非空is not null
        • 示例： 
          select * from students where hometown is null; #查询没有填写地址的学生
select * from students where hometown is not null and gender=0; #查询填写了地址的女生

    • 聚合

      • 聚合能够快速得到统计数据，以下提供了5个聚合函数：

        • count(*)：表示计算总行数，等同于 count(字段)
        • max(字段)：表示求此字段的最大值
        • min(字段)：表示求此字段的最小值
        • sum(字段)：表示求此字段的和
        • avg(字段)：表示求此字段的平均值
        • 示例： 
          select count(*) from students; #查找学生总数
select max(age) from students where gender=0; #查询女生最大年龄
select min(id) from students where isdelete=0; #查询未删除的学生最小编号
select sum(id) from students where gender=1; #查询男生的编号之和
select avg(age) from students where gender=1; #查询男生的平均年龄

    • 分组

      • 按照字段分组，表示此字段相同的数据会被放到一个组中，实质是合并行，分组的目的一般是为了进行数据统计，做聚合运算
      • 语法： 
        select 列1,列2,聚合... from 表名 group by 字段1,字段2,字段3...
      • 示例： 
        select gender as 性别, count(*) as 人数 from students group by gender; #查询男女生人数
      • 分组后数据筛选
        • 语法 
          select 列1，...聚合... from 表名 group by 字段1，...having 条件;

          having后面的条件运算符与where的相同，但是选择集不同，where是对from后面指定的表进行数据筛选，属于对原始数据的筛选，having是对group by的结果进行筛选

        • 示例： 
          select gender as 性别, count(*) as 总数 from students group by gender having gender=1; #查询男生人数

    • 排序

      • 为了方便查看数据，可以对数据进行排序
      • 语法 
        select * from 表名 order by 字段1 asc|desc,字段2 asc|desc,...
        • 将行数据按照字段1进行排序，如果字段1的值相同时，则按照字段2排序，以此类推
        • asc：从小到大排列，即升序，默认就是按照这种方式排列
        • desc：从大到小排序，即降序
      • 示例： 
        select * from students where gender=1 order by id desc; 按学号降序查询男生信息

    • 获取部分行数据

      • 当数据量过大时，在一页中查看数据是一件非常麻烦的事情
      • 语法 
        select * from 表名 limit start,count;

        从start开始，获取count条数据，start索引从0开始，如果不写start，则默认start=0，表示从第1个数据开始，获取count条

      • 分页功能的实现
        • 比如每页有m条数据，显示第n页的数据 
          select * from 表名 where... limit (n-1)*m, m；

          如果第n页数据不足m条，则显示当前页的实际条数

    • 总结

      • 完整的查询语句： 
        select distinct *
from 表名
where ....
group by ... having ...
order by ...
limit start,count;

        实际使用中，只是语句中某些部分的组合，而不是全部

  • 插入

    • 全列插入单条数据

      insert into 表名 values(0,...);

      主键列是自增的，但是在全列插入时需要占位，通常使用0，插入成功后自动会添上正确的数值

    • 全列插入多条数据

      insert into 表名 values(0,...),(0,...)...;

    • 缺省插入单条数据

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

    • 缺省插入多条数据

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

  • 修改

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

  • 删除

    delete from 表名 where 条件;

  • 逻辑删除（本质上是修改操作）

    # 通常会增加一个字段isdelete，默认为0
alter table 表名 add isdelete bit default 0;
# 如果需要删除则修改isdelete=1
update 表名 set isdelete=1 where ...;

六、外键

  • 认识外键

    • 在存储关系的表中添加字段，为该字段创建外键来引用另一个表的主键，从而建立关系
    • 通过外键约束进行数据的有效性验证，当添加或修改的数据不是引用另一个表的主键时，则报错

  • 创建外键

    • 创建表的时候直接创建约束

      create table scores(
id int primary key auto_increment,
stuid int,
subid int,
score decimal(5,2),
foreign key(stuid) references students(id),
foreign key(subid) references subjects(id)
);

    • 添加外键约束

      alter table 表名 add constraint 约束名 foreign key(字段名) references 主表名(id); #其中约束名可以任意起
alter table scores add constraint stu_sco foreign key(stuid) references students(id);

  • 删除外键

    • 先查找出约束名

      show create table 表名;

    • 根据约束名进行删除

      alter table 表名 drop foreign key 约束名;

  • 外键的级联操作

    • 级联操作的类型包括:

      • restrict（限制）：默认值，抛异常
      • cascade（级联）：如果主表的记录删掉，则从表中相关联的记录都将被删除
      • set null：将外键设置为空
      • no action：什么都不做

    • 删除主表中数据，如果该数据的id在从表中已经存在，则会抛异常，解决办法：

      • 使用逻辑删除(推荐)
      • 修改外键的级联操作 
        alter table 表名 add constraint 约束名 foreign key(字段名) references 主表名(id) on delete 级联操作类型;
alter table scores add constraint stu_sco foreign key(stuid) references students(id) on delete cascade;#主表数据删掉，从表中相关联记录都将删除

七、连接查询

当查询的结果来源于有关系的多张表时，需要使用到连接查询

  • 连接查询分类：

    • 内连接（inner join）：表A inner join 表B，表A和表B都匹配的行出现在结果集当中（不区分两个表的先后顺序）
    • 左连接（left join）：表A left join 表B，表A与表B都匹配的行会出现在结果中，外加表A中独有的数据，未对应的数据使用null填充（区分先后顺序）
    • 右连接（right join）：表A right join表B，表A与表B都匹配的行会出现在结果中，外加表B中独有的数据，未对应的数据使用null填充（区分先后顺序）

  • 连接查询中字段名和表名的使用：

    • 在查询或条件中推荐使用“表名.字段名”的语法
    • 如果多个表中字段名不重复，则可以省略"表名."部分，直接使用"字段名"
    • 如果表的名称太长，可以取个简写别名，表名后面使用"as 简写名"或者省去as，直接跟上“简写名”

  • 示例：

    • 查询学生的姓名、总分

      select students.sname 姓名,sum(scores.score) 总分
from scores
inner join students on scores.stuid=students.id
group by students.sname;
    • 查询每个学生每个科目的成绩 
      select students.sname 姓名,subjects.stitle 科目,scores.score 成绩
from scores
inner join students on scores.stuid=students.id
inner join subjects on scores.subid=subjects.id;

八、自关联

  • 什么是自关联

    • 表中的某一列，通过外键引用了本表的另外一列（主键），但是它们的业务逻辑含义又是不一样的，这就是自关联
    • 物理上一张表，逻辑上是多张表，必须通过取别名来区分，能够节省表的开销

  • 应用场景

    • 比如设计了这样的两张表：省(provinces)信息的表结构（id,ptitle）和市（cities）信息的表结构（id,ctitle,pid），其中pid对应着provices表的id。通过比较发现，cities表比provinces表多一个列pid，其它列的类型都是一样的，存储的都是地区信息，而且每种信息的数据量有限，因此没必要增加一个新表，或者将来还要存储区、乡镇信息，都增加新表的开销太大。我们可以重新设计一个地区(areas)信息的结构（id,atitle,pid），因为省没有所属的省份，所以pid可以填写null，城市所属的省份pid可以填写省所对应的id。在这个表中，结构不变，还可以添加区县、乡镇街道、村社区等信息。

  • 示例：

    • 查询山西省的所有市名 
      select province.atitle 省名,city.atitle 市名 
from areas as province 
inner join areas as city on province.id = city.pid 
where province.atitle = '山西';

  • 总结

    • 对于省市区、商品分类等这种结构上相同(或相近)，并且具有包含关系的多张表，我们可以按照自关联的方式给设计成一张表，能够节省表的开销

九、子查询

  • 子查询

    • 嵌套在其它查询中的查询

  • 分类：

    • where型子查询：

      • 把子查询的结果当做外层查询的一个比较条件，单个值前面使用比较运算符，多个值使用in
      • 查询山西省的所有市名 
        select atitle 市名 from areas where pid = (select id from areas where atitle = '山西省');
      • 查询男女生编号最大的学生信息 
        select * from students where id in (select max(id) from students group by gender);

    • from型子查询：

      • 把子查询的结果当做临时表，需要取别名
      • 查询所有男生的信息，并且给每条信息添上行号 
        select @rownum:=@rownum+1 rownum,name,age from (select name,age from students where gender=1) stu,(select @rownum:=0) row;

        注：@变量名:=value是给变量赋值，from后面两个临时表用','隔开，表示全相乘

    • exists型子查询：

      • 首先执行外层查询，再执行内层查询，如果内层查询结果为真，则取出该行，以此类推，直到整个表查询结束
      • 查询商品分类表中有商品的分类信息 
        select name from category as cat where exists(select 1 from goods where goods.cat_id = cat.id );

    • 计算字段型：

      • 首先执行外层查询，再执行内层查询，如果内层查询到结果，则显示在结果行中，未查询到，则该字段查询结果为NULL
      • 查询各学生的数学成绩 
        select name as 姓名,(select score from scores inner join subjects on scores.sub_id = subjects.id where subjects.title = '数学' and stu_id = stu.id) as '数学' from students stu;

  • 总结：

    • 能够使用连接查询，就不要使用子查询，因为子查询会增加查询的次数

十、视图

  • 视图

    • 对于复杂的查询，在多次使用后维护起来比较麻烦，我们可以通过定义视图的方式来解决。
    • 视图的本质就是对复杂的查询语句进行封装，相当于生成了一个新的表，它的用途就是查询。

  • 创建视图

    create view 视图名 as select ...

    通常视图名以v_开头便于区分

  • 查看视图

    show tables;

    同查看表的操作相同

  • 修改视图

    alter view 视图名 as select ...;

  • 使用视图

    select * from 视图名;

十一、事务

  • 事务

    • 当一个业务需要多个操作步骤(insert ino | update | delete from)时，只有所有步骤都完成才算完成，如果其中某条sql语句出错，则整个操作都退回。
    • 使用事务可以完成退回的功能，从而保证业务逻辑的正确性。
    • 事务的本质是：上锁

  • 事务的四大特性：

    • 原子性(Atomicity)：事务中的全部操作在数据库中是不可分割的，要么全部完成，要么均不执行
    • 一致性(Consistency)：几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致
    • 隔离性(Isolation)：事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的
    • 持久性(Durability)：对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障

  • 要求：

    • 表的引擎类型必须是innodb或bdb类型

  • 修改表的类型：

    • 查看表的创建语句，来查看表的引擎类型 
      show create table 表名;
    • 修改表的类型 
      alter table 表名 engine=innodb;

  • 事务操作步骤

    • begein：开启事务，并在内存级临时表中进行增、删、改等操作
    • commit：将临时表中的数据更新到原表中，确认更改，释放锁
    • rollback：放弃begin后面的所有操作，释放锁
      • 注意：commit和rollback操作2选1

  • 示例：

    begin;
insert into students(name,age,gender) values('张三',25,1);
update students set age=28 where name='李四';
delete from students where name='王五';
commit; #或者rollback;

十二、索引

  • 索引

    • 针对数据库中现有的数据，整理出一块物理存储下来的用于快速查找数据用的的东西。
    • 数据库中实际数据的存储会按照某种索引，默认是按照主键（特殊的索引），并且数据物理结构上的存储顺序也是按照主键的顺序。
    • 索引的创建会增大物理上的开销。

  • 创建索引时如何选择字段数据类型？

    • 数据类型小：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。能用bit类型，不用int，能用int，不用decimal.
    • 数据类型简单：整形数据比起字符，开销更小
    • 尽量避免NULL：含有NULL的列，很难进行查询优化，你可以用0，空字符串' '或者一个特殊的值去代替。

  • 什么时候考虑创建索引？

    • 一开始做项目的时候，通常不会去考虑对数据库创建索引这件事，因为没有大量的数据作为基础，建立索引区别不大。当系统上线运营了一段时间后，数据库中数据量达到一定规模时我们可以进行监测，当发现检索速度变慢（比如大于100ms）时，我们就需要去分析业务逻辑中sql语句where后面的判断字段，针对这些字段建立索引进行优化。

  • 建立索引对sql语句的编写有什么要求？

    • 将'='值的条件尽量写在前面，表示范围的比如'>'尽量写在后面，尽量避免使用'or'，否则建立索引对优化查询速度已经没有意义了

  • 索引操作：

    • 建立索引

      create index 索引名 on 表名(字段名1,...);

      如果只有一个字段名，表示建立单列索引；多个字段名，表示建立组合索引。如果字段为字符类型，可以在字符后面指定长度，比如students(name(20),age)，也可以不指定，其他类型(int，bit)的字段不需要指定长度

    • 查看索引

      show index from 表名;

    • 删除索引

      drop index 索引名 on 表名;

    • unique索引

      # 创建unique索引
create unique index 索引名 on 表名(字段1,字段2...);

# 删除unique索引
drop index 索引名 on 表名;

      删除unique索引时，和普通索引操作一样

  • 索引缺点

    • 虽然索引会提升查询的速度，但同时会降低更新表（insert into|delete from|update）的速度，因为更新表的时候还要保存一下索引文件
    • 建立索引，会占用磁盘空间的索引文件

  • 性能监测工具profiles的使用

    • 仅限于当前数据库的本次连接，下次连接失效

    • 示例：

      set profiling = 1; #开启监测工具
select * from areas where atitle = '北京市'; #执行查询语句
show profiles; #查看查询执行时间
create index title_index on areas(atitle); #为atitle字段创建索引
select * from areas where atitle = '北京市'; #再次执行查询语句
show profiles; #再次查看执行时间

十三、与python交互

  • 安装及引入模块

    • 安装PyMySQL模块

      pip3 install PyMySQL

    • 脚本中引入模块

      import pymysql

  • Connection对象

    • 用于建立与数据库的连接

    • 创建对象：

      • 调用pymysql.connect（）方法 
        conn = pymysql.connect(host="主机",port=3306,user="用户名",passwd="密码",db="数据库名",charset="utf8")

        如果是本机，host填'localhost'，port端口号，默认3306，charset字符编码，默认是'gb2312'，要求与数据库创建时指定的编码一致，否则中文会乱码

      • 方法：
        • commit()
          • 提交事务，使操作生效，在执行select查询任务时，则不需要这一步
        • rollback()
          • 回滚事务，放弃之前的操作
        • cursor()
          • 创建cursor对象，用于执行sql语句
        • close()
          • 关闭连接
        • ping()
          • 是否重连
          • 参数reconnect：默认为True，表示重连
      • 属性
        • open
          • 可以判断连接是否关闭，没有关闭，则返回True，否则返回False

  • Cursor对象

    • 用于执行sql语句，默认开启了事务（begin）

    • 创建对象：

      • 调用Connection对象的cursor（）方法 
        cur = conn.cursor()

# 也可以传入参数pymysql.cursors.DictCursor，获取dict类型的查询结果
cur2 = conn.cursor(pymysql.cursors.DictCursor)

    • 方法：

      • execute(sql,[,parameters])
        • 执行语句，返回受影响的行数，第二个参数可选，元组或列表类型，参数化可以防止sql注入风险，sql语句中使用%s作为通用占位符，可以表示任何数据类型。
      • executemany(sql,[xx,xxx])
        • 批量操作，通常是批量插入 
          data = [
    ('Jane', '2005-02-12'),
    ('Joe', '2006-05-23'),
    ('John', '2010-10-03')
]
cursor.executemany("INSERT INTO employees (first_name, hire_date) VALUES (%s, %s)", data)
        • 批量更新，不常见 
          data = [
    (1, 'new_value1'),
    (2, 'new_value2'),
    (3, 'new_value3')
]
cursor.executemany("UPDATE table SET column = %s WHERE id = %s", data)
      • fetchone()
        • 默认情况下，执行查询语句时，获取查询结果集的第一个行数据，返回一个元组，未查到，则返回None。如果结果集不止一条数据，可以再次执行fetchone()获得第二个行数据，依此类推
        • 如果使用pymysql.cursors.DictCursor，有则返回dict类型数据，否则返回None
      • fetchall()
        • 执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回，未查到，则返回空元组()。可以与fetchone（）方法搭配使用，比如可以先fetchone（）查询一条数据，然后执行fetchall（）查询剩下所有数据。
        • 如果使用pymysql.cursors.DictCursor，有则返回list类型，元素为dict，即[dict1, dict2...]，没有则返回空元组()；
      • scroll(value[,mode])
        • 将行指针移动到某个位置。mode表示移动的方式，默认值为'relative'，value表示基于当前行向上(取负值)或向下(取正值)移动的行数。当mode的值为‘absolute’，value表示基于第一行数据(位置为0)向下移动的行数。
      • close()
        • 关闭cursor

    • 属性：

      • lastrowid：返回insert操作后，最后一条数据的id值
      • description
        • 返回表中各字段描述信息，以元组套元组的形式展示，比如： 
          (('id', 8, None, 20, 20, 0, False), ('create_time', 7, None, 19, 19, 0, False), ('update_time', 7, None, 19, 19, 0, False))
      • rowcount：只读属性，表示最近一次execute()执行后受影响的行数
      • connection：获得当前连接对象

  • 连接失败后自动重连

    • 先根据connection对象的open属性，判断连接是否正常，连接失败，则调用ping()方法，实现自动重连，不需要重新实例化cursor对象 
      if not con.open:
    con.ping() # reconnect参数默认：True

  • 增、删、改、查操作

    • 增、删、改操作大致流程

      • 导入模块：import pymysql
      • 创建connection对象，与数据库建立连接：con = pymysql.connect(...)
      • 创建cursor对象：cur=con.cursor()
      • 执行增、删、改sql语句（可以连续执行多条）：cur.execute(sql)
      • ......
      • 提交或回滚事务：con.commit()或者con.rollback()
      • 关闭cursor：cur.close()
      • 关闭connection连接：con.close()

    • 查找操作大致流程

      • 导入模块：import pymysql
      • 创建connection对象，与数据库建立连接：con = pymysql.connect(...)
      • 创建cursor对象：cur=con.cursor()
      • 执行sql语句（可以连续执行多条）：cur.execute(sql)
      • 获取查询结果：cur.fetchone()或者cur.fetchall()
      • 关闭cursor：cur.close()
      • 关闭connection连接：con.close()

    • 比较查找操作和增删改操作的区别（多一步，少一步）：

      • 多一步：查找操作在执行sql语句后，需要通过fetchone()或者fetchall()方法来获取查询结果
      • 少一步：查找操作不需要提交或回滚事务

    • 示例：

      • 增加、修改、删除一条学生表数据 
        # coding = utf-8

import pymysql

con = pymysql.connect(host='localhost',port=3306,user='root',password='123',db='test',charset='utf8') # 创建connect对象
cur = conn.cursor() #创建cursor对象
name = input('请输入添加的学生姓名:')
sql1 = 'insert into students(name) values(%s);'
sql2 = 'delete from students where name = "老王";'
sql3 = 'update students set name = “老张” where name = "老李";'
try:
    cur.execute(sql1,[name]) #增加一条数据
    cur.execute(sql2) #删除一条数据
    cur.execute(sql3) #修改一条数据
except Exception as e:
    print(e)
    con.rollback() # 放弃之前的所有操作
else:
    con.commit() # 提交，使所有操作生效
cur.close() # 关闭cursor对象
con.close() # 关闭连接对象
      • 查找学生信息 
        # coding = utf-8

import pymysql

con = pymysql.connect(host='localhost',port=3306,user='root',password='123',db='test',charset='utf8') # 创建connect对象
cur = conn.cursor() #创建cursor对象
try:
    sql = 'select * from students;'
    count = cur.execute(sql)
    data1 = cur.fetchone() #查找第一行数据
        print(data1)
    data2 = cur.fetchall() # 查找剩下所有行数据
        print(data2)
except Exception as e：
    print(e)
cur.close()
con.close()
posted @ 2021-09-01 18:19  eliwang  阅读(269)  评论(0)  编辑  收藏  举报