MySQL 002

1. How to show all the rows except one certain row:

select * from sample21 where no<>2 will show you all the rows besides the row with no.2

2. Use SINGLE QUOTES, not DOUBLE QUOTES!

3. You cannot use '=' with null values:

• You CANNOT do select * from sample21 where birthday=NULL;
• You MUST do select * from sample21 where birthday is null; or select * from sample21 where birthday is not null;

4. How to properly use OR & AND:

• WRONG: select * from sample24 where no=1 or 2;
• CORRECT: select * from sample24 where no=1 or no=2;
• AND is calculated sooner than OR
  - For example, select * from sample24 where a=1 or a=2 and b=1 or b=2; is meant to search rows with where (a=1 or a=2) and (b=1 or b=2), but it will search something like this: where a=1 or (a=2 and b=1) or b=2

5. Using Like and %_:

• like will return data that partially satisfies certain conditions.
• %_ means the part of the data that the user is looking for.
• [word]% will search data that starts with the 'word'.
• %[word] will search data that ends with the 'word'.
• %[word]% will search any data that contains the 'word' anywhere within it.

6. Escape Character

• Either |% or 'It''s' (two apostrophes).

7. Order By - single row

• order by doesn't affect the actual data. Rather, it just displays you the ordered data.
• How to order your data in ascending order: select * from sample31 order by age; or select * from sample31 order by age asc;
• How to order your data in descending order: select * from sample31 order by age desc

8. Order By - Multiple rows

• order by a will order the data in a column, but b column will stay the same. So, data in two rows don't match.
• To solve this problem, we should do order by a,b so that data in two columns can match.
• select * from sample32 order by a asc, b desc; will rearragne the a column in ascending order and b column in descending order.

9. Treating NULL value in mysql

• When ordering null values, MySQL treats NULL value as the smallest value.

10. Limit command will limit the number of columns

• limit command can only be used in MySQL and PostgreSQL.
• select * from sample33 limit 3 will return three columns.
• select * from sample33 order by no desc limit 3; will return three columns with the highest 'no' because it's desc.