6. SQL Server Data Query

First, use Select to retrieve data

Data query is the central content of SQL language, the function of the SELECT statement is to allow the database server to retrieve the required information according to customer requirements, and in accordance with the required format to be collated, returned to the client.

Basic structure of the SELECT statement

With clause

The WITH clause is used to specify a temporary named result set, which becomes a common table expression (CTE). The expression originates from a simple query and is defined within the execution scope of a single SELECCT, INSERT, update, or DELETE statement.

The query is named Agereps the age of the temporary table, the total age. The staging table groups the ages and displays the number of ages.

Select FROM clause

SELECT indicates that you want to read the information from the name of the table or tables from which you want to get data.

SELECT * FROM Tables/* * Query all columns */select id,name from tables/* Query specified column */select tables.id,tables.name from tables/* can also be table. Column name * * Aliases display */select tt.id id,tt.name name, tt.sex sex, tt.age age from Ttselect id = tt.id, First name = Tt.name from Ttselect tt.id as ID, Tt.name as name, tt.sex as sex, tt.age as age from TT

INTO clause

Insert the results of a query into a new table

Select Tt.id,tt.name,tt.age into newtable from TT

WHERE clause

Append search criteria for search

1. Logical operators (not, and, or)

1.1 Not: Negates a Boolean input, using not to return rows that do not satisfy an expression.

1.2 and: A combination of two Boolean expressions that return True when two expressions are true.

1.3 OR: Combines two conditions to true when any one is satisfied.

The precedence order is not and OR.

SELECT * FROM [user] where id=1 and name = ' a ' or name= ' BC ' and not name= ' d '

2. Comparison operators

= (equality), <> (not equal to each other),! = (does not equal each other), > (greater than), >= (greater than and equal to),!> (not greater than), < (less than), < = (less than equals),! < (not less than).

SELECT * FROM [user] where > 24

Like keyword

SELECT * from tables the where name like ' King% ' looks for the king to begin with. SELECT * from tables the where name like ' Wang _ ' looks for a word after the king begins. SELECT * from tables where name isn't like ' king _ ' find not a word after the beginning of the king. SELECT * from tables where the age like ' 2[2-4] ' finds 2 followed by 2 to 4. SELECT * from tables where the age like ' 2[^2-4] ' find 2 is no longer between 2 and 4.

Between keywords

SELECT * from tables where the age between and 24 finds between 22 and 24. SELECT * from tables where the age of not between and 24 finds no longer between 22 and 24.

Is (not) NULL keyword

You cannot use the comparison operator (=) in the WHERE clause to judge null values, and you can only query null values with IS NULL.

SELECT * from tables where an IS null query age is empty. The select * from tables where the age was not null for the age does not empty.

in keyword

Use the In keyword to specify whether the search scope matches the values in the subquery or list.

SELECT * from tables where ID in (' 001 ', ' 002 ', ' 003 ') to find the ID range in 001 002 003. SELECT * from tables where the ID not in (' 001 ', ' 002 ', ' 003 ') looks for an ID range other than 001 002 003.

All, SOME, any keywords

All: Compares scalar values and values in a single column, used with comparison operators and subqueries. >all identifies each value that is greater than the condition, greater than the maximum value.

SELECT * from the TT where age > All (select Age from TT where age = 24) query is greater than the query value in all.

some| Any: Compares scalar values and values in a single column, SOME and any are equivalent, and are used with comparison operators and self-queries. > Any represents a value that is at least greater than the condition, greater than the minimum value.

SELECT * from the TT where age > any (select age from TT where age = 24)

EXISTS keywords

Select ID, name from [user] where exists (select null)

Goup BY clause

A set of selected rows is combined into a single summary rowset according to the values of one or more columns or expressions, and one row for each group, grouped.

Select age from TT GROUP by age

HAVING clause

Typically used in the GOURP by clause to specify conditional searches in the grouping.

Select Age-From-TT GROUP BY-age has age = 24

ORDER BY clause

The ORDER BY clause is not valid in views, restrained functions, derived tables, and subqueries unless you specify top at the same time.

SELECT * from tables the ORDER by id DESC, sorted by ID in reverse order. SELECT * from tables ORDER by ID ASC is sorted in ascending order by ID.

COMPUTE clause

The resulting totals are listed as additional summaries at the end of the current result set. When used with by, the COMPUTE clause generates control interrupts and subtotals within the result set. COMPUTE by and COMPUTE can be specified within a unified query.

All keyword

Query all records.

Select All-From tables

DISTINCT keywords

Remove duplicate records from the search results.

Select distinct age from tables

TOP keywords

Limit the number of rows that the query results display

Select Top 5 * from tables.

Ii. using union to merge multiple query results

The merge operation of the table merges rows from two tables into one table without any changes to those rows. When you construct a merge query, you must:

1. Two SELECT statements the number of columns in the selection list must be the same, and the data type of the column at the corresponding location must be the same or compatible.

2. The name or alias of a column is determined by the selection column of the first SELECT statement.

3. You can add an expression for each SELECT statement that represents the data source for the row.

4. The merge operation can be used as part of a SELECT INTO command, but the Info association must be placed in the first SELECT statement.

5. The merge operation removes duplicate rows by default, and if you want to return duplicate rows, you need to use the ALL keyword.

6. The ORDER BY clause that sorts the result of the merge operation for all SELECT statements must be placed after the last select, but the sort column name must be the column name in the first select select list.

The difference between UNION and join

In a merge, the number of two table source columns must be the same as the data type, and in a join, the rows of one table may differ greatly from the rows of another table.

In a merge, the maximum number of rows is the "and" of two tables. In joins, the maximum number of rows makes their "product"

De-weight: Select Id,name from [user] union select Id,title from work results to ID name below two table information

Repeat: Select Id,name from [user] union ALL select Id,title the from work contains duplicate rows

Sort: Select Id,name from [user] union ALL select Id,title from work order BY name DESC column in first table

The number of columns is different: select Id,name,sex from [user] union ALL select Id,title,null from work order by name DESC with null padding

Sub-query

A subquery is a query that is nested within a SELECT, insert, UPDATE, or DELETE statement or other subquery, and returns a single value.

SELECT * from tables where ID not in (1,3,4).

nested queries

Nested queries are queries that nest a query block in the conditions of another query block's WHERE clause or having phrase.

SELECT * from tables where ID in (select typeID from type where id =) ID range in the returned result select * from tables where ID not in (s Elect typeid from type where id=84) ID range not in returned result select * from tables where age < Some (select AVG (avg) from student) less than Average age select * from tables where ages <> any (select AVG (avg) from student) age is not equal to average age select * from tables where ages < > All (select avg from student where ages > 90) Age does not have information greater than 90 select * from tables where is NOT EXISTS (select ID from user where tables.id = user.id) Query ID not equal information

Join query

Horizontally merges two data sets, producing a new result set that can be specified in the FROM or WHERE clause.

Inner joins

An inner join removes all rows from the result that have no matching rows in the joined table, so information may be lost.

SELECT * FROM [user] inner joins [work] on [user].id=work.id Query two table IDs for the same information.

Outer Joins

1. Left OUTER JOIN

If a row in the left table does not have a matching row in the right table, it is displayed as a null value in the associated result.

SELECT * FROM [user] left join [work] on [user].id = work.id Show All user information if work does not have corresponding information displayed as null

2. Right outer join

In contrast to the left join, if a row in the right table does not have a matching row in the left table, it is displayed as a null value.

SELECT * FROM [user] Right join [work] on [user].id = Work.id to work as principal if user does not have corresponding information displayed as null

3. Complete outer JOIN full join

Returns all rows of the left and right tables, and when one row does not have a matching row in another table, the selection column of the other table will contain null values.

SELECT * FROM [user] full join [work] on [user].id = work.id Displays all information, if not NULL

Crossing Join Cross Join

The number of rows in the first table multiplied by the number of rows in the second table equals the size of the result set

SELECT * from [user] crosses join work average cross complementary does not show null

Multiple table joins

Where:select * from Table1,table2,table3 where table1.id =table2.id and table2.id = Table3.idFORM:select * FROM table1 Joi n table2 Join table3 on table1.id = table2 and table2.id = Table3.id   

Iii. querying with the case function

Select number = CASE/* alias */    when id = Ten Then ' is 1 oh '/* If id=1 then the output is 1 Oh */when id = one and then    ' 11 Oh ' when    id = th En ' 12 oh '    Else ' no '/* Otherwise output not */    end from [user]/* modified */update [user] Set sex = case when     sex= ' n ' Then ' man '    W Hen sex= ' A ' Then ' woman ' end

Iv. functions

Aggregation functions

COUNT (*): Returns the number of rows. Count (column name): Returns the number of columns. AVG (column name): Returns the average of a column. Max (column name): Returns the maximum value of a column. Min (column name): Returns the minimum value of a column. SUM (column name): Returns the sum of a column value.  

Open Window function

--After using an aggregate function, the returned result can be only one row--using over () can extend the aggregate function to all rows-the syntax aggregation function () over () Select AVG (score) from student; --Returns an average of one message. Select *,avg (Score) over () from student; --Returns all data and the last column of the average

Date-time functions

Select GetDate (); Current system date Select DATEADD (Day,3,getdate ()); Plus 3 days Select DATEADD (Year,3,getdate ()); Plus year Select DATEADD (Hour,3,getdate ()); Plus hours Select DateDiff (Day, ' 2013-02-01 ', getDate ());  Returns the difference in days Select DateDiff (Second, ' 2013-02-01 ', getDate ()); Returns the number of seconds difference select Datename (month,getdate ());  Returns the current month of select Datename (Minute,getdate ()); Returns the current minute select Datename (Weekday,getdate ()); Returns the current week Select Day (GetDate ());  

numeric functions

String functions

Select ASCII (' a '); Returns the ASCII value of select CHARINDEX (); Returns the starting position of the string Select Unicode (' a '); Returns the Unicode value of select ' A ' + space (2) + ' B '; Output Space Select difference (' Hello ', ' helloWorld '); Compare strings with the same select replace (' Abcedef ', ' e ', ' e '); Replacement string Select stuff (' Hello World ', 3, 4, ' ABC '); Specifies the location substitution string for select replicate (' abc# ', 3); Repeat string number of select SubString (' abc ', 1, 1); Intercept string Select Len (' abc '); Returns the length of select reverse (' SQL Server '); Invert string Select left (' leftstring ', 4); Take the left string select right (' leftstring ', 6); Take the right string select lower (' abc ')  convert lowercase Select upper (' abc ') convert uppercase Select LTrim (' abc ') to remove left space select RTrim (' ABC    

Conversion functions

Cast (data as type); convert (type, data); Select ' 123 ' + 123;--output results in 246 database with digital precedence select ' 123 ' + CAST (123 as varchar); --The output is 123123 note it is not possible to nvarchar because this is a fixed length. Select ' 123 ' + convert (varchar,123); --123123

6. SQL Server Data Query