【第七弹】数据库设计

1.数据库设计简介

2.表关系之一对多

3.表关系之多对多

4.表关系之一对一

5.数据库设计案例

分析

音乐专辑表名：Music

字段名	数据类型	说明
title	varchar(32)	专辑名
alias	varchar(32)	专辑别名
image	varchar(64)	封面图片
style	varchar(8)	流派（如经典、流行、民谣、电子等）
type	varchar(4)	类型（专辑、单曲等）
medium	varchar(4)	介质（CD、黑胶、数字等）
publish_time	date	发行时间
publisher	varchar(16)	出版者
number	tinyint	唱片数
barcode	bigint	条形码
summary	varchar(1024)	简介
artist	varchar(16)	艺术家
id	int	编号（唯一）

曲目表名： Song

字段名	数据类型	说明
name	varchar(32)	歌曲名
serial_number	tinyint	歌曲序号
id	int	编号（唯一）

评论表名：Review

字段名	数据类型	说明
content	varchar(256)	评论内容
rating	tinyint	评分（1~5）
review_time	datetime	评论时间

用户表名：User

字段名	数据类型	说明
username	varchar(16)	用户名（唯一）
image	varchar(64)	用户头像图片地址
signature	varchar(64)	个人签名，例如（万般各所是 一切皆圆满）
nickname	varchar(16)	用户昵称
id	int	用户编号(主键)

表关系：

答案：

-- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述

/*

分析：

1. 员工编号，员工姓名，工资 信息在emp 员工表中

2. 职务名称，职务描述 信息在 job 职务表中

3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id

*/

-- 隐式内连接

SELECT

emp.id,

emp.ename,

emp.salary,

job.jname,

job.description

FROM

emp,

job

WHERE

emp.job_id = job.id;

SELECT

*

FROM

emp;

SELECT

*

FROM

job;

-- 显式内连接

SELECT

emp.id,

emp.ename,

emp.salary,

job.jname,

job.description

FROM

emp

INNER JOIN job ON emp.job_id = job.id;

-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

/*

分析：

1. 员工编号，员工姓名，工资 信息在emp 员工表中

2. 职务名称，职务描述 信息在 job 职务表中

3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id

4. 部门名称，部门位置 来自于 部门表 dept

5. dept 和 emp 一对多关系 dept.id = emp.dept_id

*/

-- 隐式内连接

SELECT

emp.id,

emp.ename,

emp.salary,

job.jname,

job.description,

dept.dname,

dept.loc

FROM

emp,

job,

dept

WHERE

emp.job_id = job.id

and dept.id = emp.dept_id

;

-- 显式内连接

SELECT

emp.id,

emp.ename,

emp.salary,

job.jname,

job.description,

dept.dname,

dept.loc

FROM

emp

INNER JOIN job ON emp.job_id = job.id

INNER JOIN dept ON dept.id = emp.dept_id

;

-- 3.查询员工姓名，工资，工资等级

/*

分析：

1. 员工姓名，工资 信息在emp 员工表中

2. 工资等级 信息在 salarygrade 工资等级表中

3. emp.salary >= salarygrade.losalary  and emp.salary <= salarygrade.hisalary

*/

SELECT

emp.ename,

emp.salary,

t2.*

FROM

emp,

salarygrade t2

WHERE

emp.salary >= t2.losalary

AND emp.salary <= t2.hisalary

-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

/*

分析：

1. 员工编号，员工姓名，工资 信息在emp 员工表中

2. 职务名称，职务描述 信息在 job 职务表中

3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id

4. 部门名称，部门位置 来自于 部门表 dept

5. dept 和 emp 一对多关系 dept.id = emp.dept_id

6. 工资等级 信息在 salarygrade 工资等级表中

7. emp.salary >= salarygrade.losalary  and emp.salary <= salarygrade.hisalary

*/

SELECT

emp.id,

emp.ename,

emp.salary,

job.jname,

job.description,

dept.dname,

dept.loc,

t2.grade

FROM

emp

INNER JOIN job ON emp.job_id = job.id

INNER JOIN dept ON dept.id = emp.dept_id

INNER JOIN salarygrade t2 ON emp.salary BETWEEN t2.losalary and t2.hisalary;

-- 5.查询出部门编号、部门名称、部门位置、部门人数

/*

分析：

1. 部门编号、部门名称、部门位置 来自于部门 dept 表

2. 部门人数: 在emp表中 按照dept_id 进行分组，然后count(*)统计数量

3. 使用子查询，让部门表和分组后的表进行内连接

*/

select * from dept;

select dept_id, count(*) from emp group by dept_id;

SELECT

dept.id,

dept.dname,

dept.loc,

t1.count

FROM

dept,

(

SELECT

dept_id,

count(*) count

FROM

emp

GROUP BY

dept_id

) t1

WHERE

dept.id = t1.dept_id

所学内容均来自于黑马程序员

所学内容均来自于黑马程序员

所学内容均来自于黑马程序员