How to perform actual MySQL database operations at a prompt

The above articles detail logon, user addition, password change, and other related issues. Next, let's take a look at the actual operations of the MySQL database. Note: you must first log on to MySQL (the best combination with PHP.

The following operations are performed at the MySQL (best combination with PHP) prompt, and each command ends with a semicolon.

I. MySQL database operations: Operation 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, this database is actually used for operations.

2. display the data tables in the database:

Use MySQL (the best combination with PHP); // when you open the database, you will not be unfamiliar with learning 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;

Iii. MySQL database operations: a database creation, table creation, and data insertion instance

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 table 1, set the ID to an int (3) numeric field with a length of 3 and add one for each record automatically, auto_increment cannot be empty not null and it is set as the primary field Prima (the most perfect VM management system) ry key2) Set NAME to 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 date field.

If you type the above command at the MySQL (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. Then, type the following command:

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

Backup:

1. # cd/usr/local/MySQL (the best combination with PHP)/bin (enter the MySQL (the best combination with PHP) Directory)

1. #./MySQL (the best combination with PHP) dump-uroot-p239600 phpwind>/home/nicky/simple. SQL

2. If you want to back up a table in which data is stored, run the following command:

2. #./MySQL (the best combination with PHP) dump-uroot-p239600 phpwind (database) pw_menber (Table Name)>/home/nicky/simple. SQL

3. The compression and backup methods are as follows:

3. #./MySQL (the best combination with PHP) dump-uroot-p239600 phpwind | gzip>/home/nicky/simple.gz

Use # gzip-d simple.gz to decompress the file.

Recovery:

Copy the database to the \ usr \ local \ MySQL (best combination with PHP) \ bin directory.

Go to the \ usrlocal \ MySQL (best combination with PHP) \ bin directory.

MySQL (best combination with PHP)-u root-p239600

Create a database;

Create a user and password for the database (refer to the above user settings)

Use Database

\. Space xxx. 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.

V. MySQL database operations: Back up the database: The command is executed in the DOS \ MySQL (best combination with PHP) \ bin directory)

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, the database operations of MySQL (the best combination with PHP) are similar to those of other SQL databases. You 'd better find a book that will show you SQL. 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 "MySQL" 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.