MySQL 002

Haebin Ethan Jeong·2020년 8월 11일
0

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.
profile
I'm a Junior studying Economics and Computer Science at Vanderbilt University.

0개의 댓글