SQl基础命令语句记录

1.  创建数据库teaching(包括course,score,teacher,class,teach_class表)。

use master
go
if exists(select * from sysdatabases where name='teaching')
drop database teaching
create database teaching
on (name =teaching,filename ='g:/SQL/teaching.mdf')
log on(name=teaching_log,filename='g:/SQL/teaching_log.ldf')
2.利用T -SQL 语句在teaching数据库中基本操作。
a. 创建course表的结构:

create table course(
courseno nchar(6) not null,
cname nchar(20) not null,
type nchar(8) not null,
period tinyint not null,
credit numeric(4,1) not null,
constraint PK_course primary key clustered
(courseno ASC)
)
b.插入
insert course values('c1111','电子技术','必修',74,4)
c.查询语句例子
1). 查询student表中所有年龄大于20岁的男生的姓名和年龄。

select sname as 姓名,year(getdate())-year(birthday) as 年龄
from student
2).  查询选修课程且期末成绩不为空的学生人数。
select count(*)as 人数
from score
where final is not null and courseno in(select courseno from course where type='选修')
3).查询每名学生的学号、选修课程数目、总成绩,并将查询结果存放到生成的”学生选课统计表”中。
select studentno as 学号,count(*) as 选修课程数目,sum(final) as 总成绩
into 学生选课统计表
from score
groupby studentno
4).查询选修’c05109’课程,并且期末成绩在前5名的学生学号、课程号和期末成绩。
select top 5 studentno as 学生学号,coursenoas 课程号,final as 期末成绩
from score
order by final DESC
5). 查询课程号以‘c05’开头的,被3名及其以上学生选修且期末成绩的平均分高于75分的课程号、选修人数和期末成绩平均分,并按平均分降序排序。
select courseno 课程号,count(*) 选修人数,avg(final) 期末平均分
from score
where courseno like'c05%'
group by courseno
having count(*)>=3 and avg(final)>75
order by avg(final)desc
6).  查询所有08级学生的期末成绩平均分,要求利用computeby方法显示每一名学生的编号、课程号、期末成绩明细表,以及期末成绩平均分的汇总表。
select studentno 学生编号,courseno 课程号,final 期末成绩
from score
where'08'=substring(studentno,1,2)
compute avg(final)
d.更新
update student set sex='女'where studentno='0922221326'
e.删除
delete from student
where studentno='0922221324'
f.修改
①为student表增加民族nation列,数据类型为varchar(10),允许为空。 
alter table student  add nation varchar(10)
②修改student表中的nation列的数据类型为varchar(10)。
alter table student alter column nation varchar(12)
③删除student中的列nation。
alter table student drop column nation
g.视图
if object_id('v_age','view')is not null
drop view v_age
go
create view v_age
as
select studentno,sname,sex,birthday,classno
from student
where year(getdate())-year(birthday)>=18


drop view v_age
h.索引
利用T-SQL语句在teaching数据库中teacher表的tname列上创建非聚集唯一索引UQ_tname。若该索引已经存在,则删除后重建。
if exists(select name from sys.indexes where name='UQ_tname')
drop index UQ_tname on teacher
go
create unique nonclustered index UQ_tnameon teacher(tname)


drop index UQ_tnameon teacher
3.游标、触发器、存储过程、事务
a. 使用游标输出学生姓名、选修课程名称和期末考试成绩。

print'姓名    课程       期末成绩'
declare @sname varchar(20),@cname varchar(20),@final varchar(20)
declare stu_cursor cursor
for
select sname,cname,final
from student a join score b on a.studentno=b.studentno join course c on
b.courseno=c.courseno
open stu_cursor
fetch next from stu_cursor into @sname,@cname,@final
while @@fetch_status=0
begin
print @sname+@cname+@final
fetch next from stu_cursor into @sname,@cname,@final
end
close stu_cursor
deallocate stu_cursor
b. 利用T-SQL语句创建一个存储过程ProcAvg,查询指定班级指定课程的平均分。班级号和课程名称由输入参数给定,计算出的平均分通过输出参数返回。创建完后调用该存储过程。
create proc ProcAvg
@classno nchar(10),
@cname nchar(20),
@avg int output
as
select  @avg=avg(final)
from student a join score b on a.studentno=b.studentno join course c on b.courseno=c.courseno
where a.classno=@classnoand c.cname=@cname
 
declare @avg1 int
exec ProcAvg '080601','机械制图', @avg1 output
print @avg1
c.利用T-SQL语句创建一个AFTER触发器trigclassname,当向class表中插入或修改数据时,如果出现班级名称重复则回滚事物。测试该触发器。
create trigger trigclassname
on class
for insert,update
as
declare @classname1nchar(12)
select @classname1=classname from inserted
if exists(select classname from class
          where classname=@classname1 group by classname having count(*)>=2)
    begin
    raiserror('出现班级名称重复',16,1)
    rollback
    end
else
    raiserror('成功!',16,2)
d.
1).  定义一个事物,在teaching数据库的student表和score表中新增一名学生的基本信息记录和选课记录,并提交事物。

begin transaction trans_insert
insert into student values('0922221326','wyz2','男','1988-09-02','2','444','4','4');
insert into score values('0922221326','c05104','11','11');
commit tran trans_insert
2). 定义一个事物,将Student表中学号为’0922221326’的学生性别修改为’女’,并设置一个保存点,然后将score表中学号为’0922221326’的学生的期末成绩修改为88,最后回滚到保存点,提交事务。
begin transaction
update student set sex='女' where studentno='0922221326'
save tran savepoint
update score set final=88 where studentno='0922221326'
rollback tran savepoint
commit tran
4.权限分配
a.
grant insert,delete on student to U1
b.
revoke select on student from U1 cascade
c.
deny select to U1
5.备份、还原
a.对teaching数据库进行备份,使用T-SQL语言创建一个名为“teachbak”的备份设备,该设备在硬盘中所对应的完整文件名为“D:\SQL\teaching.bak”。
Exec sp_addump device'disk','teachbak','D:\SQL\teaching.bak'
backup database teaching to teachbak
b.使用T-SQL语言把“teaching”数据库备份到“D:\SQL\teaching.bak”文件中。
backup database teaching to disk='D:\SQL\teaching.bak'
c.使用T-SQL语言把上题的备份恢复到“teaching”数据库。
restore database teaching from disk='D:\SQL\teaching.bak'with file=1







阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/wyz365889/article/details/8948825
文章标签: SQL 数据库 select AVG
个人分类: Ivan@_Learning专栏
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭