In this article, we will make an introduction to SQL Basics.
1. Introduction
Structured Query Language (SQL) is a standardized database language that is used to create, maintain, and retrieve data from a relational database. SQL was initially developed at IBM San Jose Research Laboratory in early 1970. IBM released the first Relational Database Management System (RDBMS) – Multics_Relational_Data_Store in June 1976. By 1986, American National Standards Institute (ANSI) and International Organization for Standardization (ISO) standard groups officially adopted the standard “Database Language SQL” language definition.
In this example, I will show the following basics SQL commands based on the ISO standard:
- Data Definition Language – CREATE TABLE
- Data Manipulation Language – INSERT
- Data Query Language – SELECT
2. Technologies Used
The example code in this article was built and run using:
- MySQL
- SQL
3. RDBMS
A relational database management system (RDBMS) stores data in a row-based table structure that connects related data elements. MySQL, Oracle, Sybase, SQL Server, and Postgre are common RDBMS. In this step, I will create an entity-relationship diagram that outlines two tables and their relationship.
- User_tbl has 1-to-Many relationship to Phone_tbl. A single record in User_tbl can have zero or many phone records.
- The user_id column is a foreign key which references user_tbl.
4. Data Definition Language
Data Definition Language(DDL) is used to define the structure of the database. In this step, I will create two tables and define the relationship between them.
ddl-create table.sql
create table user_tbl( user_id INT NOT NULL AUTO_INCREMENT, user_fname VARCHAR(100) NOT NULL, user_lname VARCHAR(40) NOT NULL, PRIMARY KEY ( user_id ) ); create table phone_tbl( phone_id INT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, phone_number VARCHAR(40) NOT NULL, PRIMARY KEY ( phone_id ), FOREIGN KEY (user_id ) REFERENCES user_tbl(user_id) );
- Line 5 – user_id is the primary key of user_tbl.
- line 13 – user_id is the foreign key of phone_tbl.
5. Data Manipulation Language
Data Manipulation Language(DML) is used to manipulate data in the relations. In this example, I will use the INSERT command to save records.
dml-insert.sql
insert into user_tbl(user_fname,user_lname) values('Mary','Zheng');
insert into user_tbl(user_fname, user_lname) values('Tom2','Zheng');
insert into user_tbl(user_fname, user_lname) values('Jerry2','Cheng');
insert into user_tbl(user_fname, user_lname) values('MARY','ZHENG');
insert into user_tbl(user_fname, user_lname) values('TOM2','ZHENG');
insert into user_tbl(user_fname, user_lname) values('JERRY2','CHENG');
insert into phone_tbl(user_id, phone_number) values(1, '314-123-4567');
insert into phone_tbl(user_id, phone_number) values(2, '636-213-5673');
insert into phone_tbl(user_id, phone_number) values(3, '404-132-5734');6. Data Query Language
Data Query Language (DQL) is used to extract the data from the relations. In this example, I will use the SELECT command to retrieve data.
This returns all data from user_tbl.
select *
mysql> select * from user_tbl; +---------+------------+------------+ | user_id | user_fname | user_lname | +---------+------------+------------+ | 1 | Mary | Zheng | | 2 | Tom2 | Zheng | | 3 | Jerry2 | Cheng | | 4 | MARY | ZHENG | | 5 | TOM2 | ZHENG | | 6 | JERRY2 | CHENG | +---------+------------+------------+ 6 rows in set (0.00 sec)
This returns unique user_lname from user_tbl.
select distinct
select distinct user_lname from user_tbl; +------------+ | user_lname | +------------+ | Zheng | | Cheng | +------------+ 2 rows in set (0.01 sec)
This returns all data from both user_tbl and phone_tbl with equal join at the user_id column.
select * from two tables
mysql> select * from user_tbl u, phone_tbl p
-> where u.user_id = p.user_id;
+---------+------------+------------+----------+---------+--------------+
| user_id | user_fname | user_lname | phone_id | user_id | phone_number |
+---------+------------+------------+----------+---------+--------------+
| 1 | Mary | Zheng | 1 | 1 | 314-123-4567 |
| 2 | Tom2 | Zheng | 2 | 2 | 636-213-5673 |
| 3 | Jerry2 | Cheng | 3 | 3 | 404-132-5734 |
+---------+------------+------------+----------+---------+--------------+
3 rows in set (0.00 sec)This returns all data from both user_tbl and phone_tbl with left join at the user_id column.
select * with left join
mysql> select * from user_tbl u left join phone_tbl p on u.user_id = p.user_id; +---------+------------+------------+----------+---------+--------------+ | user_id | user_fname | user_lname | phone_id | user_id | phone_number | +---------+------------+------------+----------+---------+--------------+ | 1 | Mary | Zheng | 1 | 1 | 314-123-4567 | | 2 | Tom2 | Zheng | 2 | 2 | 636-213-5673 | | 3 | Jerry2 | Cheng | 3 | 3 | 404-132-5734 | | 4 | MARY | ZHENG | NULL | NULL | NULL | | 5 | TOM2 | ZHENG | NULL | NULL | NULL | | 6 | JERRY2 | CHENG | NULL | NULL | NULL | +---------+------------+------------+----------+---------+--------------+ 6 rows in set (0.00 sec)
7. Summary
In this example, I showed basic SQL commands which manage data in a relational database. DDL commands are executed by a database administrator (DBA) usually. DML and DQL commands are executed by an application developer.
8. Download the Source Code
You can download the full source code of this example here: Introduction to SQL Basics
