SQL server database creation, table creation, and creation Constraints

SQL server database creation, table creation, and creation Constraints

The following describes SQL server database creation, table creation, and constraints.

-- Before creating the School database: first, determine whether the database exists. If so, delete the database and create it. If not, create the database --
-- Exists Keyword: If data can be queried in parentheses, 'true' is returned; otherwise, 'false' is returned'

If exists (select * from sysdatabases where name = 'school ') -- exists returns 'true', then the database deletion operation -- drop database School -- exists returns 'false', indicating that the database does not exist, directly create database Schoolon primary (-- primary database file -- name = 'school ', -- Logical name of the primary data file fileName = 'd: \ project \ School. mdf ', -- physical logic name of the primary data file size = 5 MB, -- Initial Value size maxsize = 100 MB, -- maximum size filegrowth = 15% -- data file growth volume) log on (-- log file -- name = 'school _ log', filename = 'd: \ project \ School_log.ldf ', size = 2 MB, filegrowth = 1 MB) go

---------------------------------------- Use a T-SQL to create an employee database ------------------------------------

Create database employeeon primary (-- main data file -- name = 'employee1', filename = 'd: \ project \ employee1.mdf ', size = 10 MB, filegrowth = 10% ), (-- secondary data file -- name = 'employee2', filename = 'd: \ project \ employee2.ndf ', size = 20 MB, maxsize = 100 MB, filegrowth = 1 MB) log on (-- first log file -- name = 'employee _ log1', filename = 'd: \ project \ employee_log1.ldf ', size = 10 MB, filegrowth = 1 MB ), (-- second log file -- name = 'employee _ log2', filename = 'd: \ project \ employee_log2.ldf ', size = 10 MB, maxsize = 50 MB, filegrowth = 1 MB)

--------------------------------- Query existing database information ---------------------------

select * from sysdatabases

--------------------------------- Delete a database ------------------------------------
Copy codeThe Code is as follows:
Drop database School

--------------------------------- Create a Student database table ----------------------------
Copy codeThe Code is as follows:
-- 1. Select the database to operate --
Use School
Go

-- Determine whether a table exists --
Copy codeThe Code is as follows:
If exists (select * from sysobjects where name = 'student ')
Drop table Student

-- 2. Create a table ---

Create table Student (-- feature of a specific column name data type column (whether it is empty) -- StudentNo int identity () not null, LoginPwd nvarchar (20) not null, studentName nvarchar (20) not null, Sex int not null, GradeId int not null, phone nvarchar (50) not null, BornDate datetime not null, Address nvarchar (255 ), email nvarchar (50), IDENTITYcard varchar (18) go

--- View all database objects (database tables )---
Copy codeThe Code is as follows:
Select * from sysobjects
Drop table Student

-------------------- Create a subject curriculum -------------------
Copy codeThe Code is as follows:
----- 1. Determine whether a table exists. If yes, delete the table and create another table. If no table exists, create -------- directly --------
If exists (select * from sysobjects where name = 'subobject ')
Drop table subject

Use School
Go

--- Create a subject curriculum --
Create table subject
(
SubjectNo int not null identity (1, 1 ),
SubjectName nvarchar (50 ),
ClassHour int,
GradeID int
)

---------------------------------------- Create the Result tables Table -------------------
Copy codeThe Code is as follows:
----- 1. Determine whether a table exists. If yes, delete the table and create another table. If no table exists, create -------- directly --------
If exists (select * from sysobjects where name = 'result ')
Drop table Result
Use School
Go

--- Create a Result generator table --
Copy codeThe Code is as follows:
Create table Result
(
StudentNo int not null,
SubjectNo int not null,
ExamDate Datetime not null,
StudentResult int not null
)

--------------------------------------- Create the Grande grade Table -------------------
Copy codeThe Code is as follows:
----- 1. Determine whether a table exists. If yes, delete the table and create another table. If no table exists, create -------- directly --------
If exists (select * from sysobjects where name = 'grade ')
Drop table Grade
Use School
Go

--- Create a Grande grade Table --
Copy codeThe Code is as follows:
Create table Grade
(
GradeId int not null,
GrandeName nvarchar (50)
)

----------------------------------------- T-SQL add constraints -------------------------
Copy codeThe Code is as follows:
-- Add a primary key constraint to StudentNo ---
Alter table Student
Add constraint pk_StuNo primary key (StudentNo)

-- Add a unique constraint to the ID card --
Copy codeThe Code is as follows:
Alter table Student
Add constraint uq_StuIdcard unique (IDENTITYcard)

--- Add default constraints to address --
Copy codeThe Code is as follows:
Alter table Student
Add constraint df_stuaddress default ('address unknown ') for Address

--- Delete address default constraints ---
Copy codeThe Code is as follows:
Alter table Student
Drop constraint df_stuaddress

---------- Add constraints for health check on birthdate --------
Copy codeThe Code is as follows:
Alter table Student
Add constraint ck_stuBorndate check (Borndate> '2017-01-01 ')

--------- Establish a primary-foreign key relationship with Grand (grade Table --------

-- 1. Add the Grade primary key (operate Grade )---
Copy codeThe Code is as follows:
Alter table Grade
Add constraint pk_graid primary key (GradeId)

-- 2. Add the Grade foreign key (operate Student )--
Copy codeThe Code is as follows:
Alter table Student
Add constraint fk_stuGradeID foreign key (GradeId) references Grade (GradeId)

----------------- Add constraints to the subject curriculum -----------------------

Copy codeThe Code is as follows:
---- Add a primary key constraint to the subjectNo column ------
Alter table subject
Add constraint pk_SubID primary key (SubjectNo)

------ Add a non-empty constraint to the course name subjectName ;-----
Copy codeThe Code is as follows:
----- With nocheck: Existing data does not pass the check constraint -------
Alter table subject with nocheck
Add constraint ck_subName check (SubjectName is not null)

----- The course hours must be greater than 0 -----
Copy codeThe Code is as follows:
Alter table subject with nocheck
Add constraint ck_ClassHour check (ClassHour> 0)

----- Add the primary and foreign key constraints to the Grade Table ----
Copy codeThe Code is as follows:
Alter table subject with nocheck
Add constraint fk_GradeID foreign key (GradeID)
References Grade (GradeID)

---------- Add constraints to the result sheet table ------------

------- Add multiple constraints ---------
Copy codeThe Code is as follows:
Alter table Result
Add
Constraint pk_No_subID_date primary key (StudentNo, SubjectNo, ExamDate ),
Constraint df_examdate default (getdate () for ExamDate,
Constraint ck_StudentResult check (StudentResult between 0 and 100 ),
Constraint fk_StuNo foreign key (StudentNo) references Student (StudentNo ),
Constraint fk_subNo foreign key (SubjectNo) references Subject (SubjectNo)

-- Delete multiple constraints --
Copy codeThe Code is as follows:
Alter table Result
Drop constraint pk_No_subID_date, fk_subNo, fk_StuNo, ck_StudentResult, df_examdate

-------- Change the column data type ----------
Copy codeThe Code is as follows:
Alter table Result
Alter column StudentResult int

The above is all the content in this article. I hope you will like it.