Gain insights into relational databases and SQL. Learn about creating, managing, and editing tables using MySQL. Explore complex queries, indexes, and join functions for efficient data handling.

testmysql.tar.gz

sql spa

Relational databases store data in a row-based table structure. Structured Query Language (SQL) is a core language used to retrieve data from the relational database. One of its most widely used extensions, MySQL, also gives you the power to edit, create, and manage the queried data.

In this course, you’ll be introduced to the basics of relational database management with SQL. You’ll use MySQL to create a table, edit the data, and even change the structure. You’ll also create indexes to quickly access data for faster performance on common queries. Additionally, you’ll explore more complex queries to extract useful data with aggregate functions, grouping, ordering, and creating limits and filters in output tables. Finally, you’ll learn how to combine data from different tables with join functions.

By the end of this course, you’ll be able to create, manage, edit, and merge relational databases with confidence. MySQL will prepare you for data projects large and small.

Getting Started with SQL and Relational Databases

Let’s see an example of creating an index. We want to create an index on the `identity` column of the `customers` table. Below is the command:

``` shell
create index customers_identity_idx on customers (identity);
```

Before we actually execute that, let’s analyze its format.

# Parts of the `index` query

1.  The command to create an index should start with `create index`.
2.  Then, we give the name of the index. Each index needs to have a name, which can be anything. It’s generally helpful to follow the `<table_name>_<column>_idx` pattern when naming our indexes, though.. In other words, we construct the name of the index from the table name and the column names that are going to be indexed. Then, we use the `_idx` suffix. In our example, the name of the index we decide to use is `customers_identity_idx`. This is because the index is going to index the `identity` column of the `customers` table.
3.  We specify the table that’s going to be indexed using the `on` keyword.
4.  Finally, inside parentheses, we specify a comma-separated list of the columns to be indexed. In our example, we build an index on a single column, `identity`. There might be cases in which we want to build an index on multiple columns. This is true when we query data using `where` clauses that reference more than one column. For example, what if we find ourselves executing a query such as the one below?
     ```
     select * from customers where name = 
     'John Smith' and identity = 'JS0001'
     ```
      We may want to have defined an index on both columns using the following command:
      ```mysql
      create index customers_name_identity_idx on 
      customers(name,identity);
      ```

Let’s proceed by issuing the create index command.

Getting Started

Manage MySQL Server

Basic SQL Commands

Add Columns and Indexes

Count and Sort Records

Foreign Keys

Joins, Left Joins And Subqueries

Conclusion

Create an Index

Parts of the `index` query

Create an Index

Parts of the index query

Parts of the `index` query