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