Post by Marti-Pair Furxheir S.H. on Oct 5, 2013 5:38:14 GMT -6
This week, the subject is MySQL and Databases.
I have already explained a little what databases is but I will provide more information today. Let it just be known that MySQL is the leading database engine for websites because it was the first powerful open source database engine to be deployed on a massive scale.
Today, there are alternatives like NoSQL, PostGreSQL, SQLLite, Berkley DB, MariaDB, etc…
But first, what is a database?
In short, it is a collection of data organized in a structure way. In general, this means that your database is divided in Table, composed of Fields (or columns) with a row for every of the elements present in that table.
For example, if you have a table for the clients of a company, you might have fields like "Name", "Address", "City", "State", "Country", "Zip", "Phone Number", "id", "Is A Recurring Client", etc…
Each field will have its own type, like INT (for a number), varchar(200) for a field of a maximum length, useful as they can be indexed, etc…
Every client would have a row in that table with their information, and for each of the fields a value would be associated.
One of the beauties of a database, is that when you create a new row, you do not need to specify a value for every field: some fields can have a default value, and some fields can be left NULL (without a value).
You can even have a single field to be auto-increment, meaning that each time a new row is created, that field is filled with the next available value. This is especially useful for created a unique index, such as client number.
Speaking of indexes, one of the key differences between using a database and simply using a flat file (such as a XML file), is that you can create indexes which drastically accelerate finding records you need.
If you instead store your data in an XML file, let's say the list of all of the books with the word "Mockingbird" in the title in a city library, you might need to load all of the books in memory and search thru them one by one to keep only those with "Mockingbird" in the title.
As the number of books increase, the operation would take and more and memory (eventually crashing the server) and more and more time (eventually timing out). With an Index, a database engine is able to rapidly locate the books without having to load everything in memory.
Introduction to SQL
SQL (Structured Query Language) is a language that is more or less standard amongst the various database engines. It is more or less like English and not composed of weird characters, but don't be fooled, it is still rigid.
I am not today going to show you the whole SQL language because that could be a full course in itself, in fact, it will be.
But there are 4 main operations you can do on a record and I will show you the equivalent in SQL. The 4 operations are known by the acronym CRUD: Create - Read - Update - Delete.
Create
Create allows you to create a new record in the database. Where you have N rows, you will have N+1 rows after that operation.
In SQL the operation is called "INSERT" and follows the following syntax:
For example, you could have:
Read
The Read operation allows you to retrieve data from the database. In SQL, the operation is called SELECT. I'll be honest with you, it's the most complex operation because some advanced queries can span multiple pages, and yes, I have personally written quite a few such advanced queries in my time.
One of the biggest strengths of the SELECT query is that not only can you retrieve information from database functions, database tables, but you can query multiple tables at once and use database functions on them.
But let's not get ahead of ourselves, let's start with a simply query:
This query will retrieve all of the fields of all clients with a name starting with A. How? Let's divide the query in its 3 components:
SELECT * : This part tells us what the query will return. In this case, all of the fields for all of the selected rows. We could put SELECT name, and only the name would be returned.
We could put SELECT name AS fullname, and we would even rename the field in the output from name to fullname. You can prefix by the table name a field, like SELECT clients.name.
We can even execute functions, like SELECT count(*) AS c, which would return a single row with a single field called c which will return the number of records selected by the query.
FROM clients indicates from which table(s) the data is selected.
You can specify multiple tables, like FROM clients, orders to list all of the clients and orders which match. However, be careful, if you specify 2 or more tables, it will return the combination of every matching row of each table. In short, you have 2 clients and they each have 2 orders, it will not return 4 rows (the client with the proper order, but rather 8 rows.
You can use JOIN to fix that or, more commonly, a condition in your WHERE. We'll see both in the full course on MySQL.
You can also rename the tables, like FROM clients c1 so that you would prefix the field names by c1. instead of clients.
This allows you to select from the same table twice, like FROM clients c1, clients c2, if, for example, you want to retrieve clients which have a relationship with another client.
WHERE name LIKE "A%" : This part tells us which records to return. There is a LOT you can do in a WHERE, and when you add functions from the database system and subqueries, the possibilities are limitless!
What are subqueries? It's a query within another query. For example, if you wanted to retrieve the list of clients who places a total of order over 10000$, you might have:
SELECT * FROM clients WHERE id IN (SELECT clientid FROM orders WHERE status="completed" GROUP BY clientid HAVING SUM(total) > 10000 );
In this case, we have one query:
By the way, we call the clientid field in the orders table a foreign key, because it is a field which contains the primary key of another table to create a relationship between the two tables.
This subquery is run first, and the result of that query is passed to the main query:
Subqueries have the advantage of usually being much faster than combining multiple tables because the database engine can optimize each part of the query independently from one another. BTW, many database engines allow you to nest subqueries within subqueries and you can use more than one subquery per main query. This is often how your SQL queries can become extremely long.
I personally find them easier to debug, because you can test the subquery first, and add it to the main query later.
In that subquery, we added two new sections: GROUP BY, which allows to combine records matching the WHERE, and HAVING which allows to select which GROUPs to return.
Let's me explain that again, as it is one of the least understood concept of SQL:
When you use GROUP BY, the WHERE portion is there to select which records will go into groups, and the HAVING is there to select which GROUPS will be returned.
The final component of a SELECT query is the ORDER BY statement which lets you select in which order the records will be returned. For example, you could have ORDER BY state, city which would first order by state, and then, for rows with the same state, to order by city. You can add DESC to a field to sort in descending order instead of ascending order.
MySQL lets you specify a LIMIT, like LIMIT 30 to return a maximum of 30 records, or LIMIT 300, 30, to skip 300 records and return the next 30, but not all databases support this feature in this exact syntax.
Update
Update lets you modify a record or a set of records in the database. It's syntax is:
The values can be a hard value, like SET state="AL", it can be the result of the function, or even another field, like SET oldstate=state.
The WHERE can often be as elaborate as a SELECT, but many database engine do not let you use nested queries or select from other tables. MSSQL (from Microsoft) doesn't even let you update more than 1 record at a time if you specify fixed values.
Delete
As you can imagine, DELETE removes rows from the database, and has the simplest syntax:
Each database engine has its own limitations on what the WHERE can be, but personally, I prefer to create a field named "deleted" and set it to 1 when a record is deleted as it enables to undelete deleted records.
Usage of Queries
In PHP, you can use the function mysql_query() to execute a query if you are already connected to mysql via the mysql_connect function and if a database is selected via mysql_select_db.
For CREATE, UPDATE and DELETE, that's the end of it. You can call mysql_affected_rows() to know how many rows where affected by these queries.
For SELECT, the function will return a result variable which you can pass to mysql_num_rows() to know how many rows where returned and to mysql_fetch_assoc() to retrieve an associative array with the results of the query.
No, we haven't learned what an array is yet, but here is an example:
There is a program called PHPMyAdmin which lets you manage your MySQL databases in a graphical user interface.
If you have a Cpanel account (such as one provided to you by the teacher), you have access to PHPMyAdmin in your Cpanel.
Notably, it will simplify the creation of a database table as I have not explained at all how to create such a table in this brief introduction to MySQL.
This is the heaviest class so far, I believe, and I had to go really quickly. I might have to provide corrections, so do not hesitate to ask questions.
Homework
Your homework is to get your hands on a MySQL database (if you have an account with me, I can help you with that), and a PHPMyAdmin installation (same here) and to create a table called "elevator", with the following fields:
uid : an autoincrement INT which is also the primary key
serialnumber: a varchar(20) field
lowestfloor: an int, default -1
highestfloor: an int, default 10
currentfloor: in int, default 0
direction : an enum, with 5 values: "stopped","idle", "up", "down", "firemode"
fireelevator: an int with a size of 1, with a default value of 0
You will then create 4 elevators, with the following rules:
- Only one of them will have fireelevator set to 1, it will have access to all of the floors (-1 to 11 )
- All of the other elevators cannot go to floor 11 (the penthouse)
- One of the elevators does go to the basement
- They each have a unique serial number composed of letters, numbers and dashes, with the same first 5 characters (the model number) followed by a dash and the remaining characters. They all have the same length.
Next class's homework will be to modify your existing PHP script to list the serial number of all elevators in a <ul> <li> list so you can already start on that assignment too if you want and have the time.
I do not know if the next class will be next week or not.
[/b][/b]
I have already explained a little what databases is but I will provide more information today. Let it just be known that MySQL is the leading database engine for websites because it was the first powerful open source database engine to be deployed on a massive scale.
Today, there are alternatives like NoSQL, PostGreSQL, SQLLite, Berkley DB, MariaDB, etc…
But first, what is a database?
In short, it is a collection of data organized in a structure way. In general, this means that your database is divided in Table, composed of Fields (or columns) with a row for every of the elements present in that table.
For example, if you have a table for the clients of a company, you might have fields like "Name", "Address", "City", "State", "Country", "Zip", "Phone Number", "id", "Is A Recurring Client", etc…
Each field will have its own type, like INT (for a number), varchar(200) for a field of a maximum length, useful as they can be indexed, etc…
Every client would have a row in that table with their information, and for each of the fields a value would be associated.
One of the beauties of a database, is that when you create a new row, you do not need to specify a value for every field: some fields can have a default value, and some fields can be left NULL (without a value).
You can even have a single field to be auto-increment, meaning that each time a new row is created, that field is filled with the next available value. This is especially useful for created a unique index, such as client number.
Speaking of indexes, one of the key differences between using a database and simply using a flat file (such as a XML file), is that you can create indexes which drastically accelerate finding records you need.
If you instead store your data in an XML file, let's say the list of all of the books with the word "Mockingbird" in the title in a city library, you might need to load all of the books in memory and search thru them one by one to keep only those with "Mockingbird" in the title.
As the number of books increase, the operation would take and more and memory (eventually crashing the server) and more and more time (eventually timing out). With an Index, a database engine is able to rapidly locate the books without having to load everything in memory.
Introduction to SQL
SQL (Structured Query Language) is a language that is more or less standard amongst the various database engines. It is more or less like English and not composed of weird characters, but don't be fooled, it is still rigid.
I am not today going to show you the whole SQL language because that could be a full course in itself, in fact, it will be.
But there are 4 main operations you can do on a record and I will show you the equivalent in SQL. The 4 operations are known by the acronym CRUD: Create - Read - Update - Delete.
Create
Create allows you to create a new record in the database. Where you have N rows, you will have N+1 rows after that operation.
In SQL the operation is called "INSERT" and follows the following syntax:
INSERT INTO [table_name] ( [column1], [column2], [column3], …) VALUES ( [value1], [value2],[value3], … );
For example, you could have:
INSERT INTO clients ( name, city, country) VALUES ( "Alice", "Capital City", "USA" );
Read
The Read operation allows you to retrieve data from the database. In SQL, the operation is called SELECT. I'll be honest with you, it's the most complex operation because some advanced queries can span multiple pages, and yes, I have personally written quite a few such advanced queries in my time.
One of the biggest strengths of the SELECT query is that not only can you retrieve information from database functions, database tables, but you can query multiple tables at once and use database functions on them.
But let's not get ahead of ourselves, let's start with a simply query:
SELECT * FROM clients WHERE name LIKE "A%";
This query will retrieve all of the fields of all clients with a name starting with A. How? Let's divide the query in its 3 components:
SELECT * : This part tells us what the query will return. In this case, all of the fields for all of the selected rows. We could put SELECT name, and only the name would be returned.
We could put SELECT name AS fullname, and we would even rename the field in the output from name to fullname. You can prefix by the table name a field, like SELECT clients.name.
We can even execute functions, like SELECT count(*) AS c, which would return a single row with a single field called c which will return the number of records selected by the query.
FROM clients indicates from which table(s) the data is selected.
You can specify multiple tables, like FROM clients, orders to list all of the clients and orders which match. However, be careful, if you specify 2 or more tables, it will return the combination of every matching row of each table. In short, you have 2 clients and they each have 2 orders, it will not return 4 rows (the client with the proper order, but rather 8 rows.
You can use JOIN to fix that or, more commonly, a condition in your WHERE. We'll see both in the full course on MySQL.
You can also rename the tables, like FROM clients c1 so that you would prefix the field names by c1. instead of clients.
This allows you to select from the same table twice, like FROM clients c1, clients c2, if, for example, you want to retrieve clients which have a relationship with another client.
WHERE name LIKE "A%" : This part tells us which records to return. There is a LOT you can do in a WHERE, and when you add functions from the database system and subqueries, the possibilities are limitless!
What are subqueries? It's a query within another query. For example, if you wanted to retrieve the list of clients who places a total of order over 10000$, you might have:
SELECT * FROM clients WHERE id IN (SELECT clientid FROM orders WHERE status="completed" GROUP BY clientid HAVING SUM(total) > 10000 );
In this case, we have one query:
SELECT clientid FROM orders WHERE status="completed" GROUP BY clientid HAVING SUM(total) > 10000
By the way, we call the clientid field in the orders table a foreign key, because it is a field which contains the primary key of another table to create a relationship between the two tables.
This subquery is run first, and the result of that query is passed to the main query:
SELECT * FROM clients WHERE id IN (RESULT OF THE SUBQUERY );
Subqueries have the advantage of usually being much faster than combining multiple tables because the database engine can optimize each part of the query independently from one another. BTW, many database engines allow you to nest subqueries within subqueries and you can use more than one subquery per main query. This is often how your SQL queries can become extremely long.
I personally find them easier to debug, because you can test the subquery first, and add it to the main query later.
In that subquery, we added two new sections: GROUP BY, which allows to combine records matching the WHERE, and HAVING which allows to select which GROUPs to return.
Let's me explain that again, as it is one of the least understood concept of SQL:
When you use GROUP BY, the WHERE portion is there to select which records will go into groups, and the HAVING is there to select which GROUPS will be returned.
The final component of a SELECT query is the ORDER BY statement which lets you select in which order the records will be returned. For example, you could have ORDER BY state, city which would first order by state, and then, for rows with the same state, to order by city. You can add DESC to a field to sort in descending order instead of ascending order.
MySQL lets you specify a LIMIT, like LIMIT 30 to return a maximum of 30 records, or LIMIT 300, 30, to skip 300 records and return the next 30, but not all databases support this feature in this exact syntax.
Update
Update lets you modify a record or a set of records in the database. It's syntax is:
UPDATE [table_name] SET [field1]=[value1], [field2]=[value2], … WHERE [condition]
The values can be a hard value, like SET state="AL", it can be the result of the function, or even another field, like SET oldstate=state.
The WHERE can often be as elaborate as a SELECT, but many database engine do not let you use nested queries or select from other tables. MSSQL (from Microsoft) doesn't even let you update more than 1 record at a time if you specify fixed values.
Delete
As you can imagine, DELETE removes rows from the database, and has the simplest syntax:
DELETE FROM [table_name] WHERE [condition]
Each database engine has its own limitations on what the WHERE can be, but personally, I prefer to create a field named "deleted" and set it to 1 when a record is deleted as it enables to undelete deleted records.
Usage of Queries
In PHP, you can use the function mysql_query() to execute a query if you are already connected to mysql via the mysql_connect function and if a database is selected via mysql_select_db.
For CREATE, UPDATE and DELETE, that's the end of it. You can call mysql_affected_rows() to know how many rows where affected by these queries.
For SELECT, the function will return a result variable which you can pass to mysql_num_rows() to know how many rows where returned and to mysql_fetch_assoc() to retrieve an associative array with the results of the query.
No, we haven't learned what an array is yet, but here is an example:
$result = mysql_query('SELECT name FROM clients WHERE name LIKE "A%";');
If ( $result && mysql_num_rows($result) > 0 ) {
echo '<h3>Clients beginning with A:</h3>';
echo '<ul>';
while ( $row = mysql_fetch_assoc($result) ){
echo '<li>'. $row['name'] . '</li>';
}
echo '</lu>';
}
There is a program called PHPMyAdmin which lets you manage your MySQL databases in a graphical user interface.
If you have a Cpanel account (such as one provided to you by the teacher), you have access to PHPMyAdmin in your Cpanel.
Notably, it will simplify the creation of a database table as I have not explained at all how to create such a table in this brief introduction to MySQL.
This is the heaviest class so far, I believe, and I had to go really quickly. I might have to provide corrections, so do not hesitate to ask questions.
Homework
Your homework is to get your hands on a MySQL database (if you have an account with me, I can help you with that), and a PHPMyAdmin installation (same here) and to create a table called "elevator", with the following fields:
uid : an autoincrement INT which is also the primary key
serialnumber: a varchar(20) field
lowestfloor: an int, default -1
highestfloor: an int, default 10
currentfloor: in int, default 0
direction : an enum, with 5 values: "stopped","idle", "up", "down", "firemode"
fireelevator: an int with a size of 1, with a default value of 0
You will then create 4 elevators, with the following rules:
- Only one of them will have fireelevator set to 1, it will have access to all of the floors (-1 to 11 )
- All of the other elevators cannot go to floor 11 (the penthouse)
- One of the elevators does go to the basement
- They each have a unique serial number composed of letters, numbers and dashes, with the same first 5 characters (the model number) followed by a dash and the remaining characters. They all have the same length.
Next class's homework will be to modify your existing PHP script to list the serial number of all elevators in a <ul> <li> list so you can already start on that assignment too if you want and have the time.
I do not know if the next class will be next week or not.
[/b][/b]