本文摘自PHP中文网，作者零到壹度，侵删。
本文主要为大家分享一篇关于MySQL数据库的设计问题，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧，希望能帮助到大家。
第一题：层级数据库设计
题目描述：现在有10万条左右的数据，记录一个部门的员工。大部门下是层级结构，有许多个子部门。比如，一级部分A，二级部门A’,B’,C’，三级部门A”,B”,C”。试问如何设计数据库，我们需要统计二级部分A’下的所有人数。
分析：
这里用到了一个层级数据库的设计。
1 2 3 4 5 6 | CREATE TABLE DEPARTMENT(
DEP_ID INT UNSIGNED AUTO_INCREMENT,
DEP_NAME VARCHAR(10) NOT NULL,
PARENT_ID INT,
PRIMARY KEY(DEP_ID)
)CHARSET=utf8;
|
插入数据
1 2 3 4 5 6 7 | 单个插入
INSERT INTO DEPARTMENT (DEP_NAME, PARENT_ID)
VALUES
('A',NULL);
或者批量插入
INSERT INTO department VALUES(1,'A',NULL),(2,'B',1),(3,'C',1),
(4,'D',2),(5,'E',2),(6,'F',3),(7,'G',3);
|
| dep_id | dep_name | parent_id |
|---|
| 1 | A | NULL |
| 2 | B | 1 |
| 3 | C | 1 |
| 4 | D | 2 |
| 5 | E | 2 |
| 6 | F | 3 |
| 7 | G | 3 |
显示层级，这里用到了left join，根据这一级的dep_id，寻找它的parent_id,然后通过左连接进行连接，得到当前部门以及他的父部门。
1 2 3 4 5 6 | select d1.dep_name as level1, d2.dep_name as level2, d3.dep_name as level3, d4.dep_name as level4
from department as d1
left join department as d2 on d2.parent_id = d1.dep_id
left join department as d3 on d3.parent_id = d2.dep_id
left join department as d4 on d4.parent_id = d3.dep_id
where d1.dep_name='A';
|
当存储好了部门的层级信息后，我们就开始设计一个部门人员的表。
创建表，并存储部门人员的信息
1 2 3 4 5 6 7 8 | create table people(
id INT UNSIGNED AUTO_INCREMENT,
name varchar(10) not null,
dep_id INT UNSIGNED,
departname varchar(10),
FOREIGN KEY (dep_id) REFERENCES department(dep_id),
primary key(id)
)charset=utf8;
|
插入相关的测试数据。
1 2 | INSERT INTO people VALUES(1,'hgy',4,'D'),(2,'abc',5,'E'),(3,'def',6,'F'),
(4,'ddd',2,'B'),(5,'eee',2,'B');
|
查找二级部门为B的人，并且列出了他的上级部门信息
1 2 3 4 5 6 | select p.id, p.name, d1.dep_name as level1, d2.dep_name as level2, d3.dep_name as level3
from people as p
left join department as d1 on d1.dep_id = p.dep_id
left join department as d2 on d2.dep_id = d1.parent_id
left join department as d3 on d3.dep_id = d2.parent_id
where d1.dep_name='B' or d2.dep_name='B' or d3.dep_name='B';
|
查找二级部门为B的总人数
1 2 3 4 5 6 | select count(*) as total
from people as p
left join department as d1 on d1.dep_id = p.dep_id
left join department as d2 on d2.dep_id = d1.parent_id
left join department as d3 on d3.dep_id = d2.parent_id
where d1.dep_name='B' or d2.dep_name='B' or d3.dep_name='B';
|
| id | name | department_id | departname |
|---|
| 1 | hgy | 4 | D |
| 2 | abc | 5 | E |
| 3 | def | 6 | F |
| 4 | ddd | 2 | B |
| 5 | eee | 2 | B |
应该考虑到有的人在二级部门（可能没有三级部门，没有四级部门），有的人在一级部门，有的人在四级部门（有一级部门，二级部门，三级部门，四级部门）。
第二题：简单的统计
题目描述：现在有一批学生的成绩，求四门学科总分大于200的学生，并且按逆序排列。
1 2 3 4 5 6 7 8 | CREATE TABLE STUDENT(
ID INT UNSIGNED AUTO_INCREMENT,
SCORE1 INT NOT NULL,
SCORE2 INT NOT NULL,
SCORE3 INT NOT NULL,
SCORE4 INT NOT NULL,
PRIMARY KEY(ID)
)CHARSET=utf8;
|
1 | INSERT INTO STUDENT VALUES(1,100,98,10,4),(2,100,9,10,4),(3,70,0,180,40),(4,10,98,1,4),(5,30,7,10,4),(6,8,88,1,43);
|
根据四门成绩的总分进行排序
1 2 | SELECT id, score1,score2,score3,score4, score1+score2+score3+score4 as total
FROM STUDENTwhere score1+score2+score3+score4 > 200 order by score1+score2+score3+score4 desc;
|
这里是一个不能直接用别名来排序的知识点，
以上就是实例详解MySQL数据库的设计问题的详细内容，更多文章请关注木庄网络博客！
相关阅读 >>
mysql示例dtid主从原理解析
mysql如何连上本地数据库
windows下mysql 5.7 设置区分大小写(敏感),设置默认编码 utf8mb4
实例详解mysql存储过程
都在这儿了！！mysql主从复制的原理和注意事项
mysql查询语句之复杂查询
mysql 常用函数总结
linux下mysql怎么设置表名？
mysql 实现设置多个主键的操作
mysql null的含义是什么
更多相关阅读请进入《mysql》频道 >>
机械工业出版社
本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。
转载请注明出处：木庄网络博客 » 实例详解MySQL数据库的设计问题
