连表操作、子查询、pymysql 模块、sql注入问题（感觉已没有）

【连表操作】

建表
create table dep1(
    id int,
    name varchar(20)
    );
create table emp1(
    id int primary key auto_increment,
    name varchar(20),
    sex enum('male','female') not null default 'male',
    age int,
    dep_id int
    );


插入数据
    insert into dep1 values
    (200,'技术'),
    (201,'人力资源'),
    (202,'销售'),
    (203,'运营');

insert into emp1(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);

select * from dep1;
select * from emp1;

----------------------------------------------操作
1.查询jason这个人的部门名称
    拼表操作：
    select * from dep1,emp1;     将两张表拼成一张表，结果叫：笛卡尔积
    或是
    select * from emp1,dep1 where emp1.dep_id=dep1.id;

MySQL在后面查询数据的过程中，肯定会经常用到拼表操作
所以特地开始了对应的方法：
*********************************重要
    inner join: 内连接
    left join:  左连接
    right join: 右连接
    union:      全连接


使用场景
inner join：内连接             on依据
    select * from emp1 inner join dep1 on dep1.id=emp1.dep_id;
    但这时候发现，有数据丢失，原因是，内连接，只拼接两张表共有的数据部分

left join：左连接
    select * from emp1 left join dep1 on dep1.id=emp1.dep_id;
    左边的表所有的数据都展示出来，没有对应的项就用null填充

right join：右连接
    select * from emp1 right join dep1 on dep1.id=emp1.dep_id;
    右边的表所有的数据都展示出来，没有对应的项就用null填充,(但是我的显示数据丢失)

union:       全连接，左右两个表所有的数据都展示出来
    select * from emp1 left join dep1 on dep1.id=emp1.dep_id
    union
    select * from emp1 right join dep1 on dep1.id=emp1.dep_id;

。

。

【子查询】

子查询就是我们平时解决问题的思路
    分步骤解决问题
    第一步........
    第二步........
将一个查询语句的结果当作另外一个查询语句的条件取使用

1.查询部门是技术部门或者是人力字段的员工信息
    1）.获取部门的id号
    2）.再去员工表里面筛选出对应的员工
    select id from dep1 where name='技术' or name='人力资源';
    select name from emp1 where dep1_id in (200,201);

精简写法：
    select * from emp1 where dep_id in (select id from dep1 where  name='技术' or name='人力资源');

总结：
    表的查询结果可以作为其他表的查询条件，也可以通过起别名的方式把它当作一个虚拟表根其他表关联
    多表查询就两种方式：1.先拼接表再查询 2.子查询一步一步来


------------------------------------------------------
练习

查询平均年龄在25岁以上的部门名称
只要涉及到多表查询，就有两种思路：
    方式一：连表操作
        先拿到部门和员工表，拼接之后的结果
        得出需要进行分组
        select dep1.name from emp1 inner join dep1 on emp1.dep_id=dep1.id
        group by dep1.name
        having avg(age)>25;

    2.子查询
        select name from dep1 where id in (
            select dep_id from emp1 group by dep_id
            having avg(age)>25
        );

    3.关键字 exists
    只返回T or N
    返回T的时候外层查询语句执行
    返回F的时候外层查询语句不执行
        select * from emp1 where exists (
        select * from dep1 where id>3);

        select * from emp1 where exists (
        select * from dep1 where id>300);  (后面不执行)

。

。

【python操作数据库pymysql】

基本使用

安装
    pip3 install pymysql

创建文件名不要和模块名一样

import pymysql

# 连接数据库
conn = pymysql.connect(
    host='127.0.0.1',
    user='root',
    password='199721',
    charset='utf8',  # 不要加 -
    port=3306,
    database='day4')

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 产生一个游标对象,来帮我们执行命令的
# cursor=pymysql.cursor.DictCursor # 查询结果以字典的形式返回

sql = 'select * from teacher'
res = cursor.execute(sql)
# print(res)  # execute返回的是当前sql语句所影响的行数，该返回值我们一般不用
# 获取命令执行的查询结果
print(cursor.fetchone())  # 只拿一条，并打印第一个元素{'id': 100, 'cl_name': '高三1班 '}
print(cursor.fetchmany(2))  # 可以指定拿几条
print(cursor.fetchall())  # 读取所有的数据
cursor.scroll(1, 'relative')  # 相对于光标所在的位置继续往后移动1位
cursor.scroll(1, 'absolute')  # 相对于数据的开头位置继续往后移动1位
conn.close()  # 关闭连接

===================================

pymysql操作数据库

增删改查

#  1. pymysql操作数据库的增删改查
import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    user='root',
    password='199721',
    charset='utf8',
    port=3306,
    database='day4')

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 产生一个游标对象,来帮我们执行命令的

# 增
sql = 'insert into user(name,password) values(%s,%s)'
rows = cursor.execute(sql, ('jackson', 1234))  # 放的是元组
# rows=cursor.executemany(sql,[('jason',123456),('jason2',123456),('jason3',123456)])  # 列表套元组
print(rows)  # 到这里其实没有增加
conn.commit()  # 我们需要确认


# 修改
sql = 'update user set name="jasonNB" where id=1'
rows = cursor.execute(sql)
print(rows)  # 在表中还是没有修改
conn.commit()  # 确认

# 删
sql = 'delete from user where id=1'
rows = cursor.execute(sql)
print(rows)  # 也没有用
conn.commit()  # 确认


# 查
sql = 'select * from user'
rows = cursor.execute(sql)
print(rows)  # 有

# 增删改查中，查的结果是返回一个数字，表示影响的行数，其他操作返回的数字表示操作的行数
# 需要二次确认，才能增删改

------------------------------------------------------------------------------

（在pymysql模块中如何调用存储过程）

import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    user='root',
    password='199721',
    charset='utf8',
    port=3306,
    database='day4')

cursor = conn.cursor(pymysql.cursors.DictCursor)
# # 调用存储过程
# cursor.callproc('proc_test', [1, 2])
# print(cursor.fetchall())
cursor.execute('select * from user')
print(cursor.fetchall())

 

【sql注入问题及解决办法】

SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。

SQL语言分为3种类型：

　　1、DDL语句 数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER

　　2、DML语句 数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT

　　3、DCL语句 数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE

sql注入: 利用现有的程序，将恶意的sql命令注入到后台的数据库引擎执行的能力 像符号--会注释掉之后的sql，根本 原理就是字符串的拼接name=‘%s' 原生sql中让execute帮做拼接就可以，django的orm种就不会存在这个问题

import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    user='root',
    password='199721',
    charset='utf8',
    port=3306,
    database='day4')

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 产生一个游标对象,来帮我们执行命令的
username = input('请输入用户名:')
password = input('请输入密码:')
sql = "select* from user where name='%s' and password='%s'"%(username,password)
rows = cursor.execute(sql)
if rows:
    print('登录成功')
    print(cursor.fetchall())
else:
    print('用户名或密码错误')

---------------------------------------------
上述输入密码账号看似没有问题，但是当我们不输入密码的时候，提示我们也能登录成功，其实这就是sql注入问题
解决办法

sql = "select * from user where name=%s and password=%s"
# 不要手动拼接数据，先用%s占位，然后将需要拼接的数据直接交给execute方法即可
rows = cursor.execute(sql, (username, password))
# 自动识别sql里面的%s，然后将数据替换进去
if rows:
    print('登录成功')
else:
    print('用户名或密码错误')

----------------------------------------------
36 PS：2024.5.19我重新测试了sql注入问题，发现这个漏洞已经解决了，不知道是我测试方法不对还是什么

。

。

。

【connect中一些参数的意思】

conn = pymysql.connect(
    host='127.0.0.1',
    user='root',   %
    password='199721',
    charset='utf8',
    port=3306,
    database='day4')

host：代表地址，
user：登录mysql 的用户名，
password：登录mysql 的密码，
charser：代表编码格式，
port：端口号，常规的很多都是3306也有自定义，
database：数据库名称