一、多表设计
数据库设计范式
- 第一范式(确保每列保持原子性)
第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。 - 第二范式就是要有主键，要求其他字段都依赖于主键。
- 没有主键就没有唯一性，没有唯一性在集合中就定位不到这行记录，所以要主键。
- 其他字段为什么要依赖于主键？
因为不依赖于主键，就找不到他们。更重要的是，其 他字段组成的这行记录和主键表示的是同一个东西，而主键是唯一的，它们只需要依 赖于主键，也就成了唯一的
- 第三范式,确保每列都和主键列直接相关,而不是间接相关,要求一个数据库表中不包含 已在其它表中包含的非主关键字信息
外键
外键：引用另外一个数据表的某条记录。
外键列类型与主键列类型保持一致
数据表之间的关联/引用关系是依靠具体的主键（primary key）和外键 （foreign key）建立起来的
约束名改则（约定俗成）：
注：
- 当主表中没有对应的记录时，不能将记录添加到从表。
- 不能更改主表中的值而导致从表中的记录孤立。
- 从表存在与主表对应的记录，不能从主表中删除该行。
- 删除主表前，先删从表
代码示例如下：
/*
数据库概念
mysql数据库
安装
sql
ddl
创建数据库
创建表
dml
insert into
update
delete
dql
select 结果 from 表 where 条件 [group by 条件 order by 列 limit ]
函数 单行函数 字符 逻辑 日期 数学函数
分组函数
数据库设计范式(数据库设计规则) 如何将多个信息合理的进行组织]
设计3范式
第一范式: 满足列的原子性(不可拆分)
联系范式-->邮箱,电话,qq 可以拆分
第二范式: 数据库表中必须要有主键,能唯一的表示某条记录的列(有些表中可以没有主=键)
第三范式: 一张表中存储一类信息, 在一张表中关联其他表中的数据时,只需要关联主键列即可.
专业信息表
专业编号(主键)
专业名称
学生与专业是有关系的,如何建立学生和专业表的关系.
*/
DROP TABLE stu
CREATE TABLE major(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
)
-- 修改表 为表添加一个列 一对多 多对一
ALTER TABLE student ADD majorid INT
-- 外键 在一个表中外键是用来与另一个表的主键关联的
-- 主键 不能为空 唯一 检查
-- 外键有两种情况:
-- 1.不加外键约束
-- 可以任意的对表数据进行操作,即使两个表中的数据对应不上也没有问题
-- 2.添加外键约束
-- 添加外键约束后,两张表操作时,不能导致外键列和主键列对应关系不成立
ALTER TABLE student ADD CONSTRAINT fk_student_major_majorid FOREIGN KEY(majorid) REFERENCES major(id)
-- 删除外键约束
ALTER TABLE student DROP FOREIGN KEY fk_student_major_majorid
-- 学生选课 一个学生至少选择2个课程
-- 课程信息表 多对对关系设计
CREATE TABLE course(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
)
-- 添加一个学生选课表 学生和课程关系表 放一个学生学号外键, 放一个课程外键
CREATE TABLE student_course(
stunumber INT,
courseid INT
)
ALTER TABLE student_course ADD CONSTRAINT fk_student_course_student_stunumber FOREIGN KEY(stunumber) REFERENCES student(number)
ALTER TABLE student_course ADD CONSTRAINT fk_student_course_course_courseid FOREIGN KEY(courseid) REFERENCES course(id)二、关联查询
内连接(inner join)
- 把满足了条件的两张表中的交集数据查询出来
- 语法:
左外关联(left join)
- 把左表的全部元素和满足左表中的右表元素查询出来
- 语法：
右外关联(right join)
- 把右表的全部元素和满足右表中的右表元素查询出来
- 语法：
子查询
含义：出现在其他语句中的select语句，称为子查询或内查询；外部的查询语句，称为主查询或外查询。
分类：
- 按子查询出现的位置：
- from后面：支持表子查询
- where：支持标量子查询，列子查询
- 按功能、结果集的行列数不同：
- 标量子查询（结果集只有一行一列）
- 列子查询（结果集只有一列多行）
- 表子查询（结果集一般为多行多列
代码示例如下：
-- 关联查询 多表关联在一起查询
-- 学号 姓名 性别 电话 专业名称 -- 信息来自于两张表中
-- 关联时没有任何的限制,会产生笛卡尔乘积现象 ,这是我们不想要的结果
SELECT * FROM student,major
-- 关联查询 内关联 左外关联 右外关联
-- select number,name,gender,phone,name from student inner join major on majorid = id
SELECT
s.number,s.name,s.gender,s.phone,m.name mname
FROM student s
INNER JOIN major m ON s.majorid = m.id -- 把满足条件关联在一起
SELECT
s.number,s.name,s.gender,s.phone,m.name mname
FROM student s,major m
WHERE s.majorid = m.id -- 先产生一个笛卡尔乘积 然后在条件筛选
-- 左外连接 特点 即使不满足连接条件,也会把左边表中的所有数据查询出来
SELECT
s.number,
s.name,
s.gender,
s.phone,
m.name mname
FROM
student s LEFT JOIN major m ON s.majorid = m.id
-- 右外连接 即使不满足连接条, 也要把右边表中的所有数据查询出来
SELECT
s.number,
s.name,
s.gender,
s.phone,
m.name mname
FROM
student s RIGHT JOIN major m ON s.majorid = m.id
-- 统计每个专业下有多少学生
SELECT
COUNT(number),
m.name mname
FROM
student s RIGHT JOIN major m ON s.majorid = m.id
GROUP BY m.name
