URL: google.com youtube.com google.com youtube.com test.com youtube.com
DateVisited: 1313668492 1313668540 1313668571 13154314

LINK       | timesExisted | timesVisitedYesterday
Google.com |       2      | 2
youtube.com|       3      | 3

subdate(current_date, 1)

SELECT
    url as LINK,
    count(*) as timesExisted,
    sum(DateVisited between UNIX_TIMESTAMP(subdate(current_date, 1)) and
        UNIX_TIMESTAMP(current_date)) as timesVisitedYesterday
FROM mytable
GROUP BY 1;

SELECT * FROM your_table

WHERE DateVisited >= UNIX_TIMESTAMP(CAST(NOW() - INTERVAL 1 DAY AS DATE))
  AND DateVisited <= UNIX_TIMESTAMP(CAST(NOW() AS DATE));

('2012-02-27' - INTERVAL 1 DAY)