MySQL: Summary of basic operations on databases and tables, and basic operations on mysql

MySQL: Summary of basic operations on databases and tables, and basic operations on mysql

Database and table operations can be divided into three types: creation, query, modification, and deletion.

1. create

① Create database db_name;

② Create table db_name.tb_name (column attributes) [table options];

Tip: You must specify the database to which it belongs (you can also use db_name; specify the database first, and then create table tb_name)

2. query show

① Query all databases/tables;

Tip: query the database. You can use the show statement directly;

To query a table, use the use db_name statement to specify the database where the table is located;

② Query some tables: tables prefixed with ex show tables like 'ex _ % ';

② Query creation statements

· Show create database db_name; // view the database creation statement

· Show create table tb_name; // View table creation Information

③ View the table structure describe tb_name; (abbreviated as desc tb_name ;)

3. Modify

① Alter database db_name [modify command];

Generally, there is only one type of modification command, that is, modification of database attributes:

After changing the character set of the database test_one to gbk, You can query its creation information and find that the change has taken effect.

② Database rename:

· You can directly modify the directory;

· Export all database content, create a new database, import and delete the old database;

· Create a new database, move (rename) tables in the old database to the new database, and delete the old database;

Rename a table:

Rename table old_tb_name to new_tb_name; // rename can be used to rename multiple tables.

Tip: supports cross-database rename

③ Modify the column information in the table

· Add a new column

· Modify the column definition modify

· Delete column drop

· Rename the column change

④ Alter table tb_name character set utf8;

4. Delete

① Delete the database drop database db_name;

② Delete the table drop table tb_name; (a more comprehensive statement is drop table if exists tb_name ;)