MySQL Cheatsheet

  • Add a column column_name to table table_name
  • > alter table table_name add column_name column_type
  • Modify column
  • > alter table table_name modify column_name int unsigned NOT NULL auto_increment; > alter table comments change date date datetime;
  • Delete column
  • > alter table table_name drop column_name;
  • Rename a column
  • > alter table table_name change column_old_name column_new_name tinyint(3) unsigned;
  • Add column as key index
  • > alter table table_name add key(column_nam
  • Set auto_increment for column column_name:
  • > alter table table_name add key(column_name); > alter table table_name modify column_name int unsigned NOT NULL auto_increment;
  • Set a column as primary key:
  • alter table table_name add primary key(column_name);
  • Inner join* with cartesian effect elimination
  • > select * from table_name1, table_name2 where table_name1.column = table_name2.column; *inner join - only the entries which exist in both tables are included in the resulting table.
  • Left (outer) join*
  • > select * from table1 left outer join table2 ON table1.column = table2.column; *Left join - includes ALL the table entries in the left tables and then matches entries in the right table if they exist. An INNER JOIN will retrieve all records from both tables that have matching values for whatever column you're joining them on. Records from either table that don't match will not be retrieved. A LEFT JOIN will retrieve all records from the left (first) table, and only the records from the second table with matching values for the joining column, filling in any empty spaces with NULL values.
  • Group by - sampling of results and getting the first result out of several repeating.
  • DISTINCT ensures that only unique column values will be shown.
  • WHERE cannot be used with a group column function, like count(). In such cases HAVING should be used. Example:
  • > select tc.chapter,tc.chapter_name,count(ct.topic) from table_of_contents as tc LEFT OUTER JOIN chapter_topics as ct ON tc.chapter = ct.chapter GROUP BY tc.chapter HAVING count(ct.topic) >= 2 ;
  • Using the ORDER BY clause on the column topic, the results were automatically sorted. SQL's ordering of a column depends on its type, so since topic is of a string type, SQL knew to sort in alphabetical order, rather than numerically or by date.
  • LIMIT x[, y] - x represents the desired starting row in your results, y is optional and represents the number of rows to display.

blog comments powered by Disqus