Skip to main content

Posts

Showing posts with the label mysql view index

SQL Expressions

Expressions are a common element of SQL statements,  and they occur in many contexts.  For example,  expressions often occur in the  WHERE clause of  SELECT,  DELETE, or  UPDATE statements to identify  which records to retrieve, delete, or update.  But  expressions may be used in many other places; for  example, in the output column list of a SELECT statement, or in ORDER BY or GROUP BY clauses.   Terms of expressions consist of constants (literal numbers, strings, dates, and times),  NULL values,  references to table columns, and function calls.  Terms may be combined using operators into more  complex expressions.  Many types of operators are available, such as those for arithmetic, comparison, logical, and pattern-matching operations.  Here are some examples of expressions:   •  The following statement refers to table columns to select country names and populations from the  Country table:   SELECT Name, Population FROM Country; •  Literal data values that aren't st

Aggregating Query Results

A SELECT statement can produce a list of rows that match a given set of conditions.  This list provides the  details about the selected rows. However, to know about the overall characteristics of the rows, a summary  can be more valuable. Aggregate functions (also known as Summary functions) perform summary  operations on a set of values, such as counting, averaging, or finding minimum or maximum values.  They  calculate a single value based on a group of values. However, the resulting value is based only on non  NULL values from the selected rows (with the exception that COUNT(*) counts all rows).   Functions such as AVG() that calculate summary values for groups are known as “aggregate” functions  because they are based on aggregates or groups of values.  There are several aggregate functions.  Some of  the most common are as follows;  Aggregate Functions               Definition         MIN()                                  Find the smallest value         MAX()          

SELECT with MySQL Query Browser

The primary purpose of the MySQL Query Browser is to provide a graphical user interface for performing  table queries, such as those covered in this section. All the above SELECT queries can also be executed from  the Query Browser GUI window. 

SELECT with LIMIT

When a query returns many rows, but only a few of them needs to be seen, add a LIMIT clause.  This is a  MySQL option that allows the output of a query to be  limited to the first rows of the result that would  otherwise be returned.  The LIMIT clause may be given with either one or two arguments: ƒ LIMIT row_count ƒ LIMIT skip_count, row_count limit,h Each argument must be represented as an integer constant.  Expressions, user variables, and so forth can  not be used. First Rows from the Beginning  When followed by a single integer, row_count, LIMIT returns the first row_count rows from the  beginning of the result set. To select just the first 8 rows of a result set, use LIMIT 8; mysql> SELECT ID, Name FROM City LIMIT 8; +----+-------------------+ | ID | Name               | +----+-------------------+ |  1 | Kabul                | |  2 | Qandahar         | |  3 | Herat                 | |  4 | Mazar-e-Sharif | |  5 | Amsterdam       |

SELECT with ORDER BY

          By default, the rows in the result set produced by a SELECT statement are returned by the server to the  client in no particular order.  When a query is issued, the server is free to return the rows in any convenient  order.  This order can be affected by factors such as the order in which the rows are actually stored in the  table, or which indexes are used to process the query.  If the output rows need to be returned in a specific  order, include an ORDER BY clause that indicates how to sort the results.  There is no guarantee about the order in which the server returns rows, unless the order is specified.  To do  so, add an ORDER BY clause to the statement that defines the sort order desired.  Single Column Sorts  The following example returns country names (in the Country table of the world database) alphabetically  by Country Name;  mysql> SELECT Name FROM Country ORDER BY Name;  +------------------------------+  | Name                            |  +--------

SHOW INDEX

To view the status of indexes in a table use the following statement;  SHOW INDEX FROM <table>;  Example:  mysql> SHOW INDEX FROM countrylanguage\G  *************************** 1. row ***************************         Table: countrylanguage  Non_unique: 0     Key_name: PRIMARY  Seq_in_index: 1  Column_name: CountryCode     Collation: A  Cardinality: NULL     Sub_part: NULL       Packed: NULL         Null:  Index_type: BTREE       Comment:  *************************** 2. row ***************************         Table: countrylanguage  Non_unique: 0     Key_name: PRIMARY  Seq_in_index: 2  Column_name: Language     Collation: A  Cardinality: 984     Sub_part: NULL       Packed: NULL         Null:  Index_type: BTREE       Comment:  2 rows in set (0.16 sec)  The result of the  SHOW INDEX command done on the  CountryLanguage table lists two indexes;  CountryCode and Language.  This confirms the setting of a composite index in the CREATE TABLE  c