MySQLi Articles - Page 244 of 341

To adjust display settings of MySQL command line, use the /G at the end of MySQL queries instead of semicolon(;).The syntax is as follows −SELECT *FROM yourTableName \GThe above syntax adjusts the display settings. Here we will display records in row format from our sample ‘studenttable’ table which we created using CREATE −mysql> create table StudentTable    −> (    −> Id int,    −> Name varchar(100)    −> ); Query OK, 0 rows affected (0.65 sec)To get all the records −mysql> select *from StudentTable;The following displays the records −+------+---------+ | Id   | Name    | +------+---------+ | ... Read More

To perform echo print statements while executing SQL scripts, use the following syntax.The syntax is as follows −SELECT ‘anyStringValue as’ ‘;The query is as follows −mysql> select 'This is a SQL Script' AS' ';The following is the output −+----------------------+ | | +----------------------+ | This is a SQL Script | +----------------------+ 1 row in set, 1 warning (0.00 sec)You can add dynamic data to your status like insert, update and delete with the help of concat() function. The query is ... Read More

To combine date and time column into a timestamp, you can use cast() function with concat().The syntax is as follows −select cast(concat(yourDateColumnName, ' ', yourTimeColumnName) as datetime) as anyVariableName from yourTableName;In the above concept, you will use cast() when your date and time is in string format. The cast() function can be used only for datetime. To understand the above syntax, let us create a table.The query to create a table is as follows −mysql> create table DateAndTimeToTimestamp −> ( −> Duedate date, −> DueTime time −> ); ... Read More

You can use in-built function UNIX_TIMESTAMP() from MySQL to get the timestamps and the difference between two timestamps. The syntax is as follows −SELECT UNIX_TIMESTAMP(yourColumnName1) - UNIX_TIMESTAMP(yourColumnName2) as anyVariableName from yourTableName;To understand the above concept, let us create a table. The following is the query to create a table −mysql> create table DifferenceInSeconds −> ( −> FirstTimestamp TIMESTAMP, −> SecondTimestamp TIMESTAMP −> ); Query OK, 0 rows affected (0.93 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into DifferenceInSeconds values('2012-12-12 ... Read More

Let us first create a table to increase and adecrease row value by 1. The following is the query −mysql> create table IncrementAndDecrementValue    −> (    −> UserId int,    −> UserScores int    −> ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into IncrementAndDecrementValue values(101, 20000); Query OK, 1 row affected (0.13 sec) mysql> insert into IncrementAndDecrementValue values(102, 30000); Query OK, 1 row affected (0.20 sec) mysql> insert into IncrementAndDecrementValue values(103, 40000); Query OK, 1 row affected (0.11 sec)Display all records ... Read More

You can increase and decrease row value by 1 in MySQL using UPDATE command. The syntax is as follows −UPDATE yourTableName set yourColumnName = yourColumnName-1 where condition;Let us create a table to decrease row value by 1. The following is the query −mysql> create table IncrementAndDecrementValue    −> (    −> UserId int,    −> UserScores int    −> ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into IncrementAndDecrementValue values(101, 20000); Query OK, 1 row affected (0.13 sec) mysql> insert into IncrementAndDecrementValue values(102, 30000); ... Read More

To get average on time column, use the below syntax. It will give the average in time format −SELECT SEC_TO_TIME(AVG(TIME_TO_SEC(yourColumnName))) as anyVariableName from yourTableName;To understand the above concept, let us create a table. The following is the query −mysql> create table AverageOnTime    −> (    −> PunchInTime time    −> ); Query OK, 0 rows affected (0.61 sec)Insert time values in the table using insert command. The query to insert records is as follows −mysql> insert into AverageOnTime values('00:00:40'); Query OK, 1 row affected (0.20 sec) mysql> insert into AverageOnTime values('00:02:50'); Query OK, 1 row affected (0.15 sec) ... Read More

To extract first word from a field, use in-built SUBSTRING_INDEX() function. The syntax is as follows −SELECT SUBSTRING_INDEX(yourColumnName, ’ ‘, 1) as anyVariableName from yourTableName;In the above query, if you use -1 in place of 1 then you will get the last word. To understand the above concept, let us create a table. The following is the query to create a table.mysql> create table FirstWordDemo −> ( −> AllWords longtext −> ); Query OK, 0 rows affected (0.83 sec)Now insert some words in the table using insert command. The query is ... Read More

To display the value of a variable, you can use select statement. The syntax is follows −SELECT @yourVariableName;Let us first create a variable. This can be done using SET command. The following is the syntax to create a variable −SET @yourVariableName = yourValue;Let us check the above syntax to create a variable and display the value of created variable.The following is the query to create a variable −mysql> set @FirstName = 'Bob'; Query OK, 0 rows affected (0.04 sec)Now you can display the value of a variable using select statement. The query is as follows −mysql> select @FirstName;The following is ... Read More

You can update MySQL with IF condition as well as CASE statement. For this purpose, let us first create a table. The query to create a table −mysql> create table UpdateWithIfCondition    −> (    −> BookId int,    −> BookName varchar(200)    −> ); Query OK, 0 rows affected (0.60 sec)Now you can insert records in the table using insert command. The query is as follows −mysql> insert into UpdateWithIfCondition values(1000, 'C in Depth'); Query OK, 1 row affected (0.12 sec) mysql> insert into UpdateWithIfCondition values(1001, 'Introduction to Java'); Query OK, 1 row affected (0.14 sec)Display all records ... Read More