//求所有学员的分数总和
select sum(s_mark) from stumark
//求所有学员的平均分数
select avg(s_mark) as 数据库平均成绩 from stumark where s_subject='数据库'
select count(*) as 及格人数 from stumark where s_mark>=60
//找出不及格的人并查找出学号
select s_id,s_subject,s_mark from stumark where s_mark<60
//计算出及格的人数并按课程分组
select count(*) as 不及格人数,s_subject as 科目名称 from stumark where s_mark>=60 group by s_subject
//不常用的语句 having count(*)>0
//计算不及格人数
select count(*) from stumark where s_mark<60 group by s_subject
select * from stumark
update stumark set s_subject='java编程' where s_id between 0001 and 0011
update stumark set s_subject='html网页制作' where s_id between 0012 and 0021
//内联结查询students表和stumark表
select students.s_id,students.s_name,stumark.s_subject,stumark.s_mark
from students
inner join stumark
on students.s_id=stumark.s_id
//同上,表用别名
select s.s_id,s.s_name,k.s_subject,k.s_mark
from students as s
inner join stumark as k
on s.s_id=k.s_id
//实验一种被淘汰的自联接查找方法(也可以,但不应这样子写的方法)
select s.s_id,s.s_name,k.s_subject,k.s_mark
from students s,stumark k
where s.s_id=k.s_id
//新建一张课程表course,用于记录不同学员的不同课程的成绩(还是空表)
create table course
(
c_courseID varchar(20),
c_studentID varchar(20),
c_course varchar(20),
c_mark varchar(20)
)
//左外联结students表和stumark表
select *
from students
left join stumark
on students.s_id=stumark.s_id
//右外联结students表和stumark表
select *
from students
right join stumark
on students.s_id=stumark.s_id
//多表查询之随想查询练习
//根据students中的s_id,以及stumark表中的s_id查询该学员某一课程的成绩
select s.s_id,k.s_subject,k.s_mark
from students as s
inner join stumark as k
on s.s_id=k.s_id
//在course表中插入数据以备后用
insert into course (c_courseID,c_studentID,c_course,c_mark)
values ('01','0001','英语','89')
insert into course
select '02','0010','高数','88' union
select '03','0030','电影欣赏','93' union
select '01','0002','英语','91'
insert into course
select '02','0012','高数','99' union
select '01','0030','英语','44' union
select '03','0010','电影欣赏','91'
use students
select * from course
//修改表course中c_mark的数据类型,把c_mark从varchar(20) 改成int 型
//update course set c_mark convert(int,c_mark) 不能使用update语句,而应该用alter语句,正确用法如下
alter table course alter column c_mark varchar(20)
//按学号分组查询course表中学员的所有科目的平均成绩
select c_studentID as 学号,avg(convert(int,c_mark)) as 所有科目平均成绩 from course group by c_studentID
//把course表中c_studentID等于0030的学员的英语成绩分数从 44 改成 66分
update course set c_mark=66 where c_studentID=0030 and c_course='英语'
//查询0001号学生的英语成绩,实现三表查询
select k.s_id as 学号,s.s_name as 姓名,c.c_courseID as 课程号,c.c_course as 课程名称,c.c_mark as 分数
from students as s
inner join stumark as k
on k.s_id=s.s_id
inner join course as c
on s.s_id=c.c_studentID and c.c_studentID='0001' and c.c_course='英语'
//用另一种方法实现上面一题的要求,查询结果一样
select k.s_id as 学号,s.s_name as 姓名,c.c_courseID as 课程号,c.c_course as 课程名称,c.c_mark as 分数
from stumark as k
left join students as s
on k.s_id=s.s_id
right join course as c
on c.c_studentID=k.s_id
//练习使用in 命令,要使用group by 命令的话,必须把要被分到一起的数值使用聚合命令,如avg
select avg(convert(int,c_mark)) from course where c_course in('英语','电影欣赏') group by c_courseID
//////////////////////////////////////////////////////////////////////
2009-7-17下午上课的内容 <!-------- 子查询!>
select * from course
//用子查询方式查找某学员某门课程的成绩
select s_id,s_subject,s_mark from stumark where (s_id =(select s_id from students where s_id='0010'))
//上一题中若要显示出子表中的列值,则应用连接查询
select s.s_id,s.s_name,m.s_subject,m.s_mark from students as s
inner join stumark as m
on s.s_id=m.s_id and m.s_id='0010'
//查询stumark表中 科目成绩 大于所有人的 java编程 课程的平均成绩的人的 名字
select s_name from students where s_id in(
select s_id from stumark where (s_mark>
(select avg(s_mark) from stumark where s_subject='java编程')
)
)
//用联接查询实现上题要求
select s_id,s_subject,s_mark from stumark where s_mark>avg(s_mark) 有错!!发现这样无法达到要求
//in关键字用于判断,确定给定的值是否与子查询中的值匹配.
select * from students where s_id in (select s_id from stumark where stumark.s_id=students.s_id)
//同样条件的效果
select * from students inner join stumark
on students.s_id=stumark.s_id
//使用 EXISTS 关键字引入子查询后，子查询的作用就相当于进行存在测试。
//外部查询的 WHERE 子句测试子查询返回的行是否存在。子查询实际上不产生任何数据，它只返回 TRUE 或 FALSE 值。
select * from stumark where exists (select s_id from students where students.s_id=stumark.s_id and students.s_id='0001')
//UNION并集运算将多个查询运算的结果集进行“合并”。
select * from course where c_courseID='01'
union all
select * from students where s_age>20
<--------------------2009.8.24-第二阶段作业----事务----->
use students
//新建q币表qq
create table qq
(qqNum varchar(20) primary key,
qqMoney money
)
//插入记录
insert qq
select '111111',100.0 union
select '222222',1.0
//给表中qqMoney列加约束
alter table qq add constraint ck_qqMoney check(qqMoney>=1)
//把"111111"的q币转给"222222"
begin transaction
declare @error int
set @error=0
update qq set qqMoney=qqMoney-99 where qqNum='111111'
set @error=@error+@@error
update qq set qqMoney=qqMoney+99 where qqNum='222222'
set @error=@error+@@error
if @error<>0
begin
print '余额不足,转账失败'
rollback transaction
end
else
begin
print '转账成功,请放心使用'
commit transaction
end
go
print '查询转账结果'
select * from qq
go
<!------阶段记录----->
//一些关键词汇
transaction //事务
constraint 和 check //运用于约束的创建
set implicit_transactions on //把隐性事务模式设置为打开
set implicit_transactions off //把隐性事务模式设置为关闭
distinct //标识列
<!--------阶段记录---->
<!-------2009.8.25第二阶段作业-----索引,视图,T-SQL编程-------->
//第一题
//新建成绩表
create table stuMarks
(ExamNo varchar(20),
stuNo varchar(20),
writtenExam int,
LabExam int
)
//向成绩表插入值
insert stuMarks
select 'S271811','s25303',96,58 union
select 'S271813','s25302',66,90 union
select 'S271816','s25301',93,82 union
select 'S271818','s25328',61,65
//对表值进行查询修改,根据如下规则对机试成绩进行反复加分,直到平均分超过85分止
//90分以上:不加分
//80-89分: 加1分
//70-79分: 加2分
//60-69分: 加3分
//60分以下:加5分
declare @mark int,@avgMark int
set @mark=0
set @avgMark=0
while(1=1)
begin
select @avgMark=avg(LabExam) from stuMarks
if (@avgMark<85)
begin
select @mark=LabExam from stuMarks where stuNo='s25303'//对当前列进行查询修改
if(@mark<60)
update stuMarks set LabExam=LabExam+5 where stuNo='s25303'
if(@mark between 60 and 69)
update stuMarks set LabExam=LabExam+3 where stuNo='s25303'
if(@mark between 70 and 79)
update stuMarks set LabExam=LabExam+2 where stuNo='s25303'
if(@mark between 80 and 89)
update stuMarks set LabExam=LabExam+1 where stuNo='s25303'
if(@mark >90)
update stuMarks set LabExam=LabExam where stuNo='s25303'
select @mark=LabExam from stuMarks where stuNo='s25302'//对当前列进行查询修改
if(@mark<60)
update stuMarks set LabExam=LabExam+5 where stuNo='s25302'
if(@mark between 60 and 69)
update stuMarks set LabExam=LabExam+3 where stuNo='s25302'
if(@mark between 70 and 79)
update stuMarks set LabExam=LabExam+2 where stuNo='s25302'
if(@mark between 80 and 89)
update stuMarks set LabExam=LabExam+1 where stuNo='s25302'
if(@mark >90)
update stuMarks set LabExam=LabExam where stuNo='s25302'
select @mark=LabExam from stuMarks where stuNo='s25301'//对当前列进行查询修改
if(@mark<60)
update stuMarks set LabExam=LabExam+5 where stuNo='s25301'
if(@mark between 60 and 69)
update stuMarks set LabExam=LabExam+3 where stuNo='s25301'
if(@mark between 70 and 79)
update stuMarks set LabExam=LabExam+2 where stuNo='s25301'
if(@mark between 80 and 89)
update stuMarks set LabExam=LabExam+1 where stuNo='s25301'
if(@mark >90)
update stuMarks set LabExam=LabExam where stuNo='s25301'
select @mark=LabExam from stuMarks where stuNo='s25328'//对当前列进行查询修改
if(@mark<60)
update stuMarks set LabExam=LabExam+5 where stuNo='s25328'
if(@mark between 60 and 69)
update stuMarks set LabExam=LabExam+3 where stuNo='s25328'
if(@mark between 70 and 79)
update stuMarks set LabExam=LabExam+2 where stuNo='s25328'
if(@mark between 80 and 89)
update stuMarks set LabExam=LabExam+1 where stuNo='s25328'
if(@mark >90)
update stuMarks set LabExam=LabExam where stuNo='s25328'
end
else
break
end
go
select * from stuMarks //查看修改后的成绩表
go
//第二题
use students
//新创建 员工信息表
create table person
(p_id varchar(20),
p_name varchar(20),
p_sex varchar(20),
p_age int,
p_park varchar(20),
p_work varchar(20),
p_salary Money
)
go
//插入记录
insert person
select '0001','张一','男',20,'人事部','员工管理',3000.0 union
select '0002','张二','女',26,'技术部','教学',5000.0 union
select '0003','张三','男',25,'后勤部','器械管理',3000.0 union
select '0004','王五','男',35,'管理部门','经理',8000.0
go
select * from person
//创建面向 总经理 的视图
create view view_manager
as select * from person
go
//查看总经理视图
select * from view_manager
//创建面向 人事部长 的视图
create view view_parker
as select person.p_id,person.p_name,person.p_sex,person.p_work
from person where person.p_park='人事部'
go
//查看面向 人事部长 的视图
select * from view_parker
//创建面向 普通员工 的视图
create view view_worker
as select person.p_id,person.p_name,person.p_work,person.p_salary
from person where person.p_name='张一'
//查看面向 普通员工 的视图
select * from view_worker
/*------------2009.8.31--存储过程-------------*/
use test
/*新建表*/
create table stuMarks
(ExamNo varchar(20),
stuNo varchar(20),
writtenExam int,
LabExam int
)
/*向表strMarks中多添加信息*/
insert stuMarks
select 'S271812','s25303',96,58 union
select 'S271813','s25302',66,90 union
select 'S271814','s25301',93,82 union
select 'S271815','s25328',61,65 union
select 'S271817','s25328',61,65 union
select 'S271818','s25328',61,65 union
select 'S271819','s25301',93,82 union
select 'S271822','s25301',93,82
/*写一个存储过程,用于查询平均分满足及格线的,所有学员的信息,即
根据每个人的课程求出平均分然后通过传入的平均分得出符合该平均分的学员成绩。
*/
drop proc proc_mark
/*新建存储过程*/
if exists( select * from sysobjects where name='proc_mark')
begin
drop proc proc_mark
end
go
create procedure proc_mark
@avgmark int=60
as
select * from stuMarks where (writtenExam+LabExam)/2>@avgmark
go
/*查询该存储过程*/
exec proc_mark @avgmark=80
-------------------触发器练习--9.2---------------
--创建触发器，用于insert新列值
create trigger trig_stuMarks
on stuMarks for insert
as
declare @stuNo char(20)
select @stuNo=stuNo from inserted
if not exists(select * from studentInfo where stuNo=@stuNo)
begin
insert into studentInfo values(@stuNo,null,null,null)
end
go
--如果已经存在存储过程
if exists (select * from sysobjects where name='proc_studentInfo')
begin
drop proc proc_studentInfo /*删除存储过程*/
print '成功删除存储过程：proc_studentInfo'
end
--创建一个存储过程,
create procedure proc_studentInfo
@checkStuNo varchar(20)
as
if exists (select * from studentInfo where stuNo=@checkStuNo)
begin
delete from studentInfo where stuNo=@checkStuNo /*删除表中测试列*/
print '成功删除'+@checkStuNo+'记录'
end
else
print '表studentInfo中不存在学员为 '+@checkStuNo+' 的记录'
--传入一个参数，查询是否已经存在于 studentInfo表中
exec proc_studentInfo 's25330'
select * from studentInfo
--创建触发器后，当向成绩表中插入新stuNo的记录时，触发studentInfo表，其自动添加该新stuNo学员记录
--测试触发器时，只需要重复执行下面代码
if not exists(select * from stuMarks where stuNo='s25330')
begin
insert into stuMarks values('S271833','s25330',80,90)
select * from stuMarks
select * from studentInfo
print '触发器向studentInfo表加入一行新记录，除了stuNo，其它列默认为空值'
end
else
begin
print '表stuMarks中已经存在 s25330 的记录'
delete from stuMarks where stuNo='s25330'
print '成功删除所有该记录'
end
分享到：
相关推荐
本文将针对"战胜SQL Server必做练习50题"这一主题，深入探讨SQL语言的核心知识点，并通过一系列实践题目来加深理解。 1. **SQL基础概念** - 数据库（Database）：存储数据的系统，SQL Server是其中一种。 - 表...
《SQL Server 2005管理员大全》是一本专为SQL Server 2005管理员设计的全面指南，旨在帮助读者深入理解并有效管理SQL Server 2005数据库系统。该书涵盖了一系列广泛的主题，从基础安装配置到高级性能优化，确保读者...
"SQL Server练习50题"是一个非常实用的资源，旨在帮助用户巩固SQL Server的基础知识，特别是查询和多表查询这两方面的重要技能。下面将对这两个主题进行详细讲解。 一、SQL Server查询基础 SQL（Structured Query ...
3. 查询语言：学习SQL Server 2005时，可以利用AdventureWorks进行SQL查询练习。例如，使用SELECT语句查询特定产品的销售情况，或者使用JOIN操作连接多个表以获取客户订单的详细信息。 4. 视图与存储过程：...
**SQL Server 2005基础教程PPT概述** SQL Server 2005是Microsoft公司推出的一款关系型数据库管理系统，广泛应用于数据存储、管理和分析。对于初学者来说，掌握SQL Server 2005的基础知识是至关重要的，因为它是...
### SQL Server 练习题知识点解析 #### 一、填空题知识点解析 1. **SQL Server 文件扩展名** - **主要数据文件**: `.mdf`，是SQL Server数据库的主要存储容器，包含数据库的启动信息及至少一个数据段。 - **次要...
SQL Server 2005是微软推出的一种关系型数据库管理系统，它在数据库管理、数据存储、数据处理等方面具有丰富的功能。以下将详细解释题目中涉及的一些核心知识点： 1. **数据完整性**：实体完整性和参照完整性是...
在SQL Server 2005中，数据库设计者和管理员经常使用各种约束来确保数据的完整性和一致性。约束是数据库模式定义的一部分，用于限制可插入或更新到表中的数据类型和范围。以下是对标题和描述中提及的几种SQL约束语句...
教程的最后部分通常会提供一系列上机练习和习题，旨在通过实践加深对SQLServer2005的理解和操作技能。上机练习可能包括创建数据库、设计表结构、编写查询语句、实现存储过程等实际操作；习题则可能涵盖理论知识的...
SQL Server 阶段项目练习题是一个综合性的练习项目，旨在提高初学 SQL Server 的学生的实践能力和学习效果。该项目涵盖了 SQL Server 的多个方面，包括数据库设计、表创建、触发器、索引、视图、存储过程等。 ...
这些课件可能涵盖了以上各个知识点，通过实例操作和练习，将理论知识与实践相结合，有助于更好地理解和掌握SQL Server 2005的使用。在学习过程中，建议动手实践，创建自己的数据库，编写SQL语句，以加深对概念的理解...
**SQL Server 2005 基本语法教程** SQL（Structured Query Language）是一种用于管理关系数据库的标准化语言，而SQL Server 2005是微软公司推出的一款数据库管理系统，它提供了强大的数据存储、管理和分析功能。...
本套练习题旨在帮助学者深入理解和掌握SQL Server中关于链表查询的相关技巧和方法。通过解决这些测试题，你可以提升在实际工作场景中处理复杂数据结构的能力。 链表查询，通常指的是在数据库中通过连接（JOIN）操作...
这个“sqlserver2008数据库实例练习-卡哥”提供了一个实践操作数据库的平台，尤其适合初学者理解和掌握SQL语言的基本操作。 1. **SQL基础操作**： - **查询（SELECT）**：这是SQL中最基本的操作，用于从表中获取...
同时，“在sqlserver2005上亲测，很给力”意味着这些练习经过实际测试，适用于SQL Server 2005，而且效果良好，对于初学者来说是极好的实践平台。 【标签】：“sql server”是本资料的关键标签，表明这些练习与...
总的来说，"sqlserver2008数据库实例练习"提供了丰富的实践机会，帮助你巩固和深化对SQL语言及数据库管理的理解，是提升数据库技能的理想平台。通过这个实例，你可以逐步提升自己的数据库设计、查询优化和数据管理...
本教程“SQL Server 2005数据库简明教程”电子教案将深入浅出地讲解以上各知识点，通过实例演示和练习，帮助学习者全面掌握SQL Server 2005的使用和管理，为成为合格的数据库管理员或开发者打下坚实基础。
本教程将引导您了解如何使用MFC连接到SQL Server 2005，实现一个简单的学生信息管理系统。 首先，我们需要创建一个MFC应用项目。在Visual Studio中，选择"新建项目"，然后在模板中找到"MFC应用程序"，按照向导的...
在SQLSERVER中，查询是数据库操作的核心部分，用于从数据中检索信息。下面将详细解析提供的练习题中涉及的SQL语句及其知识点： 1. 查询所有年龄在20岁以下的学生姓名及年龄： 使用`SELECT`和`WHERE`子句，结合`...