多表查询练习题、数据库可视化软件以及python操作mysql

多表查询练习题

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

-- 表：课程表，老师表
select c.cname,t.tname from course c inner join teacher t on t.tid = c.teacher_id;
-- 4、查询平均成绩大于八十分的同学的姓名和平均成绩
-- 表：分数表，学生表
select student.sname,sa.an from(select student_id,avg(num) as an from score group by student_id having avg(num)>80) sa inner join student on student.sid =sa.student_id;

-- 7、查询没有报李平老师课的学生姓名
-- 表：课程表，老师表，分数表，学生表
select sname from student where sid not in (select distinct student_id from score where course_id in (select tid from teacher where tname='李平老师') );
-- 8、查询没有同时选修物理课程和体育课程的学生姓名(只要选了其中一门的 两门都选和都没选的都不要)
-- 表： 课程表，学生表，分数表
select s.sname from student s inner join
(select student_id,count(course_id) from score where course_id in(select cid from course where cname in('物理','美术')) group by student_id having count(course_id)=1) as ss
 on ss.student_id =s.sid
-- 9、查询挂科超过两门(包括两门)的学生姓名和班级
-- 表： 分数表，学生表，课程表
select s.sname from (select s.class_id,s.sname as name from student s,(select student_id,count(student_id) from score where num<60 group by student_id having count(student_id) >1) ss where s.sid =ss.student_id) sss inner join class on class.cid =sss.class_id;

Navicat可视化软件

• 下载网址:http://www.navicat.com.cn

DataGrip可视化软件

• 下载网址: https://www.jetbrains.com.cn/datagrip/

以上两款都是数据库可视化管理软件

python操作MySQL

导入第三方模块

pip3.8 install pymysql  # 将pymysql安装到指定的pip版本

py连接mysql步骤演示

import pymysql

# 1.连接服务端
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    database='db08',
    password='123456',
    charset='utf8mb4',
    autocommit=True
)
# 2.创建游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 括号内参数将结果组织成字典形式,括号内不写参数，默认为元组套元组的形式显示数据集

# 3.编写sql语句
sql = 'select * from student;'

# 4.将sql发送到数据库
cursor.execute(sql)

# 5.获取命令的执行结果
result = cursor.fetchall()
print(result)

获取结果

cursor.fetchall()  # 获取全部结果集
cursor.fetchone()  # 获取结果集中一条数据
cursor.fetchmany(3)  # 获取结果集中指定条数据，当参数大于结果集数据个数也还是显示全部结果集数据

'类似于文件光标的概念'
cursor.scroll(1,mode='relative')  # 基于当前位置向前移动指定参数位置（1个数据量）
res = cursor.fetchone()
cursor.scroll(1,mode='absolute')  # 基于初始位置移动指定参数位置(1)
res1 = cursor.fetchone()
print(res)
print(res1)

SQL注入问题

sql注入问题即：

• 不需要用户名也可登录
• 不需要用户名和密码也能登录

代码演示：

1. select * from user where name='jason'  -- xxxx and pwd=''   # 利用了mysql的注释语法逃避了密码的输入

2.select * from user where name='xxx' or 1=1 -- xxxxxx  # 利用了where后面比较运算符or

本质：利用了一些特殊符号的组合产生了特殊的含义从而逃脱了正常的业务逻辑

措施：针对用户的数据不要自己处理，交给专门的方法自动过滤

sql = f'select * from user where name=%s and password =%s;'

cursor.execute(sql,(username,password))  # 自动识别%s，并自动过滤各种符号，最后合并数据

补充:

cursor.executemany()  # 批量执行，可用于批量插入数据

sql = f'insert into user(username,password) values (%s,%s)'

list_all=[]  # 定义一个空列表，存放每条插入的数据
for i in range(1000):
    list_i=[f'jason{i}',f'111{i}']  # 将每条数据存放在列表中
    list_all.append(list_i)  # 添加进总列表
cursor.executemany(sql,list_all)  # 第二个参数应该是列表或元组套列表或元组的形式

小知识点补充(了解)

1. as语法

   给字段起别名、起表名

2. comment语法

   给表、字段添加注释信息

   查看注释的地方
   1. show create table 
   2. use information_schema
   

3. concat、concat_ws语法

   concat用于分组之前多个字段数据的拼接
   select concat(name,'|',age,'|',salary) as info from emp1;
   
   concat_ws如果有多个字段，并且分隔符一致，可以使用该方法减少代码
   select concat_ws("|",name,age,salary) as info from emp1;
   

   

4. exists语法

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

作业1

1.pymysql编写注册登录功能
注册：
import pymysql

conn=pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123456',
    database='db08',
    charset='utf8',
    autocommit=True
)
while True:
    username=input('username>>>:').strip()
    password = input('password>>>:').strip()
    cursor=conn.cursor()
    sql_reg= 'insert into user(username,password) values(%s,%s)'

    try: # 设置username字段为唯一字段，报错即已经注册过
        cursor.execute(sql_reg,(username,password))
        print('注册成功')
    except Exception as e:
        print('用户已经注册过')

登录：

import pymysql

conn=pymysql.connect(
    host='127.0.0.1',
    port=3306,
    database='db08',
    user='root',
    password='123456',
    charset='utf8mb4'
)
while True:
    username = input('username>>>:').strip()
    password = input('password>>>:').strip()
    cursor = conn.cursor()
    sql = 'select * from user where username=%s and password=%s'
    cursor.execute(sql,(username,password))
    res = cursor.fetchall()
    if res:
        print('登录成功')
        break
    else:
        print('登录失败')
        continue

作业2

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

2、查询学生表中男女生各有多少人
	select gender,count(sid) from student group by gender;
3、查询物理成绩等于100的学生的姓名
	-- 表：学生表、分数表、课程表
  select sname from student where sid in(select student_id from score where num=100 and course_id=(select cid from course where cname='物理'));
4、查询平均成绩大于八十分的同学的姓名和平均成绩
	select s.sname,avg_num from student s,(select student_id,avg(num) as avg_num from score group by student_id having avg(num)>80) ss where s.sid=ss.student_id;
5、查询所有学生的学号，姓名，选课数，总成绩
	select s.sid,s.sname,course_number,all_scores from student s,(select student_id,count(course_id) as course_number,group_concat(num) all_scores from score group by student_id) info where s.sid=info.student_id;
6、 查询姓李老师的个数
select count(*) from teacher where tname regexp '^李.*';
7、 查询没有报李平老师课的学生姓名
select sname from student where sid not in (select distinct student_id from score where course_id in (select tid from teacher where tname='李平老师') );