一个对inner jion ...on 的sql多表联合查询的练习
create database practiceSql; use practiceSql; -- create table student( `id` bigint not null auto_increment comment '主键id自增', `name` varchar(20) not null comment '学生名', `student_num` int not null comment '学号', `sex` varchar(4) not null comment '性别', `age` int not null comment '年龄', `college` varchar(50) not null comment'学院', primary key(id), key idx_name(name), key idx_student_num(student_num), key idx_college (college) )engine=InnoDB Auto_increment=1000 default charset=utf8 comment='学生表'; --插入10个学生信息 insert into student(name,student_num,sex,age,college) values ('张三','1','男','20','计算机学院'), ('李红','2','女','21','计算机学院'), ('黄山','3','男','20','美术学院'), ('李丽','4','女','22','美术学院'), ('何冲','5','男','20','计算机学院'), ('欧皇','6','男','21','英语学院'), ('马涛','7','女','19','英语学院'), ('廖彤','8','女','20','计算机学院'); --课程表 create table course ( `course_id` bigint not null auto_increment comment'课程id', `student_num` int not null comment'学号', `student_name` varchar(20) not null comment'姓名', `name` varchar(50) not null comment '课程名字', primary key(course_id), key idx_student_num(student_num), key idx_student_name(student_name) ) engine=innoDB auto_increment=100 default charset=utf8 comment='课程表'; --向课程表插入信息 insert into course(student_num,student_name,name) values (1,'张三','数学'), (2,'李红','英语'), (1,'张三','英语'), (1,'张三','数学'), (2,'李红','语文'), (1,'张三','数学'), (3,'黄山','语文'); --教师表 create table teacher( `teacher_id` bigint not null auto_increment comment'教师id', `name` varchar (20) not null comment'教师名字', `sex` varchar(4) not null comment '性别', `age` int not null comment '年龄', `course` varchar (50) not null comment '所教科目', primary key(teacher_id), key idx_name(name), key idx_course(course) ) engine=innoDB auto_increment=200 default charset=utf8 comment='教师表'; --设置主键自增 alter table teacher change teacher_id teacher_id bigint not null auto_increment; insert into teacher(name,sex,age,course) values ('Mr.Lee','男',40,'数学'), ('Mr.Hu','女',33,'英语'), ('Mr.Chen','男',38,'语文'); --查询张三的所有信息 课程名字,课程的老师 --这个不行 select distinct id , student.name as sname,student.student_num as snum,student.sex as ssex,student.age as sage,college ,course.name as cname,teacher.name as tname from student,teacher,course where student.name='张三'; --这个也不行 select distinct id , student.name as sname,student.student_num as snum,student.sex as ssex,student.age as sage,college ,course.name as cname,teacher.name as tname from student,teacher,course where course.student_name='张三'; --这个可以 select distinct id , s.name as sname,s.student_num as snum,s.sex as ssex,s.age as sage,college ,c.name as cname,t.name as tname from ((student s inner join course c on c.student_name = s.name ) inner join teacher t on t.course=c.name) where s.name ='张三'; --没有distinct select id , s.name as sname,s.student_num as snum,s.sex as ssex,s.age as sage,college ,c.name as cname,t.name as tname from ((student s inner join course c on c.student_name = s.name ) inner join teacher t on t.course=c.name) where s.name ='张三'; --查询张三的所有信息 课程名字, select distinct id , s.name as sname,s.student_num as snum,s.sex as ssex,s.age as sage,college ,c.name as cname from student s inner join course c on c.student_name = s.name where s.name ='张三'; --更新老师课程 update teacher set course = "数学" where course ='数学老师' ;