搜档网
当前位置:搜档网 › SQL数据查询和数据操作举例

SQL数据查询和数据操作举例

实验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城市= …上海?;

相关主题