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

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