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