Adds column

personal_id

after

api_method

column in

requests

table

ALTER TABLE requests
ADD COLUMN personal_id VARCHAR(15) AFTER api_method;

If we run the same

alter

statement one more time, we will get the following error with code

1060

Error Code: 1060. Duplicate column name 'personal_id'

If we want to delete a column from a table, we use the following command

ALTER TABLE `requests` 
DROP COLUMN `status_id`;

PHP technically doesn't allow it. But we can do it using

func_get_args

and

func_num_args

functions.

func_get_args

Gets function arguments

func_num_args

Gets amount of function arguments.

class myClass {
    public function __construct() {
        $get_arguments       = func_get_args();
        $number_of_arguments = func_num_args();

        if (method_exists($this, $method_name = '__construct'.$number_of_arguments)) {
            call_user_func_array(array($this, $method_name), $get_arguments);
        }
    }

    public function __construct1($argument1) {
        echo 'constructor with 1 parameter ' . $argument1 . "\n";
    }

    public function __construct2($argument1, $argument2) {
        echo 'constructor with 2 parameter ' . $argument1 . ' ' . $argument2 . "\n";
    }

    public function __construct3($argument1, $argument2, $argument3) {
        echo 'constructor with 3 parameter ' . $argument1 . ' ' . $argument2 . ' ' . $argument3 . "\n";
    }
}

$object1 = new myClass('BUET');
$object2 = new myClass('BUET', 'is');
$object3 = new myClass('BUET', 'is', 'Best.');

^

indicates the beginning of a string

&

indicates the end of a string

|

is used to represent multiple search patterns (logical OR)

[]

is used to represent any of the listed characters

[abcdef]

[-]

is used to represent any characters from the range

[a-g]

Select all students where

last_name

contains

უა

with

REGEXP

operator

SELECT *
FROM students
WHERE last_name REGEXP 'უა'

Select all students where

last_name

starts with

გე

with

REGEXP

operator

SELECT *
FROM students
WHERE last_name REGEXP '^გე'

Select all students where

last_name

ends with

უა

with

REGEXP

operator

SELECT *
FROM students
WHERE last_name REGEXP 'უა$'

Select all students where

last_name

contains

უა

or

ია

SELECT *
FROM students
WHERE last_name REGEXP 'უა|ია'

Select all students where

last_name

contains

უა

or

ია

using square brackets

SELECT *
FROM students
WHERE last_name REGEXP '[იუ]ა'

Select all students where

last_name

ends with any one letter from the range

[ა-უ]

followed by

ა

using square brackets with range

SELECT *
FROM students
WHERE last_name REGEXP '[ა-უ]ა$'

Select all students where

last_name

contains

ვა

or ends with

ია

or starts with

ცი

SELECT *
FROM students
WHERE last_name REGEXP '^ცი|ია$|ვა'

Implicit JOIN syntax

SELECT 
    students.first_name,
    notes.*
FROM  
    `notes`,
    `students`
WHERE notes.student_id = students.id

The following explicit JOIN returns the same result as the above implicit one:

SELECT 
    students.first_name,
    notes.*
FROM `notes`
JOIN `students` ON notes.student_id = students.id

If we forget

WHERE

clause on implicit JOIN, we will get CROSS JOIN result

SELECT 
    students.*,
    notes.*
FROM  
    `notes`,
    `students

Which is the equivalent of the following explicit JOIN syntax:

SELECT 
    students.*,
    notes.*
FROM `notes`
JOIN `students

CONS

of the implicit JOIN is that chance is higher to forget

WHERE

clause

When we need to join a table to itself, it is called

SELF JOIN

SELECT  
    s1.*,
    s2.first_name
FROM `students` s1
JOIN `students` s2 ON s2.santa_id = s1.id

This type of join is often used when we have tree structure in a table. Note:

alias

is mandatory in

SELF JOIN

The function is similar to

CONCAT

where

WS

means - with separator. The first parameter is the separator between each one of the additional fields, that we pass as next parameters

SELECT 
    CONCAT_WS(' - ', first_name, 'joins', 'strings', 'and', 'columns', 'with', 'separator') 
FROM students

Function

ROUND

rounds the passed value using standard Math rules. The first argument

2.3

will be rounded to 2 and the second

2.5

becomes 3

SELECT
    ROUND(2.3),
    ROUND(2.5)

Function

FLOOR

rounds the argument down to the greater integer less then the decimal argument. Both of the arguments will be rounded down to

2

SELECT
    FLOOR(2.3),
    FLOOR(2.8)

Function

CEIL

rounds the number up to the lowest integer value greater than the passed decimal argument. Both of the arguments will be rounded to

3

SELECT
    CEIL(2.3),
    CEIL(2.8)

Function

RADIANS

converts degrees to radians.

90

degrees converted to radians gives us half of

PI

:

1.5707963267948966

.

180

degrees converted to radians gives us

PI

:

3.141592653589793

.

SELECT
    RADIANS(90),
    RADIANS(180)

Function

DEGREES

converts radians back to degrees. Half of

PI

radians

1.5707963267948966

converted to degrees gives us

90

degrees.

PI

:

3.141592653589793

radians converted to degrees gives us

180

degrees.

SELECT
    DEGREES(1.5707963267948966),
    DEGREES(3.141592653589793)

These two functions

RADIANS

and

DEGREES

are opposite to each other.

180

degrees converted to radians gives us

PI

:

3.141592653589793

PI

:

3.141592653589793

radians converted to degrees gives us

180

degrees.

SELECT
    RADIANS(180),
    DEGREES(3.141592653589793)

Function

POWER

raises the first argument to the power of another argument. The code below returns

16

because

2

to the power of

4

is

16

SELECT 
    POWER(2, 4)

Note:

POW

and

POWER

are the aliases for the same command The function

CONV

converts the first argument from one number system (the second argument) to another (the third argument) Converts

5

from

10

base system to

2

SELECT
    CONV(5, 10, 2)

Complete list of mathematical functions on the official documentation: https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html

Note: If the function parameter is not surrounded by

quotation marks

, then the parameter is going to be interpreted as a column value. Converts a string or column value to

UPPER-CASE

SELECT 
    UPPER(first_name), 
    UPPER('Converts a string or column value to UPPER-CASE') 
FROM students

Converts a string or column value to

lower-case

SELECT 
    LOWER(first_name), 
    LOWER('Converts a string or column value to lower-case') 
FROM students

Joins strings and columns together

SELECT 
    CONCAT(first_name, ' ', 'joins', ' ', 'strings', ' ', 'and', ' ', 'columns') 
FROM students

The function

LENGTH

Returns the length (in bytes)

SELECT 
    id, 
    first_name,
    LENGTH(first_name) first_name_length 
FROM students

Note: Each Georgian letter takes

three bytes

The function is similar to

CONCAT

where

WS

means - With Separator. The first parameter is the separator between each one of the additional fields, that we pass as next parameters

SELECT 
    CONCAT_WS(' - ', first_name, 'joins', 'strings', 'and', 'columns', 'with', 'separator') 
FROM students

The function

TRIM

removes leading and trailing spaces

SELECT 
    LENGTH(' text ') length,
    LENGTH(TRIM(' text ')) length_with_trim

The function

RTRIM

removes trailing spaces (removes spaces from the end)

SELECT 
    LENGTH(' text ') length,
    LENGTH(RTRIM(' text ')) length_with_right_trim

The function

LTRIM

remove leading spaces (removes spaces from the beginning)

SELECT 
    LENGTH(' text ') length,
    LENGTH(LTRIM(' text ')) length_with_left_trim

The function

LEFT

returns leftmost characters. In this case 5 characters because we pass 5 as second parameter

SELECT 
    LEFT(first_name, 5) AS 'five leftmost characters'
FROM students

The function

RPAD

appends string (third parameter) the specified number of times (second parameter) to the first parameter. In this example each one of the student's last name that is less than 10 characters long, is filled with

-

SELECT 
    RPAD(first_name, 10, '-') AS 'student name'
FROM students

Complete list of string functions on the official documentation: https://dev.mysql.com/doc/refman/8.0/en/string-functions.html

➤ Ternary operators can be defined as a conditional operator that is reasonable for cutting the lines of codes in your program while accomplishing comparisons as well as conditionals. This is treated as an alternative method of implementing if-else or even nested if-else statements. Syntax:

(Conditional statement) ? (Statement_1) : (Statement_2);

For instance:

$result = 62;
echo $result >= 50 ? "Passed\n" : "Failed\n";
// outputs:   Passed


$x = 10;
$y = 20;
$max = $x > $y ? $x : $y;
echo $max."\n";
// outputs:   20


function minvalue($a,$b) {
	return $a > $b ? $a : $b;
}
echo minvalue(150,70);
// outputs:    150

function pyramid($n) {
 // number of spaces
 $k = $n;
 // outer loop to handle number of rows
 // n in this case
 for ($i = 0; $i < $n; $i++) {
	  // inner loop to handle number spaces values changing acc.
	  // to requirement
	  for ($j = 0; $j < $k; $j++) {
	   echo " ";
	  }
	 
	  // decrementing k after each loop
	  $k = $k - 1;
	 
	  // inner loop to handle number of columns
	  // values changing acc. to outer loop
	  for ($j = 0; $j <= $i; $j++ )
	  {
	   // printing stars
	   echo "* ";
	  }
	 
	  // ending line after each row
	  echo "\n";
	 }
}
 // Driver Code
 $n = 10;
 pyramid($n);

Outputs:

          1 
         1 1 
        1 1 1 
       1 1 1 1 
      1 1 1 1 1 
     1 1 1 1 1 1 
    1 1 1 1 1 1 1 
   1 1 1 1 1 1 1 1 
  1 1 1 1 1 1 1 1 1 
 1 1 1 1 1 1 1 1 1 1