SQL Server database Building, table building, constraint techniques _mssql

Below to share the next SQL Server Building, building tables, building constraints skills, the following text has code.

--Before creating a school database: First, determine if the database exists, delete it if it exists, and create it if it does not exist-
--exists keyword: The parentheses inside can query to the data to return ' true ' otherwise return ' false '

if exists (SELECT * from sysdatabases where name = ' School ')
--exists return ' true ' to perform the delete database operation--
drop DB school
   --exists returns ' false ' to indicate that the database does not exist and directly creates the Create 
database School on
primary
(--The
primary database file--
name = ' School ',--main data file logical name
filename = ' D:\project\School.mdf ',--master data file physical logical name
size = 5MB,--Initial value size
maxsize = 100MB ,--Maximum size
filegrowth = 15%--Data file growth
) Log on (--
log file--
name = ' School_log ',
filename = ' D:\project\School_log.ldf ',
size = 2MB,
filegrowth = 1MB
)
go

----------------------------------------use T-SQL to create an employee database------------------------------------

Create DATABASE employee
on primary
(--
Primary data file--
name = ' employee1 ',
filename = ' D:\project\ Employee1.mdf ',
size = 10MB,
filegrowth = 10%
),
(--
secondary data file--
name = ' Employee2 ',
filename = ' D:\project\employee2.ndf ',
size = 20MB,
maxsize = 100MB,
filegrowth = 1MB
)
Log on (--The
first log file--
name = ' Employee_log1 ',
filename = ' D:\project\employee_log1.ldf ',
size = 10MB,
filegrowth = 1MB
),
(--
second log file--
name = ' employee_log2 ',
filename = ' D:\project \employee_log2.ldf ',
size = 10MB,
maxsize = 50MB,
filegrowth = 1MB
)

---------------------------------query for database information that already exists---------------------------

SELECT * FROM sysdatabases

---------------------------------Delete a database------------------------------------

Copy Code code as follows:

Drop Database School

---------------------------------Create a Student database table----------------------------

Copy Code code as follows:

--1, select the Operation Database--
Use School
Go

--to determine whether a table exists--

Copy Code code as follows:

if exists (select * from sysobjects where name = ' Student ')
drop table Student

--2, creating a table---

CREATE TABLE Student
(
--specific column name data type column characteristics (whether null)--
Studentno int identity (2,1) not NULL,
loginpwd nvarchar not null, studentname nvarchar is not null,
Sex int is not NULL,
Gradeid int is not NULL,
phone NV Archar is not NULL,
borndate datetime is NOT NULL, address
nvarchar (255),
Email nvarchar (M
), Identitycard varchar
) go

---View all database objects (database tables)---

Copy Code code as follows:

SELECT * from sysobjects
drop table Student

----------------------Create a subject schedule-------------------

Copy Code code as follows:

-----1, to determine whether the table exists, if it exists, delete it and create it, if it does not exist, create it directly--------
if exists (select * from sysobjects where name = ' Subject ')
drop TABLE Subject

Use School
Go

---Create subject schedule--
CREATE TABLE subject
(
Subjectno int NOT null identity (1,1),
Subjectname nvarchar (50),
Classhour int,
Gradeid int
)

----------------------------------------Create a result score table-------------------

Copy Code code as follows:

-----1, to determine whether the table exists, if it exists, delete it and create it, if it does not exist, create it directly--------
if exists (select * from sysobjects where name = ' result ')
drop table Result
Use School
Go

---Create a result score sheet--

Copy Code code as follows:

CREATE TABLE Result
(
Studentno int NOT NULL,
Subjectno int NOT NULL,
Examdate Datetime NOT NULL,
Studentresult int NOT NULL
)

-----------------------------------------Create a Grande grade table-------------------

Copy Code code as follows:

-----1, to determine whether the table exists, if it exists, delete it and create it, if it does not exist, create it directly--------
if exists (select * from sysobjects where name = ' Grade ')
drop table Grade
Use School
Go

---Create a grande grade table--

Copy Code code as follows:

CREATE TABLE Grade
(
Gradeid int NOT NULL,
Grandename nvarchar (50)
)

-----------------------------------------t-SQL Add constraint-------------------------

Copy Code code as follows:

--Add a PRIMARY KEY constraint to the studentno---
ALTER TABLE Student
Add constraint Pk_stuno primary key (Studentno)

--Add a unique constraint to the identity card--

Copy Code code as follows:

ALTER TABLE Student
Add constraint Uq_stuidcard unique (identitycard)

---Add a DEFAULT constraint to address addresses--

Copy Code code as follows:

ALTER TABLE Student
Add constraint df_stuaddress default (' Address unknown ') for addressing

---Remove the address default constraint---

Copy Code code as follows:

ALTER TABLE Student
Drop constraint df_stuaddress

add Check constraint----------date of birth--------

Copy Code code as follows:

ALTER TABLE Student
Add constraint ck_stuborndate check (borndate > ' 1980-01-01 ')

---------establishes a primary foreign key relationship with grand (grade table)--------

--1, add grade primary key (Operation grade)---

Copy Code code as follows:

ALTER TABLE Grade
Add constraint Pk_graid primary key (Gradeid)

--2, add grade foreign key (Operation student)--

Copy Code code as follows:

ALTER TABLE Student
Add constraint Fk_stugradeid foreign key (Gradeid) references Grade (Gradeid)

-------------------add constraints to the subject schedule-----------------------

Copy Code code 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 Code code as follows:

-----with Nocheck: Data already exists without a check constraint-------
ALTER TABLE subject with NOCHECK
Add constraint ck_subname Check (subjectname is not null)

-----hours must be greater than 0-----

Copy Code code as follows:

ALTER TABLE subject with NOCHECK
Add constraint ck_classhour check (classhour > 0)

-----Add a primary foreign key constraint to the grade grade table----

Copy Code code as follows:

ALTER TABLE subject with NOCHECK
Add constraint Fk_gradeid foreign key (Gradeid)
References Grade (Gradeid)

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

-------Add multiple Constraints---------

Copy Code code 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 Code code as follows:

ALTER TABLE result
Drop constraint Pk_no_subid_date,fk_subno,fk_stuno,ck_studentresult,df_examdate

--------Change the data type of the column----------

Copy Code code as follows:

ALTER TABLE result
ALTER COLUMN Studentresult INT

The above is the entire content of this article, I hope you like.