Wednesday, February 1, 2012

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;

No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...