Everything You Need to Get Started With MySQL

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.

Sequelize (JavaScript), Eloquent(Laravel), SQLAlchemy, Python, Active Record (Ruby on Rails) are some examples of ORMs.

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:

Users table
Users table
 

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.

Constraints

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

Database Normalization

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:

Users, Products and Carts tablesUsers, Products and Carts tablesUsers, Products and Carts tables
Users, Products, and Carts tables
 

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. user_idAnd 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.

This method of linking tables within a relational data base is called “Compositing and Linking Together”. normalization. I’ve only touched on the basics of normalization in this article—if you are designing a database schema for your web app, it’s worth reading a little more about normalization.

Installing MySQL

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.

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

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. SHOW DATABASE.

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:

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:

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 SELECT statement.

If we want everything from our collection, for instance UsersTable, we use the following statement

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

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

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.

Wrapping up

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.

Leave a Reply

Your email address will not be published.