Shulou(Shulou.com)06/01 Report--

This article mainly introduces the problems often encountered in the design of MySQL database, the contents of the article are carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand the problems often encountered in the design of MySQL database.

Question 1: hierarchical database design

Topic description: now there are about 100000 pieces of data, recording the employees of a department. Under the large part is a hierarchical structure, there are many sub-departments. For example, the first-level part A, the second-level department A, the second-level department A, B ", C". How to design the database, we need to count all the people under the second-level section 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

Single insertion of INSERT INTO DEPARTMENT (DEP_NAME, PARENT_ID) VALUES ('Achilles PARENT_ID null); or bulk insertion of INSERT INTO department VALUES (1), (2) (2), (3) (3), (4) (4), (5), (2), (6), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (2), (3), (2), (3), (2), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3), (3

At the display level, left join is used here, according to this level of dep_id, find its parent_id, and then connect through the left link 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 level4from department as d1left join department as d2 on d2.parent_id = d1.dep_id left join department as d3 on d3.parent_id = d2.dep_idleft join department as d4 on d4.parent_id = d3.dep_idwhere d1.depended nameplate A'

After storing the hierarchical information of the department, we began to design a table of department personnel.

Create tables and store information about department personnel

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), (2), (2), (2), (2), (2), (3), (3), (3), (2), (2), (2), (2), (3), (3), (3), (3), (3), (3), (3), (6), (6), (6), (4), (4), (4), (4), (4), (2), (2), (2) (2), (2) (2) (2), (2) (2), (2) (2), (3) (3), (3) (4), (4) (4), (4) (4) (4), (2) (2), (2) (2), (2)

Find the person whose secondary department is B and list the information of his superior department.

Select p.id, p.name, d1.dep_name as level1, d2.dep_name as level2, d3.dep_name as level3from people as pleft join department as D1 on d1.dep_id = p.dep_idleft join department as D2 on d2.dep_id = d1.parent_idleft join department as D3 on d3.dep_id = d2.parent_idwhere d1.depended nameplate B'or d2.depended nameplate B'or d3.depended nameplate B'

Find the total number of people in the second-tier department as B

Select count (*) as totalfrom people as pleft join department as D1 on d1.dep_id = p.dep_idleft join department as D2 on d2.dep_id = d1.parent_idleft join department as D3 on d3.dep_id = d2.parent_idwhere d1.depended nameplate B'or d2.depended nameplate B'or d3.depended nameplate B3. Depended nameBame1hgy4D2abc5E3def6F4ddd2B5eee2B

It should be taken into account that some people are in the second-level departments (there may be no third-level departments, no fourth-level departments), some people are in the first-level departments, and some are in the fourth-level departments (there are first-level departments, second-level departments, third-level departments, fourth-level departments).

Question 2: simple statistics

Topic description: now there are a group of students whose total scores of four subjects are more than 200, and they are arranged 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 (1meme 100rem 98je 10penny 4), (2m mei 100re 9je 9je 10je 4), (3Me 70pr 0st 180jue 40), (4meme 10meme 98pr 1rect 4), (5mine30pr 7pr 10jr 4), (6pje 8m 8je 1je 43)

Rank according to the total score of the four grades.

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 knowledge point that cannot be sorted directly by aliases.

After reading the above problems often encountered in the design of MySQL database, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.