Exemples détaillés de problèmes de conception de bases de données MySQL

Cet article partage principalement avec vous un article sur les problèmes de conception de la base de données MySQL. Il a une bonne valeur de référence et j'espère qu'il sera utile à tout le monde. Suivons l'éditeur pour y jeter un œil, j'espère que cela pourra aider tout le monde.

Question 1 : Conception d'une base de données hiérarchique

Description de la question : Il existe actuellement environ 100 000 éléments de données, enregistrant les employés d'un service. Sous la plupart des départements se trouve une structure hiérarchique avec de nombreux sous-départements. Par exemple, la partie A de premier niveau, les départements de deuxième niveau A', B', C', les départements de troisième niveau A", B", C". Comment concevoir une base de données ? Nous devons compter toutes les personnes sous la partie A' de deuxième niveau
Analyse :
Une conception de base de données hiérarchique est utilisée ici

CREATE TABLE DEPARTMENT(
    DEP_ID INT UNSIGNED AUTO_INCREMENT,
    DEP_NAME VARCHAR(10) NOT NULL,
    PARENT_ID INT,    
    PRIMARY KEY(DEP_ID)
)CHARSET=utf8;

Insérer des données

单个插入
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

Affichez la hiérarchie. La jointure gauche est utilisée ici. En fonction du dep_id de ce niveau, recherchez son parent_id, puis connectez-le via la jointure gauche pour obtenir le département actuel et son département parent. .>

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

Après avoir stocké les informations hiérarchiques du département, nous commençons à concevoir un tableau du personnel du département

Créer le tableau et stocker les informations du personnel du département

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;

Insérer les données de test pertinentes

INSERT INTO people VALUES(1,'hgy',4,'D'),(2,'abc',5,'E'),(3,'def',6,'F'),
        (4,'ddd',2,'B'),(5,'eee',2,'B');

Trouver la personne dont le département secondaire est B et répertorier les informations de son département supérieur

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

Trouver le nombre total de personnes dans le département de deuxième niveau comme B

Il faut considérer que certaines personnes sont dans le département de deuxième niveau (il peut n'y avoir pas de troisième- département de niveau et pas de département de quatrième niveau), certaines personnes sont dans des départements de premier niveau et d'autres dans des départements de quatrième niveau (il y a des départements de premier niveau, des départements de deuxième niveau, des départements de troisième niveau et des départements de quatrième niveau )

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

Deuxième question : Statistiques simples

Description du titre : Il existe maintenant un groupe de notes d'élèves. Trouvez les élèves avec un score total supérieur à 200 dans. quatre matières, et organisez-les dans l'ordre inverse >

Trier selon le score total des quatre matières

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

Voici. un point de connaissance qui ne peut pas être trié directement par alias

.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!