Hadoop Cluster (10th edition supplement) _ Common MySQL database commands

1, System Management 1.1 connection MySQL

format : mysql-h host address-u user name-P user Password

Example :

Example 1: Connect to MySQL on this computer.

First open the DOS window, and then enter the directory Mysqlbin, and then type the command "Mysql–u root–p", enter after the prompt you lose password, if just installed MySQL, super user "root" is no password, so directly enter into MySQL, The prompt for MySQL is: mysql>.

Example 2: Connect to MySQL on a remote host. Assume the remote host IP is: 110.110.110.110, the user name is root, the password is abcd123. Type the following command:

Mysql-h 110.110.110.110-u root–p abcd123

Note : You and root can not add spaces, the other is the same.

Exit MySQL command: exit (Enter).

1.2 Modify New Password

format : mysqladmin-u username-P Old password password new password

Example :

Example 1: Add a password to root ab12. First enter directory Mysqlbin under DOS, and then type the following command:

Mysqladmin-u Root-password AB12

Note : Because Root does not have a password at the beginning, the-p old password can be omitted.

Example 2: Then change the root password to djg345.

Mysqladmin-u root-p ab12 Password djg345

1.3 Adding new users

Note : Unlike the above, the following is a command in the MySQL environment, so it is followed by a semicolon ";" As a command terminator.

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 query, insert, modify, delete permissions. First, use the root user to connect to MySQL, and then type the following command:

Grant Select,insert,update,delete on * * to [e-mail protected] identified by ' ABC ';

Or

Grant all privileges on * * to [e-mail protected] identified by ' ABC ';

Then refresh the permission settings.

Flush privileges;

Example 2: If you do not want to test2 have the password operation database "MyDB" in the data table, you can call another command to erase the password.

Grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by ';

1.4 Start Stop MySQL

1) under Windows environment

First enter the DOS environment, and then do the following.

• Start the service

net start MySQL

• Stop Service

net stop MySQL

2) under Linux environment

• Start the service

Service MySQL Start

• Stop Service

Service MySQL Stop

2. Database Operation 2.1 Library operation

1) Create a database

command : Create databases < database name >

For example , create a database named XHKDB

mysql> CREATE DATABASE xhkdb;

2) Show all the databases

command : show databases ( note : There is a last s)

mysql> show databases;

3) Delete Database

command : Drop databases < database name >

Example : Delete a database named Xhkdb

mysql> drop Database xhkdb;

4) Connect to the database

command : Use < database name >

For example : If the XHKDB database exists, try to access it

mysql> use XHKDB;

Screen tip: Database changed

5) View the currently used database

Mysql> Select Database ();

6) The table information contained in the current database

Mysql> Show tables; ( Note : There is a last s)

2.2 Table operation

Note : You should connect to a database by using "Use< database name >" before operation.

1) Build Table

command : CREATE table < table name > (< Field name 1> < type 1> [,.. < Field name N> < type n>]);

Mysql> CREATE TABLE MyClass (

> ID int (4) NOT null primary key auto_increment,

> Name char () NOT NULL,

> Sex int (4) NOT null default ' 0 ',

> Degree double (16,2));

2) Get table structure

command : Desc table name, or Show columns from table name

Mysql>describe MyClass

mysql> desc MyClass;

Mysql> show columns from MyClass;

3) Delete Table

command : Drop table < table name >

example : Deleting a table with a table named MyClass

mysql> drop table MyClass;

4) Inserting data

command : INSERT into < table name > [(< Field name 1>[,.. < field name n >])] VALUES (value 1) [, (value N)]

For example : Insert two records into table MyClass, these two records indicate that: 1 is named Tom with a score of 96.45, 2 for the named Joan, and 82.99 for the number 3.

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

5) Querying the data in the table

• Querying all Rows

command : Select < Field 1, field 2,...> from < table name > where < expression >

Example : View all data in table MyClass

Mysql> select * from MyClass;

• Querying the first few rows of data

example : Viewing the first 2 rows of data in a table MyClass

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

Or

Mysql> select * from MyClass limit 0, 2;

6) Delete data from the table

command : Delete from table name where expression

example : Deleting a record with number 1 in table MyClass

Mysql> Delete from MyClass where id=1;

7) Modify the data in the table

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

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

8) Add a field to the table

command : ALTER TABLE name add field type other;

For example : Added a field passtest in table MyClass, type int (4), default value of 0

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

9) Change the table name

command : Rename table name to new table name;

For example , change the name of the table MyClass to Youclass

Mysql> Rename table MyClass to Youclass;

10) Update Field contents

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 in front of the article

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

3. Database import and Export 3.1 export database files from database

Using the "mysqldump" command

First, go to the DOS interface and do the following.

1) Export all databases

format : mysqldump-u [Database user name]-p-a>[save path to backup file]

2) Export data and structure

format : mysqldump-u [Database user name]-p [database name to be backed up]>[backup file save path]

Example :

Example 1: Export the database mydb to a e:\MySQL\mydb.sql file.

Open start à run à input "cmd" and enter command line mode.

c:\> mysqldump-h localhost-u root-p mydb >e:\mysql\mydb.sql

Then enter the password, wait for an export to succeed, you can check the target file for success.

Example 2: Export mytable from 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 a 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, it is generally used on the virtual host.

3) Export data structure only

format : mysqldump-u [Database user name]-p-t [the name of the database to be backed up]>[the save path to the backup file]

4) Export the events in the database

format : mysqldump-u [Database user name]-p-e [Database user name]>[save path to backup file]

5) exporting stored procedures and functions in the database

format : mysqldump-u [Database user name]-p-r [Database user name]>[save path to backup file]

3.2 Importing the database from an external file

1) Use the "source" command

First go to the MySQL command console, then create the database, and then use the database. Finally, perform the following actions.

Mysql>source [Save path of backup file]

2) Use the "<" symbol

First go to the "MySQL" command console, then create the database, then exit MySQL and enter the DOS interface. Finally, perform the following actions.

Mysql-u Root–p < [save path of backup file]

Hadoop Cluster (10th edition supplement) _ Common MySQL database commands