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
MySQLi Articles
Page 272 of 341
MySQL query to calculate the average of values in a row?
To calculate the average of values in a row in MySQL, use the following syntaxSELECT (yourTableName.yourColumnName1+yourTableName.yourColumnName2+yourTableName.yourColumnName3+, ..........N)/numberOfColumns 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 calculateAverageDemo -> ( -> x int, -> y int, -> z int -> ); Query OK, 0 rows affected (1.41 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into calculateAverageDemo values(10, 20, 30); Query OK, 1 row affected (0.78 sec) mysql> insert into calculateAverageDemo values(40, 50, 70); Query ...
Read MoreHow to determine the current delimiter in MySQL?
To determine current delimiter in MySQL, use the following syntax\sThe above syntax will let you know about the current delimiter. Let us implement the above syntax.The query is as followsmysql> \sThe following is the output-------------- C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe Ver 8.0.12 for Win64 on x86_64 (MySQL Community Server - GPL) Connection id: 19 Current database: sample Current user: root@localhost SSL: Cipher in use is DHE-RSA-AES128-GCM-SHA256 Using delimiter: ; Server version: 8.0.12 MySQL Community Server - GPL Protocol version: 10 Connection: localhost via TCP/IP Insert id: 11 Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 TCP port: ...
Read MoreWhy should we use MySQL CASE Statement?
Use MySQL CASE for a fixed number of arguments.The syntax is as followsSELECT *, CASE WHEN yourColumName1>yourColumName2 THEN 'yourMessage1' ELSE 'yourMessage2' END 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 CaseFunctionDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Value1 int, -> Value2 int -> ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into CaseFunctionDemo(Value1, Value2) values(10, 20); Query OK, 1 row affected ...
Read MoreMySQL update a column with an int based on order?
The syntax is as follows to update a column with an int based on orderset @yourVariableName=0; update yourTableName set yourColumnName=(@yourVariableName:=@yourVariableName+1) order by yourColumnName ASC;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table updateColumnDemo -> ( -> Id int, -> OrderCountryName varchar(100), -> OrderAmount int -> ); Query OK, 0 rows affected (1.76 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into updateColumnDemo(Id, OrderCountryName) values(10, 'US'); Query OK, 1 row affected (0.46 sec) mysql> insert into updateColumnDemo(Id, OrderCountryName) ...
Read MoreGet all MySQL records from the previous day (yesterday)?
To get the records from the previous day, the following is the syntaxselect *from yourTableName where date(yourColumnName)= DATE(NOW() - INTERVAL 1 DAY);To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table yesterDayRecordsDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> ArrivalDateTime datetime -> ); Query OK, 0 rows affected (0.44 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into yesterDayRecordsDemo(ArrivalDateTime) values('2014-11-09 13:45:21'); Query OK, 1 row affected (0.11 sec) mysql> insert into yesterDayRecordsDemo(ArrivalDateTime) values('2017-10-19 11:41:31'); Query ...
Read MoreCompare only day and month with date field in MySQL?
You can compare only day and month with date field in MySQL with the help of DATE_FORMAT().The syntax is as followsselect *from yourTableName WHERE DATE_FORMAT(yourColumnName, '%m-%d') = DATE_FORMAT('yourValue', '%m-%d') and yourCondition;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table compareDayAndMonthDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> compareDayAndTime date -> ); Query OK, 0 rows affected (0.49 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into compareDayAndMonthDemo(compareDayAndTime) values('2014-01-31'); Query OK, 1 row affected (0.20 ...
Read MoreIs it possible to delete everything after a 'space' in a MySQL field?
In order to delete everything after a space, you need to use SUBSTRING_INDEX().The syntax is as followsselect substring_index(yourColumnName, ' ', 1) 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 deleteAfterSpaceDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentName varchar(100) -> ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into deleteAfterSpaceDemo(StudentName) values('John Smith'); Query OK, 1 row affected (0.15 sec) mysql> insert into deleteAfterSpaceDemo(StudentName) ...
Read MoreHow to add a where clause in a MySQL Insert statement?
You need to use UPDATE statement for this.The syntax is as followsupdate yourTableName set yourColumnName1=yourValue1, yourColumnName2=yourValue2, ....N where yourCondition;Let us create a table for our examplemysql> create table addWhereClauseDemo -> ( -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentName varchar(30), -> StudentPassword varchar(40) -> ); Query OK, 0 rows affected (0.45 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into addWhereClauseDemo(StudentName, StudentPassword) values('John', 'John123456'); Query OK, 1 row affected (0.14 sec) mysql> insert into addWhereClauseDemo(StudentName, StudentPassword) values('Carol', '99999'); Query OK, 1 row affected (0.24 sec) mysql> insert ...
Read MoreMySQL count(*) from multiple tables?
To achieve this for multiple tables, use the UNION ALL.The syntax is as followsselect sum(variableName.aliasName) from ( select count(*) as yourAliasName from yourTableName1 UNION ALL select count(*) as yourAliasName from yourTableName2 ) yourVariableName;Let us implement the above syntax. Here, I am using the sample database which has more tables.The two tables we are using areuserdemowheredemoHere is the query to display all records of both the tables. The query is as follows to display records from table ‘userdemo’.mysql> select *from userdemo;The following is the output+--------+----------+------------------+ | UserId | UserName | RegisteredCourse | +--------+----------+------------------+ | 1 ...
Read MoreHow to update date of datetime field with MySQL?
Update date of datetime field with the help of arithmetic operator minus(-).The syntax is as followsupdate yourTableName set yourDateTimeColumnName=yourDateTimeColumnName - interval yourValue day where date(yourDateTimeColumnName)=’yourDateValue’;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table updateDateDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> ArrivalDate datetime -> ); Query OK, 0 rows affected (0.66 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into updateDateDemo(ArrivalDate) values('2011-01-13'); Query OK, 1 row affected (0.19 sec) mysql> insert into updateDateDemo(ArrivalDate) values('2013-04-21'); ...
Read More