查询与统计数据、创建视图与索引

select语句的语法

1、select语法简介

• 数据查询是数据库管理系统中的一个最重要的功能。数据查询不是简单的返回数据库中存储的数据，而是根据用户的不同需求对数据进行筛选、统计，并且以用户需要的格式返回。
• SQL Server提供了强大、灵活的Select语句，用于实现用户的各种数据查询操作。

2、select语法结构

Select <检索内容> [As<别名>] [Into<目标表名>]
From <源表名>
[Where<检索条件>]
[Group by<分类字段>][Having<检索条件>]
[Order by<排序字段>[Asc|Desc]]

Select：指定要查询的数据列
Into：创建新表并将查询的结果存到新表当中
From：指定要查询的数据源，可以是表，也可以是视图
Where：指定查询条件
Group by：对查询结果进行分组
Having：与Group by子句结合使用，指定分组筛选条件
Order by：对查询结果排序，默认为升序（ASC），降序（Desc）

3、select执行过程

• 每个select语句必须有一个from子句，其他字句的顺序非常重要，依次是：where、group by、having、order by。可以省略可选子句，但若使用必须按适当的顺序出现。
  

4、select书写格式

• 不区分大小写
• 可以随意分行
• 没有语句结束标志

数据库的简单查询

• 单表查询是指要检索的信息来自数据库中的某一张表，是最简单的一种查询操作。

1、查询表中的数据列

查询所有列：

• 最简单的select语句是查询表中的所有数据，只需要使用select、from两个关键字即可。
• 要把表中的所有列以及列数据展示出来，可以先使用符号“*”，它表示所有列。

格式：
select * from <表名>
功能：
使用select输出表中的所有列及数据

• 实例1：在SCC数据库中，查询student表中的所有学生的详细信息

Use SCC
select * from studnet

查询表中指定列：

• 如果用户只需要获取部分列的数据，只需要将select语句中的“*”换成所需字段的字段列表即可。
• 若将表中所有列都放在这个列表中，将查询整张表的数据。

格式：
select <字段列表> from <表名>
功能：
使用select输出表中指定列及列数据

• 实例2： 查询student表中所有学生的学号和姓名

select sno,sname from student

为结果列使用别名：

• 查询结果中默认输出列的列标题就是表的列名，输出表达式的列标题为无列名。
• 如果用户觉得该列名不能表达清楚，可对列名设置别名。
• 设置别名只是设置显示查询结果时的列名，而表中的列名未改变。
• 实例3： 查询student表中所有学生的学号和姓名，将显示结果加中文别名

格式1：
select sno 学号,sname 姓名 from student
格式2：
select sno as 学号,sname as 姓名 from student
格式3：
select 学号=sno,姓名=sname from student

2、查询满足条件的数据行

• 若需要查询表中满足条件的数据而不是全部数据，需要在select语句中通过where子句实现。

格式：
select <字段列表> from <表名> where <条件表达式>
功能：
使用select输出表中满足条件的列及列数据

• 实例4：查询student表中所有男生的详细信息

select * from student where sex='男'

• 实例5：查询score表中有不及格现象的学生信息

select * from score where uscore<60 or endscore<60

• 实例6：查询course表中数据库类课程的信息，结果显示为课程编号、课程名称、任课教师

select cno,cname,teacher from course where cname like '%数据库%'

• 实例7：在score表中查询期末有缺考现象的学生信息

select * from score where endscore is null

数据库的分组统计查询

1、使用聚合函数查询

• 在数据库查询时，经常需要对查询结果进行计算或分类汇总，比如，统计人数、计算总分、平均分等，可以利用SQL提供的聚集函数来完成这类查询操作。
• 常见的聚集函数如下：
  
• 实例1：查询student表中学生的总人数

select count(*) from student

【说明：】使用聚集函数列的列标题为默认为“无列名”，为了说明问题，通常需要使用别名。

• 实例2：查询student表中学生的总人数

select count(*) 总人数 from student

• 实例3：查询score表中课程c001的期末成绩、平均成绩、最高成绩和最低成绩。

select sum(endscore),avg(endscore),max(endscore),min(endscore) 
from score where cno='c001'

2、使用分组查询

• 若要对查询结果进行分类汇总时，需要使用group by子句。
• Group by子句将查询结果按某列（或多列）的值分组，值相等的为一组。
• 若未对查询结果分组，聚集函数将作用于整个查询结果；分组后聚集函数将作用于每一个组。
• Having子句用于对分组结果进行筛选，通常在Group by子句中使用。

格式：
select <字段列表> form <表名>
[where <查询条件>]
group by <分类字段> [having <检索条件>]

• 实例4：查询student表中男、女生人数，要求使用中文别名

select sex 性别,count(*) 人数 from student group by sex

• 实例5：在score表中，查询期末成绩在80分以上的学生的学号和平均成绩

select sno,avg(endscore) from score
group by sno having avg(endscore) >= 80

3、查询结果排序

• 使用select查询获取的数据通常是无序的，可使用Order by子句对查询结果集排序。
• Order by子句可将查询结果按一个或多个列值的大小顺序输出

格式：
select <字段列表> from <表名> [where<查询条件>]
[group by <分类字段> [having <检索条件>]]
order by <排序字段> [Asc|Desc]
功能：
使用order by 子句对查询结果集的相应列进行排序。Asc关键字表示升序，Desc关键字表示降序。默认情况为Asc。

• 实例6：在score表中，查询每门课的平均成绩，并按照平均成绩降序排列

select cno,avg(endscore) from score 
group by cno order by avg(endscore) desc

数据库的内连接查询

• 数据查询时，用户需要查询的数据有时并不都在一个数据表中，可能涉及到一个以上的表，这是就需要用到多表查询。
• 多表查询是将多个表连接在一起的查询，也称为连接查询。
• 根据查询的需要，连接查询分为内连接和外连接。

1、内连接查询简介

• 内连接查询时将表中的一些记录按照一定的条件与其他表的一些记录连接起来，连接条件通常是表间列的相等关系。
• 在内连接查询中，只有满足条件的元组才能出现在结果集中。
• 内连接是多表查询的主要方式。

格式1：
select <列表字段> from <表名1>,<表名2>
where <表名1.列名=表名2.列名> [And 条件表达式]

【说明：】
1、from子句指明进行连接的表名，表明之间实用逗号隔开
2、where子句指明连接的列名及其连接条件，列名若在表1和表2中都包含，则必须使用“表名.列名”的形式，以示区别。
3、连接条件即为个表之间的关联，其形式为“表名1.主键=表名2.外键”
4、如果是有条件的连接查询，则将条件表达式放在连接条件的后面，使用And关键字即可。

格式2：
select <列表字段> from <表名1> Inner Join <表名2> on
<表名1.列名=表名2.列名> 
[Where 条件表达式]

【说明：】
Inner Join称为内连接，关键字ON与之对应，以表明连接的条件。

2、内连接查询操作

• 实例1：查询所有学生的学号、姓名、课程编号、平时成绩和期末成绩

select student.sno,sname,cno,uscore,endscore 
from studet,score
where student.sno=score.sno
或者
select studnet.sno,sname,uscore,endscore
from student inner join score
on student.sno=score.sno

• 实例2：查询所有男生的学号、姓名、课程编号、平时成绩、期末成绩

select student.sno,sname,cno,uscore,endscore 
from studet,score
where student.sno=score.sno and sex='男'
或者
select studnet.sno,sname,uscore,endscore
from student inner join score
on student.sno=score.sno
where sex='男'

• 实例3：查询所有学生的学号、姓名、课程名、课程成绩

select student.sno,sname,cname,uscore,endscore 
from studet,course,score
where student.sno=score.sno and course.cno=score.cno
或者
select student.sno,sname,cname,uscore,endscore 
from student inner join score
on student.sno=score.sno
inner join course on course.cno=score.cno

• 实例4：查询每门课的课程号、课程名及选课人数

select score.cno,cname,count(*) as num
from course,score
where course.cno=score.cno
group by score.cno,cname

数据库的外连接查询

• 外连接查询通常用于相连接的表中至少有一个表需要显示所有数据行的情况。
• 外连接的结果集中不但包含满足连接条件的记录，还包括相应表中的所有记录，即某些记录即使不满足连接条件，但仍需要输出。
• 外连接主要分为左外连接、右外连接和全外连接。

1、左外连接查询

格式：
select <列表字段> from <表名1> Left [Outer] Join <表名2>
on <表名1.列名=表名2.列名>

【说明：】
1、左外连接的结果集中包括了左表的所有记录，而不仅是满足连接条件的记录，即将位于Left Join关键字左侧表的所有行都输出。
2、如果右表的某条记录在左表中没有匹配行，此时右表的数据行显示为NULL

• 实例1：查询所有学生的选修课程情况，结果显示为学号、姓名、课程编号和课程成绩

1、使用内连接，只显示有选课记录的学生信息
select student.sno,sname,score.cno,uscore,edscore
from student inner join score on student.sno=score.sno
2、使用左外连接，显示所有学生的信息，没有选课的学生，其成绩列显示为NULL
select student.sno,sname,score.cno,uscore,endscore
from student left join score on studnet.sno=score.sno 

2、右外连接查询

格式：
select <列表字段> from <表名1> Right[Outer] Join <表名2>
on <表名1.列名=表名2.列名>

【说明：】
1、右外连接的结果集中将位于Right Join关键字右侧表的所有行都输出
2、如果左表的某条记录在右表中没有匹配行，此时左表的数据行显示为NULL

• 实例2：查询所有学生的选修课程情况，结果显示为课程号、课程名和课程成绩

1、使用内连接，只显示有选课记录的学生信息
select course.cno,cname,uscore,endscore
from score inner join course on score.cno=course.cno
2、使用右外连接，显示所有学生的信息，没有选课的学生，其成绩列显示为NULL
select course.cno,cname,uscore,endscore
from student right join course on score.sno=course.cno

3、全外连接查询

格式：
select <列表字段> from <表名1> Full Join <表名2>
on <表名1.列名=表名2.列名>

【说明：】
1、全外连接的结果集中包括了左表和右表的所有记录
2、当某条记录在另一表中没有匹配记录时，则该表的相应列值为NULL

• 实例3：将课程表course和成绩表score进行内连接和全外连接比较

1、使用内连接，只显示有选课记录的学生信息，为便于查看，使用了order by子句
select course*,score.* 
from course inner join score on score.cno=course.cno order by cname
2、使用全外连接，显示两个表中的所有记录行，没有学生选课的课程，相应列显示为NULL
select course*,score.* 
from course full join score on score.sno=course.cno order by cname

子查询

• SQL子查询又称为嵌套查询，是指在一个嘻哈寻语句中又包含另一个查询的情况，其一般格式如图。子查询块必须是单表查询，然后通过相同的字段与其他查询进行联系。

select <目标列> from <单个目标表或视图>
where <字段> in (
select <目标列> from <单个目标表或视图>
where <条件>
)

1、数据库子查询的基本形式

• 带in谓词的子查询
• 带Any或All谓词的子查询
• 带exists谓词的子查询

2、数据库的子查询

• 在嵌套查询中，子查询的结果往往是一个集合，in子查询用于判断一个给定值是否在子查询结果集中，其语法格式为：

父查询 [NOT] IN (子查询)

• 例题1：查询选修了3门及以上的课的学生的姓名

select sname from student where sno in (
	select sno from score group by sno having count(*)>=3
)

• 例题2：查询选修“数据库应用”课程的学生的姓名

select sname from student where sno in(
	select sno form score where cno in(
		select cno from course where cname='数据库应用'
	)
)

视图

1、视图概述

• 视图是有基于一个或多个表（或视图）上的一个查询所生成的虚拟表，其中不包含任何数据，只是保存着该查询的定义。对视图的操作与对表的操作一样，可以对其进行查询、修改（有一定的限制）和删除。
• 作用：简化用户操作，使用户关注自己感兴趣的数据
• 优点：1、方便用户使用；2、提供一定的数据安全性

2、创建视图

• 创建视图的基本语法结构如图所示：
  
• 视图名后面的属性列可全部省略，隐含该视图子查询中select子句目标列中的字段组成。
• 下面情况属性列不能够省略：
• 1、子查询目标列you聚集函数或列表达式
• 2、子查询为多表查询，并且选出来几个同名列作为视图的字段
• 3、需要在视图中为某个列使用别名
• 例题：创建性别为“女生”的学生视图，并要求对试图进行更新时保证该视图只有性别为“女生”的学生。

create view is_student as
select * from Student where sex='女' with check option

索引

1、索引概述

• 索引是对数据库表中的一个或多个列的值进行排序的结构，索引是依赖于表建立的，它提供了数据库中编排表中数据的内部方法。
• 索引的优缺点：
  优点：
• 1、索引能提高查询表内数据的速度
• 2、能加快表与表之间的连接速度
  缺点：
• 1、创建和维护索引增加了额外的时间开销
• 2、索引会占用一定的存储空间，占用空间过大会影响数据库的性能
• 索引的分类：
  根据索引页的顺序与数据页中行的物理存储顺序是否相同，可以把索引分为聚集索引和非聚集索引

2、创建索引

• 创建索引的基本原则：

• 1、定义有主键或外键的数据列一定要建立索引
• 2、对于经常查询的数据列最好建立索引
• 3、对于需要在指定范围内快速或频繁查询的数据列最好建立索引
• 4、经常使用在where子句中的数据列最好建立索引

• 创建索引的基本语法格式为：
  
• 例题：利用SQL语句，在student表中创建一个基于姓名的简单非聚集索引，设索引名为stu_name

create index stu_name on student(sname)

3、修改索引

• 例题：利用资源管理器查看、修改、删除创建的stu_name索引：