本文主要為大家分享一篇關(guān)于mysql數(shù)據(jù)庫的設(shè)計問題,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧,希望能幫助到大家。
第一題:層級數(shù)據(jù)庫設(shè)計
題目描述:現(xiàn)在有10萬條左右的數(shù)據(jù),記錄一個部門的員工。大部門下是層級結(jié)構(gòu),有許多個子部門。比如,一級部分a,二級部門a’,b’,c’,三級部門a”,b”,c”。試問如何設(shè)計數(shù)據(jù)庫,我們需要統(tǒng)計二級部分a’下的所有人數(shù)。
分析:
這里用到了一個層級數(shù)據(jù)庫的設(shè)計。
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ù)
單個插入 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 |
顯示層級,這里用到了left join,根據(jù)這一級的dep_id,尋找它的parent_id,然后通過左連接進行連接,得到當(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)存儲好了部門的層級信息后,我們就開始設(shè)計一個部門人員的表。
創(chuàng)建表,并存儲部門人員的信息
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)的測試數(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');
查找二級部門為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的總?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';
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 |
應(yīng)該考慮到有的人在二級部門(可能沒有三級部門,沒有四級部門),有的人在一級部門,有的人在四級部門(有一級部門,二級部門,三級部門,四級部門)。
第二題:簡單的統(tǒng)計
題目描述:現(xiàn)在有一批學(xué)生的成績,求四門學(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ù)四門成績的總分進行排序
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;
這里是一個不能直接用別名來排序的知識點,