CREATE Database, User and Table — Easy examples with MySQL.

Photo by Samuel Zeller on Unsplash

This blog post will be the first part of a MySQL Beginner Series of basics I will delve into, as I journey down this path of learning.

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

I will be using Xubuntu Linux 16.04.2 LTS (Xenial Xerus) and MySQL 5.7.20 for these exercises.

Primary Goals.

• CREATE a database on the current system.
• CREATE a table to house data.
• CREATE a user that can interact with the database and table(s).

The CREATE command is used to CREATE databases, tables, and users.

Getting started, I will log into MySQL as the root or system user to CREATE a database, table, and user as shown below:
:~$ mysql -u root -p

Upon entering the correct password (not shown) I am presented with a greeting and the MySQL prompt.

Welcome to the MySQL monitor. Commands end with ; or \g.
 Your MySQL connection id is 19
 Server version: 5.7.20–0ubuntu0.16.04.1 (Ubuntu)
Copyright © 2000, 2017, 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>

A database houses tables, which in turn stores the data we work with. Here, I make a projects database with this CREATE command:

mysql> CREATE DATABASE projects;
 Query OK, 1 row affected (0.00 sec)

To see the available databases (and the newly created projects database) on the system, use the SHOW DATABASES; command.

mysql> SHOW DATABASES;
 + — — — — — — — — — — — — — — +
 | Database                    |
 + — — — — — — — — — — — — — — +
 | information_schema          |
 | BowlingLeagueExample        |
 | BowlingLeagueModify         |
 | EntertainmentAgencyExample  |
 | EntertainmentAgencyModify   |
 | RecipesExample              |
 | SalesOrdersExample          |
 | SalesOrdersModify           |
 | SchoolSchedulingExample     |
 | SchoolSchedulingModify      |
 | bank                        |
 | hr                          |
 | mysql                       |
 | northwind                   |
 | performance_schema          |
 | projects                    |
 | sakila                      |
 | sys                         |
 | world_x                     |
 + — — — — — — — — — — — — — — +
 19 rows in set (0.00 sec)

Along with all the databases, I currently have on my system, projects can be seen 4th up from the bottom. We have created a database. Woot woot!

Without tables, a database by itself is not that useful. To CREATE a table, move into the database where you want the table (in this case the projects database). To accomplish that, issue the USE command in conjunction with the database name:
mysql> USE projects;
To view all present tables, if any, in a database the SHOW command is used for that as well:

mysql> SHOW TABLES;
 Empty set (0.00 sec)

Again, there are no tables here (notice the Empty set tag) so I will fix that in the next section.

The below command creates a simple friends table:

mysql> CREATE TABLE friends(
    -> friend_id INT NOT NULL AUTO_INCREMENT,
    -> first_name VARCHAR(30) NOT NULL,
    -> last_name VARCHAR(30) NOT NULL,
    -> cell_phone VARCHAR(15),
    -> age INT DEFAULT NULL,
    -> PRIMARY KEY (friend_id));
 Query OK, 0 rows affected (0.32 sec)

Briefly discussing these column names and data types.

• friend_id INT NOT NULL AUTO_INCREMENT – This column is of type integer and will automatically advance its value by 1 upon INSERT, to the next value each time a record is inserted into the friends table. Other options are available for the starting and increment values, however they are not shown or explored here. This column will contain only whole numbers.
• first_name VARCHAR(30) NOT NULL – A variable-length character data type that will accept a string value up to 30 characters in length. Cannot be left blank due to the NOT NULL CONSTRAINT
• last_name VARCHAR(30) NOT NULL – A variable-length character data type that will accept a string value up to 30 characters in length. Cannot be left blank due to the NOT NULL CONSTRAINT
• cell_phone VARCHAR(15) – A variable-length character data type that will accept a string value up to 15 characters in length. Can be left blank during inserts/updates.
• age INT DEFAULT NULL – An integer data type that accepts whole numbers and should no values be provided during inserts, will default to NULL. (Our friends may not wish to divulge their age to us lol!)
• PRIMARY KEY (friend_id)) – A CONSTRAINT that creates a unique identifier for each record in the table on the specified column, which in this case is the friend_id column. Values will never be duplicated for this column, ensuring integrity and a unique identifier.

With all that nitty-gritty taken care of, we are missing just one thing. A user. We need a user who can work and interact with the projects database.
Coming right up.

To CREATE a user, use the CREATE USER statement.
Check it out.

mysql> CREATE USER ‘this_user’@’localhost’ IDENTIFIED BY ‘password5’;
 Query OK, 0 rows affected (0.04 sec)

Note: This is a weak password, used only for demonstration purposes. You should always strive for hardened passwords.

I will go over the different parts of the CREATE USER statement below:

• 'this_user'@'localhost' – this_user is the name I gave for this particular user.
• 'this_user'@'localhost' – the @'localhost' indicates this user can connect from the local host or local machine.
• IDENTIFIED BY 'password5'; – one of many available options for setting/assigning passwords.

I still need to GRANT permissions to the user before he or she can interact with the database and table(s).

mysql> GRANT SELECT, INSERT ON projects.* TO ‘this_user’@’localhost’;
 Query OK, 0 rows affected (0.00 sec)

For the time being, I am only allowing this_user to have SELECT and INSERT privileges on all tables in the projects database, as noted by the .* . After all, I can’t just turn this_user loose with no monitoring out of the gate now, can I?!?!

Alright, we have configured a database, table, and user. Be sure and visit the next part in this series coming soon.

Next up in the series, I will dive into the ALTER command so we can make any adjustments to the friends table and this_user user. Don’t miss out on more great content!

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Be sure and visit the “Best Of” page over on my site Digital Owl’s Prose for a collection of my best blog posts.

Have I mentioned how much I love a cup of coffee?!?!

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

Originally published at joshuaotwell.com on October 30, 2017.