Related sub query

-- Related sub query: sub queries cannot be run independently, and external queries must be run before sub queries
-- Advantages: simple and powerful
-- Disadvantages: difficult to understand

-- Query the employee with the highest salary in the Department
SELECT deptno,ename,max(sal) from emp GROUP BY deptno
-- The statements in parentheses cannot be run alone. You must run the outer query before you can query the inner one
SELECT * from emp e1 where sal in(SELECT max(sal) from emp e2 where e1.deptno=e2.deptno)

Indexes

• Improve query speed
• BTREE(B+TREE) and HASH algorithms
• Index will improve the speed of query, but reduce the speed of addition, update and deletion (not only operate database tables, but also operate indexes)

-- View index the default primary key and foreign key are indexes
show INDEX from emp;

SELECT * from emp WHERE empno=1;



SELECT * from emp WHERE ename='Tanzan ';
-- Create index
CREATE index index_emp_ename on emp(ename)
-- The search rate increases rapidly, and the amount of data should be supported
SELECT * from emp WHERE ename='Tanzan ';

-- Delete index
drop index index_emp_ename on emp;

affair

• Transactions are used to maintain database integrity
  1. Atomicity: a sequence of operations that modify data using transactions, either all or none.
  2. Consistency: consistency means that the result of transaction execution must change the database from one consistency state to another. When the database contains only the results of successful transaction submission, the database is in a consistent state. Consistency is guaranteed by atomicity.
  3. Isolation: isolation means that the execution of each transaction does not interfere with each other. The internal operation of any transaction is isolated from other concurrent transactions. In other words, concurrent transactions can neither see each other's intermediate state nor affect each other.
  4. Persistence: once a transaction is committed, any changes made to the data should be recorded in the permanent memory, usually saved into the physical database. Even if the database fails, the committed data should be able to be recovered. However, if the database fails due to external reasons, such as the hard disk is damaged, the previously submitted data may be lost.

-- Things: it is used to maintain the integrity of the database
-- Atomicity consistency isolation persistence
-- establish account Account table  
create table account(  
    id int primary key auto_increment,  
    username varchar(30) not null,  
    balance double  
);  
-- by account Insert two pieces of data into the account table at the same time  
insert into account (username, balance) values('Zhang San', 2000),('Li Si', 2000);  
-- see account Data in account table  
select * from account;  



-- Turn on the transfer transaction and set that the transaction will not be submitted automatically
start transaction;  
update account set balance=balance-200 where username='Zhang San';  
update account set balance=balance+200 where username='Li Si';  
select * from account;  
-- When we closed the database and reopened it, the account balances of Zhang San and Li Si did not change.  
-- This is because when we use“ START TRANSACTION"After a transaction is started, the submission method of the transaction is no longer automatic,  
-- Instead, you need to commit manually, and here we do not use transaction commit statements COMMIT，  
-- So yes account The modification of the data in the table was not permanently saved to the database, that is, our transfer transaction was not successfully executed  
-- Submit transfer transaction  
commit;  


-- The rollback of a transaction can also be regarded as a sign of ending the transaction, but the rollback transaction did not execute successfully, but restored the database to the initial state before the transaction operation.  
-- It should be noted that the transaction rollback must be performed before the transaction is committed, because once the transaction is committed, the rollback operation cannot be performed again.  
rollback;  




-- Transaction concurrency problem 
-- Dirty reading, unrepeatable reading, unreal reading

-- View the default transaction isolation level  MySQL The default is repeatable read  
select @@transaction_isolation;  
-- Set the isolation level of the transaction  
set session transaction isolation level read uncommitted;  
set session transaction isolation level read committed;  
set session transaction isolation level repeatable read;  

view

• Benefits:
  1. Simplify user operations and store complex statements as a view that can be called at any time
  2. Provide security protection for confidential data: with views, you can define different views for different users when designing the database application system, so as to avoid confidential data (such as sensitive field "salary") from appearing in the user view that should not see these data. In this way, the view automatically provides security for confidential data

-- Views are convenient for querying data, not for modification
SELECT * from emp;
SELECT empno,ename,job,mgr,HIREDATE,COMM,deptno from emp

-- Define a view
CREATE VIEW myview1
as
SELECT empno,ename,job,mgr,HIREDATE,COMM,deptno from emp

-- View our view
SELECT * from myview1;

-- Multi table view
CREATE VIEW myview2
as
SELECT dept.deptno,emp.ename,emp.job from emp join dept on emp.deptno=dept.deptno;

SELECT * from myview2;


-- The view can add data, and the operation is still the original table
INSERT into myview1 VALUES(7777,"ACONGSUN","HJH",7839,"1999-05-12",2850,30);




-- practice
-- Create a view to store the average salary of each department in the employee table
create or REPLACE view myview3
as 
SELECT deptno,avg(sal) "Average salary" from emp GROUP BY deptno;


SELECT * from myview3;

-- Delete view
drop view myview3;

-- Show all tables containing virtual tables
show TABLES

stored procedure

• It feels like encapsulating the form of a method calling a method in the database
• advantage:
  1. Improve execution performance.
  2. It can reduce the network burden.
  3. The processing of the database can be black box.

-- The simplest defined stored procedure has no return value
create  procedure myproc1(in name varchar(20))  
begin  
 if name is null or name = '' then  
        select * from emp;  
 else   
    select * from emp where ename like CONCAT("%",name,"%");  
 end if;  
end 

-- Run stored procedure
call myproc1("AR");  
call myproc1(""); 

-- Delete stored procedure
drop PROCEDURE myproc1


-- Defines a stored procedure with a return value  
create  procedure myproc2(in name varchar(20),out num int(4))  
begin  
 if name is null or name = '' then  
        select * from emp;  
 else   
    select * from emp where ename like CONCAT("%",name,"%");  
 end if;  
--  Number of returned data
 select found_rows() into num;  
end  

-- Call stored procedure
call myproc2("AR",@num);  
-- Be sure to bring it with you when you get the return value@
select @num 

Import and export of data

1. Export: dump the SQL file and save it
2. Import: run the SQL file in the database to be imported.

Remote access database

1. Change the permissions of your database from localhost to%
2. Refresh database permissions flush privileges;
3. Enter the ip address of the database to be accessed remotely and the corresponding database name and password