看完就懂的连表查询

一、表与表之间的关系

1.1一对一

1.2一对多

一个班级可以有多个学生，像这样就叫一对多的关系

1.3多对多

一个老师可以带多个学生，一个学生可以被多个老师教，所以两张表的关系是多对多。

二、 连表查询

2.1概念

如果要查询的数据字段在两张表里，需要连表查询

2.2笛卡尔积

select * from 表名1 inner join 表名2;

如果学生表如下：

教师表如下：

如果像上面的写法不加条件就是用学生表的每一条数据去匹配老师表的数据，就是8*5=40，就会生成一个庞大的查询结果，叫笛卡尔积。
但是上面很多数据是无效的，所以为了避免出现笛卡尔积，我们要加上条件。

2.3内连接

select * from 表名1 inner join 表名2 on 条件;

查询结果如下：

我们也可以给表起别名，语法真实表名 as 别名

2.4外连接

2.4.1左外连接

select 字段 from 左表名 left outer join 右表名 on 条件;

左外连接是以左表为基准，左表的所有数据都会显示，对于右表匹配到的就显示匹配的数据，没有匹配的就显示null.
也可以把outer省略

2.4.2右外连接

select 字段 from 左表名 right outer join 右表名 on 条件;

右外连接是以右表为基准，右表的所有数据都会显示，对于左表匹配到的就显示匹配的数据，没有匹配的就显示null.

2.4.3全连接

我们可以这样实现全部查询所有数据显示结果
左外连接语句 UNION 右外连接语句;

select sname,age,tname from student left join teacher on student.tid=teacher.tid UNION select sname,age,tname from student right join teacher on student.tid=teacher.tid;

2.4.4navicat导入导成sql语句

我们把别人给的sql语句导入如下操作：

注意导入成功以后右键刷新一下表才会显示出来。

如果我们想把自己写的表导成sql文件，可以如下操作：

2.4.5练习

假设dept表如下：

假设员工表如下：

（1） 查询sales(销售部)的员工的具体信息（姓名 工资 奖金 部门）

（2）查询姓名最后一位是R的员工的姓名 工作 奖金和所在的部门

三、 子查询

3.1概念

一个查询语句的结果当成另一个查询的语句的范围或条件了。

3.2练习

3.2.1查询工资最高的员工所有信息

第一步：先查出最高工资

select max(sal) from emp;结果是5000

第二步：查询工资=5000的人的所有信息

select * from emp where sal=5000;

第三步：替换成子查询

select * from emp where sal=(select max(sal) from emp);

3.2.2查询工资比7654工资高和7788做相同工作的人的详细信息？

第一步：定出主框架

select * from emp where 工资>？ and job=?

第二步：先实现条件1

select sal from emp where empno=7654 结果是1250

第三步:实现条件2

select job from emp where empno=7788;结果是ANALYST

第四步:替换成子查询

select * from emp where sal>( select sal from emp where empno=7654) and job=( select job from emp where empno=7788);

最终结果如下：

五、练习

5.1查询工资比JONES员工高的信息

第一步:先查询JONES的工资

select sal from emp where ename='JONES'; 结果是2975

第二步：替换成子查询

select * from emp where sal>( select sal from emp where ename='JONES');

5.2计算每个部门的平均工资比30部门最高工资还高的平均工资

第一步：先写主框架

select  deptno, avg(sal)  from emp group by deptno having avg(sal)>?

第二步：求30号部门的最高工资

select max(sal) from emp where deptno=30; 结果是2850

第三步：替换成子查询

select  deptno, avg(sal)  from emp group by deptno having avg(sal)>(select max(sal) from emp where deptno=30);

5.3查询工资排名靠前的第6位到第10位

select sal from emp order by sal desc limit 5,5;

5.4查询和SCOTT从事相同工作的信息

第一步：先查询SCOTT从事的工作

select job from emp where ename='SCOTT';结果是化验员

第二步：替换成子查询

select * from emp where job=( select job from emp where ename='SCOTT');

5.5查询有奖金的员工信息

select * from emp where comm is not null;

5.6查询部门编号是20并且工资在2000和3000之间的员工信息

select * from emp where deptno=20 and sal between 2000 and 3000;

5.7查询出工作为MANAGER或者CLERK的员工的信息。

select * from emp where job=’MANAGER’ or job=’CLERK’;

或者

select * from emp where job in (‘MANAGER’,’CLERK’);

5.8查询出名字正数第二位为R的员工信息。

select * from emp where ename like ‘_R%’;

六、补充

mysql有两个存储引擎，一个是innoDB 和MYISAM,但是如果想设置外键必须存储引擎是innodb.
其实创建数据表完整格式：

create table xx(
	字段名 类型 字段修饰符,
	…..

)engine=innodb default charset utf8;