【无标题】

Navicat可视化软件

1 安装软件
2 破解
3 连接mysql
4 创建数据库（utf8mb4）
5 创建表 （自增，主键 非空且唯一）
6 创建外键

注释 :
1 单行注释 – #
2 多行注释 /* */

多表查询练习题

1、查询所有的课程的名称以及对应的任课老师姓名
4、查询平均成绩大于八十分的同学的姓名和平均成绩
7、查询没有报李平老师课的学生姓名
8、查询没有同时选修物理课程和体育课程的学生姓名
9、查询挂科超过两门(包括两门)的学生姓名和班级

– 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

# 导入pymysql模块
import pymysql


# 1.链接服务端
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='',
    database='db5_2',
    charset='utf8mb4',
    autocommit=True  # 执行增、改、删操作自动执行conn.commit
)
# 2.产生一个游标对象(等待输入命令)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 3.编写SQL语句
sql1 = 'select * from class'
# 4.发送给服务端
cursor.execute(sql1)
# 5.获取命令的执行结果
res = cursor.fetchall()
print(res)  #[{'cid': 1, 'caption': '三年二班'}, {'cid': 2, 'caption': '三年三班'}, {'cid': 3, 'caption': '一年二班'}, {'cid': 4, 'caption': '二年九班'}]

对于用python操作mysql数据的增删改查 需要二次确认才可以执行

# 导入pymysql模块
import pymysql


# 1.链接服务端
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='',
    database='db5_2',
    charset='utf8mb4',
    autocommit=True  # 执行增、改、删操作自动执行conn.commit
)
# 2.产生一个游标对象(等待输入命令)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 3.编写SQL语句
sql1 = 'insert into class(caption)values("四年五班" )'
# 4.发送给服务端
res = cursor.execute(sql1)
print(res)
# 5.获取命令的执行结果
# res = cursor.fetchall()
# print(res)
conn.commit()

获取结果

cursor.fetchone() # 获取结果集中一条数据
cursor.fetchall() # 获取结果集中所有数据
cursor.fetchmany() # 获取结果集中指定条的数据

‘’‘类似于文件光标的概念’‘’ 移动光标

cursor.scroll(2, mode=‘relative’) # 基于当前位置往后移动
cursor.scroll(0, mode=‘absolute’) # 基于数据集开头的位置往后移动

SQL注入问题

前戏
只需要用户名即可登录
不需要用户名和密码也能登录
问题
SQL注入

只需要用户名即可登录
select * from userinfo where name=‘jason’ – haha’ and pwd=‘’
不需要用户名和密码也能登录
select * from userinfo where name=‘xyz’ or 1=1 – heihei’ and pwd=‘’

本质
利用一些特殊符号的组合产生了特殊的含义从而逃脱了正常的业务逻辑
措施
针对用户输入的数据不要自己处理 交给专门的方法自动过滤
sql = “select * from userinfo where name=%s and pwd=%s”
cursor.execute(sql, (username, password)) # 自动识别%s 并自动过滤各种符合 最后合并数据
补充
cursor.executemany() 一次性插入多行

import pymysql


# 1.链接服务端
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='',
    database='db5',
    charset='utf8mb4',
    autocommit=True  # 执行增、改、删操作自动执行conn.commit
)
# 2.产生一个游标对象(等待输入命令)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 3.编写SQL语句
username = input('username>>>:').strip()
password = input('password>>>:').strip()

sql = "select * from userinfo where name=%s and pwd=%s"
cursor.execute(sql, (username, password))
res =cursor.fetchall()
# print(res)
if res:
    print('登录成功')
    print(res)
else:
    print('用户名或者密码错误')

小知识点补充(了解)

1.as语法

给字段起别名、起表名

2.comment语法

给表、字段添加注释信息
create table server(id int) comment '这个server意思是服务器表'
create table t1(
	id int comment '用户编号',
   name varchar(16) comment '用户名'
) comment '用户表';
"""
查看注释的地方
	show create table 
	use information_schema
"""

3.concat、concat_ws语法

concat用于分组之前多个字段数据的拼接
concat_ws如果有多个字段 并且分隔符一致 可以使用该方法减少代码

4.exists语法

select * from userinfo where exists (select * from department where id<100)
exists后面的sql语句如果有结果那么执行前面的sql语句
如果没有结果则不执行