实验2准备SQL查询(上课讲解)
(教材该部分内容:P79-80,102-105)
1、SQL的数据查询功能
使用数据库和表的主要目的是存储数据,以便在需要时进行检索、统计或组织输出,通过T-SQL的查询可以从表或视图中迅速、方便地检索数据。SQL的SELECT语句可以实现对表的选择、投影及连接操作,其功能十分强大。
2、SQL查询命令格式
SQL的核心是查询,SQL的查询命令也称作SELECT命令,它的基本形式由SELECT—FROM--WHERE查询块组成,多个查询块可以嵌套执行。
3.几种常用的查询方法
下面以“学生管理”数据库为例,对常用的查询方法进行说明。
在“学生管理”数据库中有“学生”表、“课程”表、“成绩”表,如图1所示(说明:这3张表就是你上次实验课所建立的表,请附加上次实验的数据库以后,用SQL管理平台在这3张表中输入以下数据)。
成绩
图1 学生管理数据库中的基本表
往数据库中输入数据的方法有3种:
1)使用企业管理器完成数据输入:
-打开数据库表。右键表名,如“学生”——打开表——返回所有行。
-输入数据至学生表。
-单击数据表窗口右上角的关闭按钮,关闭当前数据库表,保存数据库表。
2)使用DTS导入/导出向导把EXCEL文件中的数据导入到数据库表中。
右键表名——所有任务——导入数据
3)用T-SQL命令完成数据输入(实验3介绍)。
各种查询:
1)简单查询
(1)从“成绩”表中查出所有学生分数。
方法一:
SELECT 分数
FROM 成绩;
方法二:
SELECT DISTINCT分数
FROM 成绩;
说明:使用DISTINCT,执行结果将去掉重复的元组。
(2)查询“学生”表中的所有学生信息。
方法一:
SELECT *
FROM 学生;
方法二:
SELECT 学号, 姓名, 性别, 出生日期, 院系
FROM 学生;
说明:如果要查询表中的所有列可以使用“*”标识。
(3)查询成绩大于90分的学生学号。
SELECT 学号
FROM 成绩
WHERE 分数>90;
(4)给出在计算机系或外语系学习的男学生的学号。
SELECT 学号
FROM 学生
WHERE 性别='男' AND (院系='计算机系' OR院系= '外语系');
或者:
SELECT 学号
FROM 学生
WHERE 性别='男' AND 院系in ('计算机系', '外语系')
(5)检索出分数在60~100分范围内的成绩信息。
SELECT 分数
FROM 成绩
WHERE 分数>=60 AND 分数<=100;
或者:
SELECT 分数
FROM 成绩
WHERE 分数BETWEEN 60 AND 100
说明:表达式“分数BETWEEN 60 AND l00”等价于(分数>=60) AND (分数<=100)
(6)从“学生”表中检索出姓“张”的学生信息。
SELECT *
FROM 学生
WHERE 姓名LIKE '张%';
注意:这里的LIKE是字符串匹配运算符,后面跟通配符“%”或者“_”,其中:
通配符“%”表示任意长度(0个或多个的)字符,
通配符“_”(下划线)表示任意一个字符。
(7)从“学生”表中检索出姓名的第二个字为“小”字的女生的信息。
SELECT *
FROM 学生
WHERE 姓名LIKE '_小%' AND 性别=?女?
(8)找出尚未确定分数的成绩信息。
SELECT *
FROM 成绩
WHERE 分数IS NULL;
注意:查询空值时要使用“IS NULL”,用“=NULL”是无效的,因为空值不是一个确定的数值,所以不能用“=”这样的运算符进行比较。
(9)列出已经确定了分数的成绩信息。
SELECT *
FROM成绩
WHERE 分数IS NOT NULL;
2)排序查询
(1)按学生的分数升序检索出全部成绩信息。
SELECT *
FROM 成绩
ORDER BY分数ASC;
说明:分数为NULL的会排在最前面。
(2)按学生的分数升序检索成绩,列出前3个学生的学号和分数。
SELECT TOP 3 学号,分数
FROM 成绩
ORDER BY分数ASC;
(3)先按学号排序,再按分数排序输出全部学生成绩信息。
SELECT *
FROM 成绩
ORDER BY学号, 分数;
3) 使用聚合(聚集)函数的查询
聚合函数——对一组值执行计算并返回单一的值。
聚合函数主要包括:
COUNT——计数;
SUM———求和;
A VG—一计算列值的平均值;
MAX——求最大值;
MIN——求最小值等。
(1)查询计算机系学生总人数。
select count(*) as 总人数
from 学生
where 院系='计算机系'
(2)查询选修了1号课程的学生人数和平均分。
select count(*)as 总人数,A VG(分数) as 平均分
from 成绩
where 课程号=1
4)分组查询(难点)
GROUP BY短语用于对查询结果进行分组。即对查询结果按指定列的值分组,该属性列值相等的元组为一个组。
说明:在分组查询时,经常用到聚合函数,在每组中作用聚集函数。
聚合函数经常与SELECT 语句的GROUP BY子句一同使用。
(1)查询各个系的学生总人数。
select 院系,count(*) as 总人数
from 学生
group by 院系
(2) 查询选修每门课程的课程号,学生人数和平均分。
select 课程号,count(*)as 总人数,avg(分数) as 平均分
from 成绩
group by 课程号
(3)求至少选修2门课的每名学生的学号,选修课程数和选修课的平均分数。
SELECT 学号, COUNT(*), A VG(分数)
FROM 成绩
GROUP BY学号
HA VING COUNT(*)>=2;
说明:这个例子中同时用了2个聚集函数:COUNT 和A VG
COUNT(*)用于统计元组个数。
5)多表连接查询
当查询的结果出自多个表时,需要通过表之间的连接操作来完成。
(1)一般连接:常规的两个表或多个表之间的连接。
①找出分数高于90分的学生姓名和院系。
SELECT 姓名, 院系
FROM 学生, 成绩
WHERE (分数>90)
AND (学生.学号=成绩.学号);
②找出选修数据库课程的学生的学号和分数。
SELECT 学号, 分数
FROM 课程, 成绩
WHERE 课程名= …数据库原理?
AND 课程.课程号=成绩.课程号;
③给出计算机系选修数据库课程的学生的学号和分数。
SELECT 学生.学号, 分数
FROM 学生, 课程, 成绩
WHERE 院系='计算机系' AND 课程名='数据库原理'
AND 学生.学号=成绩.学号AND 成绩.课程号=课程.课程号;
(2)自连接查询(查询的嵌套)
查询所有比王健同学出生日期晚的学生的学号、姓名、出生日期。
select 学号,姓名,出生日期
from 学生
where 出生日期>(select 出生日期
from 学生
where 姓名='王健')
实验3准备SQL数据操作(上课讲解)
1、SQL的数据操作功能
SQL的数据操作功能提供数据操纵语言(Data Manipulation Language,DML),使用DML 可以操纵数据库中的数据,实现如插入、删除和修改等数据库的基本操作。
(1)了解对表数据的插入、删除和修改都属于表数据的更新操作。对数据的操作可以使用SQL Server管理平台,也可以由SQL语句实现。
(2)掌握SQL中用于对表数据进行插入、修改和删除的命令分别是INSERT、UPDA TE 和DELETE。
(3)了解使用SQL语句在对表数据进行插入、修改和删除时,比使用SQL Server管理平台操作表数据更为灵活,功能更强大。
2、数据更新语句格式
在创建表结构后,接下来需要向表中添加数据,没有数据的表只是一个空的表结构,数据库就不能进行查询操作。在向表中添加数据后,可以根据需要进行数据的修改和删除。有两种方式在表中输入数据:一种方式是利用SQL Server的企业管理器(包括手动输入和数据导入两种方法,实验2中已练习过),另一种方式是利用SQL语句进行操作。
使用SQL语句进行操作
(1)向表中插入数据使用INSERT INTO语句。语句格式为:
①插入一个元组。
INSERT INTO <表名> [(<列名>[,<列名>…] ) )
V ALUES(<表达式>[,<表达式>…] )
②插入一个查询结果。
INSERT INTO<表名>[(<列名>[,<列名>…]])]
INSERT INTO语句操作举例:见教材P81例4-5和4-6或本章PPT上的例子
(2)对表中数据更新使用UPDA TE语句。语句格式为:
UPDA TE <表名>
SET <列名1>=<表达式1>[,<列名2>=<表达式2>……]
[from <表名>]
[WHERE <逻辑表达式>]
说明:
1)UPDA TE更新满足“逻辑表达式”条件的记录
2)一次可以更新多个属性的值
3)更新的条件可以与其他的表相关(使用FROM语句指定)
4)如果没有指定更新条件,则更新表中的全部记录
UPDA TE语句更新操作举例:见教材P80例4-2和4-3
补充一个例子:将’外语系’全体学生的成绩清零。
update成绩
set分数=0
from学生
where学生.学号=成绩.学号and院系='外语系'
或者
update成绩
set分数=0
where'外语系'=(
select院系
from学生
where学生.学号=成绩.学号)
说明:第二种写法在where语句中嵌套了一个select语句。
(3)删除表中的数据使用DELETE语句。语句格式为:
DELETE FROM <表名>
[FROM <表名>]
[WHERE <逻辑表达式>]
说明:
1)DELETE命令从指定的表中删除满足“逻辑表达式”条件的元组
2)如果没有指定删除条件,则删除表中的全部元组,所以在使用该命令时要格外小心
3)删除的条件可以与其他的表相关(使用可选的FROM语句指定)
4)DELETE命令只删除元组,不删除表或表结构。
DELETE语句操作举例:见教材P82例4-7,4-8,4-9
补充一个例子:删除’外语系’学生的全部选课记录。
delete from成绩
from学生
where学生.学号=成绩.学号and院系='外语系'
或者
Delete
From 成绩
Where '外语系'=(
Select院系
From学生
Where学生.学号=成绩.学号)
说明:第二种写法在where语句中嵌套了一个select语句。
3、操作举例
有一“仓库管理”数据库,设有四个表“职工”、“仓库”、“供应商”、“订购单”,结构如下:
(1)插入操作
在“订购单”表中插入:
①插入一个完整的元组。
INSERT INTO订购单
V ALUES (…E7?, …S4?,?0R76?, …2008-3-5?)
②插入一个不完整的元组。
INSERT INTO订购单(职工号, 订购单号)
V ALUES(…E7?, …0R78?)
③插入一个查询结果。
INSERT INTO订购单备份
SELECT *
FROM 订购单
(2)更新操作
①给WHl仓库的职工提高10%的工资。
UPDA TE职工
SET工资= 工资*1.1
WHERE仓库号=?WHl?;
②给所有职工增加l0%的工资。
UPDA TE 职工
SET工资=工资*1.1;
③给“武汉”仓库的职工提高10%的工资。
UPDA TE 职工
SET工资=工资*1.1
FROM 仓库
WHERE 仓库.仓库号=职工.仓库号
AND 城市=?武汉?
(3)删除操作
①删除仓库关系中仓库号值是WH2的元组。
DELETE FROM仓库
WHERE 仓库号= …WH2? ;
②删除所在城市是上海的仓库的所有职工元组。
DELETE FROM职工
FROM 仓库
WHERE仓库.仓库号= 职工.仓库号
AND城市= …上海?;