SQL(Structured Query Language) is a special-purpose language developed to work with relational databases. More specifically it’s used to manage and manipulate data in those databases. While SQL won’t be used to write the next big web application it’s useful in interacting with the database that would power that application. For example, think about Twitter — without the user data, tweets, and follower-following associations that are stored in a database the application wouldn’t exist.
Though SQL has a single purpose, it is very flexible and scalable. It is used by many different database systems and can be used to build small to enterprise-scale applications.
SQL is EVERYWHERE!!
As a full-stack web developer, it is important to understand how to use and navigate databases based on SQL. This series is my attempt at doing that with MySQL. I decided to focus my learning on MySQL because it is one of the leading database management systems, it’s open-source and can be used across platforms(Linux, Windows, MAC).
Before jumping in, it is important to understand some SQL database basics.
MySQL is specifically a RELATIONAL database management system meaning it stores data in a tabular structure similar to an excel spreadsheet. Below is an example of a ‘User’ table in a database.
The columns are defined properties that we want to store about each user and each user’s record is stored as a row. A record is just information referring to one specific user. When naming columns in a table it is important to follow lowercase and snake_case conventions (e.g email or email_address).
Each table must have a primary key which is a unique identifier for each entity in that table. In the above example, and typically that primary key is the id. Using a property like ‘name’ would not work because multiple users can have the same name. A database can have more than one table, and typically they do. The database schema for an e-commerce website for an e-commerce site would look something like this:
Tables in the database are linked using foreign keys. In the orders table, the ‘user_id’ is a foreign key that is linked to the primary key ‘id’ in the users table.
Now that the foundations are covered let’s get MySQL installed and set up so we can delve into querying!
There are three ways of managing the server and databases:
- Terminal/CLI/Shell (MySQL Community Server)
- Desktop tools (MySQL Workbench)
- Web-based tools (phpMyAdmin)
This series will focus on the first two. Click the links above to download and install MySQL Community Server and MySQL Workbench. During the installation process for the former, you will be asked to create a root account (Take note of the root password).
If you are on a mac, upon completion in your terminal run :
echo 'export PATH="/usr/local/mysql/bin:$PATH"' >> ~/.bash_profilemysql --version
On Windows follow this guide.
This adds MySQL to your PATH and confirms that it’s running globally.
So now that MySQL is installed and setup, log in as the root user from the terminal using the root password you created during installation.
mysql -u root -p
Upon successful login your terminal should look like the below:
We will start by creating a new user that will act as the database administrator.
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'username'@'localhost';
NB: It’s a common convention in SQL to use CAPS for commands and to signify the end of a query with a semicolon (;).
The following are some commands to keep in mind when managing users in MySQL:
SHOW GRANTS FOR 'username'@'localhost';//Remove privileges
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'localhost';//Delete user
DROP USER 'username'@'localhost';
We have covered a lot so far, so I will end here for now. You can log out of the root account by typing ‘exit’.
In Part II we will cover writing SQL code in the command line and the workbench GUI tool to create databases and tables, and perform(CRUD) operations on data from these tables.