MySQL basic commands

First, start and stop the MySQL Service

Net stop MySQL

Net start MySQL

Second, log on to MySQL

Syntax: mysql-u user name-P User Password

Enter the mysql-uroot-p command, press enter and prompt you to enter the password, enter 12345, and then press enter to enter mysql. The MySQL prompt is:

Mysql>

Note: If you are connecting to another machine, you need to add a parameter-H Machine IP address.

Third, add new users

Format: grant permission on database. * To username @ login host identified by "password"

For example, you can add a user user1 with the password password1 so that the user can log on to the machine and have the permission to query, insert, modify, and delete all databases. First, use the root user to connect to MySQL, and then type the following command:

Grant select, insert, update, delete on *. * To user1 @ localhost identified by "password1 ";

If you want the user to log on to MySQL on any machine, change localhost to "% ".

If you do not want user1 to have a password, you can run another command to remove the password.

Grant select, insert, update, delete on mydb. * To user1 @ localhost identified "";

Step 4: operate databases

Log on to MySQL and run the following commands at the MySQL prompt. Each Command ends with a semicolon.

1. display the Database List.

Show databases;

By default, two databases are available: MySQL and test. MySQL inventory contains the MySQL system and user permission information. We change the password and add users, in fact, this database is actually operated.

2. display the data tables in the database:

Use MySQL;

Show tables;

3. display the data table structure:

Describe table name;

4. Create and delete databases:

Create Database database name;

Drop database database name;

5. Create a table:

Use Database Name;

Create Table Name (Field List );

Drop table name;

6. Clear the table records:

Delete from table name;

7. display the records in the table:

Select * from table name;

Step 5: export and import data

1. Export data:

Mysqldump -- opt test> mysql. Test

Export the database test database to the mysql. Test file, which is a text file

For example, mysqldump-u root-p123456 -- databases dbname> MySQL. dbname

Export the database dbname to the mysql. dbname file.

2. import data:

Mysqlimport-u root-p123456 <MySQL. dbname.

No need to explain it.

3. Import text data to the database:

Field data of text data is separated by the tab key.

Use test;

Load data local infile "file name" into Table table name;

1: Use the show statement to find out the current database on the server:

Mysql> show databases;

2. Create a database named mysqldata

Mysql> Create Database mysqldata;

3: select the database you created

Mysql> Use mysqldata; (when you press the Enter key to see database changed, the operation is successful !)

4: view the tables in the current database

Mysql> show tables;

5. Create a database table

Mysql> Create Table mytable (name varchar (20), sex char (1 ));

6: display the table structure:

Mysql> describe mytable;

7. Add records to the table

Mysql> insert into mytable values ("hyq", "M ");

8: load data into database tables in text mode (for example, D:/mysql.txt)

Mysql> load data local infile "D:/mysql.txt" into Table mytable;

9: import the. SQL file command (for example, D:/MySQL. SQL)

Mysql> use database;

Mysql> source D:/MySQL. SQL;

10: delete a table

Mysql> drop table mytable;

11: Clear the table

Mysql> Delete from mytable;

12: Update table data

Mysql> Update mytable set sex = "F" where name = 'hyq ';

Posted on happytian read (6) Comments (0) EDIT favorites to 365key

13. Back up the database

Mysqldump-u root Database Name> XXX. Data

14: Example 2: connect to MySQL on the remote host

Assume that the IP address of the remote host is 110.110.110.110, the user name is root, and the password is abcd123. Enter the following command:

Mysql-h110.110.110.110-uroot-pabcd123

(Note: you do not need to add spaces for u and root. The same applies to others)

3. Exit MySQL command: exit (Press ENTER)

--------------------------

Change Password

Format: mysqladmin-u username-P old Password New Password

1. Add a password ab12 to the root user. First, enter the MySQL \ bin directory under DOS, and then type the following command
Mysqladmin-u root-Password ab12
Note: because the root account does not have a password at the beginning, the old-P password can be omitted.

2. Change the root password to djg345.
Mysqladmin-u root-P ab12 password djg345

(Note: Unlike the above, the following commands in the MySQL environment are followed by a semicolon as the command Terminator)

3. Change the root password through the command line:
Mysql> Update mysql. User SET Password = PASSWORD ('new password') where user = 'root ';
Mysql> flush privileges;

4. display the current user:
Mysql> Select User ();

The replace function in MySQL directly replaces a specific string in a field in the MySQL database. It is very convenient to use instead of writing a function. Replace ()

?

UPDATE `table_name` SET `field_name` = replace (`field_name`,'from_str','to_str') WHERE `field_name` LIKE '%from_str%'

First, start and stop the MySQL Service

Net stop MySQL

Net start MySQL

Second, log on to MySQL

Syntax: mysql-u user name-P User Password

Enter the mysql-uroot-p command, press enter and prompt you to enter the password, enter 12345, and then press enter to enter mysql. The MySQL prompt is:

Mysql>

Note: If you are connecting to another machine, you need to add a parameter-H Machine IP address.

Third, add new users

Format: grant permission on database. * To username @ login host identified by "password"

For example, you can add a user user1 with the password password1 so that the user can log on to the machine and have the permission to query, insert, modify, and delete all databases. First, use the root user to connect to MySQL, and then type the following command:

Grant select, insert, update, delete on *. * To user1 @ localhost identified by "password1 ";

If you want the user to log on to MySQL on any machine, change localhost to "% ".

If you do not want user1 to have a password, you can run another command to remove the password.

Grant select, insert, update, delete on mydb. * To user1 @ localhost identified "";

Step 4: operate databases

Log on to MySQL and run the following commands at the MySQL prompt. Each Command ends with a semicolon.

1. display the Database List.

Show databases;

By default, two databases are available: MySQL and test. MySQL inventory contains the MySQL system and user permission information. We change the password and add users, in fact, this database is actually operated.

2. display the data tables in the database:

Use MySQL;

Show tables;

3. display the data table structure:

Describe table name;

4. Create and delete databases:

Create Database database name;

Drop database database name;

5. Create a table:

Use Database Name;

Create Table Name (Field List );

Drop table name;

6. Clear the table records:

Delete from table name;

7. display the records in the table:

Select * from table name;

Step 5: export and import data

1. Export data:

Mysqldump -- opt test> mysql. Test

Export the database test database to the mysql. Test file, which is a text file

For example, mysqldump-u root-p123456 -- databases dbname> MySQL. dbname

Export the database dbname to the mysql. dbname file.

2. import data:

Mysqlimport-u root-p123456 <MySQL. dbname.

No need to explain it.

3. Import text data to the database:

Field data of text data is separated by the tab key.

Use test;

Load data local infile "file name" into Table table name;

1: Use the show statement to find out the current database on the server:

Mysql> show databases;

2. Create a database named mysqldata

Mysql> Create Database mysqldata;

3: select the database you created

Mysql> Use mysqldata; (when you press the Enter key to see database changed, the operation is successful !)

4: view the tables in the current database

Mysql> show tables;

5. Create a database table

Mysql> Create Table mytable (name varchar (20), sex char (1 ));

6: display the table structure:

Mysql> describe mytable;

7. Add records to the table

Mysql> insert into mytable values ("hyq", "M ");

8: load data into database tables in text mode (for example, D:/mysql.txt)

Mysql> load data local infile "D:/mysql.txt" into Table mytable;

9: import the. SQL file command (for example, D:/MySQL. SQL)

Mysql> use database;

Mysql> source D:/MySQL. SQL;

10: delete a table

Mysql> drop table mytable;

11: Clear the table

Mysql> Delete from mytable;

12: Update table data

Mysql> Update mytable set sex = "F" where name = 'hyq ';

Posted on happytian read (6) Comments (0) EDIT favorites to 365key

13. Back up the database

Mysqldump-u root Database Name> XXX. Data

14: Example 2: connect to MySQL on the remote host

Assume that the IP address of the remote host is 110.110.110.110, the user name is root, and the password is abcd123. Enter the following command:

Mysql-h110.110.110.110-uroot-pabcd123

(Note: you do not need to add spaces for u and root. The same applies to others)

3. Exit MySQL command: exit (Press ENTER)