Featured
-
How Regression Testing Detects Integral Errors In Business Processes
Humans are forever changing and evolving and so to
by kristina.rigina -
Get Display Banner Advertising Opportunity on FindNerd Platform
“Do you have a product or service that nee
by manoj.rawat -
Android O Released with Top 7 New Features for App Developers
Android was founded by Andy Rubin, Rich Miner, Nic
by sudhanshu.tripathi -
Top 5 Features That Make Laravel the Best PHP Framework for Development
Laravel is a free open source Web Framework of PHP
by abhishek.tiwari.458 -
Objective C or Swift - Which Technology to Learn for iOS Development?
Swift programming language is completely based on
by siddharth.sindhi
Tags
What is NOT NULL Constraint in MySQL?
In MySQL, the NOT NULL constraint is used to restrict a column to NOT accept NULL values.
The NOT NULL constraint allows a column to always contains a value which means you cant not insert/update a record in a table without passing value for t...
Difference between DELETE and TRUNCATE in MySQL.
In MySQL, DELETE and TRUNCATE both are used for deleting data from table.
DELETE
DELETE comes under DML(Data
Manipulation Language).
DELETE can
be used to delete a particular row
by using WHERE clause.
It is slower
than TRUNCATE as...
How to use MIN() function in MySQL?
In MySQL, MIN() function is used to return the smallest value of the specified column in a table.
MIN() Syntax
SELECT MIN(column_name) FROM table_name;
We have a table "employee" as below:
employee
id first_name ...
How to use AVG() function in MySQL?
What is the use of AVG() function in MySQL?
The AVG() function is used to return the average of the numeric column in a table.
AVG() Syntax
SELECT AVG(column_name) FROM table_name;
We have a table "employee" as below:
employe...
How to use MAX() function in MySQL?
In MySQL, the MAX() function used to return the largest value of the specified column in a table.
MAX() Syntax
SELECT MAX(column_name) FROM table_name;
We have a table "employee" as below:
employee
id first_name l...
What is the use of COUNT() function in MySQL?
In MySQL, COUNT() function used to return the number of rows that matches with a specified criteria.
Syntax:
COUNT(column_name)
When we use COUNT(column_name) function in select query it returns the number of rows for the specified colum...
What is NULL value in MySQL?
When we don't define any data for a column then the column holds the NULL value. In MySQL, NULL values are used to represent data that is missing.
NULL Values
If you have created a table with optional columns then you can insert and update ...
What is Auto-increment keyword in MySQL?
Auto-increment is used to allow a unique number when a new record inserted into a table. Generally we use Auto-increment to create value for primary key field automatically when a new record is inserted into a table.
Syntax:
The below stat...
Laravel MySql RAW Update, Delete or Select
Hi All,
Recently I needed to update, delete and select using RAW query in Laravel 5.0. When I searched the internet the only query I was able to find out is the select query but I need to accomplish both update and delete also.So after little ...
How can we prevent SQL-injection in PHP?
Hello Readers ,
For preventing SQL injection we can do by two ways :
1- > Escaping the special characters in your post or get variables , or
2-> By using a parameterized query.
Both would protect you from SQL injection.
Ex...
How to delete data from a table in MySQL?
To delete data from a table we can use either DELETE or TRUNCATE.
DELETE
If we want to delete a record from table then we use DELETE FROM statement.
Syntax:
Below statement deletes rows from table on the basis of where condition, we...
How to delete table/database in MySQL?
We can drop table or database by using DROP keyword.
Syntax:
The DROP TABLE statement is used to delete a table.
DROP TABLE table_name;
The DROP DATABASE statement is used to delete a database.
DROP DATABASE database_name;
E...
How to delete an INDEX in a table in MySQL?
We can drop INDEX from table by using DROP INDEX statement, it is used to delete an index form a table.
Syntax:
ALTER TABLE table_name DROP INDEX index_name;
Example: Suppose you have created an INDEX named "person_user_name_idx" on th...
How to create INDEXES in tables in MySQL?
We create INDEX in a table to find data data fast and efficiently. It allows the database to find data without reading all the records of the table. When we apply search and queries to table INDEX
are used to make them fast.
To create indexes i...
How to add a column into a table in MySQL?
Sometimes we need to add a column that we require into the table. We can do this by using ADD keyword with ALTER command.
Syntax:
To add a column into a table, use the following syntax:
ALTER TABLE table_name
ADD COLUMN column_name data...
How to drop a column from table in MySQL?
Sometimes we need to drop a column that we don't need further from the table. We can do this by using DROP keyword with ALTER command.
Syntax:
To drop/delete the column from a table, use the following syntax:
ALTER TABLE table_name
DROP...
How to change the datatype of a column in MySQL?
Sometimes we need to change the data type of a column in a table. We can do this by using Modify keyword with ALTER command.
Syntax:
To change the data type of a column in a table, use the following syntax:
ALTER TABLE table_name
MODIFY...
Difference between UNION and UNION ALL operators in MySQL.
In MySQL UNION operator used to combine the result of multiple SELECT statements. When we apply the UNION operator then it selects only distinct values by default (No duplicate values are allowed).
UNION Operator
When we apply UNION operato...
How to insert data from one table to another in MySQL?
We can insert data from one table to another in MySQL. For this we use the INSERT INTO SELECT statement that selects the data from on table and inserts that data into another table.
INSERT INTO SELECT Syntax
We can select and insert all the...
How to use Order By in Zend Framework
Hello Friends,
If you are looking to use order by clause in Zend Framework. Please use the below code for the same::
$dbObj = Zend_Db_Table::getDefaultAdapter();
$selectObj = $this->select();
$selectObj->from(array('tbl_users'),arra...
Difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN in MySQL
In MySQL join is used to combine records from 2 or more tables based on a common column between them.
INNER JOIN: It returns all rows when there is a match in both tables.
LEFT JOIN: It returns all rows from the left table and matched rows ...
How to get count of duplicate values from table in mysql?
Sometimes we need to check how many duplicate values a column has in MySQL table. We can do this by using "group by" and "having".
Example: Suppose you have a table "user" with "fist_name" column from which you want to find all the records w...
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...
Function in SQL
Function in SQL
There are two type of function in sql
SQL aggregate function
1) AVG() - Returns the average value
SELECT AVG(column_name) FROM table_name
2) COUNT() - Returns the number of rows
SELECT COUNT(column_name...
Save MySQL query results into a text or CSV file
Hello Readers!
MySQL provides an easy mechanism for writing the results of a select statement into a text file on the server.
Given a query such as
SELECT order_id,product_name,qty FROM orders
which returns three columns of data, t...
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...
How to use GROUP_CONCAT in a CONCAT in mysql
GROUP_CONCAT will always return a string value . It can be easily explain by the example.
Example:
bookid | bookname | isbnno | cateid | autid | pubid
| BK001 | Introduction to Electrodynamics | 0000979001 |...
Laravel 5.0 Writing Static Query In Laravel
Many times we needs to write static query in Laravel. Some time due to the complexity of the query or sometime due to the MYSQL build in functions not supported in the Query Builder or in Eloquent in Laravel example LPAD. Thus at that time Larave...
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...
Find where field is not null in cakephp
Hello Readers!
if you have a database table "users" which has a field "email". And you are looking for a query that fetch out every email field only which are not null in cakephp
Here is the following code below you can use, and its working ...
Read and Write JSON data in MySQL
Store JSON in MYSQL:- Here below snippet will show you how we can store json data in mysql and read the json data from mysql and convert into a java object. Remember that the field where you can store the JSON data in mysql to set data type of t...
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......
Connection string for connecting Android(java) app to MariaDB
Here is the simple connection string to connect the android with Maria DB on linux.
you need to have MariaDB configured to the default port:3306
try
{
Class.forName("org.mariadb.jdbc.Driver");
Connection cntn = DriverManager.getConne...
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...
