6. sql

Standard Query Language ESQU

The Database Server

• Represented schematically as a cylinder.

• In this case, resides on the same machine as the web server but accessed over a different port.

Relational Databases

• SQL (Structured Query Language) is used with Relational Database Management Systems (RDBMS).

• A relational database structures groups of related data using schemas.

• Data is grouped into tables.

  • Each row (record) refers to a unique item.

  • Records have columns (fields), with each storing a single facet of the data.

• Data organization: Records and fields are within a table inside a database.

Indices and Primary Keys

• Tables generally have indices to search and sort rows easily.

• A primary key is a unique index for each record in a table.

Relating Tables

• Tables in a database may have relationships via their indices.

  • Example: The primary key customer_id in the Customers table relates to orders in the Orders table, where it acts as a foreign key.

  • The order_id in the Shipments table relates shipments to specific orders.

  • This allows you to connect a shipment back to its corresponding customer.

• The database for this example will not require more than one table.

Where SQL Fits In

• SQL is a standardized language for creating, updating, and deleting databases, tables, and records.

• It is compatible with various RDBMS.

• RDBMS have rigid structures; updating the database requires code modifications.

SQL Operations

• Operations on records in tables:

  • SELECT

  • UPDATE

  • INSERT INTO

  • DELETE

• Operations on tables in databases:

  • CREATE TABLE

  • ALTER TABLE

  • DROP TABLE

• Operations on databases:

  • CREATE DATABASE

  • DROP DATABASE

Field Types

• Fields in a table can have various data types:

  • int: for integers.

  • varchar(255): for strings of length 255.

• To create a primary key that auto-increments with each record, specify it as:

  • int auto_increment primary key.