第2章 sql查询、sql函数与数据库对象.doc

第二章 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.91234.567890 保存在该字段中的值为1234.57如果某个字段定义成number(10)，该字段取值如下。1234567890 保存在该字段中的值为12345678901234.567890 保存在该字段中的值为1235如果某个字段定义成number(10,-2)，该字段取值如下。1234567890 保存在该字段中的值为12345679001234567846 保存在该字段中的值为12345678001234.567890 保存在该字段中的值为12003、日期型。日期数据类型用于存储日期值和时间值。（1）date数据类型。用于存储表中的日期和时间数据。Oracle使用自己的格式存储日期，使用7个字节固定长度，每个字节分别存储世纪、年、月、日、小时、分钟、秒。建表时定义字段的日期类型，只有date类型可选。（2）timestamp数据类型。该数据类型用于存储日期的年、月、日、小时、分、秒，其中秒值精确到小数点后6位，该数据类型同时包含时区信息。使用systimestamp函数可以获取系统当前日期、时间和时区。4、二进制类型。raw和long raw数据类型用于存储二进制数据。5、LOB数据类型。该类型用于存储多达4G的非结构化信息，比如声音剪辑、视频剪辑等。其中，CLOB用于存储大量的字符数据，BLOB用于存储较大的二进制对象，如图形、声音、视频等。二、oracle中的伪列与伪表。伪列就像oracle中某个表中的某个字段，但该列实际上并未存储在表中。伪列可以从表中查询，但不能插入、更新或删除它们的值。（1）rowid伪列。数据库中的每一行都有一个行地址，rowid伪列就保存了该行地址信息。可以使用rowid值来定位表中的一行。通常，rowid的值可以唯一的标识表中的一行。伪列的作用是使系统能以最快的方式访问表中的某一行。对比如下两条语句。SQLselect empno,ename from scott.emp;SQLselect rowid,empno,ename from scott.emp;（2）rownum伪列。对于查询返回的每一行，rownum伪列返回一个数值代表行的次序，返回的第一行的rownum值为1，第二行的rownum的值为2，依此类推。通过rownum伪列，用户可以限制查询返回的行数。（sql server中限制返回行数是用top n来表示，还记得么？）比如。SQLselect * from scott.emp where rownumcreate table itjob.examp (a varchar2(10) primary key,b varchar2(10) not null,c date default systimestamp,d number(10) check (d10 and dcreate table order_master (os char(1),del_date date); /创建一个带日期字段的表SQLinsert into order_master values (1,25-6月-05); /插入正确SQLinsert into order_master values(2,to_date(2005-07-31,yyyy-mm-dd); /插入正确也可以插入来自其它表的记录。Insert命令可以用来复制其它表中的记录。格式如下。SQLinsert into 表名1 select * from 表名2；/从表2中复制记录到表1中，与sql server完全一样。8、update命令。与sql server完全一样。9、delete命令。与sql server完全一样。四、事务控制语句。1、commit命令。事务提交命令。格式：commit;2、savepoint命令。用于设置保存点的命令。格式如下。Savepoint savepoint_1; /savepoint_1是指保存点名字，可任意命名。3、rollback命令。格式：rollback;比如：SQLupdate order_master set del_date=30-6月-05 where orderno=1;SQLsavepoint mark1;SQLdelete from order_master where orderno=1;SQLsavepoint mark2;SQLrollback to savepoint mark1;SQLcommit;要理解上述过程，请分别在上述每条语句后加一条查询该表的语句来看看结果的变化。四、数据控制语句。1、grant命令。如果用户创建了某个数据库对象，比如表、视图、序列、同义词等，该用户就具有对这些对象的所有权限。当该用户需要将操纵这些对象的权限授予其它用户时，就需要使用grant命令授权。比如，将order表的查询和更新权限授予martin用户，如下。SQLgrant select , update on order to martin;再如：只将order表中的某两个字段的更新权限授予martin用户SQLgrant update( qty , level ) on order to martin;2、revoke命令。要撤销已授予用户的权限，可以使用revoke命令。此命令在格式上与grant非常类似。比如，将order表的查询和更新权限从martin用户收回。如下。SQL revoke select , update on order from martin;五、sql操作符。1、算术操作符。指 。其优先顺序和用法与sql server完全相同。2、比较操作符。指， ！， ， ， 。其用法与sql server完全相同。3、between and 用法与sql server完全相同。4、in用法与sql server完全相同。SQLselect orderno from master where deldate in (15-5月-05,15-2月-05);5、like用法与sql server完全相同。6、is null或is not null用于判断某个字段的值是否为空。用法与sql server完全相同。如：从master表中找出deldate值为空的所有记录。SQLselect * from master where deldate is null ；7、逻辑操作符。逻辑操作符指not ，and ，or 。其优先顺序与用法与sql server完全相同。8、连接操作符号 | 。连接操作符用于将两个或多个字符串合并成一个字符串，或将一个字符串与一个数值合并在一起。如：SQLselect ( 供应商| name | 的电话是 | telephone ) as 联系方式 from order;sql语句结果联系方式-供应商的 李明 的电话是2565616 /其中，李明,2565616等信息是从数据库取出来。六、sql函数。1、日期函数。（1）Add_MONTHS格式：ADD_MONTHS(日期，月数)作用：给指定的日期加上指定的月数后的日期值。比如：SQLselect systimestamp , add_months(systimestamp , 2 ) from dual ;（2）MONTHS_BETWEEN格式：MONTHS_BETWEEN（日期1，日期2）作用：返回两个日期之间的月数。如果日期1晚于日期2，结果为正数；否则为负数。如果日期1和日期2均是某月中的同一天或月份的最后一天，则结果始终为整数，否则， oracle将根据一个月31天来计算结果的小数部分，并考虑日期1和日期2之间时间部分的差。比如：SQLselect months_between(31-10月-07,31-7月-07) from dual ;SQLselect months_between(31-7月-07,30-6月-07) from dual ;SQLselect months_between(11-5月-07,30-3月-07) from dual ;（3）LAST_DAY格式：LAST_DAY（日期）作用：返回指定日期当月的最后一天的日期。比如：SQL select sysdate , last_day(sysdate) from dual;（4）NEXT_DAY格式：NEXT_DAY（日期，星期几）作用：此函数指定日期的下一个星期几的日期。比如：SQLselect next_day (sysdate,星期二) from dual; /返回下个星期二的日期（5）TRUNC格式：TRUNC（日期，可选参数）可选参数可以是：year，month，day，如果不写，默认就是参数day。作用：对指定的日期按参数进行截取。如果是参数year，则返回值将原日期的月和日变成1月1日，年份不变；如果参数是month，则返回值将原日期的日变成1，年和月份不变；如果参数是day，则返回原日期。比如：SQLselect trunc(sysdate , year) from dual ； /返回当前日期的年份，月和日变成1月1日。SQLselect trunc(sysdate, month) from dual ； /返回当前日期的年月，日期部分变成1日。SQLselect trunc(sysdate, day) from dual ； /返回当前日期的年月日。（6）EXTRACT函数。格式：EXTRACT（ fmt from d） 其中，fmt是要提取的格式，可以是year，month，day，hour，minute，second等。并且不要放在单引号内。d表示日期。作用：该函数提取日期时间类型中的特定部分。比如：SQL select extract ( year from sysdate ) from dual; /提取年份数据SQL select extract ( month from sysdate ) from dual; /提取月份数据SQL select extract ( day from sysdate ) from dual; /提取日期数据2、字符函数。函数说明输入输出结果INITCAP(char)首字母大写Select initcap(hello) from dual;HelloLOWER(char)转换成小写Select lower(ABC) from dual;AbcUPPER(char)转换成大写Ltrim(char)截左边空格Select ltrim( abc) from dual;abcRtrim(char)截右边空格Select rtrim(abc ) from dual;abc Replace(char1,char2,char3)字符串替换Select replace(jack,j,bl) from dual;blackInstr(char,substr,pos)查找子串位置Select instr(abcd,d,1) from dual;4Substr(char,pos,len)取子字符串Select substr(abcdefg,3,2) from dual;cdConcat(char1,char2)连接字符串Select concat(hello,world) from dual;helloworldlength求字符串长度Select length(france) from dual;63、数字函数。函数说明输入输出ABS取绝对值Select abs(-15) from dual;15Ceil向上取整Select ceil(15.2) from dual;16floor向下取整Select floor(15.2) from dual;15round(m,n)四舍五入Select round(100.256,2) from dual;100.26Mod(m,n)取余数Select mod(10,3) from dual;1Power(m,n)M的n次幂Select power(5,2) from dual;254、类型转换函数。（1）TO_CHAR格式： to_char（要转换的内容 , 转换后的格式 ） 转换后的格式 这部分可以根据需要决定要不要。作用：转换成varchar2类型的字符串比如：SQLselect to_char(sysdate) from dual; /没有指定格式。SQL select to_char(sysdate,yyyy年fmmm月dd日 hh24:mi:ss) from dual;/指定转换后的格式。月份小于10，则只显示1位数。SQL select to_char(sysdate , yyyy”年”mm”月”dd”日” hh24:mi:ss) from dual; /指定转换后的格式。月份小于10，则在月份前补0。（2）TO_DATE格式：to_date ( char , fmt )作用：将 char或varchar2数据类型转换为日期数据类型。格式fmt指定字符的形式。比如：SQLselect to_date ( 2005/05/26,yyyy/mm/dd) from dual;（3）TO_NUMBER将数字字符串转换成number数据类型，从而进行数学运算。通常不要这么做，因为系统会自动进行隐式转换。比如：SQLselect to_number(100) from dual;5、其它函数。（1）空值替换函数。格式：NVL(字段或变量，值)作用：某个字段或变量的取值如果为null，可用该函数将null值替换成指定的值。比如：如果level字段的值是null，则将其替换为0SQLselect item , NVL(level,0) from tiemfile;（2）NVL2函数。格式：NVL2（表达式1，表达式2，表达式3）作用：如果表达式1的值不是null，则返回表达式2的值，否则，返回表达式3的值。比如：如果level字段有值，就取出并显示，否则，就显示maxlevel字段的值。SQLselect item,NVL2( level , level , maxlevel) from item;（3）NULLIF格式：NULLIF（表达式1，表达式2）作用：如果表达式1和表达式2相等，则返回空值，否则，返回表达式1的值。比如：SQL select item, nullif ( level , maxlevel ) from item;6、聚合函数。Avg，min，max，sum，count其用法与sql server完全相同。7、分析函数。（1）ROW_NUMBERROW_NUMBER为有序组中的每一行（查询结果返回的行）返回一个唯一的排序值，序号由order by子句指定，从1开始。比如：从scott . emp表中按工资sal字段排序。SQLselect ename,job,deptno,sal from scott .emp order by sal desc/这是通常的做法，虽然已经按sal字段降序排列，但没有显示序号。结果如下图1。SQLselect ename,job,deptno,sal,row_number( ) over (order by sal desc) from scott .emp;/返回结果与上面不同的是，仅仅增加了一个序号。结果如下图2。图1图2注意到，图2中第2名与第3名的工资完全相同，但名次不一样，所有员工都是按工资多少参与排序的。能不能将部门分组，每个部门内的员工按工资多少排序，而部门与部门的排序互不影响呢？请看下例。下例只是在上面的语句中增加了partition by deptno这个子句，表示按部门号进行分组，同一部门号的员工，按工资排序，不同部门号的员工互不影响。结果如下图3。SQLselect ename,job,deptno,sal,row_number( ) over (partition by deptno order by sal desc) as sal_rank from scott . emp; 图3（2）DENSE_RANK计算一个值在一组有序行中的排名，排名是以1开始的连续整数。具有相同值的行排名相同，并且排名是连续的。而图3中同一部门的员工，如果工资sal相同，其排名是不同的。如果同一部门的员工，工资sal相同，其排名也相同，则要用到dense_rank。比如：下列语句对于同一部门内工资相同的员工，其排名相同，且部门内所有员工的排名连续。结果如图4。SQLselect ename,job,deptno,sal,dense_rank( ) over (partition by deptno order by sal desc) as sal_rank from scott.emp;图4七、同义词同义词可以是数据库对象的一个别名，这些对象可以是表、视图、序列、过程、函数、程序包。通过使用同义词，用户可以访问其它模式的数据库对象而无需指定模式前缀。比如，用户user1想访问user2的表emp，必须使用user2 .emp来引用，如果user1创建了一个名为emp的同义词代表user2.emp，那么，user1就可以用该同义词象访问自己的表一样引用user2.emp表了。但同义词不能代替权限，在使用同义词之前要确保用户已得到访问对象的权限。上述user1对 user2的emp表创建了同义词，但他在创建前必须首先取得可以访问该表的权限。同义词有两种类型，私有同义词和公有同义词。1、私有同义词。私有同义词只能被当前模式的用户访问。分为两种情况，一种是在自身模式内创建私有同义词，用户必须拥有create synonym系统权限；一种是在其它用户模式下创建私有同义词，用户必须拥有create any synonym系统权限。创建私有同义词的语法如下：Create or replace synonym schema.synonym_name for schema.object_name;其中：or rplace：表示该同义词如果存在，就替换该同义词。Synonym_name：表示要创建的同义词名称。Object_name：要创建的同义词对象的名称。比如：假设系统中有两个用户模式：itjob和scott ，scott用户有表emp 。itjob用户需要频繁的引用scott模式的emp表，每次的查询语句都必须如下所写。SQL Select * from scott . emp ； /每次在表名前都要加该表的模式名scott。为了简化，itjob就需要为scott的emp表建立一个同义词，但要建立同义词，首先得让scott用户授权给itjob才行，这样，我们先给itjob用户授权，让他能够访问scott的表emp。怎么做呢？首先以scott用户身份登录数据库，执行以下命令。SQL grant all on emp to itjob ； /这样，scott就授给itjob用户权限了。再以itjob用户身份登录数据库，创建同义词，执行以下命令。SQL create synonym emp for scott . emp； /创建属于itjob用户的同义词emp，但该同义词只能被itjob使用，所以是私有同义词。这样，以后itjob用户想访问scott用户的emp表，就可以执行以下命令。SQL Select * from emp ； /表名前不要模式名scott。2、公有同义词。公有同义词可以被所有的数据库用户访问。公有同义词可以隐藏表的所有者，并降低sql 语句的复杂性。要创建公有同义词，用户必须具有create public synonym系统权限。创建公有同义词的语法如下。Create or replace public synonym synonym_name for schema.object_name;比如：scott用户为了想让自己的表emp能被任何其他用户访问到，他就可以创建一个公有模式，其他用户就可以直接访问该表了，而不需要知道该表是哪个用户模式。在scott登录数据库系统后，执行下列语句。SQL create public synonym emp_syn for scott.emp3、一个系统中，可能存在一些同义词，怎么知道有哪些同义词存在呢，它们又分别是针对哪些数据库对象建立的同义词呢？可以通过查询视图USER_SYNONYMS来查看同义词的详细信息。如：SQL select * from USER_SYNONYMS4、删除同义词。格式：SQL drop public synonym schema.synonym_name；八、序列。序列非常类似sql server中的标识列。序列是用来生成唯一、连续的整数的数据库对象，序列通常用来自动生成主键，序列可以按升序排列，也可以按降序排列。1、创建序列，格式如下。Create sequence sequence_name start with integer increment by integer maxvalue integer | nomaxvalue minvalue integer | nominvaluecycle | nocycle cache integer | nocache;其中：start with：指定要生成的第一个序列号，对于升序序列，其默认值为序列的最小值，对于降序序列，其默认值为序列的最大值。类似sql server的标识种子。Increment by：指定序列号之间的间隔，其默认值为1。如果增量为正，则生成的序列按升序排列，如果增量为负，则按降序排列。类似sql server的标识增量。Maxvalue：指定序列可以生成的最大值。默认是1027Minvalue：指定序列的最小值。默认是-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个序列号。比如：创建一个序列号。SQLcreate sequence toys_seqStart with 10Increment by 1Maxvalue 2000NocycleCache 30;2、使用序列。序列创建完成后，可以通过currval和nextval伪列来访问该序列的值。可以从伪列中选择值，但不能操纵它们的值。下面分别说明currval和nextval。Currval：保存有序列当前的值，即最后一次引用nextval时返回的值。Nextval：创建序列后第一次使用nextval时，将返回该序列的初始值。以后在引用nextval时，将使用increment by 子句的值来增加序列值，并返回这个新值。比如：SQL insert into toys (toyed,toyname,toyprice)Values (toys_seq.nextval , twenty , 25) ;SQL insert into toys (toyed,toyname,toyprice)Values (toys_seq.nextval , mary , 125) ;也可以随时查询当前序列的值。比如：Select toys_seq.currval from dual ；3、修改序列。序列一旦创建，除了不能修改序列的起始值外，其余都可修改。格式如下。Alter sequence sequence_name increment by integer maxvalue integer | nomaxvalue minvalue integer | nominvaluecycle | nocycle cache integer | nocache;比如：SQLalter sequence toys_seqMaxvalue 5000Cycle;可以通过查询USER_SEQUENCES的字典视图，获取用户所创建的序列的详细信息。4、删除序列。格式如下：SQL drop sequence toys_seq;作业1、数据定义语言是用于_的方法。A) 确保数据的准确性。 B)定义和修改数据结构。C) 查看数据。 D)删除和更新数据。2、emp表包含下面这些字段Ename varchar2(35)Salary number(8,2)Hire_date date管理部门想要一份在公司工作了5年以上的员工名单。可显示所需结果的语句是_。A) select ename from emp where sysdate hire_date 5 ;B) select ename from emp where hire_date sysdate 5 ;C) select ename from emp where (sysdate hire_date)/365 5 ;D) select ename from emp where (sysdate hire_date)*365 5 ;3、employees表的结构如下：Employee_id number primary keyFirst_name varchar2(25)Last_name varchar2(25)Hire_date date考虑下面的语句。1Create table new_emp (employee_id number , name varchar2(30) );2Insert into new_emp select employee_id, last_name from employees;3Savepoint s2;4Delete from new_emp;5Rollback to s2;6Delete from new_emp where employee_id=180;7Update new_emp set name=james;8Rollback to s2;9Update new_emp set name=james where employee_id=180;10Rollback;完成本次事务处理后，以下说法正确的是_。A)表中没有行。B)有一个姓名为james的行。C)不能多次回滚到相同的保存点。D)由于已经删除了员工编号为180的记录，所以最后一次更新操作未更新任何行。4、能为计算列sal*12生成别名annual salary的语句是_。A)select ename , sal*12 annual salary from emp;B) select ename , sal*12 “annual salary” from emp;C) select ename , sal*12 as annual salary from emp;D) select ename , sal*12 as initcap(annual salary) from emp;5、在select 语句中使用_子句来只显示工资超过5000的员工。A) order by salary 5000B) group by salary 5000C) having salary 5000D) where salary 50006、_函数通常用来计算累计排名。A)汇总 B)分析 C)分组 D)单行7、要以自身的模式创建私有同义词，用户必须拥有_系统权限。A) create private synonym B) create public synonymC)