版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
创建表:CREATETABLE[database_name.[owner].|owner.]table_name<column_definition>::={column_namedata_type[NULL|notnull]}[on<filegroup>][textimage_onON<filegroup>]
[IDENTITY[(seed,increment)][...n]
修改表
ALTERTABLEtable_name
{[ALTERCOLUMNcolumn_name{new_data_type
}]|ADDcolumn_namedata_type|DROPCOLUMNcolumn_name[,...n]}删除表
DROPTABLEtable_name
插入数据INSERT[INTO]{table_name|view_name}[column_list)]{VALUES(values_list)}修改数据
UPDATE
{table_name|view_name}SET
{column_list|variable_list}=expression[From<table_source>[,…n][WHEREsearch_conditions]
删除数据DELETEFROM
table_name
WHERE
search_conditions功能上机实验注意事项把数据库内容保存到其他存储介质时,先在SQLServer中分离数据库;下次再使用时,先在SQLServer中附加该数据库;在表中输入数据时,一行一行输入。实验二实验三第六章数据查询Chapter6Query第6章
数据查询6.1基本查询6.1.1选择表中的若干列6.1.2带条件查询6.1.3查询结果处理6.1.4分组统计与筛选6.1.5使用COMPUTE子句汇总6.2嵌套查询
6.2.1单值嵌套查询
6.2.2多值嵌套查询6.3连接查询6.3.1内连接查询6.3.2外连接查询6.3.3交叉连接查询6.4小结查询语法SELECT[ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]…FROM<表名或视图名>[,<表名或视图名>]…[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]];-SELECT子句:指定要显示的属性列-
FROM子句:指定查询对象(基本表或视图)-
WHERE子句:指定查询条件-GROUPBY子句:对查询结果按指定列的值分组,该属性列值相等的元组为一个组。通常会在每组中使用集合函数。- HAVING短语:筛选出只有满足指定条件的组-
ORDERBY子句:对查询结果按指定列值的升序或降序排序6.1基本查询格式语法格式:SELECT[ALL|DISTINCT][TOPn[PERCENT]]select_listFROMtable_nameselect_list:所要查询的选项的集合,多个选项之间用逗号分开。算术表达式、字符串常量、函数、列别名等。table_name:要查询的表。ALL:表示输出所有记录,包括重复记录。DISTINCT:表示输出记录中不包括重复记录TOPn[PERCENT]:指定返回查询结果的前n行数据(前n%行数据)查询选项可以使用聚集函数选择若干列举例(参考课本112页表字段)例6-1-1查询员工姓名及住址SELECTemployee_name,addressFROMemployee例6-1查询员工所有信息SELECT*FROMemployee例6-1-2查询员工姓名及年龄SELECTemployee_name,YEAR(GETDATE())-YEAR(birth_date)FROMemployee例6-1-3查询员工姓名及年龄SELECTemployee_name,‘年龄’,YEAR(GETDATE())-YEAR(birth_date)FROMemployee基本查询举例(续)例6-1-4查询员工姓名及年龄,并为每列起适当的标题SELECTemployee_nameAS姓名,YEAR(GETDATE())-YEAR(birth_date)AS年龄FROMemployee例6-1-5查询前两个员工姓名及住址SELECTTOP2employee_name,addressFROMemployee例6-1-6查询前20%员工信息SELECTTOP20PERCENT*FROMemployee基本查询举例(续2)例6-1-7查询员工的姓氏SELECTALLLEFT(employee_name,1),FROMemployee例6-1-8查询员工的姓氏(重复的只列一次)SELECTDISTINCTLEFT(employee_name,1)FROMemployee使用集函数(AggregateFunctions)7类主要集函数:(1)计数COUNT([DISTINCT|ALL]*)COUNT([DISTINCT|ALL]<列名>)(2)计算总和SUM([DISTINCT|ALL]<列名>)(3)计算平均值AVG([DISTINCT|ALL]<列名>)(4)求最大值MAX([DISTINCT|ALL]<列名>)(5)求最小值MIN([DISTINCT|ALL]<列名>)(6)求标准差STDEV([DISTINCT|ALL]<列名>)(7)求方差VAR([DISTINCT|ALL]<列名>)DISTINCT:在计算时要取消指定列中的重复值ALL:不取消重复值ALL为缺省值使用集函数(续1)SnoCnoGrade20050011922005001285200500138820050022902005002380SC注:用DISTINCT以避免重复计算学生人数查询结果:
count_Sno5查询结果:
count_Sno2[例]查询学生总人数。
SELECTCOUNT(*) FROMStudent;[例]查询选修了课程的学生人数。SELECTCOUNT(Sno)FROMSC;
SELECTCOUNT(DistinctSno)FROMSC;
基本查询举例(续)例6-3对employee表,分别查询公司的员工总数和公司员工的平均收入。
SELECTCOUNT(*)AS总数
FROMemployee
SELECTAVG(wages)AS平均收入FROMemployee等价于SELECTCOUNT(*)AS总数,AVG(wages)AS平均收入FROMemployee查询语法SELECT[ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]…FROM<表名或视图名>[,<表名或视图名>]…[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]];-SELECT子句:指定要显示的属性列-
FROM子句:指定查询对象(基本表或视图)-
WHERE子句:指定查询条件-GROUPBY子句:对查询结果按指定列的值分组,该属性列值相等的元组为一个组。通常会在每组中使用集合函数。- HAVING短语:筛选出只有满足指定条件的组-
ORDERBY子句:对查询结果按指定列值的升序或降序排序带条件查询语法格式:WHEREsearch_conditionAND,OR多重条件ISNULL,ISNOTNULL空值LIKE,NOTLIKE字符匹配IN,NOTIN确定集合BETWEENAND,NOTBETWEENAND确定范围=,<,>,<=,>=,>,!=,!>,!<;NOT+上述比较符比较谓词查询条件带条件查询(续1)例6-4
对employee表,列出月工资在2000以上的员工记录。SELECT*FROMemployeeWHEREwages>2000例6-5
对employee表,求出男员工的平均工资。
SELECTAVG(wages)as平均工资
FROMemployeeWHEREsex='男‘带条件查询(续2)使用谓词IN<值表>,NOTIN<值表><值表>:用逗号分隔的一组取值例6-6-1对employee表,列出D001和D002部门的员工名单。SELECTdepartment_id,employee_nameFROMemployeeWHEREdepartment_idIN(‘D001',‘D002')
等价于
WHERE(department_id=‘D001')OR(department_id=‘D002')带条件查询(续3)确定范围:BETWEEN…AND…NOTBETWEEN…AND…例6-7对employee表,列出月工资在2000到3000之间的员工名单。
SELECT*FROMemployeeWHEREwagesBETWEEN2000AND3000
等价于
WHEREwages>=2000ANDwages<=3000带条件查询(续4)[NOT]LIKE‘<匹配串>’<匹配串>:指定匹配模板。匹配模板:固定字符串或含通配符的字符串。
当匹配模板为固定字符串时,可以用=运算符取代LIKE谓词,用!=或<>运算符取代NOTLIKE谓词。%(百分号):代表任意长度(长度可以为0)的字符串
_(下横线):代表任意单个字符例6-8对employee表,列出所有的姓“张”的员工名单。
SELECT*FROMemployeeWHEREemployee_nameLIKE'张%'
语句中的WHERE子句还有等价的形式:
WHERELEFT(employee_name,1)=‘张‘带条件查询(续4)例6-8-1对employee表,列出所有的第二个字为’文’的员工名单。SELECT*FROMemployeeWHEREemployee_nameLIKE‘_文%'涉及空值的查询使用谓词ISNULL
或ISNOTNULL“ISNULL”不能用“=NULL”代替例6-9对employee表,列出所有工资为空值的员工编号和姓名。SELECTemployee_id,employee_nameFROMemployeeWHEREwagesISNULL查询结果处理1.排序输出(ORDERBY)语法格式:ORDERBYorder_by_expression1[ASC|DESC][,order_by_expression2[ASC|DESC]][,…]]例6-10-1对employee表,按性别顺序列出员工的编号、姓名、性别、部门编号及工资。SELECTemployee_id,employee_name,sex,department_id,wagesFROMemployee
ORDERBYsex查询结果处理(续1)例6-10对employee表,按性别顺序列出员工的编号、姓名、性别、部门编号及工资,性别相同的再先按部门后按工资由高到低排序。SELECTemployee_id,employee_name,sex,department_id,wagesFROMemployee
ORDERBYsex,department_id,wagesDESC查询结果处理(续2)2.重定向输出(INTO)语法格式:INTOnew_table
例6-11-1
对员工表employee,查询出“D001”部门的所有员工的信息,并将结果存入testtable表中。SELECT*INTOtesttableFROMemployeeWHEREdepartment_id=‘D001'查询结果处理(续3)3.输出合并(UNION)语法格式:
[UNION[ALL]<SELECT语句>]合并的规则是:
(1)不能合并子查询的结果。
(2)两个SELECT语句必须输出同样的列数。
(3)两个表各相应列的数据类型必须相同,数字和字符不能合并。
(4)仅最后一个SELECT语句中可以用ORDERBY子句,且排序选项必须依据第一个SELECT列表中的列。查询结果处理(续4)
例6-12对employee表,列出部门编号为“D001”或“D002”的所有员工姓名。
SELECTemployee_name,department_idFROMemployeeWHEREdepartment_id='D001'UNIONSELECTemployee_name,department_idFROMemployeeWHEREdepartment_id='D002'分组统计4.分组统计(GROUPBY)与筛选(HAVING)GROUPBYgroup_by_expression1[,group_by_expression2][,…]作用:细化集函数的作用对象
未对查询结果分组,集函数将作用于整个查询结果对查询结果分组后,集函数将分别作用于每个组分组方法:按指定的一列或多列值分组,值相等的为一组使用GROUPBY子句后,SELECT子句的列名列表中只能出现分组属性和集函数分组统计(续1)例6-13对employee表,分别统计男女员工人数。
SELECTsex,COUNT(sex)as人数
FROMemployeeGROUPBYsex例6-14对employee表,分别统计各部门男女员工的人数。
SELECTdepartment_id,sex,COUNT(*)as人数
FROMemployeeGROUPBYdepartment_id,sexHaving子句只有满足HAVING短语指定条件的组才输出HAVING短语与WHERE子句的区别:作用对象不同WHERE子句作用于基表或视图,从中选择满足条件的元组。HAVING短语作用于组,从中选择满足条件的组。例6-15对employee表,列出部门平均工资大于2000的部门编号。SELECTdepartment_id,AVG(wages)AS平均工资FROMemployeeGROUPBYdepartment_idHAVINGAVG(wages)>=2000GROUPBY使用注意事项使用了分组的查询语句,其SELECT子句中只能出现分组属性和集函数,而不能有在GROUPBY没有出现的属性。 原因在于SELECT子句是对分组后的结果集进行输出,参与分组的属性在同一组中取相同值,而不参与分组的属性(不在GROUPBY子句中出现的属性)则可能取不同的值,分组输出通常是对组的整体描述,而不是组内的详细记录,不参与分组的属性取值不同,故无法输出。SELECTsex,department_id,COUNT(sex)FROMemployeeGROUPBYsex汇总查询5.使用COMPUTE和COMPUTEBY子句汇总语法格式(先排序后汇总):COMPUTErow_aggregate(column_name)[,row_aggregate(column_name)...][BYcolumn_name[,column_name...]]使用COMPUTE子句在查询的结果集中生成汇总行的同时,还可以生成明细行。可以计算子组的汇总值,也可以计算整个结果集的汇总值。使用COMPUTEBY子句可以对结果集数据进行分组统计,即计算分组的汇总值。COMPUTE子句生成合计作为附加的汇总列出现在结果集的最后。汇总查询(续1)例6-16对employee表中部门编号为“D001”的员工工资,按照其部门编号生成汇总行和明细行。
SELECTdepartment_id,wagesFROMemployeeWHEREdepartment_id='D001‘ORDERBYdepartment_idCOMPUTEsum(wages)例6-17对employee表中部门编号为“D001”或“D002”的员工工资,按照其部门编号生成分组汇总行和明细行。SELECTdepartment_id,wagesFROMemployeeWHEREdepartment_id='D001'ORdepartment_id='D002'ORDERBYdepartment_idCOMPUTEsum(wages)BYdepartment_id汇总查询注意问题DISTINCT关键字不能与集合函数一起使用。COMPUTE子句中指定的列必须是SELECT子句中已有的。因为COMPUTE子句产生非标准行,所以COMPUTE子句不能与SELECTINTO子句一起使用。COMPUTEBY必须与ORDERBY子句一起使用,且COMPUTEBY中指定的列必须与ORDERBY子句中指定的列相同,或者为其子集,而且两者之间从左到右的顺序也必须相同。在COMPUTE子句中,不能使用ntext、text或image数据类型。SnoSnameSsexSageSdept2005001李勇男20CS2005002刘晨女19IS2005003王敏女18MA2005004张立男19ISSnoCnoGrade200500119220050012852005001388200500229020050052682005002380Student表SC表CnoCnameCpnoCcredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL语言64COURSE表补充案例(1)(实验数据)[例1]查询全体学生的姓名及其所在的系SELECT
结果:
snameSdept
----------------2005001CS2005002
IS
2005003MA2005004ISSname,SdeptFROMStudentIS18男张立2005004MA18女王敏2005003IS19女刘晨2005002CS17男李勇2005001sdeptagesexsnamesnoStudent补充案例(2)[例2]
查询全体学生的详细记录。SELECTSno,Sname,Ssex,Sage,SdeptFROMStudent;
或SELECT* //表示将表中的列全部按序输出FROMStudent;[例3]查全体学生的姓名及其出生年份。SELECTSname,2012-SageFROMStudent;
输出结果:
Sname2011-Sage----------------------
李勇1985刘晨1986王敏1987张立1986
补充案例(3)[例4]查询全体学生的姓名、出生年份和所在系,要求用小写字母表示所在系名。
SELECTSname,'YearofBirth:',2006-Sage,ISLOWER(Sdept)FROMStudent;输出结果:
Sname'YearofBirth:'2006-SageISLOWER(Sdept)-------------------------------------------------
李勇YearofBirth:1976 cs
刘晨YearofBirth:1977 is
王敏YearofBirth:1978 ma
张立YearofBirth:1977 is补充案例(4)[例5]查询全体学生的姓名、出生年份和所在系,并将输出字段依次更名为:NAME、BIRTH、BIRTHDAY、DEPARTMENT。SELECTSnameNAME,'YearofBirth:’BIRTH,2007-SageBIRTHDAY,ISLOWER(Sdept)DEPARTMENTFROMStudent;输出结果:
NAMEBIRTHBIRTHDAYDEPARTMENT----------------------------------------------------------
李勇YearofBirth:1986 cs
刘晨YearofBirth:1987 is
王敏YearofBirth:1988 ma
张立YearofBirth:1987 is补充案例(5)[例6]查询选修了课程的学生学号。(1)SELECTSnoFROMSC;或(默认ALL)SELECTALLSnoFROMSC;结果:Sno-------20050012005001200500120050022005002
(2)SELECTDISTINCTSnoFROMSC;结果:
Sno-------20050012005002
补充案例(6)[例7]查询计算机系全体学生的信息。 SELECT*FROMStudent WHERESdept=‘CS’;
[例8]查询所有年龄在20岁以下的学生姓名及其年龄。
SELECTSname,Sage FROMStudentWHERESage<20;或
SELECTSname,Sage FROMStudent WHERENOTSage>=20;补充案例(7)[例9]
查询年龄不在20~23岁之间的学生姓名、系别和年龄。SELECTSname,Sdept,SageFROMStudentWHERESageNOTBETWEEN20AND23;[例10]
查询不是信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别。
SELECTSname,SsexFROMStudentWHERESdeptNOTIN('IS','MA','CS');补充案例(8)[例11]
查询姓"欧阳"且全名为三个汉字的学生的姓名。
SELECTSnameFROMStudentWHERESnameLIKE‘欧阳__’;[例12]
查询所有不姓刘的学生姓名。
SELECTSnameFROMStudentWHERESnameNOTLIKE‘刘%’;补充案例(9)
[例13]
某些学生选修课程后没有参加考试,所以有选课记录,但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。 SELECTSno,Cno FROMSC WHEREGradeISNULL;[例14]
查询计算机系年龄在20岁以下的学生姓名。 SELECTSname FROMStudent WHERESdept='CS'ANDSage<20;补充案例(10)[例15]
查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列。
查询结果:
SnoSnameSsexSageSdept--------------------------------2005001李勇男17CS2005002刘晨女19IS2005004张立男18IS2005003王敏女18MA
查询结果:
SnoSnameSsexSageSdept--------------------------------2005001李勇男17CS2005003王敏女18MA2005004张立男18IS2005002刘晨女19IS
SELECT*FROMStudent
ORDERBYSdept,SageDESCSELECT*FROMStudent
ORDERBYSage,SdeptDESC基本查询回顾SELECT[ALL|DISTINCT][*|<columns>|<arithmeticexpressions>|<AggregateFunctions>][[AS]<alias>],…FROM<table>WHERE<conditions>GROUPBY<columnN>
HAVING<inner-groupconfitions>ORDERBY<columnN>[ASC|DESC]特别注意:集函数只能用于SELECT子句和HAVING短语之中,而绝对不能出现在WHERE子句中。WHERE子句用于对查询的表限定,而HAVING短语用于对结果集中的各分组进行限定。SnoSnameSsexSageSdept2005001李勇男20CS2005002刘晨女19IS2005003王敏女18MA2005004张立男19ISSnoCnoGrade200500119220050012852005001388200500229020050052682005002380Student表SC表CnoCnameCpnoCcredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL语言64COURSE表补充案例(11)[例16]
求各个课程号及相应的选课人数。
SELECTCno,COUNT(Sno) FROMSC GROUPBYCno;查询结果:
CnoCOUNT(Sno)112232[例17]
查询选修了3门以上课程的学生学号。
SELECTSno FROMSCGROUPBYSnoHAVINGCOUNT(*)>3;补充案例(12)[例18]
查询有3门以上课程是90分以上的学生的学号及(90分以上的)课程数。 SELECTSno,COUNT(*)FROMSCWHEREGrade>90GROUPBYSno HAVINGCOUNT(*)>=3;嵌套查询一个SELECT-FROM-WHERE语句称为一个查询块将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询SELECTemployee_name//
外层查询/父查询
FROMemployeeWHEREdepartment_idIN(SELECTdepartment_id//
内层查询/子查询
FROMdepartmentWHEREdepartment_name=‘市场部');子查询的限制--不能使用ORDERBY子句层层嵌套方式反映了SQL语言的结构化有些嵌套查询可以用连接运算替代6.2.1单值嵌套查询子查询的返回结果是一个值的嵌套查询称为单值嵌套查询。比较运算符:(>,<,=,>=,<=,!=或<>)。
例6-18对Sales数据库,列出“市场部”的所有员工的编号。①确定“市场部”的部门编号
(SELECTdepartment_idFROMdepartmentWHEREdepartment_name='市场部')
结果为department_idD002②查找所有在D002部门的员工编号。
SELECTemployee_idFROMemployeeWHEREdepartment_id=‘D002’结果为:employee_idE0036.2.1单值嵌套查询(续)SELECTemployee_idFROMemployeeWHEREdepartment_id=‘D002’(SELECTdepartment_idFROMdepartmentWHEREdepartment_name='市场部')上述分步查询可改写成如下嵌套查询单值嵌套查询举例[例6-18-1]查询工资比D001部门人员最高工资还要高的职工名单SELECTemployee_nameFROMEmployeeWHEREwages>(SELECTMAX(wages)FROMemployeeWHEREdeparment_id=‘D001’)①首先在employee关系中找出D001部门最高工资;②然后在employee关系中取出employee_name。查询执行过程:思考:例6-18-2查询工资比市场部工资最高的人还要高的职工名单单值嵌套查询举例(2)[例6-18-2]查询工资比市场部人员最高工资还要高的职工名单SELECTemployee_nameFROMEmployeeWHEREwages>(SELECTMAX(wages)FROMemployeeWHEREdeparment_id=(SELECTdepartment_idFROMdepartmentWHEREdepartment_name=‘市场部’);①首先在department关系中找出“市场部”的员工号;②然后在employee关系中找出市场部最高工资;③最后在employee关系中取出employee_name。查询执行过程:6.2.2多值嵌套查询子查询的返回结果是一列值的嵌套查询称为多值嵌套查询。IN运算符ALL运算符ANY运算符>ANY 大于子查询结果中的某个值>ALL 大于子查询结果中的所有值<ANY 小于子查询结果中的某个值<ALL 小于子查询结果中的所有值>=ANY 大于等于子查询结果中的某个值>=ALL 大于等于子查询结果中的所有值<=ANY 小于等于子查询结果中的某个值<=ALL 小于等于子查询结果中的所有值=ANY 等于子查询结果中的某个值相当于IN=ALL 等于子查询结果中的所有值(通常没有实际意义)!=(或<>)ANY 不等于子查询结果中的某个值(相当于NOTIN)!=(或<>)ALL 不等于子查询结果中的任何一个值IN运算符的用法
例6-21
对Sales数据库,列出部门为“市场部”或“销售部”的所有员工的编号。
SELECTemployee_idFROMemployeeWHEREdepartment_idIN
(SELECTdepartment_idFROMdepartmentWHEREdepartment_name='市场部'ORdepartment_name='销售部')
ANY运算符的用法
例6-19
对Sales数据库,列出D001号部门中工资比D002号部门的员工最低工资高的员工编号和工资。
SELECTemployee_id,wagesFROMemployeeWHEREdepartment_id='D001'ANDwages>ANY(SELECTwagesFROMemployeeWHEREdepartment_id='D002')ALL运算符的用法
例6-20
对Sales数据库,列出部门编号为“D001”的员工,这些员工的工资比部门为“D002”的员工的最高工资还要高的员工的编号和工资。
SELECTemployee_id,wagesFROMemployeeWHEREdepartment_id=‘D001’ANDwages>ALL
(SELECTwagesFROMemployeeWHEREdepartment_id='D002')连接查询内连接(Innerjoin)等值连接自然连接非等值连接自身连接外连接(Outerjoin)左外连接右外连接全外连接交叉连接(Crossjoin)连接查询连接查询--将两个(以上)表连接进行查询同时涉及多个表的查询一般格式:WHERE[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>FROM表名1<连接类型>表名2ON[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>连接字段连接谓词中的列名称为连接字段连接条件中的各连接字段类型必须是可比的,但不必是相同的连接查询(续1)连接操作的执行过程(1)首先在表1中找到第一个元组,然后从头开始扫描表2,逐一查找满足连接条件的元组,找到后就将表1中的第一个元组与该元组拼接起来,形成结果表中一个元组。(2)表2全部查找完后,再找表1中第二个元组,然后再从头开始扫描表2,逐一查找满足连接条件的元组,找到后就将表1中的第二个元组与该元组拼接起来,形成结果表中一个元组。(3)重复上述操作,直到表1中的全部元组都处理完毕等值连接举例SnoSnameSsexSageSdept2005001李勇男20CS2005002刘晨女19IS2005003王敏女18MA2005004张立男19ISSnoCnoGrade20050011922005001285200500138820050022902005002380Student表SC表Student.snoSnameSsexSageSdeptSC.SnoCnoGrade2005001李勇男20CS20050011922005001李勇男20CS20050012852005001李勇男20CS20050013882005002刘晨女19IS20050022902005002刘晨女19IS2005002380查询结果:自然连接去掉此列等值连接与自然连接查询等值连接:连接运算符为=的连接操作,按对应列的共同值将一个表中的记录与另一个表中的记录相连接,包括其中的重复列。自然连接:不包括重复列。[例31]
查询每个学生及其选修课程的情况。SELECTStudent.*,SC.*FROMStudent,SCWHEREStudent.Sno=SC.Sno;//等值连接或SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudent,SCWHEREStudent.Sno=SC.Sno;//自然连接注:WHERE子句中参与比较的两个属性名若在另一表中存在,则要加所属表名作前辍以区别,无同名则可省前辍。等值连接例6-23
Sales数据库中部门表department和员工表employee的等值连接。SELECT*FROMdepartmentINNERJOINemployeeONemployee.department_id=department.department_id等值连接(续)
例6-22对Sales数据库输出所有员工的销售单,要求给出员工编号、姓名、商品编号、商品名和销售数量。
SELECTemployee.employee_id,employee.employee_name,goods.goods_id,goods.goods_name,sell_order.order_numFROMemployee,sell_order,goodsWHEREemployee.employee_id=sell_order.employee_idandsell_order.goods_id=goods.goods_id不等值连接例6-24对Sales数据库,列出销售“G00001”产品的员工中,销售数量大于编号为“E001”的员工销售该类产品销售数量的那些员工的编号和销售数量。SELECTa.employee_id,a.order_numFROMsell_orderaINNERJOINsell_orderbONa.order_num>b.order_numANDa.goods_id=b.goods_idWHERE(b.goods_id='G00001')AND(b.employee_id='E001')自然连接例6-25Sales数据库中部门表department和员工表employee的自然连接。
SELECTa.department_name,b.*FROMdepartmentaINNERJOINemployeebONb.department_id=a.department_id基本查询回顾SELECT[ALL|DISTINCT][*|<columns>|<arithmeticexpressions>|<AggregateFunctions>][[AS]<alias>],…FROM<table>WHERE<conditions>GROUPBY<columnN>
HAVING<inner-groupconfitions>ORDERBY<columnN>[ASC|DESC]特别注意:集函数只能用于SELECT子句和HAVING短语之中,而绝对不能出现在WHERE子句中。WHERE子句用于对查询的表限定,而HAVING短语用于对结果集中的各分组进行限定。嵌套查询一个SELECT-FROM-WHERE语句称为一个查询块将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询SELECTemployee_name//
外层查询/父查询
FROMemployeeWHEREdepartment_idIN(SELECTdepartment_id//
内层查询/子查询
FROMdepartmentWHEREdepartment_name=‘市场部');子查询的限制--不能使用ORDERBY子句层层嵌套方式反映了SQL语言的结构化有些嵌套查询可以用连接运算替代连接查询连接查询--将两个(以上)表连接进行查询同时涉及多个表的查询一般格式:WHERE[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>FROM表名1<连接类型>表名2ON[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>连接字段连接谓词中的列名称为连接字段连接条件中的各连接字段类型必须是可比的,但不必是相同的等值连接与自然连接查询等值连接:连接运算符为=的连接操作,按对应列的共同值将一个表中的记录与另一个表中的记录相连接,包括其中的重复列。自然连接:不包括重复列。[例31]
查询每个学生及其选修课程的情况。SELECTStudent.*,SC.*FROMStudent,SCWHEREStudent.Sno=SC.Sno;//等值连接或SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudent,SCWHEREStudent.Sno=SC.Sno;//自然连接注:WHERE子句中参与比较的两个属性名若在另一表中存在,则要加所属表名作前辍以区别,无同名则可省前辍。自身连接--一个表与其自己进行连接[例32]查询每一门课的间接先修课(即先修课的先修课)。CnoCnameCpnoCcredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL语言64CnoCnameCpnoCcredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL语言64FirstSecondcnocpno1723545667SELECTFIRST.Cno,SECOND.CpnoFROMCourseFIRST,CourseSECONDWHEREFIRST.Cpno=SECOND.Cno;自身连接(续)[例33]查询每一门课的间接先修课(即先修课的先修课)。
SELECTFIRST.Cno,SECOND.CpnoFROMCourseFIRST,CourseSECONDWHEREFIRST.Cpno=SECOND.Cno;
注:(1)一个表与其自己进行连接,称为表的自身连接(2)需要给表起别名以示区别(3)由于所有属性名都是同名属性,因此必须使用别名前缀外连接(OuterJoin)外连接与内连接的区别内连接操作只输出满足连接条件的元组外连接操作以指定表为连接主体,将主体表中不满足连接条件的元组一并输出SnoSnameSsexSageSdept2005001李勇男20CS2005002刘晨女19IS2005003王敏女18MA2005004张立男19ISSnoCnoGrade20050011922005001285200500138820050022902005002380Student表SC表Student.snoSnameSsexSageSdeptCnoGrade2005001李勇男20CS1922005001李勇男20CS2852005001李勇男20CS3882005002刘晨女19IS2902005002刘晨女19IS3802005003王敏女18MANULLNULL2005004张立男19ISNULLNULL左外连接:StudentSC右外连接举例SnoSnameSsexSageSdept2005001李勇男20CS2005002刘晨女19IS2005003王敏女18MA2005004张立男19ISSnoCnoGrade200500119220050012852005001388200500229020050052682005002380Student表SC表SnameSsexSageSdeptStudent.snoCnoGrade李勇男20CS2005001192李勇男20CS2005001285李勇男20CS2005001388刘晨女19IS2005002290NULLNULLNULLNULL2005005268刘晨女19IS2005002380右外连接:StudentSC交叉连接不带连接谓词的连接很少使用例:SELECTStudent.*,SC.*FROMStudent,SCE_idE_namesexBirthD_idE001李勇男20CSE002刘晨女19ISE003王敏女18MAE004张立男19ISE_idCnoGrade20050011922005001285200500138820050022902005002380employee表SC表交叉连接结果Student.snoSnameSsexSageSdeptSC.SnoCnoGrade2005001李勇男20CS20050011922005001李勇男20CS20050012852005001李勇男20CS20050013882005001李勇男20CS20050022902005001李勇男20CS20050023802005002刘晨女19IS20050011922005002刘晨女19IS20050012852005002刘晨女19IS20050013882005002刘晨女19IS20050022902005002刘晨女19IS20050023802005003王敏女18MA20050011922005003王敏女18MA20050012852005003王敏女18MA20050013882005003王敏女18MA20050022902005003王敏女18MA20050023802005004张立男19IS20050011922005004张立男19IS20050012852005004张立男19IS20050013882005004张立男19IS20050022902005004张立男19IS2005002380选择题在SQL语句中,与表达式”工资BETWEEN1210AND1240“功能相同的表达式是()。A工资>=1210AND工资<=1240B工资>1210AND工资<1240C工资<=1210AND工资>1240D工资>=1210OR工资<=1240选择题使用SQL语句进行分组检索时,为了去掉不满足条件的分组,应当()。A使用WHERE子句B在GROUPBY后面使用HAVING子句C先使用WHERE子句,再使用HAVING子句D先使用HAVING子句,再使用WHERE子句对于某语句的条件WHERESdeptLIKE‘[CS]her%y’,将筛选出()值。ACSherryBSherrieyCChersD[CS]Herry问答题1在SELECT语句中,对查询结果进行排序的子句是什么?能消除重复行的关键字是什么?2写出表达式“仓库号NOTIN(‘wh1’,‘wh2’)”功能相同的表达式。用BETWEEN…AND形式改写条件子句WHEREmark>=550ANDmark<=650。3在一个包含集合函数的SELECT语句中,GROUPBY子句有哪些用途?4HAVING与WHERE同时用于指出查询条件,请说明各自的应用场合。SnoSnameSsexSageSdept2005001李勇男20CS2005002刘晨女19IS2005003王敏女18MA2005004张立男19ISSnoCnoScore200500119220050012852005001388200500229020050052682005002380Student表SC表CnoCnameCpnoCcredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL语言64COURSE表查询语句练习查课程名含有
DB_Design
课程的课程号和学分。查询选修了3号课程的学生的学号及其成绩,查询结果按分数降序排列。查询学习1号课程的学生最高分数。查询选修2号课程且成绩在90分以上的所有学生。查询每个学生及其选修的课程名其及成绩。求2课程的成绩高于张三的学生学号和成绩。求其他系中年龄小于计算机系年龄最大者的学生。求其他系中比计算机系学生年龄都小的学生。求选修了2号课程的学生姓名。查询综合举例及注意事项一、别名的使用 (1)别名用于对输出属性列的重命名例:查询全体学生的姓名、和所在系,并将输出字段依次更名为:姓名、出生年份、所在系。
SELECTSname姓名,year(date())-Sage出生年份,ISLOWER(Sdept)所在系FROMStudent;(2
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 高一信息技术《递推策略:从“李白沽酒”到算法建模》教学设计
- 高三英语读后续写教学设计与评析深度讲义
- 初中七年级数学《有理数的乘除运算》教学设计
- 九年级语文中考文言文复习教学设计:邹忌讽齐王纳谏课标考点精讲
- 小学一年级心理健康《我上学我骄傲》教学设计
- 2027年高三生物遗传实验设计教案
- 初中八年级数学《两条平行线之间的距离》教学设计
- 2026事业单位工勤技能-新疆-新疆垃圾清扫与处理工二级(技师)历年参考题库含答案详解
- 2026事业单位工勤技能-新疆-新疆保健按摩师一级(高级技师)历年参考题库含答案详解
- 大学管理学市场营销策略原理应用测试试卷及答案
- 老年髋部骨折患者围手术期管理专家共识
- 电缆防火封堵施工安全交底
- 两品一械不良反应培训
- 加油站企业内部举报奖励制度
- 针织厂大圆机织造实施细则
- 食品厂GMP培训教学课件
- 2026年食品质量与安全基础知识解析题库
- GB/T 46918.2-2025微细气泡技术水中微细气泡分散体系气体含量的测量方法第2部分:氢气含量
- 国家事业单位招聘2024中国人民银行数字货币研究所招聘6人笔试历年参考题库典型考点附带答案详解(3卷合一)
- 心肺功能评定课件
- 中建三局项目样板引路(样板间)专项施工方案图文完整版
评论
0/150
提交评论