oracle 数据库学习指导（1）

数据库设计范式


示例：设计ER图，包含如下信息（学号，姓名，课程，成绩，院系，班级）

数据库中的关系必须满足一定的要求，即满足不同的范式
目前一共有6种范式，分别为第一范式、第二范式。。。、第六范式
数据库设计最低要求必须满足第一范式。
第二范式要在满足第一范式的基础上，满足更多的要求，以此类推。

第一范式：无重复列或属性
表的每一列都是不可分割的数据项，同一列中不能有多个值
如出现重复属性，则定义新的属性或实体

电话号码：[phone]
拆分为两个属性：座机号和手机号




第二范式：完全依赖主键
所有非主键字段完全依赖于所有主键。

成绩表： 学号（主） 课程号（主）  成绩   学生姓名

第三范式：不依赖与其他非主键字段
一个表中不能包含其他表中已包含的非主键字段信息
数据冗余问题



数据库表的操作
创建表  create table tableName (columnName type...)
删除表  drop table tableName
修改表  
1. 添加列  alter table tableName add columnName type;
2. 修改列  alter table tableName modify columnName type;
3. 删除列  alter table tableName drop column columnName;
4. 重命名表  rename oldTableName to newTableName;
查看表结构  desc tableName;




查询语句
select语法：select columnNames from tableName;

算术运算符：+ - * /
使用方式：select columnName 运算符 数字(或columnName) from tableName;

连接运算符：||
使用方式：select columnName || '字符串'(或columnName) from tableName;

特殊值：空值  不是空字符串、不是空格、不是0
算术表达式中出现空值，则表达式结果为空
连接表达式中出现空值，会被当做一个空字符串处理




where子句
当要检索满足特定条件时，使用where子句
语法： 
select columnNames 
from tableName 
where conditions;

比较运算符
= > >= < <= !=(<>)


逻辑运算符
and  当所有的条件都为真时，and运算符结果为真
or   当有任意一个条件为真时，or运算符结果为真
not  取反


其他运算符
between m and n  返回指定范围 m与n之间 的记录，并且包含边界值m与n
等同于 >= m and <= n
in  返回的记录与列表指定的值匹配
is null  返回满足指定列为空的记录
like  返回满足匹配结果的记录
可以和匹配模式一起使用的特殊字符：
1. 通配符 %，可以和任意长度字符串匹配
以字母S开头的单词 S%
以字母h结尾的单词 %h
中间包含abc的单词 %abc%

2. 定位标记符 下划线(_)，表示精确的和任意单个字符匹配 
第二个字母为s的单词  _s%
字母a与b之间隔开2个字母的单词  %a__b%



显示非重复行
distinct子句 用于显示一列中唯一的值
语法：select distinct column from tableName




以指定顺序检索数据
order by子句
语法：select columnNames 
from tablename
order by columnName [asc|desc] , columnName [asc|desc]...
asc  是升序显示数据，默认
desc 是降序显示数据




Oracle数据类型
1. char(size)  固定长度的字符数据，最大长度2000字节
例如：name char(20 byte)，表示name字段类型是char，最大长度为20个字节
如果内容为'abc'，其长度为3
一个英文字母或数字为1个字节
一个中文或全角字符为2个字节
（因为Oracle环境中，默认字符编码是GBK，如果是UTF-8编码，中文或全角则占3个字节）

固定长度：如果字段内容长度不满最大长度，底层数据默认用空格补满最大长度

2. varchar2 长度可变的字符数据，最大长度4000字节
Oracle数据库特有的数据类型
长度可变：如果字段内容长度不满最大长度，不做任何处理。


3. varchar 同varchar2

4. number(m,n)  数值类型
m：表示数据的总长度 取值范围：1~38
n：小数位数 取值范围：-84~127，默认是0
m-n：整数位的长度
例如：age number(3,0)  age的取值范围是：0~999
money number(5,2)  小数位数是2位，整数位数3，0.01~999.99

5. date 日期类型
数据库底层实际记录的是长整数型毫秒数
日期的范围：公元前4712年1月1日 到 公元后4712年12月31日


6. long 长度可变的字符数据，上限2GB，不支持内容搜索

7. blob 二进制大对象类型，上限4GB
clob 字符大对象类型，上限4GB
 

*ROWID 十六进制字符串，表示行的唯一地址




函数
函数分为单行函数和多行函数。

单行：作用在单个数据上的函数
多行：作用在多个数据上的函数

函数的特点：
1. 接受参数并返回结果
2. 对每一行数据都起作用
3. 可嵌套使用

函数共分为：字符、数值、日期和转换函数。

字符函数：
1. lower:以小写方式显示字符串
lower(columnName)  lower(string)

2. upper:以大写方式显示字符串
upper(columnName)  upper(string)

3. initcap:以大写方式显示首字母
initcap(columnName)  initcap(string)

4. concat:将两个字符串并置（连接）起来
concat(columnName1, columnName2)

5. substr:获取子字符串
substr(string, begin, length)
string 被截取的源字符串
begin 从1开始时
length 截取长度，超过源字符串长度 则取到结束

6. length:获取字符串长度
length(string)

7. instr: 返回指定字符在目标字符串中的位置
instr(string, str)
返回值位置从1开始，如果不存在则返回0

8. trim: 去掉字符串的前后导空格
trim(string)

ltrim：去掉字符串的前导空格 ltrim(string)
rtrim：去掉字符串的后导空格 rtrim(string)

9. replace: 将源字符串中指定的字符串替换为新的字符串
replace(targetStr, oldstr, newstr)




数值函数
1. abs(num)  取绝对值
2. round(num)  四舍五入
3. trunc(num, len)  保留小数len位
4. ceil(num)  向上取整
5. floor(num)  向下取整
6. sign(num)  判断正负
7. sin(num)
8. cos(num)
9. tan(num)
10. power(n, m)  n的m次方
11. sqrt(num)  开平方根
12. mod(n, m)  n % m




日期函数
默认的格式：dd-mon-yy  02-2月-88
获取当前系统时间：sysdate

日期的算术计算：
1) 日期对象可加或减一个数值，表示加或减 天数
2) 日期对象之间可相减，表示 二者相差的 天数

1. add_months(date, monthNumber)：在指定日期上增加指定月数

2. last_day(date)：返回指定日期当月最后一天

3. months_between(date1, date2)：返回两个日期之间的月份差

4. next_day(date, dayName)：返回指定日期date之后的第一个星期几 所在日期对象


转换函数
数值类型 <——> 字符串类型 <——> 日期类型

转换方式：隐式转换和显式转换
1. 隐式转换：在Oracle里，数据类型根据select语句中函数的要求自动进行转换。

2. 显示转换   to_char(), to_date(), to_number()
1)日期格式化
1.1) 日期类型 ——> 字符串
格式化函数：to_char(date [, 'format'])
格式：yyyy 表示4位的年  yy 2位的年
mm 表示2位的月  mon 月份的全称，中文是'2月'，英文是'Feb'
dd 表示2位的天
hh 表示12小时制的小时  hh24 表示24小时制的小时
mi 表示分
ss 表示秒
特殊符号：- : /




1.2) 字符串 ——> 日期类型
转换函数：to_date(string, 'format')
to_date('2011-11-11', 'yyyy-mm-dd')


2) 数值格式化
2.1) 数值类型 ——> 字符串 （数字的格式化）
格式化函数：to_char(num, 'format')
格式： 9 表示一个数字
0 表示一个数字，如果该位置没有数字，则输出0
$ 美元符
L 本地化货币符
. 小数点
, 千位符
to_char(1234.56, '999999.99999')

2.2) 字符串 ——> 数值类型
格式化函数：to_number(string, 'format')
to_number('00123.234', '99999.9999')


通用函数
适用于任何数据类型的函数（包括空值）
1. nvl(exp1, exp2) 当exp1为空时，函数返回exp2，否则返回exp1
将空置null替换为指定的缺省值

2. nvl2(exp1, exp2, exp3) 条件表达式，当exp1不为null时，函数返回exp2，否则返回exp3

3. nullif(exp1, exp2)
当exp1与exp2相等的时候，函数返回null，否则返回exp1

4. case表达式，实现多路分支
case exp when comp1 then return1
when comp2 then return2
.
.
.
else returnDefault
end
注：when后面的表达式 只能是：char、varchar、number、date

5. decode(exp, exp1, result1, exp2, result2, ...., expn, result n, default)

6. 条件表达式
if boolean_exp 
then result1 
else 
result2



多行函数
对一组数据进行运算，返回一个结果

avg   平均值   对数值类型运行
count 获取查询返回的记录数
select count(*) from table; 用于获取一个表的总记录数

sum   获取某一字段值的总和 sum(columnName)
max   获取某一字段中最大值
min   获取某一字段中最小值


avg、count(columnName)、sum、max、min 函数会自动过滤无效值null

分组

之前使用多行函数时，默认将整个表作为一组

可以group by子句将表中数据分为若干个组

语法：
  select columnList
  from tableName
  where where_condition
  group by column
  order by