6.7.4.3.2 6.7.4.3.3
UPDATE中 DELETE中
6.7.4.4 在DDL语句中使用子查询 6.7.4.4.1
在CREATE TABLE语句中使用子查询
可以在建立新表的同时复制表数据
如：CREATE TABLE new_emp(id,name,sal,job,deptno) AS SELECT empno,ename,sal,job,deptno FROM emp;
6.7.4.4.2 在CREATE VIEW语句中使用子查询
CREATE OR REPLACE VIEW dept_10 AS SELECT empno,ename, job,sal,deptno FROM emp WHERE deptno=10 ORDER BY empno
6.8 使用集合操作符
为了合并多个SELECT语句的结果，可以使用集合操作符UNION、UNION ALL，语法如下： SELECT 语句1
[UNION | UNION ALL] SELECT 语句2 ? UNION
用于取得两个结果集的并集，自动去掉结果集中的重复行。 如：SELECT ename,sal,job FROM emp WHERE sal>2500 UNION SELECT ename,sal,job FROM emp WHERE job=‘MANAGER’ ? UNION ALL
用于取得两个结果集的并集，与UNION操作符不同，该操作符不会取消重复值
7 常用SQL函数
SQL函数是Oracle数据库的内置函数，并且可以在各种SQL语句中使用。当单独调用SQL函数时，可以使用数据字典DUAL，该数据字典专门用于取得函数返回值。SQL函数包括单行函数和多行函数，其中单行函数是指输入一行输出一行的函数；而多行函数是指输入多行输出一行的函数。按照处理功能分类，
26
SQL函数分为数字函数，字符函数，日期时间函数，转换函数及其它类型函数。 1. ASCII
返回与指定的字符对应的十进制数;
SQL> select ascii(‘A’) A,ascii(‘a’) a,ascii(‘0’) zero,ascii(‘ ‘) space from dual; 2. CHR
给出整数,返回对应的字符;
SQL> select chr(54740) zhao,chr(65) chr65 from dual; 3. CONCAT 等价与||,推荐CONCAT 连接两个字符串;
SQL> select concat(‘010-’,’88888888’)||’转23’ 联系电话 from dual; 4. INITCAP
返回字符串并将字符串的第一个字母变为大写; SQL> select initcap(‘smith’) upp from dual; 5. INSTR(C1,C2[,I[,J]])和INSTRB(C1,C2[,I[,J]])
INSTR(X,Y[,Z]) 返回Y在X中的位置，如沒有则为0,如有Z则从Z开始找到的Y在X中的位置 INSTRB(X,Y) 返回Y在X中位置，如沒有则为0，对于单字节字符系统，返回的值以字节为单位 SELECT INSTR(‘AABBABCAB’,’AB’) from dual; SELECT INSTR(‘AABBASCAB’,’AB’,4) from dual; SELECT INSTR(‘AAB我BASCAB’,’AB’,4) from dual; SELECT INSTRB(‘AAB我BASCAB’,’AB’,4) from dual; INSTR(C1,C2[,I[,J]])和INSTRB(C1,C2[,I[,J]])
在一个字符串中搜索指定的字符,返回发现指定的字符的位置; C1 被搜索的字符串 C2 希望搜索的字符串 I 搜索的开始位置,默认为1 J 子串的第J次出现的位置,默认为1
SQL> select instr(‘oracle traning’,’ra’,1,2) instring from dual; 6. LENGTH和LENGTHB
返回字符串的长度; LENGTHB按照字节进行返回
SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from
27
gao.nchar_tst;
select length(‘oracle traning’) from dual; select length(‘oracle traning我’) from dual; select lengthb(‘oracle traning我’) from dual; 7. LOWER
返回字符串,并将所有的字符小写
SQL> select lower(‘AaBbCcDd’)AaBbCcDd from dual; 8. UPPER
返回字符串,并将所有的字符大写
SQL> select upper(‘AaBbCcDd’) upper from dual; 9. RPAD和LPAD(粘贴字符) 字符串填充 RPAD 在列的右边粘贴字符 LPAD 在列的左边粘贴字符
LPAD(char1,n,char2):在字符串char1的左端填充字符串char2，直至字符串总长度为n,char2的默认值为空格，如果char1长度大于n，则该函数返回char1左端的前n个字符。如果输入参数值存在NULL，则返回NULL
SQL> select lpad(rpad(‘gao’,10,’*’),17,’*’)from dual; 10.
LTRIM和RTRIM
LTRIM 删除左边出现的字符串 RTRIM 删除右边出现的字符串
LTRIM(char1[,set])：去掉char1左端所包含的set中的任何字符。Oracle从左端第一个字符开始扫描，逐一去掉在set中出现的字符，当遇到不是set中的字符时终止，然后返回剩余结果。
SQL> select ltrim(rtrim(‘ gao qian jing ‘,’ ‘),’ ‘) from dual; 11.
SUBSTR(string,start[,count])和SUBSTRB(string,start[,count])
取子字符串,从start开始,取count个。如果m为0，则从首字符开始；如果m是负数，则从尾部开始。
SQL> select substr(‘13088888888’,3,8) from dual; select substr(‘13088888888’,-10) from dual; 12.
REPLACE(‘string’,’s1’,’s2’)
string 用于指定字符串
28
s1 用于指定要被替换的子串 s2 用于指定替换后的子串
如果s1为NULL，则返回原有字符串，如果s2为NULL，则会去掉指定子串。 SQL> select replace(‘he love you’,’he’,’i’) from dual; select replace(‘haiyi’,’yi’,’yisoft’) from dual; 13.
TRIM({trim_char|LEADING trim_char|TRAILING trim_char|BOTH trim_char} FROM
trim_source);
用于从字符串中截断特定字符。参数trim_char用于指定要截去的字符，参数trim_source用于指定源字符串，LEADING用于指定截去头部字符，TRAILING用于指定截去尾部字符，BOTH用于指定截去头部和尾部字符（默认选项）
Select TRIM(‘s’ from ‘string’) from DUAL;――tring select trim(‘a’ from ‘ahaiyi’) from dual; select trim(‘i’ from ‘ahaiyi’) from dual; select trim(‘a’ from ‘ahaiyia’) from dual; select ltrim(‘ soft’) from dual; select rtrim(‘ soft ‘) from dual; 在营销系统中，多应用在去除空格。 14.
ABS(n)：用于返回数字n的绝对值，如果输入为NULL，则返回值也是NULL
返回指定值的绝对值
SQL> select abs(100),abs(-100) from dual; 15.
CEIL(n)
返回大于等于数字n的最小整数，若输入NULL，则返回为NULL SQL> select ceil(3.1415927) from dual;--4 select ceil(3) from dual;--3
16.
FLOOR
返回小于等于数字n的最大整数，若输入NULL，则返回NULL SQL> select floor(2345.67) from dual;--2345 17.
MOD(n1,n2)
返回一个n1除以n2的余数
SQL> select mod(10,3),mod(3,3),mod(2,3) from dual;
29
18. POWER(n1,n2)
返回n1的n2次方根
SQL> select power(2,10),power(3,3) from dual; 19.
ROUND和TRUNC(x,m,n)
ROUND(n[,m])：返回四舍五入，其中n可为任意数字，m必须为整数。如果省略m，则四舍五入到整数位；如果m是负数，则四舍五入到小数点前的第m位；如果m为正数，那么四舍五入到小数点后的第m位。若输入NULL，则输出NULL
TRUNC(n[,m])：该函数用于截取数字，其中n可以是任意数字，m必须是整数。若m省略，则会将数字n的小数部分截去；如果数字m是正数，那么会将数字n截取至小数点后的第m位；如果数字m是负数，那么会将数字n截取至小数点前的第m位。 SQL> Select
round(55.5),round(55.5,2),round(55.543,2),round(-55.4),trunc(55.5),trunc(55.5,1),trunc(-55.5),trunc(55.523,-1),trunc(155.523,-1) from dual; 20.
SIGN(n) 符号函数
用于检测数字的正负。如果数字n小于0，则函数的返回值为－1；如果数字n等于0，则返回0；如果大于0，则函数的返回值为1.若输入值为NULL，则返回NULL
SQL> select sign(123),sign(-100),sign(0) from dual; 21.
SQRT(n)
返回数字n的平方根，数字n必须大于等于0，输入NULL，则输出也为NULL SQL> select sqrt(64),sqrt(10) from dual; 22.
TRUNC
按照指定的精度截取一个数
SQL> select trunc(124.1666,-1) ,trunc(124.1666,-2) ,trunc(124.16666,2) from dual; 23.
ADD_MONTHS(d,n) 在日期上加指定的月数
返回特定日期时间之后或之前的几个月所对应的日期时间。
ADD_MONTHS(date,months) 其中： date 一个日期数值 months 要加上的月份数。如果要减去的月份数用负数
SQL> select to_char(add_months(to_date(‘199912’,’yyyymm’),2),’yyyymm’) from dual; SQL> select to_char(add_months(to_date(‘199912’,’yyyymm’),-2),’yyyymm’) from dual; select ADD_MONTHS(to_date(‘2011-01-01’,’yyyy-mm-dd’),3) from dual
30