MySQL Basic Operations Command

One, MySQL service operation
0, view the database version sql-> status;
1. net start MySQL//start MySQL service
2. net stop MySQL//stop MySQL service
3, Mysql-h host address-u user name-P user Password//enter MySQL database
4. Quit//quit MySQL operation
5, mysqladmin-u username-p Old password Password new password//change Password
6. Grant Select on database. * To User name @ login host identified by "password"//Add New user
exemple:
Example: Add a user test2 password to ABC, so that he can only login on localhost, and can query, insert, modify, delete the database mydb operation

(localhost refers to the local host, the host where the MySQL database resides), so that the user knows the test2 password, and he cannot access the database directly from the Internet.

only accessible through a Web page on a MySQL host).

If you don't want the test2 to have a password, you can use a command to erase the password.
grant  select,insert,update, Delete  on  mydb.*  to  [email protected]  identified  by "";

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 (+),
pwd varchar (+)
) 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 type varchar
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>SCHOOL.BBB
mysqldump-u [user]-p [password] databasename > filename (Backup)
mysql-u [user]-p [Password] databasename < filename (recovery)

Vi. Unloading
To uninstall MySQL:

sudo apt-get remove Mysql-server mysql-client
sudo apt-get autoremove

MySQL Basic Operations Command