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

实验2准备 SQL查询（上课讲解）（教材该部分内容:P79-80,102-105）1、SQL的数据查询功能使用数据库和表的主要目的是存储数据，以便在需要时进行检索、统计或组织输出，通过T-SQL的查询可以从表或视图中迅速、方便地检索数据。SQL的SELECT语句可以实现对表的选择、投影及连接操作，其功能十分强大。2、SQL查询命令格式SQL的核心是查询，SQL的查询命令也称作SELECT命令，它的基本形式由SELECTFROM-WHERE查询块组成，多个查询块可以嵌套执行。 3几种常用的查询方法 下面以“学生管理”数据库为例，对常用的查询方法进行说明。在“学生管理”数据库中有“学生”表、“课程”表、“成绩”表，如图1所示（说明：这3张表就是你上次实验课所建立的表,请附加上次实验的数据库以后，用SQL管理平台在这3张表中输入以下数据）。学生学号姓名性别出生日期院系101001 张丽女1987-3-2计算机系101002王健男1987-4-10计算机系102001赵南南女1988-8-12外语系102002孙小娟女1987-11-17外语系学号课程号分数1010011801010012911010023NULL102001196102002180课程 成绩课程号课程名学时学分1英语6442高等数学9653数据库原理7244管理信息系统724图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)检索出分数在60100分范围内的成绩信息。SELECT 分数FROM 成绩WHERE 分数=60 AND 分数60) AND (分数=2;说明：这个例子中同时用了2个聚集函数：COUNT 和 AVGCOUNT(*)用于统计元组个数。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、UPDATE和DELETE。(3)了解使用SQL语句在对表数据进行插入、修改和删除时，比使用SQL Server管理平台操作表数据更为灵活，功能更强大。2、数据更新语句格式在创建表结构后，接下来需要向表中添加数据，没有数据的表只是一个空的表结构，数据库就不能进行查询操作。在向表中添加数据后，可以根据需要进行数据的修改和删除。有两种方式在表中输入数据：一种方式是利用SQL Server的企业管理器（包括手动输入和数据导入两种方法，实验2中已练习过），另一种方式是利用SQL语句进行操作。使用SQL语句进行操作(1)向表中插入数据使用INSERT INTO语句。语句格式为：插入一个元组。INSERT INTO (， ) )VALUES(， )插入一个查询结果。INSERT INTO(，) INSERT INTO语句操作举例：见教材P81例4-5和4-6或本章PPT上的例子(2)对表中数据更新使用UPDATE语句。语句格式为：UPDATE SET =,=from WHERE 说明：1）UPDATE更新满足“逻辑表达式”条件的记录2）一次可以更新多个属性的值3）更新的条件可以与其他的表相关（使用FROM语句指定）4）如果没有指定更新条件，则更新表中的全部记录UPDATE语句更新操作举例：见教材P80例4-2和4-3补充一个例子：将外语系全体学生的成绩清零。update 成绩set 分数=0from 学生where 学生.学号=成绩.学号and 院系=外语系或者update 成绩set 分数=0where外语系=(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、操作举例有一“仓库管理”数据库，设有四个表“职工”、“仓库”、“供应商”、“订购单”，结构如下：仓库号城市号面积WH1北京370WH2上海500WH3广州200WH4武汉400供应商仓库职工供应商号供应商名地址S3振华电子西安S4华通电子北京S6607厂郑州S7爱华电子北京仓库号职工号工资WH1E13220WH1E33210WH2E43250WH3E63230WH1E73250职工号供应商号订购单号订购日期E3S7OR672008-1-3E1S4OR732008-3-12E3S4OR772008-2-6订购单(1)插入操作在“订购单”表中插入：插入一个完整的元组。 INSERT INTO订购单 VALUES (E7, S4，0R76, 2008-3-5)插入一个不完整的元组。 INSERT INTO订购单(职工号, 订购单号) VALUES(E7, 0R78)插入一个查询结果。 INSERT INTO订购单备份 SELECT * FROM 订购单(2)更新操作给WHl仓库的职工提高10的工资。 UPDATE职工 SET工资 = 工资*1.1 WHERE仓库号=WHl; 给所有职工增加l0的工资。 UPDATE 职工 SET工资=工资*1.1; 给“武汉”仓库的职工提高10的工资。