Oracle11g基础学习---------（2）SQL语言基础

 

学习Oracle从哪里入手呢，在这里，我将带你走进Oracle的大门，一天一章，7天入门不是梦  
1. SQL语言概述
1.1  SQL语言分类
数据定义（Data Definition Language，DDL）
用于创建、修改和删除数据库对象，如CREATE TABLE、ALTER TABLE、DROP TABLE等。DDL语句会自动提交事务 
数据操纵语言（Data Manipulation Language，DML）
用于操纵数据库，包括INSERT、UPDATE、DELETE、SELECT等
数据控制语言（Data Control Language，DCL）
用于执行授予权限和撤销权限的操作，包括GRANT（授予权限）、REVOKE（撤销权限）两条命令。DCL语句会自动提交事务  
事务控制语言（Transactional Control Language，TCL）
用于维护数据的一致性，包括COMMIT（提交事务）、ROLLBACK（回滚事务）和SAVEPOINT（设置保存点）3条语句
1.2 SQL语句编写规则
SQL关键字不区分大小写，既可以使用大写格式，又可使用小写格式，或者大小写混用 
对象名与列名不区分大小写 
字符值与日期值区分大小写 
在SQL*Plus中，每条SQL语句要以分号结束。如果语句比较长，可以将语句文本分布到多行上，最后以分号结束 

2. 使用DDL语句
2.1 Oracle 11g常用的数据类型
字符类型
CHAR类型
CHAR表示固定长度字符串，长度不够的用空格补充，最多可以存储2000字节
CHAR类型区分中英文，中文在CHAR中占两个字节，而英文只占一个字节
VARCHAR2类型
VARCHAR2表示可变长度字符串，最多可以存储4000字节
在定义该数据类型时，应该指定其大小。与CHAR类型相比，使用VARCHAR2可以节省磁盘空间
数值类型
NUMBER类型可以存储正数、负数、零、定点数和精度为38位的浮点数 
NUMBER(M,N)。其中，M表示精度，代表数字的总位数；N表示小数点右边数字的位数 
日期类型
DATE类型
DATE数据类型用于存储表中的日期和时间数据，取值范围是公元前4712年1月1日至公元9999年12月31日
DATE类型的长度是7，7个字节分别表示世纪、年、月、日、时、分和秒
TIMESTAMP类型
TIMESTAMP数据类型用于存储日期的年、月、日以及时间的小时、分和秒值
其中，秒值精确到小数点后6位，该数据类型同时包含时区信息
2.2 CREATE TABLE命令
CREATE TABLE用于创建表。在创建表时，经常会创建该表的主键、外键、唯一约束、Check约束等
2.3  ALTER TABLE 命令
向已经创建的表中增加一个新列 
alter table tableName add columnName dataType
修改表中指定列的数据类型和类型长度 
alter table tableName modify column columnName dataType
删除表中指定的列 
alter table tableName drop columnName
2.4 TRUNCATE TABLE命令
RUNCATE TABLE命令用于删除表的所有内容，并释放表使用的存储空间，但不删除表结构
语法
truncate table tableName
TRUNCATE TABLE命令可以快速删除表的记录并释放空间，不使用事务处理，速度快且效率高，但无法回滚事务
DELETE命令可以在执行删除之后通过ROLLBACK撤销删除
2.5 DROP TABLE 命令
DROP TABLE命令用于从数据库中删除表及全部数据
3. 使用DML语句
3.2 UPDATE命令 
3.3 DELETE 命令
DELETE命令用于删除表的内容
4. 使用SELECT语句
4.1  示例数据库概述
安装Oracle 11g创建数据库的环节中，如果在“指定数据库配置详细资料”窗口的“示例方案”选项卡中选择了“创建带样本方案的数据库”复选框，就会安装新的用户示例数据库 
Oracle的示例方案都基于一个虚拟的通过各种渠道销售物质的公司 
其中，HR方案是基本的关系数据库方案，用于介绍最简单和最基本的话题 
该方案中包含7张表：雇员（employees）、部门（departments）、地点（locations）、国家（countries）、地区（regions）、岗位（jobs）和工作履历（job_history）
4.2 HR示例方案简介
默认情况下，所有示例方案的账户都已被锁定，它们的密码是过期的，不能连接到数据库
解除HR用户的锁定状态
4.3 HR示例方案中的表结构
4.4 子查询
4.5 
在DDL、DML语句中使用子查询
在CREATE TABLE语句中使用子查询 
在INSERT语句中使用子查询 
在DELETE语句中使用子查询
在UPDATE语句中使用子查询 
5. 使用DCL数据控制语言
5.1  GRANT
如果用户在自己的模式中创建了一张表，则该用户即为此表的所有者，拥有对这张表的所有操作权限 
除非该用户希望与其他用户共享这张表，否则无须为其他用户赋予该表的任何操作权限 
可以通过GRANT命令为其他用户授予该表的某些操作权限，这样的权限被称为对象权限 
语法
GRANT privileges ON object-name TO userName
5.2 REVOKE
REVOKE命令用于撤销已经授予的用户对象权限 
语法
REVOKE privileges ON object-name FROM userName
6. 使用TCL事务控制语言
事物的开始和结束
事务开始
连接到数据库，并开始执行一条DML（UPDATE、INSERT、DELETE）语句
前一个事务结束或者执行一条会自动提交事务的语句
事务结束
执行一条COMMIT（事务提交）或ROLBACK（事务回滚）语句
执行一条会自动提交事务的语句
执行一条DML语句却失败了
自动提交事务
执行一条DDL语句（如CREATE TABLE、ALTER TABLE等）
执行一条DCL语句（如GRANT、REVOKE等）
断开与数据库的连接。例如，在SQL*PLUS中执行一条DISCONNECT命令，或在同一个SQL*PLUS中执行一条CONNECT命令启动一个会话，或者执行EXIT命令退出SQL*PLUS等
6.2 使用COMMIT命令提交事务
会话A向emp表中插入一条记录，在提交之前，另一个会话B无法查看到新的记录。
只有在会话A提交事务之后，会话B才可以查看新增加的记录，这实际上就是事务的隔离性
6.3 使用ROLLBACK回滚事务
6.4 回滚部分事务
7. 使用基本函数
dual表
dual表是Oracle数据库中的一个虚表，它有一行一列，所有者是SYS用户，但可以被数据库中的所有用户使用
不能向该表插入数据，但可以使用该表来选择系统变量，或者求一个表达式的值
单行函数
日期函数
ADD_MONTHS函数：用于返回指定的日期加上指定的月数后的日期值
MONTHS_BETWEEN函数：用于返回两个日期之间的月份数
LAST_DAY函数：用于返回指定日期对应月份的最后一天
NEXT_DAY函数：用于返回指定下一个星期几的日期
EXTRACT函数：用于提取日期中的特定部分
字符函数
函数名  功能  示例  输出结果 
LOWER（c）  小写转换  select lower('SccE') 
from dual;  scce 
UPPER(c)  大写转换  select upper('scce') 
from dual;  SCCE 
LTRIM（c1 [,c2]）  去掉c1左边所包含的c2中的任何字符。当遇到不是c2中的字符时结束，并返回剩余字符串。若c2省略，则默认为空格  select ltrim('adminscce','ad') from dual;  minscce 
RTRIM（c1 [,c2]）  去掉c1右边所包含的c2中的任何字符。当遇到不是c2中的字符时结束，并返回剩余字符串。若c2省略，则默认为空格  select rtrim('adminscce','scce') from dual; admin 
函数名  功能  示例  输出结果 
REPLACE(c1,c2 [,c3]) 将c1中出现的c2都替换成c3，并返回剩余的字符串。c3默认为null。如果c3为null，则所有出现c2的地方的字符都被删除；如果c2为null，则返回c1；如果c1为null，则返回null select replace('a*b*','*') from dual; ab
INSTR(c1,c2) 在c1中搜索c2首次出现的位置，如果找不到则返回零 select instr('abcdbc','bc') from dual; 2
SUBSTR(c,m [,n]) 返回c的子串，其中m是子串的开始位置，n是子串的长度。如果省略n，则表示截取从m位置开始的所有子串 select Aubstr('scce',2,2) from dual; cc
CONCAT(c1,c2) 连接字符串 select concat('scc','e') from dual; scce
LENGTH(c) 返回字符串长度 select length('scce') from dual; 4
数学函数
函数名  功能   示例      结果 
ABS  取绝对值  select abs(-1) from dual;   1 
CEIL  向上取整  select ceil(1.33) from dual;   2 
FLOOR  向下取整  select floor(1.33) from dual;   1 
POWER  求幂   select power(2,3) from dual;   8 
MOD  取余   select mod(10,3) from dual;   1 
ROUND  四舍五入  select round(12.126,2) from dual;  12.13 
TRUNC  截断   select trunc(12.126,2) from dual;  12,12 
转换函数
TO_CHAR：用于将日期或数字以指定的格式转换为VARCHAR2数据类型的值
语法
TO_CHAR(d|n [,fmt])
d表示日期，n表示数字，fmt是指定日期或数字的格式
转换函数 
TO_NUMBER：将包含数字的字符串转换为NUMBER数据类型，从而可以对该数据类型执行算术运算。
语法
TO_NUMBER(char)
通常不需要进行转换，因为Oracle可以对数字字符串进行隐式转换
其他函数
NVL：用于将空值替换为指定值
语法
NVL(expr1,expr2)
如果expr1不是NULL，则返回expr1；否则返回expr2
向HR方案中的employees表中添加一个关于奖金的字段bonus，该字段充许为空。现要求计算雇员的实发工资，实发工资=基本工资+奖金
其他函数
NVL2：与NVL类似，只是参数有所不同
语法
NVL2(expr1,expr2,expr3)
如果expr1不是NULL，则返回expr2的值；否则返回expr3的值
其他函数 
DECODE：DECODE函数相当于一条件语句（IF），它将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值
语法
DECODE(input_value,value,result[,value,result…][,default_result]);
input_value表示试图处理的数值。DECODE函数将该数值与一系列的序偶相比较，决定最后的返回结果
value表示一组成序偶的数值。如果输入数值与之匹配成功，相应的结果将被返回
result表示一组成序偶的结果值
default_result表示未能与任何一序偶匹配成功时函数返回的默认值
为employees表中的雇员按表需求增加工资，并确认更新结果.
 总结
VARCHAR2类型为Oracle常用的可变长度字符类型
CLOB数据类型用于存储可变长度的字符数据，BLOB数据类型用于存储较大的二进制对象
Oracle中的子查询可以分为单行子查询、多行子查询、多列子查询、相关子查询等
GRANT命令用于授予对象权限，REVOKE命令用于撤销权限
COMMIT命令用于提交事务，ROLLBACK命令用于回滚事务
TO_CHAR函数用于将日期或数字以指定的格式转换为VARCHAR2数据类型的值
DECODE函数相当于条件语句（IF），它将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值

表空间是数据库中最大的逻辑单位，一个 Oracle 数据库至少包含一个表空间，就是名为SYSTEM的系统表空间。
每个表空间是由一个或多个数据文件组成的，一个数据文件只能与一个表空间相关联。
表空间的大小等于构成该表空间的所有数据文件大小之和。
创建表空间的语法是：
CREATE TABLESPACE tablespacename
DATAFILE ‘filename’ [SIZE integer [K|M]] 
[AUTOEXTEND [OFF|ON]];
要连接到Oracle数据库，就需要创建一个用户帐户
每个用户都有一个默认表空间和一个临时表空间
CREATE USER命令用于创建新用户
CREATE USER 命令的语法是：
CREATE USER MARTIN
IDENTIFIED BY martinpwd
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;
创建一个名称为 martin 的用户，其密码为 
martinpwd
GRANT 命令可用于为用户分配权限或角色 
GRANT CONNECT TO MARTIN; CONNECT角色允许用户连接至数据库，并创建数据库对象
GRANT RESOURCE TO MARTIN; RESOURCE角色允许用户使用数据库中的存储空间
如果上机的时候，发现Scott用户的密码被其他同学给修改了，应该如何处理？
解决方案
1）使用匿名登陆Oracle服务器
sqlplus / nolog
conn / as sysdba
2）修改scott用户的密码
Alter user scott identified by tiger;