SQL Programming : Learn How to Work with MySQL Database Structures

sql vs mysqlStructured Query Language (SQL) is a special purpose programming language that is used for manipulating, storing, and querying data. SQL operates on data in structures called relational databases. These database structures are usually managed by relational database management systems (RDBMS). Standard SQL is used in almost all major relational database management systems such as MySQL, which is an open-source relational database management system. MySQL is ideal for both large and small applications and is easy to use, reliable, and fast; the software also supports standard SQL syntax.

Creating and Using Databases

A database that contains business customers will be an example for the following tutorial. The database should contain information about many individual customers, including their names, contact information, and other business related data.

Learn SQL Programming from scratch. Take an online class at Udemy.com

Let’s take a table named “Customers” as an example:

S1

Creating the Database

Several steps are involved in creating the “Customers” database:

The first step for your data is to create the database. There will be one or more tables within a database. For this step, you use the CREATE DATABASE command. The following code will create a database named “Customers” :

CREATE DATABASE Customers;

Creating the Table

Now that the database is created, the next step is creating a table that will represent our customer data. For this step, the CREATE TABLE command will create a table within the “Customers” database:

CREATE TABLE Customers

(

ID int

CusName Varchar(200),

ContactName Varchar(200),

PostalCode int,

Country Varchar(200)

);

The preceding code created a table named “Customers” and filled it with columns. By declaring the column name and subsequently, the column data type,  “ID”, “CusName”, “ContactName”, “PostalCode” and “Country” are now fields within the “Customers” table. The NOT NULL field attribute can be assigned to columns that should not contain null values. Also, some column definitions are arrays of characters, such as Varchar, and need a specifier for the data’s maximum character allocation (the elements or characters the column can hold).

s2

Adding Data to the Database and Table

After the database, its table, and the table’s fields have been created, the next step is to insert a new record into the table. Remember, a database is like a folder, the table like a file, and the fields (CusName for example) are like items from a file’s container that belong to one customer. Of course, a database table (the virtual file) will contain many data items that one customer entity may posses. The entity, or row, is called a record. Each record has one of each declared fields (columns). SQL provides the INSERT INTO command for inserting a record into a table. The following is an example of the INSERT INTO command and how it’s used:

INSERT INTO Customers (‘ID’,’CusName’,’ContactName’,’PostalCode’,’Country’)

VALUES (‘1’,’Francis’,’Flintoff’,’223344’,’US’);

The VALUES function call correlates to the INSERT INTO command. The order of parameters in the VALUES call needs to be ordered equally with the list of fields within the INSERT INTO call (the set of fields within the parenthesis). After calling the INSERT INTO and VALUES functions, the values in the VALUES() call are assigned to the fields in the INSERT INTO() call. After these two functions are called with appropriate, correlating parameters, the “Customers” table contains a new record (row) that has values for each of the fields (columns).

S3

Database tables are populated in a sequential nature, adding one record at at a time. After several record insertions are made, a table will serve as a collection of customer entities which will store data about those customer entities.

Retrieving Data from a Database/Table

Retrieving data from a database table is as simple as adding a record to a table. The SELECT command is used for retrieval purposes. If you only want to retrieve the columns “ID,” “CusName,” and “Country.” The following code retrieves those desired columns:

SELECT ID, CusName ,ContactName FROM Customers;

S4

Alternatively, you can specify all of a table’s columns with the SELECT command to retrieve/display a table with all of its columnar data.

If you want to retrieve the data from all columns of the “Customers” table but only want to display the columns in descending order, including three rows instead of all five rows, you use the LIMIT command. The LIMIT command will limit the displayed data to three rows. The DESC clause paired with the ORDER BY command will specify the descending order of the rows to be displayed.

The following snippet is an example of limiting and ordering rows for display/retrieval:

 

SELECT * FROM Customers

ORDER BY DESC

LIMIT 3;

S5

Deleting Records from a Table

If you want to delete a record (row) that contains the customer name “Stefen” and postal code “556677”, you can use the DELETE command and WHERE clause. With that command and clause, you can apply the SQL query as:

DELETE FROM Customers WHERE CusName=’Stefen’ AND PostalCode=’556677’;

The preceding example query will delete any row from “Customers” that contains “Stefen” in the “CusName” column and “556677” in the postal code column. The table data is then changed to the following:

S6

You can also delete multiple rows using the same DELETE command.

Learn SQL Programming in just six days by taking a course at Udemy.com

Updating a Table

Suppose you want to update a record from the “Customers” table. You can use the UPDATE command for such a need.  In order to use the UPDATE command appropriately, you must use the WHERE clause to specify the row that should be updated. Using the WHERE clause is essential, because omitting the WHERE clause can cause unwanted changes in the “Customers” table. Such omissions of the WHERE clause can update every record of the database table!

To demonstrate how to update a table, imagine that you want to update your “Customers” table by changing a record’s CusName from “francis” to “mike” and the postal code from “223344” to “226070”. Also imagine that you only need to make this update in rows that contains “US” as the country value. The following query example will foster the update need.

UPDATE Customers

SET CusName=’Mike’, PostalCode=’226070’

WHERE Country=’US’;

Now the new table looks like this:

S7

After the three preceding commands are executed, the resulting table will have “Mike” and “226070” for the CusName and PostalCode fields, respectively. Additionally, only the fields which are of a record that has “US” as the country value are updated.

Adding a Column (field) to a Table

To add a new column to an existing table, you use the ALTER TABLE command. You can add a near infinite number of columns to a table;  you only have to specify the data type for the added column. In case of MySQL, if you want to add a column for the date of birth and you have given the datatype as “year” for this column, then the code will look like this:

ALTER TABLE Customers

ADD DOB year;

 

The new table will look like this:

S8

 

Removing a Column from a Table

If you need to remove a column from a table, you use the ALTER TABLE command again, but with the DROP clause. The following example will remove the “ContactName” column from the “Customer” table:

ALTER TABLE Customers

DROP COLUMN ContactName;

This code will remove the column “ContactName” from the table without affecting any other column.

S9

New to SQL programming? Take up a course at Udemy.com

Conclusion

These are all the examples of basic SQL programing but there are also many other commands and SQL functions that can be used in programming such as the JOIN command used for combining the rows of two or more tables. UNION command is used for combining the result of two or more SELECT statements into a single result set whereas the INTERSECT operator is used for returning the result of two or more SELECT statements. Functions like Avg()is used for returning the average value of the numeric column and “Count” is used for returning the number of rows which matches any specified criteria. Max()is used for returning the largest value of any specified column but Min()is used for returning the lowest value of any specified column and “Sum” is used for returning the complete sum of any numeric column.

SQL programming is based on Tuple relational calculus and uses queries for accessing the information in a database.  SQL enables programmers to create and utilize relational database structures. Structured Query Language as a whole provides a programming language that supports object oriented representation of real-world objects that have attributes that can be recorded, stored, and used.