MySQL common command MySQL processing database and table commands _mysql

Learn how to manage and navigate MySQL databases and tables is one of the first tasks to master, the following content will be mainly on MySQL database and table of some common commands to summarize, some we have to grasp the commands, some of the commands.
I. processing of databases

1, view the database
Getting a list of databases on a server is often useful. Execute show databases; command can be done.
mysql> show databases;

2. Create a database
mysql> CREATE DATABASE Db_test;
Query OK, 1 row Affected (0.00 sec)

3. Using Database
Once a database is created, it can be designated as the default working database by using the use command database.
mysql> use db_test;
Database changed

4, delete the database
The way you delete a database is similar to how you create it. You can use the drop command to delete a database from the MySQL client as follows:
mysql> drop Database db_test;
Query OK, 0 rows Affected (0.00 sec)

Second, processing table

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

1. Create a table
The table is created from the CREATE TABLE statement. There are a lot of options and clauses in the process of creating a table, and it's not realistic to summarize it all over again, but here's a summary of the most common, later encounters, and 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 NUL L,
  -> LastName varchar () NOT NULL,
  -> email varchar (a) NOT NULL,
  -> phone varchar (a) Not null,
   -> primary key (ID));
Query OK, 0 rows affected (0.03 sec)

Remember, the table contains at least one column. Also, after you create a table, you can always go back and modify the structure of the table. You can create a table regardless of whether the target database is currently in use, 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 ( Not NULL,
  -> lastname varchar (a) NOT NULL,
  -> email varchar (=) NOT null,
  -> phone varchar (10 Not NULL,
  -> primary key (ID));
Query OK, 0 rows affected (0.03 sec)

2. Create a table with conditions
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,
  -> Firstna Me varchar NOT NULL,
  -> lastname varchar (a) NOT NULL,
  -> email varchar (=) NOT null,
  -> phone VA Rchar (a) 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 window, regardless of whether you have created it.

3. Copy table
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. You can accomplish this by specifying a column in the Create SELECT statement:

Mysql> describe Tb_test; +-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default |
Extra | +-----------+------------------+------+-----+---------+----------------+
| ID | int (a) 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:0 mysql> describe; +-----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default |
Extra | +-----------+------------------+------+-----+---------+-------+
|   Id | int (a) 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)

4. Create temporary tables
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 the query on the entire table, instead of having MySQL find the few records needed each time, it may be quicker to save the records to a temporary table, and then query the temporary tables. Can be implemented 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 the 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 all memory space; Of course, you can also manually delete temporary tables using the drop TABLE command.

5, view the tables available in the database
You can use the show Tables command to complete. For example:

Mysql> show tables;
+-------------------+
| Tables_in_db_test |
+-------------------+
| tb_test      |
| tb_test2
| +-------------------+
2 rows in Set (0.00 sec)

6. 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  |   | NULL  | | |        Email |   varchar   | NO  |   | NULL  | | |
| | |   -VARCHAR   | NO  |   | NULL  |        |
+-----------+------------------+------+-----+---------+----------------+
5 rows in Set (0.00 sec)

In addition, the same result can be obtained by 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  | | |
| | |   -VARCHAR   | NO  |   | NULL  |        |
+-----------+------------------+------+-----+---------+----------------+
5 rows in Set (0.00 sec)

7, delete the table
The delete table is implemented using the DROP TABLE statement with the following syntax:

Copy Code code as follows:

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

8. 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 re-create the table each time you make a change. Instead, you can use the ALTER statement to modify the structure of the table. With this statement, you can delete, modify, and add columns if necessary. As with CREATE TABLE, ALTER TABLE provides a number of clauses, keywords, and options. Here just say some simple use, 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 ();
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 control the location of new columns with the appropriate keywords, including the top, after, and last. 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 email email varchar (=) not null;
Query OK, 0 rows affected (0.11 sec)
records:0 duplicates:0 warnings:0

If you feel the email this column is not necessary to exist, 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

In general, this article summarizes some of the commands commonly used with MySQL, mainly to learn the MySQL processing database and table of common commands, I hope to help.