python 之 MySQL

多表查询练习

-- 1、查询所有的课程的名称以及对应的任课老师姓名

# 1.先确定需要几张表 课程表 老师表
# 2.简单查看每张表中的数据
select * from course;
select * from teacher;
# 3.思考查询逻辑  多表查询(连表操作)
SELECT
	course.cname,
	teacher.tname 
FROM
	course
	INNER JOIN teacher ON course.teacher_id = teacher.tid;
	
	
	
-- 4、查询平均成绩大于八十分的同学的姓名和平均成绩

# 1.先确定需要几张表 成绩表 学生表
# 2.简单查看表中数据
select * from student;
select * from score;
# 3.先查询成绩表中平均成绩大于80分的数据
# 3.1.按照学生编号分组 利用聚合函数avg求出所有学生编号对应的平均成绩
select student_id,avg(num) from score group by student_id;
# 3.2.基于上述分组之后的结果筛选出平均成绩大于80的数据
select student_id,avg(num) from score group by student_id having avg(num) > 80;
/*针对select后面通过函数或者表达式编写的字段为了后续取值方便 一般需要重命名成普通字段*/-- select student_id,avg(num) as avg_num from score group by student_id having avg(num) > 80;
# 4.最终的结果需要从上述sql语句的结果表中获取一个字段和学生表中获取一个字段
select * from student inner join (select student_id,avg(num) as avg_num from score group by student_id having avg(num) > 80) as t1
on student.sid = t1.student_id;
/*将SQL语句当做表来使用 连接的时候需要使用as起表名*/-- SELECT
	student.sname,
	t1.avg_num 
FROM
	student
	INNER JOIN ( SELECT student_id, avg( num ) AS avg_num FROM score GROUP BY student_id HAVING avg( num ) > 80 ) AS t1 ON student.sid = t1.student_id;
	
	
	
-- 7、查询没有报李平老师课的学生姓名

# 1.先确定需要用到的表 老师表 课程表 分数表 学生表
# 2.简单的查看表中数据
# 3.两种解题思路:
-- 直接查其他老师教的课然后一步步查到学生
-- 查报了李平老师课的学生编号然后取反即可(推荐)
# 4.先获取李平老师教授的课程id号
select tid from teacher where tname='李平老师'
# 5.子查询获取课程编号
select cid from course where teacher_id=(select tid from teacher where tname='李平老师')
# 6.根据课程编号去成绩表中筛选出所有报了课程编号的数据
select distinct student_id from score where course_id in (select cid from course where teacher_id=(select tid from teacher where tname='李平老师'))
# 7.根据上述学生id号去学生表中取反 获取没有报李平老师课程的学生姓名
SELECT
	sname 
FROM
	student 
WHERE
	sid NOT IN ( SELECT DISTINCT student_id FROM score WHERE course_id IN ( SELECT cid FROM course WHERE teacher_id =( SELECT tid FROM teacher WHERE tname = '李平老师' )) )
	
	
	
-- 8、查询没有同时选修物理课程和体育课程的学生姓名(只要选了其中一门的 两门都选和都没选的都不要)

# 1.先确定需要几张表
# 2.简单的查看表里面的数据
# 3.先获取物理和体育课程的id号
select cid from course where cname in ('物理','体育');
# 4.根据课程的id号先去成绩表中过滤掉没有选择这些课程的数据
select * from score where course_id in (select cid from course where cname in ('物理','体育'))
# 5.基于上述表统计每个学生编号报了几门课
select score.student_id from score where course_id in (select cid from course where cname in ('物理','体育'))
-- group by score.student_id having count(score.course_id) = 1
# 6.根据上述学生id获取学生姓名
select sname from student where sid in (select score.student_id from score where course_id in (select cid from course where cname in ('物理','体育'))
group by score.student_id having count(score.course_id) = 1)



-- 9、查询挂科超过两门(包括两门)的学生姓名和班级

# 1.先筛选出分数小于60的数据
select * from score where num < 60;
# 2.按照学生id分组然后计数即可
select student_id from score where num < 60 group by student_id having count(course_id) >= 2;
# 3.先连接班级表和学生表
select * from class inner join student on class.cid = student.class_id;
# 4.合并23的SQL
SELECT
	class.caption,
	student.sname 
FROM
	class
	INNER JOIN student ON class.cid = student.class_id 
WHERE
	student.sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count( course_id ) >= 2 );

python操作MySQL

import pymysql


# 1.链接服务端
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='kwan',
    password='123',
    database='db',
    charset='utf8mb4',
    autocommit=True  # 执行增、改、删操作自动执行
)
# 2.产生一个游标对象(等待输入命令)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 括号内不写参数，数据是元组套元组不够精确；添加此参乎上会将数据处理成字典
# 3.编写SQL语句
sql1 = 'select * from userinfo'
sql2 = 'insert into userinfo(name,pwd) values("kevin","111")'
# 4.发送给服务端
cursor.execute(sql2)
cursor.execute(sql1)  # 返回结果可以被接收，表示sql语句执行之后影响的行数；execute执行sql语句会帮你自动加分号结束符
# 5.获取命令的执行结果
res = cursor.fetchall()
print(res)

获取结果

cursor.fetchone()   # 获取结果集中一条数据
cursor.fetchall()   # 获取结果集中所有数据(列表套字典)
cursor.fetchmany()  # 获取结果集中指定条的数据
'''类似于文件光标的概念'''
# cursor.scroll(2, mode='relative')  # 基于当前位置往后移动
cursor.scroll(0, mode='absolute')  # 基于数据集开头的位置往后移动

SQL注入问题

# 数据准备 
    首先，我们可以写一个用户登录的客户端>>>:
    import pymysql

    conn = pymysql.connect(
        host='127.0.0.1',
        port=3306,
        user='kwan',
        password='123',
        database='db',
        charset='utf8mb4',
        autocommit=True  # 执行增、改、删操作自动执行
    )
    cursor = conn_obj.cursor(
        cursor=pymysql.cursors.DictCursor
    )
    # 1.获取用户名和密码
    name = input('请输入您的用户名>>>:').strip()
    password = input('请输入您的密码>>>:').strip()
    # 2.拼接查询语句
    sql = "select * from userinfo where name=%s and password=%s;" % (name,password)
    # 3.执行SQL语句
    cursor.execute(sql)
    res = cursor.fetchall()
    if res:
        print('登录成功')
    else:
        print('用户名或密码错误')
    
写好简单登录的python操作sql数据库的语句之后，有一些问题需要我们探讨>>>:
    # 问题1：写正确的用户名，错误的密码也可以登录
    请输入您的用户名>>>:jason' -- asjdifoahfias
    请输入您的密码>>>:回车
        登录成功
        
    # 问题2：用户名和密码都不正确，也可以登录
    请输入您的用户名>>>:xxx' or 1=1 -- asjdifoahfias
    请输入您的密码>>>:回车
        登录成功
        
"""上述现象就是SQL注入问题，利用的是MySQL的注释语法及逻辑运算符"""

# 解决SQL注入的问题其实很简单，就是想办法过滤掉特殊符号
    execute方法自带校验SQL注入问题，自动处理特殊符号
    ps：涉及到敏感数据的拼接，全部交给execute方法即可

    sql = 'select * from userinfo where name=%s and password=%s'
    cursor.execute(sql,(name,password))  # 自动识别%s 并自动过滤各种符合 最后合并数据
    
    '''
	execute方法补充（了解）：
    批量插入数据>>>:
        sql = 'insert into userinfo(name,password) values(%s,%s)'
        cursor.executemany(sql,[('tom',123),('tony',321),('mark',456)])
    '''

小知识点补充(了解)

as 语法

给字段起别名、起表名

comment 语法

给表、字段添加注释信息

mysql> create table server(id int) comment '这个server意思是服务器表';
Query OK, 0 rows affected (0.05 sec)

mysql> show create table server;
+--------+-------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                            |
+--------+-------------------------------------------------------------------------------------------------------------------------+
| server | CREATE TABLE `server` (
  `id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='这个server意思是服务器表' |
+--------+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)



mysql> create table t1(
    ->     id int comment '用户编号',
    ->        name varchar(16) comment '用户名'
    ->     ) comment '用户表';
Query OK, 0 rows affected (0.03 sec)

mysql> show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int DEFAULT NULL COMMENT '用户编号',
  `name` varchar(16) DEFAULT NULL COMMENT '用户名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表' |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

"""
查看注释的地方
	show create table 
	use information_schema  # 临时库
"""

concat、concat_ws 语法
concat 用于分组之前多个字段数据的拼接

mysql> select * from userinfo;
+----+-------+-----+
| id | name  | pwd |
+----+-------+-----+
|  1 | kwan  | 123 |
|  2 | jason | 123 |
|  3 | kevin | 111 |
|  4 | kevin | 111 |
|  5 | kevin | 111 |
|  6 | lili  | 111 |
+----+-------+-----+
6 rows in set (0.00 sec)

mysql> select concat(name) from userinfo;
+--------------+
| concat(name) |
+--------------+
| kwan         |
| jason        |
| kevin        |
| kevin        |
| kevin        |
| lili         |
+--------------+
6 rows in set (0.00 sec)

mysql> select concat(name,pwd) from userinfo;
+------------------+
| concat(name,pwd) |
+------------------+
| kwan123          |
| jason123         |
| kevin111         |
| kevin111         |
| kevin111         |
| lili111          |
+------------------+
6 rows in set (0.00 sec)

mysql> select name,pwd from userinfo;
+-------+-----+
| name  | pwd |
+-------+-----+
| kwan  | 123 |
| jason | 123 |
| kevin | 111 |
| kevin | 111 |
| kevin | 111 |
| lili  | 111 |
+-------+-----+
6 rows in set (0.00 sec)

mysql> select concat(name,'|',pwd) from userinfo;
+----------------------+
| concat(name,'|',pwd) |
+----------------------+
| kwan|123             |
| jason|123            |
| kevin|111            |
| kevin|111            |
| kevin|111            |
| lili|111             |
+----------------------+
6 rows in set (0.00 sec)

concat_ws 如果有多个字段，并且分隔符一致，可以使用该方法减少代码

mysql> select concat_ws('|',id,name,pwd) from userinfo;
+----------------------------+
| concat_ws('|',id,name,pwd) |
+----------------------------+
| 1|kwan|123                 |
| 2|jason|123                |
| 3|kevin|111                |
| 4|kevin|111                |
| 5|kevin|111                |
| 6|lili|111                 |
+----------------------------+
6 rows in set (0.00 sec)

exists 语法

exists后面的sql语句如果有结果那么执行前面的sql语句

如果没有结果则不执行
```
mysql> select * from userinfo where exists (select * from t1 where id<100);
Empty set (0.01 sec)
```

posted @ 2022-08-18 21:25 梦想有双休阅读(33) 评论(0) 收藏举报

刷新页面返回顶部

yi-chun

python 之 MySQL

多表查询练习

python操作MySQL

获取结果

SQL注入问题

小知识点补充(了解)

公告