MySQL 101

Essential MySQL Commands

SELECT — choose specific data from your database
UPDATE — update data in your database
DELETE — deletes data from your database
INSERT INTO — inserts new data into a database
CREATE DATABASE — generate a new database
ALTER DATABASE — modify an existing database
CREATE TABLE — create a new table in a database
ALTER TABLE — change the selected table
DROP TABLE — delete a table
CREATE INDEX — create an index (search key for all the info stored)
DROP INDEX — delete an index

Working with Tables

Tables are the key element of MySQL databases as they let you store all the information together in organized rows. Each row consists of columns that feature a specified data type. You have plenty of options for customization using the commands below.

Create a New Simple Table

Use this command to create a new table:

CREATE TABLE [IF NOT EXISTS] table_name(
 column_list
);
DROP TABLE tablename;
CREATE TABLE movies(
 title VARCHAR(100),
 year VARCHAR(100),
 director VARCHAR(50),
 genre VARCHAR(20),
 rating VARCHAR(100),
); 

###View Tables

Use the next commands to get more information about the tables stored in your database.
show tables — call a list of all tables associated with a database.
DESCRIBE table_name; — see the columns of your table.
DESCRIBE table_name column_name; — review the information of the column in your table.

###Delete a Table

To get rid of the table specify the table name in the following command:
DROP TABLE tablename;

Working With Indexes

Indexes are the core element of your database navigation. Use them to map the different types of
data in your database, so that you don’t need to parse all the records to find a match.

NB: You have to update an index every time you are creating, changing or deleting a record in the
table. Thus, it’s best to create indexes only when you need to and for frequently searched columns.
How to Create an Index

The basic syntax is as follows:

CREATE INDEX index_name
ON table_name (column1, column2, ...);
You can also create a unique index — one that enforces the uniqueness of values in one or more columns.

CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,...);

How to Delete an Index in MySQL
Use the DROP command for that:
DROP INDEX index_name;

Working with Views

Indexes are the core element of your database navigation. Use them to map the different types of
data in your database, so that you don’t need to parse all the records to find a match.
NB: You have to update an index every time you are creating, changing or deleting a record in the
table. Thus, it’s best to create indexes only when you need to and for frequently searched columns.
A view is a virtual representation of an actual table that you can assemble up to your liking (before
adding the actual one to your database).
It features rows and columns, just like the real deal and can contain fields from one or more of the
real tables from your database. In short, it’s a good way to visualize and review data coming from
different tables within a single screen.

How to Create a New View
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Update a View
A view always displays fresh data since the database engine recreates it each time, using the view’s
SQL statement. To refresh your view use the next code:

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Rename a View
If you are dealing with multiple views at a time, it’s best to give them distinctive names. Here’s how
that done:
RENAME TABLE view_name TO new_view_name;

Show All Views
To call up all current views for all tables from the database, use this snippet:
SHOW FULL TABLES
WHERE table_type = ‘VIEW’;

Delete a View
To delete a single view use the DROP command:
DROP VIEW [IF EXISTS] view_name;

You can also delete multiple views at a time:
Drop Multiple views: DROP VIEW [IF EXISTS] view1, view2, ...;