数据库总结（常见笔面试题）

- 日理万妓 2022-04-21 21:08 559阅读 0赞

## 数据库 ##

### 1.SQL \*Plus 使用命令 ###

sqlplus 用户名 密码   登录

sql /as sysdba  登录管理员

### 2.Orcle 默认端口1521 MySql 3306 ###

### 3.三类sql语句 ###

（1）ddl：数据定义语言   数据库表的创建、删除、修改

（2）dml：数据操作语言   数据库的表数据增删改查

（3）dcl：数据控制语言   用户

### 4.简单查询 ###

--虚表
    
            SELECT (10+2)*3 FROM dual;
    
                  --查询系统时间
    
            SELECT sysdate FROM dual;

### 5.空值的处理 ###

NVL(comm,0) 将comm如果为空值，作0处理

### 6.别名(AS) ###

（1）也可以省略不写

（2）中文用双引号

（3）大小写区分  使用双引号

### 7.拼接（|| 或者 CONCAT（X1，X2）） ###

### 8.去重（DISTINCT） ###

SELECT DISTINCT depno FROM emp;
    
        SELECT DINTINCT depno,sal FROM emp;

### 9.过滤（WHERE） ###

### 10.日期、字符串 （需要使用单引号） ###

### 11.模糊查询：LIKE   % 代表0个或多个字符    \_代表一个字符 ###

### 12.排序：ORDER BY （ASC 升序，DESC 降序） ###

可以按照 列名，别名，那一列（如2），多列进行排序

分组：GROUP BY

### 13. 空值在排序时按照最大值 ###

### 14.单行函数： ###

（1）大小写转换  UPPER  LOWER

（2）字符串拼接  CONCAT（X1，X2）

（3）截取

SELECT SUBSTR('hello world',2,20) FROM  dual;
    
      SELECT TIRM('H' FROM 'HELLO WORLD') FROM dual;

（4）获得长度

SELECT LENGTH('Hello world') FROM dual;

（5）补齐函数

SELECT LPAD(sal,10,'0') FROM emp;
      SELECT RPAD(sal,10,'0') FROM emp;

（6）替换

SELECT REPLACE('hello','o','a') FROM dual;

### 15.日期相关 ###

两月相差月数  MONTHS\_BETWEEN

向指定日期加上若干月数 ADD\_MONTHS

指定日期的下一天  NEXT\_DAY

本月最后一天 LAST\_DAY

日期四舍五入 ROUND

### 16.数据类型 ###

字符串：CHAR VARCHAR VARCHAR2

CHAR(10) 固定长度,VARCHAR(10)长度可变，VARCHAR2 Orale特有

数字类型：NUMBER(5),NUMBER(7,2)

### 17.类型转换 ###

**    隐式转换**：

SELECT '92'+2    FROM dual; --字符串转化为数字
    
    SELECT '95'||2 FROM dual; --数字转换为字符串

**显示转换**：

SELECT TO_CHAR(sysdate,'yyyy-mm-dd') FROM dual;
 
 SELECT *  FROM emp
 
 WHERE hiredate < TO_DATE('1981-04-02','yyyy-mm-dd');

### 18.单行函数和多行函数区别 ###

单行函数：作用于一行数据，返回一个值

多行函数：作用于多行数据，返回一个值

### 19.多行函数： ###

最大最小值：MAX(),MIN()

平均值：AVG

计算和：SUM

统计数量：COUNT （空值不作计数）

### 20.  WHERE和HAVING的区别 ###

WHERE：先过滤后分组

SELECT deptno,COUNT(empno) FROM emp
    
    WHERE deptno != 10
    
    GROUP BY deptno;

HAVING：先分组后过滤

SELECT deptno,COUNT(empno) FROM emp
    
    GROUP BY deptno
    
    HAVING deptno != 10;

### 21.单行子查询和多行子查询的操作符 ###

单行子查询操作符：>  <  <> !=  =  >=

多行子查询操作符：IN（列表中的任何一个）

ANY（子查询的任意一个）

ALL（子查询的所有值）

### 22.集合： ###

并集：UNION/UNION ALL

交集：INTERSECT

差集：MINUS

### 23.多表查询： ###

Oracle 标准：

**   （1）等值连接**

--等值连接（可以加上别名，但是有了别名就不能有真名）

SELECT ename ,sal, e.deptno, dname,loc FROM emp e, dept d
    
    WHERE e.deptno = d.deptno;

**（2）非等值连接**

--查询大于所在部门平均薪水的员工

-- 薪水大于平均薪水    员工的部门编号  = 平均薪水的部门

SELECT *  FROM emp e1,(SELECT deptno, AVG(sal) avg_sal FROM emp GROUP BY deptno) e2
    
    WHERE e1.sal > e2.avg_sal  AND e1.deptno = e2.deptno;

**（3）外连接：想要那边显示的全，在那边加上 +**

**          （4）自连接**

--多表查询-自连接

SELECT e1.* FROM emp e1,emp e2
    
    WHERE e1.deptno = e2.deptno  AND e2.ename = 'SMITH';

SQL99标准：

**（1）自然连接：**

使用自然连接，自动匹配列名相同的  NATUARL JOIN

SELECT ename,dname  FROM emp
     NATURAL JOIN dept;

USING指明比较列名

SELECT ename,dname FROM emp
    
    JION demp
    
    USING (depno);

使用ON

SELECT ename,dname  FROM emp
    
    JOIN dept
    
    ON (emp.deptno = dept.deptno AND dept.deptno != 20);

**  （2）外连接（OUTER 关键字可以省略）**

SELETE ename,dname from emp
    
    LEFT OUTER JOIN dept   --左LEFT  右RIGHT 满FULL
    
    ON dept.deptno = emp.deptno;

### 24.Oracle不会自动提交事务，MySql会自动提交事务 ###

### 25.添加数据：INSERT  ###

INSERT INTO my_emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
    
    VALUES(1000,'李四','测试',1100,sysdate,5000,2000,30);
    
    COMMIT;

### 26.修改数据：UPDATE ###

UPDATE my_emp；
    
    SET ename = '小明'
    
    WHERE empno= 1002
    
    COMMIT;

### 27.删除：DELETE ###

DELETE FROM my_emp
    
    WHERE empno = 1003;
    
    COMMIT;

### 28.创建表 ###

CREATE TABLE student
    
    (s_id NUMBER(2)，
    
    s_name VARCHAR2(100));

### 29.设定默认值 ：DEFAULT ###

s\_age NUMBER(2) DEFAULT 18

### 30.删除表 ###

DROP TABLE student; -- 表数据结构都被删除

DELETE TABLE student; -- 删除表数据

TRUNCATE TABLE student; --删除表数据

### 31.TRUNCATE 和 DELETE的区别 ###

TRUNCATE：不能回滚，不能加过滤条件

DELETE：能回滚，能加过滤条件

### 32.修改表 ###

**   （1）添加列 ADD**

ALERT TABLE student
    
    ADD(email VARCHAR2(100));

**   （2）修改列 MODIFY**

ALERT  TABLE student
    
    MODIFY(email VARCHAR2(100) NOT NULL);

**   （3）删除列 DROP**

ALERT  TABLE student
    
    DROP email ;

**   （4）列重命名  RENAME**

ALERT  TABLE student
    
    RENAME email TO s_email ;

### **33.修改表名称** ###

RENAME TABLE emp TO emps;

### 34.约束类型 ###

主键：PRIMARYKEY 非空约束+唯一性约束

外键：FOREIGNKEY 用于表之间建立关系

非空：NULL

唯一：UNIQUE

检查：CHECK

CREATE TABLE person
    
      (
    
              p_id NUMBER(4) PRIMARY KEY,  --主键
    
              p_name VARCHAR2(4) NOT NULL,--非空约束
    
              email VARCHAR2(11) UNIQUE ,--唯一性约束
    
              age  NUMBER(2)  CHECK (age >= 18)--检查约束
    
      );

### 35.外键约束 CONSTRAINT  card\_id REFERENCES id\_card(card\_id) ###

### 36.赋权限 grant creat view scott; 赋创建视图权限 ###

### 37.视图 ###

视图是基于一个表或多个表或视图的虚表，简单来说，可以将视图理解为，已经存储起来的SELECT语句

### 38.视图的优点： ###

（1）简化复杂查询

（2）限制数据访问

（3）同样的数据，可以有不同的显示方式

### 39.创建视图 ###

CREATE OR REPLACE VIEW emp_view
    
      AS deptno, AVG(sal) avg_sal,MAX(sal) max_sal,MIN(sal)  min_sal FROM emp
    
      GROUP BY deptno;
    
      WITH READ ONLY; --只读视图

### 40.查看视图 ###

SELECT * FROM emp_view;

### 41.删除视图 ###

DROP VIEW emp_view;

### 42.序列 ###

序列：供多个用户用来产生唯一数值的数据库对象，主要用来实现主键自增

MySql 可以设置主键自增，Oracle不行，需要通过序列来实现

### 45.创建序列   ###

--创建一个产生学生主键的序列

CREATE SEQUENCE s_sequence ;
    
           INSERT INTO student
    
           VALUES(s_sequence.nextval,'张'||s_sequence.currval,18+s_sequence.currval);

.nextval  --下一个序列值   .currval   当前序列值

### 44.索引：加快查询速度 ###

### 45.创建索引 ###

**（1） 单个索引**

CREATE INDEX id_index
    
    ON student(s_id);

**   （2）联合索引**

CREATE INDEX id_age_index
    
    ON student (s_id,s_age));

### 46.DDL语句 ###

**   （1）创建一个用户**

create user guigu identfided by guigui

**   （2）登录**

sqlplus guigu/guigu

###  47.事务的四大特性 ACID ###

原子性：一个事务必须视为一个不可分割的最小单元，整个事务中，要么都提交完成 ，要么全部回滚。

一致性：事务前后，数据总额相等

隔离性：所有操作在提交前，其他事务是不可见的

持久性：一旦事务提交，对数据的改变是永久性的

### 48.隔离级别 ###

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:82.95pt;"> 隔离级别 </td> 
 <td style="vertical-align:top;width:82.95pt;"> 脏读 </td> 
 <td style="vertical-align:top;width:82.95pt;"> 不可重复读 </td> 
 <td style="vertical-align:top;width:82.95pt;"> 幻读 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:82.95pt;"> 未提交读 </td> 
 <td style="vertical-align:top;width:82.95pt;"> 是 </td> 
 <td style="vertical-align:top;width:82.95pt;"> 是 </td> 
 <td style="vertical-align:top;width:82.95pt;"> 是 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:82.95pt;"> 已提交读&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </td> 
 <td style="vertical-align:top;width:82.95pt;"> 否 </td> 
 <td style="vertical-align:top;width:82.95pt;"> 是 </td> 
 <td style="vertical-align:top;width:82.95pt;"> 是 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:82.95pt;"> 可重复（MySql默认） </td> 
 <td style="vertical-align:top;width:82.95pt;"> 否 </td> 
 <td style="vertical-align:top;width:82.95pt;"> 否 </td> 
 <td style="vertical-align:top;width:82.95pt;"> 是 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:82.95pt;"> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 串行化 </td> 
 <td style="vertical-align:top;width:82.95pt;"> 否 </td> 
 <td style="vertical-align:top;width:82.95pt;"> 否 </td> 
 <td style="vertical-align:top;width:82.95pt;"> 否 </td> 
 </tr> 
 </tbody> 
</table>

脏读：事务A读取了事务B还未提交的数据

不可重复读：两次的读的数据不一致

幻读：事务A和B都修改了数据，在事务A看来，修改的不一样

### 49. MYISAM 和 INNODB的区别 ###

MYISAM：不支持事务，不支持外键，查询总行数时，不需要全表扫描

INNODB：支持外键和事务，查询总行数时需要进行全表扫描

### 50.索引的优缺点，什么情况下使用 ###

优点：提高查询效率

缺点：更新数据时效率低，因为要同时更新索引

如果数据要进行频繁地查询时建立索引，如果频繁修改数据，不建议使用索引

### 51.Sql语句的优化 ###

（1）尽量使用索引进行查询

（2）尽量用UNION ALL 而不是UNION

（3）用WHERE替代HAVING

（4）减少 \* 号的使用

（5）可以过滤掉最大数量记录的条件必须写在WHERE子句的之右

（6）用TRUNCATE替代DELETE

（7）用 >= 替代 >

### 52.在定义主键和唯一性约束时，会自动创建索引   ###

### 53.乐观锁和悲观锁 ###

**悲观锁**：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作

**实现方式**：数据库锁机制

**乐观锁**：假设不会发生并发冲突，只在提交时检查是否违反数据完整性

**   实现方式**：使用Version版本或者时间戳

### **54. 写出一条Sql语句：取出表A中第31到第40记录 （Mysql）** ###

SELECT * FROM A LIMIT 30,10;

### 55. 写出一条Sql语句：取出表A中第31到第40记录 （Oracle）   ###

SELETE * FROM(SELETE t.*,rownum RN FROM (SELECT  *  FROM A) t) WHERE RN BETWEEN 31 AND 40;

### 补充 ###

### 1.Sql语句的SELETE语句完整的执行顺序 ###

（1）FROM子句

（2）WHERE

（3）GROUP BY

（4）使用HAVING 进行过滤

（5）计算所有的表达式

（6）SELECT字段

（7）ORDER BY

FROM（生成笛卡尔积）->JOIN ON（筛选）->WHERE（过滤）->GROUP BY（进行分组）-> HAVING（过滤）->SELECT（处理SELECT语句）->DINTINCT(去重)->ORDER BY（排序）

### 2.存储过程以及使用的好处 ###

存储过程是一组为了完成特定功能的SQL语句集，经过编译存储在数据库中，来供用户使用。

优点:SQL代码封装和执行效率的提高（因为SQL语句要进行分析和编译、优化等）

### 3.在千万级的数据库查询中，如何提高效率 ###

尽量避免全局扫描和WHERE子句对NULL值的判断（会导致搜索引擎放弃索引）

### 4.如果只有一个返回值，用存储函数；否则，就用存储过程 ###

### 5.PL/SQL ###

（1）**“Hello World!” ** **入门程序**

declare
    
        说明部分    （变量说明，光标申明，例外说明 ）
    
    begin
    
    语句序列
    dbms_output.put_line(‘hello world’);
    
    exception
    
          例外处理语句 
    
    end;

（2）赋值是使用冒号等号“:=”（中间不能有空格）

（3）说明变量  （char, varchar2, date, number, boolean, long)

（4）为变量传值

declare
    
        vname varchar(30);
    
        vsal  number(10);
    
    begin
    
        select ename, sal into vname, vsal from emp where empno = &empno;
    
      dbms_output.put_line(vname || vsal);
    
    end;