MySQL面试考点整理

2021-12-06 08:46:05

一、内外连接

  • 内连接: 只连接匹配的行
  • 左外连接: 包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行
  • 右外连接: 包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行
  • 全外连接: 包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。
  • 交叉连接: 生成笛卡尔积－它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配
drop table users;
drop table address;

create table users(
    id int primary key auto_increment,
    `name` varchar(30)
);

create table address(
    id int primary key auto_increment,
    uid int,
    city varchar(30)
 #   foreign key(uid) references users(id)
);

insert into users (`name`) values("张三");
insert into users (`name`) values("李四");
insert into users (`name`) values("王五");

insert into address (`uid`,`city`) values(1,"上海");
insert into address (`uid`,`city`) values(2,"北京");
insert into address (`uid`,`city`) values(5,"武汉");

select `name`,city from users inner join address on users.id=address.uid;#内连接
select `name`,city from users left join address on users.id=address.uid;#左外连接
select `name`,city from users right join address on users.id=address.uid;#右外连接
select `name`,city from users full join address;#全连接
select `name`,city from users cross join address;#交叉连接，类似于笛卡儿积

1.内连接(inner join)——左右两边都匹配才连接

select `name`,city from users inner join address on users.id=address.uid;
MySQL面试考点整理

2.左外连接(left join)——左边的表全部都保存下来，右边可以为空

select `name`,city from users left join address on users.id=address.uid;
MySQL面试考点整理

3.右外连接(right join)——右边的表全部都保存下来，左边可以为空

select `name`,city from users right join address on users.id=address.uid;
MySQL面试考点整理

4.全连接与交叉连接——两个都是与笛卡儿积类似，都两两相连

MySQL面试考点整理

二、数据库事务的四大特性(ACID)

1.原子性（Atomicity）

原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

2.一致性（Consistency）

如果事务执行之前数据库是一个完整的状态，那么事务结束后，无论事务是否执行成功，数据库仍然是一个完整的状态。
数据库的完整状态：当一个数据库中的所有的数据都符合数据库中所定义的所有约束，此时可以称数据库是一个完整的状态。
拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。

3.隔离型（Isolation）

多个用户并发访问数据库时，一个用户的事务不能被其他用户的事务所干扰，多个并发事务之间数据要相互隔离。

4.持久性（Durability）

指一个事务一旦被提交，他对数据库的影响是永久性的。

三、事务级别

1.不同事务级别可能带来的问题

  • 脏读
    脏数据所指的就是未提交的数据。也就是说，一个事务正在对一条记录做修改，在这个事务完成并提交之前，这条数据是处于待定状态的（可能提交也可能回滚），这时，第二个事务来读取这条没有提交的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被称为脏读。

salary=1000

T1 T2
read(salary)——1000
write(salary+2000)——3000
read(salary)——3000
rollback
  • 不可重复读
    一个事务先后读取同一条记录，而事务在两次读取之间该数据被其它事务所修改，则两次读取的数据不同，我们称之为不可重复读。

salary=1000

T1 T2
read(salary)——1000
read(salary)——1000
write(salary+2000)——3000
commit
read(salary)——3000
  • 幻读
    一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为幻读。
T1 T2
select * from users where age between 10 and 30;
insert into users(name,age) values("Bob",15);
select * from users where age between 10 and 30;

  • 脏读与不可重复读的区别

    1. 脏读是读取了未提交的数据。一个事务正在对一条记录做修改，在这个事务完成并提交之前，这条数据是处于待定状态的（可能提交也可能回滚），这时，第二个事务来读取这条没有提交的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被称为脏读。
    2. 不可重复读是在两次读取之间有其它事务对数据做了操作。

  • 不可重复读与幻读的区别

    1. 不可重复读的重点是修改，同样的条件, 你读取过的数据, 再次读取出来发现值不一样了。
    2. 幻读的重点在于新增或者删除 (数据条数变化)。同样的条件, 第1次和第2次读出来的记录数不一样。

2.事务的隔离级别

√: 可能出现 ×: 不会出现

脏读 不可重复读 幻读
Read uncommitted(读未提交) √ √ √
Read committed(读提交) × √ √
Repeatable read(重复读) × × √
Serializable(序列化) × × ×

四、三大范式

  • 第一范式（1NF）：数据表中的每一列（每个字段）必须是不可拆分的最小单元，也就是确保每一列的原子性；
  • 第二范式在第一范式的基础上更进一层，第二范式需要确保数据库表中每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
  • 第三范式需要确保数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。

简而言之：
第1范式：每个表中都有1列，并且该列是不可拆分的最小单元
第2范式：1张表只描述一件事情。如包含用户-订单-商品信息表，应该拆分为3个表。
第3范式：用外键做表的关联。比如：

  • Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话）
  • 就存在上述关系：学号--> 所在院校 --> (院校地址，院校电话)
  • 这样的表结构，我们应该拆开来：（学号，姓名，年龄，性别，所在院校）--（所在院校，院校地址，院校电话）
    参考链接：https://www.cnblogs.com/1906859953Lucas/p/8299959.html

五、五大约束

1.primary KEY:设置主键约束；
2.UNIQUE：设置唯一性约束，不能有重复值；
3.DEFAULT 默认值约束，height DOUBLE(3,2)DEFAULT 1.2 # height不输入是默认为1,2
4.NOT NULL：设置非空约束，该字段不能为空；
5.FOREIGN key :设置外键约束。

六、常考语法

假设orders表中有如下数据：
{
cust_id: "abc123",
ord_date: ISODate("2012-11-02T17:04:11.102Z"),
status: 'A',
price: 50,
items: [ { sku: "xxx", qty: 25, price: 1 }, { sku: "yyy", qty: 25, price: 1 } ]
}

1.统计orders表中记录数

db.orders.aggregate( [
   {
     $group: {
        _id: null,
        count: { $sum: 1 }
     }
   }
] )

类似mysql：SELECT COUNT(*) AS count FROM orders

2.计算orders表中所有记录的price之和

db.orders.aggregate( [
   {
     $group: {
        _id: null,
        total: { $sum: "$price" }
     }
   }
] )

类似mysql：SELECT SUM(price) AS total FROM orders

3.计算cust_id相同的所有记录的price之和

db.orders.aggregate( [
   {
     $group: {
        _id: "$cust_id",
        total: { $sum: "$price" }
     }
   }
] )

类似mysql：SELECT cust_id,SUM(price) AS total FROM orders GROUP BY cust_id

4.对(cust_id,ord_date)进行分组，并计算每组里面的price之和

db.orders.aggregate( [
   {
     $group: {
        _id: {
           cust_id: "$cust_id",
           ord_date: {
               month: { $month: "$ord_date" },
               day: { $dayOfMonth: "$ord_date" },
               year: { $year: "$ord_date"}
           }
        },
        total: { $sum: "$price" }
     }
   }
] )

类似mysql：SELECT cust_id,ord_date,SUM(price) AS total FROM orders GROUP BY cust_id,ord_date

5.当cust_id相同的记录数大于1时，查询出该cust_id及其对应的记录数

db.orders.aggregate( [
   {
     $group: {
        _id: "$cust_id",
        count: { $sum: 1 }
     }
   },
   { $match: { count: { $gt: 1 } } }
] )

类似mysql：SELECT cust_id,count(*) FROM orders GROUP BY cust_id HAVING count(*) > 1

6.对(cust_id,ord_date)进行分组，并计算每组里面的price之和,返回price之和大于250时的cust_id,ord_date以及price之和

db.orders.aggregate( [
   {
     $group: {
        _id: {
           cust_id: "$cust_id",
           ord_date: {
               month: { $month: "$ord_date" },
               day: { $dayOfMonth: "$ord_date" },
               year: { $year: "$ord_date"}
           }
        },
        total: { $sum: "$price" }
     }
   },
   { $match: { total: { $gt: 250 } } }
] )

类似mysql：SELECT cust_id,ord_date,SUM(price) AS total FROM orders GROUP BY cust_id,ord_date HAVING total > 250

7.计算status='A',且cust_id相同的记录的price之和

db.orders.aggregate( [
   { $match: { status: 'A' } },
   {
     $group: {
        _id: "$cust_id",
        total: { $sum: "$price" }
     }
   }
] )

类似mysql：SELECT cust_id,SUM(price) as total FROM orders WHERE status = 'A' GROUP BY cust_id

8.计算status='A',且cust_id相同的记录的price之和,并且只返回price之和大于250的记录

db.orders.aggregate( [
   { $match: { status: 'A' } },
   {
     $group: {
        _id: "$cust_id",
        total: { $sum: "$price" }
     }
   },
   { $match: { total: { $gt: 250 } } }
] )

类似mysql：SELECT cust_id,SUM(price) as total FROM orders WHERE status = 'A' GROUP BY cust_id HAVING total > 250

9.对于每个唯一的cust_id，将与orders相关联的相应订单项order_lineitem的qty字段进行总计

db.orders.aggregate( [
   { $unwind: "$items" },
   {
     $group: {
        _id: "$cust_id",
        qty: { $sum: "$items.qty" }
     }
   }
] )

类似mysql：SELECT cust_id,SUM(li.qty) as qty FROM orders o,order_lineitem li WHERE li.order_id = o.id GROUP BY cust_id

10.统计(cust_id,ord_date)分组的数量

db.orders.aggregate( [
   {
     $group: {
        _id: {
           cust_id: "$cust_id",
           ord_date: {
               month: { $month: "$ord_date" },
               day: { $dayOfMonth: "$ord_date" },
               year: { $year: "$ord_date"}
           }
        }
     }
   },
   {
     $group: {
        _id: null,
        count: { $sum: 1 }
     }
   }
] )

类似mysql：SELECT COUNT(*) FROM (SELECT cust_id, ord_date FROM orders GROUP BY cust_id, ord_date) as DerivedTable

参考链接：
https://www.cnblogs.com/ilikeballs/p/4341383.html
https://blog.csdn.net/qq_33862644/article/details/79692652
https://www.cnblogs.com/zhoujie/p/mongo1.html

上一篇：30个低代码投融资项目介绍，有没有你最熟悉和钟爱的一款？


下一篇：MySQL每组求最值的记录与每组前N条记录