MySQL common commands for working with databases and tables

Working with databases

View Database

It is often useful to get a list of databases on a server. Execute show databases; the command will be done.

mysql> show databases;

Create a database

mysql> CREATE DATABASE Db_test;
Query OK, 1 row Affected (0.00 sec)

Working with databases

Once the database is created, it can be specified as the default working database by using the use command database.

mysql> use db_test;
Database changed

Deleting a database

Deleting a database is similar to how it was created. You can delete the database using the drop command in the MySQL client, as follows:

mysql> drop Database db_test;
Query OK, 0 rows Affected (0.00 sec)

Working with Tables

Here's how to create, list, view, delete, and modify MySQL database tables.

Create a table

The table is created with the CREATE TABLE statement. The process of creating a table will use a lot of options and clauses, which are completely summarized here is not realistic, here is just summed up the most common, later encountered other, then a single summary. The general usage of creating tables is as follows:

Mysql> CREATE TABLE tb_test (-id int unsigned NOT NULL auto_increment, FirstName varchar (+) NOT NULL, LastName varchar (+) NOT NULL, e-mail varchar (GB) NOT NULL, phone varchar (+) NOT NULL,-> ; Primary key (ID)); Query OK, 0 rows affected (0.03 sec)

Remember that the table contains at least one column. In addition, you can always go back to modifying the structure of a table after you create the table. You can create a table regardless of whether you are currently using the target database, as long as you precede the table name with the target database. For example:

Mysql> CREATE TABLE db_test.tb_test (-id int unsigned NOT NULL auto_increment, FirstName varchar) n     OT null, LastName varchar (+) NOT NULL, e-mail varchar NOT NULL, phone varchar (+) NOT NULL, Primary key (ID)); Query OK, 0 rows affected (0.03 sec)

Conditionally creating a table

By default, if you try to create a table that already exists, MySQL generates an error. To avoid this error, the CREATE TABLE statement provides a clause that you can use if you want to simply exit the table creation if the target table already exists. For example:

Mysql> CREATE table if not EXISTS db_test.tb_test (-id int unsigned NOT NULL auto_increment, FirstName varchar (+) NOT NULL, LastName varchar (+) NOT NULL, email varchar (+) NOT NULL, phone varchar (ten) not NULL, PRIMARY key (ID)); Query OK, 0 rows affected, 1 Warning (0.00 sec)

The Query OK message appears when you return to the command prompt, whether or not you have created it.

Copying tables

Creating a new table based on an existing table is an easy task. The following code will get a copy of the Tb_test table named Tb_test2:

Mysql> CREATE TABLE Tb_test2 select * from Db_test.tb_test; Query OK, 0 rows affected (0.03 sec) records:0 duplicates:0 warnings:0

An identical table tb_test2 will be added to the database. Sometimes, you might want to create a table based on just a few columns of an existing table. The specified column in the Create SELECT statement can be implemented as follows:

mysql> describe tb_test;+-----------+------------------+------+-----+---------+----------------+|  Field     | Type              | Null | Key | Default | Extra           |+-----------+------------------+------+-----+---------+------------ ----+| id        | int (Ten)  unsigned | no    | pri | null    | auto_increment | |  firstname | varchar (+)       | NO   |      | NULL    |                 | |  lastname  | varchar (+)       | NO &Nbsp; |     | null    |                 | |  email     | varchar (      | NO )   |     | NULL    |                 | |  phone     | varchar (Ten)       | NO    |     | NULL    |                 |+-----------+------------------+------+---- -+---------+----------------+5 rows in set  (0.01 sec) mysql> create table  tb_test2 select id, firstname, lastname, email from tb_tesT query ok, 0 rows affected  (0.03 sec) records: 0  duplicates:  0  warnings: 0mysql> describe tb_test2;+-----------+------------------+------+---- -+---------+-------+| field     | type              | null | key | default | extra  |+-----------+------------------+------+-----+---------+-------+| id         | int (Ten)  unsigned | NO   |      | 0       |       | |  firstname | varchar (+)       | NO   |      | null    |       | |  lastname  | vaRchar (+)       | NO   |     |  null    |       | |  email     | varchar (      | NO )   |     | NULL    |        |+-----------+------------------+------+-----+---------+-------+4 rows in set  (0.01 SEC)

Create a temporary table

Sometimes, when working on a very large table, you may occasionally need to run many queries to get a small subset of a large amount of data, instead of running these queries against the entire table, instead of having MySQL find the few records needed each time, saving the records to a temporary table might be faster, and then query the temporary tables. This can be done by using the TEMPORARY keyword and the CREATE TABLE statement.

Mysql> Create temporary table Emp_temp SELECT FirstName, LastName from Tb_test; Query OK, 0 rows affected (0.02 sec) records:0 duplicates:0 warnings:0

Temporary tables are created just like other tables, except that they are stored in a temporary directory specified by the operating system. Temporary tables will exist during your connection to MySQL, and when you disconnect, MySQL will automatically delete the table and free up all memory space; Of course, you can also manually delete the temporary table using the drop TABLE command.

To view the tables available in the database

You can do this using the show Tables command. For example:

Mysql> Show tables;+-------------------+| Tables_in_db_test |+-------------------+| Tb_test | | Tb_test2 |+-------------------+2 rows in Set (0.00 sec)

View table Structure

You can use the describe statement to view the table structure, for example:

mysql> describe tb_test;+-----------+------------------+------+-----+---------+----------------+|  Field     | Type              | Null | Key | Default | Extra           |+-----------+------------------+------+-----+---------+------------ ----+| id        | int (Ten)  unsigned | no    | pri | null    | auto_increment | |  firstname | varchar (+)       | NO   |      | NULL    |                 | |  lastname  | varchar (+)       | NO &Nbsp; |     | null    |                 | |  email     | varchar (      | NO )   |     | NULL    |                 | |  phone     | varchar (Ten)       | NO    |     | NULL    |                 |+-----------+------------------+------+---- -+---------+----------------+5 rows in set  (0.00 sec)

In addition, you can get the same results using the show command, for example:

mysql> show columns in tb_test;+-----------+------------------+------+-----+---------+ + ---------------+| field     | type              | null | key | default | extra           |+-----------+------------------+------+-----+---- -----+----------------+| id        | int (Ten)  unsigned  | no   | pri | null    | auto_increment  | |  firstname | varchar (+)       | NO   |      | NULL    |                 | |  lastname  | varchar (+)       | no   |     | null    |                 | |  email     | varchar (      | NO )   |     | NULL    |                 | |  phone     | varchar (Ten)       | NO    |     | NULL    |                 |+-----------+------------------+------+---- -+---------+----------------+5 rows in set  (0.00 sec)

Delete a table

The delete table is implemented using the drop TABLES statement, with the following syntax:

drop [temporary] table [if exists] tbl_name [, Tbl_name, ...]

Change Table structure

We will find that we often modify and improve the table structure, especially in the early stages of development, but you do not have to delete and recreate the table each time you make a modification. Instead, you can use the ALTER statement to modify the structure of the table. With this statement, you can delete, modify, and add columns as necessary. As with CREATE TABLE, ALTER TABLE provides a number of clauses, keywords, and options. Here is just a few simple uses, such as inserting a column in the Table Tb_demo table, indicating email, the code is as follows:

Mysql> ALTER TABLE tb_demo add column email varchar (45); Query OK, 0 rows affected (0.14 sec) records:0 duplicates:0 warnings:0

The new column is placed at the last position of the table. However, you can also use the appropriate keywords, including first, after, and last, to control the location of the new column. If you want to modify the table, for example, just add the email, I want to add a not null control, the code can be this:

mysql> ALTER TABLE Tb_demo change e-mail varchar NOT null; Query OK, 0 rows affected (0.11 sec) records:0 duplicates:0 warnings:0

If this email does not exist, you can use the following code to remove it, for example:

mysql> ALTER TABLE tb_demo drop email; Query OK, 0 rows affected (0.09 sec) records:0 duplicates:0 warnings:0

MySQL common commands for working with databases and tables