第2章sql查询sql函数与数据库对象.docx
《第2章sql查询sql函数与数据库对象.docx》由会员分享,可在线阅读,更多相关《第2章sql查询sql函数与数据库对象.docx(25页珍藏版)》请在冰点文库上搜索。
第2章sql查询sql函数与数据库对象
第二章sql查询、sql函数与数据库对象
目标:
1、了解oracle的数据类型。
2、了解数据定义语言和数据操纵语言。
3、了解事务控制语言和数据控制语言。
4、掌握sql操作符和sql函数的用法。
5、了解理解和使用同义词。
6、理解和使用序列。
一、oracle 的数据类型。
1、字符型。
(1)char类型
是一种固定长度的数据类型,长度可以为1-2000字节。
在为字段定义该数据类型时,可以不指定字段长度,那么默认长度就是1个字节。
假设用户给某个字段定义成char类型,长度50字节。
如果用户输入该类型字段的值时,其长度小于2000字节,则用空格填充至固定长度50字节。
如果大于指定长度,就出错。
(2)varchar2类型。
varchar2类型。
是可变长度的字符串,该类型的长度可以为1-4000个字节范围。
在定义该数据类型时,必须指定其大小。
如果某字段定义成varchar2类型,长度50字节,即使将来实际输入字段的值只有3个字节,也不用空格填充,与char相比,节省存储空间。
(3)long数据类型。
可变长度数据类型。
最多能存储2G字节。
一般不使用,如果要使用,请注意:
一个表中只有一个字段可以为long数据类型;long字段不能定义为唯一约束或主键约束;long列上不能建索引。
2、数字型。
number类型。
这种类型看上去只有一种,但使用非常灵活,所以能表达正数、负数、零、小数等。
使用格式为number(p,s),其中p表示数字的总位数,小数点也占一位,s表示数字的小数位数。
说明:
number(10,2)//表示整个数字长度为10位(含小数点),小数部分占2位。
number(10)//表示整个数字长度为10位,且是一个整数。
number(10,-1)//表示整个数字长度为10位,等价于number(10)。
number(10,-2)//表示整个数字长度为10位,精确到百分位。
比如:
如果某个字段定义成number(10,2),该字段取值如下。
12345678.90保存在该字段中的值为12345678.9
1234.567890保存在该字段中的值为1234.57
如果某个字段定义成number(10),该字段取值如下。
1234567890保存在该字段中的值为1234567890
1234.567890保存在该字段中的值为1235
如果某个字段定义成number(10,-2),该字段取值如下。
1234567890保存在该字段中的值为1234567900
1234567846保存在该字段中的值为1234567800
1234.567890保存在该字段中的值为1200
3、日期型。
日期数据类型用于存储日期值和时间值。
(1)date数据类型。
用于存储表中的日期和时间数据。
Oracle使用自己的格式存储日期,使用7个字节固定长度,每个字节分别存储世纪、年、月、日、小时、分钟、秒。
建表时定义字段的日期类型,只有date类型可选。
(2)timestamp数据类型。
该数据类型用于存储日期的年、月、日、小时、分、秒,其中秒值精确到小数点后6位,该数据类型同时包含时区信息。
使用systimestamp函数可以获取系统当前日期、时间和时区。
4、二进制类型。
raw和longraw数据类型用于存储二进制数据。
5、LOB数据类型。
该类型用于存储多达4G的非结构化信息,比如声音剪辑、视频剪辑等。
其中,CLOB用于存储大量的字符数据,BLOB用于存储较大的二进制对象,如图形、声音、视频等。
二、oracle中的伪列与伪表。
伪列就像oracle中某个表中的某个字段,但该列实际上并未存储在表中。
伪列可以从表中查询,但不能插入、更新或删除它们的值。
(1)rowid伪列。
数据库中的每一行都有一个行地址,rowid伪列就保存了该行地址信息。
可以使用rowid值来定位表中的一行。
通常,rowid的值可以唯一的标识表中的一行。
伪列的作用是使系统能以最快的方式访问表中的某一行。
对比如下两条语句。
(2)rownum伪列。
对于查询返回的每一行,rownum伪列返回一个数值代表行的次序,返回的第一行的rownum值为1,第二行的rownum的值为2,依此类推。
通过rownum伪列,用户可以限制查询返回的行数。
(sqlserver中限制返回行数是用topn来表示,还记得么?
)比如。
(3)dual伪表。
这是一个系统中不存在的表,按语法格式要求表,但又不需要具体表名的地方使用。
三、基本的sql语句。
1、创建表命令(createtable)
格式:
createtable[schema.]table(columndatatype,columndatatype,……);
如:
2、修改表命令(altertable)
更改某个字段的定义,格式如下。
向表中添加新的字段,格式如下。
删除表中现有字段,格式如下。
3、删除表命令(droptable)
格式如下。
4、select命令。
Select语句及其包含的子句如orderby等的用法,与sqlserver完全一致。
5、根据现有表创建新表。
6、通过as使用别名。
比如:
下面两句都对。
你看出它们的区别了么?
第二句别名可以使用特殊字符如空格等。
7、insert命令。
格式与sqlserver完全一致。
注意的是,插入记录中日期的值,要通过单引号将其引起来。
如日期为2005年12月6日,就要写成‘06-12月-05’,比如:
也可以插入来自其它表的记录。
Insert命令可以用来复制其它表中的记录。
格式如下。
8、update命令。
与sqlserver完全一样。
9、delete命令。
与sqlserver完全一样。
四、事务控制语句。
1、commit命令。
事务提交命令。
格式:
commit;
2、savepoint命令。
用于设置保存点的命令。
格式如下。
3、rollback命令。
格式:
rollback;
比如:
要理解上述过程,请分别在上述每条语句后加一条查询该表的语句来看看结果的变化。
四、数据控制语句。
1、grant命令。
如果用户创建了某个数据库对象,比如表、视图、序列、同义词等,该用户就具有对这些对象的所有权限。
当该用户需要将操纵这些对象的权限授予其它用户时,就需要使用grant命令授权。
比如,将order表的查询和更新权限授予martin用户,如下。
再如:
只将order表中的某两个字段的更新权限授予martin用户
2、revoke命令。
要撤销已授予用户的权限,可以使用revoke命令。
此命令在格式上与grant非常类似。
比如,将order表的查询和更新权限从martin用户收回。
如下。
五、sql操作符。
1、算术操作符。
指+-×÷。
其优先顺序和用法与sqlserver完全相同。
2、比较操作符。
指=,!
=,<,<=,>,>=。
其用法与sqlserver完全相同。
3、between……and……
用法与sqlserver完全相同。
4、in
用法与sqlserver完全相同。
5、like
用法与sqlserver完全相同。
6、isnull或isnotnull
用于判断某个字段的值是否为空。
用法与sqlserver完全相同。
如:
从master表中找出deldate值为空的所有记录。
7、逻辑操作符。
逻辑操作符指not,and,or。
其优先顺序与用法与sqlserver完全相同。
8、连接操作符号||。
连接操作符用于将两个或多个字符串合并成一个字符串,或将一个字符串与一个数值合并在一起。
如:
sql语句结果
六、sql函数。
1、日期函数。
(1)Add_MONTHS
格式:
ADD_MONTHS(日期,月数)
作用:
给指定的日期加上指定的月数后的日期值。
比如:
(2)MONTHS_BETWEEN
格式:
MONTHS_BETWEEN(日期1,日期2)
作用:
返回两个日期之间的月数。
如果日期1晚于日期2,结果为正数;否则为负数。
如果日期1和日期2均是某月中的同一天或月份的最后一天,则结果始终为整数,否则,oracle将根据一个月31天来计算结果的小数部分,并考虑日期1和日期2之间时间部分的差。
比如:
(3)LAST_DAY
格式:
LAST_DAY(日期)
作用:
返回指定日期当月的最后一天的日期。
比如:
(4)NEXT_DAY
格式:
NEXT_DAY(日期,星期几)
作用:
此函数指定日期的下一个星期几的日期。
比如:
(5)TRUNC
格式:
TRUNC(日期,〔可选参数〕)
可选参数可以是:
year,month,day,如果不写,默认就是参数day。
作用:
对指定的日期按参数进行截取。
如果是参数year,则返回值将原日期的月和日变成1月1日,年份不变;如果参数是month,则返回值将原日期的日变成1,年和月份不变;如果参数是day,则返回原日期。
比如:
(6)EXTRACT函数。
格式:
EXTRACT(fmtfromd)
其中,fmt是要提取的格式,可以是year,month,day,hour,minute,second等。
并且不要放在单引号内。
d表示日期。
作用:
该函数提取日期时间类型中的特定部分。
比如:
2、字符函数。
函数
说明
输入
输出结果
INITCAP(char)
首字母大写
Selectinitcap(‘hello’)fromdual;
Hello
LOWER(char)
转换成小写
Selectlower(‘ABC’)fromdual;
Abc
UPPER(char)
转换成大写
Ltrim(char)
截左边空格
Selectltrim(‘abc’)fromdual;
abc
Rtrim(char)
截右边空格
Selectrtrim(‘abc‘)fromdual;
abc
Replace(char1,char2,char3)
字符串替换
Selectreplace(‘jack’,’j’,’bl’)fromdual;
black
Instr(char,substr,pos)
查找子串位置
Selectinstr(‘abcd’,’d’,1)fromdual;
4
Substr(char,pos,len)
取子字符串
Selectsubstr(‘abcdefg’,3,2)fromdual;
cd
Concat(char1,char2)
连接字符串
Selectconcat(‘hello’,’world’)fromdual;
helloworld
length
求字符串长度
Selectlength(‘france’)fromdual;
6
3、数字函数。
函数
说明
输入
输出
ABS
取绝对值
Selectabs(-15)fromdual;
15
Ceil
向上取整
Selectceil(15.2)fromdual;
16
floor
向下取整
Selectfloor(15.2)fromdual;
15
round(m,n)
四舍五入
Selectround(100.256,2)fromdual;
100.26
Mod(m,n)
取余数
Selectmod(10,3)fromdual;
1
Power(m,n)
M的n次幂
Selectpower(5,2)fromdual;
25
4、类型转换函数。
(1)TO_CHAR
格式:
to_char(要转换的内容,[转换后的格式])
[转换后的格式]这部分可以根据需要决定要不要。
作用:
转换成varchar2类型的字符串
比如:
(2)TO_DATE
格式:
to_date(char,[fmt])
作用:
将char或varchar2数据类型转换为日期数据类型。
格式[fmt]指定字符的形式。
比如:
(3)TO_NUMBER
将数字字符串转换成number数据类型,从而进行数学运算。
通常不要这么做,因为系统会自动进行隐式转换。
比如:
5、其它函数。
(1)空值替换函数。
格式:
NVL(字段或变量,值)
作用:
某个字段或变量的取值如果为null,可用该函数将null值替换成指定的值。
比如:
如果level字段的值是null,则将其替换为0
(2)NVL2函数。
格式:
NVL2(表达式1,表达式2,表达式3)
作用:
如果表达式1的值不是null,则返回表达式2的值,否则,返回表达式3的值。
比如:
如果level字段有值,就取出并显示,否则,就显示maxlevel字段的值。
(3)NULLIF
格式:
NULLIF(表达式1,表达式2)
作用:
如果表达式1和表达式2相等,则返回空值,否则,返回表达式1的值。
比如:
6、聚合函数。
Avg,min,max,sum,count
其用法与sqlserver完全相同。
7、分析函数。
(1)ROW_NUMBER
ROW_NUMBER为有序组中的每一行(查询结果返回的行)返回一个唯一的排序值,序号由orderby子句指定,从1开始。
比如:
从scott.emp表中按工资sal字段排序。
――――――――――――――图1―――――――――――――
――――――――――――――图2―――――――――――――
注意到,图2中第2名与第3名的工资完全相同,但名次不一样,所有员工都是按工资多少参与排序的。
能不能将部门分组,每个部门内的员工按工资多少排序,而部门与部门的排序互不影响呢?
请看下例。
下例只是在上面的语句中增加了partitionbydeptno这个子句,表示按部门号进行分组,同一部门号的员工,按工资排序,不同部门号的员工互不影响。
结果如下图3。
――――――――――――――图3―――――――――――――
(2)DENSE_RANK
计算一个值在一组有序行中的排名,排名是以1开始的连续整数。
具有相同值的行排名相同,并且排名是连续的。
而图3中同一部门的员工,如果工资sal相同,其排名是不同的。
如果同一部门的员工,工资sal相同,其排名也相同,则要用到dense_rank。
比如:
下列语句对于同一部门内工资相同的员工,其排名相同,且部门内所有员工的排名连续。
结果如图4。
――――――――――――――图4―――――――――――――
七、同义词
同义词可以是数据库对象的一个别名,这些对象可以是表、视图、序列、过程、函数、程序包。
通过使用同义词,用户可以访问其它模式的数据库对象而无需指定模式前缀。
比如,用户user1想访问user2的表emp,必须使用user2.emp来引用,如果user1创建了一个名为emp的同义词代表user2.emp,那么,user1就可以用该同义词象访问自己的表一样引用user2.emp表了。
但同义词不能代替权限,在使用同义词之前要确保用户已得到访问对象的权限。
上述user1对user2的emp表创建了同义词,但他在创建前必须首先取得可以访问该表的权限。
同义词有两种类型,私有同义词和公有同义词。
1、私有同义词。
私有同义词只能被当前模式的用户访问。
分为两种情况,一种是在自身模式内创建私有同义词,用户必须拥有createsynonym系统权限;一种是在其它用户模式下创建私有同义词,用户必须拥有createanysynonym系统权限。
创建私有同义词的语法如下:
其中:
orrplace:
表示该同义词如果存在,就替换该同义词。
Synonym_name:
表示要创建的同义词名称。
Object_name:
要创建的同义词对象的名称。
比如:
假设系统中有两个用户模式:
itjob和scott,scott用户有表emp。
itjob用户需要频繁的引用scott模式的emp表,每次的查询语句都必须如下所写。
为了简化,itjob就需要为scott的emp表建立一个同义词,但要建立同义词,首先得让scott用户授权给itjob才行,这样,我们先给itjob用户授权,让他能够访问scott的表emp。
怎么做呢?
首先以scott用户身份登录数据库,执行以下命令。
再以itjob用户身份登录数据库,创建同义词,执行以下命令。
这样,以后itjob用户想访问scott用户的emp表,就可以执行以下命令。
2、公有同义词。
公有同义词可以被所有的数据库用户访问。
公有同义词可以隐藏表的所有者,并降低sql语句的复杂性。
要创建公有同义词,用户必须具有createpublicsynonym系统权限。
创建公有同义词的语法如下。
比如:
scott用户为了想让自己的表emp能被任何其他用户访问到,他就可以创建一个公有模式,其他用户就可以直接访问该表了,而不需要知道该表是哪个用户模式。
在scott登录数据库系统后,执行下列语句。
3、一个系统中,可能存在一些同义词,怎么知道有哪些同义词存在呢,它们又分别是针对哪些数据库对象建立的同义词呢?
可以通过查询视图USER_SYNONYMS来查看同义词的详细信息。
如:
4、删除同义词。
格式:
八、序列。
序列非常类似sqlserver中的标识列。
序列是用来生成唯一、连续的整数的数据库对象,序列通常用来自动生成主键,序列可以按升序排列,也可以按降序排列。
1、创建序列,格式如下。
其中:
startwith:
指定要生成的第一个序列号,对于升序序列,其默认值为序列的最小值,对于降序序列,其默认值为序列的最大值。
类似sqlserver的标识种子。
Incrementby:
指定序列号之间的间隔,其默认值为1。
如果增量为正,则生成的序列按升序排列,如果增量为负,则按降序排列。
类似sqlserver的标识增量。
Maxvalue:
指定序列可以生成的最大值。
默认是1027
Minvalue:
指定序列的最小值。
默认是-10-26。
Minvalue必须小于或等于start_with的值,并小于maxvalue。
Nomaxvalue:
表示是否设置最大值。
如果设置了该参数,oracle将升序序列的最大值设置为1027,将降序序列的最大值设置为-1。
这是默认选项。
Nominvalue:
表示是否设置最小值。
如果设置该参数,oracle将升序序列的最小值设置为1,将降序序列的最小值设置为-1026,这是默认选项。
Cycle:
指定序列在达到最大值或最小值后,将从头开始生成值。
对于升序,如果指定nominvalue参数,就从1开始,否则,就根据minvalue参数从头开始。
对于降序,如果指定了nomaxvalue参数,就从-1开始,否则,就根据maxvalue参数从头开始。
Nocycle:
指定序列在达到最大值或最小值后,将不能再继续生成值。
这是默认选项。
Cache:
使用cache选项,系统预先分配一组默认序列号放到缓存中,可以加快速度。
Nocache:
不使用缓存。
如果没有用cache和nocache,系统默认缓冲20个序列号。
比如:
创建一个序列号。
2、使用序列。
序列创建完成后,可以通过currval和nextval伪列来访问该序列的值。
可以从伪列中选择值,但不能操纵它们的值。
下面分别说明currval和nextval。
Currval:
保存有序列当前的值,即最后一次引用nextval时返回的值。
Nextval:
创建序列后第一次使用nextval时,将返回该序列的初始值。
以后在引用nextval时,将使用incrementby子句的值来增加序列值,并返回这个新值。
比如:
也可以随时查询当前序列的值。
比如:
3、修改序列。
序列一旦创建,除了不能修改序列的起始值外,其余都可修改。
格式如下。
比如:
可以通过查询USER_SEQUENCES的字典视图,获取用户所创建的序列的详细信息。
4、删除序列。
格式如下:
作业
1、数据定义语言是用于______的方法。
A)确保数据的准确性。
B)定义和修改数据结构。
C)查看数据。
D)删除和更新数据。
2、emp表包含下面这些字段
管理部门想要一份在公司工作了5年以上的员工名单。
可显示所需结果的语句是____。
A)selectenamefromempwheresysdate–hire_date>5;
B)selectenamefromempwherehire_date–sysdate>5;
C)selectenamefromempwhere(sysdate–hire_date)/365>5;
D)selectenamefromempwhere(sysdate–hire_date)*365>5;
3、employees表的结构如下:
考虑下面的语句。
完成本次事务处理后,以下说法正确的是_______。
A)表中没有行。
B)有一个姓名为james的行。
C)不能多次回滚到相同的保存点。
D)由于已经删除了员工编号为180的记录,所以最后一次更新操作未更新任何行。
4、能为计算列sal*12生成别名annualsalary的语句是________。
A)selectename,sal*12‘annualsalary’fromemp;
B)selectename,sal*12“annualsalary”fromemp;
C)selectename,sal*12asannualsalaryfromemp;
D)selectename,sal*12asinitcap(annualsalary)fromemp;
5、在select语句中使用________子句来只显示工资超过5000的员工。
A)orderbysalary>5000
B)groupbysalary>5000
C)havingsalary>5000
D)wheresalary>5000
6、___________函数通常用来计算累计排名。
A)汇总B)分析C)分组D)单行
7、要以自身的模式创建私有同义词,用户必须拥有_______系统权限。
A)createprivatesynonymB)createpublicsynonym
C)createsynonymD)createanysynonym
8、可以使用________伪列来访问序列。
A)CURRVAL和NEXTVALB)NEXTVAL和PREVAL
C)CACHE和NOCACHED)MAXVALUE和MINVALUE
9、显示同时符合以下条件的书籍的名称。
购买于2001年1月21日前;价格低于500元或超过900元。
编写一个语句,按书籍的购买日期对结果