第十三章数据库


# 此语句适用于mysql5版本，若你装的是mysql8.0高版本，这样使用会报错
GRANT ALL PRIVILEGES ON test.* TO 'abuser'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION; 
 
# 报错原因：高版本数据库把创建用户和赋予权限分开了，所以高版本数据库应该按照如下步骤创建用户并授权
create user 'lappin'@'%' identified by '123456';
GRANT ALL PRIVILEGES ON test.* TO 'lappin'@'%' WITH GRANT OPTION;
 
# 刷新
flush privileges;

说明：
all privileges ：表示所有的权限，增删改查权限全部都有了
*.*：所有的数据库下面所有的表，test.*，表示test库下面所有表
'lifuche'@'%' ：test库下面所有表，所有的权限，全部都给lifuche用户， % 表示lifuche用户可以在任意机器上面进行连接登录
identified by '123456' ：远程登录连接的密码
WITH GRANT OPTION： 表示该用户可以将自己拥有的权限授权给别人。注意：经常有人在创建操作用户的时候不指定WITH GRANT OPTION选项，导致后来该用户不能使用GRANT命令创建用户或者给其它用户授权。
flush privileges：刷新权限列表

1.1.4 创建数据库

CREATE DATABASE IF NOT EXISTS test DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

说明：
CREATE DATABASE：创建数据库
IF NOT EXISTS test：如果test库不存在
DEFAULT CHARSET utf8：默认utf8字符集
COLLATE utf8_general_ci： COLLATE指规则和限制，utf8_general_ci中的ci指大小写不敏感


mysql> CREATE DATABASE IF NOT EXISTS test DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected, 2 warnings (0.02 sec)

1.1.5 查看数据库


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

1.1.6 选择数据库&查看你当前使用的数据库


mysql> use test; # 选择数据库test
Database changed
mysql> select database();# 查看你当前用的数据库
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

1.1.7 删除数据库 DROP DATABASE;

1.1.8 创建表


mysql> show tables;
Empty set (0.03 sec)
mysql> create table user(id int(10) primary key auto_increment,name varchar(20));
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user           |
+----------------+
1 row in set (0.01 sec)

1.1.9 插入表数据&查询表数据


mysql> insert into user(id,name) values(1,"张三");
Query OK, 1 row affected (0.02 sec)
 
mysql> insert into user(name) values("李四"); # id是自增的,可省略不写
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into user values(3,"王老师"); # 前面省略字段名时,values中每个字段值都要写
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into user values(4,"吴老师"),(5,"小明"),(6,"王五");# 插入多行数据
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> select * from user;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
|  3 | 王老师 |
|  4 | 吴老师 |
|  5 | 小明   |
|  6 | 王五   |
+----+--------+
6 rows in set (0.01 sec)

1.1.10 删除表数据


mysql> delete from user where id = 1;
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from user;
+----+--------+
| id | name   |
+----+--------+
|  2 | 李四   |
|  3 | 王老师 |
|  4 | 吴老师 |
|  5 | 小明   |
|  6 | 王五   |
+----+--------+
5 rows in set (0.00 sec)

1.1.11 更新表数据


mysql> update user set name="赵小明",id=10 where id = 5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from user;
+----+--------+
| id | name   |
+----+--------+
|  2 | 李四   |
|  3 | 王老师 |
|  4 | 吴老师 |
|  6 | 王五   |
| 10 | 赵小明 |
+----+--------+
5 rows in set (0.00 sec)

1.1.12 删除所有表数据

1)delete from user;

2)truncate table user;

1.1.13 删除表数据及表结构

drop table user;

删除表字段

alter table 表名 drop 字段名;

alter table studentinfo drop status;

1.1.14 delete、truncate、drop的区别

参考：drop、truncate和delete的区别_青青angle的博客-CSDN博客_truncate和delete的区别

1.1.15 查看是否设置自动提交

MySQL默认操作模式就是autocommit=1自动提交模式。这就表示除非显式地开始一个事务，否则每个查询都被当做一个单独的事务自动执行。


mysql> show variables like "autocommit";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set, 1 warning (0.01 sec)

参考：关于mysql的参数autocommit_三斤和他的朋友们的博客-CSDN博客_autocommit

autocommit变量验证：

第一步：先在客户端A中执行


mysql> show variables like "autocommit";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set, 1 warning (0.01 sec)
 
mysql>  select * from user;
+----+------+
| id | name |
+----+------+
|  1 | 一小 |
+----+------+
1 row in set (0.00 sec)

第二步：从客户端B查询表数据

第三步：然后在客户端A中执行


mysql> insert into user values(2,"小明");
Query OK, 1 row affected (0.01 sec)

第四步：在客户端A中设置autocommit = 0，并插入一条新纪录，但不commit


mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show variables like "autocommit";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)
 
mysql> insert into user values(3,"小张");
Query OK, 1 row affected (0.00 sec)

第五步：在客户端A中commit


mysql> commit;
Query OK, 0 rows affected (0.01 sec)

总结：

设定了autocommit=1后，所有的内容都被自动commit，除非写了begin，begin后的内容需要单独再commit一次才可以。
设定了autocommit=0后，所有的操作都需要commit后才会生效
如果2个终端尝试修改同一行数据，且都没有commit，第二个修改的终端则会卡死，等待第一次修改的commit。

1.1.16 查看表创建的语句

show create table user;


mysql> show create table user;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                               |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
 
mysql> show create table user \G;
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3
1 row in set (0.01 sec)
 
ERROR:
No query specified

1.1.17 查看表字段信息

desc user;


mysql> desc user;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

1.1.18 添加/删除表索引

ALTER TABLE `user` ADD INDEX name_index(`name`);

ALTER TABLE user ADD INDEX name_index(name);

注意：` 不是单引号，但可以直接不加这个符号


mysql> ALTER TABLE `user` ADD INDEX name_index(`name`);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> desc user; # key= MUL,表示一个普通索引
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  | MUL | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

删除索引：ALTER TABLE `user` drop INDEX name_index;


mysql> ALTER TABLE `user` drop INDEX name_index;# 删除索引
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> desc user;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

1.1.19 事务


mysql> create table test_transaction(id int);
Query OK, 0 rows affected (0.05 sec)
 
mysql> begin; # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into test_transaction values(1);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into test_transaction values(2);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from test_transaction;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)
 
mysql> rollback; # 回滚
Query OK, 0 rows affected (0.01 sec)
 
mysql> select * from test_transaction; # 此时表内容被清空
Empty set (0.00 sec)
 
mysql> begin; # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into test_transaction values(1);
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into test_transaction values(2);
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from test_transaction;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)
 
mysql> commit; # 提交事务
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from test_transaction;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)
 
mysql> rollback; # 提交事务后再回滚，不影响表内容
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from test_transaction;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

1.1.20 union、union all

用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。


mysql> select id from studentinfo union select id from grade;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.00 sec)
 
mysql> select id from studentinfo union all select id from grade;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
9 rows in set (0.00 sec)

1.1.21 时间相关


mysql> select  now(); # 当前时间
+---------------------+
| now()               |
+---------------------+
| 2022-06-27 16:40:36 |
+---------------------+
1 row in set (0.00 sec)
 
mysql> select date_sub(now(),interval 2 day);# 日期减法
+--------------------------------+
| date_sub(now(),interval 2 day) |
+--------------------------------+
| 2022-06-25 16:42:00            |
+--------------------------------+
1 row in set (0.01 sec)
 
mysql> select date_add(now(),interval 2 day);# 日期加法
+--------------------------------+
| date_add(now(),interval 2 day) |
+--------------------------------+
| 2022-06-29 16:42:11            |
+--------------------------------+
1 row in set (0.00 sec)
 
mysql> select curdate();# 当前日期
+------------+
| curdate()  |
+------------+
| 2022-06-27 |
+------------+
1 row in set (0.01 sec)

1.1.22 临时表


mysql> create TEMPORARY table testxx(id int(10));
Query OK, 0 rows affected, 1 warning (0.01 sec)

1.1.23 视图


mysql> create view tv as select * from grade;
Query OK, 0 rows affected (0.01 sec)
 
mysql> select * from tv;
+----+----------+----------+-------+
| ID | stuID    | course   | score |
+----+----------+----------+-------+
|  1 | 20220101 | computer |   100 |
|  2 | 20220101 | hr       |    90 |
|  3 | 20220101 | history  |    80 |
|  4 | 20220102 | history  |    60 |
|  5 | 20220102 | music    |    70 |
+----+----------+----------+-------+
5 rows in set (0.00 sec)
 
mysql> insert into grade values(6,20220104,"computer",50);
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from tv; # 视图也被更新
+----+----------+----------+-------+
| ID | stuID    | course   | score |
+----+----------+----------+-------+
|  1 | 20220101 | computer |   100 |
|  2 | 20220101 | hr       |    90 |
|  3 | 20220101 | history  |    80 |
|  4 | 20220102 | history  |    60 |
|  5 | 20220102 | music    |    70 |
|  6 | 20220104 | computer |    50 |
+----+----------+----------+-------+
6 rows in set (0.00 sec)

1.1.24 存储过程


# 创建存储过程
delimiter //
	create procedure createDatas()
	begin
		set @i = 100;
		set @n = 200;
		while @i <= @n do
			set @student_id = concat("202206",@i);
			set @name = concat("Parmley",@i);
			set @sex = "男";
			set @tel = concat("18",right(rand(),9));
			set @AdmissionDate = date_sub(now(),interval floor(rand()*50) day);
			insert into studentinfo(student_id,name,sex,tel,AdmissionDate) values(@student_id,@name,@sex,@tel,@AdmissionDate);
			set @course_one = "python";
			set @course_two = "java";
			set @score = floor(70 + rand()*30);
			insert into grade(stuID,course,score) values(@student_id,@course_one,@score);
			insert into grade(stuID,course,score) values(@student_id,@course_two,@score);
			set @i = @i + 1;
		end while;
	end //
delimiter ;
 
# 调用存储过程
call createDatas();
 
# 删除存储过程
drop procedure createDatas;

说明：

1. delimiter是mysql分隔符,在mysql客户端中分隔符默认是分号";"，delimiter //表示把mysql分隔符定义为//。
其实就是告诉mysql解释器，该段命令是否已经结束了，mysql是否可以执行了。如果一次输入的语句较多，并且语句中间有分号，这时需要新指定一个特殊的分隔符
2. create procedure createDatas()：创建存储过程
3. concat(s1,s2...)：连接字符串
4. rand(seed)：随机生成一个>=0且<1的小数
5. right(str,len)：从给定字符串str的右侧T恤指定长度len的字符。
len：要提取的字符数。如果此参数大于字符串中的字符数，则此函数将返回实际的字符串。
6. floor()：向下取整函数，如floor(2.6)=2
7. data_sub(date,INTERVAL expr type)：从日期date减去指定的时间间隔

1.1.25 触发器

1.2 扩展资料

参考SQL语言的3种分类_灰太狼_cxh的博客-CSDN博客_sql语言的分类

SQL语言按照实现的功能不同，主要分为3类：数据操纵语言（DML），数据定义语言（DDL）,数据控制语言（DCL）。

1.2.1 数据操纵语言（DML）

主要用来处理数据库中的数据内容。允许用户对数据库中的数据进行查询，插入，更新和删除等操作。DML操作是可以手动控制事务的开启、提交和回滚的

常用的DML语句及其功能：

DML语句	功能说明
SELECT	从表或视图中检索数据行
INSERT	插入数据到表或视图
UPDATE	更新数据
DELETE	删除数据
CALL	调用过程
MERGE	合并（插入或修改）
COMMIT	将当前事务所做的更改永久化（写入数据库）
ROLLBACK	取消上次提交以来的所有操作

1.2.2 数据定义语言（DDL）

是一组SQL命令，用于创建和定义数据库对象(管理数据库对象)，并且将对这些对象的定义保存到数据字典中。通过DDL语句可以创建数据库对象，修改数据库对象和删除数据库对象等。

常用的DDL语句及其功能：

DDL语句	功能说明
CREATE	创建数据库结构
ALTER	修改数据库结构
DROP	删除数据库结构
RENAME	更改数据库对象的名称
TRUNCATE	删除表的全部内容

注：每一条DDL语句执行后，Oracle都将提交当前事务。DDL操作是隐性提交的，不能rollback

1.2.3 数据控制语言（DCL）

数据控制语言用于修改数据库结构的操作权限。DCL语句主要有2种：

DCL常用语句及其功能：

DCL语句	功能说明
CRANT	授予其他用户对数据库结构的访问权限
REVOKE	收回用户访问数据库结构的权限

1.3 pymysql

数据库操作流程：
1、pymysql.connect建立链接，成功获取了conn对象
2、设定使用的数据库，select_db方法
3、获取游标cur = conn.cursor()
4、拼sql
5、cur.execute(sql语句),cur.fetchmany(sql模板字符串,[包含多组数据])
6、关闭游标：cur.close()
7、提交事务：conn.commit()
8、关闭连接：conn.close()


import pymysql
import random
def insertData():
	conn = pymysql.connect(
	host = "127.0.0.1",
	port = 3306,
	user = "root",
	passwd = "123456",
	# db = "test",
	charset = "utf8")
	cur = conn.cursor()
	conn.select_db('test')
	courseList = ['python', 'java', 'mysql', 'linux', '接口测试', '性能测试', '自动化测试','数据结构与算法']
	for i in range(1, 101):
		student_id = '201803' + '0' * (3 - len(str(i))) + str(i)
		name = random.choice(['Lucy','Tom','Lily','Amy','Dave','Aaron','Baron']) + str(i)
		tel = '1' + str(random.choice([3, 5, 7, 8])) + str(random.random())[2:11]
		sex = random.choice(['女', '男'])
		stuinfo_sql = "insert into studentInfo(student_id, name, sex, tel, AdmissionDate) \
		values('%s', '%s', '%s', '%s', date_sub(now(),interval %s day))" \
		%(student_id, name, sex, tel, random.randint(90, 120))
		cur.execute(stuinfo_sql)
		conn.commit()
		for j in courseList:
			grade_sql = "insert into grade(stuID,course,score) values('%s','%s',%s)" %(student_id,j,random.randint(80, 100))
			cur.execute(grade_sql)
	cur.close()
	conn.commit()
	conn.close()
insertData()
print("数据插入结束！ ")

1.3.1 安装pymysql

py -3 -m pip install pymysql

1.3.2 pymysql.connect()连接数据库函数


#coding=utf-8
import pymysql
# 打开数据库连接
conn = pymysql.connect(
	host = "127.0.0.1",
	port = 3306,
	user = "root",
	passwd = "123456",
	db = "test",
	charset = "utf8")
 
print(conn)
print(type(conn))

1.3.3 conn.cursor():获取游标

要想操作数据库，光连接数据库是不够的，必须拿到操作数据库的游标，才能进行后续的操作，比如读取数据、添加数据。通过获取到的数据库连接实例conn下的cursor()方法来创建游标。游标用来接收返回结果


#coding=utf-8
import pymysql
# 打开数据库连接
conn = pymysql.connect(
	host = "127.0.0.1",
	port = 3306,
	user = "root",
	passwd = "123456",
	db = "test",
	charset = "utf8")
 
 
# 使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()
print(cursor)
print(type(cursor))

1.3.4 执行sql语句execute和executemany

execute(query,args=None)

函数作用：执行单条的sql语句，执行成功后返回受影响的行数
参数说明：
query：要执行的sql语句，字符串类型
args：可选的序列或映射，用于query的参数值。如果args为序列，query中必须使用%s做占位符；如果args为映射，query中必须使用%(key)s做占位符

executemany(query,args=None)

函数作用：批量执行sql语句，比如批量插入数据，执行成功后返回受影响的行数
参数说明：
query：要执行的sql语句，字符串类型
args：嵌套的序列或映射，用于query的参数值

注意：
1.数据库性能瓶颈很大一部份就在于网络IO和磁盘IO，将多个sql语句放在一起，只执行一次IO，可以有效的提升数据库性能。推荐此方法
2.用executemany()方法一次性批量执行sql语句，固然很好，但是当数据一次传入过多到server端，可能造成server端的buffer溢出，也可能产生一些意想不到的麻烦。所以，合理、分批次使用executemany是个合理的办法

1.3.5 创建数据库


#coding=utf-8
 
import pymysql
# 创建数据库
try:
	conn = pymysql.connect(
	host = "127.0.0.1",
	port = 3306,
	user = "root",
	passwd = "123456",
	)
	cur =conn.cursor()
	cur.execute("create database if not exists pythonDB default charset utf8 collate utf8_general_ci")
	cur.close()
	print("创建数据库pythonDB成功!")
except pymysql.Error as e:
	print("Mysql Error %d:%s" %(e.args[0],e.agrs[1]))

1.3.6 创建数据表


#coding=utf-8
import pymysql
try:
	conn = pymysql.connect(
	host = "127.0.0.1",
	port = 3306,
	user = "root",
	passwd = "123456"
	)
	conn.select_db('pythondb')# 选择pythonDB数据库
	cur = conn.cursor()# 获取游标
	# 如果所建表已存在，删除重建
	cur.execute("drop table if exists User;")
	# 执行建表sql语句
	cur.execute('''CREATE TABLE `User`(
	`id` int(11) DEFAULT NULL,
	`name` varchar(255) DEFAULT NULL,
	`password` varchar(255) DEFAULT NULL,
	`birthday` date DEFAULT NULL
	)ENGINE=innodb DEFAULT CHARSET=utf8;''')
	cur.close()
	conn.close()
	print("创建数据表成功!")
except pymysql.Error as e:
	print("Mysql Error %d: %s" %(e.args[0], e.args[1]))

1.3.7 执行单条插入语句


#coding=utf-8
import pymysql
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
passwd = "123456" ,
db = "pythondb",
charset = "utf8"
)
# 使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()
# 插入一条数据
insert = cursor.execute("insert into user values(1,'Tom','123','1990-01-01')")
print("添加语句受影响的行数： ", insert)
# 另一种插入数据方法，通过格式字符串传入值，此方式可以防止sql注入
sql = "insert into user values(%s, %s, %s, %s)"
insert = cursor.execute(sql, (3,'lucy','efg','1993-02-01'))
print("添加语句受影响的行数： ", insert)
# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭数据库连接
conn.close()
print("sql语句执行成功！ ")

1.3.8 批量插入多条数据


#coding=utf-8
import pymysql
import random
import time
def now():
	return time.strftime("%Y-%m-%d")
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
passwd = "123456" ,
db = "pythondb",
charset = "utf8"
)
# 使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()
for i in range(10,20):
	# 另一种插入数据方法，通过格式字符串传入值，此方式可以防止sql注入
	sql = "insert into user values(%s, %s, %s, %s)"
	cursor.execute(sql,(random.randint(1,10000),'lucy'+str(random.randint(1,10000)),'efg'+str(random.randint(1,10000)),now()))
# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭数据库连接
conn.close()
print("sql语句执行成功！ ")

使用executemany插入多条数据：


#coding=utf-8
import pymysql
 
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
passwd = "123456" ,
db = "pythondb",
charset = "utf8"
)
# 使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()
#批量插入条数据
sql = "insert into user values(%s, %s, %s, %s)"
insert = cursor.executemany(sql, [
(5,'tom','tom','1989-03-17'),
(6,'amy','test','1898-12-01'),
(7,'lily','linux','1994-06-23')])
print("批量插入返回受影响的行数： ", insert)
# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭数据库连接
conn.close()
print("sql语句执行成功！ ")

1.3.9 查询数据

使用execute()函数得到的只是受影响的行数，并不能真正拿到查询的内容。cursor对象还提供了3种提取数据的方法：fetchone、fetchmany、fetchall.。每个方法都会导致游标动，所以必须注意游标的位置。

cursor.fetchone():获取游标所在处的一行数据，返回元组，没有返回None
cursor.fetchmany(size):接受size行返回结果行。如果size大于返回的结果行的数量，则会返回cursor.arraysize条数据;不传size时，size=1
cursor. fetchall():接收全部的返回结果行。

1. 使用fetchone()读取数据：


#coding=utf-8
import pymysql
 
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
passwd = "123456" ,
db = "pythondb",
charset = "utf8"
)
# 使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()
cursor.execute("select * from user")
while 1:
	res = cursor.fetchone()
	if res is None:
		# 表示已经取完结果集
		break
	print(res)
	#将读取到的时间格式化
	print(res[-1].strftime("%Y-%m-%d"))
# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭数据库连接
conn.close()
print("sql语句执行成功！ ")

2. 使用fetchmany()读取数据：


#coding=utf-8
import pymysql
 
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
passwd = "123456" ,
db = "pythondb",
charset = "utf8"
)
# 使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()
cursor.execute("select * from user")
# 获取游标处两条数据
resTuple = cursor.fetchmany(2)
print("结果集类型：",type(resTuple))
for i in resTuple:
	print(i)
# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭数据库连接
conn.close()
print("sql语句执行成功！ ")

3. 使用fetchall()读取数据：


#coding=utf-8
import pymysql
 
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
passwd = "123456" ,
db = "pythondb",
charset = "utf8"
)
# 使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()
cursor.execute("select * from user")
# 获取游标处两条数据
resSet = cursor.fetchall()
print("共%s条数据："%len(resSet))
print(resSet)
# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭数据库连接
conn.close()
print("sql语句执行成功！ ")

1.3.10 更新数据

1. 更新单条数据


#coding=utf-8
import pymysql
 
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
passwd = "123456" ,
db = "pythondb",
charset = "utf8"
)
# 使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()
update = cursor.execute("update user set password = 'Tom_test' where name = 'Tom'")
print("修改语句受影响的行数：",update)
# 查询一条数据
cursor.execute("select * from user where name = 'Tom'")
print(cursor.fetchone())
# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭数据库连接
conn.close()
print("sql语句执行成功！ ")

2. 批量更新数据


#coding=utf-8
import pymysql
 
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
passwd = "123456" ,
db = "pythondb",
charset = "utf8"
)
# 使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()
#查询一条数据
query = cursor.execute("select * from user")
print("表中所有数据： ")
for i in cursor.fetchall():
	print(i)
# 批量更新数据
cursor.executemany("update user set password = %s where name=%s", [('tomx2x', 'tom'),('Tomx2x', 'amy')])
# 查看更新后的结果
query = cursor.execute("select * from user")
print("表中所有数据： ")
for i in cursor.fetchall():
	print(i)
# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭数据库连接
conn.close()
print("sql语句执行成功！ ")

1.3.11 删除数据

1. 删除单条数据


#coding=utf-8
import pymysql
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
passwd = "123456" ,
db = "pythondb",
charset = "utf8"
)
# 使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()
cursor.execute("select * from user")
print("表中所有数据： ")
for i in cursor.fetchall():
	print(i)
# 删除数据
delete = cursor.execute("delete from user where name='tom'")
print("删除语句影响的行数： ", delete)
print("删除一条数据后，表中数据： ")
cursor.execute("select * from user")
for i in cursor.fetchall():
	print(i)
# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭数据库连接
conn.close()
print("sql语句执行成功！ ")

2. 批量删除数据


#coding=utf-8
import pymysql
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
passwd = "123456" ,
db = "pythondb",
charset = "utf8"
)
# 使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()
cursor.execute("select * from user")
print("表中所有数据： ")
for i in cursor.fetchall():
	print(i)
# 批量删除数据
delete = cursor.executemany("delete from user where name=%s", [('amy',), ('lily',)])
print("删除语句影响的行数： ", delete)
print("删除一条数据后，表中数据： ")
cursor.execute("select * from user")
for i in cursor.fetchall():
	print(i)
# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭数据库连接
conn.close()
print("sql语句执行成功！ ")

1.3.12 数据回滚


#coding=utf-8
import pymysql
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
passwd = "123456" ,
db = "pythondb",
charset = "utf8"
)
# 使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()
cursor.execute("select * from user")
datas = cursor.fetchall()
print("修改前的数据： \n", datas[0])
# 更新数据表中第一条数据
cursor.execute("update user set birthday='2100-08-12' where name='lucy'")
cursor.execute("select * from user")
datas = cursor.fetchall()
print("修改后的数据： \n", datas[0])
# 回滚事务
conn.rollback()
cursor.execute("select * from user")
datas = cursor.fetchall()
print("事务回滚后的数据： \n", datas[0])
# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭数据库连接
conn.close()
print("sql语句执行成功！ ")

练习：多线程并发，性能测试程序


import pymysql
import os
import random
from multiprocessing.managers import BaseManager
from multiprocessing import Pool
 
def createTable():
	conn = pymysql.connect(
	host = "localhost",
	port = 3306,
	user = "root",
	passwd = "123456")
	cur = conn.cursor()
	sql_database = 'CREATE DATABASE IF NOT EXISTS userinfo DEFAULT CHARSET utf8 COLLATE utf8_general_ci;'
	sql_table = '''create table student(
	ID int not null auto_increment comment "不为空的自增长的主键ID",
	name varchar(30) not null,
	age int default 0,
	email varchar(40),
	tel varchar(13) unique not null,
	primary key (ID)
	)engine=innodb character set utf8 comment "表注释";
	'''.encode("utf-8")
	try:
		# 建库
		cur.execute(sql_database)
		conn.select_db('userinfo')
		cur.execute("drop table if exists student;")
		# 建表
		cur.execute(sql_table)
	except Exception as e:
		print(e)
	else:
		print("数据库及数据表创建成功！ ")
		cur.close()
		conn.commit()
		conn.close()
 
 
class myMySQL(object):
	def __init__(self):
		# 打开数据库连接
		self.conn = pymysql.connect(
		host = "localhost",
		port = 3306,
		user = "root",
		passwd = "123456",
		db = "userinfo",
		charset = "utf8")
		# 使用cursor()方法获取数据库的操作游标
		self.cur = self.conn.cursor()
	
	def executeSql(self, sql) :
		print(sql)
		try:
			res = self.cur.execute(sql)
			print("sql end ", res)
			self.conn.commit()
		except:
			traceback.print_exc()
	
	def quit(self):
		print("quit====")
		self.cur.close()
		self.conn.commit()
		self.conn.close()
 
 
class MyManager(BaseManager): pass
 
def my_Manager():
	m = MyManager()
	m.start()
	return m
 
# 将myMySQL类注册到MyManager管理类中
MyManager.register('myMySQL', myMySQL)
# print("----", MyManager.__dict__)
 
def run(my_sql):
	print("subprocesses is", os.getpid())
	# 造数据
	name = 'Amy' + str(random.randint(1, 100)) + '_' + str(os.getpid())
	age = random.randint(1, 100)
	email = name + '@qq.com'
	tel = '1' + str(random.choice([3, 5, 7, 8])) + str(random.random())[2:11]
	sql = 'insert into student(name, age, email, tel) values("%s", "%s", "%s", "%s")' %(name, age, email, tel)
	#插入数据
	my_sql.executeSql(sql)
 
 
if __name__ == '__main__': 
	createTable()
	#创建进程共享数据对象
	manager = my_Manager()
	#因为myMySQL类注册到MyManager管理类中，所以可以使用如下语句创建共享对象中的myMySQL实例对象，在不同进程间调用
	my_sql = manager.myMySQL()
	# print("111", my_sql)
	print('Parent process %s.' % os.getpid())
	p = Pool(os.cpu_count())
	n = 100
	while n:
		p.apply(run, args = (my_sql,)) #每调用一次此方法会生成一个进程，最多生成和cpu盒数相当的进程
		n -= 1
	print('Waiting for all subprocesses done...')
	p.close()
	p.join()
	print('All subprocesses done.')
	my_sql.quit()

相关阅读:
HTTP协议和HTTPS协议
 openvswitch学习
 Linux 执行 shell 报错 $‘\r‘ 的解决办法
 [大模型]QAnything纯Python环境安装教程
 解决 cocos2d-x-4.0 IOS构建出错的问题—执行命令出错，返回值：65
#力扣：LCP 06. 拿硬币@FDDL
java计算机毕业设计疆域特色农家乐系统源码+mysql数据库+系统+lw文档+部署
 Python数据可视化基础：使用Matplotlib绘制图表
 长尾关键词-长尾关键词的作用-长尾关键词挖掘软件
 【相机坐标系、ORB_SLAM2坐标系】
原文地址：https://blog.csdn.net/qq_22895113/article/details/125479774

第十三章 数据库

一、数据库

1.1 基础操作

1.1.1 连接数据库

1.1.2 指定端口

1.1.3 mysql授权语句