SQL经典1-45题-zstarling

最新推荐文章于 2022-07-12 14:33:00 发布
最新推荐文章于 2022-07-12 14:33:00 发布
阅读量285 收藏 2
点赞数
分类专栏： SQL 文章标签： mysql
zstarling
本文链接：https://blog.csdn.net/weixin_44964850/article/details/117531256
版权

表及表记录点此处

  • 1.查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
SELECT
	* 
FROM
	( SELECT * FROM score sc WHERE sc.Cid = 1 ) AS cid1,
	( SELECT * FROM score sc WHERE sc.Cid = 2 ) AS cid2 
WHERE
	cid1.score > cid2.score 
	AND cid1.sid = cid2.sid;
  • 1.1查询同时存在" 01 “课程和” 02 "课程的情况
SELECT
	* 
FROM
	( SELECT * FROM score WHERE score.Cid = 1 ) AS cid1,
	( SELECT * FROM score WHERE score.Cid = 2 ) AS cid2 
WHERE
	cid1.sid = cid2.sid;
  • 1.2查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
SELECT
	* 
FROM
	( SELECT * FROM score WHERE score.Cid = 1 ) AS cid1
	LEFT JOIN ( SELECT * FROM score WHERE score.Cid = 2 ) AS cid2 ON cid1.sid = cid2.sid;
  • 1.3查询不存在" 01 “课程但存在” 02 "课程的情况
SELECT
	* 
FROM
	score 
WHERE
	score.Sid NOT IN ( SELECT score.sid FROM score WHERE score.Cid = 1 ) 
	AND Score.Cid = 2;
-- 法2
SELECT
	cid1.* 
FROM
	( SELECT * FROM score WHERE score.Cid = 2 ) AS cid1
	LEFT JOIN ( SELECT * FROM score WHERE score.Cid = 1 ) AS cid2 ON cid1.sid = cid2.sid 
WHERE
	cid2.Cid IS NULL;
  • 2.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT
	Score.Sid,
	student.Sname,
	AVG( Score.Score ) avg_score 
FROM
	score
	LEFT JOIN student ON score.Sid = student.Sid 
GROUP BY
	score.Sid 
HAVING
	AVG( score.Score ) >= 60;
  • 3.查询在 SC 表存在成绩的学生信息
SELECT DISTINCT
	student.* 
FROM
	student
	LEFT JOIN score ON student.Sid = score.Sid 
WHERE
	score.Cid IS NOT NULL;-- 法2
SELECT
	* 
FROM
	student 
WHERE
	student.Sid IN ( SELECT score.Sid FROM score );-- 法3
SELECT DISTINCT
	student.* 
FROM
	student
	INNER JOIN score ON score.Sid = student.Sid;
  • 4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT
	student.Sid,
	student.Sname,
	COUNT( score.Cid ) 客选总数,
	SUM( score.Score ) 总成绩
FROM
	student
	LEFT JOIN score ON student.Sid = score.Sid 
GROUP BY
	student.Sid 
ORDER BY
	总成绩 DESC;
  • 4.1 查有成绩的学生信息
SELECT
	student.* 
FROM
	student
	LEFT JOIN score ON student.Sid = score.Sid 
GROUP BY
	student.Sid 
HAVING
	SUM( score.Score ) > 0;
    1. 查询「李」姓老师的数量
SELECT
	COUNT( teacher.Tname ) "李姓" 
FROM
	teacher 
WHERE
	SUBSTRING( teacher.Tname, 1, 1 ) = "李";
    1. 查询学过「张三」老师授课的同学的信息
SELECT
	student.* 
FROM
	student
	LEFT JOIN score ON student.Sid = score.Sid
	LEFT JOIN course ON score.Cid = course.Cid
	LEFT JOIN teacher ON teacher.Tid = course.Tid 
WHERE
	teacher.Tname = "张三";
    1. 查询没有学全所有课程的同学的信息
-- 法1 查询结果不包含没有选课程的同学
SELECT
	student.* 
FROM
	student
	LEFT JOIN score ON student.Sid = score.Sid 
GROUP BY
	score.Sid 
HAVING
	COUNT( DISTINCT score.Cid ) < 3 AND COUNT( DISTINCT score.Cid ) > 0;-- 法2 查询结果显示没有选择课程的同学
SELECT
	student.* 
FROM
	student 
WHERE
	student.Sid NOT IN (
	SELECT
		score.Sid 
	FROM
		score 
	GROUP BY
		score.Sid 
	HAVING
		COUNT( DISTINCT score.Cid ) = ( SELECT COUNT( Cid ) FROM course ) 
	);
    1. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
SELECT
	student.* 
FROM
	student
	LEFT JOIN score ON student.Sid = score.Sid 
WHERE
	score.Cid IN ( SELECT score.Cid FROM score WHERE score.Sid = 10 ) 
GROUP BY
	student.Sid;
    1. 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
--  法1
SELECT
	* 
FROM
	student 
WHERE
	sid IN (
	SELECT
		sid 
	FROM
		score 
	WHERE
		sid NOT IN ( SELECT sid FROM score WHERE cid NOT IN ( SELECT cid FROM score WHERE sid = '01' ) ) 
	GROUP BY
		sid 
	HAVING
		count( * ) = ( SELECT count( * ) FROM score WHERE sid = '01' ) 
		AND sid != '01' 
	);
-- 法2
SELECT
	* 
FROM
	student 
WHERE
	Sid IN (
	SELECT
		sid 
	FROM
		(
		SELECT
			sid,
			cid 
		FROM
			score 
		WHERE
			sid IN (
			SELECT
				sid 
			FROM
				score 
			WHERE
				sid != '01' 
			GROUP BY
				sid 
			HAVING
				count( * ) = ( SELECT count( * ) FROM score WHERE sid = '01' GROUP BY sid ) 
			) 
		) AS t1
		INNER JOIN ( SELECT cid FROM score WHERE sid = '01' ) AS t2 ON t1.cid = t2.cid 
	GROUP BY
		t1.sid 
	HAVING
		count( Cid ) = ( SELECT count( Cid ) FROM score WHERE sid = '01' ) 
	)
    1. 查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT
	student.Sname,
	student.Sid 
FROM
	student 
WHERE
	student.Sid NOT IN (
	SELECT
		score.Sid 
	FROM
		score 
	WHERE
		score.Cid IN ( SELECT course.Cid FROM course WHERE course.Tid = ( SELECT teacher.Tid FROM teacher WHERE teacher.Tname = "张三" ) ) 
	);
    1. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
-- 法1
SELECT
	student.Sid,
	student.Sname,
	avg 
FROM
	student
	INNER JOIN (
	SELECT
		score.Sid,
		AVG( score.Score ) AS avg 
	FROM
		score 
	WHERE
		score.Score < 60 GROUP BY score.Sid HAVING COUNT( score.Sid ) > 1 
	) AS t1 ON student.Sid = t1.Sid;
-- 法2
SELECT
	student.Sid,
	student.Sname,
	AVG( score.Score )  as avg
FROM
	student,
	score 
WHERE
	student.Sid = score.Sid 
	AND score.Score < 60 GROUP BY score.Sid HAVING COUNT( score.Sid ) > 1;
    1. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
SELECT
	student.* ,score.Score
FROM
	student
	LEFT JOIN score ON student.Sid = score.Sid 
WHERE
	score.Cid = 1 
	AND score.Score < 60 ORDER BY score.Score DESC;
    1. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT
	score.Sid,
	SUM( score.Score ) sum,
	AVG( score.Score ) avg
FROM
	score 
GROUP BY
	score.Sid 
ORDER BY
	AVG( score.Score ) DESC;
    1. 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率，
      – 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90，要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT
	score.Cid,
	course.Cname,
	COUNT( score.Sid ) count,
	MAX( score.Score ) max,
	MIN( score.Score ) min,
	AVG( score.Score ) avg,
	SUM( CASE WHEN score.Score >= 60 THEN 1 ELSE 0 END ) / count( score.Sid ) "及格率",
	SUM( CASE WHEN score.SCORE >= 70 AND score.Score < 80 THEN 1 ELSE 0 END ) / COUNT( score.Sid ) "中等率",
	SUM( CASE WHEN score.Score >= 80 AND score.Score < 90 THEN 1 ELSE 0 END ) / COUNT( score.Sid ) "优良率",
	SUM( CASE WHEN score.Score >= 90 THEN 1 ELSE 0 END ) / COUNT( score.Sid ) "优秀率" 
FROM
	score,
	course 
WHERE
	score.Cid = course.Cid 
GROUP BY
	score.Cid 
ORDER BY
	count DESC,
	score.Cid;
    1. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
-- 合并
SELECT
	a.cid,
	a.sid,
	a.score,
	count( b.score ) + 1 AS rank 
FROM
	score AS a
	LEFT JOIN score AS b ON a.score < b.score 
	AND a.cid = b.Cid 
GROUP BY   -- 分组后对b表中的score数据计数(此时的计数是按照分组依据删选的)。
	a.cid,
	a.sid,
	a.score 
ORDER BY
	a.cid,
	rank ASC;
	
-- 步骤1
SELECT
	* 
FROM
	score AS a
	LEFT JOIN score AS b ON a.cid = b.cid;
-- 步骤2
SELECT
	* 
FROM
	score AS a
	LEFT JOIN score AS b ON a.score < b.score 
	AND a.cid = b.cid;
-- 步骤3
SELECT
	*
FROM
	score AS a
	LEFT JOIN score AS b ON a.score < b.score 
	AND a.cid = b.cid 
GROUP BY
	a.cid,
	a.sid,
	a.score 
ORDER BY
	a.cid

在这里插入图片描述

  • 15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
SELECT
	a.Cid,
	a.Sid,
	a.Score,COUNT(DISTINCT b.Score)+1 rank
FROM
	score a
	LEFT JOIN score b ON a.Cid = b.Cid 
	AND a.Score < b.Score 
GROUP BY
	a.Cid,
	a.Sid
ORDER BY
	a.Cid,
	rank ;

大大滴

  • 15题 ；另一种理解
SELECT
	*,
CASE
		
		WHEN ( @sco = score ) THEN
		@rank ELSE @rank := @rank + 1 
	END AS rn,
	@sco := score -- 保存上一次的分数
FROM
	score,
	( SELECT @rank := 0, @sco := NULL ) AS t 
ORDER BY
	score DESC;

在这里插入图片描述

    1. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺
SELECT
	sum1.Sid,
	sum1.sum0,
	COUNT( sum2.sum0 ) + 1  rank
FROM
	( SELECT score.Sid, sum( score.Score ) sum0 FROM score GROUP BY score.Sid ) AS sum1
	LEFT JOIN ( SELECT score.Sid, sum( score.Score ) sum0 FROM score GROUP BY score.Sid ) AS sum2 ON sum1.sum0 < sum2.sum0 
GROUP BY
	sum1.Sid
ORDER BY
rank;
  • 16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
SELECT
	sum1.Sid,
	sum1.sum0,
	COUNT( DISTINCT sum2.sum0 ) + 1 rank 
FROM
	( SELECT score.Sid, sum( score.Score ) sum0 FROM score GROUP BY score.Sid ) AS sum1
	LEFT JOIN ( SELECT score.Sid, sum( score.Score ) sum0 FROM score GROUP BY score.Sid ) AS sum2 ON sum1.sum0 < sum2.sum0 
GROUP BY
	sum1.Sid 
ORDER BY
	rank;
    1. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
SELECT
	score.Cid,
	course.Cname,
	SUM( CASE WHEN score.Score < 60 THEN 1 ELSE 0 END ) "0-60",
	SUM( CASE WHEN score.Score < 60 THEN 1 ELSE 0 END ) / COUNT( score.sid ) "0-60百分比",
	SUM( CASE WHEN score.Score < 70 AND score.Score >= 60 THEN 1 ELSE 0 END ) "60-70",
	SUM( CASE WHEN score.Score < 70 AND score.Score >= 60 THEN 1 ELSE 0 END ) / COUNT( score.Sid ) "60-70百分比",
	SUM( CASE WHEN score.Score < 85 AND score.Score >= 70 THEN 1 ELSE 0 END ) "70-85",
	SUM( CASE WHEN score.Score < 85 AND score.Score >= 70 THEN 1 ELSE 0 END ) / COUNT( score.Sid ) "70-85百分比",
	SUM( CASE WHEN score.Score <= 100 AND score.Score >= 85 THEN 1 ELSE 0 END ) "85-100",
	SUM( CASE WHEN score.Score <= 100 AND score.Score >= 85 THEN 1 ELSE 0 END ) / COUNT( score.Sid ) "85-100" 
FROM
	score
LEFT JOIN course ON score.Cid=course.Cid	
GROUP BY
	score.Cid;
    1. 查询各科成绩前三名的记录
SELECT
	* 
FROM
	(
	SELECT
		a.Cid,
		a.sid,
		COUNT( b.Score ) + 1 rank 
	FROM
		score a
		LEFT JOIN score b ON a.Cid = b.Cid 
		AND a.score < b.score 
	GROUP BY
		a.Cid,
		a.Sid 
	ORDER BY
		a.Cid,
		rank 
	) as b
WHERE
	rank<4;
    1. 查询每门课程被选修的学生数
SELECT
	course.Cid,
	COUNT( score.Sid )  "选课人数"
FROM
	course
	LEFT JOIN score ON course.Cid = score.Cid 
GROUP BY
	course.Cid;
    1. 查询出只选修两门课程的学生学号和姓名
SELECT
	student.Sid,
	student.Sname 
FROM
	student 
WHERE
	student.Sid IN ( SELECT Score.Sid FROM score GROUP BY score.Sid HAVING COUNT( score.Cid ) = 2 );

SELECT
	student.Sid,
	student.Sname 
FROM
	student
	INNER JOIN ( SELECT Score.Sid FROM score GROUP BY score.Sid HAVING COUNT( score.Cid ) = 2 ) a ON student.Sid = a.Sid;
    1. 查询男生、女生人数
SELECT
	student.Ssex,
	COUNT( student.Sid ) count 
FROM
	student 
GROUP BY
	student.Ssex;
    1. 查询名字中含有「风」字的学生信息
SELECT
	* 
FROM
	student 
WHERE
	student.Sname LIKE "%风%";
    1. 查询同名同性学生名单，并统计同名人数
-- 法1
SELECT
	b.*,
	COUNT(b.Sname) "人数"
FROM
	student a
	LEFT JOIN student b ON a.Sname = b.Sname 
	AND a.Ssex = b.Ssex 
	AND a.Sid <> b.Sid;

-- 法2
SELECT 
st.*,
COUNT(st.Sname) 
FROM student st   -- GROUP BY 相当于取相同值
GROUP BY st.Sname,st.ssex HAVING COUNT(st.Sname)>1;
    1. 查询 1990 年出生的学生名单
SELECT
	* 
FROM
	student 
WHERE
	student.Sage BETWEEN "1990-01-01" 
	AND "1990-12-31";
    1. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT
	score.Cid,
	AVG( score.Score ) avg 
FROM
	score 
GROUP BY
	score.Cid 
ORDER BY
	avg DESC,
	score.Cid;
    1. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECT
	student.Sid,
	student.Sname,
	AVG(score.Score) avg
FROM
	score
	LEFT JOIN student ON score.Sid = student.Sid 
GROUP BY
	student.Sid 
HAVING
	avg >= 85;
    1. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
SELECT
	student.Sname,
	score.Score 
FROM
	student
	LEFT JOIN score ON student.Sid = score.Sid 
WHERE
	score.Cid =(SELECT
	course.Cid 
FROM
	course 
WHERE
	course.Cname = "数学" 
	) 
	AND score.Score < 60;
-- 法2	
SELECT
	student.Sname,
	score.Score 
FROM
	student,
	score 
WHERE
	student.Sid = score.Sid 
	AND score.Cid =(SELECT
	course.Cid 
FROM
	course 
WHERE
	course.Cname = "数学" 
	) 
	AND score.Score < 60;
    1. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
SELECT
	student.Sid,
	score.Cid,
	score.Score 
FROM
	student
	LEFT JOIN score ON score.Sid = student.Sid 
ORDER BY
	student.Sid
    1. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
SELECT
	student.Sname,
	course.Cname,
	score.Score 
FROM
	student,
	score,
	course 
WHERE
	student.Sid = score.Sid 
	AND score.Cid = course.Cid 
	AND student.Sid NOT IN ( SELECT score.Sid FROM score WHERE score.Score <= 70 );
    1. 查询不及格的课程
SELECT
	score.Cid,
	course.Cname 
FROM
	score,
	course 
WHERE
	score.Cid = course.Cid 
	AND score.Score < 60 
GROUP BYss
	score.Cid;
    1. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
SELECT student.Sid, student.Sname FROM student WHERE student.Sid IN ( SELECT score.Sid FROM score WHERE score.Score > 80 
	AND score.Cid = 1 
)
    1. 求每门课程的学生人数
SELECT
	score.Cid,
	count( score.Sid ) 
FROM
	score 
GROUP BY
	score.Cid;
    1. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT
	student.*,
	MAX( DISTINCT score.Score ) max
FROM
	student
	LEFT JOIN score ON score.Sid = student.Sid
	LEFT JOIN course ON score.Cid = course.Cid
	LEFT JOIN teacher ON course.Tid = teacher.Tname 
	AND teacher.Tname = "张三" ;
    1. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT
	student.*,
DISTINCT score.Score ) max
FROM
	student
	LEFT JOIN score ON score.Sid = student.Sid
	LEFT JOIN course ON score.Cid = course.Cid
	LEFT JOIN teacher ON course.Tid = teacher.Tid
	AND teacher.Tname = "张三" ;
    1. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT
	* 
FROM
	score
	LEFT JOIN student ON score.Sid = student.Sid;
    1. 查询每门功成绩最好的前两名
SELECT
	c.Cid,
	student.Sname,
	rank 
FROM
	student
	LEFT JOIN (
	SELECT
		a.Cid,
		a.Sid,
		COUNT( b.Score ) + 1 rank 
	FROM
		score a
		LEFT JOIN score b ON a.Cid = b.Cid 
		AND a.Score < b.Score 
	GROUP BY
		a.Sid,
		a.Cid 
	ORDER BY
		a.Cid,
		rank 
	) AS c ON student.Sid = c.Sid 
WHERE
	c.rank < 3 
ORDER BY
	c.cid,
	rank;
    1. 统计每门课程的学生选修人数（超过 5 人的课程才统计）。
SELECT
	score.Cid,
	COUNT( score.Sid ) 选课人数 
FROM
	score 
GROUP BY
	score.Cid 
HAVING
	COUNT( score.Sid ) > 5;
    1. 检索至少选修两门课程的学生学号
SELECT
	score.Sid,
	COUNT( score.Cid ) 选课人数 
FROM
	score 
GROUP BY
	score.Sid 
HAVING
	COUNT( score.Cid ) > 1;
    1. 查询选修了全部课程的学生信息
SELECT
	student.* 
FROM
	student
	LEFT JOIN score ON score.Sid = student.Sid 
GROUP BY
	student.Sid 
HAVING
	COUNT( score.Cid ) = ( SELECT COUNT( course.Cid ) FROM course );
    1. 查询各学生的年龄，只按年份来算
SELECT
	student.Sid,
	YEAR ( CURDATE( ) ) - YEAR ( student.Sage ) age 
FROM
	student;
    1. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
-- 当前月日 < 出生年月的月日
SELECT
	student.Sid,
	YEAR ( CURDATE( ) ) - YEAR ( student.Sage )-1 age 
FROM
	student WHERE DATE_FORMAT(student.Sage,"%m-%d")<DATE_FORMAT(CURDATE(),"%m-%d");
	
-- 合并
SELECT
	student.Sid,
	(
	CASE
			
			WHEN DATE_FORMAT( student.Sage, "%m-%d" ) < DATE_FORMAT( CURDATE( ), "%m-%d" ) THEN
			YEAR ( CURDATE( ) ) - YEAR ( student.Sage ) - 1 ELSE YEAR ( CURDATE( ) ) - YEAR ( student.Sage ) 
		END 
		) AS age 
	FROM
		student;
    1. 查询本周过生日的学生
-- 本周的第一天日期和最后一天日期。
SELECT
	DATE_ADD( CURDATE( ), INTERVAL - DAYOFWEEK( now( ) ) + 2 DAY ),
	DATE_ADD( CURDATE( ), INTERVAL - DAYOFWEEK( now( ) ) + 8 DAY ) 
FROM
	student;
	
-- 合并	
SELECT
	student.Sname 
FROM
	student 
WHERE
	DATE_FORMAT( Sage, "%m-%d" ) 
	BETWEEN 
	DATE_FORMAT( DATE_ADD( CURDATE( ), INTERVAL - DAYOFWEEK( now( ) ) + 2 DAY ), "%m-%d" ) 
	AND 
	DATE_FORMAT( DATE_ADD( CURDATE( ), INTERVAL - DAYOFWEEK( now( ) ) + 8 DAY ), "%m-%d" );
    1. 查询下周过生日的学生
SELECT
	student.Sname 
FROM
	student 
WHERE
	DATE_FORMAT( Sage, "%m-%d" ) BETWEEN DATE_FORMAT( DATE_ADD( CURDATE( ), INTERVAL - DAYOFWEEK( now( ) ) + 9 DAY ), "%m-%d" ) 
	AND DATE_FORMAT( DATE_ADD( CURDATE( ), INTERVAL - DAYOFWEEK( now( ) ) + 17 DAY ), "%m-%d" );
    1. 查询本月过生日的学生
SELECT
	student.Sname 
FROM
	student 
WHERE
	MONTH(student.Sage) =MONTH(CURDATE());
    1. 查询下月过生日的学生
SELECT
	student.Sname 
FROM
	student 
WHERE
	MONTH(student.Sage) =MONTH(CURDATE())+1;
  • 补充：------------ 时间函数
SELECT
	student.Sage,
	DAYNAME( Sage ),
	WEEKDAY( Sage ),
	WEEK ( Sage, 1 ),
	WEEKOFYEAR( Sage ),
	DAYOFWEEK( Sage ),
	YEARWEEK( Sage ),
	YEARWEEK( sage, 0 ) 
FROM
	student;
-- eg：	
SELECT
	student.Sid 
FROM
	student 
WHERE
	WEEKOFYEAR( student.Sage ) = WEEKOFYEAR( NOW( ) );

在这里插入图片描述

米米吉吉
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
sql join 子查询_SQL练习 | 复杂查询、子查询、常用函数
weixin_39922769的博客
12-06 264
1、视图什么是视图视图中存放的是SQL查询语言，使用视图时，会运行视图里的SQL查询语句，创建出一张临时表。如何用视图create view 视图名称（<视图名称>,<视图名称>）as<select 查询语句>视图有什么用频繁使用某一子句时，将其保存为视图，减少程序视图中的数据会随源表中的数据变化视图中保存的是SQL查询语句，不是数据，节省空间注意事项避免在视图...
Mysql经典45道题(详解16-30)
Hudas的博客
02-12 5195
Mysql经典45道题(详解16-30)
Python 之MySql每日一练 232——查询每门课程的平均成绩
hdx柿子的博客
03-26 1996
本文参考原文-http://bjbsair.com/2020-03-25/tech-info/6230/ 一、表名和字段 –1. 学生表 student (s_id,s_name,s_birth,s_sex) –学生编号，学生姓名，出生年月，学生性别 –2. 课程表 course (c_id,c_name,t_id) –课程编号，课程名称，教师编号 –3. 教师表 teacher (t_id...
mysql 45题 答案,MYSQL经典45题1刷
weixin_36299242的博客
03-23 1415
Mysql 经典练习题我使用的Mysql版本是5.7.27-log，答案可能会因版本会有少许出入。一、数据源准备及说明：1.1、数据字段说明:1.学生表 Student(SId,Sname,Sage,Ssex)SId ：学生编号Sname：学生姓名Sage ：出生年月Ssex：学生性别2.课程表 Course(CId,Cname,TId)CId ：课程编号Cname ：课程名称TId ：教师编号3...
2020-08-07
weixin_41724761的博客
08-07 270
一个项目涉及到的50个Sql语句(整理版) –1.学生表 Student(S,Sname,Sage,Ssex) --S 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别 –2.课程表 Course(C,Cname,T) --C --课程编号,Cname 课程名称,T 教师编号 –3.教师表 Teacher(T,Tname) --T 教师编号,Tname 教师姓名 –4.成绩表 SC(S,C,score) --S 学生编号,C 课程编号,score 分数 */ –创建测试数据 creat
SQL2022-SSEI-Dev
11-14
SQL2022-SSEI-Dev
经典T-SQL.doc
11-18
经典T-SQL.doc
SQL语句练习-50题
最新发布
04-07
建表要求&查询要求
SQL-Server笔试题含答案
03-05
SQL-Server笔试题含答案
x-pack-sql-jdbc-7.4.2.jar
06-22
x-pack-sql-jdbc-7.4.2.jar jdbc驱动包 x-pack-sql-jdbc-7.4.2.jar jdbc驱动包
MYSQL基础知识总结
weixin_30672295的博客
07-31 180
一、数据库特点:结构化,无有害,无重复; 二、数据库优点:按一定的数据模型组织,描述和储存;可为各种用户共享,冗余度小,节省储存空间易扩展,编写有关数据应用程序。 三、常用Dos操作指令: 安装数据库:mysqld -install,开启/关闭数据库:start mysql/net stop,监听端口信息:netstat -a,登陆数据库:mysql -uroot -p,显示默认数据库:us...
sql的50道练习题(更新中。。)
qq_42858191的博客
09-16 2112
方方的我马上要准备面试了，感觉自己脑子里面什么都没有，只能拿出50个sql题先复习一下基础了。。 哎。。。 drop database if exists student_manager; create database student_manager; use student_manager; create table student( snu varchar(2) primary key, sname varchar(10) not null, age date default.
Mysql练习
chenxi8316的博客
10-07 763
1.创建student和score表 CREATE TABLE student ( id INT(10) NOT NULL UNIQUE PRIMARY KEY , name VARCHAR(20) NOT NULL , sex VARCHAR(4) , birth Y...
mysql笔记简答。
m0_55904295的博客
04-28 2577
1.请使用 select into...outfile 语句，备份数据库 db_test 中表 content 的全部数据到 c 盘的 backup 目录下一个名为 backupcontent. txt 的文件中 答： usedb_test; SELECT* FROMcontent INTOOUTFILE ‘C:/baczup/backupcontent. txt’ FIELDS TERMINATED BY ',’OPTIONALY ENCLOSED BY'"'LINES TE...
MySQL数据库题目：查询一班得分在80分以上【杭州多测师】【杭州多测师_王sir】...
多测师_王sir的博客
07-12 2865
面试题一 请看下面三张表：学生表(学生id，姓名，性别，分数) )student(s＿id， name， sex， score) 班级表(班级id，班级名称) class(c_id， c_name) 学生班级表(班级id，学生id) student_class(s_id，c_id) 1．查询一班得分在80分以上或者等于60，61，62的学生 select s_id,name,score,c_na...
查询课程数mysql_mysql_数据查询练习
weixin_29829343的博客
02-03 4712
course：teachar:studentsc:1、查询“c001”课程比“c002”课程成绩高的所有学生的学号；select a.sid from(select sid,score from sc where cid=001) as a,(select sid,score from sc where cid=002) as bwhere a.score > b.score and a.s...
数据库作业之多表查询
yikenaoguazi的博客
09-08 926
day45-461.表结构:2.建表3.练习题 1.表结构: 2.建表 # 1、创建表 # 创建班级表 create table class( cid int primary key auto_increment, caption varchar(32) not null ); # 创建学生表 create table student( sid int primary key auto_increment, gender char(1) not null, class_id int not null,
vc mysql 查询_MYSQL之查询篇
weixin_32656511的博客
01-19 184
2. 数据库操作数据库在创建以后最常见的操作便是查询2.1 查询为了便于学习和理解，我们预先准备了两个表分别是stduents表和classes表两个表的内容和结构如下所示students表的内容:idclass_idnamegenderscore11小明M9021小红F9531小军M8841小米F7352小白F8162小兵M5572小林M8583小新F9193小王M89103小丽F85创建stu...
50个常用SQL语句
cake_liu的专栏
07-22 1305
50个常用SQL语句 Student(S#,Sname,Sage,Ssex) 学生表 S#学号，主键 Course(C#,Cname,T#) 课程表 C#课程号，主键 SC(S#,C#,score) 成绩表 Teacher(T#,Tname) 教师表 T#教师编号，主键 问题： 1、查询“001”课程比“002”课程成绩高的所有学生的学...
sqllabs1-75
09-03
chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 100%"] ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值