SQL More

前言

前面我们学习玩了JDBC中基本上全部的内容，但是还没有说过SQL中的很多的基本的知识。刚好现在也在学习SQL这门课，那么就稍微的来学一下SQL中的几个基本的功能吧。

约束

SQL中总共有四种不同的约束。他们分别是主键约束，唯一约束，非空约束，外键约束。其中前面的三个约束都是非常简单容易理解的，就是第四种约束其实是有点不好说的。不过还是一一说起吧。

主键约束

所谓的主键是什么东西其实是不用多说了的，很简单的概括就是非空的唯一的字段。

create table tem(id int primary key, name varchar(10));

在类型的后面加上primary key关键词表示这个字段将是作为主键来使用。

不过有时候我们发现主键设置错了，那么可以使用如下的命令删除之前设置的主键。

alter table tem drop primary key;

不过也有点时候我们也想在创建表之后添加主键。

alter table tem add primary key (id);

需要注意的是，后面的那个东西是需要加上()的，为什么呢？其实我也不是很清楚，因为不加上括号我发现竟然报错了。不过，其实我们知道主键并不是只能有一个字段，主键也可以有多个字段。此时这多个字段是非空唯一的，单个字段可以不是唯一的。如果要设置这样的主键，我们当然不可以使用这种方式。

create table tem(id int primary key, name varchar(10) primary key);

而是需要使用如下的方式，在最后声明主键。

create table tem(id int, name varchar(10), primary key(id, name));

唯一约束

这个就是字面上面的意思，就是这个键必须是唯一的。不能有重复的数据。设置的方式也非常的简单，而且也没什么好说的。

create table tem(id int default 1 primary key, name varchar(10) unique);

上面的default是给这个字段一个默认值（虽然给主键一个默认值是非常沙雕的，一般情况下都是给一个自动增长)。后面的unqiue就是唯一约束。需要注意的是，主键约束上面是不可以再加上unique的。

比如说，这就是一个错误的用法。

create table tem(id int primary key unqiue);

如果想要删除表中的唯一约束需要使用。

alter table tem drop index name;

如果要增加的话就是使用。

alter table tem add index (name)

非空约束

这个也是很简单，直接加上一个not null就行了。

create table tem(id int primary key, name varchar(10) not null);

同样的，主键约束上面也是不可以加上not null的。

添加的修改非空约束都是比较简单的，直接使用alter xx modify xx就行了。

alter table tem modify name varchar(10) not null; //添加非空

alter table tem modify name varchar(10); // 删除非空

外键约束

学习外键约束之前，我们首先要来了解一下什么是所谓的外键。现在比如说我们有如下的一张表，表中有如下的字段。

学号 姓名 年龄 性别 手机号 籍贯 学院 学院主任

前面是学生的基本的信息。后面是学院和系主任。很多人都是计算机系的，但是计算机系的主任就是那一个，如果按这个表的方式进行存储会造成很多数据的赘余。（其实是产生了传递依赖，违背了第三范式）。所以我们要将学院的信息专门弄一个表，然后将学生的信息表和这个表产生一定的关系。

学号 姓名 年龄 性别 手机号 籍贯 学院号

学院号 学院 学院主任

这时候两个表可以通过学院号进行联系。但是这似乎还不是很好。比如说我是可以插入不存在的学员号，或者是说随便修改学院表中的学院号。比如说现在只有1 2 3三个学院。我们无法插入学院号为4的学生。当还有学生表中还有学院号为1的学生的时候，我们是无法对学院表中的1学院号进行修改或者删除的。但是上面的表结构对这些都没有任何的限制，而使用外键就可以添加这些限制。

现在随便建立两个表来举例。

create table table2(id int primary key, name varchar(10) not null);

现在我们想要表2的id作为表1的外键(一般情况下都是需要主键的)，需要这样操作。

create table table1(id int primary key, userid int not null, foreign key(userid) references table2(id));

create table table1(id int primary key, userid int references table2(id) not null)

foreigh key(userid) references table2(id)就是设置外键的操作。

我们也可以在表已经存在的时候添加外键约束。

ALTER TABLE 子表 ADD CONSTRAINT 外键名 FOREIGN KEY (关联字段) 
REFERENCES 主表(被关联的字段) on delete cascade on update restrict;

其中后面也可以增加on delete, on update。都有restrict, no action, cascade, set null四个属性。其中restrict, no action几乎是一样的，放到上面的例子中就是如果该学院还有人就无法删除学院。cascade就是如果我们删除学院，那么学院中的所有的学生都会被删除，这个东西很危险。。。set null。就是如果删除学院，那么学生表中该学院的学生学院号都会变成null，唉，无家可归的孩子，真可怜。

我们还可以对外键进行修改，不过我们需要知道外键的名字。如果我们使用上面的第一种方式建立外键，此时没办法给名字（我实在是没查到如何给名字，查名字这种事情非常的麻烦。）所以我们需要建表之后，使用这种方式创建外键。

alter table table1 add constraint sher foreign key (userid) references table2(id);

此时外键的名字就是sher，我们可以使用ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;来删除这个外键。

alter table table1 drop foreigh key sher;

如果想要查看外键的名字，可以使用show create table xxx的方式查看创建的语句。比如这里使用show create table table1可以得到如下的内容。

| table1 | CREATE TABLE `table1` (
  `id` int(11) NOT NULL,
  `userid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sher` (`userid`),
  CONSTRAINT `sher` FOREIGN KEY (`userid`) REFERENCES `table2` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

原来在创建表的时候也是可以指定外键的名字的吖！！

多表之间的关系

所谓多表之间的关系，其实就是多个实体之间的关系。大抵上分为以下的三种关系——一对一，一对多，多对多。其中一对一的关系其实没有什么说的必要，既然是一对一的关系，为何不讲其放到同一个表中呢？下面主要就是来探讨一对多和多对多关系的处理。

一对多

最典型的一对多的例子就是学生和班级之间的例子。一个学生只有一个班级，但是一个班级却有很多个学生。此时班级和学生之间的关系就是一对多的关系。比如下面的这个表。

学号 姓名 班级 班主任
001 张三 1801 章三
002 李四 1801 章三
003 王五 1802 王舞
004 赵六 1892 王舞

此时我们明显的发现，数据非常的赘余。我们知道了班级按道理来说就是知道了班主任。后面可能还有班级的语文老师，数学老师等等班级其他的信息。所以说直觉都告诉我们需要为班级另外创建一个表。

不过此时学生表和班级表如果建立联系？不就是学生表加上班级属性嘛。。其实不然，上面也讲过了外键，这里我们想要建立联系就需要使用外键。

学号 姓名 班级号（外键）
001 张三   1
002 李四   1
003 王五   2
004 赵六   2 

班级号 班级名 班主任
  1    1801  章三
  2       1802  王舞

不过，我们为什么使用班级号而不是直接使用班级号呢？或许是有时候班级的名字也是需要修改的。最主要的就是创建班级表的时候我们是将班级号作为主键来使用的。(一般情况下都是使用一个id类似的东西来作为主键，而且可以设置为自增的)。

可以设置外键如下。

alter table stud add constraint stud_class foreign key (班级号) references class(班级号) 

一般情况下是不是用中文作为列名的。。

至于设置主键自增的代码如下所示。

create table class(class_id int auto_increment primary key)

-- 初始情况是从一开始，每次增加一的，不过我们也可以使用下面的方式进行调整。
SET @@auto_increment_increment=3; -- 将自增长步长设置为3
SET @@auto_increment_offset=4; -- 将自增长开始值设置为4

-- 也可以单独对这个表进行设置
alter table class auto_increment = 1000 -- 设置初始值

总结来说就是如果一对多的话，我们需要将“一”的那个表的主键作为“多”的那个表的外键。

多对多

多对多的最典型的例子就是学生和课程之间的例子。一个学生可以选修多个课程，但是多个课程也可以被多个学生选取。这个时候就是多对多的情况。

此时我们发现多对多的情况下我们都无法将这写数据放入到同一表中。只能放入到多张表中。

学号 姓名
1    张三
2    李四
3    王五

课程号 课程名
 1     语文
 2       数学

上面是学生和课程表。想要描述这两个表之间的关系是没办法是用外键的，因为外键只有一个，没法对应多个课程。那么我们就需要使用一张表来描述二者之间的关系了。

学号 课程号 成绩
1      1       98
1      2       78
2      1       67
2      2       98
3      2       58
3      1       99

上面我们额外的增加的了一个字段——成绩，这很有必要。上面的表中的主键是(学号， 课程号)，由两个字段构成，这个之前也说过了，并不意外。其中学号是第一个表的主键，课程号是第二表的主键。由此可见，关联多对多的表的方法就是重新创建一个表格，将多表的主键作为联合主键。后面的那个成绩的那个字段其实是可有可无的，只是加上去看起来更顺眼一些，如果不加上的话，这个表完全就是表示学生和课程之间的选取关系。

子查询返回值

本来是要说说三大范式的，但是转念一想，三大范式似乎是有点简单了，而且上面的基本上我已经潜移默化的说完了三大范式了。那么这里就说说子查询的情况吧。这里主要说的是子查询的返回值的使用。其中返回的结果主要是三种情况——单行单列、多行单列，多行多列。

单行单列

产生单行单例的返回值很多情况下都是使用了聚合函数。聚合函数常用的就是一下的几种。

max min avg sum count、

select count(id) from stud;

此时返回的就是一个单行单列的表格，此时这个表格就可以作为一个数据来使用。正如一行一列的矩阵和普通的数是没有任何的区别的。

select * from stud where id = (select count(id) from stud);

这就是子查询的一个使用，其中运行的顺序是先运行子查询，然后再运行父查询。(括号是必不可少的)。

多行单列

除了单行单列的数据可以作为普通的数据使用各种各样的操作云算法，多行单例的也是可以使用一个操作符的，那就是in 操作符。

select id, name from stud where id in (select id from stud where score > avg(score));

虽然这个例子写的非常的沙雕没有意义，但是很多的体现了使用in操作符作用与多行单列的表。

多行多列

多行多列的数据是不可以使用任何的操作运算符，但是可以作为一个表格直接使用。比如说。

select * from (select * from stud where id > 5) where name = 'sher';

此时子查询的结果就是一个表，也可以给这个表一个别名as，然后在后面使用的子查询的结果的表。

总结

上面主要就是写了SQL中的三个内容。一个就是SQL中的四个约束的使用，二是如果处理多个表之间的关系。三是如何处理子查询的返回值的使用。