MySQL Commands: List & Examples – Video & Lesson Transcript

Commands

In

this lesson, we’ll cover many of the MySQL commands you’d need to create and manage databases. A more complete table of commands is near the bottom. Note that installing MySQL is not covered in this lesson, but let’s cover the main commands you’ll encounter with MYSQL, one at a time here:

1. Creating databases

and tables

In the example shown 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 (name VARCHAR(20),

last name VARCHAR(20),gender CHAR(1),grade INT(10), dob DATE);

mysql> DISPLAY TABLES;

After you create the database and the

subsequent table named Students in the database, you can view the details of the table by using the describe command, such as this

:

mysql> DESCRIBE Information;

The following will be displayed:

Null key field type Default name varchar(20) YES NULL last name varchar(20) YES NULL genus varchar(20) YES NULL grade int(10) YES NULL date dob YES NULL

The table shown here is what you will 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 will discuss this concept later in the lesson); The default command specifies whether the attribute includes a predefined default value.

Arabic numeral. Insert values into

a table

The INSERT INTO statement is used to add values to a table. Here appears 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’ve finished inserting values, how do you check if the records are in the table now? This is done with the SELECT command, which you can see here:

mysql> SELECT * FROM Information;

first name last name gender degree dob Amanda Williams f 10 1999-03-30 Peter Williams m 10 1998-03-15 Cristie Wills f 10 1999-02-05

4. Add a column to

the table

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

mysql> ALTER TABLE Information ADD COLUMN rollnumber

INT(10); mysql> ALTER TABLE Information ADD

PRIMARY KEY (rollnumber)

5. Add values

to the new column

Now that we have 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. Count the number of rows

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

mysql> SELECT COUNT (*) FROM Information

7. Using

the SELECT WHERE

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

mysql> SELECT * FROM Information WHERE lastname =

‘Williams’; first name last name gender degree dob Amanda Williams f 10 1999-03-30 Peter Williams m 10 1998-03-15

8. Updating selected records

To update the information in a specific column for a specific record, use the UPDATE command 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

. first name last name gender degree 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;

Contact US