MySQL数据库CRUD语句快速入门(五)
-+----+------+--------------+
| 1 | 开发部 | 1 | 张三 | 1 |
| 2 | 销售部 | 1 | 张三 | 1 |
| 3 | 人事部 | 1 | 张三 | 1 |
| 1 | 开发部 | 2 | 李四 | 1 |
| 2 | 销售部 | 2 | 李四 | 1 |
| 3 | 人事部 | 2 | 李四 | 1 |
| 1 | 开发部 | 3 | 王五 | 2 |
| 2 | 销售部 | 3 | 王五 | 2 |
| 3 | 人事部 | 3 | 王五 | 2 |
| 1 | 开发部 | 4 | 赵六 | 3 |
| 2 | 销售部 | 4 | 赵六 | 3 |
| 3 | 人事部 | 4 | 赵六 | 3 |
| 1 | 开发部 | 5 | 田七 | 3 |
| 2 | 销售部 | 5 | 田七 | 3 |
| 3 | 人事部 | 5 | 田七 | 3 |
| 1 | 开发部 | 6 | 周八 | NULL |
| 2 | 销售部 | 6 | 周八 | NULL |
| 3 | 人事部 | 6 | 周八 | NULL |
+----+--------+----+------+--------------+
笛卡尔集 多张表的所有记录的排列组合
笛卡尔集当中有很多部匹配的数据(参照表外键列的值和被参照表主键的值不一致) 为了方便 以下称为废数据
www.2cto.com
加条件去掉废数据
select * from department,employee where employee.departmentid=department.id;
加条件做查询
select * from department,employee where employee.departmentid=department.id and department.name='开发部';
优化 给表加别名 只保留需要的数据
select e.* from department d,employee e where e.departmentid=d.id and d.name='开发部';
查出赵六在那个部门
select d.* from department d,employee e where e.departmentid=d.id and e.name='赵六';
2. 多对多
创建老师表
create table teacher
(
id int primary key auto_increment,
name varchar(20)
);
添加三个老师
insert into teacher(name) values ('老方'),('老余'),('老毕');
create table student
(
name varchar(20)
);
添加三个学生
insert into student(name) values('大毛'),('二毛'),('三毛'),('四毛'),('五毛'),('六毛'),('七毛'),('八毛'),('小毛');
www.2cto.com
创建中间表描述关系
create table tea_stu
(
teaid int,
stuid int,
primary key(teaid,stuid),
constraint teaid_FK foreign key(teaid) references teacher(id),
constraint stuid_FK foreign key(stuid) references student(id)
);
添加数据
insert into tea_stu (teaid,stuid) values (1,1),(1,2),(1,4),(1,5),(1,6),(1,8),(2,1),(2,3),(2,5),(2,7),(2,9),(3,2),(3,3),(3,4),
(3,5),(3,6),(3,7),(3,8);
查询2号老师教过的学生
select s.* from student s,tea_stu ts where ts.stuid=s.id and ts.teaid=2;
查询老余教过的所有的学生
select s.* from student s,tea_stu ts,teacher t where ts.stuid=s.id and ts.teaid=t.id and t.name='老余';
多表联合查询
查询的结果为笛卡尔集
www.2cto.com
n张表联合查需要n-1个条件来去掉废数据
去掉废数据的条件 参照表的外键列=被参照表的主键列
再加上查询条件即可得到结果
3. 一对一
create table person
(
id int primary key auto_increment,
name varchar(20)
);
create table idcard
(
id int primary key,
location varchar(20),
constraint personid_FK foreign key(id) references person(id)
);
www.2cto.com
insert into person (name) values('zhangsan');
insert into person (name) values('lisi');
insert into idcard (id,location) values(2,'天津');
insert into idcard (id,location) values(1,'上海');
查李四的身份证
select idcard.* from person,idcard where idcard.id=person.id and person.name='lisi';