SQL Server-T-code Basic Tutorial--t-sql query and programming background

Theoretical background

1. sql:structured Query Language is the standard language for querying and managing data for RDBMS (relational Database Management Systems).

2. Understanding set theory and predicate logic, RDBMS is based on these two mathematical branches.

3. SQL statement Category:

Data definition Language, DDL: The definition language, which handles the definition of objects, including statements such as CREATE, alter, and drop.

Data manipulation LANGUAGE,DML: An operational language for querying and modifying data, including statements such as SELECT, INSERT, UPDATE, DELETE, truncate, and merge.

Data Control LANGUAGE,DCL: Controls language for defining permissions, including statements such as Grant and revoke.

4. Relational models need to understand concepts such as propositions, predicates, relationships, tuples, and attributes.

Paradigm: 1NF: Tuples (rows) in relationships (tables) must be unique and attribute stones to be atomized.

2NF: 1NF is required, and each non-key attribute must be a complete function dependency on the entire candidate key.

3NF: 2NF is required, all non-key attributes must depend on non-transitive candidate keys (all non-key attributes must be independent of each other).

5. SQL Server Architecture

A: Application application, Parallel Data Warehouse (Parallel-Warehouse, PDW), etc.

B: box, SQL Server on-premises

C: Cloud, private and public clouds. Windows Azure SQL Database Public cloud

6. SQL Server Instance

A SQL Server database engine or service that is already installed.

7. Database

user databases and System databases: an object container, such as tables, views, stored procedures, and other objects.

System database: Master: Stores the instance-scoped metadata information, server configuration, all database information in the instance, and initialization information.

Resource: A hidden, read-only database with the definition of all system objects.

Model: Used as a template for creating a new database, each new database created is created by a copy of model. If you want certain objects (such as data structures) to appear in the new database, you will need to create them in the model database or configure them (the database that only affects new pieces later).

Tempdb:sql server stores temporary data, such as worksheets, sort spaces, row versioning information, and so on.

MSDB: Where a service called SQL Server Agent stores data, SQL Server Agent is responsible for automating operations, including jobs, schedules, and alerts. Also stores Database Mail, Service Broker, backup, and so on

8. Create a table

The OBJECT_ID function is used to check that the Employees table is already present, accepts an object name and type as input, and U represents the user table.

Use tsql2012;if object_id (' dbo. Employees ', ' U ') is not NULL    DROP TABLE dbo. Employees; CREATE TABLE dbo. Employess (   empid             INT not                     null,   firstname       varchar (+) not      null,   lastname        varchar ( No null      ,   hiredate DATE not                   null,   mgrid INT not                      null,   ssn                  VARCHAR ()      Not NULL, the salary money is not                null);

Create Table

9. Define Data integrity

PRIMARY KEY constraint: enforces the uniqueness of rows, each table can have only one primary key, and the primary key is not allowed to be null.

ALTER TABLE dbo. Employees     ADD constrant pk_employees     PRIMARY KEY (empid);

Create a PRIMARY KEY constraint

Unique constraint: Enforces the uniqueness of the row, allowing the concept of alternate keys for the relational model to be implemented in its own database. Multiple unique constraints are allowed within the same table, no requirement to allow null for the field, but SQL Server rejects duplicate null tokens.

ALTER TABLE dbo. Employees   ADDCONSTRAINT  unq_employees_ssn   UNIQUE(SSN);

Create Unique

FOREIGN KEY constraint: used to enforce referential integrity. When you delete or update rows in a referenced table, you need a cascading cascade reference table, or the operation fails.

IF object_id('dbo. Orders','U') is  not NULL    DROP TABLEdbo. Orders;CREATE TABLEdbo. Orders (OrderIDINT    not NULL, EmpidINT   not NULL, CustIDVARCHAR(Ten) not NULL, QtyINT          not NULL,  CONSTRAINTpk_ordersPRIMARY KEY(OrderID));

Create Orders Table

ALTER TABLE dbo. Orders   ADD  CONSTRAINT  fk_orders_employees   FOREIGN  KEY(empid)   REFERENCES dbo. Employees (Empid);

Create References

Check constraint: The following constraint, if the attempt to update or insert salary row, the operation will fail.

ALTER TABLE dbo. Employees    ADDCONSTRAINT  chk_employees_salary    CHECK>  0.00);

Create Check

Default constraint: If you insert a row, no explicit value is specified for the row, and the default value is used.

ALTER TABLE dbo. Orders     ADDCONSTRAINT  dft_orders_orderts     DEFAULT for Orderts;

Create Default Check

SQL Server-T-code Basic Tutorial--t-sql query and programming background