Skip to content

Latest commit

 

History

History
90 lines (66 loc) · 3.17 KB

4.10.4.1 SQL.md

File metadata and controls

90 lines (66 loc) · 3.17 KB
Raw

Structured Query Language (SQL)

Data manipulation (DML)

Be able to use SQL to retrieve, update, insert and delete data from multiple tables of a relational database.

Used to create, manipulate and maintain databases in a variety of management systems.

The aim of SQL was to provide a special-purpose programming language that was very high level so that database administrators could manipulate their databases efficiently.

There are two language sections in SQL:

  1. The data definition language - used to build the structure of a database
  2. The data manipulation language - used to manipulate the data within a database

Creating a database (DDL)

Be able to use SQL to define a database table.

The data definition language consists of a variety of commands to allow the user to define structure of their database. They will do this by creating, altering and deleting tables.

The DDL gives admins the ability to apply constraints to individual tables or the whole database, giving users different privilages when it comes to accessing and editing the data.

When creating a database using SQL we need:

  • Fields
  • Types
  • A Primary Key

Example

CREATE DATABASE db;
CREATE USER dbuser IDENTIFIED BY 'password456';
GRANT ALL ON db.* to dbuser;

CREATE TABLE db.customer (
  customerID INTEGER,
  firstName VARCHAR(20),
  surname VARCHAR(20),
  phone VARCHAR(14),
  PRIMARY KEY (customerID),
  UNIQUE INDEX (customerID)
);

CREATE TABLE db.order (
  customerID INTEGER,
  orderID INTEGER,
  PRIMARY KEY (orderID),
  FOREIGN KEY (customerID) REFERENCES (customer(customerID)),
  UNIQUE INDEX (orderID)
);

Data Manipulation Language (DML)

The data manipulation language is used to populate and update the database.

The DML allows the user to insert, modify, delete and query a table for:

  • all data
  • a specific record
  • records that meet criteria

Methods

Method Command Example
Create INSERT INSERT INTO customer(customerID, firstName, surname, phone) VALUES (1, 'Bob', 'Smith', '+447243615342');
Retrieve SELECT SELECT * FROM customer WHERE customerID = 1;
Update UPDATE UPDATE customer SET firstName = "Matthew" WHERE customerID = 1;
Delete DELETE DELETE FROM customer WHERE customerID = 1;

Joins

There are a number of ways to retrieve data across more than one table.

Name Description Example
From Using a standard FROM to be able to select across multiple tables SELECT customer.firstName, order.product FROM customer, order WHERE customer.customerID = order.customerID;
Joins A join will join two tables on a certain attribute SELECT customer.firstName, order.product FROM order INNER JOIN customer ON customer.customerID = order.customerID;

Types of JOIN

Type Uses
Inner Join Returns data from all tables in join only when the ON condition returns a result. If there is no customerID value in the ON table then nothing will be returned
Left Join Returns data from the left table regardless of whether the condition is true, will only join the tables on rows where condition is true