MySQL commands for working with databases and tables

MySQL is a cross-platform open source relational database management system, currently MySQL is widely used in small and medium-sized websites on the internet. Because of its small size, fast speed, low total cost of ownership, especially the open source, many small and medium-sized web sites in order to reduce the total cost of ownership of the site chose MySQL as the site database. Learning how to manage and navigate MySQL databases and tables is the primary task to master.

I. Processing of databases
1. View the 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;
2. Create a database
mysql> CREATE DATABASE Db_test;
Query OK, 1 row Affected (0.00 sec)
3. Using the Database
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
4. Delete 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)
Ii. processing tables: How to Create, list, view, delete, and modify MySQL database tables.
1. 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,
 &nbs p; -FirstName varchar (+) NOT NULL,
   , LastName varchar (+) NOT NULL,
 &NB sp; -e email varchar NOT NULL,
   , phone varchar (+) NOT NULL,
  &NB 　　Sp 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. Example:
mysql> CREATE TABLE db_test.tb_test (
   , id int unsigned NOT NULL auto_increment,   -FirstName varchar (+) NOT NULL,
   , LastName varchar (+) not null,< br>   -e email varchar NOT NULL,
   , phone varchar (+) NOT NULL,
&NB sp;  -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. The Query OK message appears when you return to the command prompt, whether or not you have created it.
2. Copy the 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. The specified column in the Create SELECT statement can be implemented as follows:
Mysql> describe Tb_test;

3. 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, ...]

4. 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 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 commands for working with databases and tables