MySQL必知必会（中）

一 . 数据库约束

• NOT NULL - 指示某列不能存储 NULL 值。
• UNIQUE - 保证某列的每行必须有唯一的值。
• DEFAULT - 规定没有给列赋值时的默认值。
• PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
• FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。

CREATE TABLE student (
id INT NOT NULL PRIMARY KEY,
sn INT UNIQUE,
name VARCHAR(20) DEFAULT 'unkown',
qq_mail VARCHAR(20)
)

对于整数类型的主键，常配搭自增长auto_increment来使用。插入数据对应字段不给值时，使用最大
值+1。这主要也是针对一台服务器，多个客户端的情况。

id INT PRIMARY KEY auto_increment,

对于外键约束，要注意：

• 1. 外键字段不能为该表的主键；
• 2. 外键字段参考字段必须得带有索引，才能被子表引用。（关于索引，在下一篇中讲到）

举一个例子：
创建学生表student，一个学生对应一个班级，一个班级对应多个学生。使用id为主键，classes_id为外键，关联班级表id。

CREATE TABLE student (
id INT PRIMARY KEY auto_increment,
sn INT UNIQUE,
name VARCHAR(20) DEFAULT 'unkown',
qq_mail VARCHAR(20),
classes_id int,
FOREIGN KEY (classes_id) REFERENCES classes(id)  外键约束
)

此处的设计保证了学生表中的classes_id（即班级）不会是不存在的班级。
由外键约束的表可理解为父子表，父子相互约束。
例如：创建这样一个班级表，并创建一个student表，其中student表中的classId与班级表中的classId建立外键

create table student(id int ,name varchar(20),classId int, foreign key(classId) references class(classId));

父表对子表的约束在于，不能在子表中插入父表关联键中没有的项，例如，父表classId中没有4，此时尝试在子表中插入，就报错了。

子表对父表的约束在于，父表中的元素不能随便删除了，此时子表中的元素如下：

执行以下操作：

delete from class where classId=1;

那如果真的想删除这条数据怎么做呢，答案是先删除子表中的相应数据，再删除父表中的数据。

二 . 表的设计

一对一

一对多

多对多

举一个多对多的例子：
例如学生期末考试成绩，既要与学生表联系，又要与课程表联系，而一个学生可以选多门课，一门课程可以被多个学生选择，即多对多。

CREATE TABLE score (
id INT PRIMARY KEY auto_increment,
score DECIMAL(3, 1),
student_id int,
course_id int,
FOREIGN KEY (student_id) REFERENCES student(id),
FOREIGN KEY (course_id) REFERENCES course(id)
)

三 . 复杂的查询

3.1 聚合查询

聚合函数

count（*）与count（列名）的区别在于，是否对null进行计数。

GROUP BY子句

SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足：使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函数中。
这是什么意思呢，举个栗子：

这里，显然是一个错误的输出，因为jack的Chinese是96分而不是86分，那为什么出错了呢；
因为name既不是“分组依据字段”，也不在聚合函数中，那将取每组中第一个出现的name展现，所以出现了错误匹配的情况。因此，select指定的字段不能包括非分组依据字段和不在聚合函数中的字段。

HAVING子句

GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用HAVING；此处需要注意：where在分组前过滤，可能会影响到分组，被过滤掉的数据不会出现在分组中。

3.2 联合查询

说到这里，那么select from后面可以跟多个表吗，答案是当然可以，这就是我们要说的联合查询。
如果直接执行select * from table1,table2;结果是一个笛卡尔积。
检索出来的列数将是两个表列数的总和，
检索出来的行数将是两个表行数的乘积。

显然，这样的结果并不是我们想要的，因此需要建立连接，来避免这种情况。

内连接

内连接即为等值连接，有以下两种写法。

select score.score from student (inner) join score on student.id=score.student_id;
select sco.score from student, score where student.id=score.student_id;

外连接

与内连接不同的是，外连接还会显示一些“不等值”的情况。
外连接分为左外连接和右外连接。

左外连接，表1完全显示
select 字段名 from 表名1 left (outer) join 表名2 on 连接条件;
右外连接，表2完全显示
select 字段名 from 表名1 right (outer) join 表名2 on 连接条件;

由于左表或右表完全显示，所以会有一些不等值的出现。那什么时候会用到外连接呢：
例如：检索所有用户，包含那些没有订单的用户

select customers.cust_id,orders.order_num
from customers left outer join orders
on customers.cust_id=orders.cust_id;

自连接

自连接是指在同一张表连接自身进行查询，通常搭配别名进行使用。
例如：检索‘产品1’的供应商生产的所有产品

select p1.prod_id,p1.prod_name
from products as p1,products as p2
where p1.vend_id =p2.vend_id
and p2.prod_id="产品1";

子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。
上面自连接的例子也可用子查询来完成：

select prod_id,prod_name
from products
where vend_id=(select vend_id
               from products
               where prod_id='产品1')；

还可与in关键字或exists关键字搭配使用。

合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION和UNION ALL时，前后查询的结果集中，字段需要一致。
区别是union自动去重，而union all不去重。

select * from course where id<3
union all
select * from course where name='英文';