Detailed introduction to a comprehensive summary of frequently used statements in MySql
- 黄舟Original
- 2017-03-21 13:24:041020browse
The following editor will bring you a comprehensive summary of MySql frequently used statements (a must-read article). The editor thinks it is quite good, so I will share it with you now and give it as a reference for everyone. Let’s follow the editor and take a look.
The knowledge points summarized below are all frequently used, and they are all useful information, so keep them in your collection.
/* Start MySQL */
net start mysql
/* Connect and disconnect from the server*/
mysql -h address-P port-u username-p password
/* Skip permission verification to log in to MySQL */
mysqld --skip-grant-tables
-- Change root password
Password encryption function password()
update mysql.user set password=password('root');
SHOW PROCESSLIST -- Show which threads are running
SHOW VARIABLES --
/* Database operation*/ ------------------
-- View Current database
select database();
-- Display current time, user name, database version
select now(), user(), version();
-- Create database
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 library option information
alter database library name option information
-- DeleteLibrary
drop database[ if exists] database name
Also delete the directory related to the database and its directory contents
/* Table operations*/ --- ---------------
-- Create table
create [temporary] table[ if not exists] [library name .]Table name (structure definition of the table)[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 when the session ends
For field definition:
Field name data type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [ UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']
- Table option
- character set
charset = charset_name
If the table is not set, use the database character set
- storage engine
Engine = Engine_name
Tables use different data structures when managing data. Different structures will lead to different processing methods and provided feature operations.
Common engines: InnoDB MyISAM Memory/Heap BDB Merge Example CSV MaxDB Archive
Different engines are used in Different ways are used when saving the structure and data of the table
used to be used to save the structure and data of the table. Log file
SHOW ENGINES -- Display the status information of the storage engine
SHOW ENGINE engine name {LOGS|STATUS} -- Display the log or status information of the storage engine
-- Data file directory
DATA DIRECTORY = 'Directory'
-- Index file directory
INDEX DIRECTORY = 'Directory'
-- Table comment
COMMENT = 'string'
-- Partition option
PARTITION BY .. . (See the manual for details)
-- View all tables
SHOW TABLES[ LIKE 'pattern']
SHOW TABLES FROM table name
-- View table organization SHOW CREATE TABLE table name ( More detailed information) DESC table name/DESCRIBE table name/EXPLAIN table name/SHOW COLUMNS FROM table name [LIKE 'PATTERN']
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
- -
Modify the table
--Modify the options of the table itself
Alter TABLE table name table options
EG: ALTER TABLE table name ENGINE=MYISAM;
--Restart the table Naming
RENAME TABLE original table name TO new table name
RENAME TABLE original table name TO database name.Table name (the table can be moved to another database)
-- RENAME can exchange two table names
- Modify the field of the table
Alter Table Table name operation name
- Operation name
ADD [Column] field name - Add field
After field
after the field name FIRST -- means adding it to the first
ADD PRIMARY KEY (field name) -- Create a primary key
ADD UNIQUE [index name] (field name) -- Create a unique index ADD INDEX [index name] (field name) - Create a normal index - -In the support of the field attributes, and the field name cannot be modified (all original attributes also need to be written)
Change [column] original field name new field name field attribute -support field name modification
Drop Primary Key --Delete the primary key (you need to delete its AUTO_INCREMENT attribute before deleting the primary key)
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 be copied
-- Copy table structure and data
CREATE TABLE table name [AS] SELECT * FROM table name to be copied
-- Check whether the table has errors
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
-- Optimize the table
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
-- Repair table
REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]
-- Analysis table
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
/* Data operation*/ -- ----------------
-- Add
INSERT [INTO] table name [(field list)] VALUES (value list) )[, (value list), ...]
-- If the value list to be inserted contains all fields and is in the same order, the field list can be omitted.
-- Multiple data records can be inserted at the same time!
REPLACE is exactly the same as INSERT and is interchangeable.
INSERT [INTO] table name SET field name = value [, field name = value, ...]
-- Check
SELECT field list FROM table name [other clauses]
-- Multiple fields from multiple tables
FROM table name [delete condition clause]
If there is no condition clause, all will be deleted
--Change UPDATE table name SET field name=new value[, field name=new value] [Update
Conditions]
/*Character set encoding*/ ------------------
-- Encoding can be set for MySQL, database, table, and field -- Data encoding and client encoding do not need to be consistent
SHOW VARIABLES LIKE 'character_set_%' -- View all character set encoding items
character_set_client The encoding used when the client sends data to the servercharacter_set_results The encoding used by the server to return results to the client
character_set_connection Connection layer encoding
SET
Variable name
= variable value
set character_set_client = gbk; set character_set_results = gbk; set character_set_connection = gbk;
SET NAMES GBK; -- Quite To complete the above three settings
-- Proofreading set
Proofreading set is used for sorting
SHOW COLLATION [LIKE 'pattern'] View all collation sets
charset character set encoding Set character set encoding collate Collation set encoding Set proofreading set encoding
/* Data type (column type) */ ------------------
1.
Numeric typeType Bytes Range (signed bit) int(M) M represents the total number of digits - There is a sign bit by default, unsigned attribute modification - Display width, if a certain number is not enough for the number of digits set when defining the field, fill in the front with 0, zerofill attribute modification Example: int(5) Insert a number '123', fill in followed by '00123' - The smaller the better if the requirements are met. - 1 means bool value is true, 0 means bool value is false. MySQL does not have a Boolean type, which is represented by integers 0 and 1. Tinyint(1) is commonly used to represent Boolean type. -- b. Floating point type---------- Type . #float (single precision) 4 bytes double (double precision) 8 bytes The floating point type supports both the sign bit unsigned attribute and the display width zerofill attribute. Different from the integer type, 0 will be padded before and after. When defining the 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 places. The size of M and D will determine the range of floating point numbers. Different from the fixed range of integers. M represents both the total number of digits (excluding decimal points and signs) and the display width (including all display symbols). Support scientific notation. Floating point numbers represent approximate values. -- c. Fixed point number----------decimal -- Variable length decimal(M, D) M also represents the total number of digits, and D represents the number of decimal places. Save an accurate value without changing the data, unlike the rounding of floating point numbers. Convert floating point numbers to to save, each 9-digit number is saved as 4 bytes. 2. String type-- a. char, varchar ---------- char Fixed-length string, fast, but wastes space varchar Variable-length string, slow, but saves space M represents the maximum length that can be stored, this length is characters Number, not number of bytes. Different encodings occupy different spaces. char, up to 255 characters, regardless of encoding. varchar, up to 65535 characters, related to encoding. The maximum length of a valid record cannot exceed 65535 bytes. utf8 has a maximum length of 21844 characters, gbk has a maximum length of 32766 characters, latin1 has a maximum length of 65532 characters varchar is variable length, and storage space needs to be used to save the length of varchar. If the data is less than 255 bytes, one byte is used to save the length, otherwise two bytes are needed to save it. The maximum effective length of a varchar is determined by the maximum line size and the character set used. The maximum effective length is 65532 bytes, because when varchar stores a string, the first byte is empty and there is no data, and then two bytes are needed to store the length of the string. So the effective length is 64432-1-2=65532 bytes. Example: If a table is defined as CREATE TABLE tb(c1 int, c2 char(30), c3 varchar(N)) charset=utf8; What is the maximum value of N? Answer: (65535-1-2-4-30*3)/3 -- b. blob, text ----------blob binary string (byte string) tinyblob, blob, mediumblob, longblob text non-binary string (character string) tinytext, text, mediumtext, longtext When defining text, the length does not need to be defined, and the total length will not be calculated. When defining the text type, you cannot give a default value c. binary, varbinary ----------Similar to char and varchar, used to save binary strings, that is, to save byte strings instead of character strings. char, varchar, text correspond to binary, varbinary, blob. 3. Date and time typeGenerally use integer Type save , because PHP can easily format the timestamp. datetime 8 bytes date and time 1000-01-01 00:00:00 to 9999-12-31 23:59:59 date 3 bytes date 1000-01-01 to 9999- 12-31 timestamp 4 bytes Timestamp 19700101000000 to 2038-01-19 03:14:07 time 3 bytes Time -838:59:59 to 838:59:59 year 1 byte Year 1901 - 2155 -- enum(val1, val2, val3...) bit operation When creating a table, trailing spaces in SET member values will be automatically removed. -- PHP perspective 2. Storage space should be as small as possible and processing efficiency is higher 1. Just store, available string 1) Conversion ip2long can be converted to an integer type, but there will be a problem of carrying symbols. Needs to be formatted as an unsigned integer. 2) MySQL function conversion (unsigned integer, UNSIGNED) INET_ATON('127.0.0.1') Convert IP to integerINET_NTOA(2130706433) Convert integer to IP constraints) */ -------- ---------- - When declaring a field, use primary key to identify it. Example: create table tab (id int, stu varchar(10), primary key (id)); 2. unique unique index (unique constraint ) so that the value of a certain field cannot be repeated. 3. Null constraint null is not a data type, but an attribute of the column. 4. default default value attribute The default value of the current field. ##5. auto_increment automatic growth constraint 6. comment Comment 7. foreign key foreign key constraint --Associate the t1_id foreign key of table t1 to the id field of table t2. Syntax: If on update or on delete is specified: When deleting or updating, there are several operations to choose from: /* Table creation specifications */ ------------------ -- Normal Format, NF /* select */ ------------------ select [all |distinct] select_expr from -> where -> group by [total function] -> having -> order by -> limit ##a. select_expr use using ## is used to identify the query —Multiple tables will be superimposed horizontally, and the data will form a Cartesian product. select * from tb1, tb2; -- The data source obtained from from Filter in. -- Integer 1 represents true, 0 represents false. . The following [total function] needs to be used with group by: count avg finds the average value The function and usage are the same as where, but the execution timing is different. f. order by clause, sorting clause order by sorting field/alias sorting method[, sorting field/ Alias sorting method]... g. limit clause, the clause that limits the number of results only limits the number of processed results. Treat the processed results as a collection, with the index starting from 0 in the order in which the records appear. h. distinct, all options Default is all, all records /* UNION */ ------------------ SELECT ... UNION [ALL|DISTINCT] SELECT ... /* Subquery*/ ------------------ --from type - Simplify the conditions within each query. --where type - No need to alias the subquery. -- column subquery Use in or not in to complete the query -- Row subquery != all() Equivalent to not in To connect the fields of multiple tables, you can specify the connection conditions. -- Cross join cross 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; /* Import and Export*/ ------------------ select * into outfile File address [control format] from table name; -- Export table data fields Control field format lines Control line format Default: lines terminated by '\n' select The data obtained by the statement can be inserted using insert. You can omit the specification of columns, requiring values () within brackets to provide values for all fields that appear in column order. You can use multiple values at one time, in the form of (), (), ();. You can use expressions when specifying column values. The result of a query can be used as the value to be inserted. You can specify to update the information of other non-primary key columns when the inserted value has a primary key (or unique index) conflict. /* delete */ ------------ ------ DELETE FROM tbl_name [WHERE where_definition] [ORDER BY ...] [LIMIT row_count] Delete according to conditions Specify the maximum number of records to delete. Limit Can be deleted by sorting conditions. order by + limit supports multi-table deletion, using similar connection syntax. /* truncate */ ------------------ TRUNCATE [TABLE] tbl_name Difference: 1, truncate is to delete the table and create it again , delete is to delete 2 one by one, truncate resets the value of auto_increment. And delete doesn't know how many items have been deleted by truncate, but delete does. 4, when used on a partitioned table, truncate will retain the partition Use the mysqldump command to complete. 2. Export multiple tables What is a view: databases, or from other views. There are no restrictions on querying through views, and there are few restrictions on data modification through them. For reasons, the view can hide some data, such as: Social Security Fund table , you can use the view to display only the name and address without displaying the social security number and salary number. Another reason is that it can make complex queries easy to understand and use. - The view can use the column names queried by the select statement, or you can specify the corresponding column names yourself. --View algorithm (ALGORITHM) TEMPTABLE Temporary table /* Transaction (transaction) */ ------------------ - A transaction refers to a logical set of operations. Each unit that makes up this set of operations will either succeed or fail. -- Transaction opening -- Characteristics of transactions -- Transaction implementation -- Principle of transaction -- Note -- Savepoint --InnoDB automatic commit feature setting -- Create trigger -- Delete You can use old and new to replace the old and new Data -- Note -- Branch statement --Modify the end symbol of the outermost statement delimiter ; end -- Special execution If there are duplicate records but no update occurs, trigger before insert, before update --// Local variables---- ------ 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. The set statement can define and assign values to variables. You can also use the select into statement to initialize and assign a value to a variable. This requires that the select statement can only return one row, but it can be multiple fields, which means that multiple variables are assigned values at the same time. The number of variables needs to be consistent with the number of columns in the query. @var=10; if search_condition then [elseif search_condition then statement_list - If you need to terminate the while loop early within the loop, you need to use labels; labels need to appear in pairs. -- Exit the loop - The function name should be a legal identifier and should not conflict with existing keywords. -- Delete -- View --Modify -- Create Parameter list: Different from the function parameter list, the parameter type needs to be specified Note, there is no return value. A stored procedure is a collection of executable codes. It prefers business logic to functions. -- Parameter -- Syntax User information table: mysql.user /* Miscellaneous*/ ------------------ 1. Backticks (`) can be used as identifiers (library names, table names , field name, index, alias) package to avoid duplicate names with keywords! Chinese can also be used as an identifier! 2. Each library directory contains an option file db.opt that saves the current database. 3. Comment: Single-line comment# Comment content 4. Pattern wildcard character : _ Any single character 5. The statement terminator in the CMD command line can be ";", "\G", "\g", which only affects the display result. Elsewhere, end with a semicolon. delimiter can modify the statement terminator of the current conversation. 6. SQL is not case sensitive 7. Clear existing statements:\c
tinyint 1 byte -128 ~ 127 Unsigned bit: 0 ~ 255
smallint 2 bytes -32768 ~ 32767
mediumint 3 bytes -8388608 ~ 8388607
int 4 bytes
bigint 8 bytes
datetime “YYYY-MM-DD hh:mm:ss”
timestamp “YY-MM-DD hh:mm:ss”
“YYYYMMDDhhmmss”
“YYMMDDhhmmss”
YYYYMMDDhhmmss
“YYYYMMDDhhmmss”
“YY-MM-DD”
“YYYYMMDD”
“ YYMMDD"
"YYYYMMDD
"YYMMDD
time "hh:mm:ss"
"hhmmss"
“YY”
YYYY
YY
set(val1, val2, val3 ...)
3. Consider compatibility issues
- can uniquely identify The field of the record can be used as the primary key. - A table can only have one primary key.
You can also declare it after the field list
- The value of the primary key field cannot be null.
- The primary key can be composed of multiple fields. At this time, a method declared after the field list is required.
Example: create table tab (id int, stu varchar(10), age int, primary key (stu, age));
Indicates whether the current column can be null, which means there is nothing.
null, allowed to be empty. default.
not null, empty is not allowed.
insert into tab values (null, 'val');
-- This means setting the value of the first field to null, depending on whether the field is allowed to be null
insert into tab values (default, 'val'); -- This means forcing to use the default value.
create table tab (add_time timestamp default current_timestamp);
-- means setting the timestamp of the current time as the default value.
current_date, current_time
-- Each foreign key has a name, which can be specified through constraint
- Each table saves one entity information
- Each has an ID field as a primary key
- ID primary key + atomic table
-- 1NF, First Normal Form
If the field cannot be further divided, it satisfies the first normal form.
-- 2NF, Second Normal Form
Under the premise of satisfying the first normal form, partial dependence cannot occur.
but the partial dependence can be avoided by eliminating the matching primary key. Add single column keywords.
-- 3NF, Third Normal Form
Under the premise of satisfying the second normal form, transitive dependencies cannot occur.
A certain field depends on the primary key, and other fields depend on this field. This is transitive dependency.
Put the data of an entity information in a table.
expressions
(calculation formulas, function calls, and fields are also expressions) ‐ ‐ ‐ ‐ select stu, 29+25, now() from tb; -- You can use aliases for each column. Suitable for simplifying column identification and avoiding duplication of multiple column identifiers.
-Use AS keywords, can also omit AS.
SELECT STU+10 ADD10 from TB;
source. --You can give the table an alias. Use the as keyword. select * from tb1 as tt, tb2 as bb;
c. where clause
-- The expression is composed of operator and operation
but ;, >=, >, !, &&, ||,
in (not) null, (not) like, (not) in, (not) between and, is (not), and, or, no, d. group by clause, grouping clause
group by field/alias[sort method]
Grouping Will be sorted later. Ascending order: ASC, descending order: DESC
Returns the number of different non-NULL values count(*), count(field) sum summation max finds the maximum value
group_concat returns a string result with non-NULL values from the concatenation of a group.
String concatenation
within the group.
e. having clause, conditional clause
Where executes the detection data at the beginning and filters the original data.
Having to filter the filtered results again.
The having field must be queried, and the where field must exist in the data table.
Where cannot use field aliases, but having can. Because when the WHERE code is executed, the column value may not have been determined yet.
Where the total function cannot be used. Generally, having
is used when an aggregate function is required. The SQL standard requires that HAVING must reference columns in the GROUP BY clause or columns used in the aggregate function.
Ascending order: ASC, Descending order: DESC
Supports sorting of multiple fields.
limit starting position, get the number of items
Omitting the first parameter means starting from index 0. limit Get the number of records
The default DISTINCT method, that is, all returned rows are unique
It is recommended to wrap each SELECT query in parentheses.
When sorting by ORDER BY, LIMIT needs to be added for combination.
It is required that the number of fields queried by each select is the same.
The field list (number, type) of each select query should be consistent, because the field names in the results are based on the first select statement.
- The from type needs to generate a temporary
table from the result, which can be used to release the lock of the original table. - Subquery returns a table, table type subquery.
select * from (select * from tb where id>0) as subfrom where id>1;
- The table in the where subquery cannot be updated directly.
select * from tb where money = (select max(money) from tb);
Exists and not exists conditions.
If the child query returns the data, return 1 or 0. Often used to determine conditions.
select column1 from t1 where exists (select * from t2);
# The constructor is usually used to compare with a sub -query that can return two or more columns.
= some() Equivalent to in. any is an alias of some
!= some() It is not equivalent to not in, not equal to one of them.
All, some can be used with other operators.
/* Join query (join) */ ------------------
-- Inner join(inner join)
- The default is inner join, inner can be omitted.
- The connection can only be sent if the data exists. That is, the connection result cannot contain blank lines.
on represents the connection condition. Its conditional expression is similar to where. You can also omit the condition (indicating that the condition is always true)
You can also use where to express the connection condition.
There is also using, but the field names must be the same. using(field name)
That is, an inner join without conditions.
select * from tb1 cross join tb2;
-- Outer join (outer join)
- If the data does not exist, it will also appear in the connection result.
-- Left outer join left join
If the data does not exist, the left table records will appear, and the right table will be filled with nulls
-- Right outer joinright join
If the data does not exist, the right table Records will appear, and the left table is filled with null
-- Natural join(natural join)
Automatically determine the connection conditions to complete the connection.
It is equivalent to omitting using, and the same field name will be automatically found.
natural join
natural left join
natural right join
load data [local] infile file address [replace|ignore] into table table name [control format]; -- Import data
The default delimiter for generated data is the tab character
If local is not specified, the data file must be on the server
The replace and ignore keyword control pairs Duplicate processing of existing unique key records
-- Control format
Default: fields terminated by '\t' enclosed by '' escaped by '\\'
terminated by 'string' -- Termination
enclosed by 'char' -- Package
escaped by 'char' -- Escape
-- Example:
SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table ;
terminated by 'string' -- terminate
/* insert */ ------------------
Or use set syntax.
insert into tbl_name set field=value,...；
insert into tbl_name values (), (), ();
insert into tbl_name values (field_value, 10+10, now());
You can use a special value default, which means that the column uses the default value.
insert into tbl_name values (field_value, default);
insert into tbl_name select ...;
insert into tbl_name values/set/select on duplicate key update field=value, …;
delete from needs to delete data from multiple tables 1 and 2 using table connection operation conditions.
Clear data
Delete and rebuild the table
/* Backup and restore*/ ------ ------------Backup, save the data structure and the data in the table.
1. Export a table
Mysqldump -u username -p password database name table 1 table 2 table 3 > file name (D:/a.sql)
3. Export all tables
Mysqldump -u username-p password library name> File name (D:/a.sql)
4. Export a library
mysqldump -u user name-p password-B library Name> File name (D:/a.sql)
Can carry backup conditions with -w
1 . When logging in to mysql:
2. When not logging in
#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 as stored sets of data values in the database. Row and column data come from the table referenced by the query that defines the view, and are 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 a view can come from one or more tables in the current or
The view is the sql statement of the query stored in the database. It is mainly for two reasons:
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement
- You can specify the algorithm executed by the view, specified through ALGORITHM.
- If column_list exists, the number must be equal to the number of columns retrieved by the SELECT statement
SHOW CREATE VIEW view_name
- After deleting the view, the data still exists.
- Multiple views can be deleted at the same time.
- Generally do not modify the view, because not all updates Views are mapped to tables.
1. Simplify business logic
MERGE Merge
After the view is executed, a temporary table is formed, and then the outer query is performed!
UNDEFINED Undefined (default) means that MySQL independently selects the corresponding algorithm.
- Supports collective success or collective revocation of continuous SQL.
- Transaction is a function of the database in data self-study.
- You need to use InnoDB or BDB storage engine to support the automatic submission feature.
- InnoDB is called a transaction-safe engine.
START TRANSACTION; or BEGIN;
After the transaction is opened, all executed SQL statements are recognized as SQL statements within the current transaction.
-- Transaction submission
COMMIT;
-- Transaction rollback
ROLLBACK;
If there is a problem with some operations, it will be mapped to before the transaction is started.
1. Atomicity
A transaction is an indivisible unit of work. Either all operations in the transaction occur or none occur.
2. Consistency
The integrity of the data before and after the transaction must be consistent.
-At the beginning and end of the transaction, the external data is consistent
-The throughout the transaction, the operation is continuous
3. isolation (isolation)
When multiple users send a database, a user Transactions cannot be interfered with by other users' transactions, and data between multiple concurrent transactions must be isolated from each other.
4. Durability
Once a transaction is committed, its changes to the data in the database are permanent.
1. The requirement is the table type supported by the transaction
2. Open the transaction before executing a set of related operations
3. After the entire set of operations is completed, all are successful , then commit; if there is a failure, choose to roll back, and you will return to the backup point where the transaction started.
It is completed by using the automatic submission (autocommit) feature of InnoDB.
After ordinary MySQL executes the statement, the current data submission operation can be visible to other clients.
The transaction temporarily turns off the "auto-commit" mechanism and requires commit to submit the persistent data operation.
1. Data definition language (DDL) statements cannot be rolled back, such as statements that create or cancel a database, and create, cancel, or change tables or storage Subroutine statements.
2. Transactions cannot be nested
SAVEPOINT Savepoint name--Set a transaction savepoint
ROLLBACK TO SAVEPOINT Savepoint name--Roll back to the savepoint
RELEASE SAVEPOINT Save point name--delete save point
SET autocommit = 0|1; 0 means to turn off automatic commit, 1 means to turn on automatic commit.
- If it is closed, the results of ordinary operations will not be visible to other clients, and a commit is required before the data operation can be persisted.
- You can also turn off automatic submission to open transactions. But unlike START TRANSACTION,
SET autocommit permanently changes the server settings until the settings are modified again next time. (For the current connection)
And START TRANSACTION records the state before opening, and once the transaction is committed or rolled back, the transaction needs to be opened again. (For the current transaction)
/* Lock table*/
Table locking is only used to prevent other clients from improperly reading and writing
MyISAM supports table locks, InnoDB supports Row Lock
-- Lock
LOCK TABLES tbl_name [AS alias]
-- Unlock
UNLOCK TABLES
/* Trigger*/ ------ ------------
The trigger is a named database object related to the table. When a specific event occurs in the table, the object will be activated
Listening: Addition, modification, deletion of records .
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 whether the trigger is fired before or after the statement that activates it.
trigger_event specifies 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 row is changed
DELETE: deletes a row from the table When activating the trigger program
tbl_name: The monitored table must be a permanent table. The trigger program cannot be associated with the TEMPORARY table or view.
trigger_stmt: statement executed when the trigger program is activated. To execute multiple statements, you can use the BEGIN...END compound statement structure
DROP TRIGGER [schema_name.]trigger_name
Update operation, old before update, new after update.
Delete operation, only old.
Add operation, only new.
1. There cannot be two triggers for a given table with the same trigger action time and event.
-- Character concatenation function
concat(str1[, str2,...])
if condition then
Execution statement
elseif condition then
Execution statement
else
Execution statement
end if;
delimiter Custom end symbol
SQL statement
Custom end symbol
1. As long as a record is added, the program will be triggered.
2. Insert into on duplicate key update syntax will trigger:
If there is no duplicate record, it will trigger before insert, after insert;
3. Replace syntax If there is a record, execute before insert, before delete, after delete, after insert
/* SQL
Programming
*/ ------------------
-- Variable declaration declare
-- Assignment Use set and select into statements to assign values to variables.
- Note: Global variables (user-defined variables) can be used within functions
--// Global variables----------
You can also regard the assignment statement as an expression and execute it through select. At this time, in order to avoid = being treated as a relational operator, use := instead. (The set statement can use = and :=).
select @var:=20;
select @v1:=id, @v2=name from t1 limit 1;
select * from tbl_name where @var:=30;
select into can assign the data obtained from the query in the table to a variable.
-| select max(height) into @max_height from tb;
-- Custom variable name
--// Control structure---- ------
statement_list]
...
[else
statement_list ]
end if;
-- case statement
CASE value WHEN [compare-value] THEN result
[WHEN [compare-value] THEN result ...]
[ ELSE result]
--
while loop
end while [end_label];
Exit the entire loop leave
Exit the current loop iterate
Determine which loop to exit through the exit label
--// Built-in function ----------
-- Numerical function
abs(x) —Absolute value abs(-10.9) = 10
format(x, d) -- Format the thousandths 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) -She Sa Wu Enter
Mod (M, N) - M%N m Mod N to find more 10%3 = 1
pi () - #Pow (m, n) - m^n
SQRT (x) - -the arithmetic square root
Rand () - Random number
Truncate (x, d) - intercept D position
##-- Time and 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 the date part
time('yyyy-mm-dd hh:ii:ss'); -- Get the time partdate_format
('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j'); -- Format time
unix_timestamp(); - Get unix timestamp
-- String function
length(string) -- String length, bytes
char_length(string ) -- The number of characters in string
substring(str, position [,length]) -- Starting from the position of str, take length characters
replace(str, search_str, replace_str) -- Used in str replace_str replaces search_str
instr(string,substring) -- Returns the position where substring first appears in string
concat(string [,...]) -- Concatenates the string
charset(str) -- Return the string character set
lcase(string) -- Convert to lowercase
left(string, length) -- Take length characters from the left in string2
load_file(file_name) -- Read from the file Get the content
locate(substring, string [,start_position]) -- Same as instr, but you can specify the starting position
lpad(string, length, pad) -- Repeat adding pad to the beginning of the string until the length of the string For length
# LTRIM (String) -Remove the front -end spaces
Repeat (String, Count) -Repeat the COUNT times
RPAD (String, LENGTH, PAD) - is length
rtrim(string) --Remove back-end spaces
-- Process function
case when [condition ] then result [when [condition] then result ...] [else result] end Multi-branch
-- Aggregation function
count()
sum();
max();
min();
avg();
-- Other commonly used functions
md5();
--// Storage function, Custom function
----------
-- New CREATE FUNCTION function_name (parameter list) RETURN
S Return value type
- A function should belong to a certain database. You can use the form of db_name.funciton_name to execute the database to which the current function belongs, otherwise it is the current database.
- The parameter part consists of "parameter name" and "parameter type". Multiple parameters are separated by commas.
- The function body consists of multiple available mysql statements, process control, variable declaration and other statements.
- Multiple statements should be included using begin...end statement blocks.
- There must be a return return value statement.
DROP FUNCTION [IF EXISTS] function_name;
SHOW FUNCTION STATUS LIKE 'partten'
SHOW CREATE FUNCTION function_name;
ALTER FUNCTION function_name function option
--// Stored procedure, custom function----------
-- Definition
A stored procedure is a piece of code (procedure) consisting of sql stored in the database.
A stored procedure is usually used to complete a piece of business logic, such as registration, shift payment, order warehousing, etc.
A function usually focuses on a certain function and is regarded as a service for other programs. It needs to call the function in other statements. However, the stored procedure cannot be called by other people and is executed by itself through call.
CREATE PROCEDURE sp_name (parameter list)
Process body
IN, represents input type
OUT, represents output type
INOUT, represents mixed type
/* Stored procedure */ ------------------
Call: CALL procedure name
-- Note
- There is no return value.
- Can only be called alone and cannot be mixed with other statements
IN|OUT|INOUT Parameter name Data type
IN Input: During the calling process, input the data To the parameters inside the process body
OUT Output: During the calling process, the result of the process body is returned to the client
INOUT Input and output: It can be input or output
CREATE PROCEDURE process name (parameter list)
BEGIN
Process body
END
/* User and Permission Management */ -- ----------------
-- Refresh permissions
FLUSH PRIVILEGES
-- Add user
CREATE USER Username IDENTIFIED BY [PASSWORD] Password (characters String)
- You must have the global CREATE USER permission of mysql database, or have the INSERT permission.
- Users can only be created, but permissions cannot be granted.
- User name, pay attention to the quotation marks: such as 'user_name'@'192.168.1.1'
- The password also needs quotation marks, and the pure numeric password also needs quotation marks
- To specify the password in plain text, ignore it PASSWORD keyword. To specify the password as the hashed value returned by the PASSWORD() function, include the keyword PASSWORD
-- Rename user
RENAME USER old_user TO new_user
-- Set password
SET PASSWORD = PASSWORD('password') -- Set a password for the current user
SET PASSWORD FOR username = PASSWORD('password') -- Set a password for the specified user
-- Delete the user
DROP USER username
-- Assign permissions/Add user
GRANT permission list ON table name TO user name [IDENTIFIED BY [PASSWORD] 'password']
- all privileges means all permissions
- *.* represents all tables in all libraries
- Library name. Table name represents a table under a certain library
--View permissions
SHOW GRANTS FOR username
--View current user permissions
SHOW GRANTS; or SHOW GRANTS FOR CURRENT_USER; or SHOW GRANTS FOR CURRENT_USER();
-- revoke permissions
REVOKE permission list ON table name FROM user name
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user name -- Revoke All Permissions
-- Permission Hierarchy
-- To use GRANT or REVOKE, you must have the GRANT OPTION permission, and you must be using 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 grant and revoke global permissions.
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.* only grant and revoke database permissions.
Table 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 only grant and revoke table permissions.
Column level: Column permissions apply to a single column in a given table, mysql.columns_priv
When using REVOKE, you must specify the same column as the authorized column.
-- Permission list
ALL [PRIVILEGES] -- Set all simple permissions except GRANT OPTION
ALTER -- Allow the use of ALTER TABLE
ALTER ROUTINE -- Change or cancel stored subtitles Procedure
CREATE and and and USER, RENAME USER and REVOKE ALL PRIVILEGES.
CREATE VIEW -- Allow the use of CREATE VIEW
DELETE -- Allow the use of DELETE
DROP -- Allow the use of DROP TABLE
EXECUTE -- Allow the user to run a stored subroutine
FILE -- Allow the use of SELECT...INTO OUTFILE and LOAD DATA INFILE
INDEX -- Allow the use of CREATE INDEX and DROP INDEX
INSERT -- Allow the use of INSERT
LOCK TABLES -- Allow the use of tables for which you have SELECT permissions LOCK TABLES
PROCESS -- Allow the use of SHOW FULL PROCESSLIST
REFERENCES -- Not implemented
RELOAD -- Allow the use of FLUSH
REPLICATION CLIENT -- Allow the user to ask for the address of a slave or master server
REPLICATION SLAVE -- For replication slave servers (read binary log events from the master server)
SELECT -- Allowed to use SELECT
SHOW DATABASES -- Show all databases
SHOW VIEW -- Allowed to be used SHOW CREATE VIEW
SHUTDOWN -- Allow
to use mysql
admin shutdown
SUPER -- Allow to use CHANGE MASTER, KILL, PURGE MASTER LOGS and SET GLOBAL statements, mysqladmin debug command ;Allows you to connect (once) even if max_connections has been reached.
UPDATE -- Allow the use of UPDATE
USAGE -- Synonyms for "no permissions"
GRANT OPTION -- Allow permissions to be granted
/* Table maintenance*/
--Analysis and Keyword distribution of storage 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 the data files
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
Multi-line comment/* Comment content*/
Single-line comment--Comment content (Standard SQL comment style, double Add a space after the dash (space, TAB, newline, etc.))
% Any multiple characters , even including the zero character
Single quotation marks need to be escaped\'
The above is the detailed content of Detailed introduction to a comprehensive summary of frequently used statements in MySql. For more information, please follow other related articles on the PHP Chinese website!