Mysql Common Command Summary _mysql

MySQL installation directory
Database directory:/var/lib/mysql/
Configuration file:/usr/share/mysql (mysql.server command and configuration file)
Related commands:/usr/bin (mysqladmin mysqldump, etc. order)
Startup script:/etc/init.d/mysql (Directory of startup script files MySQL)

Second, System Management
Connect MySQL
Format: mysql-h host address-u user name-P user Password
Example 1: Connect to MySQL on this computer.

Copy Code code as follows:

hadoop@ubuntu:~$ Mysql-uroot-pmysql;

Example 2: Connect to MySQL on the remote host.

Copy Code code as follows:

hadoop@ubuntu:~$ mysql-h 127.0.0.1-uroot-pmysql;

Modify New Password
In the terminal input: Mysql-u username-p password, enter MySQL.

> Use MySQL;
> Update user Set Password=password (' New password ') where user= ' username ';
> Flush Privileges; #更新权限
> quit; #退出

Add new users
Format: Grant SELECT on database. * To User name @ Login host identified by ' password '
Example:
Example 1: Add a user test1 password to ABC, so that he can log on any host, and all databases have
Permissions to query, insert, modify, and delete. First connect the root user to MySQL, and then type the following command:

Copy Code code as follows:

Mysql>grant select,insert,update,delete on *.* to root@localhost identified by ' MySQL ';

Or

Copy Code code as follows:

Grant all privileges on *.* to root@localhost identified by ' MySQL ';

Then refresh the permission settings: Flush privileges;

Example 2: If you do not want to root password operation database "MyDB" in the data table, you can make another command to eliminate the password.

Copy Code code as follows:

Grant Select,insert,update,delete on mydb.* to root@localhost identified by ';

Delete User

hadoop@ubuntu:~$ mysql-u user name-p password mysql>delete from user
where user= ' username ' and host= ' localhost ';
Mysql>flush privileges;
Delete User's database
mysql>drop db dbname;

Third, database operation
Display all databases:mysql> show databases; (note: Last has an S)

Creating database:mysql> create DB test;

Connection database:mysql> use test;

View currently used databases:mysql> select database ();

Table information contained in the current database:mysql> show tables; (Note: Last has an S)

Delete database:mysql> drop DB test;

Four, table operation
Note: You should connect to a database using use < database name > before the operation.
Build Table command:CREATE table < table name > (< Field name 1> < type 1> [,.. < Field name N> < type n>];
Example:

Mysql> CREATE TABLE MyClass (
> ID int (4) NOT null primary key auto_increment,
> Name char (a) not null,
   > sex int (4) NOT null default ' 0 ',
> Degree double (16,2));

GET Table Structure command: desc table name, or Show columns from table name
Example:

Mysql> describe MyClass
mysql> desc MyClass;
Mysql> show columns from MyClass;

Delete Table command:drop table < table name >
For example, to delete a table named MyClass

Copy Code code as follows:

mysql> drop table MyClass;

Insert Data command: INSERT INTO< table name > [< field name 1>[,.. < field name n >]] VALUES (value 1) [, (value N)]
Example:

Copy Code code as follows:

mysql> INSERT INTO MyClass values (1, ' Tom ', 96.45), (2, ' Joan ', 82.99), (2, ' Wang ', 96.59);

Data in a query table
query All rows:mysql> select * from MyClass;

Query the first few lines of data
For example: View the first 2 rows of data in table MyClass

Copy Code code as follows:

Mysql> SELECT * from MyClass ORDER by ID limit 0, 2;

Or

Copy Code code as follows:

Mysql> select * from MyClass limit 0, 2;

Delete data command in table: Delete fromtable name where expression
For example: Delete a record in table MyClass that is numbered 1

Copy Code code as follows:

Mysql> Delete from MyClass where id=1;

modify Data command in table:Update table name SET field = new value,... WHERE condition

Copy Code code as follows:

mysql> Update MyClass set name= ' Mary ' where id=1;

add field command to table:ALTER TABLE name add field type other;
For example, a field passtest is added to the table MyClass, the type is int (4), and the default value is 0

Copy Code code as follows:

Mysql> ALTER TABLE MyClass add passtest int (4) default ' 0 '

Change table name command:Rename table name to new table name;
For example: In the table MyClass the name is changed to Youclass

Copy Code code as follows:

Mysql> Rename table MyClass to Youclass;

Update Field Content command:Update table name set field name = new Content
Update table name set field name = Replace (field name, ' old content ', ' new content ');
For example: Add 4 spaces before the article

Copy Code code as follows:

Update article Set Content=concat (", content);

V. Database import and Export
Export database files from the database using the "mysqldump" command, first enter the DOS interface, and then do the following.
1) Export all databases
format: mysqldump-u [database user name]-p-a>[save path for backup file]

2) Export data and structure
format: mysqldump-u [Database user name]-p [database name to be backed up]>[save path for backup file]
Example:
Example 1: Export the database mydb to the E:\MySQL\mydb.sql file.
Open Start-> run-> enter "cmd" and enter command line mode.
c:\> mysqldump-h localhost-u root-p mydb >e:\mysql\mydb.sql
Then enter the password and wait for the export to succeed and check for success in the target file.

Example 2: Export the mytable in the database mydb to the E:\MySQL\mytable.sql file.
c:\> mysqldump-h localhost-u root-p mydb mytable>e:\mysql\mytable.sql

Example 3: Export the structure of the database mydb to the E:\MySQL\mydb_stru.sql file.
c:\> mysqldump-h localhost-u root-p mydb--add-drop-table
Note:-h localhost can be omitted, it is generally used on the virtual host.

3 only export data do not export structure
Format:
mysqldump-u [Database user name]-p-t [database name to be backed up]>[save path for backup file]

4) Export events in the database
format: mysqldump-u [database user name]-P-E [Database username]>[save path to backup file]

5 Export stored procedures and functions in the database
format: mysqldump-u [database user name]-p-r [Database username]>[save path to backup file]

Import from an external file into a database
1) using the "source" command
first go to the MySQL command console, and then create the database, and then use the database. Finally, perform the following action.
Mysql>source [Save path for backup files]

2 Use "<" symbol
first go to the "MySQL" Command console, and then create the database, and then exit MySQL, into the DOS interface. Finally, perform the following action.
Mysql-u Root–p < [save path for backup files]

The above is the MySQL commonly used command summary, hope to be helpful to everybody's study.