Mysql:sql语句（3）

上篇文章我们着重讨论了对于单表的操作，这次，我们将要讨论面临多表的情况，多个表之间可以有什么关系，当我们查询时遇到多个表应该如何操作等等，那么就让我们开始吧。

数据库约束

有时候，数据库中的数据，是有一定要求的，有些数据是合法数据，有些是非法数据，我们可以通过mysql提供的约束来对这些数据进行编辑。

MySQL提供以下约束：

not null 表示某列不能存储null值

unique 表示某列的每行必须有唯一的值

default 规定没有给列赋值时的默认值

primary key 主键，可以理解为not null 和 unique 的结合。确保某列有唯一标识，有助于更容易更快速地找到表中一个特定的记录。

foreign key 外键，保证一个表中的数据匹配另一个表中的值的参照完整性。

check 保证列中的所有值符合指定条件。

主键

一张表里只能有一个主键（primary key），也就是只能有一个作为身份标识的数据。虽然只能有一个主键，但主键不一定只能是一列，主键也可以由多个列一起构成。那么我们可以在创建表时指定主键：

creat table student(

id int primary key,

class

);

这样我们就成功的设置了主键。

为保证主键唯一，mysql提供了一种自增主键的机制，主键经常使用int。插入数据时，我们可以不必指定主键，而由数据库主动分配一个主键，会从1开始，依次递增分配主键的值，我们可以这样操作：

creat table student(

id int primary key auto_increament,

class

);

此时id这一列会交给mysql自动分配，插入时null就可以了。

当然，这种方法有一定局限性，如果是分布式系统，有多个mysql服务器构成的集群，结果可能就不尽如人意了。

外键

外键（foreign key）描述了两个表之间的关联关系。

我们先观察下面两个表

一个是班级表，一个是学生表学生的班级应该都是第一个班级表里的，所以下面这个学生表受上面这个班级表的约束。

（class int,foreign key(class)）references class(id);

当我们在子表中执行插入操作时，会查询class表，如果将要修改的值已经被继承，那么修改将会失败。

进阶操作

增加：增加搭配查询使用

当我们增加数据时，我们可以把查询语句的查询结果，作为插入的数值。

举个例子，现在我们创建了一个新表student2,我们希望实现对student表的复制，那么再重新一个一个插入显然很麻烦，我们可以对student表进行全列查询，将结果作为数据填充至student2:

insert into student2 select* from student;

我们可以看到，我们成功的复制了出来，并且更加简便，更加方便我们操作。

查询：各种进阶查询

1.聚合查询

聚合查询，相当于行与行之间进行运算，我们可以通过聚合函数完成上述行之间的运算。

我们先介绍第一个聚合函数：count()

这个函数可以帮助我们返回查询到的数据的数量，简单来说就是返回查询的记录有几行。例如我们想统计目前学校有几个人，我们就可以理解为想查询student表中有几行记录，我们可以这样：

 select count(*) from student;

我们来深度讨论一下这句语句。首先，我们要先执行select * ,进行一个全列查询，之后针对全列查询查询到的结果，进行count()这个函数的操作，即对结果集合进行统计，看看具体有几行，并将查询到的结果返回。

count(*)得到的结果还可以参与各种算术运算，也可以搭配其他sql使用。

由此我们能得到，聚合函数可以实现行与行之间的运算，下面我们再介绍几个常用的聚合函数。

sum()

把这一列的若干行，进行求和，只针对数字类型使用，属于算术运算。

select sum(age) from student;

如上，我们算出了所有学生的年龄总和。（虽然没什么意义）

avg()

返回查询到的数据的平均值。

select avg(age) from student;

像这样我们可以算出学生的平均年龄，更加方便。

max()/min()

返回最大/最小值

这个应该很好理解，我们直接演示。

2.分组查询

下面我们来讨论分组查询。我们先来看一个表：（为了简便，我没有一个一个起名字，谅解一下）

这是一张关于学校职工的表，它显示了每个人的职位，有校长、教师、清洁工三类，并记录了他们每个人的工资。现在我们想统计一下平均工资，显然，将校长和老师的工资一起统计是没有意义的，他们的工资差别很大。我们最好的方法是将他们所有人按照职位分组，统计出校长、教师、清洁工每个职位的平均工资。

这里我们就要用到我们进行分组的语法，我们将要使用group by关键字，我们可以这样操作：

select role, avg(salary) from worker group by role;

我们可以发现，当我们使用group by函数时，它会将role这一列里相同的统计为一组，并进行类似于avg()这样的运算，这样就可以满足我们的要求。我们使用分组查询往往要搭配聚合查询（即聚合函数）进行计算，不然可能没有意义。

使用group by 可以搭配条件，需要区分是分组前的条件，还是分组后的条件。

分组前：用where写在group by 前面

分组后：用having写在group by后面

我们也可以同时使用，完成两类条件的筛选。

3.联合查询/多表查询

下面就进行我们的多表查询，我个人认为，难点在于进行对于两张或多张表的链接。下面，我们先了解一下表的笛卡尔积。我们先来看下面两张表，一张记录了学生的信息，一张记录了寝室的信息：

那么当我们什么都不加，直接暴力的链接两张表进行全列查询时，得到的结果就是两张表的笛卡尔积。

我们可以看到，两张表的每一行都进行了一个新的排列组合，student表的每一行与dorm表的每一行都进行了一个结合，这就是所谓的笛卡尔积。

显然，这并不是我们想要的结果，我们需要添加链接条件。我们发现，student记录了寝室信息，与dorm表是相对应的，那我们就可以进一步加入连接条件：

select * from student,dorm where student.dormno=dorm.dormno;

这样我们就添加了链接条件，得到了我们想用的表。student.dormno代表student表中dormno一列，而dorm.dormno代表dorm表中dormno一列。这就保证了当两个表的某一列列名相同时容易产生歧义和错误。

之后，我们就可以进行查询了，比如我们要查询张扬同学的信息，我们就添加条件，用and连接：

select * from student,dorm where student.dormno=dorm.dormno and sname="张扬";

最后，我们就可以对列进行精简，只保留我们想要的列。比如我们只是要查询联系电话和年龄：

这里我们建议查询时，保留的列也带上表名，这样不容易混淆，更加清楚。

以上就是我们多表查询的一般步骤，我们来总结一下：

（1）对多表进行笛卡尔积

（2）添加连接条件

（3）根据需求添加条件进行筛选

（4）针对列进行精简，只保留有需求的列

4.联合查询/自连接

自连接只需要用到一张表，一张表自己和自己笛卡尔积。

有时候我们需要进行行和行之间的比较，这时候我们可以使用自连接。

比如我们现在将要将dorm表进行自连接，我们可以这么操作：

select* from dorm as d1,dorm as d2;

这样可以将一张表自己与自己进行链接。需要注意，我们在连接时要将表用as起两个别名，不然sql会不知道你在干啥，链接不起来。

小结

到这里我们对数据的增删查改就全部讨论完了，这些都是对数据最基本的操作，大家务必掌握。