MySQL

/usr/local/mysql/bin/mysql -uroot -p12341234
 PATH=“$PATH”:/usr/local/mysql/bin
mysql -uroot -p12341234  

mac下操作

 mysql 命令。      -u 后面接用户名
    root超级管理员，拥有最高权限。
    -p 后面接密码

打开终端输入如下命令：  /usr/local/mysql/bin/mysql -u root -p 或者

/usr/local/mysql/bin/mysql -u root -p -h127.0.0.1

（注意：Windows下的是：  mysql -u root -p）

其中root为用户名。这时会出现如下命令：Enter password:  ******(，也可以不输入密码直接回车；这个密码mysql服务安装好后会分配一个密码，后期可以自己改)
断开数据库连接：quit或者exit或者\q；


****设置别名：

1)alias mysql=/usr/local/mysql/bin/mysql
2)alias mysqladmin=/usr/local/mysql/bin/mysqladmin

*********修改密码，把12341234换成自己的密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '12341234';

mysql的操作：改端口号；终端进入

#一、从终端进入mysql
不同于windows下的mysql。mac下的mysql安装路径不同，所以操作上会略有不同；
##(1):打开终端后，先设置路径，后面就不用每步操作都指定路径了（大小写区分）：
输入：PATH=“$PATH”:/usr/local/mysql/bin
回车确认；再输入：mysql -uroot -p;（从此开始的操作都与windows版的一样）

##(2):简单版带上路径。 /usr/local/mysql/bin/mysql -uroot -p
 
 
 

#二、修改mysql端口号
 不同于windows 的my.ini，mac修改端口号在：
/Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist文件中,
该文件要用终端打开，终端输入指令：open /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist

打开文件后，在<array>字段下找到<string>--port=3307</string>，将后面的3307改为你的端口号，如果找不到这句话，那就自己加进去，保存退出即可

注意：修改之前先在“系统偏好设置”中停止mysql,修改完成后再开启mysql服务。

 

Mysql-python

import pymysql

 # 打开数据库连接
 db = pymysql.connect("localhost","root","04120412","p5" )
 # 使用 cursor() 方法创建一个游标对象 cursor
 cursor = db.cursor()
 # 使用 execute()  方法执行 SQL 查询 
 cursor.execute("show tables")
 # 使用 fetchone() 方法获取单条数据.
 data = cursor.fetchone()
 print("Database version : %s " % data)
 # 关闭数据库连接
 db.close()Ss

一、关系型数据库

关系型数据库最典型的数据结构是表，由二维表及其之间的联系所组成的一个数据组织

优点：

1、易于维护：都是使用表结构，格式一致； 2、使用方便：SQL语言通用，可用于复杂查询； 3、复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询。

缺点：

1、读写性能比较差，尤其是海量数据的高效率读写；

2、固定的表结构，灵活度稍欠；

3、高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。

nodejs连接MySQL数据库

1. 建库连库
连接MySQL数据库需要安装支持

npm install mysql

我们需要提前安装按mysql sever端
建一个数据库mydb1
mysql> CREATE DATABASE mydb1;
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mydb1             |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

然后建一张表user如下
create table dbnode(id int not null primary key auto_increment,
    -> passwd VARCHAR(100) not null,
    -> name VARCHAR(100) not null,
    -> )ENFINE=InnoDB DEFAULT CHARSET = utf8;


create table user(  
id int not null primary key auto_increment,  
name VARCHAR(100) not null,  
pwd VARCHAR(100) not null,
create_date TIMESTAMP NULL DEFAULT now()
)ENGINE=InnoDB DEFAULT CHARSET=utf8;  
CREATE UNIQUE INDEX t_quiz_IDX_0 on user(name);
复制代码
接下来我们利用nodejs连接mysql数据库

var mysql  = require('mysql');  //调用MySQL模块
//创建一个connection
var connection = mysql.createConnection({
    host: 'localhost',       //主机
    user: 'root',               //MySQL认证用户名
    password: '12341234',        //MySQL认证用户密码
    database: 'p5',
    port: '3306'                   //端口号
});
// 创建一个connection
connection.connect(function(err){
    if(err){
        console.log('[query] - :'+err);
        return;
    }
    console.log('[connection connect]  succeed!');
});
// ----插入
var userAddSql = 'insert into user (name,pwd) values(?,?)';
var param = ['fff','123'];
connection.query(userAddSql,param,function(err,rs){
    if(err){
        console.log('insert err:',err.message);
        return;
    }
    console.log('insert success');
});
//执行查询
connection.query('SELECT * from user where id=?',[2], function(err, rs) {
    if (err) {
        console.log('[query] - :'+err);
        return;
    }
    for(var i=0;i<rs.length;i++){
        console.log('The solution is: ', rs[i].uname);
    }
});

//关闭connection
connection.end(function(err){
    if(err){
        console.log(err.toString());
        return;
    }
    console.log('[connection end] succeed!');
});




但是实际每次创建连接都需要一定的开销，执行效率就会有影响。

连接池配置使用

下面介绍一种连接池连mysql的方法：node-mysql

2. 连接池配置使用
node-mysql是目前最火的node下的mysql驱动，是mysqlpool的一个模块。

下面的代码是提供一个连接池，getPool函数返回createPool创建的数据库连接池对象。

复制代码
 var mysql  = require('mysql');  //调用MySQL模块 
 function OptPool(){ 
    this.flag=true; //是否连接过 
    this.pool = mysql.createPool({     
        host: 'localhost',       //主机 
        user: 'root',               //MySQL认证用户名 
        password: 'root',        //MySQL认证用户密码 
       database: 'test', 
       port: '3306'                   //端口号 
    }); 
 
   this.getPool=function(){ 
         return this.pool; 
    } 
}; 
module.exports = OptPool; 
复制代码
下面的代码展示如何使用这个连接池，插入和查询的使用。需要注意的是conn.release(); //释放一个连接放回连接池 需要再操作结束后再执行，否则后面的数据库操作会报错。

 

复制代码
var OptPool = require('./models/OptPool');  
var optPool = new OptPool(); 
var pool = optPool.getPool(); 

//执行SQL语句 
//从连接池中获取一个连接
pool.getConnection(function(err,conn){ 
    //----插入 
    var userAddSql = 'insert into user (uname,pwd) values(?,?)'; 
    var param = ['eee','eee'];
    conn.query(userAddwww.tt951.comSql,param,function(err,rs){
        if(err){ 
            console.log('insert err:',err.message); 
           return; 
         } 
         console.log('insert success'); 
         //conn.release(); //放回连接池
     })
     //查询 
     conn.query('SELECT * from user', function(err, rs) { 
         if (err) { 
             console.log('[query] - :'+err); 
             return; 
         }   
         for(var i=0;i<rs.length;i++){
             console.log(rs[i].uname); 
             }
        conn.release(); //放回连接池
     });
 }); 
复制代码
下面介绍一个复杂一点的增删查改的数据库操作，因相互之间有依赖，所以代码可读性就变得特别差。这样就引出了我们接下来要介绍饿流程控制的内容《nodejs进阶(7)—async异步流程控制》

复制代码
var OptPool = require('./models/OptPool'); 
   
var optPool = new OptPool(); 
var pool = optPool.getPool(); 

var insertSQL = 'insert into table1(name,pwd) values("conan","123"),("fens.me","456")';
 var selectSQL = 'select * from table1 limit 10';
 var deleteSQL = 'delete from table1';
 var updateSQL = 'update table1 set name="conan update"  where name="conan"';

 pool.getConnectiwww.baiyuewang.neton(function(err,conn){ 
 //delete
    conn.query(deleteSQL, function (err0, res0) {
        if (err0) console.log(err0);
        console.log("DELETE Return ==> ");
         console.log(res0); 
        //insert
         conn.query(insertSQL, function (err1, res1) {
           if (err1) console.log(err1);
            console.log("INSERT Return ==> ");
             console.log(res1);
 
           //query
            conn.query(selectSQL, function (err2, rows) {
               if (err2) console.log(err2);

              console.log("SELECT ==> ");
             for (var i in rows) {
                   console.log(rows[i]);
              }

                //update
              conn.query(updateSQL, function (err3, res3) {
                   if (err3) console.log(err3);
                   console.log("UPDATE Return ==> ");
                     console.log(res3);

                    //query
                   conn.query(selectSQL, function (err4, rows2) {
                     if (err4) console.log(err4);
                        console.log("SELECT ==> ");
                       for (var i in rows2) {
                           console.log(rows2[i]);
                       }
                    });

MySQL

MySQL 教程

MySQL 官网

• 什么是数据库？

  数据库（Database）是按照数据结构来组织、存储和管理数据的仓库，

  每个数据库都有一个或多个不同的API用于创建，访问，管理，搜索和复制所保存的数据。

  我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。

  所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理的大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

• RDBMS即关系数据库管理系统(Relational Database Management System)的特点：

  1.数据以表格的形式出现

  2.每行为各种记录名称

  3.每列为记录名称所对应的数据域

  4.许多的行和列组成一张表单

  5.若干的表单组成database

• 数据库作用

  1) 持久保存

  2) 方便查询并提取满足条件的数据, 数据访问速度快

  3) 处理并发访问

  4) 权限管理

• 常见数据库:

  关系型数据库 MySQL ->库->表->数据 MySQL Oracle PostgreSQL SQL Server ….

  非关系型数据库 Redis ->数据 MongoDB … 概念：

  数据： data

  数据库： DB

  数据库管理系统：DBMS

  数据库系统：DBS

一、MySQL 介绍

数据库管理系统中, 可以有很多库, 每个数据库中可以包括多张数据表

• 操作数据库的步骤

  连接, 打开库, 操作, 关闭退出

• SQL ( Structure query language ) 结构化查询语言

  SQL语言分为4个部分：
      DDL（Data Definition Languages） :数据的定义
      DML（Data Manipulation Language）:数据的操作        
      DQL（Data Query Language）       :数据的查询操作
      DCL（Date Control Language）     :数据控制语句
  

• SQL语句中的快捷键

  \G 格式化输出（文本式，竖立显示）

  \s 查看服务器端信息

  \c 结束命令输入操作

  \q 退出当前sql命令行模式

  \h 查看帮助

• MySQL 语法特点

  1. SQL 语句可以换行, 要以分号结尾
  2. 命令不区分大小写. 关键字和函数建议用大写
  3. 如果提示符为 ‘> 那么需要输入一个’回车
  4. 命令打错了换行后不能修改, 可以用 \c 取消

• MySQL 存储引擎：

  存储引擎	描述
  ARCHIVE	用于数据存档（行被插入后不能再修改）
  BLACKHOLE	丢弃写操作，读操作会返回空内容
  CSV	在存储数据时，以逗号分隔各个数据项
  FEDERATED	用来访问远程表
  InnoDB	具备外键支持功能的事务存储引擎
  MEMORY	置于内存的表
  MERGE	用来管理多个MyISAM表构成的表集合
  MyISAM	主要的非事务处理存储引擎
  NDB	MySQL集群专用存储引擎

  • 查看当前服务器程序支持的存储引擎：

    SHOW ENGINES;
    

  • 创建表时指定存储引擎

    CREATE TABLE 表名(
        建表语句;
    ) ENGINE = 存储引擎名称;
    

  • 修改表的存储引擎

    ALTER TABLE 表名 ENGINE = 存储引擎名称;
    

  • 字符集的查看

    SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式];
    

二、MySQL 数据类型

1. 数值类型

2. 日期和时间类型

3. 字符串类型

三、MySQL 运算符

算术运算符：+ - * / %

比较运算符：= > < >= <= <> !=

数据库特有的比较：in，not in, is null,is not null,like, between and

逻辑运算符：and or not

like: 支持特殊符号%和_ ; 其中 %表示任意数量的任意字符，_表示任意一位字符。

四、MySQL 库操作

1. 查看数据库 show databases;
2. 创建数据库 create database p5 default charset=utf8;
3. 打开数据库 use 库名;
4. 查看当前库 select database();
5. 删除数据库 drop database 库名;

五、MySQL 表操作

1. 字段约束

unsigned : 无符号

int(4) : 显示宽度

zerofill : 零填充, 位数不够的时候用前导零填充

not null : 不能为空 在操作数据库时如果输入该字段的数据为NULL ，就会报错

default : 设置默认值

auto_increment : 定义列为自增的属性，一般用于主键，数值会自动加1。

primary key : 主键 不能为空 唯一性

unique : 唯一索引 可以为空 唯一性

index : 普通索引 可以为空 可以重复

2. 基本操作

查看当前库中所有表：show tables;

创建表 ：create table 表名(字段1 类型 [字段约束],字段2 类型 [字段约束]...)default charset=utf8;

查看表结构：desc 表名;

查看建表语句：show create table 表名\G

删除表：drop table 表名;

查看当前表中所有数据: select * from 表名;

3. 表数据操作

3.1 增删改

数据的DML操作：添加数据，修改数据，删除数据

• 插入：格式 insert into 表名[(字段列表)] values(值列表...);

  ​ 1. 标准添加: insert into 表名(所有字段) values(对应的值);

​ 2. 指定部分字段添加值: insert into 表名 (部分字段) value (对应的值);

​ 3. 不指定字段添加值: insert into 表名 value(值1,值2,...);

​ 4. 批量添加值: insert into 表名 values(a值1,a值2,...),(b值1,b值2,...);

• 删除：delete from 表名 where 字段=某个值;
• 修改：update 表名 set 字段=某个值 where 条件;

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

​ update 表名 set 字段=字段+值 where 条件;

3.2 查询

SELECT子句及其顺序：

select -> from -> where -> group by -> (having) -> order by -> limi

数据的DQL操作：数据查询

 select [字段列表]  *  from 表名
 [where 搜索条件]
 [group by 分组字段 [having 子条件]]
 [order by 排序 asc|desc]
 [limit 分页参数];

• 检索所有列 : select * from 表名;

• 限定表名

  select name from user;
  select user.name from user;
  select user.name from itxdl.user;
  

• where条件查询

  • 你可以在 WHERE 子句中指定任何条件。
  • 你可以使用 AND 或者 OR 指定一个或多个条件。
  • WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
  • WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。

  查询lamp138和lamp94期的女生信息
  mysql> select * from stu where classid in('lamp138','lamp94') and sex='w';
    
    
  

• like子句

  • LIKE 通常与 % 一同使用，类似于一个元字符的搜索。
  • 你可以使用 AND 或者 OR 指定一个或多个条件。
  • 你可以在 DELETE 或 UPDATE 命令中使用 WHERE…LIKE 子句来指定条件。
  • 可以使用regexp正则来代替 like
  • 百分号（%）通配符 在搜索串中，%表示任何字符出现任意次数
  • 下划线（_）通配符 下划线的用途与%一样，但下划线只匹配单个字符而不是多个字符

  查询name字段值是以zh开头的所有信息
  mysql> select * from stu where name like "zh%";
  select name from user where name regexp '[0-5]abc'
    
  

  • 注意:

    不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。

    在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。

    把通配符置于搜索模式的开始处，搜索起来是最慢的。

    仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据

• **拼接 Concat , 别名 AS **

  把两个列拼接起来。在MySQL的SELECT语句中，可使用Concat()函数来拼接两个列

  select concat(vend_name,'(',vend_country')') as vend_title from vendors order by vend_name
    
    
  

• 函数的使用

  • 文本处理函数

    函数	说明
    Left()	返回串左边的字符
    Length()	返回串的长度
    Locate()	找出串的一个子串
    Lower()	将串转换为小写
    LTrim()	去掉串左边的空格
    Right()	返回串右边的字符
    RTrim()	去掉串右边的空格
    SubString()	返回子串的字符
    Upper()	将串转换为大写
    Concat	拼接字符串

  • 日期和时间处理函数

    函数	说明
    AddTime()	增加一个时间（时、分等）
    CurDate()	返回当前日期
    CurTime()	返回当前时间
    Date()	返回日期时间的日期部分
    DateDiff()	计算两个日期之差
    Date_Add()	高度灵活的日期运算函数
    Date_Format()	返回一个格式化的日期或时间串
    Day()	返回一个日期的天数部分
    DayOfWeek()	对于一个日期，返回对应的星期几
    Hour()	返回一个时间的小时部分
    Minute()	返回一个时间的分钟部分
    Month()	返回一个日期的月份部分
    Now()	返回当前日期和时间
    Second()	返回一个时间的秒部分
    Time()	返回一个日期时间的时间部分
    Year()	返回一个日期的年份部分

  • 数值处理函数

    函 数	说 明
    Abs()	返回一个数的绝对值
    Cos()	返回一个角度的余弦
    Exp()	返回一个数的指数值
    Mod()	返回除操作的余数
    Pi()	返回圆周率
    Rand()	返回一个随机数
    Sin()	返回一个角度的正弦
    Sqrt()	返回一个数的平方根
    Tan()	返回一个角度的正切

• MySQL的统计函数(聚合函数):

  select max(),min(),sum()avg(),count(*) from 表名;
    
    
  

  函数	说明
  COUNT()	返回某列的行数
  MAX()	返回某列的最大值
  MIN()	返回某列的最小值
  SUM()	返回某列值之和
  AVG()	返回某列的平均值

• Case 函数

  Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。 Case when 相当于一个自定义的数据透视表，group by 是行名，case when 负责列名。

  SELECT country, 
    SUM( CASE WHEN sex = '1' THEN  population ELSE 0 END),  --男性人口 
    SUM( CASE WHEN sex = '2' THEN  population ELSE 0 END)   --女性人口
    FROM  Table_A  GROUP BY country; 
    
    
  

• GROUP BY 语句 分组 和 having

  • GROUP BY 语句根据一个或多个列对结果集进行分组。
  • 在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
  • having 对分组后的数据进行删选

  MySQL> select classid,sex,count(*) from stu  group by classid,sex;
    
  

• ORDER BY 排序 —— asc 升序 / desc 降序

  • 可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
  • 可以设定多个字段来排序。
  • 可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认为asc 按升序排列
  • 可以添加 WHERE…LIKE 子句来设置条件。

  SELECT field1, field2,...fieldN table_name1, table_name2...
  ORDER BY field1, [field2...] [ASC [DESC]]
    
  

• limit 关键字 查询部分数据

  • limit m; 查询数据只显示前m条
  • limit m,n; 排除前m条，然后再查询出前n条

• DISTINCT 检索不同的行

  • 此关键字指示MySQL只返回不同的值

  • DISTINCT关键字应用于所有列而不仅是前置它的列。

    例如: select distinct a,b,c from tableA; 等同于 select a,b,c from tableA group by a,b,c

• 组合查询 UNION

  MySQL也允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。

  这些组合查询通常称为并（union）或复合查询（compound query）。

  • UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个UNION关键字）。
  • UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）
  • 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。

  --使用union将两个sql一并执行
   select vend_id,prod_id,prod_price from products where prod_price <= 5 union select vend_id,prod_id,prod_price from products where vend_id in(1001,1002);
    
    
  

4. 表结构操作

格式: alter table 表名 action(更改选项);

添加字段：alter table 表名 add 字段名 类型 [字段约束];

删除字段：alter table 表名 drop 字段名;

修改字段：change 可改字段名和类型; modify 可改字段类型, 不可改字段名
		alter table 表名 change 原字段名 修改后的字段名 类型 [字段约束];
        alter table 表名 modify 字段名 修改后的类型 [字段约束];

添加唯一性索引：alter table user add unique uni_name(name);

添加普通索引：alter table user add index index_email(email);

删除索引：alter table user drop index index_email;

修改表名：alter table 表名 rename as 新表名；

更改auto_increment初始值：alter table 表名称 auto_increment=数值；

更改表类型：alter table 表名 engine=‘innodb’;

		  alter table 表名 engine='myisam';

5. MySQL 的表复制

• 复制表结构

  mysql> create table 目标表名 like 原表名;

• 复制表数据

  mysql> insert into 目标表名 select * from 原表名;

六、MySQL 高级

1. 查询

1.1 子查询

SQL还允许创建子查询（subquery），即嵌套在其他查询中的查询。

• 利用子查询进行过滤

   select cust_name,cust_contact 
   from customers 
   where cust_id in (select cust_id 
   					from orders 
   					where order_num IN (select order_num 
   										from orderitems 
   										where prod_id = 'TNT2'));
  +----------------+--------------+
  | cust_name      | cust_contact |
  +----------------+--------------+
  | Coyote Inc.    | Y Lee        |
  | Yosemite Place | Y Sam        |
  +----------------+--------------+
  --为了执行上述SELECT语句，MySQL实际上必须执行3条SELECT语句。
  --最里边的子查询返回订单号列表，此列表用于其外面的子查询的WHERE子句。
  --外面的子查询返回客户ID列表，此客户ID列表用于最外层查询的WHERE子句。
  --最外层查询确实返回所需的数据。
    
    
  

• 作为计算字段使用子查询

  select cust_name,
  		cust_state,
  		(select count(*) 
           	from orders
           	where orders.cust_id = customers.cust_id) as orders 
  from customers 
  order by cust_name;
  +----------------+------------+--------+
  | cust_name      | cust_state | orders |
  +----------------+------------+--------+
  | Coyote Inc.    | MI         |      2 |
  | E Fudd         | IL         |      1 |
  | Mouse House    | OH         |      0 |
  | Wascals        | IN         |      1 |
  | Yosemite Place | AZ         |      1 |
  +----------------+------------+--------+
    
    
  

1. 2 多表查询

• 嵌套查询

• where 关联查询

  -- 查询所有学生信息，并跨表显示对应的班级名称信息
  mysql> select s.*,c.name cname from stu s,classes c where s.classid=c.id
    
    
  

• join 连接查询（左联、右连、内联）

  INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。

  LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。

  RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

  -- 查询新闻信息，并补齐新闻类别信息
  mysql> select n.id,n.title,t.name from news n,ntype t where n.ntid=t.id;
  mysql> select n.id,n.title,t.name from news n inner join ntype t on n.ntid=t.id;
    
    
  

2. 事务

2.1 事务的特性

• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

2.2 事务处理方法

• BEGIN 开始一个事务
• ROLLBACK 事务回滚
• COMMIT 事务确认

2.3 事务的并发问题

• 脏读：读取到了没有提交的数据, 事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。
• 不可重复读：同一条命令返回不同的结果集（更新）.事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
• 幻读：重复查询的过程中，数据就发生了量的变化（insert， delete）。

2.4 事务的隔离级别

• 不同的隔离级别的锁的情况

1. 读未提交（RU）: 有行级的锁，没有间隙锁。它与RC的区别是能够查询到未提交的数据。
2. 读已提交（RC）：有行级的锁，没有间隙锁，读不到没有提交的数据。
3. 可重复读（RR）：有行级的锁，也有间隙锁，每次读取的数据都是一样的，并且没有幻读的情况。
4. 序列化（S）：有行级锁，也有间隙锁，读表的时候，就已经上锁了

• 隐式提交

  DDL（Data Define Language）：都是隐式提交。

  隐式提交：执行这种语句相当于执行commit; DDL

• 查看当前会话中事务的隔离级别

  mysql> select @@tx_isolation;
  +-----------------+
  | @@tx_isolation  |
  +-----------------+
  | REPEATABLE-READ |
  +-----------------+
  1 row in set, 1 warning (0.93 sec)
    
    
  

• 设置当前会话中的事务隔离级别

  mysql> set session transaction isolation level read uncommitted;
  Query OK, 0 rows affected (0.00 sec)
    
    
  

3. 索引

3.1 索引

• 索引的优缺点

  优势：可以快速检索，减少I/O次数，加快检索速度；根据索引分组和排序，可以加快分组和排序；

  劣势：索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间的数据表的1.5倍；索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表;

• 索引的分类

  常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引

  primary key : 主键 不能为空 唯一性

  unique : 唯一索引 可以为空 唯一性

  index : 普通索引 可以为空 可以重复

  FULLTEXT INDEX : 全文索引 用大文本对象的列构建的索引

  组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值 (在使用组合索引的时候可能因为列名长度过长而导致索引的key太大，导致效率降低，在允许的情况下，可以只取col1和col2的前几个字符作为索引)

// 使用alter创建
 alter table user add index index_name(字段值)
 alter table user add unique unique_name(字段值)
 alter table user add primary key(字段值)
 ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col')；
 ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；
 ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2（3))；
--表示使用col1的前4个字符和col2的前3个字符作为索引

 // 使用 alter删除索引
 alter table user drop index index_name     // 删除普通索引和唯一索引
 alter table user drop primary key       // 删除主键(必须先删除自动递增)

3.2 B+树索引

正常情况下，如果不指定索引的类型，那么一般是指B+Tree索引（或者B+Tree索引）。

存储引擎以不同的方式使用B+Tree索引。性能也各有不同，但是InnoDB按照原数据格式进行存储。

B+Tree 索引能够加快数据的读取速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，相反是从索引的根节点开始进行搜索，通过相应的指针移动，最终存储引擎要么找到了对应的值，要么该记录不存在。树的深度与表的大小直接相关。

B+Tree索引是按照顺序组织存储的，所以适合范围查找数据

B+Tree索引使用与全键值、键值范围或者键前缀查找，其中键前缀进适用于根据最左前缀的查找。

• B-Tree

  

• B+Tree

  

B+ 树的优点

• 磁盘读写代价更低
• 随机I/O的次数更少
• 查询速度更稳定

3.4 聚簇和非聚簇

在索引的分类中，我们可以按照索引的键是否为主键来分为“主索引”和“辅助索引”，使用主键键值建立的索引称为“主索引”，其它的称为“辅助索引”。因此主索引只能有一个，辅助索引可以有很多个。

MyISAM——非聚簇索引

MyISAM存储引擎采用的是非聚簇索引，非聚簇索引的主索引和辅助索引几乎是一样的，只是主索引不允许重复，不允许空值，他们的叶子结点的key都存储指向键值对应的数据的物理地址。

非聚簇索引的主索引和辅助索引的叶子节点的data都是存储的数据的物理地址，也就是说索引和数据并不是存储在一起的，数据的顺序和索引的顺序并没有任何关系，也就是索引顺序与数据物理排列顺序无关。

InnoDB——聚簇索引

聚簇索引的主索引的叶子结点存储的是键值对应的数据本身，辅助索引的叶子结点存储的是键值对应的数据的主键键值。因此主键的值长度越小越好，类型越简单越好。

聚簇索引的辅助索引的叶子节点的data存储的是主键的值，主索引的叶子节点的data存储的是数据本身，也就是说数据和索引存储在一起，并且索引查询到的地方就是数据（data）本身，那么索引的顺序和数据本身的顺序就是相同的；

4. 慢查询与优化

优化sql步骤：

1,慢查询日志

2,找出执行慢的sql语句

3,进行具体语句分析,优化或建立索引

4.1 慢查询

数据库中设置SQL慢查询

第一步.开启mysql慢查询

• 方式一：

  修改配置文件 在 my.ini 增加几行: 主要是慢查询的定义时间（超过2秒就是慢查询），以及慢查询log日志记录（ slow_query_log）

  

• 方法二：

  通过MySQL数据库开启慢查询

  • 开启慢查询 (如果需要长时间开启，则需要更改mysql配置文件)

    mysql> set global slow_query_log='ON';
    Query OK, 0 rows affected (1.70 sec)
    --注：如果想关闭慢查询日志，只需要执行 set global slow_query_log = off; 即可
        
    

  • 设置慢查询时间

     -- //设置“慢查询”的时间定义
    mysql> set long_query_time=2;
    Query OK, 0 rows affected (0.00 sec)
        
    

• 查看“慢查询”的配置信息

  mysql> show variables like "%slow%";
  +---------------------------+-----------------------------------------------+
  | Variable_name             | Value                                         |
  +---------------------------+-----------------------------------------------+
  | log_slow_admin_statements | OFF                                           |
  | log_slow_slave_statements | OFF                                           |
  | slow_launch_time          | 2                                             |
  | slow_query_log            | OFF                                           |
  | slow_query_log_file       | F:\mysql-5.7.25\data\DESKTOP-7TLR5VO-slow.log |
  +---------------------------+-----------------------------------------------+
  5 rows in set, 1 warning (0.00 sec)
    
  

4.2 Explain

根据表，列，索引和WHERE子句中的条件的详细信息，MySQL优化器会考虑许多技术来有效地执行SQL查询中涉及的查找。优化程序选择执行最有效查询的操作集称为“ 查询执行计划 ”，也称为 EXPLAIN计划。

EXPLAIN返回SELECT语句中使用的每个表的一行信息 。它按照MySQL在处理语句时读取它们的顺序列出输出中的表。

• EXPLAIN输出列 官方文档说明

  Column	JSON名称	含义
  id	select_id	该SELECT标识符
  select_type	没有	该SELECT类型
  table	table_name	输出行的表
  partitions	partitions	匹配的分区,通常不用
  type	access_type	连接类型
  possible_keys	possible_keys	可供选择的索引
  key	key	实际选择的指数
  key_len	key_length	所选键的长度
  ref	ref	列与索引进行比较
  rows	rows	估计要检查的行
  filtered	filtered	按表条件过滤的行的百分比
  Extra	没有	附加信息

  mysql> explain select * from orders where order_num = 20005\G;
  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: orders
     partitions: NULL
           type: const
  possible_keys: PRIMARY
            key: PRIMARY
        key_len: 4
            ref: const
           rows: 1
       filtered: 100.00
          Extra: NULL
  1 row in set, 1 warning (0.00 sec)
    
    
  

4.3 慢查询优化

1. 索引没起作用的情况

   ① 使用LIKE关键字的查询语句

   ​ 在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置索引才会起作用。

   ② 使用多列索引的查询语句

   ​ MySQL可以为多个字段创建索引。一个索引最多可以包括16个字段。对于多列索引，只有查询条件使用了这些字段中的第一个字段时，索引才会被使用。

2. 优化数据库结构

   合理的数据库结构不仅可以使数据库占用更小的磁盘空间，而且能够使查询速度更快。数据库结构的设计，需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

   ① 将字段很多的表分解成多个表

   对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

   ② 增加中间表

   ​ 对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

3. 分解关联查询

   将一个大的查询分解为多个小查询是很有必要的。

   ​ 很多高性能的应用都会对关联查询进行分解，就是可以对每一个表进行一次单表查询，然后将查询结果在应用程序中进行关联，很多场景下这样会更高效，例如：

   SELECT * FROM tag 
   JOIN tag_post ON tag_id = tag.id
   JOIN post ON tag_post.post_id = post.id
   WHERE tag.tag = 'mysql';
      
       分解为：
       
    SELECT * FROM tag WHERE tag = 'mysql';
    SELECT * FROM tag_post WHERE tag_id = 1234;
    SELECT * FROM post WHERE post.id in (123,456,567);
      
   

4. 优化LIMIT分页

   ​ 在系统中需要分页的操作通常会使用limit加上偏移量的方法实现，同时加上合适的order by 子句。如果有对应的索引，通常效率会不错，否则MySQL需要做大量的文件排序操作。

   ​ 一个非常令人头疼问题就是当偏移量非常大的时候，例如可能是limit 10000,20这样的查询，这是mysql需要查询10020条然后只返回最后20条，前面的10000条记录都将被舍弃，这样的代价很高。

   ​ 优化此类查询的一个最简单的方法是尽可能的使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会得到很大提升。

   对于下面的查询：

   select id,title from collect limit 90000,10;
      
   

   解决方法:

   • 虑筛选字段（title）上加索引

   • 先查询出主键id值

     select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;
          
     原理：先查询出90000条数据对应的主键id的值，然后直接通过该id的值直接查询该id后面的数据。
          
     

   • 方法三：“关延迟联”

     如果这个表非常大，那么这个查询可以改写成如下的方式：

     Select news.id, news.description 
     from news 
     inner join (select id from news order by title 	limit 50000,5) as myNew using(id);
          
     

     这里的“关延迟联”将大大提升查询的效率，它让MySQL扫描尽可能少的页面，获取需要的记录后再根据关联列回原表查询需要的所有列。这个技术也可以用在优化关联查询中的limit。

   • 方法四：建立复合索引 acct_id 和 create_time

     select * from acct_trans_log WHERE  acct_id = 3095  order by create_time desc limit 0,10
          
          
     

     注意sql查询慢的原因都是:引起filesort

5. 分析具体的SQL语句

   两个表选哪个为驱动表，表面是可以以数据量的大小作为依据，但是实际经验最好交给mysql查询优化器自己去判断。

5. 视图

视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）；

可以跟基本表一样，进行增删改查操作(ps:增删改操作有条件限制)；

• 创建视图:

  mysql> create view v_t1 as select * from t1 where id>4 and id<11;
  Query OK, 0 rows affected (0.00 sec)
    
    
  

• 查看视图:

  mysql> select * from view_name;
  mysql> show tables; --可以查看到所有的表和视图
    
    
  

• 删除视图v_t1:

  mysql> drop view v_t1;
    
    
  

• view视图的帮助信息:

  mysql> ? view
  ALTER VIEW
  CREATE VIEW
  DROP VIEW
    
    
  

6. 触发器

-- 查看所有的 触发器
show triggers\G;

-- 删除触发器
drop trigger trigger_name;

① 触发器创建语法

CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt

trigger_name：触发器的名称
tirgger_time：触发时机，为BEFORE或者AFTER
trigger_event：触发事件，为INSERT、DELETE或者UPDATE
tb_name：表示建立触发器的表明，就是在哪张表上建立触发器
trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句

所以可以说MySQL创建以下六种触发器：
BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE
AFTER INSERT,AFTER DELETE,AFTER UPDATE

其中，触发器名参数指要创建的触发器的名字

BEFORE和AFTER参数指定了触发执行的时间，在事件之前或是之后

FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器

② 创建有多个执行语句的触发器

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
    执行语句列表
END

其中，BEGIN与END之间的执行语句列表参数表示需要执行的多个语句，不同语句用分号隔开

tips：一般情况下，mysql默认是以 ; 作为结束执行语句，与触发器中需要的分行起冲突

　　 当触发器创建完成后，可以用DELIMITER ;来将结束符号变成;

mysql> DELIMITER ||
mysql> CREATE TRIGGER demo BEFORE DELETE
    -> ON users FOR EACH ROW
    -> BEGIN
    -> INSERT INTO logs VALUES(NOW());
    -> INSERT INTO logs VALUES(NOW());
    -> END
    -> ||
Query OK, 0 rows affected (0.06 sec)

mysql> DELIMITER ;

最后使用DELIMITER ; 将结束符号还原

tigger_event：

load data语句是将文件的内容插入到表中，相当于是insert语句，而replace语句在一般的情况下和insert差不多，但是如果表中存在primary 或者unique索引的时候，如果插入的数据和原来的primary key或者unique相同的时候，会删除原来的数据，然后增加一条新的数据，所以有的时候执行一条replace语句相当于执行了一条delete和insert语句。

创建一个deluser的触发器，当对用户表进行删除之前往del_user中添加一个数据

\d //
create trigger deluser before delete on user for each row
begin
insert into del_user values(old.id,old.username,old.age,old.email,old.sex,old.account);
end;
//

创建一个触发器，当对用户表中数据进行添加时，修改统计的数据

\d //
create trigger countuser after insert on user for each row
begin
update user_count set num = num+1;
end;
//

注意：

• 注意在触发器中的执行语句，要保证SQL能够正确执行
• 在创建一个insert类型触发器时，在触发器中无法使用old来获取原来的数据

7. 日志

开启bin-log日志
1,使用vim或其它编辑器 打开 mysql 配置文件

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

2,找到 以下两个配置项
    server-id               = 123456
    log_bin                 = /var/log/mysql/mysql-bin

3,把注释打开 改完后 保存推出


4,重启mysql
sudo service mysql restart


5,查看bin-log日志:
mysql>show binary logs;

6,ls 查看是否存在mysql日志文件
cd /var/lib/mysql



mysql>reset master;
7,清空所有的bin-log日志

8,-- 查看binlog日志文件
mysqlbinlog mysql-bin.000001


备份数据时 刷新日志:
mysqldump -uroot -pwei test -l -F '/tmp/test.sql'
其中：-F即flush logs，可以重新生成新的日志文件，当然包括log-bin日志

8. 数据库恢复

前提是开启了bin-log日志,并定期备份

思路:

1,通过定期备份的文件恢复大量数据

2,通过日志恢复备份后的数据操作

-- 刷新 日志
mysql> reset master;
Query OK, 0 rows affected (0.39 sec)

-- 查询最新日志
show binary logs;


-- 创建数据库
mysql> create database ops;
Query OK, 1 row affected (0.28 sec)

-- 选择并打开库
mysql> use ops;
Database changed

-- 创建表
create table user(
    id int not null auto_increment,
    name char(20) not null,
    age int not null,
    primary key(id)
)engine=InnoDB;


-- 添加数据
insert into user values(1,"wangbo","24"),(2,"guohui","22"),(3,"zhangheng","27");

-- 查询
mysql> select *  from user;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
|  1 | wangbo    |  24 |
|  2 | guohui    |  22 |
|  3 | zhangheng |  27 |
+----+-----------+-----+
3 rows in set (0.00 sec)

-- 现在进行数据备份
mysqldump -uroot -p -B -F -R -x --master-data=2 ops >/home/yc/py08/04-mysql/ops.sql

-----------------
参数说明：
-B：指定数据库
-F：刷新日志
-R：备份存储过程等
-x：锁表
--master-data：在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息



-- 再添加新的数据
insert into user values(4,"liupeng","21"),(5,"xiaoda","31"),(6,"fuaiai","26");

-- 查询数据
mysql> select * from user;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
|  1 | wangbo    |  24 |
|  2 | guohui    |  22 |
|  3 | zhangheng |  27 |
|  4 | liupeng   |  21 |
|  5 | xiaoda    |  31 |
|  6 | fuaiai    |  26 |
+----+-----------+-----+
6 rows in set (0.00 sec)



-- 此时误操作，删除了test数据库
drop database ops;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| python4            |
| sys                |
| wx                 |
+--------------------+
6 rows in set (0.00 sec)




1,先讲binlog文件导出
-- 将binlog文件导出sql文件，并vim编辑它删除其中的drop语句
-- [root@vm-002 backup]# mysqlbinlog -d ops /var/lib/mysql/mysql-bin.000002> /home/yc/002bin.sql

sudo mysqlbinlog -d ops /var/log/mysql/mysql-bin.000002> /home/yc/py08/04-mysql/002bin.sql


2,-- 删除里面的drop语句
vim 002bin.sql



3,-- 导入备份的数据文件,
mysql -uroot -p < /home/yc/py08/04-mysql/ops.sql 


4,-- 再导入删除 drop语句后的 binlog日志文件
mysql -uroot -p ops < /home/yc/py08/04-mysql/002bin.sql

六、数据的导入和导出

1. 数据导出

-- 导出整个数据库
mysqldump -u 用户名 -p 数据库名 >./导出的文件名.sql
Enter password:
-- 导出一个表
mysqldump -u 用户名 -p 数据库名 表名 >./导出的文件名.sql
Enter password:

2. 数据导入

-- 将数据库导入
-- 需在本地数据库中创建相对应同名的数据库
mysql -u 用户名 -p 数据库名<./文件名.sql
Enter password:

七、常用内置函数

1. 字符串处理函数

concat(s1,s2,…Sn) 连接s1,s2..Sn为一个字符串

length(str) 返回值为字符串str 的长度

2. 数值函数

abs(x) 返回x的绝对值

round(x,y) 返回参数x的四舍五入的有y位小数的值

3. 日期和时间函数

now() 返回当前日期和时间,

unix_timestamp(date) 返回date时间的unix时间戳

date_fomat(date,fmt) 返回按字符串fmt格式化日期date值

datediff(expr,expr2) 返回起始时间和结束时间的间隔天数

//统计时间戳647583423距离当前时间相差天数（生日天数（不考虑年份））
mysql> select datediff(date_format(from_unixtime(647583423),"2017-%m-%d %h:%i:%s"),now());

4. 其他常用函数

database() 返回当前数据库名

version() 返回当前服务器版本

user()    返回当前登陆用户名

inet_aton 返回当前IP地址的数字表示 inet_aton("192.168.80.250");

inet_ntoa(num) 返回当前数字表示的ip inet_ntoa(3232256250);

password(str) 返回当前str的加密版本

md5(str) 返回字符串str的md5值

七、授权、改密

1. 授权

格式：grant 允许操作 on 库名.表名 to 账号@来源 identified by ‘密码’;

实例：创建zhangsan账号，密码123，授权lamp61库下所有表的增/删/改/查数据,来源地不限
 mysql> grant select,insert,update,delete on lamp61.* to zhangsan@'%' identified   by '123';
 mysql> grant all on *.* to zhangsan@'%' identified by '123';
 Query OK, 0 rows affected (0.00 sec)

 添加用户, 并授予在lamp189的数据库中可以对user表进行 查询和添加数据操作
 GRANT select,insert ON lamp189.user TO 'xxoo'@'%' IDENTIFIED BY 'abcd'

 删除用户
 drop user 'xxoo'@'%'

2. 改密

1.找到mysql配置文件 C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
2.在[mysqld] 下面添加 skip-grant-tables 保存
3.重启mysql服务
4.打开命令行 mysql -uroot -p  不用输入密码
5.use mysql 切换到权限库
6.执行修改密码 Update user set authentication_string=password('1234567') where user='root';
7:执行刷新权限 flush privileges;
修改成功后：要把配置文件改回来 然后在重启服务
使用新密码链接数据库

八、Python 连接MySQL

Python3 MySQL 数据库连接

1. PyMySQL安装

pip install pymysql

2. 连接数据库

通过如下代码测试数据库连接

 import pymysql
 
 # 打开数据库连接
 db = pymysql.connect("localhost","root","123456","mydb" )

 # 使用 cursor() 方法创建一个游标对象 cursor
 cursor = db.cursor()

 # 使用 execute()  方法执行 SQL 查询 
 cursor.execute("SELECT VERSION()")

 # 使用 fetchone() 方法获取单条数据.
 data = cursor.fetchone()

 print ("Database version : %s " % data)

 # 关闭数据库连接
 db.close()

3. 执行数据添加

import pymysql

# 打开数据库连接
db = pymysql.connect("localhost","root","123456","mydemo" )

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# SQL 插入语句
sql = "INSERT INTO stu(name,sex,age,classid) values('%s','%c','%d','%s')" % ('uu142','m',22,'lamp180') 

try:
   # 执行sql语句
   cursor.execute(sql)
   # 执行sql语句
   db.commit()
   print("ok: %d " % (cursor.rowcount))
except:
   # 发生错误时回滚
   db.rollback()

# 关闭数据库连接
db.close()

4. 执行删除操作

 import pymysql

 # 打开数据库连接
 db = pymysql.connect("localhost","root","","mydemo" )

 # 使用 cursor() 方法创建一个游标对象 cursor
 cursor = db.cursor()

 # SQL 删除语句
 sql = "delete from stu where id = '%d'" % (13)
 try:
    # 执行SQL语句
    cursor.execute(sql)
    # 提交修改
    db.commit()
 except:
    # 发生错误时回滚
    db.rollback()

 # 关闭数据库连接
 db.close()

5. 执行数据修改/更新

import pymysql

# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# SQL 更新语句
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1
                          WHERE SEX = '%c'" % ('M')
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()

# 关闭数据库连接
db.close()

6. 执行数据查询

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

fetchone(): 该方法获取下一个查询结果集。结果集是一个对象

fetchall(): 接收全部的返回结果行.

rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数。

 import pymysql

 # 打开数据库连接
 db = pymysql.connect("localhost","root","","mydemo" )

 # 使用 cursor() 方法创建一个游标对象 cursor
 cursor = db.cursor()

 # SQL 查询语句
 sql = "select * from stu limit %d" % (3)
 #sql = "select * from stu"

 try:
    # 执行SQL语句
    cursor.execute(sql)
    # 获取所有记录列表
    results = cursor.fetchall()
    for row in results:
       id = row[0]
       name = row[1]
       sex = row[2]
       age = row[3]
       classid = row[4]
        # 打印结果
       print ("id=%d,name=%s,sex=%s,age=%d,classid=%s" % (id,name,sex,age,classid))
 except:
    print ("Error: unable to fetch data")

 # 关闭数据库连接
 db.close()

模板

# db.py 连接数据库、执行sql语句
import pymysql

class DB():
    def __init__(self,database = 'py05',user='root',password='123456',port = 3306,host = 'localhost'):
        self.db = pymysql.connect(host = host,port=port,user = user,database = database,password = password,charset ='utf8mb4')
        self.cursor = self.db.cursor()
    
    def update(self,sql, data):
        try:
            self.cursor.execute(sql, data)
            self.db.commit()
        except:
            print("操作失败，请检查sql语句")
    def query(self,sql):
        try:
            self.cursor.execute(sql)
            data = self.cursor.fetchall()
            return data
        except:
            print("查询失败，请检查sql语句")
    def __del__(self):
        self.cursor.close()
        self.db.close()