In the process of using the database, the most exposed is the table in the database, which stores all the data in the database. Therefore, the table is the basic information storage structure of SQL Server database system and the most important part of the database. Managing the table naturally manages the database.

1. Data sheet overview

         Data table is one of the most important parts of database. Database is just a framework, data table is its essence, and all data is stored in data table. According to the classification of information, a database may contain several data tables.

        In the process of creating a data table, because you want to define the fields in the table, you need to select the data type for the fields. The following describes the basic types commonly used in SQL Server 2008 R2.

2.SQL basic data type

classification	Remarks and instructions	data type	explain
Binary data type	Stores data that is not child characters and text	Image	Can be used to store images
Text data type	Character data includes any combination of letters, symbols, or numeric characters	Char	Fixed length non Unicode character data
		Varchar	Variable length non Unicode data
		Nchar	Fixed length Unicode data
		Nvarchar	Variable length Unicode data
		Text	Store long text information
		Ntext	Store long text of variable length
Date and time	Enter the date and time in single quotation marks	Datetime	Date and time
digital data	The data contains only numbers, including positive numbers, negative numbers, and fractions	int smallint	integer
		float real	number
Currency data type	Used for decimal currency values	Money
Bit data type	Data indicating yes / no	Bit	Store Boolean data types

3. Data table creation

There are two ways to create data tables through sql structured query language: (1) object resource manager (2) script

3.1 creating tables using object Explorer

  3.2 creating tables by script

create table tb_ Table name

(

        Property name data type [(length)],

         Property name data type [(length)],

         Property name data type [(length)]

)

/*Create student information table*/
create table tb_student
(
    sid int,
    sname varchar(20),
    ssex char(2),
    sage int
)

4.SQL constraints

constraint is a method provided by Microsoft SQL Server to automatically maintain database integrity. It defines the constraints that can input data in a table or a single column of a table.

There are six kinds of constraints in SQL Server: Primary Key constraint, Foreign Key constraint, Unique constraint, Check constraint, Default Constraint and not null constraint.

4.1 primary key constraint

The primary key constraint uniquely identifies each record in the database table. The primary key must contain unique values. Primary key columns cannot contain NULL values. Each table should have a primary key, and each table can only have one primary key.

 

create table tb_Table name
(
    Attribute data type[(length)] primary key
)

4.2 foreign key references

A foreign key is one or more columns used to establish or strengthen a link between two table data. By adding one or more columns of the primary key value in a table to another table, you can create a connection between two tables, and this column becomes the foreign key of the second table.
The purpose of foreign key constraint is to control the data stored in the appearance and the modification of the data in the primary key table

 

create table tb_Table name 1
(
    Attribute 1 data type[(length)] primary key,
    Attribute 2 data type[(length)]
)

create table tb_Table name 2
(
    Attribute 1 data type[(length)] foreign key references tb_Table name 1(attribute),
    Attribute 2 data type[(length)]
)

4.3 unique ness constraint

Unique constraints ensure that a column of data in a table does not have the same value. Similar to the primary key constraint, the unique constraint also enforces uniqueness, but the unique constraint is used for one or more columns of non primary key columns, and a table can define multiple unique constraints.

 

 

 

create table tb_Table name 1
(
    Attribute 1 data type[(length)] primary key,
    Attribute 2 data type[(length)] unique
)

4.4 check constraints

Check constraints are used to limit the range of values in a column. If a check constraint is defined for a single field (column), only specific values are allowed for that column. If you define a check constraint on a table, the constraint limits values in specific columns.

 

create table tb_Table name 1
(
    Attribute 1 data type[(length)] primary key,
    Attribute 2 data type[(length)] check ( Attribute 2 = '' or Attribute 2 = '')
)

4.5 default constraint

If the default value constraint is defined in the table, when inserting a new data row, if the row does not specify data, the system assigns the default value to the column. If we do not set the default value, the system defaults to null.

create table tb_Table name 1
(
    Attribute 1 data type[(length)] primary key,
    Attribute 2 data type[(length)] default 'Default value'
)

4.6 non NULL constraint not null

Non NULL constraint means that the value of the field cannot be null. For fields with non null constraints, if the user does not specify a value when adding data, the database system will report an error.

 

create table tb_Table name 1
(
    Attribute 1 data type[(length)] primary key,
    Attribute 2 data type[(length)] not null
)

5. Create a classic case table

--Student information sheet
create table tb_student
(
    sid int primary key,--Student number
    sname varchar(50) not null,--full name
    ssex char(2) check(ssex = 'male' or ssex = 'female'),--Gender
    sage int not null,
    saddress varchar(100) default 'Changsha City, Hunan Province',--address
    sidentityCard varchar(20) unique,--ID card No.
)
--Transcript
create table tb_score
(
    sid int foreign key references tb_student(sid),--Foreign key
    degree int
)

6. Expansion

--Modify the structure of the table

--Modify field type
	alter table Table name
	alter column Field name type constraint;

--Add or delete columns
	alter table Table name
	add Field name type constraint
	
	alter table Table name
	drop column Field name


--Add or remove constraints
	Not empty:
	alter table Table name
	alter column Field name type constraint;
	
--other
	alter table Table name
	add constraint Constraint name(Field name)