When should we use `` or '' in mySQL?

Question

CREATE TABLE counties ( id int(11) NOT NULL auto_increment, county tinytext NOT NULL, PRIMARY KEY (id) ) TYPE=MyISAM;

-- 
INSERT INTO `counties` VALUES (1, 'Alachua County');

I have seen some patterns here. People uses `` to surround field names and use '' to surround values. Is that true?

Or the major reason to do so is because then we can put the code in a text file and import into the database.

thank you

Answer 1

Yes, you use backticks [`] to surround field, table, and database names. You don't really need it unless you are using spaces in your field/table names, or words that have special meaning in SQL (ie: from, where)

Single quotes ['] are use to surround strings.

Answer 2

Using `` to surround field names is specific to MySQL afaik; you might want to avoid it if you want your SQL to be portable. The table.column or schema.table.column notation usually works well unless you've done something silly like put spaces in your table/column names.

Using table.column also makes it much harder to get lost if you're looking at a large dtd with lots of tables and columns.