表之间的关系

原创

qq5e86804b406ce 2022-10-28 10:03:43 ©著作权

文章标签 数据外键等值连接 文章分类 运维

©著作权归作者所有：来自51CTO博客作者qq5e86804b406ce的原创作品，请联系作者获取转载授权，否则将追究法律责任

表之间的关系

一对一：少用。
一对多：上面使用的外键就是一对多的关系。例如：一个人可以拥有多辆汽车。创建两张表，1：create table person （id int primary key auto_increment，name varchar(50);2:create table car （name varchar（20），color varchar（20），personid int，constraint c_p_fk foreing key（personid） references person（id））；
多对多：我们是通过创建第三张表来实现多对多的。例如：一个学生可以有多个老师，一个老师也可以有多个学生。
一：创建老师表：create table teacher （teacherid int primary key，name varchar（50）；
二：创建学生表：create table student （studentid int primary key，name varchar（50）；
三：创建学生和老师的关系表：create table tea_stu（teacherid int，studentid int）；
四：添加外键：alter table tea_stuadd constraint foreing key（teacherid ）references teacher（teacherid））；alter table tea_stuadd constraint foreing key（studentid）references student（studentid））；
问题：为什么要创建多个表呢？避免大量冗余数据的出现，一个学生成绩有多个，但是学生的其他信息就会显得多余了，而且拆分表查询也快。

多表查询

（查询不需要强制表和表之间建立主外键，只是将数据查出来）

合并结果集：把两个select语句查询的结果合并在一起。
两种方式：union ：合并时去掉重复的记录
union all ：合并时不去掉重复的结果集。
示例：create table a（name varchar（50），score int）；
create table b（name varchar（50），score int）；
insert into a（‘a’，10），（‘b’，20），（‘c’，30）；
insert into b（‘a’，10），（‘b’，20），（‘d’，40）；
union：select * from a union select * from b；
union all：select * from a union all select * from b；
注意：被合并的两个结果列数，列类型必须相同。
连接查询：跨表查询，需要关联多个表进行查询。
一：了解笛卡尔集：就是两个或者多个集合各个元素的排序。例如：集合A（a，b），集合B（0，1，2），两个集合的笛卡尔集为：(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)。这个可以宽展到多个集合的情况，同时查询两张表的时候就会出现笛卡尔集结果。例如：select * from student，teacher；
二：查询时给表起别名的方法：select * from teacher t，student s；就是给表teacher起了别名t，student起别名s；
三：多表查询时保证数据正确：在查询时要把主键和外键保持一致。例如：select * from student st，score sc where st.id=sc.sid;这样就将两张表的记录显示在一起了。原理：是从笛卡尔集里面逐行判断，取出符合where后面条件的记录。//这样叫99连接法
四：
连接查询根据连接方式分类：内连接，外连接，自然连接
内连接：等值连接，多表连接，非等值连接，自连接
等值连接：其实和上面的99查询是一样的，只是写法不一样。例如：select * from stu st inner join score sc on st.id =sc.sid;//这里面的inner可以省略。on后面只写主外键，如果还有条件直接在后面写where，若还有条件的话再加上and。例如：select st.name,sc.score,sc.km from stu st inner join score sc on st.id =sc.sid where score>=70 and st.gender=‘男’;
多表连接：
先建立表：

表之间的关系_外键

使用99连接法：select st.name,sc.score,c.name from stu st,score sc,course c where st.id =sc.sid and sc.cid=c.cid;

内连接写法：select st.name,sc.score,c.name from stu st jion score sc on st.id= sc.sid jion course c on sc.cid=c.cid;

非等值连接：

创建表：

表之间的关系_等值连接_02

创建语句：

表一：create table emp(empno int(11) not null,ename varchar(255) default null,job varchar(255) default null，mgr varchar(255) default null,hiredata data default null，salary decimal(10,0) default null,comm double default null,deptno int(11) default null,primary key (empno)) enging =InnoDB default charset=utf8;

插入数据：

INSERT INTO emp VALUES (7369, ‘孙悟空’, ‘职员’, ‘7902’, ‘2010-12-17’, 800, NULL, 20);

INSERT INTO emp VALUES (7499, ‘孙尚香’, ‘销售人员’, ‘7698’, ‘2011-2-20’, 1600, 300, 30);

INSERT INTO emp VALUES (7521, ‘李白’, ‘销售人员’, ‘7698’, ‘2011-2-22’, 1250, 500, 30);

INSERT INTO emp VALUES (7566, ‘程咬金’, ‘经理’, ‘7839’, ‘2011-4-2’, 2975, NULL, 20);

INSERT INTO emp VALUES (7654, ‘妲己’, ‘销售人员’, ‘7698’, ‘2011-9-28’, 1250, 1400, 30);

INSERT INTO emp VALUES (7698, ‘兰陵王’, ‘经理’, ‘7839’, ‘2011-5-1’, 2854, NULL, 30);

INSERT INTO emp VALUES (7782, ‘虞姬’, ‘经理’, ‘7839’, ‘2011-6-9’, 2450, NULL, 10);

INSERT INTO emp VALUES (7788, ‘项羽’, ‘检查员’, ‘7566’, ‘2017-4-19’, 3000, NULL, 20);

INSERT INTO emp VALUES (7839, ‘张飞’, ‘总裁’, NULL, ‘2010-6-12’, 5000, NULL, 10);

INSERT INTO emp VALUES (7844, ‘蔡文姬’, ‘销售人员’, ‘7698’, ‘2011-9-8’, 1500, 0, 30);

INSERT INTO emp VALUES (7876, ‘阿珂’, ‘职员’, ‘7788’, ‘2017-5-23’, 1100, NULL, 20);

INSERT INTO emp VALUES (7900, ‘刘备’, ‘职员’, ‘7698’, ‘2011-12-3’, 950, NULL, 30);

INSERT INTO emp VALUES (7902, ‘诸葛亮’, ‘检查员’, ‘7566’, ‘2011-12-3’, 3000, NULL, 20);

INSERT INTO emp VALUES (7934, ‘鲁班’, ‘职员’, ‘7782’, ‘2012-1-23’, 1300, NULL, 10);

表二：

CREATE TABLE salgrade (

grade bigint(11) NOT NULL AUTO_INCREMENT COMMENT ‘工资等级’,

lowSalary int(11) DEFAULT NULL COMMENT ‘此等级的最低工资’,

highSalary int(11) DEFAULT NULL COMMENT ‘此等级的最高工资’,

PRIMARY KEY (grade)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO salgrade VALUES (1, 700, 1200);

INSERT INTO salgrade VALUES (2, 1201, 1400);

INSERT INTO salgrade VALUES (3, 1401, 2000);

INSERT INTO salgrade VALUES (4, 2001, 3000);

INSERT INTO salgrade VALUES (5, 3001, 9999);

表三：

CREATE TABLE salgrade (

grade bigint(11) NOT NULL AUTO_INCREMENT COMMENT ‘工资等级’,

lowSalary int(11) DEFAULT NULL COMMENT ‘此等级的最低工资’,

highSalary int(11) DEFAULT NULL COMMENT ‘此等级的最高工资’,

PRIMARY KEY (grade)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO salgrade VALUES (1, 700, 1200);

INSERT INTO salgrade VALUES (2, 1201, 1400);

INSERT INTO salgrade VALUES (3, 1401, 2000);

INSERT INTO salgrade VALUES (4, 2001, 3000);

INSERT INTO salgrade VALUES (5, 3001, 9999);

查询：查询所有员工的姓名，工资，所在部门的名称以及工资的等级

方式一：select e.ename,e.salary,d.dname,g,grade from emp e,dept d,salgrade g where e.deptno=d.deptno and e.salary>=g.lowSalary and e.salary<=g.highSalary;

方式二：select e.ename,e.salary,d.dname,g,grade from emp e,dept d,salgrade g where e.deptno=d.deptno and e.salary between g.lowSalary and g.highSalary;

方式三：select e.ename,e.salary,d.dname,g.grade from emp e join dept d on e.deptno=d.deptno join salgrade g on e.salary between g.lowSalary and g.highSalary;

外连接：左外连接（左连接），右外连接（右连接）

左连接：两表满足条件相同的数据查出来，如果左边表当中有不相同的数据，也把左边表当中的数据查出来，所以左边的表的数据全部都查出来了。

格式：select * from stu st left outer join score sc on st.id = sc.id；这里的outer可以省略

右连接：右边表当中的所有数据都查出来，左边表只查出满足条件的；

格式：select * from stu st right outer join score sc on st.id = sc.id；这里的outer可以省略

自然连接
连接查询会产生无用的笛卡尔集,我们通常使用主外键关系来去除多余的的,而自然连接不需要你给出主外键等式,它会找到两张表字段名和字段类型相同的列进行匹配地查询,两个字段名相同的在查询结果只显示成一列,但是它的需求是两张连接的表中列名称和类型完全一致作为条件去除相同的列,就是说如果有两列的话,两列的记录要一样才能显示.
格式:select * from stu natural join score;

子查询:一个select语句中包含另外一个完整的select语句,或者有两个以上select就是子查询了,子查询出现的位置:1. 在where后把select查询出的结果当作另一个select的条件值 2: from后,把查询出的结果当作一个新表
示例:(利用的数据是上面的表)
1:查询与项羽同一个部门人员工
select ename,deptno from emp where deptno =(select deptno from emp where ename = ‘项羽’);
2:查询部门编号为30,而且薪水大于2000的人
select ename,salary from (select ename,salary,deptno from emp where depton=30) s where s.salary>2000;
3:工资高于30号部门所有人的员工信息
1.先查出30号部门工资最高的那个人2.再到整个表中查询大于30号部门工资最高的那个人
select ename,salary from emp where salary >(select max(salary) from emp where deptno=30);
4:查询工作和工资与妲己完全相同的员工信息
方法一:select * from emp e,(select job,salary from emp where ename=‘妲己’) r where e.job =r.job and e.salary =r.salary;
方法二:select * from emp where (job,salary) in (select job,salary from emp where ename =‘妲己’);
5:有2个以上直接下属的员工信息
select * from emp where empno in(select mgr from emp group by mgr having count(mgr)>=2);
自连接:
从需求出发认识方法:
需求:求7369员工编号、姓名、经理编号和经理姓名
这样我们无法在一行显示所有需要查询的信息.就需要使用自连接
自连接就是自己连接自己,一表多用的意思,给自己起个别名来自己连接自己
例如:select e1.empno,e1.ename,e2.empno,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno and e1.empno=7369;