第二章 SQL查询和SQL函数.docx
《第二章 SQL查询和SQL函数.docx》由会员分享，可在线阅读，更多相关《第二章 SQL查询和SQL函数.docx（13页珍藏版）》请在冰豆网上搜索。
第二章SQL查询和SQL函数
第2章SQL查询和SQL函数
回顾
❑Oracle服务器由Oracle数据库和Oracle实例组成
❑Oracle实例由系统全局区内存结构和用于管理数据库的后台进程组成
❑Oracle中用于访问数据库的主要查询工具有SQL*Plus、iSQL*Plus和PL/SQL
❑Oracle企业管理器是用于管理、诊断和调整多个数据库的工具
❑Oracle中的SYSTEM用户和SYS用户具有管理权限，而SCOTT用户只有基本的权限
目标
❑了解Oracle数据类型
❑了解数据定义语言和数据操纵语言
❑了解事务控制语言和数据控制语言
❑掌握SQL操作符和SQL函数
Oracle数据类型
数据类型
说明
char
字符型，最大长度2000字节
nchar
基于NLS字符型，最大长度是2000字符
varchar2
变长的字符数据类型，最大长度2000字节
nvarchar2
变长的NLS字符数据类型，最大长度是2000字符
varchar
number（p，s）
数值类型。
p表示总位数，s表示小数位。
其中小数点不算位数。
date
时间类型
long
变长的字符类型，但不支持检索，最大支持2GB
raw
二进制数据，2000B
longraw
2GB，但不支持检索
blob
4GB
clob
4GB
nclob
4GB
bfile
保存二进制文件，数据是保存在单独的文件中，而非数据库中。
NLS：
国家语言
varchar2：
是oracle中推荐的字符类型，它是向前向后兼容。
varchar类型就可能会因为SQL改进而被过时。
number：
小数点后的位数大于精度则进位计算，若进位后的总位数大于p，则报错。
不谈小数部分，若整数部分加小数大于总位数就直接报错。
char与nchar的比较
--创建一张测试表
createtablet1
（
namechar（10）,
sexchar
（1）
）
--插入正常数据
insertintot1values（'tom','m'）;
insertintot1values（N'tom','m'）;
insertintot1values（'tomtomtomtom','m'）;
insertintot1values（'你好你好你好','m'）;
--创建一张测试表
createtablet2
（
namenchar（10）,
sexnchar
（1）
）
--插入数据
insertintot2values（'tomtomtom你好','m'）;
此时把字母的长度也改成占2个字节即1个字符
number类型：
--创建一张测试表
createtablet2
（
namenchar（10）,
sexnchar
（1）,
gradenumber（4,2）
）
insertintot2values（'你好','m',99.899）;
其中小数点后的位数已经超过精度了，但是不报错
如果有进位的话：
insertintot2values（'你好','m',99.999）;
若换一个没有整数进位的，正常：
insertintot2values（'你好','m',98.999）;
注意插入时间的格式
insertintot4values（'张三','9-9月-8'）
时间格式
selectsysdatefromdual;
altersessionsetnls_date_format=’yyyy-mm-dd’;
SQL语言回顾
DDL数据定义语言自动提交事务
用于创建、删除数据库的对象和维护数据对象的属性
createtable
altertable
droptable
truncatetable若表中存在外键则不能使用，效率必delete高
DML数据操作语言
select
使用别名
带条件的查询where
精确查询
=
模糊查询
between。
。
。
and。
。
。
like
in
关联查询
内连接
左连接
右连接
以join后面的表为参考表
注意加号的位置
交叉连接
返回的记录数是两表的记录数相乘
delete
update
insert
DCL数据控制语句
授权grant
收回权限revoke
TCL事务控制语句
commit提交数据
rollback回滚
savepoint设置保存点，rollbacktosavepoint
oracle函数
单行函数
字符函数
大小写转换函数
lower（）;
upper（）;
initcap（）;
字符处理函数
concat（）
substr（）
日期函数
缺省的格式是日-月–年
用sysdate表示系统时间
可以直接使用算术运算
日期之间可以相减，相加没意义
可以和数值加减（单位是天）
round（）四舍五入
trunc（）舍去
next_day（sysdate,’星期一’）计算这个日期以后的下个星期一是哪天
为什么错？
函数的参数有问题！
算数函数
abs（）
round（）
trunc（）
转换函数
to_char（）
to_number（）
to_date（）
通用函数
NVL（列名或常量，‘替换以后的值’）函数将列名或常量中NULL替换相应的数据
NVL2（exp1，exp2，exp3）
如果exp1的值不为null，就返回exp2的值，否则就返回exp3的值
nullif（exp1，exp2）
如果两个表达式相等，就返回null，否则返回exp1的值
case表达式
语法：
case表达式when判断值then结果
when判断值then结果
when判断值then结果
esle
endas别名
注意表达式的位置
decode（）函数
decode（表达式，条件1，值1，
条件2，值2，
其余的值）
多行函数
分组函数
对一组数据进行计算
avg
count
max
min
sum
分组函数会忽略空值的行
selectavg（nvl（comm，0））fromemp；
Groupby
说明：
出现在select列表中的字段，不是出现在分组函数中，就必须出现在groupby子句中
groupby子句中的子句不必出现在select列表中
常见错误
select在没有groupby后不能又跟单行函数，还跟多行函数
不允许在where中使用分组函数，因为先执行where，还不知道groupby是怎么分组的。
此时可以使用having
分组函数最多只能嵌套2层
分析函数
SELECTe.rowid,row_number（）over（orderbysal）,rank（）over（orderbysal）,dense_rank（）over（orderbysal）,sal,d.dname,e.ename,e.sal,e.deptno,DENSE_RANK（）
OVER（PARTITIONBYe.deptnoORDERBYe.salDESC）
ASDENRANK
FROMempe,deptdWHEREe.deptno=d.deptno;……………………..
升级会员 