SQL Server Database outline

SQL Server Database
I. Architecture
FILE--Service--Interface

File---. MAF. ldf. ndf
Service--mssqlserver, alias (instance name)
Interface--Soong Ching Ling serve how to Windouws authentication, how to change from Windows identity stewards to SQL Server authentication
Second, create
Build database, change database, delete CREATE database ALTER DATABASE DROP database
Build table, change table, delete table CREATE table ALTER TABLE DROP table

1 Library: File location, initial size, growth mode, secondary data file
Table: Types of columns
Int,smallint,bigint
float, real decimal numeric
Bit
Char, Varchar,text
Binary image
DateTime smalldatetime
2 constraints:
Primary key: Build? Primary key
Features: Unique, not empty, not heavy, sort, combined primary key
FOREIGN key: The primary table and the from table. The foreign key is built from the table. Using the main Table constraint from the table
Mode: 2 Kinds of referrnces (column name)
Cascading updates and cascading deletions? Design inside
Non-null: must be filled in. Null is not the same as an empty string. NOT NULL
Self-growth: type: Collation. The starting value and the step size. Identity
Method: Properties of the column
Any view that adds data to the self-increment column behaves incorrectly.
Check: checking (expression) for further rule checking of filled data
Method: Right-click on the column, select Check Constraint
Unique constraint: unique. The content cannot be heavy and can be empty. There can be more than one unique constraint in a table
Method: Right-click the column Select Index/Key
Default value: If this column does not fill in the contents, the default value will be used
3 operation
1. SQL statements
Insert into table name (columns, columns, Columns • ) "Values (value, value, value)
Delete delete from table name "Where condition"
Change the Update table name set column = value, column = value "Where Condition"
Check
Simple query select top N *| column from table
Projection
Select column name, column name • • From table
Sort
Order BY column Asc|desc, column Asc|desc
Screening
WHERE clause
1. Equivalent to no equivalent. where column = value. = = <> < >= <=
2. Scope
where between and
where column in (value, value, value)
More than 3 pieces of query
And OR
Where Condition 1 and condition 2 or condition 3
4 Fuzzy Query
Like% _ [4,5,6]

Grouping (count (), Max (), Min () avg (), SUM () five statistical functions)
Group BY column having a condition (with statistical function)
Note: Once you use a grouped select, you can only follow two things: 1 groupby column Name 2 statistic function
Note In general, we use groupings for statistical purposes.
Complex queries
Connect: Bar Two tables cross-linked
Principle: The Cartesian product is first obtained and then filtered according to the related columns of two tables
Grammar:
1. Select * FROM table 1, table 2 Table n WHERE join condition
2. Select 8 from table 1 join table 2 on Join condition join Table 3 on join condition
3, leftist left right connected full
Joint principle: seeking and combining
Union
Bar 2 queries are combined with a union that requires the columns of 2 queries to be consistent.
Nesting (sub-query)
Include sub-queries in parent queries
Principle: Hierarchical Query
Its sub-methods: equal substitution.
SQL functions
1. Math class: Ceiling (), floor () round ()
2. Time class: Year () month () Day () getdate () DateDiff () datepart () DateAdd ()
3 string: Len () Left () right () upper () lower ()
4. Conversion function cast (xxx as type)
Iv. High-level content:
Index
Clustered index, non-clustered index.
Advantages: Fast Disadvantage: Change
Syntax: CREATE INDEX On table
manually build the index; Similar to building unique constraints
View
A view is a query result of a query statement
The view itself does not contain data, and the results of the view are isolated from the related tables.
Views can be set up from a table or other view.
View benefits: Easy to find
Disadvantage: not easy to change and delete
Syntax: freate view
Manual: View below table new
Stored Procedures
An SQL statement that may have some procedural logic.
It can contain complex program logic.
Syntax: creation: CREATE proc stored procedure name @ parameter ... As
Call: Stored Procedure name parameter
Trigger
is also a logical SQL statement.
It does not need code to invoke, when the corresponding table performs related operations, the trigger will automatically trigger the program.
Syntax: Create TRIGGER
Things
A complete job that requires a full execution.
Four features: ACID. Atomicity, consistency, isolation, persistence
Grammar BEGIN tran---start things
Rollback----rolling back things
Commit----Commit things

SQL Server Database outline