Mysql common command summary

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 and other commands)
Startup scripts: / etc init d/mysql (mysql startup script file directory)

2. System management
Connection MySQL
Format: mysql-ES29en host address -u username -p user password
Example 1: Connect to MySQL on your own machine.

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

Example 2: Connect to MYSQL on a remote host.

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

Change the new password
Enter mysql-ES45en username -p password at the terminal and enter Mysql.

> use mysql;
> update user set password=PASSWORD(' The new password ') where user=' The user name ';
> flush privileges; # Update the permissions 
> quit; # exit 

Add new users
grant select on database.* to username @ login host identified by 'password'
For example:
Example 1: Add a user test1 whose password is abc so that he can log in on any host and have access to all databases
Query, insert, modify, delete permissions. First connect to MySQL for root user, then type the following command:

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

or

grant all privileges on *.* to root@localhost identified by 'mysql';

Then refresh the permissions Settings: flush privileges;

Example 2: If you do not want root to have a password to manipulate the table in the database "mydb", you can type another command to cancel the password.

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

Delete user

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

3. Database operation
Displays all databases: mysql > show databases; (Note: s at the end)

Create the database: mysql > create database test;

Connect to database: mysql > use test;

View the database currently in use: mysql > select database();

The current database contains table information: mysql > show tables; (Note: s at the end)

Delete database :mysql > drop database test;

4. The table operations
Note: Use "use" before the operation < The database name > You should connect to a database.
Build table command :create table < The name of the table > ( < The field name 1 > < Type 1 > [,.. < The field name n > < Type n > ]);
Example:

mysql> create table MyClass(
> id int(4) not null primary key auto_increment,
> name char(20) not null,
> sex int(4) not null default '0',
> degree double(16,2));

Gets the 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 < The name of the table >
For example: delete the table named MyClass

mysql> drop table MyClass;

Insert data command :insert into < The name of the table > [( < The field name 1 > [,.. < The field name n > ]] values (value 1)[, (value n)]
Example:

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

Query the data in the table
Query all rows: mysql > select * from MyClass;

Query the first few rows of data
For example: Look at the first 2 rows of table MyClass

mysql> select * from MyClass order by id limit 0,2;

or

mysql> select * from MyClass limit 0,2;

Delete table data command :delete from Table name where expression
For example: Delete the record numbered 1 in table MyClass

mysql> delete from MyClass where id=1;

Modify table data command :update table name set field = new value... where conditions

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

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

mysql> alter table MyClass add passtest int(4) default '0'

Change table name command :rename table to new table name;
For example: in table MyClass the name is changed to YouClass

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, put four Spaces in front of the article

update article set content=concat('    ', content);

5. Database import and export
Export the database file from the database using the "mysqldump" command, first enter the DOS interface, then do the following.
1) Export all databases
Format: mysqldump-ES254en [database user name] -ES255en-ES256en > [Save path for backup files]

2) Export data and data structure
Format: mysqldump-u [database user name] -p [database name to be backed up] > [Save path for backup files]
For example:
Example 1: Export the database mydb to the e:\MySQL\ mydb.sql file.
Open start - > Run - > Type "cmd" to enter command line mode.
c:\ > mysqldump -h localhost -u root -p mydb > e:\MySQL\mydb.sql
Then enter the password, wait for 1 will be exported successfully, you can go to the target file to check whether the success.

Example 2: Export mytable from 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 database mydb to the file e:\MySQL\ mydb_stru.sql.
c:\ > mysqldump -h localhost -u root -p mydb --add-drop-table > e:\MySQL\mydb_stru.sql
Note: -ES312en localhost can be omitted. It is generally used on the virtual host.

3) Only export data but not data structure
Format:
mysqldump-u [Database user name] - ES320en-t [Database name to be backed up] > [Save path for backup files]

4) Export Events in the database
Format: mysqldump-u [Database user name] - ES329en-ES330en [Database user name] > [Save path for backup files]

5) Export stored procedures and functions in the database
Format: mysqldump-u [Database user name] - p-ES338en [Database user name] > [Save path for backup files]

Import into the database from an external file
1) Use "source" command
First go to the "mysql" command console, then create the database, then use the database. Finally, do the following.
mysql > source [Save path for backup files]

2) use the" < "Symbol
First go to the "mysql" command console, then create the database, then exit MySQL and enter the DOS interface. Finally, do the following.
mysql - u root � p < [Save path for backup files]

Above is the summary of common Mysql commands, I hope to help you learn.