Before you can write any code, you first need to connect to a database
– Most hosting companies, such as HostGator.com, will supply a server with MySQL.
If you want to use SQL for free, then you may have to download XAMPP from https://www.apachefriends.org/download.html
Have a quick look at this video if you have any problems. Also, make sure you have your passwords and general security set up properly
To access phpmyadmin or a similar Graphical User Interface (GUI), log into your control panel and there should be an icon for all SQL programs (apologies for blurry screenshot):
To get started, click on the MySQL Databases icon (to the left of the phpmyAdmin icon)
You will then see a text-box, asking you to create a new database – so give it a name and click “Create Database”
Create a user by creating a new username and give the username a password
Just for now, give the new user All Privileges
Click to go back, then click back to the home page.
Now click on phpMyadmin on the homepage.
You should now see this screen, with your database listed on the top left under the “phpMyAdmin” logo
Click on the name of your database.
If, by any chance, you have an SQL file you want to import – you can click on the import tab, to import an sql file to create your database.
Click on the SQL tab to create a table for your database.
Create a table with the “CREATE TABLE” command.
For example, to make a table called People, use the following command:
CREATE TABLE People
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
You can insert data into your table using the INSERT command.
You can also save data in excel as a CSV. Do not add column names to the data in the spreadsheet. Just the data itself. The tab name on your spreadsheet, should also be the same as your tablename.
Go to Import tab in phpMyAdmin – then browse, then open/select your CSV file, make sure the CSV option is chosen on the import page in phpMyAdmin
See this video if you were unsuccessful in attempting this:
Important & Fundamental Commands
The SELECT command is used to select data from a database, the results are stored in a result table called the result-set.
SELECT * FROM people;
Will select an entire table called people (if such a table exists)
* is called the wildcard statement.
SELECT Name, Town FROM People;
Will return the columns called Name and Town
DISTINCT
SELECT DISTINCT town FROM people;
This query would give a distinct list; with one result for each town.
It will return only distinct/different entries from the town column
LIMIT
SELECT id, name FROM people LIMIT 5,10
This will retrieve 10 rows/entries of data, starting at the 6th entry.
Sorting Results
ORDER BY – will automatically arrange results low to high, alphabetically or numerically.
Example query:
SELECT name, address, FROM people ORDER BY id;
You can sort by ID, even if you don’t want to retrieve the ID numbers
Example query, order by county, and then when county is the same, order by name:
SELECT county, town, name FROM people ORDER BY county, name;
SELECT county, town, name FROM people ORDER BY county, name DESC;
This will sort Z-A
Data Filtering – WHERE
SELECT id, name FROM people WHERE id = 22
This will return the id and name of the person with the id of 22
SELECT id, name FROM people WHERE id != 22
This will return all the ids and names from people, where the ID is NOT equal to 22
SELECT id, name FROM people WHERE id <= 22
This will return the IDs and names from people with IDs less than 22
SELECT id, name FROM people WHERE id BETWEEN 22 AND 30
This will return the IDs and names of people with IDs between 22 and 30
SELECT id, name FROM people WHERE town = ‘Wrexham’
The above statement will treat Wrexham as a value, not a column name or anything, and will return all the IDs and names from people who live in Wrexham.
OR
SELECT id, name FROM people WHERE town = ‘Wrexham’ OR ‘Chester’
This query will return all the entries with people who live in wrexham or chester
AND
AND requires both to be true.
SELECT id, name FROM people WHERE town = ‘Wrexham’ AND’ age = ’22’
This query will return people from Wrexham, who are 22 years old.
References
Best website on the internet – https://buckysroom.org/videos.php
http://www.w3schools.com/sql/sql_create_table.asp
Small Business Dad 