mysql 学习10 多表查询 -多表关系，多表查询

多表关系

一对多

多对多

创建学生表

#多对多表 学生选课系统create table student(id int primary key auto_increment comment '主键ID',name varchar(64) comment '姓名',studentnumber varchar(10) comment '学号'
)comment '学生表';insert into student(id,name,studentnumber)values (null,'张三','00000001'),(null,'李四','00000002'),(null,'王五','00000003'),(null,'杨六','00000004');

创建课程表

create table course(id int primary key  auto_increment comment '主键ID',name varchar(128) comment '课程名称'
) comment '课程表';insert into course (id,name)values (null,'java'),(null,'c'),(null,'c++'),(null,'高等代数'),(null,'离散数学'),(null,'mysql');

创建中间关系表

create table student_course(id int auto_increment primary key comment '主键ID',studentid int not null comment '学生ID',courseid int not null comment '课程ID',constraint fk_courseid foreign key (courseid) references course(id),constraint fk_studentid foreign key (studentid) references student(id));insert into student_course(id,studentid,courseid)values (null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);

查看关系

一对一

create table tb_user(id int auto_increment primary key comment '主键ID',name varchar(128) comment '姓名',age int comment  '年龄',gender char(1) comment '1:男, 2:女',phone char(11) comment '手机号')comment '用户基本信息表';create table tb_user_edu(id int auto_increment primary key comment '主键ID',degree varchar(20) comment '学历',major varchar(50) comment '专业',primaryschool varchar(50) comment '小学',middleschool varchar(50) comment '中学',universityschool varchar(50) comment '大学',userid int unique  comment '用户ID',constraint fk_userid foreign key(userid) references tb_user(id)
)comment '用户教育信息表';insert into tb_user(id,name,age,gender,phone)values (null,'黄渤',45,'1','18700000000'),(null,'冰冰',42,'2','18800000000'),(null,'张三',22,'1','18711000000'),(null,'李四',23,'2','18722000000'),(null,'王五',26,'1','18766000000');insert into tb_user_edu(id,degree,major,primaryschool,middleschool,universityschool,userid) values(null,'大专','计算机','青岛小学','青岛中学','中科院大学',1),(null,'本科','机械自动化','北京小学','北京中学','北京大学',2),(null,'研究生','社会科学','shagnhai小学','上海中学','上海大学',3),(null,'博士','半导体','成都小学','成都中学','成都大学',4),(null,'博士后','人文科学','西安小学','西安中学','西安大学',5);# 下面的会失败，是因为tb_user_edu create的时候，设置了foreign key 关联的是 tb_user 中的id，但是tb_user id没有6的值，因此失败
insert into tb_user_edu(id,degree,major,primaryschool,middleschool,universityschool,userid) values(null,'博士后','人文科学','西安1小学','西安2中学','西安3大学',6);

多表查询

先前数据，我们有两张表 dept 和 emphasdept

查看 dept 表 的创建语法。

show create table dept;

CREATE TABLE `dept` (`id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',`name` varchar(50) NOT NULL COMMENT '部门名称',PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='部门表'

查看dept表中的数据。

查看 emphasdept 表的创建语法

show create table emphasdept;

CREATE TABLE `emphasdept` (`id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',`name` varchar(50) NOT NULL COMMENT '姓名',`age` int DEFAULT NULL COMMENT '年龄',`job` varchar(20) DEFAULT NULL COMMENT '职位',`salary` int DEFAULT NULL COMMENT '薪资',`entrydate` date DEFAULT NULL COMMENT '入职时间',`managerid` int DEFAULT NULL COMMENT '直属领导ID',`dept_id` int DEFAULT NULL COMMENT '部门ID',PRIMARY KEY (`id`),KEY `fk_emp_dept_id` (`dept_id`),CONSTRAINT `fk_emp_dept_id` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='员工表'

查看emphasdept 表中的数据。

全部查询，笛卡尔积查询

#如果只是从两张表中查询，会有笛卡尔积 出现，在dept 中，有5行数据，emphasdept 中有6行数据，因此查询出来有 5*6 = 30行数据
#多表查询，多个表用 逗号  隔开
select * from dept,emphasdept ;

但这并不是我们想要的结果，例如 金庸 本身只是在 总经办，因此多表查询一般都要有where条件参与

select * from dept,emphasdept where dept.id = emphasdept.dept_id;