SQL50题目

CREATE TABLE student (
				s_id INT,
				s_name VARCHAR(20) NOT NULl DEFAULT "", -- 默认为空，非空
				s_birth VARCHAR(20) NOt NULL DEFAULT "",
				s_sex VARCHAR(2) NOT NULL DEFAULT "男",
				PRIMARY KEY(s_id) -- 设置主键
);

CREATE TABLE course(
				c_id INT(5) PRIMARY KEY auto_increment, -- 加个自增长
				c_name VARCHAR(20) NOT NULL DEFAULT "",
				t_id INT NOT NULL
				

);


CREATE TABLE Teacher(
    t_id VARCHAR(20),
    t_name VARCHAR(20) NOT NULL DEFAULT '',
    PRIMARY KEY(t_id)
);

CREATE TABLE Score(
		s_id  VARCHAR(20) ,
		c_id  VARCHAR(20) ,
		s_score INT(3),
		PRIMARY KEY (s_id,c_id)
)

/*插入数据，几种方法都试一下
*/

INSERT INTO student
VALUES
	( '01', '赵雷', '1990-01-01', '男' );
INSERT INTO student
VALUES
	( '02', '钱电', '1990-12-21', '男' );
INSERT INTO student
VALUES
	( '03', '孙风', '1990-05-20', '男' );
INSERT INTO student
VALUES
	( '04', '李云', '1990-08-06', '男' );



INSERT INTO student
VALUES
	('05' , '周梅' , '1991-12-01' , '女'),
	('06' , '吴兰' , '1992-03-01' , '女'),
	('07' , '郑竹' , '1989-07-01' , '女'),
	('08' , '王菊' , '1990-01-20' , '女');
	
	
INSERT INTO course
VALUES
	( '01', '语文', '02' );
INSERT INTO course
VALUES
	( '02', '数学', '01' );
INSERT INTO course
VALUES
	( '03', '英语', '03' );
	SELECT * FROM course;
	
	
INSERT INTO Teacher
VALUES
	( '01', '张三' );
INSERT INTO Teacher
VALUES
	( '02', '李四' );
INSERT INTO Teacher
VALUES
	( '03', '王五' );
	
INSERT INTO Score
VALUES
	( '01', '01', 80 );
INSERT INTO Score
VALUES
	( '01', '02', 90 );
INSERT INTO Score
VALUES
	( '01', '03', 99 );
INSERT INTO Score
VALUES
	( '02', '01', 70 );
INSERT INTO Score
VALUES
	( '02', '02', 60 );
INSERT INTO Score
VALUES
	( '02', '03', 80 );
INSERT INTO Score
VALUES
	( '03', '01', 80 );
INSERT INTO Score
VALUES
	( '03', '02', 80 );
INSERT INTO Score
VALUES
	( '03', '03', 80 );
INSERT INTO Score
VALUES
	( '04', '01', 50 );
INSERT INTO Score
VALUES
	( '04', '02', 30 );
INSERT INTO Score
VALUES
	( '04', '03', 20 );
INSERT INTO Score
VALUES
	( '05', '01', 76 );
INSERT INTO Score
VALUES
	( '05', '02', 87 );
INSERT INTO Score
VALUES
	( '06', '01', 31 );
INSERT INTO Score
VALUES
	( '06', '03', 34 );
INSERT INTO Score
VALUES
	( '07', '02', 89 );
INSERT INTO Score
VALUES
	( '07', '03', 98 );
	
	INSERT INTO student VALUES ('08' , '王菊' , '1990-01-20' , '女');
	SELECT * FROM student;
	INSERT INTO Score(s_id) VALUES ("08");
	DELETE FROM Score WHERE s_id = "08";
	
	
	#查询01课程比02课程成绩高的学生信息和分数
	SELECT * FROM course;
	SELECT * FROM student;
	SELECT * FROM Score;

	
#使用自连接，将Socre中01课程筛选出来为表1，跟02课程筛选出来的表(表2)进行连接
#若表1中分数大于表2中分数，就留下来
SELECT  
	s1.s_id,   -- 选出课程为1的同学的分数
	s1.s_score S1,-- s1的课程分数
	s2.s_score S2 -- s2的课程分数
FROM
	Score s1 INNER JOIN Score s2
ON
	s1.s_id = s2.s_id AND s1.c_id = 01  -- 将表A中课程为01科目和表二中02课程作为筛选条件，
	                                     -- 留下表1中为01的科目和表2中科目为02的科目
	AND s2.c_id = 02
WHERE
	s1.s_score > s2.s_score;
	
	
	#将筛选后结果与student表进行连接,使用子查询进行链接
	SELECT 
		*
	FROM
	student st INNER JOIN
	#使用自连接，将Socre中01课程筛选出来为表1，跟02课程筛选出来的表(表2)进行连接
#若表1中分数大于表2中分数，就留下来
(SELECT  
	s1.s_id,   -- 选出课程为1的同学的分数
	s1.s_score S1,-- s1的课程分数
	s2.s_score S2 -- s2的课程分数
FROM
	Score s1 INNER JOIN Score s2
ON
	s1.s_id = s2.s_id AND s1.c_id = 01  -- 将表A中课程为01科目和表二中02课程作为筛选条件，
	                                     -- 留下表1中为01的科目和表2中科目为02的科目
	AND s2.c_id = 02
WHERE
	s1.s_score > s2.s_score) s2
	ON
	st.s_id = s2.s_id;

	

	
	
#方法2，不子查询，在原来基础上，直接再链接一张表

#使用自连接，将Socre中01课程筛选出来为表1，跟02课程筛选出来的表(表2)进行连接
#若表1中分数大于表2中分数，就留下来,比较省地方
SELECT  
	st.*,
	s1.s_id,   -- 选出课程为1的同学的分数
	s1.s_score S1,-- s1的课程分数
	s2.s_score S2 -- s2的课程分数
FROM
	Score s1 INNER JOIN Score s2 INNER JOIN
	Student st
ON
	s1.s_id = s2.s_id AND s1.c_id = 01  -- 将表A中课程为01科目和表二中02课程作为筛选条件，
	                                     -- 留下表1中为01的科目和表2中科目为02的科目
	AND s2.c_id = 02 AND st.s_id = s2.s_id
WHERE
	s1.s_score > s2.s_score;



	#查询01课程分数比02课程分数低学生信
	SELECT
		st.*,
		s1.s_id,
		s1.s_score,
		s2.s_score
		
	FROM
	Score s1 INNER JOIN Score s2 INNER JOIN student st
	ON
	s1.s_id = s2.s_id AND s1.c_id = 01  -- 将Score中01数据和Score中02数据抽出来组成表
	
	AND s2.c_id = 02 AND st.s_id = s1.s_id
	WHERE
	s1.s_score < s2.s_score;
	
	#查询平均成绩大于60分的学生的编号姓名，平均成绩
	-- 
	
	SELECT
			s.s_id,
			AVG(s.s_score) 平均分数,
			st.*
	FROM
			Score  s INNER JOIN student st
	ON 
			s.s_id = st.s_id
	GROUP BY
			s.s_id
	HAVING 
			平均分数 > 60;
			
#查询平均成绩小60分的学生的编号姓名，平均成绩，包括没成绩的
#因为有没成绩的，所以要用外连接
#需要用到外连接中ifnull时候，在筛选条件和显示条件中，都需要加ifnull
SELECT
			s.s_id 学生分数,
			IFNULL(AVG(s.s_score),0) 平均分数,
			st.*
	FROM
			student st LEFT JOIN Score s   -- 要左外，防止王菊这个没成绩的被过滤掉
			-- 以Student作为主表，防止Student中数据王菊的数据被删掉
			-- 左外连接就是，将左表中和右边匹配不上的数据强行保留下来，匹配不上的位置用null
	ON 
			s.s_id = st.s_id
	GROUP BY
			s.s_id
	HAVING 
			IFNULL(AVG(s.s_score),0) < 60;
			
		SELECT * FROM student;

			
			
#4、所有学生编号，姓名，选课情况（几门课），所有课总成绩

#用到score表，统计学生总成绩，student表统计学生信息，

#先弄出来学生选课情况和所有课程分数,根据s_id分组，在score表上
SELECT * FROM Score;
SELECT
	st.*,
	s.s_id,
	COUNT( s.s_score ),
	SUM(s.s_score )
FROM
	Score s INNER JOIN student st
ON
	s.s_id = st.s_id
GROUP BY
	s.s_id;
	
	
#查询"李"姓老师的数量
SELECT * FROM teacher;
#使用表teacher表
SELECT
		COUNT(t_name)
FROM
		teacher
WHERE
		t_name LIKE "李%";
		
		
#查询学过"张三"老师授课的同学的信息
#在teacher表中找到张三老师的t_id,在coure中找到老师的c_id
#根据c_id在score表中找到学生的s_id,根据s_id查到学生信息
/*
1.将teacher表和course表进行关联
2.关联之后，根据张三老师姓名找到老师的c_id
3.继续关联score表，利用c_id找到学生的s_id
4.利用s_id关联student表，找到学生信息


*/
SELECT
	st.*,
	c.c_id,-- 只要找到张三老师的课id就行
	s.s_id -- 三张表联合查选了张三课程的学生id
	
FROM
	teacher t
	INNER JOIN course c
	INNER JOIN Score s
	INNER JOIN student st  -- 再把学生信息表拼上来
ON
	t.t_id = c.t_id 
	AND s.c_id = c.c_id
	AND s.s_id = st.s_id
	
WHERE
	t_name = "张三";



#查询没参与过张三老师授课学生
/*上一个题目是参加过张三老师课程的人，这道题可以选择把上一题查询结果作为条件，
筛选出没参加过张三老师课程的人
*/
	
	
SELECT 
*
FROM
	student
WHERE
	s_id NOT IN(
		SELECT
			s.s_id -- 三张表联合查选了张三课程的学生id
		FROM
			teacher t
			INNER JOIN course c
			INNER JOIN Score s
			INNER JOIN student st  -- 再把学生信息表拼上来
		ON
			t.t_id = c.t_id 
			AND s.c_id = c.c_id
			AND s.s_id = st.s_id
			
		WHERE
			t_name = "张三"
	
	);


#查询编号01，并且学过编号02课程的学生信息
/*
从student表中查到编号01的学生信息
从02表中查到学习课程02的学生信息
两张表进行匹配，根据s_id进行匹配
*/

SELECT
	st.*
FROM
student st INNER JOIN score s
ON
	st.s_id = s.s_id
WHERE
	st.s_id = 01 AND
	s.c_id = 02;
	
	
	#查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
	/*
	Score内学过编号01和没学过编号02的同学抽出来弄两张表
	
	*/
	
	SELECT
		st.*  -- 我的想法是错误的
	FROM
			Score s1,Score s2,student st
	WHERE
			s1.s_id = s2.s_id AND s1.s_id = st.s_id
			AND s1.c_id = 01 AND s2.c_id = 02;
			
/*以上思路是错误的，原因在于，有很多学生选了1，2，3三门课，
我们需要的是选了1，3科目学生，但是上面的方法，只是把Score表中所有选了02课程那一行给移除群聊了而已，
而没把那个同学的id给移除群聊
相当于微信聊天，群里有人说了反动的话，正确说法应该是把那个人移除群聊，不再说话，而不是只是删除那段话
直接将选02课程的人直接拉出去
*/
			
			
			
SELECT
	a.* 
FROM
	student a 
WHERE
	a.s_id IN ( SELECT s_id FROM score WHERE c_id = '01' ) 
	AND a.s_id NOT IN ( SELECT s_id FROM score WHERE c_id = '02' );
	/*上面的方法是将选了01课程同学id,抽出来，看看在不在选了02课程的同学里
	如果不在，符合条件
	*/
	
	
	#查询没有学全所有课程的同学的信息 
	/*
	一共三门课程，还要把什么都没学那位给加上
	按学生id分组，count 课程数量小于3的就是那啥的
	记得用外连接将啥也没学那位给加进来
	*/
	
	SELECT
		st.*,
		COUNT(IFNULL(s.c_id,0))   -- 注意ifnull用法，加载字条上也行
	FROM
	student st LEFT JOIN score s
	ON 
		s.s_id = st.s_id
	GROUP BY
		st.s_id
	HAVING
		COUNT(IFNULL(s.c_id,0)) < 3;
		
		#查询至少有一门课与学号为"01"的同学所学相同的同学的信息 
		#先查询学号01同学学得课程，然后让同学们匹配一下
		SELECT
		 c_id
		 FROM
		 Score
		 WHERE
		 s_id = 01; -- 查询学号为01同学所学的课程，然后作为条件
		 
SELECT DISTINCT
	st.*
FROM
	Score s
	INNER JOIN student st ON st.s_id = s.s_id 
WHERE
	s.c_id IN ( SELECT c_id FROM Score WHERE s_id = 01 );
	


#查询没上过张三老师课程的学生姓名
#先通过teacher表和course，查询张三老师负责任教的课程
#然后利用张三老师的课程名字去Score表中筛选出学生id
SELECT
	s_id    -- 拿到没上过张三老师课程同学的名单
FROM
	teacher t,
	course c,score s
WHERE
	t_name LIKE "张三" 
	AND t.t_id = c.t_id
	AND c.c_id = s.c_id ;
	
	#将名单作为条件tudent表中没上过课的同学
SELECT 
	s_name
FROM
	student 
WHERE
	s_id NOT IN (
	SELECT
		s_id -- 拿到没上过张三老师课程同学的名单
	FROM
		teacher t,
		course c,
		score s 
	WHERE
		t_name LIKE "张三" 
		AND t.t_id = c.t_id 
		AND c.c_id = s.c_id 
	)
		
		

#查询两门以及以上不及格同学姓名和平均成绩

#将student表和score表进行外连接
SELECT
	st.s_id ,
	st.s_name
FROM
student stLEFT JOIN score s
ON
	st.s_id = s.s_id
GROUP BY
	st.s_id