Basic usage of MySQL Databases

The following articles mainly introduce the basic usage of the MySQL database. Many people do not know how to use the database correctly after installing the MySQL database, the following is a specific solution to solve this problem. I hope you will gain some benefits after browsing it.

Many of my friends have installed the MySQL database (the best combination with PHP) but do not know how to use it. In this article, we will learn some MySQL (the best combination with PHP), change the password, and add users) common commands.

1. Connect to MySQL (the best combination with PHP ).

Format: MySQL (best combination with PHP)-h host address-u user name-p User Password

1. Example 1: connect to MySQL on the local machine (the best combination with PHP ).

First, open the DOS window, enter the directory MySQL (the best combination with PHP) bin, and then enter the command MySQL (the best combination with PHP)-uroot-p, after you press enter, you are prompted to enter the password. If you have just installed MySQL (the best combination with PHP), the Super User root has no password, so press enter to enter MySQL (the best combination with PHP). The prompt for MySQ database L (the best combination with PHP) is: mySQL (the best combination with PHP)>

2. Example 2: connect to MySQL on the remote host (the best combination with PHP ). 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 (best combination with PHP)-h110.110.110.110-uroot-pabcd123

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

3. exit MySQL (the best combination with PHP) command: exit and press Enter)

2. Change the password.

Format: MySQL (best combination with PHP) admin-u username-p old password New password

1. Example 1: Add a password ab12 to the root user. First, enter the MySQL directory (the best combination with PHP) bin in DOS, and then type the following command

MySQL (best combination with PHP) admin-uroot-password ab12

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

2. Example 2: Change the root password to djg345.

MySQL (best combination with PHP) admin-uroot-pab12 password djg345

3. Add new users.

Note: Unlike the above, the following commands are used in the MySQL database (the best combination with PHP) environment, so a semicolon is followed as the command Terminator)

Format: grant select on database. * to username @ login host identified by "password"

Example 1: Add a user named "test1" with the password "abc" so that the user can log on to any host and have the permission to query, insert, modify, and delete all databases. First, use the root user to connect to MySQL (the best combination with PHP), and then type the following command:

 
 

  
  
grant select,insert,update,delete on *.* to test1@"%" Identified by "abc";  
 
 

However, the User Added in Example 1 is very dangerous. If someone knows the password of test1, then he can log on to your MySQL database (the best combination with PHP) on any computer on the internet and do whatever he wants for your data. For the solution, see Example 2.

Example 2: Add a user named "test2" with the password "abc" so that the user can only log on to localhost, you can also query, insert, modify, and delete the database mydb. localhost refers to the local host, that is, the host where the MySQL (best combination with PHP) database is located ), in this way, the user knows the password test2 and cannot directly access the database from the internet. He can only access the web page on the host through MySQL (the best combination with PHP.

 
 

  
  
grant select,insert,update,delete on mydb.* to test2@localhost identified by "abc";  
 
 

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

 
 

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

Let's take a look at the database-related works in the MySQL database (the best combination with PHP. Note: you must first log on to MySQL (the best combination of PHP and MySQL). The following operations are performed at the MySQL (the best combination of PHP and MySQL) prompt, each Command ends with a semicolon.

I. Good work skills

1. If you forget the extra points after you press Enter when making the command, you don't have to repeat the command. You just need to press a semicolon to press Enter. That is to say, you can divide a complete command into several lines, and then use a semicolon as the end sign to complete the operation.

2. You can use the cursor to bring up or down the previous commands. However, the old version of MySQL (the best combination with PHP) that I used previously does not support. I am using MySQL (the best combination with PHP)-3.23.27-beta-win.

Ii. Display commands

1. display the Database List.

 
 

  
  
show databases;  
 
 

At the beginning, there were only two databases: MySQL (the best combination with PHP) and test. MySQL (the best combination with PHP) library is very important, it contains MySQL (the best combination with PHP) system information, we change the password and add users, in fact, this database is used for better work.

2. display the data tables in the database:

Use MySQL database (the best combination with PHP); // you can open the database and learn FOXBASE.

 
 

  
  
show tables;  
 
 

3. display the data table structure:

Describe table name;

4. database creation:

Create database name;

5. Create a table:

Use Database Name;

Create table Name (field setting list );

6. Delete databases and tables:

Drop database name;

Drop table name;

7. Clear records in the table:

Delete from table name;

8. Display records in the table:

Select * from table name;

3. An instance for creating a database, creating a table, and inserting data

Drop database if exists school; // Delete if SCHOOL exists

Create database school; // create a database SCHOOL

Use school; // open the SCHOOL library

Create table teacher // create table TEACHER

(

Id int (3) auto_increment not null Prima (the most complete VM management system) ry key,

Name char (10) not null,

Address varchar (50) default 'shenzhen ',

Year date

); // Table creation ends

// Insert fields as follows

 
 

  
  
Insert into teacher values ('', 'glengang ', 'shenzhen Zhongyi', '2017-10-10 ');
  
  
Insert into teacher values ('', 'jack', 'shenzhen Zhongyi ', '2017-12-23 ');
 
 

Note: In the table in progress, 1) set the ID to a numeric field of 3: int (3) and make it automatically add one: auto_increment for each record. It cannot be blank: not null and set it to the master field Prima (the most perfect VM management system) ry key2) set NAME to the character field 3 with a length of 10) set ADDRESS to a 50-character field, and the default value is Shenzhen. What is the difference between varchar and char? It will only be discussed later. 4) set YEAR as the period field.

If you type the above command at the MySQL database (the best combination with PHP) Prompt, it is not convenient for debugging. You can write the above commands into a text file as they are. SQL, then copy it to c: \, and enter \ MySQL (the best combination with PHP) \ bin in the DOS state, and then type the following command:

MySQL (best combination with PHP)-uroot-p password <c: \ school. SQL

If it succeeds, no display is displayed for a blank row. If there is an error, a prompt is displayed. The preceding command has been debugged. You only need to remove the // annotation to use it ).

4. Transfer text data to the database

1. Text data should conform to the format: field data is separated by the tab key, and the null value is replaced by \ n.

Example:

3 rose:

4 mike: Shenzhen No. 1,-12-23

2. data Import command load data local infile "file name" into table name;

Note: You 'd better copy the file to the \ MySQL (best combination with PHP) \ bin directory and use the use command to create the database where the table is located.

5. Back up the database: run the command in the \ MySQL (the best combination with PHP) \ bin directory of DOS)

MySQL (best combination with PHP) dump -- opt school> school. bbb

Note: Back up the database school to the school. bbb file. school. bbb is a text file with any file name. Open it and you will find new discoveries.

Note: In fact, MySQL (the best combination with PHP) is similar to other SQL databases in terms of database performance. You 'd better read the SQL book here. Here I will only introduce some basic things. In fact, I only understand these things.

The best MySQL (the best combination with PHP) tutorial is the "MySQL Database" Translated by "Yan Zi" (the best combination with PHP) the Chinese Reference Manual "is not only free to download from every relevant website, but also the most authoritative. It is a pity that the chm format is not like the "PHP4 Chinese manual", and it is not convenient to search for function commands.