SQL server (oracle)语句练习案例

原创已于 2025-05-08 16:08:43 修改 · 383 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库 #sqlserver #oracle

于 2019-12-19 21:45:00 首次发布

笔记专栏收录该内容

8 篇文章

订阅专栏

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;