List of MySQL Common commands
1. System Management
Mysql-H host address-u username-P
Connect to MySQL (in MySQL/bin)
Exit
Exit MySQL Command
Mysqladmin-u username-P old Password New Password
Change Password (in MySQL/bin)
Grantselect [insert] [, update] [, delete] on database. * To username @ localhost ("%", indicating any host) identifiedby "password"
Add users
Mysqldump-u root-P opt Database Name> Backup File Name
Back up the database (in MySQL/bin)
Mysql-u root-P <batchfile (for example, backup file name)
Batch Processing
MySQL. Server start
Start the server
MySQL. server stop
Stop a server
Msql. serverlog
2. query command
Select version ()
Query version number
Select current_date
Query current date
3. Display commands
Show Databases
Show database list
Show tables
Describe Table Name displays the structure of the data table
Select * from Table Name displays records in the table
Select what_to_select from which table [whereconditions_to_satisfy and (or) Where conditions_to_satisfy] retrieve data from a table "meet the conditions 』
Select Field 1, Field 2 ,... From table name: displays the values of a specific column
Select * from table name order by field name sort row
Select Field 1, which includes field 2, calculation of the field value of the new field from Table Name
Select Field 1 is null (is not null) null operation
Select * from table name where field name like (not like) "character"
Note: "_" can be used to match any single character, while "%" matches any number of character pattern matches
Select * from table name where field name Regexp (not Regexp) or rlike (not rlike) "." matches any single character to a single character class […] Match any character in the box. For example, [a], [ASD], [AZ] matches any lowercase letter, and [09] matches any number.
Word. "*" Matches zero or multiple items before it. The regular expression is case sensitive [AA]. If it appears anywhere in the tested value, the pattern matches. Locate, use "^" at the beginning of the mode, and "$" at the end, for example, "^ B"
Extended Regular Expression
Select count (*) from Table Name
Select field name, count (*) from table name group by field name row count
4. Edit commands
Use Database Name
Databases Used
Create Database Name
Create a database
Create Table Name
Create a table in the database
Insert into table name values ("data", "data ")
Add records to a table
Load data infile "/path/FILENAME" External table name
Add data to a table from a file. each row of the file contains a record, and values are separated by a tab.
Drop database Database Name
Delete Database
Drop Table Name
Delete a table from a database
Delete from table name where
Delete records in database tables
Update table name set field = "value" wherewhereconditions_to_satisfy
Update the record value in the database table