【MySQL】基础入门（第三篇）

一、建表的范式

数据库建表的范式是关系型数据库设计中的重要概念，它定义了数据库表应满足的规范级别，以确保数据的准确性、一致性和减少数据冗余。目前，关系型数据库主要遵循六种范式，从低到高分别是第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式（4NF）和第五范式（5NF，又称完美范式）。以下是对前三个范式（1NF~3NF）的详细介绍：

第一范式（1NF）

定义：第一范式要求数据库表的每一列都是不可分割的基本数据项，即每个字段都是原子性的，不能再进行拆分。同一列中不能有多个值，也不能有重复的属性。

比如说：想要记录学生的信息：id，姓名，电话号码以及备用号码

id	name	normaltel/backtel
1	aa	123XX/124XX
2	bb	123XX/456XX

如果建表是这样的，就不会符合规范。属性列不可以拆分改为

id	name	normaltel	backtel
1	aa	123XX	124XX
2	bb	123XX	456XX

要求：

• 数据库表的每一行都是不可分割的基本数据项。
• 同一列中不能有多个值或重复的属性。
• 如果出现重复的属性，可能需要定义一个新的实体，新实体与原实体之间为一对多关系。

第二范式（2NF）

定义：第二范式是在满足第一范式的基础上，进一步要求数据库表中的每个实例或行都必须是唯一的，并且实体的属性完全依赖于主关键字。

比如 想要记录一下学生的选课以及成绩信息 (id,cid) 是组合主键

(id	cid)	name	age	coursename	score
1	1	aa	18	C	40
1	2	aa	18	C++	60
1	3	aa	18	OS	70

这个表中会有大量信息的数据冗余，name,age 只依赖于id， coursename 只依赖于cid, score 才依赖id,cid 可以进行拆分

第一个是学生信息表   

id	name	age
1	aa	18

第二个是 课程信息表

cid	coursename
1	C
2	C++
3	OS

第三个是学生选课信息表

id	cid	score
1	1	40
1	2	60
1	3	70

 

要求：

• 数据库表中的每个实例或行必须是唯一的，通常通过添加主键来实现。
• 实体的属性完全依赖于主关键字，不存在仅依赖主关键字一部分的属性。
• 如果存在仅依赖主关键字一部分的属性，应将其分离出来形成新的实体，新实体与原实体之间是一对多的关系。

第三范式（3NF）

定义：第三范式是在满足第二范式的基础上，要求数据库表中不包含已在其他表中已包含的非主关键字信息，即非主属性不传递依赖于其他非主属性。

比如：统计 每个学生的学校情况

id	name	age	schoolname	schooladdress
1	aa	18	哈理工	学府路xx
2	bb	18	黑大	学府路xx

name,age,schoolname都依赖于id,但是schooladdress依赖于schoolname，所以要把信息拆成两个表

学生学校表

id	name	age	schoolid
1	aa	18	1
2	bb	18	2

学校信息表

schoolid	schoolname	schooladdress
1	哈理工	学府路XX
2	黑大	学府路XX

 

要求：

• 一个数据库表中不包含已在其他表中已包含的非主关键字信息。
• 所有的非主属性都直接依赖于主键，不存在传递依赖。
• 如果存在传递依赖，应将其分解为多个表，并通过外键关联。

范式的优点与缺点

优点：

• 有助于消除数据库的数据冗余，提高数据的准确性和一致性。
• 便于数据的维护和管理，减少数据更新时的错误。

缺点：

• 高范式级别的设计可能会增加表的数量和复杂性，降低查询效率。
• 在某些情况下，为了提高查询性能，可能需要适当违反范式要求，增加表的冗余度。

实际应用

在实际应用中，通常只需要满足前三个范式（1NF~3NF）就足够了。更高级别的范式虽然能进一步减少数据冗余，但也会增加设计的复杂性和查询的开销。因此，在设计数据库表时，需要根据实际业务需求和数据使用场景来权衡范式的选择。

总之，数据库建表的范式是确保数据库设计合理、数据准确一致的重要手段。通过遵循不同的范式级别，可以设计出既满足业务需求又具有良好性能的数据库表结构。

二、创建一些测试数据的表

问题及描述：
--1.学生表
Student(Ssum,Sname,Sage,Ssex) --Snum 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
--2.课程表 
Course(Cnum,Cname,Tnum) --Cnum --课程编号,Cname 课程名称,Tnum 教师编号
--3.教师表 
Teacher(Tnum,Tname) --Tnum 教师编号,Tname 教师姓名
--4.成绩表 
SC(Snum,Cnum,score) --Snum 学生编号,Cnum 课程编号,score 分数
*/
--创建测试数据

create table Student(Snum varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10));
insert into Student values('01' , N'小猴' , '1998-01-01' , N'男');
insert into Student values('02' , N'小花' , '1999-12-21' , N'男');
insert into Student values('03' , N'小狗' , '1999-05-20' , N'男');
insert into Student values('04' , N'小兔' , '1999-08-06' , N'男');
insert into Student values('05' , N'猴子' ,  '2000-12-01' , N'女');
insert into Student values('06' , N'大猴' , '2003-03-01' , N'女');
insert into Student values('07' , N'拉拉' , '2010-07-01' , N'女');
insert into Student values('08' , N'么么' , '2021-01-20' , N'女');
create table Course(Cnum varchar(10),Cname nvarchar(10),Tnum varchar(10));
insert into Course values('01' , N'语文' , '02');
insert into Course values('02' , N'数学' , '01');
insert into Course values('03' , N'英语' , '03');
create table Teacher(Tnum varchar(10),Tname nvarchar(10));
insert into Teacher values('01' , N'张三');
insert into Teacher values('02' , N'李四');
insert into Teacher values('03' , N'王五');
create table SC(Snum varchar(10),Cnum varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
insert into SC values('09' , '03' , 98);

三、SQL语句练习

1.聚合函数  

select count(*) from student;
select min(score) from sc;
select sum(score) from sc;
select max(score) from sc;
select avg(score) from sc;

2.student 表中 男、女生的个数

#联合函数
select ssex,count(*) from student where ssex ='男'
union all
select ssex,count(*) from student where ssex ='女';

#student  李 的学生数量
select count(*) from student where sname like '李%';

select sname,count(sname) from student where sname like '李%';

#分组函数  group by + having 
select ssex,count(*) from student group by ssex having count(*) >1;

3.sc 每个人的选课总数

select snum, count(cnum) from sc group by snum;

4、检索至少选修两门课程的学生学号 

select snum, count(cnum) from sc group by snum having count(cnum) >=2;

5、查询每门课程被选修的学生数 

select cnum,count(*) from sc group by cnum;

6、查询选修了全部课程的学生信息 

select count(*) from course;
select snum from sc group by snum having count(*) = (select count(*) from course);

select * from student where snum 
in(select snum from sc group by snum having count(*) = (select count(*) from course));

7、查询没有学全所有课程的同学的信息 

select * from student where snum not
in(select snum from sc group by snum having count(*) = (select count(*) from course));

8、查询出只有两门课程的全部学生的学号和姓名 

select snum from sc group by snum having count(*) =2;

select * from student where snum in(select snum from sc group by snum having count(*) =2);

9、查询学过"张三"老师授课的同学的信息 

select tnum from teacher where tname = '张三';
select cnum from course where tnum = '01';
select snum from sc where cnum = '02';
select * from student where snum in(select snum from sc where cnum = (select cnum from course where tnum = (select tnum from teacher where tname = '张三')));

10、查询没学过"张三"老师授课的同学的信息 

select * from student where snum not in(select snum from sc where cnum = (select cnum from course where tnum = (select tnum from teacher where tname = '张三')));

11.1查询所有同学信息以及01课程成绩

select *,
(select score from sc where cnum ='01' and sc.snum = student.snum) sc1
from student;

11.2查询所有同学信息以及01以及02课程成绩

select *,
(select score from sc where cnum ='01' and sc.snum = student.snum) sc1,
(select score from sc where cnum ='02' and sc.snum = student.snum) sc2
from student;

11.3显示01成绩> 02成绩的同学信息

select *,
(select score from sc where cnum ='01' and sc.snum = student.snum) sc1,
(select score from sc where cnum ='02' and sc.snum = student.snum) sc2
from student ;

select * from (select *,
(select score from sc where cnum ='01' and sc.snum = student.snum) sc1,
(select score from sc where cnum ='02' and sc.snum = student.snum) sc2
from student) a where sc1 >sc2;