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