To check if a column is empty or null , we can use the WHERE clause with IS NULL and for empty we can use the condition ' ' i.e., empty space. The steps required for this are as folllows: First a table is created with the help of CREATE command as follows −mysql> CREATE table ColumnValueNullDemo -> ( -> ColumnName varchar(100) -> ); Query OK, 0 rows affected (0.61 sec)An empty value is inserted into the table using INSERT command. This is given below −mysql> INSERT into ColumnValueNullDemo values(' '); Query OK, 1 row affected (0.14 sec)After that, the ... Read More

To find the records from one MySQL table which don’t exist in another table we can use the subquery for the table which does not have the records. This can be better understood using the given steps −First a table is created using the create command. The table name is ‘PresentHistory’ and it has two columns. This is given as follows −mysql> CREATE table PresentHistory -> ( -> HisID int, -> HisName varchar(100) -> ); Query OK, 0 rows affected (0.54 sec)After creating the table, some records are inserted that will be present in the second table as well. This ... Read More

In the system, the root is defined by another name as well as password. Then the user is created as a root with the help of the create command. This will result in the ERROR 1396. The query for this is given as follows − mysql> create user 'root'@'localhost' identified by 'root123'; After executing the above query, the following error is obtained − ERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'localhost' The user can be created with another name and password successfully. This is given as follows − mysql> create user 'John'@'localhost' identified by ... Read More

To find the port of the MySQL server, the command show can be used. Its syntax is as follows −show variables where variable_name=’port’;The above syntax is used to get the port number of the MySQL server using the following query −mysql> show variables where variable_name = 'port';After executing the above command, port of MySQL server is obtained as 3306. This can be seen in the following output −+---------------+-------+ | Variable_name | Value | +---------------+-------+ | port          | 3306  | +---------------+-------+ 1 row in set (0.01 sec)Alternatively, the system variable @@port can also be used to find ... Read More

The MySQL version before 5.0.3 was capable of storing 255 characters but from the version 5.0.3 , it is capable of storing 65, 535 characters.MySQL official documentation states −The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65, 535 bytes, which is shared among all columns) and the character set used. For example, utf8 characters can require up to three bytes per character, so a VARCHAR column that uses the utf8 character set can be declared to be a maximum of 21, 844 characters. Keep in mind that the limitation of ... Read More

We can store the money values in MySQL in decimal(value1, value2). Here, value1 is the total range including value2. The value2 specifies the number of digits after the decimal point. To understand this concept, the steps are given below.First a table is created using the create command.mysql> CREATE table MoneyDemo -> ( -> Id int, -> Money decimal(10, 2) -> ); Query OK, 0 rows affected (0.46 sec)As can be seen from the above command, the decimal value has 10 digits only and also 2 digits only after the decimal point.After creating the table, some records are inserted with the ... Read More

In MySQL “Where 1=1” results in all the rows of a table as this statement is always true. An example to better unerstand this statement is given as follows −First, a table is created with the help of the create command. This is given as follows −mysql> CREATE table WhereConditon -> ( -> id int, -> name varchar(100) -> ); Query OK, 0 rows affected (0.43 sec)After successfully creating a table, some records are inserted with the help of insert command The query for this is given as follows −mysql> INSERT into WhereConditon values(1, 'John'); Query OK, 1 row affected ... Read More

The max_allowed_packet size is a session variable and is also a read only variable.To check what is the present value of max_allowed_packet, the command show variables is used. It is given as follows −mysql> show variables like 'max_allowed_packet';The following is the output+--------------------+---------+ | Variable_name | Value | +--------------------+---------+ | max_allowed_packet | 4194304 | +--------------------+---------+ 1 row in set (0.04 sec)The value of the max_allowed_packet can be changed in the ‘my.ini’ file on the client side. The query for that is given as follows −max_allowed_packet = 4567890; Now, the value can be changed globally ... Read More

To update a column value, the update command as well as the replace method can be used. The steps to better understand these are given as follows −First create a table with the help of the create command. This is given as follows −mysql> CREATE table DemoOnReplace -> ( -> Id int, -> Name varchar(200) -> ); Query OK, 0 rows affected (0.63 sec)After successfully creating a table, some records are inserted with the help of the insert command. This is shown below −mysql> INSERT into DemoOnReplace values(1, 'John'); Query OK, 1 row affected (0.10 sec) mysql> INSERT into ... Read More

To see all the foreign keys to a table or column, the referenced_column_name command is used.First, two tables are created and then related with the help of the foreign key constraint.Creating the first table −mysql> CREATE table ForeignTable -> ( -> id int, -> name varchar(200), -> Fk_pk int -> ); Query OK, 0 rows affected (0.43 sec)After creating the first table successfully, the second table is created as follows −mysql> CREATE table primaryTable1 -> ( -> Fk_pk int, -> DeptName varchar(200), -> Primary key(Fk_pk) -> ); Query OK, 0 rows affected (0.48 sec)Now, both the tables are related with ... Read More