Four commonly used database tables
There are several tables that are often used in our learning process: Students table, Classes table, Scores table, Subjects table
Students table
Structure of the table and constraints.
StudentNo(PK not null, )
StudentName(nchar(10) ,)
LoginPasswordLoginPwd(nchar(10) not null,)
AgeAge(int 0~100,)
GenderSex(bit,)
ClassNumberCLassId(FK int ,)
PhonePhone(int(10),)
AddressAddress(nvarchar(50),)
BirthdayBirthdays(date,)
EmailEmail(nvarchar(10),)
To delete or not to deleteIsDel(default('False'))
The form creation code is as follows.
use TextSchool
--Create table Students
if exists(select * from sysobjects where name = 'Students')
drop table Students
go
Create table Students
(
StudentNo int not null Primary Key ,
StudentName nvarchar(50),
LoginPwd nchar(10) not null,
Age int,
Sex bit,
Classid int,
Phone int,
Adress nvarchar(50),
Birthday date,
Email nchar(10),
IsDel bit default('False')
)
The constraint is created as follows.
--add constraint
if exists(select * from sysobjects where name='CK_Students_Age')
alter table Students
drop constraint CK_Students_Age
alter table Students
add constraint CK_Students_Age check(0
go
if exists(select * from sysobjects where name ='FK_Students_Classid')
alter table Students
drop constraint FK_Studnets_Classid
alter table Students
with nocheck
add constraint FK_Students_Classid foreign key(Classid) references Classes(CID)
ondelete set null
Classes table
Structure of the table and constraints.
Class number CLassId( int ,)
ClassName (nchar(10))
The form creation code is as follows.
use TextSchool
if exists(select * from sysobjects where name = 'Classes')
drop table Classes
create table Classes
(
Classid int primary key ,
ClassName nchar(10)
)
go
Scoresheet
Structure of the table and constraints.
Identifies the column Id(int identity(1,1),)
StudentId(int,)
CourseIdSubjectId(int, FK)
StudentScores(int ,)
ExamDate(date)
The form creation code is as follows.
use TextSchool
if exists(select * from sysobjects where name ='Scores')
drop table Scores
create table Scores
(
Id int identity(1,1),
StudentNo int ,
SubjectId int ,
StudentScores int,
ExamDate date
)
Table of Subjects
Structure of the table and constraints.
Course number SubjectId (int not null PK,)
CourseNameSubjectName (nvarchar(10) ,)
Course Hours ClassHour (int, )
Course class number ClassId(int )
The form creation code is as follows.
use TextSchool
if exists(select * from sysobjects where name ='Subjects')
drop table Subjects
create table Subjects
(
SubjectId int not null ,
SubjectName nvarchar(10),
ClassHour int,
ClassId int
)
go
The constraint is created as follows.
--add constraint **table Subjects
if exists(select * from sysobjects where name='PK_Subjects_SubjectId')
alter table Subjects
drop constraint PK_Subjects_SubjectId
alter table Subjects
add constraint PK_Subjects_SubjectId primary key (SubjectId)
go
For details on how to create databases and tables see.
SQL statement to create form table
SQL statements to create and delete databases
Introduction to SQL Data Integrity and Creating Constraints with SQL Statements
Recommended>>
1、Python爬虫之BeautifulSoup详解
2、What exactly is blockchain Read the comic in seconds
3、Aliens can hack Earths computers Its not a science fiction scenario but scientists have recently laid out theories
4、Warmly celebrate the establishment of Security Cloud Service Operation and Monitoring Center Henan Branch
5、Sprint Polytron Unveils LIDAR and Camera Fusion Technology and HD Map Based Perception System