> For the complete documentation index, see [llms.txt](https://book.bsdcn.org/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://book.bsdcn.org/ask/flat/chapter-35-database-management/di-35.1-jie-shu-ju-ku-gai-lun.md). # 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: ```sh # 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: ```sql 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: ```sql show databases; ``` PostgreSQL standard SQL: ```sql SELECT datname FROM pg_database; ``` psql meta-command: ```sql \l ``` ## Entering a Database MySQL syntax: ```sql use db_name; ``` psql meta-command (switches the database connection; no standard SQL equivalent): ```sql \c db_name ``` * `db_name`: Database name ## Dropping a Database ```sql drop database db_name; ``` * `db_name`: Database name ## Changing the Database Character Set MySQL syntax (modifying the storage encoding of the database): ```sql 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): ```sql 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 ```sql 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 ```sql 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: ```sql show tables; ``` PostgreSQL standard SQL: ```sql SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'; ``` psql meta-command: ```sql \dt ``` ## Displaying Table Structure MySQL syntax: ```sql desc table_name; ``` PostgreSQL standard SQL: ```sql SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'table_name'; ``` psql meta-command: ```sql \d table_name ``` ## Displaying All Table Information (select \* from table\_name) ```sql select * from table_name; ``` ## SQL Syntax: Comments Single-line comment: ```sql -- 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: ```sql /* 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: ```sql 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: ```sql 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 ```sql insert into table_name (col1, col2) values (123, 'Li Ming'); ``` * `col1`: Column name 1 * `col2`: Column name 2 ## Adding a Column to a Table ```sql 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 ```sql alter table table_name drop column column_name; ``` ## Modifying a Column's Data Type MySQL syntax: ```sql 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: ```sql 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 ```sql 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: ```sql alter table table_name drop primary key; ``` PostgreSQL syntax: ```sql 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 ```sql 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: ```sql alter table table_name drop index constraint_name; ``` PostgreSQL syntax: ```sql ALTER TABLE table_name DROP CONSTRAINT constraint_name; ``` ## Foreign Keys ### Adding a Foreign Key ```sql 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: ```sql alter table table_name drop foreign key fk_name; ``` PostgreSQL syntax: ```sql 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: ```sql -- 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: ```sql -- 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 ```sql -- 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. ```sql -- Delete rows DELETE FROM book_table WHERE BookNumber < 200; ``` Here are a few examples: ```sql 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%. ```sql 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. ```sql ...(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"`. ```sql 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). ```sql 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. ```sql 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: ```sql 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: ```sql 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 ```sql 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 ```sql 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. --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://book.bsdcn.org/ask/flat/chapter-35-database-management/di-35.1-jie-shu-ju-ku-gai-lun.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.