1. Constraints
Constraints are rules that act on the fields of a table to limit the data stored in the table.
2. Purpose:
Ensure the accuracy, validity and integrity of data in the database
3. Classification
| Constraints | Description | Keywords |
|---|---|---|
| Non-empty constraints | Restrictions This field cannot be empty | NOT NULL |
| Unique constraint | Ensure that the data in this field is unique and non-duplicate | UNIQUE |
| Primary key constraint | The primary key is the unique identifier of a row, and it is required that the primary key cannot be empty | PRIMIARY KEY |
| Default constraint | When saving data, if the value of this field is not specified, the default is default | DEFAULT |
| Check constraints (8.0.16) | Ensure that the field value meets a certain condition | CHECK |
| Foreign key constraints | are used to establish a connection between the data of two tables to ensure the consistency and integrity of the data | FOREIGN KEY |
For example:
Create a table,
id primary key, and automatically grows int
name is not empty and is a unique string
age is greater than 0 and less than or equal to 120 int
If status is not specified, it defaults to 1 varchar
gender unconstrained condition string
create table user(
id int primary key auto_increment comment 'primary key';
name varchar(255) not null unique comment 'name';
age int check (age>0 & amp; & amp; age <120) comment 'age';
status varchar(255) default '1' comment 'status';
gender varchar（255）comment 'gender'
) ENGINE = INNODB DEFAULT CHARSET=utf8;
3. Foreign key constraints:
Foreign key constraints are used to establish a connection between two tables to ensure data consistency and completeness.
Add a foreign key:
Grammar format:
alter table table name add constraint name of foreign key foreing key (field name of foreign key) references main table (main table column name)
Delete a foreign key:
Grammar format:
alter table table name drop foreing key foreign key name
2. Multi-table query: There can be one-to-many, one-to-one, and many-to-many relationships between tables.
Multi-table query: refers to querying data from multiple tables
Cartesian product: refers to combining all rows in two tables to generate a new table.
For the main categories of multi-table queries:
1. Connection query:
Inner join: A query equivalent to the intersection between two tables
Inner joins are mainly divided into two types: implicit inner joins and explicit inner joins
1. Implicit inner join:
select field list from Table 1, Table 2 where filter conditions;
2. Display inner connections:
select field name from table 1 [inner]join table 2 on connection conditions;
Outer join:
Left outer join: Query all the data in the left table, as well as the common parts of the two tables, and maintain the integrity of the left table
Right outer join: Query all the data in the right table, as well as the common parts of the two tables, and maintain the integrity of the right table
The syntax format of left outer join: select field column name from table 1 left [outer] join table 2 on association condition; The syntax of right outer join is: select field column name from table 1 right [outer] join table 2 on association condition;
Self-join: The current table and self-generated connection query, aliases must be used when self-joining.
Format of self-connection: select field column name from table 1 alias 1 join table 2 alias 2 on association condition;
2. Subquery: Nested select query in SQL is called a nested query, also known as a subquery.
The main format of word query:
select list field from table where column = (select list field from table 1);
a. Depending on the query results, subqueries can be divided into:
1. Scalar query: the result of the subquery is a single value
2. Column subquery: The result of the subquery is a column
3. Row subquery: The result of the subquery is a row
4. Table subquery: The result of the subquery is multiple rows and multiple columns.
b. You can also divide the subquery into: after where, after from, and after select according to the position of the subquery.
Union query: union, union all Union: refers to combining the results of queries from multiple tables to form a new query result. Grammar format: select field column name from table 1 union[all] select field column name from table 2; For union queries: the difference between union and union all: Union will deduplicate the query results, but union all will not deduplicate the query results.
3. Transaction:
A transaction refers to a set of operations. It is an indivisible unit of work. A transaction will submit all operations as a whole to the system to submit or cancel the operation request. In summary, it is a request. The final result is either success or failure.
For example: when we are transferring money, the general steps can be seen as the following steps
1. Check the balance of this account
2. The amount of the transferred account is reduced
3. The amount accepted for account transfer increases
These steps will be controlled in a transaction. The transaction will start at this time. Either all steps are successful and the transaction can be submitted. If one of the steps fails, an exception will be thrown at this time, the transaction will be rolled back, and the modification will be made. The data is restored back, thus ensuring the integrity of the data.
By default, MySQL transactions are automatically committed. That is to say, when a DML statement is executed, MySQL will automatically and implicitly commit the transaction.
View the submission method of the current transaction: select @@autocommit; 1 means automatically submitting the transaction, 0 means manually submitting the transaction
Set the transaction submission method to manual submission: set @@autocommit = 0; when adjusted to manual submission, you need to execute commit to manually submit the transaction after the transaction is completed.
Four major characteristics of transactions:
1. Atomicity: Transactions are the smallest indivisible unit, either all succeed or all fail. A transaction is considered as a whole and cannot be divided.
2. Consistency: When the transaction is completed, all data must be kept in a consistent state. This means that the total amount of data before the transaction is started and after the transaction is submitted is consistent. For example, in the previous transfer task, the total amount before the transaction is started and after the transaction is submitted is the same.
3. Isolation: The isolation mechanism provided by the database system ensures that transactions run in an independent environment without being affected by external concurrent operations. When there are multiple transactions operating on the same data, the two transactions will not affect each other.
4. Durability: Once a transaction is committed or rolled back, its changes to the data in the database are permanent.
Concurrent transaction issues:
1. Dirty read: One transaction reads uncommitted data from another transaction
2. Non-repeatable reading: A transaction reads the same piece of data successively, but the data read twice is different.
3. Phantom reading: When a transaction queries data according to conditions, there is no corresponding data, but when inserting data, it is found that the data exists.
Transaction isolation level:
1. Read uncommitted read uncommitted
2. Read committed read committed
3. Repeatable read reapeatable read
4. String formation serislizabre
Query the database isolation level:
Syntax: SELECT @@TRANSACTION_ISOLATION;
Modify the database isolation level:
Syntax: SET GLOBAL TRANSACTION ISOLATION LEVEL level string;
4. Paradigm: refers to the specifications that need to be followed when building a table
1. First normal form:
Each column of the database table is an indivisible atomic data item, not a collection or array.
2. Second normal form:
Each row of data in the database must depend on a primary key. Each row of data must have a primary key. The primary key is the unique identifier of a row of data.
3. Third normal form:
The non-primary key columns in the table must be completely dependent on the primary key. Some attributes cannot depend on other attributes. When transitive dependencies occur, a special table must be created to manage the columns that are not dependent on the primary key.