数据库应用与开发课件第5章

第5章使用SQL语言管理和设计数据库

主要学习内容SQLServer数据管理基本数据查询复杂数据查询使用SQL语句设计和管理数据库

学习目标了解SQLServer的数据管理功能掌握基本数据查询方法熟练掌握使用SQL语句设计和管理数据库

导入案例使用软件产品提供的菜单服务仿佛是到餐厅用餐，即便你第一次光临，也能在餐桌上摆放的菜单帮助下，找到你想要的一切。而软件语言如同你光顾久了的餐厅的外卖服务，只需拨动一个电话号码，你也能吃到你熟悉的餐点。如果与餐厅上下相处的十分融洽，或许你还可以在特殊时候订制菜谱，然后由外卖朋友送上门。当然不同的餐馆提供的服务不同，人性化服务的程度也各异。幸运的是，SQLServer不仅提供了全面的菜单命令，帮助我们建设数据库、保护数据库、编辑数据库，还提供了更加灵活全面的Transact-SQL(T-SQL)语言，帮助用户实现数据库数据的编辑和各种效果的查询统计，以及数据库的建设和安全保卫。本章主要介绍符合SQL标准部分的T-SQL语言。

5.1SQLServer数据管理T-SQL与SQL插入数据更新数据行删除数据行Merge语法

5.1.1T-SQL与SQLSQL(StructuredQueryLanguage)，即结构化查询语言。它包括数据定义语言DDL、数据管理语言DML和数据控制语言DCL。DDL用来建立数据库、数据库对象和定义其列，语句有CREATETABLE、DROPTABLE等。DML语言用来查询、插入、删除和修改数据库中的数据；语句有SELECT、INSERT、UPDATE、DELETE等；DCL语言用来控制存取许可、存取权限等，语句有：GRANT、REVOKE等。

为什么使用SQL一方面可以帮助关系数据库自身灵活操作数据库；一方面可以帮助应用程序方便管理数据。

T-SQLT-SQL（Transact-SQL），是SQL的加强版，包括：1）DML，有SELECT、INSERT、UPDATE、DELETE等。2）DCL，有：GRANT、REVOKE等。3）DDL，有CREATETABLE、DROPTABLE等。4）变量说明、流程控制、功能函数：定义变量、判断、分支、循环结构等；函数包括日期函数、数学函数、字符函数、系统函数等。

运算符运算符含义运算符含义运算符含义=等于>=大于或等于!非>大于<=小于或等于<小于<>不等于

通配符

通配符解释示例‘_’一个字符ALike'C_'%任意长度的字符串BLike'CO_%'[]括号中所指定范围内的一个字符CLike'9W0[1-2]'[^]不在括号中所指定范围内的一个字符DLike‘%[A-D][^1-2]'

逻辑表达式逻辑运算符说明示例AND逻辑与1AND1=1;1AND0=0;0AND0=0;OR逻辑或1OR1=1;1OR0=1;0OR0=0;NOT逻辑非NOT1=0;NOT0=1;

5.1.2插入数据1.插入一条数据行

INSERT[INTO]<表名>[列名]VALUES<值列表>例：有表Student(sNo,sName,sAddress,sGrade,sEmail,sSex)insertintoStudent(sNo,sName,sAddress,sGrade,sEmail,sSex)

values('020110001','张黎','上海','2011','ZQC@S',0)

插入语句的注意事项

1）每次插入一行数据，不可能只插入半行或者几列数据，因此，插入的数据是否有效将按照整行的完整性的要求来检验；2）每个数据值的数据类型、精度和小数位数必须与相应的列匹配；3）不能为标识列指定值，因为它的数字是自动增长的；4）如果在设计表的时候就指定了某列不允许为空，则必须插入数据；5）插入的数据项，要求符合检查约束的要求。6）具有缺省值的列，可以使用DEFAULT（缺省）关键字来代替插入的数值插入一条示例

插入多行数据

1）从已知表向已知表插入若干条满足条件记录INSERTINTO<表名>(列名)SELECT<列名>FROM<源表名>例：INSERTINTOTongXunLu(姓名,地址,电子邮件)SELECT SName,SAddress,SEmailFROM Student

插入多行数据2）从已知表向未知表插入多行数据SELECT(列名)INTO<表名>FROM<源表名>注：该语句只能执行一次。例：SELECTStudent.SName,Student.SAddress,Student.SEmailINTOTongXunLu1FROMStudent

插入多行数据3）SELECTIDENTITY(数据类型,标识种子,标识增长量)AS列名INTO新表FROM原始表例：SELECTStudent.SName,Student.SAddress,Student.SEmail,IDENTITY(int,1,1)AsStudentIDINTOTongXunLu2FROMStudent从已知表向其他表一次插入多条记录

插入多行数据4）插入多行常值记录，语法如下：INSERTINTO<表名>(列名)SELECT<列名>UNIONSELECT<列名>UNION……SELECT<列名>

插入多行数据例：INSERTSTUDENTS(SName,SGrade,SSex)SELECT'测试女生1',7,0UNIONSELECT'测试女生2',7,0UNIONSELECT'测试女生3',7,0UNIONSELECT'测试女生4',7,0UNIONSELECT'测试女生1',7,0UNIONSELECT'测试男生2',7,1UNIONSELECT'测试男生3',7,1UNIONSELECT'测试男生4',7,1UNIONSELECT'测试男生5',7,1

插入多行数据5）简化的插入多条记录的sql语句向已知表中插入多条常值

5.1.3更新数据行1.更新表数据语法

UPDATE<表名>SET<列名1=更新值1>,……SET<列名n=更新值n>[WHERE<更新条件>]例：用常量作更新值。UPDATEStudentSETSSEX=0例：有条件更新UPDATEStudentSETSAddress='北京'WHERESAddress='上海'例：用表达式更新UPDATEstudentSETsSex=sSex+1WHEREsSex=1无条件和有条件更新数据

更新数据行2.用其他表中数据更新表数据语法

UPDATE<表名1>SET<表名1．列名=表名2．列名>from<表名1>,<表名2>[WHERE<更新条件>]

更新数据行3.允许使用复合赋值操作符

更新前

更新后

5.1.4删除数据行1.删除数据行语法

DELETEFROM<表名>[WHERE<删除条件>]例：DELETEFROMStudentWHERESName='张青'

删除数据行2.清空表中数据中数据TRUNCATETABLE<表名>

5.1.5Merge语法Merge语法是在一条语句中同时执行插入，更新，删除这三个操作。操作原理是根据与源表联接的结果，对目标表执行插入、更新或删除操作。例如，根据在另一个表中找到的差异在一个表中插入、更新或删除行，可以将两个表进行同步。

Merge语法Merge语法包含4个主要的子句：Merge子句用于指定进行insert、delete和update操作的目标表或视图，Using子句用于指定要与目标数据连接的数据源，ON子句用于指定目标数据与数据源联接位置的匹配条件，When子句用于根据ON子句的结果指定的操作。

Merge语法例：usetestDB--创建一个订单表CREATETABLEOrders(orderIDint,customerIDchar(10))GO

Merge语法--往订单表中添加两行记录INSERTINTOOrdersVALUES(1,'2012010101'),(2,'2012010102')--复制订单表中第一条数据到新建表Order2SELECT*INTOOrders2FROMOrdersWHEREOrderID=1--显示两个表初值select*fromordersselect*fromorders2--将Order2表的数据进行更新UPDATEorders2SETCustomerID='2012010103'

Merge语法--合并两个表MERGEOrdersASo1USINGOrders2ASo2ONo2.OrderID=o1.OrderIDWHENMATCHEDTHENUPDATESETo1.CustomerID=o2.CustomerID--如--果匹配到了，就更新掉目标表WHENNOTMATCHEDTHENINSERTVALUES(o2.OrderID,o2.CustomerID)--如--果匹配不到，就插入WHENNOTMATCHEDBYSOURCETHENDELETE;-----如果来源表无法匹配到，就删除--显示修改后表select*fromordersselect*fromorders2go

Merge例题

小结SQL(结构化查询语言)是数据库能够识别的通用指令集。SQLServer中的通配符经常和LIKE结合使用来进行不精确的限制。WHERE用来限制条件，其后紧跟条件表达式。一次插入多行数据，可以使用INSERT…SELECT…、SELECT…INTO…或者UNION关键字来实现。使用UPDATE更新数据，一般都有限制条件。使用DELETE删除数据时，不能删除被外键值所引用的数据行。

5.2基本数据查询基本查询基本函数综合应用

5.2.1基本查询1.查询基本语法：SELECT<列名>FROM<表名>[WHERE<查询条件表达式>][ORDERBY<排序的列名>[ASC或DESC]]use数据库名--打开数据库

SELECTsNo,SName,SAddressFROMStudentWHERESSEX=1ORDERBYsNo

基本查询2.查询全部的行和列例：SELECT*FROMStudent

基本查询3.查询部分行例：SELECTsNo,SName,SAddressFROMStudentWHERESAddress='北京‘例：SELECTsNo,SName,SAddressFROMStudentWHERESAddress<>'上海'基本查询示例

基本查询4.数据查询-列名1)使用AS来命名列例：SELECTsNoAS学生编号,SNameAS学生姓名,SAddressAS学生地址FROMStudentWHERESAddress<>'北京'例：SELECTsAddress+'and'+sEmailAS'地址及邮箱'FROMstudent

基本查询4.数据查询-列名

2） 使用=来命名列例：SELECT'地址及邮箱'=sAddress+'and'+sEmailFROMstudent3）使用常量列例：SELECT姓名=SName,地址=SAddress,‘河北新龙’AS学校名称

FROMStudents命名列例题

基本查询4.数据查询-列名4）判断一行中的数据项是否为空例：SELECTSNameFromStudentwheresEmailISNULL

基本查询5.数据查询-限制行数1）限制固定行数

例：SELECTTOP5SName,SAddressFROMStudentWHERESSex=1

2）返回百分之多少行

例：SELECTTOP20PERCENTSName,SAddressFROMStudentWHERESSex=1限制行数查询

基本查询6.数据查询-排序1） 升序排列ASC是默认升序排序命令，可以缺省。例：select*fromstuInfoorderbystuNoASC

基本查询6.数据查询-排序2）降序排列DESC是降序排序命令。例：select*fromstuInfoorderbystuNoDESC

基本查询6.数据查询-排序3）按照表达式排序例：SELECTstuNoAs学员编号,(Score*0.9+5)As综合成绩FROMScoresWHERE(Score*0.9+5)>60ORDERBYScore基本查询6.数据查询-排序4）按照新命名列排序例：SELECTstuNo+'.'+cNoAS[学号.科目号]FromScoresUnionSELECTstuNo+'.'+cNoAS[学号.科目号]FromScores2ORDERBY[学号.科目号]DESC

基本查询6.数据查询-排序5）按多列排序例：SELECTstuNoAs学员编号,ScoreAs成绩FROMScoresWHEREScore>60ORDERBYScore,cNo

排序查询示例

基本查询7.distinct-去掉重复记录行例：在成绩表scores(stuNo,cNo,score)中查询参加考试的学生考号SELECTdistinctstuNofromscoresdistinct查询结果

5.2.2基本函数函数名描述举例CHARINDEX用来寻找一个指定的字符串在另一个字符串中的起始位置SELECTCHARINDEX('ACCP','MyAccpCourse',1)返回：4LEN返回传递给它的字符串长度SELECTLEN('SQLServer课程')返回：12LOWER把传递给它的字符串转换为小写SELECTLOWER('SQLServer课程')返回：sqlserver课程UPPER把传递给它的字符串转换为大写SELECTUPPER('sqlserver课程')返回：SQLSERVER课程LTRIM清除字符左边的空格SELECTLTRIM('周智宇

')返回：周智宇

（后面的空格保留）RTRIM清除字符右边的空格SELECTRTRIM('周智宇

')返回：

周智宇（前面的空格保留）RIGHT从字符串右边返回指定数目的字符SELECTRIGHT('买卖提.吐尔松',3)返回：吐尔松REPLACE替换一个字符串中的字符SELECTREPLACE('莫乐可切.杨可','可','兰')返回：莫乐兰切.杨兰STUFF在一个字符串中，删除指定长度的字符，并在该位置插入一个新的字符串SELECTSTUFF('ABCDEFG',2,3,'我的音乐我的世界')返回：A我的音乐我的世界EFG

字符串函数表

日期函数函数名描述举例GETDATE取得当前的系统日期SELECTGETDATE()返回：今天的日期DATEADD将指定的数值添加到指定的日期部分后的日期SELECTDATEADD(mm,4,’01/01/99’)返回：以当前的日期格式返回05/01/99DATEDIFF两个日期之间的指定日期部分的区别SELECTDATEDIFF(mm,’01/01/99’,’05/01/99’)返回：4DATENAME日期中指定日期部分的字符串形式SELECTDATENAME(dw,’01/01/2000’)返回：SaturdayDATEPART日期中指定日期部分的整数形式SELECTDATEPART(day,’01/15/2000’)返回：15

数学函数表函数名描述举例ABS取数值表达式的绝对值SELECTABS(-43)返回：43CEILING返回大于或等于所给数字表达式的最小整数SELECTCEILING(43.5)返回：44FLOOR取小于或等于指定表达式的最大整数SELECTFLOOR(43.5)返回：43POWER取数值表达式的幂值SELECTPOWER(5,2)返回：25ROUND将数值表达式四舍五入为指定精度SELECTROUND(43.543,1)返回：43.5Sign对于正数返回+1，对于负数返回-1，对于0则返回0SELECTSIGN(-43)返回：-1Sqrt取浮点表达式的平方根SELECTSQRT(9)返回：3

系统函数表函数名描述举例CONVERT用来转变数据类型SELECTCONVERT(VARCHAR(5)，12345)返回：字符串12345CURRENT_USER返回当前用户的名字SELECTCURRENT_USER返回：你登录的用户名DATALENGTH返回用于指定表达式的字节数SELECTDATALENGTH('中国A盟')返回：7HOST_NAME返回当前用户所登录的计算机名字SELECTHOST_NAME()返回：你所登录的计算机的名字SYSTEM_USER返回当前所登录的用户名称SELECTSYSTEM_USER返回：你当前所登录的用户名USER_NAME从给定的用户ID返回用户名SELECTUSER_NAME(1)返回：从任意数据库中返回“dbo”

5.2.3综合应用【例5.1】：某公司做了一批手机充值卡，充值密码是随机生成的，现在出现这个问题：充值密码里面的“o和0”（哦和零）“l和1”（哎和一），用户反映说看不清楚，公司决定，把存储在数据库中的密码中所有的“哦”都改成“零”，把所有的“l”都改成“1”；请编写SQL语句实现以上要求；数据库表名：Card；密码字段名：PassWord；

综合应用-I分析：这是更新语句，需要使用UPDATE语句；因为牵涉到字符串的替换，需要使用到SQLServer中的函数Replace；解决：UpdateCardSetPassWord=Replace(密码,'o','0')UpdateCardSetPassWord=Replace(密码,'l','1')或者写成一条语句Update CardSet PassWord=Replace(Replace(密码,'o','0'),'l','1')

综合应用-II【例5.2】：在数据库表中有以下字符数据，如：

1-1、1-2、1-3、1-10、1-11、1-108、1-18、1-31、1-15、2-1、2-2

现在希望通过SQL语句进行排序，并且首先要按照前半部分的数字进行排序，然后再按照后半部分的数字进行排序，输出要排成这样：

1-1、1-2、1-3、1-10、1-11、1-15、1-18、1-31、1-108、2-1、2-2数据库表名：ArticleNo；字段名：ListNumber；

综合应用-II分析：这是查询语句，需要使用SELECT语句需要使用到ORDERBY进行排序，并且在ORDERBY的排序列中，也需要重新计算出排序的数字前半部分的数字，可以从先找到“-”符号的位置，然后，取其左半部分，最后再使用Convert函数将其转换为数字：Convert(int,Left(ListNumber,CharIndex('-',ListNumber)-1))后半部分的数字，可以先找到“-”符号的位置，然后把从第一个位置到该位置的全部字符替换为空格，最后再使用Convert函数将其转换为数字：Convert(int,Stuff(ListNumber,1,Charindex('-',ListNumber),''))

综合应用-II解决：SELECTListNumberFROMArticleNoORDERBYConvert(int,Left(ListNumber,CharIndex('-',ListNumber)-1)),Convert(int,Stuff(ListNumber,1,Charindex('-',ListNumber),''))思考：还有其他的办法吗？

小结查询将逐行筛选表中的数据，最后符合要求的记录重新组合成“记录集”，记录集的结构类似于表结构；判断一行中的数据项是否为空，使用ISNULL；使用ORDERBY进行查询记录集的排序，并且可以按照多个列进行排序；在查询中，可以使用常量、表达式、运算符；在查询中使用函数，能够像在程序中那样处理查询得到的数据项。

5.3复杂数据查询模糊查询聚合函数分组汇总GroupingSets多表联结查询综合应用

5.3.1模糊查询1.LIKE：查询时，字段中的内容并不一定与查询内容完全匹配，使用LIKE和通配符。例：SELECTstuNameAS姓名FROMstuInfoWHEREstuNameLIKE'x%'

模糊查询2.ISNULL：把某一字段中内容为空的记录查询出来。例：SELECTstuNameAs姓名,stuAddressAS地址FROMstuInfoWHEREstuAddressISNULL

模糊查询3.BETWEENAND：把某一字段中内容在特定范围内的记录查询出来。例：SELECTstuNo,ScoreFROMSCORESWHEREScoreBETWEEN60AND80

模糊查询4.IN：把某一字段中内容与所列出的查询内容列表匹配的记录查询出来例：SELECTstuNameAS学员姓名,stuAddressAs地址FROMstuInfoWHEREstuAddressIN('北京','广州','上海')模糊查询

5.3.2聚合函数1.SUM：例：SELECTSUM(score)FROMscoresWHEREcNo='0001'例：错误写法！SELECTSUM(score),stuNoFROMscoresWHEREcNo='0001'

聚合函数2.AVG：例：SELECTAVG(score)AS平均成绩FromScoresWHEREScore>=60

聚合函数3.MAX、MIN：例：SELECTAVG(score)AS平均成绩,MAX(Score)AS最高分,MIN(Score)AS最低分FromScoresWHEREScore>=60

聚合函数4.COUNT：例：SELECTCOUNT(*)AS及格人数FromScoresWHEREScore>=60

聚合函数5.注意事项：聚合函数不单独出现在条件语句中。只与返回结果值数目一致的列一起查询。聚合函数

5.3.3分组汇总基本语法：SELECT[<列名x>]，[聚合函数]FROM<表名>WHERE条件GROUPBY<列名x>HAVING条件

分组汇总注：分组查询—GROUPBYWHERE子句从数据源中去掉不符合其搜索条件的数据GROUPBY子句搜集数据行到各个组中，统计函数为各个组计算统计值HAVING子句去掉不符合其组搜索条件的各组数据行

分组汇总例：SELECT 部门编号,COUNT(*)FROM 员工信息表WHERE 工资>=2000GROUPBY 部门编号HAVING COUNT(*)>1

5.3.4GroupingSets例：有数据库Sale，表sales(productID,productName,saleAmout,saleMonth)。按照销售量升序显示表sales中数据

GroupingSets使用SQL语句分组显示各产品的销售总量。SELECTproductNameas产品名,sum(saleAmout)as销售总量fromsalesGroupbyGROUPINGSETS((productName));

GroupingSets效果等同于selectproductNameas产品名,sum(saleAmout)as销售总量fromsalesGroupbyproductName显示每个产品的销售总量

GroupingSets但是如果使用两个GROUPINGSETS分组就可以实现分别分组汇总了：SELECTproductNameas产品名,sum(saleAmout)as销售总量fromsalesGroupbyGROUPINGSETS((productName),());显示每个产品的销售总量和所有产品的销售总和

GroupingSets当然，可以给分组汇总结果起个名字“汇总”，而不必用NULL显示：

select[sales.Rep]=casewhenproductNameisnullThen'汇总'elseproductNameend,sum(saleAmout)as销售总量fromsalesGroupbyGROUPINGSETS((productName),());显示每个产品的销售总量和带名字的所有产品的销售总和

GroupingSets我们还可以进行三级甚至四级分类汇总，例如将产品按月份分类汇总显示：

SELECTproductNameas产品名,sum(saleAmout)as销售总量,saleMonthas销售月份FromsalesGroupbyGROUPINGSETS((saleMonth,productName),(saleMonth),());三级甚至四级分类汇总

GroupingSets值得一提的是，CUBE和ROLLUP是在SQLServer2005中新增的groupby扩展，用来创建分组汇总。例如上面的实现效果也可以由以下SQL语句实现：

selectproductNameas产品名,sum(saleAmout)as销售总量,saleMonthas销售月份fromsalesGroupbysaleMonth,productNamewithROLLUP使用ROLLUP分组汇总

5.3.5多表联结查询1.内联结(INNERJOIN)语法：1）两表连接：SELECT <表名。列名>From左表[INNER]JOIN 右表ON 左表。列=右表。列例：查询参加考试的同学的姓名、考试科目号码、考试成绩：SELECT S.stuName,C.cNo,C.scoreFrom ScoresASCINNERJOIN stuInfoASSON C.stuNo=S.stuNo

多表联结查询1.内联结(INNERJOIN)语法：2）三表内连接：例：查参加考试的学生姓名、考试科目名称、考试成绩：SELECTS.stuNameAS姓名,CS.cNameAS课程,C.ScoreAS成绩FROMstuInfoASSINNERJOINScoresASCON(S.stuNo=C.stuNo)INNERJOINcourseInfoASCSON(CS.cNo=C.cNo)

多表联结查询2.普通多表查询：SELECT <表名.列名>From表1，表2WHERE左表.列=右表.列

例：SELECT stuInfo.stuName,scores.cNo,scores.scoreFromstuInfo,scoresWHEREstuInfo.stuNo=scores.stuNo多表连接查询示例

多表联结查询3.外联结：1)左外联结(LEFTJOIN)：例：查询所有学生考试情况SELECTS.stuName，C.cNo，C.ScoreFromstuInfoASSLEFTJOIN ScoresASCON C.stuNo=S.stuNo

多表联结查询3.外联结：2)右外联结(RIGHTJOIN)：例：SELECTS.stuName，C.cNo，C.ScoreFromstuInfoASSRightJOIN ScoresASCON C.stuNo=S.stuNo

多表联结查询4.UNION

两个结构相同的表的连接查询，相同列合并、记录行做或运算。语法：

Select<列1>,<…>,<列n>from表1UNIONSelect<列1>,<…>,<列n>from表2例：在数据库jsj2019中有结构相同的两张表：scores(stuNo,cNo,score)和表scores2(stuNo,cNo,score)，求selectstuNo,cNofromsocresUNIONselectstuNo,cNofromscores2？

5.3.6综合应用【例5.3】：在给发掘出来的远古时代的谷物种子做育种实验的过程中，种子每3粒一组，组内每颗种子的培育方法不同。现要求查看所有组内培育方法相同的种子的发育值的平均值。数据库表名：TABLE1

字段名：A

主键字段：IDKEY（标识列，种子：1；增长量：1）

综合应用-I分析：可以依靠标识列的值来进行判断和选取，但是因为操作过程中，数据行可能存在增加、修改和删除，因此标识列的数据值未必完全有序，也就不“完全可靠”。例如标识列值为3，但并不一定是第三行，因为如果第二行被删除了，它就是第二行。根据我们前面使用过的SELECT…INTO，可以创建一张新表，顺便创建新的标识列，再在新的标识列上执行除3取余判断。判断依据：标识列值%3等于0、标识列值%3等于1和标识列值%3等于2。

综合应用-ISELECT A,IDENTITY(int,1,1)ASID

INTO TABLE2FROM TABLE1SELECT AVG(A)AS1号种子发育平均值FROM TABLE2WHERE ID%3=1SELECT AVG(A)AS2号种子发育平均值FROM TABLE2WHERE ID%3=2SELECT AVG(A)AS3号种子发育平均值FROM TABLE2WHERE ID%3=0

综合应用-II【例5.4】：有学生基本信息表stuInfo（stuNo,stuName）和学生成绩表Scores(stuNo,cNo,score)。现在要求建立新表stuAllInfo(stuNo,stuName,cNo,score)，存储所有学生的考试信息。

综合应用-II分析：这是数据插入的操作，因此要使用INSERT语句来进行。参加考试的同学的考试信息在Scores表里，所以可以使用INSERTINTO…SELECT结构。但是还要插入stuName数据项。所以要用多表连接查询。但是还有同学可能没有参加任何一科的考试，所以根本不在scores成绩表中。

综合应用-II等值的多表连接和不等值的多表连接都不能找到这些同学。在前面的联结查询中，使用INNERJOIN…ON可以找出所有参加考试的学生信息项，编写以下T-SQL：SELECTstuInfo.stuNo,stuName,cNo,scorefromstuInfoINNERJOINscoresONstuInfo.stuNo=scores.stuNo但是如何把未参加任何一科考试的同学也显示其中？如下可以吗？SELECTstuInfo.stuNo,stuName,cNo,scorefromstuInfoINNERJOINscoresONstuInfo.stuNo<>scores.stuNo以上把“=”简单地改为“<>”，不仅不能找出未参加考试的同学，而且所找到的项很多，也没有意义，因此，这种方法不可行。这也说明一点：内联结查询的基础是on后面的等值比较，非等值比较就不是内联查询了。

综合应用-II考虑我们在学习过的左外连接查询，能够查询出左表中存在而相关表不存在的数据项。所以可以使用如下语句查询出所有考生。SELECTstuInfo.stuNo,stuName,cNo,scorefromstuInfoLeftOUTJOINscoresONstuInfo.stuNo=scores.stuNo最后，使用插入同时建立新表语句子查询插入新建表stuAllInfo

综合应用-II解决：SELECTstuInfo.stuNo,stuName,cNo,scoreINTOstuAllInfofromstuInfoLeftJOINscoresONstuInfo.stuNo=scores.stuNoSELECT*FROMstuAllInfo在查询分析器中选择工具->选项->结果->默认结果目标修改为“显示为文本”。

小结使用LIKE、BETWEEN、IN关键字，能够进行模糊查询——条件不明确的查询。聚合函数能够对列生成一个单一的值，对于分析和统计通常非常有用。分组查询是针对表中不同的组，分类统计和输出，GROUPBY子句通常会结合聚合函数一起来使用。HAVING子句能够在分组的基础上，再次进行筛选。多个表之间通常使用联结查询。最常见的联结查询是内联结（INNERJOIN），通常会在相关表之间提取引用列的数据项。

5.4使用SQL语句设计和管理数据库创建数据库删除数据库建表删除表为表加约束删除约束安全管理

5.4.1创建数据库语法：CREATEDATABASE数据库名

ON[PRIMARY](<数据文件参数>[，…n][<文件组参数>])[LOGON](<日志文件参数>[，…n])注：PRIMARY：主文件组，可选参数，默认；[]表示可选参数。

【例5.5】创建数据库studentDB，保存在D：\project目录，数据文件增长率：15％，初始大小5MB，日志文件初始大小2MB，文件增长率按1MB自动增长。

CREATEDATABASEstudentDBONPRIMARY--默认就属于PRIMARY主文件组，可省略

(NAME='studentDB',--主数据文件的逻辑名

FILENAME='D:\project\studentDB.mdf',--主数据文件的物理名

SIZE=5mb,--主数据文件初始大小

MAXSIZE=100mb,--主数据文件增长的最大值

FILEGROWTH=15%--主数据文件的增长率)LOGON(NAME='studentDB_log',FILENAME='D:\project\studentDB_log.ldf',SIZE=2mb,FILEGROWTH=1MB)GO

【例5.6】在D:\下创建数据库DB，包含一个主数据文件和一个从数据文件，数据文件增长率都按10%自动增长，初始大小为1MB，日志文件增长率都按1MB自动增长，初始大小都为1MB。

createdatabaseDBon(name='db1',filename='d:\db1.mdf',size=1,filegrowth=10%),(name='db2',filename='d:\db2.ndf',size=1,filegrowth=10%)

logon(name='db1_log',filename='d:\db1_log.ldf',size=1,filegrowth=1),(name='db2_log',filename='d:\db2_log.ldf',size=1,filegrowth=1)go

多个数据文件的好处：如硬盘满了，希望买个硬盘再继续存放数据，这时就可以将一个数据文件放在的D盘，另一个可能在另一个硬盘的H盘等。

5.4.2删除数据库删除数据库语法：DROPDATABASE数据库名例：是否已经存在数据库stuDB，存在删除重建。提示：新建的数据库信息在数据库的视图sys.databases中可以找到，所以我们只需要查看master数据库的sys.databases视图的name列即可。

解决方案：USEmaster--设置当前数据库为master，以便访问sys.databases系统视图GOIFEXISTS（SELECT*FROMsys.databasesWHEREname='stuDB'）

DROPDATABASEstuDBCREATEDATABASEstuDBON(…..)LOGON(…)GO注：EXISTS(查询语句)检测语句的用法，如果查询语句返回1条以上的记录，即表示存在满足条件的记录，则返回为true，否则为false

5.4.3建表创建表的语法:CREATETABLE表名

(

字段1数据类型列的特征，字段2数据类型列的特征，

...)

注意：1．数据类型：数据表的字段，一般都要求在数据类型后加“()”，并在其中声明长度。比如，char，varchar等。但int，smallint，float，datetime，image，bit和money类型不需要声明字段的长度。2．列的特征：包括该列是是否为空（NULL）、是否是标识列（自动编号）、是否有默认值、是否为主键等。

【例5.7】创建学员信息表stuInfo，具体要求如下表所示。字段类型描述stuNoCHAR(6)非空stuNameVARCHAR(20)非空stuAgeINT非空stuIDNUMERIC(18,0),身份证号stuSeatSMALLINT标识列stuAddressTEXT

实现代码如下：实现代码如下：USEstudentDB--将当前数据库设置为studentDBGOCREATETABLEstuInfo/*-创建学员信息表-*/(stuNameVARCHAR(20)NOTNULL,--姓名，非空（必填）

stuNoCHAR(6)NOTNULL,--学号，非空（必填）

stuAgeINTNOTNULL,--年龄，INT类型默认为4个字节

stuIDNUMERIC(18,0),--身份证号

stuSeatSMALLINTIDENTITY(1,1),--座位号，自动编号

stuAddressTEXT--住址，允许为空，即可选输入)GO

注意：NUMERIC(18,0)代表18位数字，小数位数为0有些类型不必规定长度，要记住，比如：int、smallint、datetime。

【例5.8】创建学员程序设计成绩表stuMarks字段类型描述ExmaNoCHAR(7)考号，非空stuNoCHAR(6)学号，非空writtenExamINT笔试成绩，非空LabExamINT机试成绩，非空

CREATETABLEstuMarks(ExamNoCHAR(7)NOTNULL,--考号

stuNoCHAR(6)NOTNULL,--学号

writtenExamINTNOTNULL,--笔试成绩

LabExamINTNOTNULL--机试成绩)GO

5.4.4删除表删除表的语法：DROPTABLE表名【例5.9】如果当前数据库中已存在stuInfo表，此次创建时系统将提示出错。如何解决呢？分析：当表中存在stuInfo表时，在studentDB数据库的系统视图sys.objects中检查name列即可。

解决方案：USEstudentDB--将当前数据库设置为studentDB，以便在studentDB数据库中建表GOIFEXISTS（SELECT*FROMsys.objectsWHEREname=’stuInfo’）

DROPTABLEstuInfoCREATETABLEstuInfo/*-创建学员信息表-*/(…..)GO

5.4.5为表加约束SQLServer中常用的约束类型如下所示：主键约束（PrimaryKeyConstraint）：要求主键列数据唯一，并且不允许为空唯一约束（UniqueConstraint）：要求该列唯一，允许为空，但只能出现一个空值。检查约束（CheckConstraint）：某列取值范围限制、格式限制等，如有关年龄的约束默认约束（DefaultConstraint）：某列的默认值，如我们的男性学员较多，性别默认为“男”外键约束（ForeignKeyConstraint）：用于两表间建立关系，需要指定引用主表的那列

添加约束的语法：ALTERTABLE表名

ADDCONSTRAINT约束名约束类型具体的约束说明

约束名的取名规则推荐采用：约束类型_约束字段主键（PrimaryKey）约束：如PK_stuNo唯一（UniqueKey）约束：如UQ_stuID默认（DefaultKey）约束：如DF_stuAddress检查（CheckKey）约束：如CK_stuAge外键(ForeignKey)约束：如FK_stuNo

【例5.10】要求在stuInfo表（见表5-8）上添加约束：①添加主键约束（stuNo作为主键），②唯一约束（因为每人的身份证号全国唯一），③默认约束（如果地址不填，默认为“地址不详”），④检查check约束：要求年龄只能在15－40岁之间，⑤外键约束（主表stuInfo和从表stuMarks建立关系，关联字段为stuNo）。

实现代码如下：①加主键约束：ALTERTABLEstuInfoADDCONSTRAINTPK_stuNoPRIMARYKEY(stuNo)②加唯一约束ALTERTABLEstuInfoADDCONSTRAINTUQ_stuIDUNIQUE(stuID)③加默认约束ALTERTABLEstuInfoADDCONSTRAINTDF_stuAddressDEFAULT('地址不详')FORstuAddress④加检查约束ALTERTABLEstuInfoADDCONSTRAINTCK_stuAgeCHECK(stuAgeBETWEEN15AND40)⑤加外键约束ALTERTABLEstuMarksADDCONSTRAINTFK_stuNoFOREIGNKEY(stuNo)REFERENCESstuInfo(stuNo)GO

各类表约束除了在数据表创建完毕之后添加，也可以在创建表时添加。【例5.11】使用SQL语句在创建表stuInfo和stuMarks的同时，添加如【例5.10】中的约束。

实现代码如下：USEstudentDB--将当前数据库设置为studentDBGOCREATETABLEstuInfo/*-创建学员信息表-*/(stuNameVARCHAR(20)NOTNULL,--姓名，非空（必填）

stuNoCHAR(6)PrimaryKey,--学号，非空（必填）

stuAgeINTCHECK(stuAgeBETWEEN15AND40),--年龄stuIDNUMERIC(18,0),--身份证号

stuSeatSMALLINTIDENTITY(1,1),--座位号，自动编号

stuAddressTEXTDEFAULT('地址不详')--住址，允许为空，即可选输入

UNIQUE(StuID))GO

CREATETABLEstuMarks/*创建学员成绩表stuMarks*/(ExamNoCHAR(7),--考号

stuNoCHAR(6),--学号

writtenExamINT,--笔试成绩

LabExamINT,--机试成绩

Primarykey(ExamNo),FOREIGNKEY(stuNo)REFERENCESstuInfo(stuNo))GO

5.4.6删除约束如果错误地添加了约束，我们还可以删除约束，删除约束的语法：ALTERTABLE表名

DROPCONSTRAINT约束名

例：删除stuInfo表