SQL Getting Started Classic (iv) creating and modifying datasheets

This chapter focuses on how to create a database using scripts, how to use scripts to create tables, how to delete objects and modify objects.

Create statement : Create <object type> <onject name>

Basic syntax for creating databases: Create Database <database name> on [parimary] ([name=< ' logical file name ' >,][filename= ' <file Name> ',][size=<size in Kilobytes,megabytes,gigabytes,or terabayes>,][maxsize=<size in kilobytes, Megabytes,gigabytes,or terabayes>,][filegrowth=<size in Kilobytes,megabytes,gigabytes,or Terabayes>])

LOG on ([name=< ' logical file NAME ' >,][filename= ' <file name> ',][size=<size in Kilobytes,megabytes, Gigabytes,or terabayes>,][maxsize=<size in Kilobytes,megabytes,gigabytes,or terabayes>,][FILEGROWTH=< Size in kilobytes,megabytes,gigabytes,or terabayes>])

[containment=off| Partial][collate<collation name>][for attach[with <service broker>][for ATTACH_REBUILD_LOG| With Db_chaining on| Off| Trustworthy on| OFF]] [as SNAPSHOT of< source Dateabase name>][;] /* Red part of data is optional SQL Server supported data is not introduced after * *

In each analysis, create the syntax:

On is mainly used for 2 aspects one is to define the storage database and the location of the journal file. You can also see primary-which means that the number of primary filegroups is physically stored. You can also store the data in a so-called satellite file group, which is beyond the scope of the write.

Name defines the logical name of the file-that is, the reference name inside SQL.

FileName defines the physical path of the filegroup on the actual operating system disk, and this chapter recommends the end of the data file MDF. Journal LDF end, attached file NDF results.

Size the initial value of the database, which can be KB,MB,GB,TB.

MAXSIZE the maximum size of the database. Bigger than size.

When the FILEGROWTH database is almost full, the value is automatically increased each time. It can be KB,MB,GB,TB, and there are percentages. The database maximum will not exceed the maxsize value after growth.

Try building a database.

CREATE DATABASE Pandaon (   name= ' panda.mdf ',   filename= ' e:\test\panda\panda.mdf ',   size=100mb,   MAXSIZE=1GB,   filegrowth=50mb) Log on (  name= ' panda_log.ldf ',   filename= ' E:\test\panda\panda_log.ldf ',   SIZE=100MB,   maxsize=1gb,   FILEGROWTH=50MB)

Under we execute EXEC sp_helpdb Panda (view create table information)

Basic usage of creating data tables: Create TABLE <table name> (<column name> <data type> [[default< constant expresstion> ]| [Indentity[(Seed,increment) [Nont for replication]]| [null| isnull]]][,.......]).

Too many columns can not be finished, because the keys and constraints can be written into the table. The next blog begins by introducing constraints and keys.

Give it a try:

CREATE TABLE panda_test (   ID int identity (primary) key,   name nvarchar () not  null,    age Bit  default (1) not NULL,   code   int     Check (code>1) is not null)

Run under exec sp_help panda_test

ALTER statement:

Basic syntax alter <object type> <object name>

ALTER DATABASE:

ALTER DATABASE Pandamodify file (   name= ' panda.mdf ',   size=200mb,   MAXSIZE=2GB)

When executing exec sp_helpdb panda get the following result, file must name this property, tell the modification is the file.

Alter table:

ALTER TABLE Panda_testalter column  age  INT--modifies the age value (if there are keys and constraints that cannot be modified) ALTER TABLE panda_testadd Test nvarchar (30) Not null--add value ALTER TABLE Panda_testdrop column <column name> constraint cannot be removed.

Run, test results: exec sp_help panda_test

There's a lot of changes to the table first. Wait for the next blog in detail to modify, delete, add table constraints and keys to these.

DROP: Drop <object type> <object name> Delete object, such as: Drop database <database name>,drop table <table name>, DRO P View <view name>

SQL Getting Started Classic (iv) creating and modifying datasheets