Featured
-
No Featured Found!
Tags
What is the use of LIMIT Clause in MySQL?
In MySQL, the LIMIT Clause is used to return the specified number of records means by using LIMIT we can specify how many records we want to return from a table..
The LIMIT Clause is useful when, we want to select some records from a table whi...
How to get the last record from a table in MySQL?
Sometimes we need to find the last inserted record into table. We can do this by using ORDER by and LIMIT.
Syntax:
SELECT *
FROM table_name
ORDER by id DESC
LIMIT 1;
Example: Suppose we have a table named "tickit" and we want to fi...
What is the use of Aliases in MySQL?
In MySQL, aliases are used to give temporarily name to a table or a column. When we write a query we use Aliases to make column name more readable.
Alias Syntax for Columns
SELECT column_name AS alias_name
FROM table_name;
Alias Synta...
What is the use of LIKE Operator in MySQL?
In MySQL, the LIKE operator is used to search for a specified pattern for a column in a WHERE clause .
LIKE Syntax
SELECT column_name
FROM table_name
WHERE column_name LIKE pattern;
We have a table "employee" as below:
employe...
What is the use of UPDATE statement in MySQL?
In MySQL, the UPDATE statement is used to update existing records in a table.
UPDATE Syntax
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
In the above syntax, WHERE clause specifies which row o...
What is the use of IN Operator in MySQL?
In MySQL, the IN operator is used to allow us specify multiple values in a WHERE clause.
IN Operator Syntax
SELECT column_name
FROM table_name
WHERE column_name IN (value1,value2,...);
We have a table "employee" as below:
empl...
What is the use of BETWEEN Operator in MySQL?
In MySQL, the BETWEEN operator is used to select values (values can be numbers, text, or dates) from a column within a specified range.
BETWEEN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
...
What is the use of ORDER by Keyword in MySQL.
In MySQL, the ORDER by keyword is used to get records from a table in sorted order. The ORDER BY keyword can use one or more columns to sort the result-set.
We can sort the records in both ascending and descending order.By default, ORDER by ke...
What is the use of WHERE Clause in MySQL?
In MySQL, the WHERE clause is used to filter records from a table. The WHERE clause is used to get the records that match the specified criteria in that.
WHERE Clause Syntax
SELECT column_name,column_name
FROM table_name
WHERE column_name...
What is the use of DISTINCT statement in MySQL.
In MySQL, the SELECT DISTINCT statement is used to return only distinct values means if in a table, a column has duplicate values then SELECT DISTINCT statement will be used to get different values.
SELECT DISTINCT Syntax
SELECT DISTINCT co...
Use of SELECT statement in MySQL.
In MySQL, The SELECT statement is used to select data from a table.
SELECT Syntax
SELECT column_name,column_name
FROM table_name;
and to select all records
SELECT * FROM table_name;
We have a table "employee" as below:
...
What is the use of GROUP by statement in MySQL?
The GROUP by statement is used with the aggregate functions to group the result by one or more columns.
GROUP by Syntax
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP by column_...
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 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...
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...
Inserting data using replace statement
REPLACE INTO Statement:
It is used to insert a row in a table.It is similar to INSERT INTO statement but it does not allow duplicate row as it replaces the old one with the new one.
Syntax:
REPLACE INTO tablename (column1, column2, col...
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...
How to use Join with Delete command?
To delete records of more than one table we can use joins. Joins are used to combine two table. Here, we will use join to delete the records from more than one table.
Syntax:
DELETE T1, T2 FROM T1 INNER JOIN T2 ON T1.key = T2.key WHERE con...
How to rename database in MySQL?
To rename a database in MySQL you need to follow the steps given below:
Dump the schema using Mysqldump
Restore the dump schema to other schema
Drop the old schema(if required)
For Example:
Suppose we have a database emp which needed...
Cursor
In MySQL, to handle a result set inside a procedure we use cursors. Cursors are:
Read only
Non-scrollable
Asensitive
Cursors can be used in stored procedure, stored functions and triggers.
Syntax:
Declare cursor:
Declare curs...
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 ...
Use of Delete, Truncate and Drop commands in MySQL
This article explains the use and difference of DELETE, TRUNCATE and DROP statements of MySQL.
1. DELETE : DELETE command is used to delete all or specific records from MySQL table. The records delete using DELETE can be rollback. This command...
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 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...
Control Flow Functions
Hello friends,
Today I am here to explain the control flow function of mysql. Some times you want to get the result with respect to some conditions then you can use these functions.
Here are 4 type of control flow function which are following...
UPPER(str) mysql function
UPPER(str)
UPPER() function returns the string str with all characters changed to uppercase according to the current character set mapping means if you want to get a string with all character in upper case then you can use this function.
He...
TRIM mysql function
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)
TRIM mysql function is used for trimming a given character even left or right and if not given then both side. If trimming character is not passed with this functio...
How to pass mulitple values to update records with a single query
Hello Readers if you want to update mulitple record by multiple id, then you have to use in clause in MySql
Lets see the example as below:-
$ids = array(474,25, 99,101);
Here I have an array $ids with multiple id's. Now this array w...
SUBSTRING mysql function
SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)
SUBSTRING mysql function returns substring from a string. You can use this function with some above types.
If you pass only one argument with...
SPACE(N) mysql function
SPACE(N)
If you want to add spaces in a string then you can use SPACE function of mysql. SPACE function returns a string consisting of N space characters means SPACE will returns the white space equal to passed argument n, where n is a intege...
RTRIM(str) mysql function
RTRIM(str)
RTRIM() mysql function returns the string str with trailing space characters removed.
In mysql if you want to remove a white space from the right side of a string then you can use this RTRIM mysql function. RTRIM mysql function re...
RPAD(str,len,padstr) mysql function
RPAD(str,len,padstr)
RPAD mysql function returns the string str, right-padded with the string padstr to a length of len characters means if you want to get a string to a given length and if the length of string is sorter than add the given cha...
RIGHT(str,len) mysql function
RIGHT(str,len)
In mysql, if you want some character from the right in a string then you can use RIGHT() mysql function means this function returns the rightmost len characters from the string str or returns NULL if any argument is NULL.
H...
REVERSE(str) mysql function
REVERSE(str)
If you want to reverse a string in mysql then you can use the REVERSE(str) mysql function. REVERSE() function returns the string str with the order of the characters reversed means if you want to reverse a string then you can pass...
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...
REPLACE(str,from_str,to_str) string function in MySQL
REPLACE(str,fromStr,toStr)
When you want to replace a string from other string in a string then you can use REPLACE function. REPLACE function returns the string str with all occurrences of the string from Str and replace it by the string to S...
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...
