Java常见面试题之SQL基础

目录

SQL 基础考核1、数据库三大范式

2、varchar 和char 的区别?

3、drop、delete、truncate 区别?

4、UNION和 UNIONALL 的区别?

5、SOL语句执行的很慢的原因是什么?

6、SOL之连接查询

7、SOL之聚合函数

8、SQL Select 语句完整的执行顺序:

9、数据库锁

10、MySQL 常用 SQL 查询语句优化方法【必问】

---

SQL 基础考核
1、数据库三大范式

**第一范式**：确保每列保持原子性，数据表中的所有字段值都是不可分解的原子值。

这是第一范式只有一张表 ，七个字段在业务中不可再拆分：

| 订单 id | 商品 id | 用户 id | 商品名称 | 商品数量 | 用户地址 | 用户电话 |
| ------- | ------- | ------- | -------- | -------- | -------- | -------- |
| 1       | 1       | 1       | Java     | 1        | 杭州     | 110      |
| 1       | 2       | 2       | C++      | 1        | 上海     | 120      |
| 2       | 2       | 3       | C++      | 1        | 北京     | 114      |

**第二范式**：确保表中的每列都和主键相关。

第二范式分成 2 表 订单明细表和用户表 ，一张表只描述一个事件，第一范式中有两件事，一个用户，一个订单。

| 订单 id | 商品 id | 用户 id | 商品名称 | 商品数量 |
| ------- | ------- | ------- | -------- | -------- |
| 1       | 1       | 1       | Java     | 1        |
| 1       | 2       | 2       | C++      | 1        |
| 2       | 2       | 3       | C++      | 1        |

| 用户 id | 用户地址 | 用户电话 |
| ------- | -------- | -------- |
| 1       | 杭州     | 110      |
| 2       | 上海     | 120      |
| 3       | 北京     | 114      |

第三范式：确保每列都和主键列直接相关而不是间接相关。

第三范式分成 3 表 继续拆分订单明细表，添加商品表，一张表中非主键字段只依赖主键，不传递依赖，商品名称只依赖商品 ID，不依赖订单 ID。

| 订单 id | 商品 id | 用户 id | 商品数量 |
| ------- | ------- | ------- | -------- |
| 1       | 1       | 1       | 1        |
| 1       | 2       | 2       | 1        |
| 2       | 2       | 3       | 1        |

| 商品 id | 商品名称 |
| ------- | -------- |
| 1       | C++      |
| 2       | C++      |
| 1       | 1        |
| 1       | 2        |
| 2       | 2        |

最后提下，三大范式在实际中也不是必须严格遵循的，根据业务可以灵活调整，并不是一成不变的。

2、varchar 和char 的区别?

1. varchar 表示**变长**，char 表示长度**固定**。
2. 存储容量不同，对于 char 来说，最多能存放的字符个数为 255。对于 varchar，最多能存放的字符个数是 65535。
3. 存储速度不同，char 长度固定，存储速度会比 varchar 快一些，但在空间上会占用额外的空间，属于一种空间换时间的策略。而 varchar 空间利用率会高些，但存储速度慢，属于一种时间换空间的策略。

3、drop、delete、truncate 区别?

​	drop：删除表、不可恢复、速度快

​	delete：删除表的数据、可恢复、速度慢

​	truncate：删除表结构和数据、表还存在、不可恢复、速度较快

4、UNION和 UNIONALL 的区别?

1. 相同点：union 和 union all 的作用都是将**两个结果集合并到一起**。
2. 不同点：**union** 会对结果**去重并排序**，union all 直接直接返回合并后的结果，**不去重也不进行排序**。
   union all 的性能比 union 性能好。

5、SOL语句执行的很慢的原因是什么?

1. 如果 SQL 语句只是偶尔执行很慢，可能是执行的时候遇到了锁，也可能是 redo log 日志写满了，要将 redo log 中的数据同步到磁盘中去。
2. 如果 SQL 语句一直都很慢，可能是字段上没有索引或者字段有索引但是没用上索引。

6、SOL之连接查询

1. 左连接（左外连接）以**左表为基准**进行查询,左表数据会全部显示出 来, 右表 如果和左表匹配 的数据则显示相应字段的数据,如果不匹配,则显示为NULL;

2. 右连接（右外连接）以**右表为基准**进行查询,右表数据会全部显示出 来, 右表 如果和左表匹配的数据则显示相应字段的数据,如果不匹配,则显示为NULL;

3. 全连接就是**两表为基准**先以左表进行左外连接，然后以右表进行右外连接。

4. 内连接：显示表之间有连接匹配的所有行。

   内连接（交集）、外连接（并集）

7、SOL之聚合函数

​	聚合函数是对一组值进行计算并返回单一的值的函数，它经常与 select语句中的 group by 子句一同使用。

1. avg()：返回的是指定组中的平均值，空值被忽略。
2. count()：返回的是指定组中的项目个数。
3. max()：返回指定数据中的最大值。
4. min()：返回指定数据中的最小值。
5. sum()：返回指定数据的和，只能用于数字列，空值忽略。

8、SQL Select 语句完整的执行顺序:

select * from student where 班级=1班 group by sex having sex>'男'order by id

​	查询中用到的关键词主要包含六个， 并且他们的顺序依次为select--from--where--group by--having--order by其中 select 和 from 是必须的，其他关键词是可选的， 这六个关键词的执行顺序如下：

​	from: 需要从哪个数据表检索数据

​	where: 过滤表中数据的条件

​	group by: 如何将上面过滤出的数据分组算结果

​	having：和group by配合使用、分组后筛选

​	order by : 按照什么样的顺序来查看返回的数据

9、数据库锁

1. 行锁：粒度最小的锁，表示只针对当前操作的行进行加锁；
2. 表锁：粒度最大的锁，表示当前的操作对整张表加锁；
3. 页锁：粒度介于行级锁和表级锁中间的一种锁，表示对页进行加锁。

10、MySQL 常用 SQL 查询语句优化方法【必问】

**SQL优化：**

- 定位要优化的SQL语句
- **分析SQL语句执行效率**（ explain select * from emp where ename like '%A%';）
- 给出SQL优化方案

```
1.频繁出现在where 或者order by子句中的列，建索引
2.在查询时，尽量避免在where子句中使用null值判断
3.在查询时，尽量避免在where子句中使用!=或者<>运算符 --- 索引引擎会使用全表扫描而不会使用索引
4.在查询时，尽量避免在where子句中使用or运算符--- 索引引擎会使用全表扫描而不会使用索引
5.在查询时，in语句要慎用，可以exist替换;
6.在查询时，不建议用'%key%'的格式进行模糊查询--- 索引引擎会使用全表扫描而不会使用索引
7.在查询时，对连续的值，能使用 between就不要用in
8.在查询时，尽量避免在where子句中对使用表达式或使用函数-- 索引引擎会使用全表扫描而不会使用索引
9.避免频繁的创建和删除临时表，以减少对系统表资源的消耗
10.只含有数值的值尽量不要设为字符型；
11.尽量不要使用*，而用字段名替换
12.尽量避免大事务操作（运时间过久，长时间不提交的事务）