mysql的连接与关联知识

待我称王封你为后i 2022-05-14 13:16 129阅读 0赞

内连接：

只要使用了join on就是内连接。查询效果与等值连接一样。

用法：表A\[inner\] join 表B on 关联条件

外连接：

在做多张表查询时，我们所需要的数据，除了满足关联条件的数据外，还有不满足关联条件的数据。此时需要使用外连接。会涉及到两个概念：

驱动表（主表）：

除了显示满足条件的数据，还需要显示不满足提哦阿剑的数据表

从表（副表）：只显示满足关联条件的数据表

外连接分三种：

左外连接：

表A left \[outer\] join 表B on 关联条件

表A是驱动表 表B是从表

\--练习：查询员工表的所有信息及其员工所在的部门的信息

select e.\*,d.\* from emp e left join dept d on e.deptno=d.deptno;

右外连接：

表A right \[outer\] join 表B on 关联条件

\--练习2.查询部门表的所有信息及其员工信息

select d.*,e.* from emp e left join dept d on e.deptno=d.deptno;

PS：mysql不支持全外连接

自连接：在多张表进行关联查询时，这些婊的表名是同一个，即自连。

\--练习3.查询员工的编号，姓名，及其领导编号和姓名

select e1.empno"员工编号",e1.ename"员工姓名" ,e2.empno"领导编号",
    e2.ename"员工姓名" from emp e1 join emp e2 on e1.empno = e2.mgr;

\--练习4.查询领导的姓名及下属的姓名

select e1.ename"领导姓名",e2.ename"员工姓名"from emp e1 join emp e2 on e1.empno = e2.mgr and mgr is not null;

新课第二部分

高级关联查询：

有时我们要查询的数据一个简单的查询语句满足不了，并且使用的数据，表中不能直观体现出，而是预先经过一次查询才有所体现。那么先执行的查询，我们称之为子查询。被子查询嵌入的查询语句称之为父查询。

比如需求：查询公子大于员工JONES工资的员工信息。

子查询返回的数据特点：

1.可能是单行单列的数据

2.可能是多行单列的数据

3.可能是单行多列的数据

4.可能是多行多列的数据

1.  子查询可以在where子句中
2.  子查询可以在from子句中
3.  子查询可以在having子句中
4.  子查询可以在select子句中，相当于外连接的另外一种写法。

\--练习1：查询工资大于员工JONES工资的员工信息

\--第一步：select sal from emp where ename='JONES';

\--第二步：select \* from emp where sal>();

\--合并：

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

\--练习2：查询员工表中工资大于10号部门的平均工资的员工姓名，职位，工资及部门号

select avg(ifnull(sal,0)) from emp where deptno=10;
    
    select ename,job,sal,deptno from emp where sal>(select avg(ifnull(sal,0)) from emp where deptno=10);

\---练习3：查询工资等于每个部门的的平均工资的人员信息

select * from emp where sal in (select avg(ifnull(sal,0))from emp group by deptno);

\--练习4：查询工资大于所有部门的平均工资的人员的信息

select * from emp where sal>all(select avg(ifnull(sal,0))from emp group by deptno);

\--练习5；查询工资大于任意部门的平均工资的人员信息

select * from emp where sal>any(select avg(ifnull(sal,0))from emp group by deptno);

\--练习6：将clerk和Scott奖金改成300，查询工资和奖金

\--查询工资与和奖金与员工Scott相同的员工信息，不显示Scott

select * from emp where sal=(select sal from emp where ename='scott')
    and comn =(select comn from emp where ename='scott')and ename!='scott';
    select * from emp where (sal,comn)=(select sal,comn from emp where ename='scott')and ename!='scott';

\--子查询在from子句中

\--练习1.查询每个员工的工资，姓名和其部门的平均工资

select deptno, avg((ifnull(sal,0))'avg' from emp group by deptno;

\--将上一步的查询语句看成一张表做关联查询

select sal"工资",ename"姓名",avg_sal from emp e join (select deptno,
    avg(ifnull(sal,0)) 'avg_sal' from emp group by deptno) m on e.depnto=m.deptno;

\--查询大于本部门平均工资的员工的信息

select sal,ename,avg_sal from emp join (select，deptno,
    
    avg(ifnull(sal,0))'avg_sal' from emp group by deptno) m on 
    emp.deptno=m.deptno and emp.sal>m.avg_sal order by m.deptno;

\--子查询在having子句中

\--练习1.查询部门平均工资大于30部门平均工资的部门的平均工资，工资之和。最大工资，最小工资，总人数

select avg(ifnull(sal,0)) from emp where deptno=30;
    
    select avg(ifnull(sal,0)),sum(sal),max(sal),min(sal),count(*)from emp group by
    
    deptno having avg(ifnull(sal,0))>(select avg(ifnull(sal,0)) from emp where deptno=30);

\--子查询在select子句中

\--练习1.查询每个员工姓名，工资，及其部门的平均工资，工资之和

select ename,sal,(select avg(ifnull(sal,0) from emp a where a.deptno=b.deptno)
     avg_sal,(select sum((sal) from emp c where c.deptno=b.deptno)
    sum_sal from emp b order by b.deptno;

新课第三部分

**约束分五种**：

主键约束：primary key

要求：作为主键的字段的字段值非空且唯一

\--练习1.创建表t\_01字段tid int 设置为主键约束，tname varchar(20)

create table t_01(tid int primary key,tname varchar(20));

\--插入数据：1001，'张三',

\-- 1002 '李四'

insert into t_01 values(1001,'张三');
    
    insert into t_01 values(1002,'李四');
    
    insert into t_01 values(null,'张三');

非空约束：not null

要求：有非空约束的字段不可以为null值

\--练习2：

create table t_02(tid int primary key,tname varchar(20) not null);
    
    insert into t_02 values(1001,'zhangsan');
    
    insert into t_02 values(1002,null);

唯一性约束:unique

要求：有唯一性约束的字段不可以重复，但是可以为null.

\--练习3：

create table t_03(tid int primary key,tname varchar(20) not null),idcard varchar(18) unique);
    
    insert into t_03 values(1001,'zhangsan',null);
    
    insert into t_03 values(1002,'zhangsan','123456789');
    
    insert into t_03 values(1003,'zhangsan','234567891');

检查性约束：check（条件）--check(gender in(‘f’,’m’)）

要求：在插入数据时必须符合字段的检查条件，但是可以为null

\--练习4：

create table t_04(tid int primary key,
    tname varchar(20) not null,idcard varchar(18) unique,gender enum('f','m'));
    
    insert into t_04 values (1001,'zhangsan','123456789','f');
    
    insert into t_04 values (1002,'zhangsan','223456789','m');
    
    insert into t_04 values (1003,'zhangsan','323456789','a');

外键约束：foreign key

要求：有外键约束的字段A必须依赖于另外一个字段B，字段B要有主键约束。

字段A的值，要么是null，要么必须是字段B里的值

\--练习5：

create table t_05(empno int primary key,ename varchar(20) not null,idcard varchar(18)
    
    unique,gender enum('f','m'),mgr int,foreign key(mgr) refernences t_05(empno);
    
    insert into t_05 values (1001,'zs','123456789','m',null);
    
    insert into t_05 values (1002,'ls','223456789','m',1001);

序列：作为主键的字段，通常不是表中的主要信息，可以用来当成信息记录的序号。序号最好是有序的序列。

Auto\_increment 关键字，用来对有主键约束的字段做自增操作。

用法：create table tname(

Tid int primary key auto\_incremnt,

...

);

函数：last\_insert\_id();

作用：获取序列最后一次的值

Select last\_insert\_id();

\--练习6：

create table t_06(tid int primary key auto_increment,tname varchar(20) not null);
    
    insert into t_06 values(1001,'zs');
    
    insert into t_06 values(null,'ls');
    
    insert into t_06 (tname) values('ww');
    
    insert into t_06 values(last_insert_id()+1,'zl');