MySQL command line [MySQL command line Common commands]

MySQL: www.mysql.org, MySQL service start and stop net stop MySQL Net start MySQL, MySQL login MySQL mysql-u user name-P User Password mysql-uroot-P, 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. Step 3: add the new user grant permission to the on database. * To username @ login host identified by "password" add a user user1 password password1 so that it can log on to the local machine, all databases are permitted to query, insert, modify, and delete 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 by ""; Step 4: 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 database name; drop database database name; Table creation: Use Database Name; create Table Name (Field List); drop table name; clear table records: delete from table name; display table records: Select * from table name; fifth, export and import data export: 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 se T 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 record: SQL =" delete from data table where condition expression "SQL =" delete from data table "(delete all records of 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 target data table select * from source data table "(add records of source data table to target data table) (5) data record statistical function: AVG (field name) obtain the average count (* | field name) of a table column for statistics on the number of data rows or the number of data rows with values in a column for statistics max (field name) obtain the maximum value min (field name) in a table column to obtain the smallest value sum (field name) in a table column to add the values in the data column and reference the above functions: SQL = "select sum (field name) as Alias from data table where condition expression" set rs = Conn. excute (SQL) uses RS ("alias") to obtain the value of the system. Other functions are used in the same way. (6) Create and delete a data table: 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 method for running 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. Values and any other valid names do not have to end with an SQL post.): Use meet_a_geek; insert into MERs (customer_id, last_name) values (null, "Block "); insert into MERs (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 need to 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, which is called "service" under Windows NT). UNIX is running. Then run the following command: Bin/MySQL-P