Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
MySQL Articles
Page 329 of 355
Increase and decrease row value by 1 in MySQL with Stored Procedure?
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 MoreMySQL difference between two timestamps in Seconds?
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 MoreCombine date and time column into a timestamp in MySQL?
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 MoreHow to echo print statements while executing an SQL script?
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 MoreHow to adjust display settings of MySQL command line?
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 MoreComparing dates in MySQL ignoring time portion of a DateTime field?
To compare dates in MySQL except time portion of a datetime field, you can use DATE() function. The syntax is as follows −select *from yourTableName where date(yourColumName) = yourDate;To understand the above concept, let us create a table. The query to create a table is as follows −mysql> create table ComparingDate −> ( −> Name varchar(100), −> Login datetime −> ); Query OK, 0 rows affected (0.50 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into ComparingDate values('John', '2014-04-06 22:50:45'); Query OK, 1 row affected ...
Read MoreCreate date from day, month, year fields in MySQL?
You can use in-built function STR_TO_DATE() from MySQL. The syntax is as follows −SELECT STR_TO_DATE(CONCAT(yourYearColumName, '-', LPAD(yourMonthColumName, 2, '00'), '-', LPAD(yourDayColumName, 2, '00')), '%Y-%m-%d') 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 DateCreateDemo −> ( −> `Day` varchar(2), −> `Month` varchar(2), −> `Year` varchar(4) −> ); Query OK, 0 rows affected (1.68 sec)Insert values for all fields using insert command. The query is as follows −mysql> insert into DateCreateDemo values('15', '12', '2018'); Query OK, 1 row affected (0.09 ...
Read MoreMySQL alias for SELECT * columns?
MySQL alias cannot be used with *. However, it can be used for individual column. The syntax is as follows −select anyaliasName.yourColumnName1 as anyaliasName1, anyaliasName.yourColumnName2 as anyaliasName2, anyaliasName.yourColumnName3 as anyaliasName3, anyaliasName.yourColumnName4 as anyaliasName4, . . . . N from yourTableName as anyaliasName;MySQL alias is a variable of table that can be used to access the column name of that particular table. To understand the above syntax, let us create a table.The query to create a table is as follows −mysql> create table TableAliasDemo −> ( −> Id int, −> Name varchar(100), −> Age int −> ...
Read MoreIs MySQL LIMIT applied before or after ORDER BY?
The MySQL LIMIT is applied after ORDER BY. Let us check the limit condition. Firstly, we will create a table −mysql> create table LimitAfterOrderBy −> ( −> Id int, −> Name varchar(100) −> ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into LimitAfterOrderBy values(101, 'John'); Query OK, 1 row affected (0.18 sec) mysql> insert into LimitAfterOrderBy values(102, 'Carol'); Query OK, 1 row affected (0.15 sec) mysql> insert into LimitAfterOrderBy values(103, 'Bob'); Query OK, 1 row affected (0.21 sec) ...
Read MoreInsert the results of a MySQL select? Is it possible?
You do not need to use values whenever you insert the results of a select. To insert the results of select, let us first create two tables.The first table query is as follows −< FirstTableDemo> mysql> create table FirstTableDemo −> ( −> StudentId int, −> StudentName varchar(100) −> ); Query OK, 0 rows affected (0.41 sec)Now create second table and after that insert the second table records in the first table using INSERT SELECT command.The query to create the second table − mysql> create table SecondTableDemo −> ( −> Id int, −> ...
Read More