本文共 5586 字，大约阅读时间需要 18 分钟。

SQL server (oracle)语句练习指南

！！！练习希望可以按顺序执行！！！

1. 建表(一)：

   create table Teacher(Tno integer ,Tname char(6) ,Title char(6),Dept char(10));create table Teacher(Tno integer Primary Key,Tname char(6) not null,Title char(6),Dept char(10));

2. 插入数据（一）：

   insert into Teachervalues(101,'李华','讲师','计算机');insert into Teachervalues(102,'张丽','讲师','通信');insert into Teachervalues(103,'刘力伟','助教','计算机');insert into Teacher(Tno,Tname,Dept)values(104,'李春生','计算机');insert into Teacher(Tno,Tname,Dept)values(105,'王华英','自动化');

3. 查询（一）：

   /*select * from teacher;*//*select * from teacher where dept='通信';*//*select distinct dept from teacher;*//*select count(*) from teacher;*//*select count(distinct dept) from teacher;*//*select * from teacher aa,teacher bb where aa.tno=bb.tno;*/

4. 建表（二）：

   create Table Course(Cno integer not null,Tno integer not null,Cname char(10) not null,credit numeric(3,1) not null,Primary key(cno,tno));

5. 插入数据（二）：

   insert into Coursevalues(1,101,'数据库',3.5);insert into Coursevalues(1,103,'数据库',3.5);insert into Coursevalues(2,102,'网络',3);insert into Coursevalues(2,101,'网络',3);insert into Coursevalues(3,103,'操作系统',3);

6. 查询（二）：

   select * from teacher,course;select * from teacher,coursewhere teacher.tno=course.tno;

7. 查询（三）：

   //select cname from course ;//select distinct cname from course;select * from teacher;

8. 更新数据：

   //update teacher	//set dept='通信工程'	//where dept='通信';

9. 删除数据：

   //delete from teacher where dept='计算机';

10. 查询（四）：

    //select * from course where credit >3;//select * from course where credit between 2 and 3;//select * from teacher where dept in('计算机','自动化' ) ;//select * from teacher where dept not in('计算机') ;//select * from teacher where tname like '李%' ;//select * from teacher where title is null ;//select * from teacher order by tno desc ;//select * from teacher order by title ;//select count(*) from teacher;//select count(distinct cname) from course;select * from course aa, course bbwhere aa.tno=bb.tno;

11. 查询（五）：

    //select * from course//    where Tno in ( select Tno //                    from Teacher//                     where Tname='李华');//////select * from teacher,course//   where (teacher.tno=course.tno) and Tname='李华';////select * from course//    where Tno in ( select Tno //                    from Teacher//                     where Title='讲师');select * from teacher,course   where (teacher.tno=course.tno) and Title='讲师';

12. 查询（六）：

    select Distinct Tno from course  where 2<=(select count(*) from Course aa                where aa.Tno=course.tno);//select count(*) from Course aa//                where Tno=102;//

13. 新建视图：

    create view v_t_c   as       select Teacher.Tno,Tname,Title,Dept,Cno,Cname        from Teacher,course          where Teacher.Tno=course.Tno;

14. 视图查询

    Select * from v_t_c;Select * from v_t_c where Tno=101;

15. 认识NUll：

    create table Teacher(Tno integer Primary Key,Tname char(6) not null,Title char(6),Dept char(10));insert into Teachervalues(901,'李华','讲师','计算机');insert into Teachervalues(902,'张丽','讲师','通信');insert into Teachervalues(903,'刘力伟','助教','计算机');insert into Teachervalues(904,'赵莺',null,'计算机');insert into Teachervalues(905,'张大军',null,null);select * from teacher;Select * from teacher where title is null;select * from teacher where dept is not null;

16. 外键1：

    create table father_t(Cno integer primary key, Cname char(10) not null, Credit numeric(3,1) );insert into father_tvalues(1,'数据库',2);insert into father_tvalues(2,'网络',3);

17. 外键2：

    create table son_t(st_no integer primary key, fk_cno integer, grade integer, foreign key(fk_cno) references father_t(Cno));insert into son_tvalues(101,2,86);insert into son_tvalues(102,5,78);

18. 查询（七）：

    select * from teacher;select title,count(*) from teacher group by title ;select title,count(*) from teacher group by title having count(*)>1;

19. 触发器（建表）：

    create table Teacher(Tno integer Primary Key,Tname char(6) not null,Title char(6),Dept char(10));insert into Teachervalues(101,'李华','讲师','计算机');insert into Teachervalues(102,'张丽','讲师','通信');insert into Teachervalues(103,'刘力伟','助教','计算机');insert into Teacher(Tno,Tname,Dept)values(104,'李春生','计算机');insert into Teacher(Tno,Tname,Dept)values(105,'王华英','自动化');create Table Course(Cno integer not null,Tno integer not null,Cname char(10) not null,credit numeric(3,1) not null,Primary key(cno,tno));insert into Coursevalues(1,101,'数据库',3.5);insert into Coursevalues(1,103,'数据库',3.5);insert into Coursevalues(2,102,'网络',3);insert into Coursevalues(2,101,'网络',3);insert into Coursevalues(3,103,'操作系统',3);

20. 触发器（测试）：

    delete from teacher where tno=101; select * from teacher;select * from course;

21. 触发器2-oracle

    create trigger trig_demo1after delete on teacherfor each rowbegin  delete course     where course.tno=:old.tno;end;

22. 触发器2-SQL Server 2000:

    create trigger trig_demo1  on teacher   for deleteas  delete course    from course,deleted        where course.tno=deleted.tno

23. 触发器3(测试):

    select * from teacher;select * from course; update teacher  set tno=110where tno=103;select * from teacher;select * from course;

24. 触发器3-oracle:

    create trigger trig_demo2   after update on teacher   for each row     begin        update course        set course.Tno=:new.Tno     where course.Tno=:old.Tno;   end;

25. 触发器3-SQL Server 2000

    create trigger trig_demo2   on teacher   for update   as   if update(Tno)   begin      Declare @old_Tno integer,@new_Tno integer      select @old_Tno=Tno         from deleted;      select @new_Tno=Tno         from inserted;       update course        set course.Tno=@new_Tno     where course.Tno=@old_Tno;   end;

26. 事务(SQL Server 2000)

    begin transactionselect * from teacher;update teacher  set title=null     where tno=101;select * from teacher;rollback;select * from teacher;

转载地址：http://pgozi.baihongyu.com/