The basic difference between Boolean and tinyint(1) is only in the naming convention. If we say that we need true or false values then Boolean comes to our mind, instead of tinyint(1). These data types are synonyms. It is up to us which data type we want to use- values can be 1 and 0 or true and false. The following is an example. Creating a table with Boolean data type. mysql> create table BooleanDemo -> ( -> Light Boolean -> ); Query OK, 0 rows affected (0.52 sec) ... Read More

BIT can be used to store the value of 1 bit. It could be 0 or 1. We cannot store, for example 2 with data type BIT. If we try to insert 2 with BIT data type, MySQL raises an error. TINYINT can be used to store value of 8 bits. The maximum value we can store is 127.We cannot store, for example 987 with 8 bit value. If we try to insert 987 with TINYINT data type, MySQL raises an error. Let us work it through MySQL version 8.0.12. To check the version installed on your system. mysql> ... Read More

To select last 10 rows from MySQL, we can use a subquery with SELECT statement and Limit concept. The following is an example. Creating a table. mysql> create table Last10RecordsDemo -> ( -> id int, -> name varchar(100) -> ); Query OK, 0 rows affected (0.75 sec) Inserting records into the table. mysql> insert into Last10RecordsDemo values(1, 'John'), (2, 'Carol'), (3, 'Bob'), (4, 'Sam'), (5, 'David'), (6, 'Taylor'); Query OK, 6 rows affected (0.12 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> insert into Last10RecordsDemo ... Read More

The maximum length of a table name is 64 characters long according to MySQl version 8.0.12. Check your installed MySQL version. mysql> select version(); The following is the output. +-----------+ | version() | +-----------+ | 8.0.12 | +-----------+ 1 row in set (0.03 sec) We can check the maximum length of the table name at the time of creating it. If we give more than 64 characters, then it will not create a table and an error is thrown. Creating a table which has more than 64 characters of table name. mysql> ... Read More

We can convert the timestamp to date time with the help of FROM_UNIXTIME() function. Let us see an example. First, we will create a table with column of int type. Then we convert it to timestamp and again into date time. Creating a table with integer type. mysql> create table TimestamptoDateDemo -> ( -> YourTimeStamp int(11) -> ); Query OK, 0 rows affected (0.57 sec) Inserting records into the table. mysql> insert into TimestamptoDateDemo values(1389453221); Query OK, 1 row affected (0.23 sec) To display all the ... Read More

BLOB stands for Binary Large Objects and as its name suggests, it can be used for storing binary data while TEXT is used for storing large number of strings. BLOB can be used to store binary data that means we can store pictures, videos, sounds and programs also. For example, the following image can be stored into BLOB because the image has binary data. BLOB values behave like byte string and BLOB does not have a character set. Therefore, comparison and sorting is fully dependent upon numeric values of bytes. TEXT values behave like non-binary string or character string. ... Read More

To find the number of columns in a MySQL table, use the count(*) function with information_schema.columns and the WHERE clause. Let us see an example. Creating a table. mysql> create table NumberOfColumns -> ( -> id int, -> FirstName varchar(100), -> LastName varchar(100), -> Age int, -> Address varchar(100) -> ); Query OK, 0 rows affected (0.70 sec) Inserting records into table. mysql> insert into NumberOfColumns values(1, 'Shane', 'Watson', 36, 'Australia'); Query OK, 1 row affected ... Read More

The ‘L’ suffix concept in MySQL can be related with Python. In Python 2, the long integer literal is suffixed with L or l, but int and long have been binded into int in version 3. Therefore, there is no need for L or l. Adding large numbers in Python Version 3.7 (Python 3), without using any suffix. Here, if we suffix L or l, Python 3 gives an error. However, Python Version 2 suffixed with L or l will not give an error. The following is the output with no error. Hence, Python int is ... Read More

Non-alphanumeric characters are as follows − @, !, #, &, (), ?, / There is no inbuilt function to remove non-alphanumeric characters from a string in MySQL. Therefore, we create a function which removes all non-alphanumeric characters. The function declaration and definition is as follows. mysql> delimiter // mysql> CREATE FUNCTION RemoveNonAlphaNumeric( s CHAR(255) ) RETURNS CHAR(255) DETERMINISTIC -> BEGIN -> DECLARE var1, length SMALLINT DEFAULT 1; -> DECLARE result CHAR(255) DEFAULT ''; -> DECLARE ch CHAR(1); ... Read More

For our example, we will create two tables and apply Natural Left Join to get the rows from a table not present in the second table. Creating the first table. mysql> create table FirstTableDemo -> ( -> id int, -> name varchar(100) -> ); Query OK, 0 rows affected (0.48 sec) Inserting records into first table. mysql> insert into FirstTableDemo values(1, 'Bob'), (2, 'John'), (3, 'Carol'); Query OK, 3 rows affected (0.13 sec) Records: 3 Duplicates: 0 Warnings: 0 To display all ... Read More