MySQL manages tables and indexes, and MySQL manages table indexes.

MySQL manages tables and indexes, and MySQL manages table indexes.

MySQL manages tables and Indexes

Create database | SCHEMA [if not exists] db_name [character set =] [COLLATE =]

Delete DATABASE: DROP {DATABASE | SCHEMA} [if exists] db_name

Create a table:
1. Define an empty table directly;
2. query data from other tables and create a new table;
3. Create an empty table using another table as the template;

1.  CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name    (create_definition,...)    [table_options]    [partition_options]2.CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name    [(create_definition,...)]    [table_options]    [partition_options]    select_statement
When a table is created using a select query statement, the format of the table to be created may be different from that of the table to be queried.

3.CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name    { LIKE old_tbl_name | (LIKE old_tbl_name) } 

When defining the field type in a table, add unisined to the value to indicate the unsigned value type (only a positive number ).
For example, the create table name (id int unisgned); the id column value can only be a positive number.

Modify Table definition:
ALTER TABLE
Add, delete, and modify Fields
Add, delete, and modify Indexes
Change table name
Modify Table attributes

 

Delete a table: DROP [TEMPORARY] TABLE [if exists] tbl_name [, tbl_name]... [RESTRICT | CASCADE]

InnoDB supports foreign keys
A key is also called a constraint and can be used as an index. It belongs to a special index and belongs to the BTree.

An index is composed of one or more columns in a data table. The purpose of creating an index is to optimize the database query speed. The index created by the user points to the location of the specific data in the database. When a user queries data in a database through an index
Data in all databases does not need to be traversed, which improves query efficiency. indexing is a structure that sorts values of one or more columns in a database.
When you query data through an index, the system does not need to traverse all the records in the data table, but queries the index column. Generally, data is queried by traversing all the data, and find matching records in the database.

Show indexes from tb_name: displays the index DESC table_name of the specified table. displays the table structure.

Index creation:

 CREATE INDEX index_name ON tb_name (col,...);　　 col_name [(length)] [ASC | DESC]
　　　　

Create index name on table name (field name (length) aes | desc)
"(Length)" in the field name (length) during index creation indicates that only the leftmost characters of the index are allowed.

Delete INDEX: drop index index_name ON tbl_name

Query statement type:
Simple Query
Multi-Table query
Subquery (nested query)

SELECT * FROM tb_name; SELECT field1, field2 FROM tb_name; projection SELECT [DISTINCT] * FROM tb_name WHERE qualification; SELECT

The same DISTINCT value is displayed only once.

FROM clause: the relational table to be queried, multiple tables, and other SELECT statements.
WHERE clause: Boolean Relational Expression
=,>, >=, <=, <Value Comparison without quotation marks, string comparison with quotation marks
Logical Relationship:
AND
OR
NOT

BETWEEN... AND... LIKE ''%: any length any character _: any single character REGEXP, RLIKE supports regular expression IN using discrete value IS NULL IS NOT NULL

Order by field_name {ASC | DESC} query result sorting (ASC ascending and DESC descending)

Field alias:
Select name as student_name from student; display student_name is not name

LIMIT clause: LIMIT [offset,] Count is displayed starting from offset. count is displayed.

Aggregation: SUM (), MIN (), MAX (), AVG (), COUNT ()

Group by: GROUP
HAVING qualification Filtering

Multi-Table query:

Join: cross join: Cartesian Product natural join: Outer Join: left Outer Join :... left join... ON... right outer join :... right join... ON... self-connection: using aliases

Subquery:

Use subquery in comparison: Only one value can be returned for a subquery;
IN (): Use subquery;
Use subquery in FROM;

Joint query:
UNION

View: the stored SELECT statement. (It is of little significance in MySQL and is related to security)
Base table-based query results;

CREATE    [OR REPLACE]    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]    [DEFINER = { user | CURRENT_USER }]    [SQL SECURITY { DEFINER | INVOKER }]    VIEW view_name [(column_list)]    AS select_statement    [WITH [CASCADED | LOCAL] CHECK OPTION]  

Displays the statements used to create a table, view, and database.

SELECT CREATE {DATABASE | TABLE | VIEW} NAME    

Generalized query:

DML:
DELETE
INSERT
UPDATE

INSERT INTO tb_name (col1, col2, ...) VALUES (val1, val2, ...)[,(val1, val2, ...),...]INSERT INTO tb_name SET col1= ,col2= ,....INSERT INTO tb_name (col1,col2...) SELECT ....

Character Type: single quotes numeric type: no quotation marks required date and time type: NULL ,''

Replace into is similar to INSERT.

DELETE：     DELETE FROM tb_name WHERE condition;

TRUNCATE tb_name: clear the table and reset the AUTOINCREMENT counter; UPDATE tb_name SET col1 =..., col2 =... WHERE