

Looking to learn: SQL in MySQL and SQLlite - LookingToLearn

Hi,<p>I'm a long time programmer in C, Python, and a range of other languages.  The need has arisen for me to write something that uses a database, and I'm looking for advice on where to start.<p>A database consists of a collection of tables, and SQL is the language used to create, populate, manipulate, query and destroy the tables and their contents.<p>Can anyone suggest a non-patronising, information dense tutorial or "How To" guide?  Everything I've found from fairly comprehensive searching seems fluffy or way too caught up in its own gravitas.<p>Suggestions welcome, and thanks in advance.
======
drpancake
I'd recommend just getting on with it. MySQL is the obvious first choice as
it's widespread and well documented. Get it running locally then install the
MySQLdb package for Python.

Note that in practice SQL is a small subset of the languages accepted by
MySQL, PostgreSQL, SQLite, Oracle et al. Each defines it's own custom dialect
but the concepts are mostly universal.

At first I would use the 'mysql' command-line utility so you can see exactly
what's going on; this comes bundled with the installer. Have a think about how
your data would fit into rows and columns, but don't over-analyse too much -
it takes practice to start thinking in a relational way.

Create your tables and insert some example data. Now in Python connect to your
local instance and try querying for some rows (hint: DictCursor is useful,
albeit wasteful if you care about performance).

Now read about database normalization and transactions. You should be well on
your way!

~~~
LookingToLearn
You'd recommend MySQL over SQLite? The latter comes "baked in" to Python, so
no installation required, and I was planning to go with that as a starter to
avoid any problems with installation and configuring.

It would be easy to write a command line for that so I could then experiment.
Thoughts?

And any suggestions for guide to SQL syntax and semantics?

Many thanks.

~~~
drpancake
Good point - SQLite is even easier to get started with. I believe its command-
line tool comes bundled on most distros as 'sqlite3'.

As for syntax and semantics... it's so simple that anything on Google will do.
The interesting part is normalization - again I'm sure there's plenty of free
guides.

~~~
LookingToLearn
I'm pretty sure normalization won't be a problem in my context and with my
background. My requirement is simple, and the design straight-forward.

Many thanks for your response.

~~~
drpancake
When I say normalization that includes simple cases. For example using foreign
keys to separate out your data - pretty common in most schemas.

Good luck!

~~~
LookingToLearn
Indeed. And thanks again.

