Menu

Subscribe

Start Using MySQL Today

on linux, mysql, database, sql, ubuntu, debian, apt, install, tutorial

Install MySQL Server on Linux:

  • $ sudo apt-get update -y && sudo apt-get upgrade -y
  • $ sudo apt-get install mysql-server -y

Verify MySQL Server Installation:

  • $ mysql --version
  • Example output: mysql Ver 14.14 Distrib 5.7.23, for Linux (x86_64)
  • Start MySQL using $ sudo mysql -u root -p and enter the MySQL command-line:
Welcome to the MySQL monitor.  Commands end with ; or \g.  
Your MySQL connection id is <#>  
Server version: 5.7.23-0ubuntu0.18.04.1 (Ubuntu)

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.
  • Exit the MySQL command-line ( mysql> ) by typing \q
  • On exit get "Bye" message; you are returned to the shell command-prompt: $

Create Your First Database in MySQL

  • $ sudo mysql -u root -p
  • Show default databases: mysql> SHOW DATABASES;
    • Example: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)
  • Create a database called 'app': mysql> CREATE DATABASE app;
  • Show all databases again: mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| app                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)
  • Work out of the new 'app' database: mysql> USE app; _ result: Database changed

Create Your First MySQL Table in the 'app' Database

  • Show tables in 'app': mysql> SHOW TABLES; _ result: Empty set (0.00 sec)

In our app database, aptly named 'app', we will start by creating a users table with user id's, and then define some attributes (columns) that apply to each user, such as first_name, last_name, and subscription start/end dates (active_from, active_until).

When creating a table, you may end up building these long, run-on SQL "sentences" if you define all attributes up front. Remember: you can always add or change attributes/columns later on.

  • Create a table: mysql> CREATE TABLE users (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, username VARCHAR(20) NOT NULL UNIQUE, first_name VARCHAR(20), last_name VARCHAR(20), active_from DATETIME DEFAULT NULL, active_until DATETIME DEFAULT NULL);
  • If you typed in the statement correctly you should see output in the form of something like the following, indicating your table has successfully been created: Query OK, 0 rows affected (0.01 sec)

What's happening in this SQL statement?

In the above 'CREATE TABLE' SQL statement, we are setting user id as a primary key, so all other attributes/columns will revolve around and depend upon this identifier. The id number will auto_increment and will remain immutable, even if users come/go, get deleted, etc. No two users will ever have the same user id. The syntax to configure id's as such is: id INT NOT NULL PRIMARY KEY AUTO_INCREMENT.

Next, we define the data type used in each column: username is a string variable with a field length of 20 characters at most. We will go ahead and set username to UNIQUE to ensure that no two users can register using the same username. We also declare that username cannot be left empty with the flag NOT NULL. Then we add first and last name columns, again with the VARCHAR 'string' type, 20 characters maximum.

Finally, we define the subscription start/end date columns, active_from and active_until. We set the data types here to DATETIME which defaults to the following format: YYYY-MM-DD HH:MM:SS. However, as we may wish to manually (or later programmatically) set subscription start/end dates, we will instruct MySQL to leave the two column fields here empty for now using the syntax DEFAULT NULL.

  • Verify that your new table is now present (as in below) in the app database: mysql> SHOW TABLES;
+---------------+
| Tables_in_app |
+---------------+
| users         |
+---------------+
1 row in set (0.00 sec)
  • If you wish to see what's in the user table at present, do: mysql> SELECT * FROM users; _ result: Empty set (0.00 sec). (We will need to add some rows to the table!)

Adding Data to the 'users' Table

  • Add a first user, 'John', whose subscription is not yet active: mysql> INSERT INTO users VALUES (id, 'johnny5', 'John', 'Smith', NULL, NULL); _ result: Query OK, 1 row affected (0.01 sec). Check using SELECT * FROM users; where * means 'everything' in the table; this now displays the data in the table.
+--+--------+----------+---------+-----------+------------+
|id|username|first_name|last_name|active_from|active_until|
+--+--------+----------+---------+-----------+------------+
| 1|johnny5 |John      |Smith    |NULL       |NULL        |
+--+--------+----------+---------+-----------+------------+
1 row in set (0.00 sec)
  • Add a second user, 'Mary', whose subscription is active as of today, using NOW() for the active_from column: mysql> INSERT INTO users VALUES (id, 'mary5', 'Mary', 'Smith', NOW(), NULL); and display the results using SELECT * FROM users;:
+--+--------+----------+---------+-------------------+------------+
|id|username|first_name|last_name|active_from        |active_until|
+--+--------+----------+---------+-------------------+------------+
| 1|johnny5 |John      |Smith    |NULL               |NULL        |
| 2|mary5   |Mary      |Smith    |2018-08-19 21:03:37|NULL        |
+--+--------+----------+---------+-------------------+------------+
  • Now let's add a third user, 'John Henderson', whose suscription began one year ago and ends today. mysql> INSERT INTO users VALUES (id, 'johnny6', 'John', 'Henderson', NOW() - INTERVAL 1 YEAR, NOW());. This leads to the following values being added to the table:
+--+--------+-------+---------+-------------------+----------...+
|id|username|first_n|last_name|active_from        |active_unt...|
+--+--------+-------+---------+-------------------+----------...+
| 1|johnny5 |John   |Smith    |NULL               |NULL      ...|
| 2|mary5   |Mary   |Smith    |2018-08-19 21:03:37|NULL      ...|
| 3|johnny6 |John   |Henderson|2017-08-19 21:08:24|2018-08-19...|
+--+--------+-------+---------+-------------------+----------...+
3 rows in set (0.01 sec)

Querying the Table for Matching Entries

  • Get all users with the first name of 'John': mysql> SELECT * FROM users WHERE first_name='John'; _ result:
+--+--------+-------+---------+-------------------+----------...+
|id|username|first_n|last_name|active_from        |active_unt...|
+--+--------+-------+---------+-------------------+----------...+
| 1|johnny5 |John   |Smith    |NULL               |NULL      ...|
| 3|johnny6 |John   |Henderson|2017-08-19 21:08:24|2018-08-19...|
+--+--------+-------+---------+-------------------+----------...+
2 rows in set (0.00 sec)

  • Similarly, you can query all users with the last name of 'Smith' as follows: mysql> SELECT * FROM users WHERE last_name='Smith';

  • Or try selecting all users whose subscriptions have expired as follows: mysql> SELECT * FROM users WHERE active_until < NOW();

+--+--------+-------+---------+----------...+-------------------+
|id|username|first_n|last_name|active_fro...|active_until       |
+--+--------+-------+---------+----------...+-------------------+
| 3|johnny6 |John   |Henderson|2017-08-19...|2018-08-19 21:08:24|
+--+--------+-------+---------+----------...+-------------------+
1 row in set (0.00 sec)

Congratulations! You've now got a working, queryable, sample database with one table!

If you wish to continue learning about MySQL, try some more advanced SQL statements and functions from the following tutorial: https://dev.mysql.com/doc/refman/8.0/en/tutorial.html

Best of luck to you in your journey to learn SQL!

QUESTIONS/COMMENTS? Email me at adam [at] kernelmastery [dot] com.