SQL基础语句

create database MySchool;
go

use MySchool;
go

create table tb_Student
(
StuId int primary key identity(1001, 1),
StuName varchar(8) not null,
StuGender char(2) check (StuGender in ('男', '女')),
StuRegDate date not null default(getdate()),
StuAddress varchar(100)
);
go

insert into tb_Student values ('罗浩', '男', DEFAULT, '四川成都');
insert into tb_Student values ('刘飞', '女', '2013-8-5', '四川成都');
insert into tb_Student values ('张洪久', '男', '2012-6-3', '云南大理');

update tb_Student set StuAddress='四川德阳' where StuName='罗浩';

select StuName, StuAddress from tb_Student;

--CRUD Create Read Update Delete

create table tb_Course
(
CourseId int primary key identity(1111, 1),
CourseName varchar(20) not null,
CourseCredit int not null check (CourseCredit between 1 and 5)
);
go

insert into tb_Course values ('Java程序设计', 3);
insert into tb_Course values ('HTML网页设计', 2);
insert into tb_Course values ('高等数学', 4);
insert into tb_Course values ('专业英语', 2);

select * from tb_Course;

create table tb_SC
(
[Sid] int,
Cid int,
Score float not null,
primary key ([Sid], Cid)
);
go

alter table tb_SC add constraint fk_01
foreign key ([Sid]) references tb_Student(StuId);


alter table tb_SC add constraint fk_02
foreign key (Cid) references tb_Course(CourseId);

insert into tb_SC values (1001, 1111, 95);
insert into tb_SC values (1002, 1113, 78);
insert into tb_SC values (1001, 1113, 80);

select * from tb_SC;

select StuName, CourseName, Score, CourseCredit
from tb_Student t1, tb_Course t2, tb_SC t3
where t3.Sid=t1.StuId and t3.Cid=t2.CourseId;