设计MySQL数据库常常会遇到的问题

发布时间:2020-05-25 11:25:57 作者:三月
来源:PHP中文网 阅读:452

本文主要给大家介绍设计MySQL数据库常常会遇到的问题,文章内容都是笔者用心摘选和编辑的,具有一定的针对性,对大家的参考意义还是比较大的,下面跟笔者一起了解下设计MySQL数据库常常会遇到的问题吧。

第一题:层级数据库设计

题目描述:现在有10万条左右的数据,记录一个部门的员工。大部门下是层级结构,有许多个子部门。比如,一级部分A,二级部门A’,B’,C’,三级部门A”,B”,C”。试问如何设计数据库,我们需要统计二级部分A’下的所有人数。
分析:
这里用到了一个层级数据库的设计。

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数据库常常会遇到的问题
插入数据

单个插入
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_iddep_nameparent_id
1ANULL
2B1
3C1
4D2
5E2
6F3
7G3

设计MySQL数据库常常会遇到的问题
显示层级,这里用到了left join,根据这一级的dep_id,寻找它的parent_id,然后通过左连接进行连接,得到当前部门以及他的父部门。

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数据库常常会遇到的问题
当存储好了部门的层级信息后,我们就开始设计一个部门人员的表。
创建表,并存储部门人员的信息

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数据库常常会遇到的问题
插入相关的测试数据。

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数据库常常会遇到的问题
查找二级部门为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数据库常常会遇到的问题
查找二级部门为B的总人数

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';
idnamedepartment_iddepartname
1hgy4D
2abc5E
3def6F
4ddd2B
5eee2B

应该考虑到有的人在二级部门(可能没有三级部门,没有四级部门),有的人在一级部门,有的人在四级部门(有一级部门,二级部门,三级部门,四级部门)。
设计MySQL数据库常常会遇到的问题

第二题:简单的统计

题目描述:现在有一批学生的成绩,求四门学科总分大于200的学生,并且按逆序排列。

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数据库常常会遇到的问题

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数据库常常会遇到的问题
根据四门成绩的总分进行排序

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数据库常常会遇到的问题
这里是一个不能直接用别名来排序的知识点,

看完以上关于设计MySQL数据库常常会遇到的问题,很多读者朋友肯定多少有一定的了解,如需获取更多的行业知识信息 ,可以持续关注我们的行业资讯栏目的。

推荐阅读:
  1. php面试常问的问题
  2. 配置mysql主从复制经常会遇到的坑

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

mysql 数据库 l数据库

上一篇:哪个云存储比较好用?

下一篇:云主机租用哪家好?从这几个方面去看

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》