Database data types, constraints, basic syntax

One: Data types in the database

1. Shaping, int (common, 4 bytes), smallint (2 bytes), tinyint, bigint (8 bytes)

2. Float type: float (Common) decimal (width, precision) has width and precision, Width contains decimal point, real

3. Logic type: bit (computer default "0" is false, "1" is true.)

4. Character type: Char 8000 bytes, the disadvantage is that it will automatically fill the space, for fixed-length data; Vachar 8000 bytes, does not fill the space, often use, Nchar,nvachar

5. Time and Date type: datetime, Minimum value is January 1, 1753; Samall datetime is January 1, 1900, the maximum is December 31, 2079

6. Currency: Money decimal place, commonly used in finance

7. Binary type: Binary cannot directly lose value, can only use the program input, after the output is not displayed, the password can be used this type

var binary (max) length Unlimited, imag type, no size limit can save picture or as varbinary

8. Text Type: Text save article, no size limit

Second: Other knowledge points

About the self-growth column, select a column, find the following identity specification, there is an identity increment, choose to be able to define the column self-growth, self-growth column does not fill auto-increment, if this line input is wrong, the new input line will automatically increase, the equivalent of the wrong line deleted, added a line:

Three: Constraints

1. PRIMARY KEY constraint: Guarantee the integrity of the entity, each value is unique, no duplicates, select a column in the upper left corner to set the primary key:

The primary key column to ensure that there is no duplicate value, there can be no null value, set up after the automatic sorting, it can only distinguish between each row of data, can improve query efficiency, a table can only have a primary key, generally do not have to self-growth column when the primary key.

There is also a composite primary key, which is a combination of two or more columns as the primary key.

2. FOREIGN KEY constraint: The value of one table must be guaranteed to exist in another, the primary KEY constraint of the main table from the foreign key of the table

Right click on the database diagram, select New database diagram, add table, right click from table, select Design, right click on Foreign key column, select Relationship, click "..." After table and column specification, select Set Primary key table name, primary key, foreign key table name, foreign key, the relationship name should be filled as required; Click OK to generate the data graph. You can also create foreign key constraints in a data graph by dragging and dropping them.

3. Uniqueness constraint: Sets the value of a column to be unique and non-repeatable

4. Check constraints: Fill in a constraint condition

5. Default constraint: Default one value if not filled

Four: Grammar

1. Starting the server: net start SQL Server

Shut down server: net stop SQL 2. New query: Create DATABASE MyDB or

3. Creating a database: Create a new query

Create DATABASE MyDB
On primary
(
Name=mydb,
Filename= "D:\MyDB.MDF"
),--Create a master data file called MyDB, placed in the D drive.
(
NAME=MYDB1,
Filename= "E:\MyDB1.NDF"
),--Create a secondary data file called MyDB1, placed in the E-disk.
Log on
(
Name= "Mydb_log",
Filename= "E:\MyDB.Log"
)--Create the MyDB log file and place it on the E disk.

4. Open the Database
Use MyDB
Use master

5. Modify the data
ALTER DATABASE MyDB--Modify MyDB databases
Add file--adding file files
(
Name= "MyDB2",--Create a secondary data file called MyDB1.

Filename= "C:\MYDB2.NDF"--Place the MyDB data file in the C drive.
)

6. Deleting a database

Drop Database MyDB

7. View the database information sp_helpdb mydb

8. Renaming a database
Sp_renamedb ' Newmydb ', ' MyDB '

9. Create a table
CREATE TABLE Login
(
UserName varchar (primary) key,--the primary key .
Password varchar () NOT NULL, --cannot be a null value .
Name varchar (unique)--a unique key is built .
The SEX bit default 1 ---------Defaults to constraints (default constraints).
Birthday datetime Check (birthday> ' 1900-1-1 ') -constructs a check constraint .
)

10. Modify the Table
ALTER TABLE Login add Money float--add
ALTER TABLE login drop Column money--delete
ALTER TABLE Login ALTER COLUMN money Real--Modify

11. Delete a table
drop table Login

Database data types, constraints, basic syntax