MySQL表的设计及查询

一、表的设计

总体思路步骤：

1、梳理清楚需求中的“实体”；2、梳理清楚实体之间的关系。

例题：

1、设计一个考勤系统。（考勤系统，包含员工表，考勤记录表）

解答：主要考虑记录表中的记录信息，是如何关联到员工表，员工与记录关系为1:m。

先写“1”的那个实体，foreign key中“1”的实体在references后面

create table emp(

id int primary key,

  name varchar(20)

);
create table info(

id int primary key,

  emp_id int,

  info_date timestamp,

  foreign key (emp_id) references emp(id)

);

2、学校宿舍管理系统，要求包含宿舍信息，学生信息，每日的宿舍查房记录。

解答：主要考虑学生与宿舍的关系：m:1，宿舍的查房记录是根据宿舍来查的，与宿舍有关系，一个宿舍可以多次查房，宿舍与查房记录是1:m的关系。

create table dormitory(

	id int primary key,

  number varchar(20)

);


create table student(

	id int primary key,

  name varchar(20),

  dormitory_id int,

  foreign key (dormitory_id) references dormitory(id)

);


create table info(

	id int primary key,

  dormitory_id int,

  status bit,

  info_date timestamp,

  foreign key (dormitory_id) references dormitory(id)

);

3、车辆违章系统，包含用户表，车辆表，违章信息表。违章信息表中包含用户和车辆的违章信息。

解答：用户可以拥有多辆车，关系为1:m，题目已经说明违章信息包括用户和车辆，说明违章信息表中要记录用户和车辆，一个用户可以有多次违章记录，用户与违章记录关系为1:m，一辆车也可以有多次违章记录，车辆与违章记录关系也为1:m。

create table user(

	id int primary key,

  name varchar(20)

);

create table cars(

	id int primary key,

  name varchar(20),

  user_id int,

  foreign key (user_id) references user(id)

);

create table info(

	id int primary key,

  user_id int,

  cars_id int,

  foreign key (user_id) references user(id),

  foreign key (cars_id) references cars(id)

);

4、设计一个学校食堂管理系统。（学校食堂管理系统，包含食堂表，食堂窗口表，窗口收费记录表）

解答：一个食堂有多个仓口卖饭，关系为1:m，每个仓口卖饭可以有很多次，仓口与收费记录也是1:m。

create table hall(

	id int primary key,

  name varchar(20)

);

create table hall_opening(

	id int primary key,

  name varchar(20),

  hall_id int,

  foreign key (hall_id) references hall(id)

);

create table info(

	id int primary key,

  price int,

  info_date timestamp,

  hall_opening_id int,

  foreign key (hall_opening_id) references hall_opening(id)

);

二、查询

例题：

1、有一张员工表emp，字段：姓名name，性别sex，部门depart，工资salary。查询以下数据：

（1）查询男女员工的平均工资

解：平均值使用聚合函数avg，并且按照性别男女分组，group by 性别字段

select sex,avg(salary) from emp group by sex;

（2）查询各部门的总薪水

解：总薪水使用聚合函数sum取薪水字段求和，并且按照部门字段分组，group by 部门字段

select depart,sum(salary) from emp group by depart;

（3）查询总薪水排名第二的部门

解：order by语句先按照总薪水排序，之后取第二条数据，可以使用分页，每一页1条数据，第二页就是该结果

select depart,sum(salary) from emp group by depart order by sum(salary) desc limit 1,1;

（4）查询姓名重复的员工信息

解：名字重复，说明同一个名字有多条数据，可以先按照名字分组，分组之后再过滤行数大于1的，就表示同一个名字至少有2条记录，重复了

select name from emp group by name having count(name)>1;

（5）查询各部门薪水大于10000的男性员工的平均薪水

解：这里需要注意题目要求是查询薪水大于10000的男性员工，这个是在按部门分组前就过滤，在过滤后的结果集中再查询各个部门的平均薪水

select depart,avg(salary) from emp where salary>10000 and sex='男' group by depart;

2、写出以下数据库的查询条件。有两个表分别如下：

表A(varchar(32) name， int grade)  数据：zhangshan 80， lisi 60， wangwu 84

表B(varchar(32) name， int age)     数据：zhangshan 26， lisi 24， wangwu 26， wutian 26

写SQL语句得到如下查询结果：

| NAME   | GRADE | AGE |

| --------- | ----- | ---- |

| zhangshan | 80  | 26  |

| lisi   | 60  | 24  |

| wangwu  | 84  | 26  |

| wutian  | null | 26  |

解：主要考察使用关联查询时需要使用内联还是外联，这里wutian再A表中没有记录，但还是需要返回结果，所以应该将B表作为外表进行外连接查询

SELECT B.NAME, A.grade, B.age FROM B left join A ON A.NAME = B.NAME;

3、有员工表、部门表和薪资表，根据查询条件写出对应的sql。现在有员工表、部门表和薪资表。部门表depart字段depart_id， name；员工表 staff字段staff_id， name， age， depart_id；薪资表salary 字段salary_id，staff_id，salary，month。

（1）求每个部门'2016-09'月份的部门薪水总额

解：mysql中年和月的函数分别是year(字段)，month(字段)。查询要求的是每个部门的信息，所以要按照部门进行分组，部门和员工为1:m，员工与薪水为1:m，查询要求有部门，有薪水，所以必须关联3张表查询

SELECT

	dep.NAME,

	sum( sal.salary ) 

FROM

	salary sal

	JOIN staff sta ON sal.staff_id = sta.staff_id

	JOIN depart dep ON sta.depart_id = dep.depart_id 

WHERE

	YEAR ( sal.MONTH ) = 2016 

	AND MONTH ( sal.MONTH ) = 9 

GROUP BY

	dep.depart_id;

（2）求每个部门的部门人数，要求输出部门名称和人数

解：查询要求的信息有部门，有人数，人数只能从员工表中获取，所以关联表为部门表/员工表。按照部门id分组查询员工id的行数

SELECT

	dep.NAME,

	count( sta.staff_id ) 

FROM

	staff sta

	JOIN depart dep ON dep.depart_id = sta.depart_id 

GROUP BY

	sta.depart_id;

（3）求公司每个部门的月支出薪资数，要求输出月份和本月薪资总数

解：按照题目要求，查询信息有部门/月/每个部门每月薪资总额，其中薪水信息再薪水表中，每个员工可以有多条薪资记录（不同时间）。所以需要按照部门分组，再按照月份分组，查询分组的部门在不同月份下的总薪水

SELECT

	dep.NAME,

	sal.MONTH,

	sum( sal.salary ) 

FROM

	depart dep

	JOIN staff sta ON dep.depart_id = sta.depart_id

	JOIN salary sal ON sta.staff_id = sal.staff_id 

GROUP BY

	dep.depart_id,

	sal.MONTH;