Web Programming: Mysql Basic QUeries

Ada aja saat-saat cuma untuk buat query mysql itu lupa syntax-nya ..:)

Berikut adalah basic syntax sql yang sering dipakai:

UPdate:

UPDATE table_name SET
column_name1 = value1,
column_name2 = value2,
column_name3 = value3 ...
[WHERE conditions];

Insert:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Or:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Or:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]
sample,

INSERT INTO example 
(name, age) VALUES('Timmy Mellowman', '23' )

Pattern Matching Data:

select f_name, l_name from employee_data where f_name = "John";

+--------+------------+
| f_name | l_name     |
+--------+------------+
| John   | Hagan      |
| John   | MacFarland |
+--------+------------+
2 rows in set (0.00 sec)

select f_name, l_name from employee_data where f_name LIKE "J%";

+--------+------------+
| f_name | l_name     |
+--------+------------+
| John   | Hagan      |
| John   | MacFarland |
| Joseph | Irvine     |
+--------+------------+
3 rows in set (0.00 sec)
select f_name, l_name, title from employee_data
where title like '%senior%';

+--------+--------+----------------------------+
| f_name | l_name | title                      |
+--------+--------+----------------------------+
| John   | Hagan  | Senior Programmer          |
| Ganesh | Pillai | Senior Programmer          |
| Kim    | Hunter | Senior Web Designer        |
| Mike   | Harper | Senior Marketing Executive |
+--------+--------+----------------------------+
4 rows in set (0.00 sec)

select f_name, l_name from employee_data
where l_name like '%a';

+--------+--------+
| f_name | l_name |
+--------+--------+
| Manish | Sharma |
| Alok   | Nanda  |
| Arthur | Hoopla |
+--------+--------+
3 rows in set (0.00 sec)

Limiting Data Retrieval:

SELECT f_name, l_name from
employee_data LIMIT 5;

+---------+--------+
| f_name  | l_name |
+---------+--------+
| Manish  | Sharma |
| John    | Hagan  |
| Ganesh  | Pillai |
| Anamika | Pandit |
| Mary    | Anchor |
+---------+--------+
5 rows in set (0.01 sec)

SELECT f_name, l_name, age from
employee_data ORDER BY age DESC
LIMIT 4;

+--------+----------+------+
| f_name | l_name   | age  |
+--------+----------+------+
| Paul   | Simon    |   43 |
| Mike   | Harper   |   36 |
| Peter  | Champion |   36 |
| Roger  | Lewis    |   35 |
+--------+----------+------+
4 rows in set (0.00 sec)

SELECT f_name, l_name, age from
employee_data ORDER BY age
LIMIT 2;

+--------+----------+------+
| f_name | l_name   | age  |
+--------+----------+------+
| Edward | Sakamuro |   25 |
| Mary   | Anchor   |   26 |
+--------+----------+------+
2 rows in set (0.01 sec)

SELECT f_name, l_name from
employee_data LIMIT 6,3;

+--------+------------+
| f_name | l_name     |
+--------+------------+
| John   | MacFarland |
| Edward | Sakamuro   |
| Alok   | Nanda      |
+--------+------------+
3 rows in set (0.00 sec)

Distinct Keyword:

select title from employee_data;

+----------------------------+
| title                      |
+----------------------------+
| CEO                        |
| Senior Programmer          |
| Senior Programmer          |
| Web Designer               |
| Web Designer               |
| Programmer                 |
| Programmer                 |
| Programmer                 |
| Programmer                 |
| Multimedia Programmer      |
| Multimedia Programmer      |
| Multimedia Programmer      |
| Senior Web Designer        |
| System Administrator       |
| System Administrator       |
| Senior Marketing Executive |
| Marketing Executive        |
| Marketing Executive        |
| Marketing Executive        |
| Customer Service Manager   |
| Finance Manager            |
+----------------------------+
21 rows in set (0.00 sec)

select DISTINCT title from employee_data;

+----------------------------+
| title                      |
+----------------------------+
| CEO                        |
| Customer Service Manager   |
| Finance Manager            |
| Marketing Executive        |
| Multimedia Programmer      |
| Programmer                 |
| Senior Marketing Executive |
| Senior Programmer          |
| Senior Web Designer        |
| System Administrator       |
| Web Designer               |
+----------------------------+
11 rows in set (0.00 sec)

MYSQL MIN() – Minimum Value

select MIN(salary) from employee_data;

+-------------+
| MIN(salary) |
+-------------+
|       70000 |
+-------------+
1 row in set (0.00 sec)

MYSQL MAX() – Maximum Value

select MAX(salary) from employee_data;

+-------------+
| MAX(salary) |
+-------------+
|      200000 |
+-------------+
1 row in set (0.00 sec)

Finding the Average and Sum:

select SUM(salary) from employee_data;

+-------------+
| SUM(salary) |
+-------------+
|     1997000 |
+-------------+
1 row in set (0.00 sec)

select SUM(perks) from employee_data;

+------------+
| SUM(perks) |
+------------+
|     390000 |
+------------+
1 row in set (0.00 sec)

select sum(salary) + sum(perks) from employee_data;

+-------------------------+
| sum(salary)+ sum(perks) |
+-------------------------+
|                 2387000 |
+-------------------------+
1 row in set (0.01 sec)

select avg(age) from employee_data;
+----------+
| avg(age) |
+----------+
|  31.6190 |
+----------+
1 row in set (0.00 sec)

Having Clause:

select title, AVG(salary)
from employee_data
GROUP BY title;

+----------------------------+-------------+
| title                      | AVG(salary) |
+----------------------------+-------------+
| CEO                        | 200000.0000 |
| Customer Service Manager   |  70000.0000 |
| Finance Manager            | 120000.0000 |
| Marketing Executive        |  77333.3333 |
| Multimedia Programmer      |  83333.3333 |
| Programmer                 |  75000.0000 |
| Senior Marketing Executive | 120000.0000 |
| Senior Programmer          | 115000.0000 |
| Senior Web Designer        | 110000.0000 |
| System Administrator       |  95000.0000 |
| Web Designer               |  87500.0000 |
+----------------------------+-------------+
11 rows in set (0.00 sec)

select title, AVG(salary)
from employee_data
GROUP BY title
HAVING AVG(salary) > 100000;

+----------------------------+-------------+
| title                      | AVG(salary) |
+----------------------------+-------------+
| CEO                        | 200000.0000 |
| Finance Manager            | 120000.0000 |
| Senior Marketing Executive | 120000.0000 |
| Senior Programmer          | 115000.0000 |
| Senior Web Designer        | 110000.0000 |
+----------------------------+-------------+
5 rows in set (0.00 sec)

Select Statement:

Displaying the MySQL version number

select version();

+-----------+
| version() |
+-----------+
| 3.22.32   |
+-----------+
1 row in set (0.00 sec)

Displaying the current date and time

select now();

+---------------------+
| now()               |
+---------------------+
| 2001-05-31 00:36:24 |
+---------------------+
1 row in set (0.00 sec)

Displaying the current Day, Month and Year

SELECT DAYOFMONTH(CURRENT_DATE);

+--------------------------+
| DAYOFMONTH(CURRENT_DATE) |
+--------------------------+
|                       28 |
+--------------------------+
1 row in set (0.01 sec)

SELECT MONTH(CURRENT_DATE);
+---------------------+
| MONTH(CURRENT_DATE) |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)

SELECT YEAR(CURRENT_DATE);
+--------------------+
| YEAR(CURRENT_DATE) |
+--------------------+
|               2001 |
+--------------------+
1 row in set (0.00 sec)

Displaying text strings

select 'I Love MySQL';

+--------------+
| I Love MySQL |
+--------------+
| I Love MySQL |
+--------------+
1 row in set (0.00 sec)

select 'Manish Sharma' as Name;

+---------------+
| Name          |
+---------------+
| Manish Sharma |
+---------------+
1 row in set (0.00 sec)

Evaluating expressions in MySQL

select ((4 * 4) / 10 ) + 25;

+----------------------+
| ((4 * 4) / 10 ) + 25 |
+----------------------+
|                26.60 |
+----------------------+
1 row in set (0.00 sec)

Concatenate:

SELECT CONCAT(f_name, " ", l_name)
from employee_data
where title = 'Programmer';

+-----------------------------+
| CONCAT(f_name, " ", l_name) |
+-----------------------------+
| Fred Kruger                 |
| John MacFarland             |
| Edward Sakamuro             |
| Alok Nanda                  |
+-----------------------------+
4 rows in set (0.00 sec)

select CONCAT(f_name, " ", l_name)
AS Name
from employee_data
where title = 'Marketing Executive';

+---------------+
| Name          |
+---------------+
| Monica Sehgal |
| Hal Simlai    |
| Joseph Irvine |
+---------------+
3 rows in set (0.00 sec)

Mathematical Functions:

select 87 % 9;
+--------+
| 87 % 9 |
+--------+
|      6 |
+--------+
1 row in set (0.00 sec)

MySQL – MOD(x, y)

Displays the remainder of x divided by y, SImilar to the Modulus  operator.
select MOD(37, 13);

+-------------+
| MOD(37, 13) |
+-------------+
|          11 |
+-------------+
1 row in set (0.00 sec)

MySQL ABS(x)

Calculates the Absolute value of number x. Thus, if x  is negative its positive value is returned.
select ABS(-4.05022);

+---------------+
| ABS(-4.05022) |
+---------------+
|       4.05022 |
+---------------+
1 row in set (0.00 sec)

select ABS(4.05022);
+--------------+
| ABS(4.05022) |
+--------------+
|      4.05022 |
+--------------+
1 row in set (0.00 sec)

SQL SIGN(x)

Returns 1, 0 or -1 when x is positive, zero or negative,  respectively.
select SIGN(-34.22);

+--------------+
| SIGN(-34.22) |
+--------------+
|           -1 |
+--------------+
1 row in set (0.00 sec)

select SIGN(54.6);
+------------+
| SIGN(54.6) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

select SIGN(0);
+---------+
| SIGN(0) |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

POWER(x,y)

Calculates the value of x raised to the power of y.
select POWER(4,3);

+------------+
| POWER(4,3) |
+------------+
|  64.000000 |
+------------+
1 row in set (0.00 sec)

SQRT(x)

Calculates the square root of x.
select SQRT(3);

+----------+
| SQRT(3)  |
+----------+
| 1.732051 |
+----------+
1 row in set (0.00 sec)

ROUND(x) and ROUND(x,y)

Returns the value of x rounded to the nearest integer. ROUND can also  accept an additional argument y that will round x to y  decimal places.
select ROUND(14.492);

+---------------+
| ROUND(14.492) |
+---------------+
|            14 |
+---------------+
1 row in set (0.00 sec)

select ROUND(4.5002);
+---------------+
| ROUND(4.5002) |
+---------------+
|             5 |
+---------------+
1 row in set (0.00 sec)

select ROUND(-12.773);
+----------------+
| ROUND(-12.773) |
+----------------+
|            -13 |
+----------------+
1 row in set (0.00 sec)

select ROUND(7.235651, 3);
+--------------------+
| ROUND(7.235651, 3) |
+--------------------+
|              7.236 |
+--------------------+
1 row in set (0.00 sec)

FLOOR(x)

Returns the largest integer that is less than or equal to x.
select FLOOR(23.544);

+---------------+
| FLOOR(23.544) |
+---------------+
|            23 |
+---------------+
1 row in set (0.00 sec)

select FLOOR(-18.4);
+--------------+
| FLOOR(-18.4) |
+--------------+
|          -19 |
+--------------+
1 row in set (0.00 sec)

CEILING(x)

Returns the smallest integer that is greater than or equal to x.
select CEILING(54.22);

+----------------+
| CEILING(54.22) |
+----------------+
|             55 |
+----------------+
1 row in set (0.00 sec)

select CEILING(-62.23);
+-----------------+
| CEILING(-62.23) |
+-----------------+
|             -62 |
+-----------------+
1 row in set (0.00 sec)

Table joins:

select CONCAT(f_name, " ", l_name) AS Name,
s_name as 'Spouse Name' from
employee_data, employee_per
where m_status = 'Y' AND
emp_id = e_id;

+-----------------+-----------------+
| Name            | Spouse Name     |
+-----------------+-----------------+
| Manish Sharma   | Anamika Sharma  |
| John Hagan      | Jane Donner     |
| Ganesh Pillai   | Sandhya Pillai  |
| Anamika Sharma  | Manish Sharma   |
| John MacFarland | Mary Shelly     |
| Alok Nanda      | Manika Nanda    |
| Paul Simon      | Muriel Lovelace |
| Arthur Hoopla   | Rina Brighton   |
| Kim Hunter      | Matt Shikari    |
| Danny Gibson    | Betty Cudly     |
| Mike Harper     | Stella Stevens  |
| Monica Sehgal   | Edgar Alan      |
| Peter Champion  | Ruby Richer     |
+-----------------+-----------------+
13 rows in set (0.00 sec)

select CONCAT(employee_data.f_name, " ", employee_data.l_name)
AS Name, employee_per.s_name AS 'Spouse Name'
from employee_data, employee_per
where employee_per.m_status = 'Y'
AND employee_data.emp_id = employee_per.e_id;

+-----------------+-----------------+
| Name            | Spouse Name     |
+-----------------+-----------------+
| Manish Sharma   | Anamika Sharma  |
| John Hagan      | Jane Donner     |
| Ganesh Pillai   | Sandhya Pillai  |
| Anamika Sharma  | Manish Sharma   |
| John MacFarland | Mary Shelly     |
| Alok Nanda      | Manika Nanda    |
| Paul Simon      | Muriel Lovelace |
| Arthur Hoopla   | Rina Brighton   |
| Kim Hunter      | Matt Shikari    |
| Danny Gibson    | Betty Cudly     |
| Mike Harper     | Stella Stevens  |
| Monica Sehgal   | Edgar Alan      |
| Peter Champion  | Ruby Richer     |
+-----------------+-----------------+
13 rows in set (0.00 sec)

Deleting Entries:

DELETE from table_name [WHERE conditions];

Dropping Table:

DELETE from employee_data;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLES;
+---------------------+
| Tables in employees |
+---------------------+
| employee_data       |
+---------------------+
1 rows in set (0.00 sec)

DROP TABLE employee_data;
Query OK, 0 rows affected (0.01 sec)

---
source: www.webdevelopersnotes.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s