第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）//表示小数点前最多8位，小数点后保留2位。
number（10）//表示小数点前最多10位，且是一个整数。
number（10,-1）//表示精确到十位数。
number（10,-2）//表示精确到百分位。
比如：
如果某个字段定义成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元。
编写一个语句，按书籍的购买日期对结果排序，购买日期最近的书籍排在前面。
10、检查employees表的结
升级会员 