SQL专题(SQL数据查询与SQL数据库表的管理)教案

《SQL数据查询与SQL数据库表的管理》教案
一、授课思路[90分钟] (2)
1.任务、目标部分[5分钟] (2)
2.重难点分析讲解[85分钟] (2)
2.1.T-SQL语句实现简单查询、条件查询与排序操作—范例演示[10分钟] (2)
2.2.T-SQL语句实现连接查询与子查询—范例演示[10分钟] (3)
2.3.T-SQL语句实现统计查询与创建查询结果集—范例演示[15分钟] (3)
2.4.T-SQL语句实现创建修改删除表与表中操纵数据语句—范例演示[10分钟] (4)
2.5.学生课堂项目案例操作[40分钟] (4)
2.6.总结[5分钟] (4)
1、学员问题汇总 (5)
2、作业布置 (5)
课时：90分钟
授课人：戴翼
⏹本课工作任务
熟练掌握T-SQL语句实现简单查询、条件查询与排序操作；
熟练掌握T-SQL语句实现连接查询与子查询；
熟练掌握T-SQL语句实现统计查询与创建查询结果集；
熟练掌握T-SQL语句实现创建修改删除表；
熟练掌握T-SQL语句实现表中操纵数据语句；
⏹本课解决目标
难点一：连接查询与子查询；
难点二：统计查询与创建查询结果集；
难点三：在对表中操纵数据如何掌握数据完整性；
一、授课思路 [90分钟]
本节依次讲解3个重难点问题：
1)重难点问题一（连接查询与子查询）
2)重难点问题二（统计查询与创建查询结果集）
3)重难点问题三（在对表中操纵数据如何掌握数据完整性）
1.任务、目标部分[5分钟]
说明本次课的任务和目标。

2.重难点分析讲解 [85分钟]
2.1.T-SQL语句实现简单查询、条件查询与排序操作—范例演示[10分钟]
范例演示：实现对考试系统数据库中数据的查询操作。

--1、查询STUDENT表中的所有记录的SNAME、SSEX和CLASS列。

SELECT SNAME,SSEX,CLASS FROM STUDENT
--2、查询教师所有的单位即不重复的DEPART列。

SELECT DISTINCT DEPART FROM TEACHER
--3、查询STUDENT表的所有记录。

SELECT SNO AS '学号',SNAME AS '姓名',SSEX AS '性别',SBIRTHDAY AS'出生日期',CLASS AS'班号'FROM STUDENT
--4、查询SCORE表中成绩在60到80之间的所有记录。

SELECT * FROM SCORE WHERE DEGREE BETWEEN 60 AND 80
--或
SELECT * FROM SCORE WHERE DEGREE>=60 AND DEGREE<=80
--5、查询SCORE表中成绩为85，86或88的记录。

SELECT * FROM SCORE WHERE DEGREE IN (85,86,88)
--6、查询STUDENT表中“95031”班或性别为“女”的同学记录。

SELECT * FROM STUDENT WHERE CLASS='95031'OR SSEX='女'
--7、以CLASS降序查询STUDENT表的所有记录。

SELECT * FROM STUDENT ORDER BY CLASS DESC
--8、以CNO升序、DEGREE降序查询SCORE表的所有记录。

SELECT * FROM SCORE ORDER BY CNO ASC,DEGREE DESC
--或
SELECT * FROM SCORE ORDER BY CNO,DEGREE DESC
讲解要点：总结语法格式说明和语句书写的注意事项。

2.2.T-SQL语句实现连接查询与子查询—范例演示[10分钟]
范例演示：实现对考试系统数据库中数据的查询操作。

--1、查询SCORE表中的最高分的学生学号和课程号。

SELECT SNO AS '学号',CNO AS '课程号', DEGREE AS '最高分' FROM SCORE WHERE DEGREE=(SELECT MAX(DEGREE) FROM SCORE)
--2、查询所有学生的SNAME、CNO和DEGREE列。

SELECT STUDENT.SNAME,O,SCORE.DEGREE FROM STUDENT,SCORE WHERE STUDENT.SNO=SCORE.SNO
--3、查询所有学生的SNO、CNAME和DEGREE列。

SELECT T1.SNO,AME,T1.DEGREE FROM SCORE T1,COURSE T2 WHERE O=O
-- 4、查询所有学生的SNAME、CNAME和DEGREE列。

SELECT T1.SNAME,AME,T3.DEGREE FROM STUDENT T1,COURSE T2,SCORE T3 WHERE T1.SNO=T3.SNO AND O=O
-- 5、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

SELECT O,T1.SNO,T1.DEGREE FROM SCORE T1,SCORE T2 WHERE T1.DEGREE>T2.DEGREE AND T2.SNO='109'AND O='3-105'
--或
SELECT CNO,SNO,DEGREE FROM SCORE WHERE DEGREE>(SELECT DEGREE FROM SCORE WHERE SNO='109' AND CNO='3-105')
讲解要点：总结语法格式说明和语句书写的注意事项。

2.3.T-SQL语句实现统计查询与创建查询结果集—范例演示[15分钟]
范例演示：实现对考试系统数据库中数据的查询操作。

--1、查询“95031”班的学生人数。

SELECT COUNT(*) AS '学生人数' FROM STUDENT WHERE CLASS='95031'
-- 2、查询‘3-105’号课程的平均分。

SELECT A VG(DEGREE)AS '课程平均分' FROM SCORE WHERE CNO='3-105'
--3、查询SCORE表中至少有5名学生选修的并以3开头的课程的平均分数。

SELECT CNO,A VG(DEGREE) FROM SCORE WHERE CNO LIKE '3%' GROUP BY CNO
HA VING COUNT(*)>5
--4、查询最低分大于70，最高分小于90的SNO列。

SELECT SNO FROM SCORE GROUP BY SNO HA VING MIN(DEGREE)>70 AND MAX(DEGREE)<90
-- 5、查询“95033”班所选课程的平均分。

SELECT O,A VG(T2.DEGREE) AS '平均分' FROM STUDENT T1,SCORE T2 WHERE T1.SNO=T2.SNO AND T1.CLASS='95033' GROUP BY O
讲解要点：总结语法格式说明和语句书写的注意事项。

2.4.T-SQL语句实现创建修改删除表与表中操纵数据语句—范例演示[10分钟]
范例演示：实现对考试系统数据库中数据的查询操作。

--1、假设使用如下命令建立了一个GRADE表：
/*
CREATE TABLE GRADE
(LOW NUMERIC(3,0),
UPP NUMERIC(3),
RANK CHAR(1))
GO
INSERT INTO GRADE V ALUE(90,100,'A')
INSERT INTO GRADE V ALUE(80,89,'B')
INSERT INTO GRADE V ALUE(70,79,'C')
INSERT INTO GRADE V ALUE(60,69,'D')
INSERT INTO GRADE V ALUE(0,59,'E')
现查询所有同学的SNO、CNO和RANK列。

*/
SELECT SNO,CNO,RANK FROM SCORE,GRADE WHERE DEGREE BETWEEN LOW AND UPP ORDER BY RANK
--2、将学号SNO为105的学生性别SSEX改为’女’
UPDATE STUDENT SET SSEX=’女’ WHERE SNO=105;
--3、删除王芳同学的所有考试成绩
DELETE FROM SCORE WHERE SNO IN (SELECT SNO FROM STUDENT WHERE SNAME=’王芳’)
讲解要点：总结语法格式说明和语句书写的注意事项。

2.5.学生课堂项目案例操作[40分钟]
2.6.总结[5分钟]
根据学生提交的重难点进行总结。

1、学员问题汇总
⏹问题1：。

答：。

⏹问题2：。

答：。

⏹问题3：。

答：。

2、作业布置
/*
SQL练习题一
*/
--有2张表，为父子关系表：
CREATE DATABASE EMP
GO
USE EMP
GO
CREATE TABLE EMPLOYEE
(
EMP_ID INT IDENTITY(1,1) NOT NULL,
NAME NV ARCHAR(50) NOT NULL,
AGE INT NOT NULL,
CONSTRAINT PK_EMPLOYEE PRIMARY KEY CLUSTERED (
EMP_ID
)
)
GO
CREATE TABLE SALARY
(
EMP_ID INT NOT NULL,
SALARY_NAME NV ARCHAR(50) NOT NULL,
SALARY_MONEY SMALLMONEY NOT NULL, CONSTRAINT FK_EMPLOYEE_SALARY FOREIGN KEY
(
EMP_ID
) REFERENCES EMPLOYEE
(
EMP_ID
)
)
GO
/*
1、EMPLOYEE 表
EMP_ID NAME AGE
1 张三31
2 李四28
3 王五40
...
2、SALARY 表
EMP_ID SALARY_NAME[工资项目] SALARY_MONEY[工资金额]
1 基本工资1000.00
1 补助300.00
1 津贴200.00
2 基本工资4000.00
2 补助200.00
2 津贴100.00
3 基本工资7000.00
3 补助500.00
3 津贴100.00
...
*/
INSERT INTO EMPLOYEE (NAME,AGE) V ALUES('张三',31)
INSERT INTO EMPLOYEE (NAME,AGE) V ALUES('李四',28)
INSERT INTO EMPLOYEE (NAME,AGE) V ALUES('王五',40)
INSERT INTO SALARY (EMP_ID,SALARY_NAME,SALARY_MONEY) V ALUES(1,'基本工资',1000.00)
INSERT INTO SALARY (EMP_ID,SALARY_NAME,SALARY_MONEY) V ALUES(1,'补助',300.00)
INSERT INTO SALARY (EMP_ID,SALARY_NAME,SALARY_MONEY) V ALUES(1,'津贴',200.00)
INSERT INTO SALARY (EMP_ID,SALARY_NAME,SALARY_MONEY) V ALUES(2,'基本工资',4000.00)
INSERT INTO SALARY (EMP_ID,SALARY_NAME,SALARY_MONEY) V ALUES(2,'补助',200.00)
INSERT INTO SALARY (EMP_ID,SALARY_NAME,SALARY_MONEY) V ALUES(2,'津贴',100.00)
INSERT INTO SALARY (EMP_ID,SALARY_NAME,SALARY_MONEY) V ALUES(3,'基本工资',7000.00)
INSERT INTO SALARY (EMP_ID,SALARY_NAME,SALARY_MONEY) V ALUES(3,'补助',500.00)
INSERT INTO SALARY (EMP_ID,SALARY_NAME,SALARY_MONEY) V ALUES(3,'津贴',100.00)
SELECT * FROM EMPLOYEE
SELECT * FROM SALARY
/*
其中，EMPLOYEE表的EMP_ID对应于SALARY表的EMP_ID是一对多的关系。

能否直接用查询检索出下面的结果？
EMP_ID NAME AGE 基本工资补助津贴合计
1 张三31 1000.00 300.00 200.00 1500.00
2 李四28 4000.00 200.00 100.00 4300.00
3 王五40 7000.00 500.00 100.00 7600.00 */
--答案：***********
SELECT S.EMP_ID,,S.AGE,
S1.基本工资,S2.补助,
S3.津贴,S4.合计
FROM EMPLOYEE S,
(SELECT EMP_ID, SALARY_MONEY AS '基本工资' FROM SALARY WHERE SALARY_NAME = '基本工资') S1,
(SELECT EMP_ID, SALARY_MONEY AS '补助' FROM SALARY WHERE SALARY_NAME = '补助') S2,
(SELECT EMP_ID, SALARY_MONEY AS '津贴' FROM SALARY WHERE SALARY_NAME = '津贴') S3,
(SELECT EMP_ID, SUM(Salary_Money) AS '合计' FROM SALARY GROUP BY EMP_ID) S4 WHERE S.EMP_ID = S1.EMP_ID AND
S.EMP_ID = S2.EMP_ID AND
S.EMP_ID = S3.EMP_ID AND
S.EMP_ID = S4.EMP_ID
/*
SQL练习题二
建立一个数据库STUDENT,数据表COMPUTER,字段名NAME,NUMBER,SEX,SQL2000,FlASH,NET,其中SQL2000,Flash,Net设置为浮点型float.
*/
CREATE DATABASE STUDENT
GO
USE STUDENT
GO
CREATE TABLE COMPUTER
(
NAME NV ARCHAR(10) NOT NULL,
NUMBER INT IDENTITY(2009,1) NOT NULL,
SEX NV ARCHAR(2) NOT NULL,
SQL2000 FLOAT NULL,
FLASH FLOAT NULL,
NET FLOAT NULL
)
GO
INSERT INTO COMPUTER (NAME,SEX,SQL2000,FLASH,NET) V ALUES('陈明','男',85.5,90.5,100.0)
INSERT INTO COMPUTER (NAME,SEX,SQL2000,FLASH,NET) V ALUES('张三','男',55.5,65.5,58.5)
INSERT INTO COMPUTER (NAME,SEX,SQL2000,FLASH,NET) V ALUES('李燕','女',90,95.5,80.5)
INSERT INTO COMPUTER (NAME,SEX,SQL2000,FLASH,NET) V ALUES('陈峰','男',80,85.5,86) INSERT INTO COMPUTER (NAME,SEX,SQL2000,FLASH,NET) VALUES('张晓瑞','女',60,62.5,55.5)
INSERT INTO COMPUTER (NAME,SEX,SQL2000,FLASH,NET) V ALUES('李四','男',93,95.5,80.5)
GO
--1、输出所有男生的成绩
USE STUDENT
GO
SELECT SQL2000 AS 'SQL数据库',FLASH AS '网络动画',NET AS '计算机网络' FROM COMPUTER WHERE SEX='男'
--2、输出所有SQL成绩在90以上的女生的成绩
USE STUDENT
GO
SELECT SQL2000 AS 'SQL数据库' FROM COMPUTER WHERE SEX='女'AND SQL2000>=90
--3、输出某一科目不合格所有的男生的成绩
USE STUDENT
GO
SELECT SQL2000 AS 'SQL数据库',FLASH AS '网络动画',NET AS '计算机网络' FROM COMPUTER WHERE SEX='男'AND SQL2000<60 OR Flash<60 OR Net<60
--4、计算并显示每位同学各科的总分和平均分，并按总分从高到低排序
USE STUDENT
GO
SELECT SQL2000+FLASH+NET AS '总分',(SQL2000+FLASH+NET)/3 AS '平均分' FROM COMPUTER ORDER BY SQL2000+FLASH+NET DESC
--5、输出所有计算机网络成绩在70-79之间的同学
USE STUDENT
GO
SELECT * FROM COMPUTER WHERE NET BETWEEN 70 AND 79
--6、输出所有姓“陈”和姓“李”的男生
USE STUDENT
GO
SELECT * FROM COMPUTER WHERE SEX='男' AND LEFT(Name,1) IN ('李', '陈')
--或者
USE STUDENT
GO
SELECT * from COMPUTER WHERE SEX='男' AND (NAME LIKE '李%' OR NAME LIKE '陈%')
--7、输出所有学号为偶数的同学成绩
USE STUDENT
GO
SELECT NUMBER AS '学号',SQL2000 AS 'SQL数据库',FLASH AS '网络动画',NET AS '计算机网络' FROM COMPUTER WHERE NUMBER%2=0
--8、输出Flash成绩最好的5位同学
USE STUDENT
GO
SELECT TOP 5 * FROM COMPUTER ORDER BY FLASH DESC
--9、更新同学的成绩，把计算机网络成绩在55-59之间的同学该科的成绩调整为60分
USE STUDENT
GO
UPDATE COMPUTER SET NET=60 WHERE NET BETWEEN 55 AND 59
--10、删除平均分最低的3位同学
USE STUDENT
GO
DELETE FROM COMPUTER WHERE NUMBER IN(SELECT TOP 3 NUMBER FROM COMPUTER ORDER BY (SQL2000+FLASH+NET)/3)
--11、统计成绩表中平均分为90以上（含90分）人数
USE STUDENT
GO
SELECT COUNT(*) AS '平均分为90以上人数' FROM COMPUTER WHERE (SQL2000+FLASH+NET)/3>=90
--12、用SQL命令向成绩表添加一个新字段——C语言
USE STUDENT
GO
ALTER TABLE COMPUTER ADD C语言FLOAT NULL
SP_HELP COMPUTER
/*
SQL练习题三
问题描述：
已知关系模式：
STUDENT（SNo,SName）学生表。

SNo 为学号，
SName 为姓名
COURSE（CNo,CName,CTeacher）课程表。

CNo 为课程号，
CName 为课程名，
CTeacher 为任课教师
SCORE（SNo,CNo,Score）成绩表。

Score 为成绩
要求实现如下5个处理：
1.找出没有选修过“李明”老师讲授课程的所有学生姓名
2.列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩
3.列出既学过“1”号课程，又学过“2”号课程的所有学生姓名
4.列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号
5.列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩*/
--1.找出没有选修过“李明”老师讲授课程的所有学生姓名
SELECT SName FROM STUDENT WHERE NOT EXISTS(
SELECT * FROM SCORE,COURSE sc,c
WHERE o=o AND COURSE.CTeacher='李明' AND SCORE.SNo=STUDENT.SNo)
--2. 列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩
SELECT STUDENT.SNo,STUDENT.SName,A VG_SCORE=A VG(SCORE.Score)
FROM STUDENT,SCORE,(SELECT SNo FROM SCORE WHERE Score<60 GROUP BY SNo HA VING COUNT(DISTINCT CNo)>=2) A
WHERE STUDENT.SNo=A.SNo AND SCORE.SNo=A.SNo GROUP BY SCORE.SNo,SCORE.SName
--3. 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名
SELECT STUDENT.SNo,STUDENT.SName FROM STUDENT,(
SELECT SCORE.SNo FROM SCORE,COURSE
WHERE o=o AND ame IN('1','2')
GROUP BY SNo HA VING COUNT(DISTINCT CNo)=2)SCORE WHERE STUDENT.SNo=SCORE.SNo
--4. 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号
SELECT STUDENT.SNo,STUDENT.SName
FROM STUDENT,SCORE sc1,SCORE sc2
WHERE o='1' AND sc2.SNo='2' AND o=o AND sc1.Score>sc2.Score
--5. 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩
SELECT sc1.SNo,[1号课成绩]=sc1.Score,[2号课成绩]=sc2.Score
FROM SCORE sc1,SCORE sc2
WHERE o='1' AND o='2' AND sc1.SNo=sc2.SNo AND sc1.Score>sc2.Score
/*
SQL练习题四
有四个表(关系）：
PRODUCT(Maker,Model,Type)
maker:产品制造商，
model:型号，
type:产品分类(pc,printer,laptop))
PC(Model,Speed,Ram,Hd,Cd,Price)
LAPTOP(Model,Speed,Ram,Hd,Screen,Price)
PRINTER(Model,Color,Price)
四个表中主键都是Model,求一SQL命令，能查出所有产品中价格(price)最高的产品。

*/
SELECT Model FROM PRODUCT WHERE Model IN (SELECT Model FROM(SELECT PC.Model Model,PC.Price Price FROM PRODUCT,PC WHERE PC.Model=PRODUCT.Model
UNION
SELECT LAPTOP.Model Model,LAPTOP.Price Price FROM PRODUCT,LAPTOP WHERE LAPTOP.Model=PRODUCT.Model
UNION
SELECT PRINTER.Model Model,PRINTER.Price Price FROM PRODUCT,PRINTER WHERE PRINTER.Model=PRODUCT.Model) WHERE Price IN (SELECT MAX(Price) FROM (SELECT PC.Model Model,PC.Price Price FROM PRODUCT,PC WHERE PC.Model=PRODUCT.Model UNION
SELECT LAPTOP.Model Model,LAPTOP.Price Price FROM PRODUCT,LAPTOP WHERE
LAPTOP.Model=PRODUCT.Model
UNION
SELECT PRINTER.Model Model,PRINTER.Price Price FROM PRODUCT,PRINTER WHERE PRINTER.Model=PRODUCT.Model)))
/*
SQL练习题五
设有如下4个关系模式：
书店（书店编号，书店名，地址）
图书（书号，书名，定价）
图书馆（馆号，馆名，城市，电话）
图书发行（馆号，书号，书店号，数量）
设各关系模式中的数据满足下列问题，请回答：
（1）用SQL语句检索已发行的图书中最贵的书名和定价。

（2）写出下列SQL语句所表达的中文意思。

*/
SELECT 馆名FROM 图书馆WHERE 馆号IN (
SELECT 馆号FROM 图书发行WHERE 书号IN (
SELECT 书号FROM 图书WHERE 书名='数据库系统基础'))
/*
SQL练习题六
1.对于一元二次方程ax^2+bx+c=0在实数范围内的解;
当b^2-4ac>=0,其方程的根为：(b+(b^2-4ac)^0.5/2a)和(b-(b^2-4ac)^0.5/2a);
当b^2-4ac<=0,无解;
编写存储过程，要求输入参数为a,b,c,输出参数为方程的根x1,x2。

2.在数据库中有张表S，字段有SC（代码），SK（货号），QH（库存），
用一句SQL将代码为CQ01、货号位001、库存为10 更改库存为20！
*/
/*
SQL练习题七
1.有两张表USER（uid，name，tel）和KEYWORDS（uid，keyword，date，time）
USER表示用户的基本信息;
KEYWORDS表示用户检索的情况;
keyword是检索用的关键词;
date是检索日期;
time是每次提交检索式的时间，精确到秒;
问题：显示使用检索词数最多的两个人
*/
SELECT USER.* FROM USER,(SELECT TOP 2 uid, COUNT(keyword) AS 'MAX' FROM KEYWORDS GROUP BY uid) Chaxun_max
WHERE USER.uid = Chaxun_max.uid。