Querying using the Selete statement
Grammar
select< Column name > from< table Name > [ORDER by < sorted column name >[ASC or DESC]]
1. Querying all data and columns
SELECT * from< Table name >
2. Querying some columns or rows
The columns in the query section need to add a where statement to make conditional restrictions
select< Column name > from< table name > WHERE [Condition]
3. Use the name of the column in the query
Can add as makes it easier for people to know what he is
select< column name > as alias from< table name > WHERE [Condition]
4. Querying for null values
In SQL statements you can use "is MULL" or "is not MULL" to determine if there is a null value
select< column name > as alias from< table name > WHERE [condition is NULL]
5. Using constant columns in queries
Sometimes, you need to add the default information for some constants to the query output for statistical and computational convenience.
6. Query returns the limit number of rows
Top limit Top 5 percent percent limit top five
SELECT TOP 5< Column name > from< table name > WHERE [Condition]
7. Query Ordering
If you want to sort them out, you need a statement. He called order by sort there are two kinds of ascending (ASC) or Descending (DESC) general default ascending sort
Grammar:
SELECT TOP 5< Column name > from< table name > WHERE [Condition] [ORDER BY < sorted column name >[ASC or DESC]]
Two. Fuzzy query
1. Wildcard characters
Wildcard characters |
Explain |
Grammar |
Instance |
_ |
A character |
A lile ' c_ ' |
CS CD CF |
% |
string of any length |
B like ' c% ' |
CSD CCA Cwww |
[] |
A character within the range indicated in parentheses |
C Clke ' 9[1-2] ' |
91 92 |
[^] |
Any character that is not in the range specified in parentheses |
D Clke ' 9[^1-2] ' |
93 95 966 |
2 using like for fuzzy search queries
SELECT * from Students WHERE snane like ' qi [wildcard] '
3 querying within a range using between
SELECT * from Students WHERE snane between and 80
The initial value is less than or equal to termination, or it will not be displayed without an error--' not ' reversed
3. Use in to query within enumeration values
The value of the query is one of the established values that can be queried using the IN keyword of the enumeration value
SELECT * from Students WHERE snane in (' 1 ', ' 2 ')
T-SQL Aggregation functions
SUM (sum "column") function
AVG (average "column") function
Max (maximum column) function
MIN (Minimum "column") function
Time Unit
Day: Days
YY: Take the Year
MM: Take month
DD: Take the day of the month
DY: Take the day of the year
WK: Take the week of the year
DW: Take the day of the week
QQ: Take the quarter of the year
HH: Take an hour
MI: Take minutes
SS: Take Seconds
Date format
Date Yyyy-mm-dd
DateTime YY-MM-DD HH:MM:SS
Time Hh:mm:ss
Timestamp 11111 to present YYYYMMDDHHMMSS
Year YYYY 1901
String functions
(Used to control the string returned to the user)
Char index (need to find, original string, starting position) to find the string position
Len () length
Upper () Convert uppercase
Lower () Convert lowercase
Lirim () Clear left space
RTrim () Clear the right space
Right (value, specified number) to find the string
Left (value, specified number) to find the string
Replace (original, replacement) character
Stuff (original, start, number, replacement) specify length-length substitution
Date function
(For Operation date)
Datdate () Current date (today)
DATEADD (type, value, date) value of the type value + Date
DateDiff (type, start date 1, End date 2) 1-2 time interval
Datename (type, date) specifies the date to return a string form
DatePart (type, date) specifies that the date returns an integer form
Mathematical functions
(used to perform algebraic operations on numeric values)
Rand () 0-1 random number
RAND () * (max-min + 1) + min-op
ABS () absolute value
Ceiling () Nearest integer (greater than or equal to)
Floor () small or equal to
Power (number, square) power
Round (number, specified accuracy) accuracy
sign () positive 1, negative reverse-1
sqrt () square root
System functions
(Get system information about objects and settings in SQL Server)
Convert (type, value) Transform data type
Cast (value as type)
Current_User returns the current user name
Datalenght () returns the number of bytes
HOST_NAME () returns the computer name of the login
System_user returns the user name of the login
USER_NAME () Specify User ID return user name
SQL data Query Base note