SQL Server Base Language

This blog is about the database, data tables, fields, constraints and other simple additions and deletions of the operation (in addition to the recorded operation).

I. Database level

Increase:

1 CREATE DATABASE <Data table name>2  on  PRIMARY  --the default belongs to the primary primary filegroup, which omits3 (  4NAME='Studb_data',--logical name of the master data file5FILENAME='D:\project\stuDB_data.mdf',--physical name of the master data file6SIZE=5MB,--Primary Data file Initial size7MAXSIZE=100MB,--maximum number of main data file growth8FileGrowth= the%   --growth rate of master data files9 )Ten LOG  on One (   ANAME='Studb_log', -FILENAME='D:\project\stuDB_log.ldf', -SIZE=2MB, theFileGrowth=1MB -)

By deleting:

DROP DATABASE < database name >;

Check:

SELECT *  from sysdatabases;

Change:

1 ALTER DATABASE <Original database name>MODIFY NAME= <New database name>; 2 / EXECSp_renamedb'< original database name >','< New database name >';3 4 5 ALTER DATABASE <Database name>MODIFYFILE(NAME= <Original logical file name>, NEWNAME= <New Logical file name>);

Second, the data table level:

Increase:

1 CREATE TABLE <Table name>2 (3        <Column Name> <Data type>[< column-level integrity constraints >]4 ... ..5         [< table-level integrity constraints >]   6          ...7)；

By deleting:

DROP TABLE < table name > ;

Check:

SELECT *  from SYS. TABLES;

Change:

EXEC ' < original data table name > ' ' < New data table name > ';

Third, field level

Increase:

ALTER TABLE < table name >ADD< new column name >< data type >[   ] ;

By deleting:

ALTER TABLE < table name >DROP< column name >;

Check:

SELECT  from WHERE id=object_id('< table name >'); View field data type information SQL statements are more complex , no write, graphical interface available

Change:

ALTER TABLE < table name >ALTERcolumn< column name >< data type  >; EXEC ' < table name. Original Column name > ' ' < new column name > ';

Iv. constraint level

Increase:

ALTER TABLE < table name >ADD< table-level integrity constraints >  - table-level integrity constraints, Take the PRIMARY KEY constraint as an example: CONSTRAINT < pick constraint name > FOREIGN key (< field name >);

By deleting:

ALTER TABLE < table name >DROPCONSTRAINT< integrity constraint name >;

Check:

-- View constraints specific information more complex, not write, the use of graphical interface

Change:

-- perform the delete and execute the increment first

V. Index level

Increase:

CREATE [UNIQUE | CLUSTERED]index< index name >on< table name >( < column name >[ASC | DESC], ... ）;

By deleting:

DROP INDEX < index name >;

Check:

-- Graphical Interface

Change:

ALTER INDEX < old index name >to< new index name >;

SQL Server Base Language