Mysql Common commands

Mysql installation directory
Database directory
/Var/lib/mysql/
Configuration File
/Usr/share/mysql (mysql. server command and configuration file)
Related commands
/Usr/bin (commands such as mysqladmin mysqldump)
Start script
/Etc/init. d/mysql (directory for starting the script file mysql)

System Management
Connect to MySQL
Format: mysql-h host address-u user name-p User Password
Example 1: connect to MySQL on the local machine.
Hadoop @ ubuntu :~ $ Mysql-uroot-pmysql;

Example 2: connect to MYSQL on the remote host.
Hadoop @ ubuntu :~ $ Mysql-h 127.0.0.1-uroot-pmysql;

Change New Password
Enter mysql-u username-p password on the terminal, and press enter to enter Mysql.
> Use mysql;
> Update user set password = PASSWORD ('new password') where user = 'username ';
> Flush privileges; # update Permissions
> Quit; # exit

Add new users
Format: grant select on database. * to username @ login host identified by 'Password'
Example:
Example 1: Add a user test1 whose password is abc so that he can log on to any host and
Query, insert, modify, and delete permissions. First, use the root user to connect to MySQL, and 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 permission settings.
Flush privileges;

Example 2: If you do not want the root user to have a password to operate the data table in the Database "mydb", you can run another command to remove the password.
Grant select, insert, update, delete on mydb. * to root @ localhost identified '';

Delete a user
Hadoop @ ubuntu :~ $ Mysql-u username-p Password
Mysql> delete from user where user = 'username' and host = 'localhost ';
Mysql> flush privileges;
// Delete the user's database
Mysql> drop database dbname;

Database Operations
Show all databases
Mysql> show databases; (Note: There is a last s)

Create a database
Mysql> create database test;

Connect to database
Mysql> use test;

View the currently used database
Mysql> select database ();

Table information contained in the current database
Mysql> show tables; (Note: There is a last s)

Delete Database
Mysql> drop database test;

Table operations
Note: before the operation, use "use <Database Name>" to connect to a database.
Create a 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 (20) 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, delete a table named MyClass.
Mysql> drop table MyClass;

Insert data
Command: insert into <Table Name> [(<field name 1> [,... <field name n>])] values (value 1) [, (value n)]
Example:
Mysql> insert into MyClass values (1, 'Tom ', 96.45), (2, 'job', 82.99), (2, 'wang', 96.59 );

Query table data
Query all rows
Mysql> select * from MyClass;

Query the first few rows of data
For example, view the first two rows of data in the MyClass table.
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 MyClass.
Mysql> delete from MyClass where id = 1;

Modify Table Data
Command: update table name set field = new value,... where Condition
Mysql> update MyClass set name = 'Mary 'where id = 1;

Add fields to the table
Command: alter table name, add, other field types;
For example, a passtest field is added to the MyClass table. The type is int (4) and the default value is 0.
Mysql> alter table MyClass add passtest int (4) default '0'

Change table name
Command: rename table original table name to new table name;
For example, the MyClass name in the table 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, four spaces are added before the article.
Update article set content = concat ('', content );

Import and Export Databases
Export Database files from a database
Use the "mysqldump" command
First, go to the DOS interface and perform the following operations.
1) Export all databases
Format: mysqldump-u [Database User Name]-p-A> [backup file storage path]

2) export data and data structure
Format: mysqldump-u [Database User Name]-p [name of the database to be backed up]> [storage path of the backup file]
Example:
Example 1: export the database mydb to the e: \ MySQL \ mydb. SQL file.
Open start> RUN> Enter cmd to Enter command line mode.
C: \> mysqldump-h localhost-u root-p mydb> e: \ MySQL \ mydb. SQL
Enter the password and wait for a moment until the export is successful. You can check whether the export is successful in the target file.

Example 2: Export mytable in 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 database mydb structure to the e: \ MySQL \ mydb_stru. SQL file.
C: \> mysqldump-h localhost-u root-p mydb -- add-drop-table> e: \ MySQL \ mydb_stru. SQL
Note:-h localhost can be omitted, which is generally used on a VM.

3) only export data without exporting the Data Structure
Format:
Mysqldump-u [Database User Name]-p-t [name of the database to be backed up]> [storage path of the backup file]

4) export the Events in the database
Format: mysqldump-u [Database User Name]-p-E [Database User Name]> [backup file storage path]

5) Export stored procedures and functions in the database
Format: mysqldump-u [Database User Name]-p-R [Database User Name]> [backup file storage path]

Importing data from external files to the database
1) use the "source" command
First, go to the "mysql" command console, create a database, and then use the database. Finally, perform the following operations.
Mysql> source [backup file storage path]

2) use the "<" symbol
First, go to the "mysql" command console, create a database, and then exit MySQL to enter the DOS interface. Finally, perform the following operations.
Mysql-u root-p <[backup file storage path]