實例詳解MySQL數(shù)據(jù)庫的設(shè)計問題

本文主要為大家分享一篇關(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;

實例詳解MySQL數(shù)據(jù)庫的設(shè)計問題
插入數(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

實例詳解MySQL數(shù)據(jù)庫的設(shè)計問題
顯示層級,這里用到了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';

實例詳解MySQL數(shù)據(jù)庫的設(shè)計問題
當(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;

實例詳解MySQL數(shù)據(jù)庫的設(shè)計問題
插入相關(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');

實例詳解MySQL數(shù)據(jù)庫的設(shè)計問題
查找二級部門為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';

實例詳解MySQL數(shù)據(jù)庫的設(shè)計問題
查找二級部門為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)該考慮到有的人在二級部門(可能沒有三級部門,沒有四級部門),有的人在一級部門,有的人在四級部門(有一級部門,二級部門,三級部門,四級部門)。
實例詳解MySQL數(shù)據(jù)庫的設(shè)計問題

第二題:簡單的統(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;

實例詳解MySQL數(shù)據(jù)庫的設(shè)計問題

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

實例詳解MySQL數(shù)據(jù)庫的設(shè)計問題
根據(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;

實例詳解MySQL數(shù)據(jù)庫的設(shè)計問題
這里是一個不能直接用別名來排序的知識點,

? 版權(quán)聲明
THE END
喜歡就支持一下吧
點贊12 分享