MySQL is a software that enables data to be stored in an organised way on a server that can then be queried (searched) and accessed by other languages to display meaningful and relevant information.
MySQL uses a language called SQL to access, modify and query the data inside a database.
We will be using PHPMyAdmin to manage and develop our databases
Use the following call to create a new Database
CREATE DATABASE testDB;
Then you need to create the tables for the database with the appropriate field names and data types:
CREATE TABLE Persons (
PersonID int primary key,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
When setting up a table, you need to set the data types for each filed. This is the type of data that will be stored in the DB.
CHAR(size)
Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis. Can store up to 255 characters
VARCHAR(size)
Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis. Can store up to 255 characters. Note:If you put a greater value than 255 it will be converted to a TEXT type
INT(size)
-2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The maximum number of digits may be specified in parenthesis
Insert in Table
List field names to insert
Values
list values or variables in the same order as the field list on line 1
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
SELECT * FROM customers
(Show every field in the table)
SELECT * FROM customers WHERE lastName ='Smith';
(Show every field in the table where the field lastname is like smith)
SELECT Name, age, DOB FROM customers WHERE age >=18;
(display on the name, age and DOB field from the customers table where the age is > 18)
= Equal (only to be used on integers and decimals)
<> Not equal. Note: In some versions of SQL this operator may be written as !=
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between a certain range
LIKE Search for a pattern in a string
IN To specify multiple possible values for a column
And - where two conditions need to be true
or - to search for one of two items in a database. i.e. one item is true
An aggregate function can summarise large sets of data by adding up all the values, finding an average of a data set etc.
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
SELECT AVG(column_name)
FROM table_name
WHERE condition;
SELECT SUM(column_name)
FROM table_name
WHERE condition;
The following SQL statement lists the number of customers in each country, grouped by country:
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;
Order by Country ASC
The following SQL statement lists the number of customers in each country, sorted high to low, grouped by country:
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;
When setting up the database in SQL, the relationships need to be defined with indexes and foreign keys.
To query the database across multiple tables we need to use a join clause. More info
In this example we need to list the table name and the field name using dot notation - see below
Table_name.field_name
Example
SELECT Infringements.ID,Infringements.Student,Infringements.Teacher, Infringements.Infringement,teachers.first,teachers.last
FROM infringements
INNER JOIN teachers ON teachers.initials = infringements.Teacher