MySQL basics summary

Time:2021-10-22

Field type

The difference between int (5) and int (10)

Clickable blogProgram ape grocery store
int(5)  Andint(11)The characters in the parentheses after represent the display width. The display width of the integer column has nothing to do with the number of characters MySQL needs to display the value of the column, and has nothing to do with the size of the storage space required by the integer,
intThe upper limit of data that can be stored in type fields is still 2147483647 (signed) and 4294967295 (unsigned).

The difference between char and varchar

Clickable blogA_aliane
1. The two storage sizes are different: char 258 varchar65535
2. Char is fixed length, varchar becomes longer, char efficiency is higher, and time changes space
3. Char keeps the space on both sides, and varchar will remove the space

example
The age of people is suitable for tinyint type, and the age of turtles is smallint type
When the primary key ID value of the data table does not exceed 16 million, you can use the type of mediumint
Mobile phone number storage: char (11)
Storage mailbox: fast char (40), space saving varchar (40)
Set: set (‘basketball ‘,’ Volleyball ‘,’ football ‘,’ Baseball ‘)
Enum: enum (‘male ‘,’ female ‘,’ confidential ‘)

Storage engine

Concept: a format in which a data table stores data.
The differences between common storage engines can be found in the blogm2ez

MyISAM and InnoDB

Properties / storage MyISAM InnoDB
storage structure Each table is stored in three files: frm lattice definition MyD (mydata) – data file MYI (myindex) – index file All tables are saved in the same data file (or multiple files or independent tablespace files). The size of InnoDB table is only limited by the size of operating system files, generally 2GB
storage space MyISAM can be compressed with small storage space InnoDB tables need more memory and storage. It will establish its special buffer pool in main memory for caching data and indexes
Storage mode Not in order, press write to store The data writing order is inconsistent with the storage order. The records are placed in the corresponding position in the order of the primary key, which is slightly slower than that of MyISAM.
Portability, backup and recovery Since MyISAM data is stored in the form of files, it will be very convenient in cross platform data transfer. A table can be operated separately during backup and recovery The free solution can be to copy data files, back up binlog, or use mysqldump. When the amount of data reaches tens of gigabytes, it is relatively painful
Transaction security Atomicity per query is not supported Transaction safe (acid compliant) tables with commit, rollback, and crash recovery capabilities are supported
AUTO_INCREMEN The MyISAM table can be federated with other fields InnoDB must contain an index with only this field
SELECT Better MyISAM query
INSERT Better InnoDB
UPDATE Better InnoDB
DELETE InnoDB is better. It does not rebuild the table, but deletes it row by row
COUNT without WHERE MyISAM is better. Because MyISAM saves the specific number of rows in the table InnoDB does not save the specific number of rows in the table. It needs to scan and count row by row, which is very slow
COUNT with WHERE equally Similarly, InnoDB locks tables
lock Only table locks are supported / low concurrency It supports table lock, row lock and row lock, which greatly improves the new ability of multi-user concurrent operation. However, the row lock of InnoDB is only valid for the primary key of where, and the non primary key where will lock the whole table
Foreign key I won’t support it support
Fulltext full text index support Not supported (English full-text indexing is supported above 5.6.4). You can obtain full-text indexing from InnoDB by using Sphinx, which will be slower

lock

Pessimistic lock and optimistic lock

Clickable blogjopen

Pessimistic lock Optimistic lock
concept Just like its name, it refers to a conservative (pessimistic) attitude towards the modification of data by the outside world (including other current transactions of the system and transactions from external systems). Therefore, the data is locked in the whole data processing process. The implementation of pessimistic locking often depends on the locking mechanism provided by the database (only the locking mechanism provided by the database layer can truly ensure the exclusivity of data access. Otherwise, even if the locking mechanism is implemented in this system, it can not guarantee that the external system will not modify the data) Optimistic concurrency control (also known as “optimistic lock”, abbreviated as “OCC”) is a method of concurrency control. It assumes that multi-user concurrent transactions will not affect each other during processing, and each transaction can process the affected part of data without locks. Before committing data updates, each transaction will first check whether other transactions have modified the data after the transaction reads the data. If other transactions have updates, the committing transaction will be rolled back. Optimistic transaction control was first proposed by Professor H.T. Kung
Used in MySQL InnoDB Step 4: 0. Start transaction begin/ begin work;/ start transaction; (select one of the three) 1. Query the commodity information select status from t_ goods where id=1 for update; 2. Generate order insert into t according to commodity information_ orders (id,goods_id) values(null,1); 3. Modify the commodity status to 2update t_ goods set status=2; 4. Commit the transaction/ commit work; Step 3: 1. Query the product information select (status, status, version) from t_ Goods where id = #{id} 2. Generate an order according to the commodity information 3. Modify the commodity status to 2update t_ goods set status=2,version=version+1where id=#{id} and version=#{version};
Advantages and disadvantages Pessimistic concurrency control is actually a conservative strategy of “take lock first and then access”, which provides a guarantee for the security of data processing. However, in terms of efficiency, the mechanism of dealing with locking will cause additional overhead to the database and increase the chance of deadlock; In addition, in read-only transaction processing, there is no conflict and it is not necessary to use locks, which can only increase the system load; In addition, it will reduce the parallelism. If a transaction locks a row of data, other transactions must wait for the transaction to finish processing before they can process that row Optimistic concurrency control believes that the probability of data race between transactions is relatively small, so it should be done directly as far as possible and locked until the time of submission, so no locks and deadlocks will be generated. However, if you simply do this directly, you may encounter unexpected results. For example, two transactions read a row in the database and write it back to the database after modification. At this time, you encounter a problem.

trigger

A trigger is a named database object related to a table that is activated when a specific event occurs in the table\
Listening: adding, modifying, and deleting records.

Create trigger

  • CREATE TRIGGER trigger_ name trigger_ time trigger_ event ON tbl_ name FOR EACH ROW trigger_ Stmt parameter: trigger_ Time is the action time of the trigger program. It can be before or after to indicate that the trigger program is triggered before or after the statement that activates it. trigger_ Event indicates the type of statement that activates the trigger
  • Insert: activates the trigger when a new row is inserted into the table
  • Update: activates the trigger when a line is changed
  • Delete: activates the trigger when a row is deleted from the table
  • tbl_ Name: the listening table must be permanent. The trigger program cannot be associated with the temporary table or view.
  • trigger_ Stmt: the statement executed when the trigger program is activated. To execute multiple statements, you can use the begin… End compound statement structure

delete

  • DROP TRIGGER [schema_name.]trigger_name
  • Old and new can be used instead of old and new data
  • Update operation: old before update and new after update
  • Delete operation, only old
  • Add operation, only new

be careful

For a given table with the same trigger action time and event, there cannot be two triggers. Character concatenation function concat (STR1, STR2,…])_ ws(separator,str1,str2,…)

grammar

Branch statement
If condition then
Execute statement
elseIf condition then
Execute statement
else
Execute statement
end if;

 Modify outermost statement Terminator
Delimiter custom end symbol
SQL statement
Custom end symbol
delimiter ;       Change back to the original semicolon

 Statement block wrap
begin
Statement block
end

Special execution

  1. As long as the record is added, the program will be triggered.
  2. Insert into on duplicate key update syntax will trigger:
    If there are no duplicate records, before insert and after insert will be triggered;
    If there are duplicate records and updates, before insert, before update and after update will be triggered;
    If there are duplicate records but no update occurs, before insert and before update are triggered
  3. Replace syntax if there is a record, execute before insert, before delete, after delete, after insert

SQL programming

variable

Variable declaration
declare var_name[,...] type [default value]
This statement is used to declare local variables. To provide a default value for a variable, include a default clause. The value can be specified as an expression and does not need to be a constant. If there is no default clause, the initial value is null.
 assignment
使用 set 和 select into 语句为变量assignment。
-Note: global variables (user-defined variables) can be used in functions

//Global variable 
 定义、assignment
set 语句可以定义并为变量assignment。
set @var = value;
也可以使用select into语句为变量初始化并assignment。这样要求select语句只能返回一行,但是可以是多个字段,就意味着同时为多个变量进行assignment,变量的数量需要与查询的列数一致。
还可以把assignment语句看作一个表达式,通过select执行完成。此时为了避免=被当作关系运算符看待,使用:=代替。(set语句可以使用= 和 :=)。
select @var:=20;
select @v1:=id, @v2=name from t1 limit 1;
select * from tbl_name where @var:=30;
Select into to assign the data obtained from the query in the table to the variable.
-| select max(height) into @max_height from tb;

 Custom variable name
In order to avoid the conflict between user-defined variables and system identifiers (usually field names) in the select statement, user-defined variables use @ as the starting symbol before the variable name.
@var=10;
-After the variable is defined, it is valid for the whole session cycle (login to exit)

control structure

If statement
if search_condition then
statement_list
[elseif search_condition then
statement_list]
...
[else
statement_list]
end if;
 Case statement
CASE value WHEN [compare-value] THEN result
[WHEN [compare-value] THEN result ...]
[ELSE result]
END
 while Loop 
[begin_label:] while search_condition do
statement_list
end while [end_label];
- 如果需要在循环内提前终止 while Loop ,则需要使用标签;标签需要成对出现。
 Exit loop
Exit the entire loop leave
Exit the current cycle iterate
Which loop to exit is determined by the exit tag

function

Numerical function
ABS (x) absolute ABS (- 10.9) = 10
Format (x, d) format the millennial value format (1234567.456, 2) = 1234567.46
Ceil (x) rounded up ceil (10.1) = 11
Floor (x) rounded down floor (10.1) = 10
Round (x) rounding
Mod (m, n) M% n m mod n remainder 10% 3 = 1
PI () to obtain pi
pow(m, n)        m^n
Sqrt (x) arithmetic square root
Rand () random number
Truncate (x, d) truncates D decimal places

 Time date function
now(), current_ timestamp();       Current date and time
current_ date();                   current date
current_ time();                   current time 
date('yyyy-mm-dd hh:ii:ss');      Get date section
time('yyyy-mm-dd hh:ii:ss');      Get time section
date_ format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j');   Format time
unix_ timestamp();                 Get UNIX timestamp
from_ unixtime();                  Get time from timestamp

 String function
Length (string) string length, bytes
char_ Length (string) the number of characters in a string
Substring (STR, position [, length]) starts from the position of STR and takes length characters
Replace (STR, search_str, replace_str) use replace in str_ STR replace search_ str
Instr (string, substring) returns the position where substring first appears in string
Concat (string [,...]) connection string
Charset (STR) returns the string character set
Convert lcase (string) to lowercase
Left (string, length) takes length characters from the left in string2
load_ File (file_name) reads content from a file
Locate (substring, string [, start_position]) is the same as instr, but the start position can be specified
Lpad (string, length, pad) repeatedly adds pad to the beginning of string until the string length is length
Ltrim (string) removes front-end spaces
Repeat (string, count) repeat count times
Rpad (string, length, pad) is supplemented with pad after STR until the length is length
Rtrim (string) removes back-end spaces
StrCmp (string1, string2) compares the size of two strings character by character

 Process function
Case when [condition] then result [when [condition] then result...] [else result] end multi branch
If (expr1, expr2, expr3) double branch.

 Aggregate function
count()
sum();
max();
min();
avg();
group_concat();

Other common functions
md5();
default();

Storage function, custom function

newly build
CREATE FUNCTION function_ Name (parameter list) return value type
Function body
-The function name should be a legal identifier and should not conflict with existing keywords.
-A function should belong to a database. You can use dB_ name.funciton_ Execute the database to which the current function belongs in the form of name, otherwise it is the current database.
-Parameter part, consisting of parameter name and parameter type. Multiple parameters are separated by commas.
- Function body由多条可用的mysql语句,流程控制,变量声明等语句构成。
-Multiple statements should be contained in the begin... End statement block.
-There must be a return value statement.

 delete
DROP FUNCTION [IF EXISTS] function_name;

 see
SHOW FUNCTION STATUS LIKE 'partten'
SHOW CREATE FUNCTION function_name;

 modify
ALTER FUNCTION function_ Name function options

stored procedure

A stored procedure is a piece of code (procedure) composed of SQL stored in the database. A stored procedure is usually used to complete a section of business logic, such as registration, shift handover fee, order warehousing, etc. A function usually focuses on a function, which is regarded as other program service, and needs to be invoked in other statements. The stored procedure can not be invoked by others, but is executed by call itself.
Examples can be clicked on the blog Crazygolf

establish
CREATE PROCEDURE sp_ Name (parameter list)
Process body
Parameter list: different from the parameter list of a function, the parameter type needs to be specified
In, indicating input type
Out, indicating output type
Inout, indicating mixed type
Note that there is no return value.

/*Stored procedure*/ 
A stored procedure is a collection of executable code. More business logic than function.
Call: call procedure name
 be careful
-No return value.
-It can only be called separately and cannot be included in other statements

 parameter
IN|OUT|INOUT parameter名 数据类型
IN      输入:在调用过程中,将数据输入到Process body内部的parameter
OUT     输出:在调用过程中,将Process body处理完的结果返回到客户端
Inout input / output: it can be input or output

 grammar
CREATE PROCEDURE 过程名 (parameter列表)
BEGIN
Process body
END

Handsome bloggers

This work adoptsCC agreement, reprint must indicate the author and the link to this article

Peter Zhao who writes poetry with his left hand and codes with his right hand