MySQL Common commands

Use the show statement to find out which database exists on the server: 

mysql> SHOW DATABASES; +----------+ | Database | +----------+ | mysql | | test | +----------+ 3 rows in set (0.00 sec)

2. Create a database, abccs 
Mysql> Create Database abccs;
Note that different operating systems are case sensitive.
3. Select the database you created 
Mysql> Use abccs
Database changed
Now you have entered the database abccs you just created.
4. Create a database table 
First, check what tables exist in your database:
Mysql> show tables;
Empty set (0.00 Sec)
It indicates that no database table exists in the Database just created. Create a database table mytable: Create a birthday table for your employees. The table contains the employee name, gender, birth date, and city of birth.

mysql> CREATE TABLE mytable (name VARCHAR(20), sex CHAR(1), -> birth DATE, birthaddr VARCHAR(20)); Query OK, 0 rows affected (0.00 sec)

Because the column values of name and birthadd change, varchar is selected and its length is not necessarily 20. You can choose any length from 1 to 255. If you need to change its font length in the future, you can use the alter table statement .); Gender can be expressed by only one character: "M" or "F". Therefore, char (1) is used, and date is used for the birth column.
After creating a table, we can look at the results just now and use show tables to show which tables are in the database:

mysql> SHOW TABLES; +---------------------+ | Tables in menagerie | +---------------------+ | mytables | +---------------------+

5. display table structure: 

mysql> DESCRIBE mytable; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | deathaddr | varchar(20) | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)

6. Add records to the table 
We first use the SELECT command to view the data in the table:
Mysql> select * From mytable;
Empty set (0.00 Sec)
This indicates that the table created just now has no records. Add a new record:

mysql> insert into mytable -> values (′abccs′,′f′,′1977-07-07′,′china′); Query OK, 1 row affected (0.05 sec)

Use the SELECT command above to check what has changed. We can add records of all employees to the table one by one using this method.
7. load data into a database table in text mode 
It is troublesome to input data one by one. We can add all records to your database table using text files. Create an example file named "“mysql.txt". Each line contains a record. Use a tab to separate the values and give them in the order of the columns listed in the create table statement. For example:

abccs f 1977-07-07 china 　 mary f 1978-12-12 usa tom m 1970-09-02 usa

Use the following command to load the parent file named mytable.txt to the mytable Table: mysql> load data local infile "mytable.txt" into Table PET;
Run the following command to check whether the data has been input to the database table: mysql> select * From mytable;

MySQL display table structure

Desc table name; Show columns from table name; Describe table name; Show create table table name; Select * From information_schema.columns where table_schema = 'database name' and table_name = 'table name '; Pay attention to the red part. Duplicate fields are displayed when multiple databases have the same table name. The red part is used to restrict the range.   When the data table structure is modified, You need to restart MySQL to make the structure change take effect!