Mysql database MYSQL database beginners guide

There are many friends who have installed mysql but don't know how to use it. In this article, we will learn some common MYSQL commands from connecting to MYSQL, changing passwords, adding users, etc.
1. 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 you can enter directly by pressing Enter. Now in MYSQL, the MYSQL prompt is: //from www.w3sky.com
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, the same applies to others)
3. Exit the MYSQL command: //from www.w3sky.com
exit (Enter)
2. 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
3. Add new users. (Note: Unlike the above, the following are commands in the MYSQL environment, so they are followed by a semicolon as the command terminator)
Format:
grant select on database.* to username@login host identified by "password"
Example 1. Add a user test1 with the password abc, so that he can log in on any host and have query, insert, modify, and delete permissions on all databases. First connect to MYSQL as the root user, and then type the following commands:
grant select,insert,update,delete on *.* to test1@"%" Identified by "abc";
But the added users in Example 1 are very dangerous , if you want someone to know 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 information. See Example 2 for the solution.
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 host where the MYSQL database 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 "";
In the previous article we talked about login, adding users, password changes and other issues. In the next article, we will 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.
1. Operation skills
1. If you forget to add a semicolon after pressing Enter when typing a command, you don’t need to type the command again, just type a semicolon and press Enter. In other words, 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 call up previous commands. But an old version of MYSQL that I used before does not support it. What I am using now is:
mysql-3.23.27-beta-win.
2. 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 //from www.w3sky.com
show tables;
3. Display the structure of the data table:
describe table name;
4. Create database:
create database database name;
5. Create table:
use database 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;
3. One database and table creation And instances of inserting data
drop database if exists school; //If SCHOOL exists, delete it from www.w3sky.com
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
//Following To insert fields
insert into teacher valuess(,glchengang,Shenzhen No.1 Middle School,1976-10-10);
insert into teacher valuess(,jack,Shenzhen No.1 Middle School,1975-12-23);
Note: Table under construction (1) Set the ID to a numeric field with a length of 3: int (3) and let each record automatically add one: auto_increment cannot be empty: not null and make it the main field primary key (2) Set the NAME Set ADDRESS to a character field of length 50 for a character field of length 10 (3), 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.
You can also type the above command at the mysql prompt, 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 mysqlbin in DOS state, and then type the following command:
mysql -uroot -p password < c:school .sql
If successful, a blank row will be left without any display; 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).
4. Transfer text data to the database
1. The format that text data should conform to: 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 mysqlbin directory, and use it first Use command to print the library where the table is located.
5. Back up the database: (The command is executed in the mysqlbin directory of DOS)
mysqldump --opt school>school.bbb
Note: Back up the database school to the school.bbb file, school.bbb is a text file, the file name is arbitrary Take it, open it and see what new discoveries you will make.
Postscript: In fact, the operation of MYSQL database is similar to that of other SQL databases. You'd better find a book about SQL. I only introduce some basic ones here. In fact, that’s all I know, haha. The best MYSQL tutorial is the "MYSQL Chinese Reference Manual" translated by "Yan Zi". It is not only free and available for download on every relevant website, but it is also the most authoritative. Unfortunately, it is not in chm format like "PHP4 Chinese Manual", which is not convenient when searching for function commands

The above introduces the Mysql database MYSQL database beginner's guide, including the Mysql database content. I hope it will be helpful to friends who are interested in PHP tutorials.