Getting started with SQL (4): Creating and modifying data tables

Getting started with SQL (4): Creating and modifying data tables

This chapter describes how to use scripts to create databases, how to use scripts to create tables, and how to delete and modify objects.

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

Basic syntax for creating a DATABASE: 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>] [;] /* The red part of the data is supported by SQL Server and will not be introduced later */

In the one-to-one analysis creation Syntax:

ON is mainly used in two aspects: defining the location of the storage database and the log file. You can also see the primary-which means the number of primary file groups stored physically. You can also store the data in the so-called affiliated file group, which is out of scope.

NAME: Logical NAME of the definition file-that is, the reference NAME in SQL.

FILENAME defines the physical path of the file group on the disk of the actual operating system. It is recommended that the data file mdf end in this chapter. The end of the ldf log and the ndf result of the affiliated file.

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

The maximum size of a MAXSIZE database. Larger than SIZE

The value that automatically increases each time when the FILEGROWTH database is full. It can be KB, MB, GB, TB, and percentage. After the database grows, the maximum value does not exceed the MAXSIZE value.

Try to build 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)

Execute exec sp_helpdb panda (view the table creation Information)

Basic usage of creating a data TABLE: 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 written, because keys and constraints can be written to the table. The next blog will introduce constraints and keys.

Try:

CREATE TABLE panda_test (   ID int identity(1,1) primary key,   name nvarchar(20)  not null,   age    bit  default(1) not null,   code   int     check(code>1) not null)

Run 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 )

Run exec sp_helpdb panda once to get the following results. The file must have the name attribute to tell the file to be modified.

Alter table:

Alter table panda_testalter column age int -- modify the age value (if keys and constraints exist, they cannot be modified) alter table panda_testadd test nvarchar (30) not null -- the alter table panda_testdrop column <column name> cannot be deleted because of constraints.

Run. Test Result: exec sp_help panda_test

Write it here, and there are still many modifications to the table. The next blog will modify, delete, and add table constraints and keys in detail.