SQL Server CREATE TABLE

Author: GW? Reprint annotated Source

??

The SQL statement creates the table format:

? CREATE table?< form name;?

? ( ?

?< column names >?< data Types > [column-Level integrity constraints]

? [,< column names > < data types > [column-Level integrity constraints] ...]

? [,< table-level integrity constraints;]?

? );

??

--above is the format of the table if you do not understand it is recommended to search the following regular expression?

??

--Let's set up a few tables now!

??

1. Create a database First

Create Database mydb1;

??

2. Select your table to be placed in that database

Use? mydb1;

??

3. Create a table

??

CREATE TABLE Student (

? ? Sno? CHAR (9) Primary key,

? ? Sname Char (a) unique,

? ? Ssex char (2),

? ? Sage smallint,

? ? Sdept Char (20)

);

??

/* Explanation:

Sno Char (9) Primary key,--Here is a column-level integrity constraint, SNO is the main code

? ??

?????????????--is also a column-level integrity constraint, unique is the only constraint?

*/

??

??

CREATE TABLE Course (

? ? CNO? CHAR (4) Primary key,

? ? CNAME char (40),

? ? Cpno char (4),

? ? Ccredit smallint,

? ? Foreign KEY (Cpno) References course (CNO)?

);

??

/* Explanation:

? Foreign KEY (Cpno) References course (CNO)?

?? This is a table-level integrity constraint cpno is the outer code reference is the CNO column in the course table.

*/

??

CREATE TABLE SC? (

? ? Sno Char (9),

? ? CNO Char (4),

? ? Grade smallint,

? ? Primary KEY (SNO,CNO),

? ? Foreign KEY (SNO) references student (SNO),

? ? Foreign KEY (CNO) references course? (CNO)

);

??

/* Explanation:

?? primary key (SNO,CNO), table-Level integrity constraints

?? Description This table has a total of two primary key Sno, CNO

*/

Here are some important basics to add:

/*?

? ? SQL Constraints

??

1? PRIMARY KEY??? (PRIMARY KEY constraint)

2? FOREIGN key??? (foreign KEY constraint)

5 y UNIQUE?????? (Unique constraint)

6} CHECK?????? (CHECK constraints)

??

?? for example: The following SQL statement creates a score (score) table, where a check constraint is used to limit the score to only between 0~100 points:

? ? ? CREATE TABLE Score

? ? ? ? (Sutdent_number int,

????? score int not NULL CHECK (score>=0 and score<=100)

? ? ? ? );

??

3 y DEFAULT????? (default value)

??

?? for example:? CREATE TABLE Datetest (

? ? ? ? ? ? C1 int,

? ? ? ? ? ? C2 datetime DEFAULT (GETDATE ())

? ? ? ? ? ? ):

??

??

??

*/

??

??

/*

Data type of SQL

? There is a certain difference in the data types of SQL in different databases.

? But the basic format for creating tables is basically the same,

=========================================================================

? What is the data type of the SQL Server database in detail? Here's what I've been searching the internet for.

? ? ? ? ? ? ? Reference URL: http://wenda.tianya.cn/question/244e34cc0cb0b2a2

? SQL Server provides 25 types of data:?

??

Binary [(N)], Varbinary [(n)],? char[(n)],? varchar[(n)],? nchar[(n)],

nvarchar[(n)],datetime,smalldatetime,decimal[(P[,s])],numeric[(P[,s]),

float[(n)],real,int,smallint,tinyint,money,smallmoney,bit,cursor,

Sysname,timestamp,uniqueidentifier,text,image,ntext.?

??

??

?? Char and nchar are fixed-length string types, and varchar and nvarchar are variable-length string types. Which means if

The field type is char (10), so even if the character you enter is ' ABC ', it will be saved as ' ABC ' in the database,

The field is automatically 7 spaces in front of it. The use of varchar (10) does not precede the space. As for the previous

n is different, there is n to support Unicode characters, and no n is not supported, the difference between text and ntext

is the same.?

??

The difference between text and char and varchar is that the data for char and varchar fields is saved in the table, and the text field can be

Save large volumes of text, the data is stored in another space, of course, on the surface does not seem to be any different.

About the type with big and small. The small representative is a simplified data type with a smaller range of support, but the space occupied is also

Small. Big is a very large data type, occupying a large space, but the scope of support is also large. For example, smallint only takes one byte,

But only 0-255 of the numbers are supported. DateTime is the same.?

??

?? char and nchar when the fill-in data is empty, the database is automatically replaced with a full space, so that the not null form is not a dummy.

So if the field cannot be empty, be sure to judge in advance in the program.

? Char and nchar are reportedly faster to read and write than varchar and nvarchar because of their fixed length?

??

These data types are described separately below:?

??

(1) Binary data type?

?? binary data includes binary, Varbinary, and Image?

Binary data types can be either fixed-length (binary) or variable-length.

binary[(N)] is a fixed n-bit binary data. where n is the range of values from 1 to 8000. The size of its storage scenting is n + 4 bytes.

varbinary[(N)] is a binary data of n-bit variable length. where n is the range of values from 1 to 8000. The size of its storage scenting is n + 4

Bytes, not n bytes.?

The data stored in the Image data type is stored as a bit string, not interpreted by SQL Server, and must be interpreted by the application.

For example, applications can store data in the Image data type using BMP, Tief, GIF, and JPEG formats.

??

(2) Character data type?

??

The type of character data includes Char,varchar and Text?

Character data is any combination of letters, symbols, and numbers.

Varchar is a variable-length character data whose length does not exceed 8KB.

Char is a fixed-length character data with a length of up to 8KB.

ASCII data over 8KB can be stored using the text data type. For example, because Html documents are all ASCII characters,

And in general the length is more than 8KB, so these documents can be stored in the Text data type in SQL Server.

??

(3) Unicode data type?

??

? ? Unicode data types include Nchar,nvarchar and ntext?

In Microsoft SQL Server, traditional non-Unicode data types allow the use of characters defined by a specific character set.

During the SQL Server installation process, a character set is allowed to be selected. With Unicode data types, any character defined by the Unicode standard can be stored in a column.

In the Unicode standard, all characters defined in various character sets are included. Using Unicode data types,

The prevailing scenting is twice times the size of the scenting used by non-Unicode data types.

??

? In SQL Server, Unicode data is stored in Nchar, Nvarchar, and Ntext data types.

Columns stored with this type of character can store characters in multiple character sets. When the length of a column changes, you should use the nvarchar character type,

You can store up to 4,000 characters at this time. When the length of the column is fixed, you should use the Nchar character type, as well,

You can store up to 4,000 characters at this time. When you use the Ntext data type, the column can store more than 4,000 characters.

??

??

(4) Date and time data type?

??

?? date and time data types include two types of datetime and smalldatetime?

Date and time data types are made up of valid dates and times.

??

?? for example, valid date and time data includes "4/01/98 12:15:00:00:00 PM" and "1:28:29:15:01am 8/17/98".

The previous data type is the date before, the time in the last data type is a moment before, and the date in the back.

In Microsoft SQL Server, date and time data types include datetime and smalldatetime two types when

The date range stored is from January 1, 1753 to the end of December 31, 9999 (each value requires 8 bytes of storage).

When using the smalldatetime data type,

The stored date range starts January 1, 1900 and ends on December 31, 2079 (each value requires 4 bytes of storage).

The format of the date can be set. The command for formatting dates is as follows:?

??

??

? ? Set DateFormat {format | @format _var|

Where, Format | @format_var is the order of the dates. Valid parameters include MDY, DMY, YMD, YDM, MYD, and DYM. In the default

In the case, the date format is MDY.

For example, when the Set DateFormat YMD is executed, the date is formatted as a month and day form;

When the Set DateFormat DMY is executed, the date is in the form of sun and moon.

??

(5) numeric data type?

??

?? digital data contains only numbers. Numeric data types include positive and negative numbers, decimals (floating-point numbers), and integers?

Integers consist of positive and negative integers, such as 39, 25, 0-2, and 33967.

In Micrsoft SQL Server, the data types stored by integers are int,smallint and Tinyint.

The INT data type stores data in a range larger than the Smallint data type stores the data.

The Smallint type stores data in a range larger than the tinyint data type stores the data.

The range of data that is stored using the INT data is from 2 147 483 648 to 2 147 483 647 (each value requires 4 bytes of storage space).

When using the Smallint data type, the range of stored data ranges from 32 768 to 32 767 (each value requires 2 bytes of storage).

When using the tinyint data type, the range of stored data is from 0 to 255 (each value requires 1 bytes of storage space).

The exact decimal data type of data in SQL Server is decimal and Numeric. The amount of storage space that this data occupies is determined by the number of bits in the data.

In SQL Server, the data type of the approximate decimal data is Float and Real. For example, One-third of this score is recorded.

3333333, which can be expressed accurately when using approximate data types. Therefore, the data retrieved from the system may not be exactly the same as the data stored in the column.

??

=======================================================================================

*/

??

SQL Server CREATE TABLE