MySQL Management tables and indexes

MySQL Management tables and indexes

SQL statements:
Database
Table
Index
View
Dml

Creating a database: Create database| SCHEMA [IF not EXISTS] db_name [CHARACTER set=] [collate=] Delete database: 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_statement3. create [temporary] table [if not exists] tbl_name    {  like old_tbl_name |  (like old_tbl_name)  }     create a blank table:     CREATE TABLE [IF NOT EXISTS] tb_name  (Col_name col_defination,  constraint )     engine [=] engine_name 

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

Create index creates indexes show INDEXES from Tb_name: Displays index on specified table DESC table_name display table structure

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

InnoDB supports foreign keys (foreign KEY constraints consume system resources very much)

Delete table: drop [temporary] table [IF EXISTS] tbl_name [, Tbl_name] ... [RESTRICT |    CASCADE] Index creation: CREATE INDEX index_name on Tb_name (Col,...); col_name [(length)] [ASC | DESC] Delete index: DROP index index_name on tbl_name SELECT select-list from TB WHERE qualification

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; Choose

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 ' '
%: Any character of any length
_: Any single character
REGEXP, Rlike supports regular expressions
In using discrete values
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:

Connection:
Cross join: Cartesian product
Natural connections:
External connection:
Left outer connection: ... Left JOIN ... On ...
Right outer connection: ... Right JOIN ... On ...
Self-connect: use aliases for


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]

Displays 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 ....

Character type: Single quotation mark
Numeric type: No quotation marks required
Date and Time type:
Empty value: null, ' '

REPLACE into is similar to insert

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

This article is from the "bustling Down" blog, please be sure to keep this source http://chenxujiang.blog.51cto.com/11737025/1846423

MySQL Management tables and indexes