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 the use of HAVING Clause in MySQL?
The HAVING clause is used with aggregate functions as the WHERE clause can not be used with them.
HAVING Syntax
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HA...
what is the use of FORMAT() function in SQL?
In SQL, FORMAT() function is used to format a column value means how a field will to be displayed.
FORMAT() Syntax
SELECT FORMAT(column_name,format) FROM table_name;
In the above syntax parameters are as below:
Parameter Descri...
what is the use of NOW() function in SQL?
In SQL, NOW() function is used to return the current system date and time.
NOW() Syntax
SELECT NOW() FROM table_name;
We have a table "employee" as below:
employee
id first_name last_name salary
........
what is the use of LEN() function in MySQL?
In MySQL, LEN() function is used to return the length of the value in a column (text field) in a table.
LEN() Syntax
SELECT LEN(column_name) FROM table_name;
We have a table "employee" as below:
employee
id first_name ...
How to use SUM() function in MySQL?
In MySQL, SUM() function is used to return the total sum of a numeric column in a table.
SUM() Syntax
SELECT SUM(column_name) FROM table_name;
We have a table "employee" as below:
employee
id first_name last_nam...
Difference between TRUNCATE and DROP in MySQL.
TRUNCATE
TRUNCATE comes under DDL(Data Definition Language).
TRUNCATE removes only rows from the table but the structure of the table remains same.
Data can not be roll backed if we use "TRUNCATE" command to delete data.
Can not use WHERE...
What is DEFAULT Constraint in MySQL?
In MySQL, the DEFAULT constraint is used to insert value into a column in a table. If there is no value is specified for a column then the default value will be added to column for all new records.
DEFAULT Constraint on CREATE TABLE
The fol...
What is CHECK Constraint in MySQL?
In MySQL, the CHECK constraint is used to put limit on value range on value inside a column.
CHECK Constraint on CREATE TABLE
The following statement creates a CHECK constraint on the "id" column when the "employee" table is created. The CH...
What is FOREIGN KEY Constraint in MySQL?
Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table.
In MySQL, the FOREIGN KEY on one table is used to point a PRIMARY KEY in another table.
We have two tables with the following values.
...
What is PRIMARY KEY Constraint in MySQL?
In MySQL, the PRIMARY KEY constraint is used to uniquely identify each record in a table.
Primary key contains UNIQUE values only and the column defined as Primary key column can not be NULL. A table can have only one PRIMARY KEY.
PRIMARY K...
SQL Tutorial -> Sql Inner Join
<-- Chapter 25: SQL Join
Chapter 26
SQL Inner Join
SQL Inner Join clause is used to fetch all the rows from more than two tables in which there is a match between the columns. Inner Join is similar like Join clause.
Lets see an ...
What is UNIQUE Constraint in MySQL?
In MySQL, the UNIQUE constraint is used to uniquely identify each record in a table.
UNIQUE Constraint on CREATE TABLE
The following statement creates a UNIQUE constraint on the "id" column when the "employee" table is created:
Example
...
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 ...
query roll back
hey ci experts can you help me plz
where im making mistake....
my query is not roll backed although im setting the first parameter in the $this->db->trans_start(TRUE); as TRUE
the queries are only rollback when i remove the $this->db-...
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...
android google map is showing only one marker using volley library
I am using Google Map inside the "Fragment" using sqlite database connection through volley library.
I have 4 location, but my map is showing only one marker. I don't know where exactly getting wrong.
Where do I need to change in my code to...
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...
