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
MySQLi Articles
Page 318 of 341
How can a query multiply 2 cells for each row in MySQL?
You can use multiplication operator (*) between two cells. The syntax is as followsSELECT yourColumnName1, yourColumnName2, yourColumnName1*yourColumnName2 as ‘anyVariableName’ from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table MultiplicationDemo -> ( -> FirstPrice int, -> SecondPrice int -> ); Query OK, 0 rows affected (0.63 sec)Now you can display all records from the table using insert command. The query is as followsmysql> insert into MultiplicationDemo values(10, 2); Query OK, 1 row affected (0.17 sec) mysql> insert into MultiplicationDemo values(4, 2); Query OK, ...
Read MoreLimit length of longtext field in MySQL SELECT results?
You can use SUBSTRING() from MySQL to limit length of strings. The syntax is as followsSELECT SUBSTRING(yourColumnName, 1, yourIntegerValueToGetTheCharacters) as anyVariableName from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table limitLengthOfLongTextDemo -> ( -> sentence LONGTEXT -> ); Query OK, 0 rows affected (0.74 sec)Insert some records in the table using insert command. The query is as followsmysql> insert into limitLengthOfLongTextDemo values('This is the introduction to MySQL'); Query OK, 1 row affected (0.17 sec) mysql> insert into ...
Read MoreSolve ERROR 1396 (HY000): Operation DROP USER failed for 'user'@'localhost' in MySql?
This error occurs when you drop a user with localhost while you have created a user with ‘%’.Let us create a user with ‘%’ and drop the user as a localhost. The syntax is as followsCREATE USER 'yourUserName'@'%' IDENTIFIED BY 'yourPassword';Let us create a user using the above syntax. The query to create a user is as followsmysql> CREATE USER 'Jack'@'%' IDENTIFIED BY '1234'; Query OK, 0 rows affected (0.26 sec)Check user is created successfully or notmysql> select user, host from MySQL.user;The following is the output+------------------+-----------+ | user | host | +------------------+-----------+ ...
Read MoreHow to set sql_mode permanently in MySQL?
If you are using Windows Operating System, check your directory my.cnf or my.ini file.mysql> select @@datadir;The following is the output+---------------------------------------------+ | @@datadir | +---------------------------------------------+ | C:\ProgramData\MySQL\MySQL Server 8.0\Data\ | +---------------------------------------------+ 1 row in set (0.00 sec)Reach the above location ‘C:\ProgramData\MySQL\MySQL Server 8.0\Data\”. The screenshot is as follows for my.cnf fileOpen the my.cnf file and write the sql_mode="TRADITIONAL". The syntax is as followssql_mode="TRADITIONAL".After that start your server once again.
Read MoreHow to select yesterday's date in MySQL?
To select yesterday’s date, use the subdate() function from MySQL. The syntax is as followsselect subdate(yourDatetimeColumnName) as anyVariableName from yourTableName;To understand the above syntax, let us create a tablemysql> create table YesterdayDateDemo -> ( -> VisitedDateTime datetime -> ); Query OK, 0 rows affected (0.59 sec)Let us now insert date in the table using insert command. The query is as followsmysql> insert into YesterdayDateDemo values(now()); Query OK, 1 row affected (0.15 sec) mysql> insert into YesterdayDateDemo values('2012-12-26 13:24:35'); Query OK, 1 row affected (0.17 sec) mysql> insert into YesterdayDateDemo values('2013-10-22 12:20:32'); Query OK, 1 row affected (0.16 sec)Let ...
Read MoreCreate a procedure in MySQL with parameters?
You can create a parameter using IN and OUT. IN is used to take input parameter and OUT can be used for output.The syntax is as followsDELIMITER // CREATE PROCEDURE yourProcedureName(IN yourParameterName dataType, OUT yourParameterName dataType ) BEGIN yourStatement1; yourStatement2; . . N END; // DELIMITER ;First, we will create a table. The query to create a table is as followsmysql> create table SumOfAll -> ( -> Amount int -> ); Query OK, 0 rows affected (0.78 sec)Insert some records in the table using insert command. The query ...
Read MoreWhat is the data type for unix_timestamp in MySQL?
The best data type for unix_timestamp in MySQL is integer. The integer data type is as followsint(11);The integer data type is useful for condition checking like ( > , create table UnixTime -> ( -> DueTime datetime -> ); Query OK, 0 rows affected (0.55 sec)Insert records in the form of date using insert command. The query is as followsmysql> insert into UnixTime values(now()); Query OK, 1 row affected (0.15 sec) mysql> insert into UnixTime values('2010-10-14'); Query OK, 1 row affected (0.15 sec) mysql> insert into UnixTime values('2020-09-24'); Query ...
Read MoreHow to add column values in MySQL without using aggregate function?
You can add column values without using aggregate function like sum(). For that, the syntax is as follows −SELECT *, (yourColumnName1+yourColumnName2+yourColumnName3, ....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 AddingColumnDemo -> ( -> StudentId int, -> StudentName varchar(20), -> MathMarks int, -> PhysicsMarks int, -> ChemistryMarks int -> ); Query OK, 0 rows affected (0.82 sec)Insert records in the table using insert command. The query is as follows −mysql> insert into AddingColumnDemo values(1, 'John', 35, ...
Read MoreHow to output MySQL query results in CSV format and display it on the screen, not a file?
To get the output MySQL query result in CSV format, use concat(). The syntax is as follows −mysql> select concat(StudentId, ', ', StudentName, ', ', StudentAge) as CSVFormat from CSVFormatOutputs;To understand the above syntax, let us create a table. The query to create a table is as follows−mysql> create table CSVFormatOutputs -> ( -> StudentId int not null auto_increment, -> StudentName varchar(20), -> StudentAge int, -> PRIMARY KEY(StudentId) -> ); Query OK, 0 rows affected (1.15 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into CSVFormatOutputs(StudentName, ...
Read MoreChanging year in MySQL date?
To change the year in MySQL date, you need to use DATE_FORMAT() function with UPDATE command. The syntax is as follows.UPDATE yourTableName SET yourDateColumnName = DATE_FORMAT(yourDateColumnName ,'yourYearValue-%m-%d');To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table ChangeYear -> ( -> id int not null auto_increment, -> ArrivalTime date, -> PRIMARY KEY(id) -> ); Query OK, 0 rows affected (0.83 sec)Insert some records in the table using insert command −mysql> insert into ChangeYear(ArrivalTime) values(date_add(now(), interval -2 year)); Query OK, 1 row affected, 1 warning ...
Read More