SQL

#Installing MySQL

Oracle bought MySQL. The MySQL home page tries to sell you the enterprise version for $10,000. Look for the free "community" version: http://dev.mysql.com/downloads/mysql/

For OS X you have a choice of a .tar package or a .dmg package.

A .tar package can be installed with curl.

A .dmg package installs with the Macintosh installer.

After installing the software, look in your System Preferences for the MySQL Preference Pane. You may need to download and install this from here: https://dev.mysql.com/doc/refman/5.7/en/osx-installation-prefpane.html

Click the MySQL Preference Pane to start the server.

You may need to set up a path in the terminal. These commands should work:

alias mysql=/usr/local/mysql/bin/mysql
alias mysqladmin=/usr/local/mysql/bin/mysqladmin

Alternatively, open the file .zshrc in your home directory. In Atom use command-shift-period to see hidden files. Add these lines at the bottom:

# Added for MySQL
export PATH="/usr/local/mysql/bin:$PATH"

To use MySQL from the terminal as root:

mysql -u root -p

You'll then be prompted for a password. The MySQL Installer should have given you a password. Copy and paste it (it won't be visible in the terminal). You should then see:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 75
Server version: 5.7.9

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

Next, MySQL will demand that you change the password before you can do anything:

SET PASSWORD = PASSWORD('pwd');

You can change pwd to any password you want.

#Starting MySQL Command Line Interface

Start the MySQL server from the Preference Pane.

To enter as root:

mysql -u root -p

To enter as a user:

mysql -u username -p
mysql -u lrngsql -p //xyz

#Exiting MySQL Command Line Interface

To get out of entry mode:

    -> \c

To get out of MySQL:

mysql> exit

or

mysql> quit

Additionally, entry mode has two sub-modes: double-quote mode and single-quote mode. If your prompt is

    ">

Then enter a double-quote " to return to the standard prompt ->

If your prompt is '> then enter a single-quote ' to return to the standard prompt ->

#Load Data From an External File

mysql> source /Users/TDK/playground/mysql/LearningSQLExample.sql

#Create a Database

Login as root:

mysql -u root -p

Enter password. pwd

Create a database:

create database humans;

Grant access to a user:

grant all privileges on humans.* to 'lrngsql'@'localhost' identified by 'xyz';

Exit or quit.

exit;

#Login As a User

Login as a user:

mysql -u lrngsql -p

Then use a database:

use humans;

Alternatively combine these two commands:

mysql -u lrngsql -p humans

#Create a Table

Use Atom to write long commands. Save the file with .sql or select SQL from the file type menu.

CREATE TABLE person 
 (person_id SMALLINT UNSIGNED,
  fname VARCHAR(20),
  lname VARCHAR(20),
  gender CHAR(1),
  birth_date DATE,
  street VARCHAR(30),
  city VARCHAR(20),
  state VARCHAR(20),
  country VARCHAR(20),
  postal_code VARCHAR(20),
  CONSTRAINT pk_person PRIMARY KEY (person_id)
);

#Insert Data

INSERT INTO users (name)
values ('jeff')