mysql pymysql 使用

最新推荐文章于 2025-05-19 23:39:24 发布

原创最新推荐文章于 2025-05-19 23:39:24 发布 · 1k 阅读

15 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #python #pymysql

本文详细介绍了MySQL的使用，包括进出MySQL、DDL、DML、DQL、DCL等操作，以及事务和隔离级别。同时，讲解了Python中pymysql库的使用，如连接数据库、查询、建表及上传数据等，并列举了常见问题及其解决方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

相关信息在/etc/my.cnf中

执行顺序

mysql

进出mysql

# 进入本机mysql
mysql -u 用户名 -p密码    # mysql -u root -proot p后直接跟密码
mysql -h localhost -u root -proot 
mysql -h 127.0.0.1 -u root -proot
mysql --user=root --password=root
#连接到远程主机上的mysql
mysql -h 远程主机地址 -u test -p123
mysql --host=ip --user=用户名 --password=密码
'''
-h 有三种参数 
%:任意主机
localhost:仅本地可以登录（比127.0.0.0更优）
	localhost和127.0.0.1大致下一样，区别：
		localhost:
			无需联网
			不适用网卡，不受网络防火墙和网卡的限制，不会解析成ip,不占网卡、网络资源。
			本机访问
		127.0.0.1：
			无需联网
			网卡传输，受网络防火墙和网卡限制
			本机访问
		本机ip
			联网
			网卡传输，受防火墙和网卡限制
			本机或外部访问
		
Ip地址:指定的ip可登录
'''

# 退出mysql
mysql> quit;
mysql> eixt;

查看编码、索引、端口、文件路径

# 查看数据文件存放路径
mysql> show variables like '%datadir%';
# 查看用户使用端口
mysql> show variables like 'port';
#查看每个库所使用的编码
mysql> show variables like 'character%';
# 查看表中的索引，两种方式：
mysql> show index from 表名；
mysql> desc 表名;（看哪个是主键 PRI)

DDL : 定义数据库对象（库，表，列）

mysql 操作库 CRUD

1. 创建 Create
# 创建
mysql> create database db;
# 创建指定编码格式的数据库
mysql> create database db default character set utf8/gbk;
# 创建数据库，当不存在该数据库时再创建
mysql> create database if not exists db;
#创建数据库，并制定编码类型
mysql> create database if not exists db character set gbk/utf8;
2. 查询 Retrieve
# 查询所有数据库的名字
mysql> show databases;
# 查询某个数据库的创建时的编码格式
mysql> show create database db;
# 现在使用的是哪个数据库
mysql> select database();
3. Update 修改
# 修改数据库的编码格式为：
mysql> alter database db character set utf8/gbk;
4. Delete 删除
# 删除数据库
mysql> drop database db;
# 判断是否存在，然后再删除
mysql> drop database if exists db;
5. 使用
mysql> use db;

mysql 操作表 CRUD

1. Create 创建
# 创建表 一定要设置主键（是索引，当数据库中量大时，可加快查找速度）。null:可为空，not null：不可为空 
mysql> create table biao(
    #默认自增（一表最多一个,做主键），插入数据时，可不用管它，primary key主键，一列的唯一索引，可有多列组合而成复合主键，此时每个字段都是索引
    	id int auto_increment,
		name varchar(3) not null default 'hua',
    	age int(3) null default 18,
    	sex int(1) null,
     	primary key(name,sex) #复合主键，每个字段都是索引，为之后加快查找
		)engine=InnoDB default charset=utf8;
mysql> create table user(
	id int primary key auto_increment,
	name varchar(5),
	password varchar(10)
	);
	insert into user(name,password) values('bb','bbb');
	insert into user values(null,'aa','aaa');
	insert into user(name,password) values
	('bb','bbb'),
	('cc','ccc');
# 复制一个表的结构
mysql> create table teacher_table like student_table;
2. Retrieve 查询
# 查看当前数据库中有几个表
mysql> show tables;
# 查看当前数据库中某个表的结构
mysql> desc student_table;


# explain:可看是否通过主键索引进行查询，显示了 mysql 如何使用索引来处理 select 语句以及连接表
mysql> explain select name,age from biao1 where sex=0;
mysql> select * from biao1 where id>1 and name != 'mign' and sex=0 and age between 5 and 14 and num in (3,6,8) and count not in (3,12);
3. Update 修改
	1. 修改表名
		alter table 表名 rename to 新表名;
	2. 修改表的字符集/编码
		alter table 表名 character set utf8/gbk;
	3. 添加一列
		alter table 表名 add 列名 数据类型;
	4. 修改列名称、类型
	mysql> alter table 表名 modify 列名 修改后的类型;//只修改列的类型
	mysql> alter table 表名 change 原列名 新列名 修改后的类型;//修改列的名字的类型
	alter table biao change id name varchar(20);
	5. 删除列
	mysql> alter table 表名 drop 列名;//需在表中保留至少一个列
	
4. Delete 删除
# 删除表
mysql> drop table student_table;
# 如果存在某表，将其删除
mysql> drop table if exists student_table;

5. 添加索引
不同表的索引名可以重复【同个库中的外键名不能重复】，因为索引文件一表一个
idx_字段 普通索引
uk_字段 唯一索引

DML : 增删改表中的数据

1. 添加数据
	* 除了数字类型，其他类型使用要用引号（单双皆可）
	# 向表中插入一条数据
		mysql> insert into biao1 values('li',32,0);#每个字段都插入，默认给所有列添加
		mysql> insert into biao1(name,age) values('li',22); #个别字段插入，没有添加的字段，给默认值。

		# 向表中插入几条数据，不定义列名，默认给所有列添加值
		mysql> insert into biao1 values
			('ling',2,0),
    		('dsf',321,0),
        	('lsa',323,0);
1.5 添加数据replace
replace into biao1(name, sex) values('hua',12);
首先该表要有主键和唯一索引
插入时，按主键或唯一索引查找
	若没找到：插入
	若找到：先把原来的那行删掉，再插入
2. 删除数据
	#删除一条/若干条
		mysql> delete from 表名 [where name='dalian'];
		# 删除所有行，将biao1清空，列名还在
		1. mysql> delete from biao1;//不推荐使用，有多少行，会执行多少次删除操作
		2. mysyl> truncate table 表名;//推荐，先删除表，在创建一张一样结构的表，执行2次操作
3. 修改数据
	# 将biao中所有sex=0的行中的name改为'hua'，age改为18,不加where会将表中所有的记录修改
	mysql> update 表名 set name='hua',age=18 [where sex=0];

DQL : 查询表中的数据（最重要）

# 查看表中数据
select * from biao1;
select * from db.biao;#查看db库下的biao表的数据
'''
where：条件列表
group by：分组字段
having：分组之后的条件
order by：排序
limit：分页限定
'''
1. 基础查询
	1.0.查询某个表中所有列名：有两种方法，分别是：
    	>DESC 表名
    	>select columns from 表名
	1.1 多个字段的查询
		select 列名1,列名2 from 表名;#可用*表示查询所有字段select * from biao
	1.2 去除重复的结果集
		select distinct 列A from biao;//把列A所有的数据去重后返回
		select distinct 列名,列名2 from biao;//此时两个列的名字都一样时，才去重
	1.3 计算列（一般是数值型）
		# 可使用四则运算
		# 计算英语和数学成绩的总分
		select ength,math,ifnull(ength,0)+ifnull(math,0) from biao;//会构造一个ength+math列
		#ifnull(是否值的null的列，若为null则替换成xx)
	1.4 起别名
		select ength,math,ifnull(ength,0)+ifnull(math,0) as 总分 from biao;//会构造一个总分列(ength+math类名的替换）
		select max(math) 数学分数 from biao;
		select max(math) as 
2. 条件查询
	2.1 where后跟条件
	运算符
	 < > <= >= = 
	 where age <> 20;#年龄不等于20
	 where age between 20 and 30;#年龄在[20,30]
	 where age in(20,30,40);#年龄等于20或30或40
	 where math is null;#查询数学成绩为Null
	 where math is not null;#isn't 查询数学成绩不为Null
	 and && or || not != #都可以
3. 模糊查询
	like 
	'''
	_：表示单个任意字符
	%：表示任意个字符（0或多个）
	'''
	#查询名字是三个字的人
	select * from biao where name like '___';
	#查询名字第二个字是化的人
	select * from biao where name like '_化%'
	#查询姓马的人有哪些
	select * from biao where name like '马%';
	#查询含有某个词的
	select * from biao where name like '%词%';
	正则表达式：关键词regexp
	#查询 j 开头且为两个字符的数据
	select * from biao where nickname regexp 'j.' ; 
	#查询 158 开头，11 位的电话号码
	select * from biao where phone regexp '158[0-9]{9}'; 
4. 排序查询
select * from biao1 order by name; #根据name列，默认从小到大排列
select * from biao1 order by name asc; #根据name列，从小到大排列
select * from biao1 order by name desc; #根据name列，从大到小排列
select * from biao1 order by name asc,age desc; #根据name从小到大排，若相同，按age从大到下排
5. 聚合函数 #将一列数据作为整体，进行纵向的计算
	5.0 计算时，会默认排除Null的那个数据，怎么办？
		1 选择不包含null的列进行计算：eg：主键 select count(id) from biao;
		2 ifnull函数：select count(ifnull(english,0)) from biao;
	5.1 count ： 计算个数 默认排除Null的那个数据
	count(*)同count(1) 返回检索条目，不管是否包含null
	count(列名) 返回检索条目，不包含null
	count(distinct 列名) 返回检索不同值条目，不包含null
	select count(english,0) from student;#有多少人
	select count(id) from biao;
	5.2 max ：计算最大值 
	select max(math) from biao;
	5.3 min ：计算最小值
	select min(math) from biao;
	5.4 sum : 计算和 默认排除Null的那个数据
	select sum(math) from biao;
	5.5 avg : 计算平均值 
	select avg(math) from biao;#默认排除Null的那个数据
	select avg(ifnull(math,0)) from biao;#把没参加考试的那个看作0分
6. 分组查询
	select 分组字段、聚合函数	group by 分组字段;
	select 分组字段、聚合函数	group by 字段1, 字段2;
	# 按性别分组，分别查询男、女（性别）的平均分和人数
	select sex, avg(math), count(id) group by sex;
	# 按性别分组，分别查询男、女（性别）的平均分和人数，分数低于70不参与分组
	select sex, avg(math), count(id) where math>=70 group by sex;
	# 按性别分组，分别查询男、女（性别）的平均分和人数，分数低于70不参与分组,分组后，人数要大于2个人
	select sex, avg(math), count(id) where math>=70 group by sex having count(id)>2;
	where和having的区别：
	where在分组前进行限定(所以：where x group by xx)，不满足条件，不参与分组
	having在分组后进行限定(所以: group by xx having x)，不满足条件，不会被查询出来
7. 分页查询
	limit 为mysql的“方言”，在其他软件中，有不同的分页关键字
	语法：
		limit 显示前几条数据;
		limit 开始的行记录索引，查询的条数;【开始的索引=从0开始】
	select * from biao1 limit 5; #只显示前5条数据
	-- 每页显示3条
	select * from biao limit 0,3; -- 第1条~第三条
	select * from biao limit 6,3; -- 第7条~第9条
8. 多表查询
多表的联结：
0）select * from biao1,biao2;biao1每行对应一个biao2
1）左联结（left join），联结结果保留左表的全部数据，条目数和左表一样，右表的记录只有当其连接字段在左表中存在时，才会显示
2）右联结（right join），联结结果保留右表的全部数据，条目数和右表一样
3）内联结（inner join），取两表的公共数据，类似写成join
//通过z查找A中的x和B中的y，y可以为空
select x,y from A left join B on A.z=B.z;
select a.x,b.y from A as a left join B as b on a.z=b.

交叉联结/自然联结: select * from biao,biao2;==select * from biao cross join biao2;//A表中的每条数据都去匹配B表中的所有数据

// left join即left outer join
在这里插入图片描述

//inner join

约束

概念： 对表中的数据进行限定，保证数据的正确性，有效性和完整性
分类：
1.主键约束：primary key
	1.1 主键约束 非空且唯一，一般一张表只有一个字段为主键（也可有复合主键），主键时表中记录的唯一标识
	1.1.1 创建表时，添加主键约束
	create table su(
		id int primary key,
		name varchar(20)
	);
	1.1.2 创建表后，还没有数据呢，添加主键约束
	alter table su modify id int primary key;
	alter table su add primary key(id);
	1.1.3 表中已有数据后，添加主键
	*. 把表中的主键部分重复的删掉，再设置主键（但一般不现实）
	*. 新建一张表，设置主键，把原来表中的数据插进来。
	1.1.4 删除主键约束
	不正确：alter table su modify id int;
	alter table su drop primary key;#为主键的该列并不会消失，只是不再作为主键了，但当自增的id作为主键时，该命令无法执行，因为自增就意味着是主键
	1.1.5 创建表时，添加复合主键
	create table su(
		id int,
		age int,
		name varchar(20),
		primary key (age,name)
	);
	1.1.6 创建表后，还没有数据呢，添加复合主键
	alter table su add parimary key(age,name);
	1.1.7 删除复合主键
	alter table su drop primary key;
	1.2 自动增长：和主键一块使用，如果某一列是数值类型（一般是int）的，使用auto_increment，可完成值自动增长
	1.2.1 创建表时，添加自动增长
	create table su(
		id int primary key auto_increment,
		name varchar(20)
	);
	insert into table su values(10,"hua");#可指定此时的id
	insert into table su values(null,"hua");#会是11，从上次的id值自动增长一下
	1.2.2 创建表后，添加自动增长
	alter table su modify id int auto_increment;
	1.2.3 删除自动增长
	alter table su modify id int;
2. 非空约束：not null 该列的值不能为空
	2.1 创建表示添加约束
		create table su (
			id int,
			name varchar(20) not null -- name非空
		);
	2.2 创建表后，添加非空约束
		alter table su modify name varchar(20) not null;
	2.3 删除name的非空约束
		alter table su modify name varchar(20);
3. 唯一约束：unique  唯一约束限定的列的值不能重复，但可由多个null
	3.1 创建表示，添加唯一约束
	create table su(
		id int,
		phone_num varchar(20) unique -- 添加唯一约束
	);
	3.2 创建表后，添加唯一约束
	alter table su modify phone_num varchar(20) unique;
	3.3 删除列的唯一约束/唯一索引
	不可以如此：alter table su modify phone_num varchar(20); ×
	alter table su drop index phone_num;
4. 外键约束：foreign key 【同一个数据库中外键名不能重复：fk_从表名【学生表】_and_主表名【班级表】(我使用），fk_从表名_主表名, fk_从表名_主表名_on_字段 】
	4.1 让表与表之间产生关系，从而保证数据的正确性和一致性
	
	A表中的一个字段，在B表是唯一性索引【主键约束和唯一性约束】，那他就可以是A表的外键【外表的主键】
	
	当表中有冗余【意味着在数据库中具有相同数据的多个副本】，某些列的值大量重复，修改时及其不方便，要逐条修改，可将表进行拆分，eg：员工在深圳的都是研发部，可分成两张表，一张员工表（多方），一张部门表，分别存储，查询时，从员工表中得到部门编号，再从部门表中通过部门编号得到which部门，但其中一个部门撤销了之后，从部门表/主表中将其删除，但是员工表/从表中有员工仍然还有在被删除的部门，导致数据不一致。如何做呢？引入外键约束
	4.1.2 主表/主键表/-方
		以公共关键字作主键的表/公共关键字不重复的表/部门表
	4.1.3 从表/外键表/多方
		以公共关键字作外键的表/公共关键字可重复的表/员工表
	4.1.4 创建从表时，添加外键
	create table 从表名/员工表(
		dep_id int,
		constraint fk_employee__dep foreign key (dep_id) references 主表名/部门表 (id)
		);
	4.1.5 创建从表后，添加外键
	alter table 从表名 add constraint 外键约束的名字 foreign key (从表中公共关键字字段名) references 主表名 (主表中公共关键字字段名称【被唯一约束，一般是主键】);
	
	alter table 员工表 add constraint dep_id_waijian foreign key (dep_id) references 部门表 (id);
	add table 表名 CONSTRAINT `fk_sender_id` FOREIGN KEY (`sender`) REFERENCES `sl_employee` (`EMPLOYEE_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION

外键约束 On Delete 和 On Update【同on delete用法】
从数据：如删除班级表中的1班，则学生表中所有1班的学生就是从数据
on delete restrict(约束) 当删除主表中的记录时，如果有从数据，则不允许删除【主键被别的表使用时，不能删除】
on delete no action 同上
on delete cascade(级联) 当删除主表中的记录时，如果有从数据，连带从数据也删除
on delete set null 当删除主表中的记录时，如果有从数据，设置从数据对应的外键值为null
on delete no action on update no action:当删除或更新主表中的记录时，如果有从数据，则不允许删除和更新

	4.1.6 删除外键
	alter table 从表名 drop foreign key 外键约束的名字;
	4.2 级联操作
	如何修改从表/多方/员工表中某个部门的编号？直接修改主表/部门表是会报错的，所以要先把从表中对应的修改
	a. 很麻烦
		update 员工表 set dep_id=null where dep_id=1;
		将主表/部门表中部门对应的id改为5
		update 员工表 set dep_id=5 where dep_id=null;
	b. 设置级联更新
		
	级联更新和级联删除，修改主表/部门表，从表/员工表直接自动更新或删除
	4.2.1 添加级联操作
		要先删除外键 alter table 从表名 drop foreign key 外键约束的名字;
		然后设置	alter table 从表/员工表 add constraint 外键约束名 foreign key (从表中公共关键字字段名) references 主表名 (主表中公共关键字字段名称【被唯一约束，一般是主键】) on update cascade on delete casecade;

DCL : 管理用户授权

1.增 
#创建用户
mysql> create user '用户名'@'ip地址' identified by '密码';
	# ip地址：
    	# %:任意主机；localhost:仅本地可以登录；Ip地址:指定的ip可登录
	# 若无需密码
    	# identified 及其后面可以不用写
    # 一般形式
    	mysql> create user 'test'@'%' identified by '123'
2.删
mysql> drop user '用户名'@'ip地址'
	# mysql> drop user 'test'@'%';
3.改（修改密码）
3.1 修改用户密码        
3.1.1 set password for '用户名'@'ip地址' = password('新密码');
	# set password for 'root'@'localhost' = password('123');
3.1.2 update user set password=password('abc') where user=root;
	#因为 表中存储的密码是加密的，所以不能直接set password='abc'
3.2 别的用户密码忘了可登陆root用户进行修改，而忘了root用户的密码，怎么办？
3.2.1 cmd->net stop mysql 停止mysql服务 （打开服务之后，才能登陆，退出mysql）
	* 需要管理员运行cmd
3.2.2 无验证启动mysql服务器，并修改密码
	cmd->mysqld --skip-grant-tables #不关闭
	新的cmd->mysql # 登陆成功
	mysql> use mysql;
	mysql> update user set password=password('新密码') where user='root';
	关闭这两个cmd命令行
	打开任务管理器，手动结束mysqld.exe进程
3.2.3 重新启动mysql服务，使用新密码登陆
	cmd->net start mysql 停止mysql服务 （打开服务之后，才能登陆，退出mysql）
		* 需要管理员运行cmd
	cmd->mysql -u root -p
3.3 权限
3.3.1 
# 查看某个用户的权限
mysql> show grants for '用户'@'ip';
	eg: show grants for 'test'@'%';

3.3.2 授权
# 给某个用户的授权
mysql> grant 权限列表 on 数据库.表 to '用户'@'ip' [with grant option]
		//带有with grant option时，该用户登录数据库时可以创建新的用户并给用户授权
		eg: grant all privileges on 库名.表名 to 'test'@'%';
        	grant select,insert,update,delete on 
        	//给同个网段的所有ip授权：
        	grant all privileges on mydb.* to root@'192.168.1.%' identified by '123456';
	'''
	
	数据库名.*           数据库中的所有表
	数据库名.表          指定数据库中的某张表
	数据库名.存储过程     指定数据库中的存储过程
	*.*                所有数据库中的所有表
	
	usage 无权限，只能登录
	select 查看权限
	insert 插入权限
	alter 更改表权限
	create 创建表权限
	create user 创建用户权限
	drop 删除表权限
	update 
	delect 
	'''
3.3.3 取消授权
# 给某个用户取消某个权限
mysql> revoke 权限列表 on 数据库.表 from '用户'@'ip'	
4.查
# 查看所有的用户的信息，(有个库加mysql，里有个表叫user，里有用户的信息)
mysql> select User,Host,Password from mysql.user; 
	或先进入mysql库 
		use mysql;
		select User,Password from user;
# 查看现在所使用的用户名
mysql> select user();

mysql 事务

# 事务操作（批处理：不用插入一次就提交一次浪费时间；也不是所有的插入后再提交，以免中间出错，前面的都操作失败，即便该次未提交成功，也只是该批次失败，未殃及全部）：ACID 原子性（Atomicity），一致性（Consistency），隔离性（Isolation），持久性（Durability）
mysql> begin;#开启事务 或 start transaction;
mysql> insert into biao1 values('li',32,0);
mysql> insert into biao1 values
		('ling',2,0),
    	('dsf',321,0),
        ('lsa',323,0);
mysql> rollback;#发现问题，回滚，此时上面的操作不算数。若是没有问题：commit;事务确认

#查看事务默认提交方式
mysql> select @@autocommit; #1自动提交,0手动提交，mysql默认1
#设置事务提交方式
mysql> set @@autocommit = 0;#设置默认为手动

隔离

#查看隔离级别
mysql> select @@tx_isolation; # repeatable read

#设置隔离级别
mysql> set global transaction isolation level 隔离级别字符串;
set global transaction isolation level repeatable read;

特别的

null

null 表示什么也不是，不能=、>、< … 所有的判断，结果都是false，所有只能用 is null进行判断

if
if（表达式，表达式为TRUE的值，表达式为FALSE的值）
ifnull（表达式，表达式为null的值）//表达式不为null时，返回表达式的值
返回排名的函数Oracle的，mysql不能用： row_number() over(partition by col1 order by col2)//默认升序
按col1分组，组内按col2升序排序，返回每个元素在其组内的排名

使用

两个日期之间差值
datediff ( (datetype), date1,date2)，后面的大为正值，后面的小为负值

timestamp比较

select * from biao where timestamp_1>='2020-09-29 15:41:55' and timestmp_1<'2020-10-29 15:41:55'

题

在这里插入图片描述

给学生分数排名，同等分数时同样的名次

select s1.student_id,s1.score,count(s2.score) rank from score s1,score s2 where s1.score<=s2.score group by s1.student_id order by rank asc;

在这里插入图片描述
2. 给学生分数排名，同等分数时同样的名次，且名次是连着的

select s1.student_id,s1.score,count(distinct s2.score) rank from score s1,score s2 where s1.score<=s2.score group by s1.student_id order by rank asc;

在这里插入图片描述

python pymysql

python pymysql库的使用

import pymysql

# 连接数据库-执行命令（事务提交）-》关闭游标-》关闭连接

#执行一个任务，返回的是影响的行数row_count
affect_rows = cur.execute('sql语句',缺省args) 
 # 占位符都用 %s
mysql> ret = cur.execute('insert into biao1(name,age) value(%s,%s)',('hua',12))
	或 ret=cur.execute('insert into biao1(name,age) values('li',12)');
	或  cur.execute('insert into biao1(name,age) values("%s",%d)'%('xiaoming',18))
   
# 一下执行多条同样任务
cur.executemany('sql语句'，缺省args)
		# 占位符都用 %s ，第二个参数用[[],[]]或[(),()]
mysql> sql_insert = 'insert into biao1(name,age) values(%s,%s)'
mysql> param = [('hua',12),('peng',13),('ji',19)]
mysql> cur.execute(sql_insert,param)


cur.execute('select name,age from person')
#从当前光标把之下的全取了，最开始光标不在数据库里，故可以取出所有
res = cur.fetchall()# 根据cur的设置返回字典或元组，此时返回的是字典，所有的字典组成列表
# 从当前光标下取3条
res = cur.fetchmany(3)
# 从当前光标下取一条
res = cur.fetchone()

#在 fetch 数据时按照顺序进行，可以使用 cursor.scroll (num,mode) 来移动游标位置
# 主 
cur.scroll (1,mode='relative') # 相对当前位置移动【1：表示向下移动一行，-1：表示向上移动一行】
cur.scroll (2,mode='absolute') # 相对绝对位置移动 【2：表示向上移动两行，-1：表示向下移动一行】

# 对批次插入，更新，删除等改变数据库的任务，进行提交，是事务操作
conn.commit()

# 最后
# 关闭指针对象
cur.close()
# 关闭连接对象
conn.close()

pymysql 连接数据库

import pymysql
def connect_db(host,db,user,passwd,port=3306):
    # 建立数据库的连接
    conn = pymysql.connect(
    	host=host,#要连接的主机地址。在本地时，host='localhost'或'127.0.0.1'。
        port=port,
        user=user,#用户名
        passwd=passwd, #密码
        db=db, #mysql中的哪个数据库
        charset='utf8'
    )
    # 获取游标（类型设置为字典形式）
    cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
    return cur,conn
cur,conn=connect_db('114.115.121.345','Movie','Movie','HLSiC',3306)

查询使用

当结果在一个表中时，使用子查询
当结果同时在多个表中时，使用联结

pymysql 建表

def creat_table(cur,conn):
    '''
    建新表 keyword_syn
    :param cur:
    :param conn:
    :return:
    '''
    sql_create = 'create table keyword_syn(
    keyword varchar(100) not null,
    syn varchar(100) not null,
    primary key(keyword,syn)# 复合主键
    )default charset=utf8'
    cur.execute(sql_create)
    conn.commit()

    # 返回新建表的情况
    cur.execute('desc keyword_syn')
    res = cur.fetchall()
    for i in range(len(res)):
        a = res[i]
        print('字段：%s 类型 %s'%(a['Field'],a['Type']))

pymysql 上传到数据库

def insert_to_keyword_syn(cur,conn,sql,data,one_commit_num):
    '''
    :param cur:
    :param conn:
    :param data: 列表，cur.executemany(sql,data)的data的形式
    :param one_commit_num: 一次事务提交的条目数
    :return:
    '''
    a = len(data)
    b = a/one_commit_num
    c = a/one_commit_num
    # 批次提交，一次是one_commit_num个，其中最后一次提交的数据的开始下标为begin_index_num
    begin_index_num = b if a>b else b-1

    i = 0
    while(i <= begin_index_num):
        if i == begin_index_num:
            args = data[one_commit_num*i:]
            try:
                cur.execute(sql,args)
                conn.commit() #事务提交
            except Exception as e:
                conn.rollback() #事务回滚
                print('下标%d处上传失败'%(i*one_commit_num),e)
            else:
                
                print('全部提交成功')
                i += 1 # 进行下一个批处理



        else:
            scale_begin = one_commit_num*i
            scale_end = one_commit_num*(i+1)
            args = data[scale_begin:scale_end] # eg: 1000*i~1000*i+999
            try:
                cur.executemany(sql,args)
                conn.commit() #事务提交
            except Exception as e:
                conn.rollback() #事务回滚
                print('下标%d处上传失败'%(i*one_commit_num),e)
            else:
                
                print('下标%d~%d提交成功'%(scale_begin,scale_end))
                i += 1 # 进行下一个批处理

mysql 出现的问题

登录服务器后，登录创建的mysql账户时：ERROR 1045 (28000): Access denied for user ‘test’@‘localhost’ (using password: YES)
#第一种原因，密码错了
#第二种原因，数据库有一个匿名用户（''@'localhost'或''@'127.0.0.1'）,mysql会先匹配它，这样你的密码就一直是错的
	# 删掉该匿名账户
 drop user ''@'localhost';
 # 刷新
 flush privileges;
远程登录服务器的mysql服务器时：OperationalError: (2003, “Can’t connect to MySQL server on ‘62.234.53.229’ (timed out)”)
# 原因
宝塔防火墙的3306端口没有看开放
插入时遇到：1062, "Duplicate entry ‘茶馆-茶馆’ for key ‘PRIMARY’

在该表有两个字段 key_word, movie_name都是主键

问题是说：表中已经存在茶馆-茶馆了

解决方法：

insert ignore into biao1 xx # ignore 保留表中的数据
insert replace into biao1 xx # replace 替换掉表中的相同数据

不能先select一个表的记录，再按此条件进行更新和删除同一个表的记录，

解决办法是，将select得到的结果，再通过中间表select一遍

//对的
delete from Person 
where id not in (
    select id from (
        select min(id) id from Person group by Email
    ) t
);
//错的
where id not in (
    select min(id) id from Person group by Email 
);不对因为,不能将在同一个表上进行查询之后再进行删除