MySQL Articles - Page 309 of 439

To get difference between two timestamps in seconds, use two in-built functions TIME_TO_SEC() and TIMEDIFF() in MySQL. The syntax is as follows −select time_to_sec(timediff(yourCoulnName1, yourCoulnName2)) as anyVariableName from yourTableName;To understand the above concept, let us first create a table. The query to create a table.mysql> create table TimeToSecond −> ( −> MyTime timestamp, −> YourTime timestamp −> ); Query OK, 0 rows affected (0.48 sec)Now you can insert some datetime values in the table. The query is as follows −mysql> insert into TimeToSecond values('2016-05-10 10:02:00', '2016-05-10 10:00:00'); Query ... Read More

To concatenate two columns, use CONCAT() function in MySQL. The syntax is as follows −select CONCAT(yourColumnName1, ' ', yourColumnName2) as anyVariableName from yourTableName;To understand the above concept, let us create a table. The query to create a table is as follows −mysql> create table concatenateTwoColumnsDemo    −> (    −> StudentId int,    −> StudentName varchar(200),    −> StudentAge int    −> ); Query OK, 0 rows affected (1.06 sec)Now you can insert some records in the table. The query to insert records is as follows −mysql> insert into concatenateTwoColumnsDemo values(1, 'Sam', 21); Query OK, 1 row affected (0.18 sec) ... Read More

To front pad zip code with 0, use LPAD() function in MySQL. The syntax is as follows −SELECT LPAD(yourColumnName, columnWidth+1, '0') as anyVariableName from yourTableName;To understand the above concept of LPAD() to add front pad zip code with 0, let us create a table. One of the columns of the table is Zip Code. The following is the query to create a table.mysql> create table ZipCodePadWithZeroDemo    −> (    −> Name varchar(200),    −> YourZipCode int(6)    −> ); Query OK, 0 rows affected (0.44 sec)Insert some records in the table. The query to insert records is as follows ... Read More

To check how many users are present in MySQL, use MySQL.user table. The syntax is as follows to check how many users are present.mysql> SELECT User FROM mysql.user;The following output displays the users −+------------------+ | User | +------------------+ | Mac | | Manish | | mysql.infoschema | | mysql.session | | mysql.sys | | root ... Read More

To select MySQL based on month and year, use in-built function YEAR() and MONTH(). The syntax is as follows −select *from yourTableName where YEAR(yourColumnName) = YearValue AND MONTH(yourColumnName) = monthValue;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table selectDataOnYearandMonthDemo −> ( −> BookId int, −> BookName varchar(100), −> BookDueDate datetime −> ); Query OK, 0 rows affected (0.57 sec)Now you can insert some records in the table. The query is as ... Read More

To insert the current date (not time), then you can use in-built function CURDATE() from MySQL. The syntax is as follows −INSERT INTO yourTableName values(curdate());Or if you want to add date and time both then you can use the in-built function NOW() from MySQL. The syntax is as follows −INSERT INTO yourTableName values(now());To understand both the syntax, let us first create a table. The query to create a table is as follows −mysql> create table NowAndCurdateDemo −> ( −> YourDueDate datetime −> ); Query OK, 0 rows affected (1.75 sec)Implement both ... Read More

In order to install MySQL command line client for Windows, you need to visit the following URL to get the download link https://dev.mysql.com/downloads/mysql/ −The snapshot is as follows −After that you need to select operating system. The snapshot is as follows −You need to choose Windows (x86, 32/64-bit) and download the installer.

To know whether a MySQL table is using MyISAM or InnoDB engine then you can use below syntax.The below syntax can be used for multiple tables −show table status from yourDatabaseName;Here is the syntax that can be used for a specific table i.e. to know the engine of a table −show table status from yourDatabaseName Like ‘yourTableName’.The following is the query to display engine of all the tables −mysql> show table status from sampleTest;The following is the output −+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ | Name          | Engine  | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | ... Read More

To update the column size, you can use alter command. The syntax is as follows −alter table yourTableName change yourColumnName yourColumnName data type;To understand the above syntax, let us create a table. The query to create a table −mysql> create table DataTruncated −> ( −> id int, −> Name varchar(5) −> ); Query OK, 0 rows affected (0.64 sec)Look at the column ‘Name’ above, the column size is 5. Whenever we will give the size greater than 5 then MySQL gives the following error −mysql> ... Read More

To get the primary key of a table, you can use the show command. The syntax is as follows −SHOW INDEX FROM yourDatebaseName.yourTableName WHERE Key_name = 'PRIMARY';Suppose, we have a table with two primary keys; one of them is “Id” and second is “RollNum". The query for a table is as follows −mysql> create table TwoOrMorePrimary    −> (    −> Id int,    −> Name varchar(200),    −> RollNum int    −> ,    −> Primary key(Id, Age)    −> ); Query OK, 0 rows affected (0.85 sec)Apply the above syntax to get primary key of a table. ... Read More