Ddl,dml in MySQL

ddl,dml in MySQL
DDL: Data definition language:
Create,alter,drop
DB components: Databases, tables, indexes, views, users, stored procedures, stored functions, triggers, event schedulers, etc.
common commands related to create:
CREATE DATABASE
CREATE EVENT
CREATE FUNCTION
CREATE FUNCTION UDF
CREATE INDEX
CREATE LOGFILE GROUP
CREATE PROCEDURE
CREATE SERVER
CREATE TABLE
CREATE tablespace
CREATE TRIGGER
CREATE USER
CREATE VIEW
    
DML: Data Manipulation Language
Insert,delete,update,select
    
Database:
Create,alter,drop
{database| SCHEMA}
[IF EXISTS]
[IF not EXISTS]
table: Two-dimensional relationship
Design tables: Following specifications
definition: field, index
Fields : Field names, field data types, modifiers
constraints: Indexes that should be created on fields that are frequently used as query criteria
index: Implementation level in the Access engine
Category:
dense indexes, sparse indexes
B + So, hash index, R-Tree index, fulltext index
Clustered index: Data and primary key indexes are stored together in the primary key index order
Nonclustered indexes: Indexes and data are stored separately, and the index finds the location of the original data by pointer
Simple Index (indexed on only one field), combined index (indexed on multiple fields)

To Create a table:
1, directly create;
CREATE [temporary] TABLE [IF not EXISTS] Tbl_name
(create_definition,...)
[Table_options]
[partition_options]
2, by querying the existing table creation: The new table will be inserted directly into the query data;
CREATE [temporary] TABLE [IF not EXISTS] Tbl_name
[(Create_definition,...)]
[Table_options]
[partition_options]
[IGNORE | REPLACE]
[as] query_expression
3. Create by copying the table structure of the existing table: No data is copied.
CREATE [temporary] TABLE [IF not EXISTS] Tbl_name
{like Old_tbl_name | (like Old_tbl_name)}
Note: Storage engine refers to the table type, which indicates the storage engine used when the table is created;
the same storage engine is used for tables in the same library;

To view the table structure:
DESCRIBE table_name
+------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| id | varchar (100) | NO | PRI |       NULL | |
| createtime | datetime |     YES | |       NULL | |
| host | varchar (255) |     YES | |       NULL | |
| name | varchar (255) |     YES | |       NULL | |
| originalfilename | varchar (255) |     YES | |       NULL | |
| relativeurl | varchar (255) |     YES | |       NULL | |
| relativeurlabb | varchar (255) |     YES | |       NULL | |
| savepath | varchar (255) |     YES | |       NULL | |
| savepathabb | varchar (255) |     YES | |       NULL | |
| suffix | varchar (255) |     YES | |       NULL | |
| url | varchar (255) |     YES | |       NULL | |
| urlabb | varchar (255) |     YES | |       NULL | |
+------------------+--------------+------+-----+---------+-------+  
      
to view status information for a table:
SHOW TABLE STATUS like ' table_name ' \g;
*************************** 1. Row ***************************
Name:fansik # table name
Engine:innodb # Storage Engine
Version:10 # version number
row_format:dynamic # line Format
rows:381042 # Number of rows already available
avg_row_length:401 # The average length of all existing rows
data_length:153026560 # Size of data in table
max_data_length:0 # Table Data maximum capacity, which is related to the storage engine, 0 is not limited
index_length:0 # Index size, no index
data_free:6291456 # currently allocated, but not yet stored in the data space, usually useful for MyISAM
Auto_increment:null # Next auto-Grow field
create_time:2017-07-11 10:12:06 # creation time of the table
update_time:2017-10-13 15:58:52 # Last modified time for table
Check_time:null # Last time the table was checked with the checktable command
collation:utf8_general_ci # Sorting rules
checksum of the Checksum:null # table
create_options: # Additional options when creating a table
Comment: # comment Information
1 row in Set (0.00 sec)

Modify table: ALTER table
Delete tables: Drop table

Ddl,dml in MySQL