mysql全解 Ubuntu/win10

叁歲伎倆 2022-11-22 12:52 215阅读 0赞

### 文章目录 ###

*  一. 知识链接
 *  \*\*优秀的读者会给优秀的文章进行点赞的\_..\_\*\*
 *  二. 数据库的核心命令
 *   *  2.1 安装数据库
     *   *  a.docker安装
         *  b. win10安装
         *  c. Ubuntu安装
     *  2.2 用户 增删改查
     *  2.3 数据库 增删改查
     *  2.4 表 增查改删
     *  2.5 导出导入数据库和数据表
 *  三、Python-mysql
 *   *  3.1 安装插件包
     *  3.2 python与mysql建立连接
     *  3.3 python操作数据库
     *  3.4 python操作数据表
     *  3.5 正则表达式
 *  四. MySQL具体应用
 *   *  4.1 设置每天自动备份mysql数据库
     *  4.2 修改服务器上面MySQL整个列的数据

# 一. 知识链接 #

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

[全部知识点跳转][Link 1]

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

# 二. 数据库的核心命令 #

## 2.1 安装数据库 ##

### a.docker安装 ###

[docker安装和docker win10下mysql安装教程][docker_docker win10_mysql]

在win10上面安装完毕docker后  
`docker pull mysql:5.7` \# 得到mysql镜像  
加载为容器, 这里相当于创建了一个mysql服务器  
然后在Ubuntu子系统上面安装mysql客户端  
`sudo apt install mysql-client`  
此时可以通过远程访问mysql进行访问本地docker里面的mysql  
`mysql -h 主机IP -u root -p 密码`

### b. win10安装 ###

[点击进入win10安装教程][win10]

### c. Ubuntu安装 ###

[点击进入Ubuntu安装教程][Ubuntu]

## 2.2 用户 增删改查 ##

**增加删除用户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 新密码

## 2.3 数据库 增删改查 ##

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

转自[博客][Link 2]

mysql -u root -p # 登录用户
    
    mysql> create database testDB DEFAULT CHARSET utf8 COLLATE utf8_general_ci; # 创建数据库testDB
    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
    mysql> show variables like 'character_set_database'; # 显示当前数据库中的字符集
    mysql> alter database submission character set 'utf8'; # 修改数据库的字符集为utf8

## 2.4 表 增查改删 ##

create table t1(age int, name varchar(10)) default charset=utf8; # 创建表
    insert into t1(age,name)  values(10,"丁丁"),(11,"泥泞"); # 插入数据
    alter table t1 add column addr varchar(50); # 给上表增加列 
    drop table t1; # 删除表
    select * from t1; # 显示数据表的全部内容
    select age,name from t1; # 显示数据表的全部年龄和姓名
    desc t1; # 显示数据表的表头和数据类型
    update t1 set age=12 where name="丁丁";  # 修改name为丁丁的年龄
    delete from t1 where name="丁丁"; # 删除name为丁丁的行
    # Cannot delete or update a parent row: a foreign key constraint fails
    SET foreign_key_checks = 0;  // 先设置外键约束检查关闭
    delete from t1 where name="丁丁";  // 删除数据，表或者视图
    SET foreign_key_checks = 1; // 开启外键约束检查，以保持表结构完整性
    
    alter table t1 convert to character set utf8; # 设置News_news这张表为utf-8的格式
    FLUSH PRIVILEGES;
    # 创建两张表
    create table boy(name varchar(10), age int, addr varchar(50));
    create table girl(name varchar(10), age int, addr varchar(50));
    # 插入并显示数据
    insert into boy(name, age, addr) 
    values
    ("张飞",32,"北京"),
    ("谢霆锋",30,"南京"),
    ("李四",25,"天井"),
    ("甲流",33,"酒精"),
    ("鲁北",24,"南海");
    select * from boy;
    
    insert into girl(name, age, addr) 
    values
    ("泻立停",32,"北京"),
    ("佳美",30,"府东"),
    ("盼盼",23,"背黑锅"),
    ("欣欣",34,"安防"),
    ("玲玲",24,"安慰人");
    select * from boy;
    select * from girl;
    
    
    select distinct age from boy; # 去重查询
    
    # 查询age在25-30之间的
    insert into boy values("彪子",28,"韩国");
    insert into boy values("鬼小四",26,"几盆");
    select * from boy where age between 25 and 30; 
    
    # 查询age 25和30的数据 固定范围
    select * from boy where age in (25,30);
    
    # 查询name中, 有四的列出来, %通配符,
    select * from boy where name like "%四%";
    select * from boy where name like "%四";
    select * from boy where name like "彪%";
    
    # 排序输出
    select * from boy order by age;
    # 降序
    select * from boy order by age desc;
    
    
    # 显示前两个
    select * from boy limit 2;
    
    # 查看表中字段
    desc t1;
    
    # 统计age数量, age,count(1)是表头
    insert into boy values("王八那",26,"as");
    insert into boy values("阿斯钢",26, "阿三哥");
    select 
    age,count(1) 
    from boy 
    group by age;
    
    
    # 多表查询 
    
    # 内连接 as后面跟的是查询出来的表头, on 后面是查询的条件
    # 两张表的交集
    select
    a.`name` as "男名字", b.`name`as "女名字"
    from
    d1.boy a 
    inner join d1.girl b on a.age=b.age;
    
    # 左连接, 以左边的a.age查询为主,
    # 这里就不得不说下我们玩的DNF了, 创建一个账号,但是里面有很多角色,
    # 左表为基础, 右表有就好了
    insert into girl values("阿美美",32, "阿哥");
    insert into girl values("慧海",24, "笔记");
    select
    a.`name` as "男名字",a.age as "男年龄", b.`name`as "女名字", b.age as "女年龄"
    from
    d1.boy a 
    left join d1.girl b on a.age=b.age;
    
    # 右连接
    insert into girl values("阿美美",32, "阿哥");
    insert into girl values("慧海",24, "笔记");
    select
    a.`name` as "男名字",a.age as "男年龄", b.`name`as "女名字", b.age as "女年龄"
    from
    d1.boy a 
    right join d1.girl b on a.age=b.age;
    
    
    # 系统函数
    # 聚合函数
    # 求表的sum, max min avg
    select 
    sum(age), max(age),min(age),avg(age)
    from boy;
    
    # 字符串汇合
    select 
    name, CONCAT("角色名",name,age,addr) as concat
    from boy;
     
    -- 将name字段的四换成一
    select 
    name, replace(name,'四', '一')
    from boy;
    
    # 日期函数
    select
    now();
    
    # 加两天
    select
    DATE_ADD("2019-1-26",interval 2 DAY);
    
    select
    DATE_SUB("2019-1-26",interval 2 DAY);

## 2.5 导出导入数据库和数据表 ##

# 数据库导出
    # mysqldump -u用户名 -p密码 数据库名 > 数据库名.sql
    mysqldump -u root -p mydatabase > save.sql
    
    # 导出表
    #mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名.sql
    mysqldump -u root -p mydatabase t1> mydatabase_t1.sql
    
    # 导入数据库
    # mysql -u用户名 -p密码 数据库名 < 数据库名.sql
    # 第二种方法, 先常见数据库,然后在导入 source 数据库名.sql
    
    # 导入表
    # mysql -u用户名 -p密码 -d 数据库名 表名 < 导出的文件名.sql

转自[博客][Link 2]

# 三、Python-mysql #

## 3.1 安装插件包 ##

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

## 3.2 python与mysql建立连接 ##

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

**问题1 ‘calling\_sha2\_password’ is not supported.**  
解决方式 摘自[菜鸟教程][Link 3]

*  修改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>

## 3.3 python操作数据库 ##

# 创建数据库
    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.4 python操作数据表 ##

# 创建数据表
    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")

*  表的插入,创建

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, "记录插入成功。")

*  表的查询

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')

*  表的排序

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')

*  删除数据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/')

*  更新表数据 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')

## 3.5 正则表达式 ##

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具体应用 #

## 4.1 设置每天自动备份mysql数据库 ##

`04 16 * * * cd /home/nisl/ && ./save_mysql.py /mnt/e/mysql_save/`  
[配置定时任务见7.32节][7.32]

#!/usr/bin/env python3
    # save_mysql.py
    import os,sys
    import time
    # 功能: 运行脚本能够自动保存mysql数据库的内容, 并进行压缩, 按照时间进行保存
    # 参数: 运行时传入的第一个参数是需要保存到的路径文件夹
    save_path = sys.argv[1]
    if not os.path.exists(save_path):
    	os.makedirs(save_path)
    ''' mysqldump Usage: mysqldump [OPTIONS] database [tables] OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] OR mysqldump [OPTIONS] --all-databases [OPTIONS] For more options, use mysqldump --help '''
    db_host="主机ip地址"
    db_user="用户"
    db_passwd="密码"
    db_name="数据库名称"
    db_charset="utf8"
    db_backup_name=os.path.join(save_path, r"dailyreport-%s.sql" % (time.strftime("%Y-%m-%d-%H-%M")))
    if __name__ == "__main__":
    	print(f"begin to dump mysql database crm...save to { db_backup_name}");
    	# 其实主要就是执行下面这句话, 如果是本地的mysql, 可以去掉-h{db_host}
    	os.system(f"mysqldump -h{ db_host} -u{ db_user} -p{ db_passwd} { db_name} \
    		--default_character-set={ db_charset} > { db_backup_name} && gzip { db_backup_name}")

## 4.2 修改服务器上面MySQL整个列的数据 ##

功能:  
将整个数据库中file\_path字段里面的`~/system`改成`/home/nisl/syste`

pip install mysql-connector # 安装
    
    import mysql.connector
    mydb = mysql.connector.connect(
      host="ip",
      user="user",
      passwd="passwd",
    )
    mycursor = mydb.cursor()
    mycursor.execute("use 数据库") 
    
    # 第二种 读取特定字段
    print("读取特定字段")
    mycursor.execute("SELECT * FROM file")
    myresult = mycursor.fetchall()
    for i, x in enumerate(myresult):
        path = x[6].replace("~/system", "/home/nisl/system") # /home/nisl/system
        mycursor.execute(f"UPDATE file SET file_path = '{path}' WHERE file_path = '{x[6]}'")
        print(mycursor.rowcount, " 条记录被修改")
        mydb.commit()

[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
[Link 1]: https://blog.csdn.net/qq_32460819/category_11269868.html
[docker_docker win10_mysql]: https://blog.csdn.net/qq_32460819/article/details/108135829
[win10]: https://blog.csdn.net/qq_32460819/article/details/120932926
[Ubuntu]: https://blog.csdn.net/qq_32460819/article/details/120932984
[Link 2]: https://www.cnblogs.com/wuxunyan/p/9095016.html
[Link 3]: https://www.runoob.com/note/45833
[7.32]: https://blog.csdn.net/qq_32460819/article/details/89409599