Basic operation commands for MySQL database

First, the database operation

show databases; List databases

Use database_name; Using the database_name database

Create Database Data_name; To create a database named Data_name

Drop database data_name; Delete a database named Data_name

Second, table operation

Show tables//List all tables

CREATE TABLE tab_name (ID int (TEN) NOT NULL Auto_increment primary key,name varchar (+), pwd varchar (+) charset=gb2312;)// Create a new table named Tab_name;

drop table tab_name; Delete a data table named Tab_name

Describe Tab_name; Display the data structure of a table named Tab_name

Show columns from Tab_name; Display the data structure of a table named Tab_name

Delete from Tab_name; Empty records in Table Tab_name

SELECT * from Tab_name; Show records in Table Tab_name

Mysqldump-uudser-ppassword--no-data DATABASE Table >table.sql//Copy table structure

Third, modify the table structure

ALTER TABLE Tab_name ADD PRIMARY key (col_name) Description: Change the definition of the table to set a field as the primary key.

ALTER TABLE tab_name Drop PRIMARY Key (col_name) Description: Delete the definition of the primary key

ALTER TABLE Tab_name add col_name varchar (20); Add a field named Col_name in the Tab_name table and the type is varchar (20)

ALTER TABLE Tab_name drop col_name//delete col_name field in Tab_name

ALTER TABLE Tab_name Modify col_name varchar (+) NOT NULL//Modify field properties, note if plus not NULL requires no data under the original field SQL Server200 is written under the following syntax: Alter table T Able_name Alter Column col_name varchar (+) not null;

How to modify the table name: ALTER TABLE Tab_name Rename to New_tab_name

How to modify the field name: ALTER TABLE tab_name change old_col new_col varchar (40); You must specify properties such as data type for the current field, or you cannot modify

CREATE TABLE New_tab_name like Old_tab_name//Use an existing table to build a new table, but not the data for the old table

Iv. backup and recovery of data

Import External Data text:

Executes an external SQL script on the current database: MySQL < input.sql execution on the specified database: MySQL [table name] < Input.sql

The data incoming command, load data local infile "[file name]" into table [table name]; BACKUP database: (Dos) mysqldump--opt school>school.bbbmysqldump-u [user] -p [Password] databasename > filename (Backup) mysql-u [user]-p [Password] databasename < filename (recovery)

Basic operation commands for MySQL database