MySQL Management tables and indexes

MySQL Management tables and indexes

创建数据库：    CREATE DATABASE|SCHEMA [IF NOT EXISTS] db_name [CHARACTER SET=] [COLLATE=]  

删除数据库：    DROP {DATABASE | SCHEMA} [IF EXISTS] db_name    

To create a 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;

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 you create a table from a SELECT query statement, the table you create may not be the same as the format definition of the queried table

3.CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name    

When you create a table, the field type is defined when it is numeric and the unisined represents an unsigned type. For example (ID int unisgned)

To modify a table definition:
ALTER TABLE
Add, delete, modify fields
Adding, deleting, and modifying indexes
Change table name
Modify Table Properties

删除表：        DROP [TEMPORARY] TABLE [IF EXISTS]    tbl_name [, tbl_name] ...    [RESTRICT | CASCADE]    

INNODB support for foreign keys
Keys are also called constraints, can be used as indexes, belong to special indexes, BTree

An index is composed of one or more columns in a data table, and the purpose of creating an index is to optimize the query speed of the database. Where the user-created index points to the specific data in the database. When the user queries the data in the database by index
Improves query efficiency by not having to traverse data in all databases, an index is a structure that sorts the values of single or multiple columns in a database
By querying the data through the index, the system can query the index columns instead of traversing all the records in the data table, and the general process of querying the data is accomplished by traversing all the data and looking for matching records in the database.

SHOW INDEXES FROM tb_name：显示指定表上的索引DESC table_name  显示表结构

索引创建：

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

Create index index name on table name (field name (length) aes|desc)
"(length)" In the field name (length) when the index is created means that only the leftmost number of characters is indexed

删除索引：    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; 投影SELECT [DISTINCT] * FROM tb_name WHERE qualification; 选择    

DISTINCT the same value is displayed only once

FROM clause: Relational table, multiple tables, other SELECT statements to query
WHERE clause: boolean-relational expression
=, >, >=, <=, < numeric comparison without quotation marks, string comparison with quotation marks
Logical Relationship:
and
OR
Not

BETWEEN ... AND ...LIKE ‘’    %: 任意长度任意字符    _：任意单个字符REGEXP, RLIKE                   支持正则表达式IN                              使用离散取值IS NULL IS NOT NULL

ORDER by Field_name {asc| DESC} query Result sort (ASC ascending, desc descending)

Field aliases: As
Select name as Student_name from student; Display as Student_name not name

Limit clause: Limit [Offset,]count is displayed starting from offset, showing Count of

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

GROUP BY: Grouping
Having qualification filter

Multi-Table query:

连接：    交叉连接：笛卡尔乘积    自然连接：    外连接：        左外连接：... LEFT JOIN ... ON ...        右外连接: ... RIGHT JOIN ... ON ...    自连接：   使用别名来进行

Sub-query:

Subqueries are used in comparison operations: Subqueries can only return a single value;
In (): Use sub-query;
Using subqueries in from;

Federated query:
UNION

Views: stored Select statements; (not useful in MySQL, meaning when security is relevant)
Query results based on the base table;

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]  

Shows what statements are used to create a table, view, database, and so on

SELECT CREATE {DATABASE | TABLE | VIEW} NAME    

Generalized query:

Dml:
DELETE
INSERT into
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 ....

字符型：单引号数值型：不需要引号日期时间型：空值：NULL， ‘‘

REPLACE into is similar to insert

DELETE：     DELETE FROM tb_name WHERE condition;

MySQL Management tables and indexes