01-Introduction

Connecting to the Database

After installing MySQL on your device or server you’ll first need to connect to it via the MySQL Workbench program or using the command line. The community edition of MySQL server is available to download for free and comes with all the required things that you'll need to work with MySQL.

⁠

If you're working in a production environment the MySQL server will be installed on the server and the MySQL Workbench program will be installed on the client machine. For development purposes, we install both locally.

MySQL Workbench allows you to see all of the available databases in your server on the left tab and has a SQL editor next to the database tab that allows you to write queries for interacting with the database and the results are shown right below the SQL editor.

When working with SQL based relational databases the words database and schema mean the same thing.

Creating a New Database

Creating new databases in MySQL is super simple, just run this command.

CREATE DATABASE practice

When run, the command creates a new database named practice. In order to work with this database we first need to tell MySQL that the database that we will run our future queries on is the practice database which we can do with this command.

USE practice

After this command is run MySQL will select the practice database as the current database and all of our queries will run on the practice database until we close the connection to it.

Databases can be removed as easily as they are created. The `DROP` command will remove the database that you need and also all the data in it including tables and relations will also be deleted.

DROP TABLE practice

Creating a New Table

Databases contain tables, that are made up of columns and row much like spreadsheets. Tables can also be created using the create command but they require you to define all of the names of the columns and what type of data will be stored in each column.

If a column is created to store the int(32) data type, it will only store values that are of the 32-bit integer and trying to store anything else will result in an error as MySQL will not run the query.

CREATE TABLE people{

id int(32)

name varchar(20)

age int(32) }

Creating Indexes

Indexes in MySQL sped up the data retrieval process. It is also not recommended to create an index on every single column and only on the `id` ones as creating too many indexes will degrade the performance of the database.

Indexes are created automatically for the columns that have primary and foreign keys as a constraint as well as the unique constraint.

In a database for an e-commerce store you'll have a table called products that can have a column that stores the data about the products supplier. If a supplier supplies more than one kind of product and we have a lot of suppliers then we can create an index on the supplier column so that we can quickly get the data about each supplier and their products.

CREATE TABLE products{

product_id int

product_name varchar(20)

supplier_id int }

CREATE INDEX product_supplier_index ON product(supplier_id)

Indexes don't change any details about the tables, they just make retrieving data from the faster.

Altering and Removing Tables

The DROP command is used when you want to remove a table completely along with all of the data stored inside of it. If the table you're trying to drop has a relation with another table like a primary or foreign key then that relation will first need to be removed in order to drop the table.

The ALTER command is used to modify tables like renaming, adding or removing constraint and columns.

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.