Featured
-
No Featured Found!
Tags
MySQL join across three tables
To implement joins(inner join,outer,full) in three tables you need to execute following MySQL statements:
select * from
table1 as t1
join table2 as t2 on condition
join table3 as t3 on condition
Example:
Suppose we have a database pr...
Count total by current year of Current month in mysql
To count total by current year and current month you need to execute the following MySQL statement:
Select * from tablename where year(columnname)=year(now()) and month(columnname)=month(now())
Example: Suppose we have a database product ...
Only day and month base search in mysql
If you are looking to make search based on day and month and wants to neglect year like you want to search all records between 2 March to 10 September under any year.
$schFrm = '2015-03-02'; //You can specify any year like 2015, 1980, 2005 etc...
Display Table Structure in MySQL
To display a table structure in MySQL first of all you need to go inside the database using following MySQL statement:
use databasename;
To display the table structure you need to execute the following MySQL statement:
describe tablena...
How to find out current user, current date and version in MySQL?
To find out the current user you need to execute the following MySQL statement:
SELECT USER();
To find out the current date you need to execute the following MySQL statement:
SELECT CURDATE();
To find out the version of MySQL you ...
How to login MySql using Unix shell?
Yo can login to MySQL using following command:
mysql -h hostname -u <UserName> -p <password>
Suppose you are login with hostname as 'localhost ', Username as 'root' and password as 'root'. Then the command will be as follows:
...
Drivers in MySQL
Drivers enables the developers to build database applications in their language of choice. Drivers available in Mysql:
PHP Driver:The MySQL native driver for PHP is a replacement for the MySQL Client Library for the PHP script language.
JD...
MySQL BETWEEN Operator
BETWEEN operator is used to test an expression within a range. Syntax of BETWEEN operator is as follows:
expr (NOT) BETWEEN begin_expr AND end_expr
-Here expr, begin_expr and end_expr is an expression which must return values.
-BETWEEN...
MySQL subqueries examples
Subqueries are generally similar to Normal queries in databases like -
They may return one column
They may return more column
They may return single rows
They may return multiple rows
Or it can be any of combination from above.
...
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...
Mapping NULL values onto other values
In database, sometimes we have to insert the record with some NULL values. But at the time of displaying the data to the user, it doesn't make sense to display NULL values. In order to make the data more readable, we have to display values such a...
Reset Auto increment values in mysql
To reset the auto-increment values you can apply any one of the method given below:
Auto increment value can be reset by using alter table statement. Syntax to reset auto-increment value is as follows:
ALTER TABLE table_name AUTO_INCREM...
How to rename a table in MySQL?
To rename a table in MySQL you need to execute the following command:
RENAME TABLE old_table_name TO new_table_name;
The old_table_name must exist n the database and the new_table_name must not. For example, we have a table name demo and ...
FIND_IN_SET() Function in MySql
find_in_set() is a function which returns the position of a string within second string.
We mostly found a situation where we want to fetch records on the basis of particular string and that string are stored in comma separated values in data...
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 select random record in MySQL?
To randomly select any single record in MySQL you need to execute the following MySQL statement:
select * from tablename order by rand() limit 1
The above statement returns a single record randomly.
For multiple records you need to...
Backup of MySQL Database Data
To take backup of MySQL database only you need to execute the following command:
mysqldump -u [username] p[password] no-create-info [database_name] > [dump_file.sql]
Here, we will use the no-create-info to take data backup only.
How to take backup of MySQL Database structure only
To take backup of structure only you need to execute the following command:
mysqldump -u [username] p[password] no-data [database_name] > [dump_file.sql]
Here, no-data tells the mysqldump to take the structure backup only.
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 show the create table statement of a table in MySQL
To display the create table statement of a table in MySQL you need to execute the following command:
use dbname;
show create table tablename;
But to view this statement you must have the privilege to access the table.
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...
Copy structure of a Table with data and without data
To copy structure in MySQL with data you need to execute the following MySQL statements:
use dbname;
create table new_table_name
select * from existing_table_name;
If you require the copy of structure without data then you need to execu...
How to make a copy of a table in MySQL?
To make a copy of a table in MySQL you need to execute the following statement:
use databasename;
Create table new_table_name
select * from existing_table_name
where conditions
For example:
We have database demo having table1. We re...
Collation in MySQL
Collation are the set of rules that compares the characters of a particular character set exist in MySQL. To display collation in MySQL you need to execute the following statement:
show collation;
GroupBy and OrderBy in MYSQL
ORDERBY alters the order of the specified column into the table by giving there fields name and arrange them in desc as well as asc ordering but default ordering is asc while in GROUPBY aggregates the records by the specified column which all...
How to display all the tables in a database in MySQL?
To display all tables in a database you need to execute the following commands:
use databasename;
First of all you will go inside your database. Suppose your database name is demo then you will execute the above MySQL statement as:
us...
SQL Selectivity
Selectivity
Selectivity is used for the definition of the number of unique values in the table.
It shows how many unique values are there in the table.
Selectivity=Cardinality/number of record *100%
If the selectivity is high than onl...
Joins In SQL
Joins
There are different type of joins in SQL
1.Inner Join-Return all the values if there is a match in both the table.
2.Outer join
-left outer join-Return all the values from left table and the values matched from the right table.
...
Display all the databases in MySQL
To display all the databases in MySQL you need to execute the following command:
SHOW DATABASES;
How to find upcoming birthdays and number of days left from current date?
Suppose, we have a table users having id, name and date_of_birth as column name. So to display upcoming birthday and days left you need to execute the following query:
SELECT id, name, date_of_birth, DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT...
Where versus having in mysql
Hello Readers,
During processing WHERE is used to SELECT data in the original tables while HAVING is used to filter the data in the result set that was produced by the query
WHERE and HAVING clause are used together in a SE...
SQL Joins
Joins
Joins in SQL are nothing but a technique to join the two table based on a common field.
OrderID CustomerID OrderDate
10308 2 1996-09-18
10309 37 1996-09-19
10310 77 1996-09-20
Custom...
Primary Key
Primary Key
A primary key in a table is a unique key that is the element in this column are unique and can not be null.
It is used to mantain the integrity.
It uniquely identify the row in the table.
It is a constraint on the table.
...
Indexing
The index is used to search the data in a table.
The index is basically a data structure that search our data.
It is applied usually on a column of a table.
It is usually a B-tree but can also be hash table and other depending upon the ...
Referential Integrity
Referential Integrity
Referential Integrity is a concept in which the tables shares a relationship between them and that should be consistent.
Ex:
If we have a table called Employee and the other Employee Salary and columns in them of na...
Character set in MySQL
Character set in MySQL is a set of characters that are valid to store in a string. Each character set has one or more collation. Collation is a set of rules to compare characters. To see all the list of Character Set that MySQL supports you need ...
The Fundamentals of Database
Hello Readers,
These are the some important fundamentals of database.
Entity and Entity Relationships:
An Entity is place, question or thing for which we can gather or collect data.
For example, we take the example of employee. Here, ...
How to create user in MySQL Database?
Whenever you create a new user in MySQL it wiil stored in the User table of mysql database. There are two ways to create users:
Create user statement:
CREATE USER username@hostname
IDENTIFIED BY 'password'
For example:
CREATE ...
How to change the password of the MySQL account?
There are different ways to change the password of MySQL account. They are:
Changing password using UPDATE statement:
For this you need to go to the User table of mysql database. To change password you need to execute the following comm...
How to find current version and database in MYSQL using select statement?
To find the version and the current database you need to execute the following query:
Select VERSION(), DATABASE()
How to export mysql database using command line?
Sometimes when you have large databases files and you are unable to export the database manually. Then the easiest (and fastest) way is to use command line.
Export:-
To export the database first need to run the mysqldump.exe and give the fi...
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...
Count values from comma separated field in mysql
Sometimes in mysql we have to count the values that are comma separated from a field in mysql. For this we will use the length function in mysql as follows:
select LENGTH(fieldname)-LENGTH( REPLACE( fieldname , ',', '' ) )+1 from tablename whe...
Update 'column' to date datatype
If a column is not in date datatype but the inserted values in that columns are as date type.
and you want to update that column in date type but without loosing values in that column.
You can use the following query-
UPDATE `tbl` SET `dat...
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...
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...
