MySql processes databases and table_mysql

It is usually useful to view the database to obtain the database list on the server. Run the showdatabases command. Mysqlshowdatabases; create database mysqlcreatedatabasedb_test; QueryOK, 1 rowaffected (0 View database

It is usually useful to obtain the database list on the server. Run the show databases command.

Mysql> show databases;

Create a database

Mysql> create database db_test;
Query OK, 1 row affected (0.00 sec)

Use Database

Once a database is created, you can use the use command to specify it as the default working database.

Mysql> use db_test;
Database changed

Delete database

The method for deleting a database is similar to that for creating a database. You can use the drop command in the mysql client to delete a database, as shown below:

Mysql> drop database db_test;
Query OK, 0 rows affected (0.00 sec)

Processing Table

This section describes how to create, list, view, delete, and modify a MySQL database table.

Create a table

You can use the create table statement to create a table. Many options and clauses are used during table creation. it is unrealistic to simply summarize them here. here, we will summarize them most commonly. in the future, we will summarize them separately. The common usage of creating a table is as follows:

mysql> create table tb_test(    -> id int unsigned not null auto_increment,    -> firstname varchar(25) not null,    -> lastname varchar(25) not null,    -> email varchar(45) not null,    -> phone varchar(10) not null,    -> primary key(id));Query OK, 0 rows affected (0.03 sec)

Remember, a table must contain at least one column. In addition, after creating a table, you can always go back and modify the table structure. You can create a table no matter whether or not you are using the target database. you only need to add the target database before the table name. For example:

mysql> create table db_test.tb_test(    -> id int unsigned not null auto_increment,    -> firstname varchar(25) not null,    -> lastname varchar(25) not null,    -> email varchar(45) not null,    -> phone varchar(10) not null,    -> primary key(id));Query OK, 0 rows affected (0.03 sec)

Conditional table creation

By default, if you try to create an existing table, MySQL will generate an error. To avoid this error, the create table statement provides a sub-statement. if you want to simply exit table creation when the target table already exists, you can use this sub-statement. For example:

mysql> create table if not exists db_test.tb_test(    -> id int unsigned not null auto_increment,    -> firstname varchar(25) not null,    -> lastname varchar(25) not null,    -> email varchar(45) not null,    -> phone varchar(10) not null,    -> primary key(id));Query OK, 0 rows affected, 1 warning (0.00 sec)

The "Query OK" message is displayed when you return to the command prompt window whether or not you have created the command.

Copy a table

Creating a new table based on an existing table is an easy task. The following code returns 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. In some cases, you may want to create a table only based on several columns of the existing table. You can use the create select statement to specify columns:

mysql> describe tb_test;+-----------+------------------+------+-----+---------+----------------+| Field     | Type             | Null | Key | Default | Extra          |+-----------+------------------+------+-----+---------+----------------+| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment || firstname | varchar(25)      | NO   |     | NULL    |                || lastname  | varchar(25)      | NO   |     | NULL    |                || email     | varchar(45)      | NO   |     | NULL    |                || phone     | varchar(10)      | 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(10) unsigned | NO   |     | 0       |       || firstname | varchar(25)      | NO   |     | NULL    |       || lastname  | varchar(25)      | NO   |     | NULL    |       || email     | varchar(45)      | NO   |     | NULL    |       |+-----------+------------------+------+-----+---------+-------+4 rows in set (0.01 sec)

Create temporary table

Sometimes, when you work on a very large table, you may occasionally need to run many queries to obtain a small subset of a large amount of data, instead of running these queries on the entire table, instead, it is faster for MySQL to locate a few required records and save the records to a temporary table. then, it queries these temporary tables. You can use 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 in the same way as other tables, but they are stored in the temporary directory specified by the operating system. Temporary tables will exist during your connection to MySQL. when you disconnect, MySQL will automatically delete the table and release all the memory space. of course, you can also manually use the drop table command to delete a temporary table.

View available tables in the database

You can run 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(10) unsigned | NO   | PRI | NULL    | auto_increment || firstname | varchar(25)      | NO   |     | NULL    |                || lastname  | varchar(25)      | NO   |     | NULL    |                || email     | varchar(45)      | NO   |     | NULL    |                || phone     | varchar(10)      | NO   |     | NULL    |                |+-----------+------------------+------+-----+---------+----------------+5 rows in set (0.00 sec)

In addition, use the show command to get the same results, for example:

mysql> show columns in tb_test;+-----------+------------------+------+-----+---------+----------------+| Field     | Type             | Null | Key | Default | Extra          |+-----------+------------------+------+-----+---------+----------------+| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment || firstname | varchar(25)      | NO   |     | NULL    |                || lastname  | varchar(25)      | NO   |     | NULL    |                || email     | varchar(45)      | NO   |     | NULL    |                || phone     | varchar(10)      | NO   |     | NULL    |                |+-----------+------------------+------+-----+---------+----------------+5 rows in set (0.00 sec)

Delete table

The drop table statement is used to delete a table. Its syntax is as follows:

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. However, you do not have to delete the table before creating a new table each time you modify it. Instead, you can use the alter Statement to modify the table structure. With this statement, you can delete, modify, and add columns if necessary. Like create table, alter table provides many clauses, keywords, and options. Here we will only talk about some simple usage. for example, insert a column in The tb_demo table to indicate 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 end of the table. However, you can also use appropriate keywords (including first, after, and last) to control the positions of new columns. If you want to modify the table, such as the newly added email address, I want to add a not null control. The code can be like this:

mysql> alter table tb_demo change email email varchar(45) not null;Query OK, 0 rows affected (0.11 sec)Records: 0  Duplicates: 0  Warnings: 0

If you think this email column is unnecessary, you can use the following code to delete it, for example:

mysql> alter table tb_demo drop email;Query OK, 0 rows affected (0.09 sec)Records: 0  Duplicates: 0  Warnings: 0