Linux: MySQL Basics

By Xah Lee. Date: 2012-09-19. Last updated: 2014-02-04.

This page is a basic tutorial on MySQL. This page assumes you are using Ubuntu Linux, but it applies to most unix/Linux too.

this tutorial is INCOMPLETE, WORK IN PROGRESS.

Install

To install MySQL, run this in terminal: sudo apt-get install mysql-server. (it will pop up a GUI window asking for a MySQL root user password, during the installation.)

The main component of MySQL is a server “mysqld” and MySQL command line client “mysql”. The server is usually installed at /usr/sbin/mysqld. Client is at /usr/bin/mysql.

MySQL File Locations

Here's MySQL's file locations.

/usr/bin: Client programs and scripts
/usr/sbin: The mysqld server
/var/lib/mysql: Log files, databases
/usr/share/info: Manual in Info format
/usr/share/man: Unix manual pages
/usr/include/mysql: Include (header) files
/usr/lib/mysql: Libraries
/usr/share/mysql: Miscellaneous support files, including error messages, character set files, sample configuration files, SQL for database installation
/usr/share/sql-bench: Bench

sudo mysqladmin -u root -h localhost password password

sudo netstat -tap | grep mysql

mysqladmin create database_name

Starting MySQL Server

/etc/init.d/mysqld start

else, you get this error if trying to use MySQL client:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysql.sock' (2)

to check if server is running: ps auwx | grep mysqld

Using MySQL Command Line Client

Here's some command-line documentation:

man mysql
mysql --help

The most common usage is this:

mysql -h host_name -u userName -p database_name

The -p means prompt for a password. The database_name specifies which database to use. If not given, none is selected. You can select a database using SQL.

Note: MySQL user names has nothing to do with the operating system's user names.

Example:

mysql -h localhost -u root -p

Here's a example of MySQL session:


SELECT VERSION();

Here's a sample output:

+-------------------------+
| version()               |
+-------------------------+
| 5.1.63-0ubuntu0.11.10.1 |
+-------------------------+
1 row in set (0.01 sec)

while in MySQL, what you type is SQL language.

Type quit to quit.

Important DB Admin SQL Statements

List all users


SELECT USER();

List all (existing) databases


SHOW DATABASES;

Show current database


SELECT DATABASE();

Switch to a database:


USE database_name

Creating a database.


CREATE DATABASE new_db_name;

Delete a database. (BE VERY CAREFUL with this command)


DROP DATABASE db_name

You can also use the command line util “mysqladmin” to create/delete database.

Important Show Table Commands

The following are most common commands for starting to work with a project.

List existing tables:


SHOW TABLES;

Show the definition of a table:


DESCRIBE table_name;

Creating a new table:


CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

Delete a table:


DROP TABLE table_name;

Most Common SQL Statements

SQL: INSERT

Populating database by a text file. The file should be tab-delimited for fields.


LOAD DATA LOCAL INFILE 'file_path' INTO TABLE table_name LINES TERMINATED BY '\n';

Insert a row. For auto-generated fields such as primary key, use “NULL”. Also, use “NULL” for fields you don't have a value.


INSERT INTO Members VALUES (NULL, 'joe','lee', '333-4444','joe@example.org');

Another way to insert, by field/value pairs.


INSERT INTO table_name SET col_name1=expr1, col_name2=expr2, …


UPDATE accounts SET date = '1989-08-31' WHERE id = 28822;

THE FOLLOWING IS WORK IN PROGRESS. Ignore it.

SQL: SELECT

SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy;

SELECT * FROM pet;

SELECT * FROM pet WHERE name = 'Bowser';

SELECT * FROM pet WHERE birth >= '1998-1-1';

mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

The preceding query uses the AND logical operator. There is also an OR operator:

mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL  |
| Slim     | Benny | snake   | m    | 1996-04-29 | NULL  |
+----------+-------+---------+------+------------+-------+

AND and OR may be intermixed, although AND has higher precedence than OR. If you use both operators, it is a good idea to use parentheses to indicate explicitly how conditions should be grouped:

SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') OR (species = 'dog' AND sex = 'f');

mysql> SELECT name, birth FROM pet;
+----------+------------+
| name     | birth      |
+----------+------------+
| Fluffy   | 1993-02-04 |
| Claws    | 1994-03-17 |
| Buffy    | 1989-05-13 |
| Fang     | 1990-08-27 |
| Bowser   | 1989-08-31 |
| Chirpy   | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim     | 1996-04-29 |
| Puffball | 1999-03-30 |
+----------+------------+

To find out who owns pets, use this query:

mysql> SELECT owner FROM pet;

mysql> SELECT DISTINCT owner FROM pet;
+--------+
| owner  |
+--------+
| Benny  |
| Diane  |
| Gwen   |
| Harold |
+--------+

You can use a WHERE clause to combine row selection with column selection. For example, to get birth dates for dogs and cats only, use this query:

mysql> SELECT name, species, birth FROM pet
    -> WHERE species = 'dog' OR species = 'cat';

mysql> SELECT name, birth FROM pet ORDER BY birth;

mysql> SELECT * FROM pet WHERE name LIKE 'b%';

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;

 CREATE TABLE event (name VARCHAR(20), date DATE, type VARCHAR(15), remark VARCHAR(255));

mysql> SELECT pet.name,
    -> (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age,
    -> remark
    -> FROM pet INNER JOIN event
    ->   ON pet.name = event.name
    -> WHERE event.type = 'litter';
+--------+------+-----------------------------+
| name   | age  | remark                      |
+--------+------+-----------------------------+
| Fluffy |    2 | 4 kittens, 3 female, 1 male |
| Buffy  |    4 | 5 puppies, 2 female, 3 male |
| Buffy  |    5 | 3 puppies, 3 female         |
+--------+------+-----------------------------+

query by batch script

mysql < sql_file.sql > mysql.out

sudo apt-get install apache2

apache web root at /var/www/

how to check if a package is installed? For example: “libapache2-mod-php5”

apt-get install php5
apt-get install libapache2-mod-php5
etc/init.d/apache2 restart

MySQL Frontend Tools

MySQL Workbench. http://www.mysql.com/products/workbench/

A great free MySQL tool for Mac OS X. SequelPro. http://www.sequelpro.com/