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

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

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

第4章2结构化查询语言SQL

4结构化查询语言SQL

4.5常用函数

4.5.1数学函数

RAND（）

返回0到1之间的随机值。

例如:

Printrand（）

declare@iFloat

set@i=rand（）

print@i

PRINTCONVERT（INT,RAND（）*26）--0to25

PRINTCONVERT（INT,RAND（）*26）+1--1to26

规则：

PRINTCONVERT（INT,RAND（）*（N-M+1））+M--MtoN（M

ROUND（78.518,2）

按照指定精度四舍五入。

78.520

FLOOR（-123.45）

返回<=参数的最大整数。

-124

CEILING（-123.45）

返回>=参数的最小整数。

-123

PI（）

3.14159

其他函数：

弧度、角度

DEGREES、RADIANS

指数函数

EXP、POWER

对数函数

LOG10、LOG

三角函数

COS、SIN、TAN、ATAN

4.5.2字符串函数

LEN（'abcd'）

计算串长

SPACE（10）

返回由重复的空格组成的字符串。

REPLICATE（'abc',3）

返回由重复的字符串组成的字符串。

REVERSE（'abcdef'）

字符串逆序

declare@schar（100）

set@s=REVERSE（'abc'）

print@s

RTRIM（'abc'）

缩减右空格

类型转换

ASCII（'abc'）

首字符转换为ASCII值。

CHAR（97）

ASCII值转换为字符

STR（123.45,8,3）

STR（float_expression[,length[,decimal]]）

数值转换为字符串

float_expression

带小数点的近似数字（float）数据类型的表达式。

length

总长度。

它包括小数点、符号、数字以及空格。

默认值为10。

decimal

小数点后的位数。

decimal必须小于或等于16。

如果decimal大于16，则会截断结果，使其保持为小数点后具有十六位。

CONVERT（real,'12.34'）

CONVERT（decimal（9,4）,'12.34'）

字符串转换为实数

（也可用于其他类型转换）

子串操作

LEFT（'abcdefg',2）

取左子串

RIGHT（'abcdefg',2）

取右子串

SUBSTRING（'abcdefg',2,4）

取子串

REPLACE（'oldisold!

','old','new'）

子串替换

STUFF（'abcdef',2,3,'12345'）

位置替换

子串/模式匹配

CHARINDEX（'Data','WhatisDataBase'）

子串匹配

PATINDEX（'%Data%','WhatisDataBase'）

模式匹配

字符串相似度

SOUNDEX

返回有四个字符的代码

DIFFERENCE

返回[0,4]，表示两串的相似性

SOUNDEX（'hello'）

H120

SOUNDEX（'what'）

0000

SOUNDEX（'water'）

0000

DIFFERENCE（'hello','what'）

0

DIFFERENCE（'what','water'）

4

4.5.3日期函数

GETDATE（）

返回当前时间

YEAR（GETDATE（））

取时间中的年

MONTH（GETDATE（））

取时间中的月

DAY（GETDATE（））

取时间中的日

DATENAME（year,GETDATE（））

DATENAME（month,GETDATE（））

DATENAME（day,GETDATE（））

取部分日期

（字符串）

DATEPART（hour,GETDATE（））

DATEPART（minute,GETDATE（））

DATEPART（second,GETDATE（））

取部分日期

（整数）

DATEADD（day,2,'2008-9-1'）

DATEADD（month,2,'2008-9-1'）

DATEADD（year,2,'2008-9-1'）

日期加法

DATEDIFF（hour,'1990-1-1',GETDATE（））

DATEDIFF（minute,'1990-1-1',GETDATE（））

DATEDIFF（second,'1990-1-1',GETDATE（））

日期减法

4.6数据查询

SELECT语句有太多的命令选项（子句）：

SELECT子句

指定要显示的属性列（相当于投影运算）

FROM子句

指定查询表

WHERE子句

指定查询条件（相当于选择运算）

GROUPBY子句

对查询结果按指定列的值分组。

一般会在每组的若干记录上使用“集函数”。

HAVING短语

筛选出满足指定条件的组

ORDERBY子句

对查询结果按指定列值的排序

4.6.1单表查询

1、选择表中的若干列（投影）

SELECTSname,Sno,SdeptFROMS;

SELECT*FROMS;

SELECTSnameas[姓名],

year（GETDATE（））-year（Sbirthday）as[年龄]

FROMS;

2、选择满足条件的元组（选择）

查询1989-9-1以前出生的学生记录

SELECT*FROMSWHERESbirthday<'1989-9-1'

查询没有参加考试学生的信息

查询信科系大三的学生姓名

SELECTSnameFROMS

WHERESdept='信科系'ANDSgrade=3;

查询大二、大三、大四的学生记录

SELECT*FROMS

WHERESgradeBETWEEN2AND4;

查询大一的学生记录

SELECT*FROMS

WHERESgradeNOTBETWEEN2AND4;

查询信科系和物理系的学生记录

SELECT*FROMS

WHERESdeptIN（'信科系','物理系'）;

SELECT*FROMS

WHERESdept='信科系'ORSdept='物理系';

字符串的模式匹配：

%

代表任意长度的字符串

_

代表任意单个字符

SELECT*FROMSWHERESnoLIKE'95%';

查询所有的刘姓学生

SELECT*FROMSWHERESnameLIKE'刘_';

查询所有的刘姓（双字）学生

SELECTSnameFROMSWHERESnameLIKE'刘_'

查询所有的姓名中第2个字是勇的学生

SELECTSname,SnoFROMSWHERESnameLIKE'_勇%'

出现重复行的情形：

查询所有选过课的学生学号（含重复行）

SELECTSnoFROMSC;

查询所有选过课的学生学号（无重复行）

SELECTDISTINCTSnoFROMSC;

3、对查询结果排序

所有记录按成绩降序排列

SELECT*FROMSC

ORDERBYSCscoreDESC

3号课程的所有记录按成绩降序排列

SELECT*FROMSC

WHERECno='3'

ORDERBYSCscoreDESC

所有记录按学号升序、成绩降序排列

SELECT*FROMSC

ORDERBYSnoASC,SCscoreDESC

4、使用集函数

计数

COUNT（）

总和

SUM（）

平均值

AVG（）

最大值

MAX（）

最小值

MIN（）

方差

STDEV（）

查询学生总数。

SELECTCOUNT（*）FROMS

查询选修了课程的学生人数。

（计算时取消重复值）

SELECTCOUNT（DISTINCTSno）FROMSC

计算2号课程的学生平均成绩。

SELECTAVG（SCscore）,MAX（SCscore）,Min（SCscore）

FROMSCWHERECno='2'

计算学生成绩的均值和方差。

SELECTAVG（SCscore）,STDEV（SCscore）fromSC

4.6.2多表（连接）查询

1两表之间的连接查询

广义笛卡尔积

SELECTS.*,SC.*FROMS,SC

等值连接：

查询每个学生及其选修课程的情况。

SELECTS.*,SC.*FROMS,SC

WHERES.Sno=SC.Sno

自然连接：

查询每个学生及其选修课程的情况。

SELECTS.Sno,Sname,Ssex,Sbirthday,Sdept,Sgrade,Cno,SCscore

FROMS,SCWHERES.Sno=SC.Sno

或

SELECTS.*,Cno,SCscore

FROMS,SCWHERES.Sno=SC.Sno

或

SELECTS.*,Cno,SCscoreFROMSINNERJOINSC

ONS.Sno=SC.Sno

内连接INNERJOIN：

第一表与第二表在联接列上满足条件而连接。

更多内涵的连接：

查询选修1号课程且成绩大于90分所有的学生

SELECTS.*,Cno,SCscoreFROMS,SC

WHERES.Sno=SC.SnoANDCno='1'ANDSCscore>90

或

SELECTS.*,Cno,SCscoreFROMSINNERJOINSC

ONS.Sno=SC.Sno

WHERECno='1'ANDSCscore>90

Cf:

查询每个学生每门课的姓名、课程名、成绩

Selects.sname,ame,scscorefromsc

Innerjoinsonsc.sno=s.sno

Innerjoinconsc.sno=o

2一个表的连接查询

自身连接：

一个表与其自己进行连接。

由于所有属性名都是同名属性，因此必须使用别名。

（/*例如一行查阅一位同学的几门课成绩*/）

自身连接

SELECTa.*,b.*FROMSCasa,SCasb

WHEREa.sno=b.sno

查询选修了1号课程和3号课程的学生信息

SELECTa.*,b.*FROMSCasa,SCasb

WHEREa.sno=b.snoANDo='1'ANDo='3'

3外连接查询

外连接：

以一个表为主体，将另一个表中“满足”和“不满足”连接条件的元组一起输出。

左外连接：

查询所有学生的选修情况（含未选修情况）

SELECTS.*,SCscoreFROMS

LEFTOUTERJOINSCONS.SNO=SC.SNO;

外连接：

查询所有课程的成绩（含未考试科目）

SELECTC.*,SCscoreFROMC

FULLOUTERJOINSCONC.Cno=SC.Cno;

右外连接：

SELECTS.*,SCscoreFROMS

RIGHTOUTERJOINSCONS.SNO=SC.SNO;

外连接：

SELECTS.*,SCscoreFROMS

FULLOUTERJOINSCONS.SNO=SC.SNO;

4.6.3嵌套查询

查询块：

SELECT-FROM-WHERE语句

嵌套查询：

将一个查询块嵌套在另一个查询块的WHERE子句中

1、不相关子查询

子查询的查询条件不依赖于父查询。

IN运算符

查询与“刘晨”在同一个系学习的学生

SELECT*FROMSWHERESdeptIN

（SELECTSdeptFROMSWHERESname='刘晨'）

同上（使用单表连接的方法）

SELECTS1.*FROMSasS1,SasS2

WHERES1.Sdept=S2.SdeptANDS2.Sname='刘晨'

查询选择了2号课程的所有学生的姓名

SELECTSnameFROMS

WHERESnoIN

（SELECTSnoFROMSCWHERECno='2'）;--如果直接用连接查询？

查询选修了“数学”的学生学号和姓名

SELECT*FROMSWHERESnoIN

（SELECTSnoFROMSCWHERECnoIN

（SELECTCnoFROMCWHERECname='数学'）

）

查询有一门课程分数高于90的学生姓名

SELECTSnameFROMS

WHERESnoIN

（SELECTdistinctSnoFROMSCWHERESCscore>90）

将信科系所有学生的成绩置100。

UPDATESCsetSCscore=100

whereSnoIN

（SELECTSnoFROMSWHERESdept='信科系'）

删除信科系所有学生的选课记录。

DELETEFROMSCWHERESnoIN

（SELECTSnofromSWHERESdept='信科系'）

ANY或ALL运算符

查询有一门课程分数高于90的学生姓名

SELECTSnameFROMS

whereSno=ANY

（SELECTdistinctSnoFROMSCWHERESCscore>90）

查询其他系中比信科系任意一个学生年龄大的学生

SELECT*FROMS

WHERESdept<>'信科系'ANDSbirthday

（SELECTSbirthdayFROMSWHERESdept='信科系'）

同上

SELECT*FROMS

WHERESdept<>'信科系'ANDSbirthday<

（SELECTMAX（Sbirthday）FROMSWHERESdept='信科系'）;

查询其他系中比信科系所有学生年龄大的学生

SELECT*FROMS

WHERESdept<>'信科系'AndSbirthday

（SELECTSbirthdayFROMSWHERESdept='信科系'）;

同上

SELECT*FROMS

WHERESdept<>'信科系'ANDSbirthday<

（SELECTMIN（Sbirthday）FROMSWHERESdept='信科系'）;

查询成绩最高的学生姓名

SELECTSnameFROMS

whereSnoin

（SELECTSnoFROMSC

whereSCscore>=ALL（SELECTSCscoreFROMSC）

）

同上

SELECTSnameFROMS

whereSnoin

（SELECTSnoFROMSC

whereSCscore=（SELECTMAX（SCscore）FROMSC）

）

Cf:

任课教师的信息

招生的专业

2、相关子查询

子查询的查询条件依赖于父查询：

依次取外层查询中每个元组，将其置于内层查询中，将使WHERE子句为真的所有元组放入结果表中。

EXISTS运算符

查询所有选修2号课程的学生。

SELECT*FROMS

WHEREEXISTS

（SELECT*FROMSCWHERESno=S.SnoANDCno='2'）/*如果用连接查询，含义一样吗？

是

查询没有选修2号课程的学生。

SELECT*FROMS

WHERENOTEXISTS

（SELECT*FROMSCWHERESno=S.SnoANDCno='2'）

/*如果用连接查询，含义一样吗？

SELECT*FROMS,sc

WHEREs.Sno=Sc.SnoANDCno<>'2'否

查询与“李勇”在同一个系的学生。

SELECT*FROMSasA

WHEREEXISTS

（SELECT*FROMSasB

WHEREB.Sdept=A.SdeptANDB.Sname='李勇'

）

4.6.4对查询结果分组

GROUPBY子句：

将查询结果表按某一列或多列值分组，值相等的为一组。

（集函数常配合使用，在输出字段中并且不是集函数）

HAVING子句：

只有满足指定条件的组才输出。

求各课程的选课人数和均分。

SELECTCno,COUNT（Sno）as[选课人数],

AVG（SCscore）as[均分]FROMSC

GROUPBYCno

查询选修了2门以上课程的学生学号。

SELECTSno,COUNT（sno）FROMSC

GROUPBYSno

HAVINGCOUNT（sno）>2

查询有2门以上课程是90分以上的学生的学号及（90分以上的）课程数

SELECTSno,COUNT（Cno）as[优秀课程数]FROMSC

WHERESCscore>=90

GROUPBYSno

HAVINGCOUNT（Cno）>=2

WHERE子句：

作用于表或视图，从中选择满足条件的元组。

（不可以是集函数）

HAVING短语：

作用于组，从中选择满足条件的组。

（一定在输出字段中

4.6.5查询的并操作

查询物理系的学生及年龄小于20岁的学生。

SELECT*FROMSWHERESdept='物理系'

UNION

SELECT*FROMSwhereDATEDIFF（YEAR,Sbirthday,GETDATE（））<20;

查询选修了001号或者003号课程的学生学号。

SELECTSnoFROMSCWHERECno='1'

UNION

SELECTSnoFROMSCWHERECno='3'

同上

SELECTDISTINCTSnoFROMSC

WHERECno='1'ORCno='3'

4.6.6查询建表

利用查询结果创建一个基本表

SELECT*

intoISTable

FROMSWHERESdept='信科系'

示例：

将信科系所有学生的成绩置100。

UPDATESCsetSCscore=100

whereSnoIN

（SELECTSnoFROMSWHERESdept='信科系'）

删除信科系所有学生的选课记录。

DELETEFROMSCWHERESnoIN

（SELECTSnofromSWHERESdept='信科系'）