SQL Server Learning Note 1

Database design Paradigm

1 Paradigm: Column atomicity, column cannot be split

2 Paradigm: Cannot describe multiple information in a table and cannot have data sink

3 paradigm: Referencing primary key information from other tables

Constraints

1. A non-null constraint is not NULL. Cannot show null values
2. PRIMARY KEY constraint (PK) PRIMARY KEY constraint. Unique and not empty
3. Unique constraint (UQ) Unique constraint. Allowed to be empty, but not repeatable
4. The default constraint (DF) is defaulted constraint. Default to fill in a value, such as the default fill in the male
5. Check constraint (CK) Check constraint. Check to see if the information you fill in meets your needs.
6. FOREIGN KEY constraint (FK) foreign KEY constraint. You can do it or not, and make sure your data is more effective when you do it.

Create a database

MS SQL Server each database contains

1 primary database files (. mdf) must be

1 things log file (. ldf) must

Can contain:

Any number of secondary data files (. ndf)

Multiple things log files

Filegroups: Multiple data files can be logically divided into a group for later management maintenance (backup, to build the table on the specified filegroup)

About column types

Text type: differentiate Var, n

For cases without Var, the non-variable length, if the assignment is not enough to specify the number of digits, with a space

For cases with VAR, the variable length, if the assignment is not sufficient to specify the number of digits, the actual assignment will prevail

For Unicode this encoding format

2 bytes per character if n is taken

If not with N, then English is 1 bytes, Chinese two bytes

FOREIGN key

Correspondence: 1 to 1, 1 to many, many to many

1 to 1, this data is stored in either side of the table can be

1-to-many relationships that store relational data in more than one side of the table

Many-to-many relationships, creating a single table for storing relationships

The information stored by the column created by the relationship needs to meet section 3NF

Example: Department-employee relationship is 1:n

As long as the EDID (Department ID) column is established in the employee table, the relationship between the department and the employee is stored. The foreign key is just a constraint that guarantees the validity of the data, and the foreign key needs to be built on the relationship column.

Script

Script execution shortcut keys Ctrl+e

SQL is mainly divided into DDL (database definition Language building tables, building libraries and other statements), DML (Database manipulation language) and DCL (Database control Language). CreateTable, altertable, etc. belong to Ddl,select, Insert, Update, delete belong to dml,grant authorization, revoke de-authorization belongs to DCL.

1. Create a database

CREATE DATABASE Name

2. Deleting a database

Drop database name

3. Create a table

Create Table Name (

Column name Type ....

CName nvarchar (ten) is not NULL,

List name is: CName

Type: nvarchar (10)

Null not allowed: NOT NULL

)

4. Delete a table

Drop Table Name

5. Adding constraints

Alter Table Name

ADD constraint ck_cname check (len (CNAME) >2)

SQL Server Learning Note 1