SQL Server 2016学习记录 --- 单表查询

学习目标：

SELECT—单表查询

---

学习内容：

--查询所有的学生的学号和姓名
select Sno,Sname from student

--查询所有的学生的学号、姓名和专业
select Sno,Sname,Sdept from student

--查询所有的学生的所有信息
select * from student

--查询所有学生的姓名和出生年份
select Sname,2021-Sage from student
select Sname,year(getdate())-Sage from student
select Sname,2021-Sage as 'birth year' from student

--查询所有学生的姓名、出生年份和专业
--要求用小写字母表示所有专业
SELECT Sname,'Year of Birth:',2021-Sage,LOWER(Sdept) FROM student
--使用列别名改变查询结果列标题
SELECT Sname NAME,'Year of Birth:'BIRTH,
       2021-Sage BIRTHDAY,
	   LOWER(Sdept) DEPARTMENT
	   FROM student;

运行结果：

• 选择表中的若干元组

消除取值重复的行

--查询选修课程的学生学号
SELECT Sno FROM SC ;--只会查全部，不会去重
SELECT DISTINCT Sno FROM SC;

查询满足条件的元组

--查询所有年龄在20岁以下的学生姓名及年龄
select sname,sage from student where sage<20;
--查询年龄在20-30之间（包括20和30）的学生姓名、专业和年龄
select sname,sdept,sage from student where sage >=20 and sage<=30;
select sname,sdept,sage from student where sage between 20 and 30;
--查询年龄不在20-30之间的学生姓名、专业和年龄
select sname,sdept,sage from student where sage not between 20 and 30;
select sname,sdept,sage from student where sage <20 or sage>30;

确定集合

--查询信息系、数学系和计算机科学系的学生姓名和性别
select sname,ssex from student where sdept in('IS','MA','CS')
--查询既不是信息系，又不是数学系的学生姓名和性别
select sname,ssex from student where sdept not in('IS','MA')

字符串匹配

--查询姓刘的学生的姓名、学号、性别
select sname,sno,ssex from student where sname like '刘%';
--通配符a%b，表以a开头，以b结尾的
--查询不姓刘的学生的姓名、学号、性别
select sname,sno,ssex from student where sname not like '刘%';
--查询姓张且全名为2个字的学生姓名
select sname from student where sname like '张_';
--查询名字中第二个字为丽的学生姓名和学号
select sname,sno from student where sname like '_丽%';
--查询DB_Design课程的课程号的学分
--select cno,ccredit from course where cname like 'DB_Design';--会将'_'看做通配符
select cno,ccredit from course where cname like 'DB\_Design' escape '\';
--使用转义字符\将通配符转换为普通字符

涉及空值的查询

--涉及空值的查询
--查询没成绩的学生的学号和课程号
select sno,cno from sc where grade is null;
--查询所以有成绩的学生的学号和课程号
select sno,cno from sc where grade is not null;

对查询结果进行排序

--对查询结果进行排序
--使用order by ；升序asc，降序desc，缺省值为升序
--当排序列含空值时，asc排序列为空值的元组最后显示
--                  desc排序列为空值的元组最先显示
--查询选修了3号课程的学生学号及成绩，查询结果按分数降序排列
select sno,grade from sc where cno=3 order by grade desc;
--查询全体学生，查询结果按专业生序排列，同一专业的学生按年龄降序排列
select * from student order by sdept,sage desc;

使用集函数
5类主要集函数：

• 统计元组个数 count（*）
  统计一列中值的个数 count（distinct or all 列名）
  计算列综合 sum（列名）
  计算列平均值 avg（列名）
  求列最大值 max（列名）
  统计时不统计NULL值

--查询学生总人数
select count(*) from student;
select count(sno) from student;
--查询选修了课程的学生人数
select count(distinct sno) from sc;
--使用distinct可以避免重复计算学生人数

--计算1号课程的学生的平均成绩
select avg(grade) from sc where cno=1;
--查询选修1号课程的学生的最高分数
select max(grade) from sc where cno=1;

对查询的结果分组

--使用group by，按制订的一列或多列值分组，值相等的为一组
--求各个课程号及相应的选修人数
select cno,count(sno) from sc group by cno;
--求各个课程及相应的选修人数及最高分
select cno,count(sno),max(grade) from sc group by cno;
--查询每个系的学生人数
select sdept,count(distinct sno) from student group by sdept;
--查询选修了2门以上课程的学生学号
SELECT Sno FROM  SC GROUP BY Sno
     HAVING  COUNT(*) >2 ;

select cno,sno,count(sno),max(grade) from sc group by cno;
--不行，因为select后面只能包含group by后面跟的列名，或者直接是集函数，不能有其他列

--求各个课程号及相应课程成绩在90分以上的学生
select cno,count(sno) from sc where grade>=90 group by cno;
--使用having短语筛选最终输出结果
--只有满足having短语指定条件的组才输出
--查询选修了3门以上的课程的学生的学号
select sno from sc group by sno having count(*)>=3;
--查询有3门以上课程在75分以上的学生的学号及75分以上的课程数
select sno,count(*) from sc where grade>=75 group by sno having count(*)>=3;
--统计每门课程的最高分
select cno,max(grade) from sc group by cno;

having和where的区别：
–1.having是对分组以后的结果进行筛选
–2.where是对from之后的对象进行筛选
–3.where后面跟不了集函数
–4.having后面可以跟where条件

---

截取字符串的函数