Your cart is currently empty!
Author: alien
-
Khóa học miễn phí MariaDB – Null Values nhận dự án làm có lương
MariaDB – Null Values
When working with NULL values, remember they are unknown values. They are not empty strings or zero, which are valid values. In table creation, column specifications allow for setting them to accept null values, or reject them. Simply utilize a NULL or NOT NULL clause. This has applications in cases of missing record information like an ID number.
User-defined variables have a value of NULL until explicit assignment. Stored routine parameters and local variables allow setting a value of NULL. When a local variable has no default value, it has a value of NULL.
NULL is case-insensitive, and has the following aliases −
- UNKNOWN (a boolean value)
- N
NULL Operators
Standard comparison operators cannot be used with NULL (e.g., =, >, >=, <=, <, or !=) because all comparisons with a NULL value return NULL, not true or false. Comparisons with NULL or possibly containing it must use the “<=>” (NULL-SAFE) operator.
Other available operators are −
-
IS NULL − It tests for a NULL value.
-
IS NOT NULL − It confirms the absence of a NULL value.
-
ISNULL − It returns a value of 1 on discovery of a NULL value, and 0 in its absence.
-
COALESCE − It returns the first non-NULL value of a list, or it returns a NULL value in the absence of one.
Sorting NULL Values
In sorting operations, NULL values have the lowest value, so DESC order results in NULL values at the bottom. MariaDB allows for setting a higher value for NULL values.
There are two ways to do this as shown below −
SELECT column1 FROM product_tbl ORDER BY ISNULL(column1), column1;
The other way −
SELECT column1 FROM product_tbl ORDER BY IF(column1 IS NULL, 0, 1), column1 DESC;
NULL Functions
Functions generally output NULL when any parameters are NULL. However, there are functions specifically designed for managing NULL values. They are −
-
IFNULL() − If the first expression is not NULL it returns it. When it evaluates to NULL, it returns the second expression.
-
NULLIF() − It returns NULL when the compared expressions are equal, if not, it returns the first expression.
Functions like SUM and AVG ignore NULL values.
Inserting NULL Values
On insertion of a NULL value in a column declared NOT NULL, an error occurs. In default SQL mode, a NOT NULL column will instead insert a default value based on data type.
When a field is a TIMESTAMP, AUTO_INCREMENT, or virtual column, MariaDB manages NULL values differently. Insertion in an AUTO_INCREMENT column causes the next number in the sequence to insert in its place. In a TIMESTAMP field, MariaDB assigns the current timestamp instead. In virtual columns, a topic discussed later in this tutorial, the default value is assigned.
UNIQUE indices can hold many NULL values, however, primary keys cannot be NULL.
NULL Values and the Alter Command
When you use the ALTER command to modify a column, in the absence of NULL specifications, MariaDB automatically assigns values.
Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc
Khóa học miễn phí MariaDB – Join nhận dự án làm có lương
MariaDB – Join
In previous discussions and examples, we examined retrieving from a single table, or retrieving multiple values from multiple sources. Most real-world data operations are much more complex, requiring aggregation, comparison, and retrieval from multiple tables.
JOINs allow merging of two or more tables into a single object. They are employed through SELECT, UPDATE, and DELETE statements.
Review the general syntax of a statement employing a JOIN as shown below −
SELECT column FROM table_name1 INNER JOIN table_name2 ON table_name1.column = table_name2.column;
Note the old syntax for JOINS used implicit joins and no keywords. It is possible to use a WHERE clause to achieve a join, but keywords work best for readability, maintenance, and best practices.
JOINs come in many forms such as a left join, right join, or inner join. Various join types offer different types of aggregation based on shared values or characteristics.
Employ a JOIN either at the command prompt or with a PHP script.
The Command Prompt
At the command prompt, simply use a standard statement −
root@host# mysql -u root -p password; Enter password:******* mysql> use PRODUCTS; Database changed mysql> SELECT products.ID_number, products.Nomenclature, inventory.inventory_ct FROM products INNER JOIN inventory ON products.ID_numbeer = inventory.ID_number; +-------------+----------------+-----------------+ | ID_number | Nomenclature | Inventory Count | +-------------+----------------+-----------------+ | 12345 | Orbitron 4000 | 150 | +-------------+----------------+-----------------+ | 12346 | Orbitron 3000 | 200 | +-------------+----------------+-----------------+ | 12347 | Orbitron 1000 | 0 | +-------------+----------------+-----------------+
PHP Script Using JOIN
Use the mysql_query() function to perform a join operation −
<?php
$dbhost = ''localhost:3036
$dbuser = ''root
$dbpass = ''rootpassword
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die(''Could not connect: '' . mysql_error());
}
$sql = ''SELECT a.product_id, a.product_manufacturer, b.product_count
FROM products_tbl a, pcount_tbl b
WHERE a.product_manufacturer = b.product_manufacturer
mysql_select_db(''PRODUCTS'');
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die(''Could not get data: '' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
echo "Manufacturer:{$row[''product_manufacturer'']} <br> ".
"Count: {$row[''product_count'']} <br> ".
"Product ID: {$row[''product_id'']} <br> ".
"--------------------------------<br>";
}
echo "Fetched data successfullyn";
mysql_close($conn);
?>
On successful data retrieval, you will see the following output −
ID Number: 12345 Nomenclature: Orbitron 4000 Inventory Count: 150 -------------------------------------- ID Number: 12346 Nomenclature: Orbitron 3000 Inventory Count: 200 -------------------------------------- ID Number: 12347 Nomenclature: Orbitron 1000 Inventory Count: 0 -------------------------------------- mysql> Fetched data successfully
Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc