Shulou(Shulou.com)05/31 Report--

This article will explain in detail what commands are commonly used on the mysql command line. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

MySql address: www.mysql.org

The first step, the start and stop of mysql service

Net stop mysql

Net start mysql

The second step is to log in to mysql

Mysql-u username-p user password

Mysql-uroot-p, enter and prompt you to enter the password, enter 12345, and then enter the mysql. The prompt for mysql is:

Mysql > Note: if you are connecting to another machine, you need to add a parameter-h machine IP

Third, add new users

Grant permission on. * to user name @ login host identified by "password"

Add a user user1 password to password1, so that it can log in on the local computer, and have the authority to query, insert, modify and delete all databases. First connect with the root user, and then type the following command: grant select,insert,update,delete on *. * to user1@localhost Identified by "password1"; if you want the user to be able to log in to mysql on any machine, change localhost to "%".

If you don't want user1 to have a password, you can issue another command to remove the password. Grant select,insert,update,delete on mydb.* to user1@localhost identified by ""

Fourth trick: operate the database to log in to mysql, and then run the following commands at the mysql prompt, with each command ending with a semicolon.

Displays a list of databases.

Show databases; has two databases by default: mysql and test. Mysql stores the system and user rights information of mysql. When we change passwords and add new users, we actually operate on this library.

Display the data tables in the library:

Use mysql; show tables

Displays the structure of the data table:

Describe table name

Build and delete libraries:

Create database library name; drop database library name

Build a table:

Use library name; create table table name (field list); drop table table name

Clear the record in the table:

Delete from table name

Display the records in the table:

Select * from table name

Fifth move, export and import data

Export data:

Mysqldump-- opt test > mysql.test exports the database test database to the mysql.test file, followed by a text file such as: mysqldump-u root-p123456-- databases dbname > mysql.dbname that exports the database dbname to the file mysql.dbname.

Import data:

Mysqlimport-u root-p123456 < mysql.dbname. You don't have to explain.

Import text data into the database:

The field data of text data is separated by tab. Use test; load data local infile File name into table table name

How to use common SQL commands:

(1) data record filtering:

Sql= "select * from data Table where Field name = Field value order by Field name [desc]"

Sql= "select * from data Table where Field name like'% Field value% 'order by Field name [desc]"

Sql= "select top 10 * from data Table where Field name order by Field name [desc]"

Sql= "select * from data Table where field name in ('value 1') 'value 2')"

Sql= "select * from data Table where Field name between value 1 and value 2"

(2) update data records:

Sql= "update data Table set Field name = Field value where conditional expression"

Sql= "update data Table set field 1 = value 1, field 2 = value 2... field n = value n where conditional expression"

(3) Delete data records:

Sql= "delete from data Table where conditional expression"

Sql= "delete from data Table" (delete all records of the data table)

(4) add data records:

Sql= "insert into data Table (Field 1, Field 2, Field 3...) valuess (value 1, value 2, value 3...)"

Sql= "insert into destination data Table select * from Source data Table" (add records from the source data table to the target data table)

(5) Statistical function of data record:

AVG (field name) get a table column average COUNT (* | Field name) Statistics on the number of rows of data or valuable rows of data in a column MAX (field name) get the maximum value of a table column MIN (field name) get the minimum value of a table column SUM (field name) add the values of the data column

The method of referencing the above function:

Sql= "select sum (field name) as alias from data table where conditional expression" set rs=conn.excute (sql)

Use rs ("alias") to get the value of the system, and other functions are the same as above.

(VI) creation and deletion of data tables:

CREATE TABLE datasheet name (field 1 type 1 (length), field 2 type 2 (length)...)

Example: CREATE TABLE tab01 (name varchar (50), datetime default now ())

DROP TABLE Datasheet name (permanently delete a Datasheet)

MySQL batch command

Batch processing is a non-interactive way to run mysql programs, and you will still use these commands just like the commands you use in mysql.

To implement batch processing, if you redirect a file into the mysql program, we first need a text file that contains the same text as the command we entered in mysql. For example, if we want to insert some data, use a file that contains the following text (the file name is New_Data.sql, of course, we can also name it New_Data.txt and any other legal name, not necessarily ending with the suffix sql):

USE Meet_A_Geek

INSERT INTO Customers (Customer_ID, Last_Name) VALUES (NULL, "Block")

INSERT INTO Customers (Customer_ID, Last_Name) VALUES (NULL, "Newton")

INSERT INTO Customers (Customer_ID, Last_Name) VALUES (NULL, "Simmons")

Note that the syntax of the above sentences must be correct and each sentence ends with a semicolon. The above USE command selects the database, and the INSERT command inserts the data.

Next, we want to import the above file into the database, and before importing, make sure that the database is already running, that is, the mysqld process (or service, called "service" below Windows NT, and "process" under unix) is already running. Then run the following command:

Bin/mysql-p < / home/mark/New_Data.sql

Then enter the password as prompted, and if there are no errors in the statements in the above file, then the data is imported into the database.

Use LOAD DATA INFILE on the command line to import data from a file into a database: now you might ask yourself, "Why on earth should I enter all these SQL statements into a file and run them through the program?" It looks like it takes a lot of work. Well, you're probably right to think so. But what if you have log records generated from all these commands? Now that's great, well, most databases automatically generate log for event records in the database. Most log contain raw SQL commands that have been used. Therefore, if you cannot export data from your current database to a new my, you can use the batch features of log and mysql to import your data quickly and easily. Of course, this saves the trouble of typing.

LOAD DATA INFILE this is the last method we will introduce to import data into the MySQL database. This command is very similar to mysqlimport, but this method can be used on the mysql command line. That means you can use this command in all programs that use API. Using this method, you can import the data you want to import in your application.

Before using this command, the mysqld process (service) must already be running. Start the mysql command line:

Bin/mysql-p

Enter the password when prompted, and after successfully entering the mysql command line, enter the following command:

USE Meet_A_Geek

LOAD DATA INFILE "/ home/mark/data.sql" INTO TABLE Orders

In a nutshell, this will import the contents of the file data.sql into the table Orders, which, like the mysqlimport tool, has some optional parameters. For example, if you need to import data from your computer to a remote database server, you can use the following command:

LOAD DATA LOCAL INFILE "C:\ MyDocs\ SQL.txt" INTO TABLE Orders

The above LOCAL parameter indicates that the file is a local file and the server is the server you logged in to. This eliminates the need to use ftp to upload files to the server, and MySQL does it for you. You can also set the priority of the insert statement, and if you want to mark it as low priority (LOW_PRIORITY), MySQL will not insert the data until no one else reads the table. You can use the following command:

LOAD DATA LOW_PRIORITY INFILE "/ home/mark/data.sql" INTO TABLE Orders

You can also specify whether to replace or ignore duplicate key values in files and data tables when inserting data. Syntax that replaces duplicate key values:

LOAD DATA LOW_PRIORITY INFILE "/ home/mark/data.sql" REPLACE INTO TABLE Orders

The above sentence looks a little clumsy, but it puts the keywords in a place that your parser can understand.

The following pair of options describe the record format of the file, which are also available in the mysqlimport tool. They look a little different here. First, the FIELDS keyword is used, and if this keyword is used, the MySQL profiler wants to see at least one of the following options:

TERMINATED BY character

ENCLOSED BY character

ESCAPED BY character

These keywords and their parameters are used the same as in mysqlimport. The delimiter of the The TERMINATED BY description field, which by default is the tab character (\ t) ENCLOSED BY describes the bracketed characters of the field. For example, enclose each field in quotation marks. The escape character of the ESCAPED BY description. The default is the reverse bar (backslash:\). Still using the previous example of the mysqlimport command, import the same file into the database with the LOAD DATA INFILE statement:

LOAD DATA INFILE "/ home/mark/Orders.txt" REPLACE INTO TABLE Orders FIELDS TERMINATED BY', 'ENCLOSED BY' "'

There is a mysqlimport tool in the LOAD DATA INFILE statement that has no features:

LOAD DATA INFILE can import files into the database in the specified columns.

This feature is important when we want to import part of the data. For example, when we upgrade from an Access database to a MySQL database, we need to add some columns (columns / fields / field) to the MySQL database to meet some additional needs. At this point, the data in our Access database is still available, but because the field of the data no longer matches that in MySQL, the mysqlimport tool can no longer be used. However, we can still use LOAD DATA INFILE, and the following example shows how to import data into a specified column (field):

LOAD DATA INFILE "/ home/Order.txt" INTO TABLE Orders (Order_Number, Order_Date, Customer_ID)

As you can see, we can specify the desired column (fields). These specified fields are still enclosed in parentheses and separated by commas, and if you miss any of them, MySQL will remind you of ^ _ ^

Mysql command line mode under ubuntu. Download the blue.sql and run it

(sudo) mysql

Create database XXXX

Use XXXX

Source blue.sql

Example: initial login to remote MYSQL database mysql-hIP-u username-p password

This is the end of this article on "what commands are commonly used on the mysql command line". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.