MySQL tutorial for beginners

This tutorial describes basic usage of MySQL database - from starting MySQL server, adding users and their privileges, inserting, updating and deleting data in database.

Starting MySQL v5.x server

Open Terminal window and navigate to:

$ cd /usr/local/mysql/bin

In bin directory there are many scripts for administration of mysql database. If these scripts are not added in the bin PATH, they must be invoked with './' prefix.

Start MySql database (as root):

$ mysqld_safe

Show version:

$ mysqladmin version

Check connection to database:

$ mysqladmin ping

Show help:

$ mysqladmin --help

Show existing databases:

$ mysqlshow

Show tables (eg. mysql):

$ mysqlshow 'table_name'

Adding users and their privileges

Log into MySql as root user:

$ mysql -u root -p

Users are defined as combination of username and host from where they connect to the database.

Create new user (unique user is defined by username and hostname):

mysql> CREATE USER 'user'@'hostname' IDENTIFIED BY 'password';

Show all users:

mysql> SELECT user, host FROM mysql.user;

Add privileges (access rights) for user (some examples):

mysql> GRANT ALL ON 'database_name'.'table' TO 'user'@'hostname';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.100';
mysql> GRANT SELECT ON *.* TO 'test'@'192.168.1.%';
mysql> GRANT SELECT, INSERT ON animals.* TO 'test'@'localhost';

Show grants:

mysql> SHOW GRANTS;
mysql> SHOW GRANTS FOR 'user'@'hostname';

Remove grants by removing the user:

mysql> DROP USER 'user'@'hostname';

Show connected users:

mysql> SHOW processlist;

mysql> SELECT SUBSTRING_INDEX(host, ':', 1) AS host_short, GROUP_CONCAT(DISTINCT USER) AS users, COUNT(*) FROM information_schema.processlist GROUP BY host_short ORDER BY COUNT(*), host_short;

mysql> SELECT USER();

Create new database

Connect to MySql as a normal user (eg. test, and make sure that the user has appropriate privileges assigned).

$ mysql -u test -p test

Create new database:

mysql> CREATE DATABASE animals;

Show existing databases:

mysql> SHOW DATABASES;

Select new database to start using it:

mysql> USE animals;

Show which database is currently in use:

mysql> SELECT DATABASE();

Inside 'animals' database create new table 'zebras':

mysql> CREATE TABLE zebras (name CHAR(20) DEFAULT '' NOT NULL, sex CHAR(1) DEFAULT '' NOT NULL, birth DATE, weight INT(5) UNSIGNED DEFAULT '0', height DOUBLE(5,2) UNSIGNED DEFAULT '000.00');

Add new column id of type integer to existing table:

mysql> ALTER TABLE zebras ADD id INT;

Show tables in current database:

mysql> SHOW TABLES;

Show details about table:

mysql> DESCRIBE animals;

Insert entries

Insert data:

mysql> INSERT INTO zebras VALUES ('Wilma','f','2003-08-11','234','752.14', '1965');

Insert data from .sql file (from Linux console):

$ mysql -u test database_name < /path/to/file.sql

Insert data from .sql file (from MySql console):

mysql> source /path/to/file.sql

Import data from plain text file. Create a file 'allMyZebras.txt' with following content:

Gordon m 2001-12-09 313 865.22 2858
Lucy f 2002-02-15 152 433.86 1327

Now import data from file into database:

mysql> LOAD DATA LOCAL INFILE '/Path/To/Your/File/allMyZebras.txt' INTO TABLE zebras;

Unix-like systems use '\n' as a line separator, while Windows use clumsy '\r\n' line terminators. To avoid problems with line separators on importing file use:

mysql> LOAD DATA LOCAL INFILE 'C:/Path/To/Your/File/allMyZebras.txt' INTO TABLE zebras LINES TERMINATED BY '\r\n';

Read data

SELECT statement syntax:

SELECT what_to_select FROM which_table WHERE conditions_to_satisfy;

Examples on selecting rows:

mysql> SELECT * FROM zebras;
mysql> SELECT * FROM zebras WHERE name = 'Lucy';
mysql> SELECT * FROM zebras WHERE birth >= '2002-1-1';
mysql> SELECT * FROM zebras WHERE birth >= '2002-1-1';
mysql> SELECT * FROM zebras WHERE weight >= '200' AND sex = 'f';

Selecting columns:

mysql> SELECT name, sex FROM zebras;

mysql> SELECT name, birth FROM zebras WHERE sex = 'm' OR birth >='2003-1-1';

If more than one zebra with the same name exist, show only non-duplicated names:

mysql> SELECT DISTINCT name FROM zebras;

Sorting rows:

mysql> SELECT name FROM zebras ORDER BY birth;

Pattern matching:

mysql> SELECT * FROM zebras WHERE name LIKE 'L%';
mysql> SELECT * FROM zebras WHERE name LIKE '%y%';

Get entries with exactly 5 letters:

mysql> SELECT * FROM zebras WHERE name LIKE '_____';

'.' matches any single character

Advanced selections

Selecting rows by comparing to a list of values:

mysql> SELECT name FROM zebras WHERE id IN (1327, 2858);

Selective selections - select ids of those zebras, whose name ends with 'y'

mysql> SELECT id FROM zebras WHERE name IN (SELECT name FROM zebras WHERE name LIKE '%y');

Select items in a specific order:

mysql> SELECT * FROM zebras WHERE sex='f' ORDER BY name ASC;

Counting rows:

mysql> SELECT COUNT(*) FROM zebras;

Group rows with the same column values:

mysql> SELECT sex FROM zebras GROUP BY sex;

Create temporary column 'number':

mysql> SELECT sex, COUNT(*) AS number FROM zebras GROUP BY sex;

Making calculations:

mysql> SELECT name, CURDATE(), (YEAR(CURDATE())-YEAR(birth)) -
    -> (RIGHT(CURDATE(),5)<RIGHT(birth,5)) AS age FROM zebras;

Update data

Change the value of the field:

mysql> UPDATE zebras SET name='Cindy' WHERE name='Lucy';

Delete data

Delete single entry from the table:

mysql> DELETE FROM zebras WHERE name='Gordon';

Delete all entries from the table:

mysql> DELETE FROM zebras;

Delete table:

mysql> DROP TABLE zebras;

Shutting down database

Exit from MySql database:

mysql> exit

Shutdown MySql database:

$ mysqladmin -u root shutdown