Linux: MySQL Basics

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. 2012-10-13

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.

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 ‹user name› -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, 'jane','lee', '333-4444','jane@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? ⁖ “libapache2-mod-php5”

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