MySQL beginner
0. MySQL install and access
$ sudo apt update
$ sudo apt upgrade
$ sudo apt install mysql-server mysql-client
# security setting
$ sudo mysql_secure_installation
n-n-y-n-y-y
$ mysql -u root -p
1. select * from
$ show databases;
$ use world;
$ select database();
$ show tables;
$ desc city;
$ desc country;
$ select * from country;
2. where
select
avg(population) as population, avg(gnp) as gnp, avg(gnp) / avg(population) * 1000 as avg
from country
where gnp != 0 and population != 0;
3. where / order by
select
avg(population) as population, avg(gnp) as gnp, avg(gnp) / avg(population) * 1000 as avg
from country
where gnp != 0 and population != 0
order by avg desc;
4. where / order by / group by
select
continent, avg(population) as population, avg(gnp) as gnp, avg(gnp) / avg(population) * 1000 as avg
from country
where gnp != 0 and population != 0
group by continent
order by avg desc;
5. where / order by / group by / having
select
continent, avg(population) as population, avg(gnp) as gnp, avg(gnp) / avg(population) * 1000 as avg
from country
where gnp != 0 and population != 0
group by continent
having avg > 0.01
order by avg desc;
6. where / order by / group by / limit / having
select
continent, avg(population) as population, avg(gnp) as gnp, avg(gnp) / avg(population) * 1000 as avg
from country
where gnp != 0 and population != 0
group by continent
having avg > 0.01
order by avg desc
limit 5;
Reference