今日学习内容
Navicat可视化软件
可以充当很多数据库软件的客户端,最主要的用于MySQL
-
下载>>>正版收费>>>找破解版>>>百度查询
-
下载完成后>>>连接>>>MySQL>>>创建连接后,右键点击新建数据库>>>创建数据库后,再右点击数据库然后建表。
-
创建表完表,想要改变或查询这表结构,可以点击表右键>>>设计表
-
如果想要另存数据库文件,点击该数据库文件右键>>>转储sql文件>>>该数据库以后缀名.sql形式保存到自定义的路径中。
-
如果想要将数据库文件.sql放到该软件中,先新建一个空的数据库文件>>>可以点击该数据库文件右键>>>运行sql文件
运行sql文件:
创建数据库:
创建表:
要记得创建完表记得保存:
需要添加外键:
多表查询练习题
1、查询所有的课程的名称以及对应的任课老师姓名
4、查询平均成绩大于八十分的同学的姓名和平均成绩
7、查询没有报李平老师课的学生姓名
8、查询没有同时选修物理课程和体育课程的学生姓名
9、查询挂科超过两门(包括两门)的学生姓名和班级
-- 1、查询所有的课程的名称以及对应的任课老师
SELECT
course.cname,
teacher.tname
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid;
-- 4、查询平均成绩大于八十分的同学的姓名和平均成绩
-- 表:score、student
-- 查询结构:姓名、平均成绩>>>连表查询
#先查询学生平均成绩>80
select score.student_id, AVG(num) AS avg_num from score GROUP BY score.student_id
HAVING AVG(num) > 80;
#两个表做连表操作
SELECT
student.sname,
avg_num
FROM
student
INNER JOIN ( SELECT score.student_id, AVG( num ) AS avg_num FROM score GROUP BY score.student_id HAVING AVG( num ) > 80 ) AS score1 ON student.sid = score1.student_id;
-- 7、查询没有报李平老师课的学生姓名
-- 表:教师表、课程表、成绩表、学生表
-- 查询结果:学生姓名>>>子查询
#查询李平老师对应的老师id
SELECT
teacher.tid
FROM
teacher
WHERE
teacher.tname = '李平老师';
#查询李平老师id对应的课程id
SELECT
course.cid
FROM
course
WHERE
course.teacher_id = ( SELECT teacher.tid FROM teacher WHERE teacher.tname = '李平老师' );
#查询课程对应的成绩id
SELECT DISTINCT
score.student_id
FROM
score
WHERE
score.course_id IN (
SELECT
course.cid
FROM
course
WHERE
course.teacher_id = ( SELECT teacher.tid FROM teacher WHERE teacher.tname = '李平老师' ));
#查询成绩id对应的学生id(去重因为李平老师有两个课程)
#将没有选择李平老师的学生找出,就是取反。查询这些学生姓名
SELECT
student.sname
FROM
student
WHERE
student.sid NOT IN (
SELECT DISTINCT
score.student_id
FROM
score
WHERE
score.course_id NOT IN (
SELECT
course.cid
FROM
course
WHERE
course.teacher_id = ( SELECT teacher.tid FROM teacher WHERE teacher.tname = '李平老师' )));
-- 8、查询没有同时选修物理课程和体育课程的学生姓名
-- 表:学生表、成绩表、课程表
-- 查询结构:学生姓名>>>子查询
-- 分析:去掉同时选两个课程和没选任何这两个课程的学生
SELECT
course.cid
FROM
course
WHERE
course.cname IN ( '物理', '体育' );
#这里要做出学生分组过滤掉选同时两个课程的学生用count
SELECT
score.student_id
FROM
score
WHERE
score.course_id IN (
SELECT
course.cid
FROM
course
WHERE
course.cname IN ( '物理', '体育' ))
GROUP BY
score.student_id
HAVING
COUNT( score.course_id ) = 1;
#查询学生id对应的姓名就是将上述sql语句作为条件
SELECT
student.sname
FROM
student
WHERE
student.sid IN (
SELECT
score.student_id
FROM
score
WHERE
score.course_id IN (
SELECT
course.cid
FROM
course
WHERE
course.cname IN ( '物理', '体育' ))
GROUP BY
score.student_id
HAVING
COUNT( score.course_id ) = 1
);
-- 9、查询挂科超过两门(包括两门)的学生姓名和班级
-- 表:班级表、学生表、成绩表
-- 查询结果:学生姓名、班级>>>连表查询
#查询挂科超过两门的学生id
SELECT score.student_id FROM score WHERE score.num <60 GROUP BY score.student_id HAVING COUNT(score.course_id) >= 2;
#查询这个学生id对应的课程id
SELECT student.class_id FROM student WHERE student.sid in (SELECT score.student_id FROM score WHERE score.num <60 GROUP BY score.student_id HAVING COUNT(score.course_id) >= 2);
#关联两张表,将上述sql语句作为条件来查询学生姓名及对应的班级
SELECT class.caption, student.sname FROM class INNER JOIN student ON class.cid = student.class_id WHERE class.cid in (SELECT student.class_id FROM student WHERE student.sid in (SELECT score.student_id FROM score WHERE score.num <60 GROUP BY score.student_id HAVING COUNT(score.course_id) >= 2)) AND student.sid in (SELECT score.student_id FROM score WHERE score.num <60 GROUP BY score.student_id HAVING COUNT(score.course_id) >= 2);
python操作MySQL
第三方模块:pip3 install pymysql
import pymysql
import pymysql
# 1.链接服务端
conn = pymysql.connect(
host='127.0.0.1', #公网或者私网IP
port=3306,
user='root',
password='123',
database='class_db',
charset='utf8mb4',
autocommit=True #执行增、删、改操作自动执行conn.commit
)
# 2.产生一个游标对象(等待输入命令)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 3.编写sql语句
# sql1 = "insert into student(sname, gender, class_id) values ('肖战', '男', 3),('王一博', '男', 1)"
# sql1 = "delete from student where sname = '肖战' "
sql1 = "update student set sname = 'wyb' where sname = '王一博'"
# 4.发送给服务端
rest = cursor.execute(sql1)
print(rest) #受影响的行数
#conn.commit()
# # 5.获取命令的执行结果
# res = cursor.fetchall()
'''
1.cursor=pymysql.cursors.DictCursor: 表示把查询结果以字典的形式输出
2.conn.commit:就是对表的增、删、改操作进行第二次确认。
'''
获取结果
"""
cursor.fetchone() # 获取结果集中一条数据
cursor.fetchall() # 获取结果集中所有数据
cursor.fetchmany() # 获取结果集中指定条的数据
有点类似 文件指针,内置函数 seek(offset,whence):示用代码来控制光标的移动。
offset:控制光标移动的位移量,以字节为单位。
whence:移动模式(正负表示方向相反,为正则从左往右)
"""
import pymysql
# 1.链接服务端
conn = pymysql.connect(
host='127.0.0.1', #公网或者私网IP
port=3306,
user='root',
password='123',
database='class_db',
charset='utf8mb4',
autocommit=True #执行增、删、改操作自动执行conn.commit
)
# 2.产生一个游标对象(等待输入命令)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 3.编写sql语句
sql1 = "select * from student;"
# 4.发送给服务端
rest = cursor.execute(sql1)
# print(rest) #受影响的行数
# conn.commit()
# # 5.获取命令的执行结果
res = cursor.fetchone()
print(res)
# cursor.scroll(1, mode='relative')
#前面取了一条数据,光标移动的位置在第一条数据后面,当前在第二个字段位置往后移动。
'''
{'sid': 1, 'gender': '男', 'class_id': 1, 'sname': '理解'}
{'sid': 3, 'gender': '男', 'class_id': 1, 'sname': '张三'}
'''
# cursor.scroll(1, mode='absolute')
#基于开头位置移动
'''
{'sid': 1, 'gender': '男', 'class_id': 1, 'sname': '理解'}
{'sid': 2, 'gender': '女', 'class_id': 1, 'sname': '钢蛋'}
'''
# cursor.scroll(2, mode='relative')
#基于当前位置往后移动
'''
{'sid': 1, 'gender': '男', 'class_id': 1, 'sname': '理解'}
{'sid': 4, 'gender': '男', 'class_id': 1, 'sname': '张一'}
'''
cursor.scroll(0, mode='absolute')
#基于数据集开头位置移动
'''
{'sid': 1, 'gender': '男', 'class_id': 1, 'sname': '理解'}
{'sid': 1, 'gender': '男', 'class_id': 1, 'sname': '理解'}
'''
# res1 = cursor.fetchone()
res = cursor.fetchone()
print(res)
SQL注入问题
问题出现:
只需要用户名即可登录
不需要用户名和密码也能登录
问题原因:SQL注入
select * from userinfo where name='jason' -- haha' and pwd=''
select * from userinfo where name='xyz' or 1=1 -- heihei' and pwd=''
注入本质:
利用一些特殊符号的组合产生了特殊的含义从而逃脱了正常的业务逻辑
措施:针对用户输入的数据不要自己处理 交给专门的方法自动过滤'execute',我们需要写正确的sql语句。
# 2.产生一个游标对象(等待输入命令)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 3.获取用户登录
username = input("username>>>:").strip()
password = input("password>>>:").strip()
# 4.编写sql语句
sql1 = "select * from users where name=%s and pwd =%s "
# 5.发送给服务端
rest = cursor.execute(sql1, (username, password))
'''
插入多条数据
sql1 = "insert into student(sname, gender, class_id) values(%s,%s,%s)"
rest = cursor.executemany(sql1, [('xz', '男', 1), ('sss', '女', 2)])
'''
print(rest) #受影响的行数
# 6.获取命令的执行结果
res = cursor.fetchall()
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 如果有多个字段需要多个相同的分隔符去分隔,可以使用该方法省去多个分隔符,只在括号最前面加一个分隔符即可。
concat_ws('|', name, salary...)
标签:cursor,python,id,学习,course,score,student,Day42,SELECT
From: https://www.cnblogs.com/bjyxxc/p/16600230.html