ORDER BY

grammar

SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]

Use the ORDER BY clause in PHP scripts

<?php
$dbhost = 'localhost:3306';  // mysql server host address
$dbuser = 'root';            // mysql username
$dbpass = '123456';          // mysql username password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('connection failed: ' . mysqli_error($conn));
}
// Setting Code to Prevent Chinese Scrambling
mysqli_query($conn , "set names utf8");

$sql = 'SELECT runoob_id, runoob_title, 
        runoob_author, submission_date
        FROM runoob_tbl
        ORDER BY  submission_date ASC';

mysqli_select_db( $conn, 'RUNOOB' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die('Unable to read data: ' . mysqli_error($conn));
}
echo '<h2>Rookie tutorial MySQL ORDER BY test<h2>';
echo '<table border="1"><tr><td>Course ID</td><td>Title</td><td>author</td><td>Date of submission</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQL_ASSOC))
{
    echo "<tr><td> {$row['runoob_id']}</td> ".
         "<td>{$row['runoob_title']} </td> ".
         "<td>{$row['runoob_author']} </td> ".
         "<td>{$row['submission_date']} </td> ".
         "</tr>";
}
echo '</table>';
mysqli_close($conn);
?>

GROUP BY

grammar

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

Using WITH ROLLUP
- Realize the same statistics on the basis of grouped statistics (SUM,AVG,COUNT...)

Using coalesce
- select coalesce(a,b,c); select b if a= null; select C if b= null; select a if a!=null; and return null if a b c is null

JOIN

• INNER JOIN (Internal Connection, or Equivalent Connection): Gets records of field matching relationships in two tables.
• LEFT JOIN (left join): Gets all records of the left table, even if the right table does not have matching records.
• RIGHT JOIN (Right Connection): Contrary to LEFT JOIN, it is used to retrieve all records of the right table, even if the left table does not have matching records.

Using JOIN in PHP scripts

<?php
$dbhost = 'localhost:3306';  // mysql server host address
$dbuser = 'root';            // mysql username
$dbpass = '123456';          // mysql username password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('connection failed: ' . mysqli_error($conn));
}
// Setting Code to Prevent Chinese Scrambling
mysqli_query($conn , "set names utf8");

$sql = 'SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author';

mysqli_select_db( $conn, 'RUNOOB' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die('Unable to read data: ' . mysqli_error($conn));
}
echo '<h2>Rookie tutorial MySQL JOIN test<h2>';
echo '<table border="1"><tr><td>Course ID</td><td>author</td><td>Landing times</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQL_ASSOC))
{
    echo "<tr><td> {$row['runoob_id']}</td> ".
         "<td>{$row['runoob_author']} </td> ".
         "<td>{$row['runoob_count']} </td> ".
         "</tr>";
}
echo '</table>';
mysqli_close($conn);
?>

sql file

/*
 Navicat MySQL Data Transfer

 Source Server         : 127.0.0.1
 Source Server Version : 50621
 Source Host           : localhost
 Source Database       : RUNOOB

 Target Server Version : 50621
 File Encoding         : utf-8

 Date: 04/13/2017 14:25:12 PM
*/

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `runoob_tbl`
-- ----------------------------
DROP TABLE IF EXISTS `runoob_tbl`;
CREATE TABLE `runoob_tbl` (
  `runoob_id` int(11) NOT NULL AUTO_INCREMENT,
  `runoob_title` varchar(100) NOT NULL,
  `runoob_author` varchar(40) NOT NULL,
  `submission_date` date DEFAULT NULL,
  PRIMARY KEY (`runoob_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `runoob_tbl`
-- ----------------------------
BEGIN;
INSERT INTO `runoob_tbl` VALUES ('1', 'Study PHP', 'Rookie tutorial', '2017-04-12'), ('2', 'Study MySQL', 'Rookie tutorial', '2017-04-12'), ('3', 'Study Java', 'RUNOOB.COM', '2015-05-01'), ('4', 'Study Python', 'RUNOOB.COM', '2016-03-06'), ('5', 'Study C', 'FK', '2017-04-05');
COMMIT;

-- ----------------------------
--  Table structure for `tcount_tbl`
-- ----------------------------
DROP TABLE IF EXISTS `tcount_tbl`;
CREATE TABLE `tcount_tbl` (
  `runoob_author` varchar(255) NOT NULL DEFAULT '',
  `runoob_count` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `tcount_tbl`
-- ----------------------------
BEGIN;
INSERT INTO `tcount_tbl` VALUES ('Rookie tutorial', '10'), ('RUNOOB.COM ', '20'), ('Google', '22');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

NULL

• In MySQL, the comparison of NULL values with any other values (even NULL) always returns false, that is, NULL = NULL returns false.
• Processing NULL in MySQL uses IS NULL and IS NOT NULL operators.
• <=>: The comparison operator (unlike the = operator) returns true when the two values of the comparison are NULL

Processing NULL values with PHP scripts

<?php
$dbhost = 'localhost:3306';  // mysql server host address
$dbuser = 'root';            // mysql username
$dbpass = '123456';          // mysql username password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('connection failed: ' . mysqli_error($conn));
}
// Setting Code to Prevent Chinese Scrambling
mysqli_query($conn , "set names utf8");

if( isset($runoob_count ))
{
   $sql = "SELECT runoob_author, runoob_count
           FROM  runoob_test_tbl
           WHERE runoob_count = $runoob_count";
}
else
{
   $sql = "SELECT runoob_author, runoob_count
           FROM  runoob_test_tbl
           WHERE runoob_count IS NULL";
}
mysqli_select_db( $conn, 'RUNOOB' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die('Unable to read data: ' . mysqli_error($conn));
}
echo '<h2>Rookie tutorial IS NULL test<h2>';
echo '<table border="1"><tr><td>author</td><td>Landing times</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQL_ASSOC))
{
    echo "<tr>".
         "<td>{$row['runoob_author']} </td> ".
         "<td>{$row['runoob_count']} </td> ".
         "</tr>";
}
echo '</table>';
mysqli_close($conn);