Databases drive the modern web. Every big or dynamic website uses a database in some way, and when combined with Structured Query Language (SQL), the possibilities for manipulating data really are endless. If you already know SQL, make sure you checkout these programming skills all website developers should know.
Today I’ll be showing you some of the core commands you need to know as a programmer.
There are many names for data returned from a database table. Data is commonly referred to as Rows, Records, or Tuples. I’ll be using these terms interchangeably throughout this article.
All of the examples today will be based on four fictional tables. The customer table contains the name and age of customers: computer science computer science computer science computer science computer science
The heights table contains the name and height of any person: computer science computer science
The staff table contains the name and age of staff members — exactly the same as the customer table:
The final table called people contains the name and age of people, just like the customer and staff tables:
The select statement is the simplest, and it’s essential you understand it as it underpins nearly all of the other commands. It’s considered a best practice to write your reserved SQL words in uppercase, as it makes the command easier to read and understand. computer science computer science computer science computer science
As its name implies, select is used to select data from a database. Here’s the simplest usage:
SELECT * FROM table;
There are two parts to this. The first part (SELECT *) specifies which columns you would like to select. The asterisk indicates that you wish to select all the columns in the table. The second part (FROM table) tells your database engine where you would like to retrieve this data from. Replace “table” with the name of your database table.
This select is known as “select star.” Using the asterisk is a good way to figure out what data is in a table, but I don’t recommend you use it for any production code. When using a select star, it’s up to the database engine to present you with the data you want. You don’t have any control over the order the data is returned, so if somebody adds a new column to the table, you may find your variables in your programming language no longer represent the correct data. Fortunately, there is a solution. computer science computer science
You can explicitly state which columns you would like to retrieve, like this:
SELECT age, name FROM people;
This query retrieves the “age” and “name” columns from the “people” table. Being this explicit can be slightly tedious if you have a lot of data, but doing so will reduce problems in the future, along with making your SQL easier to understand by any future programmers.
If you want to select an additional piece of data, but it’s not stored in any of your tables, you can do that like this:
SELECT age, '1234' FROM people;
Any string inside single quotes will be returned instead of matching a column name.