SQL SERVER Data Query

Source: Internet
Author: User

Zookeeper

This article describes the query statements. All examples are performed according to the following table.

StuID

StuName

Age

Sex

11090241031

Wang Xiaohu

21

Male

11090241032

Wang xiaoliu

22

Male

11090241033

Zhang San

23

Male

1. Use the where clause to query the comparison queries of records that meet the conditions in the table.

What is commonly used in arithmetic comparison is the so-called >,<>,= ,! <(Not less ),! = (Not equal to). The comparison syntax is as follows:

Select * from T_student where age!> 30

Select * from T_student where stuName <> 'wang xiaoqi'

Note: In the where clause, if numeric data is compared, single quotation marks can be omitted. If it is a string or date, it must be caused by single quotation marks.

 

Between and

Used to query records within a specified condition range

Select * from T_student where agebetween 3and 45

Note: The query records of between and include two boundary values.

Query records matching the list using the IN Operator

You can use the IN operator to query records matching the list.

Select * from T_student where agein (32, 15)

Note: You can also use the not in operator in SQL statements to exclude some records.

 

Logical Query

In SQL, logical operators include AND, OR, AND NOT.

AND: records that meet multiple conditions at the same time.

OR: Used to satisfy any of the multiple conditions

NOT: record used to satisfy the inverse Condition

Select * from T_student where agein (30, 15) andstuName = 'wang Xiaohu'

(If one condition is False, the result is not displayed)

Select * from T_student where agein (32, 15) orstuName = 'wang Xiaohu'

(Records can be displayed if any of the conditions is met)

Select * from T_student where agenotin (32, 15)

(Query is a counter-condition record, that is, a record whose age is 32 or 15 is not met)

Complex logic

Complex logic is actually a combination of the AND, OR, and not operators. As we know, operators also have a sequential order.

NOT> AND> OR

Select * from T_student where agenotin () ORstuName = 'wang xiaoliu'

(The query results show that the age is not 32 or 15, or the name is Wang xiaoliu)

 

Null Value Query

When using an SQL statement, it is sometimes possible that there is no value in this field, so it is called a null value, and the query statement is also very simple.

Select * from T_student where stuNameISNOT NULL

(Query records whose summary names are not null)

Like fuzzy query

Fuzzy search using the LIKE Operator

When using SQL, you may encounter a situation where you cannot fully determine the information you want to search for. However, this information has some obvious characteristics, so you can use the following fuzzy query

Match any single character

The wildcard "_" indicates matching a single character, which can be used in queries.

Select * from T_student where stuName like 'wang _ hu'

(Query Wang * Hu's records)

Matches 0 or multiple characters

The wildcard "%" indicates that the wildcard matches 0 or more characters. That is, one "%" indicates one or more characters.

Select * from T_student where stuName like 'wang %'

(The record of the first character Shi Wang in the name)

 

Wildcard []

Any single character in the character Column

Select * from T_student where stuName like '[Faye Wong] %'

(Records whose names start with Wang or Fei)

Wildcard character [^]

Represents any single character not in the character Column

Select * from T_student where stuName like '[^ Fei] %'

(Indicates that the name is not a record starting with "already)

 

Escape characters

What should I do if a field in the data table itself contains wildcards when I use SQL to perform fuzzy queries?

(1): Add an escape character before "%" or "_" to be escaped. The escape character can be any character.

(2): specify the name of the ESCAPE character after the ESCAPE keyword.

Select * from T_student where sutID like '% $ _ %' ESCAPE '$'

(This query statement indicates the records whose IDs contain _. The Escape Character $ has escaped)

 

 

Bytes ---------------------------------------------------------------------------------------

2. Grouping Data Tables

Sorting and grouping of data in a table

When we use SQL statements for query operations, the statements that we may query may be irregular, so we can sort the statements to better observe the results we have found.

GROUP

The order by clause can be used to sort the columns specified in the query results in ascending or descending ORDER, depending on the following keywords.

Select * from T_student order by age desc

(Sort the results in descending order)

Note: The order by clause only sorts the final result and cannot be used in the select statement of the select subquery. The position of the order by clause must be placed at the end of all clauses, that is, if the query statement contains multiple clauses, the order by clause must be placed after these clauses.

Common Aggregate functions

The so-called aggregate function is a statistical function, mainly used to sort and evaluate the obtained data.

Function

Function

COUNT (column expression)

Calculates the number of non-null rows in a given column or expression.

COUNT (*)

Calculates the total number of rows in a data table, including null values.

MAX (column expression)

Calculates the maximum value in a given column or expression.

MIN (column expression)

Calculates the minimum value in a given column or expression.

SUM (column expression)

Calculates the sum of all given columns or expressions.

AVG (column expression)

Calculates the average value of all values in a given column or expression.

 

 

GROUP data in a table using the group by clause

The GROUP by clause can be grouped based on the results of multiple data queries in the data column.

Select age, count (stuname) from t_student whereage <50 group by age order by age

(Group by age and count the number of students of each age group)

Use having to restrict query results after grouping

If you want to restrict query conditions for grouping results, you can use the having Statement, which is placed behind the group by statement.

Select age, count (stuname) from t_student whereage <50 group by age having age> 15 order by age

(The same as above, but the age limit is higher than 15)

Limit the number of rows in the result set

In some cases, we do not want to see all the results, but only display a few rows. Especially in paging operations, it is okay to only care about the first few records, at this time, we need to limit the result set.

Syntax: select top n {precent} column name 1, column name 2 from Table Name

Top: keyword, indicating the keyword that limits the number of rows in the result set

N: Limit the number of rows in the result set.

Precent: returns the first N % of the number of rows in the query result set.

Selecttop 1agefromt_studentwhere age <50

(Only one row is returned)

 

 

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.