MySQL Commands: List & Examples

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;
 
Looking for a Similar Assignment? Order now and Get 10% Discount! Use Coupon Code “Newclient”

The post MySQL Commands: List & Examples appeared first on Superb Professors.

"Order a Custom Paper on Similar Assignment! No Plagiarism! Enjoy 20% Discount"