当前位置:文档之家 > SQL编程基础

SQL编程基础

基础篇

概述:

对于数据库的学习首先要掌握的是数据库的数据提取与维护的语言,对于对数据库中的数据进行维护的语言,各种数据库(包括桌面数据库FOX,ACCESS,大型数据库SYBASE,DB2,ORACLE)在基础上都遵循这一种标准叫做SQL,也就是结构化查询语言,对应英文叫做STRUCTUAL QUERY LANGUAGE ,它提供了维护数据库的以下几种种语句类型,分别是数据提取(select)DML (DATA MANIPULATION LANGUAGE),DDL(DATA DEFINITION LANGUAGE) DCL(DATA CONTROL LANGUAGE) 和事务控制语句(如:COMMIT,ROLLBACK,SA VEPOINT) 分别起到数据的维护(增、删、改)数据库对象的生成(如表,索引,视图等)和数据库访问权限的控制及事务控制的作用.

各种数据库为了增强SQL语言的功能,都对标准SQL做了相应的扩充,例如增加了程序分支模块,循环模块,异常处理等,并将这些功能模块以函数、过程、包的形式存储在数据库中,使之能够更好的处理业务,各种数据库将扩充的这一部分形成不同的产品,例如ORALE 的PL-SQL,SYBASE 的T-SQL 等,在这一部分内容里我们将介绍ORACLE 的PL/SQL(过程化语言 PROCEDURE LANGUAGE)部分.

无论是从事数据库开发还是从事数据库管理,对于SQL及PL/SQL都是必须掌握的,为了更好的掌握这些内容,我们在学习了相关的基本知识后,将通过开发一个简单的人事管理系统(包含使用Client/Sserver 结构和Brower/Server 结构)和编写服务器端的一个优惠处理程序分别掌握数据库的客户端程序与服务器端程序编写的思路和方法.

第四章 SQL语言基础

第四章 SQL语言基础

4.1 查询语句

4.1.1单表查询:

表是数据库中进行数据存储的一个对象,我们所需要得到的数据必须从对应的数据库中的表里来进行提取,对单一表内部数据的查询是学习SQL语句其它功能的基础.因此我们在这一章中学习从一章表中进行提取数据.

4.1.1 语法

首先我们先介绍以下这个最基本语句的语法,然后以下的 例子都是对这个语法的各种情况的具体实现.

SELECT *|{[DISTINCT] column|expression [alias],...}

FROM table[WHERE condition(s)]

[ORDER BY {column, expr, alias} [ASC|DESC]];

gramma4.1

SELECT与from 之间可以是表中的列,也可以是表达式,包括算术表达式、字符串常数、函数等。(字符串用单引号定界)。

例1 select * from t_serv ;

说明:从用户信息中提取所有数据(取所有字段)。

例2 Select serv_name,dev_num from t_serv

说明:从用户信息中提取用户名称和电话号码(取字段 COLUMN)。

例 3 Select 3+2 from dual;

说明:取一个表达式可以是计算公式

例 4 Select sysdate “CURRENT DATE” from dual;

说明:为了使输出的数据更有实际意义,可以在选择出的数据所对应的列上标识一个有意义的名称,其中SYSDATE 是一个函数得到系统当前时间Dual 是一个系统表,只有一条数据,用来满足只需一条数据的查询需求,在使用时列名与别名之间的空格可以用AS 代替,同时如果别名中间有空格或者别名中需要区分大小写则该别名需要使用两

杰立培训个双引号将其包含.

例5 Select serv_name ,cont_id,dev_num from t_serv where dev_num =’88888888’; 说明:从用户信息中提取用户名称,合同号和电话号码,提取条件为电话号码是 88888888号码。

例6 Select serv_name,dev_num from t_serv order by dev_num;

说明:从用户信息中提取用户名称和电话号码,按照电话号码排序缺省的情况下是升序排列。例7 Select serv_name,dev_num from t_serv order by dev_num desc ;

说明:从用户信息中提取用户名称和电话号码,按照电话号码的降序排列。

查询指定的条件可以通过WHERE子句来实现,WHERE 常用查询条件主要有:

比较 =,>,<,>=,<=,!=,<>,!>,!<;NOT+上述比较运算符

确定范围 BETWEEN AND,NOT BETWEEN AND

确定集合 IN,NOT IN

字符匹配 LIKE,NOT LIKE

空值 IS NULL,IS NOT NULL

多重条件 AND,OR

例8 select serv_name,dev_num from t_serv where cont_id between 512900001 and 512900009;

说明:相当与使用大于等于某数并且小于等于某数.

例9 select orig_no,term_no,begin_time,duration,charge from t_call_ticket where orig_no like ‘88%’ and (term_no like ‘87%’ or term_no like ‘010%’) 说明:提取主叫号码是88开头,被叫号码是87开头或者被叫是北京的长途话单的主叫、被叫、起始时间、时长费用信息。对于需要使用多个条件的语句,可以使用AND OR 进行连接,其优先级顺序与其它开发语言相同,可以使用括号的方式解决优先级问题. 例10 select * from t_billing_type where billing_type_id in(110,121,34);

说明:IN谓词实际上是多个OR运算的缩写。

例11 Select distinct term_no from t_call_ticket where orig_no=’88888888’

说明:从用户的详细话单中提取主叫号码是88888888的所有不重复的被叫号码 DISTINCT 关键字用来提取不重复的数据.

例 12 select last_name “My name”,department_id,salary from employees

order by “My name”,department_id;

第四章 SQL语言基础说明: order by 后面可以使用字段的别名的方式,也可以使用多个字段进行排序.

注意:

1涉及空值查询时使用IS NULL ,NOT IS NULL,这里的IS不能用= 替代。

select * from t_serv where fee is not null,空值不能等同于0 或者空格.

排序时空值总是比任何值大.空值与其它数值的运算结果仍然是空值.

2 注意 当查询条件涉及到多个时,可以用逻辑运算符AND和OR来联多个查询条件AND的优先级高于OR,但是用户可以用括号改变优先级

3 使用LIKE 时%与_的用法。

Select serv_name ,dev_num from t_serv where dev_num like‘88%’ and dev_num like ‘____7___’order By cont_id;

说明: 从用户信息中提取用户名称和电话号码,提取条件为电话号码是88开头的号码并且 第5位是7的电话号码,在使用%时,注意%在前面与%在后面时所代表的不同的意义.注意:排序的条件不一定在所选择数据列中。

4 在WHERE 条件中的字符类型和时间类型的数据必须使用单引号包含,对于字符类型是大 小写敏感的.

5 如果在LIKE 的内容中包含了_或者%则使用ESCAPE关键字.

Select employee_id,last_name,job_id from employees Where job_id like ‘%SA\_%’ ESCAPE ‘\’;

其它内容:

例 13 select serv_name||’的电话号码是:’||dev_num from t_serv where state=’4’; 说明:可以使用||将两个字符类型的数据连接起来,对于字符类型的表达式需要用单引号包含

4.1.2 函数:

在上面的SQL语法中,无论是对列还是对与表达式,都可以使用SQL中的函数,这些函数在不同的数据库中的拼法与参数是不同的,他的语法为

Function_name[(arg1,arg2,…………)]

Gramma4.2

根据调用函数时的输入数据是一行还是多行,SQL函数可以分为单行函数和多行函数.

杰立培训单行函数处理每一行并对该行产生一条结果数据.多行函数安组处理数据多条数据,一般每

SQL编程基础

分为5类 1)字符类 2) 数值类 3)日期时间类,4)数值转换类,5 其它,在下面分别进行介绍

1)字符类:

LOWER(COL|EXP)

UPPER(COL|EXP)

INITCAP(COL|EXP)

CONCAT(COL|EXP, COL|EXP)

SUBSTR(COL|EXP,m,n)

LENGTH(COL|EXP)

INISTR(COL|EXP,’string’,[m],[n])

LPAD(COL|EXP,n,’string’)

RPAD(COL|EXP,n,’string’)

TRIM(leading|trailing|both,trim_character From trim_source)

REPLACE(text,serch_string,replacement_string)

例1

CONCAT('Hello', 'World')

SUBSTR('HelloWorld',1,5)

LENGTH('HelloWorld')

INSTR('HelloWorld', 'W')

LPAD(salary,10,'*')

RPAD(salary, 10, '*')

TRIM('H' FROM 'HelloWorld')

第四章 SQL语言基础

例 2 select employee_id,last_name,department_id from employees

where lower(last_name)=’higgins’;

说明:如果不加这个函数,由于人名中可能含有字母的大小写问题,而不能得到正确的数据

2 数值函数

round(col|exp,n) 对给定位的值进行四舍五入

trunc(col|exp,n) 对给定位的值舌去.

Mod(m,n) 取余

例 3 ROUND(45.926, 2) 45.93TRUNC( 45.926, 2) 45.92

MOD(1600, 300) 1003时间函数

当提取时间类型的数值时缺省的时间格式时 DD-MON-RR 其中 DD 是指日期,MON是

月份的名称,RR指的是两位年.Sysdate 返回当前的日期时间,一个时间类型的值可以加减

去一个数字,得到之前或之后的某一天两个时间之差是两个时间值相隔的天数.加上1个小

时可以用sysdate+1/24 得到.

MONTHS_BETWEEN

ADD_MONTHS

NEXT_DAYLAST_DAY 本月的最后一天

ROUND

TRUNC

例 4:select MONTHS_BETWEEN('16-6月 -05','16-6月 -04') from dual;

ADD_MONTHS (sysdate,6)

NEXT_DAY('16-6月-05','星期一')

LAST_DAY('16-6月-05')

ROUND(SYSDATE,’MONTH’)

ROUDN(SYSDATE,’YEAR’)

TRUNC(SYSDATE,’MONTH’)

TURNC(SYSDATE,’YEAR’)

期,MON的格式是经常用到的格式,对于不常用的格式,请参考相关的文档例 5

select to_char(sysdate,’yyyymmddhh24miss’) from dual;

to_char(1234,’999999’)

to_char(1234,’0999999’)

to_char(1234,’to_char(1234,’999,999)

select to_date(‘2004-04-04 23:00:02’,’yyyy-mm-dd hh24:mi:ss’) from dual;

5 其它函数.

杰立培训NVL(EXPR1,EXPR2) 将空值转换为EXPR2

NVL2(EXPR1,EXPR2,EXPR3) 如果exp1非空返回exp2,否则返回exp3

NULLIF(EXPR1,EXPR2) 如果exp1=exp2 返回NULL 否则返回exp1

COALESCE(expr1,expr2,………exprn) 返回第一个非空的值.

DECODE 可以实现IF-TEN-ELSE 的功能

DECODE(COL/EXPR,SERCH1,RESULT1,[serch2,result2],[default])

例 6 SELECT last_name, salary, NVL(commission_pct, 0),

(salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SALFROM employees;

如果佣金为空值,则用0 代替.

SELECT last_name, salary, commission_pct,NVL2(commission_pct, 'SAL+COMM', 'SAL') income FROM employees WHERE department_id IN (50, 80);

如果折扣不是空值则工资为基本工资+折扣,否则工资为基本工资.

SELECT first_name, LENGTH(first_name) "expr1", last_name, LENGTH(last_name) expr2",NULLIF(LENGTH(first_name), LENGTH(last_name)) result FROM employees;

如果姓与名长度相同则返回空值,否则返回姓SELECT

last_name,COALESCE(commission_pct, salary, 10) comm.

FROM employees ORDER BY commission_pct;

如果COMMISSION_PCT列为空,返回SALARY列值,都为空 返回10SELECT last_name, job_id, salary,DECODE(job_id, 'IT_PROG', 1.10*salary, 'ST_CLERK', 1.15*salary,'SA_REP', 1.20*salary,salary)REVISED_SALARY

FROM employees;

根据不同职位进行加薪.实际作用就是IF-THEN的实现.

关于表设计中的横表与纵表的关系:

在进行表的设计时,我们可以将需求中的属性直接设计成相应的字段,也可以将该属性

设计成为一个字段,其属性用该字段的数据来表示,例如针对一个电话号码可能有月租费,是话费,国内长途费,国际长途非,优惠费,其它费等。

我们可以这样设计表

电话号码 帐期 月租费 市话费 国内长途,国际长途,优惠 其它 状态

1234567 0_2004_01 18 46 23 0 -18 0 欠费

也可以这样设计表

第四章 SQL语言基础电话号码 帐期 费用类型 费用 状态

1234567 01_2004_01 月租费 18 欠费

1234567 01_2004_01 市话费 46 欠费

1234567 01_2004_01 国内话费 23 欠费

1234567 01_2004_01 国际话费 0 欠费

1234567 01_2004_01 优惠话费 -18 欠费

1234567 01_2004_01 其它费 0 欠费

第二种设计方法虽然造成了一些数据冗余但是对于当业务发生变化,比如需要新增加一种帐目类型电报费时,第一种方法必须增加表中的一个字段,而第二种方法仅需要向表中增加一条数据,因此第二种方法增加了程序实现的灵活型。

下条语句可以实现纵表与横表之间的数据转换功能。

select substr(billing_cycle_id,4,4),

sum(decode(substrb(billing_cycle_id,9,2),'01',charge,0)) month1,

sum(decode(substrb(billing_cycle_id,9,2),'02',charge,0)) month2,

sum(decode(substrb(billing_cycle_id,9,2),'03',charge,0)) month3,

sum(decode(substrb(billing_cycle_id,9,2),'04',charge,0)) month4,

sum(decode(substrb(billing_cycle_id,9,2),'05',charge,0)) month5,

sum(decode(substrb(billing_cycle_id,9,2),'06',charge,0)) month6,

sum(decode(substrb(billing_cycle_id,9,2),'07',charge,0)) month7,

sum(decode(substrb(billing_cycle_id,9,2),'08',charge,0)) month8,

sum(decode(substrb(billing_cycle_id,9,2),'09',charge,0)) month9,

sum(decode(substrb(billing_cycle_id,9,2),'10',charge,0)) month10,

sum(decode(substrb(billing_cycle_id,9,2),'11',charge,0)) month11,

sum(decode(substrb(billing_cycle_id,9,2),'12',charge,0)) month12

from t_acct_item group by substr(billing_cycle_id,4,4);

上面的函数中 最常用的是NVL 和DECODE ,需要理解掌握,其它的函数知道可以实现的功能,如果未能记忆其使用的参数可以查阅相关的函数手册.

注意 : 1 单行函数可以进行任意层数的嵌套

select last_name,nvl(to_char(manager_id),’No manager’) from employees;

杰立培训4.1.3 多表查询:

前面的内容我们介绍了对于单表查询的语法,并给出了相应的例子,在我们实际的数据库设计中,由于业务的复杂性和为了维护数据库一致性及减少数据的冗余,一张表里的数据是不能完整描述我们所关心的信息的,为了得到我们我们所需要的信息,必须从多张表中提取需要的字段,因此需要进行多张表进行关联的查询操作.这种操作有两种形式,一种是没有关联条件的成为无条件连接,另一种是通过表之间的关联关系(具体内容将在数据库设计中介绍.)进行提取数据.

4.2.1无条件连接

在多个表连接操作中,如果不用连接条件,则称为无条件连接。无条件连接将产生大量的行,当n1,n2,…nn这n个表进行连接时,产生的记录是n1*n2*…*nn(笛卡儿乘积).由于生成大量无实际意义的数据,因此在现实应用中很少使用。

表A

A B

10 11

20 21

表B

C D

Aa ab

Ba bb

SELECT * FROM A,B 生成的结果为表C:

A B C D

10 11 aa ab

10 11 ba bb

20 21 aa ab

20 21 ba bb

4.2.2 有条件连接

有条件连接通过在多个表之间的关联条件,过滤调无意义的数据,得到有效数据,有条

第四章 SQL语言基础件连接可以分为四种情况:等值连接,非等值连接,自连接,外连接

有条件连接的一般语法:

SELECT table1.column,

table2.column FROM table1, table2WHERE

table1.column1 =table2.column2;

Programma4.4

等值连接条件的一般格式是:

[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>

Programma4.5

其中:比较运算符主要有:=、>、<、>=、<=、!=。等值连接主要适用于两个表之间的字段有主外键关系或连接的两个字段具有相同的含义

非等值连接条件的一般格式是:

[<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<

表名2>.]<列名3>

Programma4.6

非等值连接主要适用于第二张表的两个列是第一张表的某列的一个范围,例如第一张表对应的是学生的名称与学生的成绩,第二张表对应的是成绩级别(优,良,及格)所对应的成绩的范围.

有条件连接执行的步骤:

执行过程 首先在表1中找到第一个元组,然后从头开始扫描表2,逐一查找 满足连接条件的元组,找到后就将表1中的第1个元组与该元组拼接形成结果表中的一个元组。表2全部找完后,再找表1中的第2个元组,然后再从头扫描表2,逐一查找满足连接条件的元组,找到后就将表1中的第2个元组与该元组拼接形成结果表中的一个元组。重复执行,直到表1中的全部元组都处理完毕为止

例7 select st.type_name,se.serv_nam,se.cont_id from t_serv se,t_serv_type st where se.billing_type_id=st.billing_type_id

说明:t_serv 表中有一个用户类型字段,字段类型为数字类型,对该数字类型的汉字说明部分在表t_serv_type的type_name 字段中,因此关联两张表中相同的字段

杰立培训billing_type_id 提取t_serv表中的用户名称和t_serv_type表中的类型名称数据。

为了减少输入可以给表起一个别名列当两个表都有某一个字段时,语句中该字段必须加表名或别名,如果语句中的字段仅在一个表中有则可以在其前面加表名或别名,但为了提高语句的执行性能最好在其前面加上表名。同时,连接条件中的各连接字段类型必须是可比较的如日期型的变量只能与日期型的数据进行比较在,实际使用中使用的最多的是这种等值连接 如果还有其它条件,可以在语句后面再继续增加AND。

例 8 select http://www.doczj.com/doc/10dbeb3cee06eff9aef8072d.htmlst_name,d.department_name,l.city

from employees e,departments d,locations l

where e.department_id=d.department_id

and d.location_id=l.location_id;

说明: 对于多表连接,如果有N个表,为了避免笛卡儿乘积,应该至少在表之间有N-1个连接的条件

例 9 select http://www.doczj.com/doc/10dbeb3cee06eff9aef8072d.htmlst_name,e.salary,j.grade_level

from employees e,job_grades j

where e.salary between j.lowest_sal and j.highest_sal;

说明:对于非等值连接,一般情况下应用于一个字段的值在另一个表的两个字段的范围之间. 4.2.3自身连接

连接操作不仅可以在两个不同的表之间进行,也可以是一个表与其自己进行连接操作,这时称为自身连接。自身连接可以看成一张表的两个副本之间进行连接操作。一般使用在需要提取的数据可以使用自身的数据关联得到

例 10

Select c.dev_num,a.charge,b.charge from t_acct_item a,t_acct_item b,t_serv c Where a.serv_id=b.serv_id and a.billing_cycle_id='01_2004_10'

and b.billing_cycle_id='01_2004_09' and a.acct_item_type_id=68

and c.serv_id=a.serv_id And b.acct_item_type_id=68 and a.charge

例11 select http://www.doczj.com/doc/10dbeb3cee06eff9aef8072d.htmlst_name||’ works for ‘||http://www.doczj.com/doc/10dbeb3cee06eff9aef8072d.htmlst_name

from employees worker,employees manager

where worker.manager_id=manager.employees_id;

第四章 SQL语言基础说明:提取所用员工的姓名及其所属领导的姓名.在这里使用了一个表的不同字段的信息. 4.2.4 外连接

在我们上面举的所有例子当中,只有当关联的两个表或者多个表中的数据符合提取

条件时,数据才会提取出来,如果一张表中的数据在另外一张表中没有相对应的记录,

则这张表中该记录不会被提取出来.但是我们有时会遇到一种情况需要将这条没有在另一表 中有对应记录的数据提取出来,可以使用外连接.

SELECT table1.column, table2.column FROM

table1, table2WHERE

table1.column(+) = table2.column;

或者

SELECT table1.column, table2.column FROM

table1, table2WHERE

table1.column = table2.column(+);

Program4.7

例:12 seelct http://www.doczj.com/doc/10dbeb3cee06eff9aef8072d.htmlst_name,e.department_id,d.department_name

from employees e,departments d

where e.department_id(+)=d.department_id;

说明:在部门表中,有一个部门还没有分配员工,现在需要提取所有的部门名称及该部门的员工姓名(包含没有员工的部门)由于员工表重的数据可能比部门表少,因此在员工表的字段后加一个(+)符号,代表如果在员工表中没有对应的记录则使用一个空值与另一个表进行匹配.

4.2.5 嵌套查询(子查询):

子查询经常用在一个查询语句的条件是另一个查询语句的结果,例如需要查询工资大于张三工资的所有员工的姓名及工资的需求中,首先第一个SQL查询需要得到张三的实际工资是多少,第二个查询以张三得工资为条件得到所有工资大于张三工资得员工.

SELECT select_list FROM table WHERE

expr operator

(SELECT select_list FROM table);

Programm4.8

查询块:一个select-from-where语句称为一个查询块。

子查询:将一个查询块嵌套在另一个查询块的where子句或having子句中的查询。子查询的结果用于建立主查询的查找条件。子查询可以有多层,子查询中不能有ORDER BY子句

杰立培训子查询根据其内部查询返回得结果得行数可以分为单行子查询和多行子查询.对于单行子查询和多行子查询使用得操作符是不同得,单行子查询可以使用(=,>,>=,<,<=,<>)以及集合运算符(IN,ANY,ALL等),对于多行子查询仅能够使用集合运算符,不能使用比较运算符. 例13 select * from t_acct_item_type

Where Acct_item_type_id in(Select acct_item_type_id from t_acct_item where billing_cycle_id=’01_2004_01’);

说明:提取1月份的所有帐目类型,首先提取帐目表中的1月份的所有帐目类型,然后从帐目类型表中的到相关的中文名称。

例14 SELECT last_name, job_id, salary

FROM employees

WHERE job_id = (SELECT job_id FROM employeesWHERE employee_id = 141) AND salary > (SELECT salary FROM employees WHERE empl oyee_id = 143);说明:在一个嵌套查询中可以有多个内部查许.

例15 SELECT employee_id, last_name, job_id, salary

FROM employees

WHERE salary < ANY (SELECT salary FROM employees

WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG';

说明:由于在子查询中会有多条记录返回,因此必须使用集合运算符,在这里ANY起到得作用是小于其中任何一个,也就是小于最大值,试验一下使用比较运算符将会出现什么结果

例16 SELECT employee_id, last_name, job_id, salary

FROM employees

WHERE salary < ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG';

说明:相当与小于最小值.

例17 SELECT http://www.doczj.com/doc/10dbeb3cee06eff9aef8072d.htmlst_name

FROM employees emp

WHERE emp.employee_id NOT IN (SELECT mgr.manager_id FROM employees mgr); 说明:在嵌套查询中应当注意对于内部查询中结果得空值得处理,在这条语句中本来是要提取那些员工号不是领导员工号得员工姓名,但是查询结果为没有这样得记录,在我们实际分析这些数据时,应该有12个这样得员工存在,这是因为在EMPLOYEES中,由于存在MANAGER_ID 为空值得数据,所以在外部的查询中得NOT IN 就包含了NULL这样得值.而NULL 值与其它值得比较得结果仍然是空值,因此没有返回记录,因此我们在内部查询中应当增加一个条件如下:

第四章 SQL语言基础

SELECT http://www.doczj.com/doc/10dbeb3cee06eff9aef8072d.htmlst_name FROM employees emp WHERE emp.employee_id NOT IN (SELECT mgr.manager_id FROM employees mgr where mgr.manager_id is not null)

4.1.4分组查询:

分组查询是指将查询结果表按某一列或多列值分组,值相等的为一组。对查询的结果分组的目的是为了细化统计函数的作用对象。如果未对查询分组,集函数将作用于整个查询结果;如果分组后集函数将作用于每一个组,即每一个分组都有一个集函数。在包含GROUP BY子句的查询语句中,SELECT子句后面的所有字段列表(除集合函数外),均应该包含在GROUP BY 子句中。如果分组后还要按一定的条件对这些分组进行筛选,只输出满足条件的组,则应该使用HAVING短语指定筛选条件。

SELECT

[column,]group_function(column), ...

FROM table[WHERE condition]

[GROUP BY column]

[ORDER BY column];

Program4.9

例1 select trunc(begin_time),sum(charge) from call_ticket_1

group by trunc(begin_time)

说明:统计每天的话单总费用,trunc 函数作用是将日期时间型的变量截取为日期型的变量,这样就可以将同一天的所有话单进行分组求和。

例2 Select billing_cycle_id,sum(charge),round(avg(charge),2),count(*), round(sum(charge)/count(*),2) from t_acct_item

group by billing_cycle_id;

说明:按照帐期统计总费用,平均费用,记录数,总费用除记录数,

例3 Select cont_id,sum(charge) from t_acct_item where

Billing_cycle_id=’01_2004_10’

Group by cont_id

Having sum(charge)>100

说明: 含义为统计帐期为10月份的费用大于100元的用户的合同号。筛选函数必须包含统计函数以下为常用的统计函数

杰立培训AVG AVG([DISTINCT|ALL] <列名> ) 计算一列值的平均值

COUNT COUNT([DISTINCT|ALL] <列名> ) 统计一列中 非空值的个数

MAX MAX([DISTINCT|ALL] <列名> ) 求一列值中的最大值

MIN ([DISTINCT|ALL] <列名> ) 求一列值中的最小值

SUM SUM([DISTINCT|ALL] <列名> ) 计算一列值的总和(要求数值列)

例4 SELECT department_id, MIN(salary)

FROM employees

GROUP BY department_id

HAVING MIN(salary) > (SELECT MIN(salary) FROMemployees

WHERE department_id = 50);

说明: 在HAVING中也可以使用子查询.

注意:WHERE与HAVING的区别是作用对象不同:

WHERE 作用于基本表或视图,从中选择满足条件的数据HAVING短语 作用于组,从中选择满足条件的组。

4.1.5查询结果的几何运算

查询的并集、差集、交集是对多个查询语句所得到得结果进行一个集合运算

1)union 并集

select serv_name,billing_type_id,state from t_serv where billing_type_id =70 union

select serv_name,billing_type_id,state from t_serv where state=’4’;

并集对于一个表来说union可以将多条语句用OR进行关联实现如上条语句可以改写为:select serv_name,billing_type_id,state from t_serv where billing_type_id =70 or state=’4’。当我们需要将两个表的的数据一块提取出来时如:4月份的话单放在CALL_TICKET_4中,5月份的话单放在CALL_TICKET_5中,6月分的话单放在CALL_TICKET_6中,现在我们要提取这中继号为1521的三个月的主叫号码可以使用以下方法:

select orig_no from call_ticket_4 where trunk_in=1521

union

select orig_no from call_ticket_5 where trunk_in=1521

第四章 SQL语言基础union

select orig_no from call_ticket_6 where trunk_in=1521;

注意:

1 集合运算在SELECT 后的字段项必须相同。

2 union 并集中的数据是不重复的。并集得另一种关键字是UNION ALL,它与UNION的区别是UNION ALL得到得的结果如果有重复数据的话,它将显示所有重复的数据,也就是说对结果数据不进行排序操作,因此有较好的性能,在对明确没有重复数据的情况下尽量使用UNION ALL例如上面例子中话单没有重复的值,因此语句中应该使用UNION ALL

2)intersect 交集

select serv_name,billing_type_id,state

from t_serv where billing_type_id =70

intersect

select serv_name,billing_type_id,state from t_serv where state=’4’;

当是一个表时可以改写为 and 当多个表使用时可以使用子查询实现。

select orig_no from t_01_04_valid_record where trunk_in=1521

intersect

select orig_no from t_01_05_valid_record where trunk_in=1521

intersect

select orig_no from t_01_06_valid_record where trunk_in=1521

上述语句目的为查询三个月都有话单的主叫号码。可以改写为

select orig_no from t_01_04_valid_record where orig_no in(select

orig_no from t_01_05_valid_record where trunk_in=1521)

and orig_no in(select orig_no from t_01_06_valid_record where trunk_in=1521) and trunk_in=1521

3)minus 差集

select count(*) from t_serv where dev_num in(select dev_num from t_serv where billing_type_id=84 and state='4' minus select orig_no from t_01_04_valid_record) 查询在4月中所有在线的没有产生任何话单的用户。我们使用T_SERV 表提取在线的话吧用户的电话号码,减去在call_ticket_6有话单的主叫号码,剩下的就是在线但是没有话单的号码。该功能可以使用NOT IN 实现,

杰立培训select count(*) from t_serv where billing_type_id =84 and state=’4’ and dev_num not in(select dev_num from t_01_04_valid_record )

可以发现两条语句完成的功能是一样的,但是执行的时间随着数据量的增长性能可能相差不只一个数量级

通过上面的学些我们介绍了关于查询数据的基本知识,在数据库应用中,绝大部分所作的工作就是在进行数据的提取,因此对数据提取的语法和一些基本函数的使用我们应该很好的掌握,同时我们在最后部分也可以看到,实现相同功能的SQL语句有可能性能相差很大,对于如何查看所写SQL 的执行计划,并做相应的调整使SQL语句的性能达到最佳,也使在写SQL语句中应该着重考虑的问题,否则即使能够实现目的,但是性能达不到业务需求的应用程序也是没有实际意义的,就好像一个天气预报软件能够准确预报两天后的气象但是确需要四天的运算一样.,因此我们在写一些SQL语句时不仅要完成所需功能,也要十分重视SQL 语句的性能,避免使用性能差的语句。对于这部分内容我们将在以后章节介绍.

4.2 DML(数据操纵)语句:

DML语句的主要作用是对数据进行维护包含了数据的插入,更新与删除三项主要的功能,分别对应的语句为INSERT ,UPDATE,和DELETE,其所对应的语法如下表格中所示.

INSERT INTO table [(column [,

column...])]VALUES (value [, value...]);

Program4.10

UPDATE table SET

column = value [, column = value, ...]

[WHERE condition];

Program4.11

DELETE [FROM] table[WHERe condition];

Program4.12

4.2.1INSERT 语句

例1 INSERT INTO T_ACCT_ITEM_TYPE VALUES (1,’月租费,1);

例2 INSERT INTO t_acct_item_type (acct_item_type_id,name) VALUES(1,’长话费’); 例3 INSERT INTO departments (department_id, department_name)

第四章 SQL语言基础

VALUES (30, 'Purchasing');

说明:departments 表中共有四列,由于只向前两列插入了值,并且departments表的后两列允许为空,因此后两列的值为空值,其语句等同于:

INSERT INTO departments VALUES (100, 'Finance', NULL, NULL);

如果INTO子句中没有指明任何列名,则新插入的纪录必须在每个属性列上均有值

例4 INSERT INTO employees VALUES (114, 'Den', 'Raphealy', 'DRAPHEAL', '515.127.4561',TO_DATE(‘20040404’,’YYYYYMMDD’),'AC_ACCOUNT', 11000, NULL, 100, 30);

说明:列这一项可选,如果不写则在VALUES项中需要按照表中的顺序填写值.在表定义时说 明了NOT NULL的属性列不能取空值否则会出错。字符型和日期型数据在插入时要加单引号。

例5 INSERT INTO sales_reps(id, name, salary, commission_pct)

SELECT employee_id, last_name, salary, commission_pct

FROM employees WHERE job_id LIKE '%REP%';

说明:也可以使用子查询的方式代替VALUE值,与使用VALUES语法不同的是使用子查询的

插入语句一次可以插入多条数据.使用子查询的方式就不再使用VALUES关键字了.

例 6 INSERT INTO t_acct_item_type (acct_item_type_id,name) SELECT 3,’ 月租优

惠’ FROM t_acct_item_tyep WHERE acct_item_type_id = 2;

说明:向表中插入一条与t_acct_item_type的内容相同的记录,但id 改为2,名称改为月

租优惠的纪录

例 7 INSERT INTO departments (department_id, department_name, manager_id) VALUES (300, 'Engineering', DEFAULT);说明:对于在表的定义时有默认值的列,可以使用DEFAULT 关键字,表的定义我们将在后面内容介绍

4.2.2 UPDATE语句

例1 UPDATE t_acct set agent_id=84, balance=balance+1000 where agent_id=85

说明:将代理商ID为85的用户转移到代理商ID为84名下,并且将余额提高1000。

列 2 UPDATE call_ticket set rate=20 ,total_fee=total_time_unit*20 where acct_item_type_id=96 and total_fee<>0;

说明:也可以修改多个列的值如将网话话单的费率改为0.2元/分钟,费用按照相应费率计算

例 3 UPDATE t_acct_item SET charge = -10 + (SELECT AVG(charge) FROM t_acct_item ) WHERE cont_id in(select cont_id from t_acct where building = 20 and billing_cycle_id=’01_2004_07’;

杰立培训

说明: 带子查询的修改语将某小区的用户的7月月租费改为全部用户平均月租费-10

例 4 update t_serv_acct sa set sa.cont_id=(select cont_id from t_serv se where sa.serv_id=se.serv_id);

说明:根据A表的某唯一列对应值修改B表相同值对应的值要求对于一条serv_id,t_serv 表中只能有1条。

例5 UPDATE employees SET department_id = 55 WHERE department_id = 110;说明:这条语句在执行时会报错误,信息如下:

ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated parent key not found

我们在进行数据库的设计时,为了保证数据的一致性,在进行数据库的设计时使用了

关联表之间的主外键的关系,也就是说如果A表中某个字段a中的数据全部来自B

表b,则b字段与a字段的关系可以是主外键的关系,其中A表是子表,B表是父表,如果是主外键的关系,那么a字段的数据必须存在与b字段,在上面的例子中,

EMPLOYEES表的部门字段的数据(如110部门,55部门)必须存在与departments

表中的部门字段,因此我们根据错误提示可以知道55部门ID在部门表中不存在,因

此更新失败.因此对于有主外键关系的表无论是数据的插入,更新,删除,所进行的

操作如果违反了数据的一致性,则操作必然失败.例如向外键中插入主表中不存在的

数据,在父表中删除一条子表中存在的记录.等等.

例6 UPDATE departments

SET manager_id = DEFAULT WHERE department_id = 10

4.2.3 DELETE语句

例6 DELETE FROM T_SERV_TYPE WHERE ENAME = '月租费'。

说明:删除名字为月租费的数据

例7 DELETE FROM t_serv_type ;

说明:删除T_SERV_TYPE表中的所有数据.

例8 DELETE FROM t_acct_item WHERE acct_item_type_id in (SELECT acct_item_type_id FROM t_acct_item_type WHERE NAME = ‘%优惠%') and billing_cycle_id=’01_2004_10’;

例9 带子查询的删除语句删除所有帐目类型为优惠费的10月份帐目数据。

4.2.4关于事务的概念

当我们进行业务处理时,往往对于数据的一致性要求较高,一个业务处理在数据的改变