知识点SQL语句学习及详细总结.docx

知识点SQL语句学习及详细总结.docx

《知识点SQL语句学习及详细总结.docx》由会员分享，可在线阅读，更多相关《知识点SQL语句学习及详细总结.docx（25页珍藏版）》请在冰豆网上搜索。

知识点SQL语句学习及详细总结

一.数据库简介和创建

1.系统数据库

在安装好ＳＱLSERＶER后，系统会自动安装5个用于维护系统正常运行的系统数据库：

 （１）mａsteｒ：

记录了ＳQLSERVER实例的所有系统级消息,包括实例范围的元数据（如登录帐号）、端点、链接服务器和系统配置设置。

（2）msｄb:

供SQＬ　ＳＥRVEＲ代理服务调度报警和作业以及记录操作员的使用，保存关于调度报警、作业、操作员等信息。

（备份还原时） （3）ｍodel：

ＳQLSEＲVER实例上创建的所有数据库的模板。

 （４）temｐdb：

临时数据库,用于保存临时对象或中间结果集,为数据库的排列等操作提供一个临时工作空间。

（每次启动都会重新创建） （5）Rｅsｏｕrce：

一个只读数据库,包含了SQＬSEＲＶER的所有系统对象。

（隐藏的数据库）

2.数据库的组成

2.1数据文件 

（1）主要数据文件：

扩展名为.mdf，每个数据库有且只能有一个。

（2）次要数据文件:

扩展名为.ｎdf,可以没有或有多个。

２.2日志文件 扩展名为.ldｆ,用于存放恢复数据库的所有日志信息。

2.3　数据的存储分配 （１）数据文件和日志文件的默认存放位置为：

＼PrｏｇrameFｉｌes＼ＭｉcrｏsｏｆtSＱＬServer＼ＭSSQＬ.1＼ＭSＳQL\Ｄaｔa文件夹。

（2）数据的存储分配单位是数据页。

一页表是一块8KB的连续磁盘空间。

 （3）页是存储数据的最小空间分配单位，页的大小决定了数据库表中一行数据的最大大小。

３.　SQL语句数据库操作

（1）创建数据库

CREATEＤATABASＥdaｔabａse＿naｍe

二.SQＬ基础

SQＬ（Structuｒeｄ　Qｕｅrｙ　Langｕａge,结构化查询语言）是用户操作关系数据库的通用语言。

1.SQL功能概述

2.系统提供的数据类型

2.1数值数据类型

数据类型

说明

存储空间

bit

ｂit数据类型是整型,其值只能是0、１或空值。

这种数据类型用于存储只有两种可能值的数据,如Yeｓ　或Ｎｏ、Tｒｕe　或Falｓe、On　或Off. （很省空间的一种数据类型,如果能够满足需求应该尽量多用。

）

1字节

tinｙint

tｉｎyint数据类型能存储从0到255　之间的整数。

它在你只打算存储有限数目的数值时很有用。

1字节

smａlｌｉｎt

smallint数据类型可以存储从-　2的１5次幂（-３2７68）到2的1５次幂（3276７）之间的整数。

这种数据类型对存储一些常限定在特定范围内的数值型数据非常有用。

（如果tｉnyinｔ类型太单调不能满足您的需求，您可以考虑用ｓmallint类型,因为这个类型相对也是比较安全的，不接受恶意脚本内容的嵌入。

）

2字节

int

iｎｔ数据类型可以存储从-2的31次幂（-２１４748３6４８）到2的31次幂　（２147483　647）之间的整数。

存储到数据库的几乎所有数值型的数据都可以用这种数据类型

4个字节

numeriｃ（p,s）或ｄeｃiｍal（p，ｓ）

数据类型能用来存储从－１0的38次幂－１到10的３8次幂-１的固定精度和范围的数值型数据。

使用这种数据类型时,必须指定范围和精度。

范围是小数点左右所能存储的数字的总位数。

精度是小数点右边存储的数字的位数

最多1７个字节

2.２普通编码字符串类型

数据类型

说明

存储空间

chaｒ（n）

ｃhar数据类型用来存储指定长度的定长非统一编码型的数据，n表示字符串的最大长度,取值范围为1~8000（若实际字符串控件小于n,系统自动在后面补空格）

ｎ字节＿_＿＿___

varｃhａｒ（n）

可变长度的字符串类型,ｎ表示字符串的最大长度，取值范围为１~80００。

字符数＋2字节额外开销

ｔｅxt

teｘt数据类型用来存储大量的非统一编码型字符数据。

这种数据类型最多可以有２31-１或20亿个字符.

每个字符一个字节

cｈar　和　varchａr的区别:

 若某列数据类型为vａｒｃhaｒ（20），存字符串”Ｊｏｎｅ”时,只占用4个字节,而cｈａr（20）会在为填满的空间中填写空格。

所以, varchar类型比chａr类型更节省空间，但它的开销会大一些,处理速度也慢一些。

因此,n值比较小（小于4），用ｃhar类型更好些。

2.３统一编码字符串类型（Uｎiｃoｄe）

数据类型

说明

存储空间

nｃhar（n）

nchａｒ数据类型用来存储定长统一编码字符型数据。

统一编码用双字节结构来存储每个字符,而不是用单字节（普通文本中的情况）。

它允许大量的扩展字符。

此数据类型能存储4000种字符，使用的字节空间上增加了一倍.

2ｎ字节____＿__

nvａrchar（ｎ）

nｖarchａr数据类型用作变长的统一编码字符型数据。

此数据类型能存储４000种字符，使用的字节空间增加了一倍.

字符数+2字节额外开销

ntext

最多可存储2的3０次方-１将近１0亿个字符

每个字符两个字节

三.　SQL数据操作语言

1.数据查询语句

1．1查询语句的基本结构

SELEＣT＜目标列名序列>　－-需要哪些列

Fｒｏm＜表名>　　-－来自哪张表

［WHEＲE<行选择条件＞]

　[ＧＲOUＰBY<分组依据列>]

[HAVING　<组>]

　　［ORDERBY<排序依据列>]

SＥLECＴ子句用于指定输出的字段; 

FROM子句用于指定数据的来源; 

WHＥＲE子句用于指定数据的选择条件； ﻫGＲOＵＰＢY子句用于对检索到的记录进行分组； 

HAＶING子句用于指定组的选择条件; ﻫORDERBY　子句用于对查询的结果进行排序; 以上子句中，SELECＴ子句和ＦROM子句是必需的,其它是可选的。

1.2单表查询

1．２.１选择表中若干列

（１）查询指定的列

SELEＣT列名ＦRＯＭ表名

∙1

例子：

SELECT　Snamｅ,SnｏＦＲOMStudｅｎｔ

（２）查询全部列

ＳELEＣT*FRＯM表名

∙１

例子:

SELECT　＊ＦROMＳtuｄenｔ

（3）查询经过计算的列

SELＥＣＴ列名FROM表名

∙1

例子：

SＥＬEＣTSname,year（gｅｔdatａ（））　-yｅar（Birｔhｄaｔe）ＦＲOＭStｕdｅnｔ

1.2.２　选择表中的若干元祖

（1）消除取值相同的行：

DIＳＴINCT

SELＥCTＤＩSTIＮCTＳnoFROM表名

∙1

例子：

SELEＣT　DＩSTINCT　SnoＦROMStuｄent

（２）查询满足条件的元祖

查询条件

谓词

比较

=、>、>=、<=、<、<>、!

=、！

>、！

<

确定范围

BＥＴＷEEN…AND、　NＯTBＥTＷＥＥN…AND

确定集合

IＮ、NOＴIＮ

字符匹配

ＬIKE、NOＴLIＫE

空值

ＩS　NULL、ＩSNOT　NULＬ

多重条件（逻辑谓词）

ANＤ、OR

ａ.比较大小 例子:

ＳELEＣT　SｎaｍeFＲOMStudeｎtWＨERＥyear（getdaｔａ（））　-　year（Birthdatｅ）<2０

b．确定范围 

BEＴWEEN…AＮD和NOTＢEＴWEEN…AND可用于查找属性值在或不在指定范围。

列名｜表达式|　[NOT]BＥTWEEN下限值ＡND上限值

∙1

BETWEEN…AND 代表的范围是在上限值和下限值之间（包括边界值）,即为　ｔrue。

 NＯＴBETWEEN…AND 代表的范围是不在上限值和下限值之间（不包括边界值）,即为true。

（若判断值为边界值时,为　false）

例子:

SＥLＥＣＴSno,Cno　FROMSC　WHＥRＥGraｄeＢETWEEN8０ＡND　90 此查询等价于:

SＥＬEＣTSnｏ,CnｏFROM　SＣWHERE　Gｒade>=８０　ANDGrade＜=9０

例子：

SEＬECTＳno,CｎｏFＲOM　ＳＣ　ＷHＥREＧrａdｅNＯTBＥＴＷＥＥＮ８0AND90 此查询等价于：

ＳELECTSno,ＣnoＦROMSCWHEＲEGrade<８0ＯRGrade＞　90

ｃ．确定集合 ＩN运算符的含义：

当列中的值和集合中的某个常量值相等时,结果为Ｔrｕe。

 ＮＯTＩN运算符的含义:

当列中的值和集合中的全部常量值都不相等时,结果为Tｒｕe。

例子:

SEＬECTSｎoFＲOMＳtudent　ＷHERＥＤepｔIN（'信息管理系'，'计算机系＇） 此查询等价于：

ＳＥＬＥCT　ＳnoFRＯM　ＳｔudentWHEREDｅpt=＇信息管理系'　ＯＲDepｔ=　＇计算机系＇）

例子:

ＳEＬECTSno　ＦROMＳｔudentWＨEＲEＤｅｐｔNOTIN（'信息管理系','计算机系＇） 此查询等价于：

ＳELＥCTＳｎo　ＦRＯM　ＳtudenｔWHＥＲＥDｅpt!

='信息管理系'ANDDepｔ　！

='计算机系'）

d．　字符串匹配 ﻫＬiｋe运算符用于查找指定列中与匹配串匹配的元祖。

列名［NＯＴ］LIＫE　<匹配串>

通配符

含义

_（下划线）

匹配任意一个字符

％（百分号）

匹配０个或多个字符

[]

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

如［abｃd]表示匹配aｂcd其中任何一个，若是连续的,可以用-表示,如[ａ-d］

[^]

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

如[＾ａbcd］表示不匹配abcd其中任何一个，若是连续的，可以用　－表示，如[^a－d]

例子：

（查询姓“张”的学生详细信息）

SELEＣT*FROMStudｅntWHERESnamｅLＩKE'张%＇

（查询不姓“张”的学生详细信息）

ＳＥLECＴ＊ＦROMStudｅnｔ　WＨEＲＥSnameNOＴLIKＥ'张％'

（查询姓“张”、“李”的学生详细信息）

SELＥCT*FROMSｔudeｎtWHＥRESnameLＩKＥ'[张李]%'

（查询名字的第二个字为“小”　或“大”的学生详细信息）

SELＥCT*FＲＯＭStudentWＨERE　Ｓnａｍｅ　LIKＥ'_［小大]%'

ｅ．涉及空值的查询 空值（NULＬ）在数据库中有特殊含义,表示当前不确定或未知的值。

判断是否为ＮＵＬL时，不可用普通的比较运算符，需用ISNUＬＬ 例子：

SEＬECT　SｎoFROMStudｅnｔ　WHＥREGrａde　IＳＮULL

1.2.3　对查询结果进行排序

将查询结果按照指定的顺序显示。

ＡSC表示按列值升序排列（从上往下,值从大到小）。

DESC表示按列值降序排列（从上往下,值从小到大）。

默认为ＡSC。

OＲDERＢY<列名＞[ASC|DESC]

例子:

SELECTSｎｏ，GrａｄeFROM　SCORDＥRBＹ　GradeDESC

1.2.4　使用聚合函数统计数据

聚合函数也称为统计函数或集合函数，作用是对一组值进行计算并返回一个统计结果。

聚合函数

含义

COＵNT（*）

统计表中元祖的个数

COUＮＴ（[ＤISTIＮCＴ]＜列名>）

统计本列的非空列值个数

ＳUM（<列名>）

计算列值的和值（必须是数值型列）

AVG（<列名>）

计算列值的平均值（必须是数值型列）

MAX（＜列名>）

计算列值的最大值

MIＮ（＜列名＞）

计算列值的最小值

上述函数除COＵNT（*）外,其它函数在计算过程中均忽略NULＬ值

（统计学生总人数）

ＳELECＴCOUNT（*）FROMStudenｔ

（统计“０01”学号学生的考试平均成绩）

ＳＥLECTAVG（Gｒａde）　FROMＳCWHEREＳno='001＇

（查询“C00１”号课程考试成绩的最高分和最低分）

SELECＴＭＡX（Gｒadｅ）最高分,MIN（Gradｅ）　最低分FRＯMSＣWHERECno＝　'Ｃ00１'

聚合函数不能出现在WHERE子句中！

1．２.5对数据进行分组统计

需要先对数据进行分组,然后再对每个组进行统计。

分组子句GＲＯUPBY。

在一个查询语句中，可以用多个列进行分组。

 分组子句跟在WHERE子句的后面：

ＧROUPBY＜分组依据列＞［,．..ｎ］

［HAＶING　＜组筛选条件>]

（１）使用GROＵPBY子句

（统计每门课程的选课人数,列出课程号和选课人数）

SＥLEＣＴ　Cnｏａs课程号,　COUＮＴ（Ｓno）as选课人数FrｏmＳC　GroupBＹCｎo

（统计每个学生的选课门数和平均成绩）

SEＬEＣＴ　Ｓno　学号，ＣＯUＮT（*）　选课门数,ＡＶG（Grade）平均成绩From　SCＧroｕpBＹSｎo

带WHERＥ子句的分组（统计每个系的女生人数）

SＥＬECT　Dｅpｔ，CＯＵNT（*）女生人数FｒomＳtｕdentWｈｅrｅ　Sex　=＇女＇ＧrｏupBYＤeｐｔ

（2）使用HＡVINＧ子句 HAVING子句用于对分组后的统计结果再进行筛选，它的功能与ＷHＥRE子句类似，它用于组而不是单个记录。

在HAVINＧ子句中可以使用聚合函数,但在ＷHERE子句中不能，通常与GROＵP子句一起使用。

（查询选课门数超过３门的学生的学号和选课门数）

SELEＣT　Sｎo　学号，COＵNT（*）选课门数,AVＧ（Grade）　平均成绩ＦｒomSC　ＧroupBYＳｎoHAＶINGCＯUNT（*）　>３

（3）ＷＨERE　、GRＯUP　ＢY　、HＡVIＮG的作用及执行顺序

∙ＷHEＲE子句用于筛选FROＭ子句中指定的数据所产生的行数据。

∙GROUPBY 子句用于对经 WＨERE 子句筛选后的结果数据进行分组。

∙HAＶING 子句用于对分组后的统计结果再进行筛选。

可以分组操作之前应用的筛选条件，在WHEＲＥ子句中指定它们更有效，这样可以减少参与分组的数据行。

在HAＶING子句中指定的筛选条件应该是那些必须在执行分组操作之后应用的筛选条件。

（查询计算机系和信息管理系每个系的学生人数）

第一种：

ＳELEＣTDｅpt,CＯUNT（*）ＦROMＳtudentGRＯUPＢYＤeptHavｉnｇDｅｐt　in（'计算机系','信息管理系'）

第二种：

SELＥＣT　Depｔ,CＯＵＮT（*）FROＭＳｔudenｔWＨERＥ　Deptin（'计算机系','信息管理系'）ＧＲＯUＰＢY　Dept　

以上例子比较：

第一种是按照系分组好了之后,只采取所有系中的两个系,显然效率不高。

而第二种是先进行WHEＲE筛选条件之后,再进行ＧROＵPBY计算,显示更好。

１．３多表连接查询

若一个查询同时涉及到两张或以上的表,则称为连接查询。

１．3.１内连接

使用内连接时，如果两个表的相关字段满足条件，则从两个表中提取数据组成新的记录。

FROM表１　［IＮNER］JOIN表2ON＜连接条件＞

∙1

注意：

连接条件中的连接字段必须是可比的,必须是语义相同的列。

（查询学生及选课的详细信息）

SELECT*FＲOMＳtuｄenｔINNＥRJOＩN　SCONＳtuｄent．Ｓno=SC．Ｓｎo

（查询计算机系学生的选课情况,列出该学生的名字、所修课程号、成绩）-－----行选择条件

SEＬＥCTSname,Ｃno，GradｅFROMStuｄentINNERJＯINSC　ON　Studeｎｔ．Sｎo=　SC．Sno　WＨＥREDept　='计算机系'

（统计每个系的平均成绩）------分组的多表查询

SEＬECTDeｐｔ,AVG（Grａde）ＡSAveraｇｅGrade　FROMSｔudｅntSIＮNＥRＪOINSＣOＮ　S．Ｓｎo=SC.Sno　GroupBYDｅpt

（统计计算机系每个学生的选课门数、平均成绩、最高成绩、最低成绩）－----－分组和行选择条件的多表连接查询

SELECTSｎo,ＣOUNＴ（＊）,AVG（Gｒade），MAX（Grａｄｅ），MIN（Graｄｅ）　FＲOMStｕdｅntＳJＯINＳＣONS.Sno=SC.SnoWHERＥ　Deｐt　=＇计算机系'ＧrｏupBYＤept

1.3.2　自连接

自连接是一种特殊的内连接，相互连接的表在物理上是一张表,但在逻辑上可以看做是两张表。

FROM表１AＳT1ＪOIN表１ＡＳ　T２

∙1

通过为表取别名的方法，可以让物理上的一张表在逻辑上成为两张表。

（一定要为表取别名!

）

（查询与刘晨在同一个系学习的学生的姓名、所在系）

ＳELECTＳ１．Sname,S1．ＤepｔＦRＯMStudeｎtS1JＯINStudentS2

ON　S1.Dｅpt=S2．Dept　　－--同一个系的学生

ＷHERＥ　S２.Snａｍｅ＝　'刘晨'---S2表作为查询条件

ＡＮＤ　S１.Ｓnａmｅ　！

＝'刘晨＇--－－S1表作为结果表,并从中去掉‘刘晨’本人信息

１.3.3外连接

在内连接操作中，只有满足条件的元祖才能出现在查询结果集中。

 外连接是只限制一张表中的数据必须满足条件,而另一张表的数据可以不满足条件。

FROＭ　表１LＥFＴ|ＲIＧHT[OUＴＥR]JOIN表2ON<连接条件>

∙1

ＬEＦT[OUTEＲ]　ＪＯIN 称为左外连接，含义是限制表2中的数据必须满足条件，但不管表１中的数据是否满足条件，均输出表１中的数据。

 LEFT　[OＵＴＥＲ］ＪOIN 称为右外连接,含义是限制表1中的数据必须满足条件，但不管表2中的数据是否满足条件，均输出表2中的数据。

内连接与外连接的区别:

内连接:

表A与表Ｂ进行内连接,则结果为两个表中满足条件的记录集，即Ｃ部分。

 外连接:

如果表Ａ和表B进行左外连接,则结果为记录集Ａ+记录集C;如果表A和表B进行右外连接，则结果为记录集B+　记录集C。

（查询没有人选的选修课程名）

ＳELＥCTCname　FROMＣourseCLＥＦTJＯＩNSCOＮＣ．Cno=SＣ．CnｏＷＨEＲE　SC.CnoＩSNUＬL

例子解析:

如果存在部分课程为被人选择,则必定在Ｃoｕrｓｅ表中有但在SＣ表中没有出现，即在进行外连接时没人选的课程在与SC表构成的连接结果集中，对应的Snｏ、Cno、Ｇｒade列必定为空，所以只需**在连接后的结果中选出**ＳＣ表中Sno或Cno为空的元祖即可。

（统计计算机系每个学生的选课门数,包括没选课的学生）

SELECTS.SnoＡＳ学号,COUＮＴ（SC.Cno）AS选课门数ＦROＭStｕdｅntSＬEFTJOINSCON　S.Sｎｏ　=ＳC．Ｓｎｏ　WHERＥDｅｐt＝　'计算机系＇GROＵPBYＳ.Ｓno

例子解析:

 上述例子要求统计每个学生的….,所以在GROUPＢY分组时,是按照学生表中的学号来分。

而对于聚合函数COUNＴ,上述要求统计每个学生的选课门数，若写成COＵNＴ（Ｓ．Sno）或COUNT（*），则对没选课的学生都返回1,因为在外连接结果中，S.Ｓｎｏ不会是NUＬL，而ＣＯUNＴ（＊）函数本身也不考虑NULＬ,它是直接对元祖个数进行计数。

注意:

在对外连接的结果进行分组、统计等操作时，一定要注意分组依据列和统计列的选择。

1.４使用TOP限制结果集行数

在使用SELECT语句进行查询时,有时只需要前几行数据。

ＴOP（exprｅｓsiｏｎ）　[PERCENT][ＷITＨTIES]

∙１

∙eｘpreｓsion:

指定返回行数的数值表达式。

如果指定了PERCENT,expression将隐式转换成flｏａｔ，否则是bigint

∙ＰERCENT：

指定只返回结果集中前exｐｒessioｎ％行数据。

∙WITHTＩES：

指定从基本结果集中返回额外的数据行（只有在ＳＥLECT子句中包含了ＯRDERBY子句时，才能使用）。

ＴOP谓词写在SEＬＥCT单词的后面（如果有DISTINCT,则在DISTＩNＣＴ后面）。

（查询考试成绩最高的3个成绩。

列出学号、课程号、成绩）

SＥLECTTＯＰ　3　Sｎo,Cno,GradeFRＯMＳCOＲDEＲBYGradeDESC

若要包括并列第3名的成绩:

SＥＬECTTOＰ3Sno,Cno,GｒadeWITH　TＩESFROMSCORDERBYGradeDＥSC

２.数据更改功能

2.1插入数据

INSEＲTＩＮTO表名[（列名）]　VALUＥS　（值）

∙1

（１）简单插入语句

IＮSERTＩNTO　StudｅntＶALUES（'001','陈东','男',＇1996/６/23'，'信息管理系'）

∙1

（2）多行插入语句

INSERTIＮTＯSＣＶAＬUES（'001',＇Ｃ001',90），

　　　　　　（'001','C０0２',30），

　　　　　　　　　（＇001'，'C0０5＇,NULL）

（３）不按表顺序插入语句 按与表列顺序不同的顺序插入数据

ＩNSERTIＮTOStｕｄeｎt（Sno,Ｓｎame,Ｓex,Dept）VALＵES（'0０1＇，'陈东'，＇男'，'199６／６／23'，'信息管理系'）

２．2更新数据

UPDATE表名SET列名　=　值

（１）无条件更新

UPＤAＴESCSETＧradｅ=Grａde+1０

（2）有条件更新

（将“C001”号课程的学分改成5分）

UPDＡTEＣｏurｓeSＥＴGrａde=5WHEREＣno='Ｃ00１'

（将计算机系全体学生的成绩加5分）

ＵPDAＴESＣSＥTGraｄｅ　=　Graｄｅ+５FROMSC　JOIＮStudeｎtS　ONＳ.Ｓnｏ＝SC.SnoWHERE　Ｄept='计算机系'

２．３　删除数据

DEＬETE　[ＴOP（exｐression）[ＰERCENT]］

ＦROM表名

（１）无条件删除

ＤEＬETEＦROM　Studenｔ

（2）有条件删除

（删除所有考试成绩不合格的学生的选课记录）

DEＬETＥFＲＯM　SＣWＨEREGrａdｅ　<　60

（删除Sｔｕｄeｎｔ表中2.５%的行数据）

DELＥTＥTOP　（2.5）PERCＥＮTＦROMSｔudeｎt

四．高级查询

1.　CASE函数

CＡSE函数是一种多分支函数，它可以根据条件列表的值返回多个可能的结果表达式中的一个。

1．1简单ＣAＳE函数

CASEiｎput＿exprｅsｓioｎ

WHEＮ　ｗhen_eｘpressioｎ　THEN　resuｌt_exprｅsｓion

　　[...n］

　[ELSE　else_ｅｘpressiｏn]

ＥND

∙input_eｘpｒesｓion：

所计算的表达式,可以是一个变量名、字段名、函数或子查询。

∙when_expressiｏｎ ：

要与iｎput_ｅxpressioｎ进行比较的简单表达式。

简单表达式中不可包含比较运算法，只需给出被比较的表达式或值。