华为网络技术大赛笔记——数据库基础原理_amm 哪些模块自动调整大小-CSDN博客

数据库基础原理

本文包括：
1、SELECT 查询基础
2、典型单行函数
3、典型分组（多行/聚集）函数
4、多表查询
5、典型子查询
6、表结构操作语句
7、典型数据操作语句——DML
8、典型事务操作语句
9、SQL的简单优化

1、SELECT 查询基础

select三大能力
- 投影：选择合适列
- 选择：选择合适的行
- 连接：把多个表的列连接在一起
语法：
```
  SELECT *|{[DISTINCT] column | expression[alias],...} FROM table;
```
- 大小写不敏感
- 可多行书写，可以缩进
- 子句一般另取一行
- []是可选的
- 关键字不可以缩写，也不能分割跨行
NULL值
- 不可用的，未分配的
列的别名
- 重命名一个列的标题
- 紧跟在关键字后，as，可省略
- 如果有特殊字符，比如列的别名中包含空格，或者想大小写敏感，则用双引号括起来
级联操作
- 将列或字符串和其他列串联，由两条竖线标识 ||
清除重复行
- 普通的查询当中，如果有重复项，默认会显示出来
- 加入DISTINCT关键字，就可以消除重复行

查询数据的限制和排序

where

  SELECT *|{[DISTINCT] column | expression[alias],...} FROM table 
  [WHERE conditions];

查询字符串或者日期，要加单引号
比较条件
- <, >, = , <=, >=
- BETWEEN … AND … ：闭区间
- IN (SET)：匹配任意一个值
- LIKE ：模糊匹配
  - 针对字符串
  - %:通配任意长度的字符
  - _:通配单位长度的字符
- IS NULL ：是否为空
逻辑条件
- AND,OR,NOT
- 注意优先级，NOT>AND>OR，如果没有括号，无论书写顺序怎样，都会按NOT>AND>OR计算
排序
- order by：默认升序 ASC
- order by XX DESC：降序排列
- order by deptno,sal DESC：首先对deptno升序排列，再将重复的内容对sal降序排列

优先级规则

算数操作符
连接操作符
比较（><=…）
IS [NOT] NULL， LIKE， [NOT] IN
[NOT] BETWEEN … AND …
NOT逻辑比较条件
AND逻辑比较条件
OR逻辑比较条件

可以使用圆括号改变优先级顺序

2、典型单行函数

大小写转换
- LOWER(‘SQL Course’)——sql course
- UPPER(‘SQL Course’)——SQL COURSE
- INITCAP(‘SQL Course’)——Sql Course
字符串
- CONCAT(‘hello’,‘world’)：连接，答案是helloworld
- SUBSTR(‘helloworld’,1,5)：从第一个位置取5位，答案是hello
- LENGTH(‘helloworld’)：取字符串长度，结果是10
- INSTR(‘helloworld’,‘w’)：返回w在helloworld的第几位，结果是6
数字
- ROUND(45.926,2)：保留两位小数，四舍五入
- TRUNC(45.926,2)：直接截取两位小数
日期
- SYSDATE：返回当前系统的日期
  - 默认格式：19-AUG-15（2015年8月19号）
- 注意：日期可以加减，两个日期相减得到两个日期之间的天数
- 常用日期函数
  - MONTHS_BETWEEN(X,Y)：返回两个日期之间的月数，有小数点
  - ADD_MONTHS(X,Y)：返回两个日期月份相加的日期
  - NEXT_DAY(X,DAY)：返回当前日期X距离下一个DAY的最近的日期，DAY取值可以为数字从1（星期天）到7（星期六），也可以为’MONDAY’到’SUNDAY’
  - LAST_DAY(X)：返回当前月的最后一天
转换函数（日期，数字，字符三者的转换，字符作为中间桥梁）
- TO_NUMBER
  - 字符转数字：TO_NUMBER(‘00001228’)：结果是1228
- TO_DATE
  - 字符转日期：TO_DATE(‘2015-07-28 19:15:37’,‘yyyy-mm-dd hh24:ml:ss’)
- TO_CHAR
  - 日期转字符：TO_CHAR(mydate,‘YYYY-MM-DD’)
  - 数字转字符：TO_CHAR(mynum,格式）
    - TO_CHAR(sal,’$'99,999.00）
    - 9代表这里显示一个数字
    - 0代表强制显示0
    - $代表一个美元符号
    - L代表浮动的本地流通货币符号
    - .代表一个小数点
    - ,代表千分符
隐式转换：Oracle可以从VARCHAR2/CHAR自动转换到NUMBER或者DATE

3、典型分组（多行/聚集）函数

AVG
SUM
COUNT:返回匹配指定条件的行数。
- COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入），也可以写数字123，代表表的第123列
- COUNT(*) 函数返回表中的记录数
- COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目
MAX
MIN
GROUP BY子句
- 次序：首先执行
- 功能：分类汇总
- 比如：求员工表中不同部门的员工的平均工资
HAVING
- 不能单独存在，只能接在GROUP BY后面
- 功能：对分组后的结果进行过滤
- 比如：查询每个部门中员工最高工资大于10000的部门以及工资
分组函数嵌套
- 把多个分组函数嵌套在一起，完成复杂功能
- 显示部门最大平均工资
```
  SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno;
```

4、多表查询

自连接（内连接）
- 一个表自己与自己建立连接称为自连接
等值连接
- 在连接条件中用等于号比较被连接列的列值，结果集列出被连接表中的所有列，包括重复列
外连接
- 左外连接
  - 左向外连接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。
  - 视频的语法：
```
  SELECT xxx from emp,dept WHERE
  emp.deptno = dept.deptno(+);
```
- 右外连接
  - 返回右边的匹配行，不考虑左边是否有相应行，若左边没有相应行，则显示NULL
  - 视频的语法：
```
  SELECT xxx from emp,dept WHERE
  emp.deptno(+) = dept.deptno;
```
- 全连接（FULL JOIN）：相当于LEFT OUTER JOIN+RIGHT OUTER JOIN
视频没讲交叉连接（CROSS JOIN）：笛卡儿积，假设A表有8行，B表有3行，那么结果集有8x3=24行
视频的自连接用的是ANSI连接语法，不推荐

5、典型子查询

定义：也叫嵌套子查询，一个查询的输出，作为另一个查询的输入
- 在查询时基于未知时应该考虑子查询
- 子查询必须包含在括号内
- 将子查询放在比较运算符的右侧，以增强可读性
- 对单行子查询用单行运算法
- 对多行子查询用多行运算符
种类
- 单行：结果集只有一行一列
  - 单行操作符：< > = <= >=
  - 查询工资比RACHEL高的员工姓名与工资
```
  SELECT enmae,sal
  FROM emp
  WHERE sal>(
  	SELECT sal FROM emp WHERE enmae='RACHEL');
```
- 多行:：结果集多行一列
  - 操作符：IN，ANY,ALL
  - IN:等于子查询返回的任何一个
  - ANY:子查询返回的任意值比较
    - 薪水低于销售部任意员工的非销售部员工信息
  - ALL：和子查询的所有值比较
- 空值
  - 子查询为空值
  - 并没有什么用。。。

6、表结构操作语句

表是数据库存储数据的逻辑概念，表分为行和列
列约束
- not null
- unique
- primary key
- foreign key
- check（条件检查）
表约束
- unique
- primary key
- foreign key
- check

创建表格

  CREATE TABLE EMP(
  	ID VARCHAR2(6) PRIMARY KEY,
  	NAME VARCHAR2(20) NOT NULL,
  	HIREDATE DATE DEFAULT SYSDATE NOT NULL,
  	SALARY NUMBER(7,2)
  	CONSTRAINT EMP_SAL_MIN CHECK(SALARY>1000)
  );

HIREDATE如果没有设置值，则默认是系统当前日期

当插入EMP_SAL_MIN字段时，如果SALARY小于1000，则插入不成功

修改表格
- 增加列
```
  ALTER TABLE XX ADD COLUMN_NAME DATATYPE；
```
- 删除列
```
  ALTER TABLE XX DROP COLUMN COLUMN_NAME；
```
- 修改列名
```
  ALTER TABLE XX RENAME COLUMN COLUMN_NAME1 TO COLUMN_NAME2；
```
- 修改表名
```
  ALTER TABLE XX RENAME TO XXX；
```
- 删除表
```
  DROP TABLE XX CASCADE CONSTRAINTS
```
  RESTRICT：删除表是有限制的。欲删除的基本表不能被其他表的约束所引用如果存在依赖该表的对象，则此表不能被删除
  
  CASCADE：删除该表没有限制。在删除基本表的同时，相关的依赖对象一起删除
查看表结构
```
  	DESCRIBE table;
```

7、典型数据操作语句——DML

数据操作语言(DML)是SQL的核心部分，DML：Data Manipulation Language，包括：
- insert
- update
- delete
新增行
```
  INSERT INTO TABLE[COLUME [,COLUME...]] VALUES(value [,value...])；
```
- 注意：
  - 该语句一次只能插入一行到表中
  - 插入新的一行，每列都包含值
  - 按照表中列的顺序依次写值
  - 在insert语句中写出列名是可选的
  - 日期、字符记得用单引号
- 插入含空值的行
  - 隐式：只写出某些要插入的列，其他为空
```
  INSERT INTO EMP(empno,ename） VALUES(7902,'ELINA');
```
  - 显示：显示指定NULL关键字
```
  INSERT INTO EMP(empno,ename，hiredate) VALUES(7902,'ELINA',NULL);
```

修改行

  UPDATE TABLE SET COLUME_NAME=VALUE [,COLUME2=VALUE2] [WHERE CONDITIONS];

删除行

  DELETE FROM TABLE [WHERE CONDITIONS];

8、典型事务操作语句

数据库一系列操作的组成的一个单元，要么都完成，要么都取消，保证数据的一致性

Oracle事务的开始总是隐式的，结束可以用COMMIT/ROLLBACK

事务开始于第一个DML SQL语句执行时，结束于
- COMMIT/ROLLBACK
- DDL或DCL（隐式提交）
  - DDL：数据定义语言，创建表、修改表、删除表等等
  - DCL：数据控制语言，GRANT
- 用户推出iSQL*PLUS（默认提交）
- 系统崩溃（隐式it交）
控制事务常用命令
- COMMIT;
  - COMMIT前
    - DML首先影响数据库缓存区，还可以恢复
    - 当前用户看得到之前的操作
    - 其他用户看不到之前的操作
    - 其他用户不能改变受影响的行
  - COMMIT后
    - 数据持久化，永久改变
    - 所有用户都可以看到事务的结果
    - 受影响的行的锁被释放，其他用户可以进行操作
    - 所有保存点（SAVEPOINT）都会被清除
- ROLLBACK;
  - 用户发出撤销命令
  - 系统崩溃，自动回滚
- SAVEPOINT mysp;
  - 在事务过程中建立标记点，并且允许用户只回滚到标记点以前的状态
- ROLLBACK TO SAVEPOINT mysp;

9、SQL的简单优化

避免使用*
- 解析过程中，首先要将*转换为该表的所有列，再通过查询数据字典完成，意味着耗费更多时间
- 最好显式地指定想要查询的列名
多表查询时，用别名，并且使用表的别名前缀于每个列
- 清晰
- 避免相同列名时产生的错误
WHERE子句中的连接顺序
- 先计算右边的表达式，然后再计算左边的
- 所以，把那些能缩小范围更打的表达式放在最后面
  
  SELECT * FROM emp e,dept d WHERE d.deptno>10 AND e.deptno=30;
避免使用NOT命令
- 通过使用>=,<=等，避免使用NOT命令
```
  SELECT * FROM EMP WHERE SALARY<>3000;

  SELECT * FROM EMP WHERE SALARY<3000 OR SALARY>3000;
```
  第二种查询允许oracle对SALARY列使用索引，而第一种查询则不能使用索引
使用“>=”代替“>”
- 高效：SELECT * FROM EMP WHERE DEPTNO >=4;
  
  首先会查到4，4已经是符合的结果之一
- 低效：SELECT * FROM EMP WHERE DEPTNO >3;
  
  首先会查到3，3不是结果
用TRUNCATE代替DELETE
- DELETE：在事务中，可以被回滚，但是如果没有COMMIT操作，回滚段中会有删除操作恢复的信息
- TRUNCATE：回滚段不再存放任何可被恢复的信息，更少资源调用，执行时间缩短
- 注意：一定要确认数据是需要删除的
尽量多使用COMMIT
- 减小事务的长度，事务是消耗资源的，庞大的事务容易引起死锁
- COMMIT所释放的资源
  - 回滚段上用户恢复数据的信息
  - 被程序语句获得的锁
  - Redo log buffer的空间
  - oracle为管理上述3种资源所花费的资源
避免在索引列上使用函数
- 低效：SELECT * FROM deptWHERE sal*2>25000；
  
  本来可以直接搜索sal的索引，但是现在在索引列上有函数，优化器将不使用索引而全表搜索
- 高效：SELECT * FROM dept WHERE sal>25000/2；
  
  可以直接搜索sal的索引，所以更高效

test

[Y]Oracle 数据库分区表上的索引可以是分区索引，也可以是非分区索引。

 当用户编译一条 SQL 语句时，进程会访问 Oracle 内存中的什么模块？B

   A  重做日志缓冲区
   B  共享池
   C  数据库缓冲区高速缓存
   D  大型池

使用 RMAN 对数据库执行恢复时，数据库应该处于什么状态？B

   A  idle 状态
   B  mount 状态
   C  open 状态
   D  nomount 状态

如果需要查询数据库中的表数据，数库需要处在什么状态？D

   A  nomount 状态
   B  mount 状态
   C  idle 状态
   D  open 状态
   
当 Oracle 内存管理模式为 AMM（Auto Memory Management）时，下列哪些内存模块可以根据工作量自动调整大小？ BC

   A  Log buffer
   B  Large pool
   C  Shared pool
   D  Keep buffer pool

nomount:读参数文件，启动实例和后台进程
mount:打开控制文件
open:数据库打开

SGA_TARGET 指定了SGA可以使用的最大内存大小，而SGA中各个内存的大小由Oracle自行控制，不需要人为指定。Oracle 可以随时调节各个区域的大小，使之达到系统性能最佳状态的个最合理大小，并且控制他们之和在SGA_TARGET 指定的值之内。一旦给SGA_TARGET指定值后（默认为0，即没有启动ASMM），就自动启动了ASMM特性。如果不设置SGA_TARGET，则自动共享内存管理功能被禁止。
设置了SGA_TARGET后，以下的SGA内存区就可以由ASMM来自动调整： ASMM只能自动调整5个内存池的大小，它们是：shared pool、buffer cache、large pool、java pool和stream pool。我们不再需要设置shared_pool_size、db_cache_size、large_pool_size、 java_pool_size、streams_pool_size这五个初始化参数。而其他的内存池，比如log buffer、keep buffer cache等仍然需要DBA手工进行调整。