MySQL Reference Guide

MySQL is widely considered the most reliable, scaleable, and developer-friendly open source relational database management system. It powers the back end of many popular social, streaming, and service web applications.

MySQL comes with a lot of new terminology and commands to learn, so you might find it challenging to get started. You can use this reference guide to review unfamiliar MySQL commands, terminology, and syntax.

Basic Terminology

Before we cover MySQL commands, let's review the following basic terminology for relational databases:

• SQL (Structured Query Language) is a common standardized language used to access databases.

• A database is an organized collection of data, generally stored and accessed electronically from a computer system.

• A relational database organizes data into tables that can be linked, creating relationships between the data.

• A table is a collection of related data within a database. A database can have one or more tables.

• A column is a field that holds a set of related data within a table.

• A row is a single record within a table.

• A primary key provides a unique identifier for each row, or record, in a table. A table can only have one primary key, consisting of a single column.

• A foreign key is a field in one table that references the primary key of another table to link that data.

Common MySQL Commands

Great! Now let's move on to some common MySQL commands.

MySQL Shell Commands

You'll likely use the following commands in the MySQL Shell:

• mysql -u [username] -p allows you to access the MySQL Shell from the command line. You should replace the placeholder [username] with the actual username that you want to use (most likely root). After you enter this command, you'll be prompted to enter your MySQL password. Then you'll be logged into the MySQL Shell.

• USE [name of database] selects the database that you want to work in, or use.

• SHOW DATABASES displays a list of all the existing databases that you have access to.

• SHOW TABLES displays a list of all the tables in the database that you're using.

• SELECT DATABASES() shows the database currently in use.

• DESCRIBE [name of table] describes a table's structure.

Database Commands

You can use the following MySQL commands to affect databases:

• CREATE DATABASE [name of database] creates a new database with the name specified in the command.

• DROP DATABASE [name of database] drops, or deletes, the database specified in the command.

• DROP DATABASE IF EXISTS [name of database] drops, or deletes, the specified database only if it exists—preventing an error if there's no database by that name.

Table Commands

You can use the following MySQL commands to affect tables:

• CREATE TABLE [name of table] creates a table with the specified name within a database.

• DROP TABLE [name of table] drops, or deletes, a table with the specified name within a database.

• DROP TABLE IF EXISTS [name of table] drops, or deletes, a specified table only if it exists—preventing an error if there's no table by that name.

• INSERT INTO [name of table] inserts a new row into a specified table.

• UPDATE [name of table] SET [column] updates records in a specified table. The SET clause indicates which columns to modify and the values they should be given, and it's normally followed by the WHERE clause.

• DELETE FROM [name of table] deletes records from a specified table.

• ALTER TABLE [name of table] alters an existing table (for example, adding a column to the specified table).

Data Retrieval

You can retrieve specific data using the following MySQL commands:

• SELECT selects, or retrieves, records from a table.

• FROM specifies the table that contains the data we want to retrieve.

• * is a wildcard character that selects all records from a table. It can be used together with the WHERE clause to retrieve all records that meet specific criteria.

• WHERE is an optional clause that specifies conditions that the data must meet to be returned.

• LIMIT is an optional clause that limits the number of records returned.

• COUNT() is an aggregate function that takes the name of a column as an argument and returns the number of records that are returned by a SELECT query where the column isn't NULL.

• GROUP BY groups rows that have the same value. It's often used in combination with aggregate functions, such as COUNT().

• SELECT * FROM [name of table] selects, or retrieves, all records from a specified table.

• SELECT COUNT([name of column]) FROM [name of table] returns the number of records from a specified table.

Resources

You can use the terminology and commands outlined here in several variations and combinations—but this quick reference guide is just a starting point as you begin to learn MySQL. To learn more as you progress, you can refer to the following resources:

• What is MySQL?

• MySQL Shell

• MySQL: Creating and Selecting a Database

• MySQL: Creating a Table

• MySQL: SELECT Statements

This page was updated 4 months ago
© 2022 edX Boot Camps LLC. Confidential and Proprietary. All Rights Reserved.

Category: mysql

Tagged under: mysql, reference, guide, sql, databases,