SQL Table Structure Operations

Database Knowledge Summary (table structure operation)

1. Create a table scores

1 CREATE TABLE Scores               --table Name 2 (Id int identity (primary) key,--set the primary key, and the line number is self-increasing,
                                     The identity (first) indicates that the ID column is incremented from 1, and each time the Date datetime is NOT NULL,           --The setting type is datetime and cannot be empty 4 Name nvarchar (not null,5 score) nvarchar (2)                 --default state, type is empty 6)

2. Modify the table name scores to Newscores

1 exec sp_rename ' Scores ', ' newscores '

3. Delete Table Scores

1 drop table Scores-delete tables (the structure, properties, and indexes of the table will also be deleted)

4. Clear the table data

1 TRUNCATE TABLE Scores--just delete the data in the table

5. Modify column score to NOT NULL

1 ALTER TABLE Scores2 ALTER COLUMN score nvarchar (2) NOT NULL

6. Adding columns

1 ALTER TABLE Scores 2 add new nvarchar (a) NOT NULL

7. Modify column names

1 exec sp_rename ' scores.name ', ' NewName ', ' column '

8. Delete Columns

1  ALTER TABLE Scores 2  

9. Modify the Identity column

If you create a table, the self-increment column is not set. Because the self-increment column cannot be modified directly, you must drop the original ID column and then add a column with the ID field that has the identity attribute.

1  exec Sp_pkeys @table_name = ' Scores '                               --Query primary KEY Name 2  ALTER TABLE Scores drop constraint pk__scores__ 3214EC074E3D66D2  --delete the PRIMARY KEY constraint first 3  ALTER TABLE Scores drop column ID                                --delete the ID column 4  ALTER TABLE Scores add ID int ID Entity (+)                      -add a self-increment column (the Id column is not the primary key) 5  ALTER TABLE Scores add ID int identity (constraint) PK primary key  -- Add self-increment column, set ID as primary key name PK

SQL Table Structure Operations