一、选择行

　　COURSEID COURSENAME SCORE

　　———- ———- ———-

　　1  java  70

　　2  oracle  90

　　3  xml  40

　　4  jsp 30

　　5  servlet  80

　　SQL> select courseid, coursename ,score ,decode(sign(score-61),-1,’fail’,'pass’) as mark from course_v;

　　COURSEID  COURSENAME  SCORE  MARK

　　———-  ———-  ———-  —-

　　1   java  70  pass

　　2  oracle   90  pass

　　3  xml  40   fail

　　4  jsp   30   fail

　　5   servlet    80  pass

　　12、

　　已经知道原表

　　year       salary

　　——————   ———————

　　2000           1000

　　2001           2000

　　2002           3000

　　2003           4000

　　显示查询结果

　　year       salary

　　——————   ———————

　　2000           1000

　　2001           3000

　　2002           6000

　　2003           10000

　　即salary为以前年的工资的和；

　　解答过程如下：

　　select b.year,sum(a.salary)

　　from test a,test b

　　where a.year<=b.year

　　group by b.year

　　order by year

　　1. 简单的SELECT 语句

　　SELECT 字段名1 [AS] ‘字段名1 解释’ FROM table;

　　2. 处理NULL

　　NVL函数可把NULL转换成其它类型的符号

　　编程技巧: NVL函数在多条件模糊查询的时候比较有用

　　NVL函数可返回多种数据类型:

　　返回日期 NVL(start_date,’2002-02-01′)

　　返回字符串 NVL(title,’no title’)

　　返回数字 NVL(salary,1000)

　　3. 使用SQL*PLUS

　　SQL> desc table; 显示表结构

　　SQL> select * from tab; 查看用户下所有的表

　　SQL> set pause on; 可以使大量结果集在用户按“Enter”(回车)后翻页

　　SQL> set pagesize 100; 设定SQL语句返回结果集一页的行数100, 默认值是14

　　SQL> set linesize 100; 设定SQL语句返回结果集一行的宽度100, 默认值是80

　　4. SQL*PLUS里规定字段的显示格式

　　规定数字的显示格式

　　SQL>column 字段名 format 99999999999;

　　SQL>column 字段名 format 999,999,999,999;

　　SQL>column 字段名 format a数字 [word_wrapped];

　　规定long字符的显示宽度

　　SQL>set long 200;

　　规定字段名的显示内容

　　SQL> column 字段名 heading ‘字段名显示内容’;

　　SQL> set heading off; 查询时不显示字段名称

　　规定字段的对齐方向

　　SQL> column 字段名 justify [left | right | center];

　　清除字段的格式

　　SQL> column 字段名 clear;

　　5. SQL*PLUS里规定字段的显示格式例子

　　SQL> column last_name heading ‘Employee|Name’ format a15;

　　SQL> column salary justify right format $99,999.99;

　　SQL> column start_date format a10 null ‘Not Hired’;

　　说明：如果start_date为null, 显示字符串’Not Hired’

　　6. 判断题(T/F)

　　(1). SQL command are always held in sql buffer. [T]

　　(2). SQL*PLUS command assit with query data. [T]

　　7、 SQL*PLUS命令只控制SELECT结果集的显示格式及控制文件.只有SQL命令能访问数据库.

　　二、限制选择行

　　1. 按指定的规则排序

　　SELECT expr FROM table [ORDER BY {column, expr} [ASC | DESC] ];

　　默认的排序是ASC升序(由小到大)

　　还可以ORDER BY 字段名的位置[1]| [2] ASC| DESC;

　　2. 用WHERE限制选择行(1)

　　比较操作符 = > < >= <= != <> ^= 与NULL比较不能用上面的比较操作符 ANY SOME ALL

　　SQL操作符 BETWEEN … AND… IN LIKE IS NULL

　　NOT BETWEEN … AND… NOT IN NOT LIKE IS NOT NULL

　　逻辑操作符 AND OR NOT

　　3. 用WHERE限制选择行(2)

　　比较顺序(可以用括号改变它们的顺序)

　　(1). = < > >= <= in like is null between

　　(2). and

　　(3). Or

　　4. LIKE操作

　　% 零到任意多个字符 _ 一个字符

　　例如: 字段名 like ‘M%’ 字段名 like ‘%m%’ 字段名 like ‘job_’

　　如果要找含下划线的字符, 要加反斜线 例如:字段名 like ‘%X/_Y%’ escape ‘/’

　　5. 日期字段的比较

　　举例:

　　日期字段 between to_date(’2001-12-12′,’YYYY-MM-DD’) and to_date(’2002-02-01′,’YYYY-MM-DD’)

　　日期字段> to_date(’2001-12-12′,’YYYY-MM-DD’) and日期字段<=

　　to_date(’2002-02-01′,’YYYY-MM-DD’);

　　6. 不能用到索引的比较操作符

　　IS NULL IS NOT NULL  LIKE ‘%m%’ [NextPage]

　　三、单行函数

　　1. 数字函数

　　ABS 取绝对值 POWER 乘方 LN 10为底数取0

　　SQRT 平方根 EXP e的n次乘方 LOG(m,n) m为底数n取0

　　数学运算函数:ACOS ATAN ATAN2 COS COSH SIGN SIN SINH TAN TANH

　　CEIL 大于或等于取整数

　　FLOOR 小于或等于取整数

　　MOD 取余数

　　ROUND(n,m) 按m的位数取四舍五入值如果round(日期): 中午12以后将是明天的日期.

　　round(sysdate,’Y')是年的第一天

　　TRUNC(n,m) 按m的位数取小数点后的数值如果trunc(日期), 确省的是去掉时间

　　2. 字符函数

　　CHR 按数据库的字符集由数字返回字符

　　CONCAT(c1,c2) 把两个字符c1,c2组合成一个字符, 和 || 相同

　　REPLACE(c,s,r) 把字符c里出现s的字符替换成r, 返回新字符

　　SUBSTR(c,m,n) m大于0,字符c从前面m处开始取n位字符,m等于0和1一样,

　　m小与0,字符c从后面m处开始取n位字符

　　TRANSLATE(c,f1,t1) 字符c按f1到t1的规则转换成新的字符串

　　INITCAP 字符首字母大写,其它字符小写

　　LOWER 字符全部小写

　　UPPER 字符全部大写

　　LTRIM(c1,c2) 去掉字符c1左边出现的字符c2

　　RTRIM(c1,c2)

　　TRIM(c1,c2) 去掉字符c1左右两边的字符c2

　　LPAD(c1,n,c2) 字符c1按制定的位数n显示不足的位数用c2字符串替换左边的空位

　　RPAD(c1,n,c2)

　　3. 日期函数

　　ADD_MONTHS(d,n) 日期值加n月

　　LAST_DAY  返回当月的最后一天的日期

　　MONTHS_BETWEEN(d1,d2) 两个日期值间的月份,d1<d2 返回负数

　　NEXT_DAY  返回日期值下一天的日期

　　SYSDATE 当前的系统时间

　　DUAL是SYS用户下一个空表，它只有一个字段dummy

　　4. 转换函数(1)

　　TO_CHAR(date,’日期显示格式’)

　　TO_CHAR(number) 用于显示或报表的格式对齐

　　TO_DATE(char,’日期显示格式’)

　　TO_LOB 把long字段转换成lob字段

　　TO_NUMBER(char) 用于计算或者比较大小

　　4. 转换函数(2)

　　to_date里日期显示格式

　　YYYY 年 YEAR YYY YY Y

　　Q 季度

　　MM 月 MONTH MON

　　W 星期 (week of month) WW, IW (week of year)

　　(说明：周计是按ISO标准,从1月1日的星期数到后面七天为一周,不一定是从周一到周日)

　　DD 日 DAY DY

　　HH24 小时 HH12 HH

　　MI 分钟

　　SS 秒

　　如果想固定日期的显示格式可以在数据库的参数文件initorasid.ora里新写一行参数

　　NLS_DATE_FORMAT=yyyy-mm-dd

　　hh24:mi:ss可以在UNIX环境变量或者NT的注册表里的设置 NLS_DATE_FORMAT=yyyy-mm-dd hh24:mi:ss

　　4. 转换函数(3)

　　如果想固定日期的显示格式可以用alter session命令改变

　　SQL> alter session set nls_date_format=’yyyy-mm-dd hh24:mi:ss’;

　　它的作用顺序如下:

　　initialization parameter

　　Environment variable

　　ALTER SESSION command

　　4. 转换函数 (4)

　　to_char(number)里数字显示格式

　　9 数字位

　　0 数字前面补0 to_char(-1200,’00000.00′)

　　. 小数点的位置

　　, 标记位置的逗号 用在数字显示格式的左边

　　L 根据数据库字符集加货币符号 to_char(-1200,’L9999.99′)

　　B 把数字0显示为空格,用在数字显示格式的右边

　　MI 右边的负数标记 to_char(-1200,’9999.99MI’)

　　PR 括起来的负数 to_char(-1200,’9999.99PR’)

　　EEEE 用指数方式表示 to_char(-1200,’9999.99EEEE’)

　　5. 输入字符,返回数字的函数

　　instr(c1,c2) 字符c2出现在c1的位置, 不出现, 返回0, 常用于模糊查询

　　length  按数据库的字符集,算出字符c的长度,跟数据库的字符集有关, 一个汉字长度为1

　　6. 有逻辑比较的函数NVL(EXPR1, EXPR2)函数

　　解释: IF EXPR1=NULL RETURN EXPR2

　　ELSE RETURN EXPR1

　　DECODE(AA0V10R10V20R2….)函数

　　解释: IF AA=V1 THEN RETURN R1

　　IF AA=V2 THEN RETURN R2

　　..…

　　ELSE

　　RETURN NULL

　　举例: decode(id,1,’dept sale’,2,’dept tech’)

　　四、从多个表里选取数据记录

　　1. 数据表间的连接

　　简单的连接语法:

　　SELECT 字段名1, 字段名2, …… FROM 表名1, [表名2, ……]

　　WHERE 表名1.字段名 = 表名2. 字段名 [ AND ……] ;

　　SELECT 字段名1, 字段名2, …… FROM 表名1, [表名2, ……]

　　WHERE 表名1.字段名 = 表名2. 字段名(+) [ AND ……] ;

　　有(+)号的字段位置自动补空值

　　连接的分类:

　　等于的连接 =

　　不等于的连接 != BETWEEN … AND … IN 注意IN和OR不能一起用

　　外连接 有一个字段名(+) , 没有满足的条件补空值

　　自连接 同一个表自己跟自己连接 例如找重复记录

　　2. 数据表间的连接例子

　　删除table_name表里字段名email重复的记录:

　　SQL>delete from table_name t1

　　where t1.rowid >

　　(select min(rowid) from table_name t2

　　where t1.email = t2.email

　　group by email

　　having count(email) > 1);

　　找到手机用户的服务区域:

　　SQL> select a.handphoneno,nvl(c.name,’null’),a.totalscore

　　from topscore a,chargeoperator cc,chargeoperatorinfo c

　　where substr(a.handphoneno,1,7)=cc.hpnohead(+)

　　and cc.chargetype=c.chargetype(+)

　　order by a.totalscore desc;