2.|2. 关系数据库--select查询 2.关系数据库--select查询

单表查询 MySQL [stu]> select * from student where sage > 18;

文章图片
MySQL [stu]> select * from student where sage > 18 and sdept = "IS";

文章图片
多表查询建立了四个表

文章图片

MySQL [stu2]> create table class( -> id int(11) not null auto_increment, -> name varchar(10) default null, -> primary key (id) -> )engine=innodb default charset=utf8mb4 -> ; MySQL [stu2]> create table teacher( -> id int(11) not null auto_increment, -> name varchar(10) default null, -> age int(3) default null, -> phone char(11) default null, -> primary key (id) -> ); MySQL [stu2]> create table student( -> id int auto_increment primary key, -> name varchar(10), -> age int(3), -> class_id int, -> foreign key(class_id) references class(id) -> ); MySQL [stu2]> create table class2teacher( -> id int auto_increment primary key, -> class_id int, -> teacher_id int, -> foreign key(class_id) references class(id), -> foreign key(teacher_id) references teacher(id) -> ); MySQL [stu2]> insert into class(name) values -> ("云计算1810"), -> ("云计算1901"), -> ("云计算1902"); MySQL [stu2]> insert into teacher(name,age,phone) values -> ("奇哥", 18, "13733878989"), -> ("强哥", 28, "15633878989"), -> ("磊哥", 30, "13933878989"), -> ("闫老师", 18, "13633878989"); MySQL [stu2]> insert into student(name,age,class_id) values -> ("黛玉", 18, 3), ("钦文", 19, 3),("马邦德", 30, 1), -> ("九筒", 48, 1),("六子", 36, 2),("汤师爷", 18, 2), -> ("麻匪", 18,2), -> ("黛玉", 18,2); MySQL [stu2]> insert into class2teacher(class_id,teacher_id) values -> (1,1),(1,2),(2,1),(2,2),(2,3),(3,1),(3,3);

文章图片

查询每个班级的所有学员的姓名

MySQL [stu2]> select class.name,student.name -> from class,student -> where class.id=student.class_id;

文章图片

查询学生马邦德的班级名

MySQL [stu2]> select class.name,student.name ->from student,class ->where student.name="马邦德" ->and student.class_id=class.id;

文章图片
【2.|2. 关系数据库--select查询】3.查询磊哥教的学生

MySQL [stu2]> select teacher.name,class.name,student.name ->from teacher,class,class2teacher,student ->where teacher.name="磊哥" and ->teacher.id=class2teacher.teacher_id and ->class2teacher.class_id = class.id and ->class.id=student.class_id;

文章图片

查询黛玉的老师（注意，有两个黛玉）

MySQL [stu2]> select student.id,student.name,teacher.name ->from student,teacher,class2teacher ->where student.name="黛玉" and ->student.class_id=class2teacher.class_id and ->class2teacher.teacher_id=teacher.id ;

文章图片

查询云计算1901班的所有老师

MySQL [stu2]> select teacher.name,class.name -> from teacher,class,class2teacher -> where class.name="云计算1901" and ->class.id=class2teacher.class_id and ->class2teacher.teacher_id=teacher.id;

文章图片

查询马邦德班的所有学生

MySQL [stu2]> select A.name ->from student A ->where A.class_id = ->( ->select B.class_id ->from student B ->where B.name="马邦德" ->);

文章图片

查询奇哥教的所有班级（join）

MySQL [stu2]> select teacher.name,class.name from teacher join class join class2teacher on teacher.id = class2teacher.teacher_id and class2teacher.class_id=class.id where teacher.name="奇哥";