SQL data Query Base note

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