SQL (Structured Query Language) is database computer language used to manipulate the data in a relational database management systems (RDBMS). A relational database is composed of tables similar to what is displayed in <image1>. A relation database could have one table or hundreds of tables. Did you ever wonder how many tables Facebook could have? A table contains columns which identifies the entity. In this case we have a table called Students (entity) and we have column-names to gather specific information about the student such as LastName, FirstName, State and so on.
<image1>
The standard SQL language is “standard” across multiple databases types such MySQL, Oracle, Microsoft SQL, and others. However, each database software (MySQL, Oracle, Microsoft SQL) offers or bundle their own flavor to the “standard” SQL language. Some of them offers special function or unique features that will not exist other databases software.
Each database software have their out tools for accessing their databases objects. In MySQL we have the ” MySQL GUI Tools” plus other external tools from different companies. In Oracle we have “Oracle Enterprise Manager” plus some other very user friendly provides by other companies. In Microsoft SQL we have all in one “SQL Server Management Studio” and there are some other third party tools but the one that comes with database does a very good job.
The SQL language falls in three basic categories. Now, some databases may have more additions to its categories. There is the DDL (Data Definition Language), the DML (Data Manipulation Language), and DCL (Data Control Language). I will explain briefly those categories.
DDL (Data Definition Language)
A Data Definition Language or Data Description Language (DDL) is a SQL language for defining the structure(s) of database object(s) such as tables, table views, triggers, store procedures, and so on.
CREATE | It used to create objects in the database such as CREATE VIEW, CREATE TABLE, CREATE TRIGGER, and so on
Example: |
ALTER | It used to change the structure of the database object such as adding column to a table, or changing the view
Example: |
DROP | It is used to delete an objects from a database … E.g.: DROP TABLE [table-name] or views or trigger
Example: |
TRUNCATE | It is used to remove all records from a table, including all spaces allocated for the records are removed.
Example: |
DML (Data Manipulation Language)
Data Manipulation Language (DML) is the most common language used to manipulate the data in a database. Every technical personnel should be familiar with these basic four commands. Most computer programs and/or database users uses to insert, delete and update data in a database.
SELECT | The command that most people will used. It is used to retrieve data from a database table or table view.
Example: |
INSERT | Insert new data into a database table.
Example: |
UPDATE | If you want to make changes to a data that is been already stored in the database (updates = changes).
Example: |
DELETE | Delete data from a table. However, it will not delete the space allocated from that database. Use TRUNCATE to remove space allocated.
Example: |
DCL (Data Control Language )
A Data Control Language (DCL) is a computer language used to control access or privileges to data in a database or database objects with a database.
GRANT | It used to give access rights or privileges to database objects (tables, view, schemas, and so on)
Example: |
REVOKE | Remove rights or privileges give via the GRANT command.
Example: |
TCL – Transaction Control Language
A Transaction Control Language (TCL) is a computer language used to manage changes made by DML
COMMIT | It is used to save the changes or commit the changes to the database.
Example: |
ROLLBACK | Restore the changes to its original state since the last COMMIT. Used in large transactions. In the following example I did not delete anything because at the end I rollback the transaction.
Example:
|
Now, these are just simple basic SQL commands that will be standard across multiple database types. Keep in mind that every database software (Oracle, MySQL, Microsoft SQL, Pervasive) have their own additions to basic language.