[MySQL] Reset Auto Increment

Sometimes when we create a program that was paired with a database, we test it like hundred or thousand times before we say that it was working correctly. As a result of this the id, which was always auto incremented will now be starting in like more or less 100 so often we just delete the table and create it again for the id to start again on zero. There is a way to reset the auto increment counter by altering the table itself. Here's the code:

This will start the counter at zero

ALTER TABLE tbl_name AUTO_INCREMENT = 0

This will start the counter at 10

ALTER TABLE tbl_name AUTO_INCREMENT = 10

MySQL Tricks

I was rummaging on my college stuff last night when I came across to some of my database subject notes.

Displays all the data that ends with letter 'a'

SELECT * FROM table1 WHERE column1 LIKE '%a';

Displays all the data with letter 'a'

SELECT * FROM table1 WHERE column1 LIKE '%a%';

Displays all the data that starts with letter 'a'

SELECT * FROM table1 WHERE column1 LIKE 'a%';

Displays all the data but limits the displayed data to only 4 characters
note: no spaces between the underscores. I just emphasize that there are four underscores

SELECT * FROM table1 WHERE column1 LIKE '_ _ _ _';

Displays all the data that starts with 'an', 'be' and 'cy'

SELECT * FROM table1 WHERE column1 IN ("an", "be", "cy");

Displays all the data but sorted by its 'name'

SELECT * FROM table1 ORDER BY name;

Displays only the first 20 data

SELECT * FROM table1 LIMIT 20;

Displays the number of data that have 'asdf' entry

SELECT COUNT(*) FROM table1 WHERE column1 = 'asdf';

Determines what data have the highest entry

SELECT MAX(column1) FROM table1;

[MySQL] Inner Join, Left Join, Right Join

Often, common joins such as Inner Join, Left Join and Right Join confused people so I will have little demonstration to compare the difference between the three.

Sample Tables:

User
+------+---------+----------+
| id   | name    | location |
+------+---------+----------+
|    1 | erin    |        1 |
|    2 | roran   |        2 |
|    3 | vincent |        1 |
|    4 | travis  |        0 |
+------+---------+----------+

Location
+------+------------+
| id   | place      |
+------+------------+
|    1 | california |
|    2 | new york   |
|    3 | tenessee   |
|    4 | hawaii     |
|    5 | italy      |
+------+------------+

Inner Join:
INNER JOIN displays only the fields from User table who have pairs on Location table

SELECT User.id, User.name, Location.place
FROM User
INNER JOIN Location on User.location=Location.id;

+------+---------+------------+
| id   | name    | place      |
+------+---------+------------+
|    1 | erin    | california |
|    2 | roran   | new york   |
|    3 | vincent | california |
+------+---------+------------+

Left Join:
LEFT JOIN displays the all fields from User table then display place field its paired of

SELECT User.id, User.name, Location.place
FROM User
LEFT JOIN Location on User.location=Location.id;

+------+---------+------------+
| id   | name    | place      |
+------+---------+------------+
|    1 | erin    | california |
|    2 | roran   | new york   |
|    3 | vincent | california |
|    4 | travis  | NULL       |
+------+---------+------------+

Right Join:
RIGHT JOIN displays the all fields from Location table then display id and name fields its paired of

SELECT User.id, User.name, Location.place
FROM User
RIGHT JOIN Location on User.location=Location.id;

+------+---------+------------+
| id   | name    | place      |
+------+---------+------------+
| NULL | NULL    | tenessee   |
| NULL | NULL    | hawaii     |
| NULL | NULL    | italy      |
|    1 | erin    | california |
|    2 | roran   | new york   |
|    3 | vincent | california |
+------+---------+------------+

Note: In the example above, User table was referred as the left table while Location table was referred to as Right table