MySQL common command
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 mysql / bin, then 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 it can be used directly Entered into MySQL, MySQL's 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 ""; In the upper part, we told questions such as logins, increase users, password changes. 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.
First, operation skills
1. If you play a command, I found that I forgot to add the semicolon after I entered the bus. You didn't need to re-play a command, as long as you have a semicolon to enter the car. 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 are 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, display the data table in the library:
Use mysql; // Open the library, I will not be unfamiliar with FoxBase.
Show table;
3, display the structure of the data sheet:
DESCRIBE table;
4, build the library:
CREATE DATABASE library name;
5, build the table:
USE library name;
CREATE TABLE Name (Field Settings List);
6, delete libraries and deleted forms:
DROP DATABASE library name;
DROP TABLE table name;
7. Record the table in the table:
DELETE FROM table name;
8, the record in the display:
SELECT * FROM table name;
Third, an example of a construction and construction form and insert data
drop database if exists school; // delete if SCHOOL exist create database school; // build libraries SCHOOL use school; // open the library SCHOOL create table teacher // build 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 the table
/ / The following is inserted insert INTO TEACHER VALUES ('', 'Glchengang', 'Shenzhen No.1 ",' 1976-10-10 '); Insert Into Teacher Values (' ',' Jack ',' Shenzhen No.1" , '1975-12-23');
Note: In the Table of the Table (1) Set the ID to the length of 3 digital field INT (3) and let each record automatically add one, auto_increment does not make NOT NULL and let him become the primary field Primary Key (2 ) Set Name to the character field (3) of the length of 10 to set the address to the character field of length 50, and the default value 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 Directory / MySQL / BIN /, then type the following command: mysql -uroot -P password < C: /school.sql
If successful, there is no display in a row; if there is a mistake, there will be prompts. (The above command has been debugged, you only need to use the // of the annotation to go out).
Fourth, transfer the text data into the database
1. The text data should match the format: field data is used with Tab keys, and null values are replaced with / n.
example:
3 Rose Shenzhen 2nd 1976-10-10
4 MIKE Shenzhen No.1975-12-23
2, data incoming command
Load Data Local Infile "File Name" INTO TABLE table;
Note: You'd better copy files into the / mysql / bin directory and you must first use the USE command to play the library.
V. Back up the database: (command executed in DOS / mysql / bin directory)
MySQLDUMP - OPT SCHOOL> School.bbb
Note: Back up the database SCHOOL to the school.bb file, and School.bbb is a text file, the file name is all, open to see if you will have new discovery. -------------------------------------------------- ------------------ Database Backup Action:
MySQL has many ways to import data, however these is just half in data transfer, and additional data is generally exported from the MySQL database. There are many reasons why we need to export data. An important reason is to back up the database. The cost of data is often expensive and needs to be careful. Frequently backups can help prevent valuable data loss; another reason is that you may want to export data to share. In the world that continuously grows this information technology, shared data is getting more common.
For example, Macmillan USA maintains a large database of books that will be published. This database is shared between many bookstores so they know which books will be published soon. The hospital is increasingly used to use a paper-free medical record, so that these medical records can follow you. The world has become less and smaller, and the information is also shared and more. There are many ways to export data, they are very similar to imported data. Because, after all, these are just a perspective. The data exported from the database is the data imported from the other end. Here we don't discuss other methods of other databases, you will learn how to use MySQL to implement data export. Use mysqldump: (MySqlDump command in mysql / bin / directory) mysqldump tool mysqlimport similar to the opposite effect. They have some same options. But mysqldump can do more things. It can load the entire database into a separate text file. This file contains the SQL commands you need to rebuild your database. This command acquires all modes (Schema, explained later) and converts it to DDL syntax (CREATE statement, ie, database definition statements), acquire all data, and create an Insert statement from these data. This tool reverses all your designs in your database. Because all things are included in a text file. This text file can be returned to MySQL with a simple batch and a suitable SQL statement. This tool is incredibly simple and fast. There will never have some points to make people distress. Therefore, if you are on the content loaded into the entire database meet_a_geek, you can use the following command: bin / mysqldump -p meet_a_geek> Meetageek_dump_file.txt This statement also allows you to specify a table for DUMP (Backup / Export / Load?) . If you just want to import the entire contents of the table order in the database meet_a_geek, you can use the following command: bin / mysqldump -p meet_a_geek orders> Meetageek_Orders.txt This is very flexible, you can even use the WHERE clause to choose The record you need is exported to the file. To achieve this, you can use the following command: bin / mysqldump -p -where = "Order_id> 2000" meet_a_geek orders> special_dump.txt mysqldump tool has a large number of options, some options are as follows: Options / Option Action Performed --Add-Drop-Table This option will add a Drop Table if Exists statement in front of each table, which guarantees that when the Mysql database will not be wrong, because each time is returned, will be first Check if the table exists, the presence is deleted - ADD-LOCKS this option bundles a Lock Table and Unlock Table statement in the INSERT statement. This prevents the Other user's operations when these records are again imported into the database-C or-Complete_Insert this option such that the mysqldump command gives each generated name (Field) name. This option is useful when you export the data.
-DELAYED-INSERT Add to DELAY Options in the insert command -f or -flush-logs Use this option, which will refresh the mysql server log. -f or -force use this option, even if there is an error, still Continue to export -full This option also adds additional information to the CREATE TABLE statement. -T or -no-create- info This option makes the mysqldump command not to create a CREATE TABLE statement. This option is convenient when you only need data without a DDL (database definition statement). -D or-dia-data This option makes the mysqldump command does not create an Insert statement. This option can be used when you only need DDL statements. --Opt This option will open all options that will increase file export speed and create a file that can be imported faster. -Q or -quick This option makes MySQL do not read the entire exported content into memory and execute, but is written in the file when you are read. -T path or -tab = path This option will create two files, one file contains DDL statements or table creation statements, and another file contains data. The DDL file is named Table_Name.sql, and the data file is named Table_name.txt. The path name is the directory where these two files are stored. The directory must already exist, and the user has the privilege of the file. -W "where clause" or -where = "where clause" As mentioned earlier, you can use this option to filter the data that will be placed in the export file. Assuming that you need to create a file for the account you want to use in a form, the manager wants to see all orders (ORDERS) this year (Orders), they do not interested DDL, and require files with comma separation, because this is easy Import into Excel. In order to complete this person, you can use the following sentence: bin / mysqldump -p -where "Order_date> = '2000-01-01'" -tab = / home / mark -no-create-info -fields-terminated-by =, Meet_a_geek ORDERS This will get the result you want. Schema: Mode The Set of Statements, Expressed in Data Definition Language, That Completely Describe The Structure of A Data Base. A group of statement sets expressed in a data definition language, which completely describes the structure of the database. Select Into Outfile: If you feel that the mysqldump tool is not cool enough, use SELECT INTO OUTFILE, MySQL also provides a command with the opposite effect with the load data infile command, which is the select into outfile command, which has a lot of similarities. Place. First, they have all the options almost the same.
