数据库基础原理
本文包括：
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)：返回当前月的最后一天
- SYSDATE：返回当前系统的日期
-
转换函数（日期，数字，字符三者的转换，字符作为中间桥梁）
- 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
- TO_NUMBER
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 CONSTRAINTSRESTRICT：删除表是有限制的。 欲删除的基本表不能被其他表的约束所引用 如果存在依赖该表的对象，则此表不能被删除
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）都会被清除
- COMMIT前
- ROLLBACK;
- 用户发出撤销命令
- 系统崩溃，自动回滚
- SAVEPOINT mysp;
- 在事务过程中建立标记点，并且允许用户只回滚到标记点以前的状态
- ROLLBACK TO SAVEPOINT mysp;
- COMMIT;
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手工进行调整。
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
