Marco Learning Note 15--MYSQL Advanced SQL statement

Database:

Create Database|schema [if not exists] db_name [Character set =] [collate =];

ALTER DATABASE

Drop {database | schema} [if exists] db_name;

Table:

1, directly define an empty table;
2, from other tables to query the data, and create a new table;
3. Create an empty table with other tables as templates;

CREATE TABLE [IF not EXISTS] Tb_name (col_name col_defination, constraint)

CREATE TABLE tb1 (id INT UNSIGNED NOT NULL auto_increment PRIMARY KEY, Name CHAR (a) Not NULL, age TINYINT NOT null) ENGI NE [=] engine_name;

CREATE TABLE tb2 (id INT UNSIGNED NOT NULL auto_increment, Name CHAR (a) Not NULL, age TINYINT NOT NULL, PRIMARY KEY (ID), U Nique KEY (name), INDEX (age));

Single field: PRIMARY Key UNIQUE Key

Single or multiple fields: Pramary KEY (col,...)  UNIQUE KEY (Col,...) INDEX (Col,...)

Keys, also known as constraints, can be used as indexes and belong to special indexes (with special qualification): B+tree

Show INDEXES from Tb_name: Display indexes on the specified table

Index creation:
CREATE INDEX index_name on Tb_name (Col,...);

col_name [(length)] [ASC | DESC]

Query Statement type:

Simple query

Multi-Table Query

Sub-query

SELECT field1,field2 from Tb_name; Projection

SELECT [DISTINCT] * from Tb_name WHERE qualification; Choose

Distinct: The same value is displayed only once

FROM clause: Relational table, multiple tables, other SELECT statements to query

WHERE clause:

Boolean Relationship expressions =, >, >=, <=, <

Logical relationship: and OR not

Between ... And ...

Like ' '

%: Any character of any length

_: Any single character

REGEXP, Rlike: supports regular expressions

In: Discrete value

Is NULL

is not NULL

ORDER by Field_name {asc| DESC}: Sort the data after the query

Field aliases: As

Limit clause: limit [offset,]count

Aggregate function: SUM (), MIN (), MAX (), AVG (), COUNT ()

GROUP BY: Grouping

Having qualification: filtering on Groups

Multi-Table query:
Connection:
Cross join: Cartesian product
Natural connections:
External connection:
Left outer connection: ... Left JOIN ... On ...
Right outer connection: ... Right JOIN ... On ...
Self-connect:

Sub-query:

To use subqueries in comparison operations:

A subquery can only return a single value;

In (): Use sub-query;

Using subqueries in from;

Union query: Union

View: a stored SELECT statement;

Query results based on the base table;

VIEW

CREATE VIEW

Materialized view: SELECT

Show Create: Display creation process

MYSQL-E ' SQL statement ': Execute SQL statement in Shell command letter

Generalized query:

Dml:

DELETE

INSERT into

UPDATE

INSERT into Tb_name (col1, col2, ...) VALUES (Val1, Val2, ...) [, (Val1, Val2, ...),...]

Character type: Single quotation mark

Numeric type: No quotation marks required

Date and Time type:

Empty value: null, ' '

REPLACE into

Delete:delete from Tb_name WHERE condition;

TRUNCATE tb_name: Clears the table and resets the AutoIncrement counter;

UPDATE tb_name SET col1= ..., col2= ... WHERE