Create a table in SQL Server

1: In SQL statements, there are two classes of temporary tables, local (local) and global temporary tables, local temporary tables are only visible in their sessions (transactions), and global temporary tables can be used by any program in the session (transaction) or

Module access

2: Create a local temporary table

[SQL]View Plaincopyprint?

1. Use Db_sqlserver
2. Go
3. Create table #db_local_table
4. (
5. ID int,
6. name varchar,
7. Age int,
8. Area int
9. )

Use Db_sqlservergocreate table #db_local_table (  ID  int,  name varchar (+), age  Int., area  int)

Temporary tables created cannot be shared with other sessions, and the definition of rows and tables will be deleted when the session ends

3: Create a global temporary table

[SQL]View Plaincopyprint?

1. Use Db_sqlserver
2. Go
3. Create table # #db_local_table
4. (
5. ID int,
6. name varchar,
7. Age int,
8. Area int
9. )

Use Db_sqlservergocreate table # #db_local_table (  ID  int,  name varchar (+), age  Int., area  INT )

Global temporary tables are visible to all users, and global temporary tables are removed when each user accessing the table disconnects from the server

4: Create a primary key, foreign key associated database table

[SQL]View Plaincopyprint?

2. Use Db_sqlserver;
4. Go
6. Create table Db_table5
8. (
10. Employee number int primary key,
12. The employee number varchar ( a) is unique,
14. Warehouse number varchar (a),
16. Payroll int
18. )
22. Go
24. Create table Db_table6
26. (
28. Order number int primary key,
30. Order number varchar unique,
32. Staff number varchar (references) Db_table5 (employee number),
34. Order Date datetime,
36. Sales Amount int
38. )

Use Db_sqlserver;gocreate table Db_table5 (  employee number int primary key,  employee number  varchar () unique,  warehouse number  varchar ($),  payroll   int) gocreate table Db_table6 (  order number int primary key,  order number  varchar () unique,  Staff number varchar (references) DB_TABLE5 (employee number),  Order date datetime,  Sales Amount int)

5: Create a database table with a CHECK constraint field

[SQL]View Plaincopyprint?

1. USE DB_SQLSERVER;  
2. go  
3. create table db_table7  
4. (  
5.    warehouse number  int  primary key,  
6.    Employee number   varchar ( unique,   
7.    warehouse number   varchar (a),   
8.    salary    int,  
9.    area   int check (area >=600  and  area <=1800)   
10. )   

Use Db_sqlserver;gocreate table Db_table7 (  warehouse number int primary key,  employee number  varchar () unique,  warehouse number  varchar ($),  payroll   int,  area  int check (area >=600 and area <=1800))

6: Create a database table that contains calculated fields

[SQL]View Plaincopyprint?

2. Use Db_sqlserver;
4. Go
6. Create table Db_table8
8. (
10. Employee number int primary key,
12. The employee number varchar ( a) is unique,
14. Warehouse number varchar (a),
16. Base pay int check (base pay >=800 and base salary <=2100),
18. Overtime pay int,
20. Bonus int,
22. Deduction rate int,
24. Salary should be paid as (base salary + Overtime salary + bonus-deduction rate)
26. )

Use Db_sqlserver;gocreate table Db_table8 (  employee number int primary key,  employee number varchar () unique,  warehouse number varchar (50), c3/> Base Salary int Check (base salary >=800 and base salary <=2100),  overtime pay int,  bonus int,  deduction rate int,  pay as (base salary + overtime wage + bonus- Deduction rate))

7: Create a database table with AutoNumber fields

[SQL]View Plaincopyprint?

1. USE&NBSP;DB_SQLSERVER;&NBSP;&NBSP;
2. go  
3. create table db_table9  
4. (  
5.     warehouse number  int  identity (  primary  KEY,&NBSP;&NBSP;
6.     Warehouse number  varchar (  unique,  
7.     city  varchar (+)  default ( ' Qingdao '),   
8.     area  int check (area >=300 and  area <=1800)   
9. )   

Use Db_sqlserver;gocreate table Db_table9 (   warehouse number int identity (primary) key,   warehouse number varchar () Unique,   city varchar (' Qingdao '),   area int check (area >=300 and area <=1800))

To add a record to a table:

[SQL]View Plaincopyprint?

1. INSERT INTO [db_sqlserver].[ DBO]. [Db_table9] (Warehouse number, area)  values (' 400 ', 1600);

insert into [db_sqlserver]. [dbo]. [Db_table9] (Warehouse number, area) VALUES (' 400 ', 1600);

The warehouse number is automatically incremented

8: Create a data table with sort fields

[SQL]View Plaincopyprint?

2. Create table Db_table10
4. (
6. Warehouse number int identity (1, 1) primary key,
8. Warehouse number varchar (COLLATE) French_ci_ai not null,
10. City varchar (+) default ' Qingdao ',
12. Area int Check (area >=300 and area <=1800)
14. )

CREATE TABLE Db_table10 (   warehouse number int identity (1, 1) primary key,   warehouse number varchar () collate french_ci_ai NOT NULL,   City varchar (+) Default ' Qingdao ',   area int check (area >=300 and area <=1800))

The warehouse number is a sort field where CI (case insensitive) means case-insensitive, and AI (accent insensitive) denotes accent-insensitive, which creates a case-insensitive

and accent-insensitive sorting. If you want to differentiate between size and sorting, modify the code to: French_cs_as

9: Dynamically determine whether a database table exists

[SQL]View Plaincopyprint?

1. Use Db_sqlserver;
2. Go
3. if (Exists (select * from sys.sysobjects where id=object_id (' db_table9 ')))
4. Print ' database table name already exists '
6. Else
7. Print ' The database table name does not exist and can be used to create the table '

Use Db_sqlserver;goif (Exists (SELECT * from sys.sysobjects where id=object_id (' Db_table9 ')))  print ' database table name already exists '  Else   print ' The database table name does not exist, and you can create a table with that name '

10: View the various information of the table, you can view the properties of the specified database table, the field properties in the table, various constraints and other information

[SQL]View Plaincopyprint?

1. Use Db_sqlserver;
2. Go
3. Execute sp_help db_table9;

Use Db_sqlserver;goexecute sp_help db_table9;

11: View the database table's property information with the SELECT statement

[SQL]View Plaincopyprint?

1. Use Db_sqlserver;
2. Go
3. SELECT * from sysobjects where type=' U '

Use Db_sqlserver;goselect * from sysobjects where type= ' U '

12: Renaming a database table

[SQL]View Plaincopyprint?

1. Use Db_sqlserver;
2. Go
3. Execute sp_rename "Db_table9", "db_renametable"

Use Db_sqlserver;goexecute sp_rename "Db_table9", "db_renametable"

13: Add new fields to the database table

[SQL]View Plaincopyprint?

1. USE&NBSP;DB_SQLSERVER;&NBSP;&NBSP;
2. go  
3. alter table db_table1  add  e-mail  varchar (  );
4. alter table db_table1 add   Contact  varchar (+)  default  ' 0532-88886396 '   
5. &NBSP;&NBSP;
6. select name  field name, xusertype  type number,  length  length  from syscolumns where id =  object_id ( ' db_table1 ')   

Use db_sqlserver;goalter table db_table1 add email varchar (0532-8888) ALTER TABLE DB_TABLE1 add contact varchar (+) Default ' 6396 ' Select Name field name, Xusertype type number, length from syscolumns where id = object_id (' Db_table1 ')

14: Modify the fields of a database table

[SQL]View Plaincopyprint?

1. Use Db_sqlserver;
2. Go
3. Alter table DB_TABLE1 alter column e-mail varchar ($)
6. Select Name field name, Xusertype type number, length from syscolumns where id = object_id (' db_table1 ')

Use Db_sqlserver;goalter table db_table1 alter COLUMN email varchar ($) Select Name Field name Xusertype type number length from sys columns WHERE id = object_id (' Db_table1 ')

15: Delete a database table field

[SQL]View Plaincopyprint?

1. Use Db_sqlserver;
2. Go
3. Alter table Db_table1 drop column e-mail
6. Select Name field name, Xusertype type number, length from syscolumns where id = object_id (' db_table1 ')

Use Db_sqlserver;goalter table db_table1 drop column e-mail select Name field name, Xusertype type number, length from syscolumns where id = object_id (' Db_table1 ')

16: Delete Database tables

[SQL]View Plaincopyprint?

1. Use Db_sqlserver;
2. Go
3. Drop Table Db_table1
4. Drop table Db_table1, Db_table2

Use Db_sqlserver;godrop table Db_table1drop table Db_table1, Db_table2

If you delete a database table that has dependent associations, that is, the primary key, the Foreign key key table, you delete the association constraint between the two tables before you can delete the table. Note that you can also delete a database table that references the table before you delete the table.

Creating tables in SQL Server