Featured
-
No Featured Found!
Tags
MySQL: selecting rows where a column is null
In MySQL, we can select rows where a column is null by using IS NULL operator.
We have a table "employee" as below:
employee
id first_name salary country
............................................
How to make WHERE col LIKE '%' select NULL values too?
When we apply LIKE keyword to any query then it doesn't return NULL values, and sometimes it required to return NULL values as well. We can do this by using IS NULL or IFNULL operator.
Example:
We have a table "employee" as be...
Rolling Back A Transaction in MySql
Transactions are very important part of MySql and for handling the transactions the Transaction Control Language (TCL) is used. Transactions are basically used to handle all the changes made in the database.
Rolling back a transaction means stor...
How to show table list in a database in MySQL?
Sometimes we need to find table list present in a database so that we can identify which table we have to create and which one not.
To work in a particular database we need to use the below statement first:
use database_name;
Example:
...
How to show CREATE TABLE syntax in MySQL?
Sometimes we need to check the CREATE TABLE syntax for already created table in the database. When we need to check what kind of columns and what datatype a column has in a table then we require the CREATE TABLE syntax for that table.
Syntax
...
How to create a TABLE in MySQL?
To create a table in MySQL, we use the CREATE TABLE statement. A table is a combination of row and columns.
CREATE TABLE Syntax
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
....
);
In the a...
How to create a DATABASE in MySQL?
To create a database in MySQL, we use the CREATE DATABASE statement.
CREATE DATABASE Syntax
CREATE DATABASE database_name;
CREATE DATABASE Example
The below statement will create a database named "demo":
CREATE DATABASE demo;
...
How to use of AND & OR operators in MySQL?
Sometimes we need to filter records based on some conditions, for that we use the AND & OR operators in MySQL.
AND operator - We use AND operator if we require both conditions in a query means the AND operator displays a record only if bot...
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...
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 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...
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...
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 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...
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...
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 ...
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...
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...
