数据库应用技术第5章.ppt

第5章数据的基本操作,5.1数据的添加、修改和删除,5.1.1数据的添加语法格式：INSERTINTOtable_name(column_list)VALUES(data_values),1.最简单的INSERT语句例5-1在student的数据库中的“专业”表中添加一行记录。,USEstudentGOINSERT专业（专业代码，专业名称，系部代码）VALUES（0103,电子商务,01）GO,2.省略清单的INSERT语句例5-2在student的数据库中的“班级”表中添加2004级电子商务班。USEstudentGOINSERT班级VALUES（20041512,2004电子商务,0103,01,null）,3.省略VALUES清单的INSERT语句语法格式：INSERTINTOtable_name(column_name,n)SELECTcolumn_name,nFROMtable_nameWHEREsearch_conditions,例5-3创建“课程”表的一个副本“课程1”表，将“课程”表的全部数据添加到“课程1”表中。USEstudentGOCREATEtable课程1（课程号char(4)notnull,课程名char(20)notnull,学分smallintnull）GOINSERTINTO课程1（课程号,课程名,学分）SELECT（课程号,课程名,学分）FROM课程,5.1.2数据的修改,UPDATEtable_nameSETcolumn_name=expression|DEFAULT|NULL,.nWHEREsearch_condition,例5-4将“教学计划”表中专业代码为“0101”的“开课学期”的值改为第2学期。UsestudentGoUpdate教学计划Set开课学期=2Where专业代码=0101go,例5-5将“课程注册”表中所有记录的成绩改为（“注册号”-10000415）表达式的值，学分为3分。UsestudentGoUpdate课程注册Set成绩=（注册号-1000415），学分=3Go,5.1.3数据的删除,简单的语法格式：DELETEtable_nameFROM,.nWHERE,例5-6删除“课程注册”表中的所有记录。UsestudentGodelete课程注册go,例5-7删除“教师”表中没有姓名的记录。UsestudentGodelete教师where姓名isnullgo,例5-8删除“课程注册”表中姓名为“张斌”的课程号为“0001”的选课记录记录。UsestudentGodelete课程注册where学生.姓名=张斌AND课程注册.课程号=0001go,2.TRUNCATETABLE清空表格其语句格式为：TRUNCATETABLEtable_name例5-9用TRUNCATETABLE语句清空“课程注册”表。,代码如下：UsestudentGoTRUNCATETABLE课程注册Go,5.2简单查询5.2.1完整的SELECT语句的基本语法格式SELECT语句的语法形式如下：SELECTselect_listINTOnew_tableFROMtable_sourceWHEREsearch_conditionGROUPBYgroup_by_expressionHAVINGsearch_conditionORDERBYorder_expressionASC|DESC,SELECT子句用于指定所选择的要查询的特定表中的列，它可以是星号（*）、表达式、列表、变量等。INTO子句用于指定所要生成的新表的名称。FROM子句用于指定要查询的表或者视图，最多可以指定16个表或者视图，用逗号相互隔开。WHERE子句用来限定查询的范围和条件。GROUPBY子句是分组查询子句。HAVING子句用于指定分组子句的条件。GROUPBY子句、HAVING子句和集合函数一起可以实现对每个组生成一行和一个汇总值。ORDERBY子句可以根据一个列或者多个列来排序查询结果，在该子句中，既可以使用列名，也可以使用相对列号。ASC表示升序排列，DESC表示降序排列。,5.2.2选择表中的若干列,1.输出表中的所有列两种方法：一是将所有的字段名称在SELECT关键字后列出来；另外一种就是在SELECT语句后使用一个“*”。例5-10查询“学生”表中全体学生的纪录。usestudentgoselect*from学生go,2.输出表中的部分列将要现实的字段名在SELECT关键字后依次列出来，列名于列名之间用英文的逗号隔开。例5-11查询全体教师的教师编号、姓名和职称信息,usestudentgoselect教师编号,姓名,职称from教师go,练习1从pubs数据库的publishers（出版商）表中检索所有的pub_id（出版商代号）、pub_name（出版商的名字）以及country（国籍）。,usepubsgoselectpub_id,pub_name,countryfrompublishersgo,操作查询的列名,例5-12查询教师表中的全体教师的姓名及年龄。,usestudentgoselect教师编号,year(getdate()-year(出生日期)as年龄from教师go,usepubsgoselecttitle_id,type,price,price-price*0.3fromtitlesgo,补充例1从pubs数据库的titles表中检索所有的title_id（图书代号）、type（类型）、price（原价）以及现价（每本书的销售价格降低30%）。,usepubsgoselecttitle_id图书代号,type类型,price原价,price-price*0.3现价fromtitlesgo,usepubsgoselecttitle_idas图书代号,typeas类型,priceas原价,price-price*0.3as现价fromtitlesgo,usepubsgoselect图书代号=title_id,类型=type,原价=price,现价=price-price*0.3fromtitlesgo,5.2.3选择表中的若干纪录1.消除取之重复的行使用DISTINCT关键字例5-13在student数据库的课程注册表中查询选修了课程的学号。,usestudentgoselect学号from课程注册go,usestudentgoselectdistinct学号from课程注册go,从pubs数据库的publishers表中搜索出所有国家的名字（country）。UsepubsGoSelectcountryFrompublishersGo从pubs数据库的publishers表中搜索出所有国家的名字（要求不能重复）。UsepubsGoSelectdistinctcountryFrompublishersgo,2.限制返回行数使用TOP关键字，语法格式：SELECTTOPn|TOPnPERCENT列名1,列名2,列名nFROM表名,例5-14查询“课程注册”表中的前3条记录的信息。usestudentgoSelecttop3*from课程注册go,练习从northwind数据库的customers表中返回前面10行纪录。UsenorthwindGoSelecttop10*FromcustomersGo从northwind数据库的customers表中返回前10%的数据。UsenorthwindGoSelecttop10percent*FromcustomersGo,3.带条件的查询使用WHERE子句可以从表格的数据中过滤出符合条件的行。（1）使用算数表达式使用算术表达式的一般形式为：表达式算数操作符表达式WHERE子句中允许出现的算术运算符有：=（等于）、（大于）、=（对于等于）、（不等于）、!（不大于）、!=50go,补例：查询pubs库的titles表中，价格打了8折后仍大于12美元的书号、种类以及原价。UsepubsGoSelecttitle_idas书号,typeas种类,priceas原价FromtitlesWnereprice-price*0.212go,(2)确定范围（使用BETWEEN关键字）列表达式NOTBETWEEN起始值AND终止值例5-16查询出生日期在1970年至1982年之间的学生姓名、学好和出生日期。,usestudentgoselect姓名,学号,出生日期from学生where出生日期between1970-01-01and1982-12-31go,补例：查询价格在15和20美元之间的书的书号、种类和价格。UsepubsGoSelecttitle_idas书号,typeas种类,priceas原价FromtitlesWherepricebetween$15and$20go,4IN关键字列表达式NOTIN(列值1,列值2,列值3)例5-15查询计算机系、经济管理系的班机名称与班机代码。usestudentgoselect班级代码,班级名称from班级where系部代码in(01,02)go,补充：在数据库pubs中authors表查询所有居住在KS、CA、MI、IN州的作家。UsepubsGoSelectau_id,au_lname,au_fnameFromauthorsWherestatein(CA,KS,MI,IN)GoWherestate=CAorstate=KSorstate=MIorstate=IN,（4）字符匹配（模糊查询）语法格式：NOTLIKEESCAPE有效SQLServer通配符：%：可匹配任意类型和长度的字符串。_（下划线）：可匹配任何单个字符。：指定范围或集合中的任何单个字符。：不属于指定范围或集合的任何单个字符,例5-18查询“学生”表中姓“周”的同学的信息。usestudentgoselect*from学生where姓名like周%go,5.2.4对查询的结果排序使用ORDERBY子句的语法格式：ORDERBY列名ASC|DESC，n例5-22查询选修了“0001”课程的同学的学号，并按成绩的降序排列。usestudentgoselect学号,成绩from课程注册where课程号=0001orderby成绩descgo,例5-23查询全体学生信息，查询结果按所在班级代码降序排列，同一个班的按升序排列。usestudentgoselect*from学生orderby班级代码desc,学号ascgo,5.2.5对数据进行统计1.常用统计函数,例5-24查询教师表中教师总数。usestudentgoselectcount(*)as教师总数from教师Go例5-25查询“课程注册”表中学生的成绩平均分。usestudentgoselectavg(成绩)as平均分from课程注册go,2.对结果进行分组语法格式：GROUPBY列名HAVING筛选条件表达式“BY列名”是按列名指定的字段进行分组，将该字段值相同的记录组成一组，对每一组记录进行汇总计算并生成一条记录。“HAVING筛选条件表达式”表示对生成的组筛选后在对满足条件的组进行统计。SELECT子句的列名必须是GROUPBY子句已有的列名或是计算列。,例5-26查询“课程注册”表中课程选课人数4人以上的各个课程号和相应的选课人数。usestudentgoselect课程号,count(*)as选课人数from课程注册groupby课程号havingcount(*)=4go,补充：查询每位同学的课程门数、总成绩、平均成绩。Select学号，count（课程编号）as课程门数，sum（成绩）as总成绩，avg（成绩）as平均成绩From成绩表Groupby学号go,从学生基本信息表中统计各民族学生的人数。Select族别，count（族别）as学生人数From学生基本信息表Groupby族别go从学生基本信息表中统计汉族学生的人数。Select族别，count（族别）as学生人数From学生基本信息表Groupby族别Having族别=汉族go,3.使用COMPUTE子句Compute子句对查询结果集中的所有记录进行汇总统计，必须使用orderby子句对computeby中by指定的列进行排序，否则将出现错误信息。例5-27查询所有学生所有成绩的总和。usestudentgoselect*from课程注册orderby学号computesum(成绩)go,例5-28查询每个学生所有课程的成绩求和，并显示详细记录。usestudentgoselect*from课程注册orderby学号computesum(成绩)by学号go,5.2.6用查询结果生成新表通过select语句的into实现例5-29创建课程注册表的一个副本。UsestudentGoSelect*into课程注册副本From课程注册GoSelect*From课程注册副本go,例5-30创建一个空的“教师”表的副本。usestudentgoselect*into教师副本from教师where1=2,5-31查询“课程注册”表中0102专业的学生学号及课程的成绩大于70分小于79分的学生的学号，且按成绩降序排列记录。usestudentgoselect*from课程注册where专业代码=0102unionselect*from课程注册where成绩=70and成绩79orderby成绩descgo,5.3连接查询交叉连接查询,5.4子查询子查询是指一条select语句作为另一条select语句的一部分，外层的select语句称为外部查询，内层的select语句称为内部查询（或子查询）。子查询分为嵌套子查询和相关子查询。,5.4.1带有in运算符的子查询例5-41使用“学生”表、“课程”表和“课程注册”表，查询选修了课程名为“高等数学”或“计算机基础”的学生的学号和姓名。UsestudentGoSelect学号,姓名From学生Where学号in(select学号From课程注册Where课程号in(select课程号From课程Where课程名=高等数学Or课程名=计算机基础),UsestudentGoSelect教师编号,姓名,性别,学历,职务,职称From教师Where系部代码=(select系部代码From教师Where姓名=王刚),1.查询