MySQL Common operations

second, the database operation
1, show databases; List databases
2. Use database_name//using database_name database
3. Create database Data_name//Creating a db named Data_name
4. Drop database Data_name//delete a db named Data_name

Third, table operation
1. Show databases;//list all databases

Use database name; Reach a database

Show tables//List all tables
CREATE TABLE Tab_name (
ID Int (TEN) NOT NULL Auto_increment primary key,
Name varchar (40),
PWD varchar (40)
) charset=gb2312; Create a new table named Tab_name
2. drop table Tab_name Delete the data table named Tab_name
3, describe Tab_name//display data structure of a table named Tab_name
4. Show columns from Tab_name//Ibid.
5. Delete from Tab_name//Empty the record in table Tab_name
6, select * FROM Tab_name//display table Tab_name Records
7. Mysqldump-uuser-ppassword--no-data DATABASE table > Table.sql//Copy table structure

iv. Modification of table structure
1. ALTER TABLE tab_name ADD PRIMARY KEY (col_name)
Description: Change the definition of a table to set a field as the primary key.
2. ALTER TABLE tab_name DROP PRIMARY KEY (col_name)
Description: Delete the definition of the primary key
3. 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)
4. ALTER TABLE Tab_name drop col_name//delete col_name field in Tab_name
5. ALTER TABLE tab_name Modify col_name varchar (+) NOT NULL//Modify field properties, note If not NULL requires no data in the original field
The syntax under SQL Server200 is: ALTER TABLE table_name ALTER COLUMN col_name varchar (+) not null;
6. How to modify the table name: ALTER TABLE Tab_name Rename to New_tab_name
7, 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
8. CREATE table New_tab_name like Old_tab_name//Use an existing table to build a new table without the data of the old table

v. Backup and Recovery of data
Import External Data text:
1. Execute the external SQL script
Executing on current database: MySQL < input.sql
Execution on the specified database: MySQL [table name] < Input.sql
2. Data incoming commands load data local infile "[file name]" into table [table name];
Backing Up the database: (Dos)
Mysqldump--opt SCHOOL&GT;SCHOOL.BBB
mysqldump-u [user]-p [password] databasename > filename (Backup)
mysql-u [user]-p [Password] databasename < filename (recovery)

Vi. Unloading
Uninstall Mysql:sudo apt-get Remove Mysql-server mysql-client
sudo apt-get autoremove