SQl基础命令语句记录

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

[sql]  view plain copy print ?

1. use master  
2. go  
3. if exists(select * from sysdatabases where name='teaching')  
4. drop database teaching  
5. create database teaching  
6. on (name =teaching,filename ='g:/SQL/teaching.mdf')  
7. log on(name=teaching_log,filename='g:/SQL/teaching_log.ldf')  

2.利用T -SQL 语句在teaching数据库中基本操作。
a. 创建course表的结构：

[sql]  view plain copy print ?

1. create table course(  
2. courseno nchar(6) not null,  
3. cname nchar(20) not null,  
4. type nchar(8) not null,  
5. period tinyint not null,  
6. credit numeric(4,1) not null,  
7. constraint PK_course primary key clustered  
8. (courseno ASC)  
9. )  

b.插入

[sql]  view plain copy print ?

1. insert course values('c1111','电子技术','必修',74,4)  

c.查询语句例子
1). 查询student表中所有年龄大于20岁的男生的姓名和年龄。

[sql]  view plain copy print ?

1. select sname as 姓名,year(getdate())-year(birthday) as 年龄  
2. from student  

2).  查询选修课程且期末成绩不为空的学生人数。

[sql]  view plain copy print ?

1. select count(*)as 人数  
2. from score  
3. where final is not null and courseno in(select courseno from course where type='选修')  

3).查询每名学生的学号、选修课程数目、总成绩，并将查询结果存放到生成的”学生选课统计表”中。

[sql]  view plain copy print ?

1. select studentno as 学号,count(*) as 选修课程数目,sum(final) as 总成绩  
2. into 学生选课统计表  
3. from score  
4. groupby studentno  

4).查询选修’c05109’课程，并且期末成绩在前5名的学生学号、课程号和期末成绩。

[sql]  view plain copy print ?

1. select top 5 studentno as 学生学号,coursenoas 课程号,final as 期末成绩  
2. from score  
3. order by final DESC  

5). 查询课程号以‘c05’开头的，被3名及其以上学生选修且期末成绩的平均分高于75分的课程号、选修人数和期末成绩平均分，并按平均分降序排序。

[sql]  view plain copy print ?

1. select courseno 课程号,count(*) 选修人数,avg(final) 期末平均分  
2. from score  
3. where courseno like'c05%'  
4. group by courseno  
5. having count(*)>=3 and avg(final)>75  
6. order by avg(final)desc  

6).  查询所有08级学生的期末成绩平均分，要求利用computeby方法显示每一名学生的编号、课程号、期末成绩明细表，以及期末成绩平均分的汇总表。

[sql]  view plain copy print ?

1. select studentno 学生编号,courseno 课程号,final 期末成绩  
2. from score  
3. where'08'=substring(studentno,1,2)  
4. compute avg(final)  

d.更新

[sql]  view plain copy print ?

1. update student set sex='女'where studentno='0922221326'  

e.删除

[sql]  view plain copy print ?

1. delete from student  
2. where studentno='0922221324'  

f.修改

[sql]  view plain copy print ?

1. ①为student表增加民族nation列，数据类型为varchar(10)，允许为空。   
2. alter table student  add nation varchar(10)  
3. ②修改student表中的nation列的数据类型为varchar(10)。  
4. alter table student alter column nation varchar(12)  
5. ③删除student中的列nation。  
6. alter table student drop column nation  

g.视图

[sql]  view plain copy print ?

1. if object_id('v_age','view')is not null  
2. drop view v_age  
3. go  
4. create view v_age  
5. as  
6. select studentno,sname,sex,birthday,classno  
7. from student  
8. where year(getdate())-year(birthday)>=18  
9.   
10.   
11. drop view v_age  

h.索引
利用T-SQL语句在teaching数据库中teacher表的tname列上创建非聚集唯一索引UQ_tname。若该索引已经存在，则删除后重建。

[sql]  view plain copy print ?

1. if exists(select name from sys.indexes where name='UQ_tname')  
2. drop index UQ_tname on teacher  
3. go  
4. create unique nonclustered index UQ_tnameon teacher(tname)  
5.   
6.   
7. drop index UQ_tnameon teacher  

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

[sql]  view plain copy print ?

1. print'姓名　　　　课程　　　　　　　期末成绩'  
2. declare @sname varchar(20),@cname varchar(20),@final varchar(20)  
3. declare stu_cursor cursor  
4. for  
5. select sname,cname,final  
6. from student a join score b on a.studentno=b.studentno join course c on  
7. b.courseno=c.courseno  
8. open stu_cursor  
9. fetch next from stu_cursor into @sname,@cname,@final  
10. while @@fetch_status=0  
11. begin  
12. print @sname+@cname+@final  
13. fetch next from stu_cursor into @sname,@cname,@final  
14. end  
15. close stu_cursor  
16. deallocate stu_cursor  

b. 利用T-SQL语句创建一个存储过程ProcAvg，查询指定班级指定课程的平均分。班级号和课程名称由输入参数给定，计算出的平均分通过输出参数返回。创建完后调用该存储过程。

[sql]  view plain copy print ?

1. create proc ProcAvg  
2. @classno nchar(10),  
3. @cname nchar(20),  
4. @avg int output  
5. as  
6. select  @avg=avg(final)  
7. from student a join score b on a.studentno=b.studentno join course c on b.courseno=c.courseno  
8. where a.classno=@classnoand c.cname=@cname  
9.    
10. declare @avg1 int  
11. exec ProcAvg '080601','机械制图', @avg1 output  
12. print @avg1  

c.利用T-SQL语句创建一个AFTER触发器trigclassname,当向class表中插入或修改数据时，如果出现班级名称重复则回滚事物。测试该触发器。

[sql]  view plain copy print ?

1. create trigger trigclassname  
2. on class  
3. for insert,update  
4. as  
5. declare @classname1nchar(12)  
6. select @classname1=classname from inserted  
7. if exists(select classname from class  
8.           where classname=@classname1 group by classname having count(*)>=2)  
9.     begin  
10.     raiserror('出现班级名称重复',16,1)  
11.     rollback  
12.     end  
13. else  
14.     raiserror('成功!',16,2)  

d.
1).  定义一个事物，在teaching数据库的student表和score表中新增一名学生的基本信息记录和选课记录，并提交事物。

[sql]  view plain copy print ?

1. begin transaction trans_insert  
2. insert into student values('0922221326','wyz2','男','1988-09-02','2','444','4','4');  
3. insert into score values('0922221326','c05104','11','11');  
4. commit tran trans_insert  

2). 定义一个事物，将Student表中学号为’0922221326’的学生性别修改为’女’，并设置一个保存点，然后将score表中学号为’0922221326’的学生的期末成绩修改为88,最后回滚到保存点，提交事务。

[sql]  view plain copy print ?

1. begin transaction  
2. update student set sex='女' where studentno='0922221326'  
3. save tran savepoint  
4. update score set final=88 where studentno='0922221326'  
5. rollback tran savepoint  
6. commit tran  

4.权限分配

[sql]  view plain copy print ?

1. a.  
2. grant insert,delete on student to U1  
3. b.  
4. revoke select on student from U1 cascade  
5. c.  
6. deny select to U1  

5.备份、还原
a.对teaching数据库进行备份，使用T-SQL语言创建一个名为“teachbak”的备份设备，该设备在硬盘中所对应的完整文件名为“D:\SQL\teaching.bak”。

[sql]  view plain copy print ?

1. Exec sp_addump device'disk','teachbak','D:\SQL\teaching.bak'  
2. backup database teaching to teachbak  

b．使用T-SQL语言把“teaching”数据库备份到“D:\SQL\teaching.bak”文件中。

[sql]  view plain copy print ?

1. backup database teaching to disk='D:\SQL\teaching.bak'  

c．使用T-SQL语言把上题的备份恢复到“teaching”数据库。

[sql]  view plain copy print ?

1. restore database teaching from disk='D:\SQL\teaching.bak'with file=1