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

Data query: Retrieve the required data from the data table.
Data insertion: Add new data to the data table.
Data update: Modify existing information in the data table.
Data deletion: Remove unnecessary data from the data table.
Data table management: Create, modify and delete table structures.

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

INT: Integer type.
VARCHAR(n):Variable length character string, up toncharacters.
DATE:Date type.
FLOAT: Floating point type.
BOOLEAN: Boolean value (true or false).

advantage

Easy to learn and use, with intuitive syntax.
Provide efficient data processing capabilities.
Support complex data queries and operations.
Widely used in various industries and scenarios.

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

High performance and stability
Supports multi-user and multi-threading
Runs cross-platform
Rich community and business support

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

Dynamic website and online business platform
Large data storage and management
Applications that require high performance and reliability

SQLite

Lightweight database

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

characteristic

Store all data in a single file
Supports ACID transaction processing
Cross-platform support
Open source and free

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

Embedded Systems and Mobile Applications
Data storage for small websites or tools
Rapid development and testing

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

Highly compliant with SQL standards:Supports most SQL:2023 standard features, including complex queries, foreign keys, triggers, views and transaction integrity.
ACID transaction support:Fully supports Atomicity, Consistency, Isolation and Durability to ensure that data can be stored correctly under any circumstances.
Extensibility:Users can customize data types, index types, functional languages (such as PL/pgSQL, Python, Perl), and even develop their own extension packages (such as PostGIS).
Multiversion Concurrency Control (MVCC):Read and write operations do not block each other, greatly improving performance in high-concurrency environments.
Diverse data types:In addition to standard values and strings, JSONB (unstructured data), geometric coordinates, network addresses, arrays and range types are natively supported.

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:

PostGIS：Adding geographic information system (GIS) support to Postgres is currently the most powerful solution for processing spatial data.
TimescaleDB：Extensions optimized for time-series data.
pg_stat_statements：Essential tool for monitoring and analyzing SQL execution performance.
pgvector：Supports vector similarity search, which is a key component for developing AI and LLM applications (RAG).

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

Financial and e-commerce systems:Strict transaction processing and data accuracy are required.
Geographical information system:Work with PostGIS to handle map and spatial operations.
Artificial intelligence applications:Use pgvector to store and retrieve vector embeddings.
Big data analysis:As a data warehouse (Data Warehouse), handle complex report queries.

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

Variable declaration: allows the use of the DECLARE keyword to define local variables for temporary storage of calculation results.
Process control: supports IF...ELSE judgment, WHILE loop and GOTO statement.
Built-in functions: Provides a large number of exclusive functions for strings, dates, mathematical operations and system management.
Error handling: The TRY...CATCH mechanism is introduced to improve exception handling on the database side.
Transaction management: Provides a high degree of control over transactions to ensure the ACID properties of data.

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:

Develop stored procedures: Encapsulate complex logic in the database to improve performance.
Create triggers (Triggers): automatically execute actions when specific events occur in the data table.
Maintain Views: Provide virtual data views to simplify queries.
Scheduled tasks: Regularly perform data batch processing and backup through SQL Server Agent.

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

data sheetnameField values are unique.
nameThe length of the field is moderate to avoid being too long and affecting performance.
Applications operate primarily on names, not numeric IDs.

Advantages

Using names is more intuitive and easier to read and manage.
Simplify query statements, such as searching directly by name.
Avoid the need to create additional numeric ID fields.

potential problems

Failure to maintain unique names may lead to data conflicts.
Excessively long names may reduce index query performance.
After the name is changed, related associations may need to be updated simultaneously, increasing complexity.

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

The reference field in the parent data table must bePRIMARY KEYorUNIQUE。
When inserting or updating data, foreign key constraints must be followed.
When deleting data from the parent table, foreign key constraints may cause errors unless setON DELETEBehavior.

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

CASCADE: Synchronously delete or update related data in the sub-data table.
SET NULL: Set the foreign key field in the subdata table toNULL。
RESTRICT: Prevent deletion or update, default behavior.
NO ACTION:andRESTRICTSimilar, defer constraint checking.

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

MySQL supportCOMMENT, but SQLite does not support it.
Other database systems (such as PostgreSQL) often useCOMMENT ONinstruction.

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.

Rename: Change the field name to a non-conflicting name. For example, changeintervalChange totime_interval,WillrankChange touser_rank。
Check the official reserved word list: Check the official documentation of the target database (such as MySQL, PostgreSQL, SQL Server) during the design phase to ensure that the name you choose is not in the reserved word list.

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.

Define a reserved word set: Create a set containing all reserved words that need to be prevented.
Field list processing: Write a function to check whether the field name is a reserved word. If so, the name is wrapped in backticks of the target database.
WHERE condition processing: When processing the WHERE clause, you must use regular expressions (with word boundaries\b) to match field names exactly and avoid incorrectly replacing string values or long names.

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:

%Y-%m-%d %H:%i:%sIs the standard date and time format.
%fCaptures Microseconds, so you need to pass in the remaining milliseconds as a fraction.

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

Price (or value) cannot be negative or zero, otherwiseLOG()will cause errors.
If the data contains zeros, you can filter them out first:

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:

STDDEV_POP()- Calculate the population standard deviation (Population Standard Deviation)
STDDEV_SAMP()- Calculate sample standard deviation (Sample 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

useSTDDEV()The standard deviation can be calculated directly.
AvailableSTDDEV_POP()Calculate the parent standard deviation,STDDEV_SAMP()Calculate the sample standard deviation.
If built-in functions are not supported, SQL formulas can be used to calculate manually.

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

Row level filtering: WHEREThe clause is evaluated for each row (Row) as the database reads the table. Only when the data in a certain row satisfiesWHEREThe row will be included in the result set only if the following conditions are met.
Application scope:it can be used withSELECT, UPDATE, DELETEUse it with SQL commands.

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

Group information: GROUP BYThe core function is to merge multiple single rows with the same value in the table into a single group (Group).
Generate summary:by withCOUNT()、SUM()、AVG()、MAX()orMIN()Combined with other summary functions, you can calculate a statistical summary for each group rather than a summary of the entire table.

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

GROUP BY Category: directive database will all have the sameCategoryRow grouping of values.
AVG(Price): Calculate the average of all product prices in each grouping (each category).
COUNT(ProductID): Calculate the total quantity of products in each grouping (each category).

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

GROUP BY CustomerID: Group all orders by customer ID.
AVG(TotalAmount): Calculate the average order amount per customer.
HAVING AVG(TotalAmount) > 500: This is the filter condition. It tells SQL to display only those average order amounts (i.e.AVG(TotalAmount)results) for groups (customers) greater than 500**.

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

HAVING COUNT(OrderID) >= 3: Filter out customer groups with less than 3 orders in total.
AND AVG(TotalAmount) < 1000: Also filter out customer groups whose average order amount is greater than or equal to 1,000.

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

SQL cannot be in the sameSELECTReference the alias within the query.
You can use subqueries or CTEs (WITH) to solve.
For simple operations, the calculation can be repeated directly.

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

Stored procedures improve SQL efficiency and encapsulate business logic.
Parameters can be used to pass values, and conditional control and looping are supported.
useDELIMITERAvoid SQL syntax conflicts.

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

MySQL and MariaDB do not support setting parameters directlyDEFAULTvalue.
can pass throughIForCOALESCE()Analog defaults.
If the parameter is optional, it is recommended to create a different Stored Procedure.

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 directly`CALL`to 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

If you return a leaflet with one value, it is recommended to useOUTparameter.
If a query result set is returned, use it directlySELECT。
If multiple values are returned, use multipleOUTparameter.
MySQL does not supportRETURNReturning query results is only applicable to process control.

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

useOUTParameter returns a single value in leaflet, passed@variableaccess.
If query results are returned, availableINSERT INTO ... CALLStore in the staging table.
If you need to dynamically access the results, you can usePREPAREandEXECUTE。

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

MySQL/MariaDB does not allow functions to return result sets, Stored Procedure should be used instead.
If you only need to return a single value, you can use it within the function.RETURN。
If you need to return multiple rows of results, you can use Temporary Table to store and query later.

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

Manage databases, tables, indexes, views, triggers
Execute SQL queries and support multi-tab editing
Visual data table structure and ER diagram
Edit data in batches
Import/export CSV, SQL, XML, JSON and other formats
Manage user permissions

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 is a lightweight but powerful SQL client
Suitable for developers and DBAs to manage MySQL, MariaDB, and PostgreSQL
Provide visual management functions to improve efficiency

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

HeidiSQL provides a GUI for managing stored procedures.
useDELIMITERMake sure the syntax is correct.
Can be usedCALLTest stored procedures.

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.

Use PostgreSQL with the pgvector extension to process traditional relational data and AI vector data in the same database.
Use specialized vector databases such as Pinecone or Weaviate to pursue ultimate similarity search performance.
Integrate frameworks such as LangChain or LlamaIndex to establish an automated pipeline from database to AI model.

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.

Supabase: Provides Backend as a Service (BaaS) based on PostgreSQL, including authentication and instant data push.
PlanetScale: A MySQL solution based on Vitess that supports Schema Migrations and horizontal expansion.
Neon: PostgreSQL designed specifically for Serverless, supporting database branching function to facilitate development and testing.

Full-end type safety development solution

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

Using Prisma or Drizzle ORM, developers can get complete IDE code completion and error checking when writing query syntax through automatically generated type definitions.
Combined with the T3 Stack architecture, tRPC is used to achieve end-to-end type synchronization from the database layer to the front-end interface.

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.

Turso: Based on libSQL (SQLite branch), specially designed for edge computing environments, with extremely fast cold start time.
TiDB Cloud: Distributed SQL database, suitable for mixed load (HTAP) scenarios that require strong consistency and unpredictable data growth.

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.

Next.js: Currently the most popular React framework, it supports server components. It can directly define the logic of interacting with the database in the front-end code, reducing the development of the API layer.
TanStack Query (React Query): The core tool for handling the asynchronous status of the database, with powerful caching mechanism, automatic re-fetching and paging processing functions.
Remix: Emphasizes web standards and data loading optimization, and simplifies the interaction between front-end forms and databases through Loader and Action mechanisms.

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.

Nuxt.js: Vue’s full-end framework provides a development experience similar to Next.js. It has a powerful modular system and can quickly integrate database ORM.
Pinia: A modern state management library used to handle front-end cross-page data synchronization.
Element Plus / Ant Design Vue: A mature UI component library with a large number of built-in table and form components designed for database CRUD operations.

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:

TypeScript: Comes standard to ensure data type consistency from database fields to front-end display.
Tailwind CSS: A mainstream style solution that quickly builds responsive data dashboards using Utility-first concepts.
Shadcn UI: A collection of components based on Radix UI and Tailwind. It is currently very popular in the Next.js community. The program code is completely transparent and easy to customize.

Low-code and in-house tool development

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

Appsmith / Retool: You can connect to PostgreSQL, MySQL or MongoDB by dragging and dropping components to quickly generate data analysis and maintenance background.

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.

CloudBeaver:The open source web version based on DBeaver engine is powerful and supports a wide range of database drivers.
Adminer:A minimalist single PHP file management tool, extremely fast to deploy, and supports multiple SQL databases.
OmniDB:Browser management tool designed for collaboration, with security controls and an interactive SQL console.

2. Database-specific management interface

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

pgAdmin 4 (PostgreSQL):The official first choice, it can be run in server mode for shared access by teams.
phpMyAdmin (MySQL/MariaDB):The oldest and most popular web page management tool.
Redis Insight (Web):Provides key value visualization, memory analysis and real-time monitoring of streaming data.
MongoDB Atlas:Built-in powerful data browser, aggregation pipeline editor and performance analysis tools.

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.

NocoDB:An open source Airtable alternative that can directly connect to existing databases and generate APIs.
Retool / Appsmith:Low-code platform to quickly create customized database UI through drag-and-drop components.
Budibase:An internal tool development platform focused on quickly building CRUD applications.

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.

AWS RDS Query Editor:Execute SQL commands directly from the AWS Management Console.
Google BigQuery Console:A powerful web IDE that supports processing huge amounts of data and has built-in AI assistance features.
Azure SQL Editor:A lightweight query tool built into the Azure portal.

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

Cross-platform web access:There is no need to install any desktop software on the personal computer, and it supports direct operation by mainstream browsers.
Extensive database support:Supports MySQL, PostgreSQL, MariaDB, SQLite, Oracle, SQL Server, ClickHouse and other SQL databases through JDBC driver.
Powerful SQL editor:Provides syntax highlighting, auto-complete, formatting and query history functions.
Visual data editor:Supports direct editing of data in table form, filtering fields, sorting, and exporting to multiple formats (such as CSV, JSON, XLSX).
Security and permission control:Supports multi-user mode, administrators can centrally configure database connections and assign them to specific teams or members.
Docker friendly:Official Docker images are provided and can be quickly deployed on private clouds or internal servers.

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

Teamwork:Once administrators set up a database connection, developers can simply log in to a browser and start working without having to share confidential database keys.
Restricted environment:It is particularly useful in environments where desktop software cannot be installed or where it is necessary to penetrate the bastion host for database maintenance.
Lightweight maintenance:Suitable for monitoring data status, executing temporary patch scripts, or performing simple data export work.

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:

Environmental requirements:Requires Java JRE 17 or newer to be installed first.
Download steps:
1. Go to the CloudBeaver GitHub Release page to download the asset file corresponding to the operating system (such ascloudbeaver-ce-latest-linux-x86_64.tar.gz）。
2. Unzip the archive:tar -xvf cloudbeaver-ce-latest-linux-x86_64.tar.gz。
3. Execute startup script:./run-server.sh(Linux/macOS) orrun-server.bat (Windows)。

Initial settings after installation

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

Server Configuration：Set the service name and port.
Administration：Create an administrator account and password.
Database Drivers：Confirm which database driver needs to be enabled (the system handles most common drivers automatically).

email: [email protected]

T:0000

資訊與搜尋 | 回dev首頁
email: Yan Sa [email protected] Line: 阿央

電話: 02-27566655 ,03-5924828

阿央
泱泱科技
捷昱科技泱泱企業

中文

Database development

technology

computer use

software development

web development

SQL

definition

Main functions

basic grammar

Common data types

advantage

MySQL

relational database

characteristic

Usage examples

Applicable scenarios

SQLite

Lightweight database

characteristic

Usage examples

Applicable scenarios

PostgreSQL

Core features

Technical architecture

Common extension kits

Comparison of advantages and disadvantages

Applicable scenarios

T-SQL

language features

Key differences between T-SQL and standard SQL

Basic grammar example

Application scenarios

T-SQL program development

Variable declaration and assignment

Process control IF...ELSE

Error handling TRY...CATCH

Use Name instead of ID field

Applicable conditions

Advantages

potential problems

Applicable examples

Relational database design inheritance objects

Database structure design

animal form

cat table

SQL create table command

Example of inserting data

HTML table display example

animal information

Cat-specific information

illustrate

Query information about all animals

Query information about all cats

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

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

illustrate

FOREIGN KEY

use

grammar

example

Things to note

Advanced usage

Behavior options

Add comments to CREATE TABLE

Use COMMENT to set field comments

Use COMMENT to set table comments

View field notes

Modify field notes

Scope of application

PostgreSQL settings notes

SQL column names use reserved words

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

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

3. Program code automation (dynamically generate SQL)

4. Advantages of ORM framework

SQL DATETIME comparison

Use TIMESTAMPDIFF

Use ABS(TIMESTAMPDIFF)

Use DATEDIFF (only for day comparisons)