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;
Function syntax - create
There must be a return statement in the function body
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 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 ;
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
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;
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
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]
-- 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 ;
-- 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
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);
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
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;
- 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
Now deprecated cursors - poor performance
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);
Conditional branch statement
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);
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);
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;
- 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;
- 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
- When defining a function, you must specify the return type. When defining a stored procedure, you do not need to use the return type
- The function body must have a return statement + return result, but the stored procedure can have no return statement
- select keyword is used for function calls and call keyword is used for stored procedure calls
- Stored procedures focus more on encapsulating and precompiling sql statements to improve the reusability of sql statements
- Stored procedures use in to accept parameters and out to return results
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
- After trigger - execute after trigger condition
- Before trigger - execute before trigger condition
-- 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 ;
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)
- 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
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
sql optimization - at least 10
How does MySQL perform case sensitive string comparison?
select * from s_emp where binary first_name = 'Carmen';
Try to avoid using in the where clause= Or < > operator, otherwise the engine will perform a full table scan.
To optimize the query, try to avoid full table scanning. First, consider establishing indexes on the columns involved in where and order by.
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
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
select id from t where num=20
- 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
- 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
- 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
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:
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.
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
- 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)
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%;
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.
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.
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.
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.
Do not use select * from t anywhere, replace "*" with a specific field list, and do not return any fields that are not used.
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.
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.
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.
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.
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.
Try to avoid large transaction operations and improve the concurrency of the system.