Shulou(Shulou.com)05/31 Report--

This article mainly introduces "the skills of building database, table and constraint of sql server". In the daily operation, I believe that many people have doubts about the skills of building database, table and constraint of sql server. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "building database, table and constraint skills of sql server". Next, please follow the editor to study!

-- before creating a School database: first determine whether the database exists. If it exists, delete it and then create it. If it does not exist, create-exists keyword: return 'true' otherwise' false' if the data can be queried in parentheses

If exists (select * from sysdatabases where name = 'School')-if exists returns' true', it deletes the database-- if drop database School--exists returns' false', the database does not exist. Directly create create database Schoolon primary (--main database file-- name = 'School',-- main data file logical name fileName =' D:\ project\ School.mdf',-- physical logical name of master data file 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

-- create an employee database using T-SQL--

Create database employeeon 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 = 20MBMaxsize = 100MB filegrowth = 1MB) log on (--first log file-- name = 'employee_log1',filename =' D:\ project\ employee_log1.ldf',size = 10MB filegrowth = 1MB) (--the second log file-- name = 'employee_log2',filename =' D:\ project\ employee_log2.ldf',size = 10MB _ maxsize = 50MB _ filegrowth = 1MB)

-- query existing database information

Select * from sysdatabases

-- Delete database-- copy the code as follows: drop database School

-- create Student database table-- copy the code as follows:-- 1. Select the database for operation-- use Schoolgo

-- determine whether the table exists-- copy the code as follows: if exists (select * from sysobjects where name = 'Student') drop table Student

-- 2. Create tables

Create table Student (--characteristics of the column of the specific column name data type (empty or not)-- StudentNo int identity (2pj1) 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 the code as follows: select * from sysobjectsdrop table Student

-- create the subject course schedule-copy the code as follows:-1. Determine whether the table exists. Delete and create if it does not exist. If it does not exist, directly create-if exists (select * from sysobjects where name = 'subject') drop table subjectuse Schoolgo--- to create the subject course schedule-- create table subject (SubjectNo int not null identity (1), SubjectName nvarchar (50), ClassHour int,GradeID int)

-- create Result score sheet-copy the code as follows:-1. Judge whether the table exists. Delete and create if it exists, and create-if exists (select * from sysobjects where name = 'Result') drop table Resultuse Schoolgo directly if it doesn't exist.

-create Result score sheet-copy the 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 the code as follows:-1. Determine whether the table exists. Delete and create if it exists, and create-if exists (select * from sysobjects where name = 'Grade') drop table Gradeuse Schoolgo directly if it doesn't exist.

-create Grande grade table-copy the code as follows: create table Grade (GradeId int not null,GrandeName nvarchar (50))

-- add constraints to T-SQL-copy the code as follows:-- add primary key constraints to StudentNo-alter table Studentadd constraint pk_StuNo primary key (StudentNo)

-- add unique constraint to ID card-- copy the code as follows: alter table Studentadd constraint uq_StuIdcard unique (IDENTITYcard)

-add a default constraint to the address address-copy the code as follows: alter table Studentadd constraint df_stuaddress default (address unknown) for Address

-Delete address address default constraint-copy the code as follows: alter table Studentdrop constraint df_stuaddress

-add check constraint on date of birth-copy code is as follows: alter table Studentadd constraint ck_stuBorndate check (Borndate > '1980-01-01')

-establish a primary and foreign key relationship with Grand (grade table)

-1. Add Grade primary key (operation Grade)-copy the code as follows: alter table Gradeadd constraint pk_graid primary key (GradeId)

2. Add Grade foreign key (operation Student)-copy the code as follows: alter table Studentadd constraint fk_stuGradeID foreign key (GradeId) references Grade (GradeId)

-add constraints to the subject curriculum

The copy code is as follows:-add a primary key constraint to the subjectNo column-alter table subjectadd constraint pk_SubID primary key (SubjectNo)

-add a non-empty constraint to the course name subjectName;-copy the code as follows:-with nocheck: data already exists without check constraints-alter table subject with nocheckadd constraint ck_subName check (SubjectName is not null)

-the school hours must be greater than 0-the copy code is as follows: alter table subject with nocheckadd constraint ck_ClassHour check (ClassHour > 0)

-add primary foreign key constraint to Grade grade table-copy the code as follows: alter table subject with nocheckadd constraint fk_GradeID foreign key (GradeID) references Grade (GradeID)

-add constraints to the result score sheet-

-add multiple constraints-copy the code as follows: alter table Resultadd 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)

-- remove multiple constraints-- copy the code as follows: alter table Resultdrop constraint pk_No_subID_date,fk_subNo,fk_StuNo,ck_StudentResult,df_examdate

-change the data type of the column-the copy code is as follows: alter table Resultalter column StudentResult int

At this point, the study of "sql server database building, table building, constraint building skills" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.