SQL基础之数据的查询

最新推荐文章于 2024-07-13 18:10:19 发布

weixin_30486037

最新推荐文章于 2024-07-13 18:10:19 发布

阅读量228

点赞数 1

文章标签：数据库

原文链接：http://www.cnblogs.com/chenloveslife/p/9001822.html

版权

一、数据查询的一般格式：

　　SELECT [ALL|DISTINCT] <目标列表达式>[，<目标列表达式>] …
　　[INTO 新表名]
　　FROM <表名或视图名>[， <表名或视图名> ] …
　　[ WHERE <条件表达式> ]
　　[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
　　[ ORDER BY <列名2> [ ASC|DESC ] ]；

二、单表查询

　　1. 查询指定列与计算的值

　　　　(1) 查询指定列：只需在SELECT子句中加入要查询指定列字段即可，有时会根据题意要求去从，那么得在字段前添加DISTINCT关键字。

SELECT DISTINCT  Sno
FROM SC；

　　　　(2) 查询经过计算的值：在SELECT子句中的目标列表达式可以为如下：

　算术表达式
字符串常量
函数
列的别名

SELECT Sname，2009-Sage  --查询算术表达式结果
FROM Student；

SELECT Sname,'Year of Birth:'    --查询字符串常量
FROM Student;

SELECT Sname,LOWER(Sdept)    --查询经过函数处理的字段信息结果
FROM Student;

--查询结果为列的信息，不过视图上列名为别名
SELECT Sname AS NAME，’Year of Birth: ’ AS BIRTH    
FROM Student；

　　2. 查询满足条件的元组

　　　　(1) 比较大小

　　　　查询谓词：=，>，<，>=，<=，!=，<>，!>，!<；NOT+上述比较运算符

SELECT COUNT(Jno) AS '项目总数' FROM SPJ WHERE Sno = 'S1';

　　　　(2) 确认范围：

　　　　查询谓词：BETWEEN AND，NOT BETWEEN AND

SELECT Sname，Sdept，Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23；

　　　　(3) 确认集合：

　　　　查询谓词：IN , NOT IN

SELECT Sname，Ssex
FROM  Student
WHERE Sdept IN ( 'IS'，'MA'，'CS' );

　　　　(4) 字符匹配

　　　　查询谓词：[NOT] LIKE ‘<匹配串>’ [ESCAPE ‘ <换码字符>’]

　　　　匹配串为含通配符的字符串
　　　　%：代表任意长度（长度可以为0）的字符串
　　　　_：代表单个字符

--查询所有姓刘学生的姓名、学号和性别。
SELECT Sname，Sno，Ssex
FROM Student
WHERE  Sname LIKE '刘%'；
--查询姓"欧阳"且全名为三个汉字的学生的姓名。
SELECT Sname
FROM   Student
WHERE  Sname LIKE '欧阳_'；

　　　　使用换码字符ESCAPE将通配符转义为普通字符

--查询以"DB_"开头，且倒数第3个字符为 i的课程的详细情况。
SELECT  *
FROM   Course
WHERE  Cname LIKE  'DB\_%i_ _' 
ESCAPE ' \ ‘；

　　　　(5) 空值查询

　　　　查询谓词：IS NULL 或 IS NOT NULL

SELECT Sno，Cno
FROM  SC
WHERE  Grade IS NULL；

　　　　(6) 多重条件查询

　　　　查询谓词：AND和OR来联结多个查询条件，其中查询条件又可包含上述的查询条件。

SELECT Sname
FROM  Student
WHERE Sdept= 'CS' AND Sage<20；

　　3. ORDER BY ... DESC/ASC (缺省值为升序)

　　　　表示对查询结果的升降序显示，ASC为升序，DESC为降序。

SELECT Sno，Grade
FROM  SC
WHERE  Cno= ' 3 '
ORDER BY Grade DESC；

　　　　注意：

　　　　　　当排序列含空值时，ASC：排序列为空值的元组最后显示，DESC：排序列为空值的元组最先显示。

　　　　　　ORDER BY 不能在子查询中使用，由于ORDER BY是对查询结果的排序。

　　4. GROUP BY ... HAVING ...　　　　

　　　　其作用对象是查询的中间结果表，最终结果表为按指定条件对查询结果的分组结果。

　　　　GROUP BY后为分组依据(字段名)，被指定的一列或多列值分组，值相等的为一组；HAVING后多为聚合函数满足条件，聚合函数将会分别作用于前面根据分组依据分完组的每个组。

/*查询供应了3个以上项目的供应商代码和供应的项目总数。*/
SELECT Sno, COUNT(DISTINCT Jno) AS '项目总数' 
FROM SPJ 
GROUP BY Sno 
HAVING COUNT(DISTINCT Jno) > 3;

　　　　注意：

　　　　　使用Group By子句后,Select子句的字段列表达式只能是分组依据和聚集函数。

　　　　HAVING短语与WHERE子句的主要区别是作用对象不同，其中WHERE子句作用于基表或视图，从中选择满足条件的元组，而HAVING短语作用于组，从中选择满足条件的组。

　　5. 聚合函数

SELECT COUNT(Jno) AS '项目总数' 
FROM SPJ 
WHERE Sno = 'S1';

　　注意：只能在SELECT子句中或HAVING后使用。

三、连接查询

　　连接查询为多表查询，即把要查询的记录在两个表中，那么就要连接两表来查询。其中包含连接谓词：用来连接两个表的条件，必须要求连接字段数据类型是可比的，

　　例如:SELECT SUM(Ccredit) FROM SC， Course WHERE Sno='200215122' AND SC.Cno=Course.Cno。

　　3种类型的连接查询执行过程：

　　　　嵌套循环法(NESTED-LOOP)

　　　　排序合并法(SORT-MERGE)

　　　　常用于=连接索引连接(INDEX-JOIN)

　　1. 等值与非等值查询

　　等值连接运算符：=

　　非等值连接运算符：!=

SELECT  *
FROM     Student,SC
WHERE  Student.Sno = SC.Sno；
SELECT  *
FROM     Student,SC
WHERE  Student.Sno ！= SC.Sno；

　　自然连接则为在等值连接的基础上消除重复的列

SELECT  Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM     Student,SC
WHERE  Student.Sno = SC.Sno；
SELECT  Student.Sno,sc.sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM     Student,SC
WHERE  Student.Sno ！= SC.Sno；

　　2. 自身连接

　　一个表和其自身表连接，注意必须给表起别名加以区分不同表的属性，否则sql引擎识别不出你要指定的属性，会报错。

--查询每门课程的有先修课程的课程名
SELECT  FIRST.Cno，SECOND.Cpno   
FROM  Course  FIRST，Course  SECOND  
WHERE FIRST.Cpno = SECOND.Cno；

　　3. 外连接

　　这里根据内连接与外连接的区别来讲解外连接：

　　(1) 内联接：（典型的联接运算，使用像 = 或 <> 之类的比较运算符）。包括相等联接和自然联接。
　　　　内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 students和courses表中学生标识号相同的所有行。
　　(2) 外联接：外联接可以是左向外联接、右向外联接或完整外部联接。
　　　　在 FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：
　　　　1）LEFT JOIN或LEFT OUTER JOIN
　　　　左向外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。
　　　　2）RIGHT JOIN 或 RIGHT OUTER JOIN
　　　　右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。
　　　　3）FULL JOIN 或 FULL OUTER JOIN
　　　　完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

/*查询每个学生及其选修课程的情况*/
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM  Student  INNER JOIN SC
ON   Student.Sno=SC.Sno； 
--结果集合种不包括未选修课的学生


/*查询所有学生的选修课程的情况*/
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM  Student LEFT OUTER  JOIN SC    
ON Student.Sno=SC.Sno；
--结果集种没有选课的学生也包含在内，Cno和Grade字段显示为null

　　4. 多表查询

/*查询每个学生的学号、姓名、选修的课程名及成绩*/
SELECT Student.Sno,Sname,Cname,Grade
FROM  Student，SC，Course 
WHERE Student.Sno = SC.Sno 
AND SC.Cno = Course.Cno；

四、嵌套查询

　　将一个查询块嵌套在另外一个查询块的WHERE子句或HAVING子句中的查询称为嵌套查询，又称子查询。

　　分类：

　　　　不相关子查询：子查询的查询条件不依赖于父查询。由里向外逐层处理，即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。

　　　　相关子查询：子查询的查询条件依赖于父查询。首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表然后再取外层表的下一个元组重复这一过程，直至外层表全部检查完为止

　　1. 派生表：即临时表，起到中转的作用。

/*创建一个学生成绩统计视图，包括学号，姓名，选课门数，平均分，最高分，最低分*/
CREATE VIEW StuGradeSta(Sno, Sname, CourseNum, CourseAvg, CourseMax, CourseMin)
AS 
SELECT tt.SSno, yy.Sname, tt.CourseNum, tt.CourseAvg, tt.CourseMax, tt.CourseMin
FROM
   (SELECT Student.Sno SSno, COUNT(Cno) CourseNum, AVG(Grade) CourseAvg, MAX(Grade) CourseMax, MIN(Grade) CourseMin
    FROM Student, SC
    WHERE Student.Sno = SC.Sno
    GROUP BY Student.Sno)
    AS tt, Student AS yy
    WHERE tt.SSno = yy.Sno;

　　2. 谓词为IN的子查询

/*查询选修了课程名为“信息系统”的学生学号和姓名*/
SELECT Sno，Sname     
FROM    Student     
WHERE Sno  IN
            (SELECT Sno
              FROM    SC
              WHERE  Cno IN
                          (SELECT Cno
                           FROM Course
                           WHERE Cname= ‘信息系统’
                           )
             );

　　过程：首先在Course关系中找出“信息系统”的课程号，为3号。然后在SC关系中找出选修了3号课程的学生学号。最后在Student关系中取出Sno和Sname。总的来说，由内至外

　　3. 谓词为比较运算符的子查询

　　前提为子查询内层必须返回单值时，才可以用带比较运算符的子查询，常和ANY或ALL谓词配合使用。

/*假设一个学生只可能在一个系学习，并且必须属于一个系*/
SELECT Sno，Sname，Sdept
FROM    Student
WHERE Sdept   =
           (SELECT Sdept
            FROM    Student
            WHERE Sname= ‘刘晨’)；
/*找出每个学生超过他选修课程平均成绩的课程号。以下为相关子查询，*/
SELECT Sno， Cno
FROM  SC  x
WHERE Grade >=
      (SELECT AVG(Grade) 
       FROM SC，y
       WHERE y.Sno=x.Sno);

　　4. 谓词为ALL或ANY的子查询

　　　　(1) 用于子查询中返回多值时

　　　　　　ANY: 结果中某一个值　　ALL：所有值

　　　　(2) 配合比较运算符使用

　　　　> ANY 大于子查询结果中的某个值
　　　　< ANY 小于子查询结果中的某个值
　　　　>= ANY 大于等于子查询结果中的某个值
　　　　<= ANY 小于等于子查询结果中的某个值
　　　　= ANY 等于子查询结果中的某个值
　　　　!=（或<>）ANY 不等于子查询结果中的某个值
　　　　> ALL 大于子查询结果中的所有值
　　　　< ALL 小于子查询结果中的所有值
　　　　>= ALL 大于等于子查询结果中的所有值
　　　　<= ALL 小于等于子查询结果中的所有值
　　　　=ALL 等于子查询结果中的所有值（通常没有实际意义）
　　　　!=（或<>）ALL 不等于子查询结果中的任何一个值

/*查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。*/
/*方法一：用ALL谓词*/
SELECT Sname,Sage
FROM Student
WHERE Sage < ALL
           (SELECT Sage
            FROM Student
            WHERE Sdept= ' CS ')
            AND Sdept <> ' CS ’;

/*方法二：用聚合函数，ALL,ANY常常和MAX()，MIN()可以相互转换*/
SELECT Sname,Sage
FROM Student
WHERE Sage < 
          (SELECT MIN(Sage)
           FROM Student
           WHERE Sdept= ' CS ')
           AND Sdept <>' CS ’;

　　5. 谓词为EXISTS和NOT EXISTS的子查询(相关子查询)

　　　　带有EXISTS或NOT EXISTS谓词的子查询不返回任何数据，只返回逻辑真值或逻辑假值。易知此子查询为相关子查询，故执行过程为将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE 或 FALSE）来决定主查询的数据结果是否得以保留或输出。

　　　　(1) EXISTS谓词（存在量词）

　　　　　　若内层查询结果非空，则外层的WHERE子句返回真值；若内层查询结果为空，则外层的WHERE子句返回假值。

/*查询所有选修了一号课程的学生姓名，也可以用连接实现*/
SELECT Sname
FROM Student
WHERE EXISTS
       (SELECT * FROM SC
        WHERE Sno = Student.Sno 
            AND Cno =‘1’);

　　　　　　执行过程分析：外层执行过程：首先取外层查询中（student）表的第一个元组，根据它与内层查询相关的属性值（Sno值）处理内层查询，若外层的WHERE返回为真，则取外层查询中该元组的Sname放入结果表，然后外层循环继续下一组；若结果为假，则外层循环继续下一组。内层执行过程：在接受外层查询传进来的数据后，如果内层查询到满足条件元组则终止内层循环，EXISTS返回真值；如果内层查询不到任何满足条件的元组则终止内层循环，EXISTS返回假值。

　　　　(2) NOT EXISTS谓词

　　　　　　若内层查询结果非空，则外层的WHERE子句返回假值。若内层查询结果为空，则外层的WHERE子句返回真值

/*查询没有选修一号课程的学生姓名*/
SELECT Sname
FROM Student
WHERE NOT EXISTS
       (SELECT *
        FROM SC
        WHERE Sno = Student.Sno 
            AND Cno = ‘1’);

　　　　　　执行过程：外层执行过程：首先取外层查询中（student）表的第一个元组，根据它与内层查询相关的属性值（Sno值）处理内层查询，若外层的WHERE返回为真，则取外层查询中该元组的Sname放入结果表，然后外层循环继续下一组；若结果为假，则外层循环继续下一组。内层执行过程：在接受外层查询传进来的数据后，如果内层查询到满足条件元组则终止内层循环，NOT EXISTS返回假值；如果内层查询不到任何满足条件的元组则终止内层循环，NOT EXISTS返回假值。

/*以下连接查询不能代替NOT EXISTS*/
SELECT Sname
FROM Student，SC
WHERE SC.Sno = Student.Sno 
      AND Cno != ‘1’);

　　　　(3) NOT EXISTS实现全称量词

/*查询选修了全部课程的学生姓名
（可理解为： 查询没有一门课程不选修的学生姓名）
*/
SELECT Sname FROM Student
WHERE NOT EXISTS
        (SELECT * FROM Course
         WHERE NOT EXISTS
            (SELECT * FROM SC
            WHERE Sno = Student.Sno 
                         AND Cno = Course.Cno));

　　　　执行过程：

　　　　这个sql语句可以分为三个部分：最外层查询，中间层查询，最内层查询。由内而外进行分析：　　　　

最外层的student表中的第一条记录是Altair同学对应的记录，然后中间层的course表的第一条记录是数据库对应的记录，然后对该数据进行判断（最内层的WHERE语句），结果返回真，则内层的NOT EXISTS为假，然后继续对course表中的下一条记录进行判断，返现NOT EXISTS的值也为假，直到遍历完course表中的所有的数据，内层的NOT EXISTS的值一直都是假，所以中间层的WHERE语句的值也一直都是假。对应student的Altair记录，course表中的所有的记录对应的中间层的返回值为假，所以最外层的NOT EXISTS对应的值为真，最外层的WHERE的值也为真，则Altair对应的记录符合查询条件，装入结果表中。然后继续对student表中的下一条记录进行判断，直达student表中的所有数据都遍历完毕。

　　　　(4) NOT EXISTS实现蕴涵

/*查询至少选修了学生95002号选修的全部课程的学生学号*/
SELECT DISTINCT Sno  FROM SC SCX
WHERE NOT EXISTS
        (SELECT *  FROM SC SCY
         WHERE SCY.Sno = ‘95002’  
            AND 
                 NOT EXISTS
                 (SELECT * FROM SC SCZ
                 WHERE SCZ.Sno = SCX.Sno 
                     AND SCZ.Cno = SCY.Cno));

　　　　例题相关数据：

五、集合查询

　　省略。

附上xmind：

sql数据来自于：https://blog.csdn.net/qq_27571221/article/details/53090467

转载于:https://www.cnblogs.com/chenloveslife/p/9001822.html

weixin_30486037

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
SQL基础之数据的查询

一、数据查询的一般格式：　　SELECT [ALL|DISTINCT] <目标列表达式>[，<目标列表达式>] …　　[INTO 新表名]　　FROM <表名或视图名>[， <表名或视图名> ] …　　[ WHERE <条件表达式> ]　　[ GROUP BY <列名1> [ HAVING <条件表达式> ] ...
复制链接

扫一扫