测试人员要掌握的基本的SQLWord文件下载.docx
《测试人员要掌握的基本的SQLWord文件下载.docx》由会员分享，可在线阅读，更多相关《测试人员要掌握的基本的SQLWord文件下载.docx（13页珍藏版）》请在冰豆网上搜索。
ALTERTABLE<
表名>
ALTERCOLUMN<
[NULL|NOTNULL]
8、删除字段
DROPCOLUMN<
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子句，则将查询结果按照<
相同的值进行分组。
如果GROUP子句后有HAVING短语，则只输出满足HAVING条件的元组。
如果有ORDER子句，查询结果还要按照<
的值进行排序。
1.1、查询指定列
SELECT<
FROM<
表名或视图名>
1.2、查询全部列
SELECT*FROM<
或SELECT<
全部列名>
1.3、取消相同取值的行
在查询结果中有可能出现取值完全相同的行了。
SELECTDISTINCT<
1.4、比较大小
比较运算符有
=，>
，>
=，<
，<
>
，!
NOT+上述比较运算符
WHERE<
[比较运算符]<
比较的值>
1.5、多重条件查询
当WHERE子句需要指定一个以上的查询条件时，则需要使用逻辑运算符AND、OR和NOT将其连结成复合的逻辑表达式。
其优先级由高到低为：
NOT、AND、OR，用户可以使用括号改变优先级。
条件1>
AND<
OR<
…
1.6、确认范围查询
用于确定范围运算符有：
BETWEEN…AND…和NOTBETWEEN…AND…
[NOT]BETWEEN
值1AND值2
这与下等价
=值1AND<
=值2
值1OR<
值2
1.7、确认集合
确定集合符号：
IN，NOTIN
[NOT]IN
（常量1，常量2，…，常量n）
1.8字符匹配查询
字符匹配查询符号：
LIKE，NOTLIKE
SQLServer2000支持如下四种通配符：
_（下划线）：
匹配任意一个字符；
%（百分号）：
匹配O个或多个字符；
[]：
匹配[]中的任意一个字符。
如[acdg]表示匹配a或c或d或g，如果[]中的字符是有序的，则可以使用连字符一来简化[]中的内容，例如[abcde]可简写为：
[a-e]；
[^]：
不匹配[]中的任意一个字符。
如[^acdg]表示不匹配a、c、d、g，如果[^]中的字符是有序的，也可以使用简化形式例如[^abcde]可简写为：
[^a-e]。
[NOT]LIKE<
匹配字符串>
1.9空值查询
空值不同于零和空格，它不占任何存储空间。
判断某个值是否为NULL值，不能使用普通的比较运算符（一、!
一等），而只能使用专门的判断NULL值的子句来完成。
IS[NOT]NULL
1.10常用库函数及统计汇总查询
常用的库函数
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）
1.11分组查询
GROUPBY<
分组依据列>
[，
[HAVING<
组提取条件
分组依据列不能是text、ntext、image和bit类型的列。
有分组时，查询列表中的列只能取自分组依据列（计算函数中的列除外）
1.12对查询结果进行排序
ORDERBY<
[ASClDESC][，
1.13数据表连接查询
A、
INNERJOIN：
这是最普通的联接类型。
只要在这两个表的公共字段之中有相符值，内部联接将组合两个表中的记录。
SELECTfields
FROMtable1INNERJOINtable2
ONtable1.field1compoprtable2.field1AND
ONtable1.field2compoprtable2.field2）OR
ONtable1.field3compoprtable2.field3）];
B、leftouterjoin：
左外连接（左连接）：
结果集包括连接表的匹配行，也包括左连接表的所有行。
SQL:
selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.c
C：
rightouterjoin:
右外连接（右连接）：
结果集包括连接表的匹配连接行，也包括右连接表的所有行。
D：
fullouterjoin：
全外连接：
不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。
1.14使用TOP限制结果集
使用TOP谓词时注意最好与ORDERBY子句一起使用，因为这样的前几名才有意义。
但当使用WITHTIES时，要求必须使用ORDERBY子句。
TOP谓词写在SELECT单词的后边，查询列表的前边。
使用TOP谓词的格式为：
TOPn[percent]withties]
其中：
n为非负整数。
TOPn：
表示取查询结果的前n行；
TOPnpercent：
表示取查询结果的前n％行；
Withties：
表示包括并列的结果。
1.15将查询结果存入表中
INTO子句的语法格式为：
INTO新表名
INTO子句跟在SELECT子句之后、FROM子句之前。
INTO新表名FROM。
新表名是要存放查询结果的表名，SELECTINTO语句包含两个操作：
首先按查询列表创建新表，然后执行查询语句，并将结果保存到新表中。
用INTO子句创建的新表可以是永久表，也可以是临时表。
临时表又分为两种：
局部临时表和全局临时表。
局部临时表要在表名前加#，它只能用在当前的连接中；
全局临时表要在表名前加##，它的生存期为创建全局临时表的连接的生存期
1.16合并查询
使用UNION的格式为：
SELECT语句1
UNION
SELECT语句2
UNION[ALL]
SELECT语句n
使用UNION的两个基本规则是：
A、所有查询语句中的列个数和列的顺序必须相同。
B、所有查语句中的对应列的数据类型必须兼容。
1.17子查询
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、…行。
由于EXISTS的子查询只能返回真或假值，因此在这里给出列名无意义。
所以在有EXISTS的子查询中，其目标列表达式通常都用“*”。
2．数据更新
SQL语言的数据更新语句DML主要包括插入数据、修改数据和删除数据三种语句。
2.1插入一行新记录
INSERTINTO<
[（<
[,<
…]）]VALUES（<
值>
）
2.2插入一行的部分数据值
只写上部分列名，没有写上的列名值自动为空，如果列是NOTNULL则必需赋值。
2.3插入多行记录
[（<
…]）]
子查询
2.4修改数据
UPDATE<
SET<
=<
表达式>
[,<
]…
[WHERE<
条件
2.5删除记录
DELETE
FROM<
三、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<
例2：
收回用户张三所拥有的CREATETABLE的系统权限。
REVOKECREATETABLE
FROM张三
4、对象权限与角色的授予
SQL语言使用GRANT语句为用户授予对象权限，其语法格式为：
GRANTALL|<
对象权限>
[（列名[,列名]…）][,<
]…ON<
对象名>
[WITHGRANTOPTION]
将指定的操作对象的对象权限授予指定的用户或角色。
其中：
ALL代表所有的对象权限。
列名用于指定要授权的数据库对象的一列或多列。
如果不指定列名，被授权的用户将在数据库对象的所有列上均拥有指定的特权。
实际上，只有当授予INSERT、UPDATE权限时才需指定列名。
ON子句用于指定要授予对象权限的数据库对象名，可以是基本表名、视图名等。
WITHADMINOPTION为可选项，指定后则允许被授权的用户将权限再授予其他用户或角色。
例3：
将对Sc表和student表的所有对象权限授予USER1和USER2。
GRANTALL
ONSc,student
TOUSER1,USER2
例4：
将对Course表的查询权限授予所有用户。
GRANTSELECT
ONCourse
TOPUBLIC
例5:
将查询student表和修改学生年龄的权限授予USER3,并允许将此权限授予其他用户。
GRANTSELECT,UPDATE（PROF）
ONstudentT
TOUSER3
WITHADMINOPTION
USER3具有此对象权限，并可使用GRANT命令给其他用户授权，如下例，USER3将此权限授予USER4:
GRANTSELECT,UPDATE（年龄）
ONstudent
TOUSER4
5、对象权限与角色的回收
所有授予出去的权力在必要时都可以由数据库管理员和授权者收回，收回对象权限仍然使用REVOKE语句，其语法格式为：
收回用户USER1对Course表的查询权限。
REVOKESELECT
ONC
FROMUSER1
其次，大家来看一些不错的sql语句
1、说明：
复制表（只复制结构,源表名：
a新表名：
b）（Access可用）
法一：
select*intobfromawhere1<
1
法二：
selecttop0*intobfroma
2、说明：
拷贝表（拷贝数据,源表名：
a目标表名：
insertintob（a,b,c）selectd,e,ffromb;
3、说明：
跨数据库之间表的拷贝（具体数据使用绝对路径）（Access可用）
insertintob（a,b,c）selectd,e,ffrombin‘具体数据库’where条件
例子：
..frombin'
"
&
Server.MapPath（"
."
）&
\data.mdb"
&
'
where..
4、说明：
两张关联表，删除主表中已经在副表中没有的信息
deletefromtable1wherenotexists（select*fromtable2wheretable1.field1=table2.field1）
5、说明：
四表联查问题：
select*fromaleftinnerjoinbona.a=b.brightinnerjoincona.a=c.cinnerjoindona.a=d.dwhere.....
6、说明：
日程安排提前五分钟提醒
select*from日程安排wheredatediff（'
minute'
f开始时间,getdate（））>
5
7、说明：
一条sql语句搞定数据库分页
selecttop10b.*from（selecttop20主键字段,排序字段from表名orderby排序字段desc）a,表名bwhereb.主键字段=a.主键字段orderbya.排序字段
8、说明：
前10条记录
selecttop10*FROMtable1where范围
9、说明：
选择在每一组b值相同的数据中对应的a最大的记录的所有信息（类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.）
selecta,b,cfromtablenametawherea=（selectmax（a）fromtablenametbwheretb.b=ta.b）
10、说明：
包括所有在TableA中但不在TableB和TableC中的行并消除所有重复行而派生出一个结果表
（selectafromtableA）except（selectafromtableB）except（selectafromtableC）
11、说明：
随机取出10条数据
selecttop10*fromtablenameorderbynewid（）
12、说明：
随机选择记录
selectnewid（）
13、说明：
删除重复记录
Deletefromtablenamewhereidnotin（selectmax（id）fromtablenamegroupbycol1,col2,...）
14、说明：
列出数据库里所有的表名
selectnamefromsysobjectswheretype='
U'
15、说明：
列出表里的所有的
selectnamefromsyscolumnswhereid=object_id（'
TableName'
）
16、说明：
列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select中的case。
selecttype,sum（casevenderw
升级会员 