首页 > 数据库 >day24-SQL强化和实践

day24-SQL强化和实践

时间：2024-04-25 15:01:50浏览次数：23  
标签：num day24 sid course score student SQL 强化 id

1. SQL强化

image

  1. 根据上图创建 数据库 & 表结构 并 录入数据（可以自行创造数据）。
    class表：
create table class(
    cid int not null auto_increment primary key,
    caption varchar(16) not null
)default charset=utf8;

student表

create table student(
    sid int not null auto_increment primary key,
    gender char(1) not null,
    class_id int not null,
    sname varchar(16) not null,
    constraint fk_student_class foreign key (class_id) references class(cid)
)default charset=utf8;

teacher表

create table teacher(
    tid int not null auto_increment primary key,
    tname varchar(16) not null
)default charset=utf8;

course表

create table course(
    cid int not null auto_increment primary key,
    cname varchar(16) not null,
    teacher_id int not null,
    constraint fk_course_teacher foreign key (teacher_id) references teacher(tid)
)default charset=utf8;

score表

CREATE TABLE `score` (
  `sid` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `student_id` int NOT NULL,
  `course_id` int NOT NULL,
  `num` int NOT NULL,
  CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
  CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) DEFAULT CHARSET=utf8;

录入数据

INSERT INTO class VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');

INSERT INTO student VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');

INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');

INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');

INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
  1. 创建用户 luffy 并赋予此数据库的所有权限。
create user 'luffy'@'127.0.0.1' identified by '123456';

grant all privileges on test.* to 'luffy'@'127.0.0.1';
flush privileges;
  1. 查询姓“李”的老师的个数。
select count(*) as sum from teacher where tname like "李%";
  1. 查询姓“张”的学生名单。
select * from student where sname like "张%";
  1. 查询男生、女生的人数。
select gender,count(*) as num from student group by gender;
  1. 查询同名同姓学生名单，并统计同名人数。
select sname,count(1) from student group by sname;
  1. 查询 “三年二班” 的所有学生。
select * from student left join class on class.cid=student.class_id where class.caption="三年二班";
  1. 查询 每个 班级的 班级名称、班级人数。
select class.caption, count(*) from student left join class on class.cid=student.class_id group by class.cid;
  1. 查询成绩小于60分的同学的学号、姓名、成绩、课程名称。
select
    student.sid,
    student.sname,
    score.num,
    course.cname
from
    score
    left join student on score.student_id=student.sid
    left join course on score.course_id =course.cid
where num <60;
  1. 查询选修了 “生物课” 的所有学生ID、学生姓名、成绩。
select score.student_id, student.sname,score.num from course left join score on course.cid = score.course_id left join student on score.student_id=student.sid where course.cname="生物";
  1. 查询选修了 “生物课” 且分数低于60的的所有学生ID、学生姓名、成绩。
select score.student_id, student.sname,score.num from course left join score on course.cid = score.course_id left join student on score.student_id=student.sid where course.cname="生物" and score.num<60;
  1. 查询所有同学的学号、姓名、选课数、总成绩。
select student_id,student.sname,count(1),sum(num) from score left join student on score.student_id=student.sid group by student_id;
  1. 查询各科被选修的学生数。
select course_id,course.cname,count(1) from score left join course on score.course_id =course.cid group by course_id;
  1. 查询各科成绩的总分、最高分、最低分，显示：课程ID、课程名称、总分、最高分、最低分。
select course_id,course.cname,sum(num), max(num), min(num) from score left join course on score.course_id =course.cid group by course_id;
  1. 查询各科成绩的平均分，显示：课程ID、课程名称、平均分。
select course_id,course.cname,avg(num) from score left join course on score.course_id =course.cid group by course_id;
  1. 查询各科成绩的平均分，显示：课程ID、课程名称、平均分（按平均分从大到小排序）。
select course_id,course.cname,avg(num) from score left join course on score.course_id =course.cid group by course_id order by avg(num) desc;


select course_id,course.cname,avg(num) as A from score left join course on score.course_id =course.cid group by course_id order by A desc;
  1. 查询各科成绩的平均分和及格率，显示：课程ID、课程名称、平均分、及格率。
select 
    course_id,
    course.cname,
    avg(num),
    sum(case when score.num > 60 then 1 else 0 end)/count(1) *100 as percent 
from 
    score 
    left join course on score.course_id =course.cid 
group by 
    course_id;
  1. 查询平均成绩大于60的所有学生的学号、平均成绩；。
select student_id,avg(num) from score group by student_id having avg(num) > 60;
  1. 查询平均成绩大于85的所有学生的学号、平均成绩、姓名。
select student_id,avg(num),student.sname from score left join student on score.student_id=student.sid   group by student_id having avg(num) > 85;
  1. 查询 “三年二班” 每个学生的 学号、姓名、总成绩、平均成绩。
SELECT
	student_id,
	sname,
	sum( num ),
	avg( num ) 
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid
	LEFT JOIN class ON class.cid = student.class_id 
WHERE
	class.caption = "三年二班" 
GROUP BY
	student_id
  1. 查询各个班级的班级名称、总成绩、平均成绩、及格率（按平均成绩从大到小排序）。
SELECT
	class.cid,
	class.caption,
	sum( num ),
	avg( num ) as av,
	sum( CASE WHEN score.num > 60 THEN 1 ELSE 0 END ) / count( 1 ) * 100 as JG
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid
	LEFT JOIN class ON class.cid = student.class_id 
GROUP BY
	class.cid
ORDER BY
	av desc
  1. 查询学过 “波多” 老师课的同学的学号、姓名。
SELECT
	student.sid,
	student.sname
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid
	LEFT JOIN course ON score.course_id = course.cid
	LEFT JOIN teacher ON course.teacher_id = teacher.tid
WHERE
	teacher.tname = "波多"
  1. 查询没学过 “波多” 老师课的同学的学号、姓名。
select * from student where sid not in(
    SELECT
        student.sid
    FROM
        score
        LEFT JOIN student ON score.student_id = student.sid
        LEFT JOIN course ON score.course_id = course.cid
        LEFT JOIN teacher ON course.teacher_id = teacher.tid
    WHERE
        teacher.tname = "波多" 
)
  1. 查询选修 “苍空” 老师所授课程的学生中，成绩最高的学生姓名及其成绩（不考虑并列）。
SELECT
	student.sid,
	student.sname 
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid
	LEFT JOIN course ON score.course_id = course.cid
	LEFT JOIN teacher ON course.teacher_id = teacher.tid 
WHERE
	teacher.tname = "苍空" 
ORDER BY
	score.num DESC 
	LIMIT 1
  1. 查询选修 “苍空” 老师所授课程的学生中，成绩最高的学生姓名及其成绩（考虑并列）。
SELECT
	student.sid,
	student.sname 
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid
	LEFT JOIN course ON score.course_id = course.cid
	LEFT JOIN teacher ON course.teacher_id = teacher.tid 
WHERE
	teacher.tname = "苍空" 
	AND score.num = (
        SELECT
            max( num ) 
        FROM
            score
            LEFT JOIN course ON score.course_id = course.cid
            LEFT JOIN teacher ON course.teacher_id = teacher.tid 
        WHERE
        teacher.tname = "苍空" 
	)
  1. 查询只选修了一门课程的全部学生的学号、姓名。
SELECT
	student.sid,
	student.sname 
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid 
GROUP BY
	student_id 
HAVING
	count( 1 ) =1
  1. 查询至少选修两门课程的学生学号、学生姓名、选修课程数量。
SELECT
	student.sid,
	student.sname ,
	count(1)
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid 
GROUP BY
	student_id 
HAVING
	count( 1 ) >= 2
  1. 查询两门及以上不及格的同学的学号、学生姓名、选修课程数量。
SELECT
	student.sid,
	student.sname ,
	count(1)
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid 
where 
	num < 60
GROUP BY
	student_id 
HAVING
	count( 1 ) >= 2
  1. 查询选修了所有课程的学生的学号、姓名。
SELECT
	student.sid,
	student.sname
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid 
GROUP BY
	student_id 
HAVING
	count( 1 ) = ( SELECT count( 1 ) FROM course )
  1. 查询未选修所有课程的学生的学号、姓名。
SELECT
	student.sid,
	student.sname
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid 
GROUP BY
	student_id 
HAVING
	count( 1 ) != ( SELECT count( 1 ) FROM course )
  1. 查询所有学生都选修了的课程的课程号、课程名。
SELECT
	course.cid,
	course.cname
FROM
	score
	LEFT JOIN course ON score.course_id = course.cid
GROUP BY
	course_id 
HAVING
	count( 1 ) = ( SELECT count( 1 ) FROM student )
  1. 查询选修 “生物” 和 “物理” 课程的所有学生学号、姓名。
SELECT
	student.sid,
	student.sname
FROM
	score
	LEFT JOIN course ON score.course_id = course.cid
	LEFT JOIN student ON score.student_id = student.sid
WHERE
	course.cname in ("生物","物理")
GROUP BY
	student_id
having 
	count(1) = 2;
  1. 查询至少有一门课与学号为“1”的学生所选的课程相同的其他学生学号 和 姓名 。
SELECT
	student.sid,
	student.sname
FROM
	score
	LEFT JOIN course ON score.course_id = course.cid
	LEFT JOIN student ON score.student_id = student.sid
WHERE
	score.course_id in ( select course_id from score where student_id=1)
	and score.student_id != 1
GROUP BY
	student_id
HAVING
	count(1) > 1
  1. 查询与学号为 “2” 的同学选修的课程完全相同的其他 学生学号 和 姓名 。
SELECT
	student.sid,
	student.sname
FROM
	score
	LEFT JOIN course ON score.course_id = course.cid
	LEFT JOIN student ON score.student_id = student.sid
WHERE
	score.course_id in ( select course_id from score where student_id=2)
	and score.student_id in (
    	select student_id from score where student_id!=2 group by student having count(1) = select count(1) from score where student_id=2
    )
GROUP BY
	student_id
HAVING
	count(1) = ( select count(1) from score where student_id=2 )
	
	
# 如果id=2学生他的课程数量和其他人的课程数量是一样。
select student_id from score where student_id!=2 group by student having count(1) = select count(1) from score where student_id=2

select 
	student_id 
from 
	score 
where 
	student_id!=2 
group by 
	student_id 
having 
	count(1) = select count(1) from score where student_id=2
  1. 查询“生物”课程比“物理”课程成绩高的所有学生的学号；
SELECT
	* 
FROM
	score
	LEFT JOIN course ON score.course_id = course.cid
where 
	cname in ("生物","物理");

SELECT
	*,
	case cname WHEN "生物" then num else -1 end sw,
	case cname WHEN "物理" then num else -1 end wl
FROM
	score
	LEFT JOIN course ON score.course_id = course.cid
where 
	cname in ("生物","物理");

SELECT
	student_id,
	max(case cname WHEN "生物" then num else -1 end) as sw,
	max(case cname WHEN "物理" then num else -1 end) as wl
FROM
	score
	LEFT JOIN course ON score.course_id = course.cid
where 
	cname in ("生物","物理")
GROUP BY
	student_id;

SELECT
	student_id,
	max( CASE cname WHEN "生物" THEN num ELSE 0 END ) AS sw,
	max( CASE cname WHEN "物理" THEN num ELSE 0 END ) AS wl 
FROM
	score
	LEFT JOIN course ON score.course_id = course.cid 
WHERE
	cname IN ( "生物", "物理" ) 
GROUP BY
	student_id 
HAVING
	sw > wl;
  1. 查询每门课程成绩最好的前3名 (不考虑成绩并列情况) 。
SELECT
	cid,
	cname,
	( select student.sname from score left join student on student.sid = score.student_id where course_id = course.cid order by num desc limit 1 offset 0) as "第1名",
	( select student.sname from score left join student on student.sid = score.student_id where course_id = course.cid order by num desc limit 1 offset 1) as "第2名",
	( select student.sname from score left join student on student.sid = score.student_id where course_id = course.cid order by num desc limit 1 offset 2) as "第3名"
FROM
	course;
  1. 查询每门课程成绩最好的前3名 (考虑成绩并列情况) 。
SELECT
	cid,
	cname,
	( select num from score  where course_id = course.cid GROUP BY num order by num desc limit 1 offset 0) as "最高分",
	( select num from score  where course_id = course.cid GROUP BY num order by num desc limit 1 offset 1) as "第二高分",
	( select num from score  where course_id = course.cid GROUP BY num order by num desc limit 1 offset 2) as "第三高分"
FROM
	course;

select 
	* 
from 
	score 
	
	left join (
		SELECT
			cid,
			cname,
			( select num from score  where course_id = course.cid GROUP BY num order by num desc limit 1 offset 0) as "最高分",
			( select num from score  where course_id = course.cid GROUP BY num order by num desc limit 1 offset 1) as "第二高分",
			( select num from score  where course_id = course.cid GROUP BY num order by num desc limit 1 offset 2) as third
		FROM
			course ) as C on score.course_id = C.cid 
WHERE
	score.num >= C.third
  1. 创建一个表 sc，然后将 score 表中所有数据插入到 sc 表中。
CREATE TABLE `sc` (
  `sid` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `student_id` int NOT NULL,
  `course_id` int NOT NULL,
  `num` int NOT NULL,
  CONSTRAINT `fk_sc_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
  CONSTRAINT `fk_sc_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) DEFAULT CHARSET=utf8;

INSERT INTO sc SELECT * from score;

  1. 向 sc 表中插入一些记录，这些记录要求符合以下条件：

    • 学生ID为：没上过课程ID为 “2” 课程的学生的 学号；
    • 课程ID为：2
    • 成绩为：80
    -- 上过
select student_id from score where course_id =2;

-- 没上过
SELECT
	sid
FROM
	student 
WHERE
	sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 )


-- 构造数据
SELECT
	sid,
	2,
	80
FROM
	student 
WHERE
	sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 );

    INSERT INTO sc ( student_id, course_id, num ) SELECT
sid,
2,
80 
FROM
	student 
WHERE
	sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 )

  2. 向 sc 表中插入一些记录，这些记录要求符合以下条件：

    • 学生ID为：没上过课程ID为 “2” 课程的学生的 学号。
    • 课程ID为：2。
    • 成绩为：课程ID为3的最高分。
    SELECT
sid,
2,
(select max(num) from score where course_id=3) as num
FROM
	student 
WHERE
	sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 );

    INSERT INTO sc ( student_id, course_id, num ) SELECT
sid,
2,
(select max(num) from score where course_id=3) as num
FROM
	student 
WHERE
	sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 )

2. 设计表结构

根据如下的业务需求设计相应的表结构，内部需涵盖如下功能。

  • 注册
  • 登录
  • 发布博客
  • 查看博客列表，显示博客标题、创建时间、阅读数量、评论数量、赞数量等。
  • 博客详细，显示博文详细、评论 等。
    • 发表评论
    • 赞 or 踩
    • 阅读数量 + 1

参考如下图片请根据如下功能来设计相应的表结构。

注意：只需要设计表结构，不需要用python代码实现具体的功能（再学一点知识点后再更好的去实现）。

2.1 注册和登录

image

2.2 文章列表

image

2.3 文章详细

image

2.4 评论 & 阅读 & 赞 & 踩

image

注意：假设都是一级评论（不能回复评论）。

image

create table user(
    id int not null auto_increment primary key,
    username varchar(16) not null,
    nickname varchar(16) not null,
    mobile char(11) not null,
    password varchar(64) not null,
    email varchar(64) not null,
    ctime datetime not null
)default charset=utf8;


create table article(
    id int not null auto_increment primary key,
    title varchar(255) not null,
    text text not null,
    read_count int default 0,
    comment_count int default 0,
    up_count int default 0,
    down_count int default 0,
    user_id int not null,
    ctime datetime not null,
    constraint fk_article_user foreign key (user_id) references user(id)
)default charset=utf8;


create table comment(
    id int not null auto_increment primary key,
    content varchar(255) not null,
    user_id int not null,
    article_id int not null,
    ctime datetime not null,
    constraint fk_comment_user foreign key (user_id) references user(id),
    constraint fk_comment_article foreign key (article_id) references article(id)
)default charset=utf8;


create table up_down(
    id int not null auto_increment primary key,
    choice tinyint not null,
    user_id int not null,
    article_id int not null,
    ctime datetime not null,
    constraint fk_up_down_user foreign key (user_id) references user(id),
    constraint fk_up_down_article foreign key (article_id) references article(id)
)default charset=utf8;

标签：num,day24,sid,course,score,student,SQL,强化,id
From： https://www.cnblogs.com/sbhglqy/p/18157718

相关文章

  • SQL Server数据库巡检
    数据库文件信息SELECTa.name[文件名称],a.type_descAS[文件类型],cast(a.[size]*1.0/128asdecimal(12,1))AS[文件设置大小(MB)],CAST(fileproperty(s.name,'SpaceUsed')/(8*16.0)ASDECIMAL(12,1))AS[文件所占空间(MB)],CAST((fileproperty(s.n......
  • postgresql 绿化
    下载官方原版二进制包下载地址解压后删除目录doc、include、pgAdmin4、StackBuilder、symbols,分别对应文档、开发用的头文件、pgadmin管理工具、StackBuild工具、开发用的静态链接库需要安装vc2010_redisinitdb.exe-D..\data-EUTF8pg_ctl-D../datastartcr......
  • MySQL中实现支持Emoji表情存储
    MySQL中实现支持Emoji表情的存储要让MySQL支持Emoji，详细步骤：步骤1：确认MySQL版本确保您使用的MySQL版本至少为5.5.3，因为这是开始支持utf8mb4字符集的起始版本。如果您当前的版本低于此要求，您需要先升级MySQL到一个支持utf8mb4的较新版本。步骤2：检查现有表结构查看您的数据库、......
  • [转帖]MySQLdump之single-transaction详解
     作者：@张扶摇本文为作者原创，转载请注明出处：https://www.cnblogs.com/zhangshengdong/p/9196128.html目录MySQLdump之single-transaction详解single-transaction保存点的日志分析查看当前会话级别导出文件的字符集类型MySQLdump之single-transaction详解single-transact......
  • 解决mysql 事务死锁的方法
    使用以下命令查看引擎的状态SHOWENGINEINNODBSTATUS; 如果有事务死锁可以看到如下图的关键字 找到上图的线程id使用kill57763.解决问题。问题回放，事务死锁如何产生？本地调试，长事务，调试至中途，断开调试，事务未提交。下次进入事务时候同样参数会触发锁。必须kill......
  • day23-必备SQL和表关系及授权
    1.必备SQL语句上一节讲解了最基础SQL语句：增删改查，其实在日常的开发中还有很多必备的SQL语句。这一部分的SQL语句都是围绕着对表中的数据进行操作的。提示：今天的所有操作我都只会在MySQL自带的客户端工具上进行操作。例如：现在创建如下两张表。createdatabaseday26dbdef......
  • 1.MySQL
    1.1索引使用注意事项（1）索引失效情况在索引上做运算、函数、对索引列进行隐式转换（索引列式字符串，但Sql产讯未使用引号）在索引列使用左模糊查询，全模糊查询联合索引未使用最左匹配原则（2）不适用场景表数据量少不适合加索引更新频繁的表不适合加索引区分度低的字段不适合加索......
  • linux centos7 3.10+安装mysql8.0.36
    目录网络源linux操作先搜索有没有安装低版本的mysql5.7或者和mysql冲突的mariadb卸载冲突软件一定要卸载，否则会导致安装mysql失败安装mysql的依赖包，wget，解压源码包时所用的依赖库增加一个mysql的user解压源码包到当前目录给mysql改一个简单的名字编辑/etc下的mysql配置文件my.cnf......
  • 七天.NET 8操作SQLite入门到实战 - （2）第七天Blazor班级管理页面编写和接口对接
    前言上一章节我们引入BootstrapBlazorUI组件完成了EasySQLite后台界面的基本架子的搭建，本章节的主要内容是Blazor班级管理页面编写和接口对接。七天.NET8操作SQLite入门到实战详细教程第一天SQLite简介第二天在Windows上配置SQLite环境第三天SQLite快速入门......
  • SpringBoot项目实现日志打印SQL明细(包括SQL语句和参数)几种方式
    前言我们在开发项目的时候，都会连接数据库。有时候遇到问题需要根据我们编写的SQL进行分析，但如果不进行一些开发或者配置的话，这些SQL是不会打印到控制台的，它们默认是隐藏的。下面给大家介绍几种常用的方法。第一种、代码形式Mybatis框架是Java程序员最常用的数据库映射框架，MyBa......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99