MySQL-2 transaction table association table strengthening: 6 constraints multi table associated query join

MySQL

transaction

Overview: what is a transaction

Database transaction refers to a series of operations performed as a single logical unit of work, either completely or not.
To put it simply: a transaction is to bind a pile of SQL statements (usually add, delete and modify operations) together for execution. Either all of them are executed successfully or all of them fail. That is, only when all of them are executed successfully will they be successful, otherwise they will return to the state before the execution of this pile of SQL statements.
For example:

Let's take bank transfer as an example, A Transfer $100 to B Your account, which requires at least two SQL sentence:

 to A Deduct 100 yuan from your account;

update Account table set money=money-100 where name='A';

 to B Add 100 yuan to your account.

update Account table set money=money+100 where name='B';

If in the first article SQL After the statement is executed successfully, the second item is executed SQL The program was interrupted before the statement
(It may be an exception thrown or something else),
that B I didn't add 100 yuan to my account, and A But minus 100 yuan, which is definitely not allowed in real life.

If a transaction is added in the transfer process, all transactions executed in the whole transfer process will be deleted SQL Statement in a transaction,
All operations in a transaction either succeed or fail. There can be no half success.

That is to say A Deduct 100 yuan from your account. If it succeeds, give it to me B The operation of adding 100 yuan to your account must also be successful;
Otherwise, here you are A Subtract 100 yuan and give B Plus 100 yuan is a failure.

Transaction 4 attributes ACID

Generally speaking, transactions must meet four conditions (ACID): Atomicity (or indivisibility), Consistency, Isolation (also known as independence) and persistence.

Atomicity: all operations in a transaction are either completed or not completed, and will not end in an intermediate link. If an error occurs during the execution of a transaction, it will be rolled back to the state before the start of the transaction, as if the transaction had never been executed.
Consistency: the integrity of the database is not destroyed before and after the transaction. This means that the written data must fully comply with all preset rules, including the accuracy and serialization of the data, and the subsequent database can spontaneously complete the predetermined work.
Isolation: the ability of a database to allow multiple concurrent transactions to read, write and modify their data at the same time. Isolation can prevent data inconsistency caused by cross execution when multiple transactions are executed concurrently.
Persistence: after the transaction is completed, the modification of data is permanent and will not be lost even if the system fails.

Isolation level

Read uncommitted has the worst security, concurrent data problems may occur, and the performance is the best
read committed is Oracle's default isolation level
repeatable read is the default isolation level of MySQL, with good security and average performance
Serializable table level lock, locking both read and write, low efficiency, high security and no concurrency

Query the isolation level of mysql

By default, each SQL statement executed by MySQL is a separate transaction. If you need to include multiple SQL statements in a transaction, you need to manually start and end the transaction.

Start transaction: start transaction;
End transaction: commit or rollback.

Before executing SQL statements, execute strat transaction first, which starts a transaction (the starting point of the transaction). Then you can execute multiple SQL statements. Finally, to end the transaction, commit means commit, that is, the impact of multiple SQL statements in the transaction will be persisted to the database. Or rollback, which means rollback, that is, rollback to the starting point of the transaction. All previous operations have been undone!

SELECT @@tx_isolation;

Repeatable Read((re readable)

MySQL The default transaction isolation level, which ensures that multiple instances of the same transaction will see the same data row when reading data concurrently.

transaction processing

In MySQL, only databases or tables that use the Innodb database engine support transactions
Transaction processing can be used to maintain data integrity and ensure that batch SQL statements are either executed or not executed
Transactions are used to manage insert, update and delete statements, because these operations will "destroy" data, and query select statements will not
By default, the transaction of MySQL database is enabled, and it is automatically committed after executing SQL.
MySQL transactions can also be submitted manually. There are two steps: first start, write SQL, and then submit manually.

Submit commit

#When multiple statements are executed in batch, the transaction is committed
#With transactions, multi-step operations form atomic operations, and high concurrency will not cause data disorder
#mysql transactions are turned on by default -- when multiple statements operate together, they either succeed or fail together
BEGIN; #Turn off automatic commit of transactions
INSERT INTO user (id) VALUES(25);#success
INSERT INTO user (id) VALUES(5);#It already exists for 5 years and will fail
COMMIT; #Manually commit transactions

rollback

#When multiple statements are executed in batch and insert inserts duplicate primary keys, resulting in failure, the transaction is rolled back
BEGIN;
INSERT INTO user (id) VALUES(15);
INSERT INTO user (id) VALUES(35);#There it is
ROLLBACK;#If the transaction is rolled back, it will not be committed again

Table association

summary

Table represents an entity in life, such as department table dept and employee table emp. Table association represents the relationship between tables, such as department and employee, commodity and commodity classification, teacher and student, classroom and student.
At the same time, we should also know that tables are not all related. They form their own small circle. For example, it may not matter if the goods and the details of the goods are circled, and the departments and employees are circled. For example, the goods have nothing to do with the employees, and the goods have nothing to do with the students.

Below, we discuss the relationship between tables, which can be divided into four types:
1. one to one QQ and QQ email, employee and employee number
2. one to many is the most common, departments and employees, users and orders
3. many to one many to many in turn, employees and departments, orders and users
4. many to many teachers and students, teachers and courses

Examination system mysql version

Table design

l foreign key: drag the mouse from the sub table to the main table. When you reach the main table, PD will automatically add foreign key fields

l lecture schedule and curriculum: one to many, two tables. Association relationship: the child table stores the primary key of the primary table, which is called foreign key

l course schedule and student schedule: many to many, three tables. Embodiment of association relationship: the sub table cannot store multiple association information of the main table. Only one more table can be created to store its information

l intermediate table: stores the primary keys of two tables. The primary key of a table cannot identify the uniqueness of records. Only the two together can identify the uniqueness. If this primary key is multiple fields, it is called a composite primary key

Create database

Create table

Table design features:

All tables end with s, identifying the plural
Most fields start with the first letter of the table. It is convenient to identify which table field is in the multi table associated query

/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     2020                                         */
/*==============================================================*/

drop table if exists courses;

drop table if exists scores;

drop table if exists students;

drop table if exists teachers;
/*==============================================================*/
/* Table: courses                                               */
/*==============================================================*/
create table courses
(
   cno                  varchar(5) not null,
   cname                varchar(10) not null,
   tno                  varchar(3) not null,
   primary key (cno)
);
/*==============================================================*/
/* Table: scores                                                */
/*==============================================================*/
create table scores
(
   sno                  varchar(3) not null,
   cno                  varchar(5) not null,
   degree               numeric(10,1) not null,
   primary key (sno, cno)
);
/*==============================================================*/
/* Table: students                                              */
/*==============================================================*/
create table students
(
   sno                  varchar(3) not null,
   sname                varchar(4) not null,
   ssex                 varchar(2) not null,
   sbirthday            datetime,
   class                varchar(5),
   primary key (sno)
);
/*==============================================================*/
/* Table: teachers                                              */
/*==============================================================*/
create table teachers
(
   tno                  varchar(3) not null,
   tname                varchar(4),
   tsex                 varchar(2),
   tbirthday            datetime,
   prof                 varchar(6),
   depart               varchar(10),
   primary key (tno)
);
alter table courses add constraint FK_Reference_3 foreign key (tno)
      references teachers (tno) on delete restrict on update restrict;
alter table scores add constraint FK_Reference_1 foreign key (sno)
      references students (sno) on delete restrict on update restrict;
alter table scores add constraint FK_Reference_2 foreign key (cno)
      references courses (cno) on delete restrict on update restrict;

Insert test data

INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'Zeng Hua' ,'male' ,'1977-09-01',95033);

INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'Kuang Ming' ,'male' ,'1975-10-02',95031);

INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'Wang Li' ,'female' ,'1976-01-23',95033);

INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'Li Jun' ,'male' ,'1976-02-20',95033);

INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'Wang Fang' ,'female' ,'1975-02-10',95031);

INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'Lu Jun' ,'male' ,'1974-06-03',95031);

 

INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'Yi Tian','male','1958-12-02','associate professor','Computer Department');

INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'Wang Xu','male','1969-03-12','lecturer','Department of Electronic Engineering');

INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'Li Ping','female','1972-05-05','assistant','Computer Department');

INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'Bing Chen','female','1977-08-14','assistant','Department of Electronic Engineering');

 

INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-105' ,'Introduction to computer',825);

INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-245' ,'operating system' ,804);

INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-166' ,'Analog circuit' ,856);

INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-106' ,'probability theory' ,831);

INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('9-888' ,'Advanced mathematics' ,831);

 

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-245',86);

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-245',75);

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-245',68);

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-105',92);

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-105',88);

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-105',76);

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'3-105',64);

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'3-105',91);

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'3-105',78);

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'6-166',85);

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'6-106',79);

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'6-166',81);

Table 6 constraints

I Non NULL constraint not null

DROP TABLE IF EXISTS tb_user;  #If the table exists, delete it. If you use it with caution, you will lose data
CREATE TABLE tb_user(
  id INT AUTO_INCREMENT,
  NAME VARCHAR(30) UNIQUE NOT NULL,
  age INT,
  phone VARCHAR(20) UNIQUE NOT NULL,
  email VARCHAR(30) UNIQUE NOT NULL,
  PRIMARY KEY (id)
);
DESC tb_user;
#The id is a self incrementing primary key, and the null value is invalid. The database will automatically replace it with the next id value
#age can be set to null because the run is null
INSERT INTO tb_user (id,age) VALUES(NULL,NULL);

II unique constraint

The Name field creates a unique constraint. When inserting data, the database will check. If the inserted values are the same, an error will be reported:

DROP TABLE IF EXISTS tb_user;  #If the table exists, delete it. If you use it with caution, you will lose data
CREATE TABLE tb_user(
  id INT,
  NAME VARCHAR(30) UNIQUE NOT NULL,
  phone VARCHAR(20) UNIQUE NOT NULL,
  email VARCHAR(30) UNIQUE NOT NULL,
  PRIMARY KEY (id)
);
DESC tb_user;
INSERT INTO tb_user (id,NAME) VALUES(1,'tony');
INSERT INTO tb_user (id,NAME) VALUES(2,'tony');

Error executing the above statement:

Query : INSERT INTO tb_user (id,NAME) VALUES(2,'tony')
Error Code : 1062
Duplicate entry 'tony' for key 'name'

Presentation table structure:

DESC tb_user;

III Primary key constraint primary key

A primary key is the unique identification of a record. It is unique and cannot be repeated

DROP TABLE IF EXISTS tb_user;  #If the table exists, delete it. If you use it with caution, you will lose data
CREATE TABLE tb_user(
  id INT,
  NAME VARCHAR(30),
  PRIMARY KEY (id)
);
INSERT INTO tb_user (id,NAME) VALUES(1,'tony');
INSERT INTO tb_user (id,NAME) VALUES(1,'hellen');

An error will be reported if the second sentence is inserted:

Query : INSERT INTO tb_user (id,NAME) VALUES(1,'hellen')
Error Code : 1062
Duplicate entry '1' for key 'PRIMARY'

Prompt: the value of primary key 1 already exists and is duplicated

IV Foreign key constraint forgrein key

DROP TABLE IF EXISTS tb_user_address;  #If the table exists, delete it. If you use it with caution, you will lose data
DROP TABLE IF EXISTS tb_user;  #If the table exists, delete it. If you use it with caution, you will lose data

CREATE TABLE tb_user (
  id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,  #Auto increment primary key
  NAME VARCHAR(50) NOT NULL UNIQUE,  #Non empty, unique index
  sex CHAR(2) DEFAULT 'male',  #Default value
  phone CHAR(18),
  age INT,
  CHECK (age>0 AND age<=200),
  createdTime DATE DEFAULT NOW()
);

CREATE TABLE tb_user_address (
  user_id INT PRIMARY KEY NOT NULL,
  address VARCHAR(200),
  FOREIGN KEY(user_id) REFERENCES tb_user(id)
);

DESC tb_user;

tb_user_address in user_id Field entry tb_user If the primary key value of the table does not exist, an error will be reported

V Default constraint default

Default value

DROP TABLE IF EXISTS tb_user;  #If the table exists, delete it. If you use it with caution, you will lose data

CREATE TABLE tb_user (
  id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,  #Auto increment primary key
  NAME VARCHAR(50) NOT NULL UNIQUE,  #Non empty, unique index
  sex CHAR(2) DEFAULT 'male',  #Default value
  phone CHAR(18),
  age INT,
  createdTime DATE DEFAULT NOW()
);

DESC tb_user;

Vi Check constraint check

Rarely used, just understand. If the age exceeds 200, an error will be reported

DROP TABLE IF EXISTS tb_user;  #If the table exists, delete it. If you use it with caution, you will lose data

CREATE TABLE tb_user (
  id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,  #Auto increment primary key
  NAME VARCHAR(50) NOT NULL UNIQUE,  #Non empty, unique index
  sex CHAR(2) DEFAULT 'male',  #Default value
  phone CHAR(18),
  age INT,
  CHECK (age>0 AND age<=200),
  createdTime DATE DEFAULT NOW()
);

DESC tb_user;

Multi table associated query join

Multi table query refers to a query based on two or more tables. In practical applications, querying a single table may not meet your needs. For example, the employee table emp displays not only deptno, but also the Department name, and the Department name dname is in the dept table.

Cartesian product

#Join the data of the two tables together

SELECT * FROM dept,emp

The above method of querying two tables is called Cartesian product, also known as direct product. Generally, Cartesian product has no practical business significance, but multi table queries are formed into Cartesian product, and then the data is filtered.

It is worth noting that in the actual development, multi table joint query is used as little as possible. This is the fundamental reason. In the query process, a large result set is built in memory, and then the data is filtered. The construction process and the memory resources used, including the judgment during filtering, are both resource-consuming and time-consuming.
This is why the Ali specification prohibits associated queries on more than three tables

Three kinds of join

inner join
Left (outer) join
right join

Case: list the information of all employees in the research department

SELECT * FROM emp

WHERE deptno = ( SELECT deptno FROM dept WHERE dname='research' )

Case: how to use INNER JOIN to realize the above requirements? Change to left join and right join to see what's the difference?

SELECT d.dname,e.ename,e.job

FROM emp e INNER JOIN dept d

ON e.deptno=d.deptno

WHERE d.dname='research'

Case: list the extension information of tony and replace it with left join and right join to see the difference?

SELECT *

FROM emp e INNER JOIN empext t

ON e.empno=t.empno

WHERE e.ename='tony'

What is the difference between inner join, left join and right join?

Only those with records on both sides of the INNER JOIN are displayed, and the others are removed
The data in the table on the left of the LEFT JOIN appears, and there is no data on the right, which is filled with NULL
The data in the right table of RIGHT JOIN appears, and no data on the left is filled with NULL

Keywords: MySQL

Added by hdpt00 on Mon, 17 Jan 2022 09:13:11 +0200

Programming VIP