Oracle9i第5章SQL基础.ppt

第四章第四章 SQL SQL基础基础SELECTSELECT语句语句SELECT SELECT 的简单语法：的简单语法：SELECT DISTINCT|ALL*|column1,column2.SELECT DISTINCT|ALL*|column1,column2.FROM table_1|(subquery)aliasFROM table_1|(subquery)alias,table_2|(subquery)alias.,table_2|(subquery)alias.WHERE conditionWHERE conditionCONNECT BY condition START WITH conditionCONNECT BY condition START WITH conditionGROUP BY expn HAVING expnGROUP BY expn HAVING expn UNION ALL|INTERSECT|MINUS SELECT.UNION ALL|INTERSECT|MINUS SELECT.ORDER BY expn ASC|DESC ORDER BY expn ASC|DESC FOR UPDATE OF user.table|view column FOR UPDATE OF user.table|view column NOWAITNOWAIT简单简单SELECTSELECT查询查询select deptno,dname,loc from dept;select deptno,dname,loc from dept;select deptno,dname,loc from dept order by dname;select deptno,dname,loc from dept order by dname;select*from dept order by deptno desc,dname;select*from dept order by deptno desc,dname;select*from dept order by dname,deptno asc;select*from dept order by dname,deptno asc;select*from dept where dname like%SA%order by select*from dept where dname like%SA%order by deptno desc,dname;deptno desc,dname;注意：注意：n在在SQLSQL语句中，引用的字符串必须在单引号内，而不是双引号。语句中，引用的字符串必须在单引号内，而不是双引号。n如果需要在字符串内放如果需要在字符串内放“”号，应该写两次。比如语句号，应该写两次。比如语句“select select*from dept where dname=SAES*from dept where dname=SAES”中的中的“SAESSAES”字符字符就是如此。就是如此。n要注意空值的表示方法是要注意空值的表示方法是IS NULLIS NULL或或IS NOT NULLIS NOT NULL。n在在OracleOracle中使用中使用%来匹配任何字符串使用来匹配任何字符串使用_ _来匹配任何字符。来匹配任何字符。汇总数据汇总数据select count(*)from dept;select count(*)from dept;select deptno,count(*)from emp group by deptno;select deptno,count(*)from emp group by deptno;将返回工资总额小于将返回工资总额小于1000010000的部门和实际工资总额的部门和实际工资总额select deptno,sum(sal)from emp group by deptno select deptno,sum(sal)from emp group by deptno having sum(sal)10000;having sum(sal)10000;select count(distinct deptno)from emp;select count(distinct deptno)from emp;内连接内连接select a.empno,a.ename,a.job,b.dname select a.empno,a.ename,a.job,b.dname from emp a,dept b from emp a,dept b where a.deptno=b.deptno;where a.deptno=b.deptno;外连接外连接外部关联关联了两个表并使得即使第二个表没有与第一个表符外部关联关联了两个表并使得即使第二个表没有与第一个表符合的记录也能返回结果。合的记录也能返回结果。select dept.deptno,emp.ename,bonus.sal,m select dept.deptno,emp.ename,bonus.sal,m from emp,dept,bonus where emp.deptno(+)=dept.deptno from emp,dept,bonus where emp.deptno(+)=dept.deptno and bonus.ename(+)=emp.ename;and bonus.ename(+)=emp.ename;注意：注意：n表可以外部连接到至多一个其它的表表可以外部连接到至多一个其它的表nOracleOracle是使用的是是使用的是(+)(+)号作为外连接标识号作为外连接标识交叉连接交叉连接n交叉连接不带交叉连接不带WHEREWHERE子句，它返回被连接的两个表所有数子句，它返回被连接的两个表所有数据行的笛卡尔积，返回到结果集合中的数据行数等于第一据行的笛卡尔积，返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。条件的数据行数。n在表中有数据的情况下：在表中有数据的情况下：select ycit.*,ycit1.*from ycit,ycit1;select ycit.*,ycit1.*from ycit,ycit1;select deptno,ename from emp where deptno in(select select deptno,ename from emp where deptno in(select deptno from dept);deptno from dept);select deptno,ename from emp a where exists select deptno,ename from emp a where exists(select loc from dept b where(select loc from dept b where a.deptno=b.deptno);a.deptno=b.deptno);连接查询连接查询比如有两张表比如有两张表empemp和和log_emplog_emp，其中，其中empemp中保存的是现行编中保存的是现行编制员工的编号和姓名，制员工的编号和姓名，log_emplog_emp中保存着已经退休员工的中保存着已经退休员工的编号和姓名。如果要查询所有员工信息，可以执行下面的编号和姓名。如果要查询所有员工信息，可以执行下面的操作：操作：select empno,ename from emp select empno,ename from emp union union select empno,ename from log_emp;select empno,ename from log_emp;使用空值使用空值空值是三值逻辑，以下列语句为例：空值是三值逻辑，以下列语句为例：select*from emp where deptno=10 and deptno 10select*from emp where deptno=10 and deptno 10等同于等同于select*from emp where deptno is not nullselect*from emp where deptno is not null插入新数据插入新数据向表中插入行的一般用法：向表中插入行的一般用法：insert into tablename(column1,column2,column3insert into tablename(column1,column2,column3，)values(value1,value2,value3,)values(value1,value2,value3,););注意：注意：只要值的列表和表的列名精确匹配，就可以省略列全部列表。只要值的列表和表的列名精确匹配，就可以省略列全部列表。如，如果向如，如果向deptdept表中增加一个新的部门，可以通过下面语句：表中增加一个新的部门，可以通过下面语句：insert into dept(deptno,dname,loc)values(50,insert into dept(deptno,dname,loc)values(50,System,Nanjing);System,Nanjing);插入一个查询结果插入一个查询结果insert into tablename(column1,column2,column3insert into tablename(column1,column2,column3，)subquery;)subquery;insert into bonus insert into bonus select ename,job,sal,comm from emp;select ename,job,sal,comm from emp;在在INSERTINSERT语句中没有给出列的列表，意味着在语句中没有给出列的列表，意味着在BONUSBONUS表中表中的全部列都必须和的全部列都必须和SELECTSELECT查询中的对应列的排列次序相同。查询中的对应列的排列次序相同。更新数据更新数据UPDATE tablenameUPDATE tablenameSET column1=value1,SET column1=value1,Column2=values2,Column2=values2,WHERE condition;WHERE condition;update dept set loc=Shanghai where deptno=50;update dept set loc=Shanghai where deptno=50;使用基于子查询的更新使用基于子查询的更新UPDATE tablenameUPDATE tablenameSET(column1,column2,SET(column1,column2,)=)=(SELECT column3,column4,(SELECT column3,column4,FROM tablename2FROM tablename2WHERE conditions1)WHERE conditions1)WHERE conditions2;WHERE conditions2;update bonus update bonus set(job,sal,comm)=(select job,sal,comm set(job,sal,comm)=(select job,sal,comm from emp from emp where empno=7788)where empno=7788)where ename=SCOTT;where ename=SCOTT;n注意：注意：无论什么时候在更新语句的无论什么时候在更新语句的SELECTSELECT子句中使用子查询时，都子句中使用子查询时，都必须确认子查询确实返回了一行。必须确认子查询确实返回了一行。如果子查询没有返回行，那么所有被更新的列都将被置为空。如果子查询没有返回行，那么所有被更新的列都将被置为空。如果子查询返回的不止一行，如果子查询返回的不止一行，OracleOracle将返回一个错误信息。将返回一个错误信息。删除数据删除数据使用使用DELETEDELETE命令从表中删除行。命令的基本形式如下所示：命令从表中删除行。命令的基本形式如下所示：DELETE FROM tablename WHERE condition;DELETE FROM tablename WHERE condition;delete from log_emp where deptno=10;delete from log_emp where deptno=10;TRUNCATE tablenameTRUNCATE tablenameSQLSQL中的单记录函数中的单记录函数nOracleOracle的的SQLSQL语句中用到的函数分为单值函数和多值函数，语句中用到的函数分为单值函数和多值函数，单值函数又分为字符函数和数字函数。单值函数又分为字符函数和数字函数。单记录字符函数单记录字符函数ASCII()ASCII()是字符串。返回与指定的字符对应的十进制数。是字符串。返回与指定的字符对应的十进制数。select ascii(A)A,ascii(a)a,ascii(0)select ascii(A)A,ascii(a)a,ascii(0)zero,ascii()space from dual;zero,ascii()space from dual;select ascii(select ascii(赵赵)zhao,length()zhao,length(赵赵)leng from)leng from dual;dual;CHR(NCHAR)CHR(NCHAR)给出整数，返回对应字符。给出整数，返回对应字符。select chr(54740)zhao,chr(65)chr65 from dual;select chr(54740)zhao,chr(65)chr65 from dual;CONCAT(,)CONCAT(,)select concat(select concat(010-010-,8801 81598801 8159)|)|转转2323 赵元杰电话赵元杰电话 from dual;from dual;INITCAP()INITCAP()返回字符串返回字符串c1 c1 并第一个字母变为大写。并第一个字母变为大写。select initcap(simth)upp from dual;select initcap(simth)upp from dual;INSTR(,)INSTR(,)在一个字符串中搜索指定的字符，返回发现指定的字符的在一个字符串中搜索指定的字符，返回发现指定的字符的位置。位置。C1:C1:被搜索的字符串被搜索的字符串C2:C2:希望搜索的字符串希望搜索的字符串I:I:搜索的开始位置，缺省是搜索的开始位置，缺省是1 1J:J:出现的位置，缺省是出现的位置，缺省是1 1。SELECT INSTR(Oracle Training,ra,1,2)SELECT INSTR(Oracle Training,ra,1,2)Instring FROM DUAL;Instring FROM DUAL;INSTRB(,)INSTRB(,)除了返回的字节外除了返回的字节外 ，与，与INSTR INSTR 相同。相同。LENGTH()LENGTH()返回字符串返回字符串 c c 的长度。的长度。select name,length(name),addr,length(addr),sal,select name,length(name),addr,length(addr),sal,length(to_char(sal)length(to_char(sal)from nchar_tst from nchar_tstNAME LENGTH(NAME)ADDR LENGTH(ADDR)SAL LENGTH(TO_CHAR(SAL)NAME LENGTH(NAME)ADDR LENGTH(ADDR)SAL LENGTH(TO_CHAR(SAL)赵元杰赵元杰 3 3 北京市海淀区北京市海淀区 6 99999.99 8 6 99999.99 8LENGTHB()LENGTHB()以字节返回字符串的字节数。以字节返回字符串的字节数。select name,lengthb(name),length(name)select name,lengthb(name),length(name)from nchar_tst;from nchar_tst;NAME LENGTHB(NAME)LENGTH(NAME)NAME LENGTHB(NAME)LENGTH(NAME)-赵元杰赵元杰 6 3 6 3LOWER()LOWER()返回字符串并将所有字符变为小写。返回字符串并将所有字符变为小写。select lower(AaBbCcDd)AaBbCcDd from dual;select lower(AaBbCcDd)AaBbCcDd from dual;UPPER()UPPER()将给出字符串变为大写。将给出字符串变为大写。select upper(AaBbCcDd)AaBbCcDd from dual;select upper(AaBbCcDd)AaBbCcDd from dual;RPAD(stringRPAD(string，LengthLength，set)set)LPAD(stringLPAD(string，LengthLength，set)set)RPAD RPAD在列的右边粘贴字符；在列的右边粘贴字符；LPAD LPAD在列的左边粘贴字符。在列的左边粘贴字符。select RPAD(Cityselect RPAD(City，3535，.).)，temperature temperature from weather;from weather;RPAD(City RPAD(City，3535，.)temperature.)temperature -CLEVELAND.85 CLEVELAND.85 LOS ANGELES.81 LOS ANGELES.81 .(即不够即不够3535个字符用个字符用.填满填满)SUBSTR(stringSUBSTR(string，startstart，Count)Count)取子字符串中函数取子字符串中函数对字串对字串(或字段或字段)，从，从startstart字符开始，连续取字符开始，连续取 count count 个字个字符并返回结果，如果没有指符并返回结果，如果没有指countcount则一直取到尾。则一直取到尾。select phone,substr(phone,1,3)|select phone,substr(phone,1,3)|0 0|substr(phone,4)substr(phone,4)from telecommunication from telecommunication where master=where master=中国电信中国电信;SUBSTRB(stringSUBSTRB(string，startstart，Count)Count)对字串对字串(或字段或字段)，从，从startstart字节开始，连续取字节开始，连续取 count count 个字个字节并返回结果，如果没有指节并返回结果，如果没有指countcount则一直取到尾。则一直取到尾。REPLACE(REPLACE(stringstring,string_instring_in,string_outstring_out)String:String:希望被替换的字符串或变量。希望被替换的字符串或变量。String_in:String_in:被替换字符串。被替换字符串。String_out:String_out:要替换字符串。要替换字符串。select replace(select replace(InformaixInformaix中国公司中国公司,InformaixInformaix,IBM IBM InformixInformix)IBM)IBM数据库数据库 from dual;from dual;SELECT REPLACE(SELECT REPLACE(OracleOracle,OrOr,MirMir)“Example Example“FROM DUAL;FROM DUAL;TRIM(FROM )TRIM(FROM )TRIMTRIM可以使你对给定的字符串进行裁剪可以使你对给定的字符串进行裁剪(前面，后面或前后前面，后面或前后)。如果指定如果指定 LEADING,Oracle LEADING,Oracle 从从trim_char trim_char 中裁剪掉前面的字符；中裁剪掉前面的字符；如果指定如果指定TRAILING,Oracle TRAILING,Oracle 从从trim_char trim_char 中裁剪掉尾面的字符；中裁剪掉尾面的字符；如果指定两个都指定或一个都没有给出，如果指定两个都指定或一个都没有给出，OracleOracle从从trim_char trim_char 中中裁剪掉前面及后面的字符；裁剪掉前面及后面的字符；如果不指定如果不指定 trim_character,trim_character,缺省为空格符；缺省为空格符；将下面字符串中的前面和后面的将下面字符串中的前面和后面的0 0字符都去掉：字符都去掉：SELECT TRIM(0 FROM 0009872348900)TRIM Example SELECT TRIM(0 FROM 0009872348900)TRIM Example FROM DUAL;FROM DUAL;单记录数字函数单记录数字函数ABS()ABS()返回指定值的绝对值。返回指定值的绝对值。select abs(100),abs(-100)from dual;select abs(100),abs(-100)from dual;ACOS()ACOS()给出反余弦的值。给出反余弦的值。select acos(-1)acos from dual;select acos(-1)acos from dual;ASIN()ASIN()给出反正弦的值。给出反正弦的值。select asin(-1)arc sine from dual;select asin(-1)arc sine from dual;ATAN()ATAN()返回一个数字的反正切值。返回一个数字的反正切值。select atan(-1)arc tangent from dual;select atan(-1)arc tangent from dual;CEIL()CEIL()返回大于或等于给出数字的最小整数。返回大于或等于给出数字的最小整数。select ceil(3.14159)from dual;select ceil(3.14159)from dual;COS()COS()返回一个数字余弦值。返回一个数字余弦值。select cos(-3.1415926)from dual;select cos(-3.1415926)from dual;COSH()COSH()返回一个数字双曲余弦值。返回一个数字双曲余弦值。select cosh(20)cosh from dual;select cosh(20)cosh from dual;EXP()EXP()返回一个数字返回一个数字 e e 的的 n n 次方的值。次方的值。select exp(2),exp(1)from dual;select exp(2),exp(1)from dual;FLOOR()FLOOR()对给定的数字取整数。对给定的数字取整数。select floor(123.45),floor(45.56)from dual;select floor(123.45),floor(45.56)from dual;LN()LN()返回一个数字的对数值，返回一个数字的对数值，n n 是大于是大于 0 0 的数字。的数字。select ln(1),ln(2),ln(3)from dual;select ln(1),ln(2),ln(3)from dual;LOG(,)LOG(,)返回一个以返回一个以n1n1为底的为底的n2n2的对数，的对数，n1n1不是不是0 0或或1 1的正数。的正数。select log(2,1),log(2,2)from dual;select log(2,1),log(2,2)from dual;MOD(,)MOD(,)select mod(10,3),mod(10,2),mod(10,4)from dual;select mod(10,3),mod(10,2),mod(10,4)from dual;POWER(,)POWER(,)返回返回n1n1的的n2n2次方值。次方值。select power(2,10),power(3,3)from dual;select power(2,10),power(3,3)from dual;ROUND(value,precision)ROUND(value,precision)按照指定的精度进行舍入。按照指定的精度进行舍入。select round(55.5),round(-55.5),select round(55.5),round(-55.5),trunc(55.5),trunc(-55.5)trunc(55.5),trunc(-55.5)from dual;from dual;SIGN()SIGN()取数字取数字n n的符号，大于的符号，大于0 0返回返回 1 1；小于；小于0 0返回返回-1-1；等于等于0 0返返回回0 0。select sign(123),sign(-100),sign(0)from dual;select sign(123),sign(-100),sign(0)from dual;SIN()SIN()返回一个数字的正弦值。返回一个数字的正弦值。select sin(1.57079)from dual;select sin(1.57079)from dual;SINH()SINH()返回双曲余弦的值。返回双曲余弦的值。select sin(20),sinh(20)from dual;select sin(20),sinh(20)from dual;SQRT()SQRT()返回数字返回数字n n的根。的根。select sqrt(64),sqrt(10)from dual;select sqrt(64),sqrt(10)from dual;TAN()TAN()返回数字返回数字n n的正切值。的正切值。select tan(20),tan(10)from dual;select tan(20),tan(10)from dual;TANH()TANH()返回数字返回数字n n的双曲正切值。的双曲正切值。select tanh(20),tan(20)from dual;select tanh(20),tan(20)from dual;TRUNC(value,precision)TRUNC(value,precision)按照指定的截取一个数。按照指定的截取一个数。SELECT TRUNC(124.16666,-2)trunc1,SELECT TRUNC(124.16666,-2)trunc1,trunc(124.16666,2)trunc(124.16666,2)from dual;from dual;单记录日期函数单记录日期函数ADD_MONTHS(,)ADD_MONTHS(,)增加月份和减去月份。增加月份和减去月份。select to_charselect to_char(add_months(to_date(199712,yyyymm),1),yyyymm)(add_months(to_date(199712,yyyymm),1),yyyymm)add_month from dual;add_month from dual;select to_charselect to_char(add_months(to_date(199712,yyyymm),-1),yyyymm)(add_months(to_date(199712,yyyymm),-1),yyyymm)add_mo from dual;add_mo from dual;LAST_DAY(date)LAST_DAY(date)返回日期返回日期datedate所在月的最后一天。所在月的最后一天。select select to_char(sysdate,yyyy.mm.dd),to_char(sysdate)+1,yyto_char(sysdate,yyyy.mm.dd),to_char(sysdate)+1,yyyy.mm.dd)from dual;yy.mm.dd)from dual;select to_char(last_day(sysdate),select to_char(last_day(sysdate),)from dual;)from dual;select last_day(sysdate)from dual;select last_day(sysdate)from dual;MONTHS_BETWEEN(date2,date1)MONTHS_BETWEEN(date2,date1)给出给出date2-date1date2-date1的月数的月数(可以是小数可以是小数)。select months_between(19-12select months_between(19-12月月-1999,19-3-1999,19-3月月-2000)-2000)mon_betw from dual;mon_betw from dual;select months_between(to_date(2000.05.20,yyyy.mm.dd),select months_between(to_date(2000.05.20,yyyy.mm.dd),to_date(2005.05.20,yyyy.mm.dd)mon_bet from dual;to_date(2005.05.20,yyyy.mm.dd)mon_bet from dual;NEXT_DAY(date,NEXT_DAY(date,dayday)给出日期给出日期datedate和星期和星期x x之后计算下一星期之后计算下一星期x x的日期，这里的的日期，这里的dayday为星期，如：为星期，如：MONDAY,Tuesday MONDAY,Tuesday等。但在中文环境下，要写成等。但在中文环境下，要写成星期星期x x这样的格式。这样的格式。如今天是如今天是5 5月月1818日星期五，计算下一个星期五是几号：日星期五，计算下一个星期五是几号：select next_day(18-5select next_day(18-5月月-2001,-2001,星期五星期五)nxt_day)nxt_day from dual;from dual;SYSDATE SYSDATE 用来得到系统的当前日期。用来得到系统的当前日期。select to_char(sysdate,dd-mon-yyyy day)from dual;select to_char(sysdate,dd-mon-yyyy day)from dual;单记录转换函数单记录转换函数TO_CHAR(date,TO_CHAR(date,formatformat)根据根据formatformat重新格式日期重新格式日期datedate的格式。的格式。select to_char(sysdate,yyyy/mm/dd hh24:mi:ss)select to_char(sysdate,yyyy/mm/dd hh24:mi:ss)from dual;from dual;select to_char(sysdate,select to_char(sysdate,yy-mon-ddyy-mon-dd)from dual;)from dual;TO_DATE(string,TO_DATE(string,formatformat)将字符串转换为将字符串转换为ORACLEORACLE的日期。的日期。Insert into demo(demo_key,date_col)Insert into demo(demo_key,date_col)Values(1,to_date(Values(1,to_date(04-Oct-199904-Oct-1999,DD-Mon-DD-Mon-yyyyyyyy););Select to_date(Select to_date(10-810-8月月-06-06,yy-mon-ddyy-mon-dd)From dual;From dual;TO_NUMBER()TO_NUMBER()将给出的字符转换为数字。将给出的字符转换为数字。SELECT TO_NUMBER(SELECT TO_NUMBER(19471947)“FISCAL_YEARFISCAL_YEAR”FROM FROM DUAL;DUAL;GREATEST()GREATEST()返回一组表达式中的最大值，即比较字符的编码大小。返回一组表达式中的最大值，即比较字符的编码大小。select greatest(AA,AB,AC)from dual;select greatest(AA,AB,AC)from dual;select greatest(select greatest(啊啊,安安,天天)from dual;)from dual;LEAST()LEAST()返回一组表达式中的最小值，即比较字符的编码大小。返回一组表达式中的最小值，即比较字符的编码大小。select least(select least(啊啊,安安,天天)from dual;)from dual;UID UID 函数函数返回标识当前用户的唯一整数。返回标识当前用户的唯一整数。show user show user select username,user_id from dba_users select username,user_id from dba_users where user_id=UIDwhere user_id=UIDUSER USER 函数函数返回当前用户的名字。返回当前用户的名字。select user from dual;select user from dual;USERENV()USERENV()返回当前用户环境的信息，返回当前用户环境的信息，optopt选项可以是：选项可以是：ENTRYID ENTRYID 返回当前用户会话的入口返回当前用户会话的入口IDIDSESSIONID SESSIONID 返回当前用户会话的返回当前用户会话的IDIDTERMINAL TERMINAL 返回当前系统会话的操作系统标识返回当前系统会话的操作系统标识ISDBA ISDBA 如果当前用户有如果当前用户有DBADBA权限，则返回权限，则返回 TRUE TRUELABLE LABLE 返回当前用户会话的标号返回当前用户会话的标号LANGUAGE LANGUAGE 返回当前用户的语言和区域返回当前用户的语言和区域CLIENT_INFO CLIENT_INFO 为当前用户会话返回为当前用户会话返回 client-info client-i