Please note – this is absolutely not a complete guide about all MySQL commands usage – but just examples of the most commonly used ones.
mysql>
at the begin of the line means it’s executed from the MySQL console.
Symbols #
or $
means it’s executed from the Linux shell.
UPD 03.03.2019 This post originally was posted at the 2012 year and became the most widely read in this blog – 198737 views at this moment. Today it was translated to English, updated with the new formatting and Table of contents sorting to make it easier to find necessary information.
Contents
General commands
To check MySQL status run:
To connect to a MySQL server running on the same host:
To connect to a MySQL server running on the remote host db1.example.com:
Working with databases and tables
Working with databases
To create a database:
List all databases on the current MySQL server:
Connect to a database to start working with it:
To delete database:
Working with tables
List all tables in a current database:
Display table’s columns types and descriptions:
Display all table’s content:
Display overall table’s lines:
Count columns in a table:
Drop column from a table:
Delete the whole table:
Working with columns
Add a new column to the table:
Change the column’s name:
Create a column with an unique name to avoid duplicates:
Change the column’s size:
Selecting data
Display all table’s content:
Display all columns and their content from a table:
Display all records from a table with the “whatever“:
Find all records with the “Bob” in the name
column and “3444444 in the phone_number
column:
Find all records withOUT the “Bob” in the name
column and “3444444 in thephone_number
column sorting them by the phone_number
column:
Display all records starting from the ‘bob‘ and ‘3444444′ phone in a specific table:
Display all records starting from the ‘bob‘ and ‘3444444′ phone in a specific table limiting results from 1 to 5:
Show all unique records:
Display selected records sorting the by decreasing order:
Using regular expressions
You can use the regex (“REGEXP BINARY”) in your filters.
For example to find all records starting with the “A” using case-independent regex search:
Importing and exporting data
To load data from a CSV file into a table:
MySQL users, passwords
Adding a new user: connect to a server as root, connect to a database, adding a user, updating privileges:
To change a user’s password on a remote host db1.example.org using the mysqladmin
tool:
To change user’s password from the MySQL CLI:
MySQL root reset
To reset MySQL root’s password: stop the server, start it without privileges table, log in as root, set new password, exit and start MySQL in the normal way:
To update root‘s password: using mysqladmin
:
Grant permissions for remote login from the localhost host with the passwd password:
Grant specific permissions for user:
Or just grant everything:
Setting specific permissions for a user per specific table:
Do not forget update privileges:
Backup, restore
Creating backups
Create a backup (dump) from all databases into the alldatabases.sql file:
Create a backup (dump) from a database with the databasename name into the databasename.sql file:
Create one table’s backup into the databasename.tablename.sql file:
Restore from a backup
To restore database(s) or table(s) from a backup:
Tables creation examples
italic: columns name;
In CAPITAL letters: types and attributes for columns;
in (brackets) – column’s values.
Example 1:
Example 2:
More examples – in the Простая работа с MySQL в простых примерах #1 — работа с базами, создание таблиц and Простая работа с MySQL в простых примерах #2 — наполнение таблиц.