Mysql Series (6) Basics for Beginners

Connect to MySQL

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

1． Example 1: Connect to MYSQL on this machine.

First open the DOS window, then enter the directory mysqlbin, and then type the command mysql -uroot -p. After pressing Enter, you will be prompted to enter your password. If MYSQL has just been installed, the super user root does not have a password, so directly Press Enter to enter MySQL. The MySQL prompt is: mysql>

2. Example 2: Connect to MYSQL on the remote host. Assume that the IP of the remote host is: 110.110.110.110, the user name is root, and the password is abcd123. Then type the following command:

mysql -h110.110.110.110 -uroot -pabcd123

(Note: u and root do not need to add spaces, and the same applies to others)

3 ． Exit MySQL command: exit (Enter)

Note: If you want to successfully connect to the remote host, you need to open MySQL remote access permission on the remote host

The method is as follows:

In Enter the remote host as an administrator

Enter the following command

mysql>GRANT ALL PRIVILEGES ON *.* TO 'agui'@%'IDENTIFIEDBY '123' WITH GRANT OPTION;

FLUSH PRIVILEGES;

//Give any host permission to access data

mysql>FLUSH PRIVILEGES

//Modification takes effect

agui for us The username

password used is 123

that is: after setting the settings on the remote host, we can connect to the remote host through mysql -h110.110.110.110 -uagui -p123

Change password

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

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

mysqladmin -uroot -password ab12

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

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

mysqladmin -uroot -pab12 password djg345

Add new user

(Note: Different from the above, the following is a command in the MySQL environment , so there is a semicolon after it as the command terminator)

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

Example 1. Add a user The password of test1 is abc, which allows him to log in on any host and has query, insert, modify, and delete permissions on all databases. First connect to MySQL as the root user, and then type the following command:

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

But the user added in Example 1 is very dangerous. If someone knows the password of test1, then he can log in to your mysql database on any computer on the Internet and do whatever he wants with your data. Solution See example 2.

Example 2, add a user test2 with the password abc, so that he can only log in on localhost, and can query, insert, modify, and delete the database mydb (localhost refers to the local host, that is, the MYSQL database The host where it is located), so that even if the user knows the password of test2, he cannot directly access the database from the Internet, and can only access it through the web page on the MYSQL host.

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

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

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

Let’s take a look at the database operations in MySQL. Note: You must first log in to MySQL. The following operations are performed at the MySQL prompt, and each command ends with a semicolon.

Operation skills

1． If you find that you forgot to add a semicolon when typing a command and press Enter, you do not need to type the command again. You only need to add a semicolon and press Enter. That is to say, you can divide a complete command into several lines and use a semicolon as the end mark.

2. You can use the cursor up and down keys to recall previous commands. But an old version of MySQL I used before does not support it. I am using mysql-3.23.27-beta-win.

Display command

1． Display the database list.

show databases;

There were only two databases at the beginning: mysql and test. The mysql library is very important. It contains MySQL system information. When we change passwords and add new users, we actually use this library for operations.

2. Display the data tables in the library:

use mysql; //Open the library, it will be familiar to those who have learned FOXBASE

show tables;

3. Display the structure of the data table:

describe table name;

4. Create database:

create database library name;

5． Create table:

use library name;

create table table name (field setting list);

6. Delete database and table:

drop database database name;

drop table table name;

7. Clear the records in the table:

delete from table name;

8. Display records in the table:

select * from table name;

9. Display errors, warnings and notifications generated by the last executed statement:

show warnings;

10. Only the errors generated by the last executed statement are displayed:

show errors;

Instance

drop database if exists school; //If SCHOOL exists Then delete

create database school; //Create library SCHOOL

use school; //Open library SCHOOL

create table teacher //Create table TEACHER

(

id int(3) auto_increment not null primary key,

name char(10) not null,

address varchar(50) default 'Shenzhen',

year date

); //End of table creation

//The following are the inserted fields

insert into teacher values('','glchengang','XX company', '1976-10-10');

insert into teacher values('','jack','XX company','1975-12-23');

Note: in Creating table:

1． Set the ID to a numeric field of length 3: int(3) and let each record automatically increase by one: auto_increment cannot be empty: not null and make it the primary field primary key.

2. Set NAME to a character field of length 10.

3. Set ADDRESS to a character field of length 50, and the default value is Shenzhen. What is the difference between varchar and char? I will have to wait for a future article to talk about it.

4. Set YEAR as the date field.

If you type the above command at the mysql prompt, it is OK, but it is not convenient for debugging. You can write the above command as it is into a text file, assuming it is school.sql, then copy it to c:\, enter the directory \mysql\bin in DOS state, and then type the following command:

mysql - uroot -p password< c:\school.sql

If successful, a blank line will be left blank; if there is an error, there will be a prompt. (The above command has been debugged, you only need to remove the // comment to use it).

Text to database

1. The text data should conform to the format: field data are separated by tab keys, and null values ​​are replaced by \n.

Example:

3. rose Shenzhen No. 2 Middle School 1976-10-10

4． mike Shenzhen No. 1 Middle School 1975-12-23

2． Data input command load data local infile "file name" into table table name;

Note: You'd better copy the file to the \mysql\bin directory, and first use the use command to open the database where the table is located

Or transfer (cd) the path to the path where the text is to be imported before logging in to the database.

Backup database

(The command is executed in the \mysql\bin directory of DOS)

mysqldump --opt school>school.bbb

Note: Back up the school database to the school.bbb file. School.bbb is a text file. You can choose any file name. Open it and see if you will find new things.

Method to automatically back up mysql database

1. First create a batch file and save the following code as a .bat file. The file name is preferably in English. Pay attention to the path below. Taking the author's own database as an example, the database is installed under mysql\mysql under the D drive. The backup location is F:\beifen. The following code is the date [8].

@echo off[9]

color 0D

MODE con: COLS=71 LINES=25

title mysql database automatic backup script (task plan )--Script author: http://www. ***. com

set sou_dir="D:\mysql\Mysql\data"

set obj_dir=F:\beifen\%date:~0,10%

net stop mysql

md %obj_dir%

xcopy /e /y %sou_dir% %obj_dir%

net start mysql

@echo off&setlocal enabledelayeexpansion

call:D,30

echo. The date 30 days ago is: %D%

echo. Delete the backup 30 days ago...

if exist F:\beifen\%D% rd /s /q F:\beifen\%D%

echo The automatic backup is completed and the program will automatically exit...

Restore/Import

Restore/Import database [10] During the import process, enter the mysql database console, such as mysql -u root -p

mysql>use database

Then use the source command, and the following parameters are script files (such as .sql used here)

mysql>source d:\dbname.sql

If the prompt cannot be found file, after inputting the source, you can use the mouse to drag the file directly into the command line window

Start the tracking file

mysqld --debug

Shut down the server

mysqladmin -u root shutdown

Start the MySQL service

mysqld --console

The above is the basic content of Mysql series (6) for beginners. For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!