数据库学习（一）之逻辑分层&存储引擎

发表于 | 阅读次数:
字数统计: 3.6k | 阅读时长 ≈ 15

一、数据库

1、Mysql的逻辑分层

连接层->服务层->引擎层->存储层

img

分析：

  1. 在服务层中，MySQL提供了一个SQL优化器，可以将SQL语句进行优化，并且改变其执行的顺序结构，但最终的结果不变。

    比如，SQL语句顺序为 a b c，SQL优化器之后的为 b a c

  2. 简单说明InnoDB和MyISAM的区别方式

    • InnoBD（默认）：事物优先，适合高并发操作，行锁
    • MyISAM：性能优先，表锁

2、SQL优化

原因

性能低、执行时间太长、等待时间太长、SQL语句欠佳（连接查询）、索引失效、服务器参数设置不合理（缓冲、线程数）

2.1 SQL语句欠佳

MySQL参考

  1. 编写过程

    select 后是输出

    from 后是 获取数据

    where 后是过滤

    group by 后是分组

    • group by id，按照id进行分组
    • 分组后，如果遇到select，那么输出的是每一组的第一行数据
    • group by还可以和聚集函数放在一起
    • 聚集函数：count()、sum()、max()、min()、avg()、group_concat()

    having 后是过滤

    order by 后是排序

    limit 后是限定个数

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    SELECT DISTINCT
        < select_list >
    FROM
        < left_table > < join_type >
    JOIN < right_table > ON < join_condition >
    WHERE
        < where_condition >
    GROUP BY
        < group_by_list >
    HAVING
        < having_condition >
    ORDER BY
        < order_by_condition >
    LIMIT < limit_number >

  2. 解析过程

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    FROM <left_table>
    ON <join_condition>
    <join_type> JOIN <right_table>
    WHERE <where_condition>
    GROUP BY <group_by_list>
    HAVING <having_condition>
    SELECT
    DISTINCT <select_list>
    ORDER BY <order_by_condition>
    LIMIT <limit_number>

3、连接查询

3.1 inner join 内连接查询

关键字：inner join on

组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。

1
select * from a_table a inner join b_table b on a.id = b.id;

image-20210830223034047

3.2 left join 左连接查询

关键字：left join on / left outer join on

left join是left outer join的简写，它的全称是左外连接，是外连接中的一种。左（外）链接，左表（a_table）的记录将会全部表示出来，而右表（b_table）只会显示符合搜索条件的记录。右表记录不足的地方均为NULL

1
select * from a_table left join b_table on a.id = b.id;

image-20210830224538316

3.3 right join 右连接查询

关键字：right join on / right outer join on

right join 是right outer join的简写，它的全称是右外连接，是外连接中的一种。与左（外）连接相反，右（外）连接，左表（a_table）只会显示符合搜索条件的记录，而右表（b_table）的记录将会全部表示出来。左表记录不足的地方均为NULL。

1
select * from a_table right join b_table on a.id = b.id;

image-20210830225318526

3.4 union 全连接

关键字：union / union all

注意事项：

  • 通过union连接的sql，他们分别单独取出的列数必须相同；
  • 不要求合并的表列名称相同时，以第一个sql表列名为准；
  • 使用union时，完全相等的行，将会被合并，由于合并比较耗时，一般不直接使用union进行合并，而是通常采用union all进行合并；
  • 被union连接的sql子句，单个子句中不用写order by，因为不会有排序的效果。但可以对最终的结果进行排序
1
2
3
4
# union 自动去掉完全重复的数据
(select colum1,colum2...columN from tableA) union (select colum1,colum2...columN from tableB);
# union all，将所有数据输出，不会去掉完全重复的数据
(select colum1,colum2...columN from tableA) union all(select colum1,colum2...columN from tableB);

4、查询

创建库

1
2
# 创建一个数据库
create database test;

创建表

1
2
# 创建一个表
create table students(id INT,name VARCHAR(100),age INT,height FLOAT,gender VARCHAR(100),class_id INT,PRIMARY KEY(id));

给表中增加数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
use test;
insert into students values(1,"小明",18,180.00,"女",1);
insert into students values(2,"小月月",18,180.00,"女",2);
insert into students values(3,"彭于晏",29,185.00,"男",1);
insert into students values(4,"刘德华",59,175.00,"男",2);
insert into students values(5,"黄蓉",38,160.00,"女",1);
insert into students values(6,"凤姐",28,150.00,"保密",2);
insert into students values(7,"王祖贤",18,172.00,"女",1);
insert into students values(8,"周杰伦",36,NULL,"男",1);
insert into students values(9,"陈坤",27,181.00,"男",2);
insert into students values(10,"刘亦菲",25,166.00,"女",2);
insert into students values(11,"金星",33,162.00,"中性",3);
insert into students values(12,"静香",12,180.00,"女",4);
insert into students values(13,"郭靖",12,170.00,"男",4);
insert into students values(14,"周姐",34,176.00,"女",5);

4.1 模糊查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 查询姓名中以“小”开始的名字
select name from students where name like "小%";
-- 查询姓名中 有“小”所有的名字
select name from students where name like "%小%";
-- 查询有两个字的名字
select name from students where name like "__";
-- 查询有三个字的名字
select name from students where name like "___";
-- 查询至少有两个字的名字
select name from students where name like "__%";
-- rlike 正则
-- 查询 以 周开始的姓名
select name from students where name rlike "^周.*";
-- 查询 以 周开始，伦结尾的姓名
select name from students where name rlike "^周.*伦$ ";

4.2 范围查询

1
2
3
4
5
6
7
8
9
10
11
-- in(12，18,34) 表示在一个非连续的范围内    not in
-- 查询 年龄为12、18、34的姓名
select name,age from students where age in (12,18,34);
-- between ... and ... 表示咋一个连续的范围内    not between ... and ....
-- 查询 年龄在18和34之间的信息
select * from students where age between 18 and 34;
-- 使用not时，不能用括号，该语句失败：   select * from students where age not (between 18 and 34);
-- 空判断
-- 判断 is null
-- 查询身高为空的信息
select * from students where height is null;

4.3 聚合函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 总数 count
-- 查询男性有多少人
select count(*) as 男性人数 from students where gender = 1;

-- 最大值 max
-- 查询最大的年龄
select max(age) from students;

-- 最小值 min
-- 查询最小的年龄
select min(age) from students;

-- 平均值 avg
-- 计算平均年龄
select avg(age) from students;
select sum(age) / count(*) from students;

-- 四舍五入，round(123.23,1) 保留1位小数
-- 计算所有人的平均年龄，保留2位小数
select round(sum(age)/count(*),2) from students;

4.4 分组

假设想要知道这样的一种情况，计算每个男性的姓名和平均身高，使用如下sql语句会报错,因此为了解决这种情况，需要使用到分组

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 下列会报错，因为不知道给哪一个姓名，因此不能两个一起查询
select name,round(avg(height),2) from students where gender = "男";
-- group by
-- 按照性别分组，查询所有的性别
select gender from students GROUP BY gender;

![img](数据库学习（一）之逻辑分层-存储引擎/截图-16311958882171.png)



疑问1：

select gender from students GROUP BY gender;  和 select DISTINCT(gender) from students ;有什么区别？

![img](数据库学习（一）之逻辑分层-存储引擎/截图-16311959071312.png)

回答1：当想知道每一个性别的人数是多少时，可以结合聚合函数一起使用

```sql
-- 计算每个性别中的人数
select gender,count(*) from students GROUP BY gender;

img

1
2
-- group_concat
select gender,GROUP_CONCAT(name)from students GROUP BY gender;

img

1
select gender,GROUP_CONCAT(name,"_",age,"_",id) from students where gender = "男" GROUP BY gender;

img

1
2
3
4
-- having
-- 查询平均年龄超过30的性别，以及姓名  
select gender,GROUP_CONCAT(name),avg(age) from students GROUP BY gender having avg(age) > 30;
-- where对原始表进行判断，having 对查询出的结果进行判断

img

4.5 排序

1
2
3
4
-- order by
-- asc 升序  desc 降序
-- 按照年龄从小到大，身高从大到小排序
select * from students order by age asc,height desc;

4.6 分页

1
2
3
4
5
-- limit start,count 从start的下标开始查询出count个
-- 查询前5个数据
select * from students limit 0,5;
-- 查询第5-10个数
select * from students limit 5,5;

4.7 连接查询

（1）造数据

1
2
3
4
5
use test;
create table classes(id INT,name VARCHAR(100),PRIMARY KEY(id));
insert into classes values(1,"c#课程");
insert into classes values(2,"Java课程");
insert into classes values(3,"Python课程");

4.7.1 内连接

1
2
3
4
-- inner join ...on
-- select ... from 表A inner join 表B
select * from students inner join classes;
结果：表A每一个都输出3个，因为表B有3个数据

img

1
select s.*,c.name from students as s inner join classes  as c on s.class_id = c.id;

img

4.7.2 左连接

1
select s.*,c.name from students as s left join classes  as c on s.class_id = c.id;

img

4.7.3 右连接

将左连接中的左表和右表互换即可

4.8 自关联

问题描述：

​ 实现全国三级城市联动select选择

​ 按照省、市、区进行分类，会设计三个表，表有些多余，重复，但省份中没有p_id这样的字段

img

可以将身份也添加一个p_id，设计为空即可，可以将下列三个表进行融合

img

使用一个表，然后利用id进行关联，这种在一个表内的设计就是自关联

img

表数据创造

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
create table areas(aid int primary key,atitle varchar(20),pid int);
insert into areas values(110000,"北京市",NULL);
insert into areas values(120000,"天津市",NULL);
insert into areas values(130000,"河北省",NULL);
insert into areas values(140000,"山西省",NULL);
insert into areas values(150000,"内蒙古自治区",NULL);
insert into areas values(160000,"辽宁省",NULL);
insert into areas values(170000,"黑龙江省",NULL);
insert into areas values(370000,"山东省",NULL);

insert into areas values(370100,"济南市",370000);
insert into areas values(370200,"青岛市",370000);
insert into areas values(370300,"淄博市",370000);
insert into areas values(370400,"枣庄市",370000);
insert into areas values(370500,"东营市",370000);
insert into areas values(370600,"烟台市",370000);

insert into areas values(370202,"市南区",370200);
insert into areas values(370203,"市北区",370200);
insert into areas values(370205,"四方区",370200);
insert into areas values(370211,"黄岛区",370200);
insert into areas values(370212,"崂山区",370200);
insert into areas values(370213,"李沧区",370200);

查询（根据省名查询）

1
select * from areas as province inner join areas as city on city.pid = province.aid having province.atitle = "山东省";

img

1
select province.atitle,city.atitle from areas as province inner join areas as city on city.pid = province.aid having province.atitle = "山东省";

img

1
select province.atitle,city.atitle from areas as province inner join areas as city on city.pid = province.aid having province.atitle = "山东省";

img

1
select province.atitle,city.atitle from areas as province inner join areas as city on city.pid = province.aid having province.atitle = "青岛市";

img

4.9 子查询

子查询用的越少，sql效率越高

1
2
-- 查询最高的男生信息
select * from students where height = (select max(height) from students);

img

4.10 数据库设计—三范式

4.10.1 第一范式（1NF）

强调的是列的原子性，即列不能够在分成其他几列。

考虑到这样的一个表：【联系人】（姓名，性别，电话），如果在实际场景中，一个联系人有家庭电话和公司电话，那么这种表结构设计就没有达到1NF。 要符合1NF我们只需把列（电话）拆分，即【联系人】（姓名，性别，家庭电话，公司电话）

img

4.10.2 第二范式（2NF）

首先是1NF，另外包含两部分内容，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。

考虑一个订单明细表：【OrderDetail】（OrderID、ProductId、UnitPrice、Discount、Quantiy、ProductName）。因为我们知道在一个订单中可以订购多个产品， 所以单单一个OrderID是不足以成为主键的，主键应该是（OrderID、ProductID）。显而易见Discount（折扣）、Quanity（数量）完全依赖（取决于）主键（OrderID、ProductID）， 而UnitPrice、ProductName只依赖于ProductID，所以OrderDetail表不符合2NF，不符合2NF的设计容易产生冗余数据 可以把【OrderDetail】表拆分为【OrderDetail】（OrderID、ProductId、Discount、Quantiy）和【Product】（ProductId、UnitPrice、Discount）来消除 订单表中UnitPrice、Discount多次重复的情况。

img

4.10.3 第三范式（3NF）

首先是2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列A依赖于非主键列B，非主键列B依赖于主键的情况。

考虑一个订单表：【Order】（OrderID、OrderDate、CustomerID、CustomerName、CustomerAddr、CustomerCity） 主键是OrderID，其中OrderDate、CustomerID、CustomerName、CustomerAddr、CustomerCity等非主键列都完全依赖于主键OrderID，所以符合2NF，不过问题是 CustomerName、CustomerAddr、CustomerCity直接依赖的是CustomerID（非主键列），而不是直接依赖于主键，它是通过传递才能依赖于主键，所以不符合3NF。 通过拆分【Order】为【Order】（OrderID、OrderDate、CustomerID）和【Customer】（CustomerID、CustomerName、CustomerAddr、CustomerCity）从而达到3NF。 第二范式（2NF）和第三范式（3NF）的概念很容易混淆，区分它们的关键在于 2NF：非主键列是否完全依赖于主键，还是依赖于主键的一部分。3NF：非主键列是否直接依赖于主键，还是直接依赖于非主键列。

img

最终表结构

img

4.11 ER模型

  • E表示entry，实体，设计实体就像定义一个类一样，指定从哪些方面描述对象，一个实体转换为数据库中的一个表

  • R表示relationship，关系，关系描述两个实体之间的对应规则，关系的类型包括一对一、一对多、多对多

  • 关系也是一种数据，需要通过一个字段存储在表中

（1）实体A对实体B为1对1，则在表A或表B中创建一个字段，存储另一个表的主键值

img

（2）实体A对实体B为1对多：在表B中创建一个字段，存储表A的主键值

​ A表中的一条数据对应B表中的一条数据

​ B表中的一条数据对应A表中的多条数据

假设多一个女儿时，需要在子女表中多添加一条数据，即如下情况

img

若在父母表中，新增一列时，情景如下：

但这样会有个问题，若新增一个子女，还需要维护父母表中的某个字段，维护起来特别麻烦

img

（3）实体A对实体B为多对多：新建一个表C，这个表只有两个字段，一个用于存储A的主键值，一个用于存储B的主键值

参考：

Mysql—— 内连接、左连接、右连接以及全连接查询

0%