Found 4219 Articles for MySQLi

We can return 0 for NULL in MySQL with the help of IFNULL() method. The syntax of IFNULL() is as follows. IFNULL(YOUREXPRESSION, 0); Let us see an example. First, we will create a table. mysql> create table NullDemoWithZero -> ( -> id varchar(200) -> ); Query OK, 0 rows affected (0.65 sec) After creating a table, let us insert some records in the table using the INSERT command. The query is as follows − mysql> insert into NullDemoWithZero values(); Query OK, 1 row affected (0.16 sec) ... Read More

The int type takes 4 byte signed integer i.e. 32 bits ( 232 values can be stored). The BigInt type takes 8 byte signed integer i.e. 64 bits (264 values can be stored). Let us see an example. Creating a table with zerofill, that would add leading zeros. mysql> create table IntandBigint20Demo -> ( -> Number int(20) zerofill, -> Code BigInt(20) zerofill -> ); Query OK, 0 rows affected (0.58 sec) After creating a table, we will insert records into the table. mysql> insert into ... Read More

Let’s say we have a table and now there is a requirement to add AUTO_INCREMENT on column name. For that, use the MODIFY command. Here, we will create a demo table first. mysql> create table AddingAutoIncrement -> ( -> Id int, -> Name varchar(200), -> Primary key(Id) -> ); Query OK, 0 rows affected (0.47 sec) We have created a table above and now let us alter the table to add AUTO_INCREMENT on column name ‘Id’. The syntax is as follows − ... Read More

First, let us create a table with the UNIQUE constraint. It suggests that we cannot add duplicate values. Creating a table. mysql> create table UniqueConstraintDemo -> ( -> Name varchar(200) unique -> ); Query OK, 0 rows affected (1.05 sec) Now, we can check whether the table has UNIQUE constraint or not with the help of DESC command. The query is as follows. mysql> DESC UniqueConstraintDemo; The following is the output. +-------+--------------+------+-----+---------+-------+ | Field | Type | Null ... Read More

To add composite primary key, use the ALTER command. Let us first create a demo table The query to create a table. mysql> create table CompositePrimaryKey -> ( -> Id int, -> StudentName varchar(100), -> Age int -> ); Query OK, 0 rows affected (0.56 sec) Haven’t added composite primary key above till now. Let us now check with the help of desc command. mysql> desc CompositePrimaryKey; The following is the output. +-------------+--------------+------+-----+---------+-------+ | Field ... Read More

The replace() function can be used to replace a string with another string. To understand replace(), we need to create a table with some records. The following is the query to create a table. mysql> create table replaceDemo -> ( -> Name varchar(200) -> ); Query OK, 0 rows affected (0.55 sec) Insert some records with the help of INSERT command. The query to insert records is as follows − mysql> insert into replaceDemo values('John'); Query OK, 1 row affected (0.17 sec) mysql> insert into replaceDemo values('Demo'); ... Read More

To remove any database, we need to use DROP command in MySQL. Here is the syntax. DROP DATABASE yourDatabaseName; First, let us check how many databases are present in MySQL. Here is the query for the same. mysql> SHOW DATABASES; The following is the output. +--------------------+ | Database | +--------------------+ | business | | database1 | | databasesample | | education ... Read More

For representation of money, we need to use Decimal (TotalDigitsinteger, DigitsAfterDecimalinteger) method. Let’s say, we need to display the value 345.66. For that, count how many digits are available. In value 345.66, there are 5 digits in total and 2 digits after decimal point, which is 66. We can represent the same with the help of Decimal() method from MySQL. Here is the exact representation. DECIMAL(5, 2) Let us first create a table and consider the same above representation for our example − mysql> create table MoneyRepresentation -> ( -> Money ... Read More

We can set the now() function as a default value with the help of dynamic default. First, we will create a table with data type” datetime”. After that, we will set now() as the default value for column “MyTime” as shown below. Creating a table. mysql> create table DefaultDateTimeDemo -> ( -> MyTime datetime default CURRENT_TIMESTAMP -> ); Query OK, 0 rows affected (0.59 sec) After creating the above table, we won’t insert any value while using the insert command. This is done so that we can get the ... Read More

Here are the usages of the operator in MySQL. Case 1 This operator is similar to = operator i.e. when the value is equal then the result will become true(1), otherwise false(0). In the first case both = and operators work same. Case 2 Whenever we compare any value with NULL then the operator gives the value 0 and when we compare with NULL NULL, then it returns 1. While in case of = operator, this does not happen. Whenever we compare any value with NULL, it returns NULL. If we compare NULL with NULL, then ... Read More