国外推广网站,免费云电脑(可玩大型游戏),南联网站建设推广,辽宁地矿建设集团有限公司网站首先写一个三个表的联合查询
查询所有同学的每门课成绩#xff0c;及同学的个人信息
1.我们首先要确定使用哪些表
学生表#xff0c;课程表#xff0c;成绩表
2.取笛卡尔积 select * from score,student,course; 3. 确定表与表之间的联合条件
select * from score,stud…首先写一个三个表的联合查询
查询所有同学的每门课成绩及同学的个人信息
1.我们首先要确定使用哪些表
学生表课程表成绩表
2.取笛卡尔积 select * from score,student,course; 3. 确定表与表之间的联合条件
select * from score,student,course where score.student_idstudent.student_id and course.course_idscore.course_id; 4.确定过滤条件无
5.精简查询字段 select student.student_id,student.sn,score.score,course.name from score,student,course where score.student_idstudent.student_id and course.course_idscore.course_id; 外连接 外连接分为左外连接和右外连接。如果联合查询左侧的表完全显示我们就说是左外连接右侧的表完全显示我们就说是右外连接。 使用前 select * from student,class where student.class_idclass.class_id; 右外连接 select * from student right join class on student.class_idclass.class_id; 右外连接是以join 右边的表为基准这个标的数据会全部的显示出来左边的表没有与之匹配的记录全部会用null去填充。 左外连接 select * from student left join class on student.class_idclass.class_id; 查询哪些同学没有成绩 select * from student left join score on student.student_idscore.student_id; 都可以用来找出没有成绩的人
select * from student left join score on student.student_idscore.student_id where score_id is null;select * from student left join score on student.student_idscore.student_id where score.score is null; 自连接
原理自己和自己的表进行连接
注可以把行转为列通过where进行过滤进行行与行之间的比较。
显示所有的“计算机原理”成绩比“Java”成绩高的成绩信息 比较相同的表时要将表给与别名否则无法比较 select * from score sc1,score sc2; 判断连接的条件
select * from score sc1,score sc2 where sc1.student_idsc2.student_id; 找出同时有计算机原理和Java的信息
select * from score sc1,score sc2 where sc1.student_idsc2.student_id and sc1.course_id3 and sc2.course_id1; 找出计算机原理比Java成绩高的
select * from score sc1,score sc2 where sc1.student_idsc2.student_id and sc1.course_id3 and sc2.course_id1 and sc1.scoresc2.score; 子查询
也叫嵌套查询子查询是指嵌入在其他sql语句中的select语句。
select * from table where table_id(select id from table2 where ......) 单行子查询
查询不想毕业同学的同班同学
1.参与查询的表
学生表
2.找到这个同学的班级编号 select class_id from student where name不想毕业; 3.找出与不想毕业同学班级编号相同的同学 select student_id,name,class_id from student where class_id1; 4.使用子查询通过嵌套查询
select student_id,name,class_id from student where class_id(select class_id from student where name不想毕业); 5.除去这个同学
select student_id,name,class_id from student where class_id(select class_id from student where name不想毕业) and name!不想毕业; 多行子查询
查询语文或英语的课程成绩信息
1.需要的表
课程表成绩表
2.获取语文和英语的课程编号
select name,course_id from course where name语文or name 英文; 3.根据获得的编号在成绩表中查询成绩 select * from score where course_id4 or course_id6; 4.将上述的分布查询并起来成子查询
select * from score where course_id in(select course_id from course where name语文 or name 英文); 注如果查询不包括语文和英文的课程成绩信息
select * from score where course_id not in(select course_id from course where name语文 or name 英文); 查询重复的分数 select * from score where (student_id,course_id,score)- in (select student_id,course_id,score from score group by student_id,course_id,score having count(*) 1); [NOT] EXISTS关键字
exists 后面的括号中的查询语句如果有结果返回则执行外层的查询如果返回是一个空结果集则不执行外层的查询相当于if语句进行判断有结果返回true无结果返回false
select * from student where exists(select * from student where student_id1); 如果exists内返回是空结果集则不执行外层查询 查询所有比“中文系2019级3班”平均分高的成绩信息
1.获得中文系2019级3班的平均分
先从班级中根据班级名找到班级编号根据班级编号在学生表中找到班里的学生及学生编号根据学生的编号在成绩表中计算出平均分
select avg(score.score) from score,student,class where class.class_idstudent.class_id and student.student_idscore.student_id and class.name中文系2019级3班; 2.再用表中的真实成绩和上表的平均值做比较 select * from score,( select avg(score.score) score from score,student,class where class.class_idstudent.class_id and student.student_idscore.stude
nt_id and class.name中文系2019级3班) tmp where score.scoretmp.score; 合并查询
(UNUIN) (UNION all)
原理将多个查询结果合并到一个结果集中 将两个表合并
select * from student where student_id1 union select * from student where name 许仙; 注
在单表中推荐用or去连接不同的条件
再多表中就无法用or如果最终的结果是从多个表中获得到的必须用union进行合并
union和union all的差别
1.根据一张表的结构创建新表
create table student2 like student; 2.将新表中添加几个数据 通过union把student表和student2表并在一起 通过union all把student表和student2表并在一起 我们可以看出union合并时会去掉重复行union all 不会去掉重复行 上述讲解用到的几个表
DROP TABLE IF EXISTS score;
DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS class;
DROP TABLE IF EXISTS course;CREATE TABLE class (class_id bigint NOT NULL AUTO_INCREMENT,name varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,PRIMARY KEY (class_id) USING BTREE
) ENGINE InnoDB AUTO_INCREMENT 4 CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ROW_FORMAT Dynamic;INSERT INTO class VALUES (1, 计算机系2019级1班);
INSERT INTO class VALUES (2, 中文系2019级3班);
INSERT INTO class VALUES (3, 自动化2019级5班);CREATE TABLE course (course_id bigint NOT NULL AUTO_INCREMENT,name varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,PRIMARY KEY (course_id) USING BTREE
) ENGINE InnoDB AUTO_INCREMENT 7 CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ROW_FORMAT Dynamic;INSERT INTO course VALUES (1, Java);
INSERT INTO course VALUES (2, 中国传统文化);
INSERT INTO course VALUES (3, 计算机原理);
INSERT INTO course VALUES (4, 语文);
INSERT INTO course VALUES (5, 高阶数学);
INSERT INTO course VALUES (6, 英文);CREATE TABLE student (student_id bigint NOT NULL AUTO_INCREMENT,sn varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,name varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,mail varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,class_id bigint NULL DEFAULT NULL,PRIMARY KEY (student_id) USING BTREE,UNIQUE INDEX sn(sn ASC) USING BTREE,INDEX class_id(class_id ASC) USING BTREE,CONSTRAINT student_ibfk_1 FOREIGN KEY (class_id) REFERENCES class (class_id) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE InnoDB AUTO_INCREMENT 9 CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ROW_FORMAT Dynamic;INSERT INTO student VALUES (1, 09982, 黑旋风李逵, xuanfengqq.com, 1);
INSERT INTO student VALUES (2, 00835, 菩提老祖, NULL, 1);
INSERT INTO student VALUES (3, 00391, 白素贞, NULL, 1);
INSERT INTO student VALUES (4, 00031, 许仙, xuxianqq.com, 1);
INSERT INTO student VALUES (5, 00054, 不想毕业, NULL, 1);
INSERT INTO student VALUES (6, 51234, 好好说话, sayqq.com, 2);
INSERT INTO student VALUES (7, 83223, tellme, NULL, 2);
INSERT INTO student VALUES (8, 09527, 老外学中文, foreignerqq.com, 2);CREATE TABLE score (score_id bigint NOT NULL AUTO_INCREMENT,student_id bigint NULL DEFAULT NULL,course_id bigint NULL DEFAULT NULL,score decimal(5, 2) NULL DEFAULT NULL,PRIMARY KEY (score_id) USING BTREE,INDEX student_id(student_id ASC) USING BTREE,INDEX course_id(course_id ASC) USING BTREE,CONSTRAINT score_ibfk_1 FOREIGN KEY (student_id) REFERENCES student (student_id) ON DELETE RESTRICT ON UPDATE RESTRICT,CONSTRAINT score_ibfk_2 FOREIGN KEY (course_id) REFERENCES course (course_id) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE InnoDB AUTO_INCREMENT 21 CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ROW_FORMAT Dynamic;INSERT INTO score VALUES (1, 1, 1, 70.50);
INSERT INTO score VALUES (2, 1, 3, 98.50);
INSERT INTO score VALUES (3, 1, 5, 33.00);
INSERT INTO score VALUES (4, 1, 6, 98.00);
INSERT INTO score VALUES (5, 2, 1, 60.00);
INSERT INTO score VALUES (6, 2, 5, 59.50);
INSERT INTO score VALUES (7, 3, 1, 33.00);
INSERT INTO score VALUES (8, 3, 3, 68.00);
INSERT INTO score VALUES (9, 3, 5, 99.00);
INSERT INTO score VALUES (10, 4, 1, 67.00);
INSERT INTO score VALUES (11, 4, 3, 23.00);
INSERT INTO score VALUES (12, 4, 5, 56.00);
INSERT INTO score VALUES (13, 4, 6, 72.00);
INSERT INTO score VALUES (14, 5, 1, 81.00);
INSERT INTO score VALUES (15, 5, 5, 37.00);
INSERT INTO score VALUES (16, 6, 2, 56.00);
INSERT INTO score VALUES (17, 6, 4, 43.00);
INSERT INTO score VALUES (18, 6, 6, 79.00);
INSERT INTO score VALUES (19, 7, 2, 80.00);
INSERT INTO score VALUES (20, 7, 6, 92.00); 希望能对大家有所帮助
