Join Bill Weinman for an in-depth discussion in this video The SELECT statement, part of MySQL Essential Training.
- View Offline
SQL is a very common language for querying and manipulating data in a database management system. Every system has it's own variation of SQL and MySQL is no different. The basics are the same but the details can be very different. An SQL statement begins with a key word and ends with a semicolon. Technically the semicolon is a statement terminator in SQL. That means that it's always required. Some systems, however, including MySQL, may use the semicolon as a separator in some contexts.
This means that it's not always required if there's only one statement. The semicolon is always allowed so it never hurts to use it, and I recommend that you get in the habit of using it all the time. SQL statements are not case sensitive that means the capital letters and lowercase letters are treated as the same. So these two statements are effectively the same. By default most symbols in SQL are also not case sensitive, but there are exceptions. In this example, the table name may or may not be case sensitive.
So these two statements may or may not be equivalent. If your MySQL Server's running on Windows or a Mac, the table name will not be case sensitive and these two statements will be equivalent. If your MySQL Server is running on a UNIX system or any operating system with. Case sensitive file names, the table name may be case sensitive and these two statements may refer to two different tables. Keep in mind that MySQL allows configuration options that may change this behavior, it's possible to configure your server so that all symbols are case sensitive.
I suggest that you always write your SQL consistently including the case of your symbols. This will make your code as portable as possible. Remember that your server is often running on a different operating system than your desktop. So even though your desktop is a Windows or Mac systems, your server may not be. This is an example of a standard line oriented S-Q-L comment. The comment is introduced by a double dash followed by at least one space, and ends at the end of the line. More specifically, the comment is introduced by two hyphen characters with no space between them.
And at least one space after the second hyphen. This is followed by the comment text. And the comment is terminated with a new line. MySQL also recognizes c style comments. This also conforms with the latest SQL standard. This allows multi-line comments with much more ease than line-oriented comments. MySQL also recognizes single line comments introduced with the pound character. This is not standard SQL, and I recommend that you avoid using this style of comment.
You may see this in very old MySQL code, and I recommend that you change these comments. To use the double dash form. A statement may have one or more clauses depending on the syntax of the statement. For example, this SELECT statement has a FROM clause and a WHERE clause. The FROM clause specifies the table. And the WHERE clause specifies the condition that must be satisfied for each of the rows selected. Functions are used to perform specific operations on data. In this example, the count function is used to find the number of rows matching the condition in the where clause.
Expressions are used in SQL to derive values from data. For example, this statement has 2 expressions. This expression divides the population column by 1 million in order to display the population in millions. And this logical expression is used to select only those rows where the population column is greater than or equal to 1 million. The structure of SQL syntax is very simple, but the rules can be complex depending on the statements and usage. In the rest of this course, we'll look at the specific details for many usages.
- Writing queries
- Creating and updating databases and tables
- Using MySQL built-in functions
- Sorting and filtering data
- Updating tables with triggers
- Working with subselects and views
- Creating and using a stored function