MySQL Usage guide DOS operation

First, connect MySQL.
Format: mysql-h host address-u user name-P user Password
1. Example 1: Connect to MySQL on this machine.
First open the DOS window, and then enter 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 directly enter into MySQL, The prompt for MySQL is:mysql>;
2. Example 2: Connect to MySQL on the remote host. Assume the remote host IP is: 110.110.110.110, the user name is root, the password is abcd123. Type the following command:
Mysql-h110.110.110.110-uroot-pabcd123
(Note: You and root can be used without spaces, others are the same)
3. Exit MySQL command: Exit (enter)

Second, change the password.
Format: Mysqladmin-u username-P Old password password new password
1, Example 1: Add a password to root ab12. First enter 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 can be omitted.
2, Example 2: Then change the root password to djg345.
MYSQLADMIN-UROOT-PAB12 Password djg345
Third, add new users. (Note: Unlike the above, the following is because it is a command in a MySQL environment, so it is followed by a semicolon as a 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, use the root user to connect to MySQL, and then type the following command:
Grant Select,insert,update,delete on * * to [e-mail protected] "%" identified by "ABC";
But example 1 increases the user is very dangerous, you want to like someone to know test1 password, then he can be on any computer on the Internet to log on your MySQL database and to your data can do whatever, workaround see Example 2.
Example 2, add a user test2 password for ABC, so that he can only login on localhost, and the database mydb can query, insert, modify, delete operations (localhost refers to the local host, that is, the MySQL database host), This allows the user to use a password that knows test2, and he cannot access the database directly from the Internet, but only through a Web page on the MySQL host.
Grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by "ABC";
If you do not want to test2 have a password, you can call another command to erase the password.
Grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by "";


(Next article)
In the previous article we talked about login, add user, password change and other issues. In the next section, we'll look at MySQL's operations on the database. Note: You must first log in to MySQL, the following actions are performed at the prompt of MySQL, and each command ends with a semicolon.

First, the Operation skill
1, if you hit the command, enter after the discovery forgot to add a semicolon, you do not have to re-play the command, as long as a semicolon to enter the return on it. In other words, you can break a complete command into a few lines, and then use a semicolon to make the end sign OK.
2. You can use the cursor up and down keys to recall the previous command. But previously I used an old version of MySQL that was not supported. I'm using Mysql-3.23.27-beta-win now.

Second, show the command
1. Display the list of databases.
show databases;
Just started with two databases: MySQL and test. MySQL Library is very important it has the MySQL system information, we change the password and the new user, is actually using this library to operate.
2. Display the data table in the library:
use MySQL;//Open the library, learn foxbase must not be unfamiliar with it
Show tables;
3, display the structure of the data table:
describe table name;
4, build the library:
Create database name;
5, build the table:
Use library name;
CREATE TABLE table name (field settings list);
6. Deleting the library and deleting the table:
drop database name;
drop table name;
7. Empty the records in the table:
Delete from table name;
8. Display the records in the table:
SELECT * from table name;

A city build and build tables and instances of inserting data
Drop database if exists school; Delete if school is present
Create Database School; Building 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 (TEN) is not NULL,
Address varchar (+) default Shenzhen,
Year Date
); End of Build table
The following is the Insert field
Insert INTO teacher valuess (, Glchengang, Shenzhen One, 1976-10-10);
Insert INTO teacher valuess (, Jack, Shenzhen, 1975-12-23);

Note: In the Build table (1), set the ID to a number field of length 3: Int (3) and let it automatically add one to each record: Auto_increment is not null, and makes him the main field primary Key (2) sets the name to a character field of length 10 (3) To set 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 a later article to say. (4) Set year as the Date field.
It is also possible to type the above commands at the MySQL prompt, but it is not easy to debug. You can write the above command as-is to a text file, assume School.sql, then copy it to C:, and enter directory Mysqlbin in DOS, and then type the following command:
Mysql-uroot-p Password < C:school.sql
If successful, empty a row without any display, and if there is an error, there is a hint. (The above command has been debugged, you can use it only if you remove//comment).

Iv. transferring text data to the database
1, the text data should conform to the format: The field data is separated by the TAB key, the null value is substituted by N.
Cases:
3 Rose Shenzhen II 1976-10-10
4 Mike Shenzhen one 1975-12-23
2, the data incoming command, load data local infile "file name" into table table name;
Note: You might want to copy the file to the Mysqlbin directory, and use the using command to hit the library that contains the table.

V. BACKUP DATABASE: (command executed in DOS Mysqlbin directory)
Mysqldump--opt SCHOOL&GT;;SCHOOL.BBB
Note: Back up the database school to the school.bbb file, school.bbb is a text file, the filename is taken, open to see what you will find.

PostScript: In fact, MySQL database operation and other SQL class database is much the same, you'd better find a book to read SQL. I am here only to introduce some basic, in fact, I only understand these, hehe. The best MySQL tutorial or "Yanzi" translated "MySQL Chinese reference manual" not only free every relevant website has downloaded, and it is the most authoritative. Unfortunately not like the "PHP4 Chinese Manual" is the CHM format, in the search function command is not very convenient

How to import and export MySQL database----*.sql file operations

September 12, 2007 Wednesday 09:01

1. Overview MySQL database import, there are two ways: 1) pilot out the database SQL script, and then import; 2) Copy the database directory and files directly. In the case of different operating systems or versions of MySQL, the method of copying files directly may occur in an incompatible situation. Therefore, it is generally recommended to import in SQL script form. Two methods are described below. Under Linux: 2. Method one SQL script form The procedure is as follows: 2.1. Exporting SQL Scripts On the original database server, you can export the SQL script using the phpMyAdmin tool, or the mysqldump (mysqldump command is located in the mysql/bin/directory) command line. 2.1.1 with phpMyAdmin Tools Export options, select export structure and data, and do not add the drop DATABASE and drop TABLE options. Select the Save As File option, and if you have more data, you can select the gzipped option. Save the exported SQL file. 2.1.2 with mysqldump command line Command format Mysqldump-u user name-p database name > database name. sql Example: MYSQLDUMP-UROOT-P ABC > Abc.sql (Export database ABC to abc.sql file) When prompted for a password, enter the password for the database user name. 2.2. Create an empty database Create a database from the main control interface/Control Panel. Assuming that the database name is ABC, the database is full-user abc_f. 2.3. Import SQL scripts into the execution The same is true of two methods, a phpmyadmin (MySQL database management) tool, or a MySQL command line. 2.3.1 with phpMyAdmin Tools From the Control Panel, select Create an empty database, click "Manage" and go to the Administration Tools page. From the SQL menu, browse to select the SQL file you just exported, click "Execute" to download and execute. Note: phpMyAdmin has a limit on the size of uploaded files, and PHP itself limits the size of uploaded files if the original SQL file Relatively large, you can first compress it with gzip, for a text file such as SQL file, you can get 1:5 or higher compression rate. How to use gzip: # gzip Xxxxx.sql Get xxxxx.sql.gz file. 2.3.2 with mysql command line Command format Mysql-u user name-p database name < database name. sql Example: MYSQL-UABC_F-P ABC < Abc.sql (Import database ABC from Abc.sql file) When prompted for a password, enter the password for the database user name. 3 Method Two direct copies If the database is larger, you can consider the method of direct copy, but different versions and operating systems may be incompatible between the use of caution. 3.1 Preparing the original file Package as a file with tar 3.2 Creating an empty database 3.3 Decompression Unzip in the temp directory, such as: Cd/tmp Tar zxf mydb.tar.gz 3.4 Copy Copy the extracted database files to the relevant directory CD mydb/ CP */var/lib/mysql/mydb/ For FreeBSD: CP */var/db/mysql/mydb/ 3.5 Permission settings Change the owner of the copy of the previous file to Mysql:mysql and change the permission to 660 Chown mysql:mysql/var/lib/mysql/mydb/* chmod 660/var/lib/mysql/mydb/* Revise the data that mysqldump out. In addition to adding the table) Type=myisam CHARACTER set GBK, add a set NAMES GBK below; In this way, the data can be smoothly imported 4.1, without fear of garbled. Under Windows: DOS window Start-> Run-> cmd Export to: Mysqldump-u user name-p--opt database name > database name. sql Enter your database password! Import as: Mysql-u user name-p database name < database name. SQL (to operate in the current directory)

MySQL Usage guide DOS operation