Aliases
An alias is creating using the term AS followed by a case-sensitive string. It can be used in a query select list to give a column a different name in the returned results.
-
Example:
In the examples of this lesson is used the following table, named "sites":
| id | site | nr | reg_date |
| 1 | marplo.net | 5 | 2011-03-28 07:56:53 |
| 2 | https://coursesweb.net | 8 | 2011-03-28 07:57:40 |
This example selects the records in the "site" column and returns them in an alias named "st".
// connect to the "tests" database
$conn = new mysqli('localhost', 'root', '', 'tests');
// check connection
if (mysqli_connect_errno()) {
exit('Connect failed: '. mysqli_connect_error());
}
// sql query
$sql = "SELECT `site` AS st FROM `sites`";
// perform the query and store the results
$result = $conn->query($sql);
// if the $result contains at least one row
if ($result->num_rows > 0) {
// output data of each row from $result
while($row = $result->fetch_assoc()) {
echo '<br />';
print_r($row);
}
}
else {
echo '0 results';
}
$conn->close();
?>
In the returned results, the values of the "site" column will be returned in another name of field, called 'st'.
Output:
Array ( [st] => marplo.net )
Array ( [st] => https://coursesweb.net )
It can be used more aliases in a SELECT query, one for each column:
SELECT column1 AS name1, column2 AS name2 FROM table_name
Aliases are often used with functions, to return the result of the function with a name (see in the examples below).
-
Standard SQL, doesn't support the use of aliases in queries with WHERE conditionals.
MySQL Functions
Most of the MySQL functions are used with SELECT queries to format and alter the returned data, but you may use MySQL functions other types of queries as well.
To apply a function in a SELECT statement to a column's values, the query would look like:
-
Syntax:
SELECT column1, FUNCTION(column2), column3 FROM table_name
You cannot have spaces between the function name and the opening parenthesis.
Text functions
Text functions (or
String functions) are used to working with strings, these can be applied to either columns or literal values.
The table below lists some of MySQL's functions for working with text (
"t", can be a text or the name of a column).
| Function | Returns |
| CONCAT(t1, t2, ...) | A new string of the form "t1t2". |
| CONCAT_WS(S, t1, t2, ...) | A new string of the form t1St2S... (ignores columns with NULL values) |
| LENGTH(t) | The number of characters in "t". |
| LEFT(t, y) | The leftmost "y" characters from "t". |
| RIGHT(t, x) | The rightmost "x" characters from "t". |
| TRIM(t) | "t" with spaces from the beginning and end removed. |
| UPPER(t) | "t" capitalized. |
| LOWER(t) | "t" in all-lowercase format. |
| SUBSTRING(t, x, y) | "y" characters from "t" beginning with "x" (indexed from 0). |
| expr REGEXP pat | Performs a pattern match of a string expression, "expr" against a pattern, "pat". |
- In the next example we use the CONCAT() function to concatenate the values of two columns ("site" and "nr"), separated by a dash, in one string returned in a title alias named "str":
<?php
// connect to the "tests" database
$conn = new mysqli('localhost', 'root', '', 'tests');
// check connection
if (mysqli_connect_errno()) {
exit('Connect failed: '. mysqli_connect_error());
}
// sql query
$sql = "SELECT CONCAT(`site`, ' - ', `nr`) AS str FROM `sites`";
// perform the query and store the results
$result = $conn->query($sql);
// if the $result contains at least one row
if ($result->num_rows > 0) {
// output data of each row from $result
while($row = $result->fetch_assoc()) {
echo '<br />'. $row['str'];
}
}
else {
echo '0 results';
}
$conn->close();
?>
The alias, "str" is the key in the array with the records.
The code above will output:
marplo.net - 5
https://coursesweb.net - 8
• For the complete list of the MySQL string functions, see the MySQL manual:
String Functions
Numeric functions
The
numeric functions are used for working with numbers, to perform mathematical operations. As with most functions, these can be applied to either columns or literal values.
The table below lists some of MySQL's numeric functions (
"n", can be a number or the name of a column).
| Function | Returns |
| ABS(n) | The absolute value of "n". |
| CEILING(n) | The next-highest integer based upon the value of "n". |
| FLOOR(n) | The integer value of "n". |
| FORMAT(n1, n2) | "n1" formatted as a number with "n2" decimal places and commas inserted every three spaces. |
| MOD(n1, n2) | The remainder of dividing "n1" by "n2". The same as using the percent sign (n1%n2) |
| POW(n1, n2) | "n1" to the "n2" power. |
| RAND() | A random number between 0 and 1.0. |
| ROUND(n1, n2) | "n1" rounded to "n2" decimal places. |
| SQRT(n) | The square root of "n". |
- In the next example we select the "nr" column and the POW() function applied to it, followed by an alias for the returning POW() value, ordered by that alias:
<?php
// connect to the "tests" database
$conn = new mysqli('localhost', 'root', '', 'tests');
// check connection
if (mysqli_connect_errno()) {
exit('Connect failed: '. mysqli_connect_error());
}
// sql query
$sql = "SELECT `nr`, POW(`nr`, 3) AS pow3 FROM `sites` ORDER BY pow3";
// perform the query and store the results
$result = $conn->query($sql);
// if the $result contains at least one row
if ($result->num_rows > 0) {
// output data of each row from $result
while($row = $result->fetch_assoc()) {
echo '<br /> POW('. $row['nr']. ', 3) - '. $row['pow3'];
}
}
else {
echo '0 results';
}
$conn->close();
?>
Output:
POW(5, 3) - 125
POW(8, 3) - 512
- The RAND() function can be used to return the results in a random order:
SELECT * FROM table_name ORDER BY RAND()
• For the complete list of the MySQL numeric functions, see the MySQL manual:
Numeric Functions and Operators
Date and time functions
The date and time column types in MySQL are particularly flexible.
Whether you want to make calculations based upon a date or return only the name of the week day from a value, MySQL has a function for that purpose.
The table below lists some of MySQL's functions for working with dates and times. These can be applied to either columns or literal values (
"dt", can be a literal value or the name of a column).
| Function | Returns |
| HOUR(dt) | The hour value of "dt". |
| MINUTE(dt) | The minute value of "dt". |
| SECOND(dt) | The second value of "dt". |
| DAYNAME(dt) | The name of the day for "dt". |
| DAYOFMONTH(dt) | The numerical day value of "dt". |
| MONTHNAME(dt) | The name of the month of "dt". |
| MONTH(dt) | The numerical month value of "dt". |
| YEAR(dt) | The year value of "dt". |
| CURDATE() | The current date. |
| CURTIME() | The current time. |
| NOW() | The current date and time. |
| UNIX_TIMESTAMP(dt) | The Unix timestamp (number of seconds since the "January 1 1970 00:00:00 GMT" until the current moment or until the date specified). |
- The next example displays the day of the week that the last site was registered:
<?php
// connect to the "tests" database
$conn = new mysqli('localhost', 'root', '', 'tests');
// check connection
if (mysqli_connect_errno()) {
exit('Connect failed: '. mysqli_connect_error());
}
// sql query
$sql = "SELECT `id`, `site`, DAYNAME(`reg_date`) AS weekday FROM `sites` ORDER BY `reg_date` DESC LIMIT 1";
// perform the query and store the results
$result = $conn->query($sql);
// if the $result contains at least one row
if ($result->num_rows > 0) {
// output data of each row from $result
while($row = $result->fetch_assoc()) {
echo 'The last web site: '. $row['site']. ' , id: '. $row['id']. ' - was registered in a '. $row['weekday'];
}
}
else {
echo '0 results';
}
$conn->close();
?>
Output:
The last web site: https://coursesweb.net , id: 2 - was registered in a Monday
If you want to get the UNIX timestamp of a date and time column, use the fallowing sintax:
SELECT UNIX_TIMESTAMP(column_date) AS alias_name FROM table_name
• There are many other date and time functions, see the complete list in the MySQL manual:
Date and Time Functions