mysql -utfinin -ppsdHsyxwNXNJTMFt tfinin

show tables;
+--------------------+
| Tables_in_tfinin   |
+--------------------+
| filters            |
| location_aliases   |
| location_updates   |
| old_statuses       |
| old_statuses2      |
| old_statuses3      |
| old_statuses4      |
| old_statuses5      |
| searches           |
| statuses           |
| user_locations     |
| user_relationships |
| users              |
+--------------------+

select text from statuses limit 10;

select text from statuses where text like '%joke%' limit 10;

describe statuses;
describe users;

select id,name from users limit 10;
select user_id,text from statuses limit 10;

# aggregate
select count(*) from users;
select count(*) from statuses;

# range query
select name from users where name > 'aaa' and name < 'aab'
		
# database join		   
select name, text from users, statuses where users.id = statuses.user_id and text like '%joke%' limit 10;

select user_id, count(*) as n from statuses group by user_id  having n > 10000 order by n desc;

# database view
create view heavy_users as 
select user_id, count(*) as n 
from statuses 
group by user_id 
having n > 10000;

select * from heavy_users;

select name,n from users,heavy_users
where users.id = heavy_users.user_id
order by n desc;

select name,text from users,statuses where users.id = statuses.user_id and name = 'freelancejobz' limit 100;