This article mainly introduces the summary of common commands for MySQL to view and clear tables. it is the basic knowledge in MySQL getting started. For more information, see
View MySQL database tables
Go to MySQL Command line client
View the currently used database:
Mysql> select database (); mysql> status; mysql> show tables; mysql> show databases; // You can check which databases are available and return the database name (databaseName) mysql> use databaseName; // replace the currently used database mysql> show tables; // return the names of all tables in the current database
Alternatively, you can directly use the following command
Mysql> show tables from databaseName; // databaseName can be obtained using show databases.
Run the following command to view the table structure in mysql:
Desc table name; show columns from table name;
Or
Describe table name; show create table name;
Or
Use information_schemaselect * from columns where table_name = 'Table name ';
View warning:
Rows matched: 1 Changed: 0 Warnings: 1 mysql> show warnings; +---------+------+-------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------+ | Warning | 1265 | Data truncated for column 'name' at row 3 | +---------+------+-------------------------------------------+ 1 row in set
The preceding commands are used to view MySQL database tables.
MySQL clear table
Clearing a table in Mysql is an important and most common operation. The following describes how to clear a table in Mysql.
Method 1: recreate the database and table
Use mysqldump -- no-data to export the SQL statement for table creation. then drop the database and create database. execute the exported SQL file and create the table;
Method 2: generate an SQL statement to clear all tables
mysql -N -s information_schema -e "SELECT CONCAT('TRUNCATE TABLE ',TABLE_NAME,';') FROM TABLES WHERE TABLE_SCHEMA='eab12'"
The output result is as follows:
TRUNCATE TABLE AUTHGROUPBINDINGS;TRUNCATE TABLE AUTHGROUPS;TRUNCATE TABLE AUTHUSERS;TRUNCATE TABLE CORPBADCUSTOMINFO;TRUNCATE TABLE CORPSMSBLACKLISYInfo;TRUNCATE TABLE CORPSMSFILTERINFO;TRUNCATE TABLE CORPSMSINFO;TRUNCATE TABLE EABASEREGINFOS;TRUNCATE TABLE EACORPBLOB;TRUNCATE TABLE EACORPINFO;........
In this way, it is improved:
The code is as follows:
Mysql-N-s information_schema-e "select concat ('truncate table', TABLE_NAME, ';') from tables where TABLE_SCHEMA = 'eab12'" | mysql eab12
Clear all tables in eab12.
However, if a foreign key exists, an error may be reported. Therefore, you need to add a-f
The code is as follows:
Mysql-N-s information_schema-e "select concat ('truncate table', TABLE_NAME, ';') from tables where TABLE_SCHEMA = 'eab12'" | mysql-f eab12
Execute multiple times until no error is reported.
The above is the implementation of Mysql table clearing.