mysql8 多表联合查询、pymysql
一、多表联合查询
1、数据准备
部门表和员工表
create table dep( id int primary key auto_increment, name varchar(20) ); create table emp( id int primary key auto_increment, name varchar(20), sex enum('male','female') not null default 'male', age int, dep_id int );
插入数据
insert into dep values (200,'技术'), (201,'人力资源'), (202,'销售'), (203,'运营'), (205,'保洁'); insert into emp(name,sex,age,dep_id) values ('jason','male',18,200), ('egon','female',48,201), ('kevin','male',18,201), ('nick','male',28,202), ('owen','male',18,203), ('jerry','female',18,204);
2、多表查询
子查询
一条SQL语句的执行结果当成另外一条SQL语句的执行条件
问题:查看员工jason的部门名称:
select dep_id from emp where name='jason'; select * from dep where id=200;
把上述两条SQL语句合并为一条SQL语句
select * from dep where id= (select dep_id from emp where name='jason');
连表查询
把多张实际存在的表按照表关系连成一张虚拟表(不是实际存在的表,而是临时在内存中存的)
select * from emp,dep where emp.dep_id=dep.id;
联表的专业语法
inner join # 内连接,数据只取两张表中共有的数据
left join # 左连接,数据以左表为准,展示左表所有的数据,右表没有的数据使用NULL填充
right join # 右连接,数据以右表为准,展示右表所有的数据,左表没有的数据使用NULL填充
union # 连接多条SQL语句执行的结果
1. inner join
> select * from emp inner join dep where emp.dep_id = dep.id; +----+-------+--------+-----+--------+-----+----------+ | id | name | sex | age | dep_id | id | name | +----+-------+--------+-----+--------+-----+----------+ | 1 | jason | male | 18 | 200 | 200 | 技术 | | 2 | egon | female | 48 | 201 | 201 | 人力资源 | | 3 | kevin | male | 18 | 201 | 201 | 人力资源 | | 4 | nick | male | 28 | 202 | 202 | 销售 | | 5 | owen | male | 18 | 203 | 203 | 运营 | +----+-------+--------+-----+--------+-----+----------+
2. left join
> select * from emp left join dep on emp.dep_id = dep.id; +----+-------+--------+-----+--------+--------+----------+ | id | name | sex | age | dep_id | id | name | +----+-------+--------+-----+--------+--------+----------+ | 1 | jason | male | 18 | 200 | 200 | 技术 | | 2 | egon | female | 48 | 201 | 201 | 人力资源 | | 3 | kevin | male | 18 | 201 | 201 | 人力资源 | | 4 | nick | male | 28 | 202 | 202 | 销售 | | 5 | owen | male | 18 | 203 | 203 | 运营 | | 6 | jerry | female | 18 | 204 | <null> | <null> | +----+-------+--------+-----+--------+--------+----------+
3. right join
> select * from emp right join dep on emp.dep_id = dep.id; +--------+--------+--------+--------+--------+-----+----------+ | id | name | sex | age | dep_id | id | name | +--------+--------+--------+--------+--------+-----+----------+ | 1 | jason | male | 18 | 200 | 200 | 技术 | | 3 | kevin | male | 18 | 201 | 201 | 人力资源 | | 2 | egon | female | 48 | 201 | 201 | 人力资源 | | 4 | nick | male | 28 | 202 | 202 | 销售 | | 5 | owen | male | 18 | 203 | 203 | 运营 | | <null> | <null> | <null> | <null> | <null> | 205 | 保洁 | +--------+--------+--------+--------+--------+-----+----------+
4. union
select * from emp left join dep on emp.dep_id=dep.id union select * from emp right join dep on emp.dep_id=dep.id; +--------+--------+--------+--------+--------+--------+----------+ | id | name | sex | age | dep_id | id | name | +--------+--------+--------+--------+--------+--------+----------+ | 1 | jason | male | 18 | 200 | 200 | 技术 | | 2 | egon | female | 48 | 201 | 201 | 人力资源 | | 3 | kevin | male | 18 | 201 | 201 | 人力资源 | | 4 | nick | male | 28 | 202 | 202 | 销售 | | 5 | owen | male | 18 | 203 | 203 | 运营 | | 6 | jerry | female | 18 | 204 | <null> | <null> | | <null> | <null> | <null> | <null> | <null> | 205 | 保洁 | +--------+--------+--------+--------+--------+--------+----------+
还可以起别名
select * from emp as e inner join dep as d on e.dep_id=d.id;
二、pymysql
1、如何操作MySQL?需要借助于第三方模块
pymysql
mysqlclient----->非常好用,一般情况下很难安装成功
mysqldb
2、安装模块
pip install pymysql;
3、实操
import pymysql # 1 连接mysql服务端 conn = pymysql.connect( host='localhost', port=3306, user='root', password='Zjz5740##', db='data2', charset='utf8', autocommit=True ) # 2 获取游标 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 3 执行sql语句 # sql = "insert into emp(name, sex, age, hire_date) values('kevin','male',18, NOW())" sql = "select * from emp" # 4 开始执行 affect_rows = cursor.execute(sql) # print(affect_rows) # conn.commit() # 修改、添加数据需要commit # 5 拿到具体数据 # print(cursor.fetchall()) for i in cursor.fetchall(): print(i) # 6 关闭游标、连接 cursor.close() conn.close()
注⚠️:cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)中
pymysql.cursors.DictCursor
是PyMySQL库提供的一个游标(cursor)类。它用于创建一个游标对象,该对象以字典形式返回查询结果,而不是默认的元组形式。
而默认是 cursor = conn.cursor()
这种方式创建的游标对象,查询结果以元组的形式返回,其中每个元素对应结果中的一个字段的值。
import pymysql conn = pymysql.connect(host='localhost', user='user', password='password', database='mydb') cursor = conn.cursor() cursor.execute("SELECT * FROM users") row = cursor.fetchone() print(row[0])
大元组套小元组,一个小元组就是一条记录
4、简易登录注册代码使用mysql作为数据库
import pymysql # 连接数据库 conn = pymysql.connect( host='localhost', user='root', password='Zjz5740##', database='atm' autocommit=True # 针对增 改 删自动二次确认 ) # 注册函数 def register(): # 获取用户输入的用户名和密码 username = input("请输入用户名: ") password = input("请输入密码: ") # 查询用户是否已存在 cursor = conn.cursor() sql = "SELECT * FROM user WHERE name = %s" # 避免了sql注入 cursor.execute(sql, (username,)) result = cursor.fetchone() if result: print("该用户名已存在,请重新注册!") else: # 执行插入语句,将用户信息写入数据库 insert_sql = "INSERT INTO user (name, password) VALUES (%s, %s)" cursor.execute(insert_sql, (username, password)) conn.commit() print("注册成功!") cursor.close() # 登录函数 def login(): # 获取用户输入的用户名和密码 username = input("请输入用户名: ") password = input("请输入密码: ") # 查询用户是否存在 cursor = conn.cursor() sql = "SELECT * FROM user WHERE name = %s AND password = %s" cursor.execute(sql, (username, password)) result = cursor.fetchone() if result: print("登录成功!") else: print("用户名或密码错误!") cursor.close() # 主菜单 def main_menu(): while True: print("1. 注册") print("2. 登录") print("3. 退出") choice = input("请选择操作: ") if choice == '1': register() elif choice == '2': login() elif choice == '3': break else: print("无效的选择,请重新输入!") # 执行主菜单 main_menu() # 关闭数据库连接 conn.close()
sql 注入
-
基于布尔逻辑的注入:攻击者利用布尔逻辑判断条件,通过构造恶意输入来绕过验证逻辑。例如,在登录页面的用户名和密码字段中输入
' OR '1'='1
,攻击者可以成功绕过验证逻辑,因为这个输入会使SQL查询的条件始终为真。 -
基于注释的注入:攻击者使用注释符号(例如
--
)注释掉原始SQL查询的一部分,然后在注释之后添加自己的恶意代码。这样,攻击者可以在注释符号后执行任意的SQL代码。 -
UNION注入:攻击者利用UNION操作符将恶意查询结果合并到原始查询中。通过构造恶意的UNION查询,攻击者可以从其他表中提取数据,甚至是敏感信息
import pymysql conn = pymysql.connect( host='localhost', port=3306, user='root', passwd='Z740##', database='atm', charset='utf8mb4', autocommit=True ) cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) username = input('用户名:').strip() password = input('密码:').strip() sql = "select * from user where name='%s' and password='%s' " % (username, password) print(sql) affect_rows = cursor.execute(sql) res = cursor.fetchone() if res: print('登录成功') else: print('登录失败')
第一种是使用-- 把密码那一部分的sql语句给注释掉了
第二种是‘ or 1=’1,把password设置为空,接上一个or 1=‘1’