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 325 of 355
MySQL 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 MoreHow to increase varchar size of an existing column in a database without breaking existing data in MySQL?
Increase the varchar size of an existing column in a database with the help of CHANGE command. The syntax is as follows −alter table yourTableName change yourColumnName yourColumnName dataType;Here, we are creating a table with a single column and varchar size 200 −mysql> create table IncreaseVarcharDemo −> ( −> StudentId varchar(200) −> ); Query OK, 0 rows affected (0.60 sec)Now insert record in the table. The query is as follows −mysql> insert into IncreaseVarcharDemo values('John123'); Query OK, 1 row affected (0.16 sec)Displaying all records from the table with the help of the following query −mysql> select *from ...
Read MoreHow to add 30 minutes to datetime in MySQL?
To add minutes to a datetime you can use DATE_ADD() function from MySQL. In PHP, you can use strtotime().To add 30 minutes in MySQL, the DATE_ADD() function is as follows −select date_add(yourColumnName, interval 30 minute) from yourTableName;To use the above syntax, let us create a table. The following is the query to create a table.mysql> create table Add30MinutesDemo −> ( −> YourTime datetime −> ); Query OK, 0 rows affected (0.67 sec)Insert records in the table with the help of following query −mysql> insert into Add30MinutesDemo values(now()); Query OK, 1 row ...
Read MoreDelete more than one rows from a table using id in MySQL?
You can use IN statement to delete more than one rows from a table using id in MySQL. The syntax is as follows −delete from yourTableName where yourColumnName in(value1, value2, .....valueN);To understand the above syntax, let us create a table. The following is the query to create a table.mysql> create table DeleteManyRows −> ( −> Id int, −> Name varchar(200), −> Age int −> ); Query OK, 0 rows affected (3.35 sec)Insert some records in the table with the help of insert command. The query is as follows −mysql> insert into DeleteManyRows values(1, 'John', 23); ...
Read MoreReturning a value even if there is no result in a MySQL query?
You can use IFNULL() function from MySQL to return a value even if there is not result. Let us create a table. Te query to create a table.mysql> create table IfNullDemo −> ( −> Id int, −> Name varchar(100) −> ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table with the help of insert command. The query is as follows −mysql> insert into IfNullDemo values(1, 'John'); Query OK, 1 row affected (0.18 sec) mysql> insert into IfNullDemo values(200, 'Sam'); Query OK, 1 row affected (0.21 sec) mysql> insert into IfNullDemo ...
Read MoreDelete all the records from a MySQL table?
To delete all the records from a MySQL table, you can use the TRUNCATE statement.The syntax is as follows −TRUNCATE TABLE yourTableName;The above syntax deletes all the records from the table. To understand the above syntax, let us create a table. The following is the query to create a table −mysql> create table DeleteAllFromTable −> ( −> PersonId int, −> PersonName varchar(200) −> ); Query OK, 0 rows affected (0.67 sec)Insert some records in the table with the help of insert command.The query is as follows −mysql> insert ...
Read MoreMYSQL select DISTINCT values from two columns?
To select distinct values in two columns, you can use least() and greatest() function from MySQL.Let us create a table with two columns −mysql> create table SelectDistinctTwoColumns −> ( −> StudentId int, −> EmployeeId int −> ); Query OK, 0 rows affected (0.60 sec)Now you can insert records in the table. The query to insert records is as follows −mysql> insert into SelectDistinctTwoColumns values(100, 101); Query OK, 1 row affected (0.39 sec) mysql> insert into SelectDistinctTwoColumns values(102, 103); Query OK, 1 row affected (0.13 sec) mysql> insert into SelectDistinctTwoColumns values(104, 105); Query OK, 1 ...
Read MoreCan we use str_replace in MySQL?
The str_replace version in MySQL is the replace() function. Let us first create a table to understand the function −mysql> create table StringReplaceDemo −> ( −> Id int, −> URL varchar(200) −> ); Query OK, 0 rows affected (0.38 sec)Insert some records in the table with the help of insert command. The query is as follows −mysql> insert into StringReplaceDemo values(1001, 'https://www.google.co.in'); Query OK, 1 row affected (0.09 sec) mysql> insert into StringReplaceDemo values(1002, 'https://www.facebook.com'); Query OK, 1 row affected (0.11 sec) mysql> insert into StringReplaceDemo ...
Read MoreOrder strings by length of characters IN mYsql?
You can order by length of characters with the help of CHAR_LENGTH() function from MySQL. The function returns the number of characters i.e. 4 for the following string −AMITTo order strings by length of characters, the following is the syntax −select *from yourTableName order by CHAR_LENGTH(yourColumnName);To understand the above concept, let us first create a table. The following is the query to create a table −mysql> create table OrderByCharacterLength −> ( −> BookName varchar(200) −> ); Query OK, 0 rows affected (1.97 sec)Insert some records in the table with the help of insert command. The query is ...
Read More