1、 oracle安装
|
资源需要：
rpm -qa |grep libXp 创建数据库安装的准备工作: passwd oracle 如果nobody用户不存在（id nobody命令查看)，则创建： 2，建立oracle安装文件夹(sample)； 3， 配置环境变量；
su - root
kernel.sem = 250 32000 100 128 net.core.rmem_default=262144 修改后运行"/sbin/sysctl -p"命令使得内核改变立即生效；
username|@groupname：设置需要被限制的用户名，组名前面加@和用户名区别。也可以用通配符*来做所有用户的限制。 type：有 soft，hard 和 -，soft 指的是当前系统生效的设置值。hard 表明系统中所能设定的最大值。soft 的限制不能比hard 限制高。用 - 就表明同时设置了 soft 和 hard 的值。 resource：
解压缩Oracle10G的安装文件 ： (1) 简化过程（以oracle登录，释放安装文件,使用更少的磁盘空间，速度更快） xhost + |
2、登陆并启动数据库的操作。
[oracle@oracle oracle]$ lsnrctl start
[oracle@oracle oracle]$ sqlplus /nolog
SQL> connect / as sysdba
Connected.
SQL> shutdown immediate 关闭数据库 （OR "dbshut" command)
SQL> startup; 启动数据库
--配置监听
netmgr
cd /u01/app/oracle/product/10g/network/admin
[oracle@emrep admin]$ lsnrctl stop --->停监听
lsnrctl start--->起监听
lsnrctl stat---->查看监听状态
[oracle@emrep bin]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 30 14:42:39 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> show user
USER is ""
SQL> connect system/oracle
Connected.
SQL> show user
USER is "SYSTEM"
SQL> connect sys as sysdba
Enter password: ******
Connected.
[oracle@emrep bin]$ sqlplus / as sysdba
[oracle@emrep bin]$ sqlplus system/oracle
SQL> select username from dba_users;
SQL> alter user scott identified by tiger account unlock;---->修改scott用户的密码同时解锁
User altered.
SQL> conn scott/tiger
Connected.
SQL> show user
USER is "SCOTT "
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
--------------------------------
SQL> @?/rdbms/admin/utlsaml.sql
-->重建scott用户以及所属的table, @ 表示运行脚本， ? 是代替 $ORACLE_HOME
--------------------------------
3、不同的名称
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string lijh ----->数据库名字
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string lijh ------>实例名称
SQL> show parameter db_uni
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string lijh ----->数据库唯一名称
SQL> show parameter service_na
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string lijh
SQL> show parameter global
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_context_pool_size string
global_names boolean FALSE
SQL>
概念：
Db_name：对一个数据库（Oracle database）的唯一标识。这种表示对于单个数据库是足够的，但是随着由多个数据库构成的分布式数据库的普及，这种命令数据库的方法给数据库的管理造成一定的负担，因为各个数据库的名字可能一样，造成管理上的混乱。为了解决这种情况，引入了Db_domain参数，这样在数据库的标识是由Db_name和Db_domain两个参数共同决定的，避免了因为数据库重名而造成管理上的混乱。这类似于互连网上的机器名的管理。我们将Db_name和Db_domain两个参数用’.’连接起来，表示一个数据库，并将该数据库的名称称为Global_name，即它扩展了Db_name。Db_name参数只能由字母、数字、’_’、’#’、’$’组成，而且最多8个字符。
Db_domain：定义一个数据库所在的域，该域的命名同互联网的’域’没有任何关系，只是数据库管理员为了更好的管理分布式数据库而根据实际情况决定的。当然为了管理方便，可以将其等于互联网的域。
Global_name：对一个数据库（Oracle database）的唯一标识，oracle建议用此种方法命令数据库。该值是在创建数据库是决定的，缺省值为Db_name. Db_domain。在以后对参数文件中Db_name与Db_domain参数的任何修改不影响Global_name的值，如果要修改Global_name，只能用ALTER DATABASE RENAME GLOBAL_NAME TO <db_name.db_domain>命令进行修改，然后修改相应参数。
Service_name：该参数是oracle8i新引进的。在8i以前，我们用SID来表示标识数据库的一个实例，但是在Oracle的并行环境中，一个数据库对应多个实例，这样就需要多个网络服务名，设置繁琐。为了方便并行环境中的设置，引进了Service_name参数，该参数对应一个数据库，而不是一个实例，而且该参数有许多其它的好处。该参数的缺省值为Db_name. Db_domain，即等于Global_name。一个数据库可以对应多个Service_name，以便实现更灵活的配置。该参数与SID没有直接关系，即不必Service name 必须与SID一样。
Instance_name：数据库实例名。用于和操作系统之间的联系，用于对外部连接时使用。在操作系统中要取得与数据库之间的交互，必须使用数据库实例名。例如，要和某一个数据库server连接，就必须知道其数据库实例名，只知道数据库名是没有用的，与数据库名不同，在数据安装或创建数据库之后，实例名可以被修改。数据库名与实例名之间的关系一般是一一对应关系，有一个数据库名就有一个实例名，如果在一个服务器中创建两个数据库，则有两个数据库名，两个数据库实例名，用两个标识确定一个数据库，用户和实例相连接。 但在8i、9i的并行服务器结构中，数据库与实例之间不存在一一对应关系，而是一对多关系，(一个数据库对应多个实例，同一时间内用户只一个实例相联系，当某一实例出现故障，其它实例自动服务，以保证数据库安全运行。)
Net service name：网络服务名，又可以称为数据库别名（database alias）。是客户端程序访问数据库时所需要，屏蔽了客户端如何连接到服务器端的细节，实现了数据库的位置透明的特性
各个参数的获取方法：
SHOW PARAMETER DB_NAME
show parameter domain;
show parameter service_name;
4、LINUX下完全卸载ORACLE 10G的方法
1. 运行 $ORACLE_HOME/bin/localconfig delete -->root
2. rm -rf $ORACLE_BASE/ --->oracle
3. rm -f /etc/oraInst.loc /etc/oratab --->root
4. rm -rf /etc/oracle --->root
5. rm -f /etc/inittab.cssd --->root
6. rm -f /usr/local/bin/coraenv /usr/local/bin/dbhome /usr/local/bin/oraenv --->root
7. 删除oracle用户和组。
userdel oracle
groupdel dba
groupdel oinstall
此方法同样适用与AIX系统，已经测试，其他平台没有验证过。
5、sql示例
SELECT last_name, salary, salary + 300 FROM employees;
SQL> select ename,empno,job,sal*2+100 "sal is" from emp;
SQL> select ename,sal from emp
SQL> where sal between 2000 and 5000; --在两个值之间 (包含边界)
SQL> select ename,sal,deptno,hiredate from emp
SQL> where deptno in (20,30) --等于值列表中的一个
SQL> order by sal desc; –-> ASC: 升序 –> DESC: 降序
SQL> select 'ul_'||lower(ename)||'_ok' namex from emp;连接符,函数
SQL> select 'ul_'||initcap(ename)||'_ok' namex from emp;
SQL> select ename,hiredate from emp
SQL> where hiredate > '01-jul-81';
SQL> select ename from emp
SQL> where comm is null; --空值
SQL> select (21+88)/2 from dual;
SQL> select avg(comm) from emp; -->算avg空值没有计算在内,应转换成0
SQL> select ename,job from emp
SQL> where sal<2500 and deptno in (10,20) and job not in('MANAGER','SALESMAN'); --列的别名:
SELECT last_name AS name, commission_pct comm FROM employees;
SELECT last_name "Name", salary*12 "Annual Salary" FROM employees;
--提取过滤重复行
在 SELECT 子句中使用关键字‘DISTINCT’过滤重复行。
SELECT DISTINCT department_id FROM employees;
--在硬盘上删除表内重复的行
SQL> delete from t1 where rowid> (select min(rowid) from t1 x where x.a=t1.a);
或： delete from emp where rowid not in (select min(rowid) from emp group by empno,ename...); --显然这句执行计划比上句cost大
--字符和日期
字符和日期要包含在单引号中。
字符大小写敏感，日期格式敏感。
默认的日期格式是 DD-MON-RR。
--LIKE
使用 LIKE 运算选择类似的值
选择条件可以包含字符或数字:
– % 代表零个或多个字符。
– _ 代表一个字符。
--优先级:
1 算术运算符
2 连接符
3 比较符
4 IS [NOT] NULL, LIKE, [NOT] IN
5 [NOT] BETWEEN
6 NOT
7 AND
8 OR
可以使用括号改变优先级顺序
SQL> select ename,job,sal from emp where job='SALESMAN' or job='PRESIDENT' and sal>4000;
使用括号控制执行顺序：
SQL> select ename,job,sal from emp where (job='SALESMAN' or job='PRESIDENT') and sal>4000;
6、单行函数：
返回一个结果
只对一行进程转换
可以嵌套
参数可以是一列或一个数值
字符函数 数值函数 日期函数 转换函数 通用函数
--字符函数->大小写转换函数
UPPER('UPLOO KING') ：全部转大写
LOWER('UPLOO KING') ：全部转小写
INITCAP('UPLOO KING') ：首字母大写
SQL> select UPPER('UPLOO king'),LOWER('UPLOO king'),INITCAP('UPLOO king') from dual;
UPPER('UPL LOWER('UPL INITCAP('U '
---------- ---------- ----------
UPLOO KING uploo king Uploo King
--字符控制函数->控制字符
concat(s1,s2) 字符串连接
substr(s,1,2) 截取子串,从第1个截，截2个
length(s) 字符串长度
instr(s,'a') 字节a在字符串中的位置
lpad(s,5,'a') 取前5位，长度不够5的 前 面加a
rpad(s,5,'a') 取前5位，长度不够5的 后 面加a
trim('h' from s)删除首部、尾部字符'h'
ltrim('abc','a')删除首部字符'a'
rtrim('abc','c')删除尾部字符'c'
replace(s,'a','b') 把'a'替换成'b'
SQL> select concat('s1','s2'),substr('uplook',1,2),length('uplook'),instr('uplook','loo') from dual;
CONC SU LENGTH('UPLOOK') INSTR('UPLOOK','LOO')
---- -- ---------------- ------------------------
s1s2 up 6 3
SQL> select lpad('S',5,'a'),rpad('S',5,'a'),trim('h' from 'hhsah'),replace('about','a','b') from dual;
LPAD( RPAD( TR REPLA
----- ----- -- -----
aaaaS Saaaa sa bbout
--数值函数
round(125.354657,-1) :四舍五入
trunc(123.354657,0) ：截断
mod(sal,100)：取余
ceil(12.1) => 13，ceil(-12.1) => -12 ：大于它的最小整数(向上取整)
floor(12.1) => 12，floor(-12.1) => -13 ：小于它的最大整数(向下取整)
abs(-2.9) ：绝对值
sign(n)：当n>0时 => 1; 当n=0时 => 0; 当n<0时 => -1;
SQL> select round(125.354657,-1),trunc(123.354657,0),mod(-982,100) from dual;
ROUND(125.354657,-1) TRUNC(123.354657,0) MOD(-982,100)
-------------------- ------------------- -------------
130 123 -82
SQL> select ceil(12.1),ceil(-12.1),floor(12.1),floor(-12.1),abs(-2.9) from dual;
CEIL(12.1) CEIL(-12.1) FLOOR(12.1) FLOOR(-12.1) ABS(-2.9)
---------- ----------- ----------- ------------ ----------
13 -12 12 -13 2.9
SQL> select abs(mod(dbms_random.random,100)) from dual; -->产生0－99的随机数
--日期函数
to_date('1999-09-23','yyyy-mm-dd') ：将字符转日期
to_char(hiredate,'fmDD MM RR') ：将日期转字符，fm是将前导零省略
to_char :
DD-MM-RR 02-08-10
YYYY 数字年份 MM 数字月 DD 数字日 D 一周的第几天
YEAR 英文年份 MONTH 英文月 DY 英文缩写 DDD 一年的第几天
MON 英文缩写 DAY 英文星期几
months_between(sysdate,hiredate) : 2个日期相差多少个月
next_day(sysdate,7) : 下一个周几(1~7)是哪天 7代表周六，6代表周五，..........
last_day(sysdate) :该月最后一天日期
add_months('20-FEB-81',6) : 加6个月
select sysdate,add_months(sysdate,3),next_day(sysdate,3),last_day(sysdate),months_between(sysdate,last_day(sysdate)) from dual;
SYSDATE ADD_MONTHS(SYSDATE, NEXT_DAY(SYSDATE,3) LAST_DAY(SYSDATE) MONTHS_BETWEEN(SYSDATE,LAST_DAY(SYSDATE))
------------------- ------------------- ------------------- ------------------- -----------------------------------------
2011-10-06 1:3:29 2012-01-06 1:3:29 2011-10-11 1:3:29 2011-10-31 1:3:29 -.80645161
对日期的进位和截取
select sysdate,trunc(sysdate,'mi'),trunc(sysdate,'hh'),trunc(sysdate,'dd'),trunc(sysdate,'mm'),trunc(sysdate,'yy') from dual;
SYSDATE TRUNC(SYSDATE,'MI') TRUNC(SYSDATE,'HH') TRUNC(SYSDATE,'DD') TRUNC(SYSDATE,'MM') TRUNC(SYSDATE,'YY')
------------------- ------------------- ------------------- ------------------- ------------------- -------------------
2011-10-06 1:39:54 2011-10-06 1:39:0 2011-10-06 1:0:0 2011-10-06 0:0:0 2011-10-01 0:0:0 2011-01-01 0:0:0
select sysdate,ROUND(sysdate,'mi'),ROUND(sysdate,'hh'),ROUND(sysdate,'dd'),ROUND(sysdate,'mm'),ROUND(sysdate,'yy') from dual;
SYSDATE ROUND(SYSDATE,'MI') ROUND(SYSDATE,'HH') ROUND(SYSDATE,'DD') ROUND(SYSDATE,'MM') ROUND(SYSDATE,'YY')
------------------- ------------------- ------------------- ------------------- ------------------- -------------------
2011-10-06 1:43:51 2011-10-06 1:44:0 2011-10-06 2:0:0 2011-10-06 0:0:0 2011-10-01 0:0:0 2012-01-01 0:0:0
29秒舍30入 29分舍30入 11点舍12入 15号舍16入 6月舍7入
ocm> select sysdate, trunc(sysdate,'day'),round(sysdate,'day') from dual;
SYSDATE TRUNC(SYSDATE,'DAY' ROUND(SYSDATE,'DAY'
------------------- ------------------- -------------------
2011-10-06 2:0:24 2011-10-02 0:0:0 2011-10-09 0:0:0
周3舍4入到下个周日
ocm> ho cal
October 2011
Su Mo Tu We Th Fr Sa
1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31
--嵌套函数：
单行函数可以嵌套
执行顺序由内到外
--通用函数：
适用于任何数据类型，同时也使用空值
NVL(s,'a') 字符串为空值则换成指定值'a'
NVL2(s,'a','b') 字符串为非空值返回'a',为空值返回'b'
NULLIF(s1,s2) 相等返回空，不等返回第一个值
COALESCE(s1,s2...) 返回第一个非空值
--条件表达式
两种方式:
case
decode
CASE表达式：
SQL> select ename,job,sal ,case job when 'CLERK' then sal*1.3
2 when 'SALESMAN' then 1.1*sal else sal end "xin_sal" from emp;
DECODE表达式：
SQL> SELECT ENAME,job,sal ,decode(job,'CLERK',1.3*SAL,'SALESMAN',1.1*SAL,'DBA',5*SAL,SAL) RE FROM EMP;
7、多行函数
--分组函数:
AVG
COUNT
MAX
MIN
SUM
统计各部门的平均薪资：
having
行已经被分组，在进行过滤
SQL> select deptno,avg(sal) from emp
2 group by deptno
3 having avg(sal)>800
4 order by avg(sal) desc;
--分析函数：
SQL> select rownum, t.ename,t.sal from ( select ename,sal from emp order by sal desc) t where rownum<11;
rank ----->返回一个唯一值，当碰到相同数据，此时所有相同数据的排名都是一样的, 1 2 3 3 3 6 7
dense_rank----->返回一个唯一值，当碰到相同数据，此时所有相同数据的排名都是一样的,1 2 3 3 3 4 5
row_number --->返回一个唯一值，当碰到相同数据，排名按照记录顺序依次递增, 1 2 3 4 5 6 7
SQL> select empno,ename,
2 rank() over(order by sum(sal) desc) rank,
3 dense_rank() over(order by sum(sal) desc) dense_rank,
4 row_number() over(order by sum(sal) desc) row_number
5 from emp
6 group by empno,ename;
EMPNO ENAME RANK DENSE_RANK ROW_NUMBER
---------- ---------- ---------- ---------- ----------
7839 KING 1 1 1
7788 SCOTT 2 2 2
7566 JONES 3 3 3
7698 BLAKE 4 4 4
7782 CLARK 5 5 5
7499 ALLEN 6 6 6
7654 MARTIN 7 7 7
7521 WARD 7 7 8 <--
7900 JAMES 9 8 9 <--
7369 SMITH 10 9 10
存在表T(a,b,c,d),要根据字段c排序后取第21—30条记录显示，请给出sql
SQL> select * from (select rownum oo, t2.* from (select * from t order by c desc ) t2 ) where oo between 21 and 30;
8、多表join查询
equijoin
non-equijoin
outer join
self join
cross join
natural join
full of two silded outer joins
--equijoin等值连接： （主，外键）
where写入连接条件
在表中相同的列时，在列名前加上表的前缀
n个表连接至少要有n-1个连接条件
请查出员工姓名，工号，职位，工作地点
SQL> select e.ename,e.job,e.empno,d.loc from emp e,dept d
where e.deptno=d.deptno;
--non-equijoin：
请查出员工姓名，薪水，所在薪资级别（scott,king,allen)
SQL> select e.ename,e.sal,s.grade from emp e,SALGRADE s
2 where e.sal between s.LOSAL and s.HISAL
3 and e.ename in ('SCOTT','KING','ALLEN');
--outer join:外连接
(+)
SQL> select e.ename,e.job,e.empno,d.loc,d.deptno from emp e,dept d
2 where e.deptno(+)=d.deptno;
请统计所有部门的人数,显示部门名称、部门人数.(注意是所有部门)
select d.dname,nvl(x.rs,0) from
(select deptno,count(*) rs from emp
group by deptno) x ,dept d
4 where x.deptno(+)=d.deptno;
--self join
问题：没有下级的被领导者（工号，姓名，薪水，mgr)
SQL> select e.empno,e.ename,e.sal,e.mgr from emp e
2 where e.empno not in
3 (select distinct mgr from emp e1 where e1.mgr is not null);
--natural join自然连接
SQL> select empno,ename,sal ,loc from emp natural join dept;
--使用using字句创建连接
在natural join字句中创建等值连接，可是使用using指定等值连接中需要的列
SQL> select empno,ename,sal ,loc from emp join dept using(deptno);
--使用on字句创建连接
自然连接是以具有相同列名为连接条件的
使用on字句指定额外的连接条件
SQL> select e.empno,e.ename,d.loc from emp e join dept d
2 on (e.deptno=d.deptno);
--外连接（左外连接，右外连接，满外连接)
SQL> select t1.a,t2.a from t1 left outer join t2
2 on (t1.a=t2.a);
SQL> select t1.a,t2.a from t1 right outer join t2
2 on (t1.a=t2.a);
SQL> select t1.a,t2.a from t2 full outer join t1
2 on (t1.a=t2.a);
9、set运算符
union all
union 去重
minus 补集
intersect 交集
SQL> select * from t2
2 minus <-- 查t1的补集
3 select * from t1;
出了union all,其他系统自动将重复记录删除
自动按着第一个查询中第一个列的升序排列
SQL> select * from (select * from t order by c desc ) x where rownum<31
2 minus
3 select * from (select * from t order by c desc ) x where rownum<21
4 order by 3 desc;
查询部门名称，部门员工数，部门平均工资，部门最低收入员工姓名，
select d.dname, xx.avgsal,e.ename,xx.rs
from (select deptno,count(ename) rs ,avg(sal) avgsal,min(sal) minsal from emp group by deptno) xx ,dept d ,emp e
where xx.deptno=d.deptno and e.sal=xx.minsal;
10、子查询
select list from table
where expr optertor(select list from table);
薪水比scott高的有哪些人，要求显示姓名
SQL> select ename from emp
2 where sal>
3 (select sal from emp where ename='SCOTT');
子查询（内查询）在主查询之前执行
子查询的结果被主查询使用
--单行子查询：
>
>=
<
<=
<>
!=
和allen在 一个部门的，同时薪水比工号7900高的，要求显示姓名，薪水，部门号
select ename,sal,deptno from emp
where deptno=(select deptno from emp where ename='ALLEN') and sal>(select sal from emp where empno=7900);
把公司薪资最低的人的详细信息给我：
select * from emp where sal=(select min(sal) from emp);
--多行子查询：
> all
< all
> any
< any
in
not in
比20号部门任何一个员工薪资高的，同时还是从事管理工作的并且85年前入职位的有哪些人？
SQL> select ename,job,sal from emp
2 where sal >any
3 (select sal from emp where deptno=20)
4 and job like 'MANA%'
5 and hiredate<to_date('1985-01-01','yyyy-mm-dd');
--高级子查询：
exists操作符
如果在子查询中存在满足的条件的行，返回true,
如果不存在，返回false,继续查找
dept
deptno,dname,有员工的部门的部门信息
SQL> select deptno,dname from dept
2 where deptno in
3 (select deptno from emp);
select distinct d.deptno,d.dname from dept d,emp e
2 where d.deptno(+)=e.deptno;
SQL> select deptno,dname from dept d
2 where exists ( <------
3 select 't' from emp e where e.deptno=d.deptno);
没有员工的部门的部门信息：
SQL> select deptno,dname from dept d
2 where not exists ( <------
3 select '7' from emp e where e.deptno=d.deptno);
请找出哪个部门的员工薪资大于2500，显示部门名称即可.
SQL> select d.dname from dept d
2 where exists (select 't' from emp e where e.deptno=d.deptno <----
3 and e.sal>2500);
请找出部门人数超过4人的部门里的人员名单，要求显示部门名称、显示姓名。
select tt.dname,e.ename from (select d.dname,xx.rs,d.deptno from dept d,
( select deptno,count(*) rs from emp group by deptno having count(*)>4 ) xx
where d.deptno=xx.deptno) tt ,emp e
where tt.deptno=e.deptno;
select d.dname,e.ename from emp e,dept d
where e.deptno=d.deptno and e.deptno in (select deptno from emp group by deptno having count(*)>4);
思想：
SQL> select d.dname from dept d
2 where exists (select deptno,count(*) from emp e where e.deptno=d.deptno
3 group by deptno having count(*)>4);
select t.dname,e.ename from (select d.dname ,d.deptno from dept d
where exists (select deptno,count(*) from emp e where e.deptno=d.deptno
group by deptno having count(*)>4) ) t ,emp e
where t.deptno=e.deptno;
找出比本部门平均薪资高的员工姓名和薪资以及部门名称和部门的平均薪资
select e.ename,e.sal,d.dname,a.avgs
from (select avg(sal) avgs,deptno from emp group by deptno ) a,emp e,dept d
where a.deptno=d.deptno and d.deptno=e.deptno and e.sal>a.avgs;
请找出公司级薪资的2～5名，要求显示员工姓名，薪资
SQL> SELECT * from (select ename,sal from emp order by sal desc) where rownum<6
2 minus
3 SELECT * from (select ename,sal from emp order by sal desc) where rownum<2
4 order by 2 desc;
列出工资高于公司平均工资的所有员工，所在部门，上级领导，所在的工资等级
select e.ename,d.dname,em.ename,s.grade from salgrade s,emp e,emp em,dept d
where e.sal>(select avg(sal) from emp )
and e.mgr=em.empno(+)
and e.sal between s.LOSAL and s.HISAL
and d.deptno=e.deptno;
显示每个人的姓名：部门薪资，占部门薪资100％，总薪资，占总薪资的100％
select e.ename,b.b_sal ,round(e.sal/b.b_sal,2)*100 b_s ,z.z_sal,round(e.sal/z.z_sal,2)*100)*100
z_s from
( select deptno,sum(sal) b_sal from emp group by deptno) b,
( select sum(sal) z_sal from emp) z ,
emp e
where e.deptno=b.deptno;---列长设置-----------
col name format a10;
col ip format a20;
set line 1600;
----------------------group by 扩展：
rollup操作符: 产生n+1分组结果,n指roolup后面几列
SQL> select deptno,job,sum(sal) from emp
group by rollup(deptno,job); <-- 注意顺序不同，出来结果不一样
cube操作符：
对group by 扩展
类似于笛卡尔集的分组结果 n的2次方
SQL> select deptno,job,sum(sal) from emp
group by cube(deptno,job);
--分级查询：
start with column =value
遍历数
connect by prior <>
start with ename='Huold'
connect by prior empno=mgr <--Huold的empno=别人的mgr
从底遍历到顶
SQL> select empno,ename,job,mgr from emp
2 start with ename='SCOTT'
3 connect by prior mgr=empno;
从顶遍历到底
SQL> select empno,ename,job,mgr from emp
2 start with ename='KING'
3 connect by prior EMPNO=MGR;
11、SQL语言
DQL---->select
DML---->insert delete update ---->可以rollback,commit
DDL---->create alter drop truncate rename
DCL---->commit rollback grant revoke lock
--DQL数据查询语言---->select
select [column,] group_function(column), ...
from table
[where condition]
[group by column having ...] ->若select后有3列,1列使用组函数,其余2列必须出现在group by后面
[order by column];
--DML数据操纵语言---->insert, delete, update -->可以rollback,commit
<insert>
insert into dept values(50,'IT',null); ->当不指定列时候，那么要按着顺序依次赋值
insert into emp (EMPNO,ENAME) values(100,'asdfa'); ->其他未赋值的列为空
<delete>
delete from table_name where ... ;
<update>
update dept set loc=default where ...;
--DCL数据控制语言---->commit, rollback, grant, revoke, lock
commit; 提交
rollback; 回滚
grant connect,resource to tom;
revoke resource from tom;
--DDL数据定义语言---->create, alter, drop, truncate, rename
<create> (必须具备："create table"权限,存储空间) 建表的表名1~30个，以字母开头，1~9，A~z,_,$,#,不能是oracle保留字
create table tbname(a int,b varchar2(20),c date) tablespace ...;
create table tbname as select * from ...;
<drop>
drop table tbname;
<alter>
alter table tbname add(d char(8));
alter table tbname drop column d; ->不能删除只有一个列的表的列
alter table tbname modify(a number);
alter table tbname rename column a to b;
生产环境中删除某列：
alter table tbname set unused (sal);
alter table tbname drop unused columns;
<rename> 改变表, 列,视图 或同义词的名称
rename emp to empa;
<truncate>
truncate table tbname; 截断表，高水位线下调
--多表insert ：可使用一个DML 语句向多个表中插入数据
create table sal_his as select empno empid ,hiredate,sal from emp where 0=1;
create table mgr_his as select empno empid,mgr,sal from emp where 0=1;
无条件的insert：
insert all
into sal_his values(EMPID,HIREDATE,sal)
into mgr_his values(EMPID,MGR,sal)
select empno empid ,hiredate,sal,mgr from emp
where empno>200;
有条件的all insert：
insert all
when sal>3000 then
into sal_his values(empid,hiredate,sal)
when mgr>200 then
into mgr_his values(empid,sal,mgr)
select empno empid,hiredate,sal,mgr from emp;
有条件的first insert :
SQL> create table hire_his_00 as select hiredate from emp where 0=1;
SQL> create table hire_his_80 as select hiredate from emp where 0=1;
SQL> create table sal_his as select deptno deptid,hiredate,sal from emp where 0=1;
SQL> insert first
2 when hiredate like ('%00%') then
3 into hire_his_00 values(hiredate)
4 when hiredate like ('%8%') then
5 into hire_his_80 values(hiredate)
6 else
7 into sal_his values(deptid,hiredate,sal)
8 select deptno deptid,sum(sal) sal,max(hiredate) hiredate
9 from scott.emp
10 group by deptno;
--数据类型：
varchar2(20) 可变长字符类型,1~4000
char[(20)] 定长字符类型,1~2000,没指定时默认为1
number[(p,s)] 可变长数值类型，精度为p,小数位为s。1<=p<=38,-84<=s<=127
date 日期型
long 可变长字符类型，最高达到2GB
clob 字符类型 最大可达到4GB
blob 二进制数据， 最大可达到4GB
bfile 存储在外部文件的二进制的数据，最大可达到4G
raw(size) 二进制数，最大2000位
rowid 64位基本编号系统
integer number(p,0)
--oracle数据库的表：
用户定义的表：用户自己创建并维护的一组表，包含了用户所需的信息
数据字典：由oracle server创建和维护的一组表，包含数据库信息
查询数据字典：
select table_name from user_tables ; 查看用户定义的表
select distinct object_type from user_objects ; 查看用户定义的各种数据库对象
select * from user_catalog ; 查看用户定义的表,视图,同义词和序列
12、约束，视图，权限
--constraint : 约束,表级别的强制规定(not null, unique, primary key, foreign key, check)
在建立表的同时建约束：
create table tb1(x int not null); 非空,如果不指定约束名称，会自动按照SYS_Cn的格式命名约束
create table tb2(y int,constraints uni unique(y) ); 惟一,指定约束名
create table tb5(d int,check(d>20) );
create table tb3(a number,b int primary key );主键,非空且惟一
create table tb4(a int,b int,constraints con3 foreign key(b) references tb3(b));外键,必须用命名方式
->tb3(主)表的主键是tb4(子)表的外键，外键有的记录主键必须有
在已经创建的表上:
alter table tb5 add constraint con4 unique(d); 添加约束
alter table tb5 drop constraint con4; 删除约束
alter table tb2 disable constraint uni; 禁用约束
alter table tb2 enable constraint uni; 使用约束
通过字典查看约束:
select constraint_name,constraint_type,search_condition,table_name from user_constraints ;
--view : 视图,虚表，从表中抽出逻辑上相关的数据集合，控制数据访问，数据独立性，避免重复性操作
create view vv1 as select ...;
drop view vv1; ->删除视图，基表不受影响
--权限：
系统权限：针对数据库的
开发人员，除了connect,resource以外dba一般还会给以下系统权限：
create session（创建会话）
create table（创建表）
create sequence（创建序列）
create view（创建视图）
create procedure（创建过程）
grant create session ,create table to tom with admin option; --->分配权限，针对系统权限
对象权限：操作数据库对象（表，列，视图...）权限, 对象拥有者拥有所有权限,对象拥有者可以向外分配权限
select on scott.emp
insert,delete,update(sal,ename) on scott.emp
grant select,insert on scott.emp to tom with grant option; --->分配权限，针对对象权限
revoke all on scott.emp from tom ;
role: 角色,可将权限授予角色，再将角色授予多个用户
create role manager;
grant create table,create view,connect to manager;
grant manager to tom,lydia;
grant dba to tom,lydia; dba 是个角色可授予用户
通过字典查看权限：
role_sys_privs 角色拥有的系统权限
role_tab_privs 角色拥有的对象权限
user_role_privs 用户拥有的角色
user_sys_privs 用户拥有的系统权限
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
