Indexes

Data structure to help mysql obtain data efficiently

Disadvantages: it reduces the efficiency of updating tables and increases disk overhead.

B tree and B + tree

The number of B's in the m-fork contains at most m-1 keys per node, while the number of B's + contains at most M keys
The leaf node of the B + tree stores all key information. All data exists in the leaf node, and all non leaf nodes are the index part of the key

The btree index of mysql optimizes the b + tree and adds a pointer to the next leaf node for each leaf node. It is a two-way linked list structure, which is convenient for range search

Index design principles

Index tables with high query times and large amount of data
Select the fields in the query criteria to create an index
Try to use unique indexes. The higher the discrimination, the better the efficiency
The index is not the more, the better. It can be appropriate
Try to use a short index (short field index) to save disk space. If the fields constituting the index are short, more index values can be stored in a storage block of a given size,
Composite index, a composite index composed of N columns, is equivalent to establishing n indexes, which saves overhead compared with creating multiple indexes. For example, creating a composite index for three conditions col1, col2 and col3 in the where clause is equivalent to creating (col1), (col1, col2), (col1, col2 and col3) indexes. If indexes are created for col1, col2 and col3 columns respectively, MySQL will only select a column with high recognition as the index. Assuming that there are 100w data and 10% of the data is filtered out by an index, 10w data can be filtered out; For the composite index, 100w*10%*10%*10%=1000 pieces of data can be filtered
Leftmost matching principle
Assuming that a combined index (col1, col2, col3) is created, it is equivalent to sorting the col1 column, that is, we create a combined index based on the leftmost column. As long as there is the leftmost column in the query criteria, the index will be used in the query

view

A virtual table is just a select statement
Create view:

create view View name as select sentence

View view:

select * from View name

Modify view:

update View name set field="xxx" -- (Modifying the view will modify the data in the original table, which is not recommended)

Show all views:

show views

Show create view statement:

show create view View name

Delete view:

drop view (if exists) View name

Stored procedures and stored functions

They mean that they encapsulate a collection of sql, which can reduce the number of interactions between the application and the database. The stored procedure has no return value, but the stored function has a return value. They are not recommended in Ali specification, inconvenient debugging, poor portability and increase the burden of the database

stored procedure

Create stored procedure:

delimiter $ //Use $to represent ';'
create procedure name(Parameters...)
begin
	--sql
end;

Call stored procedure:

call Stored procedure name()

To view stored procedures:

show procedure status;
SELECT name FROM mysql.proc where db='Database name'
show create procedure Stored procedure name

Delete stored procedure:

drop procedure (if exists) 'name'

Declare variable

delimiter $
create PROCEDURE test01()
	BEGIN 
		declare num int DEFAULT 10;
		-- declare Variable name variable type DEFAULT Default value;
		SELECT concat('num The value of is:',num);
	END$
	
create PROCEDURE pro_test02()
	BEGIN 
		declare num int DEFAULT 0;
		set num = num + 10;
		SELECT num;
	END$
	call pro_test02()

delimiter $
create PROCEDURE pro_test03()
	BEGIN 
		declare num int;
		select count(*) into num from user;
		SELECT concat('user The number of records in the table is:',num);
	END$
	call pro_test03()

Conditional judgment

	delimiter $
create PROCEDURE pro_test04()
	BEGIN 
		declare height int default 175;
		declare descr VARCHAR(50) default '';
		if height >= 180 then 
			set descr='Big tall';
		elseif height >= 170 and height < 180 then 
			set descr='normal person';
		else 
			set descr='Dwarf';
		end if;
		SELECT concat('height',height,'Corresponding to',descr);
	END$
	call pro_test04()

Transfer parameters

		delimiter $
create PROCEDURE pro_test05(in height int)
-- in Input parameters, out Output parameters, inout Both input and output 
	BEGIN 
		declare descr VARCHAR(50) default '';
		if height >= 180 then 
			set descr='Big tall';
		elseif height >= 170 and height < 180 then 
			set descr='normal person';
		else 
			set descr='Dwarf';
		end if;
		SELECT concat('height',height,'Corresponding to',descr);
	END$
	call pro_test05(180)
			delimiter $
create PROCEDURE pro_test06(in height int,out descr VARCHAR(50) )
	BEGIN 
		if height >= 180 then 
			set descr='Big tall';
		elseif height >= 170 and height < 180 then 
			set descr='normal person';
		else 
			set descr='Dwarf';
		end if;

	END$
	call pro_test06(122,@descr)
	SELECT @descr -- @Represents a user variable @@System variable

case structure

				delimiter $
create PROCEDURE pro_test07(mon int,out result VARCHAR(10))
	BEGIN 
		CASE
	WHEN mon>=1 and mon<=3 THEN
		set result='first quarter ';
	WHEN mon>=4 and mon<=6 THEN
		set result='Second quarter';
	WHEN mon>=7 and mon<=9 THEN
		set result='Third quarter';
	ELSE
		set result='Fourth quarter';
END CASE;

	END$
	call pro_test07(5,@res);
	SELECT @res

while Loop

delimiter $-- Add from 1 to n
create PROCEDURE pro_test08(n int)
  
	BEGIN 
	DECLARE total int DEFAULT 0;
	DECLARE num int DEFAULT 1;
		WHILE num <= n DO
		set total = total + num;
		set num = num + 1;
END WHILE;
	SELECT total;

	END$
	call pro_test08(3);

repeat loop

	delimiter $
create PROCEDURE pro_test09(n int)
  
	BEGIN 
	DECLARE total int DEFAULT 0;
	REPEAT
	set total=total + n;
	set n = n - 1;
UNTIL n=0 END REPEAT;

	SELECT total;
	END$
	call pro_test09(3);

loop+leave loop

delimiter$
create PROCEDURE pro_test10(n int)
BEGIN
	DECLARE total int default 0;
	con: LOOP-- con Alias for loop
	set total = total + n;
	set n = n - 1;
	IF n <= 0 THEN-- Conditions for exiting the loop
		LEAVE con; 
	END IF; 
END LOOP con;
SELECT total;
end$

CALL pro_test10(100)

cursor

Cursors are data types used to store query result sets. Cursors can be used to cycle the result sets in stored procedures and functions. The usage of cursor includes cursor declaration, OPEN, FETCH and CLOSE. The syntax is as follows.

Declare cursor:

DECLARE cursor_name CURSOR FOR select_statement ;

OPEN cursor:

OPEN cursor_name ;

FETCH cursor:

FETCH cursor_name INTO var_name [, var_name] ...

CLOSE cursor:

CLOSE cursor_name ;

Example

create table emp(
  id int(11) not null auto_increment ,
  name varchar(50) not null comment 'full name',
  age int(11) comment 'Age',
  salary int(11) comment 'salary',
  primary key(`id`)
)engine=innodb default charset=utf8 ;

insert into emp(id,name,age,salary) values(null,'Golden King ',55,3800),(null,'White browed eagle king',60,4000),(null,'Green winged bat King',38,2800),(null,'Purple Dragon King',42,1800);

delimiter$
create PROCEDURE pro_test11()
BEGIN
	declare e_id int(11);
  declare e_name varchar(50);
  declare e_age int(11);
  declare e_salary int(11);
	DECLARE emp_result cursor for select * from emp;
	
	open emp_result;
	fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', Salary is: ',e_salary);
  
  fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', Salary is: ',e_salary);
	
	fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', Salary is: ',e_salary);
	
	fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', Salary is: ',e_salary);
	
	fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', Salary is: ',e_salary);
	CLOSE emp_result;
	
end$

CALL pro_test11()

//Using loop implementation

delimiter$
create PROCEDURE pro_test12()
BEGIN
	declare e_id int(11);
  declare e_name varchar(50);
  declare e_age int(11);
  declare e_salary int(11);
	DECLARE has_data int default 1;
  //Pay attention to the declaration order, otherwise an error will be reported
  DECLARE emp_result CURSOR FOR select * from emp;
  DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;-- Handle mechanism
	
	open emp_result;

	WHILE has_data > 0 DO
	fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', Salary is: ',e_salary);
END WHILE;

	CLOSE emp_result; 
	
end$

CALL pro_test12();

Storage function

Grammatical structure

CREATE FUNCTION function_name([param type ... ]) 
RETURNS type 
BEGIN
	...
END;

Example

delimiter$
create FUNCTION fun1(empid int)
returns int
BEGIN
	DECLARE sss int;
	SELECT salary into sss from emp where id = empid;
	return sss;

END$
-- If the creation fails, execute the following statement
set global log_bin_trust_function_creators=TRUE;

SELECT fun1(2)

trigger

introduce
Triggers are database objects related to tables. They refer to the collection of SQL statements defined in triggers that are triggered and executed before or after insert/update/delete. This feature of trigger can help the application in the database to ensure data integrity, logging, data verification and other operations.

The aliases OLD and NEW are used to refer to the changed records in the trigger, which is similar to other databases. Now triggers only support row level triggering, not statement level triggering.

Create trigger

create trigger trigger_name 

before/after insert/update/delete

on tbl_name 

[ for each row ]  -- row-level trigger 

begin

	trigger_stmt ;

end; 

Example

create table emp_logs(
  id int(11) not null auto_increment,
  operation varchar(20) not null comment 'Operation type, insert/update/delete',
  operate_time datetime not null comment 'Operation time',
  operate_id int(11) not null comment 'Operation table ID',
  operate_params varchar(500) comment 'Operating parameters',
  primary key(`id`)
)engine=innodb default charset=utf8;

-- Add trigger
delimiter$
create TRIGGER emp_trigger_insert
after insert
on emp
for each row
begin
	insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'insert',now(),new.id,concat('After insertion(id:',new.id,', name:',new.name,', age:',new.age,', salary:',new.salary,')')); 
end$
--Update trigger 
delimiter$
create TRIGGER emp_trigger_update
after UPDATE
on emp
for each row
begin
	insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'update',now(),new.id,concat('Before modification(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,'),After modification(id:',new.id,', name:',new.name,', age:',new.age,', salary:',new.salary,')')); 
end$

-- Delete trigger 
delimiter$
create TRIGGER emp_trigger_delete
after DELETE
on emp
for each row
begin
	insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'update',now(),old.id,concat('Deleted data(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,')')); 
end$

View triggers

show TRIGGERS