Navicat可视化软件
一、介绍
可以充当很多数据库软件的客户端，主要用于MySQL
二、下载
1.网址
正版收费，可以百度破解版
三、主要功能介绍
1.连接
可以选择数据库软件，如MySQL、SQL server……等
2.新建查询
可以用SQL语句获取数据
3.库
3.1.逆向数据库到模型
直观看到库里表与表之间的关系
4.表
选中表 >>> 右键点击
4.1.打开表
可以修改数据值
4.2.设计表
可以修改字段
多表查询练习题
一、题目
1、查询所有的课程的名称以及对应的任课老师姓名
2、查询平均成绩大于八十分的同学的姓名和平均成绩
3、查询没有报李平老师课的学生姓名
4、查询没有同时选修物理课程和体育课程的学生姓名
5、查询挂科超过两门(包括两门)的学生姓名和班级
二、答案
1、查询所有的课程的名称以及对应的任课老师姓名
SELECT cname,tname FROM course INNER JOIN teacher on teacher_id= tid;
2、查询平均成绩大于八十分的同学的姓名和平均成绩
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;
3、查询没有报李平老师课的学生姓名
SELECT sname FROM student WHERE sid not in (SELECT student_id FROM score WHERE course_id in (SELECT cid FROM course WHERE teacher_id in (SELECT tid FROM teacher WHERE tname='李平老师')))
4、查询没有同时选修物理课程和体育课程的学生姓名
SELECT
sname
FROM
student
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 t1 ON sid = t1.student_id;
5、查询挂科超过两门(包括两门)的学生姓名和班级
SELECT
caption,
sname
FROM
class
INNER JOIN (
SELECT
sname,
class_id
FROM
student
INNER JOIN ( SELECT student_id, COUNT( num < 60 ) AS lose_count FROM score GROUP BY student_id ) AS t1 ON sid = t1.student_id
WHERE
t1.lose_count >= 2
) AS NAME ON cid = NAME.class_id;
python操作MySQL
一、导入pymysql模块
pip install pymysql
二、代码演示
import pymysql
# 1.链接服务端
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123',
database='company',
charset='utf8mb4',
autocommit=True # 增删改查操作自动执行conn.commit二次确认
)
# 2.产生一个游标对象(等待输入命令)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 3.编写SQL语句
sql1 = 'select * from teacher'
# 4.发送给服务端
cursor.execute(sql1)
# 5.获取命令的执行结果
res2 = cursor.fetchone()
res3 = cursor.fetchall()
cursor.scroll(0, mode='absolute')
res4 = cursor.fetchmany(2)
print(res2)
print(res3)
print(res4)
# {'tid': 1, 'tname': '张磊老师'}
# [{'tid': 2, 'tname': '李平老师'}, {'tid': 3, 'tname': '刘海燕老师'}, {'tid': 4, 'tname': '朱云海老师'}, {'tid': 5, 'tname': '李杰老师'}]
# [{'tid': 1, 'tname': '张磊老师'}, {'tid': 2, 'tname': '李平老师'}]
三、获取结果
1.获取结果中的一条数据
cursor.fetchone()
2.获取结果中的所有数据
cursor.fetchall()
3. 获取结果中的指定数据
cursor.fetchmany()
4.基于数据集开头的位置往后移，类似于光标
cursor.scroll(0,mode='absolute')
5.基于数据集当前的位置往后移，类似于光标
cursor.scroll(0,mode='relative')
四、SQL注入问题
1.现象
只需要用户名就能登录
不需要用户名和密码也能登录
2.本质
利用一些特殊符号的组合产生了特殊的含义从而逃脱了正常的业务逻辑
3.解决方法
针对用户输入的数据不要自己处理，交给专门的方法自动过滤
sql = "select * from userinfo where name=%s and pwd=%s"
cursor.execute(sql,(username,pwd))
# 自动识别%s，并自动过滤各种符号，最后合并数据
补充
cursor.executemany()
五、小知识补充
1.as 语法
给字段起别名、起表名
2.comment 语法
给表、字段添加注释信息
create table server(is int) comment '阿巴阿巴'
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语句；如果没有就不执行
练习题
1.查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
-- SELECT tid FROM teacher WHERE tname='李平老师';
# 获取李平老师的id
-- SELECT cid FROM course WHERE teacher_id=(SELECT tid FROM teacher WHERE tname='李平老师')
# 获取课程id
-- SELECT DISTINCT student_id FROM score WHERE student_id in (SELECT cid FROM course WHERE teacher_id=(SELECT tid FROM teacher WHERE tname='李平老师'))
# 获取上李平老师课的学生id
-- SELECT student_id FROM score GROUP BY student_id HAVING student_id in (SELECT DISTINCT student_id FROM score WHERE student_id in (SELECT cid FROM course WHERE teacher_id=(SELECT tid FROM teacher WHERE tname='李平老师'))) ORDER BY AVG(num) DESC LIMIT 1;
# 获取平均分最高的学生id
SELECT
sname # 平均分最高的获取学生名
FROM
student
WHERE
sid = (
SELECT
student_id
FROM
score
GROUP BY
student_id
HAVING
student_id IN (
SELECT DISTINCT
student_id
FROM
score
WHERE
student_id IN (
SELECT
cid
FROM
course
WHERE
teacher_id =(
SELECT
tid
FROM
teacher
WHERE
tname = '李平老师'
)))
ORDER BY
AVG( num ) DESC
LIMIT 1
);
