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='信科系')