oracle01_sql
来源：互联网 发布：软件实施工程师要求 编辑：程序博客网 时间：2024/06/01 20:36
1、 oracle安装
资源需要：
至少1024 MB物理内存
需1.5倍物理内存的交互空间
至少400 MB /tmp 临时目录空间
oracle软件需要1.5 GB 到 3.5 GB 磁盘空间
默认数据库需要1.2 GB
查看系统资源相关语句：
cat /etc/issue
uname -r
grep MemTotal /proc/meminfo
grep SwapTotal /proc/meminfo
grep "model name" /proc/cpuinfo
free
df -k /tmp
安装前的检查和准备工作：
安装 libaio-0.3.102-1.i386.rpm 和libaio-devel0.3.102-1.i386.rpm
在Red Hat Enterprise Linux 介质的第三张 CD
以 root 用户身份运行以下命令：
rpm -ivh /mnt/cdrom/RedHat/RPMS/ libaio-0.3.102-1.i386.rpm
rpm -qa |grep libXp
创建数据库安装的准备工作:
1，创建user/group；
groupadd dba
groupadd oinstall
useradd oracle -g oinstall -G dba
(初始组为 oinstall, 附加组 dba 一个是控制软件安装，补丁安装等的；另一个是控制数据库创建，数据库管理等的。可以将两个权限都授权给dba组，只创建dba一个组就可以了)
passwd oracle
如果nobody用户不存在（id nobody命令查看)，则创建：
useradd nobody
( 首先nobody是一个普通用户，非特权用户。 使用nobody用户名的目的是，使任何人都可以登录系统，但是其UID和GID不提供任何特权，即该uid和gid只能访问人人皆可读写的文件。其次，许多系统中都按惯例地默认创建一个nobody，尽量限制它的权限至最小，当服务器向外服务时，可能会让client以nobody的身份登录)
2，建立oracle安装文件夹(sample)；
mkdir -p /u01/oracle/product/10g
mkdir /u01/oracle/database
chown -R oracle.oinstall /u01/oracle
chmod 755 -R /u01/oracle
3， 配置环境变量；
要使用 Oracle 产品，应该或必须设置几个环境变量。
如果您在同一服务器上安装了多个 Oracle 产品或数据库，则 ORACLE_HOME、ORACLE_SID 和 PATH 变量可能会更改。
ORACLE_BASE 变量不应更改，并可以在需要时在您的登录配置文件中设置它。Oracle 提供了一个称作 oraenv 的实用程序来设置其他变量。
对于数据库服务器，建议设置以下环境变量：
使用Oracle用户登陆:
su - oracle
vi ~/.bash_profile
以下是配置文件的内容
export ORACLE_BASE=/opt/oracle/
export ORACLE_HOME=/opt/oracle/product/10g
export ORACLE_SID=ge01
export PATH=$ORACLE_HOME/bin:$PATH
配置好后用 source .bash_profile 命令使配置生效
4， 设置系统参数；
Oracle 数据库 10g 需要以下所示的内核参数设置。
其中给出的是最小值，因此如果您的系统使用的值较大，则不要更改它。
su - root
A) 修改/etc/sysctl.conf(vi /etc/sysctl.conf), 添加：
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144
net.core.rmem_max=262144
net.core.wmem_default=262144
net.core.wmem_max=262144
修改后运行"/sbin/sysctl -p"命令使得内核改变立即生效；
对每个参数值做个简要的解释和说明。
（1）shmmax：该参数定义了共享内存段的最大尺寸（以字节为单位）。缺省为32M，对于oracle来说，该缺省值太低了，通常将其设置为2G。
（2）shmmni：这个内核参数用于设置系统范围内共享内存段的最大数量。该参数的默认值是 4096 。通常不需要更改。
（3）shmall：该参数表示系统一次可以使用的共享内存总量（以页为单位）。缺省值就是2097152，通常不需要修改。
（4）sem：该参数表示设置的信号量。
（5）file-max：该参数表示文件句柄的最大数量。文件句柄设置表示在linux系统中可以打开的文件数量。
B) 设置oracle对文件的要求：
编辑文件：vi /etc/security/limits.conf 加入以下语句：
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
limits.conf的格式如下：
username|@groupname type resource limit
username|@groupname：设置需要被限制的用户名，组名前面加@和用户名区别。也可以用通配符*来做所有用户的限制。
type：有 soft，hard 和 -，soft 指的是当前系统生效的设置值。hard 表明系统中所能设定的最大值。soft 的限制不能比hard 限制高。用 - 就表明同时设置了 soft 和 hard 的值。
resource：
core - 限制内核文件的大小
date - 最大数据大小
fsize - 最大文件大小
memlock - 最大锁定内存地址空间
nofile - 打开文件的最大数目
rss - 最大持久设置大小
stack - 最大栈大小
cpu - 以分钟为单位的最多 CPU 时间
noproc - 进程的最大数目
as - 地址空间限制
maxlogins - 此用户允许登录的最大数目
编辑文件：vi /etc/pam.d/login linux操作系统的登陆配置文件。
session required /lib/security/pam_limits.so
session required /lib/security/pam_limits.so
这是告诉Linux在用户完成系统登录后，应该调用pam_limits.so模块来设置系统对该用户可使用的各种资源数量的最大限制(包括用户可打开的最大文件数限制)，而pam_limits.so模块就会从/etc/security/limits.conf文件中读取配置来设置这些限制值。修改完后保存此文件
解压缩Oracle10G的安装文件 ：
1． Oracle
(1) 简化过程（以oracle登录，释放安装文件,使用更少的磁盘空间，速度更快）
在/tmp下解压缩10201_database_linux32.zip:
unzip 10201_database_linux32
开始安装oracle
（一）开始安装：
xhost +
export LANG=en
1.以oracle用户登录系统，进行Oracle的安装：
cd /tmp/database (或者你解压缩安装程序包的目录）
./runInstaller
过一会儿就会出现Oracle的安装界面
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 /nologSQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 30 14:42:39 2010Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL> show userUSER is ""SQL> connect system/oracleConnected.SQL> show userUSER is "SYSTEM"SQL> connect sys as sysdbaEnter password: ******Connected.[oracle@emrep bin]$ sqlplus / as sysdba[oracle@emrep bin]$ sqlplus system/oracleSQL> select username from dba_users;SQL> alter user scott identified by tiger account unlock;---->修改scott用户的密码同时解锁User altered.SQL> conn scott/tigerConnected.SQL> show userUSER is "SCOTT "SQL> select * from tab;TNAME TABTYPE CLUSTERID------------------------------ ------- ----------DEPT TABLEEMP TABLEBONUS TABLESALGRADE TABLE--------------------------------SQL> @?/rdbms/admin/utlsaml.sql -->重建scott用户以及所属的table, @ 表示运行脚本， ? 是代替 $ORACLE_HOME--------------------------------
3、不同的名称
SQL> show parameter db_nameNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_name string lijh ----->数据库名字SQL> show parameter instance_nameNAME TYPE VALUE------------------------------------ ----------- ------------------------------instance_name string lijh ------>实例名称SQL> show parameter db_uniNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_unique_name string lijh ----->数据库唯一名称SQL> show parameter service_naNAME TYPE VALUE------------------------------------ ----------- ------------------------------service_names string lijh SQL> show parameter global NAME TYPE VALUE------------------------------------ ----------- ------------------------------global_context_pool_size stringglobal_names boolean FALSESQL> 概念：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_NAMEshow 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 empSQL> where sal between 2000 and 5000; --在两个值之间 (包含边界) SQL> select ename,sal,deptno,hiredate from empSQL> 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 empSQL> where hiredate > '01-jul-81';SQL> select ename from empSQL> where comm is null; --空值SQL> select (21+88)/2 from dual;SQL> select avg(comm) from emp; -->算avg空值没有计算在内,应转换成0SQL> select ename,job from empSQL> 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] IN5 [NOT] BETWEEN6 NOT7 AND8 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的 前 面加arpad(s,5,'a') 取前5位，长度不够5的 后 面加atrim('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-10YYYY 数字年份 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 12 3 4 5 6 7 89 10 11 12 13 14 1516 17 18 19 20 21 2223 24 25 26 27 28 2930 31 --嵌套函数：单行函数可以嵌套执行顺序由内到外 --通用函数：适用于任何数据类型，同时也使用空值 NVL(s,'a') 字符串为空值则换成指定值'a'NVL2(s,'a','b') 字符串为非空值返回'a',为空值返回'b'NULLIF(s1,s2) 相等返回空，不等返回第一个值COALESCE(s1,s2...) 返回第一个非空值 --条件表达式两种方式:casedecode 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、多行函数
--分组函数:AVGCOUNTMAXMINSUM统计各部门的平均薪资：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 7dense_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条记录显示，请给出sqlSQL> select * from (select rownum oo, t2.* from (select * from t order by c desc ) t2 ) where oo between 21 and 30;
8、多表join查询
equijoinnon-equijoinouter joinself joincross joinnatural joinfull of two silded outer joins--equijoin等值连接： （主，外键）where写入连接条件在表中相同的列时，在列名前加上表的前缀n个表连接至少要有n-1个连接条件请查出员工姓名，工号，职位，工作地点SQL> select e.ename,e.job,e.empno,d.loc from emp e,dept dwhere 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 allunion去重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 ewhere 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 ewhere 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 dwhere exists (select deptno,count(*) from emp e where e.deptno=d.deptnogroup 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 dwhere 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 dwhere e.sal>(select avg(sal) from emp )and e.mgr=em.empno(+) and e.sal between s.LOSAL and s.HISALand 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)*100z_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.empinsert,delete,update(sal,ename) on scott.empgrant 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 用户拥有的系统权限
