Featured
-
No Featured Found!
Tags
Discussion on insert data using replace statement in Mysql
Hi Reader's,
Welcome to FindNerd, today we are going to discuss insert data using replace statement in Mysql
Basically REPLACE INTO Statement is used for inserting a row in a table. If we want to replaces any record form our table then fi...
How to use LIKE operator in MySQL
Hi Reader's,
Welcome to FindNerd, today we are going to discuss how to use LIKE operator in MySQL?
Basically, LIKE operator is used for searching for a specified pattern in a column. So in other words we can say that the LIKE operat...
What is process to use Limit clause in MySQL Query ?
Welcome to FindNerd, today we are going to discuss what is process to use Limit clause in MySQL Query.?
Basically the LIMIT clause is used to specify the number of records to return.
In the MySQL LIMIT clause is used to limit the numb...
What are Mysql Indexes
A database index is a structure that improves the speed of retrieval of data from our table. Index is similar to the index of a book. If you want to find a chapter you look in the index first without scanning the whole page. Users cannot see th...
Use of IN operator and BETWEEN operator in MySQL
MySQl IN operator facilitates us to match a column's value against multiple values in one go.
As most of us are aware that we use WHERE clause for giving any condition in SELECT, INSERT, UPDATE, DELETE statements, so whenever we ...
How to get a list of MySQL user accounts
Get MySQL user accounts
Hello friends, I am using MySQL and I want to list user accounts. So to do this I followed the below process:
This can be done by writing the following query:
SELECT User FROM mysql.user;
...
What is the use of IFNULL Operator in MySQL?
In MySQL, the IFNULL operator is used as conditional statement for NULL values. When we need to fetch NULL values with LIKE operator then we can use this operator.
Example:
We have a table "employee" as below:
...
How to make WHERE col LIKE '%' select NULL values too?
When we apply LIKE keyword to any query then it doesn't return NULL values, and sometimes it required to return NULL values as well. We can do this by using IS NULL or IFNULL operator.
Example:
We have a table "employee" as be...
How to update multiple row in single mysql query
Hello friends,
Most of time we need to update multiple row at a time or in single mysql query, So most of developer use iterate the update query and execute many time. This is not good practice. We should use syntax given as below.
For Exam...
How to use the mysql result for the sepecific values
Hello Reader's if you are learning, optimizing the mysql query then in this blog you will know some new syntax offered by Mysql where you can make your query fast. Lets see the examples as below:-
In the code below, the results select...
Mysql: Compare two columns word and print total common words count
I am posting this blog because I was facing issue to show the total common counts in 3rd column from two columns. This is following table which contain these records:
|==========title=================|======title================|
|...Cat Do...
Temporary tables
MySQL Temporary tables:
A temporary table allows a user to store temporary records and we can use these records several times until the session does not expire. They are useful in those situations when it is expensive to write a query havin...
Rolling Back A Transaction in MySql
Transactions are very important part of MySql and for handling the transactions the Transaction Control Language (TCL) is used. Transactions are basically used to handle all the changes made in the database.
Rolling back a transaction means stor...
How change date format using MySql
Hello Reader's if you are using unix timestamp for storing date the time. Then you have to convert them into date format. But now MySql has come with a new syntax of 'format', Because of this it will make the conversion as you want and will save...
How to use top in Mysql
Hello Reader's if you want to use top in your MySql query then this blog is very helfull to you.
Lets see how to use top clause in MySql in different different location.
Suppose if you want to get only first 5 records of the table, Then you c...
What is the use of DISTINCT statement in MySQL.
In MySQL, the SELECT DISTINCT statement is used to return only distinct values means if in a table, a column has duplicate values then SELECT DISTINCT statement will be used to get different values.
SELECT DISTINCT Syntax
SELECT DISTINCT co...
Inserting data using replace statement
REPLACE INTO Statement:
It is used to insert a row in a table.It is similar to INSERT INTO statement but it does not allow duplicate row as it replaces the old one with the new one.
Syntax:
REPLACE INTO tablename (column1, column2, col...
How to pass mulitple values to update records with a single query
Hello Readers if you want to update mulitple record by multiple id, then you have to use in clause in MySql
Lets see the example as below:-
$ids = array(474,25, 99,101);
Here I have an array $ids with multiple id's. Now this array w...
Handling Duplicates using INSERT IGNORE
INSERT IGNORE:
INSERT IGNORE is used for handling duplicacy in a table as it ignores the query if the data is already present in the table and if the data does not exist then new row will be inserted.
Syntax:
INSERT IGNORE INTO tablename...
What is the join in MySQL ?
Welcome to findnerd, today we are going to discuss to join in MySQL.
Firstly let know what is join ?
An SQL JOIN clause is used to combining two or more tables and return output in single table.
So in simple word we can say that MySQL ...
Pattern Matching using REGEXP
REGEXP:
It is similar to LIKE which is used to fetch data based on regular expression from a table.
Syntax:
SELECT columnname(s) FROM tablename WHERE columnname REGEXP pattern;
Example 1:
SELECT name FROM Employee WHERE name R...
What is the mysql Constraints?
Welcome to FindNerd,
Today we are going to discuss about MySQL Constraints.
Firstly let know what is Constraints in MySQL ?
Constraints:- Constraints are the utilities that restrict ingression of data into the table if the designated co...
Use of Transaction in DBMS
A transaction is any unit of work done against the database.A transaction can be used to save the changes made to the database, it can be used to rollback the database to any save point.
For example say if you are creating or updating a record...
How to reset the Auto increment in mysql
Hello Reader's! If you already using the Mysql then you have seen the ID with autoincrement always add up. Forevery new entery the ID will increase even if you drop the table.
So if you want to reset this value you just have to run the mysql cod...
How to use IN statement using array in Mysql
Hello reader's! If you have an array with multiple values to match with database coloumn. Then you can use IN statement in mysql but you have to explode the array first.
let's see the exampkle below:-
$findIN= implode(',', $YourArray); // fir...
Syntax error due to reserved names in Mysql
Hello Reader's!, On using Mysql with the some special resereved keywords the error :-
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 <Keyword>
It...
How to insert if not exists in Mysql
If you want to enter a new records that is not exits then you can use Mysql two methods
Method 1 'REPLACE' :-
REPLACE INTO `users`
SET `userid` = 65,
`username` = 'abc',
`useremail` = 'abc@gmail.com';
In this case if record exists...
How to import mysql database using command line?
Sometimes when you have large databases files and you are unable to import the database manually. Then the easiest (and fastest) way is to use command line.
Import:--
1. Run the cmd (DOS) and get into the mysql folder, which in my case wo...
How to delete duplicate rows in a table set MySql
Hello readers!. If you having the much of redundant/duplicate data growing in your database you can delete it with just a single query as I wrote here.
Let say I have a table Address and want to delete the all the duplicate records whose city...
How to add a column with comment in MySql?
Sometimes we need to add a column with comment. We can do this easily by using COMMENT attribute in alter command.
Ex- Find the below alter command which will add "address" column to "user" table after "country" column.
ALTER TABLE `user` A...
Control Flow Functions in MySQL
Mysql have several contral flow functions. Such as
1) IF
2) CASE
3) IFNULL
4) NULLIF
Control Flow functions returns value based on processed each row by query executed.
Contral Flow function can be used on **SELECT, WHERE, GROUP BY** a...
Stored Procedures and Functions in MySQL- Part 5
Conditional Constructs - IF...ELSEIF..ELSE
This construct is used to evaluate some complex conditions and based on the results execute different set of statements.
The basic syntax is
IF <conditions> THEN
&n...
Indexed full text search in PHP-MySQL (PART - 1)
We need to write search queries every now and then in our projects. This is one of the most common tasks a PHP developer has to do. Most of the developers still use wildcard queries for this purpose which is very slow in searching records. Howeve...
Stored Procedures and Functions in MySQL - Part 3
Parameters in Stored Routines
While executing any stored routine sometimes we need to pass on certain values in stored procedures these values are handled as Parameters. Their declaration consists of three parts :
Type of parameter
Par...
Case Sensitive MYSQL select query
Case Sensitive SQL query
A select query does not performs case-sensitive query which means if the user name is pravesh, Pravesh, PRAVESH then it will select all users using simple select. Below example of normal mysql select query -
SELECT...
Crosstab Query Sample
To convert the rows to column in MySQL
Sample query:
select column1, count((case when (column2 = 'M') then 0 end)) AS `males`, count((case when (column2 = 'F') then 0 end)) AS `females`,count(0) AS `Total` from table_name group by ...
Working with triggers in mySql Database
A trigger is SQL code which runs just before or just after an
INSERT, UPDATE or DELETE event occurs on a particular database table.
Creating a Trigger:
Consider we have two tables:
CREATE TABLE `blog` (
`id` mediumint(8) unsigned...
How to set default value of column with datatype varchar to empty string
We usually face this kind of issue when we are doing programing with respect to a column with varchar data-type. If we don't add any value to a particular column it takes it as NULL. Thus forcing the developer to check for NULL every-time other t...
Using Putty and SSH Tunnel to connect to MySQL on remote server from SQLYog
To connect to a remote MYSQL server using SQLYog you can create SSH tunnel using putty following the steps.
Connect to the remote server using putty.
.
Right Click on putty window and select the change settings option.
...
Set up remote access to MySQL database on Ubuntu
Without database any application is almost redundant. So this post might help you to setup access to MySQL database on Ubuntu.
Firstly you need to open up the MySQL server config file present at
/etc/mysql/my.conf
and search for the lin...
Find MYSQL server version information
To find the version information of MYSQL server from command line log into MYSQL server using command
mysql -h hostname -uusername -p
You would be prompted for the password for the user on the server type the same and press Enter key.
...
How to Work with a Stored Procedure
How to Work with a Stored Procedure
DELIMITER //
CREATE PROCEDURE `p2` ()
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'A procedure'
BEGIN
SELECT 'Hello World !';
END//
The four characteristics of a procedure are:...
Known bug of Temporary Table in MYSQL
Known Bug
We cannot use temporary table twice in same query.
http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html
However if you have not required above condition then Temporary table is best to use rather than creating rea...
Delete duplicate row in table
You can delete the duplicate row without using the temporary table. You can use the self join concept.
Suppose we have person table contain 3 columns named id, name, and city.
INSERT INTO person(id,name,city) VALUES
(1,'a', 'aa'),
(2,'b','b...
Use of Find in set() function in mysql
In some cases, we use find_in_set() instead of in() in mysql. Following is an example, where we should use find_in_set() instead of in() :-
Suppose we have a table 'flashdata':-
fid productId
1 75,73
2 72...
