MySQL note
===
sql count() function
---
### syntax:
select (col_name) from (table_name);
### example:
table name: DESSERT
|customer|dessert|
|--:|--:|
|Andrew|ice cream|
|David|chocolate|
|Ben|cake|
|Christine|chocolate|
|Anna|cake|

MySQL code:
`select count(dessert) from DESSERT where dessert = "chocolate";`

output:
2

extra practice:
Weather Observation Station 4 on hackerrank
<https://www.hackerrank.com/challenges/weather-observation-station-4/problem?isFullScreen=true>

sql min() function
---
### syntax:
select min(col_name) from (table_name);
### example:
table name: money
|name|cash|deposit|
|--:|--:|--:|
|Andrew|$1000|$3000|
|Ben|$15000|$7000|
|David|$5000|$20000|
|Teddy|$3000|$10000|
|Anna|$10000|$2000|
|Christine|$5000|$9000|

MySQL code:
`select min(cash) from money;`

output:
1000

sql max() function
---
### syntax:
selecr max(col_name) from (table_name);
### example:
table name: money
|name|cash|deposit|
|--:|--:|--:|
|Andrew|$1000|$3000|
|Ben|$15000|$7000|
|David|$5000|$20000|
|Teddy|$3000|$10000|
|Anna|$10000|$2000|
|Christine|$5000|$9000|

MySQL code:
`select max(cash) from money;`

output:
15000

sql length() function
---
### syntax:
select length(col_name) from (table_name);

### example:
table name: studentlist
|id|name|
|--:|--:|
|1|Andrew|
|2|Anna|
|3|Ben|
|4|Christine|
|5|David|
|6|Teddy|

MySQL code:
`select length(name) from studentlist; `

output:
6
4
3
9
5
5

**length() function return the length of "string"**

sql limit
---
### syntax:
select (col_name) from (table_name) limit number;

### example:
table name: studentlist
|id|name|
|--:|--:|
|1|Andrew|
|2|Anna|
|3|Ben|
|4|Christine|
|5|David|
|6|Teddy|

MySQL code:
`select name from studentlist limit 2;`

output:
Andrew
Anna

The first col and the second one will be selected.

sql order by
---
### syntax:
select (col_name) from (table_name) order by (col_name);

### example:
table name: studentmajor
|id|name|major|
|--:|--:|--:|
|1|Andrew|CS|
|2|Anna|MSE|
|3|Ben|EE|
|4|Christine|MSE|
|5|David|MED|
|6|Teddy|MSE|

MySQL code:
`select * from studentmajor order by major;`

output:
1 Andrew CS
3 Ben EE
5 David MED
2 Anna MSE
4 Christine MSE
6 Teddy MSE

**the default of order by is ascend**

sql asc/desc
---
### syntax:
select (col_name) from (table_name) order by asc/desc;

### example:
table name: student303
|id|name|major|university|
|--:|--:|--:|--:|
|1|Adrian|MED|CGU|
|2|Andrew|CS|NYCU|
|3|Anna|MSE|TUOS|
|4|Ben|EE|NTU|
|5|Christine|MSE|NTHU|
|6|David|MED|NYCU|
|7|Teddy|MSE|NTHU|

MySQL code:
`select name from student303 order by university asc, name desc;`

output:
Adrian
Teddy
Christine
Ben
David
Andrew
Anna

sql distinct
---
### syntax:
select distinct (col_name) from (table_name);

### example:
table name: studentmajor
|id|name|major|
|--:|--:|--:|
|1|Andrew|CS|
|2|Anna|MSE|
|3|Ben|EE|
|4|Christine|MSE|
|5|David|MED|
|6|Teddy|MSE|

MySQL code:
`select distinct major from studentmajor;`

output:
CS
MSE
EE
MED

sql left function
---
### syntax:
left( input_string, number_of_characters )

### example:
table name: studentlist
|id|name|
|--:|--:|
|1|Andrew|
|2|Anna|
|3|Ben|
|4|Christine|
|5|David|
|6|Teddy|

MySQL code:
`select left(name, 2) from studentlist;`

output:
An
An
Be
Ch
Da
Te

sql in
---
### syntax:

sql left join
---
### syntax:

sql limit(another type):
---
### syntax:
select (col_name) from (table_name) limit (ith row), (line_amount);

### example:
table name: money
|name|cash|deposit|
|--:|--:|--:|
|Andrew|$1000|$3000|
|Ben|$15000|$7000|
|David|$5000|$20000|
|Teddy|$3000|$10000|
|Anna|$10000|$2000|
|Christine|$5000|$9000|

MySQL code:
`select name from money order by deposit asc limit 1, 1;`

output:
Andrew
**warning: the sql default the ith row start from 0**

MySQL code:
`select name from money order by deposit asc limit 0, 1;`

output:
Anna

MySQL code:
`select name from money order by deposit asc limit 1, 2;`

output:
Andrew
Ben