1.數(shù)據(jù)庫(kù)設(shè)計(jì)

數(shù)據(jù)庫(kù)的設(shè)計(jì)是基礎(chǔ),數(shù)據(jù)庫(kù)設(shè)計(jì)的目標(biāo)是為用戶和各種應(yīng)用系統(tǒng)提供一個(gè)基礎(chǔ)的信息設(shè)施和高效的運(yùn)行環(huán)境。

1.1 數(shù)據(jù)庫(kù)設(shè)計(jì)三個(gè)范式

• 1NF: 所有的域是原子性的

  表中的每一列應(yīng)該是不可拆分的最小單元。最低要求。

• 2NF: 所有的非主鍵字段必須與主鍵相關(guān),不能與部分主鍵相關(guān)(聯(lián)合主鍵)

  每張表中描述和表示一類數(shù)據(jù),多種不同的數(shù)據(jù)需要拆分到多張表中。

• 3NF: 所有非主鍵字段必須與主鍵直接相關(guān),而不能依賴于其他非主鍵字段

  數(shù)據(jù)不能具有傳遞依賴,每個(gè)字段與主鍵的關(guān)系是直接的而非間接的。

1.2 E-R模型

Entity Relationship Model: 實(shí)體關(guān)系模型

提供了表示實(shí)體類型、屬性和聯(lián)系的方法，用來(lái)描述現(xiàn)實(shí)世界的概念模型。

1)表之間的關(guān)系

• 一對(duì)一: 表A的一條記錄對(duì)應(yīng)表B的一條記錄,反之亦然(個(gè)人信息表和檔案表)

  實(shí)現(xiàn):

  ? 1) 外鍵添加唯一約束

  ? 2)主鍵做外鍵

• 一對(duì)多: 表A的一條記錄對(duì)應(yīng)表B的多條記錄。(部門(mén)表和雇員表)

  實(shí)現(xiàn):

  ? 添加外鍵(多方)

• 多對(duì)多: 表A的一條記錄可以對(duì)應(yīng)表B的多條記錄,表B的一條記錄也可以對(duì)應(yīng)表A的多條記錄。

  實(shí)現(xiàn):

  ? 1)添加關(guān)系表

  ? 2)設(shè)置聯(lián)合主鍵

2)實(shí)體關(guān)系模型圖(ER)

矩形框：表示實(shí)體，在框中記入實(shí)體名。

菱形框：表示聯(lián)系，在框中記入聯(lián)系名。

橢圓形框：表示實(shí)體或聯(lián)系的屬性，將屬性名記入框中。對(duì)于主屬性名，則在其名稱下劃一下劃線。

連線：實(shí)體與屬性之間；實(shí)體與聯(lián)系之間；聯(lián)系與屬性之間用直線相連，并在直線上標(biāo)注聯(lián)系的類型。

3)常用的數(shù)據(jù)庫(kù)設(shè)計(jì)工具

powerdesinger   

2.商城3C產(chǎn)品數(shù)據(jù)分析案例

• 設(shè)計(jì)實(shí)體及其關(guān)系

• 數(shù)據(jù)分析案例

  • 統(tǒng)計(jì)各個(gè)城市銷(xiāo)售額的前10名

    1) 創(chuàng)建城市維度表: 包含區(qū)域信息,省份信息和城市信息
      create table china_orgin_dim(
           d_id int,
           d_name varchar(40), # 區(qū)域信息
           p_id int,
           p_name varchar(40), # 省份信息
           c_id int,
           c_name varchar(40)  # 城市信息
      );
    2) 向城市維度表插入數(shù)據(jù)
      select distinct length(c_id) from china; 
        c_id為3表示區(qū)域,6代表省份,9代表城市,12代表區(qū)。
      
     insert into china_orgin_dim
     select c.*,c3.c_id c_id,c3.c_name c_name from china c3, 
     (select c1.c_id d_id,c1.c_name dname,c2.c_id p_id,c2.c_name p_name from china c2,
     (select * from china where superior_c_id = 0) c1
     where c2.superior_c_id = c1.c_id) c
     where c3.superior_c_id = c.p_id;   
    
    3)查看各個(gè)城市的銷(xiāo)售額
     set @rank=0;   #設(shè)置變量0
     select p_name,c_name,sales,
     case 
       when length(sales_e) >8 then concat(round(sales_e/100000000,2),'億')
       when length(sales_e) >7 then concat(round(sales_e/10000000,2),'千萬(wàn)')
     end sales_e,@rank:=@rank+1 rank from    
     (select p_name,c_name,sum(amount) sales,
     sum(price*amount) sales_e from product
     inner join order_detail on product.pid = order_detail.pid
     inner join users on users.user_no = order_detail.user_no
     inner join china_orgin_dim on users.c_id = china_orgin_dim.c_id
     group by p_name,c_name order by sales_e desc limit 10) sale;
    

• 統(tǒng)計(jì)不同年齡層次的消費(fèi)及其占比情況

  1)創(chuàng)建年齡分級(jí)表
   create table age_level(
    grade  int primary key auto_increment,
    lowage int not null,
    hiage  int not null
   );
   # 是否包含?  [10,20)
   insert into AGE_LEVEL(lowage, hiage) 
     values (10, 20),(20, 30),(30, 40),(40, 50),(50, 80);
  2)查看比例
         # 字符串連接               # 分組之后實(shí)現(xiàn)銷(xiāo)量(order_detail)聚合運(yùn)算
  select concat(lowage,'-',hiage),sum(amount) sales,
       # 流程函數(shù)(銷(xiāo)售額)   sum(price * amount) 按照年齡分組實(shí)現(xiàn)銷(xiāo)售額的聚合     
  case 
     when length(sum(price*amount)) >8 then concat(round(sum(price*amount)/100000000,2),'億')
       when length(sum(price*amount)) >7 then concat(round(sum(price*amount)/10000000,2),'千萬(wàn)')
   end sales_e,
       # 比例  該年齡層級(jí)的銷(xiāo)售額/總銷(xiāo)售額  * 100    %    
  concat(round(sum(price*amount)/(select sum(price*amount) from order_detail
   inner join product on order_detail.pid = product.pid)*100,2),'%')
             
  from order_detail
  inner join users on order_detail.user_no = users.user_no
  inner join product on order_detail.pid = product.pid
  inner join age_level on age >= lowage and age < hiage  # 年齡范圍[10,20)
  group by grade;  
                                                                                                                     
  

* 統(tǒng)計(jì)2018年度月銷(xiāo)售量及環(huán)比變化比例

  select thisMonth.period as Period,
    thisMonth.amount as thisMonthAmount,lastmonth.amount as lastMonthAmount,
    concat(round((thisMonth.amount-lastmonth.amount)*100/lastmonth.amount,2),'%') as Rate 
  from
             # 提取(從日期中提取指定的部分)
      (select extract(YEAR_MONTH from order_date) as period,sum(amount) as amount
      from order_detail
      group by period) as thisMonth
    left join
      (select extract(YEAR_MONTH from (DATE_ADD(order_date,INTERVAL 1 MONTH))) as period,sum(amount) as amount
    from order_detail
      group by period) as lastMonth
      on thisMonth.period=lastMonth.period