The implementation method of database

Implementation of the database

1. Create a database called S2222

--The first step is to perform SP configure enable Xp_cmddshell

exec sp_configure ' show advanced options ', 1

Go

Reconfigure

Go

--https://howsecureismypassword.net/

exec sp_configure ' xp_cmdshell ', 1

Go

Reconfigure

Go

Version--01.server operating system

--The following string represents the DOS command in the second step

exec xp_cmdshell ' mkdir C:\aaaaa '

Create DATABASE S2218--Create a new DB name

On primary--leads to

(

Name= ' S2218_data ',--logical file name

Filename= ' C:\aaaaa\S2218_data.mdf ',--Physical file name--Hang letter with suffix

SIZE=5MB,--Initial size

filegrowth=15%--File growth

)

Log on

(

Name= ' S2218_log ',

Filename= ' C:\aaaaa\S2218_log.ldf ',

SIZE=2MB,

Filegrowth=1mb

)

Go

Create DATABASE S2220--Create a new DB name

On primary--leads to

--2. Create a student table student***************************************************************

Student (SID,SNAME,SAGE,SREMARK,CID)

Use S2218

--How to determine if there is a table in the database??? sysobjects

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

--Delete Table

drop table Student

CREATE TABLE Student1--The base statement for creating tables ' Student ' is the name of the table

(

Sid int Identity (primary) key is not NULL,

Sname nvarchar (+) NOT NULL,

Sage int not NULL,

Sremark nvarchar (255) NOT NULL default (' no comment '),

Cid int NOT NULL,

Address nvarchar (255) NOT NULL

)

--*********************************3. Creating a Grade Table Grade (cid,cname)

--If the table exists, delete the table

if exists (select* from sysobjects where name = ' Grade ')

drop TABLE Grade

CREATE TABLE Grade

(

CID int Identity (primary) key is not NULL,

CName nvarchar () NOT NULL

)

--******************************4. Adding bar data to the student table in code mode

Use S2218

Insert into Grade (cName) VALUES (' Elite class ')

Insert into Grade (cName) VALUES (' Ox x class ')

Insert into Grade (cName) VALUES (' Sprint class ')

Select* from Grade

Insert into Student (sname,sage,sremark,cid,address) VALUES (' Bruce Lee ', 1, ' xxxxx ', 1, ' xxxxxx ')

--****************************************5. Adding a PRIMARY KEY constraint

Add a PRIMARY KEY constraint to the student table, and if so, delete the original constraint and add

Add a PRIMARY KEY constraint to the grade table, if any, remove and then add

SELECT * from sysobjects

where type= ' K ' and name= ' Pk_sid '

--grade table adds a gradual

ALTER TABLE Grade

Add constraint Pk_cid primary key (CID)

ALTER TABLE Student

Add constraint Pk_sid primary key (Sid)

ALTER TABLE Student

Drop constraint pk__student__ca1e5d7808ea5793

--******************************** 6. Adding FOREIGN KEY constraints

/* Common constraints:

1: PRIMARY KEY constraint

2: Unique Constraint

3: Check Constraints

4: FOREIGN KEY constraint

5: Default constraint

6: Non-null constraint

*/

Who is the primary key table who is the foreign key table

ALTER TABLE student--student represents from the table

Add Constraint Fk_foreign

FOREIGN KEY (CID) references grade (CID)

--*******************7. Add a unique constraint to ensure that the student's name is unique

Use S2218

ALTER TABLE student

Add constraint uq_sname unique (sname)

SELECT * FROM Student1

Insert into Student (SNAME,SAGE,SREMARK,ADDRESS,CID) VALUES (' Xiao Ming ', ' 20 ', ' Beijing Ah ', ' Beijing ', 1)

--********************8. Adding a DEFAULT constraint (note default: no notes)

ALTER TABLE Student

Add constraint Df_sremark Default (' No Notes ') for Sremark

Insert into Student1 (SNAME,SAGE,ADDRESS,CID) VALUES (' Xiao Ming ', ' 20 ', ' Beijing ', 1)

--**********************9. Adding CHECK constraints student age >=18 years

ALTER TABLE Student

Add constraint ck_sage Check (sage>=18)

Update Student Set sage=18

where sage=20

SELECT * FROM Student

10. Delete the database, delete the table, delete the constraint

--Flash back

--Delete Constraint

ALTER TABLE Student

Drop constraint Pk_sid

--Delete Table

if exists (select * from sysobjects where name= ' student1 ')

drop table Student1

--Delete Database

Use master

if exists (SELECT * from sysdatabases where name= ' s2218 ')

Drop Database S2218ss

The implementation method of database