1. Services
1.1 concept of transaction
Transaction: a transaction consists of one or more SQL statements in a single unit, in which each MySQL statement is interdependent. The whole single cell is an inseparable whole. If an SQL statement in the cell fails or generates an error, the whole cell will be rolled back. All affected data will be returned to the state before the start of things; If all SQL statements in the cell are executed successfully, the transaction is executed smoothly.
1.2 characteristics of transactions
- Atomicity
Atomicity means that a transaction is an indivisible unit of work, and operations in a transaction either occur or do not occur. - Consistency
Transactions must transition the database from one consistency state to another - Isolation
Transaction isolation means that the execution of a transaction cannot be disturbed by other transactions, that is, the operations and data used within a transaction are isolated from other concurrent transactions, and the concurrent transactions cannot interfere with each other. - Durability
Persistence means that once a transaction is committed, its changes to the data in the database are permanent, and other subsequent operations and database failures should not have any impact on it
1.3 transaction creation
- Implicit transaction: the transaction has no obvious start and end marks (such as insert, delete, update statements)
- Show transactions:
Transactions must have obvious opening and closing marks
Premise: the auto submit function must be set to disabled (set autocommit=0)
Steps for a transaction
# Step 1: start the transaction set autocommit = 0; start transaction; # Optional # Step 2: write the SQL statement of the transaction (select, delete, update, insert) Statement 1 Statement 2 . . . # Step 3: end the transaction commit; #Commit transaction rollback;#Rollback transaction
1.4 isolation level of database
1.4.1 what are dirty reading, unreal reading and non repeatable reading
For multiple transactions running at the same time, when these transactions access the same data in the database, if the necessary isolation mechanism is not taken, various concurrency problems will be caused:
- Dirty read: for two transactions T1, T2, T1 reads the fields that have been updated by T2 but have not been committed. Then, if T2 rolls back, the contents read by T1 are temporary and invalid
- Non repeatable reading: for two transactions T1, T2, T1 reads a field, and T2 updates the field. Then, T1 reads the same field again, and the values are different
- Phantom reading: for two transactions T1, T2, T1 reads a field from a table, and T2 inserts some new rows in the table. Then, if T1 reads the same table again, several more rows will be added
1.4.2 differences between dirty reading, unreal reading and non repeatable reading
The difference between non repeatable reading and dirty reading is that dirty reading reads uncommitted data, while non repeatable reading reads the data committed by the previous transaction.
In some cases, non repeatable reading does not affect the correctness of the data. For example, the data that needs to be queried multiple times should also be based on the data found in the last query.
Both phantom reading and non repeatable reading read another committed transaction (this is different from dirty reading). The difference is that the non repeatable reading query is the same data item, while phantom reading is for a batch of data as a whole (such as the number of data).
1.4.3 transaction isolation
Isolation of database transactions: the database system must have the ability to isolate concurrent transactions so that they will not affect each other and avoid various concurrency problems
The isolation degree between a transaction and other transactions is called isolation level. The database specifies multiple transaction isolation levels. Different isolation levels correspond to different interference levels. The higher the isolation level, the better the data consistency, but the weaker the concurrency
Oracle supports two transaction isolation levels: read committed and serial. The default transaction isolation level of Oracle is read committed
Mysql supports four transaction isolation levels. The default transaction isolation level of Mysql is REPEATABLE READ
Every time you start a mysql program, you will get a separate database connection. Each database connection has a global variable @@tx_isolation, indicating the current transaction isolation level
View current isolation level: SELECT @@tx_isolation;
Set the isolation level of the current mySQL connection: set transaction isolation level read committed;
set global transaction isolation level read committed;
1.4.4 set save point
savepoint: node; Set save point.
set autocommit = 0； start transaction； delete from account where id = 25； savepoint a #Set save point delete from account where id= 28； rollback to a； It means that the information after setting the savepoint can be restored.
2. View
2.1 concept
A virtual table whose row and column data comes from the table used in the query defining the view, and is dynamically generated when using the view. Only sql logic is saved, and query results are not saved
2.2 creating views
grammar
create view View name as Query statement
Table preparation
insert into ss (name,math) VALUES
('Zhang San',60),
('Li Si',70),
('Wang Wu',80)
create view v1 as SELECT * from ss select * from v1 where math>70
Benefits of view
- Reuse sql statements
- Simplify complex sql operations without knowing its query details
- Protect data and improve security
2.3 modify view
grammar
# Mode 1 create or replace view View name as sql sentence # Mode II alter view View name as Query statement
test
CREATE or REPLACE view v1 as select name from ss select * from v1 alter view v1 as select id from ss select * from v1
2.4 deleting views
grammar
drop view View name,View name
2.5 view
grammar
desc View name show create view View name
2.6 update view
If the view is updated, the data in the original table corresponding to the view will also be updated
The updatability of a view is related to the definition of queries in the view. The following types of views cannot be updated (insert, delete, update).
- sql statements containing the following keywords: grouping function, distinct, group by, having, union, or union all
- Constant view
- Select contains subqueries
- join
- from a view that cannot be updated
- The subquery of the where clause references the table in the from clause
3. Variables
3.1 classification of variables
- System variable
- global variable
- Session variable
- Custom variable
- local variable
- User variable
3.2 system variables
Variables are provided by the system, not user-defined, and belong to the server
Scope of global variables: the server will assign initial values to all global variables every time it is started, which is valid for all sessions (connections), but cannot be restarted across the
Session variable scope: valid only for the current conversation (connection)
Use syntax
Note that if it is a global variable, it needs to add global; if it is a local variable, it needs to add session; if it is not written, it defaults to session
- View all system variables
show session VARIABLES show GLOBAL variables
- View some system variables that meet the criteria
show GLOBAL variables like '%a%'
- View the value of a specified system variable
select @@global .autocommit select @@session .binlog_format
- Assign a value to a system variable
Mode 1:
set global System variable name = value set session System variable name = value
Mode II
set @@global .System variable name = value set @@session .System variable name = value
3.3 user defined variables
Variables are user-defined, not system
User defined variables are divided into local variables and user variables
3.3.1 user variables
Scope: valid for the current session (connection) and the same as the scope of the session variable (can be placed anywhere)
common method
- Assignment operator: = or:=
- Declare and initialize
set @User variable name=value set @User variable name :=value select @User variable name :=value
- assignment
Method 1: through set perhaps select set @User variable name=value set @User variable name :=value select @User variable name :=value Mode 2: Pass select into select field into Variable name from surface
- View the value of the variable
select @User variable name
- View the value of the variable
select @User variable name
give an example
The user variable views the total number of rows in the ss table
set @count =(select count(*) from ss) select @count
3.3.2 local variables
Scope: only valid in defining its begin end
Where to use: the first level of begin end
- statement
DECLARE Variable name type; DECLARE Variable name type default value
- assignment
Method 1: through set perhaps select set Local variable name=value set Local variable name :=value select @Local variable name :=value Mode 2: Pass select into select field into Local variable name from surface
·-Use
select Local variable name
3.3.3 comparison of user variables and local variables
| Scope | Definition and use location | grammar | |
|---|---|---|---|
| User variable | Current session | Anywhere in the conversation | The @ sign must be added, and the type is not limited |
| local variable | In begin end | Can only be in begin end and be the first sentence | Generally, the @ symbol is not added, and the type needs to be limited |
4. Stored procedure
4.1 concepts related to stored procedures
A set of precompiled SQL statements can be understood as batch statements
benefit
- Improve code reusability
- Simplify operation
- Reduce the compilation times and the connection times with the database server, and improve the efficiency
4.2 create syntax
create procedure Stored procedure name(parameter list) begin Stored procedure body (a of combinatorial method) SQL Statement) end /** be careful 1. The parameter list consists of three parts: parameter mode parameter name parameter type Example: in stuname varchar (20) Parameter mode: in : This parameter can be used as input, that is, it needs to call the method to pass in the value out: This parameter can be used as output, that is, it can be used as return value inout: This parameter can be used as input or output, that is, it needs to pass in a value or return a value 2. If there is only one sentence in the stored procedure body, begin end can be omitted Each SQL statement in the stored procedure body must be semicoloned The end of the stored procedure can be reset using delimiter grammar delimiter End tag **/
4.2.1. Stored procedure with null parameter
delimiter $
CREATE procedure my()
BEGIN
insert into ss (name,math) VALUES
('1',11),
('2',22),
('3',33),
('4',44);
ENd $
call my()
4.2.2 stored procedure with in mode
# Find user information by name and attribute score
CREATE PROCEDURE m(in name varchar(20),in math VARCHAR(20))
BEGIN
select * from ss where ss.name=name and ss.math=math;
END
call m('1',1)
4.2.3 stored procedure with out mode
CREATE procedure ss(in id int ,out math1 int) BEGIN select ss.math into math1 from ss where ss.id=id; END call ss(1,@math1) select @math1
4.2.4 stored procedure with inout mode
CREATE PROCEDURE x(INOUT a int , inout b int)
BEGIN
set a=a+a;
set b=b+b;
END
set @m=1
set @n=2
call x(@m,@n)
select @m,@n
4.3 deletion of stored procedure
grammar
drop PROCEDURE Only one stored procedure name can be deleted at a time
4.4 viewing stored procedures
show create PROCEDURE Stored procedure name