MySQL Commands: List & Examples

An error occurred trying to load this video.

Try refreshing the page, or contact customer support.

Coming up next: Python Data Visualization: Basics & Examples

You're on a roll. Keep up the good work!

Take Quiz Watch Next Lesson
 Replay
Your next lesson will play in 10 seconds
  • 0:04 MySQL Definition
  • 0:40 MySQL Commands
  • 3:44 Lesson Summary
Save Save Save

Want to watch this again later?

Log in or sign up to add this lesson to a Custom Course.

Log in or Sign up

Timeline
Autoplay
Autoplay
Speed Speed
Lesson Transcript
Instructor: Meghalee Goswami

Meghalee has a masters of computer science and communication engineering.

In this lesson, you'll be introduced to MySQL and learn about the different MySQL commands that can be used to work with databases. At the end of the lesson, there's a great command reference table to use while you are coding.

MySQL Definition

MySQL is an open source SQL (or structured query language) database management system. It leverages the concept of relational databases wherein multiple tables can hold pieces of data pertaining to a large physical entity. MySQL databases implement many data types that are used to help define the data. These data types include numeric (as in integer, floating, mixed, signed, and unsigned), date and time (DATE, DATETIME, and TIMESTAMP ), and string ( CHAR, VARCHAR, TEXT, ENUM, and SET).

MySQL Commands

In this lesson, we'll cover many of the MySQL commands you would need to create and manage databases. A more comprehensive table of commands is near the bottom. Please note that the installation of MySQL isn't covered in this lesson, but let's cover the main commands that you'll encounter with MYSQL, one at a time here:

1. Database and Table Creation

In the example appearing here, we are creating a database called Students and then creating associated tables. To create the database, allow the database to be used and create a table, implementing the following commands:

mysql> CREATE DATABASE Students;

mysql> USE Students

mysql> CREATE TABLE Information (firstname VARCHAR(20),lastname VARCHAR(20),gender CHAR(1),grade INT(10), dob DATE);

mysql> SHOW TABLES;

After you've created the database and subsequent table called Students in the database, you can see the details of the table by using the describe command, like this one:

mysql> DESCRIBE Information;

The following will be displayed:

Field Type Null Key Default
firstname varchar(20) YES NULL
lastname varchar(20) YES NULL
gender varchar(20) YES NULL
grade int(10) YES NULL
dob date YES NULL

The table being displayed here is what you'll see. In our table, field is the name of the column; type is the datatype; null is a check to see if the attribute can have null values; key determines whether the attribute can be a primary or foreign key (we'll discuss this concept later in the lesson); the default command specifies whether the attribute comes with a pre-defined default value.

2. Inserting Values in a Table

The INSERT INTO statement is used to add values to a table. Appearing here is the command to insert 5 student records into the table 'Information:'

mysql> INSERT INTO Information VALUES ('Amanda','Williams','f','10','1999-03-30');

mysql> INSERT INTO Information VALUES ('Peter','Williams','m','10','1998-03-15');

mysql> INSERT INTO Information VALUES ('Cristie','Wills','f','10','1999-02-05');

3. Viewing the Table

Now that you're done inserting values, how do you check to see if the records are in the table now? This is done with the SELECT command, which you can see play out here:

mysql> SELECT * FROM Information;

firstname lastname gender grade dob
Amanda Williams f 10 1999-03-30
Peter Williams m 10 1998-03-15
Cristie Wills f 10 1999-02-05

4. Adding a Column to the Table

At this point, the 'Information' table does not have a unique identifier for every record, called a primary key . We can make changes to the table and add another column to it that will always take in only unique values. This can be done with multiple ALTER commands, as follows:

mysql> ALTER TABLE Information ADD COLUMN rollnumber INT(10);

mysql> ALTER TABLE Information ADD PRIMARY KEY(rollnumber)

5. Adding Values to the New Column

Now that we've added our new column and made it unique using a primary key, we can now add data to it using the INSERT INTO statement as follows:

mysql> INSERT INTO Information rollnumber VALUE ('001');

mysql> INSERT INTO Information rollnumber VALUE ('002');

mysql> INSERT INTO Information rollnumber VALUE ('003');

6. Counting the Number of Rows

If we want to count the number of rows in a table, we can use the COUNT command:

mysql> SELECT COUNT (*) FROM Information

7. Selecting Particular Records

Using the SELECT WHERE command, we can select a specific record from the table as follows:

mysql> SELECT * FROM Information WHERE lastname = 'Williams';

firstname lastname gender grade dob
Amanda Williams f 10 1999-03-30
Peter Williams m 10 1998-03-15

8. Updating Selected Records

To update information in a specific column for a specific record, the UPDATE command is used as follows:

mysql> UPDATE Information SET dob = '1999-02-02' WHERE lastname = Wills;

This updates the dob of the record whose last name is Wills and returns the result.

firstname lastname gender grade dob
Cristie Wills f 10 1999-02-02

9. Deleting Records

If you want to delete a record or multiple records from a table, you can use the DELETE command as follows:

mysql> DELETE FROM Information WHERE dob = 1999-03-30;

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support