MySQL数据行操作+员工管理Python案例

一、MySQL数据行操作

1. 新增数据

insert into 表名(列名,列名) values(值,值);
insert into 表名(列名,列名) values(值,值),(值,值),(值,值),(值,值);

insert into tb2(salary,age) values(20000,28);  //单行插入
insert into tb2(salary,age) values(30000,38),(40000,40); //批量插入

2.删除数据

delete from 表名;              //删除表中 所有数据
delete from 表名 where 条件;   //删除表中 某些条件下的数据

delete from tb7;
delete from tb7 where id = 3;
delete from tb7 where id = 4 and name="谢涛";
delete from tb7 where id = 4 or name="谢涛";
delete from tb7 where id > 4;
delete from tb7 where id >= 4;
delete from tb7 where id != 4;
delete from tb7 where id in (1,5);

3.修改数据

update 表名 set 列=值;
update 表名 set 列=值,列=值;
update 表名 set 列=值 where 条件;

update tb7 set password="哈哈哈";
update tb7 set email="哈哈哈" where id > 5;
update tb7 set age=age+10 where id > 5;

4.查询数据

select * from 表名称;                //查询表中所有数据
select 列名称,列名称 from 表名称;     //查询表中 这两列 数据
select 列名称,列名称 from 表名称 where 条件

select * from tb7;
select id,name from tb7;
select id,name from tb7 where id > 10;
select id,name from tb7 where name="xx" and password="xx";

小结
我们平时开发系统时，一般情况下：

创建数据库
创建表结构
都是需要提前通过工具+命令创建。

但是，表中的数据一般情况下都是通过程序（python代码）来实现增删改查。

二、员工管理Python案例

第一步： 创建表结构

create database unicom DEFAULT CHARSET utf8 COLLATE utf8_general_ci; //创建数据库
use unicom;     //进入数据库（进入文件夹）

//创建表
create table admin(                            
    id int not null auto_increment primary key,
    username varchar(16) not null,
    password varchar(64) not null,
    mobile char(11) not null
) default charset=utf8;

第二步：Python操作MySQL

用Python代码连接MySQL并发送指令。

pip3.9 install pymysql

1.创建数据
1.1初级查询（仅生成一条数据）

import pymysql

# 1.连接MySQL
# 创建连接对象 连接这个db='unicom'数据库
conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd="root", charset='utf8', db='unicom')
#使用连接对象conn获得一个cursor对象，使用cursor提供的方法来进行工作. 1.执行命令,2.接收返回值
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 2.发送指令
# execute仅生成指令    这里仅仅生成一条数据
cursor.execute("insert into admin(username,password,mobile) values('pkk','123','15155555555')")
conn.commit()# 提交指令

# 3.关闭
cursor.close()
conn.close()

1.2中级查询
实现 动态拼接SQL,并插入到数据库中

import pymysql
# 用户输入的数据添加到数据库中
# 1.连接MySQL
conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd="root", charset='utf8', db='unicom')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 2.发送指令（千万不要用字符串格式化去做SQL的拼接，安全隐患SQL注入）
#实现 动态拼接SQL,并插入到数据库中
# 方式一：传列表
sql = "insert into admin(username,password,mobile) values(%s,%s,%s)"  #%s作占位符
cursor.execute(sql, ["pkk", "123456", "1999999999"])   # 第二个参数 用 列表，替换占位符
# 方式二： 传字典
# sql = "insert into admin(username,password,mobile) values( %(n1)s, %(n2)s, %(n3)s)"
# cursor.execute(sql, {"n1": "集宁", "n2": "qwe123", "n3": "1999999999"})
conn.commit()
# 3.关闭
cursor.close()
conn.close()

1.3、高级查询

import pymysql
while True:
   # 通过用户输入 动态创建数据
    user = input("用户名：")
    if user.upper() == 'Q':
        break
    pwd = input("密码：")
    mobile = input("手机号：")
    # 1.连接MySQL
    conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd="root", charset='utf8', db='unicom')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    # 2.发送指令
    sql = "insert into admin(username,password,mobile) values(%s,%s,%s)"
    cursor.execute(sql, [user, pwd, mobile])  # 动态传入列表值
    conn.commit()  # 提交命令
    # 3.关闭
    cursor.close()
    conn.close()

2.查询数据
2.1 、fetchall()查询所有数据
execute()的第二个参数：[2,] 列表加逗号，表示列表只有一个元素

import pymysql
# 1.连接MySQL
conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd="root", charset='utf8', db='unicom')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 2.执行查询指令（ *** 千万不要用字符串格式化去做SQL的拼接，安全隐患SQL注入***）
# cursor.execute("select * from admin where id>2")    #执行sql指令:获取id>2的数据
cursor.execute("select * from admin where id>%s",[2,])    #也可以使用占位符（execute的第二个参数） [2,] 列表加逗号，表示列表只有一个元素

# fetchall() 执行sql，获取所有数据
data_list = cursor.fetchall() # fetchall() 执行sql，获取所有数据
print(data_list)  # data_list是 一个列表，列表中的元素是字典[ {key:value} ]

for row_dic in data_list:  #遍历列表，得到每一行字典
    print(row_dic)

# 3.关闭连接
cursor.close()
conn.close()

2.2、fetchone()获取符合条件的第一条数据，仅显示一条数据
execute()的第二个参数：[2,] 列表加逗号，表示列表只有一个元素

import pymysql

#1.连接MySQL
conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd="root", charset='utf8', db='unicom')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

 2.发送指令（ *** 千万不要用字符串格式化去做SQL的拼接，安全隐患SQL注入***）
cursor.execute("select * from admin where id > %s", [2, ])

#获取符合条件的第一条数据，字典    None
res = cursor.fetchone()
print(res)  # {'id': 3, 'username': '集宁', 'password': 'qwe123', 'mobile': '1999999999'}

 3.关闭连接
cursor.close()
conn.close()

3.删除数据

import pymysql

 1.连接MySQL
conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd="root123", charset='utf8', db='unicom')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

 2.发送指令（ *** 千万不要用字符串格式化去做SQL的拼接，安全隐患SQL注入***）
cursor.execute("delete from admin where id=%s", [3, ]) #删除id=3的一行数据   [3,] 列表加逗号，表示列表只有一个元素
conn.commit()

#3.关闭
cursor.close()
conn.close()

4.修改数据

import pymysql

#1.连接MySQL
conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd="root123", charset='utf8', db='unicom')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

 2.发送指令（ *** 千万不要用字符串格式化去做SQL的拼接，安全隐患SQL注入***）
 # 修改id=5的行数据 的 mobile 为1888888888
cursor.execute("update admin set mobile=%s where id=%s", ["1888888888", 4, ])
conn.commit()

 3.关闭
cursor.close()
conn.close()

强调：

在进行 新增、删除、修改时，一定要记得commit（），不然数据库没有数据。

cursor.execute(“…”)

conn.commit()

在查询时，不需要commit（），执行fetchall / fetchone（）

cursor.execute(“…”)

第一条数据，字典，无数据时是None

v1 = cursor.fetchone()

所有数据，列表套字典，无数据时是空列表

v1 = cursor.fetchall()

对于SQL语句不要用Python的字符串格式化进行拼接（会被SQL注入），一定要用execute+参数
cursor.execute(“.%s… %s”, [“xx”,“xx”])

以上笔记均来自B站 银角大王-武沛齐，仅限学习使用。