MySQL database command line Common commands Summary

This article mainly introduces someMySQL databaseOfCommand LineSome Common commands, including starting and stopping services, user logon, user management, and use of commonly used batch processing commands. Let's take a look at them.

Start and Stop the mysql Service

 
 

  
  
net stop mysql     
  
  
 
  
  
net start mysql 
 
 

Log on to mysql

 
 

  
  
Mysql-u user name-p User Password
  
  
 
  
  
Mysql-uroot-p. After you press enter, you are prompted to enter the password, enter 12345, and then press enter to enter mysql. The mysql prompt is:
  
  
 
  
  
Mysql> Note: if it is connected to another machine, you need to add a parameter-h Machine IP address.
 
 

Add new users

Grant permission on database. * to username @ login host identified by "password"

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

If you do not want user1 to have a password, you can run another command to remove the password. Grant select, insert, update, delete on mydb. * to user1 @ localhost identified "";

Log on to mysql by operating the database, and then run the following command at the mysql prompt. Each Command ends with a semicolon. 

Displays the Database List.

Show databases; by default, there are two databases: mysql and test. Mysql inventory contains the mysql system and user permission information. We change the password and add users, in fact, this database is actually operated.

Display the data tables in the database:

 
 

  
  
use mysql;   
  
  
 
  
  
show tables; 
 
 

Display the data table structure: describe table name;

Database creation and deletion: create database name; drop database name;

Table creation: use Database Name; create table Name (Field List); drop table name;

Clear table records: delete from table name;

Displays the records in the Table: select * from table name;

Export and import data

Export data: mysqldump -- opt test> mysql. test exports the database test to mysql. test file, which is a text file such as mysqldump-u root-p123456 -- databases dbname> mysql. dbname is to export the database dbname to the mysql file. dbname.

Import data: mysqlimport-u root-p123456 <mysql. dbname. No need to explain it.

Import text data to the database: field data of text data is separated by a tab key. Use test; load data local infile "file name" into table name;

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', 'value 3 ')"
  
  
 
  
  
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 condition expression"
  
  
 
  
  
SQL = "update data table set field 1 = value 1, Field 2 = value 2 ...... Field n = value n where condition expression"
 
 

(3) Delete data records:

 
 

  
  
SQL = "delete from data table where condition expression"
  
  
 
  
  
SQL = "delete from data table" (delete all data table Records)
 
 

(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 target data table select * from source data table" (add records of source data table to target data table)
 
 

(5) statistical functions of data records:

AVG (field name) obtains the average COUNT (* | field name) of a table column for statistics on the number of rows or the number of rows with values in a column for statistics 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 value of the data column

The method for referencing the above functions:

 
 

  
  
SQL = "select sum (field name) as Alias from data table where condition expression" set rs = conn. excute (SQL)
 
 

Use rs ("alias") to obtain the calculation value. Use the same method for other functions.

(6) Create and delete data tables:

Create table data TABLE name (Field 1 type 1 (length), Field 2 type 2 (length )...... )

Example: create table tab01 (name varchar (50), datetime default now ())

Drop table data TABLE name (permanently delete a data TABLE)

MySQL Batch Processing Command

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

To implement batch processing, you need to redirect a file to the mysql program. First, we need a text file containing the same text as the command we entered in mysql. The suffix and any other legal names do not have to end with an SQL suffix ):

 
 

  
  
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 these sentences must be correct, and each sentence ends with a semicolon. Select the database for the above USE command, and INSERT the command to INSERT data.

Next, we will import the above files to the database. Before importing the files, we need to confirm that the database is running, that is, the mysqld process or service. Windows NT is called the "service", unix is running. Then run the following command:

 
 

  
  
bin/mysql –p < /home/mark/New_Data.sql 
 
 

Enter the password as prompted. If the statements in the above file are correct, the data will be imported to the database.

Use load data infile in the command line to import DATA from the file to the database: Now you may ask yourself, "Why do I need to input all these SQL statements into the file, and then run them through the program?" This seems to require a lot of work. That's good. You may be right. But what if you have a log record generated from all these commands? This is great now. Well, most databases will automatically generate the log of event records in the database. Most logs contain original SQL commands that have been used. Therefore, if you cannot export data from your current database to a new mysql database, you can use the batch processing features of log and mysql, to quickly and conveniently import your data. Of course, this saves the trouble of typing.

Load data infile: This is the last method to import DATA to the MySQL database. This command is very similar to mysqlimport, but this method can be used in the mysql command line. That is to say, you can use this command in all the programs that use the API. With this method, you can import the data you want to import in the application.

Before using this command, mysqld process service must be running. Start mysql command line: bin/mysql-p

Enter the password as prompted. after entering the mysql command line, enter the following command:

 
 

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

To put it simply, the content in the file data. SQL will be imported into the table Orders. Like the mysqlimport tool, this command also has some optional parameters. For example, if you want to import data from your computer to a remote database server, run the following command:

 
 

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

The LOCAL parameter above indicates that the file is a LOCAL file, and the server is the server you log on. In this way, you can use ftp to upload files to the server, and MySQL completes the process for you. you can also set the insert statement priority. If you want to mark it as low-priority LOW_PRIORITY), MySQL will not insert 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 the file and data table when inserting data. Syntax to replace 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 options describe the file record format. These options are also available in mysqlimport. They look a little different here. First, you need to use the FIELDS keyword. If you use this keyword, the MySQL parser wants to see at least one of the following options:

 
 

  
  
TERMINATED BY character     
  
  
 
  
  
ENCLOSED BY character     
  
  
 
  
  
ESCAPED BY character  
 
 

These keywords have the same usage as their parameters in mysqlimport. the terminated by description field delimiter, which is The tab character \ t BY default) enclosed by describes The field's start character. For example, enclose each field in quotation marks. Escape characters described by escaped. The default value is backslash: \). The following example uses the mysqlimport command to import the same file to the database using the load data infile statement:

 
 

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

The load data infile statement has no features in mysqlimport.

Load data infile can import files to the database according to the specified columns.

This feature is important when we want to import part of the data. For example, when upgrading from an Access database to a MySQL database, you need to add columns, fields, and fields to the MySQL database to meet some additional requirements. At this time, the data in our Access database is still available, but because the columns (fields) of the data are no longer matched with those in MySQL, The mysqlimport tool cannot be used. Even so, we can still use load data infile. The following example shows how to import DATA to a specified field:

 
 

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

As you can see, we can specify the required topic fields ). These specified fields are still enclosed in parentheses and separated by commas. If you omit any of them, MySQL will remind you.

Mysql Command Line in ubuntu. Download blue. SQL and run:

 
 

  
  
(sudo) mysql    
  
  
 
  
  
create database XXXX;    
  
  
 
  
  
use XXXX;    
  
  
 
  
  
source blue.sql 
 
 

Initial login to the remote MYSQL database mysql-hIP-u username-p password.

The command line of the MySQL database is introduced here. If you want to learn more about the MySQL database, you can refer to the article http://database.51cto.com/mysql/, which will surely bring you the harvest!