Featured
-
No Featured Found!
Tags
Trigger in MySQL
Here, I have created a small database for a book store application. In which two tables are required:-
book:- Stores a unique book ID, the title, content, and a deleted flag.
audit_book:- Stores a set of historical changes with a record ID, t...
How to add a column after a particular column in MySQL?
Sometimes we need to add a column after a particular column. We can do this easily by using ALTER command with AFTER attribute.
Example: in the below command I'm adding "latitude" column after "address" column where "address" column already ex...
How to add comment to a column in MySQL?
Sometimes wee need to add a comment to a column for understanding purpose means to easily identify what is the use of that particular column.
We can add comment to a column by two ways- at the time of table creation or by ALTER command after ...
How to make a column as first column in MySQL?
Sometimes we need to make existing column as first column. For example you created a table and added column "id" after creating the column. Now you want to make "id" column as first column, then you can do this easily by using ALTER command as be...
How to add foreign key contraint to a column in MySql?
In many cases we need to create foreign key in a table. We can do this at the time of table creation and after the table creation by ALTER command.
1- At the time of table creation:
CREATE TABLE user_device (
id int(11) NOT NULL AUTO...
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...
How to add comment on a table in MySQL?
Sometimes wee need to add a comment on a table for more readability means to define what is the use of that table.
We can do this at the time of table creation or by ALTER command after creating the table.
At the time of table creation:
...
Datetime vs Timestamp in MySQL and PHP
Hello Readers,
This blog tell the difference between the Datetime vs Timestamp in MySQL and PHP.
1> Datetime and Timestamp are used to store datetime and then retrieve the datetime back.
2> Basically, In this we have a 2 factor whi...
How to create date column in MySQL to take current date automatically when row inserted
The TIMESTAMP data type is the only data type which is used to have MySQL automatically set the time when a row is inserted and/or updated. DATETIME columns cant do this.
We can define more than one TIMESTAMP column in a table, but only one TI...
How to speed up the mysql Query
Hello Readers,
If you want to speed up the mysql query or optimize the query:
Below are some points to follow while writing the mysql query:
Always every table must have a Primary Key. Primary key is very essential for mysql operation...
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...
How to get distance by mysql query
If you would like to get distance between two places with mysql query. You can use below query to find air distance between two places.
Example :: Suppose you want to get air distance between all employee's home and office location.
To run th...
MySQL Storage Engines
MySQL comes with a variety of storage engines. Each and every storage engine has some particular behavior and traits. It is very much important to know about these engines in order to create a well designed database.
MySQL stores each database(a...
Indexed full text search in PHP-MySQL (PART - 2)
We already discussed Natural full text search in Indexed full text search in PHP-MySQL (PART - 1).
Lets look into Boolean Full Text Search.
In this we can use various operators to change the behavior of search. To specify a search to be b...
Stored Procedures and Functions in MySQL- Part 6
Loops
A lot many times, situation arises that a set of commands have to be repeatedly executed. This is achievable by using looping constructs. MySQL provides us three types of looping constructs :
Simple Loops
Repeat Loops
While L...
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...
Stored Procedures and Functions in MySQL- Part 4
Conditional Constructs - CASE
As in any programming constructs even in stored routines we might need to execute different commands based on what the variable or expression evaluates to. For the same we have CASE.... END CASE and IF...ELSEIF......
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...
Exporting table from Amazon RDS into a csv file
A simple command to export tables from Amazon RDS into a csv file
mysql -uroot -ptest123 --database=test -e "select concat(id,',',login) FROM users" > userdata.csv
This command will simply connect to mysql from terminal using root as u...
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...
Stored Procedures and Functions in MySQL - Part 2
How to manage stored routines?
In the second part of the series we will see basic commands used to manage the Stored Routines
As these are also Database objects so the basic DDL commands hold true here also
Create
Alter
Drop
CRE...
Stored Procedures and Functions in MySQL- Part 1
Why do we need stored routines?
In this multi-part series I will explain the need of stored routines commonly know as stored procedures and functions , how to manage them, how to declare variables, programming blocks (loops, how to perform con...
How to create a trigger in MySQL
Many a times, we have come across a need that execution of DML statement on one database table should lead to modification in another table with some specific set of values. Whats the novice way to do? Simple, fire the first modification query th...
Top 10 MySQL queries every developer should know
As developers of any technology which uses MySQL as backend we always need to know some basic queries to work on. The list may vary as per the development need but a basic set still remains the same.
The very first query that comes into use...
How to optimize and repair all databases and tabels in mysql?
Optimization of database tables in mysql is a methodology which must be done frequently to make your database healthy.
As per mysql manual,
OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made ...
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...
Change the collation of database, tables and columns
If we have added wrong collation name of the database and its columns the we can get some problem with other languages and special character.
All other languages and characters will appear as ?????
So we can alter collation of database and tabl...
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 ...
Indexing in Mysql
Indexing is useful for faster searching of data. Suppose you have a product table in your database with more than 2,00,000 records, now when you search a record on basis of product name, it takes time too long. Now if you want to perform your sea...
Date base search in Mysql
Hello Friends,
Generally we need to do date based search in mysql like Get all the users registered in the last week, or last day, last month, last year. We have very good function of mysql DATE_SUB(). Lets get some the example below:
1) Se...
How to Run Mysql Server on Safe Mode?
MySQL is an open source database software which is used for storing data. Some time we need to run MySQL Server on safe mode. Please follow the below commands one by one to run MySQL server on safe mode.
1) First of all you have to stop MySQL ...
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint
This error generally comes when we want to delete child table row. To solve this problem we have to disable the
foreign key checks.
SET FOREIGN_KEY_CHECKS=0;
Now you can fire your query.
DELETE FROM TABLE WHERE ID = 'Somthing';
You can ena...
Full Text Search
Full Text Search is a way to provide faster search on your website. Suppose you creating a website for a newspaper where lots of article written on various subject and now you want to fetch all news related to word Modi. Full text search is ideal...
Another mysql daemon is already running with the same unix socket
Another mysql daemon is already running with the same unix socket
You got the above message from server, when are trying to restart the MySql from command promt. SO, to avoid the above error and start the MySql follow the below steps -
1 -...
Mysql Function To Rounding number to nearest 1,10,100,1000
Below is the Mysql Function To Rounding number to nearest 1,10,100,1000
DELIMITER $$
DROP FUNCTION IF EXISTS `rounding_to_nearest`$$
CREATE FUNCTION `rounding_to_nearest`(num decimal(15,2),round_to...
ERROR: Error 1005: Can't create table (errno: 121)
Sometimes we get this error on creating table in MySQL.This error will come when we use the constraint with the same name that is already used somewhere else.
If the table you're trying to create includes a foreign key constraint, and you've p...
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...
Indexes in MySQL
Indexes in MySQL can improve performance of specific queries Like as below Examples :
Syntax for creating index on one field:
CREATE INDEX indx_magrno ON EmplyeTable (EmplyeID);
These two indexes is supposed to hugely improve performan...
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...
How to enable slow query logs
For this we need to edit my.cnf file (This should be in /etc directory)
we need to enter following lines :-
log-slow-queries=/logs/mysql/slowQueries.log
long_query_time=30
This time is in seconds , so if any query wi...
How to use regular expression in mysql query?
By using REGEXP function of mysql.Below is the example via a MySQL Query
select empId,empName from tbl_employee where empName REGEXP [1-4]$;
This will select the data of all employees whose name ends with either 1 or 2 or 3 or 4
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:...
Not able to add Foreign key on table column
Error Code : 1005
Can't create table 'DatabaseName.#sql-9cc_1' (errno: 150)
(0 ms taken)
After searching a lot i have got solution for the above problem while i was adding FK to a column.
The error was because of different MySQL engine. The...
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...
Difference between Decimal,Float,Double in terms of exact calculation
Difference between float,double and decimal in terms of exact calculation(Tested in mysql)
I was creating an application where I need exact values from mysql procedure so I have to find better data type which gives me exact value so I tested i...
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...
How to search records with maximum and minimum limit in MySQL
DELIMITER $$
DROP PROCEDURE IF EXISTS GetCustomerSearchResult$$
CREATE DEFINER=root@% PROCEDURE GetCustomerSearchResult(
criteria INT
,searchValue VARCHAR(500)
,MinLimit INT
,MaxLimit INT
)
BEGIN
CASE
WHEN...
