mysql表设计讲解_实例详解MySQL数据库的设计问题

本文主要为大家分享一篇关于MySQL数据库的设计问题，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧，希望能帮助到大家。

第一题：层级数据库设计

题目描述：现在有10万条左右的数据，记录一个部门的员工。大部门下是层级结构，有许多个子部门。比如，一级部分A，二级部门A’,B’,C’，三级部门A”,B”,C”。试问如何设计数据库，我们需要统计二级部分A’下的所有人数。

分析：

这里用到了一个层级数据库的设计。CREATE TABLE DEPARTMENT(

DEP_ID INT UNSIGNED AUTO_INCREMENT,

DEP_NAME VARCHAR(10) NOT NULL,

PARENT_ID INT,

PRIMARY KEY(DEP_ID)

)CHARSET=utf8;

插入数据单个插入

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_iddep_nameparent_id1ANULL

2B1

3C1

4D2

5E2

6F3

7G3

显示层级，这里用到了left join，根据这一级的dep_id，寻找它的parent_id,然后通过左连接进行连接，得到当前部门以及他的父部门。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';

当存储好了部门的层级信息后，我们就开始设计一个部门人员的表。

创建表，并存储部门人员的信息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;

插入相关的测试数据。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的人，并且列出了他的上级部门信息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的总人数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';idnamedepartment_iddepartname1hgy4D

2abc5E

3def6F

4ddd2B

5eee2B

应该考虑到有的人在二级部门(可能没有三级部门，没有四级部门)，有的人在一级部门，有的人在四级部门(有一级部门，二级部门，三级部门，四级部门)。

第二题：简单的统计

题目描述：现在有一批学生的成绩，求四门学科总分大于200的学生，并且按逆序排列。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;

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);

根据四门成绩的总分进行排序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;

这里是一个不能直接用别名来排序的知识点，