Management of MySQL libraries and tables

After the MySQL database service is configured, the system will have 4 default databases.

Information_schema: Virtual objects, whose objects are stored in memory
Performance_schema: Server Performance Metrics Library
MySQL: Log user permissions, Help, logs, and other information
Test: Testing Library

MySQL Database and table management

1. Querying all databases

mysql> show databases;

2. Create a database

Syntax: CREATE {DATABASE | SCHEMA} [IF not EXISTS] Db_name

The default specified encoding format is Utf-8

Mysql> CREATE database if not exist db_name;

Custom encoding formats

Mysql> CREATE DATABASE db_name default character set UTF8;

Deleting a database

Mysql> drop database if exists db_name;

3. Modify the Database

Query encoding format

Mysql> Show CREATE Database db_name;

Modify the encoding format

ALTER DATABASE db_name default character set GBK;

4. Managing tables

1. Creating tables and viewing tables

View Table

Use db_name;show tables;

View table Structure

Desc Tbl_name;

View columns in a table

SHOW COLUMNS from Tbl_name;

View status information for a table

Show table status like ' Tbl_name ';

To create a table syntax:

CREATE [temporary] TABLE [IF not EXISTS] tbl_name (... Field definition ..., model VARCHAR () not NULL,... field definition ...);

To create a table:

Mysql> CREATE TABLE tbl_name (    ID int (one) not NULL auto_increment,    name char (+) NOT NULL,    Age int default NULL,    address char (+) NOT NULL,    primary key (ID),    engine=innodb default Cha Rset=utf8    ; Query OK, 0 rows affected (0.02 sec)

5. Modify the structure of the table, using the ALTER command

Add fields to the table

ALTER TABLE tbl_name add column field name varchar (5);

Remove a field from a table

ALTER TABLE tbl_name drop column field name;

Add an ID field to a table

ALTER TABLE tbl_name add ID int not nullprimary key auto_increment first;

To delete a data table:

mysql> DROP TABLE tbl_name;

Empty the records in the table:

Mysql> DELETE from tbl_name;mysql>truncate table tbl_name;

To rename a table:

Rename table table_name to tbl_name;

Copy table data (data structure is different)

CREATE TABLE T2 select * from T1;

Duplicate table structure

CREATE table new Table SELECT * from old table where 1=2 or create table new table like old table

Reference Documentation:
Http://www.cnblogs.com/zmxmumu/p/4424877.html

Http://www.cnblogs.com/chenmh/p/5644644.html

　　

Management of MySQL libraries and tables