Oracle Class2. SQL查询和SQL函数(Oracle数据类型,ddl,dml,dcl,事务控制语言tcl,sql操作符,sql函数,select语句,运算符,分析函数,临时表)
------------------------2013-5-6------------------------
system/manager@ora1128 as sysdba
Connect system/****@ora1128 AS SYSDBA
start C:\script.sql;
desc scott.emp;
col LOC heading location; --修改列的标题名称
show all --显示当前环境变量的值
show error --显示当前在创建函数、存储过程、触发器、包等对象的错误信息。
show rel --显示数据库版本
show sga --显示SGA的大小
show user --显示当前的用户名
ora-01536: 超出表空间system的空间限量 -->赋予dba角色可以解决问题。
系统权限 管理选项
create procedure x
create table x
create tablespace x
unlimited tablespace x
角色 管理选项 默认值
connect x 勾选
resource x 勾选
show user;
set sqlprompt Oaichinese;
alter table newlifeyhj.students add(stu_xxx varchar(12));
DBMS_OUTPUT.put_line('============================='); 测试窗口，打印输出。
聚合函数RANK 和 dense_rank 主要的功能是计算一组数值中的排序值。
select * from (select rank() over(partition by title order by fenshu desc) rk,newlifeyhj.a.* from newlifeyhj.a) t
where t.rk<=3;
dense_rank和rank区别：
dense_rank与rank()用法相当，但是有一个区别：dence_rank在并列关系时，相关等级不会跳过,rank则跳过!
比如:存在两个第一名是,dence_rank函数接下来是第二名，而rank函数接下来是第三名!/
收入排行
select empno, sal, rank() over(order by sal desc) R from emp;
select empno,sal,dense_rank() over(order by sal desc) R from emp;
sql操作符
算术操作符，+ - * /
比较操作符，= != < > <= >= between and, in,like, is null
逻辑操作符，and,or,not
集合操作符，union,union all,intersect,minus
连接操作符，||
操作符优先级
算术操作符 最高
连接操作符
比较操作符
逻辑操作符
not逻辑操作符
and逻辑操作符
or逻辑操作符 最低
sql函数
单行函数,每一行只返回一个值，日期函数，数字函数，字符函数，转换函数，其他函数。
分组函数,一组行来返回结果，为每个组行返回一个值。
分析函数,一组行来计算聚合值，累计排名，移动平均数，为每组记录返回多个行。
日期函数包括：
add_months
months_between
last_day
round
next_day
trunc
extract
字符函数
Initcap(char)
lower(char)
upper(char)
ltrim(char)
rtrim(char)
translate(char,from,to)
instr(char,m,n)
substr(char,m,n)
concat(expr1,expr2)
其他的字符函数
chr和ascii
lpad和rpad
trim
length
decode
490. 在ORACLE 中，语句Select decode(7782,7369,'test1',7782,'test2','other') from dual;查询结
DECOD
-----
test2
数字函数
abs(n) 绝对值
ceil(n) 比该值大的最小值
cos(n) 余弦
cosh(n)
floor(n) 比该值小的最大值
power(m,n) m的n次方
mod(m,n) 求余数
round(m,n) n位小数点，四舍五入。
trunc(m,n) n位小数点，截断，无四舍五入。
sqrt(n) 求开平方
sign(n) >0 1, =0 0, <0 -1 标记值。
转换函数
to_char
to_date
to_number
转换空值的函数
nvl
nvl2
nullif
分组函数
avg min max sum count select count(distinct qty_hand) from itemfile;
group by和having
group by子句
用于将信息划分为更小的组，每一组行返回针对该组的单个结果
having子句
用于指定group by子句检索行的条件
分析函数 row_number,rank,dense_rank
row_number 返回连续的排位，不论值是否相等。
rank 具有相等值的行排位相同，序数随后跳跃。
dense_rank 具有相等值的行排位相同，序号是连续的。
--返回连续的排位，不论值是否相等。
select ename,job,deptno,sal,row_number() over(order by sal desc) as sal_rank from scott.emp;
--按deptno分区排序
select ename,job,deptno,sal,rank() over(partition by deptno order by sal desc) as sal_rank from scott.emp;
--具有相等值的行排位相同，序数随后跳跃。
select ename,job,deptno,sal,rank() over(order by sal desc) as sal_rank from scott.emp;
--按deptno分区排序
select ename,job,deptno,sal,dense_rank() over(partition by deptno order by sal desc) as sal_rank from scott.emp;
--具有相等值的行排位相同，序号是连续的。
select ename,job,deptno,sal,dense_rank() over(order by sal desc) as sal_rank from scott.emp;
------------------------2013-5-7------------------------
用于存储和检索数据库中数据的语言是SQL
select可以用于：
创建现有表的副本 create table tbl as select * form tbl2 where 条件 and 1 <> 2 --复制表结构
插入来自其他表的记录 insert into tbl select * from tbl2; --复制表数据
使用别名让列显示其他名称 select col 字段1, col2 as 字段2 from tbl; --别名显示
select initcap('hello') from dual; --Hello
select lower('FUN') from dual; --fun
select upper('sun') from dual; --SUN
select ltrim('xyzadmin','xyz') from dual; --admin
select rtrim('xyzadmin','admin') from dual; --xyz
select translate('jack','j','b') from dual; --back
select replace('jack and jue','j','bl') from dual; --black and blue
select translate('jack and jue','j','k') from dual; --kack and kue 只能改变一个字符。
select instr('worldwide','d') from dual; --5
select substr('abcdefg',3,2) from dual; --cd
select concat('Hello','world') from dual; --Helloworld
select chr(65) from dual; --A
select lpad('function',15,'=') from dual; =======function
select rpad('function',15,'=') from dual; function=======
select trim(9 from 9999876789999) from dual; --87678
select length('frances') from dual; --7
select to_char(sysdate,'YYYY "年" fmMM "月" fmDD "日" HH24:MI:SS') from dual; --2013 年 5 月 07 日 10:19:39
--最多为5位数字，以本地货币符号作为前缀
select empno,to_char(sal,'C99999') from scott.emp; --7369 CNY800
select to_date('2005-12-06','yyyy-mm-dd') from dual; --06-12月-05
select to_number('100') from dual; --100
临时表
只在用户会话期间存在的表
数据在用户事务处理期间持续存在
使用create global temporary table命令
不会自动获得空间
插入的值仅在此特定会话中可用
desc dual;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
DUMMY VARCHAR2(1)
Rollup和Cube运算符用于查找表达式的聚集和超级聚集。
统计函数 根据分组情况进行统计，最终进行全部汇总。
Rollup结果集包含分组行和小计行
rollup(column_name1,column_name2)
--根据Deptno和JOB进行分组,求相同deptno,相同job员工工资总和。
select deptno,job,sum(sal) from scott.emp group by deptno,job order by deptno;
select dummy from dual group by rollup(dummy);
--以deptno为基准，按job统计各个部门的job总和。
select deptno,job,sum(sal) from scott.emp where deptno is not null group by rollup(deptno,job) order by deptno;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
DEPTNO JOB SUM(SAL)
---------- --------- ----------
30 9400
29025
--以job为基准，按部门统计各个job的总和。
select deptno,job,sum(sal) from scott.emp where deptno is not null group by rollup(job,deptno) order by job;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
20 ANALYST 6000
ANALYST 6000
10 CLERK 1300
20 CLERK 1900
30 CLERK 950
CLERK 4150
10 MANAGER 2450
20 MANAGER 2975
30 MANAGER 2850
MANAGER 8275
10 PRESIDENT 5000
DEPTNO JOB SUM(SAL)
---------- --------- ----------
PRESIDENT 5000
30 SALESMAN 5600
SALESMAN 5600
29025
select deptno,job,sum(sal) from scott.emp where deptno is not null group by rollup(job,deptno) order by deptno;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
30 CLERK 950
30 SALESMAN 5600
30 MANAGER 2850
ANALYST 6000
PRESIDENT 5000
DEPTNO JOB SUM(SAL)
---------- --------- ----------
SALESMAN 5600
29025
MANAGER 8275
CLERK 4150
Cube产生交叉表格报表
cube(column_name1,column_name2,column_name3)
select deptno,job,sum(sal) from scott.emp where deptno is not null group by cube(deptno,job) order by deptno;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
DEPTNO JOB SUM(SAL)
---------- --------- ----------
30 9400
ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
29025
grouping: 如果列值为空，那么grouping()返回1，否则返回0
select grouping(deptno), deptno,job,sum(sal) from scott.emp where deptno is not null group by cube(deptno,job) order by deptno;
grouping sets提供了指定汇总集合条件的功能。
select deptno,job,sum(sal) from scott.emp where deptno is not null group by grouping sets(deptno,job);
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 8750
20 10875
30 9400
ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
#177. 关于Oracle的LONG类型描述正确的是： abd
A LONG数据类型中存储的是可变长字符串，最大长度限制是2GB
B LONG类型主要用于不需要作字符串搜索的长串数据，如果要进行字符搜索就要用varchar2类型
C 索引LONG类型列会明显提升查询效率
D 一个表中只能包含一个LONG类型的列
根据别名来排序：
select empno 编号,ename,sal from scott.emp order by 编号;
--对检索结果（第2个，第1个）的排序--
select empno 编号,ename,sal from scott.emp order by 2,1;
--对检索结果（第3个）的排序--
select empno 编号,ename,sal from scott.emp order by 3 desc;
select instr('Mississippi','i',3,3) from dual; -- 11
short类型长度16位。
int,float类型长度32位。
long,double类型长度64位。
472. 关于索引的说法错误的是BD
A、删除索引不会影响表中的数据。
B、索引表中也有rowid 列
C、索引又系统管理和应用。
D、索引总是能够提高检索效率
