Found 4219 Articles for MySQLi

The EXISTS tells if the query returned results or not while IN can be used for comparing one value with other. IN uses literal values. Note − IN is preferred in a subquery whenever a subquery result is very small. If the subquery result is very large then EXISTS is used. Since IN is a preferred choice for subquery results that are smaller, we are considering an example of IN. To create a table. mysql> create table InSubQueryDemo -> ( -> PNumber int, -> PName varchar(100) -> ... Read More

We can store data in MySQL as JSON with the help of JSON data type. The following is an example. Let us now create a table. mysql> CREATE table JsonAsMySQLDemo -> ( -> id int, -> name varchar(100), -> PageDemo JSON, -> eventInfo JSON -> ); Query OK, 0 rows affected (0.67 sec) Storing records into JSON data type. mysql> INSERT into JsonAsMySQLDemo values -> ( -> 1, ... Read More

To list all columns in a table, we can use the SHOW command. Let us first create a table. mysql> create table ColumnsList -> ( -> id int, -> Firstname varchar(200), -> LastName varchar(100), -> Age int, -> Address varchar(300), -> CollegeName varchar(100) -> ); Query OK, 0 rows affected (1.33 sec) Syntax to list all column names. show columns from yourTableName; The following is the output. mysql> show columns from ... Read More

We can convert a string to date with the help of STR_TO_DATE() function. Let us see an example. Creating a table. mysql> create table StringToDateDemo -> ( -> YourDate varchar(100) -> ); Query OK, 0 rows affected (0.49 sec) Inserting records into the table. mysql> insert into StringToDateDemo values('10/27/2018'); Query OK, 1 row affected (0.11 sec) The following is the syntax to convert string to date using the STR_TO_DATE() function. SELECT STR_TO_DATE(yourColumnName, '%m/%d/%Y') from yourTableName; Let us now implement it. ... Read More

We can store an IP address with the help of INT unsigned. While using INSERT, include INET_ATON() and with SELECT, include INET_NTOA(). IP address is in dotted format. Let us see an example. Creating a table. mysql> create table IPV4AddressDemo -> ( -> `IPV4Address` INT UNSIGNED -> ); Query OK, 0 rows affected (0.52 sec) Inserting IP address into the table, with INET_ATON. mysql> insert into IPV4AddressDemo values(INET_ATON("120.0.0.1")); Query OK, 1 row affected (0.17 sec) To display all records. mysql> select *from IPV4AddressDemo; The following ... Read More

In MySQL, the term cardinality refers to the uniqueness of data values that can be put into columns. It is a kind of property which influences the ability to search, cluster and sort data. Cardinality can be of two types which are as follows − Low Cardinality − All values for a column must be same. High Cardinality − All values for a column must be unique. The concept of high cardinality is used if we put a constraint on a column in order to restrict duplicate values. High Cardinality The following is an example of High Cardinality, ... Read More

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