Featured
-
No Featured Found!
Tags
How to count unique values from table in mysql?
Sometimes we have the requirement to count how many unique values are in the table. We can do this by using "Distinct" and count().
SELECT COUNT(DISTINCT column_name) FROM table_name;
Example: Suppose you have a table user from which you ...
How to check indexes for a database or table in mysql ?
There are chances that you want to check for the indexes that you created on the tables in past because there can be a large number of indexes on numerous of tables depending on the size of the database
If you want to see the indexes for a par...
Mysql order by values within IN() function
Hello guys,
Most of time we face the problem order by within IN() values . Mysql provides the function FIELD(), It will sort fetched IN() data according to requirment . The FIELD function returns the position of the first string in the remaining...
Cloning a MySQL database on the same MySql instance
Hi Guys,
There are chances when we need to have a backup of the old database as a separate database so that if we make some experiments we should have the old database as a safe database which we can use again in the same state. So in mysql we...
MYSQL, PHP :- Query Concatenating Fields To Make Date And Running Between Query On It
Recently, I faced a problem when the date was saved in different fields month, year and date. I need to club all these fields together and run a between query on it.
Another issue which was there was that Date has '0' contacted if the value ...
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...
Mysql and Sql injections
SQL Injection:
If we are trying to save data into the database from webpage inputs than we have forget wide open security issue is known as SQL injection. Now the Question is how to prevent it and help to secure your script and MYSQL statement.
...
How to change default Character set of Schema?
Sometimes we need to change default Character set of Schema. We can do this easily by using ALTER command.
Command:
ALTER SCHEMA database-name DEFAULT CHARACTER SET utf8 ;
Example:
ALTER SCHEMA `jeeyoh` DEFAULT CHARACTER SET utf8 ;...
How to export Schema without data in MySQL?
Sometimes we need to export Schema without data means table structure only. We can do this easily by providing "--no-data" option in mysqldump command.
Command:
mysqldump -u root -p --no-data database_name > database.sql
Exa...
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 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:
...
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...
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 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......
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 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 ...
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...
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...
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...
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
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...
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...
