SQL Server 2005 Notes Summary

SQL classification

DDL Data Definition Language

Create Alter drop

DML Data Manipulation language

Insert Update Delete Select

DCL Data Control Language

Grant revoke deny

Creation and management of databases

File data file (primary data file. MDF secondary data file. ndf)

Log file. ldf

File group Logical structure contains data files

Create a database

Create DATABASE name

On primary

(name=,filename=,size=,maxsize=,filegrowth=),

Filegroup File Group name

(name=,filename=,size=,maxsize=,filegrowth=)

Log on

(name=,filename=,size=,maxsize=,filegrowth=),

(name=,filename=,size=,maxsize=,filegrowth=)

Modify

ALTER DATABASE name

|ADD Filegroup File Group name

|add file () to filegroup filegroup name

|add log file ()

|modify name= New database name

|modify file (name=,newname=,size=,maxsize=,filegrowth=)

|modify Filegroup file Group name default

|remove file

|set Database Options

Delete

Drop database name

Database shrinking

DBCC SHRINKFILE

DBCC SHRINKDATABASE

Database Information View

sp_helpdb

DBCC SQLPERF

sp_spaceused

DBCC CHECKDB

System data type

Integral type: bigint int smallint tinyint

Exact value: Decimal (p,s) numeric (p,s)

Approximate value: float real

Currency: Money smallmoney

Date: DateTime smalldatetime

--sql2008

Date Time DateTime2 DateTimeOffset

Character: char (n) varchar (n) varchar (max)

NCHAR (n) nvarchar (n) nvarchar (max)

Bit uniqueidentifier table

......

Creation of tables

CREATE TABLE Table name

(Column name data type,

The column name data type is NOT NULL,

Column name data type identity (seed,increment))

Changes to the table

ALTER TABLE table name

|add Column name data type properties

|alter Column name new data type | old data Type property

|drop Column name data type

Deletion of tables

DROP table Name

Dml

Insert

Insert into table name (List of column names) values (corresponding values)

Note: Character and date data plus single quotation marks

Insert into table name (List of column names) SELECT statement

Update

Update table name set column name = value WHERE condition

Delete

Delete from table name where condition

Select

Sentence sentence in a language

Select: Columns in the results

*

Column Name

Distinct

Top (n) [percent]

+

Function

Arithmetic operators

With ties

Column Aliases

Column name as column alias

Column list Alias

Column alias = column name

into: Put query results into a new table

From: Data source (specific table name; cte;derived table)

Where: Criteria for qualifying rows of data returned

Logical operator and not OR

Comparison operators > < >= <= <>! = =

Arithmetic operators

Between...and ... : Given range of values, including boundary

In (Value list): Take the given value

Like: Fuzzy query for Strings

Wildcard:%: Any character of any length

_: Any single character

[]: Take any one of the given

[^]: In addition to the given character, arbitrarily take a

Is null: null value judgment

Exists: judging the existence of

GROUP BY: Group Summary + aggregation function

Having: Filtering the data after grouping

ORDER BY: Sort

Ascending ASC (default) Descending desc

You can sort by more than one column null as the minimum value

Join: Multi-table connection

Where select column name from Table 1, table 2 where table 1. column = table 2. Column and condition

Join select Column name from table 1 *join* table 2 on table 1. column = table 2. Column WHERE Condition

INNER JOIN: [Inner] Join connection field value in two tables the same data

Left outer junction: The value in the left [outer] Join connection field is the same data in both tables, the value of the join field has data in the right table that is not

Right outer join: The value of the Join join field in two tables is the same data, the value of the join field in the right table has data that is not in the left table.

Full link: The value of the join field is the same data in both tables, the value of the connection field in the left table has no data in the right table, the value of the join field in the right table has data that is not in the left table.

Stu Exam

ID Name ID EID score

1 a 1 2072 100

2 B 2 2072 80

3 C 3 2072 60

4 D NULL 2072 70

Cross Connect

Cartesian product M*n

Select Column name from Table 1 cross Join table 2

Self-connect

Union

Row Overlay

SELECT statement *union* SELECT statement

Union//union all//intersect//except

Sub-query--can be converted to multiple tables

Select field from table 1 where field in (SELECT statement)

Data integrity

Entity integrity: Row Unique

Domain Integrity: Limit the range or format of values for a field

Referential integrity: Maintaining data consistency between tables

Implementation: Rule DEFAULT constraint

Constraints

PRIMARY KEY constraint primary key

A table has only one PRIMARY KEY constraint that can be created on multiple columns, the field that creates the primary key constraint is unique, cannot be null, and creating a PRIMARY key constraint automatically creates a unique clustered index

Uniquely constrained unique

A table can have multiple unique constraints that can be created on more than one column, the field that creates a unique constraint can be null, the value is unique (including null), and creating a unique constraint automatically creates a unique nonclustered index

Check constraint check

Limit the value range or format of a field

Default constraint Defaults

If a column defines a DEFAULT constraint, the default value is automatically populated when data is inserted without a value

FOREIGN KEY constraint foreign key

Referenced column of referenced table to define a PRIMARY KEY constraint or UNIQUE constraint, maintain data consistency between tables

Create a constraint ************************************ while creating a table

CREATE TABLE Table name

(Column name data type,

The column name data type is NOT NULL,

Column name data type primary key,

Column name data type unique,

Column name data type check (expression)

Column name data type default defaults,

Column name data type references referenced table (referenced column))

Add constraint *************************************** When table creation is successful

Primary key (primary key field)

Unique (single field)

ALTER TABLE name ADD constraint constraint name check (expression)

Default value for column name

Foreign key (reference field) references referenced table (referenced column)

View

Category: Normal view partitioned view indexed view

Only view definitions in the database, no data stored

Create a View

Create VIEW View name

As

SELECT statement

View Options

Create VIEW View name

With Encryption | Schemabinding

As

SELECT statement

With CHECK option

Backup restore

Backup target

Backup device: Implementation is created to view backup content through commands and graphical interfaces

Backup files: Created at the same time, can only be viewed by commands

Failure Recovery Model

Bulk-Logged: The process of recording only the results of a large volume data operation

Simple: Unable to log backup

Complete:

Backup Type

Full backup

Backup database name to disk= '

Backup database name to back up device name

Differential backup

Backup database name to disk= "with differential

Backup database name to back up device name with differential

Log backup

Backup log database name to disk= '

Backup log database name to back up device name

File and filegroup Backups

Backup database name file= to disk= "

Backup database name filegroup= to back up device name

Tail-Log backup

Backup log database name to disk= "with No_truncate

Backup log database name to back up device name with No_truncate

This article is from the "Technician Training Manual" blog, please be sure to keep this source http://juispan.blog.51cto.com/943137/1949879

SQL Server 2005 Notes Summary