sqlserver数据库快速入门.docxVIP

--建数据库create database school--建表use schoolcreate 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 schoolalter table students add constraint FK_class_studets foreign key(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 on s.s_classId=c.c_idselect * 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_idselect * 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_idselect * from class as c? full? outer join? students as s on s.s_classId=c.c_id--交叉查询SELECT * FROM? students?? CROSS