mysql数据库入门（下）_(1)查询所有“数据库系统”课程的课程号及相应教材-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_54876093/article/details/116144698

这篇博客详细介绍了MySQL数据库的多表查询、三种连表方式、子查询的使用，并探讨了视图、触发器、存储过程的概念及应用。还涉及到了权限管理、PyCharm远程连接MySQL的注意事项以及SQL注入问题的防范。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

My S Q L 数据库（二）

一、多表查询

要实现多表查询首先要回连表操作。

# 根据where只能筛选到有关联的数据，无关联的就略过了。
select * from employee,department where employee,dep_id = department,id;

二、三种连表方式：

内连接：支取两张表共同的部分

select * from employee inner join department on employee.dep_id = department.id;

左连接：在内连接的基础上 保留左表 的记录。

select * from employee left join department on employee.dep_id = department.id;

右连接:在内连接的基础上 保留右表 的记录。

select * from employee right join department on employee.dep_id = department.id;

全外连接:在内连接的基础上保留左右两表没有对应的记录。

select * from employee left join department on employee.dep_id = department.id;
union
select * from employee right join department on employee.dep_id = department.id;

A INNER JOIN B ON……：内联操作，将符合ON条件的A表和B表结果均搜索出来，然后合并为一个结果集。

A LEFT JOIN B ON……：左联操作，左联顾名思义是，将符合ON条件的B表结果搜索出来，

然后左联到A表上，然后将合并后的A表输出。

A RIGHT JOIN B ON……：右联操作，右联顾名思义是，将符合ON条件的A表结果搜索出来，

然后右联到B表上，然后将合并后的B表输出。

三、子查询

指的是一样表数据加括号以用于查询另一张表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-E12VOWdH-1619403899268)(MySQL数据库（二）.assets/image-20210405220453350.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0fEbhtz1-1619403899270)(MySQL数据库（二）.assets/image-20210405221646869.png)]

where加记录名加运算符。
exists 是指子查询的结果是否存在，存在就返回正确值。不存在就返回False.

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XjIarRD4-1619403899271)(MySQL数据库（二）.assets/image-20210405221639241.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-y2q9SrAb-1619403899273)(MySQL数据库（二）.assets/image-20210406113322246.png)]

综合练习

1、查询所有的课程的名称以及对应的任课老师姓名
select teacher.tname,course.cname from teacher inner join course on teacher.tid = course.teacher_id;

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=2);

4、查询平均成绩大于八十分的同学的姓名和平均成绩
mysql> select sname from student where sid in (select student_id from score group by student_id having avg(num)>80);
+--------+
| sname  |
+--------+
| 张三   |
| 刘三   |
+--------+
2 rows in set (0.00 sec)

mysql> select student_id,avg(num) from score group by student_id having avg(num)>80;
+------------+----------+
| student_id | avg(num) |
+------------+----------+
|          3 |  82.2500 |
|         13 |  87.0000 |
+------------+----------+
2 rows in set (0.00 sec)

5、查询所有学生的学号，姓名，选课数，总成绩
select sid,sname from student 


6、 查询姓李老师的个数

7、 查询没有报李平老师课的学生姓名

8、 查询物理课程比生物课程高的学生的学号

9、 查询没有同时选修物理课程和体育课程的学生姓名

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

11 、查询选修了所有课程的学生姓名

12、查询李平老师教的课程的所有成绩记录
 
13、查询全部学生都选修了的课程号和课程名
select 
	cid,cname 
from 
	course
where 
	cid in (
                select
            course_id 
        from 
            score 
        group by 
            course_id 
        having 
            count(sid)=(
                select 
                    count(sid) 
                from 
                    student
	)
);

14、查询每门课程被选修的次数

15、查询之选修了一门课程的学生姓名和学号

16、查询所有学生考出的成绩并按从高到低排序（成绩去重）

17、查询平均成绩大于85的学生姓名和平均成绩
select 
	t1.sname,t2.avg_num
from 
	student as t1
inner join
(
select 
	student_id,avg(num) as avg_num
from 
	score
group by 
	student_id
having
	avg(num)>85
)as t2
on t1.sid=t2.student_id
;

18、查询生物成绩不及格的学生姓名和对应生物分数

19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
select 
	sname 
from 
	student 
where 
	sid=
    (select 
        student_id
    from
        score
    where
        course_id 
    in
        (select
            cid
        from
            course
        where
            teacher_id=2)
    group by
        student_id
    order by
        avg(num) desc
    limit 1);

20、查询每门课程成绩最好的前两名学生姓名
select
	course_id
from
	score
group by
	course_id
;


21、查询不同课程但成绩相同的学号，课程号，成绩

22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；

23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；

24、任课最多的老师中学生单科成绩最高的学生姓名
select sname from student
where sid in 
(select student_id from score as t1
inner join
(select
	course_id,max(num) as max_num
from
	score
where
	course_id
in
    (select
        cid
    from
        course
    where
        teacher_id=
        (select
            teacher_id
        from
            course
        group by
            teacher_id
        order by
            count(cname) desc
        limit 1))
group by
	course_id)
as t2
on
t1.course_id=t2.course_id
and t1.num=t2.max_num)
;

权限管理

1.创建账号
    本地账号
    create user 'egon1'@'localhost' identified by '123';

    远程账号
    create user 'egon1'@'192.168.31.10' identified by '123';
    create user 'egon1'@'192.168.31.%' identified by '123';
    
	create user 'egon1'@'%' identified by '123';

2.授权
	user:*.*
	db:db1.*
	tables_priv:db1.t1
	columns_priv:id,name

用pycharm远程连接mysql

IPv4 地址: 10.224.201.31
IPv4 子网掩码: 255.255.0.0

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ukJ1lAZi-1619403899274)(MySQL数据库（二）.assets/image-20210409164230698.png)]

注入问题

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CEp7jDPt-1619403899275)(MySQL数据库（二）.assets/image-20210409210450831.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-F9LCycz3-1619403899276)(MySQL数据库（二）.assets/image-20210409210838194.png)]

# .execute已经封装了过滤非法字符功能。

cursor.execute(sql,(user,pwd))

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-V8rJXIJu-1619403899277)(MySQL数据库（二）.assets/image-20210409211130655.png)]

pymysql模块对mysql的增删改查

# 增删改
# 单次传入单条数据
rowss = cursor.execute(sql,('xx','123'))

# 传入多条数据
rows = cursor.executemany(sql,[('wdd','123'),('asd','1234'),('asdfgg','456')])

# 查询
rows= cursor.execute('select * from userinfo;')

# 获取一条数据
print(cursor.fetchoice())
# 获取多条数据
print(cursor.fetchmany(num))

mysql的内置功能

1. 视图

01视图的定义

将通过 S Q L 语句 生成的虚拟表保存起来。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zWuVLrIK-1619403899277)(MySQL数据库（二）.assets/image-20210410133706187.png)]

02视图最好只用于查功能

当视图只针对单张表的时候，可以对视图进行修改。

但是。当视图关联到多张表的时候不要修改，因为所牵扯的数据太多了。

03视图不建议创建太多

当大项目时，我们的视图如果创建的太多的话。扩展的时候就十分的难受，需要一

个个找到才能修改、扩展功能。

04修改视图

05删除视图

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XszqQgzz-1619403899278)(MySQL数据库（二）.assets/image-20210410134533275.png)]

2. 触发器

使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询

# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

3.存储过程

存储过程是mysql一系列功能的封装成的接口，

用于提供给应用程序直接的增删改查功能。

1.无参储存过程

01.创建

delimiter //
create procedure p1()
begin
	select * from db7.teacher;
end//
delimiter ;

02.调用

Mysql中

call p1();

python中

cursor.callproc('p1')
print(cursor.fetchall())

2.有参存储

还有一种可进可出的参数 inout

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jRyZ9ir0-1619403899279)(MySQL数据库（二）.assets/image-20210410212657629.png)]

4.应用程序与数据库结合使用

方式一的分类管理虽然是十分方便，

但是部门间的协作也要求交高，

由于人为因素的限制，我们**用方式二三的时候比较多**。

5.事务

定义储存过程之后定义事务以用于出错时回滚。

提交之前出现异常才能回滚

6. 函数

函数是用于 SQL 语句中，注意和储存过程区分开来

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7Y8BaRMo-1619403899279)(MySQL数据库（二）.assets/image-20210411211745663.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LjSnaosF-1619403899280)(MySQL数据库（二）.assets/image-20210411211941130.png)]

7.索引

mysql 是以键来做索引

当你需要条件查询时，用索引查询方便。

做索引
create index idx_id on s1(id)e;

在这里插入图片描述

![在这里插入图片描述![](https://img-blog.csdnimg.cn/20210426102649880.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl81NDg3NjA5Mw==,size_16,color_FFFFFF,t_70)![](https://img-blog.csdnimg.cn/20210426102652291.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl81NDg3NjA5Mw==,size_16,color_FFFFFF,t_70)![](https://img-blog.csdnimg.cn/20210426102654835.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl81NDg3NjA5Mw==,size_16,color_FFFFFF,t_70)