sql数据库

起个名难死

已于 2023-10-13 14:58:42 修改

阅读量89

点赞数

文章标签：数据库学习

于 2023-09-26 13:49:06 首次发布

本文链接：https://blog.csdn.net/weixin_71104826/article/details/133303436

版权

工具的基本用法

可视化工具选择

navicat

点击查询然后新建查询然后就可以写sql了

点击连接本地mysql 输入用户名称和密码选中数据库

然后点击查询开始写sql

创表

student表

create table student(s_id varchar(10),s_name varchar(10),s_age date,s_sex varchar(10)) engine=InnoDB default charset utf8;
insert into student(s_id,s_name,s_age,s_sex)
values('01' , '赵雷', '1990-01-01' , '男'),
('02' , '钱电' , '1990-12-21' , '男')
,('03' , '孙风' , '1990-05-20' , '男')
,('04' , '李云' , '1990-08-06' , '男')
,('05' , '周梅' , '1991-12-01' , '女')
,('06' , '吴兰' , '1992-03-01' , '女')
,('07' , '郑竹' , '1989-07-01' , '女')
,('08' , '王菊' , '1990-01-20' , '女');
select * from student;

course表

create table course(c_id varchar(10),c_name varchar(10),t_id varchar(10)) engine=InnoDB default charset utf8;
insert into course values('01' , '语文' , '02'),
('02' , '数学' , '01'),
('03' , '英语' , '03');
select * from course;

teacher表

create table teacher(t_id varchar(10),t_name varchar(10)) engine=InnoDB default charset utf8;
insert into teacher values('01' , '张三'),('02' , '李四'),('03' , '王五');
select * from teacher;

stu_sco表

create table stu_sco(s_id varchar(10),c_id varchar(10),score decimal(18,1)) engine=InnoDB default charset utf8;
insert into stu_sco values ('01' , '01' , 80), ('01' , '02' , 90), ('01' , '03' , 99), ('02' , '01' , 70), ('02' , '02' , 60), ('02' , '03' , 80), ('03' , '01' , 80), ('03' , '02' , 80), ('03' , '03' , 80), ('04' , '01' , 50), ('04' , '02' , 30), ('04' , '03' , 20), ('05' , '01' , 76), ('05' , '02' , 87), ('06' , '01' , 31), ('06' , '03' , 34), ('07' , '02' , 89), ('07' , '03' , 98);
select * from stu_sco;

primary key

其中最好设置一个primary key来关联一下太多的数据看不过来

暂时没发现有什么妙用

主关键字(primary key)是表中的一个或多个字段，它的值用于唯一的标识表中的某一条记录。在两个表的关系中，主关键字用来在一个表中引用来自于另一个表中的特定记录。主关键字是一种唯一关键字，表定义的一部分。一个表的主键可以由多个关键字共同组成，并且主关键字的列不能包含空值。

删除表

好了创的不好可以删除了

DROP TABLE student; 删除student表

SELECT *
FROM stu_sco
JOIN student ON stu_sco.s_id = student.s_id
JOIN course ON stu_sco.c_id=course.c_id
JOIN teacher ON course.t_id=teacher.t_id

将表和表连接在一起

sql语句的使用

1.查询01分数小于02 分数的学生的信息以及分数的sql语句

思路是先把每个学生的信息接上01课程的分数

然后接上02的分数

再进行where筛选

最后进行as重命名表头和需要显示的数据

SELECT a.*,b.score as s1,c.score as s2 FROM student a
JOIN stu_sco b ON a.s_id=b.s_id AND b.c_id=01
JOIN stu_sco c ON a.s_id=c.s_id AND c.c_id=02 WHERE b.score<c.score

2.查询平均成绩大于等于60的学生的sql语句

planA

思路是先拼两个表然后用s_id和s_name两个字段将其分组

将每组中的三score avg求平均

最后having过滤出满足条件的组

在进行显示数据和重命名

SELECT
student.s_id 学生编号,
student.s_name 学生姓名,
avg(score) 平均成绩
FROM
student
JOIN stu_sco ON student.s_id = stu_sco.s_id
GROUP BY
student.s_id,
student.s_name
HAVING
平均成绩>=60

planB

思路是先把stu_sco表中的数据根据s_id分组显示s_id和每组中三个score的平均值生成新表

然后把student表和该表根据s_id拼接

拼接之后过滤一下>=60的

最后进行显示数据和重命名

SELECT
student.s_id,
student.s_name,
avg_score
FROM
student
JOIN (
SELECT
s_id,
avg(score) avg_score
FROM
stu_sco
GROUP BY
s_id
) b ON student.s_id = b.s_id
AND b.avg_score >= 60;

3.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

planA

思路是先把stu_sco表中的数据根据s_id分组显示s_id和每组中三个score的平均值生成新表

然后把student表和该表根据s_id拼接

拼接之后过滤一下>=60的

最后进行显示数据和重命名

SELECT
student.s_id,
student.s_name,
avg_score
FROM
student
INNER JOIN (
SELECT
s_id,
avg(score) avg_score
FROM
stu_sco
GROUP BY
s_id
) b ON student.s_id = b.s_id
AND b.avg_score <60;

planB

思路是先通过s_id拼接表然后通过s_id和s_name进行分组

利用avg函数对score字段求平均值

SELECT
student.s_id 学生编号,
student.s_name 学生姓名,
avg(score) 平均成绩
FROM
student
JOIN stu_sco ON student.s_id = stu_sco.s_id
GROUP BY
student.s_id,
student.s_name
HAVING
平均成绩 <60;

4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

思路是先连接左右表然后groupby 通过s_id s_name进行分组

再通过count 对字段的数量求值 sum对该字段总共的分数求值

最后字段的显示和重命名

SELECT
student.s_id 学生编号,
s_name 学生姓名,
count(c_id) 选课总数,
sum(score) 总成绩
FROM
student
JOIN stu_sco ON student.s_id = stu_sco.s_id
GROUP BY student.s_id,s_name

5.查询李姓老师的数量

思路是先在teacher表中筛选用like筛选出名字中第一个带李的老师的数据

然后用count对t_name中的数据进行数量的计算

SELECT
count(t_name) 李姓老师的数量
FROM
teacher
WHERE t_name LIKE '李%';

6.查询学过张三老师授课的同学的信息

思路是先在teacher表中寻找t_name字段等于张三的行的t_id字段

在course表中找t_id等于上面数据的行将这些行的c_id显示出来

然后再stu_sco表中找到c_id等于上张三教的课程的学生的s_id

在student表中筛选出s_id等于上句获取到的数据的学生的信息

SELECT *
FROM
student
WHERE
s_id IN (
SELECT
s_id
FROM
stu_sco
WHERE
c_id IN (
SELECT
c_id
FROM
course
WHERE
c_id IN (
SELECT
t_id
FROM
teacher
WHERE
t_name = '张三' ) ) );

7.查询没学过张三老师授课的同学的信息

在teacher表中找到张三对应的t_id

根据t_id在course表中找到t_id对应的c_id

根据c_id在stu_sco表中找到没有c_id授课的学生的s_id

根据s_id在student表中找到对应的学生全部信息

SELECT *
FROM
student
WHERE
s_id NOT IN (
SELECT
s_id
FROM
stu_sco
WHERE
c_id IN (
SELECT
c_id
FROM
course
WHERE
t_id IN (
SELECT
t_id
FROM
teacher
WHERE
t_name = '张三'
)
)
);

8.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

在stu_sco表中筛选出c_id=01的再筛选出c_id=02的

将两个表根据s_id拼接在一起

最后在student表中筛选出s_id等于上句得到的s_id的学生的数据

SELECT
*
FROM
student
WHERE
s_id IN (
SELECT
a.s_id
FROM
(
SELECT
*
FROM
stu_sco
WHERE
c_id = '01'
) a
JOIN (
SELECT
*
FROM
stu_sco
WHERE
c_id = '02'
) b ON a.s_id = b.s_id
);

9.查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

在stu_sco表中筛选出c_id=02的学生的s_id

笛卡尔积出student和stu_sco表中所有的数据

然后筛选出其中 s_id相同的 stu_sco表c_id等于01的 s_id不等于学过c_id=02课程的学生s_id的

SELECT
student.*
FROM
student,
stu_sco sc1
WHERE
student.s_id = sc1.s_id
AND sc1.c_id = '01'
AND student.s_id NOT IN (
SELECT
s_id
FROM
stu_sco sc2
WHERE
sc2.c_id = '02'
);

10.查询没有学全所有课程的学生信息

将student表和stu_sco表拼接起来对s_id进行分组

然后用count求c_id的总数再用having

过滤掉学的课程总数小于总的课程总数的学生

SELECT
student.s_id,
count(stu_sco.c_id)
FROM
student
JOIN stu_sco ON student.s_id = stu_sco.s_id
GROUP BY
student.s_id
HAVING
count(stu_sco.c_id) < (
SELECT
count(DISTINCT c_id)
FROM
course
);

11.查询至少有一门课与学号为01的同学所学相同的同学

在stu_sco表中找s_id=01的同学的c_id

再筛选出c_id等于上句c_id的s_id 并且对s_id进行去重

这些s_id都是与学号为01有相同课的同学的s_id

and上一个s_id不等于01的条件排除自己的s_id

在student表中选出s_id等于上句s_id相同的同学的信息

SELECT
student.*
FROM
student
WHERE
s_id IN (
SELECT DISTINCT
(s_id)
FROM
stu_sco
WHERE
c_id IN (
SELECT
c_id
FROM
stu_sco
WHERE
s_id = '01'
)
AND s_id <> '01'
);

12.查询和01号同学学习的课程完全相同的其他同学的信息

将stu_sco表按照s_id分组

然后过滤出count（c_id）学满了的同学的s_id

然后再student表中过滤出该s_id的同学的全部信息

SELECT
student.*
FROM
student
WHERE
s_id IN (
SELECT DISTINCT
(s_id)
FROM
stu_sco
WHERE
c_id IN (
SELECT
c_id
FROM
stu_sco
WHERE
s_id = '01'
)
AND s_id <> '01'
);

13.查询没学过张三讲授任意一门课的学生姓名

查出张三所讲课程的c_id

查出选了这门课的学生的s_id

然后查出s_id不等于这些s_id的学生的个人信息

SELECT
*
FROM
student
WHERE
s_id NOT IN (
SELECT
s_id
FROM
stu_sco
WHERE
c_id IN (
SELECT
c_id
FROM
course
LEFT JOIN teacher ON course.t_id = teacher.t_id
WHERE
t_name = '张三'
)
);

14.查询两门及其以上不及格课程的同学的学号姓名和平均成绩

在stu_sco表中查询不及格的表

然后根据s_id分组查看不及格分数的数量

筛选出数量大于1的同学的s_id

最后将这个表和

SELECT
   s_id,
   s_name,
   AVG( score )
FROM
   (
   SELECT
       student.s_id,
       s_name,
       score
   FROM
       stu_sco
       JOIN student ON stu_sco.s_id = student.s_id
   HAVING
       student.s_id IN (
       SELECT
           stu_sco.s_id
       FROM
           stu_sco
           JOIN student ON student.s_id = stu_sco.s_id
       WHERE
           score < 60
       GROUP BY
           stu_sco.s_id
       HAVING
           COUNT( score )> 1
       )
   ) a
GROUP BY
   s_id,
   s_name

15.检索01课程分数小于60 按分数降序排列的学生信息

先在stu_sco中筛选出01课程并且分数小于60的学生的s_id然后降序

再将这个表和student根据s_id拼接起来

SELECT
*
FROM
( SELECT s_id
FROM stu_sco
WHERE c_id = 01 AND score < 60
ORDER BY score DESC
) AS a
JOIN student ON a.s_id = student.s_id;

16.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

在stu_sco表中按照s_id分组并且求出score的平均

将stu_sco和上表根据s_id拼接然后根据平均成绩降序排列

SELECT
stu_sco.*,
平均成绩
FROM
stu_sco
JOIN (
SELECT
s_id,
AVG(score) 平均成绩
FROM
stu_sco
GROUP BY
s_id
) a ON stu_sco.s_id = a.s_id
ORDER BY
平均成绩 DESC

17.查询各科成绩最高分最低分和平均分以如下形式显示课程id 课程name 最高分最低分平均分及格率中等率优良率优秀率

利用case语句将分数大于等于60的为1否则为0 并将其命名为是否及格

依次将优良和优秀命名出来 然后将这个表格拼接上course表进行分组显示

SELECT
   a.`c_id` AS `课程ID`,
   course.`c_name` AS `课程名`,
   MAX( a.`score` ) AS `最高分`,
   MIN( a.`score` ) AS `最低分`,
   AVG( a.`score` ) AS `平均分`,
   AVG( a.`pass` ) AS `及格率`,
   AVG( a.`middle` ) AS `中等率`,
   AVG( a.`good` ) AS `优良率`,
   AVG( a.`excellent` ) AS `优秀率`
FROM
   (
   SELECT
       stu_sco.*,
   CASE
           WHEN score >= 60 THEN
           1 ELSE 0
       END AS `pass`,
   CASE
           WHEN score >= 70
           AND score < 79 THEN 1 ELSE 0 END AS `middle`, CASE WHEN score >= 80
               AND score < 89 THEN 1 ELSE 0 END AS `good`, CASE WHEN score >= 90 THEN
                   1 ELSE 0
               END AS `excellent`
           FROM
               stu_sco
           ) a
           JOIN course ON a.`c_id` = course.`c_id`
       GROUP BY
       a.`c_id`,
   course.`c_name`;

18.查询学生的总成绩并进行排名

在stu_sco表中按照s_id分组并且求出score的平均

将stu_sco和上表根据s_id拼接然后根据平均成绩降序排列

SELECT
a.*,@rank :=@rank + 1 AS rank
FROM
(
SELECT
s_id,
sum(score) 总成绩
FROM
stu_sco
GROUP BY
s_id
ORDER BY
总成绩 DESC
) a,
(SELECT @rank := 0 AS rank) b;

19.按课程分类，用成绩对学生进行排名，排名升序

在stu_sco表中按照s_id分组并且求出score的平均

将stu_sco和上表根据s_id拼接然后根据平均成绩降序排列SELECT

select s_id, c_id ,score from stu_sco ORDER BY c_id ASC,score desc;

select d.s_name,d.score,if(@c_name=d.c_name,@rank:=@rank+1,@rank:=1) as rank, @c_name:=d.c_name as c_name
from
(select a.s_name,c.c_name,b.score
from student a left join stu_sco b on a.s_id=b.s_id
left join course c on c.c_id=b.c_id
order by c.c_name desc,b.score desc)d,
(select @c_name:=0,@rank:=0)e;

20.查询不同老师所教不同课程平均分从高到低显示

在stu_sco表中按照s_id分组并且求出score的平均

将stu_sco和上表根据s_id拼接然后根据平均成绩降序排列SELECT SELECT
t_name 老师,
c_name 课程,
avg(score) 平均分
FROM
teacher
LEFT JOIN course ON course.t_id = teacher.t_id
JOIN stu_sco ON course.c_id = stu_sco.c_id
GROUP BY
teacher.t_id,
stu_sco.c_id
ORDER BY
平均分 DESC;