Now let’s dive into the Database Language, SQL (Structured Query Language). We will learn MySQL.
1. About SQL
SQL is non-procedural, transform-oriented, and standard language for relational databases and it stands for Structured Query Language.
It is actually a combination of a four different (but similar in some sense) languages.
DDL (Data Definition Lang.) for creating DB,
DML (Data Manipulating Lang.) for manipulating DB,
DQL (Data Query Lang.) for retrieving information, and
DCL (Data Control Lang.) for controlling access or giving permissions.
2. Understanding the Relational Database Model
SQL is about Table Referencing. In Relational Database Model, every data is stored in structured tables that are strongly inter-connected with each others. Because everything is in table, it is okay to say data can be referenced by column and row. Also, one of the key concept of DB is that there are no duplicates of a row. Every row are unique, although the entities are not unique, set of entities, which is row, is unique.
Here, we should know what a ‘Primary key’ is. A primary key is a field in a table which uniquely identifies each row in a database table. Primary keys must have unique values, which is not NULL. For example, let’s say we are dealing with DB of Staff in one company. The primary key for the table name ‘Staff’ can be something like ‘Staff_No.’, which is a unique number given to every staff.
Remember this model is ‘Relational’. There are many tables that are ‘related to’ each other. How can we relate one table to other(s)? It’s done by a ‘Foreign Key’. It is a field in one table that refers to the Primary key in another table. This relationship can be termed by the really familiar relationship we always encounter; Parent-Child relationship. The table containing the foreign key is the child table, and the table containing the candidate key is the parent table.
Additionally, there’s one more thing called ‘Composite key’. It is literally a composite of several attributes, playing a role as Primary key. It is when joined attributes uniquely identify each row in a DB.
3-1. Data types
INT -- whole numbers DECIMAL(5, 2) -- decimal numbers of total 5 digits and 2 decimal digits VARCHAR(10) -- Variable Character of size 10 BLOB -- Binary Large Object DATE -- 'YYYY-MM-DD' TIMESTAMP -- 'YYY-MM-DD HH:MM:SS'
3-2. Creating DB and Making tables
Example of creating DB and making Tables: (Example of EstateAgent is borrowed from Reference No. 1)
CREATE DATABASE EstateAgent; -- making it the current DB in use USE EstateAgent; -- Creating a table to hold data CREATE TABLE Staff (staffNo VARCHAR(4), fName VARCHAR(20), sex VARCHAR(1), DOB DATE, salary DECIMAL(5, 2)); -- Inserting a row into a table INSERT INTO Staff(staffNo, fName, sex, DOB, salary) VALUES ('AB01', 'Danny', 'M', '1984-01-01', 99000) -- Let's say now we have many rows inserted in our DB, EstateAgent -- Updating data in tables (Give all staff a 5% pay increase) UPDATE Staff SET salary = salary * 1.05
3-3. Querying data
Primary Clauses for retrieving data
SELECT -- columns to appear FROM -- table to be used WHERE -- filters rows GROUP BY -- form groups of rows with the same column value HAVING -- filters groups subject to some conditions ORDER BY -- order of the results
-- column named staffNo and fName will appear from Staff Table SELECT staffNo, fName FROM Staff; -- Can select all attributes by asterisk. SELECT * FROM Staff; -- Eliminate possible duplicates by DISTINCT and give title to the attribute by AS SELECT DISTINCT fName AS firstName FROM Staff; -- Calculation (montly salaries) SELECT staffNo, salary / 12 FROM Staff;
WHERE, OR, AND, BETWEEN, NOT, IN, IS NULL, ORDER BY, Pattern Matching by LIKE
-- Male staff or staff who make salary between 40k and 60k -- Can use NOT BETWEEN SELECT * FROM Staff WHERE sex='M' OR salary BETWEEN 40000 AND 60000; -- Set membership search by IN -- Can do WHERE fName='Claire' OR fName='Soo' OR fName='Yoonho' OR fName='Cassey', but a bit messy. SELECT staffNO, fName, DOB, salary FROM Staff WHERE fName IN ('Claire', 'Soo', 'Yoonho', 'Cassey'); -- NULL Value Finding -> used a lot in Data Science -- IS NULL, IS NOT NULL SELECT staffNO, fName, DOB, salary FROM Staff WHERE DOB IS NOT NULL -- Can do pattern matching with special symbol, % and _ -- % means zero or more chars -- _ means any single chars SELECT fName, salary FROM Staff WHERE fName LIKE '%air%' -- this will give you 'Claire' on table -- If you want list of fName starts with C, you can do 'C%' -- Let's see who earns money the best by ORDER BY SELECT * FROM Staff ORDER BY fName, salary DESC;
- My note taken from Dr. John Dowell’s lectures at UCL - COMP0009: Logic and Database Theory (19/20)
- MySQL Logo: By Source, Fair use, https://en.wikipedia.org/w/index.php?curid=17119753