If you haven't set up MySql on your computer, you need to go back to the supplementary materials and follow the instructions.
After you have successfully installed mysql and made sure that you can just type:
$ mysql -u root -p
and then type in the password that you chose during the installation.
You should see something like this:
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 46 Server version: 8.0.13 MySQL Community Server - GPL Copyright (c) 2000, 2018, 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>
To quit the server simply type:
mysql> quit
We will show some basic operations with the MySQL server. In the SQL Statement Syntax you will find in detail what the SQL statements below do and what is their full syntax.
To see which databases already exist we use SHOW databases:
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| movies |
| mysql |
| performance_schema |
| pets |
| sys |
+--------------------+
6 rows in set (0.00 sec)
To create an new database named fellowship_of_the ring we use CREATE DATABASE fellowship_of_the_ring:
mysql> CREATE DATABASE fellowship_of_the_ring;
Query OK, 1 row affected (0.02 sec)
We can see now if our database was created:
mysql> SHOW DATABASES;
+------------------------+
| Database |
+------------------------+
| fellowship_of_the_ring |
| information_schema |
| movies |
| mysql |
| performance_schema |
| pets |
| sys |
+------------------------+
7 rows in set (0.00 sec)
We now want to create a table in the database we created. We can do that using
CREATE DATABASE fellowship_of_the_ring but first we want to specify which database we will be using:
mysql> USE fellowship_of_the_ring;
Database changed
We are now ready to create a table of the heroes in the Fellowship of the Ring:
mysql> CREATE TABLE heroes
(
id INT unsigned NOT NULL AUTO_INCREMENT, # Unique ID for the record
name VARCHAR(150) NOT NULL, # Name of the hero
weapon VARCHAR(150) NOT NULL, # Weapon of choice
race VARCHAR(150) NOT NULL, # Race of the hero
PRIMARY KEY (id) # Make the id the primary key
);
The available data types you can use are explained in Data Types.
We can now check if the table has been created using the SHOW tables statement:
mysql> SHOW tables;
+----------------------------------+
| Tables_in_fellowship_of_the_ring |
+----------------------------------+
| heroes |
+----------------------------------+
1 row in set (0.01 sec)
We use DESCRIBE to see information about the columns of a table:
mysql> DESCRIBE heroes;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(150) | NO | | NULL | |
| weapon | varchar(150) | NO | | NULL | |
| race | varchar(150) | NO | | NULL | |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
To add records to a table we use INSERT...VALUES statement:
mysql> INSERT INTO heroes (name, weapon, race) VALUES
-> ('Frodo', 'Sword', 'Hobbit'),
-> ('Sam', 'Sword', 'Hobbit'),
-> ('Gandalf', 'Staff', 'Istari'),
-> ('Gimli', 'Axe', 'Dwarf'),
-> ('Legolas', 'Bow', 'Elf'),
-> ('Aragorn', 'Sword','Man');
Query OK, 6 rows affected (0.10 sec)
Records: 6 Duplicates: 0 Warnings: 0
To retrieve records from a table we can use a SELECT statement, and “*” to get all columns:
mysql> SELECT * FROM heroes;
+----+---------+--------+--------+
| id | name | weapon | race |
+----+---------+--------+--------+
| 1 | Frodo | Sword | Hobbit |
| 2 | Sam | Sword | Hobbit |
| 3 | Gandalf | Staff | Istari |
| 4 | Gimli | Axe | Dwarf |
| 5 | Legolas | Bow | Elf |
| 6 | Aragorn | Sword | Man |
+----+---------+--------+--------+
6 rows in set (0.00 sec)
We can select specific columns and rows by using the WHERE clause:
mysql> SELECT name FROM heroes WHERE weapon="Sword";
+---------+
| name |
+---------+
| Frodo |
| Sam |
| Aragorn |
+---------+
3 rows in set (0.00 sec)
To delete a record from a table we can use a DELETE statement to delete a record from a table:
mysql> DELETE FROM heroes WHERE race='Man'; Query OK, 1 row affected (0.04 sec) mysql> SELECT * FROM heroes; +----+---------+--------+--------+ | id | name | weapon | race | +----+---------+--------+--------+ | 1 | Frodo | Sword | Hobbit | | 2 | Sam | Sword | Hobbit | | 3 | Gandalf | Staff | Istari | | 4 | Gimli | Axe | Dwarf | | 5 | Legolas | Bow | Elf | +----+---------+--------+--------+ 5 rows in set (0.00 sec)
Let's say that we want to add a column that specifies the gender of each hero.
We can use a ALTER TABLE...ADD statement and (optionally) an AFTER
clause to specify the location of the new column:
mysql> ALTER TABLE heroes ADD gender CHAR(1) AFTER weapon; Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESCRIBE heroes; +--------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(150) | NO | | NULL | | | weapon | varchar(150) | NO | | NULL | | | gender | char(1) | YES | | NULL | | | race | varchar(150) | NO | | NULL | | +--------+------------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
To delete a column use ALTER TABLE...DROP:
mysql> ALTER TABLE heroes DROP gender; Query OK, 0 rows affected (0.24 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESCRIBE heroes; +--------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(150) | NO | | NULL | | | weapon | varchar(150) | NO | | NULL | | | race | varchar(150) | NO | | NULL | | +--------+------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)