A 38-year-old's MYSQL primer 03
Alright, first real step — sword drawn, radish field spotted, let's go pull some radishes.
Oh, just a heads-up: I use the same practice tables, but my examples won't match the book's one-to-one. ><
0. These little slip-ups can happen.
If there's one thing that frustrates me as a non-major learning dev as a latecomer, it's that no one ever tells you the supposedly 'obvious' stuff. Before each post I'll jot down whatever I remember of those gotchas.
1) You've opened the terminal... how do you close it? You might not know. Nothing is obvious. The answer is exit and then hit Enter.
MySQL politely says bye. ;D
-> result
1. Multi-column update
The title looks a bit heavy but it's nothing fancy. You can update (i.e., modify ;D) column values in a table, and you can do several at once. Let's just work through an example.
1) Let me first view the practice table.
select * from sample41; 
+ α ) Ah... the table is empty. Let's add some values quickly.
(1) Before inserting, let's check the table's column layout.
(2) Now let's insert some values that match the schema.
insert into sample41(no,a,b) values(1,'ABC','2014-01-05'); insert into sample41(no,a,b) values(2,'XYZ',null); 
(3) It says OK. Let me check the table again.
select * from sample41; 
2) Now let's try updating just one column.
update sample41 set no=no+1; select * from sample41; 
3) Now let's update two columns at once.
update sample41 set a='xxx', b='2014-01-01' where no=2; select * from sample41; 
2. Computing averages with the AVG aggregate function
AVG returns an average, and an aggregate function is one that aggregates particular values in a table. Roughly, aggregate functions include counting rows (COUNT), summing (SUM), averaging (AVG), getting the max (MAX), and the min (MIN).
1) Before practicing, let's look at the example table ;D , FYI this table is also used in section 3 (GROUP BY)~
select * from sample51;
2) Let's try each aggregate function once.
(1) COUNT - in SQL the * sign means 'all.'
select count(*) from sample51; 
+ α ) But what if, when counting rows, you want to skip duplicates? The distinct function gives you the count excluding duplicates.
select distinct name from sample51; 
(2) SUM - let's find the sum of the quantity column in the example table.
select sum(quantity) from sample51; 
+ α ) You can also sum only the rows that match a condition on a given column. In this example let's sum only where name = 'A.'
select count(*) from sample51 where name='A'; 
(3) AVG - there are two ways to compute an average.
a) Repurpose what we did above - sum(quantity)/count(quantity)
b) Use the AVG function - avg
select
avg(quantity) '함수사용',
sum(quantity)/count(quantity) '이전방법응용'
from
sample51; 
+ α ) But wait — something's off about the average. Why is it 40000? Shouldn't it be 32000? The reason is that rows where quantity is NULL were also counted in the row count. In that case, we need to convert NULL to 0 for the calculation. Shall we revisit what we did earlier? Remember, there were two ways?
i) using the coalesce function, and ii) using a CASE statement ;D Let's print both as separate columns. Both approaches give 32000.
select
sum(quantity)/count(coalesce(quantity,0)) i,
sum(quantity)/count(case when quantity is null then 0 else quantity end) ii
from
sample51; 
(4) MAX and MIN - for strings, ordering follows alphabet order.
select min(name), max(name) , min(quantity), max(quantity) from sample51; 
3. Grouping - with group by you can broaden how aggregates are used.
1) First a quick example. The query below just groups by the name column. In other words, rows with the same name are lumped together. Even before running it, you can guess the result will be the same as the earlier select distinct name from sample51; query.
select name from sample51 group by name; 
2) This time let's run aggregate computations over the groups. The result will include the grouped name column and a sum column for each name.
select
name '그룹핑된_값들',
count(name) '그룹핑별_갯수' ,
sum(quantity) '그룹핑별_합'
from
sample51
group by name; 
3) Now let's add a condition. Say we want only the groups whose sum equals 3.
select
name '그룹핑된_값들',
sum(quantity) '그룹핑별_합'
from
sample51
group by
name
having
sum(quantity)=3 ; + α ) If you used an alias in the SELECT list,
then in the having clause (or any other downstream operation),
you can write either sum(quantity) as above, or simply use the alias (sum_per_group) as below~
Note: in ORACLE, the example below won't run. ><
select
name '그룹핑된_값들',
sum(quantity) '그룹핑별_합'
from
sample51
group by
name
having
그룹핑별_합=3 ; 
4) So far we've only put the grouped name column in SELECT. How do we include multiple columns? If you just shove them in, it blows up. You have to declare them in GROUP BY first, then put them in SELECT. In other words, any column not listed in GROUP BY can't appear in SELECT. Let's verify with an example.
(1) This one errors out — it 'blows up,' as they say ;D
select
no '선언_안한_no열',
name '선언_한_name열' ,
quantity '선언_안한_quantity열'
from
sample51
group by name; 
(2) This one runs without trouble.
select
no '선언_한_no열',
name '선언_한_name열' ,
quantity '선언_한_quantity열'
from
sample51
group by
no, name, quantity;
+ α ) Want to add a sort on top? For example, to see rows sorted by 'quantity_declared' descending. Just append DESC at the end as shown.
select
no '선언_한_no열',
name '선언_한_name열' ,
quantity '선언_한_quantity열'
from
sample51
group by
no, name, quantity
order by
quantity
desc;
