SQL Server Review

SQL is mainly divided into DDL and DML: Create Table \ drop table, ALTER TABLE, etc. are DDL, select insert, etc. are DML;

Two common primary key types in sqlserverzh: int \ identifies a column (also known as an auto-increment column) uniqueidentifier. primary key values are not required when inserting fields in the ID column; the GUID generation function in sqlserver: newid (). in. Net: guid. newguid (), return guid type;

Delete: delete data. Drop table directly deletes the entire table.

Data Retrieval:

To retrieve the required column, calculate the Select column value: Select age + 100 from person

Column alias: Select ID as 'number', name as 'name', age as 'age' from person

You can also retrieve data not associated with any table: select 1 + 1; select newid () Select getdate ()

Aggregate Function: collects statistics on the query results;

Data Sorting: ASC descselect * From person order by age ASC, Id DESC there is a sequence of order, first sort by age, if the age is the same, sort the order by clause in descending order by ID after the WHERE clause: it can be interpreted as sorting the result set.

Wildcard filter: use like single-character wildcard half-width underline _, multi-character wildcard half-width percent %

Null Value processing: If no value is specified for a column in the database, the value is null. This is different from the null roommate in C #. If the column value is null, is used: null Value processing function isnull ('value to be judged ', 'returns the second parameter if the value to be judged is null ')
Multi-value matching: Use in: Select * From person where ID in (1, 2, 3) between And: Select * From person where ID between 2 and 4

Data grouping: groups data by fields and processes the data of each group. The field names that appear in the SELECT statement must be Aggregate functions or fields that appear in the group by statement;

Fields in the aggregate function can be fields in other columns; select count (*) as 'People with the same name', name as 'name', max (AGE) as 'maximum age in the reorganization 'from person group by name

Having statement: you cannot use aggregate functions in the WHERE clause. Having must be placed after group by to filter groups; fields that can be found in having must be the same as those that can be used in select, that is, the data in the group should be filtered.

Limit the number of rows in the result set: row_number () function added after select top or SQL Server 2005

Remove duplicate data:

Distinct is for the entire row rather than for a specific field: Select distinct name, age from personu

Union

Combine the two execution results. Select ID, name from person2 Union select ID, name from person indicates the number of columns in the upper and lower rows. The data type of the field is the same as that of select 0, name from person2union select ID, name from personunion

Duplicate data is merged by default.

Union all

Select 0, name, age from person2union allselect ID, name, age from person data report instance select 'minimum age in person2', min (AGE) from person2union allselect 'maximum ages in person ', max (AGE) from personunion allselect 'minimum ages in person', min (AGE) from person
Numeric Functions

ABS () ceiling () floor () round ()

String functions:

Len () lower () ltrim () rtrim () substring ('', strarposition, length)

Date functions:

Getdate () dateadd (depart, number, date) is the date added after calculation. The date parameter is the date to be calculated, the number parameter is incremental, and the depart parameter is the measurement unit, such as day, year... datediff (depart, statrdate, enddate) datepart (depart, date); returns a specific part of a date; obtains the number of new recruits each year. Case function usage:

It can be used to modify the output style of a table:

Single value judgment,

Equivalent to switch cas

Ecase expression

When value1 then returnvalue

When value1 then returnvalue

Else defaultreturnvalue

End

Implement if else,

There is no expression after case:

Select *,

(Case

When age> 30 then 'older youths'

When age = 30

Then '30'

Else 'kid'

End)

As 'customer type'

From person

Index:

You can only create an index on fields that are frequently searched. Even if an index is created, full table scanning is possible, for example, like, function,

Type conversion

Join Query

Subquery: a query statement is used as a result set for other statements, just like a common table,