SQLServer建立表，插入資料，查詢，建立觸發器，預存程序

建資料庫
create database school
--建表
use school
create table students
(
 s_id int identity(1,1) primary key,--設主鍵，為自增id
 s_name varchar(20) not null,
 s_classId int
)
create table class
(
 c_id int identity(1,1) primary key,--設主鍵，為自增id
 c_className varchar(20) not null
)
create table student_Score
(
 ss_id  int identity(1,1) primary key,--設主鍵，為自增id
 ss_score varchar(200) not null,
 s_id int
)
--設外鍵
use school
alter table students add constraint FK_class_studets foreign key(s_classId)references class(c_id)
--設外鍵
use school
alter table student_Score add constraint FK_students_student_Score foreign key(s_id)references students(s_id)
--插class資料
insert into class(c_className) values('08ACCP1班')
insert into class(c_className) values('08ACCP2班')
insert into class(c_className) values('08ACCP3班')
insert into class(c_className) values('08ACCP4班')
insert into class(c_className) values('08網編1班')
insert into class(c_className) values('08網編2班')
insert into class(c_className) values('08軟開1班')
insert into class(c_className) values('08軟開2班')
insert into class(c_className) values('08軟開3班')
insert into class(c_className) values('08信管1班')
--插studets資料
insert into students(s_name,s_classId) values('陳志錦',2)
insert into students(s_name,s_classId) values('何金喜',4)
insert into students(s_name,s_classId) values('黃瑞馳',5)

--單表查詢

--查studets表
select * from students
--查class表
select * from class

--多表查詢

--內聯結查詢
--情況1：
select * from students as s,class as c where s.s_classId=c.c_id
--情況2：
select * from students as s inner join class as c  on(s.s_classId=c.c_id)

--外聯結查詢
--左外聯結
select * from students as s left outer join class as c on s.s_classId=c.c_id
select * from class as c left outer join students as s on s.s_classId=c.c_id
--右外聯結
select * from students as s right outer join class as c on s.s_classId=c.c_id
select * from class  as c right outer join students as s on s.s_classId=c.c_id
--全聯結
select * from students as s  full outer join  class as c  on s.s_classId=c.c_id
select * from class as c  full  outer join  students as s on s.s_classId=c.c_id

--交叉查詢
SELECT * FROM  students   CROSS   JOIN   class
SELECT * FROM  class   CROSS   JOIN   students

--觸發器
/*-------觸發器------*/
/*觸發器是一種特殊的預存程序，
觸發器是在對錶進行插入、更新或刪除操作時自動執行的預存程序
觸發器通常用於強制商務規則
觸發器是一種進階約束，可以定義比用CHECK 條件約束更為複雜的約束
可執行複雜的SQL語句（if/while/case）
可引用其它表中的列
*/

/*
DELETE 觸發器
INSERT 觸發器
UPDATE 觸發器
*/

/*----------Insert 插入 觸發器------------*/
Create Trigger Tri_insert
on students /*針對某個表，觸發器是建立在表關係上的*/
for insert /*採用的是哪種觸發器*/
as
 declare @stu_id int;
 declare @stu_score varchar(200);
 select @stu_id=s_id from inserted /*這裡的Inserted 是在建立觸發器時候 系統自動建立的記憶體表*/
 insert into student_Score(s_id,ss_score)values(@stu_id,'100')

go
--就是插一條資料進入students中，Tri_insert觸發器就會自動在student_Score插入相關的學生的一條分數的資料
insert into students(s_name,s_classId) values('黃馳',6)

select * from student_Score

--預存程序

/*
預存程序
1.不帶參數的預存程序
2.帶參數的預存程序
*/
--不帶參數的預存程序
create procedure pro_selStu
as
  select * from students;
go
--下面是測試預存程序
execute pro_selStu;

--帶參數的預存程序

/*（1）帶輸入參數的預存程序 */
Create procedure pro_stu @stu_id int
as
 select * from students where s_id=@stu_id
go
--下面是測試預存程序
execute pro_stu 1;

/*（2）帶輸出參數的預存程序*/

Create procedure pro_stu_output @stu_sum int output,@stu_down_count int ,@stu_up_count int

as
 select @stu_sum=count(*) from students where s_id between @stu_down_count and @stu_up_count
go
--下面是測試預存程序
declare @stu_sum int
execute pro_stu_output @stu_sum output ,1,4 /*在調用含有輸出參數的預存程序時 要注意*/
print @stu_sum

 

 

帶參數的預存程序在Java中的應用：

/**
create   procedure   ShipVia_Sum
@via   int,
@sum   money   output
as
select   @sum   =   sum(freight)   from   orders
where   shipVia   =   @via
**/

 

public   static   void   procedureOutput()   {

try   {
Connection   conn   =   JDBC_ODBC.getConnection();
System.out.println( "conn   is: "   +   conn);
String   call   =   "{call   ShipVia_Sum(?,?)} ";
CallableStatement   cs   =   conn.prepareCall(call);
cs.setInt(1,   1);
cs.registerOutParameter(2,   java.sql.Types.DOUBLE);
cs.execute();
System.out.println(cs.getDouble(2));
}   catch   (Exception   ex)   {
ex.printStackTrace();
}
}