Management Control: list of common MYSQL database commands

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 <batch-file (for example, backup file name)

Batch Processing

Mysql. server start

Start the server

Mysql. server stop

Stop a server

Msql. server ­ log

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 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