MySQL operation in Ubuntu

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 user name-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 2, add a user test2 password for 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 data directly from the Internet
Library, which can only be accessed through a Web page on a MySQL host.
Grant Select,insert,update,delete on mydb.* to
[email protected] identified by "ABC";
If you do not want to test2 have a password, you can call another command to erase the password.
Grant
Select,insert,update,delete on mydb.* to [e-mail 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
To create a database named Data_name

Specify a character set when creating a database:

Create DATABASE test2 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

CREATE DATABASE ' test2 ' DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci

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 a 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 property, note if plus not NULL requires no data in the original field
Sql
SERVER200 is the following: ALTER TABLE table_name ALTER COLUMN col_name varchar (in) 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
Build a new table with an existing table, but not the data for 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

MySQL operation in Ubuntu