数据库基础知识二

数据库基础知识二

一、数据库三范式

第一范式（1NF）： 列不可分，确保每一列保持原子性
举例：用一列存储用户的地址 address varchar(128)
“江苏省扬州市邗江区xxx街道xxx小区xxx号”
应该拆分为：province省 city市 area市 detail详细地址
第二范式（2NF）： 表要有主键，确保每一列都与主键相关，消除部分函数依赖，
即表中所有非关键字段都完全依赖于任意一组候选关键字
选课关系(学号, 姓名, 年龄, 课程名称, 成绩, 学分)
(学号, 课程名称) → (姓名, 年龄, 成绩, 学分）
(课程名称) → (学分)
(学号) → (姓名, 年龄)
第三范式（3NF）： 确保每一列都和主键直接相关，而不是间接相关，即不存在非关键字段对任一候选关键字段的传递函数依赖，
即如果存在A →B，B →C，那么A →C（C依赖于A），则为传递依赖
学生关系(学号, 姓名, 年龄, 所在学院, 学院地点, 学院电话)
(学号) → (姓名, 年龄, 所在学院, 学院地点, 学院电话)
(学号) → (所在学院) → (学院地点, 学院电话)

二、数据类型的选择和约束

在不浪费空间的情况下要保证空间够用

三、表之间的关系

一对一
一对多
多对多

四、设计一个学生考试系统

1、科目信息（科目编号，科目名称）
2、课程信息(课程编号，课程名)
3、老师信息（编号，姓名，性别，课程号，教龄，职称）
4、考试信息（考试编号，开始时间，结束时间，考试地点，科目名称）
5、学生信息（学号，姓名，性别，生日，专业）
6、成绩信息（学号，考试编号，分数）

-- 1、科目信息（科目编号，科目名称）
create table if not exists subject (
	id int unsigned primary key auto_increment comment '科目编号',
	name varchar(64) not null comment '科目名称'
);
-- 2、课程信息(课程编号，课程名)
create table if not exists course (
	id int unsigned primary key auto_increment comment '课程号',
	name varchar(32) not null comment '课程名'
);
-- 3、老师信息（编号，姓名，性别，课程号，教龄，职称）
create table if not exists teacher (
	id int primary key auto_increment comment '编号',
	name varchar(32) not null comment '姓名',
	sex char(1) not null comment '性别',
  c_id int unsigned comment '课程号',
	work_age tinyint comment '教龄',
  level varchar(32) comment '职称'
);
-- 4、考试信息（考试编号，考试名称，开始时间，结束时间，考试地点，科目名称）
create table if not exists exam (
	id int primary key auto_increment comment '考试编号',
	name varchar(32) not null comment '考试名称',
	start time not null comment '开始时间',
	end time not null comment '结束时间',
	date date not null comment '考试日期',
	place varchar(64) not null comment '考试地点',
	course varchar(32) not null comment '科目名称'  
);
-- 5、学生信息（学号，姓名，性别，生日，专业）
create table if not exists student (
	id int unsigned primary key auto_increment comment '学号',
	name varchar(32) not null comment '姓名',
	sex char(1) not null comment '性别',
	birthday datetime comment '生日',
  subject varchar(32) comment '专业'
);
-- 6、成绩信息（学号，考试编号，分数）
create table if not exists score (
	s_id int unsigned comment '学号',
	e_id int comment '考试编号',
	score tinyint comment '分数',
	primary key(s_id,e_id),
	foreign key(s_id) references student(id),
	foreign key(e_id) references exam(id)
);

五、对数据的四种操作（增删改查）：

1、添加:
insert into 表名(字段名) values(取值);
单条插入：一条insert into语句插入单条数据

insert into student(name,sex,birthday,subject) values('张飞','男','1999-10-8','软件工程');

批量插入 : 一条insert into语句插入多条数据(自增长主键不需要在添加列表里)

insert into student (name,sex,birthday,subject) values
('关羽','男','1999-9-8','软件工程'),
('王玉','女','1999-12-19','计算机'),
('卡卡西','男','1999-12-15','千鸟'),
('宁智波佐助','男','1999-11-15','须佐能乎'),
('张一','男','1999-1-1','电影'),
('张二','男','1999-1-2','电影'),
('张三','男','1999-1-3','电影');

insert into student (name,sex,birthday,subject) values
('关羽','男','1999-9-8','软件工程'),
('王玉','女','1999-12-19','计算机'),
('卡卡西','男','1999-12-15','千鸟'),
('宁智波佐助','男','1999-11-15','须佐能乎'),
('张一觉','男','1998-1-1','电影'),
('张三督','男','1996-1-3','电影'),
('张二城','男','1994-1-2','电影'),
('张二收到','男','1913-1-2','电影'),
('张一是','男','1914-1-1','电影'),
('张二爱吃','男','1915-1-2','电影'),
('张二收藏','男','1999-1-2','电影');

insert into exam(name,start,end,date,place,course) values
('2022年高数期末考试A','08:00:00','10:00:00','2022-01-01','杨教1108','高数'),
('18级数字电路A','8:30:00','10:30:00','2021-12-5','扬教2204','数字电路'),
('18级数据结构B','8:30:00','10:30:00','2021-1-4','扬教310','数据结构'),
('2022级期末考试','8:10:00','9:10:00','2022-1-7','杨门','军训'),
('2022级期末考试','8:20:00','9:20:00','2022-1-7','东厂','电工电子'),
('英语2021届','10:30','12:00','2022-1-4','西厂','英语'),
('2021年期末考试','8:30:00','11:00:00','2021-12-17','扬教2108','计算机网络'),
('2021年ETC4','8:30:10','12:20:00','2021-06-01','扬教3306','英语四级');

insert into score(s_id,e_id,score) values
(1,1,98),
(2,1,90),
(3,1,77),
(4,1,74),
(5,1,87),
(7,20,90),
(2,9,90),
(2,10,80),
(4,9,77),
(2,7,88),

注：

INSERT INTO score(s_id,e_id,score) values(195,172,75);

出现问题
Cannot truncate a table referenced in a foreign key constraint (detest_02.score, CONSTRAINT score_ibfk_1 FOREIGN KEY (s_id) REFERENCES detest_02.student (id))
解决办法：
方法一：手动删除外键

再把外键约束添加回来时无法保存

需要把表中数据清空，才能保存外键

truncate table score;

方法二：使用命令置0即可

SET foreign_key_checks = 0; //删除外键
SET foreign_key_checks = 1; //添加外键
SELECT @@FOREIGN_KEY_CHECKS;  //查看状态

2、修改：
update 表明 set 字段名=新的值,字段名=新的值 where 条件
把 表中所有专业改为计嵌

update student set subject=’计嵌’;

把 表中id为62的学生的专业改为计嵌

update student set subject=’计嵌’ where id = 62;

3、删除：
删除id=67这一行

delete from student where id=67;

删除表中数据（再插入时id接着原来表的id继续在后面插入）

delete from student；

快速截断一张表中的所有数据(无条件)(再插入时id从1开始)

truncate table student;

4、查询：
查某一列

select name 
from student;

查某几列

selecet id,name,subject 
from student;

查所有列

select * 
from student;

查询学生表中 专业为软件工程 的学生信息

select * 
from student 
where subject = ‘软件工程’;

查询学生表中 专业为软件工程 且 性别为女 的学生信息

select * 
from student 
where subject = ‘软件工程’ and sex = ‘女’;

查询学生表中 专业为软件工程 或 性别为女 的学生信息

select * 
from student 
where subject = ‘软件工程’ or sex = ‘女’; 

查询学生表中 专业不是软件工程 的学生信息

select * 
from student 
where subject != ‘软件工程’;

排序（默认正序排列）
按照出生日期倒序排列

select * 
from student 
order by birthday desc;

指定多个字段参与排序，先排前面的

select * 
from student 
order by birthday,sex;

有条件进行排序，排序放在条件之后

select * 
from student 
where subject = ‘软件工程’ 
order by birthday, sex;

多表查询：
–查询所有学生的考试信息（学号，姓名，考试名称，科目，考试分数）
–姓名和学号在student表中
–考试名称和科目在exam表中
–分数在score表中
–从student,exam,score,这三张表中提取5个字段作为数据合成一张表
92式连表（1992年）
一次性做完所有表的笛卡尔积，然后进行筛选，
如果表的数据多或者参与连表的个数多，极其耗费内存且语法结构不够清晰；

select student.id,student.name,exam.name,exam.course,score.score 
from student,exam,score
where student.id = score.s_id and score.e_id = exam.id;

三张表被连为一张表，但是里面会有很多连的是错误的数据，
比如说某一个分数不是某个学生的、某一个分数不是某一场考试的也会连起来
99式连表（1999年）
先做两表的笛卡尔积之后立即剔除连接错误的笛卡尔积，
再连接下一张表做笛卡尔积，
再立即进行筛选，大大加少了工作量

select student.id,student.name,exam.name,exam.course,score.score 
from score inner join student           
on score.s_id = student.id            
inner join exam                       
on score.e_id = exam.id；