Database development

SQL

definition

SQL (Structured Query Language) is a structured query language used to manage and operate relational databases. It is one of the most widely used languages ​​in database management systems.

Main functions

basic grammar

-- Query data
SELECT * FROM table name WHERE condition;

--Insert data
INSERT INTO table name (column 1, column 2) VALUES (value 1, value 2);

--Update data
UPDATE table name SET column 1 = value 1 WHERE condition;

-- Delete data
DELETE FROM table name WHERE condition;

--Create table
CREATE TABLE table name (
    Column name 1 data type,
    Column name 2 data type
);

Common data types

advantage

MySQL

relational database

MySQL is a popular open source relational database management system (RDBMS) that uses SQL as the query language and is suitable for small to medium-sized to large applications.

characteristic

Usage examples

mysql -u root -p
CREATE DATABASE example_db;
USE example_db;
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), age INT);
INSERT INTO users (name, age) VALUES ('Alice', 30);
SELECT * FROM users;

Applicable scenarios

SQLite

Lightweight database

SQLite is an embedded database that does not require a separate server for management and is suitable for lightweight applications.

characteristic

Usage examples

sqlite3 example.db
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER);
INSERT INTO users (name, age) VALUES ('Alice', 30);
SELECT * FROM users;

Applicable scenarios

PostgreSQL

PostgreSQL (often referred to as Postgres) is a powerful open source object-relational database system (ORDBMS). It is based on more than 30 years of active development and has a strong reputation for reliability, functional robustness, and performance, and is known as "the world's most advanced open source database."

Core features

Technical architecture

PostgreSQL adopts a client-server architecture and assigns independent handlers to each connection, which ensures that the crash of a single connection will not affect the stability of the entire database server.

Common extension kits

The power of Postgres comes largely from its rich ecosystem extension:

Comparison of advantages and disadvantages

advantage shortcoming
High data integrity and security When writing large amounts of data at a high frequency, the VACUUM mechanism may cause performance fluctuations.
Support complex data analysis and business logic Memory consumption is generally higher compared to MySQL
The open source community is extremely active and there is no vendor lock-in There are many parameters to set, and beginners need a long learning curve to configure and optimize performance.

Applicable scenarios

T-SQL

T-SQL, whose full name is Transact-SQL, is an extended version of the SQL standard developed by Microsoft and Sybase. It is the core communication language for Microsoft SQL Server and Azure SQL-related services. Compared with standard SQL, T-SQL adds programming capabilities, allowing it to not only query data, but also handle complex logical operations.

language features

Key differences between T-SQL and standard SQL

Function type Standard SQL (ANSI) T-SQL (Microsoft)
String concatenation Use double vertical bars || Use the plus sign +
Limit the number of columns returned Use FETCH FIRST Use the TOP keyword
Data type conversion CAST CAST and CONVERT (supports formatting)
program logic Mainly basic inquiries Complete procedural programming language capabilities

Basic grammar example

The following code shows how to combine variables, logical judgments and data queries in T-SQL:

-- Declare and set variables
DECLARE @Threshold INT = 100;
DECLARE @CurrentStock INT;

-- Get the inventory of a specific product
SELECT @CurrentStock = StockQuantity
FROM Products
WHERE ProductID = 5;

-- logical judgment
IF @CurrentStock < @Threshold
BEGIN
    PRINT 'Warning: Inventory is below preset threshold. ';
    -- Execute replenishment logic...
END
ELSE
BEGIN
    SELECT * FROM Products WHERE ProductID = 5;
END

Application scenarios

T-SQL is widely used in the following fields:

T-SQL program development

T-SQL extends standard SQL to provide full programmatic capabilities. Through variables, logic control and error handling, developers can write complex business logic at the database level.

Variable declaration and assignment

In T-SQL, all custom variables must start with the @ symbol. Use DECLARE for declaration and SET or SELECT for assignment.

-- Declare variables
DECLARE @EmployeeCount INT;
DECLARE @DepartmentName NVARCHAR(50);

-- assignment
SET @DepartmentName = 'IT Department';

--Assign values from query results
SELECT @EmployeeCount = COUNT(*)
FROM Employees
WHERE Department = @DepartmentName;

-- Output results
PRINT @EmployeeCount;

Process control IF...ELSE

IF...ELSE statements allow different blocks of code to be executed based on conditions. If a block contains multiple statements, they must be wrapped with BEGIN...END.

DECLARE @StockLevel INT;
SET @StockLevel = 10;

IF @StockLevel < 5
BEGIN
    PRINT 'Inventory is seriously low, please restock immediately. ';
END
ELSE IF @StockLevel < 20
BEGIN
    PRINT 'Inventory is low, it is recommended to restock. ';
END
ELSE
BEGIN
    PRINT 'In stock. ';
END

Error handling TRY...CATCH

The TRY...CATCH mechanism provided by T-SQL is similar to modern programming languages ​​(such as C# or Java) and can capture exceptions during execution to prevent abnormal program interruption.

BEGIN TRY
    -- Code to try to execute
    INSERT INTO Sales (OrderID, ProductID, Quantity)
    VALUES (1001, 'P01', -5); -- Assume this will trigger a constraint error
END TRY
BEGIN CATCH
    -- Handling when errors occur
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage,
        ERROR_SEVERITY() AS Severity;
        
    --Rollback transaction
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH

Use Name instead of ID field

Applicable conditions

Advantages

potential problems

Applicable examples

Such as the profile type data table:

CREATE TABLE config_types (
  name VARCHAR(50) PRIMARY KEY,
  description TEXT
);

INSERT INTO config_types (name, description) VALUES ('general', 'General settings');
SELECT * FROM config_types WHERE name = 'general';

Relational database design inheritance objects

Database structure design

animal form

This table will store the common attributes of all "animals".

Field name data type illustrate
id INT animal's unique identifier
species VARCHAR(50) types of animals
age INT animal age

cat table

This table will inherit the id of the animal table and store the unique attributes of "cat".

Field name data type illustrate
id INT Corresponds to the id of the animal table
breed VARCHAR(50) cat breed
favorite_food VARCHAR(50) Cat's favorite food

SQL create table command

CREATE TABLE animal (
    id INT PRIMARY KEY AUTO_INCREMENT,
    species VARCHAR(50) NOT NULL,
    age INT NOT NULL
);

CREATE TABLE cat (
    id INT PRIMARY KEY,
    breed VARCHAR(50),
    favorite_food VARCHAR(50),
    FOREIGN KEY (id) REFERENCES animal(id)
);

Example of inserting data

INSERT INTO animal (species, age) VALUES ('Cat', 3);

INSERT INTO cat (id, breed, favorite_food) VALUES (1, 'Siamese', 'Fish');

HTML table display example

animal information

Animal ID type age
1 Cat 3

Cat-specific information

Animal ID variety love food
1 Siamese Fish

illustrate

In this example,animalThe table stores the common attributes of all animals, andcatThe table stores the cat's unique attributes.catin the tableidis a referenceanimaltabularid, indicating that this is an inheritance relationship.

Query information about all animals

SELECT * FROM animal;

Query information about all cats

This query will return complete information about all cats, including common attributes inherited from the animal table.

SELECT animal.id, animal.species, animal.age, cat.breed, cat.favorite_food
FROM animal
JOIN cat ON animal.id = cat.id;

Query information about a specific animal (such as a specific ID)

SELECT * FROM animal WHERE id = 1;

Query information about a specific cat (such as a specific breed)

SELECT animal.id, animal.species, animal.age, cat.breed, cat.favorite_food
FROM animal
JOIN cat ON animal.id = cat.id
WHERE cat.breed = 'Siamese';

illustrate

In these query examples we useJOINWillanimaltable andcatThe tables are combined to obtain complete information about the cat. This approach ensures that query results include both inherited and unique properties.

FOREIGN KEY

use

FOREIGN KEY (foreign key) is used to establish the association between two data tables to ensure the reference integrity of the data. For example, a field value in one data table must reference a primary key or unique value in another data table.

grammar

CREATE TABLE subtable (
  Field Name Data Type,
  FOREIGN KEY (foreign key field) REFERENCES parent data table (primary key field)
);

example

Establish a one-to-many relationship, such as orders and customers:

-- Create parent data table (customers)
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(50)
);

--Create sub-tables (orders) and set foreign keys
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Things to note

Advanced usage

can pass throughON DELETEandON UPDATESpecify foreign key behavior:

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
  ON DELETE CASCADE
  ON UPDATE CASCADE
);

Behavior options

Add comments to CREATE TABLE

Use COMMENT to set field comments

In MySQL, you can useCOMMENTto add notes to the fields.

CREATE TABLE users (
  id INT PRIMARY KEY COMMENT 'User unique identification code',
  name VARCHAR(50) COMMENT 'User name',
  age INT COMMENT 'User's age'
);

Use COMMENT to set table comments

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  ageINT
) COMMENT = 'User information table';

View field notes

The comments of the field can be queried through the following syntax:

SHOW FULL COLUMNS FROM users;

Modify field notes

ALTER TABLE users MODIFY COLUMN name VARCHAR(50) COMMENT 'Modified remarks';

Scope of application

PostgreSQL settings notes

COMMENT ON COLUMN users.name IS 'user name';

SQL column names use reserved words

In SQL queries, when the field name conflicts with the reserved word (Keyword) of the database system, a syntax error will occur. Dealing with this problem requires comprehensive consideration from both prevention and solution aspects.

1. Fundamental solution: avoid using reserved words (database design)

This is the most recommended approach and aims to eliminate the problem at its source.

2. Technical solution: Use quotation marks to wrap identification symbols (program code implementation)

When the use of reserved words cannot be avoided (for example: when working with an existing database structure), the field name must be wrapped in appropriate quotation marks to clearly inform the SQL engine that this is an identifier and not an SQL command.

database system quotation marks example
MySQL / MariaDB backtick (`) SELECT `interval`, `time` FROM table;
PostgreSQL / Oracle double quotes (") SELECT "interval", "time" FROM table;
SQL Server square brackets ([]) SELECT [interval], [time] FROM table;

3. Program code automation (dynamically generate SQL)

In programming languages ​​such as Python, if you need to dynamically generate SQL statements, you should implement a function to automatically execute quotation marks to ensure that the generated statements are safe and correct.

4. Advantages of ORM framework

If you use an ORM (such as SQLAlchemy or Django ORM), the framework automatically handles reserved words and quotation mark differences between different databases, abstracting the underlying SQL output, which greatly simplifies development work and improves the stability of the code.

SQL DATETIME comparison

Use TIMESTAMPDIFF

Calculates the time difference in seconds between two DATETIME fields.

SELECT * FROM table_name
WHERE TIMESTAMPDIFF(SECOND, datetime_column1, datetime_column2) < 5;

Use ABS(TIMESTAMPDIFF)

Ensure that the time difference is an absolute value to avoid order effects.

SELECT * FROM table_name
WHERE ABS(TIMESTAMPDIFF(SECOND, datetime_column1, datetime_column2)) < 5;

Use DATEDIFF (only for day comparisons)

If you just want to compare whether they are on the same day, you can use DATEDIFF.

SELECT * FROM table_name
WHERE DATEDIFF(datetime_column1, datetime_column2) = 0;

Subtract directly using TIMESTAMP

Applicable to databases that support timestamp operations, such as MySQL.

SELECT * FROM table_name
WHERE ABS(UNIX_TIMESTAMP(datetime_column1) - UNIX_TIMESTAMP(datetime_column2)) < 5;

MySQL handles millisecond timestamps

The value you provided1763251200000Is a standard **millisecond timestamp** (Unix Epoch Time in milliseconds).

MySQL built-in functions (such asFROM_UNIXTIME) The default processing is **second level timestamp**. Therefore, you need to convert millisecond timestamps to seconds before use.

Correction: Divide by 1000

Divide your millisecond value by $1000$ to get the second-level timestamp required by the MySQL function:

1763251200000 / 1000 = 1763251200

1. Convert to DATETIME format

useFROM_UNIXTIME()Function converts second-level timestamp to standard MySQLDATETIMEFormat:

SELECT FROM_UNIXTIME(1763251200000 / 1000);

If your version of MySQL supports milliseconds and you want to preserve millisecond precision in the results, you can use the second parameter:

SELECT FROM_UNIXTIME(1763251200000 / 1000, '%Y-%m-%d %H:%i:%s.%f');

in:

2. Convert to UNIX_TIMESTAMP (seconds)

If your intention is to store the millisecond value in an integer (e.g.INTorBIGINT) field as the second-level timestamp, then only a simple division operation is required:

SELECT 1763251200000 / 1000;

Or, if your data is stored in table fields (for example, the field is namedtimestamp_ms）：

SELECT timestamp_ms / 1000 AS unix_timestamp_s FROM your_table;

3. Save to DATETIME/TIMESTAMP field

When you insert this value into something that hasDATETIMEorTIMESTAMPtype field, you need to enterVALUESPartially perform the conversion:

INSERT INTO your_table (datetime_column) 
VALUES (FROM_UNIXTIME(1763251200000 / 1000));

4. Handling UTC time zone

FROM_UNIXTIME()The function converts the UTC timestamp to the time zone configured for your MySQL server. If your timestamp is based on UTC and you want the result to be UTC as well, you need to make sure your server or connection time zone is set correctly, or useCONVERT_TZFunction performs explicit time zone handling.

Format FLOAT

Use FORMAT()

In MySQL, useFORMAT()to format floating point numbers.

SELECT FORMAT(123.4567, 2); -- Result: '123.46'

Use ROUND()

ROUND()Used for rounding to a fixed number of decimal places.

SELECT ROUND(123.4567, 2); -- Result: 123.46

Use CAST() or CONVERT()

Convert FLOAT to DECIMAL to maintain a fixed number of decimal places.

SELECT CAST(123.4567 AS DECIMAL(10,2)); -- Result: 123.46
SELECT CONVERT(123.4567, DECIMAL(10,2)); -- Result: 123.46

Apply to data table fields

SELECT id, FORMAT(price, 2) AS formatted_price FROM products;

Format fields

You can directly set the number of decimal points when creating a data table.

CREATE TABLE products (
  id INT PRIMARY KEY,
  price DECIMAL(10,2) -- two decimal places
);

Query field total

Use MAX()

Get the maximum value of the field.

SELECT MAX(price) AS max_price FROM products;

Use MIN()

Get the minimum value of the field.

SELECT MIN(price) AS min_price FROM products;

Use AVG()

Calculate the average of the fields.

SELECT AVG(price) AS avg_price FROM products;

Query MAX, MIN, AVG simultaneously

SELECT 
  MAX(price) AS max_price, 
  MIN(price) AS min_price, 
  AVG(price) AS avg_price
FROM products;

Calculated by category

SELECT category, 
       MAX(price) AS max_price, 
       MIN(price) AS min_price, 
       AVG(price) AS avg_price
FROM products
GROUP BY category;

Use subquery to find maximum value

Find the maximum value of another query result.

SELECT MAX(price) FROM (SELECT price FROM products WHERE category = 'electronics') AS subquery;

Use ORDER BY + LIMIT

Get the maximum value after sorting.

SELECT price FROM products WHERE category = 'electronics' ORDER BY price DESC LIMIT 1;

Use WITH (Common Table Expression, CTE)

WITH filtered_products AS (
  SELECT price FROM products WHERE category = 'electronics'
)
SELECT MAX(price) FROM filtered_products;

geometric mean formula

Geometric Mean calculation formula:

GM = (x1 * x2 * ... * xn)^(1/n)

Using EXP() and LOG()

In SQL, the geometric mean can be calculated using logarithmic operations.

SELECT EXP(AVG(LOG(price))) AS geometric_mean FROM products WHERE price > 0;

Use POWER()

usePOWER()Compute the nth root:

SELECT POWER(EXP(SUM(LOG(price))), 1 / COUNT(price)) AS geometric_mean 
FROM products WHERE price > 0;

Things to note

SELECT EXP(AVG(LOG(price))) AS geometric_mean FROM products WHERE price > 0;

SQL calculate standard deviation of field

Calculate standard deviation using STDDEV()

In MySQL/MariaDB, you can useSTDDEV()to calculate the standard deviation.

example:

SELECT STDDEV(salary) AS salary_stddev FROM employees;

Distinguish between parent standard deviation and sample standard deviation

SQL provides two methods of calculating standard deviation:

example:

SELECT 
    STDDEV_POP(salary) AS population_stddev, 
    STDDEV_SAMP(salary) AS sample_stddev
FROM employees;

Calculate standard deviation manually

If the SQL version does not supportSTDDEV(), you can use the following formula:

SELECT SQRT(
    SUM(POW(salary - (SELECT AVG(salary) FROM employees), 2)) / COUNT(salary)
) AS salary_stddev
FROM employees;

in conclusion

SQL WHERE clause

SQLWHEREclause is used to extract from the tableExtract records that meet specified conditions(column/row). It is one of the most basic and important parts of the Data Manipulation Language (DML), used to accurately filter the required data.

Role and purpose

basic grammar

WHEREclause usually followsFROMAfter clause:

SELECT column_name(s)
FROM table_name
WHERE condition;

or when used to modify or delete information:

UPDATE table_name
SET column1 = value1
WHERE condition;

Commonly used comparison and logical operators

WHEREOperators are used in clauses to establish conditions. The most common ones are:

Operator type operator describe
comparison operator = equal
comparison operator >, <, >=, <= Greater than, less than, greater than or equal to, less than or equal to
comparison operator <>or!= not equal to
Logical operators AND Meet multiple conditions at the same time
Logical operators OR satisfy any of the conditions
Logical operators NOT Condition is not met
special operators BETWEEN Within a certain range (including boundaries)
special operators LIKE Fuzzy matching string pattern (matching%or_）
special operators IN The value is any item in the list
special operators IS NULL / IS NOT NULL Whether the field value is NULL

Usage examples

Suppose there is a file namedEmployeestable, includingEmployeeID, LastName(surname),Salary(salary) andDepartment(department).

Example 1: Single condition

Find all employees with department 'Sales':

SELECT EmployeeID, LastName
FROM Employees
WHERE Department = 'Sales';

Example 2: Numeric comparison and logical AND

Find employees whose salary is greater than or equal to 50,000 and whose department is not 'HR':

SELECT *
FROM Employees
WHERE Salary >= 50000 AND Department != 'HR';

Example 3: Range BETWEEN

Find employees with salaries between 60,000 and 80,000 (inclusive):

SELECT *
FROM Employees
WHERE Salary BETWEEN 60000 AND 80000;

Example 4: List IN

Find employees in the 'Marketing' or 'Finance' department:

SELECT *
FROM Employees
WHERE Department IN ('Marketing', 'Finance');

This is equivalent to usingWHERE Department = 'Marketing' OR Department = 'Finance'。

SQL GROUP BY clause

SQLGROUP BYclause is used toSELECTIn a statement, rows with the same value (or combination of values ​​for multiple fields) are grouped into summary columns. It is often used with SQLsummary functionUsed together to calculate a summary value for each group.

Role and purpose

basic grammar

GROUP BYclause must be inWHEREclause, but afterHAVINGandORDER BYbefore clause.

SELECT column_name(s), aggregate_function(column_to_summarize)
FROM table_name
WHERE condition_on_rows -- (optional) filter single rows before grouping
GROUP BY column_name(s) -- specifies the column used for grouping
HAVING condition_on_groups -- (optional) filter groups after grouping
ORDER BY column_name(s);

Important rules:anything that appears inSELECTin the list, butNoColumns surrounded by summary functions aremustincluded inGROUP BYin clause.

Usage examples

Suppose there is a file namedProductstable, includingCategory(product category) andPrice(price).

Demand: Find the average price and product quantity for each product category.

SELECT Category, AVG(Price) AS AveragePrice, COUNT(ProductID) AS TotalProducts
FROM Products
GROUP BY Category;

Example description

Multi-column grouping

You can group based on multiple fields. Rows will be grouped together only if all specified fields have the same value.

Demand: Find the total sales for each product category (Category) and supplier (Supplier) combination.

SELECT Category, Supplier, SUM(SalesAmount) AS TotalSales
FROM SalesRecords
GROUP BY Category, Supplier
ORDER BY Category, Supplier;

This will produce a result where each row represents the total sales for a unique Category + Supplier combination.

Commonly used summary functions

function Function
COUNT() Count the number of rows in a group.
SUM() Calculates the sum of numeric fields in a group.
AVG() Calculates the average of numeric fields in a group.
MAX() Find the maximum value of a field in a group.
MIN() Find the minimum value of a field in a group.

SQL HAVING clause

In SQL,HAVINGclause is used to filter group (Group) results, it is usually used withGROUP BYclauses are used together.

AlthoughWHEREclause is used to filter the rows of a single column (Rows), but is useful when group-based summary values ​​are required (e.g.COUNT(), SUM(), AVG()When filtering groups by waiting for the result of the summary function), you must useHAVINGclause.

The difference between HAVING and WHERE

feature WHERE clause HAVING clause
execution timing Filter the original rows before the data is grouped (GROUP BY). After the data is grouped (GROUP BY), filter the summarized groups.
Available conditions Summary functions (such as COUNT, SUM, AVG) cannot be used directly. Aggregation functions must be used to set filter conditions.
Application objects A single column value. Group results.

basic grammar

SELECT column_name(s), aggregate_function(column_name)
FROM table_name
WHERE condition_on_rows -- (optional) filter single rows
GROUP BY column_name(s)
HAVING condition_on_groups -- (required) filter groups
ORDER BY column_name(s);

Usage examples

Suppose there is a file namedOrderstable, includingCustomerID(Customer ID) andTotalAmount(total amount of order). we want to find out allAverage order amount exceeds 500of customers.

SQL query

SELECT CustomerID, AVG(TotalAmount) AS AverageOrder
FROM Orders
GROUP BY CustomerID
HAVING AVG(TotalAmount) > 500;

Example description

Compound condition example

Suppose we want to find all customers whose total order count is **3** and whose average order amount is less than 1000**.

SQL query

SELECT CustomerID, COUNT(OrderID) AS TotalOrders, AVG(TotalAmount) AS AverageOrder
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) >= 3 AND AVG(TotalAmount) < 1000;

Example description

SQL reference alias using subquery

Problem description

existSELECTIn query, if definedexpr1 AS field1, can it be inexpr2used infield1？

Answer: No, because of the order in which SQL queries are executed

The execution order of SQL determines that aliases cannot be in the sameSELECTInternally quoted again:

SELECT price * 1.1 AS new_price, new_price + 10 AS final_price FROM products; -- Error

error message:

Unknown column 'new_price' in 'field list'

Solution

Method 1: Use a subquery

can be calculated first in the subquerynew_price, and then referenced in the outer query:

SELECT new_price, new_price + 10 AS final_price
FROM (SELECT price * 1.1 AS new_price FROM products) AS subquery;

Method 2: Using CTE (WITH statement)

Available if SQL supports Common Table Expressions (CTE)WITHTo simplify:

WITH cte AS (
    SELECT price * 1.1 AS new_price FROM products
)
SELECT new_price, new_price + 10 AS final_price FROM cte;

Method 3: Repeat the expression

If it is just a simple operation, you can repeat the calculation directly (but it is not recommended because the readability is poor):

SELECT price * 1.1 AS new_price, price * 1.1 + 10 AS final_price FROM products;

in conclusion

JOIN

JOIN Introduction

JOIN is used to merge related data from multiple data tables and establish a relationship based on a certain field (usually a foreign key).

INNER JOIN

Only the data that meets the conditions in the two data tables are returned.

SELECT orders.order_id, customers.name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

LEFT JOIN

Returns all the data in the left table. If there is no corresponding data in the right table, it will be displayed.NULL。

SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

RIGHT JOIN

Returns all the data in the right table. If there is no corresponding data in the left table, it will be displayed.NULL。

SELECT customers.name, orders.order_id
FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.customer_id;

FULL JOIN

Returns all the data in the left and right tables, and displays it if there is no match.NULL。

MySQL does not support FULL JOIN, availableLEFT JOINandRIGHT JOINCombination simulation.

SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
UNION
SELECT customers.name, orders.order_id
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;

CROSS JOIN

Returns all possible combinations (Cartesian product) of two tables.

SELECT customers.name, products.product_name
FROM customers
CROSS JOIN products;

SELF JOIN

Used for associations within the same table, such as employee superior relationships.

SELECT A.name AS employee, B.name AS manager
FROM employees A
JOIN employees B ON A.manager_id = B.employee_id;

Include other .sql when executing .sql

Use the SOURCE or \i directive

MySQL or MariaDB

You can include other `.sql` files in your MySQL or MariaDB scripts using the `SOURCE` directive:
-- abc.sql
SOURCE other_file.sql;
SOURCE another_file.sql;

PostgreSQL (psql)

In PostgreSQL, you can include other `.sql` files using the `\i` directive:
-- abc.sql
\i other_file.sql
\i another_file.sql

Execute using batch script

When the SQL client does not support direct file inclusion, you can use batch scripts to execute multiple `.sql` files sequentially.

Linux Shell Example

#!/bin/bash
mysql -u user -p database_name < abc.sql
mysql -u user -p database_name < other_file.sql

Advanced way: Preprocessing SQL files

If your SQL client does not support file inclusion directly, you can merge the main SQL file with the referenced `.sql` file before executing it.

Merge files using Shell Script

cat abc.sql other_file.sql another_file.sql > combined.sql
mysql -u user -p database_name < combined.sql

Things to note

1. **Execution order**: Ensure that the included files are executed in the correct order to avoid dependency issues with data tables or functions. 2. **Library-specific syntax**: The command syntax of different libraries may be different, please refer to the corresponding documentation. 3. **File Path**: Correctly use absolute or relative paths to reference `.sql` files. 4. **Access Permission**: Ensure that the SQL client has permission to read the included file.

Summarize

Through the above method, SQL scripts can be modularized for easy management and reuse.

Passing parameters when including other .sql files

MySQL and MariaDB

MySQL and MariaDB do not directly support passing parameters in the `SOURCE` directive, but variables can be used with the included .sql file. Here's how:

Pass parameters using variables

1. Set the variables in the main SQL file:
   SET @param1 = 'value1';
   SOURCE other_file.sql;
2. Reference variables in `other_file.sql`:
   SELECT * FROM table WHERE column = @param1;

PostgreSQL (psql)

PostgreSQL supports setting variables through the `\set` command and passing them to other files:

Pass parameters using variables

1. Set the variables in the main SQL file:
   \set param1 'value1'
   \i other_file.sql
2. Use variables in `other_file.sql`:
   SELECT * FROM table WHERE column = :'param1';

Pass parameters using command line tools

Passing parameters to a SQL file when executing via the command line is a common method.

MySQL command line example

1. Use `sed` or other tools to replace parameters at execution time:
   sed "s/{param1}/value1/g" abc.sql | mysql -u user -p database_name
2. Use placeholder `{param1}` in the SQL file, replaced by the command line tool.

PostgreSQL command line example

1. Set parameters directly in the `psql` command:
   psql -d database_name -v param1=value1 -f abc.sql
2. Use `:'param1'` to represent variables in the SQL file.

Generate SQL using code

Dynamically generating SQL through a programming language such as Python or Bash is another solution: 1. Dynamically construct a SQL file containing parameters in the program. 2. Execute the generated SQL file.

Things to note

1. **Security**: Avoid directly embedding user-entered parameters into SQL and consider the risk of SQL injection. 2. **Environment variables**: Some tools support using environment variables to pass as parameters.

Summarize

Although parameter passing of SQL files is not always supported directly, it can be achieved through variables, command-line tools, or programming languages, with flexibility depending on the characteristics of the tool and database.

stored procedure

Introduction to stored procedures (pre-saved routines)

Stored Procedure (stored routine or stored procedure) is a set of SQL statements that are pre-compiled and stored in the database and can be executed through calls to improve efficiency and reduce code duplication.

Create a stored procedure

DELIMITER //
CREATE PROCEDURE GetAllProducts()
BEGIN
  SELECT * FROM products;
END //
DELIMITER ;

call stored procedure

CALL GetAllProducts();

stored procedure with parameters

input parameters

Search for products in a specific category:

DELIMITER //
CREATE PROCEDURE GetProductsByCategory(IN category_name VARCHAR(50))
BEGIN
  SELECT * FROM products WHERE category = category_name;
END //
DELIMITER ;

call:

CALL GetProductsByCategory('electronics');

Output parameters

Calculate the total number of items in a category:

DELIMITER //
CREATE PROCEDURE GetProductCountByCategory(IN category_name VARCHAR(50), OUT total_count INT)
BEGIN
  SELECT COUNT(*) INTO total_count FROM products WHERE category = category_name;
END //
DELIMITER ;

call:

CALL GetProductCountByCategory('electronics', @count);
SELECT @count;

Save variables for use in programs

DELIMITER //
CREATE PROCEDURE CalculateTotalRevenue()
BEGIN
  DECLARE total DECIMAL(10,2);
  SELECT SUM(price) INTO total FROM sales;
  SELECT total AS total_revenue;
END //
DELIMITER ;

call:

CALL CalculateTotalRevenue();

Use conditional control in stored procedures

IF condition

DELIMITER //
CREATE PROCEDURE CheckStock(IN product_id INT, OUT stock_status VARCHAR(20))
BEGIN
  DECLARE stock INT;
  SELECT quantity INTO stock FROM inventory WHERE id = product_id;

  IF stock > 10 THEN
    SET stock_status = 'In Stock';
  ELSEIF stock > 0 THEN
    SET stock_status = 'Low Stock';
  ELSE
    SET stock_status = 'Out of Stock';
  END IF;
END //
DELIMITER ;

call:

CALL CheckStock(1, @status);
SELECT @status;

Using LOOP within a saved program

DELIMITER //
CREATE PROCEDURE CountDown(IN start_num INT)
BEGIN
  DECLARE i INT;
  SET i = start_num;

  loop_label: LOOP
    IF i <= 0 THEN
      LEAVE loop_label;
    END IF;
    SELECT i;
    SET i = i - 1;
  END LOOP;
END //
DELIMITER ;

call:

CALL CountDown(5);

Delete stored program

DROP PROCEDURE IF EXISTS GetAllProducts;

in conclusion

Parameter default values ​​for Stored Procedure

MySQL / MariaDB does not support directly setting the default value of parameters

In MySQL and MariaDB, stored procedure parameters cannot be directly set to default values ​​(unlike SQL Server or PostgreSQL). However, it is possible to useIFConditional statements to simulate preset values.

Method 1: Use IF conditions to set default values

Suppose we want to queryuserstable, when parametersuser_idWhen not provided, the default query ID is 1:

DELIMITER //
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    IF user_id IS NULL THEN
        SET user_id = 1; --Default value
    END IF;

    SELECT * FROM users WHERE id = user_id;
END //
DELIMITER;

Execution method

CALL GetUserById(NULL); -- will query id = 1
CALL GetUserById(5); -- Query id = 5

Method 2: Use COALESCE() to set default values

COALESCE()Will return the specified default value when the parameter is NULL:

DELIMITER //
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = COALESCE(user_id, 1);
END //
DELIMITER ;

Execution method

CALL GetUserById(NULL); -- Default is 1
CALL GetUserById(10); -- Query id = 10

Method 3: Using optional parameters (workaround)

If you want the parameters to be optional, you can create multiple Stored Procedures. For example:

DELIMITER //
CREATE PROCEDURE GetAllUsers()
BEGIN
    SELECT * FROM users;
END //

CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

Execution method

CALL GetAllUsers(); -- no parameters, query all
CALL GetUserById(3); -- Query id = 3

in conclusion

Return value of Stored Procedure

Method 1: Use the OUT parameter to return a value

Stored Procedure of MySQL/MariaDB is not supportedRETURNReturn query results, but can be usedOUTParameters pass back values.

DELIMITER //
CREATE PROCEDURE GetUserCount(OUT user_count INT)
BEGIN
    SELECT COUNT(*) INTO user_count FROM users;
END //
DELIMITER ;

Call Stored Procedure and get the return value

CALL GetUserCount(@total);
SELECT @total; -- Display the number of users

Method 2: Use SELECT to return the result set

If you want to return a query result, directlySELECTThat’s it:

DELIMITER //
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

Call Stored Procedure

CALL GetUserById(5); -- Query the user with ID 5

Method 3: Use RETURN to return a single value

Although MySQL supportsRETURN, but can only return a single value, usually used to control the process:

DELIMITER //
CREATE PROCEDURE GetMaxSalary()
BEGIN
    DECLARE max_salary DECIMAL(10,2);
    SELECT MAX(salary) INTO max_salary FROM employees;
    RETURN max_salary; -- But this will not return the value directly in MySQL
END //
DELIMITER;

MySQL cannot directlyCALLto get the RETURN value, so it is recommended to use the OUT parameter:

DELIMITER //
CREATE PROCEDURE GetMaxSalary(OUT max_salary DECIMAL(10,2))
BEGIN
    SELECT MAX(salary) INTO max_salary FROM employees;
END //
DELIMITER ;

CALL GetMaxSalary(@max);
SELECT @max; -- Display the maximum salary

Method 4: Return multiple values

Use multipleOUTThe parameters return different calculation results:

DELIMITER //
CREATE PROCEDURE GetUserStats(OUT total_users INT, OUT avg_age DECIMAL(5,2))
BEGIN
    SELECT COUNT(*) INTO total_users FROM users;
    SELECT AVG(age) INTO avg_age FROM users;
END //
DELIMITER ;

Call Stored Procedure and get multiple return values

CALL GetUserStats(@total, @avg);
SELECT @total, @avg; -- Display the total number and average age of users

in conclusion

Using return values ​​outside of CALL stored procedures

Method 1: Use OUT parameters and store them in variables

MySQL Stored Procedure can be accessed throughOUTParameters return values, which can then be used outside of CALLSELECTGet this value.

Create Stored Procedure

DELIMITER //
CREATE PROCEDURE GetTotalUsers(OUT total_users INT)
BEGIN
    SELECT COUNT(*) INTO total_users FROM users;
END //
DELIMITER ;

Call the Stored Procedure and use the return value

CALL GetTotalUsers(@total);
SELECT @total AS UserCount; -- Use return value outside of CALL

Method 2: Use variables to store returned query results

If Stored Procedure usesSELECTReturning results cannot be directly stored in variables, but they can be usedINSERT INTO ... SELECT。

Create Stored Procedure

DELIMITER //
CREATE PROCEDURE GetMaxSalary()
BEGIN
    SELECT MAX(salary) AS max_salary FROM employees;
END //
DELIMITER ;

Using variables to access query results

CREATE TEMPORARY TABLE temp_result (max_salary DECIMAL(10,2));

INSERT INTO temp_result EXECUTE GetMaxSalary();

SELECT max_salary FROM temp_result; -- used outside CALL

Method 3: Use Prepared Statement to dynamically access results

If the results generated by Stored Procedure need to be accessed in variables, you can usePREPAREandEXECUTE。

Create Stored Procedure

DELIMITER //
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT name FROM users WHERE id = user_id;
END //
DELIMITER ;

Call and store variables

SET @sql = 'CALL GetUserById(5)';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

in conclusion

MySQL function callback

Problem description

In MySQL / MariaDB, Stored Function (function) cannot be returnedSELECTResult set, otherwise an error will occur:

ERROR 1415 (0A000): Not allowed to return a result set from a function

Solution

Method 1: Use Stored Procedure instead

Functions cannot return result sets, but Stored Procedures can.

Wrong function:

DELIMITER //
CREATE FUNCTION GetUsers()
RETURNS TABLE
BEGIN
    RETURN (SELECT * FROM users); -- This is not allowed
END //
DELIMITER;

Correct Stored Procedure:

DELIMITER //
CREATE PROCEDURE GetUsers()
BEGIN
    SELECT * FROM users;
END //
DELIMITER ;

Call Stored Procedure:

CALL GetUsers();

Method 2: Use a function to return a single value

If you only need to return a single value (such as a count or maximum value), you can useRETURN。

DELIMITER //
CREATE FUNCTION GetUserCount()
RETURNS INT DETERMINISTIC
BEGIN
    DECLARE total INT;
    SELECT COUNT(*) INTO total FROM users;
    RETURN total;
END //
DELIMITER ;

Use function:

SELECT GetUserCount();

Method 3: Use Temporary Table

If you really need to return multiple rows of results within the function, you can have the function insert data into the Temporary Table and then query it externally.

DELIMITER //
CREATE FUNCTION PopulateTempUsers()
RETURNS INT DETERMINISTIC
BEGIN
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_users AS (SELECT * FROM users);
    RETURN 1;
END //
DELIMITER ;

Use Temporary Table to read data:

SELECT PopulateTempUsers();
SELECT * FROM temp_users;

in conclusion

HeidiSQL

Introduction

HeidiSQL is a free open source SQL client that supports MySQL, MariaDB, PostgreSQL and MS SQL Server. It provides a GUI to manage databases, execute SQL queries, import/export data, etc.

Main functions

Download and install

1. Go to the official website to download HeidiSQL:https://www.heidisql.com/download.php2. Execute the installer and follow the instructions to complete the installation
3. Open HeidiSQL and set up a new connection

Connect to MySQL/MariaDB

1. Start HeidiSQL
2. Click "Add" to create a new connection
3. Settings:
   - Hostname/IP: 127.0.0.1 or remote server IP
   - Username: root or other user
   - Password: the corresponding password
   - Port: 3306 (MySQL/MariaDB)
4. Click "Open" to connect to the database

Execute SQL query

Enter the SQL statement in the HeidiSQL query window:

SELECT * FROM users WHERE status = 'active';

Click the "Execute" button to view the results.

Import/export data

Export SQL

1. Right-click the database → select "Export SQL"
2. Select the data table to be exported
3. Set the export format (.sql, .csv, .json)
4. Click "Export"

Import SQL

1. Open HeidiSQL and select the target database
2. Click "Tools" → "Execute SQL File"
3. Select the .sql file and execute

Manage user permissions

1. Enter "Tools" → "Manage User Permissions"
2. Select the users to manage
3. Set database permissions (SELECT, INSERT, UPDATE, DELETE, etc.)
4. Click "Save"

in conclusion

HeidiSQL adds new stored procedures

Step 1: Connect to the database

1. Start HeidiSQL.
2. Connect to the MySQL or MariaDB server.
3. Select the target database in the Database list on the left.

Step 2: Create a new stored procedure

1. Right-click the database name on the left and select "Create New" → "Save Program".
2. HeidiSQL will open a new SQL editing window and provide a default stored procedure template.

Step 3: Write the stored procedure

The following is a simple example that returns all data in the users table:

DELIMITER //
CREATE PROCEDURE GetAllUsers()
BEGIN
    SELECT * FROM users;
END //
DELIMITER ;

Step 4: Execute the stored procedure

1. Click the "Go" button (green lightning bolt).
2. If the execution is successful, the program can be found in the "Save Program" column on the left.

Step 5: Test the stored procedure

CALL GetAllUsers();

Advanced: Stored procedures with parameters

Pass in parameters to filter data, such as querying based on user ID:

DELIMITER //
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

Call a stored procedure with parameters:

CALL GetUserById(1);

Delete stored program

DROP PROCEDURE IF EXISTS GetAllUsers;

in conclusion

Database application development solution

In today's software development environment, database applications have evolved from simple data storage to comprehensive solutions integrating AI, edge computing and automated operation and maintenance. The following are the current mainstream development models:

AI integration and RAG architecture solution

With the popularity of large language models (LLM), retrieval-augmented generation (RAG) has become a development standard. This type of solution focuses on vectorization and retrieval of unstructured data.

Serverless and cloud-native solutions

In order to simplify operation and maintenance and reduce start-up costs, developers tend to choose database services with automatic scaling capabilities, which is especially popular in web applications.

Full-end type safety development solution

In the TypeScript ecosystem, synchronizing database definitions with front-end types is key to ensuring system stability.

Distributed and edge database solutions

In order to cope with global access and reduce latency, it has become a trend to deploy data close to users.

Development plan comparison table

Plan Category Represent technology Core advantages
AI first pgvector, Milvus Support semantic search and knowledge base construction
Serverless type Supabase, Vercel Postgres Zero-burden operation and maintenance, billing based on volume
Efficient development type Drizzle ORM, Prisma Extremely high type safety and development speed
Decentralized architecture CockroachDB, TiDB Cross-region deployment, automatic fault tolerance

Database application front-end interface

In modern database application development, the front-end is no longer just about presenting data, but also emphasizes type synchronization with the back-end database, state management, and server-side rendering performance. The following are the current mainstream choices:

React ecosystem and Meta-frameworks

This is currently the solution with the highest market share and richest community support, and is particularly suitable for complex database management systems.

Vue ecosystem solution

Known for its simple learning curve and highly integrated tool chain, it is deeply loved by the backend management systems of medium and large enterprises.

Type Safety and UI Component Tools

In order to improve development efficiency and reduce data transmission errors, modern front-ends highly rely on the following tools:

Low-code and in-house tool development

For database management interfaces used within enterprises, developers often choose faster integration solutions.

Front-end solution selection comparison table

Scheme name Applicable scenarios Core advantages
Next.js + Tailwind SaaS products, modern web applications SEO friendly, ultimate performance optimization
Vue + Element Plus Enterprise internal backend and management system Rich components and extremely fast development
TanStack Query Applications with high-frequency data updates Powerful cache management and automatic synchronization
Retool Emergency internal maintenance tools Drag and drop, almost no need to write CSS

Database Web UI

Modern database web management tools allow teams to access data directly through a browser without the need to install desktop software. The following are divided into four categories according to functional positioning:

1. Universal management tool

This type of tool supports multiple database connections (such as MySQL, PostgreSQL, SQL Server) and is suitable for developers who manage diverse environments.

2. Database-specific management interface

A UI deeply optimized for a specific database by the original manufacturer or the community.

3. Low-code and spreadsheet interface

Convert the database into an intuitive interface similar to Excel, suitable for non-technical personnel or to quickly build an internal backend.

4. Cloud platform native interface

If the data is hosted on a cloud service provider, the native web console usually has the highest level of integration.

CloudBeaver

CloudBeaver is an open source web-based database management solution developed by the DBeaver team. It uses Java back-end and React front-end architecture to allow users to securely manage various databases through the browser, which is very suitable for scenarios that require remote access or team collaboration.

Core functional features

Differences between Community Edition and Enterprise Edition

CloudBeaver provides open source Community Edition (Community) and commercial Enterprise Edition (Enterprise). The main difference lies in advanced feature support:

Features Community Enterprise (Enterprise)
SQL database support Supports most mainstream SQL Contains NoSQL (MongoDB, Redis)
Cloud service integration basic wiring Native support for AWS, GCP, Azure resource browsing
Authentication Account password Supports SSO, SAML, LDAP, Kerberos
Advanced tools Basic query AI assistant (SQL generation), visual query builder

Applicable scenarios

CloudBeaver installation method:

The most recommended installation method for CloudBeaver is to use Docker, as it already packages all necessary Java environments and drivers. There are three main deployment paths:

1. Use Docker to quickly start

This is the simplest method, just execute one line of instructions to start the service. By default, it will listen on port 8978.

docker run --name cloudbeaver -d -p 8978:8978 dbeaver/cloudbeaver:latest

After execution, open browser inputhttp://localhost:8978You can enter the setting wizard.

2. Use Docker Compose (recommended for production environment)

Data persistence can be easily managed through Compose files to ensure that settings will not be lost after the container is restarted.


version: '3'
services:
  cloudbeaver:
    image: dbeaver/cloudbeaver:latest
    container_name: cloudbeaver
    restart: unless-stopped
    ports:
      - "8978:8978"
    volumes:
      - ./cloudbeaver-data:/opt/cloudbeaver/workspace

Save the above content asdocker-compose.yml, and then executedocker-compose up -d。

3. Standalone executable file installation (Standalone)

If Docker cannot be used in the environment, you can download the compiled binary file and install it manually:

Initial settings after installation

When entering the web interface for the first time, the system will guide you through the following configuration:

email: [email protected]
T:0000
資訊與搜尋 | 回dev首頁
email: Yan Sa [email protected] Line: 阿央
電話: 02-27566655 ,03-5924828
阿央
泱泱科技
捷昱科技泱泱企業