SQL查询教学

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,*,查 询,数据查询,语句格式,SELECT,ALL|DISTINCT ,，, ,FROM,，, ,WHERE, ,GROUP BY, ,HAVING, ,ORDER BY, ASC|DESC ,；,单表查询,查询仅涉及一个表：,一、,选择表中的若干列,二、,选择表中的若干元组,三、,ORDER BY,子句,四、,聚集函数,五、,GROUP BY,子句,1,、 选择表中的若干列,查询指定列,查询全体学生的学号与姓名。,SELECT,Sno,，,Sname,FROM Student,；,查询全体学生的姓名、学号、所在系。,SELECT,Sname,，,Sno,，,Sdept,FROM Student,；,2.,查询全部列,选出所有属性列：,在,SELECT,关键字后面列出所有列名,将,指定为,*,查询全体学生的详细记录。,SELECT,Sno,，,Sname,，,Ssex,，,Sage,，,Sdept,FROM Student,；,或,SELECT *,FROM Student,；,3.,查询经过计算的值,SELECT,子句的,可以为：,算术表达式,字符串常量,函数,列别名,查全体学生的姓名及其出生年份。,SELECT,Sname,，,2004-Sage /*,假定当年的年份为,2004,年*,/,FROM Student,；,输出结果：,Sname,2004-Sage,李勇,1984,刘晨,1985,王敏,1986,张立,1985,查询经过计算的值（续）,查询经过计算的值（续）,查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名,SELECT,Sname,，,Year of Birth: ,，,2004-Sage,，,LOWER(Sdept,),FROM Student,；,输出结果：,Sname,Year of Birth: 2004-Sage,ISLOWER(Sdept,),李勇,Year of Birth: 1984,cs,刘晨,Year of Birth: 1985 is,王敏,Year of Birth: 1986 ma,张立,Year of Birth: 1985 is,查询经过计算的值（续）,使用列,别名,改变查询结果的列标题,:,SELECT,Sname,NAME,，,Year of Birth: ,BIRTH,，,2000-Sage,BIRTHDAY,，,LOWER(Sdept,),DEPARTMENT,FROM,Student,；,输出结果：,NAME BIRTH BIRTHDAY DEPARTMENT,- - - -,李勇,Year of Birth: 1984,cs,刘晨,Year of Birth: 1985 is,王敏,Year of Birth: 1986 ma,张立,Year of Birth: 1985 is,单表查询,查询仅涉及一个表：,一、,选择表中的若干列,二、,选择表中的若干元组,三、,ORDER BY,子句,四、,聚集函数,五、,GROUP BY,子句,选择表中的若干元组,1.,消除取值重复的行,如果没有指定,DISTINCT,关键词，则缺省为,ALL,查询选修了课程的学生学号。,SELECT,Sno,FROM SC,；,等价于：,SELECT ALL,Sno,FROM SC,；,执行上面的,SELECT,语句后，结果为：,Sno,200215121,200215121,200215121,200215122,200215122,消除取值重复的行（续）,指定,DISTINCT,关键词，去掉表中重复的行,SELECT,DISTINCT,Sno,FROM SC,；,执行结果：,Sno,200215121,200215122,2.,查询满足条件的元组,查,询,条,件,谓,词,比,较,=,，,，,=,，,，,!,；,NOT+,上述比较运算符,确定范围,BETWEEN AND,，,NOT BETWEEN AND,确定集合,IN,，,NOT IN,字符匹配,LIKE,，,NOT LIKE,空,值,IS NULL,，,IS NOT NULL,多重条件（逻辑运算）,AND，OR，NOT,表,3.4,常用的查询条件,(1),比较大小,查询计算机科学系全体学生的名单。,SELECT,Sname,FROM Student,WHERE,Sdept,=CS,；,查询所有年龄在,20,岁以下的学生姓名及其年龄。,SELECT,Sname,，,Sage,FROM Student,WHERE Sage 20,；,查询考试成绩有不及格的学生的学号。,SELECT,DISTINCT,Sno,FROM SC,WHERE Grade60,；,（,2,）确定范围,谓词,:,BETWEEN AND ,NOT BETWEEN AND ,查询年龄在,2023,岁（包括,20,岁和,23,岁）之间的学生的,姓名、系别和年龄,SELECT,Sname,，,Sdept,，,Sage,FROM Student,WHERE Sage BETWEEN 20 AND 23,；,查询年龄不在,2023,岁之间的学生姓名、系别和年龄,SELECT,Sname,，,Sdept,，,Sage,FROM Student,WHERE Sage NOT BETWEEN 20 AND 23,；,(3),确定集合,谓词：,IN , NOT IN ,查询信息系（,IS,）、,数学系（,MA,）,和计算机科学系（,CS,）,学生的姓名和性别。,SELECT,Sname,，,Ssex,FROM Student,WHERE,Sdept,IN ( IS,，,MA,，,CS );,查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。,SELECT,Sname,，,Ssex,FROM Student,WHERE,Sdept,NOT IN ( IS,，,MA,，,CS );,(4),字符匹配,谓词：,NOT LIKE ESCAPE ,匹配串为固定字符串,查询学号为,200215121,的学生的详细情况。,SELECT *,FROM Student,WHERE,Sno,LIKE,200215121,；,等价于：,SELECT *,FROM Student,WHERE,Sno,= 200215121 ,；,字符匹配（续）,2),匹配串为含通配符的字符串,SQL Server,提供了四种通配符,:%,代表任意多个字符,_(,下划线,),代表单个字符, ,代表指定范围内的单个字符,代表不在指定范围内的单个字符。,查询所有姓刘学生的姓名、学号和性别。,SELECT,Sname,，,Sno,，,Ssex,FROM Student,WHERE,Sname,LIKE ,刘,%,；,查询姓,欧阳,且全名为三个汉字的学生的姓名。,SELECT,Sname,FROM Student,WHERE,Sname,LIKE ,欧阳,_,；,字符匹配（续）,查询名字中第,2,个字为,阳,字的学生的姓名和学号。,SELECT,Sname,，,Sno,FROM Student,WHERE,Sname,LIKE _,阳,%,；,查询所有不姓刘的学生姓名。,SELECT,Sname,，,Sno,，,Ssex,FROM Student,WHERE,Sname,NOT LIKE,刘,%,；,字符匹配（续）,3),使用换码字符将通配符转义为普通字符,查询,DB_Design,课程的课程号和学分。,SELECT,Cno,，,Ccredit,FROM Course,WHERE,Cname,LIKE DB,_Design,ESCAPE ,；,查询以,DB_,开头，且倒数第,3,个字符为,i,的课程的详细情况。,SELECT *,FROM Course,WHERE,Cname,LIKE DB,_%i_ _,ESCAPE ,；,ESCAPE ,表示“ ” 为换码字符,(5),涉及空值的查询,谓词：,IS NULL,或,IS NOT NULL,“IS”,不能用 “,=”,代替,某些学生选修课程后没有参加考试，所以有选课记录，但没,有考试成绩。查询缺少成绩的学生的学号和相应的课程号。,SELECT,Sno,，,Cno,FROM SC,WHERE Grade IS NULL,查所有有成绩的学生学号和课程号。,SELECT,Sno,，,Cno,FROM SC,WHERE Grade IS NOT NULL,；,(6),多重条件查询,逻辑运算符：,AND,和,OR,来联结多个查询条件,AND,的优先级高于,OR,可以用括号改变优先级,可用来实现多种其他谓词,NOT IN,NOT BETWEEN AND ,多重条件查询（续）,查询计算机系年龄在,20,岁以下的学生姓名。,SELECT,Sname,FROM Student,WHERE,Sdept,= CS AND Sage20,；,多重条件查询（续）,改写,查询信息系（,IS,）、,数学系（,MA,）,和计算机科学系（,CS,）,学生的姓名和性别。,SELECT,Sname,，,Ssex,FROM Student,WHERE,Sdept,IN ( IS,，,MA,，,CS ),可改写为：,SELECT,Sname,，,Ssex,FROM Student,WHERE,Sdept,= IS OR,Sdept,= MA OR,Sdept,= CS ,；,单表查询,查询仅涉及一个表：,一、,选择表中的若干列,二、,选择表中的若干元组,三,、,ORDER BY,子句,四、,聚集函数,五、,GROUP BY,子句,ORDER,BY,子句,ORDER BY,子句,可以按一个或多个属性列排序,升序：,ASC,；,降序：,DESC,；,缺省值为升序,当排序列含空值时,ASC,：,排序列为空值的元组最后显示,DESC,：,排序列为空值的元组最先显示,ORDER BY,子句 （续）,查询选修了,3,号课程的学生的学号及其成绩，查询结果按分数降序排列。,SELECT,Sno,，,Grade,FROM SC,WHERE,Cno,= 3 ,ORDER BY Grade DESC,；,查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。,SELECT *,FROM Student,ORDER BY,Sdept,，,Sage DESC,；,嵌套查询,嵌套查询概述,一个,SELECT-FROM-WHERE,语句称为一个,查询块,将一个查询块嵌套在另一个查询块的,WHERE,子句或,HAVING,短语的条件中的查询称为,嵌套查询,嵌套查询,(,续,),SELECT,Sname,/*,外层查询,/,父查询*,/,FROM Student,WHERE,Sno,IN,（,SELECT,Sno,/*,内层查询,/,子查询*,/,FROM SC,WHERE,Cno,= 2 ,）；,插 入,插入数据,两种插入数据方式,1.,插入元组,2.,插入子查询结果,可以一次插入多个元组,一、插入元组,语句格式,INSERT,INTO (,，,),VALUES ( ,，, ),功能,将新元组插入指定表中,插入元组（续）,INTO,子句,属性列的顺序可与表定义中的顺序不一致,没有指定属性列,指定部分属性列,VALUES,子句,提供的值必须与,INTO,子句匹配,值的个数,值的类型,插入元组（续）,例,1, 将一个新学生元组（学号：,200215128,；姓名：陈冬；性别：男；所在系：,IS,；年龄：,18,岁）插入到,Student,表中。,INSERT,INTO Student (,Sno,，,Sname,，,Ssex,，,Sdept,，,Sage),VALUES (200215128,，,陈冬,，,男,，,IS,，,18),；,插入元组（续）,例,2, 将学生张成民的信息插入到,Student,表中。,INSERT,INTO Student,VALUES (200215126,，,张成民,，,男,，,18,，,CS);,插入元组（续）,例,3, 插入一条选课记录,( 200215128,，,1 ),。,INSERT,INTO,SC(Sno,，,Cno,),VALUES ( 200215128 ,，,1 ),；,RDBMS,将在新插入记录的,Grade,列上自动地赋空值。,或者：,INSERT,INTO SC,VALUES ( 200215128 ,，, 1 ,，,NULL),；,二、插入子查询结果,语句格式,INSERT,INTO, ( ,，, ),子查询；,功能,将子查询结果插入指定表中,插入子查询结果（续）,INTO,子句,(,与插入元组类似,),子查询,SELECT,子句目标列必须与,INTO,子句匹配,值的个数,值的类型,插入子查询结果（续）,例,4,对每一个系，求学生的平均年龄，并把结果存入数据库。,第一步：建表,CREATE TABLE,Dept_age,(,Sdept,CHAR(15) /*,系名*,/,Avg_age,SMALLINT),；,/*,学生平均年龄*,/,插入子查询结果（续）,第二步：插入数据,INSERT,INTO,Dept_age(Sdept,，,Avg_age,),SELECT,Sdept,，,AVG(Sage),FROM Student,GROUP BY,Sdept,；,插入子查询结果（续）,RDBMS,在执行插入语句时会检查所插元组是,否破坏表上已定义的完整性规则,实体完整性,参照完整性,用户定义的完整性,NOT NULL,约束,UNIQUE,约束,值域约束,修 改,修改数据,语句格式,UPDATE ,SET =,，,=,WHERE ,；,功能,修改指定表中满足,WHERE,子句条件的元组,修改数据（续）,SET,子句,指定修改方式,要修改的列,修改后取值,WHERE,子句,指定要修改的元组,缺省表示要修改表中的所有元组,修改数据（续）,三种修改方式,1.,修改某一个元组的值,2.,修改多个元组的值,3.,带子查询的修改语句,1.,修改某一个元组的值,例,5,将学生,200215121,的年龄改为,22,岁,UPDATE Student,SET Sage=22,WHERE,Sno,= 200215121 ,；,2.,修改多个元组的值,例,6,将所有学生的年龄增加,1,岁,UPDATE Student,SET Sage= Sage+1,；,3.,带子查询的修改语句,例,7,将计算机科学系全体学生的成绩置零。,UPDATE SC,SET Grade=0,WHERE CS=,(SELETE,Sdept,FROM Student,WHERE,Student.Sno,=,SC.Sno,),；,修改数据（续）,RDBMS,在执行修改语句时会检查修改操作,是否破坏表上已定义的完整性规则,实体完整性,主码不允许修改,用户定义的完整性,NOT NULL,约束,UNIQUE,约束,值域约束,删 除,删除数据,语句格式,DELETE,FROM ,WHERE ,；,功能,删除指定表中满足,WHERE,子句条件的元组,WHERE,子句,指定要删除的元组,缺省表示要删除表中的全部元组，表的定义仍在字典中,删除数据（续）,三种删除方式,1.,删除某一个元组的值,2.,删除多个元组的值,3.,带子查询的删除语句,1.,删除某一个元组的值,例,8,删除学号为,200215128,的学生记录。,DELETE,FROM Student,WHERE,Sno,= 200215128 ,；,2.,删除多个元组的值,例,9,删除所有的学生选课记录。,DELETE,FROM SC,；,3.,带子查询的删除语句,例,10,删除计算机科学系所有学生的选课记录。,DELETE,FROM SC,WHERE CS=,(SELETE,Sdept,FROM Student,WHERE,Student.Sno,=,SC.Sno,),；,下课了。,休息一会儿。,追,