1、第第5章章使用使用SQL语言管理和设语言管理和设计数据库计数据库主要学习内容SQLServer数据管理基本数据查询复杂数据查询使用SQL语句设计和管理数据库学习目标了解SQLServer的数据管理功能掌握基本数据查询方法熟练掌握使用SQL语句设计和管理数据库导入案例导入案例使用软件产品提供的菜单服务仿佛是到餐厅用餐，即便你第一次光临，也能在餐桌上摆放的菜单帮助下，找到你想要的一切。而软件语言如同你光顾久了的餐厅的外卖服务，只需拨动一个电话号码，你也能吃到你熟悉的餐点。如果与餐厅上下相处的十分融洽，或许你还可以在特殊时候订制菜谱，然后由外卖朋友送上门。当然不同的餐馆提供的服务不同，人性化服务的程
2、度也各异。幸运的是，SQLServer不仅提供了全面的菜单命令，帮助我们建设数据库、保护数据库、编辑数据库，还提供了更加灵活全面的Transact-SQL(T-SQL)语言，帮助用户实现数据库数据的编辑和各种效果的查询统计，以及数据库的建设和安全保卫。本章主要介绍符合SQL标准部分的T-SQL语言。5.1SQLServer数据管理数据管理T-SQL与SQL插入数据更新数据行删除数据行Merge语法5.1.1T-SQL与与SQLSQL(StructuredQueryLanguage)，即结构化查询语言。它包括数据定义语言DDL、数据管理语言DML和数据控制语言DCL。DDL用来建立数据库、数据库
3、对象和定义其列，语句有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、DROPTABL
4、E等。4）变量说明、流程控制、功能函数：定义变量、判断、分支、循环结构等；函数包括日期函数、数学函数、字符函数、系统函数等。运算符运算符 含义运算符含义运算符含义=等于=大于或等于!非大于=小于或等于小于不等于通配符通配符通配符 解释示例_一个字符A Like C_%任意长度的字符串B Like CO_%括号中所指定范围内的一个字符C Like 9W01-2不在括号中所指定范围内的一个字符D Like%A-D1-2逻辑表达式逻辑运算符说明示例AND逻辑与1AND1=1;1AND0=0;0AND0=0;OR逻辑或1OR1=1;1OR0=1;0OR0=0;NOT逻辑非NOT1=0;NOT0=1;5
5、.1.2插入数据插入数据1.插入一条数据行插入一条数据行INSERTINTO列名VALUES例：例：有表Student(sNo,sName,sAddress,sGrade,sEmail,sSex)insertintoStudent(sNo,sName,sAddress,sGrade,sEmail,sSex)values(020110001,张黎,上海,2011,ZQCS,0)插入语句的注意事项插入语句的注意事项1）每次插入一行数据，不可能只插入半行或者几列数据，因此，插入的数据是否有效将按照整行的完整性的要求来检验；2）每个数据值的数据类型、精度和小数位数必须与相应的列匹配；3）不能为标识列指
6、定值，因为它的数字是自动增长的；4）如果在设计表的时候就指定了某列不允许为空，则必须插入数据；5）插入的数据项，要求符合检查约束的要求。6）具有缺省值的列，可以使用DEFAULT（缺省）关键字来代替插入的数值插入一条示例插入多行数据插入多行数据1）从已知表向已知表插入若干条满足条件记录INSERTINTO(列名)SELECTFROM例：INSERTINTOTongXunLu(姓名,地址,电子邮件)SELECTSName,SAddress,SEmailFROMStudent插入多行数据插入多行数据2）从已知表向未知表插入多行数据SELECT(列名)INTOFROM注：该语句只能执行一次注：该语句
7、只能执行一次。例：SELECTStudent.SName,Student.SAddress,Student.SEmailINTOTongXunLu1FROMStudent插入多行数据插入多行数据3）SELECTIDENTITY(数据类型,标识种子,标识增长量)AS列名INTO新表FROM原始表例：SELECTStudent.SName,Student.SAddress,Student.SEmail,IDENTITY(int,1,1)AsStudentIDINTOTongXunLu2FROMStudent从已知表向其他表一次插入多条记录插入多行数据插入多行数据4）插入多行常值记录，语法如下：IN
8、SERTINTO(列名)SELECTUNIONSELECTUNIONSELECT插入多行数据插入多行数据例：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语句向已知表中插入多条
9、常值5.1.3更新数据行更新数据行1.更新表数据语法更新表数据语法UPDATESET,SETWHERE例：用常量作更新值。例：用常量作更新值。UPDATEStudentSETSSEX=0例：有条件更新例：有条件更新UPDATEStudentSETSAddress=北京北京WHERESAddress=上海上海例：用表达式更新例：用表达式更新UPDATEstudentSETsSex=sSex+1WHEREsSex=1无条件和有条件更新数据更新数据行2.用其他表中数据更新表数据语法用其他表中数据更新表数据语法UPDATESETfrom,WHERE更新数据行3.允许使用复合赋值操作符允许使用复合赋值操
10、作符更新前更新后5.1.4删除数据行删除数据行1.删除数据行语法删除数据行语法DELETEFROMWHERE例：DELETEFROMStudentWHERESName=张青删除数据行删除数据行2.清空表中数据中数据清空表中数据中数据TRUNCATETABLE5.1.5Merge语法语法Merge语法是在一条语句中同时执行插入，更新，删除这三个操作。操作原理是根据与源表联接的结果，对目标表执行插入、更新或删除操作。例如，根据在另一个表中找到的差异在一个表中插入、更新或删除行，可以将两个表进行同步。Merge语法语法Merge语法包含4个主要的子句：Merge子句用于指定进行insert、dele
11、te和update操作的目标表或视图，Using子句用于指定要与目标数据连接的数据源，ON子句用于指定目标数据与数据源联接位置的匹配条件，When子句用于根据ON子句的结果指定的操作。Merge语法语法例：usetestDB-创建一个订单表CREATETABLEOrders(orderIDint,customerIDchar(10)GOMerge语法语法-往订单表中添加两行记录INSERTINTOOrdersVALUES(1,2012010101),(2,2012010102)-复制订单表中第一条数据到新建表Order2SELECT*INTOOrders2FROMOrdersWHEREOrde
12、rID=1-显示两个表初值select*fromordersselect*fromorders2-将Order2表的数据进行更新UPDATEorders2SETCustomerID=2012010103Merge语法语法-合并两个表MERGEOrdersASo1USINGOrders2ASo2ONo2.OrderID=o1.OrderIDWHENMATCHEDTHENUPDATESETo1.CustomerID=o2.CustomerID-如-果匹配到了，就更新掉目标表WHENNOTMATCHEDTHENINSERTVALUES(o2.OrderID,o2.CustomerID)-如-果匹配不
13、到，就插入WHENNOTMATCHEDBYSOURCETHENDELETE;-如果来源表无法匹配到，就删除-显示修改后表select*fromordersselect*fromorders2goMerge例题小结小结SQL(结构化查询语言)是数据库能够识别的通用指令集。SQLServer中的通配符经常和LIKE结合使用来进行不精确的限制。WHERE用来限制条件，其后紧跟条件表达式。一次插入多行数据，可以使用INSERTSELECT、SELECTINTO或者UNION关键字来实现。使用UPDATE更新数据，一般都有限制条件。使用DELETE删除数据时，不能删除被外键值所引用的数据行。5.2基本数
14、据查询基本数据查询基本查询基本函数综合应用5.2.1基本查询1.查询基本语法：查询基本语法：SELECTFROMWHEREORDERBYASC或DESCuse数据库名-打开数据库SELECTsNo,SName,SAddressFROMStudentWHERESSEX=1ORDERBYsNo基本查询2.查询全部的行和列查询全部的行和列例：SELECT*FROMStudent基本查询3.查询部分行查询部分行例：SELECTsNo,SName,SAddressFROMStudentWHERESAddress=北京例：SELECTsNo,SName,SAddressFROMStudentWHERESA
15、ddress上海基本查询示例基本查询4.数据查询数据查询-列名列名1)使用使用AS来命名列来命名列例：SELECTsNoAS学生编号,SNameAS学生姓名,SAddressAS学生地址FROMStudentWHERESAddress北京例：SELECTsAddress+and+sEmailAS地址及邮箱FROMstudent基本查询4.数据查询数据查询-列名列名2）使用=来命名列例：SELECT地址及邮箱=sAddress+and+sEmailFROMstudent3）使用常量列）使用常量列例：SELECT姓名=SName,地址=SAddress,河北新龙AS学校名称FROMStudents
16、命名列例题基本查询4.数据查询数据查询-列名列名4）判断一行中的数据项是否为空例：SELECTSNameFromStudentwheresEmailISNULL基本查询5.数据查询数据查询-限制行数限制行数1）限制固定行数限制固定行数例：例：SELECTTOP5SName,SAddressFROMStudentWHERESSex=12）返回百分之多少行返回百分之多少行例：例：SELECTTOP20PERCENTSName,SAddressFROMStudentWHERESSex=1限制行数查询基本查询6.数据查询数据查询-排序排序1）升序排列升序排列ASC是默认升序排序命令，可以缺省。是默认升
17、序排序命令，可以缺省。例：例：select*fromstuInfoorderbystuNoASC基本查询6.数据查询数据查询-排序排序2）降序排列）降序排列DESC是降序排序命令。是降序排序命令。例：例：select*fromstuInfoorderbystuNoDESC基本查询6.数据查询数据查询-排序排序3）按照表达式排序）按照表达式排序例：SELECTstuNoAs学员编号,(Score*0.9+5)As综合成绩FROMScoresWHERE(Score*0.9+5)60ORDERBYScore基本查询6.数据查询数据查询-排序排序4）按照新命名列排序按照新命名列排序例：SELECTst
18、uNo+.+cNoAS学号.科目号FromScoresUnionSELECTstuNo+.+cNoAS学号.科目号FromScores2ORDERBY学号.科目号DESC基本查询6.数据查询数据查询-排序排序5）按多列排序按多列排序例：SELECTstuNoAs学员编号,ScoreAs成绩FROMScoresWHEREScore60ORDERBYScore,cNo排序查询示例基本查询7.distinct-去掉重复记录行去掉重复记录行例：在成绩表scores(stuNo,cNo,score)中查询参加考试的学生考号SELECTdistinctstuNofromscoresdistinct查询结果
19、5.2.2基本函数基本函数函数名函数名描述描述举例例CHARINDEX用来寻找一个指定的字符串在另一个字符串中的起用来寻找一个指定的字符串在另一个字符串中的起始位置始位置SELECTCHARINDEX(ACCP,MyAccpCourse,1)返回：返回：4LEN返回传递给它的字符串长度返回传递给它的字符串长度SELECT LEN(SQL Server课程课程)返回：返回：12LOWER把传递给它的字符串转换为小写把传递给它的字符串转换为小写SELECT LOWER(SQL Server课程课程)返回：返回：sql server课程课程UPPER把传递给它的字符串转换为大写把传递给它的字符串转换
20、为大写SELECT UPPER(sql server课程课程)返回：返回：SQL SERVER课程课程LTRIM清除字符左边的空格清除字符左边的空格SELECT LTRIM(周智宇周智宇 )返回：周智宇返回：周智宇 （后面的空格保留）（后面的空格保留）RTRIM清除字符右边的空格清除字符右边的空格SELECT RTRIM(周智宇周智宇 )返回：返回：周智宇（前面的空格保留）周智宇（前面的空格保留）RIGHT从字符串右边返回指定数目的字符从字符串右边返回指定数目的字符SELECT RIGHT(买卖提买卖提.吐尔松吐尔松,3)返回：吐尔松返回：吐尔松REPLACE替换一个字符串中的字符替换一个字符
21、串中的字符SELECT REPLACE(莫乐可切莫乐可切.杨可杨可,可可,兰兰)返回：莫乐兰切返回：莫乐兰切.杨兰杨兰STUFF在一个字符串中，删除指定长度的字符，并在该位在一个字符串中，删除指定长度的字符，并在该位置插入一个新的字符串置插入一个新的字符串SELECT STUFF(ABCDEFG,2,3,我的音乐我的世我的音乐我的世界界)返回：返回：A我的音乐我的世界我的音乐我的世界EFG字符串函数表字符串函数表日期函数函数名函数名描述描述举例举例GETDATE取得当前的系统日期取得当前的系统日期SELECT GETDATE()返回：今天的日期返回：今天的日期DATEADD将指定的数值添加到指
22、定的日期将指定的数值添加到指定的日期部分后的日期部分后的日期SELECT DATEADD(mm,4,01/01/99)返回：以当前的日期格式返回返回：以当前的日期格式返回05/01/99DATEDIFF两个日期之间的指定日期部分的两个日期之间的指定日期部分的区别区别SELECT DATEDIFF(mm,01/01/99,05/01/99)返回：返回：4DATENAME日期中指定日期部分的字符串形日期中指定日期部分的字符串形式式SELECT DATENAME(dw,01/01/2000)返回：返回：SaturdayDATEPART日期中指定日期部分的整数形式日期中指定日期部分的整数形式SELEC
23、T DATEPART(day,01/15/2000)返回：返回：15数学函数表函数名函数名描述描述举例举例ABS取数值表达式的绝对值取数值表达式的绝对值SELECT ABS(-43)返回：返回：43CEILING返回大于或等于所给数字表达式返回大于或等于所给数字表达式的最小整数的最小整数SELECT CEILING(43.5)返回：返回：44FLOOR取小于或等于指定表达式的最大取小于或等于指定表达式的最大整数整数SELECT FLOOR(43.5)返回：返回：43POWER取数值表达式的幂值取数值表达式的幂值SELECT POWER(5,2)返回：返回：25ROUND将数值表达式四舍五入为指
24、定精将数值表达式四舍五入为指定精度度SELECT ROUND(43.543,1)返回：返回：43.5Sign对于正数返回对于正数返回+1，对于负数返回，对于负数返回-1，对于，对于0 则返回则返回0SELECT SIGN(-43)返回：返回：-1Sqrt取浮点表达式的平方根取浮点表达式的平方根SELECT SQRT(9)返回：返回：3系统函数表函数名函数名描述描述举例举例CONVERT 用来转变数据类型用来转变数据类型SELECT CONVERT(VARCHAR(5)，12345)返回：字符串返回：字符串12345CURRENT_USER 返回当前用户的名字返回当前用户的名字SELECT CU
25、RRENT_USER返回：你登录的用户名返回：你登录的用户名DATALENGTH返回用于指定表达式的字节返回用于指定表达式的字节数数SELECT DATALENGTH(中国中国A盟盟)返回：返回：7HOST_NAME 返回当前用户所登录的计算返回当前用户所登录的计算机名字机名字SELECT HOST_NAME()返回：你所登录的计算机的返回：你所登录的计算机的名字名字SYSTEM_USER 返回当前所登录的用户名称返回当前所登录的用户名称SELECT SYSTEM_USER返回：你当前所登录的用户返回：你当前所登录的用户名名USER_NAME从给定的用户从给定的用户I D返回用户名返回用户名S
26、ELECT USER_NAME(1)返回：从任意数据库中返回返回：从任意数据库中返回“dbo”5.2.3综合应用综合应用【例5.1】：某公司做了一批手机充值卡，充值密码是随机生成的，现在出现这个问题：充值密码里面的“o和0”（哦和零）“l和1”（哎和一），用户反映说看不清楚，公司决定，把存储在数据库中的密码中所有的“哦”都改成“零”，把所有的“l”都改成“1”；请编写SQL语句实现以上要求；数据库表名：Card；密码字段名：PassWord；综合应用综合应用-I分析：这是更新语句，需要使用UPDATE语句；因为牵涉到字符串的替换，需要使用到SQLServer中的函数Replace；解决：Upd
27、ateCardSetPassWord=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-1
28、5、1-18、1-31、1-108、2-1、2-2数据库表名：ArticleNo；字段名：ListNumber；综合应用综合应用-II分析：分析：这是查询语句，需要使用SELECT语句需要使用到ORDERBY进行排序，并且在ORDERBY的排序列中，也需要重新计算出排序的数字前半部分的数字，可以从先找到“-”符号的位置，然后，取其左半部分，最后再使用Convert函数将其转换为数字：Convert(int,Left(ListNumber,CharIndex(-,ListNumber)-1)后半部分的数字，可以先找到“-”符号的位置，然后把从第一个位置到该位置的全部字符替换为空格，最后再使用Co
29、nvert函数将其转换为数字：Convert(int,Stuff(ListNumber,1,Charindex(-,ListNumber),)综合应用综合应用-II解决：解决：SELECTListNumberFROMArticleNoORDERBYConvert(int,Left(ListNumber,CharIndex(-,ListNumber)-1),Convert(int,Stuff(ListNumber,1,Charindex(-,ListNumber),)思考：还有其他的办法吗？思考：还有其他的办法吗？小结小结查询将逐行筛选表中的数据，最后符合要求的记录重新组合成“记录集”，记录集的
30、结构类似于表结构；判断一行中的数据项是否为空，使用ISNULL；使用ORDERBY进行查询记录集的排序，并且可以按照多个列进行排序；在查询中，可以使用常量、表达式、运算符；在查询中使用函数，能够像在程序中那样处理查询得到的数据项。5.3复杂数据查询复杂数据查询模糊查询聚合函数分组汇总GroupingSets多表联结查询综合应用5.3.1模糊查询1.LIKE：查询时，字段中的内容并不一定与查询内容完全匹配，使用LIKE和通配符。例：SELECTstuNameAS姓名FROMstuInfoWHEREstuNameLIKEx%模糊查询2.ISNULL：把某一字段中内容为空的记录查询出来。例：SELE
31、CTstuNameAs姓名,stuAddressAS地址FROMstuInfoWHEREstuAddressISNULL模糊查询3.BETWEENAND：把某一字段中内容在特定范围内的记录查询出来。例：SELECTstuNo,ScoreFROMSCORESWHEREScoreBETWEEN60AND80模糊查询4.IN：把某一字段中内容与所列出的查询内容列表匹配的记录查询出来例：SELECTstuNameAS学员姓名,stuAddressAs地址FROMstuInfoWHEREstuAddressIN(北京,广州,上海)模糊查询5.3.2聚合函数聚合函数1.SUM：例：SELECTSUM(sc
32、ore)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聚合
33、函数聚合函数5.注意事项：注意事项：聚合函数不单独出现在条件语句中。只与返回结果值数目一致的列一起查询。聚合函数5.3.3分组汇总分组汇总基本语法：基本语法：SELECT，聚合函数FROMWHERE条件GROUPBYHAVING条件分组汇总分组汇总注：分组查询GROUPBYWHERE子句从数据源中去掉不符合其搜索条件的数据GROUPBY子句搜集数据行到各个组中，统计函数为各个组计算统计值HAVING子句去掉不符合其组搜索条件的各组数据行分组汇总分组汇总例：SELECT部门编号,COUNT(*)FROM员工信息表WHERE工资=2000GROUPBY 部门编号HAVINGCOUNT(*)15.3
34、.4GroupingSets例：有数据库Sale，表sales(productID,productName,saleAmout,saleMonth)。按照销售量升序显示表sales中数据GroupingSets使用SQL语句分组显示各产品的销售总量。SELECTproductNameas产品名,sum(saleAmout)as销售总量fromsalesGroupbyGROUPINGSETS(productName);GroupingSets效果等同于selectproductNameas产品名,sum(saleAmout)as销售总量fromsalesGroupbyproductName显示每
35、个产品的销售总量GroupingSets但是如果使用两个GROUPINGSETS分组就可以实现分别分组汇总了：SELECTproductNameas产品名,sum(saleAmout)as销售总量fromsalesGroupbyGROUPINGSETS(productName),();显示每个产品的销售总量和所有产品的销售总和显示每个产品的销售总量和所有产品的销售总和GroupingSets当然，可以给分组汇总结果起个名字“汇总”，而不必用NULL显示：selectsales.Rep=casewhenproductNameisnullThen汇总elseproductNameend,sum(s
36、aleAmout)as销售总量fromsalesGroupbyGROUPINGSETS(productName),();显示每个产品的销售总量和带名字的所有产品的销售总和显示每个产品的销售总量和带名字的所有产品的销售总和GroupingSets我们还可以进行三级甚至四级分类汇总，例如将产品按月份分类汇总显示：SELECTproductNameas产品名,sum(saleAmout)as销售总量,saleMonthas销售月份FromsalesGroupbyGROUPINGSETS(saleMonth,productName),(saleMonth),();三级甚至四级分类汇总三级甚至四级分类汇
37、总GroupingSets值得一提的是，CUBE和ROLLUP是在SQLServer2005中新增的groupby扩展，用来创建分组汇总。例如上面的实现效果也可以由以下SQL语句实现：selectproductNameas产品名,sum(saleAmout)as销售总量,saleMonthas销售月份fromsalesGroupbysaleMonth,productNamewithROLLUP使用使用ROLLUP分组汇总分组汇总5.3.5多表联结查询多表联结查询1.内联结内联结(INNERJOIN)语法：语法：1）两表连接：）两表连接：SELECTFrom左表左表INNERJOIN右表右表ON
38、左表。列左表。列=右表。列右表。列例：查询参加考试的同学的姓名、考试科目号码、考试成绩：例：查询参加考试的同学的姓名、考试科目号码、考试成绩：SELECTS.stuName,C.cNo,C.scoreFromScoresASCINNERJOINstuInfoASSONC.stuNo=S.stuNo多表联结查询多表联结查询1.内联结内联结(INNERJOIN)语法：语法：2）三表内连接：）三表内连接：例：查参加考试的学生姓名、考试科目名称、考试成绩：例：查参加考试的学生姓名、考试科目名称、考试成绩：SELECTS.stuNameAS姓名姓名,CS.cNameAS课程课程,C.ScoreAS成绩成
39、绩FROMstuInfoASSINNERJOINScoresASCON(S.stuNo=C.stuNo)INNERJOINcourseInfoASCSON(CS.cNo=C.cNo)多表联结查询多表联结查询2.普通多表查询：普通多表查询：SELECTFrom表表1，表，表2WHERE左表左表.列列=右表右表.列列例：SELECT stuInfo.stuName,scores.cNo,scores.scoreFromstuInfo,scoresWHEREstuInfo.stuNo=scores.stuNo多表连接查询示例多表联结查询多表联结查询3.外联结：外联结：1)左外联结(LEFTJOIN)
40、：例：查询所有学生考试情况SELECTS.stuName，C.cNo，C.ScoreFromstuInfoASSLEFTJOINScoresASCONC.stuNo=S.stuNo多表联结查询多表联结查询3.外联结：外联结：2)右外联结(RIGHTJOIN)：例：SELECTS.stuName，C.cNo，C.ScoreFromstuInfoASSRightJOINScoresASCONC.stuNo=S.stuNo多表联结查询多表联结查询4.UNION两个结构相同的表的连接查询，相同列合并、记录行做或运算。语法：Select,from表1UNIONSelect,from表2例：在数据库jsj
41、2019中有结构相同的两张表：scores(stuNo,cNo,score)和表scores2(stuNo,cNo,score)，求selectstuNo,cNofromsocresUNIONselectstuNo,cNofromscores2？5.3.6综合应用综合应用【例5.3】：在给发掘出来的远古时代的谷物种子做育种实验的过程中，种子每3粒一组，组内每颗种子的培育方法不同。现要求查看所有组内培育方法相同的种子的发育值的平均值。数据库表名：TABLE1字段名：A主键字段：IDKEY（标识列，种子：1；增长量：1）综合应用综合应用-I分析：可以依靠标识列的值来进行判断和选取，但是因为操作过程
42、中，数据行可能存在增加、修改和删除，因此标识列的数据值未必完全有序，也就不“完全可靠”。例如标识列值为3，但并不一定是第三行，因为如果第二行被删除了，它就是第二行。根据我们前面使用过的SELECTINTO，可以创建一张新表，顺便创建新的标识列，再在新的标识列上执行除3取余判断。判断依据：标识列值%3等于0、标识列值%3等于1和标识列值%3等于2。综合应用综合应用-ISELECT A,IDENTITY(int,1,1)ASIDINTOTABLE2FROMTABLE1SELECT AVG(A)AS1号种子发育平均值FROMTABLE2WHEREID%3=1SELECT AVG(A)AS2号种子发育
43、平均值FROMTABLE2WHEREID%3=2SELECT AVG(A)AS3号种子发育平均值FROMTABLE2WHEREID%3=0综合应用综合应用-II【例5.4】：有学生基本信息表stuInfo（stuNo,stuName）和学生成绩表Scores(stuNo,cNo,score)。现在要求建立新表stuAllInfo(stuNo,stuName,cNo,score)，存储所有学生的考试信息。综合应用综合应用-II分析：这是数据插入的操作，因此要使用INSERT语句来进行。参加考试的同学的考试信息在Scores表里，所以可以使用INSERTINTOSELECT结构。但是还要插入stu
44、Name数据项。所以要用多表连接查询。但是还有同学可能没有参加任何一科的考试，所以根本不在scores成绩表中。综合应用综合应用-II等值的多表连接和不等值的多表连接都不能找到这些同学。在前面的联结查询中，使用INNERJOINON可以找出所有参加考试的学生信息项，编写以下T-SQL：SELECTstuInfo.stuNo,stuName,cNo,scorefromstuInfoINNERJOINscoresONstuInfo.stuNo=scores.stuNo但是如何把未参加任何一科考试的同学也显示其中？如下可以吗？SELECTstuInfo.stuNo,stuName,cNo,score
45、fromstuInfoINNERJOINscoresONstuInfo.stuNoscores.stuNo以上把“=”简单地改为“”，不仅不能找出未参加考试的同学，而且所找到的项很多，也没有意义，因此，这种方法不可行。这也说明一点：内联结查询的基础是on后面的等值比较，非等值比较就不是内联查询了。综合应用综合应用-II考虑我们在学习过的左外连接查询，能够查询出左表中存在而相关表不存在的数据项。所以可以使用如下语句查询出所有考生。SELECTstuInfo.stuNo,stuName,cNo,scorefromstuInfoLeftOUTJOINscoresONstuInfo.stuNo=sco
46、res.stuNo最后，使用插入同时建立新表语句子查询插入新建表stuAllInfo综合应用综合应用-II解决：SELECTstuInfo.stuNo,stuName,cNo,scoreINTOstuAllInfofromstuInfoLeftJOINscoresONstuInfo.stuNo=scores.stuNoSELECT*FROMstuAllInfo在查询分析器中选择工具-选项-结果-默认结果目标修改为“显示为文本”。小结小结使用LIKE、BETWEEN、IN关键字，能够进行模糊查询条件不明确的查询。聚合函数能够对列生成一个单一的值，对于分析和统计通常非常有用。分组查询是针对表中不同
47、的组，分类统计和输出，GROUPBY子句通常会结合聚合函数一起来使用。HAVING子句能够在分组的基础上，再次进行筛选。多个表之间通常使用联结查询。最常见的联结查询是内联结（INNERJOIN），通常会在相关表之间提取引用列的数据项。5.4使用使用SQL语句设计和管理数据库语句设计和管理数据库创建数据库删除数据库建表删除表为表加约束删除约束安全管理5.4.1创建数据库创建数据库语法：CREATEDATABASE数据库名ONPRIMARY(，n)LOGON(，n)注：PRIMARY：主文件组，可选参数，默认；表示可选参数。【例5.5】创建数据库studentDB，保存在D：project目录，数
48、据文件增长率：15，初始大小5MB，日志文件初始大小2MB，文件增长率按1MB自动增长。CREATEDATABASEstudentDBONPRIMARY-默认就属于PRIMARY主文件组，可省略(NAME=studentDB,-主数据文件的逻辑名FILENAME=D:projectstudentDB.mdf,-主数据文件的物理名SIZE=5mb,-主数据文件初始大小MAXSIZE=100mb,-主数据文件增长的最大值FILEGROWTH=15%-主数据文件的增长率)LOGON(NAME=studentDB_log,FILENAME=D:projectstudentDB_log.ldf,SIZE
49、=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
50、=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.databa
个人主页
