Quick SQL query to generate sample data

Question

I need to fill in the current empty table with a hundred fake entries in order to simulate logins in the last two years in order to check my code.

The layout of the entry table is as follows:

CREATE TABLE `Logins` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `User_ID` int(11) NOT NULL,
  `Date_Login` datetime NOT NULL,
  `Location` enum('site','admin') NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I'm really new to SQL in general, so I have no idea that the query should look like the past

INSERT INTO `Logins` (`User_ID`,`Date_Login`,`Location`) VALUES ...

I need to insert N records (say 100) in Loginsto

• User_IDretrieves values ​​from Userstable fieldID
• Date_Login should be from 2 years ago and now
• Locationshould alternate between 'site'and 'admin', but with a 'site'more heavily weighted (say, 80% of the time).

Hope I can compile some SQL-fu to help with similar problems in the future: D

Thank!

(I am using MySQL 5.1)

Answer 1

SQL, Logins. (, ), . , , LIMIT 1 LIMIT 10, 10 , User_ID .

INSERT INTO `Logins` (`User_ID`, `Date_Login`, `Location`)
SELECT
  users.ID AS `User_ID`,
  DATE_SUB(NOW(), INTERVAL FLOOR(1 + (RAND() * (365 * 2))) DAY) AS `Date_Login`,
  IF(RAND() > 0.8, 'admin', 'site') AS `Location`
FROM users
ORDER BY RAND()
LIMIT 1;

ORDER BY RAND() , , .

Answer 2

SQL-, : http://dev.mysql.com/doc/refman/5.1/en/iterate-statement.html , RAND() +, * .