Constraint

1
col datatype <constraint>  # in create table

Primary key

NOT NULL + UNIQUE
usually use id as primary key

1
col datatype primary key auto_increment;

For insert after delete/truncate
delete no impact on auto_increment
truncate: auto_increment from 1

Foreign key

  • field(s) in one table, referring to the primary key in another table

  • child table: with the foreign key

    • value of foreign key is either from parent table or null

  • parent table: with the primary key

    • col cited should be unique(usually use primary key)
1
[constraint] [foreighKeyName] foreign key referneces ParentTable(colName);

  • delete foreign key constraint:

    1
    alter table <childTable> drop foreign key <foreignKeyName>;

  • default

  • check (boolean expression)

    • not on columns with auto_increment
    • no storing functions whether user defined or otherwise
    • no subqueries
    • omitted or “enforced” / not enforced : create and (not) enforced

  • on update cascade on delete cascade(when create table)

    • update/delete the rows from child table automatically when rows from parent table are deleted

join

Combine rows from two or more tables

1
select */col(s) from <leftTable> [inner] join <rightTable> on expression;

left [outer] join

match the records from left table with those from the right table.

display if available else display null

right [outer] join

Conversely

Cross Join(Cartesian Product)

Return the Cartesian product of two tables

Self Join

Join of a table to itself, used to compare rows within the same table

TPL

1
2
3
start transaction;
commit;
rollback;

DQL autocommit by default in mysql

check if autocommit is true

1
select @@autocommit;

cancel

1
set autocommit=0;

savepoint

1
2
savepoint <savepointName>;
rollback to savepoint(orrr savepointName);

Transaction isolation

transaction

  • Atomicity
  • Consistency
  • Isolation
  • Durability

isolation level

the higher of the isolation level, the worse performance, but better safety

Issues

Dirty read: when transact A, read uncommitted data from B

Non-repeatable read: a transaction gets different values when queried again

Phantom read: the second query, under the same conditions as the first query, retrieved rows of data that the first
query did not

Syntax

1
2
select @@transaction_ioslation; # get isolation level
set global transaction_isolation=str;

str:

‘read uncommited’

‘read commited’

‘repeatable read’

‘serializable’

Explain

Column Meaning
id SELECT identifier
select_type SELECT type
table The table for the output row; in the order of being read
partitions The matching partitions
type Join type
possible_keys The possible indexes to choose
key The index actually chosen
key_len The length of the chosen key
ref The columns compared to the index
rows Estimate of rows to be examined
filtered Percentage of rows filtered by table condition
Extra Additional information
system one record in the table; = const,eq_ref, index, all ……

select_type

simple: no union or subquery

primary: the outermost select

union: the second layer

subquery: the second select in subquery

Index

Single Column Index

Normal Index

when create table

1
index/key <indexName>(colName(length))

Unique Index

1
unique index <indexName>(colName(length))

Primary Key Index

not allowed with null values

1
primary key <indexName>(colName(length))

Composite Index

1
index <indexName>(col1(length),...,colN(length))

most left prefix principle: the leftmost prefix is utilized for searching and filtering data

Full-text Index

  • only on myISAM engine

  • only char, varchar,text type

  • get the required rows through key words

1
fulltext index <indexName>(col(len))
  • need to use match() func
  • at least 4 characters

Spatial Index

1
spatial index <indexName>(col(len))

Types

  • geometry
  • point
  • linestring
  • polygon

Funcs

concat(str1,…)

locate(substr,str) / instr(str,substr) : return the position of the first occurence of substr in str

left(str,len) / right(str,len) : return the left/right len characters of str

substring(str,pos): substring from pos to the end

trim(str): remove the leading and trailing spaces
ltrim(str): rm leading
rtrim(str): rm trailing

replce(str,from_str,to_str): replace from_str with to_str in str

repeat(str,times): repeat str for times

reverse(str): reverse str