Database operation:

• Create database:

  • CREATE DATABASE database name;
    

• View database:

  • # View all databases
    SHOW DATABASES;
    

• Create database:

  • #Select a database / switch to xxx database
     USE database name;
    

• Create database:

  • #Delete database
     DROP DATABASE database name;
    

Table operation:

• Create table:

  • # Multiple parameters separated by commas
    CREATE TABLE Table name( Parameter name parameter type ...);
    # Other common operations
    PRIMARY KEY #The primary key constraint can be simply understood as including unique constraints and non empty constraints
    NOT NULL #Non empty constraint
    UNIQUE #Unique constraint
    DEFAULT #Set default
    AUTO_INCREMENT #Primary key self increment
    

• Check list:

  • Switch table
     USE table name;
    SHOW TABLE table name;
    

• Delete table:

  • DORP TABLE table name;
    

• Alter table:

  • # Modify field data type
    ALTER TABLE t_id MODIFY INT BIGINT;
    # Modify field name
    ALTER TABLE Table name CHANGE t_id t_ids;
    

• Set foreign key:

  • ALTER TABLE table name ADD FOREIGN KEY (foreign key field) REFERENCES parent table (primary key field);
    

Data operation:

• Insert data:

  • INSERT INTO table name (field 1, field 2...) VALUES(value1,value2...);
    

• Modify data:

  • UPDATE table name SET field name = value WHERE condition
    

• Delete data:

  • DELETE TABLE table name WHERE condition
    

• Query data:

  • # Query all
    SELECT * FROM Table name;
    # Query assignment
    SELECT * FROM Table name WHERE condition;
    # Query specification to avoid duplicate score (de duplication)
    SELECT DISTINCT score  FROM Table name WHERE condition;
    # Fuzzy query
    SELECT * FROM Table name WHERE field LIKE '%value%';
    
    # join query
    # Internal connection
    SELECT * FROM Table 1 INNER JOIN Table 2 ON condition;
    # External connection
    # Left outer join
    SELECT * FROM Table 1 LEFT OUTER JOIN Table 2 ON condition;
    # Right outer join
    SELECT * FROM Table 1 RIGHT OUTER JOIN Table 2 ON condition;
    
    # Group query
    SELECT MIN(field) FROM Table name WHERE condition GROUP By Grouping field;
    SELECT MIN(field) FROM Table name WHERE condition GROUP By Grouping field HAVING condition#Criteria query after grouping
    
    # Grouping function
    SELECT COUNT(field) FROM Table name;#Count ignores NULL by default
    SELECT SUM(field) FROM Table name;#Sum ignores NULL by default
    SELECT AVG(field) FROM Table name;#Average value ignore NULL by default
    SELECT MAX(field) FROM Table name;#Maximum value
    SELECT MIN(field) FROM Table name;#Minimum value
    
    # Sorting can have multiple sorting fields
    # ASC forward / DESC reverse
    SELECT * FROM Table name ORDER BY Sort field 1,Sort field 2 ASC,DESC;
    
    # Limit the number of query results returned
    # i: Is the index value of the query result (starting from 0 by default), I can be omitted when i=0
    # n: Number returned for query results
    select * from Table name limit i,n;
    
    # Conclusion:
    #Statement writing order
    SELECT DISTINCT .. ROM .. WHERE .. GROUP BY .. HAVING .. ORDER BY .. LIMIT ..
    #Execution sequence
    FROM .. WHERE .. GROUP BY .. HAVING .. SELECT DISTINCT .. ORDER BY .. LIMIT ..