The Select of SQL

SELECT * ( column name ) from table_name ( table name ) where column_name operator value.

SELECT * from stock_information where stockid = str (NID)

wildcard characters in sql:

wildcard characters	Description
%	Matches 0 or more arbitrary characters
_	Replaces only one character
[Charlist]	Any single character of the word columns
[^charlist] or [!charlist]	Any single character that is not in the word columns

Wildcard characters can only be used with the like keyword

Example:

The original table (used in the example):

Persons table:

City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

From the "Persons" table above, select the person who lives in a city that starts with "Ne":

Result set: City

Id	LastName	FirstName	Address
2	Bush	George	Fifth Avenue	New York

Example 2 we would like to select from the "Persons" table the people who live in cities that contain "Lond":

SELECT * from Persons WHERE city like '%lond% '

Result set: City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London

Using _ Wildcard characters

Example 1: We want to select the "Eorge" person after the first character of the name from the "Persons" table above:

SELECT * from Persons WHERE FirstName like ' _eorge '

Result set: City

Id	LastName	FirstName	Address
2	Bush	George	Fifth Avenue	New York

Example 2, we want the last name of the record selected from the "Persons" table to start with "C", then an arbitrary character, then "R", then any character, then "ER":

SELECT * from Persons WHERE LastName like ' c_r_er '

Result set: City

Id	LastName	FirstName	Address
3	Carter	Thomas	Changan Street	Beijing

Using [charlist] wildcard characters

We would like to select from the "Persons" table above the person who lives in the city that begins with "A" or "L" or "N":

SELECT * from Persons WHERE city like '[aln]%'

Result set: City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York

Example 2 we would like to choose from the "Persons" table above for people who live in cities that do not start with "A" or "L" or "N":

SELECT * from Persons WHERE city like ' [! aln]% ' or [^ALN]

Result set: City

Id	LastName	FirstName	Address
3	Carter	Thomas	Changan Street	Beijing

ORDER BY stockid DESC (ASC)--------- sort, Desc- Descending, asc- ascending
　　 ORDER by---------by StockName = (select StockName from stock_information where Stockid = 4)   subquery: unless you can ensure that the inner layer select Returns the value of only one row, where in　　 qualifier
select DISTINCT COLUMN_NAME form table_name---------distinct Specifies to retrieve unique column values, without repeating the query

select stockname, COUNT (*)  as "number"  from table_name GROUP by StockName     -- -------group By , The specified column has the same value

,group by + [Group Field] (can have multiple )。 After performing this operation, the dataset divides a dataset into different groups based on the values of the grouped fields.

Having count (*) = 2---------have Select the specified group
span> < Span class= "Fontstyle2" >

example:    such as the following data set, where the fruit name (Fruitname) and the production country (Productplace) is the Federated primary key:

Fruitname	Productplace	Price
Apple	China	5
Apple	Japan	8
Apple	USA	4
Orange	China	6
Banana	China	12
Orange	USA	8

If we want to know how many kinds of fruit each country has, then we can do it by using the following SQL statement:

Select COUNT (*) as "fruit type", productplace as "production country" from table GROUP by Productplace
This SQL statement uses the group by + grouping field, then this SQL statement can be interpreted as "I group datasets according to the country of production (Productplace), then count the respective records according to each group." ”

It is noteworthy that there are two return fields in the result set, one is productplace (producing country) and the other is the fruit type.

If our fruit species here is not count (*), but is similar to the following wording:

SELECT fruitname,productplace from table GROUP by Productplace

then SQL will report a similar error when executing this statement: Select the column ' T_test_fruitinfo in the list. Fruitname ' is not valid because the column is not contained in an aggregate function or GROUPBY clause.

This is one of the things we need to be aware of if, in the return set field, these fields are either to be included behind the group by statement, or to be included in the aggregate function. Imagine a group by operation as follows: first the system obtains a result set based on the SELECT statement, such as a detailed table of the first fruit, the country of production, and the unit price. The records with the same grouping fields are then merged into one record, based on the grouping fields. At this time, the remainder of the field that does not exist in the group by statement is likely to have multiple values, but there is only one record for a grouping, and a data grid cannot fit into multiple values, so there is a need to convert these multivalued columns into single values through some processing. Then put it in the corresponding data grid, then the completion of this step is the aggregation function. This is why these functions are called aggregate functions (aggregate functions).

The following table is a common aggregation function:

function	function	support of
SUM (column name)	Sum
Max (column name)	Maximum Value
Min (column name)	Minimum value
AVG (column name)	Average
First (column name)	First record	Only access supports
Last (column name)	Last record	Only access supports
Count (column name)	Number of statistics records	Note the difference between the count (*) and

Group by all [expressions]:

Group BY all + Group field, this and the previously mentioned group by [Expressions] form a keyword all. This keyword is only visible if the where statement is used, and if the Where condition filters out some groups.

If you use the ALL keyword, the query results will include all groups produced by the GROUP BY clause, even if some groups do not have rows that match the search criteria. Without the all keyword, a SELECT statement that contains a GROUPBY clause will not display a group that does not have a qualifying row.

First, we do not use the GROUP BY statement with the ALL keyword:

SELECT COUNT (*) As fruit species, productplace as production country from T WHERE (productplace<> ' Japan ') GROUP by Productplace

In the final result, Japan will not appear in the grouping result because Japan does not conform to the where statement.

Now we add the ALL keyword:

SELECT COUNT (*) As fruit species, productplace as production country from T WHERE (productplace<> ' Japan ') GROUP by all Productplace

After re-running, we can see the group of Japan, but the corresponding "fruit species" does not really count, the aggregate function will be based on the return value of the type with the default value of 0 or null instead of the return value of the aggregate function.

GROUP by [Expressions] with CUBE | ROLLUP:

This temporarily did not see, but want to see the words can look at these two, two people have done summed up: http://www.cnblogs.com/glaivelee/archive/2010/11/19/1881381.html/http blog.csdn.net/dreamwbt/article/details/53437118

Group by and have, where, order by statements are executed:

When both the WHERE clause, the GROUP BY clause, the HAVING clause, and the aggregation function are included, the order of execution is as follows:
--Executes the WHERE clause to find the data that meets the criteria;
--Use the GROUP BY clause to group the data, and the GROUP BY clause to run the aggregate function to calculate the value of each group, and finally remove the non-conforming group with the HAVING clause.
Each element in a--having clause must also appear in the select list. Some database exceptions, such as Oracle.
Both the--having clause and the WHERE clause can be used to set constraints so that the query results meet certain conditional limits.
The--having clause restricts the group, not the row. You cannot use the aggregate function in the WHERE clause, but you can in the HAVING clause.

--then sort the view by the order BY statement so that the final result is generated. The column name of the final view can be used only in the ORDER BY statement

SELECT fruitname, Productplace, price, id as IDE, Discount from table WHERE (productplace= "China") ORDER by ID E

You can use the IDE only in an order BY statement, and you cannot use the IDE if you need to reference column names in other conditional statements.

8. The difference between having and where

• The purpose of the WHERE clause is to remove rows that do not conform to the where condition before grouping the results of the query, that is, filtering the data before grouping, where the cluster function cannot be included , and the Where condition is used to filter out specific rows.
• The HAVING clause is used to filter groups that satisfy a condition, that is, to filter the data after grouping, often with clustering functions, to filter out specific groups using the having condition, or to group by using multiple grouping criteria.

Example 8

Select category, sum (quantity) as number of and from agroup by category having sum (qty) > 18

Example 9:having and where's combined use method

Select category, sum (quantity) from Awhere quantity Gt;8group by category having SUM (quantity) GT; 10

The Select of SQL