A 38-year-old's MYSQL primer~
Alright, now the first real step. Drew the sword — time to go pull a radish with it!
0. These kinds of little slip-ups can happen.
If I had to name one thing I find frustrating as a non-major studying dev as a latecomer: no one bothers telling you the supposedly 'obvious' stuff. Before each post, I'll jot down whatever I remember of those gotchas.
1) At the end of a SQL statement, you must type a ' ; ' before hitting Enter.
1. DESC
Upfront: DESC is not a SQL command.
desc sample21; 
The table displayed in the result has six columns: field, type, null, key, default, and extra.
For the full explanation, please refer to the book.
2. The <> operator
Typically, to fetch all rows of a given table:
= search (select ) everything (* ) from (from ) the table name (sample21 )
= here, the table name is the part that changes.
select * from sample21; 
Extract only the rows where the no column's value is not 2.
select * from sample21 where no <> 2; 
3. Limiting result count after sorting
There are commands for limiting result count: LIMIT, TOP, ROWNUM, etc. — but each only works in certain environments.
LIMIT is for MySQL; TOP is for SQL Server; ROWNUM+WHERE is for Oracle.
1) This blog uses MySQL, so let's try LIMIT.
select * from sample33 limit 3; 
+ α ) For reference, how many rows did the sample33 table have before LIMIT?
Running it using example 2, you get this. Seven rows in total~
2) OK, I get how to limit the count. But what if I want descending order (9, 7, 6...) instead of ascending (1, 2, 3, 4...)? Let's try it.
select * from sample33 order by no desc limit 3; 
3) For reference, in Oracle you use the rownum keyword combined with operators like >, <, =, <=.
select * from sample33 where rownum<= 3; 4. LIMIT + OFFSET on MySQL
So just above in 3-3) we tried tweaking the sort order. Now how about picking a specific range? That's done via the offset option. By the way (TMI warning): 'off-set' means an integer indicating the displacement from the beginning of an object to a specific element or point within the same object.
1) The query we ran earlier, select * from sample33 limit 3 , was really this:
select * from sample33 limit 3 offset 0; 
2) Now modifying it as below, we can see the offset option is (not inclusive, i.e., 'itself +1') the starting position.
select * from sample33 limit 3 offset 3; 
5. Checking the system date with CURRENT_TIMESTAMP
Now let's step away from lists and try something new. Let's practice a SQL query that handles dates.
1) First, shall we fetch today's date?
* current_timestamp : today's date on the system (your computer)
select current_timestamp; 
+ α ) For reference, current_timestamp is a standard SQL function. Because it specifies the current date, (1) it doesn't take arguments and (2) unlike traditional DBs such as Oracle, you can omit the FROM clause.
* current_date : today's date on the system (your computer)
select current_date; 
+ α ) Note: CURRENT_TIME and CURRENT_TIME() are aliases of CURTIME(). It returns the current date in either 'YYYY-MM-DD' or YYYYMMDD form depending on whether the function is used in a string or numeric context.
Related result ->
+ α ) For reference, if you swap date for time you'll get the time info. I'll omit the result ;>
2) Now let's add to the date. As you can see from the result below, I tried two approaches — whether you include the interval option or not, the result is the same.
select current_date + 1 day; 
After some searching, more concrete examples of MySQL date/time functions are available at Nazuni Lab(?). If you're interested, follow the link~ That wraps up the first practice session the end~ ;D
