Introduction

Now that the MySQL database is installed and I have a LAMP stack configured (completed Install MySQL Lab first), I can play around with the database and its capabilities.

---

Basic Operations with MySQL

Here are some basic operations with the MySQL server. SQL Statement Syntax explains in detail the rich syntax and functionality of the SQL statements that are illustrated below.

Log into your MySQL Server:

#> mysql -u root -p

Showing existing databases:

mysql> SHOW DATABASES;

Creating a new database:

mysql> CREATE DATABASE pets;

Check if the database has been created with SHOW DATABASES;

---

Table Creation

Creating a table inside a database. First, pick the database in which you want to create the table with a USE:

mysql> USE pets

The USE statement tells MySQL to use pets as the default database for subsequent statements. Next, create a table with a CREATE TABLE statement:

CREATE TABLE cats
(
  id              INT unsigned NOT NULL AUTO_INCREMENT, # Unique ID for the record
  name            VARCHAR(150) NOT NULL,                # Name of the cat
  owner           VARCHAR(150) NOT NULL,                # Owner of the cat
  birth           DATE NOT NULL,                        # Birthday of the cat
  PRIMARY KEY     (id)                                  # Make the id the primary key
);

Data types you can use in each column are explained in Data Types Using Primary Keys explains the concept of a primary key. What follows a # on each line is a comment, which is ignored by the mysql client; see Comment Syntax for other comment styles.

Check if the table has been created with a SHOW TABLES statement:

mysql> SHOW TABLES;

---

DESCRIBE shows information on all columns of a table:

mysql> DESCRIBE cats;

---

Table Records

Adding records into a table. Use, for example, an INSERT...VALUES

INSERT INTO cats ( name, owner, birth) VALUES
  ( 'Siggy', 'Harold', '2018-01-05' ),
  ( 'Freya', 'Gutenberg', '2017-11-10' ),
  ( 'Patches', 'Charlie', '2015-05-15' );

• See Literal Values for how to write string, date, and other kinds of literals in MySQL.

Retrieving records from a table. Use a SELECT statement, and “*” to match all columns:

mysql> SELECT * FROM cats;

To select specific columns and rows by a certain condition using the WHERE clause:

mysql> SELECT name FROM cats WHERE owner = 'Charlie';

---

Deleting in Tables

Use a DELETE statement to delete a record from a table, specifying the criterion for deletion with the WHERE clause:

mysql> DELETE FROM cats WHERE name='Freya';

---

Adding or deleting a column from a table

Use an ALTER TABLE...ADD statement to add a column. You can use, for example, an AFTERclause to specify the location of the new column:

mysql> ALTER TABLE cats ADD gender CHAR(1) AFTER name;

---

SHOW CREATE TABLE shows a CREATE TABLE statement, which provides even more details on the table:

mysql> SHOW CREATE TABLE cats\G

---

Use ALTER TABLE...DROP to delete a column:

mysql> ALTER TABLE cats DROP gender;