MariaDB Crash Course - Ben Forta [15]
* * *
Caution: Row 0
The first row retrieved is row 0, not row 1. As such, LIMIT 1,1 retrieves the second row, not the first one.
* * *
Let’s review. Does LIMIT 3,4 mean 3 rows starting from row 4, or 4 rows starting from row 3? As you just learned, it means 4 rows starting from row 3, but it is a bit ambiguous. For this reason, MariaDB supports an alternative syntax for LIMIT. LIMIT 4 OFFSET 3 means get 4 rows starting from row 3, just like LIMIT 3,4. So, the following two statements are functionally identical, and you can use whichever you are more comfortable with:
Input
SELECT prod_name
FROM products
LIMIT 10,2;
Input
SELECT prod_name
FROM products
LIMIT 2 OFFSET 10;
* * *
Note: When There Aren’t Enough Rows
The number of rows to retrieve specified in LIMIT is the maximum number to retrieve. If there aren’t enough rows (for example, you specified LIMIT 10,5, but there were only 13 rows), MariaDB returns as many as it can.
* * *
Using Fully Qualified Table Names
The SQL examples used thus far have referred to columns by just the column names. It is also possible to refer to columns using fully qualified names (using both the table and column names). Look at this example:
Input
SELECT products.prod_name
FROM products;
This SQL statement is functionally identical to the first one used in this chapter, but here a fully qualified column name is specified.
Table names, too, may be fully qualified, as seen here:
Input
SELECT products.prod_name
FROM crashcourse.products;
Once again, this statement is functionally identical to the one just used (assuming, of course, that the products table is indeed in the crashcourse database).
There are situations where fully qualified names are required, as we see in later chapters. For now, it is worth noting this syntax so you know what it is if you run across it.
Using Comments
As you have seen, SQL statements are instructions processed by MariaDB. But what if you wanted to include text that you do not want processed and executed? Why would you ever want to do this? Here are a few reasons:
• The SQL statements we’ve been using here are all short and simple. But, as your SQL statements grow (in length and complexity), you’ll want to include descriptive comments (for your own future reference or for whoever has to work on the project next). These comments need to be embedded in the SQL scripts, but they are obviously not intended for MariaDB processing. (For an example of this, see the create.sql and populate.sql files used in Appendix B, “The Example Tables.”)
• The same is true for headers at the top of SQL files, perhaps containing the programmer contact information and a description and notes. (This use case is also seen in the Appendix B .sql files.)
• Another important use for comments is to temporarily stop SQL code from being executed. If you were working with a long SQL statement, and wanted to test just part of it, you could comment out some of the code so that MariaDB saw it as comments and ignored it.
MariaDB supports several forms of comment syntax. We start with inline comments:
Input
SELECT prod_name -- this is a comment
FROM products;
Analysis
Comments may be embedded inline using -- (two hyphens). Anything after the -- is considered comment text, making this a good option for describing columns in a CREATE TABLE statement, for example.
Here is another form of inline comment:
Input
# This is a comment
SELECT prod_name
FROM products;
Analysis
A # at the start of a line makes the entire line a comment. You can see this format comment used in the accompanying create.sql and populate.sql scripts.
You can also create multiline comments, and comments that stop and start anywhere within the script:
Input
/* SELECT prod_name, vend_id
FROM products; */
SELECT prod_name
FROM products;
Analysis
/* starts a comment, and */ ends it. Anything between /* and */ is comment text. This type of comment is often used to comment out code, as seen in this example. Here, two SELECT statements are defined, but the