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