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
SQL50题目
最新推荐文章于 2022-03-25 15:42:11 发布