测试人员应该掌握的基本SQL语句.docx

测试人员应该掌握的基本SQL语句.docx

《测试人员应该掌握的基本SQL语句.docx》由会员分享，可在线阅读，更多相关《测试人员应该掌握的基本SQL语句.docx（19页珍藏版）》请在冰豆网上搜索。

测试人员应该掌握的基本SQL语句

SQL发展历程　　考核要求：

达到“识记”

　　层次知识点：

SQL的发展历程

　　SQL：

结构式查询语言，虽然名为查询语言，实际上具有定义、查询、更新和控制等多种功能。

　　SQL数据库的体系结构

　　考核要求：

达到“领会”

　　层次知识点：

三级结构的理解

　　SQL数据库的体系结构也是三级结构，但术语与传统关系模型术语不同，在SQL中，关系模式称为“基本表”，存储模式称为“存储文件”，子模式称为“视图”，元组称“行”，属性称“列”。

　　SQL数据库体系的结构要点如下：

（1）一个SQL数据库是表的汇集。

（2）一个SQL表由行集构成，行是列的序列，每列对应一个数据项。

　　（3）表或者是基本表，或者是视图。

基本表是实际存储在数据库中的表，视图由是由若干基本表或其他视图构成的表的定义。

　　（4）一个基本表可以跨一个或多个存储文件，一个存储文件也可存放一个或多个基本表。

存储文件与物理文件对应。

　　（5）用户可以用SQL语句对表进行操作，包括视图和基本表。

　　（6）SQL的用户可以是应用程序，也可以是终端用户。

　　SQL的组成

　　考核要求：

达到“识记”

　　层次知识点：

四个组成部分

　　SQL由四部分组成：

（1）数据定义：

SQLDDL.定义SQL模式，基本表、视图和索引。

（2）数据操纵：

SQLDML.包括数据查询和数据更新（增、删、改）。

　　（3）数据控制：

包括对基本表和视图的授权、完整性规则的描述，事务控制等。

　　（4）嵌入式SQL的使用规定。

系统分析师是计算机行业的高级人才，是一个大型软件项目的核心领导者，他的主要职责是对软件项目进行整体规划、需求分析、设计软件的核心架构、指导和领导项目开发小组进行软件开发和软件实现，并对整个项目进行全面的管理工作。

系统分析师的工作职责决定了他必须是计算机行业各个领域的精通者，因此一个合格的系统分析师，能够精通各种计算机前沿理论、具体的软硬件开发技术、大型数据库的知识、项目的整体规划和框架设计、模块式设计和开发技术、数字化建设知识等等。

系统分析师具备在一个信息化项目从立项到正式上线整个过程中，在过程的各个不同阶段担任不同的核心角色的能力，其中最为重要的能力就是系统架构的整体设计能力和详细设计能力，这个能力直接关系到一个软件项目的成败。

　　系统分析师所具备的职业能力和素质主要有：

精通计算机行业的前沿理论，精通代表主流开发思想的程序开发语言，精通建设信息系统所要求的各种具体技术，熟悉应用领域的业务，能分析用户的需求和约束条件，写出信息系统需求规格说明书，制定项目开发计划，协调信息系统开发与运行所涉及的各类人员，能指导制定企业的战略数据规划，组织开发信息系统，能评估和选用适宜的开发方法和工具，能按照标准规范写系统分析、设计文档，能对开发过程进行质量控制与进度控制，能具体指导项目开发，具有高级工程师的实际工作能力和业务水平。

　　系统分析师的基本职责是从事管理信息系统的定制、企业资源管理系统的设计开发及市场评估策划，能独立翻译、阅读国外技术资料，理解商务逻辑和客户需求，有管理信息系统的设计、项目设计能力、开发进度的估计能力、控制力，具有良好的理解力和逻辑分析能力以及表达能力、足够的沟通能力，具备基本文档写作能力。

　　在日常工作当中，系统分析师通常都是本单位的技术骨干，主要担任项目的主导者和领导者的工作。

在政府机关，系统分析师通常负责数字化城市、电子政务、公共政务网等电子政府统一规划的规划与建设工作；在高校、研究所等科研机构，系统分析师通常担任计算机前沿理论的研究、计算机专业、信息化管理专业、电子商务及电子政务等专业的教学、数字化校园的规划与建设、大型集中式教务数据库的建设、教务系统的开发与建设等工作；在非IT企业，系统分析师通常主要负责本企业的电子商务系统的规划与建设、大型信息化系统（如MIS、ERP等）的规划、建设与开发等工作；在IT企业，系统分析师通常担任首席分析师和项目经理的工作，主要负责中大型软件项目的规划、建设、软件架构的整体设计与详细设计、开发模式的设计、项目开发工作的指导和监督、系统的整体测试工作、项目的全面管理及进度管理等。

　　总而言之，系统分析师通常在本单位担任独当一面的技术骨干，同时也应当承担起更大的工作责任，充分发挥自己的特长，更好地为单位作出应有的贡献。

编辑本段业务范围

　　根据时下IT企业所涉足开发的领域，其业务范围主要包括：

仓储管理系统、报关业务系统、销售统计与管理系统、财务管理系统、物流管理指挥系统、楼宇智能化管理系统以及各种数据查询统计与分析、业务流程控制系统、模拟考试系统、人力资源管理分析系统等等，由此而产生各种信息系统分析师人才品种，即系统分析专家，他们不仅承担着为客户设计开发软件新品的业务指导任务，而且向客户提供二次开发的技术支持和培训顾问服务，既是IT企业中的技术骨干和将才，又是IT企业软件新品市场前景的预测者和参与营销的市场策划者，从这个角度看，又是企业的智囊高参和运筹帷幄的帅才，堪称具有国际视野的高级复合型人才。

　　熟悉应用领域的业务，能分析用户的需求和约束条件，写出信息系统需求规格说明书，制定项目开发计划，协调信息系统开发与运行所涉及的各类人员，能指导制定企业的战略数据规划，组织开发信息系统，能评估和选用适宜的开发方法和工具，能按照标准规范写系统分析、设计文档，能对开发过程进行质量控制与进度控制，能具体指导项目开发，具有高级工程师的实际工作能力和业务水平。

（1）具有系统工程的基础知识；

（2）掌握开发信息系统的综合技术知识（硬件、软件、网络、数据库）；

　　（3）熟悉企业和政府信息化建设，并具有组织信息化战略规划的知识；

　　（4）熟悉掌握信息系统开发过程和方法；

　　（5）熟悉信息系统开发标准；

　　（6）掌握信息安全的相关知识与技术；

　　（7）理解软件质量保证的手段；

　　（8）具有经济与管理科学的相关基础知识，熟悉有关的法律法规；

　　（9）具有大学本科的数学基础；

　　（10）熟练阅读和正确相关领域的英文文献。

编辑本段未来展望

　　近年来，我国政府及工商企业使用电子计算器处理作业快速成长，人才需求愈来愈多，惟因学校培育的科班信息人才供不应求．

　　因而造就其它科系更多的出路，其中尤以商业科系为主。

展望台后在经济结构改变．各行业规模愈趋扩大，并须建立企业计算机化管理制度的情势下，对系统分析师的需求更将有增无减，因此．凡受过信息专业教育或训练，具有电子计算器专门知识及企业管理观念的青年，其就业前途是非常乐观的。

目录  

一、DDL—数据定义语言（CREATE，ALTER，DROP，DECLARE）  

二、DML—数据操纵语言（SELECT，DELETE，UPDATE，INSERTINTO）  

三、DCL—数据控制语言（GRANT，REVOKE）

四、下半部分内容（主要是PL/SQL:

函数，存储过程，事务等）

说明：

本文档的使用对象是对SQL有一些了解的软件测试人员，我只是把我知道的知识结合网上的资料进行二次总结，不正之处望多请教。

本文档配置附有SQL范例脚本。

一、DDL数据定义语言

首先,简要介绍基础语句，作为测试人员一般测试时，已经由数据库设计师建好了数据库，数据库设计师可能也不用语句的方式来建表，但我们应该能看懂各语句的使用格式，语句的含义，有兴趣再作深入了解。

1、创建数据库

CREATEDATABASE[database-name]

2、删除数据库

DROPDATABASEdbname1,dbname2…

3、备份数据库

---创建备份数据的device

USEmaster

EXECsp_addumpdevice'disk','testBack','c:

\mssql7backup\'

---开始备份

BACKUPDATABASEpubsTOtestBack  

4、创建表

createtabletabname（col1type1[notnull][primarykey],col2type2[notnull],..）

例如:

CREATETABLES

（SNOCHAR（10）NOTNULL,

SNVARCHAR（20）,

AGEINT,

SEXCHAR

（2）DEFAULT'男',

DEPTVARCHAR（20））;

根据已有的表创建新表：

A：

createtabletab_newliketab_old（使用旧表创建新表）

B：

createtabletab_newasselectcol1,col2…fromtab_olddefinitiononly

5、删除表

droptabletabname  

6、增加字段

Altertabletabname

ADD<列名><数据类型>[NULL|NOTNULL]

7、修改字段

ALTERTABLE<表名>

ALTERCOLUMN<列名><数据类型>[NULL|NOTNULL]

8、删除字段

ALTERTABLE<表名>

DROPCOLUMN<列名><数据类型>[NULL|NOTNULL]

9、添加主键

Altertabletabnameaddprimarykey（col）  

10、删除主键

Altertabletabnamedropprimarykey（col）  

11、创建索引

create[unique]indexidxnameontabname（col….）  

12、删除索引

dropindexidxname

注：

索引是不可更改的，想更改必须删除重新建。

13、创建视图

createviewviewnameas[selectstatement]

14、删除视图

dropviewviewname

二、DML—数据操纵语言

1、数据查询

数据查询是数据库中最常见的操作。

在本文档里将作重点介绍。

SQL语言提供SELECT语句，通过查询操作可得到所需的信息。

SELECT语句的一般格式为：

SELECT〈列名〉[{，〈列名〉}]

FROM〈表名或视图名〉[{，〈表名或视图名〉}]

[WHERE〈检索条件〉]

[GROUPBY<列名1>[HAVING<条件表达式>]]

[ORDERBY<列名2>[ASC|DESC]];

SELECT语句的执行过程是:

根据WHERE子句的检索条件，从FROM子句指定的基本表或视图中选取满足条件的元组，再按照SELECT子句中指定的列，投影得到结果表。

如果有GROUP子句，则将查询结果按照<列名1>相同的值进行分组。

如果GROUP子句后有HAVING短语，则只输出满足HAVING条件的元组。

如果有ORDER子句，查询结果还要按照<列名2>的值进行排序。

、查询指定列

SELECT<列名>FROM<表名或视图名>

、查询全部列

SELECT*FROM<表名或视图名>

或SELECT<全部列名>FROM<表名或视图名>

、取消相同取值的行

在查询结果中有可能出现取值完全相同的行了。

SELECTDISTINCT<列名>FROM<表名或视图名>

、比较大小

比较运算符有    =，>，>=，<=，<，<>，!

>，!

<

NOT+上述比较运算符

SELECT<列名>FROM<表名或视图名>WHERE<列名>[比较运算符]<比较的值>

、多重条件查询

当WHERE子句需要指定一个以上的查询条件时，则需要使用逻辑运算符AND、OR和NOT将其连结成复合的逻辑表达式。

其优先级由高到低为：

NOT、AND、OR，用户可以使用括号改变优先级。

SELECT<列名>FROM<表名或视图名>WHERE<条件1>AND<条件1>OR<条件1>…

、确认范围查询

用于确定范围运算符有：

BETWEEN…AND…和NOTBETWEEN…AND…

SELECT<列名>FROM<表名或视图名>WHERE<列名>[NOT]BETWEEN  值1AND值2

这与下等价

SELECT<列名>FROM<表名或视图名>WHERE<列名>>=值1AND<列名><=值2

SELECT<列名>FROM<表名或视图名>WHERE<列名><值1OR<列名>>值2

、确认集合

确定集合符号：

IN，NOTIN

SELECT<列名>FROM<表名或视图名>WHERE<列名>[NOT]IN  （常量1，常量2，…，常量n）

字符匹配查询

字符匹配查询符号：

LIKE，NOTLIKE

SQLServer2000支持如下四种通配符：

_（下划线）：

匹配任意一个字符；

%（百分号）：

匹配O个或多个字符；

[]：

匹配[]中的任意一个字符。

如[acdg]表示匹配a或c或d或g，如果[]中的字符是有序的，则可以使用连字符一来简化[]中的内容，例如[abcde]可简写为：

[a-e]；

[^]：

不匹配[]中的任意一个字符。

如[^acdg]表示不匹配a、c、d、g，如果[^]中的字符是有序的，也可以使用简化形式例如[^abcde]可简写为：

[^a-e]。

SELECT<列名>FROM<表名或视图名>WHERE<列名>[NOT]LIKE<匹配字符串>

空值查询

空值不同于零和空格，它不占任何存储空间。

判断某个值是否为NULL值，不能使用普通的比较运算符（一、!

一等），而只能使用专门的判断NULL值的子句来完成。

SELECT<列名>FROM<表名或视图名>WHERE<列名>IS[NOT]NULL

常用库函数及统计汇总查询

常用的库函数

AVG：

按列计算平均值

SUM：

按列计算值的总和

MAX：

求一列中的最大值

MIN：

求一列中的最小值

COUNT：

按列值计算个数

总数：

selectcount（field1）astotalcountfromtable1

求和：

selectsum（field1）assumvaluefromtable1

平均：

selectavg（field1）asavgvaluefromtable1

最大：

selectmax（field1）asmaxvaluefromtable1

最小：

selectmin（field1）asminvaluefromtable1

注1：

SQL规定，当使用计算函数时，列名不能与计算函数一起使用（除非他们出现在其他集合中）。

例如查询年龄最大的学生的姓名和年龄，如下写法是错误的：

SELECT姓名，MAX（年龄）FROMStudent

注2：

计算函数不能出现在WHERE子句中。

  ．

例如查询年龄最大的学生的姓名如下写法是错误的：

SELECT姓名FROMStudentWHERE年龄=MAX（年龄）

正确的命令应为:

  SELECT姓名，年龄FROMStudent

Where  年龄=（selectmax（年龄）fromstudent）

分组查询

SELECT<列名>FROM<表名或视图名>

GROUPBY<分组依据列>[，…n]

[HAVING<组提取条件>]

注1：

分组依据列不能是text、ntext、image和bit类型的列。

注2：

有分组时，查询列表中的列只能取自分组依据列（计算函数中的列除外）

对查询结果进行排序

SELECT<列名>FROM<表名或视图名>

ORDERBY<列名>[ASClDESC][，…n]

数据表连接查询

A、    INNERJOIN：

这是最普通的联接类型。

只要在这两个表的公共字段之中有相符值，内部联接将组合两个表中的记录。

SELECTfields

FROMtable1INNERJOINtable2

ONcompoprAND

ONcompoprOR

ONcompopr];

B、leftouterjoin：

左外连接（左连接）：

结果集包括连接表的匹配行，也包括左连接表的所有行。

SQL:

select,,,,,fromaLEFTOUTJOINbON=

C：

rightouterjoin:

右外连接（右连接）：

结果集包括连接表的匹配连接行，也包括右连接表的所有行。

D：

fullouterjoin：

全外连接：

不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。

使用TOP限制结果集

使用TOP谓词时注意最好与ORDERBY子句一起使用，因为这样的前几名才有意义。

但当使用WITHTIES时，要求必须使用ORDERBY子句。

TOP谓词写在SELECT单词的后边，查询列表的前边。

使用TOP谓词的格式为：

  TOPn[percent]withties]

  其中：

n为非负整数。

  TOPn：

表示取查询结果的前n行；

  TOPnpercent：

表示取查询结果的前n％行；  

  Withties：

表示包括并列的结果。

将查询结果存入表中

INTO子句的语法格式为：

  INTO新表名

INTO子句跟在SELECT子句之后、FROM子句之前。

SELECT<列名>INTO新表名FROM。

新表名是要存放查询结果的表名，SELECTINTO语句包含两个操作：

首先按查询列表创建新表，然后执行查询语句，并将结果保存到新表中。

  用INTO子句创建的新表可以是永久表，也可以是临时表。

临时表又分为两种：

局部临时表和全局临时表。

局部临时表要在表名前加#，它只能用在当前的连接中；全局临时表要在表名前加##，它的生存期为创建全局临时表的连接的生存期

合并查询

使用UNION的格式为：

  SELECT语句1

  UNION

  SELECT语句2

  UNION[ALL]

  SELECT语句n

使用UNION的两个基本规则是：

A、所有查询语句中的列个数和列的顺序必须相同。

B、所有查语句中的对应列的数据类型必须兼容。

子查询

A、使用子查询进行比较测试

使用子查询进行比较测试时，通过比较运算符（=、!

=、<、>、<=、>=），将一个表达式的值与子查询返回的单值进行比较。

如果比较运算的结果为True，则比较测试也返回True。

  使用子查询进行的比较测试要求子查询语句必须是返回单值的查询语句。

例1:

查询修了"c02"课程的且成绩高于此课程的平均成绩的学生的学号和成绩。

  SELECT学号,成绩FROMSC

  WHERE课程号=‘c02’

  and成绩>（SELECTAVG（成绩）fromSC

  WHERE课程号=‘c02’）

B、使用子查询基于集合的测试

使用子查询进行基于集合的测试时，通过运算符IN和NOTIN，将一个表达式的值与子查询返回的结果集进行比较。

这同前边在WHERE子句中使用的IN作用完全相同。

使用IN运算符时，如果该表达式的值与集合中的某个值相等，则此测试为True；如果该表达式与集合中的所有值均不相等，则返回False。

  注意：

使用子查询进行基于集合的测试时，由该子查询返回的结果集是仅包含单个列的一个列表，该列必须与测试表达式的数据类型相同。

当子查询返回结果之后，外层查询将使用这些结果。

C、    使用子查询进行存在性测试

使用子查询进行存在性测试时，往往使用EXISTS谓词。

带EXISTS谓词的子查询不返回查询的数据，只产生逻辑真值和逻辑假值。

例6:

查询选修了‘‘c01”号课程的学生姓名。

  SELECT姓名FROMStudent  

  WHEREEXISTS

  （SELECT*FROMSC

  WHERE学号=Student．学号

AND课程号=‘c01’）

注1:

带EXISTS谓词的查询是先执行外层查询，然后再执行内层查询。

由外层查询的值决定内层查询的结果；内层查询的执行次数由外层查询的结果数决定。

上述查询语句的处理过程为：

（1）找外层表Student表的第一行，根据其学号的值处理内层查询；

（2）用外层的值与内层的结果比较，由此决定外层条件的真、假值；如果为真，则此记录为符合条件的结果；

  （3）顺序处理外层表Student表中的第2、3、…行。

注2：

由于EXISTS的子查询只能返回真或假值，因此在这里给出列名无意义。

所以在有EXISTS的子查询中，其目标列表达式通常都用“*”。

2．数据更新

SQL语言的数据更新语句DML主要包括插入数据、修改数据和删除数据三种语句。

插入一行新记录

INSERTINTO<表名>[（<列名1>[,<列名2>…]）]VALUES（<值>）

插入一行的部分数据值

只写上部分列名，没有写上的列名值自动为空，如果列是NOTNULL则必需赋值。

插入多行记录

INSERTINTO<表名>[（<列名1>[,<列名2>…]）]  子查询

修改数据

UPDATE<表名>

SET<列名>=<表达式>[,<列名>=<表达式>]…

[WHERE<条件>]

删除记录

DELETE

FROM<表名>

[WHERE<条件>]

三、DCL—数据控制语言

1、权限与角色

在SQLSERVER中，权限可分为系统权限和对象权限。

系统权限由数据库管理员授予其他用户，是指数据库用户能够对数据库系统进行某种特定的操作的权力。

创建一个基本表（CREATETABLE）

对象权限由创建基本表、视图等数据库对象的用户授予其他用户，是指数据库用户在指定的数据库对象上进行某种特定的操作的权力。

如查询（SELECT）、插入（INSERT）、修改（UPDATE）和删除（DELETE）等操作。

角色是多种权限的集合，可以把角色授予用户或其他角色。

当要为某一用户同时授予或收回多项权限时，则可以把这些权限定义为一个角色，对此角色进行操作。

这样就避免了许多重复性的工作，简化了管理数据库用户权限的工作。

2、系统权限与角色的授予

SQL语言使用GRANT语句为用户授予系统权限，其语法格式为：

GRANT<系统权限>|<角色>[,<系统权限>|<角色>]…

TO<用户名>|<角色>|PUBLIC[,<用户名>|<角色>]…

[WITHADMINOPTION]

其语义为：

将指定的系统权限授予指定的用户或角色。

其中

ULBIC代表数据库中的全部用户。

WITHADMINOPTION为可选项，指定后则允许被授权的用户将指定的系统特权或角色再授予其他用户或角色。

例1：

为用户张三授予CREATETABLE的系统权限。

GRANTCREATETABLE

TO张三

3、系统权限与角色的收回

数据库管理员可以使用REVOKE语句收回系统权限，其语法格式为：

REVOKE<系统权限>|<角色>[,<系统权限>|<角色>]…

FROM<用户名>|<角色>|PUBLIC[,<用户名>|<角色>]