表间关系
一、表间关系
1、表间关系的含义：
同一个数据库中，不同表中的数据之间都存在一种关系，这种关系将数据库里各表中的每条数据记录都和数据库中唯一的主题相联系，使得对每一个数据的操作都成为数据库的整体操作。
2、表间关系的类型：
* 一对一
* 一对多
* 多对多
3、建立表间关系的目的：
* 保证数据的完整性
-> 表间关系的建立使主表和从表之间建立数据约束关系，防止输入错误的数据；
* 保证数据的一致性
-> 当存在多个表的时候，通过建立主表和从表的关系，可以在主表更新时，从表自动更新，省去手动更新；
* 方便多表查询
-> 方便连接两个或多个表，一次能查找到多个相关数据。
二、主外键物理表
在第3章里我们介绍了主键的设置和选择，为了建立表间关系，还需要在子表里设置外键。
1、外键的作用
外键引用另外一个数据表的某条记录，以此在两个表之间建立联系。
外键是子表里的一个字段，引用父表（主表）里的主键。
* 外键列类型尽可能与主键列类型保持一致
* 外键列应该加上 NOT NULL
例1：在创建学员信息表（父表）student 时指定主键 sid 。
create table student
(sid int not null auto_increment,
name varchar(20) not null,
primary key(sid) );
例2：在创建学员成绩表（子表）score 时定义外键。
create table score
(sid int not null auto_increment primary key,
score int,
foreign key (sid) references student(sid) );
在这个范例里，sid 字段被定义为表 score 的外键，它引用了表 student 里的 sid 字段。这个外键确保了表 score 里的每个 sid 都在表 student 里有对应的 sid。这被称为父/子（主/从）关系。
2、外键约束
外键约束是确保表与表之间引用完整性的主要机制。
研究下面范例里外键的创建：
create table employee_pay_tbl
(emp_id char(9) not null,
position varchar(15) not null,
date_hire date null,
pay_rate decimal(4,2) not null,
date_last_raise date null,
constraint emp_id_fk foreign key (emp_id) references employee_tbl (emp_id) );
在这个例子里，employee_pay_tbl 是 employee_tbl 的子表。子表里的 emp_id 引用父表里的 emp_id 字段。为了在子表里插入一个 emp_id 的值，它首先要存在于父表的 emp_id 里。类似地，父表里删除一个 emp_id 的值，子表里相应的 emp_id 值必须全部被删除。这就是引用完整性的概念。
利用 alter table 命令也可以向表里添加外键。例如：
alter table employee_pay_tbl
add constraint id_fk foreign key (emp_id) references employee_tbl (emp_id);
三、主外键物理表的数据插入删除规则
主表和从表里的数据在插入删除时，要遵循以下规则：
（1）当主表中没有对应的记录时，不能将记录添加到子表
上面的例子里学员成绩表 score 中不能出现在学员信息表 student 中不存在的学号。
（2）不能更改主表中的值而导致子表中的记录孤立
把 student 表中的学号改变了，score 表中的学号也应当随之改变。
（3）子表存在与主表对应的记录，不能从主表中删除该行
不能把有成绩的学员删除了。
（4）删除主表前，先删子表
先删除学员成绩表 score、后删除学员信息表 student。
【参考资料】
W3SChool 在线SQL教程 http://www.w3SChool.com.cn/sql/sql_join.asp SQL JOIN
课后练习：
1. 表间关系的含义是什么？建立表间关系的目的是什么？
2. 按照下面的要求建立数据库 company，分析数据库结构和各表之间的关系。
1）建库建表
--create database company
create database company;
use company;
--create tables
--部门表
create table dept
(
deptno int(3) primary key,
dname varchar(14),
loc varchar(13)
);
--雇员表
create table emp
(
empno int(4) not null primary key,
ename varchar(10),
job varchar(10),
mgr int(4),
hiredate datetime,
sal double,
comm double,
deptno int(3),
foreign key(deptno) references dept(deptno)
);
--工资级别表
create table salgrade
(
grade int(3) primary key,
losal int(3),
hisal int(3)
);
2）插入数据（进行初始化）
use company;
--往部门表中查数据
insert into dept values(10,'Accounting','New York');
insert into dept values(20,'Research','Dallas');
insert into dept values(30,'Sales','Chicago');
insert into dept values(40,'Operations','Boston');
insert into dept values(50,'Admin','Washing');
--往雇员表中插数据
insert into emp values(7369,'Smith','Clerk',7902,'1980-12-17',800,0,20);
insert into emp values(7499,'Allen','Salesman',7698,'1981-2-20',1600,300,30);
insert into emp values(7844,'Turner','Salesman',7499,'1981-9-8',1500,0,30);
insert into emp values(7698,'Tom','Manager',0,'1981-9-8',6100,600,40);
insert into emp values(7876,'Adams','Clerk',7900,'1987-5-23',1100,0,20);
insert into emp values(7900,'James','Clerk',7698,'1981-12-3',2400,0,30);
insert into emp values(7902,'Ford','Analyst',7698,'1981-12-3',3000,null,20);
insert into emp values(7901,'Kik','Clerk',7900,'1981-12-3',1900,0,30);
--往工资级别表中插数据
insert into salgrade values(1,700,1200);
insert into salgrade values(2,1201,1400);
insert into salgrade values(3,1401,2000);
insert into salgrade values(4,2001,3000);
insert into salgrade values(5,3001,5000);
insert into salgrade values(6,5001,10000);
3. 创建一个名为学员成绩的数据库 stuscore，各表的关系如下图所示：
1）创建学生信息表（Student）包含：学生编号、学生姓名、性别、年龄、地区、联系电话、入学日期；
2）创建课程信息表（Score）包含：课程编号、科目、教师编号；
3）创建学生成绩表（chengji）包含：学生编号、课程编号、考试分数；
4）创建教师信息表（teacherInfo）包含：教师编号、教师姓名；
5）建立四个表间的主从表关系。
试回答：
1）学生成绩表中能不能出现在学员信息表中不存在的学号?
2）学生信息表中学生编号发生改变时应如何处理成绩表中的学生编号?
3）可以任意删除学生信息表中的数据吗？
4）学生成绩表中的课程编号可以随意添加吗？
根据查询要求，写出SQL语句：
1）查询学生信息表，查找学生的姓名，性别，并将查询结果按姓名进行排序。
2）查询学生信息表，查找地区在大连的学生，显示其姓名，联系电话，地区。
3）查询学生信息表，查找性别为女，并且地区不在大连的学生，显示其姓名，年龄及联系电话，地区。
4）练习通过as对字段进行重命名，实现两列合并显示，如外国人名中姓+名的正常显示。
5）查询学生信息表，查找地区为空的学生的姓名，性别，联系电话。
6）查询成绩表，查找课程编号为2的成绩最好的2名学生的编号。
7）查询学生信息表，查找学生来自于哪些地区，显示地区时不重复。
8）查询学生信息表，查找姓李的学生的姓名，性别，联系电话，地区。
9）查询学生信息表，查找学生姓名中，第二个字符为"大"的学生的姓名，性别，联系电话，地区。
10）查询学生信息表，查找地区为空的学生的姓名，性别，联系电话，地区。
11）查询成绩表，查找课程编号为2的课程，显示此门课程成绩在６０ ７０分之间的学生编号。
12）查询学生信息表，地区中包含0-4的值的字段。
13）查询学生信息表，查找地区在大连、鞍山的学生姓名，联系电话。
14）查询学生信息表，查找学生姓名中，第二个字符不为"大"的学生的姓名，性别，联系电话，地区。
15）查询成绩表，查找课程编号为1的课程的平均成绩。
16）查询成绩表，显示课程编号为2的课程的最高分，最低分。
17）查询成绩表，利用count函数查找课程编号为2的课程及格的人数。
18）查询成绩表，显示每门课程的平均成绩。
19）查询成绩表，显示每个人的总成绩。
20）查询成绩表，利用group by及having，显示总成绩超过100分的学生的编号。
21）查询学生的注册日期，要求只显示年月日即可，无需时间。
22）将编号为2的成绩乘与%5后再加10分做为加权分数列升序输出。
23）查询成绩表，按照成绩及学号进行升序排序，成绩比学号的排列优先级要高。