MySQL > select * from ‘MySQL’;

View all current databasesshowDatabase # select * from databasedescTable name # Displays all tables in the current libraryshowTables # Displays all tables from other librariesshowTables form the libraryCopy the code

Query classes

Based on the query

selectQuery listfromTable name # Query list: table fields, constants, expressions, functionsCopy the code

Alias in two ways

selectfieldasThe aliasselectfield The aliasCopy the code

To heavy distinct

select distinctfieldfromThe name of the tableCopy the code

The + operator

  • If they’re both numbers, add them up
  • If it is a string, it is converted to a numeric value. If the conversion fails, it is 0
  • Is null, the result is NULL

Concat Concates characters

selectConcat (field1, the field2...).Copy the code

Conditions of the query

selectQuery listfromThe name of the tablewhereconditionsCopy the code

Conditional operator

# <>Is not equal to #< = >Security is equal to, you can tellnull
=.! =.>.<, ≥, ≤,<>.< = >
Copy the code

Logical operator

and.or.not
Copy the code

Fuzzy query

like.in.between and.is null
# %Wildcard: any number of characters including0# _ wildcard: Any single character # \ escape characterCopy the code

Sorting query

order bySort fieldasc | desc, sort fieldasc | desc... #ascascendingdescDescending # Default ascendingCopy the code

Common function

Character function

Lower, upper concat substr (string, initial index1Start count, length) length instr lpad, rpad trim replaceCopy the code

Mathematical function

round
ceil
floor
mod
truncateTruncate the specified decimal placeCopy the code

Date function

Now Date time Curdate Date Curtime Timeyear.month.day
str_to_date
date_format
Copy the code

Process control

If (condition,true.false)caseconditionswhenconstantthenstatementselseThe default valueend
Copy the code

A single function

Concat, length, ifNULLCopy the code

Multi-table join query

When the field to be queried involves more than one table

selectQuery listfrom 表1Connection typejoin 表2
onJoin conditionwherefilterCopy the code

Cartesian product

N × m Each row in table 1 matches table 2 cause: No valid join condition exists

Connection within the inner

Part of query intersection

  • Equivalent connection =
  • Unequal connection
  • Since the connection
    • Create different aliases for the same table to distinguish fields

Left outer connection /right outer connection left/right

  • Left to left is the main table and vice versa
  • All primary table fields are displayed. No NULL is displayed for secondary tables

Full external connection

Cross connection

Cartesian product

The subquery

Select statements that appear in other statements

  1. Subqueries are placed in parentheses
  2. Subqueries are placed to the right of the condition

Behind the where having

Scalar quantum query (single row subquery)
  • Generally, the single-line operator is used
  • Greater than/equal to/less than/unequal
Column subquery (multi-row subquery)
  • Usually with multi-line operators
  • IN/NOT IN :(NOT) included IN the list
  • ANY/SOME: compares with a value
  • ALL: compares with ALL values
Row subquery (multi-column multi-row)
SELECT * FROM 表
WHERE (a, b)=( SELECT A, B FROMTable)Copy the code

Select the back

SELECT d.*, (SELECT COUNT(*) FROM 表1) FROM 表2
Copy the code

From the back

Use the result set of the query as the data source for the external statement

After exists (correlation subquery)

  • Exists (query statement) : Returns whether the query result has a value, Boolean

Paging query

LIMIT offset, size
# offsetOffset # size NumberCopy the code

The joint query

To combine multiple query statements into a single result, query multiple tables and there is no federated relationship between the tables.

  • The query must have the same number of columns
  • Ensure that the types and sequences of each column of multiple query statements are consistent
  • Default automatic deduplication, use UNION ALL to query ALL
The query1 UNIONThe query2
Copy the code

DML language

insert

insert intoThe name of the table (column1Column,2) values(value1And the value2),values(value1And the value2) # support insert multiple rows # support subqueryinsert intoThe name of the tableset 列=Value,... The type of the inserted value must be the same as or compatible with the column typenull# can be transposed with the same number of columns and the same value # can have no column names, in which case the order and number of values are the same as the tableCopy the code

update

Single table

Update the table nameset 列=valuewherefilterCopy the code

More table

The update table1 inner/left/right join 表2
onJoin conditionset 列=valuewherefilterCopy the code

delete

deleteForm the name of the tablewherefilterCopy the code

Delete all

truncate tableThe name of the tableCopy the code

DDL

Management of libraries and tables

The management of the library

create

createThe database library namecreate database if not existsThe libraryCopy the code

alter

Rename Database Specifies the name of an old databasetoThe new libraryalterThe database library namecharacter setThe new character setCopy the code

drop

dropThe database library namedrop database if existsThe libraryCopy the code

The management of the table

create

create tableTable name (column name column type constraint)Copy the code

alter

Change the column namealter tableTable name change cloumn old column name new column name typealter tableTable name modify cloumn column name type # add new columnalter tableThe name of the tableaddCloumn column name type # Delete columnalter tableThe name of the tabledropCloumn column name # change table namealter tableThe table name renametoThe new name of the tableCopy the code

drop

drop tableThe name of the tableCopy the code

The replication of table

Duplicate the structure of the tablecreate tableThe new name of the tablelikeTable name # replication structure+contentcreate tableNew table name query statementCopy the code

The data type

numeric

The integer

tinyint # 1byte- 128.~127 0- 255.
smallint # 2Byte mediumint #3byteint # 4bytebigint # 8byteCopy the code
  • Default is signed, set to unsigned,
  • Set zerofill, default unsigned, length not enough to complement zero
  • Out of range insert threshold

The decimal

The simpler the type selected is the better, and the smaller the type that holds the value is the better (space saving)

  • M: Specifies the total number of integers + decimals. Default is 10
  • D: Specifies the decimal number. Default: 0
Floating point Numbers
float(M, D) # 4bytedouble(M, D) # 8byteCopy the code
  • Depending on the accuracy of the inserted value
Fixed-point number
#DEC(M, D)
DECIMAL(M, D)
Copy the code
  • More accurate than floating point numbers

character

This essay

# M Number of characters # Fixed length. Default is1
char(M) # variable length, not nullvarchar(M) # Assume the number of characters is10No matter how many characters are in the text,charWill open up10A space.varcharWill be based on the length of the content. # due tovarcharCalculate the length, so the performance is comparedcharWill be slower # save shorter binariesbinary
varbinaryENUM(a, B, c) # set, can be stored0~64An enum can be inserted into multiple enum membersset
Copy the code

Long text

Text # Saves the longer binaryblob
Copy the code

The date type

date
time
year

datetime # 8byte1000~9999Time zone is not affectedtimestamp # 4byte1970~2038Affected by Time zoneCopy the code

Common constraints

Restrict the data in the table to ensure the accuracy and reliability of the data in the table

Six major constraints

NOT NULL

Non-empty: Ensures that the value of this field cannot be empty

DEFAULT

Default: Ensure that the field has a default value

PRIMARY KEY

Primary key, which ensures that the field is unique and not empty

UNIQUE

Unique, ensuring the uniqueness of the field

CHECK (MySQL not supported)

Check for limits such as age range

FOREIGN KEY

A foreign key that limits the relationship between two tables and ensures that the value of the field must come from the associated column of the primary table

The characteristics of
  • Set the foreign key close from the table
  • The data types of the foreign key columns of the secondary table and the associated columns of the primary table should be consistent or compatible
  • The associated column of the primary table must be a key (primary key or unique)
  • When inserting data, insert the primary table first and then the secondary table
  • When deleting data, delete the secondary table first and then the primary table
Add the time
  • create
  • Modify the
classification
  • Column level
    • All six constraints can be written, but the foreign key constraint has no effect
  • Table level
    • All but non-null and default are supported

Primary key and the only difference

uniqueness Is empty Allow multiple Allow the combination
A primary key ✅ ❌ At least one ✅, but not recommended
The only ✅ ✅ You can have more than one ✅, but not recommended

Add constraints when creating tables

# column level constraintCREATE TABLE info {
  a int PRIMARY KEY,
  b varchar(20) NOT NULL,
  c char(1) CHECK(c='male' OR c='woman'),
  d int UNIQUE,
  e int DEFAULT,
  f int FOREIGN KEY REFERENCESM (id)} # table level constraintCREATE TABLE info {
  a int,
  b varchar(20),
  c char(1),
  d int,
  e int,
  f int.CONSTRAINT pk PRIMARY KEY(a),
  CONSTRAINT uq UNIQUE(b),
  CONSTRAINT ck CHECK(c='male' OR c='woman'),
  CONSTRAINT fk FOREIGN KEY(f) REFERENCES m(id),
}

Copy the code

Add constraints when modifying tables

# column level constraintALTER TABLEThe name of the table the MODIFYCOLUMNTable level constraint # table level constraintALTER TABLEThe name of the tableADD PRIMARY KEY(id)
Copy the code

Delete constraints when modifying tables

Delete primary keyALTER TABLEThe name of the tableDROP PRIMARYKEY # delete uniqueALTER TABLEThe name of the tableDROPINDEX column name # delete foreign keyALTER TABLEThe name of the tableDROP FOREIGNThe KEY of the columnCopy the code

Identity column

Increment column, increment from 1 by default

  • Only one identity column can exist in a table
  • The type of the identity column can only be numeric
CREATE TABLE info {
  a int PRIMARY KEY AUTO_INCREMENT
}
Copy the code

The transaction

One or a group of SQL statements forms an execution unit that either executes at all or not at all.

The ACID property of the transaction

  • Atomicity
    • Atomicity refers to the fact that a transaction is an indivisible unit of work in which all or none of the operations occur
  • Consistency
    • Transactions must move the database from one consistent state to another
  • Isolation
    • The execution of a transaction cannot be interfered with by other transactions
  • Durability
    • Once a transaction is committed, its changes to the database are permanent

Transaction creation

Implicit transaction

insert
update
delete
Copy the code

Explicit transaction

Prerequisite: The automatic submission function must be disabled first

# start transactionset autocommit=0;
starttransaction; Write transactionsqlstatementsselect,insert, the update,delete# end transactioncommit; Commit transactionOR
rollback; # rollback transactionCopy the code
  • DELETE Supports rollback
  • TRUNCATE does not support rollback

Isolation level

Multiple transactions running at the same time, when they access the same data in the database, can lead to all kinds of concurrency problems if the necessary isolation mechanisms are not in place:

  • Dirty read: T1 read T2Updated but not yet committedIf T2 rolls back, T1 reads invalid data
  • Non-repeatable reads: T1 reads a field, T2 updates the field, and T1 reads it again with a different value
  • Phantom read: after T1 reads a field, T2 performs an insert, and T1 reads a few more rows
Dirty read Unrepeatable read Phantom read
read uncommitted ✅ ✅ ✅
read committed ❌ ✅ ✅
repeatable read ❌ ❌ ✅
serializable ❌ ❌ ❌
  • Repeatable read by default in MySQL
  • Read COMMITTED is the default in Oracle

Viewing the Isolation Level

select @@tx_isolation
Copy the code

Set the isolation level of the current MySQL connection

set transaction isolation level read committed;
Copy the code

Sets the isolation level for the database system globally

set global transaction isolation level read committed;
Copy the code

Rollback point

Used with rollback to rollback to a marked point

set autocommit=0;
start transaction;

select,insert, the update,delete
savepointa; # set nodeselect,insert, the update,delete

rollback toa; # rollback transactionCopy the code

view

Virtual tables, rows and columns of data from the tables used in the query, are dynamically generated in use. Save only SQL logic, not query results.

Application scenarios

  • The same query results are used in multiple places
  • The SQL statements used for query results are complex

Create a view

create viewView nameasThe queryCopy the code

Modify the view

# create or replacecreate or replace viewView nameasThe queryalter viewView nameasThe queryCopy the code

Delete the view

DROP VIEWView name1The view name2..Copy the code

variable

System variables

# global variablesglobalSession variable session # Default sessionshow global variables;
show global variables like '%char%';
select @@global. System variable name;set globalSystem variable name= 值
set @@global. System variable name= 值
Copy the code
  • Session variables are only valid for the current session

Custom variable

# user variable, scoped as session variable # declaration and updateset@user variable name=valueset@user variable name:=valueselect@user variable name:=valueselectfieldintoThe variable namefromTable # local variable whose scope defines itbegin endIn thedeclareVariable name typedeclareVariable name typedefault 值

setLocal variable name=valuesetLocal variable name:=valueselectLocal variable name:=valueselectfieldintoThe variable namefrom 表
Copy the code

The stored procedure

A set of pre-compiled SQL statements that can have zero or more return values

  1. Improve code repeatability
  2. Simplified operation
  3. Reduce the number of compilations and connections to the database server
Parameter mode Parameter name typecreate procedureStored procedure name (parameter list)begin
  sqlstatementsend# call syntaxCALLStored procedure name (pass parameter) # deletedropProcedur Stored procedure name # Viewshow create procedureStored procedure nameCopy the code

Parametric model

  • IN: Can be used as input, requiring the caller to pass IN a value
  • OUT: Can be used as output, return value
  • INOUT

The end tag

Since SQL statements are followed by; , so the entire structure needs to use other closing tags

Delimit $is used for the markup configuration

function

There is only one return value

Parameter name Parameter typecreate functionFunction name (argument list)returnsThe return typebeginThe body of the functionreturn 值
end# call syntaxselectFunction name (argument list) # View functionshow create functionFunction name # deletedrop functionThe function nameCopy the code

Process control

Branching structure

# if the expression1True, returns the expression2Otherwise, the expression is returned3The value # can be applied anywhere IF(expression1The expression2The expression3) # Can only be placedbegin endThe if condition1 thenStatement elseif condition2 thenstatements2
end if

# case thenYou can put it anywhere,thenAfter is the statement can only be placedbegin end# in the grammar1
caseVariable | expression | fieldwhen 值1 thenThe return valuewhen 值1 thenThe return value...elseThe return valueend case# grammar2
case
whenJudge conditionsthenThe return valuewhenJudge conditionsthenThe return value...elseThe return valueend case
Copy the code

Loop structure

/* iterate: continue leave: break */Tag: body of a while conditional do loopendWhile tag: the body of a loopendLoop tag Tag: the condition that repeats the body of the loop untilendRepeat the labelCopy the code