交互设计师网站,视频制作软件app免费下载,小型旅游网站,宁波网站关键词排名提升文章目录 MySQL系列#xff1a;1.内连接2.外连接3.自连接4.子查询5.合并查询6.插入查询 MySQL系列#xff1a;
初识MySQL#xff0c;MySQL常用数据类型和表的操作#xff0c;增删改查(CRUD)操作(总),数据库约束数据库设计
#班级表
drop table if exists class;
create ta… 文章目录 MySQL系列1.内连接2.外连接3.自连接4.子查询5.合并查询6.插入查询 MySQL系列
初识MySQLMySQL常用数据类型和表的操作增删改查(CRUD)操作(总),数据库约束数据库设计
#班级表
drop table if exists class;
create table class(
id bigint primary key auto_increment,
name varchar(20)
);
#学生,课程与成绩的多对多关系
drop table if exists student;
create table student(
id bigint primary key auto_increment,
name varchar(20)not null,
sno varchar(10) not null,
age int default 18,
gender tinyint(1),
enroll_date date,
class_id bigint,
foreign key(class_id) references class(id)
);
#课程表
drop table if exists course;
create table course(
id bigint primary key auto_increment,
name varchar(20)
);
#成绩表
drop table if exists score;
create table score(
id bigint primary key auto_increment,
score float,
student_id bigint,
course_id bigint,
foreign key (student_id) references student(id),
foreign key (course_id) references course(id)
);# 课程表
insert into course (name) values (Java), (C), (MySQL), (操作系统), (计算机网络), (数据结构);
# 班级表
insert into class(name) values (Java001班), (C001班), (前端001班);
# 学生表
insert into student (name, sno, age, gender, enroll_date, class_id) values
(唐三藏, 100001, 18, 1, 1986-09-01, 1),
(孙悟空, 100002, 18, 1, 1986-09-01, 1),
(猪悟能, 100003, 18, 1, 1986-09-01, 1),
(沙悟净, 100004, 18, 1, 1986-09-01, 1),
(宋江, 200001, 18, 1, 2000-09-01, 2),
(武松, 200002, 18, 1, 2000-09-01, 2),
(李逹, 200003, 18, 1, 2000-09-01, 2),
(不想毕业, 200004, 18, 1, 2000-09-01, 2);# 成绩表
insert into score (score, student_id, course_id) values
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
(60, 2, 1),(59.5, 2, 5),
(33, 3, 1),(68, 3, 3),(99, 3, 5),
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
(81, 5, 1),(37, 5, 5),
(56, 6, 2),(43, 6, 4),(79, 6, 6),
(80, 7, 2),(92, 7, 6); 1.内连接
语法: 1 select 字段 from 表1 别名1, 表2 别名2 where 连接条件 and 其他条件; 2 select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 where 其他条件;
查询所有学生的考试成绩:
select s.name,c.name 课程,sc.score 分数 from score sc,student s,course cwhere c.idsc.course_idand s.idsc.student_id;select s.name,c.name as course,sc.score
from student s
join score sc on s.idsc.student_id
join course c on sc.course_idc.id;2.外连接
外连接分为左外连接、右外连接和全外连接三种类型MySQL不⽀持全外连接。 • 左外连接返回左表的所有记录和右表中匹配的记录。如果右表中没有匹配的记录则结果集中对 应字段会显⽰为NULL。 • 右外连接与左外连接相反返回右表的所有记录和左表中匹配的记录。如果左表中没有匹配的记 录则结果集中对应字段会显⽰为NULL。 • 全外连接结合了左外连接和右外连接的特点返回左右表中的所有记录。如果某⼀边表中没有匹 配的记录则结果集中对应字段会显⽰为NULL。
SELECT c.name AS course,COUNT(sc.student_id) AS student_count
FROM score sc
RIGHT JOIN course c ON sc.course_idc.id
GROUP BY c.name;SELECT s.name,COUNT(sc.course_id) AS course_count
FROM student s
LEFT JOIN score sc ON s.idsc.student_id
GROUP BY s.name;左外连接:
select s.name,sc.score from student s left join score sc on sc.student_ids.id;统计所有学生的选课情况含未选课学生 右连接不含未选课的学生
select s.name,sc.score from student s right join score sc on sc.student_ids.id;3.自连接
⾃连接是⾃⼰与⾃⼰取笛卡尔积可以把⾏转化成列在查询的时候可以使⽤where条件对结果进⾏过滤或者说实现⾏与⾏之间的⽐较。在做表连接时为表起不同的别名
再查询成绩表中JAVA成绩⽐MySQL成绩好的信息
select st.name,c.name 班级,s1.score MySQL成绩,s2.score JAVA成绩 from score s1,score s2,course c1,course c2,student st, class cwhere s1.student_ids2.student_idand s2.course_idc2.idand s1.course_idc1.idand c1.nameMySQLand c2.namejavaand st.ids1.student_idand st.class_idc.idand s1.scores2.score;4.子查询
单⾏⼦查询 查询唐三藏的同班同学 select * from student where class_id(select class_id from student s where s.name唐三藏);多行子查询: 查询MySQL或Java课程的成绩信息
select * from score where course_id in(select c.id from course c where c.nameMySQl or c.nameJAVA);多列子查询
insert into score(score,student_id,course_id) values(70.5,1,1),(98.5,1,3),(33,1,5);select * from score where(score,student_id,course_id)in(select score ,student_id ,course_id from score group by score,student_id,course_id having count(*)1);在from⼦句中使⽤⼦查询 select * from score sc1,(select avg(sc2.score)score from score sc2, student s,class c where sc2.student_ids.idand s.class_idc.idand c.namejava001班)temp where sc1.scoretemp.score;5.合并查询 create table student1 like student;insert into student1(name,sno,age,gender,enroll_date,class_id) values (张三,100008,18,1,2001-09-01,2),(孙悟空, 100002, 18, 1, 1986-09-01, 1),(李四,100009,18 ,1,2001-09-01,2),(王五,100010,18,1,2001-09-01,3);Union: 该操作符⽤于取得两个结果集的并集。当使⽤该操作符时会⾃动去掉结果集中的重复⾏。 Union all 该操作符⽤于取得两个结果集的并集。当使⽤该操作符时不会去掉结果集中的重复⾏
6.插入查询
语法:INSERT INTO table_name [(column [, column ...])] SELECT ..将student表中C001班的学⽣复制到student1表中:
insert into student1 (name,sno,age,gender,enroll_date,class_id) select s.name,s.sno, s.age,s.gender,s.enroll_date,s.class_id from student s,class c where s.class_idc.idand c.nameC001班;
