SQL, short for Structured Query Language. SQL is the language we use to communicate with databases, SQL can be used within many other languages such as Python or Go to create a link and method of accessing, searching and manipulating the data within the database. SQL is the most popular method of querying / requesting and changing data within a database, if you are handling data then you should be familiar with SQL.
In this section we look at some of the most popular SQL methods.
VIDEO BY: Danielle Thé from Glitch
Before getting into selecting and manipulating data you should become familiar with some of the most common operators as seen in the table below.
SELECTING DATA FROM THE DATABASE
Using SQL to select data from the database, pulls data from the original database and creates a new report with just the data you have selected. It does not change the original data, changing data will come later in the delete, insert and update methods.
Select specified fields of data. Only return the columns that you want from a specified table.
SELECT column1, column2 FROM table
Example: SELECT gameID, gameName FROM currently_playing
Select data that meets a specified condition. For example select all players with a score greater than 10.
SELECT fields FROM table WHERE field (operator) condition
Example: SELECT * FROM currently_playing WHERE gameID = 3
SELECT AND / OR
Two make a selection that requires more than one conditional parameter to be met.
SELECT fields FROM table WHERE condition AND condition
Example: SELECT * FROM currently_playing WHERE gameName = "Pac Land" AND gameID = 3
To select data between certain values.
SELECT fields FROM table WHERE field BETWEEN value AND value
Example: SELECT * FROM currently_playing WHERE gameID BETWEEN 1 AND 3
You can create a search to find a field that contains a value within a string of values by using the wildcard. The wildcard use the % sign or * sign with some DB such as MS Access.
SELECT fields FROM table WHERE field LIKE %condition%
Example: SELECT * FROM currently_playing WHERE gameName LIKE "%Land%"
In this example games such as PacLand should be found.
INSERT DATA INTO THE DATABASE
There are two main ways of inserting data into the database. If you specify the columns where you want to insert data, then you should specify the data from each of those columns. If you do not specify the columns, then you should specify data for all columns(fields) with in the database.
Method 1: Do not specify the fields: INSERT INTO table VALUES (‘value1’,’value2’,’value3’)
Method 2: Specify the fields If your first field is a Primary key that is auto generated you might not want to insert into that field, using the next method you can pass values into the fields stipulated. INSERT INTO table ( field2, field3) VALUES (’value2’,’value3’) Example: INSERT INTO game_details (gameName, gameDeveloper) VALUES (‘Tetris’, ‘Pajitinov’)
UPDATE DATA IN THE DATABASE
To update a record within a table you can use the following SQL
UPDATE table SET field = value WHERE condition
Example UPDATE session_details SET score = '2175' WHERE gameID = 'G001'
DELETING DATA FROM THE DATABASE
To delete a record from a table you can use the following SQL
DELETE FROM table WHERE condition
Example: DELETE FROM game_details WHERE gameName = 'Tetris'