ch4-结构化查询语言SQL

1数据库设计步骤1.需求分析2.概念设计—建立E-R模型3.逻辑设计—建立关系模型4.物理设计5.数据库实现在DBMS中建立数据库概念设计—E-R图通过图来表示实体之间的联系逻辑设计—关系模式通过主、外键来表示关系之间的联系学生（学号，姓名，性别，出生日期，院系）课程（课程号，课程名，开课单位，学时数，学分）学生成绩（学号，课程号，成绩）物理设计字段名称字段数据类型字段属性学号文本主键字段；字段大小：9；输入掩码：000000000;;*姓名文本字段大小：4性别文本字段大小：1；要求利用查阅向导输入“男”或“女”；有效性规则为不能输入”男”和“女”以外的其他值；默认值为男出生日期日期/时间格式：长日期；输入掩码：9999-99-99;0;#；有效性规则为输入的出生日期不得超过系统当前日期民族文本字段大小：4，利用查阅向导输入（值根据下表自定）所属院系文本字段大小：2；要求利用查阅向导输入，数据来自于院系表的院系“编号字段”照片OLE对象例如：学生表表结构：字段有效规则的设置有效规则是指向表中输入数据时设置的字段值所要遵循的约束条件。在向表中输入数据时，若输入的数据不符合字段的有效性规则，系统将显示提示信息，并停留在字段所在位置，直至输入数据符合有效性规则为止。有效性规则两部分：有效性规则有效性文本例：设置“员工”表中“性别”的字段的“有效性规则”，使其只能输入男或女。有效性规则：“男”or“女”有效性文本：“只能输入男或女！”完整性规则实体完整性规则是指保证关系中元组惟一的特性。通过关系的主关键字和候选关键字实现。域完整性规则是指保证关系中属性取值正确、有效的特性。如：定义属性的数据类型、设置属性的有效性规则。参照完整性与关系之间的联系有关，包括插入规则、删除规则和更新规则。用户自定义完整性规则是指为满足用户特定需要而设定的规则。实体完整性和参照完整性是关系模型必须满足的完整性约束条件被称为是关系的两个不变性。参照完整性与相关规则在数据表的关系建立以后，如果希望数据表之间存在一定的约束关系，以保证数据库中数据的有效性。在Access中可以建立参照完整性来建立主表与相关表在增、删、改记录时相关字段数据的正确性。数据表之间的约束性规则建立关系后未实施参照完整性建立参照完整性但未实施级联更新和级联删除规则建立参照完整性并实施了级联更新和级联删除规则通配符及其功能表通配符用法示例*匹配任意字符串，可以是0个或任意多个字符hi*，可以找到hit、hi和hill#匹配一个数字符号20#8，可以找到2008、2018，找不到20A8?匹配任何一个字符w?ll，可以找到wall、well，找不到weell、wll[]匹配括号内任何一个字符t[ae]ll，可以找到tell和tall，找不到tbll!匹配任何不在括号内的字符f[!bc]ll，可以找到fall和fell，找不到fbll和fcll-匹配指定范围内的任何一个字符，必须以递增排序来指定区域（A-Z）b[a-c]d，可以找到bad和bed，找不到bud1213本章要点数据查询语言（SELECT）数据操纵语言（INSERT、UPDATE、DELETE）数据定义语言（CREATE、DROP）SQL语言的介绍SQL（StructuredQueryLanguage）语言是1974年首先在IBM公司研制关系数据库原型系统SystemR上实现。1986年被美国国家标准局批准成为关系型数据库语言的标准。利用SQL语句可以完成各类查询的创建、表定义、表结构维护、表中记录操作等内容。SQL语言是一个非过程化语言，它的大多数语句都是独立执行并完成一个特定操作的，与上下文无关。SQL的主要功能SQL支持数据操作，用来描述数据的动态特性。主要功能有：数据定义功能数据查询功能数据操纵功能数据控制功能使用SQL时，必须使用正确的语法。语法是一组规则，根据需要、按照约定的规则将语言元素正确地组合在一起，就构成了SQL语句，帮助用户完成任务。数据查询语言基本形式：Select字段名|目标列表达式|函数From表名或视图名Where条件表达式语句功能：从指定的表或视图中，创建一个由指定范围、满足条件、按某字段分组、按某字段排序的指定字段组成的新记录集。命令格式：Select[All|Distinct|Top]{*|table.*|[table.]field1[Asalias1][,[table.]field2[Asalias2][,…]}Fromtable_names[Wheresearch_criteria][GroupBygroupfieldlist[Havingaggregate_criteria]][OrderBycolumn_criteria[Asc][Desc]]数据查询语言数据查询语言语句说明All：查询结果是表的全部记录Distinct：查询结果不包括重复行的记录集From表或视图名：查询的数据来源Where条件表达式：查询结果是满足条件的记录GroupBy分组字段名：查询结果按分组字段名分组的记录Having条件表达式：对分组的结果进行限定OrderBy：查询结果按指定的列进行排序，Asc为升序，Desc为降序，系统默认为升序函数：进行查询计算的函数数据查询语言4.1.1简单查询4.1.2多表查询4.1.3排序4.1.4子查询（嵌套查询）4.1.5聚集函数和分组查询4.1.6连接查询4.1.7联合查询简单查询查询是对数据库表中数据进行查找，产生一个动态表的过程。在Access中可以方便地创建查询，在创建查询的过程中定义要查询的内容和规则。在运行查询时系统将在指定的数据表中查找满足条件的记录，组成一个类似数据表的动态表。选择字段SELECTfieldsFROMtable查询学生姓名、性别和所在院系。SELECT姓名,性别,所属院系FROMstudent;例4-1查询student中所有的字段和记录。SELECT*FROMstudent;例4-2选择字段所有课程的学时数增加50%，重新计算各门课程的课时数并列出清单。SELECT课程名称,INT(学分*(1+0.5))AS新学分FROMcourse;例4-5查询学生的姓名，所属院系，出生日期和性别。SELECT姓名,所属院系,出生日期,性别FROMstudent;例4-6查询与表中属性顺序无关，通过查询可得到新的属性顺序。选择记录SELECTfieldsFROMtable[WHEREsearch_criteria]查询会计学院学生的学号，姓名和所在院系。SELECT学号,姓名,所属院系FROMstudentWHERE所属院系=会计学院;例4-7查询学分少于3（不含3学分）的课程名称，课程性质和学分。SELECT课程名称,课程性质,学分FROMcourseWHERE学分3;例4-8查询会计学院的非党员学生。SELECT学号,姓名,所属院系,政治面貌FROMstudentWHERE所属院系=会计学院and政治面貌党员;例4-9查询成绩在60分以下(不包含60分)、90分以上（含90）学生的学号和成绩。SELECT学号,成绩FROMgradeWHERE成绩60or成绩=90;例4-10选择记录选择记录查询教师中所有党员记录。SELECT*FROMteacherWHERE是否党员=true;例4-11查询中可以使用运算符，常用的关系运算符（P79）IsNullIsNotNullInNotInBetween…AndLike选择记录查询所有信息学院和会计学院的学生信息。SELECT*FROMstudentWHERE所属院系IN('信息学院','会计学院');例4-12查询所有选修了课程，但没有参加考试(成绩为“空”)的学生学号。SELECT学号FROMgradeWHERE成绩ISNULL;例4-13SELECT学号FROMgradeWHEREISNULL(成绩);选择记录查询课程名称中最后两个字是“管理”的课程信息。SELECT*FROMcourseWHERE课程名称LIKE'*管理';例4-19查询成绩在60分以下（不含60分）、90分以上（不含90分）学生的学号和成绩。SELECT学号,成绩FROMgradeWHERE成绩NOTBETWEEN60AND90;例4-21多表查询查询会计学院学生选修的课程及成绩，要求查询结果中含学号、姓名、课程名称和成绩。例4-14SELECTstudent.学号,姓名,课程名称,成绩FROMstudent,course,grade,teach_courWHEREstudent.学号=grade.学号andschedule.课程ID=grade.课程IDandcourse.课程编号=schedule.课程编号and所属院系=会计学院and成绩ISNOTNULL;多表查询查询不及格成绩学生的学号和姓名。SELECTDISTINCTstudent.学号,姓名FROMstudent,gradeWHEREstudent.学号=grade.学号and成绩60and成绩ISNOTNULL;例4-15多表查询查询所有选修了课程，但没有参加考试的学生的院系、姓名，课程名和成绩。例4-16SELECT所属院系,student.姓名,course.课程名称,成绩FROMstudent,grade,course,scheduleWHEREstudent.学号=grade.学号andcourse.课程编号=schedule.课程编号andschedule.课程ID=grade.课程IDand成绩ISNULL;多表查询查询经济学院开设的学分在2和4之间（含2和4）的所有课程的课程名称、学分和所属院系。例4-18SELECTcourse.课程名称,学分,所属院系FROMcourse,schedule,teacherWHEREcourse.课程编号=schedule.课程编号andschedule.教师编号=teacher.教师编号and所属院系=经济学院and学分BETWEEN2AND4;排序[ORDERBY]字段名1[ASC|DESC][,字段名2[ASC|DESC]]…]SELECT[TOPn[PERCENT]]…查询会计学院学生的信息，查询结果按学号排序。例4-22SELECT*FROMstudentWHERE所属院系='会计学院'ORDERBY学号;查询所有学生信息，查询结果按院系排序，同一院系学生按性别降序排序。例4-23排序关键字可以是字段名或字段在表中的排列序号。SELECT*FROMstudentORDERBY所属院系,性别DESC;也可以写成SELECT*FROMstudentORDERBY7,3DESC;排序排序查询学生所属院系、学号、姓名、课程名、成绩信息，且按所属院系升序排序，同一院系按学号升序排序，同一学生按课程名升序排序，相同课程按成绩降序排序。例4-24SELECT所属院系,student.学号,姓名,course.课程名称,成绩FROMstudent,grade,course,scheduleWHEREstudent.学号=grade.学号andcourse.课程编号=schedule.课程编号andschedule.课程ID=grade.课程IDand成绩ISNOTNULLORDERBY所属院系,student.学号,course.课程名称,成绩DESC;查询成绩最低的3名学生的课程和成绩信息。例4-24SELECTTOP3所属院系,student.学号,姓名,course.课程名称,成绩FROMstudent,grade,course,scheduleWHEREstudent.学号=grade.学号andcourse.课程编号=schedule.课程编号andschedule.课程ID=grade.课程IDand成