Found 4219 Articles for MySQLi

You need to use the CAST operator along with CONV() function. The CONV() function can be used to convert one base number system to another base system.For Example, The 16 is one base system and 10 is another base system. The 16 base system is hexadecimal and 10 is a decimal.The syntax is as follows −SELECT CAST(CONV('yourColumnName', 16, 10) AS UNSIGNED INTEGER) as anyAliasName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table castTypeToBigIntDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,     ... Read More

To update the decimal column to allow more digit, use the MODIFY COLUMN. The syntax is as follows:ALTER TABLE MODIFY COLUMN yourColumnName DECIMAL(yourIntValue, yourIntValue);To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table allowDecimalWithMoreDigit    -> (      -> Id int NOT NULL AUTO_INCREMENT,    -> Salary DECIMAL(3, 2),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.64 sec)Now you can check the description of table using DESC command. The syntax is as follows:DESC yourTableName;Now you can check the description of table using above ... Read More

The AUTO_INCREMENT=5 in a create table query tells that the first record will start from 5 i.e. not default 1. As we know if you do not set the value to AUTO_INCREMENT then MySQL starts from 1 by default.The syntax is as follows:CREATE TABLE yourTableName ( yourColumnName1 dataType NOT NULL AUTO_INCRMENT, . . . N, PRIMARY KEY(yourColumnName1 ) )AUTO_INCREMENT=5;To understand the above syntax, let us create a table.Case1 − The table starts auto increment from 1 because it is the default standard.The query to create a table is as follows:mysql> create table defaultAutoIncrementDemo    -> (    -> Id int ... Read More

For this, you need to remove the default keyword. The syntax is as follows:ALTER TABLE yourTableName ADD COLUMN yourColumnName dataType NOT NULL AFTER yourColumnName;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table AddingColumnDefaultValueNOTNULL    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> FirstName varchar(20),    -> LastName varchar(20),    -> Age int,    -> Address varchar(100),    -> Salary int,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.58 sec)Now check the description of table. The query is as follows:mysql> desc ... Read More

In MySQL, there are two approaches by which you can reset the MySQL field to default value. One is default keyword and another is default() function.Case 1: Using default keyword. The syntax is as follows:UPDATE yourTableName SET yourColumnName=default where yourCondition;Case 2: Using default() function. The syntax is as follows:UPDATE yourTableName SET yourColumnName=default(yourColumnName) where yourCondition;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table Default_Demo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(20),    -> Age int,    -> Salary float,    -> PRIMARY ... Read More

There is no DATEPART() function in MySQL, you need to use MONTH() function to extract the month name from date column. The syntax is as follows:SELECT *FROM yourTableName WHERE MONTH(yourDateColumnName)=yourValue;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table SelectRowFromDatePart -> ( -> Id int NOT NULL AUTO_INCREMENT, -> LoginDate date, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (2.42 sec)Now you can insert some records in the table using ... Read More

To add the JDBC MySQL driver to an Eclipse project, you need to follow the below steps.The first step is as follows:Step1: Create a dynamic web project with some name in Eclipse.Step2: After pressing the Dynamic Web Project, a new window will open. Now give the project name. The screenshot is as follows:After clicking the Finish button, you will get a project structure. The screenshot is as follows:Therefore, I have a project name JDBCJarFiles and in WEB-INF, there is a lib folder. You can add JDBC jar files in lib folder. Now, paste the jar files here. The screenshot is as ... Read More

You need to use COALESCE() function for this. The syntax is as follows:INSERT INTO yourTableName(yourColumnName1, yourColumnName2) SELECT 1 + COALESCE((SELECT MAX(yourColumnName1) FROM yourTableName WHERE yourColumnName2=’yourValue’), 0), ’yourValue’;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table InsertMaxPlus1Demo    -> (    -> Id int,    -> Name varchar(20)    -> ); Query OK, 0 rows affected (1.27 sec)Now you can insert some records in the table using insert command. The query is as follows:mysql> insert into InsertMaxPlus1Demo(Id, Name) values(1, 'John'); Query OK, 1 row affected (0.12 sec) mysql> insert ... Read More

If you want case-insensitive distinct, you need to use UPPER() or LOWER().Case 1: Using UPPER().The syntax is as follows:SELECT DISTINCT UPPER(yourColumnName) FROM yourTableName;Case 2: Using LOWER().The syntax is as follows:SELECT DISTINCT LOWER(yourColumnName) FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table CaseInsensitiveDistinctDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> UserEmailId varchar(30), -> UserPassword varchar(10), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.64 sec)Now you ... Read More

To run SQL file in database, you need to use below syntax:mysql -u yourUserName -p yourDatabaseName < yourFileName.sqlTo understand the above syntax, let us open command prompt using windows+R shortcut key.The snapshot is as follows:After pressing OK button, you will get a command prompt. The snapshot is as follows:Now reach the bin directory, the snapshot is as follows:Here is my file ‘mydb.sql’ which is located in bin directory. The snapshot and content of SQL file is as follows:Now you can type the above syntax which I have discussed to run SQL file. The snapshot of command is as follows:Now you ... Read More