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:
[simterm]
$ systemctl status mysql
[/simterm]
To connect to a MySQL server running on the same host:
[simterm]
$ mysql -u username -p
[/simterm]
To connect to a MySQL server running on the remote host db1.example.com:
[simterm]
$ mysql -u username -p -h db1.example.com
[/simterm]
Working with databases and tables
Working with databases
To create a database:
[simterm]
mysql> CREATE DATABASE [databasename];
[/simterm]
List all databases on the current MySQL server:
[simterm]
mysql> SHOW DATABASES;
[/simterm]
Connect to a database to start working with it:
[simterm]
mysql> USE [db name];
[/simterm]
To delete database:
[simterm]
mysql> DROP DATABASE [database name];
[/simterm]
Working with tables
List all tables in a current database:
[simterm]
mysql> SHOW TABLES;
[/simterm]
Display table’s columns types and descriptions:
[simterm]
mysql> DESCRIBE [table “” not found /]
;
[/simterm]
Display all table’s content:
[simterm]
mysql> SELECT * FROM [table “” not found /]
;
[/simterm]
Display overall table’s lines:
[simterm]
mysql> SELECT COUNT(*) FROM [table “” not found /]
;
[/simterm]
Count columns in a table:
[simterm]
mysql> SELECT SUM(*) FROM [table “” not found /]
;
[/simterm]
Drop column from a table:
[simterm]
mysql> alter table [table “” not found /]
DROP INDEX [column name];
[/simterm]
Delete the whole table:
[simterm]
mysql> DROP TABLE [table “” not found /]
;
[/simterm]
Working with columns
Add a new column to the table:
[simterm]
mysql> ALTER TABLE [table “” not found /]
ADD COLUMN [new column name] varchar (20);
[/simterm]
Change the column’s name:
[simterm]
mysql> ALTER TABLE [table “” not found /]
CHANGE [old column name] [new column name] varchar (50);
[/simterm]
Create a column with an unique name to avoid duplicates:
[simterm]
mysql> ALTER TABLE [table “” not found /]
ADD UNIQUE ([column name]);
[/simterm]
Change the column’s size:
[simterm]
mysql> ALTER TABLE [table “” not found /]
MODIFY [column name] VARCHAR(3);
[/simterm]
Selecting data
Display all table’s content:
[simterm]
mysql> SELECT * FROM [table “” not found /]
;
[/simterm]
Display all columns and their content from a table:
[simterm]
mysql> SHOW COLUMNS FROM [table “” not found /]
;
[/simterm]
Display all records from a table with the “whatever“:
[simterm]
mysql> SELECT * FROM [table “” not found /]
WHERE [field name] = "whatever";
[/simterm]
Find all records with the “Bob” in the name column and “3444444 in the phone_number column:
[simterm]
mysql> SELECT * FROM [table “” not found /]
WHERE name = "Bob" AND phone_number = '3444444';
[/simterm]
Find all records withOUT the “Bob” in the name column and “3444444 in thephone_numbercolumn sorting them by the phone_number column:
[simterm]
mysql> SELECT * FROM [table “” not found /]
WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;
[/simterm]
Display all records starting from the ‘bob‘ and ‘3444444′ phone in a specific table:
[simterm]
mysql> SELECT * FROM [table “” not found /]
WHERE name like "Bob%" AND phone_number = '3444444';
[/simterm]
Display all records starting from the ‘bob‘ and ‘3444444′ phone in a specific table limiting results from 1 to 5:
[simterm]
mysql> SELECT * FROM [table “” not found /]
WHERE name like "Bob%" AND phone_number = '3444444' limit 1,5;
[/simterm]
Show all unique records:
[simterm]
mysql> SELECT DISTINCT [column name] FROM [table “” not found /]
;
[/simterm]
Display selected records sorting the by decreasing order:
[simterm]
mysql> SELECT [col1],[col2] FROM [table “” not found /]
ORDER BY [col2] DESC;
[/simterm]
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:
[simterm]
mysql> SELECT * FROM [table “” not found /]
WHERE rec RLIKE "^a";
[/simterm]
Importing and exporting data
To load data from a CSV file into a table:
[simterm]
mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table “” not found /]
FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n' (field1,field2,field3);
[/simterm]
MySQL users, passwords
Adding a new user: connect to a server as root, connect to a database, adding a user, updating privileges:
[simterm]
# mysql -u root -p
mysql> USE mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES('%','username', PASSWORD('password'));
mysql> flush privileges;
[/simterm]
To change a user’s password on a remote host db1.example.org using the mysqladmin tool:
[simterm]
# mysqladmin -u username -h db1.example.org -p password 'new-password'
[/simterm]
To change user’s password from the MySQL CLI:
[simterm]
mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
mysql> flush privileges;
[/simterm]
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:
[simterm]
# systemctl stop mysql
# mysqld_safe --skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("newrootpassword") where User='root';
mysql> flush privileges;
mysql> quit
# systemctl start mysql
[/simterm]
To update root‘s password: using mysqladmin:
[simterm]
# mysqladmin -u root -p oldpassword newpassword
[/simterm]
Grant permissions for remote login from the localhost host with the passwd password:
[simterm]
# mysql -u root -p mysql> use mysql; mysql> grant usage on *.* to bob@localhost identified by 'passwd'; mysql> flush privileges;
[/simterm]
Grant specific permissions for user:
[simterm]
mysql> use mysql;
mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');
mysql> flush privileges;
[/simterm]
Or just grant everything:
[simterm]
mysql> grant all privileges on databasename.* to username@localhost; mysql> flush privileges;
[/simterm]
Setting specific permissions for a user per specific table:
[simterm]
mysql> UPDATE [table “” not found /]
SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';
[/simterm]
Do not forget update privileges:
[simterm]
mysql> flush privileges;
[/simterm]
Backup, restore
Creating backups
Create a backup (dump) from all databases into the alldatabases.sql file:
[simterm]
# mysqldump -u root -p password --opt >/tmp/alldatabases.sql
[/simterm]
Create a backup (dump) from a database with the databasename name into the databasename.sql file:
[simterm]
# mysqldump -u username -p password --databases databasename >/tmp/databasename.sql
[/simterm]
Create one table’s backup into the databasename.tablename.sql file:
[simterm]
# mysqldump -c -u username -p password databasename tablename > /tmp/databasename.tablename.sql
[/simterm]
Restore from a backup
To restore database(s) or table(s) from a backup:
[simterm]
# mysql -u username -p password databasename < /tmp/databasename.sql
[/simterm]
Tables creation examples
italic: columns name;
In CAPITAL letters: types and attributes for columns;
in (brackets) – column’s values.
Example 1:
[simterm]
mysql> CREATE TABLE [table “” not found /]
(firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35), suffix VARCHAR(3), officeid VARCHAR(10), userid VARCHAR(15), username VARCHAR(8), email VARCHAR(35), phone VARCHAR(25), groups VARCHAR(15), datestamp DATE, timestamp TIME, pgpemail VARCHAR(255));
[/simterm]
Example 2:
[simterm]
mysql> create table [table “” not found /]
(personid INT(50) NOT NULL AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(35), middlename VARCHAR(50), lastname VARCHAR(50) default 'bato');
[/simterm]
More examples – in the Простая работа с MySQL в простых примерах #1 — работа с базами, создание таблиц and Простая работа с MySQL в простых примерах #2 — наполнение таблиц.
