mySQL+Python学习笔记

野性酷女 2023-07-04 04:52 8阅读 0赞

### 文章目录 ###

*  一、知识链接
 *  \*\*优秀的读者会给优秀的文章进行点赞的\_..\_\*\*
 *  二、环境: win10 python3.6 mysql8.0.17
 *  三、问题与解决
 *   *  问题1: Mysql解压版安装mysqld --initialize --user=mysql--console初始化命令没反应
     *  问题2 字体问题
 *  四、安装mySql 8.0.17版本
 *   *  1 下载\[MYSql网站下载\](https://downloads.mysql.com/archives/community/)
     *  2 解压到路径
     *  3 根据路径配置系统环境变量
     *  4 在安装目录添加my.ini文件
     *  5 服务器初始化
     *  6 增加删除用户zhao,并修改权限,名称和密码
     *  7 创建删除数据库,给新用户授权数据库权限
 *  五、Python3.6连接mysql
 *   *  安装插件包
     *  1 例程
     *   *  问题1 'calling\_sha2\_password' is not supported.
     *  2 创建数据库
     *   *  mycursor.execute("SHOW DATABASES")
         *  mycursor.execute("CREATE DATABASE runoob\_db")
         *  mycursor.execute("drop DATABASE runoob\_db")
     *  3 创建表和删除表
     *   *  mycursor.execute("SHOW TABLES")
         *  mycursor.execute("CREATE TABLE sites (name VARCHAR(255), url VARCHAR(255))")
         *  mycursor.execute("ALTER TABLE sites ADD COLUMN id INT AUTO\_INCREMENT PRIMARY KEY")
         *  mycursor.execute("CREATE TABLE sites2 (id INT AUTO\_INCREMENT PRIMARY KEY, name VARCHAR(255), url VARCHAR(255))")
         *  mycursor.execute("DROP TABLE IF EXISTS sites")
     *  4 向表中增加数据
     *   *  mycursor.execute(sql, val)
         *  mycursor.executemany(sql, val)
         *  mydb.commit()
     *  5 查询数据
     *   *  mycursor.execute("SELECT \* FROM sites")
         *  mycursor.execute("SELECT \* FROM sites where id like 2")
         *  mycursor.fetchall()
         *  mycursor.execute("SELECT \* FROM sites where url like '%oo%'")
     *  6 排序查询
     *   *  mycursor.execute("SELECT \* FROM sites LIMIT 3 OFFSET 1") 第1往后三个
         *  mycursor.execute("SELECT \* FROM sites ORDER BY url desc") url排序输出降序
     *  7 删除数据
     *   *  mycursor.execute("DELETE from sites where name = 'Google'")
     *  8 更新表数据
     *   *  mycursor.execute("UPDATE sites SET name = 'guge' WHERE name = 'Google'")
 *  六、笔记二 Python3.6+8.0.17+mysql.connector
 *   *   *  建立连接
         *  新建/删除表
         *  插入数据
         *  使用分组
         *  正则表达式

# 一、知识链接 #

[mysql基础语句][mysql]  
[一千行mysql学习笔记][mysql 1]

# **优秀的读者会给优秀的文章进行点赞的\_…\_** #

# 二、环境: win10 python3.6 mysql8.0.17 #

# 三、问题与解决 #

安装MySQL直接在官网下载8.0.19最新版 zip压缩包,下载解压到安装目录,按照网上教程在解压目录添加my.ini配置文件,配置好环境变量后,出现了很多问题.

## 问题1: Mysql解压版安装mysqld --initialize --user=mysql–console初始化命令没反应 ##

*  问题解决: 查看安装路径下的文件,发现跟网上教程的图片对比少了很多文件,下图是19版本
 *  ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDYwODE5_size_16_color_FFFFFF_t_70]
 *  网络教程版本样子
 *  ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDYwODE5_size_16_color_FFFFFF_t_70 1]  
    为此猜想可能是因为新版本问题,于是重新下载17版本的安装配置

## 问题2 字体问题 ##

安装完17版本后,运行出错  
`mysqld --initialize --console`  
2020-02-08T03:52:20.608381Z 0 \[Warning\] \[MY-013242\] \[Server\] --character-set-server: ‘utf8’ is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a  
解决方案将my.ini 修改为utf8mb4:,下文有完整版

部分
    [client]
    default-character-set=utf8mb4
    
    [mysqld]
    port = 3306
    character-set-server=utf8mb4
    default-storage-engine=INNODB

# 四、安装mySql 8.0.17版本 #

## 1 下载[MYSql网站下载][MYSql] ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDYwODE5_size_16_color_FFFFFF_t_70 2]

## 2 解压到路径 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDYwODE5_size_16_color_FFFFFF_t_70 3]

## 3 根据路径配置系统环境变量 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDYwODE5_size_16_color_FFFFFF_t_70 4]

## 4 在安装目录添加my.ini文件 ##

[client]
    # 设置mysql客户端默认字符集
    # 修改为utf8mb4
    # default-character-set=utf8
    default-character-set=utf8mb4
    
    [mysqld]
    # 设置3306端口
    port = 3306
    
    # 设置mysql的安装目录 D:\\ProgramFiles
    basedir=D:\\ProgramFiles\\mysql-8.0.17-winx64
    
    # 设置 mysql数据库的数据的存放目录，MySQL 8+ 不需要以下配置，系统自己生成即可，否则有可能报错
    # datadir=D:\\ProgramFiles\\mysql-8.0.17-winx64
    
    # 允许最大连接数
    max_connections=20
    
    # 服务端使用的字符集默认为8比特编码的latin1字符集
    # character-set-server=utf8 
    character-set-server=utf8mb4
    
    # 创建新表时将使用的默认存储引擎
    default-storage-engine=INNODB

## 5 服务器初始化 ##

`mysqld --initialize --console`  
注意要使用管理员方式打开cmd

2020-02-08T04:00:56.455643Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: Xfih_ue*X1jF

A temporary password is generated for root@localhost: Xfih\_ue\*X1jF

**表示 用户root密码Xfih\_ue\*X1jF**

PS C:\Windows\system32> mysqld install  # 安装MySQL
    Service successfully installed.
    PS C:\Windows\system32> net start mysql # 启动服务
    MySQL 服务正在启动 .
    MySQL 服务已经启动成功。
    PS C:\Windows\system32> mysql -u root -p  # 登陆root用户
    Enter password: ************
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 9
    Server version: 8.0.17
    
    Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '我的新密码'; # 并修改密码
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> exit; # 退出数据库
    Bye
    PS C:\Windows\system32>

上面命令就完成了初始登陆并修改密码退出等操作  
下面总结命令, 便于下次使用

mysqld install  # 1 安装MySQL
    net start mysql # 2 启动服务
    mysql -u root -p  # 3 登陆root用户
    # 4 修改密码格式有下面三种
    ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
    ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'zjq';
    create user 'root'@'localhost' identified with 加密方式(mysql_native_password) by '123456';
    mysql> exit  # 5 退出当前用户
    mysql -u root -p  # 6 重新登陆登陆root用户, 使用新密码
    输入刚刚修改的密码
    exit;  # 7 退出当前用户
    net stop mysql # 8关闭mysql服务器

## 6 增加删除用户zhao,并修改权限,名称和密码 ##

mysql> select user,host from mysql.user# 显示用户
    
    mysql -u root -p # 登陆root用户
    
    #开始创建用户zhao.by后面是密码 返回Query OK
    mysql> create user zhao@localhost identified by 'zhao';
    
    mysql> flush privileges;# 刷新
    mysql> quit #退出
    mysql -u zhao -p # 登陆新建用户
    
    # 删除用户,在root用户下
    mysql> drop user "gerrit"@"localhost" #删除用户gerrit
    
    # 用户权限修改
    root用户权限最高,拥有所有权限,首先在root用户下创建zhao,在修改zhao的权限
    
    # 创建用户zhao,但是此时zhao权限为最低
    mysql> create user zhao@localhost identified by 'zhao'; 
    
    授权格式：grant privileges on databasename.tablename to 'username'@'host' IDENTIFIED BY 'PASSWORD';
    # all privileges是所有权限
    mysql> grant all privileges on `zhao`.* to 'zhao'@'localhost'; 
    mysql> grant select on zhao.* to 'zhao'@'localhost';  /*给予查询权限*/
    mysql> grant insert on zhao.* to 'zhao'@'localhost'; /*添加插入权限*/
    mysql> grant delete on zhao.* to 'zhao'@'localhost'; /*添加删除权限*/
    mysql> grant update on zhao.* to 'zhao'@'localhost'; /*添加权限*/
    mysql> flush privileges; /*刷新权限*/
    mysql> show grants; #查看当前用户权限
    mysql> show grants for 'zhao'@'localhost'; # 查看zhao的权限
    mysql> revoke delete on zhao.* from 'zhao'@'localhost'; # 删除权限
    
    #更改用户名字
    mysql> rename user 'zhao'@'localhost' to 'zjq'@'localhost'; 
    
    # 修改用户密码
    mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');
    mysqladmin -uroot -p123456 password 1234abcd
    格式：mysqladmin -u用户名 -p旧密码 password 新密码

## 7 创建删除数据库,给新用户授权数据库权限 ##

转自[博客][Link 1]

mysql -u root -p
    # 创建数据库testDB
    mysql> create database testDB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
    
    # 显示数据库
    mysql> show databases
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | runoob             |
    | runoob_db          |
    | sys                |
    | testdb             |
    +--------------------+
    
    # 授权用户zhao对testDB数据库的所有管理权限
    mysql> grant all privileges on testDB.* to 'zhao'@'localhost';
    
    
    # 数据库创建和删除
    mysqladmin -u root -p create RUNOOB# 使用root用户创建RUNOOB数据库
    mysql> create DATABASE RUNOOB;
    mysqladmin -u root -p drop RUNOOB
    mysql> drop database RUNOOB;
    
    # 选择数据库
    use mysql

# 五、Python3.6连接mysql #

## 安装插件包 ##

`python -m pip install mysql-connector -i https://pypi.mirrors.ustc.edu.cn/simple/`

## 1 例程 ##

import mysql.connector
    mydb = mysql.connector.connect(
      host="localhost",       # 数据库主机地址
      user="root",    # 数据库用户名
      passwd="yourpassword"   # 数据库密码
    )
    print(mydb)

### 问题1 ‘calling\_sha2\_password’ is not supported. ###

解决方式 摘自[菜鸟教程][Link 2]

*  修改myini

[client]
    # 设置mysql客户端默认字符集
    # 修改为utf8mb4
    # default-character-set=utf8
    default-character-set=utf8mb4
    # 设置mysql客户端连接服务端时默认使用的端口
    port=3306
    
    [mysqld]
    # 设置3306端口
    port = 3306
    
    # 设置mysql的安装目录 D:\\ProgramFiles
    basedir=D:\\ProgramFiles\\mysql-8.0.17-winx64
    
    # 设置 mysql数据库的数据的存放目录，MySQL 8+ 不需要以下配置，系统自己生成即可，否则有可能报错
    # datadir=D:\\ProgramFiles\\mysql-8.0.17-winx64
    
    # 允许最大连接数
    max_connections=20
    
    # 服务端使用的字符集默认为8比特编码的latin1字符集
    # character-set-server=utf8
    character-set-server=utf8mb4
    
    # 创建新表时将使用的默认存储引擎
    default-storage-engine=INNODB
    
    # 默认使用“mysql_native_password”插件认证
    default_authentication_plugin=mysql_native_password
    
    [mysql]
    # 设置mysql客户端默认字符集
    default-character-set=utf8

*  修改密码格式

# 修改加密规则为永不过期
    #注意：password 改为你的密码，别老老实实照抄，之前本人便以为 password 指代密码不用改变，结果一通指令下去，mysql 登不上了，只好重新初始化数据库
    mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
    
    #更新一下用户的密码加密方式为之前版本的方式
    mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';　　　　
    
    #刷新权限
    mysql> FLUSH PRIVILEGES;

*  重启，这是再看，已经修改了身份验证插件。

import mysql.connector
    
    mydb=mysql.connector.connect(
        host="localhost",
        user="root",
        passwd="123456",
        # auth_plugin='mysql_native_password'
    )
    
    print(mydb)

输出: <mysql.connector.connection.MySQLConnection object at 0x00000211F73DB9E8>

## 2 创建数据库 ##

### mycursor.execute(“SHOW DATABASES”) ###

### mycursor.execute(“CREATE DATABASE runoob\_db”) ###

### mycursor.execute(“drop DATABASE runoob\_db”) ###

# 创建数据库
    import mysql.connector
    mydb = mysql.connector.connect(
        host="localhost",
        user="root",
        passwd=password_my,
        # database="runoob_db" # 也可以直接打开用户下数据库
    )
    mycursor = mydb.cursor() # 创建游标
    
    print("当前用户下的数据库包含如下:")
    mycursor.execute("SHOW DATABASES")
    for x in mycursor:print(x) 
    
    try:
        mycursor.execute("CREATE DATABASE runoob_db") # 创建名为runoob_db的数据库
        print("创建数据库runoob_db") 
        mycursor.execute("SHOW DATABASES") 
        for x in mycursor:print(x) 
    except Exception as e:
        print("数据库已经存在runoob_db")
        print(e)
        
    # 删除数据库
    # mycursor.execute("drop DATABASE runoob_db")

当前用户下的数据库包含如下:
    ('information_schema',)
    ('mysql',)
    ('performance_schema',)
    ('sys',)
    创建数据库runoob_db
    ('information_schema',)
    ('mysql',)
    ('performance_schema',)
    ('runoob_db',)
    ('sys',)

## 3 创建表和删除表 ##

### mycursor.execute(“SHOW TABLES”) ###

### mycursor.execute(“CREATE TABLE sites (name VARCHAR(255), url VARCHAR(255))”) ###

### mycursor.execute(“ALTER TABLE sites ADD COLUMN id INT AUTO\_INCREMENT PRIMARY KEY”) ###

### mycursor.execute(“CREATE TABLE sites2 (id INT AUTO\_INCREMENT PRIMARY KEY, name VARCHAR(255), url VARCHAR(255))”) ###

### mycursor.execute(“DROP TABLE IF EXISTS sites”) ###

# 创建数据表
    import mysql.connector
    mydb = mysql.connector.connect(
      host="localhost",
      user="root",
      passwd=password_my,
      database="runoob_db"
    )
    mycursor = mydb.cursor()
    
    # 第一种方式,先建表,在添加主键值
    try:    
        # 创建表 sites，字段为 name 和 url 
        mycursor.execute("CREATE TABLE sites (name VARCHAR(255), url VARCHAR(255))") 
        print("创建表,并增加主键 id")
        mycursor.execute("ALTER TABLE sites ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")
        mycursor.execute("SHOW TABLES")
        for x in mycursor:print(x)
    except Exception as e:
        print(e) 
        # 给sites添加主键
        mycursor.execute("ALTER TABLE sites ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")
    mycursor.execute("SHOW TABLES")
    for x in mycursor:print(x)
    
    # 第二种方式,直接建表,包括主键值和两个参数
    # mycursor.execute("CREATE TABLE sites2 (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), url VARCHAR(255))")
    
    # 删除数据表 sites)
    mycursor.execute("DROP TABLE IF EXISTS sites")

## 4 向表中增加数据 ##

### mycursor.execute(sql, val) ###

### mycursor.executemany(sql, val) ###

### mydb.commit() ###

import mysql.connector
    mydb = mysql.connector.connect(
      host="localhost",
      user="root",
      passwd=password_my,
      database="runoob_db"
    )
    mycursor = mydb.cursor()
    
    # 第一种方法 插入一条数据
    sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
    val = ("RUNOOB", "https://www.runoob.com")
    mycursor.execute(sql, val)
    mydb.commit()    # 数据表内容有更新，必须使用到该语句
    print(mycursor.rowcount == 1, "记录插入成功。")
    print("1 条记录已插入, ID:", mycursor.lastrowid)
    
    # 第二种方法 插入批量数据
    sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
    val = [
      ('Google', 'https://www.google.com'),
      ('Github', 'https://www.github.com'),
      ('Taobao', 'https://www.taobao.com'),
      ('stackoverflow', 'https://www.stackoverflow.com/')
    ]
    mycursor.executemany(sql, val)
    mydb.commit()    # 数据表内容有更新，必须使用到该语句
    print(mycursor.rowcount, "记录插入成功。")

## 5 查询数据 ##

### mycursor.execute(“SELECT \* FROM sites”) ###

### mycursor.execute(“SELECT \* FROM sites where id like 2”) ###

### mycursor.fetchall() ###

### mycursor.execute(“SELECT \* FROM sites where url like ‘%oo%’”) ###

import mysql.connector
    mydb = mysql.connector.connect(
      host="localhost",
      user="root",
      passwd=password_my,
      database="runoob_db"
    )
    mycursor = mydb.cursor()
    
    # 新建table删除table
    # mycursor.execute("DROP TABLE IF EXISTS sites2") 
    # mycursor.execute("CREATE TABLE sites (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), url VARCHAR(255))")
    
    ''' # 插入部分 sql = "INSERT INTO sites (name, url) VALUES (%s, %s)" val = ("RUNOOB", "https://www.runoob.com") mycursor.execute(sql, val) mydb.commit() # 数据表内容有更新，必须使用到该语句 print(mycursor.rowcount == 1, "记录插入成功。") print("1 条记录已插入, ID:", mycursor.lastrowid) # 第二种方法 插入批量数据 sql = "INSERT INTO sites (name, url) VALUES (%s, %s)" val = [ ('Google', 'https://www.google.com'), ('Github', 'https://www.github.com'), ('Taobao', 'https://www.taobao.com'), ('stackoverflow', 'https://www.stackoverflow.com/') ] mycursor.executemany(sql, val) mydb.commit() # 数据表内容有更新，必须使用到该语句 print(mycursor.rowcount, "记录插入成功。") '''
    
    # 第一种查询方法
    mycursor.execute("SELECT * FROM sites")
    myresult = mycursor.fetchall()     # fetchall() 获取所有记录
    for x in myresult:print(x)
    
    # 第二种 读取特定字段
    print("读取特定字段")
    mycursor.execute("SELECT name, url FROM sites")
    myresult = mycursor.fetchall()
    for x in myresult:print(x)
    
    # 第三种 如果我们要读取指定条件的数据，可以使用 where 语句：
    print("name = RUNOOB关键字的")
    mycursor.execute("SELECT * FROM sites WHERE name ='RUNOOB'")
    myresult = mycursor.fetchall()
    for x in myresult:print(x)
    
    # 第四种 指定函数 不能放到查询的前面,不知道为啥 
    # print("读取特定字段 第一个")
    # mycursor.execute("SELECT * FROM sites")
    # myresult = mycursor.fetchone()
    # print(myresult)
    
    # 第五种 通配符%xx%
    print("读取特定字段 url中包含oo的")
    mycursor.execute("SELECT * FROM sites where url like '%oo%'")
    myresult = mycursor.fetchall()
    for x in myresult:print(x)
      
    # 第五种加一 为了防止数据库查询发生 SQL 注入的攻击，我们可以使用 %s 占位符来转义查询的条件：
    print("使用%s占位")
    sql = "SELECT * FROM sites WHERE name = %s"
    na = ("RUNOOB", )
    mycursor.execute(sql, na)
    myresult = mycursor.fetchall()
    for x in myresult:print(x)
    
    print("id = 2")
    mycursor.execute("SELECT * FROM sites where id like 2")
    myresult = mycursor.fetchall()
    for x in myresult:print(x)

(1, 'RUNOOB', 'https://www.runoob.com')
    (2, 'Google', 'https://www.google.com')
    (3, 'Github', 'https://www.github.com')
    (4, 'Taobao', 'https://www.taobao.com')
    (5, 'stackoverflow', 'https://www.stackoverflow.com/')
    读取特定字段
    ('RUNOOB', 'https://www.runoob.com')
    ('Google', 'https://www.google.com')
    ('Github', 'https://www.github.com')
    ('Taobao', 'https://www.taobao.com')
    ('stackoverflow', 'https://www.stackoverflow.com/')
    name = RUNOOB关键字的
    (1, 'RUNOOB', 'https://www.runoob.com')
    读取特定字段 url中包含oo的
    (1, 'RUNOOB', 'https://www.runoob.com')
    (2, 'Google', 'https://www.google.com')
    使用%s占位
    (1, 'RUNOOB', 'https://www.runoob.com')
    id = 2
    (2, 'Google', 'https://www.google.com')

## 6 排序查询 ##

### mycursor.execute(“SELECT \* FROM sites LIMIT 3 OFFSET 1”) 第1往后三个 ###

### mycursor.execute(“SELECT \* FROM sites ORDER BY url desc”) url排序输出降序 ###

import mysql.connector
    mydb = mysql.connector.connect(
      host="localhost",
      user="root",
      passwd=password_my,
      database="runoob_db"
    )
    mycursor = mydb.cursor()
    
    # 新建table删除table
    # mycursor.execute("DROP TABLE IF EXISTS sites2") 
    # mycursor.execute("CREATE TABLE sites (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), url VARCHAR(255))")
    
    ''' # 插入部分 sql = "INSERT INTO sites (name, url) VALUES (%s, %s)" val = ("RUNOOB", "https://www.runoob.com") mycursor.execute(sql, val) mydb.commit() # 数据表内容有更新，必须使用到该语句 print(mycursor.rowcount == 1, "记录插入成功。") print("1 条记录已插入, ID:", mycursor.lastrowid) # 第二种方法 插入批量数据 sql = "INSERT INTO sites (name, url) VALUES (%s, %s)" val = [ ('Google', 'https://www.google.com'), ('Github', 'https://www.github.com'), ('Taobao', 'https://www.taobao.com'), ('stackoverflow', 'https://www.stackoverflow.com/') ] mycursor.executemany(sql, val) mydb.commit() # 数据表内容有更新，必须使用到该语句 print(mycursor.rowcount, "记录插入成功。") '''
    
    
    # 查询结构排序,仅仅是输出的时候进行了排序,不改变源表
    # 使用 ORDER BY 语句，默认的排序方式为升序，关键字为 ASC，如果要设置降序排序，可以设置关键字 DESC
    mycursor.execute("SELECT * from sites")
    for i in mycursor.fetchall():print(i)
    
    # # 对name排序
    print("对name升序排序")
    mycursor.execute("SELECT * FROM sites ORDER BY name")
    myresult = mycursor.fetchall()
    for x in myresult:print(x)
    
    # # 对url排序 降序
    print("对url排序 降序")
    mycursor.execute("SELECT * FROM sites ORDER BY url desc")
    myresult = mycursor.fetchall()
    for x in myresult:print(x)
    
    # 设置查询数量 "LIMIT" 语句来指定
    print("LIMIT 设置查询数量")
    mycursor.execute("SELECT * FROM sites LIMIT 3")
    myresult = mycursor.fetchall()
    for x in myresult:print(x)
      
    # 也可以指定起始位置，使用的关键字是 OFFSET：
    print("LIMIT 设置查询数量,指定起始位置")
    mycursor.execute("SELECT * FROM sites LIMIT 3 OFFSET 1")
    myresult = mycursor.fetchall()
    for x in myresult:print(x)

(1, 'RUNOOB', 'https://www.runoob.com')
    (2, 'Google', 'https://www.google.com')
    (3, 'Github', 'https://www.github.com')
    (4, 'Taobao', 'https://www.taobao.com')
    (5, 'stackoverflow', 'https://www.stackoverflow.com/')
    对name升序排序
    (3, 'Github', 'https://www.github.com')
    (2, 'Google', 'https://www.google.com')
    (1, 'RUNOOB', 'https://www.runoob.com')
    (5, 'stackoverflow', 'https://www.stackoverflow.com/')
    (4, 'Taobao', 'https://www.taobao.com')
    对url排序 降序
    (4, 'Taobao', 'https://www.taobao.com')
    (5, 'stackoverflow', 'https://www.stackoverflow.com/')
    (1, 'RUNOOB', 'https://www.runoob.com')
    (2, 'Google', 'https://www.google.com')
    (3, 'Github', 'https://www.github.com')
    LIMIT 设置查询数量
    (1, 'RUNOOB', 'https://www.runoob.com')
    (2, 'Google', 'https://www.google.com')
    (3, 'Github', 'https://www.github.com')
    LIMIT 设置查询数量,指定起始位置
    (2, 'Google', 'https://www.google.com')
    (3, 'Github', 'https://www.github.com')
    (4, 'Taobao', 'https://www.taobao.com')

## 7 删除数据 ##

### mycursor.execute(“DELETE from sites where name = ‘Google’”) ###

(1, 'RUNOOB', 'https://www.runoob.com')
    (3, 'Github', 'https://www.github.com')
    (4, 'Taobao', 'https://www.taobao.com')
    (5, 'stackoverflow', 'https://www.stackoverflow.com/')

## 8 更新表数据 ##

### mycursor.execute(“UPDATE sites SET name = ‘guge’ WHERE name = ‘Google’”) ###

import mysql.connector
    mydb = mysql.connector.connect(
      host="localhost",
      user="root",
      passwd=password_my,
      database="runoob_db"
    )
    mycursor = mydb.cursor()
    
    # 注意一定要确定where条件语句,不然整个表格都会改变
    print("当前表中数据")
    mycursor.execute("SELECT * from sites")
    for i in mycursor.fetchall():print(i)
    
    mycursor.execute("UPDATE sites SET name = 'guge' WHERE name = 'Google'")
    mydb.commit() # 只要是修改表,这条命令就要用到
    print(mycursor.rowcount, " 条记录被修改")
    
    print("修改后的,将Google修改为guge表中数据")
    mycursor.execute("SELECT * from sites")
    for i in mycursor.fetchall():print(i)
    
    mycursor.execute("UPDATE sites SET name = 'Google' WHERE name = 'guge'")
    mydb.commit() # 只要是修改表,这条命令就要用到
    print(mycursor.rowcount, " 条记录被修改")
    
    print("修改回来的")
    mycursor.execute("SELECT * from sites")
    for i in mycursor.fetchall():print(i)

当前表中数据
    (1, 'RUNOOB', 'https://www.runoob.com')
    (3, 'Github', 'https://www.github.com')
    (4, 'Taobao', 'https://www.taobao.com')
    (5, 'stackoverflow', 'https://www.stackoverflow.com/')
    (6, 'Google', 'https://www.google.com')
    1  条记录被修改
    修改后的,将Google修改为guge表中数据
    (1, 'RUNOOB', 'https://www.runoob.com')
    (3, 'Github', 'https://www.github.com')
    (4, 'Taobao', 'https://www.taobao.com')
    (5, 'stackoverflow', 'https://www.stackoverflow.com/')
    (6, 'guge', 'https://www.google.com')
    1  条记录被修改
    修改回来的
    (1, 'RUNOOB', 'https://www.runoob.com')
    (3, 'Github', 'https://www.github.com')
    (4, 'Taobao', 'https://www.taobao.com')
    (5, 'stackoverflow', 'https://www.stackoverflow.com/')
    (6, 'Google', 'https://www.google.com')

# 六、笔记二 Python3.6+8.0.17+mysql.connector #

### 建立连接 ###

import mysql.connector
    mydb = mysql.connector.connect(
      host="localhost",
      user="root",
      passwd=password_my,
      database="runoob_db"
    )
    mycursor = mydb.cursor()

### 新建/删除表 ###

# 新建表 employee_tbl
    # AUTO_INCREMENT自动增量default
    sql_create_table = "CREATE TABLE `employee_tbl` \
    (`id` int(10) NOT NULL AUTO_INCREMENT,\
    `name` varchar(10) default NULL,\
    `date` datetime NOT NULL,\
    `singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',\
    PRIMARY KEY (`id`)\
    ) ENGINE=MyISAM default CHARSET=utf8"
    
    try:
      mycursor.execute(sql_create_table)
    except mysql.connector.Error as e:
      print(f'create table orange fails!{ e}')
    
    # 删除表
    mycursor.execute("drop table if exists `employee_tbl`")

### 插入数据 ###

# 插入数据
    sql = "INSERT INTO `employee_tbl` VALUES ('1', '小明', '2016-04-22 15:25:33', '1'), ('2', '小王', '2016-04-20 15:25:47', '3'), ('3', '小丽', '2016-04-19 15:26:02', '2'), ('4', '小王', '2016-04-07 15:26:14', '4'), ('5', '小明', '2016-04-11 15:26:40', '4'), ('6', '小明', '2016-04-04 15:26:54', '2')"
    mycursor.execute(sql)
    mydb.commit()    # 数据表内容有更新，必须使用到该语句
    print(mycursor.rowcount, "记录插入成功。")
    
    mycursor.execute("SELECT * FROM `employee_tbl`")
    for res in mycursor.fetchall(): print(res)

### 使用分组 ###

# 使用GROUP BY分组
    # 接下来我们使用 GROUP BY 语句 将数据表按名字进行分组，并统计每个人有多少条记录：
    sql = "SELECT name, COUNT(*) FROM `employee_tbl` GROUP BY name;"
    mycursor.execute(sql)
    for res in mycursor.fetchall(): print(res)
    
    # WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。
    # 将以上的数据表按名字进行分组，再统计每个人登录的次数：
    # 其中记录 NULL 表示所有人的登录次数。
    sql = "SELECT name, SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP"
    mycursor.execute(sql)
    for res in mycursor.fetchall(): print(res)
    
    print("名字为空我们用总数代替")
    sql = "SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP"
    mycursor.execute(sql)
    for res in mycursor.fetchall(): print(res)

### 正则表达式 ###

mycursor.execute("select name from employee_tbl where name regexp '^小'") # 所有字段中以小开头的
    for res in mycursor.fetchall(): print(res)
    
    mycursor.execute("select * from employee_tbl where name regexp '明$'") # 所有字段中以明为结尾的
    for res in mycursor.fetchall(): print(res)
    
    mycursor.execute("select * from employee_tbl where name regexp '丽'") # 所有字段中含有丽的
    for res in mycursor.fetchall(): print(res)
    
    mycursor.execute("select * from employee_tbl where name regexp '^[aeiou]|ok$'") # 所有字段中以aeiou开头的，或者以ok结尾的
    for res in mycursor.fetchall(): print(res)

[mysql]: https://blog.csdn.net/SIMBA1949/article/details/79057749#%E5%88%9B%E5%BB%BA%E8%A7%86%E5%9B%BE
[mysql 1]: https://mp.weixin.qq.com/s/QGlax67rIB6Opd6W51hhMw
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDYwODE5_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200208121310334.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDYwODE5,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDYwODE5_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200208121322921.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDYwODE5,size_16,color_FFFFFF,t_70
[MYSql]: https://downloads.mysql.com/archives/community/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDYwODE5_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200208120544478.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDYwODE5,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDYwODE5_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/202002081219428.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDYwODE5,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDYwODE5_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200208122025933.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDYwODE5,size_16,color_FFFFFF,t_70
[Link 1]: https://www.cnblogs.com/wuxunyan/p/9095016.html
[Link 2]: https://www.runoob.com/note/45833