First, the data type:
Integer: Int,bigint,smallint
Fractional: float,real,decimal (length, precision), numeric (length, precision)
Characters: char (n), varchar (n) 8000 English characters, 4000 kanji
text--large text.
Logic: Bit0,1/true/false
Binary type: binary 8000,image
Date: DateTime (1753-1-1~9999,12,31), smalldatetime (1900.1.1--2079.6.6)
The role of data types: Build tables, SQL programming
Second, the constraint-to ensure the integrity of the data.
(i) primary KEY constraint--entity
1. Not heavy. 2. Not available. 3. Sort. 4. Unique (cannot appear two primary keys) 5. Combine primary keys.
To build the primary key:
1. Visually build the primary key.
In the Design view of the table
2. Code-building the primary key. Primary key
(ii) FOREIGN KEY constraint--reference
Two tables, Main table and from table. Main Table--use the primary key to constrain each other. From a table-a constrained table, a constrained column is called a foreign-key column.
Foreign keys are always built from the table.
The content that is filled out from the foreign key table must be what is already in the primary key of the main table.
Build foreign Key:
1. Visualization: In the Design view of the table--relation--exterior window; Use database diagram.
2. Code: References primary table name (primary key column)
(iii) non-null constraint--cannot be null
1. Visualization: Table Design interface, each column has a "Allow null" check box.
2. Code: NOT NULL
(iv) Self-growth
It has a "seed" (the starting value) and a "step" (quantity per increment).
Attention:
1. Each self-growth value, once used, is obsolete and will not be reused.
2. Any behavior that adds a value to the self-growing column is incorrect.
3. There are type requirements for columns: int decimal
1. Visualize: In the properties of a column, identify the specification-yes.
2. Code: Identity
(v) CHECK constraints
The main thing is to further standardize the values inside the column.
1. Visualize: Right-click the--check constraint on the column--Add a CHECK constraint in the popup dialog box.
2. Code: Check (expression)
(vi) Default value
1. Visualization: Properties in a column--default value or binding
2. Code: Default Value
(vii) UNIQUE constraints
Can be empty, but not heavy.
1. Visualization: Right-click on the column select-Index/Key-in this interface can be both indexed and unique constraints can be built.
2. Code: Unique
Third, index
Improve the efficiency of your queries. A table can establish multiple indexes on different columns.
Cluster index (sort, primary key), non-clustered index (normal index).
The disadvantage of index: the efficiency of increase, deletion and change will be reduced.
1. Visualization: Similar to the method of building a unique constraint.
2. Code: CREATE index index name on table name (column name)
Four, view
Wrap the query as if it were a table.
The view itself does not store data.
Views can be set up from one table, multiple tables, multiple tables, and views.
Benefits of the view: easy to view. Disadvantages: increase, deletion, change inconvenient.
1. Visualization: Object Explorer, database-View right-click-New View.
2. Code: CREATE VIEW name
Database data type
