Knowledge section
1. SQL Server Data type
• Data type is a property of the data used to define the data as time, number, string (text, letter), etc.
· SQL Server provides a set of system data types that define all data types that can be used with SQL Server
• Common data types:
int: An integer from 2147483648 to-2147483647 (available for the identifier column)
Money: A currency type that can contain decimals.
Decimal: Decimal, large number of digits
float: decimal
DateTime: Date Type
Char: can contain 8,000 characters
VarChar: More characters can be stored than char
Binary: Used to store fixed-length binary data up to 8000 bytes long
Image: Approximately 2 billion binary data can be stored
2. Default value
When inserting data is if you do not type data (left blank) on a column of a row and the default value is set, the default value is used for this column.
3. Identifier column
The number of the table, auto-increment, with three features:
• The data type of the column cannot be a decimal type
• Control NULL is not allowed
• Only one identifier column per table
4. Check Constraint
Check constraints allow you to limit the integrity of your domain. Creates a check constraint from any logical expression that returns TRUE or false based on a logical operator. For example, you can limit the age, date of birth, and other data that you enter by setting a CHECK constraint
Operation section
• Graphical construction of tables
1. First expand the following node-click New Table
2. SSMs will pop up a table's design box
3, set up several columns, ready to do the operation
4, we all know that each student's school number is definitely unique, so we can set the number of this column as the primary key, do not allow the existence of duplicate
5, set to complete it will become this, a key icon appears, allowing the null value of the tick will be automatically removed
6. Then we can also make an identifier column, find the "Identity Specification" in Table Designer-change the option to "Yes"
7, for a class of students, we can set the column of the Class A default value. When writing the data, if you do not write the class system will automatically fill in the default values we specify.
8. Right-click on the space-you can create a new check constraint so that you can restrict the writing of the data. For example, restricted sex can only be filled with "male" or "female", the result limit 0 to 100 points.
9, here I limit is the result, fill in the expression, close can. Other constraints are similar.
· T-SQL statement build table
As an example:
CREATE TABLE name (
StudentID varchar (ten) is not NULL,
Sname varchar () DEFAULT NULL,
Sex char (2) DEFAULT NULL,
CardID varchar (DEFAULT) NULL,
Birthday datetime DEFAULT NULL,
Email varchar (+) DEFAULT NULL,
Class varchar (DEFAULT) NULL,
Entertime datetime DEFAULT NULL)
Go
!!! First create is the meaning of creation, the table is the form, and name is the name given to the table. followed by (), the contents of () are each column of the table, where the first field is the name of the column, and then the data type of the column, followed by whether null values are allowed null. The go to the back of the statement is executed. Operation, we click on "New Query" in the upper left corner of SSMs-then enter the statement-click "Execute"
After execution, you can see
Refresh "Table"
Create a new table named "name" to see the
SQL Server table design (build table)