function

Just understand - it's definitely not used in development

Single line function - string function, number function, date function

Multiline function (group function)

In fact, the database can be programmed

If you are using mysql8.x, you need to set it to allow custom functions

1. stay my.ini Add under file: log-bin-trust-function-creators=1 
   Restart required db The server

2. stay mysql client[terminal] - set global log_bin_trust_function_creators=TRUE;

grammar

Function syntax - create

Note:

1. There must be a return statement in the function body

2. Whenever a varchar type is encountered, the length of the parameter must be specified

-- mysql The default ending symbol in is;
-- Modified the default end symbol bit $$
-- Semicolons must appear in the body of the function
delimiter $$

-- Create function
create function Function name([Variable data type(length)]) returns Return data type
begin
	-- Function body
	return result;
end $$

delimiter ;

Delete function

-- Delete function
drop function Function name;

Exercise 1 - custom functions

Pass in two integers and return the result of the addition of two integers

drop function adds;

delimiter //
create function adds(a int,b int) returns int
begin
	return a + b;
end //
delimiter ;

Call function

select adds(10,30);

Exercise 2 - custom functions

Date format: mm / DD / yyyy

drop function my_date;

delimiter //
create function my_date(dt date) returns varchar(20)
begin
	return date_format(dt,'%Y year%m month%d day');
end //
delimiter ;

-- call
select my_date(now());

select my_date('2010-09-09');

Exercise 3 - while... do... end while

Feel loop statements (while... do.. end while), local variables (declare) and how variables are reset (set)

Sum between 1 and X

drop function x_add;
delimiter //

create function x_add(x int) returns int
begin
	-- Define two local variables
	declare i int default 1;
	declare sums int default 0;
	-- loop
	while i<=x do
		-- sums plus
		set sums = sums+i;
		-- i Self increasing
		set i = i + 1;
	end while;
	return sums;
end //
delimiter ;

select x_add(100);

Exercise 4 - if... end if

Branch statement

Add even and odd numbers between 1~x

drop function ji_add;
delimiter //
create function ji_add(x int) returns int
begin
	declare i int default 1;
	declare sums int default 0;
	while i<=x do
		if i%2!=0 then
		set sums = sums+i;
		end if;
		set i = i + 1;
	end while;
	return sums;
end //
delimiter ;

select ji_add(100);

Exercise 5 - global variables

After the number between 1 and X, but excluding multiples of 5

drop function jis_add;
delimiter //
create function jis_add(x int) returns int
begin
	-- Define a local variable
	declare i int default 1;
	-- Define a global variable - mysql Recognizable symbols@ - @Variable name - global variable
	set @sums = 0;
	-- be similar to java Medium continue sentence + Code block naming
	-- success Is the label name of a circular statement - Casually named
	success:while i<=x do
		if i%5=0 then
			set i = i + 1;
			-- The following code does not execute
			-- Continue iteration success Cycle of - continue - Continue to the next cycle,Ignore this cycle
			iterate success;
		end if;
		set @sums = @sums + i;
		set i = i + 1;
	end while;
	return @sums;
end //
delimiter ;

select jis_add(100);
-- global variable - It is accessible outside the function body
select @sums;

Stored procedure

After the mysql environment is installed, the server side of mysql server - mysql and the client side of mysql client - mysql are actually installed locally

Mysql server - right click computer - Management - Services - services and applications - right 👉 - Mysql service

MySQL client - a built-in client of MySQL. There are many third-party clients on the market [good visualization] - navicat [charged], idea

The process of executing an sql statement

mysql> select * from s_emp;

① After inputting sql and clicking enter, the client checks whether the syntax of sql is standard. If it is not standard, an error will be reported directly

② If the sql syntax is standard, the client will be responsible for sending this legal sql syntax to MySQL server - MySQL server

③ After MySQL server receives the sql from the client, MySQL server will compile and parse the sql

④ MySQL server will return the parsed result to MySQL client [execution result of sql statement]

Summary: sql statement - syntax detection - send to MySQL server - compile, parse, return to MySQL client, and save the results of this sql execution

effect

In order to complete some specific functions, pre compile sql in advance and store it in the execution schedule of MySQL server system. When calling the stored procedure for the first time

The sql will be precompiled and saved. The compilation process of sql statement is omitted in the second call

Stored procedures can achieve standard component programming [encapsulating sql statements]

grammar

-- Delete stored procedure
drop procedure Stored procedure name;

-- Create stored procedure
delimiter //
create procedure Stored procedure name([in|out] [parameter list])
begin
	-- Process body
end //
delimiter ;

experience

-- hold s_emp Average salary of employees in the table sql Precompiled on mysql-server end
-- select avg(salary) from s_emp;
-- This article sql Frequently called/Or this article sql The writing method is complex.
-- Consider putting this sql Package to - stored procedure

drop procedure sal_pro;

delimiter //
create procedure sal_pro()
begin
	-- encapsulation sql sentence
	select avg(salary) from s_emp;
end //

delimiter ;

-- precompile sql sentence - When creating stored procedures
-- mysql-server The sql Statement to compile,analysis,take sql The compiled results of the statement are saved.
-- During each subsequent call.I don't think we're going to do this again sql Statement was compiled.

-- use call Keyword to call,No compilation required
-- First call,
call sal_pro();

-- Second call
-- mysql-server There is no need to sql Statement to compile again[Omitted],Directly return the previously saved results.
call sal_pro();

Input and output

1. in
2. out
3. inout

in input

When creating a stored procedure, the parameter list. Confirm whether this parameter is used to accept parameters or return the results of the stored procedure

drop procedure in_pro;
delimiter //
create procedure in_pro(in a int)
begin
	-- System.out.println(a);
	select a;
	-- yes a Reassign
	set a = 10;
	select a;
end //
delimiter ;

-- call
-- Pass in a literal directly
call in_pro(20);

-- Test using global variables to pass parameters
set @i = 20;
call in_pro(@i);

out output

If the parameter is decorated with out, it cannot be used to save the received data

drop procedure out_pro;
delimiter //
create procedure out_pro(out a int)
begin
	-- a Yes out Embellished,So for null
	select a;
	-- a yes out - Data returned by stored procedure
	set a = 10;
end //
delimiter ;

-- report errors,If the parameter is out Embellished,Then a literal quantity cannot be directly passed in
-- call out_pro(100);

-- If it is out Embellished,Call stored procedure,Only one global variable can be passed in
set @i=100;
call out_pro(@i);
+------+
| a    |
+------+
| NULL |
+------+
Inside a Is not bound to your 100 - The reason is because a Is to use out Embellished.Parameters are not acceptable

select @i;
+------+
| @i   |
+------+
|   10 |
+------+

Exercise - encapsulating a single result set

1. Return the employee's name according to the employee's id. salary - sql statement - encapsulation

   drop procedure emp_pro;
   delimiter //
   create procedure emp_pro(in eid int(7),out fname varchar(25),out sal float(11,2))
   begin
   	select first_name into fname from s_emp where id=eid;
   	select salary into sal from s_emp where id = eid;
   end //
   delimiter ;
      
   -- If the parameter is out Embellished,Only global variables must be passed in[The function is to save the values of some execution results of the stored procedure]
   call emp_pro(1,@fname,@sal);
      
   select @fname;
   select @sal;
   

1. The second writing method is for a single result set

drop procedure emp_pro;
delimiter //
create procedure emp_pro(in eid int(7),out fname varchar(25),out sal float(11,2))
begin
	select first_name,salary into fname,sal from s_emp where id=eid;
end //
delimiter ;
   
-- If the parameter is out Embellished,Only global variables must be passed in[The function is to save the values of some execution results of the stored procedure]
call emp_pro(1,@fname,@sal);
   
select @fname;
select @sal;

Exercise - encapsulates a multiline result set

Find the first that is greater than a certain id_ Name and salary - the result may be multiline

1. Now deprecated cursors - poor performance

2. We need to define a third-party table to store our multi row result set

   1. Build the third-party table according to the specific query business 
      The data finally queried in the business happens to be s_emp In the table.
      
      -- Keep structure only,But the results are not preserved
      create table s_emp_pro select first_name,salary from s_emp where 1=2;
      
   2. For example, grouping statistics,The queried data does not exist in a table - Be sure to query the data according to the specific - Manually build a table
   

   drop procedure emp_pro;
   delimiter //
   create procedure emp_pro(in eid int(7))
   begin
   	-- insert into Table name select sentence;
   	insert into s_emp_pro(first_name,salary) select first_name,salary from s_emp where id >= eid;
   end //
   delimiter ;
      
   -- If the parameter is out Embellished,Only global variables must be passed in[The function is to save the values of some execution results of the stored procedure]
   call emp_pro(20);
   

Exercise - with transactions

-- Transfer function,Simultaneous success,Simultaneous failure.
drop procedure transfer_pro;
delimiter //
create procedure transfer_pro(in sid int(7),in tid int(7),in money double(7,2),in st int(7))
begin
	-- Define a local variable - Display information
	declare msg varchar(20) default '0';
	-- Manually start a transaction
	start transaction;
	update account set balance = balance-money where id = sid;
	-- Simulate an exception
	if st=1 then
		set msg = 'sorry,Transfer failed!';
		rollback;
	else
		update account set balance = balance+money where id = tid;
		-- Manually commit a transaction
		set msg = 'good,The transfer was successful!';
		commit;
	end if;
	select msg;
end //
delimiter ;

-- st=1,Entering the exception block.
call transfer_pro(1,2,1000.0,1);

-- st=2,Normally perform the business function of transfer
call transfer_pro(1,2,1000.0,2);

Statement use

Conditional branch statement

1. if ... then ... elseif ... then ...elseif... then ... else... end if;

   drop procedure if_pro;
   delimiter //
   create procedure if_pro(in a int)
   begin
   	declare msg varchar(20) default '';
   	if a>=90 then
   		set msg = 'excellent';
   	elseif a>=80 then
   		set msg = 'good';
   	else
   		set msg = 'pass';
   	end if;
   	select msg;
   end //
   delimiter ;
   
   call if_pro(85);
   

2. case ... when ... then...else... end case

   drop procedure case_pro;
   delimiter //
   create procedure case_pro(in a int)
   begin
   	declare msg varchar(20) default '';
   	case a
   		when 1 then
   			set msg='1';
   		when 2 then
   			set msg = '2';
   		else
   			set msg = '3';
   	end case;
   	select msg;
   end //
   delimiter ;
      
   call case_pro(2);
   

Circular statement

1. while ... do ... end while

   drop procedure while_pro;
   delimiter //
   create procedure while_pro(in x int,out result int)
   begin
   	declare i int default 1;
   	declare sums int default 0;
   	while i<=x do
   		set sums = sums + i;
   		set i = i+1;
   	end while;
   	-- result Modified into out =return result
   	set result = sums;
   end //
   delimiter ;
      
   -- out Embellished,Only global variables can be passed in
   call while_pro(100,@result);
      
   select @result;
   

1. loop ... end loop

Similar to while(true)

-- Seek 1~x end
drop procedure loop_pro;
delimiter //
create procedure loop_pro(in x int,out result int)
begin
	declare i int default 1;
	set result=0;
	success:loop
		if i>x then
			-- Break the cycle
			-- iterate success; -- conintue
			leave success; 
		end if;
		set result = result + i;
		set i = i + 1;
	end loop;
end //
delimiter ;

call loop_pro(100,@result);
select @result;

1. repeat ... until ... end repeat

Similar to Java - > do... while

drop procedure repeat_pro;
delimiter //
create procedure repeat_pro(in x int)
begin
	repeat
		set x = x + 1;
		select x;
	until x>0
	end repeat;
end //
delimiter ;
   
call repeat_pro(-1);

Summarize the differences between functions and stored procedures

1. When defining a function, you must specify the return type. When defining a stored procedure, you do not need to use the return type
2. The function body must have a return statement + return result, but the stored procedure can have no return statement
3. select keyword is used for function calls and call keyword is used for stored procedure calls
4. Stored procedures focus more on encapsulating and precompiling sql statements to improve the reusability of sql statements
5. Stored procedures use in to accept parameters and out to return results

trigger

In mysql, when we perform some operations [such as dml operation - event triggered by trigger], once the event is triggered, a program will be executed

Trigger is essentially a special stored procedure

classification

1. After trigger - execute after trigger condition
2. Before trigger - execute before trigger condition

grammar

-- Delete trigger 
drop trigger Trigger Name ;
delimiter //
create trigger Trigger Name 
Trigger timing(after,before) Trigger event(insert,delete,update) on The name of the table where the trigger event is located
for each row
-- Trigger event program
begin
	
end //
delimiter ;

experience

1. When deleting any data in the account table [trigger event], and put this data into the backup table acc_copy

   -- Create backup table
   create table acc_copy select * from account where 1=2;
   

   drop trigger acc_tri;
   delimiter //
   create trigger acc_tri
   after delete on account
   for each row
   begin
   	-- old.Listing - The data just deleted
   	insert into acc_copy values(old.id,old.name,old.balance);
   end //
   delimiter ;
      
   -- Triggers are executed only when they encounter triggered events.We didn't call it manually.
   delete from account where id=1;
   

mysql> select * from account;
+—-+-+-—-+
| id | name | balance |
+—-+-+-—-+
| 2 | tom | 10000.00 |
| 3 | jack | 9000.00 |
| 4 | ss | 9000.00 |
| 5 | ss2 | 9000.00 |
+—-+-+-—-+
4 rows in set (0.00 sec)

2. If there is a constraint relationship between tables,For example, 1:N,Whether in drop perhaps delete When.You must delete more than one party first,Then delete one party

   All sub records of one party must be deleted first,To delete a party

   ~~~mysql
   drop table tri_cus;
   drop table tri_ord;

   create table tri_cus(
   	id int(7) primary key,
     name varchar(20)
   );
   insert into tri_cus values(1,'admin');
   insert into tri_cus values(2,'tom');

   create table tri_ord(
   	id int(7) primary key,
     ord_no varchar(20),
     cus_id int(7),
     foreign key(cus_id) references tri_cus(id)
   );
   insert into tri_ord values(1,'1001',1);
   insert into tri_ord values(2,'1002',1);
   insert into tri_ord values(3,'1003',2);

   -- Reasons for foreign key constraints
   mysql> delete from tri_cus where id=1;
   ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`j03s`.`tri_ord`, CONSTRAINT `tri_ord_ibfk_1` FOREIGN KEY (`cus_id`) REFERENCES `tri_cus` (`id`))


   -- Before deleting a customer,The order should be deleted first
   drop trigger cus_tri;
   delimiter //
   create trigger cus_tri
   before delete on tri_cus
   for each row
   begin
   	-- Delete the customer's sub record first
   	delete from tri_ord where cus_id=old.id;
   end //
   delimiter ;

   mysql> delete from tri_cus where id=1;

   mysql> select * from tri_cus;
   +----+------+
   | id | name |
   +----+------+
   |  2 | tom  |
   +----+------+
   1 row in set (0.00 sec)

   mysql> select * from tri_ord;
   +----+--------+--------+
   | id | ord_no | cus_id |
   +----+--------+--------+
   |  3 | 1003   |      2 |
   +----+--------+--------+
   1 row in set (0.00 sec)

1. There will also be a self check constraint [custom detection] and check constraint in oracle database

However, there is no check constraint in Mysql

-- oracle - use check constraint
create table ckt(
	id int(7) primary key,
  age int(1) check(age>0 and age<100)
)
-- When inserting data,age Inserted values can only be check Constraints within custom rules,Otherwise, the insertion fails.
   
-- Interview questions - mysql How to implement in oracle In database check Function of self checking constraint.
-- In execution insert When,Further verification of some data - trigger
create table ckt(
	id int(7) primary key,
  age int(1)
);
   
drop trigger ckt_tri;
delimiter //
create trigger ckt_tri
before insert on ckt
for each row
begin
	-- Perform a check on the newly inserted data
	-- old.Column or use new.old column - See if the column is in the table.
	if new.age<18 then
		signal sqlstate 'HY000' set message_text='age wrongful';
	end if;
end //
delimiter ;
   
insert into ckt values(1,20);
   
insert into ckt values(2,17);
mysql> insert into ckt values(2,17);
ERROR 1644 (HY000): age wrongful

Database optimization

• Sub table / sub database

• Master slave replication

• sql optimization operation

  • The select query column should not appear*

    Try or select Specific column name,
    select * Lower efficiency select Specific columns - "dispute"
    
    For data security.
    

  • The use of the order by statement is discouraged

    mysql Medium order by Performance is extremely low,If sorting is encountered in business.
    The suggestion is to take out the data and put it into the java In memory - In collection.
    
    Pass again java The collection of codes sorts the data in various ways.
    

  • When querying the value of unique non index column, cooperate with the limit 1 statement to avoid full table scanning

  • Index optimization

sql optimization - at least 10

1. How does MySQL perform case sensitive string comparison?

   select * from s_emp where binary first_name = 'Carmen';
   

2. Try to avoid using in the where clause= Or < > operator, otherwise the engine will perform a full table scan.

3. To optimize the query, try to avoid full table scanning. First, consider establishing indexes on the columns involved in where and order by.

4. Try to avoid judging the not null value of the field in the where clause, otherwise the engine will give up using the index and scan the whole table

5. Try to avoid using or to join conditions in the where clause, otherwise the engine will give up using the index and scan the whole table, such as:

select id from t where num=10 or num=20
You can query as follows:
​ select id from t where num=10
​ union
​ select id from t where num=20

1. The following query will also lead to full table scanning: (the percentage sign cannot be preceded)

select id from t where name like ‘c%’;// Take the index

1. not in should also be used with caution, otherwise it will cause full table scanning, such as:

For continuous values, you can use between Don't use it in The following:
select id from t where num between 1 and 3

1. Try to avoid expression operations on fields in the where clause, which will cause the engine to give up using indexes and perform a full table scan. For example:
   select id from t where num/2=100

Should read:
select id from t where num=100*2

1. Try to avoid functional operations on fields in the where clause, which will cause the engine to abandon the use of indexes and perform a full table scan. For example:

2. Do not perform functions, arithmetic operations or other expression operations on the left of "=" in the where clause, otherwise the system may not use the index correctly.

3. When using an index field as a condition, if the index is a composite index, the first field in the index must be used as a condition to ensure that the system uses the index, otherwise the index will not be used, and the field order should be consistent with the index order as much as possible.

• Follow the leftmost principle

1. In many cases, using exists instead of in [query performance is very low] is a good choice:

select num from a where num in(select num from b)
Replace with the following statement:
​ select num from a where exists(select 1 from b where num=a.num)

1. Not all indexes are valid for queries. SQL optimizes queries based on the data in the table. When there are a large number of duplicate data in the index column, SQL queries may not use indexes. For example, there are almost half of the fields sex, male and female in a table. Even if an index is built on sex, it will not play a role in query efficiency - index is valid - data control is 30%;

2. The more indexes, the better. Indexes can not only improve the efficiency of corresponding select ion, but also reduce the efficiency of insert and update. Because the index may be rebuilt during insert or update, how to build an index needs careful consideration, depending on the specific situation. The index number of a table should not exceed 6. If it is too many, consider whether it is necessary to build an index on some infrequently used columns.

3. Updating clustered index data columns should be avoided as much as possible, because the order of clustered index data columns is the physical storage order of table records. Once the column value changes, the order of the whole table records will be adjusted, which will consume considerable resources. If the application system needs to update the clustered index data column frequently, it needs to consider whether the index should be built as a clustered index.

4. Try to use numeric fields. If fields containing only numeric information are not designed as characters, it will reduce the performance of query and connection and increase the storage overhead. This is because the engine will compare each character in the string one by one when processing queries and connections. For numeric types, only one comparison is enough.

5. Use varchar/nvarchar instead of char/nchar as much as possible, because firstly, the storage space of variable length fields is small, which can save storage space. Secondly, for queries, the search efficiency in a relatively small field is obviously higher.

6. Do not use select * from t anywhere, replace "*" with a specific field list, and do not return any fields that are not used.

7. Avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources.

Temporary tables and table variables - Table variables are recommended instead of temporary tables.

1. Temporary tables are not unusable. Using them appropriately can make some routines more effective, for example, when you need to repeatedly reference a dataset in a large table or common table. However, for one-time events, it is best to use the export table.

2. When creating a new temporary table, if a large amount of data is inserted at one time, select into can be used instead of create table to avoid causing a large number of log s and improve the speed; If the amount of data is small, in order to ease the resources of the system table, create table first and then insert.

3. If temporary tables are used, you must explicitly delete all temporary tables at the end of the stored procedure, truncate table first, and then drop table, so as to avoid locking the system tables for a long time.

4. Avoid using cursors as much as possible because the efficiency of cursors is poor. If the data operated by cursors exceeds 10000 rows, rewriting should be considered.

5. Try to avoid large transaction operations and improve the concurrency of the system.