第2章结构化查询语言.docx

资源描述

第2章结构化查询语言.docx

《第2章结构化查询语言.docx》由会员分享，可在线阅读，更多相关《第2章结构化查询语言.docx（13页珍藏版）》请在冰豆网上搜索。

第2章结构化查询语言.docx

第2章结构化查询语言

学习目标

了解：

SQL语言的组成、功能和特点；

理解：

表、索引、视图、存储过程等数据库对象的概念和作用；

掌握：

数据库定义、更新语言、数据查询语言等的语句格式和使用方法。

SQL 有两种使用方法:

交互式SQL:

一种与用户交互的方式，适合非计算机专业人员。

宿主型SQL:

一种作为子语言嵌入到其它程序设计语言中，适合于程序设计人员用高级语言编写应用程序并与数据库打交道,嵌入到主语言中使用。

2.1SQL 语言概述

一、SQL具有四个主要的功能

1. 数据定义（DDL）：

主要用于创建、修改、删除数据库及其中的对象。

如：

表、视图、索引等；

2. 数据操纵（DML）：

主要用于对关系中的数据进行增、删、改和更新等操作；

3. 数据查询：

主要用于对数据进行检索查询等；

4. 数据控制（DCL）：

主要用于对数据库的访问权限的授予或撤消、完整性规则描述、事务控制等操作。

二、语言的特点

1. 非过程化语言:

一次处理一条记录,对数据提供自动导航。

2. 统一的语言:

对所有用户都适用。

提供的命令有：

查询数据；在表中插入、修改和删除记录；建立、修改和删除数据对象；控制对数据和数据对象的存取；保证数据库一致性和完整性等。

3. 是所有关系数据库的公共语言。

2.2 基本数据类型

主要数据类型:

数值型、字符型、日期型、二进制型、位型和双字节型。

1.数值数据类型:

用于存储数值型数据

整数型：

包括Bitint、Int、Smallint、Tinyint,一般使用Int型。

精确数值：

用于存储带小数位的数据，包括Decimal、Numeric，一般使用后者。

浮点数值：

（Float,Real）存储非常大带小数的数据。

货币型：

带四位小数的浮点数。

2. 字符型：

用于存储字符型数据

Char型：

长度固定；

Varchar型：

长度不固定；

Text型：

存储字符型数据非常大，长度超过8000个字符时使用，最大可存储2G的数据。

3.日期时间型：

存储日期和时间的组合数据。

Datetime:

1753年1月1日至9999年12月31日，占8个字符的存储空间。

SmallDatetime:

1900年1月1日至2079年6月6日，占4个字符的存储空间。

4. 二进制：

存储图形图像、WORD文档等。

包括:

Image、Binary、varbinary。

其中，Image用于存储照片、音频和视频等文件。

5. 位型：

Bit逻辑数据，用于存储1或0，只有一位字节。

6.特殊数据类型：

用于存储特殊用途的数据。

2.3 数据定义语言

一、数据库：

1. 数据库的概念：

对象的集合，主要包括表、索引、视图、存储过程等。

数据库在磁盘上以文件形式存储，由数据文件和日志文件组成。

先建立数据库，然后才能建立对象,如：

表、索引等。

2. 数据库的定义：

CREATE DATABASE<数据库名>

功能：

建立一个空数据库。

该语句执行后，可以使用“USE<数据库>”命令把已建立的一个数据库置为当前数据库。

此后所有操作，包括建立、修改、删除基本表等操作均对当前数据库进行操作。

例：

CreateDATABASE教学管理库

3. 删除数据库:

DROP DATABASE<数据库名>

功能：

删除（撤消）数据库；同时将删除数据库中所有对象。

例：

DROPDATABASE教学管理库

二、表

1.表的概念：

表是数据库的操作对象之一，是数据库最基本、最重要的对象。

其他对象，如查询、窗体和报表都是在表的基础上建立和使用的。

表由行（记录）和列（字段）组成。

学号

课程号

成绩

001

K01

001

K02

002

K01

002

K02

数据表具有的属性：

表名：

表的唯一标识；

记录：

表中的每行为一条记录；

字段：

表的列为字段，每个字段需定义字段名称、字段类型、宽度等；

主关键字：

能够唯一地标识表的每条记录的字段或字段组合。

如：

学生表的主关键字为“学号”，选课表的“学号”和“课程号”的组合为关键字。

域：

每个字段的取值范围。

2. 创建表结构

命令格式：

CREATETABLER<基本表名>（<列名1><列的类型><列的完整性>，<列名2><列的类型><列的完整性>,…）

功能：

在当前或给定的数据库中定义一个表结构（关系模式）

列定义包括：

属性名，数据类型，完整性约束。

常用的表中数据类型有以下几种：

char（n）:

定长度字符型，如：

姓名char（10）；

varchar：

变长度字符型；

Int：

整型，占4个字节；

Float：

浮点数（实数）占4或8个字节；

Date：

日期型yyyy-mm-dd,（yyyy/mm/dd）占4个或8个字节。

列级完整性约束:

有六种

DEFAULT<常量表达式>：

默认值约束。

NULL/NOTNULL空值/非空值：

对于非主属性，若不注明此约束，则隐含为允许空值约束。

PRIMARYKEY：

主码约束。

UNIQUE：

单值约束，惟一值约束。

REFERNCES<父表名>（<主码>）:

外码约束。

CHECK（<逻辑表达式>）:

检查约束，取值限制。

举例：

创建学生基本表

CREATETABLE学生（

学生号char（7）primarykey，

姓名char（6），

性别char

（2），

出生年月datetime,

专业char（10）,

年级int）

表级完整性约束:

有四种

PRIMARYKEY（<列名>,…）主码约束:

注明一个或多个列为主码。

UNIQUE（<列名>,…）单值约束：

注明一个或同时若干个列为单值。

FOREINGKEY<列名>,…REFRENCES<父表名>（<主码列名>）外码约束：

注明一个或同时若干个列为外码。

CHECK（<逻辑表达式>）检查约束：

注明每行中一个或若干个列的取值限制。

除默认值约束、空值/非空值约束外，对于其它约束，若只涉及到一个列时，则可以作列级完整性约束，又可以作表级完整性约束。

若涉及到多个列时，则只能作为表级完整性约束。

举例：

创建选课基本表

CREATETABLE选课（

学生号char（7），

课程号char（4）,

成绩intcheck（成绩>=0and成绩<=100）,

primarykey（学生号,课程号）,

Foreignkey（学生号）refrences学生（学生号），

Foreignkey（课程号）refrences课程（课程号）

）

3．表的删除

DROPTABLE<表名>

三、索引

1.索引的概念:

索引提供一种快速访问数据的途径,当数据库很大时，在表中建立若干个索引，会加快查询。

索引有以下特点：

（1）索引是在表中的某列上建立的数据库对象，它和表分开存储，它的建立或撤消对数据的内容没有影响。

（2）索引一经创建，就完全由系统自动选择和维护。

（3）表中的记录数量越多，越有必要建立索引。

（4）索引是为加快查询速度，但其影响表中数据的插入、删除和修改。

2.创建原则

（1）表中的记录数量越多，越有必要建立索引。

（2）索引是为加快查询速度，但其影响表中数据的插入、删除和修改。

（3）索引是建立在基本表列上的对象，通常需要在经常出现在WHERE、ORDERBY、GROUPBY子句中的列建立索引。

3.不易创建索引的情况

包含太多重复值的列、查询中很少被引用的列、值特别长的列、具有很多NULL值的列、需要经常更新的列、记录较少的表等。

4. 索引的类型

单列索引：

对基本表的某一单独列进行索引，通常是对主关键字建立。

唯一索引：

建立唯一索引后，则不允许在该列上插入相同的值。

复合索引：

对表的两个或多个列上建立索引。

聚集索引：

改变表中记录的物理存储顺序，即表中记录的物理排列顺序不再按插入的顺序排列。

非聚集索引：

不改变表中记录的物理存储顺序，即表中记录的物理顺序和索引顺序不同。

5. 聚集索引和非聚集索引对比:

（1）存取速度:

聚集索引查询速度快,但降低更新速度。

（2）索引的数据:

一个表只能建立一个聚集索引,但可以建立多个非聚集索引。

（3）所用空间:

非聚集索引需要较多的空间。

6. 创建索引:

CREATE[UNIQUE][CLUSTERED]INDEX<索引名>ON<表名>（<列名1><次序>…。

UNIQUE：

创建唯一索引；

CLUSTERED：

创建聚集索引；

次序：

表示排列次序，ASC表示升序；DESC表示降序。

如：

CREATEINDEX学号索引ON学生表（学号）

7. 删除索引：

DROPINDEX<索引名>，…一次可以删除一个或多个索引。

四、视图

视图是建立在基本表上的一个虚表，它的结构和内容都来自基本表，它依据基本表存在而存在。

视图的列可以来自一个基本表或多个基本表。

视图的建立和删除只影响视图本身，不影响对应的基本表，而对视图的更新（插入、删除和修改）直接影响基本表。

对视图做的最多的是查询操作。

当删除表时同时删除在表建立的视图。

视图能够增强安全性，使数据库中一些保密的数据对无关人员成为不可见的，从而不能随意查询。

创建视图：

CREATEVIEW<视图名>（<列名>，…）AS；

为当前数据库根据SELECT子句查询结果建立视图。

举例：

把计算机系学生的学号和姓名创建为视图：

CREATEVIEWCSStudentAS

SELECTStudentNo,StudentName

FROMStudent

WHEREDept=‘计算机系'；

可把CSStudent看作是一个虚拟的表。

删除（撤消）视图：

DROPVIEW<视图名>；

举例：

DROPVIEWCSStudent 撤消视图CSStudent。

五、存储过程

1.存储过程：

存储过程是一组预先编译好的TransactSQL代码，是一种可执行的永久存储在数据的SQL代码中。

可以作为一个独立的数据库对象被用户使用。

2.存储过程的作用:

它可以接收参数,并以输出参数的形式返回多个参数给调用它的过程或批处理，使用存储过程优点：

（1）执行速度快：

存储过程在创建时就经过了语法检查和性能优化，因此在执行时不必再重复这些步骤。

存储过程在经过第一次调用后，就驻留在内存中，不必再经过编译和优化，所以执行速度快。

（2）模块化的程序设计：

可以被多次调用，用户可以独立于应用程序而对存储过程进行修改，增加了数据库应用程序的易修改特征。

（3）减少网络流量:

存储过程可以包含大量的TransactSQL语句,但在进行调用时,客户端只需要使用一个语句就可以实现了,大大减少了网络上数据的传输量。

（4）保证系统的安全性:

存储过程存放在数据库服务器端,对于某些关键数据,可以设置成只通过存储过程来访问,不允许用户直接使用TransactSQL语句或企业管理器对数据进行访问。

3.创建存储过程：

CREATEPROC存储过程名ASSQL语句

SQL语句是利用TransactSQL编写的程序。

4.执行存储过程:

如果对存储过程的调用是批处理的第一条语句，则可直接使用存储过程的名字调用。

如果不是第一条语句，调用的语法：

EXEC存储过程名。

举例:

创建USER_PROC1存储过程。

CREATEPROCUSER_PROC1

SELECT*FROM学生表

六、触发器：

触发器是一个特殊的存储过程,它基于一个表的创建,但可以针对多个表进行操作,主要用来保证数据的完整性。

一张表可以创建多年触发器,用户可以针对UPDATE、DELETE、INSERT语句分别设置触发器，当用户进行UPDATE、DELETE、INSERT等操作后，这些事先定义好的触发器对象就会被触发，并按事先定义好的规则自动执行。

创建触发器：

CREATETRIGGER触发器名ON表名/视图名OF[DELETE，INSERT，UPDATE] ASSQL语句

主要参数：

DELETE、INSERT、UPDATE指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。

必须至少指定一个选项。

举例：

当对学生表执行DELETE语句时,同时删除成绩表中学号相同的记录。

CREATETRIGGER删除学号ON学生表

FORDELETE

DECLARE@Mynochar（4）

Select@Myno=学号FROMDELETED

DELETEFROM 成绩表WHERE学号=@Myno

RETURN

2.4数据操纵语言

1.插入记录命令格式：

INSERT[INTO]<基本表名>（<列名>,…）VALUES（列值,…）

向表中插入完整的记录时,可省略列名列表。

例如：

学生关系（学生号，姓名，性别，专业）

INSERTinto学生（学生号，姓名，性别，专业）VALUES（‘001'，‘李宏'，‘女'，‘计算机'）

与下面语句功能相同

INSERTinto学生VALUES（‘001'，‘李宏'，‘女'，‘计算机'）。

2.修改记录命令格式：

UPDATE<表名>SET<列名>=<表达式>,…WHERE<逻辑表达式>；

功能：

按条件修改表中的一些列值。

SET：

要修改的列值；

WHERE：

修改记录条件，省略参数将修改表中所有记录。

例：

将所有计算机系学生的年龄增加1岁，并且把系名改为“计算机科学系”

UPDATEStudentSETAge=Age+1,Dept=‘计算机科学系'WHEREDept=‘计算机科学系'

3.删除记录命令格式：

DELETEFROM<表名>WHERE<逻辑表达式>；

例:

删除学号为9900203的学生记录

DELETEFROMStudentWHERE学生号=‘9900203’

2.5 数据查询语言

SQL中最简单的查询就是从某个关系中查找满足某种条件的元组，类似于关系代数中的选择运算。

一、查询语言格式

Select字段列表[Into]<基本表>]

From<源表名>

[Where<查询条件>]

[GROPby<分组列名1>[Having<查询条件>]

[Orderby<排序表达式>[ASC][DESC]

二、简单查询

假设教学数据库包括：

学生、课程和成绩三个基本表。

1.查询指定的字段：

用“，”隔开需要查询的字段。

例：

查询学生表所有学生的学号和姓名。

Select学号,姓名

FROM学生

2.通配符“*”:

查询所有字段

例：

查询学生表所有学生的所有字段。

Select *

FROM学生

3.基于字段的表达式:

使用字段构成需要查询的表达式，如：

例：

查询所有学生的学号和出生年月。

Select学号,STR（YEAR（出生日期）,4）+‘年’+STR（MONTH（出生日期）,2）+‘月’AS出生年月

FROM学生

说明：

AS选项定义表达式的列名，若省略AS选项，则字段名作为列名使用。

4.ALL/DISTINCT：

默认值为ALL,表示允许查询结果中出现内容重复的行；若选择DISTINCT则表示不允许出现内容重复的行。

例：

查询学生表中专业数。

Select DISTINCT专业

FROM学生

5.WHERE选项：

定义基本表之间的连接条件和对记录的筛选条件。

连接条件通常为等值连接,筛选条件是从源表中选择出满足条件的行，筛选条件可以是一般比较式，也可以是通过逻辑运算符连接的逻辑表达式。

1）使用BETWEEN短语查询。

短语格式：

BETWEEN<开始值>AND<结束值>，

例：

查询年龄在19岁-21岁的学生。

select*from学生where年龄19and21

2）使用NULL查询：

例：

查询成绩不为空值的所有记录。

select*from成绩whereNOT（成绩ISNULL）

3）使用日期进行查询：

例：

查询1985年1月1日后出生的学生。

select*from学生出生日期>'1985-01-01'

4）使用IN查询：

IN（常量表）

例：

查询年龄为19岁或21岁的记录。

select*from学生where年龄IN（19,21）

5）使用LIKE查询：

列名LIKE表达式（用%、—构成表达式）

例：

查询姓“王”的所有学生。

select * from 学生where姓名LIKE‘王%'

6.ORDERBY<排序列名1>，<排序列名2>：

将查询结果按排序列进行排序，查询结果首先按排序列名1的值排序，若该列的值相同，再按排序列名2的值排序。

排序方式有两种，关键字DESC表示降序，ASC表示升序，若省略表示升序。

例：

从教学库中查询出所有学生的学号和姓名，查询结果按学生号升序排列。

select学生号,姓名

from学生

orderby学生号

7.统计函数的使用：

COUNT（[ALL/DISTINCT]<列名>|*）：

统计指定列的值个数；

MAX（<列名>）求出对应的数值、字符或日期列的最大值；

MIN（<列名>）求出对应的数值、字符或日期列的最小值；

AVG（<列名>）求出对应的数值列的平均值；

SUM（<列名>）求出对应的数值列的总和。

例:

统计学生人数。

SelectCOUNT（*）AS学生人数

FROM学生

例：

统计每个专业的学生人数。

SelectCOUNT（DISTINCT专业）AS专业数

FROM学生

8.GROUPBY<分组列名>：

按分组列名对查询结果中的所有元组进行分组，使得分组列值相同的为一组，形成结果表中的一个元组。

select选项中只能包括分组列名和统计函数。

例：

查询每个专业的学生数。

Select专业as专业名，count（专业）学生数

From学生

Groupby专业

三、复杂查询：

涉及多个表的查询

1. 连接查询：

两表按公共字段等值连接查询。

例：

查询学生的学号、姓名、课程号和成绩。

查询需要用到两张表（学生，成绩）

SELECT学号、姓名、课程号，成绩

FROM学生表，成绩表

WHERE学生表.学号=成绩表.学号

例：

查询出所有计算机专业学生的学号,姓名,课程号,课程名和成绩。

查询条件包括连接条件和选择条件。

SELECT学号、姓名、课程号，成绩

FROM学生表，成绩表

WHERE学生表.学号=成绩表.学号 AND 专业=‘计算机'

2. 嵌套查询:

一个SELECT查询嵌套在另一个查询语句中。

例1：

查询所有被女生选出的课程号和课程名。

SELECT课程号,课程名FROM课程表

WHERE课程号IN（SELECT课程号FROM成绩单

WHERE学号IN（SELECT学号FROM学生表WHERE性别=‘女'））

第一步：

最内层的查询功能查询出所有女生的学号；

第二步：

第二层查询功能是从成绩单表中查询出女生选修课程的课程号；

第三步：

最外层查询功能是从课程表中查询出被女生选修的所有课程的课程名和课程号。

例2：

求成绩小于总平均成绩的学生学号、姓名、专业。

SELECT学号,姓名，专业FROM学生表

WHERE学号IN（SELECTDISTINCT学号FROM成绩单

WHERE成绩IN（SELECTAVG（成绩）FROM成绩表））

第一步:

计算全体学生的总平均成绩；

第二步:

在成绩单中求出所有成绩小于总平成绩的学号；

第三步:

用IN求出所有学号在第二步查出学号集合中的学生学号、姓名和专业。

2.6 数据控制语言

1.事务:

事务是并发控制的基本单位，它反映了现实世界中需要以一个完整的单位提交的一项工作。

SQLServer通过事务机制，将逻辑相关的一组操作捆绑在一起，以便服务器保持数据的完整性。

例如：

对学生表和成绩表进行更新语句:

UPDATE学生表SET学号=‘0401’WHERE学号=‘0301’

UPDATE成绩表SET学号=‘0401’WHERE学号=‘0301’

两条语句要么都执行要么都不执行，否则会使数据库中的数据混乱。

2. 事务处理控制语句：

开始一个事务：

BEGINTRAN事务名

提交一个事务：

COMMITTRAN事务名

回滚一个事务：

ROLLBACKTRAN事务名

在数据库应用程序设计中,事务控制语句一般用法是:

第1步:

BEGINTRAN事务名；

第2步:

对数据库进行增、删、改等操作；

第3步：

提交事务或回滚。

基本逻辑是，如果没有发生异常情况，则通过COMMIT语句提交事务，确认第2步的操作；否则执行ROLLBACK回滚命令，撤消第2步的所有操作。

例：

USE教学管理

SELECT*INT

展开阅读全文