Creation, deletion, and modification of attributes in the database of SQL common statements-Space 3

I. Creation of tables:

CREATE TABLE [database_name.[ schema_name].| Schema_name.] table_name

(Column_name1 data_type--Column name

[DEFAULT constant_expression]--Constraint type

[Identity (SEED, INCREMENT)]--Column ID

[NULL | Not NULL [,... n]-null allowed

)

[ON {filegroup | DEFAULT}]--the filegroup in which it is located

Example. create category table on filegroup USER1 for the sales database

Use sales

CREATE TABLE Category

(CategoryID int not NULL,

CategoryName nvarchar (15),

Description nvarchar (200),

NodeID Char (8) identity (Mark)--Identity column ID increments from 1 to 1 per increment

)

On USER1

Two. Table structure Modification--Add, delete, modify columns:

ALTER TABLE table_name

{ADD column_name Date_type [DEFAULT contant_expression]

[IDENTITY (Seed,increment)] [NULL | Not NULL] [,... N]

| DROP COLUMN column_name

| ALTER COLUMN column_name New_datetype [NULL | Not NULL]

}

Example. Add an Address column to the table custom of the database test

ALTER TABLE Custom

ADD Address Char (20)

Delete XX column

ALTER TABLE Custom

DROP COLUMN XX

Modify columns: Change the length of the Address field in the custom table to 30 and cannot be empty

ALTER TABLE Custom

ALTER COLUMN Address char (+) NOT NULL

Three. Modify the contents of the table-Insert, Delete:

1. Insert data into the table:

INSERT [INTO] table_name [(column_name [,... n])]
VALUES (Expression | NULL | default [,... n])--expression is the value to insert, the character type and the date type to be added ' number

Example. Insert the following data in the table seller

INSERT into Seller (saleid,salename,sex,birthday,hiredate,address,telephone,notes)

VALUES (' 01 ', ' Zhang San ', DEFAULT, ' 1974-07-25 ', Null,null,null,default)

--Where the null value (NULL) can not be written, the corresponding column names do not have to be listed, for default values can use default value

-A column with an identity attribute whose value is given by the system, and the user does not have to insert data into the table.

2. Modify the data in the table:

UPDATE table_name

SET column_name=expression [,... N]

[WHERE search_conditions]

3. Delete the data in the table:

DELETE [FROM] table_name
[WHERE search_conditions]

Four. Create a constraint for the table:

The general syntax format:

CREATE TABLE table_name

(column_name data_type [[CONSTRAINT constraint_name] Constraint_type]

[,... N])

To add a constraint to a table that has already been created:

ALTER TABLE table_name

[With CHECK | With NOCHECK]

ADD [CONSTRAINT constraint_name] Constraint_type

--with CHECK | The WITH NOCHECK indicates whether the newly added constraint checks the existing data in the table.

To delete a constraint:

ALTER TABLE table_name

DROP CONSTRAINT constraint_name

1. Create a PRIMARY KEY constraint: The primary key is used to uniquely identify each record in the table. You can define one or more columns in a table as primary keys. The value of the primary key column cannot be duplicated, nor can it be a null value.

[CONSTRAINT Constraint_name] PRIMARY KEY [CLUSTERED | Nonclustered]

(Col_name [,... n])

2. Create a UNIQUE constraint: You cannot have duplicate values on a specified column

[CONSTRAINT Constraint_name] UNIQUE [CLUSTERED | Nonclustered]

(Col_name [,... n])

3. Create a CHECK constraint: a range that specifies the desirable values for a column.

[CONSTRAINT Constraint_name] CHECK (exp)--exp is an expression (indicating condition, scope)

--Role: When an INSERT or update operation is performed to the table, SQL Server checks whether the inserted new column values meet the conditions of the check constraint, and if not, the system will error and refuse to perform the insert or update operation.

--NOTE: You cannot set a check constraint on a column that has an identity attribute.

4. Create the default constraint: give the specified column in the table the defaults, and when you insert data into the table, if the user does not explicitly give the value of the column, SQL Server automatically enters a default value for that column. There can be only one default constraint per column.

[CONSTRAINT Constraint_name] DEFAULT (Expression | NULL) for column_name

5. FOREIGN KEY constraint (Foreign key): Used to establish a connection to a column (called a primary key column) in another table (primary key table).

[CONSTRAINT Constraint_name]

FOREIGN KEY (col_name1[,... n])

REFERENCES table_name (column_name1[,... n])

which

col_name1[,... N]: is the column to implement the FOREIGN key constraint.

TABLE_NAME: is the table name of the primary key table (reference table).

column_name1[,... N]: Is the primary key column (reference column) column name.

Cases.

Example 3.31 if the sales database contains the seller table and the Customer table.

Seller (Saleid, Salename)

Customer (CustomerID, company)

New Table orders (OrderID, CustomerID, Saleid,orderdate)

Where CustomerID and Saleid are foreign keys.

CREATE TABLE Orders

(Orderid int PRIMARY KEY,

CustomerID char (3) REFERENCES Customer (CustomerID),

Saleid char (3) CONSTRAINT fk_sid REFERENCES Seller (Saleid),

OrderDate datetime DEFAULT getdate ())

For example, modify the OrderDetail table and create a FOREIGN KEY constraint on the OrderID column.

ALTER TABLE OrderDetail

ADD CONSTRAINT Fk_orderid

FOREIGN KEY (OrderID) REFERENCES Orders (OrderID)

ALTER TABLE OrderDetail

With NOCHECK

ADD CONSTRAINT Fk_orderid

FOREIGN KEY (OrderID) REFERENCES Orders (OrderID)

Five. View the constraint name:

sp_help Student

Or:

Sp_helpconstraint Student

Six. Delete the constraint:

ALTER TABLE_NAME

DROP CONSTRAINT constraint name

Creation, deletion, and modification of attributes in the database of SQL common statements-Space 3