Inserting data into our table can be done using a command called INSERT followed by the table name and a simple tuple of the data we would like to insert.
INSERT INTO stats VALUES (NULL, "John", 26, 22, 26+22, 104);INSERT INTO stats VALUES (NULL, "Jessica", 126, 82, 126+82, 312);INSERT INTO stats VALUES (NULL, "Nick", 8, 25, 8+25, 51);INSERT INTO stats VALUES (NULL, "Mike", 52, 12, 52+12, 174);INSERT INTO stats VALUES (NULL, "Katie", 26, 92, 26+92, 188);A couple of things to keep in mind when inserting data:We can set the id as NULL since it will auto increment (as explained in the previous section)Be sure that your data types of the data you are inserting match those of the table you have definedTo display all of the data in your table, simply run:SELECT * FROM stats;+—-+———+——-+———+——–+——-+| id | name | goals | assists | points | shots |+—-+———+——-+———+——–+——-+| 1 | John | 26 | 22 | 48 | 104 || 2 | Jessica | 126 | 82 | 208 | 312 || 3 | Nick | 8 | 25 | 33 | 31 || 4 | Mike | 52 | 12 | 64 | 174 || 5 | Katie | 26 | 92 | 118 | 88 |+—-+———+——-+———+——–+——-+There’s all our soccer stats.
Notice how “SELECT *” grabs everything from our table.
If you only wanted the name and number of goals, you can instead specify which columns you wish to retrieve:SELECT name,goals FROM stats;+———+——-+| name | goals |+———+——-+| John | 26 || Jessica | 126 || Nick | 8 || Mike | 52 || Katie | 26 |+———+——-+To update a row in our table we can use….
you guessed it, the UPDATE command!UPDATE stats SET goals=28 WHERE name="John";SELECT * FROM stats;+—-+———+——-+———+——–+——-+| id | name | goals | assists | points | shots |+—-+———+——-+———+——–+——-+| 1 | John | 28 | 22 | 48 | 104 || 2 | Jessica | 126 | 82 | 208 | 312 || 3 | Nick | 8 | 25 | 33 | 31 || 4 | Mike | 52 | 12 | 64 | 174 || 5 | Katie | 26 | 92 | 118 | 88 |+—-+———+——-+———+——–+——-+In the above query:UPDATE allows us to select the table we would like to update the data forSET performs the actual updatingWHERE defines which data cell will receive that particular updateRetrieving Data with SQL QueriesNow that we’ve populated our table with data, we’ll learn how to actually retrieve it.
This will be where the bulk of SQL work will be for those working in Data Science or Machine Learning — retrieving data to prepare it for exploration and training.
You’ve already done a query in the last section using the SELECT command.
This time we’ll run a few more complicated queries.
We can limit the number of rows we retrieve:SELECT * FROM stats LIMIT 3;+—-+———+——-+———+——–+——-+| id | name | goals | assists | points | shots |+—-+———+——-+———+——–+——-+| 1 | John | 28 | 22 | 48 | 104 || 2 | Jessica | 126 | 82 | 208 | 312 || 3 | Nick | 8 | 25 | 33 | 31 |+—-+———+——-+———+——–+——-+Or order our output by the number of goals a player scored:SELECT * FROM stats ORDER BY goals DESC;+—-+———+——-+———+——–+——-+| id | name | goals | assists | points | shots |+—-+———+——-+———+——–+——-+| 2 | Jessica | 126 | 82 | 208 | 312 || 4 | Mike | 52 | 12 | 64 | 174 || 1 | John | 28 | 22 | 48 | 104 || 5 | Katie | 26 | 92 | 118 | 88 || 3 | Nick | 8 | 25 | 33 | 31 |+—-+———+——-+———+——–+——-+We can combine our SELECT statement with the WHERE command to create a type of if-else query for retrieving data:SELECT * FROM stats WHERE goals > 20 and assists > 50;+—-+———+——-+———+——–+——-+| id | name | goals | assists | points | shots |+—-+———+——-+———+——–+——-+| 2 | Jessica | 126 | 82 | 208 | 312 || 5 | Katie | 26 | 92 | 118 | 88 |+—-+———+——-+———+——–+——-+MySQL even provides us with the ability to perform calculations.
Check out how we can compute the shooting percentage of each player and sort the table by those numbers:SELECT *, points/shots FROM stats ORDER BY points / shots DESC;+—-+———+——-+———+——–+——-+————–+| id | name | goals | assists | points | shots | points/shots |+—-+———+——-+———+——–+——-+————–+| 2 | Jessica | 126 | 82 | 208 | 312 | 0.
6667 || 3 | Nick | 8 | 25 | 33 | 51 | 0.
6471 || 5 | Katie | 26 | 92 | 118 | 188 | 0.
6277 || 1 | John | 28 | 22 | 48 | 104 | 0.
4615 || 4 | Mike | 52 | 12 | 64 | 174 | 0.
3678 |+—-+———+——-+———+——–+——-+————–+That concludes our easy introduction to SQL!If you’d like to practice using SQL queries, I’d highly recommend playing around with the HackerRank challenges which already provide pre-built databases and tables for query practice.
Like to learn?Follow me on twitter where I post all about the latest and greatest AI, Technology, and Science!.Connect with me on LinkedIn too!Recommended ReadingWant to learn more about Data Science?.The Python Data Science Handbook book is the best resource out there for learning how to do real Data Science with Python!And just a heads up, I support this blog with Amazon affiliate links to great books, because sharing great books helps everyone!.As an Amazon Associate I earn from qualifying purchases.
.