《MySQL数据库》之"浅尝辄止"（六）关联查询、约束条件和自增长序列

一、关联查询

1、概念

在查询数据时，所需要的数据不只在一张表中，可能在两张或多张表中。这个时候，需要同时操作这些表来查询数据，即关联查询。

关联查询所涉及到的表与表之间都会存在有关联的字段，如员工表的部门编号和部门表的部门编号。

2、笛卡尔积

在做关联查询时，数据库会使用某一张表中的每一条记录都与另外一张表的所有记录进行组合。比如表A有x条记录，表B有y条记录，最终组合数为x*y，这个值就是笛卡尔积，通常没有意义。

3、等值连接

在做关联查询时，这些表中存在着有关联的两个字段。我们使用某一张表中的一条记录通过相关联的字段与另外一张表的记录进行匹配，组合成一条新的记录。使用"="连接关联字段

需求1:查询员工的姓名，职位及其所在部门的名称 、地址

4、内连接

内连接返回所有满足条件的记录，关键字join  on。查询效果与等值连接一样。
用法：表A   [inner]   join  表B  on  关联条件

5、外连接

在做关联查询时，我们所需要的数据，除了那些满足关联条件的数据外，还有不满足关联条件的数据。此时需要使用外连接。

会涉及到两个概念：

驱动表(主表)：除了显示满足条件的数据，还需要显示不满足条件的数据的表
从表(副表)：只显示满足关联条件的数据的表

mysql外连接只支持左外连接，右外连接，不支持全外连接

左外连接:
            表A left [outer] join 表B  on 关联条件。 
            表A是驱动表，表B是从表
右外连接
            表A right [outer] join 表B  on 关联条件
            表B是驱动表，表A是从表
全外连接：
            两张表的数据不管满不满足条件，都做显示。
            表A  full [outer] join 表B on 关联条件
PS:mysql 不支持全外连接

需求1：查询所有员工的姓名，职位，及其部门编号，部门名称----分析：员工表为驱动表，部门表为从表

select e.ename,e.job,e.deptno,d.dname from emp e left join dept d on e.deptno = d.deptno;

6、自连接

自连接是一种特殊的关联查询。数据的来源是同一个表，这样的表内的多个字段要存有关系。我们要使用表别名来虚拟出两个表。

需求1：查询员工姓名，职位及其上司姓名，职位。

mysql >  select a.ename 员工姓名,a.job 员工职位,b.ename 上司姓名,b.job 上司职位 from emp a join emp b on a.mgr=b.empno;

      分析：可以看出 a的mgr与b的empno关联，所以，a是员工表，b是领导表

二、高级关联查询

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

主查询可以是select语句，也可以是DML语句或者是DDL语句。

根据子查询返回结果的不同，可以分为单行单列子查询、多行单列子查询、多行多列子查询。

子查询所在的位置，有可能出现在以下地方：

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

1、在where子句中

需求1：查询和员工姓名scott同职位的员工信息。

select ename,job,hiredate,sal,deptno from emp where job=(select job from emp where ename='scott');

需求2：查询薪水比所有员工的平均薪水高的员工信息

mysql > select ename,job,hiredate,sal from emp where sal > (select avg(ifnull(sal,0)) from emp);

需求3：查询出部门中有salesman但是职位不是salesman的员工信息

select ename,job,hiredate,sal,deptno from emp where deptno in (select distinct deptno from emp where job='salesman') and job <> 'salesman';

exists 关键字

有时候，子查询需要引用主查询的字段数据，我们使用exists关键字。exists后面的子查询至少返回一条记录，则整个条件为true;

需求：查询有员工的部门信息

mysql > select deptno,dname,loc from dept d where exists (select * from emp e where d.deptno =e.deptno);

2、在from子句中

from子句用于指定表，如果想在一个子查询的结果里继续查询，则子查询需要写在from子句中，相当于一个表。

需求1：查询工资大于本部门平均工资的员工的信息。

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

需求2：查询每个员工的工资，姓名和其部门的平均工资。

select e.ename, e.sal, t.avg_sal from emp e , (select deptno,avg(ifnull(sal,0)) 'avg_sal' from emp group by deptno) t where e.deptno = t.deptno order by t.deptno;

3、在having子句中

需求：查询平均工资大于30号部门平均工资的部门号、平均工资

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

4、在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;

三、约束（constraint）

约束就是在表上强制执行的一种校验规则，当执行DML操作时，数据必须符合这些规则，如果不符合，将无法执行。

约束的全称：约束条件，也称之为完整性约束条件。可以保证表中数据的完整性，保证数据之间的商业逻辑。

约束条件包括：

1）非空约束 （not null）,简称NN
2）唯一性约束（unique）,简称UK
3）主键约束（primary）,简称PK
4）外键约束（foreign key）,简称FK
5）检查约束（check）,简称CK                                                                                                                                                  

1、非空约束 （not null）

非空约束用于确保字段值不为null。默认情况下任何列都允许null值，但是业务逻辑可以会要求某些列不能去null值，因此使用非空约束。

建表时设置:

create table tName(
colName1 Type  not null,
....
);

建表后设置非空约束(ps:前提表中相应字段不能有null值)           

alter table tableName modify colName type not null;       

 取消非空约束

alter table tableName modify colName type null;

2、唯一性约束（unique）

用于保证字段或者字段的组合不出现重复值，但是允许为null值

建表时设置

create table tableName(
colName1 Type1 unique,
........ 
);

建表后设置

alter table tableName modify colName Type unique;
alter table tableName add constraint  constraintName  unique(colName);

查看表中的唯一性约束名称

show keys from tableName;

删除唯一性约束

drop index uniqueName on tableName;

3、主键约束（primary）

主键约束在功能上讲，相当于非空约束和唯一性约束的组合。主键字段可以是单字段也可以是字段组合，即在主键约束下的字段不允许有null值，也不允许出现重复值。主键可以用来在表中唯一的确定一行记录。一个表中只允许建立一个主键，其他约束条件没有个数限制。

主键选取的原则

1）主键应是对系统无意义的数据，如序号
2）永远也不要更新主键，让主键除了用于唯一标识一行记录之外，再无其他用途
3）主键不应包含动态变化的数据，如时间戳
4）主键应自动生成，不要人为干预，以免使它带有除了唯一标识一行以外的意义
5）主键尽量建立在单列上

建表时创建

create table tableName(
colName1 Type1  primary key,
........
);

建表后创建

alter table tableName modify colName Type primary key;
alter table tableName add constraint constraintName primary key(colName);

删除主键

alter table tableName drop primary key;

 

4、外键约束（foreign key）

外键约束条件定义在两张表的字段或一张表的两个字段上，用于保证相关两个字段的关系。即字段A如果设置外键约束，那么字段A的值
必须要依赖于字段B里已经存在的值，但是可以为null。而字段B要求是表中的主键约束。

字段A所在的表我们称之为从表，字段B所在的表称之为主表。如果主表中的一个值被从表依赖时，此时主表的该记录不允许被删除。

外键约束会降低数据库的性能：

如果在设置外键约束的表上频繁的进行DML操作，那么每次DML操作都会检查主表，产生额外的开销。再一个就是，外键约束会确定主从表的先后生成顺序，有时会影响业务逻辑。因此，外键约束要看需求而定，要慎用。

建表时设置

create table tableName(
empno int primary key,
ename varchar(20) not null,
mgr int,
constraint fk_name foreign key(mgr) references tableName(empno)
);-------------------mgr是字段A,empno是字段B,字段B必须有主键约束

建表后设置

alter table tableName1 add constraint FK_name foreign key(字段A)  references  tableName2(字段B)；

删除外键约束

alter table tableName drop foreign key fk_name

5、检查约束（check）

检查约束条件用来强制在字段上的每个值都要满足检查约束的条件。

mysql的检查约束注意事项：

1） 写法：check(条件) 。mysql在语法上通过，但是在约束条件上没有效果。其他数据库如oracle有效
2） 如果是集合性质的条件，如 gender 必须('f','m')中的某一个值，可以使用枚举来替代检查约束
      写法：enum('f','m')

建表时设置：

create table tableName(
name varchar(20) not null,
age int check(age >0 and age <20),---语法通过，但是约束条件无效
gender enum('f','m')---------集合性质的写法，此法有效
);

建表后设置：

alter table tableName add CONSTRAINT enum_age check(age>18);---其他数据库约束条件有效，mysql无效
alter table tableName modify gender enum('f','m','n');--mysql有效

四、mysql的自增长"序列" 

1、序列的概念

序列（sequence）是一种用来生成唯一数字值的数据库对象。序列的值通常是按递增或递减顺序自动生成，用于自动产生表中主键的值，是一种高效的获取唯一键值的途径。通常为主键服务，是一组有序的整数值，如1,2,3,4,5,.......................

2.mysql特点

mysql不支持序列机制，但是mysql的auto_increment可以达到与序列机制一样的效果。我们称之为自增长序列。

1）auto_increment 关键字，用来对有主键约束的字段做自增操作。
2）自增长序列默认从1开始
3）自增长序列的步数为1
4）可以设置起始数字

 用法如下:

 建表时设置1：

create table tname(
tid int primary key auto_increment,
 ......
 );

建表时设置1：   

create table tname(
 tid int primary key auto_increment
) auto_increment=100;

建表后设置：
alter table tableName auto_increment=100;

取消自增长：

alter table tablenName change colName colName Type unsigned not null;
      

3、函数:last_insert_id();

        作用：获取序列最后一次的值。
        select last_insert_id();

---------------------------------------------------------------------未完待续-----------------------------------------------------------------------------------------------