Tech School

User Tools

Site Tools

Trace: • lists • get
en:web_development:databases:tables

Differences

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

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
en:web_development:databases:tables [2021/12/23 05:17]
mag [Tables] 		en:web_development:databases:tables [2023/08/16 09:33] (current)
Line 25: Line 25:
 ==== Fields ==== ==== Fields ====
   * Click on the 'Structure' tab.   * 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', which is a piece of data. Each column in the 'Browse' view is a 'field'.   * 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 (a variable number of characters). 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.
Line 38: Line 38:
   * Now take another look at the 'query' (just below the yellow row at the top).   * 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.   * 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.+  * It says 'show me everything in the heroes table'. Let's take a closer look.
 <code>SELECT * FROM `heroes` </code> <code>SELECT * FROM `heroes` </code>
   * A query is a command to the database for information or to take an action.   * A query is a command to the database for information or to take an action.
Line 46: Line 46:
   * <html>FROM</html> tells the database that the following items are the data sources. In this case, it's the table 'heroes'.   * <html>FROM</html> tells the database that the following items are the data sources. In this case, it's the table 'heroes'.
  
-==== Restricting Data ====+==== 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.   * 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.   * Perhaps we're only interested in the characters with magic.
Line 53: Line 53:
 <code>SELECT * FROM `heroes` WHERE 1</code> <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.   * 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. 
-  * Add <html> WHERE power='Magic'</html>+  * 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]]
en/web_development/databases/tables.1640265430.txt.gz · Last modified: 2023/08/16 09:33 (external edit)

Page Tools

Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
CC Attribution-Share Alike 4.0 International Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki