SQLserver（oracle）语句练习指南

　　SQL server (oracle)语句练习指南
　　！！！练习希望可以按顺序执行！！！
　　建表(一)：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));
　　插入数据（一）：insert into Teacher values (101,＂李华＂,＂讲师＂,＂计算机＂); insert into Teacher values (102,＂张丽＂,＂讲师＂,＂通信＂); insert into Teacher values (103,＂刘力伟＂,＂助教＂,＂计算机＂); insert into Teacher(Tno,Tname,Dept) values (104,＂李春生＂,＂计算机＂); insert into Teacher(Tno,Tname,Dept) values (105,＂王华英＂,＂自动化＂);
　　查询（一）：/*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;*/
　　建表（二）：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 Course values(1,101,＂数据库＂,3.5); insert into Course values(1,103,＂数据库＂,3.5); insert into Course values(2,102,＂网络＂,3); insert into Course values(2,101,＂网络＂,3); insert into Course values(3,103,＂操作系统＂,3);
　　查询（二）：select * from teacher,course; select * from teacher,course where teacher.tno=course.tno;
　　查询（三）：//select cname from course ; //select distinct cname from course; select * from teacher;
　　更新数据：//update teacher //set dept=＂通信工程＂ //where dept=＂通信＂;
　　删除数据：//delete from teacher where dept=＂计算机＂;
　　查询（四）：//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 bb where aa.tno=bb.tno;
　　查询（五）：//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=＂讲师＂;
　　查询（六）：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; //
　　新建视图：create view v_t_c as select Teacher.Tno,Tname,Title,Dept,Cno,Cname from Teacher,course where Teacher.Tno=course.Tno;
　　视图查询Select * from v_t_c; Select * from v_t_c where Tno=101; 认识NUll： create table Teacher( Tno integer Primary Key, Tname char(6) not null, Title char(6), Dept char(10)); insert into Teacher values (901,＂李华＂,＂讲师＂,＂计算机＂); insert into Teacher values (902,＂张丽＂,＂讲师＂,＂通信＂); insert into Teacher values (903,＂刘力伟＂,＂助教＂,＂计算机＂); insert into Teacher values (904,＂赵莺＂,null,＂计算机＂); insert into Teacher values (905,＂张大军＂,null,null); select * from teacher; Select * from teacher where title is null; select * from teacher where dept is not null;
　　外键1：create table father_t (Cno integer primary key, Cname char(10) not null, Credit numeric(3,1) ); insert into father_t values (1,＂数据库＂,2); insert into father_t values (2,＂网络＂,3);
　　外键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_t values (101,2,86); insert into son_t values (102,5,78);
　　查询（七）：select * from teacher; select title,count(*) from teacher group by title ; select title,count(*) from teacher group by title having count(*)>1;
　　触发器（建表）：create table Teacher( Tno integer Primary Key, Tname char(6) not null, Title char(6), Dept char(10)); insert into Teacher values (101,＂李华＂,＂讲师＂,＂计算机＂); insert into Teacher values (102,＂张丽＂,＂讲师＂,＂通信＂); insert into Teacher values (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 Course values(1,101,＂数据库＂,3.5); insert into Course values(1,103,＂数据库＂,3.5); insert into Course values(2,102,＂网络＂,3); insert into Course values(2,101,＂网络＂,3); insert into Course values(3,103,＂操作系统＂,3);
　　触发器（测试）：delete from teacher where tno=101; select * from teacher; select * from course; 触发器2-oracle create trigger trig_demo1 after delete on teacher for each row begin delete course where course.tno=:old.tno; end;
　　触发器2-SQL Server 2000:create trigger trig_demo1 on teacher for delete as delete course from course,deleted where course.tno=deleted.tno
　　触发器3(测试):select * from teacher; select * from course; update teacher set tno=110 where tno=103; select * from teacher; select * from course;
　　触发器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;
　　触发器3-SQL Server 2000create 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;
　　事务(SQL Server 2000)begin transaction select * from teacher; update teacher set title=null where tno=101; select * from teacher; rollback; select * from teacher;