区块链做网站都有哪些内容呢应用软件定制开发

区块链做网站都有哪些内容呢,应用软件定制开发,网络推广技术外包,狂人站群系统数据表介绍1.学⽣表 Student(SId,Sname,Sage,Ssex)SId 学⽣编号Sname 学⽣姓名Sage 出⽣年⽉Ssex 学⽣性别2.课程表 Course(CId,Cname,TId)CId 课程编号Cname 课程名称TId 教师编号3.教师表 Teacher(TId,Tname)TId 教师编号Tname 教师姓名4.成绩表 SC(SId,CId,score)SId 学⽣编…数据表介绍1.学⽣表 Student(SId,Sname,Sage,Ssex)SId 学⽣编号Sname 学⽣姓名Sage 出⽣年⽉Ssex 学⽣性别2.课程表 Course(CId,Cname,TId)CId 课程编号Cname 课程名称TId 教师编号3.教师表 Teacher(TId,Tname)TId 教师编号Tname 教师姓名4.成绩表 SC(SId,CId,score)SId 学⽣编号CId 课程编号score 分数建表语句学⽣表 Studentcreate table Student( SId varchar(10), Sname varchar(10), Sage datetime, Ssex varchar(10) );课程表 Coursecreate table Course( CId varchar(10), Cname nvarchar(10), TId varchar(10) );教师表 Teachercreate table Teacher( TId varchar(10), Tname varchar(10) );成绩表 SCcreate table SC( SId varchar(10), CId varchar(10), score decimal(18,1) );插入数据学⽣表 Student-- 学生表 Student insert into Student values(01 , 赵雷 , 1990-01-01 , 男); insert into Student values(02 , 钱电 , 1990-12-21 , 男); insert into Student values(03 , 孙⻛ , 1990-12-20 , 男); insert into Student values(04 , 李云 , 1990-12-06 , 男); insert into Student values(05 , 周梅 , 1991-12-01 , ⼥); insert into Student values(06 , 吴兰 , 1992-01-01 , ⼥); insert into Student values(07 , 郑⽵ , 1989-01-01 , ⼥); insert into Student values(09 , 张三 , 2017-12-20 , ⼥); insert into Student values(10 , 李四 , 2017-12-25 , ⼥); insert into Student values(11 , 李四 , 2012-06-06 , ⼥); insert into Student values(12 , 赵六 , 2013-06-13 , ⼥); insert into Student values(13 , 孙七 , 2014-06-01 , ⼥);课程表 Course-- 科⽬表 Course insert into Course values(01 , 语⽂ , 02); insert into Course values(02 , 数学 , 01); insert into Course values(03 , 英语 , 03);教师表 Teacher-- 教师表 Teacher insert into Teacher values(01 , 张三); insert into Teacher values(02 , 李四); insert into Teacher values(03 , 王五);成绩表 SC-- 成绩表 SC insert into SC values(01 , 01 , 80); insert into SC values(01 , 02 , 90); insert into SC values(01 , 03 , 99); insert into SC values(02 , 01 , 70); insert into SC values(02 , 02 , 60); insert into SC values(02 , 03 , 80); insert into SC values(03 , 01 , 80); insert into SC values(03 , 02 , 80); insert into SC values(03 , 03 , 80); insert into SC values(04 , 01 , 50); insert into SC values(04 , 02 , 30); insert into SC values(04 , 03 , 20); insert into SC values(05 , 01 , 76); insert into SC values(05 , 02 , 87); insert into SC values(06 , 01 , 31); insert into SC values(06 , 03 , 34); insert into SC values(07 , 02 , 89); insert into SC values(07 , 03 , 98);1. 查询 01 课程比 02 课程成绩高的学生的信息及课程分数sql-- 步骤先分别查询01和02课程成绩再关联比较 SELECT s.*, sc1.score AS 01课程分数, sc2.score AS 02课程分数 FROM Student s JOIN SC sc1 ON s.SId sc1.SId AND sc1.CId 01 JOIN SC sc2 ON s.SId sc2.SId AND sc2.CId 02 WHERE sc1.score sc2.score;2. 查询同时存在 01 课程和 02 课程的情况sql-- 方法1关联查询 SELECT s.SId, s.Sname, sc1.score AS 01课程, sc2.score AS 02课程 FROM Student s JOIN SC sc1 ON s.SId sc1.SId AND sc1.CId 01 JOIN SC sc2 ON s.SId sc2.SId AND sc2.CId 02; -- 方法2分组筛选 SELECT SId FROM SC WHERE CId IN (01, 02) GROUP BY SId HAVING COUNT(DISTINCT CId) 2;3. 查询存在 01 课程但可能不存在 02 课程的情况不存在时显示为 nullsqlSELECT s.SId, s.Sname, sc1.score AS 01课程, sc2.score AS 02课程 FROM Student s JOIN SC sc1 ON s.SId sc1.SId AND sc1.CId 01 LEFT JOIN SC sc2 ON s.SId sc2.SId AND sc2.CId 02;4. 查询不存在 01 课程但存在 02 课程的情况sqlSELECT s.*, sc2.score AS 02课程分数 FROM Student s JOIN SC sc2 ON s.SId sc2.SId AND sc2.CId 02 WHERE s.SId NOT IN (SELECT SId FROM SC WHERE CId 01);5. 查询平均成绩大于等于 60 分的同学的学生编号、姓名和平均成绩sqlSELECT s.SId, s.Sname, ROUND(AVG(sc.score), 1) AS 平均成绩 FROM Student s JOIN SC sc ON s.SId sc.SId GROUP BY s.SId, s.Sname HAVING AVG(sc.score) 60;6. 查询在 SC 表存在成绩的学生信息sql-- 方法1DISTINCT去重 SELECT DISTINCT s.* FROM Student s JOIN SC sc ON s.SId sc.SId; -- 方法2IN子查询 SELECT * FROM Student WHERE SId IN (SELECT DISTINCT SId FROM SC);7. 查询所有同学的学生编号、姓名、选课总数、所有课程的总成绩没成绩显示为 nullsqlSELECT s.SId, s.Sname, COUNT(sc.CId) AS 选课总数, SUM(sc.score) AS 总成绩 FROM Student s LEFT JOIN SC sc ON s.SId sc.SId GROUP BY s.SId, s.Sname;8. 查询「李」姓老师的数量sqlSELECT COUNT(*) AS 李姓老师数量 FROM Teacher WHERE Tname LIKE 李%;9. 查询学过「张三」老师授课的同学的信息sql-- 步骤先找到张三老师的课程再关联成绩表和学生表 SELECT DISTINCT s.* FROM Student s JOIN SC sc ON s.SId sc.SId JOIN Course c ON sc.CId c.CId JOIN Teacher t ON c.TId t.TId WHERE t.Tname 张三;10. 查询没有学全所有课程的同学的信息sql-- 步骤先统计总课程数再筛选选课数不足的学生 SELECT s.* FROM Student s LEFT JOIN SC sc ON s.SId sc.SId GROUP BY s.SId, s.Sname HAVING COUNT(DISTINCT sc.CId) (SELECT COUNT(*) FROM Course);11. 查询至少有一门课与学号为 01 的同学所学相同的同学的信息sqlSELECT DISTINCT s.* FROM Student s JOIN SC sc ON s.SId sc.SId WHERE sc.CId IN (SELECT CId FROM SC WHERE SId 01) AND s.SId ! 01; -- 排除01号学生自身12. 查询和 01 号同学学习的课程完全相同的其他同学的信息sql-- 步骤1. 获取01号课程列表 2. 筛选选课数相同且课程完全匹配的学生 SELECT s.* FROM Student s JOIN SC sc ON s.SId sc.SId WHERE s.SId ! 01 GROUP BY s.SId, s.Sname HAVING COUNT(DISTINCT sc.CId) (SELECT COUNT(*) FROM SC WHERE SId 01) AND GROUP_CONCAT(sc.CId ORDER BY sc.CId) (SELECT GROUP_CONCAT(CId ORDER BY CId) FROM SC WHERE SId 01);13. 查询没学过 张三 老师讲授的任一课程的学生姓名sql-- 步骤先找到张三老师的课程再筛选未选这些课程的学生 SELECT s.Sname FROM Student s WHERE s.SId NOT IN ( SELECT DISTINCT sc.SId FROM SC sc JOIN Course c ON sc.CId c.CId JOIN Teacher t ON c.TId t.TId WHERE t.Tname 张三 );14. 查询两门及其以上不及格课程的同学的学号、姓名及其平均成绩sqlSELECT s.SId, s.Sname, ROUND(AVG(sc_all.score), 1) AS 平均成绩 FROM Student s JOIN SC sc_fail ON s.SId sc_fail.SId AND sc_fail.score 60 JOIN SC sc_all ON s.SId sc_all.SId GROUP BY s.SId, s.Sname HAVING COUNT(DISTINCT sc_fail.CId) 2;15. 检索 01 课程分数小于 60按分数降序排列的学生信息sqlSELECT s.*, sc.score FROM Student s JOIN SC sc ON s.SId sc.SId WHERE sc.CId 01 AND sc.score 60 ORDER BY sc.score DESC;16. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩sql-- 步骤先计算每个学生的平均成绩再关联所有课程成绩 SELECT s.SId, s.Sname, MAX(CASE WHEN sc.CId 01 THEN sc.score ELSE NULL END) AS 语文, MAX(CASE WHEN sc.CId 02 THEN sc.score ELSE NULL END) AS 数学, MAX(CASE WHEN sc.CId 03 THEN sc.score ELSE NULL END) AS 英语, ROUND(AVG(sc.score), 1) AS 平均成绩 FROM Student s LEFT JOIN SC sc ON s.SId sc.SId GROUP BY s.SId, s.Sname ORDER BY 平均成绩 DESC;17. 查询各科成绩最高分、最低分和平均分含及格率 / 中等率 / 优良率 / 优秀率sqlSELECT c.CId, c.Cname, COUNT(sc.SId) AS 选修人数, MAX(sc.score) AS 最高分, MIN(sc.score) AS 最低分, ROUND(AVG(sc.score), 1) AS 平均分, -- 及格率60 ROUND(SUM(CASE WHEN sc.score 60 THEN 1 ELSE 0 END)/COUNT(sc.SId)*100, 1) AS 及格率, -- 中等率70-80 ROUND(SUM(CASE WHEN sc.score BETWEEN 70 AND 80 THEN 1 ELSE 0 END)/COUNT(sc.SId)*100, 1) AS 中等率, -- 优良率80-90 ROUND(SUM(CASE WHEN sc.score BETWEEN 80 AND 90 THEN 1 ELSE 0 END)/COUNT(sc.SId)*100, 1) AS 优良率, -- 优秀率90 ROUND(SUM(CASE WHEN sc.score 90 THEN 1 ELSE 0 END)/COUNT(sc.SId)*100, 1) AS 优秀率 FROM Course c JOIN SC sc ON c.CId sc.CId GROUP BY c.CId, c.Cname ORDER BY 选修人数 DESC, c.CId ASC;18. 按各科平均成绩排序并显示排名Score 重复时保留名次空缺sql-- 使用RANK()函数保留空缺 SELECT CId, 平均成绩, RANK() OVER (ORDER BY 平均成绩 DESC) AS 排名 FROM ( SELECT CId, ROUND(AVG(score), 1) AS 平均成绩 FROM SC GROUP BY CId ) AS t;19. 按各科平均成绩排序并显示排名Score 重复时不保留名次空缺sql-- 使用DENSE_RANK()函数不保留空缺 SELECT CId, 平均成绩, DENSE_RANK() OVER (ORDER BY 平均成绩 DESC) AS 排名 FROM ( SELECT CId, ROUND(AVG(score), 1) AS 平均成绩 FROM SC GROUP BY CId ) AS t;20. 查询学生的总成绩并排名总分重复保留名次空缺sqlSELECT s.SId, s.Sname, IFNULL(SUM(sc.score), 0) AS 总成绩, RANK() OVER (ORDER BY IFNULL(SUM(sc.score), 0) DESC) AS 排名 FROM Student s LEFT JOIN SC sc ON s.SId sc.SId GROUP BY s.SId, s.Sname;21. 查询学生的总成绩并排名总分重复不保留名次空缺sqlSELECT s.SId, s.Sname, IFNULL(SUM(sc.score), 0) AS 总成绩, DENSE_RANK() OVER (ORDER BY IFNULL(SUM(sc.score), 0) DESC) AS 排名 FROM Student s LEFT JOIN SC sc ON s.SId sc.SId GROUP BY s.SId, s.Sname;22. 统计各科成绩各分数段人数及百分比sqlSELECT c.CId, c.Cname, -- [100-85] SUM(CASE WHEN sc.score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS 100-85人数, ROUND(SUM(CASE WHEN sc.score BETWEEN 85 AND 100 THEN 1 ELSE 0 END)/COUNT(sc.SId)*100, 1) AS 100-85百分比, -- [85-70] SUM(CASE WHEN sc.score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS 85-70人数, ROUND(SUM(CASE WHEN sc.score BETWEEN 70 AND 85 THEN 1 ELSE 0 END)/COUNT(sc.SId)*100, 1) AS 85-70百分比, -- [70-60] SUM(CASE WHEN sc.score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS 70-60人数, ROUND(SUM(CASE WHEN sc.score BETWEEN 60 AND 70 THEN 1 ELSE 0 END)/COUNT(sc.SId)*100, 1) AS 70-60百分比, -- [60-0] SUM(CASE WHEN sc.score 60 THEN 1 ELSE 0 END) AS 60-0人数, ROUND(SUM(CASE WHEN sc.score 60 THEN 1 ELSE 0 END)/COUNT(sc.SId)*100, 1) AS 60-0百分比 FROM Course c JOIN SC sc ON c.CId sc.CId GROUP BY c.CId, c.Cname;23. 查询各科成绩前三名的记录sql-- 方法使用窗口函数ROW_NUMBER() SELECT * FROM ( SELECT sc.SId, sc.CId, sc.score, ROW_NUMBER() OVER (PARTITION BY sc.CId ORDER BY sc.score DESC) AS 排名 FROM SC sc ) AS t WHERE 排名 3;24. 查询每门课程被选修的学生数sqlSELECT c.CId, c.Cname, COUNT(DISTINCT sc.SId) AS 选修人数 FROM Course c LEFT JOIN SC sc ON c.CId sc.CId GROUP BY c.CId, c.Cname;25. 查询出只选修两门课程的学生学号和姓名sqlSELECT s.SId, s.Sname FROM Student s JOIN SC sc ON s.SId sc.SId GROUP BY s.SId, s.Sname HAVING COUNT(DISTINCT sc.CId) 2;26. 查询男生、女生人数sqlSELECT Ssex AS 性别, COUNT(*) AS 人数 FROM Student GROUP BY Ssex;27. 查询名字中含有「风」字的学生信息sqlSELECT * FROM Student WHERE Sname LIKE %风%;28. 查询同名同性学生名单并统计同名人数sqlSELECT Sname, Ssex, COUNT(*) AS 同名人数 FROM Student GROUP BY Sname, Ssex HAVING COUNT(*) 1;29. 查询 1990 年出生的学生名单sql-- 方法1YEAR函数 SELECT * FROM Student WHERE YEAR(Sage) 1990; -- 方法2LIKE匹配 SELECT * FROM Student WHERE Sage LIKE 1990%;30. 查询每门课程的平均成绩降序平均成绩相同按课程号升序sqlSELECT c.CId, c.Cname, ROUND(AVG(sc.score), 1) AS 平均成绩 FROM Course c JOIN SC sc ON c.CId sc.CId GROUP BY c.CId, c.Cname ORDER BY 平均成绩 DESC, c.CId ASC;31. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩sqlSELECT s.SId, s.Sname, ROUND(AVG(sc.score), 1) AS 平均成绩 FROM Student s JOIN SC sc ON s.SId sc.SId GROUP BY s.SId, s.Sname HAVING AVG(sc.score) 85;32. 查询课程名称为「数学」且分数低于 60 的学生姓名和分数sqlSELECT s.Sname, sc.score FROM Student s JOIN SC sc ON s.SId sc.SId JOIN Course c ON sc.CId c.CId WHERE c.Cname 数学 AND sc.score 60;33. 查询所有学生的课程及分数情况含没成绩 / 没选课的情况sql-- 笛卡尔积生成所有学生-课程组合再左连接成绩 SELECT s.SId, s.Sname, c.Cname, sc.score FROM Student s CROSS JOIN Course c LEFT JOIN SC sc ON s.SId sc.SId AND c.CId sc.CId ORDER BY s.SId, c.CId;34. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数sqlSELECT DISTINCT s.Sname, c.Cname, sc.score FROM Student s JOIN SC sc ON s.SId sc.SId AND sc.score 70 JOIN Course c ON sc.CId c.CId;35. 查询不及格的课程sqlSELECT s.SId, s.Sname, c.Cname, sc.score FROM Student s JOIN SC sc ON s.SId sc.SId AND sc.score 60 JOIN Course c ON sc.CId c.CId;36. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名sqlSELECT s.SId, s.Sname FROM Student s JOIN SC sc ON s.SId sc.SId WHERE sc.CId 01 AND sc.score 80;37. 求每门课程的学生人数sql-- 同24题重复题目 SELECT c.CId, c.Cname, COUNT(DISTINCT sc.SId) AS 学生人数 FROM Course c LEFT JOIN SC sc ON c.CId sc.CId GROUP BY c.CId, c.Cname;38. 成绩不重复查询选修「张三」老师所授课程的学生中成绩最高的学生信息及其成绩sql-- 步骤先找到张三老师的课程再筛选最高分无重复 SELECT s.*, sc.score, c.Cname FROM Student s JOIN SC sc ON s.SId sc.SId JOIN Course c ON sc.CId c.CId JOIN Teacher t ON c.TId t.TId WHERE t.Tname 张三 AND sc.score ( SELECT MAX(score) FROM SC sc2 JOIN Course c2 ON sc2.CId c2.CId JOIN Teacher t2 ON c2.TId t2.TId WHERE t2.Tname 张三 );39. 成绩有重复的情况下查询选修「张三」老师所授课程的学生中成绩最高的学生信息及其成绩sql-- 方法使用窗口函数 SELECT * FROM ( SELECT s.*, sc.score, c.Cname, RANK() OVER (ORDER BY sc.score DESC) AS 排名 FROM Student s JOIN SC sc ON s.SId sc.SId JOIN Course c ON sc.CId c.CId JOIN Teacher t ON c.TId t.TId WHERE t.Tname 张三 ) AS t WHERE 排名 1;40. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩sqlSELECT DISTINCT sc1.SId, sc1.CId, sc1.score FROM SC sc1 JOIN SC sc2 ON sc1.SId sc2.SId AND sc1.CId ! sc2.CId AND sc1.score sc2.score ORDER BY sc1.score;41. 查询每门课程成绩最好的前两名sql-- 同23题前三名调整为前两名 SELECT * FROM ( SELECT sc.SId, sc.CId, sc.score, ROW_NUMBER() OVER (PARTITION BY sc.CId ORDER BY sc.score DESC) AS 排名 FROM SC sc ) AS t WHERE 排名 2;42. 统计每门课程的学生选修人数超过 5 人的课程才统计sqlSELECT c.CId, c.Cname, COUNT(DISTINCT sc.SId) AS 选修人数 FROM Course c JOIN SC sc ON c.CId sc.CId GROUP BY c.CId, c.Cname HAVING COUNT(DISTINCT sc.SId) 5;43. 检索至少选修两门课程的学生学号sqlSELECT SId FROM SC GROUP BY SId HAVING COUNT(DISTINCT CId) 2;44. 查询选修了全部课程的学生信息sqlSELECT s.* FROM Student s JOIN SC sc ON s.SId sc.SId GROUP BY s.SId, s.Sname HAVING COUNT(DISTINCT sc.CId) (SELECT COUNT(*) FROM Course);45. 查询各学生的年龄只按年份来算sqlSELECT SId, Sname, YEAR(NOW()) - YEAR(Sage) AS 年龄 FROM Student;46. 按出生日期算年龄当前月日 出生年月的月日则年龄减一sqlSELECT SId, Sname, YEAR(NOW()) - YEAR(Sage) - CASE WHEN DATE_FORMAT(NOW(), %m%d) DATE_FORMAT(Sage, %m%d) THEN 1 ELSE 0 END AS 实际年龄 FROM Student;47. 查询本周过生日的学生sql-- MySQL中WEEKOFYEAR函数本周1-53 SELECT * FROM Student WHERE WEEKOFYEAR(Sage) WEEKOFYEAR(NOW());48. 查询下周过生日的学生sqlSELECT * FROM Student WHERE WEEKOFYEAR(Sage) WEEKOFYEAR(NOW()) 1;49. 查询本月过生日的学生sqlSELECT * FROM Student WHERE MONTH(Sage) MONTH(NOW());50. 查询下月过生日的学生sqlSELECT * FROM Student WHERE MONTH(Sage) MONTH(NOW()) 1;补充说明所有 SQL 基于 MySQL 8.0 语法支持窗口函数RANK ()/DENSE_RANK ()/ROW_NUMBER ()涉及日期计算的函数YEAR/MONTH/WEEKOFYEAR为 MySQL 特有其他数据库如 Oracle/SQL Server需调整百分比计算中使用ROUND(...,1)保留 1 位小数可根据需求调整左连接LEFT JOIN确保无成绩 / 无选课的学生也能显示符合题目要求。