Implementation of the database (create libraries, tables, constraints; Delete tables, constraints)

A review of SQL Server database basics

1) Master data file: *.mdf

2) secondary data file: *.NDF

3) log file: *.ldf

　　Each database must contain at least two files: one data file and one log file

How to view Help for SQL Server ================== shortcut keys F1

First, create a database

1. Syntax

1 Create DatabaseDatabase name2     on  Primary3  (4     <Data file Parameters>[,...... N] [< file group parameters >]5   )6  Log  on7   (8{<Log file parameters> [,...... N]}9)

The data file parameters are as follows

1 (2   [name= Logical file name,] 3    FileName= physical file name 4   [, size= size ]5    [, maxsize={maximum capacity |unlimited}]6    [, filegrowth= growth ] 7   )

File group Parameters

1. Grammar

1 filegrowth file group name < file parameters > [,...... n]

2. Example (the first 8 lines of judgment, if there is a database on the delete database does not exist in the database will appear '2131231')

1 --Determine if this library is to be deleted2 if exists(Select *  fromsysdatabaseswhereName='MySchool')3 begin4 Drop DatabaseMySchool5 End6 begin7 Print '2131231'8 End9 --Create a databaseTen Create DatabaseMySchool One  on Primary A ( -     --specific description of the data file -Name='Myschool_data',--the logical name of the master data file +++++++ must be written theFileName='E:\MySchool_data.mdf',--the physical name of the master data file +++++++ must be written -Size=5MB,--Initial size of master data file -MaxSize=100MB,--maximum number of main data file growth -FileGrowth= the%                    --growth rate of master data files + ) - Log  on + ( A --the specific description of the log file, the meaning of each parameter ibid . atName='Myschool_log', -FileName='E:\MySchool_log.ldf', -Size=2MB, -FileGrowth=1MB - ) - Go

Second, create a table

1. Grammar

1 Create Table Table name 2 ( 3   column 1 data type column features 4   ... 5  ）

2, examples (5 and 6 lines to determine if there is student this table)

1 --Create a table2  UseMySchool--set the current database to MySchool to create a table in MySchool3 Go4 --Judging5 if exists(Select *  fromsysobjectswhereName='Student') 6 Drop TableStudent7 8 Create TableStudent---Create student table9 (TenStudentnoint Identity Primary Key  not NULL,--Learning number self-increment primary key, non-null OneLoginpwdnvarchar( -) not NULL, AStudentnamenvarchar( -) not NULL, -Sexbit default'female'  not NULL,--sex, value 0,1 -Gradeidint  not NULL, thePhonenvarchar( -)NULL, -Addressnvarchar( -)NULL, -Borndatedatetime  not NULL, -Emailnvarchar( -)NULL, +Identitycardvarchar( -) not NULL -  ) + Go

Third, delete the table

1. Grammar

1 drop table name

2. Example

1 drop table Student

3. Focus

How can we delete a primary key that already exists when the table is created with a primary key and a self-increment column?

1 -- View primary key, without first name of primary key 2 Select *  from where xtype='PK'3-- Delete primary key 4  altertable[Student]drop finds the name of the constraint (primary key)

Iv. adding constraints (example)

Grammar:

1 alter table name

2 add constraint constraint name constraint type specific constraint description

Example:

1. Add default constraint (default ' address unknown ')

1 Alter Table Student 2 Add constraint default (" address unknown " for address

2. Add check constraint (required to be born on October 26, 1996)

1 Alter Table  2addconstraintcheck>='1996-10-26 ')

3. Add a unique constraint (ID card only one in the world)

1 alter table Student

2 add constraint uq_identitycard unique (identitycard)

4. Add a PRIMARY KEY constraint

1 alter table Student

2 add constraint pk_studentno primary key(Studentno)

5. Add foreign KEY constraint (main table Student and build relationship from table result, associate column Studentno)

1 Alter Table  2addconstraint  fk_studentno3      foreign  KeyReferences

V. Review the syntax of T-SQL statements

1. Add Data

1 Insert into table name (column name,...... )values (value 1,......)

2. Modify the data

1 update table name set column 1= value 1, column 2= value 2,...... where (condition)

3. Delete data

1 Delete from table name where(condition)

4. Query statements

1 Select column 1, column 2,..... From table name where(condition) order by column name

Small examples of understanding

1 Select  from where Borndate>='1996-10-26'order by

Implementation of the database (create libraries, tables, constraints; Delete tables, constraints)