SQL server数据查询

背景介绍

首先介绍查询的一般格式：

在本篇博客中，我们使用的是如下数据库。

一、单表无条件查询

查询表中若干列

查询Student表中所有学生的学号，姓名，年龄。

SELECT StuNo,StuName,Age FROM Student;

查Student表所有学生全部内容。

SELECT * FROM Student;

distinct 保留字的使用

查询Student表中所有学生的性别

SELECT Sex FROM Student;

查询Student表中所有学生的性别，取消重复性别结果

SELECT DISTINCT Sex FROM Student;

查询列中含有运算的表达式

查询Student中所有学生的学号，姓名和出生年份，并给出生年份设置别名为“出生年份”

SELECT StuNo AS 学号, StuName AS 姓名, 2020-Age AS 出生年份 FROM Student;

查询列中含有字符串常量

查询course中每门课程的课程名字和学分，并加入一列学分列。

SELECT CName,'学分' AS 学分, Credit FROM Course;

查询列中含有聚合函数

除了count（*）外，其他函数会先去掉空值在计算；在加入distinct后，会将查询结果的列去掉重复值在计算。

统计学生表中的记录数

SELECT COUNT(*) FROM Student;

统计学生表中性别数量

SELECT COUNT(Sex) FROM Student;

统计学生的性别种类数量

SELECT COUNT(DISTINCT Sex) FROM Student;

查询SC表中学生的平均成绩，最高分，最低分

SELECT AVG(score) AS 平均成绩, MAX(score) AS 最高分, MIN(score) AS 最低分 
FROM SC;

返回前部数据语句

查询student表中学生的前两条记录

SELECT TOP 2 * FROM Student;

二、单表有条件查询

使用where字句来实现对表的内容的分割。where字句不能使用聚合函数，如果where字句查询条件为索引字段，则效率会提高。

查询Student表中是上海和北京的学生信息。

SELECT * FROM Student WHERE address="上海" OR address="北京";
--或者
SELECT * FROM Student WHERE address IN ("上海","北京");

从SC表中查询考试成绩在85-90之间的同学信息。

SELECT * FROM SC WHERE Score BETWEEN 85 AND 90;
-- 或
SELECT * FROM SC WHERE Score >85 and Score<90;

查询Student表中姓孙的同学的姓名，学号

SELECT StuNo,StuName FROM Student WHERE StuName like "孙%";

三、分组查询和排序查询

分组查询

group by将一个表按照指定列上的值相等的原则分组，再对每组数据进行规定操作。having通常用于分完组后对对每一组进行条件判断。

查询每一门课程的平均成绩

SELECT Cno,AVG(Score) FROM SC GROUP BY Cno;

查询SC表中至少被两人选择了的课程的平均成绩和最高分

SELECT Cno,AVG(Score),MAX(Score) FROM SC 
GROUP BY Cno HAVING COUNT(*)>=2；

排序查询

order by语句使输出的查询结果按照要求的顺序排列。默认为ASC升序，可以自己设置为降序DESC。

查询Student表中学生信息，按年龄升序，年龄相同按学号降序

SELECT * FROM Student ORDER BY Age, StuNo DESC;

四、多表查询

等值连接查询和非等值连接查询

使用了=作为连接运算符为等值连接查询，使用其他则为非等值连接查询；除此之外，为了防止歧义，属性名称前面应该加上 表名.

查询籍贯为上海的学生的学号，选修的课程号和相应的考试成绩

SELECT Student.StuNo, CNo, Score FROM Student, SC 
WHERE Student.StuNo = SC.StuNo AND Address = "上海"

查询籍贯为上海的血色花那个姓名，选修的课程名称和相应的考试成绩（三个表）

SELECT StuName, CName, Score FROM Student, SC, Course
WHERE Student.StuNo = SC.StuNo AND SC.CNo = Course.CNo AND Address = "上海"；

自身连接

自身连接中，必须为表指定两个别名。

查询籍贯相同的2个学生的基本信息

SELECT A.*,B.StuName FROM Student A,Student B 
WHERE A.Address = B.Address

内连接查询

只有满足查询条件和连接条件的元组才被输出，这样的查询叫做内连接查询，以下为两种不同的代码编写方法，一种使用inner join，一种直接使用等值连接，效果一样。

SELECT Student.StuNo, CNo, Score FROM Student, SC 
WHERE Student.StuNo = SC.StuNo AND Address = "上海"
--
SELECT Student.StuNo, CNo, Score FROM Student INNER JOIN SC 
ON Student.StuNo = SC.StuNo AND Address = "上海"

SELECT StuName, CName, Score FROM Student, SC, Course
WHERE Student.StuNo = SC.StuNo AND SC.CNo = Course.CNo AND Address = "上海"；
--
SELECT StuName, CName, Score FROM Student 
INNER JOIN SC 
ON Student.StuNo = SC.StuNo 
INNER JOIN Course 
ON SC.CNo = Course.CNo AND Address = "上海"；

外连接查询

分为左连接查询和右连接查询。
左连接查询指将在左表中的所有数据分别与右表中的每条数据进行连接组合，返回的结果除内部连接的数据外，还包含左表中不符合条件的数据，并在右表的相应列中添加null值。
右连接同理。

用左链接查询籍贯为“上海”的学生姓名，选修的课程号和相应的考试成绩。

SELECT StuName,CNo,Score FROM Student 
LEFT OUTER JOIN SC ON S.StuNo = SC.StuNo AND address = "上海"

交叉连接

将两个集合作笛卡尔积运算。

用交叉连接进行查询学生的姓名，选修的课程号和相应的考试成绩。

SELECT StuName, CNo, Score FROM Student CROSS JOIN SC

交，并操作

并操作使用UNION,默认不保留重复，若要保留，使用UNION ALL
交操作使用INTERSECT，若要保留重复，使用INTERSECT ALL

查询年龄大于18岁，籍贯是上海的同学信息

SELECT * FROM Student  WHERE Address = "上海"
UNION
SELECT * FROM Student WHERE StuName LIKE "张%"

--同理，等价于下面的代码

SELECT * FROM Student WHERE Address = "上海" OR StuName LIKE "张%"

INTERSECT的使用和这个也是等价的。

五、嵌套查询

SELECT-FROM-WHERE称为一个语句块，将一个查询块嵌套在另一个查询块的where，having子句的条件中，就叫嵌套查询。

返回单值的子查询

查询选修了数据库技术的学生的学号和相应的考试成绩。
以下三种表达方式等价

SELECT StuNo, Score FROM SC 
WHERE CNo = (SELECT CNo FROM Course WHERE CName = "数据库")
--
SELECT StuNo, score FROM SC
WHERE CNo IN (SELECT CNo FROM Course WHERE CName = "数据库")
--
SELECT StuNo, score FROM SC,Course
WHERE SC.CNo = Course.CNo AND CName = "数据库"

查询考试成绩大于总平均分的学号

SELECT Score FROM SC WHERE Score>=(SELECT AVG(Score) FROM SC)

返回多值的子查询

子查询的结果是一个集合的时候，需要使用下面的这些谓词来进行连接。

查询成绩至少比选修了“050218”号课程的一个学生成绩低的学生学号。

SELECT StuNo FROM SC 
WHERE CNo != "050218" 
AND 
Score < ANY(SELECT Score FROM SC WHERE CNo = "050218")
--

SELECT StuNo FROM SC 
WHERE CNo != "050218" 
AND 
Score < (SELECT MAX(Score) FROM SC WHERE CNo = "050218") 

查询其他专业中比S009号专业所有学生年龄都要小的学生名单

SELECT * FROM Student 
WHERE MajorNo != "S009" 
AND 
Age > ALL (SELECT Age FROM Student WHERE MajorNo = 'S009')
--

SELECT * FROM Student
WHERE MajorNo != "S009"
AND
AGE> (SELECT MAX(AGE) FROM Student WHERE MajorNo = "S009")

相关子查询

相关子查询一般会使用exists谓词，exists只返回真假，子查询结果不为空时，返回真。

查询选修了技术库的同学

SELECT StuNo FROM SC 
WHERE EXISTS (SELECT * FROM Course WHERE SC.CNo = Course.CNo AND CName = "数据库")