MySQL several points of knowledge

Common commands

Principle: Use the tool as much as possible with the visualizer, and the command line is limited to troubleshooting.

mysql -u ACCOUNT -pPWD -h IP -P port [-D database]  //连接数据库show databases; //显示数据库列表use db_name;    //使用数据库show tables; //展示所有表select XXX from XXX where XXX;select count(*) from XXX;   //统计总记录

Two ways to delete data

delete from tablename [where XXX]; //可以指定条件，删除整表/部分记录，会返回被删除记录数，执行速度不快truncate TABLE tablename; //不可指定条件，删除整表，返回记录数为0，执行非常快，不可以恢复数据delete from tablename where 1; //删除所有记录后，自增字段不会恢复为1

Conclusion: The effect of delete is a bit like deleting all records in a MySQL table to delete, and truncate is equivalent to preserving the structure of the MySQL table, recreating the table, all of which are equivalent to the new table.

Count (1), COUNT (*), Count (column)

• COUNT (*)--evaluates the number of rows, including null
• Count (column)--statistics on the number of rows that a particular column's value has, without a null value
• COUNT (1)--Same as COUNT (*) effect

Key conclusions:

1. The column name is the primary key, and the Count (column name) is faster than count (1).
   
2. The column name is not a primary key, and COUNT (1) is faster than count (column name).
3. If the table has more than one column and there is no primary key, then count (1) is faster than count (*).
   
4. If there is a primary key, count (primary key, Federated primary key) is faster than count (*).
   
5. If the table has only one field, the count (*) is the fastest.

MySQL several points of knowledge