MYSQL NOTES

Posted on In

This is a note of MySQL datbase for future review

1. Basic Concept#

  1. DB: database, a container used to store data.
  2. DBMS: database management system, a system used to create or manamge database
  3. SQL: strucutred query language, a kind of way to communicate with database.

2. DQL (Data query language)#

1. Syntax#

select col_name
from table_name;

2. Character#

  1. col_name can be a single or muliple column, constant value, expression, function
  2. the result is a virtual table

3. Example#

  1. Query a single column
    • select col_name from table_name;
  2. Query multiple columns
    • select col_name1, col_name2 from table_name;
  3. Query all columns
    • select * from table_name;
  4. Query constant value
    • select * cons_val
    • constant value with char and date must be quoted with single quote.
  5. Query function
    • select function_name(parm_list)
  6. Query expression
    • select 100/1234;
  7. alias
    • as
    • space
  8. distinct -> reduce duplication
    • select distinct col_name from table_name;
  9. + // add operation
    • select number1 + number2
    • select character + number // trying to convert character to number. If succeed, continue calculation. If failed, regard character as 0.
    • select null + any value // result would be always null
  10. concat function // do characters concatination
    • select concat(char1, char2, char3, …);
  11. ifnull function // identify xxx is null or not null. if null, return 0 or return original value
    • select ifnull(xxx, 0) from employees;
  12. isnull function // identify a col is null or nut, if null, return 1 or return 0