-- id
-- email: user1@vlrtraining.in, user2@vlrtraining.in, ...
-- random date
-- enabled (random boolean)
create table users (id int auto_increment primary key, email varchar(40) not null, registered date not null,
active boolean default false);
use classicmodels;
select round(rand());
select date(now()) - interval floor(10000*rand()) day;
drop table if exists users;
drop procedure testdata;
delimiter $$
create procedure testdata()
begin
declare NUMROWS int default 20000;
declare count int default 0;
declare registered_value date default null;
declare email_value varchar(40) default null;
declare active_value boolean default false;
drop table if exists users;
create table users (id int auto_increment primary key, email varchar(40) not null, registered date not null, active boolean default false);
while count < NUMROWS do
set registered_value := date(now()) - interval floor(10000*rand()) day;
set active_value := round(rand());
set email_value := concat("user", count, "@vlrtraining.com");
insert into users (email, registered, active) values (email_value, registered_value, active_value);
set count := count + 1;
end while;
end$$
delimiter ;
drop procedure testdata;
delimiter $$
create procedure testdata()
begin
declare NUMROWS int default 50;
declare count int default 0;
declare email_value varchar(40) default null;
declare registered_value date default null;
declare active_value boolean default false;
drop table if exists users;
create table users (id int auto_increment primary key, email varchar(40) not null, registered date not null, active boolean default false);
while count < NUMROWS do
set registered_value := date(now()) - interval floor(10000*rand()) day;
set email_value := concat("vlruser", count, "@vlrtraining.com");
set active_value := round(rand());
insert into users (email, registered, active) values (email_value, registered_value, active_value);
set count := count + 1;
end while;
end$$
delimiter ;
drop procedure testdata;
delimiter $$
create procedure testdata()
begin
declare NUMROWS int default 50;
declare count int default 0;
declare email_value varchar(40) default null;
declare registered_value date default null;
declare active_value boolean default false;
drop table if exists users;
create table users (id int auto_increment primary key, email varchar(40) not null, registered date not null, active boolean default false);
while count < NUMROWS do
set registered_value := date(now()) - interval floor(10000*rand()) day;
set email_value := concat("vlruser", count, "@vlrtraining.com");
set active_value := round(rand());
insert into users (email, registered, active) values (email_value, registered_value, active_value);
set count := count + 1;
end while;
end$$
delimiter ;
-------------
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 8.0.25 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use classicmodels;
Database changed
mysql> select Now();
+---------------------+
| Now() |
+---------------------+
| 2021-09-28 19:23:31 |
+---------------------+
1 row in set (0.00 sec)
mysql> select date(Now());
+-------------+
| date(Now()) |
+-------------+
| 2021-09-28 |
+-------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval 365 ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> select date(Now()) - interval 365 day;
+--------------------------------+
| date(Now()) - interval 365 day |
+--------------------------------+
| 2020-09-28 |
+--------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval rand() day;
+-----------------------------------+
| date(Now()) - interval rand() day |
+-----------------------------------+
| 2021-09-27 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval rand() day;
+-----------------------------------+
| date(Now()) - interval rand() day |
+-----------------------------------+
| 2021-09-28 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval (1000 * rand()) day;
+--------------------------------------------+
| date(Now()) - interval (1000 * rand()) day |
+--------------------------------------------+
| 2021-09-23 |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval (1000 * rand()) day;
+--------------------------------------------+
| date(Now()) - interval (1000 * rand()) day |
+--------------------------------------------+
| 2020-01-12 |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval (1000 * rand()) day;
+--------------------------------------------+
| date(Now()) - interval (1000 * rand()) day |
+--------------------------------------------+
| 2021-06-08 |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval (1000 * rand()) day;
+--------------------------------------------+
| date(Now()) - interval (1000 * rand()) day |
+--------------------------------------------+
| 2019-11-12 |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval (1000 * rand()) day;
+--------------------------------------------+
| date(Now()) - interval (1000 * rand()) day |
+--------------------------------------------+
| 2021-06-27 |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval (1000 * rand()) day;
+--------------------------------------------+
| date(Now()) - interval (1000 * rand()) day |
+--------------------------------------------+
| 2020-08-19 |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval (1000 * rand()) day;
+--------------------------------------------+
| date(Now()) - interval (1000 * rand()) day |
+--------------------------------------------+
| 2019-09-11 |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval (1000 * rand()) day;\
+--------------------------------------------+
| date(Now()) - interval (1000 * rand()) day |
+--------------------------------------------+
| 2020-04-21 |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval round((1000 * rand())) day;
+---------------------------------------------------+
| date(Now()) - interval round((1000 * rand())) day |
+---------------------------------------------------+
| 2020-09-14 |
+---------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval round((1000 * rand())) day;
+---------------------------------------------------+
| date(Now()) - interval round((1000 * rand())) day |
+---------------------------------------------------+
| 2020-11-12 |
+---------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval round((1000 * rand())) day;
+---------------------------------------------------+
| date(Now()) - interval round((1000 * rand())) day |
+---------------------------------------------------+
| 2020-06-23 |
+---------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval round((10000 * rand())) day;
+----------------------------------------------------+
| date(Now()) - interval round((10000 * rand())) day |
+----------------------------------------------------+
| 2012-02-18 |
+----------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval round((10000 * rand())) day;
+----------------------------------------------------+
| date(Now()) - interval round((10000 * rand())) day |
+----------------------------------------------------+
| 2011-08-20 |
+----------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval round((10000 * rand())) day;
+----------------------------------------------------+
| date(Now()) - interval round((10000 * rand())) day |
+----------------------------------------------------+
| 2000-01-11 |
+----------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval round((10000 * rand())) day;
+----------------------------------------------------+
| date(Now()) - interval round((10000 * rand())) day |
+----------------------------------------------------+
| 1998-04-02 |
+----------------------------------------------------+
1 row in set (0.00 sec)
mysql> delimiter $$
mysql> create procedure testdata()
-> begin
-> drop table if exists users;
-> create table users (id int auto_increment primary key, email varchar(40) not null, registered date not null, active boolean default false);
->
-> end$$
ERROR 1304 (42000): PROCEDURE testdata already exists
mysql> delimiter ;
mysql> drop procedure testdata;
Query OK, 0 rows affected (0.27 sec)
mysql> delimiter $$
mysql> create procedure testdata()
-> begin
-> drop table if exists users;
-> create table users (id int auto_increment primary key, email varchar(40) not null, registered date not null, active boolean default false);
->
-> end$$
Query OK, 0 rows affected (0.19 sec)
mysql> delimiter ;
mysql> show tables;
+-------------------------+
| Tables_in_classicmodels |
+-------------------------+
| accounts |
| customers |
| employees |
| offices |
| orderdetails |
| orders |
| payments |
| productlines |
| products |
| users |
+-------------------------+
10 rows in set (0.44 sec)
mysql>
mysql> drop table users;
Query OK, 0 rows affected (1.39 sec)
mysql> call testdata();
Query OK, 0 rows affected (1.29 sec)
mysql> show tables;
+-------------------------+
| Tables_in_classicmodels |
+-------------------------+
| accounts |
| customers |
| employees |
| offices |
| orderdetails |
| orders |
| payments |
| productlines |
| products |
| users |
+-------------------------+
10 rows in set (0.00 sec)
mysql> desc users;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| email | varchar(40) | NO | | NULL | |
| registered | date | NO | | NULL | |
| active | tinyint(1) | YES | | 0 | |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.07 sec)
mysql> drop procedure testdata;
Query OK, 0 rows affected (0.42 sec)
mysql> delimiter $$
mysql> create procedure testdata()
-> begin
->
-> declare NUMROWS int default 50;
-> declare count int default 0;
-> declare email_value varchar(40) default null;
-> declare registered_value date default null;
-> declare active_value boolean default false;
-> drop table if exists users;
-> create table users (id int auto_increment primary key, email varchar(40) not null, registered date not null, active boolean default false);
-> while count < NUMROWS do
-> set registered_value := date(now()) - interval floor(10000*rand()) day;
-> set email_value := concat("vlruser", count, "@vlrtraining.com");
-> set active_value := round(rand());
-> insert into users (email, registered, active) values (email_value, registered_value, active_value);
-> set count := count + 1;
-> end while;
-> end$$
Query OK, 0 rows affected (0.16 sec)
mysql> delimiter ;
mysql> call testdata();
Query OK, 1 row affected (6.95 sec)
mysql> select * from users;
+----+---------------------------+------------+--------+
| id | email | registered | active |
+----+---------------------------+------------+--------+
| 1 | vlruser0@vlrtraining.com | 1996-10-25 | 1 |
| 2 | vlruser1@vlrtraining.com | 2017-05-15 | 1 |
| 3 | vlruser2@vlrtraining.com | 1998-09-16 | 1 |
| 4 | vlruser3@vlrtraining.com | 2006-09-23 | 1 |
| 5 | vlruser4@vlrtraining.com | 1998-07-05 | 1 |
| 6 | vlruser5@vlrtraining.com | 2016-08-13 | 0 |
| 7 | vlruser6@vlrtraining.com | 1996-07-14 | 1 |
| 8 | vlruser7@vlrtraining.com | 1999-08-17 | 1 |
| 9 | vlruser8@vlrtraining.com | 2020-01-05 | 1 |
| 10 | vlruser9@vlrtraining.com | 1996-04-05 | 1 |
| 11 | vlruser10@vlrtraining.com | 2018-07-27 | 0 |
| 12 | vlruser11@vlrtraining.com | 1999-02-13 | 0 |
| 13 | vlruser12@vlrtraining.com | 2003-10-03 | 0 |
| 14 | vlruser13@vlrtraining.com | 2020-06-10 | 0 |
| 15 | vlruser14@vlrtraining.com | 1994-08-23 | 0 |
| 16 | vlruser15@vlrtraining.com | 2011-05-14 | 0 |
| 17 | vlruser16@vlrtraining.com | 2013-09-18 | 0 |
| 18 | vlruser17@vlrtraining.com | 2017-05-14 | 0 |
| 19 | vlruser18@vlrtraining.com | 2010-11-02 | 0 |
| 20 | vlruser19@vlrtraining.com | 2018-03-12 | 0 |
| 21 | vlruser20@vlrtraining.com | 1997-09-14 | 1 |
| 22 | vlruser21@vlrtraining.com | 2007-08-04 | 1 |
| 23 | vlruser22@vlrtraining.com | 1996-07-30 | 1 |
| 24 | vlruser23@vlrtraining.com | 1999-07-21 | 1 |
| 25 | vlruser24@vlrtraining.com | 2018-03-27 | 0 |
| 26 | vlruser25@vlrtraining.com | 2017-12-01 | 0 |
| 27 | vlruser26@vlrtraining.com | 2007-12-24 | 0 |
| 28 | vlruser27@vlrtraining.com | 2013-09-12 | 0 |
| 29 | vlruser28@vlrtraining.com | 2020-02-21 | 0 |
| 30 | vlruser29@vlrtraining.com | 2008-12-13 | 1 |
| 31 | vlruser30@vlrtraining.com | 2012-02-26 | 1 |
| 32 | vlruser31@vlrtraining.com | 2009-06-01 | 1 |
| 33 | vlruser32@vlrtraining.com | 2010-09-24 | 0 |
| 34 | vlruser33@vlrtraining.com | 2005-08-04 | 1 |
| 35 | vlruser34@vlrtraining.com | 2005-08-12 | 0 |
| 36 | vlruser35@vlrtraining.com | 1997-07-15 | 0 |
| 37 | vlruser36@vlrtraining.com | 2008-03-03 | 0 |
| 38 | vlruser37@vlrtraining.com | 2009-02-08 | 1 |
| 39 | vlruser38@vlrtraining.com | 2013-05-01 | 0 |
| 40 | vlruser39@vlrtraining.com | 2003-01-01 | 0 |
| 41 | vlruser40@vlrtraining.com | 2015-02-16 | 1 |
| 42 | vlruser41@vlrtraining.com | 2009-12-21 | 1 |
| 43 | vlruser42@vlrtraining.com | 1994-07-04 | 0 |
| 44 | vlruser43@vlrtraining.com | 2020-10-31 | 0 |
| 45 | vlruser44@vlrtraining.com | 2003-03-16 | 1 |
| 46 | vlruser45@vlrtraining.com | 2006-03-18 | 0 |
| 47 | vlruser46@vlrtraining.com | 2002-12-20 | 0 |
| 48 | vlruser47@vlrtraining.com | 1996-07-02 | 0 |
| 49 | vlruser48@vlrtraining.com | 2012-04-15 | 1 |
| 50 | vlruser49@vlrtraining.com | 2014-07-02 | 0 |
+----+---------------------------+------------+--------+
50 rows in set (0.00 sec)
mysql>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.