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
-
Economics & Finance
MySQL Articles
Page 280 of 355
Can MySQL concatenate strings with ||?
Yes, you can concatenate strings with || in MySQL with the help of sql_mode. Set the sql_mode to PIPES_AS_CONCAT.The syntax is as followsset sql_mode=PIPES_AS_CONCAT;The following is the syntax to concat with the help of ||.SELECT ‘yourValue' || yourColumName AS anyAliasName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table PipeConcatDemo - > ( - > Name varchar(20) - > ); Query OK, 0 rows affected (0.93 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into PipeConcatDemo values('Larry'); Query ...
Read MoreHow to add a "created at" column in a table to set the timestamp in MySQL?
You need to use ALTER command to add a created at column to an already created table in MySQL.Let us first create a table. The query to create a table is as follows. Here is your table without the “created at” columnmysql> create table formDemo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > Email varchar(128), - > PhoneNumber varchar(15), - > Country varchar(30), - > Platform varchar(40) - > ); Query OK, 0 ...
Read MoreHow to fix the incorrect datetime value while inserting in a MySQL table?
To avoid the incorrect datetime value error, you can use the STR_TO_DATE() method.As we know the datetime format is YYYY-MM-DD and if you won’t insert in the same format, the error would get generated.Let us see what actually lead to this error. For this, let us create a new table. The query to create a table is as followsmysql> create table CorrectDatetimeDemo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > ArrivalTime datetime - > ); Query OK, 0 rows affected (0.63 sec)The occurs when we try to include a ...
Read MoreHow to increment all the rows of a particular column by 1 in a single MySQL query (ID column +1)?
To increment all the rows of a particular ID column by 1, you need to use UPDATE command and update the table. The syntax of the query is as follows. We have also used ORDER BY hereUPDATE yourTableName SET yourIdColumnName=yourIdColumnName+1 ORDER BY yourIdColumnName DESC;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table IdColumnadd1Demo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY - > ); Query OK, 0 rows affected (0.58 sec)Insert some records in the ...
Read MoreWhere is the MySQL database gets saved when it is created?
If you want the database location i.e. where it is created in MySQL, you can use system variable @@datadir.The syntax is as followsSELECT @@datadir;The following is the querymysql> select @@datadir;Here is the output. The above query returns the location+---------------------------------------------+ | @@datadir | +---------------------------------------------+ | C:\ProgramData\MySQL\MySQL Server 8.0\Data\ | +---------------------------------------------+ 1 row in set (0.00 sec)Now reach the above directory in your system. The screenshot of the ...
Read MoreChange a MySQL Column datatype from text to timestamp?
To change a MySQL column datatype from text to timestamp, you need to use ALTER command.The syntax is as followsALTER TABLE yourTableName MODIFY COLUMN yourColumnName TIMESTAMP;To understand the above syntax, let us create a table.The query to create a table is as followsmysql> create table textTotimestampdemo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > Source text - > ); Query OK, 0 rows affected (0.44 sec)Here is the description of table using DESC command.The syntax is as followsDESC yourTableName;The query is as followsmysql> desc textTotimestampdemo;The following is the output+--------+---------+------+-----+---------+----------------+ | ...
Read MoreHow to set two variables in a stored procedure with a single MySQL select statement?
For this, let us first create a new table in MySQLmysql> create table useProcedure - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > FirstName varchar(20), - > LastName varchar(20) - > ); Query OK, 0 rows affected (0.65 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into useProcedure(FirstName, LastName) values('Adam', 'Smith'); Query OK, 1 row affected (0.27 sec)The following is your stored procedure to set two variables in a stored procedure with single select ...
Read MoreHow to implement GROUP by range in MySQL?
To group by range in MySQL, let us first create a table. The query to create a table is as followsmysql> create table GroupByRangeDemo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > YourRangeValue int - > ); Query OK, 0 rows affected (0.78 sec)Now you can insert some records in the table using insert command.The query is as followsmysql> insert into GroupByRangeDemo(YourRangeValue) values(1); Query OK, 1 row affected (0.14 sec) mysql> insert into GroupByRangeDemo(YourRangeValue) values(7); Query OK, 1 row affected (0.15 sec) mysql> insert into GroupByRangeDemo(YourRangeValue) values(9); Query OK, 1 ...
Read MoreCan we exclude entries with "0" while using MySQL AVG function?
To exclude entries with “0”, you need to use NULLIF() with function AVG().The syntax is as followsSELECT AVG(NULLIF(yourColumnName, 0)) AS anyAliasName FROM yourTableName;Let us first create a tablemysql> create table AverageDemo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > StudentName varchar(20), - > StudentMarks int - > ); Query OK, 0 rows affected (0.72 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into AverageDemo(StudentName, StudentMarks) values('Adam', NULL); Query OK, 1 row affected (0.12 sec) mysql> insert into AverageDemo(StudentName, StudentMarks) values('Larry', 23); Query OK, ...
Read MoreCalculate total time duration (add time) in MySQL?
To calculate the total time duration in MySQL, you need to use SEC_TO_TIME(). Let us see an example by creating a tablemysql> create table AddTotalTimeDemo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > LoginTime time - > ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into AddTotalTimeDemo(LoginTime) values('05:05:00'); Query OK, 1 row affected (0.10 sec) mysql> insert into AddTotalTimeDemo(LoginTime) values('07:20:00'); Query OK, 1 row affected (0.16 sec) mysql> insert ...
Read More