第5章PL/SQL基础

本章要点
  SQL和PL/SQL
 在PL/SQL中的数据定义语言
 在PL/SQL中的数据操纵语言
 在PL/SQL中的数据查询语言
本章介绍PL/SQL中的数据定义语言(DDL)、数据操纵语言(DML)和数据查询语言(DQL)，由于数据库查询是数据库的核心操作，本章重点讨论使用SELECT查询语句对数据库进行各种查询的方法。
5.1SQL和PL/SQL
SQL(Structured Query Language，结构化查询语言)是目前主流的关系型数据库上执行数据操作、数据检索以及数据库维护所需要的标准语言，是用户与数据库之间进行交流的接口，许多关系型数据库管理系统都支持SQL语言，但不同的数据库管理系统之间的SQL语言不能完全通用，Oracle数据库使用的SQL语言是Procedural Language/SQL(简称PL/SQL)。
5.1.1SQL语言
SQL语言是应用于数据库的结构化查询语言，是一种非过程性语言，本身不能脱离数据库而存在。一般高级语言存取数据库时要按照程序顺序处理许多动作，使用SQL语言只需简单的几行命令，由数据库系统来完成具体的内部操作。
1.  SQL语言分类
通常将SQL语言分为以下4类。
(1) 数据定义语言(Data Definition Language, DDL)。
用于定义数据库对象，对数据库、数据库中的表、视图、索引等数据库对象进行建立和删除，DDL包括CREATE、ALTER、DROP等语句。
(2) 数据操纵语言(Data Manipulation Language, DML)。
用于对数据库中的数据进行插入、修改、删除等操作，DML包括INSERT、UPDATE、DELETE等语句。
(3) 数据查询语言(Data Query Language, DQL)。
用于对数据库中的数据进行查询操作，例如用SELECT语句进行查询操作。
(4) 数据控制语言(Data Control Language, DCL)。
用于控制用户对数据库的操作权限，DCL包括GRANT、REVOKE等语句。
2.  SQL语言的特点
SQL语言具有高度非过程化、应用于数据库的语言、面向集合的操作方式、既是自含式语言又是嵌入式语言、综合统一、语言简洁和易学易用等特点。
(1) 高度非过程化。
SQL语言是非过程化语言，进行数据操作，只要提出“做什么”，而无须指明“怎么做”，因此无须说明具体处理过程和存取路径，处理过程和存取路径由系统自动完成。
(2) 应用于数据库的语言。
SQL语言本身不能独立于数据库而存在，它是应用于数据库和表的语言，使用SQL语言，应熟悉数据库中的表结构和样本数据。
(3) 面向集合的操作方式。
SQL语言采用集合操作方式，不仅操作对象、查找结果可以是记录的集合，而且一次插入、删除、更新操作的对象也可以是记录的集合。




(4) 既是自含式语言、又是嵌入式语言。
SQL语言作为自含式语言，它能够用于联机交互的使用方式，用户可以在终端键盘上直接键入SQL命令对数据库进行操作； 作为嵌入式语言，SQL语句能够嵌入到高级语言(例如C、C++、Java)程序中，供程序员设计程序时使用。在两种不同的使用方式下，SQL语言的语法结构基本上是一致的，提供了极大的灵活性与方便性。
(5) 综合统一。
SQL语言集数据查询(Data Query)、数据操纵(Data Manipulation)、数据定义(Data Definition)和数据控制(Data Control)功能于一体。
(6) 语言简洁，易学易用。
SQL语言接近英语口语，易学使用，功能很强，由于设计巧妙，语言简洁，完成核心功能只用了9个动词，如表5.1所示。


表5.1SQL语言的动词



SQL语言的功能动词SQL语言的功能动词

数据定义CREATE,ALTER,DROP数据查询SELECT
数据操纵INSERT,UPDATE,DELETE数据控制GRANT,REVOKE

5.1.2PL/SQL预备知识
本节介绍使用PL/SQL语言的预备知识： PL/SQL的语法约定，在SQL Developer中执行PL/SQL语句。
1. PL/SQL的语法约定
PL/SQL的语法约定如表5.2所示，在PL/SQL不区分大小写。


表5.2PL/SQL的基本语法约定



语 法 约 定说明

大写PL/SQL 关键字
|分隔括号或大括号中的语法项，只能选择其中一项
［ ］可选项。不要键入方括号
续表



语 法 约 定说明

{ }必选项。不要键入方括号
［,...n］指示前面的项可以重复 n 次，各项由逗号分隔
［...n］指示前面的项可以重复 n 次，各项由空格分隔
［;］可选的TransactSQL语句终止符。不要键入方括号
<label>编写PL/SQL语句时设置的值
<label>（斜体，下画线）

语法块的名称。此约定用于对可在语句中的多个位置使用的过长语法段或语法单元进行分组和标记，可使用的语法块的每个位置由括在尖括号内的标签指示： <label>

2. 在SQL Developer中执行PL/SQL语句
在SQL Developer中执行PL/SQL语句的步骤如下。
(1) 选择“开始”→“所有程序”→OracleOraDB12Home1→“应用程序开发”→SQL Developer命令，启动SQL Developer界面。
(2) 在主界面中展开system_stsys连接，单击工具栏的按钮，主界面弹出SQL工作表窗口，在窗口中输入或粘贴要运行的PL/SQL语句，这里输入：

SELECT *

FROM student;

(3) 选中所有语句并单击工具栏的按钮或直接单击按钮，即执行语句，在“结果”窗口显示PL/SQL语句执行结果，如图5.1所示。


图5.1在SQL Developer的SQL工作表窗口中输入PL/SQL语句



提示： 在SQL工作表窗口中执行PL/SQL语句命令的方法有： 
(1) 选中所有语句后单击工具栏的按钮(“执行语句”按钮)或按F9键； 
(2) 直接单击按钮(“运行脚本”按钮)或按F5键。


5.2在PL/SQL中的数据定义语言
本节介绍在SQL*Plus中使用PL/SQL语句创建数据库、表空间与表等内容。
5.2.1数据库操作语句
使用PL/SQL中的DDL语言创建数据库的过程非常复杂，一般情况下应使用图形界面方式的数据库配置向导(Data Base Configuration Assistant, DBCA)创建数据库，不使用PL/SQL语句方式创建数据库。
1. 创建数据库
使用PL/SQL语句创建数据库步骤简介如下。
(1) 设定实例标识符。
建立数据库之前，必须先指定数据库实例的系统标识符，即SID，在SQL*Plus中使用以下命令设定SID： 

SET ORACLE_SID=stdb

(2) 设定数据库管理员的验证方法。
创建Oracle数据库必须经过数据库的验证手续，且被赋予适当系统权限后才可以建立。可以使用密码文件或操作系统的验证方法，下面是密码文件验证方法。

orapwd file=D:＼app＼tao＼oradata＼DATABASE＼PWDstdb.ora

Password=123456 entries=5

(3) 创建初始化参数。
创建新数据库之前必须新增或编辑的初始化参数如下。
 全局数据库名称。
 控制文件名称与路径。
 数据块大小。
 影响SGA容量的初始化参数。
 设定处理程序最大数目。
 设定空间撤销(Undo)管理方法。
(4) 启动SQL*Plus并以SYSDBA连接到Oracle实例。

sqlplus /nolog

connect system/Ora123456 as sysdba

(5) 启动实例。
在没有装载数据库情况下启动实例，通常只有在数据库创建期间或在数据库上实施维护操作时才会这么做，使用带有NOMOUNT选项的STARTUP命令。

STARTUP NOMOUNT pfile="D:＼app＼tao＼stdb＼pfile＼initstdb.ora"

(6) 使用CREATE DATABASE语句创建数据库。
在Oracle中创建数据库，使用CREATE DATABASE语句。
语法格式： 

CREATE DATABASE  <数据库名>

{USER SYS IDENTIFIED BY <密码>

| USER SYSTEM IDENTIFIED BY <密码>

| CONTROLFILE REUSE

| MAXDATAFILES <最大数据文件数>

| MAXINSTANCES <最大实例数>

| {ARCHIVELOG︱NO ARCHIVELOG}

| CHARACTER SET <字符集>

| NATIONAL CHARACTER SET <民族字符集>

| SET DEFAULT

{ BIGFILE | SMALLFILE } TABLESPACE

| ［ LOGFILE ［ GROUP <数字值> ］ <文件选项>

| MAXLOGFILES <数字值>

| MAXLOGMEMBERS <数字值>

| MAXLOGHISTORY <数字值>

| FORCE LOGGING

| DATAFILE <文件选项>

［ AUTOEXTEND ［ OFF︱ON ［ NEXT <数字值>［K∣M | G | T ］ 

MAXSIZE ［ UNLIMITED︱<数字值> ［K∣M | G | T ］］］］

| DEFAULT TABLESPACE <表空间名> ［DATAFILE <文件选项> ］

| ［ BIGFILE | SMALL］ UNDO TABLESPACE <表空间名> ［ DATAFILE <文件选项>］

| SET TIME_ZONE='<时区名>'

}... ;

其中： 

<文件选项>::=

('<文件路径>＼<文件名>') ［ SIZE <数字值> ［ K∣M | G | T］ ［ REUSE ］］,...n］


2. 修改数据库
修改数据库使用ALTER DATABASE语句。
语法格式： 

ALTER DATABASE <数据库名>

［ARCHIVELOG | NOARCHIVELOG］

［NO］ FORCE LOGGING

RENAME FILE '<文件名>'［,...n］ TO '<新文件名>' ［,...n ］

CREATE DATAFILE '<数据文件名>'

［ AS {'<新数据文件名>' ［ SIZE <数字值> ［K∣M | G | T ］］ ［ REUSE ］］［,...n］} | NEW ］

DATAFILE '<文件名>'  {ONLINE∣OFFLINE ［ FOR DROP］∣RESIZE <数字值> ［ K∣M | G | T］

| END BACKUP∣AUTOEXTEND {OFF∣ON ［NEXT <数字值> ［K | M］］ 

［MAXSIZE UMLIMITED∣<数字值> ［K∣M ］］ ］ }}

ADD LOGFILE '<文件名>' ［ SIZE <数字值> ［K∣M | G | T ］］ ［ REUSE ］］［,...n］

DROP LOGFILE '<文件名>'

...;

3. 删除数据库
删除数据库使用DROP语句。
语法格式： 

DROP DATABASE database_name


其中，database_name是要删除的数据库名称。
5.2.2表空间操作语句
下面介绍在SQL*Plus中使用PL/SQL中的DDL语言对表空间进行创建、管理和删除。
1. 创建表空间
创建表空间在SQL*Plus中使用CREATE TABLESPACE语句，创建的用户必须拥有CREATE TABLESPACE系统权限，在创建之前必须创建包含表空间的数据库。
语法格式： 

CREATE TABLESPACE <表空间名>

DATAFILE '<文件路径>＼<文件名>' ［SIZE <文件大小> ［ K∣M ］］ ［ REUSE ］

［ AUTOEXTEND ［ OFF∣ON ［ NEXT <磁盘空间大小> ［ K∣M ］］

［ MAXSIZE ［ UMLIMITED∣<最大磁盘空间大小> ［ K∣M ］ ］ ］ ］

［ MINMUM EXTENT <数字值>［ K | M ］ ］

［ DEFAULT <存储参数>］

［ ONLINE∣OFFLINE ］

［ LOGGING∣NOLOGGING ］

［ PERMANENT∣TEMPORARY ］

［ EXTENT MANAGEMENT ［ DICTIONARY∣LOCAL ［ AUTOALLOCATE∣UNIFORM ［ SIZE <数字值>［ K∣M ］ ］ ］ ］ ］

【例5.1】创建表空间testspace，大小为40MB，禁止自动扩展数据文件。

CREATE TABLESPACE testspace

LOGGING 

DATAFILE 'I:＼app＼ora＼oradata＼orcl＼testspace01.DBF' SIZE 40M 

REUSE AUTOEXTEND OFF;

该语句运行结果如图5.2所示。


图5.2创建表空间testspace


【例5.2】创建表空间newspace，允许自动扩展数据文件。

CREATE TABLESPACE newspace

LOGGING

DATAFILE 'I:＼app＼ora＼oradata＼orcl＼ newspace01.DBF' SIZE 40M

REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 300M

EXTENT MANAGEMENT LOCAL;

该语句运行结果如图5.3所示。


图5.3创建表空间newspace


2. 管理表空间
在SQL*Plus中使用ALTER TABLESPACE命令可以修改表空间或它的一个或多个数据文件、或为数据库中每一个数据文件指定各自的存储扩展参数值。
语法格式： 

ALTER TABLESPACE <表空间名>

［ ADD DATAFILE∣TEMPFILE  '<路径>＼<文件名>' ［ SIZE <文件大小> ［ K∣M ］ ］ 

［ REUSE ］

［ AUTOEXTEND ［ OFF∣ON ［ NEXT <磁盘空间大小>  ［ K∣M ］ ］ ］ ］

［MAXSIZE ［ UNLIMITED∣<最大磁盘空间大小> ［ K∣M ］ ］ ］

［ RENAME DATAFILE '<路径>＼<文件名>',...n TO '<路径>＼<新文件名>'',...n ］

［ DEFAULT STORAGE <存储参数>］

［ ONLINE∣OFFLINE ［ NORMAL∣TEMPORARY∣IMMEDIATE ］ ］

［ LOGGING∣NOLOGGING ］

［ READ ONLY∣WRITE ］

［ PERMANENT ］

［ TEMPORARY ］

【例5.3】通过ALTER TABLESPACE命令把一个新的数据文件添加到newspace表空间，并指定了AUTOEXTEND ON和MAXSIZE 300M。

ALTER TABLESPACE newspace

ADD DATAFILE 'I:＼app＼ora＼oradata＼orcl＼DATA02.DBF' SIZE 40M 

REUSE AUTOEXTEND ON NEXT 50M MAXSIZE 300M;

3. 删除表空间
在SQL*Plus中使用DROP TABLESPACE语句删除已经创建的表空间。其语法格式如下。
语法格式： 

DROP TABLESPACE <表空间名> 

［ INCLUDING CONTENTS ［ {AND | KEEP} DATAFILES ］

［ CASCADE CONSTRAINTS ］

］ ;

【例5.4】删除表空间newspace和其对应的数据文件。

DROP TABLESPACE newspace 

INCLUDING CONTENTS AND DATAFILES;

该语句运行结果如图5.4所示。


图5.4删除表空间newspace


5.2.3表操作语句
在SQL Developer中使用PL/SQL中的DDL语言对表进行创建、管理和删除介绍如下。
1. 创建表
使用CREATE TABLE语句创建表。
语法格式： 

CREATE TABLE ［<用户方案名>.］ <表名>

(

<列名1><数据类型>［DEFAULT <默认值>］［<列约束>］	

<列名2><数据类型>［DEFAULT <默认值>］［<列约束>］	

［,...n］

<表约束>［,...n］

)

［PCTFREE <数字值>］

［PCTUSED <数字值> ］

［INITRANS <数字值>］

［MAXTRANS <最大并发事务数>］

［TABLESPACE <表空间名>］

［STORGE <参数>］

［AS <子查询>］

【例5.5】使用PL/SQL语句，在stsys数据库中创建student表。
在stsys数据库中创建student表语句如下： 

CREATE TABLE student 

(

sno char(6) NOT NULL PRIMARY KEY,

sname char(12) NOT NULL,

ssex char(3) NOT NULL,

sbirthday date NOT NULL,

speciality char(18) NULL,

sclass char(6) NULL,

tc number NULL

);

启动SQL Developer界面，在主界面中展开system_stsys连接，单击工具栏的按钮，主界面弹出SQL工作表窗口，在窗口中输入上述语句，单击按钮，在“结果”窗口显示“CREATE TABLE成功”，如图5.5所示。


图5.5创建student表的表结构




提示： 
由一条或多条PL/SQL语句组成一个程序，通常以.sql为扩展名存储，称为sql脚本。SQL工作表窗口内的PL/SQL语句，可用“文件”菜单“另存为”命令命名并存入指定目录。


2. 修改表
使用ALTER TABLE语句用来修改表的结构。
语法格式： 

ALTER TABLE ［<用户方案名>.］ <表名>

［ ADD(<新列名> <数据类型> ［DEFAULT <默认值>］［列约束］,...n) ］/*增加新列*/

［ MODIFY(［ <列名> ［<数据类型>］ ［DEFAULT <默认值>］［列约束］,...n) ］ /*修改已有列的属性*/

［ STORAGE <存储参数> ］　　/*修改存储特征*/

［<DROP子句> ］	　　/*删除列或约束条件*/

其中，<DROP子句>用于从表中删除列或约束。
语法格式： 

<DROP子句>::=

DROP 

{

COLUMN <列名>

∣PRIMARY ［KEY］

∣UNIQUE (<列名>,...n) 

∣CONSTRAINT <约束名>

∣［ CASCADE ］

}

【例5.6】使用ALTER TABLE语句修改Stsys数据库中的student表。
(1) 在student表中增加一列remarks(备注)。

ALTER TABLE student 

ADD remarks varchar(100);

(2) 在student表中删除列remarks。

ALTER TABLE student 

DROP COLUMN remarks;

3. 删除表
使用DROP TABLE语言删除表。
语法格式： 

DROP TABLE table_name;

其中，table_name是要删除的表的名称。
【例5.7】删除Stsys数据库中newstudent表(已创建)。

DROP TABLE newstudent;

5.3在PL/SQL中的数据操纵语言
下面介绍在SQL Developer中，使用PL/SQL中的数据操纵语言DML对表进行插入记录、修改记录和删除记录。
5.3.1插入语句
INSERT语句用于向数据库的表插入一行，由VALUES给定该行各列的值。
语法格式： 

INSERT INTO <表名>［(<列名1>,<列名2>,...n)］

VALUES(<列值1>,<列值2>,...n)

其中，列值表必须与列名表一一对应，且数据类型相同。向表的所有列添加数据时，可以省略列名表，但列值表必须与列名表顺序和数据类型一致。

注意： 

使用PL/SQL语言进行插入、修改和删除后，为将数据的改变保存到数据库中，应使用COMMIT命令进行提交，使用方法如下： 

COMMIT； 

本书后面的SQL语句都省略COMMIT命令，运行时请读者添加。


【例5.8】向student表插入表4.1各行数据。
向student表插入表4.1各行数据的语句如下。

INSERT INTO student VALUES('181001','宋德成','男',TO_DATE('19971105','YYYYMMDD'),'计算机','201805',52);

INSERT INTO student VALUES('181002','何静','女',TO_DATE('19980427','YYYYMMDD'),'计算机','201805',50);

INSERT INTO student VALUES('181004','刘文韬','男',TO_DATE('19980513','YYYYMMDD'),'计算机','201805',52);

INSERT INTO student VALUES('184001','李浩宇','男',TO_DATE('19971024','YYYYMMDD'),'通信','201836',50);

INSERT INTO student VALUES('184002','谢丽君','女',TO_DATE('19980116','YYYYMMDD'),'通信','201836',48);

INSERT INTO student VALUES('184003','陈春玉','女',TO_DATE('19970809','YYYYMMDD'),'通信','201836',52);

使用SELECT语句查询插入的数据： 

SELECT *

FROM student;

查询结果： 

SNOSNAMESSEXSBIRTHDAYSPECIALITYSCLASS TC

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

181001宋德成 男1997-11-05 计算机20180552

181002何静 女1998-04-27 计算机20180550

181004刘文韬 男1998-05-13 计算机20180552

184001李浩宇 男1997-10-24 通信20183650

184002谢丽君 女1998-01-16 通信20183648

184003陈春玉 女1997-08-09 通信20183652

5.3.2修改语句
UPDATE语句用于修改表中指定记录的列值。
语法格式： 

UPDATE <表名>

SET <列名>={<新值>|<表达式>} ［,...n］

［WHERE <条件表达式>］

其中，在满足WHERE子句条件的行中，将SET子句指定的各列的列值设置为SET指定的新值，如果省略WHERE子句，则更新所有行的指定列值。

注意： UPDATE语句修改的是一行或多行中的列。


【例5.9】在student表中，将所有学生的学分增加2分。

UPDATE student

SET tc=tc+2;

5.3.3删除语句
1.  DELETE语句

DELETE语句用于删除表中的一行或多行记录。
语法格式： 

DELETE FROM <表名>

［WHERE <条件表达式>］

该语句的功能为从指定的表或中删除满足WHERE子句条件行，若省略WHERE子句，则删除所有行。

注意： DELETE语句删除的是一行或多行。如果删除所有行，表结构仍然存在，即存在一个空表。



【例5.10】在student表中，删除学号为184003的行。

DELETE FROM student

WHERE sno='184003';

2. TRANCATE TABLE语句
当需要删除一个表里的全部记录，使用TRUNCATE TABLE语句，它可以释放表的存储空间，但此操作不可回退。
语法格式： 

TRUNCATE TABLE <表名>

5.4在PL/SQL中的数据查询语言
PL/SQL语言中最重要的部分是它的查询功能，PL/SQL的SELECT语句具有灵活的使用方式和强大的功能，能够实现选择、投影和连接等操作。
语法格式： 

SELECT <列>/*SELECT子句，指定列*/

FROM<表或视图> /*FROM子句，指定表或视图*/

［ WHERE<条件表达式> ］ /*WHERE子句，指定行*/

［ GROUP BY <分组表达式> ］/*GROUP BY子句，指定分组表达式*/

［ HAVING <分组条件表达式> ］/*HAVING子句，指定分组统计条件*/

［ ORDER BY <排序表达式> ［ ASC | DESC ］］ /*ORDER子句，指定排序表达式和顺序*/

5.4.1投影查询
投影查询用于选择列，投影查询通过SELECT语句的SELECT子句来表示。
语法格式： 

SELECT ［ ALL | DISTINCT ］ <列名列表>

其中，<列名列表>指出了查询结果的形式，其格式为： 

{*/*选择当前表或视图的所有列*/

|<表名>|<视图>|.*/*选择指定的表或视图的所有列*/

|{|<列名>|<表达式>}

［［ AS ］ <列别名>］/*选择指定的列，为列指定别名*/

| <列标题> =<列名表达式>/*选择指定的列并更改列标题，为列指定别名*/

}［,... n ］

1. 投影指定的列
使用SELECT语句可选择表中的一个列或多个列，如果是多个列，各列名中间要用逗号分开。
语法格式： 

SELECT <列名1> ［ , <列名2> ［,...n］ ］

FROM <表名>

［WHERE <条件表达式>］

该语句的功能为在FROM子句指定表中检索符合条件的列。
【例5.11】查询student表中所有学生的学号、姓名和班号。

SELECT sno, sname, sclass

FROM student;

查询结果： 

SNOSNAMESCLASS 

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

181001宋德成 201805

181002何静 201805

181004刘文韬 201805

184001李浩宇 201836

184002谢丽君 201836

184003陈春玉 201836

2. 投影全部列
在SELECT子句指定列的位置上使用*号时，则为查询表中所有列。
【例5.12】查询student表中所有列。

SELECT *

FROM student;

该语句与下面语句等价

SELECT sno, sname, ssex, sbirthday, speciality, sclass, tc

FROM student;

查询结果： 

SNOSNAMESSEXSBIRTHDAYSPECIALITYSCLASS TC

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

181001宋德成 男1997-11-05 计算机20180552

181002何静 女1998-04-27 计算机20180550

181004刘文韬 男1998-05-13 计算机20180552

184001李浩宇 男1997-10-24 通信20183650

184002谢丽君 女1998-01-16 通信20183648

184003陈春玉 女1997-08-09 通信20183652 

3. 修改查询结果的列标题
为了改变查询结果中显示的列标题，可以在列名后使用AS <列别名>。
【例5.13】查询student表中所有学生的学生的sno、sname、speciality，并将结果中各列的标题分别修改为学号、姓名、专业。

SELECT sno AS 学号, sname AS 姓名, speciality AS 专业

FROM student;

查询结果： 

学号姓名专业 

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

181001宋德成计算机

181002何静计算机

181004刘文韬计算机

184001李浩宇通信

184002谢丽君通信

184003陈春玉通信

4. 计算列值
使用SELECT子句对列进行查询时，可以对数字类型的列进行计算，可以使用加(＋)、减(-)、乘(*)、除(/)等算术运送符，SELECT子句可使用表达式。
语法格式： 

SELECT <表达式> ［ , <表达式> ］

5. 去掉重复行
去掉结果集中的重复行可使用DISTINCT关键字。
语法格式： 

SELECT DISTINCT <列名> ［ , <列名>...］

【例5.14】查询student表中sclass列，消除结果中的重复行。

SELECT DISTINCT sclass

FROM student;

查询结果： 

SCLASS 

---------

201836

201805

5.4.2选择查询
选择查询用于选择行，选择查询通过WHERE子句实现，WHERE子句通过条件表达式给出查询条件，该子句必须紧跟FROM子句之后。
语法格式： 

WHERE <条件表达式>

其中，<条件表达式>为查询条件，格式为： 

<条件表达式>::=

{ ［ NOT ］ <判定运算> | (<条件表达式> ) }

［ { AND | OR } ［ NOT ］ { <判定运算> | (<条件表达式>) } ］

［ ,...n ］

其中，<判定运算>的结果为TRUE、FALSE或UNKNOWN，其格式为： 

<判定运算>： ： =

{<表达式1> { = | < | <= | > | >= | <> | != } <表达式2>/*比较运算*/

| <字符串表达式1> ［ NOT ］ LIKE <字符串表达式2> ［ ESCAPE '<转义字符>' ］

/*字符串模式匹配*/

| <表达式> ［ NOT ］ BETWEEN <表达式1> AND <表达式2>/*指定范围*/

| <表达式> IS ［ NOT ］ NULL /*是否空值判断*/

| <表达式> ［ NOT ］ IN ( <子查询> | <表达式> ［,...n］ )/*IN子句*/

| EXIST ( <子查询> ) /*EXIST子查询*/

}


说明：
(1) 判定运算包括比较运算、模式匹配、指定范围、空值判断、子查询等，判定运算的结果为TRUE、FALSE或UNKNOWN。
(2) 逻辑运算符包括AND(与)、OR(或)、 NOT(非)，NOT、AND和OR的使用是有优先级的，三者之中，NOT优先级最高，AND次之，OR优先级最低。
(3) 条件表达式可以使用多个判定运算通过逻辑运算符成复杂的查询条件。
(4) 字符串和日期必须用单引号括起来。





注意： 在SQL中，返回逻辑值的运算符或关键字都称为谓词。


1. 表达式比较
比较运算符用于比较两个表达式值，共有7个运算符： =(等于)、<(小于)、<=(小于等于)、>(大于)、>=(大于等于)、<>(不等于)、!=(不等于)。
语法格式： 

<表达式1> { = | < | <= | > | >= | <> | != } <表达式2>

【例5.15】查询student表中班号为201805或性别为女的学生。

SELECT *

FROM student

WHERE sclass='201805' or ssex='女';

查询结果： 

SNOSNAMESSEXSBIRTHDAYSPECIALITYSCLASSTC

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

181001宋德成 男 1997-11-05计算机 201805 52

181002何静 女 1998-04-27计算机 201805 50

181004刘文韬 男 1998-05-13计算机 201805 52

184002谢丽君 女 1998-01-16通信 201836 48

184003陈春玉 女 1997-08-09通信 201836 52

2. 指定范围
BETWEEN、NOT BETWEEN、IN是用于指定范围的三个关键字，用于查找字段值在(或不在)指定范围的行。
当要查询的条件是某个值的范围时，可以使用BETWEEN关键字。BETWEEN关键字指出查询范围。
语法格式： 

<表达式> ［ NOT ］ BETWEEN <表达式1> AND <表达式2>

【例5.16】查询score表成绩为86、92、95的记录。

SELECT * 

FROM score

WHERE grade in (86,92,95);

查询结果： 

SNOCNOGRADE

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

181002100486

184001400292

184001800186

184003800195

181004120192

【例5.17】查询student表中不在1998年出生的学生情况。

SELECT *

FROM student

WHERE sbirthday NOT BETWEEN TO_DATE('19980101','YYYYMMDD') AND 

TO_DATE('19981231','YYYYMMDD');

查询结果： 

SNOSNAMESSEXSBIRTHDAYSPECIALITYSCLASSTC

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

181001宋德成 男 1997-11-05计算机 201805 52

184001李浩宇 男 1997-10-24通信 201836 50

184003陈春玉 女 1997-08-09通信 201836 52 

3. 模式匹配
模式匹配使用LIKE谓词，LIKE谓词用于指出一个字符串是否与指定的字符串相匹配，其运算对象可以是char、varchar2和date类型的数据，返回逻辑值TRUE或FALSE。
语法格式： 

<字符串表达式1> ［ NOT ］ LIKE <字符串表达式2> ［ ESCAPE '<转义字符>' ］

在使用LIKE谓词时，<字符串表达式2>可以含有通配符，通配符有以下两种： 
%： 代表0或多个字符； 
_： 代表一个字符。
LIKE匹配中使用通配符的查询也称模糊查询。
【例5.18】查询student表中姓谢的学生情况。

SELECT *

FROM student

WHERE sname LIKE '谢%';

查询结果： 

SNOSNAMESSEXSBIRTHDAYSPECIALITYSCLASSTC

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

184002谢丽君 女 1998-01-16通信 20183648

4. 空值判断
判定一个表达式的值是否为空值时，使用IS NULL关键字。
语法格式： 

<表达式> IS ［ NOT ］ NULL

【例5.19】查询已选课但未参加考试的学生情况。

SELECT * 

FROM score

WHERE grade IS null;

查询结果： 

SNOCNOGRADE

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

1840028001

5.4.3分组查询和统计计算
查询数据常常需要进行统计计算，本节介绍使用聚合函数、GROUP BY子句、HAVING子句进行统计计算的方法。
1. 聚合函数
聚合函数实现数据的统计计算，用于计算表中的数据，返回单个计算结果。聚合函数包括COUNT、SUM、AVG、MAX、MIN等函数，下面分别介绍。
(1) COUNT函数。
COUNT函数用于计算组中满足条件的行数或总行数。
语法格式： 

COUNT ( { ［ ALL | DISTINCT ］ <表达式> } | * )

其中，ALL表示对所有值进行计算，ALL为默认值，DISTINCT指去掉重复值，COUNT函数用于计算时忽略NULL值。
【例5.20】求学生的总人数。

SELECT COUNT(*) AS 总人数

FROM student;

该语句采用COUNT(*)计算总行数，总人数与总行数一致。
查询结果： 

总人数

----------

6


【例5.21】查询201836班学生的总人数。

SELECT COUNT(*) AS 总人数

FROM student

WHERE sclass='201836';

该语句采用COUNT(*)计算总人数，并用WHERE子句指定的条件进行限定为201836。
查询结果： 

总人数

----------

3

(2) SUM和AVG函数。
SUM函数用于求出一组数据的总和，AVG函数用于求出一组数据的平均值，这两个函数只能针对数值类型的数据。
语法格式： 

SUM / AVG ( ［ ALL | DISTINCT ］ <表达式> )


其中，ALL表示对所有值进行计算，ALL为默认值； DISTINCT指去掉重复值； SUM/AVG函数用于计算时忽略NULL值。
【例5.22】查询1201课程总分。

SELECT SUM(grade) AS课程1201总分

FROM score

WHERE cno='1201';

该语句采用SUM ()计算课程总分，并用WHERE子句指定的条件进行限定为1201课程。
查询结果： 

课程1201总分

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

509

(3) MAX和MIN函数。
MAX函数用于求出一组数据的最大值，MIN函数用于求出一组数据的最小值，这两个函数都可以适用于任意类型数据。
语法格式： 

MAX / MIN ( ［ ALL | DISTINCT ］ <表达式> )


其中，ALL表示对所有值进行计算，ALL为默认值； DISTINCT指去掉重复值； MAX/MIN函数用于计算时忽略NULL值。
【例5.23】查询8001课程的最高分、最低分、平均成绩。

SELECT MAX(grade) AS 课程8001最高分, MIN(grade) AS 课程8001最低分, AVG(grade) AS 课程8001平均成绩

FROM score

WHERE cno='8001';

该语句采用MAX求最高分、MIN求最低分、AVG求平均成绩。
查询结果： 

课程8001最高分课程8001最低分课程8001平均成绩 

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

95 85 88.8


2. GROUP BY子句
GROUP BY子句用于指定需要分组的列。
语法格式： 

GROUP BY ［ ALL ］ <分组表达式> ［,...n］

其中，分组表达式通常包含字段名，ALL显示所有分组。

注意： 如果SELECT子句的列名表包含聚合函数，则该列名表只能包含聚合函数指定的列名和GROUP BY子句指定的列名。聚合函数常与GROUP BY子句一起使用。


【例5.24】查询各门课程的最高分、最低分、平均成绩。

SELECT cno AS 课程号, MAX(grade)AS 最高分,MIN (grade)AS 最低分, AVG(grade)AS 平均成绩

FROM score

WHERE NOT grade IS null

GROUP BY cno;

该语句采用MAX、MIN、AVG等聚合函数，并用GROUP BY子句对cno (课程号)进行分组。
查询结果： 

课程号最高分最低分 平均成绩 

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

80019585 88.8

400292788.6E+01

120193758.5E+01

10049486 90

3. HAVING子句
HAVING子句用于对分组按指定条件进一步进行筛选，过滤出满足指定条件的分组。
语法格式： 

［ HAVING <条件表达式>］

其中，条件表达式为筛选条件，可以使用聚合函数。

注意： HAVING子句可以使用聚合函数，WHERE子句不可以使用聚合函数。


当WHERE子句、GROUP BY子句、HAVING子句、ORDER BY子句在一个SELECT语句中时，执行顺序如下： 
(1) 执行WHERE子句，在表中选择行； 
(2) 执行GROUP BY子句，对选取行进行分组； 
(3) 执行聚合函数； 
(4) 执行HAVING子句，筛选满足条件的分组； 
(5) 执行ORDER BY子句，进行排序。

注意： HAVING子句要放在GROUP BY子句的后面，ORDER BY子句放在HAVING子句后面。


【例5.25】查询平均成绩在90分以上的学生的学号和平均成绩。

SELECT sno AS 学号, AVG(grade) AS 平均成绩

FROM score

GROUP BY sno

HAVING AVG(grade)>90;

该语句采用COUNT聚合函数、WHERE子句、GROUP BY子句、HAVING子句； 
查询结果： 

学号 平均成绩 

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

1810019.3E+01

1840039.2E+01

【例5.26】查询至少有5名学生选修且以8开头的课程号和平均分数。

SELECT cno AS 课程号, AVG (grade) AS 平均分数

FROM score

WHERE cno LIKE '8%'

GROUP BY cno

HAVING COUNT(*)>5;

该语句采用AVG聚合函数、WHERE子句、GROUP BY子句、HAVING子句。
查询结果： 

课程号平均分数

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

800188.8

5.4.4排序查询
在Oracle中，ORDER BY子句用于对查询结果进行排序。
语法格式： 

［ ORDER BY { <排序表达式> ［ ASC | DESC ］ } ［ ,...n ］

其中，排序表达式，可以是列名、表达式或一个正整数，ASC表示升序排列，它是系统默认排序方式，DESC表示降序排列。

提示： 排序操作可对数值、日期、字符三种数据类型使用，ORDER BY子句只能出现在整个SELECT语句的最后。


【例5.27】将201836班级的学生按出生时间降序排序。

SELECT *

FROM student

WHERE sclass='201836'

ORDER BY sbirthday DESC;

该语句采用ORDER BY子句进行排序。
查询结果： 

SNOSNAMESSEXSBIRTHDAYSPECIALITYSCLASS TC 

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

184002 谢丽君 女 1998-01-16通信 201836 48

184001 李浩宇 男 1997-10-24通信 201836 50

184003 陈春玉 女 1997-08-09通信 201836 52


5.5小结
本章主要介绍了以下内容。
(1) SQL(Structured Query Language)语言是目前主流的关系型数据库上执行数据操作、数据检索以及数据库维护所需要的标准语言，是用户与数据库之间进行交流的接口，许多关系型数据库管理系统都支持SQL语言，但不同的数据库管理系统之间的SQL语言不能完全通用，Oracle数据库使用的SQL语言是Procedural Language/SQL(PL/SQL)。
(2) 通常将SQL语言分为以下4类： 数据定义语言(Data Definition Language, DDL)、数据操纵语言(Data Manipulation Language, DML)、数据查询语言(Data Query Language, DQL)、数据控制语言(Data Control Language, DCL)。
SQL语言具有高度非过程化、应用于数据库的语言、面向集合的操作方式、既是自含式语言又是嵌入式语言、综合统一、语言简洁和易学易用等特点。
(3) 在PL/SQL中的数据定义语言DDL。
DDL中的数据库操作语句有： 创建数据库用CREATE DATABASE语句、修改数据库用ALTER DATABASE语句、删除数据库用DROP DATABASE语句。
DDL中的表空间操作语句有： 创建表空间用CREATE TABLESPACE语句、修改表空间用ALTER TABLESPACE语句、删除表空间用DROP TABLESPACE语句。
DDL中的表操作语句有： 创建表用CREATE TABLE语句、修改表用ALTER TABLE语句、删除表用DROP TABLE语句。
(4) 在PL/SQL中的数据操纵语言DML。
在表中插入记录用INSERT语句，在表中修改记录或列用UPDATE语句，在表中删除记录用DELETE语句。
(5) 在PL/SQL中的数据查询语言DQL。
DQL是PL/SQL语言的核心，DQL使用SELECT语句，它包含SELECT子句、FROM子句、WHERE子句、GROUP BY子句、HAVING子句和ORDER BY子句等。
5.6创建表实验
1. 实验目的及要求
(1) 理解数据库和表的基本概念。
(2) 掌握使用PL/SQL语句创建表的操作，具备编写和调试创建表、修改表、删除表的代码的能力。
2. 验证性实验
使用PL/SQL语句创建表、修改表、删除表。
在stsys数据库中分别创建一个Employee表(员工表)和Department表(部门表)，其表结构分别如表5.3和表5.4所示。


表5.3Employee表的表结构



列名数 据 类 型允许null值是否主键说明

EmplIDvarchar(4)主键员工号
EmplNamevarchar(12)姓名
Sexvarchar(3)性别
Birthdaydate出生日期
Addressvarchar(30)√地址
Wagesnumber工资
DeptIDvarchar(4)√部门号



表5.4Department表的表结构



列名数 据 类 型允许null值是否主键说明

DeptID varchar(4)主键部门号
DeptName varchar(30)部门名称

(1) 创建Employee表。

CREATE TABLE Employee 

(

EmplID varchar2(4) NOT NULL PRIMARY KEY,

EmplName varchar2(12) NOT NULL,

Sex varchar2(3) NOT NULL, 

Birthday date NOT NULL,

Address varchar2(30) NULL,

Wages number NOT NULL,

DeptID varchar2(4) NULL

);

(2) 创建Department表。

CREATE TABLE Department

(

DeptID varchar2(4) NOT NULL PRIMARY KEY,

DeptName varchar2(30) NOT NULL 

);

（3) 将Employee表的EmplName字段的数据类型改为char (12)。

ALTER TABLE Employee 

MODIFY (EmplName char(12));

(4) 将Employee表的Address字段删除。

ALTER TABLE Employee 

DROP COLUMN Address;

(5) 在Employee表中增加名为Address的字段，数据类型为char(30)。

ALTER TABLE Employee 

ADD Address char(30);

(6) 删除Employee表。

DROP TABLE Employee;


3. 设计性试验
在stsys数据库中分别创建一个StudentInfo表(学生信息表)和ScoreInfo表(成绩信息表)，表结构分别如表5.5和表5.6所示。


表5.5StudentInfo表的表结构



列名数 据 类 型允许null值是否主键说明

StudentIDvarchar2(6)主键学号
Namevarchar2(12)姓名
Sexvarchar2(3)性别
Birthdaydate出生日期
SpecialityVarchar2(18)√专业
Addressvarchar2(30)√家庭地址



表5.6ScoreInfo表的表结构



列名数 据 类 型允许null值是否主键说明

StudentIDvarchar2 (6)主键学号
CourseIDvarchar2(4)主键课程号
GradeNumber√成绩


编写和调试创建表、修改表、删除表的代码，完成以下操作。
(1) 创建StudentInfo表。
(2) 创建ScoreInfo表。
(3) 将StudentInfo表的Name字段的数据类型改为char (12)。
(4) 将StudentInfo表的Address字段删除。
(5) 在StudentInfo表中增加名为Phone的字段，数据类型为char(20)。
(6) 删除StudentInfo表。
4. 观察与思考
(1) 在创建表的语句中，NOT NULL的作用是什么？
(2) 一个表可以设置几个主键？
(3) 主键列能否修改为NULL？
5.7表数据的插入、修改和删除实验
1. 实验目的及要求
(1) 掌握表数据的插入、修改和删除操作。
(2) 具备编写和调试插入数据、修改数据和删除数据的代码的能力。
2. 验证性实验
TeacherInfo表(教师信息表)的表结构如表5.7所示，CourseInfo表(课程信息表)的表结构如表5.8所示，在stsys数据库中，分别创建TeacherInfo表和CourseInfo表。


表5.7TeacherInfo表的表结构



列名数 据 类 型允许null值是否主键说明

TeacherIDvarchar2(6)主键教师编号
TeacherNamevarchar2(12)姓名
TeacherSexvarchar2(3)性别
TeacherBirthdaydate出生日期
Schoolvarchar2(18)学院
Addressvarchar2(30)√地址



表5.8CourseInfo表的表结构



列名数 据 类 型允许null值是否主键说明

CourseIDvarchar2 (4)主键课程号
CourseNamevarchar2 (24)课程名
Creditnumber√学分

TeacherInfo表的样本数据，如表5.9所示。


表5.9TeacherInfo表的样本数据



教师编号姓名性别出生日期学院地址

100005李慧强男19680925计算机学院北京市海淀区
100024刘松男19760217计算机学院北京市海淀区
400021陈霞飞女19751207通信学院上海市黄浦区
800004刘泉明男19780816数学学院广州市越秀区
120007张莉女19820321外国语学院成都市锦江区

CourseInfo表的样本数据，如表5.10所示。


表5.10CourseInfo表的样本数据



课程号课程名学分

1004数据库系统4
1025物联网技术3
4002数字电路3
8001高等数学4
1201英语4

按照下列要求完成表数据的插入、修改和删除操作。
(1) 向TeacherInfo表插入样本数据。

INSERT INTO TeacherInfo values('100005','李慧强','男',TO_DATE('19680925','YYYYMMDD'),'计算机学院','北京市海淀区');

INSERT INTO TeacherInfo values('100024','刘松','男',TO_DATE('19760217','YYYYMMDD'),'计算机学院','北京市海淀区');

INSERT INTO TeacherInfo values('400021','陈霞飞','女',TO_DATE('19751207','YYYYMMDD'),'通信学院','上海市黄浦区');

INSERT INTO TeacherInfo values('800004','刘泉明','男',TO_DATE('19780816','YYYYMMDD'),'数学学院','广州市越秀区');

INSERT INTO TeacherInfo values('120007','张莉','女',TO_DATE('19820321','YYYYMMDD'),'外国语学院','成都市锦江区');

(2) 向CourseInfo表插入样本数据。

INSERT INTO CourseInfo VALUES('1004','数据库系统',4);

INSERT INTO CourseInfo VALUES('1025','物联网技术',3);

INSERT INTO CourseInfo VALUES('4002','数字电路',3);

INSERT INTO CourseInfo VALUES('8001','高等数学',4);

INSERT INTO CourseInfo VALUES('1201','英语',4,);

(3) 更新教师编号为120007的记录，将出生日期改为“19830917”。

UPDATE TeacherInfo

SET TeacherBirthday='1983-09-17'

WHERE TeacherID='120007';

(4) 将性别为“男”的记录的家庭住址都改为“上海市浦东新区”。

UPDATE TeacherInfo

SET address='上海市浦东新区'

WHERE TeacherSex='男';

(5) 删除教师编号为400021的记录。

DELETE FROM TeacherInfo

WHERE TeacherID='400021';

3. 设计性试验
Goods表(商品表)的表结构如表5.11所示，在stsys数据库中，创建Goods表。



表5.11Goods表的表结构



列名数 据 类 型允许null值是否主键说明

GoodsIDvarchar2(4)主键商品号
GoodsNamevarchar2(30)商品名称
Classificationvarchar2(24)商品类型
UnitPricenumber单价
StockQuantitynumber库存量


Goods表的样本数据如表5.12所示。


表5.12Goods表的样本数据



商品号商 品 名 称商 品 类 型单价库存量

1001Microsoft Surface Pro 4笔记本电脑548812
1002Apple iPad Pro平板电脑588812
3001DELL PowerEdgeT130服务器669910
4001EPSON L565打印机18998

编写和调试表数据的插入、修改和删除的代码，完成以下操作。
(1) 向Goods表插入样本数据。
采用三种不同的方法，将Goods表的样本数据插入Goods表中。
① 省略列名表，插入记录。

INSERT INTO Goods VALUES('1001','Microsoft Surface Pro 4','笔记本电脑',5488,12);

② 不省略列名表，插入记录。

INSERT INTO Goods(GoodsID, GoodsName, Classification, UnitPrice, StockQuantity)

VALUES('1002','Apple iPad Pro','平板电脑',5888,12);

③ 同时插入多条记录。

INSERT INTO Goods VALUES('3001','DELL PowerEdgeT130','服务器',6699,10);

INSERT INTO Goods VALUES('4001','EPSON L565','打印机',1899,8);

(2) 将Microsoft Surface Pro 4的类型改为“笔记本平板电脑二合一”。
(3) 将EPSON L565的库存量改为10。
(4) 删除商品类型为平板电脑的记录。
4. 观察与思考
DROP语句与DELETE语句有何区别？
5.8查 询 实 验
1. 实验目的及要求
(1) 理解SELECT语句的语法格式。
(2) 掌握SELECT语句的操作和使用方法。
(3) 具备编写和调试SELECT语句以进行数据库查询的能力。
2. 验证性实验
对stsys数据库的员工表Employee表和部门表Department上进行信息查询。Employee表的样本数据如表5.13所示，其中，员工号、姓名、性别、出生日期、地址、工资、部门号的列名分别为EmplID、EmplName、Sex、Birthday、Address、Wages、DeptID。


表5.13Employee表的样本数据



员工号姓名性别出生日期地址工资部门号

E001刘思远男19801107北京市海淀区4100D001
E002何莉娟女19870718上海市浦东区3300D002
E003杨静女19840225上海市浦东区3700D003
E004王贵成男19740912 北京市海淀区6800D004
E005孙燕女19850223NULL3600D001
E006周永杰男19791028 成都市锦江区4300NULL

Department表的样本数据如表5.14所示，其中，部门号、部门名称的列名分别为DeptID、DeptName。


表5.14Department表的样本数据



部门号部门名称部门号部门名称

D001 销售部D004 经理办
D002 人事部D005 物资部
D003 财务部

查询要求如下。
(1) 使用两种方式，查询Employee表的所有记录。
① 使用列名表。

SELECT EmplID, EmplName, Sex, Birthday, Address, Wages, DeptID

FROM Employee;

② 使用*。

SELECT *

FROM Employee;

(2) 查询Employee表有关员工号、姓名和地址的记录。

SELECT EmplID, EmplName, Address

FROM Employee;

(3) 从Department表查询部门号、部门名称的记录。

SELECT DeptID, DeptName

FROM Department;

(4) 通过两种方式查询Goods表中价格为1500~4000元的商品。
① 通过指定范围关键字。

SELECT *

FROM Goods

WHERE UnitPrice BETWEEN 1500 AND 4000;

② 通过比较运算符。

SELECT *

FROM Goods

WHERE UnitPrice>=1500 AND UnitPrice<=4000;

(5) 查询地址是北京的员工的姓名、出生日期和部门号。

SELECT EmplID, EmplName, DeptID

FROM Employee

WHERE address LIKE '北京%';

(6) 查询各个部门的员工人数。

SELECT DeptID AS 部门号, COUNT(EmplID) AS 员工人数

FROM Employee

GROUP BY DeptID;

(7) 查询每个部门的总工资和最高工资。

SELECT DeptID AS 部门号, SUM(Wages) AS 总工资, MAX(Wages) AS 最高工资

FROM Employee

GROUP BY DeptID;

(8) 查询员工工资，按照工资从高到低的顺序排列。

SELECT *

FROM Employee

ORDER BY Wages DESC;

3. 设计性试验
对stsys数据库的学生信息表StudentInfo和成绩信息表ScoreInfo上进行信息查询。StudentInfo表的样本数据如表5.15所示，其中，学号、姓名、性别、出生日期、专业、地址的列名分别为StudentID、Name、Sex、Birthday、Speciality、Address。


表5.15StudentInfo表的样本数据



学号姓名性别出生日期专业家 庭 地 址

181001成志强男19980817计算机北京市海淀区
181002孙红梅女19971123计算机成都市锦江区
181003朱丽女19980219计算机北京市海淀区
184001王智勇男19971205电子信息工程NULL
184002周潞潞女19980224电子信息工程上海市浦东区
184004郑永波男19970919电子信息工程上海市浦东区

ScoreInfo表的样本数据如表5.16所示，其中，学号、课程号、成绩的列名分别为StudentID、CourseID、Grade。


表5.16ScoreInfo表的样本数据



学号课程号成绩学号课程号成绩

181001100495184001800185
1810021004 851840028001NULL
181003100491184004800194
184001400293181001120192
184002400276181002120178
184004400288181003120194
181001800194184001120185
181002800189184002120179
181003800186184004120194


编写和调试查询语句的代码，完成以下操作。
(1) 使用两种方式，查询StudentInfo表的所有记录。
① 使用列名表。
② 使用*。
(2) 查询ScoreInfo表的所有记录。
(3) 查询高等数学成绩低于90分的成绩信息。
(4) 使用两种方式，查询地址为上海市浦东区和成都市锦江区学生的信息。
① 使用IN关键字。
② 使用OR关键字。
(5) 使用两种方式，查询分数为90到95分的成绩信息。
① 使用BETWEEN AND关键字查询。
② 使用AND关键字和比较运算符。
(6) 查询每个专业有多少人。
(7) 查询高等数学的平均成绩、最高分和最低分。
(8) 将英语成绩按从高到低排序。
4. 观察与思考
(1) LIKE的通配符“%”和“_”有何不同？
(2) IS能用“=”来代替吗？
(3) “=”与IN在什么情况下作用相同？
(4) 空值的使用，可分为哪几种情况？
(5) 聚集函数能否直接使用在SELECT子句、WHERE子句、GROUP BY子句、HAVING子句之中？
(6) WHERE子句与HAVING子句有何不同?
(7) COUNT (*)、COUNT (列名)、COUNT (DISTINCT列名)三者的区别是什么？ 

习题5
一、 选择题
1. 以下语句执行出错的原因是。

SELECT sno AS 学号, AVG(grade) AS 平均分FROM score GROUP BY 学号;


A.  不能对grade(学分)计算平均值
B.  不能在GROUP BY子句中使用别名
C.   GROUP BY子句必须有分组内容
D.   score表没有sno列
2. 统计表中记录数，使用聚合函数。
A.   SUMB.  AVG
C.   COUNTD.  MAX
3. 在SELECT语句中使用关键字去掉结果集中的重复行。
A.   ALLB.  MERGE
C.   UPDATED.  DISTINCT
4. 查询course表的记录数，使用语句。
A.   SELECT COUNT(cno) FROM course
B.  SELECT COUNT(tno) FROM course
C.   SELECT MAX(credit) FROM course
D.   SELECT AVG(credit) FROM course
二、 填空题
1. 在DDL语句中，语句可以创建表、ALTER TABLE语句可以修改表、DROP TABLE语句可以删除表。
2. 在DML语句中，INSERT语句可以在表中插入记录、语句可以在表中修改记录、DELETE语句可以在表中删除记录。
3. SELECT语句有SELECT、FROM、、GROUP BY、HAVING、ORDER BY  6个子句。
4. WHERE子句可以接收子句输出的数据。
三、 问答题
1. SQL语言可分为哪几类？简述各类包含的语句。
2. SELECT语句包含哪几个子句？简述各个子句的功能。
3. 什么是LIKE谓词？通配符有哪几种？各有何功能？
4. 什么是聚合函数？简述聚合函数的函数名称和功能。
5. 在一个SELECT语句中，当WHERE子句、GROUP BY子句和HAVING子句同时出现在一个查询中时，SQL的执行顺序如何？
四、 应用题
1. 查询score表中学号为121004，课程号为1201的学生成绩。
2. 列出goods表的商品名称、商品价格和打7折后的商品价格。
3. 查询student表中姓周的学生情况。
4. 查询通信专业的最高学分的学生的情况。
5. 查询1004课程的最高分、最低分及平均成绩。
6. 查询至少有3名学生选修且以4开头的课程号和平均分数。
7. 将计算机专业的学生按出生时间升序排列。
8. 查询各门课程最高分的课程号和分数，并按分数降序排列。
9. 查询选修课程3门以上且成绩在85分以上的学生的情况。