Are this good MySQL practices

Question

When I make a SQL query, for example, in database where there's a table named "employees", which is the best practice of writing?

SELECT 'name', 'surname', 'phone' WHERE 'city'='ny' FROM 'employees' ORDER BY 'name'

SELECT name, surname, phone, WHERE city=ny FROM employees ORDER BY name

or

SELECT employees.name, employees.surname WHERE employees.city=ny ORDER BY employee.name

And why? Is there a standard for this?

Answer 1

IMHO, the best standard is this:

select
    o.name as office_name,
    e.name as employee_name,
    count(*) as count
from employee e
left join office o on o.id = e.office_id
where e.name like 'a%'
group by 1
order by 1

Your goal is improved clarity and maintenance. The features/benefits demonstrated here are:

• Nothing in uppercase (ie select not SELECT)
• Only use backtick escapes for reserved words
• Table names in singular (ie employee not employees)
• View names in the plural (eg current_employees)
• Underscores separating name parts (ie no camelCase or flatcase etc)
• Primary keys always named id
• Foreign keys always named table_id
• Where column names collide in a query, aliased them as <table>_<column>
• Queries are formatted as above
  • Left justified, except columns, which are eash on their own line
  • Line breaks on major keywords
• Table aliases generally using all first letters of name parts, eg my_table_name mtn
• Group by and order by preferring numbers over expressions (if you change the expression, you don't need to the group by or order by clauses, otherwise you're violating the DRY principle)

This comes from many years of coding SQL and I've found this makes things easiest.

If you always have a consistent format, you'll find syntax errors easier to find too.