Differences

This shows you the differences between two versions of the page.

--- en:web_development:databases:tables [2021/12/22 17:33]
mag created
+++ en:web_development:databases:tables [2023/08/16 09:33] (current)
@@ Line 21: / Line 21: @@
   * Above the table, you can see a menu of tabs. You're currently in the 'Browse' tab where you can see the data.
   * Below that is a yellow row showing the number of rows in the result and how long it took to process. At this stage you don't need to worry about the processing time, but that can be very important for optimising complex queries.
-  * Below the yellow row is some text that says <html>SELECT * FROM `marvel` </html>. This is the SQL command that extracts the data. We'll spend more time looking at queries in the next activity.
+  * Below the yellow row is some text that says <html>SELECT * FROM `marvel` </html>. This is the SQL command that extracts the data. We'll spend more time looking at queries in a minute.
-==== Structure ====
+==== Fields ====
   * Click on the 'Structure' tab.
-  * Here we see a different table. It shows how the table is structured.
+  * Here we see a different table. It shows how the data table is structured.
-  * Each row corresponds to a field (column of data).
+  * Each row corresponds to a 'field', which is a piece of data. Each column in the 'Browse' view is a 'field'.
-  * Two of the fields have type 'varchar' which means text. The number in brackets is the number of characters that are allowed.
+  * Two of the fields have type 'varchar' which means text (a variable number of characters). The number in brackets is the number of characters that are allowed.
   * 'first_appeared' is of type integer with length 4 for storing the year the character first appeared in Marvel comics.
   * 'hero_villain' is of type 'set' which means only specific values will be accepted. In this case, the values are 'Hero' and 'Villain'.
   * All columns have a 'No' value for 'Null' which means they must have a value. In some cases, you might be happy not to have any information for a certain field, in which case this would be marked 'Yes'.
-  * Below this table, you'll see a section called 'Indexes'. An index is way of sorting the information, and every table must have at least one index, called the 'Primary' index that is unique. It would cause confusion and waste space if two rows could be the same.
+  * Below this table, you'll see a section called 'Indexes'. An index is way of sorting the information (like the letters on library books), and every table must have at least one index, called the 'Primary' index that is unique. Imagine having two rows for 'Iron Man' and updating each of them with different information. You'd never know which one was correct.
   * In this table, the primary index is the 'character_name' field.
+==== SELECT Queries ====
+  * Click on 'Browse' to see the data again.
+  * Now take another look at the 'query' (just below the yellow row at the top).
+  * When you clicked on the 'heroes' table, PHPMyAdmin ran this query for you automatically.
+  * It says 'show me everything in the heroes table'. Let's take a closer look.
+<code>SELECT * FROM `heroes` </code>
+  * A query is a command to the database for information or to take an action.
+  * The query you see here is the basic form of every SELECT query.
+  * Queries beginning with <html>SELECT</html> are asking for information and is immediately followed by the list of fields wanted.
+  * <html> * </html> means 'all fields'.
+  * <html>FROM</html> tells the database that the following items are the data sources. In this case, it's the table 'heroes'.
+==== Conditions ====
+  * This data set is currently very small, but imagine if it had every character from every Marvel film listed. It would get quite long, and we may not want to see the whole list.
+  * Perhaps we're only interested in the characters with magic.
+  * Click on the 'SQL' tab. Here we can write our own queries.
+  * There's a query already written for you.
+<code>SELECT * FROM `heroes` WHERE 1</code>
+  * This is very similar to the query in the 'Browse' tab except for the <html>WHERE 1</html> at the end.
+  * <html>WHERE</html> is always followed by conditions, and the results will only include rows that meet those conditions.
+  * In this case the condition is <html> 1 </html> which is the definition of 'true', so it will show everything.
+  * Change the query to the following.
+<code>SELECT * FROM `heroes` WHERE power='Magic'</code>
+  * Click the checkbox 'Retain query box' so that we don't lose our query, then click 'Go'.
+  * You can now see the table with only rows where our condition is met.
+  * We can further limit our results by using <html>AND</html>. Try the following query.
+<code>SELECT * FROM `heroes` WHERE power='Magic' AND hero_villain='Hero'</code>
+  * Only one character is returned in the results now because only one character meets both conditions.
+  * We can also do inclusive results using <html>OR</html>.
+<code>SELECT * FROM `heroes` WHERE power='Magic' OR power='Technology'</code>
+  * If you run this query, you should see a longer list of characters, but still not all. Now those with powers based on technology and magic are shown, but those with skill or biological powers are not shown.
+==== Sorting ====
+  * The next useful addition to SELECT queries is being able to sort results.
+  * Imagine you're building a shopping site and want to be able to see which of your users has spent the most money so you can give them some incentives or a bonus. Sorting makes this much easier. Try the following query.
+<code>SELECT * FROM `heroes` WHERE power='Magic' OR power='Technology' ORDER BY first_appeared</code>
+  * We're almost there, but if we want the highest number first, we'll need to reverse the order.
+<code>SELECT * FROM `heroes` WHERE power='Magic' OR power='Technology' ORDER BY first_appeared DESC</code>
+==== Limit Results ====
+  * The last part of common SELECT queries is limiting results to a certain number. In the previous example, we wanted to reward top spenders, but we'd probably only want to do it for, say, the top ten, so there's no need to see more than ten rows.
+  * We don't have ten rows in our result, so you won't see the effect of this unless we use a lower number. Let's try <html>LIMIT 1</html> to see the most recent character.
+<code>SELECT * FROM `heroes` WHERE power='Magic' OR power='Technology' ORDER BY first_appeared DESC LIMIT 1</code>
+  * This should give you a good understanding of tables and basic SELECT queries. In the next activity we'll look at combining results from multiple tables.
+[[en:web_development:databases:joins|Next: Joins]]

Tech School

User Tools

Site Tools

Differences

Page Tools