SQL server (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 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;

事务(SQL Server 2000)

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