函数精通SQL【经典SQL语句大全】.doc

课程一PL/SQL基本查询与排序本课重点:

1、写SELECT语句进行数据库查询

2、进行数学运算

3、处理空值

4、使用别名ALIASES

5、连接列

6、在SQL PLUS中编辑缓冲,修改SQL SCRIPTS

7、 ORDER BY进行排序输出。

8、使用WHERE字段。

一、写SQL命令:

不区分大小写。

SQL语句用数字分行,在SQL PLUS中被称为缓冲区。

最后以;或/结束语句。

也可以用RUN来执行语句

二、从表中查询相关的字段。

例1:SQL> SELECT dept_id, last_name, manager_id FROM s_emp;

2:SQL> SELECT last_name, salary * 12, commission_pct FROM s_emp;

对于数值或日期型的字段,可以进行相应的四则运算,优先级与标准的高级语言相同。

SQL> SELECT last_name, salary, 12 * (salary + 100) FROM s_emp;

三、列的别名ALIASES:

计算的时候特别有用;

紧跟着列名,或在列名与别名之间加“AS” ;

如果别名中含有SPACE,特殊字符,或大小写,要用双引号引起。

例(因字体原因,读者请记住:引号为英文双引号Double Quotation) :

SQL> SELECT last_name, salary, 12 * (salary + 100) ” Annual Salary”FROM s_emp;

四、连接符号: | |

连接不同的列或连接字符串

使结果成为一个有意义的短语:

SQL> SELECT first_name | | ’ ’ | | last_name| | ’ , ’ | |title ” Employees” FROM s_emp;

五、管理NULL值:

SQL> SELECT last_name, title, salary * NVL(commission_pct,0)/100COMM FROM s_emp;

此函数使NULL转化为有意义的一个值,相当于替换NULL。

六、 SQL PLUS的基本内容,请参考<SQL PLUS简单实用精髓篇>

七、 ORDER BY操作:

与其他SQL92标准数据库相似,排序如:

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

从Oracle7 release 7.0. 16开始,ORDER BY可以用别名。

另:通过位置判断排序:

SQL> SELECT last_name, salary*12 FROM s_emp ORDER BY 2;

这样就避免了再写一次很长的表达式。

另:多列排序:

SQL> SELECT last name, dept_id, salary FROM s_emp ORDER BY dept_id,salary DESC;

八、限制选取行:

SELECT expr FROM table [WHERE condition(s) ] [ORDER BY expr] ;

例1:

SQL> SELECT f irst_name, last_name, start_date FROM s_emp WHEREstart_date BETWEEN ’ 09-may-91’ AND ’ 17-jun-91’ ;

例2:

SQL> SELECT last_name FROM s_emp WHERE last_name LIKE ’ _a%’ ; //显示所有第二个字母为a的last_name

例3:

如果有列为NULL

SQL> SELECT id, name, credit_rating FROM s_customer WHEREsales_rep_id IS NULL;

优先级:

Order Evaluated Operator

1 All comparison operators (=, <>, >, >=, <, <=, IN, LIKE, IS NULL,BETWEEN)

2 AND

3 OR

总结:我们今天主要学习了如何进行查询SELECT操作,具体的组合查询与子查询将在以后的课堂中学习,同时希望大家可以工作、学习中多多摸索,实践!

课程二PL/SQL PL/SQL查询行函数

本课重点:

1、掌握各种在PL/SQL中可用的ROW函数

2、使用这些函数的基本概念

3、 SELECT语句中使用函数

4、使用转换函数

注意:以下实例中标点均为英文半角

一、 FUNCTION的作用:

进行数据计算,修改独立的数据,处理一组记录的输出,不同日期显示格式,进行数据类型转换

函数分为:单独函数(ROW)和分组函数

注意:可以嵌套、可以在SELECT, WHERE,和ORDER BY中出现。

语法:function_name (column|expression, [arg1, arg2, . . . ])

二、字符型函数

1、 LOWER转小写

2、 UPPER转大写

3、 INITCAP首字母大写

4、 CONCAT连接字符,相当于| |

5、 SUBSTR SUBSTR(column|expression,m[,n])

6、 LENGTH返回字符串的长度

7、 NVL转换空值

其中, 1、 2经常用来排杂,也就是排除插入值的大小写混用的干扰,如:

SQL> SELECT f irst_name, last_name FROM s_emp WHERE UPPER(last_name)= ’ PATEL’ ;

FIRST_NAME LAST_NAME

-------------------- --------------------

Vikram Patel

Radha Patel

三、数学运算函数(SQL也能用函数来进行四舍五入)

1、 ROUND

四舍五入:ROUND(45.923,2) = 45.92

ROUND(45.923,0) = 46

ROUND(45.923,-1) = 50

2、 TRUNC截取函数(取地下)

TRUNC(45.923,2)= 45.92

TRUNC(45.923)= 45(默为去除小数点)

TRUNC(45.923,-1)= 40

3、 MOD余除

MOD(1600,300)

实例:

SQL> SELECT ROUND(45.923,2) , ROUND(45.923,0) ,ROUND(45.923,-1) FROMSYS.DUAL;

四、 ORACLE 日期格式和日期型函数:

1、默认格式为DD-MON-YY.

2、 SYSDA TE是一个求系统时间的函数

3、 DUAL['dju:el]是一个伪表,有人称之为空表,但不确切。

SQL> SELECT SYSDATE FROM SYS.DUAL;

4、 日期中应用的算术运算符

例:SQL> SELECT last_name, (SYSDA TE-start_date)/7 WEEKS FROM s_empWHERE dept_id = 43;

DA TE+ NUMBER = DA TE

DA TE-DATE= NUMBER OF DAYS

DA TE + (NUMBER/24) =加1小时

5、函数:

MONTHS_BETWEEN(date1, date2) 月份间隔,可正,可负,也可是小数

ADD_MONTHS(date,n)加上N个月,这是一个整数,但可以为负

NEXT_DAY(date, ‘char’ )如:NEXT_DAY (restock_date, ’ FRIDAY’ ) ,从此日起下个周五。

ROUND(date[, ‘fmt’ ])

TRUNC(dat e[, ‘fmt’ ])

解释下面的例子:

SQL> SELECT id, start_date,MONTHS_BETWEEN (SYSDA TE, start_date)TENURE, ADD_MONTHS(start_date,6) REVIEW FROM s_emp WHERE MONTHS_BETWEEN(SYSDATE, start_date)<48;

我们看到:MONTHS_BETWEEN (SYSDA TE, start_date)<48,说明至今工作未满一年的员工。

LAST_DAY (restock_date)返回本月的最后一天

SQL> select round(sysdate, 'MONTH' ) from dual

ROUND(SYSD

----------

01-11月-01round(sysdate, 'YEAR' ) = 01-1月-02

ROUND之后的值比基值大的最小符合值,大家可以用更改系统时间的方法测试,以15天为分界线,也是非常形象的四舍五入,而TRUNC恰好相反,是对现有的日期的截取。

五、转换函数:

1、 TO_CHAR

使一个数字或日期转换为CHAR

2、 TO_NUMBER

把字符转换为NUMBER

3、 TO_DATE

字符转换为日期

这几个函数较为简单,但要多多实践,多看复杂的实例。

SQL> SELECT ID,TO_CHA R(date_ordered, ’ MM/YY’ ) ORDERED FROM s_ordWHERE sales_rep_id = 11 ;

转换时,要注意正确的缺省格式:

SELECT TO_DATE('03-MAR-92' ) CORRECT FROM DUAL;//正确

SELECT TO_DATE('031092' ) CORRECT FROM DUAL;//不正确

SELECT TO_DATE('031095' , 'MMDDYY' ) ERRORR FROM DUAL

输出3月10日

SELECT TO_DATE('031095' , 'DDMMYY' ) ERRORR FROM DUAL

输出10月3日