Detailed examples of MySQL database design issues

This article mainly shares with you an article about the design issues of MySQL database. It has a good reference value and I hope it will be helpful to everyone. Let’s follow the editor to take a look, I hope it can help everyone.

First question: Hierarchical database design

Question description: There are now about 100,000 pieces of data, recording employees in a department. Under most departments is a hierarchical structure with many sub-departments. For example, the first-level part A, the second-level departments A', B', C', and the third-level departments A", B", C". How to design the database, we need to count all the people under the second-level part A'.
Analysis:
A hierarchical database design is used here

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 data

单个插入
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##. 123456##7G3

#dep_name	parent_id
A	NULL
B	1
C	1
D	2
E	2
F	3

Display the hierarchy. Left join is used here. Based on the

dep_id of this level, find its
parent_id , and then connect through the left connection to get the current department and its parent department

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

After storing the hierarchical information of the department, we start to design a department member. Table.

Create a table and store department personnel information

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 relevant test data

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

Find secondary level. The person whose department is B, and his superior department information is listed

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

Find the total number of people whose secondary department is 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_iddepartname1 hgy4D2abc5E3def6F4ddd 2B5eee2B It should be considered that some people are in second-level departments (there may be no third-level departments and no fourth-level departments), some people are in first-level departments, and some people are in fourth-level departments ( There are first-level departments, second-level departments, third-level departments, and fourth-level departments)

Second question: Simple statistics

Question description: There are now a group of students. For grades, find the students whose total score in the four subjects is greater than 200, and arrange them in reverse order.

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

Sort according to the total score of the four subjects

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;

Here is a question that cannot be directly Knowledge points for sorting by alias,

The above is the detailed content of Detailed examples of MySQL database design issues. For more information, please follow other related articles on the PHP Chinese website!