MySQL Beginners guide to use and introduction

mysql| Beginners
MySQL Beginners guide to use and introduction

One, the connection MySQL.
Format: mysql-h host address-u user name-P user Password
1, Example 1: Connect to MySQL on this machine.
First in the Open DOS window, and then into the directory Mysqlbin, and then type the command mysql-uroot-p, enter after the prompt you to lose the password, if just installed MySQL, superuser root is no password, so direct return can enter into MySQL, The MySQL prompt is: mysql>
2, Example 2: Connect to the remote host MySQL. Suppose the IP of the remote host is: 110
.110.110.110, the username is root and the password is abcd123. Type the following command:
Mysql-h110.110.110.110-uroot-pabcd123
(Note: U and root can be without spaces, others are the same)
3, exit MySQL command: Exit (enter)


Second, modify the password.
Format: Mysqladmin-u username-P Old password password new password
1, Example 1: To root add a password ab12. First in DOS into the directory mysqlbin, and then type the following command (password inside do not add a command character)
Mysqladmin-uroot Password AB12
Note: Since Root does not have a password at the beginning, the-p old password can be omitted.
2, Example 2: Then the root password changed to djg345.
MYSQLADMIN-UROOT-PAB12 Password djg345


Third, add new users. (Note: Unlike the above, the following are the commands in the MySQL environment, followed by a semicolon as the command Terminator)
Format: Grant Select on database. * To User name @ login host identified by "password"
Example 1, add a user test1 password for ABC, so that he can log on any host, and all databases have query, insert, modify, delete permissions. First connect the root user to MySQL, and then type the following command:
Grant Select,insert,update,delete on *.* to test1@ "%" identified by "ABC";
But for example 1 the increased user is very dangerous and you want someone who knows Test1 's password so that he can log on to your MySQL database on any computer on the Internet and can do whatever it wants with your data, as shown in Example 2.
Example 2, add a user test2 password for ABC, so that he can only log on the localhost, and can query the database mydb, insert, modify, delete operations (localhost refers to the local host, that is, the MySQL database of the host), This allows the user to use a password that knows test2, and he cannot access the database directly from the Internet, only through a Web page on the MySQL host.
Grant Select,insert,update,delete on mydb.* to Test2@localhost identified by "ABC";
If you do not want to test2 the password, you can make another command to eliminate the password.
Grant Select,insert,update,delete on mydb.* to Test2@localhost identified by "";





In the last article we talked about logging in, adding users, changing passwords, and so on. Next we look at MySQL in the database aspects of the operation. Note: You must first log in to MySQL, and the following actions are performed under the MySQL prompt, and each command ends with a semicolon.


First, the Operation skill
1, if you hit the command, when you find that you forgot to add a semicolon, you do not need to repeat the command, as long as a semicolon to enter on it. That is to say, you can divide a complete command into lines, and then use a semicolon as the closing sign to make it OK.
2, you can use the cursor up and down keys to pull up the previous command. But I used to use a MySQL older version does not support. I'm using a mysql-3.23.27-beta-win.

Second, display command
1. Display the list of databases.
show databases;
Only two databases were initially: MySQL and test. MySQL Library is very important it has MySQL system information, we change the password and new users, is actually using this library to operate.
2, display the data table in the library:
use MySQL;//open library, learned foxbase must be no stranger
Show tables;
3, display the structure of the data table:
describe table name;
4, build the library:
Create database library name;
5, the establishment of the table:
Use library name;
CREATE TABLE table name (field set list);
6, delete the database and delete the table:
drop Database library name;
The drop table table name;
7. Empty the record of the table:
Delete from table name;
8, display the records in the table:
SELECT * from table name;

Example of 三、一个 and table-building and inserting data
Drop database if exists school; Delete if there is school
Create Database School; Build a library School
Use school; Open Library School
CREATE TABLE teacher//Create tables Teacher
(
ID int (3) auto_increment NOT null primary key,
Name Char (a) NOT NULL,
Address varchar default ' Shenzhen ',
Year Date
); Build Table End
The following is the Insert field
Insert into teacher values (', ' Glchengang ', ' Shenzhen One ', ' 1976-10-10 ');
Insert into teacher values (', ' Jack ', ' Shenzhen One ', ' 1975-12-23 ');

Note: In the construction of the table

(1) Set the ID to a number field of length 3: Int (3) and let it automatically add one for each record: Auto_increment cannot be null: NOT NULL and make him the main field primary key

(2) Set name to a character field of length 10

(3) Set the address to a character field of length 50 and the default value is Shenzhen. What is the difference between varchar and char, only to wait for later articles to say.

(4) Set year as Date field.
If you type the above command at the MySQL prompt, it's not easy to debug. You can write the above command in a text file as if it were school.sql, then copy it to C:\ and enter the directory \mysql\bin in DOS, and then type the following command:
Mysql-uroot-p Password < C:chool.sql
If successful, there is no display on a single line, and if there is an error, there is a hint. (The above command has been debugged and you can use it only if you remove the comment.)

Iv. transferring text data to a database
1, the text data should conform to the format: The field data is separated by the TAB key, null value with \ n to replace.
Cases:
3 Rose Shenzhen II 1976-10-10
4 Mike Shenzhen one 1975-12-23
2, Data incoming command load infile "filename" into table name;
Note: You'd better copy the files to the \mysql\bin directory and use the using command to hit the library where the table resides.

V. BACKUP DATABASE: (command executes in DOS \mysql\bin directory)
Mysqldump--opt SCHOOL&GT;SCHOOL.BBB
Note: Back up the database school to the school.bbb file, school.bbb is a text file, file name, open to see if you will find something new.

PostScript: In fact, MySQL's operations on the database and other SQL class database is similar, you'd better find the book to read the SQL. I only introduce some basic, in fact, I only understand these, hehe. The best MySQL tutorial or "Yan Zi" translation of the "MySQL Chinese reference manual" not only free of all relevant sites have downloads, and it is the most authoritative. Unfortunately, it is not a CHM format like "PHP4 Chinese Manual", which is not convenient when looking for function commands.