MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

NULL Values
The MySQL NULL Values
MySQL uses the term "NULL" to represent a non-existent data value in the database. These values are different from an empty string or zero and do not occupy any storage space in the database. They are used to indicate the absence of a value or an unknown value in a data field.
There are some common reasons why a value may be NULL 
The value may not be provided during data entry.
The value is not yet known.
Since NULL values are non-existent, you cannot use standard comparison operators such as "=", "<," or ">" with them. Instead, you can use the "IS NULL," "IS NOT NULL," or "NOT NULL" operators to check if a value is NULL.
Creating a Table without NULL Values
To create a table without NULL values, you can use the "NOT NULL" keyword while defining the columns. If a column is specified as "NOT NULL," an error will occur when attempting to insert NULL values into that specific column.
Syntax
The basic syntax for creating a table with "NOT NULL" columns is as follows 
CREATE TABLE table_name (
   column1 datatype NOT NULL,
   column2 datatype NOT NULL,
   ...
   columnN datatype
);
Where, "NOT NULL" indicates that a column must always contain a specific value of the defined data type. Columns marked as "NOT NULL" cannot accept NULL values. On the other hand, you can insert NULL values into the columns without the "NOT NULL" constraint.
Example
Let us create a table named "CUSTOMERS" using the following query 
CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25) ,
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);
Now, let us insert some records into the above-created table 
INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', NULL),
(7, 'Muffy', 24, 'Indore', NULL),
The CUSTOMERS table obtained is as follows 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
NULL
7
Muffy
24
Indore
NULL
Now, to retrieve records that are not NULL, you can use the "IS NOT NULL" operator as shown below
SELECT ID, NAME, AGE, ADDRESS, SALARY 
FROM CUSTOMERS 
WHERE SALARY IS NOT NULL;
Following is the output of the above code 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
To retrieve records that are NULL, you can use the "IS NULL" operator as shown below 
SELECT ID, NAME, AGE, ADDRESS, SALARY 
FROM CUSTOMERS
WHERE SALARY IS NULL;
The output produced is as follows 
ID
NAME
AGE
ADDRESS
SALARY
6
Komal
22
Hyderabad
NULL
7
Muffy
24
Indore
NULL
Updating NULL Values in a Table
To update NULL values in a table, you can use the "UPDATE" statement with the "IS NULL" operator. This filter the rows containing NULL values and set new values using the "SET" keyword.
Example
Here, we are updating the NULL values in the SALARY column of the CUSTOMERS table as shown below 
UPDATE CUSTOMERS SET SALARY = 9000 WHERE SALARY IS NULL;
Output
After executing the above code, we get the following output 
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0
Verification
You can verify whether the records in the table are updated or not using the following query 
SELECT * FROM CUSTOMERS;
The output displayed is as follows 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
9000.00
7
Muffy
24
Indore
9000.00
Deleting Records with NULL Values
To delete records with NULL values from a table, you can use the "DELETE FROM" statement with the "IS NULL" operator in the "WHERE" clause.
Example
Now, we are deleting records with NULL values in the SALARY column as shown below 
DELETE FROM CUSTOMERS WHERE SALARY IS NULL;
Output
Output of the above code is as shown below 
Query OK, 2 rows affected (0.01 sec)
Verification
You can verify whether the records in the table is deleted or not using the SELECT statement as follows 
SELECT * FROM CUSTOMERS;
The table produced is as shown below 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
NULL Value Using a Client Program
We can also execute NULL value using the client program.
Syntax
PHPNodeJSJavaPython
To check whether a column's value is null through a PHP program, we need to execute the "SELECT" statement using the mysqli function query() as follows 
$sql = "SELECT * from tcount_tbl WHERE tutorial_count IS NULL";
$mysqli->query($sql);
Example
Following are the programs 
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s", $mysqli->connect_error);
    exit();
}
//printf('Connected successfully.');
$sql = "SELECT * from tcount_tbl WHERE tutorial_count IS NULL";
if($result = $mysqli->query($sql)){
    printf("Table record: \n");
    while($row = mysqli_fetch_array($result)){
        printf("Tutorial_author %s, Tutorial_count %d",
        $row['tutorial_author'],
        $row['tutorial_count']);
        printf("\n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();
Output
The output obtained is as shown below 
Table record:
Tutorial_author mahnaz, Tutorial_count 0
Tutorial_author Jen, Tutorial_count 0
﻿
﻿
﻿
﻿
﻿
﻿
﻿

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	NULL
7	Muffy	24	Indore	NULL

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

NULL Values

The MySQL NULL Values

Creating a Table without NULL Values

Syntax

Example

Updating NULL Values in a Table

Example

Output

Verification

Deleting Records with NULL Values

Example

Output

Verification

NULL Value Using a Client Program

Syntax

Example

Output