SQL Server creates tables, inserts data, queries, creates triggers, and stores stored procedures.

Database creation
Create Database School
-- Create a table
Use school
Create Table students
(
S_id int identity () primary key, -- set the primary key as the auto-increment ID
S_name varchar (20) not null,
S_classid int
)
Create Table class
(
C_id int identity () primary key, -- set the primary key as the auto-increment ID
C_classname varchar (20) not null
)
Create Table student_score
(
Ss_id int identity () primary key, -- set the primary key as the auto-increment ID
Ss_score varchar (200) not null,
S_id int
)
-- Set a foreign key
Use school
Alter table students add constraint fk_class_studets foreign key (s_classid) References class (c_id)
-- Set a foreign key
Use school
Alter table student_score add constraint fk_students_student_score foreign key (s_id) References students (s_id)
-- Insert class data
Insert into class (c_classname) values ('08accp1 ')
Insert into class (c_classname) values ('08accp2 class ')
Insert into class (c_classname) values ('08accp3 class ')
Insert into class (c_classname) values ('08accp4 class ')
Insert into class (c_classname) values ('08 net editor Class 1 ')
Insert into class (c_classname) values ('08 Online Editing 2 class ')
Insert into class (c_classname) values ('08 soft start class 1 ')
Insert into class (c_classname) values ('08 soft open 2 class ')
Insert into class (c_classname) values ('08 soft start 3 class ')
Insert into class (c_classname) values ('08 Communication Manager Class 1 ')
-- Insert studets data
Insert into students (s_name, s_classid) values ('chen Zhijin ', 2)
Insert into students (s_name, s_classid) values ('He Jinxi ', 4)
Insert into students (s_name, s_classid) values ('yellow ruichi ', 5)

-- Single Table query

-- Query the studets table
Select * from students
-- Query the class table
Select * from Class

-- Multi-Table query

-- Inner join query
-- Case 1:
Select * from students as S, class as C where S. s_classid = C. c_id
-- Case 2:
Select * from students as s inner join class as C on (S. s_classid = C. c_id)

-- Outer join query
-- Left Outer Join
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
-- Outer right join
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
-- Full join
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

-- Cross Query
Select * from students cross join class
Select * from class cross join students

-- Trigger
/* ------- Trigger ------*/
/* A trigger is a special stored procedure,
A trigger is a stored procedure that is automatically executed when a table is inserted, updated, or deleted.
Triggers are usually used to force business rules.
A trigger is an advanced constraint that can define more complex constraints than a check constraint.
Execute complex SQL statements (if/while/case)
Columns in other tables can be referenced.
*/

/*
Delete trigger
Insert trigger
Update trigger
*/

/* ---------- Insert trigger ------------*/
Create trigger tri_insert
On students/* for a table, the trigger is created on the table relationship */
For insert/* which trigger is used */
As
Declare @ stu_id int;
Declare @ stu_score varchar (200 );
Select @ stu_id = s_id from inserted/* Here, inserted is the memory table automatically created when the trigger is created */
Insert into student_score (s_id, ss_score) values (@ stu_id, '123 ')

Go
-- Inserts a piece of data into students, And the tri_insert trigger will automatically insert a score data for student_score.
Insert into students (s_name, s_classid) values ('yellow Chi ', 6)

Select * From student_score

-- Stored Procedure

/*
Stored Procedure
1. stored procedures without Parameters
2. Stored Procedures with Parameters
*/
-- Stored procedure without Parameters
Create procedure pro_selstu
As
Select * from students;
Go
-- The following is the test stored procedure.
Execute pro_selstu;

-- Stored procedures with Parameters

/* (1) stored procedure with input parameters */
Create procedure pro_stu @ stu_id int
As
Select * from students where s_id = @ stu_id
Go
-- The following is the test stored procedure.
Execute pro_stu 1;

/* (2) stored procedure with output parameters */

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
-- The following is the test stored procedure.
Declare @ stu_sum int
Execute pro_stu_output @ stu_sum output,/* Note when calling a stored procedure containing output parameters */
Print @ stu_sum

 

 

Applications of stored procedures with parameters in Java:

/**
Create procedure shipvia_sum
@ Via int,
@ Sum money output
As
Select @ sum = sum (freight) from orders
Where shipvia = @
**/

 

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.exe cute ();
System. Out. println (CS. getdouble (2 ));
} Catch (exception ex ){
Ex. printstacktrace ();
}
}