mysql之表与表之间的关系

表与表之间的关系

数据库的三范式

练习题

查询学习课程"python"比课程 "java" 成绩高的学生的学号;

查询平均成绩大于65分的同学的姓名和平均成绩(保留两位小数)

查询所有同学的姓名、选课数、总成绩；

查询所有的课程的名称以及对应的任课老师姓名;

查询没学过“alex”老师课的同学的姓名;

查询学过'python'并且也学过编号'java'课程的同学的姓名

查询挂科超过两门(包括两门)的学生姓名;

查询选修了全部课程的学生姓名;

1.表关系分类:

　　总体可以分为三类: 一对一、一对多(多对一) 、多对多

2.如何区分表与表之间是什么关系?

#分析步骤:
#多对一 /一对多
#1.站在左表的角度去看右表(情况一)
如果左表中的一条记录,对应右表中多条记录.那么他们的关系则为 一对多 关系.约束关系为:左表普通字段, 对应右表foreign key 字段.

注意:如果左表与右表的情况反之.则关系为 多对一 关系.约束关系为:左表foreign key 字段, 对应右表普通字段.

#一对一
#2.站在左表的角度去看右表(情况二)
如果左表中的一条记录 对应 右表中的一条记录. 则关系为 一对一关系.
约束关系为:左表foreign key字段上 添加唯一(unique)约束, 对应右表 关联字段.
或者:右表foreign key字段上 添加唯一(unique)约束, 对应右表 关联字段.

#多对多
#3.站在左表和右表同时去看(情况三)
如果左表中的一条记录 对应 右表中的多条记录,并且右表中的一条记录同时也对应左表的多条记录. 那么这种关系 则 多对多 关系. 
这种关系需要定义一个这两张表的[关系表]来专门存放二者的关系

3.建立表关系

1.一对多关系

　例如:一个人可以拥有多辆汽车，要求查询某个人拥有的所有车辆。
　分析：人和车辆分别单独建表，那么如何将两个表关联呢？有个巧妙的方法，在车辆的表中加个外键字段(人的编号)即可。
　* (思路小结：’建两个表，一’方不动，’多’方添加一个外键字段)*

//建立人员表
CREATE TABLE people(
    id VARCHAR(12) PRIMARY KEY,
    sname VARCHAR(12),
    age INT,
    sex CHAR(1)
);
INSERT INTO people VALUES('H001','小王',27,'1');
INSERT INTO people VALUES('H002','小明',24,'1');
INSERT INTO people VALUES('H003','张慧',28,'0');
INSERT INTO people VALUES('H004','李小燕',35,'0');
INSERT INTO people VALUES('H005','王大拿',29,'1');
INSERT INTO people VALUES('H006','周强',36,'1');
 //建立车辆信息表
CREATE TABLE car(
    id VARCHAR(12) PRIMARY KEY,
    mark VARCHAR(24),
    price NUMERIC(6,2),
    pid VARCHAR(12),
    CONSTRAINT fk_people FOREIGN KEY(pid) REFERENCES people(id)  #给表car下的pid创建与表people下的id关联的外键约束，产生一对多的关系，即一辆车可以属于多个人
);
INSERT INTO car VALUES('C001','BMW',65.99,'H001');
INSERT INTO car VALUES('C002','BenZ',75.99,'H002');
INSERT INTO car VALUES('C003','Skoda',23.99,'H001');
INSERT INTO car VALUES('C004','Peugeot',20.99,'H003');
INSERT INTO car VALUES('C005','Porsche',295.99,'H004');
INSERT INTO car VALUES('C006','Honda',24.99,'H005');
INSERT INTO car VALUES('C007','Toyota',27.99,'H006');
INSERT INTO car VALUES('C008','Kia',18.99,'H002');
INSERT INTO car VALUES('C009','Bentley',309.99,'H005');

代码示例

示例代码

2.一对一关系

例如：一个中国公民只能有一个身份证信息

分析: 一对一的表关系实际上是变异了的一对多关系. 通过在从表的外键字段上添加唯一约束(unique)来实现一对一表关系.

#身份证信息表
CREATE TABLE card (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  code varchar(18) DEFAULT NULL,
  UNIQUE un_code (CODE) -- 创建唯一约束的目的,保证身份证号码(code)同样不能出现重复
);

INSERT INTO card VALUES(null,'210123123890890678'),
                       (null,'210123456789012345'),
                       (null,'210098765432112312');

#公民表
CREATE TABLE people (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name varchar(50) DEFAULT NULL,
  sex char(1) DEFAULT '0',
  c_id int UNIQUE, -- 添加唯一约束,确保一对一
  CONSTRAINT fk_card_id FOREIGN KEY (c_id) REFERENCES card(code)  #在表公民下的c_id创建与表card下的code相关联的外键约束
);

INSERT INTO people VALUES(null,'zhangsan','1',1),
                         (null,'lisi','0',2),
                         (null,'wangwu','1',3);

代码示例

示例代码

3.多对多关系

　例如：学生选课，一个学生可以选修多门课程，每门课程可供多个学生选择。
　分析：这种方式可以按照类似一对多方式建表，但冗余信息太多，好的方式是实体和关系分离并单独建表，实体表为学生表和课程表，关系表为选修表，
其中关系表采用联合主键的方式(由学生表主键和课程表主键组成)建表。

#//建立学生表
CREATE TABLE student(
    id VARCHAR(10) PRIMARY KEY,
    sname VARCHAR(12),
    age INT,
    sex CHAR(1)
);
INSERT INTO student VALUES('S0001','王军',20,1);
INSERT INTO student VALUES('S0002','张宇',21,1);
INSERT INTO student VALUES('S0003','刘飞',22,1);
INSERT INTO student VALUES('S0004','赵燕',18,0);
INSERT INTO student VALUES('S0005','曾婷',19,0);
INSERT INTO student VALUES('S0006','周慧',21,0);
INSERT INTO student VALUES('S0007','小红',23,0);
INSERT INTO student VALUES('S0008','杨晓',18,0);
INSERT INTO student VALUES('S0009','李杰',20,1);
INSERT INTO student VALUES('S0010','张良',22,1);

# //建立课程表
CREATE TABLE course(
    id VARCHAR(10) PRIMARY KEY,
    sname VARCHAR(12),
    credit DOUBLE(2,1),
    teacher VARCHAR(12)
);
INSERT INTO course VALUES('C001','Java',3.5,'李老师');
INSERT INTO course VALUES('C002','高等数学',5.0,'赵老师');
INSERT INTO course VALUES('C003','JavaScript',3.5,'王老师');
INSERT INTO course VALUES('C004','离散数学',3.5,'卜老师');
INSERT INTO course VALUES('C005','数据库',3.5,'廖老师');
INSERT INTO course VALUES('C006','操作系统',3.5,'张老师');

# //建立选修表  创建中间表
CREATE TABLE sc(
    sid VARCHAR(10),
    cid VARCHAR(10),
      PRIMARY KEY(sid,cid),
      CONSTRAINT fk_student FOREIGN KEY(sid) REFERENCES student(id),  #在选修表中出现的数据一定是要在学生和课程表之下的，素以这里要添加两个外键约束
      CONSTRAINT fk_course FOREIGN KEY(cid) REFERENCES course(id)
);

INSERT INTO sc VALUES('S0001','C001');
INSERT INTO sc VALUES('S0001','C002');
INSERT INTO sc VALUES('S0001','C003');
INSERT INTO sc VALUES('S0002','C001');
INSERT INTO sc VALUES('S0002','C004');
INSERT INTO sc VALUES('S0003','C002');
INSERT INTO sc VALUES('S0003','C005');
INSERT INTO sc VALUES('S0004','C003');
INSERT INTO sc VALUES('S0005','C001');
INSERT INTO sc VALUES('S0006','C004');
INSERT INTO sc VALUES('S0007','C002');
INSERT INTO sc VALUES('S0008','C003');
INSERT INTO sc VALUES('S0009','C001');
INSERT INTO sc VALUES('S0009','C005');

示例代码

数据库的三范式

1．第一范式(确保每列保持原子性)

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示。

即不可用一个地址列来代替省份列、城市列和详细地址列，这样取数据的时候比较容易。

上表所示的用户信息遵循了第一范式的要求，这样在对用户使用城市进行分类的时候就非常方便，也提高了数据库的性能。

2．第二范式(确保表中的每列都和主键相关)

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示。

订单信息表

这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。

而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就非常完美了。如下所示。

这样设计，在很大程度上减小了数据库的冗余。如果要获取订单的商品信息，使用商品编号到商品信息表中查询即可。

3．第三范式(确保每列都和主键列直接相关,而不是间接相关)

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。

这样在查询订单信息的时候，就可以使用客户编号来引用客户信息表中的记录，也不必在订单信息表中多次输入客户信息的内容，减小了数据冗余。

注意事项：

1.第二范式与第三范式的本质区别：在于有没有分出两张表。

第二范式是说一张表中包含了多种不同实体的属性，那么必须要分成多张表，第三范式是要求已经分好了多张表的话，一张表中只能有另一张标的ID，而不能有其他任何信息，（其他任何信息，一律用主键在另一张表中查询）。

2.必须先满足第一范式才能满足第二范式，必须同时满足第一第二范式才能满足第三范式。

练习题

练习题所用的数据脚本：

#课程表
CREATE TABLE `course` (
  `c_id` int(11) NOT NULL,
  `c_name` varchar(50) DEFAULT NULL,
  `t_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`c_id`),
  KEY `t_id` (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `course` VALUES ('1', 'python', '1');
INSERT INTO `course` VALUES ('2', 'java', '2');
INSERT INTO `course` VALUES ('3', 'linux', '3');
INSERT INTO `course` VALUES ('4', 'web', '2');

#成绩表
CREATE TABLE `score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `s_id` int(11) DEFAULT NULL,
  `c_id` int(11) DEFAULT NULL,
  `num` double DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

INSERT INTO `score` VALUES ('1', '1', '1', '79');
INSERT INTO `score` VALUES ('2', '1', '2', '78');
INSERT INTO `score` VALUES ('3', '1', '3', '35');
INSERT INTO `score` VALUES ('4', '2', '2', '32');
INSERT INTO `score` VALUES ('5', '3', '1', '66');
INSERT INTO `score` VALUES ('6', '4', '2', '77');
INSERT INTO `score` VALUES ('7', '4', '1', '68');
INSERT INTO `score` VALUES ('8', '5', '1', '66');
INSERT INTO `score` VALUES ('9', '2', '1', '69');
INSERT INTO `score` VALUES ('10', '4', '4', '75');
INSERT INTO `score` VALUES ('11', '5', '4', '66.7');

#学生表
CREATE TABLE `student` (
  `s_id` varchar(20) NOT NULL,
  `s_name` varchar(50) DEFAULT NULL,
  `s_age` int(10) DEFAULT NULL,
  `s_sex` char(1) DEFAULT NULL,
  PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `student` VALUES ('1', '鲁班', '12', '男');
INSERT INTO `student` VALUES ('2', '貂蝉', '20', '女');
INSERT INTO `student` VALUES ('3', '刘备', '35', '男');
INSERT INTO `student` VALUES ('4', '关羽', '34', '男');
INSERT INTO `student` VALUES ('5', '张飞', '33', '女');

#老师表
CREATE TABLE `teacher` (
  `t_id` int(10) NOT NULL,
  `t_name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `teacher` VALUES ('1', '大王');
INSERT INTO `teacher` VALUES ('2', 'alex');
INSERT INTO `teacher` VALUES ('3', 'egon');
INSERT INTO `teacher` VALUES ('4', 'peiqi');

数据脚本

练习题数据脚本

1、新建一个数据库db3

插入练习题数据脚本，后生成四张表：

四张表之间的数据间的关系：学生表中的s_id和成绩表中的s_id是关联的、老师表中的t_id和课程表中的t_id是关联的、课程表中的课程编号c_id和成绩表中的c_id是关联的。

2、查询

（1）查询学习课程"python"比课程 "java" 成绩高的学生的学号;

因为课程表中只有课程名字和课程编号没有成绩，在成绩表中有课程成绩和课程编号没有课程名字，所以先联合课程表和成绩表将python和java的成绩先找出来

　　　1）select * from course,score where course.c_id=score.c_id

　　2）select num from course,score where course.c_id=score.c_id and course.c_name='python';

　　 select num from course,score where course.c_id=score.c_id and course.c_name='java';

　　3)select score.s_id,score.num from course,score where course.c_id=score.c_id and course.c_name='python';

　　　select score.s_id,score.num from course,score where course.c_id=score.c_id and course.c_name='java';

　　3)select * from (select score.s_id,score.num from course,score where course.c_id=score.c_id and course.c_name='python') as python,

　　　　　　　　(select score.s_id,score.num from course,score where course.c_id=score.c_id and course.c_name='java') as java
　　　　　　　　where python.s_id=java.s_id and python.num > java.num

　　4)select student.s_id,student.s_name from (select score.s_id,score.num from course,score where course.c_id=score.c_id and course.c_name='python') as python,

(select score.s_id,score.num from course,score where course.c_id=score.c_id and course.c_name='java') as java,
student
　　　　　　　　　　　　　　　　　　　　where python.s_id=java.s_id and python.num > java.num and python.s_id=student.s_id

(2)查询平均成绩大于65分的同学的姓名和平均成绩(保留两位小数);

分析：查询“同学姓名”要用到学生表，“成绩”要用到成绩表，学生表和成绩表之间的关联就是学生编号

　　1) select * from student,score where student.s_id = score.s_id

　　2) select student.s_id,student.s_name,score.num from student,score where student.s_id = score.s_id

3) select student.s_id,student.s_name,avg(score.num) from student,score where student.s_id = score.s_id group by score.s_id having avg(num)>65

4) select student.s_id,student.s_name,round(avg(score.num),2) from student,score where student.s_id = score.s_id group by score.s_id having avg(num)>65

　　(3) 查询所有同学的姓名、选课数、总成绩；

　　分析：要用到学生表、课程表、成绩表

　　1） select student.s_id,student.s_name,count(score.s_id) from student,score where student.s_id=score.s_id group by score.s_id

　　2）删除后：

　　3） select student.s_id,student.s_name,count(score.s_id) as sum_course,sum(score.num) as sum_score from student,score where student.s_id=score.s_id group by score.s_id

　　（4）查询所有的课程的名称以及对应的任课老师姓名;

　　1） select * from course,teacher where course.t_id=teacher.t_id

　　2） select course.c_name,teacher.t_name from course,teacher where course.t_id=teacher.t_id

　　（5）查询没学过“alex”老师课的同学的姓名;

　　1) select course.c_id from teacher,course where teacher.t_id=course.t_id and teacher.t_name='alex'

　　2) select score.s_id from score where score.c_id in (select course.c_id from teacher,course where teacher.t_id=course.t_id and teacher.t_name='alex')

　　3） select * from student where student.s_id not in (select score.s_id from score where score.c_id in (select course.c_id from teacher,course where teacher.t_id=course.t_id and teacher.t_name='alex') )

　　（6）查询学过'python'并且也学过编号'java'课程的同学的姓名;

　　由于course表中python和java对应的有用的只有课程代码（c_id），而在student表中没有课程代码（c_id)，有学生编号s_id；但是在score表中有c_id和学生编号s_id，所以引入score表

　　1）select score.s_id from score,course where course.c_id=score.c_id and course.c_name in('python','java')

　　2）select score.s_id,count(score.s_id) from score,course where course.c_id=score.c_id and course.c_name in('python','java')group by score.s_id

　　3）select score.s_id,count(score.s_id) from score,course where course.c_id=score.c_id and course.c_name in('python','java') group by score.s_id having 　　count(score.s_id)>=2

　　4） select score.s_id,count(score.s_id),student.s_name from score,course,student where course.c_id=score.c_id and student.s_id=score.s_id and course.c_name in('python','java') group by score.s_id having count(score.s_id)>=2

　　（7）查询挂科超过两门(包括两门)的学生姓名;

　　1）select * from score where score.num<70

　　2）select score.s_id from score where score.num<70 group by score.s_id having count(score.s_id)>=2

　　3）select score.s_id,student.s_name from score,student where student.s_id=score.s_id and score.num<70 group by score.s_id having count(score.s_id)>=2

　　（8）查询选修了全部课程的学生姓名;

　　　　1）select count(course.c_id) from course

　　　　2） select * from score group by score.s_id having count(*)=(select count(course.c_id) from course ) #这一句有错误，但是思路是对的们就是先查询出课程的总数，然后给score表根据课程分组，根据s_id将学生数累加起来，和总的课程相比较就可以了

　　（9）查询至少有一门课程与“貂蝉”同学所学课程相同的同学姓名；

　　　　1） select score.c_id from student,score where student.s_id=score.s_id and student.s_name='貂蝉'

　　　　2）select score.s_id from score where score.c_id in (select score.c_id from student,score where student.s_id=score.s_id and student.s_name='貂蝉')

　　　　3）select score.s_id,student.s_name from score,student where student.s_id=score.s_id and score.c_id in (select score.c_id from student,score where student.s_id=score.s_id and student.s_name='貂蝉')

　　4）去掉貂蝉的名字：select score.s_id,student.s_name from score,student where student.s_id=score.s_id and score.c_id in (select score.c_id from student,score where student.s_id=score.s_id and student.s_name='貂蝉') and student.s_name!='貂蝉'

转自：https://www.cnblogs.com/wangfengming/articles/7929118.html