typora-copy-images-to: imgs

1. 1、数据库的三大范式(理解)

1.1. 1.1 什么是范式?

范式就是设计数据库的规则;

好的数据库设计对数据库的存储性能和查询性能差生很大的影响;

1.2. 1.2 范式的基本分类

​ 目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。

​ 满足最低要求的范式是第一范式(1NF)。在第一范式的基础上进一步满足更多规范要求的称为第二范式(2NF),其余范式以次类推。一般说来,数据库只需满足第三范式(3NF)就行了。

1.3. 1.3 第一范式(1NF)

要求数据库表中的每一列保持原子性(不可分割),换句话说,第一范式要求数据库中的表都是二维表.

反例:

解决:

1.4. 1.4 第二范式

满足第一范式的基础上,表中的每一个字段都完全依赖于主键;

定义:数据库的表中不存在非主键字段对任一候选关键字段的部分依赖.

反例:

商品与供应商关系:多对多

联合主键:商品名称与供应商名称作为联合主键

解决:

总结:

1)第二范式要满足第一范式;

2)不能表中非主键字段对主键的部分依赖;(使用商品与供应上信息合并的一张表,其中商品的重量字段只跟商品有关系,与供应商是没有关系的,存储部分依赖,不符合第二范式)

1.5. 1.5 第三范式

1.第三范式是在第二范式的基础上定义的;

2.如果数据表中不存在非关键字段对任意候选关键字段的传递依赖则符合第三范式.

​ 换句话说就是表中不存在字段对主键的传递依赖,则符合第三范式;

反例:

说明:分类描述字段依赖于分类,然后分类又与商品信息关联,所以存在传递依赖;

总结:

1)第三范式满足第二范式;

2)表中非主键字段与主键字段不能存在传递依赖;

3大范式总结:

1)第一范式:表中字段保持[原子性],不可分割;
2)第二范式:首先满足第一范式,不能存在非主键字段对主键的[部分依赖];
3)第三范式:首先满足第二范式,不能存在非主键字段对主键传递依赖;

1.6. 1.6 反三范式

反三范式相对于三范式的,没有冗余的数据库未必是最好的数据库,有时为了提高数据库的运行效率,就必须降低范式标准,适当保留冗余数据。

在多表查询中,关联的表越多,性能开销越大,同时响应的时间越长;

总结:符合3大范式设计的表结构不一定是最优的表结构,适当的加入一些冗余字段(反3大范式)之后,提供了数据查询的效率;

小结:

范式 特点
第一范式 原子性:表中每列不可再拆分
第二范式 不产生局部依赖,表中每列完全依赖于主键
第三范式 不产生传递依赖,表中和每列都是直接依赖于主键

2. 2、多表查询介绍(掌握)

2.1. 2.1 什么是多表查询?

同时查询多张表,获取需要的数据;

比如:我们向查询水果对应的价格,需要将水果表和价格表同时进行查询;

2.2. 2.2 多表查询的分类

image-20201015205830176

3. 3、笛卡尔积现象

准备数据:

有两张表,一张是水果表fruit,一张是价格表price。

建表:

-- 价格 1
create table price( 
   id int primary key auto_increment,
   price double
);
-- 水果 n
create table fruit(
  id int primary key auto_increment,
  name varchar(20) not null,
  price_id int,
  foreign key(price_id) references price(id)
);
insert into price values(1,2.30);
insert into price values(2,3.50);
insert into price values(4,null);

insert into fruit values(1,'苹果',1);
insert into fruit values(2,'橘子',2);
insert into fruit values(3,'香蕉',null);
-- 一种水果有一个价格 一个价格对应多种水果
-- 价格 1 水果 n 水果将价格主键作为外键

3.1. 3.1 什么是笛卡尔积现象

-- 运行sql
select * from fruit,price;

水果--->价格---->笛卡尔积结果映射关系:

image-20201031152423293

如何避免笛卡尔积?

select * from fruit,price where fruit.price_id=price.id;
-- 条件过滤笛卡尔积

image-20201031152521899

4. 4、内连接

4.1. 4.1 什么是内连接

image-20201031152914867

4.2. 1.隐式内连接

看不到join关键字,条件使用where指定

语法:
select 列名 , 列名 .... from 表名1,表名2 where 表名1.列名 = 表名2.列名;
select * from fruit,price where fruit.price_id = price.id;

说明:在产生两张表的笛卡尔积的数据后,通过条件筛选出正确的结果。

4.3. 2.显示内连接

显示内连接:使用INNER JOIN ... ON语句, 可以省略INNER

语法:
select * from 表名1  inner join 表名2 on 条件;
或者
select * from 表名1  join 表名2 on 条件

4.4. 3.需求

实现:查询苹果的信息,显示苹果id,名字,和价格

select * from fruit inner join price on fruit.price_id=price.id;

select fruit.id as fruit_id,fruit.name,price.id as price_id,price.price from fruit inner join price on fruit.price_id=price.id;

5. 5、左外连接

image-20201031154615190

5.1. 1.格式:

select * from1 left outer join2 on 条件;
把left 关键字之前的表,是定义为左侧。 left关键字之后的表,定义右侧。
查询的内容,以左侧的表为主,如果左侧有数据,右侧没有对应的数据,仍然会把左侧数据进行显示。

5.2. 2.需求

实现:不管能否查到水果对应的价格,都要把水果显示出来

select * from fruit left outer join price on fruit.price_id=price.id;

6. 6、右外连接

语法:select * from1 right outer join2 on 条件;
说明:如果右侧有数据,左侧没匹配到,把右侧的数据显示出来。
right之前的是左侧,right之后的是右侧。

6.1. 6.2 需求

实现:不管能否查到价格对应的水果,都要把价格显示出来

image-20201031155332936

-- 实现:不管能否查到价格对应的水果,都要把价格显示出来
select * from fruit right join price on fruit.price_id=price.id;

select * from price left join fruit on fruit.price_id=price.id;

7. 7.子查询

子查询就是查询的结果作为另一个查询语句语法的一部分;

需求:

查询班内那些学生成绩比刘彪的成绩要高?

成绩表student:id name score

​ 1)查询到刘彪的成绩

​ select score from student where name='刘彪'; -- 60

​ 2)查询成绩大于60的学生信息

​ select * from student where score > 60;

合并

​ select * from student where score > ( select score from student where name='刘彪');

7.1. 7.1 数据准备

一个部门有多个员工,一个员工只能属于一个部门,关系由员工维护;

-- 创建部门表 1
CREATE TABLE dept (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
);

INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');

-- 创建员工表 n
CREATE TABLE emp (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10),
  gender CHAR(1),   -- 性别
  salary DOUBLE,   -- 工资
  join_date DATE,  -- 入职日期
  dept_id INT,
  foreign key(dept_id) references dept(id)
);

INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);

7.2. 7.2 子查询结果分类

子查询的结果分为3种情况:

1.单行单列

< > = ...

2.多行单列

in not in

3.多行多列

使用as取别名

8. 8.子查询结果是单行单列

需求1:查询工资最高的员工是谁?

-- 1.查询最高工资
   select max(salary) from emp;-- 9000
-- 2.查询工资等于9000的员工
   select * from emp where  salary=9000;  
-- 合并子查询
    select * from emp where  salary=(select max(salary) from emp);

image-20201031161850722

需求2:查询工资小于平均工资的员工有哪些?

-- 1)查询出平均工资
    select avg(salary) from emp;-- 5860
-- 2)查询小于平均工资的员工信息
    select * from emp where salary <5860;-- 2,4,5
-- 合并
      select * from emp where salary <(select avg(salary) from emp) ;

9. 9、子查询结果是多行单列的时候

子查询结果是多行单列,结果集类似于一个数组,在WHERE后面作为条件,父查询使用IN运算符

需求1:查询工资大于5000的员工,来自于哪些部门的名字

-- 1)查询薪资大于5000的员工对应的部门id集合
     select distinct dept_id from emp where salary > 5000;-- 1,2
-- 2)查询部门id在这个集合内的部门名称
     select name from dept where id in(1,2);
-- 合并
     select name from dept where id 
     in( select distinct dept_id from emp where salary > 5000);

image-20201031162612209

需求2:查询开发部与财务部所有的员工信息

-- 需求2:查询开发部与财务部所有的员工信息
-- 1)确定查询需要的表:dept,emp
-- 2)确定两张表之间的关系:emp.dept_id=dept.id
-- 1.查询部门为开发部和财务部的部门id集合
    select id from dept where name ='开发部' or name='财务部';
    select id from dept where name in ('开发部','财务部');-- 1,3
-- 2.查询员工信息为1或者3
    select * from emp where dept_id in(1,3); 

-- 合并
    select * from emp where dept_id
     in(select id from dept where name in ('开发部','财务部'))

image-20201031163155286

10. 10、子查询的结果是多行多列

注意:子查询如果是多行多列,一般使用as取别名作为表进行关联查询

说明:对于多行多列,首先需要用as取别名,然后与其他表关联查询;

需求1:查询出2011年以后入职的员工信息,包括部门名称

-- 1)分析查询需要的表:emp,dept
-- 2)确定查询需要的表的关联字段:emp.dept_id=dept.id
-- 3)分析业务
   -- 1.查询员工表中是2011年之后入职的员工信息(包含部门id)
   select * from emp where join_date>'2011-01-01';-- as tmp
   -- 2.将上述查询结果取别名作为一张表与部门表关联查询
   select tmp.*,dept.name from 
   (select * from emp where join_date>'2011-01-01') as tmp,dept 
   where tmp.dept_id=dept.id;

   select emp.*,dept.name from emp,dept where emp.dept_id=dept.id and emp.join_date >'2011-01-01';

image-20201031164210916

说明:

子查询结果集是多行多列的结果时,需要使用as区别名(语义就是给不存在的表数据取一个名字),这样可以使用功能这个表与其他表进行关联查询(where innder join left join right join)

11. 11、多表查询案例(子查询练习题)

11.1. 1.数据准备

create table teacher (
  id int(11) primary key auto_increment,
  name varchar(20) not null unique
 );
create table student (
  id int(11)  primary key auto_increment,
  name varchar(20) NOT NULL unique,
  city varchar(40) NOT NULL,
  age int 
) ;
create table course(
  id int(11)  primary key auto_increment,
  name varchar(20) not null unique,
  teacher_id int(11) not null,
  foreign key(teacher_id) references teacher (id)
);

create table studentcourse (
   student_id int NOT NULL,
   course_id int NOT NULL,
   score double NOT NULL,
   foreign key (student_id) references student (id),
   foreign key (course_id) references course (id)
);

insert into teacher values(null,'关羽');
insert into teacher values(null,'张飞');
insert into teacher values(null,'赵云');

insert into student values(null,'小王','北京',20);
insert into student values(null,'小李','上海',18);
insert into student values(null,'小周','北京',22);
insert into student values(null,'小刘','北京',21);
insert into student values(null,'小张','上海',22);
insert into student values(null,'小赵','北京',17);
insert into student values(null,'小蒋','上海',23);
insert into student values(null,'小韩','北京',25);
insert into student values(null,'小魏','上海',18);
insert into student values(null,'小明','广州',20);

insert into course values(null,'语文',1);
insert into course values(null,'数学',1);
insert into course values(null,'生物',2);
insert into course values(null,'化学',2);
insert into course values(null,'物理',2);
insert into course values(null,'英语',3);

insert into studentcourse values(1,1,80);
insert into studentcourse values(1,2,90);
insert into studentcourse values(1,3,85);
insert into studentcourse values(1,4,78);
insert into studentcourse values(2,2,53);
insert into studentcourse values(2,3,77);
insert into studentcourse values(2,5,80);
insert into studentcourse values(3,1,71);
insert into studentcourse values(3,2,70);
insert into studentcourse values(3,4,80);
insert into studentcourse values(3,5,65);
insert into studentcourse values(3,6,75);
insert into studentcourse values(4,2,90);
insert into studentcourse values(4,3,80);
insert into studentcourse values(4,4,70);
insert into studentcourse values(4,6,95);
insert into studentcourse values(5,1,60);
insert into studentcourse values(5,2,70);
insert into studentcourse values(5,5,80);
insert into studentcourse values(5,6,69);
insert into studentcourse values(6,1,76);
insert into studentcourse values(6,2,88);
insert into studentcourse values(6,3,87);
insert into studentcourse values(7,4,80);
insert into studentcourse values(8,2,71);
insert into studentcourse values(8,3,58);
insert into studentcourse values(8,5,68);
insert into studentcourse values(9,2,88);
insert into studentcourse values(10,1,77);
insert into studentcourse values(10,2,76);
insert into studentcourse values(10,3,80);
insert into studentcourse values(10,4,85);
insert into studentcourse values(10,5,83);

image-20201031165129686

11.2. 需求1

实现:查询获取最高分的学生信息

11.3. 需求2

实现:查询编号是2的课程比编号是1的课程最高成绩高的学生信息

 1)编号是1的课程的最高成绩 --- a=80
 select max(score) from studentcourse where course_id=1;
 2)查询编号是2的信息中成绩比a要高的学生的id集合-- b
 select student_id from studentcourse where course_id=2 and score>80;-- (1,4,6,9)
 3)查询学生信息中id在b集合内的学生信息
 select * from student where id in (1,4,6,9);
 -- 合并
  select * from student 
  where id in (select student_id from studentcourse where course_id=2 
    and 
  score>(select max(score) from studentcourse where course_id=1));

image-20201031170050038

11.4. 需求3

实现:查询编号是2的课程比编号是1的课程最高成绩高的学生姓名和成绩

11.5. 需求4

实现:查询每个同学的学号、姓名、选课数、总成绩

-- 分析:查询的信息来自哪些表: student,studentcourse
-- 实现
-- 1:在studentcourse表查询出每个学生的选课数和总成绩,学生的id 作为临时表as取别名
    select student_id,count(*),sum(score) from studentcourse group by student_id;-- tmp 
-- 2.上述表作为一张表与学生表关联查询
-- 说明:在子查询中如果用到了聚合函数,同时主查询中如果要引用聚合查询的结果,那么需要取别名
   select student.*,tmp.course_count,tmp.total from student,(select student_id,count(*) as course_count ,sum(score) as total from studentcourse group by student_id)as tmp where tmp.student_id=student.id;

image-20201031171204566

12. 12、 多表查询案例(连接查询练习题)

12.1. 1.数据准备

-- 部门表
CREATE TABLE dept (
  id INT PRIMARY KEY PRIMARY KEY, -- 部门id
  dname VARCHAR(50), -- 部门名称
  loc VARCHAR(50) -- 部门位置
);

-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');

-- 职务表,职务名称,职务描述
CREATE TABLE job (
  id INT PRIMARY KEY,
  jname VARCHAR(20),
  description VARCHAR(50)
);

-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司,接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');

-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id
  ename VARCHAR(50), -- 员工姓名
  job_id INT, -- 职务id
  mgr INT , -- 上级领导
  joindate DATE, -- 入职日期
  salary DECIMAL(7,2), -- 工资
  bonus DECIMAL(7,2), -- 奖金
  dept_id INT, -- 所在部门编号
  CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
  CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);

-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);

-- 工资等级表
CREATE TABLE salarygrade (
  grade INT PRIMARY KEY,
  losalary INT, -- 最低薪资
  hisalary INT -- 最高薪资
);

-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

12.2. 需求1

实现:查询所有员工信息。显示员工编号,员工姓名,工资,职务名称,职务描述

12.3. 需求2

实现:查询所有员工信息。显示员工编号,员工姓名,工资,职务名称,职务描述,部门名称,部门位置

12.4. 需求3

实现:查询所有员工信息。显示员工姓名,工资,职务名称,职务描述,部门名称,部门位置,工资等级

12.5. 需求4

实现:查询经理的信息,显示员工姓名,工资,职务名称,职务描述,部门名称,部门位置,工资等级

12.6. 需求5

实现:查询出每个部门的部门编号、部门名称、部门位置、部门人数

重点总结:

面试:

​ 1)数据库约束分类:

约束名称 关键字 作用
主键约束 primary key 唯一+非空+索引(auto_increment)
非空约束 not null 不为空
唯一约束 unique 唯一+索引
外键约束 foreign key 维护多表之间的关系
alter table t add constraint 约束名称 foreign key(当前表字段) references 主表(主键);
默认值约束 default 值 默认值

2)主键与唯一+非空的区别?

 ~~~tex

1)主键一张表只能有一个,同时底层维护了一个主键索引; 2)唯一+非空在表中可以添加多个,同时底层维护了一个唯一索引; 3)主键可以自增,但是唯一+非空不可以;

 ~~~

3)3大范式

1)第一范式:
     表中的字段保证原子性,不可分割;
2)第二范式:
     首先满足第一范式基础上,不能存在非主键字段对主键字段部分依赖;
3)第三范式:
     首选满足第二范式,然后不能存在非主键字段对主键传递依赖;

4)多表查询

​ 内连接查询与外连接查询区别?

内连接:
   2中方式:1)隐式内连接 where 2)显式内连接  inner join on 条件
   强调:结果的交集(多表同时满足条件的共性部分)
外连接:
   2中方式:1)左外连接 left outer join on 条件 (以左表为基础,无论是否满足条件,都要显示)
          2)右外连接 right outer join on 条件 (以右表为基础,无论是否满足条件,都要显示)

重点:

1.约束

2.练习多表查询

1.内连接
2.外连接

results matching ""

    No results matching ""