Found 4219 Articles for MySQLi

You can convert varchar to double using CAST() function. The syntax is as follows:SELECT yourColumnName1, yourColumnName2, ......N, CAST(yourColumnName AS DECIMAL(TotalDigit, DigitAfterDecimalPoint)) anyVariableName FROM yourtableName ORDER BY anyVariableName DESC;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table VarcharToDouble    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(10),    -> Amount varchar(10) ,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.67 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into VarcharToDouble(Name, Amount) values('John', ... Read More

In MySQL, convert datetime to integer using UNIX_TIMESTAMP() function. The syntax is as follows:SELECT UNIX_TIMESTAMP(yourDatetimeColumnName) 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 DatetimeToInteger    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> ArrivalTime datetime,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.94 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into DatetimeToInteger(ArrivalTime) values(now()); Query OK, 1 row affected (0.09 sec) mysql> insert into DatetimeToInteger(ArrivalTime) values(curdate()); Query OK, 1 ... Read More

To concatenate more than 2 fields with SQL, you can use CONCAT() or CONCAT_WS() function. The syntax is as follows. Let us first see using CONCAT().SELECT CONCAT(yourColumnName1, '/', yourColumnName2, '/', yourColumnName3, '/', ......N) AS anyVariableName FROM yourTableName;The syntax is as follows:SELECT CONCAT_WS(‘/’, yourColumnName1, yourColumnName2, .....N) 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 MoreThan2ColumnConcat    -> (    -> Id int,    -> Name varchar(20),    -> Age int,    -> Marks int    -> ); Query OK, 0 rows affected (2.59 sec)Insert some ... Read More

You can use CASE from MySQL to change value from 1 to Y. Let us first create a table. The query to create a table is as follows:mysql> create table changeValuefrom1toY    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> isValidAddress tinyint(1),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.76 sec)Now you can insert some records in the table using insert command. The query is as follows:mysql> insert into changeValuefrom1toY(isValidAddress) values(1); Query OK, 1 row affected (0.22 sec) mysql> insert into changeValuefrom1toY(isValidAddress) values(0); Query OK, 1 row affected (0.16 sec) mysql> insert ... Read More

You can use IF() from MySQL to change value from 1 to Y. The syntax is as follows:SELECT IF(yourColumnName, ’Y’, yourColumnName) 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 changeValuefrom1toY    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> isValidAddress tinyint(1),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.76 sec)Now you can insert some records in the table using insert command. The query is as follows:mysql> insert into changeValuefrom1toY(isValidAddress) values(1); Query OK, 1 row affected (0.22 ... Read More

You need to use GROUP BY command with aggregate function count(*) from MySQL to achieve this. The syntax is as follows:SELECT yourColumnName, COUNT(*) AS anyVariableNameFROM yourTableName GROUP BY yourColumnName;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table selectDistinct_CountDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(10),    -> AppearanceId int,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into selectDistinct_CountDemo(Name, AppearanceId) values('Larry', ... Read More

If your table name or column name are any reserved words then you need to use quotes around table name and column name in a MySQL query. You need to use backticks around table name and column name. The syntax is as follows:SELECT *FROM `table` where `where`=condition;Here is the query to create a table without quotes with reserved words. You will get an error since they are predefined reserved words. The error is as follows:mysql> create table table    -> (    -> where int    -> ); ERROR 1064 (42000): You have an error in your SQL syntax; check ... Read More

You can execute insert if table is empty with the help of subquery. For that, work on not exists condition with subquery.The below syntax will work only when your table is empty. If your table is not empty then it will not insert the record. The syntax is as follows:INSERT INTO yourTableName(yourColumnName) SELECT ‘anyValue’ WHERE NOT EXISTS (SELECT *FROM yourTableName);To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table ExecuteInsertDemo    -> (    -> Name varchar(20)    -> ); Query OK, 0 rows affected (0.67 sec)Let us insert ... Read More

You can add a value to each row in MySQL using UPDATE command.Let us see when your column is an integer. The syntax is as follows:UPDATE yourTableName SET yourIntegerColumnName = yourIntegerColumnName+anyValue; UPDATE yourTableName SET yourIntegerColumnName = anyValue WHERE yourIntegerColumnName IS NULL;You can add a value for a date column name. The syntax is as follows:UPDATE yourTableName SET yourDateColumnName = DATEADD(yourDateColumnName, INTERVAL anyIntegerMonth)To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table addEachRowValue    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Amount int,    -> ShippingDate ... Read More

To remove NOT NULL restriction from column in MySQL, use ALTER command. The syntax is as follows:ALTER TABLE yourTableName MODIFY COLUMN yourColumnName dataType;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table NotNullDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(20) NOT NULL,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.58 sec)In the above table, if you insert the NULL value to the column ‘Name’ then MySQL will give an error of NOT NULL restriction. The query to ... Read More