语句格式 SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>] … FROM <表名或视图名>[,<表名或视图名> ]…|(SELECT 语句) [AS]<别名> [ WHERE <条件表达式> ] [ GROUP BY <列名1> [ HAVING <条件表达式> ] ] [ ORDER BY <列名2> [ ASC|DESC ] ]; 说明:
查询指定列: 【1】查询全体学生的学号与姓名。 SELECT Sno,Sname FROM Student; 【2】查询全体学生的姓名、学号、所在系。 SELECT Sname,Sno,Sdept FROM Student; 查询全部列:在SELECT关键字后面列出所有列名 ,将<目标列表达式>指定为 * 【3】查询全体学生的详细记录 SELECT Sno,Sname,Ssex,Sage,Sdept FROM Student; 或 SELECT * FROM Student; 查询经过计算的值 :SELECT子句的<目标列表达式>不仅可为表中的属性列,也可是表达式 【4】查全体学生的姓名及其出生年份。 SELECT Sname,2014-Sage /*假设当时为2014年*/ FROM Student; 输出结果: Sname 2014-Sage 李勇 1994 刘晨 1995 王敏 1996 张立 1995 【5】查询全体学生的姓名、出生年份和所在的院系,要求用小写字母表示系名。 SELECT Sname,'Year of Birth: ',2014-Sage,LOWER(Sdept) FROM Student; 输出结果: Sname 'Year of Birth:' 2014-Sage LOWER(Sdept) 李勇 Year of Birth: 1994 cs 刘晨 Year of Birth: 1995 cs 王敏 Year of Birth: 1996 ma 张立 Year of Birth: 1995 is 【6】使用列别名改变查询结果的列标题: SELECT Sname NAME,'Year of Birth:' BIRTH, 2014-Sage BIRTHDAY,LOWER(Sdept) DEPARTMENT FROM Student; 输出结果: NAME BIRTH BIRTHDAY DEPARTMENT 李勇 Year of Birth: 1994 cs 刘晨 Year of Birth: 1995 cs 王敏 Year of Birth: 1996 ma 张立 Year of Birth: 1995 is
消除取值重复的行,如果没有指定DISTINCT关键词,则缺省为ALL 【1】查询选修了课程的学生学号。 SELECT Sno FROM SC; 等价于: SELECT ALL Sno FROM SC; 执行上面的SELECT语句后,结果为: Sno 201215121 201215121 201215121 201215122 201215122 指定DISTINCT关键词,去掉表中重复的行 SELECT DISTINCT Sno FROM SC; 执行结果: Sno 201215121 201215122
【1】查询计算机科学系全体学生的名单。 SELECT Sname FROM Student WHERE Sdept=‘CS’; 【1】查询所有年龄在20岁以下的学生姓名及其年龄。 SELECT Sname,Sage FROM Student WHERE Sage < 20; 【1】查询考试成绩有不及格的学生的学号。 SELECT DISTINCT Sn FROM SC WHERE Grade<60; 谓词: BETWEEN … AND … NOT BETWEEN … AND … 【1】查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄 SELECT Sname, Sdept, Sage FROM Student WHERE Sage BETWEEN 20 AND 23; 【1】 查询年龄不在20~23岁之间的学生姓名、系别和年龄 SELECT Sname, Sdept, Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23; 谓词:IN <值表>, NOT IN <值表> 【1】查询计算机科学系(CS)、数学系(MA)和信息系(IS)学生的姓名和性别。 SELECT Sname, Ssex FROM Student WHERE Sdept IN ('CS','MA’,'IS' ); 【1】查询既不是计算机科学系、数学系,也不是信息系的学生的姓名和性别。 SELECT Sname, Ssex FROM Student WHERE Sdept NOT IN ('IS','MA’,'CS' ); 谓词: [NOT] LIKE ‘<匹配串>’ [ESCAPE ‘ <换码字符>’] 说明:
匹配串为固定字符串 【1】 查询学号为201215121的学生的详细情况。 SELECT * FROM Student WHERE Sno LIKE ‘201215121'; 等价于: SELECT * FROM Student WHERE Sno = ' 201215121 '; 匹配串为含通配符的字符串 【1】 查询所有姓刘学生的姓名、学号和性别。 SELECT Sname, Sno, Ssex FROM Student WHERE Sname LIKE '刘%'; 【1】查询姓"欧阳"且全名为三个汉字的学生的姓名。 SELECT Sname FROM Student WHERE Sname LIKE '欧阳__'; 【1】查询名字中第2个字为"阳"字的学生的姓名和学号。 SELECT Sname,Sno FROM Student WHERE Sname LIKE '__阳%'; 【1】查询所有不姓刘的学生姓名、学号和性别。 SELECT Sname, Sno, Ssex FROM Student WHERE Sname NOT LIKE '刘%'; 使用换码字符将通配符转义为普通字符 ESCAPE '\' 表示“ \” 为换码字符 【1】查询DB_Design课程的课程号和学分。 SELECT Cno,Ccredit FROM Course WHERE Cname LIKE 'DB\_Design' ESCAPE '\ ' ; 【1】查询以"DB_"开头,且倒数第3个字符为 i的课程的详细情况。 SELECT * FROM Course WHERE Cname LIKE 'DB\_%i_ _' ESCAPE '\ ' ; 谓词: IS NULL 或 IS NOT NULL,注意“IS” 不能用 “=” 代替 【1】某些学生选修课程后没有参加考试,所以有选课记录,但没 有考试成绩。查询缺少成绩的学生的学号和相应的课程号。 SELECT Sno,Cno FROM SC WHERE Grade IS NULL 【1】查所有有成绩的学生学号和课程号。 SELECT Sno,Cno FROM SC WHERE Grade IS NOT NULL; 逻辑运算符:AND和 OR来连接多个查询条件 AND的优先级高于OR 可以用括号改变优先级 【1】查询计算机系年龄在20岁以下的学生姓名。 SELECT Sname FROM Student WHERE Sdept= 'CS' AND Sage<20; 【1】查询计算机科学系(CS)、数学系(MA)和信息系(IS)学生的姓名和性别。 SELECT Sname, Ssex FROM Student WHERE Sdept IN ('CS ','MA ','IS') 可改写为: SELECT Sname, Ssex FROM Student WHERE Sdept= ' CS' OR Sdept= ' MA' OR Sdept= 'IS ';
ORDER BY子句
【1】查询选修了3号课程的学生的学号及其成绩,查询结果按分数降序排列。 SELECT Sno, Grade FROM SC WHERE Cno= ' 3 ' ORDER BY Grade DESC; 【1】查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列。 SELECT * FROM Student ORDER BY Sdept, Sage DESC;
聚集函数:
【1】查询学生总人数。 SELECT COUNT(*) FROM Student; 【1】查询选修了课程的学生人数。 SELECT COUNT(DISTINCT Sno) FROM SC; 【1】计算1号课程的学生平均成绩。 SELECT AVG(Grade) FROM SC WHERE Cno= ' 1 '; 【1】查询选修1号课程的学生最高分数。 SELECT MAX(Grade) FROM SC WHERE Cno='1'; 【1】查询学生201215012选修课程的总学分数。 SELECT SUM(Ccredit) FROM SC,Course WHERE Sno='201215012' AND SC.Cno=Course.Cno;
GROUP BY子句分组:细化聚集函数的作用对象
【1】求各个课程号及相应的选课人数。 SELECT Cno,COUNT(Sno) FROM SC GROUP BY Cno; 查询结果可能为: Cno COUNT(Sno) 1 22 2 34 3 44 4 33 5 48 【1】查询选修了3门以上课程的学生学号。 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) >3; 【1】查询平均成绩大于等于90分的学生学号和平均成绩 下面的语句是不对的: SELECT Sno, AVG(Grade) FROM SC WHERE AVG(Grade)>=90 GROUP BY Sno; 因为WHERE子句中是不能用聚集函数作为条件表达式 正确的查询语句应该是: SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno HAVING AVG(Grade)>=90; HAVING短语与WHERE子句的区别:
连接条件或连接谓词:用来连接两个表的条件 一般格式: [<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2> [<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3> 说明:
等值连接:连接运算符为= 【1】查询每个学生及其选修课程的情况 SELECT Student.*, SC.* FROM Student, SC WHERE Student.Sno = SC.Sno; 查询结果:
自然连接 【1】对[例 3.49]用自然连接完成。 SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student,SC WHERE Student.Sno = SC.Sno; 【1】查询选修2号课程且成绩在90分以上的所有学生的学号和姓名。 SELECT Student.Sno, Sname FROM Student, SC WHERE Student.Sno=SC.Sno AND SC.Cno=' 2 ' AND SC.Grade>90;
自身连接:一个表与其自己进行连接,需要给表起别名以示区别 由于所有属性名都是同名属性,因此必须使用别名前缀 【1】查询每一门课的间接先修课(即先修课的先修课) SELECT FIRST.Cno, SECOND.Cpno FROM Course FIRST, Course SECOND WHERE FIRST.Cpno = SECOND.Cno;
外连接与普通连接的区别
左外连接:列出左边关系中所有的元组 右外连接:列出右边关系中所有的元组 【1】查询每个学生及其选修课程的情况 SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student LEFT OUT JOIN SC ON (Student.Sno=SC.Sno);
【1】查询每个学生的学号、姓名、选修的课程名及成绩 SELECT Student.Sno, Sname, Cname, Grade FROM Student, SC, Course /*多表连接*/ WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;
嵌套查询概述 一个SELECT-FROM-WHERE语句称为一个查询块 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询 SELECT Sname /*外层查询/父查询*/ FROM Student WHERE Sno IN ( SELECT Sno /*内层查询/子查询*/ FROM SC WHERE Cno= ' 2 '); 说明:
【1】查询与“刘晨”在同一个系学习的学生。 此查询要求可以分步来完成 ① 确定“刘晨”所在系名 SELECT Sdept FROM Student WHERE Sname= ' 刘晨 '; 结果为: CS ② 查找所有在CS系学习的学生。 SELECT Sno, Sname, Sdept FROM Student WHERE Sdept= ' CS '; 结果为:
【1】将第一步查询嵌入到第二步查询的条件中【此查询为不相关子查询】 SELECT Sno, Sname, Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname= ' 刘晨 '); 【1】用自身连接完成查询与“刘晨”在同一个系学习的学生。 SELECT S1.Sno, S1.Sname,S1.Sdept FROM Student S1,Student S2 WHERE S1.Sdept = S2.Sdept AND S2.Sname = '刘晨'; 【1】查询选修了课程名为“信息系统”的学生学号和姓名 SELECT Sno,Sname ③ 最后在Student关系中 FROM Student 取出Sno和Sname WHERE Sno IN (SELECT Sno ② 然后在SC关系中找出选 FROM SC 修了3号课程的学生学号 WHERE Cno IN (SELECT Cno ① 首先在Course关系中找出 FROM Course “信息系统”的课程号,为3号 WHERE Cname= '信息系统' ) ); 【1】用连接查询实现上述示例 SELECT Sno,Sname FROM Student,SC,Course WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Course.Cname='信息系统';
当能确切知道内层查询返回单值时,可用比较运算符(>,<,=,>=,<=,!=或< >)。 【1】在查询与“刘晨”在同一个系学习的学生。中,由于一个学生只可能在一个系学习,则可以用 = 代替IN : SELECT Sno,Sname,Sdept FROM Student WHERE Sdept = (SELECT Sdept FROM Student WHERE Sname= '刘晨'); 【1】找出每个学生超过他选修课程平均成绩的课程号。【相关子查询】 SELECT Sno, Cno FROM SC x WHERE Grade >=(SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno); 可能的执行过程 ①从外层查询中取出SC的一个元组x,将元组x的Sno值(201215121)传送给内层查询。 SELECT AVG(Grade) FROM SC y WHERE y.Sno='201215121‘; ②执行内层查询,得到值88(近似值),用该值代替内层查询,得到外层查询: SELECT Sno,Cno FROM SC x WHERE Grade >=88; ③执行这个查询,得到 (201215121,1) (201215121,3) 然后外层查询取出下一个元组重复上述①至③步骤,直到外层的SC元组全部处理完。结果: (201215121,1) (201215121,3) (201215122,2)
使用ANY或ALL谓词时必须同时使用比较运算 语义为:
【1】查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄 SELECT Sname,Sage FROM Student WHERE Sage < ANY (SELECT Sage FROM Student WHERE Sdept= ' CS ') AND Sdept <> ‘CS ' ; /*父查询块中的条件 */ 结果:
执行过程: (1)首先处理子查询,找出CS系中所有学生的年龄,构成一个集合(20,19) (2)处理父查询,找所有不是CS系且年龄小于 20 或 19的学生 【1】用聚集函数实现上述示例 SELECT Sname,Sage FROM Student WHERE Sage < (SELECT MAX(Sage) FROM Student WHERE Sdept= 'CS ') AND Sdept <> ' CS '; 【1】查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄。 方法一:用ALL谓词 SELECT Sname,Sage FROM Student WHERE Sage < ALL (SELECT Sage FROM Student WHERE Sdept= ' CS ') AND Sdept <> ' CS ’; 方法二:用聚集函数 SELECT Sname,Sage FROM Student WHERE Sage < (SELECT MIN(Sage) FROM Student WHERE Sdept= ' CS ') AND Sdept <>' CS ';
EXISTS谓词,存在量词 ヨ
NOT EXISTS谓词
【1】查询所有选修了1号课程的学生姓名。 思路分析: 本查询涉及Student和SC关系 在Student中依次取每个元组的Sno值,用此值去检查SC表 若SC中存在这样的元组,其Sno值等于此Student.Sno值,并且其Cno= ‘1’,则取此Student.Sname送入结果表 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= ' 1 '); 【1】查询没有选修1号课程的学生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno='1'); 不同形式的查询间的替换 一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换 所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换 用EXISTS/NOT EXISTS实现全称量词(难点) SQL语言中没有全称量词
(For all) 可以把带有全称量词的谓词转换为等价的带有存在量词的谓词:
【1】查询与“刘晨”在同一个系学习的学生。 可以用带EXISTS谓词的子查询替换: SELECT Sno,Sname,Sdept FROM Student S1 WHERE EXISTS (SELECT * FROM Student S2 WHERE S2.Sdept = S1.Sdept AND S2.Sname = '刘晨'); 【1】查询选修了全部课程的学生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno= Student.Sno AND Cno= Course.Cno ) ); 用EXISTS/NOT EXISTS实现逻辑蕴涵(难点) SQL语言中没有蕴涵逻辑运算 可以利用谓词演算将逻辑蕴涵谓词等价转换为:
【1】查询至少选修了学生201215122选修的全部课程的学生号码。 解题思路: 用逻辑蕴涵表达:查询学号为x的学生,对所有的课程y,只要201215122学生选修了课程y,则x也选修了y。 形式化表示: 用P表示谓词 “学生201215122选修了课程y” 用q表示谓词 “学生x选修了课程y” 则上述查询为:
等价变换:
变换后语义:不存在这样的课程y,学生201215122选修了y,而学生x没有选。 【1】用NOT EXISTS谓词表示: SELECT DISTINCT Sno FROM SC SCX WHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.Sno = ' 201215122 ' AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno));
集合操作的种类
参加集合操作的各查询结果的列数必须相同;对应项的数据类型也必须相同 【1】查询计算机科学系的学生及年龄不大于19岁的学生。 SELECT * FROM Student WHERE Sdept= 'CS' UNION SELECT * FROM Student WHERE Sage<=19; UNION:将多个查询结果合并起来时,系统自动去掉重复元组 UNION ALL:将多个查询结果合并起来时,保留重复元组 【1】查询选修了课程1或者选修了课程2的学生。 SELECT Sno FROM SC WHERE Cno=' 1 ' UNION SELECT Sno FROM SC WHERE Cno= ' 2 '; 【1】查询计算机科学系的学生与年龄不大于19岁的学生的交集。 SELECT * FROM Student WHERE Sdept='CS' INTERSECT SELECT * FROM Student WHERE Sage<=19 【1】实际上就是查询计算机科学系中年龄不大于19岁的学生。 SELECT * FROM Student WHERE Sdept= 'CS' AND Sage<=19; 【1】查询既选修了课程1又选修了课程2的学生。 SELECT Sno FROM SC WHERE Cno=' 1 ' INTERSECT SELECT Sno FROM SC WHERE Cno='2 '; 【1】也可以表示为: SELECT Sno FROM SC WHERE Cno=' 1 ' AND Sno IN (SELECT Sno FROM SC WHERE Cno=' 2 '); 【1】查询计算机科学系的学生与年龄不大于19岁的学生的差集。 SELECT * FROM Student WHERE Sdept='CS' EXCEPT SELECT * FROM Student WHERE Sage <=19; 【1】实际上是查询计算机科学系中年龄大于19岁的学生 SELECT * FROM Student WHERE Sdept= 'CS' AND Sage>19;
子查询不仅可以出现在WHERE子句中,还可以出现在FROM子句中,这时子查询生成的临时派生表成为主查询的查询对象 【1】找出每个学生超过他自己选修课程平均成绩的课程号 SELECT Sno, Cno FROM SC, (SELECTSno, Avg(Grade) FROM SC GROUP BY Sno) AS Avg_sc(avg_sno,avg_grade) WHERE SC.Sno = Avg_sc.avg_sno and SC.Grade >=Avg_sc.avg_grade 如果子查询中没有聚集函数,派生表可以不指定属性列,子查询SELECT子句后面的列名为其缺省属性。 【1】查询所有选修了1号课程的学生姓名,可以用如下查询完成: SELECT Sname FROM Student, (SELECT Sno FROM SC WHERE Cno=' 1 ') AS SC1 WHERE Student.Sno=SC1.Sno;
Select语句的一般形式: SELECT [ALL|DISTINCT] <目标列表达式> [别名] [ ,<目标列表达式> [别名]] … FROM <表名或视图名> [别名] [ ,<表名或视图名> [别名]] … |(<SELECT语句>)[AS]<别名> [WHERE <条件表达式>] [GROUP BY <列名1>[HAVING<条件表达式>]] [ORDER BY <列名2> [ASC|DESC]]; 目标列表达式格式
其中<属性列名表达式>可以是由属性列、作用于属性列的聚集函数和常量的任意算术运算(+,-,*,/)组成的运算公式
两种插入数据方式
1. 插入元组语句格式 INSERT INTO <表名> [(<属性列1>[,<属性列2 >…)] VALUES (<常量1> [,<常量2>]… ); 功能:将新元组插入指定表中 INTO子句
VALUES子句
【1】将一个新学生元组(学号:201215128;姓名:陈冬;性别:男;所在系:IS;年龄:18岁)插入到Student表中。 INSERT INTO Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215128','陈冬','男','IS',18); 【1】插入一条选课记录( '200215128','1 ')。 INSERT INTO SC(Sno,Cno) VALUES ('201215128 ',' 1 '); 关系数据库管理系统将在新插入记录的Grade列上自动地 赋空值。 或者: INSERT INTO SC VALUES (' 201215128 ',' 1 ',NULL); 【1】将学生张成民的信息插入到Student表中。 INSERT INTO Student VALUES ('201215126','张成民','男’,18,'CS'); 2. 插入子查询结果 语句格式 INSERT INTO <表名> [(<属性列1> [,<属性列2>… )] 子查询; SELECT子句目标列必须与INTO子句匹配:值的个数+类型 【1】对每一个系,求学生的平均年龄,并把结果存入数据库 第一步:建表 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; 关系数据库管理系统在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则
语句格式 UPDATE <表名> SET <列名>=<表达式>[,<列名>=<表达式>]… [WHERE <条件>]; 功能
三种修改方式
【1】将学生201215121的年龄改为22岁 UPDATE Student SET Sage=22 WHERE Sno=' 201215121 '; 【1】将所有学生的年龄增加1岁。 UPDATE Student SET Sage= Sage+1; 【1】将计算机科学系全体学生的成绩置零。 UPDATE SC SET Grade=0 WHERE Sno IN (SELETE Sno FROM Student WHERE Sdept= 'CS' );
数据库SQL Server领域就像一片未被勘探的信息大海,引领你勇敢踏入数据科学的神秘领域。这是一场独特的学习冒险,从基本概念到算法实现,逐步揭示更深层次的模式分析、匹配算法和智能模式识别的奥秘。渴望挑战数据库SQL Server中的模式匹配学习路径和掌握信息领域的技术?不妨点击下方链接,一同探讨更多数据科学的奇迹吧。我们推出了引领趋势的💻 数据科学专栏:《数据之谜 | 数据奇迹解码》,旨在深度探索数据库SQL Server中模式匹配技术的实际应用和创新。🌐🔍