|
The knowledge points summarized below are all frequently used and are all valuable information. Please collect them well. /* Start MySQL */
net start mysql /* Connect and disconnect from server*/
mysql -h address-P port-u username-p password /* Skip permission verification to log in to MySQL */
mysqld --skip-grant-tables
-- Modify the root password password encryption function password()
update mysql.user set password=password('root'); SHOW PROCESSLIST -- show which threads are running
SHOW VARIABLES -- /* Database operations */ ------------------
-- View the current database
select database();
-- Display current time, user name, 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 library option information
alter database library name option information
-- Delete library
drop database[ if exists] database name
At the same time, delete the directory and its contents related to the database /* Table operations */ ------------------
-- Create table
create [temporary] table[ if not exists] [database 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 table, which disappears automatically when the session ends
For the definition of the fields:
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
The table uses different data structures when managing data. Different structures will lead to different processing methods, feature operations provided, etc.
Common engines: InnoDB MyISAM Memory/Heap BDB Merge Example CSV MaxDB Archive
Different engines use different methods to save table structure and data.
MyISAM table file meaning: .frm table definition, .MYD table data, .MYI table index
InnoDB table file meaning: .frm table definition, tablespace data and log files
SHOW ENGINES -- Display status information about storage engines
SHOW ENGINE engine name {LOGS|STATUS} -- Displays log or status information of the storage engine
--Data file directory
DATA DIRECTORY = 'Directory'
-- Index file directory
INDEX DIRECTORY = 'Directory'
-- Table comments
COMMENT = 'string'
-- Partition options
PARTITION BY ... (see manual for details)
-- View all tables
SHOW TABLES[ LIKE 'pattern']
SHOW TABLES FROM table name
-- View table structure
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 table
-- Options to modify the table itself
ALTER TABLE table-name table options
EG: ALTER TABLE table name ENGINE=MYISAM;
-- Rename the table
RENAME TABLE original table name TO new table name
RENAME TABLE original table name TO database name.table name (you can move the table to another database)
-- RENAME can exchange two table names
-- Modify the field structure of the table
ALTER TABLE table name operation name
-- Operation name
ADD[COLUMN] field name -- add a field
AFTER field name -- indicates adding after the field name
FIRST -- indicates adding 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
ADD
DROP[ COLUMN] field name -- delete a field
MODIFY[COLUMN] field name field attributes -- Supports modification of field attributes, but cannot modify field names (all original attributes must also be written)
CHANGE[COLUMN] original field name new field name field attributes -- support for modifying field names
DROP PRIMARY KEY -- Delete the primary key (before deleting the primary key, you need to delete its AUTO_INCREMENT attribute)
DROP INDEX index-name -- delete an index
DROP FOREIGN KEY -- delete a 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 the table name to be copied
-- Copy table structure and data
CREATE TABLE table name [AS] SELECT * FROM table name to be copied
-- Check the table for errors
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
-- Optimize 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 */ ------------------ -- Increase
INSERT [INTO] table name [(field list)] VALUES (value list) [, (value list), ...]
-- If the list of values 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 can come from multiple tables
-- Other clauses can be omitted
-- The field list can be replaced by * to indicate all fields
-- Delete
DELETE FROM table name [delete condition clause]
Without a conditional clause, all
-- change
UPDATE table name SET field name = new value [, field name = new value] [update condition] /* Character set encoding */ ------------------ -- MySQL, database, table, and field can all be encoded -- Data encoding does not need to be consistent with client encoding SHOW VARIABLES LIKE 'character_set_%' -- View all character set encoding items
character_set_client The encoding used by the client to send data to the server
character_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; -- equivalent to completing the above three settings -- Proofreading set Collation set for sorting
SHOW CHARACTER SET [LIKE 'pattern']/SHOW CHARSET [LIKE 'pattern'] View all character sets
SHOW COLLATION [LIKE 'pattern'] View all collation sets
charset character set encoding Set character set encoding
collate Collation set encoding Set the collation set encoding /* Data type (column type) */ ------------------ 1. Numeric Types -- a. Integer--------- Type Byte Range (signed bit)
tinyint 1 byte -128 ~ 127 Unsigned bits: 0 ~ 255
smallint 2 bytes -32768 ~ 32767
mediumint 3 bytes -8388608 ~ 8388607
int 4 bytes
bigint 8 bytes int(M) M represents the total number of digits - By default, there is a sign bit, unsigned attribute modification - Display width. If a number is not enough for the number of digits set when defining the field, it will be filled with 0s. Zerofill attribute modification Example: int(5) inserts a number '123', and after filling it is '00123' - The smaller the better, provided the requirements are met. - 1 represents a bool value of true, 0 represents a bool value of false. MySQL does not have a Boolean type and is represented by integers 0 and 1. Tinyint(1) is often used to represent Boolean types. -- b. Floating point type---------- Type Bytes Range float (single precision) 4 bytes double (double precision) 8 bytes Floating point types support both the unsigned attribute and the zerofill attribute. Unlike integers, 0s are filled in front and behind. 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 places. The size of M and D determines the range of floating point numbers. Different from the fixed range of integer types. M represents both the total number of digits (excluding the decimal point and positive and negative signs) and the display width (including all displayed symbols). Supports 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. Saves an exact value without changing the data, unlike the rounding of floating point numbers. Convert the floating point number to a string to store it, with each 9 digits stored 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 the number of characters, not the number of bytes. Different codes occupy different spaces. char, up to 255 characters, regardless of encoding. varchar, up to 65535 characters, depending on the encoding. The maximum length of a valid record cannot exceed 65535 bytes. The maximum length of a utf8 string is 21844 characters, the maximum length of a gbk string is 32766 characters, and the maximum length of a latin1 string is 65532 characters. VARCHAR is variable length and requires storage space to store the length of VARCHAR. If the data is less than 255 bytes, one byte is used to store the length, otherwise two bytes are required to store it. The maximum effective length of varchar is determined by the maximum row size and the character set used. The maximum valid length is 65532 bytes, because when a varchar stores a string, the first byte is empty and does not contain any data, and then two bytes are required to store the length of the string, so the effective length is 64432-1-2=65532 bytes. For 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, there is no need to define the length, and the total length will not be calculated. When defining a text type, you cannot give it a default value. -- c. binary, varbinary ---------- Similar to char and varchar, it is used to store binary strings, that is, to store byte strings instead of character strings. char, varchar, text correspond to binary, varbinary, blob. 3. Date and time types Generally, integers are used to save timestamps, because PHP can easily format timestamps. 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
datetime "YYYY-MM-DD hh:mm:ss"
timestamp "YY-MM-DD hh:mm:ss"
"YYYYMMDDhhmmss"
"YYMMDDhhmmss"
YYYYMMDDhhmmss
YYMMDDhhmmss
date "YYYY-MM-DD"
"YY-MM-DD"
"YYYYMMDD"
"YYMMDD"
YYYYMMDD
YYMMDD
time "hh:mm:ss"
"hhmmss"
hhmmss
year "YYYY"
"YY"
YYYY
YY 4. Enumerations and Sets --enum ----------- enum(val1, val2, val3...) Select a single value from the known values. The maximum number is 65535. When the enumeration value is saved, it is saved as a 2-byte integer (smallint). Each enumeration value is incremented one by one starting from 1 in the order of the saved position. It is represented as a string type, but is stored as an integer type. The index of a NULL value is NULL. The index value of the empty string error value is 0. -- Set ----------
set(val1, val2, val3...) create table tab ( gender set('男', '女', 'None') ); insert into tab values ('男, 女'); There can be up to 64 different members. Stored as bigint, 8 bytes in total. Takes the form of bitwise operations. When creating a table, trailing spaces in SET member values are automatically removed. /*Select type*/
-- PHP perspective
1. Functional Satisfaction
2. Minimize storage space and improve processing efficiency
3. Consider compatibility issues -- IP Storage ----------- 1. Only need to store, string can be used 2. If calculation, search, etc. are required, it can be stored as a 4-byte unsigned int, i.e. unsigned 1) PHP function conversion ip2long can be converted to an integer, but there will be a sign-carrying problem. Need to be formatted as an unsigned integer. Formatting a string using the sprintf function sprintf("%u", ip2long('192.168.3.134')); Then use long2ip to convert the integer back to an IP string 2) MySQL function conversion (unsigned integer, UNSIGNED) INET_ATON('127.0.0.1') Convert IP to integer
INET_NTOA(2130706433) Convert integer to IP
/* Column attributes (column constraints) */ ------------------ 1. Primary Key
- A field that can uniquely identify a record and can be used as a primary key.
- A table can have only one primary key.
- The primary key is unique.
- When declaring a field, use the primary key to identify it.
It can also be declared after the field list 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 be composed of multiple fields. In this case, the method needs to be declared after the field list.
Example: create table tab ( id int, stu varchar(10), age int, primary key (stu, age)); 2. unique index (unique constraint)
This prevents the value of a field from being repeated.
3. Null Constraints NULL is not a data type, but a property of a column.
Indicates whether the current column can be null, indicating nothing.
null, empty is allowed. default.
not null, not allowed to be empty.
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
4. default default value attribute The default value for the current field.
insert into tab values (default, 'val'); -- This means that the default value is forced to be used.
create table tab ( add_time timestamp default current_timestamp );
-- means setting the current timestamp as the default value.
current_date, current_time 5. auto_increment automatic growth constraint The auto-increment must be an index (primary key or unique) Only one field can be auto-incremented. The default value is 1 to start automatic growth. You can set it by using the table attribute auto_increment = x, or by using alter table tbl auto_increment = x; 6. Comment Example: create table tab (id int) comment 'Comment content'; 7. Foreign key constraints Used to limit the data integrity of the master table and the slave table. alter table t1 add constraint `t1_t2_fk` foreign key (t1_id) references t2(id);
-- Associate the t1_id foreign key of table t1 to the id field of table t2.
-- Each foreign key has a name, which can be specified through a constraint The table with foreign keys is called a slave table (child table), and the table pointed to by the foreign keys is called a master table (parent table). Function: Maintain data consistency and integrity. The main purpose is to control the data stored in the foreign key table (from table). In MySQL, you can use foreign key constraints with the InnoDB engine: grammar: foreign key (foreign key field) references main table name (related field) [action when main table record is deleted] [action when main table record is updated] At this time, it is necessary to detect that a foreign key of a secondary table needs to be constrained to an existing value of the primary table. A foreign key can be set to null when there is no association, provided that the foreign key column does not have not null. You do not need to specify the action when the master table record is changed or updated, in which case the operation on the master table is rejected. If on update or on delete is specified: When deleting or updating, you can choose from the following operations: 1. cascade, cascade operation. The master table data is updated (primary key value is updated), and the slave table is also updated (foreign key value is updated). The master table record is deleted, and the related records in the slave table are also deleted. 2. set null, set to null. The primary table data is updated (the primary key value is updated), and the foreign key of the secondary table is set to null. The master table record is deleted and the foreign key of the related record in the slave table is set to null. But note that the foreign key column is required to have no not null attribute constraint. 3. restrict, reject parent table deletion and update. Note that foreign keys are only supported by the InnoDB storage engine. Other engines are not supported. /* Table creation specifications*/ ------------------
-- Normal Format, NF
- Each table stores an entity information
- Each has an ID field as the primary key
- ID primary key + atomic table
-- 1NF, First Normal Form
If the field cannot be divided any further, the first normal form is satisfied.
-- 2NF, Second Normal Form
Under the premise of satisfying the first normal form, no partial dependency can occur.
Partial dependencies can be avoided by eliminating the primary key. Add single column keywords.
-- 3NF, the third normal form
Under the premise of satisfying the second paradigm, no transitive dependencies can occur.
A field depends on the primary key, and other fields depend on it. This is a transitive dependency.
The data of an entity information is placed in a table. /* select */ ------------------ select [all|distinct] select_expr from -> where -> group by [aggregate function] -> having -> order by -> limit a. select_expr
-- You can use * to indicate all fields.
select * from tb;
-- Expressions can be used (calculation formulas, function calls, and fields are also expressions)
select stu, 29+25, now() from tb;
-- You can use aliases for each column. Useful for simplifying column identification and avoiding duplication of multiple column identifiers.
- Use the as keyword, or omit as.
select stu+10 as add10 from tb; b. from clause
Used to identify the source of the query.
-- You can give a table an alias. Use the as keyword.
select * from tb1 as tt, tb2 as bb;
-- Multiple tables can appear after the from clause.
-- Multiple tables are stacked together horizontally, and the data forms a Cartesian product.
select * from tb1, tb2; c. where clause
-- Filter from the data source obtained from.
-- Integer 1 represents true, 0 represents false.
-- An expression consists of operators and operands.
-- Operands: variables (fields), values, function return values
-- Operator:
=, <=>, <>, !=, <=, <, >=, >, !, &&, ||,
in (not) null, (not) like, (not) in, (not) between and, is (not), and, or, not, xor
is/is not plus true/false/unknown to test the truth of a value
<=> has the same function as <>, and <=> can be used for null comparison d. group by clause, grouping clause
group by field/alias [sort by]
After grouping, they will be sorted. Ascending: ASC, Descending: DESC
The following aggregate functions must be used with group by:
count Returns the number of distinct non-NULL values count(*), count(field)
sum
max Find the maximum value
min Find the minimum value
avg average value
group_concat returns a string result with the concatenated non-NULL values from a group. Intra-group string concatenation. e. having clause, conditional clause
It has the same function and usage as where, but the execution timing is different.
where executes the 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 cannot use field aliases, but having can. Because when the WHERE code is executed, the column value may not have been determined yet.
Where aggregate functions cannot be used. Generally, having is used only when an aggregate function is needed.
The SQL standard requires that HAVING must reference columns in the GROUP BY clause or columns used in an aggregate function. f. order by clause, sorting clause
order by sort field/alias sort method [, sort field/alias sort method]...
Ascending: ASC, Descending: DESC
Supports sorting on multiple fields. g. limit clause, limit the number of results clause
Only the processed results are limited in number. Treat the processed results as a set, in the order in which the records appear, with the index starting from 0.
limit starting position, get the number of entries
Omitting the first parameter means starting from index 0. limit Get the number of entries h. distinct, all options
distinct removes duplicate records
The default is all, all records /* UNION */ ------------------
Combines the results of multiple select queries into a single result set.
SELECT ... UNION [ALL|DISTINCT] SELECT ...
The default DISTINCT mode means that all returned rows are unique.
It is recommended to enclose each SELECT query in parentheses.
When ORDER BY is used for sorting, LIMIT must be added for combination.
The number of fields in each select query must be the same.
The field list (number, type) of each select query should be consistent, because the field names in the result are based on the first select statement. /* Subquery */ ------------------
- Subqueries must be enclosed in parentheses.
-- from type
The from keyword must be followed by a table, and the subquery result must be given an alias.
- Simplify the conditions within each query.
- The from type needs to generate a temporary table with the result, which can be used to release the lock of the original table.
- The subquery returns a table, table type subquery.
select * from (select * from tb where id>0) as subfrom where id>1;
-- where type
- The subquery returns a value, a scalar subquery.
- No need to give subqueries aliases.
- The table in the where subquery cannot be directly updated.
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
Returns 1 or 0 if the subquery returns data. Often used to judge conditions.
select column1 from t1 where exists (select * from t2);
-- Row subquery
The query condition is one row.
select * from t1 where (id, gender) in (select id, gender from t2);
Row constructor: (col1, col2, ...) or ROW(col1, col2, ...)
Row constructors are often used to compare subqueries that return two or more columns. -- Special operators
!= all() is equivalent to not in
= some() is equivalent to in. any is an alias for some
!= some() is not equivalent to not in, and is not equal to one of them.
all, some can be used with other operators.
/* Join query (join) */ ------------------
You can connect fields from multiple tables and specify connection conditions.
-- Inner join
- The default is inner join, inner can be omitted.
- A connection can only be sent if data exists. That is, there cannot be blank lines in the connection result.
on indicates the connection condition. Its conditional expression is similar to where. You can also omit the condition (which means it is always true)
You can also use where to express the join condition.
There is also using, but the field names must be the same. using(field name) -- Cross join
That is, there is no conditional inner join.
select * from tb1 cross join tb2;
-- Outer join
- If the data does not exist, it will also appear in the connection results.
--Left outer join
If the data does not exist, the left table record will appear, and the right table will be filled with null
--Right outer join
If the data does not exist, the right table record will appear, and the left table will be filled with null.
-- Natural join
Automatically determine the connection conditions and complete the connection.
This is equivalent to omitting using, and the same field name will be automatically searched.
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; /* Import and export */ ------------------
select * into outfile file address [control format] from table name; -- export table data
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 keywords control the handling of duplicates of existing unique key records.
-- Control format
fields controls the format of fields
Default: fields terminated by '\t' enclosed by '' escaped by '\\'
terminated by 'string'
enclosed by 'char'
escaped by 'char'
-- Example:
SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;
lines controls line format
Default: lines terminated by '\n'
terminated by 'string'
/* insert */ ------------------
The data obtained by the select statement can be inserted using insert. The column specification can be omitted, and the required values () brackets provide the values of all fields in column order.
Or use the set syntax.
insert into tbl_name set field=value,...; Multiple values can be used at once in the form of (), (), ();.
insert into tbl_name values (), (), (); You can use expressions when specifying column values.
insert into tbl_name values (field_value, 10+10, now());
A special value, default, can be used to indicate that the column uses the default value.
insert into tbl_name values (field_value, default); You can use the result of a query as the value to be inserted.
insert into tbl_name select ...; You can specify that when an inserted value conflicts with the primary key (or unique index), the information of other non-primary key columns is updated.
insert into tbl_name values/set/select on duplicate key update field=value, …; /* delete */ ------------------
DELETE FROM tbl_name [WHERE where_definition] [ORDER BY ...] [LIMIT row_count] Delete by condition Specify the maximum number of records to delete. Limit Can be deleted by sorting conditions. order by + limit Supports multi-table deletion, using similar join syntax.
delete from needs to delete data from multiple tables 1 and 2 using table join operation conditions. /* truncate */ ------------------
TRUNCATE [TABLE] tbl_name
Clear data, delete and rebuild the table the difference: 1. truncate is to delete the table and then create it, delete is to delete one by one 2. truncate resets the value of auto_increment. And delete will not 3. truncate does not know how many records are deleted, but delete knows. 4. When used on a partitioned table, truncate will retain the partitions.
/* Backup and Restore */ ------------------
Backup, save the data structure and data in the table. This is done using the mysqldump command. -- Export
1. Export a table mysqldump -u username -p password database name table name > file name (D:/a.sql)
2. Export multiple tables 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 database name > file name (D:/a.sql)
4. Export a library mysqldump -u username -p password -B library name > file name (D:/a.sql) You can use -w to carry backup conditions -- Import
1. When logging into MySQL:
source backup file
2. Without logging in, mysql -u username -p password database 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 consists of a set of named columns and rows of data. However, a view does not exist as a stored set of data values in the database. The row and column data comes from the tables referenced by the query that defines the view and is generated dynamically when the view is referenced.
A view has a table structure file but no data file.
A view acts like a filter on the underlying tables referenced in it. The filters that define a view can come from one or more tables in the current or other databases, or from other views. There are no restrictions on querying through views, and there are few restrictions on modifying data through them.
A view is a SQL statement of a query stored in a database. It is used for two main reasons: security reasons. Views can hide some data, such as the social insurance fund table, which can display only the name and address without displaying the social insurance number and salary, etc. Another reason is that it can make complex queries easy to understand and use. -- Create a view
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement
- The view name must be unique and cannot have the same name as a table.
- The view can use the column names queried by the select statement, or specify the corresponding column names yourself.
- You can specify the algorithm to be executed by the view by specifying ALGORITHM.
- column_list, if present, must be equal to the number of columns retrieved by the SELECT statement -- View structure
SHOW CREATE VIEW view_name -- Delete the view
- After deleting the view, the data still exists.
- Multiple views can be deleted at the same time.
DROP VIEW [IF EXISTS] view_name ... -- Modify the view structure
- Views are generally not modified because not all updated views are mapped to tables.
ALTER VIEW view_name [(column_list)] AS select_statement -- View function
1. Simplify business logic
2. Hide the actual table structure from the client -- View algorithm (ALGORITHM)
MERGE
The query statement of the view needs to be merged with the external query before execution!
TEMPTABLE
After the view is executed, a temporary table is formed and then an outer query is performed!
UNDEFINED Undefined (default), which means that MySQL chooses the corresponding algorithm independently. /* transaction */ ------------------
A transaction refers to a logical group of operations. Each unit that makes up this group of operations must either succeed or fail.
- Supports collective success or collective cancellation of consecutive SQL statements.
- Transaction is a function of database in data management.
- Need to use InnoDB or BDB storage engine to support auto-commit feature.
- InnoDB is known as a transaction-safe engine. -- Transaction start
START TRANSACTION; or BEGIN;
After a transaction is opened, all executed SQL statements are considered SQL statements within the current transaction.
-- Transaction commit
COMMIT;
-- Transaction rollback
ROLLBACK;
If a problem occurs in some operations, it is mapped to before the transaction is started. -- Transaction characteristics
1. Atomicity
A transaction is an indivisible unit of work. Either all operations in a transaction occur or none of them occur.
2. Consistency
The integrity of the data before and after the transaction must remain consistent.
- External data is consistent when a transaction starts and ends
- Operations are continuous throughout the transaction
3. Isolation
When multiple users access the database concurrently, the transaction of one user cannot be interfered with by other users, and the 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. -- Transaction implementation
1. The table type must be supported by the transaction
2. Start a transaction before performing a set of related operations
3. After the entire set of operations is completed, if they are all successful, commit; if there is a failure, choose to roll back, and the transaction will return to the backup point at the beginning. -- The principle of transactions
This is done using the autocommit feature of InnoDB.
After a normal MySQL statement is executed, the current data submission operation can be seen by other clients.
Transactions temporarily turn off the "auto commit" mechanism and require commit to persist data operations. -- Notice
1. Data Definition Language (DDL) statements cannot be rolled back, such as statements that create or drop a database, and statements that create, drop, or modify a table or stored procedure.
2. Transactions cannot be nested -- Savepoint
SAVEPOINT savepoint name -- set a transaction savepoint
ROLLBACK TO SAVEPOINT savepoint name -- roll back to the savepoint
RELEASE SAVEPOINT savepoint name -- delete the savepoint -- InnoDB auto-commit feature settings
SET autocommit = 0|1; 0 means turning off autocommit, 1 means turning on autocommit.
- If it is closed, the results of normal operations will not be visible to other clients, and data operations can only be persisted after commit.
- You can also turn off autocommit to start a transaction. But unlike START TRANSACTION,
SET autocommit permanently changes the server's settings until the settings are modified again next time. (for current connection)
START TRANSACTION records the status before it is started, and once the transaction is committed or rolled back, the transaction needs to be started again. (For current transaction)
/* Lock table */
Table locks are only used to prevent improper reading and writing by other clients.
MyISAM supports table locks, and InnoDB supports row locks.
-- Lock
LOCK TABLES tbl_name [AS alias]
-- Unlock
UNLOCK TABLES
/* Trigger */ ------------------
A trigger is a named database object associated with a table that is activated when a specific event occurs on that table.
Monitoring: 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 is fired before or after the statement that activates it.
trigger_event specifies the type of statement that activates the trigger
INSERT: Activates a trigger when a new row is inserted into a table
UPDATE: triggers a procedure when a row is changed
DELETE: Activates a trigger when a row is deleted from a table
tbl_name: The monitored table must be a permanent table. The trigger cannot be associated with a TEMPORARY table or view.
trigger_stmt: The statement that is executed when the trigger is activated. To execute multiple statements, you can use the BEGIN...END compound statement structure. -- delete
DROP TRIGGER [schema_name.]trigger_name You can use old and new to replace old and new data
Update operation, before the update is old, after the update is new.
Delete operation, only old.
To add an operation, only new. -- Notice
1. For a given table, there cannot be two triggers with the same trigger action time and event.
-- Character concatenation function
concat(str1[, str2,...])
-- Branch statement
if condition then
Execute Statement
elseif condition then
Execute Statement
else
Execute Statement
end if; -- Modify the outermost statement terminator
delimiter custom end symbol
SQL statement custom end symbol delimiter ; -- Change back to the original semicolon -- Statement block wrapping
begin
Statement Block
end -- Special execution
1. Whenever a record is added, the program is 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 they are updated, 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 are records, execute before insert, before delete, after delete, after insert
/* SQL Programming */ ------------------
--// Local variables----------
-- 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
Use the set and select into statements to assign values to variables. - Note: Global variables (user-defined variables) can be used within a function
--// Global variables----------
--Define and assign values
The set statement defines and assigns values to variables.
set @var = value;
You can also use the select into statement to initialize and assign values to variables. This requires that the select statement can only return one row, but it can be multiple fields, which means assigning values to multiple variables at the same time, and the number of variables needs to be consistent with the number of columns in the query.
You can also think of the assignment statement as an expression and execute it through select. 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 table query to a variable.
-| select max(height) into @max_height from tb; -- Custom variable names To avoid conflicts between user-defined variables and system identifiers (usually field names) in select statements, user-defined variables use @ as the starting symbol before the variable name.
@var=10; - Once a variable is defined, it is valid throughout the entire session (login to logout)
--// 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];
- If you need to terminate the while loop early within the loop, you need to use a label; labels need to appear in pairs. -- Exit the loop
Exit the entire loop leave
Exit the current loop iterate
Determine which loop to exit by the exit label
--// Built-in functions -----------
-- Numerical functions
abs(x) -- absolute value abs(-10.9) = 10
format(x, d) -- formats 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) -- round to integer
mod(m, n) -- m%nm mod n remainder 10%3=1
pi() -- Get the value of pi
pow(m, n) -- m^n
sqrt(x) -- square root
rand() -- random numbers
truncate(x, d) -- truncate d decimal places
-- Time and date functions
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 part
date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j'); -- format time
unix_timestamp(); -- Get the Unix timestamp
from_unixtime(); -- Get time from timestamp -- String functions
length(string) -- string length, bytes
char_length(string) -- the number of characters in string
substring(str, position [,length]) -- start from position of str and take length characters
replace(str, search_str, replace_str) -- replace search_str with replace_str in str
instr(string, substring) -- returns the position of the first occurrence of substring in string
concat(string [,...]) -- concatenate strings
charset(str) -- returns the character set of a string
lcase(string) -- convert to lowercase
left(string, length) -- Take length characters from the left of string2
load_file(file_name) -- read content from a file
locate(substring, string [,start_position]) -- Same as instr, but you can specify the start position
lpad(string, length, pad) -- Repeatedly add pad to the beginning of string until the length of the string is length
ltrim(string) -- remove leading spaces
repeat(string, count) -- repeat count times
rpad(string, length, pad) -- Use pad to add to str until the length is length
rtrim(string) -- remove trailing spaces
strcmp(string1, string2) -- compare two strings character by character -- Process function
case when [condition] then result [when [condition] then result ...] [else result] end Multiple branches
if(expr1,expr2,expr3) double branch. -- Aggregate functions
count()
sum();
max();
min();
avg();
group_concat() -- Other commonly used functions
md5();
default();
--//Stored function, custom function----------
-- New
CREATE FUNCTION function_name (parameter list) RETURNS return value type
Function body
- Function names should be legal identifiers and should not conflict with existing keywords.
- 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, flow control, variable declaration and other statements.
- Multiple statements should be enclosed in a begin...end statement block.
- There must be a return statement. -- delete
DROP FUNCTION [IF EXISTS] function_name; -- Check
SHOW FUNCTION STATUS LIKE 'partten'
SHOW CREATE FUNCTION function_name; -- Revise
ALTER FUNCTION function_name Function Options
--//Stored procedure, custom function----------
--Define a stored procedure: A stored procedure is a piece of code (procedure) that is stored in the database in SQL.
A stored procedure is usually used to complete a piece of business logic, such as registration, class fee payment, order entry, etc.
A function usually focuses on a certain function and is regarded as a service for other programs. It needs to be called in other statements, while a stored procedure cannot be called by others and is executed by itself through call.
-- Create
CREATE PROCEDURE sp_name (parameter list)
Procedure 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. Compared with functions, it is more inclined to business logic.
Call: CALL procedure name
-- Notice
- No return value.
- Can only be called alone and cannot be mixed with other statements -- Parameters
IN|OUT|INOUT Parameter name Data type
IN Input: During the calling process, data is input into the parameters inside the procedure body.
OUT Output: During the calling process, the result of the procedure body is returned to the client
INOUT Input and output: both input and output -- grammar
CREATE PROCEDURE procedure name(parameter list)
BEGIN
Procedure Body
END
/* User and permission management */ ------------------
User information table: mysql.user
-- Refresh permissions
FLUSH PRIVILEGES
-- Add users
CREATE USER username IDENTIFIED BY [PASSWORD] password (string)
- You must have the global CREATE USER privilege for the mysql database, or have the INSERT privilege.
- Can only create users, cannot grant permissions.
- Username, note the quotes: e.g. 'user_name'@'192.168.1.1'
- The password also needs to be quoted, and pure numeric passwords also need to be quoted
- To specify the password in plain text, omit the PASSWORD keyword. To specify the password as a 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 the password for the current user
SET PASSWORD FOR username = PASSWORD('password') -- Set the password for the specified user
-- Delete user
DROP USER username
-- Assign permissions/add users
GRANT privilege list ON table name TO user name [IDENTIFIED BY [PASSWORD] 'password']
- all privileges means all privileges
- *.* means all tables in all libraries
- Library name. Table name indicates 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 privilege list ON table name FROM user name
REVOKE ALL PRIVILEGES, GRANT OPTION FROM username -- revoke all privileges
-- Permission levels
-- To use GRANT or REVOKE, you must have the GRANT OPTION privilege, and you must use the privilege that you are granting or revoking.
Global level: Global permissions apply to all databases in a given server.
GRANT ALL ON *.* and REVOKE ALL ON *.* grant and revoke only global privileges.
Database level: Database permissions apply to all objects in a given database, mysql.db, mysql.host
GRANT ALL ON db_name.* and REVOKE ALL ON db_name.* grant and revoke database privileges only.
Table level: Table privileges apply to all columns in a given table. mysql.table_priv
GRANT ALL ON db_name.tbl_name and REVOKE ALL ON db_name.tbl_name grant and revoke table privileges only.
Column-level: Column privileges apply to a single column in a given table. mysql.columns_priv
When using REVOKE, you must specify the same columns as those being granted.
-- Permission list
ALL [PRIVILEGES] -- Set all simple permissions except GRANT OPTION
ALTER -- Allows the use of ALTER TABLE
ALTER ROUTINE -- modify or delete a stored routine
CREATE -- Allows use of CREATE TABLE
CREATE ROUTINE -- create a stored routine
CREATE TEMPORARY TABLES -- Enables the use of CREATE TEMPORARY TABLE
CREATE USER -- Enables use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.
CREATE VIEW -- Allows the use of CREATE VIEW
DELETE -- Allows the use of DELETE
DROP -- Allows the use of DROP TABLE
EXECUTE -- Allows the user to run a stored subroutine
FILE -- Allows use of SELECT ... INTO OUTFILE and LOAD DATA INFILE
INDEX -- Allows use of CREATE INDEX and DROP INDEX
INSERT -- Allows the use of INSERT
LOCK TABLES -- Allows you to use LOCK TABLES on tables for which you have SELECT privileges.
PROCESS -- Enable use of SHOW FULL PROCESSLIST
REFERENCES -- Not implemented
RELOAD -- Allows use of FLUSH
REPLICATION CLIENT -- allows the user to query the address of a slave or master server
REPLICATION SLAVE -- for replication slave servers (reading binary log events from the master server)
SELECT -- Allows the use of SELECT
SHOW DATABASES -- show all databases
SHOW VIEW -- Enable use of SHOW CREATE VIEW
SHUTDOWN -- allow use of mysqladmin shutdown
SUPER -- Enables use of CHANGE MASTER , KILL , PURGE MASTER LOGS , and SET GLOBAL statements, and the mysqladmin debug command; allows you to connect (once) even if max_connections has been reached.
UPDATE -- Allows the use of UPDATE
USAGE -- synonym for "no permissions"
GRANT OPTION -- Allow granting of privileges
/* Table maintenance */
-- Analyze and store the keyword distribution of the table
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tablename...
-- 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] ...
/* Miscellaneous*/ ------------------ 1. You can use backticks (`) to wrap identifiers (library names, table names, field names, indexes, aliases) to avoid duplication with keywords! Chinese can also be used as an identifier! 2. Each library directory has an option file db.opt that saves the current database. 3. Notes: Single-line comment# Comment contentMulti-line comment/* Comment content*/
Single-line comment -- Comment content (standard SQL comment style, requiring a space character (space, TAB, line break, etc.) after a double dash) 4. Pattern wildcards: _ Any single character
% Any number of characters, including zero characters
Single quotes need to be escaped \' 5. The statement terminator in the CMD command line can be ";", "\G", "\g", which only affects the display result. Elsewhere, use a semicolon to end the statement. delimiter can modify the statement terminator of the current dialog. 6. SQL is not case sensitive 7. Clear existing statements: \c The above comprehensive summary of frequently used MySQL statements (must-read) is all I want to share with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. You may also be interested in:- Daily collection of commonly used SQL query statements
- MySQL series: Basic concepts of MySQL relational database
- Is mysql a relational database?
- Quickly learn MySQL basics
- Summary of SQL query syntax knowledge
| 
