Optimized MySchool Database (ii)

Source: Internet
Author: User

Java training, Android training, iOS training,. NET training, look forward to communicating with you!

Optimize the School database (TSQL build table constraint)

Use T_sql code to build libraries, build tables, and build constraints:

Build Library:

Create Database Hotelmanagersystem

On

(

----Data File---

)

Log on

(

----Log Files

)

"When there are multiple logs, multiple data files, separated by commas, a continuous definition is possible"

Create database name

On

(),(),()

Log on

(),()

Key parameters of the database file:

Name, filename, size, maxsize, filegrouwth

Attention:

If the database is in the process of normal operation, how to "dynamically add" data files?

ALTER database name

ADD FILE

(name = file name,

FILENAME = ' c:\temp\ ... ',

SIZE = 10MB,

FileGrowth = 10MB

)

ALTER database name

1. REMOVE file file name;

2, MODIFY file (name = file name, filegrowth = 50MB)

How can I tell if the database is based on existence?

Way One:

Exists (SELECT * from sysdatabases WHERE name = ' database name ')

Way two:

DB_ID (' database name ') is not null

-------------------------------------------------

if (db_id (' Mydatabase ') is not null)

Drop Database Mydatabase

Create DATABASE Mydatabase

On

(......)

Log on

(.......)

Go

How to create a "table":

1. You must open your own database (use database name go)

2, judge whether the data table exists, and delete

--If exist (select * from sysobjects WHERE name = ' table name ')

--If object_id (' table name ') is not null

3. Grammar

Create Table Table Name

Whether the column list type is empty,

Name varchar () is not NULL,

Go

"Maintaining data integrity" in SQL Server Category:

----So-called data integrity: is the data in the database, not lost, not confused

Data integrity Classification:

1. Entity integrity:

----Make sure every record is unique and won't confuse

PRIMARY KEY constraint, UNIQUE constraint, self-growing identity

2. Domain Integrity:

----Ensure that every part of each record has a valid, error-free data

Column data type, DEFAULT constraint, non-null constraint, CHECK constraint

3. Referential integrity:

-----Ensure a valid association (inter-table relationship) between a table and a table

Primary FOREIGN KEY constraint

4. Custom Integrity:

-----Ensure that the records in the table conform to the requirements of the "logical business"

Stored procedures, triggers, rules

Settings for common constraints:

----PRIMARY key, foreign key, default, check, unique

----Syntax (post-table constraint)

Alter Table Table Name

The contents of the ADD constraint constraint name constraint type constraint

Constraint name

Constraint type

Constraint content

Primary key

Pk_ Column Name

PRIMARY KEY

Primary key (column name) PRIMARY KEY constraint

Unique key

Uq_ Column Name

UNIQUE

Unique constraint for unique (column name)

Default for

DF_ Column Name

DEFAULT ... For ...

Default (value) for (column) constraint

Check

Ck_ Column Name

CHECK

Check (judging condition)

FOREIGN key

References

FK_ Column Name

FOREIGN KEY ...

REFERENCES

FOREIGN key (column of this table) foreign KEY constraint for REFERENCES appearance (primary key column for appearance)

ALTER TABLE Stuinfo

ADD CONSTRAINT Pk_stuno PRIMARY KEY (Stuno),

CONSTRAINT uq_stuid UNIQUE (stuid),

CONSTRAINT df_stuaddress DEFAULT (' address unknown ') for stuaddress,

CONSTRAINT ck_stuage CHECK (stuage between and 40),

CONSTRAINT Fk_stuno FOREIGN KEY (Stuno) REFERENCES Stumark (Stuno)

GO

ALTER TABLE Subject with NOCHECK

ADD CONSTRAINT ck_classhour CHECK (classhour>=0)

(with NOCHECK adds this mandatory addition, does not check the original data)

Tips:

exists ()----when "query words" in parentheses have a return result, exists =true

。。。。。。。。。。。 No.......... False

The difference between Char\varchar types: whether the length is permanently fixed when the data is populated. char= fixed

char type with n and without N: only populated with "letters or numbers" without N,

If you need to deposit Chinese characters or other special symbols, use n

----character type with n, length 2 bytes, character type without n, length 1 bytes

Optimized MySchool Database (ii)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.