The share mainly involves: single table query, multi-table query, at the same time to share some part of common conversion statement, this several ways to introduce the common SQL statements,
1, about the single table query:
Example: student1
1) select * from [what do you want to query the table name]
Example: select * from student1
Results:
2) select XXXX, XXXXXX the from you to query the table name
Example: select no, name, erp, calss from student1
Results:
In addition, also need to remember some of the limit of words, specific as follows:
Restrictive conditions: 1) the where
Example: query 1 class personnel information, query statement: select * from student1 where class 1, class='
'Results:
If later on this foundation have multiple conditions plus and or or to satisfy multiple constraints query
Example: query 2 is the boy in the class of personnel information, query statement: select * from student1 where class="class 2" and sex='male'
So that the results of a query is as follows:
2) to sort: XXX ORDER BY
Example: according to the class what sort, query statement: select * from student1 ORDER BY class
Results:
Behind this ORDER BY, of course, also can write multiple columns, use, can be separated, so that it will be sorted BY multiple columns in turn display
If need to shown in ascending and descending, can also be followed by DESC or ASC,
For example: select * from student1 ORDER BY class desc, age asc
3) to heavy query: DISTINCT
Example: the students come from where? Query: select DISTINCT native place from student1
Results:
4) select range of data between two values: the where XXX between... And
Example: at the age of 20-23 students have? Query: select * from student1 where the age between 20 and 23
Results:
Note: different databases in between... The and operator handling is differ,
Some database list between "20" and "23", but does not include "20" and "23";
Some database list between "20" and "23" and including "20" and "23".
While some database list between "20" and "23", including "20", but not including "23"
5) for 4 extension, and not between XXX, between the front again add a not
For example: no 20-23 students have? Query: select * from student1 where age not between 20 and 23
Results:
6) to query a column value is more, you can use (' XX ', 'XX1) in this way,
For example: Beijing and Shanghai students information, query statement: select * from student1 where native place in Beijing ', 'Shanghai' (')
Results:
7) everything like use, such as I want to check all the student surnamed zhang, or erp/contained in the letter that a few letters, or in a column to XXX to end, can be achieved by the like, can also be combined with the not like, but usually and joint use wildcard characters "%", such as: '%', '% %' XXX,
Example:
Query all the student surnamed zhang, query statement: select * from student1 where the name like 'a %'
Results:
2, and other commonly used functions as follows:
Article 1) query how much data: count
Example: query students a total of how many records in the table, the query statement: select count (*) from student1
Results: 6
Results: after 2) query summary sum
Example: query students after age and how much is in the table, the query: select sum (age) from student1
Results: 131
3) query the mean is:
Example: query students after age and how much is in the table, the query: select avg (age) from student1
Results: 21.83
4) a maximum query: Max
Example: query students after age and how much is in the table, the query: select Max (age) from student1
Results: 25
5) check out the minimum: min
Example: query students after age and how much is in the table, the query statement: select min (age) from student1
Results: 20
6) function is used to put the rounding off numerical fields for the specified decimal digits round, commonly used method: select round (query field name, a few decimal places) from the table name
7) used to display formatted on the field, often used for time format used: date_format, commonly used method: select date_format (field name, format) from the table name
Commonly used transformation format is as follows:
Query: one day date_format (fields, "% Y - % m - % d")
Month: date_format (fields, "% Y - % m")
Sometime: date_format (fields, "% Y - m - H" % d % %)
8) conversion date to_date, usually used for time format used: to_date, commonly used method: to_date (field name)
9) filtering function Spaces: TRIM, commonly used method: select the TRIM (field name) from the table name
10) turn the capital letters: ucase, commonly used method: select ucase (query field name) from the table name
Turn to lowercase letters: lcase, commonly used method: select lcase (query field name) from the table name
More than 3, table joint query:
Next add