T-SQL statements for table structure operations

Create, modify, and delete table Structures
1> Create a table structure
Example 1
Create a salers table for the sales database, which includes saleid, name (salename), sex, birthday, And hiredate) address, telephone, and note fields. The saleid and salename Columns cannot be empty.
Create Table salers
(Saleid char (3) not null,
Salename char (8) Not null,
Sex char (2 ),
Birthday datetime,
Hiredate datetime,
Address char (60 ),
Telephone char (13 ),
Note char (200)
)
Note: The not null parameter indicates that this field cannot be null. There is also a null opposite to him (indicating that the field can be blank ).
Example 2
Create an order table orders for the sales database, including the orderid, customerid, saleid, and orderdate fields. The orderid is the ID column, the start value is 10248, and the value added is 1; the customerid and saleid fields cannot be null values. The default value of the orderdate field is the current date.
Create Table orders
(Orderid int identity (10248,1 ),
Customerid char (3) not null,
Saleid char (3) not null,
Orderdate datetime default getdate ()
)
Note: getdate () is System Function, return System Current date.
The orders table has an orderid column that starts with 10248 and adds value to 1. Therefore, when inserting data, you do not need to provide the value of this column, SQL Server automatically fills this column. The values of this column are 10250, and so on ......
The orderdate column in the orders table has a default value. Therefore, if you do not provide a value for this column when inserting data, SQL Server automatically fills the column with the default value. The default value is the current system date.
Example 3
For the sales database File Create a category categories table on group user1.
Create Table categories
(Categoryid int not null,
Categoryname nvarchar (15 ),
Description nvarchar (200)
)
On user1
Note: The file group user1 must have been defined. In the first two examples, if the on keyword is not used to specify a file group, the table is stored in the default file group.
2> Modify Table Structure
Example 1
The MERs table in the sales database contains three fields: customerid, companyName, and connectname. Now, the table includes the company address Encoding (Zipcode), and phone number field (telephone ).
Exec sp_addtype telephone_code, 'varchar (15) ', 'null'
Exec sp_addtype zip, 'Char (6) ', 'not null'
Go
Alter table MERs
Add address char (40), zipcode zip, telephone telephone_code
In this column, the Data Types of the added zipcode and telephone fields are user-defined data types.
Example 2
Delete the sex column in the table salers.
Alter table salers
Drop column sex
Example 3
Change the address field length in the salers table to 30 and cannot be blank.
Alter table salers
Alter column address varchar (30) not null
Note: Only the Data Type of the column can be modified and whether the column value is null.
3> delete a table structure
Example:
Delete A Table named example1 in the current database.
Drop table example1