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;