MySQL Cheat Sheet

mysql_cheat_sheet.md

MySQL Cheat Sheet

Reference for SQL commands

Login

mysql -u abc -h 13.209.48.208 -p

Show Users

SELECT User,Host,plugin,authentication_string mysql.user;

Create User

CREATE USER 'your_id'@'localhost' IDENTIFIED BY 'your_pw';
CREATE USER 'your_id'@'%' IDENTIFIED BY 'your_pw';

Sql file import/export

sudo mysql -u <USERNAME> -p <DATABASE-NAME> < app1.sql

Data Manipulation

show databases;
use world;
show tables;
show table status;
describe city;
desc country;
desc countrylanguage;
select * from city;
desc city;
select Name, Population from city;
select Name, Population 
from city
where Population > 9000000;

select * from city
where Population < 8000000
and Population > 7000000;

select * from city
where CountryCode = 'KOR'
and Population >= 1000000;

select * from city 
where Population between 7000000 and 8000000;

select * from city
where Name in ('Seoul', 'New York');

select * from city
where CountryCode in ('KOR', 'USA');

select * from city 
where CountryCode LIKE 'KO_';

select * from city
where Name like 'Tel %';
-- Sub query
select * from city 
where CountryCode = ( select CountryCode from city
					   where Name = 'Seoul' );
                       
select * from city
where Population > ANY ( select Population from city
						 where District = 'New York' );


select * from city
where Population > ALL ( select Population from city
						 where District = 'New York' );
                         
select * from city    
order by Population DESC;                    

select * from city
order by CountryCode ASC, Population DESC;

select * from city
where CountryCode = 'KOR'
order by Population DESC;

select * from country
order by SurfaceArea desc;

select distinct CountryCode from city;

select * from city 
order by Population desc 
limit 10;

select CountryCode, MAX(Population) from city
group by CountryCode;

-- 도시 갯수 집계 
select count(*) from city;

-- 도시의 평균 인구 
select avg(Population) from city;

select CountryCode, MAX(Population) from city
group by CountryCode
having MAX(Population) > 8000000;

select * from city
JOIN country ON city.CountryCode = country.Code;

select * from city
JOIN country ON city.CountryCode = country.Code
JOIN countrylanguage ON city.CountryCode = countrylanguage.CountryCode;

-- 내장 함수 
select length ('hello');

select concat ('My', 'Sql ', '!');

select locate('abc', '0000abc');

select 
left('MySQL is an open source relational database management system.', 5),
right('MySQL is an open source relational database management system.', 7);

select 
lower("MySQL gooD"),
upper("MySQL gooD");

select replace('MSSQL', 'MS', 'My');

select format(12312300, 3);
select floor(10.995), ceil(10.95), round(10.95);
select sqrt(4), pow(2,3), exp(3), log(3);

select sin(PI() / 2), cos(PI());

select abs(-4), rand(), ROUND(RAND() * 100, 0);

select now(), curdate(), curtime();

select 
now(),
date(now()),
month(now()),
day(now());

select 
date_format(now(), '%D %y %a %d %m %j');

Data Create