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 286 of 355
Is 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 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 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 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 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 MoreHow to convert MySQL null to 0 using COALESCE() function?
You can use the COALESCE() function to convert MySQL null to 0SELECT COALESCE(yourColumnName, 0) AS anyAliasName FROM yourTableName;Let us first create a table. The query to create a table is as followsmysql> create table convertNullToZeroDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(20), -> Salary int -> ); Query OK, 0 rows affected (1.28 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into convertNullToZeroDemo(Name, Salary) values('John', NULL); Query OK, 1 row affected (0.20 sec) mysql> insert into convertNullToZeroDemo(Name, Salary) values('Carol', 5610); Query OK, 1 ...
Read MoreConvert number INT in minutes to TIME in MySQL?
To convert number INT in minutes to TIME in MySQL, you can use SEC_TO_TIME() function.The syntax is as followsselect SEC_TO_TIME(yourIntColumnName*60) 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 convertNumberToMinute -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> NumberToMinute int -> ); Query OK, 0 rows affected (0.79 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into convertNumberToMinute(NumberToMinute) values(60); Query OK, 1 row affected (0.12 sec) mysql> insert into convertNumberToMinute(NumberToMinute) values(70); Query ...
Read MoreHow to format number with "." as thousand separators, and "," as decimal separator?
You can use format() function for the separators. It will work in MySQL version greater than or equal to 5.5. We are using the version 8.0.12mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.12 | +-----------+ 1 row in set (0.00 sec)The syntax is as followsSELECT FORMAT(yourColumnName, valueAfterDecimalPoint, 'de_DE') AS anyAliasNamefrom yourTableName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table formatNumberDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Number DECIMAL(19, 1) ...
Read MoreSelecting Random Result from MySQL?
You need to use rand() function to select random result from MySQL.The syntax is as followsselect *from yourTableName order by rand() limit 1;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table selectRandomRecord -> ( -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentName varchar(20) -> ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into selectRandomRecord(StudentName) values('John'); Query OK, 1 row affected (0.17 sec) mysql> insert into selectRandomRecord(StudentName) values('Carol'); Query OK, ...
Read MoreMySQL UPDATE query where id is highest AND field is equal to variable?
The syntax is as followsupdate yourTableName set yourColumnName1=yourValue where yourColumnName2=yourValue order by yourIdColumnName DESC LIMIT 1;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table UpdateWithHighestDemo -> ( -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> UserStatus tinyint, -> UserRank int -> ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into UpdateWithHighestDemo(UserStatus, UserRank) values(1, 78); Query OK, 1 row affected (0.12 sec) mysql> insert into UpdateWithHighestDemo(UserStatus, UserRank) values(0, 118); Query ...
Read More