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)