Database Basic Learning-database statement operation, data type, constraint classification

First, the creation of the database

1. Create a database

Create DATABASE MyDB
On primary
(
Name=mydb,
Filename= "D:\MyDB.MDF"
),--Create a master data file called MyDB, placed in the D drive.
(
NAME=MYDB1,
Filename= "E:\MyDB1.NDF"
),--Create a secondary data file called MyDB1, placed in the E-disk.
Log on
(
Name= "Mydb_log",
Filename= "E:\MyDB.Log"
)--Create the MyDB log file and place it on the E disk.

2. Open the Database
Use MyDB
Use master

3. Modify the data
ALTER DATABASE MyDB--Modify MyDB databases
Add file--adding file files
(
Name= "MyDB2",--Create a secondary data file called MyDB1.

Filename= "C:\MYDB2.NDF"--Place the MyDB data file in the C drive.
)

4. Deleting a database

Drop Database MyDB

5. View the database information
sp_helpdb MyDB
6. Renaming a database
Sp_renamedb ' Newmydb ', ' MyDB '

ii. Types of variables

Integer: Int,bigint,smallint

Fractional: float,decimal (length, precision)

Character: char (n), varchar (n)

text--large text.

Logic: Bit 0,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

Third, the constraint classification (to ensure the integrity of the data).

A, 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. ( software operation).

2. Code-building the primary key. Primary key

B, 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. 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)

C, non-null constraints--cannot be null

1. Visualization: Table Design interface, each column has a "Allow null" check box.

2. Code: NOT NULL

D, 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

E, check Constraint

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)

F, default value

1. Visualization: Properties in a column--default value or binding

2. Code: Default Value

G, UNIQUE constraint

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

H, 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 Foundation Learning-DATABASE statement operations, data types, constraint classifications