Mysql经典五十题

已于 2024-03-28 14:22:40 修改
阅读量842 收藏 32
点赞数 27
文章标签： mysql 数据库 java
于 2024-02-21 17:04:59 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ZHaNHFSISOK/article/details/136216187
版权

表的创建

/*
 Navicat Premium Data Transfer

 Source Server         : 本地数据库
 Source Server Type    : MySQL
 Source Server Version : 80031
 Source Host           : localhost:3306
 Source Schema         : mysql50

 Target Server Type    : MySQL
 Target Server Version : 80031
 File Encoding         : 65001

 Date: 21/02/2024 17:04:07
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `CId` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `Cname` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  `TId` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('01', '语文', '02');
INSERT INTO `course` VALUES ('02', '数学', '01');
INSERT INTO `course` VALUES ('03', '英语', '03');

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc`  (
  `SId` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `CId` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `score` decimal(18, 1) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('01', '01', 80.0);
INSERT INTO `sc` VALUES ('01', '02', 90.0);
INSERT INTO `sc` VALUES ('01', '03', 99.0);
INSERT INTO `sc` VALUES ('02', '01', 70.0);
INSERT INTO `sc` VALUES ('02', '02', 60.0);
INSERT INTO `sc` VALUES ('02', '03', 80.0);
INSERT INTO `sc` VALUES ('03', '01', 80.0);
INSERT INTO `sc` VALUES ('03', '02', 80.0);
INSERT INTO `sc` VALUES ('03', '03', 80.0);
INSERT INTO `sc` VALUES ('04', '01', 50.0);
INSERT INTO `sc` VALUES ('04', '02', 30.0);
INSERT INTO `sc` VALUES ('04', '03', 20.0);
INSERT INTO `sc` VALUES ('05', '01', 76.0);
INSERT INTO `sc` VALUES ('05', '02', 87.0);
INSERT INTO `sc` VALUES ('06', '01', 31.0);
INSERT INTO `sc` VALUES ('06', '03', 34.0);
INSERT INTO `sc` VALUES ('07', '02', 89.0);
INSERT INTO `sc` VALUES ('07', '03', 98.0);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `SId` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `Sname` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `Sage` datetime NULL DEFAULT NULL,
  `Ssex` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('01', '赵雷', '1990-01-01 00:00:00', '男');
INSERT INTO `student` VALUES ('02', '钱电', '1990-12-21 00:00:00', '男');
INSERT INTO `student` VALUES ('03', '孙风', '1990-12-20 00:00:00', '男');
INSERT INTO `student` VALUES ('04', '李云', '1990-12-06 00:00:00', '男');
INSERT INTO `student` VALUES ('05', '周梅', '1991-12-01 00:00:00', '女');
INSERT INTO `student` VALUES ('06', '吴兰', '1992-01-01 00:00:00', '女');
INSERT INTO `student` VALUES ('07', '郑竹', '1989-01-01 00:00:00', '女');
INSERT INTO `student` VALUES ('09', '张三', '2017-12-20 00:00:00', '女');
INSERT INTO `student` VALUES ('10', '李四', '2017-12-25 00:00:00', '女');
INSERT INTO `student` VALUES ('11', '李四', '2012-06-06 00:00:00', '女');
INSERT INTO `student` VALUES ('12', '赵六', '2013-06-13 00:00:00', '女');
INSERT INTO `student` VALUES ('13', '孙七', '2014-06-01 00:00:00', '女');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `TId` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `Tname` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('01', '张三');
INSERT INTO `teacher` VALUES ('02', '李四');
INSERT INTO `teacher` VALUES ('03', '王五');

SET FOREIGN_KEY_CHECKS = 1;

MYSQL经典五十题：

1.查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数

SELECT
	a.*,
	b.score AS score1,
	c.score AS score2 
FROM
	student AS a
	LEFT JOIN sc AS b ON a.SId = b.SId 
	AND b.CId = '01'
	LEFT JOIN sc AS c ON a.SId = c.SId 
	AND c.CId = '02' 
WHERE
	b.score > c.score

2.查询同时存在" 01 “课程和” 02 "课程的情况

SELECT a.*
FROM student AS a
WHERE a.SId IN (
    SELECT SId FROM sc WHERE CId = '01'
) AND a.SId IN (
    SELECT SId FROM sc WHERE CId = '02'
);

3.查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )

SELECT
	a.*,
	b.score AS score1,
	c.score AS score2 
FROM
	student AS a
	LEFT JOIN sc AS b ON a.SId = b.SId 
	AND b.CId = '01'
	LEFT JOIN sc AS c ON a.SId = c.SId 
	AND c.CId = '02' 
	WHERE b.score IS NOT NULL

4.查询不存在" 01 “课程但存在” 02 "课程的情况

SELECT
	a.*,
	b.score AS score1,
	c.score AS score2 
FROM
	student AS a
	LEFT JOIN sc AS b ON a.SId = b.SId 
	AND b.CId = '01'
	LEFT JOIN sc AS c ON a.SId = c.SId 
	AND c.CId = '02' 
	WHERE b.score is  NULL
	and c.score is NOT NULL

5.查询平均成绩⼤于等于 60 分的同学的学生编号和学生姓名和平均成绩

ROUND函数可以放sum avg HAVING放在分组之后

SELECT b.SId, b.Sname, ROUND(AVG(a.score), 2) AS avg_score
FROM sc AS a
LEFT JOIN student AS b ON a.SId = b.SId
GROUP BY b.SId, b.Sname
HAVING avg_score > 60;

6.查询在 SC 表存在成绩的学生信息

SELECT * FROM student as a
WHERE a.SId in (SELECT DISTINCT  SId FROM sc WHERE score is not NULL)

7.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩，没成绩的显示为 null

SELECT a.SId,a.Sname, COUNT(b.CId), ROUND(SUM(b.score), 2)
FROM student AS a
LEFT JOIN sc AS b ON a.SId = b.SId 
GROUP BY a.SId,a.Sname

8.查询「李」姓老师的数量

SELECT COUNT(1) FROM teacher
WHERE Tname LIKE '李%'

9.查询学过「张三」老师授课的同学的信息

SELECT * FROM student
WHERE SId IN(SELECT c.SId FROM teacher as a 
LEFT JOIN course as b on a.TId = b.TId
left  JOIN sc as c ON b.CId = c.CId 
WHERE
a.Tname = '张三')

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

SELECT a.* 
FROM student AS a
LEFT JOIN (
    SELECT SId, COUNT(DISTINCT CId) AS total_courses
    FROM sc
    GROUP BY SId
) AS b ON a.SId = b.SId
LEFT JOIN (
    SELECT COUNT(DISTINCT CId) AS total_courses_all
    FROM course
) AS c ON 1=1
WHERE b.total_courses < c.total_courses_all OR b.total_courses IS NULL;

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

SELECT DISTINCT a.SId,COUNT(a.CId) FROM sc as a 
left JOIN student as b ON a.SId = b.SId
WHERE CId IN (SELECT CId FROM sc as a 
WHERE a.SId = '01'
)  and b.SId != '01'
GROUP BY b.SId

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

SELECT DISTINCT b.* 
FROM sc AS a 
LEFT JOIN student AS b ON a.SId = b.SId 
WHERE b.SId != '01' 
AND NOT EXISTS (
    SELECT CId 
    FROM sc 
    WHERE SId = '01'
    EXCEPT
    SELECT CId 
    FROM sc 
    WHERE SId = b.SId
)
AND NOT EXISTS (
    SELECT CId 
    FROM sc 
    WHERE SId = b.SId
    EXCEPT
    SELECT CId 
    FROM sc 
    WHERE SId = '01'
);

13.查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT DISTINCT s.Sname
FROM student AS s
WHERE s.SId NOT IN (
    SELECT c.SId
    FROM teacher AS t
    LEFT JOIN course AS b ON t.TId = b.TId
    LEFT JOIN sc AS c ON b.CId = c.CId
    WHERE t.Tname = '张三'
);

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

SELECT b.SId, b.Sname, 
ROUND(AVG(a.Score), 2) AS average_score, 
SUM(CASE WHEN a.Score < 60 THEN 1 ELSE 0 END) AS num_failures
FROM sc AS a
LEFT JOIN student AS b ON a.SId = b.SId
GROUP BY b.SId, b.Sname
HAVING num_failures >= 2;
​

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

SELECT b.*,a.score FROM sc as a
LEFT JOIN student as b on a.SId = b.SId
WHERE a.CId = '01'
ORDER BY a.score DESC

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

SELECT c.SId, 
c.avg,
d.score as 语文,
e.score as 数学,
f.score as 英语
FROM  
(
    SELECT ROUND(AVG(a.Score), 2) AS avg, a.SId
    FROM sc AS a
    GROUP BY a.SId
) AS c 
LEFT JOIN (SELECT score,SId FROM sc WHERE CId = '01') as d ON c.SId = d.SId
LEFT JOIN (SELECT score,SId FROM sc WHERE CId = '02') as e ON c.SId = e.SId
LEFT JOIN (SELECT score,SId FROM sc WHERE CId = '03') as f ON c.SId = f.SId
ORDER BY c.avg DESC

17.查询各科成绩最⾼分、最低分和平均分： 以如下形式显示：课程 ID，课程 name，最⾼分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT
	a.CId,
	b.Cname,
	MAX( score ) AS 最高分,
	MIN( score ) AS 最低分,
	ROUND(AVG(score),2) AS 平均分,
	ROUND( SUM( CASE WHEN score >= 60 THEN 1 ELSE 0 END )/ COUNT( a.CId ), 2 ) AS 及格率,
	ROUND( SUM( CASE WHEN score >= 70 AND score < 80 THEN 1 ELSE 0 END )/ COUNT( a.CId ), 2 ) AS 中等率,
	ROUND( SUM( CASE WHEN score >= 80 AND score < 90 THEN 1 ELSE 0 END )/ COUNT( a.CId ), 2 ) AS 优良率,
	ROUND( SUM( CASE WHEN score >= 90 THEN 1 ELSE 0 END )/ COUNT( a.CId ), 2 ) AS 优秀 ,
	COUNT(a.CId) AS 选修人数
FROM
	sc as a LEFT JOIN course AS b 
	ON a.CId = b.CId
GROUP BY
	a.CId,b.Cname
	ORDER BY COUNT(a.CId),a.CId

18.按各科平均成绩进行排序，并显示排名

SELECT
	a.CId,
	b.Cname,
	ROUND(AVG(score),2) AS 平均分
FROM
	sc as a LEFT JOIN course AS b 
	ON a.CId = b.CId
GROUP BY
	a.CId,b.Cname
	ORDER BY ROUND(AVG(score),2) DESC

19.按各科平均成绩进行排序，并显示排名，重复时不保留名次空缺

SET @i :=0;-- 定义一个变量
SELECT 
  c.CId,
	c.Cname,
	c.avg AS 平均分,
	@i := @i + 1 AS '排名' 
 FROM
 (
   SELECT
	a.CId,
	b.Cname,
	ROUND(AVG(score),2) AS avg
FROM
	sc as a LEFT JOIN course AS b 
	ON a.CId = b.CId
GROUP BY
	a.CId,b.Cname
	ORDER BY ROUND(AVG(score),2) ASC
) as c

20.查询学生的总成绩，并进行排名，总分重复时保留名次空缺

SELECT 
  c.SId,
	c.Sname,
	c.sum AS 总成绩,
	@i := @i + 1 AS '排名' 
 FROM
 (
   SELECT
	a.SId,
	b.Sname,
	ROUND(SUM(score),2) AS sum
FROM
	sc as a LEFT JOIN student AS b 
	ON a.SId = b.SId
GROUP BY
	a.SId,b.Sname
	ORDER BY sum DESC
) as c

22.统计各科成绩各分数段人数：课程编号，课程名称，[100-85），[85-70），[70-60），[60-0）及所占百分

SELECT
	a.CId,
	b.Cname,
	ROUND( sum( CASE WHEN score >= 85  AND score < 100 THEN 1 ELSE 0 END ), 2 )/COUNT(SId) AS 优秀,
	ROUND( sum( CASE WHEN score >= 70 AND score < 85 THEN 1 ELSE 0 END ), 2 )/COUNT(SId) AS 优良,
	ROUND( sum( CASE WHEN score >= 60 AND score < 70 THEN 1 ELSE 0 END ), 2 ) /COUNT(SId)AS 及格,
	ROUND( sum( CASE WHEN score >= 0  AND score < 60 THEN 1 ELSE 0 END ), 2 ) /COUNT(SId)AS 不及格 
FROM
	sc as a LEFT JOIN course AS b 
	ON a.CId = b.CId
GROUP BY
	a.CId,b.Cname
	ORDER BY COUNT(a.CId),a.CId

23.查询各科成绩前三名的记录

PARTITION BY CId 按照CId分组 ORDER BY score DESC 按照分数排序

SELECT * FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY CId ORDER BY score DESC) AS ranking
    FROM sc
) AS ranked_scores
WHERE ranking <= 3;

24.查询每门课程被选修的学生数

SELECT CId,COUNT(CId) FROM sc 
GROUP BY CId

25.查询出只选修两门课程的学生学号和姓名

SELECT a.SId,a.Sname,b.count FROM student as a
LEFT JOIN (SELECT SId,COUNT(SId) as count FROM sc 
GROUP BY SId) as b ON a.SId = b.SId
WHERE b.count = 2

26.查询男生、女生人数

SELECT Ssex,COUNT(SId) FROM student
GROUP BY Ssex

27.查询名字中含有「风」字的学生信息

SELECT * FROM student
WHERE Sname LIKE '%风%'

28.查询同名同性学生名单，并统计同名同性人数

SELECT Sname,COUNT(Sname) as count FROM student
GROUP BY Sname
HAVING count > 1

29.查询 1990 年出生的学生名单

SELECT * FROM student
WHERE Sage like '1990%'

30.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

SELECT CId,ROUND(AVG(score),2) as avg FROM sc 
GROUP BY CId
ORDER BY avg DESC,CId

31.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

SELECT
	sc.SId,
	ROUND( SUM( sc.score )/ COUNT( sc.score ), 2 ) AS num,
	b.Sname 
FROM
	sc
	LEFT JOIN student AS b ON sc.SId = b.SId 
GROUP BY
	sc.SId , b.Sname
HAVING
	num >= 85

32.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

SELECT c.SId,c.Sname,b.score FROM course as a
LEFT JOIN sc as b ON a.CId = b.CId
LEFT JOIN student as c ON b.SId = c.SId 
WHERE a.Cname = '数学' and b.score < 60

33.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

SELECT c.SId,c.Sname,b.`语文`,b.`英语`,b.`数学` FROM student as c
LEFT JOIN (SELECT a.SId,
SUM(CASE CId WHEN '01' THEN score ELSE 0 END) AS '语文',
SUM(CASE CId WHEN '02' THEN score ELSE 0 END) AS '数学',
SUM(CASE CId WHEN '03' THEN score ELSE 0 END) AS '英语'
FROM student as a
LEFT JOIN sc as b ON a.SId = b.SId
GROUP BY SId) as b ON c.SId = b.SId

34.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

SELECT b.Cname,c.Sname,a.score FROM sc as a
LEFT JOIN course as b ON a.CId = b.CId
LEFT JOIN student as c ON a.SId = c.SId 
WHERE a.score >70

35.查询不及格的课程

SELECT b.Cname,c.Sname,a.score FROM sc as a
LEFT JOIN course as b ON a.CId = b.CId
LEFT JOIN student as c ON a.SId = c.SId 
WHERE a.score < 60

36.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

SELECT
	a.SId,
	a.Sname,
	b.score
FROM
	student AS a
	INNER JOIN ( SELECT * FROM sc WHERE CId = '01' AND score >= '80' ) AS b ON a.SId = b.SId

37.求每门课程的学生人数

SELECT CId,COUNT(CId) as total FROM sc 
GROUP BY CId

38.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT d.*,c.score FROM teacher a
LEFT JOIN course as b ON a.TId = b.TId
LEFT JOIN sc as c ON b.CId = c.CId
LEFT JOIN student AS d ON c.SId = d.SId
WHERE
	a.Tname = '张三'
	ORDER BY c.score DESC
	LIMIT 1

40.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT sc.SId, sc.CId, sc.score
FROM sc
JOIN (
    SELECT SId, score
    FROM sc
    GROUP BY SId, score
    HAVING COUNT(DISTINCT CId) > 1
) AS same_scores ON sc.SId = same_scores.SId AND sc.score = same_scores.score;
​

42.统计每门课程的学生选修人数（超过 5 人的课程才统计）

SELECT CId,COUNT(CId) as total FROM sc 
GROUP BY CId
HAVING total  > 5

43.检索至少选修两门课程的学生学号

SELECT SId,COUNT(SId) as total FROM sc 
GROUP BY SId
HAVING total  > 2

44.查询选修了全部课程的学生信息

SELECT a.* FROM student as a
WHERE SId IN(SELECT SId FROM sc a
GROUP BY SId
HAVING COUNT(SId) = (SELECT COUNT(*) FROM course))

45.查询各学生的年龄，只按年份来算

SELECT Sname,TIMESTAMPDIFF(YEAR, Sage, NOW()) AS 年龄
FROM student;

46.按照出生日期来算，当前月日 < 出生年月的月日，则年龄减一

SELECT Sname, 
CASE 
	WHEN MONTH(Sage) < MONTH(NOW()) THEN
		(TIMESTAMPDIFF(YEAR, Sage, NOW()) - 1)
	ELSE
		TIMESTAMPDIFF(YEAR, Sage, NOW())
END as age
FROM student

47.查询本周过生日的学生

SELECT *
 FROM student 
 WHERE WEEK(Sage) = WEEK(NOW())

48.查询下周过生日的学生

SELECT *
 FROM student 
 WHERE WEEK(Sage) = WEEK(date_add(now(),INTERVAL 1 WEEK));

49.查询本月过生日的学生

SELECT *,MONTH(Sage)
 FROM student 
 WHERE MONTH(Sage) = MONTH(NOW())

50.查询下月过生日的学生

SELECT *,MONTH(Sage)
 FROM student 
 WHERE MONTH(Sage) = MONTH(date_add(now(),INTERVAL 1 MONTH));

爱写代码Z
关注
MySQL 精选问答 500 题
qq_38991247的博客
10-03 1001
1 、我创建了⼀个没有select权限的⽤户， 执⾏select * from T where k=1 ， 报错“select command denied”，并没有报错“unknown column”，是不是可以说明是在打开表之后才判断读取的列不存在？ 答：这个是⼀个安全⽅⾯的考虑。你想想⼀个⽤户如果没有查看这个表的权限，你是会告诉他字段不对还是没权限？如果告诉他字段不对，其实给的信息太多了，因为没权限的意思还包含了： 没权限知道字段是否存在。 2、wait_timeout 是客户端 ⾮交互式的
mysql经典50题，spark版本
weixin_59005638的博客
12-13 1896
object mysql50 { def main(args: Array[String]): Unit = { val spark: SparkSession = SparkSession.builder().appName("ReadJson").master("local[*]").getOrCreate() val url="jdbc:mysql://192.168.192.151:3306/mysql50" val pwd="root" val drive..
500道SQL练习题及答案.md
09-07
高级Mysql强化训练题库
MySQL之经典50道题
qq_40216188的博客
08-09 1万+
MySQL之经典50道题目一、创建数据表并插入数据 一、创建数据表并插入数据 1、学生表 Student(s_id,s_name,s_birth,s_sex) ：学生编号、姓名、年月、性别 -- 1、学生表 -- Student(s_id,s_name,s_birth,s_sex) ：学生编号、姓名、年月、性别 CREATE TABLE IF NOT EXISTS `Student` ( `s_id` VARCHAR (20), `s_name` VARCHAR (20) NOT NULL DEFA
MySQL经典50道练习题及全网最详细解析
热门推荐
hujieliang123的博客
01-16 1万+
MySQL经典50道练习全网最详细解析
sql语句练习50题(Mysql版)
Coder-Wf
09-10 3972
表名和字段 –1.学生表 Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别 –2.课程表 Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号 –3.教师表 Teacher(t_id,t_name) –教师编号,教师姓名 –4.成绩表 Score(s_id,c_id,s_scor...
mysql经典50题_大数据_mysql经典50题_mysql经典五十题_hive_
10-01
下面，我们将深入探讨MySQL的经典问题以及它们与Hive的相关性。 1. **SQL基础** - **数据类型**：MySQL支持多种数据类型，如整型（INT, TINYINT等）、浮点型（FLOAT, DOUBLE）、字符串（VARCHAR, CHAR）和日期时间...
精选常见mysql五十五道面试题目
03-10
MySQL是世界上最受欢迎的关系型数据库管理系统之一，其在各种规模的企业和项目中都有广泛的应用。以下是一些关于MySQL的面试题目及其解析，涵盖了基础概念、数据类型、存储引擎、事务处理等多个方面。 1. 关于ID...
数据库系统原理及MySQL应用教程习题答案.zip
05-01
MySQL则是一种广泛使用的开源关系型数据库管理系统，因其高效、稳定和易用的特点，被众多企业和开发者用于构建应用程序。本压缩包“数据库系统原理及MySQL应用教程习题答案.zip”显然为学习者提供了宝贵的参考资料，...
50道MySQL经典练习题
12-14
这个是网上流传的50道SQL练习题，最近拿来练习，刚做完，这里把我做的答案给大家做个参考，如有错误，还请告知。 另外，做题的时候先不要看答案（答案不唯一，只要满足要求即可），要有自己的一个思考过程，这样做出来才能达到最好的训练效果。（感觉做这种题是会上瘾的……） 我使用的Mysql版本是5.7.19。SQL语句可能会因数据库系统的不同会有少许差异。 这里放上MySQL语法执行的先后顺序： 7:SELECT 查询列表 1:FROM 表 2:连接类型 JOIN 表2 3:ON 连接条件 4:WHERE 筛选条件 5:GROUP BY 分组 6:HAV
面试必备：MySQL经典50题~学会SQL面试不在话下
尤尔小屋
09-02 2394
标题 MySQL经典50题解析及答案 作者 Peter 微信 756803877 公众号 尤而小屋 时间 2021-09-02 MySQL经典50题解析及答案 下面是网传经典的MySQL50题的习题及参考答案????，供参考和学习，有更好的方法或者不恰当的地方，欢迎提出来 题目1 题目要求 查询"01"课程比"02"课程成绩高的学生的信息及课程分数 SQL实现 -- 方法1 select a.* ,b.s_score as 1_score ,c.s_score as 2.
[Mysql] 经典 50 题
licn188的博客
07-22 7258
Mysql 经典 50题
MySQL学习：50道经典题目（含参考答案）(更新中）
weixin_42661635的博客
04-27 9187
首先根据course_id将成绩表分为两张表，筛选出" 1 "课程比" 2 "课程成绩高的学生，再与学生表关联。1. 查询" 1 "课程比" 2 "课程成绩高的学生的信息（学号、姓名、性别、出生日期）及课程分数。利用自连接，统计比当前分数高的分数有几个，排名就是几加1。使用case when语句，多个排序条件时用逗号分隔。利用课程数量，计算出所有课程数和每个学生的选课数量。先筛选与" 1 "号同学选课数量相同的同学，再剔除。10. 查询没有学全所有课程的同学的信息。选了" 1 "号同学没选的课的同学。
MySQL 50 道查询题汇总，足以巩固大部分查询（附带数据准备SQL、题型分析、演示、50道题的完整SQL）
最新发布
Java 领域技术分享
04-06 2744
MySQL 50 道查询题汇总，足以巩固大部分查询（附带数据准备SQL、题型分析、演示、50道题的完整SQL）
mysql8导出5的sql_Mysql 8.0版本导出的sql文件在Mysql 5.5中运行出错
weixin_30675619的博客
02-02 755
出现错误[err] 1064 - you have an error in your sql syntax; check the manual that corresponds to your mysql server version for the right syntax to use near '(0) null default current_timestamp(0) on update ...
MySQL必学！！！（练习题50道精选）
weixin_52263599的博客
01-19 3554
数据表介绍–1.学生表--SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别–2.课程表--CId 课程编号,Cname 课程名称,TId 教师编号–3.教师表--TId 教师编号,Tname 教师姓名–4.成绩表--SId 学生编号,CId 课程编号,score 分数。
MySQL-50道经典sql题（完整版）
溺水小鸡_的博客
06-15 1721
mysql经典50题
mysql经典50题（练习自用版）
liuxiaobai123的博客
03-30 1360
mysql经典50题（练习自用版）
MySQL锁机制与表格类型详解
MySQL数据库支持五种类型的表格： - **MyISAM**：不支持事务，表级锁定，适合大量读取场景，但不适合频繁更新。 - **Heap**：虽然没有在选项中列出，这可能是对非结构化存储的非官方术语，通常不建议在生产环境中...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值