MySQL

Peter Wad Sackett

 Help on using MySQL

The MySQL homepage is

[Link]
Very often you just use google to find details on the command you
want, like search for ’mysql select’.

The MySQL database is relational and uses SQL (Structured Query

Language) as interface vehicle for the command line.
SQL is a stateless language meaning that any SQL command will not
influence another command. There are no variables to carry meaning
from one command to the next.
If you want to carry meaning then you need to access the database
through a programming language like Perl or Python.
You start the MySQL client by typing ’mysql’ on the unix command
line. Every SQL statement must be terminated by semicolon ;
The following slides assumes using the MySQL client.
Note: A fork of MySQL is called MariaDB and is gaining popularity.

2 DTU Health Tech, Technical University of Denmark

 Navigating the database

List the databases available to you.

SHOW DATABASES;

Start using a database, here named ’pongo’.

USE pongo;

Show the tables in a database you are using.

SHOW TABLES;

Seeing how a table is defined, table is here named ’person’.

EXPLAIN person;

Just checking the first 10 rows of a table.

SELECT * FROM person LIMIT 10;

3 DTU Health Tech, Technical University of Denmark

 Creating a database

Creating a database is simple and requires nothing but the privileges

to do it. If you do not have the privileges, go bug your database
administrator. The example is creating the ’pango’ database.
CREATE DATABASE pango;
The database can be deleted. This also removes all tables in it.
DROP DATABASE pango;
Creating the tables that will ”live” in the database is much more
complex. You must first design the database according to 4NF, see
last lesson, and decide on the data types the the various attributes
should have. Then you should fill the tables with data.

Database names and table names follows the normal standard for
variable names, meaning the chars 0-9a-zA-Z_ are allowed in one
word.

4 DTU Health Tech, Technical University of Denmark

 Data types in MySQL – number types

MySQL supports a number of SQL data types in several categories:

numeric types, date and time types, string (character and byte)
types, spatial types, and the JSON data type.
Integer Types: INTEGER alias for INT – 4 bytes, SMALLINT -2 bytes,
TINYINT – 1 byte, MEDIUMINT 3 bytes, BIGINT – 8 bytes. All
integers can be SIGNED or UNSIGNED.
Fixed-Point Types (Exact value): Used when accuracy is very
important. DECIMAL alias for NUMERIC, used as DECIMAL(6, 3) for
storing 6 digits, where 3 Digits after decimal point.
Floating-Point Types (Approximate value): FLOAT alias for REAL – 4
bytes, DOUBLE – 8 bytes. Can be used similar to DECIMAL.
Bit-Value Type – BIT
Date types: DATE like YYYY-MM-DD - ’2016-10-28’, TIME
likeHH:MM:SS - ’13:37:45’, DATETIME (Stores both date and time)
like ’2016-10-28 13:37:45’, YEAR(Stores only year.) like ’2016’ and
TIMESTAMP (Similar to DATETIME but automatically updates when a
record is modified.)

5 DTU Health Tech, Technical University of Denmark

 Data types in MySQL – string types

CHAR used as CHAR(30) Fixed-length string, stores text with right

space padding, removes right space padding on retrivial. Max 255
chars.
VARCHAR used as VARCHAR(30), just stores and retrives text of
variable length. Max 65535 chars.
The BINARY and VARBINARY types are similar to CHAR and
VARCHAR, except that they contain binary strings rather than
nonbinary strings. That is, they contain byte strings rather than
character strings. Use Case: Encrypted data, Digital signatures,
Binary codes
BLOB is a binary large object that holds a variable amount of data,
Stores large binary files. like pictures, audio files, odf etc. 4 sizes;
TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB.
TEXT is similar to BLOB in all aspects, but is text data.
ENUM Allows only one value from a predefined list.
ENUM(‘Yes’,’No’,’Maybe’).
SET is a string object that can have zero or more values, each of
which must be chosen from a list of permitted values specified when
the table is created. Allows multiple values from a predefined list.
6 DTU Health Tech, Technical University of Denmark
 Data types in MySQL – string types

7 DTU Health Tech, Technical University of Denmark

 Data types in MySQL – other types

•Spatial data types: Spatial data types are used to store geographical or
location-based data. MySQL has data types that correspond to OpenGIS
classes. Some of these types hold single geometry values: GEOMETRY, POINT
(Stores a single location.
Example: (23.8103, 90.4125) Dhaka's latitude and longitude), LINESTRING
(Stores a line made of multiple points.), POLYGON (Stores an area or
boundary.)
GEOMETRY Can store any geometric object. Examples:Point, Line, Polygon.
The other data types hold collections of values: MULTIPOINT,
MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION.
MySQL supports a native JSON data type that enables efficient access to data
in JSON (JavaScript Object Notation) documents.
It is rather unlikely that you will run into use-cases for these data types, at
least in the beginning of your MySQL use.
Data Type Example
Multiple
MULTIPOINT
locations
MULTILINEST
Multiple roads
RING
MULTIPOLYG Multiple
ON regions
GEOMETRYCO Mixed
8 DTU Health Tech, Technical University of Denmark LLECTION geometries
 Creating a table

There are many options not mentioned here, but the basis is
CREATE TABLE person (
primID INT AUTO_INCREMENT NOT NULL,
name VARCHAR(30),
skill TINYINT UNSIGNED DEFAULT NULL,
PRIMARY KEY(primID));
The table ‘person’ is created with two fields; ‘primID’ which is an
integer which also is the primary key and ‘name’ which is a text
string of max 30 chars. Because the key primID is incremented
automatically, we don’t need to be concerned about this field when
inserting new data. Primary keys are always created with the NOT
NULL feature, as they have to have a defined value. The ‘skill’ can
have a default value of ‘null’ – the undefined value.

9 DTU Health Tech, Technical University of Denmark

 Inserting data

Simple insertion into tables can be in three ways. Here the previous ’person’
table is used. There is no concern about the primary key, since it is auto
generated.
INSERT INTO person VALUES (’John Doe’, 10);
INSERT INTO person (name, skill) VALUES (’John Doe’, 10);
INSERT INTO person SET skill=10, name=’John Doe’;
When a field is missing the default for the field will be inserted.
If the primary key is identical to an already existing key, the the insert will fail.
You can make it do an update instead.
INSERT INTO person VALUES (’John Doe’, 10) ON DUPLICATE KEY UPDATE
skill=skill+5;
Lastly, REPLACE can be used instead of INSERT. If the primary key does not
exist, it works just like INSERT, otherwise the old row is replaced with the new
data.
REPLACE works like:
If row exists → DELETE old row + INSERT new row
If row does not exist → INSERT new row
REPLACE INTO person VALUES (1, 'John', 20);

10 DTU Health Tech, Technical University of Denmark

 Inserting data from other tables

Assuming we have a table called ’dancers’ with a field called ’name’

all dancers can be inserted into the ’person’ table. The skill will start
as NULL.
Notice that the two ’name’ fields are from different tables. If you
need to differentiate between fields with same name in different
tables, use aliasing, see later.
INSERT INTO person (name) SELECT name FROM dancers;

If you want to give the dancers an initial skill of 20, do

INSERT INTO person (name, skill)
SELECT name,20 FROM dancers;

11 DTU Health Tech, Technical University of Denmark

 Deleting data

Deletion is even simpler, just be careful about what you select to be

deleted. The first example will remove everything.
DELETE FROM person;
DELETE FROM person WHERE skill=10;
DELETE FROM person WHERE skill>50 AND primID<20;
You need a WHERE clause which determines what rows to delete from
the given expression.

12 DTU Health Tech, Technical University of Denmark

 Modifying data

Modifying data in the table is fairly easy. Here the skill is set to 20
for everybody.
UPDATE person SET skill=20;
You often need to be more specific.
UPDATE person SET skill=30 WHERE name=’John Doe’;
UPDATE person SET name=’Jane Doe’, skill=10
WHERE primID=3;
You could double the skill for everybody below 30;
UPDATE person SET skill=skill*2 WHERE skill<30;

13 DTU Health Tech, Technical University of Denmark

 Retrieving data 1

Retrieving all rows from a table, use select.

SELECT * FROM person;
Just selecting names for skilled persons.
SELECT name FROM person WHERE skill>50;
The result can be sorted, even on several attributes
SELECT name, skill FROM person
WHERE skill < 15 ORDER BY skill, name DESC;

ORDER BY skill, name DESC

Sorts the result:
First by skill in ascending order (default).
If multiple rows have the same skill, sort those rows by name in
descending order.

14 DTU Health Tech, Technical University of Denmark

 Retrieving data 1

Student Course
StudentID Name CourseID CourseName
101 Rahim Database
CSE303
102 Karim Systems

103 Jannat CSE305 Data Mining

104 Nayeem

Enrollment
StudentID CourseID
101 CSE303
102 CSE303
104 CSE303
103 CSE305

15 DTU Health Tech, Technical University of Denmark

 Retrieving data 1

Find students enrolled in Database Systems (CSE303).

Method 1: Using Multiple Tables

SELECT [Link]
FROM Student, Enrollment
WHERE [Link] = [Link]
AND [Link] = 'CSE303';

Method 2: Using Subquery

SELECT Name
FROM Student
WHERE StudentID IN
(
SELECT StudentID
FROM Enrollment
WHERE CourseID='CSE303'
);

16 DTU Health Tech, Technical University of Denmark

 Retrieving data 2

Using aliases with AS, demonstrating INNER JOIN.

SELECT [Link], [Link]
FROM Customers AS c INNER JOIN Orders AS o
ON [Link]=[Link];

17 DTU Health Tech, Technical University of Denmark

 Aggregate functions

When retrieving data you can perform different aggregate functions

on it, like AVG, COUNT, MAX, MIN, SUM and several more.

How many rows in the table, what average skill?

SELECT COUNT(primID) FROM person;
SELECT AVG(skill) FROM person;

If an attribute is NULL it is ignored from these functions.

The function DISTINCT can be used to select the unique attributes.

Here used to eliminate duplicate names.
SELECT DISTINCT(name) FROM person;

Counting how many different skill levels exists.

SELECT COUNT(DISTINCT(skill)) FROM person;

18 DTU Health Tech, Technical University of Denmark

 Functions

There are over a 100 functions which you can use on the data. It
goes from simple upper casing of strings to exotic spatial functions.
Here is an example.
INSERT INTO person VALUES(UPPER(’John Doe’), 10);
SELECT FROM person WHERE UPPER(name) = ’JOHN DOE’;
Some basic and useful functions are:
CONCAT(string1, string2 ...) SELECT CONCAT('John', ' ', 'Doe’);

UPPER(string) and LOWER(string) SELECT UPPER('John Doe’);

TRIM(string) Removes extra spaces from the beginning and end.

SUBSTRING(string, position, length) Extracts part of a string.

SELECT SUBSTRING('Database',1,4); Result: Data

name LIKE ’%Doe’ simple pattern matching, % is wildcard

There are a number of matematical and date/time functions, which
can make life simpler.
[Link]
19 DTU Health Tech, Technical University of Denmark
 Functions

name LIKE ’%Doe’ simple pattern matching, % is wildcard

SELECT * Symbol Meaning

FROM person
Any number
WHERE name LIKE '%Doe’; % of
characters
Result: Exactly one
_
John Doe character
Jane Doe
% means anything can appear before "Doe".

LIKE Used for pattern matching.

SELECT * Output:
FROM Student Rahim
WHERE Name LIKE 'Ra%’; Rafi
Rasel

There are a number of matematical and date/time functions, which

can make life simpler.
[Link]
20 DTU Health Tech, Technical University of Denmark
 Loading data from a file into a table 1

Creating a table with insert statements is rather cumbersome and

slow. If you can produce data in some form of table, like a TSV or
CSV file, you can speedily load it into the database.
For TSV use
LOAD DATA LOCAL INFILE ’/path/to/file’ INTO TABLE person;
For CSV use
LOAD DATA LOCAL INFILE ’/path/to/file’ INTO TABLE person
FIELDS TERMINATED BY ’,’;

The columns in the file should correspond to the table definition.

More options are available, and this method should definitely be
looked up, when the need for data import arises.

21 DTU Health Tech, Technical University of Denmark

 Loading data from a file into a table 1

You are likely to get an error if you have not started mysql with
option: --local-infile
--local-infile enables MySQL to import data directly from local files
such as CSV or TSV files. If this option is not enabled, commands
like LOAD DATA LOCAL INFILE may generate an error. Because
allowing MySQL to read local files can create security risks if
malicious users gain access.
SHOW VARIABLES LIKE 'local_infile’;
If it returns: OFF then LOCAL INFILE is disabled.
Enable it:
SET GLOBAL local_infile = 1;
(Requires administrative privileges.)

22 DTU Health Tech, Technical University of Denmark

 Loading data from a file into a table 2

The standard MySQL LOAD DATA LOCAL INFILE into table fills empty
fields with empty string ’’. In order to get a NULL instead, you must
have a \N in the field in the data file.
Peter,Jensen,,42 Inserts empty string(Value exists but is empty)
Peter,Jensen,\N,42 Inserts NULL (Value is unknown or missing)

An alternative to this is using variables and functions.

LOAD DATA LOCAL INFILE '/tmp/[Link]’
INTO TABLE person
FIELDS TERMINATED BY ','(firstName, lastName, @a, @b)
SET skill = NULLIF(@a,''), age = NULLIF(@b,‘’);
Resulting Table

firstName lastName skill age

Peter Jensen NULL 42
Rahim Ahmed 10 NULL

23 DTU Health Tech, Technical University of Denmark

 Exporting data from the database

There are two smart ways of getting the data out of the database.
You can do a normal SELECT command and add INTO OUTFILE
’path/to/file’. (Useful for exporting data to CSV-like files.)
SELECT * FROM person INTO OUTFILE ’/path/to/file’;
You can also dump the entire database into a file. This is done from
the unix command line with the mysqldump command (Database
Backup Tool). There are in general three ways to use mysqldump - in
order to dump a set of one or more tables, a set of one or more
complete databases, or an entire MySQL server.
mysqldump db_name [tbl_name ...] > /path/to/file
mysqldump --databases db_name ... > /path/to/file
mysqldump --all-databases > /path/to/file
The database can be recreated on another database server like this.
cat /path/to/file | mysql
You must have the required permissions to create the database.

24 DTU Health Tech, Technical University of Denmark

 Exporting data from the database

There are two smart ways of getting the data out of the database.
You can do a normal SELECT command and add INTO OUTFILE
’path/to/file’. (Useful for exporting data to CSV-like files.)
SELECT * FROM person INTO OUTFILE ’/path/to/file’;
You can also dump the entire database into a file. This is done from
the unix command line with the mysqldump command (Database
Backup Tool). There are in general three ways to use mysqldump - in
order to dump a set of one or more tables, a set of one or more
complete databases, or an entire MySQL server.
mysqldump university Student Course > [Link]
Backs up only:Student table and Course table
mysqldump --databases university > [Link]
Backs up the entire database
mysqldump --all-databases > full_backup.sql
Creates a backup of the entire MySQL server.

25 DTU Health Tech, Technical University of Denmark

 Exporting data from the database

Restore a Database
Suppose you have:
university_backup.sql

Restore it using:
cat university_backup.sql | mysql
or
mysql < university_backup.sql

What Happens?

Backup File → MySQL Server → Database Recreated

Why Is Backup Important?

Suppose the server crashes and all student records are lost.
Without backup:
❌ Data permanently lost
With mysqldump backup:
✅ Database can be restored
✅ Student records recovered

26 DTU Health Tech, Technical University of Denmark