SQL statements create databases, SQL statements Delete databases, SQL statements create tables, SQL statements Delete tables, SQL statements add constraints, SQL statements Delete constraints

To create a database:

1 CREATE DATABASETest--The name of the database to be created2  on PRIMARY3 (4     --specific description of the database file5NAME='Test_data',--logical name of the master data file6FILENAME='E:\project\Test_data.mdf',--physical name of the master data file7SIZE=5MB,--Initial size of master data file8MAXSIZE=100MB,--maximum growth of the master data file9FileGrowth= the%                            --growth rate of master data filesTen ) One LOG  on A ( -     --the specific description of the log file, the meaning of each parameter ibid . -NAME='Test_log',                         theFILENAME='E:\project\Test_data.ldf', -SIZE=2MB, -FileGrowth=1MB - ) + GO                                            --and subsequent SQL statements are separated

which

1. log file parameters and maximum capacity are optional.

2. The database name is a maximum of 128 characters.

3.PRIMARY is a keyword that specifies the files in the primary filegroup.

4.log on indicates a clear definition of the transaction log file.

5. name Specifies the logical name of the database, which is the name that is used in SQL Server, and is the identifier of the database in SQL Server.

6.filename Specifies the operating system folder name and path for the file where the database resides, and the operating system file name corresponds to the logical name one by one of name.

7. size Specifies the initial capacity of the database.

8.MAXSIZE Specifies the maximum value that the operating system file can grow to.

9.filegrowth Specifies that the file increases the size of the capacity each time, when the specified data is 0 o'clock, the file does not grow.

10. If the size is not MB, the default is in megabytes

One. Master data Files If you do not set the maximum growth value, the default is not to limit growth, and the log file defaults to 2097152mb=2048g if you do not set the maximum growth value .

The build path must exist or an error (

Directory lookup for File "e:\******" failed with operating system error 2 (the system cannot find the file specified.) )。
CREATE DATABASE failed. Some of the listed file names could not be created. Please review the related errors. )

----------------------------------------------------------------------------------

Use xp_cmdshell to extend system stored procedures and invoke DOS commands to create folders

EXEC ' mkdir E:\Test '

Before you can use xp_cmdshell, you need to execute sp_configure to enable xp_cmdshell, as shown in the following code:

EXEC ' Show advanced Options ',1goRECONFIGUREgoEXEC'xp_cmdshell ',1goRECONFIGUREGo

----------------------------------------------------------------------------------

To delete a database:

DROP DATABASE Test--The name of the database to be deleted

----------------------------------------------------------------------------------

CREATE TABLE and delete table:

CREATE TABLETest--default is created in the database that is currently located(test_idINT  not NULL,--first columnTest_namenvarchar( -) not NULL,--second columnTest_agenvarchar( -)NULL                   --third column)

Note If nvarchar does not set the length, the default is 1.

As you create a table and create a database, you want to pre-detect whether the table already exists, and if so, delete it before you create the table.

DROP DATABASE Test            -- Delete Table

----------------------------------------------------------------------------------

To add a constraint:

ALTER  TABLE Table name ADD CONSTRAINT constraint name constraint type specific constraint description

1. The naming rules for constraint names are recommended in the form of constraint type _ constraint name.

----------------------------------------------------------------------------------

To delete a constraint:

There are two ways to add a constraint to a specified table using an SQL statement: The first is to use the CREATE TABLE statement to add a related constraint while creating the tables structure. The second is to use the ALTER TABLE statement to add a constraint to a table that has already been created. The second approach is generally recommended.

ALTER TABLE Table name DROP CONSTRAINT Constraint name

----------------------------------------------------------------------------------

Welcome to criticize ^_^

January 6, 2016 22:13:36

SQL statements create databases, SQL statements Delete databases, SQL statements create tables, SQL statements Delete tables, SQL statements add constraints, SQL statements Delete constraints