mysql全解 Ubuntu/win10

文章目录

• ​​一. 知识链接​​
• ​​**优秀的读者会给优秀的文章进行点赞的_.._**​​
• ​​二. 数据库的核心命令​​

• ​​2.1 安装数据库​​

• ​​a.docker安装​​
• ​​b. win10安装​​
• ​​c. Ubuntu安装​​
• ​​d. 更换mysql文件存储位置​​

• ​​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学习笔记​​

​​全部知识点跳转​​

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

二. 数据库的核心命令

2.1 安装数据库

a.docker安装

​​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安装教程​​

c. Ubuntu安装

​​点击进入Ubuntu安装教程​​

d. 更换mysql文件存储位置

# 修改文件存储路径为这个/home/nisl/system/mysql_data
sudo cp -ar /var/lib/mysql/* /home/nisl/system/mysql_data/

# 修改配置文件
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
# datadir         = /var/lib/mysql/
datadir         = /home/nisl/system/mysql_data

# 安全保护
sudo vi /etc/apparmor.d/usr.sbin.mysqld
# Allow data dir access
  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,
# 修改成 -----------------------------
# Allow data dir access
  # /var/lib/mysql/ r,
  /home/nisl/system/ r,
  # /var/lib/mysql/** rwk,
  /home/nisl/system/** rwk,

# 访问控制
sudo vim /etc/apparmor.d/abstractions/mysql
   #/var/lib/mysql{,d}/mysql{,d}.sock rw,
   /home/nisl/system/mysql_data{,d}/mysql{,d}.sock rw,

   
# 重启服务
sudo service apparmor restart
sudo service mysql start

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 数据库 增删改查

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

转自​​博客​​

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

转自​​博客​​

三、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.
解决方式 摘自​​​菜鸟教程​​

• 修改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’”)

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")
for i in mycursor.fetchall():print(i)

# 删除记录使用 "DELETE FROM" 语句：,
# 注意一定要指定where,不然整表删除
# mycursor.execute("DELETE from sites where name = 'Google'")

# 为了防止SQL注入击,使用%s占位符转义删除语句条件
sql = "DELETE FROM sites WHERE name = %s"
na = ("Google", )
mycursor.execute(sql, na) # 与上一个一致
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/')

• 更新表数据 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节

#!/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()