Written by Before we begin…
For all of these tutorials I will be using mysql as the base format. Somethings may be different. And for databases I will be using phpMyAdmin so please familiarise yourself with this.
SQL – part 2
Database structure
For any MySql operations we need a database. And so let’s discuss a database:
Database
This is where all the data is stored, often multiple can be used but not at the same time. Say you have a blogging script. Every piece of data regarding that will be stored i to this database.
Table
A table is a part of a database such as a table for blog posts and a table for post comments. They can be interlinked but are often separated in Oder to maximise effectiveness as different tables can have different structures.
Row
A row is one record of data, these go into tables and are read across so one blogpost might have its own row maybe in the form: ID, Title, Content, Tags, Categories, Publisher, Date… And so on.
SQL – part 3
SQL DML and DDL
SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition Language (DDL).
The query and update commands form the DML part of SQL:
- SELECT – extracts data from a database
- UPDATE – updates data in a database
- DELETE – deletes data from a database
- INSERT INTO – inserts new data into a database
The DDL part of SQL permits database tables to be created or deleted. It also defines indexes (keys), specifies links between tables, and imposes constraints between tables. The most important DDL statements in SQL are:
- CREATE DATABASE – creates a new database
- ALTER DATABASE – modifies 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
SQL – part 4
Heres some data that we might want to work with
| ID | First Name | Last Name | Age | Country |
|---|---|---|---|---|
| 1 | Simon | Dangers | 23 | England |
| 2 | Simon | Fridge | 25 | England |
| 3 | James | Fridge | 46 | America |
| 4 | Jeremy | Plotter | 13 | Scotland |
| 5 | Paul | Stavers | 96 | Canada |
| 6 | Aaron | Stothert | 32 | England |
SQL – part 5
Using PHP
Let’s have a look at how we could use PHP…
1 2 3 4 5 6 7 8 9 | <?php // Make a MySQL Connection mysql_connect("localhost", "user", "pass") or die(mysql_error()); mysql_select_db("test") or die(mysql_error()); // Run MySQL query mysql_query("SQL GOODNESS GOES HERE") or die(mysql_error()); ?> |
So we connect to the database using mysql_connect then select the database and finally run our query. Often you can use phpmyadmin to help you write SQL if you are stuck.
SQL – part 6
Creating a table
Here’s our SQL goodness…
1 2 3 4 5 6 7 | CREATE TABLE people ( ID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(ID), FirstName VARCHAR(30), LastName VARCHAR(30), Age INT, Country VARCHAR(40)) |
Line 1: ID = name
INT = integer, whole number
NOT NULL = has to be populated
AUTO_INCREMENT = it will automatically take the next number to the previous auto value
Line 2: PRIMARY KEY = what a database uses as its primary way of determining rows, unique
Line 3: VARCHAR = a type of string that can contain most text types
line 4: 30 = the max length of the string, larger values will be truncated.
SQL – part 7
Inserting
So we have our table, let’s put some more data into it…
1 | INSERT INTO people VALUES (7, "Priyan", "Javan", 34, "India") |
And that will put in a new row, however we don’t have to use all the columns…
1 | INSERT INTO people (ID, FirstName, Age) VALUES (8, "Connor", 26) |
And our table looks like this:
| ID | First Name | Last Name | Age | Country |
|---|---|---|---|---|
| 1 | Simon | Dangers | 23 | England |
| 2 | Simon | Fridge | 25 | England |
| 3 | James | Fridge | 46 | America |
| 4 | Jeremy | Plotter | 13 | Scotland |
| 5 | Paul | Stavers | 96 | Canada |
| 6 | Aaron | Stothert | 32 | England |
| 7 | Priyan | Javan | 34 | India |
| 8 | Connor | 26 |
SQL – part 8
Inserting data from another table
There’s an easy and a hard way.
The hard way involves a complex insert and select statement however we could just use:
SELECT INTO
Let’s say we have another temporary table and we want to merge it with another…
1 2 3 | SELECT * INTO people FROM old_people |
And that would put all the data from old people into people.
And if you only want some data:
1 2 3 | SELECT FirstName, LastName, Age INTO people FROM old_people |
An that should just copy the 3 fields specified.
SQL – part 9
The fun bit
Let’s get to the fun bit… Reading the data!
1 | SELECT * FROM people WHERE 1 |
SELECT this is the bit that tells the SQL to read and get data
* this is a ‘wildcard’ which means all the columns
WHERE this is a clause which indicates what to search for
1 after the WHERE clause indicates all rows
This will return all of the data!
SQL – part 10
Distinct Values
When selecting data, we may wish to find uniques…
And this is easy, just 1 word…
DISTINCT
1 | SELECT DISTINCT Country FROM people WHERE 1 |
And this will search and display all of the unique Countries in the database.
See, easy!