Detailed explanation of mysql constraints and advanced sql

constraint

Constraints in MySQL are used to define rules for data in tables to ensure the accuracy and reliability of the data. Here are some commonly used constraint types in MySQL and their overview:

• PRIMARY KEY: A field or field combination that uniquely identifies each record in the table. There can only be one primary key in a table.
• The value of the primary key field cannot be NULL.
• FOREIGN KEY: Used to establish a relationship between two tables to ensure the integrity of the referenced data. The value of the foreign key field must exist in the table it references, or be NULL (depending on the settings of the foreign key constraint).
• UNIQUE (unique constraint): Ensure that all values ​​in the column are unique, that is, no duplicate values ​​are allowed
• NOT NULL (non-null constraint): Ensure that the value in the column cannot be NULL.
• CHECK: A condition used to restrict the value in a column that must meet. (Supported in MySQL 8.0.16 and later)
• DEFAULT (default): When no value is provided for the column, the default value is used.
• AUTO_INCREMENT: Used as a column of integer type, automatically generates a unique number for the new record.

Advanced sql

MySQL provides a variety of advanced features to handle complex data operations and business logic. Here is a brief introduction to several key concepts you mentioned:

1. Subquery: A subquery is a SQL query nested in another query. It can return a single value, one or more rows of results. Subqueries are usually used in SELECT, INSERT, UPDATE, and DELETE statements and can be used in conditional expressions or as value providers.

SELECT name, MAX(salary) AS max_salary
FROM employees
WHERE salary = (
  SELECT MAX(salary)
  FROM salaries
);

First, determine the highest salary in the salaries table, and then find the employee information in the employees table whose salary is equal to this highest value in the outer query.

2. Connection (JOIN): Joining rows used to combine two or more tables based on related columns. MySQL supports multiple types of connections, including inner connection, left connection, right connection, and full connection (FULL JOIN).

SELECT , 
FROM books
INNER JOIN authors ON books.author_id = ;

The result of this query will be a table with two columns and , each row corresponding to the name of a book and its author.

3. Transaction:Transactions are used to process data with high operation volume and high complexity. A transaction is a collection of a set of SQL statements. They are either executed successfully or not, putting a series of operations in one place, and then deciding whether to take effect. This property is called Atomicity. Transactions must also meet the three characteristics of consistency, isolation, and persistence, which are usually collectively referred to as ACID characteristics.

Basic operations of transactions MySQL manages transactions through the following three main SQL statements:

• START TRANSACTION or BEGIN: Marks the beginning of a transaction.
• COMMIT: Submit a transaction, saving all changes in the transaction to the database permanently.
• ROLLBACK: Rollback the transaction, revoke all changes since the transaction begins, and bring the database back to its state before the transaction begins.

Auto-commit mode By default, MySQL automatically commits transactions after executing each SQL statement. However, you can control this behavior by setting the autocommit variable:

• SET autocommit=OFF or SET autocommit=0: Turn off automatic commit, so that it is necessary to explicitly use COMMIT or ROLLBACK to end the transaction.
• SET autocommit=ON or SET autocommit=1: Turn on automatic submission, and each SQL statement will be automatically submitted after execution.

Example of transaction usage:

-- Start a transaction
START TRANSACTION;
-- For studentsIDfor1Students increase10point
UPDATE student_scores SET score = score + 10 WHERE student_id = 1;
-- Check whether the update is successful
IF (SELECT ROW_COUNT()) > 0 THEN
    -- If the update is successful，Submit transactions
    COMMIT;
    -- Query updated results
    SELECT * FROM student_scores WHERE student_id = 1;
ELSE
    -- If the update fails，Roll back transactions
    ROLLBACK;
    -- 查询当前的point数，Confirm that there is no change
    SELECT * FROM student_scores WHERE student_id = 1;
END IF;

Correct use of transactions can ensure the atomicity and consistency of database operations and avoid the problem of data inconsistency.

4. Stored procedures:A MySQL stored procedure is a collection of SQL statements to complete a specific function. It is compiled and stored in a database and can be executed multiple times. Using stored procedures can improve performance and enhance maintainability.

1. Create stored procedures

• DELIMITER: Change the command ending character of MySQL, because it will be used in the stored procedure as the end of the SQL statement. If the ending character is not changed, MySQL will incorrectly end the definition of the stored procedure at the first;.
• CREATE PROCEDURE: Used to create new stored procedures.
• Parameters: Stored procedures can have parameters, and there are three types of parameters: IN (input parameter), OUT (output parameter) and INOUT (can be input or output).

2. Stored process body

• BEGIN ... END: The main part of the stored procedure, containing the SQL statement to be executed.
• DECLARE: Used to declare local variables.
• SET: Used to assign values ​​to variables.

Example:

DELIMITER //
CREATE PROCEDURE SumTwoNumbers(IN num1 INT, IN num2 INT, OUT result INT)
BEGIN
    SET result = num1 + num2;
END //
DELIMITER ;

• DELIMITER //Changes the default delimiter of MySQL so that we can use semicolons inside stored procedures.
• CREATE PROCEDURE SumTwoNumbers creates a stored procedure called SumTwoNumbers.
• IN num1 INT, IN num2 INT   defines two input parameters num1 and num2  , both of which are integer types.
• OUT result INT   defines an output parameter  result  , which is also an integer type, used to store the calculation results.
• SET result = num1 + num2;   is the core of the stored procedure. It adds two input parameters and assigns the result to the output parameter  result  .
• END   Marks the end of the stored procedure.
• DELIMITER ;   Reset the delimiter back to the default semicolon.

If you want to call the storage content:

CALL SumTwoNumbers(10, 20, @sumResult);
SELECT @sumResult;

This is all about this article about mysql constraints and advanced sql. For more related mysql constraints and advanced sql content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!