MySQL Immediate

Create Table

mysql> CREATE TABLE myexperience 
    -> (
    ->      myexperience_id INT NOT NULL AUTO_INCREMENT,
    ->      experience      VARCHAR(200) NOT NULL,
    ->      pay             DOUBLE NOT NULL,
    ->      year            INT NOT NULL,
    ->      PRIMARY KEY(myexperience_id)
    -> );
Query OK, 0 rows affected (0.01 sec)

Insert Sample Records

mysql> INSERT INTO myexperience(myexperience_id,pay,year,experience)
    ->      VALUES(null,56,4,'PHP Developer'),
    ->            (null,60,4,'MySQL System Admin'),
    ->            (null,100,4,'Linux System Admin');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

Select all record from the table

mysql> SELECT * FROM myexperience;
+-----------------+--------------------+-----+------+
| myexperience_id | experience         | pay | year |
+-----------------+--------------------+-----+------+
|               1 | PHP Developer      |  56 |    4 | 
|               2 | MySQL System Admin |  60 |    4 | 
|               3 | Linux System Admin | 100 |    4 | 
+-----------------+--------------------+-----+------+
3 rows in set (0.00 sec)

Delete some record in the table

mysql> DELETE FROM myexperience WHERE pay=56;
Query OK, 1 row affected (0.00 sec)

Use truncate to empty the table, but not to delete the table

mysql> truncate myexperience;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM myexperience;
Empty set (0.00 sec)

Delete the table from the database

This command will completely deleted the table from the database.

mysql> DROP TABLE myexperience;
Query OK, 0 rows affected (0.01 sec)

Create the table back and insert some sample record

mysql> CREATE TABLE myexperience  
    -> ( 
    ->       myexperience_id INT NOT NULL AUTO_INCREMENT, 
    ->       experience      VARCHAR(200) NOT NULL, 
    ->       pay             DOUBLE NOT NULL, 
    ->       year            INT NOT NULL, 
    ->       PRIMARY KEY(myexperience_id) 
    -> );
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO myexperience
    ->    (myexperience_id,pay,year,experience) 
    ->    VALUES(null,56,4,'PHP Developer'), 
    ->          (null,60,4,'MySQL System Admin'),
    ->          (null,100,4,'Linux System Admin'),
    ->          (null,56,3,'PHP MYSQL') ;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM myexperience;
+-----------------+--------------------+-----+------+
| myexperience_id | experience         | pay | year |
+-----------------+--------------------+-----+------+
|               1 | PHP Developer      |  56 |    4 | 
|               2 | MySQL System Admin |  60 |    4 | 
|               3 | Linux System Admin | 100 |    4 | 
|               4 | PHP MYSQL          |  56 |    3 | 
+-----------------+--------------------+-----+------+
4 rows in set (0.00 sec)

mysql select distinct to return non-duplicated records

mysql> SELECT DISTINCT pay FROM myexperience;
+-----+
| pay |
+-----+
|  56 | 
|  60 | 
| 100 | 
+-----+
3 rows in set (0.02 sec)

LIMIT to return the number of rows

• query 1: returns only one record, usually the first record
• query 2: returns two records
• query 3: returns two records and the return records start from row 2 and end in row 3

mysql> SELECT * FROM myexperience LIMIT 1;
+-----------------+---------------+-----+------+
| myexperience_id | experience    | pay | year |
+-----------------+---------------+-----+------+
|               1 | PHP Developer |  56 |    4 | 
+-----------------+---------------+-----+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM myexperience LIMIT 2;
+-----------------+--------------------+-----+------+
| myexperience_id | experience         | pay | year |
+-----------------+--------------------+-----+------+
|               1 | PHP Developer      |  56 |    4 | 
|               2 | MySQL System Admin |  60 |    4 | 
+-----------------+--------------------+-----+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM myexperience LIMIT 2,3;
+-----------------+--------------------+-----+------+
| myexperience_id | experience         | pay | year |
+-----------------+--------------------+-----+------+
|               3 | Linux System Admin | 100 |    4 | 
|               4 | PHP MYSQL          |  56 |    3 | 
+-----------------+--------------------+-----+------+
2 rows in set (0.00 sec)

WHERE clause to filter the return data

• query 1: return only record that year=3
• query 2: return only records that pay greater than 56

mysql> SELECT * FROM myexperience WHERE year=3;
+-----------------+------------+-----+------+
| myexperience_id | experience | pay | year |
+-----------------+------------+-----+------+
|               4 | PHP MYSQL  |  56 |    3 | 
+-----------------+------------+-----+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM myexperience WHERE pay > 56;
+-----------------+--------------------+-----+------+
| myexperience_id | experience         | pay | year |
+-----------------+--------------------+-----+------+
|               2 | MySQL System Admin |  60 |    4 | 
|               3 | Linux System Admin | 100 |    4 | 
+-----------------+--------------------+-----+------+
2 rows in set (0.00 sec)

WHERE clause and AND to filter the return data

We want only the return records that the pay column is larger than 50 and the year is equal to 4

mysql> SELECT * FROM myexperience WHERE pay > 50 AND year=4;
+-----------------+--------------------+-----+------+
| myexperience_id | experience         | pay | year |
+-----------------+--------------------+-----+------+
|               1 | PHP Developer      |  56 |    4 | 
|               2 | MySQL System Admin |  60 |    4 | 
|               3 | Linux System Admin | 100 |    4 | 
+-----------------+--------------------+-----+------+
3 rows in set (0.00 sec)

WHERE clause and OR to filter the return data

We select all records that the pay column is larger than 50 or the year is equal to 4.

mysql> SELECT * FROM myexperience WHERE pay > 50 OR year=4;
+-----------------+--------------------+-----+------+
| myexperience_id | experience         | pay | year |
+-----------------+--------------------+-----+------+
|               1 | PHP Developer      |  56 |    4 | 
|               2 | MySQL System Admin |  60 |    4 | 
|               3 | Linux System Admin | 100 |    4 | 
|               4 | PHP MYSQL          |  56 |    3 | 
+-----------------+--------------------+-----+------+
4 rows in set (0.00 sec)

WHERE clause with AND or OR keyword

• We use AND or OR together in the select statement.

mysql> SELECT * FROM myexperience WHERE year=4 OR year=3 AND pay > 56;
+-----------------+--------------------+-----+------+
| myexperience_id | experience         | pay | year |
+-----------------+--------------------+-----+------+
|               1 | PHP Developer      |  56 |    4 | 
|               2 | MySQL System Admin |  60 |    4 | 
|               3 | Linux System Admin | 100 |    4 | 
+-----------------+--------------------+-----+------+
3 rows in set (0.00 sec)

Where clause using IS operator in mysql

Create the myskill table and insert some sample records

mysql> CREATE TABLE myskill
    -> (
    ->  myskill_id    INT NOT NULL AUTO_INCREMENT,
    ->  description   VARCHAR(200),
    ->  experience_id INT NOT NULL,
    ->  PRIMARY KEY(myskill_id)
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO myskill(myskill_id, description, experience_id) 
    -> VALUES(null,null,1),
    ->  (null,'Expert',2),
    ->  (null,'Expert in Linux',3),
    ->  (null,null,5);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM myskill;
+------------+-----------------+---------------+
| myskill_id | description     | experience_id |
+------------+-----------------+---------------+
|          1 | NULL            |             1 | 
|          2 | Expert          |             2 | 
|          3 | Expert in Linux |             3 | 
|          4 | NULL            |             5 | 
+------------+-----------------+---------------+
4 rows in set (0.00 sec)

Where clause and IS NOT NULL

mysql> SELECT * FROM myskill WHERE description IS NOT NULL;
+------------+-----------------+---------------+
| myskill_id | description     | experience_id |
+------------+-----------------+---------------+
|          2 | Expert          |             2 | 
|          3 | Expert in Linux |             3 | 
+------------+-----------------+---------------+
2 rows in set (0.00 sec)

Where clause and IS NULL

Return only when the column has a null value.

mysql> SELECT * FROM myskill WHERE description IS NULL;
+------------+-------------+---------------+
| myskill_id | description | experience_id |
+------------+-------------+---------------+
|          1 | NULL        |             1 | 
|          4 | NULL        |             5 | 
+------------+-------------+---------------+
2 rows in set (0.00 sec)

Where clause using IN to return records

• First get all the experince_id from myskill table
• Secondly, using the return result from myskill table to query in the myexperience table.
• This type of query we called subquery.

mysql> SELECT * FROM myexperience 
    ->    WHERE myexperience_id IN
    ->    (SELECT experience_id FROM myskill); 
+-----------------+--------------------+-----+------+
| myexperience_id | experience         | pay | year |
+-----------------+--------------------+-----+------+
|               1 | PHP Developer      |  56 |    4 | 
|               2 | MySQL System Admin |  60 |    4 | 
|               3 | Linux System Admin | 100 |    4 | 
|               5 | C/C++              |   0 |    0 | 
+-----------------+--------------------+-----+------+

LIKE to find text

• Percentage (%) wildcard to make any character zero or more string
• Underscore (_) to make a single character or space

mysql> SELECT * FROM myexperience WHERE experience LIKE '%PHP%';
+-----------------+---------------+-----+------+
| myexperience_id | experience    | pay | year |
+-----------------+---------------+-----+------+
|               1 | PHP Developer |  56 |    4 | 
|               4 | PHP MYSQL     |  56 |    3 | 
|               6 | PHP/MySQL     |   0 |    3 | 
+-----------------+---------------+-----+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM myexperience WHERE experience LIKE '_/C++';
+-----------------+------------+-----+------+
| myexperience_id | experience | pay | year |
+-----------------+------------+-----+------+
|               5 | C/C++      |   0 |    0 | 
+-----------------+------------+-----+------+
1 row in set (0.04 sec)
mysql> SELECT * FROM myexperience WHERE experience LIKE '______System______';
+-----------------+--------------------+-----+------+
| myexperience_id | experience         | pay | year |
+-----------------+--------------------+-----+------+
|               2 | MySQL System Admin |  60 |    4 | 
|               3 | Linux System Admin | 100 |    4 | 
+-----------------+--------------------+-----+------+
2 rows in set (0.00 sec)