SQL server (oracle)語句練習(xí)指南?。?！
SQL server (oracle)語句練習(xí)指南
！?。【毩?xí)希望可以按順序執(zhí)行?。?！
建表(一)：
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));
插入數(shù)據(jù)（一）：
insert into Teacher
values
(101,'李華','講師','計(jì)算機(jī)');
insert into Teacher
values
(102,'張麗','講師','通信');
insert into Teacher
values
(103,'劉力偉','助教','計(jì)算機(jī)');
insert into Teacher(Tno,Tname,Dept)
values
(104,'李春生','計(jì)算機(jī)');
insert into Teacher(Tno,Tname,Dept)
values
(105,'王華英','自動(dòng)化');
查詢（一）：
/*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));
插入數(shù)據(jù)（二）：
insert into Course
values(1,101,'數(shù)據(jù)庫',3.5);
insert into Course
values(1,103,'數(shù)據(jù)庫',3.5);
insert into Course
values(2,102,'網(wǎng)絡(luò)',3);
insert into Course
values(2,101,'網(wǎng)絡(luò)',3);
insert into Course
values(3,103,'操作系統(tǒng)',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;
更新數(shù)據(jù)：
//update teacher
//set dept='通信工程'
//where dept='通信';
刪除數(shù)據(jù)：
//delete from teacher where dept='計(jì)算機(jī)';
查詢（四）：
//select * from course where credit >3;
//select * from course where credit between 2 and 3;
//select * from teacher where dept in('計(jì)算機(jī)','自動(dòng)化' ) ;
//select * from teacher where dept not in('計(jì)算機(jī)') ;
//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;
認(rèn)識(shí)NUll：
create table Teacher(
Tno integer Primary Key,
Tname char(6) not null,
Title char(6),
Dept char(10));
insert into Teacher
values
(901,'李華','講師','計(jì)算機(jī)');
insert into Teacher
values
(902,'張麗','講師','通信');
insert into Teacher
values
(903,'劉力偉','助教','計(jì)算機(jī)');
insert into Teacher
values
(904,'趙鶯',null,'計(jì)算機(jī)');
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,'數(shù)據(jù)庫',2);
insert into father_t
values
(2,'網(wǎng)絡(luò)',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;
觸發(fā)器（建表）：
create table Teacher(
Tno integer Primary Key,
Tname char(6) not null,
Title char(6),
Dept char(10));
insert into Teacher
values
(101,'李華','講師','計(jì)算機(jī)');
insert into Teacher
values
(102,'張麗','講師','通信');
insert into Teacher
values
(103,'劉力偉','助教','計(jì)算機(jī)');
insert into Teacher(Tno,Tname,Dept)
values
(104,'李春生','計(jì)算機(jī)');
insert into Teacher(Tno,Tname,Dept)
values
(105,'王華英','自動(dòng)化');
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,'數(shù)據(jù)庫',3.5);
insert into Course
values(1,103,'數(shù)據(jù)庫',3.5);
insert into Course
values(2,102,'網(wǎng)絡(luò)',3);
insert into Course
values(2,101,'網(wǎng)絡(luò)',3);
insert into Course
values(3,103,'操作系統(tǒng)',3);
觸發(fā)器（測(cè)試）：
delete from teacher where tno=101;
select * from teacher;
select * from course;
觸發(fā)器2-oracle
create trigger trig_demo1
after delete on teacher
for each row
begin
delete course
where course.tno=:old.tno;
end;
觸發(fā)器2-SQL Server 2000:
create trigger trig_demo1
on teacher
for delete
as
delete course
from course,deleted
where course.tno=deleted.tno
觸發(fā)器3(測(cè)試):
select * from teacher;
select * from course;
update teacher
set tno=110
where tno=103;
select * from teacher;
select * from course;
觸發(fā)器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;
觸發(fā)器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;
事務(wù)(SQL Server 2000)
begin transaction
select * from teacher;
update teacher
set title=null
where tno=101;
select * from teacher;
rollback;
select * from teacher;
