MySQL for beginners

Install

• Homebrew -> http://brew.sh
• brew install mysql
• or: http://dev.mysql.com/downloads/mysql/5.5.html#macosx-dmg
• SequelPro -> http://www.sequelpro.com

Start

• Connect to root@localhost
• Graphical vs. CLI vs. progamitacal

Basic Structure

1. What is Database?
2. What is Table?
   • columns + datatype (INT, VARCHAR, TEXT)
   • good practice: first column id as primary key & auto increment
3. Setup:
   • add table users, columns: id (INT), name VARCHAR(255), lastname VARCHAR(255)
   • add table tracks, columns: id (INT), user_id (INT), name VARCHAR(255)

CREATE TABLE `users` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255),
  `lastname` VARCHAR(255),
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE `tracks` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT(11),
  `name` VARCHAR(255),
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

Commands

INSERT

 INSERT INTO `users` SET `name` = "Marie";

or multiple line style:

 INSERT INTO `users` (`id`, `name`, `lastname`) VALUES (NULL, 'Marie', 'Parker'), (NULL, 'Peter', 'Parker');

SELECT

All users:

SELECT * FROM `users` 

WHERE

WHERE allows to filter output e.g. All users with name ‘peter’:

SELECT * FROM `users` 
WHERE `name` = 'Peter'

All users whos name contains ‘e’ (‘%’ means don’t care):

SELECT * FROM `users` 
WHERE `name` LIKE '%e%'

Combine conditions:

SELECT `name`, `id` FROM `users` 
WHERE `name` = 'Peter' OR `lastname` = 'Parker')

Select only specific columns:

SELECT `name`, `id` FROM `users` 
WHERE `name` = 'Peter'

UPDATE

Combination of INSERT & WHERE syntax:

UPDATE `users` SET `lastname` = 'muller' 
WHERE `name` = 'Peter';

Best practice, use primary key:

UPDATE `users` SET `lastname` = 'muller' 
WHERE `id` = 1;

SELECT .. JOIN

select & filter data across multiple tables.

Quick and dirty:

SELECT * FROM `users`, `tracks`	
WHERE `user`.`name` = 'Peter' AND `tracks`.`user_id` = `user`.`id`

Better: (see JOIN as dot product of users x tracks)

SELECT * FROM `users`
JOIN `tracks` ON `tracks`.`user_id` = `user`.`id`
WHERE `user`.`name` = 'Peter'

Even include empty rows:

SELECT * FROM `users`
LEFT JOIN `tracks` ON `tracks`.`user_id` = `user`.`id`

==== next session ===

More on SELECT

• Functions
• order by
• limit
• group
• indexes, constraints
• insert select

More

• csv import
• mysql CLI
• variables
• master/slave
• access rights
• transaction
• rollback