Mysql Operation database and table of common commands novice Tutorials _mysql

I'm a novice.

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.

Working with databases

1. View the database

Getting a list of databases on a server is often useful. Execute show databases; command can be done.

Copy Code code as follows:

mysql> show databases;

2. Create a database

Copy Code code as follows:

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

3. Using the Database

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

Copy Code code as follows:

mysql> use db_test;
Database changed

4. Delete 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:

Copy Code code 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.

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:

Copy Code code as follows:

Mysql> CREATE TABLE Tb_test (
-> ID int unsigned NOT NULL auto_increment,
-> FirstName varchar () NOT NULL,
-> LastName varchar () NOT NULL,
-> email varchar 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:

Copy Code code as follows:

Mysql> CREATE TABLE 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 (a) 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:

Copy Code code as follows:

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 (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:

Copy Code code as follows:

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:

Copy Code code as follows:

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 Tb_test2;
+-----------+------------------+------+-----+---------+-------+
| 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 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 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.

Copy Code code as follows:

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:

Copy Code code as follows:

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:

Copy Code code as follows:

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.00 sec)

In addition, the same result can be obtained by using the show command, for example:

Copy Code code as follows:

Mysql> show columns in 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.00 sec)

7. Delete 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 the 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:

Copy Code code 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 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:

Copy Code code as follows:

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:

Copy Code code as follows:

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

I'm not a novice.

This article generally summarizes some of the commands that are commonly used when dealing with MySQL, and I hope it helps. After reading this article, you should think that you are not a novice, if the above commands you have practiced once again, you should be more than 60% of the people familiar with the MySQL database. That's it, the simpler things, the more a lot of people will not.