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 324 of 355
MySQL 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 MoreHow to rearrange MySQL columns?
To rearrange the MySQL columns, check the column arrangement with the help of show create command. The syntax is as follows −show create table yourTableName;The syntax to rearrange the MySQL columns is as follows −alter table yourTableName change column yourColumnName yourColumnName dataType firstFor the same purpose, you can use the after keyword. The syntax is as follows −alter table yourTableName change column yourColumnName yourColumnName dataType after yourSpecificColumnName;Let us first check the column arrangement for the already created table “AddColumn” −mysql> show create table AddColumn; The following is the output −+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table ...
Read MoreWhich MySQL type is most suitable for "price" column?
The best type for price column should be DECIMAL. The type DECIMAL stores the value precisely.For Example - DECIMAL(10, 2) can be used to store price value. It means the total digit will be 10 and two digits will be after decimal point.To understand the type DECIMAL, let us create a table.mysql> create table PriceDemo −> ( −> ProductPrice DECIMAL(10, 2) −> ); Query OK, 0 rows affected (0.60 sec)Now insert some records in the table in the form of price. The query to insert records is as follows −mysql> insert into PriceDemo values(12345.67); Query OK, 1 row ...
Read MoreMySQL query to extract last word from a field?
To extract last word from a field, use in-built SUBSTRING_INDEX() function. The syntax is as follows −SELECT SUBSTRING_INDEX(yourColumnName, ’ ‘, -1) 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 FirstWordDemo −> ( −> AllWords longtext −> ); Query OK, 0 rows affected (0.83 sec)Now insert some words in the table using insert command. The query is as follows −mysql> insert into FirstWordDemo values('This is the first MySQL Query'); Query OK, 1 row affected (0.11 ...
Read MoreHow to resolve the error that occurs while using a reserved word as a table or column name in MySQL?
This error occurs when you try to use a reserved word as a table or column name. It can occur due to −Case 1: Whenever you use reserved word as a table name −mysql> create table insert −> ( −> Id int −> );The error is as follows −ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert ( Id int )' at line 1The above error occurred because the word ‘insert’ is a keyword in MySQL.Case 2 − Whenever you ...
Read More