Shulou(Shulou.com)05/31 Report--

This article mainly explains "the common operation and skills of MySQL database". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the common operations and skills of MySQL database.

Using MySQL database, the most common and most important thing is database backup, so let's introduce database backup first. Database backup, and very formal database backup method, and other database servers have the same concept, but have not thought that MySQL will be easier to use file directory backup method, and soon have good (this method has not been verified by official documents, we temporarily call it an experiment).

Objective: to back up a mysql database TestA in the hostA host and restore it to the host B machine.

Test environment:

Operating system: WinNT4.0,Mysql3.22.34,phpMyAdmin2.1.0

Install mysql database and establish TestA database in hostA

Host B machine installs mysql database, no TestA database.

Methods and steps:

1. Start phpMyAdmin to view the list of databases in HostA and HostB. There is no TestA database in HostB.

2. Find the installation directory of mysql in HostA, and find the database directory data

3. In my experimental environment, this directory is C:mysqldata

4. Find the subdirectory C:mysqldataTestA of the corresponding database name

5. Paste and copy to the Data directory of HostB. The HostA is the same as the files in the HostBMysql data directory.

6. Refresh the phpMyAdmin of HostB and take a look at the database list. We can see that TestA has appeared and the operations such as query modification are normal, and the backup recovery is successful.

Test conclusion: the database of Mysql can be saved, backed up and restored in the form of files, as long as the corresponding file directory is restored, and there is no need to use other tools to back up.

Format: mysql-h host address-u username-p user password

1. Connect to the MYSQL on this machine.

First of all, open the DOS window, then enter the directory mysqlbin, then type the command mysql-uroot-p, enter and prompt you to enter the password. If you have just installed MYSQL, the superuser root does not have a password, so enter directly into the MYSQL. The prompt for MYSQL is: mysql ".

2. Connect to the MYSQL on the remote host. Suppose the IP of the remote host is 110.110.110.110, the user name is root, and the password is abcd123. Type the following command:

Mysql-h210.110.110.110-uroot-pabcd123 (Note: U and root can not add spaces, others are the same)

3. Exit the MYSQL command: exit (enter).

1. If you enter the order and find that you forgot to add the semicolon, you don't have to type the command again, just hit the semicolon and enter. In other words, you can type a complete command into several lines and finish it with a semicolon.

You can use the cursor up and down keys to call up the previous command. But an old version of MySQL that I used before doesn't support it. I am using mysql-3.23.27-beta-win now.

What are the common operations and skills of MySQL database

1. Display a list of databases:

Showdatabases

At the beginning, there were only two databases: mysql and test. The MySQL library is very important. it contains the system information of MYSQL. We actually use this library to change passwords and add users.

2. Display the data table in the library:

Usemysql;// opens the library. Those who have studied FOXBASE must be familiar with it.

Showtables

3. Display the structure of the data table:

Describe table name

4. Build the database:

Createdatabase library name

5. Create a table:

Use library name

Createtable table name (list of field settings)

6. Delete the library and the table:

Dropdatabase library name

Droptable table name

7. Clear the records in the table:

Deletefrom table name

8. Display the records in the table:

Select*from table name

Dropdatabaseifexistsschool;// delete if SCHOOL exists

Createdatabaseschool;// build library SCHOOL

Useschool;// opens the library SCHOOL

Createtableteacher// create table TEACHER

(

Idint (3) auto_incrementnotnullprimarykey

Namechar (10) notnull

Addressvarchar (50) default' Shenzhen'

Yeardate

); / / end of table creation

/ / the following are insert fields

Insertintoteachervalues (', 'glchengang',' Shenzhen No.1 Middle School', '1976-10-10')

Insertintoteachervalues (', 'jack',' Shenzhen No.1 Middle School', '1975-12-23')

Note: in the establishment of the table (1) set ID to the length of 3 of the number field: int; (2) and let it automatically add one for each record: auto_increment can not be empty: notnull and let him become the main field primarykey; (3) set NAME to the length of 10 character field; (4) set ADDRESS to the length of 50 character field, and the default value is Shenzhen. What's the difference between varchar and char? we'll have to wait for a later article. (5) set YEAR to the date field.

It's OK if you type the above command at the MySQL prompt, but it's not easy to debug. You can write the above command into a text as is, suppose it is school.sql, then copy it to c:\\, enter the directory\\ mysql\\ bin in the DOS state, and type the following command:

Mysql-uroot-p password "c:\\ school.sql"

If successful, leave a line without any display; if there is an error, there will be a prompt. The above command has been debugged, you just need to remove the comment from / / and you can use it.

VI. Change the password

Format: mysqladmin-u username-p old password password new password

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

Mysqladmin-uroot-passwordab12

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

2. Change the password of root to djg345.

Mysqladmin-uroot-pab12passworddjg345

(note: unlike the above, the following is a command in the MySQL environment, so it is followed by a semicolon as the command Terminator)

Format: grantselecton database. * to user name @ login host identifiedby\ "password\"

Example 1. Add a user's test1 password to abc, so that he can log in on any host and have the authority to query, insert, modify and delete all databases. First use the root user to connect to MySQL, and then type the following command:

Grantselect,insert,update

Deleteon*.*totest1@\ "%\" Identifiedby\ "abc\"

But the increase in the number of users in example 1 is very dangerous, if you want someone who knows the password of test1, then he can log in to your MySQL database on any computer on internet and can do whatever he or she wants with your data.

Example 2, add a user's test2 password to abc, so that he can only log in on localhost and query, insert, modify and delete the database mydb (localhost refers to the local host, that is, the host where the MySQL database is located), so that even if the user knows the test2 password, he can not access the database directly from the internet, but only through the web page on the MySQL host.

Grantselect,insert,update

Deleteonmydb.*totest2@localhostidentifiedby\ "abc\"

If you don't want test2 to have a password, you can issue another command to eliminate the password.

Grantselect,insert,update,deleteonmydb

. * totest2@localhostidentifiedby\ "\"

At this point, I believe that you have a deeper understanding of the "common operations and skills of MySQL database", you might as well come to the actual operation! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.