第4章使用sql语句

数据库编程基础第4章使用SQL语句主要内容简单查询语句DML语句分组查询子查询合并查询其他查询复杂查询1、简单查询语句查询表结构：descdept;查询所有列：select

*

fromdept;检索特定列：select

ename,sal,job,deptno

from

emp;取消重复：select

distinct

deptno,job

from

emp;使用算术表达式：select

ename,sal*12

from

emp;使用列别名：selectename

as“姓名”,sal*12

as“年收入”

from

emp处理null：select

ename,sal+nvl(comm,0)from

emp;连接字符串：select

ename||’

is a

’||jobas

“Empdetail”from

emp;主要内容简单查询语句DML语句分组查询子查询合并查询其他查询复杂查询2、DML语句-Insert插入单行记录的方法示例一：不使用列的列表插入单行数据当使用INSERT插入数据时，可以不指定列的列表。注意，如果不指定列的列表，那么必须要为所有列提供数据，并且数据的顺序必颁与列的顺序保持一致。示例如下：SOL>

INSERT

INTO

dept

VALUES

(50，TRAIN’,'BOSTON’),创建1行。示例二：使用列的列表插入单行数据当使用列的列表插入数据时，只需为相应列提供数据。而对于那些未出现在列的列表中的列，其数据为NULL。示例如下：SOL> INSERT

INTO

emp

（empno.

ename,job,hiredata)2

VALUES(1234.‘JOHN’,’CLERK’,’01-3月—8

6-)j已创建1行。2、DML语句-Insert．使用于查询插入数据当使用VALUES子句插入数据时，一次只能插入一行数据；当使用子查询插入数据时，

可必将一张表的数据复制到另一张表中。当处理行迁移、复制表数据或者装载外部表数据到数据库时，可子查询插入数据。其语；如下：INSERT

INTO

<table> 【(column[,column.…])]

subquery如所示，table用于指定表名或视图名；column用于指定列名，如果要指定多个列，那么列之间要用逗号分开；subquery用于指定为目标表提供数据的子查询。当使用子查询插入数据时，INSERT列的数据类型和个数必须要与子查询列的数据类型和个数完全匹配。下面通过

示例说明使用子查询插入数据的方法。2、DML语句-Insert示例一：使用子查询插入数据INSERT

InTo

empioyee (empno.

ename,

sal.

deptno)SELECT empno,

ename,sal.

daptno

FROM

empWHERE

deptno-20;已创建6行。示例二：使用子查询执行直接装载INSERT

/*+APPEND 十／

INTO

employee (empno,

ename

r

sal,

deptno)SELECT empno,

ename,

sal,

deptno FROm

empWHEREdeptno=20;创建6行。提示：尽管以上两条语句的执行结果一样，但第二条语句使用，/+APPEND+/，来表示采用直接装载方式。当要装载大批量数据时，采用第二种方法装载数据的速度要远远优于第一种方法2、DML语句-update当要更新表中行的数据时，可以使用UPDATE语句。使用UPDATE语句时，既可以使用表达式更新列值，也可以使用子查询更新一列或多列的数据。使用UPDATE语句应注意以下事项：如果要更新数字列，则可以直接提供数字值；如果要更新字符列或日期列，则数据必须用单引号引住。当更新数据时，数据必须要满足约束规则。当更新数据时，数据必须要与列的数据类型匹配2、DML语句-update更新关联数据在存在可以确定的条件时，通过使用子查询只需要编写

一条语句就可以完成这项任务，从而降低网络开销，如：Update

emp

set

(

m)=(select m

from

empwhere

ename=‘SMITH’)

whereename=‘SCOTT’;复制表数据Update

employee

set

deptno=(selectdetpno

from

emp

whereempno=7788)Where

job=(select

job

from

emp

where

empno=7788)2、DML语句-delete使用TRUNCATE

TABLE截断表当使川DELETE语句删除表的所有数据时，不会释放表所占用的空间。如果用户确定要删除表的所有数据，那么使用“TRUNCATETABLE”语句速度更快。示例如下：SQL>

TrUNCATE

TABLE

Emp;表已截掉。如上所示，使用TRUNCATETABLE语句不仅会删除表的所有数据，而且还会释放表段所占用的空间。注意，DELETE语句的操作可以回退，但

TRUNCATE

TABLE语句的操作不能回退2、DML语句-delete使用于查询删除数据当使用DELETE语句删除数据时，可以直接在WHERE子句中指定值，井根据条件来删除数据。另外，也可以在WHERE子句中使用于查询作为条件。假定要解雇SALES部门的所有雇员，那么在

WHERE子句中需要使用子查询。示例如下：SQL>

DELETE

FROM

emp

WHERE

deptno=(SELECT

deptno

FROM

dept

WHERE

ename=‘SALES');已删除6行。主要内容简单查询语句DML语句分组查询子查询合并查询其他查询复杂查询3、分组查询在关系数据库中，数据分组是通过使用GROUP

BY子句、分组函数以及HAVING子句共同实现的。其中GROUP

BY子句用于指定要分组的列(例如DEPTNO)，而分组函数则用于显示统计结果（如COUNT，AVG，SUM等），而HAVING子句则用于限制分组显示3、分组查询在关系数据库中，数据分组是通过使用GROUPBY子句、分组函数以及HAVING子句共同实现的。其中GROUPBY子句用于指定要分组的列(例如DEPTNO)，而分组函数则用于显示统计结果（如COUNT，AVG，SUM等），而HAVING子句则用于限制分组显示3、分组查询最常用的五个分组函数：MAX：该函数用于取得列或表达式的最大值，它适用于任何数据类型。MIN：该函数用于取得列或表达式的最小值，它适用于任何数据类型。AVG：该函数用于取得列或表达式的平均值，它只适用于数字类型。SUM：该函数用于取得列或表达式的总和，它只适用于数字类型。COUNT:该函数用于取得总计行数。3、分组查询特别提示：当使用分组函数时，除了函数COUNT(*)之外，其他分组函数都会忽略NULL行。

当执行SELECT语句时，如果选择列表同时包含列、表达式和分组函数，那么这些列和表达式必须出现在GROUP

BY予句中。当使用分组函数时，在分组函数中可以指定ALL和DISTINCT选项。其中ALL是默认选项，该选项表示统计所有行数据（包括重复行）；如果指定DISTINCT，则只会统计不同行值3、分组查询Group

by…having…分组查询，例子：

Select

deptno,avg(sal),max(sal)fromempGroup

by

deptnoHaving

avg(sal)<20000使用GROUP

BY子句、WHERE子句和分组函数有以下一些注意事项：分组函数只能出现在选择列表、HAVING子句和ORDER

BY子句中。如果在SELECT语句中同时包含有GROUP

BY，HAVING以及ORDERBY子句，则必须将ORDER

BY子句放在最后。默认情况下，当使用GROUPBY子句统计数据时，会自动按照分组列的升序方式显示统计结果。3、分组查询如果选择列表包含有列、表达式、分组函数，那么列和表达式必须出现在

groupby子句中，否则会显示错误信息。如：Select

deptno,job,avg(sal)

from

empgroup

by

deptno;主要内容简单查询语句DML语句分组查询子查询合并查询其他查询复杂查询4、子查询1、in

查询匹配的中的任意一值Select *

from

emp

where

job

in(select

distinctjob

from

emp

where

deptno=10)2、all必须与单行操作符结合使用，返回匹配的所有查询结果Select

*

from

emp

where

sal

>all(select

sal

fromemp

where

deptno=30)4、子查询3、any返回匹配的任意子查询结果Select

*

from

emp

where

sal

>any(select

salfrom

emp

where

deptno=30)4、多列子查询Select

*

from

emp

where

(deptno,job)=(selectdeptno,job

from

emp

where

ename=‘SMITH’)4、子查询5、existsSelect

*

from

emp

where

exists(select

1

fromdept

where

dept.deptno=emp.deptno)

anddept.loc=‘NEW

YORK’6、FROM使用子查询Select ename,job,sal

from

emp,(selectdeptno,avg(sal)

avgsal

from

emp

group

bydeptno)dept

where

emp.deptno=dept.deptnoand

sal

>dept.avgsal;4、子查询7、DML中使用子查询INSERT

EMPLOYEE(ID,NAME,TITLE,SALARY)SELECT

EMPNO,ENAME,JOB,SAL

FROMEMP.8、update使用子查询Update

emp

set (

m)=(select m

from

empwhere

ename=‘SMITH’)

where

job=(select

jobfrom

emp

where

ename=‘SMITH’)4、子查询9、DELETE中使用子查询Delete

from

emp

where

deptno=(select

deptnofrom

dept

where

dname=‘SALES’).10、DDL使用子查询Create

tabel

new_emp

as

select

*

from

emp;主要内容简单查询语句DML语句分组查询子查询合并查询其他查询复杂查询5、合并查询1、UNION查询,取消重复Select

ename,sal,job

from

emp

where

sal>2500UnionSelect

ename,sal,job

from

emp

wherejob=‘MANAGER’2、UNION

ALL查询，不取消重复Select

ename,sal,job

from

emp

where

sal>2500Union

ALLSelect

ename,sal,job

from

emp

wherejob=‘MANAGER’主要内容简单查询语句DML语句分组查询子查询合并查询其他查询复杂查询主要内容简单查询语句DML语句分组查询子查询合并查询其他查询复杂查询6、其他查询1、CASESelect

ename,sal,case

when

sal>3000

then

3when

sal>2000

then

2

else

1

grade

from

empWhere

deptno=10主要内容简单查询语句DML语句分组查询子查询合并查询其他查询复杂查询7、复杂查询连接查