January 11 operation of database and table

First, the data type

1. Binary type: (with length limit, can put 800 0, 1)

Format:

binary[(N)]--fixed-length n-byte binary data

varbinary[(n)]--n binary data with a byte length

2. Character data

Format:

char[(N)]--character data with a fixed length of n

varchar[(N)]--variable-length character data with a maximum length of n

The same point: a maximum of 8kb, equivalent to 8,000 English letters or numbers, also equivalent to 4,000 Chinese characters, more than 4000 words are generally stored in text type

Distinguishing between char and varchar:

Using char (20) and varchar (20) Two to define a quantity of 10, char (20) automatically complements the free 10 positions with NULL, and the unused 10 spaces are not freed.

While varchar (20) is now equivalent to char (10), the remaining 10 spaces are automatically freed, saving space. Therefore, the general use of varchar

3. Date Time Data

Format:

DateTime range is 1753.1.1-1999.12.31

smalldatetime Range is 1900.1.1-2079.6.6

Attention:

(1), date and time range, do not exceed its scope when defined

(2), define time with date time data, try not to use varchar ()

(3), date and time type fixed length

4. Real-Data

(1), fixed-point data

Format:

decimal[(P[,s])--where p is the total and S is the number of decimal digits

numeric[(P[,s])] If S is the default, there is no number of decimal parts

Attention:

P contains the number of decimal places, the decimal point is one

Cases:

If the decimal point is 2 and 4 is after the decimal point, then p=7,s=4

(2), floating-point data

Format:

float (real) [(n)]--n the number of digits used to record the mantissa of float

5. Currency type

6. Special data types

Bit contains only 0 or 1, which can be used to represent true,false

7. Text and image data types

Text: Used to handle variable-length large text data, which can store 2GB

Image: Used to process image data and can store 2GB

Ii. creation and management of databases

There are 3 SQL languages: DDL (data definition Language), DML (Data Manipulation language), DCL (Data Control Language)

1, the creation of the database:

Format:

Create database file name--database naming creation is the meaning of creating

On [PRIMARY]--Specifies that the data master file name is the secondary file name if it is not PRIMARY

(NAME = logical_file_name,

FILENAME = ' Os_file_name ',

Size=size,--Initialize file size

Maxsize=maxsize,--Specify the maximum value of the file

filegrowth=growth_increment)--Specify the increment of file growth

Log on--Journal file

(

NAME = Logical_file_name,

FILENAME = ' Os_file_name ',

Size=size,

Maxsize=maxsize,

Filegrowth=growth_increment

)

2. Modify the Database

ALTER Database

3. Delete Database

DROP DATABASE

Note: The deleted database must not be in use

Iii. creation and management of tables

1. Create a table

CREATE TABLE [[Database name.] Table owner.] Table name

({Column name data type}

[NULL | Not NULL]

{column name as column expression}--columns that can be computed from other tables are represented by column expressions

[,........] )

[on {file Group name | DEFAULT}]

2. Specify default values

Format:

[DEFAULT constant expression]

Example: CREATE TABLE STUDENT

(

NAME CHAR (8),

Collece CHAR (Default ' Heilongjiang Army Day Land Reclamation University ')----if not completed, Heilongjiang Army Day Land Reclamation University

)

3. Identity column (self-growing column)

Format

CREATE TABLE [[Database name.] Table owner.] Table name

({Column name data type}

[IDENTITY (seed value, growth value) not NULL]--the seed value (meaning the increment from a few) the growth value is (a few increases at a time)

[.............,]

)

Attention:

(1), a table can have only one column defined as the IDENTITY property. The column must be defined in decimal, int, numeric, smallint, bigint, tinyint data.

(2), if the seed value and growth value are not defined, the default is 1

(3), the identifier column is not allowed to be null, nor can it contain the default

* (4), insert into input, if there is an identity column, can be automatically omitted.

Example: Table student has two ID column, Name column two column, where ID is listed as identifier column,

Then insert into student values (' Liu Ming ')--just enter a number and the identifier column in the table is automatically generated

* (5), logo column with a scrap one, once used, you can no longer use

Example: If a three row student table, the ID column is the identity column, the name column name, delete the second row of names, and then add a Yes, id columns automatically become 4.

If you find that the name of ID4 is wrong, the ID will automatically change to 5 after you have changed it.

(6) If you want to fill in the identifier column from the beginning, TRUNCATE TABLE name--truncate is truncated, empty meaning

4. Check checking constraints

Objective: To prevent the occurrence of identical values, which can be used as a distinction.

Action: Find the table you want to manipulate-right-to-design-find the column name that you want to set the check constraint to-right--->check constraint

5. Define Primary keywords

Characteristics of the PRIMARY key:

(1), uniqueness. A table has only one primary key

(2), not empty. Cannot enter a null value in the primary key column

(3), each table should have a primary key

(4), the primary key will be automatically sorted

Type of PRIMARY key:

(1), general set a primary key

(2), sometimes also set 2 primary keys, that is, the combination of primary key. The combination of the primary key means that it is the only non-repeating combination, one of which can be repeated, but it is not possible to duplicate it together.

Action: Locate the table you want to manipulate-right--and design the column you want to set--right--and set the primary key//or directly click on the column you want to set, then click the key marker at the top left

6. Define outside Keywords

(1), foreign key generally involves two tables: Main Table and from table

Main Table: The table used to constrain

From table: Constrained table-foreign keys are generally built from a table

(2), locate the table you want to manipulate--right-click on the top left corner with the primary key next to the relationship key---and then select the item you want to set in the right column--the first

Data diagram--right---New diagram--select Relationship Diagram--key to the main table (you can directly set the primary key, a relationship directly connected to it)

Attention:

There are update and delete rules in the INSERT and update specification:

(1), delete the rule: do not do anything--from the table has been used by the main table can not be deleted, modified

Cascade-The main table is deleted, followed by delete from the table.

Set null--The primary table is deleted, and the table becomes null.

Update rule: Do nothing--the main table of items that have been used from the table cannot be updated

Cascade-Updates the main table, followed by updates from the table.

Set null--The primary table is updated, and the table becomes null.

7. Candidate key (a unique non-repeating key other than the primary key)

UNIQUE constraint

Characteristics:

(1), non-repeating

(2), can have multiple--unique constraints can have more than one

(3), can be empty--unique constraints can be empty

(4), do not have automatic sorting function

Operation:

Locate the table that you want to manipulate-right--design--Find the columns to constrain--add-type unique keys

8. Add columns

ALTER TABLE name--Modify tables

9. Delete a table

DROP Table Name

January 11 operation of database and table