設(shè)計(jì)MySQL數(shù)據(jù)庫(kù)常常會(huì)遇到的問(wèn)題

本文主要給大家介紹設(shè)計(jì)MySQL數(shù)據(jù)庫(kù)常常會(huì)遇到的問(wèn)題，文章內(nèi)容都是筆者用心摘選和編輯的，具有一定的針對(duì)性，對(duì)大家的參考意義還是比較大的，下面跟筆者一起了解下設(shè)計(jì)MySQL數(shù)據(jù)庫(kù)常常會(huì)遇到的問(wèn)題吧。

第一題：層級(jí)數(shù)據(jù)庫(kù)設(shè)計(jì)

題目描述：現(xiàn)在有10萬(wàn)條左右的數(shù)據(jù)，記錄一個(gè)部門的員工。大部門下是層級(jí)結(jié)構(gòu)，有許多個(gè)子部門。比如，一級(jí)部分A，二級(jí)部門A’,B’,C’，三級(jí)部門A”,B”,C”。試問(wèn)如何設(shè)計(jì)數(shù)據(jù)庫(kù)，我們需要統(tǒng)計(jì)二級(jí)部分A’下的所有人數(shù)。
分析：
這里用到了一個(gè)層級(jí)數(shù)據(jù)庫(kù)的設(shè)計(jì)。

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

插入數(shù)據(jù)

單個(gè)插入
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);

顯示層級(jí)，這里用到了left join，根據(jù)這一級(jí)的dep_id，尋找它的parent_id,然后通過(guò)左連接進(jìn)行連接，得到當(dāng)前部門以及他的父部門。

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

當(dāng)存儲(chǔ)好了部門的層級(jí)信息后，我們就開(kāi)始設(shè)計(jì)一個(gè)部門人員的表。
創(chuàng)建表，并存儲(chǔ)部門人員的信息

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;

插入相關(guān)的測(cè)試數(shù)據(jù)。

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

查找二級(jí)部門為B的人，并且列出了他的上級(jí)部門信息

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

查找二級(jí)部門為B的總?cè)藬?shù)

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

應(yīng)該考慮到有的人在二級(jí)部門（可能沒(méi)有三級(jí)部門，沒(méi)有四級(jí)部門），有的人在一級(jí)部門，有的人在四級(jí)部門（有一級(jí)部門，二級(jí)部門，三級(jí)部門，四級(jí)部門）。

第二題：簡(jiǎn)單的統(tǒng)計(jì)

題目描述：現(xiàn)在有一批學(xué)生的成績(jī)，求四門學(xué)科總分大于200的學(xué)生，并且按逆序排列。

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

根據(jù)四門成績(jī)的總分進(jìn)行排序

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;

這里是一個(gè)不能直接用別名來(lái)排序的知識(shí)點(diǎn)，

看完以上關(guān)于設(shè)計(jì)MySQL數(shù)據(jù)庫(kù)常常會(huì)遇到的問(wèn)題，很多讀者朋友肯定多少有一定的了解，如需獲取更多的行業(yè)知識(shí)信息 ，可以持續(xù)關(guān)注我們的行業(yè)資訊欄目的。