Databases

The table database object is the foundation of every Relational DataBase Management System

 

Every database has one or more tables

Each table has its own unique name and consists of row and columns (akin to a spreadsheet)

 

Database columns (aka fields), have their own unique names and pre-defined data types (integer, character, date, boolean, etc.)

 

Database rows (aka records) contain the actual data

 

 

Engine aka Table Type

 

  • MyISAM default engine of mysql
    • Does NOT support foreign key
    • Stores databases in: /var/lib/mysql/db_name
    • Consists of three files:
      • table_name.frm structure of the database
      • table.MYD data
      • table.MYI index
  • INNODB
    • Supports foreign key
    • Innodb has just one file, containing structure, data and index; called db_name.frm
  • my.ini Windows
  • my.conf Linux

SQL

SQL (Structured Query Language)

 

The standard language for manipulating databases

 

Consists of two parts:

  • DML(Data Manipulation Language), query and update commands:
    • SELECT -- extracts a copy of the requested data from a database
    • UPDATE -- updates data in a database
    • DELETE -- deletes data from a database
    • INSERT INTO -- inserts new data into a database
  • DDL(Data Definition Language), create and delete tables:
    • CREATE DATABASE -- creates a new database
    • ALTER DATABASE -- modifies a database
    • DROP DATABASE -- deletes a database
    • CREATE TABLE -- creates a new table
    • ALTER TABLE -- modifies a table
    • DROP TABLE -- deletes a table
    • CREATE INDEX -- creates an index (search key)
    • DROP INDEX -- deletes an index

MySQL Server Logon

To logon to the MySQL server type the following at the command prompt:

mysql -u username -pSecret

 

*Note: there is NO SPACE between the -p and the actual password (in this example: ‘Secret’ )

 

IF you leave a space after -p, you will prompted for the password:

 

Enter password:

 

 

This will log you on to the server and provide the mysql> prompt:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 50
Server version: 5.5.28 MySQL Community Server (GPL) 

 

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

 

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

 

mysql>

 

 

Once logged on, all commands must be terminated with a semi-colon ;

 

\! clear to clear the screen (\! executes a shell command)

 

\s to see the current status information of the server

 

\q to quit

Creating Databases and Tables

To see the current databases on the system: SHOW DATABASES ;

mysql> show databases ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| phpmyadmin         |
+--------------------+
4 rows in set (0.00 sec)

 

 

To create a database called Academy:  CREATE DATABASE Academy ;

mysql> CREATE DATABASE Academy ;
Query OK, 1 row affected (0.00 sec)

 

 

In order to work with a database it  must be selected with the “use” command:  use Academy ;

mysql> use Academy ;

 

 

Now the database has been defined and is in use, we can create a table to store the data:  CREATE TABLE

mysql> CREATE TABLE IF NOT EXISTS Students ( 

id int(5) NOT NULL auto_increment,

First varchar(20) NOT NULL,

Surname varchar(20) NOT NULL,

Age int(5) NOT NULL,

Town varchar(20) NOT NULL,

PIN int(5) NOT NULL,

Study varchar(20) NOT NULL,

Score int(5) NOT NULL,

PRIMARY KEY (id)

) ;

Which has created an empty table with the fields ready to populated:

 

 

 

To see the current tables for the database in use: SHOW TABLES ;

mysql> show tables ;
+--------------------+
| Tables in Academy  |
+--------------------+
| Students           |
+--------------------+
1 rows in set (0.00 sec)

 

 

We can then use the DESCRIBE  (aka DESC) command to check the table schema: DESCRIBE Students ;

mysql> desc Students;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(5)      | NO   | PRI | NULL    | auto_increment |
| First   | varchar(20) | NO   |     | NULL    |                |
| Surname | varchar(20) | NO   |     | NULL    |                |
| Age     | int(5)      | NO   |     | NULL    |                |
| Town    | varchar(20) | NO   |     | NULL    |                |
| PIN     | int(5)      | NO   |     | NULL    |                |
| Study   | varchar(20) | NO   |     | NULL    |                |
| Score   | int(5)      | NO   |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

 

 

We could also make a copy of an existing table, using CREATE TABLE pupils LIKE students;

mysql> CREATE TABLE pupils LIKE Students ;
Query OK, 0 rows affected (0.14 sec)

 

SHOW TABLES ;

mysql> show tables ;
+--------------------+
| Tables in Academy  |
+--------------------+
| pupils             |
| Students           |
+--------------------+
1 rows in set (0.00 sec)

Inserting Data into Tables

Two methods: LOAD DATA or INSERT INTO

 

LOAD DATA requires a text file containing one record entry per line, with the values separated by tabs, in the order in which the columns are listed in the create table or describe directory commands:

mysql> LOAD DATA LOCAL INFILE ‘/path/to/data.txt’ INTO TABLE Students ;
Query OK, 8 rows affected, 0 warnings (0.06 sec)
Records: 8 Deleted: 0 Skipped: 0 Warnings: 0

 

*Note: If the error: “ERROR 1148 (42000): The used command is not allowed with this MySQL version” is received, the LOAD DATA capability may have been disabled. To enable it, use the following at mysql startup: mysql -u root -p --local-infile=1

 

 

INSERT INTO using the manual method:

mysql> INSERT INTO Students (id, First, Surname, Age, Town, PIN, Study, Score) VALUES
(‘1’, ‘Pam’, ‘Frost’, ’21’, ‘London’, ‘1065’, ‘MySQL’, ’98’),
(‘2’, ‘James’, ‘Cross’, ’20’, ‘Watford’, ‘7519’, ‘Music’, ’88’),
(‘3’, ‘Andy’, ‘Jones’, ’34’, ‘Croydon’, ‘8198’, ‘Markets’, ’64’),
(‘4’, ‘David’, ‘Smith’, ’42’, ‘London’, ‘0151’, ‘Antiques’, ’82’),
(‘5’, ‘Julia’, ‘James’, ’17’, ‘London’, ‘2108’, ‘Maths’, ’77’),
(‘6’, ‘Phil’, ‘Watson’, ’36’, ‘Hook’, ‘9437’, ‘Cars’, ’44’),
(‘7’, ‘Terry’, ‘Waite’, ’58’, ‘Manc’, ‘2643’, ‘Music’, ’59’),
(‘8’, ‘Alex’, ‘Rose’, ’49’, ‘Cheam’, ‘0672’, ‘C’, ’70’);
Query OK, 8 rows affected (0.03 sec)
Records: 8 Duplicates: 0 Warnings: 0

 

The data has now been inserted into the table, akin to:

 

 

To insert more data, simply specify the fields and their associated values:

mysql> INSERT INTO Students (First, Surname, Age, Town, PIN, Study, Score) VALUES (‘Jim’, ‘Thomson’, ‘121’, ‘Bangkok’, ‘1198’, ‘Silk’, ’96’);
Query OK, 1 row affected (0.04 sec)

*Note: In this example the ‘id’ field has NOT been included, since (having set it to auto increment) it will supplied with the primary key’s next value

Viewing Tables

To view data the SELECT command is used.

 

The result is stored in a result table, called the result-set:

 

mysql> select * from Students ;
+----+-------+---------+-----+---------+------+----------+-------+
| id | First | Surname | Age | Town    | PIN  | Study    | Score |
+----+-------+---------+-----+---------+------+----------+-------+
| 1  | Pam   | Frost   | 21  | London  | 1065 | MySQL    | 98    |
| 2  | James | Cross   | 20  | Watford | 7519 | Music    | 88    |
| 3  | Andy  | Jones   | 34  | Croydon | 8198 | Markets  | 64    |
| 4  | David | Smith   | 42  | London  | 0151 | Antiques | 82    |
| 5  | Julia | James   | 17  | London  | 2108 | Maths    | 77    |
| 6  | Phil  | Watson  | 36  | Hook    | 9437 | Cars     | 44    |
| 7  | Terry | Waite   | 58  | Manc    | 2643 | Music    | 59    |
| 8  | Alex  | Rose    | 49  | Cheam   | 0672 | C        | 70    |
+----+-------+---------+-----+---------+------+----------+-------+
8 rows in set (0.00 sec)

In the above example, the wildcard * has been to use to signify that all fields are required.

 

Alternatively, just a single field could be specified:

mysql> select Study from Students ;
+----------+
| Study    |
+----------+
| MySQL    |
| Music    |
| Markets  |
| Antiques |
| Maths    |
| Cars     |
| Music    |
| C        |
+----------+
8 rows in set (0.00 sec)

 

Records can be filtered by a specified field using the WHERE command:

mysql> select * from Students WHERE Town = ‘London’ ;
+----+-------+---------+-----+---------+------+----------+-------+
| id | First | Surname | Age | Town    | PIN  | Study    | Score |
+----+-------+---------+-----+---------+------+----------+-------+
| 1  | Pam   | Frost   | 21  | London  | 1065 | MySQL    | 98    |
| 4  | David | Smith   | 42  | London  | 0151 | Antiques | 82    |
| 5  | Julia | James   | 17  | London  | 2108 | Maths    | 77    |
+----+-------+---------+-----+---------+------+----------+-------+
3 rows in set (0.00 sec)

Altering a Table

Use the ALTER keyword, along with the following to:

  • ADD adds a column to a table
  • CHANGE changes a column in a table
  • DROP deletes a column from a table

ADD a new field called Team to the table:

mysql> alter table Students ADD Team varchar(20) ;
Query OK, 8 rows affected (0.29 sec)
Records: 8 Duplicates: 0 Warnings: 0

 

Now let’s CHANGE that field to a different name and type:

mysql> alter table Students CHANGE Team Fees int(10) ;
Query OK, 8 rows affected (0.30 sec)
Records: 8 Duplicates: 0 Warnings: 0

 

Finally, let’s DROP the field:

mysql> alter table Students DROP Fees ;
Query OK, 8 rows affected (0.30 sec)
Records: 8 Duplicates: 0 Warnings: 0

 

 

A new field can be placed as the first column by adding FIRST at the end of the command:

mysql> alter table Students DROP Fees ;
Query OK, 8 rows affected (0.30 sec)
Records: 8 Duplicates: 0 Warnings: 0

RENAME a table

To rename a table, use the RENAME keyword:

mysql> rename table Students to pupils;
Query OK, 0 rows affected (0.04 sec)

 

 

Verify the table has been renamed:

mysql> show tables;
+-------------------+
| Tables_in_Academy |
+-------------------+
| customers         |
| orders            |
| pupils            |
+-------------------+
3 rows in set (0.00 sec)

Deleting Data, Tables and Databases

To DELETE data FROM a table, across all rows:

mysql> delete from Students ;
Query OK, 8 rows affected (0.04 sec)

 

TRUNCATE table, provides the same result:

mysql> truncate Students ;
Query OK, 0 rows affected (0.11 sec)

 

 

To DELETE a row in a table, use WHERE to select either a group or unique field data :

mysql> delete from Students where id = ‘4’ ;
Query OK, 1 row affected (0.05 sec)

 

Selecting a field with with the same value in multiple records will delete all those records containing that specified value:

mysql> delete from Students where Town = ‘London’ ;
Query OK, 3 rows affected (0.07 sec)

 

 

To delete a table, use DROP TABLE :

mysql> drop table Students ;
Query OK, 0 rows affected (0.05 sec)

 

 

To delete a database, use DROP DATABASE :

mysql> drop database Academy ;
Query OK, 0 rows affected (0.00 sec)

Update Data

To UPDATE an individual field for a specific record:

mysql> UPDATE Students SET Town = ‘Manch’ WHERE Town = ‘Manc’ ;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

 

To UPDATE an entire field for all records:

mysql> UPDATE Students SET Town = UPPER(Town);
Query OK, 8 rows affected (0.06 sec)
Rows matched: 8 Changed: 8 Warnings: 0

Find & Replace

The string REPLACE() function can be used to find and replace a text string within a table.

 

Syntax:

REPLACE(text_string, from_string, to_string)

 

Which can be used with the UPDATE command as follows:

UPDATE TABLE_NAME SET FIELD_NAME = REPLACE(FIELD_NAME, ‘find this string’, ‘replace found string with this string)

 

e.g. UPDATE wp_8_posts SET post_content = REPLACE(post_content, ‘advcpp‘, ‘intcpp)

this example will UPDATE all records in the wp_8_posts table where the post_content field contains advcpp and replace it with intcpp.

 

 

Another example:

 

SELECT REPLACE(‘www.tech-academy.co.uk’, ‘w’, ‘X’)

 

Which would result in ‘XXX.tech-academy.com’

Primary Key

Each record must have a unique identifier that sets it apart from all other records. This unique identifier is know as the Primary Key (or sometimes just the key).

 

 

Often, a field simply called “id” is used with an incrementing integer. This value is also usually set with the AUTO_INCREMENT parameter

 

CREATE TABLE IF NOT EXISTS Students ( id int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, …, final varchar(20) );

 

The primary key:

  • Only one per table
  • Cannot be null
  • Must never change
  • Must be unique

 

If DELETE is used to empty a table, the primary key’s position will not be removed and therefore any new data will continue from that point.

e.g. if there 20 records in the table, and DELETE was used to empty the table, any new data would start from the 21st position.

 

To overcome this the TRUNCATE keyword is used to empty the table and reset the primary key’s position so new data starts from 1

Foreign Key

Used to create a Parent/Child relationship between two tables.

 

Provides “Referential Integrity” (by using the same field in both tables), that ensures consistency of data across both tables (i.e. changes to one are reflected in the other).

 

The (child) Foreign Key links to the (parent) primary key of the related table, using the same field:

 

 

 

In the example above, the parent “Customers” table has a primary key called “CustID”, and the child “Orders” table has a primary key called “OrderID”. A relationship is established between the two tables by adding a foreign key in the child Orders table that links to the same “CustID” field in the parent Customers table.

 

This is achieved using the following command to create the child Orders table:

 

create table Orders ( OrderID int primary key not null auto_increment, CustID int not null, Part varchar(20), Cost int, FOREIGN KEY(CustID) REFERENCES Customers(CustID) ) ;

 

Accordingly, the child Orders table, has a CustID field that is set as its FOREIGN KEY that REFERENCES the reciprocating CustID field in the parent Customers table.

 

To delete data in Child automatically from Parent, add “on delete cascade” just before closing bracket:

 

create table Orders ( OrderID int primary key not null auto_increment, CustID int not null, Part varchar(20), Cost int, FOREIGN KEY(CustID) REFERENCES Customers(CustID) ON DELETE CASCADE) ;

  • only in innodb
  • not MyISAM

WHERE

Used to specify a selection criteria for specific records:

mysql> select First, Surname from Students WHERE Town=’London’;
+-------+---------+
| First | Surname |
+-------+---------+
| Pam   | Frost   |
| David | Smith   |
| Julia | James   |
+-------+---------+
3 rows in set (0.00 sec)

In this example, just the First and Surname fields have been requested for the criteria WHERE Town=’London’

 

 

Operators 

^ Match at Beginning of Line
= Equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN To specify multiple possible values for a column

 

This example selects records where the Score is less than 70:

mysql> select First, Surname from Students WHERE Score < ’70’;
+-------+---------+
| First | Surname |
+-------+---------+
| Andy  | Jones   |
| Phil  | Watson  |
| Terry | Waite   |
+-------+---------+
3 rows in set (0.00 sec)

Backup with mysqldump

Use the mysqldump program from the command line (outside of MySQL monitor).

 

The mysqldump program requires the user name and password (no space after the -p) followed by the database to backup, which is then redirected using > to your preferred target filename, preferably with the .sql extension:

d@workstation:~$ mysqldump -u root -pSecret Academy > myBackup.sql

 

Use the --databases option to backup multiple specified databases:

d@workstation:~$ mysqldump -u root -pSecret --databases Academy phpmyadmin > myBackup.sql

*Note: use of double -- before --databases

 

 

Use the --all-databases options to backup all the databases:

d@workstation:~$ mysqldump -u root -pSecret --all-databases > myBackup.sql

*Note: use of double -- before --all-databases

 

 

To backup just a specific table, specify the database name followed by the table name:

d@workstation:~$ mysqldump -u root -pSecret Academy Students > myBackup.sql

Restore a Database

If the backup file contains: CREATE DATABASE IF NOT EXISTS, it may be possible to do a quick restore from the command line:

d@workstation:~$ mysql -u root -pSecret < backup.sql

 

 

Otherwise, it will be necessary to ensure the target (empty) database exists, to receive the content:

mysql> create database myRestore ;
Query OK, 1 row affected (0.00 sec)

 

 

After quiting the MySQL monitor, use the command line to restore the database:

d@workstation:~$ mysql -u root -pSecret myRestore < backup.sql

Data Types

When creating a table, it is important to define the correct data type for a field to ensure optimal efficiency of the database

 

Three main categories:

  • Numeric
  • Date and Time
  • String

 

 

Numeric

INT Normal Integer.  11 digits wide: 0 to 4294967295, or -2147483648 to 2147483647 signed
TINYINT Very small Integer.  4 digits wide: 0 to 255, or -128 to 127 signed
SMALLINT Small Integer.  5 digits wide: 0 to 65535, or -32768 to 32767 signed
MEDIUMINT Medium Integer.  9 digits wide: 0 to 16777215, or -8388608 to 8388607
BIGINT Large Integer.  11 digits wide: 0 to 18446744073709551615, or -9223372036854775808 to 9223372036854775807 signed
FLOAT(M,D) Floating Point number. Signed. M sets the display length, D sets the decimal places. Default to 10,2. Precision: 24 places
DOUBLE(M,D) Double Precision Floating Point number. Signed. Precision: 53 places
DECIMAL(M,D) Floating point number stored as a char

 

 

Date and Time

DATE YYYY--MM-DD Format
DATETIME YYYY-MM-DD HH:MM:SS Format
TIMESTAMP YYYYMMDDHHMMSS. Without hypens or colons. Number of seconds elapsed since Unix Epoch 1 Jan 1970
TIME HH:MM:SS Format
YEAR(M) Year in 2 or 4 (default) digit format. YEAR(2) would provide 13 for 2013

 

 

Strings

CHAR(M) Fixed Length string between 1 to 255 Characters. M defines the length, default is 1 if not specified
VARCHAR(M) Variable length string between 1 to 255 Characters. Length must be defined in M
BLOB Field with a maximum length of 65535 Characters. Binary Large OBjects. Sorts and comparisons are case sensitive
TEXT Field with a maximum length of 65535 Characters. Sorts and comparisons are case sensitive
TINYBLOB Field with a maximum length of 255 Characters. Sorts and comparisons are NOT case sensitive
TINYTEXT Field with a maximum length of 255 Characters. Sorts and comparisons are case sensitive
MEDIUMBLOB Field with a maximum length of 16777217 Characters. Sorts and comparisons are NOT case sensitive
MEDIUMTEXT Field with a maximum length of 16777217 Characters. Sorts and comparisons are case sensitive
LONGBLOB Field with a maximum length of 4294967295 Characters. Sorts and comparisons are NOT case sensitive
LONGTEXT Field with a maximum length of 4294967295 Characters. Sorts and comparisons are case sensitive
ENUM Defined list for specific values. e.g. ENUM(‘A’,’B’,’C’) only allows A, B or C (& NULL) to exist in that field

 

LIKE

LIKE is used to search for a specified pattern in a field, usually with the % wildcard operator:

mysql> select * from Students where Town LIKE ‘%d’;
+----+-------+---------+-----+---------+------+-------+-------+
| id | First | Surname | Age | Town    | PIN  | Study | Score |
+----+-------+---------+-----+---------+------+-------+-------+
| 2  | James | Cross   | 20  | Watford | 7519 | Music | 88    |
+----+-------+---------+-----+---------+------+-------+-------+
1 row in set (0.00 sec)

 

 

The wildcard operator can be placed wherever required, for example %o%o%:

mysql> select * from Students where Town LIKE ‘%o%o%’;
+----+-------+---------+-----+---------+------+----------+-------+
| id | First | Surname | Age | Town    | PIN  | Study    | Score |
+----+-------+---------+-----+---------+------+----------+-------+
| 1  | Pam   | Frost   | 21  | London  | 1065 | MySQL    | 98    |
| 3  | Andy  | Jones   | 34  | Croydon | 8198 | Markets  | 64    |
| 4  | David | Smith   | 42  | London  | 0151 | Antiques | 82    |
| 5  | Julia | James   | 17  | London  | 2108 | Maths    | 77    |
| 6  | Phil  | Watson  | 36  | Hook    | 9437 | Cars     | 44    |
+----+-------+---------+-----+---------+------+----------+-------+
5 rows in set (0.00 sec)

 

 

Negate using NOT:

mysql> select * from Students where Town NOT LIKE ‘%o%o%’;
+----+-------+---------+-----+---------+------+-------+-------+
| id | First | Surname | Age | Town    | PIN  | Study | Score |
+----+-------+---------+-----+---------+------+-------+-------+
| 2  | James | Cross   | 20  | Watford | 7519 | Music | 88    |
| 7  | Terry | Waite   | 58  | Manc    | 2643 | Music | 59    |
| 8  | Alex  | Rose    | 49  | Cheam   | 0672 | C     | 70    |
+----+-------+---------+-----+---------+------+-------+-------+
3 rows in set (0.00 sec)

ORDER BY

Used to sort the results of a search in either ascending or descending order:

mysql> select Surname, PIN from Students ORDER BY PIN ;
+---------+------+
| Surname | PIN  |
+---------+------+
| Smith   | 0151 |
| Rose    | 0672 |
| Frost   | 1065 |
| James   | 2108 |
| Waite   | 2643 |
| Cross   | 7519 |
| Jones   | 8198 |
| Watson  | 9437 |
+---------+------+
8 rows in set (0.00 sec)

 

 

Or by descending order, append DESC to the command:

mysql> select Surname, PIN from Students ORDER BY PIN DESC;
+---------+------+
| Surname | PIN  |
+---------+------+
| Watson  | 9437 |
| Jones   | 8198 |
| Cross   | 7519 |
| Waite   | 2643 |
| James   | 2108 |
| Frost   | 1065 |
| Rose    | 0672 |
| Smith   | 0151 |
+---------+------+
8 rows in set (0.00 sec)

IN

Allows multiple items to be used within a search, by specifying the required items within parentheses before the keyword IN:

mysql> select * from Students where Surname IN (‘Jones’, ‘Waite’) ;
+----+-------+---------+-----+---------+------+---------+-------+
| id | First | Surname | Age | Town    | PIN  | Study   | Score |
+----+-------+---------+-----+---------+------+---------+-------+
| 3  | Andy  | Jones   | 34  | Croydon | 8198 | Markets | 64    |
| 7  | Terry | Waite   | 58  | Manc    | 2643 | Music   | 59    |
+----+-------+---------+-----+---------+------+---------+-------+
2 rows in set (0.00 sec)

 

Can also be negated using NOT

BETWEEN

Select a range of values BETWEEN two specified field values:

mysql> select * from Students where Score BETWEEN 40 and 60;
+----+-------+---------+-----+------+------+-------+-------+
| id | First | Surname | Age | Town | PIN  | Study | Score |
+----+-------+---------+-----+------+------+-------+-------+
| 6  | Phil  | Watson  | 36  | Hook | 9437 | Cars  | 44    |
| 7  | Terry | Waite   | 58  | Manc | 2643 | Music | 59    |
+----+-------+---------+-----+------+------+-------+-------+
2 rows in set (0.00 sec)

 

Can also be negated using NOT

Alias

Provides a shorcut for a table or field.

 

For tables:

SELECT column_name(s) FROM table_name AS alias_name ;

 

mysql> select Age from Students As text;
+-----+
| Age |
+-----+
|  21 |
|  20 |
|  34 |
|  42 |
|  17 |
|  36 |
|  58 |
|  49 |
+-----+
8 rows in set (0.00 sec)

 

 

For fields:

SELECT column_name AS alias_name FROM table_name ;

 

mysql> select Age as No from Students;
+----+
| No |
+----+
| 21 |
| 20 |
| 34 |
| 42 |
| 17 |
| 36 |
| 58 |
| 49 |
+----+
8 rows in set (0.00 sec)

 

 

Or assigning the CONCAT() function (to concatenate two fields) AS an alias:

mysql> SELECT CONCAT(FirstName, ‘ ‘, Surname) AS FullName FROM directory;
+--------------+
| FullName     |
+--------------+
| Jo Jones     |
| Paul Smith   |
| Terry Brains |
| Tony Bond    |
+--------------+
4 rows in set (0.00 sec)

 

 

 

Two tables example

customers table:

mysql> select * from customers;
+--------+-------+---------+------+
| CustID | First | Surname | Age  |
+--------+-------+---------+------+
|      1 | Pam   | Frost   |   21 |
|      2 | James | Cross   |   20 |
|      3 | Andy  | Jones   |   34 |
+--------+-------+---------+------+
3 rows in set (0.00 sec)

 orders table

mysql> select * from orders;
+---------+--------+---------+------+
| OrderID | CustID | Part    | Cost |
+---------+--------+---------+------+
|       1 |      3 | CPU     |  975 |
|       2 |      1 | RAM     |  433 |
|       3 |      2 | SSD     |  768 |
|       4 |      3 | Monitor |  250 |
+---------+--------+---------+------+
4 rows in set (0.00 sec)

 

It can be seen that the orders table has a CustID field, that relates to the customers table.

 

Assign two aliases:

  • c as the customers tables alias
  • o as the orders tables alias

SELECT o.OrderID, c.First, c.Surname FROM customers AS c, orders AS o WHERE c.Surname=’Cross’  AND o.CustID=c.CustID;

 

mysql> SELECT o.OrderID, c.First, c.Surname FROM customers AS c, orders AS o WHERE c.Surname=’Cross’ AND o.CustID=c.CustID;
+---------+-------+---------+
| OrderID | First | Surname |
+---------+-------+---------+
|       3 | James | Cross   |
+---------+-------+---------+
1 row in set (0.00 sec)

 

 

*Note: the keyword AS can be completely removed/replaced with a space:

mysql> SELECT o.OrderID, c.First, c.Surname FROM customers c, orders o WHERE c.Surname=’Cross’ AND o.CustID=c.CustID;
+---------+-------+---------+
| OrderID | First | Surname |
+---------+-------+---------+
|       3 | James | Cross   |
+---------+-------+---------+
1 row in set (0.00 sec)

Indexes

Used to increase the speed of queries

 

Type of table that stores the primary key (index field) and a pointer to each record in the target table.

 

INSERT and UPDATE commands take longer to execute, since the database also inserts or updates the index values.

 

It is recommended that indexes are only created on tables/fields that are frequently queried.

 

 

Creating an Index

 

CREATE INDEX index_label ON table_name(filed_name) ;

 

To create an index called myIndex on the customers table, allowing duplicates:

mysql> CREATE INDEX myIndex ON customers (CustID);
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0

 

 

To create a unique index called myIndex on the orders table, not allowing duplicates:

mysql> CREATE UNIQUE INDEX yourIndex ON orders (OrderID);
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0

 

 

Viewing an Index

 

SHOW INDEX FROM database.table ;

 

To view an index:

 

 

Removing an Index

 

Use DROP INDEX to remove an index:

mysql> drop index myIndex on customers;
Query OK, 0 rows affected (0.21 sec)
Records: 0 Duplicates: 0 Warnings: 0

Create a user

Use CREATE USER keywords:

mysql> create user derrick@localhost identified by ‘Secret’ ;
Query OK, 0 rows affected (0.00 sec)

create user derrick@localhost identified by ‘Secret’ ;

 

Creates a user: derrick

With password: Secret

User Priveleges

PRIVILEGES
ALL Grants all privileges
ALTER Alter the structure of existing tables; e.g. adding or renaming columns or tables & changing data types
CREATE Create new databases or tables
DELETE Delete existing rows
DROP Delete databases or tables
INDEX Create and drop indexes on tables
INSERT Insert new records into table
SELECT Select records from table
UPDATE Modify values in existing table
USAGE Grants no privileges. Can only logon. Additional privileges can be added later

 

Added or taken away using the GRANT or REVOKE keywords.

 

To setup and administrator:

mysql> GRANT ALL on * to admin identified by ‘password’ with grant option ;
Query OK, 0 rows affected (0.00 sec)

 

 

To revoke all privileges:

mysql> revoke all on * from admin ;
Query OK, 0 rows affected (0.00 sec)

 

 

Setup a user (called simon in this example) with no privileges, for the Academy database and all tables:

mysql> grant usage on Academy.* to simon identified by ‘password’ ;
Query OK, 0 rows affected (0.00 sec)

 

 

Grant privileges:

mysql> grant alter, create, delete, drop, index, insert, select, update on Academy.* to simon ;
Query OK, 0 rows affected (0.00 sec)

 

 

Revoke privileges:

mysql> revoke alter, drop, index on Academy.* from simon ;
Query OK, 0 rows affected (0.00 sec)

Change User Password

To change a user password, you need to UPDATE the user table in the mysql database and SET the new Password using the PASSWORD() function, using WHERE User = ‘user_name’ to select the desired user.

 

Logon as root run this command:

mysql> UPDATE mysql.user SET Password = PASSWORD(‘secret’) WHERE User = ‘root’;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 4 Changed: 0 Warnings: 0

 

 

Then run FLUSH PRIVILEGES, to tell MySQL to re-read those tables so that it will update the passwords in memory and allow the new passwords to be used:

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

JOIN

Allows data from more than one table to be output as the result set.

 

Consider the two tables in the same database:

customers table:

mysql> select * from customers;
+--------+-------+---------+------+
| CustID | First | Surname | Age  |
+--------+-------+---------+------+
|      1 | Pam   | Frost   |   21 |
|      2 | James | Cross   |   20 |
|      3 | Andy  | Jones   |   34 |
+--------+-------+---------+------+
3 rows in set (0.00 sec)

 orders table

mysql> select * from orders;
+---------+--------+---------+------+
| OrderID | CustID | Part    | Cost |
+---------+--------+---------+------+
|       1 |      3 | CPU     |  975 |
|       2 |      1 | RAM     |  433 |
|       3 |      2 | SSD     |  768 |
|       4 |      3 | Monitor |  250 |
+---------+--------+---------+------+
4 rows in set (0.00 sec)

 

It can be seen that the orders table has a CustID field, that relates to the customers table.

 

We can therefore use this to obtain specific data from the two tables, for instance the orders relating to just a specific customer:

mysql> SELECT orders.OrderID, orders.Part, orders.Cost FROM customers, orders WHERE customers.Surname = ‘Jones’ AND customers.CustID = orders.CustID;
+---------+---------+------+
| OrderID | Part    | Cost |
+---------+---------+------+
|       1 | CPU     | 975  |
|       4 | Monitor | 250  |
+---------+---------+------+
2 rows in set (0.00 sec)

 

 

In this case a JOIN has been made (although not explicitly stated) by use of the comma , between the two tables specified after FROM.

 

The comma , is short hand equivalent to INNER JOIN:

mysql> select orders.OrderID, orders.Part, orders.Cost from customers INNER JOIN orders where customers.Surname = ‘Jones’ and customers.CustID = orders.CustID;
+---------+---------+------+
| OrderID | Part    | Cost |
+---------+---------+------+
|       1 | CPU     |  975 |
|       4 | Monitor |  250 |
+---------+---------+------+
2 rows in set (0.00 sec)

 

 

The Surname field of the customers table is then used to query the data WHERE it equals ‘Jones’ and each table has the same CustID.

 

 

SELECT orders.OrderID, orders.Part, orders.Cost FROM customers, orders WHERE customers.Surname = ‘Jones’ AND customers.CustID = orders.CustID ;

Aggregate Functions

Used to return a result calculated from the values in a specified field

 

A few of the more common functions:

  • sum()
  • avg()
  • min()
  • max()
  • count()

*Note: Thousands of functions can be found here: http://dev.mysql.com/doc/refman/5.6/en/func-op-summary-ref.html

 

 

Consider the orders table:

mysql> select * from orders;
+---------+--------+----------+-----+------+
| OrderID | CustID | Part     | Qty | Cost |
+---------+--------+----------+-----+------+
|       1 |      3 | CPU      |   2 |  975 |
|       2 |      1 | RAM      |   3 |  433 |
|       3 |      2 | SSD      |   2 |  768 |
|       4 |      3 | Monitor  |   3 |  250 |
|       5 |      3 | MoBo     |   4 |  666 |
|       6 |      1 | Case     |   2 |  150 |
|       7 |      2 | HDD      |  10 |  325 |
|       8 |      3 | Keyboard |   5 |  250 |
+---------+--------+----------+-----+------+
8 rows in set (0.00 sec)

 

 

Use sum() on the Qty field to find out how many items have been sold:

mysql> select sum(Qty) from orders;
+----------+
| sum(Qty) |
+----------+
|       31 |
+----------+
1 row in set (0.00 sec)

 

 

Use avg() on the Cost field to get the average cost of an item:

mysql> select avg(Cost) from orders;
+-----------+
| avg(Cost) |
+-----------+
|  452.1250 |
+-----------+
1 row in set (0.00 sec)

 

 

Use min() to find the cost of the cheapest item:

mysql> select min(Cost) from orders;
+-----------+
| min(Cost) |
+-----------+
|        50 |
+-----------+
1 row in set (0.00 sec)

 

Use max() to find the cost of the most expensive item:

mysql> select max(Cost) from orders;
+-----------+
| min(Cost) |
+-----------+
|       975 |
+-----------+
1 row in set (0.00 sec)

 

Use count() to find out how many orders have been placed by a specific customer:

mysql> select count(Qty) from orders where CustID =3;
+------------+
| count(Qty) |
+------------+
|          4 |
+------------+
1 row in set (0.00 sec)

GROUP BY

Used to group the output of the result set

 

Often required by aggregate functions, e.g.

  • sum()
  • avg()
  • min()
  • max()
  • count()

 

Consider the orders table:

mysql> select * from orders;
+---------+--------+----------+-----+------+
| OrderID | CustID | Part     | Qty | Cost |
+---------+--------+----------+-----+------+
|       1 |      3 | CPU      |   2 |  975 |
|       2 |      1 | RAM      |   3 |  433 |
|       3 |      2 | SSD      |   2 |  768 |
|       4 |      3 | Monitor  |   3 |  250 |
|       5 |      3 | MoBo     |   4 |  666 |
|       6 |      1 | Case     |   2 |  150 |
|       7 |      2 | HDD      |  10 |  325 |
|       8 |      3 | Keyboard |   5 |  250 |
+---------+--------+----------+-----+------+
8 rows in set (0.00 sec)

 

 

If we wanted to find out the total expenditure of each customer, we would use the sum() function to add all the individual order quantities by the cost and use GROUP BY to conveniently group each customers total:

mysql> select CustID, sum(Qty * Cost) AS total from orders group by CustID;
+--------+-------+
| CustID | total |
+--------+-------+
|      1 |  1599 |
|      2 |  4786 |
|      3 |  5614 |
+--------+-------+
3 rows in set (0.00 sec)

HAVING

Used with GROUP BY because the WHERE keyword cannot be used with aggregate functions.

 

To find all customers HAVING a total expenditure greater than 2000:

mysql> select CustID, sum(Qty*Cost) AS Total from orders group by CustID having sum(qty*cost) > 2000;
+--------+-------+
| CustID | Total |
+--------+-------+
|      2 |  4786 |
|      3 |  5614 |
+--------+-------+
2 rows in set (0.00 sec)

Current Date/Time

now()

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2012-12-07 15:38:03 |
+---------------------+
1 row in set (0.00 sec)

 

current_date() or curdate()

mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2012-12-07     |
+----------------+
1 row in set (0.00 sec)

 

current_time() or curtime()

mysql> select curtime();
+----------------+
| curtime() |
+----------------+
| 15:38:24       |
+----------------+
1 row in set (0.00 sec)

DISTINCT

Used to return just one row of identical rows:

mysql> select DISTINCT Town from Students;
+---------+
| Town    |
+---------+
| London  |
| Watford |
| Croydon |
| Hook    |
| Manc    |
| Cheam   |
+---------+
6 rows in set (0.00 sec)

 

 

Query ran upon:

mysql> select * from Students;
+----+-------+---------+-----+---------+------+----------+-------+
| id | First | Surname | Age | Town    | PIN  | Study    | Score |
+----+-------+---------+-----+---------+------+----------+-------+
|  1 | Pam   | Frost   |  21 | London  | 1065 | MySQL    |    98 |
|  2 | James | Cross   |  20 | Watford | 7519 | Music    |    88 |
|  3 | Andy  | Jones   |  34 | Croydon | 8198 | Markets  |    64 |
|  4 | David | Smith   |  42 | London  | 0151 | Antiques |    82 |
|  5 | Julia | James   |  17 | London  | 2108 | Maths    |    77 |
|  6 | Phil  | Watson  |  36 | Hook    | 9437 | Cars     |    44 |
|  7 | Terry | Waite   |  58 | Manc    | 2643 | Music    |    59 |
|  8 | Alex  | Rose    |  49 | Cheam   | 0672 | C        |    70 |
+----+-------+---------+-----+---------+------+----------+-------+
8 rows in set (0.00 sec)

Notice the duplicate Town field with London for records 1,4 & 5

LIMIT

Limits the returned result set to the number specified:

mysql> select Score from Students LIMIT 3;
+-------+
| Score |
+-------+
|    98 |
|    88 |
|    64 |
+-------+
3 rows in set (0.00 sec)

 

 

Alternatively, the starting position can be specified (starting from 0):

mysql> select Score from Students LIMIT 4, 3;
+-------+
| Score |
+-------+
|    77 |
|    44 |
|    59 |
+-------+
3 rows in set (0.00 sec)

 

In this example, LIMIT 4, 3 specifies that the result set will be limited to 3 starting from the 5th result (in relation to the result set starting from 0)

NULL

NULL indicates that a value does not exist

 

It has NO value and is NOT comparable or equivalent to the number zero

 

Can be used as a place holder for unknown values

 

Consider the table:

mysql> select * from Students;
+----+-------+---------+------+---------+------+----------+-------+
| id | First | Surname | Age  | Town    | PIN  | Study    | Score |
+----+-------+---------+------+---------+------+----------+-------+
|  1 | Pam   | Frost   | NULL | London  | 1065 | MySQL    |    98 |
|  2 | James | Cross   | 20   | Watford | NULL | Music    |    88 |
|  3 | Andy  | Jones   | 34   | Croydon | 8198 | Markets  |    64 |
|  4 | David | Smith   | 42   | London  | NULL | Antiques |    82 |
|  5 | Julia | James   | NULL | London  | 2108 | Maths    |    77 |
|  6 | Phil  | Watson  | 36   | Hook    | 9437 | Cars     |    44 |
|  7 | Terry | Waite   | 58   | Manc    | 2643 | Music    |    59 |
|  8 | Alex  | Rose    | 49   | Cheam   | NULL | C        |    70 |
+----+-------+---------+------+---------+------+----------+-------+
8 rows in set (0.00 sec)

 

Use IS NULL to obtain the records where a specified field is NULL:

mysql> select * from Students where PIN IS NULL;
+----+-------+---------+------+---------+------+----------+-------+
| id | First | Surname | Age  | Town    | PIN  | Study    | Score |
+----+-------+---------+------+---------+------+----------+-------+
|  2 | James | Cross   | 20   | Watford | NULL | Music    |    88 |
|  4 | David | Smith   | 42   | London  | NULL | Antiques |    82 |
|  8 | Alex  | Rose    | 49   | Cheam   | NULL | C        |    70 |
+----+-------+---------+------+---------+------+----------+-------+
3 rows in set (0.00 sec)

 

Conversely IS NOT NULL can be used to obtain records where the specified field is not NULL:

mysql> select * from Students where PIN IS NOT NULL ;
+----+-------+---------+------+---------+------+----------+-------+
| id | First | Surname | Age  | Town    | PIN  | Study    | Score |
+----+-------+---------+------+---------+------+----------+-------+
|  1 | Pam   | Frost   | NULL | London  | 1065 | MySQL    |    98 |
|  3 | Andy  | Jones   | 34   | Croydon | 8198 | Markets  |    64 |
|  5 | Julia | James   | NULL | London  | 2108 | Maths    |    77 |
|  6 | Phil  | Watson  | 36   | Hook    | 9437 | Cars     |    44 |
|  7 | Terry | Waite   | 58   | Manc    | 2643 | Music    |    59 |
+----+-------+---------+------+---------+------+----------+-------+
5 rows in set (0.01 sec)

 

 

 

*Note: CANNOT use the equality operator, as NULL = NULL will fail

Regular Expressions

Use the REGEXP keyword followed by the regular expression (in single quotes) against a query

 

Similar to Like %

 

The usual regular expression operators can be applied:

^ Beginning of string
$ End of string
. Any single character
[…] Any character listed between the square brackets
[^…] Any character not listed between the square brackets
p1|p2|p3 Alternation; matches any of the patterns p1, p2, or p3
* Zero or more instances of preceding element
+ One or more instances of preceding element
{n} n instances of preceding element
{m,n} m through n instances of preceding element

 

Consider the table:

mysql> select * from Students;
+----+-------+---------+------+---------+------+----------+-------+
| id | First | Surname | Age  | Town    | PIN  | Study    | Score |
+----+-------+---------+------+---------+------+----------+-------+
|  1 | Pam   | Frost   | NULL | London  | 1065 | MySQL    |    98 |
|  2 | James | Cross   | 20   | Watford | NULL | Music    |    88 |
|  3 | Andy  | Jones   | 34   | Croydon | 8198 | Markets  |    64 |
|  4 | David | Smith   | 42   | London  | NULL | Antiques |    82 |
|  5 | Julia | James   | NULL | London  | 2108 | Maths    |    77 |
|  6 | Phil  | Watson  | 36   | Hook    | 9437 | Cars     |    44 |
|  7 | Terry | Waite   | 58   | Manc    | 2643 | Music    |    59 |
|  8 | Alex  | Rose    | 49   | Cheam   | NULL | C        |    70 |
+----+-------+---------+------+---------+------+----------+-------+
8 rows in set (0.00 sec)

 

 

Use where Study REGEXP ‘^M’ in the query to obtain all records that start with M in the Study field:

mysql> select * from Students where Study REGEXP ‘^M’;
+----+-------+---------+------+---------+------+---------+-------+
| id | First | Surname | Age  | Town    | PIN  | Study   | Score |
+----+-------+---------+------+---------+------+---------+-------+
|  1 | Pam   | Frost   | NULL | London  | 1065 | MySQL   |    98 |
|  2 | James | Cross   |   20 | Watford | NULL | Music   |    88 |
|  3 | Andy  | Jones   |   34 | Croydon | 8198 | Markets |    64 |
|  5 | Julia | James   | NULL | London  | 2108 | Maths   |    77 |
|  7 | Terry | Waite   |   58 | Manc    | 2643 | Music   |    59 |
+----+-------+---------+------+---------+------+---------+-------+
5 rows in set (0.00 sec)

 

 

Use where Town REGEXP ‘don’ in the query to obtain all records that contain ‘don’ in the Town field:

mysql> select * from Students where Town REGEXP ‘don’;
+----+-------+---------+------+---------+------+----------+-------+
| id | First | Surname | Age  | Town    | PIN  | Study    | Score |
+----+-------+---------+------+---------+------+----------+-------+
|  1 | Pam   | Frost   | NULL | London  | 1065 | MySQL    |    98 |
|  3 | Andy  | Jones   | 34   | Croydon | 8198 | Markets  |    64 |
|  4 | David | Smith   | 42   | London  | NULL | Antiques |    82 |
|  5 | Julia | James   | NULL | London  | 2108 | Maths    |    77 |
+----+-------+---------+------+---------+------+----------+-------+
4 rows in set (0.00 sec)

Default Values

Default values can be assigned when a table is created using the DEFAULT keyword followed by the default value:

mysql> CREATE TABLE employees (Name CHAR(10) NOT NULL, PIN INT(5) DEFAULT NULL, Privilege varchar(10) DEFAULT ‘User’);
Query OK, 0 rows affected (0.35 sec)

 

Example

Insert some limited data:

mysql> INSERT INTO employees (Name, PIN) VALUES (‘Tom’, 3078), (‘Alison’, 1252), (‘Ian’, 4876);
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0

 

Insert some even more limited data:

mysql> INSERT INTO employees (Name) VALUES (‘Sue’);
Query OK, 1 row affected (0.04 sec)

 

View the table:

mysql> select * from employees;
+--------+------+-----------+
| Name   | PIN  | Privilege |
+--------+------+-----------+
| Tom    | 3078 | User      |
| Alison | 1252 | User      |
| Ian    | 4876 | User      |
| Sue    | NULL | User      |
+--------+------+-----------+
4 rows in set (0.00 sec)

 

Insert full record:

mysql> INSERT INTO employees (Name, PIN, Privilege) VALUES (‘Richard’, 2375, ‘Admin’);
Query OK, 1 row affected (0.05 sec)

 

View the table:

mysql> select * from employees;
+---------+------+-----------+
| Name    | PIN  | Privilege |
+---------+------+-----------+
| Tom     | 3078 | User      |
| Alison  | 1252 | User      |
| Ian     | 4876 | User      |
| Sue     | NULL | User      |
| Richard | 1252 | Admin     |
+---------+------+-----------+
5 rows in set (0.00 sec)