SQL Select

Basic Format
- Select/From/Where
- SELECT clause describes what columns you wish to display in the rinal table.
- From describes the tables from which you will gather this data
- Where specifies the rows you wish to use in your query.
There are other components we will discover in this chapter, but this will do.
You should look at the MySQL reference page
- Keywords are in ALL CAPS
- SELECT
- Optional parts are in []
- [HIGH_PRIORITY]
- Mutually exclusive portions are delimited by |
- [ALL | DISTINCT | DISTINCTROW ]
- Required parts are in bold
- [WHERE where_condition]
- Multiples are delimited by ,
- select_expr [, select_expr ...]
- select 3,5,7;
- Notice ORDER is important here
- SELECT FROM SKU_DATA Buyer;

Comments

See the MySQL reference page
/* comment */
or # comment
Wherever, like c/c++

SELECT  # this is how the select statement begins
     /* next we give the fields */
     Buyer  # the  buyer field
     /*  we can give multiples if we wish */, Department # the department name
     FROM /* now give the table name */ SKU_DATA;

```
SELECT Department, Buyer
    FROM SKU_DATA;
    
```
- Selects the Department and Buyer columns
- From the SKU_DATA table
- And returns the result as a table.
You can send data to a file
- $ mysql -u dbennett -p -e "Select * from dbennett.SKU_DATA" > foo.txt
- This will write to the local directory.
- Or SELECT * from SKU_DATA INTO OUTFILE "/tmp/foo.txt";
- This must write to tmp as the mysqld is running as user mysql
- Finally, I/O redirection at the shell level, which might be best
- mysql -u dbennett -p &tl; demo.sql > demo.out
The column order is specified by the order of field names.
- SELECT Department,Buyer FROM SKU_DATA
- SELECT Buyer,Department FROM SKU_DATA

The keyword DISTINCT

Will filter duplicates
SELECT DISTINCT fields ...
```
SELECT Department 
    FROM SKU_DATA;
```

SELEET DISTINCT Department 
    FROM SKU_DATA;

I inserted a new record into the SKU_DATA table

INSERT INTO SKU_DATA 
    (SKU, SKU_Description, Department, Buyer) 
    VALUES
    (400000,"Junk","Junk","Pete Hansen");

This puts Pete Hansen into two departments.
We can undo this with

DELETE FROM SKU_DATA
    WHERE SKU=400000;

Try this with and without the extra data.

SELECT DISTINCT Department, Buyer 
     FROM SKU_DATA;

We are cautioned that DISTINCT may be expensive on large tables.

Field wildcard
- * matches all fields in the specified tables
- ```
SELECT * 
    FROM SKU_DATA;
       
```
The WHERE clause
SELECT fields FROM tables WHERE expression

Most expressions are acceptable. SELECT * FROM INVENTORY WHERE QuantityOnHand > 10 AND QuantityOnOrder = 250;