sqlserver数据库快速入门

--建数据库create database school--建表use schoolcreate table students(s_id int identity(1,1) primary key,--设主键，为自增ids_name varchar(20) not null,s_classId int)create table class(c_id int identity(1,1) primary key,--设主键，为自增idc_className varchar(20) not null)create table student_Score(ss_id int identity(1,1) primary key,--设主键，为自增idss_score varchar(200) not null,s_id int)--设外键use schoolalter table students add constraint FK_class_studets foreignkey(s_classId)references class(c_id)--设外键use schoolalter 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 ons.s_classId=c.c_idselect * from class as c left outer join students as s ons.s_classId=c.c_id--右外联接select * from students as s right outer join class as c ons.s_classId=c.c_idselect * from class as c right outer join students as s ons.s_classId=c.c_id--全联接select * from students as s full outer join class as c ons.s_classId=c.c_idselect * from class as c full outer join students as s ons.s_classId=c.c_id--交叉查询SELECT * FROM students CROSS JOIN classSELECT * FROM class CROSS JOIN students--触发器/*-------触发器------*//*触发器是一种特殊的存储过程，触发器是在对表进行插入、更新或删除操作时自动执行的存储过程触发器通常用于强制业务规则触发器是一种高级约束，可以定义比用CHECK 约束更为复杂的约束可执行复杂的SQL语句（if/while/case）可引用其它表中的列*/DELETE 触发器INSERT 触发器UPDATE 触发器*//*----------Insert 插入触发器------------*/Create Trigger Tri_inserton students /*针对某个表，触发器是建立在表关系上的*/for insert /*采用的是哪种触发器*/asdeclare @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_selStuasselect * from students;go--下面是测试存储过程execute pro_selStu;--带参数的存储过程/*（1）带输入参数的存储过程 */Create procedure pro_stu @stu_id intasselect * from students where s_id=@stu_id--下面是测试存储过程execute pro_stu 1;/*（2）带输出参数的存储过程*/Create procedure pro_stu_output @stu_sum int output,@stu_down_count int ,@stu_up_count intasselect @stu_sum=count(*) from students where s_id between@stu_down_count and @stu_up_countgo--下面是测试存储过程declare @stu_sum intexecute pro_stu_output @stu_sum output ,1,4 /*在调用含有输出参数的存储过程时要注意*/print @stu_sum带参数的存储过程在Java中的应用：/**create procedure ShipVia_Sum@via int,@sum money outputasselect @sum = sum(freight) from orderswhere 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();}}。