Found 4219 Articles for MySQLi

You can set the INT column to value NULL.The column INT type a nullable column. The syntax is as follows:INSERT INTO yourTableName(yourIntColumnName) values(NULL);To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table nullableIntDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Price int,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.80 sec)Insert the record as NULL for a int column ‘Price’. The query is as follows:mysql> insert into nullableIntDemo(Price) values(NULL); Query OK, 1 row affected (0.11 sec) mysql> insert into ... Read More

You can use != operator from MySQL for this. The syntax is as follows:SELECT *FROM yourTableName WHERE yourColumnName1 !=yourColumnName2 OR (yourColumnName1 IS NULL AND yourColumnName2IS NOT NULL) OR (yourColumnName2 IS NULL AND yourColumnName1 IS NOT NULL);To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table selectTwoColumns    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> FirstNumber int,    -> SecondNumber int,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.87 sec)Insert some records in the table using insert command. The query is ... Read More

The condition 1=0 can be used to stop the query from returning any rows. It returns empty set.The syntax is as follows:SELECT *FROM yourTableName WHERE 1=0;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table ConditionDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(10),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into ConditionDemo(Name) values('Larry'); Query OK, 1 row affected (0.10 sec) mysql> ... Read More

The %M Date Format is not used for displaying short months like Jan for January, Feb for February, etc. You need to use DATE_FORMAT() function with %b format for short month. The syntax is as follows:SELECT DATE_FORMAT(yourColumnName, '%d-%b-%y') AS anyVariableName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table DateFormatMonthDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> ShippingDate date,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command. The ... Read More

To add 30 days to a value in the table, you can use ADDDATE() function with UPDATE command. The syntax is as follows:UPDATE yourTableName SET yourDateColumnName=ADDDATE(yourDateColumnName, INTERVAL 30 DAY);To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table Add30DayDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> ShippingDate date,    -> PRIMARY KEY(ID)    -> ); Query OK, 0 rows affected (0.76 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into Add30DayDemo(ShippingDate) values('2019-02-04'); Query OK, 1 row affected ... Read More

There are lots of ways to select NOT NULL column from two columns. The syntaxes are as follows:Case 1: Use IFNULL() function.The syntax is as follows:SELECT IFNULL(yourColumnName1, yourColumnName2) as anyVariableName from yourTableName;Case 2: Use coalesce() function.The syntax is as follows:SELECT COALESCE(yourColumnName1, yourColumnName2) as anyVariableName from yourTableName;Case 3: Use CASE statement.The syntax is as follows:SELECT CASE WHEN yourColumnName1 IS NOT NULL THEN yourColumnName1 ELSE yourColumnName2 END AS anyVariableName FROM yourTableName;Case 4: Use only IF().The syntax is as follows:SELECT IF (yourColumnName1 ISNULL, yourColumnName2, yourColumnName1) AS NotNULLValue FROM SelectNotNullColumnsDemo;To understand the above syntax, let us create a table. The query to create a ... Read More

To replace all NULL values in a particular field of a particular table, use UPDATE command with IS NULL property. The syntax is as follows:UPDATE yourTableName SET yourColumnName=”yourValue’ WHERE yourColumnName IS NULL;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table Employee_Information_Table    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(20),    -> Salary int,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using insert command. The query to insert record is as ... Read More

To remove all characters after the last comma in the string, you can use SUBSTRING_INDEX().If you do not know the location of the last comma, then you need to find the last comma dynamically using LENGTH().The syntax is as follows −UPDATE yourTableName set yourColumnName = SUBSTRING_INDEX(yourColumnName, ', ', LENGTH(yourColumnName) - LENGTH(REPLACE(yourColumnName, ', ', '')));To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table RemoveAllCharacters    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> FullInfo varchar(200),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 ... Read More

You can use DISTINCT with RLIKE operator to find a list of city names that do not start with vowels.The syntax is as follows −SELECT DISTINCT yourCityColumnName FROM yourTableName WHERE yourCityColumnName NOT RLIKE ‘ ^[AEIOUaeiou].*$’;To understand the above syntax, let us create a table. Here, we have a column for city names.The query to create a table is as follows −mysql> create table Employee_Information    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> EmployeeName varchar(20),    -> CityName varchar(20),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.76 sec)Insert some records in the ... Read More

To change the root username in MySQL, you need to use UPDATE and SET command. The syntax is as follows −UPDATE user set user = ’yourNewUserName’ WHERE user = ’root’;To understand the above syntax, let us switch the database to MySQL using USE command.The query is as follows to switch the database.mysql> use mysql; Database changedNow list all the users from MySQL.user table. The query is as follows −mysql> select user from MySQL.user;The following is the output −+------------------+ | user             | +------------------+ | Manish           | | User2     ... Read More