The most important part of a database is being able to store contents in a database and then retrieve them.
This first article in the tutorial will focus on precisely this.
How SQL works
It is very important to note that SQL is nothing more than a language used to perform tasks on a database management system. When an SQL command is run on a database, some application will run a specific request on the database.
When an SQL command is run on a MySQL database, the returned values will themselves be tables.
Inserting data
Data is put into the database through SQL commands. The INSERT INTO
keywords allow data to be put straight into
a table in the database.
Assuming data is to be put into the mytable
table in the database, a general pseudo-query could be generated as:
Insert 'Jamie' into the 'name' column and '21' into the 'age' column in the mytable table
With SQL commands, it's pretty similar. SQL uses the VALUES
keyword too:
INSERT INTO `mytable` (name, age) VALUES ("Jamie", 21)
SQL maps from the left set of parenthesis to the right set of parenthesis to generate a list of parameters for the database system to handle. Crucially, ordering matters here.
Whilst all of the samples in this tutorial use databases, tables and field names within strings, the field names do not need to be quoted. So it's perfectly normal to change the above sample to:
INSERT INTO mytable (name, age) VALUES ("Jamie", 21)
Updating a table
To update (or change the values in) a database, the UPDATE
keyword is used. The query is constructed using this keyword
alongside the SET
keyword.
Assuming a database has already been selected the query would look like this to update all First_name fields to "Jamie":
UPDATE `mytable` SET First_name="Jamie"
This query will replace the value of First_name with the value "Jamie".