数据库设计详解(数据库的表关系以及多表查询+命令和备注汇总)

数据库设计详解

数据库设计简介

1.软件的研发步骤
需求分析–>设计–>编码–>测试–>安装部署

2.数据库设计概念

• 数据库设计是根据业务系统的相关要求，结合所选用的DBMS，为当前的业务系统构造出最优的数据存储模型。
• 建立数据库的表结构以及表与表之间的关联关系的过程。
• 主要其实就是设计有哪些表？表中有哪些字段？表与表之间存在怎样的关联？
  3.数据库的设计步骤
• 需求分析（数据是什么？数据有哪些属性？数据和属性的特点是什么？）
• 逻辑分析（通过ER图对数据库进行逻辑建模，不需要考虑所选用的数据库管理系统）
  示例（ER图）：
  
• 维护设计（对新的需求进行建表；对表进行优化）
• 物理设计（根据数据库自身特点把逻辑设计转为物理设计）

数据库的表关系

表关系之一对多（多对一）

举例：学生 和 班级
一个班级有多个学生，一个学生对应一个班级

表关系之多对多

举例：商品 和 订单
一个商品对应一个订单，一个订单包含多个商品
实现方式：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

表关系之一对一

举例：一个人对应一张身份证
一对一关系多用于多表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放一张表，用于提升查询性能

数据库多表查询

部分知识回顾：

多表查询

笛卡尔积：存在A与B两个集合，取A，B两个集合的所有情况。

从多张表查询数据称之为多表查询。

• 连接查询：
  内连接：相当于查询A、B交集数据；
  外连接
  左外连接：相当于查询A表数据和交集的部分数据；
  右外连接：相当于查询B表所有数据和交集部分数据。
• 子查询

内连接

查询语法：

-- 隐式内连接
select 字段列表 from 表1,表2... where 条件;
-- 显示内连接
select 字段列表 from 表1 [inner] join 表2 on 条件;

外连接

外连接查询语法：

• 左外连接

select 字段列表 from 表1 left [outer] join 表2 on 条件;

• 右外连接

select 字段列表 from 表1 right [outer] join 表2 on 条件;

左外连接：相当于查询A表所有数据和交集部分数据；
右外连接：相当于查询B表所有数据和交集部分数据。

部分命令：

-- 隐式内连接
select * from emp1 , dept where emp1.dep_id = dept.id;
update dept set name = '赵六' where dep_name = '产品部';
-- 查询emp1的name，gender，dept的dep_name
select emp1.name,emp1.gender,dept.dep_name from emp1 , dept where emp1.dep_id = dept.id;
-- 给表起别名---和上面一句命令表达的意思一样，只是改了表的名字，但是作用一样
select 
t1.name,
t1.gender,
t2.dep_name 
from 
emp1 t1 , 
dept t2 
where 
t1.dep_id = t2.id;
-- 显示内连接
select * from emp1 inner join dept on emp1.dep_id = dept.id;
select * from emp1 join dept on emp1.dep_id = dept.id; -- inner可以省略，意思一样
-- 左外连接
-- 查询emp1表所有数据和对应的部门信息
select * from emp1 left join dept on emp1.dep_id = dept.id;
-- 右外连接
-- 查询dept表所有数据和对应的员工信息
select * from emp1 right join dept on emp1.dep_id = dept.id;

子查询

• 查询中嵌套查询，故称嵌套查询为子查询。
  
• 子查询根据查询结果不同，作用不同：
  • 单行单列：作为条件值，使用 = != > < 等进行条件判断

  select 字段列表 from 表 where 字段名 = (子查询);
  

• 多行单列:作为条件值，使用in等关键字进行条件判断

	select 字段列表 from 表 where 字段名 in (子查询);

• 多行多列：作为虚拟表

  select 字段列表 from(子查询)  where 条件； 
  

-- 给每位员工添加薪资字段
alter table emp1 add salary double(7,2);
-- 插入一条信息薪资等于5000
insert into emp1(salary) values(5000);
-- 给所有男的员工赋值薪资为8K
update emp1 set salary = 8000 where gender = '男';
-- 给单个员工赋值
update emp1 set salary = 4500 where name = '朱叭';
select * from emp1;
update emp1 set salary = 5000 where name = '张婷';
-- 给多个员工薪资赋值
update emp1 set salary = 5000 where name = '赵六' or name = '罗丹' or name = '朱叭';
-- 给id为14的员工添加姓名等相关信息
update emp1 set name = '王刚',age = 19,dep_id = 3,gender = '女' where id = 14;
select * from emp1;
-- ---------------------------------------------------------------------------
-- 子查询
-- 查询张婷的薪资
select * from emp1 salary where name = '张婷';
select salary from emp1 where name = '张婷';
-- 查询薪资高于张婷的员工信息   单行单列
select * from emp1 where salary>5000;
-- 试用一条sql语句查询张婷的薪资，以及高于张婷薪资的员工信息
select * from emp1 where salary>(select salary from emp1 where name = '张婷');  -- 嵌套子查询
-- 查询“研发部”和“销售部”所有员工的信息
-- 查询研发部所有员工的信息
select * from dept where dep_name="研发部" or dep_name="销售部";
select * from emp1 where dep_id = 1;
-- 查询销售部的信息
select * from emp1 where dep_id=(select id from dept where dep_name="销售部");
select * from emp1 where dep_id in(2);
-- 查询销售部和   多行单列
select * from emp1 where dep_id in(2,3);
select * from emp1 where dep_id in(select id from dept where dep_name="销售部" or dep_name="研发部");

desc emp1;
select * from dept;
-- 添加员工如期字段
alter table emp1 add join_date date;
desc emp1;
alter table emp1 add salary double(7,2);
select * from emp1;
update emp1 set join_date = "2021-3-12" where name = "赵六" or name = "李斯" or name = "朱叭";
update emp1 set join_date = "2022-3-1" where name = "罗丹" or name = "张婷" or name = "张三";
update emp1 set join_date = "2021-5-1" where name = "罗丹";
update emp1 set join_date = "2021-3-12" where name = "朱叭";
update emp1 set join_date = "2022.7.1"; -- 如果没有给到条件就会默认全部修改
-- 查入职日期是“2021-5-1”之后的员工信息和部门信息
-- 查入职日期是“2021-5-1”之后的员工信息
-- 单行单列
select * from emp1 where join_date>"2021-5-1";
select * from dept,emp1 where emp1.dep_id = dept.id;
-- 多行多列
select * from (select * from emp1 where join_date>"2021-5-1") t1,dept where t1.dep_id = dept.id;

命令汇总：

drop table if EXISTS emp; -- 删除表
-- 创建员工表
create table emp(
id int PRIMARY KEY auto_increment,              #员工id，主键且自增长
ename varchar(25) not null UNIQUE,              #员工姓名，非空并且唯一
joindate date not null,                         #入职日期，非空
salary double(7,2) not null,                    #薪水，非空
bonus double(7,2) default 0                     #奖金，如果没有则默认为0
);
show tables;-- 查看都有什么表
select * from emp; -- 查看表中是否有数据
-- 插入数据
insert into emp(id,ename,joindate,salary,bonus) values(1,'张州','2022-3-18',6000,5000);
-- 主键约束展示
#主键：非空且唯一  主键在一张表中只有一个
#主键非空
insert into emp(id,ename,joindate,salary,bonus) values(null,'张州','2022-3-18',6000,5000);
#主键唯一
insert into emp(id,ename,joindate,salary,bonus) values(1,'张州','2022-3-18',6000,5000);
#正常值的添加
insert into emp(id,ename,joindate,salary,bonus) values(2,'宋扬','2022-4-18',5000,500);
-- 非空约束演示
insert into emp(id,ename,joindate,salary,bonus) values(2,null,'2022-4-18',5000,500);
-- 唯一约束演示
insert into emp(id,ename,joindate,salary,bonus) values(2,'张州','2022-4-18',5000,500);
-- 唯一默认演示
insert into emp(id,ename,joindate,salary) values(3,'张宇','2022-1-18',4000);
-- 唯一默认值给为null
insert into emp(id,ename,joindate,salary,bonus) values(4,'张鹏','2022-6-18',4000,null);
select * from emp;
-- 自动增长的演示  auto_increment:当列是数字类型并且唯一约束
insert into emp(id,ename,joindate,salary,bonus) values(null,'张州','2022-3-18',6000,null);
insert into emp(ename,joindate,salary,bonus) values('张彭','2022-3-18',6000,null);

-- ------------------------------------------------------------------------------------
-- 外键约束
drop table if exists dept;
drop table if exists emp1;
-- 部门表
create table dept(
id int PRIMARY key auto_increment,
dep_name varchar(9),
addr varchar(15)
);
-- 员工表
create table emp1(
id int primary key auto_increment,
name varchar(9),
age int,
dep_id int,
-- 添加外键 dep_id，关联dept表中的id主键
CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)
);

-- 添加两个部门
insert into dept(dep_name,addr) values('研发部','广州'),('销售部','深圳');

-- 添加员工 ，dep_id 表示员工所在部门
insert into emp1 (name,age,dep_id) values
('张三',19,1),
('王五',23,1),
('赵六',17,1),
('李斯',25,2),
('朱叭',19,2),
('张婷',19,2);

-- 删除外键
alter table emp1 drop foreign key fk_emp_dept;

-- 建表完成以后添加外键
alter table emp1 add constraint fk_emp1_dept foreign key(dep_id) references dept(id);

show tables;
-- 单表查询
select * from dept;
select * from emp1;
select dep_id from emp1;
-- 多表查询
select * from dept , emp1;
#知识点补充：笛卡尔积：存在A与B两个集合，取A，B两个集合的所有情况
-- 消除无效数据
select * from emp1 , dept where emp1.dep_id = dept.id;
-- 添加一条员工数据
insert into emp1(name,age,dep_id) values('罗丹',25,1);
-- 添加一条部门数据
insert into dept(id,dep_name,addr) values(3,'人事部','杭州');
desc dept;  -- 查询表结构
select * from dept; 
-- 添加多条部门数据
insert into dept values(4,'策划部','贵阳'),(5,'财务部','上海'),(6,'产品部','杭州');
-- 添加表的列
alter table emp1 add gender char(2);
select * from emp1;
-- 修改表数据
update emp1 set gender='男' where name='张三';
update emp1 set gender='男' where name='王五';
update emp1 set gender='男' where id=9;
update emp1 set gender='男' where age=25;
update emp1 set gender='女' where id=13;
update emp1 set gender='女' where id=11 or id=12;