MySQL Operation detailed

Create and use a database

• To view the database on the server:SHOW DATABASES;
• To create a database:CREATE DATABASE <数据库名>;
• Indicate what database to use:USE <数据库名>

Create and use a table

• To create a table:CREATE TABLE <表名> (<列名> <数据类型> <约束>,...);
• View tables (These tables are available in this database):SHOW TABLES;
• View table structure (column name, data type, constraints, etc.):DESCRIBE <表名>;
• File Data Import Database:LOAD DATA INFILE <文件> INTO TABLE <表名>;
• Read all the information from the table:SELECT * FROM <表名>;
• To obtain a time difference (for example, for calculating age): e.g.: SELECT name,TIMESTAMPDIFF(YEAR,<列名>,CURDATE()) AS age FROM pet; orSELECT name,(YEAR(CURDATE())-YEAR(<列名>-(RIGHT(CURDATE(),5)<RIGHT(<列名>,5)) AS age FROM pet;
• Null value: null indicates an unknown value. Regarding NULL, it should be noted that: 1. Determine whether a data is null, should be used <列名> IS NULL/IS NOT NULL ; 2.GROUP by two values null are considered equal; 3.0 or an empty string is not null
• Support pattern matching in conditional statements where, regular expressions
• Use COUNT() function count
• In MySQL, you can connect two tables directly using the FROM clause

Getting information about a database and tables

• Get all databases under the server:SHOW DATABASES;
• Get the currently used database:SELECT DATABASE();
• Get all tables in the database:SHOW TABLES;
• Get table structure:DESCRIBE <表名>;
• Gets the index information for the table:SHOW INDEX FROM <表名>;

MySQL Operation detailed