1.1 Introduction

Here are some quick reminders about how to work with MySQL...

1.1.1 Which MySQL

which mysql

returns...

/usr/local/mysql/bin/mysql

1.1.2 Current MySQL Version

mysql -v

returns...

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.21 MySQL Community Server (GPL)
 
etc...

1.1.3 Get Help

mysql --help

1.1.4 MySQL Comments

/*
this is a
multiple-line comment
*/

1.1.5 Commands start at the MySQL prompt

mysql>

Note that all text commands must be first on line and end with a semi-colon ';'

1.1.6 Current Version

SELECT VERSION(), CURRENT_DATE;

1.1.7 Multi-line Commands

Entering return gives you a new line, the command isn't complete until the semi-colon is added. Entering return after the semi-colon executes the command.

SELECT
    -> USER()
    -> ,
    -> CURRENT_DATE;

1.1.8 Connect To A Database

mysql -h localhost -u root -p

passwords are case sensitive.

1.2 MySQL Commands

1.2.1 QUIT

\q

Not necessary to end with a semi-colon ;-)

1.2.2 SHOW

SHOW DATABASES;

1.2.3 USE

USE test

Where test is the name of a database.

Like QUIT, the USE command does not require a semicolon to follow it.

1.2.4 CREATE

CREATE DATABASE menagerie;

Where menagerie is the name of the new DB you want to create.

1.2.5 GRANT

GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';

In this case the '.*' means 'all tables' in the db named menagerie

1.2.6 SHOW TABLES

List Tables in a DB

SHOW TABLES;

1.2.7 CREATE TABLE

CREATE TABLE pet (
  name VARCHAR(20), 
  owner VARCHAR(20), 
  species VARCHAR(20), 
  sex CHAR(1), 
  birth DATE, 
  death DATE);

1.2.8 DESCRIBE TABLE

DESCRIBE pet;

Where pet is the name of the table

1.2.9 LOAD DATA

From External File into Table

LOAD DATA LOCAL INFILE '/Library/WebServer/Documents/Code hints/Pet-table.csv' INTO TABLE pet;

On a Mac you may want to add 'lines terminated by'

LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet LINES TERMINATED BY '\r';

Nota bene:

  1. datafiles should be tab delimited
  2. tab delimited files should be appended with .txt
  3. empty cells should contain "\N" (which will result in a value of 'null' being imported for that cell)

1.2.10 INSERT

To insert a single data record into a table

INSERT INTO pet
    -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

Nota bene:

  1. String and date values are specified as quoted strings here.
  2. Also, with INSERT, you can insert NULL directly to represent a missing value. (as opposed to the LOAD command above.)

1.2.11 Troubleshooting

When you have a problem and things don't work

SHOW WARNINGS\G

1.3 Quick reference

Name Command Details
? (\?) Synonym for `help'.
clear (\c) Clear the current input statement.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit (\e) Edit command with $EDITOR.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
help (\h) Display this help.
nopager (\n) Disable pager, print to stdout.
notee (\t) Don't write into outfile.
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (#) Rebuild completion hash.
source (.) Execute an SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
system (!) Execute a system shell command.
tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
use (\u) Use another database. Takes database name as argument.
charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.