Despite the recent development of NoSQL databases such as MongoDB, Firebase and Redis, SQL databases are still very popular with developers.
SQL (structured query language) is the language used to interact with data in a wide variety of databases, including popular ones like MySQL, PostgreSQL, Oracle, and MS SQL Server.
This article will show you how to get started using MySQL, an open-source database which powers content management systems such as WordPress and eCommerce platforms like Shopify. It also powers social media platforms like Twitter.
You will learn about relational databases, some of their key concepts, how you can install and interact with MySQL database via the command-line and the modern SQL syntax to create and update, delete, and modify data in MySQL.
Understanding Object Relational Mappers
Most developers who work with relational databases don’t actually create raw SQL. They use libraries that perform object relational mapping, or ORM, more often than not.
These libraries transform your database tables into objects on the server-side. This allows you to use any object-oriented language you like to manipulate data.
ORMs do away with the need for writing SQL code. Instead, you can use your object-oriented programming knowledge to create, modify, delete, and read data from the SQL database. ORMs make relational databases easier and more intuitive to use.
Relational Database Management Systems (RDMS).
A relational database management software system is basically composed of two main components: the query and the database.
The database is simply a collection tables. You will find your actual data organized in each table in columns and rows. It is identical to a spreadsheet. Tables in a relational database can be linked—or related—based on data common to each of them.
The query language is used for manipulating and reading data from the database. The query language used to manipulate data in relational databases is a variation on SQL.
Some key concepts in SQL Databases
To illustrate the following SQL database concepts visually, I’ll use a database visualization program called DrawSQL. We’ll start by looking at data types and schemas.
Schemas and data types
A schema can be thought of as a blueprint which defines the overall structure and relationship between tables.
Take a look at the following schema UsersTable example:
You’ll see that each row in the table image is a dictionary. The key represents a column in a database, and the value represents the type data that can be stored there.
MySQL supports many data types. Data types also vary between SQL databases.
You’ll also notice the
idThe key accepts integer values and has a key at its end. This indicates that
idis defined as follows: The primary key constraint.
This is why the value of
idEach column must be unique and cannot be null. This means that two or more users cannot share the same primary keys, and each row can be identified using its primary key.
These rules together form the basis of the schema. Users table.
Let’s explore the constraints in MySQL databases.
We saw the primary constraint earlier, which ensures that a key’s value is unique for each column in the table.
MySQL constraints are rules that restrict or allow values to be stored in a table. They limit the data that can be inserted into the table to ensure data integrity and accuracy.
These constraints are common in MySQL:
NOT NULL: ensures that a column does not have a NULL value
UNIQUE: Ensures that all columns have different values
PRIMARY KEY: a combination of a NOT NULL and UNIQUE—uniquely identifies each row in a table
FOREIGN KEY: Used to connect two tables together
CREATE AN INDEX: Used to quickly create and retrieve data from a database
CHECK: Ensures that the columns meet a specific condition
DEFAULTIf no value is specified, : sets the default value for a column
For context, let’s suppose that the Users Table is used for storing registered users on an internet shopping website.
Typically, there must be a database for an online shopping site. ProductsTable and a Carts table. ProductsAll products will be included in the package. CartsEach item will be listed along with its quantity.
These are all the tables that have been created so far:
Each of these tables is a unique entity. This means that each table is unique. CartsFor example, table doesn’t need information about the user to function.
Technically, we have no reason to stop merging the carts data with the Users table. Because both sets of information relate to each other (a user has a cart), However, this approach can eventually lead to congested and difficult-to-manage tables.
Instead, we created a separate entity to make it more sustainable. CartsTable to store all cart-related information. By placing the foreign keys, we created a reference to other tables.
product_idIn the Carts table.
Carts can therefore access any information that the user has about them and the products they have, even though it doesn’t physically store any of these details.
Now that you have a basic understanding of MySQL, let’s dive in and install MySQL on our system.
Go to the MySQL Community Downloads Page to install MySQL on your computer. download the installer for MySQL.
Once the download is complete you can run the installer to complete the setup wizard to install MySQL.
Make sure to include the MySQL server product when choosing a setup type. I recommend the Developer Default option.
During setup, you will be asked for a username/password. These will be needed later to connect to the database.
After successful installation, you will be able to access the MySQL commands via the command line.
To use MySQL from a command line, however you will need to first authenticate yourself. Run the following command and enter your username in the placeholder.
mysql -u <your username> -p
When you are asked to enter your admin password, you will be granted permission to access MySQL via the command line.
Keep in mind, MySQL’s default username is RootThe password is empty by default.
MySQL Common SQL Operations
How to Create a Database
We use the following to create a MySQL database:
CREATE A DATABASEStatement followed by the database name
CREATE DATABASE shopping_site
A statement is a piece of code that tells a system to do something. SQL statements always end on a semicolon. SQL will only interpret the capitalized words to do something.
The above command will create a blank database named “databasename” shopping_siteMySQL
Run the following command to see all the databases in your MySQL instance.
How to create a table in your database
Use the following to create a table in your database.
CREATE A TABLEStatement followed by the table name, and a parenthesis containing a list of columns that we want to include in the table:
CREATE a Table users ( id INT PRIMARY KEY AUTO_INCREMENT first_name VARCHAR(255), last_name VARCHAR(255), email VARCHAR(255) UNIQUE NOT NULL, password VARCHAR(255), created_at TIMESTAMP, Updated_at TIMESTAMP );
For each column in UsersWe specified the data type for each column. We also added a number of constraints to the idAnd Email columns.
The command will create a structured, but empty table called UsersIn your database
A Table Creates New Records
Use the below to create a record in your table.
INSERT INTOfollowed by the table where you want to insert these values:
INSERT INTO users(first_name, last_name, email, password) VALUES( 'Kingsley', 'Ubah', '[email protected]', '12345678' );
In the first paraphrase, we defined the columns into which we want to insert the values. The second parenthesis identifies the values we want, in the correct order.
This command will insert these values into the specified columns of the Users table.
We set the standard for excellence idIf you set field to auto-incrementing the value will be added automatically. MySQL generates timestamps automatically, so we don’t have to enter one.
Reading from the Table
To query data from your database, use this
If we want everything from our collection, for instance UsersTable, we use the following statement
SELECT * FROM users;
The asterisk is (
*() is the entire database. This statement will return all columns and the entire table.
If we only need a subset, say idAnd EmailWe specify the columns instead of the asterisk
SELECT id, email FROM users;
There is no limit to the possibilities when it comes selecting data.
Dropping a Table
To delete a MySQL table completely, use the
DROPStatement followed by the table name
Drop Table users
This command can be dangerous so be careful! It will permanently erase all data in the Users table. Don’t confuse it also with the
DROP DATABASEstatement that deletes all of your database.
MySQL is an open-source relational management system for databases that powers a large portion of the web today. MySQL databases can be manipulated and stored using structured query language (or SQL).
We covered key concepts such as ORMs, schemas and constraints in SQL. We also discussed the installation and configuration of MySQL.
We also covered some SQL statements that can manipulate MySQL data.