There are many friends who have installed mySQL but I don't know how to use it. In this article we learn some of the common commands of MySQL from connecting MySQL, modifying passwords, increasing users.
First, connect mysql.
Format: mysql -h host address -u username -P user password
1, Example 1: Connect to MySQL on the unit.
First open the DOS window, then enter the directory mysqlbin, type the command mysql -uroot -p, then prompt you to lose your password, if you just install MySQL, the superuser root is no password, so you can enter it directly. MySQL, MySQL prompts are: mysql>
2, Example 2: Connect to MySQL on the remote host. Suppose the IP of the remote host is: 110.110.110.110, the username is root, the password is ABCD123. Type the following command:
mysql -h110.110.110.110 -uroot -Pabcd123
(Note: u and root do not have to bind the body, 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: Add a password to the root AB12. First enter the directory mysqlbin under DOS, then type the following command
MySQLADMIN -UROOT -PASSWORD AB12
Note: Because the root does not have a password, the -p old password can be omitted.
2, Example 2: Change the root password to DJG345.
mysqladmin -uroot -Pab12 Password DJG345
Third, add new users. (Note: Different from the above, because it is the command in the mysql environment, then take a semicolon as the end of the command)
Format: Grant SELECT ON Database. * To Username @ Login Host Identified by "Password"
Example 1. Add a user TEST1 password to ABC, allowing him to log in on any host and have the permissions of all databases, insert, modify, delete. First, use the root user into MySQL, then type the following command:
Grant SELECT, INSERT, UPDATE, DELETE ON *. * to Test1 @ "%" Identified by "ABC";
However, the user of Example 1 is very dangerous. 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 to your data for what you want, solution. See Example 2.
Example 2, add a user TEST2 password to ABC, let him log in in localhost, and can query, insert, modify, and delete the database MYDB (localhost refers to the host host, the host where the mysql database is located) In this way, users can use the password that knows Test2, he cannot access the database directly on the Internet, and can only access 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 with a password, you can make a command to remove the password.
Grant SELECT, INSERT, UPDATE, DELETE ON MYDB. * To Test2 @ localhost iDentified by ""; (Part 2) In the upper part, we told logins, increase users, password changes, etc. Next Let's take a look at the operations in MySQL. Note: You must first log in to MySQL first, the following operations are performed at the MySQL prompt, and each command ends in a semicolon.
I. Operation skills 1, if you are playing a command, then I found that I forgot to add the semicolon, you don't have to play a command, as long as you have a semicolon, you can go. That is to say, you can divide a complete command into a few lines, and then use the semicolon to end the flag OK. 2, you can use the cursor upside button to call up the previous command. But I used a MySQL old version I have not supported. I am using mysql-3.23.27-beta-win.
Second, display command 1, display the database list. SHOW DATABASES; two databases just started: mysql and test. The mysql library is very important that there is a MySQL system information, we change your password and add users, actually use this library to operate. 2, the data table in the display library: use mysql; // Open the library, learn FoxBase, will not be unfamiliar with Show Tables; 3, display the structure of the data table: Describe table name; 4, Jian Ling: CREATE DATABASE library name ; 5, Jian Table: Use the library name; CREATE TABLE table name (field setting list); 6, delete libraries and deletions: DROP TABASE library name; Drop Table table name; 7, record the table in the table: delete from table Name; 8, the record in the table: SELECT * FROM table name;
Third, an instance of the establishment of the library and the merit mean and the inserted data DROP DATABASE IF EXISTS SCHOOL; / / If there is School, it deletes create Database School; // Set the library School Use School; // Open the library SCHOOL CREATE TABLE TEACHER / / Establishment Table TEACHER (ID INT (3) Auto_Increment Not Null Primary Key, Name Char (10) Not Null, Address Varchar (50) Default '' Shenzhen '', Year Date); // The end of the table is over // below to insert field insert INTO Teacher Values ('' ',' 'Glchengang', '' Shenzhen No.1 "',' '1976-10-10' '); Insert Into Teacher Values (' '', '' Jack ',' 'Shenzhen Yizhong' ',' '1975-12-23' ');
Note: In the form of the table (1) set the ID to the number field of length 3: int (3) and let it automatically add one: auto_increment does not empty: NOT NULL and let him become the primary field Primary Key (2) Set the NAME to a character field (3) set the address to the character field of length 50, and the default is Shenzhen. What is the difference between VARCHAR and CHAR? Only the following article will be said. (4) Set the Year of Year to the date field. If you type the above command on the MySQL prompt, it is not convenient to debug. You can write the above command to a text file to assume School.SQL, then copy to C: / Under, and enter the directory / mysql / bin in the DOS state, then type the following command: mysql -uroot -P password V. Backup Database: (Command Under DOS / MySQL / BIN Directory) mysqldump --opt school> School.bb Note: Back up database School to School.bbb file, School.bbb is a text file, file name Take it, open and see if you will find it. Postscript: In fact, Mysql's operation of the database is similar to other SQL class databases. You'd better find this book of SQL. I only introduce some basic in here. In fact, I only know these, huh, huh. The best mysql tutorial or "Mysql Chinese Reference Manual" is not only downloads every relevant website, but also is the most authoritative. Unfortunately, it is not like "PHP4 Chinese Manual", which is not very convenient to find the function command.
