工具的基本用法
可视化工具选择
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;