MySQL Base, mysqlbase

MySQL Base, mysqlbase

/* Connect and disconnect the server */mysql-h address-P port-u username-p Password
---> Input pwd

/* Database storage engine */
InnoDB:
1) Ability to submit, roll back, crash recovery, and transaction processing (ACID)
2) handling large data volumes
3) supports foreign key constraints and associated queries

MyISAM:
1) supports indexing of large files, fields, BLOB and TEXT
2) High insertion and query speeds

/* Database Operations */

-- View existing database show databases;

-- Create database [if not exists] dbName options; USE dbName; show tables; drop database dbName; // delete DATABASE

 

/* Data table operations */

-- Partition Table structure for leave_message_topic, design by yaok -- revoke drop table if exists 'ave _ message_topic '; create table 'ave _ message_topic' ('topic _ id' varchar (64) not null comment 'message topic id', 'topic _ name' varchar (64) character set utf8mb4 not null comment 'topic name ', 'topic _ context' text character set utf8mb4 COMMENT 'topic content', 'sender' varchar (64) not null comment 'sender id', 'sender _ name' varchar (200) character set utf8mb4 default null comment 'sender name', -- can be omitted. Generally, the userId is used to find the name' is _ show' varchar (32) whether 'show 'comment' is displayed by DEFAULT (SHOW, HIDE) ', 'create _ time' timestamp not null default CURRENT_TIMESTAMP COMMENT 'creation time ', 'Update _ time' timestamp not null default CURRENT_TIMESTAMP ON update CURRENT_TIMESTAMP COMMENT 'Update time', 'delete _ flag' int (1) DEFAULT '0' comment' delete flag (0: normal 1: Delete) ', primary key ('topic _ id') ENGINE = InnoDB default charset = utf8 COMMENT = 'user message topic table ';


-- View table
DESC leave_message_topic;

-- Add/modify Fields
Alter table 'risk _ special_list'
Add column 'updatedesc' VARCHAR (50) NULL comment' modify reason 'after'updatebytype', // ADD Field
CHANGE 'valid' TINYINT (1) DEFAULT 1 not null comment' data status (0: Invalid 1: valid) '; // modify the field

 

/* Data Operations (DML )*/

/* Data operation */-- add INSERT [INTO] tbName [(Field List)] VALUES (Value List) [, (Value List),...] -- if the list of values to be inserted contains all fields in the same order, you can omit the field list. -- Multiple data records can be inserted at the same time! REPLACE is exactly the same as INSERT and is interchangeable. INSERT [INTO] tbName SET field name = value [, field name = value,...] -- insert into db1_name (field1, field2) SELECT field1, field2 FROM db2_name // insert into a (field1, field2) for two tables) SELECT * FROM (SELECT B. f1, c. f2 FROM B JOIN c) AS tb // suitable for multiple tables -- Query SELECT colName FROM tbName -- multiple fields FROM multiple tables -- other clauses can be left blank -- the field list can be replaced, indicates that all fields -- DELETE delete FROM tbName have no Condition Clause, then all -- DELETE will be deleted. Multiple tables cannot be deleted. cascading deletion (associated deletion) can be performed to delete t1, t2 from t1, t2 where Condition
Truncate table tbName; // The record is permanently deleted and cannot be recovered. -- change UPDATE tbName SET field name = new value [, field name = new value] [UPDATE condition] -- UPDATE flat_community for multi-TABLE join UPDATE, city_business_area SET flat_community.districtId = city_business_area.districtId WHERE flat_community.businessId = city_business_area.id AND city_business_area.id = 123

/* Join )*/

Join fields in multiple tables to specify the connection conditions.
-- Inner join)
-A connection can be sent only when data exists. That is, the connection result cannot contain blank rows.
On indicates the connection condition. The conditional expression is similar to where. You can also omit the condition (indicating that the condition is always true)

-- Outer join)
-Left outer join: if the data does not exist, the left table record appears, and the right table is filled with null.
-Right outer join: if the data does not exist, the right table record appears, while the left table is filled with null.
-- Natural join)
-The connection condition is automatically determined to complete the connection.
Using is omitted, and the same field name is automatically searched.
Natural join
Natural left join
Natural right join

 

/* Data type */
Https://dev.mysql.com/doc/refman/5.7/en/data-types.html

 

/* Column attributes and constraints */
-- Primary key
-A field that uniquely identifies a record. It 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.
You can also declare it after the field list
Ex: 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. The method to be declared after the field list.
Ex: create table tab (id int, stu varchar (10), age int, primary key (stu, age ));

-- Unique index (unique constraint)
So that the values of a field cannot be repeated.

-- Null Constraint
Null is not a data type but an attribute of a column.
Indicates whether the current column can be null, indicating nothing
Null. It can be null. Default
Not null, cannot be blank
Insert into tab values (null, 'val ');
-- Set the value of the first field to null, depending on whether the field is allowed to be null

-- Default Property
Default Value of the current Field
Insert into tab values (default, 'val '); -- this indicates that the default value is forcibly used.
Create table tab (add_time timestamp default current_timestamp );
-- Sets the timestamp of the current time to the default value.
Current_date, current_time

-- Auto_increment: Automatic growth constraint
Automatic growth must be an index (primary key or unique)
Only one field can grow automatically.
The default value is 1. You can set the table property auto_increment = x, or alter table tbl auto_increment = x;

-- Comment
Ex: create table tab (id int) comment 'comment content ';

 

/* 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 encoding used when the client sends data to the server
Character_set_results the encoding used by the server to return the result to the client
Character_set_connection connection layer 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
-- Check set
Collation for sorting
Show character set [LIKE 'pattern']/show charset [LIKE 'pattern'] view all CHARACTER sets
Show collation [LIKE 'pattern ']
Charset character set encoding set character set encoding
Collate check set encoding setting check set encoding

 

/* Select */------------------

Select [all | distinct] select_expr from-> where-> group by [Aggregate Function]-> having-> order by-> limit

A. select_expr
-- All fields can be represented.
Select * from tb;
-- Expressions can be used (calculation formula, function call, and field are also expressions)
Select stu, 29 + 25, now () from tb;
-- Aliases can be used for each column. It is applicable to simplifying column identifiers to avoid repeated column identifiers.
-The as keyword can be omitted.
Select stu + 10 as add10 from tb;

B. from clause
Used to identify the query source.
-- Alias can be set for the table. Use the as keyword.
Select * from tb1 as tt, tb2 as bb;
After the -- from clause, multiple tables can appear at the same time.
-- Multiple tables are stacked horizontally, and data forms a Cartesian product.
Select * from tb1, tb2;

C. where clause
-- Filter data sources obtained from.
-- Integer 1 indicates true, and 0 indicates false.

SELECT * FROM user_landlord WHERE 1 = 1 AND (name LIKE # {value} OR phone LIKE # {value}) -- The format of condition judgment is clear AND is generally used for dynamic SQL condition judgment.

-- Expressions are composed of operators and operators.
-- Operation count: variable (field), value, and function return value
-- Operator:
=, <=>, <> ,! =, <=, <, >=,> ,!, &, |,
In (not) null, (not) like, (not) in, (not) between and, is (not), and, or, not, xor
Is/is not with true/false/unknown to check whether a value is true or false.
<=> Same as <>, <=> can be used for null comparison.

D. group by clause, group clause
Group by field/alias [sorting method]
The Group is sorted. Ascending: ASC, descending: DESC

The following [Aggregate functions] must be used with group:

With rollup: Indicates whether to aggregate a category before summarizing it.
Count returns different non-NULL values, including count (*) and count (field)
Sum
Max calculates the maximum value.
Min for minimum value
Avg average
Group_concat returns a string with a non-NULL value from the join of a group. Group string connection.

E. having clause, Condition Clause
Similar to the where function and usage, the execution time is different.
Where performs detection data at the beginning to filter the original data.
Having filters the results again.
The having field must be queried, And the where field must exist in the data table.
Where cannot use the field alias, having can. This is because the column value may not be determined when executing the WHERE code.
Where cannot use aggregate functions. Having is usually used only by Aggregate functions.
SQL standard requires HAVING to reference columns in the GROUP BY clause or columns used in aggregate functions.

F. order by clause, sorting clause
Order by sorting field/alias sorting method [, sorting field/alias sorting method]...
Ascending: ASC, descending: DESC
Supports sorting multiple fields.

G. limit clause, limit the number of results clause
Only limit the number of processed results. The processed results are treated as a set. The index starts from 0 in the order of records.
Start position of the limit command to obtain the number of limit entries.
The first parameter is omitted, indicating that the index starts from 0. Number of limit entries

H. distinct, all option
Distinct remove duplicate records
The default value is all. all records

/* Delete & truncate */------------------ delete from tbl_name [WHERE where_definition] [order by...] [LIMIT row_count] delete (WHERE) according to conditions, specifying the maximum number of records to be deleted. Limit, which can be deleted by sorting conditions. Order by + limit supports deletion of multiple tables, using similar connection syntax. To delete from, you need to delete the Join Operation Conditions of multiple tables 1 and Table 2 using. /* Truncate */TRUNCATE [TABLE] tbl_name clear data delete the reconstruction TABLE difference: 1, truncate is to delete the TABLE and then create, delete is to delete 2 one by one, truncate reset auto_increment value. Delete won't be 3, and truncate doesn't know how many items are deleted, but delete knows. 4. When used in a table with partitions, truncate retains the partitions.

 

/* Subquery */------------------
-Subqueries must be enclosed in parentheses.
-- From type
A from table must be followed by an alias for the subquery results.
-Simplify the conditions in each query.
-For the from type, a temporary table is generated for the result and can be used to lock and release the original table.
-A subquery returns a table with a phenotype subquery.
Select * from (select * from tb where id> 0) as subfrom where id> 1;

-- Where Type
-A subquery returns a scalar query value.
-Aliases are not required for subqueries.
-Tables in the where subquery cannot be updated directly.
Select * from tb where money = (select max (money) from tb );

-- Column subquery
If the subquery result returns a column.
Use in or not in to complete the query
Exists and not exists Conditions
If the subquery returns data, 1 or 0 is returned. It is often used to determine conditions.
Select column1 from t1 where exists (select * from t2 );

/* EXISTS, IN */exists: Indicates whether to return the result set. You do not need to know what is returned. If the returned result is true, the opposite is false (ex: exists_in usage example. SQL), Loop the External table, and query the internal table in each Loop: The External table and the internal table are connected by hash, and the inner TABLE statement returns a data column as the two tables are of the same size, all can be used. For example, if a small vertex is used, a large subtable uses exist, and a small table uses in ex1: (a small B) SELECT id, name FROM a where exists (SELECT * FROM B WHERE. id = B. aid); // If a value exists, the returned result is true. The id and name corresponding to Table a are displayed. If table B has no value, the returned result is not returned. If table B has a large table, the returned result is exists, and the small table uses in OR SELECT id, name FROM B WHERE cc IN (SELECT cc FROM a) ex2: UPDATE flat_flats f1 SET f1.districtId = 3 WHERE EXISTS (select 1 from flat_community f2 where f2.businessId = 33 and f1.communityId = f2.id ); // batch update/* not exists, not in */not in is NOT exactly the same as not exists in logic, and subqueries are called without Indexing

-- Row subquery
The query condition is a row.
Select * from t1 where (id, gender) in (select id, gender from t2 );
Line constructor: (col1, col2,...) or ROW (col1, col2 ,...)
The row constructor is usually used to compare with subqueries that can return two or more columns.

-- Special operators
! = All () is equivalent to not in
= Some () is equivalent to in. Any is an alias of some.
! = Some () is not the same as not in, not one of them.
All and some can be used together with other operators.

 

/* Join )*/------------------
Join fields in multiple tables to specify the connection conditions.
-- Inner join)
-Inner is omitted by default.
-A connection can be sent only when data exists. That is, the connection result cannot contain blank rows.
On indicates the connection condition. The conditional expression is similar to where. You can also omit the condition (indicating that the condition is always true)
The where clause can also be used to indicate the connection conditions.
There is also using, but the field names must be the same. Using (field name)

-- Left Outer join
If the data does not exist, the left table record will appear, and the right table is filled with null
-- Right Outer join
If the data does not exist, the right table record will appear, while the left table is filled with null.
-- Natural join)
The connection is automatically determined based on the connection conditions.
Using is omitted, and the same field name is automatically searched.
Natural join
Natural left join
Natural right join

/* Transaction )*/------------------
A transaction refers to a logical group of operations that constitute each unit of this group of operations. If the transaction fails, the transaction fails.
-Supports collective success or collective revocation of consecutive SQL statements.
-Transactions are a function of the database for late data self-study.
-InnoDB or BDB storage engine must be used to support automatically submitted features.
-InnoDB is called a transaction security engine.

-- Start transaction
Start transaction; or BEGIN;
After the transaction is started, all executed SQL statements are considered as SQL statements in the current transaction.
-- Transaction commit
COMMIT;
-- Transaction rollback
ROLLBACK;
If some operations have problems, map them to the transaction before it is started.

-- Transaction Features
1. Atomicity)
A transaction is an inseparable unit of work. Operations in a transaction either occur or do not occur.
2. Consistency)
Data integrity must be consistent before and after transactions.
-When the transaction starts and ends, the external data is consistent.
-Operations are continuous throughout the transaction process.
3. Isolation)
When multiple users access the database concurrently, a user's transaction cannot be disturbed by other users' transactions, and data between multiple concurrent transactions must be isolated from each other.
4. Durability)
Once a transaction is committed, it changes the data in the database permanently.

-- Transaction implementation
1. The requirement is the table type supported by the transaction.
2. Start the transaction before executing a group of related operations
3. If all the operations in the group are successful, the task is committed. If a rollback fails, the task will return to the backup point at the beginning of the transaction.

-- Principles of transactions
Use the autocommit feature of InnoDB.
After a common MySQL statement is executed, the current data submission operation can be visible to other clients.
Transactions temporarily disable the "Automatic commit" mechanism and require commit to commit persistent data operations.

-- Note
1. Data Definition Language (DDL) statements cannot be rolled back, such as statements for creating or canceling databases and statements for creating, canceling, or changing table or stored subroutines.
2. transactions cannot be nested

-- Save point
SAVEPOINT save point name -- set a transaction save point
Rollback to savepoint save point name -- roll back TO save point
Release savepoint save point name -- Delete Save point

-- InnoDB automatic submission feature settings
SET autocommit = 0 | 1; 0 indicates that automatic submission is disabled, and 1 indicates that automatic submission is enabled.
-If the operation is disabled, the result of the normal operation is invisible to other clients. The data operation can be persisted only after the commit is submitted.
-You can also disable automatic commit to start transactions. However, unlike start transaction,
SET autocommit is a permanent change of server settings until the next modification. (For the current connection)
However, the start transaction record is in the status before it is enabled. Once the TRANSACTION is committed or rolled back, the TRANSACTION needs to be started again. (For the current transaction)