Java后台之路(03)-MySql

数据的完整性

数据的完整性是为了保证添加到数据库的数据是正确的，所以我们需要在创建表时添加约束。

---

数据的完整性·实体完整性

1. 主键约束

在表中设置一个主键；被标识为主键的数据在表中是唯一的且其值不能为null。

// 方式1，创建表时声明主键create table tableName(id int primary key);// 方式2，创建表时声明主键create table tableName(id int, name varchar(10), primary key(id));//方式3，创建表时未设置，新增alter table tableName add primary key (id);

2. 联合主键

不要把联合主键理解成两个主键；它们是以两个字段联合的形式作为主键。

create table tableName(id int,age int, primary key(id,age));

3. 唯一约束

unique，为字段添加唯一约束(unique)后该字段对应的值不能重复，是唯一的。

create table tableName(id int , name varchar(10) unique);

4. 自动增长列

可用auto_increment关键字标识int类型的字段，设置后该字段的值会自动地增长。常见的做法是给int类型的主键添加auto_increment。

create table tableName(id int auto_increment);

---

数据的完整性·域完整性

域完整性用于确保表中单元格的数据正确；所以，域完整性就代表了表中单元格的完整性。

1. 非空约束（not null）

在该示例中设定name字段为NOT NULL，所以在插入数据时必须为该字段设值。

create table tableName(id int not null);

2. 默认值约束（DEFAULT）

看例子就明白了。

// 表数据的年龄默认为20岁create table student(id int, age int default 20);// 插入数据,这两句一样insert into student(id) values(0);insert into student(id,age) values(0, default);

---

数据的完整性·引用完整性

引用完整性也叫参照完整性，常用于设值外键。

// 创建一个学生表create table student(id int primary key,name varchar(10));// 创建一个成绩表create table source(studentid int,source int,constraint fk_id foreing key (studentid) references student(id) );

在此建立两张表。其中，学生表student作为主表，分数表score作为子表。请注意在score中的studentid学生编号表示成绩是属于哪个学生的，该值必须是student表中id列里的值。所以，利用外键FOREIGN KEY将score中的studentid与student表中id建立起联系。设置外键需要满足以下条件。

• 子表里的外键必须是主表的主键；
• 子表里的外键必须是主表的主键；

我们也可以利用sql语句设置外键；

alter table score add constraint fk_id foreing key (studengid) references student(id);

---

表关系·一对一

示例：

// 创建表personcreate table person(personid int primary key, personname varchar(20) not null);// 创建表persondetailcreate table persondetail(detailid int primary key, job varchar(20));//建立关系alter table persondetail add constraint fk_id foreing key (detailid) references person(personid);

一条person数据只对应一条persondetail信息，所以personid和detailid都是主键。

---

表关系·一对多

比如一个学生，他在学生表里只能有一条自己的信息，但是在成绩表中可以有多条，语文成绩/数学成绩等。

create table student(studentid int primary key,age int);create table source(sourceid int primary key, studentid int, source int);alter table source add constraint fk_id foreing key (studentid) references student(studengid);

---

表关系·多对多

一个学生有很多老师，一个老师也教很多学生。那么这样学生表student和教师表teacher就是多对多的关系。

为了化解这种复杂关系，我们引用一个表manager用来存放student的id和teacher的id，将student和manager的关系设置为一对多，即一个学生对应多个老师，将teacher和manager的关系设置为一对多，即一个老师对应多个学生，从而达到student和teacher多对多的关系。

// 创建表studentcreate table student(studentid int primary key,studentname varchar(20));// 创建表teachercreate table teacher(teacherid int primary key, teachername varchar(20));// 创建表managercreate table manager(sid int,tid int);// 添加student和manager的关系alter table manager add constraint fk_sid foreing key (sid) references student(studentid);// 添加teacher和manager的关系alter table manager add constraint fk_tid foreing key (tid) references teacher(teacherid);

---

多表查询

先进行准备工作，创建几个表。

// 创建表studentcreate table student(    studentid int primary key,    studentname varchar(20) not null,    studentaddress varchar(20) default '北京');// 创建personcreate table person(    personid int primary key,    personname varchar(20) not null,    personaddress varchar(20) default '上海');// 创建表scoreCREATE TABLE score(    scoreid INT PRIMARY KEY,    studentid INT,    score INT);

表student

表person

表score

1. 合并结果集

合并结果集就是将两个select语句的结果合并到一起，使用union关键字会去除重复数据，使用union all不会去除重复记录，被合并的两个结果它们的列数、列类型必须相同。

select studentid as sid,studentname as sname from student union select personid,personname from person;

2. 连接查询

连接查询就是连个表的乘积，比如表{a,b}和表{1,2,3}的乘积就是{a1, a2, a3, b1, b2, b3}。

// 创建表关系alter table score add contraint fk_id foreing key (studentid) references studeng(studentid);select * from student as s1, score as s2 where s1.'studentid'=s2.'studentid';

3. 内连接查询

SELECT s1.`studentid`,s1.`studentname`,s2.`scoreid`,s2.`score` FROM student AS s1 JOIN score AS s2 ON s1.`studentid`=s2.`studentid`WHERE s2.`score`>92;

4. 外连接查询

外连接分为左外连接/右外连接，它们和内连接的区别在于，左外连接依照左边表数据为准，即使右边的表无数据也会出现一条数据左表有值，右表数据为null，而内连接必须左右表都有数据才会有数据。

比如，student中有id为5的学生，而在成绩表score中没有它的成绩（他可能LOL去了），它们查询的区别；

// 内连接SELECT s1.`studentid`,s1.`studentname`,s2.`scoreid`,s2.`score` FROM student AS s1 JOIN score AS s2ON s1.`studentid`=s2.`studentid`;

// 左外连接SELECT s1.`studentid`,s1.`studentname`,s2.`scoreid`,s2.`score` FROM student AS s1 LEFT JOIN score AS s2ON s1.`studentid`=s2.`studentid`;

// 右外连接SELECT s1.`studentid`,s1.`studentname`,s2.`scoreid`,s2.`score` FROM student AS s1 RIGHT JOIN score AS s2ON s1.`studentid`=s2.`studentid`;

为什么内连接和右外连接结果一样呢？因为内连接必须左右表都有数据才会有此条数据，右连接需要右表有数据才会有此条数据，因为此例中右表score中没有studentid为5的数据，所以才有此结果。

5. 子查询

可以理解为嵌套查询。

// 搜索名为“学一”的学生成绩，分两步// 1.在表student中搜索"学一"的id// 2.id作为结果在表score中搜索成绩select * from score where studentid=(select studentid from student where studentname='学一');

---