s1/c# language and database technology base/09-Data Query Foundation

Source: Internet
Author: User
Tags abs getdate mathematical functions rtrim square root management studio sql server management sql server management studio

The mechanism of the query:

The query is for data rows that already exist in the table and can be simply interpreted as "filtering" to extract the eligible data.

When the data table accepts the query request, it can be simply understood as "he will judge by line", to determine whether the query criteria are met, if the query criteria are extracted, and then all the selected rows are organized together to form another table-like structure, forming the results of the query, Often called a result set (Recordset).

Because the result set results in a similar structure to the table, it is made up of rows, so it is still possible to query the recordset again.

Querying using the SELECT statement

The query uses the SELECT statement, and the simplest form of a query statement can be expressed as follows.

Grammar:

SELECT < column name >

From < table name >

[WHERE < query criteria expression;]

[ORDER BY < sorted column name >[ASC or DESC]]

Where conditions are optional and, if not limited, the query returns data items for all rows. Order BY is used for sorting, and subsequent content will be described in detail.

Experience: Query statements can be divided into multiple clause sections, for example, the query syntax above can be divided into select ... From ... WHERE ... ORDER BY four clauses, for complex SQL statements, you can write each clause in a separate line to facilitate debugging and finding errors.

Query statements are typically debugged in the query window of SQL Server Management Studio, and the following examples illustrate different scenarios for basic queries.

1. Querying all data rows and columns

It is easier to enumerate all the rows and columns in a table, and you can use "*" to denote all columns:

SELECT * from Students

2. Query some rows or columns

Querying some columns requires listing different column names, while querying some of the rows requires conditional restrictions using a WHERE clause, such as:

SELECT scode,sname,saddress

From Students

WHERE saddress = ' Henan Xinxiang '

The above query statement, will only query the address of "Xinxiang, Henan" students, and only display the number, name and address column. Similarly, the following statement is used to query the address is not "Henan Xinxiang" student information.

SELECT scode,sname,saddress

From Students

WHERE saddress <> ' Henan Xinxiang '

3. Use the alias of the column in the query

The AS clause can be used to change the name of a column in a result set, or to specify a name for a combination or computed column, and one case is to make the information in the header column more understandable, for example, by querying the SCODE column name as "student number."

Renaming a column in T-SQL can use the AS clause, for example:

SELECT SCode as student number, SName as student name, saddress as student address

From Students

WHERE saddress <> ' Henan Xinxiang '

Another scenario is to use calculations, merging to get the naming of new columns. For example, FirstName columns and LastName columns exist in the employee table employees of a database, and now learn to combine these two columns into a column called "Name", which you can use with the following query statement.

SELECT firstname+ '. ' +lastname as Name

From Employees

Renaming a column name is another way to name it by "=", for example:

SELECT name = Firstname+ '. ' +lastname

From Employees

4. Query null value

Use "is NULL" or "is not NULL" in the SQL statement to determine whether it is empty, so if you want to query Student information table, students who do not fill in the e-mail information can be used to query the statement.

SELECT SName from Students WHERE Semail is NULL

5. Use constant columns in queries

Sometimes, you need to add the default information for some constants to the query output for statistical or computational convenience. For example, when querying student information, the school's name Unification is "Beijing Xinxing Bridge", the query statement is as follows.

SELECT name =sname, address =saddress, ' Beijing Xinxing Bridge ' as school name

From Students

The query input has a list of "school names", all of which are "Beijing Xinxing Bridge".

6. Query returns the limit of the number of rows

Some queries need to return the limit of the number of rows. For example, in the test, if there are tens of thousands of records in the database, and as long as the first 10 rows of data to check whether the number is valid, there is no need to query the output of all the data to improve the query speed, it is necessary to limit the number of rows returned query.

In T-SQL, limit the number of rows to be constrained using the top keyword. For example, to query the name and address information for the first five girls who returned many student records, the query is as follows.

SELECT TOP 5 sname,saddress

From Students WHERE ssex=0

Another situation is that you need to extract records from a table in a certain percentage, and you need to use the Precent keyword to limit it. For example, to extract 20% of the girls ' data is as follows.

SELECT TOP precent sname,saddress

From Students WHERE SSex = 0

How to use ORDER by

If you need to arrange the rows selected by the query statement in a certain order, you need to use the ORDER BY clause, and the sort can be ascending (ASC) or Descending (DESC). If you do not specify ASC or DESC, the result set is sorted in ascending order by default ASC.

The SQL statements described above can be sorted after them with an order by.

For example, when checking students ' grades, if all the results are reduced by 10% plus 5 points, in the query pass scores and according to the level of performance to arrange, SQL statement as follows.

SELECT StudentID as student number, (SCORE*0.9+5) as comprehensive results

From score

WHERE (score*0.9+5) >60

ORDER by Score

You can also sort by more than one column. For example, the following are the statements to sort by the course ID, based on the order of the students ' grades.

SELECT StudentID as student number, CourseID as course Id,score as score

From score

WHERE score>60

ORDER by Score,courseid

Using Functions in Queries

SQL Server provides intrinsic functions, each of which implements different functions, and different categories of functions can be used in conjunction with SELECT, UPDATE, and INSERT statements.

There are four kinds of functions commonly used, namely, string function, date function, mathematical function, system function.

/String function/

Some of the commonly used string functions

Name of function

Describe

Example

CHARINDEX

Used to find the starting position of a specified string in another string

SELECT CHARINDEX (' name ', ' My NAME is Tom ', 1)

Returns: 4

Len

Returns the length of the string passed to it

SELECT LEN (' SQL Server course ')

Returns: 12

UPPER

Convert the string passed to her to uppercase

SELECT UPPER (' SQL Server Course ')

Back to: SQL SERVER Course

LTRIM

Clear the space to the left of the character

SELECT LTRIM (' Zhou Zhiyu ')

return: Zhou Zhiyu

RTRIM

Clear the space to the right of the character

SELECT RTRIM (' Zhou Zhiyu ')

return: Zhou Zhiyu

Right

Returns the specified number of characters from the right side of the string

SELECT right (' Buy and sell Tourson ', 3)

return: Tourson

REPLACE

Replace a character in a string

SELECT REPLACE (' Mo le can be cut ', ' can ', ' LAN ')

Back: Mo Lok can be cut. Yang Lan

STUFF

In a string, delete the specified length of character and insert a new string at that location

SELECT STUFF (' ABCDEFG ', 2, 3, ' My Music My World ')

Back to: A My music my world EFG

Date function

Some of the commonly used date functions

Name of function

Describe

Example

GETDATE

Get current system date

SELECT GETDATE ()

Return: Today's date

For example: 2009-12-25 12:00:00.000

DATEADD

Adds the specified value to the date after the specified date part

SELECT DATEADD (mm,4, ' 01/01/2009 ')

Returns: the current date format returned 05/01/2009

DATEDIFF

Interval between two dates for a specified date part

SELECT DATEDIFF (mm, ' 01/01/2009 ', ' 05/01/2009 ')

Returns: 4

Datename

The string form of the date part specified in the date

SELECT datename (DW, ' 01/01/2000 ')

Back: Saturday or Saturday

DATEPART

The integer form of the date part specified in the date

SELECT DATEPART (Day, ' 01/15/2000 ')

Returns: 15

Date part parameters and their abbreviations

Date Part parameter

Abbreviation

Date Part parameter

Abbreviation

Year

Yy,yyyy

Weekday

Dw,w

Quarter

Qq,q

Hour

hh

Month

Mm,m

Minute

Mi,n

DayOfYear

Dy,,y

Second

Ss,s

Day

Dd,d

Millisecond

Ms

Week

Wk,ww

Mathematical functions

Some of the commonly used mathematical functions

Name of function

Describe

Example

RAND

Returns a random float value from 0 to 1

SELECT RAND ()

Returns: 0.79288062146374

Abs

Take the absolute value of a numeric expression

SELECT ABS (-43)

Returns: 43

CEILING

Rounding up to the smallest integer greater than or equal to the specified numeric value, expression

SELECT CEILING (43.5)

Returns: 44

Floor

Rounding down to the maximum integer less than or equal to the specified expression

SELECT Floor (43.5)

Returns: 43

POWER

To take the power of a numeric expression

SELECT POWER (5,2)

Returns: 25

ROUND

Rounding a numeric expression to a specified precision

SELECT ROUND (43.543,1)

Returns: 43.500

Sign

Returns +1 for positive numbers, 1 for negative returns, and 0 for 0

SELECT sign (-43)

Return:-1

SQRT

Takes the square root of a floating-point expression

SELECT SQRT (9)

Returns: 3

System functions

Some of the commonly used system functions

Name of function

Describe

Example

CONVERT

Used to transform data types

SELECT CONVERT (VARCHAR (5), 12345)

Return: String 12345

Current_User

Returns the name of the current user

SELECT Current_User

Return: The user name you logged in

Datalength

Returns the number of bytes used to specify an expression

SELECT datalength (' China a Union ')

Returns: 5

Host_name

Returns the name of the computer to which the current user is logged

SELECT HOST_NAME ()

Return: The name of the computer you are logged on to

System_user

Returns the name of the user currently logged in

SELECT System_user

Return: The name of the user you are currently logged into

User_name

Returns the user name from the given user ID

SELECT user_name (1)

return: Return "dbo" from any database

Comparison: LEN () is used to get the length of a string. Datalength () is used for the number of bytes of memory that the user gets an expression. When the parameters are character-type data, they can be common, for example, "Select LEN (' 6 ')", "Select Datalength (' 6 ')" Return all 1, and "Select Datalength (6)" returns 4, which indicates that the integer data "6" takes up a byte.

s1/c# language and database technology base/09-Data Query Foundation

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.