SQL语句学习实例版.

SQL语句学习手册实例版(仅供学习表操作例1对于表的教学管理数据库中的表STUDENTS,可以定义如下:CREATETABLESTUDENTS(SNONUMERIC(6,0NOTNULLSNAMECHAR(8NOTNULLAGENUMERIC(3,0SEXCHAR(2BPLACECHAR(20PRIMARYKEY(SNO例2对于表的教学管理数据库中的表ENROLLS,可以定义如下:CREATETABLEENROLLS(SNONUMERIC(6,0NOTNULLCNOCHAR(4NOTNULLGRADEINTPRIMARYKEY(SNO,CNOFOREIGNKEY(SNOREFERENCESSTUDENTS(SNOFOREIGNKEY(CNOREFERENCESCOURSES(CNOCHECK((GRADEISNULLOR(GRADEBETWEEN0AND100例3根据表的STUDENTS表,建立一个只包含学号、姓名、年龄的女学生表。CREATETABLEGIRLASSELECTSNO,SNAME,AGEFROMSTUDENTSWHERESEX='女';例4删除教师表TEACHER。DROPTABLETEACHER例5在教师表中增加住址列。ALTERTABLETEACHERSADD(ADDRCHAR(50例6把STUDENTS表中的BPLACE列删除,并且把引用BPLACE列的所有视图和约束也一起删除。ALTERTABLESTUDENTSDROPBPLACECASCADE例7补充定义ENROLLS表的主关键字。ALTERTABLEENROLLSADDPRIMARYKEY(SNO,CNO;视图操作(虚表例9建立一个只包括教师号、姓名和年龄的视图FACULTY。(在视图定义中不能包含ORDERBY子句CREATEVIEWFACULTYASSELECTTNO,TNAME,AGEFROMTEACHERS例10从学生表、课程表和选课表中产生一个视图GRADE_TABLE,它包括学生姓名、课程名和成绩。CREATEVIEWGRADE_TABLEASSELECTSNAME,CNAME,GRADEFROMSTUDENTS,COURSES,ENROLLSWHERESTUDENTS.SNO=ENROLLS.SNOANDCOURSES.CNO=ENROLLS.CNO例11删除视图GRADE_TABLEDROPVIEWGRADE_TABLERESTRICT索引操作例12在学生表中按学号建立索引。CREATEUNIQUEINDEXSTONSTUDENTS(SNO,ASC例13删除按学号所建立的索引。DROPINDEXST数据库模式操作例14创建一个简易教学数据库的数据库模式TEACHING_DB,属主为ZHANG。CREATESCHEMATEACHING_DBAUTHRIZATIONZHANG例15删除简易教学数据库模式TEACHING_DB。((1选用CASCADE,即当删除数据库模式时,则本数据库模式和其下属的基本表、视图、索引等全部被删除。(2选用RESTRICT,即本数据库模式下属的基本表、视图、索引等事先已清除,才能删除本数据库模式,否则拒绝删除。DROPSCHEMATEACHING_DBCASCADE单表操作例16找出3个学分的课程号和课程名。SELECTCNO,CNAMEFROMCOURSESWHERECREDIT=3例17查询年龄大于22岁的学生情况。SELECT*FROMSTUDENTSWHEREAGE22例18找出籍贯为河北的男生的姓名和年龄。SELECTSNAME,AGEFROMSTUDENTSWHEREBPLACE='河北'ANDSEX='男'例19找出年龄在20~23岁之间的学生的学号、姓名和年龄,并按年龄升序排序。(ASC(升序或DESC(降序声明排序的方式,缺省为升序。SELECTSNO,SNAME,AGEFROMSTUDENTSWHEREAGEBETWEEN20AND23ORDERBYAGE例20找出年龄小于23岁、籍贯是湖南或湖北的学生的姓名和性别。(条件比较运算符=、和逻辑运算符AND(与,此外还可以使用的运算符有:(大于、=(大于等于、=(小于等于、(不等于、NOT(非、OR(或等。谓词LIKE只能与字符串联用,常常是“列名LIKEpattern”的格式。特殊字符“_”和“%”作为通配符。谓词IN表示指定的属性应与后面的集合(括号中的值集或某个查询子句的结果中的某个值相匹配,实际上是一系列的OR(或的缩写。谓词NOTIN表示指定的属性不与后面的集合中的某个值相匹配。谓词BETWEEN是“包含于…之中”的意思。SELECTSNAME,SEXFROMSTUDENTSWHEREAGE23ANDBPLACELIKE'湖%'或SELECTSNAME,SEXFROMSTUDENTSWHEREAGE23ANDBPLACEIN('湖南','湖北'例22找出学生表中籍贯是空值的学生的姓名和性别。(在SQL中不能使用条件:列名=NULL。在SQL中只有一个特殊的查询条件允许查询NULL值:SELECTSNAME,SEXFROMSTUDENTSWHEREBPLACEISNULL多表操作例23找出成绩为95分的学生的姓名。(子查询SELECTSNAMEFROMSTUDENTSWHERESNO=(SELECTSNOFROMENROLLSWHEREGRADE=95例24找出成绩在90分以上的学生的姓名。SELECTSNAMEFROMSTUDENTSWHERESNOIN(SELECTSNOFROMENROLLSWHEREGRADE90或SELECTSNAMEFROMSTUDENTSWHERESNO=ANY(SELECTSNOFROMENROLLSWHEREGRADE90例25查询全部学生的学生名和所学课程号及成绩。(连接查询SELECTSNAME,CNO,GRADEFROMSTUDENTS,ENROLLSWHERESTUDENTS.SNO=ENROLLS.SNO例26找出籍贯为山西或河北,成绩为90分以上的学生的姓名、籍贯和成绩。(当构造多表连接查询命令时,必须遵循两条规则。第一,连接条件数正好比表数少1(若有三个表,就有两个连接条件;第二,若一个表中的主关键字是由多个列组成,则对此主关键字中的每一个列都要有一个连接条件(也有少数例外情况SELECTSNAME,BPLACE,GRADEFROMSTUDENTS,ENROLLSWHEREBPLACEIN(…山西‟,…河北‟ANDGRADE=90ANDSTUDENTS.SNO=ENROLLS.SNO例28查出课程成绩在80分以上的女学生的姓名、课程名和成绩。(FROM子句中的子查询SELECTSNAME,CNAME,GRADEFROM(SELECTSNAME,CNAME,GRADEFROMSTUDENTS,ENROLLS,COURSESWHERESEX='女'ASTEMP(SNAME,CNAME,GRADEWHEREGRADE80表达式与函数的使用例29查询各课程的学时数。(算术表达式由算术运算符+、-、*、/与列名或数值常量所组成。SELECTCNAME,COURSE_TIME=CREDIT*16FROMCOURSES例30找出教师的最小年龄。(内部函数:SQL标准中只使用COUNT、SUM、AVG、MAX、MIN函数,称之为聚集函数(SetFunction。COUNT函数的结果是该列统计值的总数目,SUM函数求该列统计值之和,AVG函数求该列统计值之平均值,MAX函数求该列最大值,MIN函数求该列最小值。SELECTMIN(AGEFROMTEACHERS例31统计年龄小于等于22岁的学生人数。(统计SELECTCOUNT(*FROMSTUDENTSWHEREAGE=22例32找出学生的平均成绩和所学课程门数。SELECTSNO,AVG(GRADE,COURSES=COUNT(*FROMENROLLSGROUPBYSNO例34找出年龄超过平均年龄的学生姓名。SELECTSNAMEFROMSTUDENTSWHEREAGE(SELECTAVG(AGEFROMSTUDENTS例35找出各课程的平均成绩,按课程号分组,且只选择学生超过3人的课程的成绩。(GROUPBY与HAVINGGROUPBY子句把一个表按某一指定列(或一些列上的值相等的原则分组,然后再对每组数据进行规定的操作。GROUPBY子句总是跟在WHERE子句后面,当WHERE子句缺省时,它跟在FROM子句后面。HAVING子句常用于在计算出聚集之后对行的查询进行控制。SELECTCNO,AVG(GRADE,STUDENTS=COUNT(*FROMENROLLSGROUPBYCNOHAVINGCOUNT(*=3相关子查询例37查询没有选任何课程的学生的学号和姓名。(当一个子查询涉及到一个来自外部查询的列时,称为相关子查询(CorrelatedSubquery。相关子查询要用到存在测试谓词EXISTS和NOTEXISTS,以及ALL、ANY(SOME等。SELECTSNO,SNAMEFROMSTUDENTSWHERENOTEXISTS(SELECT*FROMENROLLSWHEREENROLLS.SNO=STUDENTS.SNO例38查询哪些课程只有男生选读。SELECTDISTINCTCNAMEFROMCOURSESCWHERE'男'=ALL(SELECTSEXFROMENROLLS,STUDENTSWHEREENROLLS.SNO=STUDENTS.SNOANDENROLLS.CNO=C.CNO例39要求给出一张学生、籍贯列表,该表中的学生的籍贯省份,也是其他一些学生的籍贯省份。SELECTSNAME,BPLACEFROMSTUDENTSAWHEREEXISTS(SELECT*FROMSTUDENTSBWHEREA.BPLACE=B.BPLACEANDA.SNOB.SNO例40找出选修了全部课程的学生的姓名。本查询可以改为:查询这样一些学生,没有一门课程是他不选修的。SELECTSNAMEFROMSTUDENTSWHERENOTEXISTS(SELECT*FROMCOURSESWHERENOTEXISTS(SELECT*FROMENROLLSWHEREENROLLS.SNO=STUDENTS.SNOANDENROLLS.CNO=COURSES.CNO关系代数运算例41设有某商场工作人员的两张表:营业员表SP_SUBORD和营销经理表SP_MGR,其关系数据模式如下:SP_SUBORD(SALPERS_ID,SALPERS_NAME,MANAGER_ID,OFFICESP_MGR(SALPERS_ID,SALPERS_NAME,MANAGER_ID,OFFICE其中,属性SALPERS_ID为工作人员的编号,SALPERS_NAME为工作人员的姓名,MANAGER_ID为所在部门经理的编号,OFFICE为工作地点。若查询全部商场工作人员,可以用下面的SQL语句:(SELECT*FROMSP_SUBORDUNION(SELECT*FROMSP_MGR或等价地用下面的SQL语句:SELECT*FROM(TABLESP_SUBORDUNIONTABLESP_MGR(2INTERSECT(SELECT*FROMSP_SUBORDINTERSECT(SELECT*FROMSP_MGR或等价地用下面的SQL语句:SELECT*FROM(TABLESP_SUBORDINTERSECTTABLESP_MGR或用带ALL的SQL语句:(SELECT*FROMSP_SUBORDINTERSECTALL(SELECT*FROMSP_MGR或SELECT*FROM(TABLESP_SUBORDINTERSECTALLTABLESP_MGR(3EXCEPT(SELECT*FROMSP_MGREXCEPT(SEL