The following articles mainly describe MySQL common command line operations, that is, MySQL command line Common commands, MySQL command line Common commands in the actual application proportion is still the majority, if you are curious about this technology, the following articles will unveil its mysteries.
First, start and stop the MySQL Service
- net stop MySQL
- net start MySQL
Second, log on to MySQL
Syntax: MySQL-u user name-p User Password
Enter the MySQL-uroot-p command, press enter and prompt you to enter the password, enter 12345, and then press enter to enter MySQL. The MySQL prompt is:
MySQL>
Note: If you are connecting to another machine, you need to add a parameter-h Machine IP address.
Third, add new users
Format: grant permission on database. * to username @ login host identified by "password"
For example, you can 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 type the following command:
- grant select,insert,update,delete on *.* to user1@localhost Identified by "password1";
If you want the 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: MySQL command-line Common commands and Database Operations
Log on to MySQL and run the following commands at the MySQL prompt. Each Command ends with a semicolon.
1. display the Database List.
Show databases;
By default, two databases are available: 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.
2. display the data tables in the database:
Use MySQL;
Show tables;
3. display the data table structure:
Describe table name;
4. Create and delete databases:
Create database name;
Drop database name;
5. Create a table:
Use Database Name;
Create table Name (Field List );
Drop table name;
6. Clear the table records:
Delete from table name;
7. display the records in the table:
Select * from table name;
Step 5: use MySQL command lines to export and import data
1. Export data:
- MySQLdump --opt test > MySQL.test
Export the database test database to the MySQL. test file, which is a text file
For example:
- MySQLdump -u root -p123456 --databases dbname > MySQL.dbname
Export the database dbname to the MySQL. dbname file.
2. import data:
- MySQLimport -u root -p123456 < MySQL.dbname。
No need to explain it.
3. Import text data to the database:
Field data of text data is separated by the 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) returns the average value of a table column
COUNT (* | field name) statistics on the number of data rows or the number of data rows with values in a column
MAX (field name) obtains the maximum value of a table column.
MIN (field name) obtains the minimum value of a table column.
SUM (field name) adds the values in 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) MySQL command line Common commands, data table creation and deletion:
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)