35.1 Overview of Databases

Relational databases are based on the relational model, storing data in two-dimensional tables and organizing, managing, and querying data through inter-table relationships.

A Relational Database Management System (RDBMS) provides data definition, data manipulation, and data control capabilities through Structured Query Language (SQL), and ensures data consistency and integrity through transaction mechanisms.

This section introduces the basic operations and SQL syntax of relational databases. The SQL examples in this section are all based on MySQL syntax and PostgreSQL syntax. Each non-standard syntax is followed by the corresponding PostgreSQL syntax.

The default command-line client for PostgreSQL, psql, provides a series of meta-commands starting with a backslash (such as \l, \c, \dt, \d, etc.) for quickly viewing and manipulating database objects. These meta-commands are shortcuts specific to the psql client and are not SQL statements; they are only available in the psql interactive interface. In other clients (such as pgAdmin, programming language database drivers, etc.), the corresponding standard SQL queries must be used instead. Wherever psql meta-commands appear below, equivalent standard SQL statements are provided where possible.

Connecting to a Database and Executing SQL Scripts in UNIX Systems

In UNIX or UNIX-like systems, you can connect to a database and execute SQL scripts via the command line.

The following command connects to a MySQL database and executes an SQL script:

# mysql -u root -p          # Log in to MySQL as the root user; -u specifies the username, -p indicates password verification is required
mysql> source FileName.sql  # Execute the specified SQL file in the MySQL interactive interface; the source command reads the file contents and executes SQL statements one by one

Creating a Database

In a relational database, a database is a container for storing related data tables, and each database has its own independent permission control mechanism and storage space.

You can create a new database using an SQL statement:

create database db_name;

• db_name: The database name, used to identify the database. The database name must be unique within the database instance.

Viewing Databases

MySQL syntax:

show databases;

PostgreSQL standard SQL:

SELECT datname FROM pg_database;

psql meta-command:

\l

Entering a Database

MySQL syntax:

use db_name;

psql meta-command (switches the database connection; no standard SQL equivalent):

\c db_name

• db_name: Database name

Dropping a Database

drop database db_name;

• db_name: Database name

Changing the Database Character Set

MySQL syntax (modifying the storage encoding of the database):

alter database xxx
default character set utf8mb4;

PostgreSQL syntax (only modifies the default client encoding for connections, does not change the storage encoding of existing data):

ALTER DATABASE xxx SET client_encoding TO 'UTF8';

Note: MySQL's DEFAULT CHARACTER SET changes the storage encoding of the database, affecting the default encoding of subsequently created tables and columns; PostgreSQL's SET client_encoding only sets the default value for the client connection encoding (GUC parameter) of that database, and does not change the actual storage encoding of the database. The storage encoding of a PostgreSQL database is specified at creation time by CREATE DATABASE ... ENCODING 'UTF8' and cannot be changed after creation.

SQL Data Types

SQL data types define the kinds and formats of data that can be stored in table columns. The following data types use MySQL syntax; some types (such as tinyint, mediumint, unsigned) are MySQL-specific.

PostgreSQL syntax notes: PostgreSQL does not support tinyint and mediumint; use smallint (2B) and integer (4B) instead. PostgreSQL also does not support the unsigned modifier; use a CHECK constraint instead, for example CHECK (col >= 0).

• Integer types

Type	Size	Remarks
tinyint	1B	MySQL-specific
smallint	2B
mediumint	3B	MySQL-specific
int	4B
bigint	8B

• Floating-point and fixed-point types

Type	Size
float	4B
double	8B
decimal(M, D)	M is the precision, D is the scale

• Character types

Type	Description
char(N)	Fixed-length character type, N is the number of characters

• Type modifiers

Keyword	Description
unsigned	Sets an integer type to unsigned (MySQL-specific)

Creating a Table

create table table_name (
  column_name data_type,
  column_name data_type,
  column_name data_type
);

• table_name: Table name
• column_name: Column name
• data_type: Data type

Renaming a Table

alter table old_name rename to new_name;

• old_name: Old table name
• new_name: New table name

Viewing Tables in a Database

MySQL syntax:

show tables;

PostgreSQL standard SQL:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

psql meta-command:

\dt

Displaying Table Structure

MySQL syntax:

desc table_name;

PostgreSQL standard SQL:

SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'table_name';

psql meta-command:

\d table_name

Displaying All Table Information (select * from table_name)

select * from table_name;

SQL Syntax: Comments

Single-line comment:

-- This is a single-line comment: comments are not executed and are used to explain the purpose of SQL statements or important notes

Multi-line comment:

/*
   This is a multi-line comment
   It can span multiple lines, used to describe complex business logic or temporarily disable code blocks
*/

Primary Keys, Unique Constraints, Not Null Constraints, and Auto-increment

MySQL syntax:

create table table_name
(
  -- Primary key
  column_name data_type primary key,
  -- Unique
  column_name char(20) unique,
  -- Not null
  column_name data_type not null,
  -- Auto-increment
  column_name data_type auto_increment
);

PostgreSQL syntax:

CREATE TABLE table_name
(
  -- Primary key
  column_name data_type PRIMARY KEY,
  -- Unique
  column_name CHAR(20) UNIQUE,
  -- Not null
  column_name data_type NOT NULL,
  -- Auto-increment (GENERATED ... AS IDENTITY is recommended; SERIAL is a backward-compatible syntax)
  -- column_name INT GENERATED BY DEFAULT AS IDENTITY
  -- Or (SERIAL itself is a data type, equivalent to INT + auto-increment sequence, but not SQL standard-compliant)
  column_name SERIAL
);

Inserting Data into a Table

insert into table_name (col1, col2) values (123, 'Li Ming');

• col1: Column name 1
• col2: Column name 2

Adding a Column to a Table

alter table table_name
add column new_col data_type;

• new_col: New column name
• data_type: Data type

Dropping a Column from a Table

alter table table_name drop column column_name;

Modifying a Column's Data Type

MySQL syntax:

ALTER TABLE table_name
MODIFY COLUMN column_name new_data_type; -- MySQL-specific syntax

-- For example (reset the data type, disallow null, and set a default value)
ALTER TABLE book_table
MODIFY COLUMN BookName CHAR(100) NOT NULL DEFAULT 'Book Title';

-- If the modified column is a foreign key, you must first drop the foreign key constraint, then modify the data type, and finally re-add the foreign key constraint
alter table child_table drop FOREIGN KEY fk_name;
alter table child_table modify column column_name new_data_type;
alter table child_table add constraint fk_name FOREIGN KEY (column_name) REFERENCES parent_table(parent_column_name);

PostgreSQL syntax:

ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type;
-- For example (reset the data type, disallow null, and set a default value)
ALTER TABLE book_table ALTER COLUMN BookName TYPE CHAR(100);
ALTER TABLE book_table ALTER COLUMN BookName SET NOT NULL;
ALTER TABLE book_table ALTER COLUMN BookName SET DEFAULT 'Book Title';

-- If the modified column is a foreign key, you must first drop the foreign key constraint, then modify the data type, and finally re-add the foreign key constraint
ALTER TABLE child_table DROP CONSTRAINT constraint_name;
ALTER TABLE child_table ALTER COLUMN column_name TYPE new_data_type;
ALTER TABLE child_table ADD CONSTRAINT fk_name FOREIGN KEY (column_name) REFERENCES parent_table(parent_column_name);

Adding a Primary Key to a Table

alter table table_name
add primary key (col_name);

• col_name: The column name to use as the primary key (usually an existing column)

Dropping a Primary Key from a Table

MySQL syntax:

alter table table_name drop primary key;

PostgreSQL syntax:

ALTER TABLE table_name DROP CONSTRAINT constraint_name;
-- The constraint name must be specified; it can be viewed via \d table_name

Unique Constraints

Adding a Unique Constraint to a Table

alter table table_name
add constraint constraint_name unique (col_name);

• constraint_name: Unique constraint name
• col_name: Column name

Dropping a Unique Constraint from a Table

MySQL syntax:

alter table table_name
drop index constraint_name;

PostgreSQL syntax:

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

Foreign Keys

Adding a Foreign Key

ALTER TABLE table_name
ADD CONSTRAINT fk_column_name
FOREIGN KEY (fk_column)
REFERENCES tstu(sid);

• fk_column_name: Foreign key name
• fk_column: Foreign key column name in the child table

Dropping a Foreign Key

MySQL syntax:

alter table table_name
drop foreign key fk_name;

PostgreSQL syntax:

ALTER TABLE table_name DROP CONSTRAINT fk_name;

• table_name: Child table name
• fk_name: Foreign key name

Concept of Foreign Keys

A foreign key is used to establish an association between two tables, consisting of one or more columns that reference the primary key or unique key of another table, ensuring data consistency and integrity. The database management system maintains referential integrity on foreign key constraints, ensuring that foreign key values in the child table must exist in the primary key or unique key of the parent table, or be NULL (if allowed).

Child table: The table that contains the foreign key. The foreign key column in the child table references the primary key or unique key of the parent table. For example, the orders table in the example below.

Parent table: The table referenced by the foreign key. The referenced column in the parent table is typically a primary key (PRIMARY KEY) or unique key (UNIQUE). For example, the customers table in the example below.

Parent Table Data Deletion Failure

Cause: There are records in the child table referencing that data, and the database management system prevents the deletion operation to maintain referential integrity. Solution: Use ON DELETE CASCADE to automatically delete related records in the child table, or manually delete the child table records first, then delete the parent table records. ON DELETE CASCADE is an option for foreign key constraints; when a parent table record is deleted, the database automatically deletes all rows in the child table that reference that record. This mechanism is described in detail in the "Automatically Maintaining Referential Integrity Between Parent and Child Tables" section.

Parent-Child Tables and Foreign Key Example

MySQL syntax:

-- Create the parent table customers with the following columns:
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY, -- Primary key, auto-increment (AUTO_INCREMENT is MySQL-specific)
    name VARCHAR(100) NOT NULL,        -- Customer name
    email VARCHAR(150) UNIQUE          -- Unique email
);

-- Create the child table orders
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY, -- Primary key, order number (AUTO_INCREMENT is MySQL-specific)
    order_date DATE NOT NULL,                -- Order date
    customer_id INT,                         -- Foreign key column, references customer ID
    amount DECIMAL(10,2),                    -- Order amount
    FOREIGN KEY (customer_id) REFERENCES customers(id) -- Define foreign key
    ON DELETE CASCADE -- When a parent table record is deleted, cascade delete related records in the child table
    ON UPDATE CASCADE -- When the parent table primary key is updated, the child table foreign key is automatically updated
);

PostgreSQL syntax:

-- Create the parent table customers using GENERATED BY DEFAULT AS IDENTITY (recommended, SQL standard-compliant)
CREATE TABLE customers (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- Primary key, auto-increment
    name VARCHAR(100) NOT NULL,                          -- Customer name
    email VARCHAR(150) UNIQUE                            -- Unique email
);

CREATE TABLE orders (
    order_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- Primary key, order number
    order_date DATE NOT NULL,                                  -- Order date
    customer_id INT,                                           -- Foreign key column, references customer ID
    amount DECIMAL(10,2),                                      -- Order amount
    FOREIGN KEY (customer_id) REFERENCES customers(id)         -- Define foreign key
    ON DELETE CASCADE -- When a parent table record is deleted, cascade delete related records in the child table
    ON UPDATE CASCADE -- When the parent table primary key is updated, the child table foreign key is automatically updated
);

-- Or using SERIAL (backward-compatible syntax; SERIAL itself is a data type, equivalent to INT + auto-increment sequence, but not SQL standard-compliant)
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,                 -- Primary key, auto-increment
    name VARCHAR(100) NOT NULL,            -- Customer name
    email VARCHAR(150) UNIQUE              -- Unique email
);

-- Create the child table orders
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,           -- Primary key, order number
    order_date DATE NOT NULL,              -- Order date
    customer_id INT,                       -- Foreign key column, references customer ID
    amount DECIMAL(10,2),                  -- Order amount
    FOREIGN KEY (customer_id) REFERENCES customers(id) -- Define foreign key
    ON DELETE CASCADE -- When a parent table record is deleted, cascade delete related records in the child table
    ON UPDATE CASCADE -- When the parent table primary key is updated, the child table foreign key is automatically updated
);

1. Create the parent table customers: Columns: id: Primary key, used to uniquely identify customers. name: Customer name. email: Unique constraint, used to prevent duplicate email addresses.
2. Create the child table orders: order_id: Primary key, used to uniquely identify orders. order_date: Records the order date. customer_id: Foreign key column, used to associate with customers. amount: Order amount.
3. Set foreign key: FOREIGN KEY (customer_id) REFERENCES customers(id) indicates that the customer_id column in the child table references the id column in the parent table. ON DELETE CASCADE When a parent table record is deleted, rows in the child table referencing that record will also be deleted. ON UPDATE CASCADE When the parent table primary key is updated, the child table foreign key column will be automatically updated.

Modifying Table Information, Deleting Rows

-- Modify table information
update table_name
set column_name = new_value
where condition;

The SET clause is used to specify the columns to be modified. If you only want to modify certain records, you should use the WHERE clause to limit the conditions and clarify the scope of modifications.

-- Delete rows
DELETE FROM book_table
WHERE BookNumber < 200;

Here are a few examples:

update book_table
set Price = Price * 1.2
where Publisher = "People's Posts and Telecommunications Press"; -- MySQL allows double quotes for string values by default; in standard SQL and PostgreSQL, double quotes are for identifier quoting, and string values must use single quotes
-- Note: If MySQL has the ANSI_QUOTES SQL mode enabled (SET sql_mode = 'ANSI_QUOTES'), double quote behavior matches standard SQL and can only be used for quoting identifiers, not for wrapping string values

This example multiplies the Price value of all records in the book_table where the Publisher value is "People's Posts and Telecommunications Press" by 1.2, effectively increasing the price by 20%.

DELETE FROM book_table
WHERE BookNumber < 200;

DELETE FROM book_table
WHERE author IN ('Wang Yang', 'Liu Tianyang');

WHERE Clause

The WHERE clause is an important component of SQL statements, used to specify filtering conditions so that only records meeting the conditions are operated on.

...(update or delete)
where Price < 50;
...(update or delete)
WHERE author = 'Wang Yang' or author = 'Liu Tianyang';
...(update or delete)
WHERE name in ('Zhang San', 'Li Si');

1. If the Price value is less than 50, the corresponding operation is performed.
2. If the author value is Wang Yang or Liu Tianyang, the corresponding operation is performed.
3. If the name value is Zhang San or Li Si, the corresponding operation is performed.

The WHERE clause specifies a conditional expression that evaluates and returns a boolean value.

SQL Operators

Operators are symbols used in SQL statements for data calculation and comparison, and are classified into arithmetic operators, comparison operators, logical operators, and special operators.

• Arithmetic operators

Operator	Description
+	Addition
-	Subtraction
*	Multiplication
/	Division
%	Modulo

• Comparison operators

Operator	Description
=	Equal to (used for comparison in SQL, not assignment)
!=	Not equal to
<	Less than
>	Greater than
<=	Less than or equal to
>=	Greater than or equal to
<=>	NULL-safe equal to, NULL <=> NULL returns TRUE (MySQL-specific)

PostgreSQL syntax notes: PostgreSQL does not support the <=> operator; to test NULL equality, use IS NOT DISTINCT FROM.

• Logical operators

Operator	Description
and	Returns true only when both conditions are true, otherwise false
or	Returns true when at least one condition is true, returns false only when both are false
not	Reverses true and false
xor	Returns true only when exactly one condition is true, otherwise false (MySQL-specific)

PostgreSQL syntax notes: PostgreSQL does not support the xor operator; use (a AND NOT b) OR (NOT a AND b) instead.

• Special operators

Operator	Description
in	"Value in list" operator, used to filter records that meet the condition and returns a boolean result for use in update or delete statements. For example, 5 in (1, 3, 5) returns true
between	Range matching: for example, 5 between 1 and 10 returns true
like	Pattern matching: 'abc' like 'a%' returns true. Percent sign (%) matches zero or more arbitrary characters, underscore (_) matches a single arbitrary character
IS NULL	"Check whether a table entry is NULL": NULL IS NULL returns TRUE

Example: Check whether a book title entry matches the pattern "xxx design yyy".

select * from book_table
where BookName like '%design%';

select * from book_table
where BookName like '_____';
-- Five underscores represent five characters, returning book titles with a length of 5 characters.

In a book management database, using LIKE '%design%' would return book titles such as "MySQL Database Design".

Automatically Maintaining Referential Integrity Between Parent and Child Tables

Referential integrity ensures data consistency between related tables. When data in the parent table changes, the related data in the child table must also be adjusted accordingly.

Option	Description
CASCADE	Cascade operation: when a parent table record is deleted/updated, related records in the child table are also deleted/updated
SET NULL	Sets the foreign key column in the child table to NULL (requires the foreign key column to allow NULL)
RESTRICT	Rejects the operation: does not allow deletion/update of referenced records in the parent table (returns an error immediately)
NO ACTION	Similar to RESTRICT. In MySQL, the two are completely equivalent, both immediately rejecting operations that violate referential integrity. In the SQL standard, RESTRICT is non-deferrable; if the constraint is DEFERRABLE, NO ACTION can defer integrity checking until transaction end. PostgreSQL follows the SQL standard, and the two behaviors differ
SET DEFAULT	Sets to the default value; MySQL does not support this

SELECT Statement

The SELECT statement is the most commonly used query statement in SQL, used to retrieve data from a database. The SELECT keyword is followed by the column names to query; use * to select all columns. The basic syntax of the SELECT statement includes the SELECT clause (specifying the columns to return), the FROM clause (specifying the data source table), the WHERE clause (specifying filter conditions), the GROUP BY clause (group aggregation), the HAVING clause (group filtering), and the ORDER BY clause (result sorting).

select * from student_table
where age = (select max(age) from student_table);

This query displays records in student_table where the age equals the maximum age value in the table. The inner subquery (select max(age) from student_table) executes first, returning the maximum age value in the table; the outer query uses the WHERE clause to filter all records whose age equals that maximum value.

Ascending and Descending Output

Sorting is an important function of queries. The ORDER BY clause can specify the sorting method of query results.

ASC for ascending, DESC for descending.

SELECT student_name FROM student_table ORDER BY student_name DESC;

MySQL syntax: LIMIT a, b is used to limit the number of query results, where a represents the starting position (starting from 0) and b represents the number of records to return. PostgreSQL syntax: LIMIT b OFFSET a.

MySQL syntax:

SELECT * from `student_table` -- Backticks are MySQL-specific identifier quoting; standard SQL uses double quotes
ORDER BY `student_id` desc
LIMIT 0,3;

PostgreSQL syntax:

SELECT * FROM "student_table"
ORDER BY "student_id" DESC
LIMIT 3 OFFSET 0;

Join Queries

Join queries are a core feature of relational databases, allowing data to be retrieved from multiple related tables simultaneously.

Explicit Join

SELECT name, score --name from the student table, score from the score table
FROM stu_table -- Table one
JOIN score_table -- Table two
ON stu_table.stu_id = score_table.s_id; -- Join condition
WHERE name = 'Jack' -- Filter condition; AND or OR can be appended after this.

name is from stu_table score is from score_table

Implicit Join

SELECT name, score
FROM stu_table, score_table
WHERE stu_table.stu_id = score_table.s_id
AND name = 'Jack';

Although implicit join syntax is more concise, explicit joins are recommended in practice for better readability and maintainability.