Interrogating database with the query in MySQL
The SELECT statement is probably the most widely used in SQL, because it is one that allows us to perform the essential work that needs to do a database, retrieve data that is stored. Each SELECT produces a result that is, from a logical point of view, from a table, just like the ones from which to read it. As a result, it is composed, as each table, with rows and columns. The columns are those indicated in the SELECT, and rows are selected from the original tables according to the conditions specified in the WHERE clause. We see two examples represent opposite cases:
Code:
SELECT * FROM table_name
SELECT COUNT (*) FROM table_name
These two examples are apparently very similar, but in fact produce the opposite effect on the result table (or resultset). In the first case we will get all rows and all columns of the table (basically a copy), because the use of the asterisk in the SELECT means 'I want all columns', while the absence of the WHERE clause ensures that all lines are returned. In the second case, the resultset is formed by one row and one column: the column is in fact constituted by the expression COUNT (*) (which means 'I want to know how many rows'), while the fact of having used a function column (the same COUNT) means that all the rows have "concentrated" into one. The functions of column perform its task: to draw n lines (in this case all rows of the table, since the new WHERE clause is missing) and summarize in a single value. Another example to clarify:
Code:
SELECT COUNT (*), MAX (Column1) FROM tablename WHERE column2 = value
Re: Interrogating database with the query in MySQL
In this case we will always have a line, because even here we used functions of the column. The columns, however, will be two, because we asked two values: the number of rows and the maximum value of Column1. The presence of the WHERE clause causes are included in the count only the rows where the value in column2 is the same as specified. Let's look at the syntax of the SELECT (some terms are omitted for simplicity):
Code:
SELECT
[ALL | DISTINCT | DISTINCTROW]
expression, ...
[INTO OUTFILE 'file_name' options
| INTO DUMPFILE 'file_name']
[FROM tables
[WHERE conditions]
[GROUP BY {column_name | expression | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING conditions]
[ORDER BY {column_name | expression | position}
[ASC | DESC], ...]
[LIMIT [offset,] row_number]
As you can see, the basic structure of a SELECT statement is as follows:
- SELECT followed by one or more expressions that will be the columns of the table result;
- FROM followed by the names of one or more tables from which data must be extracted
- WHERE clause that specifies the conditions under which each line will be drawn from the tables or not
- GROUP BY specifies columns whose values should be grouped on the rows in the result: all rows with equal values will be reduced to a
- HAVING clause that specifies additional requirements to be applied to rows after the grouping made by the GROUP BY
- ORDER BY clause that specifies the order in which rows appear in the resultset
- LIMIT sets the maximum number of rows to extract
You can omit any part of the FROM and then to perform very simple queries do not refer to any table, for example:
that extracts the current date and time.
Re: Interrogating database with the query in MySQL
The terms that will form the columns in output may refer to columns of tables referenced, but can also be obtained with functions applied to columns, or mathematical expressions, or functions that return values are independent (as in the case of NOW ()). We have already seen that if we use the so-called functions column we will have a unique value that is n rows. Therefore we can not have a regular column name (scalar) function next to a column:
Code:
SELECT Column1, max (column2) FROM table_name
This query produces an error, because 'Column1' returns a value for each row of the table, while 'max (column2)' returns a single value. Therefore it is not possible to determine how many rows must be the result table. The only way to have a SELECT where functions are next to the column scaling is to apply a GROUP BY on them: in this way, the column functions are not applied to all values of the table result, but individual groups values that belong to each group of rows. An example for better concept:
Code:
SELECT category, max (salary) FROM employees GROUP BY category
Since this query will get one row for each different value of 'category', and in each row the value of 'max (salary)' will be reported to the rows that have that particular value of 'category'. So we know what is the maximum salary of employees, executives and managers. Basically we can say that the functions column are applied to groups of rows, and only if we do not expect groupings are applied to the entire table result. The DISTINCT clause allows you to exclude from the result rows that are identical to other lines. If there are two or more lines of the same result, with DISTINCT (or DISTINCTROW, that means) we will see one. ALL is the opposite of DISTINCT, that pulls everything and the value applied by default.
Re: Interrogating database with the query in MySQL
INTO OUTFILE is used to write the table result to an output file that will be created on the server (you can not use a file name already exists). The options are the same FIELDS and LINES already seen for LOAD DATA INFILE, which is complementary SELECT INTO OUTFILE. To use this clause requires the FILE privilege. If instead of using INTO DUMPFILE INTO OUTFILE, the output file will contain a single line, without delimiters or rows and columns without escape characters. The clause indicates the table or tables from which data will be extracted. The simplest queries extract data from one table, but it is very often need to combine multiple tables. In this case you make a JOIN.
> As each table belongs to a database, the full form is
name_database.name_table.
If you do not specify a database name implies is the use of the current database. In addition to any table we can associate an alias to refer to the rest of the query, via the AS clause. The WHERE clause determines the conditions to be applied to each row of the input table to decide if that line will be part of the result. The GROUP BY, as we have seen, is used to group the results on the values of one or more columns. The result table will be sorted according to these values. If we add the WITH ROLLUP, we will get the extra lines with the total figures for each break value of the fields together. The HAVING clause plays a selection of the lines, just like the WHERE clause. The difference is that the WHERE clause is applied to the rows of the original tables, while the HAVING clause is applied to the rows in the result table after the groupings required by the GROUP BY clause. In practice, it is useful to test the values returned by the column. With ORDER BY values indicate on which order the output. If we add the values DESC are sorted in descending order, while the default is ascending. If we do not use the ORDER BY order of the results will be undefined (unless you use GROUP BY). The LIMIT clause, then, limits the number of rows to be returned. When used with a single parameter, the maximum number of rows returned from the first. When used with two parameters, the first indicates the starting line (the first line is equal to 0), while the second is the maximum number of rows.
