【连表操作】

 1 建表
 2 create table dep1(
 3     id int,
 4     name varchar(20)
 5     );
 6 create table emp1(
 7     id int primary key auto_increment,
 8     name varchar(20),
 9     sex enum('male','female') not null default 'male',
10     age int,
11     dep_id int
12     );
13 
14 
15 插入数据
16     insert into dep1 values
17     (200,'技术'),
18     (201,'人力资源'),
19     (202,'销售'),
20     (203,'运营');
21 
22 insert into emp1(name,sex,age,dep_id) values
23 ('jason','male',18,200),
24 ('egon','female',48,201),
25 ('kevin','male',18,201),
26 ('nick','male',28,202),
27 ('owen','male',18,203),
28 ('jerry','female',18,204);
29 
30 select * from dep1;
31 select * from emp1;
32 
33 ----------------------------------------------操作
34 1.查询jason这个人的部门名称
35     拼表操作：
36     select * from dep1,emp1;     将两张表拼成一张表，结果叫：笛卡尔积
37     或是
38     select * from emp1,dep1 where emp1.dep_id=dep1.id;
39 
40 MySQL在后面查询数据的过程中，肯定会经常用到拼表操作
41 所以特地开始了对应的方法：
42 *********************************重要
43     inner join: 内连接
44     left join:  左连接
45     right join: 右连接
46     union:      全连接
47 
48 
49 使用场景
50 inner join：内连接             on依据
51     select * from emp1 inner join dep1 on dep1.id=emp1.dep_id;
52     但这时候发现，有数据丢失，原因是，内连接，只拼接两张表共有的数据部分
53 
54 left join：左连接
55     select * from emp1 left join dep1 on dep1.id=emp1.dep_id;
56     左边的表所有的数据都展示出来，没有对应的项就用null填充
57 
58 right join：右连接
59     select * from emp1 right join dep1 on dep1.id=emp1.dep_id;
60     右边的表所有的数据都展示出来，没有对应的项就用null填充,(但是我的显示数据丢失)
61 
62 union:       全连接，左右两个表所有的数据都展示出来
63     select * from emp1 left join dep1 on dep1.id=emp1.dep_id
64     union
65     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()  # 关闭连接

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

 1 pymysql操作数据库
 2 
 3 增删改查
 4 
 5 #  1. pymysql操作数据库的增删改查
 6 import pymysql
 7 
 8 conn = pymysql.connect(
 9     host='127.0.0.1',
10     user='root',
11     password='199721',
12     charset='utf8',
13     port=3306,
14     database='day4')
15 
16 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 产生一个游标对象,来帮我们执行命令的
17 
18 # 增
19 sql = 'insert into user(name,password) values(%s,%s)'
20 rows = cursor.execute(sql, ('jackson', 1234))  # 放的是元组
21 # rows=cursor.executemany(sql,[('jason',123456),('jason2',123456),('jason3',123456)])  # 列表套元组
22 print(rows)  # 到这里其实没有增加
23 conn.commit()  # 我们需要确认
24 
25 
26 # 修改
27 sql = 'update user set name="jasonNB" where id=1'
28 rows = cursor.execute(sql)
29 print(rows)  # 在表中还是没有修改
30 conn.commit()  # 确认
31 
32 # 删
33 sql = 'delete from user where id=1'
34 rows = cursor.execute(sql)
35 print(rows)  # 也没有用
36 conn.commit()  # 确认
37 
38 
39 # 查
40 sql = 'select * from user'
41 rows = cursor.execute(sql)
42 print(rows)  # 有
43 
44 # 增删改查中，查的结果是返回一个数字，表示影响的行数，其他操作返回的数字表示操作的行数
45 # 需要二次确认，才能增删改

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

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

 1 import pymysql
 2 
 3 conn = pymysql.connect(
 4     host='127.0.0.1',
 5     user='root',
 6     password='199721',
 7     charset='utf8',
 8     port=3306,
 9     database='day4')
10 
11 cursor = conn.cursor(pymysql.cursors.DictCursor)
12 # # 调用存储过程
13 # cursor.callproc('proc_test', [1, 2])
14 # print(cursor.fetchall())
15 cursor.execute('select * from user')
16 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种就不会存在这个问题

 1 import pymysql
 2 
 3 conn = pymysql.connect(
 4     host='127.0.0.1',
 5     user='root',
 6     password='199721',
 7     charset='utf8',
 8     port=3306,
 9     database='day4')
10 
11 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 产生一个游标对象,来帮我们执行命令的
12 username = input('请输入用户名:')
13 password = input('请输入密码:')
14 sql = "select* from user where name='%s' and password='%s'"%(username,password)
15 rows = cursor.execute(sql)
16 if rows:
17     print('登录成功')
18     print(cursor.fetchall())
19 else:
20     print('用户名或密码错误')
21 
22 ---------------------------------------------
23 上述输入密码账号看似没有问题，但是当我们不输入密码的时候，提示我们也能登录成功，其实这就是sql注入问题
24 解决办法
25 
26 sql = "select * from user where name=%s and password=%s"
27 # 不要手动拼接数据，先用%s占位，然后将需要拼接的数据直接交给execute方法即可
28 rows = cursor.execute(sql, (username, password))
29 # 自动识别sql里面的%s，然后将数据替换进去
30 if rows:
31     print('登录成功')
32 else:
33     print('用户名或密码错误')
34 
35 ----------------------------------------------
36 PS：2024.5.19我重新测试了sql注入问题，发现这个漏洞已经解决了，不知道是我测试方法不对还是什么

。

。

。

【connect中一些参数的意思】

 1 conn = pymysql.connect(
 2     host='127.0.0.1',
 3     user='root',   %
 4     password='199721',
 5     charset='utf8',
 6     port=3306,
 7     database='day4')
 8 
 9 host：代表地址，
10 user：登录mysql 的用户名，
11 password：登录mysql 的密码，
12 charser：代表编码格式，
13 port：端口号，常规的很多都是3306也有自定义，
14 database：数据库名称