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 310 of 341
Group month and year in MySQL?
You can group month and year with the help of function DATE_FORMAT() in MySQL. The GROUP BY clause is also used.The syntax is as follows −SELECT DATE_FORMAT(yourColumnName, '%m-%Y') from yourTableName GROUP BY MONTH(yourColumnName), YEAR(yourColumnName)DESC;To understand the above concept, let us create a table. The following is the query to create a table −mysql> create table GroupMonthAndYearDemo −> ( −> DueDate datetime −> ); Query OK, 0 rows affected (1.49 sec)Insert records in the table using the following query −mysql> insert into GroupMonthAndYearDemo values(now()); Query OK, 1 row affected (0.11 sec) ...
Read MorePerform MySQL delete under safe mode?
To delete under safe mode, you can use the below query −SET SQL_SAFE_UPDATES = 0;To understand the above query, let us create a table. The following is the query to create a table −mysql> create table SafeDeleteDemo −> ( −> Price int −> ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table with the help of insert command. The query is as follows −mysql> insert into SafeDeleteDemo values(100); Query OK, 1 row affected (0.11 sec) mysql> insert into SafeDeleteDemo values(200); Query OK, 1 row affected (0.19 sec) mysql> insert into SafeDeleteDemo ...
Read MoreHow to count distinct values in MySQL?
To count distinct values, you can use distinct in aggregate function count().The syntax is as follows −select count(distinct yourColumnName) 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 DistinctDemo −> ( −> Name varchar(200) −> ); Query OK, 0 rows affected (0.58 sec)For our example, let us insert duplicate records in the table. The query to insert records is as follows −mysql> insert into DistinctDemo values('John'); Query OK, 1 row affected (0.15 sec) mysql> ...
Read MoreUsing group by on two fields and count in MySQL?
To implement GROUP BY on two fields and count, let us create a table. The following is the query to create a table −mysql> create table GroupByTwoFieldsDemo −> ( −> Id int, −> Name varchar(200) −> ); Query OK, 0 rows affected (0.53 sec)Let us insert some records in the table −mysql> insert into GroupByTwoFieldsDemo values(1, 'John'); Query OK, 1 row affected (0.15 sec) mysql> insert into GroupByTwoFieldsDemo values(10, 'Johnson'); Query OK, 1 row affected (0.16 sec) mysql> insert into GroupByTwoFieldsDemo values(9, 'Carol'); Query OK, 1 row affected (0.14 sec) mysql> insert into ...
Read MoreMySQL query to return all records with a datetime older than 1 week
To get dates older than 1 week, you can use the following syntax −select *from yourTableName where yourColumnName < now() - interval 1 week;To understand the above concept, let us create a table. The query to create a table is as follows −mysql> create table DatesOfOneWeek −> ( −> ArrivalTime datetime −> ); Query OK, 0 rows affected (0.87 sec)Insert some records in the table −mysql> insert into DatesOfOneWeek values(date_add(now(), interval 2 week)); Query OK, 1 row affected (0.11 sec) mysql> insert into DatesOfOneWeek values('2018-11-04'); Query OK, 1 row affected ...
Read MoreCreate a table in MySQL that matches another table?
To create a table in MySQL that matches with another table, use CREATE TABLE command with LIKE operator. The syntax is as follows −create table yourNewTableName like yourOldTableName;The above syntax creates structure of the table.If you want all records then use INSERT INTO…...SELECT *FROM command. The syntax is as follows −insert into yourNewTableName select *from yourOldTableName.I have an old table and some data −mysql> create table WholeWordMatchDemo −> ( −> Words varchar(200) −> ); Query OK, 0 rows affected (0.84 sec)First, we will create a table structure. The query is as ...
Read MoreMySQL Select Date Equal to Today and return results for the same date?
To get today’s date, use in-built function CURDATE(). The CURDATE() gives only current date not time. With that, to get the records for the same day, you can try the following syntax −select yourColumnName1, yourColumnName2, ......, yourColumnNameN, DATE_FORMAT(yourDateColumnName, '%Y-%m-%d') from yourTableName WHERE DATE(yourDateColumnName) = CURDATE();To understand the above concept, let us create a table. The query to create a table is as follows. One of these columns will have datetime datatype to display dates −mysql> create table GmailSignIn −> ( −> UserId int, −> UserName varchar(200), −> DateOfSignIn ...
Read MoreGet all characters before space in MySQL?
In order to get all characters before space in MySQL, you can use left() function from the MySQL. The syntax is as follows −select left(yourColumnName, LOCATE(' ', yourColumnName) - 1) 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 AllCharacterBeforeSpace −> ( −> FirstNameAndLastName varchar(200) −> ); Query OK, 0 rows affected (0.51 sec)Now you can insert some records in the table.The query to insert records is as follows −mysql> insert into AllCharacterBeforeSpace values('John Smith'); Query ...
Read MoreHow to create a Cumulative Sum Column in MySQL?
To create a cumulative sum column in MySQL, you need to create a variable and set to value to 0. Cumulative sum increments the next value step by step with current value.Firstly, you need to create a variable with the help of SET. The syntax is as follows −set @anyVariableName:= 0;The syntax to create a cumulative sum column in MySQL is as follows −select yourColumnName1, yourColumnName2, ........N, (@anyVariableName := @anyVariableName + yourColumnName2) as anyVariableName from yourTableName order by yourColumnName1;To understand the above concept, let us create a table. The following is the query to create a table −mysql> create table ...
Read MoreStrip last two characters of a column in MySQL?
You can strip last two characters with the help of SUBSTRING() and CHAR_LENGTH() methods. The syntax is as follows −select yourColumnName, SUBSTRING(yourColumnName, 1, CHAR_LENGTH(yourColumnName) - 2) AS anyVariableName from yourTableName;To understand the above syntax, let us create a table −mysql> create table LastTwoCharacters −> ( −> Words varchar(200) −> ); Query OK, 0 rows affected (0.71 sec)Now you can insert some records in the table with the help of select statement. The query to insert records is as follows −mysql> insert into LastTwoCharacters values('Hellooo'); Query OK, 1 row affected (0.23 sec) ...
Read More