A 38-Year-Old's MYSQL Primer — First Steps / Table of Contents

A 38-year-old's MYSQL primer~ 

Alright, now the first real step. Day two. Late Saturday afternoon, I've come to sit in the library and slowly get the engine started.

1. So what, exactly, will we practice?

First, the reference book is 'SQL First Steps' — please read it on your own ;D 

I'll focus on what I personally dog-eared last year. It looks like a lot, but each example is fairly short. 

Primer 01

p.62 DESC command - a command that lets you look up a table's structure. Technically not a SQL command.

p.70 <> operator - can be used to build a condition that is true when the two values differ.

p.111 Limiting the output count after sorting — LIMIT(MySQL) VS TOP(SQL Server) VS ROWNUM+WHERE(Oracle)

p.113 LIMIT+OFFSET(MySQL)

p.136 CURRENT_TIMESTAMP Checking the system date. 

p.139 Adding and subtracting dates

Primer 02

p.140 CASE clause - combine various operators and functions to return values in a specific shape.

p.142 COALESCE function - returns a NULL value.

p.158 DEFAULT - stores a specified value instead of NULL as the initial value.

Primer 03

p.169 Updating multiple columns - in the SET clause of UPDATE you can specify multiple columns to update, separated by commas (,). 

p.193 Computing averages with the AVG aggregate function - when computing, use CASE to treat NULL as 0.

p.198 Specifying conditions with HAVING when using aggregate functions - understanding the internal processing order of GROUP BY and WHERE.

p.200 Grouping by multiple columns —  columns other than the ones listed in GROUP BY cannot be placed in the SELECT clause unless wrapped in an aggregate function.

Primer 04

p.204 Subquery (1) - using a subquery in the WHERE clause of DELETE. 

p.207 Subquery (2) - scalar values. 

p.209 Subquery (3) - using subqueries in SELECT, SET, and FROM clauses. 

Primer 05

p.219 Correlated subquery (2) - using NOT EXISTS to update a specific field to 'none.'  

p.219 Correlated subquery (3) - and now the main event, correlated subquery practice. 

p.221 Correlated subquery (4) - using IN to check whether a value exists within a set. 

Primer 06

p.230 Schema - database objects live inside a container called a schema.

p.240 ALTER TABLE for managing tables - extending a table's max length and adding columns.

+  DEFAULT setting revisited (via the ALTER statement)

p.243 Defining constraints - you can define constraints on a column or on a table.

p.258 Indexes are created via the CREATE INDEX command. 

p.260 EXPLAIN - lets you check whether the index is being used.

Primer 07

p.274 Chapter 7: working with multiple tables - (Oh, you really have to do all of this~ this is important~ ;D) set operations, cartesian product / cross join, inner join, outer join.

Primer 08

p.304 Relational model - refers to reshaping and splitting tables into correct forms. 

p.319 Normalization - refers to reshaping and splitting tables into correct forms.

2. Ah, one more warm-up — creating tables for testing~

Example source: http://download.hanbit.co.kr/exam/2231. Download and unzip.

Let's open the file produced by the unzip operation

A long~ long~ blah~ blah~ stretch of content comes out. Those statements drop, create, and insert values into specific named tables.

Select all of that, copy it, and paste it into the terminal~ The terminal will busily do a whole lot of work.

There'll be a few error messages here and there — no worries. That's just because the bulk copy-paste tried to execute comments as commands. You can ignore those.

3. HELLO WORLD: let's start with logging in. 

        1) Connect to MySQL

mysql -u root -p 

-> result

Alright, now the actual first step. Connection successful. So, what now? ...Sadly there are still one or two more config steps left.

2) List installed databases

 SHOW DATABASES; 

-> result 

3) Activate a specific database

 USE sample  

-> result 

2. I can't think of a second one yet.