basic operation

connect

mysql -h address -P port -u user name -p password

Database operation

To view the current database:

SELECT DATABASE();

Display current time, user name and database version:

SELECT now(), user(), version();

Create Library

CREATE DATABASE [IF NOT EXISTS] Database name database options
-- Database options:
--     CHARACTER SET charset_name
--     COLLATE collation_name

View existing libraries

SHOW DATABASES [LIKE 'PATTERN']

View current library information

SHOW CREATE DATABASE Database name

Modify the option information of the library

ALTER DATABASE Library name option information

Delete Library

DROP DATABASE [IF EXISTS] Database name
-- At the same time, delete the relevant directories of the database and their contents

Table operation

Create table

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] [Library name.]Table name (Table structure definition) [Table options]
-- Each field must have a data type
--     There cannot be a comma after the last field
--     TEMPORARY Temporary table. The table disappears automatically at the end of painting
--     For field definition:
-- 			Field name data type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']

Table options

• character set

CHARSET = charset_name
-- If the table is not set, the database character set is used

• Storage engine

  ENGINE = engine_name
  

• Displays status information for the storage engine

  SHOW ENGINES
  

• Displays log or status information for the storage engine

  SHOW ENGINE Engine name {LOGS|STATUS}
  

• Self increasing starting number

  AUTO_INCREMENT = Number of rows
  

• Data file directory

  DATA DIRECTORY = 'catalogue'
  

• Index file directory

  INDEX DIRECTORY = 'catalogue'
  

• Table notes

  COMMENT = 'string'
  

View all tables

SHOW TABLES [LIKE 'pattern']
SHOW TABLES FROM Library name

View table structure

SHOW CREATE TABLE Table name (more details)
DESC Table name / DESCRIBE indicate / EXPLAIN Table name / SHOW COLUMNS FROM Table name [LIKE 'PATTERN']
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']

Modify table

-- Options for modifying the table itself
ALTER TABLE Table name table options
eg: ALTER TABLE Table name ENGINE = MYISAM;

-- Rename table
RENAME TABLE Original table name TO New table name
RENAME TABLE Original table name TO Library name.Table name	(Table can be moved to another database)

Modify the field structure of the table
ALTER TABLE Table name operation name
-- Operation name:
    ADD [COLUMN] Field definition		-- Add field
        AFTER Field name			-- Indicates that it is added after this field
        FIRST					-- Indicates an increase in the first
    ADD PRIMARY KEY(Field name)	-- Create primary key
    ADD UNIQUE [Index name] (Field name)	-- Create unique index
    ADD INDEX [Index name] (Field name)	-- Create normal index
    DROP [COLUMN] Field name			-- Delete field
    MODIFY [COLUMN] Field name field properties	-- Field properties can be modified, but field names cannot be modified (all original properties also need to be written)
    CHANGE [COLUMN] Original field name new field name field property		-- Field name modification is supported
    DROP PRIMARY KEY 				-- Delete the primary key (delete the primary key before deleting it) AUTO_INCREMENT Properties)
    DROP INDEX Index name				-- Delete index
    DROP FOREIGN KEY Foreign key				-- Delete foreign key

Delete table

DROP TABLE [IF EXISTS] Table name...

Clear table data

TRUNCATE [TABLE] Table name

Copy table structure

CREATE TABLE Table name LIKE Table name to copy

Copy table structure and data

CREATE TABLE Table name [AS] SELECT * FROM Table name to copy

Check the table for errors

CHECK TABLE tb_name [, tb_name] ... [option] ...

Optimization table

OPTIMIZE [LOCAL | NO_WRIT_TO_BINLOG] TABLE tb_name [, tb_name] ...

Repair table

REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb_name [, tb_name] ... [QUICK] [EXTENDED] [USE_FRM]

Analysis table

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb_name [, tb_name] ...

Data operation

increase

INSERT [INTO] Table name [(Field list)] VALUES (Value list) [, (Value list), ...]
-- If the list of values to insert contains all fields in the same order, the field list can be omitted
-- Multiple data records can be inserted at the same time
INSERT [INTO] Table name SET Field name=value[, Field name=value, ...]

Delete

DELETE FROM Table name [Delete conditional clause]
-- If there is no conditional clause, all will be deleted

change

UPDATE Table name SET Field name=New value[, Field name=New value] [update criteria]

check

SELECT Field list FROM Table name [Other clauses]

Character set encoding

Codes can be set for MySQL, database, table and field. The data code does not need to be consistent with the client code

View all character set encoding entries

SHOW VARIABLES LIKE 'character_set_%'
-- character_set_client			The encoding used by the client when sending data to the server
-- character_set_results		The encoding used by the server to return the result to the client
-- character_set_connection		Connection layer coding

Set character set encoding

SET character_set_client = gbk;
SET character_set_results = gbk;
SET character_set_connection = gbk;
SET NAMES GBK;		-- It is equivalent to completing the above three settings

Proofreading set

View all character sets:
SHOW CHARACTER SET [LIKE 'pattern'] or
SHOW CHARSET [LIKE 'pattern']

View all proofing sets:
SHOW COLLATION [LIKE 'pattern']
Set character set encoding:
CHARSET Character set encoding
 Set proofset encoding
COLLATE Proofreading set coding

Data type (column type)

value type

plastic

type	byte	Range (signed bit)
tinyint	1	-128 ~ 127 Unsigned bit: 0 ~ 255
smallint	2	-32768 ~ 32767
mediumint	3	-8388608 ~ 8388607
int	4
bigint	8

int(M) M represents the total number of digits

• The sign bit exists by default, and the unsigned attribute is modified

• Display width. If a certain number is not enough to define the number of digits set when the field is defined, fill 0 in the front and modify the zerofill property

  For example, int(5) inserts a number 123, which is 00123 after filling

• When the requirements are met, the smaller the better

• MySQL has no boolean type. tinyint(1) is often used to represent boolean type. 1 means bool value is true and 0 means bool value is false.

float

type	byte	Range
Float (single precision)	4
Double (double)	8

• Floating point type supports both unsigned attribute of sign bit and zerofill attribute of display width. Different from plastic surgery, 0 will be filled before and after

• When defining a floating-point type, you need to specify the total number of digits and the number of decimal places.

  float(M, D) double(M, D)

  M represents the total number of digits and D represents the number of decimal digits.

  The size of M and D determines the range of floating point numbers. Fixed range different from integer.

  Support scientific counting representation.

  Floating point numbers represent approximations.

Fixed point number

decimal variable length

decimal(M, D) M also represents the total number of digits and D represents the number of decimal digits.

Save an exact value without data change, which is different from the rounding of floating-point numbers.

Convert floating-point numbers into strings to save, and save every 9 digits as 4 bytes.

String type

char	varchar
Fixed length string, fast, but a waste of space	Variable length string, slow, but space saving
Up to 255 characters, regardless of encoding	Up to 65535 characters, related to encoding

blob	text
Binary string (byte string)	Non binary string (character string)
tinyblob, blob, mediumblob, longblob	tinytext, text, mediumtext, longtext
	When defining, the length does not need to be defined, and the total length will not be calculated default cannot be given when defining

Date time type

type	byte	storage	format	Range
datetime	8	Date and time	YYYY-MM-DD hh:mm:ss	1000-01-01 00:00:00 to 9999-12-31 23:59:59
date	3	date	YYYY-MM-DD YY-MM-DD YYYYMMDD YYMMDD	1000-01-01 to 9999-12-31
timestamp	4	time stamp	YY-MM-DD hh:mm:ss YYMMDDhhmmss YYYYMMDDhhmmss YYMMDDhhmmss	1970011000000 to 2038-01-19 03:14:07
time	3	time	hh:mm:ss hhmmss	-838:59:59 to 838:59:59
year	1	particular year	YYYY YY	1901 - 2155

Enumerations and collections

Enum (enum)

• enum(val1, val2, val3 ...)
• Make a single choice among known values. The maximum quantity is 65535
• When the enumeration value is saved, it is saved as a 2-byte integer (smallint). Each enumeration value is incremented from 1 in the order of the saved location.
• Expressed as a string type, but stored as an integer.
• Index of NULL value
• The index value of the empty string error value is 0

set

• set(val1, val2, val3 ...)
• create table tab (gender set('male ',' female ',' none ');
• insert into tab values ('male, female ');
• There can be up to 64 different members. It is stored in bigint, a total of 8 bytes, in the form of bit operation
• When creating a table, the trailing space of the SET member value will be automatically deleted.

Column properties (column constraints)

PRIMARY primary key

• Fields that uniquely identify records can be used as primary keys.

• A table can only have one primary key.

• The primary key is unique.

• When declaring a field, it is identified by the primary key. You can also declare after the field list. For example:

  create table tab (
      id int,
      stu varchar(10),
      primary key (id)
  );
  

• The value of the primary key field cannot be NULL

• A primary key can consist of multiple fields. For example:

  create table tab (
      id int,
      stu varchar(10),
      age int,
      primary key(stu, age)
  );
  

UNIQUE unique index (UNIQUE constraint)

So that the value of a field cannot be repeated

NULL constraint

• null is not a data type, but a property of a column.

• Indicates whether the front row can be null, indicating that there is nothing

• Null, null allowed, default. not null, cannot be null

DEFAULT default property

The default value for the current field.

insert into tab values (default, 'val');	-- This means that the default value is enforced.
create table tab (add_time timestamp default current_timestamp);	-- Indicates that the timestamp of the current time is set to the default value

AUTO_INCREMENT automatic growth constraint

• Auto growth must be an index (primary key or unique)

• Only one field can exist for automatic growth

• By default, it grows automatically from 1. You can use auto_increment = x to set

Comment on COMMET

FOREIGN KEY constraint

Used to limit the data integrity of master and slave tables

alter table t1 add constraint `t1_t2_fk` foreign key(t1_id) references t2(id);
-- Will table t1 of t1_id Foreign key association to table t2 of id field
-- Each foreign key has a name, which can be constraint appoint

• Tables with foreign keys are called slave tables / child tables, and tables pointed to by foreign keys are called master tables / parent tables
• Function: maintain data consistency and integrity. The main purpose is to control the data stored in the foreign key table
• Only the InnoDB engine supports foreign keys

Table building specification

Normal Format，NF

• Each table holds one entity information
• Each table has an ID field as the primary key
• ID primary key + atomic table

1NF, first paradigm

• Field cannot be subdivided

2NF, second paradigm

• Under the premise of meeting the first paradigm, partial dependence cannot occur
• Eliminating composite primary keys can avoid partial dependencies. Add single column keyword

3NF, third paradigm

• On the premise of meeting the second paradigm, transitive dependency cannot occur
• A field depends on the primary key, while other fields depend on it. This is called transitive dependency
• Put the data of an entity information in a table

SELECT

SELECT [ALL|DISTINCT] select_expr FROM -> WHERE -> GROUP BY [aggregate function ] ->HAVING -> ORDER BY -> LIMIT

• select_expr

  • You can use * to represent all fields.

    select * from tb;
    

  • You can use expressions (calculation formulas, function calls, and fields are also expressions)

    select stu, 29+25, now() from tb;
    

  • You can use aliases for each column. It is applicable to simplify column identification and avoid duplication of multiple identification columns

    • The AS keyword can be omitted

    select stu+10 as add10 from tb;
    

• FROM clause

  • Used to identify the query source

  • Tables can be aliased (AS)

    select * from tb1 as t1, tb2 as t2;
    

  • After the from clause, multiple tables can appear at the same time

    • Multiple tables will be stacked horizontally, and the data will form a Cartesian product

      select * from tb1, tb2;
      

  • Prompts the optimizer how to select an index

    USE INDEX,IGNORE INDEX,FORCE INDEX
    SELECT * FROM table1 USE INDEX (key1,key2) WHERE key1=1 AND key2=2 AND key3=3;
    SELECT * FROM table1 IGNORE INDEX (key1,key2) WHERE key1=1 AND key2=2 AND key3=3;
    

• WHERE clause

  • Filter from the data source obtained from
  • Type 1 means true and 0 means false
  • Expressions consist of operators and operands.
    • Operand: variable (field), value, function return value
    • Operators: =, < = >, < >,! =, < =, <, > =, >,!, & &, |, In (not) null, (not) like, (not) in, (not) between and, is (not), and, or, not, XOR, etc

• GROUP BY clause

  GROUP BY field / alias [sort by]

  Sorting is performed after grouping. Ascending: ASC. Descending: DESC

  To be used with GROUP BY:

  count,sum,max,min,avg,group_concat (returns a string result with a non NULL value of a connection from a group. String connection within a group)

• HAVING clause

  It has the same function and usage as where, and the execution time is different

  where performs detection data at the beginning and filters the original data

  having filters the filtered results again

  The having field must be queried, and the where field must exist in the data table

  Where can't use the alias of the field, having can. This is because the column value may not have been determined when the where code is executed.

  where cannot use aggregate function. Generally, having is used only when the total function is used

  The SQL standard requires that having must refer to a column in the group by clause or a column used in the aggregate function

• ORDER BY clause, sort clause

  order by sort field / alias sort by [, sort field / alias sort by...]

  Ascending: ASC; Descending: DESC

  Support sorting of multiple fields

• LIMIT clause, LIMIT the number of results clause

  Only limit the number of processed results. The processed results are regarded as a set, and the index starts from 0 according to the order in which the records appear. Limit start position, get the number of entries

  Omitting the first parameter means starting from index 0. limit gets the number of entries

• DISTINCT, ALL option

  distinct: remove duplicate records. All is the default, indicating all records

UNION

Combine the results of multiple select queries into a result set.

SELECT ... UNION [ALL|DISTINCT] SELECT ...

The default mode is DISTINCT, that is, the returned rows are unique

It is recommended that you add parentheses to each select query

When ORDER BY is sorted, it needs to be combined with limit.

The number of fields required for each select query is the same.

The field list (quantity and type) of each select query should be consistent, because the field name in the result is subject to the first select statement

Subquery

Subqueries need to be wrapped in parentheses

from type

After from, it is required to be a table, and the sub query result must be given an alias

Simplify conditions within each query

From type needs to generate a temporary table from the result, which can be released with the lock of the original table

The sub query returns a table. The sub query:

select * from(select * from tb where id > 1) as subfrom where id > 2;

where type

Subquery returns a value, scalar subquery

There is no need to alias the subquery

Tables in the where subquery cannot be updated directly

select * from tb where money = (select max(money) from tb);

Column subquery:

• If the subquery result returns a column.

• Use in or not in to complete the query

• Exists and not exists conditions

  • If the subquery returns data, 1 or 0 is returned. Often used to judge conditions

    select column1 from t1 where exists(select * from t2);
    

Row subquery

• The query condition is a row

  select * from t1 where (id, gender) in (select id, gender from t2);
  

  Line constructor: (col1, col2,...) or ROW(col1, col2,...)

  Row constructors are often used to compare with subqueries that can return two or more columns.

Special operator

! = all() is equivalent to not in

=some() is equivalent to in. any is an alias for some

! = some() is not equal to not in, not equal to one of them

all, some can be used with other operators

join query

/* Join query */ ------------------
    Connect the fields of multiple tables, and you can specify the connection conditions.
-- Inner connection(inner join)
    - The default is internal connection, which can be omitted inner. 
    - The connection can only be sent when the data exists. That is, there can be no empty lines in the connection result.
    on Represents a connection condition. Its condition expression is the same as where Similarly, you can omit the condition (indicating that the condition is always true)
    Also available where Indicates the connection condition.
    also using, But the field name should be the same. using(Field name)
    -- Cross connect cross join
        That is, there is no conditional inner connection.
        select * from tb1 cross join tb2;
-- External connection(outer join)
    - If the data does not exist, it will also appear in the connection results.
    -- Left outer connection left join
        If the data does not exist, the left table record will appear and the right table is null fill
    -- Right outer connection right join
        If the data does not exist, the right table record will appear, while the left table is null fill
-- Natural connection(natural join)
    Automatically judge the connection conditions and complete the connection.
    Equivalent to omitting using，The same field name is automatically found.
    natural join
    natural left join
    natural right join
select info.id, info.name, info.stu_num, extra_info.hobby, extra_info.sex from info, extra_info where info.stu_num = extra_info.stu_id;

TRUNCATE

/* TRUNCATE */ ------------------
TRUNCATE [TABLE] tbl_name
 wipe data 
Delete rebuild table
 difference:
1，truncate Delete the table and create it again, delete Delete item by item
2，truncate Reset auto_increment The value of the delete can't
3，truncate I don't know how many items were deleted, and delete Yes.
4，When used with partitioned tables, truncate The partition is preserved

Backup and restore

/* Backup and restore */ ------------------
Backup, save the data structure and the data in the table.
utilize mysqldump Command complete.
-- export
mysqldump [options] db_name [tables]
mysqldump [options] ---database DB1 [DB2 DB3...]
mysqldump [options] --all--database
1. Export a table
　　mysqldump -u user name -p Password library name table name > file name(D:/a.sql)
2. Export multiple tables
　　mysqldump -u user name -p Password library name table 1 Table 2 Table 3 > file name(D:/a.sql)
3. Export all tables
　　mysqldump -u user name -p Password library name > file name(D:/a.sql)
4. Export a library
　　mysqldump -u user name -p password --lock-all-tables --database Library name > file name(D:/a.sql)
sure-w carry WHERE condition
-- Import
1. Logging in mysql In case of:
　　source  Backup file
2. Without logging in
　　mysql -u user name -p Password library name < Backup file

view

What is a view:
    A view is a virtual table whose contents are defined by a query. Like a real table, a view contains a series of named columns and rows of data. However, views do not exist in the database as a set of stored data values. The table referenced by the query of the view is freely defined by row and column data, and is generated dynamically when the view is referenced.
    The view has a table structure file, but no data file exists.
    For the underlying table referenced in it, the view acts like a filter. Filters that define views can come from one or more tables in the current or other databases, or other views. There are no restrictions on queries through views, and there are few restrictions on data modification through them.
    Views are the of queries stored in a database sql Statement, which is mainly for two reasons: for security reasons, the view can hide some data, such as social insurance fund table. The view can only display the name and address, but not the social insurance number and salary. Another reason is that it can make complex queries easy to understand and use.
-- Create view
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement
    - The view name must be unique and cannot be the same as the table name.
    - Views can be used select Statement, or you can specify the corresponding column name yourself.
    - You can specify the algorithm to be executed by the view ALGORITHM appoint.
    - column_list If present, the number must be equal to SELECT The number of columns retrieved by the statement
-- View structure
    SHOW CREATE VIEW view_name
-- Delete view
    - After deleting the view, the data still exists.
    - You can delete multiple views at the same time.
    DROP VIEW [IF EXISTS] view_name ...
-- Modify view structure
    - Generally, the view is not modified because not all updated views are mapped to the table.
    ALTER VIEW view_name [(column_list)] AS select_statement
-- View function
    1. Simplify business logic
    2. Hide the real table structure from the client
-- View algorithm(ALGORITHM)
    MERGE       merge
        The query statements of the view and external queries need to be merged before execution!
    TEMPTABLE   cursor
        After the view is executed, a temporary table is formed, and then an external query is performed!
    UNDEFINED   Undefined(default)，refer to MySQL Choose the corresponding algorithm independently.

affair

A transaction is a logical set of operations. The units that make up this set of operations either succeed or fail.
    - Support continuous SQL Collective success or collective revocation.
    - Transaction is a function of database in terms of data integrity.
    - Need to use InnoDB or BDB The storage engine supports automatic submission.
    - InnoDB It is called transaction security engine.
-- Transaction on
    START TRANSACTION; perhaps BEGIN;
    After the transaction is started, all executed SQL Statements are considered to be within the current transaction SQL sentence.
-- Transaction commit
    COMMIT;
-- Transaction rollback
    ROLLBACK;
    If some operations have problems, they are mapped to before the transaction is started.
-- Characteristics of transactions
    1. Atomicity( Atomicity)
        A transaction is an inseparable unit of work. Operations in a transaction either occur or do not occur.
    2. Consistency( Consistency)
        Data integrity must be consistent before and after a transaction.
        - External data is consistent at the beginning and end of a transaction
        - Operations are continuous throughout the transaction
    3. Isolation( Isolation)
        When multiple users access the database concurrently, the transactions of one user shall not be disturbed by the transactions of other users, and the data between multiple concurrent transactions shall be isolated from each other.
    4. Persistence( Durability)
        Once a transaction is committed, its changes to the data in the database are permanent.
-- Implementation of transaction
    1. Table type supported by transaction is required
    2. Start a transaction before performing a set of related operations
    3. After the whole set of operations is completed, if they are successful, they are committed; if there are failures, select rollback, and they will return to the backup point at the beginning of the transaction.
-- Principle of transaction
    utilize InnoDB Automatic submission of(autocommit)Feature complete.
    ordinary MySQL After the statement is executed, the current data submission operation can be visible to other clients.
    The transaction is to temporarily turn off the "auto commit" mechanism, which requires commit Commit persistent data operation.
-- be careful
    1. Data definition language( DDL)Statements cannot be rolled back, such as statements that create or cancel a database, and statements that create, cancel, or change tables or stored subroutines.
    2. Transactions cannot be nested
-- Save point
    SAVEPOINT Save point name -- Set a transaction savepoint
    ROLLBACK TO SAVEPOINT Save point name -- Rollback to savepoint
    RELEASE SAVEPOINT Save point name -- Delete savepoint
-- InnoDB Auto submit property settings
    SET autocommit = 0|1;   0 Indicates that auto submit is turned off, and 1 indicates that auto submit is turned on.
    - If it is turned off, the results of normal operations are not visible to other clients, and you need to commit The data operation cannot be persisted until it is committed.
    - You can also turn off auto commit to start a transaction START TRANSACTION The difference is,
        SET autocommit Is to permanently change the settings of the server until the next time you modify the settings again.(For current connection)
        and START TRANSACTION Record the status before the transaction is started. Once the transaction is committed or rolled back, it is necessary to start the transaction again.(For the current transaction)

Lock table

/* Lock table */
Table locking is only used to prevent other clients from reading and writing improperly
MyISAM Table lock is supported, InnoDB Support row lock
-- locking
    LOCK TABLES tbl_name [AS alias]
-- Unlock
    UNLOCK TABLES

trigger

/* 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
    Parameters:
    trigger_time Is the action time of the trigger program. It can be before or after，To indicate that the trigger 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: Activate trigger when a line is changed
        DELETE: Activates the trigger when a row is deleted from the table
    tbl_name: The monitored table must be a permanent table, and the trigger program and TEMPORARY Associated with a table or view.
    trigger_stmt: The statement executed when the trigger program is activated. To execute multiple statements, you can use BEGIN...END Compound sentence structure
-- delete
DROP TRIGGER [schema_name.]trigger_name
 have access to old and new Replace old and new data
    Update operation, before update is old，Updated yes new.
    Delete operation, only old.
    Add operation, only new.
-- be careful
    1. For a given table with the same trigger action time and event, there cannot be two triggers.
-- Character concatenation function
concat(str1,str2,...])
concat_ws(separator,str1,str2,...)
-- 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 sentence
 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 triggers:
    If there is no duplicate record, it will be triggered before insert, after insert;
    If there is a duplicate record and it is updated, it will be triggered before insert, before update, after update;
    Triggered if there are duplicate records but no updates occur before insert, before update
3. Replace If the syntax is recorded, execute before insert, before delete, after delete, after insert

SQL programming

/* SQL programming */ ------------------
--// Local variable----------
-- Variable declaration
    declare var_name[,...] type [default value]
    This statement is used to declare a local variable. 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 default Clause with an initial value of null. 
-- assignment
    use set and select into Statement to assign a value to a variable.
    - Note: global variables (user-defined variables) can be used in functions
--// Global variable----------
-- Definition and assignment
set Statement can define and assign values to variables.
set @var = value;
You can also use select into Statement initializes and assigns values to variables. Such requirements select Statement can only return one row, but it can be multiple fields, which means that multiple variables are assigned at the same time, and the number of variables must be consistent with the number of columns queried.
You can also think of the assignment statement as an expression select Execution completed. At this time, in order to avoid=Treated as a relational operator, using:=Replace. ( set Statement can be used= and :=). 
select @var:=20;
select @v1:=id, @v2=name from t1 limit 1;
select * from tbl_name where @var:=30;
select into The data obtained from the query in the table can be assigned to variables.
    -| select max(height) into @max_height from tb;
-- Custom variable name
 for fear of select Statement, the user-defined variable conflicts with the system identifier (usually the field name), and the user-defined variable is used before the variable name@As the start symbol.
@var=10;
    - After the variable is defined, it is valid for the whole session cycle (login to exit)
--// Control structure----------
-- if sentence
if search_condition then
    statement_list   
[elseif search_condition then
    statement_list]
...
[else
    statement_list]
end if;
-- case sentence
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];
- If necessary, terminate early in the cycle while Loop, you need to use labels; labels need to appear in pairs.
    -- Exit loop
        Exit the entire cycle leave
        Exit current loop iterate
        Which loop to exit is determined by the exit tag
--// Built in function----------
-- Numerical function
abs(x)          -- absolute value abs(-10.9) = 10
format(x, d)    -- Format the millennial value format(1234567.456, 2) = 1,234,567.46
ceil(x)         -- Round up ceil(10.1) = 11
floor(x)        -- Round down floor (10.1) = 10
round(x)        -- Rounding off
mod(m, n)       -- m%n m mod n Remainder 10%3=1
pi()            -- Obtain pi
pow(m, n)       -- m^n
sqrt(x)         -- arithmetic square root 
rand()          -- random number
truncate(x, d)  -- intercept d Decimal place
-- 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 time stamp
from_unixtime();                -- Get time from timestamp
-- String function
length(string)          -- string Length, bytes
char_length(string)     -- string Number of characters
substring(str, position [,length])      -- from str of position start,take length Characters
replace(str ,search_str ,replace_str)   -- stay str of use replace_str replace search_str
instr(string ,substring)    -- return substring First in string Location in
concat(string [,...])   -- connection string 
charset(str)            -- Returns the string character set
lcase(string)           -- Convert to lowercase
left(string, length)    -- from string2 From left in length Characters
load_file(file_name)    -- Read content from file
locate(substring, string [,start_position]) -- with instr,However, the start position can be specified
lpad(string, length, pad)   -- Reuse pad Add in string start,Until the string length is length
ltrim(string)           -- Remove front-end spaces
repeat(string, count)   -- repeat count second
rpad(string, length, pad)   --stay str Later use pad supplement,Until the length is length
rtrim(string)           -- Remove back-end spaces
strcmp(string1 ,string2)    -- Compare two string sizes 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) RETURNS return 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 and can be used db_name.funciton_name Execute the database to which the current function belongs, otherwise it is the current database.
    - Parameter part, by"Parameter name"and"Parameter type"Composition. Multiple parameters are separated by commas.
    - The function body consists of multiple available mysql Statement, process control, variable declaration and other statements.
    - Multiple statements should use begin...end The statement block contains.
    - There must be return 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, custom function----------
-- definition
 A stored procedure is a piece of code (procedure) stored in a database sql form.
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 itself. call Execution.
-- 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，Indicates input type
OUT，Indicates output type
INOUT，Indicates mixed type
 Note that there is no return value.

stored procedure

/* stored procedure */ ------------------
A stored procedure is a collection of executable code. It is more business logic than a function.
Call: CALL Process name
-- be careful
- No return value.
- It can only be called separately and cannot be included in other statements
-- parameter
IN|OUT|INOUT Parameter name data type
IN      Input: in the calling process, input data into the parameters inside the procedure body
OUT     Output: returns the processed result of the procedure body to the client during the call
INOUT   Input / output: both input and output
-- grammar
CREATE PROCEDURE Process name (parameter list)
BEGIN
    Process body
END

User and rights management

/* User and rights management */ ------------------
-- root Password Reset 
1. stop it MySQL service
2.  [Linux] /usr/local/mysql/bin/safe_mysqld --skip-grant-tables &
    [Windows] mysqld --skip-grant-tables
3. use mysql;
4. UPDATE `user` SET PASSWORD=PASSWORD("password") WHERE `user` = "root";
5. FLUSH PRIVILEGES;
User information table: mysql.user
-- Refresh permissions
FLUSH PRIVILEGES;
-- Add user
CREATE USER user name IDENTIFIED BY [PASSWORD] password(character string)
    - Must have mysql Global of database CREATE USER Permission, or possession INSERT jurisdiction.
    - Only users can be created and cannot be granted permissions.
    - User name, note the quotation marks: for example: 'user_name'@'192.168.1.1'
    - Passwords also need quotation marks, and pure digital passwords also need quotation marks
    - To specify a password in plain text, ignore it PASSWORD Keyword. To specify the password as PASSWORD()The mixed value returned by the function must contain keywords PASSWORD
-- Rename User 
RENAME USER old_user TO new_user
-- Set password
SET PASSWORD = PASSWORD('password')  -- Set password for current user
SET PASSWORD FOR user name = PASSWORD('password') -- Sets the password for the specified user
-- delete user
DROP USER user name
-- Assign permissions/Add user
GRANT Permission list ON Table name TO user name [IDENTIFIED BY [PASSWORD] 'password']
    - all privileges Indicates all permissions
    - *.* All tables representing all libraries
    - Library name.The table name represents a table under a library
    GRANT ALL PRIVILEGES ON `pms`.* TO 'pms'@'%' IDENTIFIED BY 'pms0817';
-- View permissions
SHOW GRANTS FOR user name
    -- View current user permissions
    SHOW GRANTS; or SHOW GRANTS FOR CURRENT_USER; or SHOW GRANTS FOR CURRENT_USER();
-- revoking permission
REVOKE Permission list ON Table name FROM user name
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user name   -- Revoke all permissions
-- privilege level 
-- To use GRANT or REVOKE，You must have GRANT OPTION Permission, and you must use it for the permission you are granting or revoking.
Global level: Global permissions apply to all databases in a given server, mysql.user
    GRANT ALL ON *.*and REVOKE ALL ON *.*Only global permissions are granted and revoked.
Database level: database permissions apply to all targets in a given database, mysql.db, mysql.host
    GRANT ALL ON db_name.*and REVOKE ALL ON db_name.*Grant and revoke database permissions only.
Surface level: table permissions apply to all columns in a given table, mysql.talbes_priv
    GRANT ALL ON db_name.tbl_name and REVOKE ALL ON db_name.tbl_name Grant and revoke table permissions only.
Column level: column permissions apply to a single column in a given table, mysql.columns_priv
    When used REVOKE When, you must specify the same column as the authorized column.
-- Permission list
ALL [PRIVILEGES]    -- Set division GRANT OPTION All simple permissions except
ALTER   -- Allow use ALTER TABLE
ALTER ROUTINE   -- Change or cancel stored subroutines
CREATE  -- Allow use CREATE TABLE
CREATE ROUTINE  -- Create stored subroutines
CREATE TEMPORARY TABLES     -- Allow use CREATE TEMPORARY TABLE
CREATE USER     -- Allow use CREATE USER, DROP USER, RENAME USER and REVOKE ALL PRIVILEGES. 
CREATE VIEW     -- Allow use CREATE VIEW
DELETE  -- Allow use DELETE
DROP    -- Allow use DROP TABLE
EXECUTE     -- Allows the user to run stored subroutines
FILE    -- Allow use SELECT...INTO OUTFILE and LOAD DATA INFILE
INDEX   -- Allow use CREATE INDEX and DROP INDEX
INSERT  -- Allow use INSERT
LOCK TABLES     -- Allow you to have SELECT Table usage of permissions LOCK TABLES
PROCESS     -- Allow use SHOW FULL PROCESSLIST
REFERENCES  -- Not implemented
RELOAD  -- Allow use FLUSH
REPLICATION CLIENT  -- Allows the user to ask for the address of the secondary or primary server
REPLICATION SLAVE   -- For replicated secondary servers (reading binary log events from the primary server)
SELECT  -- Allow use SELECT
SHOW DATABASES  -- Show all databases
SHOW VIEW   -- Allow use SHOW CREATE VIEW
SHUTDOWN    -- Allow use mysqladmin shutdown
SUPER   -- Allow use CHANGE MASTER, KILL, PURGE MASTER LOGS and SET GLOBAL sentence, mysqladmin debug Command; allows you to connect (once), even if you have reached max_connections. 
UPDATE  -- Allow use UPDATE
USAGE   -- "Synonymous with "no permission"
GRANT OPTION    -- Permission granted

Table maintenance

/* Table maintenance */
-- Analyze and store the keyword distribution of the table
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE Table name ...
-- Check one or more tables for errors
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
-- Defragment data files
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

other

/* miscellaneous */ ------------------
1. Available backquotes(`)Wrap identifiers (database name, table name, field name, index, alias) to avoid duplicate names with keywords! Chinese can also be used as identifiers!
2. There is an option file for saving the current database in each library directory db.opt. 
3. notes:
    Single-Line Comments  # Note Content 
    multiline comment  /* Note Content  */
    Single-Line Comments  -- Note Content      (standard SQL Note style, double dash followed by a space character (space TAB,Line feed, etc.))
4. Pattern wildcard:
    _   Any single character
    %   Any number of characters, even zero characters
    Single quotation marks need to be escaped \'
5. CMD The statement terminator on the command line can be ";", "\G", "\g"，Only the displayed results are affected. Other places still end with semicolons. delimiter You can modify the statement terminator of the current conversation.
6. SQL Case insensitive
7. Clear existing statements:\c