Inquire
Fetching data rows from 0 or more tables
...............] ][ LIMIT count ]
from_item
As one of the following
......] ) ]
GROUP BY clause
The GROUP BY clause groups the output of the SELECT statement, and the data row that matches the value in the group. The Group by clause supports arbitrary expressions, including specifying column names or column ordinals (starting at 1).
HAVING clause
The HAVING clause is used in conjunction with the aggregate function and the GROUP BY clause to control the selection grouping. The HAVING clause removes the grouping that does not satisfy the condition. After the grouping and aggregation calculations are complete, you have to filter the groupings.
The following example queries the Customer table and groups the records that account balances are larger than the specified values:
SELECT count(*), mktsegment, nationkey, CAST(sum(acctbal) AS bigint) AS totalbalFROM customerGROUP BY mktsegment, nationkeyHAVING sum(acctbal) > 5700000ORDER BY totalbal DESC;
Union clause
The UNION clause is used to combine the results of multiple query statements into one result set:
queryUNION [ALLDISTINCTquery
The parameter all or DISTINCT controls which rows The final result set contains. If the parameter all is specified, all rows are included, even if the rows are identical. If the parameter DISTINCT is specified, the result set is merged and the result set has only unique rows that are not duplicates. If you do not specify a parameter, DISTINCT is used by default when executing.
Multiple unions are executed from left to right unless the order is explicitly specified in parentheses.
Insert
Inserts a row into the table.
INSERT INTO table_name query
The specified column name is not currently supported. Therefore, the columns in the query statement must exactly match the columns in the table that you want to insert. For example:
INSERT INTO orders SELECT * FROM new_orders;INSERT INTO cities VALUES (1, ‘San Francisco‘);NSERT INTO cities VALUES (2, ‘San Jose‘), (3, ‘Oakland‘);
Table Operations
function |
Statement |
Description |
Modify Table |
ALTER TABLE name RENAME to New_name |
Change the definition of an existing table |
Build table |
CREATE TABLE table_name as Query |
Create a new table that contains query query results |
Build View |
CREATE [OR REPLACE] VIEW view_name as Query |
Creates a new view of a query query. A view is a logical table that can be used in future queries. The view does not contain any data. Query statements stored in the view are executed whenever the view is used by another query statement |
View table Structure |
DESCRIBE table_name |
View a table structure that already exists |
Delete Table |
DROP TABLE table_name |
Delete a table that already exists |
Delete View |
DROP VIEW view_name |
Delete A view that already exists |
Show
function |
Statement |
Description |
Show Columns |
SHOW COLUMNS from table |
List columns and their data types and other properties in a table |
Display functions |
SHOW FUNCTIONS |
List all functions that are available for querying |
Show partitions |
SHOW partitions from table [WHERE ...] [ORDER by ...] [LIMIT ...] |
Lists the partitions in the table, which can be filtered using the WHERE clause, ordered by the ORDER BY clause, and restricted using the limit clause. These clauses work the same way they do in queries |
Show Library |
SHOW SCHEMAS [from catalog] |
List the libraries in the catalog or current catalog |
Show session |
SHOW SESSION |
List current session properties |
Show Table |
SHOW TABLES [from schema] [like pattern] |
Lists the tables in the specified library or in the current library. You can control the table names listed by using the LIKE clause |
Explain
Displays a logical or distributed execution scenario for a statement.
...] ) ] statementoption可以为以下之一: FORMAT { TEXT | GRAPHVIZ } TYPE { LOGICAL | DISTRIBUTED }
Presto commonly used sentence collation