SQL Server database fundamentals and data types

I. Base and management of the database:

Persistence Technology: Memory--FILE----database (most commonly used, popular)

1. Database:
1. Store massive data (P) Big data, cloud computing
2. Security
3. Easy to manage and operate (DDL,DML.TCL ...)
4. Sharing of data (concurrent access)

2. Product Brand: (relational database)
Oracle: Oracle (SUN:JAVA) ordbm (object-oriented relational database)
DB2:IBM (RDB)
Sybase:sybase
Mysql:sybase Free (* * *)
Sqlserver:ms (high cost performance, large customer volume) (* *)

3. Relational database language unification (SQL language:)

DDL: Data Definition language (structure)
Create: Creating
Alter: Modify
Truncate: Truncate

DML: Data Manipulation language (data)
Insert: Add
Update: Modify
Delete: Remove
Select: Query (**************************)

TCL: Things control statements
Commit: Submit
Rollback: Rolling back

4. Basic concepts:

Logic: Server--Database--table (entity set)--Rows (entities, records)--Columns
ROM: Relational (DB) objects (object language) mappings
Table----> Entity classes
Row----> Properties
Column----> Entity class object
Multi-line data--Generics < entity classes > Collections

Physical: File
Data files: Data (tables, indexes (faster query speed))
Master data File (MDF) has and only one
A secondary data file (. ndf) can have no or multiple
Role: expansion, sharing of risk and data pressure to put questions
Log files: Record all writes to the database
Role: Data Recovery
Log file: LDF (at least one)

5.Sqlserver:

System database:
Master: Master database, all system-related resources
Model: Template Database
The initial state of all new databases is consistent with the template database
Tempdb: Staging Database
Intermediate data, information, the opening and release of memory
MSDB: Proxy database, primarily for management of proxy functions (DBA)

6. Login Management:

Way 1:windows Mode Login
You can log in if you have permission to the operating system
mode 2:windows and SQL Server hybrid login mode

7. Porting the database:

Separation and addition of databases: (Backup files) Cold backup database is completed in a non-working state
Backup and restore of database: (. bak) Hot backup: Database completed in working state ()

Two. Creation and management of tables

1. Create a table (two-dimensional)
From a logical point of view, data is stored in a table (from a physical point of view stored in a data file)

1) syntax
varchar (character length) is equivalent to string
Example:
Use database name
CREATE TABLE Info
(
ID int PRIMARY KEY,
Name varchar (50),
Age int Check (age>18 and age<=60),
[Address]varchar (+) Delault ' Shanxi province Yongji Chenghuzhuang eight group '
)

--Grammar
CREATE TABLE Table name
(
Column name data type [constraint NOT NULL or NULL],
Column name data type [constraint NOT NULL or NULL],
Column name data type [constraint NOT NULL or NULL],
......
)

1. Data types for databases
Number: Int,decimal (4,2), numeric (4,0)--c# decimal
Float,real
Currency: Money, accurate to four digits after the decimal point

Text type:
Char: fixed-length character type
Name char (x) eg: Zhang San, accounting for 4 remaining 16 left space occupied by space (there is a waste of space)
varchar; variable-length text type
Name varchar eg: Zhang San, 4 remaining 16 remaining space is automatically redistributed by the system (there is no space waste problem)

Thinking:
When to use char when to use varchar
Char
CHAR (18): Suitable for fixed-length characters
Eg: identity card (18), when fixed in length, the char type is more efficient than the varchar type
varchar (): type that is suitable for characters that cannot be fixed length
Note: The default length of no write length is 1

Text: Long text information
Nchar,nvarchar,ntext:
N:unicode: Supports double-byte encoding. (Kanji, Korean ...)
This double-byte text with n is more suitable for storing Chinese characters.

Bit: (bit) 0 or 1 (usually when BOOL is used)
Eg:gender: (1,0)
False Delete flag (1)
8bit=1byte

DateTime: A function of the date event type
Note: added: ' 1999/09/09 '
Built-in functions: System time GETDATE ()
Eg:select CreateDate ()
IMG: Storing binary data such as images
Actual development: The image path is stored in the data

SQL Server database fundamentals and data types