Category Archives: MySql

MySQL Truncate

What if we only want to empty the data inside the table, and not to delete the table definition in this case we use TRUNCATE TABLE statement:

Syntax:

TRUNCATE TABLE table_name

1	TRUNCATE TABLE table_name

MySQL UNION Keyword

Leave a reply

MySQL UNION Keyword allows you to combine two or more result sets from multiple tables or queries together.

The number of columns in each SELECT statement has to be the same .
The data type of the column in the column list of the SELECT statement must be the same or at least convertible.

MySQL UNION removes all duplicate rows from the result set

Syntax:

SELECT statement
UNION [DISTINCT | ALL]
SELECT statement
UNION [DISTINCT | ALL]

SELECT statement

UNION [DISTINCT | ALL]

SELECT statement

UNION [DISTINCT | ALL]

MySQL BETWEEN Clause

Leave a reply

The BETWEEN operator is used in combination with WHERE clause match or select a range of data value between two values.

Syntax:

SELECT <field1, field2,...>
FROM <table_name>
WHERE <field_name>
BETWEEN value1 AND value2

SELECT <field1, field2,...>

FROM <table_name>

WHERE <field_name>

BETWEEN value1 AND value2

Consider “emp_info” table:

empID	empName	sale
1	Mark	1000
2	Max	1500
3	Mark	1800
5	John	1500

SELECT * FROM emp_info
WHERE SALE
BETWEEN 1000 AND 2000;

SELECT * FROM emp_info

WHERE SALE

BETWEEN 1000 AND 2000;

Output:

empID	empName	sale
1	Mark	1000
2	Max	1500
3	Mark	1800
4	Mark	3000
5	John	1500

MySQL IN Clause

Leave a reply

The IN clause is used to match multiple values in a WHERE clause.

Syntax:

SELECT <field1, field2,...>
FROM <table_name>
WHERE <field> IN (value1,value2,...)

SELECT <field1, field2,...>

FROM <table_name>

WHERE <field> IN (value1,value2,...)

Consider “emp_info” table:

empID	empName	sale
1	Mark	1000
2	Max	1000
3	Mark	1000
4	Mark	1000
5	John	1000

SELECT * FROM emp_info
WHERE EMPNAME IN ('Mark','Max')

1 2	SELECT * FROM emp_info WHERE EMPNAME IN ('Mark','Max')

Output:

empID	empName	sale
1	Mark	1000
2	Max	1000
3	Mark	1000
4	Mark	1000

MySQL Group By Clause

Leave a reply

The GROUP BY statement is used to group the result set returned by SELECT statement by one or more columns.We can use GROUP BY to group values of a column we can perform any calculations on that column. COUNT, SUM, AVG etc. function can be applied on the grouped column.

Consider below “emp_info” table

empID	empName	sale
1	Mark	1000
2	Max	1000
3	Mark	1000
4	Mark	1000
5	John	1000

SELECT EMPNAME,SUM(SALE) FROM emp_info
GROUP BY EMPNAME

1 2	SELECT EMPNAME,SUM(SALE) FROM emp_info GROUP BY EMPNAME

Output:

empName	sale
Mark	3000
Max	1000
John	1000

MySQL NULL Values

Leave a reply

We have seen SQL SELECT command along with WHERE clause to fetch data from MySQL table. But when we try to give a condition which compare field or column value to NULL it does not work properly.

To handle such situation MySQL provides three operators

IS NULL: operator returns true of column value is NULL.
IS NOT NULL: operator returns true of column value is not NULL.
<=> operator compare values, which (unlike the = operator) is true even for two NULL values

Conditions involving NULL are special. You cannot use = NULL or != NULL to look for NULL values in columns. Such comparisons always fail because it’s impossible to tell whether or not they are true. Even NULL = NULL fails.

To look for columns that are or are not NULL, use IS NULL or IS NOT NULL.

Using NULL values at Command Prompt:

Suppose a table tcount_tbl in TUTORIALS database and it contains two columns tutorial_author and tutorial_count, where a NULL tutorial_count indicates that the value is unknown:

Example:

Try out following examples:

root@host# mysql -u root -p password;
Enter password:*******
mysql&gt; use TUTORIALS;
Database changed
mysql&gt; create table tcount_tbl
    -&gt; (
    -&gt; tutorial_author varchar(40) NOT NULL,
    -&gt; tutorial_count  INT
    -&gt; );
Query OK, 0 rows affected (0.05 sec)
mysql&gt; INSERT INTO tcount_tbl
    -&gt; (tutorial_author, tutorial_count) values ('mahran', 20);
mysql&gt; INSERT INTO tcount_tbl
    -&gt; (tutorial_author, tutorial_count) values ('mahnaz', NULL);
mysql&gt; INSERT INTO tcount_tbl
    -&gt; (tutorial_author, tutorial_count) values ('Jen', NULL);
mysql&gt; INSERT INTO tcount_tbl
    -&gt; (tutorial_author, tutorial_count) values ('Gill', 20);

mysql&gt; SELECT * from tcount_tbl;
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
| mahran          |             20 |
| mahnaz          |           NULL |
| Jen             |           NULL |
| Gill            |             20 |
+-----------------+----------------+
4 rows in set (0.00 sec)

mysql&gt;

root@host# mysql -u root -p password;

Enter password:*******

mysql> use TUTORIALS;

Database changed

mysql> create table tcount_tbl

-> (

-> tutorial_author varchar(40) NOT NULL,

-> tutorial_count INT

-> );

Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO tcount_tbl

-> (tutorial_author, tutorial_count) values ('mahran', 20);

mysql> INSERT INTO tcount_tbl

-> (tutorial_author, tutorial_count) values ('mahnaz', NULL);

mysql> INSERT INTO tcount_tbl

-> (tutorial_author, tutorial_count) values ('Jen', NULL);

mysql> INSERT INTO tcount_tbl

-> (tutorial_author, tutorial_count) values ('Gill', 20);

mysql> SELECT * from tcount_tbl;

+-----------------+----------------+

| tutorial_author | tutorial_count |

+-----------------+----------------+

| mahran | 20 |

| mahnaz | NULL |

| Jen | NULL |

| Gill | 20 |

+-----------------+----------------+

4 rows in set (0.00 sec)

mysql>

You can see that = and != do not work with NULL values as follows:

mysql&gt; SELECT * FROM tcount_tbl WHERE tutorial_count = NULL;
Empty set (0.00 sec)
mysql&gt; SELECT * FROM tcount_tbl WHERE tutorial_count != NULL;
Empty set (0.01 sec)

mysql> SELECT * FROM tcount_tbl WHERE tutorial_count = NULL;

Empty set (0.00 sec)

mysql> SELECT * FROM tcount_tbl WHERE tutorial_count != NULL;

Empty set (0.01 sec)

To find records where the tutorial_count column is or is not NULL, the queries should be written like this:

mysql&gt; SELECT * FROM tcount_tbl 
    -&gt; WHERE tutorial_count IS NULL;
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
| mahnaz          |           NULL |
| Jen             |           NULL |
+-----------------+----------------+
2 rows in set (0.00 sec)
mysql&gt; SELECT * from tcount_tbl 
    -&gt; WHERE tutorial_count IS NOT NULL;
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
| mahran          |             20 |
| Gill            |             20 |
+-----------------+----------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM tcount_tbl

-> WHERE tutorial_count IS NULL;

+-----------------+----------------+

| tutorial_author | tutorial_count |

+-----------------+----------------+

| mahnaz | NULL |

| Jen | NULL |

+-----------------+----------------+

2 rows in set (0.00 sec)

mysql> SELECT * from tcount_tbl

-> WHERE tutorial_count IS NOT NULL;

+-----------------+----------------+

| tutorial_author | tutorial_count |

+-----------------+----------------+

| mahran | 20 |

| Gill | 20 |

+-----------------+----------------+

2 rows in set (0.00 sec)

Handling NULL Values in PHP Script:

You can use if…else condition to prepare a query based on NULL value.

Example:

Following example take tutorial_count from outside and then compare it with the value available in the table.

&lt;?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
if( isset($tutorial_count ))
{
   $sql = 'SELECT tutorial_author, tutorial_count
           FROM  tcount_tbl
           WHERE tutorial_count <strong>=</strong> $tutorial_count';
}
else
{
   $sql = 'SELECT tutorial_author, tutorial_count
           FROM  tcount_tbl
           WHERE tutorial_count <strong>IS</strong> $tutorial_count';
}

mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    echo "Author:{$row['tutorial_author']}  &lt;br&gt; ".
         "Count: {$row['tutorial_count']} &lt;br&gt; ".
         "--------------------------------&lt;br&gt;";
} 
echo "Fetched data successfully\n";
mysql_close($conn);
?&gt;

<?php

$dbhost = 'localhost:3036';

$dbuser = 'root';

$dbpass = 'rootpassword';

$conn = mysql_connect($dbhost, $dbuser, $dbpass);

if(! $conn )

{

die('Could not connect: ' . mysql_error());

}

if( isset($tutorial_count ))

{

$sql = 'SELECT tutorial_author, tutorial_count

FROM tcount_tbl

WHERE tutorial_count = $tutorial_count';

}

else

{

$sql = 'SELECT tutorial_author, tutorial_count

FROM tcount_tbl

WHERE tutorial_count IS $tutorial_count';

}

mysql_select_db('TUTORIALS');

$retval = mysql_query( $sql, $conn );

if(! $retval )

{

die('Could not get data: ' . mysql_error());

}

while($row = mysql_fetch_array($retval, MYSQL_ASSOC))

{

echo "Author:{$row['tutorial_author']} ".

"Count: {$row['tutorial_count']} ".

"-------------------------------- ";

}

echo "Fetched data successfully\n";

mysql_close($conn);

MySQL Sorting Results

Leave a reply

The result set returned by the SELECT Query is free from any sort order unless you specify it in the SELECT statement.ORDER BY clause is used to sort the result set by a specified column or columns.The ORDER BY clause sorts the records in ascending order by default. To sort the result set in a descending order, use the DESC keyword.

Syntax:

SELECT <field1, field2,...>
FROM <table_name>
ORDER BY <field1, field2,...> ASC|DESC

SELECT <field1, field2,...>

FROM <table_name>

ORDER BY <field1, field2,...> ASC|DESC

We can sort returned result set on any column provided.
We can sort the result set on column or columns.
ASC or DESC keyword is used to get result in ascending or descending order accordingly. Default is ascending order.

MySQL Like Clause

Leave a reply

The LIKE operator is used in a WHERE clause to search for a specified pattern of string in a column value, WHERE clause with equal sign (=) works fine where we want to do an exact match. Like if “emp_name = ‘deepmala'”. But there may be a requirement where we want to filter out all the results where emp_name should contain “mala”. This can be handled using SQL LIKE clause along with WHERE clause.

If LIKE clause is used along with % characters then sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern.

Without a % character LIKE clause works similar to equal sign along with WHERE clause.

Syntax:

SELECT <column_name>
FROM <table_name>
WHERE <column_name> LIKE <pattern>

SELECT <column_name>

FROM <table_name>

WHERE <column_name> LIKE <pattern>

LIKE clause can be used with WHERE clause.
LIKE clause can be used in place of equal sign.
When LIKE is used alongwith % sign then sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern.
We can have more than one conditions with AND or OR operators
LIKE clause can be used along in DELETE or UPDATE query too.

LIKE Operator Example

The “emp_info” table:

empID	empName
1	smita
2	sneha
3	jyoti
4	pooja

Now we want to select the employees whose name starts with character “s” from the table above then the query would be like below.

SELECT * FROM emp_info
WHERE EMPNAME LIKE 's%';

1 2	SELECT * FROM emp_info WHERE EMPNAME LIKE 's%';

OUTPUT:

empID	empName
1	smita
2	sneha

MySQL Delete Query

Leave a reply

If you want to delete a record from any MySQL table then you can use SQL command DELETE FROM.

MySql Delete Syntax:

DELETE FROM table_name [WHERE Clause]

1	DELETE FROM table_name [WHERE Clause]

WHERE clause can be used to delete specified rows, if WHERE clause is not specified then all rows get deleted.

MySQL Update Query

Leave a reply

The MySQL UPDATE statement is used to update column values of existing rows in a table with new values.Specific columns can be modified using the SET clause by supplying new values for those column. MySQL UPDATE statement can be used with WHERE clause to specify the conditions for the rows to update. Without using WHERE clause, all rows are updated.

MySql UPDATE Syntax:

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

1 2	UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]

MySQL Where Clause

Leave a reply

In the previous chapter SELECT statement is used to fetch data from MySQL table. Here we can add WHERE clause to filter out results. Using WHERE clause we can specify a selection condition to select only those records which fulfills the specified condition.

Syntax:

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

1 2	SELECT field1, field2,...fieldN FROM table_name1, table_name2... [WHERE condition1 [AND [OR]] condition2.....

A WHERE clause can be used alongwith DELETE or UPDATE SQL command also.

MySQL Select Query

Leave a reply

The SQL SELECT command is used to fetch data from MySQL database.

MySql SELECT Syntax:

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE Clause]
[OFFSET M ][LIMIT N]

SELECT field1, field2,...fieldN FROM table_name1, table_name2...

[WHERE Clause]

[OFFSET M ][LIMIT N]

You can use one or more tables separated by comma to include various condition using a WHERE clause. But WHERE clause is an optional part of SELECT command.
You can fetch one or more columns in a single SELECT query.
You can specify star (*) in place of fields. In this case SELECT will return all the fields
You can specify any condition using WHERE clause.
You can specify an offset using OFFSET from where SELECT will start returning records. By default offset is zero
You can limit the number of returned using LIMIT attribute.

Select all data

SELECT *
FROM <table_name>

1 2	SELECT * FROM <table_name>

MySql select specific columns

SELECT <column1,column2,...>
FROM <table_name>

1 2	SELECT <column1,column2,...> FROM <table_name>

MySql select specific rows

SELECT <column1,column2,...>
FROM <table_name>
WHERE <column1> operator <value>
//For all columns
SELECT *
FROM <table_name>
WHERE <expression>

SELECT <column1,column2,...>

FROM <table_name>

WHERE <column1> operator <value>

//For all columns

SELECT *

FROM <table_name>

WHERE <expression>

MySql select specific rows with AND operator

SELECT *
FROM <table_name>
  WHERE <column1> operator <value>
  AND <column2> operator <value>;

SELECT *

FROM <table_name>

WHERE <column1> operator <value>

AND <column2> operator <value>;

MySql select specific rows with OR operator

SELECT *
FROM <table_name>
  WHERE <column1> operator <value>
  OR <column2> operator <value>;

SELECT *

FROM <table_name>

WHERE <column1> operator <value>

OR <column2> operator <value>;

While fetching data you can write as complex SQL as you like. Procedure will remain same as mentioned above.

MySql select with LIMIT

The LIMIT clause can be used to limit the number of rows to be returned by the SELECT query. LIMIT takes one or two numeric arguments, which must both be non negative integer constants.With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

SELECT * FROM tbl LIMIT 5;     # Retrieve first 5 rows

SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15

SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows

More MySql

Learn

ABOUT PAGES

Category Archives: MySql

MySQL Truncate

MySQL UNION Keyword

MySQL BETWEEN Clause

MySQL IN Clause

MySQL Group By Clause

MySQL NULL Values

Using NULL values at Command Prompt:

Example:

Handling NULL Values in PHP Script:

Example:

MySQL Sorting Results

MySQL Like Clause

LIKE Operator Example

MySQL Delete Query

MySql Delete Syntax:

MySQL Update Query

MySql UPDATE Syntax:

MySQL Where Clause

MySQL Select Query

MySql SELECT Syntax:

Select all data

MySql select specific columns

MySql select specific rows

MySql select specific rows with AND operator

MySql select specific rows with OR operator

MySql select with LIMIT