C语言开发工程师笔试面试题整理（七）——数据库SQL

灰太狼 2022-12-08 01:50 227阅读 0赞

### 1.简述一下数据库的三大范式（2020兴业数金面试题） ###

> 答：第一范式（1NF）:原子性 字段不可再分,否则就不是关系数据库;
> 
> 第二范式（2NF）:唯一性 一个表只说明一个事物;
> 
> 第三范式（3NF）:每列都与主键有直接关系，不存在传递依赖；即非主属性既不部分函数依赖与候选码也不传递函数依赖于候选码。
> 
> PS：第二范式要遵循第一范式，第三范式要遵循第二范式。

### 2.索引 ###

### 数据库索引的作用，索引是不是越多越好（2020兴业数金面试题） ###

> 答：索引的作用是提高查询效率，降低I/O次数，提高数据库性能。
> 
> 创建索引不一定会提高查询效率吗，要看创建索引的合理性。
> 
> 索引不是越多越好，索引也要占内存空间。过多的索引反而会降低数据库的性能。

### 哪些数据库及字段适合创建索引 ###

> 答：数据库适合创建索引的规则如下：
> 
> 1.数据量比较大的表应该创建索引；
> 
> 2.表的主键，外键应该创建索引；
> 
> 3.经常需要和其他表建立连接，在连接字段应该创建索引；
> 
> 4.经常出现在where子句中的字段，应该创建索引。

### 哪些数据库及字段不适合创建索引 ###

> 答：数据库不适合创建索引的情况：
> 
> 1.小型表(数据量低于300行)不要建立索引。
> 
> 2.比较大的文本字段或者长度较长的字段，不适合创建索引；
> 
> 3.频繁进行数据操作的表，不适合创建过多的索引，因为额外维护索引表需要更多的开销；

### 3.什么是主键、外键，各自的作用（2020招商网络科技测开） ###

> 答：1.主键是能确定一条记录的唯一标识，比如，一条记录包括身份正号，姓名，年龄。
> 
> 身份证号是唯一能确定你这个人的，其他都可能有重复，所以，身份证号是主键。  
> 2.外键用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性。
> 
> 比如，A表中的一个字段，是B表的主键，那他就可以是A表的外键。
> 
> 定义主键和外键主要是为了维护关系数据库的完整性

### 4.SQL语言的分类 ###

> 1.数据定义语言（DDL）：在数据库中创建新表或删除表，包括create 和drop；创建索引等。
> 
> 2.数据操作语言（DML）：
> 
> （1）数据查询语句：从表中获得数据，select，where，order by，group by和having
> 
> （2）数据修改语句：包括insert（插入），update（修改）和delete（删除）
> 
> 3.数据控制语言（DCL）：
> 
> （1）事务处理语句：begin transaction，commit和rollback
> 
> （2）授权语句：语句通过grant或revoke获得或撤销许可，确定单个用户和用户组对数据库对象的访问

### 4.什么是事务？事务的四个特性ACID分别是什么？（2020恒生等考的次数很多） ###

> 答：事务（Transaction）是并发控制单位，是用户定义的一个操作序列，这些操作要么都做，要么都不做，是一个不可分割的工作单位。事务通常以BEGIN TRANSACTION开始，以COMMIT或ROLLBACK结束。
> 
> 2.事务具有四个特征：原子性（ Atomicity ）、一致性（ Consistency ）、隔离性（ Isolation ）和持续性（ Durability ）。这四个特性简称为 ACID 特性。  
> 事务中的所有操作要么全部执行，要么都不执行； 如果事务没有原子性的保证，那么在发生系统 故障的情况下，数据库就有可能处于不一致状态。 因而，事务的原子性与一致性是密切相关的。  
> ⑴ 原子性（Atomicity）  
> 　　原子性是指事务包含的所有操作要么全部成功，要么全部失败。或者说，所有操作要么都做，要么都不做。  
> ⑵ 一致性（Consistency）  
> 　　一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。  
> ⑶ 隔离性（Isolation）  
> 　　隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。  
> ⑷ 持久性（Durability）  
> 　　持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

### 5.数据库三个模式 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2Mzg0NjU3_size_16_color_FFFFFF_t_70][]

> 模式描述的是数据的全局逻辑结构，外模式描述的是数据的局部逻辑结构。一个模式可以对应多个外模式，对于每一个外模式，数据库都有一个外模式/模式映像。
> 
> **数据的逻辑独立性**：模式改变时，对应的外模式/模式映像发生改变。应用程序是根据外模式编写的，从而应用程序不必，保证了数据与程序的逻辑独立性
> 
> **数据的物理独立性**：数据库的物理结构改变时，对应的模式/内模式映像发生改变，可以使模式保持不变，从而应用程序保持不变，保证了数据与应用程序的物理独立性。

### 数据库模式分解的三个准则 ###

> 答：一个模式的分解是多种多样的，但是分解后产生的模式应与原模式等价，从不同角度去观察问题，对等价的概念，形成三种不同的定义：
> 
> 1.分解具有无损连接性
> 
> 2.分解要保持函数依赖
> 
> 3.分解既要保持函数依赖，又要具有无损连接性。

### 6.查询优化策略 ###

> 1.选择运算尽可能早做，在优化策略中这是最重要，最基本的一条，它常常可以使执行节约几个数量级，因为选择运算一般使计算的中间结果大大变小
> 
> 2.把投影运算和选择运算同时进行。如果有若干的投影和选择运算，并且他们都对同一个关系进行操纵的话，就可以在扫描此关系的同时完成所有的这些运算以避免重复扫描关系。
> 
> 3.把投影同其前或其后的双目运算结合起来，没有必要为了去掉某些字段而扫描一遍关系
> 
> 4.把某些选择同在他前面要执行的笛卡儿积结合成一个连接运算，连接特别是等值连接运算要比同样关系上的笛卡儿积省很多时间
> 
> 5.找出公共子表达式

### 7.数据库有哪些隔离机制（2020招商银行金融科技） ###

> 数据库事务的隔离级别有4种，由低到高分别为Read uncommitted 、Read committed 、Repeatable read 、Serializable。
> 
> **读未提交 （Read uncommitted）**
> 
> 　　读未提交，一个事务可以读取另一个未提交事务的数据。
> 
> **读提交（Read Committed）**
> 
> 　　读提交，只能读到已经提交了的内容
> 
> **可重复读(Repeated Read)**
> 
> 　　可重复读，专门针对“不可重复读”这种情况而制定的隔离级别，自然，它就可以有效的避免“不可重复读”。而它也是MySql的默认隔离级别。
> 
> **序列化 Serializable**
> 
> 　　这是数据库最高的隔离级别，这种级别下，事务“串行化顺序执行”，也就是一个一个排队执行。这种级别下，“脏读”、“不可重复读”、“幻读”都可以被避免，但是执行效率低，性能开销最大。

### 8.什么是脏数据（2020招商银行金融科技） ###

> 答：指当一个事务正在访问数据，并且对数据进行了修改，而这种数据还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据还没有提交那么另外一个事务读取到的这个数据我们称之为脏数据。

### 9.union的作用、约束条件（2020中信银行笔试） ###

> 答：union操作符用于合并两个或多个select语句的结果集
> 
> 用法：select 列名1 from 表1 union select 列名2 from 表2
> 
> 约束条件：union内部的每个select语句必须拥有相同数量的列，列也拥有相似的数据类型。同时每个select语句中的列的顺序必须相同。union中每个查询必须包含相同的列、表达式、聚集函数。
> 
> 并且union只查询不同的列，去除重复的列。

### 10.常见的数据库sql语句写法 ###

> 记住以下书写规则，任何简单的查询语句不在话下。 面试一般不会考，笔试一般让你写sql查询，建表不会考，可以去牛客上练习一下。

### **select基本查询语句** ###

> **（2020恒生、2020科达）等公司笔试时考到；一般不会太复杂，掌握以下例子中出现的各命令的使用场景就差不多能应对SQL方面的考试了。**

> **复杂的查询语句可能包括以下**
> 
>     select  字段1，字段2 
>     from 表1，表2，表3
>     where 表1.字段a=表2.字段a and 表2.字段b=表3.字段b  
>     group by 字段1，字段2  （按照字段1，2进行分组）   
>     having  聚合函数+限制条件对分组结果指定条件    如:[大于60分]
>     order by  字段  asc升序 /desc降序 
>     limit   topN

### group by+having分组查询 ###

> 使用group by子句分组，分组方法是按照指定的一列或多列值分组，值相等的为一组，使用group by子句后分组中的列名列表中只能出现分组属性和统计函数;
> 
> **举例：**
> 
> **1.查询各课程号及相应的选课人数**
> 
>     select cno, count(sno)
>     from sc
>     group by cno
> 
> having 语句作用与组，对group by后满足条件的组进行筛选；
> 
> **举例：**
> 
> **2.查询了选修了3门以上课程的学生学号**
> 
>     select sno from sc
>     group by sno
>     having count(*)>3

### order by 查询结果排序 ###

> 使用order by子句，可以按照一个或多个属性列排序
> 
> 升序使用order by 字段1，字段2 asc
> 
> 降序使用order by 字段1，字段2 desc
> 
> **举例：**
> 
> **1.查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列**
> 
>     select sno, grade 
>     from sc
>     where cno='3'
>     order by grade desc
> 
> **2.查询全体学生情况，查询结果将按所在系的系号升序排列，同一系中的学生按年龄降序排列**
> 
>     select *
>     from student
>     order by sdept asc,sage desc

### count\\sum\\avg\\max\\min统计函数 ###

> **count 用于计数**
> 
> 用法：count(\[distinct|all\]列名)
> 
> **举例：**
> 
> **（1）查询学生总人数**
> 
>     select count(*)
>     from student
> 
> **（2）查询选修了课程的学生人数**
> 
>     select count(distinct sno)
>     from sc
> 
> **sum用于求和**
> 
> 用法：sum(\[distinct|all\]列名)
> 
> **avg用于求平均值**
> 
> 用法：avg(\[distinct|all\]列名)
> 
> **举例：**
> 
> **（3）计算1号课程的学生平均成绩**
> 
>     select avg(grade)
>     from sc
>     where cno='1'
> 
> **max求最大值**
> 
> 用法：
> 
> max(\[distinct|all\]列名)
> 
>     select max(grade)
>     from sc
>     where cno='1'
> 
> **min求最小值**
> 
> 用法：
> 
> min(\[distinct|all\]列名)

### in子查询 ###

> 使用in子查询的场景：如果在嵌套查询中子查询的结果是一个集合，可以使用in
> 
> **举例：**
> 
> **1.查询与“刘晨”在同一个系学习的学生**
> 
>     select sno,sname,sdept
>     from student 
>     where sdept in
>         (
>             select sdept
>             from student  
>             where sname='刘晨'
>         )

### NULL空值查询 ###

> 涉及空值的查询使用谓词is null 或is not null
> 
> is null 不能用=null代替
> 
> **举例：.某些学生选修课程后没有参加考试，所以有选课记录但没有考试成绩。**
> 
> **1.查询缺少成绩的学生的学号和相应的课程号**
> 
>     select sno,cno from sc where grade is null
> 
> **2.查询所有有成绩的学生学号和课程号**
> 
>     select sno,cno from sc where grade is not null

### distinct查询去重 ###

> select 字句中使用distinct短语会消除取值重复的行，distinct短语的作用范围是所有目标列
> 
> **举例：**
> 
> **1.查询选修了课程的学生学号**
> 
>     select distinct sno from sc
> 
> **2.查询选修课程的各种成绩**
> 
>     select distinct cno,grade from sc

### update更新 字段 ###

> **语法：Update** 表名称
> 
> **set** 列名称=新值
> 
> **where** 列名称= 某值
> 
> **举例：**
> 
> **1.将学生95001的年龄修改为22岁**
> 
>     update student set sage=22 where sno='95001'
> 
> **2.将所有学生的年龄增加1岁**
> 
>     update student set sage=sage+1
> 
> **3.将信息系所有学生的年龄增加1岁**
> 
>     update student set sage=sage+1 
>     where sdept='IS'

### delete删除字段 ###

> 语法：delete from 表名 where 条件
> 
> 功能：删除指定要删除的元组，缺省表示删除表中所有元素
> 
> 举例：
> 
> **1.删除学号为95019的学生记录**
> 
>     delete from student where sno='95019'
> 
> **2.删除2号课程的所有选课记录**
> 
>     delete from sc where cno='2'
> 
> **3.删除计算机科学系所有学生的选课记录**
> 
>     delete from sc where sno in
>     (
>         select sno from student where sdept='cs'
>     )

### view创建视图 ###

> 语法：
> 
> create view 视图名 （列名1，列名2...）
> 
> as
> 
> select 子查询语句+限制条件
> 
> **举例：建立“信息系IS”选修了课程号为“1”的学生视图**
> 
>     create view IS_S1(sno,sname,grade)
>     
>     as
>     
>     select student.sno, sname, grade
>     
>     from student, sc
>     
>     where sdept='IS' and student.sno=sc.sno and sc.cno='1'

### drop 删除表 ###

> 基本语法：drop table 表名
> 
> **举例：删除学生表student**
> 
>     drop table student

### **alter修改表** ###

> 语法：
> 
> **alter table 表名**
> 
> add 新列名 数据类型\[完整性约束\]
> 
> drop 完整性约束名（用于删除表中的列）
> 
> modify 列名 数据类型（用于修改原有列定义）
> 
> **举例：**
> 
> **1.向student表增加“入学时间”列，其数据类型为日期型**
> 
>     alter table student add scome date
> 
> **2.将年龄的数据类型改为半字长整数**
> 
>     alter table student modify sage smallint
> 
> **3.删除学生姓名必须唯一值的约束**
> 
>     alter table student drop unique(sname)

### index索引创建、删除 ###

> **创建索引语法：**
> 
> create \[unique\] \[custer\] index 索引名
> 
> on 表名 （列名1 次序1，列名2 次序2）
> 
> unique 表明此索引的每一个索引值只对应唯一的数据记录
> 
> cluster 表示要建立的索引是聚簇索引
> 
> **举例：1.在student表的sname（姓名）列上建立一个聚簇索引，而且student表中的记录将按照sname值的升序存放**
> 
>     create cluster index stusname on student (sname)
> 
> **2.为student，course，sc三个表建立索引。其中student表按学号升序建唯一索引，course表按课程号升序建唯一索引，sc按学号升序和课程号降序建唯一索引**
> 
>     create unique index stusno on student(sno)
>     create unique index coucno on course(cno)
>     create unique index scno on sc(sno asc,cno desc)
> 
> **索引删除语法：**drop index 索引名
> 
> **举例：**
> 
> **3.删除student表的stusname索引**
> 
>     drop index stusname

### grant\\revoke授权与收回 ###

> SQL中使用grant向用户授予权限，使用revoke语句收回授予权限
> 
> grant语法：
> 
> grant 权限1，权限2
> 
> on 对象类型1 对象名1，对象类型2 对象名2
> 
> to 用户1，用户2
> 
> \[with grant option\]（可选，允许将此权限再授予其他用户）
> 
> **举例：1.把查询student表的权限给用户U1**
> 
>     grant select on table student to u1
> 
> **2.把对表SC的查询权限授予所有用户**
> 
>     grant select on table sc to public
> 
> **3.把查询student表和修改学生学号的权限授予给用户U4**
> 
>     grant update(sno),select on table student to U4
> 
> **4.把对表sc的insert权限授予用户U5，并允许将此权限再授予其他用户**
> 
>     grant insert on table sc to u5 
>     with grant option
> 
> revoke收回权限
> 
> 用法：revoke 权限
> 
> on 对象类型1 对象名1，对象类型2 对象名2
> 
> from 用户1，用户2
> 
> \[cascade|restrict\]
> 
> **举例：5.把用户U4修改学生学号的权限收回**
> 
>     revoke update(sno) on table student from U4
> 
> **6.收回所有用户对表视图V的查询权限**
> 
>     revoke insert on V from U5

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2Mzg0NjU3_size_16_color_FFFFFF_t_70]: /images/20221123/914c171021e64b868b09892983217a25.png