5 Random Things I Wish I Knew Earlier About MySQL

1) `DESC table_name`

The DESC table_name command is used to display a table’s metadata:

• field names

• field types

• whether each field is nullable

• whether each field is a primary key

• etc

^ long ago, I used to use the SHOW COLUMNS command, but typing DESC takes significantly fewer characters.

2) `EXPLAIN query_statement`

The EXPLAIN keyword allows us to display the query execution plan ie. how MySQL plans to execute a query.

Let’s pretend that SELECT name, age, breed FROM dogs WHERE age < 6; is a complex query, and here, we simply add an EXPLAIN keyword in front.

3) Declarative VS Imperative programming

I learnt about this later in my career than I would’ve wished.

Imperative programming focuses on how a program executes by defining a sequence of steps to be executed.

Writing Python code (or Java, Go, etc) are examples of imperative programming. Here, we decide the sequence of steps to be executed. And if our output is crap, it is entirely on us.

Declarative programming focuses on our desired output.

Writing SQL is declarative — ie we specify the shape, and SQL decides the sequence of steps to execute in order to achieve the shape that we’ve specified.

4) ‘ VS “ VS `

• ' (single quote) is used to specify string literals.

• ` (backtick) is used to wrap column names with spaces in them

By default " is used to specify string literals. But if set SET sql_mode = 'ANSI', " is used to wrap column names with spaces in them.

Which might be why sometimes, " is used to specify strings, while other times, " is used to wrap column names.

I had a professor that told us — just don’t use " if you don’t want to deal with this ambiguity.

5) SELECT literals

Long ago, I started learning SQL by creating tables, inserting data, and using SELECT * FROM tablename WHERE whatever (don’t we all)

For the longest time, I was not aware the we could SELECT literals in MySQL (there’s not much use case I think, but still a cool thing)

Let’s as aliases to make our result look less weird:

Conclusion

Hopefully this was clear and easy to understand

If You Wish To Support Me As A Writer

• Buy my book — 101 Things I Never Knew About Python at https://payhip.com/b/vywcf

• Clap 50 times for this story

• Leave a comment telling me your thoughts

• Highlight your favourite part of the story

Thank you! These tiny actions go a long way, and I really appreciate it!

LinkedIn: https://www.linkedin.com/company/the-python-rabbithole/